示例#1
0
        public void ServerHelper_ChangeDatabase_AdHoc(DatabaseType type, bool useApiFirst)
        {
            if (type == DatabaseType.Oracle)
            {
                Assert.Inconclusive("FAnsiSql understanding of Database cannot be encoded in DbConnectionStringBuilder sadly so we can end up with DiscoveredServer with no GetCurrentDatabase");
            }

            //create initial server reference
            var helper = ImplementationManager.GetImplementation(type).GetServerHelper();
            var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky"));

            Assert.AreEqual("loco", server.Name);
            Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName());

            //Use API to change databases
            if (useApiFirst)
            {
                server.ChangeDatabase("omgggg");
                Assert.AreEqual("loco", server.Name);
                Assert.AreEqual("omgggg", server.GetCurrentDatabase().GetRuntimeName());
            }

            //adhoc changes to builder
            server.Builder["Database"] = "Fisss";
            Assert.AreEqual("loco", server.Name);
            Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName());

            server.Builder["Server"] = "Amagad";
            Assert.AreEqual("Amagad", server.Name);
            Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName());
        }
示例#2
0
        public void ServerHelper_ChangeDatabase_AdHoc(DatabaseType type, bool useApiFirst)
        {
            //create initial server reference
            var helper = ImplementationManager.GetImplementation(type).GetServerHelper();
            var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky"));

            Assert.AreEqual("loco", server.Name);
            Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName());

            //Use API to change databases
            if (useApiFirst)
            {
                server.ChangeDatabase("omgggg");
                Assert.AreEqual("loco", server.Name);
                Assert.AreEqual("omgggg", server.GetCurrentDatabase().GetRuntimeName());
            }

            //adhoc changes to builder
            server.Builder["Database"] = "Fisss";
            Assert.AreEqual("loco", server.Name);
            Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName());

            server.Builder["Server"] = "Amagad";
            Assert.AreEqual("Amagad", server.Name);
            Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName());
        }
示例#3
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)));
        }
示例#4
0
        /// <summary>
        /// Creates new databases on the given server for RDMP platform databases
        /// </summary>
        /// <param name="options"></param>
        public void CreatePlatformDatabases(PlatformDatabaseCreationOptions options)
        {
            DiscoveredServerHelper.CreateDatabaseTimeoutInSeconds = options.CreateDatabaseTimeout;

            Create(DefaultCatalogueDatabaseName, new CataloguePatcher(), options);
            Create(DefaultDataExportDatabaseName, new DataExportPatcher(), options);

            var dqe     = Create(DefaultDQEDatabaseName, new DataQualityEnginePatcher(), options);
            var logging = Create(DefaultLoggingDatabaseName, new LoggingDatabasePatcher(), options);

            CatalogueRepository.SuppressHelpLoading = true;

            var repo = new PlatformDatabaseCreationRepositoryFinder(options);

            if (!options.SkipPipelines)
            {
                var creator = new CataloguePipelinesAndReferencesCreation(repo, logging, dqe);
                creator.Create();
            }

            if (options.ExampleDatasets)
            {
                var examples = new ExampleDatasetsCreation(new ThrowImmediatelyActivator(repo, null), repo);
                var server   = new DiscoveredServer(options.GetBuilder("ExampleData"));

                examples.Create(server.GetCurrentDatabase(), new ThrowImmediatelyCheckNotifier()
                {
                    WriteToConsole = true
                }, options);
            }
        }
