Class: PLSQL::Table
- Inherits:
-
Object
- Object
- PLSQL::Table
- Extended by:
- TableClassMethods
- Defined in:
- lib/plsql/table.rb
Direct Known Subclasses
Defined Under Namespace
Classes: TableProcedure
Instance Attribute Summary collapse
-
#columns ⇒ Object
readonly
:nodoc:.
-
#schema_name ⇒ Object
readonly
:nodoc:.
-
#table_name ⇒ Object
readonly
:nodoc:.
Instance Method Summary collapse
-
#all(sql = '', *bindvars) ⇒ Object
Select all table records using optional conditions.
-
#column_names ⇒ Object
list of table column names.
-
#count(sql = '', *bindvars) ⇒ Object
Count table records using optional conditions.
-
#delete(sql_params = '', *bindvars) ⇒ Object
Delete table records using optional conditions.
-
#first(sql = '', *bindvars) ⇒ Object
Select first table record using optional conditions.
-
#initialize(schema, table, override_schema_name = nil) ⇒ Table
constructor
:nodoc:.
-
#insert(record) ⇒ Object
Insert record or records in table.
-
#insert_values(*args) ⇒ Object
Insert record or records in table using array of values.
-
#select(first_or_all, sql_params = '', *bindvars) ⇒ Object
General select method with :first, :all or :count as first parameter.
-
#update(params) ⇒ Object
Update table records using optional conditions.
Methods included from TableClassMethods
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')
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
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 |