test-postgresql.cpp 30.2 KB
Newer Older
1
//
msobczak's avatar
msobczak committed
2
// Copyright (C) 2004-2008 Maciej Sobczak, Stephen Hutton
3 4 5 6 7
// Distributed under the Boost Software License, Version 1.0.
// (See accompanying file LICENSE_1_0.txt or copy at
// http://www.boost.org/LICENSE_1_0.txt)
//

8 9
#include "soci/soci.h"
#include "soci/postgresql/soci-postgresql.h"
10
#include "common-tests.h"
11 12 13 14
#include <iostream>
#include <sstream>
#include <string>
#include <cmath>
Maciej Sobczak's avatar
Maciej Sobczak committed
15
#include <cstring>
16
#include <ctime>
17
#include <cstdlib>
18

19 20
using namespace soci;
using namespace soci::tests;
21 22

std::string connectString;
Mateusz Loskot's avatar
Mateusz Loskot committed
23
backend_factory const &backEnd = *soci::factory_postgresql();
24

25
// Postgres-specific tests
26

27
struct oid_table_creator : public table_creator_base
28
{
29
    oid_table_creator(soci::session& sql)
30
    : table_creator_base(sql)
31
    {
32
        sql << "create table soci_test ("
33 34 35
                " id integer,"
                " name varchar(100)"
                ") with oids";
36
    }
37
};
38 39 40 41 42

// ROWID test
// Note: in PostgreSQL, there is no ROWID, there is OID.
// It is still provided as a separate type for "portability",
// whatever that means.
43
TEST_CASE("PostgreSQL ROWID", "[postgresql][rowid][oid]")
44
{
45
    soci::session sql(backEnd, connectString);
46

47
    oid_table_creator tableCreator(sql);
48

49
    sql << "insert into soci_test(id, name) values(7, \'John\')";
50

51 52
    rowid rid(sql);
    sql << "select oid from soci_test where id = 7", into(rid);
53

54 55
    int id;
    std::string name;
56

57 58
    sql << "select id, name from soci_test where oid = :rid",
        into(id), into(name), use(rid);
59

60 61 62 63 64 65
    CHECK(id == 7);
    CHECK(name == "John");
}

TEST_CASE("PostgreSQL prepare error", "[postgresql][exception]")
{
66
    soci::session sql(backEnd, connectString);
67 68 69 70

    // Must not cause the application to crash.
    statement st(sql);
    st.prepare(""); // Throws an exception in some versions.
71 72
}

73
// function call test
74
class function_creator : function_creator_base
75
{
76 77
public:

78
    function_creator(soci::session & sql)
msobczak's avatar
msobczak committed
79
    : function_creator_base(sql)
80
    {
81 82
        // before a language can be used it must be defined
        // if it has already been defined then an error will occur
msobczak's avatar
msobczak committed
83
        try { sql << "create language plpgsql"; }
84
        catch (soci_error const &) {} // ignore if error
85

msobczak's avatar
msobczak committed
86
        sql  <<
87
            "create or replace function soci_test(msg varchar) "
88
            "returns varchar as $$ "
89
            "declare x int := 1;"
90 91 92
            "begin "
            "  return msg; "
            "end $$ language plpgsql";
93 94 95 96
    }

protected:

97
    std::string drop_statement()
98 99
    {
        return "drop function soci_test(varchar)";
100 101 102
    }
};

103
TEST_CASE("PostgreSQL function call", "[postgresql][function]")
104
{
105
    soci::session sql(backEnd, connectString);
106

107
    function_creator functionCreator(sql);
108

109 110
    std::string in("my message");
    std::string out;
111

112 113 114 115
    statement st = (sql.prepare <<
        "select soci_test(:input)",
        into(out),
        use(in, "input"));
116

117 118
    st.execute(true);
    CHECK(out == in);
119

120 121 122 123
    // explicit procedure syntax
    {
        std::string in("my message2");
        std::string out;
124

125 126 127
        procedure proc = (sql.prepare <<
            "soci_test(:input)",
            into(out), use(in, "input"));
128

129 130
        proc.execute(true);
        CHECK(out == in);
131 132 133
    }
}

134
// BLOB test
135
struct blob_table_creator : public table_creator_base
136
{
137
    blob_table_creator(soci::session & sql)
msobczak's avatar
msobczak committed
138
    : table_creator_base(sql)
139
    {
msobczak's avatar
msobczak committed
140
        sql <<
141 142 143 144
             "create table soci_test ("
             "    id integer,"
             "    img oid"
             ")";
145
    }
146
};
147

