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)

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.