Section 10.4. Connecting to External Databases

10.3. Using KirbyBase

KirbyBase is one of those little libraries that everyone should learn to use. At present it is not a standard library packaged with Ruby; if it were, its usefulness might increase even more.

KirbyBase is the work of Jamey Cribbs (apparently named after his dog). Although it is in many ways a full-fledged database, it is mentioned here rather than along with MySQL and Oracle for a few reasons.

First, it is not a separate application. It is a pure-Ruby library and is not usable without Ruby. Second, it doesn't know SQL at all; so if you are dependent on SQL for some reason, it isn't for you. Third, if your application is sophisticated enough, you may have issues with KirbyBase's functionality or its speed.

Having said all that, there are numerous reasons to like KirbyBase. It is a single-file pure-Ruby library with zero installation/configuration overhead. It works across platforms, and its data files are interchangeable across those platforms. It is a "true" database in the sense that the data are not all held in memory at once.

It is easy to use and has a Rubyesque interface with a little DBI flavor. In general, a database corresponds to a directory and each table to a file. It produces tables that are human-readable (and editable), or optionally these can be encrypted (only enough to discourage editing). It is aware of Ruby objects and can store and retrieve objects intelligently.

Finally, it can operate in distributed mode thanks to a dRuby interface. It is as simple to access KirbyBase data from a remote machine as from a local one.

To open a database, you specify first whether it is local; the next two parameters are typically nil, and the fourth specifies the directory in which database files will be kept (defaulting to the current directory).

To create a table, call create_table on the database, passing in a table name (as a Symbol); the name on disk will be based on this name. Then pass in a series of pairs of symbols, indicating the field names and types.

require 'kirbybase' db = KirbyBase.new(:local, nil, nil, "mydata") books = db.create_table(:books,             # name of table                         :title, :String,    # field, type, ...                         :author, :String)

The field types currently recognized by KirbyBase are String, Integer, Float, Boolean, Time, Date, DateTime, Memo, Blob, and YAML. By the time you read this, there may be others.

To insert into a table, call its insert method. You may pass in a list of values, a hash, or any object that responds to the given field names.

books.insert("The Case for Mars","Robert Zubrin") books.insert(:title => "Democracy in America",              :author => "Alexis de Tocqueville") Book = Struct.new(:title, :author) book = Book.new("The Ruby Way","Hal Fulton") books.insert(book)

In every case, the insert method returns the row id of the new record (which you may use or ignore). This is a "hidden" autoincrement field that is present in every record of every table.

Records are selected with the select method. With no parameters at all, it simply selects every field of every record in the table. The fields may be limited to certain ones by specifying symbols as parameters. If a block is specified, it is used to determine which records to select (much as the find_all method works for an array).

list1 = people.select               # All people, all fields list2 = people.select(:name,:age)   # All people, name/age only list3 = people.select(:name) {|x| x.age >= 18 && x.age < 30 } # Names of all people 18 to 30

Any operation can be performed in the code block. This means that, for example, you can query using Ruby regular expressions (unlike the typical SQL-oriented database).

A KirbyBase resultset can be sorted by multiple keys, ascending or descending. To sort descending, specify a minus before the key name. (This works because Symbol has a unary minus method added to it.)

sorted = people.select.sort(:name,-:age) # sort ascending by name, descending by age

A resultset also has the interesting feature that it can provide arrays that "crosscut" the results. This is slightly tricky to understand at first.

Suppose that we have a resultset composed of people, where each element stores the name, age, height, and weight. Naturally the resultset can be indexed like an array, but it also has methods named the same as the field names, which store arrays of values for just those field names. For example:

list = people.select(:name,:age,:height,:weight) p list[0]          # All info for person 0 p list[1].age      # Age for person 1 p list[2].height   # Height for person 2 ages  = list.age   # Array: Ages for all people names = list.name  # Array: Names for all people

KirbyBase has some limited report-printing features; just call to_report on any resultset. Here is an example:

rpt = books.select.sort(:title).to_report puts rpt # Output: # recno | title                | author # ---------------------------------------------------- #     2 | Democracy in America | Alexis de Tocqueville #     1 | The Case for Mars    | Robert Zubrin #     3 | The Ruby Way         | Hal Fulton

The encrypt accessor on a table can be set to true to prevent the table from being readable/editable as plain text. Be aware that it is based on a Vigenere ciphernot exactly a "toy" but not cryptographically secure. Therefore use this feature only as a way to discourage editing; don't use it as a way to hide sensitive data. You would typically set this using a block when the table is created:

     db.create_table(:mytable, f1, :String, f2, :Date) {|t| t.encrypt = true }

Because remote access is an interesting feature, we'll mention briefly how it works. A sample server looks like this:

require 'kirbybase' require 'drb' host = 'localhost' port = 44444 db = KirbyBase.new(:server)   # Create an instance of the database. DRb.start_service("druby://#{host}:#{port}", db) DRb.thread.join

This is a straightforward application of dRuby (refer to Chapter 20, "Distributed Ruby"). On the client side, you would specify :client rather than the usual :local when you connect to the database.

db = KirbyBase.new(:client,'localhost',44444) # All other code remains the same.

It's also possible to perform the usual operations you would expect: Update or delete a record, drop a table, and so on. In addition there are more sophisticated features I don't mention here, such as one-to-many links, calculated fields, and custom record classes. Consult the KirbyBase documentation on RubyForge for more details.