pgsql_connection.h 14.8 KB
Newer Older
1
// Copyright (C) 2016-2020 Internet Systems Consortium, Inc. ("ISC")
2 3 4 5 6 7 8
//
// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at http://mozilla.org/MPL/2.0/.
#ifndef PGSQL_CONNECTION_H
#define PGSQL_CONNECTION_H

9
#include <database/database_connection.h>
10 11 12 13 14

#include <libpq-fe.h>
#include <boost/scoped_ptr.hpp>

#include <vector>
15
#include <stdint.h>
16 17

namespace isc {
18
namespace db {
19

20
/// @brief Define PostgreSQL backend version: 6.1
21 22
const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 6;
const uint32_t PGSQL_SCHEMA_VERSION_MINOR = 1;
23

24
// Maximum number of parameters that can be used a statement
25 26 27
// @todo This allows us to use an initializer list (since we can't
// require C++11).  It's unlikely we'd go past this many a single
// statement.
28 29
const size_t PGSQL_MAX_PARAMETERS_IN_QUERY = 32;

30
/// @brief Define a PostgreSQL statement.
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
///
/// Each statement is associated with an index, which is used to reference the
/// associated prepared statement.
struct PgSqlTaggedStatement {
    /// Number of parameters for a given query
    int nbparams;

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

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

    /// Text representation of the actual query.
    const char* text;
};

Andrei Pavel's avatar
Andrei Pavel committed
52
/// @{
53
/// @brief Constants for PostgreSQL data types
54
/// These are defined by PostgreSQL in <catalog/pg_type.h>, but including
55
/// this file is extraordinarily convoluted, so we'll use these to fill-in.
56
/// @{
57 58 59 60 61
const size_t OID_NONE = 0;   // PostgreSQL infers proper type
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
62
const size_t OID_INT4 = 23;  // 4 byte int
63
const size_t OID_TEXT = 25;
64
const size_t OID_VARCHAR = 1043;
65
const size_t OID_TIMESTAMP = 1114;
Andrei Pavel's avatar
Andrei Pavel committed
66
/// @}
67

Andrei Pavel's avatar
Andrei Pavel committed
68
/// @brief RAII wrapper for PostgreSQL Result sets
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
///
/// When a Postgresql statement is executed, the results are returned
/// in pointer allocated structure, PGresult*. Data and status information
/// are accessed via calls to functions such as PQgetvalue() which require
/// the results pointer.  In order to ensure this structure is freed, any
/// invocation of Psql function which returns a PGresult* (e.g. PQexec and

/// class. Examples:
/// {{{
///       PgSqlResult r(PQexec(conn_, "ROLLBACK"));
/// }}}
///
/// This eliminates the need for an explicit release via, PQclear() and
/// guarantees that the resources are released even if the an exception is
/// thrown.

85
class PgSqlResult : public boost::noncopyable {
86 87 88
public:
    /// @brief Constructor
    ///
89 90
    /// Store the pointer to the result set to being fetched.  Set row
    /// and column counts for convenience.
91
    ///
92 93 94 95
    /// @param result - pointer to the Postgresql client layer result
    /// If the value of is NULL, row and col values will be set to -1.
    /// This allows PgSqlResult to be passed into statement error
    /// checking.
96
    PgSqlResult(PGresult *result);
97 98 99 100

    /// @brief Destructor
    ///
    /// Frees the result set
101
    ~PgSqlResult();
102

103 104 105 106 107 108 109 110 111 112 113 114 115 116
    /// @brief Returns the number of rows in the result set.
    int getRows() const {
        return (rows_);
    }

    /// @brief Returns the number of columns in the result set.
    int getCols() const {
        return (cols_);
    }

    /// @brief Determines if a row index is valid
    ///
    /// @param row index to range check
    ///
117 118
    /// @throw DbOperationError if the row index is out of range
    void rowCheck(int row) const;
119 120 121 122 123

    /// @brief Determines if a column index is valid
    ///
    /// @param col index to range check
    ///
124 125
    /// @throw DbOperationError if the column index is out of range
    void colCheck(int col) const;
126 127 128 129 130 131

    /// @brief Determines if both a row and column index are valid
    ///
    /// @param row index to range check
    /// @param col index to range check
    ///
132
    /// @throw DbOperationError if either the row or column index
133
    /// is out of range
134 135 136 137 138 139 140 141 142 143
    void rowColCheck(int row, int col) const;

    /// @brief Fetches the name of the column in a result set
    ///
    /// Returns the column name of the column from the result set.
    /// If the column index is out of range it will return the
    /// string "Unknown column:<index>"
    ///
    /// @param col index of the column name to fetch
    /// @return string containing the name of the column
144
    /// This method is exception safe.
145
    std::string getColumnLabel(const int col) const;
146

147 148
    /// @brief Conversion Operator
    ///
149
    /// Allows the PgSqlResult object to be passed as the result set argument to
150 151 152 153 154 155 156 157 158 159 160 161 162 163
    /// PQxxxx functions.
    operator PGresult*() const {
        return (result_);
    }

