Using SQLite: Create a Table in The Database

Once you’ve created a SQLite database file in the Documents folder of the application sandbox, the next logical step is to open the database file and create a table in it. As you already know, a database table is for storing the application’s data. A table’s schema (pronounce skee-ma) consist of columns and rows. Each column contains a different type of attribute and each row correspond to a single record or row, as shown in this image.

Step 1: Declare and Implement the createTable method

Now, to create a table in the projects’s database file, you’ll have to declare and implement this method in the AppDelegate class.

- (void) createTable
{
  char *error;
  const char *sql = "CREATE TABLE IF NOT EXISTS possessions (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, photo BLOB)";

  // Create the "possessions" table in the database file
  int result = sqlite3_exec(dbHandle, sql, NULL, NULL, &error) != SQLITE_OK;

  if (result != SQLITE_OK) {
    // Query failed, display the query error message
    NSLog(@"OOPS! There's an error in the sql statement.\n'%s'", sqlite3_errmsg(dbHandle));
  }
}

This method create a table called possessions in the itemsOwned.sqlite database file, only if doesn’t already exist in the database file. The statement in the if() control structure is executed only if the sqlite3_exec() function is unable to create the possessions table in the database file. For example, the SQL statement on line 04 may contain an error.

Step 2: Modify the didFinishLaunchingWithOptions: method

I want you to locate the didFinishLaunchingWithOptions: method in the AppDelegate.m file and add highlighted code.

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
  [self openDatabase];

  [self createTable];

  [self closeDatabase];

  return YES;
}

Step 3: Run the application

Run the application and you should see the Master view in the simulator window. If the sqlite3_exec() function successfully created the possessions table in the database file, nothing is displayed in the Debugger window. However; if the sqlite3_exec() function failed to create the possessions table in the itemsOwned.sqlite database file, the application will display the Master view in the simulator and an error message in Xcode’s Debugger window. For example, say you omitted the keyword INTEGER in the sql statement, you’ll see this error message in the debugger window.

Step 4: Verify that the possessions table was created in the database file

Ok, so you’ve created a table called possessions in the database file. How to do you verify that the table was actually created in the database file? If you thought about using Finder to open the itemsOwned.sqlite database file; well you’re wrong. Instead, you’ll have to declare and implement this method in the AppDelegate class.

-(NSString*) getDatabaseTableNames
{
  NSString *tableNames = nil;
  NSMutableArray *dbRecords = [[NSMutableArray alloc] init];

  const char *sql = "SELECT tbl_name FROM sqlite_master";
  sqlite3_stmt *compiledStatement;

  int result = sqlite3_prepare_v2(dbHandle, sql, -1, &compiledStatement, NULL);

  if (result != SQLITE_OK) {
    // Query failed, display the query error message in the debugger window
    NSLog(@"createTable Error:\n'%s'", sqlite3_errmsg(dbHandle));
  }

  while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
    // Put records the sql query returned in the dbRecords array
    NSString *currentRecord = [NSString stringWithUTF8String: (char *)sqlite3_column_text(compiledStatement, 0)];
    [dbRecords addObject:currentRecord];
  }

  sqlite3_finalize(compiledStatement);

  // Convert the mutable array to an array
  NSArray *mutableArray = [dbRecords mutableCopy];

  // Convert the array elements string objects before assigning them to the tableNames variable
  tableNames = [mutableArray componentsJoinedByString:@"\n"];

  return tableNames;
}

In above method, we used these SQLite functions to verify that the videos table was successfully created in the database file.

The first three functions are collectively called SQLite prepare query functions. We used them to  prepare, step, and finalize the statement that select a single column called tble_name from the sqlite_master table. The SQLite engine automatically created the sqlite_master table in the vintageDatabase file, when we used the sqlite_open() function.

We used the sqlite3_step() function within the while() loop to execute the previously prepared statement. Result sets (records) the sqlite3_step() function returned is processed within the body of the while() loop.

Moving along, I want you to add highlighted code shown below in the didFinishLaunchingWithOptions: method.

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
  [self openDatabase];

  NSString *dbTables = [self getDatabaseTableNames];
  NSLog(@"The itemsOwned database file contain these tables\n%@",dbTables);

  [self closeDatabase];

  return YES;
}

When you are done, run the application and you’ll see the Master view in the simulator window, as usual. You’ll also see this output in the Debugger window:

As you can can see the sqlite engine created two tables in the itemsOwn.sqlite database file: possessions and sqlite_sequence. The sqlite_sequence table is for keeping track of the possessions table’s id number, since we set it to AUTOINCREMENT. When the first record is inserted in the possessions table, the SQLite engine will add a record in the sqlite_sequence table and it will look like this:

When more records are added in the possessions table, the seq column number is incremented by 1. Say, you added two more records in the possessions table, its id column will contain the number 3 and the sequence table’s seq column will now display the number 3.

That’s all for this week’s workshop. Next week you will learn how to add records in the database’s possessions table.