public ColumnExpression(TableExpression table, DbColumnInfo columnInfo)
: base(SqlExpressionType.Column, GetMemberType(columnInfo))
{
Table = table;
ColumnInfo = columnInfo;
Name = ColumnInfo.ColumnName;
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var colSpec = GetColumnSpec(column, options);
var tbl = column.Table;
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
change.AddScript(scriptType, "ALTER TABLE {0} MODIFY COLUMN {1};", tbl.FullName, colSpec);
}
//ALTER TABLE employees DROP COLUMN "employee_num";
public override void BuildColumnDropSql(DbObjectChange change, DbColumnInfo column)
{
if(!string.IsNullOrEmpty(column.DefaultExpression))
change.AddScript(DbScriptType.ColumnModify, "ALTER TABLE {0} DROP CONSTRAINT \"{1}\";", column.Table.FullName, column.DefaultConstraintName);
//Note: the column drop comes after table-rename, so it might be table is already renamed, and we have to get its new name
var tableName = column.Table.Peer.FullName; //new name if renamed
change.AddScript(DbScriptType.ColumnDrop, "ALTER TABLE {0} DROP COLUMN \"{1}\";", tableName, column.ColumnName);
}
//not supported; all we can do is nullify it; so if it is a FK it no longer holds target refs
public override void BuildColumnDropSql(DbObjectChange change, DbColumnInfo column)
{
//Note: the column drop comes after table-rename, so it might be table is already renamed, and we have to get its new name
var tableName = column.Table.Peer.FullName; //new name if renamed
if (column.Flags.IsSet(DbColumnFlags.Nullable) && column.Flags.IsSet(DbColumnFlags.ForeignKey)) {
change.AddScript(DbScriptType.ColumnInit, "UPDATE {0} SET \"{1}\" = NULL;", tableName, column.ColumnName);
}
}
public OutColumnMapping AddColumn(DbColumnInfo column, int readerColumnIndex = -1)
{
if (readerColumnIndex == -1)
readerColumnIndex = _columns.Count;
var colMapping = new OutColumnMapping() { ReaderColumnIndex = readerColumnIndex, DbColumn = column };
_columns.Add(colMapping);
if (column.Flags.IsSet(DbColumnFlags.PrimaryKey))
_primaryKeyColumns.Add(colMapping);
return colMapping;
}
//ALTER TABLE employees ALTER COLUMN [employee_name] nvarchar(100) Null;
public virtual void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
if(ShouldResetNullsToDefault(column)) {
BuildColumnSetDefaultValuesSql(change, column);
scriptType = DbScriptType.ColumnSetupComplete;
}
var colSpec = GetColumnSpec(column);
change.AddScript(scriptType,
"ALTER TABLE {0} ALTER COLUMN {1};", column.Table.FullName, colSpec);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
//SqlCe does not allow any modification of 'ntext'/memo columns
var dbType = column.TypeInfo.VendorDbType.DbType;
bool isNText = column.TypeInfo.Size < 0 && (dbType == DbType.String || dbType == DbType.Binary);
if(isNText) {
change.NotSupported("Modifying ntext column not supported in SqlCE. Column: {0}.{1}", column.Table.TableName, column.ColumnName);
return;
}
base.BuildColumnModifySql(change, column, options);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
if(ShouldResetNullsToDefault(column))
BuildColumnSetDefaultValuesSql(change, column);
// In Pg you modify column one aspect at a time; setting TYPE and Nullable requires 2 calls
change.AddScript(DbScriptType.ColumnModify, "ALTER TABLE {0} ALTER COLUMN \"{1}\" TYPE {2};",
column.Table.FullName, column.ColumnName, column.TypeInfo.SqlTypeSpec);
var nullStr = column.Flags.IsSet(DbColumnFlags.Nullable) ? "DROP NOT NULL" : "SET NOT NULL";
change.AddScript(DbScriptType.ColumnSetupComplete, "ALTER TABLE {0} ALTER COLUMN \"{1}\" {2};",
column.Table.FullName, column.ColumnName, nullStr);
}
public override void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
if(!column.Flags.IsSet(DbColumnFlags.Nullable)) {
var dft = column.TypeInfo.VendorDbType.DefaultColumnInit;
if (string.IsNullOrWhiteSpace(dft))
dft = column.TypeInfo.ToLiteral(new byte[] {0});
colSpec += " DEFAULT " + dft;
}
//workaround for unit test with renaming table - ignore rename, use old table
var tbl = column.Table;
if (tbl.Peer != null)
tbl = tbl.Peer; //use old table name
change.AddScript(DbScriptType.ColumnAdd, "ALTER TABLE {0} ADD {1};", tbl.FullName, colSpec);
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var idStr = string.Empty;
bool isNew = column.Peer == null;
if(isNew && column.Flags.IsSet(DbColumnFlags.Identity)) {
idStr = "IDENTITY(1,1)";
}
string defaultStr = null;
//Default constraint can be set only on new columns in SQL server
if(!string.IsNullOrWhiteSpace(column.DefaultExpression) && isNew)
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(" \"{0}\" {1} {2} {3} {4}", column.ColumnName, typeStr, idStr, defaultStr, nullStr);
return spec;
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var strAutoInc = string.Empty;
bool isNew = column.Peer == null;
if(isNew && column.Flags.IsSet(DbColumnFlags.Identity)) {
// MySql requires that auto-incr column is supported by a key - either a primary key, or an index
var strKeyType = column.Flags.IsSet(DbColumnFlags.PrimaryKey) ? "PRIMARY KEY" : "KEY";
strAutoInc = string.Format("AUTO_INCREMENT, {0}(\"{1}\")", strKeyType, column.ColumnName);
}
string defaultStr = null;
//Default constraint can be set only on new columns
if(!string.IsNullOrWhiteSpace(column.DefaultExpression) && options.IsSet(DbScriptOptions.NewColumn))
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(@" ""{0}"" {1} {2} {3}", column.ColumnName, typeStr, nullStr, strAutoInc);
return spec;
}
public override void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
{
change.AddScript(DbScriptType.ColumnRename, "EXEC SYS.SP_RENAME '{0}.{1}.{2}' , '{3}', 'COLUMN'",
oldColumn.Table.Schema, oldColumn.Table.TableName, oldColumn.ColumnName, newColumn.ColumnName);
}
//ALTER TABLE employees DROP COLUMN [employee_pwd];
public virtual void BuildColumnDropSql(DbObjectChange change, DbColumnInfo column)
{
//Note: the column drop comes after table-rename, so it might be table is already renamed, and we have to get its new name
var tableName = column.Table.Peer.FullName; //new name if renamed
change.AddScript(DbScriptType.ColumnDrop, "ALTER TABLE {0} DROP COLUMN \"{1}\"", tableName, column.ColumnName);
}
protected bool ShouldResetNullsToDefault(DbColumnInfo column)
{
if(column.Flags.IsSet(DbColumnFlags.Nullable | DbColumnFlags.ForeignKey))
return false;
// (new) column is not nullable
if(column.Peer == null) // it is new column
return true;
if(column.Peer.Flags.IsSet(DbColumnFlags.Nullable)) //old was nullable, but new is not nullable
return true;
return false;
}
public DbParamInfo(EntityCommandParameter owner, string name, DbColumnInfo sourceColumn, int argIndex)
: this(owner, name, argIndex)
{
SourceColumn = sourceColumn;
Direction = owner.Direction;
TypeInfo = sourceColumn.TypeInfo;
}
public DbParamInfo AddParameter(EntityCommandParameter owner, string name, DbColumnInfo column, int argIndex)
{
return AddParameter(new DbParamInfo(owner, name, column, argIndex));
}
private DbColumnInfo CreateDbColumn(DbTableInfo table, EntityMemberInfo member)
{
bool isError = false;
var colName = member.ColumnName;
string colDefault = member.ColumnDefault; //comes from attribute
if (colDefault != null && member.DataType == typeof(string) && !colDefault.StartsWith("'"))
colDefault = colDefault.Quote();
var dbTypeInfo = _driver.TypeRegistry.GetDbTypeInfo(member, _log);
if (dbTypeInfo == null) {
isError = true;
LogError("Driver failed to match db type for data type {0}, member {1}.{2}", member.DataType, member.Entity.FullName, member.MemberName);
// do not throw, continue to find more errors
//Util.Throw("Driver failed to match db type for data type {0}, member {1}.{2}", member.DataType, member.Entity.FullName, member.MemberName);
return null;
}
var dbColumn = new DbColumnInfo(member, table, colName, dbTypeInfo);
if (!string.IsNullOrEmpty(colDefault))
dbColumn.DefaultExpression = colDefault;
if (member.AutoValueType == AutoType.Identity)
dbColumn.Flags |= DbColumnFlags.Identity | DbColumnFlags.NoUpdate | DbColumnFlags.NoInsert;
if (member.Flags.IsSet(EntityMemberFlags.Secret))
dbColumn.Flags |= DbColumnFlags.NoUpdate; //updated only thru custom update method
if (member.Flags.IsSet(EntityMemberFlags.NoDbInsert))
dbColumn.Flags |= DbColumnFlags.NoInsert;
if(member.Flags.IsSet(EntityMemberFlags.NoDbUpdate))
dbColumn.Flags |= DbColumnFlags.NoUpdate;
if(isError)
dbColumn.Flags |= DbColumnFlags.Error;
if (member.Flags.IsSet(EntityMemberFlags.UnlimitedSize) && _driver.Supports(DbFeatures.ForceNullableMemo)) //case for SQL CE
dbColumn.Flags |= DbColumnFlags.Nullable;
return dbColumn;
}
public virtual void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
{
Util.Throw("Column renaming is not supported by DbDriver {0}.", this.GetType());
}
public DbParamInfo AddParameter(EntityCommandParameter owner, string name, DbColumnInfo column, int argIndex)
{
return(AddParameter(new DbParamInfo(owner, name, column, argIndex)));
}
public virtual void BuildColumnSetDefaultValuesSql(DbObjectChange change, DbColumnInfo column)
{
var fullTableRef = column.Table.FullName;
change.AddScript(DbScriptType.ColumnInit, "UPDATE {0} SET \"{1}\" = {2} WHERE \"{1}\" IS NULL;",
fullTableRef, column.ColumnName, column.TypeInfo.InitExpression);
}
public override void BuildColumnSetDefaultValuesSql(DbObjectChange change, DbColumnInfo column)
{
const string sqlUpdateValues = "UPDATE {0} SET \"{1}\" = {2} WHERE \"{1}\" IS NULL;";
var fullTableRef = column.Table.FullName;
change.AddScript(DbScriptType.ColumnInit, sqlUpdateValues, fullTableRef, column.ColumnName, column.TypeInfo.InitExpression);
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
if(column.Flags.IsSet(DbColumnFlags.Identity)) {
if(column.TypeInfo.SqlTypeSpec == "bigint")
return string.Format(@"""{0}"" BIGSERIAL ", column.ColumnName);
else
return string.Format(@"""{0}"" SERIAL ", column.ColumnName);
}
return base.GetColumnSpec(column, options);
}
// All columns are added as nullable, to allow for existing rows be filled with nulls
// Then extra step sets default values for types (zeros), and then column is modified to NOT NULL
protected virtual void BuildColumnAddSqlSafe(DbObjectChange change, DbColumnInfo column)
{
var nullable = column.Flags.IsSet(DbColumnFlags.Nullable);
var noDefault = string.IsNullOrEmpty(column.DefaultExpression);
var isIdentity = column.Flags.IsSet(DbColumnFlags.Identity);
var forceNull = !nullable && noDefault && !isIdentity;
var options = forceNull ? DbScriptOptions.ForceNull : DbScriptOptions.None;
BuildColumnAddSql(change, column, options);
if(forceNull) {
BuildColumnSetDefaultValuesSql(change, column);
BuildColumnModifySql(change, column, DbScriptOptions.CompleteColumnSetup);
}
}
public override void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
{
change.AddScript(DbScriptType.ColumnRename, "ALTER TABLE {0} RENAME COLUMN \"{1}\" TO \"{2}\";", newColumn.Table.FullName, oldColumn.ColumnName, newColumn.ColumnName);
}
// ALTER TABLE employees ADD [employee_pwd] nvarchar(20) Null;
// All columns are added as nullable, to allow for existing rows be filled with nulls
// Then extra step columnInit sets default values for types (zeros), and then column is modified to NOT NULL
public virtual void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
change.AddScript(DbScriptType.ColumnAdd, "ALTER TABLE {0} ADD {1};", column.Table.FullName, colSpec);
}
public DbKeyColumnInfo(DbColumnInfo column, EntityKeyMemberInfo keyMember = null, bool desc = false)
{
Column = column;
EntityKeyMember = keyMember;
if(EntityKeyMember == null)
Desc = desc;
else
Desc = EntityKeyMember.Desc;
}
//Helper methods
protected virtual void BuildColumnRenameSqlWithAddDrop(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
{
//Add new column
BuildColumnAddSql(change, newColumn, DbScriptOptions.ForceNull);
// copy data
change.AddScript(DbScriptType.ColumnCopyValues,
"UPDATE {0} SET \"{1}\" = \"{2}\";", oldColumn.Table.FullName, newColumn.ColumnName, oldColumn.ColumnName);
// finalize added column
BuildColumnModifySql(change, newColumn, DbScriptOptions.CompleteColumnSetup);
//drop old column
BuildColumnDropSql(change, oldColumn);
}
// A chance for a driver to add vendor-specific information to the loaded column.
public virtual void OnColumnLoaded(DbColumnInfo column, DataRow columnRow)
{
}
protected void CheckDefaultConstraintName(DbColumnInfo column)
{
if(string.IsNullOrEmpty(column.DefaultExpression) || !string.IsNullOrWhiteSpace(column.DefaultConstraintName))
return;
var tbl = column.Table;
column.DefaultConstraintName = "DEFAULT_" + tbl.Schema + "_" + tbl.TableName + "_" + column.ColumnName;
}
private static Type GetMemberType(DbColumnInfo columnInfo)
{
return columnInfo.Member.DataType;
}
protected virtual string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
string defaultStr = null;
if(!string.IsNullOrWhiteSpace(column.DefaultExpression))
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(" \"{0}\" {1} {2} {3}", column.ColumnName, typeStr, defaultStr, nullStr);
return spec;
}
