From 715c6b6d2543bed88d636a7b89277e55a85f437c Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Thu, 19 Sep 1996 20:19:05 +0000 Subject: [PATCH] Newer version of Bruce's pginterface library... --- contrib/pginterface/Makefile | 24 ++++ contrib/pginterface/README | 42 ++++++ contrib/pginterface/halt.c | 58 ++++++++ contrib/pginterface/halt.h | 7 + contrib/pginterface/pginsert.c | 98 ++++++++++++++ contrib/pginterface/pginterface.c | 212 ++++++++++++++++++++++++++++++ contrib/pginterface/pginterface.h | 14 ++ contrib/pginterface/pgnulltest.c | 140 ++++++++++++++++++++ contrib/pginterface/pgwordcount.c | 72 ++++++++++ 9 files changed, 667 insertions(+) create mode 100644 contrib/pginterface/Makefile create mode 100644 contrib/pginterface/README create mode 100644 contrib/pginterface/halt.c create mode 100644 contrib/pginterface/halt.h create mode 100644 contrib/pginterface/pginsert.c create mode 100644 contrib/pginterface/pginterface.c create mode 100644 contrib/pginterface/pginterface.h create mode 100644 contrib/pginterface/pgnulltest.c create mode 100644 contrib/pginterface/pgwordcount.c diff --git a/contrib/pginterface/Makefile b/contrib/pginterface/Makefile new file mode 100644 index 0000000000..659aac30f0 --- /dev/null +++ b/contrib/pginterface/Makefile @@ -0,0 +1,24 @@ +# +# Makefile +# +# +PGINTERFACE = pginterface.o halt.o +TARGET = pginsert pgwordcount pgnulltest +CFLAGS = -g -Wall -I/u/postgres95/include +LDFLAGS = -L/u/postgres95/lib -lpq + +all : $(TARGET) + +$(TARGET): $(PGINTERFACE) $*.c + cc -o $* $(CFLAGS) $*.c $(PGINTERFACE) $(LDFLAGS) + +$(PGINTERFACE): pginterface.c halt.c + cc -c $(CFLAGS) pginterface.c halt.c + +clean: + rm -f *.o $(TARGET) log core + +install: + make clean + make CFLAGS=-O + install -s -o bin -g bin $(TARGET) /usr/local/bin diff --git a/contrib/pginterface/README b/contrib/pginterface/README new file mode 100644 index 0000000000..7a82b36305 --- /dev/null +++ b/contrib/pginterface/README @@ -0,0 +1,42 @@ + + + Pginterface 1.0 + +Attached is a copy of the Postgres support routines I wrote to allow me +to more cleanly interface to the libpg library, more like a 4gl SQL +interface. + +It has several features that may be useful for others: + +I have simplified the C code that calls libpq by wrapping all the +functionality of libpq in calls to connectdb(), doquery(), fetch(), +fetchisnull() and disconnectdb(). Each call returns a structure or +value, so if you need to do more work with the result, you can. Also, I +have a global variable that allows you to disable the error checking I +have added to the doquery() routine. + +I have added a function called fetch(), which allows you to pass +pointers as parameters, and on return the variables are filled with the +data from the binary cursor you opened. These binary cursors are not +useful if you are running the query engine on a system with a different +architecture than the database server. If you pass a NULL pointer, the +column is skipped, and you can use libpq to handle it as you wish. + +I have used sigprocmask() to block the reception of certain signals +while the program is executing SQL queries. This prevents a user +pressing Control-C from stopping all the back ends. It blocks SIGHUP, +SIGINT, and SIGTERM, but does not block SIGQUIT or obviously kill -9. +If your platform does not support sigprocmask(), you can remove those +function calls. ( Am I correct that abnormal termination can cause +shared memory resynchronization?) + +There is a demo program called pginsert that demonstrates how the +library can be used. + +You can create a library of pginterface.c and halt.c, and just include +pginterface.h in your source code. + +I am willing to maintain this if people find problems or want additional +functionality. + +Bruce Momjian (root@candle.pha.pa.us) diff --git a/contrib/pginterface/halt.c b/contrib/pginterface/halt.c new file mode 100644 index 0000000000..58ca11a587 --- /dev/null +++ b/contrib/pginterface/halt.c @@ -0,0 +1,58 @@ +/* +** +** halt.c +** +** This is used to print out error messages and exit +*/ + +#include +#include +#include +#include +#include +#include + + +/*------------------------------------------------------------------------- +** +** halt - print error message, and call clean up routine or exit +** +**------------------------------------------------------------------------*/ + +/*VARARGS*/ +void halt(va_alist) +va_dcl +{ + va_list arg_ptr; + char *format, *pstr; + void (*sig_func)(); + + va_start(arg_ptr); + format = va_arg(arg_ptr,char *); + if (strncmp(format,"PERROR", 6) != 0) + vfprintf(stderr,format,arg_ptr); + else + { + for (pstr=format+6; *pstr == ' ' || *pstr == ':'; pstr++) + ; + vfprintf(stderr,pstr,arg_ptr); + perror(""); + } + va_end(arg_ptr); + fflush(stderr); + + /* call one clean up function if defined */ + if ( (sig_func = signal(SIGTERM, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGHUP, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGINT, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGQUIT, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + exit(1); +} diff --git a/contrib/pginterface/halt.h b/contrib/pginterface/halt.h new file mode 100644 index 0000000000..cb4ea545b9 --- /dev/null +++ b/contrib/pginterface/halt.h @@ -0,0 +1,7 @@ +/* +** halt.h +** +*/ + +void halt(); + diff --git a/contrib/pginterface/pginsert.c b/contrib/pginterface/pginsert.c new file mode 100644 index 0000000000..92c869b383 --- /dev/null +++ b/contrib/pginterface/pginsert.c @@ -0,0 +1,98 @@ +/* + * insert.c + * +*/ + +#include +#include +#include +#include +#include "halt.h" +#include "pginterface.h" + +int main(int argc, char **argv) +{ + char query[4000]; + int row =1; + int aint; + float afloat; + double adouble; + char achar[11], achar16[17], abpchar[11], avarchar[51], atext[51]; + time_t aabstime; + + if (argc != 2) + halt("Usage: %s database\n",argv[0]); + + connectdb(argv[1],NULL,NULL,NULL,NULL); + + on_error_continue(); + doquery("DROP TABLE testfetch"); + on_error_stop(); + + doquery("\ + CREATE TABLE testfetch( \ + aint int4, \ + afloat float4, \ + adouble float8, \ + achar char, \ + achar16 char16, \ + abpchar char(10), \ + avarchar varchar(50), \ + atext text, \ + aabstime abstime) \ + "); + + while(1) + { + sprintf(query,"INSERT INTO testfetch VALUES ( \ + %d, \ + 2322.12, \ + '923121.0323'::float8, \ + 'A', \ + 'Betty', \ + 'Charley', \ + 'Doug', \ + 'Ernie', \ + 'now' )", row); + doquery(query); + + doquery("BEGIN WORK"); + doquery("DECLARE c_testfetch BINARY CURSOR FOR \ + SELECT * FROM testfetch"); + + doquery("FETCH ALL IN c_testfetch"); + + while (fetch( + &aint, + &afloat, + &adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + &aabstime) != END_OF_TUPLES) + printf("int %d\nfloat %f\ndouble %f\nchar %s\nchar16 %s\n\ +bpchar %s\nvarchar %s\ntext %s\nabstime %s", + aint, + afloat, + adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + ctime(&aabstime)); + + + doquery("CLOSE c_testfetch"); + doquery("COMMIT WORK"); + printf("--- %-d rows inserted so far\n",row); + + row++; + } + + disconnectdb(); + return 0; +} + diff --git a/contrib/pginterface/pginterface.c b/contrib/pginterface/pginterface.c new file mode 100644 index 0000000000..58f7cfa7eb --- /dev/null +++ b/contrib/pginterface/pginterface.c @@ -0,0 +1,212 @@ +/* + * pginterface.c + * +*/ + +#include +#include +#include +#include + +#include +#include "halt.h" +#include "pginterface.h" + +static void sig_disconnect(); +static void set_signals(); + +#define NUL '\0' + +/* GLOBAL VARIABLES */ +static PGconn* conn; +static PGresult* res = NULL; + +#define ON_ERROR_STOP 0 +#define ON_ERROR_CONTINUE 1 + +static int on_error_state = ON_ERROR_STOP; + +/* LOCAL VARIABLES */ +static sigset_t block_sigs, unblock_sigs; +static int tuple; + +/* +** +** connectdb - returns PGconn structure +** +*/ +PGconn *connectdb( char *dbName, + char *pghost, + char *pgport, + char *pgoptions, + char *pgtty) +{ + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); + if (PQstatus(conn) == CONNECTION_BAD) + halt("Connection to database '%s' failed.\n%s\n", dbName, + PQerrorMessage(conn)); + set_signals(); + return conn; +} + +/* +** +** disconnectdb +** +*/ +void disconnectdb() +{ + PQfinish(conn); +} + +/* +** +** doquery - returns PGresult structure +** +*/ +PGresult *doquery(char *query) +{ + if (res != NULL) + PQclear(res); + + sigprocmask(SIG_SETMASK,&block_sigs,NULL); + res = PQexec(conn, query); + sigprocmask(SIG_SETMASK,&unblock_sigs,NULL); + + if (on_error_state == ON_ERROR_STOP && + (res == NULL || + PQresultStatus(res) == PGRES_BAD_RESPONSE || + PQresultStatus(res) == PGRES_NONFATAL_ERROR || + PQresultStatus(res) == PGRES_FATAL_ERROR)) + { + if (res != NULL) + fprintf(stderr,"query error: %s\n",PQcmdStatus(res)); + else fprintf(stderr,"connection error: %s\n",PQerrorMessage(conn)); + PQfinish(conn); + halt("failed request: %s\n", query); + } + tuple = 0; + return res; +} + +/* +** +** fetch - returns tuple number (starts at 0), or the value END_OF_TUPLES +** NULL pointers are skipped +** +*/ +int fetch(void *param, ...) +{ + va_list ap; + int arg, num_args; + + num_args = PQnfields(res); + + if (tuple >= PQntuples(res)) + return END_OF_TUPLES; + + va_start(ap, param); + for (arg = 0; arg < num_args; arg++) + { + if (param != NULL) + { + if (PQfsize(res, arg) == -1) + { + memcpy(param,PQgetvalue(res,tuple,arg),PQgetlength(res,tuple,arg)); + ((char *)param)[PQgetlength(res,tuple,arg)] = NUL; + } + else + memcpy(param,PQgetvalue(res,tuple,arg),PQfsize(res,arg)); + } + param = va_arg(ap, char *); + } + va_end(ap); + return tuple++; +} + +/* +** +** fetchisnull - returns tuple number (starts at 0), or the value END_OF_TUPLES +** NULL pointers are skipped +** Returns true or false into null indicator variables +*/ +int fetchisnull(void *param, ...) +{ + va_list ap; + int arg, num_args; + + if (tuple == 0) + halt("pginterface:fetchisnull(): You must call fetch() first.\n"); + + num_args = PQnfields(res); + + if (tuple-1 >= PQntuples(res)) + return END_OF_TUPLES; + va_start(ap, param); + for (arg = 0; arg < num_args; arg++) + { + if (param != NULL) + { + if (PQgetisnull(res,tuple-1,arg) != 0) + *(int *)param = 1; + else + *(int *)param = 0; + } + param = va_arg(ap, char *); + } + va_end(ap); + return tuple-1; +} + +/* +** +** on_error_stop +** +*/ +void on_error_stop() +{ + on_error_state = ON_ERROR_STOP; +} + +/* +** +** on_error_continue +** +*/ +void on_error_continue() +{ + on_error_state = ON_ERROR_CONTINUE; +} + +/* +** +** sig_disconnect +** +*/ +static void sig_disconnect() +{ + fprintf(stderr,"exiting...\n"); + PQfinish(conn); + exit(1); +} + +/* +** +** set_signals +** +*/ +static void set_signals() +{ + sigemptyset(&block_sigs); + sigemptyset(&unblock_sigs); + sigaddset(&block_sigs,SIGTERM); + sigaddset(&block_sigs,SIGHUP); + sigaddset(&block_sigs,SIGINT); +/* sigaddset(&block_sigs,SIGQUIT); no block */ + sigprocmask(SIG_SETMASK,&unblock_sigs,NULL); + signal(SIGTERM,sig_disconnect); + signal(SIGHUP,sig_disconnect); + signal(SIGINT,sig_disconnect); + signal(SIGQUIT,sig_disconnect); +} diff --git a/contrib/pginterface/pginterface.h b/contrib/pginterface/pginterface.h new file mode 100644 index 0000000000..d73739f485 --- /dev/null +++ b/contrib/pginterface/pginterface.h @@ -0,0 +1,14 @@ +/* + * pglib.h + * +*/ + +PGresult *doquery(char *query); +PGconn *connectdb(); +void disconnectdb(); +int fetch(void *param, ...); +int fetchisnull(void *param, ...); +void on_error_continue(); +void on_error_stop(); + +#define END_OF_TUPLES (-1) diff --git a/contrib/pginterface/pgnulltest.c b/contrib/pginterface/pgnulltest.c new file mode 100644 index 0000000000..85e8a0a6d6 --- /dev/null +++ b/contrib/pginterface/pgnulltest.c @@ -0,0 +1,140 @@ +/* + * insert.c + * +*/ + +/*#define TEST_NON_NULLS*/ + +#include +#include +#include +#include +#include "halt.h" +#include "pginterface.h" + +int main(int argc, char **argv) +{ + char query[4000]; + int row =1; + int aint; + float afloat; + double adouble; + char achar[11], achar16[17], abpchar[11], avarchar[51], atext[51]; + time_t aabstime; + int aint_null, + afloat_null, + adouble_null, + achar_null, + achar16_null, + abpchar_null, + avarchar_null, + atext_null, + aabstime_null; + + if (argc != 2) + halt("Usage: %s database\n",argv[0]); + + connectdb(argv[1],NULL,NULL,NULL,NULL); + + on_error_continue(); + doquery("DROP TABLE testfetch"); + on_error_stop(); + + doquery("\ + CREATE TABLE testfetch( \ + aint int4, \ + afloat float4, \ + adouble float8, \ + achar char, \ + achar16 char16, \ + abpchar char(10), \ + avarchar varchar(50), \ + atext text, \ + aabstime abstime) \ + "); + +#ifdef TEST_NON_NULLS + sprintf(query,"INSERT INTO testfetch VALUES ( \ + 0, \ + 0, \ + 0, \ + '', \ + '', \ + '', \ + '', \ + '', \ + '');"); +#else + sprintf(query,"INSERT INTO testfetch VALUES ( \ + NULL, \ + NULL, \ + NULL, \ + NULL, \ + NULL, \ + NULL, \ + NULL, \ + NULL, \ + NULL);"); +#endif + doquery(query); + + doquery("BEGIN WORK"); + doquery("DECLARE c_testfetch BINARY CURSOR FOR \ + SELECT * FROM testfetch"); + + doquery("FETCH ALL IN c_testfetch"); + + if (fetch( + &aint, + &afloat, + &adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + &aabstime) != END_OF_TUPLES) + printf("int %d\nfloat %f\ndouble %f\nchar %s\nchar16 %s\n\ +bpchar %s\nvarchar %s\ntext %s\nabstime %s\n", + aint, + afloat, + adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + ctime(&aabstime)); + if (fetchisnull( + &aint_null, + &afloat_null, + &adouble_null, + &achar_null, + &achar16_null, + &abpchar_null, + &avarchar_null, + &atext_null, + &aabstime_null) != END_OF_TUPLES) + printf("NULL:\nint %d\nfloat %d\ndouble %d\nchar %d\nchar16 %d\n\ +bpchar %d\nvarchar %d\ntext %d\nabstime %d\n", + aint_null, + afloat_null, + adouble_null, + achar_null, + achar16_null, + abpchar_null, + avarchar_null, + atext_null, + aabstime_null); + + + doquery("CLOSE c_testfetch"); + doquery("COMMIT WORK"); + printf("--- 1 row inserted\n"); + + row++; + + disconnectdb(); + return 0; +} + diff --git a/contrib/pginterface/pgwordcount.c b/contrib/pginterface/pgwordcount.c new file mode 100644 index 0000000000..64096f23e6 --- /dev/null +++ b/contrib/pginterface/pgwordcount.c @@ -0,0 +1,72 @@ +/* + * wordcount.c + * +*/ + +#include +#include +#include +#include +#include "halt.h" +#include "pginterface.h" + +int main(int argc, char **argv) +{ + char query[4000]; + int row = 0; + int count; + char line[4000]; + + if (argc != 2) + halt("Usage: %s database\n",argv[0]); + + connectdb(argv[1],NULL,NULL,NULL,NULL); + on_error_continue(); + doquery("DROP TABLE words"); + on_error_stop(); + + doquery("\ + CREATE TABLE words( \ + matches int4, \ + word text ) \ + "); + doquery("\ + CREATE INDEX i_words_1 ON words USING btree ( \ + word text_ops )\ + "); + + while(1) + { + if (scanf("%s",line) != 1) + break; + doquery("BEGIN WORK"); + sprintf(query,"\ + DECLARE c_words BINARY CURSOR FOR \ + SELECT count(*) \ + FROM words \ + WHERE word = '%s'", line); + doquery(query); + doquery("FETCH ALL IN c_words"); + + while (fetch(&count) == END_OF_TUPLES) + count = 0; + doquery("CLOSE c_words"); + doquery("COMMIT WORK"); + + if (count == 0) + sprintf(query,"\ + INSERT INTO words \ + VALUES (1, '%s')", line); + else + sprintf(query,"\ + UPDATE words \ + SET matches = matches + 1 + WHERE word = '%s'", line); + doquery(query); + row++; + } + + disconnectdb(); + return 0; +} + -- 2.40.0