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:



38
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
 
# File 'lib/plsql/table.rb', line 38

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, _, 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:



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

def columns
  @columns
end

#schema_name ⇒ Object (readonly)

:nodoc:



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

def schema_name
  @schema_name
end

#table_name ⇒ Object (readonly)

:nodoc:



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

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)



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

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

#column_names ⇒ Object

list of table column names



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

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)



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

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



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

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)



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

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



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

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) 


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

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.



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
120
121
122
123
124
125
126
 
# File 'lib/plsql/table.rb', line 87

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) 


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

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