public string TransferSingleQuotationString(string comment)
{
if (string.IsNullOrEmpty(comment))
{
return(comment);
}
return(ValueHelper.TransferSingleQuotation(comment));
}
public override Script SetTableComment(Table table, bool isNew = true)
{
return(new AlterDbObjectScript <Table>($"ALTER TABLE {this.GetQuotedString(table.Name)} COMMENT = '{this.dbInterpreter.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(table.Comment))}';"));
}
public override ScriptBuilder GenerateSchemaScripts(SchemaInfo schemaInfo)
{
ScriptBuilder sb = new ScriptBuilder();
MySqlInterpreter mySqlInterpreter = this.dbInterpreter as MySqlInterpreter;
string dbCharSet = mySqlInterpreter.DbCharset;
string notCreateIfExistsClause = mySqlInterpreter.NotCreateIfExistsClause;
#region Function
sb.AppendRange(this.GenerateScriptDbObjectScripts <Function>(schemaInfo.Functions));
#endregion
#region Create Table
foreach (Table table in schemaInfo.Tables)
{
this.FeedbackInfo(OperationState.Begin, table);
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
IEnumerable <TableColumn> tableColumns = schemaInfo.TableColumns.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.TableName == tableName);
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
IEnumerable <TableIndex> indexes = schemaInfo.TableIndexes.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
this.RestrictColumnLength(tableColumns, primaryKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(tableColumns, foreignKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(tableColumns, indexes.SelectMany(item => item.Columns));
string primaryKeyColumns = "";
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
{
TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
primaryKeyColumns =
$@"
,PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKey.Columns.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#region Table
string tableScript =
$@"
CREATE TABLE {notCreateIfExistsClause} {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}{primaryKeyColumns}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{this.dbInterpreter.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(table.Comment))}'") : "")}
DEFAULT CHARSET={dbCharSet}" + this.scriptsDelimiter;
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
//#region Primary Key
//if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
//{
// TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
// if (primaryKey != null)
// {
// sb.AppendLine(this.AddPrimaryKey(primaryKey));
// }
//}
//#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
}
}
#endregion
sb.AppendLine();
this.FeedbackInfo(OperationState.End, table);
}
#endregion
#region View
sb.AppendRange(this.GenerateScriptDbObjectScripts <View>(schemaInfo.Views));
#endregion
#region Trigger
sb.AppendRange(this.GenerateScriptDbObjectScripts <TableTrigger>(schemaInfo.TableTriggers));
#endregion
#region Procedure
sb.AppendRange(this.GenerateScriptDbObjectScripts <Procedure>(schemaInfo.Procedures));
#endregion
if (this.option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb);
}
private object ParseValue(TableColumn column, object value, bool bytesAsString = false)
{
if (value != null)
{
Type type = value.GetType();
bool needQuotated = false;
string strValue = "";
if (type == typeof(DBNull))
{
return("NULL");
}
else if (type == typeof(Byte[]))
{
if (((Byte[])value).Length == 16) //GUID
{
string str = ValueHelper.ConvertGuidBytesToString((Byte[])value, this.databaseType, column.DataType, column.MaxLength, bytesAsString);
if (!string.IsNullOrEmpty(str))
{
needQuotated = true;
strValue = str;
}
else
{
return(value);
}
}
else
{
return(value);
}
}
bool oracleSemicolon = false;
switch (type.Name)
{
case nameof(Guid):
needQuotated = true;
if (this.databaseType == DatabaseType.Oracle && column.DataType.ToLower() == "raw" && column.MaxLength == 16)
{
strValue = StringHelper.GuidToRaw(value.ToString());
}
else
{
strValue = value.ToString();
}
break;
case nameof(String):
needQuotated = true;
strValue = value.ToString();
if (this.databaseType == DatabaseType.Oracle)
{
if (strValue.Contains(";"))
{
oracleSemicolon = true;
}
}
break;
case nameof(DateTime):
case nameof(DateTimeOffset):
case nameof(MySql.Data.Types.MySqlDateTime):
if (this.databaseType == DatabaseType.Oracle)
{
if (type.Name == nameof(MySql.Data.Types.MySqlDateTime))
{
DateTime dateTime = ((MySql.Data.Types.MySqlDateTime)value).GetDateTime();
strValue = this.GetOracleDatetimeConvertString(dateTime);
}
else if (type.Name == nameof(DateTime))
{
DateTime dateTime = Convert.ToDateTime(value);
strValue = this.GetOracleDatetimeConvertString(dateTime);
}
else if (type.Name == nameof(DateTimeOffset))
{
DateTimeOffset dtOffset = DateTimeOffset.Parse(value.ToString());
int millisecondLength = dtOffset.Millisecond.ToString().Length;
string strMillisecond = millisecondLength == 0 ? "" : $".{"f".PadLeft(millisecondLength, 'f')}";
string format = $"yyyy-MM-dd HH:mm:ss{strMillisecond}";
string strDtOffset = dtOffset.ToString(format) + $"{dtOffset.Offset.Hours}:{dtOffset.Offset.Minutes}";
strValue = $@"TO_TIMESTAMP_TZ('{strDtOffset}','yyyy-MM-dd HH24:MI:ssxff TZH:TZM')";
}
}
else if (this.databaseType == DatabaseType.MySql)
{
if (type.Name == nameof(DateTimeOffset))
{
DateTimeOffset dtOffset = DateTimeOffset.Parse(value.ToString());
strValue = $"'{dtOffset.DateTime.Add(dtOffset.Offset).ToString("yyyy-MM-dd HH:mm:ss.ffffff")}'";
}
}
if (string.IsNullOrEmpty(strValue))
{
needQuotated = true;
strValue = value.ToString();
}
break;
case nameof(Boolean):
strValue = value.ToString() == "True" ? "1" : "0";
break;
case nameof(TimeSpan):
if (this.databaseType == DatabaseType.Oracle)
{
return(value);
}
else
{
needQuotated = true;
if (column.DataType.IndexOf("datetime", StringComparison.OrdinalIgnoreCase) >= 0)
{
DateTime dateTime = this.dbInterpreter.MinDateTime.AddSeconds(TimeSpan.Parse(value.ToString()).TotalSeconds);
strValue = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
strValue = value.ToString();
}
}
break;
case "SqlHierarchyId":
case "SqlGeography":
case "SqlGeometry":
needQuotated = true;
strValue = value.ToString();
break;
default:
if (string.IsNullOrEmpty(strValue))
{
strValue = value.ToString();
}
break;
}
if (needQuotated)
{
strValue = $"{this.dbInterpreter.UnicodeInsertChar}'{ValueHelper.TransferSingleQuotation(strValue)}'";
if (oracleSemicolon)
{
strValue = strValue.Replace(";", $"'{OracleInterpreter.CONNECT_CHAR}{OracleInterpreter.SEMICOLON_FUNC}{OracleInterpreter.CONNECT_CHAR}'");
}
return(strValue);
}
else
{
return(strValue);
}
}
else
{
return(null);
}
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region User Defined Type
foreach (UserDefinedType userDefinedType in schemaInfo.UserDefinedTypes)
{
this.FeedbackInfo(OperationState.Begin, "user defined type", userDefinedType.Name);
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 {this.GetQuotedString(userDefinedType.Owner)}.{this.GetQuotedString(userDefinedType.Name)} FROM {this.GetQuotedString(userDefinedType.Type)}{(dataLength == "" ? "" : "(" + dataLength + ")")} {(userDefinedType.IsRequired ? "NOT NULL" : "NULL")};");
sb.Append(this.ScriptsSplitString);
this.FeedbackInfo(OperationState.End, "user defined type", userDefinedType.Name);
}
if (sb.Length > 0)
{
sb.Append(this.ScriptsSplitString);
}
#endregion
#region 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.TableColumns.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 (this.Option.GenerateKey && primaryKeys.Count() > 0)
{
primaryKey =
$@"
,CONSTRAINT {this.GetQuotedString(primaryKeys.First().Name)} PRIMARY KEY CLUSTERED
(
{string.Join(Environment.NewLine, primaryKeys.Select(item => $"{this.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.AppendLine(
$@"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.ParseColumn(table, item)))}{primaryKey}
) ON [PRIMARY]{(hasBigDataType ? " TEXTIMAGE_ON [PRIMARY]" : "")}" + this.ScriptsSplitString);
#endregion
#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.Name}';");
}
#endregion
#region Foreign Key
if (this.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.Name);
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 => $"{ this.GetQuotedString(item.ColumnName)}"));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{ this.GetQuotedString(item.ReferencedColumnName)}"));
sb.AppendLine(
$@"
ALTER TABLE {quotedTableName} WITH CHECK ADD CONSTRAINT { this.GetQuotedString(keyName)} FOREIGN KEY({columnNames})
REFERENCES {this.GetQuotedString(table.Owner)}.{this.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 { this.GetQuotedString(keyName)};");
}
}
}
#endregion
#region Index
if (this.Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndexes.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.Name);
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 => $"{this.GetQuotedString(item.ColumnName)} {(item.IsDesc ? "DESC" : "ASC")}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
sb.AppendLine($"CREATE {(tableIndex.IsUnique ? "UNIQUE" : "")} INDEX {tableIndex.Name} ON {quotedTableName}({columnNames});");
if (!indexColumns.Contains(columnNames))
{
indexColumns.Add(columnNames);
}
}
}
}
#endregion
#region Default Value
if (this.Option.GenerateDefaultValue)
{
IEnumerable <TableColumn> defaultValueColumns = schemaInfo.TableColumns.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 {this.GetQuotedString($" DF_{tableName}_{column.Name}")} DEFAULT {this.GetColumnDefaultValue(column)} FOR { this.GetQuotedString(column.Name)};");
}
}
#endregion
sb.Append(this.ScriptsSplitString);
this.FeedbackInfo(OperationState.End, "table", table.Name);
}
#endregion
if (sb.Length > 0)
{
sb.Append(this.ScriptsSplitString);
}
#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.AppendLine(view.Definition);
sb.Append(this.ScriptsSplitString);
this.FeedbackInfo(OperationState.End, "view", view.Name);
}
#endregion
if (this.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb.ToString());
}
public override string ParseColumn(Table table, TableColumn column)
{
string dataType = this.ParseDataType(column);
bool isChar = DataTypeHelper.IsCharType(dataType.ToLower());
if (isChar || DataTypeHelper.IsTextType(dataType.ToLower()))
{
dataType += $" CHARACTER SET {DbCharset} COLLATE {DbCharsetCollation} ";
}
if (column.IsComputed)
{
string computeExpression = this.GetColumnComputeExpression(column);
return($"{ this.GetQuotedString(column.Name)} {dataType} AS {computeExpression}");
}
else
{
string requiredClause = (column.IsRequired ? "NOT NULL" : "NULL");
string identityClause = (this.Option.TableScriptsGenerateOption.GenerateIdentity && column.IsIdentity ? $"AUTO_INCREMENT" : "");
string commentClause = (!string.IsNullOrEmpty(column.Comment) ? $"COMMENT '{this.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(column.Comment))}'" : "");
string defaultValueClause = this.Option.TableScriptsGenerateOption.GenerateDefaultValue && !string.IsNullOrEmpty(column.DefaultValue) ? (" DEFAULT " + this.GetColumnDefaultValue(column)) : "";
string scriptComment = string.IsNullOrEmpty(column.ScriptComment) ? "" : $"/*{column.ScriptComment}*/";
return($"{this.GetQuotedString(column.Name)} {dataType} {requiredClause} {identityClause}{defaultValueClause} {scriptComment}{commentClause}");
}
}
public override ScriptBuilder AddTable(Table table, IEnumerable <TableColumn> columns,
TablePrimaryKey primaryKey,
IEnumerable <TableForeignKey> foreignKeys,
IEnumerable <TableIndex> indexes,
IEnumerable <TableConstraint> constraints)
{
ScriptBuilder sb = new ScriptBuilder();
MySqlInterpreter mySqlInterpreter = this.dbInterpreter as MySqlInterpreter;
string dbCharSet = mySqlInterpreter.DbCharset;
string notCreateIfExistsClause = mySqlInterpreter.NotCreateIfExistsClause;
string tableName = table.Name;
string quotedTableName = this.GetQuotedObjectName(table);
this.RestrictColumnLength(columns, primaryKey?.Columns);
this.RestrictColumnLength(columns, foreignKeys.SelectMany(item => item.Columns));
this.RestrictColumnLength(columns, indexes.SelectMany(item => item.Columns));
string primaryKeyColumns = "";
if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKey != null)
{
primaryKeyColumns =
$@"
,PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKey.Columns.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#region Table
string tableScript =
$@"
CREATE TABLE {notCreateIfExistsClause} {quotedTableName}(
{string.Join("," + Environment.NewLine, columns.Select(item => this.dbInterpreter.ParseColumn(table, item)))}{primaryKeyColumns}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{this.dbInterpreter.ReplaceSplitChar(ValueHelper.TransferSingleQuotation(table.Comment))}'") : "")}
DEFAULT CHARSET={dbCharSet}" + this.scriptsDelimiter;
sb.AppendLine(new CreateDbObjectScript <Table>(tableScript));
#endregion
//#region Primary Key
//if (this.option.TableScriptsGenerateOption.GeneratePrimaryKey && primaryKeys.Count() > 0)
//{
// TablePrimaryKey primaryKey = primaryKeys.FirstOrDefault();
// if (primaryKey != null)
// {
// sb.AppendLine(this.AddPrimaryKey(primaryKey));
// }
//}
//#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (this.option.TableScriptsGenerateOption.GenerateForeignKey)
{
foreach (TableForeignKey foreignKey in foreignKeys)
{
sb.AppendLine(this.AddForeignKey(foreignKey));
}
}
#endregion
#region Index
if (this.option.TableScriptsGenerateOption.GenerateIndex)
{
foreach (TableIndex index in indexes)
{
sb.AppendLine(this.AddIndex(index));
}
}
#endregion
sb.AppendLine();
return(sb);
}
private object ParseValue(TableColumn column, object value, bool byteAsString = false)
{
if (value != null)
{
Type type = value.GetType();
bool needQuotated = false;
string strValue = "";
if (type == typeof(DBNull))
{
return("NULL");
}
else if (type == typeof(Byte[]))
{
if (((Byte[])value).Length == 16) //GUID
{
if (this.GetType() == typeof(SqlServerInterpreter) && column.DataType.ToLower() == "uniqueidentifier")
{
needQuotated = true;
strValue = new Guid((byte[])value).ToString();
}
else if (this.GetType() == typeof(MySqlInterpreter) && column.DataType == "char" && column.MaxLength == 36)
{
needQuotated = true;
strValue = new Guid((byte[])value).ToString();
}
else if (byteAsString && this.GetType() == typeof(OracleInterpreter) && column.DataType.ToLower() == "raw" && column.MaxLength == 16)
{
needQuotated = true;
strValue = StringHelper.GuidToRaw(new Guid((byte[])value).ToString());
}
else
{
return(value);
}
}
else
{
return(value);
}
}
bool oracleSemicolon = false;
switch (type.Name)
{
case nameof(Guid):
needQuotated = true;
if (this.GetType() == typeof(OracleInterpreter) && column.DataType.ToLower() == "raw" && column.MaxLength == 16)
{
strValue = StringHelper.GuidToRaw(value.ToString());
}
else
{
strValue = value.ToString();
}
break;
case nameof(String):
needQuotated = true;
strValue = value.ToString();
if (this.GetType() == typeof(OracleInterpreter))
{
if (strValue.Contains(";"))
{
oracleSemicolon = true;
}
}
break;
case nameof(DateTime):
if (this.GetType() == typeof(OracleInterpreter))
{
strValue = $"TO_TIMESTAMP('{Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.fff")}','yyyy-MM-dd hh24:mi:ss.ff')";
}
else
{
needQuotated = true;
strValue = value.ToString();
}
break;
case nameof(Boolean):
strValue = value.ToString() == "True" ? "1" : "0";
break;
default:
if (string.IsNullOrEmpty(strValue))
{
strValue = value.ToString();
}
break;
}
if (needQuotated)
{
strValue = $"{this.UnicodeInsertChar}'{ValueHelper.TransferSingleQuotation(strValue)}'";
if (oracleSemicolon)
{
strValue = strValue.Replace(";", $"'{OracleInterpreter.CONNECT_CHAR}{OracleInterpreter.SEMICOLON_FUNC}{OracleInterpreter.CONNECT_CHAR}'");
}
return(strValue);
}
else
{
return(strValue);
}
}
else
{
return(null);
}
}
public override string ParseColumn(Table table, TableColumn column)
{
string dataType = column.DataType;
bool isChar = DataTypeHelper.IsCharType(dataType.ToLower());
if (column.DataType.IndexOf("(") < 0)
{
if (isChar)
{
dataType = $"{dataType}({column.MaxLength.ToString()})";
}
else if (!this.IsNoLengthDataType(dataType))
{
long precision = column.Precision.HasValue ? column.Precision.Value : column.MaxLength.Value;
int scale = column.Scale.HasValue ? column.Scale.Value : 0;
dataType = $"{dataType}({precision},{scale})";
}
if (isChar || DataTypeHelper.IsTextType(dataType.ToLower()))
{
dataType += $" CHARACTER SET {DbCharset} COLLATE {DbCharsetCollation} ";
}
}
return($@"{this.GetQuotedString(column.Name)} {dataType} {(column.IsRequired ? "NOT NULL" : "NULL")} {(this.Option.GenerateIdentity && column.IsIdentity ? $"AUTO_INCREMENT" : "")} {(string.IsNullOrEmpty(column.DefaultValue) ? "" : " DEFAULT " + this.GetColumnDefaultValue(column))} {(!string.IsNullOrEmpty(column.Comment) ? $"comment '{ValueHelper.TransferSingleQuotation(column.Comment)}'" : "")}");
}
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.TableColumns.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 (this.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 => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)";
}
#endregion
List <string> foreignKeysLines = new List <string>();
#region Foreign Key
if (this.Option.GenerateKey)
{
IEnumerable <TableForeignKey> foreignKeys = schemaInfo.TableForeignKeys.Where(item => item.TableName == tableName);
if (foreignKeys.Count() > 0)
{
ILookup <string, TableForeignKey> foreignKeyLookup = foreignKeys.ToLookup(item => item.Name);
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 => this.GetQuotedString(item.ColumnName)));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{ this.GetQuotedString(item.ReferencedColumnName)}"));
string line = $"CONSTRAINT { this.GetQuotedString(keyName)} FOREIGN KEY ({columnNames}) REFERENCES { this.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);
}
}
}
string foreignKey = $"{(foreignKeysLines.Count > 0 ? (Environment.NewLine + "," + string.Join("," + Environment.NewLine, foreignKeysLines)) : "")}";
#endregion
#region Table
sb.AppendLine(
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.ParseColumn(table, item)))}{primaryKey}{foreignKey}
){(!string.IsNullOrEmpty(table.Comment) ? ($"comment='{ValueHelper.TransferSingleQuotation(table.Comment)}'") : "")}
DEFAULT CHARSET={DbCharset}" + this.ScriptsSplitString);
#endregion
#region Index
if (this.Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndexes.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.Name);
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 => $"{this.GetQuotedString(item.ColumnName)}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
var tempIndexName = tableIndex.Name;
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 {this.GetQuotedString(column.ColumnName)} SET DEFAULT {column.DefaultValue};");
// }
//}
//#endregion
this.FeedbackInfo(OperationState.End, "table", table.Name);
}
#endregion
if (sb.Length > 0)
{
sb.AppendLine();
}
#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(view.Definition + this.ScriptsSplitString);
sb.AppendLine();
this.FeedbackInfo(OperationState.End, "view", view.Name);
}
#endregion
if (this.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb.ToString());
}
public override string GenerateSchemaScripts(SchemaInfo schemaInfo)
{
StringBuilder sb = new StringBuilder();
#region 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.TableColumns.Where(item => item.TableName == tableName).OrderBy(item => item.Order);
IEnumerable <TablePrimaryKey> primaryKeys = schemaInfo.TablePrimaryKeys.Where(item => item.TableName == tableName);
#region Create Table
sb.AppendLine(
$@"
CREATE TABLE {quotedTableName}(
{string.Join("," + Environment.NewLine, tableColumns.Select(item => this.ParseColumn(table, item))).TrimEnd(',')}
)
TABLESPACE
{this.ConnectionInfo.Database}" + this.ScriptsSplitString);
#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)}'" + this.ScriptsSplitString);
}
foreach (TableColumn column in tableColumns.Where(item => !string.IsNullOrEmpty(item.Comment)))
{
sb.AppendLine($"COMMENT ON COLUMN {this.ConnectionInfo.UserId}.{GetQuotedString(tableName)}.{GetQuotedString(column.Name)} IS '{ValueHelper.TransferSingleQuotation(column.Comment)}'" + this.ScriptsSplitString);
}
#endregion
#region Primary Key
if (this.Option.GenerateKey && primaryKeys.Count() > 0)
{
string primaryKey =
$@"
ALTER TABLE {quotedTableName} ADD CONSTRAINT {this.GetQuotedString(primaryKeys.FirstOrDefault().Name)} PRIMARY KEY
(
{string.Join(Environment.NewLine, primaryKeys.Select(item => $"{ this.GetQuotedString(item.ColumnName)},")).TrimEnd(',')}
)
USING INDEX
TABLESPACE
{this.ConnectionInfo.Database}{this.ScriptsSplitString}";
sb.AppendLine(primaryKey);
}
#endregion
#region Foreign Key
if (this.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.Name);
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 => $"{ this.GetQuotedString(item.ColumnName)}"));
string referenceColumnName = string.Join(",", foreignKeyLookup[keyName].Select(item => $"{ this.GetQuotedString(item.ReferencedColumnName)}"));
sb.AppendLine(
$@"
ALTER TABLE {quotedTableName} ADD CONSTRAINT { this.GetQuotedString(keyName)} FOREIGN KEY ({columnNames})
REFERENCES { this.GetQuotedString(tableForeignKey.ReferencedTableName)}({referenceColumnName})");
if (tableForeignKey.DeleteCascade)
{
sb.AppendLine("ON DELETE CASCADE");
}
sb.Append(this.ScriptsSplitString);
}
}
}
#endregion
#region Index
if (this.Option.GenerateIndex)
{
IEnumerable <TableIndex> indices = schemaInfo.TableIndexes.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.Name);
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 => $"{ this.GetQuotedString(item.ColumnName)}"));
if (indexColumns.Contains(columnNames))
{
continue;
}
sb.AppendLine($"CREATE {(tableIndex.IsUnique ? "UNIQUE" : "")} INDEX { this.GetQuotedString(tableIndex.Name)} ON { this.GetQuotedString(tableName)} ({columnNames})" + this.ScriptsSplitString);
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(view.Definition);
sb.Append(this.ScriptsSplitString);
sb.AppendLine();
this.FeedbackInfo(OperationState.End, "view", view.Name);
}
#endregion
if (this.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
this.AppendScriptsToFile(sb.ToString(), GenerateScriptMode.Schema, true);
}
return(sb.ToString());
}
