366 lines
10 KiB
PHP
366 lines
10 KiB
PHP
<?php
|
|
/**
|
|
* The zdb library of zentaopms, can be used to bakup and restore a database.
|
|
*
|
|
* @copyright Copyright 2009-2023 禅道软件（青岛）有限公司(ZenTao Software (Qingdao) Co., Ltd., www.zentao.net)
|
|
* @license ZPL (http://zpl.pub/page/zplv12.html)
|
|
* @author Yidong Wang <yidong@cnezsoft.com>
|
|
* @package Zdb
|
|
* @version $Id$
|
|
* @link http://www.zentao.net
|
|
*/
|
|
class zdb
|
|
{
|
|
/**
|
|
* dbh
|
|
*
|
|
* @var object
|
|
* @access public
|
|
*/
|
|
public $dbh;
|
|
|
|
/**
|
|
* Construct
|
|
*
|
|
* @access public
|
|
* @return void
|
|
*/
|
|
public function __construct()
|
|
{
|
|
global $dbh;
|
|
$this->dbh = $dbh;
|
|
}
|
|
|
|
/**
|
|
* Get all tables.
|
|
*
|
|
* @param string $type if type is 'base', just get base table.
|
|
* @access public
|
|
* @return array
|
|
*/
|
|
public function getAllTables($type = 'base')
|
|
{
|
|
global $config;
|
|
|
|
$allTables = array();
|
|
$stmt = $this->dbh->query("show full tables");
|
|
while($table = $stmt->fetch(PDO::FETCH_ASSOC))
|
|
{
|
|
$tableType = strtolower($table['Table_type']);
|
|
if($type == 'base' and $tableType != 'base table') continue;
|
|
|
|
$tableName = $table["Tables_in_{$config->db->name}"];
|
|
$allTables[$tableName] = $tableType == 'base table' ? 'table' : $tableType;
|
|
}
|
|
|
|
return $allTables;
|
|
}
|
|
|
|
/**
|
|
* Get table fields.
|
|
*
|
|
* @param string $table
|
|
* @access public
|
|
* @return array
|
|
*/
|
|
public function getTableFields($table)
|
|
{
|
|
try
|
|
{
|
|
$this->dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
|
|
$sql = "DESC $table";
|
|
$rawFields = $this->dbh->query($sql)->fetchAll();
|
|
$this->dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
|
|
}
|
|
catch (PDOException $e)
|
|
{
|
|
global $dao;
|
|
$dao->sqlError($e);
|
|
}
|
|
|
|
$fields = array();
|
|
foreach($rawFields as $field) $fields[$field->field] = $field;
|
|
|
|
return $fields;
|
|
}
|
|
|
|
/**
|
|
* Diff current table fields with a fields array.
|
|
*
|
|
* @param string $table
|
|
* @param array $fields
|
|
* @access public
|
|
* @return array
|
|
*/
|
|
public function diffTable($table, $fields)
|
|
{
|
|
$tableFields = $this->getTableFields($table);
|
|
|
|
$diff = array_udiff_assoc($fields, $tableFields,
|
|
function($a, $b)
|
|
{
|
|
return (array)$a == (array)$b ? 0 : 1;
|
|
}
|
|
);
|
|
|
|
return $diff;
|
|
}
|
|
|
|
/**
|
|
* Add a column to a table, or modify a existing column.
|
|
*
|
|
* @param string $table
|
|
* @param object $column
|
|
* @param boolean $add if true, add $column as a new column, otherwise modify a existing column to $column.
|
|
* @access public
|
|
* @return object
|
|
*/
|
|
public function updateColumn($table, $column, $add = true)
|
|
{
|
|
$return = new stdclass();
|
|
$return->result = true;
|
|
$return->error = '';
|
|
|
|
$query = "ALTER TABLE `$table` " . ($add ? 'ADD' : 'MODIFY COLUMN') . " `$column->field` $column->type" . ($column->null == 'NO' ? ' NOT NULL' : '') . (is_null($column->default) ? '' : " DEFAULT '$column->default'") . (empty($column->extra) ? '' : " $column->extra") . ';';
|
|
|
|
try
|
|
{
|
|
$this->dbh->exec($query);
|
|
return $return;
|
|
}
|
|
catch(PDOException $e)
|
|
{
|
|
$return->result = false;
|
|
$return->error = $e->getMessage();
|
|
$return->sql = $query;
|
|
return $return;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Create a table with fields.
|
|
*
|
|
* @param string $name
|
|
* @param array $fields
|
|
* @access public
|
|
* @return object
|
|
*/
|
|
public function createTable($name, $fields)
|
|
{
|
|
$return = new stdclass();
|
|
$return->result = true;
|
|
$return->error = '';
|
|
$createTableQuery = "CREATE TABLE `$name` (";
|
|
|
|
foreach($fields as $field)
|
|
{
|
|
$createColumnQuery = "`$field->field` $field->type" . ($field->null == 'NO' ? ' NOT NULL' : '') . (is_null($field->default) ? '' : " DEFAULT '$field->default'") . (empty($field->extra) ? '' : " $field->extra") . ", ";
|
|
if(!empty($field->key))
|
|
{
|
|
if($field->key === 'PRI') $createColumnQuery .= "PRIMARY KEY (`{$field->field}`), ";
|
|
if($field->key === 'MUL') $createColumnQuery .= "KEY `{$field->field}` (`{$field->field}`), ";
|
|
if($field->key === 'UNI') $createColumnQuery .= "UNIQUE KEY `{$field->field}` (`{$field->field}`), ";
|
|
}
|
|
$createTableQuery .= $createColumnQuery;
|
|
}
|
|
|
|
$createTableQuery = rtrim($createTableQuery, ', ');
|
|
$createTableQuery .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8;";
|
|
|
|
try
|
|
{
|
|
$this->dbh->exec($createTableQuery);
|
|
return $return;
|
|
}
|
|
catch(PDOException $e)
|
|
{
|
|
$return->result = false;
|
|
$return->error = $e->getMessage();
|
|
$return->sql = $createTableQuery;
|
|
return $return;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Dump db.
|
|
*
|
|
* @param string $fileName
|
|
* @param array $tables
|
|
* @access public
|
|
* @return object
|
|
*/
|
|
public function dump($fileName, $tables = array())
|
|
{
|
|
/* Init the return. */
|
|
$return = new stdclass();
|
|
$return->result = true;
|
|
$return->error = '';
|
|
|
|
/* Get all tables in database. */
|
|
$allTables = $this->getAllTables();
|
|
|
|
/* Dump all tables when tables is empty. */
|
|
if(empty($tables))
|
|
{
|
|
$tables = $allTables;
|
|
}
|
|
else
|
|
{
|
|
foreach($tables as $table) $tables[$table] = $allTables[$table];
|
|
}
|
|
|
|
/* Check file. */
|
|
if(empty($fileName))
|
|
{
|
|
$return->result = false;
|
|
$return->error = 'Has not file';
|
|
return $return;
|
|
}
|
|
if(!is_writable(dirname($fileName)))
|
|
{
|
|
$return->result = false;
|
|
$return->error = 'The directory is not writable';
|
|
return $return;
|
|
}
|
|
|
|
/* Open this file. */
|
|
$fp = fopen($fileName, 'w');
|
|
fwrite($fp, "SET NAMES utf8;\n");
|
|
foreach($tables as $table => $tableType)
|
|
{
|
|
/* Check table exists. */
|
|
if(!isset($allTables[$table])) continue;
|
|
|
|
/* Create sql code. */
|
|
$backupSql = "DROP " . strtoupper($tableType) . " IF EXISTS `$table`;\n";
|
|
|
|
$schemaSQL = $this->getSchemaSQL($table, $tableType);
|
|
if($schemaSQL->result) $backupSql .= $schemaSQL->sql;
|
|
|
|
fwrite($fp, $backupSql);
|
|
if($tableType != 'table') continue;
|
|
|
|
$rows = $this->dbh->query("select * from `$table`");
|
|
while($row = $rows->fetch(PDO::FETCH_ASSOC))
|
|
{
|
|
/* Create key sql for insert. */
|
|
$keys = array_keys($row);
|
|
$keys = array_map('addslashes', $keys);
|
|
$keys = join('`,`', $keys);
|
|
$keys = "`" . $keys . "`";
|
|
|
|
/* Create a value sql. */
|
|
$value = array_values($row);
|
|
$value = array_map('addslashes', $value);
|
|
$value = join("','", $value);
|
|
$value = "'" . $value . "'";
|
|
$sql = "INSERT INTO `$table`($keys) VALUES (" . $value . ");\n";
|
|
|
|
/* Write sql code. */
|
|
fwrite($fp, $sql);
|
|
}
|
|
}
|
|
fclose($fp);
|
|
return $return;
|
|
}
|
|
|
|
/**
|
|
* Import DB
|
|
*
|
|
* @param string $fileName
|
|
* @access public
|
|
* @return object
|
|
*/
|
|
public function import($fileName)
|
|
{
|
|
$return = new stdclass();
|
|
$return->result = true;
|
|
$return->error = '';
|
|
|
|
if(!file_exists($fileName))
|
|
{
|
|
$return->result = false;
|
|
$return->error = "File is not exists";
|
|
return $return;
|
|
}
|
|
|
|
$fp = fopen($fileName, 'r');
|
|
$sqlEnd = 0;
|
|
while(($buffer = fgets($fp)) !== false)
|
|
{
|
|
$line = trim($buffer);
|
|
if(empty($line)) continue;
|
|
|
|
if($sqlEnd == 0) $sql = '';
|
|
$quotNum = substr_count($line, "'") - substr_count($line, "\'");
|
|
if(substr($line, -1) == ';' and $quotNum % 2 == 0 and $sqlEnd == 0)
|
|
{
|
|
$sql .= $buffer;
|
|
}
|
|
elseif($quotNum % 2 == 1 and $sqlEnd == 0)
|
|
{
|
|
$sql .= $buffer;
|
|
$sqlEnd = 1;
|
|
}
|
|
elseif(substr($line, -1) == ';' and $quotNum % 2 == 1 and $sqlEnd == 1)
|
|
{
|
|
$sql .= $buffer;
|
|
$sqlEnd = 0;
|
|
}
|
|
elseif(substr($line, -1) == ';' and $quotNum % 2 == 0 and $sqlEnd == 2)
|
|
{
|
|
$sql .= $buffer;
|
|
$sqlEnd = 0;
|
|
}
|
|
else
|
|
{
|
|
$sql .= $buffer;
|
|
$sqlEnd = $sqlEnd == 0 ? 2 : $sqlEnd;
|
|
}
|
|
|
|
if($sqlEnd == 0)
|
|
{
|
|
try
|
|
{
|
|
$this->dbh->query($sql);
|
|
}
|
|
catch(PDOException $e)
|
|
{
|
|
$return->result = false;
|
|
$return->error = $e->getMessage();
|
|
return $return;
|
|
}
|
|
}
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
/**
|
|
* Get schema SQL.
|
|
*
|
|
* @param string $table
|
|
* @access public
|
|
* @return object
|
|
*/
|
|
public function getSchemaSQL($table, $type = 'table')
|
|
{
|
|
$return = new stdclass();
|
|
$return->result = true;
|
|
$return->error = '';
|
|
|
|
try
|
|
{
|
|
$sql = "SHOW CREATE $type `$table`";
|
|
$createSql = $this->dbh->query($sql)->fetch(PDO::FETCH_ASSOC);
|
|
$return->sql = $createSql['Create ' . ucfirst($type)] . ";\n";
|
|
return $return;
|
|
}
|
|
catch(PDOException $e)
|
|
{
|
|
$return->result = false;
|
|
$return->error = $e->getMessage();
|
|
return $return;
|
|
}
|
|
}
|
|
}
|