protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            const string sql = @"
select 'Foo' ""TableSchema""
        , rf.RDB$RELATION_NAME ""TableName""
        , rf.RDB$FIELD_NAME ""ColumnName""
        , case when rf.RDB$NULL_FLAG is null then 1 else 0 end ""Nullable""
        , t.RDB$TYPE_NAME ""Type""
        , case when f.RDB$COMPUTED_SOURCE is null then 0 else 1 end ""Generated""
        , case when exists(select *
            from RDB$RELATION_CONSTRAINTS rc
                inner join RDB$INDEX_SEGMENTS xs on xs.RDB$INDEX_NAME = rc.RDB$INDEX_NAME
            where rc.RDB$RELATION_NAME = rf.RDB$RELATION_NAME and xs.RDB$FIELD_NAME = rf.RDB$FIELD_NAME
                and rc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') then 1 else 0 end ""PrimaryKey""
        , f.RDB$FIELD_LENGTH ""Length""
        , f.RDB$FIELD_PRECISION ""Precision""
        , f.RDB$FIELD_SCALE ""Scale""
        , rf.RDB$DEFAULT_VALUE ""DefaultValue""
    from RDB$RELATION_FIELDS rf
        inner join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
        inner join RDB$TYPES t on t.RDB$TYPE = f.RDB$FIELD_TYPE and t.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
    where rf.RDB$SYSTEM_FLAG = 0
    order by rf.RDB$RELATION_NAME, rf.RDB$FIELD_POSITION
";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, "@db", databaseName, ReadColumn));
        }
        protected virtual IList <StoredProcedureParameter> ReadParameters(IDbConnection connection, string databaseName)
        {
            const string sql = @"select object_name, argument_name, owner, data_type, data_length, data_precision, data_scale, in_out
from all_arguments where lower(owner) = :db order by object_id, position";

            return(DataCommand.Find <StoredProcedureParameter>(connection, sql, ":db", databaseName.ToLower(), ReadParameter));
        }
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            var constraints = new List <DataConstraint>();

            string sql = @"
SELECT UCC.owner, UCC.constraint_name, UCC.table_name, UCC.column_name, UC.constraint_type, UC.R_constraint_name
FROM all_cons_columns UCC, all_constraints UC
WHERE UCC.constraint_name=UC.constraint_name
AND UCC.table_name=UC.table_name
AND UCC.TABLE_NAME NOT LIKE '%$%' AND UCC.TABLE_NAME NOT LIKE 'LOGMNR%' AND UCC.TABLE_NAME NOT IN ('HELP','SQLPLUS_PRODUCT_PROFILE')
AND UC.CONSTRAINT_TYPE!='C'
and lower(UCC.owner) = :owner";

            constraints.AddRange(DataCommand.Find <DataConstraint>(conn, sql, ":owner", db.ToLower(), ReadConstraint));

            string sql2 =
                @"
select t.TRIGGER_NAME, t.TABLE_OWNER, t.TABLE_NAME, t.TRIGGER_BODY from ALL_TRIGGERS t
where t.status = 'ENABLED'
 and t.TRIGGERING_EVENT = 'INSERT'
 and t.TRIGGER_TYPE='BEFORE EACH ROW'
 and lower(t.owner) = :owner";

            constraints.AddRange(DataCommand.Find <DataConstraint>(conn, sql2, ":owner", db.ToLower(), ReadTrigger));
            return(constraints);
        }
Exemple #4
0
        protected virtual List <DataStoredProcedure> ReadProcedures(IDbConnection conn, string db)
        {
            string sql = @"select procedure_name, procedure_owner, text_segment " +
                         "from iiprocedures where system_use='U' and " +
                         "procedure_owner!='$ingres' and text_sequence=1";

            return(DataCommand.Find <DataStoredProcedure>(conn, sql, ":db", db, ReadProcedure));
        }
        // We use mysql.PROC instead of information_schema.ROUTINES, because it saves us parsing of parameters.
        // Note: higher permissions are required to access mysql.PROC.
        public List <DataStoredProcedure> ReadProcedures(IDbConnection conn, string db)
        {
            string sql = @"
SELECT db, name, type, specific_name, param_list, returns, body
FROM mysql.proc
WHERE db=?db AND type IN ('FUNCTION','PROCEDURE')";

            return(DataCommand.Find <DataStoredProcedure>(conn, sql, "?db", db.ToLower(), ReadProcedure));
        }
