Audrey

PLEASE NOTE: Audrey is in the very early stages of development. You're welcome to try it out, but it's not ready for prime time yet.

Audrey is an easy, yet powerful database system. With Audrey you can create your database and start using it in a few lines of code.

Install

gem install audrey

Basic usage

By default, Audrey uses SQLite for storage, so the only dependencies are SQLite, which is standard on most Unixish systems, and this gem. To create and start using an Audrey database, all you have to do is open it with Audrey.connect, giving a path to a database file and a read/write mode - 'rw', 'r', or 'w'. The file doesn't need to already exist; it will be created automatically as needed.

require 'audrey'
path = '/tmp/my.db'

Audrey.connect(path, 'rw') do |db|
end

It its simplest use, use the db object as a hash to store information:

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'
    db['antagonist'] = 'Loki'
    db['score'] = 1.3
    db['ready'] = true

    db.each do |k, v|
        puts k + ': ' + v.to_s
    end
end

That gives us this output:

hero: Thor
antagonist: Loki
score: 1.3
ready: true

Audrey can store complex structures such as nested arrays and hashes.

Audrey.connect(path, 'rw') do |db|
    db['people'] = {}
    people = db['people']

    people['fred'] = {'name'=>'Fred'}
    people['mary'] = {'name'=>'Mary', 'towns'=>['Blacksburg', 'Seattle']}

    people['mary']['friend'] = people['fred']

    puts db['people']['mary']['name']
    puts db['people']['mary']['towns'].join(', ')
    puts db['people']['mary']['friend']['name']
end

Take note of the line that reads people['mary']['friend'] = people['fred']. Audrey doesn't use foreign keys. Instead, you simply link objects directly to each other. So in this case, fred is an element in the people hash, but it is also an element in Mary's hash with the key friend. Objects can be linked in this free form manner without the need for setting up lookup tables or defining foreign keys.

Custom classes

Audrey provides a system for defining your own classes. In this way, you can define your own classes, using their properties and methods as usual. Objects of those classes are automatically stored in the Audrey database and can be retrieved, as objects, for use.

Consider, for example, this simple class:

class Person < Audrey::Object::Custom
    self.fco = true
    field 'first'
    field 'middle'
    field 'surname'
end

This class inherits Audrey::Object::Custom, which almost all of your Audrey classes should do.

The next line, self.fco = true, is important. Every Audrey class must be set at self.fco = true or self.fco = false. fco mean "first class object". When the database is closed, objects that are not first class objects, and are not descended from first class objects, are purged. Every custom class must be explicitly set with fco=true or fco=false. Think of clases withfco=false as being "cascade delete".

The next few lines define fields for a person record, first, middle and surname.

So we can use our class like this:

Audrey.connect(path, 'rw') do |db|
    mary = Person.new()
    mary.first = 'Mary'
    mary.middle = 'F.'
    mary.surname = 'Sullivan'

    fred = Person.new()
    fred.first = 'Fred'
    fred.middle = 'C.'
    fred.surname = 'Murray'
end

Later we're going to need to find the Person records. The simplest way to get to them is to each them with the Person class itself:

Audrey.connect(path, 'rw') do |db|
    Person.each do |person|
        puts person.first
    end
end

Notice that there is no need to reinstantiate the objects using data from the database. Audrey automatically creates the objects from the stored data, and makes them available for use as they were originally created.

The example above produces this output:

Fred
Mary

Under the hood, each uses a type of query called q0. Later we'll look at more details about how you can use Q0.

Subclassing

Like any Ruby class, you can subclass your Audrey classes. For example, let's subclass Person with Guest, and subclass Guest with Preferred:

class Guest < Person
    field 'stays'
end

class Preferred < Guest
    field 'rating'
end

Notice that we didn't bother to set these subclasses as first class objects -- they inherited that property from Person. We also added a field to each of our new subclasses. Now we can add Guest and Preferred objects to the database:

Audrey.connect(path, 'rw') do |db|
    dan = Guest.new()
    dan.first = 'Dan'
    dan.stays = 10

    pete = Preferred.new()
    pete.first = 'Pete'
    pete.stays = 12
    pete.rating = 'gold'
