How to do complex selections

From: John Verbrugge <[email protected]> - 30 Nov 1998

 > [...] wondering how to "search" a database. I'm quite familiar with
 > SQL syntax, which allows me to select records which are "like" some
 > criteria.

Metakit does not offer the level of non-procedural query capabilities of SQL. Not directly. On the other hand, it is highly optimized towards scanning for results. So much so that indexing techniques are far less often needed than you would expect. On my PII/400 machine, linear scans through integers and short strings take place place at roughly 1 million entries per second (on a new - unreleased - version, that is). The main concept to keep in mind is that Metakit stores data column-wise, so a scan through one or two properties is trivial.

That means that this "dumb" loop is extremely efficient:

         for each i in 0..<last-row> do
                 if row.prop1 matches criterium1 etc...
                         then store i (or process the row)

People usually do not realize, or even accept, this. My only reply is: do a few test and see how your loops work out in practice. There is a whole bag of tricks in case the performance really is not good enough for you - but in Metakit, the trick is to try the dumb way first.

 > I want to be able to search my database for all records that contain
 > a specific word. The database layout is very simple:
 > book_id, chapter_id, paragraph_id, paragraph_text.
 > Does anyone know how I would even approach this?

If using the scripting language "Tcl" is an option (or Python, in a few weeks), then check out this page:

         https://www.equi4.com/metakit/info/README-Tcl.html

It contains a reasonably powerful general selection operator, which implements searches - including regular expressions and keywords, btw.

Here's an example (untested, but basically complete) of such a search:

        package require mk4tcl
        mk::file open db myfile.dat -readonly
        set fields [list book_id chapter_id paragraph_id paragraph_text]
        foreach i [mk::select db.myview -keyword $fields mytext] {
                puts [mk::get db.myview!$i]
        }
        mk::file close db

The underlying code does nothign other than scan through all rows and compare each of the fields in turn to the "mytext" text string.

-- JC