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 *****************************************************************************/
32 #include "sql_media_library.h"
36 * @brief Generic update in Media Library database
38 * @param p_ml the media library object
39 * @param selected_type the type of the element we're selecting
40 * @param where the list of ids or uri to change
41 * @param changes list of changes to make in the entries
42 * @return VLC_SUCCESS or VLC_EGENERIC
43 * @note This function is transactional
45 int Update( media_library_t *p_ml, ml_select_e selected_type,
46 const char* psz_lvalue, ml_ftree_t *where, vlc_array_t *changes )
48 int i_ret = VLC_EGENERIC;
49 char *psz_query = NULL;
50 char *psz_id_query = NULL;
51 char **pp_results = NULL;
52 int i_rows = 0, i_cols = 0;
54 i_ret = BuildUpdate( p_ml, &psz_query, &psz_id_query,
55 psz_lvalue, selected_type, where, changes );
57 if( i_ret != VLC_SUCCESS )
59 msg_Err(p_ml,"Failed to generate update query" );
65 if( QuerySimple( p_ml, "%s", psz_query ) != VLC_SUCCESS )
67 msg_Err( p_ml, "Couldn't run the generated update query successfully" );
71 /* Get the updated IDs to send events! */
72 if( Query( p_ml, &pp_results, &i_rows, &i_cols, psz_id_query )
78 if( i_ret != VLC_SUCCESS )
85 for( int i = 0; i < i_rows; i++ )
87 var_SetInteger( p_ml, "media-meta-change",
88 atoi( pp_results[i*i_cols] ) );
92 FreeSQLResult( p_ml, pp_results );
98 #define SET_STR( a ) \
99 if( !psz_set[i_type] ) \
101 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, a, find->value.str ); \
102 if( !psz_set[i_type] ) \
103 goto quit_buildupdate; \
107 #define SET_INT( a ) \
108 if( !psz_set[i_type] ) \
110 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql, a, find->value.i ); \
111 if( !psz_set[i_type] ) \
112 goto quit_buildupdate; \
116 /* TODO: Build smarter updates by using IN () */
117 static int BuildWhere( media_library_t* p_ml, char **ppsz_where, ml_ftree_t *tree )
119 assert( ppsz_where );
120 char* psz_left = NULL;
121 char* psz_right = NULL;
122 int i_ret = VLC_SUCCESS;
127 i_ret = BuildWhere( p_ml, &psz_left, tree->left );
128 if( i_ret != VLC_SUCCESS )
129 goto quit_buildwhere;
130 i_ret = BuildWhere( p_ml, &psz_right, tree->right );
131 if( i_ret != VLC_SUCCESS )
132 goto quit_buildwhere;
133 if( psz_left == NULL || psz_right == NULL )
135 msg_Err( p_ml, "Couldn't build AND/OR for Update statement" );
136 i_ret = VLC_EGENERIC;
137 goto quit_buildwhere;
139 if( asprintf( ppsz_where, "( %s %s %s )", psz_left,
140 ( tree->op == ML_OP_AND ? "AND" : "OR" ), psz_right ) == -1 )
143 goto quit_buildwhere;
147 i_ret = BuildWhere( p_ml, &psz_left, tree->left );
148 if( i_ret != VLC_SUCCESS )
149 goto quit_buildwhere;
150 if( psz_left == NULL )
152 msg_Err( p_ml, "Couldn't build NOT for Update statement" );
153 i_ret = VLC_EGENERIC;
154 goto quit_buildwhere;
156 if( asprintf( ppsz_where, "( NOT %s )", psz_left ) == -1 )
159 goto quit_buildwhere;
163 msg_Err( p_ml, "Couldn't build special for Update statement" );
166 switch( tree->criteria )
169 assert( tree->comp == ML_COMP_EQUAL );
170 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.id = %d",
172 if( *ppsz_where == NULL )
173 goto quit_buildwhere;
176 assert( tree->comp == ML_COMP_EQUAL );
177 *ppsz_where = sql_Printf( p_ml->p_sys->p_sql, "media.uri = %q",
179 if( *ppsz_where == NULL )
180 goto quit_buildwhere;
183 msg_Err( p_ml, "Trying to update with unsupported condition" );
192 * @brief Generic UPDATE query builder
194 * @param p_ml This media_library_t object
195 * @param ppsz_query *ppsz_query will contain query for update
196 * @param ppsz_id_query will contain query to get the ids of updated files
197 * @param selected_type the type of the element we're selecting
198 * @param where parse tree of where condition
199 * @param changes list of changes to make in the entries
200 * @return VLC_SUCCESS or VLC_EGENERIC
202 int BuildUpdate( media_library_t *p_ml,
203 char **ppsz_query, char **ppsz_id_query,
204 const char *psz_lvalue,
205 ml_select_e selected_type,
206 ml_ftree_t *where, vlc_array_t *changes )
208 assert( ppsz_query );
209 assert( ppsz_id_query );
214 int i_ret = VLC_ENOMEM;
216 char *psz_table = NULL;
218 char *psz_set[ ML_DIRECTORY + 1 ] = { NULL };
219 char *psz_fullset = NULL;
220 char *psz_extra = NULL; /*<< For an update to extra table */
222 char *psz_where = NULL;
223 char *psz_tmp = NULL;
225 int *pi_padd_ids = NULL;
226 int i_people_add = 0;
229 char *psz_album = NULL;
230 char *psz_cover = NULL;
235 msg_Warn( p_ml, "You're trying to update no rows."
236 "Trying to guess update based on uri" );
239 /* Create the id/uri lists for WHERE part of the query */
240 i_ret = BuildWhere( p_ml, &psz_where, where );
241 if( i_ret != VLC_SUCCESS )
242 goto quit_buildupdate;
245 /** Firstly, choose the right table */
246 switch( selected_type )
249 psz_table = strdup( "album" );
252 psz_table = strdup( "people" );
255 psz_table = strdup( "media" );
258 msg_Err( p_ml, "Not a valid element to Update!" );
259 i_ret = VLC_EGENERIC;
260 goto quit_buildupdate;
267 /** Secondly, build the SET part of the query */
268 for( i_index = 0; i_index < vlc_array_count( changes ); i_index++ )
270 ml_element_t *find = ( ml_element_t * )
271 vlc_array_item_at_index( changes, i_index );
272 i_type = find->criteria;
277 if( selected_type == ML_ALBUM )
279 if( !psz_set[i_type] )
281 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
284 if( !psz_set[i_type] )
286 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
287 __FILE__, __LINE__ );
288 goto quit_buildupdate;
292 else if( selected_type == ML_MEDIA )
295 psz_album = find->value.str;
301 assert( selected_type != ML_ALBUM );
302 if( selected_type == ML_MEDIA )
304 if( i_album_id <= 0 )
306 i_album_id = find->value.i;
307 if( !psz_set[i_type] )
309 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
312 if( !psz_set[i_type] )
314 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
315 __FILE__, __LINE__ );
316 goto quit_buildupdate;
323 if( selected_type == ML_MEDIA )
325 pi_padd_ids = (int*) realloc( pi_padd_ids , ( ++i_people_add * sizeof(int) ) );
326 pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID,
327 find->lvalue.str, ML_PEOPLE, find->lvalue.str,
329 if( pi_padd_ids[ i_people_add - 1 ] <= 0 )
331 AddPeople( p_ml, find->value.str, find->lvalue.str );
332 pi_padd_ids[ i_people_add - 1 ] = ml_GetInt( p_ml, ML_PEOPLE_ID,
333 find->lvalue.str, ML_PEOPLE, find->lvalue.str,
337 else if( strcmp( psz_lvalue, find->lvalue.str ) )
339 msg_Err( p_ml, "Trying to update a different person type" );
344 if( !psz_set[i_type] ) psz_set[i_type] =
345 sql_Printf( p_ml->p_sys->p_sql, "name = %Q", find->value.str );
349 /* TODO: Implement smarter updates for this case? */
350 assert( selected_type == ML_MEDIA );
351 if( selected_type == ML_MEDIA )
353 bool b_update = true;
354 for( int i = 0; i < i_people_add; i++ )
356 if( pi_padd_ids[ i ] == find->value.i )
364 pi_padd_ids = (int *)realloc( pi_padd_ids, ( ++i_people_add * sizeof(int) ) );
365 pi_padd_ids[ i_people_add - 1 ] = find->value.i;
370 msg_Dbg( p_ml, "Can't update role" );
373 assert( selected_type == ML_MEDIA );
374 SET_STR( "comment = %Q" );
376 assert( selected_type == ML_ALBUM || selected_type == ML_MEDIA );
377 psz_cover = find->value.str;
378 SET_STR( "cover = %Q" );
380 assert( selected_type == ML_MEDIA );
381 SET_INT( "disc = '%d'" );
383 assert( selected_type == ML_MEDIA );
384 SET_INT( "duration = '%d'" );
386 assert( selected_type == ML_MEDIA );
387 SET_STR( "extra = %Q" );
388 case ML_FIRST_PLAYED:
389 assert( selected_type == ML_MEDIA );
390 SET_INT( "first_played =='%d'" );
392 assert( selected_type == ML_MEDIA );
393 SET_STR( "genre = %Q" );
394 /* ID cannot be updated */
395 /* Import time can't be updated */
397 assert( selected_type == ML_MEDIA );
398 SET_INT( "last_played = '%d'" );
399 case ML_ORIGINAL_TITLE:
400 assert( selected_type == ML_MEDIA );
401 SET_STR( "original_title = %Q" );
402 case ML_PLAYED_COUNT:
403 assert( selected_type == ML_MEDIA );
404 SET_INT( "played_count = '%d'" );
406 assert( selected_type == ML_MEDIA );
407 SET_STR( "preview = %Q" );
408 case ML_SKIPPED_COUNT:
409 assert( selected_type == ML_MEDIA );
410 SET_INT( "skipped_count = '%d'" );
412 assert( selected_type == ML_MEDIA );
413 SET_INT( "score = '%d'" );
415 assert( selected_type == ML_MEDIA );
416 SET_STR( "title = %Q" );
417 case ML_TRACK_NUMBER:
418 assert( selected_type == ML_MEDIA );
419 SET_INT( "track = '%d'" );
421 assert( selected_type == ML_MEDIA );
422 if( !psz_set[i_type] ) psz_set[i_type] =
423 sql_Printf( p_ml->p_sys->p_sql, "type = '%d'", find->value.i );
426 assert( selected_type == ML_MEDIA );
427 if( !psz_set[i_type] )
429 psz_set[i_type] = sql_Printf( p_ml->p_sys->p_sql,
435 assert( selected_type == ML_MEDIA );
436 SET_INT( "vote = '%d'" );
438 assert( selected_type == ML_MEDIA );
439 SET_INT( "year = '%d'" );
443 msg_Warn( p_ml, "Invalid type for update : %d", i_type );
448 /* TODO: Album artist. Verify albumart */
449 if( i_album_id <= 0 && psz_album )
451 i_album_id = ml_GetAlbumId( p_ml, psz_album );
452 if( i_album_id < 0 ) //0 is Unknown
454 i_ret = AddAlbum( p_ml, psz_album, psz_cover, 0 );
455 if( i_ret != VLC_SUCCESS )
458 msg_Err( p_ml, "Couldn't AddAlbum at BuildUpdate():(%s, %d)",
459 __FILE__, __LINE__ );
460 goto quit_buildupdate;
462 i_album_id = ml_GetAlbumId( p_ml, psz_album );
464 goto quit_buildupdate;
466 psz_set[ML_ALBUM_ID] = sql_Printf( p_ml->p_sys->p_sql,
467 "album_id = '%d'", i_album_id );
468 if( !psz_set[ML_ALBUM_ID] )
470 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
471 __FILE__, __LINE__ );
472 goto quit_buildupdate;
476 for( unsigned i = 0; i <= ML_DIRECTORY; i++ )
480 if( i == ML_EXTRA || i == ML_LANGUAGE )
483 if( asprintf( &psz_tmp, "%s%s%s", psz_extra ? psz_extra : "",
484 psz_extra ? ", ": "", psz_set[i] ) == -1 )
486 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
487 __FILE__, __LINE__ );
488 goto quit_buildupdate;
491 psz_extra = strdup( psz_tmp );
496 if( asprintf( &psz_tmp, "%s%s%s", psz_fullset ? psz_fullset : "",
497 psz_fullset ? ", ": "", psz_set[i] ) == -1 )
499 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
500 __FILE__, __LINE__ );
501 goto quit_buildupdate;
504 psz_fullset = strdup( psz_tmp );
510 /** Now build the right WHERE condition */
511 assert( psz_where && *psz_where );
513 /** Finally build the full query */
514 /** Pass if we have some people to add - Indirect update */
515 if( !psz_fullset && i_people_add == 0 )
517 i_ret = VLC_EGENERIC;
518 msg_Err( p_ml, "Nothing found to create update at BuildUpdate():(%s, %d)",
519 __FILE__, __LINE__ );
520 goto quit_buildupdate;
524 if( asprintf( ppsz_query, "UPDATE %s SET %s WHERE %s", psz_table,
525 psz_fullset, psz_where ) == -1 )
527 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
528 __FILE__, __LINE__ );
529 goto quit_buildupdate;
533 if( selected_type == ML_MEDIA )
537 if( asprintf( &psz_tmp, "%s; UPDATE extra SET %s WHERE %s",
538 *ppsz_query, psz_extra, psz_where ) == -1 )
540 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
541 __FILE__, __LINE__ );
542 goto quit_buildupdate;
545 *ppsz_query = psz_tmp;
548 char* psz_idstring = NULL;
549 if( i_people_add > 0 )
551 for( int i = 0; i < i_people_add; i++ )
553 if( asprintf( &psz_tmp, "%s%s%d", psz_idstring == NULL? "" : psz_idstring,
554 psz_idstring == NULL ? "" : ",", pi_padd_ids[i] ) == -1 )
557 free( psz_idstring );
558 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
559 __FILE__, __LINE__ );
560 goto quit_buildupdate;
562 free( psz_idstring );
563 psz_idstring = psz_tmp;
566 /* Delete all connections with people whom we will update now! */
567 if( asprintf( &psz_tmp, "%s;DELETE FROM media_to_people WHERE EXISTS "
568 "(SELECT media.id, people.id FROM media JOIN media_to_people "
569 "AS temp ON media.id = temp.media_id "
570 "JOIN people ON temp.people_id = people.id "
571 "WHERE %s AND people.role IN "
572 "(SELECT people.role FROM people WHERE people.id IN (%s)) "
573 "AND people.id NOT IN (%s) "
574 "AND temp.media_id = media_to_people.media_id AND "
575 "temp.people_id = media_to_people.people_id )",
576 *ppsz_query == NULL ? "": *ppsz_query, psz_where,
577 psz_idstring, psz_idstring ) == -1 )
579 free( psz_idstring );
580 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
581 __FILE__, __LINE__ );
582 goto quit_buildupdate;
585 *ppsz_query = psz_tmp;
587 free( psz_idstring );
589 for( int i = 0; i < i_people_add ; i++ )
591 if( pi_padd_ids[i] > 0 )
593 /* OR IGNORE will avoid errors from collisions from old media
594 * Perhaps this hack can be fixed...FIXME */
595 if( asprintf( &psz_tmp, "%s;INSERT OR IGNORE into media_to_people "
596 "(media_id,people_id) SELECT media.id, %d FROM media WHERE %s",
597 *ppsz_query == NULL ? "" : *ppsz_query, pi_padd_ids[i],
600 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
601 __FILE__, __LINE__ );
602 goto quit_buildupdate;
604 FREENULL( *ppsz_query );
605 *ppsz_query = psz_tmp;
611 if( asprintf( ppsz_id_query, "SELECT id AS %s_id FROM %s WHERE %s",
612 psz_table, psz_table, psz_where ) == -1 )
614 msg_Err( p_ml, "Couldn't create string at BuildUpdate():(%s, %d)",
615 __FILE__, __LINE__ );
616 goto quit_buildupdate;
619 msg_Dbg( p_ml, "updated media where %s", psz_where );
621 goto quit_buildupdate_success;
624 msg_Warn( p_ml, "BuildUpdate() could not generate update sql query" );
625 quit_buildupdate_success:
631 for( int i = 0; i <= ML_DIRECTORY; i++ )
632 free( psz_set[ i ] );
641 * @brief Update a ml_media_t
643 * @param p_ml the media library object
644 * @param p_media media to synchronise in the database
645 * @return VLC_SUCCESS or VLC_EGENERIC
646 * @note: the media id may be 0, in this case, the update is based
647 * on the url (less powerful). This function is threadsafe
649 * This synchronises all non NULL and non zero fields of p_media
650 * Synchronization of album and people is TODO
652 int UpdateMedia( media_library_t *p_ml, ml_media_t *p_media )
654 assert( p_media->i_id || ( p_media->psz_uri && *p_media->psz_uri ) );
655 vlc_array_t *changes = vlc_array_new();
656 ml_element_t *find = NULL;
657 int i_ret = VLC_EGENERIC;
659 ml_LockMedia( p_media );
660 #define APPEND_ICHANGES( cond, crit ) \
662 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) ); \
663 find->criteria = crit; \
664 find->value.i = cond; \
665 vlc_array_append( changes, find ); \
667 #define APPEND_SCHANGES( cond, crit ) \
669 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) ); \
670 find->criteria = crit; \
671 find->value.str = cond; \
672 vlc_array_append( changes, find ); \
675 APPEND_SCHANGES( p_media->psz_title, ML_TITLE );
676 APPEND_ICHANGES( p_media->i_type, ML_TYPE );
677 APPEND_ICHANGES( p_media->i_duration, ML_DURATION );
678 APPEND_SCHANGES( p_media->psz_preview, ML_PREVIEW );
679 APPEND_SCHANGES( p_media->psz_cover, ML_COVER );
680 APPEND_ICHANGES( p_media->i_disc_number, ML_DISC_NUMBER );
681 APPEND_ICHANGES( p_media->i_track_number, ML_TRACK_NUMBER );
682 APPEND_ICHANGES( p_media->i_year, ML_YEAR);
683 APPEND_SCHANGES( p_media->psz_genre, ML_GENRE );
684 APPEND_ICHANGES( p_media->i_album_id, ML_ALBUM_ID );
685 APPEND_SCHANGES( p_media->psz_album, ML_ALBUM );
686 APPEND_ICHANGES( p_media->i_skipped_count, ML_SKIPPED_COUNT );
687 APPEND_ICHANGES( p_media->i_last_skipped, ML_LAST_SKIPPED );
688 APPEND_ICHANGES( p_media->i_played_count, ML_PLAYED_COUNT );
689 APPEND_ICHANGES( p_media->i_last_played, ML_LAST_PLAYED );
690 APPEND_ICHANGES( p_media->i_first_played, ML_FIRST_PLAYED );
691 APPEND_ICHANGES( p_media->i_vote, ML_VOTE );
692 APPEND_ICHANGES( p_media->i_score, ML_SCORE );
693 APPEND_SCHANGES( p_media->psz_comment, ML_COMMENT );
694 APPEND_SCHANGES( p_media->psz_extra, ML_EXTRA );
695 APPEND_SCHANGES( p_media->psz_language, ML_LANGUAGE );
697 if( p_media->psz_uri && p_media->i_id )
699 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
700 find->criteria = ML_URI;
701 find->value.str = p_media->psz_uri;
702 vlc_array_append( changes, find );
704 /*TODO: implement extended meta */
705 /* We're not taking import time! Good */
707 #undef APPEND_ICHANGES
708 #undef APPEND_SCHANGES
709 ml_person_t* person = p_media->p_people;
712 if( person->i_id > 0 )
714 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
715 find->criteria = ML_PEOPLE_ID;
716 find->lvalue.str = person->psz_role;
717 find->value.i = person->i_id;
718 vlc_array_append( changes, find );
720 else if( person->psz_name && *person->psz_name )
722 find = ( ml_element_t* ) calloc( 1, sizeof( ml_element_t ) );
723 find->criteria = ML_PEOPLE;
724 find->lvalue.str = person->psz_role;
725 find->value.str = person->psz_name;
726 vlc_array_append( changes, find );
728 person = person->p_next;
731 ml_ftree_t* p_where = NULL;
732 ml_ftree_t* p_where_elt = ( ml_ftree_t* ) calloc( 1, sizeof( ml_ftree_t ) );
735 p_where_elt->criteria = ML_ID;
736 p_where_elt->value.i = p_media->i_id ;
737 p_where_elt->comp = ML_COMP_EQUAL;
738 p_where = ml_FtreeFastAnd( p_where, p_where_elt );
740 else if( p_media->psz_uri )
742 p_where_elt->criteria = ML_URI;
743 p_where_elt->value.str = p_media->psz_uri;
744 p_where_elt->comp = ML_COMP_EQUAL;
745 p_where = ml_FtreeFastAnd( p_where, p_where_elt );
751 i_ret = Update( p_ml, ML_MEDIA, NULL, p_where, changes );
754 ml_FreeFindTree( p_where );
755 for( int i = 0; i < vlc_array_count( changes ); i++ )
756 /* Note: DO NOT free the strings because
757 * they belong to the ml_media_t object */
758 free( vlc_array_item_at_index( changes, i ) );
759 vlc_array_destroy( changes );
760 ml_UnlockMedia( p_media );
765 * @brief Update an album's cover art
766 * @param p_ml The Media Library
767 * @param i_album_id Album's ID
768 * @param psz_cover New cover art
769 * @return VLC success/error code
771 int SetArtCover( media_library_t *p_ml,
772 int i_album_id, const char *psz_cover )
774 assert( i_album_id != 0 );
775 assert( psz_cover != NULL );
777 char *psz_query = sql_Printf( p_ml->p_sys->p_sql,
778 "UPDATE album SET cover = %Q WHERE id = '%d'",
779 psz_cover, i_album_id );
784 if( QuerySimple( p_ml, "%s", psz_query ) != VLC_SUCCESS )
786 msg_Warn( p_ml, "Could not update the album's cover art "
787 "(Database error)" );