Class: Tina4::SQLTranslator

Inherits:
Object
  • Object
show all
Defined in:
lib/tina4/sql_translator.rb

Overview

Cross-engine SQL translator.

Each database adapter calls the rules it needs. Rules are composable and stateless -- just string transforms.

Also includes query caching with TTL support.

Usage:

translated = Tina4::SQLTranslator.limit_to_rows("SELECT * FROM users LIMIT 10 OFFSET 5")
# => "SELECT * FROM users ROWS 6 TO 15"

Constant Summary collapse

PRIMARY =

A concat/ilike operand: a masked literal-or-identifier token, a simple function call, a (qualified) identifier, a placeholder, or a number. The function-call args exclude | so a nested || never splits the chain.

'(?:\x00\d+\x00|[A-Za-z_][\w$]*\s*\([^()|]*\)|[A-Za-z_][\w$]*(?:\.[A-Za-z_][\w$]*)*|:[A-Za-z_]\w*|\$\d+|\?|%s|\d+(?:\.\d+)?)'
MAX_BIND_PARAMS =

Hard per-statement bind-parameter ceiling per engine. 0 = never collapse. Sourced from spec/fixtures/batch_write_contract.json, byte-identical in all four frameworks.

{
  "sqlite" => 999,
  "postgres" => 65_535,
  "mysql" => 65_535,
  "mssql" => 2_100,
  "firebird" => 0,
  "odbc" => 0,
  "mongodb" => 0
}.freeze
ENGINE_ALIASES =

The four frameworks do not agree on what an engine calls itself - Python and PHP report "postgresql", Ruby and Node report "postgres". Without normalising, the cap lookup misses and the collapse silently does nothing on the engine with the largest win.

{
  "postgresql" => "postgres",
  "pgsql" => "postgres",
  "sqlite3" => "sqlite",
  "sqlserver" => "mssql",
  "sqlsrv" => "mssql",
  "mariadb" => "mysql"
}.freeze
INSERT_VALUES = 
/\A\s*INSERT\s+INTO\s+.+?\s+VALUES\s*\(([^()]*)\)\s*\z/im
FIRST_ID_ENGINES =

Engines whose last_insert_id reports the FIRST generated id of a multi-row INSERT rather than the last. Verified live against MySQL.

%w[mysql].freeze

Class Method Summary collapse

Class Method Details

.auto_increment_syntax(sql, engine) ⇒ String

Translate AUTOINCREMENT across engines in DDL.

Parameters:

  • sql (String)
  • engine (String) —

    one of: mysql, postgresql, mssql, firebird, sqlite

Returns:

  • (String) 


152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/tina4/sql_translator.rb', line 152

def auto_increment_syntax(sql, engine)
  case engine
  when "mysql"
    sql.gsub("AUTOINCREMENT", "AUTO_INCREMENT")
  when "postgresql"
    # BIGINT PRIMARY KEY AUTOINCREMENT -> BIGSERIAL (a real 64-bit sequence);
    # INTEGER -> SERIAL. A plain BIGINT with the keyword merely stripped has
    # no sequence and cannot auto-increment.
    sql
      .gsub(/\bBIGINT\s+PRIMARY\s+KEY\s+AUTOINCREMENT\b/i, "BIGSERIAL PRIMARY KEY")
      .gsub(/\bINTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT\b/i, "SERIAL PRIMARY KEY")
      .gsub(/\s*\bAUTOINCREMENT\b/i, "")
  when "mssql"
    sql.gsub(/AUTOINCREMENT/i, "IDENTITY(1,1)")
  when "firebird"
    sql.gsub(/\s*AUTOINCREMENT\b/i, "")
  else
    sql
  end
end

.batch_last_id(reported_id, rows_in_chunk, engine) ⇒ Array<Array(String, Array)>

Collapse a row-at-a-time INSERT batch into chunked multi-row VALUES.

