/*    
 *     pgstats -- collect and display statistics on postgres databases
 */
#include <stdio.h>
#include <sys/types.h>
#include <sys/file.h>

#ifndef sprite
#include <sys/signal.h>
#endif /* !sprite */

#include "tmp/c.h"
#include "tmp/oid.h"
#include "tmp/libpq-fe.h"

/* just in case someone left memory debugging turned on... */
#undef palloc
#undef pfree

RcsId("$Header: /private/postgres/sample/RCS/pgstats.c,v 1.2 1991/04/12 15:01:22 mao Exp $");

typedef struct _dblist_d {
    char		*dbname;
    char		*dbowner;
    ObjectId		dbownerId;
    struct _dblist_d	*next;
} DBLIST_DATA;

typedef DBLIST_DATA	*DBLIST;

extern char     *getenv();
extern char	*get_attr();
extern DBLIST	build_dblist();
int		count_users();
int		count_dbs();

extern char     *PQhost;     /* machine on which the backend is running */
extern char     *PQport;     /* comm. port with the postgres backend. */

extern char *optarg;
extern int optind,opterr;

char Buf[512];

main(argc,argv)
     int argc;
     char **argv;
{
    DBLIST dbs;
    DBLIST old_dbs;
    int nusers, ndbs;
    int  errflag = 0;
    char c;

    /* ----------------
     *    process command line options
     * ----------------
     */
    while ((c = getopt(argc, argv, "p:h:")) != EOF) {
    switch(c) {
        case 'h':
            PQhost = optarg;
            break;        
        case 'p':
            PQport = optarg;
            break;
        case '?' :
            errflag++;
        }
    }

    if (errflag) {
        fprintf(stderr, "usage: %s [-p port] [-h host] dbname\n", *argv);
        exit (1);
    }

    dbs = build_dblist();
    nusers = count_users();
    ndbs = count_dbs(dbs);

    printf("%d registered postgres users, %d database%s\n",
	   nusers, ndbs, (ndbs == 1 ? "" : "s"));
    printf(" database name     DBA            classes  special  indices  archived  shared\n");

    while (dbs != (DBLIST) NULL) {

	show_stats(dbs);

	pfree(dbs->dbname);
	pfree(dbs->dbowner);
	old_dbs = dbs;
	dbs = dbs->next;
	pfree(old_dbs);
    }
}

/*
 *  count_users() -- print stats on users.
 *
 *	This routine actually just counts the number of users registered
 *	to postgres.
 */

int
count_users()
{
    PortalBuffer *portalbuf;
    char *res;
    int ngroups, ntups, grpno;
    int nusers;

    /* open a connection to the backend */
    PQsetdb("template1");

    /* we know the schema of the system catalogs... */
    sprintf(Buf, "retrieve (pg_user.usename)");
    res = PQexec(&Buf[0]);     

    if (*res == 'E') {
        fprintf(stderr, "%s\npgstats failed", ++res);
        exit(1);
    }

    if (*res != 'P') {
	fprintf(stderr, "pgstats:  no portal?!?\n");
	exit (1);
    }

    /* count result tuples -- get portal first */
    portalbuf = PQparray(++res);
    ngroups = PQngroups(portalbuf);

    /*
     *  If we'd executed multiple queries, or if the target lists of the
     *  returned tuples changed in mid-stream, there would be more than one
     *  group of tuples in this portal.  As it is, that won't happen, but
     *  we go ahead and code this correctly anyway.
     */

    nusers = 0;
    for (grpno = 0; grpno < ngroups; grpno++) {
	nusers += PQntuplesGroup(portalbuf, grpno);
    }

    /* shut down communications */
    PQfinish();

    return (nusers);
}
/*
 *  build_dblist() -- build a list of all databases that exist.
 *
 *	We know that the template database always exists.  We run a query
 *	that returns the names of all databases by using the template db
 *	to get an initial connection to postgres.  We fetch tuples one
 *	at a time, and exploit our knowledge of the system catalog schema
 *	to burst out the results into our list of databases.
 */