Exemple #6
0
        public override IList <IDataName> ReadTables(IDbConnection connectionString, string databaseName)
        {
            // note: the ReadDataNameAndSchema relies on information order
            const string sql = @"SELECT table_name, table_owner FROM iitables " +
                               "WHERE table_owner <> '$ingres' " +
                               "AND table_type in ('T', 'V') " +
                               "AND table_name NOT LIKE 'ii%'";

            return(DataCommand.Find <IDataName>(connectionString, sql, ReadDataNameAndSchema));
        }
        public override IList <IDataName> ReadTables(IDbConnection connectionString, string databaseName)
        {
            // note: the ReadDataNameAndSchema relies on information order
            const string sql = @"
SELECT table_name, table_schema
FROM information_schema.`TABLES`
WHERE table_schema=?db";

            return(DataCommand.Find <IDataName>(connectionString, sql, "?db", databaseName, ReadDataNameAndSchema));
        }
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            string sql = @"
SELECT constraint_name,table_schema,table_name
    ,GROUP_CONCAT(column_name SEPARATOR ',') AS column_name,referenced_table_schema,referenced_table_name,GROUP_CONCAT(referenced_column_name SEPARATOR ',') AS referenced_column_name
FROM information_schema.`KEY_COLUMN_USAGE`
WHERE table_schema=?db GROUP BY constraint_name,table_schema,table_name,referenced_table_name";

            return(DataCommand.Find <DataConstraint>(conn, sql, "?db", db, ReadConstraint));
        }
Exemple #9
0
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            string sql = @"
SELECT constraint_name,table_schema,table_name
    ,column_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE constraint_catalog=:db";

            return(DataCommand.Find <DataConstraint>(conn, sql, ":db", db, ReadConstraint));
        }
        public override IList <IDataName> ReadTables(IDbConnection connectionString, string databaseName)
        {
            // note: the ReadDataNameAndSchema relies on information order
            const string sql = @"
SELECT table_name, table_schema
FROM information_schema.TABLES
WHERE table_catalog=:db
AND table_schema NOT IN ('pg_catalog','information_schema')";

            return(DataCommand.Find <IDataName>(connectionString, sql, ":db", databaseName, ReadDataNameAndSchema));
        }
Exemple #11
0
        protected virtual List <DataForeignConstraint> ReadForeignConstraints(IDbConnection conn, string db)
        {
            string sql = @"
SELECT t.constraint_name, t.table_name, t.constraint_type,
    c.table_schema, c.table_name, c.column_name
FROM information_schema.table_constraints t,
    information_schema.constraint_column_usage c
WHERE t.constraint_name = c.constraint_name
    and t.constraint_type IN  ('FOREIGN KEY','PRIMARY KEY')";

            return(DataCommand.Find <DataForeignConstraint>(conn, sql, ":db", db, ReadForeignConstraint));
        }
        protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            const string sql = @"
SELECT table_schema,table_name,column_name
    ,is_nullable,data_type,extra,column_type
    ,column_key,character_maximum_length,numeric_precision,numeric_scale,
    column_default
FROM information_schema.`COLUMNS`
WHERE table_schema=?db";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, "?db", databaseName, ReadColumn));
        }
Exemple #13
0
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            var constraints = new List <DataConstraint>();

            string sql = @"
