tcl, sqlite and dbedit

Looking for code examples pertinent to a CRUD tcl app I came across dbedit.tcl, explained here and downloadable from here.

After some thrashing around I found out how to make it work:

package require Tk
package require sqlite3
source "dbedit.tcl"

# open example.db or create if absent
# dbhandle is how we access the db
set dbName "example.db"
sqlite3 dbhandle $dbName

# set up sql to create & populate table
set createString { DROP TABLE IF EXISTS "rows";
  "col1" TEXT, 
  "col2" TEXT, 
  "col3" TEXT);

CREATE TABLE "numbers" (
  "number1" INTEGER, 
  "number2" INTEGER, 
  "number3" INTEGER);

INSERT INTO "rows"("col1", "col2", "col3") 
VALUES ('one', 'two', 'three')

INSERT INTO "numbers"("number1", "number2", "number3") 
VALUES (1, 2, 3)

# execute sql
dbhandle eval $createString

# create a 'UI' with a button which invokes dbedit on $dbhandle
set blurb "Example use of dbedit.tcl from"
pack [text .e1 -width 50 -height 2 ]
.e1 insert 1.0 $blurb
.e1 configure -relief flat
.e1 configure -bg gray80
.e1 configure -state disabled 
pack [label .l2 -text "Press the button to look at example.db
- this will open another window."]

# and this it where it happens
pack [button .b -text "view $dbName" -command "dbedit::start dbhandle"]

copy this to example.tcl, do

tclsh example.tcl
and note that dbedit actually does the whole CRUD thing.


Learning tcl/tk

In an increasingly desperate and protracted struggle to find a decent GUI framework I'm trying to learn tcl/tk. Step 1, find a grammar for the language.

There is no formal grammar and we don't need one comes the answer. OK.

Here's some legal yacc:



: commands 

: command
| commands SEMICOLON command
| commands NEWLINE command

: words

: firstWord
| firstWord TAB_OR_SPACE trailingWords
| comment

: word

: word
| trailingWords TAB_OR_SPACE word

: simpleWord
| quotedWord
| bracketedWord
| wordForSubstitution
| wordForExpansion

| simpleWord CHAR

: '"' word '"'

: '{' word '}'

: '[' word ']'

: '{' '*' '}' word

: '#' charList NEWLINE

| charList ANYCHAR


Now it has to be said this doesn't get you very far, I've skipped the lexer and the CHAR / ANYCHAR tokens are distinctly iffy. I can't recall if yacc parsers are rentrant, but if they aren't that's a problem as well. So that's a rough cut at the syntax. The semantic actions you ask? My problems are only just beginning, the semantics are huuuge.

A diagram here:


Computer graphics basics

Having computers put letters on screen and paper happened fairly soon after computing emerged from it's primeval phase: displaying images was the next obvious thing to do. Early printers and screens were raster devices: output is a series of dots written left to right and top to bottom. Scanners and cameras split the image into dots of a particular colour which are then saved to a file, generally in left-right top-down order.

Each dot an image is called a pixel, short for picture element. It encodes a colour, the image is rendered on the output as a series of coloured dots in rows of the correct size. More pixels means a more detailed image, hence a 10 megapixel camera will capture more detail than a 1 megapixel one, similarly scanning a document at 300 dots (ie pixels) per inch will capture more pixels than at 100 dpi.

Images can also be generated and saved using vector graphics. Instead of storing pixels the file contains abbreviated or encoded instructions for drawing a particular shape. This cuts down on file size and enables better enlargement of the image. This method is impractical for for cameras and scanners and requires more sophisticated software to interpret the instructions and send the resulting pixels to the output device.

The colour encoded by a pixel can be a simple black / white distinction, or a reference to an entry in a palette (eg .gif files have pixels that are one of the 256 colours in the images palette), or a shade of grey or a red / green / blue triple specifying a colour. RGB triples usually consist of three numbers in the range 0 - 255 where 0 is no colour and 255 is intense red / green / blue. This is the most prevalent scheme at the moment, it includes a large proportion of colours distinguishable by eye.

Full colour image files can get pretty big, leading to storage problems, so compression has been used for a long time. Data compression comes in two forms, lossy and lossless. Lossy compression is irrevocable loss of data whilst retaining a recognisable image: lossless compression enables exact reconstruction of the original data.

Different image files use different techniques and have different limitations, a summary follows and there's more at wikipedia.

raster files
palette based
lossless compression
.png .tif(f)
lossy compression
vector files

Acquiring a file name in a batch file using drag and drop

@echo off
set /p FILE_NAME="drag file here: "
echo %FILE_NAME%

save as getFile.bat or somesuch, run it and drag a file of your choice onto the command window. Job done!


Concatenating images into video using ffmpeg under windows

This can be a pain if you have arbitrarily named image files to assemble into a video. However, if you list your files, one per line, in a file called jpegsList.txt and execute the following script, you have your video in jpegs.mp4.

set LIST_FILE=jpegsList.txt
set CONCAT_FILE=jpegs.bin
set OUTPUT_FILE=jpegs.mp4
for /f "tokens=*" %%t in (%LIST_FILE%) do type %%t >> %CONCAT_FILE%
type %CONCAT_FILE% | ffmpeg.exe -f image2pipe -i - %OUTPUT_FILE%
The example in the documentation:
ffmpeg -f image2 -i foo-%03d.jpeg -r 12 -s WxH foo.avi

leads one to believe that ffmpeg will accept a list of file names as input. This is true only as per their example, not in general. The insight here is that ffmpeg isn't expecting a list of file names, it's expecting raw image data.

caveat: This is using ffmpeg version N-69972-g6c91afe


Markdown cheat sheet

quick reference for basic markdown.


Postioning elements at the centre of a web page

An old chestnut, often solved by using a table. If you, your client or your employer object to such unnatural practices you have to use css or (just say no) javascript. There are various dodges around, this one extends them with a bit of a cheat:


    background-color: black;
    overflow: hidden;
    color: white;

    /* it's not a table, mkay? */
    display: table;
    /* any dimension does for height and width, you may need a 
    max-height or max-width on contained elements... */
    height: 2000px;
    width: 3000px;
    /* position top left of (non)table at centre of page */
    position: absolute;
    top: 50%;
    left: 50%;
    /* drag it back by half width and height */
    margin-top: -1000px;
    margin-left: -1500px;
    /* centre inline elements */
    text-align: center;

    /* what table? */
    display: table-cell;
    /* centre vertically */
    vertical-align: middle;


    <div class="table">
    <div class="tabledata">
        <!-- et voila -->

See it for real here.

Caveat works on Firefox 11. Your mileage may vary.


cgi web server in 8 LoC*

Found the following incantation on the web, sorry, I can't remember where. An improvement on this one

import CGIHTTPServer
import BaseHTTPServer
class Handler(CGIHTTPServer.CGIHTTPRequestHandler):
    cgi_directories = ["/htbin"]
PORT = 80
httpd = BaseHTTPServer.HTTPServer(("", PORT), Handler)
print "serving at port", PORT

* you need to have python installed...


parsing a string with yacc

yacc, being of unix heritage, parses FILEs. On occasion it's nice to parse strings: one can faff with popen(), which seems to suffer from race conditions when I try it, or write to a file and fopen() it. However whilst idly rtfming I came across yy_scan_string.

This is defined in lex.yy.c; however, lex doesn't give you any header files, so doing


gives any respectable compiler the heebie jeebies. Trying to fish out the heavily #ifdefed function declaration is horrible. A work around is to define a wrapper function in the first, definition, section of your .l file, like so:

void scanThisString(const char *s)

Stick a declaration

void scanThisString(const char *s);

in a header file, include the header in your .l file and wherever you're calling yyparse from, and do something like

    scanThisString("parse me please");

And hey presto!


example sqlite extension code

Not every day something works out of the box: for reasons too long to go into I wanted a pow() function in sqlite. This is easy enough to do using sqlite3_load_extension() within a program, but it's a different story using the SQL load_extension() function.

However, the sqlite wiki has a recipie which worked first time; cook up a shared library,

SELECT load_extension('filename');
and away you go.



pp preprocesses it's input by substituting lines beginning !!PP with the results of the following command.

The manual is here.



csv files get everywhere and sometimes contain data that is better (or just more easily) manipulated in another format.

csvRewrite fills the bill; it turns csv into html, xml, json or sql. Whilst it may not provide the final answer, it can get you a long way down the road.

More here


background-color for svg

Whilst you can style svg with css there is no background-color. You can work round this by first drawing a <rect> and then styling it's fill, as follows:

<?xml version="1.0" standalone="yes"?>
<?xml-stylesheet type="text/css" href="svgBack.css"?>
<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" 
<rect id="backgroundRect" fill="white" x="0" y="0" width="415" height="90" />

<text x="10" y="20" font-family="Palatino" font-size="12" fill="black" >
rect#backgroundRect is the first thing to be painted, so it is effectively
<text x="10" y="35" font-family="Palatino" font-size="12" fill="black" >
the background for everything that follows.
<text x="10" y="55" font-family="Palatino" font-size="12" fill="black" >
You can now control background colour using the fill property of 
<text x="10" y="70" font-family="Palatino" font-size="12" fill="black" >
rect#backgroundRect in your css.

svgBack.css looks like this:

    fill: #aaa;
which changes the background from the pre-ordained fill="white" to grey.

Example at svgBack.svg


cgi web server in 2 LoC*

import CGIHTTPServer 


Save as or somesuch, knock up an index.html in the same directory, cgi scripts in htbin/ and you're hot to trot.

hg serve was the inspiration for this and if it's good enough for them, it's good enough for me.

The reason for doing such a thing? I'm writing this note on a page served by CGIHTTPServer; when I press the "Write to DB" button this lot ends up in the database. Given a program to generate cgi scripts from a simple specification (I'm reinventing the wheel as we speak) you can do most of your programming without having to faff with a GUI framework. Writing html and styling it beats grinding GUI code out with MFC, JUCE, QT etc etc any day.

