1 /*****************************************************************************
2 * sql_search.c: SQL-based media library: all find/get functions
3 *****************************************************************************
4 * Copyright (C) 2008-2010 the VideoLAN team and AUTHORS
7 * Authors: Antoine Lejeune <phytos@videolan.org>
8 * Jean-Philippe André <jpeg@videolan.org>
9 * Rémi Duraffort <ivoire@videolan.org>
10 * Adrien Maglo <magsoft@videolan.org>
11 * Srikanth Raju <srikiraju at gmail dot com>
13 * This program is free software; you can redistribute it and/or modify
14 * it under the terms of the GNU General Public License as published by
15 * the Free Software Foundation; either version 2 of the License, or
16 * (at your option) any later version.
18 * This program is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License
24 * along with this program; if not, write to the Free Software
25 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
26 *****************************************************************************/
28 #include "sql_media_library.h"
30 int Find( media_library_t *p_ml, vlc_array_t *p_result_array, ... )
35 va_start( args, p_result_array );
36 returned = FindVa( p_ml, p_result_array, args );
43 * @brief Generic find in Media Library, returns arrays of psz or int
45 * @param p_ml the media library object
46 * @param result A pointer to a result array
47 * @param criterias list of criterias used in SELECT
48 * @return VLC_SUCCESS or VLC_EGENERIC
50 int FindVa( media_library_t *p_ml,
51 vlc_array_t *p_result_array, va_list criterias )
53 int i_ret = VLC_SUCCESS;
55 ml_result_type_e result_type;
56 char **pp_results = NULL;
62 i_ret = BuildSelectVa( p_ml, &psz_query, &result_type, criterias );
63 if( i_ret != VLC_SUCCESS )
66 if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
69 msg_Err( p_ml, "Error occured while making the query to the database" );
73 i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
77 FreeSQLResult( p_ml, pp_results );
83 * @brief Generic find in Media Library, returns arrays of psz or int
85 * @param p_ml the media library object
86 * @param result a pointer to a result array
87 * @param selected_type the type of the element we're selecting
88 * @param criterias list of criterias used in SELECT
89 * @return VLC_SUCCESS or VLC_EGENERIC
92 int FindAdv( media_library_t *p_ml, vlc_array_t *p_result_array,
93 ml_select_e selected_type, const char* psz_lvalue, ml_ftree_t *tree )
95 int i_ret = VLC_SUCCESS;
97 ml_result_type_e result_type;
98 char **pp_results = NULL;
101 if( !p_result_array )
104 i_ret = BuildSelect( p_ml, &psz_query, &result_type, psz_lvalue,
105 selected_type, tree );
107 if( i_ret != VLC_SUCCESS )
110 if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
113 msg_Err( p_ml, "Error occured while making the query to the database" );
117 i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
121 FreeSQLResult( p_ml, pp_results );
127 * @brief Generic SELECT query builder with va_list parameter
129 * @param p_ml This media_library_t object
130 * @param ppsz_query *ppsz_query will contain query
131 * @param p_result_type see enum ml_result_type_e
132 * @param criterias list of criterias used in SELECT
133 * @return VLC_SUCCESS or a VLC error code
134 * NOTE va_list criterias must end with ML_END or this will fail (segfault)
136 * This function handles results of only one column (or two if ID is included),
137 * of 'normal' types: int and strings
139 int BuildSelectVa( media_library_t *p_ml, char **ppsz_query,
140 ml_result_type_e *p_result_type, va_list criterias )
143 ml_ftree_t* p_ftree = NULL;
144 char* psz_lvalue = NULL;
146 /* Get the name of the data we want */
147 ml_select_e selected_type = va_arg( criterias, int );
148 if( selected_type == ML_PEOPLE || selected_type == ML_PEOPLE_ID ||
149 selected_type == ML_PEOPLE_ROLE )
150 psz_lvalue = va_arg( criterias, char * );
152 /* Loop on every arguments */
155 ml_ftree_t *p_find = ( ml_ftree_t* ) calloc( 1, sizeof( ml_ftree_t ) );
158 p_find->criteria = va_arg( criterias, int );
159 p_find->comp = ML_COMP_EQUAL;
160 switch( p_find->criteria )
163 p_ftree = ml_FtreeSpecAsc( p_ftree, va_arg( criterias, char* ) ); break;
165 p_ftree = ml_FtreeSpecDesc( p_ftree, va_arg( criterias, char* ) ); break;
167 p_ftree = ml_FtreeSpecDistinct( p_ftree ); break;
169 p_ftree = ml_FtreeSpecLimit( p_ftree, va_arg( criterias, int ) );
172 /* This is OK because of a shallow free find */
173 p_find->lvalue.str = (char *)ML_PERSON_ARTIST;
174 p_find->value.str = va_arg( criterias, char* );
175 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
178 p_find->lvalue.str = va_arg( criterias, char* );
179 p_find->value.str = va_arg( criterias, char* );
180 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
183 p_find->lvalue.str = va_arg( criterias, char* );
184 p_find->value.i = va_arg( criterias, int );
185 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
191 switch( ml_AttributeIsString( p_find->criteria ) )
194 p_find->value.i = va_arg( criterias, int );
197 p_find->value.str = va_arg( criterias, char* );
200 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
205 int i_ret = BuildSelect( p_ml, ppsz_query, p_result_type, psz_lvalue,
206 selected_type, p_ftree );
208 ml_ShallowFreeFindTree( p_ftree );
213 * @brief Append a string and format it using SQL vmprintf
215 static int AppendStringFmtVa( media_library_t *p_ml,
216 char **ppsz_dst, const char *psz_fmt,
219 char *psz_tmp = NULL, *psz_fullexp = NULL;
221 assert( ppsz_dst != NULL );
226 *ppsz_dst = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
232 /* Create new expression B */
233 psz_tmp = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
237 if( asprintf( &psz_fullexp, "%s%s", *ppsz_dst, psz_tmp ) == -1 )
244 *ppsz_dst = psz_fullexp;
250 static int AppendStringFmt( media_library_t *p_ml,
251 char **ppsz_dst, const char *psz_fmt, ... )
254 va_start( args, psz_fmt );
255 int i_ret = AppendStringFmtVa( p_ml, ppsz_dst, psz_fmt, args );
260 /* Early Declaration of Where String Generator */
261 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t* tree,
262 char** sort, int* limit, const char** distinct, char*** pppsz_frompersons,
263 int* i_frompersons, int* join );
265 # define table_media (1 << 0)
266 # define table_album (1 << 1)
267 # define table_people (1 << 2)
268 # define table_extra (1 << 3)
270 static void PackFromPersons( char*** pppsz_frompersons, int i_num_frompersons )
272 for( int i = 0; i < i_num_frompersons; i++ )
274 if( *pppsz_frompersons[i] == NULL )
276 for( int j = i+1; j < i_num_frompersons; j++ )
278 if( strcmp( *pppsz_frompersons[i], *pppsz_frompersons[j] ) == 0 )
280 *pppsz_frompersons[j] = NULL;
286 * @brief Generic SELECT query builder
288 * @param p_ml This media_library_t object
289 * @param ppsz_query *ppsz_query will contain query
290 * @param p_result_type see enum ml_result_type_e
291 * @param selected_type the type of the element we're selecting
292 * @param tree the find tree
293 * @return VLC_SUCCESS or VLC_EGENERIC
295 int BuildSelect( media_library_t *p_ml,
296 char **ppsz_query, ml_result_type_e *p_result_type,
297 const char *psz_selected_type_lvalue, ml_select_e selected_type,
300 /* Basic verification */
304 int i_ret = VLC_SUCCESS;
305 char *psz_query = NULL;
307 /* Building psz_query :
308 psz_query = "SELECT psz_distinct psz_select
309 FROM psz_from [JOIN psz_join ON psz_on]
310 [JOIN psz_join2 ON psz_on2]
311 [WHERE psz_where[i] [AND psz_where[j] ...]]
312 [LIMIT psz_limit] [ORDER BY psz_select psz_sort] ;"
314 char *psz_select = NULL;
315 const char *psz_distinct = ""; /* "DISTINCT" or "" */
318 char *psz_from = NULL;
319 int i_from = 0; /* Main select table */
321 char **ppsz_frompersons = NULL;
322 int i_num_frompersons = 0;
323 char *psz_peoplerole = NULL; /* Person to get selected */
325 /* JOIN ... ON ... */
326 char *psz_join = NULL;
327 char *psz_join2 = NULL;
329 char *psz_on2 = NULL;
330 int i_join = 0; /* Tables that need to be joined */
333 char *psz_where = NULL;
334 char *psz_sort = NULL; /* ASC or DESC or NULL */
335 char *psz_tmp = NULL;
339 /* Build the WHERE condition */
340 BuildWhere( p_ml, &psz_where, tree, &psz_sort, &i_limit,
341 &psz_distinct, &ppsz_frompersons, &i_num_frompersons, &i_join );
343 PackFromPersons( &ppsz_frompersons, i_num_frompersons );
345 /* What is the result type? */
346 ml_result_type_e res_type = ML_TYPE_PSZ;
349 /* Note that a DISTINCT select makes id of result non sense */
350 switch( selected_type )
353 psz_select = ( !*psz_distinct ) ?
354 strdup( "album.id, album.title AS album_title" )
355 : strdup( "album.title AS album_title" );
356 i_from = table_album;
359 psz_select = ( !*psz_distinct ) ?
360 strdup( "album.id, album.cover" ) : strdup( "album.cover" );
361 i_from = table_album;
364 psz_select = strdup( "album.id" );
365 psz_distinct = "DISTINCT";
366 i_from = table_album;
367 res_type = ML_TYPE_INT;
370 psz_select = ( !*psz_distinct ) ?
371 strdup( "people_Artist.id, people_Artist.name" )
372 : strdup( "people_Artist.name" );
373 i_from = table_people;
374 psz_peoplerole = strdup( ML_PERSON_ARTIST );
377 psz_select = strdup( "people_Artist.id" );
378 psz_distinct = "DISTINCT";
379 i_from = table_people;
380 res_type = ML_TYPE_INT;
381 psz_peoplerole = strdup( ML_PERSON_ARTIST );
384 psz_select = ( !*psz_distinct ) ?
385 strdup( "media.id, media.cover" ) : strdup( "media.cover" );
386 i_from = table_media;
389 psz_select = ( !*psz_distinct ) ?
390 strdup( "media.id, extra.comment" ) : strdup( "extra.comment" );
391 i_from = table_extra;
394 psz_select = ( !*psz_distinct ) ?
395 strdup( "media.id, media.genre" ) : strdup( "media.genre" );
396 i_from = table_media;
399 psz_select = ( !*psz_distinct ) ?
400 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT media.id )" );
401 i_from = table_media;
402 res_type = ML_TYPE_INT;
405 psz_select = ( !*psz_distinct ) ?
406 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT album.id )" );
407 i_from = table_album;
408 res_type = ML_TYPE_INT;
410 case ML_COUNT_PEOPLE:
411 psz_select = ( !*psz_distinct ) ?
412 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT people.id )" );
413 i_from = table_people;
414 res_type = ML_TYPE_INT;
417 psz_select = strdup( "media.filesize" );
418 i_from = table_media;
419 res_type = ML_TYPE_INT;
422 psz_select = strdup( "media.id" ); /* ID: must be distinct */
423 psz_distinct = "DISTINCT";
424 i_from = table_media;
425 res_type = ML_TYPE_INT;
428 psz_select = strdup( "extra.language" );
429 psz_distinct = "DISTINCT";
430 i_from = table_extra;
432 case ML_MEDIA_SPARSE:
433 i_ret = AppendStringFmt( p_ml, &psz_select, "media.id AS id,"
435 "media.type AS type,"
436 "media.title AS title,"
437 "media.duration AS duration,"
438 "media.original_title AS original_title,"
439 "media.album_id AS album_id,"
440 "media.cover AS cover,"
441 "media.preview AS preview,"
442 "media.disc AS disc,"
443 "media.track AS track,"
444 "media.year AS year,"
445 "media.genre AS genre,"
446 "media.played_count AS played_count,"
447 "media.last_played AS last_played,"
448 "media.first_played AS first_played,"
449 "media.import_time AS import_time,"
450 "media.skipped_count AS skipped_count,"
451 "media.last_skipped AS last_skipped,"
452 "media.vote AS vote,"
453 "media.score AS score,"
454 "media.comment AS comment,"
455 "media.filesize AS filesize,"
456 "album.title AS album_title,"
457 "album.cover AS album_cover,"
458 "(SELECT name FROM media_to_people JOIN people "
459 "ON (people_id = id) WHERE media_id = media.id AND role = %Q LIMIT 1) AS people_%s",
460 ML_PERSON_ARTIST, ML_PERSON_ARTIST );
461 if( i_ret != VLC_SUCCESS )
463 i_from = table_media;
464 i_join |= ( table_album | table_people );
465 psz_distinct = "DISTINCT";
466 res_type = ML_TYPE_MEDIA;
469 /* Who said this was over-complicated ?? */
471 psz_select = strdup( "media.id AS id,"
473 "media.type AS type,"
474 "media.title AS title,"
475 "media.duration AS duration,"
476 "media.original_title AS original_title,"
477 "media.album_id AS album_id,"
478 "media.cover AS cover,"
479 "media.preview AS preview,"
480 "media.disc as disc,"
481 "media.track AS track,"
482 "media.year AS year,"
483 "media.genre AS genre,"
484 "media.played_count AS played_count,"
485 "media.last_played AS last_played,"
486 "media.first_played AS first_played,"
487 "media.import_time AS import_time,"
488 "media.last_skipped AS last_skipped,"
489 "media.skipped_count AS skipped_count,"
490 "media.vote AS vote,"
491 "media.score AS score,"
492 "media.comment AS comment,"
493 "media.filesize AS filesize,"
494 "album.title AS album_title,"
495 "album.cover AS album_cover,"
496 "people.id AS people_id,"
497 "people.name AS people_name,"
498 "people.role AS people_role,"
499 "extra.language AS language,"
500 "extra.extra AS extra" );
501 i_from = table_media;
502 i_join |= ( table_album | table_people | table_extra );
503 psz_distinct = "DISTINCT";
504 res_type = ML_TYPE_MEDIA;
507 psz_select = strdup( "media.id AS id,"
508 "people.id AS people_id,"
509 "people.name AS people_name,"
510 "people.role AS people_role,"
511 "extra.extra AS extra,"
512 "extra.language AS language" );
513 i_from = table_media;
514 i_join |= ( table_album | table_people | table_extra );
515 psz_distinct = "DISTINCT";
516 res_type = ML_TYPE_MEDIA;
518 case ML_ORIGINAL_TITLE:
519 psz_select = ( !*psz_distinct ) ?
520 strdup( "media.id, media.original_title" ) : strdup( "media.original_title" );
521 i_from = table_media;
523 /* For people, if lvalue = "", then we want ANY people. */
525 assert( psz_selected_type_lvalue );
526 i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.name",
527 *psz_selected_type_lvalue ? "_" : "",
528 *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
529 if( i_ret != VLC_SUCCESS )
533 i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.name",
534 *psz_selected_type_lvalue ? "_" : "",
535 *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
536 if( i_ret != VLC_SUCCESS )
539 i_from = table_people;
540 psz_peoplerole = strdup( psz_selected_type_lvalue );
543 assert( psz_selected_type_lvalue );
544 i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.id",
545 *psz_selected_type_lvalue ? "_" : "",
546 *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
547 if( i_ret != VLC_SUCCESS )
551 i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.id",
552 *psz_selected_type_lvalue ? "_" : "",
553 *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
554 if( i_ret != VLC_SUCCESS )
557 psz_distinct = "DISTINCT";
558 i_from = table_people;
559 psz_peoplerole = strdup( psz_selected_type_lvalue );
560 res_type = ML_TYPE_INT;
563 psz_select = strdup( "people.role" );
564 psz_distinct = "DISTINCT";
565 i_from = table_people;
568 psz_select = ( !*psz_distinct ) ?
569 strdup( "media.id, media.title" ) : strdup( "media.title" );
570 i_from = table_media;
573 psz_select = ( !*psz_distinct ) ?
574 strdup( "media.id, media.type" ): strdup( "media.type" );
575 i_from = table_media;
576 res_type = ML_TYPE_INT;
579 psz_select = ( !*psz_distinct ) ?
580 strdup( "media.id, media.uri" ) : strdup( "media.uri" );
581 i_from = table_media;
584 psz_select = ( !*psz_distinct ) ?
585 strdup( "media.id, media.vote" ) : strdup( "media.vote" );
586 i_from = table_media;
587 res_type = ML_TYPE_INT;
590 psz_select = ( !*psz_distinct ) ?
591 strdup( "media.id, media.year" ) : strdup( "media.year" );
592 i_from = table_media;
593 res_type = ML_TYPE_INT;
600 msg_Dbg( p_ml, "unknown select (%d) in BuildSelect", selected_type );
604 /* Let's build full psz_query ! */
607 /* Figure out select and join tables */
616 case 2: i_join = 0; break;
618 case 3: i_from = table_media; i_join = table_album; break;
622 case 7: i_from = table_media; i_join = table_album | table_people; break;
626 case 11: i_from = table_media; i_join = table_extra | table_album; break;
630 case 15: i_from = table_media; i_join = table_extra | table_album | table_people; break;
638 case 1: i_from = table_media; i_join = table_people; break;
640 case 3: i_from = table_media; i_join = table_album | table_people; break;
642 /* Determine if a join from media is required */
643 if( i_num_frompersons > 1 )
644 i_from = table_media;
648 case 5: i_from = table_media; i_join = table_people; break;
650 case 7: i_from = table_media; i_join = table_album | table_people; break;
652 case 9: i_from = table_media; i_join = table_people | table_extra; break;
654 case 11: i_from = table_media; i_join = table_people | table_album | table_extra; break;
656 case 13: i_from = table_media; i_join = table_people | table_extra; break;
658 case 15: i_from = table_media; i_join = table_people | table_album | table_extra; break;
666 case 1: i_from = table_media; i_join = table_extra; break;
668 case 3: i_from = table_media; i_join = table_extra | table_album; break;
670 case 5: i_from = table_media; i_join = table_extra | table_people; break;
672 case 7: i_from = table_media; i_join = table_extra | table_people | table_album; break;
673 case 8: i_from = table_extra; i_join = 0; break;
674 case 9: i_from = table_media; i_join = table_extra; break;
676 case 11: i_from = table_media; i_join = table_extra | table_album; break;
678 case 13: i_from = table_media; i_join = table_extra | table_people; break;
680 case 15: i_from = table_media; i_join = table_extra | table_people | table_album; break;
684 default: msg_Warn( p_ml, "You can't be selecting from this table!!" );
685 i_ret = VLC_EGENERIC;
689 assert( !( i_from & table_album && i_join & table_album ) );
690 assert( !( i_from & table_people && i_join & table_people ) );
691 assert( !( i_from & table_extra && i_join & table_extra ) );
693 /* Generate FROM - psz_from */
694 if( i_from == table_media )
695 i_ret = AppendStringFmt( p_ml, &psz_from, "media" );
696 else if( i_from == table_album )
697 i_ret = AppendStringFmt( p_ml, &psz_from, "album" );
698 else if( i_from == table_extra )
699 i_ret = AppendStringFmt( p_ml, &psz_from, "extra" );
700 else if( i_from == table_people )
702 i_ret = AppendStringFmt( p_ml, &psz_from, "people AS people%s%s",
703 psz_peoplerole ? "_" : "", psz_peoplerole );
704 if( i_ret < 0 ) goto exit;
706 /* The ugly next statement is only required if persons are being
707 * selected. Otherwise the joins will handle this */
708 if( psz_peoplerole && *psz_peoplerole )
710 i_ret = AppendStringFmt( p_ml, &psz_where, "%s people_%s.role = %Q ",
711 ( psz_where && *psz_where ) ? " AND" : "",
712 psz_peoplerole, psz_peoplerole );
713 if( i_ret < 0 ) goto exit;
716 if( i_ret < 0 ) goto exit;
718 i_ret = AppendStringFmt( p_ml, &psz_query,
719 "SELECT %s %s ", psz_distinct, psz_select );
720 if( i_ret < 0 ) goto exit;
722 i_ret = AppendStringFmt( p_ml, &psz_query, "FROM %s ", psz_from );
723 if( i_ret < 0 ) goto exit;
725 /* Create join conditions */
726 if( i_join & table_people )
728 /* we can join psz_peoplerole safely because
729 * if i_join = people, then i_from != people */
731 for( int i = 0; i < i_num_frompersons ; i++ )
733 /* We assume ppsz_frompersons has unique entries and
734 * if ppsz_frompersons[i] is empty(but not NULL), then it
735 * means we accept any role */
736 if( ppsz_frompersons[i] && *ppsz_frompersons[i] )
738 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
740 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ",
741 psz_join == NULL ? "" : ",", ppsz_frompersons[i] );
742 /* This is possible because from is usually the media table */
743 AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ",
744 psz_on == NULL ? "" : " AND ", ppsz_frompersons[i] );
745 AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ",
746 psz_join2 == NULL ? "" : ",", ppsz_frompersons[i] );
747 AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND "
748 "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ",
749 ppsz_frompersons[i], ppsz_frompersons[i],
750 ppsz_frompersons[i], ppsz_frompersons[i] );
752 else if( ppsz_frompersons[i] )
754 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
756 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ",
757 psz_join == NULL ? "" : "," );
758 /* This is possible because from is usually the media table */
759 AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ",
760 psz_on == NULL ? "" : " AND " );
761 AppendStringFmt( p_ml, &psz_join2, "%speople AS people ",
762 psz_join2 == NULL ? "" : "," );
763 AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id",
764 psz_on2 == NULL ? "" : " AND " );
769 if( psz_peoplerole && *psz_peoplerole )
771 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ",
772 psz_join == NULL ? "" : ",", psz_peoplerole );
773 /* This is possible because from is always the media table */
774 AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ",
775 psz_on == NULL ? "" : " AND ", psz_peoplerole );
776 AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ",
777 psz_join2 == NULL ? "" : ",", psz_peoplerole );
778 AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND "
779 "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ",
780 psz_peoplerole, psz_peoplerole,
781 psz_peoplerole, psz_peoplerole );
785 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ",
786 psz_join == NULL ? "" : "," );
787 /* This is possible because from is usually the media table */
788 AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ",
789 psz_on == NULL ? "" : " AND " );
790 AppendStringFmt( p_ml, &psz_join2, "%speople ",
791 psz_join2 == NULL ? "" : "," );
792 AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id",
793 psz_on2 == NULL ? "" : " AND " );
798 if( i_join & table_album )
800 AppendStringFmt( p_ml, &psz_join, "%salbum", psz_join == NULL ? "" : "," );
801 AppendStringFmt( p_ml, &psz_on, "%s album.id = media.album_id ",
802 psz_on == NULL ? "" : " AND " );
804 if( i_join & table_extra )
806 AppendStringFmt( p_ml, &psz_join, "%sextra", psz_join == NULL ? "" : "," );
807 AppendStringFmt( p_ml, &psz_on, "%s extra.id = media.id ",
808 psz_on == NULL ? "" : " AND " );
811 /* Complete the join clauses */
814 AppendStringFmt( p_ml, &psz_query,
815 "JOIN %s ON %s ", psz_join, psz_on );
819 AppendStringFmt( p_ml, &psz_query,
820 "JOIN %s ON %s ", psz_join2, psz_on2 );
822 if( psz_where && *psz_where )
824 AppendStringFmt( p_ml, &psz_query,
825 "WHERE %s ", psz_where );
827 /* TODO: FIXME: Limit on media objects doesn't work! */
830 AppendStringFmt( p_ml, &psz_query,
831 "LIMIT %d ", i_limit );
836 AppendStringFmt( p_ml, &psz_query,
837 "ORDER BY %s %s", psz_select, psz_sort );
840 if( i_ret > 0 ) i_ret = VLC_SUCCESS;
842 if( p_result_type ) *p_result_type = res_type;
843 if( !psz_query ) i_ret = VLC_EGENERIC;
844 else *ppsz_query = strdup( psz_query );
856 free( psz_peoplerole );
857 free( ppsz_frompersons );
859 if( i_ret != VLC_SUCCESS )
860 msg_Warn( p_ml, "an unknown error occured (%d)", i_ret );
866 #define CASE_INT( casestr, fmt, table ) \
868 assert( tree->comp != ML_COMP_HAS && tree->comp != ML_COMP_STARTS_WITH \
869 && tree->comp != ML_COMP_ENDS_WITH ); \
870 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "%s %s %d", fmt, \
871 tree->comp == ML_COMP_LESSER ? "<" : \
872 tree->comp == ML_COMP_LESSER_OR_EQUAL ? "<=" : \
873 tree->comp == ML_COMP_GREATER ? ">" : \
874 tree->comp == ML_COMP_GREATER_OR_EQUAL ? ">=" : "=", tree->value.i ); \
875 if( *ppsz_where == NULL ) \
881 #define CASE_PSZ( casestr, fmt, table ) \
883 assert( tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_EQUAL \
884 || tree->comp == ML_COMP_STARTS_WITH \
885 || tree->comp == ML_COMP_ENDS_WITH ); \
886 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "%s LIKE '%s%q%s'", fmt, \
887 tree->comp == ML_COMP_HAS \
888 || tree->comp == ML_COMP_STARTS_WITH? "%%" : "", \
890 tree->comp == ML_COMP_HAS \
891 || tree->comp == ML_COMP_ENDS_WITH? "%%" : "" ); \
892 if( *ppsz_where == NULL ) \
897 #define SLDPJ sort, limit, distinct, pppsz_frompersons, i_frompersons, join
898 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t* tree,
899 char** sort, int* limit, const char** distinct,
900 char*** pppsz_frompersons, int* i_frompersons, int* join )
902 assert( ppsz_where && sort && distinct );
903 if( !tree ) /* Base case */
908 int i_ret = VLC_EGENERIC;
909 char* psz_left = NULL;
910 char* psz_right = NULL;
916 i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
917 if( i_ret != VLC_SUCCESS )
919 i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
920 if( i_ret != VLC_SUCCESS )
922 if( psz_left == NULL || psz_right == NULL )
924 msg_Err( p_ml, "Parsing failed for AND/OR" );
925 i_ret = VLC_EGENERIC;
928 if( asprintf( ppsz_where, "( %s %s %s )", psz_left,
929 ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 )
936 i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
937 if( i_ret != VLC_SUCCESS )
939 if( psz_left == NULL )
941 msg_Err( p_ml, "Parsing failed at NOT" );
942 i_ret = VLC_EGENERIC;
945 if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 )
952 i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
953 if( i_ret != VLC_SUCCESS )
955 i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
956 if( i_ret != VLC_SUCCESS )
958 /* Ignore right parse tree as this is a special node */
959 if( asprintf( ppsz_where, "%s", psz_left ? psz_left : "" ) == -1 )
966 switch( tree->criteria )
969 assert( tree->comp == ML_COMP_HAS
970 || tree->comp == ML_COMP_EQUAL
971 || tree->comp == ML_COMP_STARTS_WITH
972 || tree->comp == ML_COMP_ENDS_WITH );
973 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
974 "people%s%s.name LIKE '%s%q%s'",
975 tree->lvalue.str ? "_" : "",
976 tree->lvalue.str ? tree->lvalue.str : "",
977 tree->comp == ML_COMP_HAS
978 || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
980 tree->comp == ML_COMP_HAS
981 || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
982 if( *ppsz_where == NULL )
984 *pppsz_frompersons = realloc( *pppsz_frompersons,
985 ++*i_frompersons * sizeof( char* ) );
986 *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
987 *join |= table_people;
990 assert( tree->comp == ML_COMP_EQUAL );
991 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
992 "( people%s%s.id = %d )", tree->lvalue.str ? "_":"",
993 tree->lvalue.str ? tree->lvalue.str:"",
995 if( *ppsz_where == NULL )
997 *pppsz_frompersons = realloc( *pppsz_frompersons,
998 ++*i_frompersons * sizeof( char* ) );
999 *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1000 *join |= table_people;
1002 case ML_PEOPLE_ROLE:
1003 assert( tree->comp == ML_COMP_HAS
1004 || tree->comp == ML_COMP_EQUAL
1005 || tree->comp == ML_COMP_STARTS_WITH
1006 || tree->comp == ML_COMP_ENDS_WITH );
1007 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1008 "people%s%s.role LIKE '%s%q%s'",
1009 tree->lvalue.str ? "_" : "",
1010 tree->lvalue.str ? tree->lvalue.str : "",
1011 tree->comp == ML_COMP_HAS
1012 || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
1014 tree->comp == ML_COMP_HAS
1015 || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
1016 if( *ppsz_where == NULL )
1018 *pppsz_frompersons = realloc( *pppsz_frompersons,
1019 ++*i_frompersons * sizeof( char* ) );
1020 *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1021 *join |= table_people;
1023 CASE_PSZ( ML_ALBUM, "album.title", table_album );
1024 CASE_PSZ( ML_ALBUM_COVER, "album.cover", table_album );
1026 assert( tree->comp == ML_COMP_EQUAL );
1027 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1028 "album.id = %d", tree->value.i );
1029 if( *ppsz_where == NULL )
1031 *join |= table_album;
1033 CASE_PSZ( ML_COMMENT, "media.comment", table_media );
1034 CASE_PSZ( ML_COVER, "media.cover", table_media );
1035 CASE_INT( ML_DURATION, "media.duration", table_media );
1036 CASE_PSZ( ML_EXTRA, "extra.extra", table_extra );
1037 CASE_INT( ML_FILESIZE, "media.filesize", table_media );
1038 CASE_PSZ( ML_GENRE, "media.genre", table_media );
1040 assert( tree->comp == ML_COMP_EQUAL );
1041 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1042 "media.id = %d", tree->value.i );
1043 if( *ppsz_where == NULL )
1045 *join |= table_media;
1047 CASE_PSZ( ML_LANGUAGE, "extra.language", table_extra );
1048 CASE_INT( ML_LAST_PLAYED, "media.last_played", table_media );
1049 CASE_PSZ( ML_ORIGINAL_TITLE, "media.original_title", table_media );
1050 msg_Warn( p_ml, "Deprecated Played Count tags" );
1051 CASE_INT( ML_PLAYED_COUNT, "media.played_count", table_media );
1052 CASE_INT( ML_SCORE, "media.score", table_media );
1053 CASE_PSZ( ML_TITLE, "media.title", table_media );
1054 CASE_INT( ML_TRACK_NUMBER, "media.track", table_media);
1055 CASE_INT( ML_TYPE, "media.type", table_media );
1056 CASE_PSZ( ML_URI, "media.uri", table_media );
1057 CASE_INT( ML_VOTE, "media.vote", table_media );
1058 CASE_INT( ML_YEAR, "media.year", table_media );
1061 *limit = tree->value.i;
1063 msg_Warn( p_ml, "Double LIMIT found" );
1066 *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s DESC ",
1067 sort ? *sort : "", sort ? ", " : "",
1073 *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s ASC ",
1074 sort ? *sort : "", sort ? ", " : "",
1081 *distinct = "DISTINCT";
1083 msg_Warn( p_ml, "Double DISTINCT found!" );
1086 msg_Err( p_ml, "Invalid select type or unsupported: %d", tree->criteria );
1090 msg_Err( p_ml, "Broken find tree!" );
1091 i_ret = VLC_EGENERIC;
1095 i_ret = VLC_SUCCESS;
1108 # undef table_people