summaryrefslogtreecommitdiff
path: root/mg_mysql.c
diff options
context:
space:
mode:
Diffstat (limited to 'mg_mysql.c')
-rw-r--r--mg_mysql.c583
1 files changed, 583 insertions, 0 deletions
diff --git a/mg_mysql.c b/mg_mysql.c
new file mode 100644
index 0000000..d7b6476
--- /dev/null
+++ b/mg_mysql.c
@@ -0,0 +1,583 @@
+/*! \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, Wolfgang Rohdewald
+ * \author file owner: $Author: LarsAC $
+ */
+
+#include "mg_mysql.h"
+#include "mg_tools.h"
+
+#include <assert.h>
+#include <stdarg.h>
+#include <sys/stat.h>
+#include <sys/types.h>
+#include <time.h>
+
+#include "vdr_setup.h"
+
+bool needGenre2;
+static bool needGenre2_set;
+
+class mysqlhandle_t {
+ public:
+ mysqlhandle_t();
+ ~mysqlhandle_t();
+};
+
+#ifndef HAVE_SERVER
+static char *datadir;
+
+static char *server_args[] =
+{
+ "muggle",
+ "--datadir=/tmp", // stupid default
+ "--key_buffer_size=32M"
+};
+static char *server_groups[] =
+{
+ "embedded",
+ "server",
+ "muggle_SERVER",
+ 0
+};
+
+void
+set_datadir(char *dir)
+{
+ mgDebug(1,"setting datadir to %s",dir);
+ struct stat stbuf;
+ datadir=strdup(dir);
+ asprintf(&server_args[1],"--datadir=%s",datadir);
+ if (stat(datadir,&stbuf))
+ mkdir(datadir,0755);
+ if (stat(datadir,&stbuf))
+ {
+ mgError("Cannot access datadir %s: errno=%d",datadir,errno);
+ }
+}
+#endif
+
+mysqlhandle_t::mysqlhandle_t()
+{
+#ifndef HAVE_SERVER
+ mgDebug(1,"calling mysql_server_init");
+ if (mysql_server_init(sizeof(server_args) / sizeof(char *),
+ server_args, server_groups))
+ mgDebug(3,"mysql_server_init failed");
+#endif
+}
+
+mysqlhandle_t::~mysqlhandle_t()
+{
+#ifndef HAVE_SERVER
+ mgDebug(3,"calling mysql_server_end");
+ mysql_server_end();
+#endif
+}
+
+static mysqlhandle_t* mysqlhandle;
+
+mgmySql::mgmySql()
+{
+ m_database_found=false;
+ m_hasfolderfields=false;
+ if (!mysqlhandle)
+ mysqlhandle = new mysqlhandle_t;
+ m_db = 0;
+ Connect();
+}
+
+mgmySql::~mgmySql()
+{
+ if (m_db)
+ {
+ mgDebug(3,"%X: closing DB connection",this);
+ mysql_close (m_db);
+ m_db = 0;
+ }
+}
+
+static char *db_cmds[] =
+{
+ "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;"
+};
+
+
+MYSQL_RES*
+mgmySql::exec_sql( string query)
+{
+ if (!m_db || query.empty())
+ return 0;
+ mgDebug(4,"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 ());
+}
+
+struct genres_t {
+ char *id;
+ int id3genre;
+ char *name;
+};
+
+struct lang_t {
+ char *id;
+ char *name;
+};
+
+struct musictypes_t {
+ char *name;
+};
+
+struct sources_t {
+ char *name;
+};
+
+#include "mg_tables.h"
+void mgmySql::FillTables()
+{
+ int len = sizeof( genres ) / sizeof( genres_t );
+ for( int i=0; i < len; i ++ )
+ {
+ char b[600];
+ char id3genre[5];
+ if (genres[i].id3genre>=0)
+ sprintf(id3genre,"%d",genres[i].id3genre);
+ else
+ strcpy(id3genre,"NULL");
+ string genre = sql_string(genres[i].name);
+ sprintf(b,"INSERT INTO genre SET id='%s', id3genre=%s, genre=%s",
+ genres[i].id,id3genre,genre.c_str());
+ exec_sql(b);
+ }
+ len = sizeof( languages ) / sizeof( lang_t );
+ for( int i=0; i < len; i ++ )
+ {
+ char b[600];
+ sprintf(b,"INSERT INTO language SET id='%s', language=",
+ languages[i].id);
+ sql_Cstring(languages[i].name,strchr(b,0));
+ exec_sql(b);
+ }
+ len = sizeof( musictypes ) / sizeof( musictypes_t );
+ for( int i=0; i < len; i ++ )
+ {
+ char b[600];
+ sprintf(b,"INSERT INTO musictype SET musictype='%s'",
+ musictypes[i].name);
+ exec_sql(b);
+ }
+ len = sizeof( sources ) / sizeof( sources_t );
+ for( int i=0; i < len; i ++ )
+ {
+ char b[600];
+ sprintf(b,"INSERT INTO source SET source='%s'",
+ sources[i].name);
+ exec_sql(b);
+ }
+}
+
+time_t createtime;
+
+void mgmySql::Create()
+{
+ createtime=time(0);
+ // create database and tables
+ mgDebug(1,"Dropping and recreating database %s",the_setup.DbName);
+ if (mysql_query(m_db,"DROP DATABASE IF EXISTS GiantDisc;"))
+ {
+ mgWarning("Cannot drop existing database:%s",mysql_error (m_db));
+ return;
+ }
+ if (mysql_query(m_db,"CREATE DATABASE GiantDisc;"))
+ {
+ mgWarning("Cannot create database:%s",mysql_error (m_db));
+ return;
+ }
+#ifdef HAVE_SERVER
+ mysql_query(m_db,"grant all privileges on GiantDisc.* to vdr@localhost;");
+ // ignore error. If we can create the data base, we can do everything
+ // with it anyway.
+#endif
+ mysql_query(m_db,"use GiantDisc;");
+ int len = sizeof( db_cmds ) / sizeof( char* );
+ for( int i=0; i < len; i ++ )
+ {
+ if (mysql_query (m_db, db_cmds[i]))
+ {
+ mgWarning("%20s: %s",db_cmds[i],mysql_error (m_db));
+ mysql_query(m_db, "DROP DATABASE IF EXISTS GiantDisc;"); // clean up
+ return;
+ }
+ }
+ m_database_found=true;
+ Use();
+ FillTables();
+}
+
+string
+mgmySql::sql_string( const string s )
+{
+ char *b = sql_Cstring(s);
+ string result = string( b);
+ free( b);
+ return result;
+}
+
+char*
+mgmySql::sql_Cstring( const string s, char *buf )
+{
+ return sql_Cstring(s.c_str(),buf);
+}
+
+char*
+mgmySql::sql_Cstring( const char *s, char *buf)
+{
+ char *b;
+ if (buf)
+ b=buf;
+ else
+ {
+ int buflen;
+ if (!this)
+ buflen=strlen(s)+2;
+ else
+ buflen=2*strlen(s)+3;
+ b = (char *) malloc( buflen);
+ }
+ b[0]='\'';
+ if (!this)
+ strcpy(b+1,s);
+ else
+ mysql_real_escape_string( m_db, b+1, s, strlen(s) );
+ *(strchr(b,0)+1)=0;
+ *(strchr(b,0))='\'';
+ return b;
+}
+
+bool
+mgmySql::ServerConnected () const
+{
+ return m_db;
+}
+
+bool
+mgmySql::Connected () const
+{
+ return m_database_found;
+}
+
+void
+mgmySql::Connect ()
+{
+ assert(!m_db);
+#ifdef HAVE_SERVER
+ if (the_setup.DbHost == "") return;
+#endif
+ m_db = mysql_init (0);
+ if (!m_db)
+ return;
+#ifdef HAVE_SERVER
+ bool success;
+ if (the_setup.DbSocket != NULL)
+ {
+ mgDebug(1,"Using socket %s for connecting to server as user %s.",
+ the_setup.DbSocket,
+ the_setup.DbUser);
+ mgDebug(3,"DbPassword is: '%s'",the_setup.DbPass);
+ success = (mysql_real_connect( m_db,
+ "",
+ the_setup.DbUser,
+ the_setup.DbPass,
+ 0,
+ 0,
+ the_setup.DbSocket, 0 ) != 0 );
+ }
+ else
+ {
+ mgDebug(1,"Using TCP for connecting to server %s as user %s.",
+ the_setup.DbHost,
+ 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,
+ 0,
+ the_setup.DbPort,
+ 0, 0 ) != 0 );
+ }
+ if (!success)
+ {
+ mgWarning("Failed to connect to server '%s' as User '%s', Password '%s': %s",
+ the_setup.DbHost,the_setup.DbUser,the_setup.DbPass,mysql_error(m_db));
+ mysql_close (m_db);
+ m_db = 0;
+ }
+#else
+ if (!mysql_real_connect(m_db, 0, 0, 0, 0, 0, 0, 0))
+ mgWarning("Failed to connect to embedded mysql in %s:%s ",datadir,mysql_error(m_db));
+ else
+ mgDebug(1,"Connected to embedded mysql in %s",datadir);
+#endif
+ if (m_db)
+ {
+ mysql_query(m_db,"SHOW DATABASES");
+ MYSQL_RES * rows = mysql_store_result(m_db);
+ if (rows)
+ {
+ MYSQL_ROW row;
+ while ((row = mysql_fetch_row (rows)) != 0)
+ if (!strcmp(row[0],the_setup.DbName))
+ {
+ m_database_found=true;
+ break;
+ }
+ mysql_free_result(rows);
+ }
+ if (m_database_found)
+ Use();
+ else
+ if (!createtime)
+ mgWarning("Database %s does not exist",the_setup.DbName);
+ }
+ if (!needGenre2_set && Connected())
+ {
+ needGenre2_set=true;
+ needGenre2=exec_count("SELECT COUNT(DISTINCT genre2) from tracks")>1;
+ }
+ return;
+}
+
+void
+mgmySql::Use()
+{
+ char b[100];
+ sprintf(b,"USE %s;",the_setup.DbName);
+ mysql_query(m_db,b);
+ mgDebug(1,"found database %s",the_setup.DbName);
+}
+
+void
+mgmySql::CreateFolderFields()
+{
+ if (!Connected())
+ return;
+ if (HasFolderFields())
+ return;
+ mysql_query(m_db,"DESCRIBE tracks folder1");
+ MYSQL_RES *rows = mysql_store_result(m_db);
+ if (rows)
+ {
+ m_hasfolderfields = mysql_num_rows(rows)>0;
+ mysql_free_result(rows);
+ if (!m_hasfolderfields)
+ {
+ m_hasfolderfields = !mysql_query(m_db,
+ "alter table tracks add column folder1 varchar(255),"
+ "add column folder2 varchar(255),"
+ "add column folder3 varchar(255),"
+ "add column folder4 varchar(255)");
+
+ }
+ }
+}
+
+void
+database_end()
+{
+ delete mysqlhandle;
+ mysqlhandle=0;
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-14 22:52:37 +0000