* Aha, the magic asterisk. You need python on your PATH. Plus all the other python impedimenta amounting to about 200MB. You may find you've already got half a dozen python installations on your computer, see if you can use one of those!

Don't want to do that? You could have 184MB of Apache plus some messing (make sure you've found the right one) with httpd.conf.

Apache too big? Nginx is a measly 2MB or so; but you've got some configuration to do... And, sadly, doesn't support GCI by design.


css for xml

Whilst mooching about in an out of date version of the css spec, I came across an example of using css with xml.

It works! (note the lack of dtd etc etc, but the concept is just lovely)

I realize I'm something like ten years behind the times here, but I'm not sure this is as well known as it ought to be. This means you can do away with html and have an entire domain specific markup language. In the past I've transformed xml to html with xslt, which is then styled; seemingly no need for that at all - just create a stylesheet.

This is a big time saver, xslt can be truly horrible to wrestle with. No good if you're wanting to do cgi stuff on the server I suppose, nor if you are forced to issue valid html of some description.


Yacc Howto

I started playing with lex and yacc a very long time ago, on the unix command line. They were and are available as windows executables and I've used them for a couple of mini languages, batchgen and zoom. Some time has gone by since then, the compiler I used (vc 6) is old hat now whilst lex and yacc are prehistoric.

