Wednesday, December 28, 2005


I've done quite a bit of work with SQL and there are two things that always occur to me when I'm using it: 1) it's really quite simple in terms of what each command does and how it does it and 2) it is really ugly; it is syntactically inelegant, inconsistent and inefficient. So what should I do? Yes, re-create it or at least a workable sub-set of it!

Lisp should be good for making a working SQL-alike. I can use bottom up design to abstract myself from list access to SQL's set like operators and I can get some practice using macros to replicate SQL's 3i syntax. And it'll give me something to do over the holidays, of course I'm currently unemployed so every day is a holiday! ;(

Layer 1: Basic database structure.
What does a database, table, column, row look like? How can I get tables from a database, columns or rows from a table, fields from a row etc. Here's what I decided on (in my own unique BNF that may or may not bear any resemblance to real BNF)
database -> (database (table*))
table -> (table (column*) (row*))
column -> (column column-name column-number)
row -> (row (datum*))

At this stage I can create items and get at their contents but other than that they are in the same table there is no relation between rows and columns.

Layer 2a: Adding/Removing items.
This layer just adds/removes tables to databases and columns and rows to tables.
It also maintains some basic data consistency like database, table and column name uniqueness and that rows have the correct number of elements for the tables they are added to.
I'm feeling pretty good about this because it allows me to do something that normal database systems generally can't: add and remove columns from tables in a live database while maintaining data consistency.

Layer 2b: Getting and setting data.
Using only table column and row objects (or their unique names) I can get and set at each data item uniquely.

Layer 3: Proto-SQL.
This is where things start to get interesting. I decided to make the SELECT statement and the row eliminator (WHERE clause functionality) return a temporary table. This seems like it could be pretty horrible for performance but it makes my life very easy because all the other operators can accept a temporary table just as easily as one that's connected to a database.
This layer also has the first macro the so called parse-where and my first ever use of eval-when for its sidekick expand-where.
This is also where the first major limitation occurs, consider:
WHERE (string= "COLUMN1" "VALUE1")
In normal SQL column names are double quoted and strings are single quoted. You can't do this (easily) in lisp because of #'quote/' so no database value can have the same value as a database, table or column name. I do plan to get around this using symbols at some point but not right now.

Layer 4: Real SQL-like.
This part is all macros that re-arrange, de-reference and error check the 4 implemented statements: SELECT, INSERT, UPDATE and DELETE-FROM (delete's already taken).
There are some real limitations: no order by, no joins, no sub-selects and some departures from SQL that I think make it better: where clauses are lisp expressions and the set clause for an update statement is a list of column-names and new values.

(in-package :looseql)
(defun test ()
(setf *databases* nil)
(let* ((database (make-database "DB1")))
(add-table (make-table "TABLE1") database)
(add-column (make-column "COLUMN0") (get-table-by-name database "TABLE1"))
(add-column (make-column "COLUMN1") (get-table-by-name database "TABLE1"))
(add-column (make-column "COLUMN2") (get-table-by-name database "TABLE1"))
(add-database database)
(setq *current-db* database)

(print 'inserts)
(insert INTO "TABLE1" VALUES ("INS00" "INS01" "INS02"))
(insert INTO "TABLE1" VALUES ("INS10" "INS11" "INS12"))
(insert INTO "TABLE1" VALUES ("INS20" "INS21" "INS22"))
(insert INTO "TABLE1" VALUES ("INS30" "INS31" "INS32"))
(insert INTO "TABLE1" VALUES ("INS40" "INS41" "INS42"))
(insert INTO "TABLE1" VALUES ("INS50" "INS51" "INS52"))

(print 'inserts-into-columns)
(insert INTO "TABLE1"("COLUMN0" "COLUMN1") VALUES ("INS0" "INS1"))
(insert INTO "TABLE1"("COLUMN1" "COLUMN2") VALUES ("INS1" "INS2"))
(insert INTO "TABLE1"("COLUMN0" "COLUMN2") VALUES ("INS0" "INS2"))

(print 'databases-after-inserts)
(print *databases*)

(print 'select-without-where)
(print (select ("COLUMN1" "COLUMN2") from "TABLE1"))

(print 'select-with-where)
(print (select ("COLUMN1" "COLUMN2") from "TABLE1" where
(or (string= "COLUMN0" "INS0")
(null "COLUMN1"))))

(print 'select-*)
(print (select * from "TABLE1"))

(print 'select-into)
(let (var)
(select ("COLUMN0") INTO (var) FROM "TABLE1"
WHERE (string= "COLUMN0" "INS50"))
(print var))

(print 'update)
(update "TABLE1" SET (("COLUMN1" "UPD1")
("COLUMN2" "UPD2"))
WHERE (not (or (string= "COLUMN0" "INS0")
(string= "COLUMN0" "INS00")
(string= "COLUMN0" "INS10")
(string= "COLUMN0" "INS20"))))

(print *databases*)

(print 'delete-from-with-where)
(delete-from "TABLE1" WHERE (null "COLUMN0"))

(print *databases*)

(print 'delete-from)
(delete-from "TABLE1")

I think that for three days work its not too shabby.
I also think that the name's pretty cool LooSeQL: Lisp SQL, loose because it's not only loosely SQL and also its loosely typed and finally I hear SQL refered to as 'sequel' a lot, I think 'squil' would be more accurate, so SeQL is better because we can all agree that it sounds like 'sequel'. It would be better if I used CLOS then I could say that it was Lisp Object Oriented SeQL, maybe next version.

Get the full source here.

Tuesday, December 20, 2005

New IFS-designer version.

Thanks to Alexey Dejneka for supplying the latest fixes to allow cmucl to benefit from porable sdl:surface type declarations and fix some issues in the README. So following the instructions may actually result in a usable system! I also put the examples back that I forgot to include last time.

The latest version is 0.1.2 and it now requires uffi.

Saturday, December 17, 2005

Fixes for IFS Designer in sbcl.

I can't remember exactly why I stopped using sbcl. It used to be my default lisp. I suspect that when I started using slime I just found that cmucl was easier. I'm happy to report, however, that sbcl works with slime (and has done for a while); I was just to busy/lazy to give it a try.

I released the first version of the IFS Designer that does animation and the first feedback that I get is that it doesn't work under sbcl. After a bit of investigation I found that it actually does work but sbcl was emitting a warning every time I referenced an sdl:surface. An IFS typically requires 1000s of points to be drawn so getting a warning for each was a big performance problem. A little help from sbcl-help and some type declarations and all is well. Get the 0.1.1 version, and thanks Zach, I'll be testing under sb and cmu cl from now on.
Under sbcl the IFS Designer is actually faster than under cmucl now. From not working to faster than my development environment, how amusing.

Thursday, December 15, 2005

IFS Designer animates!

The IFS Designer finally does what I intended from the very beginning. It creates animated IFS fractals YAY! It's not polished but the basic functionality of creation, animation and exporting is there. I've also consolidated it so that the matrix library and the required ltk-goodie are in the 0.1.0 archive. You still have to copy the .asd files to the right place because I have no idea how to work adsf-install. I expect that'll change soon if Peter Seibel and his gardening team has anything to do with it.

The animation functionality it pretty simple: you set the keyframes and the program will generate a smooth set of tween frames. The animation you see above is a result of 6 keyframes. I've added a short animation creation to the tutorial to the README.

I'd love to get some feedback from anyone who installs this even if you hate it, tell me.

And now I'm going to bed, good night.

Thursday, December 08, 2005

Reddit, Linkit, lisp, java and Wesnoth.

Reddit was the one general news source that could be relied upon to have lisp news on the front page. Is it too much to ask that we not alienate ourselves from a great opportunity to reach a bigger audience? I guess it is.
This whole thing makes us lispers look like vindictive idiots, again. I give credit to the Linkit people for doing more than yabbering impotently at one another. It's more than I could manage but Linkit's supposed to be a proof of concept but all it proves is that seven hours isn't enough time.
When I visited Linkit I was surprised to find that voting didn't work. This is, to my mind, one of the three fundamental features of reddit along with karma and user submissions. I know it only took seven hours but ten, twelve or even twenty-four hours to a fully working replica of reddit is a much better claim than seven hours to a broken imitation. Nice logo though.

In other news I recently had to take a java test for employment with a certain company. Java's 'features' irritated me for a while, but then I gave up trying to make java do it my way and bent to java's will. Things went a lot better after that and my code got smaller and prettier. A lesson for all of us I think.

Also, go and play Battle for Wesnoth, its great.

Friday, December 02, 2005

A couple of Ltk goodies.

I've had cause to play with Ltk quite a lot so I thought I'd share the widgets I've made. I wrapped up the BWidget tree control and a couple of other BWidgets that go along with it. The BWidget tree control works reasonably well, supports drag and drop and all the usual tree stuff so there it is. Enjoy.

The other widget is one I build from scratch. It's an animation controller widget. It supports multiple draggable frame and keyframe pointers and user controlled progress bars as well as bars that synch with frame generation. It's all CLOS so to use it all you have to do is inherit from frame and keyframe, override the frame creation methods and it'll do the rest. Nice. I created this one to use with the IFS program so that I can create custom IFS animations and it's quite handy but then I would say that.

So grab them here and run the #'test function in either asdf package for a crumby demo.

I other news I've started to learn prolog. I'm reminded of when I first started to learn lisp and the time I had to spend to understand the simplest procedure. It's also clear to me that, despite their many differences, if I didn't already know lisp it'd be a lot harder. Recursive unification makes my head hurt but at least I get the recursive part!