static List <string> GetFullFilePath(DirectoryInfo dir, string filename, IResultWriter writer) { Regex reg = new Regex(filename); List <string> fileinfo = new List <string>(); //string file = String.Empty; try { fileinfo = Directory.GetFiles(dir.FullName, @"*.*") .Where(path => reg.IsMatch(path)) .ToList(); } catch (Exception e) { writer.WriteVerbose("Couldn't locate file " + filename); } return(fileinfo); }
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); }
static string SetCurrentStep(string desc, IResultWriter writer) { writer.WriteVerbose("[" + DateTime.Now + "] " + desc); return(desc); }
public void ValidateFields(SchemaField schemaField, string tableName, IResultWriter writer) { var sqlbase = @"INSERT INTO " + tableName + "_Errors" + @" SELECT {0}, '" + schemaField.Name + @"', {1},* FROM " + tableName + @" {2} LIMIT " + vOptions.QueryErrorLimit + ";"; string sql = ""; if (schemaField.Constraints.Required) { sql = String.Format(sqlbase, "'Required Field'", esettings.RequiredErrorLevel.SingleQuote(), " WHERE " + schemaField.Name + " = '' OR " + schemaField.Name + " IS NULL"); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Required Field check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Required Field check for " + schemaField.Name); } if (schemaField.DataType != DataType.@string && (schemaField.Constraints.Required || (esettings.ErrorOnUnrequiredWithBadDatatype && !schemaField.Constraints.Required))) { sql = GetSqlForDatatype(schemaField, sqlbase, esettings.DatatypeErrorLevel.SingleQuote()); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Datatype check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Datatype check for " + schemaField.Name); } if (!double.IsNaN(schemaField.Constraints.Maximum)) { sql = String.Format(sqlbase, "'Maximum Value'", esettings.MaximumErrorLevel.SingleQuote(), "WHERE COMPARE(" + schemaField.Name + @", '>', " + schemaField.Constraints.Maximum + @", '" + schemaField.DataType.ToString() + "')"); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Maximum Value check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Maximum Value check for " + schemaField.Name); } if (!double.IsNaN(schemaField.Constraints.Minimum)) { sql = String.Format(sqlbase, "'Minimum Value'", esettings.MinimumErrorLevel.SingleQuote(), "WHERE COMPARE(" + schemaField.Name + @", '<', " + schemaField.Constraints.Minimum + @", '" + schemaField.DataType.ToString() + "')"); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Minimum Value check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Minimum Value check for " + schemaField.Name); } if (schemaField.Constraints.MinLength > -1) { sql = String.Format(sqlbase, "'Minimum Length'", esettings.MinLengthErrorLevel.SingleQuote(), "WHERE LENGTH(" + schemaField.Name + @") < " + schemaField.Constraints.MinLength); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Minimum Length check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Minimum Length check for " + schemaField.Name); } if (schemaField.Constraints.MaxLength > -1) { sql = String.Format(sqlbase, "'Maximum Length'", esettings.MaxLengthErrorLevel.SingleQuote(), "WHERE LENGTH(" + schemaField.Name + @") > " + schemaField.Constraints.MaxLength); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Maximum Length check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Maximum Length check for " + schemaField.Name); } if ((schemaField.Constraints.Pattern != null) && (schemaField.Constraints.Pattern != String.Empty)) { sql = String.Format(sqlbase, "'Invalid Value(pattern)'", esettings.PatternErrorLevel.SingleQuote(), "WHERE '" + schemaField.Constraints.Pattern + @"' NOT REGEXP " + schemaField.Name); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Pattern check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Pattern check for " + schemaField.Name); } if (schemaField.Constraints.Enum != null && schemaField.Constraints.Enum.Length > 0) { var values = "(" + String.Join(",", schemaField.Constraints.Enum.ToList().Select(x => "'" + x + "'")) + ")"; sql = String.Format(sqlbase, "'Enum Valid Values'", esettings.MaxLengthErrorLevel.SingleQuote(), "WHERE " + schemaField.Name + @" NOT IN " + values); writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Enum Valid Values check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Enum Valid Values check for " + schemaField.Name); } if (schemaField.Constraints.Unique) { // Unique speed hack sql = @"INSERT INTO " + tableName + "_Errors" + @" SELECT 'Unique', '" + schemaField.Name + @"'," + esettings.UniqueErrorLevel.SingleQuote() + @" ,* FROM " + tableName + @" WHERE rowid IN (SELECT rowid FROM " + tableName + @" GROUP BY " + schemaField.Name + "HAVING COUNT(*) > 1;"; writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Unique check for " + schemaField.Name); SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount); writer.WriteVerbose("[" + DateTime.Now + "] " + "End Unique check for " + schemaField.Name); } }