Differences
This shows you the differences between the selected revision and the current version of the page.
| punbb13:database_helpers 2010/04/09 08:52 | 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 and is available 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 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 ===== | ||
| 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> | ||
