| OLD | NEW |
| (Empty) |
| 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) */ | |
| OLD | NEW |