chg: write future logs to database

[cassiopeia.git] / src / db / psql.cpp

diff --git a/src/db/psql.cpp b/src/db/psql.cpp
index 41219944f0258d8c257d9e8f16d25d412df2b107..cdbce22393a253c00734008feb4e75dde5742c8a 100644 (file)
--- a/src/db/psql.cpp
+++ b/src/db/psql.cpp
@@ -10,11 +10,21 @@
 PostgresJobProvider::PostgresJobProvider( const std::string& server, const std::string& user, const std::string& password, const std::string& database ):
     c( "dbname=" + database + " host=" + server + " user=" + user + " password=" + password + " client_encoding=UTF-8 application_name=cassiopeia-client" ) {
     // TODO better connection string generation??
+    pqxx::work txn( c );
+    pqxx::result version = txn.exec( "SELECT \"version\" FROM \"schemeVersion\"" );
+
+    if( version.size() != 1 ) {
+        throw std::runtime_error( "Only one version row expected but multiple found." );
+    }
+
+    if( version[0][0].as<int>() < 33 ) {
+        throw std::runtime_error( "Requires at least database schema version 33. Please update gigi before restarting cassiopeia." );
+    }
 }
 
 
 std::shared_ptr<Job> PostgresJobProvider::fetchJob() {
-    std::string q = "SELECT id, \"targetId\", task, \"executeFrom\", \"executeTo\", warning FROM jobs WHERE state='open' AND warning < 3";
+    std::string q = "SELECT id, \"targetId\", task, \"executeFrom\", \"executeTo\", attempt FROM jobs WHERE state='open' AND attempt < 3";
     pqxx::work txn( c );
     pqxx::result result = txn.exec( q );
 
@@ -30,10 +40,9 @@ std::shared_ptr<Job> PostgresJobProvider::fetchJob() {
     job->task = result[0]["task"].as<std::string>();
     job->from = result[0]["\"executeFrom\""].as<std::string>( "" );
     job->to = result[0]["\"executeTo\""].as<std::string>( "" );
-    job->warning = result[0]["warning"].as<std::string>();
-
-    logger::notef( "Got a job: (id=%s, target=%s, task=%s, from=%s, to=%s, warnings=%s)", job->id, job->target, job->task, job->from, job->to, job->warning );
+    job->attempt = result[0]["attempt"].as<std::string>();
 
+    logger::notef( "Got a job: (id=%s, target=%s, task=%s, from=%s, to=%s, attempts=%s)", job->id, job->target, job->task, job->from, job->to, job->attempt );
     return job;
 }
 
@@ -47,26 +56,32 @@ void PostgresJobProvider::finishJob( std::shared_ptr<Job> job ) {
         throw std::runtime_error( "No database entry found." );
     }
 
+    c.prepare( "insertLog", "INSERT INTO \"jobLog\"(\"jobid\", \"attempt\", \"content\") VALUES($1,$2,$3)" );
+    txn.prepared( "insertCrt" )( job->id )( job->attempt )( job->log.str() ).exec();
+
     txn.commit();
 }
 
 void PostgresJobProvider::failJob( std::shared_ptr<Job> job ) {
     pqxx::work txn( c );
 
-    std::string q = "UPDATE jobs SET warning = warning + 1 WHERE id=" + txn.quote( job->id );
+    std::string q = "UPDATE jobs SET attempt = attempt + 1 WHERE id=" + txn.quote( job->id );
     pqxx::result r = txn.exec( q );
 
     if( r.affected_rows() != 1 ) {
         throw std::runtime_error( "No database entry found." );
     }
 
+    c.prepare( "insertLog", "INSERT INTO \"jobLog\"(\"jobid\", \"attempt\", \"content\") VALUES($1,$2,$3)" );
+    txn.prepared( "insertCrt" )( job->id )( job->attempt )( job->log.str() ).exec();
+
     txn.commit();
 }
 
 std::shared_ptr<TBSCertificate> PostgresJobProvider::fetchTBSCert( std::shared_ptr<Job> job ) {
     pqxx::work txn( c );
     auto cert = std::make_shared<TBSCertificate>();
-    std::string q = "SELECT md, profile, csr_name, csr_type, keyname FROM certs INNER JOIN profiles ON profiles.id = certs.profile WHERE certs.id=" + txn.quote( job->target );
+    std::string q = "SELECT md, profile, csr_type, keyname, att.content AS csr FROM certs INNER JOIN profiles ON profiles.id = certs.profile INNER JOIN \"certificateAttachment\" att ON att.certid=certs.id AND att.type='CSR' WHERE certs.id=" + txn.quote( job->target );
     pqxx::result r = txn.exec( q );
 
     if( r.size() != 1 ) {
@@ -86,7 +101,7 @@ std::shared_ptr<TBSCertificate> PostgresJobProvider::fetchTBSCert( std::shared_p
 
     cert->profile = profileId + "-" + profileName;
 
-    cert->csr = ro["csr_name"].as<std::string>();
+    cert->csr_content = ro["csr"].as<std::string>();
     cert->csr_type = ro["csr_type"].as<std::string>();
 
     cert->SANs = std::vector<std::shared_ptr<SAN>>();
@@ -140,7 +155,7 @@ void PostgresJobProvider::writeBack( std::shared_ptr<Job> job, std::shared_ptr<S
         serial = serial.substr( 1 );
     }
 
-    std::string q = "UPDATE certs SET crt_name=" + txn.quote( res->crt_name ) + ", serial=" + txn.quote( serial ) + ", \"caid\" = " + txn.quote( read_id ) + ", created=" + txn.quote( pgTime( res->before ) ) + ", expire=" + txn.quote( pgTime( res->after ) ) + "  WHERE id=" + txn.quote( job->target );
+    std::string q = "UPDATE certs SET serial=" + txn.quote( serial ) + ", \"caid\" = " + txn.quote( read_id ) + ", created=" + txn.quote( pgTime( res->before ) ) + ", expire=" + txn.quote( pgTime( res->after ) ) + "  WHERE id=" + txn.quote( job->target );
     // TODO write more thingies back
 
     r = txn.exec( q );
@@ -149,6 +164,9 @@ void PostgresJobProvider::writeBack( std::shared_ptr<Job> job, std::shared_ptr<S
         throw std::runtime_error( "Only one row should be updated." );
     }
 
+    c.prepare( "insertCrt", "INSERT INTO \"certificateAttachment\"(\"certid\", \"type\", \"content\") VALUES($1,'CRT',$2)" );
+    txn.prepared( "insertCrt" )( job->target )( res->certificate ).exec();
+
     txn.commit();
 }