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
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.
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.
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 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.
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
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.
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.
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.
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
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 mysql_utf8mb3).
The (bold) assumptions here are:
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.
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.
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).
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).
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 Tue, 28 Jun 2016 19:10:37 +0200 by phpDocumentor 1.4.0