schema.inc.php

来自「PHP 知识管理系统（基于树结构的知识管理系统）, 英文原版的PHP源码。」· PHP 代码 · 共 1,058 行 · 第 1/3 页

		$definition->type = $type;
		$definition->extra = $extra;
		$this->indexes[] = $definition;
	}

	public function createPrimaryKeys()
	{
		foreach($this->primaryKeys as $primaryKey)
		{
			$this->createPrimaryKey($primaryKey->table, $primaryKey->primaryKey);
		}
	}

	/**
	 * Add a primary key to a table.
	 *
	 * @param string $tablename
	 * @param string $primaryKey
	 */
	private function createPrimaryKey($tablename, $primaryKey)
	{
		if (!array_key_exists($tablename, $this->schema))
		{
			// if we don't know about the table, possibly it is in the commercial version.
			// exit gracefully.
			return;
		}

		if (is_array($primaryKey))
		{
			$primaryKey = implode(',', $primaryKey);
		}

		$sql="ALTER TABLE $tablename ADD PRIMARY KEY ($primaryKey)";
		$this->_exec($sql, false);

		if (strpos($primaryKey,',') === false)
		{
			// for some reason, there seems to be a problem periodically when adding foreign key constraints
			// unless there is a unique key. just a primary key isn't good enough for some reason. so for now,
			// we add the unique key, doubling up the effort of the primary key. we can drop these indexes again
			// later after the constraints have been added.
			$this->primary[$tablename] = $primaryKey;
			$sql="ALTER TABLE $tablename ADD UNIQUE KEY ($primaryKey)";
			$this->_exec($sql);
		}
	}

	public function createForeignKeys()
	{
		foreach($this->foreignKeys as $foreignKey)
		{
			$this->createForeignKey($foreignKey->table,$foreignKey->field,$foreignKey->otherTable,$foreignKey->otherField,$foreignKey->onDelete, $foreignKey->onUpdate);
		}
	}

	/**
	 * Add a foreign key constraint for a table.
	 *
	 * @param string $table
	 * @param string $field
	 * @param string $othertable
	 * @param string $otherfield
	 * @param string $ondelete
	 * @param string $onupdate
	 */
	private function createForeignKey($table, $field, $otherTable, $otherField, $onDelete='cascade', $onUpdate='cascade')
	{
		if (!array_key_exists($table, $this->schema) || !array_key_exists($otherTable, $this->schema))
		{
			// if we don't know about the tables, possibly it is in the commercial version.
			// exit gracefully.
			return;
		}

		$this->fixForeignKey($table, $field, $otherTable, $otherField);

		$sql = "ALTER TABLE $table ADD FOREIGN KEY ($field) REFERENCES $otherTable ($otherField) ";
		if ($onDelete != '')
		{
			$sql .= " ON DELETE $onDelete";
		}
		if ($onUpdate != '')
		{
			$sql .= " ON UPDATE $onUpdate";
		}
		$this->_exec($sql);
	}

	public function createIndexes()
	{
		foreach($this->indexes as $index)
		{
			$this->createIndex($index->table, $index->fields, $index->type, $index->extra);
		}
		$this->removeDuplicateIndexes();
	}

	private function fixForeignKey($table, $field, $otherTable, $otherField)
	{
		if ($table == $otherTable)
		{
			$this->_exec("create temporary table tmp_{$table}(id int);");
			$this->_exec("insert into tmp_{$table} select distinct id FROM {$table};");
			$this->_exec("insert into tmp_{$table} select distinct id FROM {$table};");
			$otherTable = "tmp_{$table}";
		}
		if ($otherTable == 'users' && $otherField == 'id')
		{
			$this->createFixUser();
			$sql = "UPDATE $table SET $field = -10 WHERE $field is not null and $field not in (select distinct id from users)";
			$this->_exec($sql);
			return;
		}

		$sql = "DELETE FROM $table WHERE $field is not null and $field not in (select distinct $otherField FROM $otherTable)";
		$this->_exec($sql);

		if ($table == $otherTable)
		{
			$this->_exec("drop table tmp_{$table};");
		}
	}

	/**
	 * Add an index to a table.
	 *
	 * @param string $table
	 * @param array $fields
	 * @param string $type
	 * @param string $extra
	 */
	private function createIndex($table, $fields, $type='', $extra='')
	{
		if (!array_key_exists($table, $this->schema))
		{
			// if we don't know about the tables, possibly it is in the commercial version.
			// exit gracefully.
			return;
		}

		if (!is_array($fields))
		{
			$fields = array($fields);
		}
		$index = implode('_', $fields);
		$fields = implode(',',$fields);
		$sql = "ALTER TABLE $table ADD $type INDEX $index ($fields$extra) ";
		$this->_exec($sql);
	}


	/**
	 * Drop all indexes and foreign key constraints from the system.
	 *
	 * @return int The number of elements cleared.
	 */
	private function getDBSchema()
	{
		$this->schema = array();
		$result = DBUtil::getResultArray('SHOW TABLES');
		$tables=array();

		foreach($result as $table)
		{
			$keys = array_keys($table);

			$tablename = $table[$keys[0]];
			if (substr($tablename,0,5) == 'zseq_')
			{
				continue;
			}

			$stmt = DBUtil::getResultArray("SHOW CREATE TABLE $tablename;");

			$keys = array_keys($stmt[0]);

			$sql = $stmt[0][$keys[1]];

			$this->schema[$tablename] = $sql;
		}
	}

	private function dropForeignKey($tablename, $foreignKey)
	{
		$sql = "ALTER TABLE $tablename DROP FOREIGN KEY $foreignKey;";
		return $this->_exec($sql);
	}

	/**
	 * Drops foreign keys based on the current schema
	 *
	 * @return int
	 */
	public function dropForeignKeys()
	{
		$dropped = 0;
		foreach($this->schema as $tablename=>$schema)
		{
			$lines = explode("\n", $schema);
			foreach($lines as $line)
			{
				if (strpos($line, 'CONSTRAINT') === false)
				{
					continue;
				}
				preg_match_all('(\`([^\`])*\`)',$line, $params);

				$constraint=substr($params[0][0],1,-1);
				$table= substr($params[0][2],1,-1);

				($this->dropForeignKey($tablename, $constraint));
				$dropped++;
			}
		}
		return $dropped;
	}

	/**
	 * Drops primary keys based on the current schema
	 *
	 * @return int
	 */
	public function dropPrimaryKeys()
	{
		$dropped = 0;
		foreach($this->schema as $tablename=>$schema)
		{
			$lines = explode("\n", $schema);
			foreach($lines as $line)
			{
				if (strpos($line, 'PRIMARY KEY') === false)
				{
					continue;
				}

				($this->dropPrimaryKey($tablename));
				$dropped++;
			}
		}
		return $dropped;
	}

	/**
	 * Drops the primary key from a table
	 *
	 * @param string $tablename
	 */
	private function dropPrimaryKey($tablename)
	{
		$sql = "ALTER TABLE $tablename DROP primary key;";
		return $this->_exec($sql,false);
	}

	/**
	 * Drops indexes based on the current schema
	 *
	 * @return int
	 */
	public function dropIndexes()
	{
		$dropped = 0;
		foreach($this->schema as $tablename=>$schema)
		{
			$lines = explode("\n", $schema);
			foreach($lines as $line)
			{
				if (strpos($line, 'KEY') === false)
				{
					continue;
				}

				if (strpos($line, 'PRIMARY KEY') !== false)
				{
					continue;
				}

				if (strpos($line, 'FOREIGN KEY') !== false)
				{
					continue;
				}

				preg_match_all('(\`([^\`])*\`)',$line, $params);

				$key = substr($params[0][0],1,-1);
				($this->dropIndex($tablename, $key));
				$dropped++;
			}
		}
		return $dropped;
	}


	/**
	 * Drop an index from the database.
	 *
	 * @param string $table
	 * @param string $field
	 */
	function dropIndex($table, $field)
	{
		if (!is_array($fields)) $field = array($field);
		$field = implode('_', $field);
		$sql = "ALTER TABLE $table DROP INDEX $field";
		$result = $this->_exec($sql);

		if (!$result)
		{
			print "...";
		}

		return $result;
	}

	/**
	 * Execute a db sql statement on the database.
	 *
	 * @param string $sql
	 * @return boolean
	 */
	private function _exec($sql )
	{
		global $default;
		if (!$this->persist)
		{
			print "$sql\n";
			return;
		}
		$this->log("Action: $sql");
		$rs = DBUtil::runQuery($sql, $default->_admindb );
		if (PEAR::isError($rs))
		{
			$this->log("* " . $rs->getMessage());
			return false;
		}
		return true;
	}

	/**
	 * Logs a message to the log file
	 *
	 * @param string $msg
	 */
	private function log($msg, $level='info')
	{
		global $default;
		$default->log->$level('KTSchemaUtil: ' .$msg);
	}
}


?>