mg_mysql

git-svn-id: https://vdr-muggle.svn.sourceforge.net/svnroot/vdr-muggle/branches/0.1.3-wr@517 e10066b5-e1e2-0310-b819-94efdf66514b

7 files changed, 442 insertions, 248 deletions

diff --git a/Makefile b/Makefile
index 2349694..84b74f6 100644
--- a/Makefile
+++ b/Makefile
@@ -55,7 +55,7 @@ MIFLAGS += -I/usr/include/taglib -lmysqlclient
 
 ### The object files (add further files here):
 
-OBJS = $(PLUGIN).o i18n.o mg_valmap.o mg_order.o mg_content.o mg_selection.o mg_actions.o vdr_menu.o mg_tools.o \
+OBJS = $(PLUGIN).o i18n.o mg_valmap.o mg_mysql.o mg_order.o mg_content.o mg_selection.o mg_actions.o vdr_menu.o mg_tools.o \
 	vdr_decoder_mp3.o vdr_stream.o vdr_decoder.o vdr_player.o \
 	vdr_setup.o mg_setup.o
 
diff --git a/mg_mysql.c b/mg_mysql.c
new file mode 100644
index 0000000..0a78a05
--- /dev/null
+++ b/mg_mysql.c
@@ -0,0 +1,315 @@
+/*! \file   mg_mysql.c
+ *  \brief  A capsule around MySql database access
+ *
+ * \version $Revision: 1.2 $
+ * \date    $Date: 2005-02-10 17:42:54 +0100 (Thu, 10 Feb 2005) $
+ * \author  Ralf Klueber, Lars von Wedel, Andreas Kellner
+ * \author  file owner: $Author: LarsAC $
+ */
+
+#include "mg_mysql.h"
+#include "mg_tools.h"
+
+#include <stdarg.h>
+
+#include "vdr_setup.h"
+
+bool needGenre2;
+static bool needGenre2_set;
+
+
+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, "
+	  "folder1 varchar(255), "
+	  "folder2 varchar(255), "
+	  "folder3 varchar(255), "
+	  "folder4 varchar(255), "
+	  "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;"
+};
+
+
+mgmySql::mgmySql() 
+{
+	m_db = 0;
+	Connect();
+}
+
+mgmySql::~mgmySql()
+{
+    if (m_db) 
+    {
+       mysql_close (m_db);
+       m_db = 0;
+    }
+}
+
+
+MYSQL_RES*
+mgmySql::exec_sql( string query)
+{
+  if (!m_db || query.empty())
+	  return 0;
+  mgDebug(3,"exec_sql(%X,%s)",m_db,query.c_str());
+  if (mysql_query (m_db, (query + ';').c_str ()))
+  {
+    mgError("SQL Error in %s: %s",query.c_str(),mysql_error (m_db));
+    std::cout<<"ERROR in " << query << ":" << mysql_error(m_db)<<std::endl;
+    return 0;
+  }
+  return mysql_store_result (m_db);
+}
+
+string
+mgmySql::get_col0( const string query)
+{
+	MYSQL_RES * sql_result = exec_sql ( query);
+	if (!sql_result)
+		return "NULL";
+	MYSQL_ROW row = mysql_fetch_row (sql_result);
+	string result;
+	if (row == NULL)
+		result = "NULL";
+	else if (row[0] == NULL)
+		result = "NULL";
+	else
+		result = row[0];
+	mysql_free_result (sql_result);
+	return result;
+}
+
+unsigned long
+mgmySql::exec_count( const string query) 
+{
+	return atol (get_col0 ( query).c_str ());
+}
+
+
+void mgmySql::Create()
+{
+  // create database and tables
+  int len = sizeof( db_cmds ) / sizeof( char* );
+  for( int i=0; i < len; i ++ )
+    {
+      exec_sql( string( db_cmds[i] ) );
+    }
+}
+
+string
+mgmySql::sql_string( const string s )
+{
+  if (!this)
+     return "'" + s + "'";
+
+  char *buf = (char *) malloc( 2*s.size() + 1 );
+  mysql_real_escape_string( m_db, buf, s.c_str(), s.size() );
+  string result = "'" + string( buf ) + "'";
+  free( buf );
+  return result;
+}
+
+void
+mgmySql::Connect ()
+{
+    if (m_db) 
+    {
+       mysql_close (m_db);
+       m_db = 0;
+    }
+    if (the_setup.DbHost == "") return;
+    m_db = mysql_init (0);
+    if (!m_db)
+        return;
+    bool success;
+    if (the_setup.DbSocket != NULL)
+    {
+      mgDebug(1,"Using socket %s for connecting to Database %s as user %s.",
+		      the_setup.DbSocket,
+		      the_setup.DbName,
+		      the_setup.DbUser);
+      mgDebug(3,"DbPassword is: '%s'",the_setup.DbPass);
+      success = (mysql_real_connect( m_db,
+			      "",
+			      the_setup.DbUser,       
+			      the_setup.DbPass, 
+			      the_setup.DbName,
+			      0,
+			      the_setup.DbSocket, 0 ) != 0 );
+    }
+    else
+    {
+      mgDebug(1,"Using TCP-%s for connecting to Database %s as user %s.",
+		      the_setup.DbHost,
+		      the_setup.DbName,
+		      the_setup.DbUser);
+      mgDebug(3,"DbPassword is: '%s'",the_setup.DbPass);
+      success = ( mysql_real_connect( m_db,
+			      the_setup.DbHost, 
+			      the_setup.DbUser,       
+			      the_setup.DbPass, 
+			      the_setup.DbName,
+			      the_setup.DbPort,
+			      0, 0 ) != 0 );
+    }
+    if (!success)
+    {
+	mgWarning("Failed to connect to host '%s' as User '%s', Password '%s': Error: %s",
+			    the_setup.DbHost,the_setup.DbUser,the_setup.DbPass,mysql_error(m_db));
+        mysql_close (m_db);
+	m_db = 0;
+    }
+    if (!needGenre2_set && m_db)
+    {
+	needGenre2_set=true;
+	needGenre2=exec_count("SELECT COUNT(DISTINCT genre2) from tracks")>1;
+    }
+    return;
+}
+
+
diff --git a/mg_mysql.h b/mg_mysql.h
new file mode 100644
index 0000000..efa5902
--- /dev/null
+++ b/mg_mysql.h
@@ -0,0 +1,64 @@
+/*! 
+ * \file   mg_mysql.h
+ * \brief  A capsule around MySql database access
+ *
+ * \version $Revision: 1.2 $
+ * \date    $Date: 2005-02-10 17:42:54 +0100 (Thu, 10 Feb 2005) $
+ * \author  Ralf Klueber, Lars von Wedel, Andreas Kellner, Wolfgang Rohdewald
+ * \author  Responsible author: $Author: LarsAC $
+ */
+
+#ifndef __MG_MYSQL_H
+#define __MG_MYSQL_H
+
+#include <string>
+#include <mysql/mysql.h>
+
+using namespace std;
+
+/*!
+ * \brief an abstract database class
+ * 
+ */
+class mgmySql
+{
+ public:
+
+  /*! \brief default constructor 
+   */
+  mgmySql( );
+
+  ~mgmySql();
+
+  /*! 
+   * \brief helper function to execute queries
+   *
+   */
+  MYSQL_RES* exec_sql( const string query);
+
+  /*!
+   * \brief escape arguments to be contained in a query
+   */
+  string sql_string( string s );
+
+  string get_col0( const string query);
+  
+/*! \brief executes a query and returns the integer value from
+ * the first column in the first row. The query shold be a COUNT query
+ * returning only one row.
+ * \param query the SQL query to be executed
+ */
+  unsigned long exec_count (string query);
+
+  long thread_id() { return mysql_thread_id(m_db);}
+  long affected_rows() { return mysql_affected_rows(m_db);}
+  bool Connected() const { return m_db;}
+  void Connect();
+  //! \brief create database and tables
+  void Create();
+
+ private:
+  MYSQL *m_db;
+};
+
+#endif
diff --git a/mg_order.c b/mg_order.c
index 6a4c855..ae8623e 100644
--- a/mg_order.c
+++ b/mg_order.c
@@ -24,18 +24,6 @@ strlist& operator+=(strlist&a, strlist b)
 }
 
 
