public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select ucc.owner as schema_name,
ucc.table_name,
ucc.constraint_name,
ucc.position,
ucc.column_name
from all_cons_columns ucc, all_constraints uc
where uc.owner = '{0}'
and uc.owner = ucc.owner
and uc.constraint_name = ucc.constraint_name
and uc.constraint_type = 'P'
order by ucc.table_name, ucc.position",
schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["constraint_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
ky.table_schema as schema_name,
ky.table_name,
ky.constraint_name as key_name,
ky.ordinal_position,
ky.column_name
from information_schema.key_column_usage as ky
inner join information_schema.table_constraints as co
on ky.constraint_name = co.constraint_name
where co.constraint_schema = '{0}'
and co.constraint_type = 'PRIMARY KEY'
order by ky.constraint_name, ky.ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <DBObjects.PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
inx.tabschema as schema_name,
inx.tabname as table_name,
inx.indname as index_name,
inxcol.colname as column_name,
inxcol.colseq as column_position
from
syscat.indexes as inx,
syscat.indexcoluse inxcol
where
inxcol.indschema = inx.tabschema
and inxcol.indname = inx.indname
and inx.tabschema = '{0}'
and inx.uniquerule = 'P'
order by inxcol.indname, inxcol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <DBObjects.PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"SELECT
rc.rdb$constraint_name as ConstraintName,
s.rdb$field_name as FieldName,
rc.rdb$relation_name as TableName,
s.rdb$field_position+1 as FieldPosition
FROM
rdb$index_segments s
LEFT JOIN rdb$relation_constraints rc ON rc.rdb$index_name = s.rdb$index_name
WHERE
rc.rdb$constraint_type = 'PRIMARY KEY';");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var pk = new PrimaryKey(this._schemaName, dr["TableName"].ToString().Trim(), dr["ConstraintName"].ToString().Trim());
pk.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <PrimaryKey> GetPrimaryKeys(string schemaName)
{
var stmt = String.Format(
@"select
ky.table_schema as schema_name,
ky.table_name,
ky.constraint_name as key_name,
ky.ordinal_position,
ky.column_name
from information_schema.key_column_usage as ky
where table_schema = '{0}'
order by ky.table_name, ky.ordinal_position",
schemaName);
var constraints = from c in GetConstraints(schemaName)
where c.ConstraintType == "PRIMARY KEY"
select c;
var ds = this.ExecuteSelect(stmt);
var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
join c in constraints on dr["key_name"].ToString() equals c.ConstraintName
where c.TableName == dr["table_name"].ToString()
select dr;
var cList = new Utils.ChainedList <PrimaryKey>();
foreach (DataRow dr in rows)
{
var pk = new PrimaryKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString());
pk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
PrimaryKey tmp;
if (!cList.Add(pk, out tmp))
{
tmp.AddCoumns(pk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
/// <summary>
/// Initializes a mapping table.
/// </summary>
/// <param name="type">the type to map</param>
/// <param name="namingStrategy">the naming strategy to apply</param>
public Table(Type type, INamingStrategy namingStrategy)
{
String typeName = type.FullName;
TableAttribute tableAttr = ReflectHelper.GetAttribute<TableAttribute>(type);
Name = (tableAttr == null) ? namingStrategy.GetTableName(typeName) : tableAttr.Name;
EntityType = type;
_fields = ReflectHelper.GetSettableFields(type);
_properties = DefaultTypeMap.GetSettableProps(type);
foreach (PropertyInfo pi in _properties)
{
Column column = CreateColumn(pi, namingStrategy);
if (column != null)
{
if (column.DbType == DbType.Empty)
column.DbType = LookupDbType(pi.PropertyType, pi.Name);
column.MemberInfo = new SimpleMemberMap(column.ColumnName, pi);
AddColumn(column);
}
}
foreach (FieldInfo fi in _fields)
{
Column column = CreateColumn(fi, namingStrategy);
if (column != null)
{
if (column.DbType == DbType.Empty)
column.DbType = LookupDbType(fi.FieldType, fi.Name);
column.MemberInfo = new SimpleMemberMap(column.ColumnName, fi);
AddColumn(column);
}
}
if (PrimaryKey == null)
{
Column idCol = FindColumnByFieldName("id");
if (idCol != null)
{
PrimaryKey = new PrimaryKey();
PrimaryKey.AddColumn(idCol);
}
}
}
public override sealed void Refresh()
{
using (IDbConnection cnx = GetConnection())
{
cnx.Open();
Tables.Clear();
using (IDbCommand cmd = cnx.CreateCommand())
{
cmd.CommandType = CommandType.Text;
//Case Sensibility
cmd.CommandText = CaseSensitivityTestQuery;
using (IDataReader reader = cmd.ExecuteReader())
{
reader.Read();
IsCaseSensitive = new CaseSensitivity(reader.Read());
}
//Tables
cmd.CommandText = TableQuery;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Table table = CreateTable(reader);
Tables.Add(table.ToString(), table);
}
}
//Columns & Primary Keys
foreach (Table table in Tables.Values.Cast <Table>())
{
cmd.CommandText = string.Format(ColumnQuery, table.Name);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Column column = CreateColumn(reader, table);
table.AddColumn(column);
int pkpos = (int)reader.GetInt64OrDefault(5);
if (pkpos > 0)
{
if (table.PrimaryKey == null)
{
table.SetPrimaryKey(CreatePrimaryKey(table));
}
PrimaryKey primaryKey = table.PrimaryKey as PrimaryKey;
// ReSharper disable PossibleNullReferenceException
primaryKey.AddColumn(pkpos, column);
// ReSharper restore PossibleNullReferenceException
}
}
}
}
//Indexes
foreach (Table table in Tables.Values.Cast <Table>())
{
cmd.CommandText = string.Format(IndexListQuery, table.Name);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Index index = CreateIndex(reader, table);
table.AddIndex(index);
}
}
foreach (Index index in table.Indexes().Cast <Index>())
{
cmd.CommandText = string.Format(IndexInfoQuery, index.Name);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
IColumn column = table.GetColumn(reader.GetStringOrDefault(2));
index.AddColumn(new ColumnForIndex {
Column = column, Position = (int)reader.GetInt64OrDefault(0)
});
}
}
}
}
//ALERT: Foreign Key List TO BE CODED
}
}
}