v1.0.1
AtheraStudio Database
- <?php
- declare(strict_types=1);
-
- namespace Athera;
-
- defined('ATHERA') || exit;
-
- use PDO;
-
- final class Import
- {
- private const READ = 1048576;
-
- private static function path(string $id, string $ext): string
- {
- if (!preg_match('/^[a-f0-9]{32}$/', $id)) throw new UserError('Invalid import id', 400);
- return Config::storage('tmp/' . $id . '.' . $ext);
- }
-
- private static function load(string $id): array
- {
- $f = self::path($id, 'json');
- $s = is_file($f) ? json_decode((string)file_get_contents($f), true) : null;
- if (!is_array($s) || !hash_equals((string)$s['owner'], hash('sha256', Auth::user()))) throw new UserError('Import not found', 404, 'import_missing');
- return $s;
- }
-
- private static function save(array $s): void
- {
- file_put_contents(self::path($s['id'], 'json'), json_encode($s, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE), LOCK_EX);
- }
-
- private static function gc(): void
- {
- $now = time();
- foreach (glob(Config::storage('tmp/*.{part,json}'), GLOB_BRACE) ?: [] as $f) {
- if (@filemtime($f) < $now - 86400) @unlink($f);
- }
- }
-
- public static function begin(array $in): array
- {
- self::gc();
- $kind = ($in['kind'] ?? 'sql') === 'csv' ? 'csv' : 'sql';
- $size = (int)($in['size'] ?? 0);
- if ($size < 0) throw new UserError('Invalid size', 400);
- $free = @disk_free_space(Config::storage('tmp'));
- if ($free !== false && $size > $free - 52428800) throw new UserError('Not enough disk space on the server', 507, 'disk');
- $id = bin2hex(random_bytes(16));
- $csv = is_array($in['csv'] ?? null) ? $in['csv'] : [];
- $s = [
- 'id' => $id,
- 'owner' => hash('sha256', Auth::user()),
- 'name' => mb_substr((string)($in['name'] ?? 'import'), 0, 200),
- 'kind' => $kind,
- 'size' => $size,
- 'db' => (string)($in['db'] ?? ''),
- 'table' => (string)($in['table'] ?? ''),
- 'offset' => 0,
- 'delimiter' => ';',
- 'done' => 0,
- 'failed' => 0,
- 'errors' => [],
- 'session' => [],
- 'finished' => false,
- 'fk' => !empty($in['fk']),
- 'stop' => !isset($in['stop']) || !empty($in['stop']),
- 'csv' => [
- 'sep' => in_array($csv['sep'] ?? ',', [',', ';', "\t", '|', 'tab'], true) ? (($csv['sep'] ?? ',') === 'tab' ? "\t" : ($csv['sep'] ?? ',')) : ',',
- 'header' => !isset($csv['header']) || !empty($csv['header']),
- 'mode' => in_array($csv['mode'] ?? '', ['ignore', 'replace'], true) ? $csv['mode'] : 'insert',
- 'cols' => null,
- ],
- 'started' => time(),
- ];
- if ($kind === 'csv' && ($s['db'] === '' || $s['table'] === '')) throw new UserError('CSV import needs a target table', 400);
- touch(self::path($id, 'part'));
- self::save($s);
- Security::log(['ev' => 'import_begin', 'user' => Auth::user(), 'db' => $s['db'], 'name' => $s['name'], 'size' => $size]);
- return ['id' => $id];
- }
-
- public static function chunk(array $q): array
- {
- $s = self::load((string)($q['id'] ?? ''));
- $file = self::path($s['id'], 'part');
- clearstatcache(true, $file);
- $have = (int)filesize($file);
- $offset = (int)($q['offset'] ?? -1);
- if ($offset !== $have) return ['received' => $have];
- $free = @disk_free_space(Config::storage('tmp'));
- if ($free !== false && $free < 67108864) {
- self::cancel($s['id']);
- throw new UserError('Not enough disk space on the server', 507, 'disk');
- }
- $in = fopen('php://input', 'rb');
- $out = fopen($file, 'ab');
- if (!$in || !$out) throw new \RuntimeException('Cannot store upload');
- flock($out, LOCK_EX);
- $n = stream_copy_to_stream($in, $out, 16777216);
- flock($out, LOCK_UN);
- fclose($out);
- fclose($in);
- if ($s['size'] > 0 && $have + $n > $s['size']) {
- self::cancel($s['id']);
- throw new UserError('Upload larger than declared', 400);
- }
- return ['received' => $have + (int)$n];
- }
-
- public static function cancel(string $id): void
- {
- @unlink(self::path($id, 'part'));
- @unlink(self::path($id, 'json'));
- }
-
- public static function run(array $in): array
- {
- $s = self::load((string)($in['id'] ?? ''));
- $file = self::path($s['id'], 'part');
- clearstatcache(true, $file);
- if ($s['size'] > 0 && filesize($file) < $s['size']) throw new UserError('Upload not complete', 409, 'incomplete');
- if (!empty($in['skip']) && !empty($s['error'])) {
- $s['offset'] = $s['error']['end'];
- $s['delimiter'] = $s['error']['delimiter'];
- }
- unset($s['error']);
- $db = Auth::db();
- $budget = microtime(true) + Http::timeBudget(20);
- if ($s['db'] !== '') $db->useDb($s['db']);
- foreach ($s['session'] as $q) {
- try { $db->exec($q); } catch (\PDOException $e) {}
- }
- if (!$s['fk']) $db->exec('SET FOREIGN_KEY_CHECKS = 0');
- $s = $s['kind'] === 'csv' ? self::runCsv($db, $s, $file, $budget) : self::runSql($db, $s, $file, $budget);
- $total = max(1, (int)filesize($file));
- if ($s['finished']) {
- @unlink($file);
- @unlink(self::path($s['id'], 'json'));
- Security::log(['ev' => 'import_done', 'user' => Auth::user(), 'db' => $s['db'], 'name' => $s['name'], 'ok' => $s['done'], 'failed' => $s['failed']]);
- } else {
- self::save($s);
- }
- return [
- 'offset' => $s['offset'],
- 'size' => $total,
- 'done' => $s['done'],
- 'failed' => $s['failed'],
- 'errors' => array_slice($s['errors'], -20),
- 'finished' => $s['finished'],
- 'error' => $s['error'] ?? null,
- ];
- }
-
- private static function sessionStatement(string $q): bool
- {
- return (bool)preg_match('/^(\/\*!\d+\s*)?(SET\s+(?!GLOBAL\b|PASSWORD\b|@@GLOBAL\.)|USE\s)/i', $q);
- }
-
- private static function runSql(Db $db, array $s, string $file, float $budget): array
- {
- $h = fopen($file, 'rb');
- fseek($h, $s['offset']);
- $sp = new SqlSplitter($s['delimiter'], $s['offset']);
- $db->pdo->beginTransaction();
- $inTx = true;
- $eof = false;
- try {
- while (true) {
- $st = $sp->next($eof);
- if ($st === null) {
- if ($eof) { $s['finished'] = true; break; }
- $chunk = fread($h, self::READ);
- if ($chunk === false || $chunk === '') { $eof = true; continue; }
- if ($s['offset'] === 0 && ftell($h) === strlen($chunk) && str_starts_with($chunk, "\xEF\xBB\xBF")) $chunk = ' ' . substr($chunk, 3);
- $sp->feed($chunk);
- continue;
- }
- $q = SqlSplitter::strip($st['sql']);
- if ($q === '' || preg_match('/^(START\s+TRANSACTION|BEGIN|COMMIT|ROLLBACK|SET\s+AUTOCOMMIT|LOCK\s+TABLES?|UNLOCK\s+TABLES?)\b/i', $q)) {
- $s['offset'] = $st['end'];
- $s['delimiter'] = $sp->delimiter;
- continue;
- }
- try {
- $r = $db->pdo->query($st['sql']);
- if ($r) { if ($r->columnCount() > 0) $r->fetchAll(); $r->closeCursor(); }
- $s['done']++;
- if (self::sessionStatement($q)) {
- $s['session'][] = $st['sql'];
- if (count($s['session']) > 50) array_shift($s['session']);
- }
- if (!$db->pdo->inTransaction()) { $db->pdo->beginTransaction(); }
- } catch (\PDOException $e) {
- $s['failed']++;
- $msg = Db::error($e);
- $s['errors'][] = ['message' => $msg, 'sql' => mb_substr($st['sql'], 0, 300)];
- if (count($s['errors']) > 200) array_shift($s['errors']);
- if ($s['stop']) {
- if ($db->pdo->inTransaction()) $db->pdo->commit();
- $s['error'] = ['message' => $msg, 'sql' => mb_substr($st['sql'], 0, 2000), 'end' => $st['end'], 'delimiter' => $sp->delimiter];
- return $s;
- }
- if (!$db->pdo->inTransaction()) $db->pdo->beginTransaction();
- }
- $s['offset'] = $st['end'];
- $s['delimiter'] = $sp->delimiter;
- if (microtime(true) > $budget) break;
- }
- if ($db->pdo->inTransaction()) $db->pdo->commit();
- } finally {
- fclose($h);
- }
- if ($s['finished']) $s['offset'] = (int)filesize($file);
- return $s;
- }
-
- private static function runCsv(Db $db, array $s, string $file, float $budget): array
- {
- $c = $s['csv'];
- $h = fopen($file, 'rb');
- fseek($h, $s['offset']);
- if ($s['offset'] === 0) {
- $bom = fread($h, 3);
- if ($bom !== "\xEF\xBB\xBF") fseek($h, 0);
- }
- $cols = $db->column('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION', [$s['db'], $s['table']]);
- if (!$cols) throw new UserError('Target table not found', 404);
- if ($c['cols'] === null) {
- if ($c['header']) {
- $head = fgetcsv($h, 0, $c['sep'], '"', '');
- $head = array_map(function ($x) { return trim((string)$x); }, $head ?: []);
- foreach ($head as $x) if (!in_array($x, $cols, true)) throw new UserError('CSV column not in table: ' . $x, 400, 'csv_cols');
- $c['cols'] = $head;
- } else {
- $c['cols'] = $cols;
- }
- $s['csv'] = $c;
- $s['offset'] = ftell($h);
- }
- $verb = $c['mode'] === 'ignore' ? 'INSERT IGNORE' : ($c['mode'] === 'replace' ? 'REPLACE' : 'INSERT');
- $head = $verb . ' INTO ' . Db::tid($s['db'], $s['table']) . ' (' . implode(', ', array_map([Db::class, 'id'], $c['cols'])) . ') VALUES ';
- $n = count($c['cols']);
- try {
- while (true) {
- $batch = [];
- $start = ftell($h);
- while (count($batch) < 500 && ($row = fgetcsv($h, 0, $c['sep'], '"', '')) !== false) {
- if ($row === [null]) continue;
- $row = array_slice(array_pad($row, $n, null), 0, $n);
- $batch[] = '(' . implode(',', array_map(function ($v) use ($db) { return $v === null || $v === '\\N' ? 'NULL' : $db->quote($v); }, $row)) . ')';
- }
- if (!$batch) { $s['finished'] = true; break; }
- try {
- $s['done'] += $db->exec($head . implode(',', $batch));
- } catch (\PDOException $e) {
- $s['failed'] += count($batch);
- $msg = Db::error($e);
- $s['errors'][] = ['message' => $msg, 'sql' => 'rows @' . $start];
- if ($s['stop']) {
- $s['error'] = ['message' => $msg, 'sql' => mb_substr($head, 0, 300) . '…', 'end' => ftell($h), 'delimiter' => ';'];
- $s['offset'] = $start;
- return $s;
- }
- }
- $s['offset'] = ftell($h);
- if (microtime(true) > $budget) break;
- }
- } finally {
- fclose($h);
- }
- return $s;
- }
- }
-