[TransWarp] TableDM proof of concept, request for comments

[John Landahl]

I've added a bit more functionality to the TableDM code seen in my
recent post, and thought it might be worth sharing with the rest of the
PEAK community.  TableDM encapsulates all the basic functionality needed
to load, create, and update records in a SQL database, using mapping
classes to define how to move data between specific properties and
fields, enumerations, or other DMs.

The FieldMap class is used for simple property-to-field mappings, the
EnumerationMap class automates moving data into and out of PEAK
enumerations, and QueryMap and EntityMap provide mapping functionality
for QueryDMs and EntityDMs, respectively.

The Bulletins DM examples could be simplified using TableDM follows:

    class UserDM(TableDM):
        db           = binding.bindTo(DATABASE)
        defaultClass = User

        fieldMap = (
            FieldMap('loginId'),
            FieldMap('fullName'),
            FieldMap('password')
        )

    class BulletinDM(TableDM):
        db           = binding.bindTo(DATABASE)
        CategoryDM   = binding.bindTo(storage.DMFor(Category))
        UserDM       = binding.bindTo(storage.DMFor(User))
        forCategory  = binding.New(BulletinsForCategoryDM)
        defaultClass = Bulletin

        def fieldMap(self):
            return (
                FieldMap('id'),
                EntityMap('category', self.CategoryDM)
                FieldMap('fullText'),
                EntityMap('postedBy', self.UserDM)
                FieldMap('postedOn'),
                EntityMap('editedBy', self.UserDM)
                FieldMap('editedOn'),
                # no equivalent for this yet, perhaps a ValueMap class?
                # hidden = row.hidden <> 0,
            )
        fieldMap = binding.Make(fieldMap)

    class CategoryDM(TableDM):

        db = binding.bindTo(DATABASE)
        BulletinDM = binding.bindTo(storage.DMFor(Bulletin))
        bulletinsForCategory = binding.bindTo('BulletinDM/forCategory')

        defaultClass = Category

        def fieldMap(self):
            return (
                FieldMap('pathName'),
                FieldMap('title'),
                FieldMap('sortPosn'),
                QueryMap('bulletins', self.bulletinsForCategory, 'pathName'),
                EnumerationMap('sortBulletinsBy', SortBy),
                FieldMap('postingTemplate'),
                FieldMap('editingTemplate')
            )
        fieldMap = binding.Make(fieldMap)

See the attached tabledm.py for the code, and feel free to offer any
criticism or suggestions.

John Landahl
john at landahl.org
-------------- next part --------------
from peak.api import binding, storage, PropertyName

DATABASE = PropertyName('db')

class Mapping:
    def __init__(self, property):
        self.property = property

    def writable(self, value):
        return value

class FieldMap(Mapping):
    def __init__(self, property, field=None):
        Mapping.__init__(self, property)

        if field:
            self.field = field
        else:
            self.field = property

    def mapFrom(self, row):
        return self._field

class QueryMap(FieldMap):
    def __init__(self, property, dm, field=None):
        FieldMap.__init__(self, property, field)
        self.dm = dm

    def mapFrom(self, row):
        return storage.QueryLink(self.dm[getattr(row, self.field)])

    def writable(self, value):
        return None

class EntityMap(QueryMap):
    def writable(self, value):
        return self.dm.oidFor(value)

class EnumerationMap(Mapping):
    def __init__(self, property, enumeration, field=None):
        Mapping.__init__(self, property)

        self.enumeration = enumeration
        if field:
            self.field = field
        else:
            self.field = property

    def mapFrom(self, row):
        return self.enumeration(self.field)

    def writable(self, value):
        return hash(value)

class TableDM(storage.EntityDM):
    log = binding.Obtain('logging.logger:storage.TableDM')
    db  = binding.Obtain(DATABASE)

    table    = binding.Require('Name of database table')
    fieldMap = binding.Require('A list to map field names to property names')
    keyField = binding.Require('The field name of the primary key for this table')

    def _load(self, oid, ob):
        row = ~self.db('select * from %s where %s=%%s' % (self.table, self.keyField), (oid,))
        return self.stateFromRow(row)

    def stateFromRow(self, row):
        d = {}
        for mapper in self.fieldMap:
            d[property] = mapper.mapFrom(self, row)
        return d

    def _items(self, ob):
        fields = []
        values = []
        placeholders = []

        for mapper in self.fieldMap:
            value = mapper.writable(getattr(ob, mapper.property, None))
            if value is not None:
                values.append(value)
                fields.append(mapper.field)
                placeholders.append('%s')

        return fields, values, placeholders

    def _save(self, ob):
        fields, values, placeholders = self._items(ob)
        assignments = ','.join(['%s = %%s' % field for field in fields])
        sql = 'update %s set %s where %s = %%d' % (self.table, assignments, self.keyField)
        self.db(sql, values + [ob._p_oid])

    def _new(self, ob):
        if ob.id:
            ob._p_oid = ob.id
        else:
            ct, = ~self.db('select max(%s) from %s' % (self.keyField, self.table))
            ct = int(ct or 0) + 1
            ob._p_oid = ob.id = ct

        fields, values, placeholders = self._items(ob)

        sql = 'insert into %s (%s) values (%s)' % (
            self.table,
            ','.join(fields),
            ','.join(placeholders)
        )
        self.db(sql, values)

        return ob.id

    def getAll(self):
        return [
            self.preloadState(getattr(row, self.keyField), self.stateFromRow(row))
            for row in self.db('select * from %s' % self.table)
        ]