v1.0.1
AtheraStudio Database
- <?php
- declare(strict_types=1);
-
- namespace Athera;
-
- defined('ATHERA') || exit;
-
- use PDO;
-
- final class Export
- {
- private $out;
- private ?Atbde $enc = null;
- private $gz = null;
- private string $buf = '';
-
- public static function check(array $in): array
- {
- $p = self::prepare(Auth::db(), $in);
- return ['tables' => count($p[2])];
- }
-
- private static function prepare(Db $db, array $in): array
- {
- $d = (string)($in['db'] ?? '');
- if ($d === '') throw new UserError('Missing database', 400);
- $format = (string)($in['format'] ?? 'sql');
- if (!in_array($format, ['sql', 'csv', 'json', 'atbde'], true)) throw new UserError('Unknown format', 400);
- $tables = array_values(array_filter(array_map('strval', (array)($in['tables'] ?? [])), 'strlen'));
- $known = $db->all('SELECT TABLE_NAME AS n, TABLE_TYPE AS t FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME', [$d]);
- $types = array_column($known, 't', 'n');
- if (!$types && !$tables) {
- if (!in_array($d, $db->column('SHOW DATABASES'), true)) throw new UserError('Database not found', 404);
- }
- $tables = $tables ? array_values(array_filter($tables, function ($t) use ($types) { return isset($types[$t]); })) : array_keys($types);
- if ($format === 'csv' && count($tables) !== 1) throw new UserError('CSV export needs exactly one table', 400, 'csv_one');
- if (!$tables && $format !== 'sql' && $format !== 'atbde') throw new UserError('Nothing to export', 400, 'empty');
- $password = (string)($in['password'] ?? '');
- if ($format === 'atbde') Atbde::checkPassword($password);
- $gzip = !empty($in['gzip']) && $format !== 'atbde';
- if ($gzip && !function_exists('deflate_init')) throw new UserError('zlib is not available', 500);
- return [$d, $format, $tables, $types, $password, $gzip];
- }
-
- public static function run(array $in): void
- {
- $db = Auth::db();
- [$d, $format, $tables, $types, $password, $gzip] = self::prepare($db, $in);
- Http::timeBudget(86400);
- @ignore_user_abort(false);
- $base = preg_replace('/[^\w.\-]+/u', '_', $d) . (count($tables) === 1 && !empty($in['tables']) ? '-' . preg_replace('/[^\w.\-]+/u', '_', $tables[0]) : '') . '-' . date('Ymd-His');
- $ext = ['sql' => 'sql', 'csv' => 'csv', 'json' => 'json', 'atbde' => 'atbde'][$format] . ($gzip ? '.gz' : '');
- $mime = $format === 'atbde' ? 'application/octet-stream' : ($gzip ? 'application/gzip' : ($format === 'csv' ? 'text/csv' : ($format === 'json' ? 'application/json' : 'application/sql')));
- while (ob_get_level() > 0) @ob_end_clean();
- header('Content-Type: ' . $mime);
- header('Content-Disposition: attachment; filename="' . $base . '.' . $ext . '"; filename*=UTF-8\'\'' . rawurlencode($base . '.' . $ext));
- header('X-Accel-Buffering: no');
- $x = new self();
- $x->out = function (string $s): void { echo $s; };
- if ($format === 'atbde') {
- $sink = $x->out;
- $x->enc = new Atbde($password, Atbde::SQL_MYSQL, $sink);
- } elseif ($gzip) {
- $x->gz = deflate_init(ZLIB_ENCODING_GZIP, ['level' => 6]);
- }
- $db->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
- $db->exec('START TRANSACTION WITH CONSISTENT SNAPSHOT');
- $db->exec("SET SESSION time_zone = '+00:00'");
- if ($format === 'csv') $x->csv($db, $d, $tables, $in);
- elseif ($format === 'json') $x->json($db, $d, $tables, $types);
- else $x->sql($db, $d, $tables, $types, $in);
- $db->exec('COMMIT');
- $x->close();
- Security::log(['ev' => 'export', 'user' => Auth::user(), 'db' => $d, 'tables' => count($tables), 'format' => $format . ($gzip ? '.gz' : '')]);
- }
-
- private function w(string $s): void
- {
- $this->buf .= $s;
- if (strlen($this->buf) >= 262144) $this->flushBuf();
- }
-
- private function flushBuf(): void
- {
- if ($this->buf === '') return;
- $data = $this->buf;
- $this->buf = '';
- if ($this->enc) $this->enc->write($data);
- elseif ($this->gz) ($this->out)(deflate_add($this->gz, $data, ZLIB_NO_FLUSH));
- else ($this->out)($data);
- flush();
- if (connection_aborted()) exit;
- }
-
- private function close(): void
- {
- $this->flushBuf();
- if ($this->enc) $this->enc->finish();
- if ($this->gz) ($this->out)(deflate_add($this->gz, '', ZLIB_FINISH));
- flush();
- }
-
- private static function lit($v, bool $bin): string
- {
- if ($v === null) return 'NULL';
- if (is_int($v) || is_float($v)) return (string)$v;
- if ($bin || !mb_check_encoding((string)$v, 'UTF-8')) return $v === '' ? "''" : '0x' . bin2hex((string)$v);
- return "'" . strtr((string)$v, ["\\" => "\\\\", "\0" => "\\0", "\n" => "\\n", "\r" => "\\r", "'" => "\\'", "\x1a" => "\\Z"]) . "'";
- }
-
- private function sql(Db $db, string $d, array $tables, array $types, array $in): void
- {
- $o = $in + ['structure' => true, 'data' => true, 'drop' => true, 'ifNotExists' => false, 'routines' => true, 'triggers' => true, 'events' => false, 'definer' => false, 'createDb' => false, 'rowsPerInsert' => 500];
- $per = max(1, min(10000, (int)$o['rowsPerInsert']));
- $this->w("-- AtheraStudio Database " . VERSION . " SQL dump\n-- Server: " . $db->version() . "\n-- Database: " . $d . "\n-- Created: " . gmdate('Y-m-d H:i:s') . " UTC\n\n");
- $this->w("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\nSET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\nSET UNIQUE_CHECKS = 0;\nSET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\nSET time_zone = '+00:00';\n\n");
- if ($o['createDb']) {
- $c = $db->one('SHOW CREATE DATABASE ' . Db::id($d));
- $this->w(preg_replace('/^CREATE DATABASE /', 'CREATE DATABASE IF NOT EXISTS ', (string)array_values($c)[1]) . ";\nUSE " . Db::id($d) . ";\n\n");
- }
- $base = array_values(array_filter($tables, function ($t) use ($types) { return $types[$t] !== 'VIEW'; }));
- $views = array_values(array_filter($tables, function ($t) use ($types) { return $types[$t] === 'VIEW'; }));
- foreach ($base as $t) {
- $id = Db::id($t);
- if ($o['structure']) {
- $create = (string)$db->run('SHOW CREATE TABLE ' . Db::tid($d, $t))->fetch(PDO::FETCH_NUM)[1];
- if ($o['ifNotExists']) $create = preg_replace('/^CREATE TABLE /', 'CREATE TABLE IF NOT EXISTS ', $create);
- $this->w("-- Table " . $id . "\n" . ($o['drop'] ? 'DROP TABLE IF EXISTS ' . $id . ";\n" : '') . $create . ";\n\n");
- }
- if ($o['data']) $this->tableData($db, $d, $t, $per);
- if ($o['triggers']) $this->triggers($db, $d, $t, (bool)$o['definer']);
- }
- if ($views && $o['structure']) {
- $defs = [];
- foreach ($views as $v) {
- $c = (string)$db->run('SHOW CREATE VIEW ' . Db::tid($d, $v))->fetch(PDO::FETCH_NUM)[1];
- if (!$o['definer']) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
- $c = str_replace(Db::id($d) . '.', '', $c);
- $defs[$v] = $c;
- }
- foreach (self::viewOrder($defs) as $v) {
- $this->w("-- View " . Db::id($v) . "\n" . ($o['drop'] ? 'DROP VIEW IF EXISTS ' . Db::id($v) . ";\n" : '') . $defs[$v] . ";\n\n");
- }
- }
- if ($o['routines'] && $o['structure']) $this->routines($db, $d, (bool)$o['definer']);
- if ($o['events'] && $o['structure']) $this->events($db, $d, (bool)$o['definer']);
- $this->w("SET FOREIGN_KEY_CHECKS = 1;\nSET UNIQUE_CHECKS = 1;\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n");
- }
-
- private static function viewOrder(array $defs): array
- {
- $out = [];
- $seen = [];
- $visit = function ($v) use (&$visit, &$out, &$seen, $defs) {
- if (isset($seen[$v])) return;
- $seen[$v] = true;
- foreach ($defs as $o => $_) {
- if ($o !== $v && str_contains($defs[$v], Db::id($o))) $visit($o);
- }
- $out[] = $v;
- };
- foreach (array_keys($defs) as $v) $visit($v);
- return $out;
- }
-
- private function binCols(Db $db, string $d, string $t): array
- {
- $bin = [];
- foreach ($db->all('SELECT COLUMN_NAME AS c, DATA_TYPE AS y FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION', [$d, $t]) as $i => $r) {
- $bin[$i] = (bool)preg_match('/^(binary|varbinary|tinyblob|blob|mediumblob|longblob|bit|geometry|point|linestring|polygon|multi\w+|geometrycollection)$/i', $r['y']);
- }
- return $bin;
- }
-
- private function tableData(Db $db, string $d, string $t, int $per): void
- {
- $bin = $this->binCols($db, $d, $t);
- $gen = $db->column("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND (EXTRA LIKE '%GENERATED%' OR EXTRA LIKE '%VIRTUAL%' OR EXTRA LIKE '%PERSISTENT%' OR EXTRA LIKE '%STORED%') ", [$d, $t]);
- $cols = $db->column('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION', [$d, $t]);
- $keep = [];
- foreach ($cols as $i => $c) if (!in_array($c, $gen, true)) $keep[$i] = $c;
- if (!$keep) return;
- $head = 'INSERT INTO ' . Db::id($t) . ' (' . implode(', ', array_map([Db::class, 'id'], $keep)) . ") VALUES\n";
- $db->buffered(false);
- $st = $db->run('SELECT ' . implode(', ', array_map([Db::class, 'id'], $keep)) . ' FROM ' . Db::tid($d, $t));
- $idx = array_keys($keep);
- $n = 0;
- $bytes = 0;
- while (($r = $st->fetch(PDO::FETCH_NUM)) !== false) {
- $vals = [];
- foreach ($r as $j => $v) $vals[] = self::lit($v, $bin[$idx[$j]] ?? false);
- $row = '(' . implode(',', $vals) . ')';
- if ($n === 0) $this->w($head);
- else $this->w(",\n");
- $this->w($row);
- $n++;
- $bytes += strlen($row);
- if ($n >= $per || $bytes > 1048576) {
- $this->w(";\n");
- $n = 0;
- $bytes = 0;
- }
- }
- if ($n > 0) $this->w(";\n");
- $st->closeCursor();
- $db->buffered(true);
- $this->w("\n");
- }
-
- private function triggers(Db $db, string $d, string $t, bool $definer): void
- {
- $list = $db->column('SELECT TRIGGER_NAME FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_SCHEMA = ? AND EVENT_OBJECT_TABLE = ? ORDER BY ACTION_ORDER', [$d, $t]);
- if (!$list) return;
- $this->w("DELIMITER ;;\n");
- foreach ($list as $tr) {
- $c = (string)$db->run('SHOW CREATE TRIGGER ' . Db::tid($d, $tr))->fetch(PDO::FETCH_NUM)[2];
- if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
- $this->w('DROP TRIGGER IF EXISTS ' . Db::id($tr) . ";;\n" . $c . ";;\n");
- }
- $this->w("DELIMITER ;\n\n");
- }
-
- private function routines(Db $db, string $d, bool $definer): void
- {
- $list = $db->all('SELECT ROUTINE_NAME AS n, ROUTINE_TYPE AS t FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = ? ORDER BY ROUTINE_TYPE, ROUTINE_NAME', [$d]);
- if (!$list) return;
- $this->w("DELIMITER ;;\n");
- foreach ($list as $r) {
- $row = $db->run('SHOW CREATE ' . $r['t'] . ' ' . Db::tid($d, $r['n']))->fetch(PDO::FETCH_NUM);
- $c = (string)($row[2] ?? '');
- if ($c === '') continue;
- if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
- $this->w('DROP ' . $r['t'] . ' IF EXISTS ' . Db::id($r['n']) . ";;\n" . $c . ";;\n");
- }
- $this->w("DELIMITER ;\n\n");
- }
-
- private function events(Db $db, string $d, bool $definer): void
- {
- try {
- $list = $db->column('SELECT EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA = ?', [$d]);
- } catch (\PDOException $e) {
- return;
- }
- if (!$list) return;
- $this->w("DELIMITER ;;\n");
- foreach ($list as $e) {
- $c = (string)$db->run('SHOW CREATE EVENT ' . Db::tid($d, $e))->fetch(PDO::FETCH_NUM)[3];
- if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
- $this->w('DROP EVENT IF EXISTS ' . Db::id($e) . ";;\n" . $c . ";;\n");
- }
- $this->w("DELIMITER ;\n\n");
- }
-
- private function csv(Db $db, string $d, array $tables, array $in): void
- {
- if (count($tables) !== 1) throw new UserError('CSV export needs exactly one table', 400);
- $sep = (string)($in['sep'] ?? ',');
- $sep = $sep === '\t' || $sep === 'tab' ? "\t" : mb_substr($sep, 0, 1);
- if ($sep === '') $sep = ',';
- $q = '"';
- $esc = function ($v) use ($sep, $q): string {
- if ($v === null) return '';
- $v = (string)$v;
- if (!mb_check_encoding($v, 'UTF-8')) $v = '0x' . bin2hex($v);
- if (strpbrk($v, $sep . $q . "\r\n") !== false || ($v !== '' && ($v[0] === ' ' || substr($v, -1) === ' '))) return $q . str_replace($q, $q . $q, $v) . $q;
- return $v;
- };
- $this->w("\xEF\xBB\xBF");
- $db->buffered(false);
- $st = $db->run('SELECT * FROM ' . Db::tid($d, $tables[0]));
- if (!empty($in['header']) || !isset($in['header'])) {
- $h = [];
- for ($i = 0, $n = $st->columnCount(); $i < $n; $i++) $h[] = $esc($st->getColumnMeta($i)['name'] ?? '');
- $this->w(implode($sep, $h) . "\r\n");
- }
- while (($r = $st->fetch(PDO::FETCH_NUM)) !== false) {
- $this->w(implode($sep, array_map($esc, $r)) . "\r\n");
- }
- $st->closeCursor();
- $db->buffered(true);
- }
-
- private function json(Db $db, string $d, array $tables, array $types): void
- {
- $flags = JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE | JSON_PARTIAL_OUTPUT_ON_ERROR;
- $this->w('{"database":' . json_encode($d, $flags) . ',"exported":' . json_encode(gmdate('c')) . ',"tables":{');
- foreach ($tables as $i => $t) {
- $this->w(($i ? ',' : '') . "\n" . json_encode($t, $flags) . ':[');
- $db->buffered(false);
- $st = $db->run('SELECT * FROM ' . Db::tid($d, $t));
- $n = 0;
- while (($r = $st->fetch(PDO::FETCH_ASSOC)) !== false) {
- foreach ($r as $k => $v) if (is_string($v) && !mb_check_encoding($v, 'UTF-8')) $r[$k] = ['$base64' => base64_encode($v)];
- $this->w(($n++ ? ',' : '') . "\n" . json_encode($r, $flags));
- }
- $st->closeCursor();
- $db->buffered(true);
- $this->w(']');
- }
- $this->w("\n}}\n");
- }
- }
-