ADOdb was built for speed. It has a unique ability to cache data locally, on the web server and not go to the database for some amount of time. ADOdb caches query results based on time only. If the underlying data changes before the cache expires and the query is re-executed, it will still retrieve data from the cache, instead of the database.

This feature can be very useful for executing large and expensive database queries, but be aware that time-based caching can cause inconsistencies with the state of the database. In particular, it is not advised to use the results of cached queries to update other databases. This feature is used almost exclusively for producing reports that do not have any real time components.

The mechanics of the ADOdb cache is rather simple. ADOdb caches results of queries as files in the directory defined by the ADOdb variable, $ADODB_CACHE_DIR. This directory must be accessible to the user executing the web server. In the case of the Apache web server on Linux, the user needs the ability to create files as “nobody”.  The following directive sets /tmp/adodb as the ADOdb cache directory:

This variable is script specific. You can have one cache directory per script. For the results of a query to be cached, the query must be executed by the CacheExecute() method. Below is the syntax:

The first argument defines the number of seconds to the cache expiration. The cache expiration can be set globally on the database connection level. It is not necessary to specify the first argument in the CacheExecute() method. The expiration is specified globally like this:

After such specification, the CacheExecute() method can omit the first argument and be used like this:

All result sets can be flushed from the cache by using the CacheFlush() method like this:

If the query argument is not specified, all entries are flushed from the cache. Also, if the application is a multi-user, it flushes the cache for all other users as well.  Flushing the cache without arguments removes all adodb_*.cache files from the $ADODB_CACHE_DIR and is essentially equivalent to executing the following (UNIX) command:

In other words, the mechanics of caching is very simple and the benefits of the caching mechanism depend on the decision of how to use it. Caching results emulates features of expensive application servers and can, potentially, take the significant burden away from the database server. The next example shows how caching works.

Example 31

<?php
require('adodb/adodb.inc.php');
require('adodb/tohtml.inc.php');
require('adodb/adodb-exceptions.inc.php'); 

This example selects all records from the SCOTT.EMP table and displays them in a tabular format, shown on the next page. The output is very simple but can be further modified by adding or changing the HTML attributes of rows and columns. Of course, this must be done in a visually appealing manner, in a style consistent with the rest of the page.

So, the result is exactly the same as when using the ordinary Execute() method, without caching. The following information reveals what is in the “/tmp/adodb” directory, designated as the cache directory, through the use of $ADODB_CACHE_DIRvariable. Note that different applications can have different cache directories:

$ ls -ld /tmp/adodb
drwxrwxrwx  3 nobody nobody 4096 Dec 13 21:11 /tmp/adodb
$ 

$ ps -fu nobody
UID        PID  PPID  C STIME TTY          TIME CMD
nobody    2159  2124  0 20:18 ?        00:00:00 /opt/apache/bin/httpd -k start
nobody    2160  2124  0 20:18 ?        00:00:00
/opt/apache/bin/httpd -k start
nobody    2161  2124  0 20:18 ?        00:00:00
/opt/apache/bin/httpd -k start
nobody    2162  2124  0 20:18 ?        00:00:00
/opt/apache/bin/httpd -k start
nobody    2163  2124  0 20:18 ?        00:00:00
/opt/apache/bin/httpd -k start
nobody    4075  2124  0 21:19 ?        00:00:00
/opt/apache/bin/httpd -k start
$

The directory, “/tmp/adodb” is owned by the same user employed to run the Apache server. This must be the case if it is desired to run PHP scripts from the web server and display the output in a browser. The following shows what is in the directory:

$ cd /tmp/adodb
$ ls -l
total 4
drwxrwx--x  2 nobody nobody 4096 Dec 13 21:11 90
$
$ ls -l 90
ls: 90: Permission denied
$

So, ADOdb protects its cache from unauthorized prying eyes.  The root authority is necessary to take a look:

$ su - root
Password:
[root@medo ~]#
[root@medo ~]# cd /tmp/adodb/90
[root@medo 90]# ls
adodb_904f2dcdb1aed2e9cbc4fac135718078.cache
[root@medo 90]#

If the contents of the $ADODB_CACHE_DIRECTORY are listed, it reveals that the caching file is now gone:

