coWiki 0.3.4
Current user:   guest   Change Preferences
   List directory   Similar   Print version 
Diff: Database Abstraction
 Legend:   Removed   Changed   Added 
 Ownership:  rw-rw-r-- iezzip users
 Modified:  15 Aug 05, 00:07
 Modified by:  Philip Iezzi (iezzip)
Rev.:  15 (Old)
 
 Ownership:  rw-rw-r-- iezzip users
 Modified:  07 Jan 06, 08:57
 Modified by:  Philip Iezzi (iezzip)
Rev.:  16 (Current)


+ %TITLE%

The Sourdough framework provides you with an extensive database abstraction layer. It is neatly integrated into Sourdough and it is recommended to use the global Sourdough object to initialize it.

<toc>

---

++ Philosophy

There are a lot of database abstraction libraries around for PHP. Most of them try to support every single database feature even if it is only available in one single database system (DBS). Actually this is not the point of an abstraction layer. The main advantage of such a layer is portability. We should be able to run an application on one DBS and then switch the next day to another without noticing anything.

This was the intention of Sourdough's database abstraction layer. It won't support any special features like triggers, foreign key generation, and stored procedures, as long as not ALL supported database systems do support them.

So, from time to time you're going to find something that can not be done with Sourdough's database abstraction layer. But on the other hand you will be more than greatfull about it's true portability!

---

++ Supported database systems

The following database systems have been tested:

