Tag Archives: INSERT

Common SQLite Operations

In nearly any program with persistent data that can be manipulated, I find myself writing three types of routines: insert, update, and delete. In the example below, I focus on using SQLite to perform these operations on some generic data.

To create a simple table for demonstration purposes, we can use the command-line ‘sqlite3’ utility.  In one of my previous posts, I discuss how to automate the (re)generation of your database, but this example is contrived enough that it doesn’t warrant such attention.  In the shell, use the following commands:

   $> sqlite3 db.sqlite
   sqlite> CREATE TABLE myObject (primaryKey INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, amount NUMERIC);
   sqlite> .quit
   $>

Getting this new database into your XCode project, and adding the SQLite3 framework would be next, but I’m going to skip over it, as it’s covered elsewhere and not difficult to put together.

Here’s a simple Objective-C object as the data model for our newly created table.

MyObject.h

@interface MyObject : NSObject
{
    NSInteger primaryKey;
    NSString *name;
    float amount;
}

@property (nonatomic, assign, readonly) NSInteger primaryKey;
@property (nonatomic, retain) NSString *name;
@property (nonatomic, assign) float amount;

// Returns: new MyObject with primary key
- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3*)db;

// Updates the database with the object values
- (void)updateDatabase:(sqlite3*)db;

// Delete an object with primary key 'pk'
+ (void)removeWithPrimaryKey:(NSInteger)pk database:(sqlite3*)db;

// Returns: primary key of newly inserted object
+ (NSInteger)addMyObjectIntoDatabase:(sqlite3*)db;

// Clean up, called on app termination
+ (void)finalizeStatements;

@end

The class defines “insert” using addMyObjectIntoDatabase:, “update” using updateDatabase:, and “delete” with removeWithPrimaryKey:database:

The class also has a initialization member presumably called by the controller that has better visibility to all of the table elements.  I’ll fill in the method bodies, but leave the actual hook-up to higher level classes as an “exercise for the reader”.

In the pattern above, generally the controller class will issue a “SELECT primaryKey FROM myObject” statement, then iterate through the returned result.  With each row, the controller factory method creates a new MyObject using the row’s primary key.  But, there are a number of valid approaches depending on your implementation requirements.

Here’s the implementation body of the MyObject class.  I took the design choice of preparing the various queries and storing the result as a class static pointer.  The advantage is performance since the queries are parameterized and “pre-compiled” for SQLite.  The disadvantage is that they consume resources that must be released at some point – in our case using the finalizeStatements method.

MyObject.m

static sqlite3_stmt *init_MyObj_statement = nil;
static sqlite3_stmt *update_MyObj_statement = nil;
static sqlite3_stmt *delete_MyObj_statement = nil;
static sqlite3_stmt *insert_MyObj_statement = nil;

@implementation MyObject

@synthesize primaryKey;
@synthesize name;
@synthesize amount;

- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3*)db
{
   if (self = [super init])
   {
      primaryKey = pk;
      if (init_MyObj_statement == nil)
      {
         const char *sql = "SELECT * FROM myObject WHERE primaryKey=?";
         int result = sqlite3_prepare_v2(db, sql, -1, &init_MyObj_statement, NULL);
         NSAssert1(result == SQLITE_OK, @"initWithPrimaryKey: failed to prepare statement with err '%s'", sqlite3_errmsg(db));
      }

      sqlite3_bind_int(init_MyObj_statement, 1, primaryKey);

      if (sqlite3_step(init_MyObj_statement) == SQLITE_ROW)
      {
         int columnID = 1; // 0 is primary key
         char *nameStr = (char*)sqlite3_column_text(init_MyObj_statement, columnID++);
         name = (nameStr) ? [[NSString stringWithUTF8String:nameStr] retain] : @"";

         amount = sqlite3_column_double(init_MyObj_statement, columnID);
      }

      sqlite3_reset(init_MyObj_statement);
   }

   return self;
}

- (void)updateDatabase:(sqlite3*)db
{
   if (update_MyObj_statement == nil)
   {
      const char *sql = "UPDATE myObject SET name=?, amount=? WHERE primaryKey=?";
      int success = sqlite3_prepare_v2(db, sql, -1, &update_MyObj_statement, NULL);
      NSAssert1(success == SQLITE_OK, @"updateDatabase: failed to prepare with message '%s'", sqlite3_errmsg(db));
   }

   int columnID = 1;

   sqlite3_bind_text(update_MyObj_statement, columnID++, [name UTF8String], -1, SQLITE_TRANSIENT);
   sqlite3_bind_double(update_MyObj_statement, columnID++, amount);
   sqlite3_bind_int(update_MyObj_statement, columnID++, primaryKey);

   int success = sqlite3_step(update_MyObj_statement);
   NSAssert1(success == SQLITE_DONE, @"updateDatabase: update failed with message '%s'", sqlite3_errmsg(db));

   sqlite3_reset(update_MyObj_statement);
}

+ (void)removeWithPrimaryKey:(NSInteger)pk database:(sqlite3*)db
{
   if (delete_MyObj_statement == nil)
   {
      static char *sql = "DELETE FROM myObject WHERE primaryKey=?";
      int result = sqlite3_prepare_v2(db, sql, -1, &delete_MyObj_statement, NULL);
      NSAssert1(result == SQLITE_OK, @"removeWithPrimaryKey: failed with message '%s'", sqlite3_errmsg(db));
   }

   sqlite3_bind_int(delete_MyObj_statement, 1, pk);
   int success = sqlite3_step(delete_MyObj_statement);
   NSAssert1(success == SQLITE_DONE, @"removeWithPrimaryKey: failed with message '%s'", sqlite3_errmsg(db));
   sqlite3_reset(delete_MyObj_statement);
}

+ (NSInteger)addMyObjectIntoDatabase:(sqlite3*)db
{
   if (insert_MyObj_statement == nil)
   {
      static char *sql = "INSERT INTO myObject (name) VALUES ('')";
      int result = sqlite3_prepare_v2(db, sql, -1, &insert_MyObj_statement, NULL);
      NSAssert1(result == SQLITE_OK, @"addMyObjectIntoDatabase: failed to prepare statement with err '%s'", sqlite3_errmsg(db));
   }

   int success = sqlite3_step(insert_MyObj_statement);
   if (success != SQLITE_ERROR)
   {
      return sqlite3_last_insert_rowid(db);
   }

   NSAssert1(0, @"addMyObjectIntoDatabase: failed with message '%s'", sqlite3_errmsg(db));
   return -1;
}

+ (void)finalizeStatements
{
   if (init_MyObj_statement)
      sqlite3_finalize(init_MyObj_statement);
   if (update_MyObj_statement)
      sqlite3_finalize(update_MyObj_statement);
   if (delete_MyObj_statement)
      sqlite3_finalize(delete_MyObj_statement);
   if (insert_MyObj_statement)
      sqlite3_finalize(insert_MyObj_statement);
}

@end