148
TEST_CASE("PostgreSQL blob", "[postgresql][blob]")
149
{
150
    {
151
        soci::session sql(backEnd, connectString);
152

153
        blob_table_creator tableCreator(sql);
154

155
        char buf[] = "abcdefghijklmnopqrstuvwxyz";
156

157
        sql << "insert into soci_test(id, img) values(7, lo_creat(-1))";
158

159 160
        // in PostgreSQL, BLOB operations must be within transaction block
        transaction tr(sql);
161

162 163 164 165 166
        {
            blob b(sql);

            sql << "select img from soci_test where id = 7", into(b);
            CHECK(b.get_len() == 0);
167

168 169
            b.write(0, buf, sizeof(buf));
            CHECK(b.get_len() == sizeof(buf));
170

171 172 173 174 175 176 177 178 179 180 181
            b.append(buf, sizeof(buf));
            CHECK(b.get_len() == 2 * sizeof(buf));
        }
        {
            blob b(sql);
            sql << "select img from soci_test where id = 7", into(b);
            CHECK(b.get_len() == 2 * sizeof(buf));
            char buf2[100];
            b.read(0, buf2, 10);
            CHECK(std::strncmp(buf2, "abcdefghij", 10) == 0);
        }
182

183 184 185
        unsigned long oid;
        sql << "select img from soci_test where id = 7", into(oid);
        sql << "select lo_unlink(" << oid << ")";
186
    }
187 188

    // additional sibling test for read_from_start and write_from_start
189
    {
190
        soci::session sql(backEnd, connectString);
191 192 193 194

        blob_table_creator tableCreator(sql);

        char buf[] = "abcdefghijklmnopqrstuvwxyz";
195

196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225
        sql << "insert into soci_test(id, img) values(7, lo_creat(-1))";

        // in PostgreSQL, BLOB operations must be within transaction block
        transaction tr(sql);

        {
            blob b(sql);

            sql << "select img from soci_test where id = 7", into(b);
            CHECK(b.get_len() == 0);

            b.write_from_start(buf, sizeof(buf));
            CHECK(b.get_len() == sizeof(buf));

            b.append(buf, sizeof(buf));
            CHECK(b.get_len() == 2 * sizeof(buf));
        }
        {
            blob b(sql);
            sql << "select img from soci_test where id = 7", into(b);
            CHECK(b.get_len() == 2 * sizeof(buf));
            char buf2[100];
            b.read_from_start(buf2, 10);
            CHECK(std::strncmp(buf2, "abcdefghij", 10) == 0);
        }

        unsigned long oid;
        sql << "select img from soci_test where id = 7", into(oid);
        sql << "select lo_unlink(" << oid << ")";
    }
226 227
}

msobczak's avatar
msobczak committed
228 229
struct longlong_table_creator : table_creator_base
{
230
    longlong_table_creator(soci::session & sql)
msobczak's avatar
msobczak committed
231 232 233 234 235 236
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val int8)";
    }
};

237
// long long test
238
TEST_CASE("PostgreSQL long long", "[postgresql][longlong]")
msobczak's avatar
msobczak committed
239
{
240
    soci::session sql(backEnd, connectString);
msobczak's avatar
msobczak committed
241

242
    longlong_table_creator tableCreator(sql);
msobczak's avatar
msobczak committed
243

244 245
    long long v1 = 1000000000000LL;
    sql << "insert into soci_test(val) values(:val)", use(v1);
msobczak's avatar
msobczak committed
246

247 248
    long long v2 = 0LL;
    sql << "select val from soci_test", into(v2);
msobczak's avatar
msobczak committed
249

250 251
    CHECK(v2 == v1);
}
252

253 254 255
// vector<long long>
TEST_CASE("PostgreSQL vector long long", "[postgresql][vector][longlong]")
{
256
    soci::session sql(backEnd, connectString);
257

258
    longlong_table_creator tableCreator(sql);
259

260 261 262 263 264 265
    std::vector<long long> v1;
    v1.push_back(1000000000000LL);
    v1.push_back(1000000000001LL);
    v1.push_back(1000000000002LL);
    v1.push_back(1000000000003LL);
    v1.push_back(1000000000004LL);
266

267
    sql << "insert into soci_test(val) values(:val)", use(v1);
268

269 270
    std::vector<long long> v2(10);
    sql << "select val from soci_test order by val desc", into(v2);
271

272 273 274 275 276 277
    REQUIRE(v2.size() == 5);
    CHECK(v2[0] == 1000000000004LL);
    CHECK(v2[1] == 1000000000003LL);
    CHECK(v2[2] == 1000000000002LL);
    CHECK(v2[3] == 1000000000001LL);
    CHECK(v2[4] == 1000000000000LL);
msobczak's avatar
msobczak committed
278 279
}