[root@medo adodb]# ls -lR /tmp/adodb
/tmp/adodb:
total 4
drwxrwx--x  2 nobody nobody 4096 Dec 13 21:46 90 

/tmp/adodb/90:
total 0
[root@medo adodb]#

This defeats the very purpose of caching.  The recommended practice is to clean the cache directory by using crontab or at.exe on Windows, when the web server is not active. Please, exercise extreme caution when flushing the cache programmatically, because cached data from other sessions could accidentally get cleaned. Thus, not diminishing the burden of the database server, but increasing the burden of the web server, which defeats the purpose of caching entirely.

Another ADOdb feature of limited use is logging the SQL statements into the database. This is being mentioned here for completeness only. The Oracle native tracing and performance monitoring mechanisms are far superior to those of ADOdb.

The logging of SQL statements is turned on or off like this:

$db->LogSQL();
           - or -
$db->LogSQL(FALSE);

When logging is turned on, PHP logs each statement into the table named ADODB_LOGSQL. This table is created by the following CREATE TABLE statement:

                CREATE TABLE adodb_logsql (
                  created date NOT NULL,
                  sql0 varchar(250) NOT NULL,
                  sql1 varchar(4000) NOT NULL,
                  params varchar(4000),
                  tracer varchar(4000),
                  timer decimal(16,6) NOT NULL
                );

The following example traces statements from Example 29. To do this, the $db->LogSQL() statement is inserted after the SetFetchMode on line 14.  Now, Example 29 looks like this:

Example29a

#!/usr/local/bin/php
<?php
require('adodb/adodb.inc.php');
require('adodb/adodb-exceptions.inc.php');
 

$DSN="oci8://scott:tiger@local";
$title="CLERK";
$SQL="begin
         php_demo1.demo1(:title,:csr);
      end;"; 

The only difference this example has from the original is that line 15 now contains a call to the LogSQL() method instead of the BeginTrans() method. Remember, the BeginTrans() method is used to turn off the AUTOCOMMIT mode. Logging the SQL requires AUTOCOMMIT to be turned on. After example29a.php is executed several times, the contents of the ADODB_LOGSQL table appear as follows:

This can be used for debugging unless there is a requirement that AUTOCOMMIT must be turned off. Also, bind parameters are not shown in the ADODB_LOGSQL table, only the SQL and timings.

Facilities for tracing that come bundled with the Oracle RDBMS are much more flexible and general. They are capable of showing bind values, wait events and timings, and there is no requirement for AUTOCOMMIT to be turned on. Oracle RDBMS tracing facilities should be chosen over ADOdb tracing, unless there is a specific reason not to use them. It is very hard to think of a reason for using ADOdb tracing facilities instead of using much more reliable and accurate Oracle RDBMS native tracing methods. These methods are intended for the databases with less developed tracing methodology then Oracle RDBMS.

In addition to logging and caching, ADOdb has extremely useful methods for converting result sets to HTML and paginating through result sets using the browser. The rs2html() function is bundled with ADOdb and requires the inclusion of another heather file. Here is the syntax of the rs2html function, directly from the on-line ADOdb documentation:

function rs2html($adorecordset,[$tableheader_attributes], [$col_titles])

This is a standalone function (rs2html = recordset to html) that is similar to PHP's odbc_result_all function, it prints a ADORecordSet, $adorecordset as a HTML table. $tableheader_attributes allow you to control the table cellpadding, cellspacing and border attributes. Lastly you can replace the database column names with your own column titles with the array $col_titles. This is designed more as a quick debugging mechanism, not a production table recordset viewer.

You will need to include the file tohtml.inc.php.  Example 29 is modified into yet another version:

Example 29b

<?php
require_once('adodb/adodb.inc.php');
require_once('adodb/adodb-exceptions.inc.php');
require_once('adodb/tohtml.inc.php'); 

$DSN="oci8://scott:tiger@local";
$title="CLERK";
$SQL="begin
         php_demo1.demo1(:title,:csr);
      end;"; 

The only differences from the original are the inclusion of the “tohtml.inc.php” file and using the rs2html function instead of the “foreach” function to present the output. Here is what the output looks like:

Of course, the table headers and column titles can be further adjusted. The reader is encouraged to test different attributes and column arguments.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_2005_2_php_oracle.htm