replace / load database on iphone

Question

My simulator runs fine and fast. My iphone seems to be freezing at a part where I try to create and fill a database. However i prefer to use the database from the simulator and put that on the iphone so the user doesn't have to recreate the database. What i like to know is how can i load from the database added to the folders.

I searched a lot but either it is outdated or different from what i want. I added the database file now from the finder into the xcode project. So if I'm correct I have to change _databasePath to point to wherever the file is, am I correct?

And if so where is it, the one from the code is here: /var/mobile/Applications/65B5541A-1E73-46F6-AB5A-C5988003103E/Documents/paths.db But that is no the one i dragged into xcode. Also i looked at organizer, i can see there documents/paths.db but since it misses other files i also assume that that is the code created db and not the dragged in. I tried to delete it as well but i can't select it.

can someone help?

in header:

@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *pathDB;

in .m:

- (void) createDataBaseIfNotExist {

    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    // Build the path to the database file
    _databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent:@"paths.db"]];

    NSLog(@"databasePath: %@", _databasePath);

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: _databasePath] == NO) {

        const char *dbpath = [_databasePath UTF8String];

        if(sqlite3_open(dbpath, &_pathDB) == SQLITE_OK) {
            char *errMsg;
            const char *sql_stmt =
            "CREATE TABLE IF NOT EXISTS Paths (ID INTEGER PRIMARY KEY AUTOINCREMENT, START INTEGER, END INTEGER, DISTANCE REAL, NODES TEXT)";

            if (sqlite3_exec(_pathDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                //_status.text = @"Failed to create table";
                NSLog(@"Failed to create table");

            }
            sqlite3_close(_pathDB);
        } else {
            // _status.text = @"Failed to open/create database";
            NSLog(@"Failed to open/create database");
        }
    }

}

Answer 1

So, a couple of things:

1. You first need to modify the createDatabaseIfNotExist to copy from the bundle if it's not found in Documents:

   - (void) createDataBaseIfNotExist {
   
       // Get the documents database path
       NSString *docsDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
       self.databasePath = [docsDir stringByAppendingPathComponent:@"paths.db"]; // always use setter when setting property's value
   
       NSFileManager *fileManager = [NSFileManager defaultManager];
   
       if ([fileManager fileExistsAtPath:_databasePath] == NO) {
   
           // if the database doesn't exist in documents, look for it in the bundle and copy it if found
   
           // get the bundle database path
           NSString *bundleDatabasePath = [[NSBundle mainBundle] pathForResource:@"paths" ofType:@"db"];
   
           if (bundleDatabasePath) {
               // if we successfully copied from bundle, then quit
   
               if ([fileManager copyItemAtPath:bundleDatabasePath toPath:self.databasePath error:nil])
                   return;
           }
   
           // otherwise, let's proceed with creating the database
   
           if(sqlite3_open([_databasePath UTF8String], &_pathDB) == SQLITE_OK) {
               char *errMsg;
               const char *sql_stmt = "CREATE TABLE IF NOT EXISTS Paths (ID INTEGER PRIMARY KEY AUTOINCREMENT, START INTEGER, END INTEGER, DISTANCE REAL, NODES TEXT)";
   
               if (sqlite3_exec(_pathDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {
                   //_status.text = @"Failed to create table";
                   NSLog(@"Failed to create table, %s", errMsg);
                   sqlite3_free(errMsg); // if you're going to use that fifth parameter, you must free it when you're done
               }
               sqlite3_close(_pathDB);
           } else {
               // _status.text = @"Failed to open/create database";
               NSLog(@"Failed to open/create database");
           }
       }
   }
   

2. Second, once you've run this once on the simulator, find the database in the simulator's Documents folder to your Xcode project. The simulator's files can be found in

   ~/Library/Application Support/iPhone Simulator/6.1/Applications/XXX/Documents

   where XXX is the cryptic identifier (e.g. 85206BA6-9D03-4F18-BB0A-3B8C25B552C4). Note, by default, the Library folder is hidden, so I go to a Terminal command line and type in the following command to show it:

   chflags nohidden Library
   

3. You can then add the database back to your project by dragging from Finder to Xcode's file navigator window, at which point you'll see a window like:

   

   Make sure to check the two highlighted checkmarks to ensure that the database will be included in the bundle.

Two final observations:

• Now that you have a "copy from bundle if necessary logic", it's an interesting question whether you really want the code to create the table in code at all anymore. Personally, I always just create my databases with a nice Mac graphical SQLite tool and then copy them to my project. The only time I do programmatic creating of tables is when (a) I'm deploying an update which involves new/altered tables; and (b) the user's database might contain some key data that I don't want to simply replace with the database from the bundle.

• I personally always include a configuration table in my app which contains a single row for which one of the columns is the database version. Thus, my app will open the database from documents, check the version number, and if out of date (because the user only just recently upgraded their app) then update the database. This "database version number" logic is something that you really want to get in place as part of version 1.0 of your app.