#!/bin/bash
#
# test_pgsql_statistic
#
#############################
#
SAVEBASEDIR=/dev/shm
#
if [[ ! -f /etc/uls/pgsql_statistic.conf ]]
 then
  exit 0
fi
#
if [[ -f /etc/uls/pgsql.conf ]]
 then
  . /etc/uls/pgsql.conf
else
  echo "/etc/uls/pgsql.conf not found."
  exit 0
fi
#
if [[ $# -lt 2 ]]
 then
  set `date '+%d.%m.%y %T %z'`
fi
#
DT="$1 $2"
TZ="$3"
#
SAVEDIR=$SAVEBASEDIR/uls.pgsql.statistic
mkdir -p $SAVEDIR
SAVESTAT=$SAVEDIR/last
#
( L=`psql -c '\encoding'`
  echo "L;;;;;;$L;"
  DBS=`psql -t -F ';' -A -c '\l' | cut -d ';' -f 1 | grep -v 'template[01]'`
  echo "D;$DT;${ULSNAME:-${ULSHOSTNAME:-`hostname`}};PostgreSQL;"
  grep '^ *[[{].*[]}]' /etc/uls/pgsql_statistic.conf | sed 's/^[^[{]*\([[{]\)/\1 /;s/[]}].*//' | while read a v
   do
    if [[ $a = "[" ]]
     then
      echo $v
    else
      for d in $DBS
       do
        echo $v $d
      done
    fi
  done | while read v d
   do
    ( egrep -v '^ *#|^ *$' /etc/uls/pgsql_statistic.conf | awk -v D=$d '
        $1 ~ "[[{]'"$v"'[]}]" {
            S = 1
            printf("%s", $1)
            for( i = 2; i <= NF; i++ )
              printf(";%s", $i)
            printf("\n")
            next
          }
        S { if( $1 ~ "[[{][a-zA-Z_].*[]}]" )
              exit
            if( D != "" )
              gsub("{}", D, $1)
            fi
            printf("%s;%s;%s;%s;%s", $1, $2, $3, $4, $5)
            for( i = 6; i <= NF; i++ )
              printf(" %s", $i)
            printf("\n")
          }'
      if [[ -f $SAVESTAT.$d.$v ]]
       then
        echo "MAGIC17348576"
        cat $SAVESTAT.$d.$v
      fi
      echo "MAGIC27348576"
      if [[ -n "$d" ]]
       then
        D="-d $d"
      else
        D=""
      fi
      echo "\\pset footer off
select * from $v" | /usr/bin/psql $D -A -F ';' -q >$SAVESTAT.$d.$v
      date '+MAGIT7348576;%s' >>$SAVESTAT.$d.$v
      cat $SAVESTAT.$d.$v
    ) | awk -F ';' '
function flformat(f)
{ if( f < 0.0000001 )
    d = 0
  else
  { d = 4 - log(f)/2.3
    if( d < 0 )
     d = 0
  }
  return sprintf("%0.*f", d, f)
}

function firstpar(fkt)
{ t = fkt
  gsub(/[a-zA-Z_][a-zA-Z_]*/, "|&|", t)
  sn = split(t, spt, "|")
  for(i = 1; i <= sn; i++)
  { if( spt[i] ~ "^[a-zA-Z_][a-zA-Z_]*$" )
      return spt[i]
  }
  return ""
}

function runfkt(fkt, s)
{ t = fkt
  gsub(/[a-zA-Z_][a-zA-Z_]*/, "|&|", t)
  sn = split(t, spt, "|")
  rfkt = ""
  for(i = 1; i <= sn; i++)
  { if( spt[i] ~ "^[a-zA-Z_][a-zA-Z_]*$" )
    { if(  w2[s":"n2n[spt[i]]] == "" )
        return ""
      rfkt = rfkt " " w2[s":"n2n[spt[i]]]
    }
    else
      rfkt = rfkt " " spt[i]
  }
  print "scale=12;" rfkt |& "bc 2>&1"
  "bc 2>&1" |& getline ret
  if( ret ~ "^-?[0-9.]+$" )
    return ret
  else
    return ""
}

function rundfkt(fkt, s, so)
{ t = fkt
  gsub(/[a-zA-Z_][a-zA-Z_]*/, "|&|", t)
  sn = split(t, spt, "|")
  rfkt = ""
  for(i = 1; i <= sn; i++)
  { if( spt[i] ~ "^[a-zA-Z_][a-zA-Z_]*$" )
    { if( w2[s":"n2n[spt[i]]] == "" || w1[so":"n1n[spt[i]]] == "" )
        return ""
      rfkt = rfkt " (" w2[s":"n2n[spt[i]]] " - " w1[so":"n1n[spt[i]]] ")"
    }
    else
      rfkt = rfkt " " spt[i]
  }
  print "scale=12;" rfkt |& "bc 2>&1"
  "bc 2>&1" |& getline ret
  if( ret ~ "^-?[0-9.]+$" )
    return ret
  else
    return ""
}

function runsumfkt(fkt)
{ t = fkt
  gsub(/[a-zA-Z_][a-zA-Z_]*/, "|&|", t)
  sn = split(t, spt, "|")
  rfkt = ""
  for(i = 1; i <= sn; i++)
  { if( spt[i] ~ "^[a-zA-Z_][a-zA-Z_]*$" )
    { if(  ws2[n2n[spt[i]]] == "" )
        return ""
      rfkt = rfkt " " ws2[n2n[spt[i]]]
    }
    else
      rfkt = rfkt " " spt[i]
  }
  print "scale=12;" rfkt |& "bc 2>&1"
  "bc 2>&1" |& getline ret
  if( ret ~ "^-?[0-9.]+$" )
    return ret
  else
    return ""
}

function runsumdfkt(fkt)
{ t = fkt
  gsub(/[a-zA-Z_][a-zA-Z_]*/, "|&|", t)
  sn = split(t, spt, "|")
  rfkt = ""
  for(i = 1; i <= sn; i++)
  { if( spt[i] ~ "^[a-zA-Z_][a-zA-Z_]*$" )
    { if( ws2[n2n[spt[i]]] == "" || ws1[n1n[spt[i]]] == "" )
        return ""
      rfkt = rfkt " (" ws2[n2n[spt[i]]] " - " ws1[n1n[spt[i]]] ")"
    }
    else
      rfkt = rfkt " " spt[i]
  }
  print "scale=12;" rfkt |& "bc 2>&1"
  "bc 2>&1" |& getline ret
  if( ret ~ "^-?[0-9.]+$" )
    return ret
  else
    return ""
}


BEGIN { C = 0 }

/MAGIC17348576/ { C = 1
                  next
                }

/MAGIC27348576/ { C = 3
                  next
                }

C == 0 { if( $1 ~ "[[{]..*[]}]" )
         { for( i = 2; i <= NF; i++ )
             sep[i-1] = $i
           nsep = NF-1
           ng = 0
         }
         else
         { if( $2 != "" )
           { an[++ng] = $1
             ar[ng] = $2
             am[ng] = $3
             if( $3 ~ "S" )
               sflag = 1
             au[ng] = $4
             af[ng] = $5
           }
         } 
       }

C == 1 { for( i = 1; i <= NF; i++ )
           n1n[$i] = i
         cn1 = NF
         C = 2
         cc1 = 0
         next
       }

C == 2 { if( $1 == "MAGIT7348576" )
         { starttime = $2
           next
         }
         cc1++
         for( i = 1; i <= NF; i++ )
           w1[cc1":"i] = $i
         if( nsep >= 1 )
         { sepname = $n1n[sep[1]]
           for( i = 2; i <= nsep; i++ )
             sepname = sepname ":" $n1n[sep[i]]
           sepnum1[sepname] = cc1
         }
         else
           w1idnum[$1] = cc1
         if( sflag )
         { for( i = 1; i <= NF; i++ )
             ws1[i] += $i
         }
         next
       }

C == 3 { for( i = 1; i <= NF; i++ )
         { n2n[$i] = i
           n2[i] = $i
         }
         cn2 = NF
         C = 4
         cc2 = 0
         next
       }

C == 4 { if( $1 == "MAGIT7348576" )
         { endtime = $2
           next
         }
         cc2++
         for( i = 1; i <= NF; i++ )
           w2[cc2":"i] = $i
         if( nsep >= 1 )
         { sepname = $n2n[sep[1]]
           for( i = 2; i <= nsep; i++ )
             sepname = sepname ":" $n2n[sep[i]]
           sepnum2[sepname] = cc2
         }
         if( sflag )
         { for( i = 1; i <= NF; i++ )
             ws2[i] += $i
         }
         next
       }

END { secdiff = endtime - starttime
      for( ing = 1; ing <= ng; ing++ )
      { m = am[ing] == "" ? "t" : am[ing]
        tst = an[ing]
        fkt = af[ing]
        if( fkt == "" )
          r = ar[ing]
        else
          r = firstpar(fkt)
        if( r == "" )
          continue
        if( au[ing] == "" )
        { e = "#"
          f = 1
        }
        else
        { if( split(au[ing], g, ":") > 1 )
            f = g[2]
          else
            f = 1
          e = g[1]
          if( e == "" )
            e = "#"
        }
        if( m ~ "S" )
        { for( n = 1; n < cc2; n++ )
          { if( n2[n] ~ "^"r"$" && ws2[n] != "" )
            { if( fkt == "" )
              { dnam = n2[n]
                if( m ~ "t" )
                { if( f == 1 )
                    print "V;;;;statistic:" tst ";" dnam ";" ws2[n] ";" e
                  else
                    print "V;;;;statístic:" tst ";" dnam ";" flformat(ws2[n]/f) ";" e
                }
                if( m ~ "d" && (no = n1n[dnam]) )
                { if( f == 1 )
                    print "V;;;;statistic:" tst ";" dnam ";" ws2[n] - ws1[no] ";" e
                  else
                    print "V;;;;statistic:" tst ";" dnam ";" flformat((w2[n] - ws1[no])/f) ";" e
                }
                if( m ~ "s" && secdiff > 0 && (no = n1n[dnam]) )
                { if( f == 1 )
                    print "V;;;;statistic:" tst ";" dnam "-ps;" flformat((ws2[n] - w1[no])/secdiff) ";" e "/s"
                  else
                    print "V;;;;statistic:" tst ";" dnam "-ps;" flformat((ws2[n] - w1[no])/f/secdiff) ";" e "/s"
                }
              }
              else
              { dnam = ar[ing]
                if( m ~ "[sd]" )
                { if( cc1 > 1 )
                    ret = runsumdfkt(fkt)
                }
                else
                  ret = runsumfkt(fkt)
                if( ret != "" && m ~ "s" )
                { if( secdiff > 0 )
                    ret = ret / secdiff
                  else
                    ret = ""
                }
                if( ret != "" )
                { if( f == 1 )
                    print "V;;;;statistic:" tst ";" dnam ";" flformat(ret) ";" e
                  else
                    print "V;;;;statistic:" tst ";" dnam ";" flformat(ret/f) ";" e
                }
              }
            }
          }
        }
        else if( nsep == 0 )
        { tstnam = tst
          for( s = 1 ; s < cc2; s++ )
          { so = w1idnum[w1[s":1"]]
            for( n = 1; n <= cn2; n++ )
            { if( n2[n] ~ "^"r"$" && w2[s":"n] != "" )
              { if( w2[s":"n] ~ "^[-+]?[0-9.]+$" )
                { if( fkt == "" )
                  { dnam = n2[n]
                    if( m ~ "t" )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(w2[s":"n]/f) ";" e
                    }
                    if( m ~ "d" && so && (no = n1n[dnam]) )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] - w1[so":"no] ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat((w2[s":"n] - w1[so":"no])/f) ";" e
                    }
                    if( m ~ "s" && secdiff > 0 && so && (no = n1n[dnam]))
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam "-ps;" flformat((w2[s":"n] - w1[so":"no])/secdiff) ";" e "/s"
                      else
                        print "V;;;;statistic:" tstnam ";" dnam "-ps;" flformat((w2[s":"n] - w1[so":"no])/f/secdiff) ";" e "/s"
                    }
                  }
                  else
                  { dnam = ar[ing]
                    if( m ~ "[sd]" )
                    { if( so )
                        ret = rundfkt(fkt, s, so)
                    }
                    else
                      ret = runfkt(fkt, s)
                    if( ret != "" && m ~ "s" )
                    { if( secdiff > 0 )
                        ret = ret / secdiff
                      else
                        ret = ""
                    }
                    if( ret != "" )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(ret) ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(ret/f) ";" e
                    }
                  }
                }
                else
                { dnam = ar[ing]
                  print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] "; "
                }
              }
            }
          }
        }
        else
        { for( sn in sepnum2 )
          { s = sepnum2[sn]
            so = sepnum1[sn]
            tstnam = tst ":" sn
            for( n = 1; n <= cn2; n++ )
            { if( n2[n] ~ "^"r"$" && w2[s":"n] != "" )
              { if( w2[s":"n] ~ "^[-+]?[0-9.]+$" )
                { if( fkt == "" )
                  { dnam = n2[n]
                    if( m ~ "t" )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(w2[s":"n]/f) ";" e
                    }
                    if( m ~ "d" && so && (no = n1n[dnam]) )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] - w1[so":"no] ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat((w2[s":"n] - w1[so":"no])/f) ";" e
                    }
                    if( m ~ "s" && secdiff > 0 && so && (no = n1n[dnam]))
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam "-ps;" flformat((w2[s":"n] - w1[so":"no])/secdiff) ";" e "/s"
                      else
                        print "V;;;;statistic:" tstnam ";" dnam "-ps;" flformat((w2[s":"n] - w1[so":"no])/f/secdiff) ";" e "/s"
                    }
                  }
                  else
                  { dnam = ar[ing]
                    if( m ~ "[sd]" )
                    { if( so )
                        ret = rundfkt(fkt, s, so)
                    }
                    else
                      ret = runfkt(fkt, s)
                    if( ret != "" && m ~ "s" )
                    { if( secdiff > 0 )
                        ret = ret / secdiff
                      else
                        ret = ""
                    }
                    if( ret != "" )
                    { if( f == 1 )
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(ret) ";" e
                      else
                        print "V;;;;statistic:" tstnam ";" dnam ";" flformat(ret/f) ";" e
                    }
                  }
                }
                else
                { dnam = ar[ing]
                  print "V;;;;statistic:" tstnam ";" dnam ";" w2[s":"n] "; "
                }
              }
            }
          }
        }
      }
    }'
  done
) | send_test_tab
