Fautore Database

Currently we have a choice of 2 db engines, one written by Hammer and the other SQLite.
It is an option of Fautore to choose between one or the other.
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.

Advanced Routines

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:

However FAR MORE RECOMMENDED is to use the following routines instead.

• THERE IS NO NEED TO USE ANY OF THE DBI_ routines as described above.
• 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 Routines to be Used

Examples

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;
    }
}

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)';
        }
    }
}

Note: usage of the alias option here so that different active statement handles may be differentiated.

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!';
}

Note: No row is selected.

Error Handling / Testing

SqlError() - Returns last error as a string if there was one

Convenience Routines

SQLite and Fautore

Turned into quite a mission as sqlite has many quirks and things we take for granted with the likes of MySQL just arnt there or behave very differently.

What I have ended up with is a module DBIsqlite.pm which acts as a pre-processor to reformat or simulate MySQL statements into statements that work with SQLite.

At the end of the day I think many many developers would find this module a good go between if they wish to use sqlite and do not wish to learn the quirks of this db engine.

The approach I have taken is one which means absolutely NO CHANGES required to existing Fautore SQL statements.

Fautore Switching DB's is achieved when you start fautore.

• fautore.pl -db sqlite
• fautore.pl -db fautore

Note the default if no arg specified is to use the fautore DB engine

Just as we have a fausql.pl interface utility there is now a fausqlite.pl utility
You can import fautore.sql using this utility to create the sqlite fautore database.
Via this utility you can use sql statements just as you would with MySQL.
There is one extra command disconnect dbname which you might need if you had entered
use dbname and then say wish to drop schema dbname.

REMEMBER!!! for the correct DBI.pm module to be used by either fausql.pl or fausqlite.pl
fautore MUST have been started (and or stopped if not required) selecting the DB engine you wish to use! (I may alter this but at present this is what I have done).

sqlite does have a command line interface 'sqlite3'
usage ...

• sqlite3 {pathtodbfilename}

Typically you could cd to the fautore database folder then type..

• sqlite3 fautore.db

This will create (if not there) and connect to this db after that you are stuck using the sqlite statements.
I found sqlite slow on create's but fast after that on transactions.

There are many many comments at the start of the DBIsqlite.pm module describing just what is simulated and or re-interpreted to make sqlite behave like MySQL

In particular auto_increment has been re-done and blob handling as well.

I have ended up encountering many development quirks with sqlite that they describe and mention ways around. Comments such as 'this is a known bug but it is too late to alter now as existing installations with work arounds would break' from its creators are things I have come up against.

I would suggest you try their interface sqlite3 and have a play just to see the things we take for granted that either arnt there or behave very differently.
Then try our fausqlite.pl instead which takes the hassle away.