Fixed-length Database Query engine

This is portable Common Lisp library which is to enable subset of SQL-like operations over DBs with fields/columns with fixed number of ASCII bytes. It’s extensible but so far supports only flat file DBs.

This is not a DB engine, each query is treated on its own without persistent caches, indexes etc. That said, it tries to generate near optimal specialized code which is then fed to the compiler before getting run. See Uniform Structured Syntax, Metaprogramming and Run-time Compilation.

Declare DBs

The defspec function is to be used to register a DB with respective schema. Here’s how the article example would look like:

(defspec 'recordS5         ;db identifier symbol
         :file            ;db type, currently only :file is supported
         ;; declare list of fields in order, offsets are automatically assigned
         '(2                             ;2 unnamed filler bytes
           (cxr 2)                       ;named field with 2 bytes
           (type 1) (subcode 3) (date_eff 6) (date_disc 6) 20
           (commercial_name 16))
         :path "/path/to/records5.db")  ;filename

If a DB uses 2 bytes for end of line, 1 extra filler byte should be declared.

Operations

Here’s variation on the article example:

(select (cxr subcode commercial_name date_disc) ;list of selected fields
        recordS5                                ;db name
        :where (and (like cxr "YY|XX")          ;where clause
                    (or (and (like COMMERCIAL_NAME "PET")
                             (= type "C"))
                        (and (= type "F")
                             (= commercial_name "MEAL")))
                    (< "180620" date_disc)
                    (< date_eff date_disc))
        :jobs 4                         ;parallelize
        :print nil)       ;return array of results instead of printing

The select macro is meant for REPL usage. There’s also analogous select* function which is targeted for programmatic use.

There are also cnt and cnt* which just count matched entries.

Extensibility

Add storage types

While only file DBs are currently supported, there should be enough machinery to allow extending it to other storage types without too much work. Of course as long as the fixed length format is kept. Here’s what has to be done:

• create new class which inherits spec adding whatever additional information is needed
• implement appropriate defspec method
• implement appropriate gen-do-lines method over this new spec class

That is the minimum. You’ll have to check if the default implementations of gen-select and gen-cnt suit your gen-do-lines implementation. The default implementations expect fully populated string line.

As an example for customization, have a look at storage-file.lisp where the file db implementation takes advantage of additional byte buffer (while mostly ignoring the string line) and because of this adds method specializations.

Add operations

While only subset of select-like functionality is currently present, there are some useful building blocks to help building other operations. Here’s what’s available:

• gen-do-lines provides the generic abstraction to iterate over db entries
• gen-where translates WHERE tree to concrete code; it’s meant to be used within the body of gen-do-lines

Look at gen-cnt for a simple example or gen-select for a more complex one.

Future TODO

SQL IN operator

Embedded statements

Joins

Group by

Order by