dbi - Database independent access layer ¶This module provides the unified interface to access various relational database systems (RDBMS). The operations specific to individual database systems are packaged in database driver (DBD) modules, which is usually loaded implicitly by DBI layer.
The module is strongly influenced by Perl’s DBI/DBD architecture. If you have used Perl DBI, it would be easy to use this module.
It’s better to look at the example. This is a simple outline
of accessing a database by dbi module:
(use dbi)
(use gauche.collection) ; to make 'map' work on the query result
(guard (e ((<dbi-error> e)
;; handle error
))
(let* ((conn (dbi-connect "dbi:mysql:test;host=dbhost"))
(query (dbi-prepare conn
"SELECT id, name FROM users WHERE department = ?"))
(result (dbi-execute query "R&D"))
(getter (relation-accessor result)))
(map (lambda (row)
(list (getter row "id")
(getter row "name")))
result)))
There’s nothing specific to the underlying database system
except the argument "dbi:mysql:test;host=dbhost"
passed to dbi-connect, from which dbi module
figures out that it is an access to mysql database,
loads dbd.mysql module, and let it handle the mysql-specific
stuff. If you want to use whatever database system, you can just
pass "dbi:whatever:parameter" to dbi-connect
instead, and everything stays the same as far as you have
dbd.whatever installed in your system.
A query to the database can be created by dbi-prepare.
You can issue the query by dbi-execute. This two-phase
approach allows you to create a prepared query, which is a kind of
parameterized SQL statement. In the above example the query
takes one parameter, denoted as '?' in the SQL.
The actual value is given in dbi-execute. When you
issue similar queries a lot, creating a prepared query and
execute it with different parameters may give you performance gain.
Also the parameter is automatically quoted.
When the query is a SELECT statement,
its result is returned as a collection that implements
the relation protocol. See gauche.collection - Collection framework
and util.relation - Relation framework for the details.
The outermost guard is to catch errors. The dbi related
errors are supposed to inherit <dbi-error> condition.
There are a few specific errors defined in dbi module.
A specific dbd layer may define more specific errors.
In the next section we describe user-level API, that is,
the procedures you need to concern when you’re using dbi.
The following section is for the driver API, which you need to use
to write a specific dbd driver to make it work with dbi
framework.
| • DBI user API: | ||
| • Writing drivers for DBI: |
There are several predefined conditions dbi API may throw.
See Exceptions for the details of conditions.
{dbi}
The base class of dbi-related conditions. Inherits <error>.
{dbi}
This condition is thrown by dbi-connect when it cannot
find the specified driver. Inherits <dbi-error>.
<dbi-nonexistent-driver-error>: driver-name ¶Holds the requested driver name as a string.
{dbi}
This condition is thrown when the called method isn’t supported
by the underlying driver. Inherits <dbi-error>.
{dbi}
This condition is thrown when the number of parameters given to
the prepared query doesn’t match the ones in the prepared statement.
Besides these errors, if a driver relies on dbi to
parse the prepared SQL statement, <sql-parse-error> may
be thrown if an invalid SQL statement is passed to dbi-prepare.
(see text.sql - SQL parsing and construction).
{dbi}
Connect to a database using a data source specified by dsn
(data source name). Dsn is a string with the following syntax:
dbi:driver:options
Driver part names a specific driver. You need to have the
corresponding driver module, dbd.driver, installed in
your system. For example, if dsn begins with "dbi:mysql:",
dbi-connect tries to load dbd.mysql.
Interpretation of the options part is up to the driver.
Usually it is in the form of key1=value1;key2=value2;...,
but some driver may interpret it differently. For example,
mysql driver allows you to specify a database name
at the beginning of options. You have to check out
the document of each driver for the exact specification of
options.
The keyword arguments gives extra information required for connection. The username and password are commonly supported arguments. The driver may recognize more keyword arguments.
If a connection to the database is successfully established,
a connection object (an instance of a subclass of <dbi-connection>)
is returned. Otherwise, an error is signaled.
{dbi}
The base class of a connection to a database system.
Each driver defines a subclass of this to keep information about
database-specific connections.
{dbi}
Queries whether a connection to the database is still open (active).
{dbi}
Closes a connection to the database. This causes releasing resources
related to this connection. Once closed, c cannot
be used for any dbi operations (except passing to dbi-open?).
Calling dbi-close on an already closed connection has no effect.
Although a driver usually closes a connection when <dbi-connection>
object is garbage-collected, it is not a good idea to rely on that,
since the timing of GC is unpredictable. The user program must make
sure that it calls dbi-close at a proper moment.
{dbi}
Returns a list of module names of known drivers.
{dbi}
The base class of a driver. You usually don’t need to see this
as far as you’re using the high-level dbi API.
{dbi}
This is a low-level function called from dbi-connect method,
and usually a user doesn’t need to call it.
Loads a driver module specified by driver-name, and instantiate the driver class and returns it.
{dbi}
From a string representation of SQL statement sql,
creates and returns a query object (an instance of <dbi-query>
or its subclass) for the database connection conn
Sql may contain parameter slots, denoted by ?.
(dbi-prepare conn "insert into tab (col1, col2) values (?, ?)") (dbi-prepare conn "select * from tab where col1 = ?")
They will be filled when you actually issue the query by
dbi-execute.
There are some advantages of using parameter
slots: (1) The necessary quoting is done automatically.
You don’t need to concern about security holes caused by
improper quoting, for example.
(2) Some drivers support a feature to send the template SQL
statement to the server at the preparation stage, and send
only the parameter values at the execution stage. It would be
more efficient if you issue similar queries lots of time.
If the backend doesn’t support prepared statements (SQL templates
having ? parameters), the driver may use text.sql
module to parse sql. It may raise <sql-parse-error>
condition if the given SQL is not well formed.
You may pass a true value to the keyword argument pass-through
to suppress interpretation of SQL and pass sql as-is to the
back end database system. It is useful if the back-end supports
extension of SQL which text.sql doesn’t understand.
If the driver lets prepared statement handled in back-end,
without using text.sql, the pass-through argument
may be ignored.
The driver may also take other keyword arguments. Check out
the documentation of individual drivers.
Note: Case folding of SQL statement is implementation dependent. Some DBMS may treat table names and column names in case insensitive way, while others do in case sensitive way. To write a portable SQL statement, make them quoted identifiers, that is, always surround names by double quotes.
{dbi}
Holds information about prepared query, created by dbi-prepare.
The following slots are defined.
<dbi-query>: connection ¶Contains the <dbi-connection> object.
<dbi-query>: prepared ¶If the driver prepares query by itself, this slot may contain a prepared statement. It is up to each driver how to use this slot, so the client shouldn’t rely on its value.
{dbi}
Returns #t iff the query can still be passed to
dbi-execute.
{dbi}
Destroy the query and free resources associated to the query.
After this operation, dbi-open? returns #f for q,
and the query can’t be used in any other way. Although the resource
may be freed when q is garbage-collected, it is strongly recommended
that the application closes queries explicitly.
{dbi}
Executes a query created by dbi-prepare. You should pass
the same number of parameters as the query expects.
If the issued query is select statement, dbi-execute
returns an object represents a relation.
A relation encapsulates the values in
rows and columns, as well as meta information like column names.
See "Retrieving query results" below for how to access the result.
If the query is other types, such as create, insert
or delete, the return value of the query closure is unspecified.
{dbi}
This is a convenience procedure when you create a query
and immediately execute it. It is equivalent to the following
expression, although the driver may overload this method to avoid
creating intermediate query object to avoid the overhead.
(dbi-execute (apply dbi-prepare conn sql options)
parameter-value ...)
{dbi}
Returns a string where special characters in str are escaped.
The official SQL standard only specify a single quote (') as
such character. However, it doesn’t specify non-printable characters,
and the database system may use other escaping characters. So
it is necessary to use this method rather than doing escaping
by your own.
;; assumes c is a valid DBI connection (dbi-escape-sql c "don't know") ⇒ "don''t know"
If the query is a select statement, it returns an object
of both <collection> and <relation>.
It is a collection of rows (that is, it implements <collection> API),
so you can use map, for-each or other generic functions
to access rows. You can also use the relation API to retrieve column
names and accessors from it. See util.relation - Relation framework, for the relation
API, and gauche.collection - Collection framework, for the collection API.
The actual class of the object returned from a query depends on the driver, but you may use the following method on it.
{dbi}
Check whether the result of a query is still active.
The result may become inactive when it is explicitly closed
by dbi-close and/or the connection to the database is
closed.
{dbi}
Close the result of the query. This may cause releasing resources
related to the result. You can no longer use result once it is
closed, except passing it to dbi-open?.
Although a driver usually releases resources when the result is
garbage-collected, the application shouldn’t rely on that and
is recommended call dbi-close explicitly when it is done
with the result.
Writing a driver for a specific database system means implementing
a module dbd.foo, where foo is the name of the driver.
The module have to implement several classes and methods, as explained below.
You have to define the following classes.
<dbi-driver>.
The class name must be <foo-driver>, where
foo is the name of the driver.
Usually this class produces a singleton instance,
and is only used to dispatch dbi-make-connection
method below.
<dbi-connection>. An instance of this class is created
by dbi-make-connection. It needs to keep the information about
the actual connections.
<relation> and <collection> to represent
query results suitable for the driver. (In most cases, the order of
the result of SELECT statement is significant, since it may be
sorted by ORDER BY clause. Thus it is more appropriate to
inherit <sequence>, rather than <collection>).
<dbi-query> to keep driver-specific
information of prepared queries.
The driver need to implement the following methods.
{dbi}
This method is called from dbi-connect, and responsible to
connect to the database and to create a connection object.
It must return a connection object, or raise an <dbi-error> if
it cannot establish a connection.
Options is the option part of the data source name (DSN) given to
dbi-connect. options-alist is an assoc list of
the result of parsing options. Both are provided so that
the driver may interpret options string in nontrivial way.
For example, given "dbi:foo:myaddressbook;host=dbhost;port=8998"
as DSN, foo’s dbi-make-connection will receive
"myaddressbook;host=dbhost;port=8998" as options,
and (("myaddressbook" . #t) ("host" . "dbhost") ("port" . "8998"))
as options-alist.
After options-alist, whatever keyword arguments given to
dbi-connect are passed. DBI protocol currently
specifies only username and password.
The driver may define other keyword arguments.
It is recommended to name the driver-specific keyword arguments
prefixed by the driver name, e.g. for dbd.foo, it may take
a :foo-whatever keyword argument.
It is up to the driver writer to define what options are available and the syntax of the options. The basic idea is that the DSN identifies the source of the data; it’s role is like URL in WWW. So, it may include the hostname and port number of the database, and/or the name of the database, etc. However, it shouldn’t include information related to authentication, such as username and password. That’s why those are passed via keyword arguments.
{dbi}
This method should create and return a prepared query object,
which is an instance of <dbi-query> or its subclass.
The query specified by sql is issued to the database system
when the prepared query object is passed to dbi-execute.
The method must set c to the connection slot of
the returned query object.
Sql is an SQL statement. It may contain placeholders represented
by '?'. The query closure should take the same number of arguments
as of the placeholders. It is up to the driver whether it parses
sql internally and construct a complete SQL statement when
the query closure is called, or it passes sql to the back-end
server to prepare the statement and let the query closure just send
parameters.
If the driver parses SQL statement internally, it should recognize
a keyword argument pass-through. If a true value is given,
the driver must treat sql opaque and pass it as is when
the query closure is called.
The driver may define other keyword arguments.
It is recommended to name the driver-specific keyword arguments
prefixed by the driver name, e.g. for dbd.foo, it may take
a :foo-whatever keyword argument.
{dbi}
This method is called from dbi-execute.
It must issue the query kept in q. If the query is parameterized,
the actual parameters given to dbi-execute are passed to
params argument.
If q is a select-type query, this method must return
an appropriate relation object.
{dbi}
If the default escape method isn’t enough, the driver may
overload this method to implement a specific escaping.
For example, MySQL treats backslash characters specially
as well as single quotes, so it has its dbi-escape-sql
method.
{dbi}
Queries open/close status of a connection and a result, and
closes a connection and a result. The close methods should cause
releasing resources used by connection/result. The driver
has to allow dbi-close to be called on a connection or a
result which has already been closed.
{dbi}
The default method uses dbi-prepare and dbi-execute
to implement the function. It just works,
but the driver may overload this method in order to skip
creating intermediate query object for efficiency.
The following functions are low-level utilities which you may use to implement the above methods.
{dbi}
Parse the data source name (DSN) string given to dbi-connect,
and returns tree values: (1) The driver name in a string. (2)
’options’ part of DSN as a string. (3) parsed options in an assoc
list. This may raise <dbi-error> if the given string doesn’t
conform DSN syntax.
You don’t need to use this to write a typical driver, for the
parsing is done before dbi-make-connection is called.
This method may be useful if you’re writing a kind of meta-driver,
such as a proxy.
{dbi}
Parses an SQL statement sql which may contain placeholders,
and returns a closure, which generates a complete SQL statement when
called with actual values for the parameters. If the back-end
doesn’t support prepared statements, you may use this function
to prepare queries in the driver.
Connection is a DBI connection to the database. It is required
to escape values within SQL properly (see dbi-escape-sql above).
;; assume c contains a valid dbi connection ((dbi-prepare-sql c "select * from table where id=?") "foo'bar") => "select * from table where id='foo''bar'"