SELECT UCC.owner, UCC.constraint_name, UCC.table_name, UC.constraint_type, UC.R_constraint_name, UCC.column_name, UCC.position
FROM all_cons_columns UCC, all_constraints UC
WHERE UCC.constraint_name=UC.constraint_name
AND UCC.table_name=UC.table_name
AND UCC.owner=UC.owner
AND UCC.TABLE_NAME NOT LIKE '%$%' AND UCC.TABLE_NAME NOT LIKE 'LOGMNR%' AND UCC.TABLE_NAME NOT IN ('HELP','SQLPLUS_PRODUCT_PROFILE')
AND UC.CONSTRAINT_TYPE!='C'
and lower(UCC.owner) = :owner";

            constraints.AddRange(DataCommand.Find(conn, sql, ":owner", db.ToLower(),
                                                  r => new
            {
                Key = new
                {
                    Owner       = r.GetString(0),
                    ConName     = r.GetString(1),
                    TableName   = r.GetString(2),
                    ConType     = r.GetString(3),
                    RevCconName = r.GetAsString(4)
                },
                Value = new
                {
                    ColName = r.GetString(5),
                    ColPos  = r.GetInt32(6)
                }
            })
                                 .GroupBy(r => r.Key, r => r.Value, (r, rs) => new DataConstraint
            {
                TableSchema           = r.Owner,
                ConstraintName        = r.ConName,
                TableName             = r.TableName,
                ConstraintType        = r.ConType,
                ReverseConstraintName = r.RevCconName,
                ColumnNames           = rs.OrderBy(t => t.ColPos).Select(t => t.ColName).ToList()
            }));

            string sql2 =
                @"
select t.TRIGGER_NAME, t.TABLE_OWNER, t.TABLE_NAME, t.TRIGGER_BODY from ALL_TRIGGERS t
where t.status = 'ENABLED'
 and t.TRIGGERING_EVENT = 'INSERT'
 and t.TRIGGER_TYPE='BEFORE EACH ROW'
 and lower(t.owner) = :owner";

            constraints.AddRange(DataCommand.Find <DataConstraint>(conn, sql2, ":owner", db.ToLower(), ReadTrigger));
            return(constraints);
        }
Exemple #14
0
        protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            const string sql = @"SELECT t.table_owner, t.table_name, column_name, " +
                               "column_nulls, column_datatype, column_default_val, " +
                               "column_length, column_scale " +
                               "FROM iicolumns c join iitables t on " +
                               "(c.table_name=t.table_name and c.table_owner=t.table_owner) " +
                               "WHERE t.table_owner <> '$ingres' and t.table_type in ('T', 'V') " +
                               "AND t.table_name NOT LIKE 'ii%' " +
                               "ORDER BY column_sequence";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, ReadColumn));
        }
Exemple #15
0
        protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            const string sql = @"
SELECT table_schema, table_name, column_name
    ,is_nullable, data_type, domain_schema, domain_name, column_default
    ,character_maximum_length, numeric_precision, numeric_scale, udt_name
FROM information_schema.COLUMNS
WHERE table_catalog=:db
    AND table_schema NOT IN ('pg_catalog','information_schema')
ORDER BY ordinal_position
";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, ":db", databaseName, ReadColumn));
        }
