Hello, I have implemented a dialect for a new database (EXASol). 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. 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? 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. 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 - autoincrement My first attempt was to set the postfetch_lastrowid switch to False. However, this switch seems to have wider implications than just switching off postfetching of the lastrowid. With the swtich to False the SQLCompiler generates different INSERT statement: for: test_tab.insert().values(name='foo', age=12).execute() I do get... with postfetch_lastrowid=True: INSERT INTO test_exadialect.test (name, age) VALUES ('foo', 12) with postfetch_lastrowid=False: INSERT INTO test_exadialect.test (id, name, age) VALUES (NULL, 'foo', 12) with this statement obviously being rejected by the DB as NULL is not allowed (and not desired) for the primary key column. So far my understanding of SA is limited, but I assume that setting postfetch_rowid to False is interpreted by SA as "this DB does not support sequences/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. Second question: Can someone give me a hint or pointer on where to look? Am I doing something wrong or trying to misuse the autoincrement flag? All I want to achieve is to make the fetching of the lastrowid optional. Do I have to implement my own dialect-specific flag? If so, what is the recommended way of doing this? Thanks for your time and any hint/advice, Jan -- 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 sqlalchemy+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.