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

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

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