Class: JDBCHelper::TableWrapper

Inherits:
ObjectWrapper show all
Includes:
Enumerable
Defined in:
lib/jdbc-helper/wrapper/table_wrapper.rb

Overview

table.drop_table!

Instance Attribute Summary

Attributes inherited from ObjectWrapper

#connection, #name

Instance Method Summary collapse

Constructor Details

#initialize(connection, table_name) ⇒ TableWrapper

Returns a new instance of TableWrapper.



309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 309

def initialize connection, table_name
  super connection, table_name
  @update_method = :update
  @query_default = {}
  @query_where   = []
  @query_order   = nil
  @query_limit   = nil
  @query_select  = nil
  @pstmts = {
    :select => {},
    :insert => {},
    :delete => {},
    :count  => {},
    :update => {}
  }
  @fetch_size = nil
end

Instance Method Details

#batch ⇒ JDBCHelper::TableWrapper

Returns a new TableWrapper object whose subsequent inserts, updates, and deletes are added to batch for JDBC batch-execution. The actual execution is deferred until JDBCHelper::Connection#execute_batch method is called. Self is returned when batch is called more than once.

Returns:

Since:

  • 0.4.0



245
246
247
248
249
250
251
252
253
254
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 245

def batch
  if batch?
    self
  else
    # dup makes @pstmts to be shared
    obj = self.dup
    obj.instance_variable_set :@update_method, :add_batch
    obj
  end
end

#batch? ⇒ Boolean

Returns if the subsequent updates for this wrapper will be batched

Returns:

  • (Boolean)

Since:

  • 0.4.0



259
260
261
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 259

def batch?
  @update_method == :add_batch
end

#clear_batch(*types) ⇒ nil

Clear batched operations.

Parameters:

  • types (*Symbol) —

    Types of batched operations to clear. If not given, :insert, :update and :delete.

Returns:

  • (nil) 


267
268
269
270
271
272
273
274
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 267

def clear_batch *types
  types = [:insert, :update, :delete] if types.empty?
  types.each do |type|
    raise ArgumentError.new("Invalid type: #{type}") unless @pstmts.has_key?(type)
    @pstmts[type].values.each(&:clear_batch)
  end
  nil
end

#close ⇒ Object

Closes the prepared statements

Since:

  • 0.5.0



329
330
331
332
333
334
335
336
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 329

def close
  @pstmts.each do |typ, hash|
    hash.each do |sql, pstmt|
      pstmt.close if pstmt
    end
    @pstmts[typ] = {}
  end
end

#count(*where) ⇒ Fixnum

Retrieves the count of the table

Parameters:

  • where (List of Hash/String) —

    Filter conditions

Returns:

  • (Fixnum) —

    Count of the records.



52
53
54
55
56
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 52

def count *where
  sql, *binds = SQLHelper.count :table => name, :where => @query_where + where, :prepared => true
  pstmt = prepare :count, sql
  pstmt.query(*binds).to_a[0][0].to_i
end

#default(data_hash, &block) ⇒ JDBCHelper::TableWrapper

Returns a new TableWrapper object with default values, which will be applied to the subsequent inserts and updates.

Parameters:

  • data_hash (Hash) —

    Default values

Returns:

Raises:

  • (ArgumentError)

Since:

  • 0.4.5



204
205
206
207
208
209
210
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 204

def default data_hash, &block
  raise ArgumentError.new("Hash required") unless data_hash.kind_of? Hash

  obj = self.dup
  obj.instance_variable_set :@query_default, @query_default.merge(data_hash)
  ret obj, &block
end

#delete(*where) ⇒ Fixnum

Deletes records matching given condtion

Parameters:

  • where (List of Hash/String) —

    Delete filters

Returns:

  • (Fixnum) —

    Number of affected records



122
123
124
125
126
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 122

def delete *where
  sql, *binds = SQLHelper.delete(:table => name, :where => @query_where + where, :prepared => true)
  pstmt = prepare :delete, sql
  pstmt.send @update_method, *binds
end

#drop! ⇒ JDBCHelper::TableWrapper Also known as: drop_table!

Note:

This operation cannot be undone

Drops the table.

Returns:



140
141
142
143
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 140

def drop!
  @connection.update("drop table #{name}")
  self
end

#each(&block) ⇒ JDBCHelper::Connection::ResultSet

