Eric, a friendly comment: you do sound as crazy as Svil :-) Eric
2008/11/25, Eric Ongerth <[EMAIL PROTECTED]>: > > Yes, I am very glad to be free of multiversion and bitemporal > concerns, although I will eventually be setting this up for partial > multimaster asynchronous replication (but with a lot of intentional > compromises in order to avoid the major, currently unsolved, problems > with that entire field). > > As for different NodeTypes and polymorphic associations to values, I > am indeed taking part in something similar, but I do not allow the > polymorphism to exist on the Python side of the code | database > distinction, nor do I even allow the polymorphism to even enter the > ORM layer in mappers. All of my polymorphism is strictly contained > within the database using a 'vertical tables' approach. All of that > dbcook stuff scares me, though I think I can see why you want it. I > will eventually need to wade into the waters of stricter type checking > and conversion on my tables, and that will get me into a lot of > similar concerns but hopefully not as deeply! > > Also, yes, there are definitely a lot of 80/20 concerns and > willingness go for creative and seemingly partial solutions when > appropriate. > > I found this wiki article very interesting (though I had already > reinvented most of what it refers to before I ever knew about it): > > http://en.wikipedia.org/wiki/Entity-attribute-value_model > > It was encouraging to invent a wheel, later to find that the design > decisions I made aligned nicely with an entire existing field of > wheels. In this case I don't consider the work of reinvention to be > wasted, because this project has been my vehicle for learning Python, > sqlalchemy and much more. > > Wish I could offer to work with you, but this is my spare-time > project, I don't even have a coding job yet. I'm supporting myself > with manual labor while rolling this out. I think my project will at > least get me a decent job once it's far enough into beta stage to show > to anyone; or possibly it could become my own business next year. > > Eric > > > On Nov 25, 1:04 pm, [EMAIL PROTECTED] wrote: >> > 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. >> >> i think you're better than me, as u dont have the burden of >> bitemporal/multiversion stuff, and the completely different NodeTypes >> (hence polymorphic-assoc to values). seems your domain is better >> mathematicalizable - or u havent hit the edge of the door yet. >> >> having less types is also a relief, u dont need a dbcook layer to >> automate/hide mappings and value-type-related stuff (right now in a >> prototype scenario i have say 100 tables, 20 m2m assoc, 220 foreign >> keys - finally will probably be 200/100/600 or similar... it's >> unthinkable to handle them one by one, esp. if most of team has never >> seen sql). >> >> 1st make sure your model is correct. then, this full_heritage() func >> (mine is called similar, order_of_inheritance()) has the trouble of >> firing too many queries itself. once u start counting the queries >> fired around your current model, then u'll realize that such >> step-by-step recursive stuff - which is nice and pretty ok for >> c/python/lisp/whatever - isn't going to be very fast... but this also >> depends on the size of searchables. yours are simple and maybe 1000 >> queries arent going to be too bad. in my case, 1 awful complex >> bitemporal query is 100x faster than 1000 smaller bitemporal ones. >> i found it might be better in certain cases to get more data (2x) than >> needed and filter it further in python, than go crazy expressing the >> filter in flat set arithmetics (what sql is, more or less). (small) >> compromises are not a bad thing if placed properly. >> >> beware, my initial code about this graph-data-inheritance was the size >> of yours, while currently it's like 5x bigger - but can work either >> way (oh well 95% there). >> >> the moral i got from all this is, if the whole wardrobe wont get >> through the door, there are a lot of seemingly partial solutions that >> at the end may do better than initial requirement - and should NOT be >> ignored - but are notoriously difficult to guess as the initial >> target obvious "solution" obscures them. be it turning upside down, >> disassembling / cutting (the wardrobe or the door.. or another door), >> replacing with 3 small cupboards... swapping rooms... burning it and >> using plain hangers off the walls... moving elsewhere... >> >> have fun, and eeer... i'm sorta looking for job. >> too bad this _very_ "interesting times" project doesnt pay bills well. >> svilwww.svilendobrev.com >> >> > 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): >> >> ... >> >> 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 -~----------~----~----~----~------~----~------~--~---
