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