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 } } }