diff options
Diffstat (limited to 'tests/dialects/test_tsql.py')
| -rw-r--r-- | tests/dialects/test_tsql.py | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index f43b41b..c27b7fa 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -6,10 +6,55 @@ class TestTSQL(Validator): dialect = "tsql" def test_tsql(self): + self.validate_all( + "CREATE TABLE x ( A INTEGER NOT NULL, B INTEGER NULL )", + write={ + "tsql": "CREATE TABLE x (A INTEGER NOT NULL, B INTEGER NULL)", + "hive": "CREATE TABLE x (A INT NOT NULL, B INT)", + }, + ) + + self.validate_identity( + 'CREATE TABLE x (CONSTRAINT "pk_mytable" UNIQUE NONCLUSTERED (a DESC)) ON b (c)' + ) + + self.validate_identity( + """ + CREATE TABLE x( + [zip_cd] [varchar](5) NULL NOT FOR REPLICATION + CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED + ([zip_cd_mkey] ASC) + WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY] + ) ON [PRIMARY] + """, + 'CREATE TABLE x ("zip_cd" VARCHAR(5) NULL NOT FOR REPLICATION CONSTRAINT "pk_mytable" PRIMARY KEY CLUSTERED ("zip_cd_mkey") WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON "PRIMARY") ON "PRIMARY"', + ) + + self.validate_identity( + "CREATE TABLE tbl (a AS (x + 1) PERSISTED, b AS (y + 2), c AS (y / 3) PERSISTED NOT NULL)" + ) + + self.validate_identity( + "CREATE TABLE [db].[tbl]([a] [int])", 'CREATE TABLE "db"."tbl" ("a" INTEGER)' + ) + projection = parse_one("SELECT a = 1", read="tsql").selects[0] projection.assert_is(exp.Alias) projection.args["alias"].assert_is(exp.Identifier) + self.validate_all( + "IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL DROP TABLE #TempTableName", + write={ + "tsql": "DROP TABLE IF EXISTS #TempTableName", + "spark": "DROP TABLE IF EXISTS TempTableName", + }, + ) + + self.validate_identity( + "MERGE INTO mytable WITH (HOLDLOCK) AS T USING mytable_merge AS S " + "ON (T.user_id = S.user_id) WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (S.c1, S.c2)" + ) + self.validate_identity("UPDATE STATISTICS x") self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b INTO @y FROM y") self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b FROM y") self.validate_identity("INSERT INTO x (y) OUTPUT x.a, x.b INTO l SELECT * FROM z") @@ -397,8 +442,68 @@ class TestTSQL(Validator): }, ) + self.validate_all( + "CAST(x AS BOOLEAN)", + write={"tsql": "CAST(x AS BIT)"}, + ) + def test_ddl(self): self.validate_all( + "CREATE TABLE tbl (id INTEGER IDENTITY PRIMARY KEY)", + read={ + "mysql": "CREATE TABLE tbl (id INT AUTO_INCREMENT PRIMARY KEY)", + "tsql": "CREATE TABLE tbl (id INTEGER IDENTITY PRIMARY KEY)", + }, + ) + self.validate_all( + "CREATE TABLE tbl (id INTEGER NOT NULL IDENTITY(10, 1) PRIMARY KEY)", + read={ + "postgres": "CREATE TABLE tbl (id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10) PRIMARY KEY)", + "tsql": "CREATE TABLE tbl (id INTEGER NOT NULL IDENTITY(10, 1) PRIMARY KEY)", + }, + ) + self.validate_all( + "IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('db.tbl') AND name = 'idx') EXEC('CREATE INDEX idx ON db.tbl')", + read={ + "": "CREATE INDEX IF NOT EXISTS idx ON db.tbl", + }, + ) + + self.validate_all( + "IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'foo') EXEC('CREATE SCHEMA foo')", + read={ + "": "CREATE SCHEMA IF NOT EXISTS foo", + }, + ) + self.validate_all( + "IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'foo') EXEC('CREATE TABLE foo (a INTEGER)')", + read={ + "": "CREATE TABLE IF NOT EXISTS foo (a INTEGER)", + }, + ) + + self.validate_all( + "CREATE OR ALTER VIEW a.b AS SELECT 1", + read={ + "": "CREATE OR REPLACE VIEW a.b AS SELECT 1", + }, + write={ + "tsql": "CREATE OR ALTER VIEW a.b AS SELECT 1", + }, + ) + + self.validate_all( + "ALTER TABLE a ADD b INTEGER, c INTEGER", + read={ + "": "ALTER TABLE a ADD COLUMN b INT, ADD COLUMN c INT", + }, + write={ + "": "ALTER TABLE a ADD COLUMN b INT, ADD COLUMN c INT", + "tsql": "ALTER TABLE a ADD b INTEGER, c INTEGER", + }, + ) + + self.validate_all( "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))", write={ "spark": "CREATE TEMPORARY TABLE mytemp (a INT, b CHAR(2), c TIMESTAMP, d FLOAT)", @@ -898,6 +1003,9 @@ WHERE ) def test_iif(self): + self.validate_identity( + "SELECT IF(cond, 'True', 'False')", "SELECT IIF(cond, 'True', 'False')" + ) self.validate_identity("SELECT IIF(cond, 'True', 'False')") self.validate_all( "SELECT IIF(cond, 'True', 'False');", @@ -961,9 +1069,12 @@ WHERE ) def test_format(self): + self.validate_identity("SELECT FORMAT(foo, 'dddd', 'de-CH')") + self.validate_identity("SELECT FORMAT(EndOfDayRate, 'N', 'en-us')") self.validate_identity("SELECT FORMAT('01-01-1991', 'd.mm.yyyy')") self.validate_identity("SELECT FORMAT(12345, '###.###.###')") self.validate_identity("SELECT FORMAT(1234567, 'f')") + self.validate_all( "SELECT FORMAT(1000000.01,'###,###.###')", write={"spark": "SELECT FORMAT_NUMBER(1000000.01, '###,###.###')"}, |