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::CONNECT_TIMEOUT_SLACK_SECONDS, 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

bounding_connect, connect_timed_out!, connect_timeout_seconds, connect_timeout_whole_seconds, implemented_by?

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).



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

def affected_rows
  @affected_rows.to_i
end

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



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 116

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 



133
134
135
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 133

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 



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 161

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 



137
138
139
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 137

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 



61
62
63
64
65
66
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
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 61

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")
    # @@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

#last_insert_id ⇒ Object 



97
98
99
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 97

def last_insert_id
  @last_insert_id
end

#placeholder ⇒ Object 



108
109
110
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 108

def placeholder
  "?"
end

#placeholders(count) ⇒ Object 



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

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

#rollback ⇒ Object 



141
142
143
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 141

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) 


147
148
149
150
151
152
153
154
 
# File 'lib/tina4/drivers/mssql_driver.rb', line 147

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 



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

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