diff options
Diffstat (limited to 'psql/psql.c')
-rw-r--r-- | psql/psql.c | 371 |
1 files changed, 371 insertions, 0 deletions
diff --git a/psql/psql.c b/psql/psql.c new file mode 100644 index 0000000..5fac954 --- /dev/null +++ b/psql/psql.c @@ -0,0 +1,371 @@ +/* [psql] - A PostgreSQL client for PD + * + * Copyright (C) 2006 Jamie Bullock and others + * + * Large portions of the code are based on [sqlsingle] by Iain Mott + * + * Copyright (C) 2001 Iain Mott + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2, or (at your option) + * any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License, which should be included with this + * program, for more details. + * + */ + +/* up to 10 fields may be returned. returns floats or symbols */ + +/* code for psql pd class */ + +#include "m_pd.h" +#include <string.h> + +/* PSQL */ + +#include <stdio.h> +#include <stdlib.h> +#include "libpq-fe.h" + +#define MAXSQLFIELDS 10 + +/* postgres datatypes and corresponding 'Oid's */ + +#define PGINT4 23 +#define PGFLOAT8 701 +#define PGDOUBLE 1700 +#define PGDATE 1082 +#define PGDATETIME 1184 +#define PGVARCHAR 1043 + +typedef struct psql +{ + t_object t_ob; + t_outlet *x_outlet1; + t_outlet *x_outlet2; + t_atom get_atom; + t_symbol *x_sym; + char sqlStringStore[MAXPDSTRING]; + char *pghost, + *pgport, + *pgoptions, + *pgtty, + *dbName; + char port[20]; + PGconn *conn; + t_int connected; +} t_psql; + +static void psql_float(t_psql *x, t_floatarg f) +{ + post("psql: %f", f); +} + + +static void psql_SQL (t_psql *x, t_symbol *s) +{ + /* post("psql_SQL called");*/ + char sqlString[MAXPDSTRING]; + int argc = 10; + t_atom argv[argc]; + t_atom *test; + int descriptor_fnum; + int starttime_fnum; + int endtime_fnum; + int spurtorder_fnum; + int nFields; + int i, j; + t_symbol *t_sym; + PGresult *res; + int tupplecount; + test = argv; + + /* make a connection to the database */ + if(!x->connected){ + post("Reconnecting to database, %s", x->dbName); + x->conn = PQsetdb(x->pghost, x->pgport, + x->pgoptions, x->pgtty, x->dbName); + } + + + if(PQstatus(x->conn) == CONNECTION_BAD){ + fprintf(stderr, "Connection to database '%s' failed.\n", x->dbName); + fprintf(stderr, "%s", PQerrorMessage(x->conn)); + } + else{ + x->connected = 1; + + res = PQexec(x->conn, x->sqlStringStore); + if (PQresultStatus(res) != PGRES_TUPLES_OK && PQresultStatus(res) != PGRES_COMMAND_OK) + { + fprintf(stderr, "psql: Action failed. PQresultStatus is %s\n", PQresStatus(PQresultStatus(res))); + } + + else { + tupplecount = PQntuples(res); + if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) + { + PQclear(res); + } + else { + + nFields = PQnfields(res); + + + descriptor_fnum = PQfnumber(res, "descriptor"); + starttime_fnum = PQfnumber(res, "starttime"); + endtime_fnum = PQfnumber(res, "endtime"); + spurtorder_fnum = PQfnumber(res, "spurtorder"); + /* fetch the instances */ + for (i = 0; i < PQntuples(res); i++) + { + /* merge field of a query instance into a list */ + SETFLOAT(&argv[0], i); + for (j=0; j<nFields; j++) + { + int fType = PQftype(res, j); + + if (fType == PGINT4) + SETFLOAT(&argv[j+1], (float) atoi(PQgetvalue(res, i, j))); + else if (fType == PGFLOAT8 || fType == PGDOUBLE) + SETFLOAT(&argv[j+1], (float) atof(PQgetvalue(res, i, j))); + else if (fType == PGVARCHAR || fType == PGDATE || fType == PGDATETIME) + { + t_sym = gensym( PQgetvalue(res, i, j)); + SETSYMBOL(&argv[j+1], t_sym); + } + else { + t_sym = gensym( PQgetvalue(res, i, j)); + SETSYMBOL(&argv[j+1], t_sym); + post("Undefined PG data type. OID: %d. Stored in list as Symbol", fType); + } + + } + t_sym = gensym( "A_FLOAT"); + outlet_list(x->x_outlet1, t_sym, nFields+1, argv); + } + PQclear(res); + } + } + } +} + +static void psql_close(t_psql *x){ + post("Closing connection to database %s", x->dbName); + PQfinish(x->conn); + x->connected = 0; +} + +static void psql_anything(t_psql *x, t_symbol *s, int ac, t_atom *av, t_floatarg f) +{ + char sqlString[MAXPDSTRING]; + int i; + char buf[MAXPDSTRING]; + char mybuf[MAXPDSTRING]; + + /*post("Calling psql_anything");*/ + + if(!strcmp(s->s_name,"close")) + psql_close(x); + else{ + if (strcmp(s->s_name, "sql") != 0) + { + strcat(x->sqlStringStore, ", "); + + /* replace the truncated first symbol */ + + strcat(x->sqlStringStore, s->s_name); + strcat(x->sqlStringStore, " "); + + /* see if it ends OK */ + + atom_string(av+ac-1, buf, MAXPDSTRING); + + if (strcmp(buf, "sqlend") == 0) + { + int tc = ac-1; + + + for (i = 0; i < tc; i++) + { + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + if (i < tc - 1) + strcat(x->sqlStringStore, " "); + } + + /*post("executing query");*/ + psql_SQL (x, s); + } + else + { + for (i = 0; i < ac; i++) + { + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + if (i < ac - 1) + strcat(x->sqlStringStore, " "); + } + + } + + } + else { + /* if s->s_name DOES equal "sql" - first clear sqlStringStore then check if end of string terminates with "sqlend" */ + + strcpy(x->sqlStringStore, ""); + atom_string(av+ac-1, buf, MAXPDSTRING); + if (strcmp(buf, "sqlend") != 0) + { + for (i = 0; i < ac; i++) + { + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + if (i < ac - 1) + strcat(x->sqlStringStore, " "); + } + } + + else + { + ac -= 1; + for (i = 0; i < ac; i++) + { + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + if (i < ac - 1) + strcat(x->sqlStringStore, " "); + } + /*post("executing query");*/ + psql_SQL (x, s); + } + + + + } + + atom_string(av+ac-1, buf, MAXPDSTRING); + } +} + +/* this one is needed if the new line begins with a number */ + +static void psql_list(t_psql *x, t_symbol *s, int ac, t_atom *av) +{ + int i; + char buf[MAXPDSTRING]; + /* post("list"); */ + strcat(x->sqlStringStore, ","); + + if (strcmp(x->sqlStringStore, "") != 0) + { + + atom_string(av+ac-1, buf, MAXPDSTRING); + if (strcmp(buf, "sqlend") == 0) + { + ac = ac -1; + for (i = 0; i < ac; i++) + { + strcat(x->sqlStringStore, " "); + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + + } + } + else + { + for (i = 0; i < ac; i++) + { + strcat(x->sqlStringStore, " "); + atom_string(av+i, buf, MAXPDSTRING); + strcat(x->sqlStringStore, buf); + + } + } + } + else + post("psql: Not SQL"); + +} + + +t_class *psql_class; + +static void *psql_new(t_symbol *s, int argc, t_atom *argv) +{ + t_psql *x = (t_psql *)pd_new(psql_class); + x->x_sym = gensym("psql"); + x->x_outlet1 = outlet_new(&x->t_ob, &s_list); + if(argc == 0) + { + x->pghost = NULL; /* host name of the backend server */ + x->pgport = NULL; /* port of the backend server */ + x->pgoptions = NULL; /* special options to start up the backend server */ + x->pgtty = NULL; /* debugging tty for the backend server */ + x->dbName = "template1"; + post("using dbase template1 on local UNIX socket"); + } + else if(argc == 1 && argv[0].a_type == A_SYMBOL) + { + x->pghost = NULL; /* host name of the backend server */ + x->pgport = NULL; /* port of the backend server */ + x->pgoptions = NULL; /* special options to start up the backend server */ + x->pgtty = NULL; /* debugging tty for the backend server */ + x->dbName = argv[0].a_w.w_symbol->s_name; + /* post("using %s on localhost", argv[0].a_w.w_symbol->s_name); */ + } + else if(argc == 3 && argv[0].a_type == A_SYMBOL && argv[1].a_type == A_SYMBOL + && argv[2].a_type == A_FLOAT) + { + x->pghost = argv[1].a_w.w_symbol->s_name; /* host name of the backend server */ + sprintf(x->port, "%d", (int)argv[2].a_w.w_float); + x->pgport = x->port; /* port of the backend server */ + // strncpy(x->pgport, tmp); /* port of the backend server */ + x->pgoptions = NULL; /* special options to start up the backend server */ + x->pgtty = NULL; /* debugging tty for the backend server */ + x->dbName = argv[0].a_w.w_symbol->s_name; + /* post("using dbase %s on %s and port %s", x->dbName, x->pghost, x->pgport); */ + } + + else + { + x->pghost = NULL; /* host name of the backend server */ + x->pgport = NULL; /* port of the backend server */ + x->pgoptions = NULL; /* special options to start up the backend server */ + x->pgtty = NULL; /* debugging tty for the backend server */ + x->dbName = "template1"; + post("psql: invalid arguments using default template1 dbase on localhost"); + } + + // check postmaster is running on specified port and machine by attempting to x->connect to template1 + x->conn = PQsetdb(x->pghost, x->pgport, x->pgoptions, x->pgtty, "template1"); + if (PQstatus(x->conn) == CONNECTION_BAD) + { + fprintf(stderr, "psql: Connection to template1 failed. Perhaps the postmaster is not running on the specified port and machine \n"); + fprintf(stderr, "psql: Connect error is: %s", PQerrorMessage(x->conn)); + } + else + psql_close(x); + return (x); +} + + static void psql_free(t_psql *x){ + if(x->connected) + psql_close(x); + } + +void psql_setup(void) +{ + /* post("psql_setup"); */ + psql_class = class_new(gensym("psql"), (t_newmethod)psql_new, (t_method)psql_free, + sizeof(t_psql), 0, A_GIMME, 0); + class_addanything(psql_class, psql_anything); + class_addlist(psql_class, psql_list); +} + + |