#!/bin/bash
# $Id: slonikconfdump.sh,v 1.1.2.4 2009-06-18 16:52:22 cbbrowne Exp $
# This tool rummages through a Slony-I cluster, generating a slonik script
# suitable to recreate the cluster

# Start with:
# SLONYCLUSTER indicating the cluster name
echo "# building slonik config files for cluster ${SLONYCLUSTER}"
echo "# generated by: slonikconfdump.sh"
echo "# Generated on: " `date`
SS="\"_${SLONYCLUSTER}\""
echo "cluster name=${SLONYCLUSTER};"

function RUNQUERY () {
    local QUERY=$1
    RESULTSET=`psql -qtA -F ":" -R " " -c "${QUERY}"`
    echo ${RESULTSET}
}
function argn () {
    local V=$1
    local n=$2
    local res=`echo ${V} | cut -d : -f ${n}`
    echo $res
}
    
function arg1 () {
    echo `argn "$1" 1`
}
function arg2 () {
    echo `argn "$1" 2`
}
function arg3 () {
    echo `argn "$1" 3`
}

Q="select distinct pa_server from ${SS}.sl_path order by pa_server;"
PATHS=`RUNQUERY "${Q}"`
for svr in `echo ${PATHS}`; do
    CONNINFOQ="select pa_conninfo from ${SS}.sl_path where pa_server=${svr} order by pa_client asc limit 1;"
    conninfo=`RUNQUERY "${CONNINFOQ}"`
    echo "node ${svr} admin conninfo='${conninfo}';"
done

FIRSTNODEQ="select no_id, no_comment from ${SS}.sl_node order by no_id limit 1;"
NODE1=`RUNQUERY "${FIRSTNODEQ}"`
nn=`arg1 "${NODE1}"`
comment=`arg2 ${NODE1}`
echo "init cluster (id=${nn}, comment='${comment}');"

RESTOFNODESQ="select no_id from ${SS}.sl_node where no_id <> ${nn};"
NODES=`RUNQUERY "${RESTOFNODESQ}"`
for node in `echo ${NODES}`; do
    NODECOMMENT="select no_comment from ${SS}.sl_node where no_id = ${node};"
    comment=`RUNQUERY "${NODECOMMENT}"`
    echo "store node (id=${node}, comment='${comment}', event node=${nn});"
done

PATHSQ="select pa_server, pa_client, pa_connretry from ${SS}.sl_path order by pa_server, pa_client;"
PATHS=`RUNQUERY "${PATHSQ}"`
for sc in `echo $PATHS`; do
    server=`arg1 $sc`
    client=`arg2 $sc`
    retry=`arg3 $sc`
    PATHCONN="select pa_conninfo from ${SS}.sl_path where pa_server=${server} and pa_client=${client};"
    conninfo=`RUNQUERY "${PATHCONN}"`
    echo "store path (server=${server}, client=${client}, conninfo='${conninfo}', connretry=${retry});"
done

SETSQ="select set_id, set_origin from ${SS}.sl_set order by set_id;"
SETS=`RUNQUERY "${SETSQ}"`
for sc in `echo ${SETS}`; do
    set=`arg1 ${sc}`
    origin=`arg2 ${sc}`
    SETCOMMENT="select set_comment from ${SS}.sl_set where set_id=${set};"
    comment=`RUNQUERY "${SETCOMMENT}"`
    echo "create set (id=${set}, origin=${origin}, comment='${comment}');"
done

TABLESOVERVIEW="select tab_id,tab_set, set_origin from ${SS}.sl_table, ${SS}.sl_set where tab_set = set_id order by tab_id;"
TABIDS=`RUNQUERY "${TABLESOVERVIEW}"`
for tablebase in `echo ${TABIDS}`; do
    tab=`arg1 ${tablebase}`
    set=`arg2 ${tablebase}`
    origin=`arg3 ${tablebase}`
    TABNAME="select tab_relname from ${SS}.sl_table where tab_id = ${tab};"
    relname=`RUNQUERY "${TABNAME}"`
    TABNSP="select tab_nspname from ${SS}.sl_table where tab_id = ${tab};"
    nsp=`RUNQUERY "${TABNSP}"`
    TABIDX="select tab_idxname from ${SS}.sl_table where tab_id = ${tab};"
    idx=`RUNQUERY "${TABIDX}"`
    TABCOMMENT="select tab_comment from ${SS}.sl_table where tab_id = ${tab};"
    comment=`RUNQUERY "${TABCOMMENT}"`
    echo "set add table (id=${tab}, set id=${set}, origin=${origin}, fully qualified name='\"${nsp}\".\"${relname}\"', comment='${comment}', key='${idx}');"
done


SEQSQ="select seq_id,seq_set,set_origin from ${SS}.sl_sequence, ${SS}.sl_set where seq_set = set_id order by seq_id;"
SEQS=`RUNQUERY "${SEQSQ}"`
for sq in `echo ${SEQS}`; do
    seqid=`arg1 ${sq}`
    setid=`arg2 ${sq}`
    origin=`arg3 ${sq}`
    RELQ="select seq_relname from ${SS}.sl_sequence where seq_id = ${seqid};"
    relname=`RUNQUERY "${RELQ}"`
    NSQ="select seq_nspname from ${SS}.sl_sequence where seq_id = ${seqid};"
    nsp=`RUNQUERY "${NSQ}"`
    COMQ="select seq_comment from ${SS}.sl_sequence where seq_id = ${seqid};"
    comment=`RUNQUERY "${COMQ}"`
    echo "set add sequence(id=${seqid}, set id=${setid}, origin=${origin}, fully qualified name='\"${nsp}\".\"${relname}\"', comment='${comment}');"
done

Q="select set_id, set_origin from ${SS}.sl_set;"
SETS=`RUNQUERY "${Q}"`
for setinfo in `echo ${SETS}`; do
    set=`arg1 ${setinfo}`
    origin=`arg2 ${setinfo}`
    SUBQ="select sub_provider, sub_receiver from ${SS}.sl_subscribe where sub_set=${set};"
    # We use tsort to determine a feasible ordering for subscriptions, filter8ng out the origin node
    SUBRES=`psql -qtA -F " " -c "${SUBQ}" | tsort | egrep -v "^${origin}\$"`
    for recv in `echo ${SUBRES}`; do
	PROVIDERSQUERY="select sub_provider, sub_forward from ${SS}.sl_subscribe where sub_set=${set} and sub_receiver=${recv};"
	SUBSCRIPTIONS=`RUNQUERY "${PROVIDERSQUERY}"`
	prov=`arg1 ${SUBSCRIPTIONS}`
        if [ `arg2 ${SUBSCRIPTIONS}` = 't' ]; then
	    forw='YES'
        else
            forw='NO'
	fi
	echo "subscribe set (id=${set}, provider=${prov}, receiver=${recv}, forward=${forw}, omit copy=true);"
	if [ $prov != $origin ]; then
	    echo "wait for event (origin=${prov}, confirmed=${origin}, wait on=${prov}, timeout=0);"
	fi
	echo "sync (id=${origin});"
	echo "wait for event (origin=${origin}, confirmed=ALL, wait on=${origin}, timeout=0);"
    done
done