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(connectHash, testClient = nil) ⇒ TdsInterface

Initialise the interface by passing it a TinyTds connection hash.# For testing ONLY you can also pass an object which pretends to be a TinyTds client, in which case the hash is pretty much ignored.



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

def initialize(connectHash, testClient=nil)
  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?
  raise(ArgumentError, 'invalid connection hash') unless connectHash.kind_of?(Hash)

  @connect_hash = connectHash.dup
  @test_client  = testClient 
  @client       = 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.



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

def id_fld
  @id_fld
end

Class Method Details

.db ⇒ Object

Raises:



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

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

.id_fld ⇒ Object

Raises:



82
83
84
 
# File 'lib/pod4/tds_interface.rb', line 82

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

.schema ⇒ Object 



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

def schema; nil; end

.set_db(db) ⇒ Object

Use this to set the database name.



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

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

.set_id_fld(idFld) ⇒ Object

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



78
79
80
 
# File 'lib/pod4/tds_interface.rb', line 78

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)



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

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



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

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

.table ⇒ Object

Raises:



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

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



148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/pod4/tds_interface.rb', line 148

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

  sql, vals = sql_insert(record)

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

rescue => e
  handle_error(e) 
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



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

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



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

def escape(thing)
  open unless 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



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

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

  open unless connected?

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

  r.do
  r

rescue => e
  handle_error(e)
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
142
 
# 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

#quote_field(fld) ⇒ Object 



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

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

#quoted_table ⇒ Object 



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

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

#read(id) ⇒ Object

ID corresponds to whatever you set in set_id_fld



165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/pod4/tds_interface.rb', line 165

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



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

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

  open unless 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.



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

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