git.sesse.net Git - vlc/blob - modules/media_library/sql_search.c

   1 /*****************************************************************************
   2  * sql_search.c: SQL-based media library: all find/get functions
   3  *****************************************************************************
   4  * Copyright (C) 2008-2010 the VideoLAN team and AUTHORS
   5  * $Id$
   6  *
   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>
  12  *
  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.
  17  *
  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.
  22  *
  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  *****************************************************************************/
  27
  28 #include "sql_media_library.h"
  29
  30 int Find( media_library_t *p_ml, vlc_array_t *p_result_array, ... )
  31 {
  32     va_list args;
  33     int returned;
  34
  35     va_start( args, p_result_array );
  36     returned = FindVa( p_ml, p_result_array, args );
  37     va_end( args );
  38
  39     return returned;
  40 }
  41
  42 /**
  43  * @brief Generic find in Media Library, returns arrays of psz or int
  44  *
  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
  49  */
  50 int FindVa( media_library_t *p_ml,
  51             vlc_array_t *p_result_array, va_list criterias )
  52 {
  53     int i_ret = VLC_SUCCESS;
  54     char *psz_query;
  55     ml_result_type_e result_type;
  56     char **pp_results = NULL;
  57     int i_cols, i_rows;
  58
  59     if( !p_result_array )
  60         return VLC_EGENERIC;
  61
  62     i_ret = BuildSelectVa( p_ml, &psz_query, &result_type, criterias );
  63     if( i_ret != VLC_SUCCESS )
  64         return i_ret;
  65
  66     if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
  67         != VLC_SUCCESS )
  68     {
  69         msg_Err( p_ml, "Error occured while making the query to the database" );
  70         return VLC_EGENERIC;
  71     }
  72
  73     i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
  74                               result_type );
  75
  76     free( psz_query);
  77     FreeSQLResult( p_ml, pp_results );
  78
  79     return i_ret;
  80 }
  81
  82 /**
  83  * @brief Generic find in Media Library, returns arrays of psz or int
  84  *
  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
  90  */
  91
  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 )
  94 {
  95     int i_ret = VLC_SUCCESS;
  96     char *psz_query;
  97     ml_result_type_e result_type;
  98     char **pp_results = NULL;
  99     int i_cols, i_rows;
 100
 101     if( !p_result_array )
 102         return VLC_EGENERIC;
 103
 104     i_ret = BuildSelect( p_ml, &psz_query, &result_type, psz_lvalue,
 105                          selected_type, tree );
 106
 107     if( i_ret != VLC_SUCCESS )
 108         return i_ret;
 109
 110     if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
 111         != VLC_SUCCESS )
 112     {
 113         msg_Err( p_ml, "Error occured while making the query to the database" );
 114         return VLC_EGENERIC;
 115     }
 116
 117     i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
 118                               result_type );
 119
 120     free( psz_query);
 121     FreeSQLResult( p_ml, pp_results );
 122
 123     return i_ret;
 124 }
 125
 126 /**
 127  * @brief Generic SELECT query builder with va_list parameter
 128  *
 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)
 135  *
 136  * This function handles results of only one column (or two if ID is included),
 137  * of 'normal' types: int and strings
 138  */
 139 int BuildSelectVa( media_library_t *p_ml, char **ppsz_query,
 140                    ml_result_type_e *p_result_type, va_list criterias )
 141 {
 142     int i_continue = 1;
 143     ml_ftree_t* p_ftree = NULL;
 144     char* psz_lvalue = NULL;
 145
 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 * );
 151
 152     /* Loop on every arguments */
 153     while( i_continue )
 154     {
 155         ml_ftree_t *p_find = ( ml_ftree_t* ) calloc( 1, sizeof( ml_ftree_t ) );
 156         if( !p_find )
 157             return VLC_ENOMEM;
 158         p_find->criteria = va_arg( criterias, int );
 159         p_find->comp = ML_COMP_EQUAL;
 160         switch( p_find->criteria )
 161         {
 162             case ML_SORT_ASC:
 163                 p_ftree = ml_FtreeSpecAsc( p_ftree, va_arg( criterias, char* ) ); break;
 164             case ML_SORT_DESC:
 165                 p_ftree = ml_FtreeSpecDesc( p_ftree, va_arg( criterias, char* ) ); break;
 166             case ML_DISTINCT:
 167                 p_ftree = ml_FtreeSpecDistinct( p_ftree ); break;
 168             case ML_LIMIT:
 169                 p_ftree = ml_FtreeSpecLimit( p_ftree, va_arg( criterias, int ) );
 170                 break;
 171             case ML_ARTIST:
 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 );
 176                 break;
 177             case ML_PEOPLE:
 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 );
 181                 break;
 182             case ML_PEOPLE_ID:
 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 );
 186                 break;
 187             case ML_END:
 188                 i_continue = 0;
 189                 break;
 190             default:
 191                 switch( ml_AttributeIsString( p_find->criteria ) )
 192                 {
 193                     case 0:
 194                         p_find->value.i = va_arg( criterias, int );
 195                         break;
 196                     case 1:
 197                         p_find->value.str = va_arg( criterias, char* );
 198                         break;
 199                 }
 200                 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
 201                 break;
 202         }
 203     }
 204
 205     int i_ret = BuildSelect( p_ml, ppsz_query, p_result_type, psz_lvalue,
 206                              selected_type, p_ftree );
 207
 208     ml_ShallowFreeFindTree( p_ftree );
 209     return i_ret;
 210 }
 211
 212 /**
 213  * @brief Append a string and format it using SQL vmprintf
 214  **/
 215 static int AppendStringFmtVa( media_library_t *p_ml,
 216                               char **ppsz_dst, const char *psz_fmt,
 217                               va_list args )
 218 {
 219     char *psz_tmp = NULL, *psz_fullexp = NULL;
 220
 221     assert( ppsz_dst != NULL );
 222
 223     if( !( *ppsz_dst ) )
 224     {
 225         /* New expression */
 226         *ppsz_dst = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
 227         if( !( *ppsz_dst ) )
 228             return VLC_ENOMEM;
 229     }
 230     else
 231     {
 232         /* Create new expression B */
 233         psz_tmp = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
 234         if( !psz_tmp )
 235             return VLC_ENOMEM;
 236
 237         if( asprintf( &psz_fullexp, "%s%s", *ppsz_dst, psz_tmp ) == -1 )
 238         {
 239             free( psz_tmp );
 240             return VLC_ENOMEM;
 241         }
 242
 243         free( *ppsz_dst );
 244         *ppsz_dst = psz_fullexp;
 245     }
 246
 247     return VLC_SUCCESS;
 248 }
 249
 250 static int AppendStringFmt( media_library_t *p_ml,
 251                             char **ppsz_dst, const char *psz_fmt, ... )
 252 {
 253     va_list args;
 254     va_start( args, psz_fmt );
 255     int i_ret = AppendStringFmtVa( p_ml, ppsz_dst, psz_fmt, args );
 256     va_end( args );
 257     return i_ret;
 258 }
 259
 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 );
 264
 265 #   define table_media             (1 << 0)
 266 #   define table_album             (1 << 1)
 267 #   define table_people            (1 << 2)
 268 #   define table_extra             (1 << 3)
 269
 270 static void PackFromPersons( char*** pppsz_frompersons, int i_num_frompersons )
 271 {
 272     for( int i = 0; i < i_num_frompersons; i++ )
 273     {
 274         if( *pppsz_frompersons[i] == NULL )
 275             continue;
 276         for( int j = i+1; j < i_num_frompersons; j++ )
 277         {
 278             if( strcmp( *pppsz_frompersons[i], *pppsz_frompersons[j] ) == 0 )
 279             {
 280                 *pppsz_frompersons[j] = NULL;
 281             }
 282         }
 283     }
 284 }
 285 /**
 286  * @brief Generic SELECT query builder
 287  *
 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
 294  */
 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,
 298                  ml_ftree_t *tree )
 299 {
 300     /* Basic verification */
 301     if( !ppsz_query )
 302         return VLC_EGENERIC;
 303
 304     int i_ret = VLC_SUCCESS;
 305     char *psz_query = NULL;
 306
 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] ;"
 313     */
 314     char *psz_select             = NULL;
 315     const char *psz_distinct     = ""; /* "DISTINCT" or "" */
 316
 317     /* FROM */
 318     char *psz_from               = NULL;
 319     int i_from                   = 0; /* Main select table */
 320
 321     char **ppsz_frompersons      = NULL;
 322     int  i_num_frompersons       = 0;
 323     char *psz_peoplerole         = NULL; /* Person to get selected */
 324
 325     /* JOIN ... ON ... */
 326     char *psz_join               = NULL;
 327     char *psz_join2              = NULL;
 328     char *psz_on                 = NULL;
 329     char *psz_on2                = NULL;
 330     int i_join                   = 0;    /* Tables that need to be joined */
 331
 332     /* String buffers */
 333     char *psz_where              = NULL;
 334     char *psz_sort               = NULL; /* ASC or DESC or NULL */
 335     char *psz_tmp                = NULL;
 336
 337     int i_limit                  = 0;
 338
 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 );
 342
 343     PackFromPersons( &ppsz_frompersons, i_num_frompersons );
 344
 345     /* What is the result type? */
 346     ml_result_type_e res_type   = ML_TYPE_PSZ;
 347
 348     /* SELECT, FROM */
 349     /* Note that a DISTINCT select makes id of result non sense */
 350     switch( selected_type )
 351     {
 352     case ML_ALBUM:
 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;
 357         break;
 358     case ML_ALBUM_COVER:
 359         psz_select   = ( !*psz_distinct ) ?
 360                 strdup( "album.id, album.cover" ) : strdup( "album.cover" );
 361         i_from       = table_album;
 362         break;
 363     case ML_ALBUM_ID:
 364         psz_select   = strdup( "album.id" );
 365         psz_distinct = "DISTINCT";
 366         i_from       = table_album;
 367         res_type     = ML_TYPE_INT;
 368         break;
 369     case ML_ARTIST:
 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 );
 375         break;
 376     case ML_ARTIST_ID:
 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 );
 382         break;
 383     case ML_COVER:
 384         psz_select   = ( !*psz_distinct ) ?
 385                 strdup( "media.id, media.cover" ) : strdup( "media.cover" );
 386         i_from       = table_media;
 387         break;
 388     case ML_COMMENT:
 389         psz_select   = ( !*psz_distinct ) ?
 390                 strdup( "media.id, extra.comment" ) : strdup( "extra.comment" );
 391         i_from       = table_extra;
 392         break;
 393     case ML_GENRE:
 394         psz_select   = ( !*psz_distinct ) ?
 395                strdup( "media.id, media.genre" ) : strdup( "media.genre" );
 396         i_from       = table_media;
 397         break;
 398     case ML_COUNT_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;
 403         break;
 404     case ML_COUNT_ALBUM:
 405         psz_select   = ( !*psz_distinct ) ?
 406                 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT album.id )" );
 407         i_from       = table_album;
 408         res_type     = ML_TYPE_INT;
 409         break;
 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;
 415         break;
 416     case ML_FILESIZE:
 417         psz_select   = strdup( "media.filesize" );
 418         i_from       = table_media;
 419         res_type     = ML_TYPE_INT;
 420         break;
 421     case ML_ID:
 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;
 426         break;
 427     case ML_LANGUAGE:
 428         psz_select   = strdup( "extra.language" );
 429         psz_distinct = "DISTINCT";
 430         i_from       = table_extra;
 431         break;
 432     case ML_MEDIA_SPARSE:
 433         i_ret = AppendStringFmt( p_ml, &psz_select, "media.id AS id,"
 434                 "media.uri AS uri,"
 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 )
 462             goto exit;
 463         i_from       = table_media;
 464         i_join      |= ( table_album | table_people );
 465         psz_distinct = "DISTINCT";
 466         res_type     = ML_TYPE_MEDIA;
 467         break;
 468     case ML_MEDIA:
 469         /* Who said this was over-complicated ?? */
 470         /* Yea right. */
 471         psz_select   = strdup( "media.id AS id,"
 472                 "media.uri AS uri,"
 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;
 505         break;
 506     case ML_MEDIA_EXTRA:
 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;
 517         break;
 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;
 522         break;
 523     /* For people, if lvalue = "", then we want ANY people. */
 524     case ML_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 )
 530             goto exit;
 531         if( *psz_distinct )
 532         {
 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 )
 537                 goto exit;
 538         }
 539         i_from         = table_people;
 540         psz_peoplerole = strdup( psz_selected_type_lvalue );
 541         break;
 542     case ML_PEOPLE_ID:
 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 )
 548             goto exit;
 549         if( *psz_distinct )
 550         {
 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 )
 555                 goto exit;
 556         }
 557         psz_distinct   = "DISTINCT";
 558         i_from         = table_people;
 559         psz_peoplerole = strdup( psz_selected_type_lvalue );
 560         res_type       = ML_TYPE_INT;
 561         break;
 562     case ML_PEOPLE_ROLE:
 563         psz_select   = strdup( "people.role" );
 564         psz_distinct = "DISTINCT";
 565         i_from       = table_people;
 566         break;
 567     case ML_TITLE:
 568         psz_select   = ( !*psz_distinct ) ?
 569                 strdup( "media.id, media.title" ) : strdup( "media.title" );
 570         i_from       = table_media;
 571         break;
 572     case ML_TYPE:
 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;
 577         break;
 578     case ML_URI:
 579         psz_select   = ( !*psz_distinct ) ?
 580                 strdup( "media.id, media.uri" ) : strdup( "media.uri" );
 581         i_from       = table_media;
 582         break;
 583     case ML_VOTE:
 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;
 588         break;
 589     case ML_YEAR:
 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;
 594         break;
 595     case ML_LIMIT:
 596     case ML_SORT_DESC:
 597     case ML_SORT_ASC:
 598     case ML_END:
 599     default:
 600         msg_Dbg( p_ml, "unknown select (%d) in BuildSelect", selected_type );
 601         return VLC_EGENERIC;
 602     }
 603
 604     /* Let's build full psz_query ! */
 605     i_ret = VLC_SUCCESS;
 606
 607     /* Figure out select and join tables */
 608     switch( i_from )
 609     {
 610         case table_media:
 611             break;
 612         case table_album:
 613             switch( i_join )
 614             {
 615                 case 0: break;
 616                 case 2: i_join = 0; break;
 617                 case 1:
 618                 case 3: i_from = table_media; i_join = table_album; break;
 619                 case 4:
 620                 case 5:
 621                 case 6:
 622                 case 7: i_from = table_media; i_join = table_album | table_people; break;
 623                 case 8:
 624                 case 9:
 625                 case 10:
 626                 case 11: i_from = table_media; i_join = table_extra | table_album; break;
 627                 case 12:
 628                 case 13:
 629                 case 14:
 630                 case 15: i_from = table_media; i_join = table_extra | table_album | table_people; break;
 631                 default: break;
 632             }
 633             break;
 634         case table_people:
 635             switch( i_join )
 636             {
 637                 case 0: break;
 638                 case 1: i_from = table_media; i_join = table_people; break;
 639                 case 2:
 640                 case 3: i_from = table_media; i_join = table_album | table_people; break;
 641                 case 4:
 642                     /* Determine if a join from media is required */
 643                     if( i_num_frompersons > 1 )
 644                         i_from = table_media;
 645                     else
 646                         i_join = 0;
 647                     break;
 648                 case 5: i_from = table_media; i_join = table_people; break;
 649                 case 6:
 650                 case 7: i_from = table_media; i_join = table_album | table_people; break;
 651                 case 8:
 652                 case 9: i_from = table_media; i_join = table_people | table_extra; break;
 653                 case 10:
 654                 case 11: i_from = table_media; i_join = table_people | table_album | table_extra; break;
 655                 case 12:
 656                 case 13: i_from = table_media; i_join = table_people | table_extra; break;
 657                 case 14:
 658                 case 15: i_from = table_media; i_join = table_people | table_album | table_extra; break;
 659                 default: break;
 660             }
 661             break;
 662         case table_extra:
 663             switch( i_join )
 664             {
 665                 case 0: break;
 666                 case 1: i_from = table_media; i_join = table_extra; break;
 667                 case 2:
 668                 case 3: i_from = table_media; i_join = table_extra | table_album; break;
 669                 case 4:
 670                 case 5: i_from = table_media; i_join = table_extra | table_people; break;
 671                 case 6:
 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;
 675                 case 10:
 676                 case 11: i_from = table_media; i_join = table_extra | table_album; break;
 677                 case 12:
 678                 case 13: i_from = table_media; i_join = table_extra | table_people; break;
 679                 case 14:
 680                 case 15: i_from = table_media; i_join = table_extra | table_people | table_album; break;
 681                 default: break;
 682             }
 683             break;
 684         default: msg_Warn( p_ml, "You can't be selecting from this table!!" );
 685                  i_ret = VLC_EGENERIC;
 686                  goto exit;
 687     }
 688
 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 ) );
 692
 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 )
 701     {
 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;
 705
 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 )
 709         {
 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;
 714         }
 715     }
 716     if( i_ret < 0 ) goto exit;
 717
 718     i_ret = AppendStringFmt( p_ml, &psz_query,
 719                              "SELECT %s %s ", psz_distinct, psz_select );
 720     if( i_ret < 0 ) goto exit;
 721
 722     i_ret = AppendStringFmt( p_ml, &psz_query, "FROM %s ", psz_from );
 723     if( i_ret < 0 ) goto exit;
 724
 725     /* Create join conditions */
 726     if( i_join & table_people )
 727     {
 728         /* we can join psz_peoplerole safely because
 729          * if i_join = people, then i_from != people */
 730         bool join = true;
 731         for( int i = 0; i < i_num_frompersons ; i++ )
 732         {
 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] )
 737             {
 738                 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
 739                     join = false;
 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]  );
 751             }
 752             else if( ppsz_frompersons[i] )
 753             {
 754                 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
 755                     join = false;
 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 " );
 765             }
 766         }
 767         if( join )
 768         {
 769             if( psz_peoplerole && *psz_peoplerole )
 770             {
 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 );
 782             }
 783             else
 784             {
 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 " );
 794
 795             }
 796         }
 797     }
 798     if( i_join & table_album )
 799     {
 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 " );
 803     }
 804     if( i_join & table_extra )
 805     {
 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 " );
 809     }
 810
 811     /* Complete the join clauses */
 812     if( psz_join )
 813     {
 814         AppendStringFmt( p_ml, &psz_query,
 815                          "JOIN %s ON %s ", psz_join, psz_on );
 816     }
 817     if( psz_join2 )
 818     {
 819         AppendStringFmt( p_ml, &psz_query,
 820                          "JOIN %s ON %s ", psz_join2, psz_on2 );
 821     }
 822     if( psz_where && *psz_where )
 823     {
 824         AppendStringFmt( p_ml, &psz_query,
 825                          "WHERE %s ", psz_where );
 826     }
 827     /* TODO: FIXME: Limit on media objects doesn't work! */
 828     if( i_limit )
 829     {
 830         AppendStringFmt( p_ml, &psz_query,
 831                          "LIMIT %d ", i_limit );
 832     }
 833
 834     if( psz_sort )
 835     {
 836         AppendStringFmt( p_ml, &psz_query,
 837                          "ORDER BY %s %s", psz_select, psz_sort );
 838     }
 839
 840     if( i_ret > 0 ) i_ret = VLC_SUCCESS;
 841
 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 );
 845
 846 exit:
 847     free( psz_query );
 848     free( psz_where );
 849     free( psz_tmp   );
 850     free( psz_from  );
 851     free( psz_join  );
 852     free( psz_select );
 853     free( psz_join2 );
 854     free( psz_on    );
 855     free( psz_on2   );
 856     free( psz_peoplerole );
 857     free( ppsz_frompersons );
 858
 859     if( i_ret != VLC_SUCCESS )
 860         msg_Warn( p_ml, "an unknown error occured (%d)", i_ret );
 861
 862     return i_ret;
 863 }
 864
 865 #undef CASE_INT
 866 #define CASE_INT( casestr, fmt, table )                                     \
 867 case casestr:                                                               \
 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 )                                                   \
 876     goto parsefail;                                                         \
 877 *join |= table;                                                             \
 878 break
 879
 880 #undef CASE_PSZ
 881 #define CASE_PSZ( casestr, fmt, table )                                       \
 882 case casestr:                                                                 \
 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? "%%" : "",                      \
 889             tree->value.str,                                                  \
 890         tree->comp == ML_COMP_HAS                                             \
 891         || tree->comp == ML_COMP_ENDS_WITH? "%%" : "" );                      \
 892     if( *ppsz_where == NULL )                                                 \
 893         goto parsefail;                                                       \
 894     *join |= table;                                                           \
 895     break
 896
 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 )
 901 {
 902     assert( ppsz_where && sort && distinct );
 903     if( !tree ) /* Base case */
 904     {
 905         return VLC_SUCCESS;
 906     }
 907
 908     int i_ret = VLC_EGENERIC;
 909     char* psz_left = NULL;
 910     char* psz_right = NULL;
 911
 912     switch( tree->op )
 913     {
 914         case ML_OP_AND:
 915         case ML_OP_OR:
 916             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 917             if( i_ret != VLC_SUCCESS )
 918                 goto parsefail;
 919             i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
 920             if( i_ret != VLC_SUCCESS )
 921                 goto parsefail;
 922             if( psz_left == NULL || psz_right == NULL )
 923             {
 924                 msg_Err( p_ml, "Parsing failed for AND/OR" );
 925                 i_ret = VLC_EGENERIC;
 926                 goto parsefail;
 927             }
 928             if( asprintf( ppsz_where, "( %s %s %s )", psz_left,
 929                 ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 )
 930             {
 931                 i_ret = VLC_ENOMEM;
 932                 goto parsefail;
 933             }
 934             break;
 935         case ML_OP_NOT:
 936             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 937             if( i_ret != VLC_SUCCESS )
 938                 goto parsefail;
 939             if( psz_left == NULL )
 940             {
 941                 msg_Err( p_ml, "Parsing failed at NOT" );
 942                 i_ret = VLC_EGENERIC;
 943                 goto parsefail;
 944             }
 945             if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 )
 946             {
 947                 i_ret = VLC_ENOMEM;
 948                 goto parsefail;
 949             }
 950             break;
 951         case ML_OP_SPECIAL:
 952             i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
 953             if( i_ret != VLC_SUCCESS )
 954                 goto parsefail;
 955             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 956             if( i_ret != VLC_SUCCESS )
 957                 goto parsefail;
 958             /* Ignore right parse tree as this is a special node */
 959             *ppsz_where = strdup( psz_left ? psz_left : "" );
 960             if( !*ppsz_where )
 961             {
 962                 i_ret = VLC_ENOMEM;
 963                 goto parsefail;
 964             }
 965             break;
 966         case ML_OP_NONE:
 967             switch( tree->criteria )
 968             {
 969                 case ML_PEOPLE:
 970                     assert( tree->comp == ML_COMP_HAS
 971                             || tree->comp == ML_COMP_EQUAL
 972                             || tree->comp == ML_COMP_STARTS_WITH
 973                             || tree->comp == ML_COMP_ENDS_WITH );
 974                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
 975                             "people%s%s.name LIKE '%s%q%s'",
 976                             tree->lvalue.str ? "_" : "",
 977                             tree->lvalue.str ? tree->lvalue.str : "",
 978                             tree->comp == ML_COMP_HAS
 979                             || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
 980                             tree->value.str,
 981                             tree->comp == ML_COMP_HAS
 982                             || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
 983                     if( *ppsz_where == NULL )
 984                         goto parsefail;
 985                     *pppsz_frompersons = realloc( *pppsz_frompersons,
 986                             ++*i_frompersons * sizeof( char* ) );
 987                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
 988                     *join |= table_people;
 989                     break;
 990                 case ML_PEOPLE_ID:
 991                     assert( tree->comp == ML_COMP_EQUAL );
 992                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
 993                                 "( people%s%s.id = %d )", tree->lvalue.str ? "_":"",
 994                                 tree->lvalue.str ? tree->lvalue.str:"",
 995                                 tree->value.i );
 996                     if( *ppsz_where == NULL )
 997                         goto parsefail;
 998                     *pppsz_frompersons = realloc( *pppsz_frompersons,
 999                             ++*i_frompersons * sizeof( char* ) );
1000                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1001                     *join |= table_people;
1002                     break;
1003                 case ML_PEOPLE_ROLE:
1004                     assert( tree->comp == ML_COMP_HAS
1005                             || tree->comp == ML_COMP_EQUAL
1006                             || tree->comp == ML_COMP_STARTS_WITH
1007                             || tree->comp == ML_COMP_ENDS_WITH );
1008                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1009                             "people%s%s.role LIKE '%s%q%s'",
1010                             tree->lvalue.str ? "_" : "",
1011                             tree->lvalue.str ? tree->lvalue.str : "",
1012                             tree->comp == ML_COMP_HAS
1013                             || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
1014                             tree->value.str,
1015                             tree->comp == ML_COMP_HAS
1016                             || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
1017                     if( *ppsz_where == NULL )
1018                         goto parsefail;
1019                     *pppsz_frompersons = realloc( *pppsz_frompersons,
1020                             ++*i_frompersons * sizeof( char* ) );
1021                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1022                     *join |= table_people;
1023                     break;
1024                 CASE_PSZ( ML_ALBUM, "album.title", table_album );
1025                 CASE_PSZ( ML_ALBUM_COVER, "album.cover", table_album );
1026                 case ML_ALBUM_ID:
1027                     assert( tree->comp == ML_COMP_EQUAL );
1028                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1029                             "album.id = %d", tree->value.i );
1030                     if( *ppsz_where == NULL )
1031                         goto parsefail;
1032                     *join |= table_album;
1033                     break;
1034                 CASE_PSZ( ML_COMMENT, "media.comment", table_media );
1035                 CASE_PSZ( ML_COVER, "media.cover", table_media );
1036                 CASE_INT( ML_DURATION, "media.duration", table_media );
1037                 CASE_PSZ( ML_EXTRA, "extra.extra", table_extra );
1038                 CASE_INT( ML_FILESIZE, "media.filesize", table_media );
1039                 CASE_PSZ( ML_GENRE, "media.genre", table_media );
1040                 case ML_ID:
1041                     assert( tree->comp == ML_COMP_EQUAL );
1042                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1043                             "media.id = %d", tree->value.i );
1044                     if( *ppsz_where == NULL )
1045                         goto parsefail;
1046                     *join |= table_media;
1047                     break;
1048                 CASE_PSZ( ML_LANGUAGE, "extra.language", table_extra );
1049                 CASE_INT( ML_LAST_PLAYED, "media.last_played", table_media );
1050                 CASE_PSZ( ML_ORIGINAL_TITLE, "media.original_title", table_media );
1051                    msg_Warn( p_ml, "Deprecated Played Count tags" );
1052                 CASE_INT( ML_PLAYED_COUNT, "media.played_count", table_media );
1053                 CASE_INT( ML_SCORE, "media.score", table_media );
1054                 CASE_PSZ( ML_TITLE, "media.title", table_media );
1055                 CASE_INT( ML_TRACK_NUMBER, "media.track", table_media);
1056                 CASE_INT( ML_TYPE, "media.type", table_media );
1057                 CASE_PSZ( ML_URI, "media.uri", table_media );
1058                 CASE_INT( ML_VOTE, "media.vote", table_media );
1059                 CASE_INT( ML_YEAR, "media.year", table_media );
1060                 case ML_LIMIT:
1061                     if( !*limit )
1062                         *limit = tree->value.i;
1063                     else
1064                         msg_Warn( p_ml, "Double LIMIT found" );
1065                     break;
1066                 case ML_SORT_DESC:
1067                     *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s DESC ",
1068                                         sort ? *sort : "", sort ? ", " : "",
1069                                         tree->value.str );
1070                     if( *sort == NULL )
1071                         goto parsefail;
1072                     break;
1073                 case ML_SORT_ASC:
1074                     *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s ASC ",
1075                                         sort ? *sort : "", sort ? ", " : "",
1076                                         tree->value.str );
1077                     if( *sort == NULL )
1078                         goto parsefail;
1079                     break;
1080                 case ML_DISTINCT:
1081                     if( !**distinct )
1082                         *distinct = "DISTINCT";
1083                     else
1084                         msg_Warn( p_ml, "Double DISTINCT found!" );
1085                     break;
1086                 default:
1087                     msg_Err( p_ml, "Invalid select type or unsupported: %d", tree->criteria );
1088             }
1089             break;
1090         default:
1091             msg_Err( p_ml, "Broken find tree!" );
1092             i_ret = VLC_EGENERIC;
1093             goto parsefail;
1094     }
1095
1096     i_ret = VLC_SUCCESS;
1097 parsefail:
1098     free( psz_left );
1099     free( psz_right );
1100     return i_ret;
1101 }
1102
1103
1104 #   undef CASE_INT
1105 #   undef CASE_PSZ
1106
1107 #   undef table_media
1108 #   undef table_album
1109 #   undef table_people
1110 #   undef table_extra