Intro

Database Abstraction (or Database Independence) means that Tiki 1.8 and beyond will be compatible with not only MySQL, but also SQLite, Oracle, PostgreSQL, etc

In the past the ADOdb class library was used. However, we’re switching to PDO now.

Why? Also see “Why use a database class library?”

If you'd like to help, here's how:

Note: This wiki page may be severely out of date as it was written back when porting to ADOdb started.

Instructions how to rewrite Queries

We/You can help to rewrite the SQL queries.

The new format uses the technique of bind variables.
This requires that in the SQL query all identifiers are backquoted (`) and all values are replaced with a placeholder (?).
The bind variables are then passed, in an array, to the tikilib->query and tikilib->GetOne routines.

Note: Calls to the addslashes() function are no longer required.

Everybody can help. Take a file in the lib/* dirs and look for SQL queries and rewrite them.

---

First Example:

$user=addslashes($user);
$query = "select value from tiki_user_preferences where prefName=$name and user=$user";
$result = $this->query($query);

should be rewritten to:

$query = "select `value` from `tiki_user_preferences` where `prefName`=? and `user`=?";
$result = $this->query($query,array($name,$user));

---

Next: the "limit $offset,$numrows" in SQL queries should be deleted and
passed as parameters to the tikilib->query function. Example:

$query = "select value,prefName from tiki_user_preferences limit $offset, $numrows";
$result = $this->query($query);

should be rewritten to:

$query = "select value,prefName from tiki_user_preferences";
$result = $this->query($query,array(),$numrows,$offset);

---

A widely used construct in tiki has problems with DB abstraction: The sort mode. Example:

function list_games($offset, $maxRecords, $sort_mode, $find) {
$sort_mode = str_replace("_", " ", $sort_mode);
....some code ....
$query = "select * from tiki_games $mid order by $sort_mode limit $offset,$maxRecords";
$result = $this->query($query);

the st_replace line should be deleted, and the SQL part that does the sorting should be generated with $this->convert_sortmode($sort_mode). Therefore the example should go to:

function list_games($offset, $maxRecords, $sort_mode, $find) {
....some code ....
$query = "select * from `tiki_games` $mid order by ".$this->convert_sortmode($sort_mode);
$result = $this->query($query,array(),$maxRecords,$offset);

---

Now we go to the part ...some code ...
Another widely used construct is:

function list_games($offset, $maxRecords, $sort_mode, $find) {
$sort_mode = str_replace("_", " ", $sort_mode);

if ($find) {
$findesc = $this->qstr('%' . $find . '%');

$mid = " where (gameName like $findesc)";
} else {
$mid = "";
}

$query = "select * from tiki_games $mid order by $sort_mode limit $offset,$maxRecords";

Since we use bind variables, we have to create a array with the correct number of elements. The $mid is empty or has some SQL in there. In the first case we need a empty array (no other variables in query) in the second we have one bind variable. Look at the rewritten code:

function list_games($offset, $maxRecords, $sort_mode, $find) {
if ($find) {
$findesc = '%' . $find . '%'; //$this->qstr() is a alias for addslashes

$mid = " where (`gameName` like ?)";
$bindvars=array($findesc);
} else {
$mid = "";
$bindvars=array();
}
$query = "select * from `tiki_games` $mid order by ".$this->convert_sortmode($sort_mode);
$result = $this->query($query,$bindvars,$maxRecords,$offset);

---

All "replace into" queries have to be rewritten to do a delete from the table ignoring possible errors, and then do an insert. Example:

$query = "replace into tiki_semaphores(semName,timestamp,user) values($semName,$now,$user)";
$result = $this->query($query);

We have to find out what to delete, therefore we have to have a look at the table structure:

mysql> desc tiki_semaphores;
+-----+----+--+---+---+---+
| Field | Type | Null | Key | Default | Extra |
+-----+----+--+---+---+---+
| semName | varchar(250) | | PRI | | |
| user | varchar(200) | YES | | NULL | |
| timestamp | int(14) | YES | | NULL | |
+-----+----+--+---+---+---+
3 rows in set (0.00 sec)

The ))semName(( is the primary key. so we have to delete with a "where semName=?":

$query = "delete from `tiki_semaphores` where `semName`=?";
$result = $this->query($query,array($semName),-1,-1,false); //the false allows ignoring errors
$query = "insert into `tiki_semaphores`(`semName`,`timestamp`,`user`) values(?,?,?)";
$result = $this->query($query,array($semName,$now,$user));

---

Todo, unclear:

• Some sql functions have other names in other DB's or have to be programmed into the DB
• Some of them have to be worked around. Example: I plan to add an extra table that holds the relation object name and md5(objectname) to circumvent the md5 sql function of sybase. This can increase performance too, because not all databases can build function based indexes.
• Some bind variables have to be casted for sybase and postgres. If you encounter strange errors in these db's try something like $bindvars=array($somestring, (int) $someint, ....)
• For binary or large data, the data types of the fields have to be tested.
• Some table names and other identifiers are to long. sybase can take up to 26 characters (I think) for identifiers.
• Oracle 8 seems to have more and more problems. length() does not work on CLOB columns and the LEFT JOIN syntax is not supported. We have to test if Oracle 9i will do that (Documentation says yes).

  
  

Porting Issues

We already have a DB abstraction layer (PEAR::DB).

What we need to do is remove all mySQL specific contructs. Some of these are:

ENUM fields
MD5() (This has been addressed for Postgres and Oracle)
CONCAT()
LOCATE()
REPLACE()
BINARY
LIMIT X, Y
LENGTH() - is this ANSI?

Other issues:

Case sensitive searches.
Searching for values that could be null.
Full text searching (mySQL's MATCH/AGAINST).

Can anyone think of anything else?

ADOdb, is a much better abstraction layer than PEAR::DB, and I highly recommend we use it. Here's why:

1. It's faster than PEAR::DB. See http://phplens.com/lens/adodb/.
2. It already abstracts functions such as CONCAT() and LIMIT.
3. It supports more databases, including SQLite. See http://phplens.com/lens/adodb/docs-adodb.htm#drivers.
4. Its error messages are much more descriptive (ever get PEAR's 'DB Error' message?)
5. It's more feature-complete. See http://php.weblogs.com/Compare_PEAR_DB_ADOdb.
6. It's actively maintained.

Also, as I've posted to tiki-devel several times, we need to start moving towards:

$sql = "select a,b from c where d=? and e=?";
$db->query($sql, array('dvalue', 'evalue'));

This allows the abstraction layer to do the quoting for us, as it varies from DB to DB.

It will also improve performance when we move to PHP's new mySQLi driver.
See Archive(SqlStatementsInTiki) for a list of all the SQL statement in tiki.

Comments / Discussion

I can: login, change admin password, view HomePage with

• PostgreSQL 7.3
• Mysql 3.X
• Oracle 8.1.7
• Sybase 12.5

If someone has other versions of these servers, please verify!
Especially MSSQL should work with sybase_ct drivers, but i cannot verify.
If you want to try, do a checkout of the DB_abstraction branch, (see instructions above).

Add these lines to your local.php:
$api_tiki = 'adodb';
$dbversion_tiki="sybase";
$db_tiki="sybase";

If you have problems connecting, drop me a mail.

As I discussed with ross, this branch (DB_abstraction) is still a very
experimental branch. And since it will make much work to change all the
thousands of lines to make all features working, I'll stop here now until
the final decision.

For a overview, what has to be changed to make all working with all DB's:

• quote all identifiers with `. Example:

delete from tiki_history where pageName='$pageName_sl' and version='$version'

has to be changed to:

delete from `tiki_history` where `pageName`='$pageName_sl' and `version`='$version'

• all SQL variables have to go in bind-variable arrays:

$query = "delete from `tiki_history` where `pageName`='$pageName_sl' and `version`='$version'";
$this->query($query);

has to be changed to:

$query = "delete from `tiki_history` where `pageName`=? and `version`=?
$this->query($query,array($pageName_sl,$version);

• all integers saved in php variables that are not integer have to be

casted (PostgreSQL and Sybase do not implicitely convert)

$this->query($query,array($pageName_sl,$version);

has to be changed to:

$this->query($query,array($pageName_sl,(int) $version);

• All identifiers have to be truncated to a maximum length of 28

characters (Sybase, Oracle: 32). Some table names are affected as far as
i see.

• All (or almost all) SQL functions have to be written in PHP:

$query="insert into `bla` values(MD(?))";
$this->query($query,array($blubb));

has to be rewritten to:

$query="insert into `bla` values(?)";
$this->query($query,array$blubb;

other functions are possibly not so easy!

• Sybase and Postgres are picky if you try:

$query = "select `groupName` from `users_usergroups` where `userId`=?";
$result = $this->query($query,array($userid));

(from userslib.php function get_user_groups)
and $userid id NULL because another query ($userid = $this->get_user_id($user);)
returned no rows... Here we have to default some values to -1 or any
other invalid value.

The recent addition of multiple sites is really nice. The next step would be to allow multiple Tiki's to live in a single DB by giving each database object a prefix that could be linked to a domain. The default prefix could be 'tiki_' for single domain sites. This might not belong with DB Abstraction, but as long as we are in changing all the SQL code, we could implement the prefix at the same time.

First, we could add query timings and EXPLAIN each query. My guess is we're doing table scans when we should be using indexes. This is really simple to do, but will yield huge benefits.

Second, we could remove all unnecessary SELECT COUNT(*)'s, which are very expensive DB wise, as they do a full index scan. For example:

$cant = $this->getOne("select count(*) from tiki_referer_stats where referer='$referer'");
if($cant) {
$query = "update tiki_referer_stats set hits=hits+1,last=$now where referer='$referer'";
} else {
$query = "insert into tiki_referer_stats(referer,hits,last) values('$referer',1,$now)";
}

would be better written as:

$query = 'update tiki_referer_stats set hits = hits + 1, last = ? where referer = ?';
$tikilib->query($query, array($now, $referer));
if ($tikilib->db->affectedRows() == 0) {
$query = 'insert into tiki_referer_stats (referer, last, hits) values (?, ?, ?)';
$tikilib->query($query, array($referer, $now, 1));
}

This not only gets rid of the SELECT COUNT(*), but replaces two queries with one, in most cases.

We could even generalize this as:

$fields = array(
'referer' => $referer,
'last' => $now,
'hits' => array('insert' => 1, 'update' => '~hits + 1'),
);
$tikilib->replace('tiki_referer_stats', $fields, 'referer');

Which is a lot easier to read than the first two examples, IMHO.

Lastly, we could cache preferences, and other information that changes infrequently, in $_SESSION, and update it only if the information changes (such as the user changing their preferences).

-Ross

We already have a little problem with some MySQL properties using the search function. I guess other database programs also have similar problems. If we want to document those limitations (by example indicating to the user "We can't proceed to this search because...") we'll have to document each problematic behavior of databases. See for this Documentation of database's limitations for search - Chealer

See DbPerformance