From eb91c040703370586ae376bae7a94ff276ef188e Mon Sep 17 00:00:00 2001 From: lvw Date: Sun, 29 Aug 2004 14:39:33 +0000 Subject: Import running in basic version git-svn-id: https://vdr-muggle.svn.sourceforge.net/svnroot/vdr-muggle/trunk@127 e10066b5-e1e2-0310-b819-94efdf66514b --- muggle-plugin/Makefile | 8 +- muggle-plugin/README | 168 +++++++++++++++++++++++++++++++++- muggle-plugin/mg_tools.c | 87 +++++++++--------- muggle-plugin/mugglei.c | 170 ++++++++++++++++++++++++++++------- muggle-plugin/scripts/createdb.mysql | 7 +- 5 files changed, 358 insertions(+), 82 deletions(-) (limited to 'muggle-plugin') diff --git a/muggle-plugin/Makefile b/muggle-plugin/Makefile index 46bac1e..870bd6e 100644 --- a/muggle-plugin/Makefile +++ b/muggle-plugin/Makefile @@ -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 - diff --git a/muggle-plugin/README b/muggle-plugin/README index 4f6cbbf..209dd46 100644 --- a/muggle-plugin/README +++ b/muggle-plugin/README @@ -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 - Ralf Klüber , + Ralf Klueber , 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. + + + diff --git a/muggle-plugin/mg_tools.c b/muggle-plugin/mg_tools.c index d12d0df..b581396 100644 --- a/muggle-plugin/mg_tools.c +++ b/muggle-plugin/mg_tools.c @@ -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 #include } -#include +#include #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. * diff --git a/muggle-plugin/mugglei.c b/muggle-plugin/mugglei.c index 191bebb..66fb766 100755 --- a/muggle-plugin/mugglei.c +++ b/muggle-plugin/mugglei.c @@ -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 - specify host of mySql database server (default is 'localhost')" << endl; + cout << " -n - specify database name (default is 'GiantDisc')" << endl; + cout << " -u - specify user of mySql database (default is empty)" << endl; + cout << " -p - specify password of user (default is empty password)" << endl; + cout << " -f - 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/muggle-plugin/scripts/createdb.mysql b/muggle-plugin/scripts/createdb.mysql index b68d90c..7ccdc89 100755 --- a/muggle-plugin/scripts/createdb.mysql +++ b/muggle-plugin/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; -- cgit v1.2.3