    /// @brief Boolean Operator
    ///
    /// Allows testing the PgSqlResult object for emptiness: "if (result)"
    operator bool() const {
        return (result_);
    }

private:
    PGresult*     result_;     ///< Result set to be freed
164 165
    int rows_;   ///< Number of rows in the result set
    int cols_;   ///< Number of columns in the result set
166 167 168
};


169
/// @brief Postgresql connection handle Holder
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
///
/// Small RAII object for safer initialization, will close the database
/// connection upon destruction.  This means that if an exception is thrown
/// during database initialization, resources allocated to the database are
/// guaranteed to be freed.
///
/// It makes no sense to copy an object of this class.  After the copy, both
/// objects would contain pointers to the same PgSql context object.  The
/// destruction of one would invalid the context in the remaining object.
/// For this reason, the class is declared noncopyable.
class PgSqlHolder : public boost::noncopyable {
public:

    /// @brief Constructor
    ///
185
    /// Sets the Postgresql API connector handle to NULL.
186 187 188 189 190 191 192 193 194 195 196 197 198
    ///
    PgSqlHolder() : pgconn_(NULL) {
    }

    /// @brief Destructor
    ///
    /// Frees up resources allocated by the connection.
    ~PgSqlHolder() {
        if (pgconn_ != NULL) {
            PQfinish(pgconn_);
        }
    }

199 200 201
    /// @brief Sets the connection to the value given
    ///
    /// @param connection - pointer to the Postgresql connection instance
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230
    void setConnection(PGconn* connection) {
        if (pgconn_ != NULL) {
            // Already set? Release the current connection first.
            // Maybe this should be an error instead?
            PQfinish(pgconn_);
        }

        pgconn_ = connection;
    }

    /// @brief Conversion Operator
    ///
    /// Allows the PgSqlHolder object to be passed as the context argument to
    /// PQxxxx functions.
    operator PGconn*() const {
        return (pgconn_);
    }

    /// @brief Boolean Operator
    ///
    /// Allows testing the connection for emptiness: "if (holder)"
    operator bool() const {
        return (pgconn_);
    }

private:
    PGconn* pgconn_;      ///< Postgresql connection
};

231 232 233
/// @brief Forward declaration to @ref PgSqlConnection.
class PgSqlConnection;

234
/// @brief RAII object representing a PostgreSQL transaction.
235 236 237 238 239 240
///
/// An instance of this class should be created in a scope where multiple
/// INSERT statements should be executed within a single transaction. The
/// transaction is started when the constructor of this class is invoked.
/// The transaction is ended when the @ref PgSqlTransaction::commit is
/// explicitly called or when the instance of this class is destroyed.
241 242 243 244 245
/// The @ref PgSqlTransaction::commit commits changes to the database.
/// If the class instance is destroyed before @ref PgSqlTransaction::commit
/// has been called, the transaction is rolled back. The rollback on
/// destruction guarantees that partial data is not stored in the database
/// when an error occurs during any of the operations within a transaction.
246
///
247 248 249 250
/// By default PostgreSQL performs a commit following each statement which
/// alters the database (i.e. "autocommit"). Starting a transaction
/// stops autocommit for the connection until the transaction is ended by
/// either commit or rollback. Other connections are unaffected.
251 252 253 254 255
class PgSqlTransaction : public boost::noncopyable {
public:

    /// @brief Constructor.
    ///
256
    /// Starts transaction by executing the SQL statement: "START TRANSACTION"
257 258 259 260
    ///
    /// @param conn PostgreSQL connection to use for the transaction. This
    /// connection will be later used to commit or rollback changes.
    ///
261
    /// @throw DbOperationError if statement execution fails
262 263 264 265
    PgSqlTransaction(PgSqlConnection& conn);

    /// @brief Destructor.
    ///
266 267 268 269
    /// If the transaction has not been committed, it is rolled back
    /// by executing the SQL statement: "ROLLBACK"
    ///
    /// @throw DbOperationError if statement execution fails
270 271 272
    ~PgSqlTransaction();

    /// @brief Commits transaction.
273 274
    ///
    /// Commits all changes made during the transaction by executing the
275
    /// SQL statement: "COMMIT"
276 277
    ///
    /// @throw DbOperationError if statement execution fails
278 279 280 281 282 283 284 285 286 287 288 289 290 291
    void commit();

private:

    /// @brief Holds reference to the PostgreSQL database connection.
    PgSqlConnection& conn_;

    /// @brief Boolean flag indicating if the transaction has been committed.
    ///
    /// This flag is used in the class destructor to assess if the
    /// transaction should be rolled back.
    bool committed_;
};

292 293 294 295 296 297 298
/// @brief Common PgSql Connector Pool
///
/// This class provides common operations for PgSql database connection
/// used by both PgSqlLeaseMgr and PgSqlHostDataSource. It manages connecting
/// to the database and preparing compiled statements. Its fields are
/// public, because they are used (both set and retrieved) in classes
/// that use instances of PgSqlConnection.
299
class PgSqlConnection : public db::DatabaseConnection {
300
public:
Andrei Pavel's avatar
Andrei Pavel committed
301
    /// @brief Define the PgSql error state for a duplicate key error.
302 303 304 305 306 307 308 309 310 311 312 313
    static const char DUPLICATE_KEY[];

