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 #ifdef HAVE_CONFIG_H
  29 # include "config.h"
  30 #endif
  31
  32 #include "sql_media_library.h"
  33
  34 int Find( media_library_t *p_ml, vlc_array_t *p_result_array, ... )
  35 {
  36     va_list args;
  37     int returned;
  38
  39     va_start( args, p_result_array );
  40     returned = FindVa( p_ml, p_result_array, args );
  41     va_end( args );
  42
  43     return returned;
  44 }
  45
  46 /**
  47  * @brief Generic find in Media Library, returns arrays of psz or int
  48  *
  49  * @param p_ml the media library object
  50  * @param result A pointer to a result array
  51  * @param criterias list of criterias used in SELECT
  52  * @return VLC_SUCCESS or VLC_EGENERIC
  53  */
  54 int FindVa( media_library_t *p_ml,
  55             vlc_array_t *p_result_array, va_list criterias )
  56 {
  57     int i_ret = VLC_SUCCESS;
  58     char *psz_query;
  59     ml_result_type_e result_type;
  60     char **pp_results = NULL;
  61     int i_cols, i_rows;
  62
  63     if( !p_result_array )
  64         return VLC_EGENERIC;
  65
  66     i_ret = BuildSelectVa( p_ml, &psz_query, &result_type, criterias );
  67     if( i_ret != VLC_SUCCESS )
  68         return i_ret;
  69
  70     if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
  71         != VLC_SUCCESS )
  72     {
  73         msg_Err( p_ml, "Error occurred while making the query to the database" );
  74         return VLC_EGENERIC;
  75     }
  76
  77     i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
  78                               result_type );
  79
  80     free( psz_query);
  81     FreeSQLResult( p_ml, pp_results );
  82
  83     return i_ret;
  84 }
  85
  86 /**
  87  * @brief Generic find in Media Library, returns arrays of psz or int
  88  *
  89  * @param p_ml the media library object
  90  * @param result a pointer to a result array
  91  * @param selected_type the type of the element we're selecting
  92  * @param criterias list of criterias used in SELECT
  93  * @return VLC_SUCCESS or VLC_EGENERIC
  94  */
  95
  96 int FindAdv( media_library_t *p_ml, vlc_array_t *p_result_array,
  97              ml_select_e selected_type, const char* psz_lvalue, ml_ftree_t *tree )
  98 {
  99     int i_ret = VLC_SUCCESS;
 100     char *psz_query;
 101     ml_result_type_e result_type;
 102     char **pp_results = NULL;
 103     int i_cols, i_rows;
 104
 105     if( !p_result_array )
 106         return VLC_EGENERIC;
 107
 108     i_ret = BuildSelect( p_ml, &psz_query, &result_type, psz_lvalue,
 109                          selected_type, tree );
 110
 111     if( i_ret != VLC_SUCCESS )
 112         return i_ret;
 113
 114     if( Query( p_ml, &pp_results, &i_rows, &i_cols, "%s", psz_query )
 115         != VLC_SUCCESS )
 116     {
 117         msg_Err( p_ml, "Error occurred while making the query to the database" );
 118         return VLC_EGENERIC;
 119     }
 120
 121     i_ret = SQLToResultArray( p_ml, p_result_array, pp_results, i_rows, i_cols,
 122                               result_type );
 123
 124     free( psz_query);
 125     FreeSQLResult( p_ml, pp_results );
 126
 127     return i_ret;
 128 }
 129
 130 /**
 131  * @brief Generic SELECT query builder with va_list parameter
 132  *
 133  * @param p_ml This media_library_t object
 134  * @param ppsz_query *ppsz_query will contain query
 135  * @param p_result_type see enum ml_result_type_e
 136  * @param criterias list of criterias used in SELECT
 137  * @return VLC_SUCCESS or a VLC error code
 138  * NOTE va_list criterias must end with ML_END or this will fail (segfault)
 139  *
 140  * This function handles results of only one column (or two if ID is included),
 141  * of 'normal' types: int and strings
 142  */
 143 int BuildSelectVa( media_library_t *p_ml, char **ppsz_query,
 144                    ml_result_type_e *p_result_type, va_list criterias )
 145 {
 146     int i_continue = 1;
 147     ml_ftree_t* p_ftree = NULL;
 148     char* psz_lvalue = NULL;
 149
 150     /* Get the name of the data we want */
 151     ml_select_e selected_type = va_arg( criterias, int );
 152     if( selected_type == ML_PEOPLE || selected_type == ML_PEOPLE_ID ||
 153             selected_type == ML_PEOPLE_ROLE )
 154         psz_lvalue = va_arg( criterias, char * );
 155
 156     /* Loop on every arguments */
 157     while( i_continue )
 158     {
 159         ml_ftree_t *p_find = ( ml_ftree_t* ) calloc( 1, sizeof( ml_ftree_t ) );
 160         if( !p_find )
 161             return VLC_ENOMEM;
 162         p_find->criteria = va_arg( criterias, int );
 163         p_find->comp = ML_COMP_EQUAL;
 164         switch( p_find->criteria )
 165         {
 166             case ML_SORT_ASC:
 167                 p_ftree = ml_FtreeSpecAsc( p_ftree, va_arg( criterias, char* ) ); break;
 168             case ML_SORT_DESC:
 169                 p_ftree = ml_FtreeSpecDesc( p_ftree, va_arg( criterias, char* ) ); break;
 170             case ML_DISTINCT:
 171                 p_ftree = ml_FtreeSpecDistinct( p_ftree ); break;
 172             case ML_LIMIT:
 173                 p_ftree = ml_FtreeSpecLimit( p_ftree, va_arg( criterias, int ) );
 174                 break;
 175             case ML_ARTIST:
 176                 /* This is OK because of a shallow free find */
 177                 p_find->lvalue.str = (char *)ML_PERSON_ARTIST;
 178                 p_find->value.str = va_arg( criterias, char* );
 179                 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
 180                 break;
 181             case ML_PEOPLE:
 182                 p_find->lvalue.str = va_arg( criterias, char* );
 183                 p_find->value.str = va_arg( criterias, char* );
 184                 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
 185                 break;
 186             case ML_PEOPLE_ID:
 187                 p_find->lvalue.str = va_arg( criterias, char* );
 188                 p_find->value.i = va_arg( criterias, int );
 189                 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
 190                 break;
 191             case ML_END:
 192                 i_continue = 0;
 193                 break;
 194             default:
 195                 switch( ml_AttributeIsString( p_find->criteria ) )
 196                 {
 197                     case 0:
 198                         p_find->value.i = va_arg( criterias, int );
 199                         break;
 200                     case 1:
 201                         p_find->value.str = va_arg( criterias, char* );
 202                         break;
 203                 }
 204                 p_ftree = ml_FtreeFastAnd( p_ftree, p_find );
 205                 break;
 206         }
 207     }
 208
 209     int i_ret = BuildSelect( p_ml, ppsz_query, p_result_type, psz_lvalue,
 210                              selected_type, p_ftree );
 211
 212     ml_ShallowFreeFindTree( p_ftree );
 213     return i_ret;
 214 }
 215
 216 /**
 217  * @brief Append a string and format it using SQL vmprintf
 218  **/
 219 static int AppendStringFmtVa( media_library_t *p_ml,
 220                               char **ppsz_dst, const char *psz_fmt,
 221                               va_list args )
 222 {
 223     char *psz_tmp = NULL, *psz_fullexp = NULL;
 224
 225     assert( ppsz_dst != NULL );
 226
 227     if( !( *ppsz_dst ) )
 228     {
 229         /* New expression */
 230         *ppsz_dst = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
 231         if( !( *ppsz_dst ) )
 232             return VLC_ENOMEM;
 233     }
 234     else
 235     {
 236         /* Create new expression B */
 237         psz_tmp = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, args );
 238         if( !psz_tmp )
 239             return VLC_ENOMEM;
 240
 241         if( asprintf( &psz_fullexp, "%s%s", *ppsz_dst, psz_tmp ) == -1 )
 242         {
 243             free( psz_tmp );
 244             return VLC_ENOMEM;
 245         }
 246
 247         free( *ppsz_dst );
 248         *ppsz_dst = psz_fullexp;
 249     }
 250
 251     return VLC_SUCCESS;
 252 }
 253
 254 static int AppendStringFmt( media_library_t *p_ml,
 255                             char **ppsz_dst, const char *psz_fmt, ... )
 256 {
 257     va_list args;
 258     va_start( args, psz_fmt );
 259     int i_ret = AppendStringFmtVa( p_ml, ppsz_dst, psz_fmt, args );
 260     va_end( args );
 261     return i_ret;
 262 }
 263
 264 /* Early Declaration of Where String Generator */
 265 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t* tree,
 266        char** sort, int* limit, const char** distinct, char*** pppsz_frompersons,
 267        int* i_frompersons, int* join );
 268
 269 #   define table_media             (1 << 0)
 270 #   define table_album             (1 << 1)
 271 #   define table_people            (1 << 2)
 272 #   define table_extra             (1 << 3)
 273
 274 static void PackFromPersons( char*** pppsz_frompersons, int i_num_frompersons )
 275 {
 276     for( int i = 0; i < i_num_frompersons; i++ )
 277     {
 278         if( *pppsz_frompersons[i] == NULL )
 279             continue;
 280         for( int j = i+1; j < i_num_frompersons; j++ )
 281         {
 282             if( strcmp( *pppsz_frompersons[i], *pppsz_frompersons[j] ) == 0 )
 283             {
 284                 *pppsz_frompersons[j] = NULL;
 285             }
 286         }
 287     }
 288 }
 289 /**
 290  * @brief Generic SELECT query builder
 291  *
 292  * @param p_ml This media_library_t object
 293  * @param ppsz_query *ppsz_query will contain query
 294  * @param p_result_type see enum ml_result_type_e
 295  * @param selected_type the type of the element we're selecting
 296  * @param tree the find tree
 297  * @return VLC_SUCCESS or VLC_EGENERIC
 298  */
 299 int BuildSelect( media_library_t *p_ml,
 300                  char **ppsz_query, ml_result_type_e *p_result_type,
 301                  const char *psz_selected_type_lvalue, ml_select_e selected_type,
 302                  ml_ftree_t *tree )
 303 {
 304     /* Basic verification */
 305     if( !ppsz_query )
 306         return VLC_EGENERIC;
 307
 308     int i_ret = VLC_SUCCESS;
 309     char *psz_query = NULL;
 310
 311     /* Building psz_query :
 312     psz_query = "SELECT psz_distinct psz_select
 313                  FROM psz_from [JOIN psz_join ON psz_on]
 314                  [JOIN psz_join2 ON psz_on2]
 315                  [WHERE psz_where[i] [AND psz_where[j] ...]]
 316                  [LIMIT psz_limit] [ORDER BY psz_select psz_sort] ;"
 317     */
 318     char *psz_select             = NULL;
 319     const char *psz_distinct     = ""; /* "DISTINCT" or "" */
 320
 321     /* FROM */
 322     char *psz_from               = NULL;
 323     int i_from                   = 0; /* Main select table */
 324
 325     char **ppsz_frompersons      = NULL;
 326     int  i_num_frompersons       = 0;
 327     char *psz_peoplerole         = NULL; /* Person to get selected */
 328
 329     /* JOIN ... ON ... */
 330     char *psz_join               = NULL;
 331     char *psz_join2              = NULL;
 332     char *psz_on                 = NULL;
 333     char *psz_on2                = NULL;
 334     int i_join                   = 0;    /* Tables that need to be joined */
 335
 336     /* String buffers */
 337     char *psz_where              = NULL;
 338     char *psz_sort               = NULL; /* ASC or DESC or NULL */
 339     char *psz_tmp                = NULL;
 340
 341     int i_limit                  = 0;
 342
 343     /* Build the WHERE condition */
 344     BuildWhere( p_ml, &psz_where, tree, &psz_sort, &i_limit,
 345             &psz_distinct, &ppsz_frompersons, &i_num_frompersons, &i_join );
 346
 347     PackFromPersons( &ppsz_frompersons, i_num_frompersons );
 348
 349     /* What is the result type? */
 350     ml_result_type_e res_type   = ML_TYPE_PSZ;
 351
 352     /* SELECT, FROM */
 353     /* Note that a DISTINCT select makes id of result non sense */
 354     switch( selected_type )
 355     {
 356     case ML_ALBUM:
 357         psz_select   = ( !*psz_distinct ) ?
 358                         strdup( "album.id, album.title AS album_title" )
 359                         : strdup( "album.title AS album_title" );
 360         i_from       = table_album;
 361         break;
 362     case ML_ALBUM_COVER:
 363         psz_select   = ( !*psz_distinct ) ?
 364                 strdup( "album.id, album.cover" ) : strdup( "album.cover" );
 365         i_from       = table_album;
 366         break;
 367     case ML_ALBUM_ID:
 368         psz_select   = strdup( "album.id" );
 369         psz_distinct = "DISTINCT";
 370         i_from       = table_album;
 371         res_type     = ML_TYPE_INT;
 372         break;
 373     case ML_ARTIST:
 374         psz_select   = ( !*psz_distinct ) ?
 375                         strdup( "people_Artist.id, people_Artist.name" )
 376                         : strdup( "people_Artist.name" );
 377         i_from       = table_people;
 378         psz_peoplerole = strdup( ML_PERSON_ARTIST );
 379         break;
 380     case ML_ARTIST_ID:
 381         psz_select   = strdup( "people_Artist.id" );
 382         psz_distinct = "DISTINCT";
 383         i_from       = table_people;
 384         res_type     = ML_TYPE_INT;
 385         psz_peoplerole = strdup( ML_PERSON_ARTIST );
 386         break;
 387     case ML_COVER:
 388         psz_select   = ( !*psz_distinct ) ?
 389                 strdup( "media.id, media.cover" ) : strdup( "media.cover" );
 390         i_from       = table_media;
 391         break;
 392     case ML_COMMENT:
 393         psz_select   = ( !*psz_distinct ) ?
 394                 strdup( "media.id, extra.comment" ) : strdup( "extra.comment" );
 395         i_from       = table_extra;
 396         break;
 397     case ML_GENRE:
 398         psz_select   = ( !*psz_distinct ) ?
 399                strdup( "media.id, media.genre" ) : strdup( "media.genre" );
 400         i_from       = table_media;
 401         break;
 402     case ML_COUNT_MEDIA:
 403         psz_select   = ( !*psz_distinct ) ?
 404                 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT media.id )" );
 405         i_from       = table_media;
 406         res_type     = ML_TYPE_INT;
 407         break;
 408     case ML_COUNT_ALBUM:
 409         psz_select   = ( !*psz_distinct ) ?
 410                 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT album.id )" );
 411         i_from       = table_album;
 412         res_type     = ML_TYPE_INT;
 413         break;
 414     case ML_COUNT_PEOPLE:
 415         psz_select   = ( !*psz_distinct ) ?
 416                 strdup( "COUNT()" ) : strdup( "COUNT( DISTINCT people.id )" );
 417         i_from       = table_people;
 418         res_type     = ML_TYPE_INT;
 419         break;
 420     case ML_FILESIZE:
 421         psz_select   = strdup( "media.filesize" );
 422         i_from       = table_media;
 423         res_type     = ML_TYPE_INT;
 424         break;
 425     case ML_ID:
 426         psz_select   = strdup( "media.id" ); /* ID: must be distinct */
 427         psz_distinct = "DISTINCT";
 428         i_from       = table_media;
 429         res_type     = ML_TYPE_INT;
 430         break;
 431     case ML_LANGUAGE:
 432         psz_select   = strdup( "extra.language" );
 433         psz_distinct = "DISTINCT";
 434         i_from       = table_extra;
 435         break;
 436     case ML_MEDIA_SPARSE:
 437         i_ret = AppendStringFmt( p_ml, &psz_select, "media.id AS id,"
 438                 "media.uri AS uri,"
 439                 "media.type AS type,"
 440                 "media.title AS title,"
 441                 "media.duration AS duration,"
 442                 "media.original_title AS original_title,"
 443                 "media.album_id AS album_id,"
 444                 "media.cover AS cover,"
 445                 "media.preview AS preview,"
 446                 "media.disc AS disc,"
 447                 "media.track AS track,"
 448                 "media.year AS year,"
 449                 "media.genre AS genre,"
 450                 "media.played_count AS played_count,"
 451                 "media.last_played AS last_played,"
 452                 "media.first_played AS first_played,"
 453                 "media.import_time AS import_time,"
 454                 "media.skipped_count AS skipped_count,"
 455                 "media.last_skipped AS last_skipped,"
 456                 "media.vote AS vote,"
 457                 "media.score AS score,"
 458                 "media.comment AS comment,"
 459                 "media.filesize AS filesize,"
 460                 "album.title AS album_title,"
 461                 "album.cover AS album_cover,"
 462         "(SELECT name FROM media_to_people JOIN people "
 463         "ON (people_id = id) WHERE media_id = media.id AND role = %Q LIMIT 1) AS people_%s",
 464         ML_PERSON_ARTIST, ML_PERSON_ARTIST );
 465         if( i_ret != VLC_SUCCESS )
 466             goto exit;
 467         i_from       = table_media;
 468         i_join      |= ( table_album | table_people );
 469         psz_distinct = "DISTINCT";
 470         res_type     = ML_TYPE_MEDIA;
 471         break;
 472     case ML_MEDIA:
 473         /* Who said this was over-complicated ?? */
 474         /* Yea right. */
 475         psz_select   = strdup( "media.id AS id,"
 476                 "media.uri AS uri,"
 477                 "media.type AS type,"
 478                 "media.title AS title,"
 479                 "media.duration AS duration,"
 480                 "media.original_title AS original_title,"
 481                 "media.album_id AS album_id,"
 482                 "media.cover AS cover,"
 483                 "media.preview AS preview,"
 484                 "media.disc as disc,"
 485                 "media.track AS track,"
 486                 "media.year AS year,"
 487                 "media.genre AS genre,"
 488                 "media.played_count AS played_count,"
 489                 "media.last_played AS last_played,"
 490                 "media.first_played AS first_played,"
 491                 "media.import_time AS import_time,"
 492                 "media.last_skipped AS last_skipped,"
 493                 "media.skipped_count AS skipped_count,"
 494                 "media.vote AS vote,"
 495                 "media.score AS score,"
 496                 "media.comment AS comment,"
 497                 "media.filesize AS filesize,"
 498                 "album.title AS album_title,"
 499                 "album.cover AS album_cover,"
 500                 "people.id AS people_id,"
 501                 "people.name AS people_name,"
 502                 "people.role AS people_role,"
 503                 "extra.language AS language,"
 504                 "extra.extra AS extra" );
 505         i_from       = table_media;
 506         i_join      |= ( table_album | table_people | table_extra );
 507         psz_distinct = "DISTINCT";
 508         res_type     = ML_TYPE_MEDIA;
 509         break;
 510     case ML_MEDIA_EXTRA:
 511         psz_select = strdup( "media.id AS id,"
 512                 "people.id AS people_id,"
 513                 "people.name AS people_name,"
 514                 "people.role AS people_role,"
 515                 "extra.extra AS extra,"
 516                 "extra.language AS language" );
 517         i_from       = table_media;
 518         i_join      |= ( table_album | table_people | table_extra );
 519         psz_distinct = "DISTINCT";
 520         res_type     = ML_TYPE_MEDIA;
 521         break;
 522     case ML_ORIGINAL_TITLE:
 523         psz_select   = ( !*psz_distinct ) ?
 524                strdup( "media.id, media.original_title" ) : strdup( "media.original_title" );
 525         i_from       = table_media;
 526         break;
 527     /* For people, if lvalue = "", then we want ANY people. */
 528     case ML_PEOPLE:
 529         assert( psz_selected_type_lvalue );
 530         i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.name",
 531            *psz_selected_type_lvalue ? "_" : "",
 532            *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
 533         if( i_ret != VLC_SUCCESS )
 534             goto exit;
 535         if( *psz_distinct )
 536         {
 537             i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.name",
 538                *psz_selected_type_lvalue ? "_" : "",
 539                *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
 540             if( i_ret != VLC_SUCCESS )
 541                 goto exit;
 542         }
 543         i_from         = table_people;
 544         psz_peoplerole = strdup( psz_selected_type_lvalue );
 545         break;
 546     case ML_PEOPLE_ID:
 547         assert( psz_selected_type_lvalue );
 548         i_ret = AppendStringFmt( p_ml, &psz_select, "people%s%s.id",
 549            *psz_selected_type_lvalue ? "_" : "",
 550            *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
 551         if( i_ret != VLC_SUCCESS )
 552             goto exit;
 553         if( *psz_distinct )
 554         {
 555             i_ret = AppendStringFmt( p_ml, &psz_select, ", people%s%s.id",
 556                *psz_selected_type_lvalue ? "_" : "",
 557                *psz_selected_type_lvalue ? psz_selected_type_lvalue : "" );
 558             if( i_ret != VLC_SUCCESS )
 559                 goto exit;
 560         }
 561         psz_distinct   = "DISTINCT";
 562         i_from         = table_people;
 563         psz_peoplerole = strdup( psz_selected_type_lvalue );
 564         res_type       = ML_TYPE_INT;
 565         break;
 566     case ML_PEOPLE_ROLE:
 567         psz_select   = strdup( "people.role" );
 568         psz_distinct = "DISTINCT";
 569         i_from       = table_people;
 570         break;
 571     case ML_TITLE:
 572         psz_select   = ( !*psz_distinct ) ?
 573                 strdup( "media.id, media.title" ) : strdup( "media.title" );
 574         i_from       = table_media;
 575         break;
 576     case ML_TYPE:
 577         psz_select   = ( !*psz_distinct ) ?
 578                 strdup( "media.id, media.type" ): strdup( "media.type" );
 579         i_from       = table_media;
 580         res_type     = ML_TYPE_INT;
 581         break;
 582     case ML_URI:
 583         psz_select   = ( !*psz_distinct ) ?
 584                 strdup( "media.id, media.uri" ) : strdup( "media.uri" );
 585         i_from       = table_media;
 586         break;
 587     case ML_VOTE:
 588         psz_select   = ( !*psz_distinct ) ?
 589                 strdup( "media.id, media.vote" ) : strdup( "media.vote" );
 590         i_from       = table_media;
 591         res_type     = ML_TYPE_INT;
 592         break;
 593     case ML_YEAR:
 594         psz_select   = ( !*psz_distinct ) ?
 595                strdup( "media.id, media.year" ) : strdup( "media.year" );
 596         i_from       = table_media;
 597         res_type     = ML_TYPE_INT;
 598         break;
 599     case ML_LIMIT:
 600     case ML_SORT_DESC:
 601     case ML_SORT_ASC:
 602     case ML_END:
 603     default:
 604         msg_Dbg( p_ml, "unknown select (%d) in BuildSelect", selected_type );
 605         return VLC_EGENERIC;
 606     }
 607
 608     /* Let's build full psz_query ! */
 609     i_ret = VLC_SUCCESS;
 610
 611     /* Figure out select and join tables */
 612     switch( i_from )
 613     {
 614         case table_media:
 615             break;
 616         case table_album:
 617             switch( i_join )
 618             {
 619                 case 0: break;
 620                 case 2: i_join = 0; break;
 621                 case 1:
 622                 case 3: i_from = table_media; i_join = table_album; break;
 623                 case 4:
 624                 case 5:
 625                 case 6:
 626                 case 7: i_from = table_media; i_join = table_album | table_people; break;
 627                 case 8:
 628                 case 9:
 629                 case 10:
 630                 case 11: i_from = table_media; i_join = table_extra | table_album; break;
 631                 case 12:
 632                 case 13:
 633                 case 14:
 634                 case 15: i_from = table_media; i_join = table_extra | table_album | table_people; break;
 635                 default: break;
 636             }
 637             break;
 638         case table_people:
 639             switch( i_join )
 640             {
 641                 case 0: break;
 642                 case 1: i_from = table_media; i_join = table_people; break;
 643                 case 2:
 644                 case 3: i_from = table_media; i_join = table_album | table_people; break;
 645                 case 4:
 646                     /* Determine if a join from media is required */
 647                     if( i_num_frompersons > 1 )
 648                         i_from = table_media;
 649                     else
 650                         i_join = 0;
 651                     break;
 652                 case 5: i_from = table_media; i_join = table_people; break;
 653                 case 6:
 654                 case 7: i_from = table_media; i_join = table_album | table_people; break;
 655                 case 8:
 656                 case 9: i_from = table_media; i_join = table_people | table_extra; break;
 657                 case 10:
 658                 case 11: i_from = table_media; i_join = table_people | table_album | table_extra; break;
 659                 case 12:
 660                 case 13: i_from = table_media; i_join = table_people | table_extra; break;
 661                 case 14:
 662                 case 15: i_from = table_media; i_join = table_people | table_album | table_extra; break;
 663                 default: break;
 664             }
 665             break;
 666         case table_extra:
 667             switch( i_join )
 668             {
 669                 case 0: break;
 670                 case 1: i_from = table_media; i_join = table_extra; break;
 671                 case 2:
 672                 case 3: i_from = table_media; i_join = table_extra | table_album; break;
 673                 case 4:
 674                 case 5: i_from = table_media; i_join = table_extra | table_people; break;
 675                 case 6:
 676                 case 7: i_from = table_media; i_join = table_extra | table_people | table_album; break;
 677                 case 8: i_from = table_extra; i_join = 0; break;
 678                 case 9: i_from = table_media; i_join = table_extra; break;
 679                 case 10:
 680                 case 11: i_from = table_media; i_join = table_extra | table_album; break;
 681                 case 12:
 682                 case 13: i_from = table_media; i_join = table_extra | table_people; break;
 683                 case 14:
 684                 case 15: i_from = table_media; i_join = table_extra | table_people | table_album; break;
 685                 default: break;
 686             }
 687             break;
 688         default: msg_Warn( p_ml, "You can't be selecting from this table!!" );
 689                  i_ret = VLC_EGENERIC;
 690                  goto exit;
 691     }
 692
 693     assert( !( i_from & table_album && i_join & table_album ) );
 694     assert( !( i_from & table_people && i_join & table_people ) );
 695     assert( !( i_from & table_extra && i_join & table_extra ) );
 696
 697     /* Generate FROM - psz_from */
 698     if( i_from == table_media )
 699         i_ret = AppendStringFmt( p_ml, &psz_from, "media" );
 700     else if( i_from == table_album )
 701         i_ret = AppendStringFmt( p_ml, &psz_from, "album" );
 702     else if( i_from == table_extra )
 703         i_ret = AppendStringFmt( p_ml, &psz_from, "extra" );
 704     else if( i_from == table_people )
 705     {
 706         i_ret = AppendStringFmt( p_ml, &psz_from, "people AS people%s%s",
 707                 psz_peoplerole ? "_" : "", psz_peoplerole );
 708         if( i_ret < 0 ) goto exit;
 709
 710         /* The ugly next statement is only required if persons are being
 711          * selected. Otherwise the joins will handle this */
 712         if( psz_peoplerole && *psz_peoplerole )
 713         {
 714             i_ret = AppendStringFmt( p_ml, &psz_where, "%s people_%s.role = %Q ",
 715                                      ( psz_where && *psz_where ) ? " AND" : "",
 716                                      psz_peoplerole, psz_peoplerole );
 717             if( i_ret < 0 ) goto exit;
 718         }
 719     }
 720     if( i_ret < 0 ) goto exit;
 721
 722     i_ret = AppendStringFmt( p_ml, &psz_query,
 723                              "SELECT %s %s ", psz_distinct, psz_select );
 724     if( i_ret < 0 ) goto exit;
 725
 726     i_ret = AppendStringFmt( p_ml, &psz_query, "FROM %s ", psz_from );
 727     if( i_ret < 0 ) goto exit;
 728
 729     /* Create join conditions */
 730     if( i_join & table_people )
 731     {
 732         /* we can join psz_peoplerole safely because
 733          * if i_join = people, then i_from != people */
 734         bool join = true;
 735         for( int i = 0; i < i_num_frompersons ; i++ )
 736         {
 737             /* We assume ppsz_frompersons has unique entries and
 738              * if ppsz_frompersons[i] is empty(but not NULL), then it
 739              * means we accept any role */
 740             if( ppsz_frompersons[i] && *ppsz_frompersons[i] )
 741             {
 742                 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
 743                     join = false;
 744                 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ",
 745                         psz_join == NULL ? "" : ",", ppsz_frompersons[i] );
 746                 /* This is possible because from is usually the media table */
 747                 AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ",
 748                         psz_on == NULL ? "" : " AND ", ppsz_frompersons[i] );
 749                 AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ",
 750                         psz_join2 == NULL ? "" : ",", ppsz_frompersons[i] );
 751                 AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND "
 752                         "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ",
 753                         ppsz_frompersons[i], ppsz_frompersons[i],
 754                         ppsz_frompersons[i], ppsz_frompersons[i]  );
 755             }
 756             else if( ppsz_frompersons[i] )
 757             {
 758                 if( strcmp( psz_peoplerole, ppsz_frompersons[i] ) == 0 )
 759                     join = false;
 760                 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ",
 761                         psz_join == NULL ? "" : "," );
 762                 /* This is possible because from is usually the media table */
 763                 AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ",
 764                         psz_on == NULL ? "" : " AND " );
 765                 AppendStringFmt( p_ml, &psz_join2, "%speople AS people ",
 766                         psz_join2 == NULL ? "" : "," );
 767                 AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id",
 768                         psz_on2 == NULL ? "" : " AND " );
 769             }
 770         }
 771         if( join )
 772         {
 773             if( psz_peoplerole && *psz_peoplerole )
 774             {
 775                 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS people_%sx ",
 776                         psz_join == NULL ? "" : ",", psz_peoplerole );
 777                 /* This is possible because from is always the media table */
 778                 AppendStringFmt( p_ml, &psz_on, "%speople_%sx.media_id = media.id ",
 779                         psz_on == NULL ? "" : " AND ", psz_peoplerole );
 780                 AppendStringFmt( p_ml, &psz_join2, "%speople AS people_%s ",
 781                         psz_join2 == NULL ? "" : ",", psz_peoplerole );
 782                 AppendStringFmt( p_ml, &psz_on2, "%s ( people_%sx.people_id = people_%s.id AND "
 783                         "people_%s.role = %Q )", psz_on2 == NULL ? "" : " AND ",
 784                         psz_peoplerole, psz_peoplerole,
 785                         psz_peoplerole, psz_peoplerole );
 786             }
 787             else
 788             {
 789                 AppendStringFmt( p_ml, &psz_join, "%smedia_to_people AS peoplex ",
 790                         psz_join == NULL ? "" : "," );
 791                 /* This is possible because from is usually the media table */
 792                 AppendStringFmt( p_ml, &psz_on, "%speoplex.media_id = media.id ",
 793                         psz_on == NULL ? "" : " AND " );
 794                 AppendStringFmt( p_ml, &psz_join2, "%speople ",
 795                         psz_join2 == NULL ? "" : "," );
 796                 AppendStringFmt( p_ml, &psz_on2, "%s peoplex.people_id = people.id",
 797                         psz_on2 == NULL ? "" : " AND " );
 798
 799             }
 800         }
 801     }
 802     if( i_join & table_album )
 803     {
 804         AppendStringFmt( p_ml, &psz_join, "%salbum", psz_join == NULL ? "" : "," );
 805         AppendStringFmt( p_ml, &psz_on, "%s album.id = media.album_id ",
 806                 psz_on == NULL ? "" : " AND " );
 807     }
 808     if( i_join & table_extra )
 809     {
 810         AppendStringFmt( p_ml, &psz_join, "%sextra", psz_join == NULL ? "" : "," );
 811         AppendStringFmt( p_ml, &psz_on, "%s extra.id = media.id ",
 812                 psz_on == NULL ? "" : " AND " );
 813     }
 814
 815     /* Complete the join clauses */
 816     if( psz_join )
 817     {
 818         AppendStringFmt( p_ml, &psz_query,
 819                          "JOIN %s ON %s ", psz_join, psz_on );
 820     }
 821     if( psz_join2 )
 822     {
 823         AppendStringFmt( p_ml, &psz_query,
 824                          "JOIN %s ON %s ", psz_join2, psz_on2 );
 825     }
 826     if( psz_where && *psz_where )
 827     {
 828         AppendStringFmt( p_ml, &psz_query,
 829                          "WHERE %s ", psz_where );
 830     }
 831     /* TODO: FIXME: Limit on media objects doesn't work! */
 832     if( i_limit )
 833     {
 834         AppendStringFmt( p_ml, &psz_query,
 835                          "LIMIT %d ", i_limit );
 836     }
 837
 838     if( psz_sort )
 839     {
 840         AppendStringFmt( p_ml, &psz_query,
 841                          "ORDER BY %s %s", psz_select, psz_sort );
 842     }
 843
 844     if( i_ret > 0 ) i_ret = VLC_SUCCESS;
 845
 846     if( p_result_type ) *p_result_type = res_type;
 847     if( !psz_query )    i_ret = VLC_EGENERIC;
 848     else                *ppsz_query = strdup( psz_query );
 849
 850 exit:
 851     free( psz_query );
 852     free( psz_where );
 853     free( psz_tmp   );
 854     free( psz_from  );
 855     free( psz_join  );
 856     free( psz_select );
 857     free( psz_join2 );
 858     free( psz_on    );
 859     free( psz_on2   );
 860     free( psz_peoplerole );
 861     free( ppsz_frompersons );
 862
 863     if( i_ret != VLC_SUCCESS )
 864         msg_Warn( p_ml, "an unknown error occurred (%d)", i_ret );
 865
 866     return i_ret;
 867 }
 868
 869 #undef CASE_INT
 870 #define CASE_INT( casestr, fmt, table )                                     \
 871 case casestr:                                                               \
 872 assert( tree->comp != ML_COMP_HAS && tree->comp != ML_COMP_STARTS_WITH      \
 873         && tree->comp != ML_COMP_ENDS_WITH );                               \
 874 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "%s %s %d", fmt,              \
 875     tree->comp == ML_COMP_LESSER ? "<" :                                    \
 876     tree->comp == ML_COMP_LESSER_OR_EQUAL ? "<=" :                          \
 877     tree->comp == ML_COMP_GREATER ? ">" :                                   \
 878     tree->comp == ML_COMP_GREATER_OR_EQUAL ? ">=" : "=", tree->value.i );   \
 879 if( *ppsz_where == NULL )                                                   \
 880     goto parsefail;                                                         \
 881 *join |= table;                                                             \
 882 break
 883
 884 #undef CASE_PSZ
 885 #define CASE_PSZ( casestr, fmt, table )                                       \
 886 case casestr:                                                                 \
 887     assert( tree->comp == ML_COMP_HAS || tree->comp == ML_COMP_EQUAL          \
 888         || tree->comp == ML_COMP_STARTS_WITH                                  \
 889         || tree->comp == ML_COMP_ENDS_WITH );                                 \
 890     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "%s LIKE '%s%q%s'", fmt,    \
 891         tree->comp == ML_COMP_HAS                                             \
 892         || tree->comp == ML_COMP_STARTS_WITH? "%%" : "",                      \
 893             tree->value.str,                                                  \
 894         tree->comp == ML_COMP_HAS                                             \
 895         || tree->comp == ML_COMP_ENDS_WITH? "%%" : "" );                      \
 896     if( *ppsz_where == NULL )                                                 \
 897         goto parsefail;                                                       \
 898     *join |= table;                                                           \
 899     break
 900
 901 #define SLDPJ sort, limit, distinct, pppsz_frompersons, i_frompersons, join
 902 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t* tree,
 903        char** sort, int* limit, const char** distinct,
 904        char*** pppsz_frompersons, int* i_frompersons, int* join )
 905 {
 906     assert( ppsz_where && sort && distinct );
 907     if( !tree ) /* Base case */
 908     {
 909         return VLC_SUCCESS;
 910     }
 911
 912     int i_ret = VLC_EGENERIC;
 913     char* psz_left = NULL;
 914     char* psz_right = NULL;
 915
 916     switch( tree->op )
 917     {
 918         case ML_OP_AND:
 919         case ML_OP_OR:
 920             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 921             if( i_ret != VLC_SUCCESS )
 922                 goto parsefail;
 923             i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
 924             if( i_ret != VLC_SUCCESS )
 925                 goto parsefail;
 926             if( psz_left == NULL || psz_right == NULL )
 927             {
 928                 msg_Err( p_ml, "Parsing failed for AND/OR" );
 929                 i_ret = VLC_EGENERIC;
 930                 goto parsefail;
 931             }
 932             if( asprintf( ppsz_where, "( %s %s %s )", psz_left,
 933                 ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 )
 934             {
 935                 i_ret = VLC_ENOMEM;
 936                 goto parsefail;
 937             }
 938             break;
 939         case ML_OP_NOT:
 940             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 941             if( i_ret != VLC_SUCCESS )
 942                 goto parsefail;
 943             if( psz_left == NULL )
 944             {
 945                 msg_Err( p_ml, "Parsing failed at NOT" );
 946                 i_ret = VLC_EGENERIC;
 947                 goto parsefail;
 948             }
 949             if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 )
 950             {
 951                 i_ret = VLC_ENOMEM;
 952                 goto parsefail;
 953             }
 954             break;
 955         case ML_OP_SPECIAL:
 956             i_ret = BuildWhere( p_ml, &psz_right, tree->right, SLDPJ );
 957             if( i_ret != VLC_SUCCESS )
 958                 goto parsefail;
 959             i_ret = BuildWhere( p_ml, &psz_left, tree->left, SLDPJ );
 960             if( i_ret != VLC_SUCCESS )
 961                 goto parsefail;
 962             /* Ignore right parse tree as this is a special node */
 963             *ppsz_where = strdup( psz_left ? psz_left : "" );
 964             if( !*ppsz_where )
 965             {
 966                 i_ret = VLC_ENOMEM;
 967                 goto parsefail;
 968             }
 969             break;
 970         case ML_OP_NONE:
 971             switch( tree->criteria )
 972             {
 973                 case ML_PEOPLE:
 974                     assert( tree->comp == ML_COMP_HAS
 975                             || tree->comp == ML_COMP_EQUAL
 976                             || tree->comp == ML_COMP_STARTS_WITH
 977                             || tree->comp == ML_COMP_ENDS_WITH );
 978                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
 979                             "people%s%s.name LIKE '%s%q%s'",
 980                             tree->lvalue.str ? "_" : "",
 981                             tree->lvalue.str ? tree->lvalue.str : "",
 982                             tree->comp == ML_COMP_HAS
 983                             || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
 984                             tree->value.str,
 985                             tree->comp == ML_COMP_HAS
 986                             || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
 987                     if( *ppsz_where == NULL )
 988                         goto parsefail;
 989                     *pppsz_frompersons = realloc( *pppsz_frompersons,
 990                             ++*i_frompersons * sizeof( char* ) );
 991                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
 992                     *join |= table_people;
 993                     break;
 994                 case ML_PEOPLE_ID:
 995                     assert( tree->comp == ML_COMP_EQUAL );
 996                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
 997                                 "( people%s%s.id = %d )", tree->lvalue.str ? "_":"",
 998                                 tree->lvalue.str ? tree->lvalue.str:"",
 999                                 tree->value.i );
1000                     if( *ppsz_where == NULL )
1001                         goto parsefail;
1002                     *pppsz_frompersons = realloc( *pppsz_frompersons,
1003                             ++*i_frompersons * sizeof( char* ) );
1004                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1005                     *join |= table_people;
1006                     break;
1007                 case ML_PEOPLE_ROLE:
1008                     assert( tree->comp == ML_COMP_HAS
1009                             || tree->comp == ML_COMP_EQUAL
1010                             || tree->comp == ML_COMP_STARTS_WITH
1011                             || tree->comp == ML_COMP_ENDS_WITH );
1012                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1013                             "people%s%s.role LIKE '%s%q%s'",
1014                             tree->lvalue.str ? "_" : "",
1015                             tree->lvalue.str ? tree->lvalue.str : "",
1016                             tree->comp == ML_COMP_HAS
1017                             || tree->comp == ML_COMP_STARTS_WITH ? "%%" : "",
1018                             tree->value.str,
1019                             tree->comp == ML_COMP_HAS
1020                             || tree->comp == ML_COMP_ENDS_WITH ? "%%" : "" );
1021                     if( *ppsz_where == NULL )
1022                         goto parsefail;
1023                     *pppsz_frompersons = realloc( *pppsz_frompersons,
1024                             ++*i_frompersons * sizeof( char* ) );
1025                     *pppsz_frompersons[ *i_frompersons - 1 ] = tree->lvalue.str;
1026                     *join |= table_people;
1027                     break;
1028                 CASE_PSZ( ML_ALBUM, "album.title", table_album );
1029                 CASE_PSZ( ML_ALBUM_COVER, "album.cover", table_album );
1030                 case ML_ALBUM_ID:
1031                     assert( tree->comp == ML_COMP_EQUAL );
1032                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1033                             "album.id = %d", tree->value.i );
1034                     if( *ppsz_where == NULL )
1035                         goto parsefail;
1036                     *join |= table_album;
1037                     break;
1038                 CASE_PSZ( ML_COMMENT, "media.comment", table_media );
1039                 CASE_PSZ( ML_COVER, "media.cover", table_media );
1040                 CASE_INT( ML_DURATION, "media.duration", table_media );
1041                 CASE_PSZ( ML_EXTRA, "extra.extra", table_extra );
1042                 CASE_INT( ML_FILESIZE, "media.filesize", table_media );
1043                 CASE_PSZ( ML_GENRE, "media.genre", table_media );
1044                 case ML_ID:
1045                     assert( tree->comp == ML_COMP_EQUAL );
1046                     *ppsz_where = sql_Printf( p_ml->p_sys->p_sql,
1047                             "media.id = %d", tree->value.i );
1048                     if( *ppsz_where == NULL )
1049                         goto parsefail;
1050                     *join |= table_media;
1051                     break;
1052                 CASE_PSZ( ML_LANGUAGE, "extra.language", table_extra );
1053                 CASE_INT( ML_LAST_PLAYED, "media.last_played", table_media );
1054                 CASE_PSZ( ML_ORIGINAL_TITLE, "media.original_title", table_media );
1055                    msg_Warn( p_ml, "Deprecated Played Count tags" );
1056                 CASE_INT( ML_PLAYED_COUNT, "media.played_count", table_media );
1057                 CASE_INT( ML_SCORE, "media.score", table_media );
1058                 CASE_PSZ( ML_TITLE, "media.title", table_media );
1059                 CASE_INT( ML_TRACK_NUMBER, "media.track", table_media);
1060                 CASE_INT( ML_TYPE, "media.type", table_media );
1061                 CASE_PSZ( ML_URI, "media.uri", table_media );
1062                 CASE_INT( ML_VOTE, "media.vote", table_media );
1063                 CASE_INT( ML_YEAR, "media.year", table_media );
1064                 case ML_LIMIT:
1065                     if( !*limit )
1066                         *limit = tree->value.i;
1067                     else
1068                         msg_Warn( p_ml, "Double LIMIT found" );
1069                     break;
1070                 case ML_SORT_DESC:
1071                     *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s DESC ",
1072                                         sort ? *sort : "", sort ? ", " : "",
1073                                         tree->value.str );
1074                     if( *sort == NULL )
1075                         goto parsefail;
1076                     break;
1077                 case ML_SORT_ASC:
1078                     *sort = sql_Printf( p_ml->p_sys->p_sql, "%s%s%s ASC ",
1079                                         sort ? *sort : "", sort ? ", " : "",
1080                                         tree->value.str );
1081                     if( *sort == NULL )
1082                         goto parsefail;
1083                     break;
1084                 case ML_DISTINCT:
1085                     if( !**distinct )
1086                         *distinct = "DISTINCT";
1087                     else
1088                         msg_Warn( p_ml, "Double DISTINCT found!" );
1089                     break;
1090                 default:
1091                     msg_Err( p_ml, "Invalid select type or unsupported: %d", tree->criteria );
1092             }
1093             break;
1094         default:
1095             msg_Err( p_ml, "Broken find tree!" );
1096             i_ret = VLC_EGENERIC;
1097             goto parsefail;
1098     }
1099
1100     i_ret = VLC_SUCCESS;
1101 parsefail:
1102     free( psz_left );
1103     free( psz_right );
1104     return i_ret;
1105 }
1106
1107
1108 #   undef CASE_INT
1109 #   undef CASE_PSZ
1110
1111 #   undef table_media
1112 #   undef table_album
1113 #   undef table_people
1114 #   undef table_extra