Page 1 of 1

Database class

Posted: Mon Nov 22, 2004 11:08 pm
by andre_c
When using a database for retrieving and updating records (not for modeling or other not-so-common practices) and portability of the SQL is not important, is there a reason to put the functionality inside of a class?
When I first started programming using OOP it seemed like a good idea to handle everything with objects. I'm not so sure anymore. I like OOP and I tend to believe is the better way to program, I just want to know if there's other reasons to put the database functionality inside of a class. Do most of you do this?

Posted: Mon Nov 22, 2004 11:27 pm
by crabyars
For what it's worth, I always use a database abstraction class so that if i need to change databases (from MySQL to Postgres for example) I only need to modify the queries.

The class I use is ezsql - http://php.justinvincent.com I love it, very easy to use and OO too!

I know many people like the PEAR classes also - http://pear.php.net/[/url]

Posted: Tue Nov 23, 2004 3:48 am
by Maugrim_The_Reaper
ADODB - http://adodb.sourceforge.net/

To simplify it even more you can add an additional overlay encapsulating the ADODB - or PEAR methods for querying the database. e.g.

Code: Select all

<?php
function db_connect($database_host, $database_user, $database_password, $database, $database_persistent)
{
	global $CONFIG;
	$db = &ADONewConnection($CONFIG['db_type']);
	$db->autoRollback = true;
	// use postgres7 usual connection through single host parameter
	if($CONFIG['db_type'] == "postgres7") 
	{
		$db->PConnect("host=$database_host port=$CONFIG[database_port] dbname=$database");
	}
	elseif($database_persistent == 1) 
	{
		$db->PConnect($database_host, $database_user, $database_password, $database);
	}
	else
	{
		$db->Connect($database_host, $database_user, $database_password, $database);
	}
	return $db;
}

function db($string,$filename,$linenum) 
{
	global $query, $db;
	$query = $db->Execute($string);
	if ($query == false) 
	{ 
        print '<h3>SQL SELECT [ db() ] Query Error:</h3><p>'.$db->ErrorMsg().'</p><p><h3>Query:</h3></p><p>'.$string.'</p><p><h3>File:</h3></p><p>'.$filename.'<br />Line Number: <b>'.$linenum.'</b></p>';
		exit();
	} 
}

// function for fetching each result row - one per use of dbr()
function dbr() 
{
	global $query, $db;
	$fetched = $query->fields;
	$query->MoveNext();
	return $fetched;
}
?>
Depends on whether you think the simplification is worth the expense of course...

Posted: Tue Nov 23, 2004 4:37 am
by CoderGoblin
My 2 cents... Using a database abtraction class can be useful. It should not however totally replace the knowledge of how to handle a database, if you needed to, without the class. You should always know how to do it yourself rather than rely on a class you do not understand at all. This goes really for any class.

Also bear in mind that if you need to provide example code (e.g in this forum) you would also need to explain the methodology of the database handling.

Posted: Tue Nov 23, 2004 5:22 am
by Maugrim_The_Reaper
Given how simple any non-portable database set of methods are likely to be, I don't really see any benefit to using OOP. A list of functions in this case are just as useful as a full class.

And no, database abstraction is not something you need. I use it primarily for portability, and a few other side benefits - but it's overkill for a simple set of database functions...

Posted: Tue Nov 23, 2004 12:22 pm
by andre_c
So, I assume that the only benefit of using OOP for database is abstraction, correct?
But when you do that you're losing features specific to the database that you are using.
Can I assume then that if you're not looking for abstraction, then using the functions is just as good as using objects for the database?
Is there some other benefit from managing the database through an object?

Posted: Tue Nov 23, 2004 4:45 pm
by timvw
i've written some classes that represent a "datastore", in most systems that is synonim for a database. http://timvw.madoka.be/datastores/std.d ... class.phps

then i've extended that class to a pageable datastore it allows me to retrieve entities in pages. http://timvw.madoka.be/datastores/pagea ... class.phps

then i've implemented a mysql and a mysqli backend for that pageable datastore.
http://timvw.madoka.be/datastores/mysql ... class.phps
http://timvw.madoka.be/datastores/mysql ... class.phps

and in concreto i've written 2 concrete classes (by implementing the datadictionary methods)
http://timvw.madoka.be/datastores/city. ... class.phps
http://timvw.madoka.be/datastores/count ... class.phps

by using this abstraction, all my scripts can use the create, read, update and delete function of the datastore. all validation and dml generation (usually sql) is done by the classes ;)

Posted: Tue Nov 23, 2004 6:45 pm
by McGruff
Eclipse is also worth a look.

OOP is used to encapsulate stuff related to the database object - in Eclipse that's the connection, querying, errors & etc. OOP doesn't mean you lose db specific features (Eclipse for example has different classes for different dbs).

Further reading:

http://www.phppatterns.com/index.php/ar ... ew/25/1/1/
http://www.martinfowler.com/eaaCatalog/ (see data source architectural patterns)

Posted: Wed Nov 24, 2004 2:51 am
by jl
andre_c wrote:So, I assume that the only benefit of using OOP for database is abstraction, correct?
...
Personally I can't think of any situation when I wouldn't want to abstract database access (through OO or any other method) because having the option to be able to easily change the underlying database or data-access system (e.g. if you wanted/needed to convert from RDBMS to flat text files for e.g.) is invaluable.

I have my own db class that can be used for MySQL or PostgresQL access. It supports some PostgresQL features that aren't included in MySQL, like transactions, but still would greatly assist in the change either way between MySQL and PostgresQL, or from either to a new db.

Posted: Wed Nov 24, 2004 10:29 am
by pickle
Abstraction is definitely an advantage, but there are certainly others. I've got my own class that has a few advantages not provided by inline connecting, in as few lines of code.

For 1, it loads connection variables from a config file. This way, I don't have to re-enter connection data on each page - saving myself some time in coding, and saving time if I ever have to change the database name, password or username (it's happened more often than it should).

Second, error reporting is much nicer. The way my current class is set up, if a query fails, it very nicely outputs an organized error message telling me what the error returned was, the query that generated the error, and the string I've passed along, saying what I was doing in the query. It really helps to debug stuff.

Finally, I've got a run-time file for my class that sets up all the connections and selects the correct DB for me. So, if I want to access a database from a given page, I just include(db.php) at the beginning of the file, then I can use 1 line to actually do the query. A typical situation could look like this:

Code: Select all

<?php
include('db.php');
//some code here..
//more code
$query = <<<SQL
SELECT
   *
FROM
   users
WHERE
   clue > 0
SQL;

$result = $DB->sql_query($query,'finding non-clueless users');
$count = $DB->count_rows($result);
echo $count;

?>
In continuing with the joke, this page would just dump '0'.

Posted: Wed Nov 24, 2004 10:53 am
by timvw
i didn't see real advantages of having an abstraction, untill we got a mysql mirror (reading happens on mirror, create/update/delete happens on the main). With somewhat of an abstraction class, the correct server can be choosen easily

Code: Select all

function query($query)
if (preg_match(/^INSERT|UPDATE|DELETE/))
{     
       mysql_query($query, $this->main);
}
else
{
       mysql_query($query, $this->mirror);
}
another advantage was when i switched from mysql to mysqli library

Code: Select all

function query($query)
{
     mysqli_query($this->main, $query);
}

Posted: Thu Nov 25, 2004 1:57 pm
by McGruff
Personally I think that some custom fns - along the lines of Timvw's examples above - are a minimum. You won't see the need for abstraction until you get bitten one day.