1 /* Database interface library.
2 * - by 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: pthr_dbi.h,v 1.9 2003/02/22 14:23:06 rich Exp $
25 typedef struct db_handle *db_handle;
28 typedef struct st_handle *st_handle;
33 /* Function: new_db_handle - database interface library
35 * Function: db_rollback
36 * Function: db_in_transaction
37 * Function: get_db_handle
38 * Function: put_db_handle
39 * Function: new_st_handle
40 * Function: st_prepare
41 * Function: st_prepare_cached
42 * Function: st_execute
47 * Function: st_fetch_all_rows
49 * Function: db_set_debug
50 * Function: db_get_debug
52 * @code{pthr_dbi} is a library for interfacing pthrlib programs
53 * with the PostgreSQL database (see @code{http://www.postgresql.org/}).
55 * @code{new_db_handle} creates a new database handle, and connects
56 * to the database. The connection structure is allocated in
57 * @code{pool}. The connection is automatically closed and the memory
58 * freed when the pool is deleted. The @code{conninfo} string is
59 * the connection string passed to @code{libpq}. This string is
60 * fully documented in the PostgreSQL Programmer's Guide, Section I
61 * (Client Interfaces), libpq, 1.2 Database Connection Functions.
62 * Commonly the string will contain:
64 * @code{"host=HOSTNAME dbname=DBNAME"}
68 * @code{"host=HOSTNAME dbname=DBNAME user=USER password=PASSWORD"}
70 * The @code{flags} parameter contains zero or more of the following flags:
72 * @code{DBI_THROW_ERRORS}: If set causes database errors to
73 * call @code{pth_die} (this is the recommended behaviour).
75 * Normally this function returns a database handle. If the database
76 * connection fails, this function returns @code{NULL}.
78 * @code{db_commit} commits the current database transaction and
81 * @code{db_rollback} rolls back the current database transaction and
84 * If a database connection is closed without issuing either a commit
85 * or rollback (eg. the pool is deleted or the program exits), then
86 * the database will rollback the transaction. Some of this functionality
87 * relies on the database to do the right thing.
89 * @code{db_in_transaction} returns a flag indicating whether the
90 * handle is in a transaction. By this we mean that some commands have
91 * been executed on the handle, but neither @code{db_commit} nor
92 * @code{db_rollback} have been called.
94 * @code{get_db_handle} and @code{put_db_handle} are used to implement
97 * The @code{get_db_handle} function "borrows" a database handle from
98 * a process-wide pool of compatible handles. The thread has exclusive
99 * use of this handle until it either calls @code{put_db_handle} to
100 * "give it back" to the pool, or until the thread exits, at which
101 * point the handle is automatically returned to the pool. In either
102 * case, if the thread wishes to commit changes it has made, it must
103 * call @code{db_commit} on the handle before it is returned to the
104 * pool. If a handle is returned to the pool in an uncommitted state,
105 * then the connection is rolled back.
107 * Calling @code{get_db_handle} multiple times returns multiple
110 * Connection pooling is far more efficient than opening and closing
111 * connections using @code{new_db_handle}. However certain things are
112 * not possible with connection pooling: eg. creating a temporary
113 * table which persists across several threads, or executing a single
114 * transaction across multiple HTTP requests. In these (rare) cases,
115 * the program should do its own connection management.
117 * Note that there are separate pools for each @code{conninfo} string.
119 * @code{new_st_handle}, and the synonyms @code{st_prepare} and
120 * @code{st_prepare_cached} create a new statement and return the
121 * statement handle. The @code{query} parameter is the SQL query.
122 * '?' and '@' characters in the query may be used as placeholders
123 * (when they appear outside strings) for scalar and vector values
124 * respectively. The final parameter(s) are a list of the types
125 * of these placeholders, and must correspond exactly to the types
126 * passed in the @code{st_execute} call.
128 * If the @code{st_prepare_cached} form of statement creation is
129 * used, then the statement is cached in the database handle. At
130 * the moment, this doesn't make a lot of difference to performance,
131 * but when a future version of PostgreSQL properly supports prepared
132 * statements, this will make a big difference in performance by
133 * allowing query plans to be cached on the server. In practice it
134 * is almost always best to use @code{st_prepare_cached}. The only
135 * possible exception is when using statements which refer to
138 * @code{st_execute} executes the query with the given parameter
139 * list. The parameters are substituted for the '?' and '@' placeholders
140 * in the query, in order. The tyes of the parameters must correspond
141 * exactly to the types passed in the prepare call.
143 * @code{st_execute} may be called multiple times on the same
144 * statement handle. You do not need to (and should not, if possible)
145 * prepare the statement each time.
147 * @code{st_execute} returns the number of rows affected, for
148 * @code{INSERT} and @code{UPDATE} statements.
150 * If the command was an @CODE{INSERT} statement, then you can use
151 * @code{st_serial} as a convenience function to return the serial
152 * number assigned to the new row. The argument passed is the
153 * sequence name (usually @code{tablename_columnname_seq}).
155 * @code{st_bind} binds a local variable to a column in the
156 * result. The arguments are the column number (starting at 0),
157 * the local variable name, and the type of the variable.
159 * Unlike in Perl DBI, you may call @code{st_bind} at any point
160 * after preparing the statement, and bindings are persistent
163 * Possible types for the prepare, @code{st_execute} and
164 * @code{st_bind} calls: @code{DBI_INT}, @code{DBI_INT_OR_NULL},
165 * @code{DBI_STRING}, @code{DBI_BOOL}, @code{DBI_CHAR},
166 * @code{DBI_TIMESTAMP}, @code{DBI_INTERVAL}, @code{DBI_VECTOR_INT},
167 * @code{DBI_VECTOR_INT_OR_NULL}, @code{DBI_VECTOR_STRING},
168 * @code{DBI_VECTOR_BOOL}, @code{DBI_VECTOR_CHAR},
169 * @code{DBI_VECTOR_TIMESTAMP}, @code{DBI_VECTOR_INTERVAL}.
171 * @code{DBI_INT_OR_NULL} differs from an ordinary @code{DBI_INT}
172 * in that the integer value of @code{0} is treated as a @code{null}
173 * (useful when passed as a parameter to @code{st_execute}, not very
176 * The @code{DBI_TIMESTAMP}, @code{DBI_INTERVAL}, @code{DBI_VECTOR_TIMESTAMP}
177 * and @code{DBI_VECTOR_INTERVAL} types refer respectively to the
178 * PostgreSQL database types @code{timestamp} and @code{interval}
179 * and the relevant structures @code{struct dbi_timestamp} and
180 * @code{struct dbi_interval} defined in @code{<pthr_dbi.h>}.
182 * @code{st_fetch} fetches the next result row from the query. It
183 * returns true if the result row was fetched, or false if there
184 * are no more rows. @code{st_fetch} returns the actual results
185 * in the variables bound to each column by @code{st_bind}. Any
186 * unbound columns are ignored.
188 * @code{st_fetch_all_rows} fetches all of the result rows
189 * in one go, returning a @code{vector} of @code{vector} of @code{char *}.
191 * @code{st_finish} is an optional step which you may use once you
192 * have finished with a statement handle. It frees up the memory
193 * used by the results held in the statement handle. (This memory
194 * would otherwise not be freed up until another @code{st_execute}
195 * or the pool containing the statement handle is deleted).
197 * The @code{db_(set|get)_debug} functions are used to update the
198 * state of the debug flag on a database handle. When this handle
199 * is set to true, then database statements which are executed are
200 * also printed out to @code{stderr}. The default is no debugging.
202 * It is not likely that we will support other databases in future
203 * unless something dramatic happens to PostgreSQL. Install and learn
204 * PostgreSQL and I promise that your life will be happier.
206 extern db_handle new_db_handle (pool, const char *conninfo, int flags);
207 extern void db_commit (db_handle);
208 extern void db_rollback (db_handle);
209 extern int db_in_transaction (db_handle);
210 extern db_handle get_db_handle (const char *conninfo, int flags);
211 extern void put_db_handle (db_handle dbh);
212 extern st_handle new_st_handle (db_handle, const char *query, int flags, ...);
213 #define st_prepare(db,query,types...) new_st_handle ((db), (query), 0 , ## types)
214 #define st_prepare_cached(db,query,types...) new_st_handle ((db), (query), DBI_ST_CACHE , ## types)
215 extern int st_execute (st_handle, ...);
216 extern int st_serial (st_handle, const char *seq_name);
217 extern void _st_bind (st_handle, int colidx, void *varptr, int type);
218 #define st_bind(sth,colidx,var,type) _st_bind ((sth), (colidx), &(var), (type))
219 extern int st_fetch (st_handle);
220 extern vector st_fetch_all_rows (st_handle);
221 extern void st_finish (st_handle);
222 extern void db_set_debug (db_handle, int);
223 extern int db_get_debug (db_handle);
225 /* Flags for new_db_handle. */
226 #define DBI_THROW_ERRORS 0x0001
227 #define DBI_DEBUG 0x0002
229 /* Flags for new_st_handle. */
230 #define DBI_ST_CACHE 0x0001
232 /* Database types. */
233 /* NB. 0 must not be a valid type! */
234 #define DBI_MIN_TYPE 1001
236 #define DBI_STRING 1002
237 #define DBI_BOOL 1003
238 #define DBI_CHAR 1004
239 #define DBI_TIMESTAMP 1005
240 #define DBI_INTERVAL 1006
241 #define DBI_INT_OR_NULL 1007
242 #define DBI_MAX_TYPE 1007
243 #define DBI_VECTOR_INT DBI_INT
244 #define DBI_VECTOR_STRING DBI_STRING
245 #define DBI_VECTOR_BOOL DBI_BOOL
246 #define DBI_VECTOR_CHAR DBI_CHAR
247 #define DBI_VECTOR_TIMESTAMP DBI_TIMESTAMP
248 #define DBI_VECTOR_INTERVAL DBI_INTERVAL
249 #define DBI_VECTOR_INT_OR_NULL DBI_INT_OR_NULL
251 /* For the timestamp and interval types, these structures are used. */
254 int is_null; /* NULL if true (other fields will be zero). */
255 int year, month, day;
263 int is_null; /* NULL if true (other fields will be zero). */
264 int secs, mins, hours;
265 int days, months, years;
268 #endif /* PTHR_DBI_H */