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