summaryrefslogtreecommitdiff
path: root/mg_db_gd_pg.c
diff options
context:
space:
mode:
Diffstat (limited to 'mg_db_gd_pg.c')
-rw-r--r--mg_db_gd_pg.c404
1 files changed, 187 insertions, 217 deletions
diff --git a/mg_db_gd_pg.c b/mg_db_gd_pg.c
index ea63816..b51b1c8 100644
--- a/mg_db_gd_pg.c
+++ b/mg_db_gd_pg.c
@@ -1,4 +1,4 @@
-/*!
+/*!
* \file mg_db_gd_pg.c
* \brief A capsule around postgresql database access
*
@@ -18,7 +18,6 @@
#include <time.h>
#include <assert.h>
-
#include "mg_setup.h"
#include "mg_item_gd.h"
#include "mg_db_gd_pg.h"
@@ -29,35 +28,32 @@
using namespace std;
mgQueryPG::mgQueryPG(void* db,string sql,mgQueryNoise noise)
- : mgQueryImp(db,sql,noise)
-{
+: mgQueryImp(db,sql,noise) {
m_db = (PGconn*)m_db_handle;
m_cursor = 0;
m_table = PQexec(m_db,m_optsql);
switch ((m_rc=PQresultStatus(m_table))) {
case PGRES_COMMAND_OK:
- m_rows = atol(PQcmdTuples(m_table));
+ m_rows = atol(PQcmdTuples(m_table));
break;;
case PGRES_TUPLES_OK:
- m_rows = PQntuples(m_table);
+ m_rows = PQntuples(m_table);
m_columns = PQnfields(m_table);
break;
- default:
+ default:
m_errormessage = PQresultErrorMessage (m_table);
break;
}
HandleErrors();
}
-mgQueryPG::~mgQueryPG()
-{
+mgQueryPG::~mgQueryPG() {
PQclear(m_table);
}
char **
-mgQueryPG::Next()
-{
- if (m_cursor>=Rows())
+mgQueryPG::Next() {
+ if (m_cursor>=Rows())
return 0;
assert(Columns()<100);
memset(m_rowpointers,0,sizeof(m_rowpointers));
@@ -68,26 +64,23 @@ mgQueryPG::Next()
return m_rowpointers;
}
-mgSQLStringPG::~mgSQLStringPG()
-{
+mgSQLStringPG::~mgSQLStringPG() {
if (m_unquoted)
free(m_unquoted);
}
-mgSQLStringPG::mgSQLStringPG(const char*s)
-{
+mgSQLStringPG::mgSQLStringPG(const char*s) {
m_unquoted = 0;
m_original = s;
}
-char*
+char*
mgSQLStringPG::unquoted() const
{
- if (!m_unquoted)
- {
- int buflen=2*strlen(m_original)+5;
- m_unquoted = (char *) malloc( buflen);
- PQescapeString(m_unquoted,m_original,strlen(m_original));
+ if (!m_unquoted) {
+ int buflen=2*strlen(m_original)+5;
+ m_unquoted = (char *) malloc( buflen);
+ PQescapeString(m_unquoted,m_original,strlen(m_original));
}
return m_unquoted;
}
@@ -101,19 +94,18 @@ mgDbGd::Options() const
const char*
mgDbGd::HelpText() const
{
- return
- " -h HHHH, --host=HHHH specify database host (default is localhost)\n"
- " -s SSSS --socket=PATH specify database socket\n"
- " -p PPPP, --port=PPPP specify port of database server\n"
- " -u UUUU, --user=UUUU specify database user (default is current login name)\n"
- " -w WWWW, --password=WWWW specify database password (default is empty)\n"
- "\n"
- " if the database runs on the same computer you should only need -u and -w if\n"
- " at all, see README.postgresql\n";
+ return
+ " -h HHHH, --host=HHHH specify database host (default is localhost)\n"
+ " -s SSSS --socket=PATH specify database socket\n"
+ " -p PPPP, --port=PPPP specify port of database server\n"
+ " -u UUUU, --user=UUUU specify database user (default is current login name)\n"
+ " -w WWWW, --password=WWWW specify database password (default is empty)\n"
+ "\n"
+ " if the database runs on the same computer you should only need -u and -w if\n"
+ " at all, see README.postgresql\n";
}
-mgDb* GenerateDB(bool SeparateThread)
-{
+mgDb* GenerateDB(bool SeparateThread) {
// \todo should return different backends according to the_setup.Variant
return new mgDbGd(SeparateThread);
}
@@ -121,241 +113,219 @@ mgDb* GenerateDB(bool SeparateThread)
static bool needGenre2;
static bool needGenre2_set=false;
-mgDbGd::mgDbGd(bool SeparateThread)
-{
- m_db = 0;
+mgDbGd::mgDbGd(bool SeparateThread) {
+ m_db = 0;
if (m_separate_thread)
if (!Threadsafe())
mgError("Your database library is not thread safe");
}
-mgDbGd::~mgDbGd()
-{
- PQfinish (m_db);
- m_db = 0;
+mgDbGd::~mgDbGd() {
+ PQfinish (m_db);
+ m_db = 0;
}
bool
-mgDbGd::Threadsafe()
-{
+mgDbGd::Threadsafe() {
return ENABLE_THREAD_SAFETY;
}
-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) default '', "
- "coverimg varchar(255) default NULL, "
- "covertxt text, "
- "modified date default NULL, "
- "genre varchar(10) default NULL, "
- "PRIMARY KEY (cddbid))",
- "CREATE INDEX alb_artist ON album (artist)",
- "CREATE INDEX alb_title ON album (title)",
- "DROP TABLE IF EXISTS genre",
- "CREATE TABLE genre ("
- "id varchar(10) NOT NULL default '', "
- "id3genre smallint default NULL, "
- "genre varchar(255) default NULL, "
- "freq int default NULL, "
- "PRIMARY KEY (id))",
- "DROP TABLE IF EXISTS language",
- "CREATE TABLE language ("
- "id varchar(4) NOT NULL default '', "
- "language varchar(40) default NULL, "
- "freq int default NULL, "
- "PRIMARY KEY (id))",
- "DROP TABLE IF EXISTS musictype",
- "CREATE TABLE musictype ("
- "musictype varchar(40) default NULL, "
- "id serial, "
- "PRIMARY KEY (id)) ",
- "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 default NULL, "
- "id serial, "
- "PRIMARY KEY (id))",
- "DROP TABLE IF EXISTS playlistitem",
- "CREATE TABLE playlistitem ( "
- "playlist int NOT NULL,"
- "trackid int NOT NULL) WITH OIDS",
- "DROP TABLE IF EXISTS source",
- "CREATE TABLE source ( "
- "source varchar(40) default NULL, "
- "id serial, "
- "PRIMARY KEY (id)) ",
- "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 default NULL, "
- "lang varchar(4) default NULL, "
- "type smallint default NULL, "
- "rating smallint default NULL, "
- "length smallint default NULL, "
- "source smallint default NULL, "
- "sourceid varchar(20) default NULL, "
- "tracknb smallint default NULL, "
- "mp3file varchar(255) default NULL, "
- "quality smallint default NULL, "
- "voladjust smallint default '0', "
- "lengthfrm int default '0', "
- "startfrm int default '0', "
- "bpm smallint default '0', "
- "lyrics text, "
- "bitrate varchar(10) default NULL, "
- "created date default NULL, "
- "modified date default NULL, "
- "backup smallint default NULL, "
- "samplerate int default NULL, "
- "channels smallint default NULL, "
- "id serial, "
- "folder1 varchar(255), "
- "folder2 varchar(255), "
- "folder3 varchar(255), "
- "folder4 varchar(255), "
- "PRIMARY KEY (id))",
- "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_genre2 ON tracks (genre2)",
- "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)",
+static const char *db_cmds[] = {
+ "DROP TABLE IF EXISTS album",
+ "CREATE TABLE album ( "
+ "artist varchar(255) default NULL, "
+ "title varchar(255) default NULL, "
+ "cddbid varchar(20) default '', "
+ "coverimg varchar(255) default NULL, "
+ "covertxt text, "
+ "modified date default NULL, "
+ "genre varchar(10) default NULL, "
+ "PRIMARY KEY (cddbid))",
+ "CREATE INDEX alb_artist ON album (artist)",
+ "CREATE INDEX alb_title ON album (title)",
+ "DROP TABLE IF EXISTS genre",
+ "CREATE TABLE genre ("
+ "id varchar(10) NOT NULL default '', "
+ "id3genre smallint default NULL, "
+ "genre varchar(255) default NULL, "
+ "freq int default NULL, "
+ "PRIMARY KEY (id))",
+ "DROP TABLE IF EXISTS language",
+ "CREATE TABLE language ("
+ "id varchar(4) NOT NULL default '', "
+ "language varchar(40) default NULL, "
+ "freq int default NULL, "
+ "PRIMARY KEY (id))",
+ "DROP TABLE IF EXISTS musictype",
+ "CREATE TABLE musictype ("
+ "musictype varchar(40) default NULL, "
+ "id serial, "
+ "PRIMARY KEY (id)) ",
+ "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 default NULL, "
+ "id serial, "
+ "PRIMARY KEY (id))",
+ "DROP TABLE IF EXISTS playlistitem",
+ "CREATE TABLE playlistitem ( "
+ "playlist int NOT NULL,"
+ "trackid int NOT NULL) WITH OIDS",
+ "DROP TABLE IF EXISTS source",
+ "CREATE TABLE source ( "
+ "source varchar(40) default NULL, "
+ "id serial, "
+ "PRIMARY KEY (id)) ",
+ "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 default NULL, "
+ "lang varchar(4) default NULL, "
+ "type smallint default NULL, "
+ "rating smallint default NULL, "
+ "length smallint default NULL, "
+ "source smallint default NULL, "
+ "sourceid varchar(20) default NULL, "
+ "tracknb smallint default NULL, "
+ "mp3file varchar(255) default NULL, "
+ "quality smallint default NULL, "
+ "voladjust smallint default '0', "
+ "lengthfrm int default '0', "
+ "startfrm int default '0', "
+ "bpm smallint default '0', "
+ "lyrics text, "
+ "bitrate varchar(10) default NULL, "
+ "created date default NULL, "
+ "modified date default NULL, "
+ "backup smallint default NULL, "
+ "samplerate int default NULL, "
+ "channels smallint default NULL, "
+ "id serial, "
+ "folder1 varchar(255), "
+ "folder2 varchar(255), "
+ "folder3 varchar(255), "
+ "folder4 varchar(255), "
+ "PRIMARY KEY (id))",
+ "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_genre2 ON tracks (genre2)",
+ "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()
-{
+mgDbGd::StartTransaction() {
Execute("BEGIN TRANSACTION");
}
void
-mgDbGd::Commit()
-{
+mgDbGd::Commit() {
Execute("COMMIT");
}
-
bool
-mgDbGd::SetCharset()
-{
- const char *cmd;
- if (the_setup.utf8)
+mgDbGd::SetCharset() {
+ const char *cmd;
+ if (the_setup.utf8)
cmd="SET NAMES 'UTF8'";
else
cmd="SET NAMES 'LATIN1'";
- mgQuery q0(m_db,cmd);
- if (!q0.ErrorMessage().empty())
+ mgQuery q0(m_db,cmd);
+ if (!q0.ErrorMessage().empty())
return false;
return true;
}
bool
-mgDbGd::Creatable()
-{
- return false;
+mgDbGd::Creatable() {
+ return false;
}
bool
-mgDbGd::Create()
-{
- return false;
+mgDbGd::Create() {
+ return false;
}
bool
-mgDbGd::Clear()
-{
- // 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],mgQueryWarnOnly);
- if (!q.ErrorMessage().empty())
- return false;
- }
- return true;
+mgDbGd::Clear() {
+ // 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],mgQueryWarnOnly);
+ if (!q.ErrorMessage().empty())
+ return false;
+ }
+ return true;
}
bool
-mgDbGd::ServerConnect ()
-{
- return true;
+mgDbGd::ServerConnect () {
+ return true;
}
-
bool
-mgDbGd::ConnectDatabase ()
-{
- char conninfo[500];
- char port[20];
- char host[200];
- char *user;
- if (the_setup.DbPort>0)
- sprintf(port," port = %d ",the_setup.DbPort);
- else
- port[0]=0;
- if (notempty(the_setup.DbHost))
- snprintf(host,199," host = %s ",the_setup.DbHost);
- else if (notempty(the_setup.DbSocket))
- snprintf(host,199," host = %s ",the_setup.DbSocket);
- else
- host[0]=0;
- if (the_setup.DbUser==0)
- user=getenv("LOGNAME");
- else
- user=the_setup.DbUser;
- snprintf(conninfo,499,"%s %s dbname = %s user = %s ",
- host,port,the_setup.DbName,user);
- m_db = PQconnectdb(conninfo);
- if (PQstatus(m_db) != CONNECTION_OK)
- {
- mgWarning("Failed to connect to postgres server using %s:%s",conninfo,PQerrorMessage(m_db));
- return false;
- }
- return SetCharset();
+mgDbGd::ConnectDatabase () {
+ char conninfo[500];
+ char port[20];
+ char host[200];
+ char *user;
+ if (the_setup.DbPort>0)
+ sprintf(port," port = %d ",the_setup.DbPort);
+ else
+ port[0]=0;
+ if (notempty(the_setup.DbHost))
+ snprintf(host,199," host = %s ",the_setup.DbHost);
+ else if (notempty(the_setup.DbSocket))
+ snprintf(host,199," host = %s ",the_setup.DbSocket);
+ else
+ host[0]=0;
+ if (the_setup.DbUser==0)
+ user=getenv("LOGNAME");
+ else
+ user=the_setup.DbUser;
+ snprintf(conninfo,499,"%s %s dbname = %s user = %s ",
+ host,port,the_setup.DbName,user);
+ m_db = PQconnectdb(conninfo);
+ if (PQstatus(m_db) != CONNECTION_OK) {
+ mgWarning("Failed to connect to postgres server using %s:%s",conninfo,PQerrorMessage(m_db));
+ return false;
+ }
+ return SetCharset();
}
-bool
-mgDbGd::NeedGenre2()
-{
- if (!needGenre2_set && Connect())
- {
- needGenre2_set=true;
- needGenre2=exec_count("SELECT COUNT(DISTINCT genre2) FROM tracks")>1;
- }
- return needGenre2;
+bool
+mgDbGd::NeedGenre2() {
+ if (!needGenre2_set && Connect()) {
+ needGenre2_set=true;
+ needGenre2=exec_count("SELECT COUNT(DISTINCT genre2) FROM tracks")>1;
+ }
+ return needGenre2;
}
-
bool
-mgDbGd::FieldExists(string table, string field)
-{
- char *b;
- msprintf(&b,"SELECT COUNT(*) FROM information_schema.columns WHERE table_name='%s' AND column_name='%s'",
+mgDbGd::FieldExists(string table, string field) {
+ char *b;
+ msprintf(&b,"SELECT COUNT(*) FROM information_schema.columns WHERE table_name='%s' AND column_name='%s'",
table.c_str(),field.c_str());
- bool result = exec_count(b)==1;
+ bool result = exec_count(b)==1;
free(b);
return result;
}
const char*
-mgDbGd::DecadeExpr()
-{
- return "substring(10 * floor(tracks.year/10),3)";
+mgDbGd::DecadeExpr() {
+ return "substring(cast(10 * floor(tracks.year/10) as char(4)),3)";
}
-
generated by cgit v1.2.3 (git 2.25.1) at 2026-05-12 21:32:06 +0000