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 /* 200 ** Only compile the code in this file on UNIX with a THREADSAFE build 201 ** and only if the SQLITE_SERVER macro is defined. 202 */ 203 #ifdef SQLITE_SERVER 204 #if defined(OS_UNIX) && OS_UNIX && defined(THREADSAFE) && THREADSAFE 205 206 /* 207 ** We require only pthreads and the public interface of SQLite. 208 */ 209 #include <pthread.h> 210 #include "sqlite3.h" 211 212 /* 213 ** Messages are passed from client to server and back again as 214 ** instances of the following structure. 215 */ 216 typedef struct SqlMessage SqlMessage; 217 struct SqlMessage { 218 int op; /* Opcode for the message */ 219 sqlite3 *pDb; /* The SQLite connection */ 220 sqlite3_stmt *pStmt; /* A specific statement */ 221 int errCode; /* Error code returned */ 222 const char *zIn; /* Input filename or SQL statement */ 223 int nByte; /* Size of the zIn parameter for prepare() */ 224 const char *zOut; /* Tail of the SQL statement */ 225 SqlMessage *pNext; /* Next message in the queue */ 226 SqlMessage *pPrev; /* Previous message in the queue */ 227 pthread_mutex_t clientMutex; /* Hold this mutex to access the message */ 228 pthread_cond_t clientWakeup; /* Signal to wake up the client */ 229 }; 230 231 /* 232 ** Legal values for SqlMessage.op 233 */ 234 #define MSG_Open 1 /* sqlite3_open(zIn, &pDb) */ 235 #define MSG_Prepare 2 /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */ 236 #define MSG_Step 3 /* sqlite3_step(pStmt) */ 237 #define MSG_Reset 4 /* sqlite3_reset(pStmt) */ 238 #define MSG_Finalize 5 /* sqlite3_finalize(pStmt) */ 239 #define MSG_Close 6 /* sqlite3_close(pDb) */ 240 #define MSG_Done 7 /* Server has finished with this message */ 241 242 243 /* 244 ** State information about the server is stored in a static variable 245 ** named "g" as follows: 246 */ 247 static struct ServerState { 248 pthread_mutex_t queueMutex; /* Hold this mutex to access the msg queue */ 249 pthread_mutex_t serverMutex; /* Held by the server while it is running */ 250 pthread_cond_t serverWakeup; /* Signal this condvar to wake up the server */ 251 volatile int serverHalt; /* Server halts itself when true */ 252 SqlMessage *pQueueHead; /* Head of the message queue */ 253 SqlMessage *pQueueTail; /* Tail of the message queue */ 254 } g = { 255 PTHREAD_MUTEX_INITIALIZER, 256 PTHREAD_MUTEX_INITIALIZER, 257 PTHREAD_COND_INITIALIZER, 258 }; 259 260 /* 261 ** Send a message to the server. Block until we get a reply. 262 ** 263 ** The mutex and condition variable in the message are uninitialized 264 ** when this routine is called. This routine takes care of 265 ** initializing them and destroying them when it has finished. 266 */ 267 static void sendToServer(SqlMessage *pMsg){ 268 /* Initialize the mutex and condition variable on the message 269 */ 270 pthread_mutex_init(&pMsg->clientMutex, 0); 271 pthread_cond_init(&pMsg->clientWakeup, 0); 272 273 /* Add the message to the head of the server's message queue. 274 */ 275 pthread_mutex_lock(&g.queueMutex); 276 pMsg->pNext = g.pQueueHead; 277 if( g.pQueueHead==0 ){ 278 g.pQueueTail = pMsg; 279 }else{ 280 g.pQueueHead->pPrev = pMsg; 281 } 282 pMsg->pPrev = 0; 283 g.pQueueHead = pMsg; 284 pthread_mutex_unlock(&g.queueMutex); 285 286 /* Signal the server that the new message has be queued, then 287 ** block waiting for the server to process the message. 288 */ 289 pthread_mutex_lock(&pMsg->clientMutex); 290 pthread_cond_signal(&g.serverWakeup); 291 while( pMsg->op!=MSG_Done ){ 292 pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex); 293 } 294 pthread_mutex_unlock(&pMsg->clientMutex); 295 296 /* Destroy the mutex and condition variable of the message. 297 */ 298 pthread_mutex_destroy(&pMsg->clientMutex); 299 pthread_cond_destroy(&pMsg->clientWakeup); 300 } 301 302 /* 303 ** The following 6 routines are client-side implementations of the 304 ** core SQLite interfaces: 305 ** 306 ** sqlite3_open 307 ** sqlite3_prepare 308 ** sqlite3_step 309 ** sqlite3_reset 310 ** sqlite3_finalize 311 ** sqlite3_close 312 ** 313 ** Clients should use the following client-side routines instead of 314 ** the core routines above. 315 ** 316 ** sqlite3_client_open 317 ** sqlite3_client_prepare 318 ** sqlite3_client_step 319 ** sqlite3_client_reset 320 ** sqlite3_client_finalize 321 ** sqlite3_client_close 322 ** 323 ** Each of these routines creates a message for the desired operation, 324 ** sends that message to the server, waits for the server to process 325 ** then message and return a response. 326 */ 327 int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){ 328 SqlMessage msg; 329 msg.op = MSG_Open; 330 msg.zIn = zDatabaseName; 331 sendToServer(&msg); 332 *ppDb = msg.pDb; 333 return msg.errCode; 334 } 335 int sqlite3_client_prepare( 336 sqlite3 *pDb, 337 const char *zSql, 338 int nByte, 339 sqlite3_stmt **ppStmt, 340 const char **pzTail 341 ){ 342 SqlMessage msg; 343 msg.op = MSG_Prepare; 344 msg.pDb = pDb; 345 msg.zIn = zSql; 346 msg.nByte = nByte; 347 sendToServer(&msg); 348 *ppStmt = msg.pStmt; 349 if( pzTail ) *pzTail = msg.zOut; 350 return msg.errCode; 351 } 352 int sqlite3_client_step(sqlite3_stmt *pStmt){ 353 SqlMessage msg; 354 msg.op = MSG_Step; 355 msg.pStmt = pStmt; 356 sendToServer(&msg); 357 return msg.errCode; 358 } 359 int sqlite3_client_reset(sqlite3_stmt *pStmt){ 360 SqlMessage msg; 361 msg.op = MSG_Reset; 362 msg.pStmt = pStmt; 363 sendToServer(&msg); 364 return msg.errCode; 365 } 366 int sqlite3_client_finalize(sqlite3_stmt *pStmt){ 367 SqlMessage msg; 368 msg.op = MSG_Finalize; 369 msg.pStmt = pStmt; 370 sendToServer(&msg); 371 return msg.errCode; 372 } 373 int sqlite3_client_close(sqlite3 *pDb){ 374 SqlMessage msg; 375 msg.op = MSG_Close; 376 msg.pDb = pDb; 377 sendToServer(&msg); 378 return msg.errCode; 379 } 380 381 /* 382 ** This routine implements the server. To start the server, first 383 ** make sure g.serverHalt is false, then create a new detached thread 384 ** on this procedure. See the sqlite3_server_start() routine below 385 ** for an example. This procedure loops until g.serverHalt becomes 386 ** true. 387 */ 388 void *sqlite3_server(void *NotUsed){ 389 sqlite3_enable_shared_cache(1); 390 if( pthread_mutex_trylock(&g.serverMutex) ){ 391 sqlite3_enable_shared_cache(0); 392 return 0; /* Another server is already running */ 393 } 394 while( !g.serverHalt ){ 395 SqlMessage *pMsg; 396 397 /* Remove the last message from the message queue. 398 */ 399 pthread_mutex_lock(&g.queueMutex); 400 while( g.pQueueTail==0 && g.serverHalt==0 ){ 401 pthread_cond_wait(&g.serverWakeup, &g.queueMutex); 402 } 403 pMsg = g.pQueueTail; 404 if( pMsg ){ 405 if( pMsg->pPrev ){ 406 pMsg->pPrev->pNext = 0; 407 }else{ 408 g.pQueueHead = 0; 409 } 410 g.pQueueTail = pMsg->pPrev; 411 } 412 pthread_mutex_unlock(&g.queueMutex); 413 if( pMsg==0 ) break; 414 415 /* Process the message just removed 416 */ 417 pthread_mutex_lock(&pMsg->clientMutex); 418 switch( pMsg->op ){ 419 case MSG_Open: { 420 pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb); 421 break; 422 } 423 case MSG_Prepare: { 424 pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte, 425 &pMsg->pStmt, &pMsg->zOut); 426 break; 427 } 428 case MSG_Step: { 429 pMsg->errCode = sqlite3_step(pMsg->pStmt); 430 break; 431 } 432 case MSG_Reset: { 433 pMsg->errCode = sqlite3_reset(pMsg->pStmt); 434 break; 435 } 436 case MSG_Finalize: { 437 pMsg->errCode = sqlite3_finalize(pMsg->pStmt); 438 break; 439 } 440 case MSG_Close: { 441 pMsg->errCode = sqlite3_close(pMsg->pDb); 442 break; 443 } 444 } 445 446 /* Signal the client that the message has been processed. 447 */ 448 pMsg->op = MSG_Done; 449 pthread_mutex_unlock(&pMsg->clientMutex); 450 pthread_cond_signal(&pMsg->clientWakeup); 451 } 452 pthread_mutex_unlock(&g.serverMutex); 453 sqlite3_thread_cleanup(); 454 return 0; 455 } 456 457 /* 458 ** Start a server thread if one is not already running. If there 459 ** is aleady a server thread running, the new thread will quickly 460 ** die and this routine is effectively a no-op. 461 */ 462 void sqlite3_server_start(void){ 463 pthread_t x; 464 int rc; 465 g.serverHalt = 0; 466 rc = pthread_create(&x, 0, sqlite3_server, 0); 467 if( rc==0 ){ 468 pthread_detach(x); 469 } 470 } 471 472 /* 473 ** If a server thread is running, then stop it. If no server is 474 ** running, this routine is effectively a no-op. 475 ** 476 ** This routine returns immediately without waiting for the server 477 ** thread to stop. But be assured that the server will eventually stop. 478 */ 479 void sqlite3_server_stop(void){ 480 g.serverHalt = 1; 481 pthread_cond_broadcast(&g.serverWakeup); 482 } 483 484 #endif /* defined(OS_UNIX) && OS_UNIX && defined(THREADSAFE) && THREADSAFE */ 485 #endif /* defined(SQLITE_SERVER) */ 486