src/test_server.c

   1 /*
   2 ** 2006 January 07
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 ******************************************************************************
  12 **
  13 ** This file contains demonstration code.  Nothing in this file gets compiled
  14 ** or linked into the SQLite library unless you use a non-standard option:
  15 **
  16 **      -DSQLITE_SERVER=1
  17 **
  18 ** The configure script will never generate a Makefile with the option
  19 ** above.  You will need to manually modify the Makefile if you want to
  20 ** include any of the code from this file in your project.  Or, at your
  21 ** option, you may copy and paste the code from this file and
  22 ** thereby avoiding a recompile of SQLite.
  23 **
  24 **
  25 ** This source file demonstrates how to use SQLite to create an SQL database
  26 ** server thread in a multiple-threaded program.  One or more client threads
  27 ** send messages to the server thread and the server thread processes those
  28 ** messages in the order received and returns the results to the client.
  29 **
  30 ** One might ask:  "Why bother?  Why not just let each thread connect
  31 ** to the database directly?"  There are a several of reasons to
  32 ** prefer the client/server approach.
  33 **
  34 **    (1)  Some systems (ex: Redhat9) have broken threading implementations
  35 **         that prevent SQLite database connections from being used in
  36 **         a thread different from the one where they were created.  With
  37 **         the client/server approach, all database connections are created
  38 **         and used within the server thread.  Client calls to the database
  39 **         can be made from multiple threads (though not at the same time!)
  40 **
  41 **    (2)  Beginning with SQLite version 3.3.0, when two or more
  42 **         connections to the same database occur within the same thread,
  43 **         they can optionally share their database cache.  This reduces
  44 **         I/O and memory requirements.  Cache shared is controlled using
  45 **         the sqlite3_enable_shared_cache() API.
  46 **
  47 **    (3)  Database connections on a shared cache use table-level locking
  48 **         instead of file-level locking for improved concurrency.
  49 **
  50 **    (4)  Database connections on a shared cache can by optionally
  51 **         set to READ UNCOMMITTED isolation.  (The default isolation for
  52 **         SQLite is SERIALIZABLE.)  When this occurs, readers will
  53 **         never be blocked by a writer and writers will not be
  54 **         blocked by readers.  There can still only be a single writer
  55 **         at a time, but multiple readers can simultaneously exist with
  56 **         that writer.  This is a huge increase in concurrency.
  57 **
  58 ** To summarize the rational for using a client/server approach: prior
  59 ** to SQLite version 3.3.0 it probably was not worth the trouble.  But
  60 ** with SQLite version 3.3.0 and beyond you can get significant performance
  61 ** and concurrency improvements and memory usage reductions by going
  62 ** client/server.
  63 **
  64 ** Note:  The extra features of version 3.3.0 described by points (2)
  65 ** through (4) above are only available if you compile without the
  66 ** option -DSQLITE_OMIT_SHARED_CACHE.
  67 **
  68 ** Here is how the client/server approach works:  The database server
  69 ** thread is started on this procedure:
  70 **
  71 **       void *sqlite3_server(void *NotUsed);
  72 **
  73 ** The sqlite_server procedure runs as long as the g.serverHalt variable
  74 ** is false.  A mutex is used to make sure no more than one server runs
  75 ** at a time.  The server waits for messages to arrive on a message
  76 ** queue and processes the messages in order.
  77 **
  78 ** Two convenience routines are provided for starting and stopping the
  79 ** server thread:
  80 **
  81 **       void sqlite3_server_start(void);
  82 **       void sqlite3_server_stop(void);
  83 **
  84 ** Both of the convenience routines return immediately.  Neither will
  85 ** ever give an error.  If a server is already started or already halted,
  86 ** then the routines are effectively no-ops.
  87 **
  88 ** Clients use the following interfaces:
  89 **
  90 **       sqlite3_client_open
  91 **       sqlite3_client_prepare
  92 **       sqlite3_client_step
  93 **       sqlite3_client_reset
  94 **       sqlite3_client_finalize
  95 **       sqlite3_client_close
  96 **
  97 ** These interfaces work exactly like the standard core SQLite interfaces
  98 ** having the same names without the "_client_" infix.  Many other SQLite
  99 ** interfaces can be used directly without having to send messages to the
 100 ** server as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.
 101 ** The following interfaces fall into this second category:
 102 **
 103 **       sqlite3_bind_*
 104 **       sqlite3_changes
 105 **       sqlite3_clear_bindings
 106 **       sqlite3_column_*
 107 **       sqlite3_complete
 108 **       sqlite3_create_collation
 109 **       sqlite3_create_function
 110 **       sqlite3_data_count
 111 **       sqlite3_db_handle
 112 **       sqlite3_errcode
 113 **       sqlite3_errmsg
 114 **       sqlite3_last_insert_rowid
 115 **       sqlite3_total_changes
 116 **       sqlite3_transfer_bindings
 117 **
 118 ** A single SQLite connection (an sqlite3* object) or an SQLite statement
 119 ** (an sqlite3_stmt* object) should only be passed to a single interface
 120 ** function at a time.  The connections and statements can be passed from
 121 ** any thread to any of the functions listed in the second group above as
 122 ** long as the same connection is not in use by two threads at once and
 123 ** as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.  Additional
 124 ** information about the SQLITE_ENABLE_MEMORY_MANAGEMENT constraint is
 125 ** below.
 126 **
 127 ** The busy handler for all database connections should remain turned
 128 ** off.  That means that any lock contention will cause the associated
 129 ** sqlite3_client_step() call to return immediately with an SQLITE_BUSY
 130 ** error code.  If a busy handler is enabled and lock contention occurs,
 131 ** then the entire server thread will block.  This will cause not only
 132 ** the requesting client to block but every other database client as
 133 ** well.  It is possible to enhance the code below so that lock
 134 ** contention will cause the message to be placed back on the top of
 135 ** the queue to be tried again later.  But such enhanced processing is
 136 ** not included here, in order to keep the example simple.
 137 **
 138 ** This example code assumes the use of pthreads.  Pthreads
 139 ** implementations are available for windows.  (See, for example
 140 ** http://sourceware.org/pthreads-win32/announcement.html.)  Or, you
 141 ** can translate the locking and thread synchronization code to use
 142 ** windows primitives easily enough.  The details are left as an
 143 ** exercise to the reader.
 144 **
 145 **** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****
 146 **
 147 ** If you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT defined, then
 148 ** SQLite includes code that tracks how much memory is being used by
 149 ** each thread.  These memory counts can become confused if memory
 150 ** is allocated by one thread and then freed by another.  For that
 151 ** reason, when SQLITE_ENABLE_MEMORY_MANAGEMENT is used, all operations
 152 ** that might allocate or free memory should be performanced in the same
 153 ** thread that originally created the database connection.  In that case,
 154 ** many of the operations that are listed above as safe to be performed
 155 ** in separate threads would need to be sent over to the server to be
 156 ** done there.  If SQLITE_ENABLE_MEMORY_MANAGEMENT is defined, then
 157 ** the following functions can be used safely from different threads
 158 ** without messing up the allocation counts:
 159 **
 160 **       sqlite3_bind_parameter_name
 161 **       sqlite3_bind_parameter_index
 162 **       sqlite3_changes
 163 **       sqlite3_column_blob
 164 **       sqlite3_column_count
 165 **       sqlite3_complete
 166 **       sqlite3_data_count
 167 **       sqlite3_db_handle
 168 **       sqlite3_errcode
 169 **       sqlite3_errmsg
 170 **       sqlite3_last_insert_rowid
 171 **       sqlite3_total_changes
 172 **
 173 ** The remaining functions are not thread-safe when memory management
 174 ** is enabled.  So one would have to define some new interface routines
 175 ** along the following lines:
 176 **
 177 **       sqlite3_client_bind_*
 178 **       sqlite3_client_clear_bindings
 179 **       sqlite3_client_column_*
 180 **       sqlite3_client_create_collation
 181 **       sqlite3_client_create_function
 182 **       sqlite3_client_transfer_bindings
 183 **
 184 ** The example code in this file is intended for use with memory
 185 ** management turned off.  So the implementation of these additional
 186 ** client interfaces is left as an exercise to the reader.
 187 **
 188 ** It may seem surprising to the reader that the list of safe functions
 189 ** above does not include things like sqlite3_bind_int() or
 190 ** sqlite3_column_int().  But those routines might, in fact, allocate
 191 ** or deallocate memory.  In the case of sqlite3_bind_int(), if the
 192 ** parameter was previously bound to a string that string might need
 193 ** to be deallocated before the new integer value is inserted.  In
 194 ** the case of sqlite3_column_int(), the value of the column might be
 195 ** a UTF-16 string which will need to be converted to UTF-8 then into
 196 ** an integer.
 197 */
 198
 199 /* Include this to get the definition of SQLITE_THREADSAFE, in the
 200 ** case that default values are used.
 201 */
 202 #include "sqliteInt.h"
 203
 204 /*
 205 ** Only compile the code in this file on UNIX with a SQLITE_THREADSAFE build
 206 ** and only if the SQLITE_SERVER macro is defined.
 207 */
 208 #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
 209 #if SQLITE_OS_UNIX && SQLITE_THREADSAFE
 210
 211 /*
 212 ** We require only pthreads and the public interface of SQLite.
 213 */
 214 #include <pthread.h>
 215 #include "sqlite3.h"
 216
 217 /*
 218 ** Messages are passed from client to server and back again as
 219 ** instances of the following structure.
 220 */
 221 typedef struct SqlMessage SqlMessage;
 222 struct SqlMessage {
 223   int op;                      /* Opcode for the message */
 224   sqlite3 *pDb;                /* The SQLite connection */
 225   sqlite3_stmt *pStmt;         /* A specific statement */
 226   int errCode;                 /* Error code returned */
 227   const char *zIn;             /* Input filename or SQL statement */
 228   int nByte;                   /* Size of the zIn parameter for prepare() */
 229   const char *zOut;            /* Tail of the SQL statement */
 230   SqlMessage *pNext;           /* Next message in the queue */
 231   SqlMessage *pPrev;           /* Previous message in the queue */
 232   pthread_mutex_t clientMutex; /* Hold this mutex to access the message */
 233   pthread_cond_t clientWakeup; /* Signal to wake up the client */
 234 };
 235
 236 /*
 237 ** Legal values for SqlMessage.op
 238 */
 239 #define MSG_Open       1  /* sqlite3_open(zIn, &pDb) */
 240 #define MSG_Prepare    2  /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */
 241 #define MSG_Step       3  /* sqlite3_step(pStmt) */
 242 #define MSG_Reset      4  /* sqlite3_reset(pStmt) */
 243 #define MSG_Finalize   5  /* sqlite3_finalize(pStmt) */
 244 #define MSG_Close      6  /* sqlite3_close(pDb) */
 245 #define MSG_Done       7  /* Server has finished with this message */
 246
 247
 248 /*
 249 ** State information about the server is stored in a static variable
 250 ** named "g" as follows:
 251 */
 252 static struct ServerState {
 253   pthread_mutex_t queueMutex;   /* Hold this mutex to access the msg queue */
 254   pthread_mutex_t serverMutex;  /* Held by the server while it is running */
 255   pthread_cond_t serverWakeup;  /* Signal this condvar to wake up the server */
 256   volatile int serverHalt;      /* Server halts itself when true */
 257   SqlMessage *pQueueHead;       /* Head of the message queue */
 258   SqlMessage *pQueueTail;       /* Tail of the message queue */
 259 } g = {
 260   PTHREAD_MUTEX_INITIALIZER,
 261   PTHREAD_MUTEX_INITIALIZER,
 262   PTHREAD_COND_INITIALIZER,
 263 };
 264
 265 /*
 266 ** Send a message to the server.  Block until we get a reply.
 267 **
 268 ** The mutex and condition variable in the message are uninitialized
 269 ** when this routine is called.  This routine takes care of
 270 ** initializing them and destroying them when it has finished.
 271 */
 272 static void sendToServer(SqlMessage *pMsg){
 273   /* Initialize the mutex and condition variable on the message
 274   */
 275   pthread_mutex_init(&pMsg->clientMutex, 0);
 276   pthread_cond_init(&pMsg->clientWakeup, 0);
 277
 278   /* Add the message to the head of the server's message queue.
 279   */
 280   pthread_mutex_lock(&g.queueMutex);
 281   pMsg->pNext = g.pQueueHead;
 282   if( g.pQueueHead==0 ){
 283     g.pQueueTail = pMsg;
 284   }else{
 285     g.pQueueHead->pPrev = pMsg;
 286   }
 287   pMsg->pPrev = 0;
 288   g.pQueueHead = pMsg;
 289   pthread_mutex_unlock(&g.queueMutex);
 290
 291   /* Signal the server that the new message has be queued, then
 292   ** block waiting for the server to process the message.
 293   */
 294   pthread_mutex_lock(&pMsg->clientMutex);
 295   pthread_cond_signal(&g.serverWakeup);
 296   while( pMsg->op!=MSG_Done ){
 297     pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex);
 298   }
 299   pthread_mutex_unlock(&pMsg->clientMutex);
 300
 301   /* Destroy the mutex and condition variable of the message.
 302   */
 303   pthread_mutex_destroy(&pMsg->clientMutex);
 304   pthread_cond_destroy(&pMsg->clientWakeup);
 305 }
 306
 307 /*
 308 ** The following 6 routines are client-side implementations of the
 309 ** core SQLite interfaces:
 310 **
 311 **        sqlite3_open
 312 **        sqlite3_prepare
 313 **        sqlite3_step
 314 **        sqlite3_reset
 315 **        sqlite3_finalize
 316 **        sqlite3_close
 317 **
 318 ** Clients should use the following client-side routines instead of
 319 ** the core routines above.
 320 **
 321 **        sqlite3_client_open
 322 **        sqlite3_client_prepare
 323 **        sqlite3_client_step
 324 **        sqlite3_client_reset
 325 **        sqlite3_client_finalize
 326 **        sqlite3_client_close
 327 **
 328 ** Each of these routines creates a message for the desired operation,
 329 ** sends that message to the server, waits for the server to process
 330 ** then message and return a response.
 331 */
 332 int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){
 333   SqlMessage msg;
 334   msg.op = MSG_Open;
 335   msg.zIn = zDatabaseName;
 336   sendToServer(&msg);
 337   *ppDb = msg.pDb;
 338   return msg.errCode;
 339 }
 340 int sqlite3_client_prepare(
 341   sqlite3 *pDb,
 342   const char *zSql,
 343   int nByte,
 344   sqlite3_stmt **ppStmt,
 345   const char **pzTail
 346 ){
 347   SqlMessage msg;
 348   msg.op = MSG_Prepare;
 349   msg.pDb = pDb;
 350   msg.zIn = zSql;
 351   msg.nByte = nByte;
 352   sendToServer(&msg);
 353   *ppStmt = msg.pStmt;
 354   if( pzTail ) *pzTail = msg.zOut;
 355   return msg.errCode;
 356 }
 357 int sqlite3_client_step(sqlite3_stmt *pStmt){
 358   SqlMessage msg;
 359   msg.op = MSG_Step;
 360   msg.pStmt = pStmt;
 361   sendToServer(&msg);
 362   return msg.errCode;
 363 }
 364 int sqlite3_client_reset(sqlite3_stmt *pStmt){
 365   SqlMessage msg;
 366   msg.op = MSG_Reset;
 367   msg.pStmt = pStmt;
 368   sendToServer(&msg);
 369   return msg.errCode;
 370 }
 371 int sqlite3_client_finalize(sqlite3_stmt *pStmt){
 372   SqlMessage msg;
 373   msg.op = MSG_Finalize;
 374   msg.pStmt = pStmt;
 375   sendToServer(&msg);
 376   return msg.errCode;
 377 }
 378 int sqlite3_client_close(sqlite3 *pDb){
 379   SqlMessage msg;
 380   msg.op = MSG_Close;
 381   msg.pDb = pDb;
 382   sendToServer(&msg);
 383   return msg.errCode;
 384 }
 385
 386 /*
 387 ** This routine implements the server.  To start the server, first
 388 ** make sure g.serverHalt is false, then create a new detached thread
 389 ** on this procedure.  See the sqlite3_server_start() routine below
 390 ** for an example.  This procedure loops until g.serverHalt becomes
 391 ** true.
 392 */
 393 void *sqlite3_server(void *NotUsed){
 394   if( pthread_mutex_trylock(&g.serverMutex) ){
 395     return 0;  /* Another server is already running */
 396   }
 397   sqlite3_enable_shared_cache(1);
 398   while( !g.serverHalt ){
 399     SqlMessage *pMsg;
 400
 401     /* Remove the last message from the message queue.
 402     */
 403     pthread_mutex_lock(&g.queueMutex);
 404     while( g.pQueueTail==0 && g.serverHalt==0 ){
 405       pthread_cond_wait(&g.serverWakeup, &g.queueMutex);
 406     }
 407     pMsg = g.pQueueTail;
 408     if( pMsg ){
 409       if( pMsg->pPrev ){
 410         pMsg->pPrev->pNext = 0;
 411       }else{
 412         g.pQueueHead = 0;
 413       }
 414       g.pQueueTail = pMsg->pPrev;
 415     }
 416     pthread_mutex_unlock(&g.queueMutex);
 417     if( pMsg==0 ) break;
 418
 419     /* Process the message just removed
 420     */
 421     pthread_mutex_lock(&pMsg->clientMutex);
 422     switch( pMsg->op ){
 423       case MSG_Open: {
 424         pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb);
 425         break;
 426       }
 427       case MSG_Prepare: {
 428         pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,
 429                                         &pMsg->pStmt, &pMsg->zOut);
 430         break;
 431       }
 432       case MSG_Step: {
 433         pMsg->errCode = sqlite3_step(pMsg->pStmt);
 434         break;
 435       }
 436       case MSG_Reset: {
 437         pMsg->errCode = sqlite3_reset(pMsg->pStmt);
 438         break;
 439       }
 440       case MSG_Finalize: {
 441         pMsg->errCode = sqlite3_finalize(pMsg->pStmt);
 442         break;
 443       }
 444       case MSG_Close: {
 445         pMsg->errCode = sqlite3_close(pMsg->pDb);
 446         break;
 447       }
 448     }
 449
 450     /* Signal the client that the message has been processed.
 451     */
 452     pMsg->op = MSG_Done;
 453     pthread_mutex_unlock(&pMsg->clientMutex);
 454     pthread_cond_signal(&pMsg->clientWakeup);
 455   }
 456   pthread_mutex_unlock(&g.serverMutex);
 457   return 0;
 458 }
 459
 460 /*
 461 ** Start a server thread if one is not already running.  If there
 462 ** is aleady a server thread running, the new thread will quickly
 463 ** die and this routine is effectively a no-op.
 464 */
 465 void sqlite3_server_start(void){
 466   pthread_t x;
 467   int rc;
 468   g.serverHalt = 0;
 469   rc = pthread_create(&x, 0, sqlite3_server, 0);
 470   if( rc==0 ){
 471     pthread_detach(x);
 472   }
 473 }
 474
 475 /*
 476 ** A wrapper around sqlite3_server() that decrements the int variable
 477 ** pointed to by the first argument after the sqlite3_server() call
 478 ** returns.
 479 */
 480 static void *serverWrapper(void *pnDecr){
 481   void *p = sqlite3_server(0);
 482   (*(int*)pnDecr)--;
 483   return p;
 484 }
 485
 486 /*
 487 ** This function is the similar to sqlite3_server_start(), except that
 488 ** the integer pointed to by the first argument is decremented when
 489 ** the server thread exits.
 490 */
 491 void sqlite3_server_start2(int *pnDecr){
 492   pthread_t x;
 493   int rc;
 494   g.serverHalt = 0;
 495   rc = pthread_create(&x, 0, serverWrapper, (void*)pnDecr);
 496   if( rc==0 ){
 497     pthread_detach(x);
 498   }
 499 }
 500
 501 /*
 502 ** If a server thread is running, then stop it.  If no server is
 503 ** running, this routine is effectively a no-op.
 504 **
 505 ** This routine waits until the server has actually stopped before
 506 ** returning.
 507 */
 508 void sqlite3_server_stop(void){
 509   g.serverHalt = 1;
 510   pthread_cond_broadcast(&g.serverWakeup);
 511   pthread_mutex_lock(&g.serverMutex);
 512   pthread_mutex_unlock(&g.serverMutex);
 513 }
 514
 515 #endif /* SQLITE_OS_UNIX && SQLITE_THREADSAFE */
 516 #endif /* defined(SQLITE_SERVER) */