* ((http://www.mysql.com)(MySQL)) 3.23.x/4.0.x (*mysql*)
* ((http://www.mysql.com)(MySQL)) > 4.1 (*mysqli*)
* ((http://www.postgresql.org)(PostgreSQL)) (*pgsql*)
* ((http://www.sqlite.org)(SQLite)) (*sqlite*)
* ((http://www.microsoft.com)(Microsoft SQL Server)) (*mssql*)

There are drivers for some additional database systems but they haven't been tested yet and are not expected to work yet. If you're planning to use one of those systems and could do some testing, let us know.

* ((http://www.sybase.com)(Sybase)) (*sybase*)
* ((http://www.borland.com/interbase/)(Borland InterBase)) (*ibase*)
* ((http://www.frontbase.com)(FrontBase)) (*fbase*)

---

++ Connecting to the database

<q>
Database connection settings belong to the global Sourdough configuration array, so all we need to do is to override the default settings. Sourdough uses PEAR-style DSN arrays for connecting to the database. Sourdough also provides a parseDSN() method for converting a connection URL to the DSN hash.

<code>
$conf['dsn'] = array(
+ %TITLE%

The Sourdough framework provides you with an extensive database abstraction layer. It is neatly integrated into Sourdough and it is recommended to use the global Sourdough object to initialize it.

<toc>

---

++ Philosophy

There are a lot of database abstraction libraries around for PHP. Most of them try to support every single database feature even if it is only available in one single database system (DBS). Actually this is not the point of an abstraction layer. The main advantage of such a layer is portability. We should be able to run an application on one DBS and then switch the next day to another without noticing anything.

This was the intention of Sourdough's database abstraction layer. It won't support any special features like triggers, foreign key generation, and stored procedures, as long as not ALL supported database systems do support them.

So, from time to time you're going to find something that can not be done with Sourdough's database abstraction layer. But on the other hand you will be more than greatfull about it's true portability!

---

++ Supported database systems

The following database systems have been tested:

* ((http://www.mysql.com)(MySQL)) 3.23.x/4.0.x (*mysql*)
* ((http://www.mysql.com)(MySQL)) > 4.1 (*mysqli*)
* ((http://www.postgresql.org)(PostgreSQL)) (*pgsql*)
* ((http://www.sqlite.org)(SQLite)) (*sqlite*)
* ((http://www.microsoft.com)(Microsoft SQL Server)) (*mssql*)

There are drivers for some additional database systems but they haven't been tested yet and are not expected to work yet. If you're planning to use one of those systems and could do some testing, let us know.

* ((http://www.sybase.com)(Sybase)) (*sybase*)
* ((http://www.borland.com/interbase/)(Borland InterBase)) (*ibase*)
* ((http://www.frontbase.com)(FrontBase)) (*fbase*)

---

++ Connecting to the database

<q>
Database connection settings belong to the global Sourdough configuration array, so all we need to do is to override the default settings. Sourdough uses PEAR-style DSN arrays for connecting to the database. Sourdough also provides a parseDSN() method for converting a connection URL to the DSN hash.

<code>
$conf['dsn'] = array(
'phptype' => 'mysql',
'driver' => 'mysql',
'username' => 'dbuser',
'password' => 'dbpass',
'hostspec' => 'localhost',
'port' => null,
'database' => 'testdb'
);

$SOURD = new Sourdough($conf);
$DB = $SOURD->Db();
</code>

This could also be written as:

<code>
$conf['dsn'] = "mysql://dbuser:dbpass@localhost/testdb";

$SOURD = new Sourdough($conf);
'username' => 'dbuser',
'password' => 'dbpass',
'hostspec' => 'localhost',
'port' => null,
'database' => 'testdb'
);

$SOURD = new Sourdough($conf);
$DB = $SOURD->Db();
</code>

This could also be written as:

<code>
$conf['dsn'] = "mysql://dbuser:dbpass@localhost/testdb";

$SOURD = new Sourdough($conf);
$DB = $SOURD->getDb();
$DB = $SOURD->Db();
</code>

*Important*: Each Sourdough object got one single database object, say one single connection. If you would like to work with several different connections, you would need to use a separate instance of the global Sourdough object. This works. Sourdough is designed to support multiple "frameworks" in one single script without interfering eachother.
</q>

---

++ SQLHelper vs. DB object

<q>
Sourdough provides you with a nice so called =SQLHelper= that helps you build your SQL queries.

Before using the SQLHelper, let us show how you could use the database object directly to execute your queries:

<code>
$DB->execute("SELECT id, name FROM users");
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

This would basically do it. For this simple SQL query we wouldn't even run into any trouble. But we want to design our code more portable, we don't want to pass a plain SQL command to the database class. We don't want to worry about different notation of different DBS. That's where the /SQLHelper/ comes in.

Look at the following example which does the same as the above:

<code>
$SQL = $SOURD->SqlHelper();

$SQL->create('users');
$SQL->addFields(array('id', 'name'));
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

This is the preferred way of doing it! You should always use SQLHelper instead of passing plain SQL-statements to the database object. The database object is also accessible through the SQLHelper object, so you might as well use =$SQL->db->moveNext()= instead of =$DB->moveNext()=. They both refer to the same object.

In all the examples below, we are going to use the SQLHelper to build our queries. Nobody hinders you of using =$DB->execute("...")= but if you wish to design your application really portable go the "SQLHelper way"!

*Remember: Use =SQLHelper= ($SQL) to create your SQL commands and use the database object ($DB) to get information about a recordset / browse through it.*
</q>

---

++ SELECTing Data

<q>
When we are talking about /SELECTing data/, we are talking about SQL that returns results, as opposed to SQL that changes the data in your database.


---



+++ Basic Loop


Here's a very basic query and result-fetch loop (same as above, extended by a 'WHERE' statement):

<code>
$SQL->create('users', 'id > 10');
$SQL->addFields(array('id', 'name'));
$SQL->execute();
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

*Variation*: instead of directly specifying the =WHERE=-part in your create statement, you could also use =$SQL->setWhere('id > 10')=.

*Warning*: If you have set a database table prefix in your global Sourdough configuration with e.g. =$conf['dboptions']['prefix'] = 'sourd_';=, this prefix will get automatically prepended to your table name. In the above example this would result in ='sourd_users'=. If you don't use the SQLHelper, '{prefix}users' would accomplish the same.


---



+++ Limiting Results


SQLHelper supports setting LIMIT/OFFSET for queries. For drivers that support this in SQL, the SQL query will be modified. For other drivers (e.g. MS SQL Server) the LIMIT/OFFSET will be emulated by the ResultSet. The behavior of the ResultSet methods will be identical regardless of whether LIMIT/OFFSET is emulated or performed natively (in SQL).

<code>
$SQL->setLimit(10, 5);
</code>

...whereas the first parameter is the limit and the second the offset value.

/Sourdough does not support using OFFSET w/o LIMIT. Some drivers (e.g. Oracle) can do this natively, but others (MySQL, PostgreSQL, SQLite) cannot./


---



+++ How many values returned?


Use the =getRowsCount()= method to return the number of records returned by current SELECT statement.

<code>
echo $Db->getRowsCount();
</code>

/If you specified a LIMIT/OFFSET then =getRowsCount()= will only return the number of results after LIMIT/OFFSET has been applied./


---



+++ Getting Column Values


The default method to get column values is to directly access the public =record= property which contains the values of the current row. In the future, there will be get*() methods which will typecast the values, but for now, stick with the =record= array.

<code>
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n";
}

// or
while($DB->moveNext()) {
foreach($DB->record as $key => $value) {
print "$key: $value\n";
}
}
</code>

Currently you need to do type conversions yourself, e.g. check for this:

<code>
$val = $DB->record['id'];
if ($val !== null) $val = (int) $val;
</code>

Also you cannot rely on the type of the returned results. Many (most?) native PHP db driver methods will return strings for every column type, but others (e.g. MS SQL Server) will return integers for numeric columns, etc. So if you are doing type-sensitive PHP scripting, you should be aware of this.


---



+++ ResultSet Indexing


By default results are indexed by field name (associative). You can specify numeric indexing after executing the query.

*Numerically indexed columns start with 0.* (This may change in the future. Following JDBC convention they start with 1.)

<code>
$SQL->create('users');
$SQL->addFields(array('id', 'name'));
$SQL->execute();

$DB->setFetchMode(Sd_Db::FETCH_NUM);

while($DB->moveNext()) {
echo $DB->record[1] . " (" . $DB->record[0] . ")\n"
}
</code>

*Warning:* Before each query execution fetch mode will be reset to =Sd_Db::FETCH_ASSOC=. You always need to set the mode after an executed query if you with to use =FETCH_NUM= or =FETCH_BOTH=.

/It is not recommended to use both numeric and assoc indexing (=FETCH_BOTH=) because not all drivers handle this natively and support for this might get deprecated some day./


---



+++ Scrolling <noop>ResultSet</noop> Methods


Scrolling ResultSet features are supported well in MySQL, PostgreSQL, and SQLite. In other DBS it might be emulated.

<code>
// Move to first record
$DB->moveFirst();

// Move ahead 3 rows
$DB->moveRelative(3);

// Scroll backwards
$DB->movePrevious();

// Move to last position
$DB->moveLast();
</code>


---



+++ ResultSetIterator


/This is a planned feature which will be implemented before version 1.0. For now, use the traditional scrolling functions./
</q>

---

++ Updating the Database

<q>
To perform a database update you proceed the same way as you would with building a regular SELECT statement. All you need to do is to change the statement to change the execution command type.

<code>
// on statement creation...
$SQL->create('users', null, Sd_SqlHelper::EXEC_UPDATE);

// or manually afterwards...
$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);
</code>

An existing SELECT statment can be transformed into an UPDATE statement without creating a new one.

<code>
$SQL->create('users', 'id = 5');
$SQL->addField('name', 'Philip');
echo $SQL->getCommand(); // SELECT name FROM sourd_users WHERE id = 5;
$SQL->execute();

$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);
echo $SQL->getCommand(); // UPDATE sourd_users SET name = 'Philip' WHERE id = 5;
$SQL->execute();
</code>

You could as well use =Sd_SqlHelper::EXEC_DELETE= to create a DELETE statement. =$SQL->getCommand()= is intended for debugging purposes and always returns the final statement.

Information about the executed statement can again be taken from the database object:

<code>
$numaffected = $DB->getAffectedRows();
</code>
</q>

---

++ Advanced Queries

<q>
+++ JOINs


Again the SQLHelper comes into use. To build a JOIN we need to specify all tables that will be used in the =$SQL->create()= statement. A join condition is defined by first creating a join with the =addJoin()= method and then adding its join ID to the =addJoinCondition()= method where we specify the affected fields.

<code>
$SQL->create(array('U' => 'users', 'S' => 'salary'));
$SQL->addFields(array('U.id', 'S.amount'));
$SQL->addJoinCondition('id', 'userid', $SQL->addJoin('U', 'S'));
$SQL->execute();

while ($DB->moveNext()){
echo 'user: '.$DB->record['id'].' salary: '.$DB->record['amount'];
}
</code>

This example would produce a simple inner JOIN:

<code>
SELECT U.id, S.amount FROM users U, salary S WHERE U.id = S.userid;
</code>

SQLHelper also supports left and right outer joins. Also, you are not limited to the usage of a single join condition. You might add as many conditions as make sense to you.


---



+++ Multiple ResultSets


In some occasions we need to use multiple resultsets. Usually a single resultset is fine, but as soon as we need to execute a query inside the loop of another resultset, we may run into trouble. In this case the query inside the loop would overwrite the above's resource ID and free its data.

Building a resultset basically means we clone an existing one. The original resultset then is ready to be overwritten by a new query. That's the way to do it:

<code>
$SQL->create('users');
$SQL->execute();

$rs = $DB->getRecordset();
while($rs->moveNext()) {
$SQL->create('salary', 'userid = '.$rs->record['id']);
$SQL->execute();
echo 'userid: '.$DB->record['userid'].' '.
'amount: '.$DB->record['amount']."\n";
}
</code>

The above sample actually doesn't make any sense as we could run this query also as a simple join, but it should demonstrate the use of multiple recordsets in a simple way. Please note the use of both =$rs->record= and =$DB->record= resultsets inside the loop.
</q>
</code>

*Important*: Each Sourdough object got one single database object, say one single connection. If you would like to work with several different connections, you would need to use a separate instance of the global Sourdough object. This works. Sourdough is designed to support multiple "frameworks" in one single script without interfering eachother.
</q>

---

++ SQLHelper vs. DB object

<q>
Sourdough provides you with a nice so called =SQLHelper= that helps you build your SQL queries.

Before using the SQLHelper, let us show how you could use the database object directly to execute your queries:

<code>
$DB->execute("SELECT id, name FROM users");
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

This would basically do it. For this simple SQL query we wouldn't even run into any trouble. But we want to design our code more portable, we don't want to pass a plain SQL command to the database class. We don't want to worry about different notation of different DBS. That's where the /SQLHelper/ comes in.

Look at the following example which does the same as the above:

<code>
$SQL = $SOURD->SqlHelper();

$SQL->create('users');
$SQL->addFields(array('id', 'name'));
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

This is the preferred way of doing it! You should always use SQLHelper instead of passing plain SQL-statements to the database object. The database object is also accessible through the SQLHelper object, so you might as well use =$SQL->db->moveNext()= instead of =$DB->moveNext()=. They both refer to the same object.

In all the examples below, we are going to use the SQLHelper to build our queries. Nobody hinders you of using =$DB->execute("...")= but if you wish to design your application really portable go the "SQLHelper way"!

*Remember: Use =SQLHelper= ($SQL) to create your SQL commands and use the database object ($DB) to get information about a recordset / browse through it.*
</q>

---

++ SELECTing Data

<q>
When we are talking about /SELECTing data/, we are talking about SQL that returns results, as opposed to SQL that changes the data in your database.


---



+++ Basic Loop


Here's a very basic query and result-fetch loop (same as above, extended by a 'WHERE' statement):

<code>
$SQL->create('users', 'id > 10');
$SQL->addFields(array('id', 'name'));
$SQL->execute();
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}
</code>

*Variation*: instead of directly specifying the =WHERE=-part in your create statement, you could also use =$SQL->setWhere('id > 10')=.

*Warning*: If you have set a database table prefix in your global Sourdough configuration with e.g. =$conf['dboptions']['prefix'] = 'sourd_';=, this prefix will get automatically prepended to your table name. In the above example this would result in ='sourd_users'=. If you don't use the SQLHelper, '{prefix}users' would accomplish the same.


---



+++ Limiting Results


SQLHelper supports setting LIMIT/OFFSET for queries. For drivers that support this in SQL, the SQL query will be modified. For other drivers (e.g. MS SQL Server) the LIMIT/OFFSET will be emulated by the ResultSet. The behavior of the ResultSet methods will be identical regardless of whether LIMIT/OFFSET is emulated or performed natively (in SQL).

<code>
$SQL->setLimit(10, 5);
</code>

...whereas the first parameter is the limit and the second the offset value.

/Sourdough does not support using OFFSET w/o LIMIT. Some drivers (e.g. Oracle) can do this natively, but others (MySQL, PostgreSQL, SQLite) cannot./


---



+++ How many values returned?


Use the =getRowsCount()= method to return the number of records returned by current SELECT statement.

<code>
echo $Db->getRowsCount();
</code>

/If you specified a LIMIT/OFFSET then =getRowsCount()= will only return the number of results after LIMIT/OFFSET has been applied./


---



+++ Getting Column Values


The default method to get column values is to directly access the public =record= property which contains the values of the current row. In the future, there will be get*() methods which will typecast the values, but for now, stick with the =record= array.

<code>
while($DB->moveNext()) {
echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n";
}

// or
while($DB->moveNext()) {
foreach($DB->record as $key => $value) {
print "$key: $value\n";
}
}
</code>

Currently you need to do type conversions yourself, e.g. check for this:

<code>
$val = $DB->record['id'];
if ($val !== null) $val = (int) $val;
</code>

Also you cannot rely on the type of the returned results. Many (most?) native PHP db driver methods will return strings for every column type, but others (e.g. MS SQL Server) will return integers for numeric columns, etc. So if you are doing type-sensitive PHP scripting, you should be aware of this.


---



+++ ResultSet Indexing


By default results are indexed by field name (associative). You can specify numeric indexing after executing the query.

*Numerically indexed columns start with 0.* (This may change in the future. Following JDBC convention they start with 1.)

<code>
$SQL->create('users');
$SQL->addFields(array('id', 'name'));
$SQL->execute();

$DB->setFetchMode(Sd_Db::FETCH_NUM);

while($DB->moveNext()) {
echo $DB->record[1] . " (" . $DB->record[0] . ")\n"
}
</code>

*Warning:* Before each query execution fetch mode will be reset to =Sd_Db::FETCH_ASSOC=. You always need to set the mode after an executed query if you with to use =FETCH_NUM= or =FETCH_BOTH=.

/It is not recommended to use both numeric and assoc indexing (=FETCH_BOTH=) because not all drivers handle this natively and support for this might get deprecated some day./


---



+++ Scrolling <noop>ResultSet</noop> Methods


Scrolling ResultSet features are supported well in MySQL, PostgreSQL, and SQLite. In other DBS it might be emulated.

<code>
// Move to first record
$DB->moveFirst();

// Move ahead 3 rows
$DB->moveRelative(3);

// Scroll backwards
$DB->movePrevious();

// Move to last position
$DB->moveLast();
</code>


---



+++ ResultSetIterator


/This is a planned feature which will be implemented before version 1.0. For now, use the traditional scrolling functions./
</q>

---

++ Updating the Database

<q>
To perform a database update you proceed the same way as you would with building a regular SELECT statement. All you need to do is to change the statement to change the execution command type.

<code>
// on statement creation...
$SQL->create('users', null, Sd_SqlHelper::EXEC_UPDATE);

// or manually afterwards...
$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);
</code>

An existing SELECT statment can be transformed into an UPDATE statement without creating a new one.

<code>
$SQL->create('users', 'id = 5');
$SQL->addField('name', 'Philip');
echo $SQL->getCommand(); // SELECT name FROM sourd_users WHERE id = 5;
$SQL->execute();

$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);
echo $SQL->getCommand(); // UPDATE sourd_users SET name = 'Philip' WHERE id = 5;
$SQL->execute();
</code>

You could as well use =Sd_SqlHelper::EXEC_DELETE= to create a DELETE statement. =$SQL->getCommand()= is intended for debugging purposes and always returns the final statement.

Information about the executed statement can again be taken from the database object:

<code>
$numaffected = $DB->getAffectedRows();
</code>
</q>

---

++ Advanced Queries

<q>
+++ JOINs


Again the SQLHelper comes into use. To build a JOIN we need to specify all tables that will be used in the =$SQL->create()= statement. A join condition is defined by first creating a join with the =addJoin()= method and then adding its join ID to the =addJoinCondition()= method where we specify the affected fields.

<code>
$SQL->create(array('U' => 'users', 'S' => 'salary'));
$SQL->addFields(array('U.id', 'S.amount'));
$SQL->addJoinCondition('id', 'userid', $SQL->addJoin('U', 'S'));
$SQL->execute();

while ($DB->moveNext()){
echo 'user: '.$DB->record['id'].' salary: '.$DB->record['amount'];
}
</code>

This example would produce a simple inner JOIN:

<code>
SELECT U.id, S.amount FROM users U, salary S WHERE U.id = S.userid;
</code>

SQLHelper also supports left and right outer joins. Also, you are not limited to the usage of a single join condition. You might add as many conditions as make sense to you.


---



+++ Multiple ResultSets


In some occasions we need to use multiple resultsets. Usually a single resultset is fine, but as soon as we need to execute a query inside the loop of another resultset, we may run into trouble. In this case the query inside the loop would overwrite the above's resource ID and free its data.

Building a resultset basically means we clone an existing one. The original resultset then is ready to be overwritten by a new query. That's the way to do it:

<code>
$SQL->create('users');
$SQL->execute();

$rs = $DB->getRecordset();
while($rs->moveNext()) {
$SQL->create('salary', 'userid = '.$rs->record['id']);
$SQL->execute();
echo 'userid: '.$DB->record['userid'].' '.
'amount: '.$DB->record['amount']."\n";
}
</code>

The above sample actually doesn't make any sense as we could run this query also as a simple join, but it should demonstrate the use of multiple recordsets in a simple way. Please note the use of both =$rs->record= and =$DB->record= resultsets inside the loop.
</q>


Home | Documentation | Download | RSS 1.0 | GEO URL

Driven by coWiki 0.3.4 (Boron) web collaboration tool.