Differences

This shows you the differences between the selected revision and the current version of the page.

punbb13:database_helpers 2010/04/09 07:26 punbb13:database_helpers 2020/02/06 11:04 current
Line 1: Line 1:
-====== Database helpers ======+====== 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. 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. On ''include/common.php'' inclusion an instance of this database layer named ''$forum_db'' is being created in global scope to provide database helpers.
-==== Performing a query ====+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 [[http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join|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''. 
 + 
 +===== Performing a query =====
  * **Direct query** (not recommended). You can simply write an SQL-statement and execute it.   * **Direct query** (not recommended). You can simply write an SQL-statement and execute it.
-<code php> $result = $forum_db->query('SELECT * FROM topics WHERE id = 10');</code>+<code php> $result = $forum_db->query('SELECT * FROM '.$forum_db->prefix.'topics WHERE id = 10');</code>
Be sure, that your SQL code is cross-compatible with all database engines supported by PunBB. 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:+  * **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:
<code php>$query = array( <code php>$query = array(
  'SELECT'  => '*',   'SELECT'  => '*',
Line 15: Line 20:
$result = $forum_db->query_build($query); $result = $forum_db->query_build($query);
</code> </code>
-See [[query builder]] page for details. 
-===== Data Manipulation with query builder ======+Let's discuss how to use the query builder.
-Consider the DML (Data Manipulation Language) part of the PunBB database layer class.+===== Query Builder: Data Manipulation ======
==== SELECT ==== ==== SELECT ====
Line 54: Line 58:
For example, we have this query: For example, we have this query:
<code php> <code php>
-  $query = array( +$query = array( 
-   'SELECT' => 't.id, t.poster, t.subject, t.posted', +   'SELECT' => 't.id, t.poster, t.subject, t.posted', 
-   'FROM'   => 'topics AS t', +   'FROM'   => 'topics AS t', 
-   'WHERE' => 't.forum_id = 1' +   'WHERE'   => 't.forum_id = 1' 
- ); +); 
- $result = $forum_db->query_build($query) or error(__FILE__, __LINE__);+$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);
</code> </code>
-  * To know how many rows this query returns, use this:+ 
 +To know how many rows this query returns, use this:
<code php> <code php>
  $forum_db->num_rows($result);   $forum_db->num_rows($result);
</code> </code>
-  * To fetch the current row to associative array:+ 
 +To fetch the current row into an associative array:
<code php> <code php>
  $data = $forum_db->fetch_assoc($result);   $data = $forum_db->fetch_assoc($result);
Line 71: Line 77:
  $topic_id = $data['id'];   $topic_id = $data['id'];
</code> </code>
-  * To fetch the current row to numeric array:+ 
 +To fetch the current row into a numeric array:
<code php> <code php>
  $data = $forum_db->fetch_row($result);   $data = $forum_db->fetch_row($result);
Line 77: Line 84:
  $topic_id = $data[0];   $topic_id = $data[0];
</code> </code>
-  * To fetch only some values from the current row:+ 
 +To fetch only some values from the current row:
<code php> <code php>
  //This code will fetch only the topic id and the topic subject   //This code will fetch only the topic id and the topic subject
  list($id,, $subject,) = $forum_db->fetch_row($result);   list($id,, $subject,) = $forum_db->fetch_row($result);
</code> </code>
-  * To process all rows in a set you can use this code:+ 
 +To process all rows in a set you can use this code:
<code php> <code php>
  while ($cur_row = $forum_db->fetch_assoc($result))   while ($cur_row = $forum_db->fetch_assoc($result))
Line 116: Line 125:
==== DELETE ==== ==== DELETE ====
-<code php>Only ''WHERE'' entry is allowed for ''DELETE'' queries. +Only ''WHERE'' entry is allowed for ''DELETE'' queries.
-// Taken from admin/bans.php+<code php>// Taken from admin/bans.php
$query = array( $query = array(
  'DELETE'  => 'bans',  // table name   'DELETE'  => 'bans',  // table name
Line 124: Line 133:
); );
$forum_db->query_build($query) or error(__FILE__, __LINE__);</code> $forum_db->query_build($query) or error(__FILE__, __LINE__);</code>
 +
 +===== Data Definition ======
 +
 +Now let's consider the DDL (Data Definition Language) part of the PunBB database layer class.
 +
 +==== Tables ====
 +
 +<code php>function table_exists($table_name, $no_prefix = false)</code>
 +
 +This function returns ''true'' if the table with ''$table_name'' name exists and ''false'' otherwise.
 +
 +<code php>function create_table($table_name, $schema, $no_prefix = false)</code>
 +
 +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''.
 +
 +<code php>function drop_table($table_name, $no_prefix = false)</code>
 +
 +Deletes the table that has the ''$table_name'' name.
 +
 +=== Creating a table ===
 +
 +==Table structure==
 +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.
 +==Acceptable field types==
 +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.
 +
 +<PHP>
 +//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')
 +  );
 +</PHP>
 +
 +After creating the schema, a method of the database class is used:
 +<PHP>$forum_db->create_table('categories', $schema);</PHP>
 +
 +==== Fields ====
 +
 +<code php>function field_exists($table_name, $field_name, $no_prefix = false)</code>
 +
 +Checks if the field ''$field_name'' is present in the table ''$table_name''.
 +
 +<code php>function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)</code>
 +
 +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.
 +
 +<code php>function alter_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)</code>
 +
 +Modifies the ''$field_name'' field. The meaning of parameters is the same as above.
 +
 +<code php>function drop_field($table_name, $field_name, $no_prefix = false)</code>
 +
 +Removes the ''$field_name'' field from the ''$table_name'' table.
 +
 +Here are examples of the fields manipulation.
 +
 +<code php>// 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');</code>
 +
 +<code php>// 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');</code>
 +
 +==== Indexes ====
 +
 +<code php>function index_exists($table_name, $index_name, $no_prefix = false)</code>
 +
 +Checks if the index ''$index_name'' is present in the table ''$table_name''.
 +
 +<code php>function add_index($table_name, $index_name, $index_fields, $unique = false, $no_prefix = false)</code>
 +
 +Adds an index to the ''$table_name'' table. ''$index_fields'' is the array of fields to be added to the index.
 +
 +<code php>function drop_index($table_name, $index_name, $no_prefix = false)</code>
 +
 +Removes the ''$index_name'' index from the ''$table_name'' table.
 +
 +Here are examples of the indexes manipulation.
 +
 +<code php>// 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');</code>

Personal Tools