-string
-sql_string (MYSQL *db, const string s) 
-{
-	if (!db)
-		return "'" + s + "'";
-	char *buf = (char *) malloc (s.size () * 2 + 1);
-	mysql_real_escape_string (db, buf, s.c_str (), s.size ());
-	string result = "'" + string (buf) + "'";
-	free (buf);
-	return result;
-}
-
 /*! \brief if the SQL command works on only 1 table, remove all table
 * qualifiers. Example: SELECT tracks.title FROM tracks becomes SELECT title
 * FROM tracks
@@ -65,48 +53,6 @@ optimize (string & spar)
 	return spar;
 }
 
-MYSQL_RES *
-exec_sql (MYSQL *db,string query)
-{
-	if (!db)
-		return 0;
-	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);
-}
-
-string
-get_col0(MYSQL *db, string query)
-{
-    MYSQL_RES * sql_result = exec_sql (db, query);
-    if (!sql_result)
-	    return "NULL";
-    MYSQL_ROW row = mysql_fetch_row (sql_result);
-    string result;
-    if (row == NULL)
-        result = "NULL";
-    else if (row[0] == NULL)
-        result = "NULL";
-    else
-        result = row[0];
-    mysql_free_result (sql_result);
-    return result;
-}
-
-long
-exec_count(MYSQL *db, string query)
-{
-    return atol (get_col0 (db, query).c_str ());
-}
-
-
 string&
 addsep (string & s, string sep, string n)
 {
@@ -158,7 +104,7 @@ class mgKeyNormal : public mgKey {
 	public:
 		mgKeyNormal(const mgKeyNormal& k);
 			mgKeyNormal(const mgKeyTypes kt, string table, string field);
-		virtual mgParts Parts(MYSQL *db,bool orderby=false) const;
+		virtual mgParts Parts(mgmySql &db,bool orderby=false) const;
 		string value() const;
 		string id() const;
 		void set(string value,string id);
@@ -166,8 +112,8 @@ class mgKeyNormal : public mgKey {
 		virtual string expr() const { return m_table + "." + m_field; }
 		virtual string table() const { return m_table; }
 	protected:
-		string IdClause(MYSQL *db,string what,string::size_type start=0,string::size_type len=string::npos) const;
-		void AddIdClause(MYSQL *db,mgParts &result,string what) const;
+		string IdClause(mgmySql &db,string what,string::size_type start=0,string::size_type len=string::npos) const;
+		void AddIdClause(mgmySql &db,mgParts &result,string what) const;
 		string m_id;
 		string m_field;
 	private:
@@ -184,17 +130,17 @@ class mgKeyDate : public mgKeyNormal {
 class mgKeyTrack : public mgKeyNormal {
 	public:
 		mgKeyTrack() : mgKeyNormal(keyTrack,"tracks","tracknb") {};
-		mgParts Parts(MYSQL *db,bool orderby=false) const;
+		mgParts Parts(mgmySql &db,bool orderby=false) const;
 };
 
 class mgKeyAlbum : public mgKeyNormal {
 	public:
 		mgKeyAlbum() : mgKeyNormal(keyAlbum,"album","title") {};
-		mgParts Parts(MYSQL *db,bool orderby=false) const;
+		mgParts Parts(mgmySql &db,bool orderby=false) const;
 };
 
 mgParts
-mgKeyAlbum::Parts(MYSQL *db,bool orderby) const
+mgKeyAlbum::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result = mgKeyNormal::Parts(db,orderby);
 	result.tables.push_back("tracks");
@@ -205,7 +151,7 @@ class mgKeyFolder : public mgKeyNormal {
 	public:
 		mgKeyFolder(mgKeyTypes kt,const char *fname)
 			: mgKeyNormal(kt,"tracks",fname) { m_enabled=-1;};
-		bool Enabled(MYSQL *db);
+		bool Enabled(mgmySql &db);
 	private:
 		int m_enabled;
 };
@@ -228,15 +174,15 @@ class mgKeyFolder4 : public mgKeyFolder {
 };
 
 bool
-mgKeyFolder::Enabled(MYSQL *db)
+mgKeyFolder::Enabled(mgmySql &db)
 {
     if (m_enabled<0)
     {
-    	if (!db) 
+    	if (!db.Connected()) 
 		return false;
     	char *b;
     	asprintf(&b,"DESCRIBE tracks %s",m_field.c_str());
-    	MYSQL_RES * rows = exec_sql (db, b);
+    	MYSQL_RES * rows = db.exec_sql (b);
     	free(b);
     	if (rows)
 	{
@@ -251,14 +197,14 @@ class mgKeyGenres : public mgKeyNormal {
 	public:
 		mgKeyGenres() : mgKeyNormal(keyGenres,"tracks","genre1") {};
 		mgKeyGenres(mgKeyTypes kt) : mgKeyNormal(kt,"tracks","genre1") {};
-		mgParts Parts(MYSQL *db,bool orderby=false) const;
+		mgParts Parts(mgmySql &db,bool orderby=false) const;
 		string map_idfield() const { return "id"; }
 		string map_valuefield() const { return "genre"; }
 		string map_valuetable() const { return "genre"; }
 	protected:
 		virtual unsigned int genrelevel() const { return 4; }
 	private:
-		string GenreClauses(bool orderby) const;
+		string GenreClauses(mgmySql &db,bool orderby) const;
 };
 
 class mgKeyGenre1 : public mgKeyGenres
@@ -283,7 +229,7 @@ class mgKeyGenre3 : public mgKeyGenres
 };
 
 string
-mgKeyGenres::GenreClauses(bool orderby) const
+mgKeyGenres::GenreClauses(mgmySql &db,bool orderby) const
 {
 	strlist g1;
 	strlist g2;
@@ -304,8 +250,8 @@ mgKeyGenres::GenreClauses(bool orderby) const
 	{
 		unsigned int len=genrelevel();
 		if (len==4) len=0;
-      		g1.push_back(IdClause(0,"tracks.genre1",0,genrelevel()));
-      		g2.push_back(IdClause(0,"tracks.genre2",0,genrelevel()));
+      		g1.push_back(IdClause(db,"tracks.genre1",0,genrelevel()));
+      		g2.push_back(IdClause(db,"tracks.genre2",0,genrelevel()));
 	}
 
 	extern bool needGenre2;
@@ -323,10 +269,10 @@ mgKeyGenres::GenreClauses(bool orderby) const
 
 
 mgParts
-mgKeyGenres::Parts(MYSQL *db,bool orderby) const
+mgKeyGenres::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result;
-   	result.clauses.push_back(GenreClauses(orderby));
+   	result.clauses.push_back(GenreClauses(db,orderby));
 	result.tables.push_back("tracks");
 	if (orderby)
 	{
@@ -342,7 +288,7 @@ mgKeyGenres::Parts(MYSQL *db,bool orderby) const
 class mgKeyLanguage : public mgKeyNormal {
 	public:
 		mgKeyLanguage() : mgKeyNormal(keyLanguage,"tracks","lang") {};
-		mgParts Parts(MYSQL *db,bool orderby=false) const;
+		mgParts Parts(mgmySql &db,bool orderby=false) const;
 		string map_idfield() const { return "id"; }
 		string map_valuefield() const { return "language"; }
 		string map_valuetable() const { return "language"; }
@@ -351,7 +297,7 @@ class mgKeyLanguage : public mgKeyNormal {
 class mgKeyCollection: public mgKeyNormal {
 	public:
   	  mgKeyCollection() : mgKeyNormal(keyCollection,"playlist","id") {};
-	  mgParts Parts(MYSQL *db,bool orderby=false) const;
+	  mgParts Parts(mgmySql &db,bool orderby=false) const;
 	 string map_idfield() const { return "id"; }
 	 string map_valuefield() const { return "title"; }
 	 string map_valuetable() const { return "playlist"; }
@@ -359,7 +305,7 @@ class mgKeyCollection: public mgKeyNormal {
 class mgKeyCollectionItem : public mgKeyNormal {
 	public:
 		mgKeyCollectionItem() : mgKeyNormal(keyCollectionItem,"playlistitem","tracknumber") {};
-		mgParts Parts(MYSQL *db,bool orderby=false) const;
+		mgParts Parts(mgmySql &db,bool orderby=false) const;
 };
 
 class mgKeyDecade : public mgKeyNormal {
@@ -415,7 +361,7 @@ mgParts::~mgParts()
 }
 
 mgParts
-mgKeyNormal::Parts(MYSQL *db, bool orderby) const
+mgKeyNormal::Parts(mgmySql &db, bool orderby) const
 {
 	mgParts result;
 	result.tables.push_back(table());
@@ -429,30 +375,30 @@ mgKeyNormal::Parts(MYSQL *db, bool orderby) const
 }
 
 string
-mgKeyNormal::IdClause(MYSQL *db,string what,string::size_type start,string::size_type len) const
+mgKeyNormal::IdClause(mgmySql &db,string what,string::size_type start,string::size_type len) const
 {
 	if (len==0)
 		len=string::npos;
        	if (id() == "'NULL'")
 		return what + " is NULL";
        	else if (len==string::npos)
-		return what + "=" + sql_string(db,id());
+		return what + "=" + db.sql_string(id());
 	else
 	{
 		return "substring("+what + ","+ltos(start+1)+","+ltos(len)+")="
-			+ sql_string(db,id().substr(start,len));
+			+ db.sql_string(id().substr(start,len));
 	}
 }
 
 void
-mgKeyNormal::AddIdClause(MYSQL *db,mgParts &result,string what) const
+mgKeyNormal::AddIdClause(mgmySql &db,mgParts &result,string what) const
 {
 	if (id() != EMPTY)
        		result.clauses.push_back(IdClause(db,what));
 }
 
 mgParts
-mgKeyTrack::Parts(MYSQL *db,bool orderby) const
+mgKeyTrack::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result;
 	result.tables.push_back("tracks");
@@ -468,7 +414,7 @@ mgKeyTrack::Parts(MYSQL *db,bool orderby) const
 }
 
 mgParts
-mgKeyLanguage::Parts(MYSQL *db,bool orderby) const
+mgKeyLanguage::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result;
 	AddIdClause(db,result,"tracks.lang");
@@ -484,7 +430,7 @@ mgKeyLanguage::Parts(MYSQL *db,bool orderby) const
 }
 
 mgParts
-mgKeyCollection::Parts(MYSQL *db,bool orderby) const
+mgKeyCollection::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result;
 	if (orderby)
@@ -504,7 +450,7 @@ mgKeyCollection::Parts(MYSQL *db,bool orderby) const
 }
 
 mgParts
-mgKeyCollectionItem::Parts(MYSQL *db,bool orderby) const
+mgKeyCollectionItem::Parts(mgmySql &db,bool orderby) const
 {
 	mgParts result;
 	result.tables.push_back("playlistitem");
@@ -911,7 +857,7 @@ cleanagain:
 
 
 mgParts
-mgOrder::Parts(MYSQL *db,unsigned int level,bool orderby) const
+mgOrder::Parts(mgmySql &db,unsigned int level,bool orderby) const
 {
 	mgParts result;
 	result.orderByCount = m_orderByCount;
diff --git a/mg_order.h b/mg_order.h
index 0943d88..3fd0935 100644
--- a/mg_order.h
+++ b/mg_order.h
@@ -1,7 +1,6 @@
 #ifndef _MG_SQL_H
 #define _MG_SQL_H
 #include <stdlib.h>
-#include <mysql/mysql.h>
 #include <typeinfo>
 #include <string>
 #include <assert.h>
@@ -9,6 +8,7 @@
 #include <vector>
 #include <sstream>
 #include "mg_valmap.h"
+#include "mg_mysql.h"
 
 using namespace std;
 
@@ -79,7 +79,7 @@ private:
 class mgKey {
 	public:
 		virtual ~mgKey() {};
-		virtual mgParts Parts(MYSQL *db,bool orderby=false) const = 0;
+		virtual mgParts Parts(mgmySql &db,bool orderby=false) const = 0;
 		virtual string id() const = 0;
 		virtual string value () const = 0;
 		//!\brief translate field into user friendly string
@@ -88,8 +88,7 @@ class mgKey {
 		virtual string map_idfield() const { return ""; }
 		virtual string map_valuefield() const { return ""; }
 		virtual string map_valuetable() const { return ""; }
-		void setdb(MYSQL *db);
-		virtual bool Enabled(MYSQL *db) { return true; }
+		virtual bool Enabled(mgmySql &db) { return true; }
 };
 
 
@@ -125,13 +124,6 @@ private:
 	mgReferences ref;
 };
 
-string
-sql_string (MYSQL *db, const string s);
-
-MYSQL_RES * exec_sql (MYSQL *db,string query);
-string get_col0 (MYSQL *db,string query);
-long exec_count (MYSQL *db,string query);
-
 //! \brief converts long to string
 string itos (int i);
 
@@ -150,7 +142,7 @@ public:
 	~mgOrder();
 	void InitFrom(const mgOrder &from);
         void DumpState(mgValmap& nv, char *prefix) const;
-	mgParts Parts(MYSQL *db,unsigned int level,bool orderby=true) const;
+	mgParts Parts(mgmySql &db,const unsigned int level,bool orderby=true) const;
 	const mgOrder& operator=(const mgOrder& from);
 	mgKey*& operator[](unsigned int idx);
 	unsigned int size() const { return Keys.size(); }
diff --git a/mg_selection.c b/mg_selection.c
index 3bb79e1..05904f3 100644
--- a/mg_selection.c
+++ b/mg_selection.c
@@ -14,8 +14,6 @@
 #include "vdr_setup.h"
 #include "mg_tools.h"
 
-bool needGenre2;
-bool needGenre2_set;
 
 //! \brief adds string n to string s, using a comma to separate them
 static string comma (string & s, string n);
@@ -107,41 +105,6 @@ mgSelection::getKeyType (const unsigned int level) const
         return order.getKeyType(level);
 }
 
-MYSQL_RES*
-mgSelection::exec_sql(string query) const
-{
-	return ::exec_sql(m_db, query);
-}
-
-/*! \brief executes a query and returns the first columnu of the
- * first row.
- * \param query the SQL query string to be executed
- */
-string mgSelection::get_col0 (string query) const
-{
-    return ::get_col0(m_db, query);
-}
-
-
-unsigned long
-mgSelection::exec_count (string query) const
-{
-    return ::exec_count(m_db, query);
-}
-
-
-
-string
-mgSelection::sql_string (const string s) const
-{
-    char *buf = (char *) malloc (s.size () * 2 + 1);
-    mysql_real_escape_string (m_db, buf, s.c_str (), s.size ());
-    string result = "'" + std::string (buf) + "'";
-    free (buf);
-    return result;
-}
-
-
 mgContentItem *
 mgSelection::getTrack (unsigned int position)
 {
@@ -230,28 +193,28 @@ mgSelection::LoopMode mgSelection::toggleLoopMode ()
 unsigned int
 mgSelection::AddToCollection (const string Name)
 {
-    if (!m_db) return 0;
+    if (!m_db.Connected()) return 0;
     CreateCollection(Name);
-    string listid = sql_string (get_col0
-        ("SELECT id FROM playlist WHERE title=" + sql_string (Name)));
+    string listid = m_db.sql_string (m_db.get_col0
+        ("SELECT id FROM playlist WHERE title=" + m_db.sql_string (Name)));
     unsigned int tracksize = getNumTracks ();
 
     // this code is rather complicated but works in a multi user
     // environment:
  
     // insert a unique trackid:
-    string trackid = ltos(mysql_thread_id(m_db)+1000000);
-    exec_sql("INSERT INTO playlistitem SELECT "+listid+","
+    string trackid = ltos(m_db.thread_id()+1000000);
+    m_db.exec_sql("INSERT INTO playlistitem SELECT "+listid+","
 	   "MAX(tracknumber)+"+ltos(tracksize)+","+trackid+
 	   " FROM playlistitem WHERE playlist="+listid);
     
     // find tracknumber of the trackid we just inserted:
     string sql = string("SELECT tracknumber FROM playlistitem WHERE "
 		    "playlist=")+listid+" AND trackid="+trackid;
-    long first = atol(get_col0(sql).c_str()) - tracksize + 1;
+    long first = atol(m_db.get_col0(sql).c_str()) - tracksize + 1;
 
     // replace the place holder trackid by the correct value:
-    exec_sql("UPDATE playlistitem SET trackid="+ltos(m_tracks[tracksize-1].getId())+
+    m_db.exec_sql("UPDATE playlistitem SET trackid="+ltos(m_tracks[tracksize-1].getId())+
 		    " WHERE playlist="+listid+" AND trackid="+trackid);
     
     // insert all other tracks:
@@ -264,11 +227,11 @@ mgSelection::AddToCollection (const string Name)
 	comma(sql, item);
 	if ((i%100)==99)
 	{
-    		exec_sql (sql_prefix+sql);
+    		m_db.exec_sql (sql_prefix+sql);
 		sql = "";
 	}
     }
-    if (!sql.empty()) exec_sql (sql_prefix+sql);
+    if (!sql.empty()) m_db.exec_sql (sql_prefix+sql);
     if (inCollection(Name)) clearCache ();
     return tracksize;
 }
@@ -277,11 +240,11 @@ mgSelection::AddToCollection (const string Name)
 unsigned int
 mgSelection::RemoveFromCollection (const string Name)
 {
-    if (!m_db) return 0;
+    if (!m_db.Connected()) return 0;
     mgParts p = order.Parts(m_db,m_level,false);
     string sql = p.sql_delete_from_collection(id(keyCollection,Name));
-    exec_sql (sql);
-    unsigned int removed = mysql_affected_rows (m_db);
+    m_db.exec_sql (sql);
+    unsigned int removed = m_db.affected_rows ();
     if (inCollection(Name)) clearCache ();
     return removed;
 }
@@ -289,30 +252,30 @@ mgSelection::RemoveFromCollection (const string Name)
 
 bool mgSelection::DeleteCollection (const string Name)
 {
-    if (!m_db) return false;
+    if (!m_db.Connected()) return false;
     ClearCollection(Name);
-    exec_sql ("DELETE FROM playlist WHERE title=" + sql_string (Name));
+    m_db.exec_sql ("DELETE FROM playlist WHERE title=" + m_db.sql_string (Name));
     if (isCollectionlist()) clearCache ();
-    return (mysql_affected_rows (m_db) == 1);
+    return (m_db.affected_rows () == 1);
 }
 
 
 void mgSelection::ClearCollection (const string Name)
 {
-    if (!m_db) return;
+    if (!m_db.Connected()) return;
     string listid = id(keyCollection,Name);
-    exec_sql ("DELETE FROM playlistitem WHERE playlist="+sql_string(listid));
+    m_db.exec_sql ("DELETE FROM playlistitem WHERE playlist="+m_db.sql_string(listid));
     if (inCollection(Name)) clearCache ();
 }
 
 
 bool mgSelection::CreateCollection(const string Name)
 {
-    if (!m_db) return false;
-    string name = sql_string(Name);
-    if (exec_count("SELECT count(title) FROM playlist WHERE title = " + name)>0) 
+    if (!m_db.Connected()) return false;
+    string name = m_db.sql_string(Name);
+    if (m_db.exec_count("SELECT count(title) FROM playlist WHERE title = " + name)>0) 
 	return false;
-    exec_sql ("INSERT playlist VALUES(" + name + ",'VDR',NULL,NULL,NULL)");
+    m_db.exec_sql ("INSERT playlist VALUES(" + name + ",'VDR',NULL,NULL,NULL)");
     if (isCollectionlist()) clearCache ();
     return true;
 }
@@ -503,7 +466,7 @@ string mgSelection::ListFilename ()
 const vector < mgContentItem > &
 mgSelection::tracks () const
 {
-    if (!m_db) return m_tracks;
+    if (!m_db.Connected()) return m_tracks;
     if (!m_current_tracks.empty()) return m_tracks;
     mgParts p = order.Parts(m_db,m_level);
     p.fields.clear();
@@ -526,7 +489,7 @@ mgSelection::tracks () const
     	p += order.Key(i)->Parts(m_db,true);
      m_current_tracks = p.sql_select(false); 
      m_tracks.clear ();
-        MYSQL_RES *rows = exec_sql (m_current_tracks);
+        MYSQL_RES *rows = m_db.exec_sql (m_current_tracks);
         if (rows)
         {
         	MYSQL_ROW row;
@@ -541,7 +504,6 @@ mgSelection::tracks () const
 
 
 void mgSelection::InitSelection() {
-        setDB(0);
 	m_Directory=".";
     	m_level = 0;
     	m_position = 0;
@@ -563,7 +525,6 @@ void mgSelection::InitSelection() {
 mgSelection::mgSelection (const bool fall_through)
 {
     InitSelection ();
-    Connect();
     m_fall_through = fall_through;
 }
 
@@ -583,12 +544,6 @@ mgSelection::mgSelection (mgValmap& nv)
 }
 
 void
-mgSelection::setDB(MYSQL *db)
-{
-	m_db = db;
-}
-
-void
 mgSelection::setOrder(mgOrder* o)
 {
 	if (o)
@@ -603,7 +558,6 @@ void
 mgSelection::InitFrom(mgValmap& nv)
 {
 	InitSelection();
-        Connect();
 	m_fall_through = nv.getbool("FallThrough");
     	m_Directory = nv.getstr("Directory");
 	while (m_level < nv.getuint("Level"))
@@ -624,8 +578,6 @@ mgSelection::InitFrom(mgValmap& nv)
 
 mgSelection::~mgSelection ()
 {
-    if (m_db)
-    	mysql_close (m_db);
 }
 
 void mgSelection::InitFrom(const mgSelection* s)
@@ -640,7 +592,6 @@ void mgSelection::InitFrom(const mgSelection* s)
     m_position = s->m_position;
     m_trackid = s->m_trackid;
     m_tracks_position = s->m_tracks_position;
-    Connect();
     setShuffleMode (s->getShuffleMode ());
     setLoopMode (s->getLoopMode ());
 }
@@ -699,7 +650,7 @@ void
 mgSelection::refreshValues ()  const
 {
     assert(this);
-    if (!m_db)
+    if (!m_db.Connected())
 	return;
     if (m_current_values.empty())
     {
@@ -708,7 +659,7 @@ mgSelection::refreshValues ()  const
         values.strings.clear ();
         m_ids.clear ();
 	m_counts.clear();
-        MYSQL_RES *rows = exec_sql (m_current_values);
+        MYSQL_RES *rows = m_db.exec_sql (m_current_values);
         if (rows)
         {
                 unsigned int num_fields = mysql_num_fields(rows);
@@ -744,63 +695,6 @@ mgSelection::count () const
 {
     return values.size ();
 }
-void
-mgSelection::Connect ()
-{
-    if (m_db) 
-    {
-       mysql_close (m_db);
-       setDB(0);
-    }
-    if (the_setup.DbHost == "") return;
-    setDB(mysql_init (0));
-    if (!m_db)
-        return;
-    bool success;
-    if (the_setup.DbSocket != NULL)
-    {
-      mgDebug(1,"Using socket %s for connecting to Database %s as user %s.",
-		      the_setup.DbSocket,
-		      the_setup.DbName,
-		      the_setup.DbUser);
-      mgDebug(3,"DbPassword is: '%s'",the_setup.DbPass);
-      success = (mysql_real_connect( m_db,
-			      "",
-			      the_setup.DbUser,       
-			      the_setup.DbPass, 
-			      the_setup.DbName,
-			      0,
-			      the_setup.DbSocket, 0 ) != 0 );
-    }
-    else
-    {
-      mgDebug(1,"Using TCP-%s for connecting to Database %s as user %s.",
-		      the_setup.DbHost,
-		      the_setup.DbName,
-		      the_setup.DbUser);
-      mgDebug(3,"DbPassword is: '%s'",the_setup.DbPass);
-      success = ( mysql_real_connect( m_db,
-			      the_setup.DbHost, 
-			      the_setup.DbUser,       
-			      the_setup.DbPass, 
-			      the_setup.DbName,
-			      the_setup.DbPort,
-			      0, 0 ) != 0 );
-    }
-    if (!success)
-    {
-	mgWarning("Failed to connect to host '%s' as User '%s', Password '%s': Error: %s",
-			    the_setup.DbHost,the_setup.DbUser,the_setup.DbPass,mysql_error(m_db));
-        mysql_close (m_db);
-	setDB(0);
-    }
-    if (!needGenre2_set && m_db)
-    {
-	needGenre2_set=true;
-	needGenre2=exec_count("SELECT COUNT(DISTINCT genre2) from tracks")>1;
-    }
-    return;
-}
 
 
 bool mgSelection::enter (unsigned int position)
@@ -1117,7 +1011,7 @@ mgSelection::loadvalues (mgKeyTypes kt) const
 	map<string,string>& valmap = map_values[kt];
 	char *b;
 	asprintf(&b,"select %s,%s from %s;",k->map_idfield().c_str(),k->map_valuefield().c_str(),k->map_valuetable().c_str());
-	MYSQL_RES *rows = exec_sql (string(b));
+	MYSQL_RES *rows = m_db.exec_sql (string(b));
 	free(b);
 	if (rows) 
 	{
@@ -1141,7 +1035,6 @@ static vector<int> keycounts;
 unsigned int
 mgSelection::keycount(mgKeyTypes kt)
 {
-	assert(strlen(m_db->host));
 	if (keycounts.size()==0)
 	{
 		for (unsigned int ki=int(mgKeyTypesLow);ki<=int(mgKeyTypesHigh);ki++)
@@ -1154,7 +1047,7 @@ mgSelection::keycount(mgKeyTypes kt)
 	{
 		mgKey* k = ktGenerate(kt);
 		if (k->Enabled(m_db))
-			count = exec_count(k->Parts(m_db,true).sql_count());
+			count = m_db.exec_count(k->Parts(m_db,true).sql_count());
 		else
 			count = 0;
 		delete k;
diff --git a/mg_selection.h b/mg_selection.h
index 4e10644..422040d 100644
--- a/mg_selection.h
+++ b/mg_selection.h
@@ -12,7 +12,6 @@
 #ifndef _MG_SELECTION_H
 #define _MG_SELECTION_H
 #include <stdlib.h>
-#include <mysql/mysql.h>
 #include <string>
 #include <list>
 #include <vector>
@@ -74,9 +73,6 @@ class mgSelection
             LM_FULL                               //!< \brief loop the whole track list
         };
 
-//! \brief escapes special characters
-	string sql_string(const string s) const;
-
 /*! \brief the main constructor
  * \param fall_through if TRUE: If enter() returns a choice
  * containing only one item, that item is automatically entered.
@@ -449,15 +445,13 @@ class mgSelection
         ShuffleMode m_shuffle_mode;
         void Shuffle() const;
         LoopMode m_loop_mode;
-        MYSQL *m_db;
-	void setDB(MYSQL *db);
+        mutable mgmySql m_db;
         unsigned int m_level;
         long m_trackid;
 
         mgOrder order;
 	bool UsedBefore (mgOrder *o,const mgKeyTypes kt, unsigned int level) const;
         void InitSelection ();
-        void Connect ();
 	/*! \brief returns the SQL command for getting all values. 
 	 * For the leaf level, all values are returned. For upper
 	 * levels, every distinct value is returned only once.
@@ -471,19 +465,9 @@ class mgSelection
         string ListFilename ();
         string m_Directory;
         void loadgenres ();
-	MYSQL_RES * exec_sql(string query) const;
-        string get_col0 (string query) const;
 
 	void InitFrom(const mgSelection* s);
 
-/*! \brief executes a query and returns the integer value from
- * the first column in the first row. The query shold be a COUNT query
- * returning only one row.
- * \param query the SQL query to be executed
- */
-        unsigned long exec_count (string query) const;
-
-	
 };