    /// @brief Constructor
    ///
    /// Initialize PgSqlConnection object with parameters needed for connection.
    PgSqlConnection(const ParameterMap& parameters)
        : DatabaseConnection(parameters) {
    }

    /// @brief Destructor
    virtual ~PgSqlConnection();

314 315 316 317 318 319 320 321 322 323 324 325 326
    /// @brief Get the schema version.
    ///
    /// @param parameters A data structure relating keywords and values
    ///        concerned with the database.
    ///
    /// @return Version number as a pair of unsigned integers.  "first" is the
    ///         major version number, "second" the minor number.
    ///
    /// @throw isc::db::DbOperationError An operation on the open database has
    ///        failed.
    static std::pair<uint32_t, uint32_t>
    getVersion(const ParameterMap& parameters);

327 328 329 330 331
    /// @brief Prepare Single Statement
    ///
    /// Creates a prepared statement from the text given and adds it to the
    /// statements_ vector at the given index.
    ///
332
    /// @param statement SQL statement to be prepared.
333
    ///
334
    /// @throw isc::db::DbOperationError An operation on the open database has
335 336 337
    ///        failed.
    void prepareStatement(const PgSqlTaggedStatement& statement);

338 339 340 341
    /// @brief Prepare statements
    ///
    /// Creates the prepared statements for all of the SQL statements used
    /// by the PostgreSQL backend.
342 343 344 345 346
    ///
    /// @param start_statement Pointer to the first statement in range of the
    /// statements to be compiled.
    /// @param end_statement Pointer to the statement marking end of the
    /// range of statements to be compiled. This last statement is not compiled.
347
    ///
348
    /// @throw isc::db::DbOperationError An operation on the open database has
349 350 351 352
    ///        failed.
    void prepareStatements(const PgSqlTaggedStatement* start_statement,
                           const PgSqlTaggedStatement* end_statement);

353 354 355 356 357 358 359 360 361
    /// @brief Open Database
    ///
    /// Opens the database using the information supplied in the parameters
    /// passed to the constructor.
    ///
    /// @throw NoDatabaseName Mandatory database name not given
    /// @throw DbOpenError Error opening the database
    void openDatabase();

362 363 364 365 366 367 368
    /// @brief Start a transaction
    ///
    /// Starts a transaction.
    ///
    /// @throw DbOperationError If the transaction start failed.
    void startTransaction();

369 370
    /// @brief Commit Transactions
    ///
371
    /// Commits all pending database operations.
372 373 374 375 376 377
    ///
    /// @throw DbOperationError If the commit failed.
    void commit();

    /// @brief Rollback Transactions
    ///
378
    /// Rolls back all pending database operations.
379 380 381 382 383 384 385 386 387 388 389
    ///
    /// @throw DbOperationError If the rollback failed.
    void rollback();

    /// @brief Checks a result set's SQL state against an error state.
    ///
    /// @param r result set to check
    /// @param error_state error state to compare against
    ///
    /// @return True if the result set's SQL state equals the error_state,
    /// false otherwise.
390
    bool compareError(const PgSqlResult& r, const char* error_state);
391 392 393 394 395 396 397

    /// @brief Checks result of the r object
    ///
    /// This function is used to determine whether or not the SQL statement
    /// execution succeeded, and in the event of failures, decide whether or
    /// not the failures are recoverable.
    ///
398 399 400 401
    /// If the error is recoverable, the function will throw a DbOperationError.
    /// If the error is deemed unrecoverable, such as a loss of connectivity
    /// with the server, the function will call invokeDbLostCallback(). If the
    /// invocation returns false then either there is no callback registered
402 403
    /// or the callback has elected not to attempt to reconnect, and a
    /// DbUnrecoverableError is thrown.
404
    ///
405 406 407
    /// If the invocation returns true, this indicates the calling layer will
    /// attempt recovery, and the function throws a DbOperationError to allow
    /// the caller to error handle the failed db access attempt.
408 409 410 411
    ///
    /// @param r result of the last PostgreSQL operation
    /// @param statement - tagged statement that was executed
    ///
412
    /// @throw isc::db::DbOperationError Detailed PostgreSQL failure
413
    void checkStatementError(const PgSqlResult& r,
414
                             PgSqlTaggedStatement& statement) const;
415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438

    /// @brief PgSql connection handle
    ///
    /// This field is public, because it is used heavily from PgSqlLeaseMgr
    /// and from PgSqlHostDataSource.
    PgSqlHolder conn_;

    /// @brief Conversion Operator
    ///
    /// Allows the PgConnection object to be passed as the context argument to
    /// PQxxxx functions.
    operator PGconn*() const {
        return (conn_);
    }

    /// @brief Boolean Operator
    ///
    /// Allows testing the PgConnection for initialized connection
    operator bool() const {
        return (conn_);
    }

};

439
}; // end of isc::db namespace
440 441 442
}; // end of isc namespace

#endif // PGSQL_CONNECTION_H