[TransWarp] TableDM proof of concept, request for comments

[Ulrich Eck]

Hi John, Phillip,

I've implemented something fairly similar for our apps as well
(it was inspired by Pyro and is considered as proof-of-concept,
that needs refactoring to make it valuable).

my current implementation is available at:

http://cvs.net-labs.de/cgi-bin/viewcvs.cgi/libs/nll/src/nll/database/sqldm/datamanager.py

There is a

- SQLEntityDM: same as John's TableDM
- SQLQueryDM: a very simple QueryDM that handles simple queries like: 
  "select <field-list-from-entity-dm> from <table-from-entity-dm> where
   <query-spec> <query-oid-as-param>"

it uses a Metaclass to prepare the field-specs (collected from
current-class and bases) and then a late-binding that pre-creates all
queries based on information gathered from the SQLConnection (e.g.
paramStyle). it uses parameterized queries where available and cares
about sequences as well as relations.

It expects a ISQLConnection to have a

  def getSequenceValue(name):
      "Return the next value for sequence <name>"

that is used to automatically fill values into fields
that are marked as sequenced-fields.


Example of usage (old binding-syntax):

class ContactsDM(_sqldm.SQLEntityDM):
    
    # Bound Datamangers
    contactTypesDM = _binding.bindTo('contactTypesDM')
    contactGroupsDM = _binding.bindTo('contactGroupsDM')
    
    # Local QueryDM Instances
    listContactPersonsFor = _binding.New(listCPForContactQueryDM)
    
    # Configuration
    # ElementClass to be used for this EntityDM
    defaultClass = Contact
    # TableName in DB
    tableName = 'CONTACT'
    # Fieldspec (('ROW', 'FEATURE'),)
    fieldSpec = (('ID', 'id', ),
                 ('CONTACT_TYPE_ID', None, ),
                 ('NAME', 'name', ),
                 ('STREET', 'street', ),
                 ('ZIP', 'zip', ),
                 ('CITY', 'city', ),
                 ('STATE', 'state', ),
                 ('COUNTRY', 'country', ),
                 ('CONTACT_GROUP_ID', None, ),
                 )
    # Specify the Unique Index
    indexSpec = ('ID', )
    # Specify automatically filled fields from sequences
    sequenceSpec = (('ID', 'SEQ_CONTACT_ID', ), )
    
    # StateForRelations defines the Relations to other Objects
    # and is used when loading the object's state from the DB 
    def stateForRelations(self, oid, ob, row, state):
        state['contactType'] = self.contactTypesDM[( \
                               row['CONTACT_TYPE_ID'],)]
        state['contactGroup'] = self.contactGroupsDM[( \
                               row['CONTACT_GROUP_ID'],)]
        state['contactPersons'] = _storage.QueryLink( \
                  self.listContactPersonsFor[(row['ID'],)])
        return state
    
    # ForeingKeysFromElement collects the necessary information
    # to be able to save the Elements state into the DB
    def foreignKeysFromElement(self, ob):
        values = {}
        values['CONTACT_TYPE_ID'] = self.oidFor( \
                       getattr(ob, 'contactType'))[0]
        values['CONTACT_GROUP_ID'] = self.oidFor( \
                       getattr(ob, 'contactGroup'))[0]
        return values
    


There are some implicit requirements and simplifications
in my implementation, that need to go away.
I like the way John defines the FieldMap/etc. .. 
so i'ld propose to find a common way of doing all this. 
I'ld be happy to contribute my stuff or help on creating 
a more advanced solution for this problem/requirement.

I need to get through all the replies and make a list
of requirements and if/how they're met with my implementation.

what would be the preferred way of collaborating on this ?


cheers 

Ulrich

Am Do, den 09.10.2003 schrieb John Landahl um 21:36:
> 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
-- 
---------------------------------------

Ulrich Eck
net-labs Systemhaus GmbH
Ebersberger Str. 46
85570 Markt Schwaben - Germany

eMail: ueck <at> net-labs.de
phone: +49 8121 4747 10
fax:   +49 8121 4747 77