Database class
Moderator: General Moderators
- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
Database class
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?
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?
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]
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]
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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.
Depends on whether you think the simplification is worth the expense of course...
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;
}
?>- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.
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.
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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...
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...
- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
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?
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?
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
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
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)
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)
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.andre_c wrote:So, I assume that the only benefit of using OOP for database is abstraction, correct?
...
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.
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:
In continuing with the joke, this page would just dump '0'.
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;
?>Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
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
another advantage was when i switched from mysql to mysqli library
Code: Select all
function query($query)
if (preg_match(/^INSERT|UPDATE|DELETE/))
{
mysql_query($query, $this->main);
}
else
{
mysql_query($query, $this->mirror);
}Code: Select all
function query($query)
{
mysqli_query($this->main, $query);
}