1 /* Test the database interface.
2 * Copyright (C) 2002 Richard W.M. Jones <rich@annexia.org>
4 * This library is free software; you can redistribute it and/or
5 * modify it under the terms of the GNU Library General Public
6 * License as published by the Free Software Foundation; either
7 * version 2 of the License, or (at your option) any later version.
9 * This library is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 * Library General Public License for more details.
14 * You should have received a copy of the GNU Library General Public
15 * License along with this library; if not, write to the Free
16 * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 * $Id: test_dbi.c,v 1.6 2002/12/09 10:43:27 rich Exp $
33 #include "pthr_pseudothread.h"
36 static pool test_pool;
37 static pseudothread test_pth;
45 char *alias, *username;
46 struct dbi_timestamp ts;
47 struct dbi_interval inv;
49 /* Open a connection to the database. */
50 dbh = new_db_handle (test_pool, "", DBI_THROW_ERRORS);
52 pth_die ("failed to connect to the database, check PGHOST, etc.");
54 /* Create some tables and some data. */
55 sth = st_prepare_cached
57 "create temporary table tdbi_users "
59 " username text not null, "
60 " age int2 not null, "
63 " unique (username))");
66 sth = st_prepare_cached
68 "create temporary table tdbi_aliases "
69 " (userid int4 references tdbi_users (userid), "
70 " alias text not null)");
73 sth = st_prepare_cached
75 "insert into tdbi_users (userid, username, age) values (?, ?, ?)",
76 DBI_INT, DBI_STRING, DBI_INT);
77 st_execute (sth, 1, "rich", 30);
78 st_execute (sth, 2, "anna", 45);
79 st_execute (sth, 3, "bob", 55);
80 st_execute (sth, 4, "dan", 24);
82 sth = st_prepare_cached
84 "insert into tdbi_aliases (userid, alias) values (?, ?)",
86 st_execute (sth, 1, "richard");
87 st_execute (sth, 1, "richie");
88 st_execute (sth, 1, "richy");
89 st_execute (sth, 2, "ann");
90 st_execute (sth, 2, "annie");
91 st_execute (sth, 3, "robert");
92 st_execute (sth, 3, "bobbie");
93 st_execute (sth, 3, "bobby");
95 /* Select out some results. */
96 sth = st_prepare_cached
98 "select u.userid, u.username, a.alias "
99 "from tdbi_users u, tdbi_aliases a "
100 "where u.userid = a.userid "
104 st_bind (sth, 0, userid, DBI_INT);
105 st_bind (sth, 1, username, DBI_STRING);
106 st_bind (sth, 2, alias, DBI_STRING);
109 while (st_fetch (sth))
114 assert (userid == 2 &&
115 strcmp (username, "anna") == 0 &&
116 strcmp (alias, "ann") == 0);
119 assert (userid == 2 &&
120 strcmp (username, "anna") == 0 &&
121 strcmp (alias, "annie") == 0);
124 assert (userid == 3 &&
125 strcmp (username, "bob") == 0 &&
126 strcmp (alias, "bobbie") == 0);
129 assert (userid == 3 &&
130 strcmp (username, "bob") == 0 &&
131 strcmp (alias, "bobby") == 0);
134 assert (userid == 1 &&
135 strcmp (username, "rich") == 0 &&
136 strcmp (alias, "richard") == 0);
139 assert (userid == 1 &&
140 strcmp (username, "rich") == 0 &&
141 strcmp (alias, "richie") == 0);
144 assert (userid == 1 &&
145 strcmp (username, "rich") == 0 &&
146 strcmp (alias, "richy") == 0);
149 assert (userid == 3 &&
150 strcmp (username, "bob") == 0 &&
151 strcmp (alias, "robert") == 0);
160 sth = st_prepare_cached
162 "select username from tdbi_users where age > 40 order by 1");
165 st_bind (sth, 0, username, DBI_STRING);
168 while (st_fetch (sth))
173 assert (strcmp (username, "anna") == 0);
176 assert (strcmp (username, "bob") == 0);
185 /* Select out one row, no rows. */
186 sth = st_prepare_cached
188 "select userid from tdbi_users where username = ?", DBI_STRING);
189 st_execute (sth, "rich");
191 st_bind (sth, 0, userid, DBI_INT);
193 assert (st_fetch (sth) != 0);
194 assert (userid == 1);
195 assert (st_fetch (sth) == 0);
196 assert (userid == 1); /* Hasn't splatted userid. */
198 st_execute (sth, "fred");
200 assert (st_fetch (sth) == 0);
202 /* Check the st_finish function does nothing bad. */
205 /* Drop the tables. */
206 sth = st_prepare_cached
208 "drop table tdbi_aliases; drop table tdbi_users");
211 /* Test timestamps and intervals.
212 * XXX Retrieval only tested/supported at present.
214 sth = st_prepare_cached
216 "create temporary table tdbi_times "
217 " (ord int2 not null, ts timestamp, inv interval)");
220 sth = st_prepare_cached
222 "insert into tdbi_times (ord, ts, inv) values (?, ?, ?)",
223 DBI_INT, DBI_STRING, DBI_STRING);
224 st_execute (sth, 0, "2002/11/09 01:02", 0);
225 st_execute (sth, 1, "2002/10/07 03:04:05", "1 year 1 day");
226 st_execute (sth, 2, "2002/09/04 06:07:08.999", "01:00");
227 st_execute (sth, 3, 0, "30 mins");
228 st_execute (sth, 4, 0, "1 year 2 months 6 days 8 hours 9 mins");
230 sth = st_prepare_cached
231 (dbh, "select ord, ts, inv from tdbi_times order by 1");
234 st_bind (sth, 1, ts, DBI_TIMESTAMP);
235 st_bind (sth, 2, inv, DBI_INTERVAL);
237 assert (st_fetch (sth));
238 assert (!ts.is_null);
239 assert (ts.year == 2002 && ts.month == 11 && ts.day == 9 &&
240 ts.hour == 1 && ts.min == 2 && ts.sec == 0 &&
242 assert (inv.is_null);
244 assert (st_fetch (sth));
245 assert (!ts.is_null);
246 assert (ts.year == 2002 && ts.month == 10 && ts.day == 7 &&
247 ts.hour == 3 && ts.min == 4 && ts.sec == 5 &&
249 assert (!inv.is_null);
250 assert (inv.years == 1 && inv.months == 0 &&
251 inv.days == 1 && inv.hours == 0 && inv.mins == 0 &&
254 assert (st_fetch (sth));
255 assert (!ts.is_null);
256 assert (ts.year == 2002 && ts.month == 9 && ts.day == 4 &&
257 ts.hour == 6 && ts.min == 7 && ts.sec == 8 &&
258 ts.microsecs == 999);
259 assert (!inv.is_null);
260 assert (inv.years == 0 && inv.months == 0 &&
261 inv.days == 0 && inv.hours == 1 && inv.mins == 0 &&
264 assert (st_fetch (sth));
266 assert (!inv.is_null);
267 assert (inv.years == 0 && inv.months == 0 &&
268 inv.days == 0 && inv.hours == 0 && inv.mins == 30 &&
271 assert (st_fetch (sth));
273 assert (!inv.is_null);
274 assert (inv.years == 1 && inv.months == 2 &&
275 inv.days == 6 && inv.hours == 8 && inv.mins == 9 &&
278 /* Drop the table. */
279 sth = st_prepare_cached
281 "drop table tdbi_times");
284 /* Try rolling back the database. */
291 char *env = getenv ("TEST_DBI");
293 /* Skip the test unless the 'TEST_DBI' environment variable is set. */
294 if (!env || strcmp (env, "1") != 0)
297 "WARNING: DBI test skipped. If you want to run the DBI test, then you must\n"
299 " (a) A working PostgreSQL >= 7.1 database.\n"
300 " (b) postgresql-devel packages installed (ie. libpq, header files).\n"
301 " (c) PGHOST, etc., set up to provide access to a database where I can\n"
302 " create temporary tables.\n"
303 "Set the TEST_DBI environment variable to 1 and run this test again.\n");
308 test_pool = new_pool ();
309 test_pth = new_pseudothread (test_pool, do_test, 0, "testing thread");
310 pth_start (test_pth);
312 while (pseudothread_count_threads () > 0)