Playing ISBL

JBR - 2011-04-02

I first read about ISBL and PRTV[1 ](pdf) in early 2008 and it has been running about my thoughts ever since. This is mostly because I find SQL to be unintuitive and difficult to understand. I rather like something more algebraic.

I didn't want to write a complete database engine so here is a package that is a thin layer translating ISBL to sqlite. The main ISBL operators are available. Some features are a little different (delayed evaluation as views). The type system of "Domains" is absent. As with the original ISBL system the database update is problematic but I have provided a nice insert and delete with "+=" and "-=". I'll add update if I get some inspiration.

Here are the pieces:

Some examples to test it:

 lappend auto_path /home/john/lib

 ::tcl::tm::path add .

 package require isbl 1

 isbl create db data.db

 proc ? { 1 2 } { if { $1 ne [string trim $2] } { error "Huh? $1 ne $2" } }


 # Table construction
 #
 db eval { T  = % 1->x, 2->y }                          ; # make some tables from tuples
 db eval { T  = % 1->x, 4->y }                          ; # Assignment deletes old data!
 db eval { K  = % 1->x, 5->l }

 db eval { T += % 5->x, 9->y }                          ; # Insert
 db eval { T += % 7->x, 9->y }                          ; # Insert
 db eval { T -= % 7->x }                                ; # Delete


 db eval { X := T % x }                                 ; # isbl reference by name (sql view)
 ? [db eval X] { 1 5 }

 # Relational Operators
 #
 ? [db eval { T }]              { 1 4 5 9 }             ; # A single table

 db eval { T += % 5->x, 9->y }                          ; # Insert this again - It should replace
 db eval { T += % 5->x, 9->y }                          ; # Insert this again - It should replace

 ? [db eval { T }]              { 1 4 5 9 }             ; # Better not have changed

 ? [db eval { T % x }]          { 1 5 }                 ; # Project
 ? [db eval { (T + K) % x }]    { 1 5 }                 ; # Union
 ? [db eval { T % x . K % x }]  { 1 }                   ; # Intersect
 ? [db eval { T % x - K % x }]  { 5 }                   ; # Difference
 ? [db eval { T * K }]          { 1 4 5 }               ; # Join
 ? [db eval { T : x == 1 }]     { 1 4 }                 ; # Select

 ? [db eval { T * K % l -> x, x -> f }] { 5 9 1 }       ; # Column mapping w/as
 ? [db eval { T * K % l as x, x as f }] { 5 9 1 }       ; # Column mapping w/->


 # Special forms
 #
 db function int          mod2 { x } { expr { int($x) % 2 } }
 db function { real int } func { x } { list [expr $x*2] 2 }

 ? [db eval { T : mod2(x) }]       { 1 4 5 9 }  ; # Select from function return value
 ? [db eval { T % func(x | a,b) }] { 2 2 10 2 } ; # Project for function w/ tuple return


 # Project and remove columns.
 #
 ? [db eval { T % * !x }]          { 4 9 }
 ? [db eval { T %   !x }]          { 4 9 }
 ? [db eval { T % y  * }]          { 4 1 9 5 }

A Makefile to wrap it all up:

 PT=/home/john/src/tcllib-1.13/modules/pt/pt

 VERSION = 1.0

 isbl : main.tcl isbl-$(VERSION).tm
        sdx wrap isbl -runtime `which tclkit8.6`
        make test

 isbl-$(VERSION).tm : unsource isbl.tcl isbl-parser.tcl isbl-actions.tcl
        unsource isbl.tcl > isbl-$(VERSION).tm

 isbl-actions.tcl : parser-actions isbl-parser.peg isbl-actions.act
        parser-actions isbl-parser.peg isbl-actions.act > isbl-actions.tcl

 isbl-parser.tcl : isbl-parser.peg
        $(PT) generate snit -class isbl-parser -name isbl-parser isbl-parser.tcl peg isbl-parser.
        sed -e s/PACKAGE/isbl-parser/ < isbl-parser.tcl > tmp
        sed -e s/^return//            < tmp > isbl-parser.tcl
        rm tmp

arjen - 2011-04-04 03:09:11

Have you looked at TclRAL? That is a complete relational algebra package

jbr - There was no real imperative to writing the Playing ISBL package other than to explore Parser Tools, sqlite, modules, starpack, and to create an infix relational expression syntax. As a preprocessor for SQL it may be useful in simplifying existing code (or not). TclRAL is excellent. It has a complete set of prefix operators (tcl commands), but it has no backend.