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