#include "global.h"
#ifdef SQL
#include "commands.h"
#include "socket.h"
#include "sql.h"


#if !defined(_lint)
static char rcsid[] OPTIONAL = "$Id: sql.c,v 1.11 1996/12/29 02:47:22 root Exp $";
#endif

static char *SQL_HOST;
static char *SQL_DATABASE;
static void separatorline (m_result *result);
static int SQL_LIMIT = 1;

#define SQLRETRIES 20


static int dosqlquery (int argc,char *argv[],void *p);
static int dosqluserquery (int argc,char *argv[],void *p);
static int dosqlhost (int argc,char *argv[],void *p);
static int dosqllimit (int argc,char *argv[],void *p);
static int dosqldatabase (int argc,char *argv[],void *p);

/* sql subcommand table */
static struct cmds SOUNDtab[] = {
	{ "database",		dosqldatabase,		0, 0, NULLCHAR },
	{ "host",		dosqlhost,		0, 0, NULLCHAR },
	{ "limitusers",		dosqllimit,		0, 0, NULLCHAR },
	{ "query",		dosqlquery,		0, 2, "sql query \"query string\"" },
	{ "userquery",		dosqluserquery,		0, 2, "sql userquery \"select query string\"" },
	{ NULLCHAR,		NULL,			0, 0, NULLCHAR }
};


int
dosql(argc,argv,p)
int argc;
char *argv[];
void *p;
{
	return subcmd(SOUNDtab,argc,argv,p);
}


static int
dosqldatabase(argc,argv,p)
int argc;
char *argv[];
void *p OPTIONAL;
{
	if(argc < 2)	{
		if (SQL_DATABASE)
			tprintf ("%s\n", SQL_DATABASE);
	} else {
		if (SQL_DATABASE)
			free (SQL_DATABASE);
		SQL_DATABASE = strdup (argv[1]);
	}
	return 0;
}


static int
dosqllimit(argc,argv,p)
int argc;
char *argv[];
void *p OPTIONAL;
{
	return setbool(&SQL_LIMIT,"Limit non-SYSOPs to only 'select' queries",argc,argv);
}


static int
dosqlhost(argc,argv,p)
int argc;
char *argv[];
void *p OPTIONAL;
{
	if(argc < 2)	{
		if (SQL_HOST)
			tprintf ("%s\n", SQL_HOST);
	} else {
		if (SQL_HOST)
			free (SQL_HOST);
		SQL_HOST = strdup (argv[1]);
	}
	return 0;
}


static int
dosqlquery(argc,argv,p)
int argc OPTIONAL;
char *argv[];
void *p OPTIONAL;
{
	(void) sql_query (argv[1]);
	return 0;
}



static int
dosqluserquery(argc,argv,p)
int argc OPTIONAL;
char *argv[];
void *p OPTIONAL;
{
	if (SQL_LIMIT && strnicmp (argv[1], "select ", 7))
		tprintf ("Sorry, only SQL 'select' commands are availble to non-SYSOPs\n");
	else
		(void) sql_query (argv[1]);
	return 0;
}



void
sql_init ()
{
	SQL_DATABASE = strdup ("tnos");
}


/* This opens the sql server, and sets the database to dbname */
int
sql_open (dbname)
char *dbname;
{
int sock;

	if ((sock = msqlConnect (SQL_HOST)) != -1)
		if (msqlSelectDB (sock, dbname) != -1)
			return sock;
	return -1;
}


void
sql_error ()
{
	tprintf ("SQL ERROR: %s\n", msqlErrMsg);
}


void
sql_close (sock)
int sock;
{
	msqlClose (sock);
}


void
sql_endquery (result)
m_result *result;
{
	msqlFreeResult(result);
}


m_result *
sql_run (sock, query)
int sock;
char *query;
{
m_result *result;
int count;
int retries;

	for (retries = 0; retries < SQLRETRIES; retries++) {
		if (msqlQuery (sock, query) != -1)
			break;
		(void) kpause (500);
	}
	if (retries == SQLRETRIES)	{
		sql_error ();
		return NULLSQLRESULT;
	}
	tprintf ("\nQuery OK.\n\n");
	result = msqlStoreResult();
	if (result)	{
		count = msqlNumRows(result);
		tprintf("%d row%s matched.\n\n",count, (count != 1) ? "s" : "");
	}
	return (result);
}


static void
separatorline (result)
m_result *result;
{
m_field	*curField;
int length = 0;
int k;

	/* Print a pretty header .... */
	tputs (" +");

	/* first calculate size */
	while((curField = msqlFetchField(result)) != ((m_field *)0))	{
		switch(curField->type)	{
		    	case REAL_TYPE:
				length = (int) strlen(curField->name);
				if (length < 12)
					length = 12;
				break;

		    	case INT_TYPE:
				length = (int) strlen(curField->name);
				if (length < 8)
					length = 8;
				break;

		    	case CHAR_TYPE:
				length = (int)strlen(curField->name);
				length = max(length, curField->length);
				break;

			default:
				break;
		}
		for (k = 0; k < length; k++)
			tputc ('-');
		tputs ("-+");
	}
	tputs ("\n");
	msqlFieldSeek(result,0);
}

void
sql_printheader (result)
m_result *result;
{
m_field	*curField;
int length = 0;

	separatorline (result);
	tprintf(" |");
	while((curField = msqlFetchField(result)) != ((m_field *)0))	{
		switch(curField->type)	{
		    	case INT_TYPE:
				length = (int) strlen(curField->name);
				if (length < 8)
					length = 8;
				break;

		    	case REAL_TYPE:
				length = (int) strlen(curField->name);
				if (length < 12)
					length = 12;
				break;

		    	case CHAR_TYPE:
				length = (int)strlen(curField->name);
				length = max(length, curField->length);
				break;

			default:
				break;
		}
		tprintf(" %-*.*s|", length, length, curField->name);
	}
	tputs("\n");
	msqlFieldSeek(result,0);
	separatorline (result);
}


void
sql_printresults (result)
m_result *result;
{
m_row cur;
m_field *curField;
int off, length = 0;

	/* Print the returned data */
	while ((cur = msqlFetchRow(result)) != ((m_row)0))	{
		off = 0;
		tprintf(" |");
		while(off < msqlNumFields(result))	{
			curField = msqlFetchField(result);
			switch(curField->type)		{
			    case INT_TYPE:
				length = (int) strlen(curField->name);
				if (length < 8)
					length = 8;
				break;

			    case REAL_TYPE:
				length = (int) strlen(curField->name);
				if (length < 12)
					length = 12;
				break;

			    case CHAR_TYPE:
				length = (int)strlen(curField->name);
				length = max(length, curField->length);
				break;

			    default:
			    	break;
			}
			if (cur[off])
				tprintf(" %-*.*s|", length, length, cur[off]);
			else
				tprintf(" %-*.*s|", length, length, "NULL");
			off++;
		}
		tprintf("\n");
		msqlFieldSeek(result,0);
	}
	separatorline (result);
	tputs ("\n");
}


int
sql_query (query)
char *query;
{
int sock;
m_result *result;
int retries;

	for (retries = 0; retries < SQLRETRIES; retries++)	{
		if ((sock = sql_open (SQL_DATABASE)) != -1)
			break;
		(void) kpause (500);
	}
	if (retries == SQLRETRIES)	{
		sql_error ();
		return -1;
	}
	result = sql_run (sock, query);
	if (result != NULLSQLRESULT)	{
		sql_printheader (result);
		sql_printresults (result);
		sql_endquery (result);
	}
	sql_close (sock);
	return 0;
}



#endif /* SQL */

