Recent Changes - Search:


Mod /

SqliteSock

(:toc:) (:toc:)

SOS: Sqlite On Sockets

SOS provides client/server access to an sqlite databases via Tcl sockets. The SOS client sends a query to the server, then collects the full response before invoking call-backs. There is also optional modes for Tcl transactions, server defined commands, bulk uploaded lists of rows, and up/downloaded files. This makes the protocol suitable for light-duty clients where queries do not routinely up/download millions of rows. (eg. a Small office or POS terminal).

The SOS implementation is a single script that runs in either client or server mode. Both of these modes also have optional graphical user interfaces.

SOS may be started from the File menu in Gsqlite, or it can be started from the command line using Wize:

% wize /zvfs/mod/lib/sos.tcl -server 2 -dbfile /tmp/foo.db &
% wize /zvfs/mod/lib/sos.tcl -client 2

or, if you've downloaded the script:

% wize sos.tcl -client 2

Note the value 2 opens the Tk gui for that mode. The client gui provides a user interface for sending queries, while the server gui displays statistics (and can start a client). Here are a couple of screen-shots:

Note, when a client runs on a host other than the server, the -host option needs to be specified.

SOS Options

Following are options accepted by SOS:

    
{ -authsql    False      "Enable authorize of SQL compile" }
{ -client     0          "Run client UI (2=gui, 3=blocking-gui)"}
{ -cryptpass  {}         "Password to encrypt net traffic (uses xor-salt)" }
{ -db         {}         "Handle of already open database for server" }
{ -dbfile     {}         "Database file for the server" }
{ -debug      0          "Set to >0 to trace transactions, or <0 for quiet" }
{ -host       127.0.0.1  "Hostname containing server" }
{ -icfg       {}         "Internal configuration option pairs" }
{ -initfile   {}         "Tcl file to source: may access $(db), etc" }
{ -initscript {}         "Like -initfile in a string" }
{ -limit      -1         "The maximum rows to allow in a result" }
{ -localhost  False      "Server accepts only localhost connections" }
{ -login      user/      "Userid/password for client authentication" }
{ -number     False      "Client output lines are numbered" }
{ -optsfile   {}         "File containing initialization options" }
{ -port       5775       "Port of server" }
{ -sep        \t|        "Output data separator for builtin client" }
{ -server     0          "Run in server mode (2=gui)"}
{ -tls        False      "Use tls secure connection (requires TLS)" } 
{ --authcmd   {}         "Command to setup sqlite authorize" }
{ --cryptcmd  {}         "External command to do encryption" }
{ --passcmd   {}         "External command to verify userid/password"}
{ --srvcmd    {}         "Command called everytime server gets a query" }

Commands

There following are the main user commands in SOS.

query

The client makes use of the [query] command to send a query up to the server. The signature for query is:

  proc query {_ query args}

where args options are:

    
{ -cmd      {}    "Callback handler for async mode"}
{ -columns  {}    "Column names, for -mode insert (optional)"}
{ -destfile {}    "File to send when -type is wfile" }
{ -file     {}    "When -type is wfile or rfile, the local file name" }
{ -limit    {}    "Override the clients default -limit" }
{ -mode     rows  "Format of data returned from server (see below)"}
{ -set      {}    "Name/values pairs for server to set in array v()" }
{ -type     sql   "Type of query (see below)"  }
{ -values   {}    "Values to server to setup into array v()" }
{ -xlock    deferred "Locking mode for -type xact (ie. a transaction)"}

(:showex div=tosamp:)

Here is an example session:

set o [::lib sos new]
array set r [$o query {SELECT * FROM t1 WHERE a > 5}]
puts "RESP($r(-code),$r(-fields)): $r(-data)"

and this outputs:

RESP(0,count name): {1 {Bob Brown}} { 2 {Mary Kay}}

The results of a query are returned as a pair list including:

-code   : The result code with 0=OK and 1=ERROR
-data   : The data results from the query
-fields : The column names (except when -mode is raw)

By default queries will block, but the -cmd option may be used for async callback handling.

Optional name/value data pairs may be passed in a query using the -set option. These are substituted for :v() or $v() in the query by sqlite.

(:showex div=toset:)

Here is a sample using -set:

