XMMS2
src/xmms/sqlite.c
Go to the documentation of this file.
00001 /*  XMMS2 - X Music Multiplexer System
00002  *  Copyright (C) 2003-2011 XMMS2 Team
00003  *
00004  *  PLUGINS ARE NOT CONSIDERED TO BE DERIVED WORK !!!
00005  *
00006  *  This library is free software; you can redistribute it and/or
00007  *  modify it under the terms of the GNU Lesser General Public
00008  *  License as published by the Free Software Foundation; either
00009  *  version 2.1 of the License, or (at your option) any later version.
00010  *
00011  *  This library is distributed in the hope that it will be useful,
00012  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00013  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014  *  Lesser General Public License for more details.
00015  */
00016 
00017 /** @file
00018  * Sqlite Backend.
00019  */
00020 
00021 #include <stdio.h>
00022 #include <stdlib.h>
00023 
00024 #include "xmms/xmms_config.h"
00025 #include "xmms/xmms_log.h"
00026 #include "xmmspriv/xmms_sqlite.h"
00027 #include "xmmspriv/xmms_statfs.h"
00028 #include "xmmspriv/xmms_utils.h"
00029 #include "xmmspriv/xmms_collection.h"
00030 #include "xmmsc/xmmsc_idnumbers.h"
00031 
00032 #include <sqlite3.h>
00033 #include <string.h>
00034 #include <glib.h>
00035 
00036 /* increment this whenever there are incompatible db structure changes */
00037 #define DB_VERSION 36
00038 
00039 const char set_version_stm[] = "PRAGMA user_version=" XMMS_STRINGIFY (DB_VERSION);
00040 
00041 /* Tables and unique constraints */
00042 const char *tables[] = {
00043     /* Media */
00044     "CREATE TABLE Media (id INTEGER, key, value, source INTEGER, "
00045                         "intval INTEGER DEFAULT NULL)",
00046     /* Media unique constraint */
00047     "CREATE UNIQUE INDEX key_idx ON Media (id, key, source)",
00048 
00049     /* Sources */
00050     "CREATE TABLE Sources (id INTEGER PRIMARY KEY AUTOINCREMENT, source)",
00051 
00052     /* CollectionAttributes */
00053     "CREATE TABLE CollectionAttributes (collid INTEGER, key TEXT, value TEXT)",
00054     /* CollectionAttributes unique constraint */
00055     "CREATE UNIQUE INDEX collectionattributes_idx "
00056            "ON CollectionAttributes (collid, key)",
00057 
00058     /* CollectionConnections */
00059     "CREATE TABLE CollectionConnections (from_id INTEGER, to_id INTEGER)",
00060     /* CollectionConnections unique constraint */
00061     "CREATE UNIQUE INDEX collectionconnections_idx "
00062            "ON CollectionConnections (from_id, to_id)",
00063 
00064     /* CollectionIdlists */
00065     "CREATE TABLE CollectionIdlists (collid INTEGER, position INTEGER, "
00066                                     "mid INTEGER)",
00067     /* CollectionIdlists unique constraint */
00068     "CREATE UNIQUE INDEX collectionidlists_idx "
00069            "ON CollectionIdlists (collid, position)",
00070 
00071     /* CollectionLabels */
00072     "CREATE TABLE CollectionLabels (collid INTEGER, namespace INTEGER, "
00073                                    "name TEXT)",
00074 
00075     /* CollectionOperators */
00076     "CREATE TABLE CollectionOperators (id INTEGER PRIMARY KEY AUTOINCREMENT, "
00077                                       "type INTEGER)",
00078     NULL
00079 };
00080 
00081 const char *views[] = {
00082     NULL
00083 };
00084 
00085 const char *triggers[] = {
00086     NULL
00087 };
00088 
00089 const char *indices[] = {
00090     /* Media idices */
00091     "CREATE INDEX id_key_value_1x ON Media (id, key, value COLLATE BINARY)",
00092     "CREATE INDEX id_key_value_2x ON Media (id, key, value COLLATE NOCASE)",
00093     "CREATE INDEX key_value_1x ON Media (key, value COLLATE BINARY)",
00094     "CREATE INDEX key_value_2x ON Media (key, value COLLATE NOCASE)",
00095 
00096     /* Collections DAG index */
00097     "CREATE INDEX collectionlabels_idx ON CollectionLabels (collid)",
00098 
00099     NULL
00100 };
00101 
00102 const char create_CollectionAttributes_stm[] = "create table CollectionAttributes (collid integer, key text, value text)";
00103 const char create_CollectionConnections_stm[] = "create table CollectionConnections (from_id integer, to_id integer)";
00104 const char create_CollectionIdlists_stm[] = "create table CollectionIdlists (collid integer, position integer, mid integer)";
00105 const char create_CollectionLabels_stm[] = "create table CollectionLabels (collid integer, namespace integer, name text)";
00106 const char create_CollectionOperators_stm[] = "create table CollectionOperators (id integer primary key AUTOINCREMENT, type integer)";
00107 
00108 /**
00109  * This magic numbers are taken from ANALYZE on a big database, if we change the db
00110  * layout drasticly we need to redo them!
00111  */
00112 const char fill_stats[] = "INSERT INTO sqlite_stat1 VALUES('Media', 'key_idx', '199568 14 1 1');"
00113                           "INSERT INTO sqlite_stat1 VALUES('Media', 'prop_idx', '199568 6653 3');"
00114                           "INSERT INTO sqlite_stat1 VALUES('PlaylistEntries', 'playlistentries_idx', '12784 12784 1');"
00115                           "INSERT INTO sqlite_stat1 VALUES('Playlist', 'playlist_idx', '2 1');"
00116                           "INSERT INTO sqlite_stat1 VALUES('Playlist', 'sqlite_autoindex_Playlist_1', '2 1');"
00117                           "INSERT INTO sqlite_stat1 VALUES('CollectionLabels', 'collectionlabels_idx', '2 2');"
00118                           "INSERT INTO sqlite_stat1 VALUES('CollectionIdlists', 'collectionidlists_idx', '15 15 1');"
00119                           "INSERT INTO sqlite_stat1 VALUES('CollectionAttributes', 'collectionattributes_idx', '2 2 1');";
00120 
00121 const char fill_init_playlist_stm[] = "INSERT INTO CollectionOperators VALUES(1, %d);"
00122                                       "INSERT INTO CollectionLabels VALUES(1, %d, 'Default');"
00123                                       "INSERT INTO CollectionLabels VALUES(1, %d, '" XMMS_ACTIVE_PLAYLIST "');"
00124                                       "INSERT INTO CollectionIdlists VALUES(1, 1, 1);";
00125 
00126 const char create_collidx_stm[] = "create unique index collectionconnections_idx on CollectionConnections (from_id, to_id);"
00127                                   "create unique index collectionattributes_idx on CollectionAttributes (collid, key);"
00128                                   "create unique index collectionidlists_idx on CollectionIdlists (collid, position);"
00129                                   "create index collectionlabels_idx on CollectionLabels (collid);";
00130 
00131 /**
00132  * @defgroup SQLite SQLite
00133  * @ingroup XMMSServer
00134  * @brief The SQLite backend of medialib
00135  * @{
00136  */
00137 
00138 static int
00139 xmms_sqlite_version_cb (void *pArg, int argc, char **argv, char **columnName)
00140 {
00141     guint *id = pArg;
00142 
00143     if (argv[0]) {
00144         *id = atoi (argv[0]);
00145     } else {
00146         *id = 0;
00147     }
00148 
00149     return 0;
00150 }
00151 
00152 static int
00153 xmms_sqlite_integer_coll (void *udata, int len1, const void *str1, int len2, const void *str2)
00154 {
00155     gint32 a, b;
00156     a = strtol (str1, NULL, 10);
00157     b = strtol (str2, NULL, 10);
00158     if (a < b) return -1;
00159     if (a == b) return 0;
00160     return 1;
00161 }
00162 
00163 static void
00164 upgrade_v26_to_v27 (sqlite3 *sql)
00165 {
00166     XMMS_DBG ("Upgrade v26->v27");
00167     sqlite3_exec (sql,
00168                   "drop view albums;"
00169                   "drop view artists;"
00170                   "drop view compilations;"
00171                   "drop view songs;",
00172                   NULL, NULL, NULL);
00173 
00174     XMMS_DBG ("done");
00175 }
00176 
00177 static void
00178 upgrade_v27_to_v28 (sqlite3 *sql)
00179 {
00180     XMMS_DBG ("Upgrade v27->v28");
00181 
00182     sqlite3_exec (sql,
00183                   "drop table Log;",
00184                   NULL, NULL, NULL);
00185 
00186     XMMS_DBG ("done");
00187 }
00188 
00189 static void
00190 upgrade_v28_to_v29 (sqlite3 *sql)
00191 {
00192     XMMS_DBG ("Upgrade v28->v29");
00193 
00194     sqlite3_exec (sql, "delete from Media where source in"
00195                   "(select id from Sources where source like 'plugin%')",
00196                   NULL, NULL, NULL);
00197     sqlite3_exec (sql, "delete from Sources where source like 'plugin%'",
00198                   NULL, NULL, NULL);
00199     sqlite3_exec (sql, "update Media set value=0 where key='resolved'",
00200                   NULL, NULL, NULL);
00201 
00202     XMMS_DBG ("done");
00203 }
00204 
00205 static void
00206 upgrade_v29_to_v30 (sqlite3 *sql)
00207 {
00208     XMMS_DBG ("Upgrade v29->v30");
00209     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);
00210     XMMS_DBG ("done");
00211 }
00212 
00213 static void
00214 upgrade_v30_to_v31 (sqlite3 *sql)
00215 {
00216     XMMS_DBG ("Upgrade v30->v31");
00217 
00218     sqlite3_exec (sql, create_CollectionAttributes_stm, NULL, NULL, NULL);
00219     sqlite3_exec (sql, create_CollectionConnections_stm, NULL, NULL, NULL);
00220     sqlite3_exec (sql, create_CollectionIdlists_stm, NULL, NULL, NULL);
00221     sqlite3_exec (sql, create_CollectionLabels_stm, NULL, NULL, NULL);
00222     sqlite3_exec (sql, create_CollectionOperators_stm, NULL, NULL, NULL);
00223     sqlite3_exec (sql, create_collidx_stm, NULL, NULL, NULL);
00224 
00225     /* Create a default playlist */
00226     xmms_sqlite_exec (sql, fill_init_playlist_stm, XMMS_COLLECTION_TYPE_IDLIST,
00227                                                    XMMS_COLLECTION_NSID_PLAYLISTS,
00228                                                    XMMS_COLLECTION_NSID_PLAYLISTS);
00229 
00230     XMMS_DBG ("done");
00231 }
00232 
00233 static void
00234 upgrade_v31_to_v32 (sqlite3 *sql)
00235 {
00236     XMMS_DBG ("upgrade v31->v32");
00237     sqlite3_exec (sql, "delete from Media where id = (select id from Media where key='available' and value=0)", NULL, NULL, NULL);
00238     sqlite3_exec (sql, "delete from Media where key='available' and source = 1", NULL, NULL, NULL);
00239     sqlite3_exec (sql, "update media set key='status' where key='resolved' and source = 1", NULL, NULL, NULL);
00240     XMMS_DBG ("done");
00241 }
00242 
00243 static void
00244 upgrade_v32_to_v33 (sqlite3 *sql)
00245 {
00246     /* Decrement collection type id, as we removed ERROR from the enum. */
00247     XMMS_DBG ("upgrade v32->v33");
00248     sqlite3_exec (sql, "update CollectionOperators set type=type - 1", NULL, NULL, NULL);
00249     XMMS_DBG ("done");
00250 }
00251 
00252 static void
00253 upgrade_v33_to_v34 (sqlite3 *sql)
00254 {
00255     XMMS_DBG ("upgrade v33->v34");
00256     sqlite3_exec (sql, "update CollectionAttributes set value=replace(replace(value, '%', '*'), '_', '?') WHERE collid IN (SELECT id FROM CollectionOperators WHERE type='6')", NULL, NULL, NULL);
00257     XMMS_DBG ("done");
00258 }
00259 
00260 
00261 static void
00262 upgrade_v34_to_v35 (sqlite3 *sql)
00263 {
00264     XMMS_DBG ("upgrade v34->v35");
00265     sqlite3_exec (sql, "DROP INDEX prop_idx;"
00266                        "CREATE INDEX id_key_value_1x ON Media (id, key, value COLLATE BINARY);"
00267                        "CREATE INDEX id_key_value_2x ON Media (id, key, value COLLATE NOCASE);"
00268                        "CREATE INDEX key_value_1x ON Media (key, value COLLATE BINARY);"
00269                        "CREATE INDEX key_value_2x ON Media (key, value COLLATE NOCASE);"
00270                        "UPDATE CollectionAttributes SET value=replace(replace(value, '%', '*'), '_', '?') WHERE collid IN (SELECT id FROM CollectionOperators WHERE type='6');", NULL, NULL, NULL);
00271     XMMS_DBG ("done");
00272 }
00273 
00274 static void
00275 xmms_sqlite_stringify (sqlite3_context *context, int args, sqlite3_value **val)
00276 {
00277     gint i;
00278     gchar buffer[32];
00279 
00280     if (sqlite3_value_type (val[0]) == SQLITE_INTEGER) {
00281         i = sqlite3_value_int (val[0]);
00282         sprintf (buffer, "%d", i);
00283         sqlite3_result_text (context, buffer, -1, SQLITE_TRANSIENT);
00284     } else {
00285         sqlite3_result_value (context, val[0]);
00286     }
00287 }
00288 
00289 static void
00290 upgrade_v35_to_v36 (sqlite3 *sql)
00291 {
00292     XMMS_DBG ("upgrade v35->v36 (save integers as strings also)");
00293 
00294     xmms_sqlite_exec (sql, "ALTER TABLE Media "
00295                            "ADD COLUMN intval INTEGER DEFAULT NULL");
00296 
00297     sqlite3_create_function (sql, "xmms_stringify", 1, SQLITE_UTF8, NULL,
00298                              xmms_sqlite_stringify, NULL, NULL);
00299     xmms_sqlite_exec (sql, "UPDATE Media "
00300                            "SET intval = value, value = xmms_stringify (value) "
00301                            "WHERE value < ''",
00302                       NULL, NULL, NULL);
00303     sqlite3_create_function (sql, "xmms_stringify", 1, SQLITE_UTF8, NULL, NULL,
00304                              NULL, NULL);
00305 
00306     XMMS_DBG ("done");
00307 }
00308 
00309 static gboolean
00310 try_upgrade (sqlite3 *sql, gint version)
00311 {
00312     gboolean can_upgrade = TRUE;
00313 
00314     switch (version) {
00315         case 26:
00316             upgrade_v26_to_v27 (sql);
00317         case 27:
00318             upgrade_v27_to_v28 (sql);
00319         case 28:
00320             upgrade_v28_to_v29 (sql);
00321         case 29:
00322             upgrade_v29_to_v30 (sql);
00323         case 30:
00324             upgrade_v30_to_v31 (sql);
00325         case 31:
00326             upgrade_v31_to_v32 (sql);
00327         case 32:
00328             upgrade_v32_to_v33 (sql);
00329         case 33:
00330             upgrade_v33_to_v34 (sql);
00331         case 34:
00332             upgrade_v34_to_v35 (sql);
00333         case 35:
00334             upgrade_v35_to_v36 (sql);
00335             break; /* remember to (re)move this! We want fallthrough */
00336         default:
00337             can_upgrade = FALSE;
00338             break;
00339     }
00340 
00341     if (can_upgrade) {
00342         /* store the new version in the database */
00343         sqlite3_exec (sql, set_version_stm, NULL, NULL, NULL);
00344     }
00345 
00346     return can_upgrade;
00347 }
00348 
00349 static void
00350 xmms_sqlite_set_common_properties (sqlite3 *sql)
00351 {
00352     sqlite3_exec (sql, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
00353     sqlite3_exec (sql, "PRAGMA auto_vacuum = 1", NULL, NULL, NULL);
00354     sqlite3_exec (sql, "PRAGMA cache_size = 8000", NULL, NULL, NULL);
00355     sqlite3_exec (sql, "PRAGMA temp_store = MEMORY", NULL, NULL, NULL);
00356 
00357     /* One minute */
00358     sqlite3_busy_timeout (sql, 60000);
00359 
00360     sqlite3_create_collation (sql, "INTCOLL", SQLITE_UTF8, NULL,
00361                               xmms_sqlite_integer_coll);
00362 }
00363 
00364 gboolean
00365 xmms_sqlite_create (gboolean *create)
00366 {
00367     xmms_config_property_t *cv;
00368     gchar *tmp;
00369     gboolean analyze = FALSE;
00370     const gchar *dbpath;
00371     gint version = 0;
00372     sqlite3 *sql;
00373     guint i;
00374 
00375     *create = FALSE;
00376 
00377     cv = xmms_config_lookup ("medialib.path");
00378     dbpath = xmms_config_property_get_string (cv);
00379 
00380     if (!g_file_test (dbpath, G_FILE_TEST_EXISTS)) {
00381         *create = TRUE;
00382     }
00383 
00384     if (sqlite3_open (dbpath, &sql)) {
00385         xmms_log_fatal ("Error opening sqlite db: %s", sqlite3_errmsg (sql));
00386         return FALSE;
00387     }
00388 
00389     xmms_sqlite_set_common_properties (sql);
00390 
00391     if (!*create) {
00392         sqlite3_exec (sql, "PRAGMA user_version",
00393                       xmms_sqlite_version_cb, &version, NULL);
00394 
00395         if (version != DB_VERSION && !try_upgrade (sql, version)) {
00396             gchar *old;
00397 
00398             sqlite3_close (sql);
00399 
00400             old = XMMS_BUILD_PATH ("medialib.db.old");
00401             rename (dbpath, old);
00402             if (sqlite3_open (dbpath, &sql)) {
00403                 xmms_log_fatal ("Error creating sqlite db: %s",
00404                                 sqlite3_errmsg (sql));
00405                 g_free (old);
00406                 return FALSE;
00407             }
00408             g_free (old);
00409 
00410             xmms_sqlite_set_common_properties (sql);
00411             *create = TRUE;
00412         }
00413 
00414         cv = xmms_config_lookup ("medialib.analyze_on_startup");
00415         analyze = xmms_config_property_get_int (cv);
00416         if (analyze) {
00417             xmms_log_info ("Analyzing db, please wait a few seconds");
00418             sqlite3_exec (sql, "ANALYZE", NULL, NULL, NULL);
00419             xmms_log_info ("Done with analyze");
00420         }
00421     }
00422 
00423     if (*create) {
00424         /* Check if we are about to put the medialib on a
00425          * remote filesystem. They are known to work less
00426          * well with sqlite and therefore we should refuse
00427          * to do so. The user has to know that he is doing
00428          * something stupid
00429          */
00430 
00431         tmp = g_path_get_dirname (dbpath);
00432         if (xmms_statfs_is_remote (tmp)) {
00433             cv = xmms_config_lookup ("medialib.allow_remote_fs");
00434             if (xmms_config_property_get_int (cv) == 1) {
00435                 xmms_log_info ("Allowing database on remote system against best judgement.");
00436             } else {
00437                 xmms_log_fatal ("Remote filesystem detected!\n"
00438                                 "* It looks like you are putting your database: %s\n"
00439                                 "* on a remote filesystem, this is a bad idea since there are many known bugs\n"
00440                                 "* with SQLite on some remote filesystems. We recomend that you put the db\n"
00441                                 "* somewhere else. You can do this by editing the xmms2.conf and find the\n"
00442                                 "* property for medialib.path. If you however still want to try to run the\n"
00443                                 "* db on a remote filesystem please set medialib.allow_remote_fs=1 in your\n"
00444                                 "* config and restart xmms2d.", dbpath);
00445             }
00446         }
00447 
00448         g_free (tmp);
00449 
00450         XMMS_DBG ("Creating the database...");
00451         /**
00452          * This will create the sqlite_stats1 table which we
00453          * fill out with good information about our indexes.
00454          * Thanks to drh for these pointers!
00455          */
00456         sqlite3_exec (sql, "ANALYZE", NULL, NULL, NULL);
00457         /**
00458          * Fill out sqlite_stats1
00459          */
00460         sqlite3_exec (sql, fill_stats, NULL, NULL, NULL);
00461         /**
00462          * Create the tables and unique constraints
00463          */
00464         for (i = 0; tables[i]; i++) {
00465             sqlite3_exec (sql, tables[i], NULL, NULL, NULL);
00466         }
00467         /**
00468          * Create the views
00469          */
00470         for (i = 0; views[i]; i++) {
00471             sqlite3_exec (sql, views[i], NULL, NULL, NULL);
00472         }
00473         /**
00474          * Create the triggers
00475          */
00476         for (i = 0; triggers[i]; i++) {
00477             sqlite3_exec (sql, triggers[i], NULL, NULL, NULL);
00478         }
00479         /**
00480          * Create indices
00481          */
00482         for (i = 0; indices[i]; i++) {
00483             sqlite3_exec (sql, indices[i], NULL, NULL, NULL);
00484         }
00485         /**
00486          * Add the server source
00487          */
00488         sqlite3_exec (sql, "INSERT INTO Sources (source) VALUES ('server')",
00489                       NULL, NULL, NULL);
00490         /**
00491          * Create a default playlist
00492          */
00493         xmms_sqlite_exec (sql, fill_init_playlist_stm,
00494                           XMMS_COLLECTION_TYPE_IDLIST,
00495                           XMMS_COLLECTION_NSID_PLAYLISTS,
00496                           XMMS_COLLECTION_NSID_PLAYLISTS);
00497         /**
00498          * Set database version
00499          */
00500         sqlite3_exec (sql, set_version_stm, NULL, NULL, NULL);
00501     }
00502 
00503     sqlite3_close (sql);
00504 
00505     XMMS_DBG ("xmms_sqlite_create done!");
00506     return TRUE;
00507 }
00508 
00509 /**
00510  * Open a database or create a new one
00511  */
00512 sqlite3 *
00513 xmms_sqlite_open ()
00514 {
00515     sqlite3 *sql;
00516     const gchar *dbpath;
00517     xmms_config_property_t *cv;
00518 
00519     cv = xmms_config_lookup ("medialib.path");
00520     dbpath = xmms_config_property_get_string (cv);
00521 
00522     if (sqlite3_open (dbpath, &sql)) {
00523         xmms_log_fatal ("Error opening sqlite db: %s", sqlite3_errmsg (sql));
00524         return NULL;
00525     }
00526 
00527     g_return_val_if_fail (sql, NULL);
00528 
00529     xmms_sqlite_set_common_properties (sql);
00530 
00531     return sql;
00532 }
00533 
00534 static xmmsv_t *
00535 xmms_sqlite_column_to_val (sqlite3_stmt *stm, gint column)
00536 {
00537     xmmsv_t *val = NULL;
00538 
00539     switch (sqlite3_column_type (stm, column)) {
00540         case SQLITE_INTEGER:
00541         case SQLITE_FLOAT:
00542             val = xmmsv_new_int (sqlite3_column_int (stm, column));
00543             break;
00544         case SQLITE_TEXT:
00545         case SQLITE_BLOB:
00546             val = xmmsv_new_string ((gchar *)sqlite3_column_text (stm, column));
00547             break;
00548         case SQLITE_NULL:
00549             val = xmmsv_new_none ();
00550             break;
00551         default:
00552             XMMS_DBG ("Unhandled SQLite type!");
00553             break;
00554     }
00555 
00556     return val;
00557 
00558 }
00559 
00560 /**
00561  * A query that can't retrieve results
00562  */
00563 gboolean
00564 xmms_sqlite_exec (sqlite3 *sql, const char *query, ...)
00565 {
00566     gchar *q, *err;
00567     va_list ap;
00568     gint ret;
00569 
00570     g_return_val_if_fail (query, FALSE);
00571     g_return_val_if_fail (sql, FALSE);
00572 
00573     va_start (ap, query);
00574 
00575     q = sqlite3_vmprintf (query, ap);
00576 
00577     ret = sqlite3_exec (sql, q, NULL, NULL, &err);
00578     if (ret == SQLITE_BUSY) {
00579         xmms_log_fatal ("BUSY EVENT!");
00580         g_assert_not_reached ();
00581     }
00582     if (ret != SQLITE_OK) {
00583         xmms_log_error ("Error in query! \"%s\" (%d) - %s", q, ret, err);
00584         sqlite3_free (q);
00585         va_end (ap);
00586         return FALSE;
00587     }
00588 
00589     sqlite3_free (q);
00590     va_end (ap);
00591 
00592     return TRUE;
00593 }
00594 
00595 /**
00596  * Execute a query to the database.
00597  */
00598 gboolean
00599 xmms_sqlite_query_table (sqlite3 *sql, xmms_medialib_row_table_method_t method, gpointer udata, xmms_error_t *error, const gchar *query, ...)
00600 {
00601     gchar *q;
00602     va_list ap;
00603     gint ret;
00604     sqlite3_stmt *stm;
00605 
00606     g_return_val_if_fail (query, FALSE);
00607     g_return_val_if_fail (sql, FALSE);
00608 
00609     va_start (ap, query);
00610     q = sqlite3_vmprintf (query, ap);
00611     va_end (ap);
00612 
00613     ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
00614 
00615     if (ret == SQLITE_BUSY) {
00616         xmms_log_fatal ("BUSY EVENT!");
00617         g_assert_not_reached ();
00618     }
00619 
00620     if (ret != SQLITE_OK) {
00621         gchar err[256];
00622         g_snprintf (err, sizeof (err),
00623                     "Error in query: %s", sqlite3_errmsg (sql));
00624         xmms_error_set (error, XMMS_ERROR_GENERIC, err);
00625         xmms_log_error ("Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
00626         sqlite3_free (q);
00627         return FALSE;
00628     }
00629 
00630     while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
00631         gint num, i;
00632         xmmsv_t *dict;
00633 
00634         dict = xmmsv_new_dict ();
00635         num = sqlite3_data_count (stm);
00636 
00637         for (i = 0; i < num; i++) {
00638             const char *key;
00639             xmmsv_t *val;
00640 
00641             /* We don't need to strdup the key because xmmsv_dict_set
00642              * will create its own copy.
00643              */
00644             key = sqlite3_column_name (stm, i);
00645             val = xmms_sqlite_column_to_val (stm, i);
00646 
00647             xmmsv_dict_set (dict, key, val);
00648 
00649             /* The dictionary owns the value. */
00650             xmmsv_unref (val);
00651         }
00652 
00653         if (!method (dict, udata)) {
00654             break;
00655         }
00656 
00657     }
00658 
00659     if (ret == SQLITE_ERROR) {
00660         xmms_log_error ("SQLite Error code %d (%s) on query '%s'", ret, sqlite3_errmsg (sql), q);
00661     } else if (ret == SQLITE_MISUSE) {
00662         xmms_log_error ("SQLite api misuse on query '%s'", q);
00663     } else if (ret == SQLITE_BUSY) {
00664         xmms_log_error ("SQLite busy on query '%s'", q);
00665         g_assert_not_reached ();
00666     }
00667 
00668     sqlite3_free (q);
00669     sqlite3_finalize (stm);
00670 
00671     return (ret == SQLITE_DONE);
00672 }
00673 
00674 /**
00675  * Execute a query to the database.
00676  */
00677 static gboolean
00678 xmms_sqlite_query_array_va (sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query, va_list ap)
00679 {
00680     gchar *q;
00681     gint ret, num_cols;
00682     xmmsv_t **row;
00683     sqlite3_stmt *stm = NULL;
00684 
00685     g_return_val_if_fail (query, FALSE);
00686     g_return_val_if_fail (sql, FALSE);
00687 
00688     q = sqlite3_vmprintf (query, ap);
00689 
00690     ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
00691 
00692     if (ret == SQLITE_BUSY) {
00693         xmms_log_fatal ("BUSY EVENT!");
00694         g_assert_not_reached ();
00695     }
00696 
00697     if (ret != SQLITE_OK) {
00698         xmms_log_error ("Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
00699         sqlite3_free (q);
00700         return FALSE;
00701     }
00702 
00703     num_cols = sqlite3_column_count (stm);
00704 
00705     row = g_new (xmmsv_t *, num_cols + 1);
00706     row[num_cols] = NULL;
00707 
00708     while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
00709         gint i;
00710         gboolean b;
00711 
00712         /* I'm a bit paranoid */
00713         g_assert (num_cols == sqlite3_data_count (stm));
00714 
00715         for (i = 0; i < num_cols; i++) {
00716             row[i] = xmms_sqlite_column_to_val (stm, i);
00717         }
00718 
00719         b = method (row, udata);
00720 
00721         for (i = 0; i < num_cols; i++) {
00722             xmmsv_unref (row[i]);
00723         }
00724 
00725         if (!b) {
00726             break;
00727         }
00728     }
00729 
00730     g_free (row);
00731 
00732     if (ret == SQLITE_ERROR) {
00733         xmms_log_error ("SQLite Error code %d (%s) on query '%s'", ret, sqlite3_errmsg (sql), q);
00734     } else if (ret == SQLITE_MISUSE) {
00735         xmms_log_error ("SQLite api misuse on query '%s'", q);
00736     } else if (ret == SQLITE_BUSY) {
00737         xmms_log_error ("SQLite busy on query '%s'", q);
00738     }
00739 
00740     sqlite3_free (q);
00741     sqlite3_finalize (stm);
00742 
00743     return (ret == SQLITE_DONE);
00744 }
00745 
00746 gboolean
00747 xmms_sqlite_query_array (sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query, ...)
00748 {
00749     va_list ap;
00750     gboolean r;
00751 
00752     va_start (ap, query);
00753     r = xmms_sqlite_query_array_va (sql, method, udata, query, ap);
00754     va_end (ap);
00755 
00756     return r;
00757 }
00758 
00759 static gboolean
00760 xmms_sqlite_int_cb (xmmsv_t **row, gpointer udata)
00761 {
00762     gint *i = udata;
00763 
00764     if (row && row[0] && xmmsv_get_type (row[0]) == XMMSV_TYPE_INT32)
00765         xmmsv_get_int (row[0], i);
00766     else
00767         XMMS_DBG ("Expected int32 but got something else!");
00768 
00769     return TRUE;
00770 }
00771 
00772 gboolean
00773 xmms_sqlite_query_int (sqlite3 *sql, gint32 *out, const gchar *query, ...)
00774 {
00775     va_list ap;
00776     gboolean r;
00777 
00778     g_return_val_if_fail (query, FALSE);
00779     g_return_val_if_fail (sql, FALSE);
00780 
00781     va_start (ap, query);
00782     r = xmms_sqlite_query_array_va (sql, xmms_sqlite_int_cb, out, query, ap);
00783     va_end (ap);
00784 
00785     return r;
00786 }
00787 
00788 
00789 /**
00790  * Close database and free all resources used.
00791  */
00792 void
00793 xmms_sqlite_close (sqlite3 *sql)
00794 {
00795     g_return_if_fail (sql);
00796     sqlite3_close (sql);
00797 }
00798 
00799 void
00800 xmms_sqlite_print_version (void)
00801 {
00802     printf (" Using sqlite version %d (compiled against "
00803             XMMS_STRINGIFY (SQLITE_VERSION_NUMBER) ")\n",
00804             sqlite3_libversion_number ());
00805 }
00806 
00807 /* Return an escaped string */
00808 gchar *
00809 sqlite_prepare_string (const gchar *input) {
00810     gchar *q, *str;
00811     q = sqlite3_mprintf ("%Q", input);
00812     str = g_strdup (q);
00813     sqlite3_free (q);
00814     return str;
00815 }
00816 
00817 /** @} */