Class: Pod4::TdsInterface

Inherits:
Interface show all
Includes:
SQLHelper
Defined in:
lib/pod4/tds_interface.rb

Overview

Pod4 Interface for requests on a SQL table via TinyTds.

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

Example:

class CustomerInterface < SwingShift::TdsInterface
  set_db     :fred
  set_table  :customer
  set_id_fld :id
end

Note: TinyTDS does not appear to support parameterised queries!

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(args) ⇒ TdsInterface

Initialise the interface by passing it a TinyTds connection hash OR a ConnectionPool object.



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/pod4/tds_interface.rb', line 89

def initialize(args)
  case args
    when Hash
      @connection = ConnectionPool.new(interface: self.class)
      @connection.data_layer_options = args

    when ConnectionPool
      @connection = args

    else
      raise ArgumentError, "Bad Argument"
  end

  sc = self.class
  raise(Pod4Error, 'no call to set_db in the interface definition')     if sc.db.nil?
  raise(Pod4Error, 'no call to set_table in the interface definition')  if sc.table.nil?
  raise(Pod4Error, 'no call to set_id_fld in the interface definition') if sc.id_fld.nil?

  TinyTds::Client.default_query_options[:as] = :hash
  TinyTds::Client.default_query_options[:symbolize_keys] = true

rescue => e
  handle_error(e)
end

Instance Attribute Details

#id_fld ⇒ Object (readonly)

Returns the value of attribute id_fld.



32
33
34
 
# File 'lib/pod4/tds_interface.rb', line 32

def id_fld
  @id_fld
end

Class Method Details

.db ⇒ Object

Raises:



47
48
49
 
# File 'lib/pod4/tds_interface.rb', line 47

def db 
  raise Pod4Error, "You need to use set_db to set the database name"
end

.id_fld ⇒ Object

Raises:



80
81
82
 
# File 'lib/pod4/tds_interface.rb', line 80

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

.schema ⇒ Object 



58
 
# File 'lib/pod4/tds_interface.rb', line 58

def schema; nil; end

.set_db(db) ⇒ Object

Use this to set the database name.



43
44
45
 
# File 'lib/pod4/tds_interface.rb', line 43

def set_db(db)
  define_class_method(:db) {db.to_s.to_sym}
end

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

This sets the column that holds the unique id for the table



74
75
76
77
78
 
# File 'lib/pod4/tds_interface.rb', line 74

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)



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

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

.set_table(table) ⇒ Object

Use this to set the name of the table



63
64
65
 
# File 'lib/pod4/tds_interface.rb', line 63

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

.table ⇒ Object

Raises:



67
68
69
 
# File 'lib/pod4/tds_interface.rb', line 67

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

Instance Method Details

#_connection ⇒ Object

Expose @connection for test purposes only



337
338
339
 
# File 'lib/pod4/tds_interface.rb', line 337

def _connection
  @connection
end

#close_connection(conn) ⇒ Object

Close the connection to the database.

We don’t actually use this. Theoretically it would be called by ConnectionPool, but we don’t. I’ve left it in for completeness.



326
327
328
329
330
331
332
 
# File 'lib/pod4/tds_interface.rb', line 326

def close_connection(conn)
  Pod4.logger.info(__FILE__){ "Closing connection to DB" }
  conn.close unless conn.nil?

rescue => e
  handle_error(e)
end

#create(record) ⇒ Object

Record is a Hash or Octothorpe of field: value



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/pod4/tds_interface.rb', line 146

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

  sql, vals = sql_insert(ot)
  x = select sql_subst(sql, *vals.map{|v| quote v})
  x.first[id_fld]

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

#db ⇒ Object 



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

def db;     self.class.db;     end

#delete(id) ⇒ Object

ID is whatever you set in the interface using set_id_fld



210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/pod4/tds_interface.rb', line 210

def delete(id)
  read_or_die(id)

  sql, vals = sql_delete(id_fld => id)
  execute sql_subst(sql, *vals.map{|v| quote v})

  self

rescue => e
  handle_error(e)
end

#escape(thing) ⇒ Object

Wrapper for the data source library escape routine, which is all we can offer in terms of SQL injection protection. (Its not much.)



297
298
299
300
 
# File 'lib/pod4/tds_interface.rb', line 297

def escape(thing)
  client = ensure_connected
  thing.kind_of?(String) ? client.escape(thing) : thing
end

#execute(sql) ⇒ Object

Run SQL code on the server; return true or false for success or failure



278
279
280
281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/pod4/tds_interface.rb', line 278

