XMMS2
|
00001 /* XMMS2 - X Music Multiplexer System 00002 * Copyright (C) 2003-2009 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 /** @} */