aboutsummaryrefslogtreecommitdiff
path: root/psql/psql.c
diff options
context:
space:
mode:
Diffstat (limited to 'psql/psql.c')
-rw-r--r--psql/psql.c371
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);
+}
+
+
generated by cgit v1.2.1 (git 2.18.0) at 2024-06-02 21:54:43 +0000