Class: Tina4::Drivers::MssqlDriver

Inherits:
Object
  • Object
show all
Includes:
SchemaSplit
Defined in:
lib/tina4/drivers/mssql_driver.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SchemaSplit

#split_schema

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



9
10
11
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 9

def connection
  @connection
end

Instance Method Details

#apply_limit(sql, limit, offset = 0) ⇒ Object 



78
79
80
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 78

def apply_limit(sql, limit, offset = 0)
  "#{sql} OFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY"
end

#begin_transaction ⇒ Object 



82
83
84
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 82

def begin_transaction
  @connection.execute("BEGIN TRANSACTION").do
end

#close ⇒ Object 



30
31
32
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 30

def close
  @connection&.close
end

#columns(table_name) ⇒ Object 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 110

def columns(table_name)
  # v3.13.14 (#48): honour a schema-qualified name; bare names match any schema.
  schema, tbl = split_schema(table_name)
  sql = "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS " \
        "WHERE TABLE_NAME = ? AND (? IS NULL OR TABLE_SCHEMA = ?)"
  rows = execute_query(sql, [tbl, schema, schema])
  rows.map do |r|
    {
      name: r[:COLUMN_NAME] || r[:column_name],
      type: r[:DATA_TYPE] || r[:data_type],
      nullable: (r[:IS_NULLABLE] || r[:is_nullable]) == "YES",
      default: r[:COLUMN_DEFAULT] || r[:column_default],
      primary_key: false
    }
  end
end

#commit ⇒ Object 



86
87
88
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 86

def commit
  @connection.execute("COMMIT").do
end

#connect(connection_string, username: nil, password: nil) ⇒ Object 



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 11

def connect(connection_string, username: nil, password: nil)
  begin
    require "tiny_tds"
  rescue LoadError
    raise LoadError,
          "The 'tiny_tds' gem is required for MSSQL connections. Install one of:\n" \
          "    bundle add tiny_tds     # if your project uses Bundler\n" \
          "    gem install tiny_tds    # bare driver"
  end
  uri = parse_connection(connection_string)
  @connection = TinyTds::Client.new(
    host: uri[:host],
    port: uri[:port] || 1433,
    username: username || uri[:username],
    password: password || uri[:password],
    database: uri[:database]
  )
end

#execute(sql, params = []) ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 42

def execute(sql, params = [])
  effective_sql = interpolate_params(sql, params)

  # Capture the generated IDENTITY AT WRITE TIME — mirror of the Python
  # master (mssql.py execute(): SELECT SCOPE_IDENTITY() runs straight after
  # the INSERT on the SAME cursor). tiny_tds runs each #execute as its OWN
  # T-SQL batch, and SCOPE_IDENTITY() is batch-scoped: read in a separate
  # later batch it is always NULL — which is why both insert(...).last_id
  # and db.get_last_id came back nil (issue #262). So for an INSERT we run
  # the INSERT and SELECT SCOPE_IDENTITY() in ONE batch (a single
  # @connection.execute), read the id from the SAME batch, and cache it.
  if sql.to_s.lstrip[0, 6].casecmp?("INSERT")
    result = @connection.execute("#{effective_sql}; SELECT SCOPE_IDENTITY() AS id")
    rows = result.each(symbolize_keys: true).to_a
    result.cancel if result.respond_to?(:cancel)
    row = rows.last
    @last_insert_id = row && row[:id] ? row[:id].to_i : nil
    return true
  end

  result = @connection.execute(effective_sql)
  result.do
end

#execute_query(sql, params = []) ⇒ Object 



34
35
36
37
38
39
40
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 34

def execute_query(sql, params = [])
  effective_sql = interpolate_params(sql, params)
  result = @connection.execute(effective_sql)
  rows = result.each(symbolize_keys: true).to_a
  result.cancel if result.respond_to?(:cancel)
  rows
end

#last_insert_id ⇒ Object 



66
67
68
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 66

def last_insert_id
  @last_insert_id
end

#placeholder ⇒ Object 



70
71
72
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 70

def placeholder
  "?"
end

#placeholders(count) ⇒ Object 



74
75
76
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 74

def placeholders(count)
  (["?"] * count).join(", ")
end

#rollback ⇒ Object 



90
91
92
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 90

def rollback
  @connection.execute("ROLLBACK").do
end

#table_exists?(name) ⇒ Boolean

v3.13.14 (#48): honour a schema-qualified name (“dbo.widget”); a bare name matches in any schema (NULL guard skips the schema filter).

Returns:

  • (Boolean) 


96
97
98
99
100
101
102
103
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 96

def table_exists?(name)
  schema, tbl = split_schema(name)
  sql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES " \
        "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = ? " \
        "AND (? IS NULL OR TABLE_SCHEMA = ?)"
  rows = execute_query(sql, [tbl, schema, schema])
  !rows.empty?
end

#tables ⇒ Object 



105
106
107
108
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 105

def tables
  rows = execute_query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
  rows.map { |r| r[:TABLE_NAME] || r[:table_name] }
end