public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, "table", table.Name);
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(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
this.FeedbackInfo(OperationState.End, "table", table.Name);
}
#endregion
#region View
foreach (View view in schemaInfo.Views)
{
this.FeedbackInfo(OperationState.Begin, "view", view.Name);
string viewName = view.Name;
string quotedTableName = this.GetQuotedObjectName(view);
sb.AppendLine();
sb.Append(view.Definition.TrimEnd(';') + ";");
this.FeedbackInfo(OperationState.End, "view", view.Name);
}
#endregion
return(sb.ToString());
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, "table", table.Name);
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(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
this.FeedbackInfo(OperationState.End, "table", table.Name);
}
#endregion
#region View
foreach (View view in schemaInfo.Views)
{
this.FeedbackInfo(OperationState.Begin, "view", view.Name);
string viewName = view.Name;
string quotedTableName = this.GetQuotedObjectName(view);
sb.AppendLine();
sb.Append(view.Definition);
sb.Append(";");
this.FeedbackInfo(OperationState.End, "view", view.Name);
}
#endregion
return(sb.ToString());
}
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());
}
