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));
}
private void parseSubQueryColumnDefinition(TCreateTableSqlStatement createTable, TSelectSqlStatement stmt, TableMetaData tableMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(createTable, stmt.LeftStmt, tableMetaData, columnImpactModel);
parseSubQueryColumnDefinition(createTable, stmt.RightStmt, tableMetaData, columnImpactModel);
}
else
{
int columnCount = stmt.ResultColumnList.size();
string[] aliasNames = new string[columnCount];
if (createTable.ColumnList != null && createTable.ColumnList.size() > 0)
{
columnCount = createTable.ColumnList.size();
aliasNames = new string[columnCount];
TColumnDefinitionList items = createTable.ColumnList;
for (int i = 0; i < items.size(); i++)
{
aliasNames[i] = items.getColumn(i).ToString();
}
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
}
private void parseStatement(TCustomSqlStatement stmt)
{
if (!stmtList.Contains(stmt.ToString()))
{
stmtList.Add(stmt.ToString());
}
else
{
return;
}
if (stmt is TCreateViewSqlStatement)
{
TCreateViewSqlStatement createView = ((TCreateViewSqlStatement)stmt);
parseCreateView(createView);
}
else if (stmt is TCreateTableSqlStatement && ((TCreateTableSqlStatement)stmt).SubQuery != null)
{
TCreateTableSqlStatement createTable = ((TCreateTableSqlStatement)stmt);
parseCreateTable(createTable);
}
else if (stmt is TInsertSqlStatement && ((TInsertSqlStatement)stmt).SubQuery != null)
{
TInsertSqlStatement insert = ((TInsertSqlStatement)stmt);
parseInsertStmt(insert);
}
if (stmt is TUseDatabase)
{
TUseDatabase use = (TUseDatabase)stmt;
database = use.DatabaseName.ToString();
}
}
private static void AnalyzeCreateTableStmt(TCreateTableSqlStatement stmt)
{
Console.WriteLine(string.Format("Table name: {0}", stmt.TargetTable.ToString()));
Console.WriteLine("Columns:");
TColumnDefinition column;
for (int i = 0; i < stmt.ColumnList.Count; i++)
{
column = stmt.ColumnList.getColumn(i);
Console.WriteLine("\tname: {0}", column.ColumnName.ToString());
Console.WriteLine("\tdatatype: {0}", column.Datatype.ToString());
if (column.DefaultExpression != null)
{
Console.WriteLine("\tdefault: {0}", column.DefaultExpression.ToString());
}
if (column.Null)
{
Console.WriteLine("\tnull: yes");
}
if (column.Constraints != null)
{
Console.WriteLine("\tinline constraints:");
for (int j = 0; j < column.Constraints.Count; j++)
{
PrintConstraint(column.Constraints.getConstraint(j), true);
}
}
Console.WriteLine();
}
}
public void testMinusSetToString()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE TABLE X AS \n" + "SELECT A FROM B\n" + "minus\n" + "SELECT A FROM C";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement ct = (TCreateTableSqlStatement)sqlparser.sqlstatements.get(0);
TSelectSqlStatement select = ct.SubQuery;
Assert.IsTrue(!string.ReferenceEquals(select.ToString(), null));
}
public void testCreateTable()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum);
sqlparser.sqltext = "CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS \n" + "EXECUTE recentfilms('2007-01-01');";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement createTableSqlStatement = (TCreateTableSqlStatement)sqlparser.sqlstatements.get(0);
TExecutePreparedStatement executePreparedStatement = createTableSqlStatement.ExecutePreparedStatement;
Assert.IsTrue(executePreparedStatement.StatementName.ToString().Equals("recentfilms", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(executePreparedStatement.Parameters.getExpression(0).ToString().Equals("'2007-01-01'", 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));
}
private void parseCreateTable(TCreateTableSqlStatement createTable)
{
if (createTable.TableName != null)
{
string tableName = createTable.TableName.TableString;
string tableSchema = createTable.TableName.SchemaString;
string databaseName = createTable.TableName.DatabaseString;
TableMetaData tableMetaData = new TableMetaData(vendor, strict);
tableMetaData.Name = tableName;
tableMetaData.SchemaName = tableSchema;
if (isNotEmpty(databaseName))
{
tableMetaData.CatalogName = databaseName;
}
else
{
tableMetaData.CatalogName = database;
}
tableMetaData.View = false;
if (!tableColumns.ContainsKey(tableMetaData))
{
tableColumns[tableMetaData] = new List <ColumnMetaData>();
}
else
{
IList <TableMetaData> tables = new List <TableMetaData>(tableColumns.Keys);
tableMetaData = (TableMetaData)tables[tables.IndexOf(tableMetaData)];
tableMetaData.View = false;
}
if (createTable.SubQuery != null)
{
ColumnImpact impact = new ColumnImpact(removeParentheses(createTable.SubQuery.ToString().Trim()), createTable.dbvendor, tableColumns, strict);
impact.ignoreTopSelect(true);
impact.Debug = false;
impact.ShowUIInfo = true;
impact.TraceErrorMessage = false;
impact.impactSQL();
ColumnImpactModel columnImpactModel = impact.generateModel();
parseSubQueryColumnDefinition(createTable, createTable.SubQuery, tableMetaData, columnImpactModel);
}
}
}
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));
}
private void analyzeSqlStatement(procedureInfo procedureInfo, TCustomSqlStatement stmt)
{
if (stmt is TMssqlBlock)
{
TMssqlBlock block = (TMssqlBlock)stmt;
if (block.BodyStatements != null)
{
for (int i = 0; i < block.BodyStatements.size(); i++)
{
analyzeSqlStatement(procedureInfo, block.BodyStatements.get(i));
}
}
}
else if (stmt is TMssqlIfElse)
{
TMssqlIfElse ifElse = (TMssqlIfElse)stmt;
if (ifElse.Stmt != null)
{
analyzeSqlStatement(procedureInfo, ifElse.Stmt);
}
if (ifElse.Condition != null)
{
}
if (ifElse.ElseStmt != null)
{
analyzeSqlStatement(procedureInfo, ifElse.ElseStmt);
}
}
else if (stmt is TMssqlDeclare)
{
TMssqlDeclare declareStmt = (TMssqlDeclare)stmt;
if (declareStmt.Subquery != null && declareStmt.Subquery.ToString().Trim().Length > 0)
{
analyzeSqlStatement(procedureInfo, declareStmt.Subquery);
}
}
else if (stmt is TMssqlExecute && ((TMssqlExecute)stmt).ModuleName != null)
{
TMssqlExecute executeStmt = (TMssqlExecute)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.SP;
operateInfo.objectUsed = executeStmt.ModuleName.ToString().Trim();
operateInfo.usageType = usageType.Exec;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TCreateTableSqlStatement)
{
TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement)stmt;
TColumnDefinitionList columns = createStmt.ColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = createStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Create;
for (int i = 0; i < columns.size(); i++)
{
TColumnDefinition column = columns.getColumn(i);
operateInfo.columns.Add(column.ColumnName.ToString());
}
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TInsertSqlStatement)
{
TInsertSqlStatement insertStmt = (TInsertSqlStatement)stmt;
TObjectNameList columns = insertStmt.ColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = insertStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Insert;
if (columns != null)
{
for (int i = 0; i < columns.size(); i++)
{
TObjectName column = columns.getObjectName(i);
operateInfo.columns.Add(column.ToString());
}
}
procedureInfo.operates.Add(operateInfo);
// if (insertStmt.ExecStmt != null)
// {
// analyzeSqlStatement(procedureInfo, insertStmt.ExecStmt);
// }
}
else if (stmt is TUpdateSqlStatement)
{
TUpdateSqlStatement updateStmt = (TUpdateSqlStatement)stmt;
TResultColumnList columns = updateStmt.ResultColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = updateStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Update;
for (int i = 0; i < columns.size(); i++)
{
TResultColumn column = columns.getResultColumn(i);
operateInfo.columns.Add(column.Expr.LeftOperand.ToString());
}
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TDeleteSqlStatement)
{
TDeleteSqlStatement deleteStmt = (TDeleteSqlStatement)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = deleteStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Delete;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TMssqlDropTable)
{
TMssqlDropTable dropStmt = (TMssqlDropTable)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = dropStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Drop;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TDropTableSqlStatement)
{
TDropTableSqlStatement dropStmt = (TDropTableSqlStatement)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = dropStmt.TableName.ToString().Trim();
operateInfo.usageType = usageType.Drop;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TSelectSqlStatement)
{
TSelectSqlStatement selectStmt = (TSelectSqlStatement)stmt;
List <columnInfo> columnInfos = new List <columnInfo>();
List <tableInfo> tableInfos = new List <tableInfo>();
tableTokensInStmt(columnInfos, tableInfos, selectStmt);
LinkedHashMap <tableInfo, List <columnInfo> > columnMap = new LinkedHashMap <tableInfo, List <columnInfo> >();
for (int i = 0; i < columnInfos.Count; i++)
{
columnInfo column = columnInfos[i];
tableInfo table = column.table;
if (columnMap.ContainsKey(table))
{
List <columnInfo> columns = (List <columnInfo>)columnMap[table];
bool flag = false;
foreach (columnInfo temp in columns)
{
if (temp.ToString().Equals(column.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
flag = true;
break;
}
}
if (!flag)
{
columns.Add(column);
}
}
else
{
List <columnInfo> columns = new List <columnInfo>();
columnMap[table] = columns;
columns.Add(column);
}
}
for (int i = 0; i < tableInfos.Count; i++)
{
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = tableInfos[i].ToString();
if (tableInfos[i].stmt is TSelectSqlStatement && ((TSelectSqlStatement)tableInfos[i].stmt).IntoClause != null)
{
operateInfo.usageType = usageType.Insert;
}
else
{
operateInfo.usageType = usageType.Read;
}
if (columnMap.ContainsKey(tableInfos[i]))
{
foreach (columnInfo column in (List <columnInfo>)columnMap[tableInfos[i]])
{
operateInfo.columns.Add(column.ToString());
operateInfo.objectUsed = column.table.ToString();
}
}
procedureInfo.operates.Add(operateInfo);
}
}
}
