SQLite 3.0.7

D. Richard Hipp's SQLite database engine has earned a well-respected place in the toolbox of many programmers. Its small size and simple distribution make it a natural choice for standalone and embedded applications. Wide support by many programming languages, including PHP, has made SQLite popular for Web applications that need persistent data storage but don't need the kind of multi-user scaling capabilities provided by server-based solutions.

Version 3 of SQL adds support for UTF-8 data. If your content doesn't happen to be in English, this addition obviously is of great benefit to you. According to the SQLite author, version 3 also has internal support for UTF-16 data. If you need that feature at the moment, however, you need to implement it yourself or make arrangements with the author to have that functionality built in.

One of the great peculiarities of the SQLite database has been its typeless storage--any data of any size can be stuffed into any field. That feature hasn't changed in version 3, but a new feature called manifest typing has been added. With manifest typing, the database engine attempts to make the data stored in a field be of the same general type as the declared type of the field. The engine still stores any data in any field, but if the type of the data matches the declared type of the field, the database engine then stores the data in a binary format that takes up less space.

Manifest typing means that a programmer can query the database for the type of an individual field in a result set with the sqlite3_column_type() function call. The types that are stored are integer, float, text, blob and null.

Version 3 also adds support for BLOBS. This means, for example, that it would be perfectly reasonable to store photos of the desired items or the family members themselves in my family wish list database.

The most apparent change that most previous SQLite users notice is the prefix changes. SQLite 3 functions all begin with sqlite3_ instead of sqlite_, as in older versions.

Old methods of data access still are available, including sqlite3_exec() and sqlite3_get_table(). For the uninitiated, sqlite3_exec takes a callback function as one of its parameters and calls this function for each row in the data set. The sqlite3_get_table function returns the entire results set in a single-character array.

The preferred way to interact with the database in SQLite 3 is the prepared statement interface. Advanced users of SQLite 2.8.x might recognize prepared statement routines as an extension of the experimental callback free vm interface to SQLite.

ODBC programmers have been able to use prepared statements for a long time. In addition to extending the older virtual machine interface to SQLite, prepared statements offer a number of convenience features for programmers. For those who haven't seen a prepared statement, an example might be:

     SELECT id, name, email FROM user WHERE name = ?

The question mark represents a wildcard, a place where real data is bound before the query actually runs. The query is prepared once, and for each execution, actual data is bound to the wildcards. This eliminates the need for careful, paranoid parsing of user-supplied data for killer characters and potential exploits.

Prepared queries also offer some savings in terms of speed. Internally, a database must convert a statement to a set of parameters that define the actual steps to be carried out in the search. This can represent some computational expense, especially if a single query is executed repetitively. Removing the compilation step for all but the first invocation of the query saves processor time. This saved times adds up in applications such as data conversions or modifications, where hundreds or thousands of identical queries may need to be run.

The best way to understand the new API is to see it in use. Here, I show some examples from an e-commerce wish list application on which I'm currently working. The particular code shown comes from a command-line utility for generating shopping lists. The complete code for the example can be downloaded (see Resources).

The first change to note is the changed connection command.

sqlite3* db;

if (sqlite3_open("wishlist.db", &db))
  error_handler(db);

As with other SQLite functions, sqlite3_open now returns an integer result code rather than a connection handle. The second parameter then becomes a pointer to a pointer to an sqlite3 structure. Any result other than SQLITE_OK (0) causes control to flow to my error handler. That function shows another of the API changes:

void error_handler(sqlite3* db) {

  fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
  exit(EXIT_FAILURE);

}

The error string function now returns a pointer rather than asking for a pointer to a character pointer as a parameter. Although it's not in keeping with the rest of the library, it's rather pointless to return an integer error code from this function, so there was no benefit to putting the programmer through pointer gymnastics.

In my program, I left the statement processing, binding and execution in the main function and moved the data gathering out to a second function.

#define ITEM_SQL "SELECT * FROM item WHERE user_id=?"

struct item;

struct item* item_new();
void item_delete(struct item*);
struct item* item_populate(sqlite3_stmt*, struct item*);

int* get_user_ids();
void print_wishlist(struct item*);

