SQLite should use cursor.lastrowid for integer primary key

[Tom Lynn]

Every SQLite insert statement is followed by a "select <primary-key> where oid=<cursor.lastrowid>" statement. This select statement is unnecessary when the table has an integer primary key, since in that case id=cursor.lastrowid (see: <http://www.sqlite.org/autoinc.html>). Particularly when inserting a lot of rows, this makes a considerable speed difference (almost a factor of 2, as you might expect).

[James Henstridge (jamesh)]

What is needed to move this bug forward are:

1. a reliable way of determining which column represents the OID for the row.
2. Alter SQLiteConnection.execute() to fill in the primary_vars on the Update() statement object, similar to what PostgresConnection and MySQLConnection do.

If (1) isn't possible, you could probably use MySQLConnection's algorithm (which is the best that you can do with MySQL).

[Tom Lynn (tom-ynhapucnq)]

On Mon, 25 Aug 2008, James Henstridge wrote:

> What is needed to move this bug forward are:
>
> 1. a reliable way of determining which column represents the OID for the row.
> 2. Alter SQLiteConnection.execute() to fill in the primary_vars on the
> Update() statement object, similar to what PostgresConnection and
> MySQLConnection do.
>
> If (1) isn't possible, you could probably use MySQLConnection's
> algorithm (which is the best that you can do with MySQL).

It's presumably possible to derive (1) via introspection, but it's not
clear when that should be done, so it's probably better to just look for
an Integer column with primary=True or where __storm_primary__ ==
name-of-Integer-column (or where isinstance(__storm_primary__, Integer),
if that's supported?).

If you could see a use for introspection, the techniques for deriving this
that I've found are:

  * Compare "explain select column from table" to "explain select rowid
    from table", same output indicates that column is an alias.
    (This doesn't work if they've named a column "rowid", you're actually
    looking for a "3|SetNumColumns|0|0" row.)

  * "pragma table_info(table)" gives cid, name, type, notnull, dflt_value, pk.
    It seems to be an alias for rowid if type.lower()=='integer' and pk!=0.

[Siegfried Gevatter (rainct)]

(Not a bug in Zeitgeist).