private static void AddSqlOperation(this IDbMigration migration, string sql, bool suppressTransaction = false, object anonymousArguments = null) { var operation = new SqlOperation(sql, anonymousArguments) { SuppressTransaction = suppressTransaction }; migration.AddOperation(operation); }
public static void DeleteDefaultContraint(this IDbMigration migration, string tableName, string colName, bool suppressTransaction = false) { var sql = new SqlOperation(String.Format(@"DECLARE @SQL varchar(1000) SET @SQL='ALTER TABLE {0} DROP CONSTRAINT ['+(SELECT name FROM sys.default_constraints WHERE parent_object_id = object_id('{0}') AND col_name(parent_object_id, parent_column_id) = '{1}')+']'; PRINT @SQL; EXEC(@SQL);", tableName, colName)) { SuppressTransaction = suppressTransaction }; migration.AddOperation(sql); }
protected virtual IEnumerable<MigrationStatement> Generate(SqlOperation operation) { yield return Statement(operation.Sql, operation.SuppressTransaction); }
public void Can_get_and_set_sql() { var sqlOperation = new SqlOperation("foo"); Assert.Equal("foo", sqlOperation.Sql); }
protected virtual MigrationStatement Generate(SqlOperation op) { return new MigrationStatement { Sql = op.Sql, SuppressTransaction = op.SuppressTransaction }; }
protected virtual string Generate(ColumnModel op) { TypeUsage typeUsage = _providerManifest.GetStoreType(op.TypeUsage); StringBuilder sb = new StringBuilder(); string type = MySqlProviderServices.Instance.GetColumnType(typeUsage); sb.Append(type); if (!type.EndsWith(")", StringComparison.InvariantCulture)) { if ((op.ClrType == typeof(string)) || ((op.ClrType == typeof(byte)) && (op.ClrType.IsArray))) { if (op.MaxLength.HasValue) { sb.AppendFormat("({0}) ", op.MaxLength.Value); } } if (op.Precision.HasValue && op.Scale.HasValue) { sb.AppendFormat("( {0}, {1} ) ", op.Precision.Value, op.Scale.Value); } else { if (type == "datetime" || type == "timestamp" || type == "time") { if (op.Precision.HasValue && op.Precision.Value >= 1) { sb.AppendFormat("({0}) ", op.Precision.Value <= 6 ? op.Precision.Value : 6); } if (op.IsIdentity && (String.Compare(type, "datetime", true) == 0 || String.Compare(type, "timestamp", true) == 0)) { sb.AppendFormat(" DEFAULT CURRENT_TIMESTAMP{0}", op.Precision.HasValue && op.Precision.Value >= 1 ? "( " + op.Precision.Value.ToString() + " )" : ""); } } } } if (!(op.IsNullable ?? true)) { sb.Append(" not null "); } if (op.IsIdentity && type == "int") { sb.Append(" auto_increment primary key "); } else { if (op.IsIdentity && String.Compare(type,"CHAR(36) BINARY", true) == 0) { var createTrigger = new StringBuilder(); createTrigger.AppendLine(string.Format("DROP TRIGGER IF EXISTS `{0}_IdentityTgr`;", _tableName)); createTrigger.AppendLine(string.Format("CREATE TRIGGER `{0}_IdentityTgr` BEFORE INSERT ON `{0}`", _tableName)); createTrigger.AppendLine("FOR EACH ROW BEGIN"); createTrigger.AppendLine(string.Format("SET NEW.{0} = UUID();", op.Name)); createTrigger.AppendLine(string.Format("DROP TEMPORARY TABLE IF EXISTS tmpIdentity_{0};", _tableName)); createTrigger.AppendLine(string.Format("CREATE TEMPORARY TABLE tmpIdentity_{0} (guid CHAR(36))ENGINE=MEMORY;", _tableName)); createTrigger.AppendLine(string.Format("INSERT INTO tmpIdentity_{0} VALUES(New.{1});", _tableName, op.Name)); createTrigger.AppendLine("END"); var sqlOp = new SqlOperation(createTrigger.ToString()); _specialStmts.Add(Generate(sqlOp)); } } if (!string.IsNullOrEmpty(op.DefaultValueSql)) { sb.Append(string.Format(" default {0}", op.DefaultValueSql)); } return sb.ToString(); }
/// <summary> /// Generates code to perform a <see cref="SqlOperation" />. /// </summary> /// <param name="sqlOperation"> The operation to generate code for. </param> /// <param name="writer"> Text writer to add the generated code to. </param> protected virtual void Generate(SqlOperation sqlOperation, IndentedTextWriter writer) { Check.NotNull(sqlOperation, "sqlOperation"); Check.NotNull(writer, "writer"); writer.Write("Sql("); writer.Write(Quote(sqlOperation.Sql)); if (sqlOperation.SuppressTransaction) { writer.Write(", suppressTransaction := True"); } writer.WriteLine(")"); }
/// <summary> /// Generates SQL for a <see cref="SqlOperation" />. /// Generated SQL should be added using the Statement or StatementBatch methods. /// </summary> /// <param name="sqlOperation"> The operation to produce SQL for. </param> protected virtual void Generate(SqlOperation sqlOperation) { Check.NotNull(sqlOperation, "sqlOperation"); StatementBatch(sqlOperation.Sql, sqlOperation.SuppressTransaction); }
protected virtual MigrationStatement Generate(CreateTableOperation op) { StringBuilder sb = new StringBuilder(); string tableName = TrimSchemaPrefix(op.Name); primaryKeyCols.Clear(); autoIncrementCols.Clear(); if (_generatedTables == null) _generatedTables = new List<string>(); if (!_generatedTables.Contains(tableName)) { _generatedTables.Add(tableName); } sb.Append("create table " + "`" + tableName + "`" + " ("); _tableName = op.Name; if (op.PrimaryKey != null) { op.PrimaryKey.Columns.ToList().ForEach(col => primaryKeyCols.Add(col)); } //columns sb.Append(string.Join(",", op.Columns.Select(c => "`" + c.Name + "` " + Generate(c)))); // Determine columns that are GUID & identity List<ColumnModel> guidCols = new List<ColumnModel>(); ColumnModel guidPK = null; foreach( ColumnModel opCol in op.Columns ) { if (opCol.Type == PrimitiveTypeKind.Guid && opCol.IsIdentity && String.Compare(opCol.StoreType, "CHAR(36) BINARY", true) == 0) { if( primaryKeyCols.Contains( opCol.Name ) ) guidPK = opCol; guidCols.Add(opCol); } } if (guidCols.Count != 0) { var createTrigger = new StringBuilder(); createTrigger.AppendLine(string.Format("DROP TRIGGER IF EXISTS `{0}_IdentityTgr`;", TrimSchemaPrefix(_tableName))); createTrigger.AppendLine(string.Format("CREATE TRIGGER `{0}_IdentityTgr` BEFORE INSERT ON `{0}`", TrimSchemaPrefix(_tableName))); createTrigger.AppendLine("FOR EACH ROW BEGIN"); for (int i = 0; i < guidCols.Count; i++) { ColumnModel opCol = guidCols[i]; createTrigger.AppendLine(string.Format("SET NEW.{0} = UUID();", opCol.Name)); } createTrigger.AppendLine(string.Format("DROP TEMPORARY TABLE IF EXISTS tmpIdentity_{0};", TrimSchemaPrefix(_tableName))); createTrigger.AppendLine(string.Format("CREATE TEMPORARY TABLE tmpIdentity_{0} (guid CHAR(36))ENGINE=MEMORY;", TrimSchemaPrefix(_tableName))); createTrigger.AppendLine(string.Format("INSERT INTO tmpIdentity_{0} VALUES(New.{1});", TrimSchemaPrefix(_tableName), guidPK.Name)); createTrigger.AppendLine("END"); var sqlOp = new SqlOperation(createTrigger.ToString()); _specialStmts.Add(Generate(sqlOp)); } if (op.PrimaryKey != null)// && !sb.ToString().Contains("primary key")) { sb.Append(","); sb.Append("primary key ( " + string.Join(",", op.PrimaryKey.Columns.Select(c => "`" + c + "`")) + ") "); } string keyFields = ","; autoIncrementCols.ForEach(col => keyFields += (!primaryKeyCols.Contains(col) ? string.Format(" KEY (`{0}`),", col) : "")); sb.Append(keyFields.Substring(0, keyFields.LastIndexOf(","))); sb.Append(") engine=InnoDb auto_increment=0"); return new MigrationStatement() { Sql = sb.ToString() }; }
/// <summary> /// Generates code to perform a <see cref = "SqlOperation" />. /// </summary> /// <param name = "sqlOperation">The operation to generate code for.</param> /// <param name = "writer">Text writer to add the generated code to.</param> protected virtual void Generate(SqlOperation sqlOperation, IndentedTextWriter writer) { Contract.Requires(sqlOperation != null); Contract.Requires(writer != null); writer.Write("Sql("); writer.Write(Quote(sqlOperation.Sql)); if (sqlOperation.SuppressTransaction) { writer.Write(", suppressTransaction := True"); } writer.WriteLine(")"); }
/// <summary> /// Gera SQL para uma operação <see cref="SqlOperation" />. /// </summary> /// <param name="opeSQL"> The operation to produce SQL for. </param> protected virtual void Generate(SqlOperation opeSQL) { ComandoSQL(opeSQL.Sql, opeSQL.SuppressTransaction); }
/// <summary> /// Generates SQL for a <see cref = "SqlOperation" />. /// Generated SQL should be added using the Statement method. /// </summary> /// <param name = "sqlOperation">The operation to produce SQL for.</param> protected virtual void Generate(SqlOperation sqlOperation) { Contract.Requires(sqlOperation != null); Statement(sqlOperation.Sql, sqlOperation.SuppressTransaction); }