示例#5
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));
        }
        /// <summary>
        /// Constructor for use in tests, if possible use the LoadMetadata constructor instead
        /// </summary>
        /// <param name="liveServer">The live server where the data is held, IMPORTANT: this must contain InitialCatalog parameter</param>
        /// <param name="namer">optionally lets you specify how to pick database names for the temporary bubbles STAGING and RAW</param>
        /// <param name="defaults">optionally specifies the location to get RAW default server from</param>
        /// <param name="overrideRAWServer">optionally specifies an explicit server to use for RAW</param>
        public HICDatabaseConfiguration(DiscoveredServer liveServer, INameDatabasesAndTablesDuringLoads namer = null, IServerDefaults defaults = null, IExternalDatabaseServer overrideRAWServer = null)
        {
            //respects the override of LIVE server
            var liveDatabase = liveServer.GetCurrentDatabase();

            if (liveDatabase == null)
            {
                throw new Exception("Cannot load live without having a unique live named database");
            }

            // Default namer
            if (namer == null)
            {
                if (liveServer.DatabaseType == DatabaseType.PostgreSql)
                {
                    //create the DLE tables on the live database because postgres can't handle cross database references
                    namer = new FixedStagingDatabaseNamer(liveDatabase.GetRuntimeName(), liveDatabase.GetRuntimeName());
                }
                else
                {
                    namer = new FixedStagingDatabaseNamer(liveDatabase.GetRuntimeName());
                }
            }

            //if there are defaults
            if (overrideRAWServer == null && defaults != null)
            {
                overrideRAWServer = defaults.GetDefaultFor(PermissableDefaults.RAWDataLoadServer);//get the raw default if there is one
            }
            DiscoveredServer rawServer;

            //if there was defaults and a raw default server
            if (overrideRAWServer != null)
            {
                rawServer = DataAccessPortal.GetInstance().ExpectServer(overrideRAWServer, DataAccessContext.DataLoad, false); //get the raw server connection
            }
            else
            {
                rawServer = liveServer; //there is no raw override so we will have to use the live server for RAW too.
            }
            //populates the servers -- note that an empty rawServer value passed to this method makes it the localhost
            DeployInfo = new StandardDatabaseHelper(liveServer.GetCurrentDatabase(), namer, rawServer);

            RequiresStagingTableCreation = true;
        }
        public SelfCertifyingDataAccessPoint(DiscoveredServer server)
        {
            Server       = server.Name;
            Database     = server.GetCurrentDatabase()?.GetRuntimeName();
            DatabaseType = server.DatabaseType;

            Username = server.ExplicitUsernameIfAny;
            Password = server.ExplicitPasswordIfAny;
        }
示例#8
0
        public void ServerHelper_GetCurrentDatabase_WhenNoneSpecified(DatabaseType type)
        {
            var helper  = ImplementationManager.GetImplementation(type).GetServerHelper();
            var builder = helper.GetConnectionStringBuilder("");
            var server  = new DiscoveredServer(builder);

            Assert.AreEqual(null, server.Name);
            Assert.AreEqual(null, server.GetCurrentDatabase());
        }
示例#9
0
        public void ServerHelper_ChangeDatabase(DatabaseType type, bool expectCaps)
        {
            var server = new DiscoveredServer("loco", "bob", type, "franko", "wacky");

            Assert.AreEqual("loco", server.Name);

            //this failure is already exposed by Server_Helper_GetConnectionStringBuilder
            Assert.AreEqual(expectCaps?"BOB":"bob", server.GetCurrentDatabase().GetRuntimeName());

            Assert.AreEqual("franko", server.ExplicitUsernameIfAny);
            Assert.AreEqual("wacky", server.ExplicitPasswordIfAny);

            server.ChangeDatabase("omgggg");

            Assert.AreEqual(server.Name, "loco");

            Assert.AreEqual(expectCaps?"OMGGGG":"omgggg", server.GetCurrentDatabase().GetRuntimeName());
            Assert.AreEqual("franko", server.ExplicitUsernameIfAny);
            Assert.AreEqual("wacky", server.ExplicitPasswordIfAny);
        }
示例#10
0
        public void ServerHelper_GetConnectionStringBuilder(DatabaseType type)
        {
            var helper  = ImplementationManager.GetImplementation(type).GetServerHelper();
            var builder = helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky");

            var server = new DiscoveredServer(builder);

            Assert.AreEqual("loco", server.Name);

            //Oracle does not persist database in connection string
            if (type == DatabaseType.Oracle)
            {
                Assert.IsNull(server.GetCurrentDatabase());
            }
            else
            {
                Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName());
            }

            Assert.AreEqual("franko", server.ExplicitUsernameIfAny);
            Assert.AreEqual("wacky", server.ExplicitPasswordIfAny);
        }
示例#11
0
        public void ServerHelper_GetConnectionStringBuilder_NoDatabase(DatabaseType type, bool useWhitespace)
        {
            var helper  = ImplementationManager.GetImplementation(type).GetServerHelper();
            var builder = helper.GetConnectionStringBuilder("loco", useWhitespace? "  ":null, "franko", "wacky");

            var server = new DiscoveredServer(builder);

            Assert.AreEqual("loco", server.Name);

            Assert.IsNull(server.GetCurrentDatabase());

            Assert.AreEqual("franko", server.ExplicitUsernameIfAny);
            Assert.AreEqual("wacky", server.ExplicitPasswordIfAny);

            server = new DiscoveredServer("loco", useWhitespace?"  ":null, type, "frank", "kangaro");
            Assert.AreEqual("loco", server.Name);

            Assert.IsNull(server.GetCurrentDatabase());
        }
