Further stuff for using mySQL embedded

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

1 files changed, 58 insertions, 1 deletions

diff --git a/mugglei.c b/mugglei.c
index a31a601..c3e07f5 100755
--- a/mugglei.c
+++ b/mugglei.c
@@ -49,8 +49,29 @@ static char *server_groups[] =
   (char *)NULL
 };
 
+char *db_cmds[] = 
+{
+  "DROP DATABASE IF EXISTS GiantDisc; CREATE DATABASE GiantDisc;",
+  "grant all privileges on GiantDisc.* to vdr@localhost;",
+  "use GiantDisc;",
+  "drop table if exists album; CREATE TABLE album ( artist varchar(255) default NULL, title varchar(255) default NULL, cddbid varchar(20) NOT NULL default '', coverimg varchar(255) default NULL, covertxt mediumtext, modified date default NULL, genre varchar(10) default NULL, PRIMARY KEY  (cddbid), KEY artist (artist(10)), KEY title (title(10)), KEY genre (genre), KEY modified (modified)) TYPE=MyISAM;",
+  "drop table if exists genre; CREATE TABLE genre (id varchar(10) NOT NULL default '', id3genre smallint(6) default NULL, genre varchar(255) default NULL, freq int(11) default NULL, PRIMARY KEY (id)) TYPE=MyISAM;",
+  "drop table if exists language; CREATE TABLE language (id varchar(4) NOT NULL default '', language varchar(40) default NULL, freq int(11) default NULL, PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists musictype; CREATE TABLE musictype (musictype varchar(40) default NULL, id tinyint(3) unsigned NOT NULL auto_increment, PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists player;CREATE TABLE player ( ipaddr varchar(255) NOT NULL default '', uichannel varchar(255) NOT NULL default '', logtarget int(11) default NULL, cdripper varchar(255) default NULL, mp3encoder varchar(255) default NULL, cdromdev varchar(255) default NULL, cdrwdev varchar(255) default NULL, id int(11) NOT NULL default '0', PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists playerstate;CREATE TABLE playerstate ( playerid int(11) NOT NULL default '0', playertype int(11) NOT NULL default '0', snddevice varchar(255) default NULL, playerapp varchar(255) default NULL, playerparams varchar(255) default NULL, ptlogger varchar(255) default NULL, currtracknb int(11) default NULL, state varchar(4) default NULL, shufflepar varchar(255) default NULL, shufflestat varchar(255) default NULL, pauseframe int(11) default NULL, framesplayed int(11) default NULL, framestotal int(11) default NULL, anchortime bigint(20) default NULL, PRIMARY KEY  (playerid,playertype)) TYPE=HEAP;",
+  "drop table if exists playlist;CREATE TABLE playlist ( title varchar(255) default NULL, author varchar(255) default NULL, note varchar(255) default NULL, created timestamp(8) NOT NULL, id int(10) unsigned NOT NULL auto_increment, PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists playlistitem;CREATE TABLE playlistitem ( playlist int(11) NOT NULL default '0', tracknumber mediumint(9) NOT NULL default '0', trackid int(11) default NULL, PRIMARY KEY  (playlist,tracknumber)) TYPE=MyISAM;",
+  "drop table if exists playlog;CREATE TABLE playlog ( trackid int(11) default NULL, played date default NULL, id tinyint(3) unsigned NOT NULL auto_increment, PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists recordingitem;CREATE TABLE recordingitem ( trackid int(11) default NULL, recdate date default NULL, rectime time default NULL, reclength int(11) default NULL, enddate date default NULL, endtime time default NULL,  repeat varchar(10) default NULL, initcmd varchar(255) default NULL, parameters varchar(255) default NULL, atqjob int(11) default NULL, id int(11) NOT NULL default '0', PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists source; CREATE TABLE source ( source varchar(40) default NULL, id tinyint(3) unsigned NOT NULL auto_increment, PRIMARY KEY  (id)) TYPE=MyISAM;",
+  "drop table if exists tracklistitem;CREATE TABLE tracklistitem ( playerid int(11) NOT NULL default '0', listtype smallint(6) NOT NULL default '0', tracknb int(11) NOT NULL default '0', trackid int(11) NOT NULL default '0', PRIMARY KEY  (playerid,listtype,tracknb)) TYPE=MyISAM;",
+  "drop table if exists tracks;CREATE TABLE tracks ( artist varchar(255) default NULL, title varchar(255) default NULL, genre1 varchar(10) default NULL, genre2 varchar(10) default NULL, year smallint(5) unsigned default NULL, lang varchar(4) default NULL, type tinyint(3) unsigned default NULL, rating tinyint(3) unsigned default NULL, length smallint(5) unsigned default NULL, source tinyint(3) unsigned default NULL, sourceid varchar(20) default NULL, tracknb tinyint(3) unsigned default NULL, mp3file varchar(255) default NULL, condition tinyint(3) unsigned default NULL, voladjust smallint(6) default '0', lengthfrm mediumint(9) default '0', startfrm mediumint(9) default '0', bpm smallint(6) default '0', lyrics mediumtext, bitrate varchar(10) default NULL, created date default NULL, modified date default NULL, backup tinyint(3) unsigned default NULL, samplerate int(7) unsigned default NULL, channels   tinyint(3) unsigned default NULL,  id int(11) NOT NULL auto_increment, PRIMARY KEY  (id), KEY title (title(10)), KEY mp3file (mp3file(10)), KEY genre1 (genre1), KEY genre2 (genre2), KEY year (year), KEY lang (lang), KEY type (type), KEY rating (rating), KEY sourceid (sourceid), KEY artist (artist(10))) TYPE=MyISAM;"
+};
+
+
 std::string host, user, pass, dbname, sck;
