Database functions in a specific system

Writing portable code for various databases

[coopster]

I was just curious as to how others in this forum handle database functions within an integrated system when you may want to port the entire set of scripts to another database. For example, let's say you have developed your own Content Management System (CMS) and it uses the MySQL database. I usually setup a singe script to handle the database connection and use of a specific database. This would be easy to change as it occurs in a single script and I include this script when necessary. However, I often create sql statements and process them in their individual scripts using the mysql_xxx functions, like this:

include ("$mysql_connection.php");
$sql = "SELECT * FROM table";
$rows = [b]mysql_query[/b]($sql);
$row = [b]mysql_fetch_array[/b]($rows);
.
.
.

Now I have "hard-coded" the mysql_xxx functions into an individual script, and I may have hundreds of scripts similar to this in the entire collection. Let's say that a company decides they want to run this collection of scripts on their own server, using a different database, ORACLE, DB2/400, etc. I'll have to go through my code and find/replace all the functions to use a different database connection. I am familiar with some of my options, like the dbx functions [us2.php.net] and PEAR [pear.php.net], but was curious if others ever ponder/consider these thoughts as well and what your conclusions are?

I'm not necessarily looking for direction, just your thoughts or maybe your development style if you have made this type of consideration.

[lorax]

Taking off on the idea of portability I've been thinking that I should simply make these php/mysql interface functions a part of my own generic functions. Ideally, I would make changes to one includ file and be done with it.

[killroy]

Well, For my own scripting language I wrote a caching DB middle-end. Originally it was a CSV database engine, but a small plug lets it connect to any server.

Dpeeneding on how much code you do, it might be worth it to write such an interface.

For example: sendQuery('SELECT * FROM table');get_row; and so on, and then write a different set for each engine. Of course, in my case I have a table cahce and access part in the middle.

As I said, it all depends on how often you do cross engine development and if you want to invest the time.

SN

[bcc1234]

Take a look at how phpbb guys do it.

[jetboy_70]

Coopster,

it might be worth running a search on n-tier architectures on Google. If I remember correctly, PHP Builder had a decent article.

I run a 3-tier system:

Tier 1:
Regular HTML pages that make calls to tier 2 for any dynamic content.

Tier 2:
Library functions that return formatted HTML to tier 1 and request data (in the form of arrays, variables and objects) from tier 3.

Tier 3:
API tier made up mostly of data access functions that access the database.

A couple of sample tier 3 functions:

// MySQL fetch associated array
function api_fetch($result)
{
$row = mysql_fetch_array($result);
return $row;
}

// MySQL get number of_rows
function api_num_rows($result)
{
$num_rows = mysql_num_rows($result);
return $num_rows;
}

Very simple, but if I change database, all I need to alter are the tier 3 functions.

There will be a performance hit for having this sort of portability, but it will be very minor. Apparently the performance hit of the PEAR functions is more significant, as they need to be all things to all men.

[edited by: jetboy_70 at 5:46 pm (utc) on Nov. 3, 2003]

[sun818]

PeopleSoft makes their software compatible across multiple database platforms being using very generic SQL. The proprietary functions for each DB platform is excluded. The queries and views they do have also don't contain functions that convert data types on the fly (like date to string, number to string, etc).

[gethan]

There is a library here [php.weblogs.com...] that aims to encapsulate all DB interaction. I like the look of it but haven't used it.

I wrote a simple library a few years ago to do this... but having only really used MySQL in anger with PHP am concerned that my library will not be quite as portable. If I was starting a new large project then I would look seriously for a third party library.

[jatar_k]

in the corporate software setup we use oracle and we use a lib for all db accesses etc. This is a fairly large application and the thought of changing every call to db is horrific so I wouldn't do it any other way.

With smaller stuff, up to about 10 or 20k lines, it is a lot simpler, for me, to change a bunch of lines and save the hit in process.

BUT, as with all things, it depends on the situation.