From 3e192fd092ecbc8725a02afc9b6fb90934b258fb Mon Sep 17 00:00:00 2001
From: =?utf8?q?Felix=20D=C3=B6rre?= <felix@dogcraft.de>
Date: Sun, 5 Nov 2017 15:33:34 +0100
Subject: [PATCH] chg: make cassiopeia conform to db schema version 33

Change-Id: I3d8661bb1e009f9c9d2c9d66bd627c9c43adb7f3
---
 src/apps/client.cpp | 11 -----------
 src/db/database.h   |  5 -----
 src/db/psql.cpp     | 19 ++++++++++++++++---
 3 files changed, 16 insertions(+), 19 deletions(-)

diff --git a/src/apps/client.cpp b/src/apps/client.cpp
index e26ebbb..4f2d81a 100644
--- a/src/apps/client.cpp
+++ b/src/apps/client.cpp
@@ -245,8 +245,6 @@ int main( int argc, const char *argv[] ) {
                         logger::notef( "INFO: AVA %s: %s", AVA->name, AVA->value );
                     }
 
-                    logger::notef( "FINE: Found the CSR at '%s'", cert->csr );
-                    cert->csr_content = readFile( keyDir + "/../" + cert->csr );
                     logger::note( "FINE: CSR content:\n", cert->csr_content );
 
                     std::shared_ptr<SignedCertificate> res = sign->sign( cert );
@@ -260,15 +258,6 @@ int main( int argc, const char *argv[] ) {
                     logger::note( "FINE: CERTIFICATE LOG:\n", res->log,
                                   "FINE: CERTIFICATE:\n", res->certificate );
 
-                    std::string fn = writeBackFile( job->target.c_str(), res->certificate, keyDir );
-
-                    if( fn.empty() ) {
-                        logger::error( "ERROR: Writeback of the certificate failed." );
-                        jp->failJob( job );
-                        continue;
-                    }
-
-                    res->crt_name = fn;
                     jp->writeBack( job, res ); //! \FIXME: Check return value
                     logger::note( "FINE: signing done." );
 
diff --git a/src/db/database.h b/src/db/database.h
index fb3b093..e457196 100644
--- a/src/db/database.h
+++ b/src/db/database.h
@@ -27,10 +27,6 @@ struct TBSCertificate {
     std::string md;
     std::string profile;
 
-    /**
-     * CSR path
-     */
-    std::string csr;
     std::string csr_type;
     std::string csr_content;
     std::vector<std::shared_ptr<SAN>> SANs;
@@ -49,7 +45,6 @@ struct SignedCertificate {
     std::string after;
     std::string pkHash;
     std::string certHash;
-    std::string crt_name;
     std::string log;
     std::string ca_name;
 };
diff --git a/src/db/psql.cpp b/src/db/psql.cpp
index 4121994..8ddc7f1 100644
--- a/src/db/psql.cpp
+++ b/src/db/psql.cpp
@@ -10,6 +10,16 @@
 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." );
+    }
 }
 
 
@@ -66,7 +76,7 @@ void PostgresJobProvider::failJob( std::shared_ptr<Job> job ) {
 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 +96,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 +150,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 +159,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();
 }
 
-- 
2.39.2