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

SPATIAL_ENGINES = 
%w[postgres postgresql].freeze
SPATIAL_IDENTIFIER = 
/\A[A-Za-z_][A-Za-z0-9_]*(?:\.[A-Za-z_][A-Za-z0-9_]*)*\z/
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) 


215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/tina4/sql_translator.rb', line 215

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.



318
319
320
321
322
323
324
325
 
# File 'lib/tina4/sql_translator.rb', line 318

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

.bbox(engine, column, srid = Point::DEFAULT_SRID) ⇒ Object 



78
79
80
81
 
# File 'lib/tina4/sql_translator.rb', line 78

def bbox(engine, column, srid = Point::DEFAULT_SRID)
  require_spatial(engine, "bbox")
  "ST_Intersects(#{spatial_identifier(column)}, ST_MakeEnvelope(?, ?, ?, ?, #{Integer(srid)})::geography)"
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) 


185
186
187
188
189
190
191
 
# File 'lib/tina4/sql_translator.rb', line 185

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 



327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
 
# File 'lib/tina4/sql_translator.rb', line 327

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) 


167
168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/tina4/sql_translator.rb', line 167

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

.ddl_types(sql, engine) ⇒ String

Translate SQLite-canonical DDL column TYPES + CREATE-TABLE options to the target engine.

ONLY acts on CREATE TABLE / ALTER TABLE statements, so a query or INSERT that happens to contain the word TEXT (a column name, a string literal) is never rewritten. Complements auto_increment_syntax (which maps the id keyword) so ONE portable migration -- and every ORM.create_table DDL, which is also SQLite-canonical -- applies on every engine instead of failing on Firebird/MSSQL.

  • Firebird has no TEXT (-607), no REAL, and no CREATE TABLE IF NOT EXISTS.
  • MSSQL has no CREATE TABLE IF NOT EXISTS and its TIMESTAMP is a rowversion, not a datetime -- a created_at TIMESTAMP there is wrong.
  • MySQL's TIMESTAMP carries auto-update / 2038 surprises, so a datetime column maps to DATETIME (matching ORM.create_table).

Parameters:

  • sql (String)
  • engine (String) —

    firebird, mssql, mysql (others pass through)

Returns:

  • (String) 


256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
 
# File 'lib/tina4/sql_translator.rb', line 256

def ddl_types(sql, engine)
  # Gate to DDL only, tolerating leading `-- ...` comment lines / blank
  # lines that a migration file carries before its CREATE TABLE. A SELECT
  # or INSERT that merely mentions a type keyword is never rewritten.
  head = sql.sub(/\A(?:\s*--[^\n]*\n)+/, "")
  return sql unless head =~ /\A\s*(?:CREATE\s+TABLE|ALTER\s+TABLE)\b/i

  case engine.to_s.downcase
  when "firebird"
    sql = sql.gsub(/\bIF\s+NOT\s+EXISTS\b/i, "")
    # Map bare TEXT -> BLOB SUB_TYPE TEXT, but leave an existing
    # "BLOB SUB_TYPE TEXT" intact (it already contains the word TEXT).
    sql = sql.gsub(/\bBLOB\s+SUB_TYPE\s+TEXT\b/i, "\x00FBTEXT\x00")
    sql = sql.gsub(/\bTEXT\b/i, "BLOB SUB_TYPE TEXT")
    sql = sql.gsub("\x00FBTEXT\x00", "BLOB SUB_TYPE TEXT")
    sql.gsub(/\bREAL\b/i, "DOUBLE PRECISION")
  when "mssql"
    sql = sql.gsub(/\bIF\s+NOT\s+EXISTS\b/i, "")
    sql.gsub(/\bTIMESTAMP\b/i, "DATETIME2")
  when "mysql"
    sql.gsub(/\bTIMESTAMP\b/i, "DATETIME")
  else
    sql
  end
end

.distance(engine, column, srid = Point::DEFAULT_SRID) ⇒ Object 



59
60
61
 
# File 'lib/tina4/sql_translator.rb', line 59

def distance(engine, column, srid = Point::DEFAULT_SRID)
  "ST_Distance(#{spatial_identifier(column)}, #{point_literal(engine, srid)})"
end

.distance_as(engine, column, alias_name, srid = Point::DEFAULT_SRID) ⇒ Object 



