Does copying sql database onto user's phone = 2 copies?

[zardon]

I have a question about copying a sqlite database onto a user's phone?

If you copy a database onto a user's phone, does this mean there are 2 copies of the database, or are you simply moving it?

Also, let's assume the following steps.

1. User launches app for first time
2. The application copies over the .sql database to the user's phone.

Does this mean there are 2 copies now?

Does it mean that the program can rollback to the old database?

Let's assume you have a database of contacts, but you do not want the core content to be touched (essentially its a read-only database) -- so you would copy the database, but now this means there are 3 copies of the database?

Thanks for your help?

[shahab74]

Originally Posted by zardon

I have a question about copying a sqlite database onto a user's phone?

If you copy a database onto a user's phone, does this mean there are 2 copies of the database, or are you simply moving it?

Also, let's assume the following steps.

1. User launches app for first time
2. The application copies over the .sql database to the user's phone.

Does this mean there are 2 copies now?

Does it mean that the program can rollback to the old database?

Let's assume you have a database of contacts, but you do not want the core content to be touched (essentially its a read-only database) -- so you would copy the database, but now this means there are 3 copies of the database?

Thanks for your help?

If I understand you properly then why not you just put a logic where in your appdelegate check if the sql already exist if not then copy it.
Something along these lines:

- (void) copyDatabaseIfNeeded {
	
	//Using NSFileManager we can perform many file system operations.
	NSFileManager *fileManager = [NSFileManager defaultManager];
	NSError *error;
	NSString *dbPath = [self getDBPath];
	BOOL success = [fileManager fileExistsAtPath:dbPath]; 
	
	if(!success) {
		
		NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"<yourdb>.sqlite"];
		success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
		
		if (!success) 
			NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
	}	
}

- (NSString *) getDBPath {
	
	//Search for standard documents using NSSearchPathForDirectoriesInDomains
	//First Param = Searching the documents directory
	//Second Param = Searching the Users directory and not the System
	//Expand any tildes and identify home directories.
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	return [documentsDir stringByAppendingPathComponent:@"<yourdb>.sqlite"];
}

and in the applicationdidfinishlaunching call this

- (void)applicationDidFinishLaunching:(UIApplication *)application {
	
	//Copy database to the user's phone if needed.
	[self copyDatabaseIfNeeded];
	
	// Configure and show the window
	[window addSubview:[navigationController view]];
	[window makeKeyAndVisible];
}

[Stubborn Dreams]

Yes, you will have multiple copies but one is truly read-only since it is locked inside your .app bundle.

I've found this debug statement to be helpful in locating your persistent SQL store when you run it in the simulator. The simulator acts as the phone will - unless you reset the simulator and/or delete the app from the simulator - the database will remain in place (once you copy it into your Documents directory).

NSLog( @"Using persistent store(s):\n%@",
		  [[[managedObjectContext persistentStoreCoordinator] persistentStores] valueForKeyPath:@"URL.path"] );

Prints out something like:

"/Users/<your user name>/Library/Application Support/iPhone Simulator/<the iOS version e.g. 4.0.1>/Applications/<UUID>/Documents/storedata.sqlite"

[zardon]

Oh that helps a lot, thanks a lot!

I have a question. Lets say you wanted to specifically create a database via App Delegate from the database that is preloaded with the phone.

IE:
Start game = Create a new database ('savegame.db') from the preloaded database ('readonly.db') and drop any ('savegame.db') that might already exist. In other words, never use 'readonly.db' as a resource except for reading.

Continue game = Load the ('savegame.db')

I have another question relating sqlite.

Can you combine primary key (INTEGER PRIMARY KEY) with the indexes, or rather should you?

Background

I am learning SQLite and seeing how it differs to MySQL, and I downloaded an app and explored its sql database.

The one thing I don't understand is why the programmer opted to use indexes rather than the primary keys.

According to the docs on sqlite, "The PRIMARY KEY attribute normally creates a UNIQUE index on the column or columns that are specified as the PRIMARY KEY."

In the database I am looking at, the programmer:

1. Does not use primary keys at all
2. Has an index for every primary key. ie:

CREATE UNIQUE INDEX admin_flags_pk_index ON admin_flags(flag_id ASC)

So, my question is -- can someone clarify whether a unique index needs to be created for primary key's?