00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
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
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 typedef enum {
00059 COLL_QUERY_VALUE_TYPE_STRING,
00060 COLL_QUERY_VALUE_TYPE_INT,
00061 COLL_QUERY_VALUE_TYPE_BOTH
00062 } coll_query_value_type_t;
00063
00064 static coll_query_t* init_query (coll_query_params_t *params);
00065 static void add_fetch_group_aliases (coll_query_t *query, coll_query_params_t *params);
00066 static void destroy_query (coll_query_t* query);
00067 static GString* xmms_collection_gen_query (coll_query_t *query);
00068 static void xmms_collection_append_to_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll, coll_query_t *query);
00069
00070 static void query_append_int (coll_query_t *query, gint i);
00071 static void query_append_string (coll_query_t *query, const gchar *s);
00072 static void query_append_protect_string (coll_query_t *query, gchar *s);
00073 static void query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll);
00074 static void query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll);
00075 static void query_append_filter (coll_query_t *query, xmmsv_coll_type_t type, gchar *key, gchar *value, gboolean case_sens);
00076 static void query_string_append_joins (gpointer key, gpointer val, gpointer udata);
00077 static void query_string_append_alias_list (coll_query_t *query, GString *qstring, xmmsv_t *fields);
00078 static void query_string_append_fetch (coll_query_t *query, GString *qstring);
00079 static void query_string_append_alias (GString *qstring, coll_query_alias_t *alias, coll_query_value_type_t type);
00080
00081 static const gchar *canonical_field_name (const gchar *field);
00082 static gboolean operator_is_allmedia (xmmsv_coll_t *op);
00083 static coll_query_alias_t *query_make_alias (coll_query_t *query, const gchar *field, gboolean optional);
00084 static coll_query_alias_t *query_get_alias (coll_query_t *query, const gchar *field);
00085
00086
00087
00088
00089
00090
00091
00092
00093
00094
00095
00096 GString*
00097 xmms_collection_get_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll,
00098 guint limit_start, guint limit_len,
00099 xmmsv_t *order, xmmsv_t *fetch, xmmsv_t *group)
00100 {
00101 GString *qstring;
00102 coll_query_t *query;
00103 coll_query_params_t params = { limit_start, limit_len, order, fetch, group };
00104
00105 query = init_query (¶ms);
00106 xmms_collection_append_to_query (dag, coll, query);
00107 add_fetch_group_aliases (query, ¶ms);
00108
00109 qstring = xmms_collection_gen_query (query);
00110
00111 destroy_query (query);
00112
00113 return qstring;
00114 }
00115
00116
00117
00118 static coll_query_t*
00119 init_query (coll_query_params_t *params)
00120 {
00121 coll_query_t *query;
00122
00123 query = g_new (coll_query_t, 1);
00124 if (query == NULL) {
00125 return NULL;
00126 }
00127
00128 query->aliases = g_hash_table_new_full (g_str_hash, g_str_equal,
00129 g_free, g_free);
00130
00131 query->alias_count = 1;
00132 query->alias_base = NULL;
00133 query->conditions = g_string_new (NULL);
00134 query->params = params;
00135
00136 return query;
00137 }
00138
00139 static void
00140 append_each_alias (xmmsv_t *value, void *udata)
00141 {
00142 const gchar *name;
00143 coll_query_t *query = (coll_query_t *) udata;
00144 xmmsv_get_string (value, &name);
00145 query_make_alias (query, name, TRUE);
00146 }
00147
00148 static void
00149 add_fetch_group_aliases (coll_query_t *query, coll_query_params_t *params)
00150 {
00151
00152 xmmsv_list_foreach (query->params->group, append_each_alias, query);
00153 xmmsv_list_foreach (query->params->fetch, append_each_alias, query);
00154 }
00155
00156
00157 static void
00158 destroy_query (coll_query_t* query)
00159 {
00160 g_hash_table_destroy (query->aliases);
00161 g_string_free (query->conditions, TRUE);
00162 g_free (query);
00163 }
00164
00165
00166
00167 static GString*
00168 xmms_collection_gen_query (coll_query_t *query)
00169 {
00170 GString *qstring;
00171
00172
00173 if (query->alias_base == NULL) {
00174 query_make_alias (query, XMMS_COLLQUERY_DEFAULT_BASE, FALSE);
00175 } else {
00176
00177
00178 if (query->conditions->len > 0) {
00179 g_string_append (query->conditions, " AND ");
00180 }
00181 g_string_append_printf (query->conditions,
00182 "xmms_source_pref (m0.source) = "
00183 "(SELECT MIN (xmms_source_pref (n.source)) FROM Media AS n "
00184 "WHERE n.id = m0.id AND n.key = '%s')",
00185 query->alias_base);
00186 }
00187
00188
00189 qstring = g_string_new ("SELECT DISTINCT ");
00190 query_string_append_fetch (query, qstring);
00191 g_string_append (qstring, " FROM Media AS m0");
00192 g_hash_table_foreach (query->aliases, query_string_append_joins, qstring);
00193
00194
00195 g_string_append_printf (qstring, " WHERE m0.key='%s'", query->alias_base);
00196 if (query->conditions->len > 0) {
00197 g_string_append_printf (qstring, " AND %s", query->conditions->str);
00198 }
00199
00200
00201 if (xmmsv_list_get_size (query->params->group) > 0) {
00202 g_string_append (qstring, " GROUP BY ");
00203 query_string_append_alias_list (query, qstring, query->params->group);
00204 }
00205
00206
00207
00208 if (xmmsv_list_get_size (query->params->order) > 0) {
00209 g_string_append (qstring, " ORDER BY ");
00210 query_string_append_alias_list (query, qstring, query->params->order);
00211 }
00212
00213
00214 if (query->params->limit_len != 0) {
00215 if (query->params->limit_start ) {
00216 g_string_append_printf (qstring, " LIMIT %u,%u",
00217 query->params->limit_start,
00218 query->params->limit_len);
00219 } else {
00220 g_string_append_printf (qstring, " LIMIT %u",
00221 query->params->limit_len);
00222 }
00223 }
00224
00225 return qstring;
00226 }
00227
00228
00229 static void
00230 xmms_collection_append_to_query (xmms_coll_dag_t *dag, xmmsv_coll_t *coll,
00231 coll_query_t *query)
00232 {
00233 xmmsv_coll_t *op;
00234 xmms_medialib_entry_t entry;
00235 gchar *attr1, *attr2, *attr3;
00236 gboolean case_sens;
00237 xmmsv_list_iter_t *iter;
00238 xmmsv_t *tmp;
00239 gboolean first;
00240
00241 xmmsv_coll_type_t type = xmmsv_coll_get_type (coll);
00242 switch (type) {
00243 case XMMS_COLLECTION_TYPE_REFERENCE:
00244 if (!operator_is_allmedia (coll)) {
00245 query_append_operand (query, dag, coll);
00246 } else {
00247
00248 query_append_string (query, "1");
00249 }
00250 break;
00251
00252 case XMMS_COLLECTION_TYPE_UNION:
00253 case XMMS_COLLECTION_TYPE_INTERSECTION:
00254 first = TRUE;
00255 query_append_string (query, "(");
00256
00257 xmmsv_get_list_iter (xmmsv_coll_operands_get (coll), &iter);
00258
00259 for (xmmsv_list_iter_first (iter);
00260 xmmsv_list_iter_valid (iter);
00261 xmmsv_list_iter_next (iter)) {
00262 if (first) {
00263 first = FALSE;
00264 } else {
00265 if (type == XMMS_COLLECTION_TYPE_UNION)
00266 query_append_string (query, " OR ");
00267 else
00268 query_append_string (query, " AND ");
00269 }
00270 xmmsv_list_iter_entry (iter, &tmp);
00271 xmmsv_get_coll (tmp, &op);
00272 xmms_collection_append_to_query (dag, op, query);
00273 }
00274 xmmsv_list_iter_explicit_destroy (iter);
00275
00276 query_append_string (query, ")");
00277 break;
00278
00279 case XMMS_COLLECTION_TYPE_COMPLEMENT:
00280 query_append_string (query, "NOT ");
00281 query_append_operand (query, dag, coll);
00282 break;
00283
00284 case XMMS_COLLECTION_TYPE_HAS:
00285 case XMMS_COLLECTION_TYPE_EQUALS:
00286 case XMMS_COLLECTION_TYPE_MATCH:
00287 case XMMS_COLLECTION_TYPE_SMALLER:
00288 case XMMS_COLLECTION_TYPE_GREATER:
00289 xmmsv_coll_attribute_get (coll, "field", &attr1);
00290 xmmsv_coll_attribute_get (coll, "value", &attr2);
00291 xmmsv_coll_attribute_get (coll, "case-sensitive", &attr3);
00292 case_sens = (attr3 != NULL && strcmp (attr3, "true") == 0);
00293
00294 query_append_string (query, "(");
00295 query_append_filter (query, type, attr1, attr2, case_sens);
00296
00297 query_append_intersect_operand (query, dag, coll);
00298 query_append_string (query, ")");
00299 break;
00300
00301 case XMMS_COLLECTION_TYPE_IDLIST:
00302 case XMMS_COLLECTION_TYPE_QUEUE:
00303 case XMMS_COLLECTION_TYPE_PARTYSHUFFLE:
00304 first = TRUE;
00305 query_append_string (query, "m0.id IN (");
00306
00307 xmmsv_get_list_iter (xmmsv_coll_idlist_get (coll), &iter);
00308 for (xmmsv_list_iter_first (iter);
00309 xmmsv_list_iter_valid (iter);
00310 xmmsv_list_iter_next (iter)) {
00311
00312 if (first) {
00313 first = FALSE;
00314 } else {
00315 query_append_string (query, ",");
00316 }
00317
00318 xmmsv_list_iter_entry_int (iter, &entry);
00319 query_append_int (query, entry);
00320 }
00321 xmmsv_list_iter_explicit_destroy (iter);
00322
00323 query_append_string (query, ")");
00324 break;
00325
00326
00327 default:
00328 XMMS_DBG ("Cannot append invalid collection operator!");
00329 g_assert_not_reached ();
00330 break;
00331 }
00332
00333 }
00334
00335
00336
00337
00338
00339
00340
00341
00342
00343
00344 static coll_query_alias_t *
00345 query_make_alias (coll_query_t *query, const gchar *field, gboolean optional)
00346 {
00347 coll_query_alias_t *alias;
00348 alias = g_hash_table_lookup (query->aliases, field);
00349
00350
00351 if (alias == NULL) {
00352 gchar *fieldkey = g_strdup (field);
00353
00354 alias = g_new (coll_query_alias_t, 1);
00355 alias->optional = optional;
00356 alias->id = 0;
00357
00358 if (strcmp (field, "id") == 0) {
00359 alias->type = XMMS_QUERY_ALIAS_ID;
00360 } else {
00361 alias->type = XMMS_QUERY_ALIAS_PROP;
00362
00363
00364 if (query->alias_base == NULL &&
00365 (!optional || strcmp (field, XMMS_COLLQUERY_DEFAULT_BASE) == 0)) {
00366 alias->id = 0;
00367 query->alias_base = fieldkey;
00368 } else {
00369 alias->id = query->alias_count;
00370 query->alias_count++;
00371 }
00372 }
00373
00374 g_hash_table_insert (query->aliases, fieldkey, alias);
00375
00376
00377 } else if (!alias->optional && optional) {
00378 alias->optional = optional;
00379 }
00380
00381 return alias;
00382 }
00383
00384 static coll_query_alias_t *
00385 query_get_alias (coll_query_t *query, const gchar *field)
00386 {
00387 return g_hash_table_lookup (query->aliases, field);
00388 }
00389
00390
00391 static const gchar *
00392 canonical_field_name (const gchar *field) {
00393 if (*field == '-') {
00394 field++;
00395 } else if (*field == '~') {
00396 field = NULL;
00397 }
00398 return field;
00399 }
00400
00401
00402
00403 static gboolean
00404 operator_is_allmedia (xmmsv_coll_t *op)
00405 {
00406 gchar *target_name;
00407 xmmsv_coll_attribute_get (op, "reference", &target_name);
00408 return (target_name != NULL && strcmp (target_name, "All Media") == 0);
00409 }
00410
00411 static void
00412 query_append_int (coll_query_t *query, gint i)
00413 {
00414 g_string_append_printf (query->conditions, "%d", i);
00415 }
00416
00417 static void
00418 query_append_string (coll_query_t *query, const gchar *s)
00419 {
00420 g_string_append (query->conditions, s);
00421 }
00422
00423 static void
00424 query_append_protect_string (coll_query_t *query, gchar *s)
00425 {
00426 gchar *preps;
00427 if ((preps = sqlite_prepare_string (s)) != NULL) {
00428 query_append_string (query, preps);
00429 g_free (preps);
00430 }
00431 }
00432
00433 static void
00434 query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll)
00435 {
00436 xmmsv_coll_t *op = NULL;
00437 gchar *target_name;
00438 gchar *target_ns;
00439 guint target_nsid;
00440
00441 if (!xmmsv_list_get_coll (xmmsv_coll_operands_get (coll), 0, &op)) {
00442
00443
00444 if (xmmsv_coll_attribute_get (coll, "reference", &target_name) &&
00445 xmmsv_coll_attribute_get (coll, "namespace", &target_ns)) {
00446
00447 target_nsid = xmms_collection_get_namespace_id (target_ns);
00448 op = xmms_collection_get_pointer (dag, target_name, target_nsid);
00449 }
00450 }
00451
00452
00453 if (op != NULL) {
00454 xmms_collection_append_to_query (dag, op, query);
00455
00456
00457 } else {
00458 query_append_string (query, "1");
00459 }
00460 }
00461
00462 static void
00463 query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag,
00464 xmmsv_coll_t *coll)
00465 {
00466 xmmsv_coll_t *op;
00467 xmmsv_t *tmp;
00468
00469 if (xmmsv_list_get (xmmsv_coll_operands_get (coll), 0, &tmp)) {
00470 xmmsv_get_coll (tmp, &op);
00471
00472 if (!operator_is_allmedia (op)) {
00473 query_append_string (query, " AND ");
00474 xmms_collection_append_to_query (dag, op, query);
00475 }
00476 }
00477 }
00478
00479
00480 static void
00481 query_append_filter (coll_query_t *query, xmmsv_coll_type_t type,
00482 gchar *key, gchar *value, gboolean case_sens)
00483 {
00484 coll_query_alias_t *alias;
00485 gboolean optional;
00486 gchar *temp;
00487 gint i;
00488
00489 if (type == XMMS_COLLECTION_TYPE_HAS) {
00490 optional = TRUE;
00491 } else {
00492 optional = FALSE;
00493 }
00494
00495 alias = query_make_alias (query, key, optional);
00496
00497 switch (type) {
00498
00499 case XMMS_COLLECTION_TYPE_EQUALS:
00500 case XMMS_COLLECTION_TYPE_MATCH:
00501 if (case_sens) {
00502 query_string_append_alias (query->conditions, alias,
00503 COLL_QUERY_VALUE_TYPE_STRING);
00504 } else {
00505 query_append_string (query, "(");
00506 query_string_append_alias (query->conditions, alias,
00507 COLL_QUERY_VALUE_TYPE_STRING);
00508 query_append_string (query, " COLLATE NOCASE)");
00509 }
00510
00511 if (type == XMMS_COLLECTION_TYPE_EQUALS) {
00512 query_append_string (query, "=");
00513 } else {
00514 if (case_sens) {
00515 query_append_string (query, " GLOB ");
00516 } else {
00517 query_append_string (query, " LIKE ");
00518 }
00519 }
00520
00521 if (type == XMMS_COLLECTION_TYPE_MATCH && !case_sens) {
00522 temp = g_strdup(value);
00523 for (i = 0; temp[i]; i++) {
00524 switch (temp[i]) {
00525 case '*': temp[i] = '%'; break;
00526 case '?': temp[i] = '_'; break;
00527 default : break;
00528 }
00529 }
00530 query_append_protect_string (query, temp);
00531 g_free(temp);
00532 } else {
00533 query_append_protect_string (query, value);
00534 }
00535 break;
00536
00537
00538 case XMMS_COLLECTION_TYPE_SMALLER:
00539 case XMMS_COLLECTION_TYPE_GREATER:
00540 query_string_append_alias (query->conditions, alias,
00541 COLL_QUERY_VALUE_TYPE_INT);
00542 if (type == XMMS_COLLECTION_TYPE_SMALLER) {
00543 query_append_string (query, " < ");
00544 } else {
00545 query_append_string (query, " > ");
00546 }
00547 query_append_string (query, value);
00548 break;
00549
00550 case XMMS_COLLECTION_TYPE_HAS:
00551 query_string_append_alias (query->conditions, alias,
00552 COLL_QUERY_VALUE_TYPE_STRING);
00553 query_append_string (query, " is not null");
00554 break;
00555
00556
00557 default:
00558 g_assert_not_reached ();
00559 break;
00560 }
00561 }
00562
00563
00564 static void
00565 query_string_append_joins (gpointer key, gpointer val, gpointer udata)
00566 {
00567 gchar *field;
00568 GString *qstring;
00569 coll_query_alias_t *alias;
00570
00571 field = key;
00572 qstring = (GString*)udata;
00573 alias = (coll_query_alias_t*)val;
00574
00575 if ((alias->id > 0) && (alias->type == XMMS_QUERY_ALIAS_PROP)) {
00576 if (alias->optional) {
00577 g_string_append_printf (qstring, " LEFT");
00578 }
00579
00580 g_string_append_printf (qstring,
00581 " JOIN Media AS m%u ON m0.id=m%u.id AND m%u.key='%s' AND"
00582 " xmms_source_pref (m%u.source) = "
00583 "(SELECT MIN (xmms_source_pref (n.source)) FROM Media AS n"
00584 " WHERE n.id = m0.id AND n.key = '%s')",
00585 alias->id, alias->id, alias->id, field, alias->id, field);
00586 }
00587 }
00588
00589
00590 static void
00591 query_string_append_alias_list (coll_query_t *query, GString *qstring,
00592 xmmsv_t *fields)
00593 {
00594 coll_query_alias_t *alias;
00595 xmmsv_list_iter_t *it;
00596 xmmsv_t *valstr;
00597 gboolean first = TRUE;
00598
00599 for (xmmsv_get_list_iter (fields, &it);
00600 xmmsv_list_iter_valid (it);
00601 xmmsv_list_iter_next (it)) {
00602
00603
00604 const gchar *field, *canon_field;
00605 xmmsv_list_iter_entry (it, &valstr);
00606 xmmsv_get_string (valstr, &field);
00607 canon_field = canonical_field_name (field);
00608
00609 if (first) first = FALSE;
00610 else {
00611 g_string_append (qstring, ", ");
00612 }
00613
00614 if (canon_field != NULL) {
00615 alias = query_get_alias (query, canon_field);
00616 if (alias != NULL) {
00617 query_string_append_alias (qstring, alias,
00618 COLL_QUERY_VALUE_TYPE_BOTH);
00619 } else {
00620 if (*field != '~') {
00621 if (strcmp(canon_field, "id") == 0) {
00622 g_string_append (qstring, "m0.id");
00623 } else {
00624 g_string_append_printf (qstring,
00625 "(SELECT IFNULL (intval, value) "
00626 "FROM Media WHERE id = m0.id AND key='%s' AND "
00627 "xmms_source_pref (source) = "
00628 "(SELECT MIN (xmms_source_pref (n.source)) "
00629 "FROM Media AS n WHERE n.id = m0.id AND "
00630 "n.key = '%s'))",
00631 canon_field, canon_field);
00632 }
00633 }
00634 }
00635 }
00636
00637
00638 if (*field == '-') {
00639 g_string_append (qstring, " DESC");
00640 } else if (*field == '~') {
00641
00642 g_string_append (qstring, field + 1);
00643 }
00644 }
00645 }
00646
00647 static void
00648 query_string_append_fetch (coll_query_t *query, GString *qstring)
00649 {
00650 coll_query_alias_t *alias;
00651 xmmsv_list_iter_t *it;
00652 xmmsv_t *valstr;
00653 gboolean first = TRUE;
00654 const gchar *name;
00655
00656 for (xmmsv_get_list_iter (query->params->fetch, &it);
00657 xmmsv_list_iter_valid (it);
00658 xmmsv_list_iter_next (it)) {
00659
00660
00661 xmmsv_list_iter_entry (it, &valstr);
00662 xmmsv_get_string (valstr, &name);
00663 alias = query_make_alias (query, name, TRUE);
00664
00665 if (first) first = FALSE;
00666 else {
00667 g_string_append (qstring, ", ");
00668 }
00669
00670 query_string_append_alias (qstring, alias,
00671 COLL_QUERY_VALUE_TYPE_BOTH);
00672 g_string_append_printf (qstring, " AS %s", name);
00673 }
00674 }
00675
00676 static void
00677 query_string_append_alias (GString *qstring, coll_query_alias_t *alias,
00678 coll_query_value_type_t type)
00679 {
00680 switch (alias->type) {
00681 case XMMS_QUERY_ALIAS_PROP:
00682 switch (type) {
00683 case COLL_QUERY_VALUE_TYPE_STRING:
00684 g_string_append_printf (qstring, "m%u.value", alias->id);
00685 break;
00686 case COLL_QUERY_VALUE_TYPE_INT:
00687 g_string_append_printf (qstring, "m%u.intval", alias->id);
00688 break;
00689 case COLL_QUERY_VALUE_TYPE_BOTH:
00690 g_string_append_printf (qstring, "IFNULL (m%u.intval, m%u.value)",
00691 alias->id, alias->id);
00692 break;
00693 }
00694 break;
00695
00696 case XMMS_QUERY_ALIAS_ID:
00697 g_string_append (qstring, "m0.id");
00698 break;
00699
00700 default:
00701 break;
00702 }
00703 }
00704
00705
00706
00707