Class DatabaseMysqli

Description

MySQL database

This implements access to the MySQL database.

Located in /program/lib/database/mysqli.class.php (line 60)


	
			
Variable Summary
 mixed $charset
 mixed $collation
 resource $db_link
 string $db_name
 string $db_password
 string $db_server
 string $db_type
 string $db_username
 string $db_version
 bool $debug
 mixed $engine
 integer $errno
 string $error
 string $prefix
 integer $query_counter
Method Summary
 void DatabaseMysqli (string $prefix, [bool $debug = FALSE])
 void check_engine ()
 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 = ''])
 string mysqli_utf8mb3 (string $utf8str)
 int|bool mysqli_utf8_support (resource $db_link)
 object|bool query (string $sql, [int $limit = ''], [int $offset = ''])
 bool table_exists (string $tablename)
Variables
mixed $charset = NULL (line 104)
  • var: $charset is the MySQL character set to use (e.g. utf8 or utf8mb4)
mixed $collation = NULL (line 107)
  • var: $collation is the MySQL collation to use (e.g. utf8_unicode_ci or utf8mb4_unicode_ci
resource $db_link (line 80)
  • var: $db_link is the database link identifier
string $db_name (line 74)
  • var: $dn_name is the name of the database to use, e.g. 'was'
string $db_password (line 71)
  • var: $db_password is part of credentials for database access
string $db_server (line 65)
  • var: $db_server is the name of the database server, e.g. 'localhost' or 'db.example.com:3306'
string $db_type (line 62)
  • var: $db_type is the database type, always 'mysqli' for this class
string $db_username (line 68)
  • var: $db_username is part of credentials for database access
string $db_version (line 83)
  • var: $db_version is the database version string. e.g. '5.1.32' or '4.0.23a' or FALSE if unavailable
bool $debug (line 95)
  • var: $debug if TRUE switch debugging on
mixed $engine = NULL (line 101)
  • var: $engine is the MySQL engine to use (e.g. MyISAM or InnoDB)
integer $errno (line 89)
  • var: $errno is the error number generated by the latest mysqli command
string $error (line 92)
  • var: $error is the error message generated by the latest mysqli command
string $prefix (line 77)
  • var: $prefix is the table name prefix, e.g. 'was_'
integer $query_counter (line 86)
  • var: $query_counter is the number of queries executed sofar
int $utf8_support (line 98)
  • var: $utf8_support is the level of UTF-8 support: (none), 3 (limited), 4 (full, but with a quirky name)
Methods
Constructor DatabaseMysqli (line 116)

initialise query counter and other variables, store the table prefix

void DatabaseMysqli (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)
check_engine (line 1005)

determine the database engine (and charset and collation) to use for new tables

this routine attempts to find the engine that is/was used to create existing tables with the prefix for this installation. As a side effect we also try to determine the charset and the collation. This is all based on the output of SHOW TABLE STATUS LIKE '{$prefix}%';

If we get an empty result set, it means there is no table yet from which we can learn the engine/charset/collation. In that case we want to use the database default engine with an appropriate charset/collation. We indicate this to the caller by returning FALSE.

If, however, we have at least one existing table with prefix $prefix, the SHOW TABLE STATUS query returns at least 1 result row. From this row we can use the column 'Engine' to find out which engine was used for that table. From the column 'Collation' we can extract the collation used. Once we have that, we can also (re-)construct the charset (because a collation is always based on the charset, e.g. 'utf8' and 'utf8_unicode_ci') by looking at the string upto the first '_'.

If the SHOW TABLE STATUS query does return a result but there is no field 'Engine' and possibly also no field 'Collation', we simply leave those parameters blank. This is based on the assumption that IF the field 'Engine' does not exist, we are dealing with a very old version of MySQL < 4.1.1 which has no support for utf8 anyway.

So, usage of this routine in create_table_sql() is something like this:

...
if ($this->check_engine()) {
    $sql .= (empty($this->engine)) ? '' : ' ENGINE='.$this->engine;
    $sql .= (empty($this->charset)) ? '' : ' DEFAULT CHARSET='.$this->charset;
    $sql .= (empty($this->collation)) ? '' : ' COLLATE='.$this->collation;
} else {
    switch($this->utf8_support) {
    case 3: $sql .= ' DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'; break;
    case 4: $sql .= ' DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'; break;
    }
}
...

The reason to go through all this trouble is as follows. Suppose we have installed W@S in a database 5.1.0 with MyISAM as the default engine and an appropriate charset and collation (utf8_unicode_ci). That implies that all core tables are MyISAM / utf8 / utf8_unicode_ci. OK. Now this database is moved to a newer version, say 5.5.39, with InnoDB as default. Now add a module with a new table with a foreign key on one of the core tables, say was_users. Boom! Error 150 and no table is created. Aaarghhh.

This heuristic workaround is to look at existing tables in the database with 'our' prefix and copy the engine/charset/collation from there. That means that at least all tables, old and new, have the same engine, etc. (The same logic applies to a migration from a pre 5.5.3 database with only utf8 to a newer one where perhaps utf8mb4 would be default.

For a new installation it doesn't matter: the first table that gets installed is installed with the default engine. The second table looks up the properties of the first (in this routine check_engine()) and uses those. The answers are cached in the $DB-object so we only have to do this twice for a new installation: the first time we leave here in step 3 with FALSE, the second time we leave here in step 5 with TRUE and after that we return immediately with TRUE In step 1.

I must say that this is all pretty confusing and not straightforward (more like straightbackward...).

Note that this can still blow up if there is a real mysql-error in steps 2/3/4 because that implies to use the database's default values which may have changed since the original installation. Oh well. The chances are slim. I hope. I wish.

void check_engine ()
close (line 196)

close the connection to the database

this closes the connection and resets the resource $this->db__link, in order to prevent use of the connection after it is already closed.

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

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 may or may not be 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 in old MySQL-versions.

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 299)

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 144)

