示例#1
0
            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);
            //}
        }
示例#4
0
        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));
        }
示例#5
0
        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());
        }
示例#7
0
        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());
        }
示例#8
0
        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"));
        }
示例#9
0
        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());
        }
示例#10
0
        /// <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);
        }
示例#12
0
        /// <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]);
        }
示例#14
0
        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);
            }
        }
示例#15
0
        /// <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();
                }
            }
        }
示例#16
0
        //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));
            }
        }
示例#17
0
        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);
        }
示例#19
0
        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);
        }
示例#20
0
        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();
            }
        }
示例#21
0
        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"));
        }
示例#23
0
            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();
                }
            }
示例#24
0
        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));
        }
示例#25
0
        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]);
        }
示例#26
0
 public MappingRepository(DiscoveredServer server, string databaseName)
 {
     _server   = server;
     _database = _server.ExpectDatabase(databaseName);
 }
示例#27
0
        /// <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);
        }
示例#28
0
        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);
            }
        }
示例#29
0
 public MasterDatabaseScriptExecutor(DiscoveredServer server, string database) : this(server.ExpectDatabase(database))
 {
 }
示例#30
0
        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);
        }