SQLite - a smart way to remove and add new objects

Question

Hi, I have a table in my database and I want for each row in my table to have an unique id and to have the rows named sequently.

For example: I have 10 rows, each has an id - starting from 0, ending at 9. When I remove a row from a table, lets say - row number 5, there occurs a "hole". And afterwards I add more data, but the "hole" is still there.

It is important for me to know exact number of rows and to have at every row data in order to access my table arbitrarily.

There is a way in sqlite to do it? Or do I have to manually manage removing and adding of data?

Thank you in advance, Ilya.

Answer 1

If you want to reclaim deleted row ids the VACUUM command or pragma may be what you seek,

http://www.sqlite.org/faq.html#q12

http://www.sqlite.org/lang_vacuum.html

http://www.sqlite.org/pragma.html#pragma_auto_vacuum

Answer 2

It may be worth considering whether you really want to do this. Primary keys usually should not change through the lifetime of the row, and you can always find the total number of rows by running:

SELECT COUNT(*) FROM table_name;

That said, the following trigger should "roll down" every ID number whenever a delete creates a hole:

CREATE TRIGGER sequentialize_ids AFTER DELETE ON table_name FOR EACH ROW
BEGIN
UPDATE table_name SET id=id-1 WHERE id > OLD.id;
END;

I tested this on a sample database and it appears to work as advertised. If you have the following table:

id name
1  First
2  Second
3  Third
4  Fourth

And delete where id=2, afterwards the table will be:

id name
1  First
2  Third
3  Fourth

This trigger can take a long time and has very poor scaling properties (it takes longer for each row you delete and each remaining row in the table). On my computer, deleting 15 rows at the beginning of a 1000 row table took 0.26 seconds, but this will certainly be longer on an iPhone.

Answer 3

If you don't want to take Stephen Jennings's very clever but performance-killing approach, just query a little differently. Instead of:

SELECT * FROM mytable WHERE id = ?

Do:

SELECT * FROM mytable ORDER BY id LIMIT 1 OFFSET ?

Note that OFFSET is zero-based, so you may need to subtract 1 from the variable you're indexing in with.

Answer 4

I strongly suggest that you re-think your design. In my opinion your asking yourself for troubles in the future (e.g. if you create another table and want to have some relations between the tables).

If you want to know the number of rows just use:

SELECT count(*) FROM table_name;

If you want to access rows in the order of id, just define this field using PRIMARY KEY constraint:

CREATE TABLE test (
id INTEGER PRIMARY KEY,
...
);

and get rows using ORDER BY clause with ASC or DESC:

SELECT * FROM table_name ORDER BY id ASC;

Sqlite creates an index for the primary key field, so this query is fast. I think that you would be interested in reading about LIMIT and OFFSET clauses. The best source of information is the SQLite documentation.