Class: Minus5::Mssql::Adapter

Inherits:
Object
  • Object
show all
Defined in:
lib/minus5_mssql/adapter.rb

Instance Method Summary collapse

Constructor Details

#initialize(params) ⇒ Adapter

params - tiny_tds connection params: github.com/rails-sqlserver/tiny_tds with additon of mirror_host Example:

SqlBase.new({  :username    => "rails",
               :password    => "",
               :host        => "bedem",
               :mirror_host => "mssql",
               :database    => "activerecord_unittest_mirroring"
            })



16
17
18
19
20
21
 
# File 'lib/minus5_mssql/adapter.rb', line 16

def initialize(params)
  params = YAML.load_file(params).symbolize_keys if params.kind_of?(String)
  @params = params
  @params_cache = {}
  connect
end

Instance Method Details

#create_table(schema, table, columns_def) ⇒ Object 



100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/minus5_mssql/adapter.rb', line 100

def create_table(schema, table, columns_def)
  execute <<-SQL
    if not exists(select * 
                  from sys.tables 
                  inner join sys.schemas on tables.schema_id = schemas.schema_id 
                  where 
                    tables.name = '#{table}' 
                    and schemas.name = '#{schema}')
      create table #{schema}.#{table} (#{columns_def})
  SQL
end

#delete(table_name, data) ⇒ Object

Delete rows from table_name. Data is hash with keys eg. => 123



35
36
37
38
39
40
41
42
43
 
# File 'lib/minus5_mssql/adapter.rb', line 35

def delete(table_name, data)
  columns, values = hash_to_columns_values(data)
  keys = []
  for i in (0..columns.size-1)
    keys << "#{columns[i]} = #{values[i]}"
  end
  sql = "delete from #{table_name} where #{keys.join(' and ')}"
  execute(sql).cancel
end

#execute(sql) ⇒ Object

Send query to the database. With reconnect in case of db mirroring failover.



46
47
48
49
50
51
 
# File 'lib/minus5_mssql/adapter.rb', line 46

def execute(sql)
  @connection.execute(sql)
rescue TinyTds::Error => e
  connect
  execute(sql)
end

#host ⇒ Object 



112
113
114
 
# File 'lib/minus5_mssql/adapter.rb', line 112

def host
  return @params[:host]
end

#insert(table_name, data) ⇒ Object

Insert row into table_name. Data is hash => value, … Acutal column names will be discovered from database.



26
27
28
29
30
31
 
# File 'lib/minus5_mssql/adapter.rb', line 26

def insert(table_name, data)
  columns = get_params(table_name).reject{|c| c == "id"}
  values = hash_to_values columns, data
  sql = "insert into #{table_name} (#{columns.join(',')}) values (#{values.join(',')})"
  execute(sql).insert
end

#select(options) ⇒ Object 



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/minus5_mssql/adapter.rb', line 67

def select(options)
  if options.kind_of?(String)
    execute(options).each(:symbolize_keys=>true)
  else
    options = {:primary_key=>:id}.merge(options)
    results = execute(options[:sql]).each(:symbolize_keys=>true)
    return [] if results.size == 0
    return results if results[0].kind_of?(Hash)
    data = {} #parent indexed by primary_key
    results[0].each{ |row| data[row[options[:primary_key]]] = row }
    options[:relations].each_with_index do |relation, index|
      result = results[index+1] #child result
      result.each do |row|
        #find parent row by foreign key and insert child row in collection
        data_row = data[row[relation[:foreign_key]]]
        next unless data_row
        if relation[:type] == :one_to_many
         data_row[relation[:name]] = [] unless data_row[relation[:name]]
          data_row[relation[:name]] << row
        elsif relation[:type] == :one_to_one
          #merge child row with parent
          if relation[:name]
            data_row[relation[:name]] = row
          else                
            data_row.merge!(row.reject{|key, value| key == relation[:foreign_key]})
          end
        end
      end
    end
    options[:return_hash] ? data : results[0]
  end
end

#select_value(sql) ⇒ Object

Returns results first column of the first row.



54
55
56
57
58
 
# File 'lib/minus5_mssql/adapter.rb', line 54

def select_value(sql)
  rows = execute(sql).each(:as=>:array)
  return if rows.size == 0
  rows[0][0].kind_of?(Array) ? rows[0][0][0] : rows[0][0]
end

#select_values(sql) ⇒ Object

Returns array of the values from the first column of all returned rows



61
62
63
64
65
 
# File 'lib/minus5_mssql/adapter.rb', line 61

def select_values(sql)
  rows = execute(sql).each(:as=>:array)
  return [] if rows.size == 0
  (rows[0][0].kind_of?(Array) ? rows[0] : rows).map{|row| row[0] }        
end