Exemple #16
0
        protected virtual List <DataStoredProcedure> ReadProcedures(IDbConnection conn, string db)
        {
            string sql = @"
SELECT pr.proowner, pr.proname, pr.proretset, pr.prorettype, pg_catalog.format_type(pr.prorettype, NULL) 
  ,pr.proargtypes, pr.proallargtypes, pr.proargnames, pr.proargmodes
FROM pg_proc pr, pg_type tp 
WHERE tp.oid = pr.prorettype AND pr.proisagg = FALSE 
AND tp.typname <> 'trigger' 
AND pr.pronamespace IN ( SELECT oid FROM pg_namespace 
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' ); 

";

            return(DataCommand.Find <DataStoredProcedure>(conn, sql, ":db", db, ReadProcedure));
        }
        public override IList <IDataName> ReadTables(IDbConnection connectionString, string databaseName)
        {
            // note: the ReadDataNameAndSchema relies on information order
            const string sql = @"
select RDB$RELATION_NAME ""TableName""
        , 'Foo' ""TableSchema""
    from RDB$RELATIONS
    where RDB$SYSTEM_FLAG = 0
    order by RDB$RELATION_NAME
";

            return(DataCommand.Find <IDataName>(connectionString, sql, "@db", databaseName, ReadDataNameAndSchema)
                   .ConvertAll(delegate(IDataName dn) {
                dn.Name = dn.Name.Trim();
                return dn;
            }));
        }
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            // picrap -> Thomas: I switched the FK orders without really understanding why
            //                   because relations were inversed
            string sql = @"
                SELECT DISTINCT 
		                c.constraint_type as constraint_type,
		                c.constraint_name as constraint_name, 
                        k.schema_name as schema_name,
                        k.table_name as table_name,
		                k.column_name AS column_name,
                        '' as referenced_schema_name,
                        '' as referenced_table_name,
                        '' as referenced_column_name
                FROM 
		                iiconstraints c, 
		                iikeys k 
                WHERE 
		                k.constraint_name = c.constraint_name AND 
		                c.constraint_type = 'P'
                UNION
                SELECT DISTINCT
		                c.constraint_type as constraint_type,
                        squeeze(f.table_name) || '_' || p.constraint_name as constraint_name,
                        f.schema_name as referenced_schema_name,
                        f.table_name as referenced_table_name,
                        f.column_name as referenced_column_name,
                        p.schema_name as schema_name,
                        p.table_name as table_name,
                        p.column_name as column_name
                FROM
                        iikeys p,
                        iiconstraints c,
                        iiref_constraints rc,
                        iikeys f
                WHERE
                        c.constraint_type = 'R' and
                        c.constraint_name = rc.ref_constraint_name AND
                        p.constraint_name = rc.unique_constraint_name AND
                        f.constraint_name = rc.ref_constraint_name AND
                        p.key_position = f.key_position
                ";

            return(DataCommand.Find <DataConstraint>(conn, sql, ReadContraint));
        }
Exemple #19
0
        public override IList <IDataName> ReadTables(IDbConnection connectionString, string databaseName)
        {
            // note: the ReadDataNameAndSchema relies on information order
            const string sql = @"
SELECT table_name, owner
FROM all_tables 
WHERE table_name NOT LIKE '%$%' 
AND table_name NOT LIKE 'LOGMNR%' 
AND table_name NOT IN ('SQLPLUS_PRODUCT_PROFILE','HELP')
and lower(owner) = :owner
UNION
SELECT view_name, owner
FROM all_views
WHERE lower(owner) = :owner
";

            return(DataCommand.Find <IDataName>(connectionString, sql, ":owner", databaseName.ToLower(), ReadDataNameAndSchema));
        }
        protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            const string sql = @"
SELECT owner, table_name, column_name, 
  case when data_type like 'TIMESTAMP%' then 'TIMESTAMP'
       when data_type like 'INTERVAL%' then 'INTERVAL'
  else data_type
  end data_type , 
    data_length, data_precision, data_scale, nullable
FROM all_tab_columns
WHERE table_name NOT LIKE '%$%' 
    AND table_name NOT LIKE 'LOGMNR%' 
    AND table_name NOT LIKE 'MVIEW%' 
    AND table_name NOT IN ('SQLPLUS_PRODUCT_PROFILE','HELP', 'PRODUCT_PRIVS')
    -- skip nested table columns
    AND data_type_owner is null
    AND lower(owner) = :owner
ORDER BY table_name, column_id";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, ":owner", databaseName.ToLower(), ReadColumn));
        }
