XMMS2
sqlite.c
Go to the documentation of this file.
1/* XMMS2 - X Music Multiplexer System
2 * Copyright (C) 2003-2011 XMMS2 Team
3 *
4 * PLUGINS ARE NOT CONSIDERED TO BE DERIVED WORK !!!
5 *
6 * This library is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This library is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 */
16
17/** @file
18 * Sqlite Backend.
19 */
20
21#include <stdio.h>
22#include <stdlib.h>
23
24#include "xmms/xmms_config.h"
25#include "xmms/xmms_log.h"
28#include "xmmspriv/xmms_utils.h"
31
32#include <sqlite3.h>
33#include <string.h>
34#include <glib.h>
35
36/* increment this whenever there are incompatible db structure changes */
37#define DB_VERSION 36
38
39const char set_version_stm[] = "PRAGMA user_version=" XMMS_STRINGIFY (DB_VERSION);
40
41/* Tables and unique constraints */
42const char *tables[] = {
43 /* Media */
44 "CREATE TABLE Media (id INTEGER, key, value, source INTEGER, "
45 "intval INTEGER DEFAULT NULL)",
46 /* Media unique constraint */
47 "CREATE UNIQUE INDEX key_idx ON Media (id, key, source)",
48
49 /* Sources */
50 "CREATE TABLE Sources (id INTEGER PRIMARY KEY AUTOINCREMENT, source)",
51
52 /* CollectionAttributes */
53 "CREATE TABLE CollectionAttributes (collid INTEGER, key TEXT, value TEXT)",
54 /* CollectionAttributes unique constraint */
55 "CREATE UNIQUE INDEX collectionattributes_idx "
56 "ON CollectionAttributes (collid, key)",
57
58 /* CollectionConnections */
59 "CREATE TABLE CollectionConnections (from_id INTEGER, to_id INTEGER)",
60 /* CollectionConnections unique constraint */
61 "CREATE UNIQUE INDEX collectionconnections_idx "
62 "ON CollectionConnections (from_id, to_id)",
63
64 /* CollectionIdlists */
65 "CREATE TABLE CollectionIdlists (collid INTEGER, position INTEGER, "
66 "mid INTEGER)",
67 /* CollectionIdlists unique constraint */
68 "CREATE UNIQUE INDEX collectionidlists_idx "
69 "ON CollectionIdlists (collid, position)",
70
71 /* CollectionLabels */
72 "CREATE TABLE CollectionLabels (collid INTEGER, namespace INTEGER, "
73 "name TEXT)",
74
75 /* CollectionOperators */
76 "CREATE TABLE CollectionOperators (id INTEGER PRIMARY KEY AUTOINCREMENT, "
77 "type INTEGER)",
78 NULL
79};
80
81const char *views[] = {
82 NULL
83};
84
85const char *triggers[] = {
86 NULL
87};
88
89const char *indices[] = {
90 /* Media idices */
91 "CREATE INDEX id_key_value_1x ON Media (id, key, value COLLATE BINARY)",
92 "CREATE INDEX id_key_value_2x ON Media (id, key, value COLLATE NOCASE)",
93 "CREATE INDEX key_value_1x ON Media (key, value COLLATE BINARY)",
94 "CREATE INDEX key_value_2x ON Media (key, value COLLATE NOCASE)",
95
96 /* Collections DAG index */
97 "CREATE INDEX collectionlabels_idx ON CollectionLabels (collid)",
98
99 NULL
100};
101
102const char create_CollectionAttributes_stm[] = "create table CollectionAttributes (collid integer, key text, value text)";
103const char create_CollectionConnections_stm[] = "create table CollectionConnections (from_id integer, to_id integer)";
104const char create_CollectionIdlists_stm[] = "create table CollectionIdlists (collid integer, position integer, mid integer)";
105const char create_CollectionLabels_stm[] = "create table CollectionLabels (collid integer, namespace integer, name text)";
106const char create_CollectionOperators_stm[] = "create table CollectionOperators (id integer primary key AUTOINCREMENT, type integer)";
107
108/**
109 * This magic numbers are taken from ANALYZE on a big database, if we change the db
110 * layout drasticly we need to redo them!
111 */
112const char fill_stats[] = "INSERT INTO sqlite_stat1 VALUES('Media', 'key_idx', '199568 14 1 1');"
113 "INSERT INTO sqlite_stat1 VALUES('Media', 'prop_idx', '199568 6653 3');"
114 "INSERT INTO sqlite_stat1 VALUES('PlaylistEntries', 'playlistentries_idx', '12784 12784 1');"
115 "INSERT INTO sqlite_stat1 VALUES('Playlist', 'playlist_idx', '2 1');"
116 "INSERT INTO sqlite_stat1 VALUES('Playlist', 'sqlite_autoindex_Playlist_1', '2 1');"
117 "INSERT INTO sqlite_stat1 VALUES('CollectionLabels', 'collectionlabels_idx', '2 2');"
118 "INSERT INTO sqlite_stat1 VALUES('CollectionIdlists', 'collectionidlists_idx', '15 15 1');"
119 "INSERT INTO sqlite_stat1 VALUES('CollectionAttributes', 'collectionattributes_idx', '2 2 1');";
120
121const char fill_init_playlist_stm[] = "INSERT INTO CollectionOperators VALUES(1, %d);"
122 "INSERT INTO CollectionLabels VALUES(1, %d, 'Default');"
123 "INSERT INTO CollectionLabels VALUES(1, %d, '" XMMS_ACTIVE_PLAYLIST "');"
124 "INSERT INTO CollectionIdlists VALUES(1, 1, 1);";
125
126const char create_collidx_stm[] = "create unique index collectionconnections_idx on CollectionConnections (from_id, to_id);"
127 "create unique index collectionattributes_idx on CollectionAttributes (collid, key);"
128 "create unique index collectionidlists_idx on CollectionIdlists (collid, position);"
129 "create index collectionlabels_idx on CollectionLabels (collid);";
130
131/**
132 * @defgroup SQLite SQLite
133 * @ingroup XMMSServer
134 * @brief The SQLite backend of medialib
135 * @{
136 */
137
138static int
139xmms_sqlite_version_cb (void *pArg, int argc, char **argv, char **columnName)
140{
141 guint *id = pArg;
142
143 if (argv[0]) {
144 *id = atoi (argv[0]);
145 } else {
146 *id = 0;
147 }
148
149 return 0;
150}
151
152static int
153xmms_sqlite_integer_coll (void *udata, int len1, const void *str1, int len2, const void *str2)
154{
155 gint32 a, b;
156 a = strtol (str1, NULL, 10);
157 b = strtol (str2, NULL, 10);
158 if (a < b) return -1;
159 if (a == b) return 0;
160 return 1;
161}
162
163static void
164upgrade_v26_to_v27 (sqlite3 *sql)
165{
166 XMMS_DBG ("Upgrade v26->v27");
167 sqlite3_exec (sql,
168 "drop view albums;"
169 "drop view artists;"
170 "drop view compilations;"
171 "drop view songs;",
172 NULL, NULL, NULL);
173
174 XMMS_DBG ("done");
175}
176
177static void
178upgrade_v27_to_v28 (sqlite3 *sql)
179{
180 XMMS_DBG ("Upgrade v27->v28");
181
182 sqlite3_exec (sql,
183 "drop table Log;",
184 NULL, NULL, NULL);
185
186 XMMS_DBG ("done");
187}
188
189static void
190upgrade_v28_to_v29 (sqlite3 *sql)
191{
192 XMMS_DBG ("Upgrade v28->v29");
193
194 sqlite3_exec (sql, "delete from Media where source in"
195 "(select id from Sources where source like 'plugin%')",
196 NULL, NULL, NULL);
197 sqlite3_exec (sql, "delete from Sources where source like 'plugin%'",
198 NULL, NULL, NULL);
199 sqlite3_exec (sql, "update Media set value=0 where key='resolved'",
200 NULL, NULL, NULL);
201
202 XMMS_DBG ("done");
203}
204
205static void
206upgrade_v29_to_v30 (sqlite3 *sql)
207{
208 XMMS_DBG ("Upgrade v29->v30");
209 sqlite3_exec (sql, "insert into Media (id, key, value, source) select distinct id, 'available', 1, (select id from Sources where source='server') from Media", NULL, NULL, NULL);
210 XMMS_DBG ("done");
211}
212
213static void
214upgrade_v30_to_v31 (sqlite3 *sql)
215{
216 XMMS_DBG ("Upgrade v30->v31");
217
218 sqlite3_exec (sql, create_CollectionAttributes_stm, NULL, NULL, NULL);
219 sqlite3_exec (sql, create_CollectionConnections_stm, NULL, NULL, NULL);
220 sqlite3_exec (sql, create_CollectionIdlists_stm, NULL, NULL, NULL);
221 sqlite3_exec (sql, create_CollectionLabels_stm, NULL, NULL, NULL);
222 sqlite3_exec (sql, create_CollectionOperators_stm, NULL, NULL, NULL);
223 sqlite3_exec (sql, create_collidx_stm, NULL, NULL, NULL);
224
225 /* Create a default playlist */
229
230 XMMS_DBG ("done");
231}
232
233static void
234upgrade_v31_to_v32 (sqlite3 *sql)
235{
236 XMMS_DBG ("upgrade v31->v32");
237 sqlite3_exec (sql, "delete from Media where id = (select id from Media where key='available' and value=0)", NULL, NULL, NULL);
238 sqlite3_exec (sql, "delete from Media where key='available' and source = 1", NULL, NULL, NULL);
239 sqlite3_exec (sql, "update media set key='status' where key='resolved' and source = 1", NULL, NULL, NULL);
240 XMMS_DBG ("done");
241}
242
243static void
244upgrade_v32_to_v33 (sqlite3 *sql)
245{
246 /* Decrement collection type id, as we removed ERROR from the enum. */
247 XMMS_DBG ("upgrade v32->v33");
248 sqlite3_exec (sql, "update CollectionOperators set type=type - 1", NULL, NULL, NULL);
249 XMMS_DBG ("done");
250}
251
252static void
253upgrade_v33_to_v34 (sqlite3 *sql)
254{
255 XMMS_DBG ("upgrade v33->v34");
256 sqlite3_exec (sql, "update CollectionAttributes set value=replace(replace(value, '%', '*'), '_', '?') WHERE collid IN (SELECT id FROM CollectionOperators WHERE type='6')", NULL, NULL, NULL);
257 XMMS_DBG ("done");
258}
259
260
261static void
262upgrade_v34_to_v35 (sqlite3 *sql)
263{
264 XMMS_DBG ("upgrade v34->v35");
265 sqlite3_exec (sql, "DROP INDEX prop_idx;"
266 "CREATE INDEX id_key_value_1x ON Media (id, key, value COLLATE BINARY);"
267 "CREATE INDEX id_key_value_2x ON Media (id, key, value COLLATE NOCASE);"
268 "CREATE INDEX key_value_1x ON Media (key, value COLLATE BINARY);"
269 "CREATE INDEX key_value_2x ON Media (key, value COLLATE NOCASE);"
270 "UPDATE CollectionAttributes SET value=replace(replace(value, '%', '*'), '_', '?') WHERE collid IN (SELECT id FROM CollectionOperators WHERE type='6');", NULL, NULL, NULL);
271 XMMS_DBG ("done");
272}
273
274static void
275xmms_sqlite_stringify (sqlite3_context *context, int args, sqlite3_value **val)
276{
277 gint i;
278 gchar buffer[32];
279
280 if (sqlite3_value_type (val[0]) == SQLITE_INTEGER) {
281 i = sqlite3_value_int (val[0]);
282 sprintf (buffer, "%d", i);
283 sqlite3_result_text (context, buffer, -1, SQLITE_TRANSIENT);
284 } else {
285 sqlite3_result_value (context, val[0]);
286 }
287}
288
289static void
290upgrade_v35_to_v36 (sqlite3 *sql)
291{
292 XMMS_DBG ("upgrade v35->v36 (save integers as strings also)");
293
294 xmms_sqlite_exec (sql, "ALTER TABLE Media "
295 "ADD COLUMN intval INTEGER DEFAULT NULL");
296
297 sqlite3_create_function (sql, "xmms_stringify", 1, SQLITE_UTF8, NULL,
298 xmms_sqlite_stringify, NULL, NULL);
299 xmms_sqlite_exec (sql, "UPDATE Media "
300 "SET intval = value, value = xmms_stringify (value) "
301 "WHERE value < ''",
302 NULL, NULL, NULL);
303 sqlite3_create_function (sql, "xmms_stringify", 1, SQLITE_UTF8, NULL, NULL,
304 NULL, NULL);
305
306 XMMS_DBG ("done");
307}
308
309static gboolean
310try_upgrade (sqlite3 *sql, gint version)
311{
312 gboolean can_upgrade = TRUE;
313
314 switch (version) {
315 case 26:
316 upgrade_v26_to_v27 (sql);
317 case 27:
318 upgrade_v27_to_v28 (sql);
319 case 28:
320 upgrade_v28_to_v29 (sql);
321 case 29:
322 upgrade_v29_to_v30 (sql);
323 case 30:
324 upgrade_v30_to_v31 (sql);
325 case 31:
326 upgrade_v31_to_v32 (sql);
327 case 32:
328 upgrade_v32_to_v33 (sql);
329 case 33:
330 upgrade_v33_to_v34 (sql);
331 case 34:
332 upgrade_v34_to_v35 (sql);
333 case 35:
334 upgrade_v35_to_v36 (sql);
335 break; /* remember to (re)move this! We want fallthrough */
336 default:
337 can_upgrade = FALSE;
338 break;
339 }
340
341 if (can_upgrade) {
342 /* store the new version in the database */
343 sqlite3_exec (sql, set_version_stm, NULL, NULL, NULL);
344 }
345
346 return can_upgrade;
347}
348
349static void
350xmms_sqlite_set_common_properties (sqlite3 *sql)
351{
352 sqlite3_exec (sql, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
353 sqlite3_exec (sql, "PRAGMA auto_vacuum = 1", NULL, NULL, NULL);
354 sqlite3_exec (sql, "PRAGMA cache_size = 8000", NULL, NULL, NULL);
355 sqlite3_exec (sql, "PRAGMA temp_store = MEMORY", NULL, NULL, NULL);
356
357 /* One minute */
358 sqlite3_busy_timeout (sql, 60000);
359
360 sqlite3_create_collation (sql, "INTCOLL", SQLITE_UTF8, NULL,
361 xmms_sqlite_integer_coll);
362}
363
364gboolean
365xmms_sqlite_create (gboolean *create)
366{
368 gchar *tmp;
369 gboolean analyze = FALSE;
370 const gchar *dbpath;
371 gint version = 0;
372 sqlite3 *sql;
373 guint i;
374
375 *create = FALSE;
376
377 cv = xmms_config_lookup ("medialib.path");
379
380 if (!g_file_test (dbpath, G_FILE_TEST_EXISTS)) {
381 *create = TRUE;
382 }
383
384 if (sqlite3_open (dbpath, &sql)) {
385 xmms_log_fatal ("Error opening sqlite db: %s", sqlite3_errmsg (sql));
386 return FALSE;
387 }
388
389 xmms_sqlite_set_common_properties (sql);
390
391 if (!*create) {
392 sqlite3_exec (sql, "PRAGMA user_version",
393 xmms_sqlite_version_cb, &version, NULL);
394
395 if (version != DB_VERSION && !try_upgrade (sql, version)) {
396 gchar *old;
397
398 sqlite3_close (sql);
399
400 old = XMMS_BUILD_PATH ("medialib.db.old");
401 rename (dbpath, old);
402 if (sqlite3_open (dbpath, &sql)) {
403 xmms_log_fatal ("Error creating sqlite db: %s",
404 sqlite3_errmsg (sql));
405 g_free (old);
406 return FALSE;
407 }
408 g_free (old);
409
410 xmms_sqlite_set_common_properties (sql);
411 *create = TRUE;
412 }
413
414 cv = xmms_config_lookup ("medialib.analyze_on_startup");
415 analyze = xmms_config_property_get_int (cv);
416 if (analyze) {
417 xmms_log_info ("Analyzing db, please wait a few seconds");
418 sqlite3_exec (sql, "ANALYZE", NULL, NULL, NULL);
419 xmms_log_info ("Done with analyze");
420 }
421 }
422
423 if (*create) {
424 /* Check if we are about to put the medialib on a
425 * remote filesystem. They are known to work less
426 * well with sqlite and therefore we should refuse
427 * to do so. The user has to know that he is doing
428 * something stupid
429 */
430
431 tmp = g_path_get_dirname (dbpath);
432 if (xmms_statfs_is_remote (tmp)) {
433 cv = xmms_config_lookup ("medialib.allow_remote_fs");
434 if (xmms_config_property_get_int (cv) == 1) {
435 xmms_log_info ("Allowing database on remote system against best judgement.");
436 } else {
437 xmms_log_fatal ("Remote filesystem detected!\n"
438 "* It looks like you are putting your database: %s\n"
439 "* on a remote filesystem, this is a bad idea since there are many known bugs\n"
440 "* with SQLite on some remote filesystems. We recomend that you put the db\n"
441 "* somewhere else. You can do this by editing the xmms2.conf and find the\n"
442 "* property for medialib.path. If you however still want to try to run the\n"
443 "* db on a remote filesystem please set medialib.allow_remote_fs=1 in your\n"
444 "* config and restart xmms2d.", dbpath);
445 }
446 }
447
448 g_free (tmp);
449
450 XMMS_DBG ("Creating the database...");
451 /**
452 * This will create the sqlite_stats1 table which we
453 * fill out with good information about our indexes.
454 * Thanks to drh for these pointers!
455 */
456 sqlite3_exec (sql, "ANALYZE", NULL, NULL, NULL);
457 /**
458 * Fill out sqlite_stats1
459 */
460 sqlite3_exec (sql, fill_stats, NULL, NULL, NULL);
461 /**
462 * Create the tables and unique constraints
463 */
464 for (i = 0; tables[i]; i++) {
465 sqlite3_exec (sql, tables[i], NULL, NULL, NULL);
466 }
467 /**
468 * Create the views
469 */
470 for (i = 0; views[i]; i++) {
471 sqlite3_exec (sql, views[i], NULL, NULL, NULL);
472 }
473 /**
474 * Create the triggers
475 */
476 for (i = 0; triggers[i]; i++) {
477 sqlite3_exec (sql, triggers[i], NULL, NULL, NULL);
478 }
479 /**
480 * Create indices
481 */
482 for (i = 0; indices[i]; i++) {
483 sqlite3_exec (sql, indices[i], NULL, NULL, NULL);
484 }
485 /**
486 * Add the server source
487 */
488 sqlite3_exec (sql, "INSERT INTO Sources (source) VALUES ('server')",
489 NULL, NULL, NULL);
490 /**
491 * Create a default playlist
492 */
497 /**
498 * Set database version
499 */
500 sqlite3_exec (sql, set_version_stm, NULL, NULL, NULL);
501 }
502
503 sqlite3_close (sql);
504
505 XMMS_DBG ("xmms_sqlite_create done!");
506 return TRUE;
507}
508
509/**
510 * Open a database or create a new one
511 */
512sqlite3 *
514{
515 sqlite3 *sql;
516 const gchar *dbpath;
518
519 cv = xmms_config_lookup ("medialib.path");
521
522 if (sqlite3_open (dbpath, &sql)) {
523 xmms_log_fatal ("Error opening sqlite db: %s", sqlite3_errmsg (sql));
524 return NULL;
525 }
526
527 g_return_val_if_fail (sql, NULL);
528
529 xmms_sqlite_set_common_properties (sql);
530
531 return sql;
532}
533
534static xmmsv_t *
535xmms_sqlite_column_to_val (sqlite3_stmt *stm, gint column)
536{
537 xmmsv_t *val = NULL;
538
539 switch (sqlite3_column_type (stm, column)) {
540 case SQLITE_INTEGER:
541 case SQLITE_FLOAT:
542 val = xmmsv_new_int (sqlite3_column_int (stm, column));
543 break;
544 case SQLITE_TEXT:
545 case SQLITE_BLOB:
546 val = xmmsv_new_string ((gchar *)sqlite3_column_text (stm, column));
547 break;
548 case SQLITE_NULL:
549 val = xmmsv_new_none ();
550 break;
551 default:
552 XMMS_DBG ("Unhandled SQLite type!");
553 break;
554 }
555
556 return val;
557
558}
559
560/**
561 * A query that can't retrieve results
562 */
563gboolean
564xmms_sqlite_exec (sqlite3 *sql, const char *query, ...)
565{
566 gchar *q, *err;
567 va_list ap;
568 gint ret;
569
570 g_return_val_if_fail (query, FALSE);
571 g_return_val_if_fail (sql, FALSE);
572
573 va_start (ap, query);
574
575 q = sqlite3_vmprintf (query, ap);
576
577 ret = sqlite3_exec (sql, q, NULL, NULL, &err);
578 if (ret == SQLITE_BUSY) {
579 xmms_log_fatal ("BUSY EVENT!");
580 g_assert_not_reached ();
581 }
582 if (ret != SQLITE_OK) {
583 xmms_log_error ("Error in query! \"%s\" (%d) - %s", q, ret, err);
584 sqlite3_free (q);
585 va_end (ap);
586 return FALSE;
587 }
588
589 sqlite3_free (q);
590 va_end (ap);
591
592 return TRUE;
593}
594
595/**
596 * Execute a query to the database.
597 */
598gboolean
599xmms_sqlite_query_table (sqlite3 *sql, xmms_medialib_row_table_method_t method, gpointer udata, xmms_error_t *error, const gchar *query, ...)
600{
601 gchar *q;
602 va_list ap;
603 gint ret;
604 sqlite3_stmt *stm;
605
606 g_return_val_if_fail (query, FALSE);
607 g_return_val_if_fail (sql, FALSE);
608
609 va_start (ap, query);
610 q = sqlite3_vmprintf (query, ap);
611 va_end (ap);
612
613 ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
614
615 if (ret == SQLITE_BUSY) {
616 xmms_log_fatal ("BUSY EVENT!");
617 g_assert_not_reached ();
618 }
619
620 if (ret != SQLITE_OK) {
621 gchar err[256];
622 g_snprintf (err, sizeof (err),
623 "Error in query: %s", sqlite3_errmsg (sql));
624 xmms_error_set (error, XMMS_ERROR_GENERIC, err);
625 xmms_log_error ("Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
626 sqlite3_free (q);
627 return FALSE;
628 }
629
630 while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
631 gint num, i;
632 xmmsv_t *dict;
633
634 dict = xmmsv_new_dict ();
635 num = sqlite3_data_count (stm);
636
637 for (i = 0; i < num; i++) {
638 const char *key;
639 xmmsv_t *val;
640
641 /* We don't need to strdup the key because xmmsv_dict_set
642 * will create its own copy.
643 */
644 key = sqlite3_column_name (stm, i);
645 val = xmms_sqlite_column_to_val (stm, i);
646
647 xmmsv_dict_set (dict, key, val);
648
649 /* The dictionary owns the value. */
650 xmmsv_unref (val);
651 }
652
653 if (!method (dict, udata)) {
654 break;
655 }
656
657 }
658
659 if (ret == SQLITE_ERROR) {
660 xmms_log_error ("SQLite Error code %d (%s) on query '%s'", ret, sqlite3_errmsg (sql), q);
661 } else if (ret == SQLITE_MISUSE) {
662 xmms_log_error ("SQLite api misuse on query '%s'", q);
663 } else if (ret == SQLITE_BUSY) {
664 xmms_log_error ("SQLite busy on query '%s'", q);
665 g_assert_not_reached ();
666 }
667
668 sqlite3_free (q);
669 sqlite3_finalize (stm);
670
671 return (ret == SQLITE_DONE);
672}
673
674/**
675 * Execute a query to the database.
676 */
677static gboolean
678xmms_sqlite_query_array_va (sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query, va_list ap)
679{
680 gchar *q;
681 gint ret, num_cols;
682 xmmsv_t **row;
683 sqlite3_stmt *stm = NULL;
684
685 g_return_val_if_fail (query, FALSE);
686 g_return_val_if_fail (sql, FALSE);
687
688 q = sqlite3_vmprintf (query, ap);
689
690 ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
691
692 if (ret == SQLITE_BUSY) {
693 xmms_log_fatal ("BUSY EVENT!");
694 g_assert_not_reached ();
695 }
696
697 if (ret != SQLITE_OK) {
698 xmms_log_error ("Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
699 sqlite3_free (q);
700 return FALSE;
701 }
702
703 num_cols = sqlite3_column_count (stm);
704
705 row = g_new (xmmsv_t *, num_cols + 1);
706 row[num_cols] = NULL;
707
708 while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
709 gint i;
710 gboolean b;
711
712 /* I'm a bit paranoid */
713 g_assert (num_cols == sqlite3_data_count (stm));
714
715 for (i = 0; i < num_cols; i++) {
716 row[i] = xmms_sqlite_column_to_val (stm, i);
717 }
718
719 b = method (row, udata);
720
721 for (i = 0; i < num_cols; i++) {
722 xmmsv_unref (row[i]);
723 }
724
725 if (!b) {
726 break;
727 }
728 }
729
730 g_free (row);
731
732 if (ret == SQLITE_ERROR) {
733 xmms_log_error ("SQLite Error code %d (%s) on query '%s'", ret, sqlite3_errmsg (sql), q);
734 } else if (ret == SQLITE_MISUSE) {
735 xmms_log_error ("SQLite api misuse on query '%s'", q);
736 } else if (ret == SQLITE_BUSY) {
737 xmms_log_error ("SQLite busy on query '%s'", q);
738 }
739
740 sqlite3_free (q);
741 sqlite3_finalize (stm);
742
743 return (ret == SQLITE_DONE);
744}
745
746gboolean
747xmms_sqlite_query_array (sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query, ...)
748{
749 va_list ap;
750 gboolean r;
751
752 va_start (ap, query);
753 r = xmms_sqlite_query_array_va (sql, method, udata, query, ap);
754 va_end (ap);
755
756 return r;
757}
758
759static gboolean
760xmms_sqlite_int_cb (xmmsv_t **row, gpointer udata)
761{
762 gint *i = udata;
763
764 if (row && row[0] && xmmsv_get_type (row[0]) == XMMSV_TYPE_INT32)
765 xmmsv_get_int (row[0], i);
766 else
767 XMMS_DBG ("Expected int32 but got something else!");
768
769 return TRUE;
770}
771
772gboolean
773xmms_sqlite_query_int (sqlite3 *sql, gint32 *out, const gchar *query, ...)
774{
775 va_list ap;
776 gboolean r;
777
778 g_return_val_if_fail (query, FALSE);
779 g_return_val_if_fail (sql, FALSE);
780
781 va_start (ap, query);
782 r = xmms_sqlite_query_array_va (sql, xmms_sqlite_int_cb, out, query, ap);
783 va_end (ap);
784
785 return r;
786}
787
788
789/**
790 * Close database and free all resources used.
791 */
792void
793xmms_sqlite_close (sqlite3 *sql)
794{
795 g_return_if_fail (sql);
796 sqlite3_close (sql);
797}
798
799void
801{
802 printf (" Using sqlite version %d (compiled against "
803 XMMS_STRINGIFY (SQLITE_VERSION_NUMBER) ")\n",
804 sqlite3_libversion_number ());
805}
806
807/* Return an escaped string */
808gchar *
809sqlite_prepare_string (const gchar *input) {
810 gchar *q, *str;
811 q = sqlite3_mprintf ("%Q", input);
812 str = g_strdup (q);
813 sqlite3_free (q);
814 return str;
815}
816
817/** @} */
gint xmms_config_property_get_int(const xmms_config_property_t *prop)
Return the value of a config property as an int.
Definition: config.c:255
const gchar * xmms_config_property_get_string(const xmms_config_property_t *prop)
Return the value of a config property as a string.
Definition: config.c:243
xmms_config_property_t * xmms_config_lookup(const gchar *path)
Look up a config key from the global config.
Definition: config.c:171
int xmmsv_dict_set(xmmsv_t *dictv, const char *key, xmmsv_t *val)
Insert an element under the given key in the dict xmmsv_t.
Definition: value.c:1752
xmmsv_t * xmmsv_new_dict(void)
Allocates a new dict xmmsv_t.
Definition: value.c:268
gboolean xmms_sqlite_exec(sqlite3 *sql, const char *query,...)
A query that can't retrieve results.
Definition: sqlite.c:564
gchar * sqlite_prepare_string(const gchar *input)
Definition: sqlite.c:809
gboolean xmms_sqlite_query_int(sqlite3 *sql, gint32 *out, const gchar *query,...)
Definition: sqlite.c:773
gboolean xmms_sqlite_query_table(sqlite3 *sql, xmms_medialib_row_table_method_t method, gpointer udata, xmms_error_t *error, const gchar *query,...)
Execute a query to the database.
Definition: sqlite.c:599
void xmms_sqlite_print_version(void)
Definition: sqlite.c:800
gboolean xmms_sqlite_create(gboolean *create)
Definition: sqlite.c:365
gboolean xmms_sqlite_query_array(sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query,...)
Definition: sqlite.c:747
sqlite3 * xmms_sqlite_open()
Open a database or create a new one.
Definition: sqlite.c:513
void xmms_sqlite_close(sqlite3 *sql)
Close database and free all resources used.
Definition: sqlite.c:793
void xmmsv_unref(xmmsv_t *val)
Decreases the references for the xmmsv_t When the number of references reaches 0 it will be freed.
Definition: value.c:303
xmmsv_t * xmmsv_new_none(void)
Allocates a new empty xmmsv_t.
Definition: value.c:129
xmmsv_t * xmmsv_new_string(const char *s)
Allocates a new string xmmsv_t.
Definition: value.c:180
struct xmmsv_St xmmsv_t
Definition: xmmsv_general.h:48
int xmmsv_get_int(const xmmsv_t *val, int32_t *r)
Retrieves a signed integer from the value.
Definition: value.c:823
xmmsv_type_t xmmsv_get_type(const xmmsv_t *val)
Get the type of the value.
Definition: value.c:392
xmmsv_t * xmmsv_new_int(int32_t i)
Allocates a new integer xmmsv_t.
Definition: value.c:161
@ XMMSV_TYPE_INT32
Definition: xmmsv_general.h:38
const char * views[]
Definition: sqlite.c:81
const char create_CollectionAttributes_stm[]
Definition: sqlite.c:102
const char set_version_stm[]
Definition: sqlite.c:39
#define DB_VERSION
Definition: sqlite.c:37
const char * tables[]
Definition: sqlite.c:42
const char * triggers[]
Definition: sqlite.c:85
const char fill_init_playlist_stm[]
Definition: sqlite.c:121
const char create_collidx_stm[]
Definition: sqlite.c:126
const char create_CollectionIdlists_stm[]
Definition: sqlite.c:104
const char create_CollectionConnections_stm[]
Definition: sqlite.c:103
const char fill_stats[]
This magic numbers are taken from ANALYZE on a big database, if we change the db layout drasticly we ...
Definition: sqlite.c:112
const char create_CollectionOperators_stm[]
Definition: sqlite.c:106
const char * indices[]
Definition: sqlite.c:89
const char create_CollectionLabels_stm[]
Definition: sqlite.c:105
gboolean xmms_statfs_is_remote(const gchar *path)
This function uses the statfs() call to check if the path is on a remote filesystem or not.
Definition: statfs_bsd.c:42
struct xmms_config_property_St xmms_config_property_t
Definition: xmms_config.h:26
#define xmms_log_error(fmt,...)
Definition: xmms_log.h:35
#define xmms_log_info(fmt,...)
Definition: xmms_log.h:34
#define XMMS_DBG(fmt,...)
Definition: xmms_log.h:32
#define xmms_log_fatal(fmt,...)
Definition: xmms_log.h:33
@ XMMS_COLLECTION_NSID_PLAYLISTS
G_BEGIN_DECLS struct xmms_error_St xmms_error_t
gboolean(* xmms_medialib_row_table_method_t)(xmmsv_t *row, gpointer udata)
Definition: xmms_sqlite.h:27
gboolean(* xmms_medialib_row_array_method_t)(xmmsv_t **row, gpointer udata)
Definition: xmms_sqlite.h:26
#define XMMS_BUILD_PATH(...)
Definition: xmms_utils.h:4
@ XMMS_ERROR_GENERIC
#define XMMS_ACTIVE_PLAYLIST
@ XMMS_COLLECTION_TYPE_IDLIST
#define XMMS_STRINGIFY(x)
Definition: xmmsc_util.h:10