• Main Page
  • Related Pages
  • Modules
  • Data Structures
  • Files
  • File List
  • Globals

src/xmms/collquery.c

Go to the documentation of this file.
00001 /*  XMMS2 - X Music Multiplexer System
00002  *  Copyright (C) 2003-2009 XMMS2 Team
00003  *
00004  *  PLUGINS ARE NOT CONSIDERED TO BE DERIVED WORK !!!
00005  *
00006  *  This library is free software; you can redistribute it and/or
00007  *  modify it under the terms of the GNU Lesser General Public
00008  *  License as published by the Free Software Foundation; either
00009  *  version 2.1 of the License, or (at your option) any later version.
00010  *
00011  *  This library is distributed in the hope that it will be useful,
00012  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00013  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014  *  Lesser General Public License for more details.
00015  */
00016 
00017 
00018 /** @file
00019  *  Functions to build an SQL query from a collection.
00020  */
00021 
00022 #include <string.h>
00023 #include <glib.h>
00024 
00025 #include "xmmspriv/xmms_collquery.h"
00026 #include "xmms/xmms_log.h"
00027 
00028 
00029 /* Query structures */
00030 
00031 typedef struct {
00032     guint limit_start;
00033     guint limit_len;
00034     xmmsv_t *order;
00035     xmmsv_t *fetch;
00036     xmmsv_t *group;
00037 } coll_query_params_t;
00038 
00039 typedef enum {
00040     XMMS_QUERY_ALIAS_ID,
00041     XMMS_QUERY_ALIAS_PROP,
00042 } coll_query_alias_type_t;
00043 
00044 typedef struct {
00045     coll_query_alias_type_t type;
00046     guint id;
00047     gboolean optional;
00048 } coll_query_alias_t;
00049 
00050 typedef struct {
00051     GHashTable *aliases;
00052     guint alias_count;
00053     gchar *alias_base;
00054     GString *conditions;
00055     coll_query_params_t *params;
00056 } coll_query_t;
00057 
00058 
00059 
00060 static coll_query_t* init_query (coll_query_params_t *params);
00061 static void add_fetch_group_aliases (coll_query_t *query, coll_query_params_t *params);
00062 static void destroy_query (coll_query_t* query);
00063 static GString* xmms_collection_gen_query (coll_query_t *query);
00064 static void xmms_collection_append_to_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll, coll_query_t *query);
00065 
00066 static void query_append_uint (coll_query_t *query, guint i);
00067 static void query_append_string (coll_query_t *query, const gchar *s);
00068 static void query_append_protect_string (coll_query_t *query, gchar *s);
00069 static void query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll);
00070 static void query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll);
00071 static void query_append_filter (coll_query_t *query, xmmsv_coll_type_t type, gchar *key, gchar *value, gboolean case_sens);
00072 static void query_string_append_joins (gpointer key, gpointer val, gpointer udata);
00073 static void query_string_append_alias_list (coll_query_t *query, GString *qstring, xmmsv_t *fields);
00074 static void query_string_append_fetch (coll_query_t *query, GString *qstring);
00075 static void query_string_append_alias (GString *qstring, coll_query_alias_t *alias);
00076 
00077 static const gchar *canonical_field_name (const gchar *field);
00078 static gboolean operator_is_allmedia (xmmsv_coll_t *op);
00079 static coll_query_alias_t *query_make_alias (coll_query_t *query, const gchar *field, gboolean optional);
00080 static coll_query_alias_t *query_get_alias (coll_query_t *query, const gchar *field);
00081 
00082 
00083 
00084 /** @defgroup CollectionQuery CollectionQuery
00085   * @ingroup XMMSServer
00086   * @brief This module generates queries from collections.
00087   *
00088   * @{
00089   */
00090 
00091 /* Generate a query string from a collection and query parameters. */
00092 GString*
00093 xmms_collection_get_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll,
00094                            guint limit_start, guint limit_len,
00095                            xmmsv_t *order, xmmsv_t *fetch, xmmsv_t *group)
00096 {
00097     GString *qstring;
00098     coll_query_t *query;
00099     coll_query_params_t params = { limit_start, limit_len, order, fetch, group };
00100 
00101     query = init_query (&params);
00102     xmms_collection_append_to_query (dag, coll, query);
00103     add_fetch_group_aliases (query, &params);
00104 
00105     qstring = xmms_collection_gen_query (query);
00106 
00107     destroy_query (query);
00108 
00109     return qstring;
00110 }
00111 
00112 
00113 /* Initialize a query structure */
00114 static coll_query_t*
00115 init_query (coll_query_params_t *params)
00116 {
00117     coll_query_t *query;
00118 
00119     query = g_new (coll_query_t, 1);
00120     if (query == NULL) {
00121         return NULL;
00122     }
00123 
00124     query->aliases = g_hash_table_new_full (g_str_hash, g_str_equal,
00125                                             g_free, g_free);
00126 
00127     query->alias_count = 1;
00128     query->alias_base = NULL;
00129     query->conditions = g_string_new (NULL);
00130     query->params = params;
00131 
00132     return query;
00133 }
00134 
00135 static void
00136 append_each_alias (xmmsv_t *value, void *udata)
00137 {
00138     const gchar *name;
00139     coll_query_t *query = (coll_query_t *) udata;
00140     xmmsv_get_string (value, &name);
00141     query_make_alias (query, name, TRUE);
00142 }
00143 
00144 static void
00145 add_fetch_group_aliases (coll_query_t *query, coll_query_params_t *params)
00146 {
00147     /* Prepare aliases for the group/fetch fields */
00148     xmmsv_list_foreach (query->params->group, append_each_alias, query);
00149     xmmsv_list_foreach (query->params->fetch, append_each_alias, query);
00150 }
00151 
00152 /* Free a coll_query_t object */
00153 static void
00154 destroy_query (coll_query_t* query)
00155 {
00156     g_hash_table_destroy (query->aliases);
00157     g_string_free (query->conditions, TRUE);
00158     g_free (query);
00159 }
00160 
00161 
00162 /* Generate a query string from a query structure. */
00163 static GString*
00164 xmms_collection_gen_query (coll_query_t *query)
00165 {
00166     GString *qstring;
00167 
00168     /* If no alias base yet (m0), select the default base property */
00169     if (query->alias_base == NULL) {
00170         query_make_alias (query, XMMS_COLLQUERY_DEFAULT_BASE, FALSE);
00171     }
00172 
00173     /* Append select and joins */
00174     qstring = g_string_new ("SELECT DISTINCT ");
00175     query_string_append_fetch (query, qstring);
00176     g_string_append (qstring, " FROM Media as m0");
00177     g_hash_table_foreach (query->aliases, query_string_append_joins, qstring);
00178 
00179     /* Append conditions */
00180     g_string_append_printf (qstring, " WHERE m0.key='%s'", query->alias_base);
00181     if (query->conditions->len > 0) {
00182         g_string_append_printf (qstring, " AND %s", query->conditions->str);
00183     }
00184 
00185     /* Append grouping */
00186     if (xmmsv_list_get_size (query->params->group) > 0) {
00187         g_string_append (qstring, " GROUP BY ");
00188         query_string_append_alias_list (query, qstring, query->params->group);
00189     }
00190 
00191     /* Append ordering */
00192     /* FIXME: Ordering is Teh Broken (source?) */
00193     if (xmmsv_list_get_size (query->params->order) > 0) {
00194         g_string_append (qstring, " ORDER BY ");
00195         query_string_append_alias_list (query, qstring, query->params->order);
00196     }
00197 
00198     /* Append limit */
00199     if (query->params->limit_len != 0) {
00200         if (query->params->limit_start ) {
00201             g_string_append_printf (qstring, " LIMIT %u,%u",
00202                                     query->params->limit_start,
00203                                     query->params->limit_len);
00204         } else {
00205             g_string_append_printf (qstring, " LIMIT %u",
00206                                     query->params->limit_len);
00207         }
00208     }
00209 
00210     return qstring;
00211 }
00212 
00213 /* Recursively append conditions corresponding to the given collection to the query. */
00214 static void
00215 xmms_collection_append_to_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll,
00216                                  coll_query_t *query)
00217 {
00218     gint i;
00219     xmmsv_coll_t *op;
00220     guint *idlist;
00221     gchar *attr1, *attr2, *attr3;
00222     gboolean case_sens;
00223 
00224     xmmsv_coll_type_t type = xmmsv_coll_get_type (coll);
00225     switch (type) {
00226     case XMMS_COLLECTION_TYPE_REFERENCE:
00227         if (!operator_is_allmedia (coll)) {
00228             query_append_operand (query, dag, coll);
00229         } else {
00230             /* FIXME: Hackish solution to append a ref to All Media */
00231             query_append_string (query, "1");
00232         }
00233         break;
00234 
00235     case XMMS_COLLECTION_TYPE_UNION:
00236     case XMMS_COLLECTION_TYPE_INTERSECTION:
00237         i = 0;
00238         query_append_string (query, "(");
00239 
00240         xmmsv_coll_operand_list_save (coll);
00241         xmmsv_coll_operand_list_first (coll);
00242         while (xmmsv_coll_operand_list_entry (coll, &op)) {
00243             if (i != 0) {
00244                 if (type == XMMS_COLLECTION_TYPE_UNION)
00245                     query_append_string (query, " OR ");
00246                 else
00247                     query_append_string (query, " AND ");
00248             } else {
00249                 i = 1;
00250             }
00251             xmms_collection_append_to_query (dag, op, query);
00252             xmmsv_coll_operand_list_next (coll);
00253         }
00254         xmmsv_coll_operand_list_restore (coll);
00255 
00256         query_append_string (query, ")");
00257         break;
00258 
00259     case XMMS_COLLECTION_TYPE_COMPLEMENT:
00260         query_append_string (query, "NOT ");
00261         query_append_operand (query, dag, coll);
00262         break;
00263 
00264     case XMMS_COLLECTION_TYPE_HAS:
00265     case XMMS_COLLECTION_TYPE_EQUALS:
00266     case XMMS_COLLECTION_TYPE_MATCH:
00267     case XMMS_COLLECTION_TYPE_SMALLER:
00268     case XMMS_COLLECTION_TYPE_GREATER:
00269         xmmsv_coll_attribute_get (coll, "field", &attr1);
00270         xmmsv_coll_attribute_get (coll, "value", &attr2);
00271         xmmsv_coll_attribute_get (coll, "case-sensitive", &attr3);
00272         case_sens = (attr3 != NULL && strcmp (attr3, "true") == 0);
00273 
00274         query_append_string (query, "(");
00275         query_append_filter (query, type, attr1, attr2, case_sens);
00276 
00277         query_append_intersect_operand (query, dag, coll);
00278         query_append_string (query, ")");
00279         break;
00280 
00281     case XMMS_COLLECTION_TYPE_IDLIST:
00282     case XMMS_COLLECTION_TYPE_QUEUE:
00283     case XMMS_COLLECTION_TYPE_PARTYSHUFFLE:
00284         idlist = xmmsv_coll_get_idlist (coll);
00285         query_append_string (query, "m0.id IN (");
00286         for (i = 0; idlist[i] != 0; ++i) {
00287             if (i != 0) {
00288                 query_append_string (query, ",");
00289             }
00290             query_append_uint (query, idlist[i]);
00291         }
00292         query_append_string (query, ")");
00293         break;
00294 
00295     /* invalid type */
00296     default:
00297         XMMS_DBG ("Cannot append invalid collection operator!");
00298         g_assert_not_reached ();
00299         break;
00300     }
00301 
00302 }
00303 
00304 
00305 /** Register a (unique) field alias in the query structure and return
00306  * the corresponding alias pointer.
00307  *
00308  * @param query  The query object to insert the alias in.
00309  * @param field  The name of the property that will correspond to the alias.
00310  * @param optional  Whether the property can be optional (i.e. LEFT JOIN)
00311  * @return  The alias pointer.
00312  */
00313 static coll_query_alias_t *
00314 query_make_alias (coll_query_t *query, const gchar *field, gboolean optional)
00315 {
00316     coll_query_alias_t *alias;
00317     alias = g_hash_table_lookup (query->aliases, field);
00318 
00319     /* Insert in the hashtable */
00320     if (alias == NULL) {
00321         gchar *fieldkey = g_strdup (field);
00322 
00323         alias = g_new (coll_query_alias_t, 1);
00324         alias->optional = optional;
00325         alias->id = 0;
00326 
00327         if (strcmp (field, "id") == 0) {
00328             alias->type = XMMS_QUERY_ALIAS_ID;
00329         } else {
00330             alias->type = XMMS_QUERY_ALIAS_PROP;
00331 
00332             /* Found a base */
00333             if (query->alias_base == NULL &&
00334                 (!optional || strcmp (field, XMMS_COLLQUERY_DEFAULT_BASE) == 0)) {
00335                 alias->id = 0;
00336                 query->alias_base = fieldkey;
00337             } else {
00338                 alias->id = query->alias_count;
00339                 query->alias_count++;
00340             }
00341         }
00342 
00343         g_hash_table_insert (query->aliases, fieldkey, alias);
00344 
00345     /* If was not optional but now is, update */
00346     } else if (!alias->optional && optional) {
00347         alias->optional = optional;
00348     }
00349 
00350     return alias;
00351 }
00352 
00353 static coll_query_alias_t *
00354 query_get_alias (coll_query_t *query, const gchar *field)
00355 {
00356     return g_hash_table_lookup (query->aliases, field);
00357 }
00358 
00359 /* Find the canonical name of a field (strip flags, if any) */
00360 static const gchar *
00361 canonical_field_name (const gchar *field) {
00362     if (*field == '-') {
00363         field++;
00364     } else if (*field == '~') {
00365         field = NULL;
00366     }
00367     return field;
00368 }
00369 
00370 
00371 /* Determine whether the given operator is a reference to "All Media" */
00372 static gboolean
00373 operator_is_allmedia (xmmsv_coll_t *op)
00374 {
00375     gchar *target_name;
00376     xmmsv_coll_attribute_get (op, "reference", &target_name);
00377     return (target_name != NULL && strcmp (target_name, "All Media") == 0);
00378 }
00379 
00380 static void
00381 query_append_uint (coll_query_t *query, guint i)
00382 {
00383     g_string_append_printf (query->conditions, "%u", i);
00384 }
00385 
00386 static void
00387 query_append_string (coll_query_t *query, const gchar *s)
00388 {
00389     g_string_append (query->conditions, s);
00390 }
00391 
00392 static void
00393 query_append_protect_string (coll_query_t *query, gchar *s)
00394 {
00395     gchar *preps;
00396     if ((preps = sqlite_prepare_string (s)) != NULL) {  /* FIXME: Return oom error */
00397         query_append_string (query, preps);
00398         g_free (preps);
00399     }
00400 }
00401 
00402 static void
00403 query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll)
00404 {
00405     xmmsv_coll_t *op;
00406     gchar *target_name;
00407     gchar *target_ns;
00408     guint  target_nsid;
00409 
00410     xmmsv_coll_operand_list_save (coll);
00411     xmmsv_coll_operand_list_first (coll);
00412     if (!xmmsv_coll_operand_list_entry (coll, &op)) {
00413         /* Ref'd coll not saved as operand, look for it */
00414         if (xmmsv_coll_attribute_get (coll, "reference", &target_name) &&
00415             xmmsv_coll_attribute_get (coll, "namespace", &target_ns)) {
00416 
00417             target_nsid = xmms_collection_get_namespace_id (target_ns);
00418             op = xmms_collection_get_pointer (dag, target_name, target_nsid);
00419         }
00420     }
00421     xmmsv_coll_operand_list_restore (coll);
00422 
00423     /* Append reference operator */
00424     if (op != NULL) {
00425         xmms_collection_append_to_query (dag, op, query);
00426 
00427     /* Cannot find reference, append dummy TRUE */
00428     } else {
00429         query_append_string (query, "1");
00430     }
00431 }
00432 
00433 static void
00434 query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag,
00435                                 xmmsv_coll_t *coll)
00436 {
00437     xmmsv_coll_t *op;
00438 
00439     xmmsv_coll_operand_list_save (coll);
00440     xmmsv_coll_operand_list_first (coll);
00441     if (xmmsv_coll_operand_list_entry (coll, &op)) {
00442         if (!operator_is_allmedia (op)) {
00443             query_append_string (query, " AND ");
00444             xmms_collection_append_to_query (dag, op, query);
00445         }
00446     }
00447     xmmsv_coll_operand_list_restore (coll);
00448 }
00449 
00450 /* Append a filtering clause on the field value, depending on the operator type. */
00451 static void
00452 query_append_filter (coll_query_t *query, xmmsv_coll_type_t type,
00453                      gchar *key, gchar *value, gboolean case_sens)
00454 {
00455     coll_query_alias_t *alias;
00456     gboolean optional;
00457     gchar *temp;
00458     gint i;
00459 
00460     if (type == XMMS_COLLECTION_TYPE_HAS) {
00461         optional = TRUE;
00462     } else {
00463         optional = FALSE;
00464     }
00465 
00466     alias = query_make_alias (query, key, optional);
00467 
00468     switch (type) {
00469     /* escape strings */
00470     case XMMS_COLLECTION_TYPE_EQUALS:
00471     case XMMS_COLLECTION_TYPE_MATCH:
00472         if (case_sens) {
00473             query_string_append_alias (query->conditions, alias);
00474         } else {
00475             query_append_string (query, "(");
00476             query_string_append_alias (query->conditions, alias);
00477             query_append_string (query, " COLLATE NOCASE)");
00478         }
00479 
00480         if (type == XMMS_COLLECTION_TYPE_EQUALS) {
00481             query_append_string (query, "=");
00482         } else {
00483             if (case_sens) {
00484                 query_append_string (query, " GLOB ");
00485             } else {
00486                 query_append_string (query, " LIKE ");
00487             }
00488         }
00489 
00490         if (type == XMMS_COLLECTION_TYPE_MATCH && !case_sens) {
00491             temp = g_strdup(value);
00492             for (i = 0; temp[i]; i++) {
00493                 switch (temp[i]) {
00494                     case '*': temp[i] = '%'; break;
00495                     case '?': temp[i] = '_'; break;
00496                     default :                break;
00497                 }
00498             }
00499             query_append_protect_string (query, temp);
00500             g_free(temp);
00501         } else {
00502             query_append_protect_string (query, value);
00503         }
00504         break;
00505 
00506     /* do not escape numerical values */
00507     case XMMS_COLLECTION_TYPE_SMALLER:
00508     case XMMS_COLLECTION_TYPE_GREATER:
00509         query_string_append_alias (query->conditions, alias);
00510         if (type == XMMS_COLLECTION_TYPE_SMALLER) {
00511             query_append_string (query, " < ");
00512         } else {
00513             query_append_string (query, " > ");
00514         }
00515         query_append_string (query, value);
00516         break;
00517 
00518     case XMMS_COLLECTION_TYPE_HAS:
00519         query_string_append_alias (query->conditions, alias);
00520         query_append_string (query, " is not null");
00521         break;
00522 
00523     /* Called with invalid type? */
00524     default:
00525         g_assert_not_reached ();
00526         break;
00527     }
00528 }
00529 
00530 /* Append SELECT joins to the argument string for each alias of the hashtable. */
00531 static void
00532 query_string_append_joins (gpointer key, gpointer val, gpointer udata)
00533 {
00534     gchar *field;
00535     GString *qstring;
00536     coll_query_alias_t *alias;
00537 
00538     field = key;
00539     qstring = (GString*)udata;
00540     alias = (coll_query_alias_t*)val;
00541 
00542     if ((alias->id > 0) && (alias->type == XMMS_QUERY_ALIAS_PROP)) {
00543         if (alias->optional) {
00544             g_string_append_printf (qstring, " LEFT");
00545         }
00546 
00547         g_string_append_printf (qstring,
00548                                 " JOIN Media as m%u ON m0.id=m%u.id"
00549                                 " AND m%u.key='%s'",
00550                                 alias->id, alias->id, alias->id, field);
00551     }
00552 }
00553 
00554 /* Given a list of fields, append the corresponding aliases to the argument string. */
00555 static void
00556 query_string_append_alias_list (coll_query_t *query, GString *qstring,
00557                                 xmmsv_t *fields)
00558 {
00559     coll_query_alias_t *alias;
00560     xmmsv_list_iter_t *it;
00561     xmmsv_t *valstr;
00562     gboolean first = TRUE;
00563 
00564     for (xmmsv_get_list_iter (fields, &it);
00565          xmmsv_list_iter_valid (it);
00566          xmmsv_list_iter_next (it)) {
00567 
00568         /* extract string from cmdval_t */
00569         const gchar *field, *canon_field;
00570         xmmsv_list_iter_entry (it, &valstr);
00571         xmmsv_get_string (valstr, &field);
00572         canon_field = canonical_field_name (field);
00573 
00574         if (first) first = FALSE;
00575         else {
00576             g_string_append (qstring, ", ");
00577         }
00578 
00579         if (canon_field != NULL) {
00580             alias = query_get_alias (query, canon_field);
00581             if (alias != NULL) {
00582                 query_string_append_alias (qstring, alias);
00583             } else {
00584                 if (*field != '~') {
00585                     if (strcmp(canon_field, "id") == 0) {
00586                         g_string_append (qstring, "m0.id");
00587                     } else {
00588                         g_string_append_printf (qstring,
00589                             "(SELECT value FROM Media WHERE id = m0.id AND "
00590                             "key='%s')", canon_field);
00591                     }
00592                 }
00593             }
00594         }
00595 
00596         /* special prefix for ordering */
00597         if (*field == '-') {
00598             g_string_append (qstring, " DESC");
00599         } else if (*field == '~') {
00600             /* FIXME: Temporary hack to allow custom ordering functions */
00601             g_string_append (qstring, field + 1);
00602         }
00603     }
00604 }
00605 
00606 static void
00607 query_string_append_fetch (coll_query_t *query, GString *qstring)
00608 {
00609     coll_query_alias_t *alias;
00610     xmmsv_list_iter_t *it;
00611     xmmsv_t *valstr;
00612     gboolean first = TRUE;
00613     const gchar *name;
00614 
00615     for (xmmsv_get_list_iter (query->params->fetch, &it);
00616          xmmsv_list_iter_valid (it);
00617          xmmsv_list_iter_next (it)) {
00618 
00619         /* extract string from cmdval_t */
00620         xmmsv_list_iter_entry (it, &valstr);
00621         xmmsv_get_string (valstr, &name);
00622         alias = query_make_alias (query, name, TRUE);
00623 
00624         if (first) first = FALSE;
00625         else {
00626             g_string_append (qstring, ", ");
00627         }
00628 
00629         query_string_append_alias (qstring, alias);
00630         g_string_append_printf (qstring, " AS %s", name);
00631     }
00632 }
00633 
00634 static void
00635 query_string_append_alias (GString *qstring, coll_query_alias_t *alias)
00636 {
00637     switch (alias->type) {
00638     case XMMS_QUERY_ALIAS_PROP:
00639         g_string_append_printf (qstring, "m%u.value", alias->id);
00640         break;
00641 
00642     case XMMS_QUERY_ALIAS_ID:
00643         g_string_append (qstring, "m0.id");
00644         break;
00645 
00646     default:
00647         break;
00648     }
00649 }
00650 
00651 /**
00652  * @}
00653  */

Generated on Wed Feb 9 2011 for XMMS2 by  doxygen 1.7.1