May 15

Experimental Database Class

persistence 1 Comment »

This is an incomplete experiment creating a class to manage an sqlite3 database on the iPhone.

The place that needs the most work is the mcaDB::queryDB() method. The plan is to eventually have this method populate an array or list that I can access after the query is finished. There are methods in PHP that do similar things.

What got me started was Chapter 11: Basic Data Persistence in Beginning iPhone Development.

You can find documentation and the API reference for SQLite here: http://www.sqlite.org.

/*
 *  mcaDB.h
 *  mcaDB1
 *
 *  Created by Mitchell Allen on 5/15/09.
 *  Copyright 2009 __MyCompanyName__. All rights reserved.
 *
 */
 
/*
 *  Instructions for linking to sqlite3 library
 *
 *  In Xcode:
 *
 *      Click on folder: Groups & File / Frameworks
 *      Select menu: Project / Add to Project ...
 *      Navigate to:  /Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator[version].sdk/usr/lib/
 *      Select file: libsqlite3.dylib
 *      Uncheck: Copy items into destination groups folder if needed
 *      Change Reference Type to: Relative to Current SDK
 */
 
#include "/usr/include/sqlite3.h"
 
#include "mcaUtils.h"
 
class mcaDB {
 
protected:
 
	sqlite3	*m_database;
 
	bool m_open;
 
public:
 
	void errorDB();
 
	bool openDB( const char *szDB );
 
	void closeDB( bool bForce );
 
	bool execDB( const char *szSQL );
 
	bool queryDB( const char *szSQL );
 
};
/*
 *  mcaDB.mm
 *  mcaDB1
 *
 *  Created by Mitchell Allen on 5/15/09.
 *  Copyright 2009 __MyCompanyName__. All rights reserved.
 *
 */
 
#include "mcaDB.h"
 
void mcaDB::errorDB()
{
	mcaUtils_logString( @"ERROR", sqlite3_errmsg( m_database ) );
}
 
bool mcaDB::openDB( const char *szDB ) {
 
	NSLog( @"mcaDB::openDB( ... )" );
 
	mcaUtils_logString( @"DATABASE", szDB );
 
	if( sqlite3_open( szDB, &m_database ) != SQLITE_OK ) {
 
		errorDB();
 
		closeDB( true );
 
		return false;
	}
 
	m_open = true;
 
	return true;
}
 
void mcaDB::closeDB( bool bForce ) {
 
	NSLog( @"mcaDB::closeDB( ... )" );
 
	if( m_open || bForce ) {
 
		sqlite3_close( m_database );
 
		m_open = false;
	}
}
 
bool mcaDB::execDB( const char *szQuery ) {
 
	NSLog( @"mcaDB::execDB( ... )" );
 
	mcaUtils_logString( @"QUERY", szQuery );
 
	if( ! m_open ) return false;	
 
	if( sqlite3_exec( m_database, szQuery, NULL, NULL, NULL ) != SQLITE_OK ) {
 
		errorDB();
 
		return false;
	}
 
	return true;
}
 
bool mcaDB::queryDB( const char *szSQL ) {
 
	NSLog( @"mcaDB::queryDB( ... )" );
 
	mcaUtils_logString( @"QUERY", szSQL );
 
	if( ! m_open ) return false;
 
	sqlite3_stmt	*stmt;
 
	if( sqlite3_prepare_v2( m_database, szSQL, -1, &stmt, nil ) == SQLITE_OK) {
 
		while( sqlite3_step( stmt ) == SQLITE_ROW ) {
 
			NSLog( @" ... looping ... " );
 
			int iCols = sqlite3_column_count( stmt );
 
			for( int i = 0; i < iCols; i++ ) {
 
				const char *colName = sqlite3_column_name( stmt, i );
 
				mcaUtils_logString( @"COLUMN", colName );
 
				long colType = sqlite3_column_type( stmt, i );
 
				switch( colType ) {
 
					case SQLITE_INTEGER:
 
						int fldInt = sqlite3_column_int( stmt, i );
						mcaUtils_logInteger( @"INTEGER", fldInt );
 
						break;
					case SQLITE_FLOAT:
 
						NSLog( @"FLOAT" );
 
						float fldFloat = sqlite3_column_double( stmt, i );
						break;
					case SQLITE_TEXT:
 
						const unsigned char * fldStr = sqlite3_column_text( stmt, i );
						mcaUtils_logString( @"TEXT", (const char *) fldStr );
 
						break;
					case SQLITE_BLOB:
 
						int fldSize = sqlite3_column_bytes( stmt, i );
						mcaUtils_logInteger( @"BLOB (size)", fldSize );
						break;
					case SQLITE_NULL:
						break;
					default:
						break;
				}
			}
 
		}
 
		sqlite3_finalize(stmt);
	}
 
	return true;
}

Utility Class

This is just something I whipped up to make it easy to log info in the class above while I debug. This will probably change.

/*
 *  mcaUtils.h
 *  mcaDB1
 *
 *  Created by Mitchell Allen on 5/15/09.
 *  Copyright 2009 __MyCompanyName__. All rights reserved.
 *
 */
 
void mcaUtils_logString( NSString *szLabel, const char * str );
void mcaUtils_logInteger( NSString * szLabel, int i );
/*
 *  mcaUtils.mm
 *  mcaDB1
 *
 *  Created by Mitchell Allen on 5/15/09.
 *  Copyright 2009 __MyCompanyName__. All rights reserved.
 *
 */
 
