public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dillutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
var cmd = destDatabaseInfo.Server.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dillution = dillutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dillution != null)
{
column.DataType.AlterTypeTo(dillution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
private string WorkOutArchiveTableCreationSQL()
{
//script original table
string createTableSQL = _table.ScriptTableCreation(true, true, true);
string toReplaceTableName = "CREATE TABLE " + _table.GetFullyQualifiedName();
if (!createTableSQL.Contains(toReplaceTableName))
{
throw new Exception("Expected to find occurrence of " + toReplaceTableName + " in the SQL " + createTableSQL);
}
//rename table
createTableSQL = createTableSQL.Replace(toReplaceTableName, "CREATE TABLE " + _archiveTable.GetFullyQualifiedName());
string toRemoveIdentities = "IDENTITY\\(\\d+,\\d+\\)";
//drop identity bit
createTableSQL = Regex.Replace(createTableSQL, toRemoveIdentities, "");
return(createTableSQL);
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dilutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Creating table with SQL:" + sql));
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
using (var cmd = destDatabaseInfo.Server.GetCommand(sql, con))
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
//drop hic_ columns
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//if the ColumnInfo is explicitly marked to be ignored
if (_tableInfo.ColumnInfos.Any(c => c.IgnoreInLoads && c.GetRuntimeName(_copyToBubble.ToLoadStage()).Equals(colName)))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is marked IgnoreInLoads"));
drop = true;
}
//also drop any columns we have specifically been told to ignore in the DLE configuration
if (_hicDatabaseConfiguration.IgnoreColumns != null && _hicDatabaseConfiguration.IgnoreColumns.IsMatch(colName))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is matches the gloabl ignores pattern ({_hicDatabaseConfiguration.IgnoreColumns})"));
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dilution = dilutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dilution != null)
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Altering diluted column {colName} to {dilution.Data_type}"));
column.DataType.AlterTypeTo(dilution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}