private string DF_constraint(APGenTable table, APGenColumn column)
{
if (column.DBDefaultValue != "")
{
return(String.Format("default {0}", column.DBDefaultValue));
}
return("default null");
}
private string DF_constraint(APGenTable table, APGenColumn column)
{
if (column.DBDefaultValue != "")
{
return(String.Format("constraint {0} default {1}", DF_name(table, column), column.DBDefaultValue));
}
return("");
}
private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
{
bool changeNullable = (!isTableEmpty && !column.IsNullable);
if (changeNullable)
{
column.IsNullable = true;
}
string sql;
if (column.IdentityType == APColumnIdentityType.Database)
{
sql = string.Format("alter table {0} add {1} {2} IDENTITY {3} {4}",
table.TableName,
column.ColumnName,
DBTypeName(column),
IsNullableString(column),
DF_constraint(table, column));
}
else
{
sql = string.Format("alter table {0} add {1} {2} {3} {4}",
table.TableName,
column.ColumnName,
DBTypeName(column),
IsNullableString(column),
DF_constraint(table, column));
}
DbCommand dbCmd = db.CreateSqlCommand(sql);
dbCmd.ExecuteNonQuery();
if (changeNullable)
{
column.IsNullable = false;
string defaultValue = column.DBDefaultValue == "" ? "''" : column.DBDefaultValue;
dbCmd = db.CreateSqlCommand("update {0} set {1} = {2}", table.TableName, column.ColumnName, defaultValue);
dbCmd.ExecuteNonQuery();
dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
dbCmd.ExecuteNonQuery();
}
}
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
{
if (info.dfname != "")
{
_dropConstraint(db, table, info.dfname);
}
DbCommand dbCmd;
// no safe mode to change identity, so ingone this, throw sql exception.
if (info.isnullable && !column.IsNullable)
{
dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
if ((int)dbCmd.ExecuteScalar() > 0)
{
// when column nullable change to not nullable and has data,
// set default value.
dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue());
dbCmd.ExecuteNonQuery();
}
}
dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
dbCmd.ExecuteNonQuery();
if (column.DBDefaultValue != "")
{
dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}",
table.TableName, DF_constraint(table, column), column.ColumnName);
dbCmd.ExecuteNonQuery();
}
// alter table table_nae alter column column_name type_name [not] null
// go
// alter table add constraint DF_tablename_columnname default dfvalue for column_name
// go
}
private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
{
string sql = "";
if (column.IdentityType == APColumnIdentityType.Database)
{
ThrowDBUnsupport("identityType=\"Database\"");
}
else
{
sql = string.Format("alter table {0} add {1} {2} {3} {4}",
table.TableName,
column.ColumnName,
DBTypeName(column),
DF_constraint(table, column),
IsNullableString(column));
}
DbCommand dbCmd = db.CreateSqlCommand(sql);
dbCmd.ExecuteNonQuery();
}
private string DBTypeName(APGenColumn column)
{
if (column.DBType == DbType.Object)
{
// auto detect dbtype
Type dataType = column.ParsedType;
if (column.IsEnum || dataType == null || dataType.IsEnum)
{
return("number(10, 0)");
}
else
{
if (dataType.IsGenericType && dataType.IsValueType)
{
dataType = Nullable.GetUnderlyingType(dataType);
}
if (dataType == typeof(bool))
{
return("number(1, 0)");
}
else if (dataType == typeof(byte))
{
return("number(3, 0)");
}
else if (dataType == typeof(byte[]))
{
if (column.DataLength == 0 || column.DataLength > 2000)
{
return("blob");
}
else
{
return("raw(" + column.DataLength.ToString() + ")");
}
}
else if (dataType == typeof(DateTime))
{
return("date");
}
else if (dataType == typeof(decimal))
{
if (column.Precision >= 1 && column.Precision <= 38 && column.Scale >= 0 && column.Scale <= column.Precision)
{
return(String.Format("number({0}, {1})", column.Precision, column.Scale));
}
else
{
ThrowColumnParsedTypeNotImplemented(dataType);
}
return(null);
}
else if (dataType == typeof(double))
{
return("binary_double");
}
else if (dataType == typeof(float))
{
return("binary_float");
}
else if (dataType == typeof(Guid))
{
return("raw(16)");
}
else if (dataType == typeof(short))
{
return("number(5, 0)");
}
else if (dataType == typeof(int))
{
return("number(10, 0)");
}
else if (dataType == typeof(long))
{
return("number(19, 0)");
}
else if (dataType == typeof(string))
{
if (column.DataLength == 0 || column.DataLength > 2000)
{
return("nclob");
}
else
{
return("nvarchar2(" + column.DataLength.ToString() + ")");
}
}
else
{
ThrowColumnParsedTypeNotImplemented(dataType);
return(null);
}
}
}
else
{
switch (column.DBType)
{
case DbType.Boolean:
return("number(1, 0)");
case DbType.Byte:
return("number(3, 0)");
case DbType.Binary:
{
if (column.DataLength == 0 || column.DataLength > 2000)
{
return("blob");
}
else
{
return("raw(" + column.DataLength.ToString() + ")");
}
}
case DbType.DateTime:
return("date");
case DbType.Decimal:
{
if (column.Precision >= 1 && column.Precision <= 38 && column.Scale >= 0 && column.Scale <= column.Precision)
{
return(String.Format("number({0}, {1})", column.Precision, column.Scale));
}
else
{
ThrowColumnParsedTypeNotImplemented(column.DBType);
}
return(null);
}
case DbType.Double:
return("binary_double");
case DbType.Single:
return("binary_float");
case DbType.Guid:
return("raw(16)");
case DbType.Int16:
return("number(5, 0)");
case DbType.Int32:
return("number(10, 0)");
case DbType.Int64:
return("number(19, 0)");
case DbType.String:
if (column.DataLength == 0 || column.DataLength > 2000)
{
return("nclob");
}
else
{
return("nvarchar2(" + column.DataLength.ToString() + ")");
}
case DbType.AnsiString:
if (column.DataLength == 0 || column.DataLength > 4000)
{
return("clob");
}
else
{
return("varchar2(" + column.DataLength.ToString() + ")");
}
case DbType.AnsiStringFixedLength:
if (column.DataLength == 0)
{
return("char(10)");
}
else
{
return("char(" + column.DataLength.ToString() + ")");
}
case DbType.Currency:
return("number(19, 4)");
case DbType.StringFixedLength:
if (column.DataLength == 0)
{
return("nchar(10)");
}
else
{
return("nchar(" + column.DataLength.ToString() + ")");
}
default:
ThrowColumnParsedTypeNotImplemented(column.DBType);
return(null);
}
}
}
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
{
DbCommand dbCmd;
int totalCount, nullCount = 0;
dbCmd = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName);
totalCount = Convert.ToInt32(dbCmd.ExecuteScalar());
if (totalCount > 0)
{
dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
nullCount = Convert.ToInt32(dbCmd.ExecuteScalar());
}
if (totalCount > 0)
{
if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount)
{
// change db type, the column value must be null
if (!info.isnullable)
{
dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null",
table.TableName, column.ColumnName);
dbCmd.ExecuteNonQuery();
info.isnullable = true;
}
_tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName));
nullCount = totalCount;
}
}
string sqlPadding = "";
if (DBTypeName(column) != info.GetTypeFullName())
{
sqlPadding += " " + DBTypeName(column);
}
if (column.DBDefaultValue != info.dfvalue)
{
sqlPadding += " " + DF_constraint(table, column);
}
bool lazyNotNullable = false;
if (column.IsNullable != info.isnullable)
{
if (column.IsNullable || nullCount == 0)
{
sqlPadding += " " + IsNullableString(column);
}
else
{
lazyNotNullable = true;
}
}
if (sqlPadding != "")
{
dbCmd = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName);
dbCmd.CommandText += sqlPadding;
dbCmd.ExecuteNonQuery();
}
if (lazyNotNullable)
{
string dfv = column.DBDefaultValue;
if (dfv == "")
{
dfv = "0";
}
_tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv));
dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName);
dbCmd.ExecuteNonQuery();
}
// alter table table_nae modify column_name type_name default dfvalue [not] null
// go
}
private string SEQ_name(APGenTable table, APGenColumn column)
{
return("SEQ_" + table.TableName + "_" + column.ColumnName);
}
private string IsNullableString(APGenColumn column)
{
return(column.IsNullable ? "null" : "not null");
}
/// <summary>
/// Add a column definition to collection.
/// </summary>
/// <param name="column">The column definition to add.</param>
public void Add(APGenColumn column)
{
BaseAdd(column);
}
/// <summary>
/// Add a column definition to collection.
/// </summary>
/// <param name="column">The column definition to add.</param>
public void Add(APGenColumn column)
{
BaseAdd(column);
}