Exemple #21
0
        public List <DataStoredProcedure> ReadProcedures(IDbConnection conn, string db)
        {
            string sql = @"
select 'Foo' ""TableSchema""
        , p.RDB$PROCEDURE_NAME ""Name""
        , 'PROCEDURE' ""Type""
        , pp.RDB$PARAMETER_NAME ""ParameterName""
        , pp.RDB$PARAMETER_TYPE ""IsOutputParameter""
        , t.RDB$TYPE_NAME ""ParameterType""
        , f.RDB$FIELD_LENGTH ""Length""
        , f.RDB$FIELD_PRECISION ""Precision""
        , f.RDB$FIELD_SCALE ""Scale""
        , pp.RDB$DEFAULT_VALUE ""DefaultValue""
        , case when p.RDB$PROCEDURE_OUTPUTS is null then 0 else 1 end ""BodyContainsSelectStatement""
    from RDB$PROCEDURES p
        inner join RDB$PROCEDURE_PARAMETERS pp on pp.RDB$PROCEDURE_NAME = p.RDB$PROCEDURE_NAME
        inner join RDB$FIELDS f on f.RDB$FIELD_NAME = pp.RDB$FIELD_SOURCE
        inner join RDB$TYPES t on t.RDB$TYPE = f.RDB$FIELD_TYPE and t.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
    where p.RDB$SYSTEM_FLAG = 0
union
select @db ""TableSchema""
        , p.RDB$FUNCTION_NAME ""Name""
        , 'FUNCTION' ""Type""
        , pp.RDB$FUNCTION_NAME ""ParameterName""
        , case when pp.rdb$mechanism = 5 then 1 else 0 end ""IsOutputParameter""
        , t.RDB$TYPE_NAME ""ParameterType""
        , pp.RDB$FIELD_LENGTH ""Length""
        , pp.RDB$FIELD_PRECISION ""Precision""
        , pp.RDB$FIELD_SCALE ""Scale""
        , null ""DefaultValue""
        , 0 ""BodyContainsSelectStatement""
    from RDB$FUNCTIONS p
        inner join RDB$FUNCTION_ARGUMENTS pp on pp.RDB$FUNCTION_NAME = p.RDB$FUNCTION_NAME
        inner join RDB$TYPES t on t.RDB$TYPE = pp.RDB$FIELD_TYPE and t.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
    where p.RDB$SYSTEM_FLAG = 0
";

            return(DataCommand.Find <DataStoredProcedure>(conn, sql, "@db", db.ToLower(), ReadProcedure));
        }
Exemple #22
0
        protected virtual List <DataConstraint> ReadConstraints(IDbConnection conn, string db)
        {
            // TODO: Only supports constrains where the columns have the same name.
            string sql = @"
select tbl.RDB$CONSTRAINT_NAME ""ConstraintName""
       , 'Foo' ""TableSchema""
       , tbl.RDB$RELATION_NAME ""TableName""
       , col.RDB$FIELD_NAME ""ColumnName""
       , 'Foo'  ""ReferencedTableSchema""
       , rtbl.RDB$RELATION_NAME ""ReferencedTableName""
       , rcol.RDB$FIELD_NAME ""ReferencedColumnName""
    from RDB$RELATION_CONSTRAINTS tbl
        inner join RDB$INDEX_SEGMENTS col on col.RDB$INDEX_NAME = tbl.RDB$INDEX_NAME
        inner join RDB$REF_CONSTRAINTS ref on ref.RDB$CONSTRAINT_NAME = tbl.RDB$CONSTRAINT_NAME
        inner join RDB$RELATION_CONSTRAINTS rtbl on rtbl.RDB$CONSTRAINT_NAME = ref.RDB$CONST_NAME_UQ
        inner join RDB$INDEX_SEGMENTS rcol on rcol.RDB$INDEX_NAME = rtbl.RDB$INDEX_NAME
    where tbl.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' and col.RDB$FIELD_NAME = rcol.RDB$FIELD_NAME
    order by tbl.RDB$RELATION_NAME
";

            return(DataCommand.Find <DataConstraint>(conn, sql, "@db", db, ReadConstraint));
        }
Exemple #23
0
        protected override IList <IDataTableColumn> ReadColumns(IDbConnection connectionString, string databaseName)
        {
            var sql = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS";

            return(DataCommand.Find <IDataTableColumn>(connectionString, sql, null, null, ReadColumn));
        }