Repository navigation
Expand file tree
/
Copy pathsqlite.py
More file actions
379 lines (311 loc) · 14.4 KB
/
Copy pathsqlite.py
File metadata and controls
379 lines (311 loc) · 14.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
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
152
153
154
155
156
157
158
159
160
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
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
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
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
367
368
369
370
371
372
373
374
375
376
377
378
379
from __future__ import annotations
import typing as t
from sqlglot import exp, generator, transforms
from sqlglot.dialects.dialect import (
any_value_to_max_sql,
arrow_json_extract_sql,
concat_to_dpipe_sql,
count_if_to_sum,
no_ilike_sql,
no_pivot_sql,
no_tablesample_sql,
no_trycast_sql,
rename_func,
strposition_sql,
)
from sqlglot.generator import unsupported_args
from sqlglot.optimizer.scope import find_in_scope
from sqlglot.tokens import TokenType
def _transform_create(expression: exp.Expr) -> exp.Expr:
"""Move primary key to a column and enforce auto_increment on primary keys."""
schema = expression.this
if isinstance(expression, exp.Create) and isinstance(schema, exp.Schema):
defs = {}
primary_key = None
for e in schema.expressions:
if isinstance(e, exp.ColumnDef):
defs[e.name] = e
elif isinstance(e, exp.PrimaryKey):
primary_key = e
if primary_key and len(primary_key.expressions) == 1:
column = defs[primary_key.expressions[0].name]
column.append(
"constraints", exp.ColumnConstraint(kind=exp.PrimaryKeyColumnConstraint())
)
schema.expressions.remove(primary_key)
for column in defs.values():
primary_key_index = -1
auto_increment = None
auto_increment_index = -1
for i, constraint in enumerate(column.constraints):
if isinstance(constraint.kind, exp.PrimaryKeyColumnConstraint):
primary_key_index = i
elif isinstance(constraint.kind, exp.AutoIncrementColumnConstraint):
auto_increment = constraint
auto_increment_index = i
if auto_increment is not None and (
primary_key_index == -1 or auto_increment_index < primary_key_index
):
column.constraints.remove(auto_increment)
if primary_key_index != -1:
column.constraints.insert(primary_key_index, auto_increment)
return expression
def _generated_to_auto_increment(expression: exp.Expr) -> exp.Expr:
if not isinstance(expression, exp.ColumnDef):
return expression
generated = expression.find(exp.GeneratedAsIdentityColumnConstraint)
# Only rewrite true identity columns. Expression-bearing forms are computed
# columns (GENERATED ALWAYS AS (expr)) and must keep their expression.
if generated and generated.expression is None:
t.cast(exp.ColumnConstraint, generated.parent).pop()
not_null = expression.find(exp.NotNullColumnConstraint)
if not_null:
t.cast(exp.ColumnConstraint, not_null.parent).pop()
expression.append(
"constraints", exp.ColumnConstraint(kind=exp.AutoIncrementColumnConstraint())
)
return expression
def _offset_to_limit(expression: exp.Expr) -> exp.Expr:
if not isinstance(expression, exp.Select):
return expression
offset = expression.args.get("offset")
if offset and not expression.args.get("limit"):
expression.limit(-1, copy=False)
return expression
class SQLiteGenerator(generator.Generator):
SELECT_KINDS: tuple[str, ...] = ()
TRY_SUPPORTED = False
SUPPORTS_UESCAPE = False
SUPPORTS_DECODE_CASE = False
AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
JOIN_HINTS = False
TABLE_HINTS = False
QUERY_HINTS = False
NVL2_SUPPORTED = False
JSON_PATH_BRACKETED_KEY_SUPPORTED = False
SUPPORTS_CREATE_TABLE_LIKE = False
SUPPORTS_TABLE_ALIAS_COLUMNS = False
SUPPORTS_TO_NUMBER = False
SUPPORTS_WINDOW_EXCLUDE = True
EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
SUPPORTS_MEDIAN = False
JSON_KEY_VALUE_PAIR_SEP = ","
PARSE_JSON_NAME: str | None = None
SUPPORTED_JSON_PATH_PARTS = {
exp.JSONPathKey,
exp.JSONPathRoot,
exp.JSONPathSubscript,
}
TYPE_MAPPING = {
**{k: v for k, v in generator.Generator.TYPE_MAPPING.items() if k != exp.DType.BLOB},
exp.DType.BOOLEAN: "INTEGER",
exp.DType.TINYINT: "INTEGER",
exp.DType.SMALLINT: "INTEGER",
exp.DType.INT: "INTEGER",
exp.DType.BIGINT: "INTEGER",
exp.DType.FLOAT: "REAL",
exp.DType.DOUBLE: "REAL",
exp.DType.DECIMAL: "REAL",
exp.DType.CHAR: "TEXT",
exp.DType.NCHAR: "TEXT",
exp.DType.VARCHAR: "TEXT",
exp.DType.NVARCHAR: "TEXT",
exp.DType.BINARY: "BLOB",
exp.DType.VARBINARY: "BLOB",
}
TOKEN_MAPPING = {
TokenType.AUTO_INCREMENT: "AUTOINCREMENT",
}
TRANSFORMS = {
**generator.Generator.TRANSFORMS,
exp.AnyValue: any_value_to_max_sql,
exp.Chr: rename_func("CHAR"),
exp.Concat: concat_to_dpipe_sql,
exp.CountIf: count_if_to_sum,
exp.Create: transforms.preprocess([_transform_create]),
exp.CurrentDate: lambda *_: "CURRENT_DATE",
exp.CurrentTime: lambda *_: "CURRENT_TIME",
exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
exp.CurrentVersion: lambda *_: "SQLITE_VERSION()",
exp.ColumnDef: transforms.preprocess([_generated_to_auto_increment]),
exp.DateStrToDate: lambda self, e: self.sql(e, "this"),
exp.If: rename_func("IIF"),
exp.ILike: no_ilike_sql,
exp.JSONArrayAgg: unsupported_args("order", "null_handling", "return_type", "strict")(
rename_func("JSON_GROUP_ARRAY")
),
exp.JSONObjectAgg: lambda self, e: self._jsonobject_sql(e, name="JSON_GROUP_OBJECT"),
exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
rename_func("EDITDIST3")
),
exp.LogicalOr: rename_func("MAX"),
exp.LogicalAnd: rename_func("MIN"),
exp.Pivot: no_pivot_sql,
exp.Rand: rename_func("RANDOM"),
exp.RegexpLike: lambda self, e: self.binary(e, "REGEXP"),
exp.Select: transforms.preprocess(
[
_offset_to_limit,
transforms.eliminate_distinct_on,
transforms.eliminate_qualify,
transforms.eliminate_semi_and_anti_joins,
]
),
exp.StrPosition: lambda self, e: strposition_sql(self, e, func_name="INSTR"),
exp.TableSample: no_tablesample_sql,
exp.TimeStrToTime: lambda self, e: self.sql(e, "this"),
exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.args.get("format"), e.this),
exp.TryCast: no_trycast_sql,
exp.TsOrDsToTimestamp: lambda self, e: self.sql(e, "this"),
}
# SQLite doesn't generally support CREATE TABLE .. properties
# https://www.sqlite.org/lang_createtable.html
PROPERTIES_LOCATION = {
**{
prop: exp.Properties.Location.UNSUPPORTED
for prop in generator.Generator.PROPERTIES_LOCATION
},
# There are a few exceptions (e.g. temporary tables) which are supported or
# can be transpiled to SQLite, so we explicitly override them accordingly
exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
exp.ModuleProperty: exp.Properties.Location.POST_SCHEMA,
exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
exp.VirtualProperty: exp.Properties.Location.POST_CREATE,
}
LIMIT_FETCH = "LIMIT"
def insert_sql(self, expression: exp.Insert) -> str:
if expression.args.get("ignore"):
expression.set("ignore", False)
expression.set("alternative", "IGNORE")
return super().insert_sql(expression)
def bitwiseandagg_sql(self, expression: exp.BitwiseAndAgg) -> str:
self.unsupported("BITWISE_AND aggregation is not supported in SQLite")
return self.function_fallback_sql(expression)
def bitwiseoragg_sql(self, expression: exp.BitwiseOrAgg) -> str:
self.unsupported("BITWISE_OR aggregation is not supported in SQLite")
return self.function_fallback_sql(expression)
def bitwisexoragg_sql(self, expression: exp.BitwiseXorAgg) -> str:
self.unsupported("BITWISE_XOR aggregation is not supported in SQLite")
return self.function_fallback_sql(expression)
def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
if expression.expressions:
return self.function_fallback_sql(expression)
return arrow_json_extract_sql(self, expression)
def jsonextractscalar_sql(self, expression: exp.JSONExtractScalar) -> str:
if expression.args.get("json_subtype"):
# json_extract() keeps the JSON subtype on object/array results;
# ->> strips it, observable when the result feeds another JSON function
return self.func("JSON_EXTRACT", expression.this, expression.expression)
return arrow_json_extract_sql(self, expression)
def dateadd_sql(self, expression: exp.DateAdd) -> str:
modifier = expression.expression
unit = expression.args.get("unit")
# An INTERVAL amount carries its own unit, e.g. DATE_ADD(d, INTERVAL 1 DAY);
# unwrap it so the unit is not left inside the quoted modifier string.
if isinstance(modifier, exp.Interval):
unit = unit or modifier.unit
modifier = modifier.this
modifier = modifier.name if modifier.is_string else self.sql(modifier)
modifier = f"'{modifier} {unit.name}'" if unit else f"'{modifier}'"
return self.func("DATE", expression.this, modifier)
def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
if expression.is_type("date"):
return self.func("DATE", expression.this)
return super().cast_sql(expression)
# https://www.sqlite.org/gencol.html
# Inline unsupported check: mypyc cannot compile @unsupported_args on an
# override of an undecorated base-class method.
def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
if expression.args.get("data_type"):
self.unsupported("SQLite generated columns do not support a data type")
this = expression.this
this_sql = self.sql(this) if isinstance(this, exp.Paren) else f"({self.sql(this)})"
storage = " STORED" if expression.args.get("persisted") else ""
not_null = " NOT NULL" if expression.args.get("not_null") else ""
return f"AS {this_sql}{storage}{not_null}"
# Note: SQLite's TRUNC always returns REAL (e.g., trunc(10.99) -> 10.0), not INTEGER.
# This creates a transpilation gap affecting division semantics, similar to Presto.
# Unlike Presto where this only affects decimals=0, SQLite has no decimals parameter
# so every use of TRUNC is affected. Modeling precisely would require exp.FloatTrunc.
@unsupported_args("decimals")
def trunc_sql(self, expression: exp.Trunc) -> str:
return self.func("TRUNC", expression.this)
def generateseries_sql(self, expression: exp.GenerateSeries) -> str:
parent = expression.parent
alias = parent and parent.args.get("alias")
if isinstance(alias, exp.TableAlias) and alias.columns:
column_alias = alias.columns[0]
alias.set("columns", None)
sql = self.sql(
exp.select(exp.alias_("value", column_alias)).from_(expression).subquery()
)
else:
sql = self.function_fallback_sql(expression)
return sql
def datediff_sql(self, expression: exp.DateDiff) -> str:
unit = expression.args.get("unit")
unit = unit.name.upper() if unit else "DAY"
sql = f"(JULIANDAY({self.sql(expression, 'this')}) - JULIANDAY({self.sql(expression, 'expression')}))"
if unit == "MONTH":
sql = f"{sql} / 30.0"
elif unit == "YEAR":
sql = f"{sql} / 365.0"
elif unit == "HOUR":
sql = f"{sql} * 24.0"
elif unit == "MINUTE":
sql = f"{sql} * 1440.0"
elif unit == "SECOND":
sql = f"{sql} * 86400.0"
elif unit == "MILLISECOND":
sql = f"{sql} * 86400000.0"
elif unit == "MICROSECOND":
sql = f"{sql} * 86400000000.0"
elif unit == "NANOSECOND":
sql = f"{sql} * 8640000000000.0"
else:
self.unsupported(f"DATEDIFF unsupported for '{unit}'.")
return f"CAST({sql} AS INTEGER)"
# https://www.sqlite.org/lang_aggfunc.html#group_concat
def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
this = expression.this
distinct = find_in_scope(expression, exp.Distinct)
if distinct:
this = distinct.expressions[0]
distinct_sql = "DISTINCT "
else:
distinct_sql = ""
if isinstance(expression.this, exp.Order):
self.unsupported("SQLite GROUP_CONCAT doesn't support ORDER BY.")
if expression.this.this and not distinct:
this = expression.this.this
separator = expression.args.get("separator")
return f"GROUP_CONCAT({distinct_sql}{self.format_args(this, separator)})"
def least_sql(self, expression: exp.Least) -> str:
if expression.expressions:
return rename_func("MIN")(self, expression)
return self.sql(expression, "this")
def greatest_sql(self, expression: exp.Greatest) -> str:
if expression.expressions:
return rename_func("MAX")(self, expression)
return self.sql(expression, "this")
def transaction_sql(self, expression: exp.Transaction) -> str:
this = expression.this
this = f" {this}" if this else ""
return f"BEGIN{this} TRANSACTION"
def isascii_sql(self, expression: exp.IsAscii) -> str:
return f"(NOT {self.sql(expression.this)} GLOB CAST(x'2a5b5e012d7f5d2a' AS TEXT))"
@unsupported_args("this")
def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
return "'main'"
def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
self.unsupported("SQLite does not support IGNORE NULLS.")
return self.sql(expression.this)
def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
return self.sql(expression.this)
def windowspec_sql(self, expression: exp.WindowSpec) -> str:
if (
expression.text("kind").upper() == "RANGE"
and expression.text("start").upper() == "CURRENT ROW"
):
return "RANGE CURRENT ROW"
return super().windowspec_sql(expression)