how to create a sqlite

Question

how to create a sqlite file when the application starts (didFinishLaunchingWithOptions) the test if it already or not exsist otherwise create the file sqlite

Answer 1

Like this... the sqlPath variable is the path to the pre-made sql database on your ressource

- (void) checkAndCreateSQL
{
 if (![[NSFileManager defaultManager] fileExistsAtPath:[documentPath stringByAppendingString:@"/database.sql"]]) {
 [[NSFileManager defaultManager] createFileAtPath:[documentPath stringByAppendingString:@"/database.sql"] 
 contents:[NSData dataWithContentsOfFile:sqlPath]
 attributes:nil];
 }
}

EDIT 1:

You can create the database on your mac using this command line :

sqlite3 database.sql < DATABASE_CREATION.txt

in the DATABASE_CREATION.txt something like this :

CREATE TABLE IF NOT EXISTS `group` (
 `id` integer PRIMARY KEY,
 `name` text,
 `position` integer
);

Then put directly the database.sql file into your project resource. (like an image)

• marzapower: But in this case you assume that the file to be created is already present, since you use a call to [NSData dataWithContentsOfFile:]. Where should sqlPath be?
• marzapower: The handling of databases, unless where required by highly specific tasks, should be realized using the Core Data libraries, I think, since for using them you are not bound to specific SQL language implementation and specific database characteristics.
• TheSquad: Oh yeah, sorry... I mixed up names ;-)

Answer 2

You'd probably want to use the default Core Data libraries instead of manually creating and handling a single sqlite file. Please check the official Apple Core Data Programming Guide. It will automatically handle the creation and update of the inner database in the app.

• TheSquad: I agree that Core Data should be in most case the API used, but sometimes you need a database with primary and unique keys, you can not just deal with Core Data without hassle
• marzapower: If Core Data couldn't easily handle unique or primary keys ... it wouldn't be there. It just uses an approach that is fully SQL-independent, as happens in Hibernate, for example.

Answer 3

sqlite3 *reference2Database() {
if (_database == nil) {
 // First, test for existence.
 NSError *error;
 NSFileManager *fileManager = [NSFileManager defaultManager];
 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 NSString *documentsDirectory = [paths objectAtIndex:0];
 NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"my.sqlite"];
 if ([fileManager fileExistsAtPath:writableDBPath] == NO) {
 // Database file doesnt exists. Copy the database at writable path (documents directory).
 NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"my.sqlite"];
 [fileManager removeItemAtPath:writableDBPath error:nil];
 BOOL databaseCopied = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
 if (!databaseCopied) {
 // Handle the error...
 }
 }else {
 // Open the database. The database was prepared outside the application.
 if (sqlite3_open([writableDBPath UTF8String], &_database) != SQLITE_OK) {
 // Even though the open failed, call close to properly clean up resources.
 sqlite3_close(_database);
 _database = nil;
 // Additional error handling, as appropriate...
 }
 }
}
return _database;
}

// Sample usage.

-(void) someDatabaseFunction {
 sqlite3 *database = reference2Database();
 // Do something with "database"...
}

// Close the database. This should be called when the application terminates.

void closeDatabase() {
if (_database == nil) return;
// Close the database.
if (sqlite3_close(_database) != SQLITE_OK) {
 // Handle the error...
}
_database = nil;

}

NOTE: At the top of the file, you should have: static sqlite3 *_database = nil;

Answer 4

I use Matteo Bertozzi's SQLite Wrapper to create my sqlite database with the following code:

-(void)checkDatabase 
{
 if([[NSFileManager defaultManager] fileExistsAtPath:DBPATH] == NO)
 {
 sqlite = [[Sqlite alloc] init];
 if (![sqlite open:DBPATH]) return;
 [sqlite executeNonQuery:@"DROP TABLE yourtable"];
 [sqlite executeNonQuery:@"CREATE TABLE yourtable (record1 TEXT NOT NULL, 
 record2 TEXT NOT NULL, 
 record3 TEXT NOT NULL, 
 record4 TEXT NOT NULL);"];
 NSArray *results = [sqlite executeQuery:@"SELECT * FROM yourtable;"];
 for (NSDictionary *dictionary in results) {
 for (NSString *key in [dictionary keyEnumerator])
 NSLog(@" - %@ %@", key, [dictionary objectForKey:key]);
 }
 [results release];
 [sqlite release];
 }
}