1 // Copyright (C) 2016-2020 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 #include <config.h>
8
9 #include <pgsql/pgsql_connection.h>
10 #include <pgsql/pgsql_exchange.h>
11
12 #include <boost/lexical_cast.hpp>
13
14 #include <gtest/gtest.h>
15
16 #include <sstream>
17 #include <vector>
18
19 using namespace isc;
20 using namespace isc::db;
21
22 namespace {
23
24 /// @brief Verifies the ability to add various data types to
25 /// the bind array.
TEST(PsqlBindArray,addDataTest)26 TEST(PsqlBindArray, addDataTest) {
27
28 PsqlBindArray b;
29
30 // Declare a vector to add. Vectors are not currently duplicated
31 // So they will go out of scope, unless caller ensures it.
32 std::vector<uint8_t> bytes;
33 for (int i = 0; i < 10; i++) {
34 bytes.push_back(i+1);
35 }
36
37 // Declare a string
38 std::string not_temp_str("just a string");
39
40 // Now add all the items within a different scope. Everything should
41 // still be valid once we exit this scope.
42 {
43 // Add a const char*
44 b.add("booya!");
45
46 // Add the non temporary string
47 b.add(not_temp_str);
48
49 // Add a temporary string
50 b.addTempString("walah walah washington");
51
52 // Add a one byte int
53 uint8_t small_int = 25;
54 b.add(small_int);
55
56 // Add a four byte int
57 int reg_int = 376;
58 b.add(reg_int);
59
60 // Add a eight byte unsigned int
61 uint64_t big_int = 48786749032;
62 b.add(big_int);
63
64 // Add boolean true and false
65 b.add((bool)(1));
66 b.add((bool)(0));
67
68 // Add IP addresses
69 b.add(isc::asiolink::IOAddress("192.2.15.34"));
70 b.add(isc::asiolink::IOAddress("3001::1"));
71
72 // Add the vector
73 b.add(bytes);
74 }
75
76 // We've left bind scope, everything should be intact.
77 std::string expected =
78 "0 : \"booya!\"\n"
79 "1 : \"just a string\"\n"
80 "2 : \"walah walah washington\"\n"
81 "3 : \"25\"\n"
82 "4 : \"376\"\n"
83 "5 : \"48786749032\"\n"
84 "6 : \"TRUE\"\n"
85 "7 : \"FALSE\"\n"
86 "8 : \"3221360418\"\n"
87 "9 : \"3001::1\"\n"
88 "10 : 0x0102030405060708090a\n";
89
90 EXPECT_EQ(expected, b.toText());
91 }
92
93 /// @brief Defines a pointer to a PgSqlConnection
94 typedef boost::shared_ptr<PgSqlConnection> PgSqlConnectionPtr;
95 /// @brief Defines a pointer to a PgSqlResult
96 typedef boost::shared_ptr<PgSqlResult> PgSqlResultPtr;
97
98 /// @brief Fixture for exercising basic PostgreSQL operations and data types
99 ///
100 /// This class is intended to be used to verify basic operations and to
101 /// verify that each PostgreSQL data type currently used by Kea, can be
102 /// correctly written to and read from PostgreSQL. Rather than use tables
103 /// that belong to Kea the schema proper, it creates its own. Currently it
104 /// consists of a single table, called "basics" which contains one column for
105 /// each of the supported data types.
106 ///
107 /// It creates the schema during construction, deletes it upon destruction, and
108 /// provides functions for executing SQL statements, executing prepared
109 /// statements, fetching all rows in the table, and deleting all the rows in
110 /// the table.
111 class PgSqlBasicsTest : public ::testing::Test {
112 public:
113 /// @brief Column index for each column
114 enum BasicColIndex {
115 ID_COL,
116 BOOL_COL,
117 BYTEA_COL,
118 BIGINT_COL,
119 SMALLINT_COL,
120 INT_COL,
121 TEXT_COL,
122 TIMESTAMP_COL,
123 VARCHAR_COL,
124 NUM_BASIC_COLS
125 };
126
127 /// @brief Constructor
128 ///
129 /// Creates the database connection, opens the database, and destroys
130 /// the table (if present) and then recreates it.
PgSqlBasicsTest()131 PgSqlBasicsTest() : expectedColNames_(NUM_BASIC_COLS) {
132 // Create database connection parameter list
133 DatabaseConnection::ParameterMap params;
134 params["name"] = "keatest";
135 params["user"] = "keatest";
136 params["password"] = "keatest";
137
138 // Create and open the database connection
139 conn_.reset(new PgSqlConnection(params));
140 conn_->openDatabase();
141
142 // Create the list of expected column names
143 expectedColNames_[ID_COL] = "id";
144 expectedColNames_[BOOL_COL] = "bool_col";
145 expectedColNames_[BYTEA_COL] = "bytea_col";
146 expectedColNames_[BIGINT_COL] = "bigint_col";
147 expectedColNames_[SMALLINT_COL] = "smallint_col";
148 expectedColNames_[INT_COL] = "int_col";
149 expectedColNames_[TEXT_COL] = "text_col";
150 expectedColNames_[TIMESTAMP_COL] = "timestamp_col";
151 expectedColNames_[VARCHAR_COL] = "varchar_col";
152
153 destroySchema();
154 createSchema();
155 }
156
157 /// @brief Destructor
158 ///
159 /// Destroys the table. The database resources are freed and the connection
160 /// closed by the destruction of conn_.
~PgSqlBasicsTest()161 virtual ~PgSqlBasicsTest () {
162 destroySchema();
163 }
164
165 /// @brief Gets the expected name of the column for a given column index
166 ///
167 /// Returns the name of column as we expect it to be when the column is
168 /// fetched from the database.
169 ///
170 /// @param col index of the desired column
171 ///
172 /// @return string containing the column name
173 ///
174 /// @throw BadValue if the index is out of range
expectedColumnName(int col)175 const std::string& expectedColumnName(int col) {
176 if (col < 0 || col >= NUM_BASIC_COLS) {
177 isc_throw(BadValue,
178 "definedColumnName: invalid column value" << col);
179 }
180
181 return (expectedColNames_[col]);
182 }
183
184 /// @brief Creates the basics table
185 /// Asserts if the creation step fails
createSchema()186 void createSchema() {
187 // One column for OID type, plus an auto-increment
188 const char* sql =
189 "CREATE TABLE basics ( "
190 " id SERIAL PRIMARY KEY NOT NULL, "
191 " bool_col BOOLEAN, "
192 " bytea_col BYTEA, "
193 " bigint_col BIGINT, "
194 " smallint_col SMALLINT, "
195 " int_col INT, "
196 " text_col TEXT, "
197 " timestamp_col TIMESTAMP WITH TIME ZONE, "
198 " varchar_col VARCHAR(255) "
199 "); ";
200
201 PgSqlResult r(PQexec(*conn_, sql));
202 ASSERT_EQ(PQresultStatus(r), PGRES_COMMAND_OK)
203 << " create basics table failed: " << PQerrorMessage(*conn_);
204 }
205
206 /// @brief Destroys the basics table
207 /// Asserts if the destruction fails
destroySchema()208 void destroySchema() {
209 if (conn_) {
210 PgSqlResult r(PQexec(*conn_, "DROP TABLE IF EXISTS basics;"));
211 ASSERT_EQ(PQresultStatus(r), PGRES_COMMAND_OK)
212 << " drop basics table failed: " << PQerrorMessage(*conn_);
213 }
214 }
215
216 /// @brief Executes a SQL statement and tests for an expected outcome
217 ///
218 /// @param r pointer which will contain the result set returned by the
219 /// statement's execution.
220 /// @param sql string containing the SQL statement text. Note that
221 /// PostgreSQL supports executing text which contains more than one SQL
222 /// statement separated by semicolons.
223 /// @param exp_outcome expected status value returned with within the
224 /// result set such as PGRES_COMMAND_OK, PGRES_TUPLES_OK.
225 /// @lineno line number from where the call was invoked
226 ///
227 /// Asserts if the result set status does not equal the expected outcome.
runSql(PgSqlResultPtr & r,const std::string & sql,int exp_outcome,int lineno)228 void runSql(PgSqlResultPtr& r, const std::string& sql, int exp_outcome,
229 int lineno) {
230 r.reset(new PgSqlResult(PQexec(*conn_, sql.c_str())));
231 ASSERT_EQ(PQresultStatus(*r), exp_outcome)
232 << " runSql at line: " << lineno << " failed, sql:[" << sql
233 << "]\n reason: " << PQerrorMessage(*conn_);
234 }
235
236 /// @brief Executes a SQL statement and tests for an expected outcome
237 ///
238 /// @param r pointer which will contain the result set returned by the
239 /// statement's execution.
240 /// @param statement statement descriptor of the prepared statement
241 /// to execute.
242 /// @param bind_array bind array containing the input values to submit
243 /// along with the statement
244 /// @param exp_outcome expected status value returned with within the
245 /// result set such as PGRES_COMMAND_OK, PGRES_TUPLES_OK.
246 /// @lineno line number from where the call was invoked
247 ///
248 /// Asserts if the result set status does not equal the expected outcome.
runPreparedStatement(PgSqlResultPtr & r,PgSqlTaggedStatement & statement,PsqlBindArrayPtr bind_array,int exp_outcome,int lineno)249 void runPreparedStatement(PgSqlResultPtr& r,
250 PgSqlTaggedStatement& statement,
251 PsqlBindArrayPtr bind_array, int exp_outcome,
252 int lineno) {
253 r.reset(new PgSqlResult(PQexecPrepared(*conn_, statement.name,
254 statement.nbparams,
255 &bind_array->values_[0],
256 &bind_array->lengths_[0],
257 &bind_array->formats_[0], 0)));
258 ASSERT_EQ(PQresultStatus(*r), exp_outcome)
259 << " runPreparedStatement at line: " << lineno
260 << " statement name:[" << statement.name
261 << "]\n reason: " << PQerrorMessage(*conn_);
262 }
263
264 /// @brief Fetches all of the rows currently in the table
265 ///
266 /// Executes a select statement which returns all of the rows in the
267 /// basics table, in their order of insertion. Each row contains all
268 /// of the defined columns, in the order they are defined.
269 ///
270 /// @param r pointer which will contain the result set returned by the
271 /// statement's execution.
272 /// @param exp_rows expected number of rows fetched. (This can be 0).
273 /// @lineno line number from where the call was invoked
274 ///
275 /// Asserts if the result set status does not equal the expected outcome.
fetchRows(PgSqlResultPtr & r,int exp_rows,int line)276 void fetchRows(PgSqlResultPtr& r, int exp_rows, int line) {
277 std::string sql =
278 "SELECT"
279 " id, bool_col, bytea_col, bigint_col, smallint_col, "
280 " int_col, text_col,"
281 " extract(epoch from timestamp_col)::bigint as timestamp_col,"
282 " varchar_col FROM basics";
283
284 runSql(r, sql, PGRES_TUPLES_OK, line);
285 ASSERT_EQ(r->getRows(), exp_rows) << "fetch at line: " << line
286 << " wrong row count, expected: " << exp_rows
287 << " , have: " << r->getRows();
288
289 }
290
291 /// @brief Database connection
292 PgSqlConnectionPtr conn_;
293
294 /// @brief List of column names as we expect them to be in fetched rows
295 std::vector<std::string> expectedColNames_;
296 };
297
298 // Macros defined to ease passing invocation line number for output tracing
299 // (Yes I could have used scoped tracing but that's so ugly in code...)
300 #define RUN_SQL(a,b,c) (runSql(a,b,c, __LINE__))
301 #define RUN_PREP(a,b,c,d) (runPreparedStatement(a,b,c,d, __LINE__))
302 #define FETCH_ROWS(a,b) (fetchRows(a,b,__LINE__))
303 #define WIPE_ROWS(a) (RUN_SQL(a, "DELETE FROM BASICS", PGRES_COMMAND_OK))
304
305 /// @brief Verifies that PgResultSet row and column meta-data is correct
TEST_F(PgSqlBasicsTest,rowColumnBasics)306 TEST_F(PgSqlBasicsTest, rowColumnBasics) {
307 // We fetch the table contents, which at this point should be no rows.
308 PgSqlResultPtr r;
309 FETCH_ROWS(r, 0);
310
311 // Column meta-data is determined by the select statement and is
312 // present whether or not any rows were returned.
313 EXPECT_EQ(r->getCols(), NUM_BASIC_COLS);
314
315 // Negative indexes should be out of range. We test negative values
316 // as PostgreSQL functions accept column values as type int.
317 EXPECT_THROW(r->colCheck(-1), DbOperationError);
318
319 // Iterate over the column indexes verifying:
320 // 1. the column is valid
321 // 2. the result set column name matches the expected column name
322 for (int i = 0; i < NUM_BASIC_COLS; i++) {
323 EXPECT_NO_THROW(r->colCheck(i));
324 EXPECT_EQ(r->getColumnLabel(i), expectedColumnName(i));
325 }
326
327 // Verify above range column value is detected.
328 EXPECT_THROW(r->colCheck(NUM_BASIC_COLS), DbOperationError);
329
330 // Verify the fetching a column label for out of range columns
331 // do NOT throw.
332 std::string label;
333 ASSERT_NO_THROW(label = r->getColumnLabel(-1));
334 EXPECT_EQ(label, "Unknown column:-1");
335 ASSERT_NO_THROW(label = r->getColumnLabel(NUM_BASIC_COLS));
336 std::ostringstream os;
337 os << "Unknown column:" << NUM_BASIC_COLS;
338 EXPECT_EQ(label, os.str());
339
340 // Verify row count and checking. With an empty result set all values of
341 // row are invalid.
342 EXPECT_EQ(r->getRows(), 0);
343 EXPECT_THROW(r->rowCheck(-1), DbOperationError);
344 EXPECT_THROW(r->rowCheck(0), DbOperationError);
345 EXPECT_THROW(r->rowCheck(1), DbOperationError);
346
347 // Verify Row-column check will always fail with an empty result set.
348 EXPECT_THROW(r->rowColCheck(-1, 1), DbOperationError);
349 EXPECT_THROW(r->rowColCheck(0, 1), DbOperationError);
350 EXPECT_THROW(r->rowColCheck(1, 1), DbOperationError);
351
352 // Insert three minimal rows. We don't really care about column content
353 // for this test.
354 int num_rows = 3;
355 for (int i = 0; i < num_rows; i++) {
356 RUN_SQL(r, "INSERT INTO basics (bool_col) VALUES ('t')",
357 PGRES_COMMAND_OK);
358 }
359
360 // Fetch the newly created rows.
361 FETCH_ROWS(r, num_rows);
362
363 // Verify we row count and checking
364 EXPECT_EQ(r->getRows(), num_rows);
365 EXPECT_THROW(r->rowCheck(-1), DbOperationError);
366
367 // Iterate over the row count, verifying that expected rows are valid
368 for (int i = 0; i < num_rows; i++) {
369 EXPECT_NO_THROW(r->rowCheck(i));
370 EXPECT_NO_THROW(r->rowColCheck(i, 0));
371 }
372
373 // Verify an above range row is detected.
374 EXPECT_THROW(r->rowCheck(num_rows), DbOperationError);
375 }
376
377 /// @brief Verify that we can read and write BOOL columns
TEST_F(PgSqlBasicsTest,boolTest)378 TEST_F(PgSqlBasicsTest, boolTest) {
379 // Create a prepared statement for inserting bool_col
380 const char* st_name = "bool_insert";
381 PgSqlTaggedStatement statement[] = {
382 {1, { OID_BOOL }, st_name,
383 "INSERT INTO BASICS (bool_col) values ($1)" }
384 };
385
386 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
387
388 bool bools[] = { true, false };
389 PsqlBindArrayPtr bind_array(new PsqlBindArray());
390 PgSqlResultPtr r;
391
392 // Insert bool rows
393 for (int i = 0; i < 2; ++i) {
394 bind_array.reset(new PsqlBindArray());
395 bind_array->add(bools[i]);
396 RUN_PREP(r,statement[0], bind_array, PGRES_COMMAND_OK);
397 }
398
399 // Fetch the newly inserted rows.
400 FETCH_ROWS(r, 2);
401
402 // Verify the fetched bool values are what we expect.
403 bool fetched_bool;
404 int row = 0;
405 for ( ; row < 2; ++row ) {
406 // Verify the column is not null.
407 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, BOOL_COL));
408
409 // Fetch and verify the column value
410 fetched_bool = !bools[row];
411 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, BOOL_COL,
412 fetched_bool));
413 EXPECT_EQ(fetched_bool, bools[row]);
414 }
415
416 // While we here, verify that bad row throws
417 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, 1, fetched_bool),
418 DbOperationError);
419
420 // Clean out the table
421 WIPE_ROWS(r);
422
423 // Verify we can insert a NULL boolean
424 bind_array.reset(new PsqlBindArray());
425 bind_array->addNull();
426
427 // Run the insert with the bind array.
428 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
429
430 // Fetch the newly inserted row.
431 FETCH_ROWS(r, 1);
432
433 // Verify the column is null.
434 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, 1));
435 }
436
437 /// @brief Verify that we can read and write BYTEA columns
TEST_F(PgSqlBasicsTest,byteaTest)438 TEST_F(PgSqlBasicsTest, byteaTest) {
439 const char* st_name = "bytea_insert";
440 PgSqlTaggedStatement statement[] = {
441 {1, { OID_BYTEA }, st_name,
442 "INSERT INTO BASICS (bytea_col) values ($1)" }
443 };
444
445 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
446
447 const uint8_t bytes[] = {
448 0x01, 0x02, 0x03, 0x04
449 };
450 std::vector<uint8_t> vbytes(bytes, bytes + sizeof(bytes));
451
452 // Verify we can insert bytea from a vector
453 PsqlBindArrayPtr bind_array(new PsqlBindArray());
454 PgSqlResultPtr r;
455 bind_array->add(vbytes);
456 RUN_PREP(r,statement[0], bind_array, PGRES_COMMAND_OK);
457
458 // Verify we can insert bytea from a buffer.
459 bind_array.reset(new PsqlBindArray());
460 bind_array->add(bytes, sizeof(bytes));
461 RUN_PREP(r,statement[0], bind_array, PGRES_COMMAND_OK);
462
463 // Fetch the newly inserted rows.
464 int num_rows = 2;
465 FETCH_ROWS(r, num_rows);
466
467 uint8_t fetched_bytes[sizeof(bytes)];
468 size_t byte_count;
469 int row = 0;
470 for ( ; row < num_rows; ++row) {
471 // Verify the column is not null.
472 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, BYTEA_COL));
473
474 // Extract the data into a correctly sized buffer
475 memset(fetched_bytes, 0, sizeof(fetched_bytes));
476 ASSERT_NO_THROW(PgSqlExchange::convertFromBytea(*r, row, BYTEA_COL,
477 fetched_bytes,
478 sizeof(fetched_bytes),
479 byte_count));
480
481 // Verify the data is correct
482 ASSERT_EQ(byte_count, sizeof(bytes));
483 for (int i = 0; i < sizeof(bytes); i++) {
484 ASSERT_EQ(bytes[i], fetched_bytes[i]);
485 }
486 }
487
488 // While we here, verify that bad row throws
489 ASSERT_THROW(PgSqlExchange::convertFromBytea(*r, row, BYTEA_COL,
490 fetched_bytes,
491 sizeof(fetched_bytes),
492 byte_count), DbOperationError);
493
494 // Verify that too small of a buffer throws
495 ASSERT_THROW(PgSqlExchange::convertFromBytea(*r, 0, BYTEA_COL,
496 fetched_bytes,
497 sizeof(fetched_bytes) - 1,
498 byte_count), DbOperationError);
499
500 // Clean out the table
501 WIPE_ROWS(r);
502
503 // Verify we can insert a NULL for a bytea column
504 bind_array.reset(new PsqlBindArray());
505 bind_array->addNull(PsqlBindArray::BINARY_FMT);
506 RUN_PREP(r,statement[0], bind_array, PGRES_COMMAND_OK);
507
508 // Fetch the newly inserted row.
509 FETCH_ROWS(r, 1);
510
511 // Verify the column is null.
512 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, BYTEA_COL));
513
514 // Verify that fetching a NULL bytea, returns 0 byte count
515 ASSERT_NO_THROW(PgSqlExchange::convertFromBytea(*r, 0, BYTEA_COL,
516 fetched_bytes,
517 sizeof(fetched_bytes),
518 byte_count));
519 EXPECT_EQ(byte_count, 0);
520 }
521
522 /// @brief Verify that we can read and write BIGINT columns
TEST_F(PgSqlBasicsTest,bigIntTest)523 TEST_F(PgSqlBasicsTest, bigIntTest) {
524 // Create a prepared statement for inserting BIGINT
525 const char* st_name = "bigint_insert";
526 PgSqlTaggedStatement statement[] = {
527 { 1, { OID_INT8 }, st_name,
528 "INSERT INTO BASICS (bigint_col) values ($1)" }
529 };
530
531 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
532
533 // Build our reference list of reference values
534 std::vector<int64_t> ints;
535 ints.push_back(-1);
536 ints.push_back(0);
537 ints.push_back(0x7fffffffffffffff);
538 ints.push_back(0xffffffffffffffff);
539
540 // Insert a row for each reference value
541 PsqlBindArrayPtr bind_array;
542 PgSqlResultPtr r;
543 for (int i = 0; i < ints.size(); ++i) {
544 bind_array.reset(new PsqlBindArray());
545 bind_array->add(ints[i]);
546 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
547 }
548
549 // Fetch the newly inserted rows.
550 FETCH_ROWS(r, ints.size());
551
552 // Iterate over the rows, verifying each value against its reference
553 int64_t fetched_int;
554 int row = 0;
555 for ( ; row < ints.size(); ++row ) {
556 // Verify the column is not null.
557 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, BIGINT_COL));
558
559 // Fetch and verify the column value
560 fetched_int = 777;
561 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, BIGINT_COL,
562 fetched_int));
563 EXPECT_EQ(fetched_int, ints[row]);
564 }
565
566 // While we here, verify that bad row throws
567 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, BIGINT_COL,
568 fetched_int), DbOperationError);
569
570 // Clean out the table
571 WIPE_ROWS(r);
572
573 // Verify we can insert a NULL value.
574 bind_array.reset(new PsqlBindArray());
575 bind_array->addNull();
576 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
577
578 // Fetch the newly inserted row.
579 FETCH_ROWS(r, 1);
580
581 // Verify the column is null.
582 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, BIGINT_COL));
583 }
584
585 /// @brief Verify that we can read and write SMALLINT columns
TEST_F(PgSqlBasicsTest,smallIntTest)586 TEST_F(PgSqlBasicsTest, smallIntTest) {
587 // Create a prepared statement for inserting a SMALLINT
588 const char* st_name = "smallint_insert";
589 PgSqlTaggedStatement statement[] = {
590 { 1, { OID_INT2 }, st_name,
591 "INSERT INTO BASICS (smallint_col) values ($1)" }
592 };
593
594 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
595
596 // Build our reference list of reference values
597 std::vector<int16_t>ints;
598 ints.push_back(-1);
599 ints.push_back(0);
600 ints.push_back(0x7fff);
601 ints.push_back(0xffff);
602
603 // Insert a row for each reference value
604 PsqlBindArrayPtr bind_array;
605 PgSqlResultPtr r;
606 for (int i = 0; i < ints.size(); ++i) {
607 bind_array.reset(new PsqlBindArray());
608 bind_array->add(ints[i]);
609 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
610 }
611
612 // Fetch the newly inserted rows.
613 FETCH_ROWS(r, ints.size());
614
615 // Iterate over the rows, verifying each value against its reference
616 int16_t fetched_int;
617 int row = 0;
618 for ( ; row < ints.size(); ++row ) {
619 // Verify the column is not null.
620 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, SMALLINT_COL));
621
622 // Fetch and verify the column value
623 fetched_int = 777;
624 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, SMALLINT_COL,
625 fetched_int));
626 EXPECT_EQ(fetched_int, ints[row]);
627 }
628
629 // While we here, verify that bad row throws
630 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, SMALLINT_COL,
631 fetched_int),
632 DbOperationError);
633
634 // Clean out the table
635 WIPE_ROWS(r);
636
637 // Verify we can insert a NULL value.
638 bind_array.reset(new PsqlBindArray());
639 bind_array->addNull();
640 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
641
642 // Fetch the newly inserted row.
643 FETCH_ROWS(r, 1);
644
645 // Verify the column is null.
646 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, SMALLINT_COL));
647 }
648
649 /// @brief Verify that we can read and write INT columns
TEST_F(PgSqlBasicsTest,intTest)650 TEST_F(PgSqlBasicsTest, intTest) {
651 // Create a prepared statement for inserting an INT
652 const char* st_name = "int_insert";
653 PgSqlTaggedStatement statement[] = {
654 { 1, { OID_INT4 }, st_name,
655 "INSERT INTO BASICS (int_col) values ($1)" }
656 };
657
658 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
659
660 // Build our reference list of reference values
661 std::vector<int32_t> ints;
662 ints.push_back(-1);
663 ints.push_back(0);
664 ints.push_back(0x7fffffff);
665 ints.push_back(0xffffffff);
666
667 // Insert a row for each reference value
668 PsqlBindArrayPtr bind_array;
669 PgSqlResultPtr r;
670 for (int i = 0; i < ints.size(); ++i) {
671 bind_array.reset(new PsqlBindArray());
672 bind_array->add(ints[i]);
673 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
674 }
675
676 // Fetch the newly inserted rows.
677 FETCH_ROWS(r, ints.size());
678
679 // Iterate over the rows, verifying each value against its reference
680 int32_t fetched_int;
681 int row = 0;
682 for ( ; row < ints.size(); ++row ) {
683 // Verify the column is not null.
684 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, INT_COL));
685
686 // Fetch and verify the column value
687 fetched_int = 777;
688 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, INT_COL,
689 fetched_int));
690 EXPECT_EQ(fetched_int, ints[row]);
691 }
692
693 // While we here, verify that bad row throws
694 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, INT_COL, fetched_int),
695 DbOperationError);
696
697 // Clean out the table
698 WIPE_ROWS(r);
699
700 // Verify we can insert a NULL value.
701 bind_array.reset(new PsqlBindArray());
702 bind_array->addNull();
703 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
704
705 // Fetch the newly inserted rows
706 FETCH_ROWS(r, 1);
707
708 // Verify the column is null.
709 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, INT_COL));
710 }
711
712 /// @brief Verify that we can read and write TEXT columns
TEST_F(PgSqlBasicsTest,textTest)713 TEST_F(PgSqlBasicsTest, textTest) {
714 // Create a prepared statement for inserting TEXT
715 PgSqlTaggedStatement statement[] = {
716 { 1, { OID_TEXT }, "text_insert",
717 "INSERT INTO BASICS (text_col) values ($1)" }
718 };
719
720 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
721
722 // Our reference string.
723 std::string ref_string = "This is a text string";
724
725 // Insert the reference from std::string
726 PsqlBindArrayPtr bind_array;
727 PgSqlResultPtr r;
728 bind_array.reset(new PsqlBindArray());
729 bind_array->add(ref_string);
730 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
731
732 // Insert the reference from a buffer
733 bind_array.reset(new PsqlBindArray());
734 bind_array->add(ref_string.c_str());
735 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
736
737 // Fetch the newly inserted rows.
738 FETCH_ROWS(r, 2);
739
740 // Iterate over the rows, verifying the value against the reference
741 std::string fetched_str;
742 int row = 0;
743 for ( ; row < 2; ++row ) {
744 // Verify the column is not null.
745 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, TEXT_COL));
746
747 // Fetch and verify the column value
748 fetched_str = "";
749 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, TEXT_COL,
750 fetched_str));
751 EXPECT_EQ(fetched_str, ref_string);
752 }
753
754 // While we here, verify that bad row throws
755 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, TEXT_COL, fetched_str),
756 DbOperationError);
757
758 // Clean out the table
759 WIPE_ROWS(r);
760
761 // Verify we can insert a NULL value.
762 bind_array.reset(new PsqlBindArray());
763 bind_array->addNull();
764 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
765
766 // Fetch the newly inserted row.
767 FETCH_ROWS(r, 1);
768
769 // Verify the column is null.
770 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, TEXT_COL));
771 }
772
773 /// @brief Verify that we can read and write VARCHAR columns
TEST_F(PgSqlBasicsTest,varcharTest)774 TEST_F(PgSqlBasicsTest, varcharTest) {
775 // Create a prepared statement for inserting a VARCHAR
776 PgSqlTaggedStatement statement[] = {
777 { 1, { OID_VARCHAR }, "varchar_insert",
778 "INSERT INTO BASICS (varchar_col) values ($1)" }
779 };
780
781 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
782
783 // Our reference string.
784 std::string ref_string = "This is a varchar string";
785
786 // Insert the reference from std::string
787 PsqlBindArrayPtr bind_array;
788 PgSqlResultPtr r;
789 bind_array.reset(new PsqlBindArray());
790 bind_array->add(ref_string);
791 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
792
793 // Insert the reference from a buffer
794 bind_array.reset(new PsqlBindArray());
795 bind_array->add(ref_string.c_str());
796 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
797
798 // Fetch the newly inserted rows.
799 FETCH_ROWS(r, 2);
800
801 // Iterate over the rows, verifying the value against the reference
802 std::string fetched_str;
803 int row = 0;
804 for ( ; row < 2; ++row ) {
805 // Verify the column is not null.
806 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, VARCHAR_COL));
807
808 // Fetch and verify the column value
809 fetched_str = "";
810 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, VARCHAR_COL,
811 fetched_str));
812 EXPECT_EQ(fetched_str, ref_string);
813 }
814
815 // While we here, verify that bad row throws
816 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, VARCHAR_COL,
817 fetched_str), DbOperationError);
818
819 // Clean out the table
820 WIPE_ROWS(r);
821
822 // Verify we can insert a NULL value.
823 bind_array.reset(new PsqlBindArray());
824 bind_array->addNull();
825 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
826
827 // Fetch the newly inserted rows
828 FETCH_ROWS(r, 1);
829
830 // Verify the column is null.
831 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, VARCHAR_COL));
832 }
833
834 /// @brief Verify that we can read and write TIMESTAMP columns
TEST_F(PgSqlBasicsTest,timeStampTest)835 TEST_F(PgSqlBasicsTest, timeStampTest) {
836 // Create a prepared statement for inserting a TIMESTAMP
837 PgSqlTaggedStatement statement[] = {
838 { 1, { OID_TIMESTAMP }, "timestamp_insert",
839 "INSERT INTO BASICS (timestamp_col) values ($1)" }
840 };
841
842 ASSERT_NO_THROW(conn_->prepareStatement(statement[0]));
843
844 // Build our list of reference times
845 time_t now;
846 time(&now);
847 std::vector<time_t> times;
848 times.push_back(now);
849 times.push_back(DatabaseConnection::MAX_DB_TIME);
850 // Note on a 32-bit OS this value is really -1. PosgreSQL will store it
851 // and return it intact.
852 times.push_back(0xFFFFFFFF);
853
854 // Insert a row for each reference value
855 PsqlBindArrayPtr bind_array;
856 PgSqlResultPtr r;
857 std::string time_str;
858 for (int i = 0; i < times.size(); ++i) {
859 // Timestamps are inserted as strings so convert them first
860 ASSERT_NO_THROW(time_str =
861 PgSqlExchange::convertToDatabaseTime(times[i]));
862
863 // Add it to the bind array and insert it
864 bind_array.reset(new PsqlBindArray());
865 bind_array->add(time_str);
866 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
867 }
868
869 // Insert a row with ref time plus one day
870 times.push_back(now + 24*3600);
871 ASSERT_NO_THROW(time_str =
872 PgSqlExchange::convertToDatabaseTime(times[0], 24*3600));
873
874 bind_array.reset(new PsqlBindArray());
875 bind_array->add(time_str);
876 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
877
878 // Fetch the newly inserted rows.
879 FETCH_ROWS(r, times.size());
880
881 // Iterate over the rows, verifying the value against its reference
882 std::string fetched_str;
883 int row = 0;
884 for ( ; row < times.size(); ++row ) {
885 // Verify the column is not null.
886 ASSERT_FALSE(PgSqlExchange::isColumnNull(*r, row, TIMESTAMP_COL));
887
888 // Fetch and verify the column value
889 fetched_str = "";
890 ASSERT_NO_THROW(PgSqlExchange::getColumnValue(*r, row, TIMESTAMP_COL,
891 fetched_str));
892
893 time_t fetched_time;
894 ASSERT_NO_THROW(fetched_time =
895 PgSqlExchange::convertFromDatabaseTime(fetched_str));
896 EXPECT_EQ(fetched_time, times[row]) << " row: " << row;
897 }
898
899 // While we here, verify that bad row throws
900 ASSERT_THROW(PgSqlExchange::getColumnValue(*r, row, TIMESTAMP_COL,
901 fetched_str), DbOperationError);
902
903 // Clean out the table
904 WIPE_ROWS(r);
905
906 // Verify we can insert a NULL value.
907 bind_array.reset(new PsqlBindArray());
908 bind_array->addNull();
909 RUN_PREP(r, statement[0], bind_array, PGRES_COMMAND_OK);
910
911 // Fetch the newly inserted rows
912 FETCH_ROWS(r, 1);
913
914 // Verify the column is null.
915 ASSERT_TRUE(PgSqlExchange::isColumnNull(*r, 0, TIMESTAMP_COL));
916
917 // Verify exceeding max time throws
918 ASSERT_THROW(PgSqlExchange::convertToDatabaseTime(times[0],
919 DatabaseConnection::
920 MAX_DB_TIME), BadValue);
921 }
922
923 }; // namespace
924