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