Creating SQLite Data Base For iPhone Apps.

#import
#import

@interface todoAppDelegate : NSObject {

IBOutlet UIWindow *window;
IBOutlet UINavigationController *navigationController;

sqlite3 *database;
NSMutableArray *todos;
}

@property (nonatomic, retain) UIWindow *window;
@property (nonatomic, retain) UINavigationController *navigationController;
@property (nonatomic, retain) NSMutableArray *todos;

@end


#import "todoAppDelegate.h"
#import "RootViewController.h"
#import "Todo.h"

@interface todoAppDelegate (Private)
- (void)createEditableCopyOfDatabaseIfNeeded;
- (void)initializeDatabase;
@end

@implementation todoAppDelegate

@synthesize window;
@synthesize navigationController;
@synthesize todos;

- (id)init {
if (self = [super init]) {
//
}
return self;
}


- (void)applicationDidFinishLaunching:(UIApplication *)application {

[self createEditableCopyOfDatabaseIfNeeded];
[self initializeDatabase];

// Configure and show the window
[window addSubview:[navigationController view]];
[window makeKeyAndVisible];
}

// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded {

// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"todo.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)
return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"todo.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {

NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {

NSMutableArray *todoArray = [[NSMutableArray alloc] init];
self.todos = todoArray;
[todoArray release];
// The database is stored in the application bundle.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"todo.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
// Get the primary key for all books.
const char *sql = "SELECT pk FROM todo";
sqlite3_stmt *statement;
// Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
// The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
// We "step" through the results - once for each row.
while (sqlite3_step(statement) == SQLITE_ROW) {
// The second parameter indicates the column index into the result set.
int primaryKey = sqlite3_column_int(statement, 0);
// We avoid the alloc-init-autorelease pattern here because we are in a tight loop and
// autorelease is slightly more expensive than release. This design choice has nothing to do with
// actual memory management - at the end of this block of code, all the book objects allocated
// here will be in memory regardless of whether we use autorelease or release, because they are
// retained by the books array.
Todo *td = [[Todo alloc] initWithPrimaryKey:primaryKey database:database];
[todos addObject:td];
[td release];
}
}
// "Finalize" the statement - releases the resources associated with the statement.
sqlite3_finalize(statement);
} else {
// Even though the open failed, call close to properly clean up resources.
sqlite3_close(database);
NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
// Additional error handling, as appropriate...
}

//NSLog([[NSString alloc] initWithFormat:@"size:%@",[[todos objectAtIndex:1] text]]);
}


- (void)applicationWillTerminate:(UIApplication *)application {
// Save data if appropriate
}


- (void)dealloc {
[navigationController release];
[window release];
[super dealloc];
}

@end


#import

@interface RootViewController : UITableViewController {

}

@end


#import "RootViewController.h"


@implementation RootViewController


- (void)viewDidLoad {

}


- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {

return 1;
}


- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {

return 0;
}


- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

static NSString *MyIdentifier = @"MyIdentifier";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:MyIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:MyIdentifier] autorelease];
}

// Set up the cell
return cell;
}


- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {

// Navigation logic
}
- (void)viewWillAppear:(BOOL)animated {

[super viewWillAppear:animated];
}

- (void)viewDidAppear:(BOOL)animated {

[super viewDidAppear:animated];
}

- (void)viewWillDisappear:(BOOL)animated {
}

- (void)viewDidDisappear:(BOOL)animated {
}


- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation {

// Return YES for supported orientations
return (interfaceOrientation == UIInterfaceOrientationPortrait);
}


- (void)didReceiveMemoryWarning {

[super didReceiveMemoryWarning]; // Releases the view if it doesn't have a superview
// Release anything that's not essential, such as cached data
}


- (void)dealloc {

[super dealloc];
}


@end


#import
#import

@interface Todo : NSObject {
sqlite3 *database;
NSInteger primaryKey;
NSString *text;
}

@property (assign, nonatomic, readonly) NSInteger primaryKey;
@property (nonatomic, retain) NSString *text;

- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db;

@end


#import "Todo.h"

static sqlite3_stmt *init_statement = nil;

@implementation Todo
@synthesize primaryKey,text;

- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db {

if (self = [super init]) {

primaryKey = pk;
database = db;
// Compile the query for retrieving book data. See insertNewBookIntoDatabase: for more detail.
if (init_statement == nil) {
// Note the '?' at the end of the query. This is a parameter which can be replaced by a bound variable.
// This is a great way to optimize because frequently used queries can be compiled once, then with each
// use new variable values can be bound to placeholders.
const char *sql = "SELECT text FROM todo WHERE pk=?";
if (sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}
// For this query, we bind the primary key to the first (and only) placeholder in the statement.
// Note that the parameters are numbered from 1, not from 0.
sqlite3_bind_int(init_statement, 1, primaryKey);
if (sqlite3_step(init_statement) == SQLITE_ROW) {
self.text = [NSString stringWithUTF8String:(char *)sqlite3_column_text(init_statement, 0)];
} else {
self.text = @"Nothing";
}
// Reset the statement for future reuse.
sqlite3_reset(init_statement);
}
return self;
}

@end