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.

top

renum

renum substitutes numbers in it's input according to a user specified list of computations.

Read the manual.

top

pp

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

The manual is here.

top

csvRewrite

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

top

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" 

  "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">

<svg

 xmlns="http://www.w3.org/2000/svg"

 version="1.1"

 x="0"

 y="0"

 width="415"

 height="90">

<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>

<text x="10" y="35" font-family="Palatino" font-size="12" fill="black" >

the background for everything that follows.

</text>

<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>

<text x="10" y="70" font-family="Palatino" font-size="12" fill="black" >

rect#backgroundRect in your css.

</text>

</svg>

svgBack.css looks like this:

rect#backgroundRect

{

    fill: #aaa;

}

which changes the background from the pre-ordained fill="white" to grey.

Example at svgBack.svg

top

Mercurial: the end of the affair.

hg log
abort: requirement '' not supported!

hg stat
abort: requirement '' not supported!

hg diff
abort: requirement '' not supported!

I emailed the first addy I could find and, surprisingly, got a reply in about five minutes. Apparently I needed to look at my requires file. Compared to the other ones it looked like junk, but I've no idea (nor do I want to know) what should be there.

The mercurial tail was now wagging the programming dog. I'd been trying to do a branch or a clone and something had gone wrong (there again it might have been when I was mucking about with hg serve) and I'd lost patience. I kept reading stuff like

Branch (Noun) A child changeset that has been created from a parent that is not a head. These are known as topological branches, see 'Branch, topological'. If a topological branch is named, it becomes a named branch. If a topological branch is not named, it becomes an anonymous branch. See 'Branch, anonymous' and 'Branch, named'.

For crying out loud. I haven't got time for all that: it's a truckload of cds for me. Added advantages - you've actually got a backup and you save a lot (300MB in my case) of disk space.

top

cgi web server in 2 LoC*

import CGIHTTPServer 

CGIHTTPServer.test()

Save as cgiserver.py 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.

top

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.

top

Viewing mercurial diffs with winmerge

Mercurial is a command line revision control program, evangelized here. Winmerge is diff for windows; if you're not on windows, this is of no interest to you.

If Winmerge is on your %PATH%, editing or creating mercurial.ini (to be found at: XP or older - C:\Documents and Settings\USERNAME\Mercurial.ini, with Vista or later - C:\Users\USERNAME\Mercurial.ini) so that you have a section like so:

[extensions] extdiff =

you can say

hg extdiff -p winmergeu.exe foo.cpp

and look at the diff in glorious techicolor.

Going one step further and inserting

 [extdiff] cmd.winmerge = C:\Program Files (x86)\WinMerge\WinMergeU.exe

in mercurial.ini and then doing

 hg winmerge

diffs everything from hgroot, or you can diff a single file with

 hg winmerge foo.cpp

as before.

top

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.

top

Grammar Markup

One of the problems with yacc is actually finding your way through the grammar, often necessary when trying to get it right. Hyperlinking would solve the problem of going down the grammar but going up can only be done using the back button. It turns out (to my mind anyway) that that's just about good enough.

So here it is: yaccalyzer converts a yacc grammar to html. It's a cgi version of a native MarkupGrammar program.

Thanks to Thomas Boutell for the cgic library.

top

sql injection explained

Short of zapping one of your own databases, this is a good memory jogger.

top

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:

CREATE TABLE blog
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
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
lines
must
be
escaped
.');

CREATE TABLE tags
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT
);
INSERT INTO "tags" VALUES(1,'inane');
INSERT INTO "tags" VALUES(2,'vacuous');
INSERT INTO "tags" VALUES(3,'banal');

CREATE TABLE blog_tag
(
blogID INTEGER,
tagID INTEGER,
UNIQUE (blogID, tagID),
FOREIGN KEY (blogID) REFERENCES blog(id) ON DELETE CASCADE,
FOREIGN KEY (tagID) REFERENCES tags(id) ON DELETE CASCADE
);
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 blog.id AS blogid, blog.title, blog.content, '"' || group_concat(tags.tag, '","') || '"' AS tags
FROM blog
JOIN blog_tag ON blog.id = blog_tag.blogID
JOIN tags ON blog_tag.tagID = tags.id
GROUP BY blog.id
ORDER BY blog.id ASC;

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

CREATE VIEW "JSON" AS
SELECT 1 AS seq, '{
"docs" :
[' AS JSON
UNION
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
AS JSON
FROM flattenedDB
UNION
SELECT 3 AS seq, ']
}' AS JSON
ORDER BY 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 http://127.0.0.1:5984/blog/_bulk_docs -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.

top

Batchgen

If you've tried and despaired of implementing boolean logic in a batch file, you need this program. Whilst command.com / 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
:main
if "%1" == "%2" goto AT0
goto ORF1
:AT0
if "%3" == "%4" goto ORF1
goto IF0
:ORF1
if exist %5 goto IF0
goto EI0
:IF0

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

if we ask for optimization we get:

if not "%1" == "%2" goto ORF1
if not "%3" == "%4" goto IF0
:ORF1
if not exist %5 goto EI0
:IF0
     @echo it's true
:EI0

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

Download

Get the zip from sourceforge or here

top

Command Line Graphics

This is a suite of programs giving scriptable access to web quality graphics functions; this solves the problem of snipping fixed bits out of images programatically.

If you're looking for high performance, you need GIMP or Adobe.

If you're on linux, the netpbm suite probably does more than you'll ever need.

There are four programs:

shrink

change image size

snip

cut rectangle out of image

stick

join images together

zoom

a virtual rostrum camera

Download

Get the zip from here.

top

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.

top