On Jan 25, 2013, at 2:25 AM, jank wrote: > Hello, > I have implemented a dialect for a new database (EXASol).
that's great. I'd like to point you to a new system we have for testing and deploying external dialects, where your dialect can be packaged with a standard layout and make use of a series of "compliance" suites within SQLAlchemy. Within this test system, you can customize fully those capabilities which your dialect supports. If you check out SQLAlchemy-Access and SQLAlchemy-Akiban, you can see the standard forms: https://bitbucket.org/zzzeek/sqlalchemy-access https://github.com/zzzeek/sqlalchemy_akiban the key files within these packages regarding using the SQLAlchemy compliance suite are: /run_tests.py - test runner, is a front-end to Nose /setup.cfg - test runner configuration /test/requirements.py - a custom SuiteRequirements class which provides rules for those features and behaviors supported by the database /test/test_suite.py - pulls in the sqlalchemy.testing.suite package which causes the "suite" tests to be present for the Nose runner. the "compliance suite" is a work in progress and doesn't cover everything yet. Key areas that it does cover are the whole INSERT/lastrowid mechanics you're concerned with here, database reflection, and basic SQL types. > I have not done tests using the ORM layer of SA so far as I am primarily > interested in the Core layer. > So far things worked out pretty well DDL and DML support are basically > running. > The EXASol DB does not offer Sequences but autoincrement columns that are > very similar to postgres SERIAL types. > > Example DDL statement: > > CREATE TABLE test_exadialect.test ( > id INTEGER IDENTITY 10 NOT NULL, > name VARCHAR(40) NOT NULL, > age INTEGER, > PRIMARY KEY (id) > ) > > Identity is the keyword to add autoincrement behavior to an Integer-like > column. 10 is the initial value of the autoincrement. > > This DDL statement is generated based on this table metadata: > > Table('test', self.metadata, > Column('id', Integer, Sequence('test.id.seq', start=10, optional=True), > primary_key=True), > Column('name', String(40), nullable=False), > Column('age', Integer) > ) > > Looking at the postgres dialect implementation, I came to the conclusion that > using Sequences is the only way to get the desired behavior. The best dialect for you to look at here would be the MSSQL dialect, lib/sqlalchemy/dialects/mssql/base.py and perhaps the pyodbc implementation of it, lib/sqlalchemy/dialects/mssql/pyodbc.py. MSSQL's INSERT system resembles this the most, where we use the Sequence to allow configurability of the IDENTITY column, and a "post-fetch" at the cursor level is used to get at the last inserted identity. The post-fetch is performed right on the same cursor that the INSERT occurred on and bypasses the usual SQLAlchemy mechanics of executing a statement, so to that degree the Python overhead of this "post fetch" is negligible. > I have also implemented the get_lastrowid() method of the ExecutionContext > class. This all works as expected albeit at the costs of an additional > roundtrip for each single insert as the DB in question does not support > RETURNING. > > First question: is this the intended way to implement autoincrement behavior > in the absence of support for explicit sequence objects in the DB? sounds like you're on the right track, the usage of Sequence is optional overall but if you want configurability of the "start" and all that then yes. > No to the problem that I could not solve so far. I want to make the costs of > fetching the last autoincrement id upon insert/update optional. the last row id mechanics only come into play when an Insert() construct is used. This construct supports a flag "inline=True" which is intended to indicate an INSERT where you don't need any of the "default" values back. If you execute a table.insert(inline=True)... the entire "lastrowid" mechanics are bypassed, you can see this in sqlalchemy/engine/default.py line 663 post_insert(). This functionality of this flag is invoked automatically whenever the Insert() construct is used in an "executemany" context as well. > In our use case we are fine with the DB determining the next id value without > knowing about the value upon insert. I tried to fiddle around with various > configuration switches. Namely: > postfetch_lastrowid postfetch_lastrowid refers to whether or not the method of acquiring the last inserted id, when it is desired, is done via post-fetch, or whether the last inserted id is provided by some other method, which could be one of: pre-execute+embed in the INSERT, embed in the INSERT+use RETURNING, use the dbapi lastrowid() method. When this flag is False, in the absense of lastrowid() or RETURNING the system behaves as though a "pre-execute" insert is present, but since that's not implemented either you get a NULL. The flag does not indicate that the dialect flat out doesn't support returning an inserted PK value - the ability to return the last inserted PK is a requirement for a SQLAlchemy dialect as this is one of the most fundamental features the Core provides. > autoincrement > > I tried setting for the id column autoincrement=False would prevent the > SQLCompiler from forcing it into the INSERT statement: > > Column('id', Integer, Sequence('test.id.seq', start=10, optional=True), > primary_key=True, autoincrement=False), > Running and debugging my test case, the column object had the value True for > the autoincrement property. I assume that the combination of Sequence and > primary_key somehow overrides the value to True but I am lost in the SA code > base. autoincrement this is a Column level flag that indicates whether or not a column should be treated as autoincrement during the DDL process, assuming the column is integer based and doesn't have a foreign key constraint. It also has some significance during the "postfetch" process, but doesn't at the moment indicate that the postfetch process should be skipped unconditionally - however, you can certainly, if you wanted, check this flag within your own postfetch() routine and then not perform the lastrowid action. As for the autoincrement flag being flipped to True, I can't reproduce your behavior: from sqlalchemy import Column, Integer, Table, MetaData, Sequence t = Table('t', MetaData(), Column('x', Integer, Sequence('y'), primary_key=True, autoincrement=False)) assert t.c.x.autoincrement is False assert t._autoincrement_column is None if you can send me a code example illustrating the autoincrement flag being silently flipped to True that would be helpful. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
