src/tools/findoidjoins/make_oidjoins_check

   1 #! /bin/sh
   2
   3 # $PostgreSQL$
   4
   5 # You first run findoidjoins on the template1 database, and send that
   6 # output into this script to generate a list of SQL statements.
   7
   8 # NOTE: any field that findoidjoins thinks joins to more than one table
   9 # will NOT be checked by the output of this script.  You should be
  10 # suspicious of multiple entries in findoidjoins' output.
  11
  12 # Caution: you may need to use GNU awk.
  13 AWK=${AWK:-awk}
  14
  15 TMP="${TMPDIR:-/tmp}/make_oidjoins_check.$$"
  16 trap "rm -rf $TMP" 0 1 2 3 15
  17
  18 # Create a temporary directory with the proper permissions so no one can
  19 # intercept our temporary files and cause a security breach.
  20 OMASK="`umask`"
  21 umask 077
  22 if ! mkdir $TMP
  23 then    echo "Can't create temporary directory $TMP." 1>&2
  24         exit 1
  25 fi
  26 umask "$OMASK"
  27 unset OMASK
  28
  29 INPUTFILE="$TMP/a"
  30 DUPSFILE="$TMP/b"
  31 NONDUPSFILE="$TMP/c"
  32
  33 # Read input
  34 cat "$@" >$INPUTFILE
  35
  36 # Look for fields with multiple references.
  37 cat $INPUTFILE | cut -d' ' -f2 | sort | uniq -d >$DUPSFILE
  38 if [ -s $DUPSFILE ] ; then
  39         echo "Ignoring these fields that link to multiple tables:" 1>&2
  40         cat $DUPSFILE 1>&2
  41 fi
  42
  43 # Get the non-multiply-referenced fields.
  44 cat $INPUTFILE | while read LINE
  45 do
  46         set -- $LINE
  47         grep "^$2\$" $DUPSFILE >/dev/null 2>&1 || echo $LINE
  48 done >$NONDUPSFILE
  49
  50 # Generate the output.
  51 cat $NONDUPSFILE |
  52 $AWK -F'[ \.]' '\
  53         BEGIN \
  54         {
  55                 printf "\
  56 --\n\
  57 -- This is created by pgsql/src/tools/findoidjoins/make_oidjoins_check\n\
  58 --\n";
  59         }
  60         {
  61                 printf "\
  62 SELECT  ctid, %s \n\
  63 FROM    %s.%s fk \n\
  64 WHERE   %s != 0 AND \n\
  65         NOT EXISTS(SELECT 1 FROM %s.%s pk WHERE pk.oid = fk.%s);\n",
  66         $4, $2, $3, $4,
  67         $6, $7, $4;
  68         }'
  69
  70 exit 0