Differences

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

punbb13:database_helpers 2010/04/09 08:49 punbb13:database_helpers 2020/02/06 11:04 current
Line 6: Line 6:
  * 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.   * 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]].   * 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. The database layer is able to add the prefix to table names automatically (the default behavior). It's recommended to use prefix (do not change the default value of ''$no_prefix'').+  * 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 ===== ===== 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:
Line 23: Line 23:
Let's discuss how to use the query builder. Let's discuss how to use the query builder.
-===== Data Manipulation in query builder ====== +===== Query Builder: Data Manipulation ======
- +
-First consider the DML (Data Manipulation Language) part of the PunBB database layer class.+
==== SELECT ==== ==== SELECT ====
Line 136: Line 134:
$forum_db->query_build($query) or error(__FILE__, __LINE__);</code> $forum_db->query_build($query) or error(__FILE__, __LINE__);</code>
-===== Data Definition in query builder ======+===== Data Definition ======
Now let's consider the DDL (Data Definition Language) part of the PunBB database layer class. Now let's consider the DDL (Data Definition Language) part of the PunBB database layer class.
Line 201: Line 199:
==== Fields ==== ==== Fields ====
-FIXME+ 
 +<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 ==== ==== Indexes ====
-FIXME 
 +<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