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