Re: Transfering tables
You could drop referential integrity, load, then add the constraints.
Or if you are a peon without privileges, use the crude sort code below
that gives ONE order (of many) that can be loaded with ref integrity in
effect. (Slow to load with constraints on). Naturally, it can be improved.
If you run into problems with these scripts you're on yer own.
#!/bin/sh
# Name: load_order
# find dependencies in a database, list an order of loading tables
ref_sql $1 | rtsort | reverse
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#!/bin/sh
# Name: ref_sql
# Query for finding referential dependency pairs using Informix SQL.
#
usage () {
echo Usage: $0 database_name
echo Query for finding referential dependency pairs using Informix SQL.
exit 1;
}
############################################################################
# main
test $# -eq 1 || usage
DB=$1
dbaccess << EOF |
database $DB;
select
a.tabname,
b.tabname
from
"informix".sysreferences r,
"informix".systables a,
"informix".systables b,
"informix".sysconstraints c
where
r.ptabid = a.tabid and
c.tabid = b.tabid and
c.constrid = r.constrid;
EOF
# remove blanks, field names, duplicates
sed '/^$/d' |
sed '/tabname/d' |
sort -u
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#!/bin/sh
# Name: rtsort
# Depth first sort of directed graph from book: AWK by Aho, Wien???, Kernigan.
# input: pairs of names representing dependency: name name
# output in reversed order.
nawk '
{ if (!($1 in pcnt))
pcnt[$1] = 0
pcnt[$2]++
slist[$1, ++scnt[$1]] = $2
}
END { for (node in pcnt) {
nodecnt++
if (pcnt[node] == 0)
rtsort(node)
}
if (pncnt != nodecnt) {
print "error: input contains a cycle"
print "node: ",node
}
printf("\\n")
}
function rtsort(node, i, s) {
visited[node] = 1
for ( i = 1; i <= scnt[node]; i++)
if (visited[s = slist[node, i]] == 0)
rtsort(s)
else if (visited[s] == 1)
printf("error: nodes %s and %s are in a cycle\\n",s,node)
visited[node] = 2
printf("%s\\n", node)
pncnt++
}'
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#!/bin/sh
# Name: reverse
# Reverse order of lines input.
nawk '{ x[NR] = $0}
END { for (i = NR; i > 0; i--) print x[i] }'