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*/ 221typedef struct SqlMessage SqlMessage; 222struct 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*/ 252static 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*/ 272static 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*/ 332int 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} 340int 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} 357int 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} 364int 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} 371int 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} 378int 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*/ 393void *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*/ 465void 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** If a server thread is running, then stop it. If no server is 477** running, this routine is effectively a no-op. 478** 479** This routine waits until the server has actually stopped before 480** returning. 481*/ 482void sqlite3_server_stop(void){ 483 g.serverHalt = 1; 484 pthread_cond_broadcast(&g.serverWakeup); 485 pthread_mutex_lock(&g.serverMutex); 486 pthread_mutex_unlock(&g.serverMutex); 487} 488 489#endif /* SQLITE_OS_UNIX && SQLITE_THREADSAFE */ 490#endif /* defined(SQLITE_SERVER) */ 491