test-postgresql.cpp 15.6 KB
Newer Older
1
//
msobczak's avatar
msobczak committed
2
// Copyright (C) 2004-2008 Maciej Sobczak, Stephen Hutton
3 4 5 6 7 8 9
// 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)
//

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

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

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

26
// Postgres-specific tests
27

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

// 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.
44
void test1()
45 46
{
    {
47
        session sql(backEnd, connectString);
48

49
        oid_table_creator tableCreator(sql);
50

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

53
        rowid rid(sql);
54
        sql << "select oid from soci_test where id = 7", into(rid);
55 56 57 58

        int id;
        std::string name;

59
#ifndef SOCI_POSTGRESQL_NOPARAMS
60

61
        sql << "select id, name from soci_test where oid = :rid",
62 63 64 65 66
            into(id), into(name), use(rid);

#else
        // Older PostgreSQL does not support use elements.

67 68
        postgresql_rowid_backend *rbe
            = static_cast<postgresql_rowid_backend *>(rid.get_backend());
69 70 71

        unsigned long oid = rbe->value_;

72
        sql << "select id, name from soci_test where oid = " << oid,
73 74
            into(id), into(name);

75
#endif // SOCI_POSTGRESQL_NOPARAMS
76 77 78 79 80

        assert(id == 7);
        assert(name == "John");
    }

81
    std::cout << "test 1 passed" << std::endl;
82 83
}

84
// function call test
85
class function_creator : function_creator_base
86
{
87 88
public:

msobczak's avatar
msobczak committed
89 90
    function_creator(session & sql)
    : function_creator_base(sql)
91
    {
92 93
        // 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
94
        try { sql << "create language plpgsql"; }
95
        catch (soci_error const &) {} // ignore if error
96

97
#ifndef SOCI_POSTGRESQL_NOPARAMS
98

msobczak's avatar
msobczak committed
99
        sql  <<
100
            "create or replace function soci_test(msg varchar) "
101 102 103 104
            "returns varchar as $$ "
            "begin "
            "  return msg; "
            "end $$ language plpgsql";
105 106
#else

msobczak's avatar
msobczak committed
107
       sql <<
108 109 110 111 112
            "create or replace function soci_test(varchar) "
            "returns varchar as \' "
            "begin "
            "  return $1; "
            "end \' language plpgsql";
msobczak's avatar
msobczak committed
113
#endif
114 115 116 117
    }

protected:

118
    std::string drop_statement()
119 120
    {
        return "drop function soci_test(varchar)";
121 122 123 124 125 126
    }
};

void test2()
{
    {
127
        session sql(backEnd, connectString);
128

129
        function_creator functionCreator(sql);
130 131 132 133

        std::string in("my message");
        std::string out;

134
#ifndef SOCI_POSTGRESQL_NOPARAMS
135

136
        statement st = (sql.prepare <<
137
            "select soci_test(:input)",
138 139 140 141 142 143
            into(out),
            use(in, "input"));

#else
        // Older PostgreSQL does not support use elements.

144
        statement st = (sql.prepare <<
145
            "select soci_test(\'" << in << "\')",
146 147
            into(out));

148
#endif // SOCI_POSTGRESQL_NOPARAMS
149

msobczak's avatar
msobczak committed
150
        st.execute(true);
151 152 153 154 155 156 157
        assert(out == in);

        // explicit procedure syntax
        {
            std::string in("my message2");
            std::string out;

158
#ifndef SOCI_POSTGRESQL_NOPARAMS
159

160
            procedure proc = (sql.prepare <<
161
                "soci_test(:input)",
162 163 164 165 166
                into(out), use(in, "input"));

#else
        // Older PostgreSQL does not support use elements.

167
            procedure proc = (sql.prepare <<
168
                "soci_test(\'" << in << "\')", into(out));
169

170
#endif // SOCI_POSTGRESQL_NOPARAMS
171

msobczak's avatar
msobczak committed
172
            proc.execute(true);
173 174 175 176
            assert(out == in);
        }
    }

177
    std::cout << "test 2 passed" << std::endl;
178 179
}

180
// BLOB test
181
struct blob_table_creator : public table_creator_base
182
{
msobczak's avatar
msobczak committed
183 184
    blob_table_creator(session & sql)
    : table_creator_base(sql)
185
    {
msobczak's avatar
msobczak committed
186
        sql <<
187 188 189 190
             "create table soci_test ("
             "    id integer,"
             "    img oid"
             ")";
191
    }
192
};
193

