[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
Having a sqlalchemy.__version__ and revision (or whatever named vars) is definitly usefull, even more when there are frequent project releases such as with SA. +1 for this feature... Maybe just add one simple .revision as of svn's $Revision$, that should be okay for flying on devel. But the file where you put this $Revision$ property has to be changed on every commit so the property is updated, no ? If so, this would give wrong revision number... but u'll need to setup a svn autoprop for that, add . ~/.subversion [miscellany] enable-auto-props = yes [auto-props] *.py = svn:keywords=Id Revision You can also: svn propset svn:keywords Revision the_file_you_want_to_tag avoiding the autoprop for all files. Cheers, Seb -- Sébastien LELONG [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
Maybe just add one simple .revision as of svn's $Revision$, that should be okay for flying on devel. But the file where you put this $Revision$ property has to be changed on every commit so the property is updated, no ? If so, this would give wrong revision number... if u touch the file and commit it, it does gets updated with proper new revision. But u have to do commit; no update nor checkout would do it for you. Well, that's svn. Nothing really automatic. More scripts... (may use svn info) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
while on polymorphic-union mappers vs non-polymorphic.. i have 3 kinds of type-queries: query_ALL-instances, quiery_BASE-instances, quiery_SUB-instances. First gives me all instances of a class or subclasess thereof, and is matching the main polymorphic-union mapper. The 3rd is a .select( table.c.typecolumn != base-type), piped through that too. For the 2nd one - getting the direct instances only (no subclasses) - i have a choice: a) have another non-primary mapper with some select( table.c.typecolumn==class_discriminator) as a table b) pipe this same select through the the main mapper So far i'm going the first way, but i'm not sure which one is better. Can u give any suggestion? If there are no big penalties for b), i will prefer that one; building another mapper was a tricky thing - and it does not work right now, for rev 2233. e.g. For a very simple case, B inh A, A points to self: ArgumentError: Cant determine relation direction for 'linkA' on mapper 'Mapper|A|bz4A|non-primary' with primary join 'bz4A. linkA_id = A.db_id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreign key' argument. Although the foreignkey _is_ there. i rmember this same error hapened before on some other occasion and then it disappeared. Anyway, if b) will work, i'll abandon it and not debug it to find why it breaks - unless you want it found and fixed. ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
your best bet: import pkg_resources as p p.get_distribution('sqlalchemy').version '0.3.3dev-r2190' still researching ways to make that more integrated but setuptools doesnt give a whole lot of options it seems. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
your best bet: import pkg_resources as p p.get_distribution('sqlalchemy').version '0.3.3dev-r2190' hm, this pkg_resources is from easy_setup or what? i dont have it. still researching ways to make that more integrated but setuptools doesnt give a whole lot of options it seems. yeah, i used to generate my setup.py _only_ to have the version automaticaly put somehow. Maybe just add one simple .revision as of svn's $Revision$, that should be okay for flying on devel. but u'll need to setup a svn autoprop for that, add . ~/.subversion [miscellany] enable-auto-props = yes [auto-props] *.py = svn:keywords=Id Revision well, not something pretty either... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
limited to non_primary mapper ? creating relationships with non_primary mapper is a bad idea. the foreignkey error really means, cant find a foreignkey *that I recognize as part of the parent or child tables*. On Jan 23, 2007, at 12:43 PM, svilen wrote: while on polymorphic-union mappers vs non-polymorphic.. i have 3 kinds of type-queries: query_ALL-instances, quiery_BASE-instances, quiery_SUB-instances. First gives me all instances of a class or subclasess thereof, and is matching the main polymorphic-union mapper. The 3rd is a .select( table.c.typecolumn != base-type), piped through that too. For the 2nd one - getting the direct instances only (no subclasses) - i have a choice: a) have another non-primary mapper with some select( table.c.typecolumn==class_discriminator) as a table b) pipe this same select through the the main mapper So far i'm going the first way, but i'm not sure which one is better. Can u give any suggestion? If there are no big penalties for b), i will prefer that one; building another mapper was a tricky thing - and it does not work right now, for rev 2233. e.g. For a very simple case, B inh A, A points to self: ArgumentError: Cant determine relation direction for 'linkA' on mapper 'Mapper|A|bz4A|non-primary' with primary join 'bz4A. linkA_id = A.db_id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreign key' argument. Although the foreignkey _is_ there. i rmember this same error hapened before on some other occasion and then it disappeared. Anyway, if b) will work, i'll abandon it and not debug it to find why it breaks - unless you want it found and fixed. ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
limited to non_primary mapper ? creating relationships with non_primary mapper is a bad idea. the foreignkey error really means, cant find a foreignkey *that I recognize as part of the parent or child tables*. hmmm. u mean, making the non-primary mapper equivalent (relation-wise) to the main mapper is no good? i don't use it for saving any data, only for fetching. okay, i'll try piping the select through main-mapper... On Jan 23, 2007, at 12:43 PM, svilen wrote: while on polymorphic-union mappers vs non-polymorphic.. i have 3 kinds of type-queries: query_ALL-instances, quiery_BASE-instances, quiery_SUB-instances. First gives me all instances of a class or subclasess thereof, and is matching the main polymorphic-union mapper. The 3rd is a .select( table.c.typecolumn != base-type), piped through that too. For the 2nd one - getting the direct instances only (no subclasses) - i have a choice: a) have another non-primary mapper with some select( table.c.typecolumn==class_discriminator) as a table b) pipe this same select through the the main mapper So far i'm going the first way, but i'm not sure which one is better. Can u give any suggestion? If there are no big penalties for b), i will prefer that one; building another mapper was a tricky thing - and it does not work right now, for rev 2233. e.g. For a very simple case, B inh A, A points to self: ArgumentError: Cant determine relation direction for 'linkA' on mapper 'Mapper|A|bz4A|non-primary' with primary join 'bz4A. linkA_id = A.db_id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreign key' argument. Although the foreignkey _is_ there. i rmember this same error hapened before on some other occasion and then it disappeared. Anyway, if b) will work, i'll abandon it and not debug it to find why it breaks - unless you want it found and fixed. ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
okay, let me release something too. http://linuxteam.sistechnology.com/o2rm/sawrap0124.tar.bz2 i'm still looking for a name... Basic idea of this SAwrapper is to be 3rd-level declarative translator (SA has 2, non-declarative: orm-sqlpy, sqlpy-sql-dialect) and hide the SA's specifics, i.e. OBJ-SAorm. What's in now: - all possible single relations of objects with =2 levels of inheritance are translated correctly (tests/sa_ref_A_B_A_all.py and tests/test_A_B_inh_ref_all.py) - some multilevel inheritances also can be mapped - see doc/example.py - YMMV - removed staticType stuff, will be released separate + it's adapter/wrapper - can generate a flat source-code for the prepared tables/mappers/etc (well, mostly) - for testcases etc. - needs sqlalchemy 0.3.4 what's to todo: - 3+ level inheritances, as well as mixed-type inheritances, do not work well (if at all) - fix current and do more tests - converting expressions into table.column-clauses - many2* relations basic usage: - declare your types as inheriting sawrap.plainwrap.Type (empty is ok) - declare your class hierarchy starting off sawrap.plainwrap.Base - declare attributes as instance/s of above Types - declare references as instances of sawrap.plainwrap.Type4SubStruct(name) - choose DB_inheritance decomposition type at each class - create fieldtypemap as a dictionary yourType:sqlalchemy.type - create SAdb() instance - sadb.bind( dict-of-all-classes, fieldtypemap, base_klas=yourbase) -- declarative phase ends here - all info is available, tables+mappers are built now. - populate - create obj-instances, fill them, link them. Then make session, and either use sadb.saveall( namespace-of-all-instances) or save them one by one. - query - use some of sadb.query_ALL_instances(), sadb.query_BASE_instances(), query_SUB_instances(), then do .select(..) over them - have a drink... or two. $ PYTHONPATH=sawrap/ python sawrap/doc/example.py ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- example.py Description: application/python
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
the concretes fail because concrete is not a completed feature at the momentim not even looking at those yet. the session.clear() should be done in all cases, the tests are completely pointless without it since you are just re-displaying the relationships you already constructed. so i made it just session.clear() in all cases. add this to line 110: remote_side=(Alink=='A' and table_A.c.id or None), and all non-concrete tests pass when no_sisters is turned on. um, whats no_sisters=False, is that taking A and pointing it to itself ? or a different instance of A ? if you want to help me out, creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. then again ive said that in every email... also this app is becoming close to something useful so it may become part of the unit tests at some point, although i cant help but think its overly exhaustive for a small range of conditions (like, what if we have A-B-C, etc.). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
the concretes fail because concrete is not a completed feature at the momentim not even looking at those yet. the session.clear() should be done in all cases, the tests are completely pointless without it since you are just re-displaying the relationships you already constructed. so i made it just session.clear() in all cases. add this to line 110: remote_side=(Alink=='A' and table_A.c.id or None), and all non-concrete tests pass when no_sisters is turned on. um, whats no_sisters=False, is that taking A and pointing it to itself ? yes. or a different instance of A ? if you want to help me out, creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. then again ive said that in every email... yes-yes-yes. just wait a little bit.. i'm generating them all. also this app is becoming close to something useful so it may become part of the unit tests at some point, although i cant help but think its overly exhaustive for a small range of conditions (like, what if we have A-B-C, etc.). this is not app, just a testcase ;-) Well, my idea is: i want to prove that all primitive operations needed for straight-forward o2r mapping, work in SA - in the way specified in this case. i have more tests for the multilevel inheritance, mixed type inheritance, etc, etc. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
creating non-dynamic forms of the failing tests, like 2 or 3 of them (i.e. one or two concretes, one sisters=True test), will save me the trouble of going through this script and extracting them myself. here. The thing generates them now itself. $python sa_ref_A_B_A_all.py relink generate_testfile 34 of 115 failed $ python _test_AB_cases.py 34 of 115 failed (u can give -v, echo, debug, dump on commandline of _test*.py) maybe simplest one of them: tableinh, A=None B=A1 BA=None $ py _test_AB_cases.py AB.test_AB_inh_tableinh__poly_1__Alink_None__Blink_A1__BAlink_None config: dump=False, echo=False, session_clear=True, debug=False -- Traceback (most recent call last): File _test_AB_cases.py, line 5077, in test_AB_inh_tableinh__poly_1__Alink_None__Blink_A1__BAlink_None me.query( session, A,B, table_A,table_B, a.id, b.id, sa,sb, samulti, sbmulti ) File /home/az/src/hor-trunk/model/db/sa_o2rm/tests/sa_gentestbase.py, line 40, in query me.assertEqual( sb, str(q)) AssertionError: 'B( id=2 name=ben data2=gun link2=a1 )' != 'B( id=2 name=ben data2=gun link2=anna )' -- all of the failures are of this kind. hmm... extractng the failed case from that 110-functions file is not a pleasant job... Maybe i should put each in separate file then... tomorrow. ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- sa_AB_combinations.tbz Description: application/tbz
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
im playing with some code to do this automatically, but looking back at sa_ref_A_B_A_all.py, change your primaryjoins to: mapper_A/link1: primaryjoin= table_A.c.link1_id==(Alink=='A' and (poly and Ajoin or table_A) or table_B).c.id, mapper_B/link2: primaryjoin= table_B.c.link2_id==(Blink=='A' and (poly and Ajoin or table_A) or table_B).c.id, i.e. when using a polymorphic union to select, make the primary join go from the parent table to the child selectable, instead of a table deep inside the child selectable (the selectable is the polymorphic union). then I get no failures outside of the concrete test cases. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
changeset 2221 addresses the pattern youre doing in test_case3. it required SA is more strict about matching foreignkey columns in a relatoinship into the primary join condition, luckily all the unit tests still pass. this is because youre doing self-referential relationships on polymorphic mappers...so all the involved tables are everywhere and its a big mess to figure out what points where. other random factoids: lazy=True is the default. correlate=False not needed in most cases. foreignkey not needed in most cases. it tells a relationship which column in the primaryjoin condition is foreign. the idea behind SA flags is to start with as few flags as possible and add in as needed. this conflicts with what youre trying to do, which involves getting a mapping to work with no manual experimentation phase. in particular a flag like post_update should only be used if absolutely needed, so it will take some fancy logic to guess things like that (not to mention, using primaryjoin on a concrete table mapping...now you need a distinct relation() + primaryjoin on each concrete mapper, etc.) concrete inheritance is also hardly implemented, in rev 2221 above i added a basic unit test thats been hanging around for concrete + polymorphic. it required i stick a kind of hacky thing in the mapper to get it to work. which generally means that new variations on concrete are going to keep failing until a more general method is worked out. in particular the big issue with concrete is that you cant really inherit a relation(), since the primaryjoin condition is going to be different for each submapper. the current method of relation propigation between inheriting mappers in the 0.3 series is more or less broken for concrete inheritancewhich implies any real relation propigation with concrete mappers (since im sure youre going to stick a relation() on a concrete mapper's base mapper if not already) is going to require major surgery inside the mapper compilation and a whole boatload of new tests. also i need to get an SA release out before any work like that starts. unit tests for concrete are going to live in test/orm/inheritance4.py to start. as far as typecolname i dont like that flag much. using an explicit setup means you know exactly what youre doing: p = polymorphic_union({ 'foo':select([footable, column(footype).label ('type')]), 'bar':bartable }) also i tried your polymorphic_union, but since you changed its API it doesnt pass any of the unit tests. polymorphic_union is intended for the end-user to manually configure, so it wouldnt have arguments like allow_empty_typecolname. however, one reason i like having the function separate from mapper config is so folks can do exactly what you did; come up with some other way to make their selection criterion. On Jan 19, 2007, at 7:32 AM, svilen wrote: and that change is in rev 2214, your two test scripts run unmodified now. without these changes (pure 0.3.3), adding explicit foreignkey=... on all relations worked OK in all 39 cases. With new changes (trunk/2216), B pointing to A (manager pointing to employee) does not work. B pointing to B is ok, A pointing to A or B is ok. sqlalchemy.exceptions.ArgumentError: Cant determine relation direction for 'manager' on mapper 'Mapper|Manager|Manager' with primary join 'Manager.manager_id = Employee.id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreignkey' argument. regardless that foreignkey= argument is there. see attach. -- Thanks for the other changes. Here's a fixed polumunion.py. It is best if typecolname there is always given - it will be used only if needed (concrete). But by default i've made it fit your present protocol. test_case3.py polymunion.py --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
if you read closely, you can see that the embedded query for selecting the employee is wrong; it has no FROM clause: SELECT Employee.id AS id, Employee.name AS name, Employee.atype AS atype, Employee.manager_id AS manager_id \nWHERE Employee.atype = ? SQLite is a little dumb in that it doesnt give a reasonable error message for this condition (other DB's do). when you see a SELECT that has no FROM, it usually means SA is trying to correlate the select as a subquery to the enclosing query. this will occur anytime a select involving the employee table occurs within another select involving the employee table. in this case its clearly wrong. so the fix is just to insure that the subquery doesnt get correlated: ajoin = { 'Employee': employee_table.select( employee_table.c.atype =='Employee', correlate=False), 'Manager': join( employee_table, manager_table,manager_table.c.id ==employee_table.c.id), } and all is well again. okay, would the join() also need .select(correlate=False) on it, or SA will not mistake it? Next variant, if tables are concrete, it does same mistake - so i have to specify employee_table.select( correlate=False) instead of plain employee_table in the polymorphic_union. Anything else? And, as i understand this applies only to things that go in a polymorphic_union; otherwise the correlate=False is not needed. the polymorphic union thing is one of the most ambitious queries SQLAlchemy's ORM produces. which is why I still have not made it Speaking of polyunion, i have made another version of your helper function. Your variant works ok if all tables under the poly-base are inherited in same way, i.e. either all are concrete_table, or all are table_inheritance. My version (~5-6 lines changed) does not require this (see attached file), and works ok for any type of mixed inheritance. btw, this correlate=False can be added automaticaly in the polyunion(). i've added it there and it seems to work... What do u think? ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- polymunion.py Description: application/python
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
sum up for me what the current bugs youre observing are. the foreignkey parameter is definitely needed for some of your cases. it un-ambiguates which column in the join condition is remote, for a join where its otherwise not clear. On Jan 19, 2007, at 7:32 AM, svilen wrote: and that change is in rev 2214, your two test scripts run unmodified now. without these changes (pure 0.3.3), adding explicit foreignkey=... on all relations worked OK in all 39 cases. With new changes (trunk/2216), B pointing to A (manager pointing to employee) does not work. B pointing to B is ok, A pointing to A or B is ok. sqlalchemy.exceptions.ArgumentError: Cant determine relation direction for 'manager' on mapper 'Mapper|Manager|Manager' with primary join 'Manager.manager_id = Employee.id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreignkey' argument. regardless that foreignkey= argument is there. see attach. -- Thanks for the other changes. Here's a fixed polumunion.py. It is best if typecolname there is always given - it will be used only if needed (concrete). But by default i've made it fit your present protocol. test_case3.py polymunion.py --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
From that point, we have a category of issue that comes up all the time, where what you want to do is possible, but SA never expected exactly what youre doing and ... This hinted me about another view point of what this wrapper of mine is: a storage for all the knowledge about how to use SA properly, for that particular field-area - easy transparent O2R mapping. For example, i don't know SQL - u do. And SA encapsulates that knowledge. My wrapper will essentialy encapsulate the 'how i use SA' knowledge. Anything out of this scope would again be done manualy, in a way or another. Anyway, to me all Software is just a (machine-interpretable) storage of human's knowledge about something. bye svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
in both test cases, the stack trace reveals that the error occurs during the lazy load operation of the child items: File test_case2b.py, line 57, in ? print a.manager ... File /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py, line 220, in lazyload the query being issued is: SELECT p_union.atype AS p_union_atype, p_union.manager_id AS p_union_manager_id, p_union.name AS p_union_name, p_union.id AS p_union_id \nFROM (SELECT anon_e513.manager_id AS manager_id, anon_e513.atype AS atype, anon_e513.id AS id, anon_e513.name AS name \nFROM (SELECT Employee.id AS id, Employee.name AS name, Employee.atype AS atype, Employee.manager_id AS manager_id \nWHERE Employee.atype = ?) AS anon_e513) AS p_union, Employee \nWHERE Employee.manager_id = ? ORDER BY p_union.oid if you read closely, you can see that the embedded query for selecting the employee is wrong; it has no FROM clause: SELECT Employee.id AS id, Employee.name AS name, Employee.atype AS atype, Employee.manager_id AS manager_id \nWHERE Employee.atype = ? SQLite is a little dumb in that it doesnt give a reasonable error message for this condition (other DB's do). when you see a SELECT that has no FROM, it usually means SA is trying to correlate the select as a subquery to the enclosing query. this will occur anytime a select involving the employee table occurs within another select involving the employee table. in this case its clearly wrong. so the fix is just to insure that the subquery doesnt get correlated: ajoin = { 'Employee': employee_table.select( employee_table.c.atype =='Employee', correlate=False), 'Manager': join( employee_table, manager_table,manager_table.c.id ==employee_table.c.id), } and all is well again. the polymorphic union thing is one of the most ambitious queries SQLAlchemy's ORM produces. which is why I still have not made it automatic, i.e. you have to explicitly create it yourself (even though theres a helper function). its also why its still plastered with all those alpha feature warnings. I think if theres a change to be made to SA its that I have to try a little harder to insure that the auto-correlation doesnt result in a SELECT that has no FROM, meaning this little confusion wouldnt occur. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
and that change is in rev 2214, your two test scripts run unmodified now. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
mmmh. This is about underlying base-framework of a system, equivalent in complexity to ERP. So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. if u want some example: DocumentA1 has some data and references other such documents (e.g. parent - in a reason-consequence chain); then DocumentA2 inherits DocumentA1 and adds some more fields to fill. More, i'm dealing with this O2R stuff over 10 years and i'm sick of specificaly write something over and over if it can be automated. But just recently i saw some light in the tunnel - SAlchemy. Therefore, all this about automating the O2R mapping. Major step? can be. i will do it. On Jan 17, 11:29 am, Paul Johnston [EMAIL PROTECTED] wrote: Hi Svil, here the case where B inherits A, and A references either A or B. i cannot get this to work.What may help everyone here, is if you can give a concrete usage example of why you want to do this. It's likely there's a way to achieve what you want to do without such major steps. Paul --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: P.S. u can remove the other repeating post from another account fo mine - seems GG has weird latenicies, hence i double posted... and first one appeared 10hrs later than second. its because google marked them all as spam. seeing as there were 5 duplicate messages, i let one through and deleted the rest. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. so, you are looking to create an application that generates python applications, basically. if you want to use SA for that, you have to completely master SA's relationships using simple, non-dynamically generated examples first, so you can get a feel for what it does. For example, i notice in your example youre trying to send in primaryjoin conditions which are already expressed in the Table objects - that already is overly complex since SA can divine simple join conditions from the tables themselves. for each pattern you are trying to create dynamically, create a non-dynamic version first, get that to work, then figure out what about your dynamic thing is different from the simple version. also, instead of creating class objects in memory and dynamically/conditionally attaching properties/methods/bells/whistles to each one, consider using true code generation instead (i.e. create a string, then call eval() / imp.load_source()) . that way, all the dynamic/conditional stuff can remain a complex beast, but generate very simple and readable code that would be easy to debug (not to mention perform much faster, since the complexity at generation time gets discarded at runtime). i hear theres a hot new template language called Mako that does this kind of thing.* * the author is also the author of said template language --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
So i have unknown hierarchy of classes - be them documents, entities, whatever u fancy. And they can point to each other in an unknown way. so, you are looking to create an application that generates python applications, basically. hm, can be interpreted this way. just add 'and interpret them on the fly' i want to use and extend the python's self-reflection - and self-modificiation - to get a sort-of self-interpretable thing, but without generating any source. Haveing proper self-reflection can auto-generate me the source - or multiple sources for multiple target environments, e.g. idl-python-python+C+html+binary. i've done that multiple times - but i want the thing to interpret itself. i know it's slower, but more flexible. if you want to use SA for that, you have to completely master SA's relationships using simple, non-dynamically generated examples first, so you can get a feel for what it does. i've done tons of this, to get where i am now. And i keep doing it. My approach is like start always a new and try with minimal possible stuff, so any thing i am using (e.g. those primary-joins) has been required in a way, (e.g. when u have multiple references, SA wants u to explicitly specify which is what). For example, i notice in your example youre trying to send in primaryjoin conditions which are already expressed in the Table objects - that already is overly complex since SA can divine simple join conditions from the tables themselves. is it errorneus to explicitly put the (correct) joins even if they could be figured out by SA? for each pattern you are trying to create dynamically, create a non-dynamic version first, get that to work, then figure out what about your dynamic thing is different from the simple version. yes, yes. so far so good, see all the sa_*py files; and when i started combining the different viewpoints - pure inheritance, pure (cyclical) references, and pure types, the thing broke. i hear theres a hot new template language called Mako that does this kind of thing.* *hehe*. good. i have done many languages before, interpretable and compileable, but now i am basing all my language creations stuff onto python - that is, using the python as syntax interpreter, and eventualy completely replacing the actual semantix; be it on-the-fly or via the objects themselves. Two examples are the StaticType declarative structure language (creatings python objects of unchangeable structure; very usable for DBs, protocols and similar must-not-change stuff), and the expr.py, which has a way to interpret python functions into Expr-trees, which then can be translated (read code-generation) into whatever, like text or sql-alchemy column expressions. It's all like using the python to build _your_ syntax tree made of your objects, and then use the objects - or some visitor - to do code-generation/ interpretation. But this is all offtopic; i could explain much-more if anyone is interested. i really _really_ want to extend the SA to be able to automaticaly do table-decomposition and mapping of an object-hierarchy. As i said, think of activemapper that also does inheritance and queries, provided just a few hints of how user exactly wants it done. So, back to the question: inheritance and referencing together? This is what stops me now; otherwise i have about 10 diff.cases that SA goes wrong, but i've found a way around so don't bother about them _now_. Also, if u can fix that repeating return obj.__dict__[key] in orm/attributes.py InstrumentedAttribute.get()... to be just return value, will be great. line 214 or so. see, i am replacing it myself, runtime (see sahack4repeatability.py ;-), but it's not nice, changing library's source on the fly ;-). Tomorrow i'll put a version stripped of all my statictype stuff, and all of the testcases (if u want so), which may seem more nice to you... all is about 1500 lines so far. have fun. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
svil wrote: is it errorneus to explicitly put the (correct) joins even if they could be figured out by SA? its not. but made your code that much harder to read (there was a lot more that made it even harder, thats just one cherry-picked example). Two examples are the StaticType declarative structure language (creatings python objects of unchangeable structure; very usable for DBs, protocols and similar must-not-change stuff), i hear Java is good for that ;). and the expr.py, which has a way to interpret python functions into Expr-trees, which then can be translated (read code-generation) into whatever, like text or sql-alchemy column expressions. are you using AST for this at least ? no need to reinvent the wheel It's all like using the python to build _your_ syntax tree made of your objects, and then use the objects - or some visitor - to do code-generation/ interpretation. sounds awfully generic, when i could just write a little AST visitor... i really _really_ want to extend the SA to be able to automaticaly do table-decomposition and mapping of an object-hierarchy. As i said, think of activemapper that also does inheritance and queries, provided just a few hints of how user exactly wants it done. well there is a project going on to produce the next gen. of activemapper and turboentity, and im sure they will add inheritance to it, since its pretty trivial. as far as querying, im guessing you are looking for an object-query-language (which is what all the pure ORM types seem to be desiring...but strangely they never get) So, back to the question: inheritance and referencing together? This is what stops me now; otherwise i have about 10 diff.cases that SA goes wrong, but i've found a way around so don't bother about them _now_. yeah...to be honest, i dont have the resources to debug an entire application, im in the business of maintaining and developing SQLAlchemy, and if theres a bug which you can illustrate with a *simple* test case of around 50 lines or less, ill fix the bug, or if theres some configuration based upon SA's *documented* usage patterns that doesnt make any sense, i can similarly illustrate how its done. reading the thousands of posts on the mailing list (including the old ones) as well as the tickets in trac can illustrate the kinds of examples that I can work with, without requiring my spending all day learning how someone's application works (because i cant spend all day, ive got paying work ive got to be doing). but if youre writing a whole big thing that is dynamically creating tables/classes which are then invalid, and it just doesnt work, thats kind of out of scope for me...just reading a single test case of yours takes me a very long time since they are not at all easy to follow...maybe some other folks on the list have some cycles to dig into the things that you are doing. you are getting SA exceptions which do have meaning and id suggest you dig in to SA itself to figure out what conditions might create those errors (such as various conflicting columns/properties seems to be a theme with what you have there). but if it turns out that youre doing something that is just wildly different from what SA's ORM basically does (such as its attribute instrumentation), im not sure if building on SA's ORM is your best bet...its already very high level and it is definitely going to be hard to build many more layers of class manipulation on top of it. you can always write your own ORM layer, feel free to borrow from SA's ORM concepts that you like and just use SA's SQL construction facilities to help you out. it might sound like a much bigger job but you would A. definitely learn SA's internals very well and B. get some deeper insight on the problems youre trying to tackle. Also, if u can fix that repeating return obj.__dict__[key] in orm/attributes.py InstrumentedAttribute.get()... to be just return value, will be great. line 214 or so. sure, changeset 2211. things like that, you should create a patch, then run the entire unittest suite to insure it doesnt break anything, then create a new ticket with the patch. also try to create distinct tickets for patches that address distinct issues. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
is it errorneus to explicitly put the (correct) joins even if they could be figured out by SA? its not. but made your code that much harder to read well, this case does require them :-( which has a way to interpret python functions into Expr-trees, which then can be translated (read code-generation) into whatever, like text or sql-alchemy column expressions. are you using AST for this at least ? no need to reinvent the wheel AST... i don't parse it, python parses it. i just replace .__eq__() with something else. The funny thing is, the python compiled code of some expression can be reinterpreted in diff.ways, e.g. def myfunc(x,y): return x100 y.startswith('big') .. myfunc( 23, big_balls_of_fire) - will eval the func yielding true/false, while myfunc( Var('x'), Var('y')) - will build you an expression (procided Var()s have operators etc defined). And even give errors - not any syntax will fit your new semantix. of course, theoretically, there is some AST, and some grammar, but it's sort-a virtual... It's all like using the python to build _your_ syntax tree made of your objects, and then use the objects - or some visitor - to do code-generation/ interpretation. sounds awfully generic, when i could just write a little AST visitor... Each time a new grammar and AST and new visitor? and teach users to that new grammar? and testsuite preventing the huns into rome? mmh. i did that. couple of languages per year... Users (be them end-customers or the other-team) know their semantix pretty well and they can cope with just about any _sensible_ easy grammar as far as it speaks in their lingo. oh well. my choice is - no more grammars (lowlevel) - if i can avoid them. As i said, think of activemapper that also does inheritance and queries, provided just a few hints of how user exactly wants it done. as far as querying, im guessing you are looking for an object-query-language (which is what all the pure ORM types seem to be desiring...but strangely they never get) not at all. SQL, OQL and probably all them *QL frighten me. Too extreme. Looks like academical creation with little touch to mortal people. i am targeting what u have with columns, but applied directly to object attributes, and used more for filtering, than real calculations. Practical stuff, as u said - whatever we can do, now. So, back to the question: inheritance and referencing together? im in the business of maintaining and developing SQLAlchemy, and if theres a bug which you can illustrate with a *simple* test case of around 50 lines or less, ill fix the bug, 110. can't make it less ;-) can? and it is about A=Employee and B=Manager, and all Employees having a manager. http://linuxteam.sistechnology.com/orm/sa_B_inh_A_A_ref_AB2.py usage: 1) $ python sa_B_inh_A_A_ref_AB2.py Alink=Manager or 2) $ python sa_B_inh_A_A_ref_AB2.py Alink=Empluyee same 2 errors. if of any help, i've found that if no manager (reference) is being ever assigned, then case 1 does not crash. - from sqlalchemy import * #table_inheritance, polymorphic def case( Alink='Manager' ): class Employee( object): name = 'notset' def __str__(me): return ' '.join( [me.__class__.__name__, str(me.id), str(me.name), getattr( me.manager, 'name', 'none') ]) class Manager( Employee ): bonus = 'notset' def __str__(me): return Employee.__str__(me) + ' ' + str(me.bonus) db = create_engine( 'sqlite:///:memory:') meta = BoundMetaData( db) meta.engine.echo = 0 class tables: pass tables.Employee = Table('Employee', meta, Column('id', Integer, primary_key=True), Column('name', String, ), Column('atype', String), Column('manager_id', Integer, ForeignKey( Alink+'.id', use_alter=True, name='whatever1' ) ) ) tables.Manager = Table('Manager', meta, Column('bonus', String, ), Column('id', Integer, ForeignKey( 'Employee.id'), primary_key=True, ), ) meta.create_all() ajoin = { 'Employee': tables.Employee.select( tables.Employee.c.atype == 'Employee'), 'Manager': join( tables.Employee, tables.Manager, tables.Manager.c.id ==tables.Employee.c.id), } Ajoin = polymorphic_union( ajoin, None ) mA = mapper( Employee, tables.Employee, select_table=Ajoin, polymorphic_on=Ajoin.c.atype, polymorphic_identity='Employee' ) mA.add_property( 'manager', relation( Alink == 'Employee' and Employee or Manager, primaryjoin= tables.Employee.c.manager_id==(Alink=='Employee' and tables.Employee or tables.Manager).c.id, lazy=True, uselist=False, post_update=True ) ) mB = mapper( Manager,
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
On Jan 17, 2007, at 5:09 PM, svil wrote: 110. can't make it less ;-) can? and it is about A=Employee and B=Manager, and all Employees having a manager. http://linuxteam.sistechnology.com/orm/sa_B_inh_A_A_ref_AB2.py OK. the first attachment called test_case1.py is how I'd like you to send me these test cases. What you do is when you have your 110 lines, continue to refactor it down, removing conditionals and code until you have a *single* illustration of the failure. in this case it involved replacing superfluous objects with simple variable names, removing all the function enclosures (i.e. straight-down program execution), and factoring all the embedded hard-to-read conditionals into the single version that fails (in this case the Manager version is the one that fails). The attached case illustrates the problem succinctly and as it turns out is exactly 50 nonblank lines :). It still includes lots of things that are not part of the failure, such as all the polymorphic arguments, but I can handle a little bit of extra fluff. From that point, we have a category of issue that comes up all the time, where what you want to do is possible, but SA never expected exactly what youre doing and therefore does not report the problem its having in any useful way. the reason its not working is because the relationship between Employee and Manager has a join condition where each column in the join is a foreign key to one or the other side of the relationship - so neither the direction of the relationship, nor the proper column to populate, can be determined from the join condition alone. SA then incorrectly determines which column in the join is the foreign column and gets all confused later on. If you now try this test program with the trunk, youll get as clear as an error as I could come up with: Cant determine relation direction for 'manager' on mapper 'Mapper|Employee|Employee' with primary join 'Employee.manager_id = Manager.id' - foreign key columns are present in both the parent and the child's mapped tables. Specify 'foreignkey' argument. So one way that this would work, and what SA normally expected here, is that when you make a relation between Employee and Manager, SA would see that as a self-referential relationship between Employee and Employee; if you made your ForeignKey on the employee table point to employee.id instead of manager.id, and you also update your primaryjoin condition accordingly, the mappers detect a self- referential condition and then it works: Column('manager_id', Integer, ForeignKey( 'Employee.id', use_alter=True, name='whatever1' ) ) But it works the way you have it as well. If you just provide a foreignkey parameter to the join condition, limiting which columns in the join you'd like it to consider to be the foreignkey that is significant in this join, it also works: properties={ 'manager':relation(Manager, primaryjoin=employee_table.c.manager_id==manager_table.c.id, foreignkey=employee_table.c.manager_id, uselist=False, post_update=True) } and that is what is attached in working_version.py. The unfortunate thing about foreignkey is that it is kind of a messy parameter which is a little inconsistent, namely that it isnt used for many-to-many relationships right now, and I plan on replacing with something more comprehensive in the future (ticket 385) so that it can totally replace the usage of ForeignKey on Table (and make life easier for MySQL folks who like to reflect their tables). In fact until I saw this example I didnt think there was ever a reason you'd need to use foreignkey right now provided the Table's had proper ForeignKeys on them (self-referential mappers use remote_side now). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- from sqlalchemy import * class Employee( object): name = 'notset' def __str__(me): return ' '.join( [me.__class__.__name__, str(me.id),str(me.name), getattr( me.manager, 'name', 'none') ]) class Manager( Employee ): bonus = 'notset' def __str__(me): return Employee.__str__(me) + ' ' + str(me.bonus) db = create_engine( 'sqlite:///:memory:') meta = BoundMetaData( db) meta.engine.echo = 0 class tables: pass employee_table = Table('Employee', meta, Column('id', Integer, primary_key=True), Column('name', String, ), Column('atype', String), Column('manager_id', Integer,
[sqlalchemy] Re: ORM ramblings 2 - and announcing some o2r wrapper
hi - there is far too much in that post to respond to, and i dont have the resources to crack open a huge new API with loads of tests and just run through them all (and i did do the cracking part...156 files for a test case posted to a mailing list is 155 files too many)...the various things youre having problems seem to be things that SA can certainly do (such as an inheriting relationship can easily reference itself or sub-types). if you can submit very simple illustrations as single-file test cases (also, one at a time, please), we can look into which features are things that you may be misunderstanding and which point to real deficiencies in SA's abilities. also the various issues and complaints you have with SA (not to mention the various behaviors that you deem unnecessary) seem more an invite for you to write your own ORM and/or work with SA's code to create something new yourself, since SA's ORM is by no means a there-must-only-be-one type of system as you put it...SA's ORM is just one of any number of ways to map objects, it is in fact an approach that values mixing object and relational concepts together so its not appropriate for those who feel that relational concepts should be completely hidden, and it seems you have some very different ideas of how ORM should be done, to which i say, great, write it up and show us ! no need rambling about it/trying to get me to completely rewrite SA to suit a project that seems quite distinct. also i think that vietnam of computer science article is total horseshit written by someone who tried and failed to write his/her own object-relational layer at some time, gave up, and decided instead to devote all the rest of his/her energy writing 5-word metaphorically-broken screeds about how all other ORMs are completely evil, without even trying them. to which I say, if ORMs are evil, then *just dont use them!* i didnt think there was any cabal of gangsters putting a gun to your head. in short, SA is about people *doing things with their databases right now* as opposed to rambling and handwaving about how ORMs should and should not work. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---