module Sequel::ADO::Access::DatabaseMethods

Constants

DECIMAL_TYPE_RE
LAST_INSERT_ID

Public Instance Methods

alter_table(name, *) click to toggle source

Remove cached schema after altering a table, since otherwise it can be cached incorrectly in the rename column case.

Calls superclass method
# File lib/sequel/adapters/ado/access.rb, line 96
def alter_table(name, *)
  super
  remove_cached_schema(name)
  nil
end
disconnect_connection(conn) click to toggle source

Access doesn't let you disconnect if inside a transaction, so try rolling back an existing transaction first.

Calls superclass method
# File lib/sequel/adapters/ado/access.rb, line 104
def disconnect_connection(conn)
  conn.RollbackTrans rescue nil
  super
end
execute_insert(sql, opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 109
def execute_insert(sql, opts=OPTS)
  synchronize(opts[:server]) do |conn|
    begin
      log_yield(sql){conn.Execute(sql)}
      res = log_yield(LAST_INSERT_ID){conn.Execute(LAST_INSERT_ID)}
      res.getRows.transpose.each{|r| return r.shift}
    rescue ::WIN32OLERuntimeError => e
      raise_error(e)
    end
  end
  nil
end
foreign_key_list(table, opts=OPTS) click to toggle source

Note OpenSchema returns compound foreign key relationships as multiple rows

# File lib/sequel/adapters/ado/access.rb, line 148
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  fks = ado_schema_foreign_keys(table).inject({}) do |memo, fk|
    name = m.call(fk['FK_NAME'])
    specs = memo[name] ||= {
      :columns => [],
      :table   => m.call(fk['PK_TABLE_NAME']),
      :key     => [],
      :deferrable => fk['DEFERRABILITY'],
      :name    => name,
      :on_delete => fk['DELETE_RULE'],
      :on_update => fk['UPDATE_RULE']
    }
    specs[:columns] << m.call(fk['FK_COLUMN_NAME'])
    specs[:key]     << m.call(fk['PK_COLUMN_NAME'])
    memo
  end
  fks.values
end
indexes(table_name,opts=OPTS) click to toggle source

Note OpenSchema returns compound indexes as multiple rows

# File lib/sequel/adapters/ado/access.rb, line 133
def indexes(table_name,opts=OPTS)
  m = output_identifier_meth
  idxs = ado_schema_indexes(table_name).inject({}) do |memo, idx|
    unless idx["PRIMARY_KEY"]
      index = memo[m.call(idx["INDEX_NAME"])] ||= {
        :columns=>[], :unique=>idx["UNIQUE"]
      }
      index[:columns] << m.call(idx["COLUMN_NAME"])
    end
    memo
  end
  idxs
end
tables(opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 122
def tables(opts=OPTS)
  m = output_identifier_meth
  ado_schema_tables.map {|tbl| m.call(tbl['TABLE_NAME'])}
end
views(opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 127
def views(opts=OPTS)
  m = output_identifier_meth
  ado_schema_views.map {|tbl| m.call(tbl['TABLE_NAME'])}
end

Private Instance Methods

ado_schema_columns(table_name) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 279
def ado_schema_columns(table_name)
  rows=[]
  fetch_ado_schema(:columns, [nil,nil,table_name.to_s,nil]) do |row| 
    rows << AdoSchema::Column.new(row)
  end
  rows.sort!{|a,b| a["ORDINAL_POSITION"] <=> b["ORDINAL_POSITION"]}
end
ado_schema_foreign_keys(table_name) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 287
def ado_schema_foreign_keys(table_name)
  rows=[]
  fetch_ado_schema(:foreign_keys, [nil,nil,nil,nil,nil,table_name.to_s]) do |row| 
    rows << row
  end
  rows.sort!{|a,b| a["ORDINAL"] <=> b["ORDINAL"]}
end
ado_schema_indexes(table_name) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 271
def ado_schema_indexes(table_name)
  rows=[]
  fetch_ado_schema(:indexes, [nil,nil,nil,nil,table_name.to_s]) do |row|
    rows << row
  end
  rows
end
ado_schema_tables() click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 255
def ado_schema_tables
  rows=[]
  fetch_ado_schema(:tables, [nil,nil,nil,'TABLE']) do |row|
    rows << row
  end
  rows
end
ado_schema_views() click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 263
def ado_schema_views
  rows=[]
  fetch_ado_schema(:views, [nil,nil,nil]) do |row|
    rows << row
  end
  rows
end
alter_table_sql(table, op) click to toggle source

Emulate rename_column by adding the column, copying data from the old column, and dropping the old column.

Calls superclass method
# File lib/sequel/adapters/ado/access.rb, line 172
def alter_table_sql(table, op)
  case op[:op]
  when :rename_column
    unless sch = op[:schema]
      raise(Error, "can't find existing schema entry for #{op[:name]}") unless sch = op[:schema] || schema(table).find{|c| c.first == op[:name]}
      sch = sch.last
    end
    [
      alter_table_sql(table, :op=>:add_column, :name=>op[:new_name], :default=>sch[:ruby_default], :type=>sch[:db_type], :null=>sch[:allow_null]),
      from(table).update_sql(op[:new_name]=>op[:name]),
      alter_table_sql(table, :op=>:drop_column, :name=>op[:name])
    ]
  when :set_column_null, :set_column_default
    raise(Error, "can't find existing schema entry for #{op[:name]}") unless sch = op[:schema] || schema(table).find{|c| c.first == op[:name]}
    sch = sch.last

    sch = if op[:op] == :set_column_null
      sch.merge(:allow_null=>op[:null])
    else
      sch.merge(:ruby_default=>op[:default])
    end

    [
      alter_table_sql(table, :op=>:rename_column, :name=>op[:name], :new_name=>:sequel_access_backup_column, :schema=>sch),
      alter_table_sql(table, :op=>:rename_column, :new_name=>op[:name], :name=>:sequel_access_backup_column, :schema=>sch)
    ]
  else
    super
  end
end
begin_transaction(conn, opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 203
def begin_transaction(conn, opts=OPTS)
  log_yield('Transaction.begin'){conn.BeginTrans}
end
commit_transaction(conn, opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 207
def commit_transaction(conn, opts=OPTS)
  log_yield('Transaction.commit'){conn.CommitTrans}
end
execute_open_ado_schema(type, criteria=[]) { |r| ... } click to toggle source

This is like execute() in that it yields an ADO RecordSet, except instead of an SQL interface there's this OpenSchema call cf. msdn.microsoft.com/en-us/library/ee275721(v=bts.10)

# File lib/sequel/adapters/ado/access.rb, line 310
def execute_open_ado_schema(type, criteria=[])
  ado_schema = AdoSchema.new(type, criteria)
  synchronize(opts[:server]) do |conn|
    begin
      r = log_yield("OpenSchema #{type.inspect}, #{criteria.inspect}") { 
        if ado_schema.criteria.empty?
          conn.OpenSchema(ado_schema.type) 
        else
          conn.OpenSchema(ado_schema.type, ado_schema.criteria) 
        end
      }
      yield(r) if block_given?
    rescue ::WIN32OLERuntimeError => e
      raise_error(e)
    end
  end
  nil
end
fetch_ado_schema(type, criteria=[]) { |row| ... } click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 295
def fetch_ado_schema(type, criteria=[])
  execute_open_ado_schema(type, criteria) do |s|
    cols = s.Fields.extend(Enumerable).map {|c| c.Name}
    s.getRows.transpose.each do |r|
      row = {}
      cols.each{|c| row[c] = r.shift}
      yield row
    end unless s.eof
  end
end
rollback_transaction(conn, opts=OPTS) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 211
def rollback_transaction(conn, opts=OPTS)
  log_yield('Transaction.rollback'){conn.RollbackTrans}
end
schema_column_type(db_type) click to toggle source
Calls superclass method
# File lib/sequel/adapters/ado/access.rb, line 215
def schema_column_type(db_type)
  case db_type.downcase
  when 'bit'
    :boolean
  when 'byte', 'guid'
    :integer
  when 'image'
    :blob
  else
    super
  end
end
schema_parse_table(table_name, opts) click to toggle source
# File lib/sequel/adapters/ado/access.rb, line 228
def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])
  m2 = input_identifier_meth(opts[:dataset])
  tn = m2.call(table_name.to_s)
  idxs = ado_schema_indexes(tn)
  ado_schema_columns(tn).map {|row|
    specs = { 
      :allow_null => row.allow_null,
      :db_type => row.db_type,
      :default => row.default,
      :primary_key => !!idxs.find {|idx| 
                        idx["COLUMN_NAME"] == row["COLUMN_NAME"] &&
                        idx["PRIMARY_KEY"]
                      },
      :type =>  if row.db_type =~ DECIMAL_TYPE_RE && row.scale == 0
                  :integer
                else
                  schema_column_type(row.db_type)
                end,
      :ado_type => row["DATA_TYPE"]
    }
    specs[:default] = nil if blank_object?(specs[:default])
    specs[:allow_null] = specs[:allow_null] && !specs[:primary_key]
    [ m.call(row["COLUMN_NAME"]), specs ]
  }
end