What is the best way to write a previously created Sqlite database to documents in iOS

Question

I have a previously created Sqlite database file that I am bundling with an iOS application. My question is what is the best way to write this data to the phone so that I may alter the tables from the application there on?

My research indicates that I may need to loop through the entire .sql file and insert/create the necessary tables and values on the phone manually.

Is there no way to just write the database to internal storage and then manipulate it from then on?

score 3 · Accepted Answer · answered Oct 25 '11 at 00:45

You can add it to your supporting files in the project and then copy the file as a template db if it doesn't exist and then open it. Once you open it you can do what you want with it.

Here's some sample code from a sample project I had that does this. In my sample it's a contact database. Notice how ensurePrepared will copy it from the bundle if it doesn't exist. I copy to library path but you can copy to documents path.

- (BOOL)ensureDatabaseOpen: (NSError **)error
{
    // already created db connection
    if (_contactDb != nil)
    {
        return YES;
    }

    NSLog(@">> ContactManager::ensureDatabaseOpen");    
    if (![self ensureDatabasePrepared:error])
    {
        return NO;
    }

    const char *dbpath = [_dbPath UTF8String]; 
    if (sqlite3_open(dbpath, &_contactDb) != SQLITE_OK &&
        error != nil)
    {
        *error = [[[NSError alloc] initWithDomain:@"ContactsManager" code:1000 userInfo:nil] autorelease];
        return NO;
    }

    NSLog(@"opened");

    return YES;
}

- (BOOL)ensureDatabasePrepared: (NSError **)error
{
    // already prepared
    if ((_dbPath != nil) &&
        ([[NSFileManager defaultManager] fileExistsAtPath:_dbPath]))
    {
        return YES;
    }

    // db in main bundle - cant edit.  copy to library if !exist
    NSString *dbTemplatePath = [[NSBundle mainBundle] pathForResource:@"contacts" ofType:@"db"];
    NSLog(@"%@", dbTemplatePath);

    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    _dbPath = [libraryPath stringByAppendingPathComponent:@"contacts.db"];

    NSLog(@"dbPath: %@", _dbPath);

    // copy db from template to library
    if (![[NSFileManager defaultManager] fileExistsAtPath:_dbPath])
    {
        NSLog(@"db not exists");
        NSError *error = nil;
        if (![[NSFileManager defaultManager] copyItemAtPath:dbTemplatePath toPath:_dbPath error:&error])
        {
            return NO;
        }

        NSLog(@"copied");
    }    

    return YES;    
}

score 2 · Answer 2 · edited May 23 '17 at 12:12

2

Store the database in the bundle (in the project). In the App Delegate copy the database to the Documents directory (only do this if the database file doesn't already exist in the documents directory otherwise you will always be overwriting your file). In your code always reference the copy in the Document's directory.
Also read this SO Question for some code on how to do the copy.

Voila.

edited May 23 '17 at 12:12

Community

1
1

answered Oct 25 '11 at 00:43

Walter

5,867
2
30
43

I would be wary of the appDelegate copying model data storage. the app delegate should have no specific knowledge of how the model is storing data ... – bryanmac Oct 25 '11 at 04:26
I offered the appDelegate because that's where Apple's Core Data template sets it up. – Walter Oct 25 '11 at 11:12

What is the best way to write a previously created Sqlite database to documents in iOS

2 Answers2