Exemplo n.º 1
0
        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());
            }
        }
Exemplo n.º 2
0
        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());
            }
        }
Exemplo n.º 3
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    owner,
                    index_name,
                    ind.table_name,
                    ind.uniqueness,
                    col.column_name,
                    col.column_position
                from all_indexes ind
                inner join (select * from all_ind_columns where index_owner = '{0}') col using (index_name)
                where ind.owner = '{0}'
                and ind.table_type = 'TABLE'
                order by index_name, column_position", schemaName);

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <Index>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    var index = new Index(dr["owner"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                    index.IsUnique = (dr["uniqueness"].ToString() == "UNIQUE");
                    index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
                    Index tmp;
                    if (!cList.Add(index, out tmp))
                    {
                        tmp.AddCoumns(index.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 4
0
        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());
        }
Exemplo n.º 5
0
        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());
        }
Exemplo n.º 6
0
        public override List <DBObjects.Unique> GetUniqueConstraints(string schemaName)
        {
            var stmt = String.Format(
                @"SELECT 
                  rc.rdb$constraint_name as ConstraintName,
                  rc.rdb$relation_name as TableName,
                  s.rdb$field_name as FieldName,
                  s.rdb$field_position+1 as FieldPosition,
                  rc.rdb$deferrable as Deferrable,
                  rc.rdb$initially_deferred as InitiallyDeferred
                FROM rdb$relation_constraints rc
                  LEFT JOIN rdb$index_segments s ON (rc.rdb$index_name = s.rdb$index_name)
                WHERE 
                  rc.rdb$constraint_type = 'UNIQUE';");

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <Unique>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    var uk = new Unique(this._schemaName, dr["TableName"].ToString().Trim(), dr["ConstraintName"].ToString().Trim());
                    uk.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
                    Unique tmp;
                    if (!cList.Add(uk, out tmp))
                    {
                        tmp.AddCoumns(uk.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 7
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    sta.table_schema as schema_name,
                    sta.table_name,
                    sta.index_name,
                    sta.column_name,
                    sta.seq_in_index as column_index,
                    sta.non_unique
                  from   information_schema.statistics sta
                  where  sta.table_schema = '{0}'",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = !Convert.ToBoolean(dr["non_unique"]);
                index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 8
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                ixs.schemaname as schema_name,
	                ixs.tablename as table_name,
	                ixs.indexname as index_name,
	                jo.attname as column_name,
	                jo.attnum as column_index,
	                jo.indkey as index_order,
	                jo.indisunique as is_unique
                from pg_catalog.pg_indexes as ixs
                inner join (
	                select a.attname, a.attnum, ix.indkey, ix.indisunique, i.relname
	                from pg_catalog.pg_class as t,
		                pg_catalog.pg_class as i,
		                pg_catalog.pg_index as ix,
		                pg_catalog.pg_attribute as a
	                where a.attnum > 0
                    and t.oid = ix.indrelid
	                and i.oid = ix.indexrelid
	                and a.attrelid = t.oid
	                and a.attnum = ANY(ix.indkey)) as jo
                on ixs.indexname = jo.relname
                where ixs.schemaname = '{0}'",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
                var indexPos = dr["index_order"].ToString().Split(' ').ToArray();
                for (int i = 0; i < indexPos.Length; i++)
                {
                    if (Convert.ToInt32(dr["column_index"].ToString()) == Convert.ToInt32(indexPos[i]))
                    {
                        index.AddColumn(i + 1, dr["column_name"].ToString());
                        break;
                    }
                }
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 9
0
        public override List <ForeignKey> GetForeignKeys(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                fky.table_schema as schema_name,
	                fky.table_name,
	                fky.constraint_name as key_name,
	                fky.column_name,
	                pky.table_schema as pri_schema_name,
	                pky.table_name as pri_table_name,
	                pky.column_name as pri_column_name,
	                fky.ordinal_position as ordinal_position,
	                rco.update_rule,
	                rco.delete_rule,
	                co.is_deferrable,
                    co.initially_deferred
                from information_schema.key_column_usage as fky
                inner join information_schema.table_constraints as co
                on fky.constraint_name = co.constraint_name
                inner join information_schema.referential_constraints as rco
                on rco.constraint_name = co.constraint_name
                inner join information_schema.key_column_usage as pky
                on pky.constraint_name = rco.unique_constraint_name
	                and fky.ordinal_position = pky.ordinal_position
                where co.constraint_schema = '{0}'
	                and co.constraint_type = 'FOREIGN KEY'
                order by table_name, key_name, ordinal_position",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <ForeignKey>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
                                        dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
                fk.UpdateRule    = this.ParseUpdateRule(dr["update_rule"].ToString());
                fk.DeleteRule    = this.ParseDeleteRule(dr["delete_rule"].ToString());
                fk.Deferrability = this.ParseBoolean(dr["is_deferrable"].ToString()) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
                fk.InitialMode   = this.ParseBoolean(dr["initially_deferred"].ToString()) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
                fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
                ForeignKey tmp;
                if (!cList.Add(fk, out tmp))
                {
                    tmp.AddColumns(fk.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 10
0
        public override List <ForeignKey> GetForeignKeys(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    c.owner as schema_name,
                    c.constraint_name as key_name,
                    c.delete_rule,
                    c.deferrable,
                    c.deferred,
                    source.table_name as table_name,
                    source.column_name as column_name,
                    source.position,
                    target.owner as pri_schema_name,
                    target.table_name as pri_table_name,
                    target.column_name as pri_column_name
                from
                    all_constraints c,
                    all_cons_columns source,
                    all_cons_columns target
                where c.owner = '{0}'
                and   c.constraint_type = 'R'
                and   c.constraint_name = source.constraint_name
                and   c.r_constraint_name = target.constraint_name
                and   source.position = target.position
                and   source.owner = '{0}'
                and   target.owner = '{0}'", schemaName);

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <ForeignKey>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
                                            dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
                    // TODO missing update rule
                    fk.DeleteRule    = this.ParseDeleteRule(dr["delete_rule"].ToString());
                    fk.Deferrability = this.ParseDeferrability(dr["deferrable"].ToString());
                    fk.InitialMode   = this.ParseInitialMode(dr["deferred"].ToString());
                    fk.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
                    ForeignKey tmp;
                    if (!cList.Add(fk, out tmp))
                    {
                        tmp.AddColumns(fk.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 11
0
        public override List <DBObjects.Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"SELECT 
                  i.rdb$relation_name AS TableName,
                  s.rdb$field_name AS FieldName,
                  i.rdb$index_name AS IndexName,
                  rc.rdb$constraint_name AS AltIndexName,
                  i.rdb$foreign_key AS IsForeignKey,
                  i.rdb$unique_flag AS IsUnique,
                  s.rdb$field_position+1 AS FieldPosition,
                  rc.rdb$deferrable AS Deferrable,
                  rc.rdb$initially_deferred AS InitiallyDeferred
                FROM rdb$indices i
                  LEFT JOIN rdb$index_segments s ON (i.rdb$index_name = s.rdb$index_name)
                  LEFT JOIN rdb$relation_constraints rc ON (i.rdb$index_name = rc.rdb$index_name)
                WHERE 
                  i.rdb$system_flag = 0 AND
                  s.rdb$field_name IS NOT NULL");

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <Index>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string index_name;
                    if (dr["AltIndexName"] == DBNull.Value)
                    {
                        index_name = dr["IndexName"].ToString().Trim();
                    }
                    else
                    {
                        index_name = dr["AltIndexName"].ToString().Trim();
                    }
                    var ix = new Index(this._schemaName, dr["TableName"].ToString().Trim(), index_name);
                    ix.IsUnique = (dr["IsUnique"] != DBNull.Value) ? true : false;
                    ix.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim());
                    Index tmp;
                    if (!cList.Add(ix, out tmp))
                    {
                        tmp.AddCoumns(ix.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 12
0
        public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                ccol.constname as key_name,
	                ccol.tabschema as child_schema_name,
	                ccol.tabname as child_table_name,
	                ccol.colname as child_column_name,
	                pcol.tabschema as parent_schema_name,
	                pcol.tabname as parent_table_name,
	                pcol.colname as parent_column_name,
	                ccol.colseq as ordinal_position,
	                fk.deleterule as delete_rule, -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
	                fk.updaterule as update_rule -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
                from
	                syscat.references as fk,
	                syscat.keycoluse as pcol, -- The parent columns
	                syscat.keycoluse as ccol  -- The child columns
                where
	                fk.tabschema = '{0}'
	                and ccol.tabschema = fk.tabschema
	                and ccol.tabname = fk.tabname
	                and ccol.constname = fk.constname
	                and pcol.tabschema = fk.tabschema  
	                and pcol.tabname = fk.reftabname
	                and pcol.constname = fk.refkeyname
	                and ccol.colseq = pcol.colseq
	                order by ccol.tabname, ccol.constname, ccol.colseq"    , schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <ForeignKey>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var fk = new ForeignKey(dr["child_schema_name"].ToString(), dr["child_table_name"].ToString(), dr["key_name"].ToString(),
                                        dr["parent_schema_name"].ToString(), dr["parent_table_name"].ToString());
                fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
                fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
                fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["child_column_name"].ToString(), dr["parent_column_name"].ToString());
                ForeignKey tmp;
                if (!cList.Add(fk, out tmp))
                {
                    tmp.AddColumns(fk.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 13
0
        public override List <Index> GetIndices(string schemaName)
        {
            var stmt = String.Format(
                @"select
	                '{0}' as schema_name,
	                tab.name as table_name,
	                inx.name as index_name,
	                col.name as column_name,
	                ixcol.key_ordinal as column_index,
	                inx.is_unique
                from sys.objects as obj
                inner join sys.indexes as inx
                on obj.object_id = inx.object_id
                inner join sys.index_columns as ixcol
                on inx.object_id = ixcol.object_id
	                and inx.index_id = ixcol.index_id
                inner join sys.columns as col
                on obj.object_id = col.object_id
	                and ixcol.column_id = col.column_id
                inner join sys.tables as tab
                on obj.object_id = tab.object_id
                where obj.schema_id = (select schema_id from sys.schemas where name = '{0}')
	                and inx.is_disabled = 0
	                and inx.name is not Null
                    and ixcol.is_included_column = 0",
                schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = Convert.ToBoolean(dr["is_unique"]);
                index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 14
0
        public override List <Unique> GetUniqueConstraints(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,
                    ky.constraint_name
                from information_schema.key_column_usage as ky
                where ky.table_schema = '{0}'
                order by ky.constraint_name, ky.ordinal_position",
                schemaName);
            var ds          = this.ExecuteSelect(stmt);
            var cList       = new Utils.ChainedList <Unique>();
            var constraints = from c in this.GetConstraints(schemaName)
                              where c.ConstraintType == "UNIQUE"
                              select c;

            var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
                       join c in constraints on dr["table_name"].ToString() equals c.TableName
                       where dr["constraint_name"].ToString() == c.ConstraintName
                       select new { row = dr, constraint = c };

            foreach (var item in rows)
            {
                var dr           = item.row;
                var uqSchemaName = dr["schema_name"].ToString();
                var uqTableName  = dr["table_name"].ToString();
                var uqkKeyName   = dr["key_name"].ToString();
                var uq           = new Unique(uqSchemaName, uqTableName, uqkKeyName);
                uq.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString());
                Unique tmp;
                if (!cList.Add(uq, out tmp))
                {
                    tmp.AddCoumns(uq.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 15
0
        public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
        {
            var stmt = String.Format(
                @"SELECT
                  rc.rdb$constraint_name AS FkName,
                  rcc.rdb$relation_name AS ChildTable,
                  isc.rdb$field_name AS ChildColumn,
                  isc.rdb$field_position+1 AS ChildPosition,
                  rcp.rdb$relation_name AS ParentTable,
                  isp.rdb$field_name AS ParentColumn,
                  rc.rdb$update_rule AS UpdateRule,
                  rc.rdb$delete_rule AS DeleteRule,
                  rcc.rdb$deferrable AS Deferrable,
                  rcc.rdb$initially_deferred AS InitiallyDeferred
                FROM
                  rdb$ref_constraints rc
                  INNER JOIN rdb$relation_constraints rcc on rc.rdb$constraint_name = rcc.rdb$constraint_name
                  INNER JOIN rdb$index_segments isc on rcc.rdb$index_name = isc.rdb$index_name
                  INNER JOIN rdb$relation_constraints rcp on rc.rdb$const_name_uq  = rcp.rdb$constraint_name
                  INNER JOIN rdb$index_segments isp on rcp.rdb$index_name = isp.rdb$index_name;");

            using (var ds = this.ExecuteSelect(stmt))
            {
                var cList = new Utils.ChainedList <ForeignKey>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    var fk = new ForeignKey(this._schemaName, dr["ChildTable"].ToString().Trim(), dr["FkName"].ToString().Trim(), this._schemaName, dr["ParentTable"].ToString().Trim());
                    fk.DeleteRule    = this.ParseDeleteRule(dr["DeleteRule"].ToString().Trim());
                    fk.UpdateRule    = this.ParseUpdateRule(dr["UpdateRule"].ToString().Trim());
                    fk.InitialMode   = (this.ParseBoolean(dr["InitiallyDeferred"].ToString().Trim())) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
                    fk.Deferrability = (this.ParseBoolean(dr["Deferrable"].ToString().Trim())) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
                    fk.AddColumn(Convert.ToInt32(dr["ChildPosition"]), dr["ChildColumn"].ToString().Trim(), dr["ParentColumn"].ToString().Trim());
                    ForeignKey tmp;
                    if (!cList.Add(fk, out tmp))
                    {
                        tmp.AddColumns(fk.IndexOrderdColumnNames);
                    }
                }
                return(cList.ToList());
            }
        }
Exemplo n.º 16
0
        public override List <DBObjects.Index> GetIndices(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,
	                case (inx.uniquerule)
	                    when 'P' then 'Y'
	                    when 'U' then 'Y'
	                    when 'D' then 'N'
	                end as is_unique
                from
	                syscat.indexes as inx,
	                syscat.indexcoluse inxcol
                where
	                inxcol.indschema = inx.tabschema
	                and inxcol.indname = inx.indname
	                and inx.tabschema = '{0}'
	                and inx.indextype in ('BLOK', 'CLUS', 'DIM', 'REG')
	                order by inxcol.indname, inxcol.colseq"    , schemaName);
            var ds    = this.ExecuteSelect(stmt);
            var cList = new Utils.ChainedList <Index>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString());
                index.IsUnique = dr["is_unique"].ToString().ToLower() == "y";
                index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString());
                Index tmp;
                if (!cList.Add(index, out tmp))
                {
                    tmp.AddCoumns(index.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }
Exemplo n.º 17
0
        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());
        }
Exemplo n.º 18
0
        public override List <ForeignKey> GetForeignKeys(string schemaName)
        {
            var stmt = String.Format(
                @"select
                    fky.constraint_schema as schema_name,
                    fky.table_name,
                    fky.constraint_name as key_name,
                    fky.column_name,
                    fky.referenced_table_schema as pri_schema_name,
                    fky.referenced_table_name as pri_table_name,
                    fky.referenced_column_name as pri_column_name,
                    fky.ordinal_position,
                    rco.update_rule,
                    rco.delete_rule
                from (select * from information_schema.key_column_usage where table_schema = '{0}') as fky
                            
                inner join (select  constraint_schema as table_schema, 
                                    constraint_name, 
                                    table_name, 
                                    referenced_table_name, 
                                    unique_constraint_name, 
                                    update_rule, 
                                    delete_rule
                            from information_schema.referential_constraints 
                            WHERE CONSTRAINT_SCHEMA = '{0}'                           
                            ) as rco
                            using (table_schema, constraint_name, table_name, referenced_table_name)",
                schemaName);

            // Hack, to avoide the missing referential_constraints table on old MySQL Versions, default to "Set Default" rules
            if (this._runningVersion != null && this._runningVersion.Major == 5 && this._runningVersion.Minor == 0)
            {
                stmt = String.Format(@"select
	                constraint_schema as schema_name,
	                table_name,
	                constraint_name as key_name,
	                column_name,
	                referenced_table_schema as pri_schema_name,
	                referenced_table_name as pri_table_name,
	                referenced_column_name as pri_column_name,
	                ordinal_position,
	                'RESTRICT' as update_rule,
	                'CASCADE' as delete_rule
                from information_schema.key_column_usage where table_schema = '{0}'
                and constraint_name <> 'PRIMARY'",
                                     schemaName);
            }

            var constraints = from c in GetConstraints(schemaName)
                              where c.ConstraintType == "FOREIGN 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["table_name"].ToString() equals c.TableName
                       where c.ConstraintName == dr["key_name"].ToString()
                       select dr;


            var cList = new Utils.ChainedList <ForeignKey>();

            foreach (DataRow dr in rows)
            {
                var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
                                        dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
                fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
                fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
                // fk.IsDeferrable = this.ParseBoolean(dr["is_deferrable"].ToString()); // TODO NOT EXTRACTED FROM MYSQL
                fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
                ForeignKey tmp;
                if (!cList.Add(fk, out tmp))
                {
                    tmp.AddColumns(fk.IndexOrderdColumnNames);
                }
            }
            return(cList.ToList());
        }