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); }