Class: Tina4::Drivers::MssqlDriver

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

Constant Summary

Constants included from Tina4::DatabaseAdapter

Tina4::DatabaseAdapter::ABSTRACT_CONTRACT, Tina4::DatabaseAdapter::CONNECT_TIMEOUT_VAR, Tina4::DatabaseAdapter::CONTRACT, Tina4::DatabaseAdapter::DEFAULT_CONNECT_TIMEOUT_SECONDS

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SchemaSplit

#split_schema

Methods included from Tina4::DatabaseAdapter

#autocommit, #autocommit=, bound_reached?, bounding_connect, connect_timed_out!, connect_timeout_seconds, connect_timeout_whole_seconds, #execute_many, #fetch, #fetch_one, implemented_by?, #open, #start_transaction, #supports_atomic_batch, #supports_atomic_batch=, validate!

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



18
19
20
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 18

def connection
  @connection
end

Instance Method Details

#affected_rows ⇒ Object

Rows changed by the most recent INSERT/UPDATE/DELETE on this connection. Parity with SQLite (connection.changes), MySQL (stmt.affected_rows), PostgreSQL (cmd_tuples) and the Python master (cursor.rowcount).



118
119
120
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 118

def affected_rows
  @affected_rows.to_i
end

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



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 130

def apply_limit(sql, limit, offset = 0)
  # SQL Server's OFFSET/FETCH paging REQUIRES an ORDER BY. A query with
  # none (a fetch_one aggregate like "SELECT COUNT(*)" / "SELECT MAX(id)",
  # or any unordered SELECT given a limit) otherwise raises "Incorrect
  # syntax near '0'" at the OFFSET. Append a no-op ORDER BY (SELECT NULL)
  # when the SQL has no ORDER BY, mirroring the Python master (mssql.py).
  #
  # Both appends go on a NEW LINE: inline they land inside a trailing
  # `-- comment` and are silently swallowed (see the note on
  # Drivers::SqliteDriver#apply_limit). The ORDER BY probe reads the
  # SCRUBBED SQL for the same reason — an ORDER BY that only appears
  # inside a comment or a string literal is not an ORDER BY.
  has_order = Tina4::Database.scrub_sql_text(sql) =~ /\bORDER\s+BY\b/i
  ordered = has_order ? sql : "#{sql}\nORDER BY (SELECT NULL)"
  "#{ordered}\nOFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY"
end

#begin_transaction ⇒ Object 



147
148
149
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 147

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

#close ⇒ Object 



49
50
51
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 49

def close
  @connection&.close
end

#columns(table_name) ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 180

def columns(table_name)
  # v3.13.14 (#48): honour a schema-qualified name; bare names match any schema.
  schema, tbl = split_schema(table_name)
  # Same hole PostgreSQL had: :primary_key hardcoded false meant
  # Database#primary_key introspected NOTHING on SQL Server, so the
  # feature-4 filterless-write guard rejected every PK-keyed update.
  # Ported from the Python master; the subquery yields every column of the
  # PK, so a COMPOSITE key reports true on each of its columns.
  sql = <<~SQL
    SELECT c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, c.COLUMN_DEFAULT,
           CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS is_primary
    FROM INFORMATION_SCHEMA.COLUMNS c
    LEFT JOIN (
      SELECT ku.COLUMN_NAME
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
        ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
      WHERE tc.TABLE_NAME = ? AND (? IS NULL OR tc.TABLE_SCHEMA = ?)
        AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
    WHERE c.TABLE_NAME = ? AND (? IS NULL OR c.TABLE_SCHEMA = ?)
    ORDER BY c.ORDINAL_POSITION
  SQL
  rows = execute_query(sql, [tbl, schema, schema, 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: (r[:is_primary] || r[:IS_PRIMARY]).to_i == 1
    }
  end
end

#commit ⇒ Object 



151
152
153
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 151

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

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



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 20

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)
  options = {
    host: uri[:host],
    port: uri[:port] || 1433,
    username: username || uri[:username],
    password: password || uri[:password],
    database: uri[:database]
  }
  # FreeTDS's OWN login_timeout, in whole seconds - the bound on reaching
  # and logging in to the server. MEASURED: 3.01s ("TDS server connection
  # timed out") against a TCPServer that accepts and never replies, where
  # the same connect with no bound sat past 20s and needed SIGKILL. The
  # separate :timeout (per-query, tiny_tds default 5s) is untouched.
  seconds = Tina4::DatabaseAdapter.connect_timeout_whole_seconds
  options[:login_timeout] = seconds if seconds
  Tina4::DatabaseAdapter.bounding_connect(options[:host], options[:port]) do
    @connection = TinyTds::Client.new(**options)
  end
end

#count_subquery_alias ⇒ Object

Postgres, MySQL, MSSQL and ODBC all REQUIRE a name for a derived table, so the COUNT probe in Database#count_probe wraps as FROM (sql) AS _count_query. SQLite and Firebird do not define this and get no alias - Firebird rejects AS in that position.



12
13
14
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 12

def count_subquery_alias
  "_count_query"
end

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



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
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 74

def execute(sql, params = [])
  sql = translate_sql(sql)
  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")
    # @@ROWCOUNT rides along in the SAME batch for the same reason
    # SCOPE_IDENTITY() does: it reports the row count of the immediately
    # preceding statement (the INSERT), and read in a later batch it would
    # describe something else entirely.
    result = @connection.execute(
      "#{effective_sql}; SELECT SCOPE_IDENTITY() AS id, @@ROWCOUNT AS affected"
    )
    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
    @affected_rows = row && row[:affected] ? row[:affected].to_i : 1
    return true
  end

  result = @connection.execute(effective_sql)
  # TinyTds::Result#do runs the statement and RETURNS the number of rows
  # it affected. That count was computed and thrown away: the driver
  # exposed no #affected_rows, so Database#write_affected fell through to
  # its default of 0 and an UPDATE that really changed a row reported
  # affected_rows = 0 — indistinguishable from "matched nothing".
  @affected_rows = result.do
end

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



53
54
55
56
57
58
59
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 53

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

#get_database_type ⇒ Object

ADR-0044 required adapter capability.



171
172
173
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 171

def get_database_type
  'mssql'
end

#last_insert_id ⇒ Object 



111
112
113
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 111

def last_insert_id
  @last_insert_id
end

#placeholder ⇒ Object 



122
123
124
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 122

def placeholder
  "?"
end

#placeholders(count) ⇒ Object 



126
127
128
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 126

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

#rollback ⇒ Object 



155
156
157
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 155

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) 


161
162
163
164
165
166
167
168
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 161

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 



175
176
177
178
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 175

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

#translate_sql(sql) ⇒ Object

Translate SQLite-canonical SQL to T-SQL at APPLY time, mirroring the Python master's mssql.py _translate_sql: AUTOINCREMENT -> IDENTITY(1,1), bare TRUE/FALSE -> 1/0 (MSSQL has BIT, not a boolean type; a TRUE/FALSE INSIDE a string literal is data and is left untouched), strip CREATE TABLE IF NOT EXISTS (not T-SQL) and map TIMESTAMP -> DATETIME2 (MSSQL's TIMESTAMP is a rowversion). Every helper only touches DDL keywords or bare boolean tokens, so a plain INSERT/UPDATE/DELETE/SELECT is returned untouched.



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

def translate_sql(sql)
  sql = Tina4::SQLTranslator.auto_increment_syntax(sql, "mssql")
  sql = Tina4::SQLTranslator.boolean_to_int(sql)
  Tina4::SQLTranslator.ddl_types(sql, "mssql")
end