1 // Copyright (C) 2016-2018,2021 Internet Systems Consortium, Inc. ("ISC") 2 // 3 // This Source Code Form is subject to the terms of the Mozilla Public 4 // License, v. 2.0. If a copy of the MPL was not distributed with this 5 // file, You can obtain one at http://mozilla.org/MPL/2.0/. 6 7 #ifndef PGSQL_EXCHANGE_H 8 #define PGSQL_EXCHANGE_H 9 10 #include <asiolink/io_address.h> 11 #include <pgsql/pgsql_connection.h> 12 13 #include <boost/lexical_cast.hpp> 14 #include <boost/noncopyable.hpp> 15 #include <boost/shared_ptr.hpp> 16 17 #include <stdint.h> 18 #include <vector> 19 #include <iostream> 20 21 namespace isc { 22 namespace db { 23 24 /// @brief Structure used to bind C++ input values to dynamic SQL parameters 25 /// The structure contains three vectors which store the input values, 26 /// data lengths, and formats. These vectors are passed directly into the 27 /// PostgreSQL execute call. 28 /// 29 /// Note that the data values are stored as pointers. These pointers need to 30 /// be valid for the duration of the PostgreSQL statement execution. In other 31 /// words populating them with pointers to values that go out of scope before 32 /// statement is executed is a bad idea. 33 /// 34 /// Other than vectors or buffers of binary data, all other values are currently 35 /// converted to their string representation prior to sending them to PostgreSQL. 36 /// All of the add() method variants which accept a non-string value internally 37 /// create the conversion string which is then retained in the bind array to ensure 38 /// scope. 39 /// 40 /// @brief smart pointer to const std::strings used by PsqlBindArray to ensure scope 41 /// of strings supplying exchange values 42 typedef boost::shared_ptr<const std::string> ConstStringPtr; 43 44 struct PsqlBindArray { 45 /// @brief Vector of pointers to the data values. 46 std::vector<const char *> values_; 47 /// @brief Vector of data lengths for each value. 48 std::vector<int> lengths_; 49 /// @brief Vector of "format" for each value. A value of 0 means the 50 /// value is text, 1 means the value is binary. 51 std::vector<int> formats_; 52 53 /// @brief Format value for text data. 54 static const int TEXT_FMT; 55 /// @brief Format value for binary data. 56 static const int BINARY_FMT; 57 58 /// @brief Constant string passed to DB for boolean true values. 59 static const char* TRUE_STR; 60 /// @brief Constant string passed to DB for boolean false values. 61 static const char* FALSE_STR; 62 63 /// @brief Fetches the number of entries in the array. 64 /// @return Returns size_t containing the number of entries. sizePsqlBindArray65 size_t size() const { 66 return (values_.size()); 67 } 68 69 /// @brief Indicates it the array is empty. 70 /// @return Returns true if there are no entries in the array, false 71 /// otherwise. emptyPsqlBindArray72 bool empty() const { 73 74 return (values_.empty()); 75 } 76 77 /// @brief Adds a char array to bind array based 78 /// 79 /// Adds a TEXT_FMT value to the end of the bind array, using the given 80 /// char* as the data source. The value is expected to be NULL 81 /// terminated. The caller is responsible for ensuring that value 82 /// remains in scope until the bind array has been discarded. 83 /// 84 /// @param value char array containing the null-terminated text to add. 85 /// @throw DbOperationError if value is NULL. 86 void add(const char* value); 87 88 /// @brief Adds an string value to the bind array 89 /// 90 /// Adds a TEXT formatted value to the end of the bind array using the 91 /// given string as the data source. The caller is responsible for 92 /// ensuring that string parameter remains in scope until the bind 93 /// array has been discarded. 94 /// 95 /// @param value std::string containing the value to add. 96 void add(const std::string& value); 97 98 /// @brief Adds a vector of binary data to the bind array. 99 /// 100 /// Adds a BINARY_FMT value to the end of the bind array using the 101 /// given vector as the data source. NOTE this does not replicate 102 /// the vector, so it must remain in scope until the bind array 103 /// is destroyed. 104 /// 105 /// @param data vector of binary bytes. 106 void add(const std::vector<uint8_t>& data); 107 108 /// @brief Adds a buffer of binary data to the bind array. 109 /// 110 /// Adds a BINARY_FMT value to the end of the bind array using the 111 /// given vector as the data source. NOTE this does not replicate 112 /// the buffer, so it must remain in scope until the bind array 113 /// is destroyed. 114 /// 115 /// @param data buffer of binary data. 116 /// @param len number of bytes of data in buffer 117 /// @throw DbOperationError if data is NULL. 118 void add(const uint8_t* data, const size_t len); 119 120 /// @brief Adds a boolean value to the bind array. 121 /// 122 /// Converts the given boolean value to its corresponding to PostgreSQL 123 /// string value and adds it as a TEXT_FMT value to the bind array. 124 /// This creates an internally scoped string. 125 /// 126 /// @param value the boolean value to add. 127 void add(const bool& value); 128 129 /// @brief Adds a uint8_t value to the bind array. 130 /// 131 /// Converts the given uint8_t value to its corresponding numeric string 132 /// literal and adds it as a TEXT_FMT value to the bind array. 133 /// This creates an internally scoped string. 134 /// 135 /// @param byte the one byte value to add. 136 void add(const uint8_t& byte); 137 138 /// @brief Adds the given IOAddress value to the bind array. 139 /// 140 /// Converts the IOAddress, based on its protocol family, to the 141 /// corresponding string literal and adds it as a TEXT_FMT value to 142 /// the bind array. 143 /// This creates an internally scoped string. 144 /// 145 /// @param addr IP address value to add. 146 void add(const isc::asiolink::IOAddress& addr); 147 148 /// @brief Adds the given value to the bind array. 149 /// 150 /// Converts the given value to its corresponding string literal 151 /// boost::lexical_cast and adds it as a TEXT_FMT value to the bind array. 152 /// This is intended primarily for numeric types. 153 /// This creates an internally scoped string. 154 /// 155 /// @param value data value to add. 156 template<typename T> addPsqlBindArray157 void add(const T& value) { 158 addTempString(boost::lexical_cast<std::string>(value)); 159 } 160 161 /// @brief Binds the given string to the bind array. 162 /// 163 /// Prior to add the given string the vector of exchange values, 164 /// it duplicated as a ConstStringPtr and saved internally. This guarantees 165 /// the string remains in scope until the PsqlBindArray is destroyed, 166 /// without the caller maintaining the string values. 167 /// 168 /// @param str string value to add. 169 void addTempString(const std::string& str); 170 171 /// @brief Adds a NULL value to the bind array 172 /// 173 /// This should be used whenever the value for a parameter specified 174 /// in the SQL statement should be NULL. 175 void addNull(const int format = PsqlBindArray::TEXT_FMT); 176 177 //std::vector<const std::string> getBoundStrs() { getBoundStrsPsqlBindArray178 std::vector<ConstStringPtr> getBoundStrs() { 179 return (bound_strs_); 180 } 181 182 /// @brief Dumps the contents of the array to a string. 183 /// @return std::string containing the dump 184 std::string toText() const; 185 186 private: 187 /// @brief vector of strings which supplied the values 188 std::vector<ConstStringPtr> bound_strs_; 189 190 }; 191 192 /// @brief Defines a smart pointer to PsqlBindArray 193 typedef boost::shared_ptr<PsqlBindArray> PsqlBindArrayPtr; 194 195 /// @brief Base class for marshalling data to and from PostgreSQL. 196 /// 197 /// Provides the common functionality to set up binding information between 198 /// application objects in the program and their representation in the 199 /// database, and for retrieving column values from rows of a result set. 200 class PgSqlExchange { 201 public: 202 /// @brief Constructor columns_(num_columns)203 PgSqlExchange(const size_t num_columns = 0) : columns_(num_columns) {} 204 205 /// @brief Destructor ~PgSqlExchange()206 virtual ~PgSqlExchange(){} 207 208 /// @brief Converts time_t value to a text representation in local time. 209 /// 210 /// @param input_time A time_t value representing time. 211 /// @return std::string containing stringified time. 212 static std::string convertToDatabaseTime(const time_t input_time); 213 214 /// @brief Converts lease expiration time to a text representation in 215 /// local time. 216 /// 217 /// The expiration time is calculated as a sum of the cltt (client last 218 /// transmit time) and the valid lifetime. 219 /// 220 /// The format of the output string is "%Y-%m-%d %H:%M:%S". Database 221 /// table columns using this value should be typed as TIMESTAMP WITH 222 /// TIME ZONE. For such columns PostgreSQL assumes input strings without 223 /// timezones should be treated as in local time and are converted to UTC 224 /// when stored. Likewise, these columns are automatically adjusted 225 /// upon retrieval unless fetched via "extract(epoch from <column>))". 226 /// 227 /// Unless we start using binary input, timestamp columns must be input as 228 /// date/time strings. 229 /// 230 /// @param cltt Client last transmit time 231 /// @param valid_lifetime Valid lifetime 232 /// 233 /// @return std::string containing the stringified time 234 /// @throw isc::BadValue if the sum of the calculated expiration time is 235 /// greater than the value of @c DataSource::MAX_DB_TIME. 236 static std::string convertToDatabaseTime(const time_t cltt, 237 const uint32_t valid_lifetime); 238 239 /// @brief Converts time stamp from the database to a time_t 240 /// 241 /// We're fetching timestamps as an integer string of seconds since the 242 /// epoch. This method converts such a string int a time_t. 243 /// 244 /// @param db_time_val timestamp to be converted. This value 245 /// is expected to be the number of seconds since the epoch 246 /// expressed as base-10 integer string. 247 /// @return Converted timestamp as time_t value. 248 static time_t convertFromDatabaseTime(const std::string& db_time_val); 249 250 /// @brief Gets a pointer to the raw column value in a result set row 251 /// 252 /// Given a result set, row, and column return a const char* pointer to 253 /// the data value in the result set. The pointer is valid as long as 254 /// the result set has not been freed. It may point to text or binary 255 /// data depending on how query was structured. You should not attempt 256 /// to free this pointer. 257 /// 258 /// @param r the result set containing the query results 259 /// @param row the row number within the result set 260 /// @param col the column number within the row 261 /// 262 /// @return a const char* pointer to the column's raw data 263 /// @throw DbOperationError if the value cannot be fetched. 264 static const char* getRawColumnValue(const PgSqlResult& r, const int row, 265 const size_t col); 266 267 /// @brief Fetches the name of the column in a result set 268 /// 269 /// Returns the column name of the column from the result set. 270 /// If the column index is out of range it will return the 271 /// string "Unknown column:<index>". Note this is NOT from the 272 /// list of columns defined in the exchange. 273 /// 274 /// @param r the result set containing the query results 275 /// @param col index of the column name to fetch 276 /// 277 /// @return string containing the name of the column 278 static std::string getColumnLabel(const PgSqlResult& r, const size_t col); 279 280 /// @brief Fetches text column value as a string 281 /// 282 /// @param r the result set containing the query results 283 /// @param row the row number within the result set 284 /// @param col the column number within the row 285 /// @param[out] value parameter to receive the string value 286 /// 287 /// @throw DbOperationError if the value cannot be fetched or is 288 /// invalid. 289 static void getColumnValue(const PgSqlResult& r, const int row, 290 const size_t col, std::string& value); 291 292 /// @brief Fetches boolean text ('t' or 'f') as a bool. 293 /// 294 /// @param r the result set containing the query results 295 /// @param row the row number within the result set 296 /// @param col the column number within the row 297 /// @param[out] value parameter to receive the converted value 298 /// 299 /// @throw DbOperationError if the value cannot be fetched or is 300 /// invalid. 301 static void getColumnValue(const PgSqlResult& r, const int row, 302 const size_t col, bool &value); 303 304 /// @brief Fetches an integer text column as a uint8_t. 305 /// 306 /// @param r the result set containing the query results 307 /// @param row the row number within the result set 308 /// @param col the column number within the row 309 /// @param[out] value parameter to receive the converted value 310 /// 311 /// @throw DbOperationError if the value cannot be fetched or is 312 /// invalid. 313 static void getColumnValue(const PgSqlResult& r, const int row, 314 const size_t col, uint8_t &value); 315 316 /// @brief Converts a column in a row in a result set into IPv6 address. 317 /// 318 /// @param r the result set containing the query results 319 /// @param row the row number within the result set 320 /// @param col the column number within the row 321 /// 322 /// @return isc::asiolink::IOAddress containing the IPv6 address. 323 /// @throw DbOperationError if the value cannot be fetched or is 324 /// invalid. 325 static isc::asiolink::IOAddress getIPv6Value(const PgSqlResult& r, 326 const int row, 327 const size_t col); 328 329 /// @brief Returns true if a column within a row is null 330 /// 331 /// @param r the result set containing the query results 332 /// @param row the row number within the result set 333 /// @param col the column number within the row 334 /// 335 /// @return True if the column values in the row is NULL, false otherwise. 336 static bool isColumnNull(const PgSqlResult& r, const int row, 337 const size_t col); 338 339 /// @brief Fetches a text column as the given value type 340 /// 341 /// Uses boost::lexicalcast to convert the text column value into 342 /// a value of type T. 343 /// 344 /// @param r the result set containing the query results 345 /// @param row the row number within the result set 346 /// @param col the column number within the row 347 /// @param[out] value parameter to receive the converted value 348 /// 349 /// @throw DbOperationError if the value cannot be fetched or is 350 /// invalid. 351 template<typename T> getColumnValue(const PgSqlResult & r,const int row,const size_t col,T & value)352 static void getColumnValue(const PgSqlResult& r, const int row, 353 const size_t col, T& value) { 354 const char* data = getRawColumnValue(r, row, col); 355 try { 356 value = boost::lexical_cast<T>(data); 357 } catch (const std::exception& ex) { 358 isc_throw(db::DbOperationError, "Invalid data:[" << data 359 << "] for row: " << row << " col: " << col << "," 360 << getColumnLabel(r, col) << " : " << ex.what()); 361 } 362 } 363 364 /// @brief Converts a column in a row in a result set to a binary bytes 365 /// 366 /// Method is used to convert columns stored as BYTEA into a buffer of 367 /// binary bytes, (uint8_t). It uses PQunescapeBytea to do the conversion. 368 /// 369 /// @param r the result set containing the query results 370 /// @param row the row number within the result set 371 /// @param col the column number within the row 372 /// @param[out] buffer pre-allocated buffer to which the converted bytes 373 /// will be stored. 374 /// @param buffer_size size of the output buffer 375 /// @param[out] bytes_converted number of bytes converted 376 /// value 377 /// 378 /// @throw DbOperationError if the value cannot be fetched or is 379 /// invalid. 380 static void convertFromBytea(const PgSqlResult& r, const int row, 381 const size_t col, uint8_t* buffer, 382 const size_t buffer_size, 383 size_t &bytes_converted); 384 385 /// @brief Diagnostic tool which dumps the Result row contents as a string 386 /// 387 /// @param r the result set containing the query results 388 /// @param row the row number within the result set 389 /// 390 /// @return A string depiction of the row contents. 391 static std::string dumpRow(const PgSqlResult& r, int row); 392 393 protected: 394 /// @brief Stores text labels for columns, currently only used for 395 /// logging and errors. 396 std::vector<std::string>columns_; 397 }; 398 399 }; // end of isc::db namespace 400 }; // end of isc namespace 401 402 #endif // PGSQL_EXCHANGE_H 403