[PD-cvs] externals/postlude/psql help-psql.pd, 1.1, 1.2 psql.c, 1.1, 1.2
Jamie Bullock
postlude at users.sourceforge.net
Wed Nov 14 23:35:38 CET 2007
Update of /cvsroot/pure-data/externals/postlude/psql
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv26093
Modified Files:
help-psql.pd psql.c
Log Message:
Fixed bug where if a query contained ", close" the 'close' method would be invoked.
Added bang to left outlet when results from each query have been returned
Tidied up help file (a little).
Index: psql.c
===================================================================
RCS file: /cvsroot/pure-data/externals/postlude/psql/psql.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** psql.c 6 Nov 2007 09:59:03 -0000 1.1
--- psql.c 14 Nov 2007 22:35:35 -0000 1.2
***************
*** 33,37 ****
#include "libpq-fe.h"
! #define MAXSQLFIELDS 10
/* postgres datatypes and corresponding 'Oid's */
--- 33,37 ----
#include "libpq-fe.h"
! #define MAXSQLFIELDS 20
/* postgres datatypes and corresponding 'Oid's */
***************
*** 44,52 ****
#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;
--- 44,52 ----
#define PGVARCHAR 1043
! typedef struct psql{
!
t_object t_ob;
! t_outlet *x_outlet1,
! *x_outlet2;
t_atom get_atom;
t_symbol *x_sym;
***************
*** 59,74 ****
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;
--- 59,69 ----
char port[20];
PGconn *conn;
! t_int connected,
! in_query;
! } t_psql;
+ static void psql_SQL (t_psql *x, t_symbol *s){
char sqlString[MAXPDSTRING];
int argc = 10;
***************
*** 79,84 ****
int endtime_fnum;
int spurtorder_fnum;
! int nFields;
! int i, j;
t_symbol *t_sym;
PGresult *res;
--- 74,79 ----
int endtime_fnum;
int spurtorder_fnum;
! int nFields;
! int i, j;
t_symbol *t_sym;
PGresult *res;
***************
*** 102,108 ****
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)));
}
--- 97,105 ----
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)));
}
***************
*** 122,128 ****
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);
--- 119,125 ----
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);
***************
*** 143,147 ****
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);
}
--- 140,145 ----
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);
}
***************
*** 150,153 ****
--- 148,152 ----
outlet_list(x->x_outlet1, t_sym, nFields+1, argv);
}
+ outlet_bang(x->x_outlet2);
PQclear(res);
}
***************
*** 162,167 ****
}
! static void psql_anything(t_psql *x, t_symbol *s, int ac, t_atom *av, t_floatarg f)
! {
char sqlString[MAXPDSTRING];
int i;
--- 161,165 ----
}
! static void psql_anything(t_psql *x, t_symbol *s, int ac, t_atom *av, t_floatarg f){
char sqlString[MAXPDSTRING];
int i;
***************
*** 169,179 ****
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, ", ");
--- 167,176 ----
char mybuf[MAXPDSTRING];
! if(!strcmp(s->s_name,"close") && !x->in_query)
psql_close(x);
else{
! if (strcmp(s->s_name, "sql")){
!
!
strcat(x->sqlStringStore, ", ");
***************
*** 183,208 ****
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++)
{
--- 180,206 ----
strcat(x->sqlStringStore, " ");
! /* see if it ends OK */
atom_string(av+ac-1, buf, MAXPDSTRING);
! if (!strcmp(buf, "sqlend")){
!
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, " ");
+
}
psql_SQL (x, s);
+
+ x->in_query = 0;
+
}
! else {
for (i = 0; i < ac; i++)
{
***************
*** 212,225 ****
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++)
--- 210,225 ----
strcat(x->sqlStringStore, " ");
}
}
}
else {
! /* if s->s_name DOES equal "sql" - first clear sqlStringStore then check
! * if end of string terminates with "sqlend" */
!
! x->in_query = 1;
strcpy(x->sqlStringStore, "");
atom_string(av+ac-1, buf, MAXPDSTRING);
! if (strcmp(buf, "sqlend"))
{
for (i = 0; i < ac; i++)
***************
*** 242,251 ****
strcat(x->sqlStringStore, " ");
}
- /*post("executing query");*/
psql_SQL (x, s);
- }
-
-
}
--- 242,249 ----
strcat(x->sqlStringStore, " ");
}
psql_SQL (x, s);
+ x->in_query = 0;
+ }
}
***************
*** 260,275 ****
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);
--- 258,272 ----
int i;
char buf[MAXPDSTRING];
!
strcat(x->sqlStringStore, ",");
! if (strcmp(x->sqlStringStore, "")){
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);
***************
*** 277,285 ****
}
}
! else
! {
! for (i = 0; i < ac; i++)
! {
strcat(x->sqlStringStore, " ");
atom_string(av+i, buf, MAXPDSTRING);
--- 274,284 ----
}
+
+ x->in_query = 0;
+
}
! else{
!
! for (i = 0; i < ac; i++){
strcat(x->sqlStringStore, " ");
atom_string(av+i, buf, MAXPDSTRING);
***************
*** 294,298 ****
}
-
t_class *psql_class;
--- 293,296 ----
***************
*** 302,311 ****
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");
--- 300,313 ----
x->x_sym = gensym("psql");
x->x_outlet1 = outlet_new(&x->t_ob, &s_list);
+ x->x_outlet2 = outlet_new(&x->t_ob, &s_bang);
+
+ x->in_query = 0;
+
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");
***************
*** 313,351 ****
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));
}
--- 315,353 ----
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;
}
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;
}
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));
}
***************
*** 355,366 ****
}
! 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);
--- 357,366 ----
}
! static void psql_free(t_psql *x){
! if(x->connected)
! psql_close(x);
! }
! void psql_setup(void) {
psql_class = class_new(gensym("psql"), (t_newmethod)psql_new, (t_method)psql_free,
sizeof(t_psql), 0, A_GIMME, 0);
***************
*** 368,371 ****
class_addlist(psql_class, psql_list);
}
-
-
--- 368,369 ----
Index: help-psql.pd
===================================================================
RCS file: /cvsroot/pure-data/externals/postlude/psql/help-psql.pd,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** help-psql.pd 6 Nov 2007 09:59:03 -0000 1.1
--- help-psql.pd 14 Nov 2007 22:35:35 -0000 1.2
***************
*** 1,50 ****
! #N canvas 36 70 917 494 10;
! #X obj 377 470 print postgres;
! #X msg 36 186 sql DROP DATABASE pddbase sqlend;
! #X msg 79 87 sql CREATE DATABASE pddbase sqlend;
! #X text 69 44 1 Create a new database - by first;
! #X text 86 61 connecting to PostgreSQL's default database: 'template1'
! ;
! #X text 479 56 to the newly created 'pddbase' database;
! #X text 461 40 2 Create a new table in the database - this time connecting
;
! #X text 467 114 3 Insert some data. Each click of the message below
;
! #X text 484 132 inserts a new row into the specified table.;
! #X text 471 228 4 Retrieve all data in the specified table using a
wild;
! #X text 32 208 click this to destroy the database;
! #X msg 456 79 sql CREATE TABLE datatable(id INTEGER \, duration FLOAT
\, type VARCHAR \, datetime DATETIME) sqlend;
! #X text 487 242 card '*'. Note each row returned is preceded by an
index;
! #X text 484 300 Also show the degree of error;
! #X msg 466 318 sql SELECT id \, ABS((duration - 1500)/1500) AS error
FROM datatable ORDER BY error LIMIT 1 sqlend;
! #X text 470 288 5 Find the 'id' of entry closest to 1500 msec in duration.
;
! #X msg 466 150 sql INSERT INTO datatable VALUES(22 \, 1023.33 \, 'singing'
\, datetime('now'::date \, 'now'::time)) sqlend \, sql INSERT INTO
datatable VALUES(24 \, 1500.1 \, 'coughing' \, datetime('now'::date
\, 'now'::time)) sqlend \, sql INSERT INTO datatable VALUES(26 \, 2222.11
\, 'sobbing' \, datetime('now'::date \, 'now'::time)) sqlend;
! #X text 56 350 example of sqlsingle specifying;
! #X text 57 366 host machine and port;
! #X text 9 9 sqlsingle. Follow the numbered steps;
! #X obj 500 393 route 0 1 2 3 4 5 6 7 8;
! #X text 649 392 use route to separate indexed 'tuples';
! #X obj 500 428 unpack 0 0 s s;
! #X text 596 428 use unpack to access individual fields;
! #X msg 470 262 sql select * from datatable WHERE id < 30 sqlend;
! #X obj 96 137 psql template1;
! #X obj 57 330 psql pddbase localhost 5432;
! #X obj 377 365 psql pddbase;
! #X connect 1 0 25 0;
! #X connect 2 0 25 0;
! #X connect 11 0 27 0;
! #X connect 14 0 27 0;
! #X connect 16 0 27 0;
! #X connect 20 0 22 0;
! #X connect 24 0 27 0;
! #X connect 27 0 20 0;
! #X connect 27 0 0 0;
--- 1,53 ----
! #N canvas 241 93 917 646 10;
! #X obj 377 547 print postgres;
! #X msg 27 171 sql DROP DATABASE pddbase sqlend;
! #X msg 8 105 sql CREATE DATABASE pddbase sqlend;
! #X text 374 63 to the newly created 'pddbase' database;
! #X text 375 52 2 Create a new table in the database - this time connecting
;
! #X text 419 141 3 Insert some data. Each click of the message below
;
! #X text 418 151 inserts a new row into the specified table.;
! #X text 450 271 4 Retrieve all data in the specified table using a
wild;
! #X text 27 155 click this to destroy the database;
! #X msg 377 95 sql CREATE TABLE datatable(id INTEGER \, duration FLOAT
\, type VARCHAR \, datetime DATETIME) sqlend;
! #X text 451 282 card '*'. Note each row returned is preceded by an
index;
! #X text 463 358 Also show the degree of error;
! #X msg 459 376 sql SELECT id \, ABS((duration - 1500)/1500) AS error
FROM datatable ORDER BY error LIMIT 1 sqlend;
! #X text 463 346 5 Find the 'id' of entry closest to 1500 msec in duration.
;
! #X msg 418 177 sql INSERT INTO datatable VALUES(22 \, 1023.33 \, 'singing'
\, datetime('now'::date \, 'now'::time)) sqlend \, sql INSERT INTO
datatable VALUES(24 \, 1500.1 \, 'coughing' \, datetime('now'::date
\, 'now'::time)) sqlend \, sql INSERT INTO datatable VALUES(26 \, 2222.11
\, 'sobbing' \, datetime('now'::date \, 'now'::time)) sqlend;
! #X obj 475 549 route 0 1 2 3 4 5 6 7 8;
! #X text 624 548 use route to separate indexed 'tuples';
! #X obj 475 584 unpack 0 0 s s;
! #X text 571 584 use unpack to access individual fields;
! #X msg 449 305 sql select * from datatable WHERE id < 30 sqlend;
! #X obj 377 446 psql pddbase;
! #X obj 8 222 psql template1 localhost 5432;
! #X obj 446 479 bng 15 250 50 0 empty empty empty 0 -6 0 10 -262144
! -1 -1;
! #X text 472 480 Bang when the results from each query have been returned
! ;
! #X text 7 56 1 Create a new database - by connecting;
! #X text 7 69 to PostgreSQL's default database: 'template1';
! #X obj 8 4 cnv 15 250 40 empty empty [psql] 20 12 1 14 -191407 -66577
! 0;
! #X text 27 24 based on [sqlsingle] by Iain Mott;
! #X connect 1 0 21 0;
! #X connect 2 0 21 0;
! #X connect 9 0 20 0;
! #X connect 12 0 20 0;
! #X connect 14 0 20 0;
! #X connect 15 0 17 0;
! #X connect 19 0 20 0;
! #X connect 20 0 15 0;
! #X connect 20 0 0 0;
! #X connect 20 1 22 0;
More information about the Pd-cvs
mailing list