Fautore DB Overview
Currently we have a choice of 2 db engines, one written by Hammer and SQLite. It is an option of Fautore to choose between one or the other.
SQLite Note: The SQLite DB has a list of keywords that should not be used in queries.
Modules
Two modules exist to make life easier
- DBIsqlite.pm
- DBIfautore.pm
When fautore starts, depending on which db you have chosen to use, one of these modules will be copied to create DBI.pm
DBI.pm is then used as our interface to our DB by our programs.
For those that like the traditional DB style of using ... connect, prepare, execute, fetchrow/array, finish and disconnect these are available using calls such as:
| Function | Description |
|---|---|
| DBI_Error() | Returns last error as a string if there was one |
| DBI_Connect(port,host,db) | Returns dbh if successful |
| DBI_Disconnect(dbh) | Returns undef |
| DBI_Prepare(dbh,statement) | Returns sth if successful |
| DBI_Execute(sth) | Returns true or rows if successful |
| DBI_Finish(sth) | Returns undef |
| DBI_FetchRow_Array(sth) | Returns columns as an array or a single string eg my @data = DBI_FetchRow_Array($sth); my ($a,$b,$c) = DBI_FetchRow_Array($sth); my $a = DBI_FetchRow_Array($sth); |
DB Shortcuts
It is highly recommended the functions in this section be used instead of the basic functions listed above for these reasons:
- Fewer lines of code
- Automatic connection to our default db 'fautore' (optionally select/specify another dbname)
- Automatic preparation and execution of statements.
- Automatic finishing of statement handles (unless breaking out of a SqlFetch loop)
Basic Recommended Functions
These below functions are recommended for use to simplify coding.
| Function | Description |
|---|---|
| SqlStatement(statement,alias) | Returns true/false or quantity of rows depending on the statement passed to it and if the statement was successful. The alias is an optional identifier for use where nested calls to this routine are within loops. |
| SqlFetch(alias) | Returns column data as an array or string. The alias is optional for nested calls to this routine within loops. |
| SqlFinish(alias) | Finishes and flushes control/data of a still active fetch loop. The alias is optional for nested calls to this routine within loops. |
Examples
Simple Loop
my $statement = "select name,phone from accounts where name like 'hammer%'"; if (SqlStatement($statement)) { # we know there was one or more rows found while(my($name,$phone) = SqlFetch()) { # do some thing print 'Name:' . $name . ' Phone:' . $phone; } }
Nested Loops
# NOTE usage of the alias option here so that different active statement handles may be differentiated. my $statement = "select name,phone from accounts where name like 'hammer%'"; if (SqlStatement($statement)) { # we know there was one or more rows found while(my($name,$phone) = SqlFetch()) { $statement = "select town from phones where phone = '$phone'"; if (SqlStatement($statement,"SOMEALIASTEXT")) { my $town = SqlFetch("SOMEALIASTEXT"); print 'Name:' . $name . ' Phone:' . $phone . ' (it seems you live in ' . $town . ')'; } else { # do some thing else print 'Name:' . $name . ' Phone:' . $phone . ' (dont know which town your phone number belongs to)'; } } }
Row Status
# lets just see if row data exists BUT NOT fetch it my $statement = "select password from accounts where accountname = 'hammer' and password = 'nails'"; if (SqlStatement($statement)) { # yah password match lets just finish this as we dont need to fetch row/column data SqlFinish(); print 'Password OK'; } else { # The statement has failed to return data there is NO NEED to finish this statement as this has been done automatically print 'Password BAD!'; }
Error Handling / Testing
| Function | Description |
|---|---|
| SqlError() | Returns last error as a string if there was one |
Convenience Routines
| Function | Description |
|---|---|
| SqlNow(type,utcflag) | Returns a nice string formatted using the time now in either local (default) or UTC. 'type' may be one of date,datetime,timestamp,time,year |
| SqlDefaultDB(newdbname) | Return either the dbname of the currently selected db OR swap to a newly specified newdbname returning said name if successful. |
| SqlEscapeString($str) | Process string for any special characters meaningful to the DB and escape them. |