A batch that loops one INSERT per row pays a full network round-trip per row, and the round-trip - not SQL building - is the entire cost of a batch write. Measured over 500 rows: PostgreSQL 9848ms row-at-a-time against 15.8ms as a single multi-row statement (625x), MySQL 216x, MSSQL 121x.

PURE: no I/O and no engine contact, so the chunking rules are checkable without a database. The live-engine runners prove the rows land.

Normalise a collapsed batch's last id to the LAST row's id.

A row-at-a-time batch reports the last row's id simply because the last statement inserted the last row. Collapsing rows into one statement changes that on any engine that reports the FIRST generated id, so this restores the contract instead of quietly redefining it.

Verified live, not assumed: a 3-row insert into a fresh MySQL table reports 1 while MAX(id) is 3. SQLite, PostgreSQL and MSSQL already report the last and are left alone. The ids in one statement are consecutive, so the last is +first + rows - 1+.

Parameters:

  • sql (String) —

    the single-row INSERT the batch would loop

  • params_list (Array<Array>) —

    one entry per row

  • engine (String) —

    engine name as the driver reports it (aliases ok)

Returns:

  • (Array<Array(String, Array)>) —

    statements to run INSTEAD of the loop, or an EMPTY array meaning "not collapsible - keep looping", which is always correct.



209
210
211
212
213
214
215
216
 
# File 'lib/tina4/sql_translator.rb', line 209

def batch_last_id(reported_id, rows_in_chunk, engine)
  name = engine.to_s.downcase
  name = ENGINE_ALIASES.fetch(name, name)
  return reported_id unless FIRST_ID_ENGINES.include?(name)
  return reported_id unless reported_id.is_a?(Integer) || reported_id.to_s.match?(/\A-?\d+\z/)

  reported_id.to_i + [rows_in_chunk.to_i, 1].max - 1
end

.boolean_to_int(sql) ⇒ String

Convert a bare TRUE/FALSE to 1/0 for engines without a boolean type. A TRUE/FALSE INSIDE a string literal is data and is left untouched.

Parameters:

  • sql (String)

Returns:

  • (String) 


122
123
124
125
126
127
128
 
# File 'lib/tina4/sql_translator.rb', line 122

def boolean_to_int(sql)
  return sql unless sql.match?(/\b(?:TRUE|FALSE)\b/i)

  masked, literals = mask_literals(sql)
  masked = masked.gsub(/\bTRUE\b/i, "1").gsub(/\bFALSE\b/i, "0")
  restore_literals(masked, literals)
end

.build_batch_inserts(sql, params_list, engine) ⇒ Object 



218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/tina4/sql_translator.rb', line 218

def build_batch_inserts(sql, params_list, engine)
  rows = params_list || []
  return [] if rows.length < 2

  name = engine.to_s.downcase
  name = ENGINE_ALIASES.fetch(name, name)
  cap = MAX_BIND_PARAMS.fetch(name, 0)
  # Firebird has no multi-row VALUES syntax; ODBC's real ceiling depends on
  # the driver behind it. Emitting SQL the engine cannot parse to save a
  # round-trip is not a trade worth making.
  return [] if cap <= 0

  upper = sql.upcase
  # A collapsed statement returns N rows where the caller expects one, and
  # conflict arbitration changes once rows share a statement.
  return [] if upper.include?("RETURNING") ||
               upper.include?("ON CONFLICT") ||
               upper.include?("ON DUPLICATE KEY")

  match = INSERT_VALUES.match(sql)
  return [] if match.nil?

  # Every slot must be a bare placeholder. `now()` repeated per row inside
  # one statement is not the same write as `now()` evaluated per statement.
  slots = match[1].split(",").map(&:strip)
  return [] if slots.empty? || slots.any? { |slot| slot != "?" }

  columns = slots.length
  return [] if rows.any? { |params| params.length != columns }

  chunk_rows = [1, cap / columns].max
  return [] if chunk_rows < 2

  head = sql[0...(match.begin(1) - 1)].rstrip
  one_row = "(#{Array.new(columns, '?').join(', ')})"

  rows.each_slice(chunk_rows).map do |chunk|
    ["#{head} #{Array.new(chunk.length, one_row).join(', ')}", chunk.flatten(1)]
  end
