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|-p|--allfiles|--devfiles] [-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 [ "$1" ]; then
  50         cat "$1"
  51     elif [ "$TYPE_FILES" = "a" ]; then
  52         cat sql.d/[0-9]*
  53     elif [ "$TYPE_FILES" = "d" ]; then
  54         cat sql.d/[0-9]*.{sql,devel}
  55     else
  56         cat sql.d/[0-9]*.sql
  57     fi
  58 }
  59
  60 #
  61 #  MAIN
  62 #
  63
  64 CMD=$1
  65 shift
  66
  67 while [ $# -gt 0 ]; do
  68     case $1 in
  69         -d|--dryrun)
  70             DRY_RUN=y
  71             psql () {
  72                 echo "MOCKPSQL params: $@"
  73                 cat
  74             }
  75             ;;
  76         -a|--allfiles)
  77             TYPE_FILES=a
  78             ;;
  79         -p|--devfiles)
  80             TYPE_FILES=d
  81             ;;
  82         -s|--short)
  83             SHORT=y
  84             ;;
  85         *)
  86             break
  87             ;;
  88     esac
  89     shift
  90 done
  91
  92 if [ -f $HOME/.brisk-db.conf ]; then
  93     source $HOME/.brisk-db.conf
  94 elif [ -f $HOME/.db.conf ]; then
  95     source $HOME/.db.conf
  96 else
  97     DBHOST=127.0.0.1
  98     DBUSER=brisk
  99     DBPORT=5432
 100     DBBASE=brisk
 101     DBPASS=briskpass
 102     PFX="bsk_"
 103 fi
 104
 105 if [ -f $HOME/.brisk_install ]; then
 106     source $HOME/.brisk_install
 107 fi
 108
 109 pg_args=""
 110 test "$DBHOST" != "" && pg_args="$pg_args -h $DBHOST"
 111 test "$DBUSER" != "" && pg_args="$pg_args -U $DBUSER"
 112 test "$DBPORT" != "" && pg_args="$pg_args -p $DBPORT"
 113 test "$DBBASE" != "" && pg_args="$pg_args $DBBASE"
 114
 115
 116 case $CMD in
 117     "create")
 118         echo "su root"
 119         su root -c "su postgres -c \"echo \\\"DBUser passwd: $DBPASS\\\" ; createuser -S -D -R -P $DBUSER && createdb -E utf8 -O $DBUSER $DBBASE\""
 120         ;;
 121
 122     "destroy")
 123         echo "su root"
 124         su root -c "su postgres -c \"dropdb $DBBASE && dropuser $DBUSER\""
 125         ;;
 126     "clean")
 127         ( echo "-- MESG: clean start" ; one_or_all $2 | egrep "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ;   ) | sqlexe
 128         ;;
 129     "build")
 130         ( echo "-- MESG: build start" ; one_or_all $2 | egrep -v "$MATCH_DROP" ; echo "-- MESG: build end" ;   ) | sqlexe
 131         ;;
 132     "rebuild")
 133         ( echo "-- MESG: clean start" ; one_or_all $2 | egrep "$MATCH_DROP" | tac ; echo "-- MESG: clean end" ; \
 134             echo "-- MESG: build start" ; one_or_all $2 | egrep -v "$MATCH_DROP" ; echo "-- MESG: build end" ;   ) \
 135             | sqlexe
 136         ;;
 137     "psql")
 138         psql $pg_args $@
 139         ;;
 140
 141     "piped")
 142         psql $pg_args -t -q -A -F '|' $@
 143         ;;
 144     "dump")
 145         if [ $# -eq 1 ]; then
 146             pg_dump -a --inserts -h $DBHOST -U $DBUSER $DBBASE
 147         else
 148             pg_dump -a --inserts -h $DBHOST -U $DBUSER $DBBASE > $1
 149         fi
 150         ;;
 151     "dumpall")
 152         if [ $# -eq 1 ]; then
 153             pg_dump -h $DBHOST -U $DBUSER $DBBASE
 154         else
 155             pg_dump -h $DBHOST -U $DBUSER $DBBASE > $1
 156         fi
 157         ;;
 158     "add")
 159         ( echo "-- MESG: add start" ; cat "$@" | egrep -v "$MATCH_DROP" ; echo "-- MESG: add end" ;   ) | sqlexe
 160         ;;
 161     "del")
 162         ( echo "-- MESG: del start" ; cat "$@" | egrep "$MATCH_DROP" | tac ; echo "-- MESG: del end" ;   ) | sqlexe
 163         ;;
 164     "res")
 165         ( echo "-- MESG: res start" ; cat "$@" | egrep "$MATCH_DROP" | tac ; cat "$@" | egrep -v "$MATCH_DROP" ; echo "-- MESG: del end" ;   ) | sqlexe
 166         ;;
 167     "help"|"-h"|"--help")
 168         usage 0
 169         ;;
 170     *)
 171         usage 1
 172         ;;
 173 esac
 174
 175 exit 0