private void CheckCircleReferences(Table table, TableMappingDefinition mappingDefinition)
{
if (_tablesToUpdateCircleReferences.ContainsKey(table.Name))
{
_tablesToUpdateCircleReferences[table.Name].ForEach(r =>
{
TableMappingDefinition temp = _tableMappingDefinitions.Single(d => d.DestinationTable.Name.Equals(r.OriginTableName));
FieldMappingDefinition fieldMappingDefinition = temp.FieldMappingDefinitions.SingleOrDefault(f => f.DestinationField.Name.Equals(r.OriginFieldName));
if (fieldMappingDefinition != null)
{
SourceTable sourceTable = temp.SourceTable;
Field sourceField = fieldMappingDefinition.SourceField;
DestinationTable destinationTable = temp.DestinationTable;
Field destinationField = fieldMappingDefinition.DestinationField;
FieldMappingInfo info = new FieldMappingInfo
{
DestinationField = destinationField,
DestinationTable = destinationTable,
SourceField = sourceField,
SourceTable = sourceTable
};
mappingDefinition.CircleReferences.Add(info);
}
});
}
}
private bool CanMap(DestinationTable table)
{
bool result = true;
if (table.IsMapped)
{
result = false;
}
else
{
List <Reference> configIngoreReference = null;
if (this._ignoreCircleReferences.ContainsKey(table.Name))
{
configIngoreReference = this._ignoreCircleReferences[table.Name];
}
var referenceFields = table.Fields.Where(f => f.Type.HasFlag(FieldType.ForeignKey)).ToList();
foreach (Field field in referenceFields)
{
if (configIngoreReference != null && configIngoreReference.Any(r => r.ReferenceTableName.Equals(field.Reference.ReferenceTableName)))
{
continue;
}
DestinationTable referenceTable = _destinationDatabase.GetTable(field.Reference.ReferenceTableName);
if (referenceTable.IsMapped == false)
{
result = false;
break;
}
}
}
return(result);
}
public void Serialize(BinaryWriter output)
{
output.Write(StepId);
DestinationTable.Serialize(output);
DestinationRow.Serialize(output);
SerializationHelper.Serialize(output.BaseStream, ForeignKey);
}
private string DoConversion()
{
Logger.Info("Doing conversion");
List <string> scriptNames = new List <string>();
Dictionary <string, string> bcpExportCommands = new Dictionary <string, string>();
Dictionary <string, string> bcpImportCommands = new Dictionary <string, string>();
_destinationDatabase.Tables.ForEach(t =>
{
DestinationTable destinationTable = t;
SourceTable sourceTable = null;
try
{
Logger.Info("Processing " + destinationTable.Name);
TableMappingConfiguration mappingConfig = GetTableMappingConfig(destinationTable.Name);
sourceTable = GetSourceTable(destinationTable.Name, mappingConfig);
var mappingDefinition = CreateTableMappingDefinition(sourceTable, destinationTable, mappingConfig);
SqlGenerator sqlGenerator = new SqlGenerator(mappingDefinition, _options.CheckExistData);
BcpGenerator bcpGenerator = new BcpGenerator(_options.ServerName, _options.InstanceName, mappingDefinition, _options.BcpMode);
HandleBcp(sqlGenerator, bcpGenerator, mappingDefinition, bcpExportCommands, bcpImportCommands);
HandleMaxTextUpdate(sqlGenerator, mappingDefinition, scriptNames);
if (_options.BlobHandling)
{
HandleBlobUpdate(sqlGenerator, mappingDefinition, scriptNames);
}
}
catch (AppException ex)
{
if (ex.ErrorCode == AppExceptionCodes.DATABASE_ERROR_TABLE_NOT_FOUND)
{
Logger.Warn(destinationTable.Name + " not mapped");
}
}
});
// Generate bat file
string exportScript = BatGenerator.GenerateBcpExecuteBat(bcpExportCommands);
SaveToFile(_packageOutputPath, "BCP_Export.bat", exportScript);
Logger.Info("BCP Exporting data");
Process.Start(new ProcessStartInfo {
WorkingDirectory = _packageOutputPath, FileName = "BCP_Export.bat"
});
string importScript = BatGenerator.GenerateBcpExecuteBat(bcpImportCommands);
//SaveToFile(_packageOutputPath, "BCP_Import.bat", importScript);
List <string> scriptPaths = scriptNames.Select(s => Path.Combine(CONVERSION_PACKAGE_FOLDER, s)).ToList();
string updateScript = BatGenerator.GenerateSqlExecuteBat(scriptPaths, _options.ServerName, _options.InstanceName, _options.Username, _options.Password);
//SaveToFile(_packageOutputPath, "Update_Blob_Text.bat", updateScript);
string batScript = importScript + Environment.NewLine + updateScript;
return(batScript);
}
public void Serialize(BinaryWriter output)
{
var bf = SerializationHelper.DefaultFormatter;
output.Write(StepId);
DestinationTable.Serialize(output);
DestinationRow.Serialize(output);
bf.Serialize(output.BaseStream, ForeignKey);
}
private DestinationTable CreateDestination(string schemaName, string tableName)
{
GetUniqueTableName(schemaName, ref tableName);
var destination = new DestinationTable(
MyDBDataset,
MyDBDataset.DatabaseName,
schemaName,
tableName,
Graywulf.Schema.TableInitializationOptions.Create);
return(destination);
}
private void InitializeMembers(StreamingContext context)
{
this.queryTimeout = 60; // TODO ***
this.destination = null;
this.isDestinationTableInitialized = false;
this.sourceDatabaseVersionName = String.Empty;
this.statDatabaseVersionName = String.Empty;
this.tableStatistics = new List <TableReference>();
this.partitions = new List <QueryPartitionBase>();
this.partitioningTable = null;
this.partitioningKey = null;
}
private void CopyMembers(QueryBase old)
{
this.queryTimeout = old.queryTimeout;
this.destination = old.destination;
this.isDestinationTableInitialized = old.isDestinationTableInitialized;
this.sourceDatabaseVersionName = old.sourceDatabaseVersionName;
this.statDatabaseVersionName = old.statDatabaseVersionName;
this.tableStatistics = new List <TableReference>(); // *** TODO
this.partitions = new List <QueryPartitionBase>(old.partitions.Select(p => (QueryPartitionBase)p.Clone()));
this.partitioningTable = old.partitioningTable;
this.partitioningKey = old.partitioningKey;
}
private void HandleMaxTextUpdate(SqlGenerator sqlGenerator, TableMappingDefinition mappingDefinition, List <string> scriptNames)
{
Logger.Info("Handling max text");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string postSqlPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
Logger.Info("Generating script for updating max text fields");
string script = sqlGenerator.GenerateVarCharMaxUpdateScript();
if (!string.IsNullOrEmpty(script))
{
var fileName = string.Format("{0}-{1}_TEXT.sql", srcTable.Name, destTable.Name);
SaveToFile(postSqlPackagePath, fileName, script);
scriptNames.Add(fileName);
}
}
public void Serialize(BinaryWriter output, FastAccessList <object> referenceTracking)
{
var bf = SerializationHelper.DefaultFormatter;
output.Write(StepId);
output.Write(Depth);
SourceTable.Serialize(output);
DestinationTable.Serialize(output);
//Variable
output.Write(Variables.Count);
foreach (var v in Variables)
{
var vId = referenceTracking.TryAdd(v);
output.Write(vId);
}
//TableSchema
var tsId = referenceTracking.TryAdd(TableSchema);
output.Write(tsId);
//Datarow
output.Write(Datarow.Length);
for (var i = 0; i < Datarow.Length; i++)
{
var data = Datarow[i];
var type = data.GetType();
var tag = SerializationHelper.TypeToTag[type];
output.Write(tag);
if (type == typeof(SqlVariable))
{
var svId = referenceTracking.TryAdd(data);
output.Write(svId);
}
else
{
bf.Serialize(output.BaseStream, data);
}
}
}
/// <summary>
/// Creates and initializes a remote or local table copy task
/// </summary>
/// <param name="source"></param>
/// <param name="destination"></param>
/// <param name="local"></param>
/// <returns></returns>
protected ICopyTable CreateTableCopyTask(SourceTableQuery source, DestinationTable destination, bool local)
{
var desthost = GetHostnameFromSqlConnectionString(destination.Dataset.ConnectionString);
ICopyTable qi;
if (local)
{
qi = new CopyTable();
}
else
{
qi = RemoteServiceHelper.CreateObject <ICopyTable>(desthost);
}
qi.Source = source;
qi.Destination = destination;
return(qi);
}
public TableMappingDefinition(SourceTable sourceTable, DestinationTable destinationTable, List<FieldMappingConfiguration> explicitMappings = null)
{
_sourceTable = sourceTable;
_destinationTable = destinationTable;
bool hasExplicitMappings = explicitMappings != null;
destinationTable.Fields.ForEach(f =>
{
try
{
if(hasExplicitMappings)
{
var mappingConfig = explicitMappings.SingleOrDefault(m => m.DestinationFieldName.Equals(f.Name, StringComparison.InvariantCultureIgnoreCase));
if (mappingConfig != null)
{
var sourceField = sourceTable.GetField(mappingConfig.SourceFieldName);
FieldMappingDefinitions.Add(new FieldMappingDefinition(sourceField, f, mappingConfig.Type) { BlobCategory = mappingConfig.BlobCategory, ForceValue = mappingConfig.ForceValue });
}
else
{
var sourceField = sourceTable.GetField(f.Name);
FieldMappingDefinitions.Add(new FieldMappingDefinition(sourceField, f));
}
}
else
{
var sourceField = sourceTable.GetField(f.Name);
FieldMappingDefinitions.Add(new FieldMappingDefinition(sourceField, f));
}
}
catch(MigrationException ex)
{
if(ex.ErrorCode == MigrationExceptionCodes.DATABASE_ERROR_FIELD_NOT_FOUND)
{
// TODO: write log
//Console.WriteLine(sourceTable.Name + " -> " + destinationTable.Name);
}
}
});
}
/// <summary>
/// Copies a table from a remote data source by creating and
/// executing a table copy task.
/// </summary>
/// <param name="table"></param>
/// <param name="source"></param>
public void CopyRemoteTable(TableReference table, SourceTableQuery source)
{
// Create a target table name
var temptable = GetTemporaryTable(GetEscapedUniqueName(table));
TemporaryTables.TryAdd(table.UniqueName, temptable);
var dest = new DestinationTable(temptable)
{
Options = TableInitializationOptions.Drop | TableInitializationOptions.Create
};
var tc = CreateTableCopyTask(source, dest, false);
var guid = Guid.NewGuid();
RegisterCancelable(guid, tc);
tc.Execute();
UnregisterCancelable(guid);
}
private void HandleBcp(SqlGenerator sqlGenerator, BcpGenerator bcpGenerator, TableMappingDefinition mappingDefinition, Dictionary <string, string> bcpExportCommands, Dictionary <string, string> bcpImportCommands)
{
Logger.Info("Handling BCP");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string bcpPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
// Generate and save format file
Logger.Info("Generating BCP format file for EXPORT");
string bcpExportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Export);
string exportFmtFileName = string.Format("{0}_EXPORT.fmt", destTable.Name);
string exportFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, exportFmtFileName);
SaveToFile(bcpPackagePath, exportFmtFileName, bcpExportFormatFile);
Logger.Info("Generating BCP format file for IMPORT");
string bcpImportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Import);
string importFmtFileName = string.Format("{0}_IMPORT.fmt", destTable.Name);
string importFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, importFmtFileName);
SaveToFile(bcpPackagePath, importFmtFileName, bcpImportFormatFile);
// Generate export, import commands
Logger.Info("Generating BCP Export/Import commands");
string bcpSelect = sqlGenerator.GenerateSelectStatement();
string dataFileName = string.Format("{0}-{1}_BCP.txt", srcTable.Name, destTable.Name);
string dataFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, dataFileName);
File.Create(Path.Combine(_packageOutputPath, dataFilePath));
var bcpExportCommand = bcpGenerator.GenerateExportCommand(bcpSelect, exportFmtFilePath, dataFilePath);
bcpExportCommands.Add(dataFileName, bcpExportCommand);
var bcpImportCommand = bcpGenerator.GenerateImportCommand(importFmtFilePath, dataFilePath);
bcpImportCommands.Add(dataFileName, bcpImportCommand);
}
/// <summary>
/// Copies resultset from the output temporary table to the destination database (MYDB)
/// </summary>
public void CopyResultset()
{
switch (Query.ExecutionMode)
{
case ExecutionMode.SingleServer:
// Do nothing as execute writes results directly into destination table
break;
case ExecutionMode.Graywulf:
{
var source = GetOutputSourceQuery();
var destination = new DestinationTable(Query.Destination)
{
// Change destination to Append, output table has already been created,
// partitions only append to it
Options = TableInitializationOptions.Append
};
DumpSqlCommand(source.Query);
// Create bulk copy task and execute it
var tc = CreateTableCopyTask(source, destination, false);
var guid = Guid.NewGuid();
RegisterCancelable(guid, tc);
tc.Execute();
UnregisterCancelable(guid);
}
break;
default:
throw new NotImplementedException();
}
}
public void Wait() => DestinationTable.Wait();
public TableMappingDefinition(SourceTable sourceTable, DestinationTable destTable, List <FieldMappingConfiguration> explicitMappings = null)
{
_sourceTable = sourceTable;
_destinationTable = destTable;
bool hasExplicitMappings = explicitMappings != null;
_destinationTable.Fields.ForEach(f =>
{
if (hasExplicitMappings)
{
var mappingConfig = explicitMappings.SingleOrDefault(m => m.DestinationFieldName.Equals(f.Name, StringComparison.InvariantCultureIgnoreCase));
if (mappingConfig != null)
{
if (!string.IsNullOrEmpty(mappingConfig.GetBlobScriptPath))
{
FieldMappingDefinitions.Add(new FieldMappingDefinition
{
DestinationField = f,
SourceField = new Field {
Name = mappingConfig.SourceFieldName
},
Type = mappingConfig.Type,
BlobCategory = mappingConfig.BlobCategory,
ForceValue = mappingConfig.ForceValue,
GetBlobScriptPath = mappingConfig.GetBlobScriptPath
});
}
else
{
Field srcField = _sourceTable.GetField(mappingConfig.SourceFieldName);
if (srcField != null)
{
FieldMappingDefinitions.Add(new FieldMappingDefinition
{
DestinationField = f,
SourceField = srcField,
Type = mappingConfig.Type,
BlobCategory = mappingConfig.BlobCategory,
ForceValue = mappingConfig.ForceValue,
GetBlobScriptPath = mappingConfig.GetBlobScriptPath
});
}
}
}
else
{
Field srcField = _sourceTable.GetField(f.Name);
if (srcField != null)
{
FieldMappingDefinitions.Add(new FieldMappingDefinition
{
DestinationField = f,
SourceField = srcField
});
}
}
}
else
{
Field srcField = _sourceTable.GetField(f.Name);
if (srcField != null)
{
FieldMappingDefinitions.Add(new FieldMappingDefinition
{
DestinationField = f,
SourceField = srcField
});
}
}
});
}
private TableMappingDefinition CreateTableMappingDefinition(SourceTable srcTable, DestinationTable destTable, TableMappingConfiguration mappingConfig)
{
TableMappingDefinition mappingDefinition;
if (mappingConfig != null)
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable, mappingConfig.FieldMappings);
}
else
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable);
}
return(mappingDefinition);
}
public void GenerateMigrationScripts()
{
int count = 0;
string outputPath = ConfigurationManager.AppSettings["SQLOutputFolder"];
outputPath = Path.Combine(outputPath, DateTime.Now.ToString("ddMMyyyy"));
List <string> scriptNames = new List <string>();
var temp = new List <string>();
while (count < _destinationDatabase.Tables.Count)
{
DestinationTable destinationTable = null;
SourceTable sourceTable = null;
try
{
// Get next table to migrate
destinationTable = GetNextTableCanMap();
if (destinationTable == null)
{
break;
}
Console.WriteLine("Processing " + destinationTable.Name);
// Check explicit mapping for source table - destination table
TableMappingDefinition mappingDefinition;
TableMappingConfiguration mappingConfig = GetTableMappingConfig(destinationTable.Name);
sourceTable = GetSourceTable(destinationTable.Name, mappingConfig);
if (mappingConfig != null)
{
mappingDefinition = new TableMappingDefinition(sourceTable, destinationTable, mappingConfig.FieldMappings);
mappingDefinition.IsIdentityInsert = mappingConfig.IsIdentityInsert;
}
else
{
mappingDefinition = new TableMappingDefinition(sourceTable, destinationTable);
}
// Retain mapping definition for later use
_tableMappingDefinitions.Add(mappingDefinition);
// Check circle references needed to update
CheckCircleReferences(destinationTable, mappingDefinition);
// Generate script
var scriptGenerator = new TableScriptGenerator(_sourceDatabase, mappingDefinition);
var script = scriptGenerator.GenerateScript();
var fileName = string.Format("{0}.{1}-{2}.sql", count++, sourceTable.Name, destinationTable.Name);
Utils.SaveToFile(outputPath, fileName, script);
scriptNames.Add(fileName);
temp.Add(destinationTable.Name);
destinationTable.IsMapped = true;
}
catch (MigrationException ex)
{
if (ex.ErrorCode == MigrationExceptionCodes.DATABASE_ERROR_TABLE_NOT_FOUND)
{
destinationTable.IsMapped = true;
// TODO: write log
Console.WriteLine(destinationTable.Name + " not mapped");
}
}
}
// Generate script clear temp database
var clearScript = string.Format(SqlScriptTemplates.TRUNCATE_TABLE, "[TempDatabase].dbo.[TrackingRecords]");
var clearFileName = string.Format("{0}.Clear.sql", count);
Utils.SaveToFile(outputPath, clearFileName, clearScript);
scriptNames.Add(clearFileName);
// Generate bat file
string batScript = BatGenerator.GenerateSqlExecuteScript(scriptNames, _options.ServerName, _options.InstanceName, outputPath);
Utils.SaveToFile(outputPath, "RunMigration.bat", batScript);
}
public async Task Completion() => await DestinationTable.Completion();
/// <summary>
/// Generates script for inserting data from table to table using SqlScriptTemplates.MERGE template
/// </summary>
/// <param name="isIdentityInsert">if set to <c>true</c> [is identity insert].</param>
/// <returns></returns>
private string GenerateMergeScript(bool isIdentityInsert)
{
SourceTable sourceTable = _definition.SourceTable;
DestinationTable destinationTable = _definition.DestinationTable;
Field sourcePK = sourceTable.Fields.FirstOrDefault(x => x.Type.HasFlag(FieldType.PrimaryKey));
Field destinationPK = destinationTable.Fields.FirstOrDefault(x => x.Type.HasFlag(FieldType.PrimaryKey));
bool hasPK = sourcePK != null && destinationPK != null;
// Generate template's parts
string sourceSelectFields;
string sourceInsertFields;
string targetInsertFields;
string recordComparison;
string sourceConditions = "";
FieldScriptGenerator fieldScriptGenerator = new FieldScriptGenerator(_definition.FieldMappingDefinitions);
fieldScriptGenerator.GeneratePartsForMergeTemplate(out targetInsertFields, out sourceInsertFields, out sourceSelectFields, out recordComparison, isIdentityInsert, hasPK);
// Generate Merge SQL
string mergeSql = "";
if (hasPK)
{
mergeSql = SqlScriptTemplates.MERGE_HAS_PK.Inject(new
{
TargetTableFullName = destinationTable.FullName,
SourceTableFullName = sourceTable.FullName,
SourceTableName = sourceTable.Name,
TargetPKName = destinationPK.Name,
SourcePKName = sourcePK.Name,
SourcePKDataType = sourcePK.DataType,
SourceConditions = sourceConditions,
SourceSelectFields = sourceSelectFields,
SourceInsertFields = sourceInsertFields,
TargetInsertFields = targetInsertFields,
RecordComparison = recordComparison
});
}
else
{
mergeSql = SqlScriptTemplates.MERGE_NO_PK.Inject(new
{
TargetTableFullName = destinationTable.FullName,
SourceTableFullName = sourceTable.FullName,
SourceConditions = sourceConditions,
SourceSelectFields = sourceSelectFields,
SourceInsertFields = sourceInsertFields,
TargetInsertFields = targetInsertFields,
RecordComparison = recordComparison
});
}
// Check Identity Insert
string identityMergeSql = "";
if (isIdentityInsert)
{
identityMergeSql += string.Format(SqlScriptTemplates.IDENTITY_INSERT_ON, destinationTable.FullName) + Environment.NewLine;
identityMergeSql += mergeSql + Environment.NewLine;
identityMergeSql += string.Format(SqlScriptTemplates.IDENTITY_INSERT_OFF, destinationTable.FullName);
}
else
{
identityMergeSql += mergeSql;
}
// Check circle references
string updateCircleSql = "";
if (_definition.CircleReferences.Any())
{
_definition.CircleReferences.ForEach(r =>
{
var sourceRefTablePK = r.SourceTable.GetPrimaryKey();
var destinationRefTablePK = r.DestinationTable.GetPrimaryKey();
updateCircleSql += NewLines(2) + SqlScriptTemplates.MERGE_UPDATE_CIRCLE_REFERENCES.Inject(new
{
TargetTableFullName = r.DestinationTable.FullName,
TargetTableName = r.DestinationTable.Name,
SourceTableFullName = r.SourceTable.FullName,
TargetPKName = destinationRefTablePK.Name,
SourcePKName = sourceRefTablePK.Name,
TargetFKName = r.DestinationField.Name,
TargetReferenceTableName = destinationTable.Name,
SourceFKName = r.SourceField.Name
});
});
}
var result = identityMergeSql + updateCircleSql;
return(result);
}
