1 #include <cassert>
2 #include <iostream>
3 #include <string>
4 #include <map>
5
6 #include "zdbpp.h"
7 using namespace zdb;
8
9 const std::map<std::string, std::string> data {
10 {"Fry", "Ceci n'est pas une pipe"},
11 {"Leela", "Mona Lisa"},
12 {"Bender", "Bryllup i Hardanger"},
13 {"Farnsworth", "The Scream"},
14 {"Zoidberg", "Vampyre"},
15 {"Amy", "Balcony"},
16 {"Hermes", "Cycle"},
17 {"Nibbler", "Day & Night"},
18 {"Cubert", "Hand with Reflecting Sphere"},
19 {"Zapp", "Drawing Hands"},
20 {"Joey Mousepad", "Ascending and Descending"}
21 };
22
23 const std::map<std::string, std::string> schema {
24 { "mysql", "CREATE TABLE zild_t(id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), percent REAL, image BLOB);"},
25 { "postgresql", "CREATE TABLE zild_t(id SERIAL PRIMARY KEY, name VARCHAR(255), percent REAL, image BYTEA);"},
26 { "sqlite", "CREATE TABLE zild_t(id INTEGER PRIMARY KEY, name VARCHAR(255), percent REAL, image BLOB);"},
27 { "oracle", "CREATE TABLE zild_t(id NUMBER GENERATED AS IDENTITY, name VARCHAR(255), percent REAL, image CLOB);"}
28 };
29
testCreateSchema(ConnectionPool & pool)30 static void testCreateSchema(ConnectionPool& pool) {
31 Connection con = pool.getConnection();
32 try { con.execute("drop table zild_t;"); } catch (...) {}
33 con.execute(schema.at(pool.getURL().protocol()).c_str());
34 }
35
testPrepared(ConnectionPool & pool)36 static void testPrepared(ConnectionPool& pool) {
37 double percent = 0.12;
38 Connection con = pool.getConnection();
39 PreparedStatement p1 = con.prepareStatement("insert into zild_t (name, percent, image) values(?, ?, ?);");
40 con.beginTransaction();
41 for (const auto &[name, image] : data) {
42 percent += 1 + percent;
43 p1.bind(1, name);
44 p1.bind(2, percent);
45 p1.bind(3, std::tuple{image.c_str(), int(image.length() + 1)}); // include terminating \0
46 p1.execute();
47 }
48 // Implicit prepared statement. Any execute or executeQuery statement which
49 // takes parameters are automatically translated into a prepared statement.
50 // Here we also demonstrate how to set a SQL null value by using nullptr which
51 // must be used instead of NULL
52 con.execute("update zild_t set image = ? where id = ?", nullptr, 11);
53 con.commit();
54 }
55
testQuery(ConnectionPool & pool)56 static void testQuery(ConnectionPool& pool) {
57 Connection con = pool.getConnection();
58 // Implicit prepared statement because of parameters
59 ResultSet result = con.executeQuery("select id, name, percent, image from zild_t where id < ? order by id;", 100);
60 result.setFetchSize(10); // Optionally set prefetched rows. Default is 100
61 assert(result.columnCount() == 4);
62 assert(std::string(result.columnName(1)) == "id");
63 while (result.next()) {
64 int id = result.getInt(1);
65 const char *name = result.getString("name");
66 double percent = result.getDouble("percent");
67 auto [image, size] = result.getBlob("image");
68 printf("\t%-5d%-20s%-10.2f%-16.38s\n", id, name ? name : "null", percent, size ? (char *)image : "null");
69 // Assert that SQL null above was set
70 if (id == 11) {
71 assert(result.isnull(4));
72 }
73 }
74 }
75
testException(ConnectionPool & pool)76 static void testException(ConnectionPool& pool) {
77 try {
78 Connection con = pool.getConnection();
79 PreparedStatement p = con.prepareStatement("blablablabla ?;", "Bla");
80 p.execute();
81 std::cout << "Test failed, did not get exception\n";
82 exit(1);
83 } catch (sql_exception& e) {}
84
85 try {
86 Connection con = pool.getConnection();
87 ResultSet r = con.executeQuery("blablabala ? ;", "bla!");
88 r.next();
89 std::cout << "Test failed, did not get exception\n";
90 exit(1);
91 } catch (sql_exception& e) {}
92 }
93
testDropSchema(ConnectionPool & pool)94 static void testDropSchema(ConnectionPool& pool) {
95 pool.getConnection().execute("drop table zild_t;");
96 }
97
main(void)98 int main(void) {
99 auto help =
100 "Please enter a valid database connection URL and press ENTER\n"
101 "E.g. sqlite:///tmp/sqlite.db?synchronous=off&heap_limit=2000\n"
102 "E.g. mysql://localhost:3306/test?user=root&password=root\n"
103 "E.g. postgresql://localhost:5432/test?user=root&password=root\n"
104 "E.g. oracle://scott:tiger@localhost:1521/servicename\n"
105 "To exit, enter '.' on a single line\n\nConnection URL> ";
106 std::cout << "\033[0;35m\nC++17 zdbpp.h API Test:\033[0m\n\n" << help;
107 for (std::string line; std::getline(std::cin, line);) {
108 if (line == "q" || line == ".")
109 break;
110 URL url(line);
111 if (!url) {
112 std::cout << "Please enter a valid database URL or stop by entering '.'\n\n";
113 std::cout << "Connection URL> ";
114 continue;
115 }
116 ConnectionPool pool(line);
117 pool.start();
118 std::cout << std::string(8, '=') + "> Start Tests\n";
119 testCreateSchema(pool);
120 testPrepared(pool);
121 testQuery(pool);
122 testException(pool);
123 testDropSchema(pool);
124 std::cout << std::string(8, '=') + "> Tests: OK\n\n";
125 std::cout << help;
126 }
127 }
128