private void CreateTableIfNotExists() { try { //create the database if it doesn't exist if (!_table.Database.Exists()) { _table.Database.Create(); } //create the table if it doesn't exist if (!_table.Exists()) { _logger.Info("Guid mapping table does not exist, creating it now"); _table.Database.CreateTable(_table.GetRuntimeName(), new[] { new DatabaseColumnRequest(_options.SwapColumnName, new DatabaseTypeRequest(typeof(string), 10), false) { IsPrimaryKey = true }, new DatabaseColumnRequest(_options.ReplacementColumnName, new DatabaseTypeRequest(typeof(string), 255), false) } ); } if (_table.Exists()) { _logger.Info("Guid mapping table exist (" + _table + ")"); } else { throw new Exception("Table creation did not result in table existing!"); } _logger.Info("Checking for column " + _options.SwapColumnName); _swapColumnLength = _table.DiscoverColumn(_options.SwapColumnName).DataType.GetLengthIfString(); _logger.Info("Checking for column " + _options.ReplacementColumnName); _table.DiscoverColumn(_options.ReplacementColumnName); } catch (Exception e) { var sb = new StringBuilder(); if (_table != null) { sb.AppendLine("Server:" + _table.Database.Server.Name); sb.AppendLine("Database:" + _table.Database.GetRuntimeName()); sb.AppendLine("Username:"******"Table:" + _table.GetFullyQualifiedName()); } throw new Exception("Error creating/checking Guid substitution table on:" + Environment.NewLine + sb, e); } }
public void CreateWithPks_Valid(DatabaseType dbType) { CreateTable(dbType); _table.CreatePrimaryKey(new [] { _table.DiscoverColumn("name") }); GetImplementer().CreateTrigger(new ThrowImmediatelyCheckNotifier()); Assert.AreEqual(TriggerStatus.Enabled, GetImplementer().GetTriggerStatus()); Assert.AreEqual(true, GetImplementer().CheckUpdateTriggerIsEnabledAndHasExpectedBody()); }
private void DropColumnFromTable(DiscoveredTable table, string columnName, IDataLoadEventListener listener) { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, string.Format("Dropping column '{0}' from table '{1}'", columnName, table.GetFullyQualifiedName()))); var col = table.DiscoverColumn(columnName); table.DropColumn(col); }
public override void CommitTableDataCompleted(DiscoveredTable resultingTable) { //if user has an explicit type to use for the column (probably a good idea to have all extraction idetntifiers of the same data type var col = resultingTable.DiscoverColumn(_identifierColumn.ColumnName); CreateIndex(resultingTable, col, Configuration.ToString()); }
/// <inheritdoc/> public string GetReleaseIdentifierDataType() { DiscoveredTable table = ExternalCohortTable.Discover().ExpectTable(ExternalCohortTable.TableName); //discover the column return(table.DiscoverColumn(GetReleaseIdentifier(true)) .DataType.SQLType); //and return it's datatype }
private void CheckStagingToLiveMigrationForTable(DiscoveredTable stagingTable, DiscoveredColumn[] stagingCols, DiscoveredTable liveTable, DiscoveredColumn[] liveCols, ICheckNotifier notifier) { try { new MigrationColumnSet(stagingTable, liveTable, new StagingToLiveMigrationFieldProcessor() { NoBackupTrigger = _loadMetadata.IgnoreTrigger }); notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + liveTable + " passed " + typeof(MigrationColumnSet).Name + " check ", CheckResult.Success, null)); } catch (Exception e) { notifier.OnCheckPerformed(new CheckEventArgs( typeof(MigrationColumnSet).Name + " reports a problem with the configuration of columns on STAGING/LIVE or in the ColumnInfos for TableInfo " + liveTable, CheckResult.Fail, e)); } //live columns foreach (DiscoveredColumn col in liveCols) { if (!SpecialFieldNames.IsHicPrefixed(col) && col.IsAutoIncrement) //must start hic_ if they are identities { notifier.OnCheckPerformed(new CheckEventArgs("Column " + col + " is an identity column in the LIVE database but does not start with hic_", CheckResult.Fail, null)); //this one does not } } //staging columns foreach (DiscoveredColumn col in stagingCols) //staging columns { if (col.IsAutoIncrement) //if there are any auto increments { notifier.OnCheckPerformed(new CheckEventArgs( "Column " + col + " is an identity column and is in STAGING, the identity flag must be removed from the STAGING table", CheckResult.Fail, null));//complain since don't want a mismatch between IDs in staging and live or complaints about identity insert from SQL server } } //staging must allow null dataloadrunids and validfroms ConfirmNullability(stagingTable.DiscoverColumn(SpecialFieldNames.DataLoadRunID), true, notifier); ConfirmNullability(stagingTable.DiscoverColumn(SpecialFieldNames.ValidFrom), true, notifier); //live must allow nulls in validFrom ConfirmNullability(liveTable.DiscoverColumn(SpecialFieldNames.ValidFrom), true, notifier); }
public void Test_OracleBit_IsActuallyString() { DiscoveredDatabase db = GetTestDatabase(DatabaseType.Oracle); DiscoveredTable table = db.CreateTable("MyTable", new[] { new DatabaseColumnRequest("MyCol", new DatabaseTypeRequest(typeof(bool))) }); var col = table.DiscoverColumn("MyCol"); Assert.AreEqual("varchar2(5)", col.DataType.SQLType); Assert.AreEqual(5, col.DataType.GetLengthIfString()); }
public void CreateTable_PrimaryKey_FromDataTable(DatabaseType databaseType) { DiscoveredDatabase database = GetTestDatabase(databaseType); var dt = new DataTable(); dt.Columns.Add("Name"); dt.PrimaryKey = new[] { dt.Columns[0] }; dt.Rows.Add("Frank"); DiscoveredTable table = database.CreateTable("PkTable", dt); Assert.IsTrue(table.DiscoverColumn("Name").IsPrimaryKey); }
/// <summary> /// Generates and runs an SQL command on <paramref name="t"/> /// </summary> /// <param name="t"></param> /// <param name="message"></param> protected virtual int UpdateTable(DiscoveredTable t, UpdateValuesMessage message) { var audit = _audits.GetOrAdd(t, (k) => new UpdateTableAudit(k)); StringBuilder builder = new StringBuilder(); builder.AppendLine("UPDATE "); builder.AppendLine(t.GetFullyQualifiedName()); builder.AppendLine(" SET "); for (int i = 0; i < message.WriteIntoFields.Length; i++) { var col = t.DiscoverColumn(message.WriteIntoFields[i]); builder.Append(GetFieldEqualsValueExpression(col, message.Values[i], "=")); //if there are more SET fields to come if (i < message.WriteIntoFields.Length - 1) { builder.AppendLine(","); } } builder.AppendLine(" WHERE "); for (int i = 0; i < message.WhereFields.Length; i++) { var col = t.DiscoverColumn(message.WhereFields[i]); builder.Append(GetFieldEqualsValueExpression(col, message.HaveValues[i], message?.Operators?[i])); //if there are more WHERE fields to come if (i < message.WhereFields.Length - 1) { builder.AppendLine(" AND "); } } var sql = builder.ToString(); int affectedRows = 0; audit.StartOne(); try { using (var con = t.Database.Server.GetConnection()) { con.Open(); var cmd = t.Database.Server.GetCommand(sql, con); cmd.CommandTimeout = UpdateTimeout; try { return(affectedRows = cmd.ExecuteNonQuery()); } catch (Exception ex) { throw new Exception($"Failed to excute query {sql} ", ex); } } } finally { audit.EndOne(affectedRows < 0 ? 0 : affectedRows); } }
/// <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 void SetUpDle() { var rootFolder = new DirectoryInfo(TestContext.CurrentContext.TestDirectory); var subdir = rootFolder.CreateSubdirectory("TestsRequiringADle"); LoadDirectory = LoadDirectory.CreateDirectoryStructure(rootFolder, subdir.FullName, true); LiveTable = CreateDataset <Demography>(500, 5000, new Random(190)); LiveTable.CreatePrimaryKey(new DiscoveredColumn[] { LiveTable.DiscoverColumn("chi"), LiveTable.DiscoverColumn("dtCreated"), LiveTable.DiscoverColumn("hb_extract") }); TestCatalogue = Import(LiveTable); RowsBefore = 5000; TestLoadMetadata = new LoadMetadata(CatalogueRepository, "Loading Test Catalogue"); TestLoadMetadata.LocationOfFlatFiles = LoadDirectory.RootPath.FullName; TestLoadMetadata.SaveToDatabase(); //make the load load the table TestCatalogue.LoadMetadata_ID = TestLoadMetadata.ID; TestCatalogue.SaveToDatabase(); var csvProcessTask = new ProcessTask(CatalogueRepository, TestLoadMetadata, LoadStage.Mounting); var args = csvProcessTask.CreateArgumentsForClassIfNotExists <AnySeparatorFileAttacher>(); csvProcessTask.Path = typeof(AnySeparatorFileAttacher).FullName; csvProcessTask.ProcessTaskType = ProcessTaskType.Attacher; csvProcessTask.SaveToDatabase(); var filePattern = args.Single(a => a.Name == "FilePattern"); filePattern.SetValue("*.csv"); filePattern.SaveToDatabase(); var tableToLoad = args.Single(a => a.Name == "TableToLoad"); tableToLoad.SetValue(TestCatalogue.GetTableInfoList(false).Single()); tableToLoad.SaveToDatabase(); var separator = args.Single(a => a.Name == "Separator"); separator.SetValue(","); separator.SaveToDatabase(); var ignoreDataLoadRunIDCol = args.Single(a => a.Name == "IgnoreColumns"); ignoreDataLoadRunIDCol.SetValue("hic_dataLoadRunID"); ignoreDataLoadRunIDCol.SaveToDatabase(); //Get DleRunner to run pre load checks (includes trigger creation etc) var runner = new DleRunner(new DleOptions() { LoadMetadata = TestLoadMetadata.ID, Command = CommandLineActivity.check }); runner.Run(RepositoryLocator, new ThrowImmediatelyDataLoadEventListener(), new AcceptAllCheckNotifier(), new GracefulCancellationToken()); }
private DiscoveredColumn Discover(DiscoveredTable tbl, string column) { return(tbl.DiscoverColumn(tbl.Database.Server.GetQuerySyntaxHelper().GetRuntimeName(column))); }