rhythmdb/rhythmdb-gda.c

   1 /*
   2  *  arch-tag: Implementation of RhythmDB libgda/SQLite database
   3  *
   4  *  Copyright (C) 2004 Benjamin Otte <otte@gnome.org>
   5  *
   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.
  10  *
  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.
  15  *
  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.
  19  *
  20  */
  21
  22 #include <config.h>
  23 #include "rhythmdb-gda.h"
  24 #include "rhythmdb-query-model.h"
  25
  26 #if 0
  27 #define g_print(...)
  28 #endif
  29
  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);
  33
  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,
  46     const char *uri);
  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);
  51
  52 G_DEFINE_TYPE (RhythmDBGda, rhythmdb_gda, RHYTHMDB_TYPE)
  53
  54
  55 static void
  56 rhythmdb_gda_class_init (RhythmDBGdaClass * klass)
  57 {
  58   GObjectClass *object_class = G_OBJECT_CLASS (klass);
  59   RhythmDBClass *rhythmdb_class = RHYTHMDB_CLASS (klass);
  60
  61
  62   object_class->finalize = rhythmdb_gda_finalize;
  63
  64   rhythmdb_class->impl_load = rhythmdb_gda_load;
  65   rhythmdb_class->impl_save = rhythmdb_gda_save;
  66   rhythmdb_class->impl_entry_new = rhythmdb_gda_entry_new;
  67   rhythmdb_class->impl_entry_set = rhythmdb_gda_entry_set;
  68   rhythmdb_class->impl_entry_get = rhythmdb_gda_entry_get;
  69   rhythmdb_class->impl_entry_delete = rhythmdb_gda_entry_delete;
  70   rhythmdb_class->impl_entry_delete_by_type = rhythmdb_gda_entry_delete_by_type;
  71   rhythmdb_class->impl_lookup_by_location =
  72       rhythmdb_gda_entry_lookup_by_location;
  73   rhythmdb_class->impl_evaluate_query = rhythmdb_gda_evaluate_query;
  74   rhythmdb_class->impl_do_full_query = rhythmdb_gda_do_full_query;
  75 }
  76
  77 static void
  78 rhythmdb_gda_init (RhythmDBGda * db)
  79 {
  80   gint i;
  81   /* FIXME: This is a hack to replace '-' with '_' because of SQL column syntax */
  82   for (i = 0; i < RHYTHMDB_NUM_PROPERTIES; i++) {
  83     gchar *mod = (gchar *) rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), i);
  84     while (*mod) {
  85       if (*mod == '-') *mod = '_';
  86       mod ++;
  87     }
  88   }
  89
  90   /* we'll set up the Db in the _new function when we actually know the filename */
  91 }
  92
  93 static gchar *
  94 escape_string (const gchar *orig)
  95 {
  96   gchar **strv;
  97   gchar *ret, *tmp;
  98   /* this is the shortest possible version. it's definitely slow */
  99   strv = g_strsplit (orig, "\"", 0);
 100   tmp = g_strjoinv ("\"\"", strv);
 101   g_strfreev (strv);
 102   ret = g_strdup_printf ("\"%s\"", tmp);
 103   g_free (tmp);
 104   return ret;
 105 }
 106
 107 /* debugging */
 108 static void
 109 dump_model (GdaDataModel *model)
 110 {
 111   guint i, j;
 112
 113   for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
 114     for (j = 0; j < gda_data_model_get_n_columns (model); j++) {
 115       const GdaValue *value = gda_data_model_get_value_at (model, j, i);
 116
 117       if (value) {
 118         gchar *str = gda_value_stringify (value);
 119         g_print ("(%4u, %4u) - %s (%d)\n", i, j, str, gda_value_get_type (value));
 120         g_free (str);
 121       } else {
 122         g_print ("(%4u, %4u) - (NULL)\n", i, j);
 123       }
 124     }
 125   }
 126 }
 127
 128 GStaticMutex my_mutex = G_STATIC_MUTEX_INIT;
 129
 130 static GdaDataModel *
 131 execute_query (RhythmDBGda *db, const gchar *query)
 132 {
 133   GdaDataModel *model;
 134   GdaCommand *command;
 135
 136   g_print ("Executing Query:    %s\n", query);
 137   command = gda_command_new (query, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS);
 138   g_static_mutex_lock (&my_mutex);
 139   model = gda_connection_execute_single_command (db->conn, command, NULL);
 140   g_static_mutex_unlock (&my_mutex);
 141   gda_command_free (command);
 142   if (model) {
 143     dump_model (model);
 144   } else {
 145     g_warning ("query '%s' failed", query);
 146   }
 147
 148   return model;
 149 }
 150
 151 static gboolean
 152 execute_nonquery (RhythmDBGda *db, const gchar *query)
 153 {
 154   gboolean ret;
 155   GdaCommand *command;
 156
 157   g_print ("Executing NonQuery: %s\n", query);
 158   command = gda_command_new (query, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS);
 159   g_static_mutex_lock (&my_mutex);
 160   ret = gda_connection_execute_non_query (db->conn, command, NULL) != -1;
 161   g_static_mutex_unlock (&my_mutex);
 162   gda_command_free (command);
 163   if (!ret)
 164     g_warning ("query '%s' failed", query);
 165
 166   return ret;
 167 }
 168
 169 #define TABLE "tracks"
 170 static gboolean
 171 ensure_table_exists (RhythmDBGda *db)
 172 {
 173   GdaDataModel *model;
 174   guint i;
 175   GString *s;
 176   gboolean ret;
 177
 178   model = gda_connection_get_schema (db->conn, GDA_CONNECTION_SCHEMA_TABLES, NULL);
 179   g_assert (model);
 180   dump_model (model);
 181
 182   for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
 183     const GdaValue *value = gda_data_model_get_value_at (model, i, 0);
 184     if (g_str_equal (gda_value_get_string (value), TABLE)) {
 185       g_print ("Table %s already exists. Great!\n", TABLE);
 186       return TRUE;
 187     }
 188   }
 189   /* create the table */
 190   s = g_string_new ("create table " TABLE " (refcount INTEGER, ");
 191   for (i = 0; i < RHYTHMDB_NUM_PROPERTIES; i++) {
 192     GType type = rhythmdb_get_property_type (RHYTHMDB (db), i);
 193     if (i > 0)
 194       g_string_append (s, ", ");
 195     g_string_append (s, rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), i));
 196     switch (type) {
 197       case G_TYPE_STRING:
 198         g_string_append_printf (s, " VARCHAR (200)");
 199         break;
 200       case G_TYPE_BOOLEAN:
 201         g_string_append_printf (s, " BOOLEAN");
 202         break;
 203       case G_TYPE_INT:
 204       case G_TYPE_LONG: /* FIXME */
 205       case G_TYPE_UINT64: /* FIXME */
 206         g_string_append_printf (s, " INTEGER");
 207         break;
 208       case G_TYPE_FLOAT:
 209       case G_TYPE_DOUBLE:
 210         g_string_append_printf (s, " FLOAT");
 211         break;
 212       default:
 213         g_warning ("unknown type %u", (guint) type);
 214         g_assert_not_reached ();
 215         break;
 216     }
 217   }
 218   /* optimizations */
 219   if (i == RHYTHMDB_PROP_LOCATION) {
 220     /* location is unique */
 221     g_string_append (s, " UNIQUE");
 222   }
 223   g_string_append (s, ")");
 224   ret =  execute_nonquery (db, s->str);
 225   g_string_free (s, TRUE);
 226   if (ret) {
 227     /* refcounting with autodelete (woohoo!) */
 228     ret = execute_nonquery (db, "create trigger delete_track after update of refcount on "
 229         TABLE " when new.refcount = 0 begin delete from " TABLE
 230         " where _rowid_ = new._rowid_; end");
 231   }
 232   return ret;
 233 }
 234
 235 static gchar *
 236 collect_value_for_sql (const GValue *val)
 237 {
 238   gchar *value;
 239
 240   switch (G_VALUE_TYPE (val)) {
 241     case G_TYPE_STRING:
 242       value = escape_string (g_value_get_string (val));
 243       break;
 244     case G_TYPE_BOOLEAN:
 245       value = g_strdup (g_value_get_boolean (val) ? "\"TRUE\"" : "\"FALSE\"");
 246       break;
 247     case G_TYPE_INT:
 248       value = g_strdup_printf ("%d", g_value_get_int (val));
 249       break;
 250     case G_TYPE_LONG:
 251       value = g_strdup_printf ("%ld", g_value_get_long (val));
 252       break;
 253     case G_TYPE_UINT64:
 254       value = g_strdup_printf ("%"G_GUINT64_FORMAT, g_value_get_uint64 (val));
 255       break;
 256     case G_TYPE_FLOAT:
 257       value = g_strdup_printf ("%f", g_value_get_float (val));
 258       break;
 259     case G_TYPE_DOUBLE:
 260       value = g_strdup_printf ("%g", g_value_get_double (val));
 261       break;
 262     default:
 263       g_assert_not_reached ();
 264       return NULL;
 265   }
 266   return value;
 267 }
 268
 269 static void
 270 collect_value_from_sql (GValue *dest, const GdaValue *src)
 271 {
 272   const gchar *str;
 273
 274   if (gda_value_isa (src, GDA_VALUE_TYPE_NULL))
 275     return;
 276   g_assert (gda_value_isa (src, GDA_VALUE_TYPE_STRING));
 277   str = gda_value_get_string (src);
 278
 279   /* keep in sync with create table */
 280   switch (G_VALUE_TYPE (dest)) {
 281     case G_TYPE_STRING:
 282       g_value_set_string (dest, str);
 283       break;
 284     case G_TYPE_BOOLEAN:
 285       g_value_set_boolean (dest, g_str_equal (str, "TRUE"));
 286       break;
 287     case G_TYPE_INT:
 288       g_value_set_int (dest, strtol (str, NULL, 10));
 289       break;
 290     case G_TYPE_LONG:
 291       g_value_set_long (dest, strtol (str, NULL, 10));
 292       break;
 293     case G_TYPE_UINT64:
 294       g_value_set_uint64 (dest, strtoul (str, NULL, 10));
 295       break;
 296     case G_TYPE_FLOAT:
 297       g_value_set_float (dest, atof (str));
 298       break;
 299     case G_TYPE_DOUBLE:
 300       g_value_set_double (dest, atof (str));
 301       break;
 302     default:
 303       g_assert_not_reached ();
 304       break;
 305   }
 306 }
 307
 308 static gboolean
 309 _initialize (RhythmDBGda *db)
 310 {
 311   /* check songs table */
 312   if (!ensure_table_exists (db))
 313     return FALSE;
 314
 315   return execute_nonquery (db, "update " TABLE " set refcount=1");
 316 }
 317
 318 RhythmDB *
 319 rhythmdb_gda_new (const char *name)
 320 {
 321   RhythmDBGda *db = g_object_new (RHYTHMDB_TYPE_GDA, "name", name, NULL);
 322   gchar *conn_str = g_strdup_printf ("URI=%s", name);
 323
 324   g_print ("opening Db with conn string: %s\n", conn_str);
 325   db->client = gda_client_new ();
 326   g_return_val_if_fail (db->client, NULL);
 327   db->conn = gda_client_open_connection_from_string (db->client, "SQLite",
 328       conn_str, 0);
 329   g_free (conn_str);
 330   if (!db->conn) {
 331     g_warning ("GDA: error opening the library");
 332     g_object_unref (db->client);
 333     g_object_unref (db);
 334     return NULL;
 335   }
 336   if (!_initialize (db)) {
 337     g_warning ("GDA: error initializing the library");
 338     g_object_unref (db->conn);
 339     g_object_unref (db->client);
 340     g_object_unref (db);
 341     return NULL;
 342   }
 343
 344   return RHYTHMDB (db);
 345 }
 346
 347 static void
 348 rhythmdb_gda_finalize (GObject * object)
 349 {
 350   RhythmDBGda *db = RHYTHMDB_GDA (object);
 351
 352   g_object_unref (db->conn);
 353   g_object_unref (db->client);
 354
 355   G_OJECT_CLASS (rhythmdb_gda_parent_class)->finalize (object);
 356 }
 357
 358 static void
 359 rhythmdb_gda_load (RhythmDB * rdb, gboolean * die)
 360 {
 361   guint i, j;
 362   static guint types[] = { RHYTHMDB_PROP_TITLE, RHYTHMDB_PROP_ARTIST,
 363       RHYTHMDB_PROP_ALBUM, RHYTHMDB_PROP_LAST_PLAYED };
 364   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 365   GdaDataModel *model = execute_query (db, "select _rowid_, title, artist, album, last_played from " TABLE);
 366   g_return_if_fail (model);
 367
 368   for (i = 0; i < gda_data_model_get_n_rows (model); i++) {
 369     gpointer entry = GINT_TO_POINTER ((gint) strtol (gda_value_get_string (
 370             gda_data_model_get_value_at (model, 0, i)), NULL, 10));
 371     for (j = 0; j < G_N_ELEMENTS (types); j++) {
 372       GValue val = {0, };
 373       g_value_init (&val, rhythmdb_get_property_type (rdb, types [j]));
 374       collect_value_from_sql (&val,  gda_data_model_get_value_at (model, j + 1, i));
 375       rhythmdb_entry_sync_mirrored (rdb, entry, types [j], &val);
 376       g_value_unset (&val);
 377     }
 378     rhythmdb_emit_entry_restored (rdb, entry);
 379   }
 380 }
 381
 382 static void
 383 rhythmdb_gda_save (RhythmDB * rdb)
 384 {
 385   /* nothing to do here */
 386 }
 387
 388 static RhythmDBEntry *
 389 rhythmdb_gda_entry_new (RhythmDB * rdb, RhythmDBEntryType type, const char *uri)
 390 {
 391   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 392   gchar *query = g_strdup_printf ("insert into " TABLE
 393       " (type, refcount, location) values (%d, 1, \"%s\")", (gint) type, uri);
 394
 395   if (!execute_nonquery (db, query)) {
 396     g_free (query);
 397     return NULL;
 398   }
 399   g_free (query);
 400   return rhythmdb_gda_entry_lookup_by_location (rdb, uri);
 401 }
 402
 403 static void
 404 rhythmdb_gda_entry_set (RhythmDB * rdb, RhythmDBEntry * entry,
 405     guint propid, const GValue * value)
 406 {
 407   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 408   gchar *collect = collect_value_for_sql (value);
 409   gchar *query = g_strdup_printf ("update " TABLE " set %s = %s where _rowid_ = %d",
 410       rhythmdb_nice_elt_name_from_propid (rdb, propid), collect,
 411       GPOINTER_TO_INT (entry));
 412
 413   execute_nonquery (db, query);
 414   g_free (query);
 415 }
 416
 417 static void
 418 rhythmdb_gda_entry_get (RhythmDB * rdb, RhythmDBEntry * entry,
 419     guint propid, GValue * value)
 420 {
 421   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 422   gchar *query = g_strdup_printf ("select %s from " TABLE
 423       " where _ROWID_ = %d", rhythmdb_nice_elt_name_from_propid (rdb, propid),
 424       GPOINTER_TO_INT (entry));
 425   GdaDataModel *model = execute_query (db, query);
 426
 427   g_free (query);
 428   if (!model) return;
 429
 430   if (gda_data_model_get_n_rows (model) > 0) {
 431     g_assert (gda_data_model_get_n_rows (model) == 1);
 432     collect_value_from_sql (value, gda_data_model_get_value_at (model, 0, 0));
 433   }
 434   g_object_unref (G_OBJECT (model));
 435 }
 436
 437 void
 438 rhythmdb_gda_ref (RhythmDBGda *db, gint id, gint count)
 439 {
 440   gchar *query = g_strdup_printf ("select refcount from " TABLE
 441       " where _ROWID_ = %d", id);
 442   GdaDataModel *model = execute_query (db, query);
 443
 444   g_free (query);
 445   g_assert (model);
 446
 447   g_assert (gda_data_model_get_n_rows (model) == 1);
 448   count += strtol (gda_value_get_string (
 449         gda_data_model_get_value_at (model, 0, 0)), NULL, 10);
 450   g_object_unref (model);
 451
 452   query = g_strdup_printf ("update " TABLE " set refcount = %d where _ROWID_ = %d",
 453       count, id);
 454   execute_nonquery (db, query);
 455   g_free (query);
 456 }
 457
 458 static void
 459 rhythmdb_gda_entry_delete (RhythmDB * rdb, RhythmDBEntry * entry)
 460 {
 461   rhythmdb_gda_ref (RHYTHMDB_GDA (rdb), GPOINTER_TO_INT (entry), -1);
 462 }
 463
 464 static void
 465 rhythmdb_gda_entry_delete_by_type (RhythmDB * rdb, RhythmDBEntryType type)
 466 {
 467   g_assert_not_reached ();
 468 }
 469
 470 static RhythmDBEntry *
 471 rhythmdb_gda_entry_lookup_by_location (RhythmDB * rdb, const char *uri)
 472 {
 473   gpointer ret;
 474   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 475   gchar *escaped_uri = escape_string (uri);
 476   gchar *query = g_strdup_printf ("select _ROWID_ from " TABLE
 477       " where location = %s", escaped_uri);
 478   GdaDataModel *model = execute_query (db, query);
 479
 480   g_free (escaped_uri);
 481   g_free (query);
 482   if (!model) return NULL;
 483
 484   if (gda_data_model_get_n_rows (model) > 0) {
 485     g_assert (gda_data_model_get_n_rows (model) == 1);
 486     ret = GINT_TO_POINTER (strtol (gda_value_get_string (
 487             gda_data_model_get_value_at (model, 0, 0)), NULL, 10));
 488   } else {
 489     ret = NULL;
 490   }
 491   g_object_unref (G_OBJECT (model));
 492   g_print ("FOUND ENTRY %p\n", ret);
 493   return ret;
 494 }
 495
 496 static gchar *
 497 translate_query (RhythmDBGda *db, RhythmDBQueryData *data)
 498 {
 499   gchar *operation = NULL, *value, *ret;
 500
 501   switch (data->type) {
 502     case RHYTHMDB_QUERY_DISJUNCTION:
 503     case RHYTHMDB_QUERY_SUBQUERY:
 504       g_assert_not_reached (); /* FIXME */
 505       return NULL;
 506     case RHYTHMDB_QUERY_PROP_EQUALS:
 507       operation = "%s = %s";
 508       break;
 509     case RHYTHMDB_QUERY_PROP_LIKE:
 510     case RHYTHMDB_QUERY_PROP_NOT_LIKE:
 511       g_assert_not_reached (); /* FIXME */
 512       break;
 513     case RHYTHMDB_QUERY_PROP_GREATER:
 514       operation = "%s > %s";
 515       break;
 516     case RHYTHMDB_QUERY_PROP_LESS:
 517       operation = "%s < %s";
 518       break;
 519     case RHYTHMDB_QUERY_END:
 520     default:
 521       g_assert_not_reached ();
 522       return NULL;
 523   }
 524   /* collect value */
 525   value = collect_value_for_sql (data->val);
 526   ret = g_strdup_printf (operation, rhythmdb_nice_elt_name_from_propid (RHYTHMDB (db), data->propid),
 527       value);
 528   g_free (value);
 529
 530   return ret;
 531 }
 532
 533 /* set rowid to 0 for all rows */
 534 static GdaDataModel *
 535 do_query (RhythmDBGda *db, GPtrArray * query, gint rowid)
 536 {
 537   guint i;
 538   RhythmDBQueryData *data;
 539   GString *s = g_string_new (NULL);
 540   GdaDataModel *model;
 541   gchar *tmp;
 542
 543   g_assert (query->len == 1);
 544   g_string_append (s, "select _ROWID_ from " TABLE " where ");
 545   if (rowid)
 546     g_string_append_printf (s, "rowid == %d AND (", rowid);
 547   for (i = 0; i < query->len; i++) {
 548     data = (RhythmDBQueryData *) g_ptr_array_index (query, i);
 549     tmp = translate_query (db, data);
 550     if (i > 0)
 551       g_string_append (s, " and ");
 552     g_string_append (s, tmp);
 553     g_free (tmp);
 554   }
 555   if (rowid)
 556     g_string_append (s, ")");
 557
 558   model = execute_query (db, s->str);
 559   g_string_free (s, TRUE);
 560   return model;
 561 }
 562
 563 static void
 564 rhythmdb_gda_do_full_query (RhythmDB * rdb, GPtrArray * query,
 565     GtkTreeModel * main_model, gboolean * cancel)
 566 {
 567   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 568   GdaDataModel *model = do_query (db, query, 0);
 569   g_return_if_fail (model);
 570
 571   /* now the cludge */
 572   {
 573     int j;
 574     GPtrArray *queue;
 575
 576     queue = g_ptr_array_sized_new (gda_data_model_get_n_rows (model));
 577     for (j = 0; j < gda_data_model_get_n_rows (model); j++) {
 578       g_ptr_array_add (queue, GINT_TO_POINTER (strtol (gda_value_get_string (
 579             gda_data_model_get_value_at (model, 0, j)), NULL, 10)));
 580     }
 581     rhythmdb_query_model_add_entries (RHYTHMDB_QUERY_MODEL (main_model), queue);
 582   }
 583 }
 584
 585 static gboolean
 586 rhythmdb_gda_evaluate_query (RhythmDB * rdb, GPtrArray * query,
 587     RhythmDBEntry * aentry)
 588 {
 589   gboolean ret;
 590   RhythmDBGda *db = RHYTHMDB_GDA (rdb);
 591   GdaDataModel *model = do_query (db, query, GPOINTER_TO_INT (aentry));
 592
 593   if (!model) return FALSE;
 594   ret = gda_data_model_get_n_rows (model) > 0;
 595   g_object_unref (model);
 596   return ret;
 597 }