63
64
65
 
# File 'lib/tina4/sql_translator.rb', line 63

def distance_as(engine, column, alias_name, srid = Point::DEFAULT_SRID)
  "#{distance(engine, column, srid)} AS #{spatial_identifier(alias_name, 'result alias')}"
end

.geometry_literal(engine, form = :ewkt, srid = Point::DEFAULT_SRID) ⇒ Object

Raises:

  • (ArgumentError) 


67
68
69
70
71
72
 
# File 'lib/tina4/sql_translator.rb', line 67

def geometry_literal(engine, form = :ewkt, srid = Point::DEFAULT_SRID)
  require_spatial(engine, "spatial predicates")
  return "ST_GeogFromText(?)" if form.to_sym == :ewkt
  return "ST_SetSRID(ST_GeomFromGeoJSON(?), #{Integer(srid)})::geography" if form.to_sym == :geojson
  raise ArgumentError, "Unsupported spatial geometry form: #{form}"
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) 


199
200
201
202
203
204
205
206
207
208
 
# File 'lib/tina4/sql_translator.rb', line 199

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

.intersects(engine, column, form = :ewkt, srid = Point::DEFAULT_SRID) ⇒ Object 



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

def intersects(engine, column, form = :ewkt, srid = Point::DEFAULT_SRID)
  "ST_Intersects(#{spatial_identifier(column)}, #{geometry_literal(engine, form, srid)})"
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.



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/tina4/sql_translator.rb', line 105

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

.point_column_type(engine, srid = Point::DEFAULT_SRID) ⇒ Object 



38
39
40
41
 
# File 'lib/tina4/sql_translator.rb', line 38

def point_column_type(engine, srid = Point::DEFAULT_SRID)
  require_spatial(engine, "PointField")
  "geography(Point,#{Integer(srid)})"
end

.point_literal(engine, srid = Point::DEFAULT_SRID) ⇒ Object 



50
51
52
53
 
# File 'lib/tina4/sql_translator.rb', line 50

def point_literal(engine, srid = Point::DEFAULT_SRID)
  require_spatial(engine, "spatial predicates")
  "ST_SetSRID(ST_MakePoint(?, ?), #{Integer(srid)})::geography"
end

.require_spatial(engine, feature) ⇒ Object

Raises:



23
24
25
26
27
28
29
30
 
# File 'lib/tina4/sql_translator.rb', line 23

def require_spatial(engine, feature)
  name = engine.to_s.downcase
  return name if SPATIAL_ENGINES.include?(name)
  raise SpatialNotSupportedError,
        "#{feature} is not supported on the '#{name.empty? ? 'unknown' : name}' database engine. " \
        "Tina4 GIS support is PostGIS-first: use PostgreSQL with CREATE EXTENSION postgis. " \
        "Tina4 will not replace a spatial query with an approximate coordinate query."
end

.restore_literals(masked, literals) ⇒ Object

Inverse of #mask_literals.



154
155
156
 
# File 'lib/tina4/sql_translator.rb', line 154

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

.spatial_identifier(name, what = "column") ⇒ Object

Raises:

  • (ArgumentError) 


32
33
34
35
36
 
# File 'lib/tina4/sql_translator.rb', line 32

def spatial_identifier(name, what = "column")
  text = name.to_s
  raise ArgumentError, "Spatial #{what} is not a valid SQL identifier: #{text}" unless SPATIAL_IDENTIFIER.match?(text)
  text
end

.spatial_index(engine, table, column) ⇒ Object 



43
44
45
46
47
48
 
# File 'lib/tina4/sql_translator.rb', line 43

def spatial_index(engine, table, column)
  require_spatial(engine, "spatial index creation")
  table = spatial_identifier(table, "table")
  column = spatial_identifier(column)
  "CREATE INDEX IF NOT EXISTS #{table.tr('.', '_')}_#{column}_gist ON #{table} USING GIST (#{column})"
end

.within_distance(engine, column, srid = Point::DEFAULT_SRID) ⇒ Object 



55
56
57
 
# File 'lib/tina4/sql_translator.rb', line 55

def within_distance(engine, column, srid = Point::DEFAULT_SRID)
  "ST_DWithin(#{spatial_identifier(column)}, #{point_literal(engine, srid)}, ?)"
end