private void GetColumnConstraints(TableMap map)
{
// primary key
string sql = String.Format(selectPrimaryKeyConstraints, map.TableId);
SqlResult sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
int columnId = sr.GetInt(i, "column_id");
FieldMap fm = map.Fields.FindColumnById(columnId);
if (fm != null)
{
fm.SetIsPrimaryKey(true);
}
}
// foreign key
sql = String.Format(selectForeignKeyConstraints, map.TableId);
sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
int columnId = sr.GetInt(i, "column_id");
FieldMap fm = map.Fields.FindColumnById(columnId);
if (fm != null)
{
fm.SetForeignKeyTableName(sr.GetString(i, "PT"));
fm.SetForeignKeyColumnName(sr.GetString(i, "PK"));
}
}
}
protected void GetIdentityInformation(TableMap map)
{
if (map != null)
{
// also obtain identity column information
GentleSqlFactory sf = provider.GetSqlFactory();
try
{
string tableName = sf.GetTableName(map.TableName);
foreach (FieldMap fm in map.Fields)
{
if (fm.IsPrimaryKey)
{
if (!map.IsView)
{
SqlResult sr = broker.Execute(String.Format(selectIdentityInfo, tableName, fm.ColumnName));
fm.SetIsAutoGenerated(sr.GetInt(0, "IsIdentity") == 1);
}
}
}
}
catch (Exception e)
{
Check.LogWarning(LogCategories.Metadata, "Unable to determine whether PK column of table {0} is an identity column.",
map.TableName);
Check.LogWarning(LogCategories.Metadata, e.Message);
}
}
}
private void GetColumnData(TableMap map)
{
string sql = String.Format(selectColumns, map.TableId);
SqlResult sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
string column = sr.GetString(i, "column_name");
FieldMap fm = map.GetFieldMapFromColumn(column);
if (fm == null)
{
fm = new FieldMap(map, column);
map.Fields.Add(fm);
}
bool isNullable = sr.GetString(i, "nulls").Equals("Y");
bool isAutoGenerated = sr.GetString(i, "default").ToLower() == "autoincrement";
fm.ColumnId = sr.GetInt(i, "column_id");
fm.SetDbType(sr.GetString(i, "domain_name"), false);
fm.SetIsNullable(isNullable);
fm.SetIsAutoGenerated(isAutoGenerated);
fm.SetSize(sr.GetInt(i, "width"));
}
}
private void GetColumnData(TableMap map)
{
string sql = String.Format(selectColumns, map.TableName);
SqlStatement stmt = broker.GetStatement(sql);
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
for (int i = 0; i < sr.Rows.Count; i++)
{
// returns columns: Field, Type, TypeSize, FieldSize, NotNull, HasDefault, Default
string columnName = sr.GetString(i, "field");
// get or create FieldMap for column
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
bool isNullable = sr.GetString(i, "notnull").Trim().ToLower().StartsWith("f");
if (fm != null)
{
bool hasDefault = sr.GetBoolean(i, "hasdefault");
fm.SetDbType(sr.GetString(i, "type"), false);
int size = ExtractSize(sr.GetInt(i, "typesize"), sr.GetInt(i, "fieldsize"));
fm.SetSize(size);
fm.SetIsNullable(isNullable);
//fm.SetIsPrimaryKey( sr.GetString( i, "Key" ).Equals( "PRI" ) );
// fm.SetIsForeignKey( sr.GetString( i, "Key" ).Equals( "FOR" ) );
//if( fm.IsPrimaryKey )
// fm.SetIsAutoGenerated( sr.GetString( i, "Key" ).Equals( "auto_increment" ) );
//if( sr.GetString( i, "HasDefault" ).Equals( "t" ) )
// fm.SetMagicValue( sr.GetObject( i, "Default" ) );
}
}
}
private void GetColumnData(TableMap map)
{
string sql = String.Format(selectColumns, map.TableId);
SqlResult sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
string column = sr.GetString(i, "column_name");
FieldMap fm = map.GetFieldMapFromColumn(column);
bool isNullable = sr.GetString(i, "nulls").Equals("Y");
if (fm != null)
{
fm.ColumnId = sr.GetInt(i, "column_id");
fm.SetDbType(sr.GetString(i, "domain_name"), false);
fm.SetIsNullable(isNullable);
fm.SetSize(sr.GetInt(i, "width"));
}
else // raise an error if we've detected a database/type mismatch
{
// TODO disabled due to code restructuring
// Check.Verify( isNullable, Error.NoPropertyForNotNullColumn, column, map.Type );
}
}
}
"t.table_id = c.table_id and t.creator = u.user_id and t.table_id={0} and pkey in('Y','M')"; // table id
/// <summary>
/// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
/// interface it implements a description of this method.
/// </summary>
public override void Analyze(string tableName)
{
try
{
bool isSingleRun = tableName != null;
SqlResult sr = broker.Execute(selectTables);
for (int i = 0; i < sr.Rows.Count; i++)
{
try
{
string dbTableName = sr.GetString(i, "table_name");
if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
{
TableMap map = GetTableMap(dbTableName);
if (map == null)
{
map = new TableMap(provider, dbTableName);
maps[dbTableName.ToLower()] = map;
}
map.TableId = sr.GetInt(i, "table_id");
// get column information for this table
GetColumnData(map);
// get additional column information for this table
GetColumnConstraints(map);
// abort loop if analyzing single table only
if (isSingleRun)
{
break;
}
}
}
catch (GentleException fe)
{
// ignore errors caused by tables found in db but for which no map exists
// TODO this should be a config option
if (fe.Error != Error.NoObjectMapForTable)
{
throw fe;
}
}
}
}
catch (Exception e)
{
Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
}
}
/// <summary>
/// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
/// interface it implements a description of this method.
/// </summary>
public override void Analyze(string tableName)
{
try
{
bool isSingleRun = tableName != null;
string selectSingle = isSingleRun ? String.Format(SELECT_SINGLE, tableName) : String.Empty;
// Check Oracle version and select appropriate SQL-Syntax
SqlResult sr = broker.Execute(ORA_VERSION_SELECT);
// string ver = sr.GetString( 0, "Version" ).Substring( 0, 1 );
// int version = Convert.ToInt32( sr.GetString( 0, "Version" ).Substring( 0, 1 ) );
string ver = sr.GetString(0, "Version");
int indexOfDot = ver.IndexOf(".");
if (indexOfDot < 0)
{
throw new GentleException(Error.DeveloperError, "Unable to determine Oracle database version.");
}
int version = Convert.ToInt32(ver.Substring(0, indexOfDot));
string select;
string selectReferences;
if (version < 9)
{
// If Oracle version == '8.1.6' use no-views selectReferences
if (ver.Substring(0, 5).CompareTo("8.1.6") == 0)
{
selectReferences = ORA816_SELECT_REFERENCES;
}
else
{
selectReferences = ORA8_SELECT_REFERENCES;
}
select = ORA8_SELECT + selectSingle + ORDER_BY;
}
else
{
select = ORA9_SELECT + selectSingle + ORDER_BY;
selectReferences = ORA9_SELECT_REFERENCES;
}
sr = broker.Execute(select);
// process result set using columns:
// TableName, ColumnName, Type, Size, IsNullable, DefaultValue,
// ConstraintName, ConstraintReference, ConstraintType, UpdateRule, DeleteRule
for (int i = 0; i < sr.Rows.Count; i++)
{
try
{
string dbTableName = sr.GetString(i, "TableName");
if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
{
// get or create TableMap for table
TableMap map = GetTableMap(dbTableName);
if (map == null)
{
map = new TableMap(provider, dbTableName);
maps[dbTableName.ToLower()] = map;
}
// get or create FieldMap for column
string columnName = sr.GetString(i, "ColumnName");
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
// get basic column information
fm.SetDbType(sr.GetString(i, "Type"), false);
fm.SetIsNullable(GetBoolean(sr.GetString(i, "IsNullable")));
if (sr[i, "Size"] != null)
{
if (fm.DbType == (long)OracleDbType.Clob)
{
//Max 4GB
//Preferred size 4294967296
fm.SetSize(int.MaxValue);
}
else
{
fm.SetSize(sr.GetInt(i, "Size"));
}
}
// get column constraint infomation
if (sr[i, "ConstraintName"] != null)
{
string typ = sr.GetString(i, "ConstraintType");
if (typ.ToLower().Equals("p"))
{
fm.SetIsPrimaryKey(true);
}
else if (typ.ToLower().Equals("r"))
{
string conref = sr.GetString(i, "ConstraintReference");
SqlResult res = broker.Execute(String.Format(selectReferences, conref));
fm.SetForeignKeyTableName(res.GetString(0, "ChildTable"));
fm.SetForeignKeyColumnName(res.GetString(0, "ChildColumn"));
}
}
}
}
catch (GentleException fe)
{
// ignore errors caused by tables found in db but for which no map exists
// TODO this should be a config option
if (fe.Error != Error.NoObjectMapForTable)
{
throw;
}
}
}
}
catch (Exception e)
{
Check.LogInfo(LogCategories.General, "Using provider {0} and connectionString {1}.",
provider.Name, provider.ConnectionString);
Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
}
}
public override void Analyze(string tableName)
{
GentleSqlFactory sf = provider.GetSqlFactory();
try
{
bool isSingleRun = tableName != null;
// Create foreign key statement
SqlStatement fk = broker.GetStatement(StatementType.Select, selectReferences);
sf.AddParameter(fk.Command, "TableName", (long)FbDbType.VarChar);
sf.AddParameter(fk.Command, "ColumnName", (long)FbDbType.VarChar);
// Get tables information
SqlResult sr = broker.Execute(select);
// process result set
for (int i = 0; i < sr.Rows.Count; i++)
{
try
{
string dbTableName = sr.GetString(i, "TableName").Trim();
if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
{
TableMap map = GetTableMap(dbTableName);
if (map == null)
{
map = new TableMap(provider, dbTableName);
maps[dbTableName.ToLower()] = map;
}
// get column information for this table
string columnName = sr.GetString(i, "ColumnName").Trim();
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
FbDbType type = GetFbDbType(
sr.GetInt(i, "ColumnDataType"),
sr[i, "ColumnSubType"] != null ? sr.GetInt(i, "ColumnSubType") : 0,
sr[i, "ColumnScale"] != null ? sr.GetInt(i, "ColumnScale") : 0);
fm.SetDbType((long)type);
if (sr[i, "NullFlag"] == null)
{
fm.SetIsNullable(true);
}
else
{
fm.SetIsNullable(false);
}
if (sr[i, "ColumnSize"] != null)
{
switch (type)
{
case FbDbType.Binary:
case FbDbType.Text:
fm.SetSize(Int32.MaxValue);
break;
default:
fm.SetSize(sr.GetInt(i, "ColumnSize"));
break;
}
}
if (sr.GetInt(i, "PrimaryKey") > 0)
{
fm.SetIsPrimaryKey(true);
}
if (sr.GetInt(i, "ForeignKey") > 0)
{
fk.SetParameter("TableName", map.TableName.ToUpper());
fk.SetParameter("ColumnName", columnName.ToUpper());
SqlResult res = fk.Execute();
fm.SetForeignKeyTableName(res.GetString(0, "FKTableName").Trim());
fm.SetForeignKeyColumnName(res.GetString(0, "FKColumnName").Trim());
}
}
}
catch (GentleException fe)
{
// ignore errors caused by tables found in db but for which no map exists
// TODO this should be a config option
if (fe.Error != Error.NoObjectMapForTable)
{
throw fe;
}
}
}
}
catch (Exception e)
{
Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
}
}
/// <summary>
/// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
/// interface it implements a description of this method.
/// </summary>
public override void Analyze(string tableName)
{
GentleSqlFactory sf = provider.GetSqlFactory();
try
{
bool isSingleRun = tableName != null;
// don't quote reserved words here (table name is just a string parameter)
string sql = isSingleRun ? select + String.Format(selectSingle, tableName) : select;
SqlResult sr = broker.Execute(sql, null, null);
// process result set using columns:
// TableName, ColumnName, Type, Size, IsNullable, DefaultValue,
// ConstraintName, ConstraintReference, ConstraintType,
// UpdateRule, DeleteRule, TableType
for (int i = 0; i < sr.Rows.Count; i++)
{
try
{
string dbTableName = sr.GetString(i, "tablename");
if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
{
TableMap map = GetTableMap(dbTableName);
if (map == null)
{
map = new TableMap(provider, dbTableName);
maps[dbTableName.ToLower()] = map;
}
map.IsView = sr.GetString(i, "TableType") == "VIEW";
// get column information for this table
string columnName = sr.GetString(i, "ColumnName");
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
// get basic column information
fm.SetDbType(sr.GetString(i, "Type"), false); // sql server is always false
fm.SetIsNullable(GetBoolean(sr.GetString(i, "IsNullable")));
fm.SetIsAutoGenerated(sr.GetString(i, "DefaultValue").Length > 0 ? true : false);
if (sr[i, "Size"] != null && fm.DbType != (long)SqlDbType.Text)
{
fm.SetSize(sr.GetInt(i, "Size"));
}
// get column constraint infomation
if (sr[i, "ConstraintName"] != null)
{
string type = sr.GetString(i, "ConstraintType");
if (type.ToLower().Equals("primary key"))
{
fm.SetIsPrimaryKey(true);
}
else if (type.ToLower().Equals("foreign key"))
{
string conref = sr.GetString(i, "ConstraintReference");
if (conref.StartsWith("IDX"))
{
string fkRef = sr.GetString(i, "ConstraintName");
if (fkRef != null && fkRef.StartsWith("FK"))
{
conref = fkRef;
}
}
SqlResult res = broker.Execute(String.Format(selectReferences, conref), null, null);
if (res.ErrorCode == 0 && res.RowsContained == 1)
{
fm.SetForeignKeyTableName(res.GetString(0, "TableName"));
fm.SetForeignKeyColumnName(res.GetString(0, "ColumnName"));
}
else
{
if (res.RowsContained == 0)
{
// see GOPF-155 for additional information
Check.LogWarning(LogCategories.Metadata,
"Unable to obtain foreign key information for column {0} of table {1}.",
fm.ColumnName, map.TableName);
}
else
{
Check.LogWarning(LogCategories.Metadata, "Gentle 1.x does not support composite foreign keys.");
}
}
}
}
if (map.IsView)
{
// TODO
// process backing table members and infer PK/identity info
// requires that tables be processed before views!
//
//string sv = String.Format( selectViewDependencies, map.TableName );
//SqlResult res = broker.Execute( sv );
}
}
}
catch (GentleException fe)
{
// ignore errors caused by tables found in db but for which no map exists
// TODO this should be a config option
if (fe.Error != Error.NoObjectMapForTable)
{
throw;
}
}
}
}
catch (Exception e)
{
Check.LogInfo(LogCategories.General, "Using provider {0} and connectionString {1}.",
provider.Name, provider.ConnectionString);
Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
}
}
