SQLite: incrementing the rowid of multiple rows.

Discussion in 'Mac Programming' started by Nutter, Apr 28, 2008.

  1. Nutter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #1
    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?
     
  2. lazydog macrumors 6502a

    Joined:
    Sep 3, 2005
    Location:
    Cramlington, UK
    #2
    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
     
  3. mysticwhiskey macrumors newbie

    Joined:
    Mar 31, 2008
    #3
    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.
     
  4. Nutter thread starter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #4
    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.)
     
  5. lazydog macrumors 6502a

    Joined:
    Sep 3, 2005
    Location:
    Cramlington, UK
    #5
    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
     
  6. fishcove Guest

    #6
    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.
     
  7. MacDonaldsd macrumors 65816

    MacDonaldsd

    Joined:
    Sep 8, 2005
    Location:
    London , UK
    #7
    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.
     
  8. Nutter thread starter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #8
    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.

    That's right.

    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.
     
  9. Nutter thread starter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #9
    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...
     
  10. ChrisA macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #10
    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..."
     
  11. ChrisA macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #11
    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?
     
  12. fishcove Guest

    #12
    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.
     
  13. MacDonaldsd macrumors 65816

    MacDonaldsd

    Joined:
    Sep 8, 2005
    Location:
    London , UK
    #13
    Yeah thats what I meant to put. :D

    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.
     
  14. lazydog macrumors 6502a

    Joined:
    Sep 3, 2005
    Location:
    Cramlington, UK
    #14
    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
     
  15. Nutter thread starter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #15
    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...

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

    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.
     
  16. MacDonaldsd macrumors 65816

    MacDonaldsd

    Joined:
    Sep 8, 2005
    Location:
    London , UK
    #16
    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 ?
     
  17. Nutter thread starter macrumors 6502

    Joined:
    Mar 31, 2005
    Location:
    London, England
    #17
    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.
     
  18. fishcove Guest

    #18
    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.
     

Share This Page