Class DatabaseMysql

Description

MySQL database

This implements access to the MySQL database.

Located in /program/lib/database/mysql.class.php (line 58)


	
			
Variable Summary
 resource $db_link
 string $db_name
 string $db_password
 string $db_server
 string $db_type
 string $db_username
 bool $debug
 integer $errno
 string $error
 string $prefix
 integer $query_counter
Method Summary
 void DatabaseMysql (string $prefix, [bool $debug = FALSE])
 bool close ()
 bool|string column_definition (array $fielddef)
 concatenation concat (string $string1, string $string2)
 bool connect (string $db_server, string $db_username, string $db_password, string $db_name)
 int|bool create_table (array $tabledef)
 string|bool create_table_sql (array $tabledef)
 int|bool drop_table (string $tablename)
 bool dump (string &$data, [bool $drop = TRUE], [mixed $tables = ''])
 string|bool escape (string $unescaped_string)
 int|bool exec (string $sql)
 int|bool last_insert_id ([string $table_name = ''], [string $field_name = ''])
 object|bool query (string $sql, [int $limit = ''], [int $offset = ''])
 bool table_exists (string $tablename)
Variables
resource $db_link (line 78)
  • var: database link identifier
string $db_name (line 72)
  • var: database to use, e.g. 'was'
string $db_password (line 69)
  • var: part of credentials for database access
string $db_server (line 63)
  • var: database server, e.g. 'localhost' or 'db.example.com:3306'
string $db_type (line 60)
  • var: database type, e.g. 'mysql'
string $db_username (line 66)
  • var: part of credentials for database access
bool $debug (line 90)
  • var: if true switch debugging on
integer $errno (line 84)
  • var: the error number generated by the latest mysql command
string $error (line 87)
  • var: the error message generated by the latest mysql command
string $prefix (line 75)
  • var: table name prefix, e.g. 'was_'
integer $query_counter (line 81)
  • var: the number of queries executed sofar
Methods
Constructor DatabaseMysql (line 100)

initialise query counter and other variables, store the table prefix

void DatabaseMysql (string $prefix, [bool $debug = FALSE])
  • string $prefix: table name prefix, e.g. 'was_'
  • bool $debug: if TRUE extra information is displayed (handy for debugging the code)
close (line 143)

close the connection to the database

  • return: FALSE if failed, TRUE otherwise
bool close ()
column_definition (line 526)

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:

  • name: the name of the field (required)
  • type: the generic type of the field (see table below for supported types) (required)
  • length: the size of a numeric field or the length of a textual field
  • decimals: the number of decimals in real numbers (implies length)
  • unsigned: used for numeric fields that don't need negative values (see note 5 below)
  • notnull: if true, the field is not allowed/able to contain the NULL value
  • default: the default value for the field
  • enum_values: an array with allowable values for the enum field
  • comment: a string that can be used to document the field definition
The generic field types are mapped to actual MySQL data types via a lookup table. Most types have a number of boolean flags which indicate how the field definition must be interpreted. Entries with an empty type are considered special cases.

  • len = 1: look for a length parameter and use it if it is defined.
  • dec = 1: look for a decimals parameter and use it if it is defined. Implies len.
  • unsigned = 1: look for an unsigned parameter and use it if it is defined and true (see note 5 below).
  • default = 1: look for a default parameter, and use it if it is defined.
  • quote = 1: if a default is allowed AND defined, use single quotes + escaped string.
Note 1: a 1 in the table means that the field type _allows_ the corresponding parameter, and 0 means that this parameter is NOT allowed. It doesn't say anything about parameters being _required_ (e.g. a varchar must have a length). It is the responsability of the author of the field definition to provide all necessary parameters.

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.

  • return: FALSE on failure or a MySQL-specific string defining the column
  • todo: should we allow both int and integer?
  • todo: 'enum' type equivalent with varchar, enum_values[] array is not used at all, only as a form of documentation
bool|string column_definition (array $fielddef)
  • array $fielddef: an array that describes a field in a generic way
concat (line 231)

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:

    ... $sql = 'UPDATE {$DB->prefix}table '.        'SET message = '.$DB->concat('message',"'".$DB->escape("addition to message\n")."'")." ".        'WHERE table_id = '.$some_id; $DB->exec($sql); ...
