[TIMOB-12291] iOS: Return SQLite query result set as Javascript Object

GitHub Issue	n/a
Type	New Feature
Priority	Low
Status	Open
Resolution	Unresolved
Affected Version/s	n/a
Fix Version/s	n/a
Components	iOS
Labels	n/a
Reporter	Pedro Enrique
Assignee	Unknown
Created	2013-01-15T15:26:21.000+0000
Updated	2018-02-28T20:03:34.000+0000

Description

When looping through the result set of an SQLite query, we need to cross the bridge every time to get the results, this can be slow and painful for big databases. It would be nice to get all the results in a JS object and then work from there. This would be ideal:

    var rows = db.execute('SELECT * FROM people');

    var results = rows.asJSON();

This would need to be added to TiDatabaseResultSetProxy.m

-(NSDictionary *)dictionaryFromCurrentField
{
    NSMutableDictionary *dict = [NSMutableDictionary dictionary];
    for(NSString *field in [results fieldNames])
    {
        [dict setObject:[results objectForColumn:field] forKey:field];
    }
    return dict;
}

-(id)asJSON:(id)args
{
    NSMutableArray *ar = [NSMutableArray array];
    if (results != nil && validRow)
    {
        [ar addObject:[self dictionaryFromCurrentField]];
        while([results next])
        {
            [ar addObject:[self dictionaryFromField]];
        }
    }
    return ar;
}

Comments

Sergey Nosenko 2013-07-09

+1 for this. For example you need to do this at js side. In general you should do FOUR "over board" proxy calls for each record. isValidRow getFieldName getField next so if each call is about 1-10ms at iPhone3GS for just 1000 records it will take about 3-4 SECONDS :( :( :( UPDATE: just tested code above with 1000 records on iPhone 4. case 1: with default style isValidRow, then getFieldName, getField, next (then create backboneModel object for each) - 3200 miliseconds case 2: as asJSON (then create backboneModel object for each) - 800 miliseconds amazing performance improvement!

Matt Langston 2013-07-09

This looks fine but we should also allow for "chunking" the data. For example, we should also allow for this use case:

       var rows = db.execute('SELECT * FROM people');
       var count = rows.count;
   
       // Ger first half of results.
       var results = rows.asJSON(count / 2);
       // process first half of results
   
       // Get remaining rows.
       results = rows.asJSON();

Sergey Nosenko 2013-07-09

Nice idea. btw, I'm going to create modules (iOS and Android) with this feature, because I can't wait and need it for my applications. (3-4 seconds for 1000 records it's very slow). UPDATE: I published module for IOS here: https://github.com/darknos/TiAdvancedDatabase and I hope I didn't break any licenses agreements.
Martijn Kooij 2014-02-07

Brilliant! I would prefer if this would be officially included in the SDK, but for now I am definitely including and using this in my app. Thanks!

Martijn Kooij 2014-02-07

The code needs a small addition to take care of the BLOB data type as well.

   -(NSDictionary *)dictionaryFromCurrentField
   {
       NSMutableDictionary *dict = [NSMutableDictionary dictionary];
       for(NSString *field in [results fieldNames])
       {
   	    id result = [results objectForColumn:field];
   		if ([result isKindOfClass:[NSData class]]) {
   			result = [[[TiBlob alloc] initWithData:result mimetype:@"application/octet-stream"] autorelease];
   		}
           [dict setObject:result forKey:field];
       }
       return dict;
   }

Martijn Kooij 2014-11-20

Any update on this? I would really prefer just having this included in the default SDK instead of modifying it myself... And a new problem arises as I do also want to start supporting Android, which I can't if I have completely different methods of retrieving data... This issue should not only target iOS. 1. Please include the suggested modification in the SDK. 2. Please also implement for Android.

JSON Source