WARNING : This content was auto generated from a backup file, links and images may be broken.

I created this code a while back when I was still targeting older iPhone OS i.e. 2.0 and above. I have found really useful when I need to throw things at a local SQL database on the device since it is creation I have moved up to target OS 3.0 and above only. Though I have 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 have tried to keep it simple so if you know how to write SQL statements and do not 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.hDatabase

Sample Project : SqliteDBWrapperSample

Thanks for reading.

Comments

Comments powered by Disqus