Class: PLSQL::Table

Inherits:
Object
  • Object
show all
Extended by:
TableClassMethods
Defined in:
lib/plsql/table.rb

Direct Known Subclasses

View

Defined Under Namespace

Classes: TableProcedure

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from TableClassMethods

find

Constructor Details

#initialize(schema, table, override_schema_name = nil) ⇒ Table

:nodoc:



39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/plsql/table.rb', line 39

def initialize(schema, table, override_schema_name = nil) #:nodoc:
  @schema = schema
  @schema_name = override_schema_name || schema.schema_name
  @table_name = table.to_s.upcase
  @columns = {}

  @schema.select_all(
    "SELECT c.column_name, c.column_id position,
          c.data_type, c.data_length, c.data_precision, c.data_scale, c.char_used,
          c.data_type_owner, c.data_type_mod,
          CASE WHEN c.data_type_owner IS NULL THEN NULL
          ELSE (SELECT t.typecode FROM all_types t
            WHERE t.owner = c.data_type_owner
            AND t.type_name = c.data_type) END typecode,
          c.nullable, c.data_default
    FROM all_tab_columns c
    WHERE c.owner = :owner
    AND c.table_name = :table_name",
    @schema_name, @table_name
  ) do |r|
    column_name, position,
          data_type, data_length, data_precision, data_scale, char_used,
          data_type_owner, data_type_mod, typecode, nullable, data_default = r
    # remove scale (n) from data_type (returned for TIMESTAMPs and INTERVALs)
    data_type.sub!(/\(\d+\)/,'')
    # store column metadata
    @columns[column_name.downcase.to_sym] = {
      :position => position && position.to_i,
      :data_type => data_type_owner && (typecode == 'COLLECTION' ? 'TABLE' : 'OBJECT' ) || data_type,
      :data_length => data_type_owner ? nil : data_length && data_length.to_i,
      :data_precision => data_precision && data_precision.to_i,
      :data_scale => data_scale && data_scale.to_i,
      :char_used => char_used,
      :type_owner => data_type_owner,
      :type_name => data_type_owner && data_type,
      :sql_type_name => data_type_owner && "#{data_type_owner}.#{data_type}",
      :nullable => nullable == 'Y', # store as true or false
      :data_default => data_default && data_default.strip # remove leading and trailing whitespace
    }
  end
end

Instance Attribute Details

#columns ⇒ Object (readonly)

:nodoc:



37
38
39
 
# File 'lib/plsql/table.rb', line 37

def columns
  @columns
end

#schema_name ⇒ Object (readonly)

:nodoc:



37
38
39
 
# File 'lib/plsql/table.rb', line 37

def schema_name
  @schema_name
end

#table_name ⇒ Object (readonly)

:nodoc:



37
38
39
 
# File 'lib/plsql/table.rb', line 37

def table_name
  @table_name
end

Instance Method Details

#all(sql = '', *bindvars) ⇒ Object

Select all table records using optional conditions. Examples:

plsql.employees.all
plsql.employees.all(:order_by => :employee_id)
plsql.employees.all("WHERE employee_id > :employee_id", 5)



135
136
137
 
# File 'lib/plsql/table.rb', line 135

def all(sql='', *bindvars)
  select(:all, sql, *bindvars)
end

#column_names ⇒ Object

list of table column names



82
83
84
 
# File 'lib/plsql/table.rb', line 82

def column_names
  @column_names ||= @columns.keys.sort_by{|k| columns[k][:position]}
end

#count(sql = '', *bindvars) ⇒ Object

Count table records using optional conditions. Examples:

plsql.employees.count
plsql.employees.count("WHERE employee_id > :employee_id", 5)



155
156
157
 
# File 'lib/plsql/table.rb', line 155

def count(sql='', *bindvars)
  select(:count, sql, *bindvars)
end

#delete(sql_params = '', *bindvars) ⇒ Object

Delete table records using optional conditions. Example:

plsql.employees.delete(:employee_id => 1)
# => DELETE FROM employees WHERE employee_id = 1



234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/plsql/table.rb', line 234

def delete(sql_params='', *bindvars)
  delete_sql = "DELETE FROM \"#{@schema_name}\".\"#{@table_name}\" "
  case sql_params
  when String
    delete_sql << sql_params
  when Hash
    raise ArgumentError, "Cannot specify bind variables when passing WHERE conditions as Hash" unless bindvars.empty?
    where_sqls = []
    sql_params.each do |k,v|
      where_sqls << "#{k} = :#{k}"
      bindvars << v
    end
    delete_sql << "WHERE " << where_sqls.join(' AND ') unless where_sqls.empty?
  else
    raise ArgumentError, "Only String or Hash can be provided as SQL condition argument"
  end
  @schema.execute(delete_sql, *bindvars)
end

#first(sql = '', *bindvars) ⇒ Object

