phpBBHacks.com, the place for phpBB users

Bookmark and Share

phpBB Database Layer

phpBB Database Layer

Postby Thoul » September 23rd 2003, 4:23 am

The PHP coding language features easy-to-use functions for many forms of database software. These functions, usually named after the database software, are the means by which PHP scripts access and perform operations on a database. phpBB, as a PHP script, uses these functions to provide dynamic content for a forum.

However, phpBB supports several popular database software packages, as you may know. In order to do this while keeping the size of phpBB itself reasonable, a special coding method called a "database abstraction layer," or "DBAL," is used. As with most things, the DBAL has good and bad points. However, it is one of the backbones of phpBB.

In this tutorial, we will explore the phpBB Database Abstraction Layer, it's good and bad points and benefits for hack authors. The DBAL's functions will also be listed, explained and equated to the PHP functions they use.



For ease of use by hack authors, a downloadable version of this tutorial is available in the phpBBHacks.com database. Download it and you'll be able to browse the tutorial offline at anytime in your favorite web browser.
Last edited by Thoul on September 26th 2003, 4:53 am, edited 3 times in total.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 23rd 2003, 4:46 am

What is good about the DBAL?

There are two major useful points to the phpBB Database Abstraction Layer.

Since the PHP functions for different databases have different names, using them without a DBAL would mean phpBB would consist of much larger files or support only one or two database types. The DBAL allows phpBB to be coded for potentially any type of database supported by PHP with the addition of merely one file per database type. If the DBAL does not include a coder's favorite type of database software, they can extend the DBAL by creating a new file to add that support.

Hacks can also use the DBAL just like the original phpBB code. A hack written for the DBAL can potentially be used on several types of databases, while a hack written with the PHP functions can only be used on the type supported by those functions. The DBAL allows hack authors to create cross-platform scripts that can be more easily used by a wider audience.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 23rd 2003, 4:47 am

What is bad about the DBAL?

Nothing is perfect and the phpBB Database Abstraction Layer is no exception.

The absolute largest problem is that the DBAL is not as familiar to coders as the PHP functions. phpBB, while a very popular forum software, has not been around as long as PHP and is not likely to have been used by nearly as many coders. Using phpBB's DBAL means learning how to use a new set of functions for which there has been no documentation available. There has been no method of learning how to use the DBAL other than examining the files and experimentation. Obviously, this tutorial is designed to correct this problem.

The second problem is that the DBAL is not compatible with all types of software. Certain server caching software can cause the DBAL to slow down a site and even prevent it from working. Certain server settings can prevent more than one database connection from being opened in a script (the DBAL cannot be used alongside PHP functions in some cases). There may be other cases as well.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 23rd 2003, 4:48 am

Should hack authors write code using the DBAL? Why?

Yes, hack authors should definitely write code using the phpBB Database Abstraction Layer whenever possible.

The most important reason behind this statement is not that phpBB uses the DBAL, but rather that phpBB users rely on the DBAL. If a hack author writes a hack using the PHP functions for MySQL, that hack can only be used on sites using MySQL. Any user of the Oracle, PostgreSQL, Microsoft Access or Microsoft SQL database software will be unable to use the hack. If the hack had been written to use the DBAL, these users might have been able to use it.

While the majority of phpBB users do use MySQL, users of other database software packages are not a minority that should be ignored. A good web designer knows that you do not design a site to work only in one browser, because site users may use any of a hundred different browsers. The same holds true for coding phpBB hacks that access the database - hack users may use any of several database software packages.

Another reason to use the DBAL is efficiency. Any file that includes phpBB's db.php (or common.php, which includes that file) will establish a database connection that will remain open until the script ends or the connection is closed within the script. Using PHP database functions in these files may establish a second database connection, resulting in the use of excess server resources and processing time. There may also be server settings that prevent the second connection from being established, which would cause the file to function improperly.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 23rd 2003, 4:50 am

How to use the DBAL

Once you get the hang of it, the phpBB Database Abstraction Layer is deceptively simple to use.

The DBAL is a series of variables and functions operating on those variables, all of which are related to the database connection. In PHP, this is called a class. All of the DBAL's variables and functions can be used outside of the class, making them available in any part of phpBB's code after the database connection is established.

