diff options
Diffstat (limited to 'server')
| -rw-r--r-- | server/pracro.psql | 60 | ||||
| -rw-r--r-- | server/src/database.cc | 419 |
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 |