Version 7 of Storing procedures in SQL

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.

Testig (omiting the connect command and the obvious error checks):

 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:

Clearly, there is a posssibility to seperate 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 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 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:

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.

enter categories here