1 /*****************************************************************************
2 * sql_update.c: SQL-based media library: all database update functions
3 *****************************************************************************
4 * Copyright (C) 2008-2010 the VideoLAN team and AUTHORS
7 * Authors: Antoine Lejeune <phytos@videolan.org>
8 * Jean-Philippe André <jpeg@videolan.org>
9 * Rémi Duraffort <ivoire@videolan.org>
10 * Adrien Maglo <magsoft@videolan.org>
11 * Srikanth Raju <srikiraju at gmail dot com>
13 * This program is free software; you can redistribute it and/or modify
14 * it under the terms of the GNU General Public License as published by
15 * the Free Software Foundation; either version 2 of the License, or
16 * (at your option) any later version.
18 * This program is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License
24 * along with this program; if not, write to the Free Software
25 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
26 *****************************************************************************/
28 #include "sql_media_library.h"
32 * @brief Generic update in Media Library database
34 * @param p_ml the media library object
35 * @param selected_type the type of the element we're selecting
36 * @param where the list of ids or uri to change
37 * @param changes list of changes to make in the entries
38 * @return VLC_SUCCESS or VLC_EGENERIC
39 * @note This function is transactional
41 int Update( media_library_t *p_ml, ml_select_e selected_type,
42 const char* psz_lvalue, ml_ftree_t *where, vlc_array_t *changes )
44 int i_ret = VLC_EGENERIC;
45 char *psz_query = NULL;
46 char *psz_id_query = NULL;
47 char **pp_results = NULL;
48 int i_rows = 0, i_cols = 0;
50 i_ret = BuildUpdate( p_ml, &psz_query, &psz_id_query,
51 psz_lvalue, selected_type, where, changes );
53 if( i_ret != VLC_SUCCESS )
55 msg_Err(p_ml,"Failed to generate update query" );
61 if( QuerySimple( p_ml, "%s", psz_query ) != VLC_SUCCESS )
63 msg_Err( p_ml, "Couldn't run the generated update query successfully" );
67 /* Get the updated IDs to send events! */
68 if( Query( p_ml, &pp_results, &i_rows, &i_cols, psz_id_query )
74 if( i_ret != VLC_SUCCESS )
81 for( int i = 0; i < i_rows; i++ )
83 var_SetInteger( p_ml, "media-meta-change",
84 atoi( pp_results[i*i_cols] ) );
88 FreeSQLResult( p_ml, pp_results );
94 #define SET_STR( a ) \
95 if( !psz_set[i_type] ) \
97 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, a, find->value.str ); \
98 if( !psz_set[i_type] ) \
99 goto quit_buildupdate; \
103 #define SET_INT( a ) \
104 if( !psz_set[i_type] ) \
106 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, a, find->value.i ); \
107 if( !psz_set[i_type] ) \
108 goto quit_buildupdate; \
112 /* TODO: Build smarter updates by using IN () */
113 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t *tree )
115 assert( ppsz_where );
116 char* psz_left = NULL;
117 char* psz_right = NULL;
118 int i_ret = VLC_SUCCESS;
123 i_ret = BuildWhere( p_ml, &psz_left, tree->left );
124 if( i_ret != VLC_SUCCESS )
125 goto quit_buildwhere;
126 i_ret = BuildWhere( p_ml, &psz_right, tree->right );
127 if( i_ret != VLC_SUCCESS )
128 goto quit_buildwhere;
129 if( psz_left == NULL || psz_right == NULL )
131 msg_Err( p_ml, "Couldn't build AND/OR for Update statement" );
132 i_ret = VLC_EGENERIC;
133 goto quit_buildwhere;
135 if( asprintf( ppsz_where, "( %s %s %s )", psz_left,
136 ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 )
139 goto quit_buildwhere;
143 i_ret = BuildWhere( p_ml, &psz_left, tree->left );
144 if( i_ret != VLC_SUCCESS )
145 goto quit_buildwhere;
146 if( psz_left == NULL )
148 msg_Err( p_ml, "Couldn't build NOT for Update statement" );
149 i_ret = VLC_EGENERIC;
150 goto quit_buildwhere;
152 if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 )
155 goto quit_buildwhere;
159 msg_Err( p_ml, "Couldn't build special for Update statement" );
162 switch( tree->criteria )
165 assert( tree->comp == ML_COMP_EQUAL );
166 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.id = %d",
168 if( *ppsz_where == NULL )
169 goto quit_buildwhere;
172 assert( tree->comp == ML_COMP_EQUAL );
173 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.uri = %q",
175 if( *ppsz_where == NULL )
176 goto quit_buildwhere;
179 msg_Err( p_ml, "Trying to update with unsupported condition" );
188 * @brief Generic UPDATE query builder
190 * @param p_ml This media_library_t object
191 * @param ppsz_query *ppsz_query will contain query for update
192 * @param ppsz_id_query will contain query to get the ids of updated files
193 * @param selected_type the type of the element we're selecting
194 * @param where parse tree of where condition
195 * @param changes list of changes to make in the entries
196 * @return VLC_SUCCESS or VLC_EGENERIC
198 int BuildUpdate( media_library_t *p_ml,
199 char **ppsz_query, char **ppsz_id_query,
200 const char *psz_lvalue,
201 ml_select_e selected_type,
202 ml_ftree_t *where, vlc_array_t *changes )
204 assert( ppsz_query );
205 assert( ppsz_id_query );
210 int i_ret = VLC_ENOMEM;
212 char *psz_table = NULL;
214 char *psz_set[ ML_DIRECTORY + 1 ] = { NULL };
215 char *psz_fullset = NULL;
216 char *psz_extra = NULL; /*<< For an update to extra table */
218 char *psz_where = NULL;
219 char *psz_tmp = NULL;
221 int *pi_padd_ids = NULL;
222 int i_people_add = 0;
225 char *psz_album = NULL;
226 char *psz_cover = NULL;
231 msg_Warn( p_ml, "You're trying to update no rows."
232 "Trying to guess update based on uri" );
235 /* Create the id/uri lists for WHERE part of the query */
236 i_ret = BuildWhere( p_ml, &psz_where, where );
237 if( i_ret != VLC_SUCCESS )
238 goto quit_buildupdate;
241 /** Firstly, choose the right table */
242 switch( selected_type )
245 psz_table = strdup( "album" );
248 psz_table = strdup( "people" );
251 psz_table = strdup( "media" );
254 msg_Err( p_ml, "Not a valid element to Update!" );
255 i_ret = VLC_EGENERIC;
256 goto quit_buildupdate;
263 /** Secondly, build the SET part of the query */
264 for( i_index = 0; i_index < vlc_array_count( changes ); i_index++ )
266 ml_element_t *find = ( ml_element_t * )
267 vlc_array_item_at_index( changes, i_index );
268 i_type = find->criteria;
273 if( selected_type == ML_ALBUM )
275 if( !psz_set[i_type] )
277 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
280 if( !psz_set[i_type] )
282 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
283 __FILE__, __LINE__ );
284 goto quit_buildupdate;
288 else if( selected_type == ML_MEDIA )
291 psz_album = find->value.str;
297 assert( selected_type != ML_ALBUM );
298 if( selected_type == ML_MEDIA )
300 if( i_album_id <= 0 )
302 i_album_id = find->value.i;
303 if( !psz_set[i_type] )
305 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
308 if( !psz_set[i_type] )
310 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
311 __FILE__, __LINE__ );
312 goto quit_buildupdate;
319 if( selected_type == ML_MEDIA )
321 pi_padd_ids = (int*) realloc( pi_padd_ids , ( ++i_people_add * sizeof(int) ) );
322 pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID,
323 find->lvalue.str, ML_PEOPLE, find->lvalue.str,
325 if( pi_padd_ids[ i_people_add - 1 ] <= 0 )
327 AddPeople( p_ml, find->value.str, find->lvalue.str );
328 pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID,
329 find->lvalue.str, ML_PEOPLE, find->lvalue.str,
333 else if( strcmp( psz_lvalue, find->lvalue.str ) )
335 msg_Err( p_ml, "Trying to update a different person type" );
340 if( !psz_set[i_type] ) psz_set[i_type] =
341 sql_Printf( p_ml->p_sys->p_sql, "name = %Q", find->value.str );
345 /* TODO: Implement smarter updates for this case? */
346 assert( selected_type == ML_MEDIA );
347 if( selected_type == ML_MEDIA )
349 bool b_update = true;
350 for( int i = 0; i < i_people_add; i++ )
352 if( pi_padd_ids[ i ] == find->value.i )
360 pi_padd_ids = (int *)realloc( pi_padd_ids, ( ++i_people_add * sizeof(int) ) );
361 pi_padd_ids[ i_people_add - 1 ] = find->value.i;
366 msg_Dbg( p_ml, "Can't update role" );
369 assert( selected_type == ML_MEDIA );
370 SET_STR( "comment = %Q" );
372 assert( selected_type == ML_ALBUM || selected_type == ML_MEDIA );
373 psz_cover = find->value.str;
374 SET_STR( "cover = %Q" );
376 assert( selected_type == ML_MEDIA );
377 SET_INT( "disc = '%d'" );
379 assert( selected_type == ML_MEDIA );
380 SET_INT( "duration = '%d'" );
382 assert( selected_type == ML_MEDIA );
383 SET_STR( "extra = %Q" );
384 case ML_FIRST_PLAYED:
385 assert( selected_type == ML_MEDIA );
386 SET_INT( "first_played =='%d'" );
388 assert( selected_type == ML_MEDIA );
389 SET_STR( "genre = %Q" );
390 /* ID cannot be updated */
391 /* Import time can't be updated */
393 assert( selected_type == ML_MEDIA );
394 SET_INT( "last_played = '%d'" );
395 case ML_ORIGINAL_TITLE:
396 assert( selected_type == ML_MEDIA );
397 SET_STR( "original_title = %Q" );
398 case ML_PLAYED_COUNT:
399 assert( selected_type == ML_MEDIA );
400 SET_INT( "played_count = '%d'" );
402 assert( selected_type == ML_MEDIA );
403 SET_STR( "preview = %Q" );
404 case ML_SKIPPED_COUNT:
405 assert( selected_type == ML_MEDIA );
406 SET_INT( "skipped_count = '%d'" );
408 assert( selected_type == ML_MEDIA );
409 SET_INT( "score = '%d'" );
411 assert( selected_type == ML_MEDIA );
412 SET_STR( "title = %Q" );
413 case ML_TRACK_NUMBER:
414 assert( selected_type == ML_MEDIA );
415 SET_INT( "track = '%d'" );
417 assert( selected_type == ML_MEDIA );
418 if( !psz_set[i_type] ) psz_set[i_type] =
419 sql_Printf( p_ml->p_sys->p_sql, "type = '%d'", find->value.i );
422 assert( selected_type == ML_MEDIA );
423 if( !psz_set[i_type] )
425 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
431 assert( selected_type == ML_MEDIA );
432 SET_INT( "vote = '%d'" );
434 assert( selected_type == ML_MEDIA );
435 SET_INT( "year = '%d'" );
439 msg_Warn( p_ml, "Invalid type for update : %d", i_type );
444 /* TODO: Album artist. Verify albumart */
445 if( i_album_id <= 0 || ( psz_album && *psz_album ) )
447 i_album_id = ml_GetAlbumId( p_ml, psz_album );
448 if( i_album_id < 0 ) //0 is Unknown
450 i_ret = AddAlbum( p_ml, psz_album, psz_cover, 0 );
451 if( i_ret != VLC_SUCCESS )
454 msg_Err( p_ml, "Couldn't AddAlbum at BuildUpdate():(%s, %d)",
455 __FILE__, __LINE__ );
456 goto quit_buildupdate;
458 i_album_id = ml_GetAlbumId( p_ml, psz_album );
460 goto quit_buildupdate;
462 psz_set[ML_ALBUM_ID] = sql_Printf( p_ml->p_sys->p_sql,
463 "album_id = '%d'", i_album_id );
464 if( !psz_set[ML_ALBUM_ID] )
466 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
467 __FILE__, __LINE__ );
468 goto quit_buildupdate;
472 for( unsigned i = 0; i <= ML_DIRECTORY; i++ )
476 if( i == ML_EXTRA || i == ML_LANGUAGE )
479 if( asprintf( &psz_tmp, "%s%s%s", psz_extra ? psz_extra : "",
480 psz_extra ? ", ": "", psz_set[i] ) == -1 )
482 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
483 __FILE__, __LINE__ );
484 goto quit_buildupdate;
487 psz_extra = strdup( psz_tmp );
492 if( asprintf( &psz_tmp, "%s%s%s", psz_fullset ? psz_fullset : "",
493 psz_fullset ? ", ": "", psz_set[i] ) == -1 )
495 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
496 __FILE__, __LINE__ );
497 goto quit_buildupdate;
500 psz_fullset = strdup( psz_tmp );
506 /** Now build the right WHERE condition */
507 assert( psz_where && *psz_where );
509 /** Finally build the full query */
510 /** Pass if we have some people to add - Indirect update */
511 if( !psz_fullset && i_people_add == 0 )
513 i_ret = VLC_EGENERIC;
514 msg_Err( p_ml, "Nothing found to create update at BuildUpdate():(%s, %d)",
515 __FILE__, __LINE__ );
516 goto quit_buildupdate;
520 if( asprintf( ppsz_query, "UPDATE %s SET %s WHERE %s", psz_table,
521 psz_fullset, psz_where ) == -1 )
523 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
524 __FILE__, __LINE__ );
525 goto quit_buildupdate;
529 if( selected_type == ML_MEDIA )
533 if( asprintf( &psz_tmp, "%s; UPDATE extra SET %s WHERE %s",
534 *ppsz_query, psz_extra, psz_where ) == -1 )
536 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
537 __FILE__, __LINE__ );
538 goto quit_buildupdate;
541 *ppsz_query = psz_tmp;
544 char* psz_idstring = NULL;
545 if( i_people_add > 0 )
547 for( int i = 0; i < i_people_add; i++ )
549 if( asprintf( &psz_tmp, "%s%s%d", psz_idstring == NULL? "" : psz_idstring,
550 psz_idstring == NULL ? "" : ",", pi_padd_ids[i] ) == -1 )
553 free( psz_idstring );
554 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
555 __FILE__, __LINE__ );
556 goto quit_buildupdate;
558 free( psz_idstring );
559 psz_idstring = psz_tmp;
562 /* Delete all connections with people whom we will update now! */
563 if( asprintf( &psz_tmp, "%s;DELETE FROM media_to_people WHERE EXISTS "
564 "(SELECT media.id, people.id FROM media JOIN media_to_people "
565 "AS temp ON media.id = temp.media_id "
566 "JOIN people ON temp.people_id = people.id "
567 "WHERE %s AND people.role IN "
568 "(SELECT people.role FROM people WHERE people.id IN (%s)) "
569 "AND people.id NOT IN (%s) "
570 "AND temp.media_id = media_to_people.media_id AND "
571 "temp.people_id = media_to_people.people_id )",
572 *ppsz_query == NULL ? "": *ppsz_query, psz_where,
573 psz_idstring, psz_idstring ) == -1 )
575 free( psz_idstring );
576 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
577 __FILE__, __LINE__ );
578 goto quit_buildupdate;
581 *ppsz_query = psz_tmp;
583 free( psz_idstring );
585 for( int i = 0; i < i_people_add ; i++ )
587 if( pi_padd_ids[i] > 0 )
589 /* OR IGNORE will avoid errors from collisions from old media
590 * Perhaps this hack can be fixed...FIXME */
591 if( asprintf( &psz_tmp, "%s;INSERT OR IGNORE into media_to_people "
592 "(media_id,people_id) SELECT media.id, %d FROM media WHERE %s",
593 *ppsz_query == NULL ? "" : *ppsz_query, pi_padd_ids[i],
596 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
597 __FILE__, __LINE__ );
598 goto quit_buildupdate;
600 FREENULL( *ppsz_query );
601 *ppsz_query = psz_tmp;
607 if( asprintf( ppsz_id_query, "SELECT id AS %s_id FROM %s WHERE %s",
608 psz_table, psz_table, psz_where ) == -1 )
610 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
611 __FILE__, __LINE__ );
612 goto quit_buildupdate;
615 msg_Dbg( p_ml, "updated media where %s", psz_where );
617 goto quit_buildupdate_success;
620 msg_Warn( p_ml, "BuildUpdate() could not generate update sql query" );
621 quit_buildupdate_success:
627 for( int i = 0; i <= ML_DIRECTORY; i++ )
628 free( psz_set[ i ] );
637 * @brief Update a ml_media_t
639 * @param p_ml the media library object
640 * @param p_media media to synchronise in the database
641 * @return VLC_SUCCESS or VLC_EGENERIC
642 * @note: the media id may be 0, in this case, the update is based
643 * on the url (less powerful). This function is threadsafe
645 * This synchronises all non NULL and non zero fields of p_media
646 * Synchronization of album and people is TODO
648 int UpdateMedia( media_library_t *p_ml, ml_media_t *p_media )
650 assert( p_media->i_id || ( p_media->psz_uri && *p_media->psz_uri ) );
651 vlc_array_t *changes = vlc_array_new();
652 ml_element_t *find = NULL;
653 int i_ret = VLC_EGENERIC;
655 ml_LockMedia( p_media );
656 #define APPEND_ICHANGES( cond, crit ) \
658 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) ); \
659 find->criteria = crit; \
660 find->value.i = cond; \
661 vlc_array_append( changes, find ); \
663 #define APPEND_SCHANGES( cond, crit ) \
665 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) ); \
666 find->criteria = crit; \
667 find->value.str = cond; \
668 vlc_array_append( changes, find ); \
671 APPEND_SCHANGES( p_media->psz_title, ML_TITLE );
672 APPEND_ICHANGES( p_media->i_type, ML_TYPE );
673 APPEND_ICHANGES( p_media->i_duration, ML_DURATION );
674 APPEND_SCHANGES( p_media->psz_preview, ML_PREVIEW );
675 APPEND_SCHANGES( p_media->psz_cover, ML_COVER );
676 APPEND_ICHANGES( p_media->i_disc_number, ML_DISC_NUMBER );
677 APPEND_ICHANGES( p_media->i_track_number, ML_TRACK_NUMBER );
678 APPEND_ICHANGES( p_media->i_year, ML_YEAR);
679 APPEND_SCHANGES( p_media->psz_genre, ML_GENRE );
680 APPEND_ICHANGES( p_media->i_album_id, ML_ALBUM_ID );
681 APPEND_SCHANGES( p_media->psz_album, ML_ALBUM );
682 APPEND_ICHANGES( p_media->i_skipped_count, ML_SKIPPED_COUNT );
683 APPEND_ICHANGES( p_media->i_last_skipped, ML_LAST_SKIPPED );
684 APPEND_ICHANGES( p_media->i_played_count, ML_PLAYED_COUNT );
685 APPEND_ICHANGES( p_media->i_last_played, ML_LAST_PLAYED );
686 APPEND_ICHANGES( p_media->i_first_played, ML_FIRST_PLAYED );
687 APPEND_ICHANGES( p_media->i_vote, ML_VOTE );
688 APPEND_ICHANGES( p_media->i_score, ML_SCORE );
689 APPEND_SCHANGES( p_media->psz_comment, ML_COMMENT );
690 APPEND_SCHANGES( p_media->psz_extra, ML_EXTRA );
691 APPEND_SCHANGES( p_media->psz_language, ML_LANGUAGE );
693 if( p_media->psz_uri && p_media->i_id )
695 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
696 find->criteria = ML_URI;
697 find->value.str = p_media->psz_uri;
698 vlc_array_append( changes, find );
700 /*TODO: implement extended meta */
701 /* We're not taking import time! Good */
703 #undef APPEND_ICHANGES
704 #undef APPEND_SCHANGES
705 ml_person_t* person = p_media->p_people;
708 if( person->i_id > 0 )
710 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
711 find->criteria = ML_PEOPLE_ID;
712 find->lvalue.str = person->psz_role;
713 find->value.i = person->i_id;
714 vlc_array_append( changes, find );
716 else if( person->psz_name && *person->psz_name )
718 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
719 find->criteria = ML_PEOPLE;
720 find->lvalue.str = person->psz_role;
721 find->value.str = person->psz_name;
722 vlc_array_append( changes, find );
724 person = person->p_next;
727 ml_ftree_t* p_where = NULL;
728 ml_ftree_t* p_where_elt = ( ml_ftree_t* ) calloc( 1, sizeof( ml_ftree_t ) );
731 p_where_elt->criteria = ML_ID;
732 p_where_elt->value.i = p_media->i_id ;
733 p_where_elt->comp = ML_COMP_EQUAL;
734 p_where = ml_FtreeFastAnd( p_where, p_where_elt );
736 else if( p_media->psz_uri )
738 p_where_elt->criteria = ML_URI;
739 p_where_elt->value.str = p_media->psz_uri;
740 p_where_elt->comp = ML_COMP_EQUAL;
741 p_where = ml_FtreeFastAnd( p_where, p_where_elt );
747 i_ret = Update( p_ml, ML_MEDIA, NULL, p_where, changes );
750 ml_FreeFindTree( p_where );
751 for( int i = 0; i < vlc_array_count( changes ); i++ )
752 /* Note: DO NOT free the strings because
753 * they belong to the ml_media_t object */
754 free( vlc_array_item_at_index( changes, i ) );
755 vlc_array_destroy( changes );
756 ml_UnlockMedia( p_media );
761 * @brief Update an album's cover art
762 * @param p_ml The Media Library
763 * @param i_album_id Album's ID
764 * @param psz_cover New cover art
765 * @return VLC success/error code
767 int SetArtCover( media_library_t *p_ml,
768 int i_album_id, const char *psz_cover )
770 assert( i_album_id != 0 );
771 assert( psz_cover != NULL );
773 char *psz_query = sql_Printf( p_ml->p_sys->p_sql,
774 "UPDATE album SET cover = %Q WHERE id = '%d'",
775 psz_cover, i_album_id );
780 if( QuerySimple( p_ml, "%s", psz_query ) != VLC_SUCCESS )
782 msg_Warn( p_ml, "Could not update the album's cover art "
783 "(Database error)" );