set o [::lib sos new]
$o query {SELECT * FROM t1 WHERE a > :v(a)} -set {a 19}
$o query {SELECT * FROM t1 WHERE a > $v(a)} -set {a 19}
$o query {INSERT INTO t1 VALUES(:v(a),:v(b)} -set {a 19 b "My String"}

-mode load prepends "INSERT INTO" (if needed) and auto-appends the VALUES(). As well, load uses transactions and so is faster.

The -mode option controls server processing and the format of expected results:

rows   : -data = a list of lists (this is the default)
flat   : -data = a flat list
raw    : -data = a flat list (and -fields = {})
load   : -data = rowids from INSERT'ing from a list

In addition to SQL, query is capable of sending other types of requests using -type:

sql    : An SQL query.  This is the default.
rfile  : Read or download a file from the server (rdir).
wfile  : Write or upload a file to the server (wdir).
cmd    : Execute a server defined command (cmdns)
xact   : Execute a server defined command as a transaction (xactns)
eval   : Eval an arbitrary Tcl script string (evalok)

These might be useful for example if a client needed to handle image files. However, non-sql requests do require the server to setup the associated -icfg option (given in brackets above) to be available.

loadquery

The loadquery command performs a bulk upload or INSERT from lists of values. (Note: The loadquery command is just a front-end to query)

The signature for load is:

  proc loadquery {_ table values {columns {}}}

Column names need only be specified if a subset of fields is being set. The INSERT INTO prefix is optional.

(:showex div=toload:)

All of the following are valid ways of doing bulk insertion:

set lsts {{1 "Mr Brown"} {2 "Mr Ed"} {3 "Mrs Lusk"}}

$o loadquery t1 $lsts
$o loadquery t2 $lsts {a b}
$o loadquery {INSERT INTO t2} $lsts {a b}
$o query {INSERT INTO t2} -columns {a b} -values $lsts -mode load

# The traditional method just loops over an INSERT statement.
foreach i $lsts {
    set vars [list a [lindex $i 0] b [lindex $i 1]]
    $o query "INSERT INTO t2 (a b) VALUES(:v(a),:v(b));" -set $vars
}

evalquery

The evalquery command emulates the sqlite eval sub-command. It hides the fact a client is remote from the server.

The signature for evalquery is:

  proc evalquery {_ query {var {}} {body {}}}

The main advantage of evalquery is that it reports syntax errors when used with wize -Wall. The disadvantage is that it blocks.

(:showex div=toeval:)

Here is a small example that returns tables.

  package require Mod
  namespace eval ::tst {

     # Force sos to load, then import evalquery
     catch {::lib  sos  query}
     namespace import ::lib::sos::evalquery

     proc Main {} {
         set o [::lib::sos::new]
         set qry "SELECT * FROM SQLITE_MASTER"
         set lst {}
         evalquery $o $qry x {
             if {$x(type) != "table"} continue
             lappend lst $x(name)
             if {[info exists XYZ]} { set a b c }
         }
         $o delete
         return $lst
     }

     tclLog "TBLS: [Main]"
     exit
  }

Note that when evalquery is called in server mode, the query is passed directly to the sqlite eval command. Otherwise, it is passed to query.

Security

There are 3 areas of security within SOS: encryption, userid logins and SQL authorization.

Encryption

Encryption of all data going across the network can be enabled by setting the -cryptpass option. Both the client and server must use the same setting. The protocol used is Xor-salt, a weak but low overhead homegrown encryption. If more security is needed the -tls option can be used, but this requires TLS as well as key management.

Note: the -icfg option sitesalt option may also be set, to add a global prefix to -cryptpass.

Userid Logins

SOS supports a userid/password via the client -login option. The default setting is guest/ (ie. userid is guest and password is empty).

The server stores passwords as slash separated triplets userid/password/group. By default the server setup is:

    { admin/service/root   appl/demo/users   guest//users }

This can be modified with the -icfg options userids or useradds.

Authorized SQL

Authorize provides access control to SQL statements based on userids and groups. Authorize is discussed in detail in the following section.

Authorize

When a server mode is run with the -authsql option, sqlite authorize will be used to limit access to SQL by userid and/or group.

Rule Form

The AuthUsers array stores sets of rules that define access control to SQL.

The general form for each entry in AuthUsers is:

    USERID/OP  { DBNAME|TABLE|COLUMN|ARG AUTH ... }
    @GROUP/OP  { DBNAME|TABLE|COLUMN|ARG AUTH ... }

OP is the suffix part of any SQLITE_* operation (eg. INSERT, DELETE, UPDATE, READ, WRITE, SELECT, CREATE_TABLE, etc). It can also be an alias if prefixed with an =.

DBNAME|TABLE|COLUMN|ARG is 4 bar separated glob patterns or just a single *.

AUTH must be one of: DENY IGNORE OK.

To see what OPs sqlite [authorize] generates, try running the following, open the client and then issues some queries:

  wize sos.tcl -server 2 -debug 3

Default Rules

(:showex div=ause:)

The following rules come predefined in AuthUsers:

    variable AuthUsers
    array set AuthUsers {
        appl/INSERT   { *|sqlite_master|*|* DENY }
        appl/DELETE   { hide*|*|*|* IGNORE  }
        guest/=modify { * DENY }
        admin/        { * OK }
        @users/CREATE_TABLE  { * DENY }
        /PRAGMA       {
            *|database_list|*|* OK
            * DENY
        }

    }

The rules limit userid guest to read-only access and userid appl to non-structural access, minus deletions from any dbname begining with hide. Only admin is allowed arbitrary access to PRAGMA.

These default rules can be replaced or added to via the -icfg options authdata or authadd.

(:showex div=toinit:)

Here is an example of adding new values via -initfile.

  wize sos.tcl -initfile ./srv.conf

where srv.conf contains:

-server 2 -authsql 1 -icfg {
  useradd {
     usr1/pippass/users
     usr2/sloppass/users
     guest/somepass/users
  }
  authadd {
     usr1/INSERT    { * DENY }
     usr2/DELETE    { main|privtbl|*|* DENY  }
     @users/INSERT  { privdb|*|*|* DENY }
  }

}

OP Aliases

Aliases are defined to represent sets of operations. They prefixed with an = and used in place of an OP. Aliases definitions are stored in the array AuthAlias. New alias definitions may be added with the -icfg option authalias. The alias modify comes predefined, ie:

    variable AuthAlias
    array set AuthAlias {
        modify {PRAGMA INSERT UPDATE WRITE DELETE CREATE_TABLE}
    }

Catch-All Rules

Catch-all rules (like /PRAGMA above) are defined with an empty USERID, @GROUP or OP. These matches are attempted only after failing to match a rule that does contain all these fields (see Precedence).

Note, catch-alls work only if there is at least one AuthUser entry defined for the current userid or group. Otherwise [authorize] setup will not even be entered. Here is an example catch-all:

  lowuser/   { * DENY }
  @lowgroup/ { * DENY }
  /PRAGMA    { * DENY }

Rule Precedence

The order of precedence for rules checking is:

  user/op
  user/=alias
  @group/op
  @group/=alias
  user/
  @group/
  /op
  /=alias

Replacing Authorize

If all of the above is insufficient another alternative is to implement your own authentication via --authcmd. See SetupAuth in the sos.tcl source.

Getting SOS

SOS is builtin to Wize, and may be started from the command line:

    wize /zvfs/mod/lib/sos.tcl -client 2

or from the Gsqlite File menu after running:

    wize /zvfs Mod/Gsqlite

Alternatively, the source can be downloaded from CVS.

The GUI mode of the SOS server requires the TreeView widget in Wize in order to work. Non-gui SOS server can run stand-alone by sourcing Wizlite.tcl (available from CVS).

(:showex div=tostdal:)

For example, the following should work with most any version of wish:

 source Wizlite.tcl
 source sos.tcl
 set s [lib::sos::new -server 1 -dbfile ~/tmp/mydb.dat]
 lib::sos::new -client 2

Client GUI

The client GUI uses a spinbox widget for sending commands and a text widget to display results. Various options can be changed with the conf command, and help will display info on the other available commands.

Server GUI

The server GUI uses a TreeView to displays various metrics. You can also right-click to bring up the menu to start a new client.

There are 4 sections in the display:

Status

Displays info about the number and size of queries, as well as bad query or login attempts.

Hosts

Hosts Similar to Status, except that it breaks down information on a host-by-host basis.

Conf

Conf shows the configuration options that SOS was started with. Double clicking on the value lets you change the values. You could for example change the cryptpass.

DB

DB displays the sqlite options and the datafile info. The latter also shows tables, views, etc. Closing and opening DB will refresh these values.

The -icfg Options

The main -icfg options discussed above are:

  • authdata - replace all values in auth
  • authadd - add values to auth
  • authalias - add auth aliases
  • sitesalt - prefix to add to -cryptpass
  • userids - userid/pass/group triplets
  • useradd - add values to userids

For others, see the source code.

TODO's

  • Limit the query run time on server via [progress].
  • Document passwords, authorize, host filtering, etc.
Edit - History - Print - Recent Changes - Search
Page last modified on November 23, 2009, at 12:09 PM