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