private void InitializeXpMsVer()
{
// permit disabling of these checks
if (xp_msverInitialized || GentleSettings.AnalyzerLevel == AnalyzerLevel.None)
{
return;
}
try
{
SqlCommand cmd = provider.GetCommand() as SqlCommand;
cmd.CommandType = CommandType.StoredProcedure;
// retrieve name
cmd.Parameters.AddWithValue("@optname", NameProperty);
SqlStatement stmt = new SqlStatement(StatementType.Unknown, cmd, xp_msverProcedureName);
SqlResult sr = provider.ExecuteStatement(stmt);
name = sr.GetString(0, 3);
// retrieve version
cmd.Parameters["@optname"].Value = VersionProperty;
stmt = new SqlStatement(StatementType.Unknown, cmd, xp_msverProcedureName);
sr = provider.ExecuteStatement(stmt);
version = new Version(sr.GetString(0, 3));
}
finally
{
xp_msverInitialized = true;
}
}
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"));
}
}
}
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, Null, Key, Default, Extra
string columnName = sr.GetString(i, "Field");
FieldMap fm = map.GetFieldMapFromColumn(columnName);
if (fm == null)
{
fm = new FieldMap(map, columnName);
map.Fields.Add(fm);
}
if (fm != null)
{
string typeinfo = sr.GetString(i, "Type");
bool isUnsigned;
fm.SetDbType(ExtractType(typeinfo, out isUnsigned), isUnsigned);
if (fm.DbType == (long)MySqlDbType.Enum)
{
fm.HandleEnumAsString = true;
}
fm.SetSize(ExtractSize(typeinfo));
fm.SetIsNullable(sr.GetString(i, "Null").Equals("YES"));
fm.SetIsPrimaryKey(sr.GetString(i, "Key").Equals("PRI"));
if (fm.IsPrimaryKey)
{
fm.SetIsAutoGenerated(sr.GetString(i, "Extra").Equals("auto_increment"));
}
}
else // raise an error if we've detected a database/type mismatch
{
bool hasDefault = sr.GetObject(i, "Default") != null;
// TODO disabled due to code restructuring
// Check.Verify( isNullable || hasDefault, Error.NoPropertyForNotNullColumn, column, map.Type );
}
}
}
private void UpdateTableMapWithColumnInformation(TableMap map)
{
SqlResult sr = broker.Execute(String.Format(selectColumns, map.TableName), null, null);
// process result set using columns: cid, name, type, notnull, dflt_value, pk
for (int i = 0; i < sr.RowsContained; i++)
{
string columnName = sr.GetString(i, "name");
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(!sr.GetBoolean(i, "notnull"));
fm.SetIsPrimaryKey(sr.GetBoolean(i, "pk"));
fm.SetIsAutoGenerated(fm.IsPrimaryKey && (fm.Type == typeof(int) || fm.Type == typeof(long)));
}
}
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 );
}
}
}
public override void Analyze(string tableName)
{
try
{
bool isSingleRun = tableName != null;
SqlStatement stmt = broker.GetStatement(selectTables);
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
for (int i = 0; i < sr.Rows.Count; i++)
{
try
{
string dbTableName = sr.GetString(i, 0);
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
GetColumnData(map);
// get foreign key information
GetConstraintData(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.");
}
}
private void UpdateTableMapWithIndexInformation(TableMap map)
{
SqlResult sr = broker.Execute(String.Format(selectIndexes, map.TableName), null, null);
// process result set using columns: name, unique
for (int i = 0; i < sr.RowsContained; i++)
{
string indexName = sr.GetString(i, "name");
SqlResult indexInfo = broker.Execute(String.Format(selectIndex, map.TableName), null, null);
// process result set using columns: seqno, cid, name
for (int indexColumn = 0; indexColumn < sr.RowsContained; indexColumn++)
{
// fm.SetIsAutoGenerated( sr.GetString( i, "dflt_value" ).Length > 0 ? true : false );
}
}
}
/// <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;
string sql = isSingleRun ? String.Format("{0} and name = '{1}'", selectTables, tableName) : selectTables;
SqlResult sr = broker.Execute(sql, null, null);
for (int row = 0; row < sr.RowsContained; row++)
{
tableName = sr.GetString(row, 0);
// get TableMap for current table
TableMap map = GetTableMap(tableName);
if (map == null)
{
map = new TableMap(provider, tableName);
maps[tableName.ToLower()] = map;
}
// get column information
UpdateTableMapWithColumnInformation(map);
// get foreign key information
UpdateTableMapWithForeignKeyInformation(map);
}
}
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.");
}
}
/// <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.");
}
}
private void GetConstraintData(TableMap map)
{
SqlStatement stmt = broker.GetStatement(String.Format(selectConstraints, map.TableName));
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
if (sr.Rows.Count == 1)
{
// returns columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length,
// Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Create_options,
// Comment (as "InnoDB free: 3072 kB; (ListId) REFER test/List(ListId)"
string comment = sr.GetString(0, 1); // column 1 is either "Create table" or "Create view"
if (comment != null && comment.Length > 5)
{
string[] comments = comment.Split(',');
foreach (string cmt in comments)
{
string tmp = cmt.Trim();
// samples:
// "(Column) REFER database/Table(Column)"
// "(`Column`) REFER `database/Table`(`Column`)"
string pattern = @"\(`?(?<column>\w+)`?\) REFER .*/(?<fkTable>\w+)[`\s]+\(`?(?<fkColumn>\w+)`?\)";
Regex regex = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled);
Match m = regex.Match(tmp);
if (m.Success)
{
FieldMap fm = map.GetFieldMapFromColumn(m.Groups["column"].Value);
if (fm != null)
{
fm.SetForeignKeyTableName(m.Groups["fkTable"].Value);
fm.SetForeignKeyColumnName(m.Groups["fkColumn"].Value);
}
}
else
{
//CONSTRAINT `fk_employee_type_employee` FOREIGN KEY (`employee_type_id`) REFERENCES `employee_type` (`employee_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
pattern = @"[\s\w]FOREIGN KEY\s\(`?(?<column>\w+)`?\) REFERENCES `?(?<fkTable>\w+)`? \(`?(?<fkColumn>\w+)`?\)[\s\w]+";
Regex regexNew = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled);
Match mNew = regexNew.Match(tmp);
if (mNew.Success)
{
// string constraintPart = mNew.Groups["constraint"].Value;
FieldMap fm = map.GetFieldMapFromColumn(mNew.Groups["column"].Value);
if (fm != null)
{
fm.SetForeignKeyTableName(mNew.Groups["fkTable"].Value);
fm.SetForeignKeyColumnName(mNew.Groups["fkColumn"].Value);
}
}
else if (tmp != null)
{
int index = tmp.IndexOf("REFER");
if (index > 0)
{
string columnName = ExtractColumn(tmp.Substring(0, index - 1));
tmp = tmp.Substring(index + 5, tmp.Length - index - 5).Trim();
index = tmp.IndexOf("/");
int start = tmp.IndexOf("(");
int end = tmp.IndexOf(")");
if (index > 0 && start > 0 && end > start)
{
string foreignTable = tmp.Substring(index + 1, start - index - 1);
string foreignColumn = tmp.Substring(start + 1, end - start - 1);
FieldMap fm = map.GetFieldMapFromColumn(columnName);
fm.SetForeignKeyTableName(foreignTable);
fm.SetForeignKeyColumnName(foreignColumn);
}
}
}
}
}
}
}
}
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.");
}
}