SQLite AUTOINCREMENT non-primary key column

Question

I have the following SQLite table:

CREATE TABLE podcast_search (
        _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        search TEXT NOT NULL UNIQUE
)

Whenever a user inserts/updates a row in the table, I want to sort that row at the end of the table. Thus, if I insert the following values:

_id | search | sort
===================
  1 | foo    | 1
  2 | bar    | 2
  3 | quiz   | 3

And then later update the 1 row from foo to foo2, the values should look like:

_id | search | sort
===================
  2 | bar    | 2
  3 | quiz   | 3
  1 | foo2   | 4

I've implemented this thusly:

CREATE TABLE podcast_search (
        _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        search TEXT NOT NULL UNIQUE, 
        update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
CREATE TRIGGER update_date_update_trigger
    AFTER UPDATE ON podcast_search FOR EACH ROW
        BEGIN
            UPDATE podcast_search
                SET update_date = CURRENT_TIMESTAMP
                WHERE _id = OLD._id;
        END

However, my unit tests require a 1000ms sleep between insert/update operations in order to reliably sort, and this amount of delay is very annoying for unit testing.

I thought I could implement a vector clock instead, but it seems that AUTOINCREMENT values only exist for primary key columns. Does SQLite offer any other AUTOINCREMENT or AUTOINCREMENT-like option?

I'm running this on Android P, but this should be a generic SQLite problem.

UPDATE

I'm now using an sort INTEGER NOT NULL UNIQUE column, and SELECT-ing the largest row in that column and manually incrementing it before an INSERT/UPDATE:

CREATE TABLE podcast_search (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    search TEXT NOT NULL UNIQUE,
    sort INTEGER NOT NULL UNIQUE
)

SELECT sort from podcast_search ORDER BY sort DESC

either increment sort in application code, or set it to 0

Could I do this in a TRIGGER instead?

Answer 1

I thought I could implement a vector clock instead, but it seems that AUTOINCREMENT values only exist for primary key columns. Does SQLite offer any other AUTOINCREMENT or AUTOINCREMENT-like option?

They are not in fact AUTOINCREMENT values rather a column with AUTOINCREMENT will be an alias of the rowid column; not because AUTOINCREMENT has been coded but because INTEGER PRIMARY KEY has been coded.

All coding AUTOINCREMENT does is add a constraint that an auto-generated value MUST be greater than any other existing or used value. This only in fact becomes apparent if when a rowid with the value of 9223372036854775807 exists. In which case an attempt to insert a new row with an auto-generated rowid (i.e. no value is specified for the rowid column or an alias thereof) will result in an SQLITE_FULL error.

Without AUTOINCREMENT and when the highest rowid is 9223372036854775807 (the highest possible value for a rowid) an attempt is made to use a free value, which would obviously be lower than 9223372036854775807. SQLite Autoincrement

• You may wish to note the very first line of the linked page which says :-

• The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

• I can't see any need from your description.

So what you want is a means of assigning a value for the column that is to be sorted that is 1 greater than the highest current value for that column, so it becomes the latest for sorting purposes, a subquery that retrieves max(the_column) + 1 would do what you wish. This could be in an UPDATE, TRIGGER or in an INSERT.

• rowid = max(rowid) + 1 is basically how SQLite assigns a value to rowid unless AUTOINCREMENT is used when 1 is added to the greater of max(rowid) and the value, for the respective table, obtained from the table sqlite_sequence (will only exist if AUTOINCREMENT is used). It is referencing and maintaining sqlite_sequence that incurs the penalties.

For example you could use the following (which eliminates the need for an additional column and the additional index) :-

-- SETUP THE DATA FOR TESTING
DROP TABLE IF EXISTS podcast_searchv1;

CREATE TABLE IF NOT EXISTS podcast_searchv1 (
        _id INTEGER NOT NULL PRIMARY KEY, 
        search TEXT NOT NULL UNIQUE
);

INSERT INTO podcast_searchv1 (search) 
VALUES('foo'),('bar'),('guide')
;

-- Show original data
SELECT * FROM podcast_searchv1;

-- DO THE UPDATE
UPDATE podcast_searchv1 SET search = 'new value', _id = (SELECT max(_id) + 1 FROM podcast_searchv1) WHERE search = 'foo';

-- Show the changed data
SELECT * FROM podcast_searchv1;

The results being :-

and then :-