|
Table Of Contents (show)
1. SOS: Sqlite On SocketsSOS 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. 1.1 SOS OptionsFollowing 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" }
2. CommandsThere following are the main user commands in SOS. 2.1 queryThe 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)"}
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.
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. 2.2 loadqueryThe 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.
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
}
2.3 evalqueryThe 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.
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. 3. SecurityThere are 3 areas of security within SOS: encryption, userid logins and SQL authorization. 3.1 EncryptionEncryption 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. 3.2 Userid LoginsSOS 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. 3.3 Authorized SQLAuthorize provides access control to SQL statements based on userids and groups. Authorize is discussed in detail in the following section. 4. AuthorizeWhen a server mode is run with the -authsql option, sqlite authorize will be used to limit access to SQL by userid and/or group. 4.1 Rule FormThe 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 4.2 Default Rules
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.
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 }
}
}
4.3 OP AliasesAliases 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}
}
4.4 Catch-All RulesCatch-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 }
4.5 Rule PrecedenceThe order of precedence for rules checking is: user/op user/=alias @group/op @group/=alias user/ @group/ /op /=alias 4.6 Replacing AuthorizeIf all of the above is insufficient another alternative is to implement your own authentication via --authcmd. See SetupAuth in the sos.tcl source. 5. Getting SOSSOS 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).
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 6. Client GUIThe 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. 7. Server GUIThe 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: 7.1 StatusDisplays info about the number and size of queries, as well as bad query or login attempts. 7.2 HostsHosts Similar to Status, except that it breaks down information on a host-by-host basis. 7.3 ConfConf 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. 7.4 DBDB displays the sqlite options and the datafile info. The latter also shows tables, views, etc. Closing and opening DB will refresh these values. 8. The -icfg OptionsThe main -icfg options discussed above are:
For others, see the source code. 9. TODO's
© 2008 Peter MacDonald |