[PD-cvs] externals/postlude/psql help-psql.pd, 1.1, 1.2 psql.c, 1.1, 1.2

14 Nov 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;

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

2003

[PD-cvs] externals/postlude/psql help-psql.pd, 1.1, 1.2 psql.c, 1.1, 1.2