280
// unsigned long long test
281
TEST_CASE("PostgreSQL unsigned long long", "[postgresql][unsigned][longlong]")
282
{
283
    soci::session sql(backEnd, connectString);
284

285
    longlong_table_creator tableCreator(sql);
286

287 288
    unsigned long long v1 = 1000000000000ULL;
    sql << "insert into soci_test(val) values(:val)", use(v1);
289

290 291
    unsigned long long v2 = 0ULL;
    sql << "select val from soci_test", into(v2);
292

293
    CHECK(v2 == v1);
294 295
}

296 297
struct boolean_table_creator : table_creator_base
{
298
    boolean_table_creator(soci::session & sql)
299 300 301 302 303 304
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val boolean)";
    }
};

305
TEST_CASE("PostgreSQL boolean", "[postgresql][boolean]")
306
{
307
    soci::session sql(backEnd, connectString);
308

309
    boolean_table_creator tableCreator(sql);
310

311
    int i1 = 0;
312

313
    sql << "insert into soci_test(val) values(:val)", use(i1);
314

315 316
    int i2 = 7;
    sql << "select val from soci_test", into(i2);
317

318
    CHECK(i2 == i1);
319

320 321 322
    sql << "update soci_test set val = true";
    sql << "select val from soci_test", into(i2);
    CHECK(i2 == 1);
323 324
}

325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349
struct uuid_table_creator : table_creator_base
{
    uuid_table_creator(soci::session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val uuid)";
    }
};

// uuid test
TEST_CASE("PostgreSQL uuid", "[postgresql][uuid]")
{
    soci::session sql(backEnd, connectString);

    uuid_table_creator tableCreator(sql);

    std::string v1("cd2dcb78-3817-442e-b12a-17c7e42669a0");
    sql << "insert into soci_test(val) values(:val)", use(v1);

    std::string v2;
    sql << "select val from soci_test", into(v2);

    CHECK(v2 == v1);
}

350 351
// dynamic backend test -- currently skipped by default
TEST_CASE("PostgreSQL dynamic backend", "[postgresql][backend][.]")
352 353 354
{
    try
    {
355
        soci::session sql("nosuchbackend://" + connectString);
356
        FAIL("expected exception not thrown");
357 358 359
    }
    catch (soci_error const & e)
    {
360 361
        CHECK(e.get_error_message() ==
            "Failed to open: libsoci_nosuchbackend.so");
362 363 364
    }

    {
msobczak's avatar
msobczak committed
365
        dynamic_backends::register_backend("pgsql", backEnd);
366

msobczak's avatar
msobczak committed
367
        std::vector<std::string> backends = dynamic_backends::list_all();
368 369
        REQUIRE(backends.size() == 1);
        CHECK(backends[0] == "pgsql");
370

msobczak's avatar
msobczak committed
371
        {
372
            soci::session sql("pgsql://" + connectString);
msobczak's avatar
msobczak committed
373
        }
374

msobczak's avatar
msobczak committed
375
        dynamic_backends::unload("pgsql");
376

msobczak's avatar
msobczak committed
377
        backends = dynamic_backends::list_all();
378
        CHECK(backends.empty());
379 380 381
    }

    {
382
        soci::session sql("postgresql://" + connectString);
383 384 385
    }
}

386
TEST_CASE("PostgreSQL literals", "[postgresql][into]")
387
{
388
    soci::session sql(backEnd, connectString);
389

390 391 392
    int i;
    sql << "select 123", into(i);
    CHECK(i == 123);
393

394 395 396 397 398 399 400 401 402 403
    try
    {
        sql << "select 'ABC'", into (i);
        FAIL("expected exception not thrown");
    }
    catch (soci_error const & e)
    {
        char const * expectedPrefix = "Cannot convert data";
        CAPTURE(e.what());
        CHECK(strncmp(e.what(), expectedPrefix, strlen(expectedPrefix)) == 0);
404 405 406
    }
}

407
TEST_CASE("PostgreSQL backend name", "[postgresql][backend]")
msobczak's avatar
msobczak committed
408
{
409
    soci::session sql(backEnd, connectString);
msobczak's avatar
msobczak committed
410

411
    CHECK(sql.get_backend_name() == "postgresql");
msobczak's avatar
msobczak committed
412 413
}

414
// test for double-colon cast in SQL expressions
415
TEST_CASE("PostgreSQL double colon cast", "[postgresql][cast]")
416
{
417
    soci::session sql(backEnd, connectString);
418

419 420 421 422
    int a = 123;
    int b = 0;
    sql << "select :a::integer", use(a), into(b);
    CHECK(b == a);
423 424
}

