#!/bin/bash
O_PATH=/var/opt/bukdbase/backup
LOGDIR=/var/opt/bukdbase/log
### значения по умолчанию (переопределяются в cmdline)
DBNAME=ubuk
DBUSER=bukts
if [ ! -d $LOGDIR ]; then
  LOGDIR=$HOME
fi

psql="psql $DBNAME -U $DBUSER -h 127.0.0.1 -qAt"

## сформировать имя log-файла из имени программы
LOG=$LOGDIR/$(basename $0).$(date +%y%m%d).log
echo >> $LOG
echo "$(date +%H:%M:%S) start $@" >> $LOG

### обработать командную строку
i=$#;  # число параметров в командной строке
while [ $i -ge "0" ]; do
  is="no"
  case $1 in
  -db) # имя БД
    is="yes"; DBNAME=$2 ;;
  -user|-U) # пользователь БД
    is="yes"; DBUSER=$2 ;;
  -op)
    is="yes"; O_PATH=$2 ;;
  esac
  if [ $is == "yes" ]; then
    shift
    i=`expr $i - 1`
  fi
  shift
  i=`expr $i - 1`
done

OFILE0=$O_PATH/testdb.txt
rm -f $OFILE0 2>/dev/null

MSG="Создание файла $OFILE0"
echo $MSG
echo "$(date +%H:%M:%S) $MSG" >> $LOG

SQL="SELECT 'VER='||sf_get_cur_ver();"
$psql -c "$SQL" >> $OFILE0
if [ $? != 0 ]; then
  MSG="Ошибка запроса $SQL"
  echo $MSG
  echo "$(date +%H:%M:%S) $MSG" >> $LOG
  exit 1
fi

#schema
for f1 in `$psql -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ( 'public', 'ldict', 'typdict', 'uaction', 'udict', 'uremote', 'userver_smoke', 'userver_talon', 'utranz') ORDER BY schema_name"`; do 
  SCHEMANAME=`echo $f1 | awk -F'|' '{print $1}'` 
  echo "Обработка схемы $SCHEMANAME"
  echo "SCHEMA: $SCHEMANAME" >> $OFILE0
  
  # table
  for f2 in `$psql -c "SELECT table_name FROM information_schema.tables WHERE table_schema = '$SCHEMANAME' AND table_type='BASE TABLE' ORDER BY table_name"`; do 
    TABLE=`echo $f2 | awk -F'|' '{print $1}'` 
    echo "Обработка таблицы $SCHEMANAME.$TABLE"
    echo "-TABLE: $SCHEMANAME.$TABLE" >> $OFILE0
    
    # columns
    for f3 in `$psql -c "SELECT ordinal_position, column_name, REPLACE(data_type,' ','_'), is_nullable, column_default FROM information_schema.columns WHERE table_schema = '$SCHEMANAME' AND table_name   = '$TABLE' ORDER BY ordinal_position;"`; do 
      CORD=`echo $f3 | awk -F'|' '{print $1}'` 
      CNAME=`echo $f3 | awk -F'|' '{print $2}'` 
      CTP=`echo $f3 | awk -F'|' '{print $3}'` 
      CNULL=`echo $f3 | awk -F'|' '{print $4}'` 
      CDEF=`echo $f3 | awk -F'|' '{print $5}'` 
      echo "--FIELD: $CORD:$CNAME:$CTP:$CNULL:$CDEF" >> $OFILE0
    done # columns
    
    # constraint
    for f3 in `$psql -c "SELECT con.conname, con.contype, array_to_string(con.conkey,',')  FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = '$SCHEMANAME' AND rel.relname = '$TABLE' ORDER BY con.conname, con.contype;"`; do 
      CNAME=`echo $f3 | awk -F'|' '{print $1}'` 
      CTP=`echo $f3 | awk -F'|' '{print $2}'` 
      CA=`echo $f3 | awk -F'|' '{print $3}'` 
      echo "--CONSTRAINT: $CNAME:$CTP:$CA" >> $OFILE0
    done # constraint
    
    if [ "$SCHEMANAME" == "typdict" ]; then
      ORD=`$psql -c "SELECT array_to_string(array_agg(t.attname),',') AS columns_of_pk FROM (SELECT ind_column.attname FROM pg_class tbl INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace INNER JOIN pg_index ind ON ind.indrelid = tbl.oid INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid WHERE sch.nspname = '$SCHEMANAME' AND tbl.relname = '$TABLE' AND ind.indisprimary ORDER BY ind_column.attnum) as t"`
      $psql -c "SELECT * FROM $SCHEMANAME.$TABLE ORDER BY $ORD" >> $OFILE0
    fi
  done # table
  
  echo "Обработка view $SCHEMANAME"
  # view
  for f2 in `$psql -c "SELECT table_name, md5(pg_get_viewdef(table_schema||'.'||quote_ident(table_name), true)), REPLACE(obj_description((table_schema||'.'||quote_ident(table_name))::regclass),' ','_') as desc FROM information_schema.tables WHERE table_schema = '$SCHEMANAME' AND table_type='VIEW' ORDER BY table_name"`; do 
    VIEW=`echo $f2 | awk -F'|' '{print $1}'` 
    MD5=`echo $f2 | awk -F'|' '{print $2}'` 
    DESC=`echo $f2 | awk -F'|' '{print $3}' | awk -F'_' '{print $1}'` 
    echo "-VIEW : $SCHEMANAME.$VIEW|$MD5|$DESC" >> $OFILE0
  done # view
  
  echo "Обработка функций $SCHEMANAME"
  # functions
  for f2 in `$psql -c "SELECT routine_name, type_udt_name, md5(routine_definition) FROM information_schema.routines WHERE specific_schema = '$SCHEMANAME' AND routine_type = 'FUNCTION' ORDER BY routine_name, type_udt_name"`; do 
    FUNC=`echo $f2 | awk -F'|' '{print $1}'`
    TP=`echo $f2 | awk -F'|' '{print $2}'`
    MD5=`echo $f2 | awk -F'|' '{print $3}'` 
    echo "-FUNC : $SCHEMANAME.$FUNC|$TP|$MD5" >> $OFILE0
  done # functions
  
  echo "Обработка триггеров $SCHEMANAME"
  # triggers
  for f2 in `$psql -c "SELECT trigger_name, event_manipulation, action_order, REPLACE(action_statement,' ','_'), action_timing, event_object_schema, event_object_table FROM information_schema.triggers WHERE trigger_schema='$SCHEMANAME' ORDER BY trigger_name, event_manipulation, action_order, action_statement, action_timing, event_object_schema, event_object_table"`; do 
    R1=`echo $f2 | awk -F'|' '{print $1}'`
    R2=`echo $f2 | awk -F'|' '{print $2}'`
    R3=`echo $f2 | awk -F'|' '{print $3}'`
    R4=`echo $f2 | awk -F'|' '{print $4}'`
    R5=`echo $f2 | awk -F'|' '{print $5}'`
    R6=`echo $f2 | awk -F'|' '{print $6}'`
    R7=`echo $f2 | awk -F'|' '{print $7}'`
    echo "-TRIGGER : $SCHEMANAME.$R1|$R2|$R3|$R4|$R5|$R6|$R7" >> $OFILE0
  done # triggers
done # schema

MSG="Файл создан успешно $OFILE0"
echo $MSG
echo "$(date +%H:%M:%S) $MSG" >> $LOG

md5sum $OFILE0 | awk '{print $1;}'

exit 0
