pgsql_lease_mgr.cc 53.9 KB
Newer Older
1
// Copyright (C) 2014 Internet Systems Consortium, Inc. ("ISC")
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
//
// Permission to use, copy, modify, and/or distribute this software for any
// purpose with or without fee is hereby granted, provided that the above
// copyright notice and this permission notice appear in all copies.
//
// THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH
// REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
// AND FITNESS.  IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT,
// INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
// LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
// OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
// PERFORMANCE OF THIS SOFTWARE.

#include <config.h>

#include <asiolink/io_address.h>
#include <dhcp/duid.h>
#include <dhcp/hwaddr.h>
#include <dhcpsrv/dhcpsrv_log.h>
#include <dhcpsrv/pgsql_lease_mgr.h>

#include <boost/static_assert.hpp>

#include <iostream>
#include <iomanip>
#include <sstream>
#include <string>
#include <time.h>

31 32
// PostgreSQL errors should be tested based on the SQL state code.  Each state
// code is 5 decimal, ASCII, digits, the first two define the category of
33
// error, the last three are the specific error.  PostgreSQL makes the state
34 35 36 37
// code as a char[5].  Macros for each code are defined in PostgreSQL's
// errorcodes.h, although they require a second macro, MAKE_SQLSTATE for
// completion.  PostgreSQL deliberately omits this macro from errocodes.h
// so callers can supply their own.
38
#define MAKE_SQLSTATE(ch1,ch2,ch3,ch4,ch5) {ch1,ch2,ch3,ch4,ch5}
39
#include <utils/errcodes.h>
40 41 42 43 44
const size_t STATECODE_LEN = 5;

// Currently the only one we care to look for is duplicate key.
const char DUPLICATE_KEY[] = ERRCODE_UNIQUE_VIOLATION;

45 46 47 48 49
using namespace isc;
using namespace isc::dhcp;
using namespace std;

namespace {
50

51
// Maximum number of parameters used in any single query
52
const size_t MAX_PARAMETERS_IN_QUERY = 13;
53

54
/// @brief  Defines a single query
55
struct TaggedStatement {
56 57

    /// Query index
58
    PgSqlLeaseMgr::StatementIndex index;
59 60

    /// Number of parameters for a given query
61
    int nbparams;
62 63 64

    /// @brief OID types
    ///
65
    /// Specify parameter types. See /usr/include/postgresql/catalog/pg_type.h.
66 67
    /// For some reason that header does not export those parameters.
    /// Those OIDs must match both input and output parameters.
68
    const Oid types[MAX_PARAMETERS_IN_QUERY];
69 70