425
// test for date, time and timestamp parsing
426
TEST_CASE("PostgreSQL datetime", "[postgresql][datetime]")
427
{
428
    soci::session sql(backEnd, connectString);
429

430
    std::string someDate = "2009-06-17 22:51:03.123";
431
    std::tm t1 = std::tm(), t2 = std::tm(), t3 = std::tm();
432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458

    sql << "select :sd::date, :sd::time, :sd::timestamp",
        use(someDate, "sd"), into(t1), into(t2), into(t3);

    // t1 should contain only the date part
    CHECK(t1.tm_year == 2009 - 1900);
    CHECK(t1.tm_mon == 6 - 1);
    CHECK(t1.tm_mday == 17);
    CHECK(t1.tm_hour == 0);
    CHECK(t1.tm_min == 0);
    CHECK(t1.tm_sec == 0);

    // t2 should contain only the time of day part
    CHECK(t2.tm_year == 0);
    CHECK(t2.tm_mon == 0);
    CHECK(t2.tm_mday == 1);
    CHECK(t2.tm_hour == 22);
    CHECK(t2.tm_min == 51);
    CHECK(t2.tm_sec == 3);

    // t3 should contain all information
    CHECK(t3.tm_year == 2009 - 1900);
    CHECK(t3.tm_mon == 6 - 1);
    CHECK(t3.tm_mday == 17);
    CHECK(t3.tm_hour == 22);
    CHECK(t3.tm_min == 51);
    CHECK(t3.tm_sec == 3);
459 460
}

461 462 463 464
// test for number of affected rows

struct table_creator_for_test11 : table_creator_base
{
465
    table_creator_for_test11(soci::session & sql)
466 467 468 469 470 471
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val integer)";
    }
};

472
TEST_CASE("PostgreSQL get affected rows", "[postgresql][affected-rows]")
473
{
474
    soci::session sql(backEnd, connectString);
475

476
    table_creator_for_test11 tableCreator(sql);
477

478 479 480 481
    for (int i = 0; i != 10; i++)
    {
        sql << "insert into soci_test(val) values(:val)", use(i);
    }
482

483 484 485
    statement st1 = (sql.prepare <<
        "update soci_test set val = val + 1");
    st1.execute(false);
486

487
    CHECK(st1.get_affected_rows() == 10);
488

489 490 491
    statement st2 = (sql.prepare <<
        "delete from soci_test where val <= 5");
    st2.execute(false);
492

493
    CHECK(st2.get_affected_rows() == 5);
494 495
}

496 497 498 499
// test INSERT INTO ... RETURNING syntax

struct table_creator_for_test12 : table_creator_base
{
500
    table_creator_for_test12(soci::session & sql)
501 502 503 504 505 506
        : table_creator_base(sql)
    {
        sql << "create table soci_test(sid serial, txt text)";
    }
};

507
TEST_CASE("PostgreSQL insert into ... returning", "[postgresql]")
508
{
509
    soci::session sql(backEnd, connectString);
510

511
    table_creator_for_test12 tableCreator(sql);
512

513 514 515 516 517 518 519
    std::vector<long> ids(10);
    for (std::size_t i = 0; i != ids.size(); i++)
    {
        long sid(0);
        std::string txt("abc");
        sql << "insert into soci_test(txt) values(:txt) returning sid", use(txt, "txt"), into(sid);
        ids[i] = sid;
520 521
    }

522 523 524
    std::vector<long> ids2(ids.size());
    sql << "select sid from soci_test order by sid", into(ids2);
    CHECK(std::equal(ids.begin(), ids.end(), ids2.begin()));
525 526
}

527 528
struct bytea_table_creator : public table_creator_base
{
529
    bytea_table_creator(soci::session& sql)
530 531 532 533 534 535 536
        : table_creator_base(sql)
    {
        sql << "drop table if exists soci_test;";
        sql << "create table soci_test ( val bytea null )";
    }
};

