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"
26 #include "xmmspriv/xmms_sqlite.h"
27 #include "xmmspriv/xmms_statfs.h"
28 #include "xmmspriv/xmms_utils.h"
30 #include "xmmsc/xmmsc_idnumbers.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 
39 const char set_version_stm[] = "PRAGMA user_version=" XMMS_STRINGIFY (DB_VERSION);
40 
41 /* Tables and unique constraints */
42 const 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 
81 const char *views[] = {
82  NULL
83 };
84 
85 const char *triggers[] = {
86  NULL
87 };
88 
89 const 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 
102 const char create_CollectionAttributes_stm[] = "create table CollectionAttributes (collid integer, key text, value text)";
103 const char create_CollectionConnections_stm[] = "create table CollectionConnections (from_id integer, to_id integer)";
104 const char create_CollectionIdlists_stm[] = "create table CollectionIdlists (collid integer, position integer, mid integer)";
105 const char create_CollectionLabels_stm[] = "create table CollectionLabels (collid integer, namespace integer, name text)";
106 const 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  */
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');";
120 
121 const 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 
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);";
130 
131 /**
132  * @defgroup SQLite SQLite
133  * @ingroup XMMSServer
134  * @brief The SQLite backend of medialib
135  * @{
136  */
137 
138 static int
139 xmms_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 
152 static int
153 xmms_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 
163 static void
164 upgrade_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 
177 static void
178 upgrade_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 
189 static void
190 upgrade_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 
205 static void
206 upgrade_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 
213 static void
214 upgrade_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 
233 static void
234 upgrade_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 
243 static void
244 upgrade_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 
252 static void
253 upgrade_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 
261 static void
262 upgrade_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 
274 static void
275 xmms_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 
289 static void
290 upgrade_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 
309 static gboolean
310 try_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 
349 static void
350 xmms_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 
364 gboolean
365 xmms_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");
378  dbpath = xmms_config_property_get_string (cv);
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  */
512 sqlite3 *
514 {
515  sqlite3 *sql;
516  const gchar *dbpath;
518 
519  cv = xmms_config_lookup ("medialib.path");
520  dbpath = xmms_config_property_get_string (cv);
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 
534 static xmmsv_t *
535 xmms_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  */
563 gboolean
564 xmms_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  */
598 gboolean
599 xmms_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  */
677 static gboolean
678 xmms_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 
746 gboolean
747 xmms_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 
759 static gboolean
760 xmms_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 
772 gboolean
773 xmms_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  */
792 void
793 xmms_sqlite_close (sqlite3 *sql)
794 {
795  g_return_if_fail (sql);
796  sqlite3_close (sql);
797 }
798 
799 void
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 */
808 gchar *
809 sqlite_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 /** @} */
const char * indices[]
Definition: sqlite.c:89
sqlite3 * xmms_sqlite_open()
Open a database or create a new one.
Definition: sqlite.c:513
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
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
const char create_CollectionIdlists_stm[]
Definition: sqlite.c:104
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
struct xmmsv_St xmmsv_t
Definition: xmmsv_general.h:48
xmms_config_property_t * xmms_config_lookup(const gchar *path)
Look up a config key from the global config.
Definition: config.c:171
xmmsv_t * xmmsv_new_none(void)
Allocates a new empty xmmsv_t.
Definition: value.c:129
const char * tables[]
Definition: sqlite.c:42
const char create_CollectionConnections_stm[]
Definition: sqlite.c:103
xmmsv_t * xmmsv_new_string(const char *s)
Allocates a new string xmmsv_t.
Definition: value.c:180
int xmmsv_get_int(const xmmsv_t *val, int32_t *r)
Retrieves a signed integer from the value.
Definition: value.c:823
void xmms_sqlite_print_version(void)
Definition: sqlite.c:800
xmmsv_t * xmmsv_new_dict(void)
Allocates a new dict xmmsv_t.
Definition: value.c:268
const char * triggers[]
Definition: sqlite.c:85
gboolean xmms_sqlite_query_int(sqlite3 *sql, gint32 *out, const gchar *query,...)
Definition: sqlite.c:773
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 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
#define xmms_log_error(fmt,...)
Definition: xmms_log.h:35
const char set_version_stm[]
Definition: sqlite.c:39
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
const char create_CollectionLabels_stm[]
Definition: sqlite.c:105
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
gboolean(* xmms_medialib_row_table_method_t)(xmmsv_t *row, gpointer udata)
Definition: xmms_sqlite.h:27
const char create_CollectionOperators_stm[]
Definition: sqlite.c:106
gchar * sqlite_prepare_string(const gchar *input)
Definition: sqlite.c:809
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
gboolean(* xmms_medialib_row_array_method_t)(xmmsv_t **row, gpointer udata)
Definition: xmms_sqlite.h:26
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
#define XMMS_ACTIVE_PLAYLIST
#define XMMS_STRINGIFY(x)
Definition: xmmsc_util.h:10
#define xmms_log_info(fmt,...)
Definition: xmms_log.h:34
#define DB_VERSION
Definition: sqlite.c:37
const char create_collidx_stm[]
Definition: sqlite.c:126
#define XMMS_DBG(fmt,...)
Definition: xmms_log.h:32
G_BEGIN_DECLS struct xmms_error_St xmms_error_t
const char fill_init_playlist_stm[]
Definition: sqlite.c:121
gboolean xmms_sqlite_exec(sqlite3 *sql, const char *query,...)
A query that can&#39;t retrieve results.
Definition: sqlite.c:564
struct xmms_config_property_St xmms_config_property_t
Definition: xmms_config.h:26
const char create_CollectionAttributes_stm[]
Definition: sqlite.c:102
#define xmms_log_fatal(fmt,...)
Definition: xmms_log.h:33
#define XMMS_BUILD_PATH(...)
Definition: xmms_utils.h:4
void xmms_sqlite_close(sqlite3 *sql)
Close database and free all resources used.
Definition: sqlite.c:793
const char * views[]
Definition: sqlite.c:81