pdns/backends/gsql/gsqlbackend.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 #ifdef HAVE_CONFIG_H
  23 #include "config.h"
  24 #endif
  25 #include "pdns/dns.hh"
  26 #include "pdns/dnsbackend.hh"
  27 #include "gsqlbackend.hh"
  28 #include "pdns/dnspacket.hh"
  29 #include "pdns/pdnsexception.hh"
  30 #include "pdns/logger.hh"
  31 #include "pdns/arguments.hh"
  32 #include "pdns/base32.hh"
  33 #include "pdns/dnssecinfra.hh"
  34 #include <boost/algorithm/string.hpp>
  35 #include <sstream>
  36 #include <boost/format.hpp>
  37 #include <boost/scoped_ptr.hpp>
  38
  39 #define ASSERT_ROW_COLUMNS(query, row, num) { if (row.size() != num) { throw PDNSException(std::string(query) + " returned wrong number of columns, expected "  #num  ", got " + std::to_string(row.size())); } }
  40
  41 GSQLBackend::GSQLBackend(const string &mode, const string &suffix)
  42 {
  43   setArgPrefix(mode+suffix);
  44   d_db=0;
  45   d_logprefix="["+mode+"Backend"+suffix+"] ";
  46
  47   try
  48   {
  49     d_dnssecQueries = mustDo("dnssec");
  50   }
  51   catch (const ArgException&)
  52   {
  53     d_dnssecQueries = false;
  54   }
  55
  56   d_NoIdQuery=getArg("basic-query");
  57   d_IdQuery=getArg("id-query");
  58   d_ANYNoIdQuery=getArg("any-query");
  59   d_ANYIdQuery=getArg("any-id-query");
  60
  61   d_listQuery=getArg("list-query");
  62   d_listSubZoneQuery=getArg("list-subzone-query");
  63
  64   d_InfoOfDomainsZoneQuery=getArg("info-zone-query");
  65   d_InfoOfAllSlaveDomainsQuery=getArg("info-all-slaves-query");
  66   d_SuperMasterInfoQuery=getArg("supermaster-query");
  67   d_GetSuperMasterIPs=getArg("supermaster-name-to-ips");
  68   d_InsertZoneQuery=getArg("insert-zone-query");
  69   d_InsertRecordQuery=getArg("insert-record-query");
  70   d_UpdateMasterOfZoneQuery=getArg("update-master-query");
  71   d_UpdateKindOfZoneQuery=getArg("update-kind-query");
  72   d_UpdateSerialOfZoneQuery=getArg("update-serial-query");
  73   d_UpdateLastCheckofZoneQuery=getArg("update-lastcheck-query");
  74   d_UpdateAccountOfZoneQuery=getArg("update-account-query");
  75   d_InfoOfAllMasterDomainsQuery=getArg("info-all-master-query");
  76   d_DeleteDomainQuery=getArg("delete-domain-query");
  77   d_DeleteZoneQuery=getArg("delete-zone-query");
  78   d_DeleteRRSetQuery=getArg("delete-rrset-query");
  79   d_DeleteNamesQuery=getArg("delete-names-query");
  80   d_getAllDomainsQuery=getArg("get-all-domains-query");
  81
  82   d_InsertEmptyNonTerminalOrderQuery=getArg("insert-empty-non-terminal-order-query");
  83   d_DeleteEmptyNonTerminalQuery = getArg("delete-empty-non-terminal-query");
  84   d_RemoveEmptyNonTerminalsFromZoneQuery = getArg("remove-empty-non-terminals-from-zone-query");
  85
  86   d_ListCommentsQuery = getArg("list-comments-query");
  87   d_InsertCommentQuery = getArg("insert-comment-query");
  88   d_DeleteCommentRRsetQuery = getArg("delete-comment-rrset-query");
  89   d_DeleteCommentsQuery = getArg("delete-comments-query");
  90
  91   d_firstOrderQuery = getArg("get-order-first-query");
  92   d_beforeOrderQuery = getArg("get-order-before-query");
  93   d_afterOrderQuery = getArg("get-order-after-query");
  94   d_lastOrderQuery = getArg("get-order-last-query");
  95
  96   d_updateOrderNameAndAuthQuery = getArg("update-ordername-and-auth-query");
  97   d_updateOrderNameAndAuthTypeQuery = getArg("update-ordername-and-auth-type-query");
  98   d_nullifyOrderNameAndUpdateAuthQuery = getArg("nullify-ordername-and-update-auth-query");
  99   d_nullifyOrderNameAndUpdateAuthTypeQuery = getArg("nullify-ordername-and-update-auth-type-query");
 100
 101   d_AddDomainKeyQuery = getArg("add-domain-key-query");
 102   d_GetLastInsertedKeyIdQuery = getArg("get-last-inserted-key-id-query");
 103   d_ListDomainKeysQuery = getArg("list-domain-keys-query");
 104
 105   d_GetAllDomainMetadataQuery = getArg("get-all-domain-metadata-query");
 106   d_GetDomainMetadataQuery = getArg("get-domain-metadata-query");
 107   d_ClearDomainMetadataQuery = getArg("clear-domain-metadata-query");
 108   d_ClearDomainAllMetadataQuery = getArg("clear-domain-all-metadata-query");
 109   d_SetDomainMetadataQuery = getArg("set-domain-metadata-query");
 110
 111   d_ActivateDomainKeyQuery = getArg("activate-domain-key-query");
 112   d_DeactivateDomainKeyQuery = getArg("deactivate-domain-key-query");
 113   d_RemoveDomainKeyQuery = getArg("remove-domain-key-query");
 114   d_ClearDomainAllKeysQuery = getArg("clear-domain-all-keys-query");
 115
 116   d_getTSIGKeyQuery = getArg("get-tsig-key-query");
 117   d_setTSIGKeyQuery = getArg("set-tsig-key-query");
 118   d_deleteTSIGKeyQuery = getArg("delete-tsig-key-query");
 119   d_getTSIGKeysQuery = getArg("get-tsig-keys-query");
 120
 121   d_SearchRecordsQuery = getArg("search-records-query");
 122   d_SearchCommentsQuery = getArg("search-comments-query");
 123
 124   d_query_stmt = NULL;
 125   d_NoIdQuery_stmt = NULL;
 126   d_IdQuery_stmt = NULL;
 127   d_ANYNoIdQuery_stmt = NULL;
 128   d_ANYIdQuery_stmt = NULL;
 129   d_listQuery_stmt = NULL;
 130   d_listSubZoneQuery_stmt = NULL;
 131   d_InfoOfDomainsZoneQuery_stmt = NULL;
 132   d_InfoOfAllSlaveDomainsQuery_stmt = NULL;
 133   d_SuperMasterInfoQuery_stmt = NULL;
 134   d_GetSuperMasterIPs_stmt = NULL;
 135   d_InsertZoneQuery_stmt = NULL;
 136   d_InsertRecordQuery_stmt = NULL;
 137   d_InsertEmptyNonTerminalOrderQuery_stmt = NULL;
 138   d_UpdateMasterOfZoneQuery_stmt = NULL;
 139   d_UpdateKindOfZoneQuery_stmt = NULL;
 140   d_UpdateSerialOfZoneQuery_stmt = NULL;
 141   d_UpdateLastCheckofZoneQuery_stmt = NULL;
 142   d_UpdateAccountOfZoneQuery_stmt = NULL;
 143   d_InfoOfAllMasterDomainsQuery_stmt = NULL;
 144   d_DeleteDomainQuery_stmt = NULL;
 145   d_DeleteZoneQuery_stmt = NULL;
 146   d_DeleteRRSetQuery_stmt = NULL;
 147   d_DeleteNamesQuery_stmt = NULL;
 148   d_firstOrderQuery_stmt = NULL;
 149   d_beforeOrderQuery_stmt = NULL;
 150   d_afterOrderQuery_stmt = NULL;
 151   d_lastOrderQuery_stmt = NULL;
 152   d_updateOrderNameAndAuthQuery_stmt = NULL;
 153   d_updateOrderNameAndAuthTypeQuery_stmt = NULL;
 154   d_nullifyOrderNameAndUpdateAuthQuery_stmt = NULL;
 155   d_nullifyOrderNameAndUpdateAuthTypeQuery_stmt = NULL;
 156   d_RemoveEmptyNonTerminalsFromZoneQuery_stmt = NULL;
 157   d_DeleteEmptyNonTerminalQuery_stmt = NULL;
 158   d_AddDomainKeyQuery_stmt = NULL;
 159   d_GetLastInsertedKeyIdQuery_stmt = NULL;
 160   d_ListDomainKeysQuery_stmt = NULL;
 161   d_GetAllDomainMetadataQuery_stmt = NULL;
 162   d_GetDomainMetadataQuery_stmt = NULL;
 163   d_ClearDomainMetadataQuery_stmt = NULL;
 164   d_ClearDomainAllMetadataQuery_stmt = NULL;
 165   d_SetDomainMetadataQuery_stmt = NULL;
 166   d_RemoveDomainKeyQuery_stmt = NULL;
 167   d_ActivateDomainKeyQuery_stmt = NULL;
 168   d_DeactivateDomainKeyQuery_stmt = NULL;
 169   d_ClearDomainAllKeysQuery_stmt = NULL;
 170   d_getTSIGKeyQuery_stmt = NULL;
 171   d_setTSIGKeyQuery_stmt = NULL;
 172   d_deleteTSIGKeyQuery_stmt = NULL;
 173   d_getTSIGKeysQuery_stmt = NULL;
 174   d_getAllDomainsQuery_stmt = NULL;
 175   d_ListCommentsQuery_stmt = NULL;
 176   d_InsertCommentQuery_stmt = NULL;
 177   d_DeleteCommentRRsetQuery_stmt = NULL;
 178   d_DeleteCommentsQuery_stmt = NULL;
 179   d_SearchRecordsQuery_stmt = NULL;
 180   d_SearchCommentsQuery_stmt = NULL;
 181 }
 182
 183 void GSQLBackend::setNotified(uint32_t domain_id, uint32_t serial)
 184 {
 185   try {
 186     reconnectIfNeeded();
 187
 188     d_UpdateSerialOfZoneQuery_stmt->
 189       bind("serial", serial)->
 190       bind("domain_id", domain_id)->
 191       execute()->
 192       reset();
 193   }
 194   catch(SSqlException &e) {
 195     throw PDNSException("GSQLBackend unable to refresh domain_id "+itoa(domain_id)+": "+e.txtReason());
 196   }
 197 }
 198
 199 void GSQLBackend::setFresh(uint32_t domain_id)
 200 {
 201   try {
 202     reconnectIfNeeded();
 203
 204     d_UpdateLastCheckofZoneQuery_stmt->
 205       bind("last_check", time(0))->
 206       bind("domain_id", domain_id)->
 207       execute()->
 208       reset();
 209   }
 210   catch (SSqlException &e) {
 211     throw PDNSException("GSQLBackend unable to refresh domain_id "+itoa(domain_id)+": "+e.txtReason());
 212   }
 213 }
 214
 215 bool GSQLBackend::setMaster(const DNSName &domain, const string &ip)
 216 {
 217   try {
 218     reconnectIfNeeded();
 219
 220     d_UpdateMasterOfZoneQuery_stmt->
 221       bind("master", ip)->
 222       bind("domain", domain)->
 223       execute()->
 224       reset();
 225   }
 226   catch (SSqlException &e) {
 227     throw PDNSException("GSQLBackend unable to set master of domain '"+domain.toLogString()+"': "+e.txtReason());
 228   }
 229   return true;
 230 }
 231
 232 bool GSQLBackend::setKind(const DNSName &domain, const DomainInfo::DomainKind kind)
 233 {
 234   try {
 235     reconnectIfNeeded();
 236
 237     d_UpdateKindOfZoneQuery_stmt->
 238       bind("kind", toUpper(DomainInfo::getKindString(kind)))->
 239       bind("domain", domain)->
 240       execute()->
 241       reset();
 242   }
 243   catch (SSqlException &e) {
 244     throw PDNSException("GSQLBackend unable to set kind of domain '"+domain.toLogString()+"': "+e.txtReason());
 245   }
 246   return true;
 247 }
 248
 249 bool GSQLBackend::setAccount(const DNSName &domain, const string &account)
 250 {
 251   try {
 252     reconnectIfNeeded();
 253
 254     d_UpdateAccountOfZoneQuery_stmt->
 255             bind("account", account)->
 256             bind("domain", domain)->
 257             execute()->
 258             reset();
 259   }
 260   catch (SSqlException &e) {
 261     throw PDNSException("GSQLBackend unable to set account of domain '"+domain.toLogString()+"': "+e.txtReason());
 262   }
 263   return true;
 264 }
 265
 266 bool GSQLBackend::getDomainInfo(const DNSName &domain, DomainInfo &di, bool getSerial)
 267 {
 268   /* fill DomainInfo from database info:
 269      id,name,master IP(s),last_check,notified_serial,type,account */
 270   try {
 271     reconnectIfNeeded();
 272
 273     d_InfoOfDomainsZoneQuery_stmt->
 274       bind("domain", domain)->
 275       execute()->
 276       getResult(d_result)->
 277       reset();
 278   }
 279   catch(SSqlException &e) {
 280     throw PDNSException("GSQLBackend unable to retrieve information about a domain: "+e.txtReason());
 281   }
 282
 283   int numanswers=d_result.size();
 284   if(!numanswers)
 285     return false;
 286
 287   ASSERT_ROW_COLUMNS("info-zone-query", d_result[0], 7);
 288
 289   di.id=pdns_stou(d_result[0][0]);
 290   try {
 291     di.zone=DNSName(d_result[0][1]);
 292   } catch (...) {
 293     return false;
 294   }
 295   vector<string> masters;
 296   stringtok(masters, d_result[0][2], " ,\t");
 297   for(const auto& m : masters)
 298     di.masters.emplace_back(m, 53);
 299   di.last_check=pdns_stou(d_result[0][3]);
 300   di.notified_serial = pdns_stou(d_result[0][4]);
 301   string type=d_result[0][5];
 302   di.account=d_result[0][6];
 303   di.backend=this;
 304
 305   di.serial = 0;
 306   if(getSerial) {
 307     try {
 308       SOAData sd;
 309       if(!getSOA(domain, sd))
 310         g_log<<Logger::Notice<<"No serial for '"<<domain<<"' found - zone is missing?"<<endl;
 311       else
 312         di.serial = sd.serial;
 313     }
 314     catch(PDNSException &ae){
 315       g_log<<Logger::Error<<"Error retrieving serial for '"<<domain<<"': "<<ae.reason<<endl;
 316     }
 317   }
 318
 319   di.kind = DomainInfo::stringToKind(type);
 320
 321   return true;
 322 }
 323
 324 void GSQLBackend::getUnfreshSlaveInfos(vector<DomainInfo> *unfreshDomains)
 325 {
 326   /* list all domains that need refreshing for which we are slave, and insert into SlaveDomain:
 327      id,name,master IP,serial */
 328   try {
 329     reconnectIfNeeded();
 330
 331     d_InfoOfAllSlaveDomainsQuery_stmt->
 332       execute()->
 333       getResult(d_result)->
 334       reset();
 335   }
 336   catch (SSqlException &e) {
 337     throw PDNSException("GSQLBackend unable to retrieve list of slave domains: "+e.txtReason());
 338   }
 339
 340   vector<DomainInfo> allSlaves;
 341   int numanswers=d_result.size();
 342   for(int n=0;n<numanswers;++n) { // id,name,master,last_check
 343     DomainInfo sd;
 344     ASSERT_ROW_COLUMNS("info-all-slaves-query", d_result[n], 4);
 345     sd.id=pdns_stou(d_result[n][0]);
 346     try {
 347       sd.zone= DNSName(d_result[n][1]);
 348     } catch (...) {
 349       continue;
 350     }
 351
 352     vector<string> masters;
 353     stringtok(masters, d_result[n][2], ", \t");
 354     for(const auto& m : masters)
 355       sd.masters.emplace_back(m, 53);
 356
 357     sd.last_check=pdns_stou(d_result[n][3]);
 358     sd.backend=this;
 359     sd.kind=DomainInfo::Slave;
 360     allSlaves.push_back(sd);
 361   }
 362
 363   for(vector<DomainInfo>::iterator i=allSlaves.begin();i!=allSlaves.end();++i) {
 364     SOAData sdata;
 365     sdata.serial=0;
 366     sdata.refresh=0;
 367     getSOA(i->zone,sdata);
 368     if((time_t)(i->last_check+sdata.refresh) < time(0)) {
 369       i->serial=sdata.serial;
 370       unfreshDomains->push_back(*i);
 371     }
 372   }
 373 }
 374
 375 void GSQLBackend::getUpdatedMasters(vector<DomainInfo> *updatedDomains)
 376 {
 377   /* list all domains that need notifications for which we are master, and insert into updatedDomains
 378      id,name,master IP,serial */
 379   try {
 380     reconnectIfNeeded();
 381
 382     d_InfoOfAllMasterDomainsQuery_stmt->
 383       execute()->
 384       getResult(d_result)->
 385       reset();
 386   }
 387   catch(SSqlException &e) {
 388     throw PDNSException("GSQLBackend unable to retrieve list of master domains: "+e.txtReason());
 389   }
 390
 391   vector<DomainInfo> allMasters;
 392   size_t numanswers=d_result.size();
 393   for(size_t n=0;n<numanswers;++n) { // id,name,master,last_check,notified_serial
 394     DomainInfo sd;
 395     ASSERT_ROW_COLUMNS("info-all-master-query", d_result[n], 6);
 396     sd.id=pdns_stou(d_result[n][0]);
 397     try {
 398       sd.zone= DNSName(d_result[n][1]);
 399     } catch (...) {
 400       continue;
 401     }
 402     sd.last_check=pdns_stou(d_result[n][3]);
 403     sd.notified_serial=pdns_stou(d_result[n][4]);
 404     sd.backend=this;
 405     sd.kind=DomainInfo::Master;
 406     allMasters.push_back(sd);
 407   }
 408
 409   for(vector<DomainInfo>::iterator i=allMasters.begin();i!=allMasters.end();++i) {
 410     SOAData sdata;
 411     sdata.serial=0;
 412     sdata.refresh=0;
 413     getSOA(i->zone,sdata);
 414     if(i->notified_serial!=sdata.serial) {
 415       i->serial=sdata.serial;
 416       updatedDomains->push_back(*i);
 417     }
 418   }
 419 }
 420
 421 bool GSQLBackend::updateDNSSECOrderNameAndAuth(uint32_t domain_id, const DNSName& qname, const DNSName& ordername, bool auth, const uint16_t qtype)
 422 {
 423   if(!d_dnssecQueries)
 424     return false;
 425
 426   if (!ordername.empty()) {
 427     if (qtype == QType::ANY) {
 428       try {
 429         reconnectIfNeeded();
 430
 431         d_updateOrderNameAndAuthQuery_stmt->
 432           bind("ordername", ordername.labelReverse().toString(" ", false))->
 433           bind("auth", auth)->
 434           bind("domain_id", domain_id)->
 435           bind("qname", qname)->
 436           execute()->
 437           reset();
 438       }
 439       catch(SSqlException &e) {
 440         throw PDNSException("GSQLBackend unable to update ordername and auth for domain_id "+itoa(domain_id)+": "+e.txtReason());
 441       }
 442     } else {
 443       try {
 444         reconnectIfNeeded();
 445
 446         d_updateOrderNameAndAuthTypeQuery_stmt->
 447           bind("ordername", ordername.labelReverse().toString(" ", false))->
 448           bind("auth", auth)->
 449           bind("domain_id", domain_id)->
 450           bind("qname", qname)->
 451           bind("qtype", QType(qtype).getName())->
 452           execute()->
 453           reset();
 454       }
 455       catch(SSqlException &e) {
 456         throw PDNSException("GSQLBackend unable to update ordername and auth per type for domain_id "+itoa(domain_id)+": "+e.txtReason());
 457       }
 458     }
 459   } else {
 460     if (qtype == QType::ANY) {
 461       reconnectIfNeeded();
 462
 463       try {
 464         d_nullifyOrderNameAndUpdateAuthQuery_stmt->
 465           bind("auth", auth)->
 466           bind("domain_id", domain_id)->
 467           bind("qname", qname)->
 468           execute()->
 469           reset();
 470       }
 471       catch(SSqlException &e) {
 472         throw PDNSException("GSQLBackend unable to nullify ordername and update auth for domain_id "+itoa(domain_id)+": "+e.txtReason());
 473       }
 474     } else {
 475       try {
 476         reconnectIfNeeded();
 477
 478         d_nullifyOrderNameAndUpdateAuthTypeQuery_stmt->
 479           bind("auth", auth)->
 480           bind("domain_id", domain_id)->
 481           bind("qname", qname)->
 482           bind("qtype", QType(qtype).getName())->
 483           execute()->
 484           reset();
 485       }
 486       catch(SSqlException &e) {
 487         throw PDNSException("GSQLBackend unable to nullify ordername and update auth per type for domain_id "+itoa(domain_id)+": "+e.txtReason());
 488       }
 489     }
 490   }
 491   return true;
 492 }
 493
 494 bool GSQLBackend::updateEmptyNonTerminals(uint32_t domain_id, set<DNSName>& insert, set<DNSName>& erase, bool remove)
 495 {
 496   if(remove) {
 497     try {
 498       reconnectIfNeeded();
 499
 500       d_RemoveEmptyNonTerminalsFromZoneQuery_stmt->
 501         bind("domain_id", domain_id)->
 502         execute()->
 503         reset();
 504     }
 505     catch (SSqlException &e) {
 506       throw PDNSException("GSQLBackend unable to delete empty non-terminal records from domain_id "+itoa(domain_id)+": "+e.txtReason());
 507       return false;
 508     }
 509   }
 510   else
 511   {
 512     for(const auto& qname: erase) {
 513       try {
 514         reconnectIfNeeded();
 515
 516         d_DeleteEmptyNonTerminalQuery_stmt->
 517           bind("domain_id", domain_id)->
 518           bind("qname", qname)->
 519           execute()->
 520           reset();
 521       }
 522       catch (SSqlException &e) {
 523         throw PDNSException("GSQLBackend unable to delete empty non-terminal rr '"+qname.toLogString()+"' from domain_id "+itoa(domain_id)+": "+e.txtReason());
 524         return false;
 525       }
 526     }
 527   }
 528
 529   for(const auto& qname: insert) {
 530     try {
 531       reconnectIfNeeded();
 532
 533       d_InsertEmptyNonTerminalOrderQuery_stmt->
 534         bind("domain_id", domain_id)->
 535         bind("qname", qname)->
 536         bindNull("ordername")->
 537         bind("auth", true)->
 538         execute()->
 539         reset();
 540     }
 541     catch (SSqlException &e) {
 542       throw PDNSException("GSQLBackend unable to insert empty non-terminal rr '"+qname.toLogString()+"' in domain_id "+itoa(domain_id)+": "+e.txtReason());
 543       return false;
 544     }
 545   }
 546
 547   return true;
 548 }
 549
 550 bool GSQLBackend::doesDNSSEC()
 551 {
 552     return d_dnssecQueries;
 553 }
 554
 555 bool GSQLBackend::getBeforeAndAfterNamesAbsolute(uint32_t id, const DNSName& qname, DNSName& unhashed, DNSName& before, DNSName& after)
 556 {
 557   if(!d_dnssecQueries)
 558     return false;
 559   after.clear();
 560
 561   SSqlStatement::row_t row;
 562   try {
 563     reconnectIfNeeded();
 564
 565     d_afterOrderQuery_stmt->
 566       bind("ordername", qname.labelReverse().toString(" ", false))->
 567       bind("domain_id", id)->
 568       execute();
 569     while(d_afterOrderQuery_stmt->hasNextRow()) {
 570       d_afterOrderQuery_stmt->nextRow(row);
 571       ASSERT_ROW_COLUMNS("get-order-after-query", row, 1);
 572       if(! row[0].empty()) { // Hack because NULL values are passed on as empty strings
 573         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 574       }
 575     }
 576     d_afterOrderQuery_stmt->reset();
 577   }
 578   catch(SSqlException &e) {
 579     throw PDNSException("GSQLBackend unable to find before/after (after) for domain_id "+itoa(id)+": "+e.txtReason());
 580   }
 581
 582   if(after.empty()) {
 583     try {
 584       reconnectIfNeeded();
 585
 586       d_firstOrderQuery_stmt->
 587         bind("domain_id", id)->
 588         execute();
 589       while(d_firstOrderQuery_stmt->hasNextRow()) {
 590         d_firstOrderQuery_stmt->nextRow(row);
 591         ASSERT_ROW_COLUMNS("get-order-first-query", row, 1);
 592         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 593       }
 594       d_firstOrderQuery_stmt->reset();
 595     }
 596     catch(SSqlException &e) {
 597       throw PDNSException("GSQLBackend unable to find before/after (first) for domain_id "+itoa(id)+": "+e.txtReason());
 598     }
 599   }
 600
 601   if (before.empty()) {
 602     unhashed.clear();
 603
 604     try {
 605       reconnectIfNeeded();
 606
 607       d_beforeOrderQuery_stmt->
 608         bind("ordername", qname.labelReverse().toString(" ", false))->
 609         bind("domain_id", id)->
 610         execute();
 611       while(d_beforeOrderQuery_stmt->hasNextRow()) {
 612         d_beforeOrderQuery_stmt->nextRow(row);
 613         ASSERT_ROW_COLUMNS("get-order-before-query", row, 2);
 614         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 615         try {
 616           unhashed=DNSName(row[1]);
 617         } catch (...) {
 618           continue;
 619         }
 620       }
 621       d_beforeOrderQuery_stmt->reset();
 622     }
 623     catch(SSqlException &e) {
 624       throw PDNSException("GSQLBackend unable to find before/after (before) for domain_id "+itoa(id)+": "+e.txtReason());
 625     }
 626
 627     if(! unhashed.empty())
 628     {
 629       // cerr<<"unhashed="<<unhashed<<",before="<<before<<", after="<<after<<endl;
 630       return true;
 631     }
 632
 633     try {
 634       reconnectIfNeeded();
 635
 636       d_lastOrderQuery_stmt->
 637         bind("domain_id", id)->
 638         execute();
 639       while(d_lastOrderQuery_stmt->hasNextRow()) {
 640         d_lastOrderQuery_stmt->nextRow(row);
 641         ASSERT_ROW_COLUMNS("get-order-last-query", row, 2);
 642         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 643         try {
 644           unhashed=DNSName(row[1]);
 645         } catch (...) {
 646           continue;
 647         }
 648       }
 649       d_lastOrderQuery_stmt->reset();
 650     }
 651     catch(SSqlException &e) {
 652       throw PDNSException("GSQLBackend unable to find before/after (last) for domain_id "+itoa(id)+": "+e.txtReason());
 653     }
 654   } else {
 655     before=qname;
 656   }
 657
 658   return true;
 659 }
 660
 661 bool GSQLBackend::addDomainKey(const DNSName& name, const KeyData& key, int64_t& id)
 662 {
 663   if(!d_dnssecQueries)
 664     return false;
 665
 666   try {
 667     reconnectIfNeeded();
 668
 669     d_AddDomainKeyQuery_stmt->
 670       bind("flags", key.flags)->
 671       bind("active", key.active)->
 672       bind("content", key.content)->
 673       bind("domain", name)->
 674       execute()->
 675       reset();
 676   }
 677   catch (SSqlException &e) {
 678     throw PDNSException("GSQLBackend unable to store key: "+e.txtReason());
 679   }
 680
 681   try {
 682     reconnectIfNeeded();
 683
 684     d_GetLastInsertedKeyIdQuery_stmt->execute();
 685     if (!d_GetLastInsertedKeyIdQuery_stmt->hasNextRow()) {
 686       id = -2;
 687       return true;
 688     }
 689     SSqlStatement::row_t row;
 690     d_GetLastInsertedKeyIdQuery_stmt->nextRow(row);
 691     ASSERT_ROW_COLUMNS("get-last-inserted-key-id-query", row, 1);
 692     id = std::stoi(row[0]);
 693     d_GetLastInsertedKeyIdQuery_stmt->reset();
 694     return true;
 695   }
 696   catch (SSqlException &e) {
 697     id = -2;
 698     return true;
 699   }
 700
 701   return false;
 702 }
 703
 704 bool GSQLBackend::activateDomainKey(const DNSName& name, unsigned int id)
 705 {
 706   if(!d_dnssecQueries)
 707     return false;
 708
 709   try {
 710     reconnectIfNeeded();
 711
 712     d_ActivateDomainKeyQuery_stmt->
 713       bind("domain", name)->
 714       bind("key_id", id)->
 715       execute()->
 716       reset();
 717   }
 718   catch (SSqlException &e) {
 719     throw PDNSException("GSQLBackend unable to activate key: "+e.txtReason());
 720   }
 721   return true;
 722 }
 723
 724 bool GSQLBackend::deactivateDomainKey(const DNSName& name, unsigned int id)
 725 {
 726   if(!d_dnssecQueries)
 727     return false;
 728
 729   try {
 730     reconnectIfNeeded();
 731
 732     d_DeactivateDomainKeyQuery_stmt->
 733       bind("domain", name)->
 734       bind("key_id", id)->
 735       execute()->
 736       reset();
 737   }
 738   catch (SSqlException &e) {
 739     throw PDNSException("GSQLBackend unable to deactivate key: "+e.txtReason());
 740   }
 741   return true;
 742 }
 743
 744 bool GSQLBackend::removeDomainKey(const DNSName& name, unsigned int id)
 745 {
 746   if(!d_dnssecQueries)
 747     return false;
 748
 749   try {
 750     reconnectIfNeeded();
 751
 752     d_RemoveDomainKeyQuery_stmt->
 753       bind("domain", name)->
 754       bind("key_id", id)->
 755       execute()->
 756       reset();
 757   }
 758   catch (SSqlException &e) {
 759     throw PDNSException("GSQLBackend unable to remove key: "+e.txtReason());
 760   }
 761   return true;
 762 }
 763
 764 bool GSQLBackend::getTSIGKey(const DNSName& name, DNSName* algorithm, string* content)
 765 {
 766   try {
 767     reconnectIfNeeded();
 768
 769     d_getTSIGKeyQuery_stmt->
 770       bind("key_name", name)->
 771       execute();
 772
 773     SSqlStatement::row_t row;
 774
 775     content->clear();
 776     while(d_getTSIGKeyQuery_stmt->hasNextRow()) {
 777       d_getTSIGKeyQuery_stmt->nextRow(row);
 778       ASSERT_ROW_COLUMNS("get-tsig-key-query", row, 2);
 779       try{
 780         if(algorithm->empty() || *algorithm==DNSName(row[0])) {
 781           *algorithm = DNSName(row[0]);
 782           *content = row[1];
 783         }
 784       } catch (...) {}
 785     }
 786
 787     d_getTSIGKeyQuery_stmt->reset();
 788   }
 789   catch (SSqlException &e) {
 790     throw PDNSException("GSQLBackend unable to retrieve named TSIG key: "+e.txtReason());
 791   }
 792
 793   return !content->empty();
 794 }
 795
 796 bool GSQLBackend::setTSIGKey(const DNSName& name, const DNSName& algorithm, const string& content)
 797 {
 798   try {
 799     reconnectIfNeeded();
 800
 801     d_setTSIGKeyQuery_stmt->
 802       bind("key_name", name)->
 803       bind("algorithm", algorithm)->
 804       bind("content", content)->
 805       execute()->
 806       reset();
 807   }
 808   catch (SSqlException &e) {
 809     throw PDNSException("GSQLBackend unable to store named TSIG key: "+e.txtReason());
 810   }
 811   return true;
 812 }
 813
 814 bool GSQLBackend::deleteTSIGKey(const DNSName& name)
 815 {
 816   try {
 817     reconnectIfNeeded();
 818
 819     d_deleteTSIGKeyQuery_stmt->
 820       bind("key_name", name)->
 821       execute()->
 822       reset();
 823   }
 824   catch (SSqlException &e) {
 825     throw PDNSException("GSQLBackend unable to store named TSIG key: "+e.txtReason());
 826   }
 827   return true;
 828 }
 829
 830 bool GSQLBackend::getTSIGKeys(std::vector< struct TSIGKey > &keys)
 831 {
 832   try {
 833     reconnectIfNeeded();
 834
 835     d_getTSIGKeysQuery_stmt->
 836       execute();
 837
 838     SSqlStatement::row_t row;
 839
 840     while(d_getTSIGKeysQuery_stmt->hasNextRow()) {
 841       d_getTSIGKeysQuery_stmt->nextRow(row);
 842       ASSERT_ROW_COLUMNS("get-tsig-keys-query", row, 3);
 843       struct TSIGKey key;
 844       try {
 845         key.name = DNSName(row[0]);
 846         key.algorithm = DNSName(row[1]);
 847       } catch (...) {
 848         continue;
 849       }
 850       key.key = row[2];
 851       keys.push_back(key);
 852     }
 853
 854     d_getTSIGKeysQuery_stmt->reset();
 855   }
 856   catch (SSqlException &e) {
 857     throw PDNSException("GSQLBackend unable to retrieve TSIG keys: "+e.txtReason());
 858   }
 859
 860   return keys.empty();
 861 }
 862
 863 bool GSQLBackend::getDomainKeys(const DNSName& name, std::vector<KeyData>& keys)
 864 {
 865   if(!d_dnssecQueries)
 866     return false;
 867
 868   try {
 869     reconnectIfNeeded();
 870
 871     d_ListDomainKeysQuery_stmt->
 872       bind("domain", name)->
 873       execute();
 874
 875     SSqlStatement::row_t row;
 876     KeyData kd;
 877     while(d_ListDomainKeysQuery_stmt->hasNextRow()) {
 878       d_ListDomainKeysQuery_stmt->nextRow(row);
 879       ASSERT_ROW_COLUMNS("list-domain-keys-query", row, 4);
 880       //~ for(const auto& val: row) {
 881         //~ cerr<<"'"<<val<<"'"<<endl;
 882       //~ }
 883       kd.id = pdns_stou(row[0]);
 884       kd.flags = pdns_stou(row[1]);
 885       kd.active = row[2] == "1";
 886       kd.content = row[3];
 887       keys.push_back(kd);
 888     }
 889
 890     d_ListDomainKeysQuery_stmt->reset();
 891   }
 892   catch (SSqlException &e) {
 893     throw PDNSException("GSQLBackend unable to list keys: "+e.txtReason());
 894   }
 895
 896   return true;
 897 }
 898
 899 void GSQLBackend::alsoNotifies(const DNSName &domain, set<string> *ips)
 900 {
 901   vector<string> meta;
 902   getDomainMetadata(domain, "ALSO-NOTIFY", meta);
 903   for(const auto& str: meta) {
 904     ips->insert(str);
 905   }
 906 }
 907
 908 bool GSQLBackend::getAllDomainMetadata(const DNSName& name, std::map<std::string, std::vector<std::string> >& meta)
 909 {
 910   try {
 911     reconnectIfNeeded();
 912
 913     d_GetAllDomainMetadataQuery_stmt->
 914       bind("domain", name)->
 915       execute();
 916
 917     SSqlStatement::row_t row;
 918
 919     while(d_GetAllDomainMetadataQuery_stmt->hasNextRow()) {
 920       d_GetAllDomainMetadataQuery_stmt->nextRow(row);
 921       ASSERT_ROW_COLUMNS("get-all-domain-metadata-query", row, 2);
 922
 923       if (!isDnssecDomainMetadata(row[0]))
 924         meta[row[0]].push_back(row[1]);
 925     }
 926
 927     d_GetAllDomainMetadataQuery_stmt->reset();
 928   }
 929   catch (SSqlException &e) {
 930     throw PDNSException("GSQLBackend unable to list metadata: "+e.txtReason());
 931   }
 932
 933   return true;
 934 }
 935
 936
 937 bool GSQLBackend::getDomainMetadata(const DNSName& name, const std::string& kind, std::vector<std::string>& meta)
 938 {
 939   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
 940     return false;
 941
 942   try {
 943     reconnectIfNeeded();
 944
 945     d_GetDomainMetadataQuery_stmt->
 946       bind("domain", name)->
 947       bind("kind", kind)->
 948       execute();
 949
 950     SSqlStatement::row_t row;
 951
 952     while(d_GetDomainMetadataQuery_stmt->hasNextRow()) {
 953       d_GetDomainMetadataQuery_stmt->nextRow(row);
 954       ASSERT_ROW_COLUMNS("get-domain-metadata-query", row, 1);
 955       meta.push_back(row[0]);
 956     }
 957
 958     d_GetDomainMetadataQuery_stmt->reset();
 959   }
 960   catch (SSqlException &e) {
 961     throw PDNSException("GSQLBackend unable to list metadata: "+e.txtReason());
 962   }
 963
 964   return true;
 965 }
 966
 967 bool GSQLBackend::setDomainMetadata(const DNSName& name, const std::string& kind, const std::vector<std::string>& meta)
 968 {
 969   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
 970     return false;
 971
 972   try {
 973     reconnectIfNeeded();
 974
 975     d_ClearDomainMetadataQuery_stmt->
 976       bind("domain", name)->
 977       bind("kind", kind)->
 978       execute()->
 979       reset();
 980     if(!meta.empty()) {
 981       for(const auto& value: meta) {
 982          d_SetDomainMetadataQuery_stmt->
 983            bind("kind", kind)->
 984            bind("content", value)->
 985            bind("domain", name)->
 986            execute()->
 987            reset();
 988       }
 989     }
 990   }
 991   catch (SSqlException &e) {
 992     throw PDNSException("GSQLBackend unable to store metadata key: "+e.txtReason());
 993   }
 994
 995   return true;
 996 }
 997
 998 void GSQLBackend::lookup(const QType &qtype,const DNSName &qname, DNSPacket *pkt_p, int domain_id)
 999 {
1000   try {
1001     reconnectIfNeeded();
1002
1003     if(qtype.getCode()!=QType::ANY) {
1004       if(domain_id < 0) {
1005         d_query_name = "basic-query";
1006         d_query_stmt = &d_NoIdQuery_stmt;
1007         (*d_query_stmt)->
1008           bind("qtype", qtype.getName())->
1009           bind("qname", qname);
1010       } else {
1011         d_query_name = "id-query";
1012         d_query_stmt = &d_IdQuery_stmt;
1013         (*d_query_stmt)->
1014           bind("qtype", qtype.getName())->
1015           bind("qname", qname)->
1016           bind("domain_id", domain_id);
1017       }
1018     } else {
1019       // qtype==ANY
1020       if(domain_id < 0) {
1021         d_query_name = "any-query";
1022         d_query_stmt = &d_ANYNoIdQuery_stmt;
1023         (*d_query_stmt)->
1024           bind("qname", qname);
1025       } else {
1026         d_query_name = "any-id-query";
1027         d_query_stmt = &d_ANYIdQuery_stmt;
1028         (*d_query_stmt)->
1029           bind("qname", qname)->
1030           bind("domain_id", domain_id);
1031       }
1032     }
1033
1034     (*d_query_stmt)->
1035       execute();
1036   }
1037   catch(SSqlException &e) {
1038     throw PDNSException("GSQLBackend lookup query:"+e.txtReason());
1039   }
1040
1041   d_qname=qname;
1042 }
1043
1044 bool GSQLBackend::list(const DNSName &target, int domain_id, bool include_disabled)
1045 {
1046   DLOG(g_log<<"GSQLBackend constructing handle for list of domain id '"<<domain_id<<"'"<<endl);
1047
1048   try {
1049     reconnectIfNeeded();
1050
1051     d_query_name = "list-query";
1052     d_query_stmt = &d_listQuery_stmt;
1053     (*d_query_stmt)->
1054       bind("include_disabled", (int)include_disabled)->
1055       bind("domain_id", domain_id)->
1056       execute();
1057   }
1058   catch(SSqlException &e) {
1059     throw PDNSException("GSQLBackend list query: "+e.txtReason());
1060   }
1061
1062   d_qname.clear();
1063   return true;
1064 }
1065
1066 bool GSQLBackend::listSubZone(const DNSName &zone, int domain_id) {
1067
1068   string wildzone = "%." + zone.makeLowerCase().toStringNoDot();
1069
1070   try {
1071     reconnectIfNeeded();
1072
1073     d_query_name = "list-subzone-query";
1074     d_query_stmt = &d_listSubZoneQuery_stmt;
1075     (*d_query_stmt)->
1076       bind("zone", zone)->
1077       bind("wildzone", wildzone)->
1078       bind("domain_id", domain_id)->
1079       execute();
1080   }
1081   catch(SSqlException &e) {
1082     throw PDNSException("GSQLBackend listSubZone query: "+e.txtReason());
1083   }
1084   d_qname.clear();
1085   return true;
1086 }
1087
1088 bool GSQLBackend::get(DNSResourceRecord &r)
1089 {
1090   // g_log << "GSQLBackend get() was called for "<<qtype.getName() << " record: ";
1091   SSqlStatement::row_t row;
1092
1093 skiprow:
1094   if((*d_query_stmt)->hasNextRow()) {
1095     try {
1096       (*d_query_stmt)->nextRow(row);
1097       ASSERT_ROW_COLUMNS(d_query_name, row, 8);
1098     } catch (SSqlException &e) {
1099       throw PDNSException("GSQLBackend get: "+e.txtReason());
1100     }
1101     try {
1102       extractRecord(row, r);
1103     } catch (...) {
1104       goto skiprow;
1105     }
1106     return true;
1107   }
1108
1109   try {
1110     (*d_query_stmt)->reset();
1111   } catch (SSqlException &e) {
1112       throw PDNSException("GSQLBackend get: "+e.txtReason());
1113   }
1114   d_query_stmt = NULL;
1115   return false;
1116 }
1117
1118 bool GSQLBackend::superMasterBackend(const string &ip, const DNSName &domain, const vector<DNSResourceRecord>&nsset, string *nameserver, string *account, DNSBackend **ddb)
1119 {
1120   // check if we know the ip/ns couple in the database
1121   for(vector<DNSResourceRecord>::const_iterator i=nsset.begin();i!=nsset.end();++i) {
1122     try {
1123       reconnectIfNeeded();
1124
1125       d_SuperMasterInfoQuery_stmt->
1126         bind("ip", ip)->
1127         bind("nameserver", i->content)->
1128         execute()->
1129         getResult(d_result)->
1130         reset();
1131     }
1132     catch (SSqlException &e) {
1133       throw PDNSException("GSQLBackend unable to search for a domain: "+e.txtReason());
1134     }
1135     if(!d_result.empty()) {
1136       ASSERT_ROW_COLUMNS("supermaster-query", d_result[0], 1);
1137       *nameserver=i->content;
1138       *account=d_result[0][0];
1139       *ddb=this;
1140       return true;
1141     }
1142   }
1143   return false;
1144 }
1145
1146 bool GSQLBackend::createDomain(const DNSName &domain, const string &type, const string &masters, const string &account)
1147 {
1148   try {
1149     reconnectIfNeeded();
1150
1151     d_InsertZoneQuery_stmt->
1152       bind("type", type)->
1153       bind("domain", domain)->
1154       bind("masters", masters)->
1155       bind("account", account)->
1156       execute()->
1157       reset();
1158   }
1159   catch(SSqlException &e) {
1160     throw PDNSException("Database error trying to insert new domain '"+domain.toLogString()+"': "+ e.txtReason());
1161   }
1162   return true;
1163 }
1164
1165 bool GSQLBackend::createSlaveDomain(const string &ip, const DNSName &domain, const string &nameserver, const string &account)
1166 {
1167   string name;
1168   string masters(ip);
1169   try {
1170     if (!nameserver.empty()) {
1171       // figure out all IP addresses for the master
1172       reconnectIfNeeded();
1173
1174       d_GetSuperMasterIPs_stmt->
1175         bind("nameserver", nameserver)->
1176         bind("account", account)->
1177         execute()->
1178         getResult(d_result)->
1179         reset();
1180       if (!d_result.empty()) {
1181         // collect all IP addresses
1182         vector<string> tmp;
1183         for(const auto& row: d_result) {
1184           if (account == row[1])
1185             tmp.push_back(row[0]);
1186         }
1187         // set them as domain's masters, comma separated
1188         masters = boost::join(tmp, ", ");
1189       }
1190     }
1191     createDomain(domain, "SLAVE", masters, account);
1192   }
1193   catch(SSqlException &e) {
1194     throw PDNSException("Database error trying to insert new slave domain '"+domain.toLogString()+"': "+ e.txtReason());
1195   }
1196   return true;
1197 }
1198
1199 bool GSQLBackend::deleteDomain(const DNSName &domain)
1200 {
1201   DomainInfo di;
1202   if (!getDomainInfo(domain, di)) {
1203     return false;
1204   }
1205
1206   try {
1207     reconnectIfNeeded();
1208
1209     d_DeleteZoneQuery_stmt->
1210       bind("domain_id", di.id)->
1211       execute()->
1212       reset();
1213     d_ClearDomainAllMetadataQuery_stmt->
1214       bind("domain", domain)->
1215       execute()->
1216       reset();
1217     d_ClearDomainAllKeysQuery_stmt->
1218       bind("domain", domain)->
1219       execute()->
1220       reset();
1221     d_DeleteCommentsQuery_stmt->
1222       bind("domain_id", di.id)->
1223       execute()->
1224       reset();
1225     d_DeleteDomainQuery_stmt->
1226       bind("domain", domain)->
1227       execute()->
1228       reset();
1229   }
1230   catch(SSqlException &e) {
1231     throw PDNSException("Database error trying to delete domain '"+domain.toLogString()+"': "+ e.txtReason());
1232   }
1233   return true;
1234 }
1235
1236 void GSQLBackend::getAllDomains(vector<DomainInfo> *domains, bool include_disabled)
1237 {
1238   DLOG(g_log<<"GSQLBackend retrieving all domains."<<endl);
1239
1240   try {
1241     reconnectIfNeeded();
1242
1243     d_getAllDomainsQuery_stmt->
1244       bind("include_disabled", (int)include_disabled)->
1245       execute();
1246
1247     SSqlStatement::row_t row;
1248     while (d_getAllDomainsQuery_stmt->hasNextRow()) {
1249       d_getAllDomainsQuery_stmt->nextRow(row);
1250       ASSERT_ROW_COLUMNS("get-all-domains-query", row, 8);
1251       DomainInfo di;
1252       di.id = pdns_stou(row[0]);
1253       try {
1254         di.zone = DNSName(row[1]);
1255       } catch (...) {
1256         continue;
1257       }
1258
1259       if (!row[4].empty()) {
1260         vector<string> masters;
1261         stringtok(masters, row[4], " ,\t");
1262         for(const auto& m : masters)
1263           di.masters.emplace_back(m, 53);
1264       }
1265
1266       SOAData sd;
1267       fillSOAData(row[2], sd);
1268       di.serial = sd.serial;
1269       di.notified_serial = pdns_stou(row[5]);
1270       di.last_check = pdns_stou(row[6]);
1271       di.account = row[7];
1272
1273       if (pdns_iequals(row[3], "MASTER"))
1274         di.kind = DomainInfo::Master;
1275       else if (pdns_iequals(row[3], "SLAVE"))
1276         di.kind = DomainInfo::Slave;
1277       else
1278         di.kind = DomainInfo::Native;
1279
1280       di.backend = this;
1281
1282       domains->push_back(di);
1283     }
1284     d_getAllDomainsQuery_stmt->reset();
1285   }
1286   catch (SSqlException &e) {
1287     throw PDNSException("Database error trying to retrieve all domains:" + e.txtReason());
1288   }
1289 }
1290
1291 bool GSQLBackend::replaceRRSet(uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<DNSResourceRecord>& rrset)
1292 {
1293   try {
1294     reconnectIfNeeded();
1295
1296     if (qt != QType::ANY) {
1297       d_DeleteRRSetQuery_stmt->
1298         bind("domain_id", domain_id)->
1299         bind("qname", qname)->
1300         bind("qtype", qt.getName())->
1301         execute()->
1302         reset();
1303     } else {
1304       d_DeleteNamesQuery_stmt->
1305         bind("domain_id", domain_id)->
1306         bind("qname", qname)->
1307         execute()->
1308         reset();
1309     }
1310   }
1311   catch (SSqlException &e) {
1312     throw PDNSException("GSQLBackend unable to delete RRSet: "+e.txtReason());
1313   }
1314
1315   if (rrset.empty()) {
1316     try {
1317       reconnectIfNeeded();
1318
1319       d_DeleteCommentRRsetQuery_stmt->
1320         bind("domain_id", domain_id)->
1321         bind("qname", qname)->
1322         bind("qtype", qt.getName())->
1323         execute()->
1324         reset();
1325     }
1326     catch (SSqlException &e) {
1327       throw PDNSException("GSQLBackend unable to delete comment: "+e.txtReason());
1328     }
1329   }
1330   for(const auto& rr: rrset) {
1331     feedRecord(rr, DNSName());
1332   }
1333
1334   return true;
1335 }
1336
1337 bool GSQLBackend::feedRecord(const DNSResourceRecord &r, const DNSName &ordername)
1338 {
1339   int prio=0;
1340   string content(r.content);
1341   if (r.qtype == QType::MX || r.qtype == QType::SRV) {
1342     string::size_type pos = content.find_first_not_of("0123456789");
1343     if (pos != string::npos) {
1344       prio=pdns_stou(content.substr(0,pos));
1345       boost::erase_head(content, pos);
1346     }
1347     trim_left(content);
1348   }
1349
1350   try {
1351     reconnectIfNeeded();
1352
1353     d_InsertRecordQuery_stmt->
1354       bind("content",content)->
1355       bind("ttl",r.ttl)->
1356       bind("priority",prio)->
1357       bind("qtype",r.qtype.getName())->
1358       bind("domain_id",r.domain_id)->
1359       bind("disabled",r.disabled)->
1360       bind("qname",r.qname);
1361
1362     if (!ordername.empty())
1363       d_InsertRecordQuery_stmt->bind("ordername", ordername.labelReverse().makeLowerCase().toString(" ", false));
1364     else
1365       d_InsertRecordQuery_stmt->bindNull("ordername");
1366
1367     if (d_dnssecQueries)
1368       d_InsertRecordQuery_stmt->bind("auth", r.auth);
1369     else
1370       d_InsertRecordQuery_stmt->bind("auth", true);
1371
1372     d_InsertRecordQuery_stmt->
1373       execute()->
1374       reset();
1375   }
1376   catch (SSqlException &e) {
1377     throw PDNSException("GSQLBackend unable to feed record: "+e.txtReason());
1378   }
1379   return true; // XXX FIXME this API should not return 'true' I think -ahu
1380 }
1381
1382 bool GSQLBackend::feedEnts(int domain_id, map<DNSName,bool>& nonterm)
1383 {
1384   for(const auto& nt: nonterm) {
1385     try {
1386       reconnectIfNeeded();
1387
1388       d_InsertEmptyNonTerminalOrderQuery_stmt->
1389         bind("domain_id",domain_id)->
1390         bind("qname", nt.first)->
1391         bindNull("ordername")->
1392         bind("auth",(nt.second || !d_dnssecQueries))->
1393         execute()->
1394         reset();
1395     }
1396     catch (SSqlException &e) {
1397       throw PDNSException("GSQLBackend unable to feed empty non-terminal: "+e.txtReason());
1398     }
1399   }
1400   return true;
1401 }
1402
1403 bool GSQLBackend::feedEnts3(int domain_id, const DNSName &domain, map<DNSName,bool> &nonterm, const NSEC3PARAMRecordContent& ns3prc, bool narrow)
1404 {
1405   if(!d_dnssecQueries)
1406       return false;
1407
1408   string ordername;
1409
1410   for(const auto& nt: nonterm) {
1411     try {
1412       reconnectIfNeeded();
1413
1414       d_InsertEmptyNonTerminalOrderQuery_stmt->
1415         bind("domain_id",domain_id)->
1416         bind("qname", nt.first);
1417       if (narrow || !nt.second) {
1418         d_InsertEmptyNonTerminalOrderQuery_stmt->
1419           bindNull("ordername");
1420       } else {
1421         ordername=toBase32Hex(hashQNameWithSalt(ns3prc, nt.first));
1422         d_InsertEmptyNonTerminalOrderQuery_stmt->
1423           bind("ordername", ordername);
1424       }
1425       d_InsertEmptyNonTerminalOrderQuery_stmt->
1426         bind("auth",nt.second)->
1427         execute()->
1428         reset();
1429     }
1430     catch (SSqlException &e) {
1431       throw PDNSException("GSQLBackend unable to feed empty non-terminal: "+e.txtReason());
1432     }
1433   }
1434   return true;
1435 }
1436
1437 bool GSQLBackend::startTransaction(const DNSName &domain, int domain_id)
1438 {
1439   try {
1440     reconnectIfNeeded();
1441
1442     d_db->startTransaction();
1443     d_inTransaction = true;
1444     if(domain_id >= 0) {
1445       d_DeleteZoneQuery_stmt->
1446         bind("domain_id", domain_id)->
1447         execute()->
1448         reset();
1449     }
1450   }
1451   catch (SSqlException &e) {
1452     d_inTransaction = false;
1453     throw PDNSException("Database failed to start transaction: "+e.txtReason());
1454   }
1455
1456   return true;
1457 }
1458
1459 bool GSQLBackend::commitTransaction()
1460 {
1461   try {
1462     d_db->commit();
1463     d_inTransaction = false;
1464   }
1465   catch (SSqlException &e) {
1466     d_inTransaction = false;
1467     throw PDNSException("Database failed to commit transaction: "+e.txtReason());
1468   }
1469   return true;
1470 }
1471
1472 bool GSQLBackend::abortTransaction()
1473 {
1474   try {
1475     d_db->rollback();
1476     d_inTransaction = false;
1477   }
1478   catch(SSqlException &e) {
1479     d_inTransaction = false;
1480     throw PDNSException("Database failed to abort transaction: "+string(e.txtReason()));
1481   }
1482   return true;
1483 }
1484
1485 bool GSQLBackend::listComments(const uint32_t domain_id)
1486 {
1487   try {
1488     reconnectIfNeeded();
1489
1490     d_query_name = "list-comments-query";
1491     d_query_stmt = &d_ListCommentsQuery_stmt;
1492     (*d_query_stmt)->
1493       bind("domain_id", domain_id)->
1494       execute();
1495   }
1496   catch(SSqlException &e) {
1497     throw PDNSException("GSQLBackend list comments query: "+e.txtReason());
1498   }
1499
1500   return true;
1501 }
1502
1503 bool GSQLBackend::getComment(Comment& comment)
1504 {
1505   SSqlStatement::row_t row;
1506
1507   for(;;) {
1508     if (!(*d_query_stmt)->hasNextRow()) {
1509       try {
1510         (*d_query_stmt)->reset();
1511       } catch(SSqlException &e) {
1512         throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1513       }
1514       d_query_stmt = NULL;
1515       return false;
1516     }
1517
1518     try {
1519       (*d_query_stmt)->nextRow(row);
1520       ASSERT_ROW_COLUMNS(d_query_name, row, 6);
1521     } catch(SSqlException &e) {
1522       throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1523     }
1524     try {
1525       extractComment(row, comment);
1526     } catch (...) {
1527       continue;
1528     }
1529     return true;
1530   }
1531 }
1532
1533 void GSQLBackend::feedComment(const Comment& comment)
1534 {
1535   try {
1536     reconnectIfNeeded();
1537
1538     d_InsertCommentQuery_stmt->
1539       bind("domain_id",comment.domain_id)->
1540       bind("qname",comment.qname)->
1541       bind("qtype",comment.qtype.getName())->
1542       bind("modified_at",comment.modified_at)->
1543       bind("account",comment.account)->
1544       bind("content",comment.content)->
1545       execute()->
1546       reset();
1547   }
1548   catch (SSqlException &e) {
1549     throw PDNSException("GSQLBackend unable to feed comment: "+e.txtReason());
1550   }
1551 }
1552
1553 bool GSQLBackend::replaceComments(const uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<Comment>& comments)
1554 {
1555   try {
1556     reconnectIfNeeded();
1557
1558     d_DeleteCommentRRsetQuery_stmt->
1559       bind("domain_id",domain_id)->
1560       bind("qname", qname)->
1561       bind("qtype",qt.getName())->
1562       execute()->
1563       reset();
1564   }
1565   catch (SSqlException &e) {
1566     throw PDNSException("GSQLBackend unable to delete comment: "+e.txtReason());
1567   }
1568
1569   for(const auto& comment: comments) {
1570     feedComment(comment);
1571   }
1572
1573   return true;
1574 }
1575
1576 string GSQLBackend::directBackendCmd(const string &query)
1577 {
1578  try {
1579    ostringstream out;
1580
1581    auto stmt = d_db->prepare(query,0);
1582
1583    reconnectIfNeeded();
1584
1585    stmt->execute();
1586
1587    SSqlStatement::row_t row;
1588
1589    while(stmt->hasNextRow()) {
1590      stmt->nextRow(row);
1591      for(const auto& col: row)
1592        out<<"\'"<<col<<"\'\t";
1593      out<<endl;
1594    }
1595
1596    return out.str();
1597  }
1598  catch (SSqlException &e) {
1599    throw PDNSException("GSQLBackend unable to execute query: "+e.txtReason());
1600  }
1601 }
1602
1603 string GSQLBackend::pattern2SQLPattern(const string &pattern)
1604 {
1605   string escaped_pattern = boost::replace_all_copy(pattern,"\\","\\\\");
1606   boost::replace_all(escaped_pattern,"_","\\_");
1607   boost::replace_all(escaped_pattern,"%","\\%");
1608   boost::replace_all(escaped_pattern,"*","%");
1609   boost::replace_all(escaped_pattern,"?","_");
1610   return escaped_pattern;
1611 }
1612
1613 bool GSQLBackend::searchRecords(const string &pattern, int maxResults, vector<DNSResourceRecord>& result)
1614 {
1615   d_qname.clear();
1616   try {
1617     string escaped_pattern = pattern2SQLPattern(pattern);
1618
1619     reconnectIfNeeded();
1620
1621     d_SearchRecordsQuery_stmt->
1622       bind("value", escaped_pattern)->
1623       bind("value2", escaped_pattern)->
1624       bind("limit", maxResults)->
1625       execute();
1626
1627     while(d_SearchRecordsQuery_stmt->hasNextRow())
1628     {
1629       SSqlStatement::row_t row;
1630       DNSResourceRecord r;
1631       d_SearchRecordsQuery_stmt->nextRow(row);
1632       ASSERT_ROW_COLUMNS("search-records-query", row, 8);
1633       try {
1634         extractRecord(row, r);
1635       } catch (...) {
1636         continue;
1637       }
1638       result.push_back(r);
1639     }
1640
1641     d_SearchRecordsQuery_stmt->reset();
1642
1643     return true;
1644   }
1645   catch (SSqlException &e) {
1646     throw PDNSException("GSQLBackend unable to execute query: "+e.txtReason());
1647   }
1648
1649   return false;
1650 }
1651
1652 bool GSQLBackend::searchComments(const string &pattern, int maxResults, vector<Comment>& result)
1653 {
1654   Comment c;
1655   try {
1656     string escaped_pattern = pattern2SQLPattern(pattern);
1657
1658     reconnectIfNeeded();
1659
1660     d_SearchCommentsQuery_stmt->
1661       bind("value", escaped_pattern)->
1662       bind("value2", escaped_pattern)->
1663       bind("limit", maxResults)->
1664       execute();
1665
1666     while(d_SearchCommentsQuery_stmt->hasNextRow()) {
1667       SSqlStatement::row_t row;
1668       d_SearchCommentsQuery_stmt->nextRow(row);
1669       ASSERT_ROW_COLUMNS("search-comments-query", row, 6);
1670       Comment comment;
1671       extractComment(row, comment);
1672       result.push_back(comment);
1673     }
1674
1675     d_SearchRecordsQuery_stmt->reset();
1676
1677     return true;
1678   }
1679   catch (SSqlException &e) {
1680     throw PDNSException("GSQLBackend unable to execute query: "+e.txtReason());
1681   }
1682
1683   return false;
1684 }
1685
1686 void GSQLBackend::extractRecord(const SSqlStatement::row_t& row, DNSResourceRecord& r)
1687 {
1688   if (row[1].empty())
1689       r.ttl = ::arg().asNum( "default-ttl" );
1690   else
1691       r.ttl=pdns_stou(row[1]);
1692   if(!d_qname.empty())
1693     r.qname=d_qname;
1694   else
1695     r.qname=DNSName(row[6]);
1696
1697   r.qtype=row[3];
1698
1699   if (r.qtype==QType::MX || r.qtype==QType::SRV)
1700     r.content=row[2]+" "+row[0];
1701   else
1702     r.content=row[0];
1703
1704   r.last_modified=0;
1705
1706   if(d_dnssecQueries)
1707     r.auth = !row[7].empty() && row[7][0]=='1';
1708   else
1709     r.auth = 1;
1710
1711   r.disabled = !row[5].empty() && row[5][0]=='1';
1712
1713   r.domain_id=pdns_stou(row[4]);
1714 }
1715
1716 void GSQLBackend::extractComment(const SSqlStatement::row_t& row, Comment& comment)
1717 {
1718   comment.domain_id = pdns_stou(row[0]);
1719   comment.qname = DNSName(row[1]);
1720   comment.qtype = row[2];
1721   comment.modified_at = pdns_stou(row[3]);
1722   comment.account = row[4];
1723   comment.content = row[5];
1724 }
1725
1726 SSqlStatement::~SSqlStatement() {
1727 // make sure vtable won't break
1728 }