sql/builder.sh

   1 #! /bin/bash
   2 # set -x
   3 #
   4 MATCH_DROP='^DROP.*([^-]...|.[^-]..|..[^M].|...[^F])$|^ALTER TABLE.* DROP .*([^-]...|.[^-]..|..[^M].|...[^F])$|^DELETE .*([^-]...|.[^-]..|..[^M].|...[^F])$|--MB$'
   5
   6
   7 #  functions
   8 usage () {
   9     echo " USAGE"
  10     echo "   ./builder <command> [-d|--dryrun] [-a|--allfiles] [-s|--short] ..."
  11     echo "   ./builder <-h|--help|help>"
  12     echo "   commands are:"
  13     echo "       create"
  14     echo "       destroy"
  15     echo "       clean"
  16     echo "       build"
  17     echo "       rebuild"
  18     echo "       psql"
  19     echo "       piped"
  20     echo "       add <filesql> [<filesql2> [..]]"
  21     echo "       del <filesql> [<filesql2> [..]]"
  22     echo "       res <filesql> [<filesql2> [..]]"
  23     echo "       dump [dumpfile]"
  24     echo "       dumpall [dumpfile]"
  25     echo "       all"
  26     echo
  27     echo "The match rule for clean lines is:"
  28     echo "  [$MATCH_DROP]"
  29     echo "NOTE: to invert normal 'del' rules add '--MF' (move forward) suffix to each line"
  30     echo "      to invert normal 'add' rules add '--MB' (move backward) suffix to each line"
  31
  32     exit $1
  33 }
  34
  35 sqlexe () {
  36     local sht
  37     sht=$1
  38
  39     if [ "$SHORT" = "y" ];  then
  40         sed "s/#PFX#/$PFX/g" | psql -a $pg_args 2>&1 | egrep 'ERROR|^-- MESG'
  41     else
  42         sed "s/#PFX#/$PFX/g" | psql -a $pg_args
  43     fi
  44
  45     return 0
  46 }
  47
  48 one_or_all() {
  49     if [ "$ALL_FILES" = "y" ]; then
  50         sfx_files='*'
  51     else
  52         sfx_files='*.sql'
  53     fi
  54
  55     if [ "$1" = "" ]; then
  56         cat sql.d/$sfx_files
  57     else
  58         cat "$1"
  59     fi
  60 }
  61
  62 #
  63 #  MAIN
  64 #
  65
  66 CMD=$1
  67 shift
  68
  69 while [ $# -gt 0 ]; do
  70     case $1 in
  71         -d|--dryrun)
  72             DRY_RUN=y
  73             psql () {
  74                 echo "MOCKPSQL params: $@"
  75                 cat
  76             }
  77             ;;
  78         -a|--allfiles)
  79             ALL_FILES=y
  80             ;;
  81         -s|--short)
  82             SHORT=y
  83             ;;
  84         *)
  85             break
  86             ;;
  87     esac
  88     shift
  89 done
  90
  91 if [ -f $HOME/.brisk-db.conf ]; then
  92     source $HOME/.brisk-db.conf
  93 elif [ -f $HOME/.db.conf ]; then
  94     source $HOME/.db.conf
  95 else
  96     DBHOST=127.0.0.1
  97     DBUSER=brisk
  98     DBPORT=5432
  99     DBBASE=brisk
 100     DBPASS=briskpass
 101     PFX="bsk_"
 102 fi
 103
 104 if [ -f $HOME/.brisk_install ]; then
 105     source $HOME/.brisk_install
 106 fi
 107
 108 pg_args=""
 109 test "$DBHOST" != "" && pg_args="$pg_args -h $DBHOST"
 110 test "$DBUSER" != "" && pg_args="$pg_args -U $DBUSER"
 111 test "$DBPORT" != "" && pg_args="$pg_args -p $DBPORT"
 112 test "$DBBASE" != "" && pg_args="$pg_args $DBBASE"
 113
 114
 115 case $CMD in
 116     "create")
 117         echo "su root"
 118         su root -c "su postgres -c \"echo \\\"DBUser passwd: $DBPASS\\\" ; createuser -S -D -R -P $DBUSER && createdb -E utf8 -O $DBUSER $DBBASE\""
 119         ;;
 120
 121     "destroy")
 122         echo "su root"
 123         su root -c "su postgres -c \"dropdb $DBBASE && dropuser $DBUSER\""
 124         ;;
 125     "clean")
 126         ( echo "-- MESG: clean start" ; one_or_all $2 | egrep "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ;   ) | sqlexe
 127         ;;
 128     "build")
 129         ( echo "-- MESG: build start" ; one_or_all $2 | egrep -v "$MATCH_DROP" ; echo "-- MESG: build end" ;   ) | sqlexe
 130         ;;
 131     "rebuild")
 132         ( echo "-- MESG: clean start" ; one_or_all $2 | egrep "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ; \
 133             echo "-- MESG: build start" ; one_or_all $2 | egrep -v "$MATCH_DROP" ; echo "-- MESG: build end" ;   ) \
 134             | sqlexe
 135         ;;
 136     "psql")
 137         psql $pg_args $@
 138         ;;
 139
 140     "piped")
 141         psql $pg_args -t -q -A -F '|' $@
 142         ;;
 143     "dump")
 144         if [ $# -eq 1 ]; then
 145             pg_dump -a --inserts -h $DBHOST -U $DBUSER $DBBASE
 146         else
 147             pg_dump -a --inserts -h $DBHOST -U $DBUSER $DBBASE > $1
 148         fi
 149         ;;
 150     "dumpall")
 151         if [ $# -eq 1 ]; then
 152             pg_dump -h $DBHOST -U $DBUSER $DBBASE
 153         else
 154             pg_dump -h $DBHOST -U $DBUSER $DBBASE > $1
 155         fi
 156         ;;
 157     "add")
 158         ( echo "-- MESG: add start" ; cat "$@" | egrep -v "$MATCH_DROP" ; echo "-- MESG: add end" ;   ) | sqlexe
 159         ;;
 160     "del")
 161         ( echo "-- MESG: del start" ; cat "$@" | egrep "$MATCH_DROP" | tac ; echo "-- MESG: del end" ;   ) | sqlexe
 162         ;;
 163     "res")
 164         ( echo "-- MESG: res start" ; cat "$@" | egrep "$MATCH_DROP" | tac ; cat "$@" | egrep -v "$MATCH_DROP" ; echo "-- MESG: del end" ;   ) | sqlexe
 165         ;;
 166     "help"|"-h"|"--help")
 167         usage 0
 168         ;;
 169     *)
 170         usage 1
 171         ;;
 172 esac
 173
 174 exit 0