44 "CREATE TABLE Media (id INTEGER, key, value, source INTEGER, " 45 "intval INTEGER DEFAULT NULL)",
47 "CREATE UNIQUE INDEX key_idx ON Media (id, key, source)",
50 "CREATE TABLE Sources (id INTEGER PRIMARY KEY AUTOINCREMENT, source)",
53 "CREATE TABLE CollectionAttributes (collid INTEGER, key TEXT, value TEXT)",
55 "CREATE UNIQUE INDEX collectionattributes_idx " 56 "ON CollectionAttributes (collid, key)",
59 "CREATE TABLE CollectionConnections (from_id INTEGER, to_id INTEGER)",
61 "CREATE UNIQUE INDEX collectionconnections_idx " 62 "ON CollectionConnections (from_id, to_id)",
65 "CREATE TABLE CollectionIdlists (collid INTEGER, position INTEGER, " 68 "CREATE UNIQUE INDEX collectionidlists_idx " 69 "ON CollectionIdlists (collid, position)",
72 "CREATE TABLE CollectionLabels (collid INTEGER, namespace INTEGER, " 76 "CREATE TABLE CollectionOperators (id INTEGER PRIMARY KEY AUTOINCREMENT, " 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)",
97 "CREATE INDEX collectionlabels_idx ON CollectionLabels (collid)",
112 const 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');";
122 "INSERT INTO CollectionLabels VALUES(1, %d, 'Default');" 124 "INSERT INTO CollectionIdlists VALUES(1, 1, 1);";
126 const 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);";
139 xmms_sqlite_version_cb (
void *pArg,
int argc,
char **argv,
char **columnName)
144 *
id = atoi (argv[0]);
153 xmms_sqlite_integer_coll (
void *udata,
int len1,
const void *str1,
int len2,
const void *str2)
156 a = strtol (str1, NULL, 10);
157 b = strtol (str2, NULL, 10);
158 if (a < b)
return -1;
159 if (a == b)
return 0;
164 upgrade_v26_to_v27 (sqlite3 *sql)
170 "drop view compilations;" 178 upgrade_v27_to_v28 (sqlite3 *sql)
190 upgrade_v28_to_v29 (sqlite3 *sql)
194 sqlite3_exec (sql,
"delete from Media where source in" 195 "(select id from Sources where source like 'plugin%')",
197 sqlite3_exec (sql,
"delete from Sources where source like 'plugin%'",
199 sqlite3_exec (sql,
"update Media set value=0 where key='resolved'",
206 upgrade_v29_to_v30 (sqlite3 *sql)
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);
214 upgrade_v30_to_v31 (sqlite3 *sql)
234 upgrade_v31_to_v32 (sqlite3 *sql)
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);
244 upgrade_v32_to_v33 (sqlite3 *sql)
248 sqlite3_exec (sql,
"update CollectionOperators set type=type - 1", NULL, NULL, NULL);
253 upgrade_v33_to_v34 (sqlite3 *sql)
256 sqlite3_exec (sql,
"update CollectionAttributes set value=replace(replace(value, '%', '*'), '_', '?') WHERE collid IN (SELECT id FROM CollectionOperators WHERE type='6')", NULL, NULL, NULL);
262 upgrade_v34_to_v35 (sqlite3 *sql)
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);
275 xmms_sqlite_stringify (sqlite3_context *context,
int args, sqlite3_value **val)
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);
285 sqlite3_result_value (context, val[0]);
290 upgrade_v35_to_v36 (sqlite3 *sql)
292 XMMS_DBG (
"upgrade v35->v36 (save integers as strings also)");
295 "ADD COLUMN intval INTEGER DEFAULT NULL");
297 sqlite3_create_function (sql,
"xmms_stringify", 1, SQLITE_UTF8, NULL,
298 xmms_sqlite_stringify, NULL, NULL);
300 "SET intval = value, value = xmms_stringify (value) " 303 sqlite3_create_function (sql,
"xmms_stringify", 1, SQLITE_UTF8, NULL, NULL,
310 try_upgrade (sqlite3 *sql, gint version)
312 gboolean can_upgrade = TRUE;
316 upgrade_v26_to_v27 (sql);
318 upgrade_v27_to_v28 (sql);
320 upgrade_v28_to_v29 (sql);
322 upgrade_v29_to_v30 (sql);
324 upgrade_v30_to_v31 (sql);
326 upgrade_v31_to_v32 (sql);
328 upgrade_v32_to_v33 (sql);
330 upgrade_v33_to_v34 (sql);
332 upgrade_v34_to_v35 (sql);
334 upgrade_v35_to_v36 (sql);
350 xmms_sqlite_set_common_properties (sqlite3 *sql)
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);
358 sqlite3_busy_timeout (sql, 60000);
360 sqlite3_create_collation (sql,
"INTCOLL", SQLITE_UTF8, NULL,
361 xmms_sqlite_integer_coll);
369 gboolean analyze = FALSE;
380 if (!g_file_test (dbpath, G_FILE_TEST_EXISTS)) {
384 if (sqlite3_open (dbpath, &sql)) {
385 xmms_log_fatal (
"Error opening sqlite db: %s", sqlite3_errmsg (sql));
389 xmms_sqlite_set_common_properties (sql);
392 sqlite3_exec (sql,
"PRAGMA user_version",
393 xmms_sqlite_version_cb, &version, NULL);
395 if (version !=
DB_VERSION && !try_upgrade (sql, version)) {
401 rename (dbpath, old);
402 if (sqlite3_open (dbpath, &sql)) {
404 sqlite3_errmsg (sql));
410 xmms_sqlite_set_common_properties (sql);
418 sqlite3_exec (sql,
"ANALYZE", NULL, NULL, NULL);
431 tmp = g_path_get_dirname (dbpath);
435 xmms_log_info (
"Allowing database on remote system against best judgement.");
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);
450 XMMS_DBG (
"Creating the database...");
456 sqlite3_exec (sql,
"ANALYZE", NULL, NULL, NULL);
460 sqlite3_exec (sql,
fill_stats, NULL, NULL, NULL);
464 for (i = 0;
tables[i]; i++) {
465 sqlite3_exec (sql,
tables[i], NULL, NULL, NULL);
470 for (i = 0;
views[i]; i++) {
471 sqlite3_exec (sql,
views[i], NULL, NULL, NULL);
477 sqlite3_exec (sql,
triggers[i], NULL, NULL, NULL);
483 sqlite3_exec (sql,
indices[i], NULL, NULL, NULL);
488 sqlite3_exec (sql,
"INSERT INTO Sources (source) VALUES ('server')",
505 XMMS_DBG (
"xmms_sqlite_create done!");
522 if (sqlite3_open (dbpath, &sql)) {
523 xmms_log_fatal (
"Error opening sqlite db: %s", sqlite3_errmsg (sql));
527 g_return_val_if_fail (sql, NULL);
529 xmms_sqlite_set_common_properties (sql);
535 xmms_sqlite_column_to_val (sqlite3_stmt *stm, gint column)
539 switch (sqlite3_column_type (stm, column)) {
552 XMMS_DBG (
"Unhandled SQLite type!");
570 g_return_val_if_fail (query, FALSE);
571 g_return_val_if_fail (sql, FALSE);
573 va_start (ap, query);
575 q = sqlite3_vmprintf (query, ap);
577 ret = sqlite3_exec (sql, q, NULL, NULL, &err);
578 if (ret == SQLITE_BUSY) {
580 g_assert_not_reached ();
582 if (ret != SQLITE_OK) {
606 g_return_val_if_fail (query, FALSE);
607 g_return_val_if_fail (sql, FALSE);
609 va_start (ap, query);
610 q = sqlite3_vmprintf (query, ap);
613 ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
615 if (ret == SQLITE_BUSY) {
617 g_assert_not_reached ();
620 if (ret != SQLITE_OK) {
622 g_snprintf (err,
sizeof (err),
623 "Error in query: %s", sqlite3_errmsg (sql));
625 xmms_log_error (
"Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
630 while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
635 num = sqlite3_data_count (stm);
637 for (i = 0; i < num; i++) {
644 key = sqlite3_column_name (stm, i);
645 val = xmms_sqlite_column_to_val (stm, i);
653 if (!method (dict, udata)) {
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) {
663 }
else if (ret == SQLITE_BUSY) {
665 g_assert_not_reached ();
669 sqlite3_finalize (stm);
671 return (ret == SQLITE_DONE);
683 sqlite3_stmt *stm = NULL;
685 g_return_val_if_fail (query, FALSE);
686 g_return_val_if_fail (sql, FALSE);
688 q = sqlite3_vmprintf (query, ap);
690 ret = sqlite3_prepare (sql, q, -1, &stm, NULL);
692 if (ret == SQLITE_BUSY) {
694 g_assert_not_reached ();
697 if (ret != SQLITE_OK) {
698 xmms_log_error (
"Error %d (%s) in query '%s'", ret, sqlite3_errmsg (sql), q);
703 num_cols = sqlite3_column_count (stm);
705 row = g_new (
xmmsv_t *, num_cols + 1);
706 row[num_cols] = NULL;
708 while ((ret = sqlite3_step (stm)) == SQLITE_ROW) {
713 g_assert (num_cols == sqlite3_data_count (stm));
715 for (i = 0; i < num_cols; i++) {
716 row[i] = xmms_sqlite_column_to_val (stm, i);
719 b = method (row, udata);
721 for (i = 0; i < num_cols; i++) {
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) {
736 }
else if (ret == SQLITE_BUSY) {
741 sqlite3_finalize (stm);
743 return (ret == SQLITE_DONE);
752 va_start (ap, query);
753 r = xmms_sqlite_query_array_va (sql, method, udata, query, ap);
760 xmms_sqlite_int_cb (
xmmsv_t **row, gpointer udata)
767 XMMS_DBG (
"Expected int32 but got something else!");
778 g_return_val_if_fail (query, FALSE);
779 g_return_val_if_fail (sql, FALSE);
781 va_start (ap, query);
782 r = xmms_sqlite_query_array_va (sql, xmms_sqlite_int_cb, out, query, ap);
795 g_return_if_fail (sql);
802 printf (
" Using sqlite version %d (compiled against " 804 sqlite3_libversion_number ());
811 q = sqlite3_mprintf (
"%Q", input);
sqlite3 * xmms_sqlite_open()
Open a database or create a new one.
gboolean xmms_sqlite_create(gboolean *create)
gboolean xmms_sqlite_query_array(sqlite3 *sql, xmms_medialib_row_array_method_t method, gpointer udata, const gchar *query,...)
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.
const char create_CollectionIdlists_stm[]
void xmmsv_unref(xmmsv_t *val)
Decreases the references for the xmmsv_t When the number of references reaches 0 it will be freed...
xmms_config_property_t * xmms_config_lookup(const gchar *path)
Look up a config key from the global config.
xmmsv_t * xmmsv_new_none(void)
Allocates a new empty xmmsv_t.
const char create_CollectionConnections_stm[]
xmmsv_t * xmmsv_new_string(const char *s)
Allocates a new string xmmsv_t.
int xmmsv_get_int(const xmmsv_t *val, int32_t *r)
Retrieves a signed integer from the value.
void xmms_sqlite_print_version(void)
xmmsv_t * xmmsv_new_dict(void)
Allocates a new dict xmmsv_t.
gboolean xmms_sqlite_query_int(sqlite3 *sql, gint32 *out, const gchar *query,...)
gint xmms_config_property_get_int(const xmms_config_property_t *prop)
Return the value of a config property as an int.
const char fill_stats[]
This magic numbers are taken from ANALYZE on a big database, if we change the db layout drasticly we ...
#define xmms_log_error(fmt,...)
const char set_version_stm[]
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.
const char create_CollectionLabels_stm[]
const gchar * xmms_config_property_get_string(const xmms_config_property_t *prop)
Return the value of a config property as a string.
gboolean(* xmms_medialib_row_table_method_t)(xmmsv_t *row, gpointer udata)
const char create_CollectionOperators_stm[]
gchar * sqlite_prepare_string(const gchar *input)
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...
gboolean(* xmms_medialib_row_array_method_t)(xmmsv_t **row, gpointer udata)
xmmsv_type_t xmmsv_get_type(const xmmsv_t *val)
Get the type of the value.
xmmsv_t * xmmsv_new_int(int32_t i)
Allocates a new integer xmmsv_t.
#define XMMS_ACTIVE_PLAYLIST
#define XMMS_STRINGIFY(x)
#define xmms_log_info(fmt,...)
const char create_collidx_stm[]
#define XMMS_DBG(fmt,...)
G_BEGIN_DECLS struct xmms_error_St xmms_error_t
const char fill_init_playlist_stm[]
gboolean xmms_sqlite_exec(sqlite3 *sql, const char *query,...)
A query that can't retrieve results.
struct xmms_config_property_St xmms_config_property_t
const char create_CollectionAttributes_stm[]
#define xmms_log_fatal(fmt,...)
#define XMMS_BUILD_PATH(...)
void xmms_sqlite_close(sqlite3 *sql)
Close database and free all resources used.