DBLIST
build_dblist()
{
    DBLIST dbhead, dblist, ndb;
    PortalBuffer *portalbuf;
    char *res;
    int ngroups, ntups, nflds;
    int grpno, tupno;

    dbhead = dblist = (DBLIST) NULL;

    /* open a connection to the backend */
    PQsetdb("template1");

    /* we know the schema of the system catalogs... */
    sprintf(Buf, "retrieve (p.datname, u.usename, u.oid)\
		  from p in pg_database, u in pg_user \
		  where p.datdba = u.oid");
    res = PQexec(&Buf[0]);     

    if (*res == 'E') {
        fprintf(stderr, "%s\npgstats failed", ++res);
        exit(1);
    }

    if (*res != 'P') {
	fprintf(stderr, "pgstats:  no portal?!?\n");
	exit (1);
    }

    /*
     *  Now get tuples from the result stream and add them to the dblist.
     *  Need a portal for query results.  The backend returns the portal name
     *  associated with our results in the stream, immediately after the 'P'.
     */

    portalbuf = PQparray(++res);
    ngroups = PQngroups(portalbuf);

    /*
     *  If we'd executed multiple queries, or if the target lists of the
     *  returned tuples changed in mid-stream, there would be more than one
     *  group of tuples in this portal.  As it is, that won't happen, but
     *  we go ahead and code this correctly anyway.
     */

    for (grpno = 0; grpno < ngroups; grpno++) {
	ntups = PQntuplesGroup(portalbuf, grpno);
	if ((nflds = PQnfieldsGroup(portalbuf, grpno)) != 3) {
	    fprintf(stderr, "expected 3 attributes, got %d\n", nflds);
	    exit (1);
	}

	/*
	 *  Copy values from the return stream into our db list.  We exploit
	 *  our knowledge of the target list here -- (datname, usename, oid)
	 *  -- from the query above.
	 */

	for (tupno = 0; tupno < ntups; tupno++) {
	    ndb = (DBLIST) palloc(sizeof(*ndb));

	    ndb->dbname = get_attr(portalbuf, tupno, 0);
	    ndb->dbowner = get_attr(portalbuf, tupno, 1);
	    ndb->dbownerId = (ObjectId) atoi(PQgetvalue(portalbuf, tupno, 2));

	    ndb->next = (DBLIST) NULL;

	    if (dbhead == (DBLIST) NULL)
		dbhead = dblist = ndb;
	    else {
		dblist->next = ndb;
		dblist = ndb;
	    }
	}
    }

    /* shut down communications */
    PQfinish();

    return (dbhead);
}

show_stats(db)
    DBLIST db;
{
    PortalBuffer *portalbuf;
    char *res;
    int ngroups, ntups, nflds;
    int grpno, tupno;
    char *relisshared, *relkind, *relarch;
    int nspecial, nindex, nclasses, narch, nshared;
    bool istemplate;

    PQsetdb(db->dbname);

    sprintf(Buf, "retrieve (r.relisshared, r.relkind, r.relarch) \
		  from r in pg_class");

    res = PQexec(&Buf[0]);

    if (*res == 'E') {
        fprintf(stderr, "%s\npgstats failed", ++res);
        exit(1);
    }

    if (*res != 'P') {
	fprintf(stderr, "pgstats:  no portal?!?\n");
	exit (1);
    }

    portalbuf = PQparray(++res);
    ngroups = PQngroups(portalbuf);

    nspecial = nindex = nclasses = narch = nshared = 0;

    for (grpno = 0; grpno < ngroups; grpno++) {
	ntups = PQntuplesGroup(portalbuf, grpno);
	nclasses += ntups;

	if ((nflds = PQnfieldsGroup(portalbuf, grpno)) != 3) {
	    fprintf(stderr, "expected 3 attributes, got %d\n", nflds);
	    exit (1);
	}

	/* once again, we know what our target list looks like... */
	for (tupno = 0; tupno < ntups; tupno++) {

	    relisshared = get_attr(portalbuf, tupno, 0);
	    relkind = get_attr(portalbuf, tupno, 1);
	    relarch = get_attr(portalbuf, tupno, 2);

	    switch (*relkind) {
	      case 'i':
		nindex++;
		break;

	      case 's':
		nspecial++;
		break;

	      default:
		break;
	    }

	    if (*relarch == 't')
		narch++;

	    if (*relisshared == 't')
		nshared++;

	    pfree(relisshared);
	    pfree(relkind);
	    pfree(relarch);
	}
    }

    printf("%s%*s  %s%*s  %4d    %4d     %4d      %4d     %4d\n",
	    db->dbname, 16 - strlen(db->dbname), "",
	    db->dbowner, 16 - strlen(db->dbowner), "",
	    nclasses, nspecial, nindex, narch, nshared);

    /* shut down communications */
    PQfinish();
}

char *
get_attr(portalbuf, tupno, attno)
    PortalBuffer *portalbuf;
    int tupno;
    int attno;
{
    char *attval;
    char *result;

    attval = PQgetvalue(portalbuf, tupno, attno);
    result = (char *) palloc(strlen(attval) + 1);
    strcpy(result, attval);

    return (result);
}

int
count_dbs(dbs)
    DBLIST dbs;
{
    int ndbs = 0;

    while (dbs != (DBLIST) NULL) {
	ndbs++;
	dbs = dbs->next;
    }

    return (ndbs);
}
