private void Expand(DerivedTable derivedTable) { builder.AppendFormat("("); ExpandExpression(derivedTable.Query); builder.AppendFormat(")"); builder.AppendFormat(" [{0}]", derivedTable.Alias); }
public override IList <SchemaObject> Create(SchemaFile file) { foreach (var selectStatement in Fragments) { var columns = selectStatement.GetFields(Logger, file); if (columns.Any()) { // TODO : add data set to local schema var select = new DerivedTable() { Columns = columns, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, File = file, Identifier = "DataSet" }; file .LocalSchema .Add(new KeyValuePair <string, SchemaObject>(select.GetQualifiedIdentfier(), select)); } } // we don't return it because it would go to global schema, keep it local return(new List <SchemaObject>()); }
protected void FormatFrom() { bool multipleFroms = _statement.From.Count > 1; if (_statement.From == null || !_statement.From.Any()) { return; } bool canCompactFormat = CanCompactFormat(); NewLine(canCompactFormat ? 0 : 1); int fromIndex = 0; foreach (var from in _statement.From) { string fromText = !multipleFroms || from == _statement.From.First() ? "FROM " : ""; DerivedTable derivedTable = from as DerivedTable; if (derivedTable != null) { NewLine(); IndentAppend(String.Format("{0}(", fromText)); NewLine(canCompactFormat ? 1 : 2); using (new IndentScope(this)) { var formatter = new SelectStatementFormatter(this, _sql, derivedTable.SelectStatement); formatter.Execute(); } NewLine(canCompactFormat ? 1 : 2); IndentAppend(String.Format("){0}", from.Alias.Value)); } else { bool isLast = from == _statement.From.Last(); NewLine(1); IndentAppendFormat( "{0}{1}{2}{3}{4}{5}", fromIndex > 0 ? Indent + " " : "", fromText, from.Name, from.Alias.Value, FormatHints(from), !isLast && !from.Joins.Any() ? Constants.Comma + "\n" : "" ); } FormatJoins(from, multipleFroms, from == _statement.From.Last()); fromIndex++; } }
public void Select_Ranking_Functions_Over_With_Order_By_And_Partition_By(string functionName) { // Exercise var statement = ParserFactory.Execute <SelectStatement>(String.Format( @" SELECT * FROM ( SELECT RowIndex = {0} OVER (PARTITION BY Code ORDER BY SomeNumber, OtherNumber DESC), * FROM [PagedTable] ) T WHERE RowIndex BETWEEN 2 AND 3 ", functionName )).First(); // Verify outcome Assert.IsNotNull(statement); Assert.AreEqual(1, statement.From.Count); Assert.AreEqual("T", statement.From[0].Alias.Name); Assert.IsTrue(statement.From[0] is DerivedTable); Assert.AreEqual("RowIndex BETWEEN 2 AND 3", statement.Where.Value); DerivedTable derivedTable = (DerivedTable)statement.From[0]; Assert.AreEqual("RowIndex", derivedTable.SelectStatement.Fields[0].Alias.Name); Assert.IsTrue(derivedTable.SelectStatement.Fields[0].Expression is RankingFunctionExpression); var rankingFunctionExpression = (RankingFunctionExpression)derivedTable.SelectStatement.Fields[0].Expression; Assert.AreEqual(functionName, rankingFunctionExpression.Name); Assert.AreEqual(functionName + " OVER (PARTITION BY Code ORDER BY SomeNumber, OtherNumber DESC)", rankingFunctionExpression.Value); Assert.AreEqual(2, rankingFunctionExpression.OrderBy.Count); Assert.AreEqual("SomeNumber", rankingFunctionExpression.OrderBy[0].Expression.Value); Assert.AreEqual("OtherNumber", rankingFunctionExpression.OrderBy[1].Expression.Value); Assert.AreEqual(1, rankingFunctionExpression.PartitionBy.Count); Assert.AreEqual("Code", rankingFunctionExpression.PartitionBy[0].Expression.Value); }
public void Select_From_Derived_View() { // Exercise SelectStatement statement = ParserFactory.Execute <SelectStatement>(@" select * from (select field from table x) as t ").First(); // Verify outcome Assert.IsNotNull(statement); Assert.AreEqual(1, statement.From.Count); Assert.AreEqual("t", statement.From[0].Alias.Name); Assert.IsTrue(statement.From[0] is DerivedTable); DerivedTable derivedTable = (DerivedTable)statement.From[0]; Assert.AreEqual("field", derivedTable.SelectStatement.Fields[0].Expression.Value); Assert.AreEqual("table", derivedTable.SelectStatement.From[0].Name); Assert.AreEqual("x", derivedTable.SelectStatement.From[0].Alias.Name); }
public static Schema addDerivedTables(string derivedTableFilePath) { Schema schema = null; if (derivedTableFilePath != null) { schema = new Schema("DerivedTables"); List <Table> tables = new List <Table>(); foreach (string file in Directory.EnumerateFiles(derivedTableFilePath, "*.xml")) { XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder)); SelectQueryBuilder loadedQuery = (SelectQueryBuilder)SerializerObj.Deserialize(new StreamReader(file)); string fileName = file.Substring(file.LastIndexOf('/') + 1).Replace(' ', '_'); DerivedTable table = new DerivedTable(fileName.Substring(0, fileName.Length - 4), "", getQuery(loadedQuery)); List <Column> columns = new List <Column>(); foreach (SQLBuilder.Clauses.Column col in loadedQuery.SelectedColumns) { string columnName = col.AliasName; string columnType = col.DataType; Object formatType = col.Format; Column column; if (formatType != null) { column = new Column(columnName, columnType, (string)formatType); } else { column = new Column(columnName, columnType); } columns.Add(column); } table.columns = columns; tables.Add(table); } schema.tables = tables; } return(schema); }
public static IEnumerable <SchemaObject> CollectLocalSchema(this TSqlStatement statement, ILogger logger, SchemaFile file) { switch (statement) { case BeginEndBlockStatement beginEndBlockStatement: return(beginEndBlockStatement .StatementList .Statements .CollectLocalSchema(logger, file) .ToList()); case DeclareVariableStatement declareVariableStatement: { foreach (var declaration in declareVariableStatement.Declarations) { var name = declaration.VariableName.Value; var isNullable = false; // TODO : how to determine this? var variable = declaration.DataType.GetField(name, isNullable, logger, file); variable.Origin = OriginType.Variable; file.FileContext.Variables.Peek().Add(variable); } // TODO : what should I return here? break; } case DeclareTableVariableStatement declareTableVariableStatement: { var columns = declareTableVariableStatement .Body .Definition .ColumnDefinitions .GetFields(logger, file) .ToList(); var tableReference = new Table() { Columns = columns, File = file, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = declareTableVariableStatement.Body.VariableName.Value, }; var field = new TableReferenceField() { Name = declareTableVariableStatement.Body.VariableName.Value, Type = FieldType.Table, Origin = OriginType.Variable, IsNullable = false, Reference = tableReference, }; file.FileContext.Variables.Peek().Add(field); // TODO : what should I return here? break; } // TODO : this could be an actual create table statement and not just a temp table case CreateTableStatement createTableStatement: { if (!createTableStatement.SchemaObjectName.BaseIdentifier.Value.StartsWith("#")) { break; // not a temp table } var columns = createTableStatement .Definition .ColumnDefinitions .GetFields(logger, file) .ToList(); columns.ForEach(c => c.Origin = OriginType.Table); var tempTable = new TemporaryTable() { Database = createTableStatement.SchemaObjectName.DatabaseIdentifier?.Value ?? SchemaObject.TempDb, Schema = createTableStatement.SchemaObjectName.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema, Identifier = createTableStatement.SchemaObjectName.BaseIdentifier.Value, File = file, Columns = columns, }; file .LocalSchema .Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable)); break; } case IfStatement ifStatement: { // TODO : conditional output? which data set to return? we don't know till runtime var thenReferences = ifStatement.ThenStatement.CollectLocalSchema(logger, file).ToList(); if (ifStatement.ElseStatement != null) { var elseReferences = ifStatement.ElseStatement.CollectLocalSchema(logger, file).ToList(); return(thenReferences.Concat(elseReferences)); } return(thenReferences); } case SelectStatement selectStatement: { var columns = selectStatement.GetFields(logger, file); if (!columns.Any()) { // if there are no columns there's no data set to return.. // this happens for SELECT statement that assigns values to variables break; } if (selectStatement.Into != null && selectStatement.Into.BaseIdentifier.Value.StartsWith("#") && !file.LocalSchema.ContainsKey(selectStatement.Into.GetTemporaryQualifiedIdentfier())) { var tempTableColumns = selectStatement.GetFields(logger, file);; var tempTable = new TemporaryTable() { Columns = tempTableColumns, File = file, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = selectStatement.Into.BaseIdentifier.Value, }; file .LocalSchema .Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable)); } var dataSet = new DerivedTable() { Columns = columns, File = file, Identifier = selectStatement.GetTokenText(), }; return(new List <SchemaObject>() { dataSet }); } case WhileStatement whileStatement: return(whileStatement.Statement.CollectLocalSchema(logger, file)); case TryCatchStatement tryCatchStatement: { var tryReferences = tryCatchStatement.TryStatements.Statements.CollectLocalSchema(logger, file); var catchReferences = tryCatchStatement.CatchStatements.Statements.CollectLocalSchema(logger, file); return(tryReferences.Concat(catchReferences).ToList()); } case ReturnStatement x: { // TODO : check this statement, do I want to stop collecting data sets now? // what if it is conditinal return statement? break; } case MergeStatement mergeStatement: break; // TODO : what to do with this one? // NOTE : I don't care about these statements yet case PredicateSetStatement x: break; case SetVariableStatement x: break; case SetCommandStatement x: break; case SetRowCountStatement x: break; case UseStatement x: break; case DenyStatement x: break; case RevokeStatement x: break; case SetIdentityInsertStatement x: break; case SetTransactionIsolationLevelStatement x: break; case BeginTransactionStatement x: break; case RollbackTransactionStatement x: break; case CommitTransactionStatement x: break; case RaiseErrorStatement x: break; case ThrowStatement x: break; case BreakStatement x: break; case ContinueStatement x: break; case SaveTransactionStatement x: break; case UpdateStatisticsStatement x: break; case InsertStatement x: break; case UpdateStatement x: break; case DeleteStatement x: break; case ExecuteStatement x: break; case GrantStatement x: break; case CreateIndexStatement x: break; case GoToStatement x: break; case LabelStatement x: break; case PrintStatement x: break; case DeclareCursorStatement x: break; case OpenCursorStatement x: break; case FetchCursorStatement x: break; case CloseCursorStatement x: break; case DeallocateCursorStatement x: break; case WaitForStatement x: break; case BeginDialogStatement x: break; case SendStatement x: break; case EndConversationStatement x: break; // TODO : statements to generate schema.. might be useful for sql in the test project case TruncateTableStatement x: break; case DropTableStatement x: break; case DropViewStatement x: break; case CreateFunctionStatement x: break; case AlterFunctionStatement x: break; case CreateOrAlterFunctionStatement x: break; case DropFunctionStatement x: break; case AlterTableAddTableElementStatement x: break; case AlterTableConstraintModificationStatement x: break; case CreateTypeTableStatement x: break; case CreateViewStatement x: break; case AlterViewStatement x: break; case DropProcedureStatement x: break; case CreateProcedureStatement x: break; case CreateOrAlterProcedureStatement x: break; case CreateOrAlterViewStatement x: break; case AlterTableSetStatement x: break; case AlterProcedureStatement x: break; case CreateTypeUddtStatement x: break; default: { logger.Log(LogLevel.Warning, LogType.NotSupportedYet, file.Path, $"\"{statement.GetType()}\" Tsql statement is not supported yet. " + $"Fragment: \"{statement.GetTokenText()}\""); break; } } return(new List <SchemaObject>()); }
public static IEnumerable <SchemaObjectReference> GetSchemaObjectReferences( this TableReference tableReference, ILogger logger, SchemaFile file ) { switch (tableReference) { case JoinTableReference joinTableReference: // NOTE : handles both, qualified and unqualified joins return(joinTableReference.GetSchemaObjectReferences(logger, file)); case NamedTableReference namedReference: return(new List <SchemaObjectReference>() { namedReference.GetSchemaObjectReference(logger, file) }); case QueryDerivedTable queryDerivedTable: { var queryDerivedTableColumns = queryDerivedTable .QueryExpression .GetFields(logger, file); if (queryDerivedTable.Columns.Any()) { for (int i = 0; i < queryDerivedTable.Columns.Count(); i++) { queryDerivedTableColumns[i].Name = queryDerivedTable.Columns[i].Value; } } var derivedTable = new DerivedTable() { Identifier = queryDerivedTable.Alias.Value, // TODO : do they have a name? File = file, Columns = queryDerivedTableColumns, }; var identifier = derivedTable.GetQualifiedIdentfier(); // TODO : do I need to add it to local schema? why? file .LocalSchema .Add(new KeyValuePair <string, SchemaObject>(identifier, derivedTable)); return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = queryDerivedTable.Alias.Value, Identifier = identifier, Value = derivedTable, } }); } case InlineDerivedTable inlineDerivedTable: var inlineTableColumns = inlineDerivedTable .RowValues .First() .ColumnValues .Select(c => c.GetField("", logger, file)) .ToList(); if (inlineDerivedTable.Columns.Any()) { for (int i = 0; i < inlineDerivedTable.Columns.Count(); i++) { inlineTableColumns[i].Name = inlineDerivedTable.Columns[i].Value; } } var inlineTable = new DerivedTable() { Identifier = inlineDerivedTable.Alias.Value, // TODO : do they have a name? File = file, Columns = inlineTableColumns, }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = inlineDerivedTable.Alias.Value, Identifier = inlineTable.GetQualifiedIdentfier(), Value = inlineTable, } }); case VariableTableReference variableTableReference: { // TODO : wrap in GetVariable() var name = variableTableReference.Variable.Name; var variable = (TableReferenceField)file .FileContext .Variables .SelectMany(x => x) .Distinct(new KeyEqualityComparer <Field, string>(x => x.Name)) .First(x => x.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase)); return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = variableTableReference.Alias?.Value, Identifier = variable.Reference.GetQualifiedIdentfier(), Value = variable.Reference, } }); } case SchemaObjectFunctionTableReference schemaObjectFunctionTableReference: { // TODO : add support for XML handling // "col.nodes('entry') y(row)" // schema.base(parameter) alias(column) var qualifiedIdentifier = schemaObjectFunctionTableReference.SchemaObject.GetQualifiedIdentfier(file); var tempQualifiedIdentifier = schemaObjectFunctionTableReference.SchemaObject.GetTemporaryQualifiedIdentfier(); SchemaObject reference; if (file.Schema.ContainsKey(qualifiedIdentifier)) { reference = file.Schema[qualifiedIdentifier]; } else if (file.LocalSchema.ContainsKey(tempQualifiedIdentifier)) { reference = file.LocalSchema[tempQualifiedIdentifier]; } else { // TODO : it doesn't have to be just XML? // TODO : columns can be null var functionColumns = schemaObjectFunctionTableReference .Columns .Select(x => new DefaultField() { Name = x.Value, Type = FieldType.Xml, IsNullable = false, }) .Cast <Field>() .ToList(); reference = new DerivedTable() { Columns = functionColumns, File = file, Identifier = schemaObjectFunctionTableReference.SchemaObject.BaseIdentifier.Value, Database = SchemaObject.MasterDb, Schema = schemaObjectFunctionTableReference.SchemaObject.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema, }; } return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = schemaObjectFunctionTableReference.Alias?.Value, Identifier = reference.GetQualifiedIdentfier(), Value = reference, } }); } case OpenJsonTableReference openJsonTableReference: { var f = openJsonTableReference.Variable.GetField(null, logger, file); var columns = openJsonTableReference .SchemaDeclarationItems .Select(declarationItem => { var column = declarationItem .ColumnDefinition .DataType .GetField(declarationItem.ColumnDefinition.ColumnIdentifier.Value, false, logger, file); column.Origin = OriginType.SystemType; return(column); }) .ToList(); var jsonTable = new DerivedTable() { Columns = columns, File = file, Identifier = $"{f.Name}-openjson", Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = openJsonTableReference.Alias?.Value, Identifier = jsonTable.GetQualifiedIdentfier(), Value = jsonTable, } }); } case VariableMethodCallTableReference variableMethodCallTableReference: { // TODO : find out how this really works in SQL // FROM @delivery_xml.nodes('/delivery/fixedPrices') AS x(col) .. (fn_get_highest_shipping_price.udf) //variableMethodCallTableReference.MethodName; // nodes //variableMethodCallTableReference.Parameters; // '/delivery/fixedPrices' var columns = variableMethodCallTableReference .Columns .Select(x => new DefaultField() { Name = x.Value, Type = FieldType.Xml, IsNullable = false, }) .Cast <Field>() .ToList(); var variableMethodCallTable = new DerivedTable() { Columns = columns, File = file, Identifier = variableMethodCallTableReference.Variable.Name, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = variableMethodCallTableReference.Alias?.Value, Identifier = variableMethodCallTable.GetQualifiedIdentfier(), Value = variableMethodCallTable, } }); } case JoinParenthesisTableReference joinParenthesisTableReference: return(joinParenthesisTableReference .Join .GetSchemaObjectReferences(logger, file)); case BuiltInFunctionTableReference builtInFunctionTableReference: SchemaObject value = null; switch (builtInFunctionTableReference.Name.Value) { case "fn_virtualfilestats": value = new Table() { File = file, Database = SchemaObject.MasterDb, Schema = SchemaObject.SystemSchema, Identifier = builtInFunctionTableReference.Name.Value, Columns = new List <Field>() { // https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-virtualfilestats-transact-sql?view=sql-server-2017 new DefaultField() { Name = "DbId", Type = FieldType.SmallInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "FileId", Type = FieldType.SmallInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "TimeStamp", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "NumberReads", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "BytesRead", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "IoStallReadMS", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "NumberWrites", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "BytesWritten", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "IoStallWriteMS", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "IoStallMS", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "FileHandle", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, new DefaultField() { Name = "BytesOnDisk", Type = FieldType.BigInt, Origin = OriginType.FunctionReturn }, }, }; break; } return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = builtInFunctionTableReference.Alias?.Value, Identifier = value.GetQualifiedIdentfier(), Value = value, } }); case FullTextTableReference fullTextTableReference: var fullTextTableIdentifier = fullTextTableReference .TableName .GetQualifiedIdentfier(file); var fullTextTableSource = file.Schema.ContainsKey(fullTextTableIdentifier) ? file.Schema[fullTextTableIdentifier] : new Unknown() { File = file, Database = "", Schema = "", Identifier = "", }; var newReferences = new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = null, Identifier = fullTextTableSource.GetQualifiedIdentfier(), Value = fullTextTableSource, } }; // TODO : or should these be added to current scope instead of pushing new scope? using (new StatementContext(file.FileContext, newReferences)) { var keyColumn = fullTextTableSource .Columns .FirstOrDefault(x => x.HasIdentity) ?.Copy("KEY") ?? new DefaultField() { Name = "KEY", Type = FieldType.Int, Origin = OriginType.Table, IsNullable = false, }; var rankColumn = new DefaultField() { Name = "RANK", Type = FieldType.Int, Origin = OriginType.Table, IsNullable = false, }; // TODO : are these part of the result set or not? //var fullTextSourceTableColumns = fullTextTableReference.Columns.Count == 1 // && fullTextTableReference.Columns.First().ColumnType == ColumnType.Wildcard // ? newReferences // .SelectMany(x => x.Value.Columns) // .ToList() // : fullTextTableReference // .Columns // .Select(x => x.GetField(null, logger, file)); var fullTextTableColumns = new List <Field>() { keyColumn, rankColumn, }; //.Concat(fullTextSourceTableColumns) //.ToList(); var fullTextTable = new DerivedTable() { Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = $"{fullTextTableIdentifier}-containstable", File = file, Columns = fullTextTableColumns, }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = fullTextTableReference.Alias?.Value, Identifier = fullTextTable.GetQualifiedIdentfier(), Value = fullTextTable, } }); } case UnpivotedTableReference unpivotedTableReference: var unpivotReferences = unpivotedTableReference .TableReference .GetSchemaObjectReferences(logger, file) .ToList(); // TODO : or should these be added to current scope instead of pushing new scope? using (new StatementContext(file.FileContext, unpivotReferences)) { var sourceColumn = unpivotedTableReference .InColumns .First() // TODO : taking first, do I need to compute the value? .GetField(null, logger, file); var columnName = unpivotedTableReference.ValueColumn?.Value; var unpivotTable = new DerivedTable() { Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = unpivotedTableReference.PivotColumn?.Value, // TODO : find a better name File = file, Columns = new List <Field>() { sourceColumn.Copy(columnName) }, }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = unpivotedTableReference.Alias?.Value, Identifier = unpivotTable.GetQualifiedIdentfier(), Value = unpivotTable, } }); } case PivotedTableReference pivotedTableReference: var schemaObjectReferences = pivotedTableReference .TableReference .GetSchemaObjectReferences(logger, file) .ToList(); // TODO : or should these be added to current scope instead of pushing new scope? using (new StatementContext(file.FileContext, schemaObjectReferences)) { var valueField = pivotedTableReference .ValueColumns .Select(x => x.GetField(null, logger, file)) .First(); // TODO : can there be more than one? I haven't seen any examples var pivotColumns = pivotedTableReference .InColumns .Select(x => valueField.Copy(x.Value)) .ToList(); var pivotTable = new DerivedTable() { File = file, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = "TODO", // TODO : find suitable name Columns = pivotColumns, }; var pivotTableReference = new SchemaObjectReference() { Alias = pivotedTableReference.Alias?.Value, Identifier = pivotTable.GetQualifiedIdentfier(), Value = pivotTable, }; schemaObjectReferences.Add(pivotTableReference); return(schemaObjectReferences); } case GlobalFunctionTableReference globalFunctionTableReference: { switch (globalFunctionTableReference.Name.Value.ToUpper()) { case "STRING_SPLIT": { // Returns a single - column table with fragments. The name of the column is value. // Returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. // The length of the return type is the same as the length of the string argument. var inputStringField = globalFunctionTableReference .Parameters .First() .GetField(null, logger, file) as StringField; var table = new Table() { File = file, Database = SchemaObject.TempDb, Schema = SchemaObject.DefaultSchema, Identifier = "STRING_SPLIT", Columns = new List <Field>() { new StringField() { Name = "value", Type = FieldType.String, Origin = OriginType.SystemType, IsNullable = false, Length = inputStringField?.Length ?? 0, } } }; return(new List <SchemaObjectReference>() { new SchemaObjectReference() { Alias = globalFunctionTableReference.Alias?.Value, Identifier = table.GetQualifiedIdentfier(), Value = table, } }); } default: break; } break; } } logger.Log(LogLevel.Warning, LogType.NotSupportedYet, file.Path, $"{tableReference.GetType()} table reference type is not supported yet. " + $"Fragment \"{tableReference.GetTokenText()}\""); return(new List <SchemaObjectReference>()); }
protected void ProcessFrom() { if (!Tokenizer.TokenEquals(Constants.From)) { return; } do { Table table = null; if (Tokenizer.IsNextToken(Constants.OpenBracket)) { using (Tokenizer.ExpectBrackets()) { DerivedTable derivedTable = new DerivedTable(); Tokenizer.ExpectToken(Constants.Select); var parser = new SelectStatementParser(Tokenizer); derivedTable.SelectStatement = (SelectStatement)parser.Execute(); table = derivedTable; } } else { table = new Table { Name = GetTableName() } }; _statement.From.Add(table); // TODO: This needs to be changed to test Tokenizer.Token.Current.TokenType for TokenType.Keyword // if a new statement is initiated here, do not process the alias if (IsTerminatingFromExpression()) { return; } Alias alias = new Alias(null); if (Tokenizer.IsNextToken(Constants.As)) { alias.Type = AliasType.As; Tokenizer.ReadNextToken(); } if (!Tokenizer.IsNextToken(Constants.OpenBracket) && (alias.Type != AliasType.Implicit || !Tokenizer.IsNextToken(FromTerminatorSet))) { if (Tokenizer.HasMoreTokens) { if (!Tokenizer.Current.IsTypeIn( TokenType.AlphaNumeric, TokenType.AlphaNumeric, TokenType.BlockedText, TokenType.SingleQuote ) ) { throw new SyntaxException(String.Format("Incorrect syntax near '{0}'", CurrentToken)); } alias.Name = CurrentToken; table.Alias = alias; ReadNextToken(); } } ProcessTableHints(table); ProcessJoins(table); }while (Tokenizer.HasMoreTokens && Tokenizer.TokenEquals(Constants.Comma)); }
public static Schema addDerivedTables(string derivedTableFilePath) { Schema schema = null; if (derivedTableFilePath != null) { schema = new Schema("DerivedTables"); List<Table> tables = new List<Table>(); foreach (string file in Directory.EnumerateFiles(derivedTableFilePath, "*.xml")) { XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder)); SelectQueryBuilder loadedQuery = (SelectQueryBuilder)SerializerObj.Deserialize(new StreamReader(file)); string fileName = file.Substring(file.LastIndexOf('/') + 1).Replace(' ', '_'); DerivedTable table = new DerivedTable(fileName.Substring(0, fileName.Length - 4), "", getQuery(loadedQuery)); List<Column> columns = new List<Column>(); foreach (SQLBuilder.Clauses.Column col in loadedQuery.SelectedColumns) { string columnName = col.AliasName; string columnType = col.DataType; Object formatType = col.Format; Column column; if (formatType != null) { column = new Column(columnName, columnType, (string)formatType); } else { column = new Column(columnName, columnType); } columns.Add(column); } table.columns = columns; tables.Add(table); } schema.tables = tables; } return schema; }