Ensure that sqlite3AuthRead() is only call for TK_COLUMN and TK_TRIGGER
[sqlite.git] / src / test_server.c
blob4eb1cf19660e9bddd5f7cfd6228f78b54c3c9276
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.
11 ******************************************************************************
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:
16 ** -DSQLITE_SERVER=1
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.
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.
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.
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!)
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.
47 ** (3) Database connections on a shared cache use table-level locking
48 ** instead of file-level locking for improved concurrency.
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.
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.
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.
68 ** Here is how the client/server approach works: The database server
69 ** thread is started on this procedure:
71 ** void *sqlite3_server(void *NotUsed);
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.
78 ** Two convenience routines are provided for starting and stopping the
79 ** server thread:
81 ** void sqlite3_server_start(void);
82 ** void sqlite3_server_stop(void);
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.
88 ** Clients use the following interfaces:
90 ** sqlite3_client_open
91 ** sqlite3_client_prepare
92 ** sqlite3_client_step
93 ** sqlite3_client_reset
94 ** sqlite3_client_finalize
95 ** sqlite3_client_close
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:
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
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.
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.
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.
145 **** Restrictions Associated With SQLITE_ENABLE_MEMORY_MANAGEMENT ****
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:
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
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:
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
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.
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.
199 /* Include this to get the definition of SQLITE_THREADSAFE, in the
200 ** case that default values are used.
202 #include "sqliteInt.h"
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.
208 #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE)
209 #if SQLITE_OS_UNIX && SQLITE_THREADSAFE
212 ** We require only pthreads and the public interface of SQLite.
214 #include <pthread.h>
215 #include "sqlite3.h"
218 ** Messages are passed from client to server and back again as
219 ** instances of the following structure.
221 typedef struct SqlMessage SqlMessage;
222 struct 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 */
237 ** Legal values for SqlMessage.op
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 */
249 ** State information about the server is stored in a static variable
250 ** named "g" as follows:
252 static 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,
266 ** Send a message to the server. Block until we get a reply.
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.
272 static void sendToServer(SqlMessage *pMsg){
273 /* Initialize the mutex and condition variable on the message
275 pthread_mutex_init(&pMsg->clientMutex, 0);
276 pthread_cond_init(&pMsg->clientWakeup, 0);
278 /* Add the message to the head of the server's message queue.
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;
287 pMsg->pPrev = 0;
288 g.pQueueHead = pMsg;
289 pthread_mutex_unlock(&g.queueMutex);
291 /* Signal the server that the new message has be queued, then
292 ** block waiting for the server to process the message.
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);
299 pthread_mutex_unlock(&pMsg->clientMutex);
301 /* Destroy the mutex and condition variable of the message.
303 pthread_mutex_destroy(&pMsg->clientMutex);
304 pthread_cond_destroy(&pMsg->clientWakeup);
308 ** The following 6 routines are client-side implementations of the
309 ** core SQLite interfaces:
311 ** sqlite3_open
312 ** sqlite3_prepare
313 ** sqlite3_step
314 ** sqlite3_reset
315 ** sqlite3_finalize
316 ** sqlite3_close
318 ** Clients should use the following client-side routines instead of
319 ** the core routines above.
321 ** sqlite3_client_open
322 ** sqlite3_client_prepare
323 ** sqlite3_client_step
324 ** sqlite3_client_reset
325 ** sqlite3_client_finalize
326 ** sqlite3_client_close
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.
332 int 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;
340 int sqlite3_client_prepare(
341 sqlite3 *pDb,
342 const char *zSql,
343 int nByte,
344 sqlite3_stmt **ppStmt,
345 const char **pzTail
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;
357 int 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;
364 int 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;
371 int 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;
378 int sqlite3_client_close(sqlite3 *pDb){
379 SqlMessage msg;
380 msg.op = MSG_Close;
381 msg.pDb = pDb;
382 sendToServer(&msg);
383 return msg.errCode;
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.
393 void *sqlite3_server(void *NotUsed){
394 if( pthread_mutex_trylock(&g.serverMutex) ){
395 return 0; /* Another server is already running */
397 sqlite3_enable_shared_cache(1);
398 while( !g.serverHalt ){
399 SqlMessage *pMsg;
401 /* Remove the last message from the message queue.
403 pthread_mutex_lock(&g.queueMutex);
404 while( g.pQueueTail==0 && g.serverHalt==0 ){
405 pthread_cond_wait(&g.serverWakeup, &g.queueMutex);
407 pMsg = g.pQueueTail;
408 if( pMsg ){
409 if( pMsg->pPrev ){
410 pMsg->pPrev->pNext = 0;
411 }else{
412 g.pQueueHead = 0;
414 g.pQueueTail = pMsg->pPrev;
416 pthread_mutex_unlock(&g.queueMutex);
417 if( pMsg==0 ) break;
419 /* Process the message just removed
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;
427 case MSG_Prepare: {
428 pMsg->errCode = sqlite3_prepare(pMsg->pDb, pMsg->zIn, pMsg->nByte,
429 &pMsg->pStmt, &pMsg->zOut);
430 break;
432 case MSG_Step: {
433 pMsg->errCode = sqlite3_step(pMsg->pStmt);
434 break;
436 case MSG_Reset: {
437 pMsg->errCode = sqlite3_reset(pMsg->pStmt);
438 break;
440 case MSG_Finalize: {
441 pMsg->errCode = sqlite3_finalize(pMsg->pStmt);
442 break;
444 case MSG_Close: {
445 pMsg->errCode = sqlite3_close(pMsg->pDb);
446 break;
450 /* Signal the client that the message has been processed.
452 pMsg->op = MSG_Done;
453 pthread_mutex_unlock(&pMsg->clientMutex);
454 pthread_cond_signal(&pMsg->clientWakeup);
456 pthread_mutex_unlock(&g.serverMutex);
457 return 0;
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.
465 void 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);
476 ** A wrapper around sqlite3_server() that decrements the int variable
477 ** pointed to by the first argument after the sqlite3_server() call
478 ** returns.
480 static void *serverWrapper(void *pnDecr){
481 void *p = sqlite3_server(0);
482 (*(int*)pnDecr)--;
483 return p;
487 ** This function is the similar to sqlite3_server_start(), except that
488 ** the integer pointed to by the first argument is decremented when
489 ** the server thread exits.
491 void sqlite3_server_start2(int *pnDecr){
492 pthread_t x;
493 int rc;
494 g.serverHalt = 0;
495 rc = pthread_create(&x, 0, serverWrapper, (void*)pnDecr);
496 if( rc==0 ){
497 pthread_detach(x);
502 ** If a server thread is running, then stop it. If no server is
503 ** running, this routine is effectively a no-op.
505 ** This routine waits until the server has actually stopped before
506 ** returning.
508 void sqlite3_server_stop(void){
509 g.serverHalt = 1;
510 pthread_cond_broadcast(&g.serverWakeup);
511 pthread_mutex_lock(&g.serverMutex);
512 pthread_mutex_unlock(&g.serverMutex);
515 #endif /* SQLITE_OS_UNIX && SQLITE_THREADSAFE */
516 #endif /* defined(SQLITE_SERVER) */