示例#12
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);
        }
示例#13
0
        public int BulkInsertWithBetterErrorMessages(SqlBulkCopy insert, DataTable dt, DiscoveredServer serverForLineByLineInvestigation)
        {
            int rowsWritten = 0;

            EmptyStringsToNulls(dt);

            InspectDataTableForFloats(dt);

            ConvertStringDatesToDateTime(dt);

            try
            {
                //send data read to server
                insert.WriteToServer(dt);
                rowsWritten += dt.Rows.Count;

                return(rowsWritten);
            }
            catch (Exception e)
            {
                //user does not want to replay the load one line at a time to get more specific error messages
                if (serverForLineByLineInvestigation == null)
                {
                    throw;
                }

                int    line          = 1;
                string baseException = ExceptionToListOfInnerMessages(e, true);
                baseException  = baseException.Replace(Environment.NewLine, Environment.NewLine + "\t");
                baseException  = Environment.NewLine + "First Pass Exception:" + Environment.NewLine + baseException;
                baseException += Environment.NewLine + "Second Pass Exception:";

                DiscoveredColumn[] destinationColumnsContainedInMapping;

                try
                {
                    var dest = serverForLineByLineInvestigation.GetCurrentDatabase().ExpectTable(insert.DestinationTableName);
                    destinationColumnsContainedInMapping = dest.DiscoverColumns().Where(c => insert.ColumnMappings.Cast <SqlBulkCopyColumnMapping>().Any(m => m.DestinationColumn == c.GetRuntimeName())).ToArray();
                }
                catch (Exception)
                {
                    throw e; //couldn't even enumerate the destination columns, whatever the original Exception was it must be serious, just rethrow it
                }

                //have to use a new object because current one could have a broken transaction associated with it
                using (var con = (SqlConnection)serverForLineByLineInvestigation.GetConnection())
                {
                    con.Open();
                    SqlTransaction investigationTransaction    = con.BeginTransaction("Investigate BulkCopyFailure");
                    SqlBulkCopy    investigationOneLineAtATime = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, investigationTransaction)
                    {
                        DestinationTableName = insert.DestinationTableName
                    };

                    foreach (SqlBulkCopyColumnMapping m in insert.ColumnMappings)
                    {
                        investigationOneLineAtATime.ColumnMappings.Add(m);
                    }

                    //try a line at a time
                    foreach (DataRow dr in dt.Rows)
                    {
                        try
                        {
                            investigationOneLineAtATime.WriteToServer(new[] { dr }); //try one line
                            line++;
                        }
                        catch (Exception exception)
                        {
                            Regex columnLevelComplaint = new Regex("bcp client for colid (\\d+)");

                            Match match = columnLevelComplaint.Match(exception.Message);

                            if (match.Success)
                            {
                                //it counts from 1 because its stupid, hence to get our column index we subtract 1 from whatever column it is complaining about
                                int columnItHates = Convert.ToInt32(match.Groups[1].Value) - 1;

                                if (destinationColumnsContainedInMapping.Length > columnItHates)
                                {
                                    var offendingColumn = destinationColumnsContainedInMapping[columnItHates];

                                    if (dt.Columns.Contains(offendingColumn.GetRuntimeName()))
                                    {
                                        var sourceValue = dr[offendingColumn.GetRuntimeName()];
                                        throw new FileLoadException(baseException + "BulkInsert complained on data row " + line + " the complaint was about column number " + columnItHates + ": <<" + offendingColumn.GetRuntimeName() + ">> which had value <<" + sourceValue + ">> destination data type was <<" + offendingColumn.DataType + ">>", exception);
                                    }
                                }
                            }

                            throw new FileLoadException(
                                      baseException + "Failed to load data row " + line + " the following values were rejected by the database: " +
                                      Environment.NewLine + dr.ItemArray.Aggregate((o, n) => o + Environment.NewLine + n),
                                      exception);
                        }
                    }

                    //it worked... how!?
                    investigationTransaction.Rollback();
                    con.Close();

                    throw new Exception(baseException + "Bulk insert failed but when we tried to repeat it a line at a time it worked... very strange", e);
                }
            }
        }