ACID: Database API

By virtue of the fact that PHP has no uniform interface for accessing databases, an abstraction layer is necessary to provide ACID a single API for multiple database types. The ADODB library provides exactly such an abstraction for a wide-variety of popular databases. However, in order to further separate ACID database access from any particular API, a further generalization was made in the form of two classes: acidCon and acidRS.

Example: Simple Select 

include ("acid_db.inc");

$db = NewACIDDBConnection("../adodb/", "mysql");
$db->acidConnect("snort_log", "localhost", "", "root", "mypassword");

$result = $db->acidExecute("SELECT count(*) FROM event");

$myrow = $result->acidFetchRow();
echo "Number of rows = $myrow[0]"; 

$result->acidFreeRows();
$db->acidClose();

class acidCon     class acidRS 
  var $DB_type;
  var $DB_name;
  var $DB_host;
  var $DB_port;
  var $DB_username;

  function acidCon
  function acidConnect
  function acidPConnect
  function acidClose
  function acidExecute
  function acidErrorMessage
  function acidTableExists
  function acidFieldExists
  function acidIndexExists
  function acidTimestampFmt
  function acidSQL_YEAR
  function acidSQL_MONTH
  function acidSQL_DAY
  function acidSQL_HOUR
  function acidSQL_MINUTE
  function acidSQL_SECOND
  function acidSQL_UNIXTIME
  
  var $DB_type;

  function acidRS
  function acidFetchRow
  function acidRecordCount
  function acidFreeRows
Support Functions
  function VerifyDBAbstractionLib
  function NewACIDDBConnection

I. class acidCon

var $DB_typeThe type of the underlying database
var $DB_nameName of the alert database
var $DB_hostHost on which the database is stored
var $DB_portPort over which to communicate to the database
var $DB_usernameUsername for the database
 
 

function acidCon ($type)

Constructor function initialized with the $type of the underlying database.
 
 

function acidConnect ($database, $host, $port, $username, $password)

Initiates a connection to database on $host through $port, using $username with $password

RETURNS: boolean of success of creating the DB connection
 
 

function acidPConnect ($database, $host, $port, $username, $password)

Initiates a persistent connection to database on $host through $port, using $username with $password

RETURNS: boolean of success of creating the DB connection
 
 

function acidClose ()

Closes the connection to the database
 
 

function acidExecute ($sql, $start_row=0, $num_rows=-1, $die_on_error=true )

Executes the $sql command and returns a record set (of type class acidRS). $start_row and $num_rows define an optional interval of rows which should be returned within the resulting record set. If no interval is defined, none is assumed; all matching records are returned. An interval of all records is equivalent to $start_row=-1, $num_rows=-1.
Typically, this function will halt and print an error message on any SQL error. The parameter $die_on_error will control the behavior of the function when errors occur.

RETURNS: record set generated by the query
 
 

function acidErrorMessage ()

RETURNS: the last error messages triggered on the database connection
 
 

function acidTableExists ($table)

Verifies that $table exists in the database

RETURNS: boolean of the existence of $table
 
 

function acidFieldExists ($table, $field)

Verifies that $field in $table exists in the database

RETURNS: boolean of the existence of $field
 
 

function acidIndexExists ($table, $index)

Verifies that $index in $table exists in the database

RETURNS: boolean of the existence of $index
 
 

function acidTimestampFmt ($timestamp)
 
 

function acidSQL_YEAR ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the year from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) YEAR(timstamp) = '2001'
 
 

function acidSQL_MONTH ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the month from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) MONTH(timstamp) = '01'
 
 

function acidSQL_DAY ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the day from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) DAYOFMONTH(timstamp) = '01'
 
 

function acidSQL_HOUR ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the hour from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) HOUR(timstamp) = '11'
 
 

function acidSQL_MINUTE ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the minute from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) MINUTE(timstamp) = '30'
 
 

function acidSQL_SECOND ($func_param, $op, $timestamp)

Generates the DB specific SQL function which extracts the second from a timestamp. 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) SECOND(timstamp) = '01'
 
 

function acidSQL_UNIXTIME ($func_param, $op, $timestamp)

Generates the DB specific SQL function which converts a timestamp to a UNIX Timestamp: the number of seconds since the epoch (Jan 01, 1970). 

       SQL_function ( $func_param ) $op $timestamp

       e.g. (in MySQL) UNIXTIMESTAMP(timstamp) > '3453134'
 
 

II. class acidRS

var $DB_typeThe type of the underlying database
 
 

function acidRS ($id, $type)

Constructor function initialized with a record set $id and the $type of the underlying database.
 
 

function acidFetchRow ()

Returns the next row in the record set and advances the record set pointer

RETURNS: an array consisting of a particular row from the record set
 
 

function acidRecordCount ()

Calculates the number of rows in the record set

RETURNS: the number of rows in the record set
 
 

function acidFreeRows ()

Deallocates the memory associated with a record set.
 
 

III. Support Functions

function VerifyDBAbstractionLib ($path)

A helper function which validates the presence of the underlying DB library in $path

RETURNS: boolean of whether the library was found
 
 

function NewACIDDBConnection ($path, $type)

Instantiates a new connection to the database of $type using the underlying library found at $path.

RETURNS: the newly created object