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