Access to the Database File

Because SQLite uses a local filesystem to store its databases, the SQL language does not implement the GRANT and REVOKE commands that are commonly found in client/server database systems. They simply do not make any sense in this context.

Therefore the only access issues that need to be addressed are those associated with the database file itself and are done at the operating-system level.

File Permissions

For SQLite to be able to open a database, the user under which a process is running must have permission to access that file on the operating system.

Quite simply, to open a database for reading the process must be able to open the file in read-only mode, and to open it for writing the process must have both read and write access to the file.

On a Unix system, if the process runs as the owner of the database file, the file mode can be 0400 or 0600 for read and write access respectively. Mode 0444 would enable all users to access the database file as read-only, whereas mode 0644 would additionally grant write access to the owner of the file, and mode 0666 would allow writing by all users respectively. More information about file permissions can be found from man chmod.

Locking and Timeouts

Remember that although there is no limit to the number of concurrent reads allowed by SQLite, a single write process will lock the database and prevent any further read or write operations until it is complete.

It is therefore important to handle the SQLITE_BUSY return code if more than one process might access your database at a time. The more instances of SQLite there are running in your application, the greater chance that one of them will encounter a timeout when trying to access the database file.

The simplest approach is to continue attempting the query in a loop until SQLITE_OK is returned or some other exit forces you to stop trying, with a small pause on each iteration of the loop. Listing 10.3 shows how you might take care of this.

Listing 10.3. Handling the SQLITE_BUSY Status

[View full width]

 #include <stdio.h> #include <sqlite.h> int main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("db1", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     return(1);   }   do {     ret = sqlite_exec(db, "INSERT INTO mytable (col1, col2) VALUES (1, 2)", NULL, NULL,  &errmsg);     switch(ret) {        case SQLITE_OK:    break;        case SQLITE_BUSY:  fprintf(stderr, "Database locked, sleeping...\n");                           sleep(1);                           break;        default:           fprintf(stderr, "Error: %s\n", errmsg);                           sqlite_freemem(errmsg);                           return(ret);     }   } while (ret != SQLITE_OK);   sqlite_close(db);   return(0); } 

The do ... while loop ensures that the query is executed at least once, and if the return code in ret is SQLITE_OK, the loop will not repeat a second time. However, if the SQLITE_BUSY error is encountered, the program outputs a message and sleeps for one second before the loop restarts and the query is attempted again.

Note

The sleep() function on Unix takes as its argument a number of seconds to wait. However, on Windows it requires a number of milliseconds. Listing 10.3 assumes a Unix platform so will only sleep for one thousandth of a second under Windows unless the value passed to sleep() is adjusted to 1000. However, it is much more useful if your program can do some other task than just hanging around while waiting to obtain a database lock.

Upon encountering any other return code, the program will halt execution displaying the contents of errmsg.

Similar functionality can be implemented using sqlite_busy_handler() to set a callback function that is invoked whenever a lock cannot be obtained.

Listing 10.4 shows a busy handler callback that will cause the program to attempt to obtain a lock five times, sleeping for one second between each try and giving up after the fifth attempt.

Listing 10.4. Using a Busy Handler Callback Function

 #include <stdio.h> #include <sqlite.h> static int myhandler(void *NotUsed, const char *dbname, int numtries) {   fprintf(stderr, "Database locked on try %d, sleeping...\n", numtries);   if (numtries >= 5)     return(0);   else {     sleep(1);     return(1);   } } int main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("db1", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     return(1);   }   sqlite_busy_handler(db, myhandler, NULL);   ret = sqlite_exec(db, "INSERT INTO mytable (col1, col2) VALUES (1, 2)",                     NULL, NULL, &errmsg);   if (ret != SQLITE_OK) {     fprintf(stderr, "Error: %s\n", errmsg);     sqlite_freemem(errmsg);     return(ret);   }   sqlite_close(db);   return(0); } 

The busy callback function is registered with the line

 sqlite_busy_handler(db, myhandler, NULL); 

The function pointer myhandler references the callback function declared at the top of the listing. The callback takes three arguments. The first is the pointer passed as the third argument to sqlite_busy_handler(), which allows arbitrary data to be passed to the callback. We have not used this facility in this example. Secondly, the name of the database table or index that SQLite was attempting to access is passed in. The third argument contains the number of attempts that have been made to obtain a lock, and Listing 10.4 restricts the number of attempts that may be made by looking at this value.

 if (numtries >= 5)   return(0); else {   sleep(1);   return(1); } 

If numtries exceeds 5, the sleep() instruction will not occur and a value of 1 is returned. When the busy callback returns zero, it instructs the query to continue attempting execution. A non-zero return value causes the query to be interrupted and the SQLITE_BUSY error to be raised.

The following output shows what happens when the program in Listing 10.4 is run on a locked database:

 $ ./listing10.4 Database locked on try 1, sleeping... Database locked on try 2, sleeping... Database locked on try 3, sleeping... Database locked on try 4, sleeping... Database locked on try 5, sleeping... Error: database is locked 

Multithreaded Database Access

SQLite includes support for multithreaded database connections so that you can access your database from two or more threads simultaneously.

The ThrEADSAFE preprocessor macro is used to determine whether SQLite is built in thread-safe mode. Enable this feature by adding DTHREADSAFE=1 to an appropriate place in the Makefile. Precompiled binary distributions are thread-safe by default for Windows systems, but not for Unix systems.

The most important thing to remember is that each thread must make its own call to sqlite_open() to obtain an sqlite* type pointer. The same sqlite* pointer should not be accessed from more than one thread at a time as unpredictable results may be seen. The child process from the result of a fork() command should also open its own copy of the database after being spawned.

Be aware that because SQLite reads the database schema only once upon opening the database, if one thread changes the schema the other thread will not be able to see the amended schema until you close and reopen the database connectionjust the same as would happen in a multi-process environment.