> I'm looking for a tool that (should) be relatively simple to build with jOOQ
> (and maybe someone already did it): I'd like to pass it a list of records
> (or tables + PKs) and it should dump the convex hull of all FK relations to
> an XML file.
>
> Usage: Extract test data from a testing/production system.
>
> Anyone?
That's an interesting problem. How about this canonical, yet a bit
inefficient algorithm:
-------------------------------------------------
public class Hull {
public static Set<Table<?>> hull(Table<?>... tables) {
Set<Table<?>> result = new HashSet<Table<?>>(Arrays.asList(tables));
// Loop as long as there are no new result tables
int size = 0;
while (result.size() > size) {
size = result.size();
for (Table<?> table : new ArrayList<Table<?>>(result)) {
// Follow all outbound foreign keys
for (ForeignKey<?, ?> fk : table.getReferences()) {
result.add(fk.getKey().getTable());
}
// Follow all inbound foreign keys from tables within
the same schema
for (Table<?> other : table.getSchema().getTables()) {
if (other.getReferencesTo(table).size() > 0) {
result.add(other);
}
}
}
}
return result;
}
public static void main(String[] args) {
System.out.println(hull(T_AUTHOR));
}
}
-------------------------------------------------
Note that this algorithm relies on a set of generated tables from a
single schema and doesn't follow foreign keys, if they lead to enum
types generated from "master data tables". A more reliable solution
would be to query the Oracle dictionary tables as such:
-------------------------------------------------
-- "graph" denotes an undirected foreign key reference graph
-- for schema "TEST"
with graph as (
select c1.table_name t1, c2.table_name t2
from all_constraints c1
join all_constraints c2
on c1.owner = c2.r_owner
and c1.constraint_name = c2.r_constraint_name
where c1.owner = 'TEST'
union all
select c2.table_name t1, c1.table_name t2
from all_constraints c1
join all_constraints c2
on c1.owner = c2.r_owner
and c1.constraint_name = c2.r_constraint_name
where c1.owner = 'TEST'
),
-- "paths" are all directed paths within that schema as a #-delimited string
paths as (
select sys_connect_by_path(t1, '#') || '#' path
from graph
connect by nocycle prior t1 = t2
),
-- "subgraph" are all those directed paths that go trough a given table T_AUTHOR
subgraph as (
select distinct regexp_replace(path, '^#(.*)#$', '\1') path
from paths
where path like '%#T_AUTHOR#%'
)
-- This XML-trick splits paths and generates rows for every distinct
select distinct cast(t.column_value.extract('//text()') as
varchar2(4000)) table_name
from subgraph,
table(xmlsequence(xmltype(
'<x><x>' || replace(path, '#', '</x><x>') ||
'</x></x>').extract('//x/*'))) t
order by table_name
-------------------------------------------------
Happy deciphering this beast! I'll pay you a beer if you manage to
phrase this query with jOOQ (contributing the required extensions, of
course) ;-)
Cheers
Lukas
