sl@0: /*
sl@0: ** 2006 January 07
sl@0: **
sl@0: ** The author disclaims copyright to this source code.  In place of
sl@0: ** a legal notice, here is a blessing:
sl@0: **
sl@0: **    May you do good and not evil.
sl@0: **    May you find forgiveness for yourself and forgive others.
sl@0: **    May you share freely, never taking more than you give.
sl@0: **
sl@0: ******************************************************************************
sl@0: **
sl@0: ** $Id: test_server.c,v 1.8 2008/06/26 10:41:19 danielk1977 Exp $
sl@0: **
sl@0: ** This file contains demonstration code.  Nothing in this file gets compiled
sl@0: ** or linked into the SQLite library unless you use a non-standard option:
sl@0: **
sl@0: **      -DSQLITE_SERVER=1
sl@0: **
sl@0: ** The configure script will never generate a Makefile with the option
sl@0: ** above.  You will need to manually modify the Makefile if you want to
sl@0: ** include any of the code from this file in your project.  Or, at your
sl@0: ** option, you may copy and paste the code from this file and
sl@0: ** thereby avoiding a recompile of SQLite.
sl@0: **
sl@0: **
sl@0: ** This source file demonstrates how to use SQLite to create an SQL database 
sl@0: ** server thread in a multiple-threaded program.  One or more client threads
sl@0: ** send messages to the server thread and the server thread processes those
sl@0: ** messages in the order received and returns the results to the client.
sl@0: **
sl@0: ** One might ask:  "Why bother?  Why not just let each thread connect
sl@0: ** to the database directly?"  There are a several of reasons to
sl@0: ** prefer the client/server approach.
sl@0: **
sl@0: **    (1)  Some systems (ex: Redhat9) have broken threading implementations
sl@0: **         that prevent SQLite database connections from being used in
sl@0: **         a thread different from the one where they were created.  With
sl@0: **         the client/server approach, all database connections are created
sl@0: **         and used within the server thread.  Client calls to the database
sl@0: **         can be made from multiple threads (though not at the same time!)
sl@0: **
sl@0: **    (2)  Beginning with SQLite version 3.3.0, when two or more 
sl@0: **         connections to the same database occur within the same thread,
sl@0: **         they can optionally share their database cache.  This reduces
sl@0: **         I/O and memory requirements.  Cache shared is controlled using
sl@0: **         the sqlite3_enable_shared_cache() API.
sl@0: **
sl@0: **    (3)  Database connections on a shared cache use table-level locking
sl@0: **         instead of file-level locking for improved concurrency.
sl@0: **
sl@0: **    (4)  Database connections on a shared cache can by optionally
sl@0: **         set to READ UNCOMMITTED isolation.  (The default isolation for
sl@0: **         SQLite is SERIALIZABLE.)  When this occurs, readers will
sl@0: **         never be blocked by a writer and writers will not be
sl@0: **         blocked by readers.  There can still only be a single writer
sl@0: **         at a time, but multiple readers can simultaneously exist with
sl@0: **         that writer.  This is a huge increase in concurrency.
sl@0: **
sl@0: ** To summarize the rational for using a client/server approach: prior
sl@0: ** to SQLite version 3.3.0 it probably was not worth the trouble.  But
sl@0: ** with SQLite version 3.3.0 and beyond you can get significant performance
sl@0: ** and concurrency improvements and memory usage reductions by going
sl@0: ** client/server.
sl@0: **
sl@0: ** Note:  The extra features of version 3.3.0 described by points (2)
sl@0: ** through (4) above are only available if you compile without the
sl@0: ** option -DSQLITE_OMIT_SHARED_CACHE. 
sl@0: **
sl@0: ** Here is how the client/server approach works:  The database server
sl@0: ** thread is started on this procedure:
sl@0: **
sl@0: **       void *sqlite3_server(void *NotUsed);
sl@0: **
sl@0: ** The sqlite_server procedure runs as long as the g.serverHalt variable
sl@0: ** is false.  A mutex is used to make sure no more than one server runs
sl@0: ** at a time.  The server waits for messages to arrive on a message
sl@0: ** queue and processes the messages in order.
sl@0: **
sl@0: ** Two convenience routines are provided for starting and stopping the
sl@0: ** server thread:
sl@0: **
sl@0: **       void sqlite3_server_start(void);
sl@0: **       void sqlite3_server_stop(void);
sl@0: **
sl@0: ** Both of the convenience routines return immediately.  Neither will
sl@0: ** ever give an error.  If a server is already started or already halted,
sl@0: ** then the routines are effectively no-ops.
sl@0: **
sl@0: ** Clients use the following interfaces:
sl@0: **
sl@0: **       sqlite3_client_open
sl@0: **       sqlite3_client_prepare
sl@0: **       sqlite3_client_step
sl@0: **       sqlite3_client_reset
sl@0: **       sqlite3_client_finalize
sl@0: **       sqlite3_client_close
sl@0: **
sl@0: ** These interfaces work exactly like the standard core SQLite interfaces
sl@0: ** having the same names without the "_client_" infix.  Many other SQLite
sl@0: ** interfaces can be used directly without having to send messages to the
sl@0: ** server as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.
sl@0: ** The following interfaces fall into this second category:
sl@0: **
sl@0: **       sqlite3_bind_*
sl@0: **       sqlite3_changes
sl@0: **       sqlite3_clear_bindings
sl@0: **       sqlite3_column_*
sl@0: **       sqlite3_complete
sl@0: **       sqlite3_create_collation
sl@0: **       sqlite3_create_function
sl@0: **       sqlite3_data_count
sl@0: **       sqlite3_db_handle
sl@0: **       sqlite3_errcode
sl@0: **       sqlite3_errmsg
sl@0: **       sqlite3_last_insert_rowid
sl@0: **       sqlite3_total_changes
sl@0: **       sqlite3_transfer_bindings
sl@0: **
sl@0: ** A single SQLite connection (an sqlite3* object) or an SQLite statement
sl@0: ** (an sqlite3_stmt* object) should only be passed to a single interface
sl@0: ** function at a time.  The connections and statements can be passed from
sl@0: ** any thread to any of the functions listed in the second group above as
sl@0: ** long as the same connection is not in use by two threads at once and
sl@0: ** as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.  Additional
sl@0: ** information about the SQLITE_ENABLE_MEMORY_MANAGEMENT constraint is
sl@0: ** below.
sl@0: **
sl@0: ** The busy handler for all database connections should remain turned
sl@0: ** off.  That means that any lock contention will cause the associated
sl@0: ** sqlite3_client_step() call to return immediately with an SQLITE_BUSY
sl@0: ** error code.  If a busy handler is enabled and lock contention occurs,
sl@0: ** then the entire server thread will block.  This will cause not only
sl@0: ** the requesting client to block but every other database client as
sl@0: ** well.  It is possible to enhance the code below so that lock
sl@0: ** contention will cause the message to be placed back on the top of
sl@0: ** the queue to be tried again later.  But such enhanced processing is
sl@0: ** not included here, in order to keep the example simple.
sl@0: **
sl@0: ** This example code assumes the use of pthreads.  Pthreads
sl@0: ** implementations are available for windows.  (See, for example
sl@0: ** http://sourceware.org/pthreads-win32/announcement.html.)  Or, you
sl@0: ** can translate the locking and thread synchronization code to use
sl@0: ** windows primitives easily enough.  The details are left as an
sl@0: ** exercise to the reader.
sl@0: **
sl@0: **** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****
sl@0: **
sl@0: ** If you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT defined, then
sl@0: ** SQLite includes code that tracks how much memory is being used by
sl@0: ** each thread.  These memory counts can become confused if memory
sl@0: ** is allocated by one thread and then freed by another.  For that
sl@0: ** reason, when SQLITE_ENABLE_MEMORY_MANAGEMENT is used, all operations
sl@0: ** that might allocate or free memory should be performanced in the same
sl@0: ** thread that originally created the database connection.  In that case,
sl@0: ** many of the operations that are listed above as safe to be performed
sl@0: ** in separate threads would need to be sent over to the server to be
sl@0: ** done there.  If SQLITE_ENABLE_MEMORY_MANAGEMENT is defined, then
sl@0: ** the following functions can be used safely from different threads
sl@0: ** without messing up the allocation counts:
sl@0: **
sl@0: **       sqlite3_bind_parameter_name
sl@0: **       sqlite3_bind_parameter_index
sl@0: **       sqlite3_changes
sl@0: **       sqlite3_column_blob
sl@0: **       sqlite3_column_count
sl@0: **       sqlite3_complete
sl@0: **       sqlite3_data_count
sl@0: **       sqlite3_db_handle
sl@0: **       sqlite3_errcode
sl@0: **       sqlite3_errmsg
sl@0: **       sqlite3_last_insert_rowid
sl@0: **       sqlite3_total_changes
sl@0: **
sl@0: ** The remaining functions are not thread-safe when memory management
sl@0: ** is enabled.  So one would have to define some new interface routines
sl@0: ** along the following lines:
sl@0: **
sl@0: **       sqlite3_client_bind_*
sl@0: **       sqlite3_client_clear_bindings
sl@0: **       sqlite3_client_column_*
sl@0: **       sqlite3_client_create_collation
sl@0: **       sqlite3_client_create_function
sl@0: **       sqlite3_client_transfer_bindings
sl@0: **
sl@0: ** The example code in this file is intended for use with memory
sl@0: ** management turned off.  So the implementation of these additional
sl@0: ** client interfaces is left as an exercise to the reader.
sl@0: **
sl@0: ** It may seem surprising to the reader that the list of safe functions
sl@0: ** above does not include things like sqlite3_bind_int() or
sl@0: ** sqlite3_column_int().  But those routines might, in fact, allocate
sl@0: ** or deallocate memory.  In the case of sqlite3_bind_int(), if the
sl@0: ** parameter was previously bound to a string that string might need
sl@0: ** to be deallocated before the new integer value is inserted.  In
sl@0: ** the case of sqlite3_column_int(), the value of the column might be
sl@0: ** a UTF-16 string which will need to be converted to UTF-8 then into
sl@0: ** an integer.
sl@0: */
sl@0: 
sl@0: /* Include this to get the definition of SQLITE_THREADSAFE, in the
sl@0: ** case that default values are used.
sl@0: */
sl@0: #include "sqliteInt.h"
sl@0: 
sl@0: /*
sl@0: ** Only compile the code in this file on UNIX with a SQLITE_THREADSAFE build
sl@0: ** and only if the SQLITE_SERVER macro is defined.
sl@0: */
sl@0: #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
sl@0: #if defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE
sl@0: 
sl@0: /*
sl@0: ** We require only pthreads and the public interface of SQLite.
sl@0: */
sl@0: #include <pthread.h>
sl@0: #include "sqlite3.h"
sl@0: 
sl@0: /*
sl@0: ** Messages are passed from client to server and back again as 
sl@0: ** instances of the following structure.
sl@0: */
sl@0: typedef struct SqlMessage SqlMessage;
sl@0: struct SqlMessage {
sl@0:   int op;                      /* Opcode for the message */
sl@0:   sqlite3 *pDb;                /* The SQLite connection */
sl@0:   sqlite3_stmt *pStmt;         /* A specific statement */
sl@0:   int errCode;                 /* Error code returned */
sl@0:   const char *zIn;             /* Input filename or SQL statement */
sl@0:   int nByte;                   /* Size of the zIn parameter for prepare() */
sl@0:   const char *zOut;            /* Tail of the SQL statement */
sl@0:   SqlMessage *pNext;           /* Next message in the queue */
sl@0:   SqlMessage *pPrev;           /* Previous message in the queue */
sl@0:   pthread_mutex_t clientMutex; /* Hold this mutex to access the message */
sl@0:   pthread_cond_t clientWakeup; /* Signal to wake up the client */
sl@0: };
sl@0: 
sl@0: /*
sl@0: ** Legal values for SqlMessage.op
sl@0: */
sl@0: #define MSG_Open       1  /* sqlite3_open(zIn, &pDb) */
sl@0: #define MSG_Prepare    2  /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */
sl@0: #define MSG_Step       3  /* sqlite3_step(pStmt) */
sl@0: #define MSG_Reset      4  /* sqlite3_reset(pStmt) */
sl@0: #define MSG_Finalize   5  /* sqlite3_finalize(pStmt) */
sl@0: #define MSG_Close      6  /* sqlite3_close(pDb) */
sl@0: #define MSG_Done       7  /* Server has finished with this message */
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** State information about the server is stored in a static variable
sl@0: ** named "g" as follows:
sl@0: */
sl@0: static struct ServerState {
sl@0:   pthread_mutex_t queueMutex;   /* Hold this mutex to access the msg queue */
sl@0:   pthread_mutex_t serverMutex;  /* Held by the server while it is running */
sl@0:   pthread_cond_t serverWakeup;  /* Signal this condvar to wake up the server */
sl@0:   volatile int serverHalt;      /* Server halts itself when true */
sl@0:   SqlMessage *pQueueHead;       /* Head of the message queue */
sl@0:   SqlMessage *pQueueTail;       /* Tail of the message queue */
sl@0: } g = {
sl@0:   PTHREAD_MUTEX_INITIALIZER,
sl@0:   PTHREAD_MUTEX_INITIALIZER,
sl@0:   PTHREAD_COND_INITIALIZER,
sl@0: };
sl@0: 
sl@0: /*
sl@0: ** Send a message to the server.  Block until we get a reply.
sl@0: **
sl@0: ** The mutex and condition variable in the message are uninitialized
sl@0: ** when this routine is called.  This routine takes care of 
sl@0: ** initializing them and destroying them when it has finished.
sl@0: */
sl@0: static void sendToServer(SqlMessage *pMsg){
sl@0:   /* Initialize the mutex and condition variable on the message
sl@0:   */
sl@0:   pthread_mutex_init(&pMsg->clientMutex, 0);
sl@0:   pthread_cond_init(&pMsg->clientWakeup, 0);
sl@0: 
sl@0:   /* Add the message to the head of the server's message queue.
sl@0:   */
sl@0:   pthread_mutex_lock(&g.queueMutex);
sl@0:   pMsg->pNext = g.pQueueHead;
sl@0:   if( g.pQueueHead==0 ){
sl@0:     g.pQueueTail = pMsg;
sl@0:   }else{
sl@0:     g.pQueueHead->pPrev = pMsg;
sl@0:   }
sl@0:   pMsg->pPrev = 0;
sl@0:   g.pQueueHead = pMsg;
sl@0:   pthread_mutex_unlock(&g.queueMutex);
sl@0: 
sl@0:   /* Signal the server that the new message has be queued, then
sl@0:   ** block waiting for the server to process the message.
sl@0:   */
sl@0:   pthread_mutex_lock(&pMsg->clientMutex);
sl@0:   pthread_cond_signal(&g.serverWakeup);
sl@0:   while( pMsg->op!=MSG_Done ){
sl@0:     pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex);
sl@0:   }
sl@0:   pthread_mutex_unlock(&pMsg->clientMutex);
sl@0: 
sl@0:   /* Destroy the mutex and condition variable of the message.
sl@0:   */
sl@0:   pthread_mutex_destroy(&pMsg->clientMutex);
sl@0:   pthread_cond_destroy(&pMsg->clientWakeup);
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** The following 6 routines are client-side implementations of the
sl@0: ** core SQLite interfaces:
sl@0: **
sl@0: **        sqlite3_open
sl@0: **        sqlite3_prepare
sl@0: **        sqlite3_step
sl@0: **        sqlite3_reset
sl@0: **        sqlite3_finalize
sl@0: **        sqlite3_close
sl@0: **
sl@0: ** Clients should use the following client-side routines instead of 
sl@0: ** the core routines above.
sl@0: **
sl@0: **        sqlite3_client_open
sl@0: **        sqlite3_client_prepare
sl@0: **        sqlite3_client_step
sl@0: **        sqlite3_client_reset
sl@0: **        sqlite3_client_finalize
sl@0: **        sqlite3_client_close
sl@0: **
sl@0: ** Each of these routines creates a message for the desired operation,
sl@0: ** sends that message to the server, waits for the server to process
sl@0: ** then message and return a response.
sl@0: */
sl@0: int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Open;
sl@0:   msg.zIn = zDatabaseName;
sl@0:   sendToServer(&msg);
sl@0:   *ppDb = msg.pDb;
sl@0:   return msg.errCode;
sl@0: }
sl@0: int sqlite3_client_prepare(
sl@0:   sqlite3 *pDb,
sl@0:   const char *zSql,
sl@0:   int nByte,
sl@0:   sqlite3_stmt **ppStmt,
sl@0:   const char **pzTail
sl@0: ){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Prepare;
sl@0:   msg.pDb = pDb;
sl@0:   msg.zIn = zSql;
sl@0:   msg.nByte = nByte;
sl@0:   sendToServer(&msg);
sl@0:   *ppStmt = msg.pStmt;
sl@0:   if( pzTail ) *pzTail = msg.zOut;
sl@0:   return msg.errCode;
sl@0: }
sl@0: int sqlite3_client_step(sqlite3_stmt *pStmt){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Step;
sl@0:   msg.pStmt = pStmt;
sl@0:   sendToServer(&msg);
sl@0:   return msg.errCode;
sl@0: }
sl@0: int sqlite3_client_reset(sqlite3_stmt *pStmt){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Reset;
sl@0:   msg.pStmt = pStmt;
sl@0:   sendToServer(&msg);
sl@0:   return msg.errCode;
sl@0: }
sl@0: int sqlite3_client_finalize(sqlite3_stmt *pStmt){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Finalize;
sl@0:   msg.pStmt = pStmt;
sl@0:   sendToServer(&msg);
sl@0:   return msg.errCode;
sl@0: }
sl@0: int sqlite3_client_close(sqlite3 *pDb){
sl@0:   SqlMessage msg;
sl@0:   msg.op = MSG_Close;
sl@0:   msg.pDb = pDb;
sl@0:   sendToServer(&msg);
sl@0:   return msg.errCode;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** This routine implements the server.  To start the server, first
sl@0: ** make sure g.serverHalt is false, then create a new detached thread
sl@0: ** on this procedure.  See the sqlite3_server_start() routine below
sl@0: ** for an example.  This procedure loops until g.serverHalt becomes
sl@0: ** true.
sl@0: */
sl@0: void *sqlite3_server(void *NotUsed){
sl@0:   if( pthread_mutex_trylock(&g.serverMutex) ){
sl@0:     return 0;  /* Another server is already running */
sl@0:   }
sl@0:   sqlite3_enable_shared_cache(1);
sl@0:   while( !g.serverHalt ){
sl@0:     SqlMessage *pMsg;
sl@0: 
sl@0:     /* Remove the last message from the message queue.
sl@0:     */
sl@0:     pthread_mutex_lock(&g.queueMutex);
sl@0:     while( g.pQueueTail==0 && g.serverHalt==0 ){
sl@0:       pthread_cond_wait(&g.serverWakeup, &g.queueMutex);
sl@0:     }
sl@0:     pMsg = g.pQueueTail;
sl@0:     if( pMsg ){
sl@0:       if( pMsg->pPrev ){
sl@0:         pMsg->pPrev->pNext = 0;
sl@0:       }else{
sl@0:         g.pQueueHead = 0;
sl@0:       }
sl@0:       g.pQueueTail = pMsg->pPrev;
sl@0:     }
sl@0:     pthread_mutex_unlock(&g.queueMutex);
sl@0:     if( pMsg==0 ) break;
sl@0: 
sl@0:     /* Process the message just removed
sl@0:     */
sl@0:     pthread_mutex_lock(&pMsg->clientMutex);
sl@0:     switch( pMsg->op ){
sl@0:       case MSG_Open: {
sl@0:         pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb);
sl@0:         break;
sl@0:       }
sl@0:       case MSG_Prepare: {
sl@0:         pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,
sl@0:                                         &pMsg->pStmt, &pMsg->zOut);
sl@0:         break;
sl@0:       }
sl@0:       case MSG_Step: {
sl@0:         pMsg->errCode = sqlite3_step(pMsg->pStmt);
sl@0:         break;
sl@0:       }
sl@0:       case MSG_Reset: {
sl@0:         pMsg->errCode = sqlite3_reset(pMsg->pStmt);
sl@0:         break;
sl@0:       }
sl@0:       case MSG_Finalize: {
sl@0:         pMsg->errCode = sqlite3_finalize(pMsg->pStmt);
sl@0:         break;
sl@0:       }
sl@0:       case MSG_Close: {
sl@0:         pMsg->errCode = sqlite3_close(pMsg->pDb);
sl@0:         break;
sl@0:       }
sl@0:     }
sl@0: 
sl@0:     /* Signal the client that the message has been processed.
sl@0:     */
sl@0:     pMsg->op = MSG_Done;
sl@0:     pthread_mutex_unlock(&pMsg->clientMutex);
sl@0:     pthread_cond_signal(&pMsg->clientWakeup);
sl@0:   }
sl@0:   sqlite3_thread_cleanup();
sl@0:   pthread_mutex_unlock(&g.serverMutex);
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Start a server thread if one is not already running.  If there
sl@0: ** is aleady a server thread running, the new thread will quickly
sl@0: ** die and this routine is effectively a no-op.
sl@0: */
sl@0: void sqlite3_server_start(void){
sl@0:   pthread_t x;
sl@0:   int rc;
sl@0:   g.serverHalt = 0;
sl@0:   rc = pthread_create(&x, 0, sqlite3_server, 0);
sl@0:   if( rc==0 ){
sl@0:     pthread_detach(x);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** If a server thread is running, then stop it.  If no server is
sl@0: ** running, this routine is effectively a no-op.
sl@0: **
sl@0: ** This routine waits until the server has actually stopped before
sl@0: ** returning.
sl@0: */
sl@0: void sqlite3_server_stop(void){
sl@0:   g.serverHalt = 1;
sl@0:   pthread_cond_broadcast(&g.serverWakeup);
sl@0:   pthread_mutex_lock(&g.serverMutex);
sl@0:   pthread_mutex_unlock(&g.serverMutex);
sl@0: }
sl@0: 
sl@0: #endif /* defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE */
sl@0: #endif /* defined(SQLITE_SERVER) */