PunBB 1.3 database helpers

PunBB has an implementation of the database layer class. It takes into account the forum configuration and works with different databases. On include/common.php inclusion an instance of this database layer named $forum_db is being created in global scope to provide database helpers.

Please remember the following when use PunBB database layer:

• Potentially harmful data is escaped using $forum_db→escape(). Expected integer values should be forced into integer form using intval() before being used in a query.
• Use the SQL 2003 style explicit join notation.
• Database helper functions have an optional parameter $no_prefix. The prefix of table names is being selected during the forum installation and is available in the database layer class ($forum_db→prefix). The database layer is able to add the prefix to table names automatically (the default behavior). It's recommended not to change the default value of $no_prefix.

• Direct query (not recommended). You can simply write an SQL-statement and execute it.

 $result = $forum_db->query('SELECT * FROM '.$forum_db->prefix.'topics WHERE id = 10');

Be sure, that your SQL code is cross-compatible with all database engines supported by PunBB.

• Using query builder (recommended). You may transparently build database queries. All the specific of database engines and database structure will automatically be taken in account. Example of usage:

$query = array(
   'SELECT'  => '*',
   'FROM'    => 'topics',
   'WHERE'   => 'id = 10'
);
$result = $forum_db->query_build($query);

Let's discuss how to use the query builder.

The following entries can be used in SELECT queries: FROM, JOINS, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