One day I found myself thinking 'I need a parser here' but found I'd forgotten most of what I knew, so I wrote it up: have a look here.


Importing a small sql database into couchdb

I've been finding sql a bit frustrating for a while, I found out about couchdb so I had a go.

In order to have something to play with I've invented an sql database (the inevitable blog) and imported it into couchdb. I'm on windows and I'm using sqlite as the DBMS, so YMMV. Please note, this isn't a general solution to the problem of exporting an sql database to couchdb; rather a quick hack which you may be able to adapt to your needs.

If we have the following database:

title TEXT,
content TEXT
INSERT INTO "blog" VALUES(1,'blog the first','This the first in a series designed to illustrate the transfer of a small sql database to couchdb.');
INSERT INTO "blog" VALUES(2,'escape','New

tag TEXT
INSERT INTO "tags" VALUES(1,'inane');
INSERT INTO "tags" VALUES(2,'vacuous');
INSERT INTO "tags" VALUES(3,'banal');

UNIQUE (blogID, tagID),
INSERT INTO "blog_tag" VALUES(1,3);
INSERT INTO "blog_tag" VALUES(1,1);
INSERT INTO "blog_tag" VALUES(2,2);

which we want to import into couchdb, we need to turn it into JSON first.

I've done this by creating two views, the first flattens the database into blog entries, the second turns the blog entries into JSON. So, firstly, we create a view that has one row per blog entry:

CREATE VIEW "flattenedDB" AS
SELECT AS blogid, blog.title, blog.content, '"' || group_concat(tags.tag, '","') || '"' AS tags
FROM blog
JOIN blog_tag ON = blog_tag.blogID
JOIN tags ON blog_tag.tagID =

The crux here is getting the tags into one row. Now for the JSON:

SELECT 1 AS seq, '{
"docs" :
SELECT 2 AS seq,  '{' || '
' || '"blogid" : "' || blogid || '",
' || '"title" : "' || title || '",
"content" :"' || replace (replace(content, x'0D', '\r'), x'0A', '\n') || '",
"tags" : [' || tags || ']
}' ||
CASE WHEN blogid<(SELECT MAX(id) FROM blog) THEN ',' ELSE '' END
FROM flattenedDB
SELECT 3 AS seq, ']

