SQLite Wrapper
I created this code a while back when I was still targeting older iPhone OS i.e. 2.0 and above. I’ve found really useful when I need to throw things at a local SQL database on the device, since it’s creation I’ve moved up to target OS 3.0 and above only. Though I’ve migrated my latest app to these later targets I still have yet to migrate the database code over to user Core Data. 
It’s just not a priority and using the SQLite database directly still works perfectly on the later OS versions, though I would really like to get the benefits of using Core Data. Ah well maybe version 1.1 of the application.
The wrapper is written in c++ as I felt it was more kind to the usage of the SQLite API plus I ended up move the core of the original project that the code came from over to c++ due to performance issues (it was doing a lot of work and an iPhone is not my macbook).
//
// Database.h
//
// Created by Robert on 20/07/2009.
// Copyright 2009 Electric TopHat Ltd. All rights reserved.
//
#import
#import
// ---------------------------------------------
// Database Access Class
// ---------------------------------------------
class Database
{
#pragma mark ---------------------------------------------------------
#pragma mark === Constructor / Destructor Functions ===
#pragma mark ---------------------------------------------------------
public: // Functions
// ---------------------------------------------
// Constructor
// ---------------------------------------------
Database( NSString * _name, NSString * _createString = nil );
// ---------------------------------------------
// Destructor
// ---------------------------------------------
~Database();
#pragma mark ---------------------------------------------------------
#pragma mark === End Constructor / Destructor Functions ===
#pragma mark ---------------------------------------------------------
#pragma mark ---------------------------------------------------------
#pragma mark === Public Functions ===
#pragma mark ---------------------------------------------------------
public:
// ---------------------------------------------
// execute a single sql statement
// ---------------------------------------------
Boolean execute( NSString * _sql, NSMutableArray * _results = nil );
Boolean execute( NSString * _sql, NSMutableDictionary * _result );
// ---------------------------------------------
// ---------------------------------------------
// Table has Column
// ---------------------------------------------
Boolean hasTableColumn( NSString * _tableName, NSString * _columnName );
Boolean tableColumns( NSString * _tableName, NSMutableArray * _results );
// ---------------------------------------------
// ---------------------------------------------
// Alter the table
// ---------------------------------------------
Boolean updateTable( NSString * create, NSString * insert, NSString * _name );
// ---------------------------------------------
// ---------------------------------------------
// Return the error string
// ---------------------------------------------
NSString * error() { return m_error; };
// ---------------------------------------------
#pragma mark ---------------------------------------------------------
#pragma mark === End Public Functions ===
#pragma mark ---------------------------------------------------------
#pragma mark ---------------------------------------------------------
#pragma mark === Private Functions ===
#pragma mark ---------------------------------------------------------
private: //Functions
Boolean createWithBundleDatabaseWithName(NSString* _name);
Boolean createAnEmptyDatabaseWithName(NSString* _name, NSString * _createStatement);
#pragma mark ---------------------------------------------------------
#pragma mark === End Private Functions ===
#pragma mark ---------------------------------------------------------
#pragma mark ---------------------------------------------------------
#pragma mark === Protected Data ===
#pragma mark ---------------------------------------------------------
protected: // Data
// -------------------------
// Error String
// -------------------------
NSString * m_error;
// -------------------------
// -------------------------
// sql database
// -------------------------
sqlite3 * m_database;
// -------------------------
#pragma mark ---------------------------------------------------------
#pragma mark === End Private Data ===
#pragma mark ---------------------------------------------------------
};
// ---------------------------------------------
The above API is very simple or I hope it is. The basic idea is to provide a minimum wrapper round the core SQLite API though later on I did end up providing an additional helper function ‘updateTable‘. So what goes on?
Construction
The class constructor takes a ‘name‘ and an optional sql command, the ‘name’ is the name of the database file that will be created on disk. The first time you construct the database class it will first try to copy a database of the same name from your application bundle onto the disk, if the database file is not found in your bundle it will create one and optionally run an sql command (this can be used to create the desired table structure). The second time you construct the database class it will use the RW database contained in the applications working directory.
Execute
The class provides a very simple interface to run SQL commands, just create a string and call the execute command. You can optionally provide a mutable dictionary or a mutable array to return data from the database. Note that the array will contain a dictionary for each row provided and the key values are those requested in your sql statement or the column name.
Helper Functions
The class provides a few little helper functions:
- hasTableColumn : has the table got the column.
- tableColumns : return all the table columns associated with a table.
- updateTable : this can be used to make changes to your table (though I will need to document it more sorry).
WARNING : If you want to include c++ class’s in your project you must change the .m file extension to .mm this will inform the compiler that you are using Objective-C++ else you will get a lot of compile errors.
I’ve tried to keep it simple, so if you know how to write SQL statements and don’t want to worry about the low level API or C++ just scares you then hopefully this is for you. As always the code is as is and use it at your own risk, feel free to alter, hack or otherwise distribute the code and if you have any questions please feel free to get in touch.
Download : Database.h, Database
Sample Project : SqliteDBWrapperSample
Thanks for reading.
I’ve been plodding away on a project (it’s getting there almost) and one of the things that I really wanted was to provide video tutorials for the user. I really don’t want to package all these videos with the application, really once you’ve watched them once you will probably never look at them again (If I’m wrong about that let me know ).
Thanks..
I am growing a moustache this year for Movember. I have decided to put pickup my razor for one month (November) and help raise awareness and funds for men’s health – specifically prostate cancer.
