New SQLite3 handling library for iPhones and CoCoa now under development

This library is more suitable for smaller databases as right now is automatically transforming all the data from select into the NSMutableArray where every row has it's Own NSMutableDictionary. This is not the best idea if you have loads of data, because that you have all the data in the variable and that means that it eats all your memory and application crashes.

To get some data from database is as easy as that:

NSMutableArray *arr = [conn getData:@"SELECT * FROM testTable;" fromDatabase:@"testDatabase"];

http://code.google.com/p/sqlite3connector/

Now I'll show you how easy is to connect to the database and retrieve some data.

//
//  ContactsController.h
//  TestApp
//
//  Created by Ondrej Rafaj on 26.10.09.
//  Copyright 2009 Home. All rights reserved.
//

#import 
#import "SQLite3Connector.h"

@interface ContactsController : UIViewController {
	IBOutlet UITableView *table;
	IBOutlet UIActivityIndicatorView *preloader;
	NSMutableArray *data;
	SQLite3Connector *conn;
}

@property (nonatomic, retain) IBOutlet UITableView *table;
@property (nonatomic, retain) IBOutlet UIActivityIndicatorView *preloader;
@property (nonatomic, retain) NSMutableArray *data;

@end

We've included the SQLite3Connector class into the project and we've created tableView and activity indicator as a preloader, than we have the data variable where we are going to have all the results from the database. Last thing is a class SQLite3Connector which allows you to do all the queries.

//
//  ContactsController.m
//  TestApp
//
//  Created by Ondrej Rafaj on 26.10.09.
//  Copyright 2009 Home. All rights reserved.
//

#import "ContactsController.h"


@implementation ContactsController

@synthesize table, preloader, data;

/*
- (id)initWithStyle:(UITableViewStyle)style {
    // Override initWithStyle: if you create the controller programmatically and want to perform customization that is not appropriate for viewDidLoad.
    if (self = [super initWithStyle:style]) {
    }
    return self;
}
*/



- (void) importDataFromContacts {
	// creating fake data for import
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Ondrej', 'Rafaj');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Hana', 'Cerna');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Andrew', 'Walker');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Greg', 'Jarrett');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Chris', 'Doull');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Malc', 'Seaborn');" inDatabase:@"contacts"];
	[conn executeQuery:@"INSERT INTO contacts (name, surname) VALUES ('Jidh', 'George');" inDatabase:@"contacts"];
}





- (void)viewDidLoad {
    [super viewDidLoad];
	[preloader startAnimating];
	conn = [[SQLite3Connector alloc] init];
	if ([SQLite3Connector databaseExists:@"contacts"]) [SQLite3Connector dropDatabase:@"contacts"];
	NSString *query = @"";
	query = @"CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, surname VARCHAR);";
	if (![conn executeQuery:query inDatabase:@"contacts"]) NSAssert1(0, @"Error creating database. (%@)", query);
	[self importDataFromContacts];
	NSLog(@"Creating database & importing contacts");
	data = [conn getData:@"SELECT * FROM contacts ORDER BY surname ASC;" fromDatabase:@"contacts"];
	[table reloadData];
	[preloader stopAnimating];
}

- (void)didReceiveMemoryWarning {
	// Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];
	
	// Release any cached data, images, etc that aren't in use.
}

- (void)viewDidUnload {
	// Release any retained subviews of the main view.
	// e.g. self.myOutlet = nil;
}


#pragma mark Table view methods

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
    return 1;
}


// Customize the number of rows in the table view.
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    return [data count];
}


// Customize the appearance of table view cells.
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    
    static NSString *CellIdentifier = @"Cell";
    
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) {
        cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];
    }
    
	int index = [indexPath indexAtPosition: [indexPath length] - 1];
    cell.textLabel.text = [NSString stringWithFormat:@"%@, %@", [[data objectAtIndex: index] objectForKey: @"surname"], [[data objectAtIndex: index] objectForKey: @"name"]];
	
    return cell;
}


- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
    // Navigation logic may go here. Create and push another view controller.
	// AnotherViewController *anotherViewController = [[AnotherViewController alloc] initWithNibName:@"AnotherView" bundle:nil];
	// [self.navigationController pushViewController:anotherViewController];
	// [anotherViewController release];
}

- (void)dealloc {
	[table, preloader, data release];
    [super dealloc];
}


@end

Check out the importDataFromContacts and viewDidLoad functions ... here you can see that the handling SQLite is quite piece of cake.

All the source codes are available on the google code here:

svn checkout http://sqlite3connector.googlecode.com/svn/trunk/ sqlite3connector-read-only

Here is a little bit more extended example of what you can do with it, please don't worry about the printResults function, I just took part of the example project as I don't have a time right now to prepare anything better (I want to go home, I'm still stucked in the office and right now is 21:45), ok here we go:

- (void) printResults:(NSString *)text {
	counter++;
	textView.text = [NSString stringWithFormat:@"%d) %@\r\n\r\n%@", counter, text, textView.text];
}

// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad {
    [super viewDidLoad];
	counter = 0;
	SQLite3Connector *conn = [[SQLite3Connector alloc] init];
	//[conn getData:@"" fromDatabase:@""];
	NSString *queryCount = @"SELECT COUNT(id) FROM testTable;";
	[self printResults:[NSString stringWithFormat:@"Directory path: %@", [SQLite3Connector getDocumentsDirectoryPath]]];
	[self printResults:[NSString stringWithFormat:@"Database folder path: %@", [SQLite3Connector getDatabaseFolderPath]]];
	[self printResults:[NSString stringWithFormat:@"Database file name: %@", [SQLite3Connector getDatabasePathFromName:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Escape string (it's): %@", [SQLite3Connector escapeString:@"it's"]]];
	//[self printResults:[NSString stringWithFormat:@"Dropping database: %d", [SQLite3Connector dropDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Database exists: %d", [SQLite3Connector databaseExists:@"testDatabase"]]];
	NSString *query = @"CREATE TABLE IF NOT EXISTS testTable (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);";
	[self printResults:[NSString stringWithFormat:@"Creating table: %d", [conn executeQuery:query inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Database size: %@", [SQLite3Connector getDatabaseFormatedFileSize:@"testDatabase"]]];
	//[self printResults:[NSString stringWithFormat:@"Dropping table: %d", [conn dropTable:@"testTable" inDatabase:@"testDatabase" useVacuum:YES]]];
	//[self printResults:[NSString stringWithFormat:@"Creating table: %d", [conn executeQuery:query inDatabase:@"testDatabase"]]];
	//[self printResults:[NSString stringWithFormat:@"Database size: %@", [SQLite3Connector getDatabaseFormatedFileSize:@"testDatabase"]]];
	//[self printResults:[NSString stringWithFormat:@"Database exists: %d", [SQLite3Connector databaseExists:@"testDatabase"]]];
	query = @"INSERT INTO testTable (data) VALUES ('Test Data (It''s)');";
	[self printResults:[NSString stringWithFormat:@"Inserting into the table: %d", [conn executeQuery:query inDatabase:@"testDatabase"]]];
	//[self printResults:[NSString stringWithFormat:@"Truncate table: %@", [conn truncateTable:@"testTable" inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Inserting into the table: %d", [conn executeQuery:query inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Database size: %@", [SQLite3Connector getDatabaseFormatedFileSize:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Inserting into the table: %d", [conn executeQuery:query inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Table exists: %d", [conn tableExists:@"testTable" inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Database size: %@", [SQLite3Connector getDatabaseFormatedFileSize:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Table exists: %d", [conn tableExists:@"testTable" inDatabase:@"testDatabase"]]];
	[self printResults:[NSString stringWithFormat:@"Count rows in the table: %d", [conn executeScalar:queryCount inDatabase:@"testDatabase"]]];
	
	NSMutableArray *arr = [conn getData:@"SELECT * FROM testTable;" fromDatabase:@"testDatabase"];
	NSLog(@"%@", arr);
	
	[conn release];
}