| #! /bin/sh |
| |
| # $PostgreSQL: pgsql/src/tools/findoidjoins/make_oidjoins_check,v 1.3 2008/10/13 12:59:29 tgl Exp $ |
| |
| # You first run findoidjoins on the template1 database, and send that |
| # output into this script to generate a list of SQL statements. |
| |
| # NOTE: any field that findoidjoins thinks joins to more than one table |
| # will NOT be checked by the output of this script. You should be |
| # suspicious of multiple entries in findoidjoins' output. |
| |
| # Caution: you may need to use GNU awk. |
| AWK=${AWK:-awk} |
| |
| TMP="${TMPDIR:-/tmp}/make_oidjoins_check.$$" |
| trap "rm -rf $TMP" 0 1 2 3 15 |
| |
| # Create a temporary directory with the proper permissions so no one can |
| # intercept our temporary files and cause a security breach. |
| OMASK="`umask`" |
| umask 077 |
| if ! mkdir $TMP |
| then echo "Can't create temporary directory $TMP." 1>&2 |
| exit 1 |
| fi |
| umask "$OMASK" |
| unset OMASK |
| |
| INPUTFILE="$TMP/a" |
| DUPSFILE="$TMP/b" |
| NONDUPSFILE="$TMP/c" |
| |
| # Read input |
| cat "$@" >$INPUTFILE |
| |
| # Look for fields with multiple references. |
| cat $INPUTFILE | cut -d' ' -f2 | sort | uniq -d >$DUPSFILE |
| if [ -s $DUPSFILE ] ; then |
| echo "Ignoring these fields that link to multiple tables:" 1>&2 |
| cat $DUPSFILE 1>&2 |
| fi |
| |
| # Get the non-multiply-referenced fields. |
| cat $INPUTFILE | while read LINE |
| do |
| set -- $LINE |
| grep "^$2\$" $DUPSFILE >/dev/null 2>&1 || echo $LINE |
| done >$NONDUPSFILE |
| |
| # Generate the output. |
| cat $NONDUPSFILE | |
| $AWK -F'[ \.]' '\ |
| BEGIN \ |
| { |
| printf "\ |
| --\n\ |
| -- This is created by pgsql/src/tools/findoidjoins/make_oidjoins_check\n\ |
| --\n"; |
| } |
| { |
| printf "\ |
| SELECT ctid, %s \n\ |
| FROM %s.%s fk \n\ |
| WHERE %s != 0 AND \n\ |
| NOT EXISTS(SELECT 1 FROM %s.%s pk WHERE pk.oid = fk.%s);\n", |
| $4, $2, $3, $4, |
| $6, $7, $4; |
| }' |
| |
| exit 0 |