end

Because Guest and Preferred derive from Person, we can iterate through all of the records using Person, including objects from its derived classes.

Audrey.connect(path, 'rw') do |db|
    Person.each do |person|
        puts person.first
    end
end

which produces this output:

Fred
Pete
Mary
Dan

Autocommit and transactions

In all the examples so far, data has been written to the Audrey database automatically as it has been produced. However, you might want to only atomically commit data at specified points. You can do this using the autocommit feature, or transactions.

autocommit

To keep Audrey from automatically writing data, use the autocommit option in connect, like this:

Audrey.connect(path, 'rw', 'immediate_commit'=>false) do |db|
    db['hero'] = 'Thor'
end

In the example above, the data is never committed by the end of the session, so if we try to retrieve the data:

Audrey.connect(path, 'rw') do |db|
    puts 'hero: ', db['hero']
end

... we get this disappointing output:

hero:

To commit, simply use the commit method:

Audrey.connect(path, 'rw', 'immediate_commit'=>false) do |db|
    db['hero'] = 'Thor'
    db.commit
end

Which will give us more fulfilling results:

hero:
Thor

Any time during the session you can use rollback to rollback to the previous commit or to the state of the database when it was opened. So this code:

Audrey.connect(path, 'rw', 'immediate_commit'=>false) do |db|
    db['antagonist'] = 'Loki'
    db.rollback
    puts 'antagonist: ', db['antagonist']
end

... will output without Loki:

antagonist:

transaction

For more fine-grained control of when data is commited, you might prefer to use transaction. Any time during a database session you can start a transaction block. Changes to the database inside that block are not committed without an explicit commit command. For example, consider this code::

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'

    db.transaction do |tr|
        db['antagonist'] = 'Loki'
    end

    db.each do |k, v|
        puts k + ': ' + v
    end
end

The database session is set to automatically commit data as it is created (because autocommit defaults to true). However, when we use db.transaction to start a transaction block. Within that block, data is not automatically committed. So the output for this code will look like this:

hero: Thor

Inside a transaction block, you can commit by calling the transaction's commit method:

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'

    db.transaction do |tr|
        db['antagonist'] = 'Loki'
        tr.commit
    end

    db.each do |k, v|
        puts k + ': ' + v
    end
end

That gives us this output:

hero: Thor
antagonist: Loki

Rollback transactions with the rollback method. For example, in this code we use both rollback and commit:

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'

    db.transaction do |tr|
        db['antagonist'] = 'Loki'
        tr.rollback
        db['ally'] = 'Captain America'
        tr.commit
    end

    db.each do |k, v|
        puts k + ': ' + v
    end
end

In that example, we set db['antagonist'] = 'Loki'. But in the next line we roll it back. Then in the next two lines we set db['ally'] = 'Captain America' and commit it. The result is that antagonist is never committed but ally is, producing this output:

hero: Thor
ally: Captain America

Transactions can be nested. For example, in this code, the outer transaction is committed, but not the inner transaction:

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'

    db.transaction do |tr1|
        db['antagonist'] = 'Loki'

        db.transaction do |tr2|
            db['ally'] = 'Captain America'
        end

        tr1.commit
    end

    db.each do |k, v|
        puts k + ': ' + v
    end
end

That gives us this output

hero: Thor
antagonist: Loki

If an inner transaction is committed, but not the outer transaction, then nothing in the inner transaction is finally committed. So, for example, consider this code:

Audrey.connect(path, 'rw') do |db|
    db['hero'] = 'Thor'

    db.transaction do |tr1|
        db['antagonist'] = 'Loki'

        db.transaction do |tr2|
            db['ally'] = 'Captain America'
            tr2.commit
        end
    end

    db.each do |k, v|
        puts k + ': ' + v
    end
end

In that example we commit tr2. But tr2 is nested inside tr1, which is never committed. Therefore everything inside tr1 is rolled back, giving us this output:

hero: Thor

Exiting database connections and transactions

You might find that in some situations you don't need to continue a transaction, or even an entire database connection, if certain conditions are met. For example, suppose you want to add a record using web parameters, but only if the surname is given. You might do that like this:

