macOS SQLite: incrementing the rowid of multiple rows.

[Nutter]

I want to create and maintain an SQLite database which mirrors the content of an NSMutableArray. To do so I obviously need to keep track of the array index corresponding to each database entry, and it seems to me (as someone who knows relatively little about SQLite) that using the rowid column is a good way to do so.

When an object is inserted in the middle of the array, I need to update the database by incrementing the rowid column for all entries after the insertion point, before I can insert the new entry. For example:

UPDATE table SET rowid = rowid + 1 WHERE rowid >= 5;

This returns an error, as is to be expected: when the first rowid is incremented, it immediately conflicts with the next rowid.

What I can't figure out for the life of me is whether there is a way around this problem. Common sense would suggest that there is - all I really need to do is to increment the rowid column in reverse order, but I can't find any way to do so.

Online documentation for SQLite, both official and unofficial, seems quite poor. Can anybody point me in the right direction?

 

[lazydog]

I'm not sure if I can be of much help, but isn't the row number of a record equivalent to the index that you want? I guess you could make use of this whenever you need to access your records. Rather than selecting immediately from your table, create a temporary table and select your records into it first, then select your record from the temporary table. rowid should then be correct.

The other option is to have another, non-key, column in your table which is the index of the record. But I guess that's kind of obvious and would indicate that I don't understand your problem fully!

b e n

 

[mysticwhiskey]

The other option is to have another, non-key, column in your table which is the index of the record. But I guess that's kind of obvious and would indicate that I don't understand your problem fully!

lazydog's solution sounds like it will do the job - have an integer column, called say ArrayIndex, and use this instead. That way you'll have full control of the value you're storing in it. Having it non-key means you won't get the constraint violation error you're getting when trying to update the value of rowid.

 

[Nutter]

I did consider that, but it seems inelegant when there's already a rowid column sitting there doing nothing, and I do essentially want the entries to be unique (this could help to catch programming errors).

Also, isn't it faster to search for entries by rowid? Perhaps this isn't the case if I add an index to my custom column...?

