Linking Bwise with PostgreSql

TV Because for non-Tcl reasons ([L1 ], [L2 ]) I recently looked PostgreSQL, I rethought the thought I had many years ago, that bwise and a good database could make a good combination.

So I looked at pgacces ([L3 ] moved to pgfoundry) which requires a compile while I want to make a easy to use also windows front end, and pgintcl [L4 ] which is written in Tcl and should do the trick.

BAS Note that pgaccess is an application that uses the pg driver (it can use either pgtcl or pgintcl). There are 3 main PG Tcl drivers: pgtcl, pgtclng and pgintcl. The first 2 are C based, and the last one (as you noted) is in Tcl

So I installed the latter in Tcl/lib, the package require worked, and after setting some permissions on the local net I could access my (web, file and otherwise) Server/TV/Video/HD edit station from the example script from pgintcl. The fedora 8/64 based postgress server resonded normal, while wiki access and web site indexer sql databases continued to function normal. So far so good.

To test with latest version pgintcl:

 source tkpsql.tcl
 # requires pgin.tcl in the same dir it seems

The data base should have been created with a user with rights to do what you want and login rightd (see pg_sql docu) and the password must be in some system file in the postgres installation directory, just like for like in this case a login on another machine requires a .conf file edit.

Using some special buttons the output is:

 List Databases
 OK with 6 rows and 2 columns.

 Database Name Owner
 postgres      postgres
 abcd          postgres
 template0     postgres
 template1     postgres
 test          postgres
 wikidb        wikiuser

 List Tables
 OK with 1 row and 3 columns.

 Schema Table      Owner
 public table_name zyx

 List Users
 OK with 3 rows and 3 columns.

 Username User-ID Rights
 zyx      76543   
 postgres 7865     Superuser, Create Database, Update Catalogs
 wikiuser 0987   

If you have a console window:

 % array get dbinfo
 has_schema 1 port 5432 host 192.168.0.1 password pass dbname test user zyx
 % run_sql "select * from table_name"

will put the result in the upper window.

Let's test a small computation aimed example, first through the run sql window:

 create table squares (
 n int,
 sq varchar(20)
 )

then from the tcl prompt:

 for {set x 0} {$x <= 100} {incr x} {
     run_sql "insert into squares values ($x, [expr $x*$x])" 
 }

Now from sql:

 select * from squares

gets a list of number to 100 and their squares from the database, good. Getting results is a little bit of a haggle with the database driver routines it seems, results must be gotten per row.

Let's try something:

  % proc pp {} {global a ; puts [array get a]}
  % pg_select $db "select * from squares where n<10" a pp
 sq 0 .tupno 0 n 0 .headers {n sq} .numcols 2
 sq 1 .tupno 1 n 1 .headers {n sq} .numcols 2
 sq 4 .tupno 2 n 2 .headers {n sq} .numcols 2
 sq 9 .tupno 3 n 3 .headers {n sq} .numcols 2
 sq 16 .tupno 4 n 4 .headers {n sq} .numcols 2
 sq 25 .tupno 5 n 5 .headers {n sq} .numcols 2
 sq 36 .tupno 6 n 6 .headers {n sq} .numcols 2
 sq 49 .tupno 7 n 7 .headers {n sq} .numcols 2
 sq 64 .tupno 8 n 8 .headers {n sq} .numcols 2
 sq 81 .tupno 9 n 9 .headers {n sq} .numcols 2

BAS Note that there are several ways to get data out with pgtcl (pgintcl). The above probably isn't the most efficient for this case, but has other uses. You can look at [L5 ] and [L6 ] for more info on getting data out of PG.

After a little reading I tried this:

 package require pgintcl
 # connect to the database, for now presume a one time connection of the whole bwise to one DB and server
 set db [pg_connect -conninfo [list host = 192.168.0.1 user = zxy dbname = test password = 654321]]

 # try some data retrieval
 set dbres [pg_exec $db "select * from squares limit 5"]
 # and get the result in some form
 pg_result $dbres -llist
   {0 0} {1 1} {2 4} {3 9} {4 16}

Ok, that should work for a block.

Let's define a block and a dosql function to try it out with BWise . Using pro_args (see interactive command composer)

 % pro_args newproc {{f {dosql}}  {name {sqlsearch1}}  {in {term table}}  {out {ol}}}
 newproc dosql sqlsearch1 {term table} ol

Of course I used the clicking on the arguments to obtain the correct argument list for the newproc function. It would also be possible to create the wanted function (proc) first and then make a block with pins exactly matching its arguments :

 proc dosql { te ta } {
   # testing
   set dbres [pg_exec $db "select $te from $ta limit 5"]
   puts -nonewline [pg_result $dbres -error]
   return [pg_result $dbres -llist]
 }

now call the proc from the block:

 set sqlsearch1.ol [dosql ${sqlsearch1.term} ${sqlsearch1.table} ]

Set some values for the inputs, and:

funpropping the left to blocks gives the desired results.

See also Storing procedures in SQL, which (when proven to work good) could be used to store the procedures of blocks.

I tried PL/Tcl, which is server-side built-in Tcl processing in the PostgreSQL server, comes standard with recent distributions, and appears to work on Fedora 10, enabled with

 createlang pltcl test;

from the manual:

 CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
    if {$1 > $2} {return $1}
    return $2
 $$ LANGUAGE pltcl STRICT;

to test:

 ==>select tcl_max(3,4);
 tcl_max 
 ---------
       4
 (1 row)

And also: Scripting formula manipulations in Maxima with results in PostgreSQL.