Wian Drs
488 lines
19 KiB
PHP
488 lines
19 KiB
PHP
<?php
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
/**
|
|
* Creates a consistent, one-way snapshot from a legacy database and upgrades
|
|
* it in an empty target database. It never writes to the source database.
|
|
*/
|
|
class DatabaseSyncService
|
|
{
|
|
private $CI;
|
|
private $sourceConfig;
|
|
private $targetConfig;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->CI =& get_instance();
|
|
$this->sourceConfig = $this->readConfig('DBSYNC_SOURCE');
|
|
$this->targetConfig = $this->readConfig('DBSYNC_TARGET');
|
|
|
|
if ($this->sourceConfig['host'] === $this->targetConfig['host']
|
|
&& $this->sourceConfig['port'] === $this->targetConfig['port']
|
|
&& $this->sourceConfig['database'] === $this->targetConfig['database']) {
|
|
throw new RuntimeException('Database sumber dan target tidak boleh sama.');
|
|
}
|
|
}
|
|
|
|
public function check()
|
|
{
|
|
list($source, $target) = $this->connectPair();
|
|
try {
|
|
$result = array(
|
|
'status' => true,
|
|
'source' => $this->databaseInfo($source, $this->sourceConfig['database']),
|
|
'target' => $this->databaseInfo($target, $this->targetConfig['database']),
|
|
);
|
|
$result['target']['is_empty'] = $result['target']['objects'] === 0;
|
|
return $result;
|
|
} finally {
|
|
$source->close();
|
|
$target->close();
|
|
}
|
|
}
|
|
|
|
public function snapshot()
|
|
{
|
|
list($source, $target) = $this->connectPair();
|
|
$target->close();
|
|
try {
|
|
$path = $this->createSnapshot($source);
|
|
return array(
|
|
'status' => true,
|
|
'backup' => $path,
|
|
'size' => filesize($path),
|
|
'sha256' => hash_file('sha256', $path),
|
|
);
|
|
} finally {
|
|
$source->close();
|
|
}
|
|
}
|
|
|
|
public function bootstrap()
|
|
{
|
|
// Both connections must succeed before the first target write.
|
|
list($source, $target) = $this->connectPair();
|
|
try {
|
|
$targetInfo = $this->databaseInfo($target, $this->targetConfig['database']);
|
|
if ($targetInfo['objects'] !== 0) {
|
|
throw new RuntimeException(
|
|
'Bootstrap ditolak: target tidak kosong (' . $targetInfo['objects'] . ' objek).'
|
|
);
|
|
}
|
|
|
|
$backup = trim((string) app_env('DBSYNC_BACKUP_FILE', ''));
|
|
if ($backup === '') {
|
|
$backup = $this->createSnapshot($source);
|
|
} elseif (!is_file($backup) || !is_readable($backup)) {
|
|
throw new RuntimeException('File backup tidak ditemukan atau tidak dapat dibaca.');
|
|
}
|
|
|
|
$expectedHash = strtolower(trim((string) app_env('DBSYNC_BACKUP_SHA256', '')));
|
|
$actualHash = hash_file('sha256', $backup);
|
|
if ($expectedHash !== '' && !hash_equals($expectedHash, strtolower($actualHash))) {
|
|
throw new RuntimeException('Checksum backup tidak sesuai. Target belum diubah.');
|
|
}
|
|
} finally {
|
|
$source->close();
|
|
$target->close();
|
|
}
|
|
|
|
$sanitizedBackup = $this->sanitizeBackup($backup);
|
|
try {
|
|
$this->importBackup($sanitizedBackup);
|
|
$this->runTargetMigrations();
|
|
$verification = $this->verify();
|
|
} finally {
|
|
if (is_file($sanitizedBackup)) {
|
|
@unlink($sanitizedBackup);
|
|
}
|
|
}
|
|
|
|
return array(
|
|
'status' => true,
|
|
'message' => 'Snapshot legacy berhasil dimuat dan struktur target berhasil dimodernisasi.',
|
|
'backup' => $backup,
|
|
'backup_sha256' => $actualHash,
|
|
'verification' => $verification,
|
|
);
|
|
}
|
|
|
|
public function verify()
|
|
{
|
|
list($source, $target) = $this->connectPair();
|
|
try {
|
|
$required = array('accounts', 'users', 'journals', 'journal_details', 'items', 'item_barcodes');
|
|
$missing = array();
|
|
foreach ($required as $table) {
|
|
if (!$this->tableExists($target, $this->targetConfig['database'], $table)) {
|
|
$missing[] = $table;
|
|
}
|
|
}
|
|
if ($missing) {
|
|
throw new RuntimeException('Verifikasi gagal, tabel wajib tidak tersedia: ' . implode(', ', $missing));
|
|
}
|
|
|
|
$latestFileVersion = $this->latestMigrationVersion();
|
|
$migrationVersion = 0;
|
|
if ($this->tableExists($target, $this->targetConfig['database'], 'migrations')) {
|
|
$row = $target->query('SELECT COALESCE(MAX(version), 0) AS version FROM migrations')->fetch_assoc();
|
|
$migrationVersion = (int) $row['version'];
|
|
}
|
|
|
|
$invalidAccounts = (int) $target->query(
|
|
"SELECT COUNT(*) AS total FROM accounts WHERE kode_akun IS NULL OR kode_akun NOT REGEXP '^[0-9]{4}$'"
|
|
)->fetch_assoc()['total'];
|
|
$journal = $target->query(
|
|
'SELECT COALESCE(SUM(debit),0) AS debit, COALESCE(SUM(kredit),0) AS kredit FROM journal_details'
|
|
)->fetch_assoc();
|
|
$journalDifference = round((float) $journal['debit'] - (float) $journal['kredit'], 2);
|
|
|
|
$orphans = array(
|
|
'journal_details' => $this->scalar($target,
|
|
'SELECT COUNT(*) FROM journal_details d LEFT JOIN journals j ON j.id=d.journal_id WHERE j.id IS NULL'),
|
|
'invoice_details' => $this->tableExists($target, $this->targetConfig['database'], 'invoice_details')
|
|
? $this->scalar($target,
|
|
'SELECT COUNT(*) FROM invoice_details d LEFT JOIN invoices i ON i.id=d.invoice_id WHERE i.id IS NULL')
|
|
: 0,
|
|
'item_barcodes' => $this->scalar($target,
|
|
'SELECT COUNT(*) FROM item_barcodes b LEFT JOIN items i ON i.id=b.item_id WHERE i.id IS NULL'),
|
|
);
|
|
|
|
$passed = $migrationVersion === $latestFileVersion
|
|
&& $invalidAccounts === 0
|
|
&& abs($journalDifference) < 0.01
|
|
&& array_sum($orphans) === 0;
|
|
|
|
if (!$passed) {
|
|
throw new RuntimeException('Verifikasi target gagal. Periksa hasil audit sebelum cutover.');
|
|
}
|
|
|
|
return array(
|
|
'status' => true,
|
|
'migration_version' => $migrationVersion,
|
|
'latest_migration_file' => $latestFileVersion,
|
|
'account_codes_invalid' => $invalidAccounts,
|
|
'journal_debit' => (float) $journal['debit'],
|
|
'journal_credit' => (float) $journal['kredit'],
|
|
'journal_difference' => $journalDifference,
|
|
'orphans' => $orphans,
|
|
'target' => $this->databaseInfo($target, $this->targetConfig['database']),
|
|
);
|
|
} finally {
|
|
$source->close();
|
|
$target->close();
|
|
}
|
|
}
|
|
|
|
private function readConfig($prefix)
|
|
{
|
|
$config = array(
|
|
'host' => trim((string) app_env($prefix . '_HOST', '')),
|
|
'port' => (int) (app_env($prefix . '_PORT', 3306) ?: 3306),
|
|
'username' => trim((string) app_env($prefix . '_USERNAME', '')),
|
|
'password' => (string) app_env($prefix . '_PASSWORD', ''),
|
|
'database' => trim((string) app_env($prefix . '_DATABASE', '')),
|
|
);
|
|
|
|
foreach (array('host', 'port', 'username', 'password', 'database') as $key) {
|
|
if ($config[$key] === '' || $config[$key] === 0) {
|
|
throw new RuntimeException('Konfigurasi ' . $prefix . '_' . strtoupper($key) . ' belum lengkap.');
|
|
}
|
|
}
|
|
return $config;
|
|
}
|
|
|
|
private function connectPair()
|
|
{
|
|
$source = $this->connect($this->sourceConfig, 'sumber');
|
|
try {
|
|
$target = $this->connect($this->targetConfig, 'target');
|
|
} catch (Throwable $exception) {
|
|
$source->close();
|
|
throw $exception;
|
|
}
|
|
return array($source, $target);
|
|
}
|
|
|
|
private function connect($config, $label)
|
|
{
|
|
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
|
|
$connection = mysqli_init();
|
|
$connection->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
|
|
try {
|
|
$connection->real_connect(
|
|
$config['host'],
|
|
$config['username'],
|
|
$config['password'],
|
|
$config['database'],
|
|
$config['port']
|
|
);
|
|
$connection->set_charset('utf8mb4');
|
|
return $connection;
|
|
} catch (Throwable $exception) {
|
|
throw new RuntimeException('Koneksi database ' . $label . ' gagal. Proses dihentikan.', 0, $exception);
|
|
}
|
|
}
|
|
|
|
private function databaseInfo($connection, $database)
|
|
{
|
|
$statement = $connection->prepare(
|
|
'SELECT COUNT(*) AS total FROM information_schema.tables WHERE table_schema=?'
|
|
);
|
|
$statement->bind_param('s', $database);
|
|
$statement->execute();
|
|
$objects = (int) $statement->get_result()->fetch_assoc()['total'];
|
|
$statement->close();
|
|
|
|
return array(
|
|
'database' => $database,
|
|
'server_version' => $connection->server_info,
|
|
'objects' => $objects,
|
|
);
|
|
}
|
|
|
|
private function tableExists($connection, $database, $table)
|
|
{
|
|
$statement = $connection->prepare(
|
|
'SELECT COUNT(*) AS total FROM information_schema.tables WHERE table_schema=? AND table_name=?'
|
|
);
|
|
$statement->bind_param('ss', $database, $table);
|
|
$statement->execute();
|
|
$exists = (int) $statement->get_result()->fetch_assoc()['total'] > 0;
|
|
$statement->close();
|
|
return $exists;
|
|
}
|
|
|
|
private function createSnapshot($source)
|
|
{
|
|
$directory = trim((string) app_env('DBSYNC_BACKUP_DIRECTORY', ''));
|
|
if ($directory === '') {
|
|
$directory = dirname(FCPATH) . DIRECTORY_SEPARATOR . 'private_backups' . DIRECTORY_SEPARATOR . 'accounting_dev';
|
|
}
|
|
if (!is_dir($directory) && !mkdir($directory, 0700, true)) {
|
|
throw new RuntimeException('Folder backup tidak dapat dibuat.');
|
|
}
|
|
$path = rtrim($directory, '/\\') . DIRECTORY_SEPARATOR
|
|
. $this->sourceConfig['database'] . '-cutover-' . date('Ymd-His') . '.sql';
|
|
$file = fopen($path, 'xb');
|
|
if (!$file) {
|
|
throw new RuntimeException('File snapshot tidak dapat dibuat.');
|
|
}
|
|
|
|
try {
|
|
fwrite($file, "-- One-way database cutover snapshot\nSET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\nSET UNIQUE_CHECKS=0;\n\n");
|
|
$tables = array();
|
|
$views = array();
|
|
$result = $source->query('SHOW FULL TABLES');
|
|
while ($row = $result->fetch_row()) {
|
|
if (strtoupper($row[1]) === 'VIEW') {
|
|
$views[] = $row[0];
|
|
} else {
|
|
$tables[] = $row[0];
|
|
}
|
|
}
|
|
|
|
$source->query('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
|
|
$source->query('START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY');
|
|
foreach ($tables as $table) {
|
|
$quotedTable = $this->identifier($table);
|
|
$createRow = $source->query('SHOW CREATE TABLE ' . $quotedTable)->fetch_row();
|
|
fwrite($file, 'DROP TABLE IF EXISTS ' . $quotedTable . ";\n" . $createRow[1] . ";\n\n");
|
|
|
|
$selectColumns = array();
|
|
$columnResult = $source->query('SHOW FULL COLUMNS FROM ' . $quotedTable);
|
|
while ($column = $columnResult->fetch_assoc()) {
|
|
if (stripos((string) $column['Extra'], 'GENERATED') === false) {
|
|
$selectColumns[] = $this->identifier($column['Field']);
|
|
}
|
|
}
|
|
$rows = $source->query(
|
|
'SELECT ' . implode(',', $selectColumns) . ' FROM ' . $quotedTable,
|
|
MYSQLI_USE_RESULT
|
|
);
|
|
$columns = array();
|
|
foreach ($rows->fetch_fields() as $field) {
|
|
$columns[] = $this->identifier($field->name);
|
|
}
|
|
$batch = array();
|
|
while ($row = $rows->fetch_row()) {
|
|
$values = array();
|
|
foreach ($row as $value) {
|
|
$values[] = $value === null ? 'NULL' : "'" . $source->real_escape_string($value) . "'";
|
|
}
|
|
$batch[] = '(' . implode(',', $values) . ')';
|
|
if (count($batch) >= 200) {
|
|
$this->writeInsertBatch($file, $quotedTable, $columns, $batch);
|
|
$batch = array();
|
|
}
|
|
}
|
|
$rows->free();
|
|
if ($batch) {
|
|
$this->writeInsertBatch($file, $quotedTable, $columns, $batch);
|
|
}
|
|
fwrite($file, "\n");
|
|
}
|
|
$source->commit();
|
|
|
|
foreach ($views as $view) {
|
|
$quotedView = $this->identifier($view);
|
|
$create = $source->query('SHOW CREATE VIEW ' . $quotedView)->fetch_assoc();
|
|
fwrite($file, 'DROP VIEW IF EXISTS ' . $quotedView . ";\n" . $create['Create View'] . ";\n\n");
|
|
}
|
|
fwrite($file, "SET UNIQUE_CHECKS=1;\nSET FOREIGN_KEY_CHECKS=1;\n");
|
|
} catch (Throwable $exception) {
|
|
@$source->rollback();
|
|
fclose($file);
|
|
@unlink($path);
|
|
throw $exception;
|
|
}
|
|
|
|
fclose($file);
|
|
@chmod($path, 0600);
|
|
return $path;
|
|
}
|
|
|
|
private function writeInsertBatch($file, $table, $columns, $batch)
|
|
{
|
|
fwrite(
|
|
$file,
|
|
'INSERT INTO ' . $table . '(' . implode(',', $columns) . ") VALUES\n"
|
|
. implode(",\n", $batch) . ";\n"
|
|
);
|
|
}
|
|
|
|
private function sanitizeBackup($sourcePath)
|
|
{
|
|
$targetPath = tempnam(sys_get_temp_dir(), 'accounting-cutover-');
|
|
$input = fopen($sourcePath, 'rb');
|
|
$output = fopen($targetPath, 'wb');
|
|
if (!$input || !$output) {
|
|
throw new RuntimeException('File sementara untuk import tidak dapat dibuat.');
|
|
}
|
|
$sqlModeInjected = false;
|
|
$generatedScheduleInsert = false;
|
|
while (($line = fgets($input)) !== false) {
|
|
$line = preg_replace('/\\sDEFINER=`[^`]+`@`[^`]+`/i', '', $line);
|
|
|
|
// Older snapshots produced before this service included the
|
|
// generated schedule_date column. MySQL only accepts DEFAULT for
|
|
// generated values, so retain the row and let MySQL calculate it.
|
|
if (stripos($line, 'INSERT INTO `k_employee_shift_schedules`') === 0
|
|
&& stripos($line, '`schedule_date`') !== false) {
|
|
$generatedScheduleInsert = true;
|
|
} elseif ($generatedScheduleInsert && isset($line[0]) && $line[0] === '(') {
|
|
$line = preg_replace(
|
|
"/^(\\('[^']*','[^']*',(?:'[^']*'|NULL),'[^']*','[^']*'),(?:'[^']*'|NULL)(,)/",
|
|
'$1,DEFAULT$2',
|
|
$line
|
|
);
|
|
if (substr(rtrim($line), -1) === ';') {
|
|
$generatedScheduleInsert = false;
|
|
}
|
|
}
|
|
fwrite($output, $line);
|
|
// Legacy MySQL ENUM columns contain empty-string values that are
|
|
// valid in the old data but rejected by modern strict SQL mode.
|
|
// Compatibility mode preserves those rows; later migrations map
|
|
// them to the modern representation without deleting data.
|
|
if (!$sqlModeInjected && preg_match('/^SET NAMES /i', trim($line))) {
|
|
fwrite($output, "SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';\n");
|
|
$sqlModeInjected = true;
|
|
}
|
|
}
|
|
fclose($input);
|
|
fclose($output);
|
|
return $targetPath;
|
|
}
|
|
|
|
private function importBackup($backup)
|
|
{
|
|
$mysql = trim((string) app_env('DBSYNC_MYSQL_BIN', ''));
|
|
if ($mysql === '') {
|
|
$mysql = 'C:\\xampp\\mysql\\bin\\mysql.exe';
|
|
}
|
|
if (!is_file($mysql)) {
|
|
throw new RuntimeException('mysql client tidak ditemukan: ' . $mysql);
|
|
}
|
|
|
|
$command = array(
|
|
$mysql,
|
|
'--host=' . $this->targetConfig['host'],
|
|
'--port=' . $this->targetConfig['port'],
|
|
'--user=' . $this->targetConfig['username'],
|
|
'--default-character-set=utf8mb4',
|
|
'--database=' . $this->targetConfig['database'],
|
|
);
|
|
$this->runProcess($command, $backup, array('MYSQL_PWD' => $this->targetConfig['password']));
|
|
}
|
|
|
|
private function runTargetMigrations()
|
|
{
|
|
$environment = array(
|
|
'DB_HOST' => $this->targetConfig['host'],
|
|
'DB_PORT' => (string) $this->targetConfig['port'],
|
|
'DB_USERNAME' => $this->targetConfig['username'],
|
|
'DB_PASSWORD' => $this->targetConfig['password'],
|
|
'DB_DATABASE' => $this->targetConfig['database'],
|
|
);
|
|
$this->runProcess(
|
|
array(PHP_BINARY, FCPATH . 'index.php', 'migrate/latest'),
|
|
null,
|
|
$environment,
|
|
FCPATH
|
|
);
|
|
}
|
|
|
|
private function runProcess($command, $stdinFile = null, $environment = array(), $workingDirectory = null)
|
|
{
|
|
$descriptors = array(
|
|
0 => $stdinFile ? array('file', $stdinFile, 'rb') : array('pipe', 'r'),
|
|
1 => array('pipe', 'w'),
|
|
2 => array('pipe', 'w'),
|
|
);
|
|
$processEnvironment = app_env_all();
|
|
foreach ($environment as $key => $value) {
|
|
$processEnvironment[$key] = $value;
|
|
}
|
|
|
|
$process = proc_open($command, $descriptors, $pipes, $workingDirectory, $processEnvironment);
|
|
if (!is_resource($process)) {
|
|
throw new RuntimeException('Proses database tidak dapat dijalankan.');
|
|
}
|
|
if (!$stdinFile) {
|
|
fclose($pipes[0]);
|
|
}
|
|
$stdout = stream_get_contents($pipes[1]);
|
|
$stderr = stream_get_contents($pipes[2]);
|
|
fclose($pipes[1]);
|
|
fclose($pipes[2]);
|
|
$exitCode = proc_close($process);
|
|
if ($exitCode !== 0) {
|
|
throw new RuntimeException(trim($stderr ?: $stdout ?: 'Proses database gagal.') . ' (exit ' . $exitCode . ')');
|
|
}
|
|
return trim($stdout);
|
|
}
|
|
|
|
private function latestMigrationVersion()
|
|
{
|
|
$versions = array();
|
|
foreach (glob(APPPATH . 'migrations' . DIRECTORY_SEPARATOR . '*.php') as $file) {
|
|
if (preg_match('/^(\\d+)_/', basename($file), $match)) {
|
|
$versions[] = (int) $match[1];
|
|
}
|
|
}
|
|
return $versions ? max($versions) : 0;
|
|
}
|
|
|
|
private function scalar($connection, $sql)
|
|
{
|
|
$row = $connection->query($sql)->fetch_row();
|
|
return (int) $row[0];
|
|
}
|
|
|
|
private function identifier($value)
|
|
{
|
|
return '`' . str_replace('`', '``', $value) . '`';
|
|
}
|
|
}
|