WebOrbiton
v1.0.1

AtheraStudio Database

275 lines · 11.6 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 Import
  11. {
  12. private const READ = 1048576;
  13. ​
  14. private static function path(string $id, string $ext): string
  15. {
  16. if (!preg_match('/^[a-f0-9]{32}$/', $id)) throw new UserError('Invalid import id', 400);
  17. return Config::storage('tmp/' . $id . '.' . $ext);
  18. }
  19. ​
  20. private static function load(string $id): array
  21. {
  22. $f = self::path($id, 'json');
  23. $s = is_file($f) ? json_decode((string)file_get_contents($f), true) : null;
  24. if (!is_array($s) || !hash_equals((string)$s['owner'], hash('sha256', Auth::user()))) throw new UserError('Import not found', 404, 'import_missing');
  25. return $s;
  26. }
  27. ​
  28. private static function save(array $s): void
  29. {
  30. file_put_contents(self::path($s['id'], 'json'), json_encode($s, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE), LOCK_EX);
  31. }
  32. ​
  33. private static function gc(): void
  34. {
  35. $now = time();
  36. foreach (glob(Config::storage('tmp/*.{part,json}'), GLOB_BRACE) ?: [] as $f) {
  37. if (@filemtime($f) < $now - 86400) @unlink($f);
  38. }
  39. }
  40. ​
  41. public static function begin(array $in): array
  42. {
  43. self::gc();
  44. $kind = ($in['kind'] ?? 'sql') === 'csv' ? 'csv' : 'sql';
  45. $size = (int)($in['size'] ?? 0);
  46. if ($size < 0) throw new UserError('Invalid size', 400);
  47. $free = @disk_free_space(Config::storage('tmp'));
  48. if ($free !== false && $size > $free - 52428800) throw new UserError('Not enough disk space on the server', 507, 'disk');
  49. $id = bin2hex(random_bytes(16));
  50. $csv = is_array($in['csv'] ?? null) ? $in['csv'] : [];
  51. $s = [
  52. 'id' => $id,
  53. 'owner' => hash('sha256', Auth::user()),
  54. 'name' => mb_substr((string)($in['name'] ?? 'import'), 0, 200),
  55. 'kind' => $kind,
  56. 'size' => $size,
  57. 'db' => (string)($in['db'] ?? ''),
  58. 'table' => (string)($in['table'] ?? ''),
  59. 'offset' => 0,
  60. 'delimiter' => ';',
  61. 'done' => 0,
  62. 'failed' => 0,
  63. 'errors' => [],
  64. 'session' => [],
  65. 'finished' => false,
  66. 'fk' => !empty($in['fk']),
  67. 'stop' => !isset($in['stop']) || !empty($in['stop']),
  68. 'csv' => [
  69. 'sep' => in_array($csv['sep'] ?? ',', [',', ';', "\t", '|', 'tab'], true) ? (($csv['sep'] ?? ',') === 'tab' ? "\t" : ($csv['sep'] ?? ',')) : ',',
  70. 'header' => !isset($csv['header']) || !empty($csv['header']),
  71. 'mode' => in_array($csv['mode'] ?? '', ['ignore', 'replace'], true) ? $csv['mode'] : 'insert',
  72. 'cols' => null,
  73. ],
  74. 'started' => time(),
  75. ];
  76. if ($kind === 'csv' && ($s['db'] === '' || $s['table'] === '')) throw new UserError('CSV import needs a target table', 400);
  77. touch(self::path($id, 'part'));
  78. self::save($s);
  79. Security::log(['ev' => 'import_begin', 'user' => Auth::user(), 'db' => $s['db'], 'name' => $s['name'], 'size' => $size]);
  80. return ['id' => $id];
  81. }
  82. ​
  83. public static function chunk(array $q): array
  84. {
  85. $s = self::load((string)($q['id'] ?? ''));
  86. $file = self::path($s['id'], 'part');
  87. clearstatcache(true, $file);
  88. $have = (int)filesize($file);
  89. $offset = (int)($q['offset'] ?? -1);
  90. if ($offset !== $have) return ['received' => $have];
  91. $free = @disk_free_space(Config::storage('tmp'));
  92. if ($free !== false && $free < 67108864) {
  93. self::cancel($s['id']);
  94. throw new UserError('Not enough disk space on the server', 507, 'disk');
  95. }
  96. $in = fopen('php://input', 'rb');
  97. $out = fopen($file, 'ab');
  98. if (!$in || !$out) throw new \RuntimeException('Cannot store upload');
  99. flock($out, LOCK_EX);
  100. $n = stream_copy_to_stream($in, $out, 16777216);
  101. flock($out, LOCK_UN);
  102. fclose($out);
  103. fclose($in);
  104. if ($s['size'] > 0 && $have + $n > $s['size']) {
  105. self::cancel($s['id']);
  106. throw new UserError('Upload larger than declared', 400);
  107. }
  108. return ['received' => $have + (int)$n];
  109. }
  110. ​
  111. public static function cancel(string $id): void
  112. {
  113. @unlink(self::path($id, 'part'));
  114. @unlink(self::path($id, 'json'));
  115. }
  116. ​
  117. public static function run(array $in): array
  118. {
  119. $s = self::load((string)($in['id'] ?? ''));
  120. $file = self::path($s['id'], 'part');
  121. clearstatcache(true, $file);
  122. if ($s['size'] > 0 && filesize($file) < $s['size']) throw new UserError('Upload not complete', 409, 'incomplete');
  123. if (!empty($in['skip']) && !empty($s['error'])) {
  124. $s['offset'] = $s['error']['end'];
  125. $s['delimiter'] = $s['error']['delimiter'];
  126. }
  127. unset($s['error']);
  128. $db = Auth::db();
  129. $budget = microtime(true) + Http::timeBudget(20);
  130. if ($s['db'] !== '') $db->useDb($s['db']);
  131. foreach ($s['session'] as $q) {
  132. try { $db->exec($q); } catch (\PDOException $e) {}
  133. }
  134. if (!$s['fk']) $db->exec('SET FOREIGN_KEY_CHECKS = 0');
  135. $s = $s['kind'] === 'csv' ? self::runCsv($db, $s, $file, $budget) : self::runSql($db, $s, $file, $budget);
  136. $total = max(1, (int)filesize($file));
  137. if ($s['finished']) {
  138. @unlink($file);
  139. @unlink(self::path($s['id'], 'json'));
  140. Security::log(['ev' => 'import_done', 'user' => Auth::user(), 'db' => $s['db'], 'name' => $s['name'], 'ok' => $s['done'], 'failed' => $s['failed']]);
  141. } else {
  142. self::save($s);
  143. }
  144. return [
  145. 'offset' => $s['offset'],
  146. 'size' => $total,
  147. 'done' => $s['done'],
  148. 'failed' => $s['failed'],
  149. 'errors' => array_slice($s['errors'], -20),
  150. 'finished' => $s['finished'],
  151. 'error' => $s['error'] ?? null,
  152. ];
  153. }
  154. ​
  155. private static function sessionStatement(string $q): bool
  156. {
  157. return (bool)preg_match('/^(\/\*!\d+\s*)?(SET\s+(?!GLOBAL\b|PASSWORD\b|@@GLOBAL\.)|USE\s)/i', $q);
  158. }
  159. ​
  160. private static function runSql(Db $db, array $s, string $file, float $budget): array
  161. {
  162. $h = fopen($file, 'rb');
  163. fseek($h, $s['offset']);
  164. $sp = new SqlSplitter($s['delimiter'], $s['offset']);
  165. $db->pdo->beginTransaction();
  166. $inTx = true;
  167. $eof = false;
  168. try {
  169. while (true) {
  170. $st = $sp->next($eof);
  171. if ($st === null) {
  172. if ($eof) { $s['finished'] = true; break; }
  173. $chunk = fread($h, self::READ);
  174. if ($chunk === false || $chunk === '') { $eof = true; continue; }
  175. if ($s['offset'] === 0 && ftell($h) === strlen($chunk) && str_starts_with($chunk, "\xEF\xBB\xBF")) $chunk = ' ' . substr($chunk, 3);
  176. $sp->feed($chunk);
  177. continue;
  178. }
  179. $q = SqlSplitter::strip($st['sql']);
  180. if ($q === '' || preg_match('/^(START\s+TRANSACTION|BEGIN|COMMIT|ROLLBACK|SET\s+AUTOCOMMIT|LOCK\s+TABLES?|UNLOCK\s+TABLES?)\b/i', $q)) {
  181. $s['offset'] = $st['end'];
  182. $s['delimiter'] = $sp->delimiter;
  183. continue;
  184. }
  185. try {
  186. $r = $db->pdo->query($st['sql']);
  187. if ($r) { if ($r->columnCount() > 0) $r->fetchAll(); $r->closeCursor(); }
  188. $s['done']++;
  189. if (self::sessionStatement($q)) {
  190. $s['session'][] = $st['sql'];
  191. if (count($s['session']) > 50) array_shift($s['session']);
  192. }
  193. if (!$db->pdo->inTransaction()) { $db->pdo->beginTransaction(); }
  194. } catch (\PDOException $e) {
  195. $s['failed']++;
  196. $msg = Db::error($e);
  197. $s['errors'][] = ['message' => $msg, 'sql' => mb_substr($st['sql'], 0, 300)];
  198. if (count($s['errors']) > 200) array_shift($s['errors']);
  199. if ($s['stop']) {
  200. if ($db->pdo->inTransaction()) $db->pdo->commit();
  201. $s['error'] = ['message' => $msg, 'sql' => mb_substr($st['sql'], 0, 2000), 'end' => $st['end'], 'delimiter' => $sp->delimiter];
  202. return $s;
  203. }
  204. if (!$db->pdo->inTransaction()) $db->pdo->beginTransaction();
  205. }
  206. $s['offset'] = $st['end'];
  207. $s['delimiter'] = $sp->delimiter;
  208. if (microtime(true) > $budget) break;
  209. }
  210. if ($db->pdo->inTransaction()) $db->pdo->commit();
  211. } finally {
  212. fclose($h);
  213. }
  214. if ($s['finished']) $s['offset'] = (int)filesize($file);
  215. return $s;
  216. }
  217. ​
  218. private static function runCsv(Db $db, array $s, string $file, float $budget): array
  219. {
  220. $c = $s['csv'];
  221. $h = fopen($file, 'rb');
  222. fseek($h, $s['offset']);
  223. if ($s['offset'] === 0) {
  224. $bom = fread($h, 3);
  225. if ($bom !== "\xEF\xBB\xBF") fseek($h, 0);
  226. }
  227. $cols = $db->column('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ORDER BY ORDINAL_POSITION', [$s['db'], $s['table']]);
  228. if (!$cols) throw new UserError('Target table not found', 404);
  229. if ($c['cols'] === null) {
  230. if ($c['header']) {
  231. $head = fgetcsv($h, 0, $c['sep'], '"', '');
  232. $head = array_map(function ($x) { return trim((string)$x); }, $head ?: []);
  233. foreach ($head as $x) if (!in_array($x, $cols, true)) throw new UserError('CSV column not in table: ' . $x, 400, 'csv_cols');
  234. $c['cols'] = $head;
  235. } else {
  236. $c['cols'] = $cols;
  237. }
  238. $s['csv'] = $c;
  239. $s['offset'] = ftell($h);
  240. }
  241. $verb = $c['mode'] === 'ignore' ? 'INSERT IGNORE' : ($c['mode'] === 'replace' ? 'REPLACE' : 'INSERT');
  242. $head = $verb . ' INTO ' . Db::tid($s['db'], $s['table']) . ' (' . implode(', ', array_map([Db::class, 'id'], $c['cols'])) . ') VALUES ';
  243. $n = count($c['cols']);
  244. try {
  245. while (true) {
  246. $batch = [];
  247. $start = ftell($h);
  248. while (count($batch) < 500 && ($row = fgetcsv($h, 0, $c['sep'], '"', '')) !== false) {
  249. if ($row === [null]) continue;
  250. $row = array_slice(array_pad($row, $n, null), 0, $n);
  251. $batch[] = '(' . implode(',', array_map(function ($v) use ($db) { return $v === null || $v === '\\N' ? 'NULL' : $db->quote($v); }, $row)) . ')';
  252. }
  253. if (!$batch) { $s['finished'] = true; break; }
  254. try {
  255. $s['done'] += $db->exec($head . implode(',', $batch));
  256. } catch (\PDOException $e) {
  257. $s['failed'] += count($batch);
  258. $msg = Db::error($e);
  259. $s['errors'][] = ['message' => $msg, 'sql' => 'rows @' . $start];
  260. if ($s['stop']) {
  261. $s['error'] = ['message' => $msg, 'sql' => mb_substr($head, 0, 300) . '…', 'end' => ftell($h), 'delimiter' => ';'];
  262. $s['offset'] = $start;
  263. return $s;
  264. }
  265. }
  266. $s['offset'] = ftell($h);
  267. if (microtime(true) > $budget) break;
  268. }
  269. } finally {
  270. fclose($h);
  271. }
  272. return $s;
  273. }
  274. }
  275. ​