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()); }