diff options
Diffstat (limited to 'tests/dialects/test_tsql.py')
| -rw-r--r-- | tests/dialects/test_tsql.py | 123 |
1 files changed, 88 insertions, 35 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 4a475f6..45a4657 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -1,5 +1,4 @@ from sqlglot import exp, parse, parse_one -from sqlglot.parser import logger as parser_logger from tests.dialects.test_dialect import Validator from sqlglot.errors import ParseError @@ -8,6 +7,8 @@ class TestTSQL(Validator): dialect = "tsql" def test_tsql(self): + self.validate_identity("CREATE view a.b.c", "CREATE VIEW b.c") + self.validate_identity("DROP view a.b.c", "DROP VIEW b.c") self.validate_identity("ROUND(x, 1, 0)") self.validate_identity("EXEC MyProc @id=7, @name='Lochristi'", check_command_warning=True) # https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187879(v=sql.105)?redirectedfrom=MSDN @@ -29,6 +30,9 @@ class TestTSQL(Validator): self.validate_identity("1 AND true", "1 <> 0 AND (1 = 1)") self.validate_identity("CAST(x AS int) OR y", "CAST(x AS INTEGER) <> 0 OR y <> 0") self.validate_identity("TRUNCATE TABLE t1 WITH (PARTITIONS(1, 2 TO 5, 10 TO 20, 84))") + self.validate_identity( + "COPY INTO test_1 FROM 'path' WITH (FILE_TYPE = 'CSV', CREDENTIAL = (IDENTITY = 'Shared Access Signature', SECRET = 'token'), FIELDTERMINATOR = ';', ROWTERMINATOR = '0X0A', ENCODING = 'UTF8', DATEFORMAT = 'ymd', MAXERRORS = 10, ERRORFILE = 'errorsfolder', IDENTITY_INSERT = 'ON')" + ) self.validate_all( "SELECT IIF(cond <> 0, 'True', 'False')", @@ -188,16 +192,9 @@ class TestTSQL(Validator): ) self.validate_all( - """ - CREATE TABLE x( - [zip_cd] [varchar](5) NULL NOT FOR REPLICATION, - [zip_cd_mkey] [varchar](5) NOT NULL, - CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) - WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF) ON [INDEX] - ) ON [SECONDARY] - """, + """CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]""", write={ - "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]", + "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]", "spark2": "CREATE TABLE x (`zip_cd` VARCHAR(5), `zip_cd_mkey` VARCHAR(5) NOT NULL, CONSTRAINT `pk_mytable` PRIMARY KEY (`zip_cd_mkey`))", }, ) @@ -256,7 +253,7 @@ class TestTSQL(Validator): self.validate_identity("SELECT * FROM ##foo") self.validate_identity("SELECT a = 1", "SELECT 1 AS a") self.validate_identity( - "DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'", check_command_warning=True + "DECLARE @TestVariable AS VARCHAR(100) = 'Save Our Planet'", ) self.validate_identity( "SELECT a = 1 UNION ALL SELECT a = b", "SELECT 1 AS a UNION ALL SELECT b AS a" @@ -458,6 +455,7 @@ class TestTSQL(Validator): self.validate_identity("CAST(x AS IMAGE)") self.validate_identity("CAST(x AS SQL_VARIANT)") self.validate_identity("CAST(x AS BIT)") + self.validate_all( "CAST(x AS DATETIME2)", read={ @@ -485,7 +483,7 @@ class TestTSQL(Validator): }, ) - def test__types_ints(self): + def test_types_ints(self): self.validate_all( "CAST(X AS INT)", write={ @@ -518,10 +516,14 @@ class TestTSQL(Validator): self.validate_all( "CAST(X AS TINYINT)", + read={ + "duckdb": "CAST(X AS UTINYINT)", + }, write={ - "hive": "CAST(X AS TINYINT)", - "spark2": "CAST(X AS TINYINT)", - "spark": "CAST(X AS TINYINT)", + "duckdb": "CAST(X AS UTINYINT)", + "hive": "CAST(X AS SMALLINT)", + "spark2": "CAST(X AS SMALLINT)", + "spark": "CAST(X AS SMALLINT)", "tsql": "CAST(X AS TINYINT)", }, ) @@ -761,22 +763,44 @@ class TestTSQL(Validator): expression.sql(dialect="tsql"), "ALTER TABLE dbo.DocExe DROP CONSTRAINT FK_Column_B" ) - for clusterd_keyword in ("CLUSTERED", "NONCLUSTERED"): + for clustered_keyword in ("CLUSTERED", "NONCLUSTERED"): self.validate_identity( 'CREATE TABLE "dbo"."benchmark" (' '"name" CHAR(7) NOT NULL, ' '"internal_id" VARCHAR(10) NOT NULL, ' - f'UNIQUE {clusterd_keyword} ("internal_id" ASC))', + f'UNIQUE {clustered_keyword} ("internal_id" ASC))', "CREATE TABLE [dbo].[benchmark] (" "[name] CHAR(7) NOT NULL, " "[internal_id] VARCHAR(10) NOT NULL, " - f"UNIQUE {clusterd_keyword} ([internal_id] ASC))", + f"UNIQUE {clustered_keyword} ([internal_id] ASC))", ) self.validate_identity( + "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)" + ) + self.validate_identity( + "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE)" + ) + self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF") + self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'") + self.validate_identity( + "ALTER TABLE tbl SET DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS)" + ) + self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON") + self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF") + + self.validate_identity( "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < CURRENT_TIMESTAMP - 7 END", "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < GETDATE() - 7 END", ) + + self.validate_all( + "CREATE TABLE [#temptest] (name VARCHAR)", + read={ + "duckdb": "CREATE TEMPORARY TABLE 'temptest' (name VARCHAR)", + "tsql": "CREATE TABLE [#temptest] (name VARCHAR)", + }, + ) self.validate_all( "CREATE TABLE tbl (id INTEGER IDENTITY PRIMARY KEY)", read={ @@ -889,8 +913,7 @@ class TestTSQL(Validator): def test_udf(self): self.validate_identity( - "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)", - check_command_warning=True, + "DECLARE @DWH_DateCreated AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)", ) self.validate_identity( "CREATE PROCEDURE foo @a INTEGER, @b INTEGER AS SELECT @a = SUM(bla) FROM baz AS bar" @@ -962,9 +985,9 @@ WHERE BEGIN SET XACT_ABORT ON; - DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104); - DECLARE @DWH_DateModified DATETIME = CONVERT(DATETIME, getdate(), 104); - DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (SYSTEM_USER); + DECLARE @DWH_DateCreated AS DATETIME = CONVERT(DATETIME, getdate(), 104); + DECLARE @DWH_DateModified DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104); + DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (CURRENT_USER()); DECLARE @DWH_IdUserModified INTEGER = SUSER_ID (SYSTEM_USER); DECLARE @SalesAmountBefore float; @@ -974,18 +997,17 @@ WHERE expected_sqls = [ "CREATE PROCEDURE [TRANSF].[SP_Merge_Sales_Real] @Loadid INTEGER, @NumberOfRows INTEGER AS BEGIN SET XACT_ABORT ON", - "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)", - "DECLARE @DWH_DateModified DATETIME = CONVERT(DATETIME, getdate(), 104)", - "DECLARE @DWH_IdUserCreated INTEGER = SUSER_ID (SYSTEM_USER)", - "DECLARE @DWH_IdUserModified INTEGER = SUSER_ID (SYSTEM_USER)", - "DECLARE @SalesAmountBefore float", + "DECLARE @DWH_DateCreated AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)", + "DECLARE @DWH_DateModified AS DATETIME2 = CONVERT(DATETIME2, GETDATE(), 104)", + "DECLARE @DWH_IdUserCreated AS INTEGER = SUSER_ID(CURRENT_USER())", + "DECLARE @DWH_IdUserModified AS INTEGER = SUSER_ID(CURRENT_USER())", + "DECLARE @SalesAmountBefore AS FLOAT", "SELECT @SalesAmountBefore = SUM(SalesAmount) FROM TRANSF.[Pre_Merge_Sales_Real] AS S", "END", ] - with self.assertLogs(parser_logger): - for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): - self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): + self.assertEqual(expr.sql(dialect="tsql"), expected_sql) sql = """ CREATE PROC [dbo].[transform_proc] AS @@ -999,14 +1021,13 @@ WHERE """ expected_sqls = [ - "CREATE PROC [dbo].[transform_proc] AS DECLARE @CurrentDate VARCHAR(20)", + "CREATE PROC [dbo].[transform_proc] AS DECLARE @CurrentDate AS VARCHAR(20)", "SET @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 120)", "CREATE TABLE [target_schema].[target_table] (a INTEGER) WITH (DISTRIBUTION=REPLICATE, HEAP)", ] - with self.assertLogs(parser_logger): - for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): - self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): + self.assertEqual(expr.sql(dialect="tsql"), expected_sql) def test_charindex(self): self.validate_identity( @@ -1812,3 +1833,35 @@ FROM OPENJSON(@json) WITH ( "duckdb": "WITH t1(c) AS (SELECT 1), t2 AS (SELECT CAST(c AS INTEGER) FROM t1) SELECT * FROM t2", }, ) + + def test_declare(self): + # supported cases + self.validate_identity("DECLARE @X INT", "DECLARE @X AS INTEGER") + self.validate_identity("DECLARE @X INT = 1", "DECLARE @X AS INTEGER = 1") + self.validate_identity( + "DECLARE @X INT, @Y VARCHAR(10)", "DECLARE @X AS INTEGER, @Y AS VARCHAR(10)" + ) + self.validate_identity( + "declare @X int = (select col from table where id = 1)", + "DECLARE @X AS INTEGER = (SELECT col FROM table WHERE id = 1)", + ) + self.validate_identity( + "declare @X TABLE (Id INT NOT NULL, Name VARCHAR(100) NOT NULL)", + "DECLARE @X AS TABLE (Id INTEGER NOT NULL, Name VARCHAR(100) NOT NULL)", + ) + self.validate_identity( + "declare @X TABLE (Id INT NOT NULL, constraint PK_Id primary key (Id))", + "DECLARE @X AS TABLE (Id INTEGER NOT NULL, CONSTRAINT PK_Id PRIMARY KEY (Id))", + ) + self.validate_identity( + "declare @X UserDefinedTableType", + "DECLARE @X AS UserDefinedTableType", + ) + self.validate_identity( + "DECLARE @MyTableVar TABLE (EmpID INT NOT NULL, PRIMARY KEY CLUSTERED (EmpID), UNIQUE NONCLUSTERED (EmpID), INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID))", + check_command_warning=True, + ) + self.validate_identity( + "DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID", + check_command_warning=True, + ) |