<?php
class DbTestUtil
{
@var
const DSN = "mysql:host=192.168.XX.XX;port=3306;dbname=XXXX;charset=utf8";
@var
const USERNAME = "XXX";
@var
const PASSWORD = "XXX";
@var
private $_pdo = null;
@var
private $_original_table = null;
@var
private $_backup_table = null;
@var
private $_primaryKeyList = null;
@param
public function __construct($original_table)
{
$this->_original_table = $original_table;
$this->_backup_table = "{$original_table}_backup";
}
@param
@return
public function init($importXmlPath)
{
if ($this->_pdo === null) {
$option = array(PDO::MYSQL_ATTR_LOCAL_INFILE => true);
$this->_pdo = new PDO(
self::DSN,
self::USERNAME,
self::PASSWORD,
$option
);
}
$this->_exec("DROP TABLE {$this->_backup_table}", false);
$this->_exec(
"CREATE TABLE {$this->_backup_table} LIKE {$this->_original_table}"
);
$this->_exec(
"INSERT INTO {$this->_backup_table}
SELECT * FROM {$this->_original_table}"
);
$this->_primaryKeyList = $this->_getPrimaryKeyList();
$this->_loadXml($this->_original_table, $importXmlPath);
}
@return
public function revert()
{
$this->_exec("DROP TABLE {$this->_original_table}");
$this->_exec(
"ALTER TABLE {$this->_backup_table} RENAME TO {$this->_original_table}"
);
}
@return
public function getAfter()
{
return $this->_selectAll($this->_original_table);
}
@param
@return
public function getExpected($expectedXmlPath)
{
$this->_loadXml($this->_original_table, $expectedXmlPath);
return $this->_selectAll($this->_original_table);
}
@param
@param
@return
private function _loadXml($tableName, $xmlFilePath)
{
if (file_exists($xmlFilePath) === false) {
throw new Exception("XMLファイルが存在しません: {$xmlFilePath}");
}
$this->_exec("TRUNCATE TABLE {$tableName}");
$this->_exec(
"LOAD XML LOCAL INFILE '{$xmlFilePath}' INTO TABLE {$tableName}"
);
}
@param
@param
@return
private function _exec($sql, $isCheck = true)
{
$ret = (int)$this->_pdo->exec($sql);
if ($isCheck === true && $ret === false) {
throw new Exception("SQL実行に失敗しました: {$sql}");
}
}
@param
@return
private function _query($sql)
{
$stmt = $this->_pdo->query($sql);
$ret = $stmt->fetchAll();
if ($ret === false) {
throw new Exception("SQL実行に失敗しました: {$sql}");
}
return $ret;
}
@return
private function _getPrimaryKeyList()
{
preg_match('/dbname=(\w+)/', self::DSN, $match);
$schema = $match[1];
$sql = "
SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = '{$schema}'
AND table_name = '{$this->_original_table}'
AND column_key = 'PRI'
ORDER BY
ordinal_position
";
return $this->_query($sql);
}
@param
@return
private function _selectAll($tabelName)
{
$order = "";
foreach ($this->_primaryKeyList as $primaryKey) {
if ($order === "") {
$order = $primaryKey[0];
} else {
$order = $order.", ".$primaryKey[0];
}
}
$sql = "
SELECT
*
FROM
{$tabelName}
ORDER BY
{$order} ASC
";
return $this->_query($sql);
}
}
<?php
require_once(dirname(__FILE__)."/DbTestUtil.php");
class DbTestSampleForPhpUnitOnly extends PHPUnit_Framework_TestCase
{
private $_dbTestUtil = null;
public function setUp()
{
$this->_dbTestUtil = new DbTestUtil("test");
$this->_dbTestUtil->init(dirname(__FILE__)."/init/init.xml");
}
public function tearDown()
{
$this->_dbTestUtil->revert();
}
public function testSample()
{
$dsn = "mysql:host=192.168.XX.XX;port=3306;dbname=XXXX;charset=utf8";
$username = "XXXX";
$password = "XXXX";
$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->query("SELECT * FROM test ORDER BY id ASC");
$list = $stmt->fetchAll();
$answer = [
['id' => '1', 0 => '1', 'name' => 'john', 1 => 'john'],
['id' => '2', 0 => '2', 'name' => 'tom', 1 => 'tom'],
['id' => '3', 0 => '3', 'name' => 'apache', 1 => 'apache']
];
$this->assertEquals($list, $answer);
$id = 4;
$name = 'php';
$stmt = $pdo->prepare("INSERT INTO test (id, name) VALUES (:id, :name)");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$after = $this->_dbTestUtil->getAfter();
$expected = $this->_dbTestUtil->getExpected(dirname(__FILE__)."/expected/expected.xml");
$this->assertEquals($after, $expected);
}
}