Storing procedures in SQL

TV Thinking a little (not ´´that´´ much) about Linking Bwise with PostgreSql I thought about storing a procedure in SQL (I used the Postgres lately, I suppose mySql or some commercial dbase variation are similar).

In its simplest form, we could take the whole normal tcl procedure definition in text form and after quoting correctly store it in a sql field.

Testing (omiting the pgintcl require, connect command with result in bd and the obvious error checks on the $res var):

 pg_exec $db "create table procs (name varchar(80), version int, body varchar(80))"
 set res [pg_exec $db "insert into procs values ([pg_quote square], 1, [pg_quote {proc square {a} {return [expr $a*$a]}}])"]
 set res [pg_exec $db "insert into procs values ([pg_quote add], 1, [pg_quote {proc add {a b} {return [expr $a+$b]}}])"]

I opened a console with tkpsql (1.2.1) to try the above out, because its command form doesn´t allow the extra quote command, but once the procs are filled in, we can say:

Image TV Wiki srcprocsql1.gif

Clearly, there is a possibility to separate the arguments and the default values like with the equiv. Tcl commands, and there are relations possible with web programming (e.g.apache server with tcl cgi scripts running on safe user), and command construction tools (like interactive command composer)

Now let's see if we can made the procs work from the database:

The sql command could look like:

 select body from procs where version = 1 and name = 'square'

 # make sure the proc we search for isn't there:
 proc square {} {}
 # search for the proc square
 set dbres [pg_exec $db "select body from procs where version = 1 and name = 'square' order by version asc"]
 # implement the latest version:
 eval [lindex [pg_result $dbres -list] end]

Trying it:
 % square 3
 9

Ok.

Now let's add square version 2:

 set res [pg_exec $db 
    "insert into procs values ([pg_quote square], 2, [pg_quote {proc square {a} {return [expr $a^2]}}])"
 ]

 set dbres [pg_exec $db "select body from procs where name = 'square' order by version asc"]
 pg_result $dbres -list

gives:

 {proc square {a} {return [expr $a*$a]}} {proc square {a} {return [expr $a^2]}}

So now:

 lindex [pg_result $dbres -list] end

gets us the last version:

 proc square {a} {return [expr $a^2]}

Clearly we want to correct a mistake here:

 set dbres [pg_exec $db "select max(version) from procs where name = 'square'"]
 set ve [pg_result $dbres -list]
 set dbres [pg_exec $db "update procs set body = [pg_quote {proc square {a} {\nreturn [expr pow($a,2)]\n}}] where version = $ve and name = 'square'"]

We now get a warning, but: Image TV Wiki scrprocsql2.gif

Such is the work often, sigh.

Let's try to make use of the sql storing, by adding a comment possibility:

 set dbres [pg_exec $db "alter table procs add column comments text"]
 set dbres [pg_exec $db "update procs set comments = [pg_quote {from 1\nto 2. \n}] where version = 2 and name = 'square'"]

Again the newline gives a warning, but postgresql should not have a problem with multi-line fields.

Let's try something a bit bigger. I made this procedure to save the procedures from a running bwise instance:

 proc save_procs_sql { {n} } {
   global nnn
   set nnn $n
   uplevel #0 {
      set ooo {}
      foreach i [lsort -dict [info procs]] {
         if {[lsearch $defaultprocs $i] == -1 &&
                 [string index $i 0] != "\$"  &&
                 ![string match pkg_* $i] &&
                 ![string match auto_* $i] &&
                 ![string match tcl* $i] &&
                                 ![string match tk* $i] } {
 #              eval set ttt $\{$i\}
 #              set uuu "set \{$i\} \{$ttt\} \n"
 #              eval set uuu {}
              #append ooo "set " $ttt " " $uuu \n
              set ooo "proc $i { "
              foreach a [info args $i] {
                  set d ""
                  if {[info default $i $a d]} {set d " {$d}"}
                  append ooo "{$a $d} "
              }
              append ooo " } {" [info body $i] "}\n\n"
             set dbres [pg_exec $db "insert into $nnn values ([pg_quote $i], 0, [pg_quote $ooo])"]
          }
      }
   }
 }

I made a new table with large body entries, I don´t know what 10000 chars does for sql, but it appears to work:

 pg_exec $db "create table bwiseprocs (name varchar(80), version int, body varchar(10000))"

Now:

 save_procs_sql bwiseprocs

gets a lot of complaints on the command console, but no errors, it seems, but then again if a field is too long for instance, things just move on, and there is no tcl script error thrown. Of course this is just a tryout...

Let's see if data comes back from the database backend with bwise procedures now:

Image TV Wiki scrprocsql3.gif

Yep. How many procs:

 select count(body) from bwiseprocs

gives 176 (the procs from pg* are counted, too), and operations seem to go smooth and fast, except for the quoting warning.

A simple but usefull example: Saving song data from file names to sql

Category Example

Category Database

Category Bwise