/// <summary> /// Gets a list of statements required to drop the database table field. /// </summary> /// <param name="schema">the field schema to drop</param> /// <param name="dbName">the database to drop the field for</param> /// <param name="tblName">the database table to drop the field for</param> internal static List <string> GetDropFieldStatements(this DbFieldSchema schema, string dbName, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (tblName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } return(new List <string>() { string.Format("ALTER TABLE `{0}`.`{1}` DROP COLUMN {2};", dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent)) }); }
/// <summary> /// Gets a list of statements required to add a new database table using the schema specified. /// </summary> /// <param name="schema">a canonical schema of the new database table</param> /// <param name="dbName">the database to add the table for</param> /// <param name="engine">the SQL engine to use for the table</param> /// <param name="charset">the default charset for the table</param> internal static List <string> GetCreateTableStatements(this DbTableSchema schema, string dbName, string engine, string charset, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } var lines = schema.Fields.Select(field => field.GetFieldDefinition(agent)).ToList(); lines.AddRange(from field in schema.Fields where field.IndexType == DbIndexType.Simple || field.IndexType == DbIndexType.Unique || field.IndexType == DbIndexType.ForeignKey select field.GetIndexDefinition(agent)); lines.AddRange(from field in schema.Fields where field.IndexType.IsForeignKey() select field.GetForeignKeyDefinition(agent)); return(new List <string>() { string.Format("CREATE TABLE {0}.{1}({2}) ENGINE={3} DEFAULT CHARSET={4};", dbName.Trim(), schema.Name.ToConventional(agent), string.Join(", ", lines.ToArray()), engine.Trim(), charset.Trim()) }); }
/// <summary> /// Gets the mysql native index schema definition. /// </summary> /// <param name="schema">the canonical field schema to translate</param> /// <remarks>MySql creates an index for each foreign key.</remarks> internal static string GetIndexDefinition(this DbFieldSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } // no backing index for primary key if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary || schema.IndexType == DbIndexType.ForeignPrimary) { return(string.Empty); } if (schema.IndexType == DbIndexType.Unique) { return(string.Format("UNIQUE KEY `{0}` (`{1}`)", schema.IndexName.ToConventional(agent), schema.Name.ToConventional(agent))); } return(string.Format("KEY `{0}` (`{1}`)", schema.IndexName.ToConventional(agent), schema.Name.ToConventional(agent))); }
/// <summary> /// Gets a list of statements required to add a new database table using the schema specified. /// </summary> /// <param name="schema">a canonical schema of the new database table</param> internal static List <string> GetCreateTableStatements(this DbTableSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } var lines = schema.Fields.Select(field => field.GetFieldDefinition(false, agent)).ToArray(); var result = new List <string>() { string.Format("CREATE TABLE {0}({1});", schema.Name.ToConventional(agent), string.Join(", ", lines)) }; foreach (var field in schema.Fields) { if (field.IndexType == DbIndexType.Simple || field.IndexType == DbIndexType.Unique) { result.AddRange(field.GetAddIndexStatements(schema.Name, agent)); } } return(result); }
/// <summary> /// Gets a list of statements required to alter the database table field schema /// to match the specified gauge schema.(does not fixes indexes) /// </summary> /// <param name="schema">the gauge field schema to apply</param> /// <param name="dbName">the database to alter the field for</param> /// <param name="tblName">the database table to alter the field for</param> internal static List <string> GetAlterFieldStatements(this DbFieldSchema schema, string dbName, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (tblName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } return(new List <string>() { string.Format("ALTER TABLE `{0}`.`{1}` MODIFY COLUMN {2};", dbName.Trim(), tblName.ToConventional(agent), schema.GetFieldDefinition(agent).Replace("PRIMARY KEY", "")) }); }
/// <summary> /// Gets a list of statements required to add a new database table field using the field schema specified. /// (also fixes datetime defaults and adds index if required) /// </summary> /// <param name="schema">a canonical schema of the new database table field</param> /// <param name="tblName">the database table to add the field for</param> internal static List <string> GetAddFieldStatements(this DbFieldSchema schema, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (tblName.IsNullOrWhiteSpace()) { throw new ArgumentNullException(nameof(tblName)); } var alterStatement = string.Format("ALTER TABLE {0} ADD COLUMN {1};", tblName.ToConventional(agent), schema.GetFieldDefinition(true, agent)); var result = new List <string>() { alterStatement }; result.AddRange(schema.GetAddIndexStatements(tblName, agent)); return(result); }
/// <summary> /// Gets the SQLite native field schema definition. /// </summary> /// <param name="schema">the canonical field schema to translate</param> /// <param name="addSafe">whether the definition should be safe for add field, /// i.e. dto add default value for not null fields</param> internal static string GetFieldDefinition(this DbFieldSchema schema, bool addSafe, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (schema.Autoincrement) { schema.DataType = DbDataType.Integer; } var result = string.Format("{0} {1}", schema.Name.ToConventional(agent), schema.GetNativeDataType()); if ((schema.DataType == DbDataType.Char || schema.DataType == DbDataType.VarChar) && schema.Length > 0) { result = string.Format("{0}({1})", result, schema.Length.ToString(CultureInfo.InvariantCulture)); } if (schema.NotNull) { if (addSafe) { result = string.Format("{0} DEFAULT {1} NOT NULL", result, GetDefaultValueForFieldType(schema.DataType, schema.EnumValues)); } else { result = string.Format("{0} NOT NULL", result); } } if (schema.IndexType == DbIndexType.Primary) { if (schema.Autoincrement) { result = result + " PRIMARY KEY AUTOINCREMENT"; } else { result = result + " PRIMARY KEY"; } } if (schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary) { result = result + string.Format(" REFERENCES {0}({1}) ON UPDATE {2} ON DELETE {3}", schema.RefTable.ToConventional(agent), schema.RefField.ToConventional(agent), schema.OnUpdateForeignKey.GetNativeActionType(), schema.OnDeleteForeignKey.GetNativeActionType()); } return(result); }
/// <summary> /// Gets a list of statements required to add a new index using the field schema specified. /// (also adds a foreign key if required) /// </summary> /// <param name="schema">a canonical database table field schema to apply</param> /// <param name="dbName">the database to add the index for</param> /// <param name="tblName">the database table to add the index for</param> internal static List <string> GetAddIndexStatements(this DbFieldSchema schema, string dbName, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (tblName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary) { return(new List <string>()); } string result; if (schema.IndexType.IsForeignKey()) { result = string.Format("ALTER TABLE `{0}`.`{1}` ADD CONSTRAINT `{2}` FOREIGN KEY `{2}`(`{3}`) REFERENCES `{4}`(`{5}`) ON DELETE {6} ON UPDATE {7};", dbName.Trim(), tblName.ToConventional(agent), schema.IndexName.ToConventional(agent), schema.Name.ToConventional(agent), schema.RefTable.ToConventional(agent), schema.RefField.ToConventional(agent), schema.OnDeleteForeignKey.GetNativeActionType(), schema.OnUpdateForeignKey.GetNativeActionType()); } else { var uniqueStr = ""; if (schema.IndexType == DbIndexType.Unique) { uniqueStr = "UNIQUE "; } result = string.Format("CREATE {0}INDEX `{1}` ON `{2}`.`{3}` (`{4}`);", uniqueStr, schema.IndexName.ToConventional(agent), dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent)); } return(new List <string>() { result }); }
/// <summary> /// Gets a list of statements required to drop the database table. /// </summary> /// <param name="schema">the table schema to drop</param> internal static List <string> GetDropTableStatements(this DbTableSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } return(new List <string>() { string.Format("DROP TABLE {0};", schema.Name.ToConventional(agent)) }); }
/// <summary> /// Gets a list of statements required to add a new database table field using the field schema specified. /// (also fixes datetime defaults and adds index if required) /// </summary> /// <param name="schema">a canonical schema of the new database table field</param> /// <param name="dbName">the database to add the field for</param> /// <param name="tblName">the database table to add the field for</param> internal static List <string> GetAddFieldStatements(this DbFieldSchema schema, string dbName, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (tblName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } var result = new List <string>() { string.Format("ALTER TABLE `{0}`.`{1}` ADD COLUMN {2};", dbName.Trim(), tblName.ToConventional(agent), schema.GetFieldDefinition(agent)) }; if (schema.NotNull && (schema.DataType == DbDataType.Date)) { result.Add(string.Format("UPDATE `{0}`.`{1}` SET {2}='{3}';", dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent), DateTime.UtcNow.ToString("yyyy'-'MM'-'dd"))); } if (schema.NotNull && (schema.DataType == DbDataType.Time || schema.DataType == DbDataType.DateTime || schema.DataType == DbDataType.TimeStamp)) { result.Add(string.Format("UPDATE `{0}`.`{1}` SET {2}='{3}';", dbName.Trim(), tblName.ToConventional(agent), schema.Name.ToConventional(agent), DateTime.UtcNow.ToString("yyyy'-'MM'-'dd HH':'mm':'ss"))); } result.AddRange(schema.GetAddIndexStatements(dbName, tblName, agent)); return(result); }
/// <summary> /// Gets a list of statements required to drop the index.(also drops a foreign key if required) /// </summary> /// <param name="schema">the field schema containing the index to drop</param> /// <param name="dbName">the database to drop the index for</param> /// <param name="tblName">the database table to drop the index for</param> internal static List <string> GetDropIndexStatements(this DbFieldSchema schema, string dbName, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (dbName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(dbName)); } if (tblName.IsNullOrWhitespace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary) { return(new List <string>()); } string result; if (schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary) { result = string.Format("ALTER TABLE `{0}`.`{1}` DROP FOREIGN KEY `{2}`;", dbName.Trim(), tblName.ToConventional(agent), schema.IndexName.ToConventional(agent)); } else { result = string.Format("DROP INDEX `{0}` ON `{1}`.`{2}`;", schema.IndexName.ToConventional(agent), dbName.Trim(), tblName.ToConventional(agent)); } return(new List <string>() { result }); }
/// <summary> /// Gets a list of statements required to add a new index using the field schema specified. /// (also adds a foreign key if required) /// </summary> /// <param name="schema">a canonical database table field schema to apply</param> /// <param name="tblName">the database table to add the index for</param> internal static List <string> GetAddIndexStatements(this DbFieldSchema schema, string tblName, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (tblName.IsNullOrWhiteSpace()) { throw new ArgumentNullException(nameof(tblName)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } // No backing index for foreign key in sqlite if (schema.IndexType == DbIndexType.None || schema.IndexType == DbIndexType.Primary || schema.IndexType == DbIndexType.ForeignKey || schema.IndexType == DbIndexType.ForeignPrimary) { return(new List <string>()); } string result; if (schema.IndexType == DbIndexType.Simple) { result = string.Format("CREATE INDEX {0} ON {1}({2});", schema.IndexName.ToConventional(agent), tblName.ToConventional(agent), schema.Name.ToConventional(agent)); } else { result = string.Format("CREATE UNIQUE INDEX {0} ON {1}({2});", schema.IndexName.ToConventional(agent), tblName.ToConventional(agent), schema.Name.ToConventional(agent)); } return(new List <string>() { result }); }
/// <summary> /// Gets the mysql native foreign key schema definition. /// </summary> /// <param name="schema">the canonical field schema to translate</param> internal static string GetForeignKeyDefinition(this DbFieldSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (!schema.IndexType.IsForeignKey()) { return(string.Empty); } return(string.Format("CONSTRAINT `{0}` FOREIGN KEY `{0}`(`{1}`) REFERENCES `{2}`(`{3}`) ON DELETE {4} ON UPDATE {5}", schema.IndexName.ToConventional(agent), schema.Name.ToConventional(agent), schema.RefTable.ToConventional(agent), schema.RefField.ToConventional(agent), schema.OnDeleteForeignKey.GetNativeActionType(), schema.OnUpdateForeignKey.GetNativeActionType())); }
/// <summary> /// Gets a list of statements required to drop the index.(also drops a foreign key if required) /// </summary> /// <param name="schema">the field schema containing the index to drop</param> internal static List <string> GetDropIndexStatements(this DbFieldSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } if (schema.IndexType != DbIndexType.Simple && schema.IndexType != DbIndexType.Unique) { return(new List <string>()); } return(new List <string>() { string.Format("DROP INDEX {0};", schema.IndexName.ToConventional(agent)) }); }
/// <summary> /// Gets the mysql native field schema definition. /// </summary> /// <param name="schema">the canonical field schema to translate</param> internal static string GetFieldDefinition(this DbFieldSchema schema, SqlAgentBase agent) { if (schema.IsNull()) { throw new ArgumentNullException(nameof(schema)); } if (agent.IsNull()) { throw new ArgumentNullException(nameof(agent)); } var result = string.Format("{0} {1}", schema.Name.ToConventional(agent), schema.GetNativeDataType()); if (schema.DataType == DbDataType.Char || schema.DataType == DbDataType.VarChar) { result = string.Format("{0}({1})", result, schema.Length.ToString()); } if (schema.DataType == DbDataType.Decimal) { result = string.Format("{0}({1}, {2})", result, (schema.Length + 15).ToString(), schema.Length.ToString()); } if (schema.DataType == DbDataType.Enum) { var enumValues = schema.EnumValues.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries) .Select(v => string.Format("'{0}'", v.ToConventional(agent))); result = string.Format("{0}({1})", result, string.Join(", ", enumValues)); } if (schema.DataType == DbDataType.Char || schema.DataType == DbDataType.Enum || schema.DataType == DbDataType.Text || schema.DataType == DbDataType.TextLong || schema.DataType == DbDataType.TextMedium || schema.DataType == DbDataType.VarChar) { if (schema.CollationType == DbFieldCollationType.ASCII_Binary) { result = result + " CHARACTER SET 'ascii' COLLATE 'ascii_bin'"; } else if (schema.CollationType == DbFieldCollationType.ASCII_CaseInsensitive) { result = result + " CHARACTER SET 'ascii' COLLATE 'ascii_general_ci'"; } } if (schema.Unsigned && (schema.DataType.IsDbDataTypeInteger() || schema.DataType.IsDbDataTypeFloat() || schema.DataType == DbDataType.Decimal)) { result = result + " UNSIGNED"; } if (schema.NotNull) { result = result + " NOT NULL"; } if (schema.Autoincrement && schema.DataType.IsDbDataTypeInteger()) { result = result + " AUTO_INCREMENT"; } if (schema.IndexType.IsPrimaryKey()) { result = result + " PRIMARY KEY"; } return(result); }