sqlglot.parsers.sqlite

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, parser
  6from sqlglot.parser import IsPredicatePrecedence, binary_range_parser
  7from sqlglot.tokens import Token, TokenType
  8
  9
 10def _build_date(args: list) -> exp.Date | exp.Anonymous:
 11    if len(args) <= 1:
 12        return exp.Date.from_arg_list(args)
 13    return exp.Anonymous(this="DATE", expressions=args)
 14
 15
 16def _build_strftime(args: list) -> exp.Anonymous | exp.TimeToStr:
 17    if len(args) == 1:
 18        args.append(exp.CurrentTimestamp())
 19    if len(args) == 2:
 20        return exp.TimeToStr(this=exp.TsOrDsToTimestamp(this=args[1]), format=args[0])
 21    return exp.Anonymous(this="STRFTIME", expressions=args)
 22
 23
 24def _build_max(args: list) -> exp.Max | exp.Greatest:
 25    # The multi-argument max() is a scalar that returns NULL if any argument is NULL
 26    if len(args) > 1:
 27        return exp.Greatest(this=args[0], expressions=args[1:], ignore_nulls=False)
 28    return exp.Max.from_arg_list(args)
 29
 30
 31def _build_min(args: list) -> exp.Min | exp.Least:
 32    # The multi-argument min() is a scalar that returns NULL if any argument is NULL
 33    if len(args) > 1:
 34        return exp.Least(this=args[0], expressions=args[1:], ignore_nulls=False)
 35    return exp.Min.from_arg_list(args)
 36
 37
 38def _build_dpipe(
 39    self: parser.Parser, this: exp.Expr | None, expression: exp.Expr | None
 40) -> exp.DPipe:
 41    return self.expression(
 42        exp.DPipe(this=this, expression=expression, safe=not self.dialect.STRICT_STRING_CONCAT)
 43    )
 44
 45
 46def _build_json_extract(args: list, dialect: t.Any) -> exp.JSONExtract | exp.JSONExtractScalar:
 47    # Single-path json_extract() returns an SQL representation like ->>, except
 48    # that object/array results keep the JSON subtype (json_subtype); the
 49    # multi-path form returns a JSON array. https://www.sqlite.org/json1.html#jex
 50    if len(args) == 2:
 51        extract = parser.build_extract_json_with_path(exp.JSONExtractScalar)(args, dialect)
 52        extract.set("json_subtype", True)
 53        return extract
 54    return parser.build_extract_json_with_path(exp.JSONExtract)(args, dialect)
 55
 56
 57class SQLiteParser(parser.Parser):
 58    IS_PREDICATE_PRECEDENCE = IsPredicatePrecedence.EQUALITY
 59
 60    PRIMARY_PARSERS = {
 61        **parser.Parser.PRIMARY_PARSERS,
 62        TokenType.HEX_STRING: lambda self, token: self._parse_hex_string(token),
 63    }
 64
 65    STRING_ALIASES = True
 66    ALTER_RENAME_REQUIRES_COLUMN = False
 67    JOINS_HAVE_EQUAL_PRECEDENCE = True
 68    ADD_JOIN_ON_TRUE = True
 69
 70    TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {
 71        TokenType.ANTI,
 72        TokenType.SEMI,
 73    }
 74
 75    COLUMN_OPERATORS = {
 76        k: v
 77        for k, v in parser.Parser.COLUMN_OPERATORS.items()
 78        if k not in (TokenType.ARROW, TokenType.DARROW)
 79    }
 80
 81    # ||, -> and ->> form a single left-associative precedence tier that binds tighter
 82    # than multiplication, so it's consumed in _parse_factor_operand rather than in
 83    # _parse_bitwise: https://sqlite.org/lang_expr.html
 84    CONCAT_OPERATORS: t.ClassVar[dict[TokenType, t.Callable]] = {
 85        TokenType.DPIPE: _build_dpipe,
 86        TokenType.ARROW: parser.build_json_extract,
 87        TokenType.DARROW: parser.build_json_extract_scalar,
 88    }
 89
 90    ARITHMETIC_TOKENS = {
 91        *parser.Parser.BITWISE,
 92        *parser.Parser.TERM,
 93        *parser.Parser.FACTOR,
 94    } - {TokenType.COLLATE}
 95
 96    FUNCTIONS = {
 97        **parser.Parser.FUNCTIONS,
 98        "DATE": _build_date,
 99        "DATETIME": lambda args: exp.Anonymous(this="DATETIME", expressions=args),
100        "EDITDIST3": exp.Levenshtein.from_arg_list,
101        "JSON_EXTRACT": _build_json_extract,
102        "JSON_GROUP_ARRAY": exp.JSONArrayAgg.from_arg_list,
103        "JSON_GROUP_OBJECT": lambda args: exp.JSONObjectAgg(expressions=args),
104        "MAX": _build_max,
105        "MIN": _build_min,
106        "STRFTIME": _build_strftime,
107        "SQLITE_VERSION": exp.CurrentVersion.from_arg_list,
108        "TIME": lambda args: exp.Anonymous(this="TIME", expressions=args),
109    }
110
111    FUNCTION_PARSERS = {
112        **parser.Parser.FUNCTION_PARSERS,
113        "GROUP_CONCAT": lambda self: self._parse_string_agg(),
114    }
115
116    PROPERTY_PARSERS = {
117        **parser.Parser.PROPERTY_PARSERS,
118        "USING": lambda self, **kwargs: self._parse_module_property(),
119        "VIRTUAL": lambda self: self.expression(exp.VirtualProperty()),
120        "WITHOUT": lambda self: self._parse_without_rowid(),
121    }
122
123    STATEMENT_PARSERS = {
124        **parser.Parser.STATEMENT_PARSERS,
125        TokenType.ATTACH: lambda self: self._parse_attach_detach(),
126        TokenType.DETACH: lambda self: self._parse_attach_detach(is_attach=False),
127        TokenType.PRAGMA: lambda self: self._parse_pragma(),
128    }
129
130    RANGE_PARSERS = {
131        **parser.Parser.RANGE_PARSERS,
132        # https://www.sqlite.org/lang_expr.html
133        TokenType.MATCH: binary_range_parser(exp.Match),
134    }
135
136    def _parse_is(self, this: exp.Expr | None) -> exp.Expr | None:
137        """SQLite's IS / IS NOT is a null-safe equality against any expression.
138
139        Other dialects only accept NULL, TRUE, FALSE or UNKNOWN on the right
140        side, so anything else is parsed as IS [NOT] DISTINCT FROM; each
141        transpiler then emits the syntax it supports (SQLite keeps IS / IS
142        NOT) (#8855).
143        """
144        this = super()._parse_is(this)
145
146        is_node = this
147        wrapped_in_not = False
148        if isinstance(this, exp.Not) and isinstance(this.this, exp.Is):
149            is_node = this.this
150            wrapped_in_not = True
151
152        if isinstance(is_node, exp.Is):
153            rhs = is_node.expression.unnest()
154            if isinstance(rhs, (exp.Null, exp.Boolean)):
155                is_node.set("expression", rhs)
156            else:
157                cls = exp.NullSafeNEQ if wrapped_in_not else exp.NullSafeEQ
158                this = cls(this=is_node.this, expression=is_node.expression)
159
160        return this
161
162    def _parse_is_rhs(self, distinct: bool = False) -> exp.Expr | None:
163        # Ordering comparisons bind above IS, but range predicates bind below it.
164        this = self._parse_bitwise()
165        while self._match_set(self.COMPARISON):
166            comments = self._prev_comments
167            this = self.expression(
168                self.COMPARISON[self._prev.token_type](this=this, expression=self._parse_bitwise()),
169                comments=comments,
170            )
171        return this
172
173    def _parse_hex_string(self, token: Token) -> exp.Expr:
174        if self.sql.startswith(("0x", "0X"), token.start):
175            value = int(token.text, 16)
176            # SQLite interprets hex integers as signed 64-bit two's-complement values.
177            if value >= 1 << 63:
178                value -= 1 << 64
179            return self.expression(exp.Literal.number(value), token)
180
181        return self.expression(exp.HexString(this=token.text), token)
182
183    def _parse_factor_operand(self) -> exp.Expr | None:
184        in_arithmetic_operand = self._prev.token_type in self.ARITHMETIC_TOKENS
185        this = self._parse_concat_operand()
186        parsed_op = False
187
188        while self._curr and (build := self.CONCAT_OPERATORS.get(self._curr.token_type)):
189            if self._curr.token_type != TokenType.DPIPE and isinstance(this, exp.DPipe):
190                # Parenthesize so that dialects where the arrows bind tighter
191                # than || still produce the same, left-associative grouping
192                this = self.expression(exp.Paren(this=this))
193
194            self._advance()
195            this = build(self, this, self._parse_concat_operand())
196            parsed_op = True
197
198        if parsed_op and (
199            in_arithmetic_operand
200            or (self._curr and self._curr.token_type in self.ARITHMETIC_TOKENS)
201        ):
202            # This tier binds tighter than arithmetic in SQLite but looser elsewhere,
203            # so parenthesize it to preserve the grouping in other dialects
204            this = self.expression(exp.Paren(this=this))
205
206        return this
207
208    def _parse_concat_operand(self) -> exp.Expr | None:
209        this = self._parse_unary()
210
211        # COLLATE binds tighter than any binary operator, including this tier
212        while self._match(TokenType.COLLATE):
213            collate = self.expression(exp.Collate(this=this, expression=self._parse_unary()))
214            self._normalize_collate(collate)
215            this = collate
216
217        return this
218
219    def _parse_unique(self) -> exp.UniqueColumnConstraint:
220        # Do not consume more tokens if UNIQUE is used as a standalone constraint, e.g:
221        # CREATE TABLE foo (bar TEXT UNIQUE REFERENCES baz ...)
222        if self._curr.text.upper() in self.CONSTRAINT_PARSERS:
223            return self.expression(exp.UniqueColumnConstraint())
224
225        return super()._parse_unique()
226
227    def _parse_without_rowid(self) -> exp.WithoutRowidProperty | None:
228        index = self._index
229        if self._match_text_seq("ROWID"):
230            return self.expression(exp.WithoutRowidProperty())
231        self._retreat(index)
232        return None
233
234    def _parse_module_property(self, **kwargs: t.Any) -> exp.ModuleProperty:
235        name = self._parse_id_var()
236        expressions = (
237            self._parse_wrapped_csv(self._parse_id_var)
238            if self._match(TokenType.L_PAREN, advance=False)
239            else None
240        )
241        return self.expression(exp.ModuleProperty(this=name, expressions=expressions))
242
243    def _parse_pragma(self) -> exp.Pragma:
244        name = self._parse_var(any_token=True)
245
246        if self._match(TokenType.DOT):
247            name = exp.Dot(this=name, expression=self._parse_var(any_token=True))
248
249        self._match(TokenType.EQ)
250
251        value = self._parse_wrapped(
252            lambda: self._parse_unary() or self._parse_var(any_token=True), optional=True
253        )
254
255        if value:
256            return self.expression(exp.Pragma(this=exp.EQ(this=name, expression=value)))
257
258        return self.expression(exp.Pragma(this=name))
259
260    def _parse_attach_detach(self, is_attach=True) -> exp.Attach | exp.Detach:
261        self._match(TokenType.DATABASE)
262        this = self._parse_expression()
263
264        return (
265            self.expression(exp.Attach(this=this))
266            if is_attach
267            else self.expression(exp.Detach(this=this))
268        )
269
270    # https://www.sqlite.org/gencol.html
271    def _parse_generated_as_identity(
272        self,
273    ) -> (
274        exp.GeneratedAsIdentityColumnConstraint
275        | exp.ComputedColumnConstraint
276        | exp.GeneratedAsRowColumnConstraint
277    ):
278        this = super()._parse_generated_as_identity()
279
280        # Only expression-bearing GENERATED ALWAYS AS (expr) forms are computed
281        # columns. Match STORED/VIRTUAL inside this branch so true identity
282        # (AS IDENTITY) does not silently consume a trailing storage keyword.
283        if (
284            isinstance(this, exp.GeneratedAsIdentityColumnConstraint)
285            and this.expression is not None
286        ):
287            persisted = (
288                self._match_texts(("STORED", "VIRTUAL")) and self._prev.text.upper() == "STORED"
289            )
290            return self.expression(
291                exp.ComputedColumnConstraint(this=this.expression, persisted=persisted)
292            )
293
294        return this