WebOrbiton
v1.0.1

AtheraStudio Database

304 lines · 14.8 KB
  1. <?php
  2. declare(strict_types=1);
  3. ​
  4. namespace Athera;
  5. ​
  6. defined('ATHERA') || exit;
  7. ​
  8. use PDO;
  9. ​
  10. final class Export
  11. {
  12. private $out;
  13. private ?Atbde $enc = null;
  14. private $gz = null;
  15. private string $buf = '';
  16. ​
  17. public static function check(array $in): array
  18. {
  19. $p = self::prepare(Auth::db(), $in);
  20. return ['tables' => count($p[2])];
  21. }
  22. ​
  23. private static function prepare(Db $db, array $in): array
  24. {
  25. $d = (string)($in['db'] ?? '');
  26. if ($d === '') throw new UserError('Missing database', 400);
  27. $format = (string)($in['format'] ?? 'sql');
  28. if (!in_array($format, ['sql', 'csv', 'json', 'atbde'], true)) throw new UserError('Unknown format', 400);
  29. $tables = array_values(array_filter(array_map('strval', (array)($in['tables'] ?? [])), 'strlen'));
  30. $known = $db->all('SELECT TABLE_NAME AS n, TABLE_TYPE AS t FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME', [$d]);
  31. $types = array_column($known, 't', 'n');
  32. if (!$types && !$tables) {
  33. if (!in_array($d, $db->column('SHOW DATABASES'), true)) throw new UserError('Database not found', 404);
  34. }
  35. $tables = $tables ? array_values(array_filter($tables, function ($t) use ($types) { return isset($types[$t]); })) : array_keys($types);
  36. if ($format === 'csv' && count($tables) !== 1) throw new UserError('CSV export needs exactly one table', 400, 'csv_one');
  37. if (!$tables && $format !== 'sql' && $format !== 'atbde') throw new UserError('Nothing to export', 400, 'empty');
  38. $password = (string)($in['password'] ?? '');
  39. if ($format === 'atbde') Atbde::checkPassword($password);
  40. $gzip = !empty($in['gzip']) && $format !== 'atbde';
  41. if ($gzip && !function_exists('deflate_init')) throw new UserError('zlib is not available', 500);
  42. return [$d, $format, $tables, $types, $password, $gzip];
  43. }
  44. ​
  45. public static function run(array $in): void
  46. {
  47. $db = Auth::db();
  48. [$d, $format, $tables, $types, $password, $gzip] = self::prepare($db, $in);
  49. Http::timeBudget(86400);
  50. @ignore_user_abort(false);
  51. $base = preg_replace('/[^\w.\-]+/u', '_', $d) . (count($tables) === 1 && !empty($in['tables']) ? '-' . preg_replace('/[^\w.\-]+/u', '_', $tables[0]) : '') . '-' . date('Ymd-His');
  52. $ext = ['sql' => 'sql', 'csv' => 'csv', 'json' => 'json', 'atbde' => 'atbde'][$format] . ($gzip ? '.gz' : '');
  53. $mime = $format === 'atbde' ? 'application/octet-stream' : ($gzip ? 'application/gzip' : ($format === 'csv' ? 'text/csv' : ($format === 'json' ? 'application/json' : 'application/sql')));
  54. while (ob_get_level() > 0) @ob_end_clean();
  55. header('Content-Type: ' . $mime);
  56. header('Content-Disposition: attachment; filename="' . $base . '.' . $ext . '"; filename*=UTF-8\'\'' . rawurlencode($base . '.' . $ext));
  57. header('X-Accel-Buffering: no');
  58. $x = new self();
  59. $x->out = function (string $s): void { echo $s; };
  60. if ($format === 'atbde') {
  61. $sink = $x->out;
  62. $x->enc = new Atbde($password, Atbde::SQL_MYSQL, $sink);
  63. } elseif ($gzip) {
  64. $x->gz = deflate_init(ZLIB_ENCODING_GZIP, ['level' => 6]);
  65. }
  66. $db->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
  67. $db->exec('START TRANSACTION WITH CONSISTENT SNAPSHOT');
  68. $db->exec("SET SESSION time_zone = '+00:00'");
  69. if ($format === 'csv') $x->csv($db, $d, $tables, $in);
  70. elseif ($format === 'json') $x->json($db, $d, $tables, $types);
  71. else $x->sql($db, $d, $tables, $types, $in);
  72. $db->exec('COMMIT');
  73. $x->close();
  74. Security::log(['ev' => 'export', 'user' => Auth::user(), 'db' => $d, 'tables' => count($tables), 'format' => $format . ($gzip ? '.gz' : '')]);
  75. }
  76. ​
  77. private function w(string $s): void
  78. {
  79. $this->buf .= $s;
  80. if (strlen($this->buf) >= 262144) $this->flushBuf();
  81. }
  82. ​
  83. private function flushBuf(): void
  84. {
  85. if ($this->buf === '') return;
  86. $data = $this->buf;
  87. $this->buf = '';
  88. if ($this->enc) $this->enc->write($data);
  89. elseif ($this->gz) ($this->out)(deflate_add($this->gz, $data, ZLIB_NO_FLUSH));
  90. else ($this->out)($data);
  91. flush();
  92. if (connection_aborted()) exit;
  93. }
  94. ​
  95. private function close(): void
  96. {
  97. $this->flushBuf();
  98. if ($this->enc) $this->enc->finish();
  99. if ($this->gz) ($this->out)(deflate_add($this->gz, '', ZLIB_FINISH));
  100. flush();
  101. }
  102. ​
  103. private static function lit($v, bool $bin): string
  104. {
  105. if ($v === null) return 'NULL';
  106. if (is_int($v) || is_float($v)) return (string)$v;
  107. if ($bin || !mb_check_encoding((string)$v, 'UTF-8')) return $v === '' ? "''" : '0x' . bin2hex((string)$v);
  108. return "'" . strtr((string)$v, ["\\" => "\\\\", "\0" => "\\0", "\n" => "\\n", "\r" => "\\r", "'" => "\\'", "\x1a" => "\\Z"]) . "'";
  109. }
  110. ​
  111. private function sql(Db $db, string $d, array $tables, array $types, array $in): void
  112. {
  113. $o = $in + ['structure' => true, 'data' => true, 'drop' => true, 'ifNotExists' => false, 'routines' => true, 'triggers' => true, 'events' => false, 'definer' => false, 'createDb' => false, 'rowsPerInsert' => 500];
  114. $per = max(1, min(10000, (int)$o['rowsPerInsert']));
  115. $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");
  116. $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");
  117. if ($o['createDb']) {
  118. $c = $db->one('SHOW CREATE DATABASE ' . Db::id($d));
  119. $this->w(preg_replace('/^CREATE DATABASE /', 'CREATE DATABASE IF NOT EXISTS ', (string)array_values($c)[1]) . ";\nUSE " . Db::id($d) . ";\n\n");
  120. }
  121. $base = array_values(array_filter($tables, function ($t) use ($types) { return $types[$t] !== 'VIEW'; }));
  122. $views = array_values(array_filter($tables, function ($t) use ($types) { return $types[$t] === 'VIEW'; }));
  123. foreach ($base as $t) {
  124. $id = Db::id($t);
  125. if ($o['structure']) {
  126. $create = (string)$db->run('SHOW CREATE TABLE ' . Db::tid($d, $t))->fetch(PDO::FETCH_NUM)[1];
  127. if ($o['ifNotExists']) $create = preg_replace('/^CREATE TABLE /', 'CREATE TABLE IF NOT EXISTS ', $create);
  128. $this->w("-- Table " . $id . "\n" . ($o['drop'] ? 'DROP TABLE IF EXISTS ' . $id . ";\n" : '') . $create . ";\n\n");
  129. }
  130. if ($o['data']) $this->tableData($db, $d, $t, $per);
  131. if ($o['triggers']) $this->triggers($db, $d, $t, (bool)$o['definer']);
  132. }
  133. if ($views && $o['structure']) {
  134. $defs = [];
  135. foreach ($views as $v) {
  136. $c = (string)$db->run('SHOW CREATE VIEW ' . Db::tid($d, $v))->fetch(PDO::FETCH_NUM)[1];
  137. if (!$o['definer']) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
  138. $c = str_replace(Db::id($d) . '.', '', $c);
  139. $defs[$v] = $c;
  140. }
  141. foreach (self::viewOrder($defs) as $v) {
  142. $this->w("-- View " . Db::id($v) . "\n" . ($o['drop'] ? 'DROP VIEW IF EXISTS ' . Db::id($v) . ";\n" : '') . $defs[$v] . ";\n\n");
  143. }
  144. }
  145. if ($o['routines'] && $o['structure']) $this->routines($db, $d, (bool)$o['definer']);
  146. if ($o['events'] && $o['structure']) $this->events($db, $d, (bool)$o['definer']);
  147. $this->w("SET FOREIGN_KEY_CHECKS = 1;\nSET UNIQUE_CHECKS = 1;\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n");
  148. }
  149. ​
  150. private static function viewOrder(array $defs): array
  151. {
  152. $out = [];
  153. $seen = [];
  154. $visit = function ($v) use (&$visit, &$out, &$seen, $defs) {
  155. if (isset($seen[$v])) return;
  156. $seen[$v] = true;
  157. foreach ($defs as $o => $_) {
  158. if ($o !== $v && str_contains($defs[$v], Db::id($o))) $visit($o);
  159. }
  160. $out[] = $v;
  161. };
  162. foreach (array_keys($defs) as $v) $visit($v);
  163. return $out;
  164. }
  165. ​
  166. private function binCols(Db $db, string $d, string $t): array
  167. {
  168. $bin = [];
  169. 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) {
  170. $bin[$i] = (bool)preg_match('/^(binary|varbinary|tinyblob|blob|mediumblob|longblob|bit|geometry|point|linestring|polygon|multi\w+|geometrycollection)$/i', $r['y']);
  171. }
  172. return $bin;
  173. }
  174. ​
  175. private function tableData(Db $db, string $d, string $t, int $per): void
  176. {
  177. $bin = $this->binCols($db, $d, $t);
  178. $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]);
  179. $cols = $db->column('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION', [$d, $t]);
  180. $keep = [];
  181. foreach ($cols as $i => $c) if (!in_array($c, $gen, true)) $keep[$i] = $c;
  182. if (!$keep) return;
  183. $head = 'INSERT INTO ' . Db::id($t) . ' (' . implode(', ', array_map([Db::class, 'id'], $keep)) . ") VALUES\n";
  184. $db->buffered(false);
  185. $st = $db->run('SELECT ' . implode(', ', array_map([Db::class, 'id'], $keep)) . ' FROM ' . Db::tid($d, $t));
  186. $idx = array_keys($keep);
  187. $n = 0;
  188. $bytes = 0;
  189. while (($r = $st->fetch(PDO::FETCH_NUM)) !== false) {
  190. $vals = [];
  191. foreach ($r as $j => $v) $vals[] = self::lit($v, $bin[$idx[$j]] ?? false);
  192. $row = '(' . implode(',', $vals) . ')';
  193. if ($n === 0) $this->w($head);
  194. else $this->w(",\n");
  195. $this->w($row);
  196. $n++;
  197. $bytes += strlen($row);
  198. if ($n >= $per || $bytes > 1048576) {
  199. $this->w(";\n");
  200. $n = 0;
  201. $bytes = 0;
  202. }
  203. }
  204. if ($n > 0) $this->w(";\n");
  205. $st->closeCursor();
  206. $db->buffered(true);
  207. $this->w("\n");
  208. }
  209. ​
  210. private function triggers(Db $db, string $d, string $t, bool $definer): void
  211. {
  212. $list = $db->column('SELECT TRIGGER_NAME FROM information_schema.TRIGGERS WHERE EVENT_OBJECT_SCHEMA = ? AND EVENT_OBJECT_TABLE = ? ORDER BY ACTION_ORDER', [$d, $t]);
  213. if (!$list) return;
  214. $this->w("DELIMITER ;;\n");
  215. foreach ($list as $tr) {
  216. $c = (string)$db->run('SHOW CREATE TRIGGER ' . Db::tid($d, $tr))->fetch(PDO::FETCH_NUM)[2];
  217. if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
  218. $this->w('DROP TRIGGER IF EXISTS ' . Db::id($tr) . ";;\n" . $c . ";;\n");
  219. }
  220. $this->w("DELIMITER ;\n\n");
  221. }
  222. ​
  223. private function routines(Db $db, string $d, bool $definer): void
  224. {
  225. $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]);
  226. if (!$list) return;
  227. $this->w("DELIMITER ;;\n");
  228. foreach ($list as $r) {
  229. $row = $db->run('SHOW CREATE ' . $r['t'] . ' ' . Db::tid($d, $r['n']))->fetch(PDO::FETCH_NUM);
  230. $c = (string)($row[2] ?? '');
  231. if ($c === '') continue;
  232. if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
  233. $this->w('DROP ' . $r['t'] . ' IF EXISTS ' . Db::id($r['n']) . ";;\n" . $c . ";;\n");
  234. }
  235. $this->w("DELIMITER ;\n\n");
  236. }
  237. ​
  238. private function events(Db $db, string $d, bool $definer): void
  239. {
  240. try {
  241. $list = $db->column('SELECT EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA = ?', [$d]);
  242. } catch (\PDOException $e) {
  243. return;
  244. }
  245. if (!$list) return;
  246. $this->w("DELIMITER ;;\n");
  247. foreach ($list as $e) {
  248. $c = (string)$db->run('SHOW CREATE EVENT ' . Db::tid($d, $e))->fetch(PDO::FETCH_NUM)[3];
  249. if (!$definer) $c = preg_replace('/\sDEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`/', '', $c);
  250. $this->w('DROP EVENT IF EXISTS ' . Db::id($e) . ";;\n" . $c . ";;\n");
  251. }
  252. $this->w("DELIMITER ;\n\n");
  253. }
  254. ​
  255. private function csv(Db $db, string $d, array $tables, array $in): void
  256. {
  257. if (count($tables) !== 1) throw new UserError('CSV export needs exactly one table', 400);
  258. $sep = (string)($in['sep'] ?? ',');
  259. $sep = $sep === '\t' || $sep === 'tab' ? "\t" : mb_substr($sep, 0, 1);
  260. if ($sep === '') $sep = ',';
  261. $q = '"';
  262. $esc = function ($v) use ($sep, $q): string {
  263. if ($v === null) return '';
  264. $v = (string)$v;
  265. if (!mb_check_encoding($v, 'UTF-8')) $v = '0x' . bin2hex($v);
  266. if (strpbrk($v, $sep . $q . "\r\n") !== false || ($v !== '' && ($v[0] === ' ' || substr($v, -1) === ' '))) return $q . str_replace($q, $q . $q, $v) . $q;
  267. return $v;
  268. };
  269. $this->w("\xEF\xBB\xBF");
  270. $db->buffered(false);
  271. $st = $db->run('SELECT * FROM ' . Db::tid($d, $tables[0]));
  272. if (!empty($in['header']) || !isset($in['header'])) {
  273. $h = [];
  274. for ($i = 0, $n = $st->columnCount(); $i < $n; $i++) $h[] = $esc($st->getColumnMeta($i)['name'] ?? '');
  275. $this->w(implode($sep, $h) . "\r\n");
  276. }
  277. while (($r = $st->fetch(PDO::FETCH_NUM)) !== false) {
  278. $this->w(implode($sep, array_map($esc, $r)) . "\r\n");
  279. }
  280. $st->closeCursor();
  281. $db->buffered(true);
  282. }
  283. ​
  284. private function json(Db $db, string $d, array $tables, array $types): void
  285. {
  286. $flags = JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE | JSON_PARTIAL_OUTPUT_ON_ERROR;
  287. $this->w('{"database":' . json_encode($d, $flags) . ',"exported":' . json_encode(gmdate('c')) . ',"tables":{');
  288. foreach ($tables as $i => $t) {
  289. $this->w(($i ? ',' : '') . "\n" . json_encode($t, $flags) . ':[');
  290. $db->buffered(false);
  291. $st = $db->run('SELECT * FROM ' . Db::tid($d, $t));
  292. $n = 0;
  293. while (($r = $st->fetch(PDO::FETCH_ASSOC)) !== false) {
  294. foreach ($r as $k => $v) if (is_string($v) && !mb_check_encoding($v, 'UTF-8')) $r[$k] = ['$base64' => base64_encode($v)];
  295. $this->w(($n++ ? ',' : '') . "\n" . json_encode($r, $flags));
  296. }
  297. $st->closeCursor();
  298. $db->buffered(true);
  299. $this->w(']');
  300. }
  301. $this->w("\n}}\n");
  302. }
  303. }
  304. ​