Back to index

lightning-sunbird  0.9+nobinonly
test_server.c
Go to the documentation of this file.
00001 /*
00002 ** 2006 January 07
00003 **
00004 ** The author disclaims copyright to this source code.  In place of
00005 ** a legal notice, here is a blessing:
00006 **
00007 **    May you do good and not evil.
00008 **    May you find forgiveness for yourself and forgive others.
00009 **    May you share freely, never taking more than you give.
00010 **
00011 ******************************************************************************
00012 **
00013 ** This file contains demonstration code.  Nothing in this file gets compiled
00014 ** or linked into the SQLite library unless you use a non-standard option:
00015 **
00016 **      -DSQLITE_SERVER=1
00017 **
00018 ** The configure script will never generate a Makefile with the option
00019 ** above.  You will need to manually modify the Makefile if you want to
00020 ** include any of the code from this file in your project.  Or, at your
00021 ** option, you may copy and paste the code from this file and
00022 ** thereby avoiding a recompile of SQLite.
00023 **
00024 **
00025 ** This source file demonstrates how to use SQLite to create an SQL database 
00026 ** server thread in a multiple-threaded program.  One or more client threads
00027 ** send messages to the server thread and the server thread processes those
00028 ** messages in the order received and returns the results to the client.
00029 **
00030 ** One might ask:  "Why bother?  Why not just let each thread connect
00031 ** to the database directly?"  There are a several of reasons to
00032 ** prefer the client/server approach.
00033 **
00034 **    (1)  Some systems (ex: Redhat9) have broken threading implementations
00035 **         that prevent SQLite database connections from being used in
00036 **         a thread different from the one where they were created.  With
00037 **         the client/server approach, all database connections are created
00038 **         and used within the server thread.  Client calls to the database
00039 **         can be made from multiple threads (though not at the same time!)
00040 **
00041 **    (2)  Beginning with SQLite version 3.3.0, when two or more 
00042 **         connections to the same database occur within the same thread,
00043 **         they can optionally share their database cache.  This reduces
00044 **         I/O and memory requirements.  Cache shared is controlled using
00045 **         the sqlite3_enable_shared_cache() API.
00046 **
00047 **    (3)  Database connections on a shared cache use table-level locking
00048 **         instead of file-level locking for improved concurrency.
00049 **
00050 **    (4)  Database connections on a shared cache can by optionally
00051 **         set to READ UNCOMMITTED isolation.  (The default isolation for
00052 **         SQLite is SERIALIZABLE.)  When this occurs, readers will
00053 **         never be blocked by a writer and writers will not be
00054 **         blocked by readers.  There can still only be a single writer
00055 **         at a time, but multiple readers can simultaneously exist with
00056 **         that writer.  This is a huge increase in concurrency.
00057 **
00058 ** To summarize the rational for using a client/server approach: prior
00059 ** to SQLite version 3.3.0 it probably was not worth the trouble.  But
00060 ** with SQLite version 3.3.0 and beyond you can get significant performance
00061 ** and concurrency improvements and memory usage reductions by going
00062 ** client/server.
00063 **
00064 ** Note:  The extra features of version 3.3.0 described by points (2)
00065 ** through (4) above are only available if you compile without the
00066 ** option -DSQLITE_OMIT_SHARED_CACHE. 
00067 **
00068 ** Here is how the client/server approach works:  The database server
00069 ** thread is started on this procedure:
00070 **
00071 **       void *sqlite3_server(void *NotUsed);
00072 **
00073 ** The sqlite_server procedure runs as long as the g.serverHalt variable
00074 ** is false.  A mutex is used to make sure no more than one server runs
00075 ** at a time.  The server waits for messages to arrive on a message
00076 ** queue and processes the messages in order.
00077 **
00078 ** Two convenience routines are provided for starting and stopping the
00079 ** server thread:
00080 **
00081 **       void sqlite3_server_start(void);
00082 **       void sqlite3_server_stop(void);
00083 **
00084 ** Both of the convenience routines return immediately.  Neither will
00085 ** ever give an error.  If a server is already started or already halted,
00086 ** then the routines are effectively no-ops.
00087 **
00088 ** Clients use the following interfaces:
00089 **
00090 **       sqlite3_client_open
00091 **       sqlite3_client_prepare
00092 **       sqlite3_client_step
00093 **       sqlite3_client_reset
00094 **       sqlite3_client_finalize
00095 **       sqlite3_client_close
00096 **
00097 ** These interfaces work exactly like the standard core SQLite interfaces
00098 ** having the same names without the "_client_" infix.  Many other SQLite
00099 ** interfaces can be used directly without having to send messages to the
00100 ** server as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.
00101 ** The following interfaces fall into this second category:
00102 **
00103 **       sqlite3_bind_*
00104 **       sqlite3_changes
00105 **       sqlite3_clear_bindings
00106 **       sqlite3_column_*
00107 **       sqlite3_complete
00108 **       sqlite3_create_collation
00109 **       sqlite3_create_function
00110 **       sqlite3_data_count
00111 **       sqlite3_db_handle
00112 **       sqlite3_errcode
00113 **       sqlite3_errmsg
00114 **       sqlite3_last_insert_rowid
00115 **       sqlite3_total_changes
00116 **       sqlite3_transfer_bindings
00117 **
00118 ** A single SQLite connection (an sqlite3* object) or an SQLite statement
00119 ** (an sqlite3_stmt* object) should only be passed to a single interface
00120 ** function at a time.  The connections and statements can be passed from
00121 ** any thread to any of the functions listed in the second group above as
00122 ** long as the same connection is not in use by two threads at once and
00123 ** as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.  Additional
00124 ** information about the SQLITE_ENABLE_MEMORY_MANAGEMENT constraint is
00125 ** below.
00126 **
00127 ** The busy handler for all database connections should remain turned
00128 ** off.  That means that any lock contention will cause the associated
00129 ** sqlite3_client_step() call to return immediately with an SQLITE_BUSY
00130 ** error code.  If a busy handler is enabled and lock contention occurs,
00131 ** then the entire server thread will block.  This will cause not only
00132 ** the requesting client to block but every other database client as
00133 ** well.  It is possible to enhance the code below so that lock
00134 ** contention will cause the message to be placed back on the top of
00135 ** the queue to be tried again later.  But such enhanced processing is
00136 ** not included here, in order to keep the example simple.
00137 **
00138 ** This example code assumes the use of pthreads.  Pthreads
00139 ** implementations are available for windows.  (See, for example
00140 ** http://sourceware.org/pthreads-win32/announcement.html.)  Or, you
00141 ** can translate the locking and thread synchronization code to use
00142 ** windows primitives easily enough.  The details are left as an
00143 ** exercise to the reader.
00144 **
00145 **** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****
00146 **
00147 ** If you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT defined, then
00148 ** SQLite includes code that tracks how much memory is being used by
00149 ** each thread.  These memory counts can become confused if memory
00150 ** is allocated by one thread and then freed by another.  For that
00151 ** reason, when SQLITE_ENABLE_MEMORY_MANAGEMENT is used, all operations
00152 ** that might allocate or free memory should be performanced in the same
00153 ** thread that originally created the database connection.  In that case,
00154 ** many of the operations that are listed above as safe to be performed
00155 ** in separate threads would need to be sent over to the server to be
00156 ** done there.  If SQLITE_ENABLE_MEMORY_MANAGEMENT is defined, then
00157 ** the following functions can be used safely from different threads
00158 ** without messing up the allocation counts:
00159 **
00160 **       sqlite3_bind_parameter_name
00161 **       sqlite3_bind_parameter_index
00162 **       sqlite3_changes
00163 **       sqlite3_column_blob
00164 **       sqlite3_column_count
00165 **       sqlite3_complete
00166 **       sqlite3_data_count
00167 **       sqlite3_db_handle
00168 **       sqlite3_errcode
00169 **       sqlite3_errmsg
00170 **       sqlite3_last_insert_rowid
00171 **       sqlite3_total_changes
00172 **
00173 ** The remaining functions are not thread-safe when memory management
00174 ** is enabled.  So one would have to define some new interface routines
00175 ** along the following lines:
00176 **
00177 **       sqlite3_client_bind_*
00178 **       sqlite3_client_clear_bindings
00179 **       sqlite3_client_column_*
00180 **       sqlite3_client_create_collation
00181 **       sqlite3_client_create_function
00182 **       sqlite3_client_transfer_bindings
00183 **
00184 ** The example code in this file is intended for use with memory
00185 ** management turned off.  So the implementation of these additional
00186 ** client interfaces is left as an exercise to the reader.
00187 **
00188 ** It may seem surprising to the reader that the list of safe functions
00189 ** above does not include things like sqlite3_bind_int() or
00190 ** sqlite3_column_int().  But those routines might, in fact, allocate
00191 ** or deallocate memory.  In the case of sqlite3_bind_int(), if the
00192 ** parameter was previously bound to a string that string might need
00193 ** to be deallocated before the new integer value is inserted.  In
00194 ** the case of sqlite3_column_int(), the value of the column might be
00195 ** a UTF-16 string which will need to be converted to UTF-8 then into
00196 ** an integer.
00197 */
00198 
00199 /*
00200 ** Only compile the code in this file on UNIX with a THREADSAFE build
00201 ** and only if the SQLITE_SERVER macro is defined.
00202 */
00203 #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
00204 #if defined(OS_UNIX) && OS_UNIX && defined(THREADSAFE) && THREADSAFE
00205 
00206 /*
00207 ** We require only pthreads and the public interface of SQLite.
00208 */
00209 #include <pthread.h>
00210 #include "sqlite3.h"
00211 
00212 /*
00213 ** Messages are passed from client to server and back again as 
00214 ** instances of the following structure.
00215 */
00216 typedef struct SqlMessage SqlMessage;
00217 struct SqlMessage {
00218   int op;                      /* Opcode for the message */
00219   sqlite3 *pDb;                /* The SQLite connection */
00220   sqlite3_stmt *pStmt;         /* A specific statement */
00221   int errCode;                 /* Error code returned */
00222   const char *zIn;             /* Input filename or SQL statement */
00223   int nByte;                   /* Size of the zIn parameter for prepare() */
00224   const char *zOut;            /* Tail of the SQL statement */
00225   SqlMessage *pNext;           /* Next message in the queue */
00226   SqlMessage *pPrev;           /* Previous message in the queue */
00227   pthread_mutex_t clientMutex; /* Hold this mutex to access the message */
00228   pthread_cond_t clientWakeup; /* Signal to wake up the client */
00229 };
00230 
00231 /*
00232 ** Legal values for SqlMessage.op
00233 */
00234 #define MSG_Open       1  /* sqlite3_open(zIn, &pDb) */
00235 #define MSG_Prepare    2  /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */
00236 #define MSG_Step       3  /* sqlite3_step(pStmt) */
00237 #define MSG_Reset      4  /* sqlite3_reset(pStmt) */
00238 #define MSG_Finalize   5  /* sqlite3_finalize(pStmt) */
00239 #define MSG_Close      6  /* sqlite3_close(pDb) */
00240 #define MSG_Done       7  /* Server has finished with this message */
00241 
00242 
00243 /*
00244 ** State information about the server is stored in a static variable
00245 ** named "g" as follows:
00246 */
00247 static struct ServerState {
00248   pthread_mutex_t queueMutex;   /* Hold this mutex to access the msg queue */
00249   pthread_mutex_t serverMutex;  /* Held by the server while it is running */
00250   pthread_cond_t serverWakeup;  /* Signal this condvar to wake up the server */
00251   volatile int serverHalt;      /* Server halts itself when true */
00252   SqlMessage *pQueueHead;       /* Head of the message queue */
00253   SqlMessage *pQueueTail;       /* Tail of the message queue */
00254 } g = {
00255   PTHREAD_MUTEX_INITIALIZER,
00256   PTHREAD_MUTEX_INITIALIZER,
00257   PTHREAD_COND_INITIALIZER,
00258 };
00259 
00260 /*
00261 ** Send a message to the server.  Block until we get a reply.
00262 **
00263 ** The mutex and condition variable in the message are uninitialized
00264 ** when this routine is called.  This routine takes care of 
00265 ** initializing them and destroying them when it has finished.
00266 */
00267 static void sendToServer(SqlMessage *pMsg){
00268   /* Initialize the mutex and condition variable on the message
00269   */
00270   pthread_mutex_init(&pMsg->clientMutex, 0);
00271   pthread_cond_init(&pMsg->clientWakeup, 0);
00272 
00273   /* Add the message to the head of the server's message queue.
00274   */
00275   pthread_mutex_lock(&g.queueMutex);
00276   pMsg->pNext = g.pQueueHead;
00277   if( g.pQueueHead==0 ){
00278     g.pQueueTail = pMsg;
00279   }else{
00280     g.pQueueHead->pPrev = pMsg;
00281   }
00282   pMsg->pPrev = 0;
00283   g.pQueueHead = pMsg;
00284   pthread_mutex_unlock(&g.queueMutex);
00285 
00286   /* Signal the server that the new message has be queued, then
00287   ** block waiting for the server to process the message.
00288   */
00289   pthread_mutex_lock(&pMsg->clientMutex);
00290   pthread_cond_signal(&g.serverWakeup);
00291   while( pMsg->op!=MSG_Done ){
00292     pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex);
00293   }
00294   pthread_mutex_unlock(&pMsg->clientMutex);
00295 
00296   /* Destroy the mutex and condition variable of the message.
00297   */
00298   pthread_mutex_destroy(&pMsg->clientMutex);
00299   pthread_cond_destroy(&pMsg->clientWakeup);
00300 }
00301 
00302 /*
00303 ** The following 6 routines are client-side implementations of the
00304 ** core SQLite interfaces:
00305 **
00306 **        sqlite3_open
00307 **        sqlite3_prepare
00308 **        sqlite3_step
00309 **        sqlite3_reset
00310 **        sqlite3_finalize
00311 **        sqlite3_close
00312 **
00313 ** Clients should use the following client-side routines instead of 
00314 ** the core routines above.
00315 **
00316 **        sqlite3_client_open
00317 **        sqlite3_client_prepare
00318 **        sqlite3_client_step
00319 **        sqlite3_client_reset
00320 **        sqlite3_client_finalize
00321 **        sqlite3_client_close
00322 **
00323 ** Each of these routines creates a message for the desired operation,
00324 ** sends that message to the server, waits for the server to process
00325 ** then message and return a response.
00326 */
00327 int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){
00328   SqlMessage msg;
00329   msg.op = MSG_Open;
00330   msg.zIn = zDatabaseName;
00331   sendToServer(&msg);
00332   *ppDb = msg.pDb;
00333   return msg.errCode;
00334 }
00335 int sqlite3_client_prepare(
00336   sqlite3 *pDb,
00337   const char *zSql,
00338   int nByte,
00339   sqlite3_stmt **ppStmt,
00340   const char **pzTail
00341 ){
00342   SqlMessage msg;
00343   msg.op = MSG_Prepare;
00344   msg.pDb = pDb;
00345   msg.zIn = zSql;
00346   msg.nByte = nByte;
00347   sendToServer(&msg);
00348   *ppStmt = msg.pStmt;
00349   if( pzTail ) *pzTail = msg.zOut;
00350   return msg.errCode;
00351 }
00352 int sqlite3_client_step(sqlite3_stmt *pStmt){
00353   SqlMessage msg;
00354   msg.op = MSG_Step;
00355   msg.pStmt = pStmt;
00356   sendToServer(&msg);
00357   return msg.errCode;
00358 }
00359 int sqlite3_client_reset(sqlite3_stmt *pStmt){
00360   SqlMessage msg;
00361   msg.op = MSG_Reset;
00362   msg.pStmt = pStmt;
00363   sendToServer(&msg);
00364   return msg.errCode;
00365 }
00366 int sqlite3_client_finalize(sqlite3_stmt *pStmt){
00367   SqlMessage msg;
00368   msg.op = MSG_Finalize;
00369   msg.pStmt = pStmt;
00370   sendToServer(&msg);
00371   return msg.errCode;
00372 }
00373 int sqlite3_client_close(sqlite3 *pDb){
00374   SqlMessage msg;
00375   msg.op = MSG_Close;
00376   msg.pDb = pDb;
00377   sendToServer(&msg);
00378   return msg.errCode;
00379 }
00380 
00381 /*
00382 ** This routine implements the server.  To start the server, first
00383 ** make sure g.serverHalt is false, then create a new detached thread
00384 ** on this procedure.  See the sqlite3_server_start() routine below
00385 ** for an example.  This procedure loops until g.serverHalt becomes
00386 ** true.
00387 */
00388 void *sqlite3_server(void *NotUsed){
00389   sqlite3_enable_shared_cache(1);
00390   if( pthread_mutex_trylock(&g.serverMutex) ){
00391     sqlite3_enable_shared_cache(0);
00392     return 0;  /* Another server is already running */
00393   }
00394   while( !g.serverHalt ){
00395     SqlMessage *pMsg;
00396 
00397     /* Remove the last message from the message queue.
00398     */
00399     pthread_mutex_lock(&g.queueMutex);
00400     while( g.pQueueTail==0 && g.serverHalt==0 ){
00401       pthread_cond_wait(&g.serverWakeup, &g.queueMutex);
00402     }
00403     pMsg = g.pQueueTail;
00404     if( pMsg ){
00405       if( pMsg->pPrev ){
00406         pMsg->pPrev->pNext = 0;
00407       }else{
00408         g.pQueueHead = 0;
00409       }
00410       g.pQueueTail = pMsg->pPrev;
00411     }
00412     pthread_mutex_unlock(&g.queueMutex);
00413     if( pMsg==0 ) break;
00414 
00415     /* Process the message just removed
00416     */
00417     pthread_mutex_lock(&pMsg->clientMutex);
00418     switch( pMsg->op ){
00419       case MSG_Open: {
00420         pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb);
00421         break;
00422       }
00423       case MSG_Prepare: {
00424         pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,
00425                                         &pMsg->pStmt, &pMsg->zOut);
00426         break;
00427       }
00428       case MSG_Step: {
00429         pMsg->errCode = sqlite3_step(pMsg->pStmt);
00430         break;
00431       }
00432       case MSG_Reset: {
00433         pMsg->errCode = sqlite3_reset(pMsg->pStmt);
00434         break;
00435       }
00436       case MSG_Finalize: {
00437         pMsg->errCode = sqlite3_finalize(pMsg->pStmt);
00438         break;
00439       }
00440       case MSG_Close: {
00441         pMsg->errCode = sqlite3_close(pMsg->pDb);
00442         break;
00443       }
00444     }
00445 
00446     /* Signal the client that the message has been processed.
00447     */
00448     pMsg->op = MSG_Done;
00449     pthread_mutex_unlock(&pMsg->clientMutex);
00450     pthread_cond_signal(&pMsg->clientWakeup);
00451   }
00452   pthread_mutex_unlock(&g.serverMutex);
00453   sqlite3_thread_cleanup();
00454   return 0;
00455 }
00456 
00457 /*
00458 ** Start a server thread if one is not already running.  If there
00459 ** is aleady a server thread running, the new thread will quickly
00460 ** die and this routine is effectively a no-op.
00461 */
00462 void sqlite3_server_start(void){
00463   pthread_t x;
00464   int rc;
00465   g.serverHalt = 0;
00466   rc = pthread_create(&x, 0, sqlite3_server, 0);
00467   if( rc==0 ){
00468     pthread_detach(x);
00469   }
00470 }
00471 
00472 /*
00473 ** If a server thread is running, then stop it.  If no server is
00474 ** running, this routine is effectively a no-op.
00475 **
00476 ** This routine returns immediately without waiting for the server
00477 ** thread to stop.  But be assured that the server will eventually stop.
00478 */
00479 void sqlite3_server_stop(void){
00480   g.serverHalt = 1;
00481   pthread_cond_broadcast(&g.serverWakeup);
00482 }
00483 
00484 #endif /* defined(OS_UNIX) && OS_UNIX && defined(THREADSAFE) && THREADSAFE */
00485 #endif /* defined(SQLITE_SERVER) */