[Sqlalchemy-commits] [1211] sqlalchemy/trunk/examples: a cool example that illustrates vertical tabl
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1211] sqlalchemy/trunk/examples: a cool example that illustrates vertical table storage, and objects that automatically configure themselves for this type of storage
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1211] sqlalchemy/trunk/examples: a cool example that illustrates vertical table storage, and objects that automatically configure themselves for this type of storage</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1211</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-03-26 00:30:21 -0600 (Sun, 26 Mar 2006)</dd> </dl> <h3>Log Message</h3> <pre>a cool example that illustrates vertical table storage, and objects that automatically configure themselves for this type of storage</pre> <h3>Added Paths</h3> <ul> <li>sqlalchemy/trunk/examples/vertical/</li> <li><a href="#sqlalchemytrunkexamplesverticalverticalpy">sqlalchemy/trunk/examples/vertical/vertical.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemytrunkexamplesverticalverticalpy"></a> <div class="addfile"><h4>Added: sqlalchemy/trunk/examples/vertical/vertical.py (1210 => 1211)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/examples/vertical/vertical.py 2006-03-26 06:29:47 UTC (rev 1210) +++ sqlalchemy/trunk/examples/vertical/vertical.py 2006-03-26 06:30:21 UTC (rev 1211) </span><span class="lines">@@ -0,0 +1,164 @@ </span><ins>+from sqlalchemy import * +import datetime + +"""this example illustrates a "vertical table". an object is stored with individual attributes +represented in distinct database rows. This allows objects to be created with dynamically changing +fields that are all persisted in a normalized fashion.""" + +e = create_engine('sqlite://', echo=True) + +# this table represents Entity objects. each Entity gets a row in this table, +# with a primary key and a title. +entities = Table('entities', e, + Column('entity_id', Integer, primary_key=True), + Column('title', String(100), nullable=False), + ).create() + +# this table represents dynamic fields that can be associated +# with values attached to an Entity. +# a field has an ID, a name, and a datatype. +entity_fields = Table('entity_fields', e, + Column('field_id', Integer, primary_key=True), + Column('name', String(40), nullable=False), + Column('datatype', String(30), nullable=False)).create() + +# this table represents attributes that are attached to an +# Entity object. It combines a row from entity_fields with an actual value. +# the value is stored in one of four columns, corresponding to the datatype +# of the field value. +entity_values = Table('entity_values', e, + Column('value_id', Integer, primary_key=True), + Column('field_id', Integer, ForeignKey('entity_fields.field_id'), nullable=False), + Column('entity_id', Integer, ForeignKey('entities.entity_id'), nullable=False), + Column('int_value', Integer), + Column('string_value', String(500)), + Column('binary_value', PickleType), + Column('datetime_value', DateTime)).create() + +class EntityDict(dict): + """this is a dictionary that implements an append() and an __iter__ method. + such a dictionary can be used with SQLAlchemy list-based attributes.""" + def append(self, entityvalue): + self[entityvalue.field.name] = entityvalue + def __iter__(self): + return iter(self.values()) + +class Entity(object): + """represents an Entity. The __getattr__ method is overridden to search the + object's _entities dictionary for the appropriate value, and the __setattribute__ + method is overridden to set all non "_" attributes as EntityValues within the + _entities dictionary. """ + def __init__(self): + """the constructor sets the "_entities" member to an EntityDict. A mapper + will wrap this property with its own history-list object.""" + self._entities = EntityDict() + def __getattr__(self, key): + """getattr proxies requests for attributes which dont 'exist' on the object + to the underying _entities dictionary.""" + try: + return self._entities[key].value + except KeyError: + raise AttributeError(key) + def __setattr__(self, key, value): + """setattr proxies certain requests to set attributes as EntityValues within + the _entities dictionary. This one is tricky as it has to allow underscore attributes, + as well as attributes managed by the Mapper, to be set by default mechanisms. Since the + mapper uses property-like objects on the Entity class to manage attributes, we check + for the key as an attribute of the class and if present, default to normal __setattr__ + mechanisms, else we use the special logic which creates EntityValue objects in the + _entities dictionary.""" + if key[0] == "_" or hasattr(Entity, key): + object.__setattr__(self, key, value) + return + try: + ev = self._entities[key] + ev.value = value + except KeyError: + ev = EntityValue(key, value) + self._entities[key] = ev + +class EntityField(object): + """this class represents a row in the entity_fields table.""" + def __init__(self, name=None): + self.name = name + self.datatype = None + +class EntityValue(object): + """the main job of EntityValue is to hold onto a value, corresponding the type of + the value to the underlying datatype of its EntityField.""" + def __init__(self, key=None, value=None): + if key is not None: + self.field = class_mapper(EntityField).get_by(name=key) or EntityField(key) + if self.field.datatype is None: + if isinstance(value, int): + self.field.datatype = 'int' + elif isinstance(value, str): + self.field.datatype = 'string' + elif isinstance(value, datetime.datetime): + self.field.datatype = 'datetime' + else: + self.field.datatype = 'binary' + setattr(self, self.field.datatype + "_value", value) + def _get_value(self): + return getattr(self, self.field.datatype + "_value") + def _set_value(self, value): + setattr(self, self.field.datatype + "_value", value) + name = property(lambda s: s.field.name) + value = property(_get_value, _set_value) + +# the mappers are a straightforward eager chain of +# Entity--(1->many)->EntityValue-(many->1)->EntityField +# notice that we are identifying each mapper to its connecting +# relation by just the class itself. +mapper(EntityField, entity_fields) +mapper( + EntityValue, entity_values, + properties = { + 'field' : relation(EntityField, lazy=False) + } +) + +entitymapper = mapper(Entity, entities, properties = { + '_entities' : relation(EntityValue, lazy=False) +}) + +# create two entities. the objects can be used about as regularly as +# any object can. +entity = Entity() +entity.title = 'this is the first entity' +entity.name = 'this is the name' +entity.price = 43 +entity.data = ('hello', 'there') + +entity2 = Entity() +entity2.title = 'this is the second entity' +entity2.name = 'this is another name' +entity2.price = 50 +entity2.data = ('hoo', 'ha') + +# commit +objectstore.commit() + +# we would like to illustate loading everything totally clean from +# the database, so we clear out the objectstore. +objectstore.clear() + +# select both objects and print +entities = entitymapper.select() +for entity in entities: + print entity.title, entity.name, entity.price, entity.data + +# now change some data +entities[0].price=90 +entities[0].title = 'another new title' +entities[1].data = {'oof':5,'lala':8} + +# commit changes. the correct rows are updated, nothing else. +objectstore.commit() + +# lets see if that one came through. clear object store, re-select +# and print +objectstore.clear() +entities = entitymapper.select() +for entity in entities: + print entity.title, entity.name, entity.price, entity.data </ins></span></pre> </div> </div> </body> </html> |
