Create sqlite database index without holes

Question

I have a table, where I store images and I want to assign each image a consecutive frame number. When I add the images to the database table, it is easy to add it with a frame number that is higher than the last frame number I inserted (because I cache the last number). But when I remove images, I want to update the frame number so that there aren't any "holes" in the frame number and I haven't found a way yet to do this without doing an N+1 query.

The table structure is (simplified):

id | filename          | frame_number
---+-------------------+-------------
1  | image1.jpg        | 0
2  | image2.jpg        | 1
3  | another_image.jpg | 2

Most searched lead to people wanting to create a new (autoincrement) index without holes and people saying that they shouldn't do that or have to create a new table.

I am using python and handle my database with peewee, but that shouldn't make any difference, as I think the thing I want to do could be done in a raw sqlite query.

What is the problem you're trying to solve by not having "holes" in your data? Looks like it's something that should be done on your presentation layer and not in the data layer. — laalto, May 20 '16 at 12:20
Well I later want to assign for example markers to the images and group them to tracks. Therefore I want every image to have a well defined order, which is best represented by such a frame number. So I want to be able to "ask" the image which position it is in the overall sorting. I already thought about doing it in other ways, but realized that having a running number without holes is the easiest way. It only makes problems when updating the images (which doesn't happen that often). — Randrian, May 20 '16 at 12:23

score 0 · Answer 1 · edited May 23 '17 at 10:29

I finally got it to work with the help of the second answer from this question: How to fill in the "holes" in auto-incremenet fields?

CREATE TABLE NewIDs
(
    sort_index INTEGER PRIMARY KEY AUTOINCREMENT,
    id INT UNSIGNED
)

INSERT INTO NewIDs (id)
SELECT
    id
FROM
    image
ORDER BY
    filename ASC

UPDATE image SET sort_index = (SELECT sort_index FROM NewIDs WHERE image.id = NewIDs.id)-1

DROP TABLE NewIDs

Create sqlite database index without holes

1 Answers1