class Pod4::TdsInterface
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!
Attributes
Public Class Methods
# File lib/pod4/tds_interface.rb, line 47 def db raise Pod4Error, "You need to use set_db to set the database name" end
# 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
Initialise the interface by passing it a TinyTds connection hash OR a ConnectionPool
object.
# 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
# File lib/pod4/tds_interface.rb, line 58 def schema; nil; end
Use this to set the database name.
# File lib/pod4/tds_interface.rb, line 43 def set_db(db) define_class_method(:db) {db.to_s.to_sym} end
This sets the column that holds the unique id for the table
# 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
Use this to set the schema name (optional)
# File lib/pod4/tds_interface.rb, line 54 def set_schema(schema) define_class_method(:schema) {schema.to_s.to_sym} end
Use this to set the name of the table
# File lib/pod4/tds_interface.rb, line 63 def set_table(table) define_class_method(:table) {table.to_s.to_sym} end
# File lib/pod4/tds_interface.rb, line 67 def table raise Pod4Error, "You need to use set_table to set the table name" end
Public Instance Methods
Expose @connection for test purposes only
# File lib/pod4/tds_interface.rb, line 337 def _connection @connection end
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.
# 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
Record is a Hash or Octothorpe of field: value
# 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
# File lib/pod4/tds_interface.rb, line 114 def db; self.class.db; end
ID is whatever you set in the interface using set_id_fld
# 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
Wrapper for the data source library escape routine, which is all we can offer in terms of SQL injection protection. (Its not much.)
# File lib/pod4/tds_interface.rb, line 297 def escape(thing) client = ensure_connected thing.kind_of?(String) ? client.escape(thing) : thing end
Run SQL code on the server; return true or false for success or failure
# 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
# File lib/pod4/tds_interface.rb, line 118 def id_ai ; self.class.id_ai; end
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.
# 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
Open the connection to the database.
This is called by ConnectionPool
.
# 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
# File lib/pod4/tds_interface.rb, line 124 def quote_field(fld) "[#{super(fld, nil)}]" end
# File lib/pod4/tds_interface.rb, line 120 def quoted_table schema ? %Q|[#{schema}].[#{table}]| : %Q|[#{table}]| end
ID corresponds to whatever you set in set_id_fld
# 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
# File lib/pod4/tds_interface.rb, line 115 def schema; self.class.schema; end
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).
# 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
Override the sql_insert
method in sql_helper since our SQL is rather different
# 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
# File lib/pod4/tds_interface.rb, line 116 def table; self.class.table; end
ID is whatever you set in the interface using set_id_fld
record should be a Hash or Octothorpe.
# 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
Private Instance Methods
True if we are connected to a database
# File lib/pod4/tds_interface.rb, line 363 def connected?(conn) conn && conn.active? end
Return an open client connection from the Connection
Pool, or else raise an error
# File lib/pod4/tds_interface.rb, line 346 def ensure_connected client = @connection.client(self) # If this connection has expired somehow, try to get another one. unless connected?(client) @connection.drop(self) client = @connection.client(self) end fail "Bad Connection" unless connected?(client) client end
# File lib/pod4/tds_interface.rb, line 367 def handle_error(err, kaller=nil) kaller ||= caller[1..-1] Pod4.logger.error(__FILE__){ err.message } case err when ArgumentError, Pod4::Pod4Error, Pod4::CantContinue raise err.class, err.message, kaller when TinyTds::Error raise Pod4::DatabaseError, err.message, kaller else raise Pod4::Pod4Error, err.message, kaller end end
Overrride the quote routine in sql_helper.
-
TinyTDS doesn't cope with datetime
-
We might as well use it to escape strings, since that's the best we can do – although I suspect that it's just turning ' into '' and nothing else…
Pod4::SQLHelper#quote
# File lib/pod4/tds_interface.rb, line 395 def quote(fld) case fld when DateTime, Time %Q|'#{fld.to_s[0..-7]}'| when String, Symbol %Q|'#{escape fld.to_s}'| else super end end
# File lib/pod4/tds_interface.rb, line 407 def read_or_die(id) raise CantContinue, "'No record found with ID '#{id}'" if read(id).empty? end