int main() {

  sqlite3_stmt* itemqry;
  struct item* wishlist;
  int* user_id;
  int i;

  user_id = get_user_ids(); /* Magic function */

  /* Prepare the statement for use, many times over */
  if (sqlite3_prepare(db, ITEM_SQL, strlen(ITEM_SQL), &itemqry, NULL))
    error_handler(db);
  
  for(i=0; user_id[i]; i++) {
    /* Data binding */
    if (sqlite3_bind_int(itemqry, 1, user_id[i]))
      error_handler(db);
    
    /* Actual data gathering */
    while((rc = sqlite3_step(itemqry)) == SQLITE_ROW) 
      wishlist = item_populate(itemqry, wishlist);

    /* Use the data we've collected */
    print_wishlist(wishlist);
    
    /* Get ready for the next user_id*/
    sqlite3_reset(itemqry);
  }

  /* Final cleanup */
  sqlite3_finalize(itemqry);
  return EXIT_SUCCESS;
}

The code has been commented lightly to make it a little more clear what is happening. For sqlite function, we start with the statement preparation. Notice that it is outside of any loops; once we've done it, we don't need to do it again. The itemqry pointer is the handle that we're going to use for all of our other steps.

Inside of the for loop, we need to rebind the single wildcard in the statement each time through the loop. There's no recompilation, which saves code and processor time.

In the execution step we're responsible for looping over all of the data to collect it. The sqlite3_step function handles that for us and returns SQLITE_ROW whenever a call results in actual data. A more robust program would look for the other possible values, which include SQLITE_BUSY and SQLITE_DONE. In practical terms, anything other than SQLITE_ROW means that our data gathering process is done.

Every time I get a row of data, I'm shipping off my prepared statement to a data handler function, item_populate();. This function is responsible for extracting the data from the row, populating an item structure and appending it to our linked list of items.

Once I have my data, I do whatever processing is necessary, which in this case is a printing function.

Finally, I call sqlite3_reset() on my prepared statement to free up any consumed by the query. It's worth noting that sqlite3_reset() doesn't change or remove the wildcard values from the prepared statement. If you step through the query again, you get the same result set.

As a last step, here I show you how data is extracted from a result set in SQLite 3. The new retrieval features are much more sophisticated than the string parsing necessary with previous versions.

struct item {

  int id;
  char* description;
  char externalkey[41];
  struct item* next;

};

struct item* item_populate(sqlite3_stmt* s, struct item* top) {

  struct item* cur;
  struct item* i;
  int size;
  const char* hold;

  i = item_new();
  i->id = sqlite3_column_int(s, 0);
  hold = sqlite3_column_text(s, 1);
  if (hold)
    strncpy(i->externalkey, hold, 40);
  size = sqlite3_column_bytes(s, 2);
  hold = sqlite3_column_text(s, 2);
  if (hold) {
    i->description = (char*)calloc(1, size + 1);
    strncpy(i->description, hold, size);
  }

  /* Linked list handling code */
  if (top == NULL) return i;
  for(cur = top; cur->next ; cur = cur->next);
  cur->next = i;

  return top;

}

The sqlite3_column family of functions extract the data from an individual column. Columns can be indexed numerically only, so it's a good idea to know the order in which your columns will be returning. If you don't know, you can inquire about the name of a specific column with sqlite3_column_name().

Some of my columns are allowed to have null values, and I need to take precautions to address that. That's why, rather than copying the results of sqlite3_column_text() directly to the externalkey element of my item structure, I assign it to a temporary pointer. This way, I can check to make sure it isn't a NULL before copying a fixed length of the string into my array. If you haven't worked with SQLite before, it's important to remember that the declared size of a string in the database has no bearing on the size of the string stored or returned. The strn series of string functions are vital to dealing with this data.

Finally, we have the code for handling the last column of our data, which is a potentially large text object. sqlite3_column_bytes() tells me how large the object is, which lets me allocate sufficient space for copying the data.

There's plenty more to the API, but hopefully these few examples have shown you enough to get you going. Features I didn't address include the ability to define your own collating sequences, implement a database permissions system and define your own database functions.