1 /*****************************************************************************
2 * sql_media_library.c: SQL-based media library
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"
34 static const char* ppsz_AudioExtensions[] = { EXTENSIONS_AUDIO_CSV, NULL };
35 static const char* ppsz_VideoExtensions[] = { EXTENSIONS_VIDEO_CSV, NULL };
37 #define MEDIA_LIBRARY_PATH_TEXT N_( "Filename of the SQLite database" )
38 #define MEDIA_LIBRARY_PATH_LONGTEXT N_( "Path to the file containing " \
39 "the SQLite database" )
41 #define IGNORE_TEXT N_( "Ignored extensions in the media library" )
42 #define IGNORE_LONGTEXT N_( "Files with these extensions will not be added to"\
43 " the media library when scanning directories." )
45 #define RECURSIVE_TEXT N_( "Subdirectory recursive scanning" )
46 #define RECURSIVE_LONGTEXT N_( "When scanning a directory, scan also all its"\
51 /*****************************************************************************
53 *****************************************************************************/
55 /* Module entry point and exit point */
56 static int load( vlc_object_t* );
57 static void unload( vlc_object_t* );
59 static int CreateInputItemFromMedia( input_item_t **pp_item,
60 ml_media_t *p_media );
66 const char* column_name;
69 static int compare_ml_elts( const void *a, const void *b )
71 return strcmp( ( (struct ml_table_elt* )a )->column_name,
72 ( ( struct ml_table_elt* )b )->column_name );
75 static const struct ml_table_elt ml_table_map[]=
77 { ML_ALBUM_COVER, "album_cover" },
78 { ML_ALBUM_ID, "album_id" },
79 { ML_ALBUM, "album_title" },
80 { ML_COMMENT, "comment" },
81 { ML_COVER, "cover" },
82 { ML_DIRECTORY, "directory_id" },
83 { ML_DISC_NUMBER, "disc" },
84 { ML_DURATION, "duration" },
85 { ML_EXTRA, "extra" },
86 { ML_FILESIZE, "filesize" },
87 { ML_FIRST_PLAYED, "first_played" },
88 { ML_GENRE, "genre" },
90 { ML_IMPORT_TIME, "import_time" },
91 { ML_LANGUAGE, "language" },
92 { ML_LAST_PLAYED, "last_played" },
93 { ML_LAST_SKIPPED, "last_skipped" },
94 { ML_ORIGINAL_TITLE, "original_title" },
95 { ML_PEOPLE_ID, "people_id" },
96 { ML_PEOPLE, "people_name" },
97 { ML_PEOPLE_ROLE, "people_role" },
98 { ML_PLAYED_COUNT, "played_count" },
99 { ML_PREVIEW, "preview" },
100 { ML_SCORE, "score" },
101 { ML_SKIPPED_COUNT, "skipped_count" },
102 { ML_TITLE, "title" },
103 { ML_TRACK_NUMBER, "track" },
110 /*****************************************************************************
112 *****************************************************************************/
114 set_shortname( "Media Library" )
115 set_description( _( "Media Library based on a SQL based database" ) )
116 set_capability( "media-library", 1 )
117 set_callbacks( load, unload )
118 set_category( CAT_ADVANCED )
119 set_subcategory( SUBCAT_ADVANCED_MISC )
120 add_string( "ml-filename", "vlc-media-library.db",
121 MEDIA_LIBRARY_PATH_TEXT, MEDIA_LIBRARY_PATH_LONGTEXT, false )
122 add_string( "ml-username", "", N_( "Username for the database" ),
123 N_( "Username for the database" ), false )
124 add_string( "ml-password", "", N_( "Password for the database" ),
125 N_( "Password for the database" ), false )
126 add_integer( "ml-port", 0,
127 N_( "Port for the database" ), N_("Port for the database"), false )
128 add_bool( "ml-recursive-scan", true, RECURSIVE_TEXT,
129 RECURSIVE_LONGTEXT, false )
130 add_bool( "ml-auto-add", true, N_("Auto add new medias"),
131 N_( "Automatically add new medias to ML" ), false )
132 add_bool( "ml-synchronous", true, N_("Use transactions"),
133 N_( "Disabling transactions saves I/O but can corrupt database in case of crash" ), false )
139 * @param obj Parent object
141 static int load( vlc_object_t *obj )
143 msg_Dbg( obj, "loading media library module" );
145 media_library_t *p_ml = ( media_library_t * ) obj;
146 p_ml->p_sys = ( media_library_sys_t* )
147 calloc( 1, sizeof( media_library_sys_t ) );
151 p_ml->functions.pf_Find = FindVa;
152 p_ml->functions.pf_FindAdv = FindAdv;
153 p_ml->functions.pf_Control = Control;
154 p_ml->functions.pf_InputItemFromMedia = GetInputItemFromMedia;
155 p_ml->functions.pf_Update = Update;
156 p_ml->functions.pf_Delete = Delete;
157 p_ml->functions.pf_GetMedia = GetMedia;
159 vlc_mutex_init( &p_ml->p_sys->lock );
161 /* Initialise Sql module */
162 if ( InitDatabase( p_ml ) != VLC_SUCCESS )
164 vlc_mutex_destroy( &p_ml->p_sys->lock );
169 /* Initialise the media pool */
170 ARRAY_INIT( p_ml->p_sys->mediapool );
171 vlc_mutex_init( &p_ml->p_sys->pool_mutex );
173 /* Create variables system */
174 var_Create( p_ml, "media-added", VLC_VAR_INTEGER );
175 var_Create( p_ml, "media-deleted", VLC_VAR_INTEGER );
176 var_Create( p_ml, "media-meta-change", VLC_VAR_INTEGER );
178 /* Launching the directory monitoring thread */
179 monitoring_thread_t *p_mon =
180 vlc_object_create( p_ml, sizeof( monitoring_thread_t ) );
183 vlc_mutex_destroy( &p_ml->p_sys->lock );
184 sql_Destroy( p_ml->p_sys->p_sql );
188 p_ml->p_sys->p_mon = p_mon;
192 if( vlc_clone( &p_mon->thread, RunMonitoringThread, p_mon,
193 VLC_THREAD_PRIORITY_LOW ) )
195 msg_Err( p_ml, "cannot spawn the media library monitoring thread" );
196 vlc_mutex_destroy( &p_ml->p_sys->lock );
197 sql_Destroy( p_ml->p_sys->p_sql );
199 vlc_object_release( p_mon );
202 /* Starting the watching system (starts a thread) */
205 msg_Dbg( p_ml, "Media library module loaded successfully" );
212 * @brief Unload module
214 * @param obj the media library object
217 static void unload( vlc_object_t *obj )
219 media_library_t *p_ml = ( media_library_t* ) obj;
221 /* Stopping the watching system */
224 /* Stop the monitoring thread */
225 vlc_cancel( p_ml->p_sys->p_mon->thread );
226 vlc_join( p_ml->p_sys->p_mon->thread, NULL );
227 vlc_object_release( p_ml->p_sys->p_mon );
229 /* Destroy the variable */
230 var_Destroy( p_ml, "media-meta-change" );
231 var_Destroy( p_ml, "media-deleted" );
232 var_Destroy( p_ml, "media-added" );
234 /* Empty the media pool */
236 FOREACH_ARRAY( item, p_ml->p_sys->mediapool )
237 ml_gc_decref( item );
239 vlc_mutex_destroy( &p_ml->p_sys->pool_mutex );
241 sql_Destroy( p_ml->p_sys->p_sql );
243 vlc_mutex_destroy( &p_ml->p_sys->lock );
249 * @brief Get results of an SQL-Query on the database (please : free the result)
251 * @param p_ml the media library object
252 * @param ppp_res char *** in which to store the table of results (allocated)
253 * @param pi_rows resulting row number in table
254 * @param pi_cols resulting column number in table
255 * @param psz_fmt query command with printf-like format enabled
256 * @param va_args format the command
257 * @return VLC_SUCCESS or a VLC error code
259 int Query( media_library_t *p_ml,
260 char ***ppp_res, int *pi_rows, int *pi_cols,
261 const char *psz_fmt, ... )
264 va_start( argp, psz_fmt );
266 int i_ret = QueryVa( p_ml, ppp_res, pi_rows, pi_cols, psz_fmt, argp );
273 * @brief Get results of an SQL-Query on the database (please : free the result)
275 * @param p_ml the media library object
276 * @param ppp_res char *** in which to store the table of results (allocated)
277 * @param pi_rows resulting row number in table
278 * @param pi_cols resulting column number in table
279 * @param psz_fmt query command with printf-like format enabled
280 * @param va_args format the command
281 * @return VLC_SUCCESS or a VLC error code
283 int QueryVa( media_library_t *p_ml, char ***ppp_res,
284 int *pi_rows, int *pi_cols, const char *psz_fmt,
288 if( !ppp_res || !psz_fmt ) return VLC_EGENERIC;
290 char *psz_query = sql_VPrintf( p_ml->p_sys->p_sql, psz_fmt, argp );
294 int i_ret = sql_Query( p_ml->p_sys->p_sql, psz_query,
295 ppp_res, pi_rows, pi_cols );
302 * @brief Do a SQL-query without any data coming back
304 * @param p_ml the media library object
305 * @param psz_fmt query command with printf-like format enabled
306 * @param va_args format the command
307 * @return VLC_SUCCESS or a VLC error code
309 int QuerySimple( media_library_t *p_ml,
310 const char *psz_fmt, ... )
313 va_start( argp, psz_fmt );
315 int i_ret = QuerySimpleVa( p_ml, psz_fmt, argp );
322 * @brief Do a SQL-query without any data coming back
324 * @param p_ml the media library object
325 * @param psz_fmt query command with printf-like format enabled
326 * @param argp format the command
327 * @return VLC_SUCCESS or a VLC error code
329 int QuerySimpleVa( media_library_t *p_ml,
330 const char *psz_fmt, va_list argp )
334 int i_ret = VLC_SUCCESS;
336 char **pp_results = NULL;
338 i_ret = QueryVa( p_ml, &pp_results, &i_rows, &i_cols, psz_fmt, argp );
340 FreeSQLResult( p_ml, pp_results );
347 * @brief Transforms a string to a ml_result_t, with given type and id (as psz)
349 * @param res the result of the function
350 * @param psz string to transform into a result
351 * @param psz_id id as a string
352 * @param result_type type of the result
353 * @return ID or a VLC error code
355 int StringToResult( ml_result_t *p_result, const char *psz,
356 const char *psz_id, ml_result_type_e result_type )
358 memset( &p_result->value, 0, sizeof( p_result->value ) );
360 p_result->id = psz_id ? atoi( psz_id ) : 0;
361 p_result->type = result_type;
363 switch( result_type )
366 p_result->value.i = psz ? atoi( psz ) : 0;
370 p_result->value.time = psz ? ( mtime_t ) atoi( psz )
375 p_result->value.psz = psz ? strdup( psz ) : NULL;
380 /* This is an error */
389 * @brief fills an ml_result_array_t with result of an SQL query
391 * @param p_ml the media library object
392 * @param p_media ml_result_array_t object to fill
393 * @param pp_results result of sql query
394 * @param i_rows row number
395 * @param i_cols column number
396 * @param result_type type of the result
397 * @return VLC_SUCCESS or a VLC error code
399 int SQLToResultArray( media_library_t *p_ml, vlc_array_t *p_result_array,
400 char **pp_results, int i_rows, int i_cols,
401 ml_result_type_e result_type )
404 if( !p_result_array )
406 if( i_cols == 0 ) /* No result */
410 msg_Err( p_ml, "negative number of columns in result ?" );
416 for( int i = 1; i <= i_rows; i++ )
418 ml_result_t *res = ( ml_result_t* )
419 calloc( 1, sizeof( ml_result_t ) );
422 StringToResult( res, pp_results[ i ], NULL, result_type );
423 vlc_array_append( p_result_array, res );
426 /* FIXME?: Assuming all double column results are id - result pairs */
427 else if( ( i_cols == 2 ) )
429 for( int i = 1; i <= i_rows; i++ )
431 ml_result_t *res = ( ml_result_t* )
432 calloc( 1, sizeof( ml_result_t ) );
435 StringToResult( res, pp_results[ i * 2 + 1], pp_results[ i * 2 ],
437 vlc_array_append( p_result_array, res );
440 else if( result_type == ML_TYPE_MEDIA )
442 return SQLToMediaArray( p_ml, p_result_array,
443 pp_results, i_rows, i_cols );
447 msg_Err( p_ml, "unable to convert SQL result to a ml_result_t array" );
455 * @brief fills a vlc_array_t with results of an SQL query
456 * medias in ml_result_t
458 * @param p_ml the media library object
459 * @param p_array array to fill with ml_media_t elements (might be initialized)
460 * @param pp_results result of sql query
461 * @param i_rows row number
462 * @param i_cols column number
463 * @return VLC_SUCCESS or a VLC error code
464 * Warning: this returns VLC_EGENERIC if i_rows == 0 (empty result)
466 int SQLToMediaArray( media_library_t *p_ml, vlc_array_t *p_result_array,
467 char **pp_results, int i_rows, int i_cols )
469 int i_ret = VLC_SUCCESS;
472 #define res( i, j ) ( pp_results[ i * i_cols + j ] )
473 #define atoinull( a ) ( (a) ? atoi( a ) : 0 )
474 #define strdupnull( a ) ( (a) ? strdup( a ) : NULL )
479 if( !p_result_array || !pp_results || i_rows < 0 || i_cols <= 0 )
481 msg_Warn( p_ml, "bad arguments (%s:%d)", __FILE__, __LINE__ );
485 vlc_array_t* p_intermediate_array = vlc_array_new();
487 /* Analyze first row */
488 int *indexes = ( int* ) calloc( i_cols + 1, sizeof( int ) );
491 vlc_array_destroy( p_intermediate_array );
495 const int count = sizeof( ml_table_map )/ sizeof( struct ml_table_elt );
496 for( int col = 0; col < i_cols; col++ )
498 struct ml_table_elt key, *result = NULL;
499 key.column_name = res( 0, col );
500 result = bsearch( &key, ml_table_map, count,
501 sizeof( struct ml_table_elt ), compare_ml_elts );
504 msg_Warn( p_ml, "unknown column: %s", res( 0, col ) );
506 indexes[col] = result->column_id;
509 /* Read rows 1 to i_rows */
510 ml_media_t *p_media = NULL;
511 ml_result_t *p_result = NULL;
513 for( int row = 1; ( row <= i_rows ) && ( i_ret == VLC_SUCCESS ); row++ )
515 p_media = media_New( p_ml, 0, ML_MEDIA, false );
520 goto quit_sqlmediaarray;
522 p_result = ( ml_result_t * ) calloc( 1, sizeof( ml_result_t ) );
525 ml_gc_decref( p_media );
528 goto quit_sqlmediaarray;
531 char* psz_append_pname = NULL;
532 char* psz_append_prole = NULL;
533 int i_append_pid = 0;
535 #define SWITCH_INT( key, value ) case key: \
536 p_media-> value = atoinull( res( row, col ) );
537 #define SWITCH_PSZ( key, value ) case key: \
538 p_media-> value = strdupnull( res( row, col ) );
540 ml_LockMedia( p_media );
541 for( int col = 0; ( col < i_cols ) && ( i_ret == VLC_SUCCESS ); col++ )
543 switch( indexes[ col ] )
545 SWITCH_INT( ML_ALBUM_ID, i_album_id );
546 SWITCH_PSZ( ML_ALBUM, psz_album );
547 SWITCH_PSZ( ML_COMMENT, psz_comment );
548 SWITCH_INT( ML_DISC_NUMBER, i_disc_number );
549 SWITCH_INT( ML_DURATION, i_duration );
550 SWITCH_PSZ( ML_EXTRA, psz_extra );
551 SWITCH_INT( ML_FILESIZE, i_filesize );
552 SWITCH_INT( ML_FIRST_PLAYED, i_first_played );
553 SWITCH_PSZ( ML_GENRE, psz_genre);
554 SWITCH_INT( ML_IMPORT_TIME, i_import_time );
555 SWITCH_PSZ( ML_LANGUAGE, psz_language );
556 SWITCH_INT( ML_LAST_PLAYED, i_last_played );
557 SWITCH_INT( ML_LAST_SKIPPED, i_last_skipped );
558 SWITCH_PSZ( ML_ORIGINAL_TITLE, psz_orig_title );
559 SWITCH_INT( ML_PLAYED_COUNT, i_played_count );
560 SWITCH_PSZ( ML_PREVIEW, psz_preview );
561 SWITCH_INT( ML_SCORE, i_score );
562 SWITCH_INT( ML_SKIPPED_COUNT, i_skipped_count );
563 SWITCH_PSZ( ML_TITLE, psz_title );
564 SWITCH_INT( ML_TRACK_NUMBER, i_track_number );
565 SWITCH_INT( ML_TYPE, i_type );
566 SWITCH_INT( ML_VOTE, i_vote);
567 SWITCH_INT( ML_YEAR, i_year );
570 // Discard attachment://
571 if( !p_media->psz_cover || !*p_media->psz_cover
572 || !strncmp( p_media->psz_cover, "attachment://", 13 ) )
574 free( p_media->psz_cover );
575 p_media->psz_cover = strdupnull( res( row, col ) );
579 psz_append_pname = strdupnull( res( row, col ) );
582 i_append_pid = atoinull( res( row, col ) );
585 psz_append_prole = strdupnull( res( row, col ) );
588 /* See ML_ALBUM_COVER */
589 if( !p_media->psz_cover || !*p_media->psz_cover
590 || !strncmp( p_media->psz_cover, "attachment://", 13 ) )
592 free( p_media->psz_cover );
593 p_media->psz_cover = strdupnull( res( row, col ) );
597 p_media->i_id = atoinull( res( row, col ) );
598 if( p_media->i_id <= 0 )
599 msg_Warn( p_ml, "entry with id null or inferior to zero" );
602 p_media->psz_uri = strdupnull( res( row, col ) );
603 if( !p_media->psz_uri )
604 msg_Warn( p_ml, "entry without uri" );
607 break; // The column directory_id is'nt part of the media model
609 msg_Warn( p_ml, "unknown element, row %d column %d (of %d) - %s - %s",
610 row, col, i_cols, res( 0 , col ), res( row, col ) );
618 ml_result_t* p_append = NULL;
619 for( i_appendrow = 0; i_appendrow < vlc_array_count( p_intermediate_array ); i_appendrow++ )
621 p_append = ( ml_result_t* )
622 vlc_array_item_at_index( p_intermediate_array, i_appendrow );
623 if( p_append->id == p_media->i_id )
626 if( i_appendrow == vlc_array_count( p_intermediate_array ) )
628 p_result->id = p_media->i_id;
629 p_result->type = ML_TYPE_MEDIA;
630 p_result->value.p_media = p_media;
631 if( psz_append_pname && i_append_pid && psz_append_prole )
632 ml_CreateAppendPersonAdv( &(p_result->value.p_media->p_people),
633 psz_append_prole, psz_append_pname, i_append_pid );
634 vlc_array_append( p_intermediate_array, p_result );
635 ml_UnlockMedia( p_media );
637 else /* This is a repeat row and the people need to be put together */
640 ml_LockMedia( p_append->value.p_media );
641 if( psz_append_pname && i_append_pid && psz_append_prole )
642 ml_CreateAppendPersonAdv( &(p_append->value.p_media->p_people),
643 psz_append_prole, psz_append_pname, i_append_pid );
644 ml_UnlockMedia( p_append->value.p_media );
645 ml_UnlockMedia( p_media );
646 ml_gc_decref( p_media );
648 FREENULL( psz_append_prole );
649 FREENULL( psz_append_pname );
655 /* Now check if these medias are already on the pool, and sync */
656 for( int i = 0; i < vlc_array_count( p_intermediate_array ); i++ )
659 ( ml_result_t* )vlc_array_item_at_index( p_intermediate_array, i );
660 p_media = p_result->value.p_media;
661 ml_media_t* p_poolmedia = pool_GetMedia( p_ml, p_result->id );
662 /* TODO: Pool_syncMedia might be cleaner? */
664 p_result = ( ml_result_t* ) calloc( 1, sizeof( ml_result_t * ) );
666 goto quit_sqlmediaarray;
669 /* TODO: This might cause some weird stuff to occur w/ GC? */
670 ml_CopyMedia( p_poolmedia, p_media );
671 p_result->id = p_poolmedia->i_id;
672 p_result->type = ML_TYPE_MEDIA;
673 p_result->value.p_media = p_poolmedia;
674 vlc_array_append( p_result_array, p_result );
678 i_ret = pool_InsertMedia( p_ml, p_media, false );
679 if( i_ret == VLC_SUCCESS )
681 ml_gc_incref( p_media );
682 p_result->id = p_media->i_id;
683 p_result->type = ML_TYPE_MEDIA;
684 p_result->value.p_media = p_media;
685 vlc_array_append( p_result_array, p_result );
694 for( int k = 0; k < vlc_array_count( p_intermediate_array ); k++ )
696 ml_result_t* temp = ((ml_result_t*)vlc_array_item_at_index( p_intermediate_array, k ));
697 ml_FreeResult( temp );
699 vlc_array_destroy( p_intermediate_array );
705 * @brief Returns (unique) ID of media with specified URI
707 * @param p_ml the media library object
708 * @param psz_uri URI to look for
709 * @return i_id: (first) ID found, VLC_EGENERIC in case of error
710 * NOTE: Normally, there should not be more than one ID with one URI
712 int GetMediaIdOfURI( media_library_t *p_ml, const char *psz_uri )
714 int i_ret = VLC_EGENERIC;
715 vlc_array_t *p_array = vlc_array_new();
716 i_ret = Find( p_ml, p_array, ML_ID, ML_URI, psz_uri, ML_LIMIT, 1, ML_END );
717 if( ( i_ret == VLC_SUCCESS )
718 && ( vlc_array_count( p_array ) > 0 )
719 && vlc_array_item_at_index( p_array, 0 ) )
721 i_ret = ( (ml_result_t*)vlc_array_item_at_index( p_array, 0 ) )
726 i_ret = VLC_EGENERIC;
728 vlc_array_destroy( p_array );
734 * @brief Control function for media library
736 * @param p_ml Media library handle
737 * @param i_query query type
738 * @param args query arguments
739 * @return VLC_SUCCESS if ok
741 int Control( media_library_t *p_ml, int i_query, va_list args )
745 case ML_ADD_INPUT_ITEM:
747 input_item_t *p_item = (input_item_t *)va_arg( args, input_item_t * );
748 return AddInputItem( p_ml, p_item );
751 case ML_ADD_PLAYLIST_ITEM:
753 playlist_item_t *p_item = (playlist_item_t *)va_arg( args, playlist_item_t * );
754 return AddPlaylistItem( p_ml, p_item );
757 case ML_ADD_MONITORED:
759 char *psz_dir = (char *)va_arg( args, char * );
760 return AddDirToMonitor( p_ml, psz_dir );
763 case ML_GET_MONITORED:
765 vlc_array_t *p_array = (vlc_array_t *)va_arg( args, vlc_array_t * );
766 return ListMonitoredDirs( p_ml, p_array );
769 case ML_DEL_MONITORED:
771 char *psz_dir = (char *)va_arg( args, char * );
772 return RemoveDirToMonitor( p_ml, psz_dir );
782 * @brief Create a new (empty) database. The database might be initialized
784 * @param p_ml This ML
785 * @return VLC_SUCCESS or VLC_EGENERIC
786 * @note This function is transactional
788 int CreateEmptyDatabase( media_library_t *p_ml )
791 int i_ret = VLC_SUCCESS;
792 msg_Dbg( p_ml, "creating a new (empty) database" );
797 i_ret= QuerySimple( p_ml,
798 "CREATE TABLE album ( "
799 "id INTEGER PRIMARY KEY,"
800 "album_artist_id INTEGER,"
801 "title VARCHAR(1024),"
802 "cover VARCHAR(1024) )" );
803 if( i_ret != VLC_SUCCESS )
804 goto quit_createemptydatabase;
806 i_ret = QuerySimple( p_ml, "CREATE INDEX album_title_index ON album (title);" );
807 if( i_ret != VLC_SUCCESS )
808 goto quit_createemptydatabase;
810 /* Add "unknown" entry to albums */
811 i_ret = QuerySimple( p_ml,
812 "INSERT INTO album ( id, title, cover, album_artist_id ) "
813 "VALUES ( 0, 'Unknown', '', 0 )" );
815 if( i_ret != VLC_SUCCESS )
816 goto quit_createemptydatabase;
818 /* Main media table */
819 i_ret= QuerySimple( p_ml,
820 "CREATE TABLE media ( "
821 "id INTEGER PRIMARY KEY,"
822 "timestamp INTEGER," /* File timestamp */
825 "title VARCHAR(1024),"
826 "original_title VARCHAR(1024),"
828 "cover VARCHAR(1024),"
829 "preview VARCHAR(1024)," /* Video preview */
830 "track INTEGER," /* Track number */
831 "disc INTEGER," /* Disc number */
833 "genre VARCHAR(1024),"
834 "vote INTEGER," /* Rating/Stars */
835 "score INTEGER," /* ML score/rating */
836 "comment VARCHAR(1024)," /* Comment */
838 /* Dates and times */
839 "duration INTEGER," /* Length of media */
840 "played_count INTEGER,"
844 "skipped_count INTEGER,"
846 "directory_id INTEGER,"
847 "CONSTRAINT associated_album FOREIGN KEY(album_id) "
848 "REFERENCES album(id) ON DELETE SET DEFAULT ON UPDATE RESTRICT)" );
849 if( i_ret != VLC_SUCCESS )
850 goto quit_createemptydatabase;
852 i_ret = QuerySimple( p_ml, "CREATE INDEX media_ui_index ON media (uri);" );
853 if( i_ret != VLC_SUCCESS )
854 goto quit_createemptydatabase;
857 i_ret = QuerySimple( p_ml,
858 "CREATE TABLE people ( "
859 "id INTEGER PRIMARY KEY,"
860 "name VARCHAR(1024) ,"
861 "role VARCHAR(1024) )" );
862 if( i_ret != VLC_SUCCESS )
863 goto quit_createemptydatabase;
865 /* Media to people */
866 i_ret = QuerySimple( p_ml,
867 "CREATE TABLE media_to_people ( "
869 "people_id INTEGER, "
870 "PRIMARY KEY( media_id, people_id ), "
871 "CONSTRAINT associated_people FOREIGN KEY(people_id) "
872 "REFERENCES people(id) ON DELETE SET DEFAULT ON UPDATE RESTRICT, "
873 "CONSTRAINT associated_media FOREIGN KEY(media_id) "
874 "REFERENCES media(id) ON DELETE CASCADE ON UPDATE RESTRICT )" );
875 if( i_ret != VLC_SUCCESS )
876 goto quit_createemptydatabase;
878 /* Add "unknown" entry to people */
879 i_ret = QuerySimple( p_ml,
880 "INSERT INTO people ( id, name, role ) "
881 "VALUES ( 0, 'Unknown', NULL )" );
882 if( i_ret != VLC_SUCCESS )
883 goto quit_createemptydatabase;
885 /* recursive is set to 1 if the directory is added to the database
886 by recursion and 0 if not */
887 i_ret = QuerySimple( p_ml,
888 "CREATE TABLE directories ( "
889 "id INTEGER PRIMARY KEY,"
892 "recursive INTEGER )" );
893 if( i_ret != VLC_SUCCESS )
894 goto quit_createemptydatabase;
896 /* Create information table
897 * This table should have one row and the version number is the version
899 * Other information may be stored here at later stages */
900 i_ret = QuerySimple( p_ml,
901 "CREATE TABLE information ( "
902 "version INTEGER PRIMARY KEY )" );
903 if( i_ret != VLC_SUCCESS )
904 goto quit_createemptydatabase;
906 /* Insert current DB version */
907 i_ret = QuerySimple( p_ml,
908 "INSERT INTO information ( version ) "
909 "VALUES ( %d )", ML_DBVERSION );
910 if( i_ret != VLC_SUCCESS )
911 goto quit_createemptydatabase;
913 /* Text data: song lyrics or subtitles */
914 i_ret = QuerySimple( p_ml,
915 "CREATE TABLE extra ( "
916 "id INTEGER PRIMARY KEY,"
918 "language VARCHAR(256),"
920 "samplerate INTEGER,"
922 if( i_ret != VLC_SUCCESS )
923 goto quit_createemptydatabase;
925 /* Emulating foreign keys with triggers */
926 /* Warning: Lots of SQL */
927 if( !strcmp( module_get_name( p_ml->p_sys->p_sql->p_module, false ),
930 i_ret = QuerySimple( p_ml,
931 "\nCREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON \"media\" WHEN\n"
932 " new.\"album_id\" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM \"album\" WHERE new.\"album_id\" == \"id\")\n"
934 " SELECT RAISE(ABORT, 'constraint genfkey1_insert_referencing failed. Cannot insert album_id into media. Album did not exist');\n"
937 "CREATE TRIGGER genfkey1_update_referencing BEFORE\n"
938 " UPDATE OF album_id ON \"media\" WHEN \n"
939 " new.\"album_id\" IS NOT NULL AND \n"
940 " NOT EXISTS (SELECT 1 FROM \"album\" WHERE new.\"album_id\" == \"id\")\n"
942 " SELECT RAISE(ABORT, 'constraint genfkey1_update_referencing failed. Cannot update album_id in media. Album did not exist');\n"
945 "CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON \"album\" WHEN\n"
946 " EXISTS (SELECT 1 FROM \"media\" WHERE old.\"id\" == \"album_id\")\n"
948 " SELECT RAISE(ABORT, 'constraint genfkey1_delete_referenced failed. Cannot delete album, media still exist');\n"
952 "CREATE TRIGGER genfkey1_update_referenced AFTER\n"
953 " UPDATE OF id ON \"album\" WHEN \n"
954 " EXISTS (SELECT 1 FROM \"media\" WHERE old.\"id\" == \"album_id\")\n"
956 " SELECT RAISE(ABORT, 'constraint genfkey1_update_referenced failed. Cannot change album id in album, media still exist');\n"
960 "CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON \"media_to_people\" WHEN \n"
961 " new.\"media_id\" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM \"media\" WHERE new.\"media_id\" == \"id\")\n"
963 " SELECT RAISE(ABORT, 'constraint genfkey2_insert_referencing failed. Cannot insert into media_to_people, that media does not exist');\n"
966 "CREATE TRIGGER genfkey2_update_referencing BEFORE\n"
967 " UPDATE OF media_id ON \"media_to_people\" WHEN \n"
968 " new.\"media_id\" IS NOT NULL AND \n"
969 " NOT EXISTS (SELECT 1 FROM \"media\" WHERE new.\"media_id\" == \"id\")\n"
971 " SELECT RAISE(ABORT, 'constraint genfkey2_update_referencing failed. Cannot update media_to_people, that media does not exist');\n"
974 "CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON \"media\" WHEN\n"
975 " EXISTS (SELECT 1 FROM \"media_to_people\" WHERE old.\"id\" == \"media_id\")\n"
977 " DELETE FROM \"media_to_people\" WHERE \"media_id\" = old.\"id\";\n"
980 "CREATE TRIGGER genfkey2_update_referenced AFTER\n"
981 " UPDATE OF id ON \"media\" WHEN \n"
982 " EXISTS (SELECT 1 FROM \"media_to_people\" WHERE old.\"id\" == \"media_id\")\n"
984 " SELECT RAISE(ABORT, 'constraint genfkey2_update_referenced failed. Cannot update media id, refs still exist in media_to_people');\n"
987 "CREATE TRIGGER genfkey3_insert_referencing BEFORE INSERT ON \"media_to_people\" WHEN \n"
988 " new.\"people_id\" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM \"people\" WHERE new.\"people_id\" == \"id\")\n"
990 " SELECT RAISE(ABORT, 'constraint genfkey3_insert_referencing failed. Cannot insert into media_to_people, people does not exist');\n"
992 "CREATE TRIGGER genfkey3_update_referencing BEFORE\n"
993 " UPDATE OF people_id ON \"media_to_people\" WHEN \n"
994 " new.\"people_id\" IS NOT NULL AND \n"
995 " NOT EXISTS (SELECT 1 FROM \"people\" WHERE new.\"people_id\" == \"id\")\n"
997 " SELECT RAISE(ABORT, 'constraint genfkey3_update_referencing failed. Cannot update media_to_people, people does not exist');\n"
1000 "CREATE TRIGGER genfkey3_delete_referenced BEFORE DELETE ON \"people\" WHEN\n"
1001 " EXISTS (SELECT 1 FROM \"media_to_people\" WHERE old.\"id\" == \"people_id\")\n"
1003 " UPDATE media_to_people SET people_id = 0 WHERE people_id == old.\"id\";\n"
1006 "CREATE TRIGGER genfkey3_update_referenced AFTER\n"
1007 " UPDATE OF id ON \"people\" WHEN \n"
1008 " EXISTS (SELECT 1 FROM \"media_to_people\" WHERE old.\"id\" == \"people_id\")\n"
1010 " SELECT RAISE(ABORT, 'constraint genfkey3_update_referenced failed. Cannot update people_id, people does not exist');\n"
1013 "CREATE TRIGGER keep_people_clean AFTER \n"
1014 " DELETE ON \"media_to_people\"\n"
1015 " WHEN NOT EXISTS( SELECT 1 from \"media_to_people\" WHERE old.\"people_id\" == \"people_id\" )\n"
1017 " DELETE FROM people WHERE people.id = old.\"people_id\" AND people.id != 0;\n"
1020 "CREATE TRIGGER keep_album_clean AFTER\n"
1021 " DELETE ON \"media\"\n"
1022 " WHEN NOT EXISTS( SELECT 1 FROM \"media\" WHERE old.\"album_id\" == \"album_id\" )\n"
1024 " DELETE FROM album WHERE album.id = old.\"album_id\" AND album.id != 0;\n"
1026 if( i_ret != VLC_SUCCESS )
1027 goto quit_createemptydatabase;
1030 quit_createemptydatabase:
1031 if( i_ret == VLC_SUCCESS )
1039 * @brief Journal and synchronous disc and writes
1041 * @param p_ml media library object
1042 * @param b_sync boolean
1043 * @return <= 0 on error.
1045 static int SetSynchronous( media_library_t *p_ml, bool b_sync )
1051 i_return = Query( p_ml, &pp_results, &i_rows, &i_cols,
1052 "PRAGMA synchronous = ON;PRAGMA journal_mode = TRUNCATE" );
1054 i_return = Query( p_ml, &pp_results, &i_rows, &i_cols,
1055 "PRAGMA synchronous = OFF;PRAGMA journal_mode = MEMORY" );
1056 if( i_return != VLC_SUCCESS )
1059 i_return = atoi( pp_results[ 1 ] );
1061 FreeSQLResult( p_ml, pp_results );
1067 * @brief Initiates database (create the database and the tables if needed)
1069 * @param p_ml This ML
1070 * @return VLC_SUCCESS or an error code
1072 int InitDatabase( media_library_t *p_ml )
1075 msg_Dbg( p_ml, "initializing database" );
1077 /* Select database name */
1078 char *psz_dbhost = NULL, *psz_user = NULL, *psz_pass = NULL;
1080 bool b_sync = false;
1081 psz_dbhost = config_GetPsz( p_ml, "ml-filename" );
1082 psz_user = config_GetPsz( p_ml, "ml-username" );
1083 psz_pass = config_GetPsz( p_ml, "ml-password" );
1084 i_port = config_GetInt( p_ml, "ml-port" );
1085 b_sync = config_GetInt( p_ml, "ml-synchronous" );
1087 /* Let's consider that a filename with a DIR_SEP is a full URL */
1088 if( strchr( psz_dbhost, DIR_SEP_CHAR ) == NULL )
1090 char *psz_datadir = config_GetUserDir( VLC_DATA_DIR );
1091 char *psz_tmp = psz_dbhost;
1092 if( asprintf( &psz_dbhost, "%s" DIR_SEP "%s",
1093 psz_datadir, psz_tmp ) == -1 )
1095 free( psz_datadir );
1099 free( psz_datadir );
1103 p_ml->p_sys->p_sql = sql_Create( p_ml, NULL, psz_dbhost, i_port, psz_user,
1105 if( !p_ml->p_sys->p_sql )
1106 return VLC_EGENERIC;
1108 /* Let's check if tables exist */
1109 int i_version = GetDatabaseVersion( p_ml );
1110 if( i_version <= 0 )
1111 CreateEmptyDatabase( p_ml );
1112 else if( i_version != ML_DBVERSION )
1113 return VLC_EGENERIC;
1116 * The below code ensures that correct code is written
1117 * when database versions are changed
1120 #if ML_DBVERSION != 1
1121 #error "ML versioning code needs to be updated. Is this done correctly?"
1124 SetSynchronous( p_ml, b_sync );
1126 msg_Dbg( p_ml, "ML initialized" );
1131 * @brief Gets the current version number from the database
1133 * @param p_ml media library object
1134 * @return version number of the current db. <= 0 on error.
1136 int GetDatabaseVersion( media_library_t *p_ml )
1141 i_return = Query( p_ml, &pp_results, &i_rows, &i_cols,
1142 "SELECT version FROM information ORDER BY version DESC LIMIT 1" );
1143 if( i_return != VLC_SUCCESS )
1146 i_return = atoi( pp_results[ 1 ] );
1148 FreeSQLResult( p_ml, pp_results );
1154 * @brief Object constructor for ml_media_t
1155 * @param p_ml The media library object
1156 * @param id If 0, this item isn't in database. If non zero, it is and
1157 * it will be a singleton
1158 * @param select Type of object
1159 * @param reload Whether to reload from database
1161 ml_media_t* GetMedia( media_library_t* p_ml, int id,
1162 ml_select_e select, bool reload )
1165 assert( select == ML_MEDIA || select == ML_MEDIA_SPARSE );
1166 int i_ret = VLC_SUCCESS;
1167 ml_media_t* p_media = NULL;
1170 p_media = pool_GetMedia( p_ml, id );
1175 ml_LockMedia( p_media );
1176 if( p_media->b_sparse && select == ML_MEDIA )
1178 /* Utilise ML_MEDIA_EXTRA load? TODO */
1179 ml_UnlockMedia( p_media );
1180 ml_gc_incref( p_media );
1185 vlc_array_t *p_array = vlc_array_new();
1186 i_ret = ml_Find( p_ml, p_array, select, ML_ID, id );
1187 assert( vlc_array_count( p_array ) == 1 );
1188 if( ( i_ret == VLC_SUCCESS )
1189 && ( vlc_array_count( p_array ) > 0 )
1190 && vlc_array_item_at_index( p_array, 0 ) )
1192 p_media = ((ml_result_t*)vlc_array_item_at_index( p_array, 0 ))->value.p_media;
1193 ml_gc_incref( p_media );
1194 ml_FreeResult( vlc_array_item_at_index( p_array, 0 ) );
1196 vlc_array_destroy( p_array );
1197 if( select == ML_MEDIA )
1198 p_media->b_sparse = false;
1200 p_media->b_sparse = true;
1205 * @brief Create an input item from media (given its ID)
1207 * @param p_ml This media_library_t object
1208 * @param i_media Media ID
1209 * @return input_item_t* created
1211 * @note This is a public function (pf_InputItemFromMedia)
1212 * The input_item will have a refcount at 2 (1 for the ML, 1 for you)
1214 input_item_t* GetInputItemFromMedia( media_library_t *p_ml, int i_media )
1216 input_item_t *p_item = NULL;
1218 p_item = watch_get_itemOfMediaId( p_ml, i_media );
1221 ml_media_t* p_media = media_New( p_ml, i_media, ML_MEDIA, true );
1222 if( p_media == NULL )
1224 CreateInputItemFromMedia( &p_item, p_media );
1225 watch_add_Item( p_ml, p_item, p_media );
1226 ml_gc_decref( p_media );
1233 * @brief Copy an input_item_t to a ml_media_t
1234 * @param p_media Destination
1235 * @param p_item Source
1236 * @note Media ID will not be set! This function is threadsafe. Leaves
1237 * unsyncable items alone
1239 void CopyInputItemToMedia( ml_media_t *p_media, input_item_t *p_item )
1241 ml_LockMedia( p_media );
1244 input_item_GetCopyright( item )
1245 input_item_GetRating( item ) /* TODO */
1246 input_item_GetGetting( item )
1247 input_item_GetNowPlaying( item )
1248 input_item_GetTrackID( item )
1249 input_item_GetSetting( item )
1251 p_media->psz_title = input_item_GetTitle ( p_item );
1252 p_media->psz_uri = input_item_GetURL ( p_item );
1253 if( !p_media->psz_uri )
1254 p_media->psz_uri = input_item_GetURI( p_item );
1255 p_media->psz_album = input_item_GetAlbum ( p_item );
1256 p_media->psz_cover = input_item_GetArtURL ( p_item );
1257 p_media->psz_genre = input_item_GetGenre ( p_item );
1258 p_media->psz_language = input_item_GetLanguage ( p_item );
1259 p_media->psz_comment = input_item_GetDescription ( p_item );
1260 char *psz_track = input_item_GetTrackNum ( p_item );
1261 p_media->i_track_number = psz_track ? atoi( psz_track ) : 0;
1263 char *psz_date = input_item_GetDate( p_item );
1264 p_media->i_year = psz_date ? atoi( psz_date ) : 0;
1266 p_media->i_duration = p_item->i_duration;
1269 char *psz_tmp = input_item_GetArtist( p_item );
1271 ml_CreateAppendPersonAdv( &p_media->p_people, ML_PERSON_ARTIST,
1274 psz_tmp = input_item_GetPublisher( p_item );
1276 ml_CreateAppendPersonAdv( &p_media->p_people, ML_PERSON_PUBLISHER,
1279 psz_tmp = input_item_GetEncodedBy( p_item );
1281 ml_CreateAppendPersonAdv( &p_media->p_people, ML_PERSON_ENCODER,
1285 /* Determine input type: audio, video, stream */
1286 /* First read input type */
1287 switch( p_item->i_type )
1289 case ITEM_TYPE_FILE:
1290 p_media->i_type |= 0;
1292 case ITEM_TYPE_DISC:
1293 case ITEM_TYPE_CARD:
1294 p_media->i_type |= ML_REMOVABLE;
1296 case ITEM_TYPE_CDDA:
1298 p_media->i_type |= ML_STREAM;
1300 case ITEM_TYPE_PLAYLIST:
1301 case ITEM_TYPE_NODE:
1302 case ITEM_TYPE_DIRECTORY:
1303 p_media->i_type |= ML_NODE;
1305 case ITEM_TYPE_NUMBER:
1306 case ITEM_TYPE_UNKNOWN:
1308 p_media->i_type |= ML_UNKNOWN;
1312 /* Then try to guess if this is a video or not */
1313 /* Check file extension, and guess if this is a video or an audio media
1314 Note: this test is not very good, but it's OK for normal files */
1315 char *psz_ext = strrchr( p_item->psz_uri, '.' );
1316 if( psz_ext && strlen( psz_ext ) < 5 )
1320 for( unsigned i = 0; ppsz_AudioExtensions[i]; i++ )
1322 if( strcasecmp( psz_ext, ppsz_AudioExtensions[i] ) == 0 )
1324 p_media->i_type |= ML_AUDIO;
1331 for( unsigned i = 0; ppsz_VideoExtensions[i]; i++ )
1333 if( strcasecmp( psz_ext, ppsz_VideoExtensions[i] ) == 0 )
1335 p_media->i_type |= ML_VIDEO;
1341 ml_UnlockMedia( p_media );
1345 * @brief Copy a ml_media_t to an input_item_t
1346 * @param p_item Destination
1347 * @param p_media Source
1349 void CopyMediaToInputItem( input_item_t *p_item, ml_media_t *p_media )
1351 ml_LockMedia( p_media );
1352 if( p_media->psz_title && *p_media->psz_title )
1353 input_item_SetTitle( p_item, p_media->psz_title );
1354 if( p_media->psz_uri && *p_media->psz_uri && !strncmp( p_media->psz_uri, "http", 4 ) )
1355 input_item_SetURL( p_item, p_media->psz_uri );
1356 if( p_media->psz_album && *p_media->psz_album )
1357 input_item_SetAlbum( p_item, p_media->psz_album );
1358 if( p_media->psz_cover && *p_media->psz_cover )
1359 input_item_SetArtURL( p_item, p_media->psz_cover );
1360 if( p_media->psz_genre && *p_media->psz_genre )
1361 input_item_SetGenre( p_item, p_media->psz_genre );
1362 if( p_media->psz_language && *p_media->psz_language )
1363 input_item_SetLanguage( p_item, p_media->psz_language );
1364 if( p_media->psz_comment && *p_media->psz_comment )
1365 input_item_SetDescription( p_item, p_media->psz_comment );
1366 if( p_media->i_track_number )
1369 if( asprintf( &psz_track, "%d", p_media->i_track_number ) != -1 )
1370 input_item_SetTrackNum( p_item, psz_track );
1373 if( p_media->i_year )
1376 if( asprintf( &psz_date, "%d", p_media->i_year ) != -1 )
1377 input_item_SetDate( p_item, psz_date );
1380 p_item->i_duration = p_media->i_duration;
1381 ml_person_t *person = p_media->p_people;
1384 if( !strcmp( person->psz_role, ML_PERSON_ARTIST ) )
1385 input_item_SetArtist( p_item, person->psz_name );
1386 else if( !strcmp( person->psz_role, ML_PERSON_PUBLISHER ) )
1387 input_item_SetPublisher( p_item, person->psz_name );
1388 else if( !strcmp( person->psz_role, ML_PERSON_ENCODER ) )
1389 input_item_SetEncodedBy( p_item, person->psz_name );
1390 person = person->p_next;
1392 ml_UnlockMedia( p_media );
1396 * @brief Copy a ml_media_t to an input_item_t
1397 * @param pp_item A pointer to a new input_item (return value)
1398 * @param p_media The media to copy as an input item
1399 * @note This function is threadsafe
1401 static int CreateInputItemFromMedia( input_item_t **pp_item,
1402 ml_media_t *p_media )
1404 *pp_item = input_item_New( p_media->psz_uri, p_media->psz_title );
1405 /* ITEM_TYPE_FILE ); */
1407 return VLC_EGENERIC;
1408 CopyMediaToInputItem( *pp_item, p_media );
1413 * @brief Find the media_id associated to an input item
1415 * @param p_item Input item to look for
1416 * @return Media ID or <= 0 if not found
1418 int GetMediaIdOfInputItem( media_library_t *p_ml, input_item_t *p_item )
1420 int i_media_id = watch_get_mediaIdOfItem( p_ml, p_item );
1421 if( i_media_id <= 0 )
1423 i_media_id = GetMediaIdOfURI( p_ml, p_item->psz_uri );