// Taken from include/search_idx.php
$query = array(
   'SELECT'   => 'word_id',            // field names
   'FROM'     => 'search_matches',     // table name
   'WHERE'    => 'word_id IN('.implode(',', $word_ids).')',
   'GROUP BY' => 'word_id, subject_match',
   'HAVING'   => 'COUNT(word_id)=1'
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

// Taken from admin/reindex.php
$query = array(
   'SELECT'   => 'p.id, p.message, t.id, t.subject, t.first_post_id',
   'FROM'     => 'posts AS p',
   'JOINS'    => array(
      array(
         'INNER JOIN' => 'topics AS t',
         'ON'         => 't.id=p.topic_id'
      )
   ),
   'WHERE'    => 'p.id>='.$start_at,
   'ORDER BY' => 'p.id',
   'LIMIT'    => $per_page
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

For example, we have this query:

$query = array(
   'SELECT'  => 't.id, t.poster, t.subject, t.posted',
   'FROM'    => 'topics AS t',
   'WHERE'   => 't.forum_id = 1'
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

To know how many rows this query returns, use this:

  $forum_db->num_rows($result);

To fetch the current row into an associative array:

  $data = $forum_db->fetch_assoc($result);
  //An example of getting topic_id
  $topic_id = $data['id'];

To fetch the current row into a numeric array:

  $data = $forum_db->fetch_row($result);
  //An example of getting topic_id
  $topic_id = $data[0];

To fetch only some values from the current row:

  //This code will fetch only the topic id and the topic subject
  list($id,, $subject,) = $forum_db->fetch_row($result);

To process all rows in a set you can use this code:

  while ($cur_row = $forum_db->fetch_assoc($result))
  {
    //Actions with $cur_row
  }

The following entries allowed in INSERT queries: INTO, VALUES.

// Taken from admin/categories.php
$query = array(
   'INSERT'   => 'cat_name, disp_position',  // field names
   'INTO'     => 'categories',               // table name
   'VALUES'   => '\''.$forum_db->escape($new_cat_name).'\', '.$new_cat_pos  // inserting values
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

The following entries allowed in UPDATE queries: SET, WHERE.

// Taken from admin/censoring.php
$query = array(
   'UPDATE'   => 'censoring',  // table name
   'SET'      => 'search_for=\''.$forum_db->escape($search_for).'\', replace_with=\''.$forum_db->escape($replace_with).'\'',
   'WHERE'    => 'id='.$id
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

Only WHERE entry is allowed for DELETE queries.

// Taken from admin/bans.php
$query = array(
   'DELETE'   => 'bans',  // table name
   'WHERE'    => 'id='.$ban_id
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

Now let's consider the DDL (Data Definition Language) part of the PunBB database layer class.

function table_exists($table_name, $no_prefix = false)

This function returns true if the table with $table_name name exists and false otherwise.

function create_table($table_name, $schema, $no_prefix = false)

Takes either two or three parameters and creates a database table. The first parameter is the name of the table to be added. The second is an array of schema for the table itself (see the explanation and example below). The third is the optional parameter $no_prefix.

function drop_table($table_name, $no_prefix = false)

Deletes the table that has the $table_name name.

The table structure is described by an array which accepts following parameters:

• FIELDS: Fields is an array, with the keys being field names and the values being an array of data about the field. The field data contains a key datatype that should be mapped to one of the acceptable field types, a key allow_null which should be set to either true or false, and an optional key default which specifies a default value for the field.
• PRIMARY KEY: An array whose elements are field names from FIELDS.
• UNIQUE KEYS: An array with keys that act as the name of unique indexes and values that are arrays of field names (again, the field names should match field names in FIELDS).
• INDEXES: An array with keys that act as the name of indexes and values that are arrays of field names (again, the field names should match field names in FIELDS).
• ENGINE: This element is optional and is only used by MySQL. It specifies the database engine that MySQL should use in creating this table.

When adding a column or creating a new table, you must specify the data type for each field. The following data-types are acceptable to use in the helper functions (create_table() and add_field()). These data-types are guaranteed to be translated properly when used.

• SERIAL (translates to INT(10) UNSIGNED AUTO_INCREMENT in MySQL)
• TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT (##) UNSIGNED (UNSIGNED and (##) are optional)
• VARCHAR/CHAR
• TINYTEXT/MEDIUMTEXT/TEXT/LONGTEXT
• FLOAT
• DOUBLE

Note: Don't use $query in extension code that has global scope as this variable is used in the core and might interfere with it. Rather use a specific variable for your script to avoid errors.

//Taken from install.php
$schema = array(
   'FIELDS'      => array(
       'id'         => array(
            'datatype'     => 'SERIAL',
            'allow_null'   => false
         ),
         'cat_name'        => array(
            'datatype'     => 'VARCHAR(80)',
            'allow_null'   => false,
            'default'      => '\'New Category\''
         ),
         'disp_position'   => array(
            'datatype'     => 'INT(10)',
            'allow_null'   => false,
            'default'      => '0'
         )
      ),
      'PRIMARY KEY'   => array('id')
   );

After creating the schema, a method of the database class is used:

$forum_db->create_table('categories', $schema);

function field_exists($table_name, $field_name, $no_prefix = false)

Checks if the field $field_name is present in the table $table_name.

function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)

Adds a field to the $table_name table. One can specify the name of an existent field, $after_field, to insert after it the field to be added. The meaning of the other parameters is clear from its names.

function alter_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)

Modifies the $field_name field. The meaning of parameters is the same as above.

function drop_field($table_name, $field_name, $no_prefix = false)

Removes the $field_name field from the $table_name table.

Here are examples of the fields manipulation.

// Taken from admin/db_update.php
$forum_db->alter_field('posts', 'poster_ip', 'VARCHAR(39)', true);
$forum_db->alter_field('users', 'registration_ip', 'VARCHAR(39)', false, '0.0.0.0');

// Taken from the pun_pm extension
// Install section
if (!$forum_db->field_exists('users', 'pun_pm_new_messages'))
   $forum_db->add_field('users', 'pun_pm_new_messages', 'INT(10)', true);
 
// Uninstall section
$forum_db->drop_field('users', 'pun_pm_new_messages');

function index_exists($table_name, $index_name, $no_prefix = false)

Checks if the index $index_name is present in the table $table_name.

function add_index($table_name, $index_name, $index_fields, $unique = false, $no_prefix = false)

Adds an index to the $table_name table. $index_fields is the array of fields to be added to the index.

function drop_index($table_name, $index_name, $no_prefix = false)

Removes the $index_name index from the $table_name table.

Here are examples of the indexes manipulation.

// Taken from admin/db_update.php
if (!$forum_db->index_exists('online', 'user_id_ident_idx'))
{
   ...
   $forum_db->add_index('online', 'user_id_ident_idx', array('user_id', 'ident'), true);
   ...
}
 
$forum_db->drop_index('online', 'user_id_idx');