modules/gpgsqlbackend/spgsql.cc

   1 /*
   2  * This file is part of PowerDNS or dnsdist.
   3  * Copyright -- PowerDNS.COM B.V. and its contributors
   4  *
   5  * This program is free software; you can redistribute it and/or modify
   6  * it under the terms of version 2 of the GNU General Public License as
   7  * published by the Free Software Foundation.
   8  *
   9  * In addition, for the avoidance of any doubt, permission is granted to
  10  * link this program with OpenSSL and to (re)distribute the binaries
  11  * produced as the result of such linking.
  12  *
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  * GNU General Public License for more details.
  17  *
  18  * You should have received a copy of the GNU General Public License
  19  * along with this program; if not, write to the Free Software
  20  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  21  */
  22
  23 #ifdef HAVE_CONFIG_H
  24 #include "config.h"
  25 #endif
  26 #include <string>
  27 #include "spgsql.hh"
  28 #include <sys/time.h>
  29 #include <iostream>
  30 #include "pdns/logger.hh"
  31 #include "pdns/dns.hh"
  32 #include "pdns/namespaces.hh"
  33 #include <algorithm>
  34
  35
  36 class SPgSQLStatement: public SSqlStatement
  37 {
  38 public:
  39   SPgSQLStatement(const string& query, bool dolog, int nparams, SPgSQL* db) {
  40     d_query = query;
  41     d_dolog = dolog;
  42     d_parent = db;
  43     d_prepared = false;
  44     d_nparams = nparams;
  45     d_res = NULL;
  46     d_res_set = NULL;
  47     paramValues = NULL;
  48     paramLengths = NULL;
  49     d_do_commit = false;
  50   }
  51
  52   SSqlStatement* bind(const string& name, bool value) { return bind(name, string(value ? "t" : "f")); }
  53   SSqlStatement* bind(const string& name, int value) { return bind(name, std::to_string(value)); }
  54   SSqlStatement* bind(const string& name, uint32_t value) { return bind(name, std::to_string(value)); }
  55   SSqlStatement* bind(const string& name, long value) { return bind(name, std::to_string(value)); }
  56   SSqlStatement* bind(const string& name, unsigned long value) { return bind(name, std::to_string(value)); }
  57   SSqlStatement* bind(const string& name, long long value) { return bind(name, std::to_string(value)); }
  58   SSqlStatement* bind(const string& name, unsigned long long value) { return bind(name, std::to_string(value)); }
  59   SSqlStatement* bind(const string& name, const std::string& value) {
  60     prepareStatement();
  61     allocate();
  62     if (d_paridx>=d_nparams) {
  63       releaseStatement();
  64       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
  65     }
  66     paramValues[d_paridx] = new char[value.size()+1];
  67     memset(paramValues[d_paridx], 0, sizeof(char)*(value.size()+1));
  68     value.copy(paramValues[d_paridx], value.size());
  69     paramLengths[d_paridx] = value.size();
  70     d_paridx++;
  71     return this;
  72   }
  73   SSqlStatement* bindNull(const string& name) { prepareStatement(); d_paridx++; return this; } // these are set null in allocate()
  74   SSqlStatement* execute() {
  75     prepareStatement();
  76     if (d_dolog) {
  77       L<<Logger::Warning<<"Query: "<<d_query<<endl;
  78     }
  79     if (!d_parent->in_trx()) {
  80       PQexec(d_db(),"BEGIN");
  81       d_do_commit = true;
  82     } else d_do_commit = false;
  83     d_res_set = PQexecPrepared(d_db(), d_stmt.c_str(), d_nparams, paramValues, paramLengths, NULL, 0);
  84     ExecStatusType status = PQresultStatus(d_res_set);
  85     string errmsg(PQresultErrorMessage(d_res_set));
  86     if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
  87       string errmsg(PQresultErrorMessage(d_res_set));
  88       releaseStatement();
  89       throw SSqlException("Fatal error during query: " + d_query + string(": ") + errmsg);
  90     }
  91     d_cur_set = 0;
  92     nextResult();
  93     return this;
  94   }
  95
  96   void nextResult() {
  97     if (d_res_set == NULL) return; // no refcursor
  98     if (d_cur_set >= PQntuples(d_res_set)) {
  99       PQclear(d_res_set);
 100       d_res_set = NULL;
 101       return;
 102     }
 103     // this code handles refcursors if they are returned
 104     // by stored procedures. you can return more than one
 105     // if you return SETOF refcursor.
 106     if (PQftype(d_res_set, 0) == 1790) { // REFCURSOR
 107 #if PG_VERSION_NUM > 90000
 108       // PQescapeIdentifier was added to libpq in postggresql 9.0
 109       char *val = PQgetvalue(d_res_set, d_cur_set++, 0);
 110       char *portal =  PQescapeIdentifier(d_db(), val, strlen(val));
 111       string cmd = string("FETCH ALL FROM \"") + string(portal) + string("\"");
 112       PQfreemem(portal);
 113 #else
 114       string portal = string(PQgetvalue(d_res_set, d_cur_set++, 0));
 115       string cmd = string("FETCH ALL FROM \"") + portal + string("\"");
 116 #endif
 117       // execute FETCH
 118       if (d_dolog)
 119          L<<Logger::Warning<<"Query: "<<cmd<<endl;
 120       d_res = PQexec(d_db(),cmd.c_str());
 121       d_resnum = PQntuples(d_res);
 122       d_fnum = PQnfields(d_res);
 123       d_residx = 0;
 124     } else {
 125       d_res = d_res_set;
 126       d_res_set = NULL;
 127       d_resnum = PQntuples(d_res);
 128       d_fnum = PQnfields(d_res);
 129     }
 130   }
 131
 132   bool hasNextRow()
 133   {
 134     return d_residx<d_resnum;
 135   }
 136
 137   SSqlStatement* nextRow(row_t& row) {
 138     int i;
 139     row.clear();
 140     if (d_residx>=d_resnum || !d_res) return this;
 141     row.reserve(PQnfields(d_res));
 142     for(i=0;i<PQnfields(d_res);i++) {
 143       if (PQgetisnull(d_res, d_residx, i)) {
 144         row.push_back("");
 145       } else if (PQftype(d_res, i) == 16) { // BOOLEAN
 146         char *val = PQgetvalue(d_res, d_residx, i);
 147         row.push_back(val[0] == 't' ? "1" : "0");
 148       } else {
 149         row.push_back(string(PQgetvalue(d_res, d_residx, i)));
 150       }
 151     }
 152     d_residx++;
 153     if (d_residx >= d_resnum) {
 154       PQclear(d_res);
 155       d_res = NULL;
 156       nextResult();
 157     }
 158     return this;
 159   }
 160
 161   SSqlStatement* getResult(result_t& result) {
 162     result.clear();
 163     if (d_res == NULL) return this;
 164     result.reserve(d_resnum);
 165     row_t row;
 166     while(hasNextRow()) { nextRow(row); result.push_back(row); }
 167     return this;
 168   }
 169
 170   SSqlStatement* reset() {
 171      int i;
 172      if (!d_parent->in_trx() && d_do_commit) {
 173        PGresult *res = PQexec(d_db(),"COMMIT");
 174        PQclear(res);
 175      }
 176      d_do_commit = false;
 177      if (d_res)
 178        PQclear(d_res);
 179      if (d_res_set)
 180        PQclear(d_res_set);
 181      d_res_set = NULL;
 182      d_res = NULL;
 183      d_paridx = d_residx = d_resnum = 0;
 184      if (paramValues)
 185        for(i=0;i<d_nparams;i++)
 186          if (paramValues[i]) delete [] paramValues[i];
 187      delete [] paramValues;
 188      paramValues = NULL;
 189      delete [] paramLengths;
 190      paramLengths = NULL;
 191      return this;
 192   }
 193
 194   const std::string& getQuery() { return d_query; }
 195
 196   ~SPgSQLStatement() {
 197     releaseStatement();
 198   }
 199 private:
 200   PGconn* d_db() {
 201     return d_parent->db();
 202   }
 203
 204   void releaseStatement() {
 205     d_prepared = false;
 206     reset();
 207     if (!d_stmt.empty()) {
 208       string cmd = string("DEALLOCATE " + d_stmt);
 209       PGresult *res = PQexec(d_db(), cmd.c_str());
 210       PQclear(res);
 211       d_stmt.clear();
 212     }
 213   }
 214
 215   void prepareStatement() {
 216     struct timeval tv;
 217     if (d_prepared) return;
 218     // prepare a statement
 219     gettimeofday(&tv,NULL);
 220     this->d_stmt = string("stmt") + std::to_string(tv.tv_sec) + std::to_string(tv.tv_usec);
 221     PGresult* res = PQprepare(d_db(), d_stmt.c_str(), d_query.c_str(), d_nparams, NULL);
 222     ExecStatusType status = PQresultStatus(res);
 223     string errmsg(PQresultErrorMessage(res));
 224     PQclear(res);
 225     if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
 226       releaseStatement();
 227       throw SSqlException("Fatal error during prepare: " + d_query + string(": ") + errmsg);
 228     }
 229     paramValues=NULL;
 230     d_cur_set=d_paridx=d_residx=d_resnum=d_fnum=0;
 231     paramLengths=NULL;
 232     d_res=NULL;
 233     d_res_set=NULL;
 234     d_do_commit=false;
 235     d_prepared = true;
 236   }
 237
 238   void allocate() {
 239      if (paramValues != NULL) return;
 240      paramValues = new char*[d_nparams];
 241      paramLengths = new int[d_nparams];
 242      memset(paramValues, 0, sizeof(char*)*d_nparams);
 243      memset(paramLengths, 0, sizeof(int)*d_nparams);
 244   }
 245
 246   string d_query;
 247   string d_stmt;
 248   SPgSQL *d_parent;
 249   PGresult *d_res_set;
 250   PGresult *d_res;
 251   bool d_dolog;
 252   bool d_prepared;
 253   int d_nparams;
 254   int d_paridx;
 255   char **paramValues;
 256   int *paramLengths;
 257   int d_residx;
 258   int d_resnum;
 259   int d_fnum;
 260   int d_cur_set;
 261   bool d_do_commit;
 262 };
 263
 264 bool SPgSQL::s_dolog;
 265
 266 SPgSQL::SPgSQL(const string &database, const string &host, const string& port, const string &user,
 267                const string &password)
 268 {
 269   d_db=0;
 270   d_in_trx = false;
 271   d_connectstr="";
 272
 273   if (!database.empty())
 274     d_connectstr+="dbname="+database;
 275
 276   if (!user.empty())
 277     d_connectstr+=" user="+user;
 278
 279   if(!host.empty())
 280     d_connectstr+=" host="+host;
 281
 282   if(!port.empty())
 283     d_connectstr+=" port="+port;
 284
 285   d_connectlogstr=d_connectstr;
 286
 287   if(!password.empty()) {
 288     d_connectlogstr+=" password=<HIDDEN>";
 289     d_connectstr+=" password="+password;
 290   }
 291
 292   d_db=PQconnectdb(d_connectstr.c_str());
 293
 294   if (!d_db || PQstatus(d_db)==CONNECTION_BAD) {
 295     try {
 296       throw sPerrorException("Unable to connect to database, connect string: "+d_connectlogstr);
 297     }
 298     catch(...) {
 299       if(d_db)
 300         PQfinish(d_db);
 301       d_db = 0;
 302       throw;
 303     }
 304   }
 305 }
 306
 307 void SPgSQL::setLog(bool state)
 308 {
 309   s_dolog=state;
 310 }
 311
 312 SPgSQL::~SPgSQL()
 313 {
 314   PQfinish(d_db);
 315 }
 316
 317 SSqlException SPgSQL::sPerrorException(const string &reason)
 318 {
 319   return SSqlException(reason+string(": ")+(d_db ? PQerrorMessage(d_db) : "no connection"));
 320 }
 321
 322 void SPgSQL::execute(const string& query)
 323 {
 324   PGresult* res = PQexec(d_db, query.c_str());
 325   ExecStatusType status = PQresultStatus(res);
 326   string errmsg(PQresultErrorMessage(res));
 327   PQclear(res);
 328   if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
 329     throw sPerrorException("Fatal error during query: " + errmsg);
 330   }
 331 }
 332
 333 SSqlStatement* SPgSQL::prepare(const string& query, int nparams)
 334 {
 335   return new SPgSQLStatement(query, s_dolog, nparams, this);
 336 }
 337
 338 void SPgSQL::startTransaction() {
 339   execute("begin");
 340   d_in_trx = true;
 341 }
 342
 343 void SPgSQL::commit() {
 344   execute("commit");
 345   d_in_trx = false;
 346 }
 347
 348 void SPgSQL::rollback() {
 349   execute("rollback");
 350   d_in_trx = false;
 351 }