diff options
Diffstat (limited to 'tests/dialects/test_tsql.py')
| -rw-r--r-- | tests/dialects/test_tsql.py | 75 |
1 files changed, 67 insertions, 8 deletions
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 065cdd0..f0a590f 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -33,15 +33,15 @@ class TestTSQL(Validator): ) self.validate_all( - "SELECT DATEPART(year, TRY_CAST('2017-01-01' AS DATE))", + "SELECT DATEPART(year, CAST('2017-01-01' AS DATE))", read={"postgres": "SELECT DATE_PART('year', '2017-01-01'::DATE)"}, ) self.validate_all( - "SELECT DATEPART(month, TRY_CAST('2017-03-01' AS DATE))", + "SELECT DATEPART(month, CAST('2017-03-01' AS DATE))", read={"postgres": "SELECT DATE_PART('month', '2017-03-01'::DATE)"}, ) self.validate_all( - "SELECT DATEPART(day, TRY_CAST('2017-01-02' AS DATE))", + "SELECT DATEPART(day, CAST('2017-01-02' AS DATE))", read={"postgres": "SELECT DATE_PART('day', '2017-01-02'::DATE)"}, ) self.validate_all( @@ -54,7 +54,7 @@ class TestTSQL(Validator): self.validate_all( "CONVERT(INT, CONVERT(NUMERIC, '444.75'))", write={ - "mysql": "CAST(CAST('444.75' AS DECIMAL) AS INT)", + "mysql": "CAST(CAST('444.75' AS DECIMAL) AS SIGNED)", "tsql": "CAST(CAST('444.75' AS NUMERIC) AS INTEGER)", }, ) @@ -389,8 +389,59 @@ class TestTSQL(Validator): }, ) + def test_ddl(self): + self.validate_all( + "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))", + write={ + "tsql": "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIMESTAMP(4), d FLOAT(24))" + }, + ) + + def test_transaction(self): + # BEGIN { TRAN | TRANSACTION } + # [ { transaction_name | @tran_name_variable } + # [ WITH MARK [ 'description' ] ] + # ] + # [ ; ] + self.validate_identity("BEGIN TRANSACTION") + self.validate_all("BEGIN TRAN", write={"tsql": "BEGIN TRANSACTION"}) + self.validate_identity("BEGIN TRANSACTION transaction_name") + self.validate_identity("BEGIN TRANSACTION @tran_name_variable") + self.validate_identity("BEGIN TRANSACTION transaction_name WITH MARK 'description'") + + def test_commit(self): + # COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] [ ; ] + + self.validate_all("COMMIT", write={"tsql": "COMMIT TRANSACTION"}) + self.validate_all("COMMIT TRAN", write={"tsql": "COMMIT TRANSACTION"}) + self.validate_identity("COMMIT TRANSACTION") + self.validate_identity("COMMIT TRANSACTION transaction_name") + self.validate_identity("COMMIT TRANSACTION @tran_name_variable") + + self.validate_identity( + "COMMIT TRANSACTION @tran_name_variable WITH (DELAYED_DURABILITY = ON)" + ) + self.validate_identity( + "COMMIT TRANSACTION transaction_name WITH (DELAYED_DURABILITY = OFF)" + ) + + def test_rollback(self): + # Applies to SQL Server and Azure SQL Database + # ROLLBACK { TRAN | TRANSACTION } + # [ transaction_name | @tran_name_variable + # | savepoint_name | @savepoint_variable ] + # [ ; ] + self.validate_all("ROLLBACK", write={"tsql": "ROLLBACK TRANSACTION"}) + self.validate_all("ROLLBACK TRAN", write={"tsql": "ROLLBACK TRANSACTION"}) + self.validate_identity("ROLLBACK TRANSACTION") + self.validate_identity("ROLLBACK TRANSACTION transaction_name") + self.validate_identity("ROLLBACK TRANSACTION @tran_name_variable") + def test_udf(self): self.validate_identity( + "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)" + ) + self.validate_identity( "CREATE PROCEDURE foo @a INTEGER, @b INTEGER AS SELECT @a = SUM(bla) FROM baz AS bar" ) self.validate_identity( @@ -446,6 +497,12 @@ WHERE pretty=True, ) + def test_procedure_keywords(self): + self.validate_identity("BEGIN") + self.validate_identity("END") + self.validate_identity("SET XACT_ABORT ON") + + def test_fullproc(self): sql = """ CREATE procedure [TRANSF].[SP_Merge_Sales_Real] @Loadid INTEGER @@ -696,14 +753,14 @@ WHERE self.validate_all( "SELECT CONVERT(VARCHAR(10), testdb.dbo.test.x, 120) y FROM testdb.dbo.test", write={ - "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS VARCHAR(10)) AS y FROM testdb.dbo.test", + "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS CHAR(10)) AS y FROM testdb.dbo.test", "spark": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(10)) AS y FROM testdb.dbo.test", }, ) self.validate_all( "SELECT CONVERT(VARCHAR(10), y.x) z FROM testdb.dbo.test y", write={ - "mysql": "SELECT CAST(y.x AS VARCHAR(10)) AS z FROM testdb.dbo.test AS y", + "mysql": "SELECT CAST(y.x AS CHAR(10)) AS z FROM testdb.dbo.test AS y", "spark": "SELECT CAST(y.x AS VARCHAR(10)) AS z FROM testdb.dbo.test AS y", }, ) @@ -838,7 +895,8 @@ WHERE write={"spark": "SELECT FORMAT_NUMBER(1000000.01, '###,###.###')"}, ) self.validate_all( - "SELECT FORMAT(1234567, 'f')", write={"spark": "SELECT FORMAT_NUMBER(1234567, 'f')"} + "SELECT FORMAT(1234567, 'f')", + write={"spark": "SELECT FORMAT_NUMBER(1234567, 'f')"}, ) self.validate_all( "SELECT FORMAT('01-01-1991', 'dd.mm.yyyy')", @@ -853,7 +911,8 @@ WHERE write={"spark": "SELECT DATE_FORMAT(date_col, 'MMMM d')"}, ) self.validate_all( - "SELECT FORMAT(num_col, 'c')", write={"spark": "SELECT FORMAT_NUMBER(num_col, 'c')"} + "SELECT FORMAT(num_col, 'c')", + write={"spark": "SELECT FORMAT_NUMBER(num_col, 'c')"}, ) def test_string(self): |