def execute(sql)
  raise(ArgumentError, "Bad sql parameter") unless sql.kind_of?(String)

  client = ensure_connected

  Pod4.logger.debug(__FILE__){ "execute: #{sql}" }
  r = client.execute(sql)

  r.do
  r

rescue => e
  handle_error(e)
end

#id_ai ⇒ Object 



118
 
# File 'lib/pod4/tds_interface.rb', line 118

def id_ai ; self.class.id_ai;  end

#list(selection = nil) ⇒ Object

Selection is a hash or something like it: keys should be field names. We return any records where the given fields equal the given values.



132
133
134
135
136
137
138
139
140
141
 
# File 'lib/pod4/tds_interface.rb', line 132

def list(selection=nil)
  raise(Pod4::DatabaseError, 'selection parameter is not a hash') \
    unless selection.nil? || selection.respond_to?(:keys)

  sql, vals = sql_select(nil, selection)
  select( sql_subst(sql, *vals.map{|v| quote v}) ) {|r| Octothorpe.new(r) }

rescue => e
  handle_error(e)
end

#new_connection(params) ⇒ Object

Open the connection to the database.

This is called by ConnectionPool.



307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/pod4/tds_interface.rb', line 307

def new_connection(params)
  Pod4.logger.info(__FILE__){ "Connecting to DB" }
  client = TinyTds::Client.new(params)
  raise "Bad Connection" unless client.active?

  client.execute("use [#{self.class.db}]").do

  client

rescue => e
  handle_error(e)
end

#quote_field(fld) ⇒ Object 



124
125
126
 
# File 'lib/pod4/tds_interface.rb', line 124

def quote_field(fld)
  "[#{super(fld, nil)}]"
end

#quoted_table ⇒ Object 



120
121
122
 
# File 'lib/pod4/tds_interface.rb', line 120

def quoted_table
  schema ? %Q|[#{schema}].[#{table}]| : %Q|[#{table}]|
end

#read(id) ⇒ Object

ID corresponds to whatever you set in set_id_fld



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/pod4/tds_interface.rb', line 169

def read(id)
  raise(ArgumentError, "ID parameter is nil") if id.nil?

  sql, vals = sql_select(nil, id_fld => id) 
  rows = select sql_subst(sql, *vals.map{|v| quote v}) 
  Octothorpe.new(rows.first)

rescue => e
  # select already wrapped any error in a Pod4::DatabaseError, but in this case we want to try
  # to catch something. Ruby 2.0 doesn't define Exception.cause, but if it doesn't, we do in
  # Pod4Error, so. (Side note: TinyTds' error class structure is a bit poor...)
  raise CantContinue, "Problem reading record. Is '#{id}' really an ID?" \
    if e.respond_to?(:cause) \
    && e.cause.class   == TinyTds::Error \
    && e.cause.message =~ /conversion failed/i

  handle_error(e)
end

#schema ⇒ Object 



115
 
# File 'lib/pod4/tds_interface.rb', line 115

def schema; self.class.schema; end

#select(sql) ⇒ Object

Run SQL code on the server. Return the results.

Will return an array of records, or you can use it in block mode, like this:

select("select * from customer") do |r|
  # r is a single record
end

The returned results will be an array of hashes (or if you passed a block, of whatever you returned from the block).



249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
 
# File 'lib/pod4/tds_interface.rb', line 249

def select(sql)
  raise(ArgumentError, "Bad sql parameter") unless sql.kind_of?(String)

  client = ensure_connected

  Pod4.logger.debug(__FILE__){ "select: #{sql}" }
  query = client.execute(sql)

  rows = []
  query.each do |r| 

    if block_given? 
      rows << yield(r)
    else
      rows << r
    end

  end

  query.cancel 
  rows

rescue => e
  handle_error(e)
end

#sql_insert(record) ⇒ Object

Override the sql_insert method in sql_helper since our SQL is rather different



225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/pod4/tds_interface.rb', line 225

def sql_insert(record)
  flds, vals = parse_fldsvalues(record)
  ph = vals.map{|x| placeholder }

  sql = %Q|insert into #{quoted_table}
             ( #{flds.join ','} )
             output inserted.#{quote_field id_fld}
             values( #{ph.join ','} );|

  [sql, vals]
end

#table ⇒ Object 



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

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.



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

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

  read_or_die(id)

  sql, vals = sql_update(record, id_fld => id)
  execute sql_subst(sql, *vals.map{|v| quote v})

  self

rescue => e
  handle_error(e)
end