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