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.

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(db) ⇒ SequelInterface

Initialise the interface by passing it the Sequel DB object.



77
78
79
80
81
82
83
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
112
113
114
115
116
117
 
# File 'lib/pod4/sequel_interface.rb', line 77

def initialize(db)
  raise(ArgumentError, "Bad database") unless db.kind_of? Sequel::Database
  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?

  @sequel_version = Sequel.respond_to?(:qualify) ? 5 : 4
  @db             = db # reference to the db object
  @id_fld         = self.class.id_fld

  @table  = 
    if schema
      if @sequel_version == 5
        db[ Sequel[schema][table] ]
      else
        db[ "#{schema}__#{table}".to_sym ]
      end
    else
      db[table]
    end
      
  # Work around a problem with jdbc-postgresql where it throws an exception whenever it sees
  # the money type. This workaround actually allows us to return a BigDecimal, so it's better
  # than using postgres_pr when under jRuby!
  if @db.uri =~ /jdbc:postgresql/
    @db.conversion_procs[790] = ->(s){BigDecimal(s[1..-1]) rescue nil}
    c = Sequel::JDBC::Postgres::Dataset

    if @sequel_version >= 5
      # In Sequel 5 everything is frozen, so some hacking is required.
      # See https://github.com/jeremyevans/sequel/issues/1458
      vals = c::PG_SPECIFIC_TYPES + [Java::JavaSQL::Types::DOUBLE] 
      c.send(:remove_const, :PG_SPECIFIC_TYPES) # We can probably get away with just const_set, but.
      c.send(:const_set,    :PG_SPECIFIC_TYPES, vals.freeze)
    else
      c::PG_SPECIFIC_TYPES << Java::JavaSQL::Types::DOUBLE
    end
  end

rescue => e
  handle_error(e)
end

Instance Attribute Details

#id_fld ⇒ Object (readonly)

Returns the value of attribute id_fld.



27
28
29
 
# File 'lib/pod4/sequel_interface.rb', line 27

def id_fld
  @id_fld
end

Class Method Details

.id_fld ⇒ Object

Raises:



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

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

.schema ⇒ Object 



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

def schema; nil; end

.set_id_fld(idFld) ⇒ Object

Set the unique id field on the table.



62
63
64
 
# File 'lib/pod4/sequel_interface.rb', line 62

def set_id_fld(idFld)
  define_class_method(:id_fld) {idFld.to_s.to_sym}
end

.set_schema(schema) ⇒ Object

Use this to set the schema name (optional)



40
41
42
 
# File 'lib/pod4/sequel_interface.rb', line 40

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

.set_table(table) ⇒ Object

Set the table name.



50
51
52
 
# File 'lib/pod4/sequel_interface.rb', line 50

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

.table ⇒ Object

Raises:



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

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

Instance Method Details

#create(record) ⇒ Object

Record is a hash of field: value



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/pod4/sequel_interface.rb', line 150

def create(record)
  raise(ArgumentError, "Bad type for record parameter") \
    unless record.kind_of?(Hash) || record.kind_of?(Octothorpe)

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

  id = @table.insert( sanitise_hash(record.to_h) )

  # Sequel doesn't return the key unless it is an autoincrement; otherwise it turns a row
  # number regardless.  It probably doesn' t matter, but try to catch that anyway.
  # (bamf: If your non-incrementing key happens to be an integer, this won't work...)

  id_val = record[id_fld] || record[id_fld.to_s]

  if (id.kind_of?(Fixnum) || id.nil?) && id_val && !id_val.kind_of?(Fixnum)
    id_val
  else
    id
  end

rescue => e
  handle_error(e) 
end

#delete(id) ⇒ Object

ID is whatever you set in the interface using set_id_fld



213
214
215
216
217
218
219
220
221
222
223
224
 
# File 'lib/pod4/sequel_interface.rb', line 213

def delete(id)
  read_or_die(id)

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

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

#execute(sql) ⇒ Object

Bonus method: execute arbitrary SQL. Returns nil.



230
231
232
233
234
235
236
237
 
# File 'lib/pod4/sequel_interface.rb', line 230

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

  @db.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)”



250
251
252
253
254
255
256
257
258
 
# File 'lib/pod4/sequel_interface.rb', line 250

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}" }

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

#list(selection = nil) ⇒ Object

Selection is whatever Sequel’s ‘where` supports.



137
138
139
140
141
142
143
144
 
# File 'lib/pod4/sequel_interface.rb', line 137

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

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

#quoted_table ⇒ Object 



124
125
126
127
128
129
130
 
# File 'lib/pod4/sequel_interface.rb', line 124

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



178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/pod4/sequel_interface.rb', line 178

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( @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 



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

def schema; self.class.schema; end

#select(sql) ⇒ Object

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



265
266
267
268
269
270
271
272
 
# File 'lib/pod4/sequel_interface.rb', line 265

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

  @db[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.



281
282
283
284
285
286
287
288
 
# File 'lib/pod4/sequel_interface.rb', line 281

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

  @db.fetch(sql, *values).all
rescue => e
  handle_error(e)
end

#table ⇒ Object 



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

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.



196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/pod4/sequel_interface.rb', line 196

def update(id, record)
  read_or_die(id)

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

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