A Micro Database

Author: Carl Sassenrath
Return to REBOL Cookbook

It's easy to create small databases directly in REBOL, and there are a few advantages:

• You don't need to install or understand a large database system.
• It's very fast (but takes more memory as a tradeoff).
• Records can be variable length and can be easily expanded.
• The data file format is portable between all systems that run REBOL.
• If necessary, you can directly edit the database with a text editor.
• It can handle several thousand records without too much delay.

Of course, if you need the features or tools of a larger database, REBOL/Command provides an interface to a number of commercial database systems. See the documentation for REBOL/Command.

The basic idea behind a REBOL database is to create a block of blocks (a table in database terms) that you save to file. The database structure is:

    [ record-1 ]
    [ record-2 ]
    [ record-3 ]

Each record consists of one or more REBOL values. These values can be strings, numbers, files, or even blocks.

For example a contact database may have records that consist of these fields:

    fields: [
        name
        company
        email
        phone
        address
        city
        zip
        date
    ]

So, an example database might look like this:

    ["Bob Smith" "ABC" "bob@abc" "123-4567" ...]
    ["Jane Doe" "XYZ, Inc." "jane@xyz" "555-1212" ...]
    ...

Here are some helper functions to load and save your database file:

    load-db: func [file] [
        either exists? file [load/all file][copy []]
    ]

    save-db: func [db file /local out] [
        out: make string! 50 * length? db ; estimate
        foreach record db [
            append out trim/lines mold record
            append out newline
        ]
        write file out
    ]

The first time LOAD-DB is called, it will create a new database. The SAVE-DB function is implemented to make the database file look "pretty" with one line per record.

Now you are ready to write an application that will add a record to your database. This example will request all the fields for a record then add it to your database:

    db: load-db %test.r

    record: copy []
    foreach field fields [
        value: either field = 'date [now][
            ask reform ["Enter" field "value: "]
        ]
        append record value
    ]
    append/only db record

    save-db db %test.r

Note that APPEND/ONLY is used to add the record to the tail of the database. That provides the block in a block structure we described earlier. Don't forget the /only refinement or you will have a problem.

To view all records of the database:

    print-db: func [db] [
        foreach record db [
            print record
        ]
    ]

    print-db db

Here is a function that provides a simple search. It takes a field name (name, email, phone, etc.) and a value to find. It returns all records that match the search.

    find-db: func [db field [word!] value /local result offset] [
        result: copy []
        offset: find fields field
        either offset [offset: index? offset][
            print ["ERROR:" field "does not exist"]
            halt
        ]
        foreach record db [
            if find pick record offset value [
                append/only result record
            ]
        ]
        result
    ]

Here is an example of a search:

    result: find-db db 'company "rebol"
    print-db result

Here is another function that will sort the database according to a specific field:

    sort-db: func [db field [word!] /local result offset] [
        offset: find fields field
        either offset [offset: index? offset][
            print ["ERROR:" field "does not exist"]
            halt
        ]
        db: copy db
        sort/compare db func [a b] [
            if a/:offset = b/:offset [return 0]
            either a/:offset > b/:offset [1][-1]
        ]
        db
    ]

Notice that a copy of the database is returned, so the actual database (as saved to a file) is not affected by the sort. If you delete the "db: copy db" line above, then the database itself will be sorted.

To sort the database by zip code:

    result: sort-db db 'zip
    print-db result

Adding Keys

If you frequently search a database for specific value, you can speed up searches by adding a key field. The key would be placed before the record block. key-1 [ record-1 ] key-2 [ record-2 ] key-3 [ record-3 ] ... Now you can use the REBOL SELECT or FIND functions for a fast search based on the key value: record: select db 1234 If you make this change, then you must also change where you use FOREACH on the database to account for the fact that each record has two values (a key and a block). Here's the PRINT-DB function as an example: print-db: func [db] [ foreach [key record] db [ print record ] ] Even though the key is not used here, it is provided for proper function of the FOREACH. Note that in most databases, the key would be unique (the only one). However in REBOL, this is actually not required. Here is a function that returns all records of a given key: fast-find-db: func [db key /local result][ result: copy [] while [db: find db key] [ append/only result second db db: skip db 2 ] result ] You can further expand on this structure by allowing multiple keys per record, as long as the second key does not have values that overlap with the first key. key1-1 key2-1 [ record-1 ] key1-2 key2-2 [ record-2 ] key1-3 key2-3 [ record-3 ] ... If you do this, you will need to add another word to the FOREACH function calls, as you did earlier for a single key. foreach [key1 key2 record] db [...] Also, when you use FIND, you will need to also move forward to the record block. fast-find-db: func [db key /local result][ result: copy [] while [db: find db key] [ db: find db block1 append/only result first db db: next db ] result ]