On May 10, 2006, at 2:17 PM, Ian Bicking wrote:
But I'm also concerned that it requires the structures contain a lot more functionality, because they have to be able to portably express fairly high-level ideas. Off the top of my head, the difference between a database that uses sequences and one that uses lastinsertid is pretty substantial, in that it changes the containing logic. That's probably not the best example, except that there do exist portability concerns that influence more logic than might be expressable in a set of standard declarative structures.
SQLAlchemy SQL constructs are created with no methodology of creating primary keys built into them. you may *optionally* specify a Sequence object, if youre using a database with sequences and you know the name of a specific sequence youd like to use. if you specify a Sequence and try to use it in a Table with a database that doesnt have sequences, the Sequence is ignored.
whether a sequence or lastinsertedid is used is determined not even by the SQL Compiler but at execution time, based on the Dialect of the database being used and on the parameters sent along to the execution (the Dialect is separate from the Compiler).
You can make a Join object, and specify that its an outer join. on most databases, this compiles to a string like "tablea LEFT OUTER JOIN tableb ON tablea.col=tableb.col". but use the Oracle compiler with use_ansi=False, you get "tablea.col = tableb.col(+)", since Oracle 8 doesnt support the "LEFT OUTER JOIN" syntax.
You might also say "select * from table LIMIT 10". Oracle also doesnt have LIMIT. So the Oracle compiler will rearrange a LIMIT call into something like "select * from (select *, ROW NUMBER() OVER (ORDER BY table.rowid) as ora_rn from table) WHERE ora_rn<10" at the moment it compiles the statement for Oracle. The same statement object can then be taken over to Postgres where youll get plain old LIMIT when its compiled.
Another example: you say "order by table.oid". Some databases support a special column called "oid" or "rowid". Some do not, and others like postgres support it only based on options. For some databases, it will transform "oid" into the primary key column of the table, for others it converts it into its appropriate name, like "rowid". If theres no OID available and no primary key column on the table, it drops that clause from the ORDER BY, dropping the ORDER BY altogether if theres no other ordering specified. The same statement object, with no knowledge of the database's actual "OID" functionality, compiled repeatedly against various Compiler/Dialect combinations will produce these very different results.
These are examples where the shape of the SQL construct is highly different from what you get at compile or execution time; SQLAlchemy is definitely doing the "do everything as late as possible" thing to the max. I think SQLAlchemy's high level constructs (in 0.2), which borrow to some degree from Hibernate with regards to Dialect/ ExecutionContext, etc. should be carefully considered when proposing a spec for a generic SQL construction and execution API.
------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
