Class: Pod4::SequelInterface

Inherits:
Interface show all
Defined in:
lib/pod4/sequel_interface.rb

Overview

Pod4 Interface for a Sequel table.

If your DB table is one-one with your model, you shouldn’t need to override anything.

Example:

class CustomerInterface < SwingShift::SequelInterface
  set_table  :customer
  set_id_fld :id
end

Data types: Sequel itself will translate to BigDecimal, Float, Integer, date, and datetime as appropriate – but it also depends on the underlying adapter. TinyTds maps dates to strings, for example.

Connections: Because the Sequel client – the “DB” object – has its own connection pool and does most of the heavy lifting for us, the only reason we use the Connection class is to defer creating the DB object until the first time we need it. Most of what Connection does, we don’t need, so our interactions with Connection are a little strange.

Constant Summary

Constants inherited from Interface

Interface::ACTIONS

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Metaxing

#define_class_method, #metaclass

Constructor Details

#initialize(arg) ⇒ SequelInterface

Initialise the interface by passing it the Sequel DB object. Or a Sequel connection string. Or a Pod4::Connection object.



84
85
86
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
 
# File 'lib/pod4/sequel_interface.rb', line 84

def initialize(arg)
  raise(Pod4Error, 'no call to set_table in the interface definition') if self.class.table.nil?
  raise(Pod4Error, 'no call to set_id_fld in the interface definition') if self.class.id_fld.nil?

  case arg
    when Sequel::Database
      @connection = Connection.new(interface: self.class)
      @connection.data_layer_options = arg 

    when Hash, String
      @connection = Connection.new(interface: self.class)
      @connection.data_layer_options = Sequel.connect(arg)

    when Connection
      @connection = arg

    else
      raise ArgumentError, "Bad argument"

  end

  @sequel_version = Sequel.respond_to?(:qualify) ? 5 : 4
  @id_fld         = self.class.id_fld
  @db             = nil

rescue => e
  handle_error(e)
end

Instance Attribute Details

#id_fld ⇒ Object (readonly)

Returns the value of attribute id_fld.



33
34
35
 
# File 'lib/pod4/sequel_interface.rb', line 33

def id_fld
  @id_fld
end

Class Method Details

.id_ai ⇒ Object

Raises:



74
75
76
 
# File 'lib/pod4/sequel_interface.rb', line 74

def id_ai
  raise Pod4Error, "You need to use set_id_fld to set the ID column name"
end

.id_fld ⇒ Object

Raises:



70
71
72
 
# File 'lib/pod4/sequel_interface.rb', line 70

def id_fld
  raise Pod4Error, "You need to use set_id_fld to set the ID column name"
end

.schema ⇒ Object 



48
 
# File 'lib/pod4/sequel_interface.rb', line 48

def schema; nil; end

.set_id_fld(idFld, opts = {}) ⇒ Object

Set the unique id field on the table.



64
65
66
67
68
 
# File 'lib/pod4/sequel_interface.rb', line 64

def set_id_fld(idFld, opts={})
  ai = opts.fetch(:autoincrement) { true }
  define_class_method(:id_fld) {idFld.to_s.to_sym}
  define_class_method(:id_ai)  {!!ai}
end

.set_schema(schema) ⇒ Object

Use this to set the schema name (optional)



44
45
46
 
# File 'lib/pod4/sequel_interface.rb', line 44

def set_schema(schema)
  define_class_method(:schema) {schema.to_s.to_sym}
end

.set_table(table) ⇒ Object

Set the table name.



53
54
55
 
# File 'lib/pod4/sequel_interface.rb', line 53

def set_table(table)
  define_class_method(:table) {table.to_s.to_sym}
end

.table ⇒ Object

Raises:



57
58
59
 
# File 'lib/pod4/sequel_interface.rb', line 57

def table
  raise Pod4Error, "You need to use set_table to set the table name"
end

Instance Method Details

#_connection ⇒ Object

Return the connection object, for testing purposes only



301
302
303
 
# File 'lib/pod4/sequel_interface.rb', line 301

def _connection
  @connection
end

#close_connection ⇒ Object

Called by @connection to “close” the DB object. Never called internally, and given the way Sequel works, we implement this as a dummy operation



294
295
296
 
# File 'lib/pod4/sequel_interface.rb', line 294

def close_connection
  self
end

#create(record) ⇒ Object

Record is a Hash or Octothorpe of field: value



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
# File 'lib/pod4/sequel_interface.rb', line 141

def create(record)
  raise Octothorpe::BadHash if record.nil?
  ot = Octothorpe.new(record)

  if id_ai
    ot = ot.reject{|k,_| k == id_fld}
  else
    raise(ArgumentError, "ID field missing from record") if ot[id_fld].nil?
  end

  Pod4.logger.debug(__FILE__) { "Creating #{self.class.table}: #{ot.inspect}" }

  id = db_table.insert( sanitise_hash(ot.to_h) )

  # Sequel doesn't return the key unless it is an autoincrement; otherwise it turns a row
  # number, which isn't much use to us. We always return the key.
  if id_ai
    id
  else
    ot[id_fld]
  end

rescue Octothorpe::BadHash
  raise ArgumentError, "Bad type for record parameter"
rescue
  handle_error $!
end

#delete(id) ⇒ Object

ID is whatever you set in the interface using set_id_fld



205
206
207
208
209
210
211
212
213
214
215
216
 
# File 'lib/pod4/sequel_interface.rb', line 205