end

.concat_pipes_to_func(sql) ⇒ String

Convert || string concatenation to CONCAT() for MySQL/MSSQL. Rewrites ONLY || operators joining expression operands OUTSIDE any string literal or comment, and only the operand chain - never the whole statement.

SELECT a || b FROM t   =>  SELECT CONCAT(a, b) FROM t
WHERE data = 'a||b'    =>  WHERE data = 'a||b'   (literal untouched)

Parameters:

  • sql (String)

Returns:

  • (String) 


104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/tina4/sql_translator.rb', line 104

def concat_pipes_to_func(sql)
  return sql unless sql.include?("||")

  masked, literals = mask_literals(sql)
  return sql unless masked.include?("||")

  chain = /#{SQLTranslator::PRIMARY}(?:\s*\|\|\s*#{SQLTranslator::PRIMARY})+/
  rewritten = masked.gsub(chain) do |m|
    "CONCAT(#{m.split(/\s*\|\|\s*/).join(', ')})"
  end
  restore_literals(rewritten, literals)
end

.ilike_to_like(sql) ⇒ String

Convert col ILIKE pattern to LOWER(col) LIKE LOWER(pattern) for engines without ILIKE. The pattern operand is captured whole (a multi-word '%two words%' survives), and an ILIKE INSIDE a string literal is untouched.

Parameters:

  • sql (String)

Returns:

  • (String) 


136
137
138
139
140
141
142
143
144
145
 
# File 'lib/tina4/sql_translator.rb', line 136

def ilike_to_like(sql)
  return sql unless sql =~ /ilike/i

  masked, literals = mask_literals(sql)
  pattern = /(#{SQLTranslator::PRIMARY})\s+ILIKE\s+(#{SQLTranslator::PRIMARY})/i
  rewritten = masked.gsub(pattern) do
    "LOWER(#{::Regexp.last_match(1)}) LIKE LOWER(#{::Regexp.last_match(2)})"
  end
  restore_literals(rewritten, literals)
end

.mask_literals(sql) ⇒ Object

Replace string literals, quoted identifiers and comments with opaque "\x00N\x00" tokens. Returns [masked_sql, literals]; doubled-quote escapes ('' "" ``) are handled so an embedded quote never ends the span early.



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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
 
# File 'lib/tina4/sql_translator.rb', line 42

def mask_literals(sql)
  literals = []
  out = +""
  i = 0
  n = sql.length
  while i < n
    ch = sql[i]
    nxt = sql[i + 1]
    if ch == "'" || ch == '"' || ch == "`"
      start = i
      i += 1
      while i < n
        if sql[i] == ch
          if sql[i + 1] == ch
            i += 2
            next
          end
          i += 1
          break
        end
        i += 1
      end
      out << "\x00#{literals.length}\x00"
      literals << sql[start...i]
      next
    end
    if ch == "-" && nxt == "-"
      start = i
      i += 1 while i < n && sql[i] != "\n"
      out << "\x00#{literals.length}\x00"
      literals << sql[start...i]
      next
    end
    if ch == "/" && nxt == "*"
      start = i
      i += 2
      i += 1 while i < n && !(sql[i] == "*" && sql[i + 1] == "/")
      i = [i + 2, n].min
      out << "\x00#{literals.length}\x00"
      literals << sql[start...i]
      next
    end
    out << ch
    i += 1
  end
  [out, literals]
end

.restore_literals(masked, literals) ⇒ Object

Inverse of #mask_literals.



91
92
93
 
# File 'lib/tina4/sql_translator.rb', line 91

def restore_literals(masked, literals)
  masked.gsub(/\x00(\d+)\x00/) { literals[::Regexp.last_match(1).to_i] }
end