2
* arch-tag: Implementation of RhythmDB libgda/SQLite database
4
* Copyright (C) 2004 Benjamin Otte <otte@gnome.org>
6
* This program is free software; you can redistribute it and/or modify
7
* it under the terms of the GNU General Public License as published by
8
* the Free Software Foundation; either version 2 of the License, or
9
* (at your option) any later version.
11
* This program 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
14
* GNU General Public License for more details.
16
* You should have received a copy of the GNU General Public License
17
* along with this program; if not, write to the Free Software
18
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
23
#include "rhythmdb-gda.h"
24
#include "rhythmdb-query-model.h"
30
static void rhythmdb_gda_class_init (RhythmDBGdaClass * klass);
31
static void rhythmdb_gda_init (RhythmDBGda * shell_player);
32
static void rhythmdb_gda_finalize (GObject * object);
34
static void rhythmdb_gda_load (RhythmDB * rdb, gboolean * die);
35
static void rhythmdb_gda_save (RhythmDB * rdb);
36
static RhythmDBEntry *rhythmdb_gda_entry_new (RhythmDB * db,
37
RhythmDBEntryType type, const char *uri);
38
static void rhythmdb_gda_entry_set (RhythmDB * db, RhythmDBEntry * entry,
39
guint propid, const GValue * value);
40
static void rhythmdb_gda_entry_get (RhythmDB * db, RhythmDBEntry * entry,
41
guint propid, GValue * value);
42
static void rhythmdb_gda_entry_delete (RhythmDB * db, RhythmDBEntry * entry);
43
static void rhythmdb_gda_entry_delete_by_type (RhythmDB * adb,
44
RhythmDBEntryType type);
45
static RhythmDBEntry *rhythmdb_gda_entry_lookup_by_location (RhythmDB * db,
47
static void rhythmdb_gda_do_full_query (RhythmDB * db, GPtrArray * query,
48
GtkTreeModel * main_model, gboolean * cancel);
49
static gboolean rhythmdb_gda_evaluate_query (RhythmDB * adb, GPtrArray * query,
50
RhythmDBEntry * aentry);
52
static GObjectClass *parent_class = NULL;
55
rhythmdb_gda_get_type (void)
57
static GType rhythmdb_gda_type = 0;
59
if (rhythmdb_gda_type == 0) {
60
static const GTypeInfo our_info = {
61
sizeof (RhythmDBGdaClass),
64
(GClassInitFunc) rhythmdb_gda_class_init,
69
(GInstanceInitFunc) rhythmdb_gda_init
72
rhythmdb_gda_type = g_type_register_static (RHYTHMDB_TYPE,
73
"RhythmDBGda", &our_info, 0);
76
return rhythmdb_gda_type;
80
rhythmdb_gda_class_init (RhythmDBGdaClass * klass)
82
GObjectClass *object_class = G_OBJECT_CLASS (klass);
83
RhythmDBClass *rhythmdb_class = RHYTHMDB_CLASS (klass);
85
parent_class = g_type_class_peek_parent (klass);
87
object_class->finalize = rhythmdb_gda_finalize;
89
rhythmdb_class->impl_load = rhythmdb_gda_load;
90
rhythmdb_class->impl_save = rhythmdb_gda_save;
91
rhythmdb_class->impl_entry_new = rhythmdb_gda_entry_new;
92
rhythmdb_class->impl_entry_set = rhythmdb_gda_entry_set;
93
rhythmdb_class->impl_entry_get = rhythmdb_gda_entry_get;
94
rhythmdb_class->impl_entry_delete = rhythmdb_gda_entry_delete;
95
rhythmdb_class->impl_entry_delete_by_type = rhythmdb_gda_entry_delete_by_type;
96
rhythmdb_class->impl_lookup_by_location =
97
rhythmdb_gda_entry_lookup_by_location;
98
rhythmdb_class->impl_evaluate_query = rhythmdb_gda_evaluate_query;
99
rhythmdb_class->impl_do_full_query = rhythmdb_gda_do_full_query;
103
rhythmdb_gda_init (RhythmDBGda * db)
106
/* FIXME: This is a hack to replace '-' with '_' because of SQL column syntax */
107
for (i = 0; i < RHYTHMDB_NUM_PROPERTIES; i++) {
108
gchar *mod = (gchar *) rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), i);
110
if (*mod == '-') *mod = '_';
115
/* we'll set up the Db in the _new function when we actually know the filename */
119
escape_string (const gchar *orig)
123
/* this is the shortest possible version. it's definitely slow */
124
strv = g_strsplit (orig, "\"", 0);
125
tmp = g_strjoinv ("\"\"", strv);
127
ret = g_strdup_printf ("\"%s\"", tmp);
134
dump_model (GdaDataModel *model)
138
for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
139
for (j = 0; j < gda_data_model_get_n_columns (model); j++) {
140
const GdaValue *value = gda_data_model_get_value_at (model, j, i);
143
gchar *str = gda_value_stringify (value);
144
g_print ("(%4u, %4u) - %s (%d)\n", i, j, str, gda_value_get_type (value));
147
g_print ("(%4u, %4u) - (NULL)\n", i, j);
153
GStaticMutex my_mutex = G_STATIC_MUTEX_INIT;
155
static GdaDataModel *
156
execute_query (RhythmDBGda *db, const gchar *query)
161
g_print ("Executing Query: %s\n", query);
162
command = gda_command_new (query, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS);
163
g_static_mutex_lock (&my_mutex);
164
model = gda_connection_execute_single_command (db->conn, command, NULL);
165
g_static_mutex_unlock (&my_mutex);
166
gda_command_free (command);
170
g_warning ("query '%s' failed", query);
177
execute_nonquery (RhythmDBGda *db, const gchar *query)
182
g_print ("Executing NonQuery: %s\n", query);
183
command = gda_command_new (query, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS);
184
g_static_mutex_lock (&my_mutex);
185
ret = gda_connection_execute_non_query (db->conn, command, NULL) != -1;
186
g_static_mutex_unlock (&my_mutex);
187
gda_command_free (command);
189
g_warning ("query '%s' failed", query);
194
#define TABLE "tracks"
196
ensure_table_exists (RhythmDBGda *db)
203
model = gda_connection_get_schema (db->conn, GDA_CONNECTION_SCHEMA_TABLES, NULL);
207
for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
208
const GdaValue *value = gda_data_model_get_value_at (model, i, 0);
209
if (g_str_equal (gda_value_get_string (value), TABLE)) {
210
g_print ("Table %s already exists. Great!\n", TABLE);
214
/* create the table */
215
s = g_string_new ("create table " TABLE " (refcount INTEGER, ");
216
for (i = 0; i < RHYTHMDB_NUM_PROPERTIES; i++) {
217
GType type = rhythmdb_get_property_type (RHYTHMDB (db), i);
219
g_string_append (s, ", ");
220
g_string_append (s, rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), i));
223
g_string_append_printf (s, " VARCHAR (200)");
226
g_string_append_printf (s, " BOOLEAN");
229
case G_TYPE_LONG: /* FIXME */
230
case G_TYPE_UINT64: /* FIXME */
231
g_string_append_printf (s, " INTEGER");
235
g_string_append_printf (s, " FLOAT");
238
g_warning ("unknown type %u", (guint) type);
239
g_assert_not_reached ();
244
if (i == RHYTHMDB_PROP_LOCATION) {
245
/* location is unique */
246
g_string_append (s, " UNIQUE");
248
g_string_append (s, ")");
249
ret = execute_nonquery (db, s->str);
250
g_string_free (s, TRUE);
252
/* refcounting with autodelete (woohoo!) */
253
ret = execute_nonquery (db, "create trigger delete_track after update of refcount on "
254
TABLE " when new.refcount = 0 begin delete from " TABLE
255
" where _rowid_ = new._rowid_; end");
261
collect_value_for_sql (const GValue *val)
265
switch (G_VALUE_TYPE (val)) {
267
value = escape_string (g_value_get_string (val));
270
value = g_strdup (g_value_get_boolean (val) ? "\"TRUE\"" : "\"FALSE\"");
273
value = g_strdup_printf ("%d", g_value_get_int (val));
276
value = g_strdup_printf ("%ld", g_value_get_long (val));
279
value = g_strdup_printf ("%"G_GUINT64_FORMAT, g_value_get_uint64 (val));
282
value = g_strdup_printf ("%f", g_value_get_float (val));
285
value = g_strdup_printf ("%g", g_value_get_double (val));
288
g_assert_not_reached ();
295
collect_value_from_sql (GValue *dest, const GdaValue *src)
299
if (gda_value_isa (src, GDA_VALUE_TYPE_NULL))
301
g_assert (gda_value_isa (src, GDA_VALUE_TYPE_STRING));
302
str = gda_value_get_string (src);
304
/* keep in sync with create table */
305
switch (G_VALUE_TYPE (dest)) {
307
g_value_set_string (dest, str);
310
g_value_set_boolean (dest, g_str_equal (str, "TRUE"));
313
g_value_set_int (dest, strtol (str, NULL, 10));
316
g_value_set_long (dest, strtol (str, NULL, 10));
319
g_value_set_uint64 (dest, strtoul (str, NULL, 10));
322
g_value_set_float (dest, atof (str));
325
g_value_set_double (dest, atof (str));
328
g_assert_not_reached ();
334
_initialize (RhythmDBGda *db)
336
/* check songs table */
337
if (!ensure_table_exists (db))
340
return execute_nonquery (db, "update " TABLE " set refcount=1");
344
rhythmdb_gda_new (const char *name)
346
RhythmDBGda *db = g_object_new (RHYTHMDB_TYPE_GDA, "name", name, NULL);
347
gchar *conn_str = g_strdup_printf ("URI=%s", name);
349
g_print ("opening Db with conn string: %s\n", conn_str);
350
db->client = gda_client_new ();
351
g_return_val_if_fail (db->client, NULL);
352
db->conn = gda_client_open_connection_from_string (db->client, "SQLite",
356
g_warning ("GDA: error opening the library");
357
g_object_unref (db->client);
361
if (!_initialize (db)) {
362
g_warning ("GDA: error initializing the library");
363
g_object_unref (db->conn);
364
g_object_unref (db->client);
369
return RHYTHMDB (db);
373
rhythmdb_gda_finalize (GObject * object)
375
RhythmDBGda *db = RHYTHMDB_GDA (object);
377
g_object_unref (db->conn);
378
g_object_unref (db->client);
380
parent_class->finalize (object);
384
rhythmdb_gda_load (RhythmDB * rdb, gboolean * die)
387
static guint types[] = { RHYTHMDB_PROP_TITLE, RHYTHMDB_PROP_ARTIST,
388
RHYTHMDB_PROP_ALBUM, RHYTHMDB_PROP_LAST_PLAYED };
389
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
390
GdaDataModel *model = execute_query (db, "select _rowid_, title, artist, album, last_played from " TABLE);
391
g_return_if_fail (model);
393
for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
394
gpointer entry = GINT_TO_POINTER ((gint) strtol (gda_value_get_string (
395
gda_data_model_get_value_at (model, 0, i)), NULL, 10));
396
for (j = 0; j < G_N_ELEMENTS (types); j++) {
398
g_value_init (&val, rhythmdb_get_property_type (rdb, types [j]));
399
collect_value_from_sql (&val, gda_data_model_get_value_at (model, j + 1, i));
400
rhythmdb_entry_sync_mirrored (rdb, entry, types [j], &val);
401
g_value_unset (&val);
403
rhythmdb_emit_entry_restored (rdb, entry);
408
rhythmdb_gda_save (RhythmDB * rdb)
410
/* nothing to do here */
413
static RhythmDBEntry *
414
rhythmdb_gda_entry_new (RhythmDB * rdb, RhythmDBEntryType type, const char *uri)
416
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
417
gchar *query = g_strdup_printf ("insert into " TABLE
418
" (type, refcount, location) values (%d, 1, \"%s\")", (gint) type, uri);
420
if (!execute_nonquery (db, query)) {
425
return rhythmdb_gda_entry_lookup_by_location (rdb, uri);
429
rhythmdb_gda_entry_set (RhythmDB * rdb, RhythmDBEntry * entry,
430
guint propid, const GValue * value)
432
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
433
gchar *collect = collect_value_for_sql (value);
434
gchar *query = g_strdup_printf ("update " TABLE " set %s = %s where _rowid_ = %d",
435
rhythmdb_nice_elt_name_from_propid (rdb, propid), collect,
436
GPOINTER_TO_INT (entry));
438
execute_nonquery (db, query);
443
rhythmdb_gda_entry_get (RhythmDB * rdb, RhythmDBEntry * entry,
444
guint propid, GValue * value)
446
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
447
gchar *query = g_strdup_printf ("select %s from " TABLE
448
" where _ROWID_ = %d", rhythmdb_nice_elt_name_from_propid (rdb, propid),
449
GPOINTER_TO_INT (entry));
450
GdaDataModel *model = execute_query (db, query);
455
if (gda_data_model_get_n_rows (model) > 0) {
456
g_assert (gda_data_model_get_n_rows (model) == 1);
457
collect_value_from_sql (value, gda_data_model_get_value_at (model, 0, 0));
459
g_object_unref (G_OBJECT (model));
463
rhythmdb_gda_ref (RhythmDBGda *db, gint id, gint count)
465
gchar *query = g_strdup_printf ("select refcount from " TABLE
466
" where _ROWID_ = %d", id);
467
GdaDataModel *model = execute_query (db, query);
472
g_assert (gda_data_model_get_n_rows (model) == 1);
473
count += strtol (gda_value_get_string (
474
gda_data_model_get_value_at (model, 0, 0)), NULL, 10);
475
g_object_unref (model);
477
query = g_strdup_printf ("update " TABLE " set refcount = %d where _ROWID_ = %d",
479
execute_nonquery (db, query);
484
rhythmdb_gda_entry_delete (RhythmDB * rdb, RhythmDBEntry * entry)
486
rhythmdb_gda_ref (RHYTHMDB_GDA (rdb), GPOINTER_TO_INT (entry), -1);
490
rhythmdb_gda_entry_delete_by_type (RhythmDB * rdb, RhythmDBEntryType type)
492
g_assert_not_reached ();
495
static RhythmDBEntry *
496
rhythmdb_gda_entry_lookup_by_location (RhythmDB * rdb, const char *uri)
499
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
500
gchar *escaped_uri = escape_string (uri);
501
gchar *query = g_strdup_printf ("select _ROWID_ from " TABLE
502
" where location = %s", escaped_uri);
503
GdaDataModel *model = execute_query (db, query);
505
g_free (escaped_uri);
507
if (!model) return NULL;
509
if (gda_data_model_get_n_rows (model) > 0) {
510
g_assert (gda_data_model_get_n_rows (model) == 1);
511
ret = GINT_TO_POINTER (strtol (gda_value_get_string (
512
gda_data_model_get_value_at (model, 0, 0)), NULL, 10));
516
g_object_unref (G_OBJECT (model));
517
g_print ("FOUND ENTRY %p\n", ret);
522
translate_query (RhythmDBGda *db, RhythmDBQueryData *data)
524
gchar *operation = NULL, *value, *ret;
526
switch (data->type) {
527
case RHYTHMDB_QUERY_DISJUNCTION:
528
case RHYTHMDB_QUERY_SUBQUERY:
529
g_assert_not_reached (); /* FIXME */
531
case RHYTHMDB_QUERY_PROP_EQUALS:
532
operation = "%s = %s";
534
case RHYTHMDB_QUERY_PROP_LIKE:
535
case RHYTHMDB_QUERY_PROP_NOT_LIKE:
536
g_assert_not_reached (); /* FIXME */
538
case RHYTHMDB_QUERY_PROP_GREATER:
539
operation = "%s > %s";
541
case RHYTHMDB_QUERY_PROP_LESS:
542
operation = "%s < %s";
544
case RHYTHMDB_QUERY_END:
546
g_assert_not_reached ();
550
value = collect_value_for_sql (data->val);
551
ret = g_strdup_printf (operation, rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), data->propid),
558
/* set rowid to 0 for all rows */
559
static GdaDataModel *
560
do_query (RhythmDBGda *db, GPtrArray * query, gint rowid)
563
RhythmDBQueryData *data;
564
GString *s = g_string_new (NULL);
568
g_assert (query->len == 1);
569
g_string_append (s, "select _ROWID_ from " TABLE " where ");
571
g_string_append_printf (s, "rowid == %d AND (", rowid);
572
for (i = 0; i < query->len; i++) {
573
data = (RhythmDBQueryData *) g_ptr_array_index (query, i);
574
tmp = translate_query (db, data);
576
g_string_append (s, " and ");
577
g_string_append (s, tmp);
581
g_string_append (s, ")");
583
model = execute_query (db, s->str);
584
g_string_free (s, TRUE);
589
rhythmdb_gda_do_full_query (RhythmDB * rdb, GPtrArray * query,
590
GtkTreeModel * main_model, gboolean * cancel)
592
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
593
GdaDataModel *model = do_query (db, query, 0);
594
g_return_if_fail (model);
601
queue = g_ptr_array_sized_new (gda_data_model_get_n_rows (model));
602
for (j = 0; j < gda_data_model_get_n_rows (model); j++) {
603
g_ptr_array_add (queue, GINT_TO_POINTER (strtol (gda_value_get_string (
604
gda_data_model_get_value_at (model, 0, j)), NULL, 10)));
606
rhythmdb_query_model_add_entries (RHYTHMDB_QUERY_MODEL (main_model), queue);
611
rhythmdb_gda_evaluate_query (RhythmDB * rdb, GPtrArray * query,
612
RhythmDBEntry * aentry)
615
RhythmDBGda *db = RHYTHMDB_GDA (rdb);
616
GdaDataModel *model = do_query (db, query, GPOINTER_TO_INT (aentry));
618
if (!model) return FALSE;
619
ret = gda_data_model_get_n_rows (model) > 0;
620
g_object_unref (model);