public override int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd, IManagedTransaction transaction = null) { var autoIncrement = discoveredTable.DiscoverColumns(transaction).SingleOrDefault(c => c.IsAutoIncrement); if (autoIncrement == null) { return(Convert.ToInt32(cmd.ExecuteScalar())); } var p = discoveredTable.Database.Server.Helper.GetParameter("identityOut"); p.Direction = ParameterDirection.Output; p.DbType = DbType.Int32; cmd.Parameters.Add(p); cmd.CommandText += " RETURNING " + autoIncrement + " INTO :identityOut;"; cmd.CommandText = "BEGIN " + Environment.NewLine + cmd.CommandText + Environment.NewLine + "COMMIT;" + Environment.NewLine + "END;"; cmd.ExecuteNonQuery(); return(Convert.ToInt32(p.Value)); }
public override void MakeDistinct(DiscoveredTable discoveredTable, int timeoutInSeconds) { string sql = @"DELETE f FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum FROM {1} ) as f where RowNum > 1"; string columnList = string.Join(",", discoveredTable.DiscoverColumns().Select(c => c.GetRuntimeName())); string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName()); var server = discoveredTable.Database.Server; using (var con = server.GetConnection()) { con.Open(); var cmd = server.GetCommand(sqlToExecute, con); cmd.CommandTimeout = timeoutInSeconds; cmd.ExecuteNonQuery(); } }
public override int Run() { DiscoveredTable tbl = GetServer(_opts.DatabaseConnectionString, _opts.DatabaseType, _opts.TableName); var server = tbl.Database.Server; _factory = new DatabaseFailureFactory(tbl); _columns = tbl.DiscoverColumns(); _columnsNames = _columns.Select(c => c.GetRuntimeName()).ToArray(); _stringColumns = _columns.Select(c => c.GetGuesser().Guess.CSharpType == typeof(string)).ToArray(); using (var con = server.GetConnection()) { con.Open(); var cmd = server.GetCommand( string.Format("SELECT {0} FROM {1}" , string.Join("," + Environment.NewLine, _columns.Select(c => c.GetFullyQualifiedName()).ToArray()) , tbl.GetFullyQualifiedName()), con); _logger.Info("About to send command:" + Environment.NewLine + cmd.CommandText); var reader = cmd.ExecuteReader(); foreach (Failure failure in reader.Cast <DbDataRecord>().SelectMany(GetFailuresIfAny)) { AddToReports(failure); } CloseReports(); } return(0); }
private void CheckColumnDefinitionsMatchArchive() { List <string> errors = new List <string>(); var archiveTableCols = _archiveTable.DiscoverColumns().ToArray(); foreach (DiscoveredColumn col in _columns) { var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName())); if (colInArchive == null) { errors.Add("Column " + col.GetRuntimeName() + " appears in Table '" + _table + "' but not in archive table '" + _archiveTable + "'"); } else if (!AreCompatibleDatatypes(col.DataType, colInArchive.DataType)) { errors.Add("Column " + col.GetRuntimeName() + " has data type '" + col.DataType + "' in '" + _table + "' but in Archive table '" + _archiveTable + "' it is defined as '" + colInArchive.DataType + "'"); } } if (errors.Any()) { throw new IrreconcilableColumnDifferencesInArchiveException("The following column mismatch errors were seen:" + Environment.NewLine + string.Join(Environment.NewLine, errors)); } }
public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable) { var syntax = discoveredTable.GetQuerySyntaxHelper(); string sql = @"DELETE f FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum FROM {1} ) as f where RowNum > 1"; string columnList = string.Join(",", discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName()))); string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName()); var server = discoveredTable.Database.Server; using (var con = args.GetManagedConnection(server)) { using (var cmd = server.GetCommand(sqlToExecute, con)) args.ExecuteNonQuery(cmd); } }
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dillutionColumns) { if (!sourceTable.Exists()) { throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo); } //new table will start with the same name as the as the old scripted one DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName); var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable); using (var con = destDatabaseInfo.Server.GetConnection()) { con.Open(); var cmd = destDatabaseInfo.Server.GetCommand(sql, con); cmd.ExecuteNonQuery(); } if (!newTable.Exists()) { throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!"); } foreach (DiscoveredColumn column in newTable.DiscoverColumns()) { bool drop = false; var colName = column.GetRuntimeName(); if (column.IsAutoIncrement) { drop = true; } if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns) { drop = true; } //drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway. if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom)) { drop = true; } var dillution = dillutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName)); if (dillution != null) { column.DataType.AlterTypeTo(dillution.Data_type); } if (drop) { newTable.DropColumn(column); } } }
protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table) { var server = table.Database.Server; job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to run Coalese on table " + table)); var allCols = table.DiscoverColumns(); var pkColumnInfos = tableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).Select(c => c.GetRuntimeName()).ToArray(); var nonPks = allCols.Where(c => !pkColumnInfos.Contains(c.GetRuntimeName())).ToArray(); var pks = allCols.Except(nonPks).ToArray(); if (!pkColumnInfos.Any()) { throw new Exception("Table '" + tableInfo + "' has no IsPrimaryKey columns"); } if (allCols.Length == pkColumnInfos.Length) { job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping Coalesce on table " + table + " because it has no non primary key columns")); return; } //Get an update command for each non primary key column Dictionary <string, Task <int> > sqlCommands = new Dictionary <string, Task <int> >(); foreach (DiscoveredColumn nonPk in nonPks) { sqlCommands.Add(GetCommand(table, pks, nonPk), null); } server.EnableAsync(); using (var con = table.Database.Server.GetConnection()) { con.Open(); if (CreateIndex) { var idxCmd = server.GetCommand(string.Format(@"CREATE INDEX IX_PK_{0} ON {0}({1});", table.GetRuntimeName(), string.Join(",", pks.Select(p => p.GetRuntimeName()))), con); idxCmd.CommandTimeout = Timeout; idxCmd.ExecuteNonQuery(); } foreach (var sql in sqlCommands.Keys.ToArray()) { var cmd = server.GetCommand(sql, con); cmd.CommandTimeout = Timeout; sqlCommands[sql] = cmd.ExecuteNonQueryAsync(); } Task.WaitAll(sqlCommands.Values.ToArray()); } int affectedRows = sqlCommands.Values.Sum(t => t.Result); job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Coalesce on table '" + table + "' completed (" + affectedRows + " rows affected)")); }
public TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true) { _server = table.Database.Server; _table = table; _archiveTable = _table.Database.ExpectTable(table.GetRuntimeName() + "_Archive", table.Schema); _columns = table.DiscoverColumns(); _primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray(); _createDataLoadRunIdAlso = createDataLoadRunIDAlso; }
/// <summary> /// Update the database <paramref name="server"/> to redact the <paramref name="failure"/>. /// </summary> /// <param name="server">Where to connect to get the data, can be null if <see cref="RulesOnly"/> is true</param> /// <param name="failure">The failure to redact/create a rule for</param> /// <param name="usingRule">Pass null to create a new rule or give value to reuse an existing rule</param> public void Update(DiscoveredServer server, Failure failure, IsIdentifiableRule usingRule) { //theres no rule yet so create one (and add to RedList.yaml) if (usingRule == null) { usingRule = Add(failure, RuleAction.Report); } //if we are running in rules only mode we don't need to also update the database if (RulesOnly) { return; } var syntax = server.GetQuerySyntaxHelper(); //the fully specified name e.g. [mydb]..[mytbl] string tableName = failure.Resource; var tokens = tableName.Split('.', StringSplitOptions.RemoveEmptyEntries); var db = tokens.First(); tableName = tokens.Last(); if (string.IsNullOrWhiteSpace(db) || string.IsNullOrWhiteSpace(tableName) || string.Equals(db, tableName)) { throw new NotSupportedException($"Could not understand table name {failure.Resource}, maybe it is not full specified with a valid database and table name?"); } db = syntax.GetRuntimeName(db); tableName = syntax.GetRuntimeName(tableName); DiscoveredTable table = server.ExpectDatabase(db).ExpectTable(tableName); //if we've never seen this table before if (!_primaryKeys.ContainsKey(table)) { var pk = table.DiscoverColumns().SingleOrDefault(k => k.IsPrimaryKey); _primaryKeys.Add(table, pk); } using (var con = server.GetConnection()) { con.Open(); foreach (var sql in UpdateStrategy.GetUpdateSql(table, _primaryKeys, failure, usingRule)) { var cmd = server.GetCommand(sql, con); cmd.ExecuteNonQuery(); } } }
public override void FillDataTableWithTopX(DiscoveredTable table, int topX, DataTable dt, DbConnection connection, DbTransaction transaction = null) { ((OracleConnection)connection).PurgeStatementCache(); var cols = table.DiscoverColumns(); string sql = "SELECT " + string.Join(",", cols.Select(c => c.GetFullyQualifiedName()).ToArray()) + " FROM " + table.GetFullyQualifiedName() + " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY"; var da = table.Database.Server.GetDataAdapter(sql, connection); da.Fill(dt); }
public override void FillDataTableWithTopX(DatabaseOperationArgs args, DiscoveredTable table, int topX, DataTable dt) { using (var con = args.GetManagedConnection(table)) { ((OracleConnection)con.Connection).PurgeStatementCache(); var cols = table.DiscoverColumns(); //apparently * doesn't fly with Oracle DataAdapter string sql = "SELECT " + string.Join(",", cols.Select(c => c.GetFullyQualifiedName()).ToArray()) + " FROM " + table.GetFullyQualifiedName() + " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY"; using (var cmd = table.Database.Server.GetCommand(sql, con)) using (var da = table.Database.Server.GetDataAdapter(cmd)) args.Fill(da, cmd, dt); } }
private void CheckCohortDatabaseHasCorrectTables(ICheckNotifier notifier) { try { var database = DataAccessPortal.GetInstance().ExpectDatabase(this, DataAccessContext.DataExport); DiscoveredTable cohortTable = database.ExpectTable(TableName); if (cohortTable.Exists()) { notifier.OnCheckPerformed(new CheckEventArgs("Found table " + cohortTable + " in database " + Database, CheckResult.Success, null)); var columns = cohortTable.DiscoverColumns(); ComplainIfColumnMissing(TableName, columns, PrivateIdentifierField, notifier); ComplainIfColumnMissing(TableName, columns, ReleaseIdentifierField, notifier); ComplainIfColumnMissing(TableName, columns, DefinitionTableForeignKeyField, notifier); } else { notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + TableName + " in database " + Database, CheckResult.Fail, null)); } DiscoveredTable foundCohortDefinitionTable = database.ExpectTable(DefinitionTableName); if (foundCohortDefinitionTable.Exists()) { notifier.OnCheckPerformed(new CheckEventArgs("Found table " + DefinitionTableName + " in database " + Database, CheckResult.Success, null)); var cols = foundCohortDefinitionTable.DiscoverColumns(); foreach (string requiredField in ExternalCohortTable.CohortDefinitionTable_RequiredFields) { ComplainIfColumnMissing(DefinitionTableName, cols, requiredField, notifier); } } else { notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + DefinitionTableName + " in database " + Database, CheckResult.Fail, null)); } } catch (Exception e) { notifier.OnCheckPerformed(new CheckEventArgs("Could not check table intactness for ExternalCohortTable '" + Name + "'", CheckResult.Fail, e)); } }
public MigrationColumnSet(DiscoveredTable from, DiscoveredTable to, IMigrationFieldProcessor migrationFieldProcessor) { var fromCols = from.DiscoverColumns(); var toCols = to.DiscoverColumns(); migrationFieldProcessor.ValidateFields(fromCols, toCols); SourceTable = from; DestinationTable = to; PrimaryKeys = fromCols.Where(c => c.IsPrimaryKey).ToArray(); FieldsToDiff = new List <DiscoveredColumn>(); FieldsToUpdate = new List <DiscoveredColumn>(); foreach (DiscoveredColumn pk in PrimaryKeys) { if (!toCols.Any(f => f.GetRuntimeName().Equals(pk.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase))) { throw new MissingFieldException("Column " + pk + " is missing from either the destination table"); } } if (!PrimaryKeys.Any()) { throw new Exception("There are no primary keys declared in table " + from); } //figure out things to migrate and whether they matter to diffing foreach (DiscoveredColumn field in fromCols) { if ( field.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase) || field.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase)) { continue; } if (!toCols.Any(c => c.GetRuntimeName().Equals(field.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase))) { throw new MissingFieldException("Field " + field + " is missing from destination table"); } migrationFieldProcessor.AssignFieldsForProcessing(field, FieldsToDiff, FieldsToUpdate); } }
protected override void MutilateTable(IDataLoadEventListener job, ITableInfo tableInfo, DiscoveredTable table) { foreach (var col in table.DiscoverColumns()) { if (AllDataTypes || col.DataType.GetLengthIfString() >= 1) { try { col.DataType.AlterTypeTo(DestinationType); job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "converted column " + col + " to " + DestinationType)); } catch (Exception e) { job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to convert column " + col + " of data type " + col.DataType + " to destination type " + DestinationType, e)); } } } }
public override int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd, IManagedTransaction transaction = null) { var autoIncrement = discoveredTable.DiscoverColumns(transaction).SingleOrDefault(c => c.IsAutoIncrement); if (autoIncrement != null) { cmd.CommandText += $" RETURNING {autoIncrement.GetFullyQualifiedName()};"; } var result = cmd.ExecuteScalar(); if (result == DBNull.Value || result == null) { return(0); } return(Convert.ToInt32(result)); }
private void ValidateIsolationTableSchema(DiscoveredTable toValidate, TableInfo tableInfo, ICheckNotifier notifier) { var expected = tableInfo.GetColumnsAtStage(LoadStage.AdjustRaw).Select(c => c.GetRuntimeName(LoadStage.AdjustRaw)).Union(new [] { SpecialFieldNames.DataLoadRunID }).ToArray(); var found = toValidate.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray(); foreach (var missingFromIsolation in expected.Except(found, StringComparer.CurrentCultureIgnoreCase)) { notifier.OnCheckPerformed( new CheckEventArgs( "Isolation table '" + toValidate + "' did not contain expected column'" + missingFromIsolation + "'", CheckResult.Fail)); } foreach (var unexpectedInIsolation in found.Except(expected, StringComparer.CurrentCultureIgnoreCase)) { notifier.OnCheckPerformed( new CheckEventArgs( "Isolation table '" + toValidate + "' contained an unexpected column'" + unexpectedInIsolation + "'", CheckResult.Fail)); } }
private void ConfirmFitToDestination(DataTable dt, DiscoveredTable tableToLoad, IDataLoadJob job) { var columnsAtDestination = tableToLoad.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray(); //see if there is a shape problem between stuff that is on the server and stuff that is in the flat file if (dt.Columns.Count != columnsAtDestination.Length) { job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "There was a mismatch between the number of columns in the flat file (" + columnsAtDestination.Aggregate((s, n) => s + Environment.NewLine + n) + ") and the number of columns in the RAW database table (" + dt.Columns.Count + ")")); } foreach (DataColumn column in dt.Columns) { if (!columnsAtDestination.Contains(column.ColumnName, StringComparer.CurrentCultureIgnoreCase)) { throw new FlatFileLoadException("Column in flat file called " + column.ColumnName + " does not appear in the RAW database table (after fixing potentially silly names)"); } } }
public void DiscoverState() { _unplannedChildren.Clear(); //assume no children exist foreach (var anticipatedChild in _anticipatedChildren) { anticipatedChild.State = LoadDiagramState.NotFound; } //we dont exist either! if (!Table.Exists()) { State = LoadDiagramState.NotFound; return; } //we do exist State = LoadDiagramState.Found; //discover children and marry them up to planned/ new unplanned ones foreach (var discoveredColumn in Table.DiscoverColumns()) { var match = _anticipatedChildren.SingleOrDefault(c => c.ColumnName.Equals(discoveredColumn.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase)); if (match != null) { match.SetState(discoveredColumn); } else { _unplannedChildren.Add(discoveredColumn); //unplanned column } } //any NotFound or Different etc cols or any unplanned children if (_anticipatedChildren.Any(c => c.State > LoadDiagramState.Found) || _unplannedChildren.Any()) { State = LoadDiagramState.Different;//elevate our state to Different } }
public void Add(DiscoveredTable discoveredTable) { if (items.Any(i => i.Tag.Equals(discoveredTable))) { return; } var snip = new SubstringAutocompleteItem(discoveredTable.GetRuntimeName()); snip.MenuText = discoveredTable.GetRuntimeName(); //name of table snip.Text = discoveredTable.GetFullyQualifiedName(); //full SQL snip.Tag = discoveredTable; //record object for future reference snip.ImageIndex = GetIndexFor(discoveredTable, RDMPConcept.TableInfo.ToString()); AddUnlessDuplicate(snip); DiscoveredColumn[] columns = null; try { if (discoveredTable.Exists()) { columns = discoveredTable.DiscoverColumns(); } } catch (Exception) { //couldn't load nevermind } if (columns != null) { foreach (var col in columns) { Add(col); } } }
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dilutionColumns) { if (!sourceTable.Exists()) { throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo); } //new table will start with the same name as the as the old scripted one DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName); var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable); _listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Creating table with SQL:" + sql)); using (var con = destDatabaseInfo.Server.GetConnection()) { con.Open(); using (var cmd = destDatabaseInfo.Server.GetCommand(sql, con)) cmd.ExecuteNonQuery(); } if (!newTable.Exists()) { throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!"); } foreach (DiscoveredColumn column in newTable.DiscoverColumns()) { bool drop = false; var colName = column.GetRuntimeName(); if (column.IsAutoIncrement) { drop = true; } //drop hic_ columns if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns) { drop = true; } //if the ColumnInfo is explicitly marked to be ignored if (_tableInfo.ColumnInfos.Any(c => c.IgnoreInLoads && c.GetRuntimeName(_copyToBubble.ToLoadStage()).Equals(colName))) { _listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is marked IgnoreInLoads")); drop = true; } //also drop any columns we have specifically been told to ignore in the DLE configuration if (_hicDatabaseConfiguration.IgnoreColumns != null && _hicDatabaseConfiguration.IgnoreColumns.IsMatch(colName)) { _listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is matches the gloabl ignores pattern ({_hicDatabaseConfiguration.IgnoreColumns})")); drop = true; } //drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway. if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom)) { drop = true; } var dilution = dilutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName)); if (dilution != null) { _listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Altering diluted column {colName} to {dilution.Data_type}")); column.DataType.AlterTypeTo(dilution.Data_type); } if (drop) { newTable.DropColumn(column); } } }
public UnplannedTable(DiscoveredTable table) { Table = table; Columns = table.DiscoverColumns(); }
/// <summary> /// Checks the object representation (Type) is perfectly synched with the underlying database (table must exist with matching name and all parameters must be column names with no unmapped fields) /// </summary> /// <param name="notifier"></param> /// <param name="type"></param> /// <param name="tables"></param> private void CheckEntities(ICheckNotifier notifier, Type type, DiscoveredTable[] tables) { if (type.IsInterface) { return; } if (type.IsAbstract) { return; } if (typeof(SpontaneousObject).IsAssignableFrom(type)) { return; } if (type.Name.StartsWith("Spontaneous")) { return; } //make sure argument was IMaps.. if (!typeof(IMapsDirectlyToDatabaseTable).IsAssignableFrom(type)) { throw new ArgumentException("Type " + type.Name + " passed into method was not an IMapsDirectlyToDatabaseTable"); } //make sure table exists with exact same name as class DiscoveredTable table = tables.SingleOrDefault(t => t.GetRuntimeName().Equals(type.Name)); if (table == null) { notifier.OnCheckPerformed(new CheckEventArgs("Could not find Table called " + type.Name + " (which implements IMapsDirectlyToDatabaseTable)", CheckResult.Fail, null)); return; } notifier.OnCheckPerformed(new CheckEventArgs("Found Table " + type.Name, CheckResult.Success, null)); //get columns from underlying database table DiscoveredColumn[] columns = table.DiscoverColumns(); //get the properties that are not explicitly set as not mapping to database PropertyInfo[] properties = TableRepository.GetPropertyInfos(type); //this is part of the interface and hence doesnt exist in the underlying data table properties = properties.Where(p => !p.Name.Equals("UpdateCommand")).ToArray(); //find columns in database where there are no properties with the same name IEnumerable <DiscoveredColumn> missingProperties = columns.Where(col => !properties.Any(p => p.Name.Equals(col.GetRuntimeName()))); IEnumerable <PropertyInfo> missingDatabaseFields = properties.Where(p => !columns.Any(col => col.GetRuntimeName().Equals(p.Name))); bool problems = false; foreach (DiscoveredColumn missingProperty in missingProperties) { notifier.OnCheckPerformed(new CheckEventArgs( "Missing property " + missingProperty + " on class definition " + type.FullName + ", the underlying table contains this field but the class does not", CheckResult.Fail, null)); problems = true; } foreach (PropertyInfo missingDatabaseField in missingDatabaseFields) { notifier.OnCheckPerformed(new CheckEventArgs( "Missing field in database table " + table + " when compared to class definition " + type.FullName + " property was called " + missingDatabaseField.Name + " and was of type " + missingDatabaseField.PropertyType + ((typeof(Enum).IsAssignableFrom(missingDatabaseField.PropertyType)?"(An Enum)":"")) , CheckResult.Warning, null)); problems = true; } //Check nullability foreach (PropertyInfo nonNullableProperty in properties.Where(property => property.PropertyType.IsEnum || property.PropertyType.IsValueType)) { //it is something like int? i.e. a nullable value type if (Nullable.GetUnderlyingType(nonNullableProperty.PropertyType) != null) { continue; } try { var col = table.DiscoverColumn(nonNullableProperty.Name); if (col.AllowNulls) { notifier.OnCheckPerformed(new CheckEventArgs("Column " + nonNullableProperty.Name + " in table " + table + " allows nulls but is mapped to a ValueType or Enum", CheckResult.Warning, null)); } } catch (Exception e) { notifier.OnCheckPerformed(new CheckEventArgs("Could not check nullability of column " + nonNullableProperty.Name + " in table " + table, CheckResult.Fail, e)); } } if (!problems) { notifier.OnCheckPerformed(new CheckEventArgs("All fields present and correct in Type/Table " + table, CheckResult.Success, null)); } }
public DatabaseFailureFactory(DiscoveredTable table) { _table = table; _tableName = _table.GetFullyQualifiedName(); PrimaryKeys = _table.DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray(); }
public void DumpAllIdentifiersInTable_UnexpectedColumnFoundInIdentifierDumpTable() { var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "surname"); preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump; preDiscardedColumn1.SqlDataType = "varchar(20)"; preDiscardedColumn1.SaveToDatabase(); var preDiscardedColumn2 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename"); preDiscardedColumn2.Destination = DiscardedColumnDestination.StoreInIdentifiersDump; preDiscardedColumn2.SqlDataType = "varchar(50)"; preDiscardedColumn2.SaveToDatabase(); //give it the correct server tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID; tableInfoCreated.SaveToDatabase(); IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated); dumper.Check(new AcceptAllCheckNotifier()); DiscoveredTable tableInDump = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable); Assert.IsTrue(tableInDump.Exists(), "ID table did not exist"); var columnsInDump = tableInDump.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray(); //works and creates table on server Assert.Contains("hic_validFrom", columnsInDump); Assert.Contains("forename", columnsInDump); Assert.Contains("chi", columnsInDump); Assert.Contains("surname", columnsInDump); //now delete it! preDiscardedColumn2.DeleteInDatabase(); //now create a new dumper and watch it go crazy IdentifierDumper dumper2 = new IdentifierDumper(tableInfoCreated); var thrower = new ThrowImmediatelyCheckNotifier(); thrower.ThrowOnWarning = true; try { var ex = Assert.Throws <Exception>(() => dumper2.Check(thrower)); Assert.AreEqual("Column forename was found in the IdentifierDump table ID_BulkData but was not one of the primary keys or a PreLoadDiscardedColumn", ex.Message); } finally { //Drop all this stuff var server = IdentifierDump_Database.Server; using (var con = server.GetConnection()) { con.Open(); //leave the identifier dump in the way we found it (empty) var cmdDrop = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable, con); cmdDrop.ExecuteNonQuery(); var cmdDropArchive = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable + "_Archive", con); cmdDropArchive.ExecuteNonQuery(); } preDiscardedColumn1.DeleteInDatabase(); tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it tableInfoCreated.SaveToDatabase(); } }
public void Dispose(IDataLoadEventListener listener, Exception pipelineFailureExceptionIfAny) { try { if (_managedConnection != null) { //if there was an error if (pipelineFailureExceptionIfAny != null) { _managedConnection.ManagedTransaction.AbandonAndCloseConnection(); listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction rolled back sucessfully")); if (_bulkcopy != null) { _bulkcopy.Dispose(); } } else { _managedConnection.ManagedTransaction.CommitAndCloseConnection(); if (_bulkcopy != null) { _bulkcopy.Dispose(); } listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction committed sucessfully")); } } } catch (Exception e) { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Commit failed on transaction (probably there was a previous error?)", e)); } //if we have a primary key to create if (pipelineFailureExceptionIfAny == null && _primaryKey != null && _primaryKey.Any() && discoveredTable != null && discoveredTable.Exists()) { //Find the columns in the destination var allColumns = discoveredTable.DiscoverColumns(); //if there are not yet any primary keys if (allColumns.All(c => !c.IsPrimaryKey)) { //find the columns the user decorated in his DataTable DiscoveredColumn[] pkColumnsToCreate = allColumns.Where(c => _primaryKey.Any(pk => pk.Equals(c.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase))).ToArray(); //make sure we found all of them if (pkColumnsToCreate.Length != _primaryKey.Count) { throw new Exception("Could not find primary key column(s) " + string.Join(",", _primaryKey) + " in table " + discoveredTable); } //create the primary key to match user provided columns discoveredTable.CreatePrimaryKey(AlterTimeout, pkColumnsToCreate); } } EndAuditIfExists(); }
public virtual string CreateTrigger(ICheckNotifier notifier, int timeout = 30) { if (!_primaryKeys.Any()) { throw new TriggerException("There must be at least 1 primary key"); } //if _Archive exists skip creating it bool skipCreatingArchive = _archiveTable.Exists(); //check _Archive does not already exist foreach (string forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" }) { if (_columns.Any(c => c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase))) { throw new TriggerException("Table " + _table + " already contains a column called " + forbiddenColumnName + " this column is reserved for Archiving"); } } bool b_mustCreate_validFrom = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase)); bool b_mustCreate_dataloadRunId = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) && _createDataLoadRunIdAlso; //forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom) { throw new TriggerException("Cannot create trigger because table contains " + SpecialFieldNames.ValidFrom + " but not " + SpecialFieldNames.DataLoadRunID + " (ID must be placed before valid from in column order)"); } //must add validFrom outside of transaction if we want SMO to pick it up if (b_mustCreate_dataloadRunId) { _table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true, timeout); } var syntaxHelper = _server.GetQuerySyntaxHelper(); var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime))); var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate); //must add validFrom outside of transaction if we want SMO to pick it up if (b_mustCreate_validFrom) { _table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, timeout); } //if we created columns we need to update _column if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom) { _columns = _table.DiscoverColumns(); } string sql = WorkOutArchiveTableCreationSQL(); if (!skipCreatingArchive) { using (var con = _server.GetConnection()) { con.Open(); var cmdCreateArchive = _server.GetCommand(sql, con); cmdCreateArchive.ExecuteNonQuery(); _archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true, timeout); _archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true, timeout); _archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true, timeout); } } return(sql); }
///<inheritdoc/> public void Check(ICheckNotifier notifier) { if (_table.Exists() && _archiveTable.Exists()) { string[] liveCols = _table.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray(); string[] archiveCols = _archiveTable.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray(); var passed = CheckColumnOrderInTablesAndArchiveMatch(liveCols, archiveCols, notifier); if (!passed) { return; } } var factory = new TriggerImplementerFactory(_server.DatabaseType); var implementer = factory.Create(_table); bool present; var primaryKeys = _table.DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray(); //we don't know the primary keys if (!primaryKeys.Any()) { try { //see if it exists present = implementer.GetTriggerStatus() == TriggerStatus.Enabled; } catch (TriggerMissingException) { //clearly it doesnt exist present = false; } } else { try { //we do know the primary keys present = implementer.CheckUpdateTriggerIsEnabledAndHasExpectedBody(); } catch (IrreconcilableColumnDifferencesInArchiveException e) { notifier.OnCheckPerformed( new CheckEventArgs( "Archive table for table " + _table + " is corrupt, see inner Exception for specific errors", CheckResult.Fail, e)); return; } catch (Exception e) { NotifyFail(e, notifier, implementer); return; } } if (present) { notifier.OnCheckPerformed(new CheckEventArgs("Trigger presence/intactness for table " + _table + " matched expected presence", CheckResult.Success, null)); } else { NotifyFail(null, notifier, implementer); //try creating it } }
public string GetCode() { var columns = _table.DiscoverColumns(); if (!columns.Any(c => c.GetRuntimeName().Equals("ID"))) { throw new CodeGenerationException("Table must have an ID automnum column to become an IMapsDirectlyToDatabaseTable class"); } StringBuilder classStart = new StringBuilder(); classStart.Append("public class " + _table.GetRuntimeName() + ": DatabaseEntity"); bool isINamed = columns.Any(c => c.GetRuntimeName() == "Name"); if (isINamed) { classStart.Append(",INamed"); } classStart.AppendLine(); classStart.AppendLine("{"); StringBuilder databaseFields = new StringBuilder(); databaseFields.AppendLine("\t#region Database Properties"); databaseFields.AppendLine(); StringBuilder databaseProperties = new StringBuilder(); StringBuilder constructors = new StringBuilder(); constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository/*, TODO Required Construction Properties For NEW*/)"); constructors.AppendLine(@" { repository.InsertAndHydrate(this,new Dictionary<string, object>() { //TODO Any parameters here as key value pairs }); if (ID == 0 || Repository != repository) throw new ArgumentException(""Repository failed to properly hydrate this class""); }"); constructors.AppendLine("\tpublic " + _table.GetRuntimeName() + "(IRepository repository, DbDataReader r): base(repository, r)"); constructors.AppendLine("\t{"); foreach (var col in columns.Where(c => c.GetRuntimeName() != "ID")) { string setCode; var type = GetCSharpTypeFor(col, out setCode); var propertyName = col.GetRuntimeName(); var fieldString = col.GetRuntimeName(); //cammel case it fieldString = "_" + fieldString.Substring(0, 1).ToLower() + fieldString.Substring(1); databaseFields.AppendLine("\tprivate " + type + " " + fieldString + ";"); databaseProperties.AppendLine("\tpublic " + type + " " + propertyName); databaseProperties.AppendLine("\t{"); databaseProperties.AppendLine("\t\tget { return " + fieldString + ";}"); databaseProperties.AppendLine("\t\tset { SetField(ref " + fieldString + ", value);}"); databaseProperties.AppendLine("\t}"); constructors.AppendLine("\t\t" + propertyName + " = " + setCode); } databaseFields.AppendLine("\t#endregion"); databaseFields.AppendLine(); constructors.AppendLine("\t}"); if (isINamed) { constructors.AppendLine("\t" + @"public override string ToString() { return Name; }"); } return(classStart.ToString() + databaseFields + databaseProperties + constructors + "}"); }
public DataTable ProcessPipelineData(DataTable toProcess, IDataLoadEventListener listener, GracefulCancellationToken cancellationToken) { if (toProcess == null) { return(null); } IDatabaseColumnRequestAdjuster adjuster = null; if (Adjuster != null) { var constructor = new ObjectConstructor(); adjuster = (IDatabaseColumnRequestAdjuster)constructor.Construct(Adjuster); } //work out the table name for the table we are going to create if (TargetTableName == null) { if (string.IsNullOrWhiteSpace(toProcess.TableName)) { throw new Exception("Chunk did not have a TableName, did not know what to call the newly created table"); } TargetTableName = QuerySyntaxHelper.MakeHeaderNameSane(toProcess.TableName); } ClearPrimaryKeyFromDataTableAndExplicitWriteTypes(toProcess); StartAuditIfExists(TargetTableName); if (_loggingDatabaseListener != null) { listener = new ForkDataLoadEventListener(listener, _loggingDatabaseListener); } EnsureTableHasDataInIt(toProcess); bool createdTable = false; if (_firstTime) { bool tableAlreadyExistsButEmpty = false; if (!_database.Exists()) { throw new Exception("Database " + _database + " does not exist"); } discoveredTable = _database.ExpectTable(TargetTableName); //table already exists if (discoveredTable.Exists()) { tableAlreadyExistsButEmpty = true; if (!AllowLoadingPopulatedTables) { if (discoveredTable.IsEmpty()) { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found table " + TargetTableName + " already, normally this would forbid you from loading it (data duplication / no primary key etc) but it is empty so we are happy to load it, it will not be created")); } else { throw new Exception("There is already a table called " + TargetTableName + " at the destination " + _database); } } if (AllowResizingColumnsAtUploadTime) { _dataTypeDictionary = discoveredTable.DiscoverColumns().ToDictionary(k => k.GetRuntimeName(), v => v.GetDataTypeComputer(), StringComparer.CurrentCultureIgnoreCase); } } else { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that the table name " + TargetTableName + " is unique at destination " + _database)); } //create connection to destination if (!tableAlreadyExistsButEmpty) { createdTable = true; if (AllowResizingColumnsAtUploadTime) { _database.CreateTable(out _dataTypeDictionary, TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster); } else { _database.CreateTable(TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster); } listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Created table " + TargetTableName + " successfully.")); } _managedConnection = _server.BeginNewTransactedConnection(); _bulkcopy = discoveredTable.BeginBulkInsert(_managedConnection.ManagedTransaction); if (Culture != null) { _bulkcopy.DateTimeDecider.Culture = Culture; } _firstTime = false; } try { if (AllowResizingColumnsAtUploadTime && !createdTable) { ResizeColumnsIfRequired(toProcess, listener); } //push the data swTimeSpentWritting.Start(); _affectedRows += _bulkcopy.Upload(toProcess); swTimeSpentWritting.Stop(); listener.OnProgress(this, new ProgressEventArgs("Uploading to " + TargetTableName, new ProgressMeasurement(_affectedRows, ProgressType.Records), swTimeSpentWritting.Elapsed)); } catch (Exception e) { _managedConnection.ManagedTransaction.AbandonAndCloseConnection(); if (LoggingServer != null) { _dataLoadInfo.LogFatalError(GetType().Name, ExceptionHelper.ExceptionToListOfInnerMessages(e, true)); } throw new Exception("Failed to write rows (in transaction) to table " + TargetTableName, e); } return(null); }