537 538
TEST_CASE("PostgreSQL bytea", "[postgresql][bytea]")
{
539
    soci::session sql(backEnd, connectString);
540

541 542 543 544 545 546 547 548 549 550 551 552 553 554 555
    // PostgreSQL supports two different output formats for bytea values:
    // historical "escape" format, which is the only one supported until
    // PostgreSQL 9.0, and "hex" format used by default since 9.0, we need
    // to determine which one is actually in use.
    std::string bytea_output_format;
    sql << "select setting from pg_settings where name='bytea_output'",
           into(bytea_output_format);
    char const* expectedBytea;
    if (bytea_output_format.empty() || bytea_output_format == "escape")
      expectedBytea = "\\015\\014\\013\\012";
    else if (bytea_output_format == "hex")
      expectedBytea = "\\x0d0c0b0a";
    else
      throw std::runtime_error("Unknown PostgreSQL bytea_output \"" +
                               bytea_output_format + "\"");
556

557
    bytea_table_creator tableCreator(sql);
558

559 560 561 562 563
    int v = 0x0A0B0C0D;
    unsigned char* b = reinterpret_cast<unsigned char*>(&v);
    std::string data;
    std::copy(b, b + sizeof(v), std::back_inserter(data));
    {
564

565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580
        sql << "insert into soci_test(val) values(:val)", use(data);

        // 1) into string, no Oid mapping
        std::string bin1;
        sql << "select val from soci_test", into(bin1);
        CHECK(bin1 == expectedBytea);

        // 2) Oid-to-dt_string mapped
        row r;
        sql << "select * from soci_test", into(r);

        REQUIRE(r.size() == 1);
        column_properties const& props = r.get_properties(0);
        CHECK(props.get_data_type() == soci::dt_string);
        std::string bin2 = r.get<std::string>(0);
        CHECK(bin2 == expectedBytea);
Mateusz Loskot's avatar
Mateusz Loskot committed
581
    }
582 583
}

584
// json
585 586
struct table_creator_json : public table_creator_base
{
587
    table_creator_json(soci::session& sql)
588 589 590 591 592 593 594
    : table_creator_base(sql)
    {
        sql << "drop table if exists soci_json_test;";
        sql << "create table soci_json_test(data json)";
    }
};

595 596 597
// Return 9,2 for 9.2.3
typedef std::pair<int,int> server_version;

598
server_version get_postgresql_version(soci::session& sql)
599 600 601 602 603 604 605 606 607 608 609
{
    std::string version;
    std::pair<int,int> result;
    sql << "select version()",into(version);
    if (sscanf(version.c_str(),"PostgreSQL %i.%i", &result.first, &result.second) < 2)
    {
        throw std::runtime_error("Failed to retrieve PostgreSQL version number");
    }
    return result;
}

610
// Test JSON. Only valid for PostgreSQL Server 9.2++
611
TEST_CASE("PostgreSQL JSON", "[postgresql][json]")
612
{
613
    soci::session sql(backEnd, connectString);
614 615
    server_version version = get_postgresql_version(sql);
    if ( version >= server_version(9,2))
616 617
    {
        std::string result;
618 619
        std::string valid_input = "{\"tool\":\"soci\",\"result\":42}";
        std::string invalid_input = "{\"tool\":\"other\",\"result\":invalid}";
620 621 622 623

        table_creator_json tableCreator(sql);

        sql << "insert into soci_json_test (data) values(:data)",use(valid_input);
624
        sql << "select data from  soci_json_test",into(result);
625
        CHECK(result == valid_input);
626

627 628 629 630
        CHECK_THROWS_AS((
            sql << "insert into soci_json_test (data) values(:data)",use(invalid_input)),
            soci_error
        );
631 632 633
    }
    else
    {
634
        WARN("JSON test skipped (PostgreSQL >= 9.2 required, found " << version.first << "." << version.second << ")");
635 636 637
    }
}

638 639
struct table_creator_text : public table_creator_base
{
640
    table_creator_text(soci::session& sql) : table_creator_base(sql)
641 642 643 644 645 646 647 648 649
    {
        sql << "drop table if exists soci_test;";
        sql << "create table soci_test(name varchar(20))";
    }
};

// Test deallocate_prepared_statement called for non-existing statement
// which creation failed due to invalid SQL syntax.
// https://github.com/SOCI/soci/issues/116
650
TEST_CASE("PostgreSQL statement prepare failure", "[postgresql][prepare]")
651
{
652
    soci::session sql(backEnd, connectString);
653 654 655
    table_creator_text tableCreator(sql);

    try
656
    {
657 658 659 660 661 662 663 664 665 666
        // types mismatch should lead to PQprepare failure
        statement get_trades =
            (sql.prepare
                << "select * from soci_test where name=9999");
        FAIL("expected exception not thrown");
    }
    catch(soci_error const& e)
    {
        std::string const msg(e.what());
        CAPTURE(msg);
667

668 669 670
        // poor-man heuristics
        CHECK(msg.find("prepared statement") == std::string::npos);
        CHECK(msg.find("operator does not exist") != std::string::npos);
671 672 673
    }
}

674
// Test the support of PostgreSQL-style casts with ORM
675
TEST_CASE("PostgreSQL ORM cast", "[postgresql][orm]")
676
{
677
    soci::session sql(backEnd, connectString);
678 679 680 681 682
    values v;
    v.set("a", 1);
    sql << "select :a::int", use(v); // Must not throw an exception!
}

