3 Using PyORQ

PyORQ contains two packages, pyorq and pyorq.interface. The pyorq package contains the following modules:

pyorq.ptype defines pobject, the base class for all persistent objects, and ptype the metaclass of pobject.
pyorq.pprop defines persistent properties.
pyorq.prel implements the relational algebra.

You can safely import the objects that constitute the public interface of PyORQ (pobject and the persistent property objects), using:

from pyorq import *

The pyorq.interface package contains the interfaces to the database backends. It contains a number of modules:

pyorq.interface.db_base contains the base class which defines the public interface of the database wrappers.
pyorq.interface.nodb contains a database wrapper without persistent store (basically only a cache, used for testing).
pyorq.interface.sql_db contains the base class for SQL databases.
pyorq.interface.postgresql_db contains the PostgreSQL interface.
pyorq.interface.mysql_db contains the MySQL interface.
pyorq.interface.sqlite_db contains the SQLite interface.

3.1 How to write persistent classes

A persistent class must:

derive from pobject, or another persistent class
have an attribute database, referring to an instance of one of the database interfaces
define one or more persistent attributes.

PyORQ supports the following persistent properties for built in python types:

Property Python type default

pint() int 0
pfloat() float 0.0
pstr() str ''
pdate() datetime.date datetime.date(1,1,1)
ptime() datetime.time datetime.time()
pdatetime() datetime.datetime datetime.datetime(1,1,1)

Property	Python type	default
`pint()`	`int`	`0`
`pfloat()`	`float`	`0.0`
`pstr()`	`str`	`''`
`pdate()`	`datetime.date`	`datetime.date(1,1,1)`
`ptime()`	`datetime.time`	`datetime.time()`
`pdatetime()`	`datetime.datetime`	`datetime.datetime(1,1,1)`

You can provide alternative defaults to the constructor of the property objects. The main purpose of defaults is to avoid NULLs in the database.

Note that the datetime properties currently don't support time zones.

This is a valid persistent class:

from pyorq import * 
from pyorq.interface import mysql_db

db = mysql_db.mysql_db(database='testdb')

class myclass(pobject)
    database = db
    a = pint()      # default = 0
    b = pfloat(7.3) # default = 7.3

You use a persistent class just like any other. You can instantiate an object and assign values to the persistent properties. For example:

m = myclass()
m.a = 5
m.b = 22.4

You can store the instance to the database using its commit method. When the instance is committed, it obtains an object identifier (oid).

print m.oid     # prints None
m.commit()     
print m.oid    # print the object identifier

Object identifiers are used internally to relate Python instances with table rows. You can use the identifier to retrieve an object from the database.

m_oid = m.oid
del m
new_m = myclass(oid=m_oid)
print new_m.a, new_m.b     # prints '5 22.4'

The database ensures that at any time there is only one object that corresponds to a given identifier. This means that:

a = myclass(oid=m_oid)
b = myclass(oid=m_oid)
print a is b              # prints True

Of course, the normal way to retrieve instances from the database is through queries (see below).

Persistent classes may contain attributes that refer to other persistent classes, using the property pref(cls). The argument of pref defines which class the persistent attribute refers to. Objects that are assigned to this attribute must be instances of that class, or one of its subclasses.

This is a persistent class with a reference to the previously defined myclass.

class newclass(pobject):
    database = db
    r = pref(myclass)

The default value for a persistent reference is None. You can also provide an instance of the referred class as a default, or, provide a tuple (cls, oid), that will create an instance cls(oid=oid), when an attribute is first read.

For example:

class newclass(pobject):
    database = db
    r = pref(myclass, (myclass, None))

n = newclass()
print m.r

will print the representation of a new instance of myclass, created using myclass(oid=None).

Persistent classes are subject to the following rules:

All persistent classes in a schema must refer to the same database instance
The names of persistent classes and persistent attributes are case-insensitive (The databases use lowercase versions of the names for the table and column names).
Derived classes cannot redefine persistent properties from the base class.
PyORQ does not support multiple inheritance yet, although you can use mixins
You can define constructors (__init__ method), but constructors will not be called when objects are retrieved from the database.
You can define __new__, but the method will not be called, when retrieving objects from the database.

The requirement that all persistent classes must refer to the same database instance is best satisfied by using some sort of singleton pattern. I prefer using modules for that. For example:

# module mydb
from pyorq.interface.postgresql_db import postgresql_db
db = postgresql_db(database='mydb')

which can then be used in the different modules that define my database schema as:

from pyorq import *
from mydb import db

class Thing(pobject):
    database = db
    ...

3.2 How to write queries

To retrieve objects from the database you write queries. The key idea behind our notation for queries is that a class represents the set of all its instances, and that a class attribute that refers to a persistent property says something about all the instances in this set. Hence, queries are expressions with persistent properties as arguments that contain comparisons.

This is a valid query:

myclass.a == 5

This query returns an iterator. For each instance i, yielded by the iterator, isinstance(i, myclass) and i.a==5 is True. If myclass has subclasses, then this query may also return instances of those subclasses. The constraint isinstance(i, myclass) implies that i may be an instance of a subclass of myclass.

Queries understand references.

This query

newclass.r.a == 5

yields instances i for which isinstance(i, newclass) and i.r.a==5 is True. Implicitly, this means that isinstance(i.r, myclass) is also True.

The query is equivalent to the generator:

def f()
    for m in myclass.a == 5:
        for n in newclass.r == m:
            yield n

Note that comparison of persistent instances (as in newclass.r == m) implies comparison by identity (i.e.: is).

Also note that if a class refers to a class with many subclasses, then the query will have to consider many possible relations between referring object and referred objects.

Queries use the bitwise operators ~, |, and & to represent the logical operators not, or, and and respectively. Note that bitwise operators have a higher precedence than comparison operators (contrary to logical operators). Make sure that you properly parenthesize the terms in your queries!

The most important rule in queries is that they should have only one 'free variable'. This is an illegal query:

(A.a == 1) & (B.b == 2)

because it is not clear if this query should return instances of type A or instances of type B.

Queries also understand the arithmetic operators +, -, /, and *. This is a valid query:

(A.x + A.b.y + 17) <= (A.y * A.b.x * 2)