summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_sqlite.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_sqlite.py')
-rw-r--r--tests/dialects/test_sqlite.py132
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)",
+ },
+ )
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-23 06:55:31 +0000