public void SetUp(DiscoveredServer server)
{
_server = server;
var databaseToLoadName = "HICPipelineTests";
// Create the databases
server.ExpectDatabase(databaseToLoadName).Create(true);
server.ChangeDatabase(databaseToLoadName);
// Create the dataset table
DatabaseToLoad = server.ExpectDatabase(databaseToLoadName);
using (var con = DatabaseToLoad.Server.GetConnection())
{
con.Open();
const string createDatasetTableQuery =
"CREATE TABLE TestData ([Col1] [int], [hic_dataLoadRunID] [int] NULL, [hic_validFrom] [datetime] NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([Col1] ASC))";
const string addValidFromDefault =
"ALTER TABLE TestData ADD CONSTRAINT [DF_TestData__hic_validFrom] DEFAULT (getdate()) FOR [hic_validFrom]";
using (var cmd = DatabaseCommandHelper.GetCommand(createDatasetTableQuery, con))
cmd.ExecuteNonQuery();
using (var cmd = DatabaseCommandHelper.GetCommand(addValidFromDefault, con))
cmd.ExecuteNonQuery();
}
// Ensure the dataset table has been created
var datasetTable = DatabaseToLoad.ExpectTable("TestData");
Assert.IsTrue(datasetTable.Exists());
}
public override void Execute()
{
var sbScript = new StringBuilder();
foreach (var tableInfo in _tableInfos)
{
var tbl = tableInfo.Discover(DataAccessContext.InternalDataProcessing);
var hypotheticalServer = new DiscoveredServer("localhost", _dbName ?? "None", _dbType ?? tableInfo.DatabaseType, null, null);
var hypotheticalTable = hypotheticalServer.ExpectDatabase(_dbName ?? tbl.Database.GetRuntimeName()).ExpectTable(tbl.GetRuntimeName());
var result = tbl.ScriptTableCreation(false, false, false, hypotheticalTable);
sbScript.AppendLine(result);
sbScript.AppendLine();
}
if (_outFile != null)
{
File.WriteAllText(_outFile.FullName, sbScript.ToString());
}
else
{
Show($"Script for {_tableInfos.Length} tables", sbScript.ToString());
}
}
public void TestGetDataTable()
{
//create an Fo-Dicom dataset
var ds = new DicomDataset(new List <DicomItem>()
{
new DicomShortString(DicomTag.PatientName, "Frank"),
new DicomAgeString(DicomTag.PatientAge, "032Y"),
new DicomDate(DicomTag.PatientBirthDate, new DateTime(2001, 1, 1))
});
var dt = new DataTable();
var row = ds.ToRow(dt);
Assert.AreEqual("Frank", row["PatientName"]);
Assert.AreEqual("032Y", row["PatientAge"]);
Assert.AreEqual(new DateTime(2001, 1, 1), row["PatientBirthDate"]);
//load the MySql implementation of FAnsi
ImplementationManager.Load <MySqlImplementation>();
//pick the location of the destination table (must exist, see ExampleTableCreation for how to create)
var server = new DiscoveredServer(new MySqlConnectionStringBuilder("Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"));
var table = server.ExpectDatabase("MyDb").ExpectTable("MyCoolTable");
//using (IBulkCopy bulkInsert = table.BeginBulkInsert())
//{
// bulkInsert.Upload(dt);
//}
}
public override CommandLineObjectPickerArgumentValue Parse(string arg, int idx)
{
var m = MatchOrThrow(arg, idx);
var dbType = (DatabaseType)Enum.Parse(typeof(DatabaseType), m.Groups[1].Value, true);
var dbName = Trim("Name:", m.Groups[2].Value);
var connectionString = m.Groups[3].Value;
var server = new DiscoveredServer(connectionString, dbType);
DiscoveredDatabase db;
if (string.IsNullOrWhiteSpace(dbName))
{
db = server.GetCurrentDatabase();
}
else
{
db = server.ExpectDatabase(dbName);
}
if (db == null)
{
throw new CommandLineObjectPickerParseException("Missing database name parameter, it was not in connection string or specified explicitly", idx, arg);
}
return(new CommandLineObjectPickerArgumentValue(arg, idx, db));
}
public override CommandLineObjectPickerArgumentValue Parse(string arg, int idx)
{
var m = MatchOrThrow(arg, idx);
var tableName = m.Groups[1].Value;
var schema = Trim("Schema:", m.Groups[2].Value);
string isViewStr = Trim("IsView:", m.Groups[3].Value);
bool isViewBool = isViewStr == null ? false : bool.Parse(isViewStr);
var dbType = (DatabaseType)Enum.Parse(typeof(DatabaseType), m.Groups[4].Value);
var dbName = m.Groups[5].Value;
var connectionString = m.Groups[6].Value;
var server = new DiscoveredServer(connectionString, dbType);
DiscoveredDatabase db;
if (string.IsNullOrWhiteSpace(dbName))
{
db = server.GetCurrentDatabase();
}
else
{
db = server.ExpectDatabase(dbName);
}
if (db == null)
{
throw new CommandLineObjectPickerParseException("Missing database name parameter, it was not in connection string or specified explicitly", idx, arg);
}
return(new CommandLineObjectPickerArgumentValue(arg, idx, db.ExpectTable(tableName, schema, isViewBool ? TableType.View:TableType.Table)));
}
public void EqualityTest_DiscoveredTable_AreEqual(string table1, string schema1, string table2, string schema2)
{
var s = new DiscoveredServer("Server=fish", DatabaseType.MicrosoftSQLServer);
var db = s.ExpectDatabase("MyDb");
var db2 = s.ExpectDatabase("MyDb");
Assert.IsFalse(ReferenceEquals(db, db2));
Assert.IsTrue(db.Equals(db2));
var t1 = db.ExpectTable(table1, schema1);
var t2 = db2.ExpectTable(table2, schema2);
Assert.AreEqual(t1, t2);
Assert.AreEqual(t1.GetHashCode(), t2.GetHashCode());
}
public void Test_ExpectDatabase(DatabaseType type, bool upperCase)
{
var helper = ImplementationManager.GetImplementation(type).GetServerHelper();
var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "db", "frank", "kangaro"));
var db = server.ExpectDatabase("omg");
Assert.AreEqual(upperCase?"OMG":"omg", db.GetRuntimeName());
}
public void EqualityTest_DiscoveredServer_AreNotEqual(string constr1, DatabaseType type1, string constr2, DatabaseType type2)
{
var s1 = new DiscoveredServer(constr1, type1);
var s2 = new DiscoveredServer(constr2, type2);
Assert.AreNotEqual(s1, s2);
Assert.AreNotEqual(s1.ExpectDatabase("MyDb"), s2.ExpectDatabase("MyDb"));
}
public void EqualityTest_DiscoveredServer_AreEqual(string constr1, DatabaseType type1, string constr2, DatabaseType type2)
{
ImplementationManager.Load(
typeof(MicrosoftSQLImplementation).Assembly,
typeof(OracleImplementation).Assembly,
typeof(MySqlImplementation).Assembly
);
var s1 = new DiscoveredServer(constr1, type1);
var s2 = new DiscoveredServer(constr2, type2);
Assert.AreEqual(s1, s2);
Assert.AreEqual(s1.GetHashCode(), s2.GetHashCode());
Assert.AreEqual(s1.ExpectDatabase("MyDb"), s2.ExpectDatabase("MyDb"));
Assert.AreEqual(s1.ExpectDatabase("MyDb").GetHashCode(), s2.ExpectDatabase("MyDb").GetHashCode());
Assert.AreEqual(s1.ExpectDatabase("Mydb"), s2.ExpectDatabase("MyDb"));
Assert.AreEqual(s1.ExpectDatabase("Mydb").GetHashCode(), s2.ExpectDatabase("MyDb").GetHashCode());
Assert.AreNotEqual(s1.ExpectDatabase("MyDb"), s2.ExpectDatabase("MyDb2"));
//This does not affect things since we are expecting a specific database anyway
s1.ChangeDatabase("Dave");
Assert.AreNotEqual(s1, s2);
Assert.AreEqual(s1.ExpectDatabase("MyDb"), s2.ExpectDatabase("MyDb"));
Assert.AreEqual(s1.ExpectDatabase("MyDb").GetHashCode(), s2.ExpectDatabase("MyDb").GetHashCode());
}
/// <summary>
/// Gets an empty database on the test server of the appropriate DBMS
/// </summary>
/// <param name="type">The DBMS you want a server of (a valid connection string must exist in TestDatabases.txt)</param>
/// <param name="dbnName">null for default test database name (recommended unless you are testing moving data from one database to another on the same test server)</param>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="justDropTablesIfPossible">Determines behaviour when the test database already exists. False to drop and recreate it. True to just drop tables (faster)</param>
/// <returns></returns>
protected DiscoveredDatabase GetCleanedServer(DatabaseType type, string dbnName, out DiscoveredServer server, out DiscoveredDatabase database, bool justDropTablesIfPossible = false)
{
switch (type)
{
case DatabaseType.MicrosoftSQLServer:
server = new DiscoveredServer(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Builder);
break;
case DatabaseType.MySql:
server = _discoveredMySqlServer == null ? null : new DiscoveredServer(_discoveredMySqlServer.Builder);
break;
case DatabaseType.Oracle:
server = _discoveredOracleServer == null ? null : new DiscoveredServer(_discoveredOracleServer.Builder);
break;
default:
throw new ArgumentOutOfRangeException("type");
}
if (server == null)
{
Assert.Inconclusive();
}
//the microsoft one should exist! others are optional
if (!server.Exists() && type != DatabaseType.MicrosoftSQLServer)
{
Assert.Inconclusive();
}
server.TestConnection();
database = server.ExpectDatabase(dbnName);
if (justDropTablesIfPossible && database.Exists())
{
foreach (var t in database.DiscoverTables(true))
{
t.Drop();
}
foreach (var t in database.DiscoverTableValuedFunctions())
{
t.Drop();
}
}
else
{
database.Create(true);
}
server.ChangeDatabase(dbnName);
Assert.IsTrue(database.Exists());
return(database);
}
public void WorkedExampleTest()
{
//pick some tags that we are interested in (determines the table schema created)
var toCreate = new ImageTableTemplate()
{
Columns = new[] {
new ImageColumnTemplate(DicomTag.SOPInstanceUID),
new ImageColumnTemplate(DicomTag.Modality)
{
AllowNulls = true
},
new ImageColumnTemplate(DicomTag.PatientID)
{
AllowNulls = true
}
}
};
//load the Sql Server implementation of FAnsi
ImplementationManager.Load <MicrosoftSQLImplementation>();
//decide where you want to create the table
var server = new DiscoveredServer(@"Server=localhost\sqlexpress;Database=mydb;Integrated Security=true;", FAnsi.DatabaseType.MicrosoftSQLServer);
var db = server.ExpectDatabase("test");
//create the table
var tbl = db.CreateTable("MyCoolTable", toCreate.GetColumns(FAnsi.DatabaseType.MicrosoftSQLServer));
//add a column for where the image is on disk
tbl.AddColumn("FileLocation", new DatabaseTypeRequest(typeof(string), 500), true, 500);
//Create a DataTable in memory for the data we read from disk
DataTable dt = new DataTable();
dt.Columns.Add("SOPInstanceUID");
dt.Columns.Add("Modality");
dt.Columns.Add("PatientID");
dt.Columns.Add("FileLocation");
//Load some dicom files and copy tag data into DataTable (where tag exists)
foreach (string file in Directory.EnumerateFiles(@"C:\temp\TestDicomFiles", "*.dcm", SearchOption.AllDirectories))
{
var dcm = DicomFile.Open(file);
var ds = dcm.Dataset;
dt.Rows.Add(
DicomTypeTranslaterReader.GetCSharpValue(dcm.Dataset, DicomTag.SOPInstanceUID),
ds.Contains(DicomTag.Modality) ? DicomTypeTranslaterReader.GetCSharpValue(dcm.Dataset, DicomTag.Modality) : DBNull.Value,
ds.Contains(DicomTag.PatientID) ? DicomTypeTranslaterReader.GetCSharpValue(dcm.Dataset, DicomTag.PatientID) : DBNull.Value,
file);
}
//put the DataTable into the database
using (var insert = tbl.BeginBulkInsert())
insert.Upload(dt);
}
/// <inheritdoc/>
public void DoImport(out TableInfo tableInfoCreated, out ColumnInfo[] columnInfosCreated)
{
string tableName;
string databaseName;
var querySyntaxHelper = _server.GetQuerySyntaxHelper();
tableName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);
if (_type == DatabaseType.MicrosoftSQLServer || _type == DatabaseType.PostgreSql)
{
tableName += "." + (_importFromSchema ?? querySyntaxHelper.GetDefaultSchemaIfAny()) + ".";
}
else if (_type == DatabaseType.MySql || _type == DatabaseType.Oracle)
{
tableName += ".";
}
else
{
throw new NotSupportedException("Unknown Type:" + _type);
}
tableName += querySyntaxHelper.EnsureWrapped(_importTableName);
databaseName = querySyntaxHelper.EnsureWrapped(_importDatabaseName);
DiscoveredColumn[] discoveredColumns = _server.ExpectDatabase(_importDatabaseName)
.ExpectTable(_importTableName, _importFromSchema, _importTableType)
.DiscoverColumns();
TableInfo parent = new TableInfo(_repository, tableName)
{
DatabaseType = _type,
Database = databaseName,
Server = _importFromServer,
Schema = _importFromSchema,
IsView = _importTableType == TableType.View
};
parent.SaveToDatabase();
List <ColumnInfo> newCols = new List <ColumnInfo>();
foreach (DiscoveredColumn discoveredColumn in discoveredColumns)
{
newCols.Add(CreateNewColumnInfo(parent, discoveredColumn));
}
tableInfoCreated = parent;
columnInfosCreated = newCols.ToArray();
//if there is a username then we need to associate it with the TableInfo we just created
if (!string.IsNullOrWhiteSpace(_username))
{
DataAccessCredentialsFactory credentialsFactory = new DataAccessCredentialsFactory(_repository);
credentialsFactory.Create(tableInfoCreated, _username, _password, _usageContext);
}
}
public override void LoadExtraText(string s)
{
_arguments = Helper.LoadDictionaryFromString(s);
DatabaseType = (DatabaseType)Enum.Parse(typeof(DatabaseType), _arguments[DatabaseTypeKey]);
var server = new DiscoveredServer(Server, Database, DatabaseType, null, null);
_table = server.ExpectDatabase(Database).ExpectTable(_arguments[TableKey]);
}
private void SetServer()
{
if (_server == null)
{
//it's a legit dataset being extracted?
_server = Request.Catalogue.GetDistinctLiveDatabaseServer(DataAccessContext.DataExport, false);
//expect a database called called tempdb
_tempDb = _server.ExpectDatabase(TemporaryDatabaseName);
}
}
/// <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();
}
}
}
//Constructor
internal StandardDatabaseHelper(DiscoveredDatabase liveDatabase, INameDatabasesAndTablesDuringLoads namer, DiscoveredServer rawServer)
{
DatabaseNamer = namer;
foreach (LoadBubble stage in new[] { LoadBubble.Raw, LoadBubble.Staging, LoadBubble.Live, })
{
var stageName = DatabaseNamer.GetDatabaseName(liveDatabase.GetRuntimeName(), stage);
DatabaseInfoList.Add(stage, stage == LoadBubble.Raw ? rawServer.ExpectDatabase(stageName) : liveDatabase.Server.ExpectDatabase(stageName));
}
}
public void Example_TableCreation()
{
//Load implementation assemblies that are relevant to your application
ImplementationManager.Load(
typeof(FAnsi.Implementations.MicrosoftSQL.MicrosoftSQLImplementation).Assembly,
typeof(FAnsi.Implementations.Oracle.OracleImplementation).Assembly,
typeof(FAnsi.Implementations.MySql.MySqlImplementation).Assembly);
//Create some test data
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add("Frank", "2001-01-01");
dt.Rows.Add("Dave", "2001-01-01");
//Create a server object
//var server = new DiscoveredServer(@"server=localhost\sqlexpress;Trusted_Connection=True;", DatabaseType.MicrosoftSQLServer);
var server = new DiscoveredServer(@"Server=localhost;Uid=root;Pwd=zombie;SSL-Mode=None", DatabaseType.MySql);
//Find the database
var database = server.ExpectDatabase("FAnsiTests");
//Or create it
if (!database.Exists())
{
database.Create();
}
//Create a table that can store the data in dt
var table = database.CreateTable("MyTable", dt);
//Table has 2 rows in it
Console.WriteLine("Table {0} has {1} rows", table.GetFullyQualifiedName(), table.GetRowCount());
Console.WriteLine("Column Name is of type {0}", table.DiscoverColumn("Name").DataType.SQLType);
Console.WriteLine("Column DateOfBirth is of type {0}", table.DiscoverColumn("DateOfBirth").DataType.SQLType);
using (DbConnection con = server.GetConnection())
{
con.Open();
DbCommand cmd = server.GetCommand("Select * from " + table.GetFullyQualifiedName(), con);
DbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
Console.WriteLine(string.Join(",", r["Name"], r["DateOfBirth"]));
}
}
//Drop the table afterwards
table.Drop();
}
public void EqualityTest_DiscoveredTable_AreNotEqual(DatabaseType type1, string constr1, string dbname1, string tablename1, string schema1, DatabaseType type2, string constr2, string dbname2, string tablename2, string schema2)
{
var s1 = new DiscoveredServer(constr1, type1);
var s2 = new DiscoveredServer(constr2, type2);
var db1 = s1.ExpectDatabase(dbname1);
var db2 = s2.ExpectDatabase(dbname2);
var t1 = db1.ExpectTable(tablename1, schema1);
var t2 = db2.ExpectTable(tablename2, schema2);
Assert.AreNotEqual(t1, t2);
}
private void CreateScratchArea()
{
var scratchDatabaseName = TestDatabaseNames.GetConsistentName("ScratchArea");
DiscoveredDatabaseICanCreateRandomTablesIn = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(scratchDatabaseName);
//if it already exists drop it
if (DiscoveredDatabaseICanCreateRandomTablesIn.Exists())
{
DiscoveredDatabaseICanCreateRandomTablesIn.Drop();
}
//create it
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.CreateDatabase(scratchDatabaseName);
}
public void CreateStaging(DiscoveredServer liveServer)
{
_stagingDatabase = liveServer.ExpectDatabase(GetDatabaseName(null, LoadBubble.Staging));
if (!_stagingDatabase.Exists())
{
_stagingDatabase.Create();
}
//get rid of any old data from previous load
foreach (var t in _stagingDatabase.DiscoverTables(false))
{
t.Truncate();
}
}
private void SetServer()
{
if (_server == null && Request != null)
{
//it's a legit dataset being extracted?
_server = Request.GetDistinctLiveDatabaseServer();
//expect a database called called tempdb
_tempDb = _server.ExpectDatabase(TemporaryDatabaseName);
var cohortServer = Request.ExtractableCohort.ExternalCohortTable.Discover();
if (AreOnSameServer(_server, cohortServer.Server))
{
_doNotMigrate = true;
}
}
}
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 void Dispose()
{
if (DatabaseToLoad == null)
{
return;
}
if (DatabaseToLoad.Exists())
{
DatabaseToLoad.Drop();
}
// check if RAW has been created and remove it
var raw = _server.ExpectDatabase(DatabaseToLoad.GetRuntimeName() + "_RAW");
if (raw.Exists())
{
raw.Drop();
}
}
public DiscoveredTable Discover()
{
var server = new DiscoveredServer(MappingConnectionString, MappingDatabaseType);
var idx = MappingTableName.LastIndexOf('.');
var tableNameUnqualified = MappingTableName.Substring(idx + 1);
idx = MappingTableName.IndexOf('.');
if (idx == -1)
{
throw new ArgumentException($"MappingTableName did not contain the database/user section:'{MappingTableName}'");
}
var databaseName = server.GetQuerySyntaxHelper().GetRuntimeName(MappingTableName.Substring(0, idx));
if (string.IsNullOrWhiteSpace(databaseName))
{
throw new ArgumentException($"Could not get database/username from MappingTableName {MappingTableName}");
}
return(server.ExpectDatabase(databaseName).ExpectTable(tableNameUnqualified));
}
public void Simple_Example()
{
//Some data we want to load
var dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Date of Birth");
dt.Rows.Add("Frank \"The Boss\" Spagetti", "1920-01-01");
dt.Rows.Add("Pete Mudarillo", "22-May-1910");
//Load the DBMS implementation(s) you need
ImplementationManager.Load <MicrosoftSQLImplementation>();
//Get Management object for the database
var server = new DiscoveredServer(
@"server=localhost\sqlexpress;Trusted_Connection=True;", DatabaseType.MicrosoftSQLServer);
var database = server.ExpectDatabase("test");
var table = database.ExpectTable("MyTable");
//Throw out whatever was there before
if (table.Exists())
{
table.Drop();
}
//Create the table
database.CreateTable("MyTable", dt);
//Database types are compatible with all the data
Assert.AreEqual("datetime2", table.DiscoverColumn("Date of Birth").DataType.SQLType);
Assert.AreEqual("varchar(25)", table.DiscoverColumn("Name").DataType.SQLType);
//And the (string) data is now properly typed and sat in our DBMS
Assert.AreEqual(2, table.GetRowCount());
Assert.AreEqual(new DateTime(1920, 1, 1), table.GetDataTable().Rows[0][1]);
Assert.AreEqual(new DateTime(1910, 5, 22), table.GetDataTable().Rows[1][1]);
}
public MappingRepository(DiscoveredServer server, string databaseName)
{
_server = server;
_database = _server.ExpectDatabase(databaseName);
}
/// <summary>
///
/// </summary>
/// <exception cref="SynchronizationFailedException">Could not figure out how to resolve a synchronization problem between the TableInfo and the underlying table structure</exception>
/// <param name="notifier">Called every time a fixable problem is detected, method must return true or false. True = apply fix, False = don't - but carry on checking</param>
public bool Synchronize(ICheckNotifier notifier)
{
bool IsSynched = true;
//server exists and is accessible?
try
{
_toSyncTo.TestConnection();
}
catch (Exception e)
{
throw new SynchronizationFailedException("Could not connect to " + _toSyncTo, e);
}
//database exists?
var expectedDatabase = _toSyncTo.ExpectDatabase(_tableToSync.GetDatabaseRuntimeName());
if (!expectedDatabase.Exists())
{
throw new SynchronizationFailedException("Server did not contain a database called " + _tableToSync.GetDatabaseRuntimeName());
}
//identify new columns
DiscoveredColumn[] liveColumns;
DiscoveredTable expectedTable;
if (_tableToSync.IsTableValuedFunction)
{
expectedTable = expectedDatabase.ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException("Database " + expectedDatabase + " did not contain a TABLE VALUED FUNCTION called " + _tableToSync.GetRuntimeName());
}
}
else
{
//table exists?
expectedTable = expectedDatabase.ExpectTable(_tableToSync.GetRuntimeName(), _tableToSync.Schema, _tableToSync.IsView ? TableType.View:TableType.Table);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException("Database " + expectedDatabase + " did not contain a table called " + _tableToSync.GetRuntimeName());
}
}
try
{
liveColumns = expectedTable.DiscoverColumns();
}
catch (SqlException e)
{
throw new Exception("Failed to enumerate columns in " +
_toSyncTo +
" (we were attempting to synchronize the TableInfo " + _tableToSync + " (ID=" + _tableToSync.ID + "). Check the inner exception for specifics", e);
}
ColumnInfo[] catalogueColumns = _tableToSync.ColumnInfos.ToArray();
IDataAccessCredentials credentialsIfExists = _tableToSync.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
string pwd = null;
string usr = null;
if (credentialsIfExists != null)
{
usr = credentialsIfExists.Username;
pwd = credentialsIfExists.GetDecryptedPassword();
}
ITableInfoImporter importer;
//for importing new stuff
if (_tableToSync.IsTableValuedFunction)
{
importer = new TableValuedFunctionImporter(_repository, (DiscoveredTableValuedFunction)expectedTable);
}
else
{
importer = new TableInfoImporter(_repository, _toSyncTo.Name, _toSyncTo.GetCurrentDatabase().GetRuntimeName(), _tableToSync.GetRuntimeName(), _tableToSync.DatabaseType, username: usr, password: pwd, importFromSchema: _tableToSync.Schema, importTableType: _tableToSync.IsView ? TableType.View:TableType.Table);
}
DiscoveredColumn[] newColumnsInLive =
liveColumns.Where(
live => !catalogueColumns.Any(columnInfo =>
columnInfo.GetRuntimeName()
.Equals(live.GetRuntimeName()))).ToArray();
//there are new columns in the live database that are not in the Catalogue
if (newColumnsInLive.Any())
{
//see if user wants to add missing columns
bool addMissingColumns = notifier.OnCheckPerformed(new CheckEventArgs("The following columns are missing from the TableInfo:" + string.Join(",", newColumnsInLive.Select(c => c.GetRuntimeName())), CheckResult.Fail, null, "The ColumnInfos will be created and added to the TableInfo"));
List <ColumnInfo> added = new List <ColumnInfo>();
if (addMissingColumns)
{
foreach (DiscoveredColumn missingColumn in newColumnsInLive)
{
added.Add(importer.CreateNewColumnInfo(_tableToSync, missingColumn));
}
ForwardEngineerExtractionInformationIfAppropriate(added, notifier);
}
else
{
IsSynched = false;
}
}
//See if we need to delete any ColumnInfos
ColumnInfo[] columnsInCatalogueButSinceDisapeared =
catalogueColumns
.Where(columnInfo => !liveColumns.Any( //there are not any
c => columnInfo.GetRuntimeName().Equals(c.GetRuntimeName())) //columns with the same name between discovery/columninfo
).ToArray();
if (columnsInCatalogueButSinceDisapeared.Any())
{
foreach (var columnInfo in columnsInCatalogueButSinceDisapeared)
{
bool deleteExtraColumnInfos = notifier.OnCheckPerformed(new CheckEventArgs("The ColumnInfo " + columnInfo.GetRuntimeName() + " no longer appears in the live table.", CheckResult.Fail, null, "Delete ColumnInfo " + columnInfo.GetRuntimeName()));
if (deleteExtraColumnInfos)
{
columnInfo.DeleteInDatabase();
}
else
{
IsSynched = false;
}
}
}
_tableToSync.ClearAllInjections();
if (IsSynched)
{
IsSynched = SynchronizeTypes(notifier, liveColumns);
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have primary keys!
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsPrimaryKey");
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have autonum
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsAutoIncrement");
}
if (IsSynched)
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "Collation");
}
if (IsSynched && _tableToSync.IsTableValuedFunction)
{
IsSynched = SynchronizeParameters((TableValuedFunctionImporter)importer, notifier);
}
_tableToSync.ClearAllInjections();
//get list of primary keys from underlying table
return(IsSynched);
}
public bool CreateDatabase(string createTablesAndFunctionsSql, string initialVersionNumber, ICheckNotifier notifier)
{
try
{
// The _builder has InitialCatalog set which will cause the pre-database creation connection to fail, so create one which doesn't contain InitialCatalog
var serverBuilder = new SqlConnectionStringBuilder(_builder.ConnectionString)
{
InitialCatalog = ""
};
DiscoveredServer server = new DiscoveredServer(serverBuilder);
server.TestConnection();
var db = server.ExpectDatabase(_database);
if (db.Exists())//make sure database does not already exist
{
bool createAnyway = notifier.OnCheckPerformed(new CheckEventArgs("Database already exists", CheckResult.Warning, null, "Attempt to create database inside existing database (will cause problems if the database is not empty)?"));
if (!createAnyway)
{
throw new Exception("User chose not continue");
}
}
else
{
using (var con = server.GetConnection())//do it manually
{
con.Open();
server.GetCommand("CREATE DATABASE " + _database + (BinaryCollation?" COLLATE Latin1_General_BIN2":""), con).ExecuteNonQuery();
notifier.OnCheckPerformed(new CheckEventArgs("Database " + _database + " created", CheckResult.Success, null));
}
}
SqlConnection.ClearAllPools();
using (var con = db.Server.GetConnection())
{
con.Open();
var cmd = db.Server.GetCommand("CREATE SCHEMA " + RoundhouseSchemaName, con);
cmd.ExecuteNonQuery();
var sql =
@"CREATE TABLE [RoundhousE].[ScriptsRun](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[version_id] [bigint] NULL,
[script_name] [nvarchar](255) NULL,
[text_of_script] [text] NULL,
[text_hash] [nvarchar](512) NULL,
[one_time_script] [bit] NULL,
[entry_date] [datetime] NULL,
[modified_date] [datetime] NULL,
[entered_by] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
CREATE TABLE [RoundhousE].[Version](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[repository_path] [nvarchar](255) NULL,
[version] [nvarchar](50) NULL,
[entry_date] [datetime] NULL,
[modified_date] [datetime] NULL,
[entered_by] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
";
var cmd2 = db.Server.GetCommand(sql, con);
cmd2.ExecuteNonQuery();
}
RunSQL(db, createTablesAndFunctionsSql, InitialDatabaseScriptName);
SetVersion(db, "Initial Setup", initialVersionNumber);
notifier.OnCheckPerformed(new CheckEventArgs("Tables created", CheckResult.Success, null));
notifier.OnCheckPerformed(new CheckEventArgs("Setup Completed successfully", CheckResult.Success, null));
return(true);
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Create failed", CheckResult.Fail, e));
return(false);
}
}
public MasterDatabaseScriptExecutor(DiscoveredServer server, string database) : this(server.ExpectDatabase(database))
{
}
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);
}
