/* * SQuirreLMUD 1.0 - Copyright (C) 2003 - Chris Meshkin * * This is the low-level SQL interface code for PostgreSQL 7.x * * File: $Source: /home/cvs/root/squirrel/src/sql.c,v $ * Revision: $Revision: 1.2 $ * Date: $Date: 2003/07/26 06:28:08 $ * Author: $Author: quixadhal $ * CVS_ID: $Id: sql.c,v 1.2 2003/07/26 06:28:08 quixadhal Exp $ * $Log: sql.c,v $ * Revision 1.2 2003/07/26 06:28:08 quixadhal * Progress, now have a working bug system * * Revision 1.1 2003/07/26 03:57:39 quixadhal * Version 0 * */ #include <stdio.h> #include <stdlib.h> #include <stdarg.h> #include <string.h> #include <sys/types.h> #include <unistd.h> /* #include "config.h" */ #include "mud.h" /* #define TESTING */ #define _SQL_C #include "sql.h" #ifdef USE_POSTGRES PGconn *dbc = NULL; #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES sql_connect_data pgcdata = { DBHOST, DBPORT, DBNAME, DBOPTS, DBUSER, DBPASS }; PGconn *sql_open(sql_connect_data *cdata) { char *s = NULL; dbc = PQsetdbLogin(cdata->dbhost, cdata->dbport, cdata->dbopts, NULL, cdata->dbname, cdata->dbuser, cdata->dbpasswd); if(dbc) { s = sql_version(); if(!s || !*s) { sql_close(); dbc = NULL; return NULL; } } return dbc; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES void sql_close(void) { if(dbc) { PQfinish(dbc); } } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES char *sql_error(ExecStatusType estat) { static char last_err[GIANT_BUF]; char *err_str = NULL; char *pgerr = NULL; if(estat) err_str = PQresStatus(estat); if(dbc) pgerr = PQerrorMessage(dbc); bzero(last_err, GIANT_BUF); snprintf(last_err, GIANT_BUF-1, "%s%s%s", err_str?err_str:"", pgerr?" ":"", pgerr?pgerr:""); #ifndef TESTING log_string("SQL: %s", last_err); #else printf("SQL: %s\n", last_err); #endif return last_err; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES int sql_execute(const char *query, ... ) { PGresult *rs = NULL; ExecStatusType rstat; long int rows = 0; char tmp[GIANT_BUF]; va_list param; if(!dbc) return -1; if(PQstatus(dbc) == CONNECTION_BAD) return -1; va_start( param, query ); vsnprintf( tmp, GIANT_BUF, query, param ); va_end( param ); rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); return -1; } PQclear(rs); rs = PQexec(dbc, tmp); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); rs = PQexec(dbc, "ROLLBACK"); PQclear(rs); return -1; } else { rows = atol(PQcmdTuples(rs)); } PQclear(rs); rs = PQexec(dbc, "COMMIT"); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); return -1; } PQclear(rs); return rows; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES sql_query_result * sql_query(const char *query, ... ) { PGresult *rs = NULL; ExecStatusType rstat; char tmp[GIANT_BUF]; char tquery[GIANT_BUF]; va_list param; sql_query_result *result = NULL; register int i; register int j; if(!dbc) return NULL; if(PQstatus(dbc) == CONNECTION_BAD) return NULL; snprintf( tquery, GIANT_BUF, "DECLARE bucket CURSOR FOR %s", query ); va_start( param, query ); vsnprintf( tmp, GIANT_BUF, tquery, param ); va_end( param ); rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); return NULL; } PQclear(rs); rs = PQexec(dbc, tmp); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); rs = PQexec(dbc, "ROLLBACK"); PQclear(rs); return NULL; } PQclear(rs); rs = PQexec(dbc, "FETCH ALL IN bucket"); if ((rstat = PQresultStatus(rs)) != PGRES_TUPLES_OK) { PQclear(rs); sql_error(rstat); rs = PQexec(dbc, "ROLLBACK"); PQclear(rs); return NULL; } result = (sql_query_result *)calloc(1, sizeof(sql_query_result *)); result->cols = PQnfields(rs); result->rows = PQntuples(rs); result->data = (char ***) calloc(result->rows, sizeof(char **)); for(i = 0; i < result->rows; i++) { result->data[i] = (char **) calloc(result->cols, sizeof(char *)); for(j = 0; j < result->cols; j++) { result->data[i][j] = (char *)strdup(PQgetvalue(rs, i, j)); } } PQclear(rs); rs = PQexec(dbc, "CLOSE bucket"); PQclear(rs); rs = PQexec(dbc, "END"); PQclear(rs); return result; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES void sql_free_result(sql_query_result *result) { register int i; register int j; if(result) { if(result->data) { for(i = 0; i < result->rows; i++) { if(result->data[i]) { for(j = 0; j < result->cols; j++) { if(result->data[i][j]) free(result->data[i][j]); } free(result->data[i]); } } free(result->data); } free(result); } return; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES char *sql_last_error(void) { static char last_err[GIANT_BUF]; char *pgerr = NULL; if(!dbc) return "No database handle!"; pgerr = PQerrorMessage(dbc); if(pgerr && *pgerr) { strncpy(last_err, pgerr, GIANT_BUF-1); } else { bzero(last_err, GIANT_BUF); } return last_err; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES const char *sql_escape(const char *str) { static char to[GIANT_BUF*2 +1]; char from[GIANT_BUF]; size_t to_len = 0; bzero(from, GIANT_BUF); strncpy(from, str, GIANT_BUF-1); to_len = PQescapeString(to, from, strlen(from)); return to; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES char *sql_version(void) { static char version[GIANT_BUF]; PGresult *rs = NULL; ExecStatusType rstat; bzero(version, GIANT_BUF); if (dbc) { if (PQstatus(dbc) == CONNECTION_OK) { rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); rs = PQexec(dbc, "DECLARE mycurser CURSOR FOR select version()"); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); rs = PQexec(dbc, "FETCH ALL in mycurser"); if ((rstat = PQresultStatus(rs)) == PGRES_TUPLES_OK) { strncpy(version, PQgetvalue(rs, 0, 0), GIANT_BUF); version[GIANT_BUF-1] = '\0'; } else { version[0] = '\0'; sql_error(rstat); } PQclear(rs); rs = PQexec(dbc, "CLOSE mycurser"); PQclear(rs); rs = PQexec(dbc, "END"); } else { sql_error(rstat); } } else { sql_error(rstat); } PQclear(rs); } } return version; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES char *sql_status(void) { static char sbuf[GIANT_BUF]; bzero(sbuf, GIANT_BUF); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Database Type", "PostgreSQL"); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Host", PQhost(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Port", PQport(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Database", PQdb(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "User", PQuser(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Password", PQpass(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "TTY", PQtty(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Options", PQoptions(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %d\n", "Backend PID", PQbackendPID(dbc)); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %d\n", "PID", getpid()); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Last Error", sql_last_error()); snprintf( sbuf + strlen(sbuf), GIANT_BUF - strlen(sbuf), "%20.20s: %s\n", "Version", sql_version()); return sbuf; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES pid_t sql_backend(void) { return (pid_t) PQbackendPID(dbc); } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES int sql_table_exists(char *table) { PGresult *rs = NULL; ExecStatusType rstat; char sqlbuf[GIANT_BUF]; int result = 0; bzero(sqlbuf, GIANT_BUF); if (dbc && table && *table) { if (PQstatus(dbc) == CONNECTION_OK) { rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); snprintf(sqlbuf, GIANT_BUF, "DECLARE mycurser CURSOR FOR " "SELECT relname FROM pg_class WHERE relname = '%s'", table); rs = PQexec(dbc, sqlbuf); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); rs = PQexec(dbc, "FETCH ALL in mycurser"); if ((rstat = PQresultStatus(rs)) == PGRES_TUPLES_OK) { if(PQntuples(rs) > 0) result = 1; } else { sql_error(rstat); } PQclear(rs); rs = PQexec(dbc, "CLOSE mycurser"); PQclear(rs); rs = PQexec(dbc, "END"); } else { sql_error(rstat); } } else { sql_error(rstat); } PQclear(rs); } } return result; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES int sql_field_exists(char *table, char *field) { PGresult *rs = NULL; ExecStatusType rstat; char sqlbuf[GIANT_BUF]; int result = 0; bzero(sqlbuf, GIANT_BUF); if (dbc && table && *table && field && *field) { if (PQstatus(dbc) == CONNECTION_OK) { rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); snprintf(sqlbuf, GIANT_BUF, "DECLARE mycurser CURSOR FOR " "SELECT * FROM %s LIMIT 1", table); rs = PQexec(dbc, sqlbuf); if ((rstat = PQresultStatus(rs)) == PGRES_COMMAND_OK) { PQclear(rs); rs = PQexec(dbc, "FETCH ALL in mycurser"); if ((rstat = PQresultStatus(rs)) == PGRES_TUPLES_OK) { if(PQfnumber(rs, field) >= 0) result = 1; } else { sql_error(rstat); } PQclear(rs); rs = PQexec(dbc, "CLOSE mycurser"); PQclear(rs); rs = PQexec(dbc, "END"); } else { sql_error(rstat); } } else { sql_error(rstat); } PQclear(rs); } } return result; } #endif /* USE_POSTGRES */ #ifdef USE_POSTGRES int sql_truncate(char *table) { PGresult *rs = NULL; ExecStatusType rstat; char query[GIANT_BUF]; bzero(query, GIANT_BUF); if(!dbc) return 0; if(PQstatus(dbc) == CONNECTION_BAD) return 0; rs = PQexec(dbc, "BEGIN"); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); return 0; } PQclear(rs); snprintf(query, GIANT_BUF, "TRUNCATE %s", table); rs = PQexec(dbc, query); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); rs = PQexec(dbc, "ROLLBACK"); PQclear(rs); return 0; } PQclear(rs); rs = PQexec(dbc, "COMMIT"); if ((rstat = PQresultStatus(rs)) != PGRES_COMMAND_OK) { PQclear(rs); sql_error(rstat); return 0; } PQclear(rs); return 1; } #endif /* USE_POSTGRES */ #ifdef TESTING int main(int argc, char **argv) { sql_query_result *result = NULL; sql_connect_data connect_me = { NULL, NULL, "socketmud", NULL, "quixadhal", NULL }; register int i = 0; register int j = 0; sql_open(&connect_me); printf("%s\n", sql_status()); printf("foo exists: %d\n", sql_table_exists("foo")); printf("log_types exists: %d\n", sql_table_exists("log_types")); printf("foo exists: %d\n", sql_table_exists("foo")); result = sql_query("SELECT * FROM log_types ORDER BY log_type_id"); if(result) { if(result->data) { for(i = 0; i < result->rows; i++) { if(result->data[i]) { printf("row %d:\t", i); for(j = 0; j < result->cols; j++) { if(result->data[i][j]) printf("%s\t", result->data[i][j]); } printf("%s", "\n"); } } } } else { printf("%s\n", sql_last_error()); } sql_free_result(result); result = NULL; sql_close(); return 1; } #endif /* TESTING */