Audrey.connect(path, 'rw') do |db|
    db.transaction do |tr|
        person = Person.new()
        person.surname = cgi['surname']

        if not person.surname
            tr.exit
        end

        person.first = cgi['first']
        person.middle= cgi['middle']
        puts 'commit'
        tr.commit
    end
end

In this example, if no surname is given, the transaction stops when it hits tr.exit. Nothing else in the transaction after that line is run.

You can also exit an entire database session with db.exit. So, using the same business rules as above, you might code your database connection like this:

Audrey.connect(path, 'rw') do |db|
    if not cgi['surname']
        db.exit
    end

    person = Person.new()
    person.surname = cgi['surname']
    person.first = cgi['first']
    person.middle= cgi['middle']
end

Queries with Q0

Audrey provides a query system called Q0. With Q0 you can perform basic queries on objects, searching for by class and by field value.

Q0 will not be the only query language

Before we go further, it's important to understand what Q0 is not: it is not the only query language that Audrey will ever have. One of the problems that database systems often have is that their query languages becomes more and more convoluted as needs evolve. SQL is a good example. What started as a simple system with English-like syntax evolved into a bizarre language with all manner of join and function syntaxes. So, instead of assuming that we can invent a query language that will always provide all needs, Audrey is designed to allow for multiple query languages. As needs evolve and Q0 becomes unsuitable for advanced needs, new query languages can be invented and added into the Audrey system.

Audrey will always have Q0. As long as they don't create problems with backward compatibility, new features can be added to Q0.

Search by class

Let's start with a simple example. In the following code, we create a Q0 object with db.q0. We tell the query to look for everything in the Person class. Then we each through the results:

Audrey.connect(path, 'rw') do |db|
    query = db.q0
    query.aclass = Person

    query.each do |person|
        puts person.surname
    end
end

Note that to search by class we use fclass (for Audrey class), not just class. There are several reasons for this. First, the class property is already taken. Second, as Audrey grows and is implemented by languages besides Ruby, it will be necessary to distinguish because a Ruby class and an Audrey class. Audrey classes have the same names as Ruby classes, but other languages, such as Python, use a different naming scheme for classes.

The example above is functionally identical to one of the earlier examples in which we use the Person class itself to search for records:

Audrey.connect(path, 'rw') do |db|
    Person.each do |person|
        puts person.first
    end
end

Search by field values

Now we're going to filter by the values of fields. In this example, we set the query to look for records in which the object's first field is "Mary".

Audrey.connect(path, 'rw') do |db|
    query = db.q0
    query.aclass = Person
    query.fields['first'] = 'Mary'

    query.each do |person|
        puts person.surname
    end
end

To search for multiple possible values of a field, use an array that contains all possible values:

query.fields['first'] = ['Mary', 'Fred']

To search for records in which the field is nil or missing, use nil:

query.fields['first'] = nil

To search for any value, as long as it is not nil, use the query's defined method:

query.fields['first'] = query.defined

You can mix and match these options in an array:

query.fields['first'] = ['Mary', nil]

Count

In addition to looping through query results, you can also get just a count of how many objects are found. Simply use the query's count method:

puts query.count()

Other query filters

Currently, Q0 only allows you to search by fclass and field values. More filters will be added as Audrey develops.

Speed

I haven't done any benchmark tests on Audrey yet. I would be very interested to see some if anybody would like to contribute in that way.

That being said, Audrey is probably not currently very fast. Keep in mind, though, that it's worthwhile to balance execution speed against development speed. Audrey, in its current implementation, probably isn't particularly fast in execution, but it allows you go from no project to working project faster than most database systems. Consider the tradeoff.

The name

Audrey is named after the character Audrey in Shakespeare's As You Like It.

Author

Mike O'Sullivan mike@idocs.com

History

version	date	notes
0.3	Feb 12, 2020	Initial upload.
0.3.1	Feb 12, 2020	Minor fixes to documentation.
0.3.2	Feb 14, 2020	Minor fixes to documentation. Minor code cleanup.