    /// Short name of the query.
71
    const char* name;
72

73
    /// Text representation of the actual query.
74
    const char* text;
75 76
};

77 78 79
/// @brief Constants for PostgreSQL data types
/// This are defined by PostreSQL in <catalog/pg_type.h>, but including
/// this file is extrordinarily convoluted, so we'll use these to fill-in.
80
const size_t OID_NONE = 0;   // PostgreSQL infers proper type
81 82 83 84 85 86
const size_t OID_BOOL = 16;
const size_t OID_BYTEA = 17;
const size_t OID_INT8 = 20;  // 8 byte int
const size_t OID_INT2 = 21;  // 2 byte int
const size_t OID_TIMESTAMP = 1114;
const size_t OID_VARCHAR = 1043;
87

88 89 90
/// @brief Catalog of all the SQL statements currently supported.  Note
/// that the order columns appear in statement body must match the order they
/// that the occur in the table.  This does not apply to the where clause.
91 92
TaggedStatement tagged_statements[] = {
    {PgSqlLeaseMgr::DELETE_LEASE4, 1,
93
        { OID_INT8 },
94
        "delete_lease4",
95
        "DELETE FROM lease4 WHERE address = $1"},
96
    {PgSqlLeaseMgr::DELETE_LEASE6, 1,
97
        { OID_VARCHAR },
98
        "delete_lease6",
99
        "DELETE FROM lease6 WHERE address = $1"},
100
    {PgSqlLeaseMgr::GET_LEASE4_ADDR, 1,
101
        { OID_INT8 },
102
        "get_lease4_addr",
103 104 105 106 107
        "SELECT address, hwaddr, client_id, "
            "valid_lifetime, extract(epoch from expire)::bigint, subnet_id, "
            "fqdn_fwd, fqdn_rev, hostname "
            "FROM lease4 "
            "WHERE address = $1"},
108
    {PgSqlLeaseMgr::GET_LEASE4_CLIENTID, 1,
109
        { OID_BYTEA },
110
        "get_lease4_clientid",
111 112 113 114 115
        "SELECT address, hwaddr, client_id, "
            "valid_lifetime, extract(epoch from expire)::bigint, subnet_id, "
            "fqdn_fwd, fqdn_rev, hostname "
            "FROM lease4 "
            "WHERE client_id = $1"},
116
    {PgSqlLeaseMgr::GET_LEASE4_CLIENTID_SUBID, 2,
117
        { OID_BYTEA, OID_INT8 },
118
        "get_lease4_clientid_subid",
119 120 121 122 123
        "SELECT address, hwaddr, client_id, "
            "valid_lifetime, extract(epoch from expire)::bigint, subnet_id, "
            "fqdn_fwd, fqdn_rev, hostname "
            "FROM lease4 "
            "WHERE client_id = $1 AND subnet_id = $2"},
124
    {PgSqlLeaseMgr::GET_LEASE4_HWADDR, 1,
125 126 127 128 129 130 131
        { OID_BYTEA },
        "get_lease4_hwaddr",
        "SELECT address, hwaddr, client_id, "
            "valid_lifetime, extract(epoch from expire)::bigint, subnet_id, "
            "fqdn_fwd, fqdn_rev, hostname "
            "FROM lease4 "
            "WHERE hwaddr = $1"},
132
    {PgSqlLeaseMgr::GET_LEASE4_HWADDR_SUBID, 2,
133 134 135 136 137 138 139
        { OID_BYTEA, OID_INT8 },
        "get_lease4_hwaddr_subid",
        "SELECT address, hwaddr, client_id, "
            "valid_lifetime, extract(epoch from expire)::bigint, subnet_id, "
            "fqdn_fwd, fqdn_rev, hostname "
            "FROM lease4 "
            "WHERE hwaddr = $1 AND subnet_id = $2"},
140
    {PgSqlLeaseMgr::GET_LEASE6_ADDR, 2,
141
        { OID_VARCHAR, OID_INT2 },
142
        "get_lease6_addr",
143 144 145 146 147
        "SELECT address, duid, valid_lifetime, "
            "extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
            "lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname "
            "FROM lease6 "
            "WHERE address = $1 AND lease_type = $2"},
148
    {PgSqlLeaseMgr::GET_LEASE6_DUID_IAID, 3,
149
        { OID_BYTEA, OID_INT8, OID_INT2 },
150
        "get_lease6_duid_iaid",
151 152 153 154 155
        "SELECT address, duid, valid_lifetime, "
            "extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
            "lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname "
            "FROM lease6 "
            "WHERE duid = $1 AND iaid = $2 AND lease_type = $3"},
156
    {PgSqlLeaseMgr::GET_LEASE6_DUID_IAID_SUBID, 4,
157
        { OID_INT2, OID_BYTEA, OID_INT8, OID_INT8 },
158
        "get_lease6_duid_iaid_subid",
159 160 161 162 163 164
        "SELECT address, duid, valid_lifetime, "
            "extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
            "lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname "
            "FROM lease6 "
            "WHERE lease_type = $1 "
            "AND duid = $2 AND iaid = $3 AND subnet_id = $4"},
165
    {PgSqlLeaseMgr::GET_VERSION, 0,
166 167 168
        { OID_NONE },
        "get_version",
        "SELECT version, minor FROM schema_version"},
169
    {PgSqlLeaseMgr::INSERT_LEASE4, 9,
170 171 172 173 174 175
        { OID_INT8, OID_BYTEA, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
           OID_BOOL, OID_BOOL, OID_VARCHAR },
        "insert_lease4",
        "INSERT INTO lease4(address, hwaddr, client_id, "
            "valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname) "
            "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)"},
176
    {PgSqlLeaseMgr::INSERT_LEASE6, 12,
177 178 179
        { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
          OID_INT8, OID_INT2, OID_INT8, OID_INT2, OID_BOOL, OID_BOOL,
          OID_VARCHAR },
180
        "insert_lease6",
181 182 183 184
        "INSERT INTO lease6(address, duid, valid_lifetime, "
            "expire, subnet_id, pref_lifetime, "
            "lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname) "
            "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)"},
185
    {PgSqlLeaseMgr::UPDATE_LEASE4, 10,
186 187
        { OID_INT8, OID_BYTEA, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
          OID_BOOL, OID_BOOL, OID_VARCHAR, OID_INT8 },
188
        "update_lease4",
189 190 191 192
        "UPDATE lease4 SET address = $1, hwaddr = $2, "
            "client_id = $3, valid_lifetime = $4, expire = $5, "
            "subnet_id = $6, fqdn_fwd = $7, fqdn_rev = $8, hostname = $9 "
            "WHERE address = $10"},
Tomek Mrugalski's avatar
Tomek Mrugalski committed
193
    {PgSqlLeaseMgr::UPDATE_LEASE6, 13,
194 195 196
        { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8, OID_INT8,
          OID_INT2, OID_INT8, OID_INT2, OID_BOOL, OID_BOOL, OID_VARCHAR,
          OID_VARCHAR },
197
        "update_lease6",
198 199 200 201 202
        "UPDATE lease6 SET address = $1, duid = $2, "
            "valid_lifetime = $3, expire = $4, subnet_id = $5, "
            "pref_lifetime = $6, lease_type = $7, iaid = $8, "
            "prefix_len = $9, fqdn_fwd = $10, fqdn_rev = $11, hostname = $12 "
        "WHERE address = $13"},
203 204 205 206 207 208 209 210 211
    // End of list sentinel
    {PgSqlLeaseMgr::NUM_STATEMENTS, 0,  { 0 }, NULL, NULL}
};

};

