public MigrationConfiguration(DiscoveredDatabase fromDatabaseInfo, LoadBubble fromBubble, LoadBubble toBubble, INameDatabasesAndTablesDuringLoads namer)
{
_fromDatabaseInfo = fromDatabaseInfo;
_fromBubble = fromBubble;
_toBubble = toBubble;
_namer = namer;
}
public void TestTableInfo(bool usenamer)
{
string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
var sw = new StreamWriter(filename);
sw.WriteLine("name,name2");
sw.WriteLine("Bob,Munchousain");
sw.WriteLine("Franky,Hollyw9ood");
sw.Flush();
sw.Close();
sw.Dispose();
TableInfo ti;
ColumnInfo[] cols;
Import(_table, out ti, out cols);
var attacher = new AnySeparatorFileAttacher();
attacher.Initialize(LoadDirectory, _database);
attacher.Separator = ",";
attacher.FilePattern = "bob*";
attacher.TableToLoad = ti;
INameDatabasesAndTablesDuringLoads namer = null;
if (usenamer)
{
_table.Rename("AAA");
namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(_database, "AAA");
}
var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, namer), ti);
var exitCode = attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
using (var con = _database.Server.GetConnection())
{
con.Open();
var r = _database.Server.GetCommand("Select name,name2 from " + _table.GetRuntimeName(), con).ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual("Bob", r["name"]);
Assert.AreEqual("Munchousain", r["name2"]);
Assert.IsTrue(r.Read());
Assert.AreEqual("Franky", r["name"]);
Assert.AreEqual("Hollyw9ood", r["name2"]);
}
attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
File.Delete(filename);
}
/// <summary>
/// Preferred Constructor, creates RAW, STAGING, LIVE connection strings based on the data access points in the LoadMetadata, also respects the ServerDefaults for RAW override (if any)
/// </summary>
/// <param name="lmd"></param>
/// <param name="namer"></param>
public HICDatabaseConfiguration(ILoadMetadata lmd, INameDatabasesAndTablesDuringLoads namer = null) :
this(lmd.GetDistinctLiveDatabaseServer(), namer, lmd.CatalogueRepository.GetServerDefaults(), lmd.OverrideRAWServer)
{
var globalIgnorePattern = GetGlobalIgnorePatternIfAny(lmd.CatalogueRepository);
if (globalIgnorePattern != null && !string.IsNullOrWhiteSpace(globalIgnorePattern.Regex))
{
IgnoreColumns = new Regex(globalIgnorePattern.Regex);
}
}
/// <inheritdoc/>
public string GetDatabaseRuntimeName(LoadStage loadStage, INameDatabasesAndTablesDuringLoads namer = null)
{
var baseName = GetDatabaseRuntimeName();
if (namer == null)
{
namer = new FixedStagingDatabaseNamer(baseName);
}
return(namer.GetDatabaseName(baseName, loadStage.ToLoadBubble()));
}
/// <inheritdoc/>
public string GetRuntimeName(LoadBubble bubble, INameDatabasesAndTablesDuringLoads tableNamingScheme = null)
{
// If no naming scheme is specified, the default 'FixedStaging...' prepends the database name and appends '_STAGING'
if (tableNamingScheme == null)
{
tableNamingScheme = new FixedStagingDatabaseNamer(Database);
}
string baseName = GetQuerySyntaxHelper().GetRuntimeName(Name);
return(tableNamingScheme.GetName(baseName, bubble));
}
//Constructor
internal StandardDatabaseHelper(DiscoveredDatabase liveDatabase, INameDatabasesAndTablesDuringLoads namer, DiscoveredServer rawServer)
{
DatabaseNamer = namer;
foreach (LoadBubble stage in new[] { LoadBubble.Raw, LoadBubble.Staging, LoadBubble.Live, })
{
var stageName = DatabaseNamer.GetDatabaseName(liveDatabase.GetRuntimeName(), stage);
DatabaseInfoList.Add(stage, stage == LoadBubble.Raw ? rawServer.ExpectDatabase(stageName) : liveDatabase.Server.ExpectDatabase(stageName));
}
}
public DicomRelationalMapperQueueConsumer(IRDMPPlatformRepositoryServiceLocator repositoryLocator, LoadMetadata lmd, INameDatabasesAndTablesDuringLoads namer, DicomRelationalMapperOptions options)
{
_lmd = lmd;
_repositoryLocator = repositoryLocator;
DatabaseNamer = namer;
_minimumBatchSize = options.MinimumBatchSize;
_useInsertIntoForRawMigration = options.UseInsertIntoForRAWMigration;
_retryOnFailureCount = options.RetryOnFailureCount;
_retryDelayInSeconds = Math.Max(10, options.RetryDelayInSeconds);
_maximumRunDelayInSeconds = new TimeSpan(0, 0, 0, options.MaximumRunDelayInSeconds <= 0 ? 15 : 0);
StartDleRunnerTask();
}
/// <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 override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return
(String.Format(
@"
ALTER TABLE {0} Add {1}_bit bit
GO
UPDATE {0} SET {1}_bit = CASE WHEN {1} is null THEN 0 else 1 end
GO
ALTER TABLE {0} DROP column {1}
GO
EXEC sp_rename '{0}.{1}_bit', '{1}' , 'COLUMN'
GO
", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
public ExitCodeType Mutilate(IDataLoadJob listener)
{
if (TimePeriodicityField == null)
{
throw new InvalidOperationException("TimePeriodicityField has not been set.");
}
var liveDatabaseInfo = GetLiveDatabaseInfo();
if (TestContext)
{
// If we are operating inside a test, the client is responsible for providing a TableNamingScheme
if (TableNamingScheme == null)
{
throw new InvalidOperationException("Executing within test context but no TableNamingScheme has been provided");
}
}
else
{
// If we are not operating inside a Test, hardwire the TableNamingScheme
TableNamingScheme = new FixedStagingDatabaseNamer(liveDatabaseInfo.GetRuntimeName());
}
// create invariant helpers
_sqlHelper = new BackfillSqlHelper(TimePeriodicityField, _dbInfo, liveDatabaseInfo);
_migrationConfiguration = new MigrationConfiguration(liveDatabaseInfo, LoadBubble.Live, LoadBubble.Staging, TableNamingScheme);
// starting with the TimePeriodicity table, we descend the join relationships to the leaf tables then ascend back up to the TimePeriodicity table
// at each step we determine the effective date of the record by joining back to the TimePeriodicity table
// this allows us to remove updates that are older than the corresponding record in live
// - however we don't remove rows that still have children, hence the recursion from leaves upwards
// -- a record may be an 'old update', but have a child for insertion (i.e. the child is not in live), in this case the parent must remain in staging despite it being 'old'
// - 'old updates' that are not deleted (because they have new descendants) must have their own data updated to reflect what is in live if there is a difference between the two, otherwise we may overwrite live with stale data
//
_tiWithTimeColumn = TimePeriodicityField.TableInfo;
ProcessOldUpdatesInTable(_tiWithTimeColumn, new List <JoinInfo>());
// Having processed all descendants of the TimePeriodicity table, we now recursively ascend back up through its predecessors to the top of the join tree
// Doing effectively the same thing, removing items that are older than the corresponding live items that do not also have new descendants and updating staging rows with live data where required.
ProcessPredecessors(_tiWithTimeColumn, new List <JoinInfo>());
return(ExitCodeType.Success);
}
public ExitCodeType Mutilate(IDataLoadJob job)
{
_dataLoadInfoId = job.JobID;
_namer = job.Configuration.DatabaseNamer;
_job = job;
BuildJoinOrder(false);
foreach (TableInfo tableInfo in TablesToIsolate)
{
var pkCol = tableInfo.ColumnInfos.Single(c => c.IsPrimaryKey);
foreach (string pkValue in DetectCollisions(pkCol, tableInfo))
{
_job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Found duplication in column '" + pkCol + "', duplicate value was '" + pkValue + "'"));
MigrateRecords(pkCol, pkValue);
}
}
return(ExitCodeType.Success);
}
public ExitCodeType Mutilate(IDataLoadJob job)
{
_dataLoadInfoId = job.JobID;
_namer = job.Configuration.DatabaseNamer;
_job = job;
BuildJoinOrder(false);
foreach (TableInfo tableInfo in TablesToIsolate)
{
var pkCol = tableInfo.ColumnInfos.Single(c => c.IsPrimaryKey);
var allCollisions = DetectCollisions(pkCol, tableInfo).Distinct().ToArray();
if (allCollisions.Any())
{
_job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Found duplication in column '{pkCol}', duplicate values were '{string.Join(",",allCollisions)}'"));
MigrateRecords(pkCol, allCollisions);
}
}
return(ExitCodeType.Success);
}
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return(String.Format(@"IF OBJECT_ID('dbo.RoundDateToMiddleOfQuarter') IS NOT NULL
DROP FUNCTION RoundDateToMiddleOfQuarter
GO
CREATE FUNCTION RoundDateToMiddleOfQuarter
(
-- Add the parameters for the function here
@DOB date
)
RETURNS date
AS
BEGIN
-- Declare the return variable here
DECLARE @anonDOB date
-- Add the T-SQL statements to compute the return value here
IF MONTH(@DOB) IN (1,2,3)
SET @anonDOB = LEFT(@DOB, 4) + '0215'
ELSE IF MONTH(@DOB) IN (4,5,6)
SET @anonDOB = LEFT(@DOB, 4) + '0515'
ELSE IF MONTH(@DOB) IN (7,8,9)
SET @anonDOB = LEFT(@DOB, 4) + '0815'
ELSE IF MONTH(@DOB)IN (10,11,12)
SET @anonDOB = LEFT(@DOB, 4) + '1115'
ELSE SET @anonDOB = NULL
-- Return the result of the function
RETURN @anonDOB
END
GO
UPDATE {0} SET {1}=dbo.RoundDateToMiddleOfQuarter({1})
GO",
ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
private string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
var factory = new DilutionOperationFactory(ColumnToDilute);
return(factory.Create(Operation).GetMutilationSql(namer));
}
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return
(string.Format(@"
IF OBJECT_ID('dbo.RemoveDodgyCharacters') IS NOT NULL
DROP FUNCTION RemoveDodgyCharacters
GO
Create Function [dbo].[RemoveDodgyCharacters](@Temp VarChar(max))
Returns VarChar(max)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^A-Za-z0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
GO
IF OBJECT_ID('dbo.Left4OfPostcodes') IS NOT NULL
DROP FUNCTION Left4OfPostcodes
GO
CREATE FUNCTION Left4OfPostcodes
(
-- Add the parameters for the function here
@str varchar(max)
)
RETURNS varchar(4)
AS
BEGIN
--Pass through nulls
if @str IS NULL
RETURN @str
--Start by stripping out all dodgy characters (see method above)
DECLARE @hackedStr varchar(max)
set @hackedStr = dbo.RemoveDodgyCharacters(@str)
--If the result is less than 3 characters, return the original string
if LEN(@hackedStr) < 3
RETURN @str
--Part we are about to discard
DECLARE @discardedBit varchar(3)
SET @discardedBit = RIGHT(@hackedStr,3)
--http://www.mrs.org.uk/pdf/postcodeformat.pdf
--PO1 3AX
--Have we identified the 3AX bit correctly?
if PATINDEX('[0-9][A-Za-z][A-Za-z]' ,UPPER(@discardedBit)) = 1 --Yes
RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3) --Return the hacked string (no dodgy characters) minus the validated suffix
--Suffix is missing or malformed but there is 5 or more characters so we aren't looking at 'DD3' we are looking at 'DD3 5L5' where the final digit should be a char but is an int by mistype
if LEN(@hackedStr) > 4 AND LEN(@hackedStr) < 8
RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3)
RETURN @hackedStr --Else just return the hacked String (at least we did them the favour of removing dodgy characters and the varchar(4) means we probably fulfilled a contract reasonably anyway
END
GO
UPDATE {0} SET {1}=dbo.Left4OfPostcodes({1})
GO", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
/// <inheritdoc/>
public string GetRuntimeName(LoadStage stage, INameDatabasesAndTablesDuringLoads tableNamingScheme = null)
{
return(GetRuntimeName(stage.ToLoadBubble(), tableNamingScheme));
}
public void TestCoalescer_RampantNullness(DatabaseType type, bool useCustomNamer)
{
var db = GetCleanedServer(type, "TestCoalescer");
int batchCount = 1000;
DataTable dt = new DataTable("TestCoalescer_RampantNullness");
dt.Columns.Add("pk");
dt.Columns.Add("f1");
dt.Columns.Add("f2");
dt.Columns.Add("f3");
dt.Columns.Add("f4");
Random r = new Random();
for (int i = 0; i < batchCount; i++)
{
int randInt = r.Next(250);
int randCompleteness = r.Next(4);
dt.Rows.Add(new object[] { randInt, randInt, randInt, randInt, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, randInt, randInt });
if (randCompleteness >= 1)
{
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, randInt, randInt });
}
if (randCompleteness >= 2)
{
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, randInt, randInt });
}
if (randCompleteness >= 3)
{
dt.Rows.Add(new object[] { randInt, randInt, randInt, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, randInt, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, randInt, randInt, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value });
}
}
var tbl = db.CreateTable(dt.TableName, dt);
var importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out var tableInfo, out var colInfos);
//lie about what hte primary key is because this component is designed to run in the RAW environment and we are simulating a LIVE TableInfo (correctly)
var pk = colInfos.Single(c => c.GetRuntimeName().Equals("pk"));
pk.IsPrimaryKey = true;
pk.SaveToDatabase();
INameDatabasesAndTablesDuringLoads namer = null;
if (useCustomNamer)
{
tbl.Rename("AAAA");
namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "AAAA");
}
HICDatabaseConfiguration configuration = new HICDatabaseConfiguration(db.Server, namer);
var coalescer = new Coalescer();
coalescer.TableRegexPattern = new Regex(".*");
coalescer.CreateIndex = true;
coalescer.Initialize(db, LoadStage.AdjustRaw);
var job = new ThrowImmediatelyDataLoadJob(configuration, tableInfo);
coalescer.Mutilate(job);
var dt2 = tbl.GetDataTable();
foreach (DataRow row in dt2.Rows)
{
Assert.AreNotEqual(DBNull.Value, row["f1"]);
Assert.AreNotEqual(DBNull.Value, row["f2"]);
Assert.AreNotEqual(DBNull.Value, row["f3"]);
Assert.AreNotEqual(DBNull.Value, row["f4"]);
}
db.Drop();
}
public ParallelDLEHost(IRDMPPlatformRepositoryServiceLocator repositoryLocator, INameDatabasesAndTablesDuringLoads namer, bool useInsertIntoForRawMigration)
{
_repositoryLocator = repositoryLocator;
_namer = namer;
_useInsertIntoForRawMigration = useInsertIntoForRawMigration;
}
public abstract string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer);
/// <summary>
/// Preferred Constructor, creates RAW, STAGING, LIVE connection strings based on the data access points in the LoadMetadata, also respects the ServerDefaults for RAW override (if any)
/// </summary>
/// <param name="lmd"></param>
/// <param name="namer"></param>
public HICDatabaseConfiguration(ILoadMetadata lmd, INameDatabasesAndTablesDuringLoads namer = null) :
this(lmd.GetDistinctLiveDatabaseServer(), namer, lmd.CatalogueRepository.GetServerDefaults(), lmd.OverrideRAWServer)
{
}