683
// Test the DDL and metadata functionality
684
TEST_CASE("PostgreSQL DDL with metadata", "[postgresql][ddl]")
685 686 687 688 689 690 691
{
    soci::session sql(backEnd, connectString);

    // note: prepare_column_descriptions expects l-value
    std::string ddl_t1 = "ddl_t1";
    std::string ddl_t2 = "ddl_t2";
    std::string ddl_t3 = "ddl_t3";
692

693 694 695 696
    // single-expression variant:
    sql.create_table(ddl_t1).column("i", soci::dt_integer).column("j", soci::dt_integer);

    // check whether this table was created:
697

698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715
    bool ddl_t1_found = false;
    bool ddl_t2_found = false;
    bool ddl_t3_found = false;
    std::string table_name;
    soci::statement st = (sql.prepare_table_names(), into(table_name));
    st.execute();
    while (st.fetch())
    {
        if (table_name == ddl_t1) { ddl_t1_found = true; }
        if (table_name == ddl_t2) { ddl_t2_found = true; }
        if (table_name == ddl_t3) { ddl_t3_found = true; }
    }

    CHECK(ddl_t1_found);
    CHECK(ddl_t2_found == false);
    CHECK(ddl_t3_found == false);

    // check whether ddl_t1 has the right structure:
716

717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747
    bool i_found = false;
    bool j_found = false;
    bool other_found = false;
    soci::column_info ci;
    soci::statement st1 = (sql.prepare_column_descriptions(ddl_t1), into(ci));
    st1.execute();
    while (st1.fetch())
    {
        if (ci.name == "i")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable);
            i_found = true;
        }
        else if (ci.name == "j")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable);
            j_found = true;
        }
        else
        {
            other_found = true;
        }
    }

    CHECK(i_found);
    CHECK(j_found);
    CHECK(other_found == false);

    // two more tables:
748

749 750 751 752 753 754 755 756 757 758 759
    // separately defined columns:
    // (note: statement is executed when ddl object goes out of scope)
    {
        soci::ddl_type ddl = sql.create_table(ddl_t2);
        ddl.column("i", soci::dt_integer);
        ddl.column("j", soci::dt_integer);
        ddl.column("k", soci::dt_integer)("not null");
        ddl.primary_key("t2_pk", "j");
    }

    sql.add_column(ddl_t1, "k", soci::dt_integer);
760
    sql.add_column(ddl_t1, "big", soci::dt_string, 0); // "unlimited" length -> text
761 762 763 764 765 766 767 768 769 770 771 772 773 774
    sql.drop_column(ddl_t1, "i");

    // or with constraint as in t2:
    sql.add_column(ddl_t2, "m", soci::dt_integer)("not null");

    // third table with a foreign key to the second one
    {
        soci::ddl_type ddl = sql.create_table(ddl_t3);
        ddl.column("x", soci::dt_integer);
        ddl.column("y", soci::dt_integer);
        ddl.foreign_key("t3_fk", "x", ddl_t2, "j");
    }

    // check if all tables were created:
775

776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792
    ddl_t1_found = false;
    ddl_t2_found = false;
    ddl_t3_found = false;
    soci::statement st2 = (sql.prepare_table_names(), into(table_name));
    st2.execute();
    while (st2.fetch())
    {
        if (table_name == ddl_t1) { ddl_t1_found = true; }
        if (table_name == ddl_t2) { ddl_t2_found = true; }
        if (table_name == ddl_t3) { ddl_t3_found = true; }
    }

    CHECK(ddl_t1_found);
    CHECK(ddl_t2_found);
    CHECK(ddl_t3_found);

    // check if ddl_t1 has the right structure (it was altered):
793

794 795 796
    i_found = false;
    j_found = false;
    bool k_found = false;
797
    bool big_found = false;
798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814
    other_found = false;
    soci::statement st3 = (sql.prepare_column_descriptions(ddl_t1), into(ci));
    st3.execute();
    while (st3.fetch())
    {
        if (ci.name == "j")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable);
            j_found = true;
        }
        else if (ci.name == "k")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable);
            k_found = true;
        }
815 816 817 818 819 820
        else if (ci.name == "big")
        {
            CHECK(ci.type == soci::dt_string);
            CHECK(ci.precision == 0); // "unlimited" for strings
            big_found = true;
        }
821 822 823 824 825 826 827 828 829
        else
        {
            other_found = true;
        }
    }

    CHECK(i_found == false);
    CHECK(j_found);
    CHECK(k_found);
830
    CHECK(big_found);
831
    CHECK(other_found == false);
832

833
    // check if ddl_t2 has the right structure:
834

