Repository: tobymao/sqlglot

Issue:
sqlglot doesn't support SQLITE square brackets around keywords
See https://www.sqlite.org/lang_keywords.html

The square bracket notation is used in place of other means of escaping keywords - eg, instead of double quotes. 

```
sqlglot.errors.ParseError: Required keyword: 'this' missing for <class 'sqlglot.expressions.Table'>. Line 2, Col: 14.

CREATE TABLE [Track]
(
    [TrackId] INTEGER  NOT NULL,
    [Name] NVARCHAR(200)  NOT NULL,
    [AlbumId] INTEGER,
```

Column 14 is the square bracket.

Another example:
`SELECT [track].[name] FROM [track];`
Runs fine in SQLite, fails to parse:
```
sqlglot.errors.ParseError: Required keyword: 'expression' missing for <class 'sqlglot.expressions.Dot'>. Line 1, Col: 16.
  SELECT [track].[name] FROM [track];
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 8c4b8a58..279ef046 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -29,7 +29,7 @@ def _date_add_sql(data_type, kind):
 
 
 class BigQuery(Dialect):
-    identifier = "`"
+    identifiers = ["`"]
     escape = "\\"
     unnest_column_only = True
 
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index d12adc0f..e820af11 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -52,15 +52,26 @@ class _Dialect(type):
         klass.parser_class = getattr(klass, "Parser", Parser)
         klass.generator_class = getattr(klass, "Generator", Generator)
 
+        identifiers = dict(
+            (identifier[0], identifier[1])
+            if isinstance(identifier, tuple)
+            else (identifier, identifier)
+            for identifier in klass.identifiers or ['"']
+        )
+        klass.identifier_start, klass.identifier_end = list(identifiers.items())[0]
+
         klass.tokenizer = klass.tokenizer_class(
-            identifier=klass.identifier,
+            identifiers=identifiers,
             escape=klass.escape,
         )
+
         return klass
 
 
 class Dialect(metaclass=_Dialect):
-    identifier = None
+    identifiers = None
+    identifier_start = '"'
+    identifier_end = '"'
     escape = "'"
     index_offset = 0
     unnest_column_only = False