and that's exactly the kind of hairy code I'd like to stay away from, because of the necessary delicate balancing of quotes and the amount of thought it requires to get a query right. Obviously it is much easier (and less quote-error prone and almost elegant) to do something like this:
        $record = db_select('tablename','message',array('table_id' => $some_id));     $value = $record['message'] . "addition to message\n";     db_update('tablename',array('message' => $value),array('table_id' => $some_id));

Note that 'Standard-SQL' would yield almost as much trouble with concatenation, even if it were possible to use in MySQL, e.g.:

    ... $sql = 'UPDATE {$DB->prefix}table '.        "SET message = message || '".$DB->escape("addition to message\n")."' ".        'WHERE table_id = '.$some_id; $DB->exec($sql); ...
No matter what: this is ugly. The reason I still want to use this kind of code is that (much) more expensive to use series of SELECT / UPDATE statements with concatenation in PHP, not to mention the fact that using two separate SQL-statements introduces race conditions, so there. Alas this is database specific.

Note that some of the quote-hell can be dealt with via db_escape_and_quote():

    $sql = 'UPDATE {$DB->prefix}table '.        'SET message = '.$DB->concat('message',db_escape_and_quote("addition to message\n")).' '.        'WHERE table_id = '.$some_id; $DB->exec($sql);

  • return: of input strings (database-specific)
  • todo: perhaps extend this function to accept more than 2 strings?
concatenation concat (string $string1, string $string2)
  • string $string1: contains a quoted/escaped string, a fieldname or other expression
  • string $string2: contains a quoted/escaped string, a fieldname or other expression
connect (line 119)

connect to the database server and open the database

  • return: FALSE if failed, TRUE otherwise
  • todo: weigh pros and cons of persistent database connections, perhaps add as config option?
bool connect (string $db_server, string $db_username, string $db_password, string $db_name)
  • string $db_server: database server, e.g. 'localhost' or 'db.example.com:3306'
  • string $db_username: part of credentials
  • string $db_password: part of credentials
  • string $db_name: database to use, e.g. 'was'
create_table (line 396)

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.

  • return: FALSE on failure or otherwise the # of affected rows (always 0)
  • todo: document correct link for documentation of generic table definition 'tabledefs.php'
int|bool create_table (array $tabledef)
  • array $tabledef: a generic table definition (not database-specific)
create_table_sql (line 415)

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.

  • return: FALSE on failure or otherwise a string with a CREATE TABLE statement
  • todo: document correct link for documentation of generic table definition 'tabledefs.php'
  • todo: find a way to deal with the enum values: where do we keep them? Or do we keep them at all?
string|bool create_table_sql (array $tabledef)
  • array $tabledef: a generic table definition (not database-specific)
drop_table (line 360)

unconditionally drop the specified table

  • return: FALSE on failure or otherwise the # of affected rows (always 0)
int|bool drop_table (string $tablename)
  • string $tablename: name of the table to drop (prefix will be added automatically)
dump (line 653)

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.

  • return: TRUE on success and data in &$dump, FALSE on failure and &$dump undefined
bool dump (string &$data, [bool $drop = TRUE], [mixed $tables = ''])
  • string &$data: receives the dump of the tables
  • bool $drop: if TRUE add code to drop the table before the data definition
  • mixed $tables: array with names of tables to dump, empty (string, array) means all our tables
escape (line 156)

escape special characters in string

  • return: FALSE on failure or the escaped string on success
string|bool escape (string $unescaped_string)
  • string $unescaped_string: the string to escape
exec (line 249)

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.

  • return: FALSE on failure or otherwise the # of affected rows
int|bool exec (string $sql)
  • string $sql: valid SQL statement
last_insert_id (line 297)

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.

  • return: FALSE on failure, 0 if no id was generated or the id that was generated
int|bool last_insert_id ([string $table_name = ''], [string $field_name = ''])
  • string $table_name: (optional) tablename (unused in MySQL)
  • string $field_name: (optional) fieldname (unused in MySQL)
query (line 328)

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.

  • return: FALSE on failure or otherwise a DatabaseMysqlResult object
object|bool query (string $sql, [int $limit = ''], [int $offset = ''])
  • int $offset: optional number of records to skip; = 0 means start with the first
  • string $sql: valid SQL statement
  • int $limit: optional limitation of the number of records returned
table_exists (line 371)

see if the named table exists

  • return: TRUE if the table exists, FALSE otherwise
bool table_exists (string $tablename)
  • string $tablename: name of the table to check (prefix will be added automatically)

Documentation generated on Wed, 9 Mar 2011 14:05:30 +0100 by phpDocumentor 1.4.0