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);
}
public void Can_get_and_set_sql()
{
var sqlOperation = new SqlOperation("foo");
Assert.Equal("foo", sqlOperation.Sql);
}
