Finding Auto Incremented values from an INSERT OR IGNORE statement in SQLite

Question

I have a table called "images":

CREATE TABLE images (
    id      INTEGER PRIMARY KEY AUTOINCREMENT, 
    url     TEXT NOT NULL UNIQUE,
    caption TEXT
);

On inserting a row, I'd like the URL column to be unique. At the same time, I'd like to find out the id of the row with that URL.

INSERT OR IGNORE images (url, caption) VALUES ("http://stackoverflow.com", "A logo");
SELECT last_insert_rowid(); -- returns the id iff there was an insert.

Of course, I'd like to do it as quickly as possible, though my first thought was along the the lines of the following pseudo code:

int oldID = execSQL("SELECT last_insert_rowid()");
execSQL("INSERT OR IGNORE images (url, caption) VALUES ('http://stackoverflow.com', 'A logo')");
int newID = execSQL("SELECT last_insert_rowid()");
if (newID != oldID) {
     // there was an insert. 
     return newID;
} else {
     // we'd already seen this URL before
     return execSQL("SELECT id FROM images WHERE url = 'http://stackoverflow.com'");
}

But this seems hopelessly inefficient.

What is the most performant way of getting the Auto incremented row id from an INSERT OR IGNORE statement.

Answer 1

In Android 2.2 you can use SQLiteDatabse.insertWithOnConflict. Link.

Returns the row ID of the newly inserted row OR the primary key of the existing row if the input param 'conflictAlgorithm' = CONFLICT_IGNORE OR -1 if any errorOR -1 if any error

In older versions of SDK you can:

1. query if entry exists
2. If entry found - return id of selected item
3. If nothing found - insert entry using SQLiteDatabse.insert (it will return primary key of new item).

Also consider using transactions if needed.