Class: Tina4::SQLTranslator

Inherits:
Object
  • Object
show all
Defined in:
lib/tina4/sql_translation.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"

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) 


105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/tina4/sql_translation.rb', line 105

def auto_increment_syntax(sql, engine)
  case engine
  when "mysql"
    sql.gsub("AUTOINCREMENT", "AUTO_INCREMENT")
  when "postgresql"
    sql.gsub(/INTEGER\s+PRIMARY\s+KEY\s+AUTOINCREMENT/i, "SERIAL PRIMARY KEY")
  when "mssql"
    sql.gsub(/AUTOINCREMENT/i, "IDENTITY(1,1)")
  when "firebird"
    sql.gsub(/\s*AUTOINCREMENT\b/i, "")
  else
    sql
  end
end

.boolean_to_int(sql) ⇒ String

Convert TRUE/FALSE to 1/0 for engines without boolean type.

Parameters:

  • sql (String)

Returns:

  • (String) 


84
85
86
 
# File 'lib/tina4/sql_translation.rb', line 84

def boolean_to_int(sql)
  sql.gsub(/\bTRUE\b/i, "1").gsub(/\bFALSE\b/i, "0")
end

.concat_pipes_to_func(sql) ⇒ String

Convert || concatenation to CONCAT() for MySQL/MSSQL.

‘a’ || ‘b’ || ‘c’ => CONCAT(‘a’, ‘b’, ‘c’)

Parameters:

  • sql (String)

Returns:

  • (String) 


69
70
71
72
73
74
75
76
77
78
 
# File 'lib/tina4/sql_translation.rb', line 69

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

  parts = sql.split("||")
  if parts.length > 1
    "CONCAT(#{parts.map(&:strip).join(', ')})"
  else
    sql
  end
end

.ilike_to_like(sql) ⇒ String

Convert ILIKE to LOWER() LIKE LOWER() for engines without ILIKE.

Parameters:

  • sql (String)

Returns:

  • (String) 


92
93
94
95
96
97
98
 
# File 'lib/tina4/sql_translation.rb', line 92

def ilike_to_like(sql)
  sql.gsub(/(\S+)\s+ILIKE\s+(\S+)/i) do
    col = ::Regexp.last_match(1).strip
    val = ::Regexp.last_match(2).strip
    "LOWER(#{col}) LIKE LOWER(#{val})"
  end
end

.limit_to_rows(sql) ⇒ String

Convert LIMIT/OFFSET to Firebird ROWS…TO syntax.

LIMIT 10 OFFSET 5 => ROWS 6 TO 15 LIMIT 10 => ROWS 1 TO 10

Parameters:

  • sql (String)

Returns:

  • (String) 


27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/tina4/sql_translation.rb', line 27

def limit_to_rows(sql)
  # Try LIMIT X OFFSET Y first
  if (m = sql.match(/\bLIMIT\s+(\d+)\s+OFFSET\s+(\d+)\s*$/i))
    limit = m[1].to_i
    offset = m[2].to_i
    start_row = offset + 1
    end_row = offset + limit
    return sql[0...m.begin(0)] + "ROWS #{start_row} TO #{end_row}"
  end

  # Then try LIMIT X only
  if (m = sql.match(/\bLIMIT\s+(\d+)\s*$/i))
    limit = m[1].to_i
    return sql[0...m.begin(0)] + "ROWS 1 TO #{limit}"
  end

  sql
end

.limit_to_top(sql) ⇒ String

Convert LIMIT to MSSQL TOP syntax.

SELECT … LIMIT 10 => SELECT TOP 10 … OFFSET queries are left unchanged (not supported by TOP).

Parameters:

  • sql (String)

Returns:

  • (String) 


53
54
55
56
57
58
59
60
61
 
# File 'lib/tina4/sql_translation.rb', line 53

def limit_to_top(sql)
  if (m = sql.match(/\bLIMIT\s+(\d+)\s*$/i)) && !sql.match?(/\bOFFSET\b/i)
    limit = m[1].to_i
    body = sql[0...m.begin(0)].strip
    return body.sub(/^(SELECT)\b/i, "\\1 TOP #{limit}")
  end

  sql
end

.placeholder_style(sql, style) ⇒ String

Convert ? placeholders to engine-specific style.

? => %s (MySQL, PostgreSQL) ? => :1, :2 (Oracle, Firebird)

Parameters:

  • sql (String)
  • style (String) —

    target placeholder style: “%s” or “:”

Returns:

  • (String) 


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

def placeholder_style(sql, style)
  case style
  when "%s"
    sql.gsub("?", "%s")
  when ":"
    count = 0
    sql.chars.map do |ch|
      if ch == "?"
        count += 1
        ":#{count}"
      else
        ch
      end
    end.join
  else
    sql
  end
end

.query_key(sql, params = nil) ⇒ String

Generate a cache key for a query and its parameters.

Parameters:

  • sql (String)
  • params (Array, nil) (defaults to: nil)

Returns:

  • (String) 


152
153
154
155
 
# File 'lib/tina4/sql_translation.rb', line 152

def query_key(sql, params = nil)
  raw = params ? "#{sql}|#{params.inspect}" : sql
  "query:#{Digest::SHA256.hexdigest(raw)}"
end