summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorbertho <bertho>2009-02-04 14:11:15 +0000
committerbertho <bertho>2009-02-04 14:11:15 +0000
commit3ad20fdd0c8d5c20f0c02e3d1ad2e1e6b0f2a078 (patch)
tree3f192e13a40e7ec6ef899437f98fec8d6c4b459a
parent6ad7301c3a364a34747ecc631a8b239570c303fa (diff)
Cleanup database access a bit and add a proper database schema
-rw-r--r--server/pracro.psql60
-rw-r--r--server/src/database.cc419
2 files changed, 219 insertions, 260 deletions
diff --git a/server/pracro.psql b/server/pracro.psql
new file mode 100644
index 0000000..34283d0
--- /dev/null
+++ b/server/pracro.psql
@@ -0,0 +1,60 @@
+-- vim: syn=sql
+-- As root:
+-- #createuser -P -h localhost -U postgres
+-- #createdb -U postgres -h localhost pracro
+
+-- DROP DATABASE IF EXISTS pracro;
+-- CREATE DATABASE pracro WITH OWNER = pracro ENCODING = 'UNICODE' TABLESPACE = pg_default;
+
+-- DROP SEQUENCE IF EXISTS transeq;
+-- CREATE SEQUENCE transeq;
+
+DROP TABLE IF EXISTS transactions;
+CREATE TABLE transactions
+(
+ -- "uid" bigint PRIMARY KEY,
+ "cpr" varchar(11),
+ "macro" text,
+ "version" text,
+ "timestamp" bigint,
+ "user" text
+) WITH OIDS;
+ALTER TABLE transactions OWNER TO pracro;
+CREATE INDEX x_transactions_cpr_timestamp ON transactions("cpr", "timestamp");
+
+
+DROP TABLE IF EXISTS fieldnames;
+CREATE TABLE fieldnames
+(
+ "name" text PRIMARY KEY,
+ "description" text,
+ "timestamp" bigint
+) WITH OIDS;
+ALTER TABLE fieldnames OWNER TO pracro;
+
+
+DROP TABLE IF EXISTS fields;
+CREATE TABLE fields
+(
+ "transaction" bigint,
+ "name" text,
+ "value" text
+) WITH OIDS;
+ALTER TABLE fields OWNER TO pracro;
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_transactions FOREIGN KEY ("transaction") REFERENCES transactions("uid");
+-- ALTER TABLE fields ADD CONSTRAINT cx_fields_name FOREIGN KEY ("name") REFERENCES fieldnames("name");
+
+
+DROP TABLE IF EXISTS journal;
+CREATE TABLE journal
+(
+ "cpr" varchar(11),
+ "macro" text,
+ "version" text,
+ "timestamp" bigint,
+ "user" text,
+ "journal" text
+) WITH OIDS;
+ALTER TABLE journal OWNER TO pracro;
+CREATE INDEX x_journal_cpr_timestamp ON journal("cpr", "timestamp");
+
diff --git a/server/src/database.cc b/server/src/database.cc
index dbc5849..ec1e736 100644
--- a/server/src/database.cc
+++ b/server/src/database.cc
@@ -27,30 +27,8 @@
#include "database.h"
#include <config.h>
+#include <stdlib.h>
-std::string protect(std::string in)
-{
- std::string out;
-
- for(size_t i = 0; i < in.size(); i++) {
- switch(in[i]) {
- case '\'':
- case '\\':
- out.append(2, in[i]);
- break;
-
- case '\0':
- out.append(1, '0');
- break;
-
- default:
- out.append(1, in[i]);
- break;
- }
- }
-
- return out;
-}
Database::Database(std::string hostname, std::string user, std::string password)
#ifndef WITHOUT_DB
@@ -76,88 +54,70 @@ void Database::commit(std::string user,
std::stringstream timestamp; timestamp << now;
#ifndef WITHOUT_DB
- pqxx::work W(c);
+ std::string ts;
+ try {
+ pqxx::work W(c);
+ ts = "INSERT INTO transactions VALUES ("
+ " '" + W.esc(cpr) + "', "
+ " '" + W.esc(macro) + "', "
+ " '" + W.esc(version) + "', "
+ " '" + W.esc(timestamp.str()) + "', "
+ " '" + W.esc(user) + "' "
+ ")"
+ ;
+ pqxx::result R = W.exec(ts);
+ std::stringstream oid;
+ oid << R.inserted_oid();
+
+ if(fields.size() > 0) {
+ // field table lookup
+ ts = "SELECT name FROM fieldnames WHERE name IN ( ";
+ std::map< std::string, std::string >::iterator i = fields.begin();
+ ts += "'" + W.esc(i->first) + "'";
+ i++;
+ while(i != fields.end()) {
+ ts += ", '" + W.esc(i->first) + "'";
+ i++;
+ }
+ ts += ")";
+ R = W.exec(ts);
+#ifdef WITH_DEBUG
+ printf("Database::commit: input fields: %d, output fields: %lu\n", fields.size(), R.size());
#endif/*WITHOUT_DB*/
- std::string ts =
- "INSERT INTO transactions"
- " VALUES('"+protect(cpr)+"', '"+protect(macro)+"', '"+protect(version)+
- "', '"+protect(timestamp.str())+"', '"+protect(user)+"')";
-
- std::stringstream oid;
-
-#ifndef WITHOUT_DB
- pqxx::result R = W.exec(ts);
- oid << R.inserted_oid();
-#else
- oid << "###GENERATED_OID###";
+ pqxx::result::const_iterator ri = R.begin();
+ // Store known fields
+ while(ri != R.end()) {
+ pqxx::result::tuple t = *ri;
+ std::string name = t[0].c_str();
+#ifdef WITH_DEBUG
+ printf("Storing: %s with value %s\n", name.c_str(), fields[name].c_str());
#endif/*WITHOUT_DB*/
+ ts = "INSERT INTO fields VALUES ("
+ " '" + W.esc(oid.str()) + "', "
+ " '" + W.esc(name) + "', "
+ " '" + W.esc(fields[name]) + "'"
+ ")"
+ ;
+ W.exec(ts);
+ ri++;
+ }
+ }
+ W.commit();
+ } catch(std::exception &e) {
+ printf("Query failed: %s: %s\n", e.what(), ts.c_str());
+ }
+#else
#ifdef WITH_DEBUG
- printf("%s\n", ts.c_str());
-#endif/*WITH_DEBUG*/
-
- //
- // FIELD TABLE LOOKUP
- //
- std::stringstream query;
- query << "SELECT name";
- query << " FROM fieldnames";
std::map< std::string, std::string >::iterator i = fields.begin();
while(i != fields.end()) {
- if(i == fields.begin()) query << " WHERE name = '" << protect(i->first) << "'";
- else query << " OR name = '" << protect(i->first) << "'";
+ printf("Storing field '%s': '%s'\n", i->first, i->second);
i++;
}
-#ifdef WITH_DEBUG
- printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
- R = W.exec(query.str());
-
- pqxx::result::const_iterator ri = R.begin();
- while(ri != R.end()) {
- pqxx::result::tuple t = *ri;
- std::string name = t[0].c_str();
-
- printf("Storing: %s with value %s\n", name.c_str(), fields[name].c_str());
- std::string fs =
- "INSERT INTO fields"
- " VALUES('"+protect(oid.str())+"', '"+protect(name)+"', '"+protect(fields[name])+"')";
-
-#ifndef WITHOUT_DB
- W.exec(fs);
-#endif/*WITHOUT_DB*/
-
-#ifdef WITH_DEBUG
- printf("%s\n", fs.c_str());
#endif/*WITH_DEBUG*/
-
-
- ri++;
- }
#endif/*WITHOUT_DB*/
- //
- // end of FIELD TABLE LOOKUP
- //
-
-#ifndef WITHOUT_DB
- W.commit();
-#endif/*WITHOUT_DB*/
-
-#if 0
- try {
- pqxx::work W(c);
-
- // ...
-
- W.commit();
- } catch(const std::exception &e) {
- // throw PostgreSQLException(e.what());
- }
-#endif/*0*/
}
void Database::putResume(std::string user,
@@ -172,27 +132,34 @@ void Database::putResume(std::string user,
std::stringstream timestamp; timestamp << now;
#ifndef WITHOUT_DB
- pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
- std::string ts =
- "INSERT INTO journal"
- " VALUES('"+protect(cpr)+"', '"+protect(macro)+"', '"+protect(version)+
- "', '"+protect(timestamp.str())+"', '"+protect(user)+"', '"+protect(resume)+"')";
-
- std::stringstream oid;
-
-#ifndef WITHOUT_DB
- pqxx::result R = W.exec(ts);
-#endif/*WITHOUT_DB*/
-
+ std::string ts;
+ try {
+ pqxx::work W(c);
+ ts = "INSERT INTO journal VALUES ("
+ "'" + W.esc(cpr) + "', "
+ "'" + W.esc(macro) + "', "
+ "'" + W.esc(version) + "', "
+ "'" + W.esc(timestamp.str()) + "', "
+ "'" + W.esc(user) + "', "
+ "'" + W.esc(resume)+"'"
+ ")";
+ pqxx::result R = W.exec(ts);
+ W.commit();
+ } catch(std::exception &e) {
+ printf("Query failed: %s: %s\n", e.what(), ts.c_str());
+ }
+#else
#ifdef WITH_DEBUG
- printf("%s\n", ts.c_str());
+ printf("INSERT INTO journal VALUES ('%s', '%s', '%s', '%s', '%s', '%s')\n",
+ cpr.c_str(),
+ macro.c_str(),
+ version.c_str(),
+ timestamp.str().c_str(),
+ user.c_str(),
+ resume.c_str()
+ );
#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
- W.commit();
-#endif/*WITHOUT_DB*/
+#endif
}
@@ -203,29 +170,30 @@ std::string Database::getResume(std::string cpr,
std::string resume;
#ifndef WITHOUT_DB
- pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
- std::stringstream query;
- query << "SELECT journal";
- query << " FROM journal";
- query << " WHERE cpr = '" << protect(cpr) << "'";
- query << " AND macro = '" << protect(macro) << "'";
- query << " ORDER BY timestamp;";
-
+ std::string query;
+ try {
+ pqxx::work W(c);
+ /* FIXME: argument 'oldest' is not used anywhere */
+ query = "SELECT journal FROM journal"
+ " WHERE cpr = '" + W.esc(cpr) + "'"
+ " AND macro = '" + W.esc(macro) + "'"
+ " ORDER BY timestamp"
+ ;
+ pqxx::result R = W.exec(query);
+ pqxx::result::const_iterator ri = R.begin();
+ /* FIXME: This will only get the *last* entry of the journal in var resume due to '=' and not '+=' */
+ while(ri != R.end()) {
+ pqxx::result::tuple t = *ri;
+ resume = t[0].c_str();
+ ri++;
+ }
+ } catch (std::exception &e) {
+ printf("Query failed: %s: %s\n", e.what(), query.c_str());
+ }
+#else
#ifdef WITH_DEBUG
- printf("%s\n", query.str().c_str());
+ printf("SELECT journal FROM journal WHERE cpr = '%s' AND macro = '%s' ORDER BY timestamp", cpr.c_str(), macro.c_str());
#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
- pqxx::result R = W.exec(query.str());
-
- pqxx::result::const_iterator ri = R.begin();
- while(ri != R.end()) {
- pqxx::result::tuple t = *ri;
- resume = t[0].c_str();
- ri++;
- }
#endif/*WITHOUT_DB*/
return resume;
@@ -237,54 +205,49 @@ Values Database::getValues(std::string cpr,
time_t oldest)
{
Values values;
-#ifndef WITHOUT_DB
- pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
- std::stringstream query;
- query << "SELECT fields.name, fields.value, transactions.timestamp";
- query << " FROM fields, transactions";
- query << " WHERE transactions.cpr = '" << protect(cpr) << "'";
- query << " AND transactions.oid = fields.transaction";
- query << " AND transactions.timestamp >= " << oldest;
-
- std::vector< std::string >::iterator i = fields.begin();
- bool first = true;
- while(i != fields.end()) {
- if(first) query << " AND ( fields.name = '" << protect(*i) << "'";
- else query << " OR fields.name = '" << protect(*i) << "'";
- first = false;
- i++;
- }
- if(fields.size() > 0) query << ")";
-
- query << " ORDER BY transactions.timestamp";
-
-#ifdef WITH_DEBUG
- printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
#ifndef WITHOUT_DB
- pqxx::result R = W.exec(query.str());
-
- pqxx::result::const_iterator ri = R.begin();
- while(ri != R.end()) {
- pqxx::result::tuple t = *ri;
-
- Value v;
- v.value = t[1].c_str();
- v.timestamp = atol(t[2].c_str());
-
- if(values.find(t[0].c_str()) == values.end() ||
- (values.find(t[0].c_str()) != values.end() &&
- values[t[0].c_str()].timestamp <= v.timestamp) ) {
- values[t[0].c_str()] = v;
+ std::string query;
+ std::stringstream soldest; soldest << oldest;
+ try {
+ pqxx::work W(c);
+ query = "SELECT f.name, f.value, t.timestamp FROM fields f JOIN transactions t ON t.oid = f.transaction "
+ " WHERE t.cpr = '" + W.esc(cpr) + "' "
+ " AND t.timestamp >= " + soldest.str();
+ if(fields.size() > 0) {
+ std::vector< std::string >::iterator i = fields.begin();
+ query += " AND f.name IN ('" + W.esc(*i) + "'";
+ i++;
+ while(i != fields.end()) {
+ query += ", '" + W.esc(*i) + "'";
+ i++;
+ }
+ query += ')';
+ }
+ query += " ORDER BY t.timestamp ";
+ pqxx::result R = W.exec(query);
+ pqxx::result::const_iterator ri = R.begin();
+ while(ri != R.end()) {
+ pqxx::result::tuple t = *ri;
+ Value v;
+ v.value = t[1].c_str();
+ v.timestamp = atol(t[2].c_str());
+ if(values.find(t[0].c_str()) == values.end() ||
+ (values.find(t[0].c_str()) != values.end() &&
+ values[t[0].c_str()].timestamp <= v.timestamp) ) {
+ values[t[0].c_str()] = v;
+ }
+ ri++;
}
-
- ri++;
+ } catch (std::exception &e) {
+ printf("Query failed: %s: %s\n", e.what(), query.c_str());
}
+#else
+#ifdef WITH_DEBUG
+ printf("getValues(%s, <fields...>, %ld) -- not implemented without database...\n", cpr.c_str(), oldest);
+#endif/*WITH_DEBUG*/
#endif/*WITHOUT_DB*/
-
+
return values;
}
@@ -293,97 +256,33 @@ bool Database::checkMacro(std::string cpr,
time_t oldest)
{
#ifndef WITHOUT_DB
- pqxx::work W(c);
-#endif/*WITHOUT_DB*/
-
- std::stringstream query;
- query << "SELECT oid";
- query << " FROM transactions";
- query << " WHERE cpr = '" << protect(cpr) << "'";
- query << " AND macro = '" << protect(macro) << "'";
- query << " AND timestamp >= " << oldest;
- query << " ORDER BY timestamp";
-
-#ifdef WITH_DEBUG
- printf("%s\n", query.str().c_str());
-#endif/*WITH_DEBUG*/
-
-#ifndef WITHOUT_DB
+ std::string query;
+ std::stringstream soldest; soldest << oldest;
try {
- pqxx::result R = W.exec(query.str());
-
+ pqxx::work W(c);
+ query = "SELECT oid FROM transactions"
+ " WHERE cpr = '" + W.esc(cpr) + "'"
+ " AND macro = '" + W.esc(macro) + "'"
+ " AND timestamp >= " + soldest.str() + ""
+ " ORDER BY timestamp"
+ ;
+ pqxx::result R = W.exec(query);
return R.size() != 0;
- } catch( ... ) {
- return false;
+ } catch(std::exception &e) {
+ printf("Query failed: %s: %s\n", e.what(), query.c_str());
}
#else
- return false;
+#ifdef WITH_DEBUG
+ printf("SELECT oid FROM transactions WHERE cpr = '%s' AND macro = '%s' AND timestamp >= %ld ORDER BY timestamp\n",
+ cpr.c_str(), macro.c_str(), oldest);
+#endif/*WITH_DEBUG*/
#endif/*WITHOUT_DB*/
+
+ return false;
}
-/*
--- As root:
--- #createuser -P -h localhost -U postgres
--- #createdb -U postgres -h localhost pracro
-
-DROP DATABASE pracro;
-
-CREATE DATABASE pracro
- WITH OWNER = pracro
- ENCODING = 'UNICODE'
- TABLESPACE = pg_default;
-
-DROP TABLE transactions;
-
-CREATE TABLE transactions
-(
- "cpr" varchar(11),
- "macro" text,
- "version" text,
- "timestamp" bigint,
- "user" text
-)
-WITH OIDS;
-ALTER TABLE transactions OWNER TO pracro;
-
-DROP TABLE fields;
-
-CREATE TABLE fields
-(
- "transaction" oid,
- "name" text,
- "value" text
-)
-WITH OIDS;
-ALTER TABLE fields OWNER TO pracro;
-
-DROP TABLE journal;
-
-CREATE TABLE journal
-(
- cpr character varying(11),
- macro text,
- "version" text,
- "timestamp" bigint,
- "user" text,
- journal text
-)
-WITH OIDS;
-ALTER TABLE journal OWNER TO pracro;
-
-DROP TABLE fieldnames;
-
-CREATE TABLE fieldnames
-(
- name text,
- description text,
- "timestamp" bigint
-)
-WITH OIDS;
-ALTER TABLE fieldnames OWNER TO pracro;
-
--- primary key(oid) ??
-*/
+
+
#ifdef TEST_DATABASE