namespace isc {
namespace dhcp {

212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
const int PsqlBindArray::TEXT_FMT = 0;
const int PsqlBindArray::BINARY_FMT = 1;
const char* PsqlBindArray::TRUE_STR = "TRUE";
const char* PsqlBindArray::FALSE_STR = "FALSE";

void PsqlBindArray::add(const char* value) {
    values_.push_back(value);
    lengths_.push_back(strlen(value));
    formats_.push_back(TEXT_FMT);
}

void PsqlBindArray::add(const std::string& value) {
    values_.push_back(value.c_str());
    lengths_.push_back(value.size());
    formats_.push_back(TEXT_FMT);
}

void PsqlBindArray::add(const std::vector<uint8_t>& data) {
    values_.push_back(reinterpret_cast<const char*>(&(data[0])));
    lengths_.push_back(data.size());
    formats_.push_back(BINARY_FMT);
}

void PsqlBindArray::add(const bool& value)  {
    add(value ? TRUE_STR : FALSE_STR);
}

239 240 241
std::string PsqlBindArray::toText() {
    std::ostringstream stream;
    for (int i = 0; i < values_.size(); ++i) {
242
        stream << i << " : ";
243
        if (formats_[i] == TEXT_FMT) {
244
            stream << "\"" << values_[i] << "\"" << std::endl;
245
        } else {
246 247 248 249 250 251 252 253 254 255 256
            const char *data = values_[i];
            if (lengths_[i] == 0) {
                stream << "empty" << std::endl;
            } else {
                stream << "0x";
                for (int i = 0; i < lengths_[i]; ++i) {
                    stream << setfill('0') << setw(2) << setbase(16)
                         << static_cast<unsigned int>(data[i]);
                }
                stream << std::endl;
            }
257 258 259 260 261 262
        }
    }

    return (stream.str());
}

263 264 265 266 267 268 269
/// @brief Base class for marshalling leases to and from PostgreSQL.
///
/// Provides the common functionality to set up binding information between
/// lease objects in the program and their database representation in the
/// database.
class PgSqlLeaseExchange {
public:
270

271 272 273 274
    PgSqlLeaseExchange()
        : addr_str_(""), valid_lifetime_(0), valid_lft_str_(""),
         expire_(0), expire_str_(""), subnet_id_(0), subnet_id_str_(""),
         cltt_(0), fqdn_fwd_(false), fqdn_rev_(false), hostname_("") {
275 276
    }

277
    virtual ~PgSqlLeaseExchange(){}
278

279
    /// @brief Converts time_t structure to a text representation in local time.
280 281 282
    ///
    /// The format of the output string is "%Y-%m-%d %H:%M:%S".  Database
    /// table columns using this value should be typed as TIMESTAMP WITH
283
    /// TIME ZONE. For such columns PostgreSQL assumes input strings without
284 285 286 287 288 289 290 291
    /// timezones should be treated as in local time and are converted to UTC
    /// when stored.  Likewise, these columns are automatically adjusted
    /// upon retrieval unless fetched via "extract(epoch from <column>))".
    ///
    /// @param time_val timestamp to be converted
    /// @return std::string containing the stringified time
    std::string
    convertToDatabaseTime(const time_t& time_val) {
292
        struct tm tinfo;
293 294 295 296
        char buffer[20];
        localtime_r(&time_val, &tinfo);
        strftime(buffer, sizeof(buffer), "%Y-%m-%d %H:%M:%S", &tinfo);
        return (std::string(buffer));
297
    }
298

299 300
    /// @brief Converts time stamp from the database to a time_t
    ///
301 302 303
    /// @param db_time_val timestamp to be converted.  This value
    /// is expected to be the number of seconds since the epoch
    /// expressed as base-10 integer string.
304
    /// @return Converted timestamp as time_t value.
305 306
    time_t convertFromDatabaseTime(const std::string& db_time_val) {
        // Convert string time value to time_t
307 308 309 310 311 312
        try  {
            return (boost::lexical_cast<time_t>(db_time_val));
        } catch (const std::exception& ex) {
            isc_throw(BadValue, "Database time value is invalid: "
                                << db_time_val);
        }
313 314
    }

315
    /// @brief Gets a pointer to the raw column value in a result set row
316
    ///
317 318 319 320 321 322 323 324 325 326 327 328
    /// Given a result set, row, and column return a const char* pointer to
    /// the data value in the result set.  The pointer is valid as long as
    /// the result set has not been freed.  It may point to text or binary
    /// data depending on how query was structured.  You should not attempt
    /// to free this pointer.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    ///
    /// @return a const char* pointer to the column's raw data
    /// @throw  DbOperationError if the value cannot be fetched.
329
    const char* getColumnValue(PGresult*& r, const int row, const size_t col) {
330 331 332 333 334 335 336 337 338
        const char* value = PQgetvalue(r, row, col);
        if (!value) {
            isc_throw(DbOperationError, "getColumnValue no data for :"
                      << getColumnLabel(col) << " row:" << row);
        }

        return (value);
    }

339 340 341 342 343 344 345 346 347
    /// @brief Converts a column in a row in a result set to a boolean.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    /// @param[out] value parameter to receive the converted value
    ///
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
348
    void getColumnValue(PGresult*& r, const int row, const size_t col,
349 350 351 352 353 354 355
                        bool &value) {
        const char* data = getColumnValue(r, row, col);
        if (!strlen(data) || *data == 'f') {
            value = false;
        } else if (*data == 't') {
            value = true;
        } else {
356 357 358
            isc_throw(DbOperationError, "Invalid boolean data: " << data
                      << " for: " << getColumnLabel(col) << " row:" << row
                      << " : must be 't' or 'f'");
359 360 361
        }
    }

362 363 364 365 366 367 368 369 370
    /// @brief Converts a column in a row in a result set to a uint32_t.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    /// @param[out] value parameter to receive the converted value
    ///
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
371
    void getColumnValue(PGresult*& r, const int row, const size_t col,
372 373 374 375 376
                        uint32_t &value) {
        const char* data = getColumnValue(r, row, col);
        try {
            value = boost::lexical_cast<uint32_t>(data);
        } catch (const std::exception& ex) {
377
            isc_throw(DbOperationError, "Invalid uint32_t data: " << data
378
                      << " for: " << getColumnLabel(col) << " row:" << row
379
                      << " : " << ex.what());
380 381
        }
    }
382

383 384 385 386 387 388 389 390 391
    /// @brief Converts a column in a row in a result set to a uint8_t.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    /// @param[out] value parameter to receive the converted value
    ///
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
392
    void getColumnValue(PGresult*& r, const int row, const size_t col,
393 394 395 396
                        uint8_t &value) {
        const char* data = getColumnValue(r, row, col);
        try {
            // lexically casting as uint8_t doesn't convert from char
397
            // so we use uint16_t and implicitly convert.
398 399
            value = boost::lexical_cast<uint16_t>(data);
        } catch (const std::exception& ex) {
400
            isc_throw(DbOperationError, "Invalid uint8_t data: " << data
401 402 403 404
                      << " for: " << getColumnLabel(col) << " row:" << row
                      << " : " << ex.what());
        }
    }
405

406 407 408 409 410 411 412 413 414
    /// @brief Converts a column in a row in a result set to a Lease6::Type
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    /// @param[out] value parameter to receive the converted value
    ///
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
415
    void getColumnValue(PGresult*& r, const int row, const size_t col,
416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435
                        Lease6::Type& value) {
        uint32_t raw_value = 0;
        getColumnValue(r, row , col, raw_value);
        switch (raw_value) {
            case Lease6::TYPE_NA:
                value = Lease6::TYPE_NA;
                break;

            case Lease6::TYPE_TA:
                value = Lease6::TYPE_TA;
                break;

            case Lease6::TYPE_PD:
                value = Lease6::TYPE_PD;
                break;

            default:
                isc_throw(DbOperationError, "Invalid lease type: " << raw_value
                      << " for: " << getColumnLabel(col) << " row:" << row);
        }
436 437
    }

438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453
    /// @brief Converts a column in a row in a result set to a binary bytes
    ///
    /// Method is used to convert columns stored as BYTEA into a buffer of
    /// binary bytes, (uint8_t).  It uses PQunescapeBytea to do the conversion.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    /// @param[out] buffer pre-allocated buffer to which the converted bytes
    /// will be stored.
    /// @param buffer_size size of the output buffer
    /// @param[out] bytes_converted number of bytes converted
    /// value
    ///
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
454
    void convertFromBytea(PGresult*& r, const int row, const size_t col,
455 456 457 458 459 460 461 462
                          uint8_t* buffer,
                          const size_t buffer_size, size_t &bytes_converted) {

        // Returns converted bytes in a dynamically allocated buffer, and
        // sets bytes_converted.
        unsigned char* bytes = PQunescapeBytea((const unsigned char*)
                                               (getColumnValue(r, row, col)),
                                               &bytes_converted);
463

464
        // Unlikely it couldn't allocate it but you never know.
465 466 467 468 469
        if (!bytes) {
            isc_throw (DbOperationError, "PQunescapeBytea failed for:"
                       << getColumnLabel(col) << " row:" << row);
        }

470 471 472
        // Make sure it's not larger than expected.
        if (bytes_converted > buffer_size) {
            // Free the allocated buffer first!
473
            PQfreemem(bytes);
474 475
            isc_throw (DbOperationError, "Converted data size: "
                       << bytes_converted << " is too large for: "
476 477 478
                       << getColumnLabel(col) << " row:" << row);
        }

479 480 481
        // Copy from the allocated buffer to caller's buffer the free up
        // the allocated buffer.
        memcpy(buffer, bytes, bytes_converted);
482 483 484
        PQfreemem(bytes);
    }

485 486 487
    /// @brief Returns column label given a column number
    std::string getColumnLabel(const size_t column) {
        if (column > columnLabels_.size()) {
488
            ostringstream os;
489
            os << "Unknown column:" << column;
490 491 492
            return (os.str());
        }

493
        return (columnLabels_[column]);
494
    }
495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515

protected:
    /// @brief Stores text labels for columns, currently only used for
    /// logging and errors.
    std::vector<std::string>columnLabels_;

