1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/SRC/test_server.c Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,493 @@
1.4 +/*
1.5 +** 2006 January 07
1.6 +**
1.7 +** The author disclaims copyright to this source code. In place of
1.8 +** a legal notice, here is a blessing:
1.9 +**
1.10 +** May you do good and not evil.
1.11 +** May you find forgiveness for yourself and forgive others.
1.12 +** May you share freely, never taking more than you give.
1.13 +**
1.14 +******************************************************************************
1.15 +**
1.16 +** $Id: test_server.c,v 1.8 2008/06/26 10:41:19 danielk1977 Exp $
1.17 +**
1.18 +** This file contains demonstration code. Nothing in this file gets compiled
1.19 +** or linked into the SQLite library unless you use a non-standard option:
1.20 +**
1.21 +** -DSQLITE_SERVER=1
1.22 +**
1.23 +** The configure script will never generate a Makefile with the option
1.24 +** above. You will need to manually modify the Makefile if you want to
1.25 +** include any of the code from this file in your project. Or, at your
1.26 +** option, you may copy and paste the code from this file and
1.27 +** thereby avoiding a recompile of SQLite.
1.28 +**
1.29 +**
1.30 +** This source file demonstrates how to use SQLite to create an SQL database
1.31 +** server thread in a multiple-threaded program. One or more client threads
1.32 +** send messages to the server thread and the server thread processes those
1.33 +** messages in the order received and returns the results to the client.
1.34 +**
1.35 +** One might ask: "Why bother? Why not just let each thread connect
1.36 +** to the database directly?" There are a several of reasons to
1.37 +** prefer the client/server approach.
1.38 +**
1.39 +** (1) Some systems (ex: Redhat9) have broken threading implementations
1.40 +** that prevent SQLite database connections from being used in
1.41 +** a thread different from the one where they were created. With
1.42 +** the client/server approach, all database connections are created
1.43 +** and used within the server thread. Client calls to the database
1.44 +** can be made from multiple threads (though not at the same time!)
1.45 +**
1.46 +** (2) Beginning with SQLite version 3.3.0, when two or more
1.47 +** connections to the same database occur within the same thread,
1.48 +** they can optionally share their database cache. This reduces
1.49 +** I/O and memory requirements. Cache shared is controlled using
1.50 +** the sqlite3_enable_shared_cache() API.
1.51 +**
1.52 +** (3) Database connections on a shared cache use table-level locking
1.53 +** instead of file-level locking for improved concurrency.
1.54 +**
1.55 +** (4) Database connections on a shared cache can by optionally
1.56 +** set to READ UNCOMMITTED isolation. (The default isolation for
1.57 +** SQLite is SERIALIZABLE.) When this occurs, readers will
1.58 +** never be blocked by a writer and writers will not be
1.59 +** blocked by readers. There can still only be a single writer
1.60 +** at a time, but multiple readers can simultaneously exist with
1.61 +** that writer. This is a huge increase in concurrency.
1.62 +**
1.63 +** To summarize the rational for using a client/server approach: prior
1.64 +** to SQLite version 3.3.0 it probably was not worth the trouble. But
1.65 +** with SQLite version 3.3.0 and beyond you can get significant performance
1.66 +** and concurrency improvements and memory usage reductions by going
1.67 +** client/server.
1.68 +**
1.69 +** Note: The extra features of version 3.3.0 described by points (2)
1.70 +** through (4) above are only available if you compile without the
1.71 +** option -DSQLITE_OMIT_SHARED_CACHE.
1.72 +**
1.73 +** Here is how the client/server approach works: The database server
1.74 +** thread is started on this procedure:
1.75 +**
1.76 +** void *sqlite3_server(void *NotUsed);
1.77 +**
1.78 +** The sqlite_server procedure runs as long as the g.serverHalt variable
1.79 +** is false. A mutex is used to make sure no more than one server runs
1.80 +** at a time. The server waits for messages to arrive on a message
1.81 +** queue and processes the messages in order.
1.82 +**
1.83 +** Two convenience routines are provided for starting and stopping the
1.84 +** server thread:
1.85 +**
1.86 +** void sqlite3_server_start(void);
1.87 +** void sqlite3_server_stop(void);
1.88 +**
1.89 +** Both of the convenience routines return immediately. Neither will
1.90 +** ever give an error. If a server is already started or already halted,
1.91 +** then the routines are effectively no-ops.
1.92 +**
1.93 +** Clients use the following interfaces:
1.94 +**
1.95 +** sqlite3_client_open
1.96 +** sqlite3_client_prepare
1.97 +** sqlite3_client_step
1.98 +** sqlite3_client_reset
1.99 +** sqlite3_client_finalize
1.100 +** sqlite3_client_close
1.101 +**
1.102 +** These interfaces work exactly like the standard core SQLite interfaces
1.103 +** having the same names without the "_client_" infix. Many other SQLite
1.104 +** interfaces can be used directly without having to send messages to the
1.105 +** server as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined.
1.106 +** The following interfaces fall into this second category:
1.107 +**
1.108 +** sqlite3_bind_*
1.109 +** sqlite3_changes
1.110 +** sqlite3_clear_bindings
1.111 +** sqlite3_column_*
1.112 +** sqlite3_complete
1.113 +** sqlite3_create_collation
1.114 +** sqlite3_create_function
1.115 +** sqlite3_data_count
1.116 +** sqlite3_db_handle
1.117 +** sqlite3_errcode
1.118 +** sqlite3_errmsg
1.119 +** sqlite3_last_insert_rowid
1.120 +** sqlite3_total_changes
1.121 +** sqlite3_transfer_bindings
1.122 +**
1.123 +** A single SQLite connection (an sqlite3* object) or an SQLite statement
1.124 +** (an sqlite3_stmt* object) should only be passed to a single interface
1.125 +** function at a time. The connections and statements can be passed from
1.126 +** any thread to any of the functions listed in the second group above as
1.127 +** long as the same connection is not in use by two threads at once and
1.128 +** as long as SQLITE_ENABLE_MEMORY_MANAGEMENT is not defined. Additional
1.129 +** information about the SQLITE_ENABLE_MEMORY_MANAGEMENT constraint is
1.130 +** below.
1.131 +**
1.132 +** The busy handler for all database connections should remain turned
1.133 +** off. That means that any lock contention will cause the associated
1.134 +** sqlite3_client_step() call to return immediately with an SQLITE_BUSY
1.135 +** error code. If a busy handler is enabled and lock contention occurs,
1.136 +** then the entire server thread will block. This will cause not only
1.137 +** the requesting client to block but every other database client as
1.138 +** well. It is possible to enhance the code below so that lock
1.139 +** contention will cause the message to be placed back on the top of
1.140 +** the queue to be tried again later. But such enhanced processing is
1.141 +** not included here, in order to keep the example simple.
1.142 +**
1.143 +** This example code assumes the use of pthreads. Pthreads
1.144 +** implementations are available for windows. (See, for example
1.145 +** http://sourceware.org/pthreads-win32/announcement.html.) Or, you
1.146 +** can translate the locking and thread synchronization code to use
1.147 +** windows primitives easily enough. The details are left as an
1.148 +** exercise to the reader.
1.149 +**
1.150 +**** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****
1.151 +**
1.152 +** If you compile with SQLITE_ENABLE_MEMORY_MANAGEMENT defined, then
1.153 +** SQLite includes code that tracks how much memory is being used by
1.154 +** each thread. These memory counts can become confused if memory
1.155 +** is allocated by one thread and then freed by another. For that
1.156 +** reason, when SQLITE_ENABLE_MEMORY_MANAGEMENT is used, all operations
1.157 +** that might allocate or free memory should be performanced in the same
1.158 +** thread that originally created the database connection. In that case,
1.159 +** many of the operations that are listed above as safe to be performed
1.160 +** in separate threads would need to be sent over to the server to be
1.161 +** done there. If SQLITE_ENABLE_MEMORY_MANAGEMENT is defined, then
1.162 +** the following functions can be used safely from different threads
1.163 +** without messing up the allocation counts:
1.164 +**
1.165 +** sqlite3_bind_parameter_name
1.166 +** sqlite3_bind_parameter_index
1.167 +** sqlite3_changes
1.168 +** sqlite3_column_blob
1.169 +** sqlite3_column_count
1.170 +** sqlite3_complete
1.171 +** sqlite3_data_count
1.172 +** sqlite3_db_handle
1.173 +** sqlite3_errcode
1.174 +** sqlite3_errmsg
1.175 +** sqlite3_last_insert_rowid
1.176 +** sqlite3_total_changes
1.177 +**
1.178 +** The remaining functions are not thread-safe when memory management
1.179 +** is enabled. So one would have to define some new interface routines
1.180 +** along the following lines:
1.181 +**
1.182 +** sqlite3_client_bind_*
1.183 +** sqlite3_client_clear_bindings
1.184 +** sqlite3_client_column_*
1.185 +** sqlite3_client_create_collation
1.186 +** sqlite3_client_create_function
1.187 +** sqlite3_client_transfer_bindings
1.188 +**
1.189 +** The example code in this file is intended for use with memory
1.190 +** management turned off. So the implementation of these additional
1.191 +** client interfaces is left as an exercise to the reader.
1.192 +**
1.193 +** It may seem surprising to the reader that the list of safe functions
1.194 +** above does not include things like sqlite3_bind_int() or
1.195 +** sqlite3_column_int(). But those routines might, in fact, allocate
1.196 +** or deallocate memory. In the case of sqlite3_bind_int(), if the
1.197 +** parameter was previously bound to a string that string might need
1.198 +** to be deallocated before the new integer value is inserted. In
1.199 +** the case of sqlite3_column_int(), the value of the column might be
1.200 +** a UTF-16 string which will need to be converted to UTF-8 then into
1.201 +** an integer.
1.202 +*/
1.203 +
1.204 +/* Include this to get the definition of SQLITE_THREADSAFE, in the
1.205 +** case that default values are used.
1.206 +*/
1.207 +#include "sqliteInt.h"
1.208 +
1.209 +/*
1.210 +** Only compile the code in this file on UNIX with a SQLITE_THREADSAFE build
1.211 +** and only if the SQLITE_SERVER macro is defined.
1.212 +*/
1.213 +#if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
1.214 +#if defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE
1.215 +
1.216 +/*
1.217 +** We require only pthreads and the public interface of SQLite.
1.218 +*/
1.219 +#include <pthread.h>
1.220 +#include "sqlite3.h"
1.221 +
1.222 +/*
1.223 +** Messages are passed from client to server and back again as
1.224 +** instances of the following structure.
1.225 +*/
1.226 +typedef struct SqlMessage SqlMessage;
1.227 +struct SqlMessage {
1.228 + int op; /* Opcode for the message */
1.229 + sqlite3 *pDb; /* The SQLite connection */
1.230 + sqlite3_stmt *pStmt; /* A specific statement */
1.231 + int errCode; /* Error code returned */
1.232 + const char *zIn; /* Input filename or SQL statement */
1.233 + int nByte; /* Size of the zIn parameter for prepare() */
1.234 + const char *zOut; /* Tail of the SQL statement */
1.235 + SqlMessage *pNext; /* Next message in the queue */
1.236 + SqlMessage *pPrev; /* Previous message in the queue */
1.237 + pthread_mutex_t clientMutex; /* Hold this mutex to access the message */
1.238 + pthread_cond_t clientWakeup; /* Signal to wake up the client */
1.239 +};
1.240 +
1.241 +/*
1.242 +** Legal values for SqlMessage.op
1.243 +*/
1.244 +#define MSG_Open 1 /* sqlite3_open(zIn, &pDb) */
1.245 +#define MSG_Prepare 2 /* sqlite3_prepare(pDb, zIn, nByte, &pStmt, &zOut) */
1.246 +#define MSG_Step 3 /* sqlite3_step(pStmt) */
1.247 +#define MSG_Reset 4 /* sqlite3_reset(pStmt) */
1.248 +#define MSG_Finalize 5 /* sqlite3_finalize(pStmt) */
1.249 +#define MSG_Close 6 /* sqlite3_close(pDb) */
1.250 +#define MSG_Done 7 /* Server has finished with this message */
1.251 +
1.252 +
1.253 +/*
1.254 +** State information about the server is stored in a static variable
1.255 +** named "g" as follows:
1.256 +*/
1.257 +static struct ServerState {
1.258 + pthread_mutex_t queueMutex; /* Hold this mutex to access the msg queue */
1.259 + pthread_mutex_t serverMutex; /* Held by the server while it is running */
1.260 + pthread_cond_t serverWakeup; /* Signal this condvar to wake up the server */
1.261 + volatile int serverHalt; /* Server halts itself when true */
1.262 + SqlMessage *pQueueHead; /* Head of the message queue */
1.263 + SqlMessage *pQueueTail; /* Tail of the message queue */
1.264 +} g = {
1.265 + PTHREAD_MUTEX_INITIALIZER,
1.266 + PTHREAD_MUTEX_INITIALIZER,
1.267 + PTHREAD_COND_INITIALIZER,
1.268 +};
1.269 +
1.270 +/*
1.271 +** Send a message to the server. Block until we get a reply.
1.272 +**
1.273 +** The mutex and condition variable in the message are uninitialized
1.274 +** when this routine is called. This routine takes care of
1.275 +** initializing them and destroying them when it has finished.
1.276 +*/
1.277 +static void sendToServer(SqlMessage *pMsg){
1.278 + /* Initialize the mutex and condition variable on the message
1.279 + */
1.280 + pthread_mutex_init(&pMsg->clientMutex, 0);
1.281 + pthread_cond_init(&pMsg->clientWakeup, 0);
1.282 +
1.283 + /* Add the message to the head of the server's message queue.
1.284 + */
1.285 + pthread_mutex_lock(&g.queueMutex);
1.286 + pMsg->pNext = g.pQueueHead;
1.287 + if( g.pQueueHead==0 ){
1.288 + g.pQueueTail = pMsg;
1.289 + }else{
1.290 + g.pQueueHead->pPrev = pMsg;
1.291 + }
1.292 + pMsg->pPrev = 0;
1.293 + g.pQueueHead = pMsg;
1.294 + pthread_mutex_unlock(&g.queueMutex);
1.295 +
1.296 + /* Signal the server that the new message has be queued, then
1.297 + ** block waiting for the server to process the message.
1.298 + */
1.299 + pthread_mutex_lock(&pMsg->clientMutex);
1.300 + pthread_cond_signal(&g.serverWakeup);
1.301 + while( pMsg->op!=MSG_Done ){
1.302 + pthread_cond_wait(&pMsg->clientWakeup, &pMsg->clientMutex);
1.303 + }
1.304 + pthread_mutex_unlock(&pMsg->clientMutex);
1.305 +
1.306 + /* Destroy the mutex and condition variable of the message.
1.307 + */
1.308 + pthread_mutex_destroy(&pMsg->clientMutex);
1.309 + pthread_cond_destroy(&pMsg->clientWakeup);
1.310 +}
1.311 +
1.312 +/*
1.313 +** The following 6 routines are client-side implementations of the
1.314 +** core SQLite interfaces:
1.315 +**
1.316 +** sqlite3_open
1.317 +** sqlite3_prepare
1.318 +** sqlite3_step
1.319 +** sqlite3_reset
1.320 +** sqlite3_finalize
1.321 +** sqlite3_close
1.322 +**
1.323 +** Clients should use the following client-side routines instead of
1.324 +** the core routines above.
1.325 +**
1.326 +** sqlite3_client_open
1.327 +** sqlite3_client_prepare
1.328 +** sqlite3_client_step
1.329 +** sqlite3_client_reset
1.330 +** sqlite3_client_finalize
1.331 +** sqlite3_client_close
1.332 +**
1.333 +** Each of these routines creates a message for the desired operation,
1.334 +** sends that message to the server, waits for the server to process
1.335 +** then message and return a response.
1.336 +*/
1.337 +int sqlite3_client_open(const char *zDatabaseName, sqlite3 **ppDb){
1.338 + SqlMessage msg;
1.339 + msg.op = MSG_Open;
1.340 + msg.zIn = zDatabaseName;
1.341 + sendToServer(&msg);
1.342 + *ppDb = msg.pDb;
1.343 + return msg.errCode;
1.344 +}
1.345 +int sqlite3_client_prepare(
1.346 + sqlite3 *pDb,
1.347 + const char *zSql,
1.348 + int nByte,
1.349 + sqlite3_stmt **ppStmt,
1.350 + const char **pzTail
1.351 +){
1.352 + SqlMessage msg;
1.353 + msg.op = MSG_Prepare;
1.354 + msg.pDb = pDb;
1.355 + msg.zIn = zSql;
1.356 + msg.nByte = nByte;
1.357 + sendToServer(&msg);
1.358 + *ppStmt = msg.pStmt;
1.359 + if( pzTail ) *pzTail = msg.zOut;
1.360 + return msg.errCode;
1.361 +}
1.362 +int sqlite3_client_step(sqlite3_stmt *pStmt){
1.363 + SqlMessage msg;
1.364 + msg.op = MSG_Step;
1.365 + msg.pStmt = pStmt;
1.366 + sendToServer(&msg);
1.367 + return msg.errCode;
1.368 +}
1.369 +int sqlite3_client_reset(sqlite3_stmt *pStmt){
1.370 + SqlMessage msg;
1.371 + msg.op = MSG_Reset;
1.372 + msg.pStmt = pStmt;
1.373 + sendToServer(&msg);
1.374 + return msg.errCode;
1.375 +}
1.376 +int sqlite3_client_finalize(sqlite3_stmt *pStmt){
1.377 + SqlMessage msg;
1.378 + msg.op = MSG_Finalize;
1.379 + msg.pStmt = pStmt;
1.380 + sendToServer(&msg);
1.381 + return msg.errCode;
1.382 +}
1.383 +int sqlite3_client_close(sqlite3 *pDb){
1.384 + SqlMessage msg;
1.385 + msg.op = MSG_Close;
1.386 + msg.pDb = pDb;
1.387 + sendToServer(&msg);
1.388 + return msg.errCode;
1.389 +}
1.390 +
1.391 +/*
1.392 +** This routine implements the server. To start the server, first
1.393 +** make sure g.serverHalt is false, then create a new detached thread
1.394 +** on this procedure. See the sqlite3_server_start() routine below
1.395 +** for an example. This procedure loops until g.serverHalt becomes
1.396 +** true.
1.397 +*/
1.398 +void *sqlite3_server(void *NotUsed){
1.399 + if( pthread_mutex_trylock(&g.serverMutex) ){
1.400 + return 0; /* Another server is already running */
1.401 + }
1.402 + sqlite3_enable_shared_cache(1);
1.403 + while( !g.serverHalt ){
1.404 + SqlMessage *pMsg;
1.405 +
1.406 + /* Remove the last message from the message queue.
1.407 + */
1.408 + pthread_mutex_lock(&g.queueMutex);
1.409 + while( g.pQueueTail==0 && g.serverHalt==0 ){
1.410 + pthread_cond_wait(&g.serverWakeup, &g.queueMutex);
1.411 + }
1.412 + pMsg = g.pQueueTail;
1.413 + if( pMsg ){
1.414 + if( pMsg->pPrev ){
1.415 + pMsg->pPrev->pNext = 0;
1.416 + }else{
1.417 + g.pQueueHead = 0;
1.418 + }
1.419 + g.pQueueTail = pMsg->pPrev;
1.420 + }
1.421 + pthread_mutex_unlock(&g.queueMutex);
1.422 + if( pMsg==0 ) break;
1.423 +
1.424 + /* Process the message just removed
1.425 + */
1.426 + pthread_mutex_lock(&pMsg->clientMutex);
1.427 + switch( pMsg->op ){
1.428 + case MSG_Open: {
1.429 + pMsg->errCode = sqlite3_open(pMsg->zIn, &pMsg->pDb);
1.430 + break;
1.431 + }
1.432 + case MSG_Prepare: {
1.433 + pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,
1.434 + &pMsg->pStmt, &pMsg->zOut);
1.435 + break;
1.436 + }
1.437 + case MSG_Step: {
1.438 + pMsg->errCode = sqlite3_step(pMsg->pStmt);
1.439 + break;
1.440 + }
1.441 + case MSG_Reset: {
1.442 + pMsg->errCode = sqlite3_reset(pMsg->pStmt);
1.443 + break;
1.444 + }
1.445 + case MSG_Finalize: {
1.446 + pMsg->errCode = sqlite3_finalize(pMsg->pStmt);
1.447 + break;
1.448 + }
1.449 + case MSG_Close: {
1.450 + pMsg->errCode = sqlite3_close(pMsg->pDb);
1.451 + break;
1.452 + }
1.453 + }
1.454 +
1.455 + /* Signal the client that the message has been processed.
1.456 + */
1.457 + pMsg->op = MSG_Done;
1.458 + pthread_mutex_unlock(&pMsg->clientMutex);
1.459 + pthread_cond_signal(&pMsg->clientWakeup);
1.460 + }
1.461 + sqlite3_thread_cleanup();
1.462 + pthread_mutex_unlock(&g.serverMutex);
1.463 + return 0;
1.464 +}
1.465 +
1.466 +/*
1.467 +** Start a server thread if one is not already running. If there
1.468 +** is aleady a server thread running, the new thread will quickly
1.469 +** die and this routine is effectively a no-op.
1.470 +*/
1.471 +void sqlite3_server_start(void){
1.472 + pthread_t x;
1.473 + int rc;
1.474 + g.serverHalt = 0;
1.475 + rc = pthread_create(&x, 0, sqlite3_server, 0);
1.476 + if( rc==0 ){
1.477 + pthread_detach(x);
1.478 + }
1.479 +}
1.480 +
1.481 +/*
1.482 +** If a server thread is running, then stop it. If no server is
1.483 +** running, this routine is effectively a no-op.
1.484 +**
1.485 +** This routine waits until the server has actually stopped before
1.486 +** returning.
1.487 +*/
1.488 +void sqlite3_server_stop(void){
1.489 + g.serverHalt = 1;
1.490 + pthread_cond_broadcast(&g.serverWakeup);
1.491 + pthread_mutex_lock(&g.serverMutex);
1.492 + pthread_mutex_unlock(&g.serverMutex);
1.493 +}
1.494 +
1.495 +#endif /* defined(SQLITE_OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE */
1.496 +#endif /* defined(SQLITE_SERVER) */