summaryrefslogtreecommitdiff
path: root/mg_db_gd_sqlite.c
diff options
context:
space:
mode:
Diffstat (limited to 'mg_db_gd_sqlite.c')
-rw-r--r--mg_db_gd_sqlite.c372
1 files changed, 372 insertions, 0 deletions
diff --git a/mg_db_gd_sqlite.c b/mg_db_gd_sqlite.c
new file mode 100644
index 0000000..8407b27
--- /dev/null
+++ b/mg_db_gd_sqlite.c
@@ -0,0 +1,372 @@
+/*!
+ * \file mg_db_gd_sqlite.c
+ * \brief A capsule around database access
+ *
+ * \version $Revision: 1.2 $
+ * \date $Date: 2005-04-13 17:42:54 +0100 (Thu, 13 Apr 2005) $
+ * \author Wolfgang Rohdewald
+ * \author Responsible author: $Author: wolfgang61 $
+ */
+
+#include <string>
+#include <sys/types.h>
+#include <sys/stat.h>
+#include <unistd.h>
+#include <errno.h>
+#include <stdio.h>
+#include <sys/time.h>
+#include <time.h>
+#include <assert.h>
+
+
+#include "mg_setup.h"
+#include "mg_item_gd.h"
+#include "mg_db_gd_sqlite.h"
+
+using namespace std;
+
+mgQuerySQLite::mgQuerySQLite(void* db,string sql,mgQueryNoise noise)
+ : mgQueryImp(db,sql,noise)
+{
+ sqlite3* m_db = (sqlite3*)m_db_handle;
+ m_table = 0;
+ char *p;
+ if (!strncmp(m_optsql,"SELECT ",7))
+ m_rc = sqlite3_get_table(m_db,m_optsql,&m_table,&m_rows,&m_columns,&p);
+ else
+ {
+ m_rc = sqlite3_exec(m_db,m_optsql,0,0,&p);
+ if (m_rc==SQLITE_OK)
+ m_rows = sqlite3_changes(m_db);
+ }
+ m_errormessage= (const char*)p;
+ HandleErrors();
+}
+
+mgQuerySQLite::~mgQuerySQLite()
+{
+ sqlite3_free_table(m_table);
+}
+
+char **
+mgQuerySQLite::Next()
+{
+ if (m_cursor>=m_rows)
+ return 0;
+ m_cursor++;
+ return &m_table[m_columns*m_cursor]; // skip header row
+}
+
+mgSQLStringSQLite::~mgSQLStringSQLite()
+{
+ if (m_unquoted)
+ sqlite3_free(m_unquoted);
+}
+
+mgSQLStringSQLite::mgSQLStringSQLite(const char*s)
+{
+ m_unquoted = 0;
+ m_original = s;
+}
+
+char*
+mgSQLStringSQLite::unquoted() const
+{
+ if (!m_unquoted)
+ m_unquoted = sqlite3_mprintf("%q",m_original);
+ return m_unquoted;
+}
+
+const char*
+mgDbGd::Options() const
+{
+ return "";
+}
+
+const char*
+mgDbGd::HelpText() const
+{
+ return "";
+}
+
+mgDb* GenerateDB(bool SeparateThread)
+{
+ // \todo should return different backends according to the_setup.Variant
+ return new mgDbGd(SeparateThread);
+}
+
+
+mgDbGd::mgDbGd(bool SeparateThread)
+{
+ m_db = 0;
+ if (m_separate_thread)
+ if (!Threadsafe())
+ mgError("Your database library is not thread safe");
+}
+
+mgDbGd::~mgDbGd()
+{
+ sqlite3_close (m_db);
+ m_db = 0;
+}
+
+bool
+mgDbGd::Threadsafe()
+{
+ return false;
+}
+
+static char *db_cmds[] =
+{
+ "drop table 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))",
+ "CREATE INDEX idx_album_artist ON album (artist)",
+ "CREATE INDEX idx_album_title ON album (title)",
+ "drop table 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))",
+ "drop table language",
+ "CREATE TABLE language ("
+ "id varchar(4) NOT NULL default '', "
+ "language varchar(40) default NULL, "
+ "freq int(11) default NULL, "
+ "PRIMARY KEY (id))",
+ "drop table musictype;",
+ "CREATE TABLE musictype ("
+ "musictype varchar(40) default NULL, "
+ "id integer PRIMARY KEY autoincrement)",
+ "drop table 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 integer PRIMARY KEY autoincrement)",
+"drop table playlistitem",
+ "CREATE TABLE playlistitem ( "
+ "playlist integer NOT NULL, "
+ "trackid int(11) NOT NULL)",
+ "CREATE INDEX playlistitem_1 on playlistitem (playlist)",
+ "drop table 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))",
+ "drop table source",
+ "CREATE TABLE source ( "
+ "source varchar(40) default NULL, "
+ "id integer PRIMARY KEY autoincrement)",
+ "drop table tracks",
+ "CREATE TABLE tracks ( "
+ "id integer PRIMARY KEY autoincrement, "
+ "artist varchar(255) default NULL, "
+ "title varchar(255) default NULL, "
+ "genre1 varchar(10) default NULL, "
+ "genre2 varchar(10) default NULL, "
+ "year smallint(5) default NULL, "
+ "lang varchar(4) default NULL, "
+ "type tinyint(3) default NULL, "
+ "rating tinyint(3) default NULL, "
+ "length smallint(5) default NULL, "
+ "source tinyint(3) default NULL, "
+ "sourceid varchar(20) default NULL, "
+ "tracknb tinyint(3) default NULL, "
+ "mp3file varchar(255) default NULL, "
+ "condition tinyint(3) 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) default NULL, "
+ "samplerate int(7) default NULL, "
+ "channels tinyint(3) default NULL, "
+ "folder1 varchar(255), "
+ "folder2 varchar(255), "
+ "folder3 varchar(255), "
+ "folder4 varchar(255)); ",
+ "CREATE INDEX tracks_title ON tracks (title)",
+ "CREATE INDEX tracks_sourceid ON tracks (sourceid)",
+ "CREATE INDEX tracks_mp3file ON tracks (mp3file)",
+ "CREATE INDEX tracks_genre1 ON tracks (genre1)",
+ "CREATE INDEX tracks_year ON tracks (year)",
+ "CREATE INDEX tracks_lang ON tracks (lang)",
+ "CREATE INDEX tracks_artist ON tracks (artist)",
+ "CREATE INDEX tracks_rating ON tracks (rating)",
+ "CREATE INDEX tracks_folder1 ON tracks (folder1)",
+ "CREATE INDEX tracks_folder2 ON tracks (folder2)",
+ "CREATE INDEX tracks_folder3 ON tracks (folder3)",
+ "CREATE INDEX tracks_folder4 ON tracks (folder4)",
+};
+
+void
+mgDbGd::StartTransaction()
+{
+ Execute("BEGIN TRANSACTION");
+}
+
+void
+mgDbGd::Commit()
+{
+ Execute("COMMIT");
+}
+
+bool
+mgDbGd::Create()
+{
+ // create database and tables
+ int len = sizeof( db_cmds ) / sizeof( char* );
+ for( int i=0; i < len; i ++ )
+ {
+ mgQuery q(m_db,db_cmds[i],mgQuerySilent);
+ if (!q.ErrorMessage().empty())
+ if (strncmp(db_cmds[i],"drop ",5))
+ {
+ mgWarning("%20s: %s",db_cmds[i],q.ErrorMessage().c_str());
+ return false;
+ }
+ }
+ m_database_found=true;
+ FillTables();
+ return true;
+}
+
+void
+mgDirectory(sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ assert(argc==1);
+ char *buf=strdup((char*)sqlite3_value_text(argv[0]));
+ char *slash=strrchr(buf,'/');
+ if (!slash)
+ slash=buf;
+ *slash=0;
+ sqlite3_result_text(context,buf,strlen(buf),free);
+}
+
+void
+mgDecade(sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ assert(argc==1);
+ unsigned int year=sqlite3_value_int(argv[0]);
+ char *buf;
+ asprintf(&buf,"%02d",(year-year%10)%100);
+ sqlite3_result_text(context,buf,2,free);
+}
+
+void
+mgSubstring(sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ assert(argc==3);
+ char*full=(char*)sqlite3_value_text(argv[0]);
+ unsigned int opos=sqlite3_value_int(argv[1]);
+ unsigned int pos=opos;
+ unsigned int olen=sqlite3_value_int(argv[2]);
+ unsigned int len=olen;
+ if (pos>strlen(full))
+ pos=strlen(full);
+ if (len==0)
+ len=99999;
+ if (len>strlen(full+pos-1))
+ len=strlen(full+pos-1);
+ char *buf=strndup(full+pos-1,len);
+ sqlite3_result_text(context,buf,len,free);
+}
+
+bool
+mgDbGd::Connect ()
+{
+ if (m_database_found)
+ return true;
+ if (time(0)<m_create_time+10)
+ return false;
+ m_create_time=time(0);
+ char *s=sqlite3_mprintf("%s/%s.sqlite",the_setup.DbDatadir,the_setup.DbName);
+ mgDebug(1,"%X opening data base %s",m_db,s);
+ int rc = sqlite3_open(s,&m_db);
+ m_database_found = rc==SQLITE_OK;
+ if (!m_database_found)
+ {
+ mgWarning("Cannot open/create SQLite database %s:%d/%s",
+ s,rc,sqlite3_errmsg(m_db));
+ sqlite3_free(s);
+ return false;
+ }
+ sqlite3_free(s);
+ rc = sqlite3_create_function(m_db,"mgDirectory",1,SQLITE_UTF8,
+ 0,&mgDirectory,0,0);
+ if (rc!=SQLITE_OK)
+ {
+ mgWarning("Cannot define mgDirectory:%d/%s",rc,sqlite3_errmsg);
+ return false;
+ }
+ rc = sqlite3_create_function(m_db,"substring",3,SQLITE_UTF8,
+ 0,&mgSubstring,0,0);
+ if (rc!=SQLITE_OK)
+ {
+ mgWarning("Cannot define mgSubstring:%d/%s",rc,sqlite3_errmsg);
+ return false;
+ }
+ rc = sqlite3_create_function(m_db,"decade",1,SQLITE_UTF8,
+ 0,&mgDecade,0,0);
+ if (rc!=SQLITE_OK)
+ {
+ mgWarning("Cannot define decade:%d/%s",rc,sqlite3_errmsg);
+ return false;
+ }
+ if (!FieldExists("tracks","id"))
+ {
+ extern bool create_question();
+ if (!create_question())
+ return false;
+ if (!Create())
+ return false;
+ extern bool import();
+ import();
+ }
+ return true;
+}
+
+bool
+mgDbGd::NeedGenre2()
+{
+// we do not support genre2 because queries like
+// SELECT title FROM tracks WHERE (genre1='m' or genre2='m')
+// are very slow with sqlite
+ return false;
+}
+
+bool
+mgDbGd::FieldExists(string table, string field)
+{
+ if (!Connect())
+ return false;
+ char *b;
+ asprintf(&b,"SELECT %s FROM %s LIMIT 1",field.c_str(),table.c_str());
+ mgQuery q(m_db,b,mgQuerySilent);
+ free(b);
+ return q.ErrorMessage().empty();
+}
+
+const char*
+mgDbGd::DecadeExpr()
+{
+ return "Decade(tracks.year)";
+}
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-10-18 13:07:17 +0000