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_uint (coll_query_t *query, guint 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 gint i;
00234 xmmsv_coll_t *op;
00235 guint *idlist;
00236 gchar *attr1, *attr2, *attr3;
00237 gboolean case_sens;
00238 xmmsv_list_iter_t *iter;
00239 xmmsv_t *tmp;
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 i = 0;
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 (i != 0) {
00263 if (type == XMMS_COLLECTION_TYPE_UNION)
00264 query_append_string (query, " OR ");
00265 else
00266 query_append_string (query, " AND ");
00267 } else {
00268 i = 1;
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 idlist = xmmsv_coll_get_idlist (coll);
00305 query_append_string (query, "m0.id IN (");
00306 for (i = 0; idlist[i] != 0; ++i) {
00307 if (i != 0) {
00308 query_append_string (query, ",");
00309 }
00310 query_append_uint (query, idlist[i]);
00311 }
00312 query_append_string (query, ")");
00313 break;
00314
00315
00316 default:
00317 XMMS_DBG ("Cannot append invalid collection operator!");
00318 g_assert_not_reached ();
00319 break;
00320 }
00321
00322 }
00323
00324
00325
00326
00327
00328
00329
00330
00331
00332
00333 static coll_query_alias_t *
00334 query_make_alias (coll_query_t *query, const gchar *field, gboolean optional)
00335 {
00336 coll_query_alias_t *alias;
00337 alias = g_hash_table_lookup (query->aliases, field);
00338
00339
00340 if (alias == NULL) {
00341 gchar *fieldkey = g_strdup (field);
00342
00343 alias = g_new (coll_query_alias_t, 1);
00344 alias->optional = optional;
00345 alias->id = 0;
00346
00347 if (strcmp (field, "id") == 0) {
00348 alias->type = XMMS_QUERY_ALIAS_ID;
00349 } else {
00350 alias->type = XMMS_QUERY_ALIAS_PROP;
00351
00352
00353 if (query->alias_base == NULL &&
00354 (!optional || strcmp (field, XMMS_COLLQUERY_DEFAULT_BASE) == 0)) {
00355 alias->id = 0;
00356 query->alias_base = fieldkey;
00357 } else {
00358 alias->id = query->alias_count;
00359 query->alias_count++;
00360 }
00361 }
00362
00363 g_hash_table_insert (query->aliases, fieldkey, alias);
00364
00365
00366 } else if (!alias->optional && optional) {
00367 alias->optional = optional;
00368 }
00369
00370 return alias;
00371 }
00372
00373 static coll_query_alias_t *
00374 query_get_alias (coll_query_t *query, const gchar *field)
00375 {
00376 return g_hash_table_lookup (query->aliases, field);
00377 }
00378
00379
00380 static const gchar *
00381 canonical_field_name (const gchar *field) {
00382 if (*field == '-') {
00383 field++;
00384 } else if (*field == '~') {
00385 field = NULL;
00386 }
00387 return field;
00388 }
00389
00390
00391
00392 static gboolean
00393 operator_is_allmedia (xmmsv_coll_t *op)
00394 {
00395 gchar *target_name;
00396 xmmsv_coll_attribute_get (op, "reference", &target_name);
00397 return (target_name != NULL && strcmp (target_name, "All Media") == 0);
00398 }
00399
00400 static void
00401 query_append_uint (coll_query_t *query, guint i)
00402 {
00403 g_string_append_printf (query->conditions, "%u", i);
00404 }
00405
00406 static void
00407 query_append_string (coll_query_t *query, const gchar *s)
00408 {
00409 g_string_append (query->conditions, s);
00410 }
00411
00412 static void
00413 query_append_protect_string (coll_query_t *query, gchar *s)
00414 {
00415 gchar *preps;
00416 if ((preps = sqlite_prepare_string (s)) != NULL) {
00417 query_append_string (query, preps);
00418 g_free (preps);
00419 }
00420 }
00421
00422 static void
00423 query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsv_coll_t *coll)
00424 {
00425 xmmsv_coll_t *op = NULL;
00426 gchar *target_name;
00427 gchar *target_ns;
00428 guint target_nsid;
00429
00430 if (!xmmsv_list_get_coll (xmmsv_coll_operands_get (coll), 0, &op)) {
00431
00432
00433 if (xmmsv_coll_attribute_get (coll, "reference", &target_name) &&
00434 xmmsv_coll_attribute_get (coll, "namespace", &target_ns)) {
00435
00436 target_nsid = xmms_collection_get_namespace_id (target_ns);
00437 op = xmms_collection_get_pointer (dag, target_name, target_nsid);
00438 }
00439 }
00440
00441
00442 if (op != NULL) {
00443 xmms_collection_append_to_query (dag, op, query);
00444
00445
00446 } else {
00447 query_append_string (query, "1");
00448 }
00449 }
00450
00451 static void
00452 query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag,
00453 xmmsv_coll_t *coll)
00454 {
00455 xmmsv_coll_t *op;
00456 xmmsv_t *tmp;
00457
00458 if (xmmsv_list_get (xmmsv_coll_operands_get (coll), 0, &tmp)) {
00459 xmmsv_get_coll (tmp, &op);
00460
00461 if (!operator_is_allmedia (op)) {
00462 query_append_string (query, " AND ");
00463 xmms_collection_append_to_query (dag, op, query);
00464 }
00465 }
00466 }
00467
00468
00469 static void
00470 query_append_filter (coll_query_t *query, xmmsv_coll_type_t type,
00471 gchar *key, gchar *value, gboolean case_sens)
00472 {
00473 coll_query_alias_t *alias;
00474 gboolean optional;
00475 gchar *temp;
00476 gint i;
00477
00478 if (type == XMMS_COLLECTION_TYPE_HAS) {
00479 optional = TRUE;
00480 } else {
00481 optional = FALSE;
00482 }
00483
00484 alias = query_make_alias (query, key, optional);
00485
00486 switch (type) {
00487
00488 case XMMS_COLLECTION_TYPE_EQUALS:
00489 case XMMS_COLLECTION_TYPE_MATCH:
00490 if (case_sens) {
00491 query_string_append_alias (query->conditions, alias,
00492 COLL_QUERY_VALUE_TYPE_STRING);
00493 } else {
00494 query_append_string (query, "(");
00495 query_string_append_alias (query->conditions, alias,
00496 COLL_QUERY_VALUE_TYPE_STRING);
00497 query_append_string (query, " COLLATE NOCASE)");
00498 }
00499
00500 if (type == XMMS_COLLECTION_TYPE_EQUALS) {
00501 query_append_string (query, "=");
00502 } else {
00503 if (case_sens) {
00504 query_append_string (query, " GLOB ");
00505 } else {
00506 query_append_string (query, " LIKE ");
00507 }
00508 }
00509
00510 if (type == XMMS_COLLECTION_TYPE_MATCH && !case_sens) {
00511 temp = g_strdup(value);
00512 for (i = 0; temp[i]; i++) {
00513 switch (temp[i]) {
00514 case '*': temp[i] = '%'; break;
00515 case '?': temp[i] = '_'; break;
00516 default : break;
00517 }
00518 }
00519 query_append_protect_string (query, temp);
00520 g_free(temp);
00521 } else {
00522 query_append_protect_string (query, value);
00523 }
00524 break;
00525
00526
00527 case XMMS_COLLECTION_TYPE_SMALLER:
00528 case XMMS_COLLECTION_TYPE_GREATER:
00529 query_string_append_alias (query->conditions, alias,
00530 COLL_QUERY_VALUE_TYPE_INT);
00531 if (type == XMMS_COLLECTION_TYPE_SMALLER) {
00532 query_append_string (query, " < ");
00533 } else {
00534 query_append_string (query, " > ");
00535 }
00536 query_append_string (query, value);
00537 break;
00538
00539 case XMMS_COLLECTION_TYPE_HAS:
00540 query_string_append_alias (query->conditions, alias,
00541 COLL_QUERY_VALUE_TYPE_STRING);
00542 query_append_string (query, " is not null");
00543 break;
00544
00545
00546 default:
00547 g_assert_not_reached ();
00548 break;
00549 }
00550 }
00551
00552
00553 static void
00554 query_string_append_joins (gpointer key, gpointer val, gpointer udata)
00555 {
00556 gchar *field;
00557 GString *qstring;
00558 coll_query_alias_t *alias;
00559
00560 field = key;
00561 qstring = (GString*)udata;
00562 alias = (coll_query_alias_t*)val;
00563
00564 if ((alias->id > 0) && (alias->type == XMMS_QUERY_ALIAS_PROP)) {
00565 if (alias->optional) {
00566 g_string_append_printf (qstring, " LEFT");
00567 }
00568
00569 g_string_append_printf (qstring,
00570 " JOIN Media AS m%u ON m0.id=m%u.id AND m%u.key='%s' AND"
00571 " xmms_source_pref (m%u.source) = "
00572 "(SELECT MIN (xmms_source_pref (n.source)) FROM Media AS n"
00573 " WHERE n.id = m0.id AND n.key = '%s')",
00574 alias->id, alias->id, alias->id, field, alias->id, field);
00575 }
00576 }
00577
00578
00579 static void
00580 query_string_append_alias_list (coll_query_t *query, GString *qstring,
00581 xmmsv_t *fields)
00582 {
00583 coll_query_alias_t *alias;
00584 xmmsv_list_iter_t *it;
00585 xmmsv_t *valstr;
00586 gboolean first = TRUE;
00587
00588 for (xmmsv_get_list_iter (fields, &it);
00589 xmmsv_list_iter_valid (it);
00590 xmmsv_list_iter_next (it)) {
00591
00592
00593 const gchar *field, *canon_field;
00594 xmmsv_list_iter_entry (it, &valstr);
00595 xmmsv_get_string (valstr, &field);
00596 canon_field = canonical_field_name (field);
00597
00598 if (first) first = FALSE;
00599 else {
00600 g_string_append (qstring, ", ");
00601 }
00602
00603 if (canon_field != NULL) {
00604 alias = query_get_alias (query, canon_field);
00605 if (alias != NULL) {
00606 query_string_append_alias (qstring, alias,
00607 COLL_QUERY_VALUE_TYPE_BOTH);
00608 } else {
00609 if (*field != '~') {
00610 if (strcmp(canon_field, "id") == 0) {
00611 g_string_append (qstring, "m0.id");
00612 } else {
00613 g_string_append_printf (qstring,
00614 "(SELECT IFNULL (intval, value) "
00615 "FROM Media WHERE id = m0.id AND key='%s' AND "
00616 "xmms_source_pref (source) = "
00617 "(SELECT MIN (xmms_source_pref (n.source)) "
00618 "FROM Media AS n WHERE n.id = m0.id AND "
00619 "n.key = '%s'))",
00620 canon_field, canon_field);
00621 }
00622 }
00623 }
00624 }
00625
00626
00627 if (*field == '-') {
00628 g_string_append (qstring, " DESC");
00629 } else if (*field == '~') {
00630
00631 g_string_append (qstring, field + 1);
00632 }
00633 }
00634 }
00635
00636 static void
00637 query_string_append_fetch (coll_query_t *query, GString *qstring)
00638 {
00639 coll_query_alias_t *alias;
00640 xmmsv_list_iter_t *it;
00641 xmmsv_t *valstr;
00642 gboolean first = TRUE;
00643 const gchar *name;
00644
00645 for (xmmsv_get_list_iter (query->params->fetch, &it);
00646 xmmsv_list_iter_valid (it);
00647 xmmsv_list_iter_next (it)) {
00648
00649
00650 xmmsv_list_iter_entry (it, &valstr);
00651 xmmsv_get_string (valstr, &name);
00652 alias = query_make_alias (query, name, TRUE);
00653
00654 if (first) first = FALSE;
00655 else {
00656 g_string_append (qstring, ", ");
00657 }
00658
00659 query_string_append_alias (qstring, alias,
00660 COLL_QUERY_VALUE_TYPE_BOTH);
00661 g_string_append_printf (qstring, " AS %s", name);
00662 }
00663 }
00664
00665 static void
00666 query_string_append_alias (GString *qstring, coll_query_alias_t *alias,
00667 coll_query_value_type_t type)
00668 {
00669 switch (alias->type) {
00670 case XMMS_QUERY_ALIAS_PROP:
00671 switch (type) {
00672 case COLL_QUERY_VALUE_TYPE_STRING:
00673 g_string_append_printf (qstring, "m%u.value", alias->id);
00674 break;
00675 case COLL_QUERY_VALUE_TYPE_INT:
00676 g_string_append_printf (qstring, "m%u.intval", alias->id);
00677 break;
00678 case COLL_QUERY_VALUE_TYPE_BOTH:
00679 g_string_append_printf (qstring, "IFNULL (m%u.intval, m%u.value)",
00680 alias->id, alias->id);
00681 break;
00682 }
00683 break;
00684
00685 case XMMS_QUERY_ALIAS_ID:
00686 g_string_append (qstring, "m0.id");
00687 break;
00688
00689 default:
00690 break;
00691 }
00692 }
00693
00694
00695
00696