public LiveVsTemplateComparer(ITableInfo table, ImageTableTemplateCollection templateCollection)
{
// locate the live table and script it as it stands today
var discoveredTable = table.Discover(DataAccessContext.InternalDataProcessing);
LiveSql = discoveredTable.ScriptTableCreation(false, false, false);
LiveSql = TailorLiveSql(LiveSql, discoveredTable.Database.Server.DatabaseType);
// The live table name e.g. CT_StudyTable
var liveTableName = discoveredTable.GetRuntimeName();
// Without the prefix e.g. StudyTable
var liveTableNameWithoutPrefix = liveTableName.Substring(liveTableName.IndexOf("_", StringComparison.Ordinal) + 1);
var template = templateCollection.Tables.FirstOrDefault(
c => c.TableName.Equals(liveTableName, StringComparison.CurrentCultureIgnoreCase) ||
c.TableName.Equals(liveTableNameWithoutPrefix, StringComparison.CurrentCultureIgnoreCase));
if (template == null)
{
throw new Exception($"Could not find a Template called '{liveTableName}' or '{liveTableNameWithoutPrefix}'. Templates in file were {string.Join(",",templateCollection.Tables.Select(t=>t.TableName))}");
}
//script the template
var creator = new ImagingTableCreation(discoveredTable.Database.Server.GetQuerySyntaxHelper());
TemplateSql = creator.GetCreateTableSql(discoveredTable.Database, liveTableName, template, discoveredTable.Schema);
TemplateSql = TailorTemplateSql(TemplateSql);
}
public void ExampleTableCreation()
{
var toCreate = new ImageTableTemplate()
{
Columns = new[] {
//pick some tags for the schema
new ImageColumnTemplate(DicomTag.SOPInstanceUID)
{
IsPrimaryKey = true, AllowNulls = false
},
new ImageColumnTemplate(DicomTag.PatientAge)
{
AllowNulls = true
},
new ImageColumnTemplate(DicomTag.PatientBirthDate)
{
AllowNulls = true
}
}
};
//load the Sql Server implementation of FAnsi
ImplementationManager.Load <MicrosoftSQLImplementation>();
//decide where you want to create the table (these methods will actually attempt to connect to the database)
var server = new DiscoveredServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;", FAnsi.DatabaseType.MicrosoftSQLServer);
var db = server.ExpectDatabase("MyDb");
var creator = new ImagingTableCreation(db.Server.GetQuerySyntaxHelper());
var sql = creator.GetCreateTableSql(db, "MyCoolTable", toCreate);
//the following Sql gets created
Assert.AreEqual(
@"CREATE TABLE [MyDb]..[MyCoolTable](
[SOPInstanceUID] varchar(64) NOT NULL ,
[PatientAge] varchar(4) NULL ,
[PatientBirthDate] datetime2 NULL ,
CONSTRAINT PK_MyCoolTable PRIMARY KEY ([SOPInstanceUID]))
"
.Replace("\r", ""), sql.Replace("\r", ""));
//actually do it
//creator.CreateTable(db.ExpectTable("MyCoolTable"));
}
public override void Execute()
{
base.Execute();
var tableCreator = new ImagingTableCreation(_expectedTable.Database.Server.GetQuerySyntaxHelper());
tableCreator.CreateTable(_expectedTable, _tableTemplate);
var importer = new TableInfoImporter(_repositoryLocator.CatalogueRepository, _expectedTable);
TableInfo tis;
ColumnInfo[] cis;
importer.DoImport(out tis, out cis);
var engineer = new ForwardEngineerCatalogue(tis, cis, true);
Catalogue cata;
CatalogueItem[] cataItems;
ExtractionInformation[] eis;
engineer.ExecuteForwardEngineering(out cata, out cataItems, out eis);
var patientIdentifier = eis.SingleOrDefault(e => e.GetRuntimeName().Equals("PatientID"));
if (patientIdentifier != null)
{
patientIdentifier.IsExtractionIdentifier = true;
patientIdentifier.SaveToDatabase();
}
var seriesEi = eis.SingleOrDefault(e => e.GetRuntimeName().Equals("SeriesInstanceUID"));
if (seriesEi != null)
{
seriesEi.IsExtractionIdentifier = true;
seriesEi.SaveToDatabase();
}
//make it extractable
new ExtractableDataSet(_repositoryLocator.DataExportRepository, cata);
NewCatalogueCreated = cata;
}
private static int RunDatabaseTarget(TargetDatabase configDatabase, ProgramOptions opts)
{
var batchSize = Math.Max(1, configDatabase.Batches);
//if we are going into a database we definitely do not need pixels!
if (opts.NoPixels == false)
{
opts.NoPixels = true;
}
Stopwatch swTotal = new Stopwatch();
swTotal.Start();
string neverDistinct = "SOPInstanceUID";
if (!File.Exists(configDatabase.Template))
{
Console.WriteLine($"Listed template file '{configDatabase.Template}' does not exist");
return(-1);
}
ImageTableTemplateCollection template;
try
{
template = ImageTableTemplateCollection.LoadFrom(File.ReadAllText(configDatabase.Template));
}
catch (Exception e)
{
Console.WriteLine($"Error reading yaml from '{configDatabase.Template}'");
Console.WriteLine(e.ToString());
return(-2);
}
ImplementationManager.Load <MySqlImplementation>();
ImplementationManager.Load <PostgreSqlImplementation>();
ImplementationManager.Load <OracleImplementation>();
ImplementationManager.Load <MicrosoftSQLImplementation>();
var server = new DiscoveredServer(configDatabase.ConnectionString, configDatabase.DatabaseType);
try
{
server.TestConnection();
}
catch (Exception e)
{
Console.WriteLine($"Could not reach target server '{server.Name}'");
Console.WriteLine(e);
return(-2);
}
var db = server.ExpectDatabase(configDatabase.DatabaseName);
if (!db.Exists())
{
Console.WriteLine($"Creating Database '{db.GetRuntimeName()}'");
db.Create();
Console.WriteLine("Database Created");
}
else
{
Console.WriteLine($"Found Database '{db.GetRuntimeName()}'");
}
var creator = new ImagingTableCreation(db.Server.GetQuerySyntaxHelper());
Console.WriteLine($"Image template contained schemas for {template.Tables.Count} tables. Looking for existing tables..");
//setting up bulk inserters
DiscoveredTable[] tables = new DiscoveredTable[template.Tables.Count];
DataTable[][] batches = new DataTable[batchSize][];
for (var i = 0; i < batches.Length; i++)
{
batches[i] = new DataTable[template.Tables.Count];
}
IBulkCopy[][] uploaders = new IBulkCopy[batchSize][];
for (int i = 0; i < uploaders.Length; i++)
{
uploaders[i] = new IBulkCopy[template.Tables.Count];
}
string[] pks = new string[template.Tables.Count];
for (var i = 0; i < template.Tables.Count; i++)
{
var tableSchema = template.Tables[i];
var tbl = db.ExpectTable(tableSchema.TableName);
tables[i] = tbl;
if (configDatabase.MakeDistinct)
{
var col = tableSchema.Columns.Where(c => c.IsPrimaryKey).ToArray();
if (col.Length > 1)
{
Console.WriteLine("MakeDistinct only works with single column primary keys e.g. StudyInstanceUID / SeriesInstanceUID");
}
pks[i] = col.SingleOrDefault()?.ColumnName;
if (pks[i] != null)
{
//if it is sop instance uid then we shouldn't be trying to deduplicate
if (string.Equals(pks[i], neverDistinct, StringComparison.CurrentCultureIgnoreCase))
{
pks[i] = null;
}
else
{
//we will make this a primary key later on
col.Single().IsPrimaryKey = false;
Console.WriteLine($"MakeDistinct will apply to '{pks[i]}' on '{tbl.GetFullyQualifiedName()}'");
}
}
}
bool create = true;
if (tbl.Exists())
{
if (configDatabase.DropTables)
{
Console.WriteLine($"Dropping existing table '{tbl.GetFullyQualifiedName()}'");
tbl.Drop();
}
else
{
Console.WriteLine($"Table '{tbl.GetFullyQualifiedName()}' already existed (so will not be created)");
create = false;
}
}
if (create)
{
Console.WriteLine($"About to create '{tbl.GetFullyQualifiedName()}'");
creator.CreateTable(tbl, tableSchema);
Console.WriteLine($"Successfully created create '{tbl.GetFullyQualifiedName()}'");
}
Console.WriteLine($"Creating uploader for '{tbl.GetRuntimeName()}''");
for (int j = 0; j < batchSize; j++)
{
//fetch schema
var dt = tbl.GetDataTable();
dt.Rows.Clear();
batches[j][i] = dt;
uploaders[j][i] = tbl.BeginBulkInsert();
}
}
var tasks = new Task[batchSize];
IPersonCollection identifiers = GetPeople(opts, out Random r);
for (int i = 0; i < batchSize; i++)
{
var batch = i;
tasks[i] = new Task(() => // lgtm[cs/local-not-disposed]
{
RunBatch(identifiers, opts, r, batches[batch], uploaders[batch]);
});
tasks[i].Start();
}
Task.WaitAll(tasks);
swTotal.Stop();
for (var i = 0; i < tables.Length; i++)
{
if (pks[i] == null)
{
continue;
}
Console.WriteLine($"{DateTime.Now} Making table '{tables[i]}' distinct (this may take a long time)");
var tbl = tables[i];
tbl.MakeDistinct(500000000);
Console.WriteLine($"{DateTime.Now} Creating primary key on '{tables[i]}' of '{pks[i]}'");
tbl.CreatePrimaryKey(500000000, tbl.DiscoverColumn(pks[i]));
}
Console.WriteLine("Final Row Counts:");
foreach (DiscoveredTable t in tables)
{
Console.WriteLine($"{t.GetFullyQualifiedName()}: {t.GetRowCount():0,0}");
}
Console.WriteLine("Total Running Time:" + swTotal.Elapsed);
return(0);
}
