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 GList *order;
00035 GList *fetch;
00036 GList *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, xmmsc_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, xmmsc_coll_t *coll);
00070 static void query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsc_coll_t *coll);
00071 static void query_append_filter (coll_query_t *query, xmmsc_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, GList *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 gchar *canonical_field_name (gchar *field);
00078 static gboolean operator_is_allmedia (xmmsc_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, xmmsc_coll_t *coll,
00094 guint limit_start, guint limit_len,
00095 GList *order, GList *fetch, GList *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 add_fetch_group_aliases (coll_query_t *query, coll_query_params_t *params)
00137 {
00138 GList *n;
00139
00140
00141 for (n = query->params->group; n; n = n->next) {
00142 query_make_alias (query, n->data, TRUE);
00143 }
00144 for (n = query->params->fetch; n; n = n->next) {
00145 query_make_alias (query, n->data, TRUE);
00146 }
00147 }
00148
00149
00150 static void
00151 destroy_query (coll_query_t* query)
00152 {
00153 g_hash_table_destroy (query->aliases);
00154 g_string_free (query->conditions, TRUE);
00155 g_free (query);
00156 }
00157
00158
00159
00160 static GString*
00161 xmms_collection_gen_query (coll_query_t *query)
00162 {
00163 GString *qstring;
00164
00165
00166 if (query->alias_base == NULL) {
00167 query_make_alias (query, XMMS_COLLQUERY_DEFAULT_BASE, FALSE);
00168 }
00169
00170
00171 qstring = g_string_new ("SELECT DISTINCT ");
00172 query_string_append_fetch (query, qstring);
00173 g_string_append (qstring, " FROM Media as m0");
00174 g_hash_table_foreach (query->aliases, query_string_append_joins, qstring);
00175
00176
00177 g_string_append_printf (qstring, " WHERE m0.key='%s'", query->alias_base);
00178 if (query->conditions->len > 0) {
00179 g_string_append_printf (qstring, " AND %s", query->conditions->str);
00180 }
00181
00182
00183 if (query->params->group != NULL) {
00184 g_string_append (qstring, " GROUP BY ");
00185 query_string_append_alias_list (query, qstring, query->params->group);
00186 }
00187
00188
00189
00190 if (query->params->order != NULL) {
00191 g_string_append (qstring, " ORDER BY ");
00192 query_string_append_alias_list (query, qstring, query->params->order);
00193 }
00194
00195
00196 if (query->params->limit_len != 0) {
00197 if (query->params->limit_start ) {
00198 g_string_append_printf (qstring, " LIMIT %u,%u",
00199 query->params->limit_start,
00200 query->params->limit_len);
00201 } else {
00202 g_string_append_printf (qstring, " LIMIT %u",
00203 query->params->limit_len);
00204 }
00205 }
00206
00207 return qstring;
00208 }
00209
00210
00211 static void
00212 xmms_collection_append_to_query (xmms_coll_dag_t *dag, xmmsc_coll_t *coll,
00213 coll_query_t *query)
00214 {
00215 gint i;
00216 xmmsc_coll_t *op;
00217 guint *idlist;
00218 gchar *attr1, *attr2, *attr3;
00219 gboolean case_sens;
00220
00221 xmmsc_coll_type_t type = xmmsc_coll_get_type (coll);
00222 switch (type) {
00223 case XMMS_COLLECTION_TYPE_REFERENCE:
00224 if (!operator_is_allmedia (coll)) {
00225 query_append_operand (query, dag, coll);
00226 } else {
00227
00228 query_append_string (query, "1");
00229 }
00230 break;
00231
00232 case XMMS_COLLECTION_TYPE_UNION:
00233 case XMMS_COLLECTION_TYPE_INTERSECTION:
00234 i = 0;
00235 query_append_string (query, "(");
00236
00237 xmmsc_coll_operand_list_save (coll);
00238 xmmsc_coll_operand_list_first (coll);
00239 while (xmmsc_coll_operand_list_entry (coll, &op)) {
00240 if (i != 0) {
00241 if (type == XMMS_COLLECTION_TYPE_UNION)
00242 query_append_string (query, " OR ");
00243 else
00244 query_append_string (query, " AND ");
00245 } else {
00246 i = 1;
00247 }
00248 xmms_collection_append_to_query (dag, op, query);
00249 xmmsc_coll_operand_list_next (coll);
00250 }
00251 xmmsc_coll_operand_list_restore (coll);
00252
00253 query_append_string (query, ")");
00254 break;
00255
00256 case XMMS_COLLECTION_TYPE_COMPLEMENT:
00257 query_append_string (query, "NOT ");
00258 query_append_operand (query, dag, coll);
00259 break;
00260
00261 case XMMS_COLLECTION_TYPE_HAS:
00262 case XMMS_COLLECTION_TYPE_EQUALS:
00263 case XMMS_COLLECTION_TYPE_MATCH:
00264 case XMMS_COLLECTION_TYPE_SMALLER:
00265 case XMMS_COLLECTION_TYPE_GREATER:
00266 xmmsc_coll_attribute_get (coll, "field", &attr1);
00267 xmmsc_coll_attribute_get (coll, "value", &attr2);
00268 xmmsc_coll_attribute_get (coll, "case-sensitive", &attr3);
00269 case_sens = (attr3 != NULL && strcmp (attr3, "true") == 0);
00270
00271 query_append_string (query, "(");
00272 query_append_filter (query, type, attr1, attr2, case_sens);
00273
00274 query_append_intersect_operand (query, dag, coll);
00275 query_append_string (query, ")");
00276 break;
00277
00278 case XMMS_COLLECTION_TYPE_IDLIST:
00279 case XMMS_COLLECTION_TYPE_QUEUE:
00280 case XMMS_COLLECTION_TYPE_PARTYSHUFFLE:
00281 idlist = xmmsc_coll_get_idlist (coll);
00282 query_append_string (query, "m0.id IN (");
00283 for (i = 0; idlist[i] != 0; ++i) {
00284 if (i != 0) {
00285 query_append_string (query, ",");
00286 }
00287 query_append_uint (query, idlist[i]);
00288 }
00289 query_append_string (query, ")");
00290 break;
00291
00292
00293 default:
00294 XMMS_DBG ("Cannot append invalid collection operator!");
00295 g_assert_not_reached ();
00296 break;
00297 }
00298
00299 }
00300
00301
00302
00303
00304
00305
00306
00307
00308
00309
00310 static coll_query_alias_t *
00311 query_make_alias (coll_query_t *query, const gchar *field, gboolean optional)
00312 {
00313 coll_query_alias_t *alias;
00314 alias = g_hash_table_lookup (query->aliases, field);
00315
00316
00317 if (alias == NULL) {
00318 gchar *fieldkey = g_strdup (field);
00319
00320 alias = g_new (coll_query_alias_t, 1);
00321 alias->optional = optional;
00322 alias->id = 0;
00323
00324 if (strcmp (field, "id") == 0) {
00325 alias->type = XMMS_QUERY_ALIAS_ID;
00326 } else {
00327 alias->type = XMMS_QUERY_ALIAS_PROP;
00328
00329
00330 if (query->alias_base == NULL &&
00331 (!optional || strcmp (field, XMMS_COLLQUERY_DEFAULT_BASE) == 0)) {
00332 alias->id = 0;
00333 query->alias_base = fieldkey;
00334 } else {
00335 alias->id = query->alias_count;
00336 query->alias_count++;
00337 }
00338 }
00339
00340 g_hash_table_insert (query->aliases, fieldkey, alias);
00341
00342
00343 } else if (!alias->optional && optional) {
00344 alias->optional = optional;
00345 }
00346
00347 return alias;
00348 }
00349
00350 static coll_query_alias_t *
00351 query_get_alias (coll_query_t *query, const gchar *field)
00352 {
00353 return g_hash_table_lookup (query->aliases, field);
00354 }
00355
00356
00357 static gchar *
00358 canonical_field_name (gchar *field) {
00359 if (*field == '-') {
00360 field++;
00361 } else if (*field == '~') {
00362 field = NULL;
00363 }
00364 return field;
00365 }
00366
00367
00368
00369 static gboolean
00370 operator_is_allmedia (xmmsc_coll_t *op)
00371 {
00372 gchar *target_name;
00373 xmmsc_coll_attribute_get (op, "reference", &target_name);
00374 return (target_name != NULL && strcmp (target_name, "All Media") == 0);
00375 }
00376
00377 static void
00378 query_append_uint (coll_query_t *query, guint i)
00379 {
00380 g_string_append_printf (query->conditions, "%u", i);
00381 }
00382
00383 static void
00384 query_append_string (coll_query_t *query, const gchar *s)
00385 {
00386 g_string_append (query->conditions, s);
00387 }
00388
00389 static void
00390 query_append_protect_string (coll_query_t *query, gchar *s)
00391 {
00392 gchar *preps;
00393 if ((preps = sqlite_prepare_string (s)) != NULL) {
00394 query_append_string (query, preps);
00395 g_free (preps);
00396 }
00397 }
00398
00399 static void
00400 query_append_operand (coll_query_t *query, xmms_coll_dag_t *dag, xmmsc_coll_t *coll)
00401 {
00402 xmmsc_coll_t *op;
00403 gchar *target_name;
00404 gchar *target_ns;
00405 guint target_nsid;
00406
00407 xmmsc_coll_operand_list_save (coll);
00408 xmmsc_coll_operand_list_first (coll);
00409 if (!xmmsc_coll_operand_list_entry (coll, &op)) {
00410
00411 if (xmmsc_coll_attribute_get (coll, "reference", &target_name) &&
00412 xmmsc_coll_attribute_get (coll, "namespace", &target_ns)) {
00413
00414 target_nsid = xmms_collection_get_namespace_id (target_ns);
00415 op = xmms_collection_get_pointer (dag, target_name, target_nsid);
00416 }
00417 }
00418 xmmsc_coll_operand_list_restore (coll);
00419
00420
00421 if (op != NULL) {
00422 xmms_collection_append_to_query (dag, op, query);
00423
00424
00425 } else {
00426 query_append_string (query, "1");
00427 }
00428 }
00429
00430 static void
00431 query_append_intersect_operand (coll_query_t *query, xmms_coll_dag_t *dag,
00432 xmmsc_coll_t *coll)
00433 {
00434 xmmsc_coll_t *op;
00435
00436 xmmsc_coll_operand_list_save (coll);
00437 xmmsc_coll_operand_list_first (coll);
00438 if (xmmsc_coll_operand_list_entry (coll, &op)) {
00439 if (!operator_is_allmedia (op)) {
00440 query_append_string (query, " AND ");
00441 xmms_collection_append_to_query (dag, op, query);
00442 }
00443 }
00444 xmmsc_coll_operand_list_restore (coll);
00445 }
00446
00447
00448 static void
00449 query_append_filter (coll_query_t *query, xmmsc_coll_type_t type,
00450 gchar *key, gchar *value, gboolean case_sens)
00451 {
00452 coll_query_alias_t *alias;
00453 gboolean optional;
00454 gchar *temp;
00455 gint i;
00456
00457 if (type == XMMS_COLLECTION_TYPE_HAS) {
00458 optional = TRUE;
00459 } else {
00460 optional = FALSE;
00461 }
00462
00463 alias = query_make_alias (query, key, optional);
00464
00465 switch (type) {
00466
00467 case XMMS_COLLECTION_TYPE_EQUALS:
00468 case XMMS_COLLECTION_TYPE_MATCH:
00469 if (case_sens) {
00470 query_string_append_alias (query->conditions, alias);
00471 } else {
00472 query_append_string (query, "(");
00473 query_string_append_alias (query->conditions, alias);
00474 query_append_string (query, " COLLATE NOCASE)");
00475 }
00476
00477 if (type == XMMS_COLLECTION_TYPE_EQUALS) {
00478 query_append_string (query, "=");
00479 } else {
00480 if (case_sens) {
00481 query_append_string (query, " GLOB ");
00482 } else {
00483 query_append_string (query, " LIKE ");
00484 }
00485 }
00486
00487 if (type == XMMS_COLLECTION_TYPE_MATCH && !case_sens) {
00488 temp = g_strdup(value);
00489 for (i = 0; temp[i]; i++) {
00490 switch (temp[i]) {
00491 case '*': temp[i] = '%'; break;
00492 case '?': temp[i] = '_'; break;
00493 default : break;
00494 }
00495 }
00496 query_append_protect_string (query, temp);
00497 g_free(temp);
00498 } else {
00499 query_append_protect_string (query, value);
00500 }
00501 break;
00502
00503
00504 case XMMS_COLLECTION_TYPE_SMALLER:
00505 case XMMS_COLLECTION_TYPE_GREATER:
00506 query_string_append_alias (query->conditions, alias);
00507 if (type == XMMS_COLLECTION_TYPE_SMALLER) {
00508 query_append_string (query, " < ");
00509 } else {
00510 query_append_string (query, " > ");
00511 }
00512 query_append_string (query, value);
00513 break;
00514
00515 case XMMS_COLLECTION_TYPE_HAS:
00516 query_string_append_alias (query->conditions, alias);
00517 query_append_string (query, " is not null");
00518 break;
00519
00520
00521 default:
00522 g_assert_not_reached ();
00523 break;
00524 }
00525 }
00526
00527
00528 static void
00529 query_string_append_joins (gpointer key, gpointer val, gpointer udata)
00530 {
00531 gchar *field;
00532 GString *qstring;
00533 coll_query_alias_t *alias;
00534
00535 field = key;
00536 qstring = (GString*)udata;
00537 alias = (coll_query_alias_t*)val;
00538
00539 if ((alias->id > 0) && (alias->type == XMMS_QUERY_ALIAS_PROP)) {
00540 if (alias->optional) {
00541 g_string_append_printf (qstring, " LEFT");
00542 }
00543
00544 g_string_append_printf (qstring,
00545 " JOIN Media as m%u ON m0.id=m%u.id"
00546 " AND m%u.key='%s'",
00547 alias->id, alias->id, alias->id, field);
00548 }
00549 }
00550
00551
00552 static void
00553 query_string_append_alias_list (coll_query_t *query, GString *qstring, GList *fields)
00554 {
00555 GList *n;
00556 gboolean first = TRUE;
00557
00558 for (n = fields; n; n = n->next) {
00559 coll_query_alias_t *alias;
00560 gchar *field = n->data;
00561 gchar *canon_field = canonical_field_name (field);
00562
00563 if (first) first = FALSE;
00564 else {
00565 g_string_append (qstring, ", ");
00566 }
00567
00568 if (canon_field != NULL) {
00569 alias = query_get_alias (query, canon_field);
00570 if (alias != NULL) {
00571 query_string_append_alias (qstring, alias);
00572 } else {
00573 if (*field != '~') {
00574 if (strcmp(canon_field, "id") == 0) {
00575 g_string_append (qstring, "m0.id");
00576 } else {
00577 g_string_append_printf (qstring,
00578 "(SELECT value FROM Media WHERE id = m0.id AND "
00579 "key='%s')", canon_field);
00580 }
00581 }
00582 }
00583 }
00584
00585
00586 if (*field == '-') {
00587 g_string_append (qstring, " DESC");
00588 } else if (*field == '~') {
00589
00590 g_string_append (qstring, field + 1);
00591 }
00592 }
00593 }
00594
00595 static void
00596 query_string_append_fetch (coll_query_t *query, GString *qstring)
00597 {
00598 GList *n;
00599 coll_query_alias_t *alias;
00600 gboolean first = TRUE;
00601
00602 for (n = query->params->fetch; n; n = n->next) {
00603 alias = query_make_alias (query, n->data, TRUE);
00604
00605 if (first) first = FALSE;
00606 else {
00607 g_string_append (qstring, ", ");
00608 }
00609
00610 query_string_append_alias (qstring, alias);
00611 g_string_append_printf (qstring, " AS %s", (gchar*)n->data);
00612 }
00613 }
00614
00615 static void
00616 query_string_append_alias (GString *qstring, coll_query_alias_t *alias)
00617 {
00618 switch (alias->type) {
00619 case XMMS_QUERY_ALIAS_PROP:
00620 g_string_append_printf (qstring, "m%u.value", alias->id);
00621 break;
00622
00623 case XMMS_QUERY_ALIAS_ID:
00624 g_string_append (qstring, "m0.id");
00625 break;
00626
00627 default:
00628 break;
00629 }
00630 }
00631
00632
00633
00634