Fix shutdown handling of sqlite3 astdb.
[asterisk/asterisk.git] / main / db.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2005, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * See http://www.asterisk.org for more information about
9  * the Asterisk project. Please do not directly contact
10  * any of the maintainers of this project for assistance;
11  * the project provides a web site, mailing lists and IRC
12  * channels for your use.
13  *
14  * This program is free software, distributed under the terms of
15  * the GNU General Public License Version 2. See the LICENSE file
16  * at the top of the source tree.
17  */
18
19 /*! \file
20  *
21  * \brief ASTdb Management
22  *
23  * \author Mark Spencer <markster@digium.com>
24  *
25  * \note DB3 is licensed under Sleepycat Public License and is thus incompatible
26  * with GPL.  To avoid having to make another exception (and complicate
27  * licensing even further) we elect to use DB1 which is BSD licensed
28  */
29
30 #include "asterisk.h"
31
32 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
33
34 #include "asterisk/_private.h"
35 #include "asterisk/paths.h"     /* use ast_config_AST_DB */
36 #include <sys/time.h>
37 #include <sys/types.h>
38 #include <sys/stat.h>
39 #include <unistd.h>
40 #include <signal.h>
41 #include <dirent.h>
42 #include <sqlite3.h>
43
44 #include "asterisk/channel.h"
45 #include "asterisk/file.h"
46 #include "asterisk/app.h"
47 #include "asterisk/dsp.h"
48 #include "asterisk/astdb.h"
49 #include "asterisk/cli.h"
50 #include "asterisk/utils.h"
51 #include "asterisk/manager.h"
52
53 /*** DOCUMENTATION
54         <manager name="DBGet" language="en_US">
55                 <synopsis>
56                         Get DB Entry.
57                 </synopsis>
58                 <syntax>
59                         <xi:include xpointer="xpointer(/docs/manager[@name='Login']/syntax/parameter[@name='ActionID'])" />
60                         <parameter name="Family" required="true" />
61                         <parameter name="Key" required="true" />
62                 </syntax>
63                 <description>
64                 </description>
65         </manager>
66         <manager name="DBPut" language="en_US">
67                 <synopsis>
68                         Put DB entry.
69                 </synopsis>
70                 <syntax>
71                         <xi:include xpointer="xpointer(/docs/manager[@name='Login']/syntax/parameter[@name='ActionID'])" />
72                         <parameter name="Family" required="true" />
73                         <parameter name="Key" required="true" />
74                         <parameter name="Val" />
75                 </syntax>
76                 <description>
77                 </description>
78         </manager>
79         <manager name="DBDel" language="en_US">
80                 <synopsis>
81                         Delete DB entry.
82                 </synopsis>
83                 <syntax>
84                         <xi:include xpointer="xpointer(/docs/manager[@name='Login']/syntax/parameter[@name='ActionID'])" />
85                         <parameter name="Family" required="true" />
86                         <parameter name="Key" required="true" />
87                 </syntax>
88                 <description>
89                 </description>
90         </manager>
91         <manager name="DBDelTree" language="en_US">
92                 <synopsis>
93                         Delete DB Tree.
94                 </synopsis>
95                 <syntax>
96                         <xi:include xpointer="xpointer(/docs/manager[@name='Login']/syntax/parameter[@name='ActionID'])" />
97                         <parameter name="Family" required="true" />
98                         <parameter name="Key" />
99                 </syntax>
100                 <description>
101                 </description>
102         </manager>
103  ***/
104
105 #define MAX_DB_FIELD 256
106 AST_MUTEX_DEFINE_STATIC(dblock);
107 static ast_cond_t dbcond;
108 static sqlite3 *astdb;
109 static pthread_t syncthread;
110 static int doexit;
111
112 static void db_sync(void);
113
114 #define DEFINE_SQL_STATEMENT(stmt,sql) static sqlite3_stmt *stmt; \
115         const char stmt##_sql[] = sql;
116
117 DEFINE_SQL_STATEMENT(put_stmt, "INSERT OR REPLACE INTO astdb (key, value) VALUES (?, ?)")
118 DEFINE_SQL_STATEMENT(get_stmt, "SELECT value FROM astdb WHERE key=?")
119 DEFINE_SQL_STATEMENT(del_stmt, "DELETE FROM astdb WHERE key=?")
120 DEFINE_SQL_STATEMENT(deltree_stmt, "DELETE FROM astdb WHERE key || '/' LIKE ? || '/' || '%'")
121 DEFINE_SQL_STATEMENT(deltree_all_stmt, "DELETE FROM astdb")
122 DEFINE_SQL_STATEMENT(gettree_stmt, "SELECT key, value FROM astdb WHERE key || '/' LIKE ? || '/' || '%'")
123 DEFINE_SQL_STATEMENT(gettree_all_stmt, "SELECT key, value FROM astdb")
124 DEFINE_SQL_STATEMENT(showkey_stmt, "SELECT key, value FROM astdb WHERE key LIKE '%' || '/' || ?")
125 DEFINE_SQL_STATEMENT(create_astdb_stmt, "CREATE TABLE IF NOT EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY KEY(key))")
126
127 static int init_stmt(sqlite3_stmt **stmt, const char *sql, size_t len)
128 {
129         ast_mutex_lock(&dblock);
130         if (sqlite3_prepare(astdb, sql, len, stmt, NULL) != SQLITE_OK) {
131                 ast_log(LOG_WARNING, "Couldn't prepare statement '%s': %s\n", sql, sqlite3_errmsg(astdb));
132                 ast_mutex_unlock(&dblock);
133                 return -1;
134         }
135         ast_mutex_unlock(&dblock);
136
137         return 0;
138 }
139
140 static int init_statements(void)
141 {
142         /* Don't initialize create_astdb_statment here as the astdb table needs to exist
143          * brefore these statments can be initialized */
144         return init_stmt(&get_stmt, get_stmt_sql, sizeof(get_stmt_sql))
145         || init_stmt(&del_stmt, del_stmt_sql, sizeof(del_stmt_sql))
146         || init_stmt(&deltree_stmt, deltree_stmt_sql, sizeof(deltree_stmt_sql))
147         || init_stmt(&deltree_all_stmt, deltree_all_stmt_sql, sizeof(deltree_all_stmt_sql))
148         || init_stmt(&gettree_stmt, gettree_stmt_sql, sizeof(gettree_stmt_sql))
149         || init_stmt(&gettree_all_stmt, gettree_all_stmt_sql, sizeof(gettree_all_stmt_sql))
150         || init_stmt(&showkey_stmt, showkey_stmt_sql, sizeof(showkey_stmt_sql))
151         || init_stmt(&put_stmt, put_stmt_sql, sizeof(put_stmt_sql));
152 }
153
154 static int convert_bdb_to_sqlite3(void)
155 {
156         char *cmd;
157         int res;
158
159         ast_asprintf(&cmd, "%s/astdb2sqlite3 '%s'\n", ast_config_AST_SBIN_DIR, ast_config_AST_DB);
160         res = ast_safe_system(cmd);
161         ast_free(cmd);
162
163         return res;
164 }
165
166 static int db_create_astdb(void)
167 {
168         int res = 0;
169
170         if (!create_astdb_stmt) {
171                 init_stmt(&create_astdb_stmt, create_astdb_stmt_sql, sizeof(create_astdb_stmt_sql));
172         }
173
174         ast_mutex_lock(&dblock);
175         if (sqlite3_step(create_astdb_stmt) != SQLITE_DONE) {
176                 ast_log(LOG_WARNING, "Couldn't create astdb table: %s\n", sqlite3_errmsg(astdb));
177                 res = -1;
178         }
179         sqlite3_reset(create_astdb_stmt);
180         db_sync();
181         ast_mutex_unlock(&dblock);
182
183         return res;
184 }
185
186 static int db_open(void)
187 {
188         char *dbname;
189         struct stat dont_care;
190
191         if (!(dbname = alloca(strlen(ast_config_AST_DB) + sizeof(".sqlite3")))) {
192                 return -1;
193         }
194         strcpy(dbname, ast_config_AST_DB);
195         strcat(dbname, ".sqlite3");
196
197         if (stat(dbname, &dont_care) && !stat(ast_config_AST_DB, &dont_care)) {
198                 if (convert_bdb_to_sqlite3()) {
199                         ast_log(LOG_ERROR, "*** Database conversion failed!\n");
200                         ast_log(LOG_ERROR, "*** Asterisk now uses SQLite3 for its internal\n");
201                         ast_log(LOG_ERROR, "*** database. Conversion from the old astdb\n");
202                         ast_log(LOG_ERROR, "*** failed. Most likely the astdb2sqlite3 utility\n");
203                         ast_log(LOG_ERROR, "*** was not selected for build. To convert the\n");
204                         ast_log(LOG_ERROR, "*** old astdb, please delete '%s'\n", dbname);
205                         ast_log(LOG_ERROR, "*** and re-run 'make menuselect' and select astdb2sqlite3\n");
206                         ast_log(LOG_ERROR, "*** in the Utilities section, then 'make && make install'.\n");
207                         ast_log(LOG_ERROR, "*** It is also imperative that the user under which\n");
208                         ast_log(LOG_ERROR, "*** Asterisk runs have write permission to the directory\n");
209                         ast_log(LOG_ERROR, "*** where the database resides.\n");
210                         sleep(5);
211                 } else {
212                         ast_log(LOG_NOTICE, "Database conversion succeeded!\n");
213                 }
214         }
215
216         ast_mutex_lock(&dblock);
217         if (sqlite3_open(dbname, &astdb) != SQLITE_OK) {
218                 ast_log(LOG_WARNING, "Unable to open Asterisk database '%s': %s\n", dbname, sqlite3_errmsg(astdb));
219                 sqlite3_close(astdb);
220                 ast_mutex_unlock(&dblock);
221                 return -1;
222         }
223         ast_mutex_unlock(&dblock);
224
225         return 0;
226 }
227
228 static int db_init(void)
229 {
230         if (astdb) {
231                 return 0;
232         }
233
234         if (db_open() || db_create_astdb() || init_statements()) {
235                 return -1;
236         }
237
238         return 0;
239 }
240
241 /* We purposely don't lock around the sqlite3 call because the transaction
242  * calls will be called with the database lock held. For any other use, make
243  * sure to take the dblock yourself. */
244 static int db_execute_sql(const char *sql, int (*callback)(void *, int, char **, char **), void *arg)
245 {
246         char *errmsg = NULL;
247         int res =0;
248
249         sqlite3_exec(astdb, sql, callback, arg, &errmsg);
250         if (errmsg) {
251                 ast_log(LOG_WARNING, "Error executing SQL: %s\n", errmsg);
252                 sqlite3_free(errmsg);
253                 res = -1;
254         }
255
256         return res;
257 }
258
259 static int ast_db_begin_transaction(void)
260 {
261         return db_execute_sql("BEGIN TRANSACTION", NULL, NULL);
262 }
263
264 static int ast_db_commit_transaction(void)
265 {
266         return db_execute_sql("COMMIT", NULL, NULL);
267 }
268
269 static int ast_db_rollback_transaction(void)
270 {
271         return db_execute_sql("ROLLBACK", NULL, NULL);
272 }
273
274 int ast_db_put(const char *family, const char *key, const char *value)
275 {
276         char fullkey[MAX_DB_FIELD];
277         size_t fullkey_len;
278         int res = 0;
279
280         if (strlen(family) + strlen(key) + 2 > sizeof(fullkey) - 1) {
281                 ast_log(LOG_WARNING, "Family and key length must be less than %zu bytes\n", sizeof(fullkey) - 3);
282                 return -1;
283         }
284
285         fullkey_len = snprintf(fullkey, sizeof(fullkey), "/%s/%s", family, key);
286
287         ast_mutex_lock(&dblock);
288         if (sqlite3_bind_text(put_stmt, 1, fullkey, fullkey_len, SQLITE_STATIC) != SQLITE_OK) {
289                 ast_log(LOG_WARNING, "Couldn't bind key to stmt: %s\n", sqlite3_errmsg(astdb));
290                 res = -1;
291         } else if (sqlite3_bind_text(put_stmt, 2, value, -1, SQLITE_STATIC) != SQLITE_OK) {
292                 ast_log(LOG_WARNING, "Couldn't bind value to stmt: %s\n", sqlite3_errmsg(astdb));
293                 res = -1;
294         } else if (sqlite3_step(put_stmt) != SQLITE_DONE) {
295                 ast_log(LOG_WARNING, "Couldn't execute statment: %s\n", sqlite3_errmsg(astdb));
296                 res = -1;
297         }
298
299         sqlite3_reset(put_stmt);
300         db_sync();
301         ast_mutex_unlock(&dblock);
302
303         return res;
304 }
305
306 int ast_db_get(const char *family, const char *key, char *value, int valuelen)
307 {
308         const unsigned char *result;
309         char fullkey[MAX_DB_FIELD];
310         size_t fullkey_len;
311         int res = 0;
312
313         if (strlen(family) + strlen(key) + 2 > sizeof(fullkey) - 1) {
314                 ast_log(LOG_WARNING, "Family and key length must be less than %zu bytes\n", sizeof(fullkey) - 3);
315                 return -1;
316         }
317
318         fullkey_len = snprintf(fullkey, sizeof(fullkey), "/%s/%s", family, key);
319
320         ast_mutex_lock(&dblock);
321         if (sqlite3_bind_text(get_stmt, 1, fullkey, fullkey_len, SQLITE_STATIC) != SQLITE_OK) {
322                 ast_log(LOG_WARNING, "Couldn't bind key to stmt: %s\n", sqlite3_errmsg(astdb));
323                 res = -1;
324         } else if (sqlite3_step(get_stmt) != SQLITE_ROW) {
325                 ast_debug(1, "Unable to find key '%s' in family '%s'\n", key, family);
326                 res = -1;
327         } else if (!(result = sqlite3_column_text(get_stmt, 0))) {
328                 ast_log(LOG_WARNING, "Couldn't get value\n");
329                 res = -1;
330         } else {
331                 strncpy(value, (const char *) result, valuelen);
332         }
333         sqlite3_reset(get_stmt);
334         ast_mutex_unlock(&dblock);
335
336         return res;
337 }
338
339 int ast_db_del(const char *family, const char *key)
340 {
341         char fullkey[MAX_DB_FIELD];
342         size_t fullkey_len;
343         int res = 0;
344
345         if (strlen(family) + strlen(key) + 2 > sizeof(fullkey) - 1) {
346                 ast_log(LOG_WARNING, "Family and key length must be less than %zu bytes\n", sizeof(fullkey) - 3);
347                 return -1;
348         }
349
350         fullkey_len = snprintf(fullkey, sizeof(fullkey), "/%s/%s", family, key);
351
352         ast_mutex_lock(&dblock);
353         if (sqlite3_bind_text(del_stmt, 1, fullkey, fullkey_len, SQLITE_STATIC) != SQLITE_OK) {
354                 ast_log(LOG_WARNING, "Couldn't bind key to stmt: %s\n", sqlite3_errmsg(astdb));
355                 res = -1;
356         } else if (sqlite3_step(del_stmt) != SQLITE_DONE) {
357                 ast_debug(1, "Unable to find key '%s' in family '%s'\n", key, family);
358                 res = -1;
359         }
360         sqlite3_reset(del_stmt);
361         db_sync();
362         ast_mutex_unlock(&dblock);
363
364         return res;
365 }
366
367 int ast_db_deltree(const char *family, const char *keytree)
368 {
369         sqlite3_stmt *stmt = deltree_stmt;
370         char prefix[MAX_DB_FIELD];
371         int res = 0;
372
373         if (!ast_strlen_zero(family)) {
374                 if (!ast_strlen_zero(keytree)) {
375                         /* Family and key tree */
376                         snprintf(prefix, sizeof(prefix), "/%s/%s", family, keytree);
377                 } else {
378                         /* Family only */
379                         snprintf(prefix, sizeof(prefix), "/%s", family);
380                 }
381         } else {
382                 prefix[0] = '\0';
383                 stmt = deltree_all_stmt;
384         }
385
386         ast_mutex_lock(&dblock);
387         if (!ast_strlen_zero(prefix) && (sqlite3_bind_text(stmt, 1, prefix, -1, SQLITE_STATIC) != SQLITE_OK)) {
388                 ast_log(LOG_WARNING, "Could bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
389                 res = -1;
390         } else if (sqlite3_step(stmt) != SQLITE_DONE) {
391                 ast_log(LOG_WARNING, "Couldn't execute stmt: %s\n", sqlite3_errmsg(astdb));
392                 res = -1;
393         }
394         res = sqlite3_changes(astdb);
395         sqlite3_reset(stmt);
396         db_sync();
397         ast_mutex_unlock(&dblock);
398
399         return res;
400 }
401
402 struct ast_db_entry *ast_db_gettree(const char *family, const char *keytree)
403 {
404         char prefix[MAX_DB_FIELD];
405         sqlite3_stmt *stmt = gettree_stmt;
406         struct ast_db_entry *cur, *last = NULL, *ret = NULL;
407
408         if (!ast_strlen_zero(family)) {
409                 if (!ast_strlen_zero(keytree)) {
410                         /* Family and key tree */
411                         snprintf(prefix, sizeof(prefix), "/%s/%s", family, keytree);
412                 } else {
413                         /* Family only */
414                         snprintf(prefix, sizeof(prefix), "/%s", family);
415                 }
416         } else {
417                 prefix[0] = '\0';
418                 stmt = gettree_all_stmt;
419         }
420
421         ast_mutex_lock(&dblock);
422         if (!ast_strlen_zero(prefix) && (sqlite3_bind_text(stmt, 1, prefix, -1, SQLITE_STATIC) != SQLITE_OK)) {
423                 ast_log(LOG_WARNING, "Could bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
424                 sqlite3_reset(stmt);
425                 ast_mutex_unlock(&dblock);
426                 return NULL;
427         }
428
429         while (sqlite3_step(stmt) == SQLITE_ROW) {
430                 const char *key_s, *value_s;
431                 if (!(key_s = (const char *) sqlite3_column_text(stmt, 0))) {
432                         break;
433                 }
434                 if (!(value_s = (const char *) sqlite3_column_text(stmt, 1))) {
435                         break;
436                 }
437                 if (!(cur = ast_malloc(sizeof(*cur) + strlen(key_s) + strlen(value_s) + 2))) {
438                         break;
439                 }
440                 cur->next = NULL;
441                 cur->key = cur->data + strlen(value_s) + 1;
442                 strcpy(cur->data, value_s);
443                 strcpy(cur->key, key_s);
444                 if (last) {
445                         last->next = cur;
446                 } else {
447                         ret = cur;
448                 }
449                 last = cur;
450         }
451         sqlite3_reset(stmt);
452         ast_mutex_unlock(&dblock);
453
454         return ret;
455 }
456
457 void ast_db_freetree(struct ast_db_entry *dbe)
458 {
459         struct ast_db_entry *last;
460         while (dbe) {
461                 last = dbe;
462                 dbe = dbe->next;
463                 ast_free(last);
464         }
465 }
466
467 static char *handle_cli_database_put(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
468 {
469         int res;
470
471         switch (cmd) {
472         case CLI_INIT:
473                 e->command = "database put";
474                 e->usage =
475                         "Usage: database put <family> <key> <value>\n"
476                         "       Adds or updates an entry in the Asterisk database for\n"
477                         "       a given family, key, and value.\n";
478                 return NULL;
479         case CLI_GENERATE:
480                 return NULL;
481         }
482
483         if (a->argc != 5)
484                 return CLI_SHOWUSAGE;
485         res = ast_db_put(a->argv[2], a->argv[3], a->argv[4]);
486         if (res)  {
487                 ast_cli(a->fd, "Failed to update entry\n");
488         } else {
489                 ast_cli(a->fd, "Updated database successfully\n");
490         }
491         return CLI_SUCCESS;
492 }
493
494 static char *handle_cli_database_get(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
495 {
496         int res;
497         char tmp[MAX_DB_FIELD];
498
499         switch (cmd) {
500         case CLI_INIT:
501                 e->command = "database get";
502                 e->usage =
503                         "Usage: database get <family> <key>\n"
504                         "       Retrieves an entry in the Asterisk database for a given\n"
505                         "       family and key.\n";
506                 return NULL;
507         case CLI_GENERATE:
508                 return NULL;
509         }
510
511         if (a->argc != 4)
512                 return CLI_SHOWUSAGE;
513         res = ast_db_get(a->argv[2], a->argv[3], tmp, sizeof(tmp));
514         if (res) {
515                 ast_cli(a->fd, "Database entry not found.\n");
516         } else {
517                 ast_cli(a->fd, "Value: %s\n", tmp);
518         }
519         return CLI_SUCCESS;
520 }
521
522 static char *handle_cli_database_del(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
523 {
524         int res;
525
526         switch (cmd) {
527         case CLI_INIT:
528                 e->command = "database del";
529                 e->usage =
530                         "Usage: database del <family> <key>\n"
531                         "       Deletes an entry in the Asterisk database for a given\n"
532                         "       family and key.\n";
533                 return NULL;
534         case CLI_GENERATE:
535                 return NULL;
536         }
537
538         if (a->argc != 4)
539                 return CLI_SHOWUSAGE;
540         res = ast_db_del(a->argv[2], a->argv[3]);
541         if (res) {
542                 ast_cli(a->fd, "Database entry does not exist.\n");
543         } else {
544                 ast_cli(a->fd, "Database entry removed.\n");
545         }
546         return CLI_SUCCESS;
547 }
548
549 static char *handle_cli_database_deltree(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
550 {
551         int res;
552
553         switch (cmd) {
554         case CLI_INIT:
555                 e->command = "database deltree";
556                 e->usage =
557                         "Usage: database deltree <family> [keytree]\n"
558                         "   OR: database deltree <family>[/keytree]\n"
559                         "       Deletes a family or specific keytree within a family\n"
560                         "       in the Asterisk database.  The two arguments may be\n"
561                         "       separated by either a space or a slash.\n";
562                 return NULL;
563         case CLI_GENERATE:
564                 return NULL;
565         }
566
567         if ((a->argc < 3) || (a->argc > 4))
568                 return CLI_SHOWUSAGE;
569         if (a->argc == 4) {
570                 res = ast_db_deltree(a->argv[2], a->argv[3]);
571         } else {
572                 res = ast_db_deltree(a->argv[2], NULL);
573         }
574         if (res < 0) {
575                 ast_cli(a->fd, "Database entries do not exist.\n");
576         } else {
577                 ast_cli(a->fd, "%d database entries removed.\n",res);
578         }
579         return CLI_SUCCESS;
580 }
581
582 static char *handle_cli_database_show(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
583 {
584         char prefix[MAX_DB_FIELD];
585         int counter = 0;
586         sqlite3_stmt *stmt = gettree_stmt;
587
588         switch (cmd) {
589         case CLI_INIT:
590                 e->command = "database show";
591                 e->usage =
592                         "Usage: database show [family [keytree]]\n"
593                         "   OR: database show [family[/keytree]]\n"
594                         "       Shows Asterisk database contents, optionally restricted\n"
595                         "       to a given family, or family and keytree. The two arguments\n"
596                         "       may be separated either by a space or by a slash.\n";
597                 return NULL;
598         case CLI_GENERATE:
599                 return NULL;
600         }
601
602         if (a->argc == 4) {
603                 /* Family and key tree */
604                 snprintf(prefix, sizeof(prefix), "/%s/%s", a->argv[2], a->argv[3]);
605         } else if (a->argc == 3) {
606                 /* Family only */
607                 snprintf(prefix, sizeof(prefix), "/%s", a->argv[2]);
608         } else if (a->argc == 2) {
609                 /* Neither */
610                 prefix[0] = '\0';
611                 stmt = gettree_all_stmt;
612
613         } else {
614                 return CLI_SHOWUSAGE;
615         }
616
617         ast_mutex_lock(&dblock);
618         if (!ast_strlen_zero(prefix) && (sqlite3_bind_text(stmt, 1, prefix, -1, SQLITE_STATIC) != SQLITE_OK)) {
619                 ast_log(LOG_WARNING, "Could bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
620                 sqlite3_reset(stmt);
621                 ast_mutex_unlock(&dblock);
622                 return NULL;
623         }
624
625         while (sqlite3_step(stmt) == SQLITE_ROW) {
626                 const char *key_s, *value_s;
627                 if (!(key_s = (const char *) sqlite3_column_text(stmt, 0))) {
628                         ast_log(LOG_WARNING, "Skipping invalid key!\n");
629                         continue;
630                 }
631                 if (!(value_s = (const char *) sqlite3_column_text(stmt, 1))) {
632                         ast_log(LOG_WARNING, "Skipping invalid value!\n");
633                         continue;
634                 }
635                 ++counter;
636                 ast_cli(a->fd, "%-50s: %-25s\n", key_s, value_s);
637         }
638
639         sqlite3_reset(stmt);
640         ast_mutex_unlock(&dblock);
641
642         ast_cli(a->fd, "%d results found.\n", counter);
643         return CLI_SUCCESS;
644 }
645
646 static char *handle_cli_database_showkey(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
647 {
648         int counter = 0;
649
650         switch (cmd) {
651         case CLI_INIT:
652                 e->command = "database showkey";
653                 e->usage =
654                         "Usage: database showkey <keytree>\n"
655                         "       Shows Asterisk database contents, restricted to a given key.\n";
656                 return NULL;
657         case CLI_GENERATE:
658                 return NULL;
659         }
660
661         if (a->argc != 3) {
662                 return CLI_SHOWUSAGE;
663         }
664
665         ast_mutex_lock(&dblock);
666         if (!ast_strlen_zero(a->argv[2]) && (sqlite3_bind_text(showkey_stmt, 1, a->argv[2], -1, SQLITE_STATIC) != SQLITE_OK)) {
667                 ast_log(LOG_WARNING, "Could bind %s to stmt: %s\n", a->argv[2], sqlite3_errmsg(astdb));
668                 sqlite3_reset(showkey_stmt);
669                 ast_mutex_unlock(&dblock);
670                 return NULL;
671         }
672
673         while (sqlite3_step(showkey_stmt) == SQLITE_ROW) {
674                 const char *key_s, *value_s;
675                 if (!(key_s = (const char *) sqlite3_column_text(showkey_stmt, 0))) {
676                         break;
677                 }
678                 if (!(value_s = (const char *) sqlite3_column_text(showkey_stmt, 1))) {
679                         break;
680                 }
681                 ++counter;
682                 ast_cli(a->fd, "%-50s: %-25s\n", key_s, value_s);
683         }
684         sqlite3_reset(showkey_stmt);
685         ast_mutex_unlock(&dblock);
686
687         ast_cli(a->fd, "%d results found.\n", counter);
688         return CLI_SUCCESS;
689 }
690
691 static int display_results(void *arg, int columns, char **values, char **colnames)
692 {
693         struct ast_cli_args *a = arg;
694         size_t x;
695
696         for (x = 0; x < columns; x++) {
697                 ast_cli(a->fd, "%-5s: %-50s\n", colnames[x], values[x]);
698         }
699         ast_cli(a->fd, "\n");
700
701         return 0;
702 }
703
704 static char *handle_cli_database_query(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
705 {
706
707         switch (cmd) {
708         case CLI_INIT:
709                 e->command = "database query";
710                 e->usage =
711                         "Usage: database query \"<SQL Statement>\"\n"
712                         "       Run a user-specified SQL query on the database. Be careful.\n";
713                 return NULL;
714         case CLI_GENERATE:
715                 return NULL;
716         }
717
718         if (a->argc != 3) {
719                 return CLI_SHOWUSAGE;
720         }
721
722         ast_mutex_lock(&dblock);
723         db_execute_sql(a->argv[2], display_results, a);
724         db_sync(); /* Go ahead and sync the db in case they write */
725         ast_mutex_unlock(&dblock);
726
727         return CLI_SUCCESS;
728 }
729
730 static struct ast_cli_entry cli_database[] = {
731         AST_CLI_DEFINE(handle_cli_database_show,    "Shows database contents"),
732         AST_CLI_DEFINE(handle_cli_database_showkey, "Shows database contents"),
733         AST_CLI_DEFINE(handle_cli_database_get,     "Gets database value"),
734         AST_CLI_DEFINE(handle_cli_database_put,     "Adds/updates database value"),
735         AST_CLI_DEFINE(handle_cli_database_del,     "Removes database key/value"),
736         AST_CLI_DEFINE(handle_cli_database_deltree, "Removes database keytree/values"),
737         AST_CLI_DEFINE(handle_cli_database_query,   "Run a user-specified query on the astdb"),
738 };
739
740 static int manager_dbput(struct mansession *s, const struct message *m)
741 {
742         const char *family = astman_get_header(m, "Family");
743         const char *key = astman_get_header(m, "Key");
744         const char *val = astman_get_header(m, "Val");
745         int res;
746
747         if (ast_strlen_zero(family)) {
748                 astman_send_error(s, m, "No family specified");
749                 return 0;
750         }
751         if (ast_strlen_zero(key)) {
752                 astman_send_error(s, m, "No key specified");
753                 return 0;
754         }
755
756         res = ast_db_put(family, key, S_OR(val, ""));
757         if (res) {
758                 astman_send_error(s, m, "Failed to update entry");
759         } else {
760                 astman_send_ack(s, m, "Updated database successfully");
761         }
762         return 0;
763 }
764
765 static int manager_dbget(struct mansession *s, const struct message *m)
766 {
767         const char *id = astman_get_header(m,"ActionID");
768         char idText[256] = "";
769         const char *family = astman_get_header(m, "Family");
770         const char *key = astman_get_header(m, "Key");
771         char tmp[MAX_DB_FIELD];
772         int res;
773
774         if (ast_strlen_zero(family)) {
775                 astman_send_error(s, m, "No family specified.");
776                 return 0;
777         }
778         if (ast_strlen_zero(key)) {
779                 astman_send_error(s, m, "No key specified.");
780                 return 0;
781         }
782
783         if (!ast_strlen_zero(id))
784                 snprintf(idText, sizeof(idText) ,"ActionID: %s\r\n", id);
785
786         res = ast_db_get(family, key, tmp, sizeof(tmp));
787         if (res) {
788                 astman_send_error(s, m, "Database entry not found");
789         } else {
790                 astman_send_ack(s, m, "Result will follow");
791                 astman_append(s, "Event: DBGetResponse\r\n"
792                                 "Family: %s\r\n"
793                                 "Key: %s\r\n"
794                                 "Val: %s\r\n"
795                                 "%s"
796                                 "\r\n",
797                                 family, key, tmp, idText);
798                 astman_append(s, "Event: DBGetComplete\r\n"
799                                 "%s"
800                                 "\r\n",
801                                 idText);
802         }
803         return 0;
804 }
805
806 static int manager_dbdel(struct mansession *s, const struct message *m)
807 {
808         const char *family = astman_get_header(m, "Family");
809         const char *key = astman_get_header(m, "Key");
810         int res;
811
812         if (ast_strlen_zero(family)) {
813                 astman_send_error(s, m, "No family specified.");
814                 return 0;
815         }
816
817         if (ast_strlen_zero(key)) {
818                 astman_send_error(s, m, "No key specified.");
819                 return 0;
820         }
821
822         res = ast_db_del(family, key);
823         if (res)
824                 astman_send_error(s, m, "Database entry not found");
825         else
826                 astman_send_ack(s, m, "Key deleted successfully");
827
828         return 0;
829 }
830
831 static int manager_dbdeltree(struct mansession *s, const struct message *m)
832 {
833         const char *family = astman_get_header(m, "Family");
834         const char *key = astman_get_header(m, "Key");
835         int res;
836
837         if (ast_strlen_zero(family)) {
838                 astman_send_error(s, m, "No family specified.");
839                 return 0;
840         }
841
842         if (!ast_strlen_zero(key))
843                 res = ast_db_deltree(family, key);
844         else
845                 res = ast_db_deltree(family, NULL);
846
847         if (res <= 0)
848                 astman_send_error(s, m, "Database entry not found");
849         else
850                 astman_send_ack(s, m, "Key tree deleted successfully");
851
852         return 0;
853 }
854
855 /*!
856  * \internal
857  * \brief Signal the astdb sync thread to do its thing.
858  *
859  * \note dblock is assumed to be held when calling this function.
860  */
861 static void db_sync(void)
862 {
863         ast_cond_signal(&dbcond);
864 }
865
866 /*!
867  * \internal
868  * \brief astdb sync thread
869  *
870  * This thread is in charge of syncing astdb to disk after a change.
871  * By pushing it off to this thread to take care of, this I/O bound operation
872  * will not block other threads from performing other critical processing.
873  * If changes happen rapidly, this thread will also ensure that the sync
874  * operations are rate limited.
875  */
876 static void *db_sync_thread(void *data)
877 {
878         ast_mutex_lock(&dblock);
879         ast_db_begin_transaction();
880         for (;;) {
881                 /* We're ok with spurious wakeups, so we don't worry about a predicate */
882                 ast_cond_wait(&dbcond, &dblock);
883                 if (ast_db_commit_transaction()) {
884                         ast_db_rollback_transaction();
885                 }
886                 if (doexit) {
887                         ast_mutex_unlock(&dblock);
888                         break;
889                 }
890                 ast_db_begin_transaction();
891                 ast_mutex_unlock(&dblock);
892                 sleep(1);
893                 ast_mutex_lock(&dblock);
894                 /* Unfortunately, it is possible for signaling to happen
895                  * when we're not waiting: in the bit when we're unlocked
896                  * above. Do the do-exit check here again. (We could do
897                  * it once, but that would impose a forced delay of 1
898                  * second always.) */
899                 if (doexit) {
900                         ast_mutex_unlock(&dblock);
901                         break;
902                 }
903         }
904
905         return NULL;
906 }
907
908 static void astdb_atexit(void)
909 {
910         /* Set doexit to 1 to kill thread. db_sync must be called with
911          * mutex held. */
912         doexit = 1;
913         ast_mutex_lock(&dblock);
914         db_sync();
915         ast_mutex_unlock(&dblock);
916
917         pthread_join(syncthread, NULL);
918         ast_mutex_lock(&dblock);
919         sqlite3_close(astdb);
920         ast_mutex_unlock(&dblock);
921 }
922
923 int astdb_init(void)
924 {
925         if (db_init()) {
926                 return -1;
927         }
928
929         ast_cond_init(&dbcond, NULL);
930         if (ast_pthread_create_background(&syncthread, NULL, db_sync_thread, NULL)) {
931                 return -1;
932         }
933
934         ast_register_atexit(astdb_atexit);
935         ast_cli_register_multiple(cli_database, ARRAY_LEN(cli_database));
936         ast_manager_register_xml("DBGet", EVENT_FLAG_SYSTEM | EVENT_FLAG_REPORTING, manager_dbget);
937         ast_manager_register_xml("DBPut", EVENT_FLAG_SYSTEM, manager_dbput);
938         ast_manager_register_xml("DBDel", EVENT_FLAG_SYSTEM, manager_dbdel);
939         ast_manager_register_xml("DBDelTree", EVENT_FLAG_SYSTEM, manager_dbdeltree);
940         return 0;
941 }