Migrator Fixed SQL column building code so it correctly builds Identity columns that are not primary keys.
Changeset 4261f27a88a2…
Parent 19c91bd2b1d8…
by
Changes to one file · Browse files at 4261f27a88a2 Showing diff from parent 19c91bd2b1d8 Diff from another changeset...
|
@@ -47,7 +47,7 @@
foreach (Column col in columns)
{
- sqlColumns[i] = GetSQLForColumn(col, name);
+ sqlColumns[i] = GetSQLForColumn(col);
i++;
}
@@ -166,13 +166,13 @@ return;
}
- string sqlColumn = GetSQLForColumn(new Column(column, type, size, property, defaultValue), table);
+ string sqlColumn = GetSQLForColumn(new Column(column, type, size, property, defaultValue));
ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD {1}", table, sqlColumn));
}
public override void AlterColumn(string table, string column, Type type, int size)
{
- string sqlColumn = GetSQLForColumn(new Column(column, type, size), table);
+ string sqlColumn = GetSQLForColumn(new Column(column, type, size));
ExecuteNonQuery(string.Format("ALTER TABLE {0} ALTER COLUMN {1}", table, sqlColumn));
}
@@ -428,56 +428,54 @@ #endregion
#region Helper methods
- private string GetSQLForColumn(Column col, string tableName)
+ private string GetSQLForColumn(Column col)
{
- string sqlType = ToSqlType(col.Type, col.Size);
- string sqlConstraint = "NULL";
+ // Force boolean values to have a default
+ if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
+ col.DefaultValue = false;
+
+ string sqlDefaultKeyword = "DEFAULT ";
+ string sqlDefault = "";
+
+ // assign default values, ignore defaults if they have been overloaded for PK/Identity purposes
+ if (col.DefaultValue != null)
+ sqlDefault = sqlDefaultKeyword + ToSqlValue(col.Type, col.DefaultValue);
+
+ string sqlIdentity = "";
+ string sqlConstraint = "";
switch (col.ColumnProperty)
{
case ColumnProperties.Null:
sqlConstraint = "NULL";
break;
+ case ColumnProperties.NotNull:
+ sqlConstraint = "NOT NULL";
+ break;
+ // means a unique ID or a uniquely generated GUID
+ case ColumnProperties.Identity:
+ sqlConstraint = "NOT NULL"; // Identity columns must also be Not Null
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
+ break;
case ColumnProperties.PrimaryKey:
+ sqlConstraint = "PRIMARY KEY";
+ break;
+ // means a unique ID or a uniquely generated GUID as the PK
case ColumnProperties.PrimaryKeyWithIdentity:
sqlConstraint = "PRIMARY KEY";
- break;
- case ColumnProperties.NotNull:
- sqlConstraint = "NOT NULL";
+ if (col.Type == typeof(Guid))
+ sqlDefault = sqlDefaultKeyword + "NEWID()";
+ else
+ sqlIdentity = "IDENTITY (1, 1)"; // generate sequential numbers
break;
case ColumnProperties.UNIQUE:
sqlConstraint = "UNIQUE";
break;
}
- string sqlDefault = "";
- string sqlIdentity = "";
- // Force boolean values to have a default
- if (col.Type == typeof(bool) && col.ColumnProperty != ColumnProperties.Null && col.DefaultValue == null)
- col.DefaultValue = false;
-
- // assign default values
- if (col.DefaultValue != null)
- {
- sqlDefault = string.Format("DEFAULT {0}", ToSqlValue(col.Type, col.DefaultValue));
- }
-
- // If this is an Identity column and a GUID we will use the NewId
- // function as the default.
- if ((col.ColumnProperty == ColumnProperties.PrimaryKeyWithIdentity
- || col.ColumnProperty == ColumnProperties.Identity)
- && col.Type == typeof(Guid))
- {
- sqlDefault = "DEFAULT NEWID()";
- }
-
- // Determine if Identity is required
- if ((col.ColumnProperty == ColumnProperties.PrimaryKeyWithIdentity
- || col.ColumnProperty == ColumnProperties.Identity)
- && (col.Type == typeof(int) || col.Type == typeof(long)))
- {
- sqlIdentity = string.Format("IDENTITY (1, 1)");
- }
-
+ string sqlType = ToSqlType(col.Type, col.Size);
return string.Join(" ", new string[] { col.Name, sqlType, sqlDefault, sqlIdentity, sqlConstraint });
}
|
||
Add a tag
Loading...