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