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
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
00085
00086
00087
00088
00089
00090
00091
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 (¶ms);
00102 xmms_collection_append_to_query (dag, coll, query);
00103 add_fetch_group_aliases (query, ¶ms);
00104
00105 qstring = xmms_collection_gen_query (query);
00106
00107 destroy_query (query);
00108
00109 return qstring;
00110 }
00111
00112
00113
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
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
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
00163 static GString*
00164 xmms_collection_gen_query (coll_query_t *query)
00165 {
00166 GString *qstring;
00167
00168
00169 if (query->alias_base == NULL) {
00170 query_make_alias (query, XMMS_COLLQUERY_DEFAULT_BASE, FALSE);
00171 }
00172
00173
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
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
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
00192
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
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
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
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
00296 default:
00297 XMMS_DBG ("Cannot append invalid collection operator!");
00298 g_assert_not_reached ();
00299 break;
00300 }
00301
00302 }
00303
00304
00305
00306
00307
00308
00309
00310
00311
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
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
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
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
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
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) {
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
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
00424 if (op != NULL) {
00425 xmms_collection_append_to_query (dag, op, query);
00426
00427
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
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
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
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
00524 default:
00525 g_assert_not_reached ();
00526 break;
00527 }
00528 }
00529
00530
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
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
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
00597 if (*field == '-') {
00598 g_string_append (qstring, " DESC");
00599 } else if (*field == '~') {
00600
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
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