public void TestClusterBy()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"create table ""TestSchema"".""TestTable""(
col1 int,
""col2"" int
)
cluster
by
(
""col2""
,
col1
)";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 1);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoClusterBy);
Assert.IsTrue(tableOption.ExpressionList.Count == 2);
// Console.WriteLine(tableOption.ExpressionList.Count);
Assert.IsTrue(tableOption.ExpressionList[0].ToString().Equals("\"col2\"", StringComparison.CurrentCultureIgnoreCase));
}
public void TestDateRetentionTime()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"CREATE TABLE ""TestTable""
(
""Col1"" int NOT NULL
)
DATA_RETENTION_TIME_IN_DAYS = 12; ";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 1);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoDateRetentionTimeInDays);
Assert.IsTrue(tableOption.DateRetentionInDays.ToString().Equals("12", StringComparison.CurrentCultureIgnoreCase));
}
public void TestStageCopyOptions()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"CREATE TABLE ""TestTable""
(
""Col1"" int NOT NULL
)
STAGE_COPY_OPTIONS =
(
ON_ERROR = CONTINUE
); ";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 1);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoStageCopyOptions);
Assert.IsTrue(tableOption.CopyOptions.ToString().Trim().Equals("ON_ERROR = CONTINUE", StringComparison.CurrentCultureIgnoreCase));
}
public void TestStageFileFormat()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"CREATE TABLE ""TestTable1""
(
""Col1"" int NOT NULL
)
STAGE_FILE_FORMAT =
(
RECORD_DELIMITER = ''''
ESCAPE_UNENCLOSED_FIELD = ''''
ESCAPE = ''
NULL_IF = ('a''a', ''''bb'' )
FIELD_DELIMITER = ')'
FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
COMMENT = 'Test'; ";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 2);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoStageFileFormat);
// Console.WriteLine(tableOption.StageFileFormat.ToString().Trim());
Assert.IsTrue(tableOption.StageFileFormat.ToString().Trim().Equals(@"RECORD_DELIMITER = ''''
ESCAPE_UNENCLOSED_FIELD = ''''
ESCAPE = ''
NULL_IF = ('a''a', ''''bb'' )
FIELD_DELIMITER = ')'
FIELD_OPTIONALLY_ENCLOSED_BY = '\''"
, StringComparison.CurrentCultureIgnoreCase));
}
public void TestTableComment()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"CREATE TABLE ""TestTable""
(
""Col1"" int NOT NULL COMMENT 'Test comment.',
""Col2"" int NOT NULL COMMENT 'Test comment 2.'
)
COMMENT = 'Table comment'; ";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 1);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoComment);
Assert.IsTrue(tableOption.CommentToken.ToString().Equals("'Table comment'", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(sql.TableComment.ToString().Equals("'Table comment'", StringComparison.CurrentCultureIgnoreCase));
TColumnDefinition cd = sql.ColumnList.getColumn(0);
Assert.IsTrue(cd.Comment.ToString().Equals("'Test comment.'", StringComparison.CurrentCultureIgnoreCase));
}