835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882
    i_found = false;
    j_found = false;
    k_found = false;
    bool m_found = false;
    other_found = false;
    soci::statement st4 = (sql.prepare_column_descriptions(ddl_t2), into(ci));
    st4.execute();
    while (st4.fetch())
    {
        if (ci.name == "i")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable);
            i_found = true;
        }
        else if (ci.name == "j")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable == false); // primary key
            j_found = true;
        }
        else if (ci.name == "k")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable == false);
            k_found = true;
        }
        else if (ci.name == "m")
        {
            CHECK(ci.type == soci::dt_integer);
            CHECK(ci.nullable == false);
            m_found = true;
        }
        else
        {
            other_found = true;
        }
    }

    CHECK(i_found);
    CHECK(j_found);
    CHECK(k_found);
    CHECK(m_found);
    CHECK(other_found == false);

    sql.drop_table(ddl_t1);
    sql.drop_table(ddl_t3); // note: this must be dropped before ddl_t2
    sql.drop_table(ddl_t2);
883

884
    // check if all tables were dropped:
885

886 887 888 889 890 891 892 893 894 895 896 897 898 899 900
    ddl_t1_found = false;
    ddl_t2_found = false;
    ddl_t3_found = false;
    st2 = (sql.prepare_table_names(), into(table_name));
    st2.execute();
    while (st2.fetch())
    {
        if (table_name == ddl_t1) { ddl_t1_found = true; }
        if (table_name == ddl_t2) { ddl_t2_found = true; }
        if (table_name == ddl_t3) { ddl_t3_found = true; }
    }

    CHECK(ddl_t1_found == false);
    CHECK(ddl_t2_found == false);
    CHECK(ddl_t3_found == false);
901

902 903 904 905 906 907 908
    int i = -1;
    sql << "select lo_unlink(" + sql.empty_blob() + ")", into(i);
    CHECK(i == 1);
    sql << "select " + sql.nvl() + "(1, 2)", into(i);
    CHECK(i == 1);
    sql << "select " + sql.nvl() + "(NULL, 2)", into(i);
    CHECK(i == 2);
909 910
}

911
// Test the bulk iterators functionality
912
TEST_CASE("Bulk iterators", "[postgresql][bulkiters]")
913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 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 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992
{
    soci::session sql(backEnd, connectString);

    sql << "create table t (i integer)";

    // test bulk iterators with basic types
    {
        std::vector<int> v;
        v.push_back(10);
        v.push_back(20);
        v.push_back(30);
        v.push_back(40);
        v.push_back(50);

        std::size_t begin = 2;
        std::size_t end = 5;
        sql << "insert into t (i) values (:v)", soci::use(v, begin, end);

        v.clear();
        v.resize(20);
        begin = 5;
        end = 20;
        sql << "select i from t", soci::into(v, begin, end);

        CHECK(end == 8);
        for (std::size_t i = 0; i != 5; ++i)
        {
            CHECK(v[i] == 0);
        }
        CHECK(v[5] == 30);
        CHECK(v[6] == 40);
        CHECK(v[7] == 50);
        for (std::size_t i = end; i != 20; ++i)
        {
            CHECK(v[i] == 0);
        }
    }

    sql << "delete from t";

    // test bulk iterators with user types
    {
        std::vector<MyInt> v;
        v.push_back(MyInt(10));
        v.push_back(MyInt(20));
        v.push_back(MyInt(30));
        v.push_back(MyInt(40));
        v.push_back(MyInt(50));

        std::size_t begin = 2;
        std::size_t end = 5;
        sql << "insert into t (i) values (:v)", soci::use(v, begin, end);

        v.clear();
        for (std::size_t i = 0; i != 20; ++i)
        {
            v.push_back(MyInt(-1));
        }

        begin = 5;
        end = 20;
        sql << "select i from t", soci::into(v, begin, end);

        CHECK(end == 8);
        for (std::size_t i = 0; i != 5; ++i)
        {
            CHECK(v[i].get() == -1);
        }
        CHECK(v[5].get() == 30);
        CHECK(v[6].get() == 40);
        CHECK(v[7].get() == 50);
        for (std::size_t i = end; i != 20; ++i)
        {
            CHECK(v[i].get() == -1);
        }
    }

    sql << "drop table t";
}

993
//
994
// Support for soci Common Tests
995 996
//

997 998 999
// DDL Creation objects for common tests
struct table_creator_one : public table_creator_base
{
1000
    table_creator_one(soci::session & sql)
1001 1002 1003 1004
        : table_creator_base(sql)
    {
        sql << "create table soci_test(id integer, val integer, c char, "
                 "str varchar(20), sh int2, ul numeric(20), d float8, "
1005
                 "num76 numeric(7,6), "
1006 1007 1008 1009 1010 1011 1012
                 "tm timestamp, i1 integer, i2 integer, i3 integer, "
                 "name varchar(20))";
    }
};