Executes a select SQL for the table and returns an Enumerable object, or yields each row if block is given.

Returns:

Since:

  • 0.4.0



227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 227

def each &block
  sql, *binds = SQLHelper.select(
      :prepared => true,
      :table    => name,
      :project  => @query_select,
      :where    => @query_where,
      :order    => @query_order,
      :limit    => @query_limit)
  pstmt = prepare :select, sql
  pstmt.enumerate(*binds, &block)
end

#empty?(*where) ⇒ boolean

Sees if the table is empty

Parameters:

  • Filter (Hash/String) —

    conditions

Returns:

  • (boolean) 


61
62
63
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 61

def empty? *where
  count(*where) == 0
end

#execute_batch(*types) ⇒ Hash

Execute batched operations. TableWrapper uses multiple PreparedStatements and each of them may have its own homogeneous batched commands. It is thus not possible for TableWrapper to precisely serialize all the commands when interleaved. What you can do here is to specify the types of commands (:insert, :update, and :delete) in the order of execution. The default is to execute deletes first, then updates, and finally inserts. You can also execute a subset of the three types.

Parameters:

  • types (*Symbol) —

    Types of batched operations to execute in order. If not given, :delete, :insert and :update.

Returns:

  • (Hash) —

    Sum of all update counts indexed by operation type



285
286
287
288
289
290
291
292
293
294
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 285

def execute_batch *types
  types = [:delete, :insert, :update] if types.empty?

  Hash.new { 0 }.tap { |cnts|
    types.each do |type|
      raise ArgumentError.new("Invalid type: #{type}") unless @pstmts.has_key?(type)
      cnts[type] += @pstmts[type].values.map(&:execute_batch).select { |e| e > 0 }.inject(:+) || 0
    end
  }
end

#fetch_size(fsz, &block) ⇒ JDBCHelper::TableWrapper

Returns a new TableWrapper object with the given fetch size. If a block is given, executes the select statement and yields each row to the block.

Parameters:

  • fsz (Fixnum) —

    Fetch size

Returns:

Since:

  • 0.7.7



217
218
219
220
221
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 217

def fetch_size fsz, &block
  obj = self.dup
  obj.instance_variable_set :@fetch_size, fsz
  ret obj, &block
end

#insert(data_hash = {}) ⇒ Fixnum

Inserts a record into the table with the given hash

Parameters:

  • data_hash (Hash) (defaults to: {}) —

    Column values in Hash

Returns:

  • (Fixnum) —

    Number of affected records



68
69
70
71
72
73
74
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 68

def insert data_hash = {}
  sql, *binds = SQLHelper.insert :table => name,
                                 :data => @query_default.merge(data_hash),
                                 :prepared => true
  pstmt = prepare :insert, sql
  pstmt.send @update_method, *binds
end

#insert_ignore(data_hash = {}) ⇒ Fixnum

Note:

This is not SQL standard. Only works if the database supports insert ignore syntax.

Inserts a record into the table with the given hash. Skip insertion when duplicate record is found.

Parameters:

  • data_hash (Hash) (defaults to: {}) —

    Column values in Hash

Returns:

  • (Fixnum) —

    Number of affected records



81
82
83
84
85
86
87
88
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 81

def insert_ignore data_hash = {}
  sql, *binds = SQLHelper.insert_ignore :table => name,
                                        :data => @query_default.merge(data_hash),
                                        :prepared => true
  pstmt = prepare :insert, sql
  pstmt.set_fetch_size @fetch_size if @fetch_size
  pstmt.send @update_method, *binds
end

#inspect ⇒ Object 



344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 344

def inspect
  {
    :conn    => @connection,
    :name    => name,
    :sqls    => @pstmts.values.map(&:keys).flatten,
    :where   => @query_where,
    :default => @query_default,
    :order   => @query_order,
    :limit   => @query_limit,
    :batch?  => batch?
  }.inspect
end

#limit(offset, limit) ⇒ JDBCHelper::TableWrapper #limit(limit) ⇒ JDBCHelper::TableWrapper

Returns:

Since:

  • 0.8.0



180
181
182
183
184
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 180

def limit *args, &block
  obj = self.dup
  obj.instance_variable_set :@query_limit, args
  ret obj, &block
end

