public override IList<string> GetOwners()
{
var owners = new List<string>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
var tableCommand = conn.CreateCommand();
tableCommand.CommandText = "SELECT DISTINCT table_owner FROM iitables WHERE table_owner <> '$ingres'";
var sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var ownerName = sqlDataReader.GetString(0).TrimEnd();
owners.Add(ownerName);
}
}
}
finally
{
conn.Close();
}
return owners;
}
protected override IList<Column> GetTablesDetails(Table table, string owner)
{
table.OwnerSchema = owner;
var columns = new List<Column>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT" +
" column_name," +
" column_datatype," +
" column_nulls," +
" column_length," +
" column_scale " +
"FROM iicolumns " +
"WHERE table_owner = '{0}' " +
"AND table_name = '{1}' " +
"ORDER BY column_sequence",
owner, table.Name);
using (var sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (sqlDataReader.Read())
{
string columnName = sqlDataReader.GetString(0).TrimEnd();
string dataType = sqlDataReader.GetString(1).TrimEnd();
bool isNullable = sqlDataReader.GetString(2).Equals("Y", StringComparison.CurrentCultureIgnoreCase);
int characterMaxLenth = sqlDataReader.GetInt32(3);
int numericPrecision = sqlDataReader.GetInt32(3);
int numericScale = sqlDataReader.GetInt32(4);
var m = new DataTypeMapper();
bool isPrimaryKey = IsPrimaryKey(owner, table.Name, columnName);
columns.Add(new Column(table)
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = IsForeignKey(owner, table.Name, columnName),
MappedDataType = m.MapFromDBType(DatabaseServerType.Ingres, dataType, characterMaxLenth, numericPrecision, numericScale, isPrimaryKey),
DataLength = characterMaxLenth,
ConstraintName = GetConstraintName(owner, table.Name, columnName),
DataPrecision = numericPrecision,
DataScale = numericScale
});
table.Columns = columns;
}
table.PrimaryKey = DeterminePrimaryKeys(table);
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
}
}
finally
{
conn.Close();
}
return columns;
}
public override List<Table> GetTables(string owner)
{
var tables = new List<Table>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
var tableCommand = conn.CreateCommand();
tableCommand.CommandText = String.Format("SELECT table_name " +
"FROM iitables " +
"WHERE table_owner = '{0}' " +
"AND table_type in ('T', 'V') " +
"AND table_name NOT LIKE 'ii%'",
owner);
var sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var tableName = sqlDataReader.GetString(0).TrimEnd();
tables.Add(new Table
{
Name = tableName,
OwnerSchema = owner,
DatabaseName = this.DatabaseName
});
}
}
tables.Sort((x, y) => String.CompareOrdinal(x.Name, y.Name));
}
finally
{
conn.Close();
}
return tables;
}
private string GetForeignKeyReferenceTableName(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableCommand = conn.CreateCommand())
{
tableCommand.CommandText = String.Format("SELECT p.table_name " +
"FROM iiref_constraints rc " +
"INNER JOIN iikeys p " +
"ON p.schema_name = rc.unique_schema_name " +
"AND p.constraint_name = rc.unique_constraint_name " +
"INNER JOIN iiconstraints c " +
"ON c.schema_name = rc.ref_schema_name " +
"AND c.constraint_name = rc.ref_constraint_name " +
"INNER JOIN iikeys f " +
"ON f.schema_name = rc.ref_schema_name " +
"AND f.constraint_name = rc.ref_constraint_name " +
"AND p.key_position = f.key_position " +
"WHERE f.schema_name = '{0}' " +
"AND f.table_name = '{1}' " +
"AND f.column_name = '{2}'",
owner,
tableName,
columnName);
return tableCommand.ExecuteScalar().ToString();
}
}
}
finally
{
conn.Close();
}
}
private string GetConstraintName(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT k.constraint_name " +
"FROM iikeys k " +
"INNER JOIN iiconstraints c " +
"ON k.constraint_name = c.constraint_name " +
"WHERE c.constraint_type = 'R' " +
"AND k.schema_name = '{0}' " +
"AND k.table_name = '{1}' " +
"AND k.column_name = '{2}'",
owner, tableName, columnName);
var result = tableDetailsCommand.ExecuteScalar();
return result == null ? String.Empty : result.ToString();
}
}
}
finally
{
conn.Close();
}
}
private bool IsForeignKey(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT COUNT(0) " +
"FROM iikeys k " +
"INNER JOIN iiconstraints c " +
"ON k.constraint_name = c.constraint_name " +
"WHERE c.constraint_type = 'R' " +
"AND k.schema_name = '{0}' " +
"AND k.table_name = '{1}' " +
"AND k.column_name = '{2}'",
owner, tableName, columnName);
var obj = tableDetailsCommand.ExecuteScalar();
int result;
if (obj != null &&
Int32.TryParse(obj.ToString(), out result))
return result > 0;
}
}
}
finally
{
conn.Close();
}
return false;
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new IngresConnection(_connectionString);
conn.Open();
using (conn)
{
using (var command = conn.CreateCommand())
{
command.CommandText = String.Format("SELECT f.table_name " +
"FROM iiref_constraints rc " +
"INNER JOIN iikeys p " +
"ON p.schema_name = rc.unique_schema_name " +
"AND p.constraint_name = rc.unique_constraint_name " +
"INNER JOIN iiconstraints c " +
"ON c.schema_name = rc.ref_schema_name " +
"AND c.constraint_name = rc.ref_constraint_name " +
"INNER JOIN iikeys f " +
"ON f.constraint_name = rc.ref_constraint_name " +
"AND p.key_position = f.key_position " +
"WHERE p.schema_name = '{0}' " +
"AND p.table_name = '{1}'",
table.OwnerSchema,
table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
hasManyRelationships.Add(new HasMany
{
Reference = reader.GetString(0).TrimEnd(),
});
}
}
return hasManyRelationships;
}
}
}