Maybe I'm trying to force SQLite to do something it wasn't really designed to do, and I should rethink my whole approach. What do you guys think? Is it going to be slow to constantly update this database to match my in-memory array? (I'm only worried about updating the array index - the rest of the data in the table can stay the same as long as the objects in the array are immutable.)

 

[lazydog]

If all you want to do is select the nth item in the table you could use a select statement with a limit and offset clause.

b e n

 

[fishcove]

If I understand your problem correctly, your only reason for keeping the db table in synch with your in-memory array is to re-create the array the next time you run. If this is the case, then go for a solution that is fast to update, since you'll be doing that a lot.

One approach is to add an additional column which contains the rowId of the entry's predecessor in the list. Then, when an insertion is made, you need only modify one entry, in addition to the newly created one.

At load time just select the table and order by predId.

 

[MacDonaldsd]

This SQL statement is probably completely wrong, as I only do nice easy things in SQL to use as the back-end to a PHP website.

The problem is that you need to increment the highest value row_id first (As you have already noticed).

UPDATE table SET rowid = rowid + 1 WHERE rowid =(Select * row_id from table WHERE rowid >= 5 Order By row_id DESC);

This probably doesn't work but i'm sure someone can adapt it to do the job.

 

[Nutter]

If all you want to do is select the nth item in the table you could use a select statement with a limit and offset clause.

But then how can I insert items into the database? By insert I mean NSMutableArray's definition of an insert, which involves bumping the array index of all following items.

If I understand your problem correctly, your only reason for keeping the db table in synch with your in-memory array is to re-create the array the next time you run.

That's right.

One approach is to add an additional column which contains the rowId of the entry's predecessor in the list. Then, when an insertion is made, you need only modify one entry, in addition to the newly created one. At load time just select the table and order by predId.

But the predId column won't be in the right order for restoring the array from disk!

Suppose I have a database with one entry: rowid = 1 and predid = 0.
I add an entry to the end of the array: rowid = 2 and predid = 1.
I add another entry: rowid = 3 and predid = 2.
Now, I want to insert an object at the second index. To do so I create a new entry (rowid = 4 and predid = 1) and update the second entry to point to the new row (rowid = 2 and predid = 4). My table now looks like this:

rowid|predid
1|0
2|4
3|2
4|1

Simply sorting by predid won't give me the right order. I'd have to start with the entry where predid = 0 and, from there, find the entry with predid = currentrowid, etcetera... not very efficient, surely.

 

[Nutter]

UPDATE table SET rowid = rowid + 1 WHERE rowid =(Select * row_id from table WHERE rowid >= 5 Order By row_id DESC);

Removing the "*" above and using "rowid" where you've written "row_id", this almost works - but it only updates the first (or rather the last) entry.

Something along these lines would be perfect... I'm trying to work it out for myself, but no joy yet...

 

[ChrisA]

When an object is inserted in the middle of the array, I need to update the database by incrementing the rowid column for all entries after the insertion point, before I can insert the new entry. For example:

No, don't do that. OK you could but it would be "dead dog slow" and the user will just hear the disk rumble and not much else will be happening.

Typically what someone does this, that is backs up an in-memory structure to a database they they write a "sync" or "close" function that will copy the in-memory stuff to the database.

The other thing about SQL databses, don't worry about the order of the rows. Order is always "undefined" and you only get them ordered when you say "order by..."

 

[ChrisA]

But then how can I insert items into the database? By insert I mean NSMutableArray's definition of an insert, which involves bumping the array index of all following items.

Can you step backwards, look at the larger picture and say "why"? Why are you doing this? Is this for an ticket resevation system and you are tracking seat asignments. Inspections as products come off an asembly line? What's the big picture here. My guess is that if you need to increment every number in a table something is wrong with the higher level design.

Well it could be a class homework asignment, they sometimes have you do things you would never do in "real life" in that case look at the SQL "update" statement. you basicall just say
"update mytable set mycolum to mycolum+1 where mycolum > insertpoint;" and that's it.

I've been doing this kind of database related work for a long time. One good rule is that when you think you have to do something this bad you have a conseptual problem with how you are representing the data. Just thinghow long it will take you to insert even 10,000 rows?

 

[fishcove]

But the predId column won't be in the right order for restoring the array from disk!

Suppose I have a database with one entry: rowid = 1 and predid = 0.
I add an entry to the end of the array: rowid = 2 and predid = 1.
I add another entry: rowid = 3 and predid = 2.
Now, I want to insert an object at the second index. To do so I create a new entry (rowid = 4 and predid = 1) and update the second entry to point to the new row (rowid = 2 and predid = 4). My table now looks like this:

rowid|predid
1|0
2|4
3|2
4|1

Simply sorting by predid won't give me the right order. I'd have to start with the entry where predid = 0 and, from there, find the entry with predid = currentrowid, etcetera... not very efficient, surely.

Sorry, you're right. However, you have the information you need to order the list. You could, for example, simply order by rowid and then traverse your in-memory array and rearrange it based on predid.

My point was it is better to incur a (small) one-time performance hit on load than to be constantly updating many rows in the db.

 

[MacDonaldsd]

Removing the "*" above and using "rowid" where you've written "row_id", this almost works - but it only updates the first (or rather the last) entry.

Something along these lines would be perfect... I'm trying to work it out for myself, but no joy yet...

Yeah thats what I meant to put.

If I was doing it in PHP I would save the inner select first into a temporary array. Then run the update statement for each entry in that array.

 

[lazydog]

Appending an item isn't a problem, but for the insert why not use a 2nd table. Select the first 'n' items from the original table and insert them into the temp table. Insert your new item, then select and insert the remaining items from the original table. When you're finished you can rename the tables so the 2nd table is now your original.

b e n

 

[Nutter]

Can you step backwards, look at the larger picture and say "why"? Why are you doing this? Is this for an ticket resevation system and you are tracking seat asignments. Inspections as products come off an asembly line? What's the big picture here.

I'm writing a couple of iPhone applications. Apple recommends that applications save changes as they happen, in order to reduce the processing time required when the application is asked to quit. I'm not entirely sure I go along with that line of thought, but I'm experimenting...

My guess is that if you need to increment every number in a table something is wrong with the higher level design.

This may be so, but bear in mind that I'm talking about fairly small arrays here (around 25 objects), and fairly infrequent updates.

My point was it is better to incur a (small) one-time performance hit on load than to be constantly updating many rows in the db.

Yeah, you're definitely right about that, but I'm not sure this particular scheme works in practice. When inserting an item I'd have to walk the linked list to work out which row's predid needs updating, so I'm not sure I'd gain any performance.

Thanks MacDonaldsd and lazydog, but creating temporary tables seems a bit excessive ... perhaps the original suggestion of setting up a non-unique column was the best idea. Or perhaps I should just stick to serialising the array when the app quits.

 

[MacDonaldsd]

This may be so, but bear in mind that I'm talking about fairly small arrays here (around 25 objects), and fairly infrequent updates.

If its so small then you can do it when the application quits. I want to do some iPhone applications but I have not got the time at the moment. Have Apple got documentation on SQLite ?

 

[Nutter]

No documentation, no objective-c wrapper. I've been using FMDB, which does the job nicely.

At the moment I'm only using SQLite as a cache for larger chunks of data, in order to avoid keeping everything in memory at once, and I think I'll stick to that after all.

 

[fishcove]

Yeah, you're definitely right about that, but I'm not sure this particular scheme works in practice. When inserting an item I'd have to walk the linked list to work out which row's predid needs updating, so I'm not sure I'd gain any performance.

If you're inserting after item i, you need to update item i+1 in your list. Do you need to walk the list to get to it?

Even if you do, this would be much faster than updating every row from i+1 to the end of the table in the db.