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
 
# 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
    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 = r
    @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}"
    }
  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)



127
128
129
 
# File 'lib/plsql/table.rb', line 127

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

#column_names ⇒ Object

list of table column names



76
77
78
 
# File 'lib/plsql/table.rb', line 76

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)



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

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



226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
 
# File 'lib/plsql/table.rb', line 226

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)



138
139
140
 
# File 'lib/plsql/table.rb', line 138

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



160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/plsql/table.rb', line 160

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) 


189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/plsql/table.rb', line 189

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.



82
83
84
85
86
87
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
 
# File 'lib/plsql/table.rb', line 82

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?
        where_sqls << "#{k} IS 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) 


210
211
212
213
214
215
216
217
218
219
 
# File 'lib/plsql/table.rb', line 210

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