sqlglot.generators.sqlite

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    any_value_to_max_sql,
  8    arrow_json_extract_sql,
  9    count_if_to_sum,
 10    groupconcat_sql,
 11    no_ilike_sql,
 12    no_pivot_sql,
 13    no_tablesample_sql,
 14    no_trycast_sql,
 15    rename_func,
 16    strposition_sql,
 17)
 18from sqlglot.generator import unsupported_args
 19from sqlglot.tokens import TokenType
 20
 21
 22def _transform_create(expression: exp.Expr) -> exp.Expr:
 23    """Move primary key to a column and place auto_increment after it."""
 24    schema = expression.this
 25
 26    if isinstance(expression, exp.Create) and isinstance(schema, exp.Schema):
 27        defs = {}
 28        primary_key = None
 29
 30        for e in schema.expressions:
 31            if isinstance(e, exp.ColumnDef):
 32                defs[e.name] = e
 33            elif isinstance(e, exp.PrimaryKey):
 34                primary_key = e
 35
 36        if primary_key and len(primary_key.expressions) == 1:
 37            column = defs[primary_key.expressions[0].name]
 38            column.append(
 39                "constraints", exp.ColumnConstraint(kind=exp.PrimaryKeyColumnConstraint())
 40            )
 41            schema.expressions.remove(primary_key)
 42
 43        for column in defs.values():
 44            primary_key_index = -1
 45            auto_increment = None
 46            auto_increment_index = -1
 47
 48            for i, constraint in enumerate(column.constraints):
 49                if isinstance(constraint.kind, exp.PrimaryKeyColumnConstraint):
 50                    primary_key_index = i
 51                elif isinstance(constraint.kind, exp.AutoIncrementColumnConstraint):
 52                    auto_increment = constraint
 53                    auto_increment_index = i
 54
 55            if auto_increment is not None and 0 <= auto_increment_index < primary_key_index:
 56                column.constraints.remove(auto_increment)
 57                column.constraints.insert(primary_key_index, auto_increment)
 58
 59    return expression
 60
 61
 62def _generated_to_auto_increment(expression: exp.Expr) -> exp.Expr:
 63    if not isinstance(expression, exp.ColumnDef):
 64        return expression
 65
 66    generated = expression.find(exp.GeneratedAsIdentityColumnConstraint)
 67
 68    # Only rewrite true identity columns. Expression-bearing forms are computed
 69    # columns (GENERATED ALWAYS AS (expr)) and must keep their expression.
 70    if generated and generated.expression is None:
 71        t.cast(exp.ColumnConstraint, generated.parent).pop()
 72
 73        not_null = expression.find(exp.NotNullColumnConstraint)
 74        if not_null:
 75            t.cast(exp.ColumnConstraint, not_null.parent).pop()
 76
 77        expression.append(
 78            "constraints", exp.ColumnConstraint(kind=exp.AutoIncrementColumnConstraint())
 79        )
 80
 81    return expression
 82
 83
 84def _offset_to_limit(expression: exp.Expr) -> exp.Expr:
 85    if not isinstance(expression, exp.Select):
 86        return expression
 87
 88    offset = expression.args.get("offset")
 89
 90    if offset and not expression.args.get("limit"):
 91        expression.limit(-1, copy=False)
 92
 93    return expression
 94
 95
 96class SQLiteGenerator(generator.Generator):
 97    SELECT_KINDS: tuple[str, ...] = ()
 98    TRY_SUPPORTED = False
 99    SUPPORTS_UESCAPE = False
