Tag Archives: UPDATE

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:

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.