1 #include "Config.h"
2
3 #include <stdio.h>
4 #include <string.h>
5 #include <assert.h>
6 #include <unistd.h>
7 #include <stdlib.h>
8
9 #include "URL.h"
10 #include "Thread.h"
11 #include "Vector.h"
12 #include "ResultSet.h"
13 #include "PreparedStatement.h"
14 #include "Connection.h"
15 #include "ConnectionPool.h"
16 #include "AssertException.h"
17 #include "SQLException.h"
18
19
20 /**
21 * libzdb connection pool unity tests.
22 */
23 #define BSIZE 2048
24
25 #define SCHEMA_MYSQL "CREATE TABLE zild_t(id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), percent REAL, image BLOB);"
26 #define SCHEMA_POSTGRESQL "CREATE TABLE zild_t(id SERIAL PRIMARY KEY, name VARCHAR(255), percent REAL, image BYTEA);"
27 #define SCHEMA_SQLITE "CREATE TABLE zild_t(id INTEGER PRIMARY KEY, name VARCHAR(255), percent REAL, image BLOB);"
28 #define SCHEMA_ORACLE "CREATE TABLE zild_t(id NUMBER GENERATED AS IDENTITY, name VARCHAR(255), percent REAL, image CLOB);"
29
30 #if HAVE_STRUCT_TM_TM_GMTOFF
31 #define TM_GMTOFF tm_gmtoff
32 #else
33 #define TM_GMTOFF tm_wday
34 #endif
35
36
TabortHandler(const char * error)37 static void TabortHandler(const char *error) {
38 fprintf(stdout, "Error: %s\n", error);
39 exit(1);
40 }
41
testPool(const char * testURL)42 static void testPool(const char *testURL) {
43 URL_T url;
44 char *schema;
45 ConnectionPool_T pool;
46 char *data[]= {"Fry", "Leela", "Bender", "Farnsworth",
47 "Zoidberg", "Amy", "Hermes", "Nibbler", "Cubert",
48 "Zapp", "Joey Mousepad", "ЯΣ༆", 0};
49
50 if (Str_startsWith(testURL, "mysql")) {
51 schema = SCHEMA_MYSQL;
52 } else if (Str_startsWith(testURL, "postgresql")) {
53 schema = SCHEMA_POSTGRESQL;
54 } else if (Str_startsWith(testURL, "sqlite")) {
55 schema = SCHEMA_SQLITE;
56 } else if (Str_startsWith(testURL, "oracle")) {
57 schema = SCHEMA_ORACLE;
58 }
59 else {
60 printf("Unsupported database protocol\n");
61 exit(1);
62 }
63
64
65 printf("=> Test1: create/destroy\n");
66 {
67 pool = ConnectionPool_new(URL_new(testURL));
68 assert(pool);
69 url = ConnectionPool_getURL(pool);
70 ConnectionPool_free(&pool);
71 assert(! pool);
72 URL_free(&url);
73 }
74 printf("=> Test1: OK\n\n");
75
76 printf("=> Test2: NULL value\n");
77 {
78 url = URL_new(NULL);
79 assert(! url);
80 TRY
81 {
82 pool = ConnectionPool_new(url);
83 printf("\tResult: Test failed -- exception not thrown\n");
84 exit(1);
85 }
86 CATCH(AssertException)
87 {
88 // OK
89 }
90 END_TRY;
91 }
92 printf("=> Test2: OK\n\n");
93
94 printf("=> Test3: start/stop\n");
95 {
96 url = URL_new(testURL);
97 pool = ConnectionPool_new(url);
98 assert(pool);
99 ConnectionPool_start(pool);
100 ConnectionPool_stop(pool);
101 ConnectionPool_free(&pool);
102 assert(pool==NULL);
103 URL_free(&url);
104 // Test that exception is thrown on start error
105 TRY
106 {
107 url = URL_new("not://a/database");
108 pool = ConnectionPool_new(url);
109 assert(pool);
110 ConnectionPool_start(pool);
111 printf("\tResult: Test failed -- exception not thrown\n");
112 exit(1);
113 }
114 CATCH(SQLException) {
115 // OK
116 }
117 FINALLY {
118 ConnectionPool_free(&pool);
119 assert(pool==NULL);
120 URL_free(&url);
121 }
122 END_TRY;
123 }
124 printf("=> Test3: OK\n\n");
125
126 printf("=> Test4: Connection execute & transaction\n");
127 {
128 int i;
129 Connection_T con;
130 url = URL_new(testURL);
131 pool = ConnectionPool_new(url);
132 assert(pool);
133 ConnectionPool_setAbortHandler(pool, TabortHandler);
134 ConnectionPool_start(pool);
135 con = ConnectionPool_getConnection(pool);
136 assert(con);
137 TRY Connection_execute(con, "drop table zild_t;"); ELSE END_TRY;
138 Connection_execute(con, "%s", schema);
139 Connection_beginTransaction(con);
140 /* Insert values into database and assume that auto increment of id works */
141 for (i = 0; data[i]; i++)
142 Connection_execute(con, "insert into zild_t (name, percent) values('%s', %d.%d);", data[i], i+1, i);
143 // Assert that the last insert statement added one row
144 assert(Connection_rowsChanged(con) == 1);
145 /* Assert that last row id works for MySQL and SQLite. Neither Oracle nor PostgreSQL
146 support last row id directly. The way to do this in PostgreSQL is to use
147 currval() or return the id on insert. */
148 if (IS(URL_getProtocol(url), "sqlite") || IS(URL_getProtocol(url), "mysql"))
149 assert(Connection_lastRowId(con) == 12);
150 Connection_commit(con);
151 printf("\tResult: table zild_t successfully created\n");
152 Connection_close(con);
153 }
154 printf("=> Test4: OK\n\n");
155
156
157 printf("=> Test5: Prepared Statement\n");
158 {
159 int i;
160 char blob[8192];
161 char *images[]= {"Ceci n'est pas une pipe", "Mona Lisa",
162 "Bryllup i Hardanger", "The Scream",
163 "Vampyre", "Balcony", "Cycle", "Day & Night",
164 "Hand with Reflecting Sphere",
165 "Drawing Hands", "Ascending and Descending", 0};
166 Connection_T con = ConnectionPool_getConnection(pool);
167 assert(con);
168 // 1. Prepared statement, perform a nonsense update to test rowsChanged
169 PreparedStatement_T p1 = Connection_prepareStatement(con, "update zild_t set image=?;");
170 PreparedStatement_setString(p1, 1, "");
171 PreparedStatement_execute(p1);
172 printf("\tRows changed: %lld\n", PreparedStatement_rowsChanged(p1));
173 // Assert that all 12 rows in the data set was changed
174 assert(PreparedStatement_rowsChanged(p1) == 12);
175 // 2. Prepared statement, update the table proper with "images".
176 PreparedStatement_T pre = Connection_prepareStatement(con, "update zild_t set image=? where id=?;");
177 assert(pre);
178 assert(PreparedStatement_getParameterCount(pre) == 2);
179 for (i = 0; images[i]; i++) {
180 PreparedStatement_setBlob(pre, 1, images[i], (int)strlen(images[i])+1);
181 PreparedStatement_setInt(pre, 2, i + 1);
182 PreparedStatement_execute(pre);
183 }
184 /* Add a database null blob value for id = 5 */
185 PreparedStatement_setBlob(pre, 1, NULL, 0);
186 PreparedStatement_setInt(pre, 2, 5);
187 PreparedStatement_execute(pre);
188 /* Add a database null string value for id = 1 */
189 PreparedStatement_setString(pre, 1, NULL);
190 PreparedStatement_setInt(pre, 2, 1);
191 PreparedStatement_execute(pre);
192 /* Add a large blob */
193 memset(blob, 'x', 8192);
194 blob[8191] = 0;
195 /* Mark start and end */
196 *blob='S'; blob[8190] = 'E';
197 PreparedStatement_setBlob(pre, 1, blob, 8192);
198 PreparedStatement_setInt(pre, 2, i + 1);
199 PreparedStatement_execute(pre);
200 printf("\tResult: prepared statement successfully executed\n");
201 Connection_close(con);
202 }
203 printf("=> Test5: OK\n\n");
204
205
206 printf("=> Test6: Result Sets\n");
207 {
208 int i;
209 int imagesize = 0;
210 Connection_T con = ConnectionPool_getConnection(pool);
211 assert(con);
212 Connection_setQueryTimeout(con, 3000);
213 assert(Connection_getQueryTimeout(con) == 3000);
214 ResultSet_T rset = Connection_executeQuery(con, "select id, name, percent, image from zild_t where id < %d order by id;", 100);
215 assert(rset);
216 printf("\tResult:\n");
217 printf("\tNumber of columns in resultset: %d\n\t", ResultSet_getColumnCount(rset));
218 assert(4==ResultSet_getColumnCount(rset));
219 i = 1;
220 printf("%-5s", ResultSet_getColumnName(rset, i++));
221 printf("%-16s", ResultSet_getColumnName(rset, i++));
222 printf("%-10s", ResultSet_getColumnName(rset, i++));
223 printf("%-16s", ResultSet_getColumnName(rset, i++));
224 printf("\n\t------------------------------------------------------\n");
225 while (ResultSet_next(rset)) {
226 int id = ResultSet_getIntByName(rset, "id");
227 const char *name = ResultSet_getString(rset, 2);
228 double percent = ResultSet_getDoubleByName(rset, "percent");
229 const char *blob = (char*)ResultSet_getBlob(rset, 4, &imagesize);
230 printf("\t%-5d%-16s%-10.2f%-16.38s\n", id, name ? name : "null", percent, imagesize ? blob : "");
231 }
232 // Column count
233 rset = Connection_executeQuery(con, "select image from zild_t where id=12;");
234 assert(1 == ResultSet_getColumnCount(rset));
235
236 // Assert that types are interchangeable and that all data is returned
237 while (ResultSet_next(rset)) {
238 const char *image = ResultSet_getStringByName(rset, "image");
239 const void *blob = ResultSet_getBlobByName(rset, "image", &imagesize);
240 // Oracle does not support getting blob as string
241 if (! Str_startsWith(testURL, "oracle")) {
242 assert(image && blob);
243 assert(strlen(image) + 1 == 8192);
244 }
245 assert(imagesize == 8192);
246 }
247
248 printf("\tResult: check isnull..");
249 rset = Connection_executeQuery(con, "select id, image from zild_t where id in(1,5,2);");
250 while (ResultSet_next(rset)) {
251 int id = ResultSet_getIntByName(rset, "id");
252 if (id == 1 || id == 5)
253 assert(ResultSet_isnull(rset, 2) == true);
254 else
255 assert(ResultSet_isnull(rset, 2) == false);
256 }
257 printf("success\n");
258
259 printf("\tResult: check max rows..");
260 Connection_setMaxRows(con, 3);
261 rset = Connection_executeQuery(con, "select id from zild_t;");
262 assert(rset);
263 i = 0;
264 while (ResultSet_next(rset)) i++;
265 assert((i)==3);
266 printf("success\n");
267
268 printf("\tResult: check prepared statement resultset..");
269 Connection_setMaxRows(con, 0);
270 PreparedStatement_T pre = Connection_prepareStatement(con, "select name from zild_t where id=?");
271 assert(pre);
272 PreparedStatement_setInt(pre, 1, 2);
273 ResultSet_T names = PreparedStatement_executeQuery(pre);
274 assert(names);
275 assert(ResultSet_next(names));
276 assert(Str_isEqual("Leela", ResultSet_getString(names, 1)));
277 printf("success\n");
278
279 printf("\tResult: check prepared statement re-execute..");
280 PreparedStatement_setInt(pre, 1, 1);
281 names = PreparedStatement_executeQuery(pre);
282 assert(names);
283 assert(ResultSet_next(names));
284 assert(Str_isEqual("Fry", ResultSet_getString(names, 1)));
285 printf("success\n");
286
287 printf("\tResult: check prepared statement without in-params..");
288 pre = Connection_prepareStatement(con, "select name from zild_t;");
289 assert(pre);
290 names = PreparedStatement_executeQuery(pre);
291 assert(names);
292 for (i = 0; ResultSet_next(names); i++);
293 assert(i==12);
294 printf("success\n");
295
296 // Test prefetch unless database is SQLite or Postgres for which prefetch is n/a
297 if (Str_startsWith(testURL, "mysql") || Str_startsWith(testURL, "oracle")) {
298 printf("\tResult: check fetch-size..");
299 assert(Connection_getFetchSize(con) == SQL_DEFAULT_PREFETCH_ROWS);
300 Connection_setFetchSize(con, 50);
301 assert(Connection_getFetchSize(con) == 50);
302 ResultSet_T fs = Connection_executeQuery(con, "select * from zild_t;");
303 assert(fs);
304 // Assert that result set inherits Connection fetch-size
305 assert(ResultSet_getFetchSize(fs) == 50);
306 ResultSet_setFetchSize(fs, 12);
307 while (ResultSet_next(fs));
308 assert(ResultSet_getFetchSize(fs) == 12);
309 printf("success\n");
310 }
311
312 /* Need to close and release statements before
313 we can drop the table, sqlite need this */
314 Connection_clear(con);
315 Connection_execute(con, "drop table zild_t;");
316 Connection_close(con);
317 ConnectionPool_stop(pool);
318 ConnectionPool_free(&pool);
319 assert(pool==NULL);
320 URL_free(&url);
321 }
322 printf("=> Test6: OK\n\n");
323
324
325 printf("=> Test7: reaper start/stop\n");
326 {
327 int i;
328 Vector_T v = Vector_new(20);
329 url = URL_new(testURL);
330 pool = ConnectionPool_new(url);
331 assert(pool);
332 ConnectionPool_setInitialConnections(pool, 4);
333 ConnectionPool_setMaxConnections(pool, 20);
334 ConnectionPool_setConnectionTimeout(pool, 4);
335 ConnectionPool_setReaper(pool, 4);
336 ConnectionPool_setAbortHandler(pool, TabortHandler);
337 ConnectionPool_start(pool);
338 assert(4==ConnectionPool_size(pool));
339 printf("Creating 20 Connections..");
340 for (i = 0; i<20; i++)
341 Vector_push(v, ConnectionPool_getConnection(pool));
342 assert(ConnectionPool_size(pool) == 20);
343 assert(ConnectionPool_active(pool) == 20);
344 printf("success\n");
345 printf("Closing Connections down to initial..");
346 while (! Vector_isEmpty(v))
347 Connection_close(Vector_pop(v));
348 assert(ConnectionPool_active(pool) == 0);
349 assert(ConnectionPool_size(pool) == 20);
350 printf("success\n");
351 printf("Please wait 10 sec for reaper to harvest closed connections..");
352 Connection_T con = ConnectionPool_getConnection(pool); // Activate one connection to verify the reaper does not close any active
353 fflush(stdout);
354 sleep(10);
355 assert(5 == ConnectionPool_size(pool)); // 4 initial connections + the one active we got above
356 assert(1 == ConnectionPool_active(pool));
357 printf("success\n");
358 Connection_close(con);
359 ConnectionPool_stop(pool);
360 ConnectionPool_free(&pool);
361 Vector_free(&v);
362 assert(pool==NULL);
363 URL_free(&url);
364 }
365 printf("=> Test7: OK\n\n");
366
367 printf("=> Test8: Exceptions handling\n");
368 {
369 int i;
370 Connection_T con;
371 ResultSet_T result;
372 url = URL_new(testURL);
373 pool = ConnectionPool_new(url);
374 assert(pool);
375 ConnectionPool_setAbortHandler(pool, TabortHandler);
376 ConnectionPool_start(pool);
377 con = ConnectionPool_getConnection(pool);
378 assert(con);
379 /*
380 * The following should work without throwing exceptions
381 */
382 TRY
383 {
384 Connection_execute(con, "%s", schema);
385 }
386 ELSE
387 {
388 printf("\tResult: Creating table zild_t failed -- %s\n", Exception_frame.message);
389 assert(false); // Should not fail
390 }
391 END_TRY;
392 TRY
393 {
394 Connection_beginTransaction(con);
395 for (i = 0; data[i]; i++)
396 Connection_execute(con, "insert into zild_t (name, percent) values('%s', %d.%d);", data[i], i+1, i);
397 Connection_commit(con);
398 printf("\tResult: table zild_t successfully created\n");
399 }
400 ELSE
401 {
402 printf("\tResult: Test failed -- %s\n", Exception_frame.message);
403 assert(false); // Should not fail
404 }
405 FINALLY
406 {
407 Connection_close(con);
408 }
409 END_TRY;
410 assert((con = ConnectionPool_getConnection(pool)));
411 TRY
412 {
413 int i, j;
414 const char *bg[]= {"Starbuck", "Sharon Valerii",
415 "Number Six", "Gaius Baltar", "William Adama",
416 "Lee \"Apollo\" Adama", "Laura Roslin", 0};
417 PreparedStatement_T p = Connection_prepareStatement
418 (con, "insert into zild_t (name) values(?);");
419 /* If we did not get a statement, an SQLException is thrown
420 and we will not get here. So we can safely use the
421 statement now. Likewise, below, we do not have to
422 check return values from the statement since any error
423 will throw an SQLException and transfer the control
424 to the exception handler
425 */
426 for (i = 0, j = 42; bg[i]; i++, j++) {
427 PreparedStatement_setString(p, 1, bg[i]);
428 PreparedStatement_execute(p);
429 }
430 }
431 CATCH(SQLException)
432 {
433 printf("\tResult: prepare statement failed -- %s\n", Exception_frame.message);
434 assert(false);
435 }
436 END_TRY;
437 TRY
438 {
439 printf("\t\tBattlestar Galactica: \n");
440 result = Connection_executeQuery(con, "select name from zild_t where id > 12;");
441 while (ResultSet_next(result))
442 printf("\t\t%s\n", ResultSet_getString(result, 1));
443 }
444 CATCH(SQLException)
445 {
446 printf("\tResult: resultset failed -- %s\n", Exception_frame.message);
447 assert(false);
448 }
449 FINALLY
450 {
451 Connection_close(con);
452 }
453 END_TRY;
454 /*
455 * The following should fail and throw exceptions. The exception error
456 * message can be obtained with Exception_frame.message, or from
457 * Connection_getLastError(con). Exception_frame.message contains both
458 * SQL errors or api errors such as prepared statement parameter index
459 * out of range, while Connection_getLastError(con) only has SQL errors
460 */
461 TRY
462 {
463 assert((con = ConnectionPool_getConnection(pool)));
464 Connection_execute(con, "%s", schema);
465 /* Creating the table again should fail and we
466 should not come here */
467 printf("\tResult: Test failed -- exception not thrown\n");
468 exit(1);
469 }
470 CATCH(SQLException)
471 {
472 Connection_close(con);
473 }
474 END_TRY;
475 TRY
476 {
477 assert((con = ConnectionPool_getConnection(pool)));
478 printf("\tTesting: Query with errors.. ");
479 Connection_executeQuery(con, "blablabala;");
480 printf("\tResult: Test failed -- exception not thrown\n");
481 exit(1);
482 }
483 CATCH(SQLException)
484 {
485 printf("ok\n");
486 Connection_close(con);
487 }
488 END_TRY;
489 TRY
490 {
491 printf("\tTesting: Prepared statement query with errors.. ");
492 assert((con = ConnectionPool_getConnection(pool)));
493 PreparedStatement_T p = Connection_prepareStatement(con, "blablabala;");
494 ResultSet_T r = PreparedStatement_executeQuery(p);
495 while(ResultSet_next(r));
496 printf("\tResult: Test failed -- exception not thrown\n");
497 exit(1);
498 }
499 CATCH(SQLException)
500 {
501 printf("ok\n");
502 Connection_close(con);
503 }
504 END_TRY;
505 TRY
506 {
507 assert((con = ConnectionPool_getConnection(pool)));
508 printf("\tTesting: Column index out of range.. ");
509 result = Connection_executeQuery(con, "select id, name from zild_t;");
510 while (ResultSet_next(result)) {
511 int id = ResultSet_getInt(result, 1);
512 const char *name = ResultSet_getString(result, 2);
513 /* So far so good, now, try access an invalid
514 column, which should throw an SQLException */
515 int bogus = ResultSet_getInt(result, 3);
516 printf("\tResult: Test failed -- exception not thrown\n");
517 printf("%d, %s, %d", id, name, bogus);
518 exit(1);
519 }
520 }
521 CATCH(SQLException)
522 {
523 printf("ok\n");
524 Connection_close(con);
525 }
526 END_TRY;
527 TRY
528 {
529 assert((con = ConnectionPool_getConnection(pool)));
530 printf("\tTesting: Invalid column name.. ");
531 result = Connection_executeQuery(con, "select name from zild_t;");
532 while (ResultSet_next(result)) {
533 const char *name = ResultSet_getStringByName(result, "nonexistingcolumnname");
534 printf("%s", name);
535 printf("\tResult: Test failed -- exception not thrown\n");
536 exit(1);
537 }
538 }
539 CATCH(SQLException)
540 {
541 printf("ok\n");
542 Connection_close(con);
543 }
544 END_TRY;
545 TRY
546 {
547 assert((con = ConnectionPool_getConnection(pool)));
548 PreparedStatement_T p = Connection_prepareStatement(con, "update zild_t set name = ? where id = ?;");
549 printf("\tTesting: Parameter index out of range.. ");
550 PreparedStatement_setInt(p, 3, 123);
551 printf("\tResult: Test failed -- exception not thrown\n");
552 exit(1);
553 }
554 CATCH(SQLException)
555 {
556 printf("ok\n");
557 }
558 FINALLY
559 {
560 Connection_close(con);
561 }
562 END_TRY;
563 assert((con = ConnectionPool_getConnection(pool)));
564 Connection_execute(con, "drop table zild_t;");
565 Connection_close(con);
566 ConnectionPool_stop(pool);
567 ConnectionPool_free(&pool);
568 assert(pool==NULL);
569 URL_free(&url);
570 }
571 printf("=> Test8: OK\n\n");
572
573 printf("=> Test9: Ensure Capacity\n");
574 {
575 /* Check that MySQL ensureCapacity works for columns that exceed the preallocated buffer and that no truncation is done */
576 if ( Str_startsWith(testURL, "mysql")) {
577 int myimagesize;
578 url = URL_new(testURL);
579 pool = ConnectionPool_new(url);
580 assert(pool);
581 ConnectionPool_start(pool);
582 Connection_T con = ConnectionPool_getConnection(pool);
583 assert(con);
584 Connection_execute(con, "CREATE TABLE zild_t(id INTEGER AUTO_INCREMENT PRIMARY KEY, image BLOB, string TEXT);");
585 PreparedStatement_T p = Connection_prepareStatement(con, "insert into zild_t (image, string) values(?, ?);");
586 char t[4096];
587 memset(t, 'x', 4096);
588 t[4095] = 0;
589 for (int i = 0; i < 4; i++) {
590 PreparedStatement_setBlob(p, 1, t, (i+1)*512); // store successive larger string-blobs to trigger realloc on ResultSet_getBlobByName
591 PreparedStatement_setString(p, 2, t);
592 PreparedStatement_execute(p);
593 }
594 ResultSet_T r = Connection_executeQuery(con, "select image, string from zild_t;");
595 for (int i = 0; ResultSet_next(r); i++) {
596 ResultSet_getBlobByName(r, "image", &myimagesize);
597 const char *image = ResultSet_getStringByName(r, "image"); // Blob as image should be terminated
598 const char *string = ResultSet_getStringByName(r, "string");
599 assert(myimagesize == (i+1)*512);
600 assert(strlen(image) == ((i+1)*512));
601 assert(strlen(string) == 4095);
602 }
603 p = Connection_prepareStatement(con, "select image, string from zild_t;");
604 r = PreparedStatement_executeQuery(p);
605 for (int i = 0; ResultSet_next(r); i++) {
606 ResultSet_getBlobByName(r, "image", &myimagesize);
607 const char *image = ResultSet_getStringByName(r, "image");
608 const char *string = (char*)ResultSet_getStringByName(r, "string");
609 assert(myimagesize == (i+1)*512);
610 assert(strlen(image) == ((i+1)*512));
611 assert(strlen(string) == 4095);
612 }
613 Connection_execute(con, "drop table zild_t;");
614 Connection_close(con);
615 ConnectionPool_stop(pool);
616 ConnectionPool_free(&pool);
617 URL_free(&url);
618 }
619 }
620 printf("=> Test9: OK\n\n");
621
622 printf("=> Test10: Date, Time, DateTime and Timestamp\n");
623 {
624 url = URL_new(testURL);
625 pool = ConnectionPool_new(url);
626 assert(pool);
627 ConnectionPool_start(pool);
628 Connection_T con = ConnectionPool_getConnection(pool);
629 if (Str_startsWith(testURL, "postgres"))
630 Connection_execute(con, "create table zild_t(d date, t time, dt timestamp, ts timestamp)");
631 else if (Str_startsWith(testURL, "oracle"))
632 Connection_execute(con, "create table zild_t(d date, t date, dt date, ts timestamp)");
633 else
634 Connection_execute(con, "create table zild_t(d date, t time, dt datetime, ts timestamp);");
635 PreparedStatement_T p = Connection_prepareStatement(con, "insert into zild_t values(?, ?, ?, ?);");
636 if (Str_startsWith(testURL, "oracle")) { // Oracle does not have a pure time data type
637 Connection_execute(con, "alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';");
638 Connection_execute(con, "alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS';");
639 PreparedStatement_setString(p, 1, "2013-12-28 00:00:00");
640 PreparedStatement_setString(p, 2, "2013-12-28 10:12:42");
641 } else {
642 PreparedStatement_setString(p, 1, "2013-12-28");
643 PreparedStatement_setString(p, 2, "10:12:42");
644 }
645 PreparedStatement_setString(p, 3, "2013-12-28 10:12:42");
646 PreparedStatement_setTimestamp(p, 4, 1387066378);
647 PreparedStatement_execute(p);
648 ResultSet_T r = Connection_executeQuery(con, "select * from zild_t");
649 if (ResultSet_next(r)) {
650 struct tm date = ResultSet_getDateTime(r, 1);
651 struct tm time = ResultSet_getDateTime(r, 2);
652 struct tm datetime = ResultSet_getDateTime(r, 3);
653 time_t timestamp = ResultSet_getTimestamp(r, 4);
654 struct tm timestampAsTm = ResultSet_getDateTime(r, 4);
655 // Check Date
656 assert(date.tm_hour == 0);
657 assert(date.tm_year == 2013);
658 assert(date.tm_mon == 11); // Remember month - 1
659 assert(date.tm_mday == 28);
660 assert(date.TM_GMTOFF == 0);
661 // Check Time
662 if (! Str_startsWith(testURL, "oracle"))
663 assert(time.tm_year == 0);
664 assert(time.tm_hour == 10);
665 assert(time.tm_min == 12);
666 assert(time.tm_sec == 42);
667 assert(time.TM_GMTOFF == 0);
668 // Check datetime
669 assert(datetime.tm_year == 2013);
670 assert(datetime.tm_mon == 11); // Remember month - 1
671 assert(datetime.tm_mday == 28);
672 assert(datetime.tm_hour == 10);
673 assert(datetime.tm_min == 12);
674 assert(datetime.tm_sec == 42);
675 assert(datetime.TM_GMTOFF == 0);
676 // Check timestamp
677 assert(timestamp == 1387066378);
678 // Check timestamp as datetime
679 assert(timestampAsTm.tm_year == 2013);
680 assert(timestampAsTm.tm_mon == 11); // Remember month - 1
681 assert(timestampAsTm.tm_mday == 15);
682 assert(timestampAsTm.tm_hour == 0);
683 assert(timestampAsTm.tm_min == 12);
684 assert(timestampAsTm.tm_sec == 58);
685 assert(timestampAsTm.TM_GMTOFF == 0);
686 // Result
687 printf("\tDate: %s, Time: %s, DateTime: %s\n\tTimestamp as numeric: %ld, Timestamp as string: %s\n",
688 ResultSet_getString(r, 1),
689 ResultSet_getString(r, 2),
690 ResultSet_getString(r, 3),
691 ResultSet_getTimestamp(r, 4),
692 ResultSet_getString(r, 4)); // SQLite will show both as numeric
693 }
694 Connection_execute(con, "drop table zild_t;");
695 Connection_close(con);
696 ConnectionPool_stop(pool);
697 ConnectionPool_free(&pool);
698 assert(pool==NULL);
699 URL_free(&url);
700 }
701 printf("=> Test10: OK\n\n");
702
703
704 printf("============> Connection Pool Tests: OK\n\n");
705 }
706
main(void)707 int main(void) {
708 URL_T url;
709 char buf[BSIZE];
710 char *help = "Please enter a valid database connection URL and press ENTER\n"
711 "E.g. sqlite:///tmp/sqlite.db?synchronous=off&heap_limit=2000\n"
712 "E.g. mysql://localhost:3306/test?user=root&password=root\n"
713 "E.g. postgresql://localhost:5432/test?user=root&password=root\n"
714 "E.g. oracle://scott:tiger@localhost:1521/servicename\n"
715 "To exit, enter '.' on a single line\n\nConnection URL> ";
716 ZBDEBUG = true;
717 Exception_init();
718 printf("============> Start Connection Pool Tests\n\n");
719 printf("This test will create and drop a table called zild_t in the database\n");
720 printf("%s", help);
721 while (fgets(buf, BSIZE, stdin)) {
722 if (*buf == '.' || *buf == 'q')
723 break;
724 if (*buf == '\r' || *buf == '\n' || *buf == 0)
725 goto next;
726 url = URL_new(buf);
727 if (! url) {
728 printf("Please enter a valid database URL or stop by entering '.'\n");
729 goto next;
730 }
731 testPool(URL_toString(url));
732 URL_free(&url);
733 printf("%s", help);
734 continue;
735 next:
736 printf("Connection URL> ");
737 }
738 return 0;
739 }
740