Import running in basic version

git-svn-id: https://vdr-muggle.svn.sourceforge.net/svnroot/vdr-muggle/trunk/muggle-plugin@127 e10066b5-e1e2-0310-b819-94efdf66514b

5 files changed, 358 insertions, 82 deletions

diff --git a/Makefile b/Makefile
index 46bac1e..870bd6e 100644
--- a/Makefile
+++ b/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/README b/README
index 4f6cbbf..209dd46 100644
--- a/README
+++ b/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 <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.
+
+
+
diff --git a/mg_tools.c b/mg_tools.c
index d12d0df..b581396 100644
--- a/mg_tools.c
+++ b/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 <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.
  *
diff --git a/mugglei.c b/mugglei.c
index 191bebb..66fb766 100755
--- a/mugglei.c
+++ b/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 <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;