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
 
# 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?

  @db     = db # referemce to the db object
  @table  = db[schema ? "#{schema}__#{table}".to_sym : table]
  @id_fld = self.class.id_fld

  # 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.new s[1..-1] rescue nil}
    Sequel::JDBC::Postgres::Dataset::PG_SPECIFIC_TYPES << Java::JavaSQL::Types::DOUBLE
  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



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/pod4/sequel_interface.rb', line 129

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



192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/pod4/sequel_interface.rb', line 192

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.



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

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)”



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

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.



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

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 



103
104
105
106
107
108
109
 
# File 'lib/pod4/sequel_interface.rb', line 103

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



157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/pod4/sequel_interface.rb', line 157

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 



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

def schema; self.class.schema; end

#select(sql) ⇒ Object

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



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

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.



260
261
262
263
264
265
266
267
 
# File 'lib/pod4/sequel_interface.rb', line 260

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 



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

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.



175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/pod4/sequel_interface.rb', line 175

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