X-Git-Url: https://code.wpia.club/?a=blobdiff_plain;f=src%2Fdb%2Fpsql.cpp;h=41219944f0258d8c257d9e8f16d25d412df2b107;hb=ba8705bba71061be2593a7755a7b987b3e6c499e;hp=b543b9e46d080140a79fa60e6e8516b40d7a916a;hpb=ed1082d9e86cbb453c5ce3be0e09c7020a6adcbd;p=cassiopeia.git diff --git a/src/db/psql.cpp b/src/db/psql.cpp index b543b9e..4121994 100644 --- a/src/db/psql.cpp +++ b/src/db/psql.cpp @@ -8,15 +8,15 @@ #include 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"){ + c( "dbname=" + database + " host=" + server + " user=" + user + " password=" + password + " client_encoding=UTF-8 application_name=cassiopeia-client" ) { // TODO better connection string generation?? } std::shared_ptr PostgresJobProvider::fetchJob() { std::string q = "SELECT id, \"targetId\", task, \"executeFrom\", \"executeTo\", warning FROM jobs WHERE state='open' AND warning < 3"; - pqxx::work txn(c); - pqxx::result result = txn.exec(q); + pqxx::work txn( c ); + pqxx::result result = txn.exec( q ); auto job = std::make_shared(); @@ -28,8 +28,8 @@ std::shared_ptr PostgresJobProvider::fetchJob() { job->id = result[0]["id"].as(); job->target = result[0]["\"targetId\""].as(); job->task = result[0]["task"].as(); - job->from = result[0]["\"executeFrom\""].as(""); - job->to = result[0]["\"executeTo\""].as(""); + job->from = result[0]["\"executeFrom\""].as( "" ); + job->to = result[0]["\"executeTo\""].as( "" ); job->warning = result[0]["warning"].as(); 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 ); @@ -38,38 +38,41 @@ std::shared_ptr PostgresJobProvider::fetchJob() { } void PostgresJobProvider::finishJob( std::shared_ptr job ) { - pqxx::work txn(c); + pqxx::work txn( c ); std::string q = "UPDATE jobs SET state='done' WHERE id=" + txn.quote( job->id ); - pqxx::result r = txn.exec(q); + pqxx::result r = txn.exec( q ); if( r.affected_rows() != 1 ) { - throw std::runtime_error("No database entry found."); + throw std::runtime_error( "No database entry found." ); } + txn.commit(); } void PostgresJobProvider::failJob( std::shared_ptr job ) { - pqxx::work txn(c); + pqxx::work txn( c ); std::string q = "UPDATE jobs SET warning = warning + 1 WHERE id=" + txn.quote( job->id ); - pqxx::result r = txn.exec(q); + pqxx::result r = txn.exec( q ); if( r.affected_rows() != 1 ) { - throw std::runtime_error("No database entry found."); + throw std::runtime_error( "No database entry found." ); } + txn.commit(); } std::shared_ptr PostgresJobProvider::fetchTBSCert( std::shared_ptr job ) { - pqxx::work txn(c); + pqxx::work txn( c ); auto cert = std::make_shared(); 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 ); - pqxx::result r = txn.exec(q); + pqxx::result r = txn.exec( q ); if( r.size() != 1 ) { - throw std::runtime_error("Error, no or multiple certs found"); - } + throw std::runtime_error( "Error, no or multiple certs found" ); + } + auto ro = r[0]; std::string profileName = ro["keyname"].as(); @@ -93,7 +96,7 @@ std::shared_ptr PostgresJobProvider::fetchTBSCert( std::shared_p std::cout << "Fetching SANs" << std::endl; - for( auto row = r.begin(); row != r.end(); ++row) { + for( auto row = r.begin(); row != r.end(); ++row ) { auto nSAN = std::make_shared(); nSAN->content = row["contents"].as(); nSAN->type = row["type"].as(); @@ -103,7 +106,7 @@ std::shared_ptr PostgresJobProvider::fetchTBSCert( std::shared_p q = "SELECT name, value FROM \"certAvas\" WHERE \"certId\"=" + txn.quote( job->target ); r = txn.exec( q ); - for( auto row = r.begin(); row != r.end(); ++row) { + for( auto row = r.begin(); row != r.end(); ++row ) { auto nAVA = std::make_shared(); nAVA->name = row["name"].as(); nAVA->value = row["value"].as(); @@ -113,55 +116,65 @@ std::shared_ptr PostgresJobProvider::fetchTBSCert( std::shared_p return cert; } -std::string pgTime( std::string isoTime){ - return isoTime.substr(0, 8) + " " + isoTime.substr(8, 6); +std::string pgTime( std::string isoTime ) { + return isoTime.substr( 0, 8 ) + " " + isoTime.substr( 8, 6 ); } void PostgresJobProvider::writeBack( std::shared_ptr job, std::shared_ptr res ) { - pqxx::work txn(c); + pqxx::work txn( c ); std::string id = "SELECT id FROM cacerts WHERE keyname=" + txn.quote( res->ca_name ); - pqxx::result r = txn.exec(id); + pqxx::result r = txn.exec( id ); std::string read_id; - if( r.size() != 1) { - throw std::runtime_error("Error while inserting new ca cert not found"); + if( r.size() != 1 ) { + throw std::runtime_error( "Error while inserting new ca cert not found" ); } else { read_id = r[0]["id"].as(); } + std::string serial = res->serial; - std::transform(serial.begin(), serial.end(), serial.begin(), ::tolower); - 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::transform( serial.begin(), serial.end(), serial.begin(), ::tolower ); + + if( serial[0] == '0' ) { + 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 ); // TODO write more thingies back r = txn.exec( q ); - if( r.affected_rows() != 1 ){ - throw std::runtime_error("Only one row should be updated."); + + if( r.affected_rows() != 1 ) { + throw std::runtime_error( "Only one row should be updated." ); } + txn.commit(); } std::pair PostgresJobProvider::getRevocationInfo( std::shared_ptr job ) { - pqxx::work txn(c); + pqxx::work txn( c ); std::string q = "SELECT certs.serial, cacerts.keyname FROM certs INNER JOIN cacerts ON certs.\"caid\" = cacerts.id WHERE certs.id = " + txn.quote( job->target ); pqxx::result r = txn.exec( q ); - if( r.size() != 1) { - throw std::runtime_error("Only one row expected but multiple found."); + + if( r.size() != 1 ) { + throw std::runtime_error( "Only one row expected but multiple found." ); } - return {r[0][0].as(), r[0][1].as()}; } void PostgresJobProvider::writeBackRevocation( std::shared_ptr job, std::string date ) { - logger::notef( "Revoking at %s", date); - pqxx::work txn(c); + logger::notef( "Revoking at %s", date ); + pqxx::work txn( c ); logger::note( "executing" ); pqxx::result r = txn.exec( "UPDATE certs SET revoked = " + txn.quote( pgTime( date ) ) + " WHERE id = " + txn.quote( job->target ) ); - if( r.affected_rows() != 1 ){ - throw std::runtime_error("Only one row should be updated."); + + if( r.affected_rows() != 1 ) { + throw std::runtime_error( "Only one row should be updated." ); } + logger::note( "committing" ); txn.commit(); logger::note( "committed" );