iPhone SDK: loading UITableView from SQLite

Question

Hi, I think I got a good handle on UITableViews and on getting/inserting data from/to SQLite db. I am straggling with an architectural question.

My application saves 3 values int the database, there can be many/many rows. However would I load them in the table?

From all the tutorials I have seen, at one point entire database is loaded in the NSMutableArray or similar object via performing SELECT statement.

Then when

-(UITableViewCell *) tableView: (UITableView *) tableView cellForRowAtIndexPath: (NSIndexPath *) indexPath

called, rows required are dolled out from the previous loaded NSMutableArray (or similar stracture).

But what i have have thousands for rows? Why would I pre-load them?

Should I just query database each time cellForRowAtIndexPath is called? If so, what would I use as an index?

Each row in the table will have an AUTOINCREMENT index, but since some rows may be deleted index will not correspond to rows in the table (in the SQL I may have something like this with row with index 3 missing):

1 Data1 Data1 2 Data2 Data2 4. data3 data3

Thanks

Answer 1

You can always do one select statement to fetch just the 'autoincrement indices' in the order you want them, and keep them in an array (you'll have an array with thousands of integers, should be fine). Then, in 'cellForRowAtIndexPath', get the right 'autoincrement index' from your array and fetch all the data you need (I'm calling 'id' your 'autoincrement index' below...).

NSArray *ids;   // Put the result of 'select id from datatable' here

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    // Fetch full row where autoincrement index is: [ids objectAtIndex:indexPath.row]

Of course, you can even use a int[] (instead of NSArray* ) if you want to save on calls to intValue and such... You can allocate your int[] using the result of 'select count(*) from datatable'

Answer 2

I solve this by reading what the table cell needs from my db into the datasource array, of all the existing db entries. The objects don't get removed from the array however, they stay there unless they need to be removed.

For example one of my apps reads 1'700 rows, for each row creates an object, assigns an NSUInteger (the autoincrement value) and an NSString (the name of the object, which will be displayed in the cell) and puts them into the datasource array. This whole process takes only about 200-300 milliseconds - you'll have to test whether it takes too long for 10'000+ entries, but for some thousand entries it sould be okay to just read it all. I remember that memory consumption is also quite low, can't look up how much exactly ATM.

Then, when the user taps a row, I query the datasource array to find the object he just tapped, and this object then loads all its other values from the database, which it can do since it knows its own database key/id (it "hydrates" itself).

For completeness sake:

query "SELECT my_key, my_name FROM table"
foreach row from query {
    MyObj *obj = [MyObj obj];
    obj.id = row.my_key;
    obj.name = row.my_name;

    [datasourceArray addObject:obj];
}

Answer 3

Would limit and offset work?

Answer 4

Here is what I did, which worked perfectly (i have tested with 3k rows in the DB). The app is EZBudget

1. I read only indexes into the array, but I do so for entire table, however many rows it has
2. In the -(UITableViewCell *) tableView: (UITableView *) tableView cellForRowAtIndexPath: (NSIndexPath *) indexPath in do SELECT on the indexPath as index only
3. If certain rows needs to be deleted (a transaction is deleted) I delete index from the array and I delete corresponding row from the table
4. When record is added in the table, I get SQLite returns the autoincremented index for the newly added record. I take this index and add to the index array

So my index array is always in sync with the entire table. At any point I can SELECT entire record by index in the array.

Thanks everyone for suggestions!