Adding upstream version 23.7.0.upstream/23.7.0

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 79 insertions, 53 deletions

diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 2421987..f3cde0b 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -6,58 +6,6 @@ from sqlglot.helper import logger as helper_logger
 class TestSQLite(Validator):
     dialect = "sqlite"
 
-    def test_ddl(self):
-        self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
-        self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
-
-        self.validate_all(
-            """
-            CREATE TABLE "Track"
-            (
-                CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
-                FOREIGN KEY ("AlbumId") REFERENCES "Album" (
-                    "AlbumId"
-                ) ON DELETE NO ACTION ON UPDATE NO ACTION,
-                FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
-                FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
-            )
-            """,
-            write={
-                "sqlite": """CREATE TABLE "Track" (
-  CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
-  FOREIGN KEY ("AlbumId") REFERENCES "Album" (
-    "AlbumId"
-  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
-  FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
-  FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
-)""",
-            },
-            pretty=True,
-        )
-        self.validate_all(
-            "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
-            read={
-                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
-            },
-            write={
-                "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
-                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
-                "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
-            },
-        )
-        self.validate_all(
-            """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
-            write={
-                "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
-                "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
-            },
-        )
-
     def test_sqlite(self):
         self.validate_identity("SELECT DATE()")
         self.validate_identity("SELECT DATE('now', 'start of month', '+1 month', '-1 day')")
@@ -65,7 +13,6 @@ class TestSQLite(Validator):
         self.validate_identity("SELECT DATETIME(1092941466, 'auto')")
         self.validate_identity("SELECT DATETIME(1092941466, 'unixepoch', 'localtime')")
         self.validate_identity("SELECT UNIXEPOCH()")
-        self.validate_identity("SELECT STRFTIME('%s')")
         self.validate_identity("SELECT JULIANDAY('now') - JULIANDAY('1776-07-04')")
         self.validate_identity("SELECT UNIXEPOCH() - UNIXEPOCH('2004-01-01 02:34:56')")
         self.validate_identity("SELECT DATE('now', 'start of year', '+9 months', 'weekday 2')")
@@ -145,6 +92,29 @@ class TestSQLite(Validator):
             write={"snowflake": "LEAST(x, y, z)"},
         )
 
+    def test_strftime(self):
+        self.validate_identity("SELECT STRFTIME('%Y/%m/%d', 'now')")
+        self.validate_identity("SELECT STRFTIME('%Y-%m-%d', '2016-10-16', 'start of month')")
+        self.validate_identity(
+            "SELECT STRFTIME('%s')",
+            "SELECT STRFTIME('%s', CURRENT_TIMESTAMP)",
+        )
+
+        self.validate_all(
+            "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+            write={
+                "duckdb": "SELECT STRFTIME(CAST('2020-01-01 12:05:03' AS TIMESTAMP), '%Y-%m-%d')",
+                "sqlite": "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+            },
+        )
+        self.validate_all(
+            "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+            write={
+                "duckdb": "SELECT STRFTIME(CAST(CURRENT_TIMESTAMP AS TIMESTAMP), '%Y-%m-%d')",
+                "sqlite": "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+            },
+        )
+
     def test_datediff(self):
         self.validate_all(
             "DATEDIFF(a, b, 'day')",
@@ -190,3 +160,59 @@ class TestSQLite(Validator):
             )
 
             self.assertIn("Named columns are not supported in table alias.", cm.output[0])
+
+    def test_ddl(self):
+        for conflict_action in ("ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"):
+            with self.subTest(f"ON CONFLICT {conflict_action}"):
+                self.validate_identity("CREATE TABLE a (b, c, UNIQUE (b, c) ON CONFLICT IGNORE)")
+
+        self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
+        self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
+
+        self.validate_all(
+            """
+            CREATE TABLE "Track"
+            (
+                CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+                FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+                    "AlbumId"
+                ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+                FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+                FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+            )
+            """,
+            write={
+                "sqlite": """CREATE TABLE "Track" (
+  CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+  FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+    "AlbumId"
+  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+  FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+)""",
+            },
+            pretty=True,
+        )
+        self.validate_all(
+            "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+            read={
+                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+            },
+            write={
+                "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+                "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
+            },
+        )
+        self.validate_all(
+            """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
+            write={
+                "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
+                "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
+            },
+        )