public override IList <SchemaObject> Create(SchemaFile file)
{
var newSchemaList = new List <SchemaObject>();
foreach (var createTypeStatement in Fragments)
{
var dbObject = new UserDefinedType()
{
Database = createTypeStatement.Name.DatabaseIdentifier?.Value ?? file.Context.Name,
Schema = createTypeStatement.Name.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema,
Identifier = createTypeStatement.Name.BaseIdentifier.Value,
File = file,
Columns = GetColumns(createTypeStatement, file)
};
newSchemaList.Add(dbObject);
}
return(newSchemaList);
}
public static IList <SchemaObjectReference> GetSchemaObjectReferences(
this IEnumerable <CommonTableExpression> commonTableExpressions,
ILogger logger,
SchemaFile file
)
{
var schemaObjectReferences = new List <SchemaObjectReference>();
foreach (var commonTableExpression in commonTableExpressions)
{
using (new StatementContext(file.FileContext, schemaObjectReferences))
{
// current CTE can be used by subsequent CTE, get reference and next iteration will push it onto the stack
var schemaObjectReference = commonTableExpression.GetSchemaObjectReference(logger, file);
schemaObjectReferences.Add(schemaObjectReference);
}
}
return(schemaObjectReferences);
}
public static IList <FieldPairReference> GetFieldPairReferences(
this UpdateSpecification updateSpecification,
ILogger logger,
SchemaFile file
)
{
var newReferences = updateSpecification
.GetSchemaObjectReferences(logger, file)
.ToList();
using (new StatementContext(file.FileContext, newReferences))
{
var setClausePairs = updateSpecification
.SetClauses
.GetFieldPairs(logger, file)
.ToList();
var fromCalusePairs = updateSpecification
.FromClause
?.TableReferences
.GetFieldPairs(logger, file)
?? new List <FieldPairReference>();
var whereClauePairs = updateSpecification
.WhereClause
?.SearchCondition
.GetFieldPairs(logger, file)
?? new List <FieldPairReference>();
var outputIntoPairs = updateSpecification
.OutputIntoClause
?.GetFieldPairs(logger, file)
?? new List <FieldPairReference>();
return(setClausePairs
.Concat(fromCalusePairs)
.Concat(whereClauePairs)
.Concat(outputIntoPairs)
.ToList());
}
}
public IDictionary <IValidationRule, IList <ValidationResult> > ValidateFile(SchemaFile file)
{
_logger.Log(LogLevel.Information, $"Validating {file.Path}");
var validationResults = new Dictionary <IValidationRule, IList <ValidationResult> >();
foreach (var validationRule in _validationRules)
{
try
{
var validationResult = validationRule.Validate(file);
validationResults.Add(validationRule, validationResult);
}
catch (Exception e) // TODO ... handle only some exceptions?
{
_logger.Log(LogLevel.Error, e.Message);
}
}
return(validationResults);
}
public static IList <FieldPairReference> GetFieldPairReferences(
this MergeAction mergeAction,
ILogger logger,
SchemaFile file
)
{
switch (mergeAction)
{
case InsertMergeAction insertMergeAction:
return(insertMergeAction
.Source
.GetFieldPairReferences(logger, file));
case UpdateMergeAction updateMergeAction:
return(updateMergeAction
.SetClauses
.GetFieldPairs(logger, file));
default:
return(new List <FieldPairReference>());
}
}
public static IEnumerable <SchemaObjectReference> GetSchemaObjectReferences(
this InsertSpecification insertSpecification,
ILogger logger,
SchemaFile file
)
{
var targetReferences = insertSpecification
.Target
.GetSchemaObjectReferences(logger, file)
.ToList();
var insertSourceReferences = insertSpecification
.InsertSource
.GetSchemaObjectReferences(logger, file)
.ToList();
var targetReference = targetReferences.First();
var outputIntoReferences = new List <SchemaObjectReference>()
{
new SchemaObjectReference()
{
Alias = "inserted",
Identifier = targetReference.Identifier,
Value = targetReference.Value
},
new SchemaObjectReference()
{
Alias = "deleted",
Identifier = targetReference.Identifier,
Value = targetReference.Value
}
};
return(targetReferences
.Concat(insertSourceReferences)
.Concat(outputIntoReferences)
.ToList());
}
public static IList <Field> GetFields(
this SelectStatement selectStatement,
ILogger logger,
SchemaFile file
)
{
var cteReferences = selectStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetSchemaObjectReferences(logger, file)
.ToList()
?? new List <SchemaObjectReference>();
using (new StatementContext(file.FileContext, cteReferences))
{
var columns = selectStatement
.QueryExpression
.GetFields(logger, file)
.ToList();
return(columns);
}
}
public static void RegistDefaultOperator(string regFile)
{
_operatorRegList = new OperatorRegList();
RegistOperator("||", typeof(Or), 1);
RegistOperator("&&", typeof(And), 1);
RegistOperator(">", typeof(GreaterThan), 2);
RegistOperator("<", typeof(LessThan), 2);
RegistOperator("<=", typeof(GE), 2);
RegistOperator(">=", typeof(LE), 2);
RegistOperator("!=", typeof(NotEqualTo), 2);
RegistOperator("==", typeof(EqualTo), 2);
RegistOperator("=", typeof(EqualTo), 2);
RegistOperator("+", typeof(Addition), 3);
RegistOperator("-", typeof(Subtraction), 3);
RegistOperator("*", typeof(Multiplication), 4);
RegistOperator("/", typeof(Division), 4);
SchemaFile.SaveSchema <OperatorRegList>(_operatorRegList, regFile);
}
private SchemaFile GetFile(TValidationRule rule)
{
var fileName = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), GetTestFileName());
var fileContent = GetFileContent(fileName);
var script = GetTSqlScript(fileName, fileContent);
var databaseContext = GetDatabaseContext(fileContent, script);
var settings = GetSettings(rule);
// TODO : how to plug in existing schema?
// I reckon that we can just define all dependencies in SQL file and generate it from there?
// or should this be a dependency hard coded in the derived class?
var file = new SchemaFile()
{
Context = databaseContext,
Path = fileName,
Settings = new DatabaseSchemaSettings()
{
FileConvetions = new DatabaseSchemaFileConvetions()
{
UserDefinedTypesFileExtension = "SQL",
TablesFileExtension = "SQL",
ViewsFileExtension = "SQL",
FunctionsFileExtension = "SQL",
ProceduresFileExtension = "SQL",
TriggersFileExtension = "SQL",
},
},
TsqlScript = script,
};
var localFileSchemaGenerator = new LocalFileSchemaGenerator(Logger);
localFileSchemaGenerator.AddLocalSchema(file);
return(file);
}
public override IList <SchemaObject> Create(SchemaFile file)
{
var schemaList = new List <SchemaObject>();
foreach (var createProcedureStatement in Fragments)
{
var parameters = createProcedureStatement
.Parameters
.GetParameters(Logger, file);
var dataSets = createProcedureStatement
.StatementList
.Statements
.CollectLocalSchema(Logger, file)
.ToList();
// TODO : this needs to change to capture multiple datasets
// TODO : also flag when there are no data sets being returned
var columns = dataSets
.FirstOrDefault()
?.Columns ?? new List <Field>();
var dbObject = new StoredProcedure()
{
File = file,
Database = createProcedureStatement.ProcedureReference.Name.DatabaseIdentifier?.Value ?? file.Context.Name,
Schema = createProcedureStatement.ProcedureReference.Name.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema,
Identifier = createProcedureStatement.ProcedureReference.Name.BaseIdentifier.Value,
Columns = columns,
Parameters = parameters,
};
schemaList.Add(dbObject);
}
return(schemaList);
}
public override IList <SchemaObject> Create(SchemaFile file)
{
var schemaList = new List <SchemaObject>();
foreach (var createViewStatement in Fragments)
{
var columns = createViewStatement
.SelectStatement
.GetFields(Logger, file);
var dbObject = new View
{
Database = createViewStatement.SchemaObjectName.DatabaseIdentifier?.Value ?? file.Context.Name,
Schema = createViewStatement.SchemaObjectName.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema,
Identifier = createViewStatement.SchemaObjectName.BaseIdentifier.Value,
Columns = columns,
File = file,
};
schemaList.Add(dbObject);
}
return(schemaList);
}
public static IList <SchemaObjectReference> GetSchemaObjectReferences(
this QueryExpression queryExpression,
ILogger logger,
SchemaFile file
)
{
switch (queryExpression)
{
case QuerySpecification querySpecification:
return(querySpecification
.FromClause
?.TableReferences
.GetSchemaObjectReferences(logger, file)
.ToList()
?? new List <SchemaObjectReference>());
case BinaryQueryExpression binaryQueryExpression:
// TODO : When data types differ, the resulting data type is determined based on the rules for data type precedence
// TODO : do I need different behaviour for various types? Union, Except, Intersect
return(binaryQueryExpression
.FirstQueryExpression
.GetSchemaObjectReferences(logger, file));
case QueryParenthesisExpression queryParenthesisExpression:
return(queryParenthesisExpression
.QueryExpression
.GetSchemaObjectReferences(logger, file));
default:
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"\"{queryExpression.GetType()}\" query expression is not supported yet.");
return(new List <SchemaObjectReference>());
}
}
public static IList <FieldPairReference> GetFieldPairReferences(
this InsertSource insertSource,
ILogger logger,
SchemaFile file
)
{
switch (insertSource)
{
case SelectInsertSource selectInsertSource:
return(selectInsertSource
.Select
.GetFieldPairReferences(logger, file)
.ToList());
case ExecuteInsertSource executeInsertSource:
return(executeInsertSource
.Execute
.GetFieldPairReferences(logger, file));
case ValuesInsertSource valuesInsertSource: // no extra pairs from pure values
default:
return(new List <FieldPairReference>());
}
}
private static Field GetField(this ColumnDefinition column, IEnumerable <ColumnDefinition> columns, ILogger logger, SchemaFile file)
{
if (column.DataType != null)
{
var name = column.ColumnIdentifier.Value;
var field = column.DataType.GetField(name, false, logger, file);
field.HasIdentity = column.HasIdentity();
field.IsNullable = column.IsNullable();
return(field);
}
if (column.ComputedColumnExpression != null && column.ComputedColumnExpression is ScalarExpression scalarExpression)
{
return(scalarExpression.GetField(column, columns, logger, file));
}
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"Column type is not supported yet. Fragment: \"{column.GetTokenText()}\"");
return(new DefaultField());
}
public static Field GetField(
this ColumnReferenceExpression columnReferenceExpression,
string columnName,
ILogger logger,
SchemaFile file
)
{
// TODO : there are other pseudo columns, find out what they're for
if (columnReferenceExpression.ColumnType == ColumnType.PseudoColumnAction)
{
return(new StringField()
{
Name = columnName ?? "$action",
Type = FieldType.String, // nvarchar(10)
Origin = OriginType.SystemType,
Length = 10,
});
}
var identifiers = columnReferenceExpression
.MultiPartIdentifier
.Identifiers
.Select(x => x.Value)
.Where(x => !string.IsNullOrWhiteSpace(x))
.ToList();
var columnReferenceName = identifiers.Last();
if (string.IsNullOrWhiteSpace(columnName))
{
columnName = columnReferenceName;
}
if (identifiers.Count > 1)
{
var schemaObjectReferenceName = identifiers[identifiers.Count - 2];
var databaseObject = file.FileContext.GetSchema(schemaObjectReferenceName);
if (databaseObject != null)
{
var column = databaseObject
.Columns
.FirstOrDefault(c => c.Name.Equals(columnReferenceName, StringComparison.InvariantCultureIgnoreCase))
?? databaseObject
.Columns
.FirstOrDefault(c => c.Type.Equals(FieldType.WildCard));
if (column != null)
{
return(column.Copy(columnName));
}
logger.Log(LogLevel.Error,
LogType.MissingColumnDefinition,
file.Path,
$"Column \"{columnReferenceName}\" does not exists in " +
$"\"{databaseObject.GetQualifiedIdentfier()}\" {databaseObject.Type.ToString().ToLower()}. " +
$"This most likely means that underlying table definition is outdated. " +
$"If it is a system object add it to the list of wildcard tables.");
}
else
{
logger.Log(LogLevel.Error,
LogType.MissingSchemaObject,
file.Path,
$"Column prefix \"{schemaObjectReferenceName}\" doesn't match any schema object reference");
}
}
else
{
// guess and take first column with the same name
// this should be safe because you can't select a column that is present in more than one table reference without a prefix
// search the stack from the top and take first value that has the same name
var column = file
.FileContext
.StatementReferences
.SelectMany(x => x)
.SelectMany(x => x.Value.Columns)
.FirstOrDefault(x => x.Name != null && x.Name.Equals(columnReferenceName, StringComparison.InvariantCultureIgnoreCase));
if (column != null)
{
return(column.Copy(columnName));
}
}
logger.Log(LogLevel.Warning,
LogType.AddingUnknownColumn,
file.Path,
$"Column \"{columnReferenceName}\" doesn't exist in any referenced table. " +
$"Adding column with undefined type.");
return(new UnknownField()
{
Name = columnName,
});
}
public static Field GetField(this DataTypeReference dataTypeReference, string name, bool isNullable, ILogger logger, SchemaFile file)
{
if (dataTypeReference is SqlDataTypeReference sqlDataTypeReference)
{
return(sqlDataTypeReference.GetField(name, isNullable, logger, file));
}
if (dataTypeReference is UserDataTypeReference userDataTypeReference)
{
return(userDataTypeReference.GetField(name, isNullable, logger, file));
}
if (dataTypeReference is XmlDataTypeReference xmlDataTypeReference)
{
return(new DefaultField()
{
Name = name,
Type = FieldType.Xml,
IsNullable = isNullable,
});
}
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"{dataTypeReference.GetType()} data type reference is not supported yet. " +
$"Fragment: \"{dataTypeReference.GetTokenText()}\"");
return(new UnknownField()
{
Name = name,
});
}
public static IEnumerable <Field> GetFields(this IEnumerable <ColumnDefinition> columns, ILogger logger, SchemaFile file)
{
return(columns.Select(column => column.GetField(columns, logger, file)));
}
public static string GetQualifiedIdentfier(this SchemaObjectName schemaObjectName, SchemaFile file)
{
var qualifiers = new List <string>()
{
schemaObjectName.DatabaseIdentifier?.Value ?? file.Context.Name,
string.IsNullOrEmpty(schemaObjectName.SchemaIdentifier?.Value)
? SchemaObject.DefaultSchema
: schemaObjectName.SchemaIdentifier.Value,
schemaObjectName.BaseIdentifier?.Value,
};
return(qualifiers.GetQualifiedIdentfier());
}
public static Field GetField(
this UserDataTypeReference userDataTypeReference,
string name,
bool isNullable,
ILogger logger,
SchemaFile file
)
{
var identifier = userDataTypeReference.Name.GetQualifiedIdentfier(file);
var identifiers = identifier.Split('.');
if (userDataTypeReference.Name.Identifiers.Count.Equals(1))
{
// TODO : trying if it is a system data type.. I basically have 2 options
// 1) hardcode what I'm interested in here
// 2) create SQL files and process those first
// we'll see how big this gets..
var systemTypeName = userDataTypeReference.Name.Identifiers.First().Value;
switch (systemTypeName.ToUpper())
{
case "SYSNAME":
return(new StringField()
{
Name = name,
Type = FieldType.String, // nvarchar
Origin = OriginType.SystemType,
Length = 128,
IsNullable = false,
});
case "GEOGRAPHY":
return(new TableReferenceField()
{
Name = name,
Type = FieldType.Table,
Origin = OriginType.SystemType,
IsNullable = false,
Reference = new Table()
{
File = file,
Database = identifiers[0],
Schema = identifiers[1],
Identifier = identifiers[2],
Columns = new List <Field>()
{
new DefaultField()
{
Name = "Lat",
Type = FieldType.Float,
Origin = OriginType.Table,
IsNullable = false,
},
new DefaultField()
{
Name = "Long",
Type = FieldType.Float,
Origin = OriginType.Table,
IsNullable = false,
},
}
}
});
}
}
var reference = file.Schema.ContainsKey(identifier)
? file.Schema[identifier]
: new Unknown()
{
File = file,
Database = identifiers[0],
Schema = identifiers[1],
Identifier = identifiers[2],
};
if (reference.Type == SchemaObjectType.NotSpecified)
{
logger.Log(LogLevel.Warning,
LogType.MissingSchemaObject,
file.Path,
$"\"{identifier}\" user data type reference is missing in the schema.");
}
return(new TableReferenceField()
{
Name = name,
Type = FieldType.Table,
Origin = OriginType.Reference,
IsNullable = isNullable,
Reference = reference,
});
}
public static int Run(ConfigFile c, string cmd = "", string filename = "")
{
configFile = c;
if (!String.IsNullOrWhiteSpace(cmd))
{
try
{
Validate = new PlainTextResultWriter(Console.Out, Console.Out, Console.Out, Console.Out, configFile.Validate.Outputs.StandardOutputDelimiter);
string sql = cmd;
db = new SqliteDb(configFile.Db.DbName, true, null, configFile.Extract.Delimiter, configFile.Db.LogFile).Init();
SqliteStatus = db.ExecuteQuery(sql, Validate, "Command line query");
}
catch (Exception e)
{
Console.WriteLine("Something went wrong trying to read the input command. Try again... " + e.Message);
}
}
else if (!String.IsNullOrWhiteSpace(filename))
{
try
{
Validate = new PlainTextResultWriter(Console.Out, Console.Out, Console.Out, Console.Out, configFile.Validate.Outputs.StandardOutputDelimiter);
string sql = GetSqlContents(filename);
db = new SqliteDb(configFile.Db.DbName, true, null, configFile.Extract.Delimiter, configFile.Db.LogFile).Init();
SqliteStatus = db.ExecuteQuery(sql, Validate, "Command line file execution");
}
catch (Exception e)
{
Console.WriteLine("Something went wrong trying to read the input file. Try again... " + e.Message);
}
}
else
{
try
{
Validate = new PlainTextResultWriter(Console.Out, Console.Out, Console.Out, Console.Out);
Transform = new PlainTextResultWriter(Console.Out, Console.Out, Console.Out, Console.Out);
Load = new PlainTextResultWriter(Console.Out, Console.Out, Console.Out, Console.Out);
Validations validator = null;
SqliteStatus = 0;
string sql;
db = new SqliteDb(configFile.Db.DbName, configFile.Db.UseExistingDb, null, configFile.Extract.Delimiter, configFile.Db.LogFile).Init();
// NPS_TODO add error outputting for when these fail to load
if (configFile.Steps.Validate)
{
Validate = ConfigFileInit.InitValidateFromConfig(configFile);
}
if (configFile.Steps.Transform)
{
Transform = ConfigFileInit.InitTransformFromConfig(configFile);
}
if (configFile.Steps.Load)
{
Load = ConfigFileInit.InitLoadFromConfig(configFile, db);
}
DirectoryInfo schemaDirInfo = new DirectoryInfo(configFile.Extract.Schemas);
DirectoryInfo sourceFilesDirInfo = new DirectoryInfo(configFile.Extract.Source);
DirectoryInfo validationDirInfo = new DirectoryInfo(configFile.Validate.ValidationSource);
DirectoryInfo loadDirInfo = new DirectoryInfo(configFile.Load.LoadSource);
DirectoryInfo seedDirInfo = new DirectoryInfo(configFile.Extract.SeedData);
SchemaFile schemaFile = null;
SqliteModeler modeler = null;
Validate.BeginOutput("");
if (configFile.Steps.Extract)
{
//NPS_TODO: Add check to see if we need to do this on reuse db
sql = new SqliteModeler().CreateGeneralErrorWarningDdl();
SqliteStatus = db.ModifyDdlFromSqlString(sql);
Validate.WriteVerbose(SqliteStatus.ToString() + ":" + sql + "||" + db.LastError);
// load seed data
if (seedDirInfo.Exists)
{
foreach (var seedFile in seedDirInfo.GetFiles("*.sql"))
{
//NPS_TODO: Add check to see if we need to do this on reuse db
currentStep = SetCurrentStep("Reading seed data from " + seedFile.Name, Validate);
SqliteStatus = db.ExecuteQuery(File.ReadAllText(seedFile.FullName), Validate);
}
}
}
foreach (var file in schemaDirInfo.GetFiles("*.json"))
{
//NPS_TODO: See if Scheme has already been created
// create schemafile object
currentStep = SetCurrentStep("Reading schema file: " + file.Name, Validate);
schemaFile = JsonConvert.DeserializeObject <SchemaFile>(File.ReadAllText(file.FullName));
Validate.BeginContext(schemaFile.Name, Globals.ResultWriterDestination.stdOut);
if (configFile.Validate.Outputs.Warnings && (configFile.Validate.Outputs.StandardOutputConnectionString != configFile.Validate.Outputs.WarningsOutputConnectionString))
{
if (Validate.ResultMode == "delimited")
{
Validate.Write(schemaFile.Name, Globals.ResultWriterDestination.Warning);
}
else if (Validate.ResultMode == "json")
{
Validate.BeginContext(schemaFile.Name, Globals.ResultWriterDestination.Warning);
}
}
// create SQLiteModeler
currentStep = SetCurrentStep("Setting schema file: " + file.Name, Validate);
modeler = new SqliteModeler().SetSchemaFile(schemaFile);
// create SQL from schemafile
currentStep = SetCurrentStep("Creating DDL...", Validate);
sql = modeler.CreateDdl(true).Ddl;
// execute DDL in schemafile
currentStep = SetCurrentStep("Modifying SQLite DB with DDL...", Validate);
SqliteStatus = db.ModifyDdlFromSqlString(sql);
Validate.WriteVerbose(SqliteStatus.ToString() + ":" + sql);
// create SQL from schemafile
currentStep = SetCurrentStep("Creating DDL...", Validate);
sql = modeler.CreateErrorDdl().ErrorDdl;
// execute DDL in schemafile
currentStep = SetCurrentStep("Modifying SQLite DB with Error DDL...", Validate);
SqliteStatus = db.ModifyDdlFromSqlString(sql);
Validate.WriteVerbose(SqliteStatus.ToString() + ":" + sql);
// find linked flat file
var files = GetFullFilePath(sourceFilesDirInfo, schemaFile.Flatfile, Validate);
Flatfile flatfile = null;
if (configFile.Steps.Extract)
{
foreach (var f in files)
{
var reuseDbSql = "SELECT * FROM FileAudit WHERE FileName = '" + f + "';";
bool shouldReadFile = true;
if (configFile.Db.UseExistingDb && db.QueryHasResults(reuseDbSql))
{
shouldReadFile = false;
}
if (f != String.Empty && shouldReadFile)
{
//import flat file
currentStep = SetCurrentStep("Importing flatfile " + f + "...", Validate);
//NPS_TODO: setup File
flatfile = new Flatfile(f, schemaFile.Name, schemaFile.Delimiter.ToString(), "\"", true, null, schemaFile);
int linesRead = 0;
SqliteStatus = db.ImportDelimitedFile(flatfile, out linesRead, configFile, true);
// NPS_TODO: Make linenum optional in configfile
currentStep = SetCurrentStep("Finished reading flatfile " + f + "...", Validate);
var auditSql = "INSERT INTO FileAudit VALUES ('" + f + "', CURRENT_TIMESTAMP, " + schemaFile.Fields.Count + ", " + linesRead + ");";
SqliteStatus = db.ExecuteQuery(auditSql, Validate);
}
}
if (files.Count == 0)
{
SqliteStatus = db.ExecuteQuery("INSERT INTO GeneralErrors VALUES ('File Missing', 'None', '" + schemaFile.Name + "', 'Error', 'Failed to find file matching " + schemaFile.Flatfile + "');", Validate);
Validate.EndContext(Globals.ResultWriterDestination.stdOut);
continue; // no files, continue the loop so no validation happens
}
else
{
var metadataSql = "";
// NPS_TODO: Handle Derivations flag
// DERIVATIONS
foreach (var schemaField in flatfile.Schemafile.Fields.Where(x => x.ColumnType == ColumnType.Derived).Select(x => x))
{
var derivationSql = "UPDATE " + flatfile.Tablename + " SET " + schemaField.Name + " = " + schemaField.Derivation + ";";
SqliteStatus = db.ExecuteQuery(derivationSql, Validate);
}
foreach (var schemaField in schemaFile.Fields)
{
metadataSql = "INSERT INTO TableMetadata VALUES ('" + schemaFile.Name + "', '" + schemaField.Name + "', '" + schemaField.DataType + "');";
// finding numeric precision/scale for sql server
// with cte as (select length(b)-1 as precision, length(b) - instr(b, '.') as scale from foo) select case when
// max(precision) - min(scale) >= 38 then 38 else max(precision) end as precision, max(scale) from cte;
SqliteStatus = db.ExecuteQuery(metadataSql, Validate);
}
metadataSql = "INSERT INTO TableMetadata VALUES ('" + schemaFile.Name + "', 'LineNum', 'int');";
SqliteStatus = db.ExecuteQuery(metadataSql, Validate);
}
}
#region Validate
// file level validations
if (configFile.Steps.Validate)
{
validator = new Validations(configFile.Validate.SchemaErrorSettings, db, Validate, (code => SqliteStatus = code), configFile.Validate, schemaFile.Name);
currentStep = SetCurrentStep("Validating file", Validate);
foreach (var schemaField in schemaFile.Fields)
{
validator.ValidateFields(schemaField, schemaFile.Name, Validate);
}
if (schemaFile.SummarizeResults)
{
validator.PrintSummaryResults(schemaFile.Name, Globals.ResultWriterDestination.stdOut);
if (configFile.Validate.Outputs.Warnings)
{
validator.PrintSummaryResults(schemaFile.Name, Globals.ResultWriterDestination.Warning);
}
}
validator.PrintDetailResults(schemaFile.Name, Globals.ResultWriterDestination.stdOut);
if (configFile.Validate.Outputs.Warnings)
{
validator.PrintDetailResults(schemaFile.Name, Globals.ResultWriterDestination.Warning);
}
}
Validate.EndContext(Globals.ResultWriterDestination.stdOut);
if (Validate.ResultMode == "json" && configFile.Validate.Outputs.Warnings &&
(configFile.Validate.Outputs.StandardOutputConnectionString != configFile.Validate.Outputs.WarningsOutputConnectionString))
{
Validate.EndContext(Globals.ResultWriterDestination.Warning);
}
} // end for each flat file
//
// Custom validation checks
// Possibly cross file / multi table joins
//
if (configFile.Steps.Validate && !string.IsNullOrWhiteSpace(configFile.Validate.ValidationSource))
{
string ctx = "Custom Data Validation Checks";
// Perhaps we have no flatfiles but do have a db and custom validations - in this case validator would be null
if (validator == null)
{
validator = new Validations(configFile.Validate.SchemaErrorSettings, db, Validate, (code => SqliteStatus = code), configFile.Validate, "GeneralErrors");
}
Validate.BeginContext(ctx, Globals.ResultWriterDestination.stdOut);
if (configFile.Validate.Outputs.Warnings && (configFile.Validate.Outputs.StandardOutputConnectionString != configFile.Validate.Outputs.WarningsOutputConnectionString))
{
if (Validate.ResultMode == "delimited")
{
Validate.Write(ctx, Globals.ResultWriterDestination.Warning);
}
else if (Validate.ResultMode == "json")
{
Validate.BeginContext(ctx, Globals.ResultWriterDestination.Warning);
}
}
foreach (var validationFile in validationDirInfo.GetFiles("*.sql"))
{
currentStep = SetCurrentStep("Getting contents from: " + validationFile.Name, Validate);
validator.ValidateCustom(validationFile, configFile.Validate.QueryErrorLimit, configFile.Validate.Outputs.Warnings);
}
Validate.EndContext(Globals.ResultWriterDestination.stdOut);
if (Validate.ResultMode == "json" && configFile.Validate.Outputs.Warnings &&
(configFile.Validate.Outputs.StandardOutputConnectionString != configFile.Validate.Outputs.WarningsOutputConnectionString))
{
Validate.EndContext(Globals.ResultWriterDestination.Warning);
}
}
if (configFile.Steps.Validate)
{
validator.PrintGeneralIssues(Globals.ResultWriterDestination.stdOut);
if (configFile.Validate.Outputs.Warnings)
{
validator.PrintGeneralIssues(Globals.ResultWriterDestination.Warning);
}
}
Validate.EndOutput("");
#endregion Validate
Load.BeginOutput("");
if (configFile.Steps.Load)
{
foreach (var loadFile in loadDirInfo.GetFiles("*.sql"))
{
currentStep = SetCurrentStep("Getting contents from: " + loadFile.Name, Validate);
string context = String.Empty;
sql = GetSqlContents(loadFile.FullName);
context = loadFile.Name;
SqliteStatus = db.ExecuteQuery(sql, Load, context);
}
}
}
catch (Exception e)
{
Validate.WriteStd("ERROR on step: " + currentStep);
Validate.WriteError("[ERROR] " + DateTime.Now);
Validate.WriteError("[ERROR MSG] " + e.Message);
if (db != null && !string.IsNullOrWhiteSpace(db.LastError))
{
Validate.WriteError("[DB MSG] " + db.LastError);
}
Validate.WriteError(e.StackTrace);
return(SqliteStatus);
}
finally
{
db.Close();
Validate.Dispose();
}
}
return(SqliteStatus);
}
// TODO : manage everything through context, this could then return void
public static IEnumerable <SchemaObject> CollectLocalSchema(this IEnumerable <TSqlStatement> statements, ILogger logger, SchemaFile file)
{
var dataSets = new List <SchemaObject>();
if (statements.Any())
{
foreach (var statement in statements)
{
var data = statement
.CollectLocalSchema(logger, file)
.ToList();
dataSets.AddRange(data);
}
}
return(dataSets);
}
public static Field GetField(this FunctionCall functionCall,
string columnName,
ILogger logger,
SchemaFile file
)
{
var functionName = functionCall.FunctionName.Value;
columnName = columnName ?? functionCall.GetTokenText();
// scalar valued system functions
switch (functionName.ToUpper())
{
case "SCOPE_IDENTITY":
case "@@IDENTITY": // TODO : this is global variable and not a function
{
// https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql
return(new DecimalField()
{
Name = columnName,
IsNullable = false,
Precision = 38,
Scale = 0,
Origin = OriginType.FunctionReturn,
});
}
case "NEWID":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.UniqueIdentifier,
Origin = OriginType.FunctionReturn,
});
case "COUNT":
case "BINARY_CHECKSUM":
case "DATEDIFF":
case "OBJECTPROPERTY":
case "ERROR_NUMBER":
case "ERROR_SEVERITY":
case "ERROR_STATE":
case "ERROR_LINE":
case "DB_ID":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.Int,
Origin = OriginType.FunctionReturn,
});
case "ERROR_MESSAGE":
return(new StringField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.String, // NVARCHAR
Origin = OriginType.FunctionReturn,
Length = 4000,
});
case "ERROR_PROCEDURE":
case "HOST_NAME":
case "SUSER_SNAME":
case "DATEPART":
return(new StringField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.String, // NVARCHAR
Origin = OriginType.FunctionReturn,
Length = 128,
});
case "COUNT_BIG":
case "ROW_NUMBER":
case "RANK":
case "DENSE_RANK":
case "NTILE":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.BigInt,
Origin = OriginType.FunctionReturn,
});
case "LOG":
case "PERCENTILE_CONT":
case "PERCENTILE_DISC":
case "PERCENT_RANK":
case "STDEV":
case "STDEVP":
case "RAND":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.Float, // TODO : some are FLOAT and some FLOAT(53), does that matter?
Origin = OriginType.FunctionReturn,
});
case "GETDATE":
case "DATEADD":
case "GETUTCDATE":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.DateTime,
Origin = OriginType.FunctionReturn,
});
case "DATENAME":
return(new StringField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.String,
Origin = OriginType.FunctionReturn,
Length = 0, // TODO..
});
case "SYSDATETIME":
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.DateTime2, // TODO : datetime2(7) .. does that matter?
Origin = OriginType.FunctionReturn,
});
case "TRIM":
case "LTRIM":
case "RTRIM":
case "UPPER":
case "LOWER":
case "SUM": // TODO : Returns the summation of all expression values in the most precise expression data type.
case "MIN":
case "MAX":
{
return(functionCall
.Parameters
.First()
.GetField(columnName, logger, file));
}
case "AVG": // The evaluated result of expression determines the return type
{
var column = functionCall
.Parameters
.First()
.GetField(columnName, logger, file);
// https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql
switch (column.Type)
{
case FieldType.SmallInt:
case FieldType.TinyInt:
column.Type = FieldType.Int;
break;
case FieldType.Decimal:
case FieldType.Numeric:
// TODO : compute the value, this will return decimal(p, s) for now
// decimal category (p, s) returns decimal(38, s) divided by decimal(10, 0)
break;
case FieldType.SmallMoney:
column.Type = FieldType.Money;
break;
case FieldType.Real:
column.Type = FieldType.Float;
break;
}
return(column);
}
case "STUFF":
{
// TODO : this can return binary data as well
// TODO : can I determine length from character_expression and replaceWith_expression?
return(new StringField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.String,
Origin = OriginType.FunctionReturn,
Length = 0,
});
}
case "LEN":
case "CHARINDEX":
// TODO : bigint if expression is nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise
return(new DefaultField()
{
Name = columnName,
IsNullable = false,
Type = FieldType.Int,
Origin = OriginType.FunctionReturn,
});
case "OBJECT_NAME":
// this is the SYSNAME type
return(new StringField()
{
Name = columnName,
Type = FieldType.String, // nvarchar
Origin = OriginType.SystemType,
Length = 128,
IsNullable = false,
});
default:
break;
}
// scalar valued user functions
//Microsoft.SqlServer.TransactSql.ScriptDom.ExpressionCallTarget
//Microsoft.SqlServer.TransactSql.ScriptDom.MultiPartIdentifierCallTarget
//Microsoft.SqlServer.TransactSql.ScriptDom.UserDefinedTypeCallTarget
var functionKey = string.Empty;
if (functionCall.CallTarget is MultiPartIdentifierCallTarget multiPartIdentifierCallTarget)
{
// TODO : make this pretty somehow
var identifiersCount = multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.Count();
var qualifiedIdentifiers = new List <string>();
if (identifiersCount.Equals(1))
{
qualifiedIdentifiers.Add(file.Context.Name);
qualifiedIdentifiers.Add(multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.First().Value);
}
else if (identifiersCount.Equals(2))
{
qualifiedIdentifiers.AddRange(multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.Select(x => x.Value));
}
else
{
logger.Log(LogLevel.Error, $"Unable to determine qualified identifier. Fragment: \"{functionCall.GetTokenText()}\"");
}
qualifiedIdentifiers.Add(functionName);
functionKey = qualifiedIdentifiers.GetQualifiedIdentfier();
}
if (file.Schema.ContainsKey(functionKey))
{
var function = file.Schema[functionKey];
var firstColumn = function.Columns.FirstOrDefault();
if (firstColumn != null)
{
return(firstColumn.Copy(columnName));
}
}
// TODO : table valued user functions
// TODO : failsafe but do not return first.. compute the value
var first = functionCall
.Parameters
.Select(x => x.GetField(columnName, logger, file))
.FirstOrDefault(x => x.Type != FieldType.NotSpecified);
if (first != null)
{
return(first);
}
logger.Log(LogLevel.Error, $"Unable to determine column type from function call. Fragment: \"{functionCall.GetTokenText()}\"");
return(new UnknownField()
{
Name = columnName,
});
}
public static FieldType GetFieldType(this DataTypeReference dataTypeReference, ILogger logger, SchemaFile file)
{
if (dataTypeReference is SqlDataTypeReference sqlDataTypeReference)
{
return(sqlDataTypeReference.GetFieldType());
}
if (dataTypeReference is UserDataTypeReference)
{
return(FieldType.UserDataType);
}
if (dataTypeReference is XmlDataTypeReference)
{
return(FieldType.Xml);
}
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"{dataTypeReference.GetType()} column type is not supported yet. " +
$"Fragment: \"{dataTypeReference.GetTokenText()}\"");
return(FieldType.NotSpecified);
}
public static SchemaObject GetSchema(
this ExecutableEntity executableEntity,
ILogger logger,
SchemaFile file
)
{
switch (executableEntity)
{
case ExecutableProcedureReference executableProcedureReference:
{
// TODO : requires more work, there is also a Variable property, who knows what's that for
var identifier = executableProcedureReference.ProcedureReference.ProcedureReference.Name.GetQualifiedIdentfier(file);
if (file.Schema.ContainsKey(identifier))
{
return(file.Schema[identifier]);
}
var parameters = new List <Parameter>();
var schema = SchemaObject.SystemSchema;
var baseIdentifier = executableProcedureReference.ProcedureReference.ProcedureReference.Name.BaseIdentifier.Value;
var columns = new List <Field>();
switch (baseIdentifier.ToUpper())
{
case "SP_EXECUTESQL":
parameters.Add(new Parameter(new StringField()
{
Name = "@stmt",
Type = FieldType.String, // nvarchar(max)
Origin = OriginType.SystemType,
IsNullable = false,
Length = 0, // TODO
})
{
HasDefaultValue = false,
IsOutput = false,
IsReadOnly = false,
});
break;
case "SP_ADDEXTENDEDPROPERTY":
parameters = new List <Parameter>()
{
new Parameter(new StringField()
{
Name = "@name",
Type = FieldType.String, // sysname
Origin = OriginType.SystemType,
Length = 128,
})
{
HasDefaultValue = false,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new DefaultField()
{
Name = "@value",
Type = FieldType.SqlVariant,
Origin = OriginType.SystemType,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level0type",
Type = FieldType.String, // varchar(128)
Origin = OriginType.SystemType,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level0name",
Type = FieldType.String, // sysname
Origin = OriginType.SystemType,
Length = 128,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level1type",
Type = FieldType.String, // varchar(128)
Origin = OriginType.SystemType,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level1name",
Type = FieldType.String, // sysname
Origin = OriginType.SystemType,
Length = 128,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level2type",
Type = FieldType.String, // varchar(128)
Origin = OriginType.SystemType,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
new Parameter(new StringField()
{
Name = "@level2name",
Type = FieldType.String, // sysname
Origin = OriginType.SystemType,
Length = 128,
})
{
HasDefaultValue = true,
IsOutput = false,
IsReadOnly = false,
},
};
break;
case "XP_CMDSHELL":
parameters.Add(new Parameter(new StringField()
{
Name = "@command_string",
Type = FieldType.String, // varchar(8000) / nvarchar(4000)
Origin = OriginType.SystemType,
IsNullable = false,
Length = 8000, // TODO
})
{
HasDefaultValue = false,
IsOutput = false,
IsReadOnly = false,
});
columns.Add(new StringField()
{
Name = null,
Type = FieldType.String, // nvarchar(255)
Origin = OriginType.SystemType,
IsNullable = false,
Length = 255,
});
break;
default:
logger.Log(LogLevel.Error,
LogType.MissingSchemaObject,
file.Path,
$"\"{identifier}\" executable entity is missing in the schema.");
schema = executableProcedureReference
.ProcedureReference
.ProcedureReference
.Name
.SchemaIdentifier
?.Value
?? SchemaObject.DefaultSchema;
break;
}
var storedProc = new StoredProcedure()
{
File = file,
Database = executableProcedureReference
.ProcedureReference
.ProcedureReference
.Name
.DatabaseIdentifier
?.Value
?? file.Context.Name,
Schema = schema,
Identifier = baseIdentifier,
Parameters = parameters,
Columns = columns,
};
return(storedProc);
}
// TODO : ugh, what can I do with this?
case ExecutableStringList executableStringList:
default:
//logger.Log(LogLevel.NotSupportedYet, $"\"{executableEntity.GetType()}\" executable entity is not supported yet.");
return(new Unknown()
{
File = file,
Database = file.Context.Name,
Schema = SchemaObject.DefaultSchema,
Identifier = "Unknown",
});
}
}
public static List <SchemaObjectReference> GetSchemaObjectReferences(this JoinTableReference qualifiedJoin, ILogger logger, SchemaFile file)
{
var databaseObjectReferences = new List <SchemaObjectReference>();
var firstReferences = qualifiedJoin.FirstTableReference.GetSchemaObjectReferences(logger, file);
if (firstReferences.Any())
{
databaseObjectReferences.AddRange(firstReferences);
}
var secondReferences = qualifiedJoin.SecondTableReference.GetSchemaObjectReferences(logger, file);
if (secondReferences.Any())
{
databaseObjectReferences.AddRange(secondReferences);
}
return(databaseObjectReferences);
}
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 override void AssertResults(ImplicitConversionInMergeStatement rule, SchemaFile file, IList <ValidationResult> results)
{
Assert.IsTrue(results.Count == 2);
Assert.AreEqual("target.photo_id = source.photo_id", results[0].Fragment.GetTokenText()); // CTE
Assert.AreEqual("target.photo_id = @photo_id", results[1].Fragment.GetTokenText()); // set clause
}
public static Field GetField(this Literal literal, string columnName, ILogger logger, SchemaFile file)
{
switch (literal)
{
case StringLiteral stringLiteral:
return(new StringField()
{
Type = FieldType.String,
Origin = OriginType.Literal,
IsNullable = false,
Name = columnName ?? $"StringLiteral: \"{stringLiteral.Value}\"",
Length = stringLiteral.Value.Length,
});
case IntegerLiteral integerLiteral:
return(new DefaultField()
{
Type = FieldType.Int,
Origin = OriginType.Literal,
IsNullable = false,
Name = columnName ?? $"IntegerLiteral: \"{integerLiteral.Value}\"",
});
case NullLiteral nullLiteral:
return(new DefaultField()
{
Type = FieldType.Null,
Origin = OriginType.Literal,
IsNullable = true,
Name = columnName ?? nullLiteral.Value,
});
case NumericLiteral numericLiteral:
switch (numericLiteral.LiteralType)
{
case LiteralType.Numeric when decimal.TryParse(numericLiteral.Value, out var n):
// TODO : parse the value or use the decimal
//var f = Math.Floor(n);
//var c = Math.Ceiling(n);
return(new DecimalField()
{
Type = FieldType.Decimal,
Origin = OriginType.Literal,
IsNullable = false,
Name = columnName ?? $"NumericLiteral: \"{numericLiteral.Value}\"",
Precision = numericLiteral.Value.Length - 1,
Scale = numericLiteral.Value.Split('.')[1].Length,
});
case LiteralType.Real when double.TryParse(numericLiteral.Value, out var d):
return(new DefaultField()
{
Type = FieldType.Float,
Origin = OriginType.Literal,
IsNullable = false,
Name = columnName ?? $"NumericLiteral: \"{numericLiteral.Value}\"",
});
}
break;
case BinaryLiteral binaryLiteral:
return(new DefaultField()
{
Type = FieldType.Binary,
Origin = OriginType.Literal,
Name = columnName ?? $"BinaryLiteral: \"{binaryLiteral.Value}\"",
});
case DefaultLiteral defaultLiteral:
return(new DefaultField()
{
Type = FieldType.WildCard,
Origin = OriginType.Literal,
Name = columnName ?? "DEFAULT",
});
default:
// TODO : other literals..
//Real = 1,
//Money = 2,
//Max = 7,
//Odbc = 8,
//Identifier = 9,
logger.Log(LogLevel.Warning,
LogType.NotSupportedYet,
file.Path,
$"Unable to determine column type from \"{literal.GetType()}\" literal. Fragment: \"{literal.GetTokenText()}\"");
break;
}
return(new UnknownField()
{
Name = "UnknownLiteral",
Origin = OriginType.Literal,
});
}
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 override IList <ValidationResult> Execute(SchemaFile file)
{
return(Fragments
.Where(ScalarExpressionIsNotPrefixed)
.ToValidationResults());
}