Select first table record using optional conditions. Examples:

plsql.employees.first
plsql.employees.first(:employee_id => 1)
plsql.employees.first("WHERE employee_id = 1")
plsql.employees.first("WHERE employee_id = :employee_id", 1)



146
147
148
 
# File 'lib/plsql/table.rb', line 146

def first(sql='', *bindvars)
  select(:first, sql, *bindvars)
end

#insert(record) ⇒ Object

Insert record or records in table. Examples:

employee = { :employee_id => 1, :first_name => 'First', :last_name => 'Last', :hire_date => Time.local(2000,01,31) }
plsql.employees.insert employee
# => INSERT INTO employees VALUES (1, 'First', 'Last', ...)

employees = [employee1, employee2, ... ]  # array of many Hashes
plsql.employees.insert employees



168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/plsql/table.rb', line 168

def insert(record)
  # if Array of records is passed then insert each individually
  if record.is_a?(Array)
    record.each {|r| insert(r)}
    return nil
  end

  table_proc = TableProcedure.new(@schema, self, :insert)
  table_proc.add_insert_arguments(record)

  call = ProcedureCall.new(table_proc, table_proc.argument_values)
  call.exec
end

#insert_values(*args) ⇒ Object

Insert record or records in table using array of values. Examples:

# with values for all columns
plsql.employees.insert_values [1, 'First', 'Last', Time.local(2000,01,31)]
# => INSERT INTO employees VALUES (1, 'First', 'Last', ...)

# with values for specified columns
plsql.employees.insert_values [:employee_id, :first_name, :last_name], [1, 'First', 'Last']
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'First', 'Last')

# with values for many records
plsql.employees.insert_values [:employee_id, :first_name, :last_name], [1, 'First', 'Last'], [2, 'Second', 'Last']
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'First', 'Last')
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (2, 'Second', 'Last')

Raises:

  • (ArgumentError) 


197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/plsql/table.rb', line 197

def insert_values(*args)
  raise ArgumentError, "no arguments given" unless args.first
  # if first argument is array of symbols then use it as list of fields
  if args.first.all?{|a| a.instance_of?(Symbol)}
    fields = args.shift
  # otherwise use all columns as list of fields
  else
    fields = column_names
  end
  args.each do |record|
    raise ArgumentError, "record should be Array of values" unless record.is_a?(Array)
    raise ArgumentError, "wrong number of column values" unless record.size == fields.size
    insert(ArrayHelpers::to_hash(fields, record))
  end
end

#select(first_or_all, sql_params = '', *bindvars) ⇒ Object

General select method with :first, :all or :count as first parameter. It is recommended to use #first, #all or #count method instead of this one.



88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/plsql/table.rb', line 88

def select(first_or_all, sql_params='', *bindvars)
  case first_or_all
  when :first, :all
    select_sql = "SELECT * "
  when :count
    select_sql = "SELECT COUNT(*) "
  else
    raise ArgumentError, "Only :first, :all or :count are supported"
  end
  select_sql << "FROM \"#{@schema_name}\".\"#{@table_name}\" "
  case sql_params
  when String
    select_sql << sql_params
  when Hash
    raise ArgumentError, "Cannot specify bind variables when passing WHERE conditions as Hash" unless bindvars.empty?
    where_sqls = []
    order_by_sql = nil
    sql_params.each do |k,v|
      if k == :order_by
        order_by_sql = " ORDER BY #{v} "
      elsif v.nil? || v == :is_null
        where_sqls << "#{k} IS NULL"
      elsif v == :is_not_null
        where_sqls << "#{k} IS NOT NULL"
      else
        where_sqls << "#{k} = :#{k}"
        bindvars << v
      end
    end
    select_sql << "WHERE " << where_sqls.join(' AND ') unless where_sqls.empty?
    select_sql << order_by_sql if order_by_sql
  else
    raise ArgumentError, "Only String or Hash can be provided as SQL condition argument"
  end
  if first_or_all == :count
    @schema.select_one(select_sql, *bindvars)
  else
    @schema.select(first_or_all, select_sql, *bindvars)
  end
end

#update(params) ⇒ Object

Update table records using optional conditions. Example:

plsql.employees.update(:first_name => 'Second', :where => {:employee_id => 1})
# => UPDATE employees SET first_name = 'Second' WHERE employee_id = 1

Raises:

  • (ArgumentError) 


218
219
220
221
222
223
224
225
226
227
 
# File 'lib/plsql/table.rb', line 218

def update(params)
  raise ArgumentError, "Only Hash parameter can be passed to table update method" unless params.is_a?(Hash)
  where = params.delete(:where)
  
  table_proc = TableProcedure.new(@schema, self, :update)
  table_proc.add_set_arguments(params)
  table_proc.add_where_arguments(where) if where
  call = ProcedureCall.new(table_proc, table_proc.argument_values)
  call.exec
end