1 files changed, 72 insertions, 0 deletions
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
new file mode 100644
index 0000000..a0576de
--- /dev/null
+++ b/tests/dialects/test_sqlite.py
@@ -0,0 +1,72 @@
+from tests.dialects.test_dialect import Validator
+
+
+class TestSQLite(Validator):
+    dialect = "sqlite"
+
+    def test_ddl(self):
+        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)",
+            },
+        )
+        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_all(
+            "SELECT CAST([a].[b] AS SMALLINT) FROM foo",
+            write={
+                "sqlite": 'SELECT CAST("a"."b" AS INTEGER) FROM foo',
+                "spark": "SELECT CAST(`a`.`b` AS SHORT) FROM foo",
+            },
+        )
+        self.validate_all(
+            "EDITDIST3(col1, col2)",
+            read={
+                "sqlite": "EDITDIST3(col1, col2)",
+                "spark": "LEVENSHTEIN(col1, col2)",
+            },
+            write={
+                "sqlite": "EDITDIST3(col1, col2)",
+                "spark": "LEVENSHTEIN(col1, col2)",
+            },
+        )
+        self.validate_all(
+            "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname ASC NULLS LAST, lname",
+            write={
+                "spark": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname",
+                "sqlite": "SELECT fname, lname, age FROM person ORDER BY age DESC NULLS FIRST, fname NULLS LAST, lname",
+            },
+        )