connect to the database server and open the database

this opens a connection to the database, perhaps sets some connection parameters and subsequently selects the requested database. If anything goes wrong, FALSE is returned and additional information can be retrieved from the variables $this->errno and $this->error.

Note that for 4.1.x <= MySQL < 5.5.3 we use charset utf8 for the connection, whereas for 5.5.3 and up we use the utf8mb4 charset which can handle UTF-8 with upto 4-byte sequences.

  • 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 464)

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 491)

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.

Note that this routine takes the level of UTF-8-support into account; for level 3 we use charset 'utf8' and for level 4 'utf8mb4' (see also connect()).

The foreign key constraint is now capable of naming the constraints with a unique (database wide) symbol. (Cures InnoDB-error 1005 (HY000) errno: 121). This symbol is the prefixed tabledname followed by either the specified key name or a 1-based integer uniquemaker per table.

  • 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 428)

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: the name of the table to drop (prefix will be added automatically)
dump (line 745)

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 223)

escape special characters in string

this makes sure that dangerous characters like single quotes are properly escaped. this routine has a special twist because of the limited support for UTF-8 in some MySQL-versions. If support is limited ($this->utf_support equals 3), we strip any 4-byte UTF-8 characters from the string and we replace those with the generic substitution character U+FFFD (see als mysqli_utf8mb3()).

The (bold) assumptions here are:

  1. the $unescaped_string is in fact proper UTF-8 (see utf8_validate()), and
  2. all strings that are headed for the database are funneled through this routine, always.

  • 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 317)

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 365)

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)
mysqli_utf8mb3 (line 924)

massage string to contain only 3-byte UTF8-sequences

this replaces an otherwise perfectly valid 4-byte UTF-8 sequence in $utf8str with a 3-byte UTF-8 sequence equivalent with the Unicode replacement character U+FFFD.

The effect is that it leaves a hint that there used to be some character instead of silently discarding 4-byte sequences which MySQL does.

  • return: UTF-8 string with all 4-byte sequences replaced
string mysqli_utf8mb3 (string $utf8str)
  • string $utf8str: valid UTF-8 encoded string
mysqli_utf8_support (line 894)

determine the level of UTF-8 support based on MySQL-server version

MySQL support for UTF-8 was non-existent before 4.1.x and limited until 5.5.3. In this context 'limited' means: only the Basic Multilingual Plane (U+0000 ... U+FFFF) is supported, i.e. a maximum of 3-byte sequences per character.

As of 5.5.3 the full UTF-8 specification according to RFC 3629 is implemented. MySQL now has 'invented' yet another proprietary name for this character set: 'utf8mb4' (WTF?), and introduces the alias 'utf8mb3' for the pre 5.5.3 limited support for 'utf8' (WTF??), hinting that the meaning of 'utf8' may change in future versions to indicate 'utf8mb4' (WTF???). IM(NS)HO this is yet another reason to go looking for a decent replacement for MySQL. YMMV.

This routine returns exactly one of the values below (based on the server version).

  • 0: there is no UTF-8 support available in this server
  • 3: the limited 3-byte sequences are supported
  • 4: full support for 4-byte sequences available, but using the stupid ad-hoc name 'utf8mb4'
or the value FALSE if version information could not be obtained.

As a side effect, we record the server version information in $this->db_version (which is handy when creating a backup and also for debugging purposes).

  • return: support level: either 0 (none), 3 (limited), 4 (full, but with a quirky name) or FALSE on error
int|bool mysqli_utf8_support (resource $db_link)
  • resource $db_link: the MySQL connection
query (line 396)

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 DatabaseMysqliResult 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 DatabaseMysqliResult object
object|bool query (string $sql, [int $limit = ''], [int $offset = ''])
  • string $sql: valid SQL statement
  • int $limit: optional limitation of the number of records returned
  • int $offset: optional number of records to skip; $offset = 0 means start with the first
table_exists (line 439)

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 Tue, 28 Jun 2016 19:10:42 +0200 by phpDocumentor 1.4.0