Hello all,
I've been reading SQLALchemy docs and examples for quite some time
now, and made a functional model of entities that "ressembles" the way
OO works in concept - but it may be very early to say that, it's just
a simple concept.
The objective is to have:
* a main table, that defines available objects;
* a fields table, that defines objects and types for those objects;
* an instances table, that creates the objects itself and applies
hierarchy; and
* a values table, which have been pivoted and polymorphic, to store
multiple values of instances.
Maybe the code can be a little bit self explanatory; my problem will
stand below:
<code>
from sqlalchemy import *
from sqlalchemy.schema import *
from sqlalchemy.orm import *
from sqlalchemy.orm.collections import *
from sqlalchemy.ext.declarative import *
from sqlalchemy.ext.associationproxy import *
from sqlalchemy.orm.util import *
metadata = MetaData()
mainObjects = Table(
'objects', metadata,
Column('id', Integer, Sequence('obj_id_seq'), primary_key=True),
Column('name', Unicode(100), nullable = False),
)
mainFields = Table(
'fields', metadata,
Column('id', Integer, Sequence('fld_id_seq'), primary_key = True),
Column('object_id', Integer, ForeignKey('objects.id'), nullable =
False),
Column('name', Unicode(100), nullable = False),
Column('mask', Unicode(100)),
Column('task', Integer)
)
mainInstances = Table(
'instances', metadata,
Column('id', Integer, Sequence('inst_id_seq'), primary_key=True),
Column('object_id', Integer, ForeignKey('objects.id'), nullable =
False),
Column('parent_id', Integer, ForeignKey('instances.id')),
Column('name', Unicode(100), nullable = False),
)
mainValuesAttributes = Table(
'values', metadata,
Column('id', Integer, Sequence('val_id_seq'), primary_key = True),
Column('instance_id', Integer, ForeignKey('instances.id'), nullable =
False),
Column('key', Unicode(64), nullable = False),
Column('item_type', Integer, nullable = False),
UniqueConstraint('key', 'instance_id', name = 'val_uq')
)
mainIntValues = Table(
'values_int', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Integer, default = None)
)
mainUnicodeSmallValues = Table(
'values_char_small', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Unicode(60), default = None)
)
mainUnicodeNormalValues = Table(
'values_char_normal', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Unicode(255), default = None)
)
mainUnicodeBigValues = Table(
'values_char_big', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', UnicodeText, default = None)
)
mainBoolValues = Table(
'values_bool', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Boolean, default = None)
)
mainDateValues = Table(
'values_date', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Date, default = None)
)
mainDateTimeValues = Table(
'values_datetime', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', DateTime, default = None)
)
mainFloatValues = Table(
'values_float', metadata,
Column('id', Integer, ForeignKey('values.id'), primary_key = True),
Column('value', Numeric(10,2), default = None)
)
class MainValues(object):
_NONE = 0
_INT = 10
_UNICODE_SMALL = 20
_UNICODE_NORMAL = 30
_UNICODE_BIG = 40
_BOOLEAN = 50
_DATE = 60
_DATETIME = 70
_FLOAT = 80
def __new__(cls, key=None, instance=None, value=None):
if hasattr(value, '__class__'):
if value.__class__.__name__ == 'str':
value = unicode(value)
if value.__class__.__name__ == 'unicode':
if len(value) < 61:
cls = MainUnicodeSmallValues
elif 61 <= len(value) < 256:
cls = MainUnicodeNormalValues
else:
cls = MainUnicodeBigValues
elif value.__class__.__name__ == 'int':
cls = MainIntValues
elif value.__class__.__name__ == 'bool':
cls = MainBoolValues
elif value.__class__.__name__ == 'date':
cls = MainDateValues
elif value.__class__.__name__ == 'datetime':
cls = MainDateTimeValues
elif value.__class__.__name__ == 'float':
cls = MainFloatValues
return object.__new__(cls)
class OrmObject(object):
'''
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GenericOrmBaseClass
'''
def __init__(self, **kwargs):
for key in kwargs.keys():
if not key.startswith('_') and key in
self.__dict__.keys():
try:
setattr(self, key, kw[key])
except AttributeError, TypeError:
print ' ** Key "%s" for %s could not be
created.' % (key,
self.__class__.__name__)
def __repr__(self):
attrs = []
for key in self.__dict__:
if not key.startswith('_'):
attrs.append((key, getattr(self, key)))
return '<' + self.__class__.__name__ + '(' + ', '.join(x[0] +
'=' +
repr(x[1]) for x in attrs) + ')>'
class MainFields(OrmObject):
pass
class MainObjects(OrmObject):
instances = association_proxy('_instances', 'name')
_property_type = MainFields
_property_mapping = '_fields'
__map = property(lambda self: getattr(self, self._property_mapping))
def add_field(self, name, task=None, mask=None):
property = self.__map.get(name, None)
if property is None:
self.__map[name] = self._property_type(name=name,
task=task,
mask=mask, object_id=self.id)
else:
property.value = value
def del_field(self, name):
del self.__map[name]
def has_field(self, name):
return name in self.__map
def get_all_fields(self):
return self.__map.keys()
class MainValuesAttributes(OrmObject):
value = association_proxy('_value', 'value', creator=MainValues)
class MainValuesMixin(MainValues, MainValuesAttributes):
def __init__(self, key, instance, value):
self.key = key
self.instance_id = instance.id
self.value = value
def __repr__(self):
return '<%s %r=%r>' % (self.__class__.__name__, self.key,
self.value
if len(self.value) < 100 else self.value[:100] + ' ...')
class MainInstances(object):
def __init__(self, name, object=None):
self.name = name
if object is not None:
self.parent_id = object.id
_property_type = MainValuesMixin
_property_mapping = '_values'
__map = property(lambda self: getattr(self, self._property_mapping))
def __getitem__(self, key):
return self.__map[key].value
def __setitem__(self, key, value):
property = self.__map.get(key, None)
if property is None:
self.__map[key] = self._property_type(key, self, value)
else:
property.value = value
def __delitem__(self, key):
del self.__map[key]
def __contains__(self, key):
return key in self.__map
def keys(self):
return self.__map.keys()
def values(self):
return [prop.value for prop in self.__map.values()]
def items(self):
return [(key, prop.value) for key, prop in self.__map.items()]
def __iter__(self):
return iter(self.keys())
def __repr__(self):
return '<%s, name="%s", items=%s>' % (self.__class__.__name__,
self.name, len(self.__map))
class MainIntValues(MainValuesMixin):
pass
class MainUnicodeSmallValues(MainValuesMixin):
pass
class MainUnicodeNormalValues(MainValuesMixin):
pass
class MainUnicodeBigValues(MainValuesMixin):
pass
class MainBoolValues(MainValuesMixin):
pass
class MainDateValues(MainValuesMixin):
pass
class MainDateTimeValues(MainValuesMixin):
pass
class MainFloatValues(MainValuesMixin):
pass
'''
mapping it all together ...
'''
mainFields_mapper = mapper(
MainFields,
mainFields
)
mainObjects_mapper = mapper(
MainObjects,
mainObjects,
properties = {
'_instances': relationship(MainInstances),
'_fields': relationship(MainFields,
collection_class=attribute_mapped_collection('name'), cascade="all,
delete-orphan", passive_deletes=True)
}
)
mainInstances_mapper = mapper(
MainInstances,
mainInstances,
properties = {
'_values': relationship(MainValuesAttributes,
collection_class=attribute_mapped_collection('key'), cascade="all,
delete-orphan", passive_deletes=True)
}
)
mainValuesMapper = mapper(
MainValuesAttributes,
mainValuesAttributes,
polymorphic_on = mainValuesAttributes.c.item_type,
polymorphic_identity = MainValues._NONE,
with_polymorphic = '*'
)
mainIntValues_mapper = mapper(MainIntValues, mainIntValues,
inherits=MainValuesAttributes, polymorphic_identity=MainValues._INT)
mainUnicodeSmallValues_mapper = mapper(MainUnicodeSmallValues,
mainUnicodeSmallValues, inherits=MainValuesAttributes,
polymorphic_identity=MainValues._UNICODE_SMALL)
mainUnicodeNormalValues_mapper = mapper(MainUnicodeNormalValues,
mainUnicodeNormalValues, inherits=MainValuesAttributes,
polymorphic_identity=MainValues._UNICODE_NORMAL)
mainUnicodeBigValues_mapper = mapper(MainUnicodeBigValues,
mainUnicodeBigValues, inherits=MainValuesAttributes,
polymorphic_identity=MainValues._UNICODE_BIG)
mainBoolValues_mapper = mapper(MainBoolValues, mainBoolValues,
inherits=MainValuesAttributes,
polymorphic_identity=MainValues._BOOLEAN)
mainDateValues_mapper = mapper(MainDateValues, mainDateValues,
inherits=MainValuesAttributes, polymorphic_identity=MainValues._DATE)
mainDateTimeValues_mapper = mapper(MainDateTimeValues,
mainDateTimeValues, inherits=MainValuesAttributes,
polymorphic_identity=MainValues._DATETIME)
mainFloatValues_mapper = mapper(MainFloatValues, mainFloatValues,
inherits=MainValuesAttributes, polymorphic_identity=MainValues._FLOAT)
if __name__ == '__main__':
engine = create_engine('sqlite://')
metadata.bind = engine
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# using it
from pdb import set_trace
set_trace()
</code>
Ok, it works fine. The problem is: I didn't want to replicate the
"item_type" for every value created under multiple instances (why do
so, if you can set it just once?). I was wondering if there's any way
to put the polymorphic identities in mainFields, and then gathered
from there to create the polymorphism based on a column like item_type
to the mainValues table (mainFields -> mainObjects <-- mainInstances
<-- mainValues). I know it's a long way, but I think it's a good line
of thinking. Also, the "key" attribute in mainValues doesn't need to
exists neither (it is set in mainFields).
Is this possible?
Thanks for your time.
Best regards,
Richard.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