@@ -142,7 +153,8 @@ class Dialect(metaclass=_Dialect):
         return self.generator_class(
             **{
                 "quote": self.tokenizer_class.QUOTES[0],
-                "identifier": self.identifier,
+                "identifier_start": self.identifier_start,
+                "identifier_end": self.identifier_end,
                 "escape": self.escape,
                 "index_offset": self.index_offset,
                 "time_mapping": self.inverse_time_mapping,
@@ -223,7 +235,7 @@ def no_trycast_sql(self, expression):
 
 def struct_extract_sql(self, expression):
     this = self.sql(expression, "this")
-    struct_key = self.sql(expression, "expression").replace(self.quote, self.identifier)
+    struct_key = self.sql(exp.Identifier(this=expression.expression, quoted=True))
     return f"{this}.{struct_key}"
 
 
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 9650a4dd..15f374ab 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -116,7 +116,7 @@ class HiveMap(exp.Map):
 
 
 class Hive(Dialect):
-    identifier = "`"
+    identifiers = ["`"]
     escape = "\\"
     alias_post_tablesample = True
 
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 53eba4bf..c596b154 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -11,7 +11,7 @@ from sqlglot.parser import Parser
 
 
 class MySQL(Dialect):
-    identifier = "`"
+    identifiers = ["`"]
 
     # https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions
     time_mapping = {
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 5af89e83..302f3f13 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -105,6 +105,7 @@ class Snowflake(Dialect):
     class Parser(Parser):
         FUNCTIONS = {
             **Parser.FUNCTIONS,
+            "ARRAYAGG": exp.ArrayAgg.from_arg_list,
             "IFF": exp.If.from_arg_list,
             "TO_TIMESTAMP": _snowflake_to_timestamp,
         }
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index ab54e024..3a2d6d2f 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -12,6 +12,8 @@ from sqlglot.tokens import Tokenizer, TokenType
 
 
 class SQLite(Dialect):
+    identifiers = ['"', ("[", "]"), "`"]
+
     class Tokenizer(Tokenizer):
         KEYWORDS = {
             **Tokenizer.KEYWORDS,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 0869d4bc..aa629e18 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -19,7 +19,8 @@ class Generator:
             represents a python time format and the output the target time format
         time_trie (trie): a trie of the time_mapping keys
         pretty (bool): if set to True the returned string will be formatted. Default: False.
-        identifier (str): specifies which character to use to delimit identifiers. Default: ".
+        identifier_start (str): specifies which starting character to use to delimit identifiers. Default: ".
+        identifier_end (str): specifies which ending character to use to delimit identifiers. Default: ".
         identify (bool): if set to True all identifiers will be delimited by the corresponding
             character.
         normalize (bool): if set to True all identifiers will lower cased
@@ -86,7 +87,8 @@ class Generator:
         "time_trie",
         "pretty",
         "configured_pretty",
-        "identifier",
+        "identifier_start",
+        "identifier_end",
         "identify",
         "normalize",
         "quote",
@@ -108,7 +110,8 @@ class Generator:
         time_mapping=None,
         time_trie=None,
         pretty=None,
-        identifier=None,
+        identifier_start=None,
+        identifier_end=None,
         identify=False,
         normalize=False,
         quote=None,
@@ -130,7 +133,8 @@ class Generator:
         self.time_trie = time_trie
         self.pretty = pretty if pretty is not None else sqlglot.pretty
         self.configured_pretty = self.pretty
-        self.identifier = identifier or '"'
+        self.identifier_start = identifier_start or '"'
+        self.identifier_end = identifier_end or '"'
         self.identify = identify
         self.normalize = normalize
         self.quote = quote or "'"
@@ -398,7 +402,7 @@ class Generator:
         value = expression.name
         value = value.lower() if self.normalize else value
         if expression.args.get("quoted") or self.identify:
-            return f"{self.identifier}{value}{self.identifier}"
+            return f"{self.identifier_start}{value}{self.identifier_end}"
         return value
 
     def partition_sql(self, expression):
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 515bb5f5..5b7408a8 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -550,7 +550,7 @@ class Tokenizer(metaclass=_Tokenizer):
     KEYWORD_TRIE = None  # autofilled
 
     __slots__ = (
-        "identifier",
+        "identifiers",
         "escape",
         "sql",
         "size",
@@ -566,17 +566,17 @@ class Tokenizer(metaclass=_Tokenizer):
 
     def __init__(
         self,
-        identifier=None,
+        identifiers=None,
         escape=None,
     ):
         """
         Tokenizer consumes a sql string and produces an array of :class:`~sqlglot.tokens.Token`
 
         Args
-            identifier (str): the identifier character
+            identifiers (dict): keys are the identifer start characters and values are the identifier end characters
             escape (str): the escape code character
         """
-        self.identifier = identifier or '"'
+        self.identifiers = identifiers or {'"': '"'}
         self.escape = escape or "'"
         self.reset()
 
@@ -606,6 +606,7 @@ class Tokenizer(metaclass=_Tokenizer):
                 break
 
             white_space = self.WHITE_SPACE.get(self._char)
+            identifier_end = self.identifiers.get(self._char)
 
             if white_space:
                 if white_space == TokenType.BREAK:
@@ -613,8 +614,8 @@ class Tokenizer(metaclass=_Tokenizer):
                     self._line += 1
             elif self._char.isdigit():
                 self._scan_number()
-            elif self._char == self.identifier:
-                self._scan_identifier()
+            elif identifier_end:
+                self._scan_identifier(identifier_end)
             else:
                 self._scan_keywords()
         return self.tokens
@@ -799,11 +800,11 @@ class Tokenizer(metaclass=_Tokenizer):
         self._add(TokenType.STRING, text)
         return True
 
-    def _scan_identifier(self):
-        while self._peek != self.identifier:
+    def _scan_identifier(self, identifier_end):
+        while self._peek != identifier_end:
             if self._end:
                 raise RuntimeError(
-                    f"Missing {self.identifier} from {self._line}:{self._start}"
+                    f"Missing {identifier_end} from {self._line}:{self._start}"
                 )
             self._advance()
         self._advance()


Test patch:
diff --git a/tests/test_dialects.py b/tests/test_dialects.py
index c17cb988..08ac181d 100644
--- a/tests/test_dialects.py
+++ b/tests/test_dialects.py
@@ -2148,6 +2148,13 @@ TBLPROPERTIES (
             write="snowflake",
         )
 
+        self.validate(
+            "SELECT ARRAYAGG(DISTINCT a)",
+            "SELECT COLLECT_LIST(DISTINCT a)",
+            read="snowflake",
+            write="spark",
+        )
+
         with self.assertRaises(UnsupportedError):
             transpile(
                 "SELECT * FROM a INTERSECT ALL SELECT * FROM b",
@@ -2208,9 +2215,15 @@ TBLPROPERTIES (
             write="sqlite",
             pretty=True,
         )
+        self.validate(
+            "SELECT CAST([a].[b] AS SMALLINT) FROM foo",
+            'SELECT CAST("a"."b" AS INTEGER) FROM foo',
+            read="sqlite",
+            write="sqlite",
+        )
         self.validate(
             "SELECT CAST(`a`.`b` AS SMALLINT) FROM foo",
-            "SELECT CAST(`a`.`b` AS INTEGER) FROM foo",
+            'SELECT CAST("a"."b" AS INTEGER) FROM foo',
             read="sqlite",
             write="sqlite",
         )