[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();
		$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();
			if(empty($typeMatches)) continue;

			$columns[] = array('name' => $row['Field'], 'data' => array(
				'type' => $typeMatches[1],
				'length' => $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'];
		
		$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();
14d48464	36	$regex = new Regex('([a-z]+)\(([0-9]+)\)', Regex::CASE_INSENSITIVE);
14d48464	37
11ade432 AE	38	$sql = "SHOW COLUMNS FROM ".$tableName;
	39	$statement = $this->dbObj->prepareStatement($sql);
	40	$statement->execute();
	41	while ($row = $statement->fetchArray()) {
14d48464	42	$regex->match($row['Type']);
	43	$typeMatches = $regex->getMatches();
	44	if(empty($typeMatches)) continue;
4d4a5125	45
ebe8e825	46	$columns[] = array('name' => $row['Field'], 'data' => array(
5fe135db K	47	'type' => $typeMatches[1],
5fe135db K	48	'length' => $typeMatches[2],
14d48464	49	'notNull' => (($row['Null'] == 'YES') ? false : true),
5fe135db K	50	'key' => (($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : '')),
	51	'default' => $row['Default'],
	52	'autoIncrement' => ($row['Extra'] == 'auto_increment' ? true : false)
	53	));
ebe8e825	54	}
bf27dc91	55
9f959ced	56	return $columns;
11ade432 AE	57	}
	58
	59	/**
9f959ced	60	* @see wcf\system\database\editor\DatabaseEditor::getIndices()
11ade432 AE	61	*/
	62	public function getIndices($tableName) {
	63	$indices = array();
	64	$sql = "SHOW INDEX FROM ".$tableName;
	65	$statement = $this->dbObj->prepareStatement($sql);
	66	$statement->execute();
	67	while ($row = $statement->fetchArray()) {
9f959ced MS	68	$indices[] = $row['Key_name'];
	69	}
	70
	71	return $indices;
11ade432 AE	72	}
	73
	74	/**
9f959ced	75	* @see wcf\system\database\editor\DatabaseEditor::createTable()
11ade432 AE	76	*/
	77	public function createTable($tableName, $columns, $indices = array()) {
	78	$columnDefinition = $indexDefinition = '';
	79
	80	// build column definition
	81	foreach ($columns as $column) {
	82	if (!empty($columnDefinition)) $columnDefinition .= ',';
	83	$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
	84	}
	85
	86	// build index definition
	87	$hasFulltextIndex = false;
	88	foreach ($indices as $index) {
	89	if (!empty($indexDefinition)) $indexDefinition .= ',';
	90	$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
	91	if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
	92	}
	93
	94	// create table
	95	$sql = "CREATE TABLE ".$tableName." (
	96	".$columnDefinition."
	97	".(!empty($indexDefinition) ? ',' : '')."
	98	".$indexDefinition."
288fb832	99	) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
11ade432 AE	100	$statement = $this->dbObj->prepareStatement($sql);
	101	$statement->execute();
	102	}
	103
	104	/**
9f959ced	105	* @see wcf\system\database\editor\DatabaseEditor::dropTable()
11ade432 AE	106	*/
	107	public function dropTable($tableName) {
	108	$sql = "DROP TABLE IF EXISTS ".$tableName;
	109	$statement = $this->dbObj->prepareStatement($sql);
	110	$statement->execute();
	111	}
	112
	113	/**
9f959ced	114	* @see wcf\system\database\editor\DatabaseEditor::addColumn()
11ade432 AE	115	*/
	116	public function addColumn($tableName, $columnName, $columnData) {
	117	$sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
	118	$statement = $this->dbObj->prepareStatement($sql);
	119	$statement->execute();
	120	}
	121
	122	/**
9f959ced	123	* @see wcf\system\database\editor\DatabaseEditor::alterColumn()
11ade432 AE	124	*/
	125	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
	126	$sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData);
	127	$statement = $this->dbObj->prepareStatement($sql);
	128	$statement->execute();
	129	}
	130
	131	/**
9f959ced	132	* @see wcf\system\database\editor\DatabaseEditor::dropColumn()
11ade432 AE	133	*/
	134	public function dropColumn($tableName, $columnName) {
	135	$sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName;
	136	$statement = $this->dbObj->prepareStatement($sql);
	137	$statement->execute();
	138	}
	139
	140	/**
9f959ced	141	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	142	*/
	143	public function addIndex($tableName, $indexName, $indexData) {
	144	$sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData);
	145	$statement = $this->dbObj->prepareStatement($sql);
	146	$statement->execute();
	147	}
	148
	149	/**
9f959ced	150	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	151	*/
	152	public function addForeignKey($tableName, $indexName, $indexData) {
	153	$sql = "ALTER TABLE ".$tableName." ADD";
	154
	155	// add index name
96017679	156	if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
11ade432 AE	157
	158	// add columns
	159	$sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
	160
	161	// add referenced table name
	162	$sql .= " REFERENCES ".$indexData['referencedTable'];
	163
	164	// add referenced columns
	165	$sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")";
	166
	167	// add operation and action
	168	if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
	169
	170	$statement = $this->dbObj->prepareStatement($sql);
	171	$statement->execute();
	172	}
	173
	174	/**
9f959ced	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	/**
9f959ced	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	}