struct table_creator_two : public table_creator_base
{
1013
    table_creator_two(soci::session & sql)
1014 1015 1016 1017 1018 1019 1020 1021 1022
        : table_creator_base(sql)
    {
        sql  << "create table soci_test(num_float float8, num_int integer,"
                     " name varchar(20), sometime timestamp, chr char)";
    }
};

struct table_creator_three : public table_creator_base
{
1023
    table_creator_three(soci::session & sql)
1024 1025 1026 1027 1028 1029 1030 1031 1032
        : table_creator_base(sql)
    {
        sql << "create table soci_test(name varchar(100) not null, "
            "phone varchar(15))";
    }
};

struct table_creator_for_get_affected_rows : table_creator_base
{
1033
    table_creator_for_get_affected_rows(soci::session & sql)
1034 1035 1036 1037 1038 1039
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val integer)";
    }
};

1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057
struct table_creator_for_xml : table_creator_base
{
    table_creator_for_xml(soci::session& sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(id integer, x xml)";
    }
};

struct table_creator_for_clob : table_creator_base
{
    table_creator_for_clob(soci::session& sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(id integer, s text)";
    }
};

1058
// Common tests context
1059
class test_context : public test_context_base
1060
{
1061
public:
1062 1063 1064
    test_context(backend_factory const &backEnd, std::string const &connectString)
        : test_context_base(backEnd, connectString)
    {}
1065

1066
    table_creator_base* table_creator_1(soci::session& s) const SOCI_OVERRIDE
1067
    {
1068
        return new table_creator_one(s);
1069 1070
    }

1071
    table_creator_base* table_creator_2(soci::session& s) const SOCI_OVERRIDE
1072
    {
1073
        return new table_creator_two(s);
1074 1075
    }

1076
    table_creator_base* table_creator_3(soci::session& s) const SOCI_OVERRIDE
1077
    {
1078
        return new table_creator_three(s);
1079 1080
    }

1081
    table_creator_base* table_creator_4(soci::session& s) const SOCI_OVERRIDE
1082 1083 1084 1085
    {
        return new table_creator_for_get_affected_rows(s);
    }

1086
    table_creator_base* table_creator_xml(soci::session& s) const SOCI_OVERRIDE
1087 1088 1089 1090
    {
        return new table_creator_for_xml(s);
    }

1091
    table_creator_base* table_creator_clob(soci::session& s) const SOCI_OVERRIDE
1092 1093 1094 1095
    {
        return new table_creator_for_clob(s);
    }

1096
    bool has_real_xml_support() const SOCI_OVERRIDE
1097 1098 1099 1100
    {
        return true;
    }

1101
    std::string to_date_time(std::string const &datdt_string) const SOCI_OVERRIDE
1102
    {
1103
        return "timestamptz(\'" + datdt_string + "\')";
1104
    }
1105

1106
    bool has_fp_bug() const SOCI_OVERRIDE
1107 1108 1109
    {
        return false;
    }
1110

1111
    std::string sql_length(std::string const& s) const SOCI_OVERRIDE
1112
    {
1113
        return "char_length(" + s + ")";
1114
    }
1115
};
1116 1117 1118

int main(int argc, char** argv)
{
1119 1120 1121 1122

#ifdef _MSC_VER
    // Redirect errors, unrecoverable problems, and assert() failures to STDERR,
    // instead of debug message window.
1123
    // This hack is required to run assert()-driven tests by Buildbot.
1124 1125 1126 1127 1128
    // NOTE: Comment this 2 lines for debugging with Visual C++ debugger to catch assertions inside.
    _CrtSetReportMode(_CRT_ERROR, _CRTDBG_MODE_FILE);
    _CrtSetReportFile(_CRT_ERROR, _CRTDBG_FILE_STDERR);
#endif //_MSC_VER

1129
    if (argc >= 2)
1130 1131
    {
        connectString = argv[1];
1132 1133 1134 1135 1136 1137 1138

        // Replace the connect string with the process name to ensure that
        // CATCH uses the correct name in its messages.
        argv[1] = argv[0];

        argc--;
        argv++;
1139 1140 1141 1142
    }
    else
    {
        std::cout << "usage: " << argv[0]
1143
            << " connectstring [test-arguments...]\n"
1144 1145
            << "example: " << argv[0]
            << " \'connect_string_for_PostgreSQL\'\n";
1146
        return EXIT_FAILURE;
1147 1148
    }

1149 1150 1151
    test_context tc(backEnd, connectString);

    return Catch::Session().run(argc, argv);
1152
}