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 ** $Id: test_server.c,v 1.8 2008/06/26 10:41:19 danielk1977 Exp $

    14 **

    15 ** This file contains demonstration code.  Nothing in this file gets compiled

    16 ** or linked into the SQLite library unless you use a non-standard option:

    17 **

    18 **      -DSQLITE_SERVER=1

    19 **

    20 ** The configure script will never generate a Makefile with the option

    21 ** above.  You will need to manually modify the Makefile if you want to

    22 ** include any of the code from this file in your project.  Or, at your

    23 ** option, you may copy and paste the code from this file and

    24 ** thereby avoiding a recompile of SQLite.

    25 **

    26 **

    27 ** This source file demonstrates how to use SQLite to create an SQL database 

    28 ** server thread in a multiple-threaded program.  One or more client threads

    29 ** send messages to the server thread and the server thread processes those

    30 ** messages in the order received and returns the results to the client.

    31 **

    32 ** One might ask:  "Why bother?  Why not just let each thread connect

    33 ** to the database directly?"  There are a several of reasons to

    34 ** prefer the client/server approach.

    35 **

    36 **    (1)  Some systems (ex: Redhat9) have broken threading implementations

    37 **         that prevent SQLite database connections from being used in

    38 **         a thread different from the one where they were created.  With

    39 **         the client/server approach, all database connections are created

    40 **         and used within the server thread.  Client calls to the database

    41 **         can be made from multiple threads (though not at the same time!)

    42 **

    43 **    (2)  Beginning with SQLite version 3.3.0, when two or more 

    44 **         connections to the same database occur within the same thread,

    45 **         they can optionally share their database cache.  This reduces

    46 **         I/O and memory requirements.  Cache shared is controlled using

    47 **         the sqlite3_enable_shared_cache() API.

    48 **

    49 **    (3)  Database connections on a shared cache use table-level locking

    50 **         instead of file-level locking for improved concurrency.

    51 **

    52 **    (4)  Database connections on a shared cache can by optionally

    53 **         set to READ UNCOMMITTED isolation.  (The default isolation for

    54 **         SQLite is SERIALIZABLE.)  When this occurs, readers will

    55 **         never be blocked by a writer and writers will not be

    56 **         blocked by readers.  There can still only be a single writer

    57 **         at a time, but multiple readers can simultaneously exist with

    58 **         that writer.  This is a huge increase in concurrency.

    59 **

    60 ** To summarize the rational for using a client/server approach: prior

    61 ** to SQLite version 3.3.0 it probably was not worth the trouble.  But

    62 ** with SQLite version 3.3.0 and beyond you can get significant performance

    63 ** and concurrency improvements and memory usage reductions by going

    64 ** client/server.

    65 **

    66 ** Note:  The extra features of version 3.3.0 described by points (2)

    67 ** through (4) above are only available if you compile without the

    68 ** option -DSQLITE_OMIT_SHARED_CACHE. 

    69 **

    70 ** Here is how the client/server approach works:  The database server

    71 ** thread is started on this procedure:

    72 **

    73 **       void *sqlite3_server(void *NotUsed);

    74 **

    75 ** The sqlite_server procedure runs as long as the g.serverHalt variable

    76 ** is false.  A mutex is used to make sure no more than one server runs

    77 ** at a time.  The server waits for messages to arrive on a message

    78 ** queue and processes the messages in order.

    79 **

    80 ** Two convenience routines are provided for starting and stopping the

    81 ** server thread:

    82 **

    83 **       void sqlite3_server_start(void);

    84 **       void sqlite3_server_stop(void);

    85 **

    86 ** Both of the convenience routines return immediately.  Neither will

    87 ** ever give an error.  If a server is already started or already halted,

    88 ** then the routines are effectively no-ops.

    89 **

    90 ** Clients use the following interfaces:

    91 **

    92 **       sqlite3_client_open

    93 **       sqlite3_client_prepare

    94 **       sqlite3_client_step

    95 **       sqlite3_client_reset

    96 **       sqlite3_client_finalize

    97 **       sqlite3_client_close

    98 **

    99 ** These interfaces work exactly like the standard core SQLite interfaces

   100 ** having the same names without the "_client_" infix.  Many other SQLite

   101 ** interfaces can be used directly without having to send messages to the

   102 ** server as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.

   103 ** The following interfaces fall into this second category:

   104 **

   105 **       sqlite3_bind_*

   106 **       sqlite3_changes

   107 **       sqlite3_clear_bindings

   108 **       sqlite3_column_*

   109 **       sqlite3_complete

   110 **       sqlite3_create_collation

   111 **       sqlite3_create_function

   112 **       sqlite3_data_count

   113 **       sqlite3_db_handle

   114 **       sqlite3_errcode

   115 **       sqlite3_errmsg

   116 **       sqlite3_last_insert_rowid

   117 **       sqlite3_total_changes

   118 **       sqlite3_transfer_bindings

   119 **

   120 ** A single SQLite connection (an sqlite3* object) or an SQLite statement

   121 ** (an sqlite3_stmt* object) should only be passed to a single interface

   122 ** function at a time.  The connections and statements can be passed from

   123 ** any thread to any of the functions listed in the second group above as

   124 ** long as the same connection is not in use by two threads at once and

   125 ** as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.  Additional

   126 ** information about the SQLITE_ENABLE_MEMORY_MANAGEMENT constraint is

   127 ** below.

   128 **

   129 ** The busy handler for all database connections should remain turned

   130 ** off.  That means that any lock contention will cause the associated

   131 ** sqlite3_client_step() call to return immediately with an SQLITE_BUSY

   132 ** error code.  If a busy handler is enabled and lock contention occurs,

   133 ** then the entire server thread will block.  This will cause not only

   134 ** the requesting client to block but every other database client as

   135 ** well.  It is possible to enhance the code below so that lock

   136 ** contention will cause the message to be placed back on the top of

   137 ** the queue to be tried again later.  But such enhanced processing is

   138 ** not included here, in order to keep the example simple.

   139 **

   140 ** This example code assumes the use of pthreads.  Pthreads

   141 ** implementations are available for windows.  (See, for example

   142 ** http://sourceware.org/pthreads-win32/announcement.html.)  Or, you

   143 ** can translate the locking and thread synchronization code to use

   144 ** windows primitives easily enough.  The details are left as an

   145 ** exercise to the reader.

   146 **

   147 **** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****

   148 **

   149 ** If you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT defined, then

   150 ** SQLite includes code that tracks how much memory is being used by

   151 ** each thread.  These memory counts can become confused if memory

   152 ** is allocated by one thread and then freed by another.  For that

   153 ** reason, when SQLITE_ENABLE_MEMORY_MANAGEMENT is used, all operations

   154 ** that might allocate or free memory should be performanced in the same

   155 ** thread that originally created the database connection.  In that case,

   156 ** many of the operations that are listed above as safe to be performed

   157 ** in separate threads would need to be sent over to the server to be

   158 ** done there.  If SQLITE_ENABLE_MEMORY_MANAGEMENT is defined, then

   159 ** the following functions can be used safely from different threads

   160 ** without messing up the allocation counts:

   161 **

   162 **       sqlite3_bind_parameter_name

   163 **       sqlite3_bind_parameter_index

   164 **       sqlite3_changes

   165 **       sqlite3_column_blob

   166 **       sqlite3_column_count

   167 **       sqlite3_complete

   168 **       sqlite3_data_count

   169 **       sqlite3_db_handle

   170 **       sqlite3_errcode

   171 **       sqlite3_errmsg

   172 **       sqlite3_last_insert_rowid

   173 **       sqlite3_total_changes

   174 **

   175 ** The remaining functions are not thread-safe when memory management

   176 ** is enabled.  So one would have to define some new interface routines

   177 ** along the following lines:

   178 **

   179 **       sqlite3_client_bind_*

   180 **       sqlite3_client_clear_bindings

   181 **       sqlite3_client_column_*

   182 **       sqlite3_client_create_collation

   183 **       sqlite3_client_create_function

   184 **       sqlite3_client_transfer_bindings

   185 **

   186 ** The example code in this file is intended for use with memory

   187 ** management turned off.  So the implementation of these additional

   188 ** client interfaces is left as an exercise to the reader.

   189 **

   190 ** It may seem surprising to the reader that the list of safe functions

   191 ** above does not include things like sqlite3_bind_int() or

   192 ** sqlite3_column_int().  But those routines might, in fact, allocate

   193 ** or deallocate memory.  In the case of sqlite3_bind_int(), if the

   194 ** parameter was previously bound to a string that string might need

   195 ** to be deallocated before the new integer value is inserted.  In

   196 ** the case of sqlite3_column_int(), the value of the column might be

   197 ** a UTF-16 string which will need to be converted to UTF-8 then into

   198 ** an integer.

   199 */

   200 

   201 /* Include this to get the definition of SQLITE_THREADSAFE, in the

   202 ** case that default values are used.

   203 */

   204 #include "sqliteInt.h"

   205 

   206 /*

   207 ** Only compile the code in this file on UNIX with a SQLITE_THREADSAFE build

   208 ** and only if the SQLITE_SERVER macro is defined.

   209 */

   210 #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)

   211 #if defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE

   212 

   213 /*

   214 ** We require only pthreads and the public interface of SQLite.

   215 */

   216 #include <pthread.h>

   217 #include "sqlite3.h"

   218 

   219 /*

   220 ** Messages are passed from client to server and back again as 

   221 ** instances of the following structure.

   222 */

   223 typedef struct SqlMessage SqlMessage;

   224 struct SqlMessage {

   225   int op;                      /* Opcode for the message */

   226   sqlite3 *pDb;                /* The SQLite connection */

   227   sqlite3_stmt *pStmt;         /* A specific statement */

   228   int errCode;                 /* Error code returned */

   229   const char *zIn;             /* Input filename or SQL statement */

   230   int nByte;                   /* Size of the zIn parameter for prepare() */

   231   const char *zOut;            /* Tail of the SQL statement */

   232   SqlMessage *pNext;           /* Next message in the queue */

   233   SqlMessage *pPrev;           /* Previous message in the queue */

   234   pthread_mutex_t clientMutex; /* Hold this mutex to access the message */

   235   pthread_cond_t clientWakeup; /* Signal to wake up the client */

   236 };

   237 

   238 /*

   239 ** Legal values for SqlMessage.op

   240 */

   241 #define MSG_Open       1  /* sqlite3_open(zIn, &pDb) */

   242 #define MSG_Prepare    2  /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */

   243 #define MSG_Step       3  /* sqlite3_step(pStmt) */

   244 #define MSG_Reset      4  /* sqlite3_reset(pStmt) */

   245 #define MSG_Finalize   5  /* sqlite3_finalize(pStmt) */

   246 #define MSG_Close      6  /* sqlite3_close(pDb) */

   247 #define MSG_Done       7  /* Server has finished with this message */

   248 

   249 

   250 /*

   251 ** State information about the server is stored in a static variable

   252 ** named "g" as follows:

   253 */

   254 static struct ServerState {

   255   pthread_mutex_t queueMutex;   /* Hold this mutex to access the msg queue */

   256   pthread_mutex_t serverMutex;  /* Held by the server while it is running */

   257   pthread_cond_t serverWakeup;  /* Signal this condvar to wake up the server */

   258   volatile int serverHalt;      /* Server halts itself when true */

   259   SqlMessage *pQueueHead;       /* Head of the message queue */

   260   SqlMessage *pQueueTail;       /* Tail of the message queue */

   261 } g = {

   262   PTHREAD_MUTEX_INITIALIZER,

   263   PTHREAD_MUTEX_INITIALIZER,

   264   PTHREAD_COND_INITIALIZER,

   265 };

   266 

   267 /*

   268 ** Send a message to the server.  Block until we get a reply.

   269 **

   270 ** The mutex and condition variable in the message are uninitialized

   271 ** when this routine is called.  This routine takes care of 

   272 ** initializing them and destroying them when it has finished.

   273 */

   274 static void sendToServer(SqlMessage *pMsg){

   275   /* Initialize the mutex and condition variable on the message

   276   */

   277   pthread_mutex_init(&pMsg->clientMutex, 0);

   278   pthread_cond_init(&pMsg->clientWakeup, 0);

   279 

   280   /* Add the message to the head of the server's message queue.

   281   */

   282   pthread_mutex_lock(&g.queueMutex);

   283   pMsg->pNext = g.pQueueHead;

   284   if( g.pQueueHead==0 ){

   285     g.pQueueTail = pMsg;

   286   }else{

   287     g.pQueueHead->pPrev = pMsg;

   288   }

   289   pMsg->pPrev = 0;

   290   g.pQueueHead = pMsg;

   291   pthread_mutex_unlock(&g.queueMutex);

   292 

   293   /* Signal the server that the new message has be queued, then

   294   ** block waiting for the server to process the message.

   295   */

   296   pthread_mutex_lock(&pMsg->clientMutex);

   297   pthread_cond_signal(&g.serverWakeup);

   298   while( pMsg->op!=MSG_Done ){

   299     pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex);

   300   }

   301   pthread_mutex_unlock(&pMsg->clientMutex);

   302 

   303   /* Destroy the mutex and condition variable of the message.

   304   */

   305   pthread_mutex_destroy(&pMsg->clientMutex);

   306   pthread_cond_destroy(&pMsg->clientWakeup);

   307 }

   308 

   309 /*

   310 ** The following 6 routines are client-side implementations of the

   311 ** core SQLite interfaces:

   312 **

   313 **        sqlite3_open

   314 **        sqlite3_prepare

   315 **        sqlite3_step

   316 **        sqlite3_reset

   317 **        sqlite3_finalize

   318 **        sqlite3_close

   319 **

   320 ** Clients should use the following client-side routines instead of 

   321 ** the core routines above.

   322 **

   323 **        sqlite3_client_open

   324 **        sqlite3_client_prepare

   325 **        sqlite3_client_step

   326 **        sqlite3_client_reset

   327 **        sqlite3_client_finalize

   328 **        sqlite3_client_close

   329 **

   330 ** Each of these routines creates a message for the desired operation,

   331 ** sends that message to the server, waits for the server to process

   332 ** then message and return a response.

   333 */

   334 int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){

   335   SqlMessage msg;

   336   msg.op = MSG_Open;

   337   msg.zIn = zDatabaseName;

   338   sendToServer(&msg);

   339   *ppDb = msg.pDb;

   340   return msg.errCode;

   341 }

   342 int sqlite3_client_prepare(

   343   sqlite3 *pDb,

   344   const char *zSql,

   345   int nByte,

   346   sqlite3_stmt **ppStmt,

   347   const char **pzTail

   348 ){

   349   SqlMessage msg;

   350   msg.op = MSG_Prepare;

   351   msg.pDb = pDb;

   352   msg.zIn = zSql;

   353   msg.nByte = nByte;

   354   sendToServer(&msg);

   355   *ppStmt = msg.pStmt;

   356   if( pzTail ) *pzTail = msg.zOut;

   357   return msg.errCode;

   358 }

   359 int sqlite3_client_step(sqlite3_stmt *pStmt){

   360   SqlMessage msg;

   361   msg.op = MSG_Step;

   362   msg.pStmt = pStmt;

   363   sendToServer(&msg);

   364   return msg.errCode;

   365 }

   366 int sqlite3_client_reset(sqlite3_stmt *pStmt){

   367   SqlMessage msg;

   368   msg.op = MSG_Reset;

   369   msg.pStmt = pStmt;

   370   sendToServer(&msg);

   371   return msg.errCode;

   372 }

   373 int sqlite3_client_finalize(sqlite3_stmt *pStmt){

   374   SqlMessage msg;

   375   msg.op = MSG_Finalize;

   376   msg.pStmt = pStmt;

   377   sendToServer(&msg);

   378   return msg.errCode;

   379 }

   380 int sqlite3_client_close(sqlite3 *pDb){

   381   SqlMessage msg;

   382   msg.op = MSG_Close;

   383   msg.pDb = pDb;

   384   sendToServer(&msg);

   385   return msg.errCode;

   386 }

   387 

   388 /*

   389 ** This routine implements the server.  To start the server, first

   390 ** make sure g.serverHalt is false, then create a new detached thread

   391 ** on this procedure.  See the sqlite3_server_start() routine below

   392 ** for an example.  This procedure loops until g.serverHalt becomes

   393 ** true.

   394 */

   395 void *sqlite3_server(void *NotUsed){

   396   if( pthread_mutex_trylock(&g.serverMutex) ){

   397     return 0;  /* Another server is already running */

   398   }

   399   sqlite3_enable_shared_cache(1);

   400   while( !g.serverHalt ){

   401     SqlMessage *pMsg;

   402 

   403     /* Remove the last message from the message queue.

   404     */

   405     pthread_mutex_lock(&g.queueMutex);

   406     while( g.pQueueTail==0 && g.serverHalt==0 ){

   407       pthread_cond_wait(&g.serverWakeup, &g.queueMutex);

   408     }

   409     pMsg = g.pQueueTail;

   410     if( pMsg ){

   411       if( pMsg->pPrev ){

   412         pMsg->pPrev->pNext = 0;

   413       }else{

   414         g.pQueueHead = 0;

   415       }

   416       g.pQueueTail = pMsg->pPrev;

   417     }

   418     pthread_mutex_unlock(&g.queueMutex);

   419     if( pMsg==0 ) break;

   420 

   421     /* Process the message just removed

   422     */

   423     pthread_mutex_lock(&pMsg->clientMutex);

   424     switch( pMsg->op ){

   425       case MSG_Open: {

   426         pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb);

   427         break;

   428       }

   429       case MSG_Prepare: {

   430         pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,

   431                                         &pMsg->pStmt, &pMsg->zOut);

   432         break;

   433       }

   434       case MSG_Step: {

   435         pMsg->errCode = sqlite3_step(pMsg->pStmt);

   436         break;

   437       }

   438       case MSG_Reset: {

   439         pMsg->errCode = sqlite3_reset(pMsg->pStmt);

   440         break;

   441       }

   442       case MSG_Finalize: {

   443         pMsg->errCode = sqlite3_finalize(pMsg->pStmt);

   444         break;

   445       }

   446       case MSG_Close: {

   447         pMsg->errCode = sqlite3_close(pMsg->pDb);

   448         break;

   449       }

   450     }

   451 

   452     /* Signal the client that the message has been processed.

   453     */

   454     pMsg->op = MSG_Done;

   455     pthread_mutex_unlock(&pMsg->clientMutex);

   456     pthread_cond_signal(&pMsg->clientWakeup);

   457   }

   458   sqlite3_thread_cleanup();

   459   pthread_mutex_unlock(&g.serverMutex);

   460   return 0;

   461 }

   462 

   463 /*

   464 ** Start a server thread if one is not already running.  If there

   465 ** is aleady a server thread running, the new thread will quickly

   466 ** die and this routine is effectively a no-op.

   467 */

   468 void sqlite3_server_start(void){

   469   pthread_t x;

   470   int rc;

   471   g.serverHalt = 0;

   472   rc = pthread_create(&x, 0, sqlite3_server, 0);

   473   if( rc==0 ){

   474     pthread_detach(x);

   475   }

   476 }

   477 

   478 /*

   479 ** If a server thread is running, then stop it.  If no server is

   480 ** running, this routine is effectively a no-op.

   481 **

   482 ** This routine waits until the server has actually stopped before

   483 ** returning.

   484 */

   485 void sqlite3_server_stop(void){

   486   g.serverHalt = 1;

   487   pthread_cond_broadcast(&g.serverWakeup);

   488   pthread_mutex_lock(&g.serverMutex);

   489   pthread_mutex_unlock(&g.serverMutex);

   490 }

   491 

   492 #endif /* defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE */

   493 #endif /* defined(SQLITE_SERVER) */