Working with records / data rows

Working with relational databases, in majority of applications, comes down to working with rows of data, also known as records. Therefore, for a library used in the database-abstraction layer , whether it wants to be called an ORM or not, it is most important to make using these records as comfortable as possible.

The most commonly used standard for such libraries is DBAPI (currently v2.0). It’s working, it’s complete, optimized and it has a well designed, widely accepted interfaces. The problem is, that to run a database task using DBAPI, you usually have to:

1. Create a cursor
2. Execute the query
3. Iterate over the cursor
4. Extract data from the cursor for each row
5. Run a seperate query to, for eg. update related data.
6. Close the cursor

This becomes tedious in larger applications, which start to look like majority of their code is related to data fetching / saving.

ORMs on the other hand provide a simpler mechanism:

1. Execute the query
2. Use/modify the returned objects

What is hidden from you is the fact that for each row you want to work with, the ORM has to instantiate a class, and fill its attributes - something you’d have to do one way or another.

XRecord provides you with two alternative ways to run database queries. The first - using basic record objects, is more suited for running complex SQL queries and working with the results. The functions used for this method, are the documented methods of XRecordDatabase, that have Object in their name. The objects they return have no reference to the database, table or row they came from, cannot be saved, updated or deleted without writing additional SQL. They also do not follow intra-table relationships. In fact, the only difference between them and the return values from DBAPI queries is possibility of accessing values via object attributes, and that there is no need to create and use a cursor object.

The second way XRecord lets you access data is the reason why we call it an ORM. The functions using this method are the ones with names starting with ‘X’. These functions return instances of classes derived from the XRecord class. Each such instance represents a row of data in a specified table, and can easily fetch referenced rows, child rows and rows related via many-to-many relationships. You may update, delete and insert rows without writing a single line of SQL.

The XRecord subclasses for each table are generated on-the-fly from the metadata in your RDBMS. It means you have to specify all the primary and foreign keys in your DDL scripts. More about this can be found in the Handling meta-data section.

The XRecord subclasses may be further extended to provide a richer object interface to your data.

Basic record objects - Record

class xRecord.Record(**kwargs)

Simple container object, for storing rows of database data in a serializable form. Objects of this class are returned by XXXObject, methods of XRecordDatabase. This is the simplest possible ORM - it takes whatever is returned by a query, looks at the signature and creates objects on the fly.

XRecord.Serialized also returns an instance of this class, since it’s easily processed by most common python serializers.

Attributes (column values) may be accessed like attributes and dictionary items alike:

>>> for r in db.ArrayObject ( "SELECT * FROM blog_entry" ):
...    print r.title, r.author
...    print r['title'], r['author']
...
Article 1  1
Article 1  1
Article 2  1
Article 2  1

Active record objects - XRecord

class xRecord.XRecord(*args, **kwargs)

Base class for all XRecords (active records).

There numerous ways to instantiate an XRecord:

>>> e1 = db.XRecord("blog_entry", 1)
>>> e2 = db.Manager.blog_entry(1)
>>> assert e1 == e2
>>> e3 = db.XSingle("blog_entry", "SELECT * FROM blog_entry WHERE id=1" )
>>> e4 = db.XSingle("blog_entry", "SELECT *, CONCAT('<h1>', title, '</h1>') as html_title FROM blog_entry WHERE id=1")
>>> assert e3 == e4
>>> print e4.html_title
<h1>Article 1</h1>

fetch(*args, **kwargs)

Fetch a row of data to this record. May raise XRecordDatabase.NotFound.

>>> e = db.XRecord("blog_entry")
>>> e.Fetch(1)
>>> print e
<xrecord::blog_entry(1)>

Parameters:*args –

primary key value of the row, as unnamed arguments

Returns:nothing

reload()

Fetch this record’s data again, losing all changes made since last Save/Fetch.

Returns:nothing

save()

UPDATE the database with this record’s data, or INSERT if the primary key is empty.

Returns:number of affected rows, should by 1 or 0

insert()

INSERT a new row into the database.

delete()

Remove this row from the database. The row must be Fetched or otherwise initialized prior to this.

Returns:number of affected rows, should be 1 or 0 (if row was already deleted)

nullify()

Make this record NULL (containing no data).

serialized(depth=1)

Generate a simple Record object with this records data, following foreign keys, children references and mtm references up to the given depth.

The references must be fetched prior to the call to this function.

Parameters:depth – the maximum recursion depth Returns:a serializable representation of self Return type:Record

PK

A tuple containing this records primary key value.

Table

Name of the table this record belongs to

SCHEMA

The XSchema object this record was derived from.

Extending XRecord

When XRecord subclasses are generated from meta-data, they provide a set of basic functions for working with records of a specified table (described above). It is also possible to further subclass them to extend data row objects with custom functionality. An example:

@db.CustomXRecord("blog_entry")
class blog_articles:
      def __repr__(self):
          return "Entry: '" + self.title + "'"

      def last_comments(self, number=10):
          return self.DB.XArray ("comment",
              "SELECT c.* FROM comment WHERE entry=? ORDER BY when DESC LIMIT ?",
              (self.id, number) )

      @classmethod
      def last_entries(cls, number=10):
          return self.DB.XArray ( "blog_entry",
              "SELECT * FROM blog_entry ORDER BY when DESC LIMIT ?",
              (number, ) )

What we’ve done here is we customized the blog_articles class, so that each subsequent instance will have a custom string representation, and will provide a last_comments method to fetch a given number of most recent comments. We also added a class method, to fetch an array of a given number of most recent blog entries.

Now we may use the new functions like this:

>>> e = db.XRecord("blog_entry", 1)
>>> print e
Entry: 'Article 1'
>>> print e.last_comments(2)
[<xrecord::comment(2)>, <xrecord::comment(3)>]
>>> print db.Manager.blog_entry.last_entries (2)
['Entry: \'Article 1\'', 'Entry: \'Article 2\'']

The piece of code that makes this happen is the class decorator: db.CustomXRecord. It takes the default class for a given table (blog_entry in this case) and derives a new class which inherits it, together with the decorated class.

For this to work the XRecordDatabase object must be instantiated and the connection to the database must be active. Therefore it is recommended that all XRecord subclass customizations be made inside the Intialize method of a XRecordDatabase subclass, like this:

class MyDatabase(XRecordDatabase):
   def Initialize(self):
       @self.CustomXRecord("blog_entry")
       class blog_entry:
           """Do your customizations here"
           pass
       #Or:
       self.CustomXRecord("category") (some_other_class)
   pass