100    SUPPORTS_DECODE_CASE = False
101    SET_OP_PARENTHESIZED_OPERANDS = False
102
103    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
104
105    JOIN_HINTS = False
106    TABLE_HINTS = False
107    QUERY_HINTS = False
108    NVL2_SUPPORTED = False
109    JSON_PATH_BRACKETED_KEY_SUPPORTED = False
110    SUPPORTS_CREATE_TABLE_LIKE = False
111    SUPPORTS_TABLE_ALIAS_COLUMNS = False
112    SUPPORTS_TO_NUMBER = False
113    SUPPORTS_WINDOW_EXCLUDE = True
114    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
115    SUPPORTS_MEDIAN = False
116    JSON_KEY_VALUE_PAIR_SEP = ","
117    PARSE_JSON_NAME: str | None = None
118
119    SUPPORTED_JSON_PATH_PARTS = {
120        exp.JSONPathKey,
121        exp.JSONPathRoot,
122        exp.JSONPathSubscript,
123    }
124
125    TYPE_MAPPING = {
126        **{k: v for k, v in generator.Generator.TYPE_MAPPING.items() if k != exp.DType.BLOB},
127        exp.DType.BOOLEAN: "INTEGER",
128        exp.DType.TINYINT: "INTEGER",
129        exp.DType.SMALLINT: "INTEGER",
130        exp.DType.INT: "INTEGER",
131        exp.DType.BIGINT: "INTEGER",
132        exp.DType.FLOAT: "REAL",
133        exp.DType.DOUBLE: "REAL",
134        exp.DType.DECIMAL: "REAL",
135        exp.DType.CHAR: "TEXT",
136        exp.DType.NCHAR: "TEXT",
137        exp.DType.VARCHAR: "TEXT",
138        exp.DType.NVARCHAR: "TEXT",
139        exp.DType.BINARY: "BLOB",
140        exp.DType.VARBINARY: "BLOB",
141    }
142
143    TOKEN_MAPPING = {
144        TokenType.AUTO_INCREMENT: "AUTOINCREMENT",
145    }
146
147    TRANSFORMS = {
148        **generator.Generator.TRANSFORMS,
149        exp.AnyValue: any_value_to_max_sql,
150        exp.Chr: rename_func("CHAR"),
151        exp.CountIf: count_if_to_sum,
152        exp.Create: transforms.preprocess([_transform_create]),
153        exp.CurrentDate: lambda *_: "CURRENT_DATE",
154        exp.CurrentTime: lambda *_: "CURRENT_TIME",
155        exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
156        exp.CurrentVersion: lambda *_: "SQLITE_VERSION()",
157        exp.ColumnDef: transforms.preprocess([_generated_to_auto_increment]),
158        exp.DateStrToDate: lambda self, e: self.sql(e, "this"),
159        exp.If: rename_func("IIF"),
160        exp.ILike: no_ilike_sql,
161        exp.JSONArrayAgg: unsupported_args("order", "null_handling", "return_type", "strict")(
162            rename_func("JSON_GROUP_ARRAY")
163        ),
164        exp.JSONObjectAgg: lambda self, e: self._jsonobject_sql(e, name="JSON_GROUP_OBJECT"),
165        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
166            rename_func("EDITDIST3")
167        ),
168        exp.LogicalOr: rename_func("MAX"),
169        exp.LogicalAnd: rename_func("MIN"),
170        exp.Pivot: no_pivot_sql,
171        exp.Rand: rename_func("RANDOM"),
172        exp.RegexpLike: lambda self, e: self.binary(e, "REGEXP"),
173        exp.Select: transforms.preprocess(
174            [
175                _offset_to_limit,
176                transforms.eliminate_distinct_on,
177                transforms.eliminate_qualify,
178                transforms.eliminate_semi_and_anti_joins,
179            ]
180        ),
181        exp.StrPosition: lambda self, e: strposition_sql(self, e, func_name="INSTR"),
182        exp.TableSample: no_tablesample_sql,
183        exp.TimeStrToTime: lambda self, e: self.sql(e, "this"),
184        exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.args.get("format"), e.this),
185        exp.TryCast: no_trycast_sql,
186        exp.TsOrDsToTimestamp: lambda self, e: self.sql(e, "this"),
187    }
188
189    # SQLite doesn't generally support CREATE TABLE .. properties
190    # https://www.sqlite.org/lang_createtable.html
191    PROPERTIES_LOCATION = {
192        **{
193            prop: exp.Properties.Location.UNSUPPORTED
194            for prop in generator.Generator.PROPERTIES_LOCATION
195        },
196        # There are a few exceptions (e.g. temporary tables) which are supported or
197        # can be transpiled to SQLite, so we explicitly override them accordingly
198        exp.LikeProperty: exp.Properties.Location.POST_SCHEMA,
199        exp.ModuleProperty: exp.Properties.Location.POST_SCHEMA,
200        exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
201        exp.VirtualProperty: exp.Properties.Location.POST_CREATE,
202    }
203
204    LIMIT_FETCH = "LIMIT"
205
206    def autoincrementcolumnconstraint_sql(
207        self, expression: exp.AutoIncrementColumnConstraint
208    ) -> str:
209        column = expression.find_ancestor(exp.ColumnDef)
210        if column and column.find(exp.PrimaryKeyColumnConstraint):
211            return super().autoincrementcolumnconstraint_sql(expression)
212
213        self.unsupported("SQLite AUTOINCREMENT requires an INTEGER PRIMARY KEY")
214        return ""
215
216    def insert_sql(self, expression: exp.Insert) -> str:
217        if expression.args.get("ignore"):
218            expression.set("ignore", False)
219            expression.set("alternative", "IGNORE")
220
221        return super().insert_sql(expression)
222
223    def bitwiseandagg_sql(self, expression: exp.BitwiseAndAgg) -> str:
224        self.unsupported("BITWISE_AND aggregation is not supported in SQLite")
225        return self.function_fallback_sql(expression)
226
227    def bitwiseoragg_sql(self, expression: exp.BitwiseOrAgg) -> str:
228        self.unsupported("BITWISE_OR aggregation is not supported in SQLite")
229        return self.function_fallback_sql(expression)
230
231    def bitwisexoragg_sql(self, expression: exp.BitwiseXorAgg) -> str:
232        self.unsupported("BITWISE_XOR aggregation is not supported in SQLite")
233        return self.function_fallback_sql(expression)
234
235    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
236        if expression.expressions:
237            return self.function_fallback_sql(expression)
238        return arrow_json_extract_sql(self, expression)
239
240    def jsonextractscalar_sql(self, expression: exp.JSONExtractScalar) -> str:
241        if expression.args.get("json_subtype"):
242            # json_extract() keeps the JSON subtype on object/array results;
243            # ->> strips it, observable when the result feeds another JSON function
244            return self.func("JSON_EXTRACT", expression.this, expression.expression)
245        return arrow_json_extract_sql(self, expression)
246
247    def dateadd_sql(self, expression: exp.DateAdd) -> str:
248        modifier = expression.expression
249        unit = expression.args.get("unit")
250        # An INTERVAL amount carries its own unit, e.g. DATE_ADD(d, INTERVAL 1 DAY);
251        # unwrap it so the unit is not left inside the quoted modifier string.
252        if isinstance(modifier, exp.Interval):
253            unit = unit or modifier.unit
254            modifier = modifier.this
255
256        modifier = modifier.unnest()
257        if (
258            unit
259            and not isinstance(modifier, exp.Literal)
260            and not (isinstance(modifier, exp.Neg) and modifier.this.is_number)
261        ):
262            return self.func(
263                "DATE",
264                expression.this,
265                exp.DPipe(
266                    this=exp.paren(modifier),
267                    expression=exp.Literal.string(f" {unit.name}"),
268                ),
269            )
270        modifier = modifier.name if modifier.is_string else self.sql(modifier)
271        modifier = f"'{modifier} {unit.name}'" if unit else f"'{modifier}'"
272        return self.func("DATE", expression.this, modifier)
273
274    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
275        if expression.is_type("date"):
276            return self.func("DATE", expression.this)
277
278        return super().cast_sql(expression)
279
280    # https://www.sqlite.org/gencol.html
281    # Inline unsupported check: mypyc cannot compile @unsupported_args on an
282    # override of an undecorated base-class method.
283    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
284        if expression.args.get("data_type"):
285            self.unsupported("SQLite generated columns do not support a data type")
286
287        this = expression.this
288        this_sql = self.sql(this) if isinstance(this, exp.Paren) else f"({self.sql(this)})"
289        storage = " STORED" if expression.args.get("persisted") else ""
290        not_null = " NOT NULL" if expression.args.get("not_null") else ""
291        return f"AS {this_sql}{storage}{not_null}"
292
293    # Note: SQLite's TRUNC always returns REAL (e.g., trunc(10.99) -> 10.0), not INTEGER.
294    # This creates a transpilation gap affecting division semantics, similar to Presto.
295    # Unlike Presto where this only affects decimals=0, SQLite has no decimals parameter
296    # so every use of TRUNC is affected. Modeling precisely would require exp.FloatTrunc.
297    @unsupported_args("decimals")
298    def trunc_sql(self, expression: exp.Trunc) -> str:
299        return self.func("TRUNC", expression.this)
300
301    def generateseries_sql(self, expression: exp.GenerateSeries) -> str:
302        parent = expression.parent
303        alias = parent and parent.args.get("alias")
304
305        if isinstance(alias, exp.TableAlias) and alias.columns:
306            column_alias = alias.columns[0]
307            alias.set("columns", None)
308            sql = self.sql(
309                exp.select(exp.alias_("value", column_alias)).from_(expression).subquery()
310            )
311        else:
312            sql = self.function_fallback_sql(expression)
313
314        return sql
315
316    def datediff_sql(self, expression: exp.DateDiff) -> str:
317        unit = expression.args.get("unit")
318        unit = unit.name.upper() if unit else "DAY"
319
320        sql = f"(JULIANDAY({self.sql(expression, 'this')}) - JULIANDAY({self.sql(expression, 'expression')}))"
321
322        if unit == "MONTH":
323            sql = f"{sql} / 30.0"
324        elif unit == "YEAR":
325            sql = f"{sql} / 365.0"
326        elif unit == "HOUR":
327            sql = f"{sql} * 24.0"
328        elif unit == "MINUTE":
329            sql = f"{sql} * 1440.0"
330        elif unit == "SECOND":
331            sql = f"{sql} * 86400.0"
332        elif unit == "MILLISECOND":
333            sql = f"{sql} * 86400000.0"
334        elif unit == "MICROSECOND":
335            sql = f"{sql} * 86400000000.0"
336        elif unit == "NANOSECOND":
337            sql = f"{sql} * 86400000000000.0"
338        else:
339            self.unsupported(f"DATEDIFF unsupported for '{unit}'.")
340
341        return f"CAST({sql} AS INTEGER)"
342
343    def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
344        node = expression.parent if isinstance(expression.parent, exp.Filter) else expression
345        window = node.parent
346
347        if (
348            isinstance(expression.this, exp.Order)
349            and isinstance(window, exp.Window)
350            and window.this is node
351        ):
352            self.unsupported(
353                "SQLite GROUP_CONCAT window functions do not support argument ORDER BY"
354            )
355            expression.set("this", expression.this.this)
356
357        return groupconcat_sql(
358            self, expression, func_name="GROUP_CONCAT", sep=None, within_group=False
359        )
360
361    def _greatest_least_sql(self, expression: exp.Greatest | exp.Least) -> str:
362        if not expression.expressions:
363            return self.sql(expression, "this")
364
365        name = "MAX" if isinstance(expression, exp.Greatest) else "MIN"
366
367        if not expression.args.get("ignore_nulls"):
368            return rename_func(name)(self, expression)
369
370        # SQLite's multi-argument MAX/MIN return NULL if any argument is NULL.
371        # GREATEST(a, b, c) -> MAX(COALESCE(a, b, c), COALESCE(b, c, a), COALESCE(c, a, b)).
372        args = [expression.this, *expression.expressions]
373
374        coalesces = []
375        for i in range(len(args)):
376            rotated = args[i:] + args[:i]
377            coalesces.append(exp.Coalesce(this=rotated[0], expressions=rotated[1:]))
378
379        return self.func(name, *coalesces)
380
381    def greatest_sql(self, expression: exp.Greatest) -> str:
382        return self._greatest_least_sql(expression)
383
384    def least_sql(self, expression: exp.Least) -> str:
385        return self._greatest_least_sql(expression)
386
387    def transaction_sql(self, expression: exp.Transaction) -> str:
388        this = expression.this
389        this = f" {this}" if this else ""
390        return f"BEGIN{this} TRANSACTION"
391
392    def isascii_sql(self, expression: exp.IsAscii) -> str:
393        return f"(NOT {self.sql(expression.this)} GLOB CAST(x'2a5b5e012d7f5d2a' AS TEXT))"
394
395    @unsupported_args("this")
396    def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
397        return "'main'"
398
399    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
400        self.unsupported("SQLite does not support IGNORE NULLS.")
401        return self.sql(expression.this)
402
403    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
404        return self.sql(expression.this)
405
406    def windowspec_sql(self, expression: exp.WindowSpec) -> str:
407        if (
408            expression.text("kind").upper() == "RANGE"
409            and expression.text("start").upper() == "CURRENT ROW"
410            and expression.args.get("end") is None
411        ):
412            return "RANGE CURRENT ROW"
413
414        return super().windowspec_sql(expression)