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) */ 494