Moving to GitHub, slowly

The software on this pages will slowly be moved to GitHub https://github.com/hilbix/. The CVS repository will be migrated to GIT as well, so the history will be preserved, a bit. See FAQ.

Scylla and Charybdis, sq - Tools

The tools are developed under Linux with ESR's paradigm release early, release often in mind.
So you can consider this beta software, or alpha, or pre-alpha, or even worse ;)

Have a look in the download directory for all downloads.
As always here, all you get is the source. No binaries here.

sq 0.5.0-20090529-180428

SQLITE3 query tool for shell usage

⇒ ⇒ ⇒ The development shifted to another location. All future versions will be published at https://github.com/hilbix/sq ⇐ ⇐ ⇐

This only works with sqlite version 3 databases!

If you want to pipe data in and out a sqlite3 database from shell, this tool is for you. With this tool you never have to escape arguments from shell ever again (as long as you don't pass them in the SQL string directly).

Example insert:

export env="this goes into col1"
arg="this goes into col2"
./sq3 test.db 'create table a ( col1, col2, col3 ); insert into a values ($env,?,:fd3)' "$arg" 3< filename

This stores the environment variable $env into col1, puts the arg into col2 and reads col3 from filename as BLOB.

Reading the data is likewise easy:

./sq3 test.db 'select col1,col2,col3 from a' |
while read -r row col type data
do
case "$type" in
t)      echo "row=$row col=$col data=$data";;
e)      echo -e "row=$row col=$col data=$data";;
0)      echo "row=$row col=$col NULL";;
esac
done

Data which cannot be read easily is escaped using the shell escape.

Another usage for access of attachment or binary data:

./sq3 test.db "insert into a values ('file',NULL,:fd0)" < file
./sq3 -r test.db "select col3 from a where col1='file'" | cmp - file

Or lazy argument processing:

./sq3 test.db "delete from a; insert into a values ('line1','line2','line3'); insert into a values ('lineA','lineB','lineC')"
./sq3 -r test.db "select * from a" |
{
while read -r col1 && read -r col2
do
read -r col3 || echo -n "last "
echo "lazy: $col1 - $col2 - $col3"
done
}

The funny thing is the missing newline which makes the "read -r col3" to return EOF. Well, one can consider this a feature, but it's moreover a crude sideeffect ;)

Another pattern now can be (you can leave out the -a and the eval if there cannot be spaces in the colums):

./sq3 -a -s' ' test.db "select * from a" |
while read -r c1 c2 c3
do
eval c1="\$'$c1'"
eval c2="\$'$c2'"
eval c3="\$'$c3'"
echo "lazy: $c1 - $c2 - $c3"
done

History:

version 0.5.0-20090529-180428

download (18137 bytes) sig

Option -v is for convenience use of -a, -s and -e Option -s now can be given multiple times. Options -b and -e added.

Option -v was added for easy shell eval purpose and needed option -e as well as multiple option -s. For symmetry of -e, option -b has been added, too.

The new option -v sets all options exactly as how in following example:

echo eval `./sq3 -a -s"=$'" -s"' " -e"'" test.db "select 'test1',' complex string ','test2','another '' string'"`

This outputs

eval test1=$'\040complex\040string\040' test2=$'another\040\'\040string'

version 0.4.1-20090528-113101

download (17945 bytes) sig

Option -d now shows the statement to execute and space-trimming of commandline.

Important! Don't use "begin;" for write transactions, use "begin exclusive;". This is due to the way SQLITE prevents deadlocks while within a transaction. If the transaction is started nonexclusively (without "exclusive"), the lock promotion code does not obey timeouts and immediately fails to prevent unneccessary deadlocks. If this is a correct behavior can be argued, however there is a way around it: By using "begin exlcusive" for transactions which permanently alter data.

Behavioral change: Before 'sq3 test.db ""' was an error, now this is silently skipped. However the behavior of 'sq3 test.db ;' still is the same, so if you need to fail on an empty statement, be sure to always have a trailing ';', even on empty statements.

This change is due to 'sq3 test.db " select 1; "' previously gave an error, as the blank after the ';' started a new, empty statement, which was not nice if you - somehow - tried to have some formatted shell script.

version 0.4.0-20090525-021918

download (15146 bytes) sig

Option -s and -u added and -t fixed for each statement. Now installs to /usr/local/bin/

Option -s sets the separator to use to output the fields. Supresses field name and type output. Best use with -a.

Option -u flushes each row (makes it unbuffered).

version 0.3.0-20090216-103503

download (14830 bytes) sig

Extended :fd#__ support. This can be considered a bugfix (feature fix).

If you wanted to read more than one field with the same termination rule, this was not possible, as, for example, several :fd0__32 parameters all defined *one*single* parameter which was set. To be able to create more than one parameter this way, just append _something to it, like: :fd0__32_a :fd0__32_b and so on.

version 0.2.1-20080607-015649

download (14492 bytes) sig

Bugfix release, echo -e sequence still was bugged

The problem is:

echo '\\0041' | { read a; echo -e "$a"; }

vs.

echo '\0041' | { read -r a; echo -e "$a"; }

I prefer the latter, but tested the first one. SIGH.

[view more history]

[view complete history]

License and Disclaimer

All you can see here is free software according to the GNU GPL.
Copyright (C)2000-2011 by Valentin Hilbig
Note that the software comes with absolutely no warranty of any kind.
You use the software at your own risk.
Valentin Hilbig cannot be hold responsible for any unintended damage,
lost data or malfunction of the software you can find here.

Last modified: 2011-09-12 by Valentin Hilbig [ Imprint / Impressum ]