    /// @brief Common Instance members used for binding and conversion
    //@{
    std::string addr_str_;
    uint32_t valid_lifetime_;
    std::string valid_lft_str_;
    time_t expire_;
    std::string expire_str_;
    uint32_t subnet_id_;
    std::string subnet_id_str_;
    time_t cltt_;
    bool fqdn_fwd_;
    bool fqdn_rev_;
    std::string hostname_;
    //@}

516 517
};

518

519
/// @brief Supports exchanging IPv4 leases with PostgreSQL.
520
class PgSqlLease4Exchange : public PgSqlLeaseExchange {
521
private:
522

523 524 525 526
    /// @brief Column numbers for each column in the Lease4 table.
    /// These are used for both retrieving data and for looking up
    /// column labels for logging.  Note that their numeric order
    /// MUST match that of the column order in the Lease4 table.
527 528 529 530 531 532 533 534 535
    static const size_t ADDRESS_COL = 0;
    static const size_t HWADDR_COL = 1;
    static const size_t CLIENT_ID_COL = 2;
    static const size_t VALID_LIFETIME_COL = 3;
    static const size_t EXPIRE_COL = 4;
    static const size_t SUBNET_ID_COL = 5;
    static const size_t FQDN_FWD_COL = 6;
    static const size_t FQDN_REV_COL = 7;
    static const size_t HOSTNAME_COL = 8;
536
    /// @brief Number of columns in the table holding DHCPv4 leases.
537
    static const size_t LEASE_COLUMNS = 9;
538

539
public:
540

541
    /// @brief Default constructor
542 543 544
    PgSqlLease4Exchange()
        : lease_(), addr4_(0), hwaddr_length_(0), hwaddr_(hwaddr_length_),
        client_id_length_(0) {
545 546 547

        BOOST_STATIC_ASSERT(8 < LEASE_COLUMNS);

548 549 550 551
        memset(hwaddr_buffer_, 0, sizeof(hwaddr_buffer_));
        memset(client_id_buffer_, 0, sizeof(client_id_buffer_));

        // Set the column names (for error messages)
552 553 554 555 556 557 558 559 560
        columnLabels_.push_back("address");
        columnLabels_.push_back("hwaddr");
        columnLabels_.push_back("client_id");
        columnLabels_.push_back("valid_lifetime");
        columnLabels_.push_back("expire");
        columnLabels_.push_back("subnet_id");
        columnLabels_.push_back("fqdn_fwd");
        columnLabels_.push_back("fqdn_rev");
        columnLabels_.push_back("hostname");
561 562
    }

563 564 565 566 567 568 569 570 571 572 573 574 575
    /// @brief Creates the bind array for sending Lease4 data to the database.
    ///
    /// Converts each Lease4 member into the appropriate form and adds it
    /// to the bind array.  Note that the array additions must occur in the
    /// order the columns are specified in the SQL statement.  By convention
    /// all columns in the table are explicitly listed in the SQL statement(s)
    /// in the same order as they occur in the table.
    ///
    /// @param lease Lease4 object that is to be written to the database
    /// @param[out] bind_array array to populate with the lease data values
    ///
    /// @throw DbOperationError if bind_array cannot be populated.
    void createBindForSend(const Lease4Ptr& lease, PsqlBindArray& bind_array) {
576 577 578 579
        if (!lease) {
            isc_throw(BadValue, "createBindForSend:: Lease4 object is NULL");
        }

580
        // Store lease object to ensure it remains valid.
581 582
        lease_ = lease;

583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600
        try {
            addr_str_ = boost::lexical_cast<std::string>
                        (static_cast<uint32_t>(lease->addr_));
            bind_array.add(addr_str_);

            if (!lease->hwaddr_.empty()) {
                // PostgreSql does not provide MAX on variable length types
                // so we have to enforce it ourselves.
                if (lease->hwaddr_.size() > HWAddr::MAX_HWADDR_LEN) {
                        isc_throw(DbOperationError, "Hardware address length : "
                                  << lease_->hwaddr_.size()
                                  << " exceeds maximum allowed of: "
                                  << HWAddr::MAX_HWADDR_LEN);
                }

                bind_array.add(lease->hwaddr_);
            } else {
                bind_array.add("");
601 602
            }

603 604 605 606 607
            if (lease->client_id_) {
                bind_array.add(lease->client_id_->getClientId());
            } else {
                bind_array.add("");
            }
608

609 610 611
            valid_lft_str_ = boost::lexical_cast<std::string>
                             (lease->valid_lft_);
            bind_array.add(valid_lft_str_);
612

613 614 615
            expire_str_ = convertToDatabaseTime(lease->valid_lft_ +
                                                lease->cltt_);
            bind_array.add(expire_str_);
616

617 618 619
            subnet_id_str_ = boost::lexical_cast<std::string>
                             (lease->subnet_id_);
            bind_array.add(subnet_id_str_);
620

621 622
            bind_array.add(lease->fqdn_fwd_);
            bind_array.add(lease->fqdn_rev_);
623

624 625 626 627 628 629
            bind_array.add(lease->hostname_);
        } catch (const std::exception& ex) {
            isc_throw(DbOperationError,
                      "Could not create bind array for Lease4 lease: "
                      << lease_->addr_.toText() << " reason: " << ex.what());
        }
630 631
    }

632 633 634 635 636 637 638 639
    /// @brief Creates a Lease4 object from a given row in a result set.
    ///
    /// @param r result set containing one or rows from the Lease4 table
    /// @param row row number within the result set from to create the Lease4
    /// object.
    ///
    /// @return Lease4Ptr to the newly created Lease4 object
    /// @throw DbOperationError if the lease cannot be created.
640
    Lease4Ptr convertFromDatabase(PGresult*& r, int row) {
641 642
        try {
            getColumnValue(r, row, ADDRESS_COL, addr4_);
643

644 645
            convertFromBytea(r, row, HWADDR_COL, hwaddr_buffer_,
                             sizeof(hwaddr_buffer_), hwaddr_length_);
646

647 648
            convertFromBytea(r, row, CLIENT_ID_COL, client_id_buffer_,
                             sizeof(client_id_buffer_), client_id_length_);
649

650
            getColumnValue(r, row, VALID_LIFETIME_COL, valid_lifetime_);
651

652 653
            expire_ = convertFromDatabaseTime(getColumnValue(r, row,
                                                             EXPIRE_COL));
654

655
            getColumnValue(r, row , SUBNET_ID_COL, subnet_id_);
656

657
            cltt_ = expire_ - valid_lifetime_;
658

659 660
            getColumnValue(r, row, FQDN_FWD_COL, fqdn_fwd_);
            getColumnValue(r, row, FQDN_REV_COL, fqdn_rev_);
661

662
            hostname_ = getColumnValue(r, row, HOSTNAME_COL);
663

664 665 666 667 668 669 670 671 672 673
            return (Lease4Ptr(new Lease4(addr4_, hwaddr_buffer_, hwaddr_length_,
                                         client_id_buffer_, client_id_length_,
                                         valid_lifetime_, 0, 0, cltt_,
                                         subnet_id_, fqdn_fwd_, fqdn_rev_,
                                         hostname_)));
        } catch (const std::exception& ex) {
            isc_throw(DbOperationError,
                      "Could not convert data to Lease4, reason: "
                       << ex.what());
        }
674 675 676
    }

private:
677 678 679 680 681 682
    /// @brief Lease4 object currently being sent to the database.
    /// Storing this value ensures that it remains in scope while any bindings
    /// that refer to its contents are in use.
    Lease4Ptr       lease_;