We use seq to top and tail the JSON for the individual blog entries to present a key of "docs" which has a value of an array of blog entries. The beauty of this is that we get our JSON straight out of the DB, we don't have to edit it by hand or mess about with sed.

We extract the JSON into a file like so:

sqlite3 blog.db "select JSON from JSON;" > flattenedBlog.json

which gives us:

"docs" :
"blogid" : "1",
"title" : "blog the first",
"content" :"This the first in a series designed to illustrate the transfer of a small sql database to couchdb. ",
"tags" : ["inane","banal"]
"blogid" : "2",
"title" : "escape",
"content" :"New \r\nlines\r\nmust\r\nbe\r\nescaped\r\n.",
"tags" : ["vacuous"]

and POST it to couchdb with

curl -X POST -H "Content-Type: application/json" -d @flattenedBlog.json

Hey presto! It's in couchdb and you can play with it to your heart's content.



This is pretty much obsolete now, cmd.exe lets you chain commands and define subroutines, but if you're stuck with an ancient version, be my guest.

If you've tried and despaired of implementing boolean logic in a batch file, you need this program. Whilst / cmd.exe is much better than it was when I first wrote this program I still find batchgen handy when things start getting a bit gnarly.

For it to be useful, however, you need to be comfortable with programming and have the ability to understand the grammar of a mini-language, since the error handling is lousy and you need to look at the language spec to have any chance of understanding what's going on. You also need a reasonable knowledge of batch programming; batchgen doesn't quite write it for you.

The big idea is that we use batchgen to do all the flow of control transformations, whilst we enclose normal DOS commands [# rem like this #]. batchgen turns the flow of control statements into legal DOS commands, and copies text enclosed in [##] verbatim.

If we have the following as input:

if (%1==%2 && ! %3==%4 || exist %5)
    [# rem this will be copied verbatim
       @echo it's true #]

batchgen's output is:

@goto main
if "%1" == "%2" goto AT0
goto ORF1
if "%3" == "%4" goto ORF1
goto IF0
if exist %5 goto IF0
goto EI0

     rem this will be copied verbatim
     @echo it's true
rem endif

If you ask me, that's pretty slick, and it saves a lot of unecessary thinking.


Get the zip from sourceforge or here


Adjusting a Casio F91-W

Having screwed the settings on my digital watch more times than enough (I hate it when it beeps every hour on the hour) I wrote the (almost) definitive guide to it's buttons.

virtually hosted by NearlyFreeSpeech