194
void test3()
195 196
{
    {
197
        session sql(backEnd, connectString);
198

199
        blob_table_creator tableCreator(sql);
200

201 202
        char buf[] = "abcdefghijklmnopqrstuvwxyz";

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

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

        {
209
            blob b(sql);
210

211
            sql << "select img from soci_test where id = 7", into(b);
212
            assert(b.get_len() == 0);
213 214

            b.write(0, buf, sizeof(buf));
215
            assert(b.get_len() == sizeof(buf));
216 217

            b.append(buf, sizeof(buf));
218
            assert(b.get_len() == 2 * sizeof(buf));
219 220
        }
        {
221
            blob b(sql);
222
            sql << "select img from soci_test where id = 7", into(b);
223
            assert(b.get_len() == 2 * sizeof(buf));
224 225
            char buf2[100];
            b.read(0, buf2, 10);
Maciej Sobczak's avatar
Maciej Sobczak committed
226
            assert(std::strncmp(buf2, "abcdefghij", 10) == 0);
227 228 229
        }

        unsigned long oid;
230
        sql << "select img from soci_test where id = 7", into(oid);
231 232 233
        sql << "select lo_unlink(" << oid << ")";
    }

234
    std::cout << "test 3 passed" << std::endl;
235 236
}

msobczak's avatar
msobczak committed
237 238 239 240 241 242 243 244 245
struct longlong_table_creator : table_creator_base
{
    longlong_table_creator(session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val int8)";
    }
};

246
// long long test
msobczak's avatar
msobczak committed
247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264
void test4()
{
    {
        session sql(backEnd, connectString);

        longlong_table_creator tableCreator(sql);

        long long v1 = 1000000000000LL;
        assert(v1 / 1000000 == 1000000);

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

        long long v2 = 0LL;
        sql << "select val from soci_test", into(v2);

        assert(v2 == v1);
    }

265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
    // vector<long long>
    {
        session sql(backEnd, connectString);

        longlong_table_creator tableCreator(sql);

        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);

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

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

        assert(v2.size() == 5);
        assert(v2[0] == 1000000000004LL);
        assert(v2[1] == 1000000000003LL);
        assert(v2[2] == 1000000000002LL);
        assert(v2[3] == 1000000000001LL);
        assert(v2[4] == 1000000000000LL);
    }

msobczak's avatar
msobczak committed
291 292 293
    std::cout << "test 4 passed" << std::endl;
}

294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313
// unsigned long long test
void test4ul()
{
    {
        session sql(backEnd, connectString);

        longlong_table_creator tableCreator(sql);

        unsigned long long v1 = 1000000000000ULL;
        assert(v1 / 1000000 == 1000000);

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

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

        assert(v2 == v1);
    }
}

314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346
struct boolean_table_creator : table_creator_base
{
    boolean_table_creator(session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val boolean)";
    }
};

void test5()
{
    {
        session sql(backEnd, connectString);

        boolean_table_creator tableCreator(sql);

        int i1 = 0;

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

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

        assert(i2 == i1);

        sql << "update soci_test set val = true";
        sql << "select val from soci_test", into(i2);
        assert(i2 == 1);
    }

    std::cout << "test 5 passed" << std::endl;
}

347 348 349 350 351
// dynamic backend test
void test6()
{
    try
    {
msobczak's avatar
msobczak committed
352 353
        session sql("nosuchbackend://" + connectString);
        assert(false);
354 355 356
    }
    catch (soci_error const & e)
    {
msobczak's avatar
msobczak committed
357
        assert(e.what() == std::string("Failed to open: libsoci_nosuchbackend.so"));
358 359 360
    }

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

msobczak's avatar
msobczak committed
363 364 365
        std::vector<std::string> backends = dynamic_backends::list_all();
        assert(backends.size() == 1);
        assert(backends[0] == "pgsql");
366

msobczak's avatar
msobczak committed
367 368 369
        {
            session sql("pgsql://" + connectString);
        }
370

msobczak's avatar
msobczak committed
371
        dynamic_backends::unload("pgsql");
372

msobczak's avatar
msobczak committed
373 374
        backends = dynamic_backends::list_all();
        assert(backends.empty());
375 376 377
    }

    {
msobczak's avatar
msobczak committed
378
        session sql("postgresql://" + connectString);
379
    }
msobczak's avatar
msobczak committed
380

msobczak's avatar
msobczak committed
381
    std::cout << "test 6 passed" << std::endl;
382 383
}

384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
void test7()
{
    {
        session sql(backEnd, connectString);

        int i;
        sql << "select 123", into(i);
        assert(i == 123);

        try
        {
            sql << "select 'ABC'", into (i);
            assert(false);
        }
        catch (soci_error const & e)
        {
            assert(e.what() == std::string("Cannot convert data."));
        }
    }

    std::cout << "test 7 passed" << std::endl;
}

msobczak's avatar
msobczak committed
407 408 409 410 411 412 413 414 415 416 417
void test8()
{
    {
        session sql(backEnd, connectString);

        assert(sql.get_backend_name() == "postgresql");
    }

    std::cout << "test 8 passed" << std::endl;
}

418 419 420 421 422 423 424 425 426 427 428 429 430 431 432
// test for double-colon cast in SQL expressions
void test9()
{
    {
        session sql(backEnd, connectString);

        int a = 123;
        int b = 0;
        sql << "select :a::integer", use(a), into(b);
        assert(b == a);
    }

    std::cout << "test 9 passed" << std::endl;
}