    /// @Brief Lease4 specific members used for binding and conversion.
683
    uint32_t        addr4_;
684
    unsigned long   hwaddr_length_;
685
    std::vector<uint8_t> hwaddr_;
686 687
    uint8_t         hwaddr_buffer_[HWAddr::MAX_HWADDR_LEN];
    unsigned long   client_id_length_;
688
    uint8_t         client_id_buffer_[ClientId::MAX_CLIENT_ID_LEN];
689 690
};

691
/// @brief Supports exchanging IPv6 leases with PostgreSQL.
692
class PgSqlLease6Exchange : public PgSqlLeaseExchange {
693
private:
694 695 696 697 698 699

    /// @brief Column numbers for each column in the Lease6 table.
    /// These are used for both retrieving data and for looking up
    /// column labels for logging.  Note that their numeric order
    /// MUST match that of the column order in the Lease6 table.
    //@{
700 701 702 703 704 705 706 707 708 709 710 711
    static const int ADDRESS_COL = 0;
    static const int DUID_COL = 1;
    static const int VALID_LIFETIME_COL = 2;
    static const int EXPIRE_COL = 3;
    static const int SUBNET_ID_COL = 4;
    static const int PREF_LIFETIME_COL = 5;
    static const int LEASE_TYPE_COL =  6;
    static const int IAID_COL = 7;
    static const int PREFIX_LEN_COL = 8;
    static const int FQDN_FWD_COL = 9;
    static const int FQDN_REV_COL = 10;
    static const int HOSTNAME_COL = 11;
712 713
    //@}
    /// @brief Number of columns in the table holding DHCPv4 leases.
714
    static const size_t LEASE_COLUMNS = 12;
715

716
public:
717 718 719 720
    PgSqlLease6Exchange()
        : lease_(), duid_length_(0), duid_(), iaid_(0), iaid_str_(""),
         lease_type_(Lease6::TYPE_NA), lease_type_str_(""), prefix_len_(0),
         prefix_len_str_(""), pref_lifetime_(0), preferred_lft_str_("") {
721 722 723

        BOOST_STATIC_ASSERT(11 < LEASE_COLUMNS);

724
        memset(duid_buffer_, 0, sizeof(duid_buffer_));
725

726
        // Set the column names (for error messages)
727 728 729 730 731 732 733 734 735 736 737 738
        columnLabels_.push_back("address");
        columnLabels_.push_back("duid");
        columnLabels_.push_back("valid_lifetime");
        columnLabels_.push_back("expire");
        columnLabels_.push_back("subnet_id");
        columnLabels_.push_back("pref_lifetime");
        columnLabels_.push_back("lease_type");
        columnLabels_.push_back("iaid");
        columnLabels_.push_back("prefix_len");
        columnLabels_.push_back("fqdn_fwd");
        columnLabels_.push_back("fqdn_rev");
        columnLabels_.push_back("hostname");
739 740
    }

741 742 743 744 745 746 747 748 749 750 751 752 753
    /// @brief Creates the bind array for sending Lease6 data to the database.
    ///
    /// Converts each Lease6 member into the appropriate form and adds it
    /// to the bind array.  Note that the array additions must occur in the
    /// order the columns are specified in the SQL statement.  By convention
    /// all columns in the table are explicitly listed in the SQL statement(s)
    /// in the same order as they occur in the table.
    ///
    /// @param lease Lease6 object that is to be written to the database
    /// @param[out] bind_array array to populate with the lease data values
    ///
    /// @throw DbOperationError if bind_array cannot be populated.
    void createBindForSend(const Lease6Ptr& lease, PsqlBindArray& bind_array) {
754 755 756 757
        if (!lease) {
            isc_throw(BadValue, "createBindForSend:: Lease6 object is NULL");
        }

758
        // Store lease object to ensure it remains valid.
759
        lease_ = lease;
760 761 762
        try {
            addr_str_ = lease_->addr_.toText();
            bind_array.add(addr_str_);
763

764
            bind_array.add(lease_->duid_->getDuid());
765

766 767 768
            valid_lft_str_ = boost::lexical_cast<std::string>
                             (lease->valid_lft_);
            bind_array.add(valid_lft_str_);
769

770 771 772
            expire_str_ = convertToDatabaseTime(lease->valid_lft_ +
                                                lease->cltt_);
            bind_array.add(expire_str_);
773

774 775 776
            subnet_id_str_ = boost::lexical_cast<std::string>
                             (lease->subnet_id_);
            bind_array.add(subnet_id_str_);
777

778 779 780
            preferred_lft_str_ = boost::lexical_cast<std::string>
                                 (lease_->preferred_lft_);
            bind_array.add(preferred_lft_str_);
781

782 783
            lease_type_str_ = boost::lexical_cast<std::string>(lease_->type_);
            bind_array.add(lease_type_str_);
784

785 786
            iaid_str_ = boost::lexical_cast<std::string>(lease_->iaid_);
            bind_array.add(iaid_str_);
787

788 789
            prefix_len_str_ = boost::lexical_cast<std::string>
                              (static_cast<unsigned int>(lease_->prefixlen_));
790

791
            bind_array.add(prefix_len_str_);
792

793 794
            bind_array.add(lease->fqdn_fwd_);
            bind_array.add(lease->fqdn_rev_);
795

796 797 798 799 800 801
            bind_array.add(lease->hostname_);
        } catch (const std::exception& ex) {
            isc_throw(DbOperationError,
                      "Could not create bind array from Lease6: "
                      << lease_->addr_.toText() << " reason: " << ex.what());
        }
802 803
    }

804 805 806 807 808 809 810 811
    /// @brief Creates a Lease6 object from a given row in a result set.
    ///
    /// @param r result set containing one or rows from the Lease6 table
    /// @param row row number within the result set from to create the Lease6
    /// object.
    ///
    /// @return Lease6Ptr to the newly created Lease4 object
    /// @throw DbOperationError if the lease cannot be created.
812
    Lease6Ptr convertFromDatabase(PGresult*& r, int row) {
813 814
        try {
            isc::asiolink::IOAddress addr(getIPv6Value(r, row, ADDRESS_COL));
815

816 817 818
            convertFromBytea(r, row, DUID_COL, duid_buffer_,
                             sizeof(duid_buffer_), duid_length_);
            DuidPtr duid_ptr(new DUID(duid_buffer_, duid_length_));
819

820
            getColumnValue(r, row, VALID_LIFETIME_COL, valid_lifetime_);
821

822 823
            expire_ = convertFromDatabaseTime(getColumnValue(r, row,
                                                             EXPIRE_COL));
824

825
            cltt_ = expire_ - valid_lifetime_;
826

827
            getColumnValue(r, row , SUBNET_ID_COL, subnet_id_);
828

829
            getColumnValue(r, row , PREF_LIFETIME_COL, pref_lifetime_);
830

831
            getColumnValue(r, row, LEASE_TYPE_COL, lease_type_);
832

833
            getColumnValue(r, row , IAID_COL, iaid_);
834

835
            getColumnValue(r, row , PREFIX_LEN_COL, prefix_len_);
836

837 838
            getColumnValue(r, row, FQDN_FWD_COL, fqdn_fwd_);
            getColumnValue(r, row, FQDN_REV_COL, fqdn_rev_);
839

840
            hostname_ = getColumnValue(r, row, HOSTNAME_COL);
841

842 843 844 845 846 847 848 849 850 851 852
            Lease6Ptr result(new Lease6(lease_type_, addr, duid_ptr, iaid_,
                                        pref_lifetime_, valid_lifetime_, 0, 0,
                                        subnet_id_, fqdn_fwd_, fqdn_rev_,
                                        hostname_, prefix_len_));
            result->cltt_ = cltt_;
            return (result);
        } catch (const std::exception& ex) {
            isc_throw(DbOperationError,
                      "Could not convert data to Lease6, reason: "
                       << ex.what());
        }
853 854
    }

855 856 857 858 859 860 861 862 863
    /// @brief Converts a column in a row in a result set into IPv6 address.
    ///
    /// @param r the result set containing the query results
    /// @param row the row number within the result set
    /// @param col the column number within the row
    ///
    /// @return isc::asiolink::IOAddress containing the IPv6 address.
    /// @throw  DbOperationError if the value cannot be fetched or is
    /// invalid.
864
    isc::asiolink::IOAddress getIPv6Value(PGresult*& r, const int row,
865
                                          const size_t col) {
866 867 868 869 870 871 872 873 874 875
        const char* data = getColumnValue(r, row, col);
        try {
            return (isc::asiolink::IOAddress(data));
        } catch (const std::exception& ex) {
            isc_throw(DbOperationError, "Cannot convert data: " << data
                      << " for: " << getColumnLabel(col) << " row:" << row
                      << " : " << ex.what());
        }
    }

876
private:
877 878 879 880 881 882 883 884
    /// @brief Lease6 object currently being sent to the database.
    /// Storing this value ensures that it remains in scope while any bindings
    /// that refer to its contents are in use.
    Lease6Ptr       lease_;

