Supplying a custom autoincrement value in custom dialects

[Florian Apolloner]

Hi,

Informix mostly follows the Postgresql behavior when it comes to SERIAL columns with one notable exception: I have to specify 0 (0 as int, not NULL) for a SERIAL column on insert or leave it out.

Code like:
```
        Table('date_table', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('date_data', cls.datatype),
              )
        config.db.execute(
            date_table.insert(),
            {'date_data': None}
        )
```

currently fails because 'id' is NULL. Can I somehow tell SQLAlchemy to use an implicit default of 0 for those inserts?
(My dialect also follows the behavior of setting integer columns to serial if they are primary keys and have no defaults…)

Thanks,
Florian

[Mike Bayer]

Can you confirm the exact sql and parameters you are seeing?  SQLAlchemy never sends NULL for an auto increment id column, it omits it from the statement so that the default takes place, which I assume is what you mean by "leave it out".

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Florian Apolloner]

On Saturday, January 6, 2018 at 8:53:41 PM UTC+1, Mike Bayer wrote:

Can you confirm the exact sql and parameters you are seeing?  SQLAlchemy never sends NULL for an auto increment id column, it omits it from the statement so that the default takes place, which I assume is what you mean by "leave it out".

According to --log-info=sqlalchemy.engine:

```

test/test_suite.py::DateTest_informix+ibmdb::test_null <- ../../sources/sqlalchemy/lib/sqlalchemy/testing/suite/test_types.py

INFO:sqlalchemy.engine.base.Engine:INSERT INTO date_table (id, date_data) VALUES (?, ?)
INFO:sqlalchemy.engine.base.Engine:(None, None)

```

I wouldn't be surprised if this is an issue in my dialect; but I didn't really figure out where yet. Maybe something is off with those options: https://gitlab.com/apollo13/sqlalchemy-informix/blob/master/sqlalchemy_informix/ibmdb.py#L262-265 ?

Thanks & cheers,

Florian

[Mike Bayer]

so yes it is those options, since there is no method given for the
dialect to either pre-fetch or post-fetch the defaulted primary key
that the database generates. It's assuming your dialect will run the
sequence explicitly ahead of time and make that part of the INSERT
statement.

When you run an INSERT and the default fires off, you need a way of
accessing the newly generated integer index after the statement is
executed, and if that's not possible, you need to provide the new
integer primary key explicitly as generated by the sequence. The
current varieties are:

1. SELECT nextval(seq) on the sequence, then provide the value as
explicit to the INSERT. Postgresql dialect does this for old PG
versions that don't support RETURNING, the Oracle dialect used to work
this way also

print stmt.compile(dialect=postgresql.dialect(implicit_returning=False),
column_keys=[])
INSERT INTO a (id) VALUES (%(id)s)

2. Run the INSERT statement with RETURNING, assuming the sequence is
implicit, to get the newly generated PK - modern PG dialect does this,
SQL server dialect also:

print stmt.compile(dialect=postgresql.dialect(implicit_returning=True),
column_keys=[])
INSERT INTO a DEFAULT VALUES RETURNING a.id

3. Run the INSERT statement with RETURNING, where the sequence has to
be explicit, Oracle dialect does this:

print stmt.compile(dialect=oracle.dialect(implicit_returning=True),
column_keys=[])
INSERT INTO a (id) VALUES (id_seq.nextval) RETURNING a.id INTO :ret_0

4. The driver provides a cursor.lastrowid function, part of the DBAPI;
this lastrowid is usually selecting something like LAST_INSERT_ID(),
SQLite and MySQL drivers do this:

print stmt.compile(dialect=sqlite.dialect(), column_keys=[])
INSERT INTO a DEFAULT VALUES

5. we will post fetch the identifier but we need to emit special
functions that aren't part of cursor.lastrowid - SQL Server dialect
does this when RETURNING isn't used

So you need to know:

1. will you always use sequences?

2. are the sequences part of a database-side implicit system like PG
SERIAL or SQL Server IDENTITY or do they need to be explicitly
rendered like on Oracle?

3. does your database support RETURNING?

4. does your database support some kind of "fetch last id" function
that you run after an INSERT statement?

5. does your DBAPI provide #4 as cursor.lastrowid or not?

then we will know what your flags should be set towards.

> ?
>
> Thanks & cheers,
> Florian
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+...@googlegroups.com.

[Florian Apolloner]

On Sunday, January 7, 2018 at 12:07:13 AM UTC+1, Mike Bayer wrote:

1. will you always use sequences?

No, my dialect tries to use sequences only when the are explicitly specified, otherwise it tries to use SERIAL. So:

Column('id', Integer, Sequence('some_id_seq'), primary_key=True) uses & creates a sequence

Column('id', Integer, primary_key=True, autoincrement=True) uses SERIAL

2. are the sequences part of a database-side implicit system like PG
SERIAL or SQL Server IDENTITY or do they need to be explicitly
rendered like on Oracle?

They are both. Like in Postgresql you can have SERIAL which creates an implicit sequence where I do not know the name for it. But I can also use named sequences instead of a SERIAL column. What I cannot do is to set a server side default value for columns with an explicit sequence to be table_seq.NEXTVAL; I have to explicitly pass it in the query (Just like in Oracle).

3. does your database support RETURNING?

No

4. does your database support some kind of "fetch last id" function
that you run after an INSERT statement?

Yes, I can run "SELECT DBINFO('sqlca.sqlerrd1') FROM sysmaster:"informix".sysdual" to retrieve the inserted serial after an insert.

5. does your DBAPI provide #4 as cursor.lastrowid or not?

No

From looking at the current test results, if I specify an explicit sequence everything behaves properly as of now. Ie the dialect issue a "SELECT seq.NEXTVAL FROM sysdual" and then uses that for inserts, the only thing where it seems to fail currently is Integer primary key with autoincrement.

I'll look into mssql's ExecutinContext pre_exec/post_exec and postfetch_lastrowid more extensively this evening. That said, I am happy for every further advice.

Thank you for the long explanation!

Cheers,

Florian

From

[Florian Apolloner]

As an addition to the previous mail: I am going full circles here.

If I add:
```
    def get_insert_default(self, column):
        if (column.primary_key and column is column.table._autoincrement_column and
                column.default is None or
                (isinstance(column.default, schema.Sequence) and column.default.optional)):
                    return 0
        return super().get_insert_default(column)
```
it properly puts 0 into the inserts (so far so good, though I am not sure why it thinks it needs to insert the column at all).

If I then set `postfetch_lastrowid` to True it properly runs `get_lastrowid` after the insert and the SERIAL case is fine. But by setting `postfetch_lastrowid` to True it also stops generting sequence values for primary keys with explicit sequences and instead inlines them into the columns as `table_seq.NEXTVAL`, which is okay for the insert part, but now I have no way of getting the value :/

So to summarize:

for SERIAL I need:
 * Insert the column with 0 or not at all
 * run `get_lastrowid` by settings `postfetch_lastrowid` to True or manually from `post_exec`

for Sequences I need:
 * Prefetch the sequence value with a separate statement so SA knows the value and insert that value into the insert

And those two options should both be able to work at the same time without conflicting :D

Cheers,
Florian