µWeb

The µWeb framework provides a @model@ module with the intention of simplifying database access. The design goal is to provide a rich abstraction that

* takes away the tedious work of retrieving, creating and deleting records

* can load its parent objects automatically if so required

* _does *not* get in the way of the developer_

Making database interaction easier without restricting the abilities of the developer is our main goal. Some default mechanisms make assumptions on the way the database is organized, but these are well-documented, and it's entirely possible to change the behavior of these mechanisms.

{{toc}}

h1. Record

The basic idea of the @Record@ class is that it is a container for your database records, with related records automatically loaded as needed, and custom methods that provide more info, child objects, etc. Outlined below are the default features available, with minimal configuration requirements.

h2. Your first @Record@ class

To create your own @Record@ subclass, nothing is required beyond the class' name. The following example substitutes a complete working example:

<pre><code class="python">

from uweb import model

class Message(model.Record):

  """Abstraction class for messages stored in the database."""

</code></pre>

h2. Loading fields from primary key

The Record class comes loaded with a way to load records from your database using the @FromPrimary@ method. This is a classmethod available on the @Record@ class and all your own subclasses, and when given a connection and primary key value, will load that record from the database. Provided you have a database that looks like this:

<pre><code class="html">

-- TABLE `message`

+----+--------+--------------------------------------------------+

| ID | author | message                                          |

+----+--------+--------------------------------------------------+

|  1 | Elmer  | First message!                                   |

|  2 | Bobby  | Robert'); DROP TABLE Students;--                 |

|  3 | Elmer  | You didn't think it would be this easy, did you? |

+----+--------+--------------------------------------------------+

</code></pre>

You can load data from this table with the following code:

<pre><code class="python">

# The model:

from uweb import model

class Message(model.Record):

  """Abstraction class for messages stored in the database."""

# Using this:

>>> message = Message.FromPrimary(db_conn, 1)

>>> print message

Message({'message': u'First message!', 'ID': 1L, 'author': u'Elmer'})

</code></pre>

h3. Changing the primary key field

By default, @Record@ uses a primary key called @'ID'@. You can change this to any value you like, and @FromPrimary@ will automatically work based on that value, and all other methods and functionality of the class will also use this new definition (deleting, creating and auto-loading from related tables, which are all explained later).

To change the primary key field, create a class with a defined @_PRIMARY_KEY@ class variable:

<pre><code class="python">

from uweb import model

class Country(model.Record):

  """Abstraction class for a country table.

  This class uses the ISO-3166-1 alpha2 country code as primary key.

  """

  _PRIMARY_KEY = 'alpha2'

</code></pre>

h3. Compound primary keys

The µWeb model also supports compound primary keys, with one limitation: @AUTO_INCREMENT@ fields are not supported for creation of the Record, all values need to be provided for it.

Loading values from a compound primary key works by passing a tuple instead of a single value:

<pre><code class="python">

# The model:

from uweb import model

class MonthReport(model.Record):

  """Abstraction class for the monthReport table.

  This is keyed on a composite of both year and month, foregoing the need for a separate AUTO_INCREMENT field.

  """

  _PRIMARY_KEY = 'year', 'month'

# Using this:

>>> report = MonthReport.FromPrimary(db_conn, (2012, 5))

>>> print report

Message({'report': 'Things went really well', 'month': 5, 'year': 2012})

</code></pre>

h3. Class and table relation

By default, the assumption is made that the table name is the same as the class name, with the first letter lowercase. *The table related to the class @Message@ would be @message@.* To change this behavior, assign your own table name to the @_TABLE@ class constant. This new table name will then be used in all built-in Record methods:

<pre><code class="python">

from uweb import model

class Message(model.Record):

  """Abstraction class for messages stored in the database."""

  _TABLE = 'MyMessage'

</code></pre>

Alternatively, you can override the @TableName@ class-method to alter the table-name transformation that is done.

h2. Creating records

To create a record in the database, you can use the classmethod @Create@. This takes the connection and a dictionary of the keys and values that should be inserted into the database. Using the @Message@ class we defined earlier, creating a new record is a relatively simple call:

<pre><code class="python">

>>> message = Message.Create(db_conn, {'author': 'Bob', 'message': 'Another message'})

>>> print message

Message({'message': 'Another message', 'ID': 4L, 'author': 'Bob'})

</code></pre>

*N.B.* Skipping fields that are optional in the database is allowed, but their default values assigned by the database will _not_ be reflected in the object. That is, the record will not be reloaded after storing.

h2. Deleting records

Records can be deleted from the database either from a loaded object, or using the @DeletePrimary@ classmethod. This latter removes the record from the database using the primary key to select it.

<pre><code class="python">

class Message(model.Record):

  """Abstraction class for messages records."""

# Loading and deleting an active record.

>>> bad_record = Message.FromPrimary(db_connection, 3)

>>> bad_record.Delete()

# Deleting a record based on its primary key.

>>> Message.DeletePrimary(db_connection, 2)

</code></pre>

h2. Listing all records

