[sqlalchemy] Re: sqlite PK autoincrement not working when you do a composite PK?
On Saturday 14 June 2008 06:50:23 Russell Warren wrote: so far i have found these ways to hack somebeody else's source: a) inherit the class, replace whatever, use the new version - works if it is just you using the new-stuff b) complete replacement: import thatclass; thatclass.method = your-own-version c) partial hacks: inspect.get_source( that method); replace some lines in that with yours; compile; replace the method with the new version. this works if u have sources; if its just *.pyc, sorry. All good ways. I was planning on b), but I just couldn't (can't) locate the right replacement location underneath the SQLA classes I'm using (Session, Engine, Metadata, etc). Where the heck is the Compiler? it depends.. there is DefaultCompiler (called AnsiCompiler in 0.3) which is used when u do not have an engine yet (meta =Metadata()). and all dialects inherit from it. see dbcook/misc/aggregator/tests/convertertest.py for another hack for better visibility/str() of bindparams. also dbcook/misc/metadata/autoload is fiddling a bit with dialects. grep - and your very own eyes - are your friends. and of course trial+error... --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
On Saturday 14 June 2008 06:28:58 Russell Warren wrote: if you'd like to specify a value generator for the columns, just use a ColumnDefault. Whatever function or SQL you like will be called if no value is present - its just in this case we can't rely upon SQLite's OID generation. Thanks - I'll look into that. I just have to figure out how to make ColumnDefault dialect dependent. I wouldn't favor a built in system of guessing within the sqlite dialect how to autoincrement a composite PK field without explicit user intervention. Why not? Is it really guessing when the table has been defined precisely within SQLA? If you have a Column that has been defined to be an Integer primary key that is supposed to autoincrement, and you are using sqlite... how could you be wrong? The worst case I can think of is if sqlite changes in the future to actually support it, in which case you'd either change the dialect or get an error. No? maybe the user should request it somehow. Here come extra dialect-preferences. e.g. Column may mantain an attribute extras4sqlite, eventualy containing a dict of sqlite-specific settings; same for postgres etc. Then the specific dialect can look the column and get his own extra-settings - if any. This gives explicitness, separation of concerns (generic vs specific), and flexibility together. And this can be applied to other objects, e.g. tables or whatever. i'm sure thre are things u can do with tables in postgress and u cannot do in sqlite. Once some feature/attribute is considered generic/unified, it is moved from those extra* settings into the usual place -- or vice versa if stops being generic. --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
Russell Warren wrote: Why not? Is it really guessing when the table has been defined precisely within SQLA? If you have a Column that has been defined to be an Integer primary key that is supposed to autoincrement, and you are using sqlite... how could you be wrong? autoincrement is very difficult to implement in application code in an atomic and high performing fashion. If we're not using what the DB engine provides natively, then the user has to pick the method he or she wishes to use, since each would have caveats the user needs to be aware of. I'm not opposed to having a catalog of id generator tools within the distro but as of yet nobody has offered any. A key philosophy of SQLA is that we don't make choices for the user without explicit statement. But at the same time the dialect is also abstracting out many of the annoying backend type differences. I thought that a big part of SQLA was going to be allowing the use of any back end. We abstract as much as is reasonably possible. But we also honor and make explicit the differences between those databases so that each backend can be used to its fullest potential. Another big philosophy of ours is to not pretend the database doesn't exist by forcing all the various vendors into the lowest denominator of functionality. Practices like that are what give object relational tools as well as RDBMS overall a bad name. --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
And a related question: What is the general feeling on how well SQLA abtstracts the underlying database away? Am I expecting too much to be able to write my application using SQLA-only from the beginning and have it work on any of the popular databases without much tweaking? YMMV. it is actualy you who break things. e.g. if u dont rely much on specific SQldialect notions, or better, on specific SQL notions, you'r settled. i've made dbcook over SA and ever since the team have forgotten about what SQL is, except some very tricky things which has to be SQL aware, as they rely on DB-structure being what it is. But dialects... only come to play when something is not supported, and my way of handling this so far is to avoid using any stuff that is not supported everywhere - workaround on lowe or higher level, including model refactoring. --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
3. What internal SQLA structures can I count on staying fixed through revisions? everything changes/can change. so just do it, and keep doors opened for being version-aware (or actualy make them later). i have a lot of this stuff, look in the dbcook sources. e.g. after rev260 i've whacked 0.3/0.4beta1 support out. --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
YMMV. it is actualy you who break things. e.g. if u dont rely much on specific SQldialect notions, or better, on specific SQL notions, you'r settled. i've made dbcook over SA and ever since the team have forgotten about what SQL is, except some very tricky things which has to be SQL aware, as they rely on DB-structure being what it is. But dialects... only come to play when something is not supported, and my way of handling this so far is to avoid using any stuff that is not supported everywhere - workaround on lowe or higher level, including model refactoring I don't think that is a very workable strategy in the long run :( There are far to many bogus restrictions in some databases, e.g. Oracle, for any meaningful program to be written to work on all platforms w/o support/wrapping/hiding of ugly details by SA. I and a coworker are currently working on a patch-set to the oracle driver for SA for this very reason, fixing issues like: * broken mangling of forbidden/to long table/column names * missing support for the BOOL data type * missing support for boolean expressions in the column list ( select([tbl.c.col1 == tbl.c.col2]) ) (related to the last one above) You might think that you could easily get around the name-length barrier using the shortnames-option. But SA combines table names with column names to form aliases in select column lists, and the length quickly exceeds 32 characters (Oracles limit). In addition, do _you_ know which words are forbidden as column names in Oracle? I can assure you that there is at least a few you don't remember (and I wouldn't remember either :P)... Just a point of measurement (ok, oracle is the worst one...) Best regards, Egil signature.asc Description: OpenPGP digital signature
[sqlalchemy] Re: sqlite PK autoincrement not working when you do a composite PK?
Hi, I don't think that is a very workable strategy in the long run :( There are far to many bogus restrictions in some databases, e.g. Oracle, for any meaningful program to be written to work on all platforms w/o support/wrapping/hiding of ugly details by SA. This is often a difficulty for libraries that provide a portable layer over different implementations. GUI toolkits are a good example. The library essentially has three choices: 1) Only expose functionality that exists on all the implementations 2) Expose the user to the slight differences between implementations 3) Expose consistent functionality, and where an implementation lacks support, fake it In practice, (1) is usually a poor option as it's too restrictive. SQLAlchemy currently takes approach (2). There is definitely consistency merit for approach (3), but it comes at a cost - there's more magic going on, which could be confusing in some circumstances. 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: sqlite PK autoincrement not working when you do a composite PK?
On Friday 13 June 2008 16:34:47 Paul Johnston wrote: Hi, I don't think that is a very workable strategy in the long run :( There are far to many bogus restrictions in some databases, e.g. Oracle, for any meaningful program to be written to work on all platforms w/o support/wrapping/hiding of ugly details by SA. This is often a difficulty for libraries that provide a portable layer over different implementations. GUI toolkits are a good example. The library essentially has three choices: 1) Only expose functionality that exists on all the implementations 2) Expose the user to the slight differences between implementations 3) Expose consistent functionality, and where an implementation lacks support, fake it In practice, (1) is usually a poor option as it's too restrictive. SQLAlchemy currently takes approach (2). There is definitely consistency merit for approach (3), but it comes at a cost - there's more magic going on, which could be confusing in some circumstances. i think there's something 2.5, allow user to make his own support/settings, let him take the decision - preferences-like; e.g. a widget with a set of general attributes and several sets of implementation-dependent extra attributes, switched depending on implementation - the user can setup them all. well maybe i got a mix of all them 3, for different aspects. e.g. if oracle db will disallow me to use my 50-long names, i'll mangle them somewhere in the middle, but will not allow such meaningless restriction to cripple all the model above. while for other things i just surrender and dont use the features... --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
On Jun 13, 2008, at 1:00 AM, Russell Warren wrote: Any help is appreciated. I expect I'm in over my head trying to mess with a dialect implementation. I'm also worried that this will just be the first of many things like this I'll be trying to overcome to get SQLA to truly abstract the database implementations away... And a related question: What is the general feeling on how well SQLA abtstracts the underlying database away? Am I expecting too much to be able to write my application using SQLA-only from the beginning and have it work on any of the popular databases without much tweaking? if you'd like to specify a value generator for the columns, just use a ColumnDefault. Whatever function or SQL you like will be called if no value is present - its just in this case we can't rely upon SQLite's OID generation. I wouldn't favor a built in system of guessing within the sqlite dialect how to autoincrement a composite PK field without explicit user intervention. The dialects don't intend to build a completely uniform layer over all database backends (for example, when using Oracle, you are required to set up a default generator, usually a Sequence, in all cases) - the idea of a dialect's default behavior is that it uses what the database backend provides by default and that's it. --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
On Jun 13, 2008, at 3:58 AM, Egil Möller wrote: I and a coworker are currently working on a patch-set to the oracle driver for SA for this very reason, fixing issues like: * broken mangling of forbidden/to long table/column names really ? we have a lot of tests which pass fine for that, including when aliases are created, etc. In compiler.py, all names go through the same length filter no matter how they got generated (the only exception to this is the too long index names ticket which is strictly a schema thing). We have a long labels test specifically for this, and lots of ORM tests generate very long names as well (all of which work fine with Oracle). We did a tremendous amount of development on this a few years back and noone has had issues since. can you post a ticket with an example ?Also if producing fixes, keep in mind theres some compiler differences between 0.4 and 0.5, 0.5 is the direction we're heading * missing support for the BOOL data type there may or may not be a ticket for this (please post one if not) * missing support for boolean expressions in the column list ( select([tbl.c.col1 == tbl.c.col2]) ) (related to the last one above) ditto --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
for example, heres a beast of a unit test: python test/orm/inheritance/query.py --log-debug=sqlalchemy.engine -- db oracle PolymorphicUnionsTest.test_primary_eager_aliasing When you run on SQLite, one of the queries is: SELECT anon_1.people_person_id AS anon_1_people_person_id, anon_1.people_company_id AS anon_1_people_company_id, anon_1.people_name AS anon_1_people_name, anon_1.people_type AS anon_1_people_type, anon_1.engineers_person_id AS anon_1_engineers_person_id, anon_1.engineers_status AS anon_1_engineers_status, anon_1.engineers_engineer_name AS anon_1_engineers_engineer_name, anon_1.engineers_primary_language AS anon_1_engineers_primary_language, anon_1.managers_person_id AS anon_1_managers_person_id, anon_1.managers_status AS anon_1_managers_status, anon_1.managers_manager_name AS anon_1_managers_manager_name, anon_1.boss_boss_id AS anon_1_boss_boss_id, anon_1.boss_golf_swing AS anon_1_boss_golf_swing, machines_1.machine_id AS machines_1_machine_id, machines_1.name AS machines_1_name, machines_1.engineer_id AS machines_1_engineer_id FROM (SELECT people.person_id AS people_person_id, people.company_id AS people_company_id, people.name AS people_name, people.type AS people_type, engineers.person_id AS engineers_person_id, engineers.status AS engineers_status, engineers.engineer_name AS engineers_engineer_name, engineers.primary_language AS engineers_primary_language, managers.person_id AS managers_person_id, managers.status AS managers_status, managers.manager_name AS managers_manager_name, boss.boss_id AS boss_boss_id, boss.golf_swing AS boss_golf_swing FROM people LEFT OUTER JOIN engineers ON people.person_id = engineers.person_id LEFT OUTER JOIN managers ON people.person_id = managers.person_id LEFT OUTER JOIN boss ON managers.person_id = boss.boss_id ORDER BY people.person_id LIMIT 2 OFFSET 1) AS anon_1 LEFT OUTER JOIN machines AS machines_1 ON anon_1.engineers_person_id = machines_1.engineer_id ORDER BY anon_1.people_person_id, machines_1.oid of note is the anonymous label anon_1_engineers_primary_language, 34 characters. This label is generated from an anonymous alias name combined with a column name, which is itself a combination of the original table name and column name. So theres three stages of name generation represented here. Here it is on oracle, including the result rows: SELECT anon_1.people_person_id AS anon_1_people_person_id, anon_1.people_company_id AS anon_1_people_company_id, anon_1.people_name AS anon_1_people_name, anon_1.people_type AS anon_1_people_type, anon_1.managers_person_id AS anon_1_managers_person_id, anon_1.managers_status AS anon_1_managers_status, anon_1.managers_manager_name AS anon_1_managers_manager_name, anon_1.boss_boss_id AS anon_1_boss_boss_id, anon_1.boss_golf_swing AS anon_1_boss_golf_swing, anon_1.engineers_person_id AS anon_1_engineers_person_id, anon_1.engineers_status AS anon_1_engineers_status, anon_1.engineers_engineer_name AS anon_1_engineers_engineer_name, anon_1.engineers_primary_language AS anon_1_engineers_primary_1, machines_1.machine_id AS machines_1_machine_id, machines_1.name AS machines_1_name, machines_1.engineer_id AS machines_1_engineer_id FROM (SELECT people_person_id, people_company_id, people_name, people_type, managers_person_id, managers_status, managers_manager_name, boss_boss_id, boss_golf_swing, engineers_person_id, engineers_status, engineers_engineer_name, engineers_primary_language FROM (SELECT people.person_id AS people_person_id, people.company_id AS people_company_id, people.name AS people_name, people.type AS people_type, managers.person_id AS managers_person_id, managers.status AS managers_status, managers.manager_name AS managers_manager_name, boss.boss_id AS boss_boss_id, boss.golf_swing AS boss_golf_swing, engineers.person_id AS engineers_person_id, engineers.status AS engineers_status, engineers.engineer_name AS engineers_engineer_name, engineers.primary_language AS engineers_primary_language, ROW_NUMBER() OVER (ORDER BY people.person_id) AS ora_rn FROM people LEFT OUTER JOIN managers ON people.person_id = managers.person_id LEFT OUTER JOIN boss ON managers.person_id = boss.boss_id LEFT OUTER JOIN engineers ON people.person_id = engineers.person_id) WHERE ora_rn1 AND ora_rn=3) anon_1 LEFT OUTER JOIN machines machines_1 ON anon_1.engineers_person_id = machines_1.engineer_id ORDER BY anon_1.people_person_id, machines_1.machine_id INFO:sqlalchemy.engine.base.Engine.0x..4c:{} DEBUG:sqlalchemy.engine.base.Engine.0x..4c:Col ('ANON_1_PEOPLE_PERSON_ID', 'ANON_1_PEOPLE_COMPANY_ID', 'ANON_1_PEOPLE_NAME', 'ANON_1_PEOPLE_TYPE', 'ANON_1_MANAGERS_PERSON_ID', 'ANON_1_MANAGERS_STATUS', 'ANON_1_MANAGERS_MANAGER_NAME', 'ANON_1_BOSS_BOSS_ID', 'ANON_1_BOSS_GOLF_SWING', 'ANON_1_ENGINEERS_PERSON_ID', 'ANON_1_ENGINEERS_STATUS', 'ANON_1_ENGINEERS_ENGINEER_NAME', 'ANON_1_ENGINEERS_PRIMARY_1',
[sqlalchemy] Re: sqlite PK autoincrement not working when you do a composite PK?
if you'd like to specify a value generator for the columns, just use a ColumnDefault. Whatever function or SQL you like will be called if no value is present - its just in this case we can't rely upon SQLite's OID generation. Thanks - I'll look into that. I just have to figure out how to make ColumnDefault dialect dependent. I wouldn't favor a built in system of guessing within the sqlite dialect how to autoincrement a composite PK field without explicit user intervention. Why not? Is it really guessing when the table has been defined precisely within SQLA? If you have a Column that has been defined to be an Integer primary key that is supposed to autoincrement, and you are using sqlite... how could you be wrong? The worst case I can think of is if sqlite changes in the future to actually support it, in which case you'd either change the dialect or get an error. No? The dialects don't intend to build a completely uniform layer over all database backends (for example, when using Oracle, you are required to set up a default generator, usually a Sequence, in all cases) - the idea of a dialect's default behavior is that it uses what the database backend provides by default and that's it. But at the same time the dialect is also abstracting out many of the annoying backend type differences. I thought that a big part of SQLA was going to be allowing the use of any back end. Maybe I'll have to re-evaluate my approach... more likely I'll just keep plugging away and see what obstacles I hit! --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
so far i have found these ways to hack somebeody else's source: a) inherit the class, replace whatever, use the new version - works if it is just you using the new-stuff b) complete replacement: import thatclass; thatclass.method = your-own-version c) partial hacks: inspect.get_source( that method); replace some lines in that with yours; compile; replace the method with the new version. this works if u have sources; if its just *.pyc, sorry. All good ways. I was planning on b), but I just couldn't (can't) locate the right replacement location underneath the SQLA classes I'm using (Session, Engine, Metadata, etc). Where the heck is the Compiler? --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
judging by the slapdown in this ticket, and it looks safe to say that this behavior in SQLite will never change: http://www.sqlite.org/cvstrac/tktview?tn=2553 backend of Yow - that is a pretty terse slapdown! It doesn't seem like sqlite will ever support it. I keep hoping that sqlalchemy can be abstract enough that it will enable the use of any database backend. Stuff like this sqlite composite PK hiccup is discouraging, but I'm convinced there is a workaround to make this work! I don't care if it is the fastest way, it just has to work. I'm trying to get this to work with logic along these lines: 1. Identify in SQLiteCompiler.visit_insert when a primary key is missing from the insert (insert_stmt) 2. If the missing key is tagged as autoincrement, auto-add it to the insert object with a bogus value before the insert is processed (and flag it for use later when working with the execution context) 3. When subbing the variables into the INSERT statement later, replace the bogus value with something like: (SELECT max(id) FROM user)+1 It seems somewhat reasonable in principle to me, but the problems I'm having in reality are: 1. How do I override SQLiteCompiler.visit_insert without modifying SQLA's sqlite.py? I of course want to avoid trashing the base SQLA install, but can't find an override location in the object tree from my session or engine or anything. 2. Even if I could find a way to override visit_insert, I'm having trouble locating a location to stuff the select max code in place. Tweaking the statement by creating an SQLiteDialect.do_execute implementation seems like it might work, but it also doesn't seem lke the cleanest way. 3. What internal SQLA structures can I count on staying fixed through revisions? eg: in visit_insert I can use self._get_colparams to figure out what columns have been requested, and I can use insert_stmt.table._columns to figure out what primary key is missing (and if it is supposed to be autoincrement). But I don't know which of those I can actually count on being there in the future! Plus, crawling around internal objects like this just seems like a bad idea. Any help is appreciated. I expect I'm in over my head trying to mess with a dialect implementation. I'm also worried that this will just be the first of many things like this I'll be trying to overcome to get SQLA to truly abstract the database implementations away... And a related question: What is the general feeling on how well SQLA abtstracts the underlying database away? Am I expecting too much to be able to write my application using SQLA-only from the beginning and have it work on any of the popular databases without much tweaking? --~--~-~--~~~---~--~~ 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: sqlite PK autoincrement not working when you do a composite PK?
On May 31, 2008, at 12:38 AM, Russell Warren wrote: I've tried sifting through the sqlite dialect to figure out what is going on and have even tried forcing supports_pk_autoincrement to be true, but it rapidly became clear I hadn't a clue what I was doing in the sqlalchemy guts. Does anyone know why autoincrement on id stopped working, and how I can fix it? this is not a SQLA behavior, its a known behavior of sqlite3 - its autoincrement feature ceases to work with a composite primary key. I've never known any workaround for it (with the possible exception of declaring the second column in the PK as unique, instead of it actually being part of the PK). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---