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