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

In it´s 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 it´s command form doesn´t allow the extra qoute command, but once the procs are filled in, we can say:

[http://82.171.148.176/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 lets see if we can made the [proc]s 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 lets 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 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:
[http://82.171.148.176/Wiki/scrprocsql2.gif]

Such is the work often, sigh.

Lets 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.

Lets try is 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, its 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...

Lets see if data comes back from the database backend with bwise procedures now:

[http://82.171.148.176/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]

----
!!!!!!
%| enter categories here |%
!!!!!!