#order(*criteria, &block) ⇒ JDBCHelper::TableWrapper

Returns a new TableWrapper object which can be used to execute a select statement for the table with the given sorting criteria. If a block is given, executes the select statement and yields each row to the block.

Parameters:

  • criteria (*String/*Symbol) —

    Sorting criteria

Returns:

Raises:

  • (ArgumentError)

Since:

  • 0.4.0



192
193
194
195
196
197
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 192

def order *criteria, &block
  raise ArgumentError.new("Wrong number of arguments") if criteria.empty?
  obj = self.dup
  obj.instance_variable_set :@query_order, criteria
  ret obj, &block
end

#prepared_statements ⇒ Hash

Returns Prepared statements for this wrapper.

Returns:

  • (Hash) —

    Prepared statements for this wrapper

Since:

  • 0.5.0



340
341
342
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 340

def prepared_statements
  @pstmts
end

#replace(data_hash = {}) ⇒ Fixnum

Note:

This is not SQL standard. Only works if the database supports replace syntax.

Replaces a record in the table with the new one with the same unique key.

Parameters:

  • data_hash (Hash) (defaults to: {}) —

    Column values in Hash

Returns:

  • (Fixnum) —

    Number of affected records



94
95
96
97
98
99
100
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 94

def replace data_hash = {}
  sql, *binds = SQLHelper.replace :table => name,
                                  :data => @query_default.merge(data_hash),
                                  :prepared => true
  pstmt = prepare :insert, sql
  pstmt.send @update_method, *binds
end

#select(*fields, &block) ⇒ JDBCHelper::TableWrapper Also known as: project

Returns a new TableWrapper object which can be used to execute a select statement for the table selecting only the specified fields. If a block is given, executes the select statement and yields each row to the block.

Parameters:

  • fields (*String/*Symbol) —

    List of fields to select

Returns:

Since:

  • 0.4.0



155
156
157
158
159
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 155

def select *fields, &block
  obj = self.dup
  obj.instance_variable_set :@query_select, fields unless fields.empty?
  ret obj, &block
end

#sql ⇒ String

Returns the select SQL for this wrapper object

Returns:

  • (String) —

    Select SQL

Since:

  • 0.4.0



299
300
301
302
303
304
305
306
307
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 299

def sql
  SQLHelper.select(
    :prepared => false,
    :table    => name,
    :project  => @query_select,
    :where    => @query_where,
    :limit    => @query_limit,
    :order    => @query_order)
end

#truncate! ⇒ JDBCHelper::TableWrapper Also known as: truncate_table!

Note:

This operation cannot be undone

Empties the table.

Returns:



131
132
133
134
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 131

def truncate!
  @connection.update("truncate table #{name}")
  self
end

#update(data_hash_with_where = {}) ⇒ Fixnum

Executes update with the given hash. :where element of the hash is taken out to generate where clause of the update SQL.

Parameters:

  • data_hash_with_where (Hash) (defaults to: {}) —

    Column values in Hash. :where element of the given hash can (usually should) point to another Hash representing update filters.

Returns:

  • (Fixnum) —

    Number of affected records



107
108
109
110
111
112
113
114
115
116
117
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 107

def update data_hash_with_where = {}
  where_ext  = data_hash_with_where.delete(:where)
  where_ext  = [where_ext] unless where_ext.is_a? Array
  sql, *binds = SQLHelper.update(
                  :prepared => true,
                  :table => name,
                  :data => @query_default.merge(data_hash_with_where),
                  :where => @query_where + where_ext.compact)
  pstmt = prepare :update, sql
  pstmt.send @update_method, *binds
end

#where(*conditions, &block) ⇒ JDBCHelper::TableWrapper

Returns a new TableWrapper object which can be used to execute a select statement for the table with the specified filter conditions. If a block is given, executes the select statement and yields each row to the block.

Parameters:

  • conditions (List of Hash/String) —

    Filter conditions

Returns:

Raises:

  • (ArgumentError)

Since:

  • 0.4.0



168
169
170
171
172
173
174
 
# File 'lib/jdbc-helper/wrapper/table_wrapper.rb', line 168

def where *conditions, &block
  raise ArgumentError.new("Wrong number of arguments") if conditions.empty?

  obj = self.dup
  obj.instance_variable_set :@query_where, @query_where + conditions
  ret obj, &block
end