For situations where all records must be retrieved or processed, there is the @List@ classmethod. This takes the connection as argument and iterates over all records in the database:

<pre><code class="python">

class Message(model.Record):

  """Abstraction class for messages records."""

# List all messages:

>>> for message in Message.List(db_connection):

...   print message

... 

Message({'message': u'First message!', 'ID': 1L, 'author': 1})

Message({'message': u"Robert'); DROP TABLE Students;--", 'ID': 2L, 'author': 2})

Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L, 'author': 1})

</code></pre>

h3. Filtering and sorting using the list method:

Possible arguments and default values:

<pre>

      @ connection: object

        Database connection to use.

      % conditions: str / iterable ~~ None

        Optional query portion that will be used to limit the list of results.

        If multiple conditions are provided, they are joined on an 'AND' string.

      % limit: int ~~ None

        Specifies a maximum number of items to be yielded. The limit happens on

        the database side, limiting the query results.

      % offset: int ~~ None

        Specifies the offset at which the yielded items should start. Combined

        with limit this enables proper pagination.

      % order: iterable of str/2-tuple

        Defines the fields on which the output should be ordered. This should

        be a list of strings or 2-tuples. The string or first item indicates the

        field, the second argument defines descending order (desc. if True).

      % yield_unlimited_total_first: bool ~~ False

        Instead of yielding only Record objects, the first item returned is the

        number of results from the query if it had been executed without limit.

</pre>

h3. Examples:

<pre><code class="python">

# List all messages:

Message.List(db_connection, conditions={'author':1}, limit=15)

# list only the first 15 records where the conditions are met.

# List all messages:

Message.List(db_connection, limit=20, offset=10)

# lists records 10 to 30

# List all messages:

Message.List(db_connection, limit=20, offset=10, order=[('ID', True)])

# lists records 10 to 30 after the table is sorted in reverse by ID.

</code></pre>

h2. On-demand loading of referenced records.

In databases that are more complex than a single table (nearly ''all''), information is often normalized. That is, the author information in our previously demonstrated *message* table will be stored in a separate *author* table. The author field on message records will be a _reference_ to a record in the author table.

Consider the following tables in your database:

<pre><code class="html">

-- TABLE `message`

+----+--------+--------------------------------------------------+

| ID | author | message                                          |

+----+--------+--------------------------------------------------+

|  1 |      1 | First message!                                   |

|  2 |      2 | Robert'); DROP TABLE Students;--                 |

|  3 |      1 | You didn't think it would be this easy, did you? |

+----+--------+--------------------------------------------------+

-- TABLE `author`

+----+-------+--------------------+

| ID | name  | emailAddress       |

+----+-------+--------------------+

|  1 | Elmer | elmer@underdark.nl |

|  2 | Bobby | bobby@tables.com   |

+----+-------+--------------------+

</code></pre>

And the following class definitions in Python:

<pre><code class="python">

from uweb import model

class Author(model.Record):

  """Abstraction class for author records."""

class Message(model.Record):

  """Abstraction class for messages records."""

</code></pre>

This makes it possible to retrieve a message, and from that Message object, retrieve the author information. This is done when the information is requested, and not pre-loaded beforehand. This means that retrieving a thousand Message objects will *not* trigger an additional 1000 queries to retrieve the author information, if that information might not be used at all.

<pre><code class="python">

>>> message = Message.FromPrimary(db_conn, 1)

>>> message

Message({'message': u'First message!', 'ID': 1L, 'author': 1})

# This is the same message we saw before, without author information.

# However, retrieving the author field specifically, provides its record:

>>> message['author']

Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})

>>> message

Message({'message': u'First message!', 'ID': 1L,

         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})

</code></pre>

This works on the assumption that *any field name that is also the table name of another Record class, is a reference to that table*. In the case of the example above: The message table contains a field _author_. There exists a Record subclass for that table (namely _Author_, table 'author'). The value of @message['author']@ (=@1@), is now used to load an Author record using the FromPrimary classmethod, with @1@ as the primary key value.

# @message['author']@ uses the _author_ field

# _author_ table is represented by Author class

# @message['author']@ is replaced by @Author.FromPrimary(db_connection, message['author']@

h3. Customize table-references

The auto-loading behavior can be modified using the @_FOREIGN_RELATIONS@ class constant. This provides a mapping that specifies (and overrides) which Record classes should be used to resolve references from fields. The key for the mapping is a field name (string), and the corresponding value can be a class or @None@.

* @None@ specifies that the field does *not* represent a reference, and should be used as-is.

* Classes may be given as string because at the time of evaluation, not all classes exist, and attempting using a class directly might result in a @NameError@. This "class as string" exception only exists for classes that are defined in the same module, and exists so that the model does not force you to define your classes in a certain order. It also enables the case where two tables cross-reference eachother.

The following is an example case where the table names are plural, but the field names are singular:

<pre><code class="python">

from uweb import model

class Author(model.Record):

  """Abstraction class for author records."""

  _TABLE = 'authors'