-bool import_assorted, delete_mode;
+bool import_assorted, delete_mode, create_mode;
 
 #define  MAX_QUERY_BUFLEN  2048
 static char querybuf[MAX_QUERY_BUFLEN];
@@ -124,6 +145,32 @@ int init_database()
   return 0;
 }
 
+MYSQL_RES *
+exec_sql( std::string query )
+{
+  if( query.empty() )
+    return 0;
+  mgDebug( 3, "exec_sql(%X,%s)", db, query.c_str() );
+  if (mysql_query (db, (query + ';').c_str ()))
+    {
+      mgError("SQL Error in %s: %s",query.c_str(),mysql_error (db));
+      std::cout << "ERROR in " << query << ":" << mysql_error(db) << std::endl;
+      return 0;
+    }
+  return mysql_store_result(db);
+}
+
+int create_database()
+{
+  // create database and tables
+  int len = sizeof( db_cmds ) / sizeof( char* );
+  for( int i=0; i < len; i ++ )
+    {
+      exec_sql( std::string( db_cmds[i] ) );
+    }
+  return 0;
+}
+
 time_t get_fs_modification_time( std::string filename )
 {
   struct stat *buf = (struct stat*) malloc( sizeof( struct stat ) );
@@ -489,6 +536,7 @@ int main( int argc, char *argv[] )
       std::cout << "  -f <filename>       - name of music file to import or update" << std::endl;
       std::cout << "  -a                  - import track as if it was on an assorted album" << std::endl;
       std::cout << "  -z                  - scan all database entries and delete entries for files not found" << std::endl;
+      std::cout << "  -c                  - create a new database entry deleting existing entries" << std::endl;
 
       exit( 1 );
     }
@@ -501,6 +549,7 @@ int main( int argc, char *argv[] )
   sck = "";
   import_assorted = false;
   delete_mode = false;
+  create_mode = false;
   filename = "";
 
   // parse command line options
@@ -549,6 +598,10 @@ int main( int argc, char *argv[] )
           {
             delete_mode = true;
           } break;
+        case 'c':
+          {
+            create_mode = true;
+          } break;
 	}
     }
 
@@ -569,6 +622,10 @@ int main( int argc, char *argv[] )
 	{
 	  update_tags( -1 );
 	}
+      else if( create_mode )
+	{
+	  create_database();
+	}
       if( filename.size() )
 	{
 	  evaluate_file( filename );