433 434 435 436 437 438
// test for date, time and timestamp parsing
void test10()
{
    {
        session sql(backEnd, connectString);

439
        std::string someDate = "2009-06-17 22:51:03.123";
440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472
        std::tm t1, t2, t3;

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

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

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

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

    std::cout << "test 10 passed" << std::endl;
}

473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511
// test for number of affected rows

struct table_creator_for_test11 : table_creator_base
{
    table_creator_for_test11(session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val integer)";
    }
};

void test11()
{
    {
        session sql(backEnd, connectString);

        table_creator_for_test11 tableCreator(sql);

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

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

        assert(st1.get_affected_rows() == 10);

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

        assert(st2.get_affected_rows() == 5);
    }

    std::cout << "test 11 passed" << std::endl;
}

512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546
// test INSERT INTO ... RETURNING syntax

struct table_creator_for_test12 : table_creator_base
{
    table_creator_for_test12(session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(sid serial, txt text)";
    }
};

void test12()
{
    {
        session sql(backEnd, connectString);

        table_creator_for_test12 tableCreator(sql);

        std::vector<long> ids(10);
        for (int 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;
        }
        
        std::vector<long> ids2(ids.size());
        sql << "select sid from soci_test order by sid", into(ids2);
        assert(std::equal(ids.begin(), ids.end(), ids2.begin()));
    }

    std::cout << "test 12 passed" << std::endl;
}

547
// DDL Creation objects for common tests
548
struct table_creator_one : public table_creator_base
549
{
msobczak's avatar
msobczak committed
550 551
    table_creator_one(session & sql)
        : table_creator_base(sql)
552
    {
msobczak's avatar
msobczak committed
553
        sql << "create table soci_test(id integer, val integer, c char, "
554
                 "str varchar(20), sh int2, ul numeric(20), d float8, "
msobczak's avatar
msobczak committed
555
                 "tm timestamp, i1 integer, i2 integer, i3 integer, "
556
                 "name varchar(20))";
557
    }
558
};
559

560
struct table_creator_two : public table_creator_base
561
{
msobczak's avatar
msobczak committed
562 563
    table_creator_two(session & sql)
        : table_creator_base(sql)
564
    {
msobczak's avatar
msobczak committed
565
        sql  << "create table soci_test(num_float float8, num_int integer,"
566
                     " name varchar(20), sometime timestamp, chr char)";
567
    }
568
};
569

570
struct table_creator_three : public table_creator_base
571
{
msobczak's avatar
msobczak committed
572 573
    table_creator_three(session & sql)
        : table_creator_base(sql)
574
    {
msobczak's avatar
msobczak committed
575
        sql << "create table soci_test(name varchar(100) not null, "
576 577 578
            "phone varchar(15))";
    }
};
579

580
//
581
// Support for soci Common Tests
582 583
//

584
class test_context : public test_context_base
585
{
586
public:
587 588 589
    test_context(backend_factory const &backEnd, std::string const &connectString)
        : test_context_base(backEnd, connectString)
    {}
590

591
    table_creator_base* table_creator_1(session& s) const
592
    {
593
        return new table_creator_one(s);
594 595
    }

596
    table_creator_base* table_creator_2(session& s) const
597
    {
598
        return new table_creator_two(s);
599 600
    }

601
    table_creator_base* table_creator_3(session& s) const
602
    {
603
        return new table_creator_three(s);
604 605
    }

606
    std::string to_date_time(std::string const &datdt_string) const
607
    {
608
        return "timestamptz(\'" + datdt_string + "\')";
609 610
    }
};
611

msobczak's avatar
msobczak committed
612

613 614
int main(int argc, char** argv)
{
615 616 617 618 619 620 621 622 623 624

#ifdef _MSC_VER
    // Redirect errors, unrecoverable problems, and assert() failures to STDERR,
    // instead of debug message window.
    // This hack is required to run asser()-driven tests by Buildbot.
    // 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

625 626 627 628 629 630 631 632 633 634
    if (argc == 2)
    {
        connectString = argv[1];
    }
    else
    {
        std::cout << "usage: " << argv[0]
            << " connectstring\n"
            << "example: " << argv[0]
            << " \'connect_string_for_PostgreSQL\'\n";
635
        return EXIT_FAILURE;
636 637 638 639
    }

    try
    {
640 641
        test_context tc(backEnd, connectString);
        common_tests tests(tc);
642 643 644
        tests.run();

        std::cout << "\nSOCI Postgres Tests:\n\n";
645 646 647
        test1();
        test2();
        test3();
msobczak's avatar
msobczak committed
648
        test4();
649
        test4ul();
650
        test5();
651

msobczak's avatar
msobczak committed
652
//         test6();
653 654 655
        std::cout << "test 6 skipped (dynamic backend)\n";

        test7();
msobczak's avatar
msobczak committed
656
        test8();
657
        test9();
658
        test10();
659
        test11();
660
        test12();
661 662

        std::cout << "\nOK, all tests passed.\n\n";
663
        return EXIT_SUCCESS;
664 665 666 667 668
    }
    catch (std::exception const & e)
    {
        std::cout << e.what() << '\n';
    }
669
    return EXIT_FAILURE;
670
}