class Message(model.Record):

  """Abstraction class for messages records."""

  _TABLE = 'messages'

  _FOREIGN_RELATIONS = {'author': Author}

</code></pre>

h2. Loading child objects (1-to-n relations)

The model provides a generic method to retrieve child records (that is, _1 to n_ relations) of a record. The desired relations _should_ have an associated Record class. The method to use is @_Children@, which is a private method of any @Record@ class. As its argument, it needs the name of a child class. Returned is an iterator that yields instances of the given @Record@ subclass. 

Given its name and usage, the suggested usage of this is to wrap a more descriptive method around this:

<pre><code class="python">

from uweb import model

class Author(model.Record):

  """Abstraction class for author records."""

  def Messages(self):

    """Returns an iterator for all messages written by this author."""

    return self._Children(Message)

class Message(model.Record):

  """Abstraction class for messages records."""

# Caller code

>>> elmer = Author.FromPrimary(db_connection, 1)

>>> for message in elmer.Messages():

...   print message

Message({'message': u'First message!', 'ID': 1L,

         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})

Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L,

         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})

# Reflowing to keep things legible

</code></pre>

What you can see here is that all messages written by the given author are retrieved from the database, and presented. This is done with a single database query, where the _child_ Record's table is searched for rows where the @relation_field@ is equal to the parent Record's primary key value. This @relation_field@ is an optional argument to the @_Children@ method, and defaults to the class' table name.

*N.B. @print@ and the methods @(iter)items@, @(iter)values@ all cause the object's foreign relations to be retrieved.*

The same example, this time with pluralized table names:

<pre><code class="python">

class Author(model.Record):

  """Abstraction class for author records."""

  _TABLE = 'authors'

  def Messages(self):

    """Returns an iterator for all messages written by this author."""

    return self._Children(Message, relation_field='author')

class Message(model.Record):

  """Abstraction class for messages records."""

  _TABLE = 'messages'

  _FOREIGN_RELATIONS = {'author': Author}

</code></pre>

h2. Updating a record

After loading a record, it can be altered, and saved. These changes (and optionally changes to nested records), will be committed to the database, and reflected in the current loaded record.

<pre><code class="python">

class Author(model.Record):

  """Abstraction class for author records."""

class Message(model.Record):

  """Abstraction class for messages records."""

>>> retort = Message.FromPrimary(db_connection, 3)

>>> retort['message'] = "Please go away Bobby."

>>> # Our changes are not yet reflected in the database:

>>> print Message.FromPrimary(db_connection, 3)

Message({'message': u"You didn't think it would be this easy, did you?", 'ID': 3L,

         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})

>>> retort.Save()

>>> # Now our changes are committed to the database:

>>> print Message.FromPrimary(db_connection, 3)

Message({'message': u'Please go away Bobby.', 'ID': 3L,

         'author': Author({'emailAddress': u'elmer@underdark.nl', 'ID': 1, 'name': u'Elmer'})})

</code></pre>

To save all changes in related fields, we can provide the named argument *save_foreign* and set it to _True_. This way we could alter both the author name and the message itself in one database transaction.

h2. Comparisons

h3. Equality

Records must pass the following criteria to be considered equal to one another.:

# *Type*: Two objects must be of the same type (class)

# *Primary key*: The primary key values must compare equal

# *Foreign relations*: Foreign relations must be the same. If these are not resolved in one object but are in the other, the primary key of the resolved object will be compared to the data of the other record.

# *Data*: All remaining data fields must be equal and symmetric (i.e. both objects describe the same fields)

h3. Greater / smaller

Comparing two objects with one another to tell their relative order can _only_ be done if they are of the same type. If they are, the comparison is done based on the primary key values of the records. In most cases this will result in an ordering similar to the database-insert order.

h3. Hooks

To facilitate various checks or custom code when records are created, initialized, inserted etc the following hooks are available:

* _PreCreate

* _PreSave

* _PostInit

* _PostCreate

* _PostSave

<pre><code class="python">

from uweb import model

import hashlib

class Author(model.Record):

  """Demonstrate a PreCreate method."""

  def _PreCreate(self, cursor):

    """Sets the API key for the new record and insert the dateCreated value"""

    now = datetime.datetime.utcnow()

    self['dateCreated'] = now

    self['apikey'] = hashlib.md5(str(random.randrange(9999999999))).hexdigest()

</code></pre>

h1. VersionedRecord

The versionedRecord class provides a dual key table, in which there is one primary key (for example a record ID) which looks up the individual versioned records, and an overall key which is used to return the most recent version.

If for example you'd need a table that holds all of your customer details. And you also need to keep older versions of their details available for older invoices, you could used a VersionedRecord to hold this information.

You would use the overall key to load the current / newest customer details, and you would use the specific record ID to keep track of which version of the customer details you need for a specific invoice.

Te VersionedRecord acts much like the Record class.

It introduces:

The method: 'Versions' which lists all the versions for a specific identifier.

The property: 'identifier' which returns the version value for the current record.

h1. MongoRecord