[GitHub/WoltLab/WCF.git] / wcfsetup / install / files / lib / system / database / editor / MySQLDatabaseEditor.class.php

<?php
namespace wcf\system\database\editor;
use wcf\system\database\util\PreparedStatementConditionBuilder;
use wcf\system\Regex;

/**
 * Database editor implementation for MySQL4.1 or higher.
 * 
 * @author	Marcel Werk
 * @copyright	2001-2013 WoltLab GmbH
 * @license	GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
 * @package	com.woltlab.wcf
 * @subpackage	system.database.editor
 * @category	Community Framework
 */
class MySQLDatabaseEditor extends DatabaseEditor {
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::getTableNames()
	 */
	public function getTableNames() {
		$existingTables = array();
		$sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) {
			$existingTables[] = $row[0];
		}
		return $existingTables;
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::getColumns()
	 */
	public function getColumns($tableName) {
		$columns = array();
		$sql = "SHOW COLUMNS FROM ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
			$typeMatches = Regex::compile('([a-z]+)\(([0-9]+)\)', Regex::CASE_INSENSITIVE)->match($row['Type']);

			$columns[] = array('name' => $row['Field'], 'data' => array(
				'type' => $typeMatches[1],
				'length' => $typeMatches[2],
				'notNull' => (($row['Null'] == 'YES') ? true : false),
				'key' => (($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : '')),
				'default' => $row['Default'],
				'autoIncrement' => ($row['Extra'] == 'auto_increment' ? true : false)
			));
		}
		
		return $columns;
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::getIndices()
	 */
	public function getIndices($tableName) {
		$indices = array();
		$sql = "SHOW INDEX FROM ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
			$indices[] = $row['Key_name'];
		}
		
