Falcon Database Interface (DBI) is a common infrastructure that interfaces different SQL database engines with the Falcon Programming Language.
The DBI module suite presents a base interface module through which all the engines can be accessed by the script as a seamless Handle instance, where the nature of the underlying engine is abstracted and hidden away from the final program.
Other than presenting a common interface, each engine driver is wrapped in a separate module that can be loaded or imported directly by the final user, and that offers engine-specific functionalities as well as the common DBI interface.
More precisely, every database driver inherits from the base Handle class and may offer functionalities that cover more adequately the specific engine that they interface.
Note: At version 1.x, all the modules just cover the basic DBI requirements. Coverage of engine-specific functionalities is due for version 2.0.
The DBI module automatically finds and instantiates the proper subclass of the Handle object through the connect Function. A "database resource locator" (DRL), which is a string describing connection parameters, must be provided to the connect function; the first element of the DRL is a database name, separated from the rest of the DRL by a colon, which must mirror the name of a DBI enabled module (usually a binary module in a .dll/.so/.dylib file), where the DBI Handle class is derived. Other parameters of the connection string are provided in "
For example, to connect a local http:sqlite3.org/ SQLite3 database, the DRL is the following:
import from dbi hdb = dbi.connect( "sqlite3:db=/home/user/mydb.db" )
DRL strings can have driver-specific parameters, but the following parameters are known by all the drivers (even if not necessarily used):
- db: The database name or path (depending on the underlying driver model)
As a database abstaction layer, DBI tries to enforce a set of policies that stay common across different database engines, causing different engines with different defaults to behave coherently. If the database is not able to support a certain default behavior that DBI tries to apply to it, then the default is ignored. In case the DBI user tries to explicitly enforce a non-supported policy by setting the dbi_opts "connection options", then a DBIError is raised.
DBI options can be set at connection through a second parameter or during the rest of the work through the Handle.options method. Options can be set through a string whose content is in the same format of the DRI (
In general, connections are opened with any kind of autocommit mechanism turned off. An explicit Handle.commit must issued if the operations perfomred on the database are meant to be actually saved; closing the database or exiting from the virtual machine in an unclean way causes an implicit rollback of any pending change. Autocommit can be turned on through the "autocommit=on" option.
Queries are fully fetched client-side before the control is returned to the script. Not all the engines can change this behavior, but the "prefetch" option can be used to keep the resultset in the server (prefetch=none) or to transfer the resultset only if it is smaller than a given amount (prefetch=N). In this second case, the DBI module will try to transfer N records at a time, if possible.
As a database layer abstraction, DBI tries to perform operations differently handled under different engines in a way that can be considered valid across the widest range of databases possible.
DBI distinguishes between SQL queries (operations meant to return recordsets, as, for example, "select"), SQL statements not meant to return any recordset and stored procedure calls, which may or may not return a recordset. In the DBI framework, prepared statements cannot be seen as queries; when the engine supports queries as prepared statements, this is internally managed by the DBI engine.
To perform queries, the user should call the Handle.query method, that will return a Recordset instance (even if empty), nil if the query didn't return any recordset.
If the stored procedure can produce resultset even when not being invoked from an explicit "select" statement (that may be invoked by a falcon), Handle.call may return a Recordset instance. It will return nil if this is not the case.
Finally, the Handle.prepare method returns an instance of the Statement class, on which multiple Statement.execute can be invoked. As different database engine behave very differently on this regard, the base Statement.execute method never returns any recordset.
All the four SQL command methods (query and prepare) accept positional parameters as question mark placeholders; the SQL parameters can be then specificed past the first parameter (except in the case of the Handle.prepare, where the parameters are to be specified in through the returned Statement class). If the underlying engine supports positional parameters, then DBI uses the database driver specific functions to pass the parameters directly to the engine, otherwise the parameters are transformed into SQL aware string values and inserted in the statement prior passing it to the underlying functions. In case of engines using different conventions for positional parameters, while the engine-specific convention can be used, the engine ensures that the question mark can be used as a positional parameter indicator, and eventually turns it into the engine-specific placeholder prior passing it to the underlying functions.
When the parameters are stored in a Falcon array, the array call semantic can be used as in the following example:
import from dbi
dbh = dbi.connect( "..." )
limits = [ 123, "2010-3-1", "2010-4-1"]
// select all the data in the limits
dbr = (.[ dbh.query "
select * from mytab
where
f1 < ? and
date_start >= ? and
date_end <= ?" ] + limits)()
The above code is semantically equivalent to calling the @ dbh.query method applying the parameters specified in the limits array.
Fetch is the basic operation that allows to retreive data from queries. Every succesful query or stored procedure call returning data generates a Record instance that can be iteratively fetched for all the data to be returned.
The Recordset.fetch method accepts a value where the incoming query data will be stored. Recycling the same input parameter allows to minimize the memory requirements that a script needs to fetch a recordset.
The following example is the most common pattern to retreive all the data stored in a table:
import from dbi
dbh = dbi.connect( "..." )
dbr = dbh.query( "select * from mytable" )
row = []
while dbr.fetch( row )
> row.describe()
end
A Recordset instance provides method to retreive informations about the size of the recordset, the list of the retreived colums, their type and so on.
In case the structure of the table is unknwon, it may be useful to invoke Recordset.fetch passing an empty dictionary; this will fill the dictionary with column-name => column-value pairs:
import from dbi dbh = dbi.connect( "..." ) dbr = dbh.query( "select * from mytable" ) > "Data in the first row: " inspect( dbr.fetch( [=>] )
The Recordset.fetch method can also accept a Table instance; if not yet configured, the table will get shaped on the colums retrieved from the query, and will be filled with all the data from the recordset in one step:
import from dbi dbh = dbi.connect( "..." ) dbr = dbh.query( "select * from mytable" ) // dbr.fetch returns the same object passed as the parameter // the [3] accessor gets the fourth row, which is an array > "Data in the fourth row: " + dbr.fetch( Table() )[3].describe()
Databases have different, often non-standard data-types that must be mapped into Falcon item types when they must be translated into SQL parameters or when they are returned as SQL query results.
DBI transform database data types into falcon items, and eventually into Falcon standard language class instances, using the nearest data type. The following list indicates how data coming from and going to the database is transformed in/to Falcon item types.
- nil: This values indicates a NULL database value.
The "string=on" option can be specified in the connection parameter or database handle option (see Handle.options) to have all the results of the queries returned as string values, except for NULL and binary blobs, that are still returned as nil and MemBuf items. If the underlying engine supports this method natively and the extracted data should just be represented on output, or if the database engine provides some information that cannot be easily determined after the automatic Value-to-Item translation, this modality may be extremely useful.