public abstract string GenerateSchemaScripts(SchemaInfo schemaInfo);
public virtual async Task <string> GenerateDataScriptsAsync(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
if (Option.ScriptOutputMode == GenerateScriptOutputMode.WriteToFile)
{
this.AppendScriptsToFile("", GenerateScriptMode.Data, true);
}
int i = 0;
int pickupIndex = -1;
if (schemaInfo.PickupTable != null)
{
foreach (Table table in schemaInfo.Tables)
{
if (table.Owner == schemaInfo.PickupTable.Owner && table.Name == schemaInfo.PickupTable.Name)
{
pickupIndex = i;
break;
}
i++;
}
}
i = 0;
using (DbConnection connection = this.GetDbConnector().CreateConnection())
{
int tableCount = schemaInfo.Tables.Count - (pickupIndex == -1 ? 0 : pickupIndex + 1);
int count = 0;
foreach (Table table in schemaInfo.Tables)
{
if (i < pickupIndex)
{
i++;
continue;
}
count++;
string strTableCount = $"({count}/{tableCount})";
string tableName = table.Name;
List <TableColumn> columns = schemaInfo.Columns.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order).ToList();
bool isSelfReference = TableReferenceHelper.IsSelfReference(tableName, schemaInfo.TableForeignKeys);
List <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.Owner == table.Owner && item.TableName == tableName).ToList();
string primaryKeyColumns = string.Join(",", primaryKeys.OrderBy(item => item.Order).Select(item => GetQuotedString(item.ColumnName)));
long total = await this.GetTableRecordCountAsync(connection, table);
if (Option.DataGenerateThreshold.HasValue && total > Option.DataGenerateThreshold.Value)
{
continue;
}
int pageSize = Option.DataBatchSize;
this.FeedbackInfo($"{strTableCount}Begin to read data from table {table.Name}, total rows:{total}.");
Dictionary <long, List <Dictionary <string, object> > > dictPagedData;
if (isSelfReference)
{
string parentColumnName = schemaInfo.TableForeignKeys.FirstOrDefault(item =>
item.Owner == table.Owner &&
item.TableName == tableName &&
item.ReferencedTableName == tableName)?.ColumnName;
string strWhere = $" WHERE {GetQuotedString(parentColumnName)} IS NULL";
dictPagedData = this.GetSortedPageDatas(connection, table, primaryKeyColumns, parentColumnName, columns, Option, strWhere);
}
else
{
dictPagedData = await this.GetPagedDataListAsync(connection, table, columns, primaryKeyColumns, total, pageSize);
}
this.FeedbackInfo($"{strTableCount}End read data from table {table.Name}.");
this.AppendDataScripts(Option, sb, table, columns, dictPagedData);
i++;
}
}
var dataScripts = string.Empty;
try
{
dataScripts = sb.ToString();
}
catch (OutOfMemoryException ex)
{
//ignore
}
finally
{
sb.Clear();
}
return(dataScripts);
}
public override string GenerateDataScripts(SchemaInfo schemaInfo)
{
return(base.GenerateDataScripts(schemaInfo));
}
public override async Task <string> GenerateDataScriptsAsync(SchemaInfo schemaInfo)
{
return(await base.GenerateDataScriptsAsync(schemaInfo));
}
public static SchemaInfo Clone(SchemaInfo schemaInfo)
{
SchemaInfo cloneSchemaInfo = (SchemaInfo)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(schemaInfo), typeof(SchemaInfo));
return(cloneSchemaInfo);
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
string tableName = table.Name;
string quotedTableName = this.GetQuotedTableName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.Columns.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.TableName == tableName);
#region Create Table
sb.Append(
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.TranslateColumn(table, item))).TrimEnd(',')}
)
TABLESPACE
{this.ConnectionInfo.Database};");
#endregion
sb.AppendLine();
#region Comment
if (!string.IsNullOrEmpty(table.Comment))
{
sb.AppendLine($"COMMENT ON TABLE {this.ConnectionInfo.UserId}.{GetQuotedString(tableName)} IS '{ValueHelper.TransferSingleQuotation(table.Comment)}';");
}
foreach (TableColumn column in tableColumns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine($"COMMENT ON COLUMN {this.ConnectionInfo.UserId}.{GetQuotedString(tableName)}.{GetQuotedString(column.ColumnName)} IS '{ValueHelper.TransferSingleQuotation(column.Comment)}';");
}
#endregion
#region Primary Key
if (Option.GenerateKey && primaryKeys.Count() > 0)
{
string primaryKey =
$@"
ALTER TABLE {quotedTableName} ADD CONSTRAINT {primaryKeys.FirstOrDefault().KeyName} PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKeys.Select(item => $"{ GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)
USING INDEX
TABLESPACE
{this.ConnectionInfo.Database}
;";
sb.Append(primaryKey);
}
#endregion
#region Foreign Key
if (Option.GenerateKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
if (foreignKeys.Count() > 0)
{
sb.AppendLine();
ILookup <string, TableForeignKey> foreignKeyLookup = foreignKeys.ToLookup(item => item.KeyName);
IEnumerable <string> keyNames = foreignKeyLookup.Select(item => item.Key);
foreach (string keyName in keyNames)
{
TableForeignKey tableForeignKey = foreignKeyLookup[keyName].First();
string columnNames = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{GetQuotedString(item.ColumnName)}"));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{GetQuotedString(item.ReferencedColumnName)}"));
sb.Append(
$@"
ALTER TABLE {quotedTableName} ADD CONSTRAINT { GetQuotedString(keyName)} FOREIGN KEY ({columnNames})
REFERENCES { GetQuotedString(tableForeignKey.ReferencedTableName)}({referenceColumnName})
");
if (tableForeignKey.DeleteCascade)
{
sb.AppendLine("ON DELETE CASCADE");
}
sb.Append(";");
}
}
}
#endregion
#region Index
if (Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndices.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
if (indices.Count() > 0)
{
sb.AppendLine();
List <string> indexColumns = new List <string>();
ILookup <string, TableIndex> indexLookup = indices.ToLookup(item => item.IndexName);
IEnumerable <string> indexNames = indexLookup.Select(item => item.Key);
foreach (string indexName in indexNames)
{
TableIndex tableIndex = indexLookup[indexName].First();
string columnNames = string.Join(",", indexLookup[indexName].Select(item => $"{GetQuotedString(item.ColumnName)}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
sb.AppendLine($"CREATE {(tableIndex.IsUnique ? "UNIQUE" : "")} INDEX { GetQuotedString(tableIndex.IndexName)} ON { GetQuotedString(tableName)} ({columnNames});");
if (!indexColumns.Contains(columnNames))
{
indexColumns.Add(columnNames);
}
}
}
}
#endregion
//#region Default Value
//if (options.GenerateDefaultValue)
//{
// IEnumerable<TableColumn> defaultValueColumns = columns.Where(item => item.TableName == tableName && !string.IsNullOrEmpty(item.DefaultValue));
// foreach (TableColumn column in defaultValueColumns)
// {
// sb.AppendLine($"ALTER TABLE \"{tableName}\" MODIFY \"{column.ColumnName}\" DEFAULT {column.DefaultValue};");
// }
//}
//#endregion
}
#endregion
return(sb.ToString());
}
private async Task InternalConvert(SchemaInfo schemaInfo = null, bool getAllIfNotSpecified = true, bool async = false)
{
DbInterpreter sourceInterpreter = this.Source.DbInterpreter;
sourceInterpreter.Option.TreatBytesAsNullForScript = true;
string[] tableNames = null;
string[] userDefinedTypeNames = null;
string[] viewNames = null;
if (schemaInfo == null || getAllIfNotSpecified)
{
tableNames = sourceInterpreter.GetTables().Select(item => item.Name).ToArray();
userDefinedTypeNames = sourceInterpreter.GetUserDefinedTypes().Select(item => item.Name).ToArray();
viewNames = sourceInterpreter.GetViews().Select(item => item.Name).ToArray();
}
else
{
tableNames = schemaInfo.Tables.Select(t => t.Name).ToArray();
userDefinedTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).ToArray();
viewNames = schemaInfo.Views.Select(item => item.Name).ToArray();
}
SelectionInfo selectionInfo = new SelectionInfo()
{
UserDefinedTypeNames = userDefinedTypeNames,
TableNames = tableNames,
ViewNames = viewNames
};
SchemaInfo sourceSchemaInfo = async? await sourceInterpreter.GetSchemaInfoAsync(selectionInfo, getAllIfNotSpecified):
sourceInterpreter.GetSchemaInfo(selectionInfo, getAllIfNotSpecified);
SchemaInfo targetSchemaInfo = SchemaInfoHelper.Clone(sourceSchemaInfo);
if (!string.IsNullOrEmpty(this.Target.DbOwner))
{
SchemaInfoHelper.TransformOwner(targetSchemaInfo, this.Target.DbOwner);
}
targetSchemaInfo.Columns = ColumnTranslator.Translate(targetSchemaInfo.Columns, this.Source.DbInterpreter.DatabaseType, this.Target.DbInterpreter.DatabaseType);
targetSchemaInfo.Views = ViewTranslator.Translate(targetSchemaInfo.Views, sourceInterpreter, this.Target.DbInterpreter, this.Target.DbOwner);
if (this.Option.EnsurePrimaryKeyNameUnique)
{
SchemaInfoHelper.EnsurePrimaryKeyNameUnique(targetSchemaInfo);
}
if (this.Option.EnsureIndexNameUnique)
{
SchemaInfoHelper.EnsureIndexNameUnique(targetSchemaInfo);
}
DbInterpreter targetInterpreter = this.Target.DbInterpreter;
bool generateIdentity = targetInterpreter.Option.GenerateIdentity;
if (generateIdentity)
{
targetInterpreter.Option.InsertIdentityValue = true;
}
string script = "";
sourceInterpreter.Subscribe(this);
targetInterpreter.Subscribe(this);
if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema))
{
script = targetInterpreter.GenerateSchemaScripts(targetSchemaInfo);
if (string.IsNullOrEmpty(script))
{
throw new Exception($"The script to create schema is null.");
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "Begin to sync schema...");
if (!this.Option.SplitScriptsToExecute)
{
if (targetInterpreter is SqlServerInterpreter)
{
string[] scriptItems = script.Split(new string[] { "GO" + Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
if (async)
{
scriptItems.ToList().ForEach(async item =>
{
await targetInterpreter.ExecuteNonQueryAsync(item);
});
}
else
{
scriptItems.ToList().ForEach(item =>
{
targetInterpreter.ExecuteNonQuery(item);
});
}
}
else
{
if (async)
{
await targetInterpreter.ExecuteNonQueryAsync(script);
}
else
{
targetInterpreter.ExecuteNonQuery(script);
}
}
}
else
{
string[] sqls = script.Split(new char[] { this.Option.ScriptSplitChar }, StringSplitOptions.RemoveEmptyEntries);
int count = sqls.Count();
int i = 0;
foreach (string sql in sqls)
{
if (!string.IsNullOrEmpty(sql.Trim()))
{
i++;
targetInterpreter.Feedback(FeedbackInfoType.Info, $"({i}/{count}), executing {sql}");
targetInterpreter.ExecuteNonQuery(sql.Trim());
}
}
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "End sync schema.");
}
if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Data))
{
List <TableColumn> identityTableColumns = new List <TableColumn>();
if (generateIdentity)
{
identityTableColumns = targetSchemaInfo.Columns.Where(item => item.IsIdentity).ToList();
}
if (this.Option.PickupTable != null)
{
sourceSchemaInfo.PickupTable = this.Option.PickupTable;
}
sourceInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
targetInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
using (DbConnection dbConnection = targetInterpreter.GetDbConnector().CreateConnection())
{
identityTableColumns.ForEach(item =>
{
if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
{
targetInterpreter.SetIdentityEnabled(dbConnection, item, false);
}
});
sourceInterpreter.OnDataRead += async(table, columns, data, dbDataReader) =>
{
try
{
StringBuilder sb = new StringBuilder();
(Table Table, List <TableColumn> Columns)targetTableAndColumns = this.GetTargetTableColumns(targetSchemaInfo, this.Target.DbOwner, table, columns);
Dictionary <string, object> paramters = targetInterpreter.AppendDataScripts(this.Target.DbInterpreter.Option, sb, targetTableAndColumns.Table, targetTableAndColumns.Columns, new Dictionary <long, List <Dictionary <string, object> > >()
{
{ 1, data }
});
try
{
script = sb.ToString();
sb.Clear();
}
catch (OutOfMemoryException e)
{
sb.Clear();
}
if (!this.Option.SplitScriptsToExecute)
{
if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
{
if (async)
{
await targetInterpreter.BulkCopyAsync(dbConnection, dbDataReader, table.Name);
}
else
{
targetInterpreter.BulkCopy(dbConnection, dbDataReader, table.Name);
}
}
else
{
if (async)
{
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, script, paramters, false);
}
else
{
targetInterpreter.ExecuteNonQuery(dbConnection, script, paramters, false);
}
}
}
else
{
string[] sqls = script.Split(new char[] { this.Option.ScriptSplitChar }, StringSplitOptions.RemoveEmptyEntries);
foreach (string sql in sqls)
{
if (!string.IsNullOrEmpty(sql.Trim()))
{
if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
{
if (async)
{
await targetInterpreter.BulkCopyAsync(dbConnection, dbDataReader, table.Name);
}
else
{
targetInterpreter.BulkCopy(dbConnection, dbDataReader, table.Name);
}
}
else
{
if (async)
{
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, sql, paramters, false);
}
else
{
targetInterpreter.ExecuteNonQuery(dbConnection, sql, paramters, false);
}
}
}
}
}
targetInterpreter.FeedbackInfo($"End write data to table {table.Name}, handled rows count:{data.Count}.");
}
catch (Exception ex)
{
ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;
throw new TableDataTransferException(ex)
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
SourceTableName = table.Name,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database,
TargetTableName = table.Name
};
}
};
if (async)
{
await sourceInterpreter.GenerateDataScriptsAsync(sourceSchemaInfo);
}
else
{
sourceInterpreter.GenerateDataScripts(sourceSchemaInfo);
}
identityTableColumns.ForEach(item =>
{
if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
{
targetInterpreter.SetIdentityEnabled(dbConnection, item, true);
}
});
}
}
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
string tableName = table.Name;
string quotedTableName = this.GetQuotedTableName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.Columns.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
string primaryKey = "";
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.TableName == tableName);
#region Primary Key
if (Option.GenerateKey && primaryKeys.Count() > 0)
{
//string primaryKeyName = primaryKeys.First().KeyName;
//if(primaryKeyName=="PRIMARY")
//{
// primaryKeyName = "PK_" + tableName ;
//}
primaryKey =
$@"
,PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKeys.Select(item => $"{GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (Option.GenerateKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
if (foreignKeys.Count() > 0)
{
ILookup <string, TableForeignKey> foreignKeyLookup = foreignKeys.ToLookup(item => item.KeyName);
IEnumerable <string> keyNames = foreignKeyLookup.Select(item => item.Key);
foreach (string keyName in keyNames)
{
TableForeignKey tableForeignKey = foreignKeyLookup[keyName].First();
string columnNames = string.Join(",", foreignKeyLookup[keyName].Select(item => GetQuotedString(item.ColumnName)));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{GetQuotedString(item.ReferencedColumnName)}"));
string line = $"CONSTRAINT {GetQuotedString(keyName)} FOREIGN KEY ({columnNames}) REFERENCES {GetQuotedString(tableForeignKey.ReferencedTableName)}({referenceColumnName})";
if (tableForeignKey.UpdateCascade)
{
line += " ON UPDATE CASCADE";
}
else
{
line += " ON UPDATE NO ACTION";
}
if (tableForeignKey.DeleteCascade)
{
line += " ON DELETE CASCADE";
}
else
{
line += " ON DELETE NO ACTION";
}
foreignKeysLines.Add(line);
}
}
}
#endregion
#region Create Table
sb.Append(
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.TranslateColumn(table, item)))}{primaryKey}
{(foreignKeysLines.Count > 0 ? ("," + string.Join("," + Environment.NewLine, foreignKeysLines)) : "")}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{ValueHelper.TransferSingleQuotation(table.Comment)}'") : "")}
DEFAULT CHARSET={DbCharset};");
#endregion
sb.AppendLine();
#region Index
if (Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndices.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
if (indices.Count() > 0)
{
sb.AppendLine();
List <string> indexColumns = new List <string>();
ILookup <string, TableIndex> indexLookup = indices.ToLookup(item => item.IndexName);
IEnumerable <string> indexNames = indexLookup.Select(item => item.Key);
foreach (string indexName in indexNames)
{
TableIndex tableIndex = indexLookup[indexName].First();
string columnNames = string.Join(",", indexLookup[indexName].Select(item => $"{GetQuotedString(item.ColumnName)}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
var tempIndexName = tableIndex.IndexName;
if (tempIndexName.Contains("-"))
{
tempIndexName = tempIndexName.Replace("-", "_");
}
sb.AppendLine($"ALTER TABLE {quotedTableName} ADD {(tableIndex.IsUnique ? "UNIQUE" : "")} INDEX {tempIndexName} ({columnNames});");
if (!indexColumns.Contains(columnNames))
{
indexColumns.Add(columnNames);
}
}
}
}
#endregion
//#region Default Value
//if (options.GenerateDefaultValue)
//{
// IEnumerable<TableColumn> defaultValueColumns = columns.Where(item => item.Owner== table.Owner && item.TableName == tableName && !string.IsNullOrEmpty(item.DefaultValue));
// foreach (TableColumn column in defaultValueColumns)
// {
// sb.AppendLine($"ALTER TABLE {quotedTableName} ALTER COLUMN {GetQuotedString(column.ColumnName)} SET DEFAULT {column.DefaultValue};");
// }
//}
//#endregion
}
#endregion
return(sb.ToString());
}
public Task ConvertAsync(SchemaInfo schemaInfo = null, bool getAllIfNotSpecified = true)
{
return(this.InternalConvert(schemaInfo, getAllIfNotSpecified, true));
}
public void Convert(SchemaInfo schemaInfo = null, bool getAllIfNotSpecified = true)
{
this.InternalConvert(schemaInfo, getAllIfNotSpecified, false).Wait();
}
private async Task InternalConvert(SchemaInfo schemaInfo = null)
{
DbInterpreter sourceInterpreter = this.Source.DbInterpreter;
sourceInterpreter.Option.TreatBytesAsNullForScript = true;
SelectionInfo selectionInfo = new SelectionInfo();
if (schemaInfo != null)
{
selectionInfo.UserDefinedTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).ToArray();
selectionInfo.TableNames = schemaInfo.Tables.Select(t => t.Name).ToArray();
selectionInfo.ViewNames = schemaInfo.Views.Select(item => item.Name).ToArray();
}
SchemaInfo sourceSchemaInfo = await sourceInterpreter.GetSchemaInfoAsync(selectionInfo);
#region Set data type by user define type
List <UserDefinedType> utypes = await sourceInterpreter.GetUserDefinedTypesAsync();
if (utypes != null && utypes.Count > 0)
{
foreach (TableColumn column in sourceSchemaInfo.TableColumns)
{
UserDefinedType utype = utypes.FirstOrDefault(item => item.Name == column.DataType);
if (utype != null)
{
column.DataType = utype.Type;
column.MaxLength = utype.MaxLength;
}
}
}
#endregion
SchemaInfo targetSchemaInfo = SchemaInfoHelper.Clone(sourceSchemaInfo);
if (!string.IsNullOrEmpty(this.Target.DbOwner))
{
SchemaInfoHelper.TransformOwner(targetSchemaInfo, this.Target.DbOwner);
}
ColumnTranslator columnTranslator = new ColumnTranslator(targetSchemaInfo.TableColumns, this.Source.DbInterpreter.DatabaseType, this.Target.DbInterpreter.DatabaseType);
targetSchemaInfo.TableColumns = columnTranslator.Translate();
ViewTranslator viewTranslator = new ViewTranslator(targetSchemaInfo.Views, sourceInterpreter, this.Target.DbInterpreter, this.Target.DbOwner);
targetSchemaInfo.Views = viewTranslator.Translate();
if (this.Option.EnsurePrimaryKeyNameUnique)
{
SchemaInfoHelper.EnsurePrimaryKeyNameUnique(targetSchemaInfo);
}
if (this.Option.EnsureIndexNameUnique)
{
SchemaInfoHelper.EnsureIndexNameUnique(targetSchemaInfo);
}
DbInterpreter targetInterpreter = this.Target.DbInterpreter;
bool generateIdentity = targetInterpreter.Option.GenerateIdentity;
if (generateIdentity)
{
targetInterpreter.Option.InsertIdentityValue = true;
}
string script = "";
sourceInterpreter.Subscribe(this.observer);
targetInterpreter.Subscribe(this.observer);
DataTransferErrorProfile dataErrorProfile = null;
using (DbConnection dbConnection = targetInterpreter.GetDbConnector().CreateConnection())
{
this.isBusy = true;
if (this.Option.UseTransaction)
{
dbConnection.Open();
this.transaction = dbConnection.BeginTransaction();
}
#region Schema sync
if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema))
{
script = targetInterpreter.GenerateSchemaScripts(targetSchemaInfo);
if (this.Option.ExecuteScriptOnTargetServer)
{
if (string.IsNullOrEmpty(script))
{
this.Feedback(targetInterpreter, $"The script to create schema is empty.", FeedbackInfoType.Error);
return;
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "Begin to sync schema...");
try
{
if (!this.Option.SplitScriptsToExecute)
{
targetInterpreter.Feedback(FeedbackInfoType.Info, script);
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(script, null, this.transaction));
}
else
{
string[] sqls = script.Split(new string[] { targetInterpreter.ScriptsSplitString }, StringSplitOptions.RemoveEmptyEntries);
int count = sqls.Count();
int i = 0;
foreach (string sql in sqls)
{
if (!string.IsNullOrEmpty(sql.Trim()))
{
i++;
if (!targetInterpreter.HasError)
{
targetInterpreter.Feedback(FeedbackInfoType.Info, $"({i}/{count}), executing:{Environment.NewLine} {sql}");
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(sql.Trim(), null, transaction));
}
}
}
}
}
catch (Exception ex)
{
targetInterpreter.CancelRequested = true;
this.Rollback();
ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;
SchemaTransferException schemaTransferException = new SchemaTransferException(ex)
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database
};
this.HandleError(schemaTransferException);
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "End sync schema.");
}
}
#endregion
#region Data sync
if (!targetInterpreter.HasError && this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Data) && sourceSchemaInfo.Tables.Count > 0)
{
List <TableColumn> identityTableColumns = new List <TableColumn>();
if (generateIdentity)
{
identityTableColumns = targetSchemaInfo.TableColumns.Where(item => item.IsIdentity).ToList();
}
if (this.Option.PickupTable)
{
dataErrorProfile = DataTransferErrorProfileManager.GetProfile(sourceInterpreter.ConnectionInfo, targetInterpreter.ConnectionInfo);
if (dataErrorProfile != null)
{
sourceSchemaInfo.PickupTable = new Table()
{
Owner = schemaInfo.Tables.FirstOrDefault()?.Owner, Name = dataErrorProfile.SourceTableName
};
}
}
if (sourceInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
sourceInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
}
if (targetInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
targetInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
}
foreach (var item in identityTableColumns)
{
if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
{
await targetInterpreter.SetIdentityEnabled(dbConnection, item, false);
}
}
if (this.Option.ExecuteScriptOnTargetServer || targetInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
sourceInterpreter.OnDataRead += async(table, columns, data, dataTable) =>
{
if (!this.hasError)
{
try
{
StringBuilder sb = new StringBuilder();
(Table Table, List <TableColumn> Columns)targetTableAndColumns = this.GetTargetTableColumns(targetSchemaInfo, this.Target.DbOwner, table, columns);
if (targetTableAndColumns.Table == null || targetTableAndColumns.Columns == null)
{
return;
}
Dictionary <string, object> paramters = targetInterpreter.AppendDataScripts(sb, targetTableAndColumns.Table, targetTableAndColumns.Columns, new Dictionary <long, List <Dictionary <string, object> > >()
{
{ 1, data }
});
script = sb.ToString().Trim().Trim(';');
if (this.Option.ExecuteScriptOnTargetServer)
{
if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
{
await targetInterpreter.BulkCopyAsync(dbConnection, dataTable, table.Name);
}
else
{
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(script, paramters, this.transaction));
}
targetInterpreter.FeedbackInfo($"Table \"{table.Name}\":{data.Count} records transferred.");
}
}
catch (Exception ex)
{
sourceInterpreter.CancelRequested = true;
this.Rollback();
ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;
DataTransferException dataTransferException = new DataTransferException(ex)
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
SourceObject = table.Name,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database,
TargetObject = table.Name
};
this.HandleError(dataTransferException);
if (!this.Option.UseTransaction)
{
DataTransferErrorProfileManager.Save(new DataTransferErrorProfile
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
SourceTableName = table.Name,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database,
TargetTableName = table.Name
});
}
}
}
};
}
await sourceInterpreter.GenerateDataScriptsAsync(sourceSchemaInfo);
foreach (var item in identityTableColumns)
{
if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
{
await targetInterpreter.SetIdentityEnabled(dbConnection, item, true);
}
}
}
#endregion
if (this.transaction != null && !this.cancelRequested)
{
this.transaction.Commit();
}
this.isBusy = false;
}
if (dataErrorProfile != null && !this.hasError && !this.cancelRequested)
{
DataTransferErrorProfileManager.Remove(dataErrorProfile);
}
}
public Task Convert(SchemaInfo schemaInfo = null)
{
return(this.InternalConvert(schemaInfo));
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region User Defined Type
foreach (UserDefinedType userDefinedType in schemaInfo.UserDefinedTypes)
{
this.FeedbackInfo($"Begin generate user defined type {userDefinedType.Name} script.");
TableColumn column = new TableColumn()
{
DataType = userDefinedType.Type, MaxLength = userDefinedType.MaxLength, Precision = userDefinedType.Precision, Scale = userDefinedType.Scale
};
string dataLength = this.GetColumnDataLength(column);
sb.AppendLine($@"CREATE TYPE {GetQuotedString(userDefinedType.Owner)}.{GetQuotedString(userDefinedType.Name)} FROM {GetQuotedString(userDefinedType.Type)}{(dataLength==""? "": "("+dataLength+")")} {(userDefinedType.IsRequired? "NOT NULL":"NULL")};");
this.FeedbackInfo($"End generate user defined type {userDefinedType.Name} script.");
}
sb.AppendLine("GO");
#endregion
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo($"Begin generate table {table.Name} script.");
string tableName = table.Name;
string quotedTableName = this.GetQuotedTableName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.Columns.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order);
bool hasBigDataType = tableColumns.Any(item => this.IsBigDataType(item));
string primaryKey = "";
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.Owner == table.Owner && item.TableName == tableName);
#region Primary Key
if (Option.GenerateKey && primaryKeys.Count() > 0)
{
primaryKey =
$@"
,CONSTRAINT {GetQuotedString(primaryKeys.First().KeyName)} PRIMARY KEY CLUSTERED
(
{string.Join(Environment.NewLine, primaryKeys.Select(item => $"{GetQuotedString(item.ColumnName)} {(item.IsDesc ? "DESC" : "ASC")},")).TrimEnd(',')}
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]";
}
#endregion
#region Create Table
sb.Append(
$@"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.TranslateColumn(table, item) ))}{primaryKey}
) ON [PRIMARY]{(hasBigDataType ? " TEXTIMAGE_ON [PRIMARY]" : "")}");
#endregion
sb.AppendLine();
#region Comment
if (!string.IsNullOrEmpty(table.Comment))
{
sb.AppendLine($"EXECUTE sp_addextendedproperty N'MS_Description',N'{ValueHelper.TransferSingleQuotation(table.Comment)}',N'SCHEMA',N'{table.Owner}',N'table',N'{tableName}',NULL,NULL;");
}
foreach (TableColumn column in tableColumns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine($"EXECUTE sp_addextendedproperty N'MS_Description',N'{ValueHelper.TransferSingleQuotation(column.Comment)}',N'SCHEMA',N'{table.Owner}',N'table',N'{tableName}',N'column',N'{column.ColumnName}';");
}
#endregion
#region Foreign Key
if (Option.GenerateKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.Owner == table.Owner && item.TableName == tableName);
if (foreignKeys.Count() > 0)
{
ILookup <string, TableForeignKey> foreignKeyLookup = foreignKeys.ToLookup(item => item.KeyName);
IEnumerable <string> keyNames = foreignKeyLookup.Select(item => item.Key);
foreach (string keyName in keyNames)
{
TableForeignKey tableForeignKey = foreignKeyLookup[keyName].First();
string columnNames = string.Join(",", foreignKeyLookup[keyName].Select(item => $"[{item.ColumnName}]"));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"[{item.ReferencedColumnName}]"));
sb.Append(
$@"
ALTER TABLE {quotedTableName} WITH CHECK ADD CONSTRAINT [{keyName}] FOREIGN KEY({columnNames})
REFERENCES {GetQuotedString(table.Owner)}.{GetQuotedString(tableForeignKey.ReferencedTableName)} ({referenceColumnName})
");
if (tableForeignKey.UpdateCascade)
{
sb.AppendLine("ON UPDATE CASCADE");
}
if (tableForeignKey.DeleteCascade)
{
sb.AppendLine("ON DELETE CASCADE");
}
sb.AppendLine($"ALTER TABLE {quotedTableName} CHECK CONSTRAINT [{keyName}];");
}
}
}
#endregion
#region Index
if (Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndices.Where(item => item.Owner == table.Owner && item.TableName == tableName).OrderBy(item => item.Order);
if (indices.Count() > 0)
{
sb.AppendLine();
List <string> indexColumns = new List <string>();
ILookup <string, TableIndex> indexLookup = indices.ToLookup(item => item.IndexName);
IEnumerable <string> indexNames = indexLookup.Select(item => item.Key);
foreach (string indexName in indexNames)
{
TableIndex tableIndex = indexLookup[indexName].First();
string columnNames = string.Join(",", indexLookup[indexName].Select(item => $"{GetQuotedString(item.ColumnName)} {(item.IsDesc ? "DESC" : "ASC")}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
sb.AppendLine($"CREATE {(tableIndex.IsUnique ? "UNIQUE" : "")} INDEX {tableIndex.IndexName} ON {quotedTableName}({columnNames});");
if (!indexColumns.Contains(columnNames))
{
indexColumns.Add(columnNames);
}
}
}
}
#endregion
#region Default Value
if (Option.GenerateDefaultValue)
{
IEnumerable <TableColumn> defaultValueColumns = schemaInfo.Columns.Where(item => item.Owner == table.Owner && item.TableName == tableName && !string.IsNullOrEmpty(item.DefaultValue));
foreach (TableColumn column in defaultValueColumns)
{
sb.AppendLine($"ALTER TABLE {quotedTableName} ADD CONSTRAINT {GetQuotedString($" DF_{tableName}_{column.ColumnName}")} DEFAULT {column.DefaultValue} FOR [{column.ColumnName}];");
}
}
#endregion
this.FeedbackInfo($"End generate table {table.Name} script.");
}
if (Option.ScriptOutputMode == GenerateScriptOutputMode.WriteToFile)
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb.ToString());
}