#include "mcaUtils.h"
 
void mcaUtils_logString( NSString * szLabel, const char * str )
{
	NSString *szTemp = [[NSString alloc] initWithUTF8String:str];
 
	NSString *szMessage = [[NSString alloc] initWithFormat: @"%@ = %@", szLabel, szTemp ];
 
	NSLog( szMessage );
 
	[szTemp release];
	[szMessage release];
}
 
void mcaUtils_logInteger( NSString * szLabel, int i )
{
	NSString *szMessage = [[NSString alloc] initWithFormat: @"%@ = %d", szLabel, i ];
 
	NSLog( szMessage );
 
	[szMessage release];
}

Sample Usage

This could go into say a viewController class file.

Define the name of your database.

#define TEST_DB_FILE	@"/test.db1"

Build a complete path to your database file.

- (NSString *) dataFilePath;
{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 
	NSString *documentsDirectory = [paths objectAtIndex:0];
 
	return [documentsDirectory stringByAppendingString:TEST_DB_FILE];
}

Pass the full path of your database to an object – in say your view controllers viewDidLoad method.

    m_table.initDB( [[self dataFilePath] UTF8String] );

This is how you might define the method above. Yes, in a production environment the final method should always close the database before returning.

void testTable::initDB( const char *szDB ) {
 
	NSLog( @"START: testTable::initDB( ... )" );
 
	if( ! m_db.openDB( szDB ) ) {
 
		NSLog( @" ... ERROR opening database" );
 
		return;
	}
 
	if( ! m_db.execDB(  "DROP TABLE TEST_TABLE;" ) ) {
 
		// Since this is just a test database - we drop the table every time to start fresh.
 
		NSLog( @" ... Couldn't drop table ... ignoring " );
	}
 
	if( ! m_db.execDB(  "CREATE TABLE IF NOT EXISTS TEST_TABLE (ROW INTEGER PRIMARY KEY, PLANET TEXT);" ) ) {
 
		NSLog( @" ... ERROR creating table" );
 
		return;
	}
 
	if( ! m_db.execDB(  "INSERT OR REPLACE INTO TEST_TABLE (PLANET) VALUES ('Mercury');" ) ) {
 
		NSLog( @" ... ERROR inserting row" );
 
		return;
	}
 
	if( ! m_db.execDB(  "INSERT OR REPLACE INTO TEST_TABLE (PLANET) VALUES ('Venus');" ) ) {
 
		NSLog( @" ... ERROR inserting row" );
 
		return;
	}
 
	if( ! m_db.queryDB(  "SELECT COUNT(*) AS myCount FROM TEST_TABLE;" ) ) {
 
		NSLog( @" ... ERROR querying DB" );
 
		return;
	}
 
	if( ! m_db.queryDB(  "SELECT * FROM TEST_TABLE;" ) ) {
 
		NSLog( @" ... ERROR querying DB" );
 
		return;
	}
 
        m_db.closeDB( false );
 
	NSLog( @"END: testTable::initDB( ... )" );
}

Log Output

mcaDB1[3138:20b] START: testTable::initDB( ... )
mcaDB1[3138:20b] mcaDB::openDB( ... )
mcaDB1[3138:20b] DATABASE = /Users/apple/Library/Application Support/iPhone Simulator/User/Applications/.../Documents/test.db1
mcaDB1[3138:20b] mcaDB::execDB( ... )
mcaDB1[3138:20b] QUERY = DROP TABLE TEST_TABLE;
mcaDB1[3138:20b] mcaDB::execDB( ... )
mcaDB1[3138:20b] QUERY = CREATE TABLE IF NOT EXISTS TEST_TABLE (ROW INTEGER PRIMARY KEY, PLANET TEXT);
mcaDB1[3138:20b] mcaDB::execDB( ... )
mcaDB1[3138:20b] QUERY = INSERT OR REPLACE INTO TEST_TABLE (PLANET) VALUES ('Mercury');
mcaDB1[3138:20b] mcaDB::execDB( ... )
mcaDB1[3138:20b] QUERY = INSERT OR REPLACE INTO TEST_TABLE (PLANET) VALUES ('Venus');
mcaDB1[3138:20b] mcaDB::queryDB( ... )
mcaDB1[3138:20b] QUERY = SELECT COUNT(*) AS myCount FROM TEST_TABLE;
mcaDB1[3138:20b]  ... looping ...
mcaDB1[3138:20b] COLUMN = myCount
mcaDB1[3138:20b] INTEGER = 2
mcaDB1[3138:20b] mcaDB::queryDB( ... )
mcaDB1[3138:20b] QUERY = SELECT * FROM TEST_TABLE;
mcaDB1[3138:20b]  ... looping ...
mcaDB1[3138:20b] COLUMN = ROW
mcaDB1[3138:20b] INTEGER = 1
mcaDB1[3138:20b] COLUMN = PLANET
mcaDB1[3138:20b] TEXT = Mercury
mcaDB1[3138:20b]  ... looping ...
mcaDB1[3138:20b] COLUMN = ROW
mcaDB1[3138:20b] INTEGER = 2
mcaDB1[3138:20b] COLUMN = PLANET
mcaDB1[3138:20b] TEXT = Venus
mcaDB1[3138:20b] mcaDB::closeDB( ... )
mcaDB1[3138:20b] END: testTable::initDB( ... )

Tags: ,