Is there a way to get the size of a table in SQLite in objective-C?

Question

I feel there's no easy way to do that ? Get each ManagedObject, get all the objects linked to it, and count the bytes of converted variables ?

Answer 1

Ok, I found the solution I need to get my db stats.

As suggested, I used SQL queries directly with the length() function. I can then later loop on to calculate total size in bytes.

First, in your XCode project, include 'libsqlite3.0.dylib'

Then, import the lib :

#import <sqlite3.h>

Finally the implementation could be :

float size;
sqlite3* dbHandle;
NSArray *paths =  NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];

NSString* dbFile = [documentsDirectory stringByAppendingPathComponent:@"mybase.sqlite"];

sqlite3_config(SQLITE_CONFIG_SERIALIZED);

int result = sqlite3_open([dbFile UTF8String], &dbHandle);
if (result != SQLITE_OK) {
    NSLog(@"Failure in connecting to the database with result %d",result);
}
else {
    NSLog(@ "Successfully opened connection to DB") ;

    NSString    *query  =   @"SELECT length(HEX(zmyField))/2 FROM zmyTable";
// You have to append 'z' to table and name field due to SQLite naming convention (cf.https://softwareengineering.stackexchange.com/questions/178994/why-do-core-data-sqlite-table-columns-start-with-z)

    const char *sql3 = [[NSString stringWithFormat:query] cStringUsingEncoding:NSUTF8StringEncoding];

    NSMutableArray *results = [NSMutableArray array];

    sqlite3_stmt *sql1Statement;
    int returnCode;
    if(sqlite3_prepare_v2(dbHandle, sql3, -1, &sql1Statement, NULL) != SQLITE_OK)
    {
        NSLog(@"Problem with prepare statement: %s", sqlite3_errmsg(dbHandle));
    }
    else
    {

        while ((returnCode = sqlite3_step(sql1Statement)) == SQLITE_ROW) {
            NSLog(@"SIZE : %f",sqlite3_column_double(sql1Statement, 0));
            size+=sqlite3_column_double(sql1Statement, 0);
        }
        if (returnCode != SQLITE_DONE)
            NSLog(@"Problem with step statement: %s", sqlite3_errmsg(dbHandle));

        sqlite3_finalize(sql1Statement);
    }
}
NSLog(@"TOTAL SIZE for myField in my Table : %f",size);

To get total db size, or table by table, even field by field, you can use SQLite instructions to list all table and field names. You then apply the length() function to the loop result :

For tables listing :

SELECT name FROM sqlite_master WHERE type='table'

For fields :

PRAGMA table_info('myTable')

Ref :

http://www.priyaontech.com/2011/09/intro-to-sqlite-for-ios-developers/

How do I find the length (size) of a binary blob in sqlite

https://softwareengineering.stackexchange.com/questions/178994/why-do-core-data-sqlite-table-columns-start-with-z

How to get a list of column names on sqlite3 / iPhone?