| 1 | <?php |
| 2 | /************************************************************* |
| 3 | # 076 License |
| 4 | |
| 5 | Copyright (c) テクニカル諏訪子 |
| 6 | |
| 7 | Permission is hereby granted to any person obtaining a copy of the software |
| 8 | Little Beast (the "Software") to use, modify, merge, copy, publish, distribute, |
| 9 | sublicense, and/or sell copies of the Software, subject to the following conditions: |
| 10 | |
| 11 | 1. **Origin Attribution**: |
| 12 | - You must not misrepresent the origin of the Software; you must not claim |
| 13 | you created the original Software. |
| 14 | - If the Software is used in a product, you must either: |
| 15 | a. Provide clear attribution in the product's documentation, user interface, |
| 16 | or other visible areas, **OR** |
| 17 | b. Pay the original developers a fee they specify in writing. |
| 18 | 2. **Usage Restriction**: |
| 19 | - The Software, or any derivative works, dependencies, or libraries |
| 20 | incorporating it, must not be used for censorship or to suppress freedom of |
| 21 | speech, expression, or creativity. Prohibited uses include, but are not |
| 22 | limited to: |
| 23 | - Censorship of so-called "hate speech", visuals, non-mainstream opinions, |
| 24 | ideas, or objective reality. |
| 25 | - Tools or systems designed to restrict access to information or |
| 26 | artistic works. |
| 27 | 3. **Notice Preservation**: |
| 28 | - This license and the above copyright notice must remain intact in all copies |
| 29 | of the source code. |
| 30 | |
| 31 | THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, |
| 32 | INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A |
| 33 | PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT |
| 34 | HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF |
| 35 | CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE |
| 36 | OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| 37 | */ |
| 38 | namespace Std\Test; |
| 39 | |
| 40 | require_once __DIR__.'/../../../autoload.php'; |
| 41 | |
| 42 | use Std\Lib\Tester; |
| 43 | use Std\Lib\Db; |
| 44 | use Std\Lib\Db\Convert; |
| 45 | use Std\Lib\Db\Convert\DbSource; |
| 46 | use Std\Lib\Db\Convert\DbTarget; |
| 47 | use Std\Lib\Db\Convert\DumpSource; |
| 48 | use Std\Lib\Db\Convert\DumpTarget; |
| 49 | use Std\Lib\Db\Convert\JsonSource; |
| 50 | use Std\Lib\Db\Convert\JsonTarget; |
| 51 | use Std\Lib\Db\Convert\Sql; |
| 52 | use Std\Lib\Db\Convert\Stores; |
| 53 | use Std\Lib\Db\DbException; |
| 54 | use Std\Lib\Db\Sqlite\Blob; |
| 55 | use Std\Test\Helper\MockServer; |
| 56 | |
| 57 | $test = new Tester([ |
| 58 | 'colorOutput' => true, |
| 59 | 'verboseOutput' => true |
| 60 | ]); |
| 61 | |
| 62 | // 本物のサーバーでも試す場合（任意、lb_test_のテーブルを作って消す）：LB_TEST_PGSQL=pgsql://ユーザー:パスワード@ホスト:ポート/データベース |
| 63 | |
| 64 | $tmp = sys_get_temp_dir().'/lbconvert_'.bin2hex(random_bytes(6)); |
| 65 | mkdir($tmp, 0700); |
| 66 | register_shutdown_function(function() use ($tmp): void { |
| 67 | $it = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($tmp, \FilesystemIterator::SKIP_DOTS), \RecursiveIteratorIterator::CHILD_FIRST); |
| 68 | foreach ($it as $f) $f->isDir() ? @rmdir($f->getPathname()) : @unlink($f->getPathname()); |
| 69 | @rmdir($tmp); |
| 70 | }); |
| 71 | $fixtures = json_decode((string)file_get_contents(__DIR__.'/Helper/convert.json'), true); |
| 72 | |
| 73 | $catch = function(callable $fn): ?\Throwable { |
| 74 | try { |
| 75 | $fn(); |
| 76 | } catch (\Throwable $e) { |
| 77 | return $e; |
| 78 | } |
| 79 | return null; |
| 80 | }; |
| 81 | $sqlite = fn(string $name): Db => new Db(['driver' => 'sqlite', 'path' => $tmp.'/'.$name]); |
| 82 | // 比べられる形の行（BLOBは16進数） |
| 83 | $dump = function(Db $db, string $sql): array { |
| 84 | return array_map(fn($r) => array_map(fn($v) => $v instanceof Blob ? 'x'.bin2hex($v->data) : (is_string($v) && !mb_check_encoding($v, 'UTF-8') ? 'x'.bin2hex($v) : $v), $r), $db->query($sql)['rows']); |
| 85 | }; |
| 86 | // 色々な型の元のSQLiteのデータベース |
| 87 | $sample = function(string $name) use ($sqlite): Db { |
| 88 | $db = $sqlite($name); |
| 89 | $db->query('CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, email TEXT, active BOOLEAN NOT NULL DEFAULT 1, score NUMERIC(10,2) DEFAULT 0, |
| 90 | ratio REAL, created DATETIME DEFAULT CURRENT_TIMESTAMP, born DATE, avatar BLOB, prefs JSON)'); |
| 91 | $db->query('CREATE UNIQUE INDEX users_name ON users (name)'); |
| 92 | $db->query('CREATE INDEX users_email ON users (email)'); |
| 93 | $db->query('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT, published DATETIME)'); |
| 94 | $db->query('CREATE INDEX posts_user ON posts (user_id, published)'); |
| 95 | $db->query('CREATE TABLE kv (k TEXT NOT NULL, n INTEGER NOT NULL, v TEXT, PRIMARY KEY (k, n))'); |
| 96 | $ins = 'INSERT INTO users (name, email, active, score, ratio, created, born, avatar, prefs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'; |
| 97 | $db->query($ins, ['諏訪子', 'suwako@example.com', 1, 123.45, 0.5, '2026-01-02 03:04:05', '1990-05-06', new Blob("\x00\xff\\'\n"), '{"theme":"dark","n":[1,2]}']); |
| 98 | $db->query($ins, ['神奈子', null, 0, -1, null, '2026-01-02 00:00:00', null, null, null]); |
| 99 | $db->query($ins, ["it's \"q\" \\ tab\tx; -- /* */", 'x@x', 1, 0, 1e100, '2026-11-12 23:59:59', '2000-02-29', new Blob(''), '[]']); |
| 100 | $db->query('BEGIN'); |
| 101 | for ($i = 1; $i <= 1200; $i++) $db->query('INSERT INTO posts (user_id, title, body, published) VALUES (?, ?, ?, ?)', [1 + $i % 3, "タイトル{$i}", str_repeat('本文', $i % 50), gmdate('Y-m-d H:i:s', 1767225600 + $i * 3600)]); |
| 102 | $db->query('COMMIT'); |
| 103 | $db->query("INSERT INTO kv VALUES ('a', 1, 'x'), ('a', 2, 'y'), ('b', 1, NULL)"); |
| 104 | return $db; |
| 105 | }; |
| 106 | $same = function($test, Db $a, Db $b, array $tables = ['users', 'posts', 'kv']) use ($dump): void { |
| 107 | foreach ($tables as $t) $test->assertSame($dump($a, "SELECT * FROM {$t} ORDER BY 1, 2"), $dump($b, "SELECT * FROM {$t} ORDER BY 1, 2"), $t); |
| 108 | }; |
| 109 | // LittleBeastのJSONのデータ |
| 110 | $data = function(string $dir): void { |
| 111 | $put = function(string $rel, mixed $v) use ($dir): void { |
| 112 | @mkdir(dirname($dir.'/'.$rel), 0755, true); |
| 113 | file_put_contents($dir.'/'.$rel, is_string($v) ? $v : json_encode($v, Stores::JSON_FLAGS)); |
| 114 | }; |
| 115 | $put('user/1.alice.json', json_encode(['id' => 1, 'username' => 'alice', 'email' => 'a@example.com', 'role' => 3, 'prefs' => new \stdClass(), 'list' => []], JSON_PRETTY_PRINT)); |
| 116 | $put('user/2.bob.json', ['id' => 2, 'username' => 'bob', 'role' => 1]); |
| 117 | $put('comments/blog/hello.json', ['title' => 'こんにちは', 'comments' => ['0190a1' => ['user' => 'alice', 'body' => '一'], '0190a2' => ['user' => 'bob', 'body' => '二', 'parent' => '0190a1']]]); |
| 118 | $put('comments/tutorial/php/basics.json', ['title' => 'PHP', 'comments' => []]); |
| 119 | $put('comments/ap.json', ['https://remote.example/notes/1' => ['thread' => 'blog/hello', 'id' => '0190a2']]); |
| 120 | $put('notifications/alice.json', [['type' => 'reply', 'from' => 'bob'], ['type' => 'like', 'from' => 'bob']]); |
| 121 | $put('cron/cron.json', [['name' => 'apdeliver', 'schedule' => '* * * * *'], ['name' => 'purge', 'schedule' => '0 3 * * *']]); |
| 122 | $put('cron/state.json', ['apdeliver' => 1234]); |
| 123 | $put('followers.txt', "https://a.example/users/x\nhttps://b.example/users/y\n"); |
| 124 | $put('ap/notes/0190b1.json', ['id' => '0190b1', 'content' => '<p>投稿</p>']); |
| 125 | $put('ap/users/alice/private.pem', "-----BEGIN PRIVATE KEY-----\nabc\n-----END PRIVATE KEY-----\n"); |
| 126 | $put('ap/remote/media/x.bin', "\x00\x01\xfe\xff".str_repeat("\x80", 300)); |
| 127 | $put('broken.json', '{not json'); |
| 128 | $put('cache/foo/bar.json', '{"x":1}'); |
| 129 | $put('ratelimit/1.2.3.4.json', '{}'); |
| 130 | $put('cron/run.lock', ''); |
| 131 | $put('site.sqlite', "SQLite format 3\0".str_repeat("\0", 100)); |
| 132 | }; |
| 133 | // ディレクトリの全部のファイル（相対パス => 中身） |
| 134 | $files = function(string $dir): array { |
| 135 | $out = []; |
| 136 | if (!is_dir($dir)) return $out; |
| 137 | foreach (new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($dir, \FilesystemIterator::SKIP_DOTS)) as $f) { |
| 138 | $out[str_replace('\\', '/', substr($f->getPathname(), strlen($dir) + 1))] = (string)file_get_contents($f->getPathname()); |
| 139 | } |
| 140 | ksort($out); |
| 141 | return $out; |
| 142 | }; |
| 143 | // 変換された事を確認する（JSONは同じ値なら良い） |
| 144 | $sameFiles = function($test, array $a, array $b): void { |
| 145 | $test->assertSame(array_keys($a), array_keys($b)); |
| 146 | foreach ($a as $rel => $x) { |
| 147 | if (!isset($b[$rel])) continue; |
| 148 | $ja = json_decode($x, true); |
| 149 | if ($x !== $b[$rel] && $ja !== null) $test->assertSame($ja, json_decode($b[$rel], true), $rel); |
| 150 | else $test->assertSame(bin2hex($x), bin2hex($b[$rel]), $rel); |
| 151 | } |
| 152 | }; |
| 153 | |
| 154 | |
| 155 | $test->describe('Convert（SQLの解析）', function($test): void { |
| 156 | $test->it('方言毎の文字列・名前を解析出来るはず', function($test): void { |
| 157 | $t = Sql::tokens("SELECT 'it''s', 'a\\'b', \"x\", `y`, 0x00ff, _binary 'z', X'41'", 'mysql'); |
| 158 | $vals = array_map(fn($k) => [$k['t'], $k['t'] === 'blob' ? bin2hex($k['v']) : $k['v']], array_values(array_filter($t, fn($k) => $k['t'] !== 'op'))); |
| 159 | $test->assertSame([['id', 'SELECT'], ['str', "it's"], ['str', "a'b"], ['str', 'x'], ['qid', 'y'], ['blob', '00ff'], ['blob', '7a'], ['blob', '41']], $vals); |
| 160 | $t = Sql::tokens("SELECT 'a\\b', E'c\\nd', \"x\"\"y\", \$q\$it's\$q\$", 'pgsql'); |
| 161 | $test->assertSame(['a\\b', "c\nd", 'x"y', "it's"], array_column(array_values(array_filter($t, fn($k) => in_array($k['t'], ['str', 'qid'], true))), 'v')); |
| 162 | }); |
| 163 | |
| 164 | $test->it('CREATE TABLEを解析出来るはず', function($test): void { |
| 165 | $w = []; |
| 166 | $t = Sql::createTable("CREATE TABLE `a` ( |
| 167 | `id` int unsigned NOT NULL AUTO_INCREMENT, |
| 168 | `name` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '', |
| 169 | `price` decimal(10,2) DEFAULT '0.00', |
| 170 | `ok` tinyint(1) NOT NULL DEFAULT '1', |
| 171 | `at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3), |
| 172 | `pid` int DEFAULT NULL, |
| 173 | PRIMARY KEY (`id`), |
| 174 | UNIQUE KEY `a_name` (`name`(20)), |
| 175 | KEY `a_low` ((lower(`name`))), |
| 176 | CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `a` (`id`) |
| 177 | ) ENGINE=InnoDB", 'mysql', $w); |
| 178 | $test->assertSame('a', $t['name']); |
| 179 | $test->assertSame(['id', 'name', 'price', 'ok', 'at', 'pid'], array_column($t['columns'], 'name')); |
| 180 | $test->assertSame(['int', 'varchar', 'decimal', 'bool', 'datetime', 'int'], array_column($t['columns'], 'type')); |
| 181 | $test->assertTrue($t['columns'][0]['autoinc'] && $t['columns'][0]['unsigned']); |
| 182 | $test->assertSame([50, 'utf8mb4_bin', ['value' => '']], [$t['columns'][1]['size'], $t['columns'][1]['collate'], $t['columns'][1]['default']]); |
| 183 | $test->assertSame([10, 2], [$t['columns'][2]['size'], $t['columns'][2]['scale']]); |
| 184 | $test->assertSame(['now' => true], $t['columns'][4]['default']); |
| 185 | $test->assertSame(['id'], $t['pk']); |
| 186 | $test->assertSame([['name' => 'a_name', 'columns' => ['name'], 'unique' => true]], $t['indexes']); |
| 187 | $test->assertCount(2, $w); // 式のインデックスと外部キー |
| 188 | |
| 189 | $t = Sql::createTable('CREATE TABLE public.b (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, s serial, t timestamp with time zone NOT NULL DEFAULT now(), |
| 190 | u uuid, j jsonb, tags text[], d double precision, c character varying(9) UNIQUE)', 'pgsql'); |
| 191 | $test->assertSame('b', $t['name']); |
| 192 | $test->assertSame(['int', 'int', 'datetime', 'uuid', 'json', 'json', 'real', 'varchar'], array_column($t['columns'], 'type')); |
| 193 | $test->assertSame([true, true, false], [$t['columns'][0]['autoinc'], $t['columns'][1]['autoinc'], $t['columns'][2]['autoinc']]); |
| 194 | $test->assertTrue($t['columns'][2]['tz'] && $t['columns'][2]['notNull']); |
| 195 | $test->assertTrue($t['columns'][5]['array']); |
| 196 | $test->assertSame(['id'], $t['pk']); |
| 197 | $test->assertSame([['c']], array_column($t['indexes'], 'columns')); |
| 198 | $test->assertNull(Sql::createTable('CREATE VIEW v AS SELECT 1', 'pgsql')); |
| 199 | }); |
| 200 | |
| 201 | $test->it('型を共通の型にするはず', function($test): void { |
| 202 | $p = fn(string $t, string $d = 'mysql') => array_intersect_key(Convert::parseType($t, $d), ['type' => 1, 'size' => 1, 'scale' => 1, 'unsigned' => 1, 'tz' => 1]); |
| 203 | $test->assertSame(['type' => 'int', 'size' => 16, 'scale' => null, 'unsigned' => false, 'tz' => false], $p('smallint(6)')); |
| 204 | $test->assertSame('bool', $p('tinyint(1)')['type']); |
| 205 | $test->assertSame(['int', 16], [$p('tinyint unsigned')['type'], $p('tinyint unsigned')['size']]); |
| 206 | $test->assertSame(['int', 64], [$p('int unsigned')['type'], $p('int unsigned')['size']]); |
| 207 | $test->assertSame('text', $p("enum('a','b')")['type']); |
| 208 | $test->assertSame('blob', $p('varbinary(16)')['type']); |
| 209 | $test->assertSame('datetime', $p('timestamp', 'mysql')['type']); |
| 210 | $test->assertSame(true, $p('timestamptz', 'pgsql')['tz']); |
| 211 | $test->assertSame('real', $p('double precision', 'pgsql')['type']); |
| 212 | $test->assertSame(['decimal', 12, 4], [$p('numeric(12,4)', 'pgsql')['type'], $p('numeric(12,4)', 'pgsql')['size'], $p('numeric(12,4)', 'pgsql')['scale']]); |
| 213 | // SQLiteの型の親和性 |
| 214 | $test->assertSame('int', $p('INTEGER', 'sqlite')['type']); |
| 215 | $test->assertSame('text', $p('', 'sqlite')['type']); |
| 216 | $test->assertSame('text', $p('STRING', 'sqlite')['type']); |
| 217 | }); |
| 218 | }); |
| 219 | |
| 220 | $test->describe('Convert（SQLのダンプ）', function($test) use ($tmp, $fixtures, $sqlite, $sample, $same, $dump, $catch): void { |
| 221 | $test->it('mysqldumpのファイルを読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void { |
| 222 | file_put_contents($tmp.'/my.sql', $fixtures['mysqldump']); |
| 223 | $src = new DumpSource($tmp.'/my.sql'); |
| 224 | $test->assertSame('mysql', $src->dialect); |
| 225 | $r = Convert::run($src, new DbTarget($sqlite('my.sqlite'))); |
| 226 | $test->assertSame(['customers' => 3, 'orders' => 2], $r['tables']); |
| 227 | $test->assertTrue(count($r['warnings']) >= 3); // 全文のインデックス、外部キー、ビュー |
| 228 | $db = $sqlite('my.sqlite'); |
| 229 | $rows = $db->query('SELECT * FROM customers ORDER BY id')['rows']; |
| 230 | $test->assertSame(['諏訪子', '神奈子', "O'Brien"], array_column($rows, 'name')); |
| 231 | $test->assertSame("\0\\'\"\n\r\x1a", $rows[0]['photo']); |
| 232 | $test->assertSame("\x00\xff\x10", $rows[1]['photo']); |
| 233 | $test->assertSame("it's; a \"test\"\\ -- not a comment", $rows[0]['note']); |
| 234 | $test->assertSame("行1\n行2\ttab", $rows[1]['note']); |
| 235 | $test->assertSame('/* x */ # y', $rows[2]['note']); |
| 236 | $test->assertSame([1, 0, 1], array_column($rows, 'active')); |
| 237 | $test->assertSame([1234.5, -0.01, 0], array_column($rows, 'balance')); |
| 238 | $test->assertSame(['2026-01-02 03:04:05', null, '2026-11-12 00:00:00'], array_column($rows, 'created_at')); |
| 239 | $test->assertSame('2026-01-02 03:04:05.123', $rows[0]['updated_at']); |
| 240 | $test->assertSame([['id' => 1, 'total' => 99.5, 'ordered' => '2026-01-03'], ['id' => 2, 'total' => 1000.0, 'ordered' => null]], |
| 241 | $db->query('SELECT id, total, ordered FROM orders ORDER BY id')['rows']); |
| 242 | $idx = array_column($db->query("SELECT name FROM sqlite_master WHERE type = 'index' ORDER BY name")['rows'], 'name'); |
| 243 | $test->assertSame(['customers_email_unique', 'customers_idx_name_level', 'orders_customer_id_foreign'], $idx); |
| 244 | // 自動の番号の続き |
| 245 | $test->assertSame(4, $db->query("INSERT INTO customers (name) VALUES ('新')")['insertId']); |
| 246 | }); |
| 247 | |
| 248 | $test->it('pg_dumpのファイル（COPY）を読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void { |
| 249 | file_put_contents($tmp.'/pg.sql', $fixtures['pgdump']); |
| 250 | $src = new DumpSource($tmp.'/pg.sql'); |
| 251 | $test->assertSame('pgsql', $src->dialect); |
| 252 | $r = Convert::run($src, new DbTarget($sqlite('pg.sqlite'))); |
| 253 | $test->assertSame(['items' => 3, 'tags' => 3], $r['tables']); |
| 254 | $test->assertTrue(in_array('式・部分のインデックスは変換しません：items_name', $r['warnings'], true)); |
| 255 | $db = $sqlite('pg.sqlite'); |
| 256 | $rows = $db->query('SELECT * FROM items ORDER BY id')['rows']; |
| 257 | $test->assertSame(['りんご', 'みかん', "it's"], array_column($rows, 'name')); |
| 258 | $test->assertSame("\x00\xff\x0a\x5c", $rows[0]['data']); |
| 259 | $test->assertSame('', $rows[2]['data']); |
| 260 | $test->assertSame('["a","b c",null,"d\"e"]', $rows[0]['labels']); // 配列はJSON |
| 261 | $test->assertSame('[]', $rows[1]['labels']); |
| 262 | $test->assertSame(['2026-01-01 18:04:05.5', '2026-11-12 00:00:00', '2000-02-29 12:00:00'], array_column($rows, 'at')); // UTC |
| 263 | $test->assertSame("行1\n行2\tタブ\\", $rows[0]['note']); |
| 264 | $test->assertSame([1, null, 0], array_column($rows, 'flags')); |
| 265 | $test->assertSame([120.5, null, -0.01], array_column($rows, 'price')); |
| 266 | $test->assertSame(['id', 'name', 'price', 'flags', 'data', 'meta', 'labels', 'at', 'day', 'uid', 'note'], array_column($db->query('PRAGMA table_info(items)')['rows'], 'name')); |
| 267 | $test->assertSame([[1, '果物', 1], [1, '赤', 2], [2, '果物', 3]], array_map('array_values', $db->query('SELECT * FROM tags ORDER BY item_id, n')['rows'])); |
| 268 | }); |
| 269 | |
| 270 | $test->it('sqlite3の.dumpのファイルを読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void { |
| 271 | file_put_contents($tmp.'/sq.sql', $fixtures['sqlitedump']); |
| 272 | $src = new DumpSource($tmp.'/sq.sql'); |
| 273 | $test->assertSame('sqlite', $src->dialect); |
| 274 | $r = Convert::run($src, new DbTarget($sqlite('sq.sqlite'))); |
| 275 | $test->assertSame(['users' => 3, 'q t' => 1, 'u2' => 2], $r['tables']); |
| 276 | $db = $sqlite('sq.sqlite'); |
| 277 | $test->assertSame([[1, "a'b", "\x00\xff", 1.5], [2, "x\ny", null, -2.0e-5], [3, "a\r\nb", null, null]], |
| 278 | array_map('array_values', $db->query('SELECT id, name, b, r FROM users ORDER BY id')['rows'])); |
| 279 | $test->assertSame(["a\nb\\c😀", "x\ty"], array_column($db->query('SELECT s FROM u2 ORDER BY rowid')['rows'], 's')); |
| 280 | $test->assertSame(4, $db->query("INSERT INTO users (name) VALUES ('新')")['insertId']); |
| 281 | }); |
| 282 | |
| 283 | $test->it('ダンプに書いて、読み戻せるはず（全部の方言）', function($test) use ($tmp, $sqlite, $sample, $same): void { |
| 284 | $a = $sample('rt.sqlite'); |
| 285 | foreach (['mysql', 'pgsql', 'sqlite'] as $d) { |
| 286 | foreach (['', '.gz'] as $gz) { |
| 287 | if ($gz !== '' && !function_exists('gzopen')) continue; |
| 288 | $file = "{$tmp}/rt-{$d}.sql{$gz}"; |
| 289 | $r = Convert::run(new DbSource($a), new DumpTarget($file, $d)); |
| 290 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']); |
| 291 | $head = $gz ? (string)gzdecode((string)file_get_contents($file)) : (string)file_get_contents($file); |
| 292 | $test->assertStringContains("-- LittleBeastのデータベースの変換（{$d}）", $head); |
| 293 | $src = new DumpSource($file); |
| 294 | $test->assertSame($d, $src->dialect); |
| 295 | $r = Convert::run($src, new DbTarget($sqlite("rt-{$d}{$gz}.sqlite"))); |
| 296 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables'], $d); |
| 297 | $b = $sqlite("rt-{$d}{$gz}.sqlite"); |
| 298 | if ($d === 'sqlite') { |
| 299 | $same($test, $a, $b); |
| 300 | } else { |
| 301 | // 型が変わる所（BOOLEAN、NUMERIC）以外は同じ |
| 302 | $same($test, $a, $b, ['posts', 'kv']); |
| 303 | $test->assertSame($a->query('SELECT name, email, avatar, prefs, created FROM users ORDER BY id')['rows'] == $b->query('SELECT name, email, avatar, prefs, created FROM users ORDER BY id')['rows'], true, $d); |
| 304 | } |
| 305 | $test->assertSame(4, $b->query("INSERT INTO users (name) VALUES ('新')")['insertId'], $d); |
| 306 | } |
| 307 | } |
| 308 | }); |
| 309 | |
| 310 | $test->it('SQLiteのダンプは本物のSQLiteで読めるはず', function($test) use ($tmp): void { |
| 311 | if (!class_exists('PDO') || !in_array('sqlite', \PDO::getAvailableDrivers(), true)) { |
| 312 | $test->assertTrue(true); |
| 313 | return; |
| 314 | } |
| 315 | $p = new \PDO('sqlite::memory:'); |
| 316 | $p->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); |
| 317 | $p->exec((string)file_get_contents($tmp.'/rt-sqlite.sql')); |
| 318 | $test->assertSame(1200, (int)$p->query('SELECT count(*) FROM posts')->fetchColumn()); |
| 319 | $test->assertSame('00ff5c270a', bin2hex((string)$p->query('SELECT avatar FROM users WHERE id = 1')->fetchColumn())); |
| 320 | $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn()); |
| 321 | }); |
| 322 | |
| 323 | $test->it('壊れたダンプは例外で、書く先のファイルは消すはず', function($test) use ($tmp, $sqlite, $sample, $catch): void { |
| 324 | file_put_contents($tmp.'/bad.sql', "CREATE TABLE t (id INT PRIMARY KEY, s TEXT);\nINSERT INTO t VALUES (1, 'a'), (2, 'b);\n"); |
| 325 | $e = $catch(fn() => Convert::run(new DumpSource($tmp.'/bad.sql'), new DumpTarget($tmp.'/bad-out.sql', 'pgsql'))); |
| 326 | $test->assertTrue($e instanceof DbException); |
| 327 | $test->assertFalse(is_file($tmp.'/bad-out.sql')); |
| 328 | $e = $catch(fn() => Convert::run(new DbSource($sample('x.sqlite')), new DumpTarget($tmp.'/x.sql', 'oracle'))); |
| 329 | $test->assertTrue($e instanceof \InvalidArgumentException); |
| 330 | }); |
| 331 | }); |
| 332 | |
| 333 | $test->describe('Convert（データベース同士）', function($test) use ($tmp, $sqlite, $sample, $same, $dump, $catch): void { |
| 334 | $test->it('SQLiteからSQLiteに変換出来て、テーブルを選べるはず', function($test) use ($sqlite, $sample, $same, $catch): void { |
| 335 | $a = $sample('a.sqlite'); |
| 336 | $n = []; |
| 337 | $r = Convert::run(new DbSource($a), new DbTarget($sqlite('b.sqlite')), ['progress' => function(string $t, int $rows) use (&$n): void { $n[$t] = $rows; }]); |
| 338 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']); |
| 339 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $n); |
| 340 | $test->assertSame([], $r['warnings']); |
| 341 | $b = $sqlite('b.sqlite'); |
| 342 | $same($test, $a, $b); |
| 343 | $test->assertSame('ok', $b->query('PRAGMA integrity_check')['rows'][0]['integrity_check']); |
| 344 | // 既に有るテーブルは、dropが無ければ例外 |
| 345 | $e = $catch(fn() => Convert::run(new DbSource($a), new DbTarget($b))); |
| 346 | $test->assertTrue($e instanceof DbException); |
| 347 | $test->assertStringContains('既に有ります', $e->getMessage()); |
| 348 | $r = Convert::run(new DbSource($a), new DbTarget($b), ['tables' => ['KV'], 'drop' => true]); |
| 349 | $test->assertSame(['kv' => 3], $r['tables']); |
| 350 | $same($test, $a, $b); |
| 351 | // 空のデータベース |
| 352 | $r = Convert::run(new DbSource($sqlite('empty.sqlite')), new DbTarget($sqlite('empty2.sqlite'))); |
| 353 | $test->assertSame([], $r['tables']); |
| 354 | }); |
| 355 | |
| 356 | $test->it('偽物のMySQLサーバーに書いて、読み戻せるはず', function($test) use ($sqlite, $sample, $same): void { |
| 357 | $srv = MockServer::start('mysql-server.php', []); |
| 358 | if ($srv === null) { |
| 359 | $test->assertTrue(true); |
| 360 | return; |
| 361 | } |
| 362 | try { |
| 363 | $a = $sample('my-a.sqlite'); |
| 364 | $my = new Db(['driver' => 'mysql', 'host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb', 'timeout' => 5]); |
| 365 | $r = Convert::run(new DbSource($a), new DbTarget($my)); |
| 366 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']); |
| 367 | $ddl = $my->query('SHOW CREATE TABLE `users`')['rows'][0]['Create Table']; |
| 368 | $test->assertStringContains('AUTO_INCREMENT', $ddl); |
| 369 | $test->assertStringContains('utf8mb4', $ddl); |
| 370 | $r = Convert::run(new DbSource($my), new DbTarget($sqlite('my-b.sqlite'))); |
| 371 | $test->assertEquals(['kv' => 3, 'posts' => 1200, 'users' => 3], $r['tables']); |
| 372 | $same($test, $a, $sqlite('my-b.sqlite'), ['posts', 'kv']); |
| 373 | $test->assertSame(array_column($a->query('SELECT name FROM users ORDER BY id')['rows'], 'name'), array_column($sqlite('my-b.sqlite')->query('SELECT name FROM users ORDER BY id')['rows'], 'name')); |
| 374 | } finally { |
| 375 | $srv->stop(); |
| 376 | } |
| 377 | }); |
| 378 | |
| 379 | $test->it('偽物のPostgreSQLサーバーに書けるはず', function($test) use ($sample): void { |
| 380 | $srv = MockServer::start('pg-server.php', []); |
| 381 | if ($srv === null) { |
| 382 | $test->assertTrue(true); |
| 383 | return; |
| 384 | } |
| 385 | try { |
| 386 | $a = $sample('pg-a.sqlite'); |
| 387 | $pg = new Db(['driver' => 'pgsql', 'host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb', 'timeout' => 5]); |
| 388 | $r = Convert::run(new DbSource($a), new DbTarget($pg)); |
| 389 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']); |
| 390 | $test->assertSame(1200, (int)$pg->query('SELECT count(*) AS c FROM posts')['rows'][0]['c']); |
| 391 | $test->assertSame(['諏訪子', '神奈子'], array_column($pg->query('SELECT name FROM users WHERE id < 3 ORDER BY id')['rows'], 'name')); |
| 392 | } finally { |
| 393 | $srv->stop(); |
| 394 | } |
| 395 | }); |
| 396 | |
| 397 | $test->it('本物のPostgreSQLと往復出来るはず（LB_TEST_PGSQL）', function($test) use ($sqlite, $sample, $same): void { |
| 398 | $url = (string)getenv('LB_TEST_PGSQL'); |
| 399 | if ($url === '') { |
| 400 | $test->assertTrue(true); |
| 401 | return; |
| 402 | } |
| 403 | $u = parse_url($url); |
| 404 | $pg = new Db(['driver' => 'pgsql', 'host' => $u['host'] ?? '127.0.0.1', 'port' => $u['port'] ?? 5432, 'username' => urldecode($u['user'] ?? ''), |
| 405 | 'password' => urldecode($u['pass'] ?? ''), 'dbname' => ltrim($u['path'] ?? '', '/'), 'timeout' => 5]); |
| 406 | $a = $sample('real-a.sqlite'); |
| 407 | $names = ['lb_test_users', 'lb_test_posts', 'lb_test_kv']; |
| 408 | foreach (['users', 'posts', 'kv'] as $i => $t) $a->query("ALTER TABLE {$t} RENAME TO {$names[$i]}"); |
| 409 | try { |
| 410 | Convert::run(new DbSource($a), new DbTarget($pg), ['drop' => true]); |
| 411 | $r = Convert::run(new DbSource($pg), new DbTarget($sqlite('real-b.sqlite')), ['tables' => $names]); |
| 412 | $test->assertEquals(['lb_test_kv' => 3, 'lb_test_posts' => 1200, 'lb_test_users' => 3], $r['tables']); |
| 413 | $same($test, $a, $sqlite('real-b.sqlite'), ['lb_test_posts', 'lb_test_kv']); |
| 414 | $test->assertSame(4, (int)$pg->query("INSERT INTO lb_test_users (name) VALUES ('新') RETURNING id")['rows'][0]['id']); |
| 415 | } finally { |
| 416 | foreach ($names as $t) $pg->query("DROP TABLE IF EXISTS {$t}"); |
| 417 | } |
| 418 | }); |
| 419 | }); |
| 420 | |
| 421 | $test->describe('Convert（JSONのデータ）', function($test) use ($tmp, $sqlite, $sample, $same, $data, $files, $sameFiles, $catch): void { |
| 422 | $test->it('データのディレクトリが無い・空なら、テーブルも無いはず', function($test) use ($tmp, $sqlite): void { |
| 423 | $test->assertSame([], (new JsonSource($tmp.'/nothing'))->tables()); |
| 424 | mkdir($tmp.'/emptydata'); |
| 425 | $r = Convert::run(new JsonSource($tmp.'/emptydata'), new DbTarget($sqlite('e.sqlite'))); |
| 426 | $test->assertSame([], $r['tables']); |
| 427 | $r = Convert::run(new JsonSource($tmp.'/emptydata'), new JsonTarget($tmp.'/emptyout')); |
| 428 | $test->assertTrue(is_dir($tmp.'/emptyout')); |
| 429 | }); |
| 430 | |
| 431 | $test->it('JSONのデータをテーブルにするはず', function($test) use ($tmp, $sqlite, $data): void { |
| 432 | $data($tmp.'/data'); |
| 433 | $src = new JsonSource($tmp.'/data'); |
| 434 | $r = Convert::run($src, new DbTarget($sqlite('j.sqlite'))); |
| 435 | $test->assertSame(['lb_users' => 2, 'lb_comment_threads' => 2, 'lb_comments' => 2, 'lb_comment_ap' => 1, 'lb_notifications' => 2, 'lb_cron' => 2, |
| 436 | 'lb_followers' => 2, 'lb_ap_notes' => 1, 'lb_files' => 4], $r['tables']); |
| 437 | $test->assertTrue(in_array('SQLiteのファイルは変換しません：site.sqlite', $r['warnings'], true)); |
| 438 | $db = $sqlite('j.sqlite'); |
| 439 | $test->assertSame([['id' => 1, 'username' => 'alice', 'email' => 'a@example.com', 'role' => 3], ['id' => 2, 'username' => 'bob', 'email' => null, 'role' => 1]], |
| 440 | $db->query('SELECT id, username, email, role FROM lb_users ORDER BY id')['rows']); |
| 441 | $test->assertSame([['id' => '0190a1', 'seq' => 0], ['id' => '0190a2', 'seq' => 1]], $db->query("SELECT id, seq FROM lb_comments WHERE thread = 'blog/hello' ORDER BY seq")['rows']); |
| 442 | $test->assertSame('二', json_decode($db->query("SELECT data FROM lb_comments WHERE id = '0190a2'")['rows'][0]['data'], true)['body']); |
| 443 | // キャッシュ・ロック・SQLiteのファイルは入れない、壊れたJSONは普通のファイル |
| 444 | $test->assertSame(['ap/remote/media/x.bin', 'ap/users/alice/private.pem', 'broken.json', 'cron/state.json'], |
| 445 | array_column($db->query('SELECT path FROM lb_files ORDER BY path')['rows'], 'path')); |
| 446 | }); |
| 447 | |
| 448 | $test->it('テーブルからJSONのデータに戻せるはず', function($test) use ($tmp, $sqlite, $files, $sameFiles, $catch): void { |
| 449 | $want = array_filter($files($tmp.'/data'), fn($k) => !preg_match('#^(cache|ratelimit)/|\.lock$|^site\.sqlite$#', $k), ARRAY_FILTER_USE_KEY); |
| 450 | $r = Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1')); |
| 451 | $test->assertSame(2, $r['tables']['lb_users']); |
| 452 | $sameFiles($test, $want, $files($tmp.'/out1')); |
| 453 | // ユーザーのファイルはそのまま |
| 454 | $test->assertSame(file_get_contents($tmp.'/data/user/1.alice.json'), file_get_contents($tmp.'/out1/user/1.alice.json')); |
| 455 | // 空でないディレクトリには、上書きを許さなければ書かない |
| 456 | $e = $catch(fn() => Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1'))); |
| 457 | $test->assertTrue($e instanceof DbException); |
| 458 | $test->assertStringContains('空ではありません', $e->getMessage()); |
| 459 | Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1', true)); |
| 460 | $sameFiles($test, $want, $files($tmp.'/out1')); |
| 461 | }); |
| 462 | |
| 463 | $test->it('JSONのデータをダンプ経由で往復出来るはず', function($test) use ($tmp, $files, $sameFiles): void { |
| 464 | $want = $files($tmp.'/out1'); |
| 465 | foreach (['mysql', 'pgsql', 'sqlite'] as $d) { |
| 466 | Convert::run(new JsonSource($tmp.'/out1'), new DumpTarget("{$tmp}/j-{$d}.sql", $d)); |
| 467 | Convert::run(new DumpSource("{$tmp}/j-{$d}.sql"), new JsonTarget("{$tmp}/out-{$d}")); |
| 468 | $sameFiles($test, $want, $files("{$tmp}/out-{$d}")); |
| 469 | } |
| 470 | }); |
| 471 | |
| 472 | $test->it('普通のテーブルはdata/dbに書いて、読み戻せるはず', function($test) use ($tmp, $sqlite, $sample, $same, $files): void { |
| 473 | $a = $sample('g.sqlite'); |
| 474 | $a->query('CREATE TABLE "odd/name" (id INTEGER PRIMARY KEY, f REAL, b BLOB)'); |
| 475 | $a->query('INSERT INTO "odd/name" (f, b) VALUES (1.0, X\'00ff\'), (NULL, NULL)'); |
| 476 | $r = Convert::run(new DbSource($a), new JsonTarget($tmp.'/gen')); |
| 477 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3, 'odd/name' => 2], $r['tables']); |
| 478 | $test->assertSame(['db/kv.json', 'db/odd%2Fname.json', 'db/posts.json', 'db/users.json'], array_keys($files($tmp.'/gen'))); |
| 479 | $j = json_decode((string)file_get_contents($tmp.'/gen/db/odd%2Fname.json'), true); |
| 480 | $test->assertSame('odd/name', $j['table']); |
| 481 | $test->assertSame([[1, 1.0, ['$blob' => base64_encode("\x00\xff")]], [2, null, null]], $j['rows']); |
| 482 | $r = Convert::run(new JsonSource($tmp.'/gen'), new DbTarget($sqlite('g2.sqlite'))); |
| 483 | $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3, 'odd/name' => 2], array_intersect_key($r['tables'], ['users' => 1, 'posts' => 1, 'kv' => 1, 'odd/name' => 1])); |
| 484 | $b = $sqlite('g2.sqlite'); |
| 485 | $same($test, $a, $b, ['users', 'posts', 'kv', '"odd/name"']); |
| 486 | }); |
| 487 | |
| 488 | $test->it('不正なパス・名前は書かないはず', function($test) use ($tmp, $sqlite, $catch): void { |
| 489 | foreach (['../x', '/etc/passwd', 'a//b', 'a/./b', "a\0b", 'C:/x', 'a\\b', ''] as $p) $test->assertFalse(Stores::safePath($p), $p); |
| 490 | $test->assertTrue(Stores::safePath('ap/users/alice/private.pem')); |
| 491 | $test->assertFalse(Stores::safeThread('../../etc')); |
| 492 | $test->assertTrue(Stores::safeThread('tutorial/php/basics')); |
| 493 | $db = $sqlite('evil.sqlite'); |
| 494 | $db->query('CREATE TABLE lb_files (path VARCHAR(700) NOT NULL PRIMARY KEY, content BLOB NOT NULL)'); |
| 495 | $db->query("INSERT INTO lb_files VALUES ('../evil.txt', X'41')"); |
| 496 | $e = $catch(fn() => Convert::run(new DbSource($db), new JsonTarget($tmp.'/evil'))); |
| 497 | $test->assertTrue($e instanceof DbException); |
| 498 | $test->assertFalse(is_file($tmp.'/evil.txt')); |
| 499 | }); |
| 500 | }); |
| 501 | |
| 502 | $test->printSummary(); |