    /// @brief Lease6 specific members for binding and conversion.
    //@{
    unsigned long   duid_length_;
885 886 887
    vector<uint8_t> duid_;
    uint8_t         duid_buffer_[DUID::MAX_DUID_LEN];
    uint32_t        iaid_;
888
    std::string iaid_str_;
889
    Lease6::Type    lease_type_;
890
    std::string lease_type_str_;
891
    uint8_t         prefix_len_;
892
    std::string prefix_len_str_;
893
    uint32_t        pref_lifetime_;
894
    std::string preferred_lft_str_;
895
    //@}
896 897 898
};

PgSqlLeaseMgr::PgSqlLeaseMgr(const LeaseMgr::ParameterMap& parameters)
899 900
    : LeaseMgr(parameters), exchange4_(new PgSqlLease4Exchange()),
    exchange6_(new PgSqlLease6Exchange()), conn_(NULL) {
901 902 903 904 905
    openDatabase();
    prepareStatements();
}

PgSqlLeaseMgr::~PgSqlLeaseMgr() {
906 907 908 909
    if (conn_) {
        // Deallocate the prepared queries.
        PGresult* r = PQexec(conn_, "DEALLOCATE all");
        if(PQresultStatus(r) != PGRES_COMMAND_OK) {
910 911 912
            // Highly unlikely but we'll log it and go on.
            LOG_ERROR(dhcpsrv_logger, DHCPSRV_PGSQL_DEALLOC_ERROR)
                      .arg(PQerrorMessage(conn_));
913 914
        }

915 916
        PQclear(r);
        PQfinish(conn_);
917
        conn_ = NULL;
918 919 920
    }
}

921 922
void
PgSqlLeaseMgr::prepareStatements() {
923
    for(int i = 0; tagged_statements[i].text != NULL; ++ i) {
924 925 926 927 928
        // Prepare all statements queries with all known fields datatype
        PGresult* r = PQprepare(conn_, tagged_statements[i].name,
                                tagged_statements[i].text,
                                tagged_statements[i].nbparams,
                                tagged_statements[i].types);
929

930
        if(PQresultStatus(r) != PGRES_COMMAND_OK) {
931
            PQclear(r);
932 933 934 935 936
            isc_throw(DbOperationError,
                      "unable to prepare PostgreSQL statement: "
                      << tagged_statements[i].text << ", reason: "
                      << PQerrorMessage(conn_));
        }
937

938
        PQclear(r);
939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974
    }
}

void
PgSqlLeaseMgr::openDatabase() {
    string dbconnparameters;
    string shost = "localhost";
    try {
        shost = getParameter("host");
    } catch(...) {
        // No host. Fine, we'll use "localhost"
    }

    dbconnparameters += "host = '" + shost + "'" ;

    string suser;
    try {
        suser = getParameter("user");
        dbconnparameters += " user = '" + suser + "'";
    } catch(...) {
        // No user. Fine, we'll use NULL
    }

    string spassword;
    try {
        spassword = getParameter("password");
        dbconnparameters += " password = '" + spassword + "'";
    } catch(...) {
        // No password. Fine, we'll use NULL
    }

    string sname;
    try {
        sname= getParameter("name");
        dbconnparameters += " dbname = '" + sname + "'";
    } catch(...) {
975 976
        // No database name.  Throw a "NoDatabaseName" exception
        isc_throw(NoDatabaseName, "must specify a name for the database");
977 978
    }

979 980
    conn_ = PQconnectdb(dbconnparameters.c_str());
    if (conn_ == NULL) {
981 982 983
        isc_throw(DbOpenError, "could not allocate connection object");
    }

984
    if (PQstatus(conn_) != CONNECTION_OK) {
985 986
        // If we have a connection object, we have to call finish
        // to release it, but grab the error message first.
987 988
        std::string error_message = PQerrorMessage(conn_);
        PQfinish(conn_);
989
        conn_ = NULL;
990
        isc_throw(DbOpenError, error_message);
991 992 993
    }
}

994 995 996
bool
PgSqlLeaseMgr::addLeaseCommon(StatementIndex stindex,
                              PsqlBindArray& bind_array) {
997
    PGresult* r = PQexecPrepared(conn_, tagged_statements[stindex].name,