public void Select_Multiple_Fields_With_Aliases()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>(
"create view v1 as select field, fielda a, field2 as b, alias = fie3ld from table"
).First();
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(4, statement.Fields.Count);
Assert.AreEqual("field", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Fields[0].Alias.Name);
Assert.AreEqual("fielda", statement.Fields[1].Expression.Value);
Assert.AreEqual("a", statement.Fields[1].Alias.Name);
Assert.AreEqual("field2", statement.Fields[2].Expression.Value);
Assert.AreEqual("b", statement.Fields[2].Alias.Name);
Assert.AreEqual("fie3ld", statement.Fields[3].Expression.Value);
Assert.AreEqual("alias", statement.Fields[3].Alias.Name);
}
public override void ExplicitVisit(CreateViewStatement view)
{
if (IsSupportedForCurrentType(view.GetType()))
{
Name = view.SchemaObjectName;
}
}
private static void LoadViewOutputColumnsX(TestSchema schema, TestView vw, CreateViewStatement stmt_CreateFunction)
{
ProcedureGenerator.LoadViewOutputColumns(schema, vw.Body, (col) =>
{
vw.AddViewColumn(col.OutputColumnName, col.ColumnType.ColumnDbType, col.ColumnType.AllowNull, col.OutputColumnName);
});
}
protected override void BuildStatement(SqlCodeObjectBuilder builder)
{
var queryExpression = (SqlQueryExpression)ExpressionBuilder.Build(QueryExpression);
var statement = new CreateViewStatement(ViewName.Name, ColumnNames, queryExpression);
statement.ReplaceIfExists = ReplaceIfExists;
builder.AddObject(statement);
}
public override void ExplicitVisit(CreateViewStatement node)
{
foreach (var viewOption in node.ViewOptions)
{
if (viewOption.OptionKind == ViewOptionKind.SchemaBinding)
{
this.IndexedViews.Add(node);
this.WithBindings.Add(viewOption);
break;
}
}
}
void AlterView(Name tokenName, IStatement definitionStatement)
{
DropViewStatement dropViewStatement = Sql.DropView(tokenName, true);
VisitStatement(dropViewStatement);
State.WriteStatementTerminator();
CreateViewStatement createViewStatement = Sql.CreateView(tokenName, definitionStatement);
VisitStatement(createViewStatement);
State.WriteStatementTerminator();
}
public void Select_Top_Missing_Top_Param_StarField()
{
try
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select top * from table").First();
Assert.Fail();
}
catch (SyntaxException ex)
{
Assert.AreEqual("Expected integer but found: '*'", ex.Message);
}
}
public void TestNoParserException()
{
// TODO: needs to be moved into a ParserFactory specific unit test
try
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("merge from table").First();
}
catch (ParserNotImplementedException ex)
{
Assert.AreEqual("No parser exists for statement type: merge", ex.Message);
}
}
public void Select_With_Aliased_Table_Without_As()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select * from table t").First();
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(1, statement.From.Count);
Assert.AreEqual("table", statement.From[0].Name);
Assert.AreEqual("t", statement.From[0].Alias.Name);
}
//TODO: Create Or Replace statement
//TODO: Check If Exist
//TODO: Add [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
//TODO: Add [DEFINER = { user | CURRENT_USER }]
//TODO: add[SQL SECURITY { DEFINER | INVOKER }]
//TODO: Add [WITH[CASCADED | LOCAL] CHECK OPTION]
protected override void VisitCreateViewStatement(CreateViewStatement statement)
{
State.Write(Symbols.CREATE);
State.Write(Symbols.VIEW);
if (statement.CheckIfNotExists)
{
throw new NotImplementedException();
}
VisitNameToken(statement.Name);
State.Write(Symbols.AS);
VisitStatement(statement.DefinitionStatement);
}
private static TestView LoadViewMetatadaFromDDL(TestSchema schema, string ddl)
{
TSqlFragment sqlF = ScriptDomFacade.Parse(ddl);
CreateViewStatement stmt_CreateFunction = (CreateViewStatement)((TSqlScript)sqlF).Batches[0].Statements[0];
//string body = ExtractViewDefinition(ddl);
string body = GetFragmentStreamAsText(stmt_CreateFunction.SelectStatement);
string schemaName = stmt_CreateFunction.SchemaObjectName.SchemaIdentifier.Dequote();
string functionName = stmt_CreateFunction.SchemaObjectName.BaseIdentifier.Dequote();
var function = new TestView(schemaName, functionName, body, f => LoadViewOutputColumnsX(schema, f, stmt_CreateFunction));
return(function);
}
public void Create_View_With_Schema()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view dbo.v1 as select * from table").First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("dbo.v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Top);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Select_Top_10_StarField()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select top 10 * from table").First();;
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.AreEqual("10", statement.Top.Expression.Value);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Select_StarField_Only(string modificationType)
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>(string.Format("{0} view v1 as select * from table", modificationType)).First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Top);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Select_With_Two_Aliased_Table_With_As()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select * from table1 as t1, table2 as t2").First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(2, statement.From.Count);
Assert.AreEqual("table1", statement.From[0].Name);
Assert.AreEqual("t1", statement.From[0].Alias.Name);
Assert.AreEqual("table2", statement.From[1].Name);
Assert.AreEqual("t2", statement.From[1].Alias.Name);
}
public static EngineResult Evaluate(CreateViewStatement createView, Scope scope)
{
var databaseName = createView.SchemaObjectName.DatabaseIdentifier?.Value ?? scope.Env.DefaultDatabase;
var schemaName = createView.SchemaObjectName.SchemaIdentifier?.Value ?? scope.Env.DefaultSchema;
var viewName = createView.SchemaObjectName.BaseIdentifier.Value;
var view = new View
{
Name = viewName,
Query = createView.SelectStatement.QueryExpression
};
var database = scope.Env.Engine.Databases.GetOrAdd(databaseName, Database.Named);
var schema = database.Schemas.GetOrAdd(schemaName, Schema.Named);
schema.Views.Declare(view);
return(null);
}
public QsiTableNode VisitCreateViewStatement(CreateViewStatement createViewStatement)
{
return(TreeHelper.Create <QsiDerivedTableNode>(n =>
{
if (createViewStatement.Columns == null || createViewStatement.Columns.Count == 0)
{
n.Columns.SetValue(TreeHelper.CreateAllColumnsDeclaration());
}
else
{
var columnsDeclaration = new QsiColumnsDeclarationNode();
columnsDeclaration.Columns.AddRange(CreateSequentialColumnNodes(createViewStatement.Columns));
n.Columns.SetValue(columnsDeclaration);
}
n.Source.SetValue(VisitSelectStatement(createViewStatement.SelectStatement));
n.Alias.SetValue(CreateAliasNode(createViewStatement.SchemaObjectName[^ 1]));
protected override void VisitCreateViewStatement(CreateViewStatement statement)
{
State.Write(Symbols.CREATE);
if (statement.IsTemporary)
{
State.Write(Symbols.TEMPORARY);
}
State.Write(Symbols.VIEW);
if (statement.CheckIfNotExists)
{
State.Write(Symbols.IF);
State.Write(Symbols.NOT);
State.Write(Symbols.EXISTS);
}
VisitNameToken(statement.Name);
State.Write(Symbols.AS);
VisitStatement(statement.DefinitionStatement);
}
public void Select_With_Join_Condition()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>(@"
CREATE VIEW SomeView
AS
SELECT FieldA
FROM Table1 T1
JOIN Table2 T2
ON T1.Field1 = T2.Field2"
).First();
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("SomeView", sut.Name);
// Test From
Assert.AreEqual(1, statement.From.Count);
Assert.AreEqual("Table1", statement.From[0].Name);
Assert.AreEqual("T1", statement.From[0].Alias.Name);
// Test Join
Assert.AreEqual(1, statement.From[0].Joins.Count);
Join join = statement.From[0].Joins[0];
Assert.AreEqual("Table2", join.Name);
Assert.AreEqual("T2", join.Alias.Name);
CriteriaExpression expr = join.Condition as CriteriaExpression;
Assert.AreEqual(JoinType.Join, join.Type);
Assert.AreEqual("=", expr.Operator);
Assert.AreEqual("T1.Field1", expr.Left.Value);
Assert.AreEqual("T2.Field2", expr.Right.Value);
Assert.AreEqual("T1.Field1 = T2.Field2", expr.Value);
}
public void Select_Multiple_Fields()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select fielda, field2, fie3ld from table").First();
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(3, statement.Fields.Count);
var expectedFields = new string[] { "fielda", "field2", "fie3ld" };
int index = 0;
foreach (var field in expectedFields)
{
Assert.AreEqual(field, statement.Fields[index++].Expression.Value);
}
Assert.AreEqual("table", statement.From[0].Name);
}
void AlterView(Name tokenName, IStatement definitionStatement)
{
State.Write(Symbols.DO);
State.WriteCRLF();
State.Write(this.TempName);
State.WriteCRLF();
State.Write(Symbols.BEGIN);
DropViewStatement dropViewStatement = Sql.DropView(tokenName, true);
VisitStatement(dropViewStatement);
State.WriteStatementTerminator();
CreateViewStatement createViewStatement = Sql.CreateView(tokenName, definitionStatement);
VisitStatement(createViewStatement);
State.WriteStatementTerminator();
State.Write(Symbols.END);
State.WriteCRLF();
State.Write(this.TempName);
}
public override void Visit(CreateViewStatement node)
{
// Determining the column types of a view is a bit more involved.
// We need to determine which tables are being queried and look up the
// columns that the query references.
// This is a basic implementation that can be enhanced as needed to accomodate more scenarios.
// It assumes that any table referenced in the view are declared before the view.
string viewName = node.SchemaObjectName.BaseIdentifier.Value;
string schemaQualifiedViewName = $"{node.SchemaObjectName.SchemaIdentifier.Value}.{viewName}";
string className = GetClassNameForView(viewName);
var querySpecification = (QuerySpecification)node.SelectStatement.QueryExpression;
IList <SelectElement> selectElements = querySpecification.SelectElements;
List <(string name, string alias)> tables =
querySpecification.FromClause.TableReferences
.SelectMany(tr => tr switch
{
JoinTableReference @join => new[] { @join.FirstTableReference, @join.SecondTableReference },
_ => new[] { tr },
})
public override void Visit(CreateViewStatement node)
{
base.Visit(node);
_statements.Add(node);
}
public virtual bool Action(CreateViewStatement stmt)
{
FixIdentifiers(stmt.Name, ObjectType.OTHER, stmt.Name.Identifiers);
mToSkip.Add(stmt.Name);
return(true);
}
public override void Visit(CreateViewStatement node) { this.action(node); }
public void Select_Top_Missing_Top_Param_StarField()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select top * from table").First();
}
private static void LoadViewOutputColumns(TestSchema schema, TestView function_source, CreateViewStatement stmt_CreateView)
{
BatchOutputColumnTypeResolver batchResolver = new BatchOutputColumnTypeResolver(schema, stmt_CreateView, function_source);
StatementOutputColumnTypeResolverV2 resolver = new StatementOutputColumnTypeResolverV2(batchResolver, stmt_CreateView.SelectStatement);
QuerySpecification first = TopQuerySpecification(stmt_CreateView.SelectStatement.QueryExpression);
foreach (SelectElement se in first.SelectElements)
{
if (se is SelectScalarExpression scalarExpr)
{
OutputColumnDescriptor col = resolver.ResolveSelectScalarExpression(scalarExpr);
function_source.AddViewColumn(col.OutputColumnName, col.ColumnType.ColumnDbType, col.ColumnType.AllowNull, col.OutputColumnName);
}
else
{
throw new NotImplementedException(se.WhatIsThis());
}
}
}
public Dictionary <string, string> ParseColumnDescriptions(string sqlViewText)
{
var columns = new Dictionary <string, string>();
var file = new StringReader(sqlViewText);
var fileLine = string.Empty;
var fileLineNumber = 0;
do
{
fileLineNumber++;
fileLine = file.ReadLine();
if (fileLine == null)
{
continue;
}
var startTag = "<d>";
var startOfDocTag = fileLine.IndexOf(startTag, StringComparison.CurrentCultureIgnoreCase);
var endOfDocTag = fileLine.IndexOf("</d>", StringComparison.CurrentCultureIgnoreCase);
if (startOfDocTag < 0)
{
continue;
}
if (endOfDocTag < 0)
{
continue;
}
var docSnippet = fileLine.Substring(startOfDocTag + startTag.Length,
endOfDocTag - (startOfDocTag + startTag.Length));
var parser = new TSql120Parser(true);
TextReader txtRdr = new StringReader(sqlViewText);
IList <ParseError> errors;
var sqlFragment = parser.Parse(txtRdr, out errors);
var sqlScript = sqlFragment as TSqlScript;
CreateViewStatement createViewStatement = null;
foreach (var batch in sqlScript.Batches)
{
var cts = batch.Statements.FirstOrDefault(s => s is CreateViewStatement);
if (cts != null)
{
createViewStatement = cts as CreateViewStatement;
}
}
if (createViewStatement == null)
{
continue;
}
if (createViewStatement.SchemaObjectName.SchemaIdentifier == null)
{
continue;
}
var schema = createViewStatement.SchemaObjectName.SchemaIdentifier.Value;
var viewName = createViewStatement.SchemaObjectName.BaseIdentifier.Value;
foreach (var fg in sqlFragment.ScriptTokenStream)
{
//find the comment, using parser
if (fg.TokenType == TSqlTokenType.SingleLineComment && fg.Line == fileLineNumber)
{
//find the column name
var columnFragment =
sqlFragment.ScriptTokenStream.Reverse().FirstOrDefault(
s => s.Line == fg.Line &&
(s.TokenType == TSqlTokenType.QuotedIdentifier ||
s.TokenType == TSqlTokenType.Identifier ||
s.TokenType == TSqlTokenType.AsciiStringLiteral));
if (columnFragment != null && !string.IsNullOrEmpty(columnFragment.Text))
{
var columnName = columnFragment.Text;
columns.Add(columnName, docSnippet);
// AddExtendedPropertyToScript(schema, tableName, columnName, docSnippet);
}
}
}
} while (fileLine != null);
return(columns);
}
public void TestNoParserException()
{
//TODO: needs to be moved into a ParserFactory specific unit test
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("merge from table").First();;
}
public override void Visit(CreateViewStatement node)
{
base.Visit(node);
stmts.Add(node);
}
public void ProcessTsqlFragment(TSqlFragment fragment)
{
String stmtType = GetFragmentType(fragment);
//Console.WriteLine(StmtType);
switch (stmtType)
{
case "CreateTableStatement":
CreateTableStatement tblStmt = (CreateTableStatement)fragment;
CurrentType = "Table";
CurrentObject = (tblStmt.SchemaObjectName.DatabaseIdentifier == null ? this.databaseName : tblStmt.SchemaObjectName.DatabaseIdentifier.Value) + "." +
(tblStmt.SchemaObjectName.SchemaIdentifier == null ? this.schemaName : tblStmt.SchemaObjectName.SchemaIdentifier.Value) + "." +
(tblStmt.SchemaObjectName.BaseIdentifier == null ? "" : tblStmt.SchemaObjectName.BaseIdentifier.Value);
addNode(CurrentObject, "Table");
CurrentType = "";
break;
case "CreateViewStatement":
CreateViewStatement vw = (CreateViewStatement)fragment;
CurrentType = "View";
CurrentObject = (vw.SchemaObjectName.DatabaseIdentifier == null ? this.databaseName : vw.SchemaObjectName.DatabaseIdentifier.Value) + "." +
(vw.SchemaObjectName.SchemaIdentifier == null ? this.schemaName : vw.SchemaObjectName.SchemaIdentifier.Value) + "." +
(vw.SchemaObjectName.BaseIdentifier == null ? "" : vw.SchemaObjectName.BaseIdentifier.Value);
addNode(CurrentObject, "View");
ProcessSelectStatement(vw.SelectStatement);
CurrentType = "";
break;
case "CreateProcedureStatement":
CreateProcedureStatement prc = (CreateProcedureStatement)fragment;
CurrentType = "Proc";
CurrentObject = (prc.ProcedureReference.Name.DatabaseIdentifier == null ? this.databaseName : prc.ProcedureReference.Name.DatabaseIdentifier.Value) + "." +
(prc.ProcedureReference.Name.SchemaIdentifier == null ? this.schemaName : prc.ProcedureReference.Name.SchemaIdentifier.Value) + "." +
(prc.ProcedureReference.Name.BaseIdentifier == null ? "" : prc.ProcedureReference.Name.BaseIdentifier.Value);
addNode(CurrentObject, "Proc");
ProcessCreateProcedure(prc);
CurrentType = "";
break;
case "SelectStatement":
ProcessSelectStatement((SelectStatement)fragment);
break;
case "BeginEndBlockStatement":
ProcessBeginEndBlockStatement((BeginEndBlockStatement)fragment);
break;
case "TryCatchStatement":
ProcessTryCatchStatement((TryCatchStatement)fragment);
break;
case "UpdateStatement":
ProcessUpdateStatement((UpdateStatement)fragment);
break;
case "InsertStatement":
ProcessInsertStatement((InsertStatement)fragment);
break;
case "IfStatement":
ProcessIfStatement((IfStatement)fragment);
break;
case "BeginTransactionStatement":
break;
default:
break;
}
}
protected abstract void VisitCreateViewStatement(CreateViewStatement statement);
public override void ExplicitVisit(CreateViewStatement view)
{
if (IsSupportedForCurrentType(view.GetType()))
{
Name = view.SchemaObjectName;
}
}
public override void ExplicitVisit(CreateViewStatement fragment)
{
_fragments.Add(fragment);
}