private void SyncSequence(APDatabase db, APGenBusinessModelSection businessModel)
{
List <string> seqnames = new List <string>();
DbCommand dbCmd = db.CreateSqlCommand("select sequence_name from user_sequences");
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
seqnames.Add(reader.GetString(0));
}
}
foreach (APGenTable table in businessModel.Tables)
{
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Provider &&
businessModel.CanIdentityRelyOnProvider(column) &&
column.ParsedType != typeof(Guid))
{
string seqname = SEQ_name(table.TableName, column.ColumnName).ToUpper();
if (!seqnames.Contains(seqname))
{
dbCmd = db.CreateSqlCommand("create sequence {0} increment by 1 start with {1} nomaxvalue nocycle nocache",
seqname, column.ProviderIdentityBase);
dbCmd.ExecuteNonQuery();
}
}
}
}
}
private void _createTable(APDatabase db, APGenTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("create table {0} (", table.TableName);
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Database)
{
ThrowDBUnsupport("identityType=\"Database\"");
}
else
{
sb.AppendFormat("\r\n\t{0} {1} {2} {3},",
column.ColumnName, DBTypeName(column), DF_constraint(table, column), IsNullableString(column));
}
}
sb.Length--;
sb.Append("\r\n)");
// create table table_name (
// column_name column_type [default ...] [not] null,
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
/// <summary>
/// Synchronize database.
/// </summary>
/// <param name="businessModel">Business model.</param>
public override void Sync(APGenBusinessModelSection businessModel)
{
using (APDatabase db = new APDatabase(this))
{
try
{
db.BeginTrans();
foreach (APGenTable table in GetAllTables(businessModel))
{
_setScanTable(table.TableName);
DbCommand dbCmd = db.CreateSqlCommand("select table_name from user_tables where temporary = 'N' and table_name = '{0}'", table.TableName.ToUpper());
object id = dbCmd.ExecuteScalar();
if (id == null)
CreateTable(db, table);
else
ModifyTable(db, table);
}
SyncSequence(db, businessModel);
db.Commit();
}
catch(Exception ex)
{
db.Rollback();
throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
}
}
}
private void SyncCurrentId(APDatabase db, APGenBusinessModelSection businessModel)
{
List <string> tcnames = new List <string>();
DbCommand dbCmd = db.CreateSqlCommand("select {1} from {0}", currentIdTable, currentIdName);
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
tcnames.Add(reader.GetString(0));
}
}
foreach (APGenTable table in businessModel.Tables)
{
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Provider &&
businessModel.CanIdentityRelyOnProvider(column) &&
column.ParsedType != typeof(Guid))
{
string tcname = table.TableName + "." + column.ColumnName;
if (!tcnames.Contains(tcname))
{
dbCmd = db.CreateSqlCommand("insert into {0} ({1}, {2}) values ('{3}', '{4}')",
currentIdTable, currentIdName, currentIdValue,
tcname, column.ProviderIdentityBase);
dbCmd.ExecuteNonQuery();
}
}
}
}
}
/// <summary>
/// Map extensions.
/// </summary>
/// <typeparam name="T">T.</typeparam>
/// <param name="db">APDatabase.</param>
/// <param name="reader">IDataReader.</param>
/// <param name="fillup">Fill function.</param>
/// <returns>IEnumerable.</returns>
public static IEnumerable <T> Map <T>(this APDatabase db, IDataReader reader, Func <IDataReader, T> fillup)
{
while (reader.Read())
{
yield return(fillup(reader));
}
}
private bool _isTableEmpty(APDatabase db, APGenTable table)
{
DbCommand dbCmd = db.CreateSqlCommand(
@"select count(*)
from {0}", table.TableName);
int count = Convert.ToInt32(dbCmd.ExecuteScalar());
return(count == 0);
}
/// <summary>
/// Query extensions.
/// </summary>
/// <typeparam name="T">T.</typeparam>
/// <param name="db">APDatabase.</param>
/// <param name="rawSql">The Raw SQL expression.</param>
/// <param name="fillup">Fill function.</param>
/// <returns>IEnumerable.</returns>
public static IEnumerable <T> Query <T>(this APDatabase db, string rawSql, Func <IDataReader, T> fillup)
{
using (IDataReader reader = db.CreateSqlCommand(rawSql).ExecuteReader())
{
while (reader.Read())
{
yield return(fillup(reader));
}
}
}
/// <summary>
/// Query extensions.
/// </summary>
/// <typeparam name="T">T.</typeparam>
/// <param name="db">APDatabase.</param>
/// <param name="command">The 'SELECT' command.</param>
/// <param name="fillup">Fill function.</param>
/// <returns>IEnumerable.</returns>
public static IEnumerable <T> Query <T>(this APDatabase db, APSqlSelectCommand command, Func <IDataReader, T> fillup)
{
using (IDataReader reader = db.ExecuteReader(command))
{
while (reader.Read())
{
yield return(fillup(reader));
}
}
}
private void _dropColumn(APDatabase db, APGenTable table, columninfo info)
{
if (info.dfname != "")
{
_dropConstraint(db, table, info.dfname);
}
DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname);
dbCmd.ExecuteNonQuery();
}
private void _dropIndex(APDatabase db, APGenTable table, indexinfo info)
{
if (info.indtype != indexType.Index)
{
_dropConstraint(db, table, info.indname);
}
else
{
DbCommand dbCmd = db.CreateSqlCommand("drop index {0}", info.indname);
dbCmd.ExecuteNonQuery();
}
}
private void CreateTable(APDatabase db, APGenTable table)
{
_createTable(db, table);
_createPrimaryKey(db, table);
foreach (APGenIndex index in table.Indexes)
{
_createIndex(db, table, index);
}
foreach (APGenIndex unique in table.Uniques)
{
_createUnique(db, table, unique);
}
}
private void CreateTable(APDatabase db, APGenTable table)
{
_createTable(db, table);
_createPrimaryKey(db, table);
foreach (APGenIndex index in table.Indexes)
{
_createIndex(db, table, index);
}
foreach (APGenIndex unique in table.Uniques)
{
_createUnique(db, table, unique);
}
}
private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table)
{
DbCommand dbCmd = db.CreateSqlCommand(
@"select
col.colid,
col.name as colname,
tp.name as typename,
tp.variable,
col.length,
col.xprec,
col.xscale,
col.isnullable,
df.name as dfname,
df.definition as dfvalue,
id.is_identity
from
syscolumns as col
inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname'
left join sys.default_constraints as df on col.cdefault = df.object_id
left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id
where col.id = object_id('{0}')
order by col.colid", table.TableName);
Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>();
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
int idx = 0;
columninfo data = new columninfo()
{
colid = Convert.ToInt32(reader.GetValue(idx++)),
colname = Convert.ToString(reader.GetValue(idx++)),
typename = Convert.ToString(reader.GetValue(idx++)),
variable = Convert.ToBoolean(reader.GetValue(idx++)),
length = Convert.ToInt32(reader.GetValue(idx++)),
xprec = Convert.ToInt32(reader.GetValue(idx++)),
xscale = Convert.ToInt32(reader.GetValue(idx++)),
isnullable = Convert.ToBoolean(reader.GetValue(idx++)),
dfname = Convert.ToString(reader.GetValue(idx++)),
dfvalue = Convert.ToString(reader.GetValue(idx++)),
is_identity = reader.IsDBNull(idx++) ? false : true,
};
data.Resolve();
dbColumns.Add(data.colname, data);
}
}
return(dbColumns);
}
private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table)
{
OracleCommand dbCmd = db.CreateSqlCommand(
@"select
column_id,
column_name,
data_type,
data_length,
nvl(data_precision, 0),
nvl(data_scale, 0),
nullable,
nvl(default_length, 0),
data_default
from user_tab_columns
where table_name = '{0}'
order by column_id", table.TableName.ToUpper()) as OracleCommand;
dbCmd.InitialLONGFetchSize = -1;
Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>(StringComparer.InvariantCultureIgnoreCase);
using (OracleDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
int idx = 0;
columninfo data = new columninfo()
{
colid = Convert.ToInt32(reader.GetValue(idx++)),
colname = Convert.ToString(reader.GetValue(idx++)),
typename = Convert.ToString(reader.GetValue(idx++)).ToLower(),
length = Convert.ToInt32(reader.GetValue(idx++)),
xprec = Convert.ToInt32(reader.GetValue(idx++)),
xscale = Convert.ToInt32(reader.GetValue(idx++)),
isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y",
dflength = Convert.ToInt32(reader.GetValue(idx++)),
dfvalue = "",
};
if (data.dflength > 0)
{
data.dfvalue = reader.GetString(idx++).Trim();
}
data.Resolve();
dbColumns.Add(data.colname, data);
}
}
return(dbColumns);
}
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 _tempSql(string sql)
{
DbCommand dbCmd;
using (APDatabase dbtemp = new APDatabase(this))
{
try
{
dbtemp.BeginTrans();
dbCmd = dbtemp.CreateSqlCommand(sql);
dbCmd.ExecuteNonQuery();
dbtemp.Commit();
}
catch
{
dbtemp.Rollback();
}
}
}
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 _createUnique(APDatabase db, APGenTable table, APGenIndex index)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("alter table {0} add constraint {1} unique (", table.TableName, index.Name);
foreach (APGenOrder order in index.Orders)
{
sb.AppendFormat("\r\n\t{0},", table.Columns[order.Name].ColumnName);
}
sb.Length--;
sb.Append("\r\n)");
// alter table table_name add constraint uq_name (
// column_name,
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private void _createPrimaryKey(APDatabase db, APGenTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("alter table {0} add constraint {1} primary key (", table.TableName, PK_name(table));
foreach (APGenColumn column in table.PrimaryKeyColumns)
{
sb.AppendFormat("\r\n\t{0},", column.ColumnName);
}
sb.Length--;
sb.Append("\r\n)");
// alter table tablen_ame add constraint PK_table_name primary key(
// column_name,
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private void _createIndex(APDatabase db, APGenTable table, APGenIndex index)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("create index {0} on {1} (", index.Name, table.TableName);
foreach (APGenOrder order in index.Orders)
{
sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
}
sb.Length--;
sb.Append("\r\n)");
// create index index_name on (
// column_name [ASC|DESC],
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
/// <summary>
/// Synchronize database.
/// </summary>
/// <param name="businessModel">Business model.</param>
public override void Sync(APGenBusinessModelSection businessModel)
{
using (APDatabase db = new APDatabase(this))
{
try
{
db.BeginTrans();
foreach (APGenTable table in GetAllTables(businessModel))
{
_setScanTable(table.TableName);
DbCommand dbCmd = db.CreateSqlCommand("select id from sysobjects where type = 'U' and name = '{0}'", table.TableName);
object id = dbCmd.ExecuteScalar();
if (id == null)
{
CreateTable(db, table);
}
else
{
ModifyTable(db, table);
}
}
SyncCurrentId(db, businessModel);
//_createCurrentIdProc(db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
}
}
}
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();
}
/// <summary>
/// Synchronize database.
/// </summary>
/// <param name="businessModel">Business model.</param>
public override void Sync(APGenBusinessModelSection businessModel)
{
using (APDatabase db = new APDatabase(this))
{
try
{
db.BeginTrans();
foreach (APGenTable table in GetAllTables(businessModel))
{
_setScanTable(table.TableName);
DbCommand dbCmd = db.CreateSqlCommand("select table_name from user_tables where temporary = 'N' and table_name = '{0}'", table.TableName.ToUpper());
object id = dbCmd.ExecuteScalar();
if (id == null)
{
CreateTable(db, table);
}
else
{
ModifyTable(db, table);
}
}
SyncSequence(db, businessModel);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
}
}
}
/// <summary>
/// Synchronize database.
/// </summary>
/// <param name="businessModel">Business model.</param>
public override void Sync(APGenBusinessModelSection businessModel)
{
using (APDatabase db = new APDatabase(this))
{
try
{
db.BeginTrans();
foreach (APGenTable table in GetAllTables(businessModel))
{
_setScanTable(table.TableName);
DbCommand dbCmd = db.CreateSqlCommand("select id from sysobjects where type = 'U' and name = '{0}'", table.TableName);
object id = dbCmd.ExecuteScalar();
if (id == null)
CreateTable(db, table);
else
ModifyTable(db, table);
}
SyncCurrentId(db, businessModel);
//_createCurrentIdProc(db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
}
}
}
private void ModifyTable(APDatabase db, APGenTable table)
{
Dictionary <string, columninfo> dbColumns = _getColumns(db, table);
Dictionary <string, indexinfo> dbIndexes = _getIndexes(db, table);
bool isTableEmpty = _isTableEmpty(db, table);
// analysis columns
List <APGenColumn> listCreateColumn = new List <APGenColumn>();
List <APGenColumn> listModifyColumn = new List <APGenColumn>();
Dictionary <string, columninfo> deleteColumns = new Dictionary <string, columninfo>(dbColumns, StringComparer.InvariantCultureIgnoreCase);
foreach (APGenColumn column in table.Columns)
{
_setScanColumn(column.ColumnName);
string colname = column.ColumnName;
if (!dbColumns.ContainsKey(colname))
{
// db has not this column, wait for create.
listCreateColumn.Add(column);
}
else
{
deleteColumns.Remove(colname);
columninfo colinfo = dbColumns[colname];
if (column.IsNullable != colinfo.isnullable ||
column.DBDefaultValue != colinfo.dfvalue ||
DBTypeName(column) != colinfo.GetTypeFullName()
// no safe mode to change identity, so ingone change this
// || (column.IdentityType == APColumnIdentityType.Database ^ colinfo.is_identity)
)
{
listModifyColumn.Add(column);
}
}
}
// analysis indexes
List <APGenIndex> listCreateIndex = new List <APGenIndex>();
foreach (APGenIndex index in table.Indexes)
{
string ix_name = index.Name;
if (!dbIndexes.ContainsKey(ix_name))
{
// db has not this index, wait for create.
listCreateIndex.Add(index);
}
else
{
// db has this index and columns according fitted, then to nothing.
// elsewise, wait fo modify this index.
// drop in db indexes residual in dbIndexes.
indexinfo info = dbIndexes[ix_name];
string ix_keys = IndKeys(table, index, false);
if (info.indkeys.Equals(ix_keys, StringComparison.InvariantCultureIgnoreCase))
{
bool maybe = false;
foreach (APGenColumn column in listModifyColumn)
{
if (info.columns.ContainsKey(column.ColumnName))
{
listCreateIndex.Add(index);
maybe = true;
break;
}
}
if (!maybe)
{
dbIndexes.Remove(ix_name);
}
}
else
{
listCreateIndex.Add(index);
}
}
}
// analysis uniques
List <APGenIndex> listCreateUnique = new List <APGenIndex>();
foreach (APGenIndex index in table.Uniques)
{
string ix_name = index.Name;
if (!dbIndexes.ContainsKey(ix_name))
{
// db has not this unique, wait for create.
listCreateUnique.Add(index);
}
else
{
// db has this unique, then to nothing.
// elsewise, wait fo modify this unique.
// drop in db uniques residual in dbIndexes.
indexinfo info = dbIndexes[ix_name];
string uq_keys = IndKeys(table, index, true);
if (info.indkeys.Equals(uq_keys, StringComparison.InvariantCultureIgnoreCase))
{
bool maybe = false;
foreach (APGenColumn column in listModifyColumn)
{
if (info.columns.ContainsKey(column.ColumnName))
{
listCreateUnique.Add(index);
maybe = true;
break;
}
}
if (!maybe)
{
dbIndexes.Remove(ix_name);
}
}
else
{
listCreateUnique.Add(index);
}
}
}
// 1. dbIndexes for 'drop', but PK_ index analysis columns.
// 2. listCreateIndex for 'create'.
// 2. listCreateUnique for 'create'.
// process
string pkKeys = "";
foreach (APGenColumn column in table.PrimaryKeyColumns)
{
if (pkKeys != "")
{
pkKeys += ",";
}
pkKeys += column.ColumnName;
}
// 1. drop indexes
bool needAddPrimary = true;
foreach (indexinfo info in dbIndexes.Values)
{
if (info.indtype == indexType.Primary && info.indkeys.Equals(pkKeys, StringComparison.InvariantCultureIgnoreCase))
{
needAddPrimary = false;
}
else
{
_dropIndex(db, table, info);
}
}
// 2. drop columns
foreach (columninfo info in deleteColumns.Values)
{
_dropColumn(db, table, info);
}
// 3. modify columns
foreach (APGenColumn column in listModifyColumn)
{
_alterColumn(db, table, column, dbColumns[column.ColumnName]);
}
// 4. create columns
foreach (APGenColumn column in listCreateColumn)
{
_createColumn(db, table, column, isTableEmpty);
}
// 5. mayby primary key
if (needAddPrimary)
{
_createPrimaryKey(db, table);
}
// 6. create indexes
foreach (APGenIndex index in listCreateIndex)
{
_createIndex(db, table, index);
}
// 7. create unique
foreach (APGenIndex unique in listCreateUnique)
{
_createUnique(db, table, unique);
}
}
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 void SyncCurrentId(APDatabase db, APGenBusinessModelSection businessModel)
{
List<string> tcnames = new List<string>();
DbCommand dbCmd = db.CreateSqlCommand("select {1} from {0}", currentIdTable, currentIdName);
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
tcnames.Add(reader.GetString(0));
}
}
foreach (APGenTable table in businessModel.Tables)
{
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Provider
&& businessModel.CanIdentityRelyOnProvider(column)
&& column.ParsedType != typeof(Guid))
{
string tcname = table.TableName + "." + column.ColumnName;
if (!tcnames.Contains(tcname))
{
dbCmd = db.CreateSqlCommand("insert into {0} ({1}, {2}) values ('{3}', '{4}')",
currentIdTable, currentIdName, currentIdValue,
tcname, column.ProviderIdentityBase);
dbCmd.ExecuteNonQuery();
}
}
}
}
}
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 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 void _tempSql(string sql)
{
DbCommand dbCmd;
using (APDatabase dbtemp = new APDatabase(this))
{
try
{
dbtemp.BeginTrans();
dbCmd = dbtemp.CreateSqlCommand(sql);
dbCmd.ExecuteNonQuery();
dbtemp.Commit();
}
catch
{
dbtemp.Rollback();
}
}
}
private void _createPrimaryKey(APDatabase db, APGenTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("alter table {0} add constraint {1} primary key (", table.TableName, PK_name(table));
foreach (APGenColumn column in table.PrimaryKeyColumns)
{
sb.AppendFormat("\r\n\t{0},", column.ColumnName);
}
sb.Length--;
sb.Append("\r\n)");
// alter table tablen_ame add constraint PK_table_name primary key(
// column_name,
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private void _createTable(APDatabase db, APGenTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("create table {0} (", table.TableName);
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Database)
sb.AppendFormat("\r\n\t{0} {1} identity {2} {3},",
column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column));
else
sb.AppendFormat("\r\n\t{0} {1} {2} {3},",
column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column));
}
sb.Length--;
sb.Append("\r\n)");
// create table table_name (
// column_name column_type [identity] [not] null [constraint ... default ...],
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private Dictionary <string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
{
OracleCommand dbCmd = db.CreateSqlCommand(
@"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C')
from user_ind_columns col
left join user_ind_expressions expr
on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position
left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name
where col.table_name = '{0}'
order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand;
dbCmd.InitialLONGFetchSize = -1;
Dictionary <string, indexinfo> dbIndexes = new Dictionary <string, indexinfo>(StringComparer.InvariantCultureIgnoreCase);
using (OracleDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
string name = Convert.ToString(reader.GetValue(0));
string col = Convert.ToString(reader.GetValue(1));
if (!reader.IsDBNull(2))
{
col = reader.GetString(2);
col = col.Substring(1, col.Length - 2);
}
bool is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC";
string type = Convert.ToString(reader.GetValue(4));
indexinfo data;
if (dbIndexes.ContainsKey(name))
{
data = dbIndexes[name];
}
else
{
data = new indexinfo()
{
indname = name
};
if (type == "P")
{
data.indtype = indexType.Primary;
}
else if (type == "U")
{
data.indtype = indexType.Unique;
}
else
{
data.indtype = indexType.Index;
}
dbIndexes.Add(name, data);
}
data.columns.Add(col, is_descending_key);
}
}
foreach (var item in dbIndexes.Values)
{
item.pickKeys();
}
return(dbIndexes);
}
private void _dropIndex(APDatabase db, APGenTable table, indexinfo info)
{
if (info.indtype != indexType.Index)
{
_dropConstraint(db, table, info.indname);
}
else
{
DbCommand dbCmd = db.CreateSqlCommand("drop index {1} on {0}", table.TableName, info.indname);
dbCmd.ExecuteNonQuery();
}
}
private void _dropConstraint(APDatabase db, APGenTable table, string name)
{
DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop constraint {1}", table.TableName, name);
dbCmd.ExecuteNonQuery();
}
private void _createUnique(APDatabase db, APGenTable table, APGenIndex index)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("alter table {0} add constraint {1} unique (", table.TableName, index.Name/* UQ_name(table, index)*/);
foreach (APGenOrder order in index.Orders)
{
sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
}
sb.Length--;
sb.Append("\r\n)");
// alter table table_name add constraint uq_name (
// column_name DESC,
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private void _createIndex(APDatabase db, APGenTable table, APGenIndex index)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("create index {0} on {1} (", index.Name/* IX_name(table, index)*/, table.TableName);
foreach (APGenOrder order in index.Orders)
{
sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
}
sb.Length--;
sb.Append("\r\n)");
// create index index_name on (
// column_name [ASC|DESC],
// ...
// )
DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
dbCmd.ExecuteNonQuery();
}
private void _dropConstraint(APDatabase db, APGenTable table, string name)
{
DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop constraint {1}", table.TableName, name);
dbCmd.ExecuteNonQuery();
}
/// <summary>
/// Execute.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="db">The database.</param>
/// <returns>The reader.</returns>
public static IDataReader executeReader(this APSqlSelectCommand command, APDatabase db)
{
return(db.ExecuteReader(command));
}
/// <summary>
/// Execute.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="db">The database.</param>
public static void execute(this APSqlDeleteCommand command, APDatabase db)
{
db.ExecuteNonQuery(command);
}
/// <summary>
/// Execute.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="db">The database.</param>
public static void execute(this APSqlUpdateCommand command, APDatabase db)
{
db.ExecuteNonQuery(command);
}
private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table)
{
OracleCommand dbCmd = db.CreateSqlCommand(
@"select
column_id,
column_name,
data_type,
data_length,
nvl(data_precision, 0),
nvl(data_scale, 0),
nullable,
nvl(default_length, 0),
data_default
from user_tab_columns
where table_name = '{0}'
order by column_id", table.TableName.ToUpper()) as OracleCommand;
dbCmd.InitialLONGFetchSize = -1;
Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>(StringComparer.InvariantCultureIgnoreCase);
using (OracleDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
int idx = 0;
columninfo data = new columninfo(){
colid = Convert.ToInt32(reader.GetValue(idx++)),
colname = Convert.ToString(reader.GetValue(idx++)),
typename = Convert.ToString(reader.GetValue(idx++)).ToLower(),
length = Convert.ToInt32(reader.GetValue(idx++)),
xprec = Convert.ToInt32(reader.GetValue(idx++)),
xscale = Convert.ToInt32(reader.GetValue(idx++)),
isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y",
dflength = Convert.ToInt32(reader.GetValue(idx++)),
dfvalue = "",
};
if (data.dflength > 0)
data.dfvalue = reader.GetString(idx++).Trim();
data.Resolve();
dbColumns.Add(data.colname, data);
}
}
return dbColumns;
}
private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
{
DbCommand dbCmd = db.CreateSqlCommand(
@"select
idx.name,
col.name as col,
idxCol.is_descending_key,
idx.is_unique,
idx.is_primary_key,
idx.is_unique_constraint
from
sys.indexes as idx
inner join sys.index_columns as idxCol on (idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id)
inner join sys.columns as col on (idx.object_id = col.object_id AND idxCol.column_id = col.column_id)
where idx.object_id = object_id('{0}')
order by idx.name", table.TableName);
Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>();
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
string name = Convert.ToString(reader.GetValue(0));
string col = Convert.ToString(reader.GetValue(1));
bool is_descending_key = Convert.ToBoolean(reader.GetValue(2));
bool is_unique = Convert.ToBoolean(reader.GetValue(3));
bool is_primary_key = Convert.ToBoolean(reader.GetValue(4));
bool is_unique_constraint = Convert.ToBoolean(reader.GetValue(5));
indexinfo data;
if (dbIndexes.ContainsKey(name))
{
data = dbIndexes[name];
}
else
{
data = new indexinfo() { indname = name };
if (is_primary_key)
data.indtype = indexType.Primary;
else if (is_unique_constraint)
data.indtype = indexType.Unique;
else
data.indtype = indexType.Index;
dbIndexes.Add(name, data);
}
data.columns.Add(col, is_descending_key);
}
}
foreach (var item in dbIndexes.Values)
{
item.pickKeys();
}
return dbIndexes;
}
/// <summary>
/// Query extensions.
/// </summary>
/// <param name="db">APDatabase.</param>
/// <param name="command">The 'SELECT' command.</param>
/// <returns>The count of "SELECT COUNT(*)" command.</returns>
public static int SizeOf(this APDatabase db, APSqlSelectCommand command)
{
return(db.ExecuteSizeOfSelect(command));
}
private void ModifyTable(APDatabase db, APGenTable table)
{
Dictionary<string, columninfo> dbColumns = _getColumns(db, table);
Dictionary<string, indexinfo> dbIndexes = _getIndexes(db, table);
bool isTableEmpty = _isTableEmpty(db, table);
// analysis columns
List<APGenColumn> listCreateColumn = new List<APGenColumn>();
List<APGenColumn> listModifyColumn = new List<APGenColumn>();
Dictionary<string, columninfo> deleteColumns = new Dictionary<string, columninfo>(dbColumns);
foreach (APGenColumn column in table.Columns)
{
_setScanColumn(column.ColumnName);
string colname = column.ColumnName;
if (!dbColumns.ContainsKey(colname))
{
// db has not this column, wait for create.
listCreateColumn.Add(column);
}
else
{
deleteColumns.Remove(colname);
columninfo colinfo = dbColumns[colname];
if (column.IsNullable != colinfo.isnullable
|| column.DBDefaultValue != colinfo.dfvalue
|| DBTypeName(column) != colinfo.GetTypeFullName()
// no safe mode to change identity, so ingone change this
// || (column.IdentityType == APColumnIdentityType.Database ^ colinfo.is_identity)
)
{
listModifyColumn.Add(column);
}
}
}
// analysis indexes
List<APGenIndex> listCreateIndex = new List<APGenIndex>();
foreach (APGenIndex index in table.Indexes)
{
string ix_name = index.Name;// IX_name(table, index);
if (!dbIndexes.ContainsKey(ix_name))
{
// db has not this index, wait for create.
listCreateIndex.Add(index);
}
else
{
// db has this index and columns according fitted, then to nothing.
// elsewise, wait fo modify this index.
// drop in db indexes residual in dbIndexes.
indexinfo info = dbIndexes[ix_name];
string ix_keys = IndKeys(table, index);
if (info.indkeys == ix_keys)
{
bool maybe = false;
foreach (APGenColumn column in listModifyColumn)
{
if (info.columns.ContainsKey(column.ColumnName))
{
listCreateIndex.Add(index);
maybe = true;
break;
}
}
if (!maybe)
dbIndexes.Remove(ix_name);
}
else
{
listCreateIndex.Add(index);
}
}
}
// analysis uniques
List<APGenIndex> listCreateUnique = new List<APGenIndex>();
foreach (APGenIndex index in table.Uniques)
{
string ix_name = index.Name;// UQ_name(table, index);
if (!dbIndexes.ContainsKey(ix_name))
{
// db has not this unique, wait for create.
listCreateUnique.Add(index);
}
else
{
// db has this unique, then to nothing.
// elsewise, wait fo modify this unique.
// drop in db uniques residual in dbIndexes.
indexinfo info = dbIndexes[ix_name];
string uq_keys = IndKeys(table, index);
if (info.indkeys == uq_keys)
{
bool maybe = false;
foreach (APGenColumn column in listModifyColumn)
{
if (info.columns.ContainsKey(column.ColumnName))
{
listCreateUnique.Add(index);
maybe = true;
break;
}
}
if (!maybe)
dbIndexes.Remove(ix_name);
}
else
{
listCreateUnique.Add(index);
}
}
}
// 1. dbIndexes for 'drop', but PK_ index analysis columns.
// 2. listCreateIndex for 'create'.
// 2. listCreateUnique for 'create'.
// process
string pkKeys = "";
foreach (APGenColumn column in table.PrimaryKeyColumns)
{
if (pkKeys != "") pkKeys += ",";
pkKeys += column.ColumnName;
}
// 1. drop indexes
bool needAddPrimary = true;
foreach (indexinfo info in dbIndexes.Values)
{
if (info.indtype == indexType.Primary && info.indkeys == pkKeys)
needAddPrimary = false;
else
_dropIndex(db, table, info);
}
// 2. drop columns
foreach (columninfo info in deleteColumns.Values)
{
_dropColumn(db, table, info);
}
// 3. modify columns
foreach (APGenColumn column in listModifyColumn)
{
_alterColumn(db, table, column, dbColumns[column.ColumnName]);
}
// 4. create columns
foreach (APGenColumn column in listCreateColumn)
{
_createColumn(db, table, column, isTableEmpty);
}
// 5. mayby primary key
if (needAddPrimary)
{
_createPrimaryKey(db, table);
}
// 6. create indexes
foreach (APGenIndex index in listCreateIndex)
{
_createIndex(db, table, index);
}
// 7. create unique
foreach (APGenIndex unique in listCreateUnique)
{
_createUnique(db, table, unique);
}
}
private void SyncSequence(APDatabase db, APGenBusinessModelSection businessModel)
{
List<string> seqnames = new List<string>();
DbCommand dbCmd = db.CreateSqlCommand("select sequence_name from user_sequences");
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
seqnames.Add(reader.GetString(0));
}
}
foreach (APGenTable table in businessModel.Tables)
{
foreach (APGenColumn column in table.Columns)
{
if (column.IdentityType == APColumnIdentityType.Provider
&& businessModel.CanIdentityRelyOnProvider(column)
&& column.ParsedType != typeof(Guid))
{
string seqname = SEQ_name(table.TableName, column.ColumnName).ToUpper();
if (!seqnames.Contains(seqname))
{
dbCmd = db.CreateSqlCommand("create sequence {0} increment by 1 start with {1} nomaxvalue nocycle nocache",
seqname, column.ProviderIdentityBase);
dbCmd.ExecuteNonQuery();
}
}
}
}
}
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();
}
}
/// <summary>
/// Execute.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="db">The database.</param>
/// <returns>The scale.</returns>
public static object executeScale(this APSqlSelectCommand command, APDatabase db)
{
return(db.ExecuteScalar(command));
}
private bool _isTableEmpty(APDatabase db, APGenTable table)
{
DbCommand dbCmd = db.CreateSqlCommand(
@"select count(*)
from {0}", table.TableName);
int count = Convert.ToInt32(dbCmd.ExecuteScalar());
return count == 0;
}
/// <summary>
/// Execute
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command"></param>
/// <param name="db"></param>
/// <param name="fillup"></param>
/// <returns></returns>
public static IEnumerable <T> query <T>(this APSqlSelectCommand command, APDatabase db, Func <IDataReader, T> fillup)
{
return(db.Query(command, fillup));
}
private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table)
{
DbCommand dbCmd = db.CreateSqlCommand(
@"select
col.colid,
col.name as colname,
tp.name as typename,
tp.variable,
col.length,
col.xprec,
col.xscale,
col.isnullable,
df.name as dfname,
df.definition as dfvalue,
id.is_identity
from
syscolumns as col
inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname'
left join sys.default_constraints as df on col.cdefault = df.object_id
left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id
where col.id = object_id('{0}')
order by col.colid", table.TableName);
Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>();
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
int idx = 0;
columninfo data = new columninfo()
{
colid = Convert.ToInt32(reader.GetValue(idx++)),
colname = Convert.ToString(reader.GetValue(idx++)),
typename = Convert.ToString(reader.GetValue(idx++)),
variable = Convert.ToBoolean(reader.GetValue(idx++)),
length = Convert.ToInt32(reader.GetValue(idx++)),
xprec = Convert.ToInt32(reader.GetValue(idx++)),
xscale = Convert.ToInt32(reader.GetValue(idx++)),
isnullable = Convert.ToBoolean(reader.GetValue(idx++)),
dfname = Convert.ToString(reader.GetValue(idx++)),
dfvalue = Convert.ToString(reader.GetValue(idx++)),
is_identity = reader.IsDBNull(idx++) ? false : true,
};
data.Resolve();
dbColumns.Add(data.colname, data);
}
}
return dbColumns;
}
private void _dropColumn(APDatabase db, APGenTable table, columninfo info)
{
if (info.dfname != "")
_dropConstraint(db, table, info.dfname);
DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname);
dbCmd.ExecuteNonQuery();
}
private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
{
OracleCommand dbCmd = db.CreateSqlCommand(
@"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C')
from user_ind_columns col
left join user_ind_expressions expr
on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position
left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name
where col.table_name = '{0}'
order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand;
dbCmd.InitialLONGFetchSize = -1;
Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>(StringComparer.InvariantCultureIgnoreCase);
using (OracleDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
string name = Convert.ToString(reader.GetValue(0));
string col = Convert.ToString(reader.GetValue(1));
if (!reader.IsDBNull(2))
{
col = reader.GetString(2);
col = col.Substring(1, col.Length - 2);
}
bool is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC";
string type = Convert.ToString(reader.GetValue(4));
indexinfo data;
if (dbIndexes.ContainsKey(name))
{
data = dbIndexes[name];
}
else
{
data = new indexinfo() { indname = name };
if (type == "P")
data.indtype = indexType.Primary;
else if (type == "U")
data.indtype = indexType.Unique;
else
data.indtype = indexType.Index;
dbIndexes.Add(name, data);
}
data.columns.Add(col, is_descending_key);
}
}
foreach (var item in dbIndexes.Values)
{
item.pickKeys();
}
return dbIndexes;
}
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
}
/// <summary>
/// Execute.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="db">The database.</param>
/// <returns>The scale.</returns>
public static object executeScale(this APSqlInsertCommand command, APDatabase db)
{
return db.ExecuteScalar(command);
}