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

id_fld[R]

Public Class Methods

db() click to toggle source
# 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() click to toggle source
# 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
new(args) click to toggle source

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
schema() click to toggle source
# File lib/pod4/tds_interface.rb, line 58
def schema; nil; end
set_db(db) click to toggle source

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
set_id_fld(idFld, opts={}) click to toggle source

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
set_schema(schema) click to toggle source

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
set_table(table) click to toggle source

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
table() click to toggle source
# 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

_connection() click to toggle source

Expose @connection for test purposes only

# File lib/pod4/tds_interface.rb, line 337
def _connection
  @connection
end
close_connection(conn) click to toggle source

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
create(record) click to toggle source

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
db() click to toggle source
# File lib/pod4/tds_interface.rb, line 114
def db;     self.class.db;     end
delete(id) click to toggle source

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
escape(thing) click to toggle source

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
execute(sql) click to toggle source

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
id_ai() click to toggle source
# File lib/pod4/tds_interface.rb, line 118
def id_ai ; self.class.id_ai;  end
list(selection=nil) click to toggle source

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
new_connection(params) click to toggle source

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
quote_field(fld) click to toggle source
# File lib/pod4/tds_interface.rb, line 124
def quote_field(fld)
  "[#{super(fld, nil)}]"
end
quoted_table() click to toggle source
# File lib/pod4/tds_interface.rb, line 120
def quoted_table
  schema ? %Q|[#{schema}].[#{table}]| : %Q|[#{table}]|
end
read(id) click to toggle source

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
schema() click to toggle source
# File lib/pod4/tds_interface.rb, line 115
def schema; self.class.schema; end
select(sql) { |r| ... } click to toggle source

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
sql_insert(record) click to toggle source

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
table() click to toggle source
# File lib/pod4/tds_interface.rb, line 116
def table;  self.class.table;  end
update(id, record) click to toggle source

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

connected?(conn) click to toggle source

True if we are connected to a database

# File lib/pod4/tds_interface.rb, line 363
def connected?(conn)
  conn && conn.active?
end
ensure_connected() click to toggle source

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
handle_error(err, kaller=nil) click to toggle source
# 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
quote(fld) click to toggle source

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…

Calls superclass method 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
read_or_die(id) click to toggle source
# 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