Svil,
Thanks for your reply. I have been following your posts with interest
over the past half year (or I thought even longer). At first I
thought you were crazy. But now I've found myself creating a model of
similar complexity, as necessary to express the domain I'm working on.
The purpose of my model is to "ingest" all of the easily expressible
facts about the characteristics ('features') of categories and
(recursive) subcategories of items within specific sub-regions of a
domain of physical items, and to encode and store those facts in a
minimally redundant and maximally searchable / indexable form. This
supports an "instant search" / "search while you type" widget that is
far aware of the conceptual structure within the domain being
searched, unlike a mere full-text search over a flatfile, or ordinary
(non-category-structure-aware) indexings of a flatfile. This
awareness should bring significant benefits in terms of reducing the
search to its minimal consistent combinations of targets and a sense
of "just bringing up exactly what the user was looking for".
In the weeks ahead I will revisit some of the threads you listed.
Thank you for the conclusions and suggestions you mentioned; they seem
reasonable.
Eric
On Nov 25, 2:42 am, [EMAIL PROTECTED] wrote:
> Eric
> i'm not sure i follow all your mapping setup as it's too detail. but:
>
> i've been battling along similar data/feature inheritance+shading
> stuff along a branchy, recursive directed graph (not a pure tree as
> it has alternative short-cut paths in it), all over bitemporal
> objects and values (i.e. m2m relations all over, + grouping by
> time/objid), for almost half an year. see these posts of mine:
> - Many to many self referential relationship /15.03
> - multiple-aspects revisited /23.06
> - tree/graph persistency, concurency-safe? 13.07
> - unions in query?
> - and probably most others
> - as well as this thread in [EMAIL PROTECTED]:
> "optimizing a query over tree-like structure", 2008-09-30
> my setup (law,company, department(s)/recursive, position,
> workplace, employment) is all explained there, less the
> bitemporalism.
>
> also see the thread "Is there a simple way to let records have the
> same groups as it parents", or just look up "data inheritance" in the
> group.
>
> and so far i've reached these decisions, based on all the experiments
> (i don't come from sql background, and OO/practicalism doesnt give
> much insights on what sql can handle):
> - the root-most branches are separate queries, and a pseudo
> multi-query mimes a plain one over all those (it can also be a union
> of all ot them, or one single query - but single one has 20+tables in
> the From, and union fails here-there). it also came that different
> root-most branches have slightly diff. meaning hence it's good if
> they are loaded separately.
> - recursion is handled by expanding it on say 3 levels deep, hoping
> that noone will go further (i.e. a.x or a.a and (a.a.x or a.a.a and
> (a.a.a.x or ...))) etc.
> - everything is generated by a node-type-walking on class level, and
> the strategy of alternativ'ing on each level can be different (i.e.
> it can start as multiquery, follow as union on branch A and as single
> query on branch B). i can give this code if anyone dares read it..
> - the query returns all values whereever reachable/associated with
> some end-node
> - actual inheritance/shading etc is done after that in python. it can
> (probably) be done at sql-level by a very specific order-by, but it's
> nightmarish bitemporal query already so no need to go hell any
> further
>
> the times i got are of this kind: 10 nodes, with 10 values each, x100
> changes each, for about 20sec, on a relatively slow machine /
> postgres.
>
> maybe we can work together to get something out of it.
>
> On Tuesday 25 November 2008 09:51:37 Eric Ongerth wrote:
>
> > Below, I have attached a working testcase. It works, yes -- but my
> > question is that I need to make an improved version of a particular
> > method on one of my classes. The following model will probably
> > explain itself for the most part. I'll let you read it first, then
> > offer a few explanatory notes afterward just in case. Finally, at
> > the end, I will describe the difference between what the method in
> > question does now, and what I would like it to do.
>
> > The nature of the response I am seeking is: a description of what I
> > need to do to build a better version of the method I'm speaking of,
> > including any further insight on the practice of joining at
> > multiple levels of a recursive / self-referential (but loop-free)
> > graph.
>
> > ---snip---
>
> > from sqlalchemy import *
> > from sqlalchemy.sql import *
> > from sqlalchemy.orm import *
>
> > engine = create_engine('sqlite://')
>
> > metadata = MetaData(bind=engine)
>
> > itemtypes = Table('itemtypes', metadata,
> > Column('name', Text, primary_key=True))
>
> > itemtype_inheritance = Table('itemtype_inheritance', metadata,
> > Column('itemtype_name', Text, ForeignKey('itemtypes.name'),
> > primary_key=True),
> > Column('parent_name', Text, ForeignKey('itemtypes.name'),
> > primary_key=True))
>
> > features = Table('features', metadata,
> > Column('id', Integer, primary_key=True),
> > Column('name', Text),
> > Column('root_itemtype_name', Text,
> > ForeignKey('itemtypes.name')))
>
> > feature_dependencies = Table('feature_dependencies', metadata,
> > Column('dependent_id', Integer, ForeignKey('features.id'),
> > primary_key=True),
> > Column('determinant_id', Integer, ForeignKey('features.id'),
> > primary_key=True))
>
> > metadata.drop_all()
> > metadata.create_all()
>
> > itemtypes.insert().execute([
> > {'name': 'Product'},
> > {'name': 'Footwear'},
> > {'name': 'Boot'},
> > {'name': 'Ski'}
> > ])
>
> > itemtype_inheritance.insert().execute([
> > {'itemtype_name': 'Footwear', 'parent_name': 'Product'},
> > {'itemtype_name': 'Boot', 'parent_name': 'Footwear'},
> > {'itemtype_name': 'Ski', 'parent_name': 'Product'}
> > ])
>
> > features.insert().execute([
> > {'id': 1, 'name': 'Manufacturer',
> > 'root_itemtype_name':'Product' },
> > {'id': 2, 'name': 'Model', 'root_itemtype_name':'Product' },
> > {'id': 3, 'name': 'Year', 'root_itemtype_name':'Product' },
> > {'id': 4, 'name': 'Gender', 'root_itemtype_name':'Footwear' },
> > {'id': 5, 'name': 'US Shoe Size',
> > 'root_itemtype_name':'Footwear' },
> > {'id': 6, 'name': 'Length', 'root_itemtype_name':'Ski' },
> > {'id': 7, 'name': 'Weight', 'root_itemtype_name':'Product' }
>
> > ])
>
> > feature_dependencies.insert().execute([
> > {'dependent_id': 7, 'determinant_id': 1},
> > {'dependent_id': 7, 'determinant_id': 2},
> > {'dependent_id': 7, 'determinant_id': 3},
> > {'dependent_id': 7, 'determinant_id': 4},
> > {'dependent_id': 7, 'determinant_id': 5},
> > {'dependent_id': 7, 'determinant_id': 6}
> > ])
>
> > class Itemtype(object):
>
> > def __repr__(self):
> > return 'Itemtype: %s' % (self.name)
>
> > @property
> > def inherited_features(self):
> > return reduce(list.extend,
> > [base_itemtype.features for base_itemtype in
> > self.inherits],
> > [])
>
> > @property
> > def features(self):
> > return self.own_features.extend(self.inherited_features)
>
> > @property
> > def dependent_features(self):
> > return [f for f in self.features if f.determinants]
>
> > @property
> > def independent_features(self):
> > return [f for f in self.features if not f.determinants]
>
> > class Feature(object):
>
> > def __repr__(self):
> > return '%s %s' % (self.root_itemtype_name, self.name)
>
> > def determinants_in_scope_of(self, itemtype):
> > return (session.query(Feature)
> > .join(FeatureDependency.determinant)
> > .join(Feature.root_itemtype)
>
> > .filter(and_(FeatureDependency.dependent_id==self.id,
> > Itemtype.name==itemtype.name))).all()
>
> > class FeatureDependency(object):
>
> > def __repr__(self):
> > return "F_D: %s depends on %s" % (self.dependent.name,
> > self.determinant.name)
>
> > mapper(Itemtype, itemtypes, properties={
> > 'inherits':relation(Itemtype,
> > secondary=itemtype_inheritance,
> > primaryjoin=
> > (itemtypes.c.name==itemtype_inheritance.c.itemtype_name),
> > secondaryjoin=
> > (itemtype_inheritance.c.parent_name==itemtypes.c.name),
> > backref='progeny'),
> > 'own_features':relation(Feature,
>
> > primaryjoin=(features.c.root_itemtype_name==itemtypes.c.name),
> > backref=backref('root_itemtype', uselist=False))
> > })
>
> > mapper(Feature, features, properties={
> > 'dependents':relation(Feature,
> > secondary=feature_dependencies,
> > primaryjoin=
> > (feature_dependencies.c.determinant_id==features.c.id),
> > secondaryjoin=
> > (feature_dependencies.c.dependent_id==features.c.id),
> > backref=backref('determinants'))
> > })
>
> > mapper(FeatureDependency, feature_dependencies, properties={
> > 'dependent':relation(Feature,
> > uselist=False,
> > primaryjoin=
> > (feature_dependencies.c.dependent_id==features.c.id),
> > backref='feature_dependencies_as_dependent'),
> > 'determinant':relation(Feature,
> > uselist=False,
> > primaryjoin=
> > (feature_dependencies.c.determinant_id==features.c.id),
> > backref='feature_dependencies_as_determinant')
> > })
>
> > Session = sessionmaker(bind=engine)
> > session = Session()
>
> > Product = session.query(Itemtype).filter_by(name='Product').one()
> > Ski = session.query(Itemtype).filter_by(name='Ski').one()
> > Footwear = session.query(Itemtype).filter_by(name='Footwear').one()
> > Boot = session.query(Itemtype).filter_by(name='Boot').one()
> > Weight = session.query(Feature).filter_by(name='Weight').one()
> > Gender = session.query(Feature).filter_by(name='Gender').one()
> > Year = session.query(Feature).filter_by(name='Year').one()
>
> > ---snip---
>
> > (to anyone who wants to play with this and doesn't know how, save
> > the above code as testcase.py, and then run it from shell with
> > python -i testcase.py; you then have a Python interpreter with all
> > of the above objects still active, including the sqlalchemy
> > session, with the ability to query and experiment).
>
> > Notes on what this is supposed to model: Here are some of the facts
> > in the problem domain.
>
> > Each Itemtype inherits from others above it, except for root
> > itemtypes (the only one of which in this example is Product). The
> > reason for using an inheritance paradigm here is so that you can
> > set Features on any Itemtype and have them inherited by all of the
> > subcategories (sub- Itemtypes) of that Itemtype. In other words,
> > once you give the itemtype called 'Product' a feature called
> > 'Manufacturer' and a feature called 'Model', now *everything* that
> > inherits from Product (in fact the whole rest of the graph, here)
> > has those Features.
>
> > What you can't see here is that there is a similar structure
> > holding all the possible values of these features, and
>
> ...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
