MySQL database
This implements access to the MySQL database.
Located in /program/lib/database/mysql.class.php (line 58)
initialise query counter and other variables, store the table prefix
close the connection to the database
convert a fielddef array to a MySQL specific column definition
this creates a MySQL specific column definition based on a generic field description. The following keywords are recognised:
Note 2: enum_values are not used at this time; an enum-field simply maps to a varchar field and that's it. Adding the enum_values to a data definition does help to document the purpose of the field. The reason for not (yet) implementing enums in full is the issues associated with the translations in the UI. Furthermore, using native enums in MySQL is a royal PITA. For now the application should know what it is doing when using and updating enums in a table.
Note 3: at this time it is not possible to set the default value of a text-type field to the string consisting of the letters N, U, L and L: that caseinsensitive string is always interpreted as the NULL-value in the database, ie. it yields "DEFAULT NULL" and not "DEFAULT 'NULL'" or "DEFAULT 'Null'" or "DEFAULT 'null'".
Note 4: even though comments are not yet stored in the MySQL database, the corresponding COMMENT-clauses are generated, if only for documentation/debugging purposes. These clauses are correctly parsed by MySQL but they are subsequently discarded.
Note 5: As of version 2009051401 (0.0.5) the parameter 'unsigned' is deprecated. This is because even though MySQL implements an unsigned attribute for numeric fields, other RDBMSs might not. Therefore we stay away from this MySQL-specific construct as much as possible. Also, the translation from the 'serial' fieldrtype no longer adds the unsigned attribute to the actual MySQL definition.
helper function for string concatenation in sql statements
From http://troels.arvin.dk/db/rdbms/#functions-concat:
SQL Standard: Core feature ID E021-07: Concatenating two strings is done with the || operator:
string1 || string2
If at least one operand is NULL, then the result is NULL.
MySQL: Badly breaks the standard by redefining || to mean OR. Offers instead a function, CONCAT(string, string), which accepts two or more arguments.
In order to try and stay as database-independent as possible without losing this concatenation feature, we have to resort to a database-specific function. Aaargggghh!
Typical use of this function:
Note that 'Standard-SQL' would yield almost as much trouble with concatenation, even if it were possible to use in MySQL, e.g.:
Note that some of the quote-hell can be dealt with via db_escape_and_quote():
connect to the database server and open the database
create a table via a generic (non-MySQL-specific) table definition
this executes a MySQL-specific CREATE TABLE statement based on a generic table definition. The actual work is done in create_table_sql(), which makes it possible to see the result of converting a generic definition to an actual table; very handy while debugging.
create the MySQL-specific SQL statement to create a table via a generic table definition
this creates a MySQL-specific CREATE TABLE statement from a generic table definition. See tabledefs.php for more information about the format of this generic table definition.
unconditionally drop the specified table
make a text dump of our tables in the database suitable for backup purposes
This creates a text dump of the selected tables in parameter $data. If $tables is empty we dump all the tables in the database that start with 'our' prefix (there could be other websites using the same table with another prefix, we won't dump those). If $tables is an array, it is assumed to be an array with table names without our prefix. In this case we will prepend the prefix. If parameter $drop is TRUE, we add code to drop the table from the database (if it exists) before we recreate it.
If there were no errors, we return TRUE (and $data contains the dump). If errors were encountered, we return FALSE and $this->errno and $this->error can tell the caller what happened. If there were errors, $data is undefined.
Strategy is as follows. First we make a valid list of tables to dump, either by asking the database for a list of tables LIKE "{$prefix}%" or by manipulating and validating the array $tables that was provided by the caller.
Subsequently we let the database generate a CREATE TABLE statement and then we step through the data (if any) and add it to $data.
Note MySQL is quite liberal in what it accepts as field values. However, I try to generate INSERT INTO-statements as clean as possible by NOT quoting numeric values. HTH. It still is a MySQL-specific dump, though. You cannot simply use the result 'as-is' to migrate to another database.
escape special characters in string
execute an action query and return the number of affected rows
this method should be used to exectute SQL-statements that do NOT return a result set, use query() for that. This method works well for INSERTs, DELETEs and UPDATEs. If there is an error this method returns FALSE. Otherwise it returns the number of affected lines. Note that there is a difference between 0 affected lines and FALSE.
retrieve the most recent automatically inserted id ('auto_increment')
this method returns the id that was automatically generated in the previous INSERT-query or 0 if the previous query was not an INSERT query.
Note: as per the MySQL manual this function returns an int and not a bigint. If the auto_increment field is a bigint, this method returns an incorrect result. There is a work-around (e.g. SELECT LAST_INSERT_ID()) but this is not the way it works in Website@School; all id's are simple int's and not bigint's, so there is no need to generate yet another query after every insert.
Note that this method can be called with a table name and a field name. This is a hook for future expansion; this MySQL-driver does not actually use it. However, it is handy to always call this method with table and field name to make adding a new database driver easier.
execute a select query and return a result set
this method should be used to exectute SQL-statements that do return a result set: SELECT, SHOW, EXPLAIN and DESCRIBE. Use exec() for action queries . If there is an error this method returns FALSE. Otherwise it returns a result set in the form of a DatabaseMysqlResult object.
if parameters $limit and $offset are set the result set contains at most $limit rows, starting at offset $offset. it is OK to specify just $limit; $offset is taken into account only when $limit is specified too. Note that different databases have a different syntax for limiting the number of returned records. MySQL supports both 'LIMIT limit OFFSET offset' (which we use here) and 'LIMIT offset,limit'.
the LIMIT-clause is blindly appended to the SQL-statement; it is up to the caller to decide wheter specifying a limit and an offset make sense or not.
see if the named table exists
Documentation generated on Wed, 9 Mar 2011 14:05:30 +0100 by phpDocumentor 1.4.0