1 |
(* Generic database interface for mod_caml programs. |
2 |
* Copyright (C) 2003 Merjis Ltd. |
3 |
* |
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. |
8 |
* |
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. |
13 |
* |
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. |
17 |
* |
18 |
* $Id: dbi.mli,v 1.12 2003/11/23 14:24:57 rwmj Exp $ |
19 |
*) |
20 |
|
21 |
(** Generic database interface. |
22 |
* |
23 |
* Making a connection to a specific type of database: |
24 |
* |
25 |
* {v |
26 |
* module DB = Dbi_postgres |
27 |
* let dbh = new DB.connection "database_name" |
28 |
* v} |
29 |
* |
30 |
* Equivalent to above, except that we make a connection to a named |
31 |
* type of database: |
32 |
* |
33 |
* {v |
34 |
* let dbh = |
35 |
* try |
36 |
* Dbi.Factory.connect "postgres" "database_name" |
37 |
* with |
38 |
* Not_found -> failwith "Postgres driver not available." |
39 |
* v} |
40 |
* |
41 |
* From Apache, using persistent connections (see {!Apache.DbiPool}): |
42 |
* |
43 |
* {v |
44 |
* let dbh = Apache.DbiPool.get r "postgres" "database_name" |
45 |
* v} |
46 |
* |
47 |
* Simple usage, returning one row: |
48 |
* |
49 |
* {v |
50 |
* let sth = dbh#prepare "select name from employees where empid = ?" in |
51 |
* sth#execute [ `Int 100 ]; |
52 |
* let row = sth#fetch1 in |
53 |
* let name = row.(0) in |
54 |
* v} |
55 |
* |
56 |
* Simple usage, returning multiple rows: |
57 |
* |
58 |
* {v |
59 |
* let sth = dbh#prepare "select name from employees where salary > ?" in |
60 |
* sth#execute [ `Int 10000 ]; |
61 |
* sth#iter (fun row -> Printf.printf "name = %s\n" row.(0)) |
62 |
* v} |
63 |
* |
64 |
* Advanced usage, binding columns for maximum efficiency: |
65 |
* |
66 |
* {v |
67 |
* let sth = dbh#prepare "select name from employees where salary > ?" in |
68 |
* sth#execute [ `Int 10000 ]; |
69 |
* let name = ref "" in |
70 |
* sth#bind_columns [ `StringRef name ]; |
71 |
* while sth#next do |
72 |
* Printf.printf "name = %s\n" !name |
73 |
* done |
74 |
* v} |
75 |
* |
76 |
* Advanced usage, reusing prepared statements: |
77 |
* |
78 |
* {v |
79 |
* let sth = |
80 |
* dbh#prepare "insert into employees (name, salary) values (?, ?)" in |
81 |
* List.iter ( |
82 |
* fun (name, salary) -> |
83 |
* sth#execute [ `String name; `Int salary ]; |
84 |
* let id = sth#serial "" in |
85 |
* Printf.printf "Employee %s has been assigned ID %d\n" name id |
86 |
* ) employee_list; |
87 |
* v} |
88 |
*) |
89 |
|
90 |
type arg_t = [ `Null |
91 |
| `Int of int |
92 |
| `IntOption of int option |
93 |
| `Numeric of float |
94 |
| `NumericOption of float option |
95 |
| `String of string |
96 |
| `StringOption of string option |
97 |
| `Timestamp of timestamp_t |
98 |
| `TimestampOption of timestamp_t option |
99 |
| `Interval of interval_t |
100 |
| `IntervalOption of interval_t option |
101 |
| `Bool of bool |
102 |
| `BoolOption of bool option |
103 |
] |
104 |
(** Type of arguments to the [statement#execute] method. The [*Option] |
105 |
* arguments can be [Some] thing or [None] meaning SQL [NULL]. |
106 |
*) |
107 |
and timestamp_t = |
108 |
{ |
109 |
ts_is_null : bool; (** Null? Other fields will be 0. *) |
110 |
ts_year : int; |
111 |
ts_month : int; |
112 |
ts_day : int; |
113 |
ts_hour : int; |
114 |
ts_min : int; |
115 |
ts_sec : int; |
116 |
ts_microsecs : int; |
117 |
ts_utc_offset : int; |
118 |
} |
119 |
(** Timestamp type. *) |
120 |
and interval_t = |
121 |
{ |
122 |
iv_is_null : bool; (** Null? Other fields will be 0. *) |
123 |
iv_years : int; |
124 |
iv_months : int; |
125 |
iv_days : int; |
126 |
iv_hours : int; |
127 |
iv_mins : int; |
128 |
iv_secs : int; |
129 |
iv_microsecs : int; |
130 |
} |
131 |
(** Interval type. *) |
132 |
|
133 |
val null_timestamp : timestamp_t |
134 |
(** A timestamp with the [ts_is_null] field set to true. *) |
135 |
val null_interval : interval_t |
136 |
(** An interval with the [iv_is_null] field set to true. *) |
137 |
|
138 |
type ref_t = [ `IntRef of int ref |
139 |
| `NumericRef of float ref |
140 |
| `StringRef of string ref |
141 |
(* XXX etc. *) |
142 |
| `IntOptionRef of int option ref |
143 |
| `NumericOptionRef of float option ref |
144 |
| `StringOptionRef of string option ref |
145 |
(* XXX etc. *) |
146 |
] |
147 |
(** Type of arguments to the [statement#bind_columns] method. *) |
148 |
|
149 |
type precommit_handle |
150 |
(** See {!Dbi.connection.register_precommit}. *) |
151 |
type postrollback_handle |
152 |
(** See {!Dbi.connection.register_postrollback}. *) |
153 |
|
154 |
exception SQL_error of string |
155 |
(** Exceptions thrown by subclasses on SQL errors. *) |
156 |
|
157 |
class virtual statement : |
158 |
connection -> |
159 |
object |
160 |
|
161 |
method virtual execute : arg_t list -> unit |
162 |
(** Execute the statement with the given list of arguments substituted |
163 |
* for [?] placeholders in the query string. |
164 |
* |
165 |
* This command can throw a variety of SQL-specific exceptions. |
166 |
*) |
167 |
|
168 |
method virtual fetch1 : string array |
169 |
(** The statement expects exactly one tuple to be returned from the query. |
170 |
* This returns the tuple, or throws [Not_found] if no tuple is |
171 |
* returned by the database. |
172 |
*) |
173 |
|
174 |
method fetchall : string array list |
175 |
(** This returns a list of all tuples returned from the query. Note that |
176 |
* this is less efficient than reading them one at a time, or using |
177 |
* [bind_columns] and [next]. |
178 |
*) |
179 |
|
180 |
method iter : (string array -> unit) -> unit |
181 |
(** Iterate over the result tuples. *) |
182 |
|
183 |
method map : 'a . (string array -> 'a) -> 'a list |
184 |
(** Map over the result tuples. *) |
185 |
|
186 |
method fold_left : 'a . ('a -> string array -> 'a) -> 'a -> 'a |
187 |
(** Fold left over the result tuples. *) |
188 |
|
189 |
method fold_right : 'a . (string array -> 'a -> 'a) -> 'a -> 'a |
190 |
(** Fold right over the result tuples. NB: not tail recursive. *) |
191 |
|
192 |
method virtual bind_columns : ref_t list -> unit |
193 |
(** This method binds result columns to variables (references). |
194 |
* Subsequent calls to [next] will set those references for each |
195 |
* tuple returned. Setting references is much more efficient than |
196 |
* using the [fetch] methods, particularly when returning large |
197 |
* objects. |
198 |
*) |
199 |
|
200 |
method virtual next : bool |
201 |
(** Returns the [next] row. You would normally want to call |
202 |
* [bind_columns] first so that you can actually retrieve the |
203 |
* value of each column. [next] returns [true] if a rows was |
204 |
* returned or [false] otherwise. |
205 |
*) |
206 |
|
207 |
method virtual serial : string -> int |
208 |
(** If the statement is an INSERT and has been executed, then some |
209 |
* databases support retrieving the serial number of the INSERT |
210 |
* statement (assuming there is a SERIAL column or SEQUENCE attached |
211 |
* to the table). The string parameter is the sequence name, which |
212 |
* is only required by some database types. See the specific documentation |
213 |
* for your database for more information. |
214 |
*) |
215 |
|
216 |
method finish : unit |
217 |
(** "Finishes" the statement. This basically just frees up any memory |
218 |
* associated with the statement (this memory would be freed up by the |
219 |
* GC later anyway). After calling [finish] you may call [execute] to |
220 |
* begin executing another query. |
221 |
*) |
222 |
|
223 |
method connection : connection |
224 |
(** Return the database handle associated with this statement handle. *) |
225 |
end |
226 |
|
227 |
and virtual connection : |
228 |
?host:string -> |
229 |
?port:string -> |
230 |
?user:string -> |
231 |
?password:string -> |
232 |
string -> |
233 |
object |
234 |
|
235 |
val mutable closed : bool |
236 |
|
237 |
method id : int |
238 |
(** Returns a unique integer which can be used to identify this |
239 |
* connection. |
240 |
*) |
241 |
|
242 |
method closed : bool |
243 |
(** Returns [true] if this database handle has been closed. Subsequent |
244 |
* operations on the handle will fail. |
245 |
*) |
246 |
|
247 |
method host : string option |
248 |
(** Return the [host] parameter. *) |
249 |
method port : string option |
250 |
(** Return the [port] parameter. *) |
251 |
method user : string option |
252 |
(** Return the [user] parameter. *) |
253 |
method password : string option |
254 |
(** Return the [password] parameter. *) |
255 |
method database : string |
256 |
(** Return the database name. *) |
257 |
|
258 |
method virtual database_type : string |
259 |
(** Database type (eg. "postgres"). *) |
260 |
|
261 |
method virtual prepare : string -> statement |
262 |
(** Prepare a database query, and return the prepared statement. |
263 |
* The statement may contain [?] placeholders which can be substituted |
264 |
* for values when the statement is executed. |
265 |
*) |
266 |
|
267 |
method prepare_cached : string -> statement |
268 |
(** This method is identical to [prepare] except that, if possible, it |
269 |
* caches the statement handle with the database object. Future calls |
270 |
* with the same query string return the previously prepared statement. |
271 |
* For databases which support prepared statement handles, this avoids |
272 |
* a round-trip to the database, and an expensive recompilation of the |
273 |
* statement. |
274 |
*) |
275 |
|
276 |
method ex : string -> arg_t list -> statement |
277 |
(** This is a shorthand for: |
278 |
* |
279 |
* {v |
280 |
* let sth = dbh#prepare_cached stmt in |
281 |
* sth#execute [args ...]; |
282 |
* sth |
283 |
* v} |
284 |
*) |
285 |
|
286 |
method commit : unit |
287 |
(** Perform a COMMIT operation on the database. *) |
288 |
|
289 |
method rollback : unit |
290 |
(** Perform a ROLLBACK operation on the database. *) |
291 |
|
292 |
method register_precommit : (unit -> unit) -> precommit_handle |
293 |
(** Register a function which will be called just BEFORE a commit |
294 |
* happens on this handle. This method returns a handle which can |
295 |
* be used to deregister the callback later. This is useful for |
296 |
* implementing various types of persistence. |
297 |
*) |
298 |
|
299 |
method unregister_precommit : precommit_handle -> unit |
300 |
(** Unregister a precommit callback. *) |
301 |
|
302 |
method register_postrollback : (unit -> unit) -> postrollback_handle |
303 |
(** Register a function which will be called just AFTER a rollback |
304 |
* happens on this handle. This method returns a handle which can |
305 |
* be used to deregister the callback later. This is useful for |
306 |
* implementing various types of persistence. |
307 |
*) |
308 |
|
309 |
method unregister_postrollback : postrollback_handle -> unit |
310 |
(** Unregister a postrollback callback. *) |
311 |
|
312 |
method close : unit |
313 |
(** Closes the database handle. All statement handles are also invalidated. |
314 |
* Database handles which are collected by the GC are automatically |
315 |
* closed, but you should explicitly close handles to save resources, |
316 |
* where possible. |
317 |
*) |
318 |
|
319 |
method ping : bool |
320 |
(** This uses some active method to verify that the database handle is |
321 |
* still working. By this I mean that it tries to execute some sort of |
322 |
* 'null' statement against the database to see if it gets a response. |
323 |
* If the database is up, it returns [true]. If the database is down or |
324 |
* unresponsive, it returns [false]. This method should never throw |
325 |
* an exception (unless, perhaps, there is some sort of catastrophic |
326 |
* internal error in the [Dbi] library or the driver). |
327 |
*) |
328 |
|
329 |
method set_debug : bool -> unit |
330 |
(** Use this to enable debugging on the handle. In this mode significant |
331 |
* events (such as executing queries) are printed out on stderr. |
332 |
*) |
333 |
|
334 |
method debug : bool |
335 |
(** Returns true if this handle has debugging enabled. *) |
336 |
end |
337 |
|
338 |
module Factory : |
339 |
sig |
340 |
val connect : string -> |
341 |
?host:string -> ?port:string -> ?user:string -> ?password:string -> |
342 |
string -> connection |
343 |
(** Connect to a specific type of database. The first string parameter |
344 |
* is the database type, eg. "postgres", "mysql", etc. |
345 |
* |
346 |
* Throws [Not_found] if the database type is not known. May throw |
347 |
* other connection-specific SQL errors. |
348 |
*) |
349 |
|
350 |
val database_types : unit -> string list |
351 |
(** Returns a list of registered database types. *) |
352 |
|
353 |
val register : string -> (?host:string -> ?port:string -> |
354 |
?user:string -> ?password:string -> |
355 |
string -> connection) -> unit |
356 |
(** Specific database drivers register themselves on load (or [Dynlink]) |
357 |
* by calling this function. |
358 |
*) |
359 |
end |