You can use the variables and functions as you would use normal variables and functions, with one exception. To use a normal variable, you would write it as $var_name. Likewise, a normal function call would be written as function_name($other_var). For DBAL functions and variables, these references would be written as $db->var_name and $db->function_name($other_var).
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 24th 2003, 2:57 am

About the Database Abstraction Layer variables

There are a number of variables available to coders using the phpBB Database Abstraction Layer.

A summary of these variables and when they are available follows. All data in this section is based on phpBB 2.0.6 DBAL files. Each variable is described in the following manner.
Variable Name ($db->whatever_it_is_called)
Supported Database Layers (The database types for which this variable is available.)
Description (What the variable stores and what it can be used to accomplish.)
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 24th 2003, 3:02 am

$db->db_connect_id
All
Stores a connection resource or link identifier associated with the database connection. This can be used in functions that access the database and to check for a valid database connection.

$db->num_queries
All
Stores the total number of queries that have been executed in the script at this point. Useful in "page generation time" type scripts.

$db->row
MySQL 3, MySQL 4, MS SQL, PostgreSQL, Oracle, ODBC
An array holding a single result row from a query. Cannot be used until the sql_fetchrow function has been called, so it is better to use that function to store the row in your own variable instead.

$db->rowset
MySQL 3, MySQL 4, MS SQL, PostgreSQL, Oracle, ODBC
Use seems to vary depending on database type, but mainly an array used to hold a single result row from a query. The row is then added to an array of all the result rows. It would be better to use the sql_fetchrowset function instead.

$db->query_result
MySQL 3, MySQL 4, PostgreSQL, Oracle, ODBC
Stores a resource link identifier associated with the result of an SQL query. Can be used in functions that access the result of a query or check for a valid result. See also: $db->result.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 24th 2003, 3:04 am

$db->in_transaction
MySQL 4, MS SQL, Oracle, PostgreSQL
Used to indicate if an SQL transaction is in progress.

$db->next_id
MS Access, MS SQL, MS SQL (ODBC), ODBC
Stores the ID number generated by a query. Examples would be user ID numbers. topic ID numbers, etc. This variable should not be used outside of the DBAL. Use the sql_nextid function instead.

$db->result
MS Access, MS SQL, MS SQL (ODBC)
Stores a resource link identifier associated with the result of an SQL query. Can be used in functions that access the result of a query or check for a valid result. See also: $db->query_result.

$db->current_row
MS Access, MS SQL (ODBC)
Holds the row number of the current row. Can be used in functions that change this number (e.g., for skipping or repeating rows).

$db->field_names
MS Access, MS SQL (ODBC)
An array storing the names of fields contained in a result. Can be used for displaying this information, among other things. There are DBAL functions that could also be used for this purpose.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 24th 2003, 10:45 pm

$db->field_types
MS Access, MS SQL (ODBC)
An array storing the types of fields contained in a result. Can be used for displaying this information, among other things. There are DBAL functions that could also be used for this purpose.

$db->num_rows
MS Access, MS SQL (ODBC)
Stores the number of rows returned or affected by a query. Also used to determine the number of of rows to return when LIMIT is used in a query. This variable should not be used outside of the DBAL. Use the sql_numrows() function instead.

$db->result_rowset
MS Access, MS SQL (ODBC)
Stores a set of result rows from a query. Could be used to process an entire set of results at once. This variable should not be used outside of the DBAL. Use the sql_fetchrowset() function instead.

$db->result_ids
MS Access
Unknown. It does not appear to be used anywhere in phpBB.

$db->limit_offset
MS SQL
Used to store the offset that should be used to limit results used. Use is not recommended.
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Postby Thoul » September 24th 2003, 10:47 pm

$db->query_limit_success
MS SQL
Unknown. It does not appear to be used anywhere in phpBB.

$db->last_query_text
Oracle
Unknown.

$db->query_numrows
ODBC
Unknown. It does not appear to be used anywhere in phpBB.

$db->query_resultset
ODBC
Unknown. It does not appear to be used anywhere in phpBB.

$db->row_index
ODBC
Holds the row number of the current row. Can be used in functions that change this number (e.g., for skipping or repeating rows).
User avatar
Thoul
Admin/Webmaster
 
Posts: 18551
Joined: July 30th 2002, 11:30 am
Location: USA

Next

Return to phpBB 2: Technical Reference

Who is online

Users browsing this forum: No registered users and 0 guests