class PLSQL::Table
Public Instance Methods
all(sql='', *bindvars)
click to toggle source
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)
# File lib/plsql/table.rb, line 135 def all(sql='', *bindvars) select(:all, sql, *bindvars) end
column_names()
click to toggle source
list of table column names
# File lib/plsql/table.rb, line 82 def column_names @column_names ||= @columns.keys.sort_by{|k| columns[k][:position]} end
count(sql='', *bindvars)
click to toggle source
Count table records using optional conditions. Examples:
plsql.employees.count plsql.employees.count("WHERE employee_id > :employee_id", 5)
# File lib/plsql/table.rb, line 155 def count(sql='', *bindvars) select(:count, sql, *bindvars) end
delete(sql_params='', *bindvars)
click to toggle source
Delete table records using optional conditions. Example:
plsql.employees.delete(:employee_id => 1) # => DELETE FROM employees WHERE employee_id = 1
# 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)
click to toggle source
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)
# File lib/plsql/table.rb, line 146 def first(sql='', *bindvars) select(:first, sql, *bindvars) end
insert(record)
click to toggle source
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
# 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)
click to toggle source
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')
# 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)
click to toggle source
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.
# 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)
click to toggle source
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
# 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