		return $indices;
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::createTable()
	 */
	public function createTable($tableName, $columns, $indices = array()) {
		$columnDefinition = $indexDefinition = '';
		
		// build column definition
		foreach ($columns as $column) {
			if (!empty($columnDefinition)) $columnDefinition .= ',';
			$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
		}
		
		// build index definition
		$hasFulltextIndex = false;
		foreach ($indices as $index) {
			if (!empty($indexDefinition)) $indexDefinition .= ',';
			$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
			if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
		}
		
		// create table
		$sql = "CREATE TABLE ".$tableName." (
				".$columnDefinition."
				".(!empty($indexDefinition) ? ',' : '')."
				".$indexDefinition."
			) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::dropTable()
	 */
	public function dropTable($tableName) {
		$sql = "DROP TABLE IF EXISTS ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::addColumn()
	 */
	public function addColumn($tableName, $columnName, $columnData) {
		$sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::alterColumn()
	 */
	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
		$sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::dropColumn()
	 */
	public function dropColumn($tableName, $columnName) {
		$sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::addIndex()
	 */
	public function addIndex($tableName, $indexName, $indexData) {
		$sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::addIndex()
	 */
	public function addForeignKey($tableName, $indexName, $indexData) {
		$sql = "ALTER TABLE ".$tableName." ADD";
		
		// add index name
		if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
		
		// add columns
		$sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
		
		// add referenced table name
		$sql .= " REFERENCES ".$indexData['referencedTable'];
		
		// add referenced columns
		$sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")";
		
		// add operation and action
		if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
		
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::dropIndex()
	 */
	public function dropIndex($tableName, $indexName) {
		$sql = "ALTER TABLE ".$tableName." DROP INDEX ".$indexName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::dropForeignKey()
	 */
	public function dropForeignKey($tableName, $indexName) {
		$sql = "ALTER TABLE ".$tableName." DROP FOREIGN KEY `".$indexName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * Builds a column definition for execution in a create table or alter table statement.
	 * 
	 * @param	string		$columnName
	 * @param	array		$columnData
	 * @param	string
	 */
	protected function buildColumnDefinition($columnName, $columnData) {
		// column name
		$definition = $columnName;
		// column type
		$definition .= " ".$columnData['type'];
		// column length and decimals
		if (!empty($columnData['length'])) {
			$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
		}
		// enum / set
		if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
			$definition .= "(".$columnData['values'].")";
		}
		// not null / null
		if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
		// default
		if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
		// auto_increment
		if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
		// key
		if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
		
		return $definition;
	}
	
	/**
	 * Builds a index definition for execution in a create table or alter table statement.
	 * 
	 * @param	string		$indexName
	 * @param	array		$indexData
	 * @param	string
	 */
	protected function buildIndexDefinition($indexName, $indexData) {
		$definition = "";
		// index type
		if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
		else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
		else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
		else $definition = "KEY";
		
		// index name
		if (!empty($indexName)) $definition .= " ".$indexName."";
		// columns
		$definition .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
		
		return $definition;
	}
}
Commit	Line	Data
11ade432 AE	1	<?php
11ade432 AE	2	namespace wcf\system\database\editor;
76d74665	3	use wcf\system\database\util\PreparedStatementConditionBuilder;
82a7de7f	4	use wcf\system\Regex;
11ade432 AE	5
11ade432 AE	6	/**
a17de04e MS	7	* Database editor implementation for MySQL4.1 or higher.
a17de04e MS	8	*
11ade432	9	* @author Marcel Werk
7405c637	10	* @copyright 2001-2013 WoltLab GmbH
11ade432 AE	11	* @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
	12	* @package com.woltlab.wcf
	13	* @subpackage system.database.editor
9f959ced	14	* @category Community Framework
11ade432 AE	15	*/
	16	class MySQLDatabaseEditor extends DatabaseEditor {
	17	/**
9f959ced	18	* @see wcf\system\database\editor\DatabaseEditor::getTableNames()
11ade432 AE	19	*/
	20	public function getTableNames() {
	21	$existingTables = array();
	22	$sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`";
	23	$statement = $this->dbObj->prepareStatement($sql);
	24	$statement->execute();
	25	while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) {
	26	$existingTables[] = $row[0];
	27	}
	28	return $existingTables;
	29	}
	30
	31	/**
9f959ced	32	* @see wcf\system\database\editor\DatabaseEditor::getColumns()
11ade432 AE	33	*/
	34	public function getColumns($tableName) {
	35	$columns = array();
	36	$sql = "SHOW COLUMNS FROM ".$tableName;
	37	$statement = $this->dbObj->prepareStatement($sql);
	38	$statement->execute();
	39	while ($row = $statement->fetchArray()) {
4d4a5125 K	40	$typeMatches = Regex::compile('([a-z]+)\(([0-9]+)\)', Regex::CASE_INSENSITIVE)->match($row['Type']);
4d4a5125 K	41
ebe8e825	42	$columns[] = array('name' => $row['Field'], 'data' => array(
5fe135db K	43	'type' => $typeMatches[1],
	44	'length' => $typeMatches[2],
	45	'notNull' => (($row['Null'] == 'YES') ? true : false),
	46	'key' => (($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : '')),
	47	'default' => $row['Default'],
	48	'autoIncrement' => ($row['Extra'] == 'auto_increment' ? true : false)
	49	));
ebe8e825	50	}
bf27dc91	51
9f959ced	52	return $columns;
11ade432 AE	53	}
	54
	55	/**
9f959ced	56	* @see wcf\system\database\editor\DatabaseEditor::getIndices()
11ade432 AE	57	*/
	58	public function getIndices($tableName) {
	59	$indices = array();
	60	$sql = "SHOW INDEX FROM ".$tableName;
	61	$statement = $this->dbObj->prepareStatement($sql);
	62	$statement->execute();
	63	while ($row = $statement->fetchArray()) {
9f959ced MS	64	$indices[] = $row['Key_name'];
	65	}
	66
	67	return $indices;
11ade432 AE	68	}
	69
	70	/**
9f959ced	71	* @see wcf\system\database\editor\DatabaseEditor::createTable()
11ade432 AE	72	*/
	73	public function createTable($tableName, $columns, $indices = array()) {
	74	$columnDefinition = $indexDefinition = '';
	75
	76	// build column definition
	77	foreach ($columns as $column) {
	78	if (!empty($columnDefinition)) $columnDefinition .= ',';
	79	$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
	80	}
	81
	82	// build index definition
	83	$hasFulltextIndex = false;
	84	foreach ($indices as $index) {
	85	if (!empty($indexDefinition)) $indexDefinition .= ',';
	86	$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
	87	if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
	88	}
	89
	90	// create table
	91	$sql = "CREATE TABLE ".$tableName." (
	92	".$columnDefinition."
	93	".(!empty($indexDefinition) ? ',' : '')."
	94	".$indexDefinition."
288fb832	95	) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
11ade432 AE	96	$statement = $this->dbObj->prepareStatement($sql);
	97	$statement->execute();
	98	}
	99
	100	/**
9f959ced	101	* @see wcf\system\database\editor\DatabaseEditor::dropTable()
11ade432 AE	102	*/
	103	public function dropTable($tableName) {
	104	$sql = "DROP TABLE IF EXISTS ".$tableName;
	105	$statement = $this->dbObj->prepareStatement($sql);
	106	$statement->execute();
	107	}
	108
	109	/**
9f959ced	110	* @see wcf\system\database\editor\DatabaseEditor::addColumn()
11ade432 AE	111	*/
	112	public function addColumn($tableName, $columnName, $columnData) {
	113	$sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
	114	$statement = $this->dbObj->prepareStatement($sql);
	115	$statement->execute();
	116	}
	117
	118	/**
9f959ced	119	* @see wcf\system\database\editor\DatabaseEditor::alterColumn()
11ade432 AE	120	*/
	121	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
	122	$sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData);
	123	$statement = $this->dbObj->prepareStatement($sql);
	124	$statement->execute();
	125	}
	126
	127	/**
9f959ced	128	* @see wcf\system\database\editor\DatabaseEditor::dropColumn()
11ade432 AE	129	*/
	130	public function dropColumn($tableName, $columnName) {
	131	$sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName;
	132	$statement = $this->dbObj->prepareStatement($sql);
	133	$statement->execute();
	134	}
	135
	136	/**
9f959ced	137	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	138	*/
	139	public function addIndex($tableName, $indexName, $indexData) {
	140	$sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData);
	141	$statement = $this->dbObj->prepareStatement($sql);
	142	$statement->execute();
	143	}
	144
	145	/**
9f959ced	146	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	147	*/
	148	public function addForeignKey($tableName, $indexName, $indexData) {
	149	$sql = "ALTER TABLE ".$tableName." ADD";
	150
	151	// add index name
96017679	152	if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
11ade432 AE	153
	154	// add columns
	155	$sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
	156
	157	// add referenced table name
	158	$sql .= " REFERENCES ".$indexData['referencedTable'];
	159
	160	// add referenced columns
	161	$sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")";
	162
	163	// add operation and action
	164	if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
	165
	166	$statement = $this->dbObj->prepareStatement($sql);
	167	$statement->execute();
	168	}
	169
	170	/**
9f959ced	171	* @see wcf\system\database\editor\DatabaseEditor::dropIndex()
11ade432 AE	172	*/
	173	public function dropIndex($tableName, $indexName) {
	174	$sql = "ALTER TABLE ".$tableName." DROP INDEX ".$indexName;
	175	$statement = $this->dbObj->prepareStatement($sql);
	176	$statement->execute();
	177	}
	178
b6ae7d04	179	/**
9f959ced	180	* @see wcf\system\database\editor\DatabaseEditor::dropForeignKey()
b6ae7d04 MW	181	*/
b6ae7d04 MW	182	public function dropForeignKey($tableName, $indexName) {
96017679	183	$sql = "ALTER TABLE ".$tableName." DROP FOREIGN KEY `".$indexName."`";
b6ae7d04 MW	184	$statement = $this->dbObj->prepareStatement($sql);
	185	$statement->execute();
	186	}
	187
11ade432 AE	188	/**
	189	* Builds a column definition for execution in a create table or alter table statement.
	190	*
	191	* @param string $columnName
	192	* @param array $columnData
	193	* @param string
	194	*/
	195	protected function buildColumnDefinition($columnName, $columnData) {
	196	// column name
	197	$definition = $columnName;
	198	// column type
	199	$definition .= " ".$columnData['type'];
	200	// column length and decimals
	201	if (!empty($columnData['length'])) {
	202	$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
	203	}
	204	// enum / set
	205	if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
	206	$definition .= "(".$columnData['values'].")";
	207	}
	208	// not null / null
	209	if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
	210	// default
	211	if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
	212	// auto_increment
	213	if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
	214	// key
	215	if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
	216
	217	return $definition;
	218	}
	219
	220	/**
	221	* Builds a index definition for execution in a create table or alter table statement.
	222	*
	223	* @param string $indexName
	224	* @param array $indexData
	225	* @param string
	226	*/
	227	protected function buildIndexDefinition($indexName, $indexData) {
	228	$definition = "";
	229	// index type
	230	if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
	231	else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
	232	else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
	233	else $definition = "KEY";
	234
	235	// index name
	236	if (!empty($indexName)) $definition .= " ".$indexName."";
	237	// columns
	238	$definition .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
	239
	240	return $definition;
	241	}
	242	}