def delete(id)
  read_or_die(id)

  Pod4.logger.debug(__FILE__) do
    "Deleting #{self.class.table} where #{@id_fld}=#{id}"
  end

  db_table.where(@id_fld => id).delete
  self
rescue => e
  handle_error(e)
end

#execute(sql) ⇒ Object

Bonus method: execute arbitrary SQL. Returns nil.



221
222
223
224
225
226
227
228
229
230
 
# File 'lib/pod4/sequel_interface.rb', line 221

def execute(sql)
  raise(ArgumentError, "Bad sql parameter") unless sql.kind_of?(String)
  Pod4.logger.debug(__FILE__) { "Execute SQL: #{sql}" }

  c = @connection.client(self)
  c.run(sql)

rescue => e
  handle_error(e)
end

#executep(sql, mode, *values) ⇒ Object

Bonus method: execute SQL as per execute(), but parameterised.

Use ? as a placeholder in the SQL mode is either :insert :update or :delete Please quote values for yourself, we don’t.

“update and delete should return the number of rows affected, and insert should return the autogenerated primary integer key for the row inserted (if any)”



242
243
244
245
246
247
248
249
250
 
# File 'lib/pod4/sequel_interface.rb', line 242

def executep(sql, mode, *values)
  raise(ArgumentError, "Bad sql parameter")    unless sql.kind_of?(String)
  raise(ArgumentError, "Bad mode parameter")   unless %i|insert delete update|.include?(mode)
  Pod4.logger.debug(__FILE__) { "Parameterised execute #{mode} SQL: #{sql}" }

  @connection.client(self)[sql, *values].send(mode)
rescue => e
  handle_error(e)
end

#id_ai ⇒ Object 



116
 
# File 'lib/pod4/sequel_interface.rb', line 116

def id_ai;  self.class.id_ai;  end

#list(selection = nil) ⇒ Object

Selection is whatever Sequel’s ‘where` supports.



129
130
131
132
133
134
135
136
 
# File 'lib/pod4/sequel_interface.rb', line 129

def list(selection=nil)
  sel = sanitise_hash(selection)
  Pod4.logger.debug(__FILE__) { "Listing #{self.class.table}: #{sel.inspect}" }

  (sel ? db_table.where(sel) : db_table.all).map {|x| Octothorpe.new(x) }
rescue => e
  handle_error(e)
end

#new_connection(options) ⇒ Object

Called by @connection to get the DB object. Never called internally. Given the way Sequel works – the data layer option object passed to Connection actually is the client object – we do something a bit weird here.



285
286
287
 
# File 'lib/pod4/sequel_interface.rb', line 285

def new_connection(options)
  options
end

#quoted_table ⇒ Object 



118
119
120
121
122
123
124
 
# File 'lib/pod4/sequel_interface.rb', line 118

def quoted_table
  if schema 
    %Q|#{db.quote_identifier schema}.#{db.quote_identifier table}|
  else
    db.quote_identifier(table)
  end
end

#read(id) ⇒ Object

ID corresponds to whatever you set in set_id_fld



172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/pod4/sequel_interface.rb', line 172

def read(id)
  raise(ArgumentError, "ID parameter is nil") if id.nil?
  Pod4.logger.debug(__FILE__) { "Reading #{self.class.table} where #{@id_fld}=#{id}" }

  Octothorpe.new( db_table[@id_fld => id] )

rescue Sequel::DatabaseError
  raise CantContinue, "Problem reading record. Is '#{id}' really an ID?"

rescue => e
  handle_error(e)
end

#schema ⇒ Object 



113
 
# File 'lib/pod4/sequel_interface.rb', line 113

def schema; self.class.schema; end

#select(sql) ⇒ Object

Bonus method: execute arbitrary SQL and return the resulting dataset as a Hash.



255
256
257
258
259
260
261
262
 
# File 'lib/pod4/sequel_interface.rb', line 255

def select(sql)
  raise(ArgumentError, "Bad sql parameter") unless sql.kind_of?(String)
  Pod4.logger.debug(__FILE__) { "Select SQL: #{sql}" }

  @connection.client(self)[sql].all
rescue => e
  handle_error(e)
end

#selectp(sql, *values) ⇒ Object

Bonus method: execute arbitrary SQL as per select(), but parameterised.

Use ? as a placeholder in the SQL Please quote values for yourself, we don’t.



270
271
272
273
274
275
276
277
278
 
# File 'lib/pod4/sequel_interface.rb', line 270

def selectp(sql, *values)
  raise(ArgumentError, "Bad sql parameter")    unless sql.kind_of?(String)
  Pod4.logger.debug(__FILE__) { "Parameterised select SQL: #{sql}" }

  @connection.client(self).fetch(sql, *values).all

rescue => e
  handle_error(e)
end

#table ⇒ Object 



114
 
# File 'lib/pod4/sequel_interface.rb', line 114

def table;  self.class.table;  end

#update(id, record) ⇒ Object

ID is whatever you set in the interface using set_id_fld record should be a Hash or Octothorpe.



189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/pod4/sequel_interface.rb', line 189

def update(id, record)
  read_or_die(id)

  Pod4.logger.debug(__FILE__) do 
    "Updating #{self.class.table} where #{@id_fld}=#{id}: #{record.inspect}"
  end

  db_table.where(@id_fld => id).update( sanitise_hash(record.to_h) )
  self
rescue => e
  handle_error(e)
end