Differences

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

punbb13:query_builder 2009/02/20 12:42 punbb13:query_builder 2020/02/06 11:04 current
Line 1: Line 1:
-=========== +Moved to [[punbb13:database helpers]]
-Please note that this page is still under development FIXME. +
-===========  +
- +
-====== Query Builder ====== +
-PunBB supports several database systems, therefore when you are wanting to interact with a database, you have to make sure that your SQL queries are compatible with all database systems, or at least the one you are planning to use. +
- +
-===== SQL Specifics ===== +
-Before creating or modifying a query, you have to make sure that following things are taken in account: +
- +
-  * 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]]. +
- +
-Take advantage of //$forum_db->query_build//, which allows to easily create/extend cross-database compatible queries. In rare cases where database specific queries are needed, every effort should be made to provide alternative queries for other supported databases. +
- +
-===== Query structure ===== +
-== Query Basics == +
-The query structure is actually 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** +
- +
-__To developers__: Don't use __$query__ in extensions 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 query, a method of the database class is used: +
-<PHP>$forum_db->create_table('categories', $schema);</PHP> +
- +
-The create_table function takes either two or three parameters. The first parameter is the name of the table to be added. The second is an array of schema for the table itself. The third parameter, which is optional, specifies whether or not to add the database prefix to the table name. If the third parameter is not specified or is set to false, the prefix will be prepended to the table name. If it is specified as true, the prefix will not be prepended. +

Personal Tools