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 [email protected].
To unsubscribe from 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.
