diff options
| -rw-r--r-- | Makefile | 8 | ||||
| -rw-r--r-- | README | 168 | ||||
| -rw-r--r-- | mg_tools.c | 87 | ||||
| -rwxr-xr-x | mugglei.c | 170 | ||||
| -rwxr-xr-x | scripts/createdb.mysql | 7 |
5 files changed, 358 insertions, 82 deletions
@@ -1,7 +1,7 @@ # # Makefile for a Video Disk Recorder plugin # -# $Id: Makefile,v 1.9 2004/05/28 15:29:18 lvw Exp $ +# $Id: Makefile,v 1.10 2004/08/29 14:39:33 lvw Exp $ # The official name of this plugin. # This name will be used in the '-P...' option of VDR to load the plugin. @@ -44,6 +44,7 @@ INCLUDES += -I$(VDRDIR) -I$(VDRDIR)/include -I$(DVBDIR)/include -I/usr/include/m DEFINES += -DPLUGIN_NAME_I18N='"$(PLUGIN)"' +MIFLAGS += -I/usr/include/taglib -ltag -lmysqlclient ### The object files (add further files here): OBJS = $(PLUGIN).o i18n.o vdr_menu.o mg_database.o mg_content_interface.o gd_content_interface.o mg_tools.o mg_media.o mg_filters.o mg_playlist.o vdr_decoder_mp3.o vdr_stream.o vdr_decoder.o vdr_player.o vdr_setup.o @@ -72,6 +73,9 @@ libvdr-$(PLUGIN).so: $(OBJS) $(CXX) $(CXXFLAGS) -shared $(OBJS) $(LIBS) -o $@ @cp $@ $(LIBDIR)/$@.$(VDRVERSION) +mugglei: mg_tools.o mugglei.o + $(CXX) $(CXXFLAGS) $(MIFLAGS) -o $^ + dist: clean @-rm -rf $(TMPDIR)/$(ARCHIVE) @mkdir $(TMPDIR)/$(ARCHIVE) @@ -82,5 +86,3 @@ dist: clean clean: @-rm -f $(OBJS) $(BINOBJS) $(DEPFILE) *.so *.tgz core* *~ - @-rm -f sh_muggle sh_muggle2 - @@ -1,8 +1,8 @@ This is a "plugin" for the Video Disk Recorder (VDR). -Written by: Andi Kellner, +Written by: Andi Kellner Lars von Wedel <vonwedel@web.de> - Ralf Klüber <r@lf-klueber.de>, + Ralf Klueber <r@lf-klueber.de>, Project's homepage: URL @@ -10,4 +10,166 @@ Latest version available at: URL See the file COPYING for license information. -Description: + +PLEASE! + +This is a difficult plugin. It's nice but difficult. +With difficult I mean, that due to the underlying +database, many more sources of error can occur as +opposed to other plugins. + +Take some time to carefully read these instructions. +Please provide feedback to the authors whenever you +think, these instructions are not appropriate, wrong, +or useless in any other sense. + +1 DESCRIPTION + +The muggle plugin provides a database link for VDR so that selection of media becomes more flexible. +Prerequisites are describedin Section 2, Notes on Compilation are in Section 3. Before using the plugin, +you need to import your media into the database (cf. Section 4). The configuration of VDR and startup +parameters are descibed in Section 5. + +2 PREREQUISITES + +The plugin is written for VDR 1.2.6. In addition, the following pieces of +software are required: + +- mySQL server (tested with 4.0.18) (Debian packages mysql-server, mysql-client) +- mySQL client libraries (Debian package libmysqlclient-dev) +- libmad (for mp3 decoding) (Debian package libmad0-dev) +- libtag (for ID3 tag reading/writing) (Debian package libtag1-dev) + +The server need not be on the same machine as the VDR. Also, music tracks can reside somewhere else, +if they are available through a remote filesystem (NFS, Samba). However, in this case you should +know what you are doing in terms of networking and security issues. + + +3 INSTALLING + +Unpack the sources in PLUGINS/src below your VDR directory (i.e. where all your other plugins are. +For example (paths and version numbers may vary) + + cd /usr/local/src/VDR/PLUGINS/src + tar xvjf muggle-0.1.7.tar.bz2 + +Establish a symlink as you would for other plugins: + + ln -s muggle-0.1.7 muggle + +Within the VDR main directory (e.g. /usr/local/src/VDR) issue a + + make plugins + +This should build all relevant stuff. If you have difficulties, check that required libraries are +in the library directories stated in the muggle Makefile. + + +4 IMPORT + +The import is done in two steps: First, a database is created and initialized with proper data structures (so-called schema). +Then, these data structures are filled from the ID3 tags of your music tracks. + +4.1 Setup Database + +This step can be done on the database server or on some other client machine. +Within the directory scripts there are a few helpful files to support setting +up the database. Change into that directory:# + + cd scripts + +The first step is to essentially create the database: + + mysql -u root -p < createdb.mysql + +You will need to enter your root password that you choose during mySQL installation. +Next, we generate the database tables (schema): + + mysql -u root -p < createtables.mysql + +Further, initial data about known languages, genres, sources and musictypes is fed into the database: +Execute these commands on a single line, the \ for the linebreak ist just for presentation purposes here. + + echo " use GiantDisc; load data local infile 'genres.txt' into table genre;" | \ + mysql -u root -p --local-infile=1 + + echo "use GiantDisc; load data local infile 'languages.txt' into table language;" | \ + mysql -u root --local-infile=1 + + echo "use GiantDisc; load data local infile 'musictypes.txt' into table language;" | \ + mysql -u root --local-infile=1 + + echo "use GiantDisc; load data local infile '$SCRIPTDIR/sources.txt' into table language;" | \ + mysql -u root --local-infile=1 + +You can find the sequence of commands in the file scripts/make-empty-db. Use it at your own luck. + +Please note, that the scripts and commands above are quite basic in terms of security (e.g. no +password set for the vdr user, no proper selection of privileges). You may want to spend some +time reading the mySQL documentation in order to set up a proper configuration. Especially when +VDR and mySQL will run on different machines you'll have to invest some time into mySQL +networking and access rights. + +If you want your database name to be different than 'GiantDisc' you will need to adapt the name +in the files createdb.mysql and createtables.mysql and in the commands above. Now your database +is ready for import. + +4.2 Import Music + +The next step is to feed all music information into the database. There is a small tool called 'mugglei' +in the muggle main directory. It connects to the database, evaluates ID3 tags from a file, and writes +the tags into the database. It runs on just one file, so you need some more effort using the Unix command +'find' to take all files into consideration. + +For this step, it is helpful, that all music files are somehow gathered under a toplevel directory. +It does not matter whether there are further subdirectories which organize files into genres, artists, +album or whatever. If this is not the case, you may want to take some time to do this. Read on before +you start + +You probably do not want to import all files in one go: albums on which tracks of various artists are found +(samplers) require different treatment than files of just one artist. What I did: all samplers are collected +below a special subdirectory "Assorted". Import is then run separately for those tracks. + +For now, let's assume your music tracks are located in /home/music and samplers are in /home/music/Assorted. + +First, import the files in Assorted. This requires the flag -a to mugglei. Further flags -h, -n, -u, and -p +specify database host, name, user and password, respectively. The filename to import is given using the -f +directive. Using 'find' you can import all files for assorted albums with a command like: + + find Assorted -name '*' -type f -exec mugglei -a -f {} \; + +For reasons of simplicity, the arguments -h, -n, -u and -p are not shown. You will need them if the default +values do not apply or modify the source code accordingly (beginning of function main). Also, make sure +that either mugglei is on your path or specify an absolute or relative path in the above command line. + +For "regular" albums, the following command may be helpful: + + find * -path 'Assorted' -prune -o -type f -exec mugglei -f {} \; + +It is important that you perform all these steps from the same location so the filenames are relative to +exactly the same directory (e.g. /home/music in the example case). + +Speed should not be an issue: on my machine, it takes about 10 secs to run the import of 60 assorted +albums with more than 600 tracks. Further 1200 tracks or so require 20 more secs. This depends on machine +configuration, of course. + +5 MUGGLE CONFIGURATION + +Muggle uses a small set of command line parameters in order to control the interaction with the mySQL server. +Let's look at an example: + + -P'muggle -h localhost -u vdr -n GiantDisc -t/home/music' + +The -h parameter specifies the database host, -u specifies the user, -n is the database name. The scripts mentioned +above do not make use of passwords, but restrict database acccess on a server basis. + +The -t argument specifies the top level directory of the music files. On a local installation, this is the +directory in which you executed the import steps (Chapter 4.2). + +6 USING MUGGLE + +Quick version: select Muggle on the OSD, browse titles (using up/down and Ok), add them using the red button. +Then turn to the playlist view using yellow and start play using again the red function key. + + + @@ -1,15 +1,11 @@ -/*******************************************************************/ -/*! \file muggle_tools.cpp - * \brief A few util functions for standalone and plugin messaging - * for the vdr muggle plugindatabase - ******************************************************************** - * \version $Revision: 1.3 $ - * \date $Date: 2004/07/25 21:33:35 $ +/*! \file mg_tools.c + * \brief A few util functions for standalone and plugin messaging for the vdr muggle plugindatabase + * + * \version $Revision: 1.4 $ + * \date $Date: 2004/08/29 14:39:33 $ * \author Ralf Klueber, Lars von Wedel, Andreas Kellner * \author file owner: $Author: lvw $ - * */ -/*******************************************************************/ #include "mg_tools.h" @@ -18,11 +14,12 @@ extern "C" #include <stdarg.h> #include <stdio.h> } -#include <stdlib.h> +#include <stdlib.h> #define MAX_BUFLEN 2048 #define MAX_QUERY_BUFLEN 2048 + static char buffer[MAX_BUFLEN]; static char querybuf[MAX_QUERY_BUFLEN]; @@ -68,22 +65,20 @@ void mgWarning(const char *fmt, ...) { va_list ap; - - va_start(ap, fmt); - + va_start(ap, fmt); vsnprintf(buffer, MAX_BUFLEN-1, fmt, ap); + if(STANDALONE) { fprintf(stderr, "warning: %s\n", buffer); } else - { + { #if !STANDALONE - isyslog( "Warning: %s\n", buffer); + isyslog( "Warning: %s\n", buffer); #endif - - } - + } + va_end(ap); } @@ -91,58 +86,60 @@ void mgError(const char *fmt, ...) { va_list ap; - - va_start(ap, fmt); - + va_start(ap, fmt); vsnprintf(buffer, MAX_BUFLEN-1, fmt, ap); + if(STANDALONE) - { - fprintf(stderr, "Error: %s\n", buffer); - exit(1); - } + { + fprintf(stderr, "Error: %s\n", buffer); + exit(1); + } else - { + { #if !STANDALONE - isyslog( "Error in Muggle: %s\n", buffer); + isyslog( "Error in Muggle: %s\n", buffer); #endif - } + } va_end(ap); } MYSQL_RES* mgSqlReadQuery(MYSQL *db, const char *fmt, ...) { - va_list ap; - va_start( ap, fmt ); - vsnprintf( querybuf, MAX_QUERY_BUFLEN-1, fmt, ap ); - - mgDebug(9, "mgSqlReadQuery: SQL query: '%s'", querybuf); + va_list ap; + va_start( ap, fmt ); + vsnprintf( querybuf, MAX_QUERY_BUFLEN-1, fmt, ap ); - if( mysql_query(db, querybuf) ) + if( mysql_query(db, querybuf) ) { - mgError("SQL error in MUGGLE\n%s\n", querybuf); + mgError( "SQL error in MUGGLE:\n%s\n", querybuf ); } - - MYSQL_RES *result = mysql_store_result(db); - - return result; + + MYSQL_RES *result = mysql_store_result(db); + + va_end(ap); + return result; } void mgSqlWriteQuery(MYSQL *db, const char *fmt, ...) { - va_list ap; - - va_start(ap, fmt); + va_list ap; + va_start(ap, fmt); + vsnprintf(querybuf, MAX_QUERY_BUFLEN-1, fmt, ap); - vsnprintf(querybuf, MAX_QUERY_BUFLEN-1, fmt, ap); - if(mysql_query(db,querybuf)) + if( mysql_query(db, querybuf) ) { - mgError("SQL error in MUGGLE\n%s\n", querybuf); + mgError( "SQL error in MUGGLE:\n%s\n", querybuf ); } + + va_end(ap); } /* -------------------- begin CVS log --------------------------------- * $Log: mg_tools.c,v $ + * Revision 1.4 2004/08/29 14:39:33 lvw + * Import running in basic version + * * Revision 1.3 2004/07/25 21:33:35 lvw * Removed bugs in finding track files and playlist indexing. * @@ -16,7 +16,8 @@ using namespace std; MYSQL *db;
-char *host, *user, *pass, *dbname;
+string host, user, pass, dbname;
+bool import_assorted;
int init_database()
{
@@ -26,8 +27,8 @@ int init_database() return -1;
}
- if( mysql_real_connect( db, host, user, pass,
- dbname, 0, NULL, 0 ) == NULL )
+ if( mysql_real_connect( db, host.c_str(), user.c_str(), pass.c_str(), dbname.c_str(),
+ 0, NULL, 0 ) == NULL )
{
return -2;
}
@@ -61,9 +62,22 @@ time_t get_db_modification_time( long uid ) return mt;
}
-long find_file_in_database( string filename )
+TagLib::String escape_string( MYSQL *db, TagLib::String s )
{
- MYSQL_RES *result = mgSqlReadQuery( db, "SELECT id FROM tracks WHERE mp3file=\"%s\"", filename.c_str() );
+ char *buf = strdup( s.toCString() );
+ char *escbuf = (char *) malloc( 2*strlen( buf ) + 1 );
+
+ int len = mysql_real_escape_string( db, escbuf, buf, strlen( buf ) );
+
+ return TagLib::String( escbuf );
+}
+
+long find_file_in_database( MYSQL *db, string filename )
+{
+ TagLib::String file = TagLib::String( filename.c_str() );
+ file = escape_string( db, file );
+
+ MYSQL_RES *result = mgSqlReadQuery( db, "SELECT id FROM tracks WHERE mp3file=\"%s\"", file.toCString() );
MYSQL_ROW row = mysql_fetch_row( result );
// obtain ID and return
@@ -73,7 +87,6 @@ long find_file_in_database( string filename ) // read tags from the mp3 file and store them into the corresponding database entry
void update_db( long uid, string filename )
{
- // char title[1024], album[1024], year[5], artist[1024], cddbid[20], trackno[5];
TagLib::String title, album, artist, genre, cddbid;
uint trackno, year;
@@ -92,20 +105,53 @@ void update_db( long uid, string filename ) artist = tag->artist();
trackno = tag->track();
genre = tag->genre();
+
+ title = escape_string( db, title );
+ album = escape_string( db, album );
+ artist = escape_string( db, artist );
- // TODO: CD identifier (?), playcounter, popularimeter?, volume adjustment
+ // TODO: CD identifier (if it exists), playcounter, popularimeter (rating?), volume adjustment, lyrics, cover
// finally update the database
// obtain associated album or create
if( album == "" )
- { // no album found, associate with default album
- cddbid = "0000unknown0000";
+ { // no album found, create default album for artist
+ MYSQL_RES *result = mgSqlReadQuery( db, "SELECT cddbid FROM album WHERE title=\"Unassigned\" AND artist=\"%s\"", artist.toCString() );
+ MYSQL_ROW row = mysql_fetch_row( result );
+
+ // Default album does not yet exist (num rows == 0)
+ int nrows = mysql_num_rows(result);
+ if( nrows == 0 )
+ {
+ // create new album entry "Unassigned" for this artist
+ long id = random();
+ char *buf;
+ asprintf( &buf, "%d-%s", id, tag->artist().toCString() );
+ cddbid = TagLib::String( buf ).substr( 0, 20 );
+ cddbid = escape_string( db, cddbid );
+ free( buf );
+
+ mgSqlWriteQuery( db, "INSERT INTO album (artist,title,cddbid) VALUES (\"%s\", \"Unassigned\", \"%s\")", artist.toCString(), cddbid.toCString() );
+ }
+ else
+ { // use first album found as source id for the track
+ cddbid = row[0];
+ }
}
else
{ // album tag found, associate or create
- MYSQL_RES *result = mgSqlReadQuery( db, "SELECT cddbid FROM album WHERE title=\"%s\" AND artist=\"%s\"",
- album.toCString(), artist.toCString() );
+ MYSQL_RES *result;
+ if( import_assorted )
+ { // lookup an existing album by title only (artist should be "Various Artists"
+ result = mgSqlReadQuery( db, "SELECT cddbid FROM album WHERE title=\"%s\" AND artist=\"Various Artists\"",
+ album.toCString(), artist.toCString() );
+ }
+ else
+ {
+ result = mgSqlReadQuery( db, "SELECT cddbid FROM album WHERE title=\"%s\" AND artist=\"%s\"",
+ album.toCString(), artist.toCString() );
+ }
MYSQL_ROW row = mysql_fetch_row( result );
// num rows == 0 ?
@@ -115,11 +161,17 @@ void update_db( long uid, string filename ) // create new album entry
long id = random();
char *buf;
- asprintf( &buf, "%d-%s", id, album.toCString() );
+ asprintf( &buf, "%d-%s", id, tag->album().toCString() );
cddbid = TagLib::String( buf ).substr( 0, 20 );
+ cddbid = escape_string( db, cddbid );
free( buf );
-
- mgSqlWriteQuery( db, "INSERT INTO album (artist,title,cddbid) VALUES (\"%s\", \"%s\", \"%s\")", artist.toCString(), album.toCString(), cddbid.toCString() );
+
+ if( import_assorted )
+ { // in this case, the album author is "Various Artists"
+ mgSqlWriteQuery( db, "INSERT INTO album (artist,title,cddbid) VALUES (\"Various Artists\", \"%s\", \"%s\")", album.toCString(), cddbid.toCString() ); }
+ else
+ {
+ mgSqlWriteQuery( db, "INSERT INTO album (artist,title,cddbid) VALUES (\"%s\", \"%s\", \"%s\")", artist.toCString(), album.toCString(), cddbid.toCString() ); }
}
else
{ // use first album found as source id for the track
@@ -138,8 +190,8 @@ void update_db( long uid, string filename ) // int t = title.find( "'" );
// int a = artist.find( "'" );
mgSqlWriteQuery( db,
- "INSERT INTO tracks (artist,title,year,sourceid,tracknb,mp3file)"
- " VALUES (\"%s\", \"%s\", %d, \"%s\", %d, \"%s\")",
+ "INSERT INTO tracks (artist,title,genre1,genre2,year,sourceid,tracknb,mp3file)"
+ " VALUES (\"%s\", \"%s\", \"\", \"\", %d, \"%s\", %d, \"%s\")",
artist.toCString(), title.toCString(), year, cddbid.toCString(), trackno, filename.c_str() );
/*
cout << "-- TAG --" << endl;
@@ -160,12 +212,12 @@ void update_tags( long uid, string filename ) }
-void evaluate_file( string filename )
+void evaluate_file( MYSQL *db, string filename )
{
if( 0 == init_database() )
{
// is filename stored in database?
- long uid = find_file_in_database( filename );
+ long uid = find_file_in_database( db, filename );
if( uid >= 0 )
{
// currently only update database, do not consider writing changes from the db back
@@ -198,33 +250,91 @@ void evaluate_file( string filename ) int main( int argc, char *argv[] )
{
- host = "134.130.124.222";
- user = "root";
- dbname = "giantdisc";
- pass = NULL;
- /*
+ int option_index;
+ string filename;
+
+ if( argc < 2 )
+ { // we need at least a filename!
+ cout << "mugglei -- import helper for Muggle VDR plugin" << endl;
+ cout << "(C) Lars von Wedel" << endl;
+ cout << "This is free software; see the source for copying conditions." << endl;
+ cout << "" << endl;
+ cout << "Options:" << endl;
+ cout << " -h <hostname> - specify host of mySql database server (default is 'localhost')" << endl;
+ cout << " -n <database> - specify database name (default is 'GiantDisc')" << endl;
+ cout << " -u <username> - specify user of mySql database (default is empty)" << endl;
+ cout << " -p <password> - specify password of user (default is empty password)" << endl;
+ cout << " -f <filename> - name of music file to import" << endl;
+ cout << " -a - import track as if it was on an assorted album" << endl;
+
+ exit( 1 );
+ }
+
+ // option defaults
host = "localhost";
- user = "vdr";
dbname = "GiantDisc";
- pass = NULL;
- */
+ user = "";
+ pass = "";
+ import_assorted = false;
+
+ // parse command line options
+ while( 1 )
+ {
+ int c = getopt(argc, argv, "h:u:p:n:af:");
+
+ if (c == -1)
+ break;
+
+ switch (c)
+ {
+ case 0:
+ { // long option
+
+ } break;
+ case 'h':
+ {
+ host = optarg;
+ } break;
+ case 'u':
+ {
+ user = optarg;
+ } break;
+ case 'p':
+ {
+ pass = optarg;
+ } break;
+ case 'd':
+ {
+ dbname = optarg;
+ } break;
+ case 'a':
+ {
+ import_assorted = true;
+ } break;
+ case 'f':
+ {
+ filename = optarg;
+ } break;
+ }
+ }
+ // init random number generator
struct timeval tv;
struct timezone tz;
gettimeofday( &tv, &tz );
- srandom( tv.tv_usec );
+ srandom( tv.tv_usec );
int res = init_database();
if( !res )
{
- update_db( 0, string( argv[1] ) );
+ update_db( 0, filename );
}
else
{
- printf( "Database initialization failed. Exiting.\n" );
+ cout << "Database initialization failed. Exiting.\n" << endl;
}
-
+
return res;
}
diff --git a/scripts/createdb.mysql b/scripts/createdb.mysql index b68d90c..7ccdc89 100755 --- a/scripts/createdb.mysql +++ b/scripts/createdb.mysql @@ -3,7 +3,12 @@ DROP DATABASE IF EXISTS GiantDisc; CREATE DATABASE GiantDisc; + use GiantDisc; -grant all privileges on GiantDisc.* to vdr@'%'; + +/* The first line is useful for granting access to user vdr on all computers in a network. */ +/* grant all privileges on GiantDisc.* to vdr@'%'; */ + +/* Grant access to user vdr on the local machine */ grant all privileges on GiantDisc.* to vdr@localhost; |