Open Tibia Server List
Grow your
Open Tibia server.
Reach new players
•
Build your community
•
Climb the rankings
convert.php
main 2812 lines · 91.7 KB Raw
1
<?php2
/**3
* Title: Convert SQL4
* Icon: fa-exchange5
* Group: Settings6
* Order: 407
* Description: Upload a MyAAC or Gesior2012 SQL dump and download a ZnoteX conversion SQL.8
* Tested with a clean MyAAC SQL (31 players test) + custom tables inserted 9
*/10
11
if (!defined('ACP_ROOT')) {12
http_response_code(403);13
die('Direct access denied.');14
}15
16
function acp_convert_identifier(string $name): string {17
if (!preg_match('/^[a-zA-Z0-9_]+$/', $name)) {18
throw new InvalidArgumentException('Unsafe SQL identifier: ' . $name);19
}20
return $name;21
}22
23
function acp_convert_table_exists(string $table): bool {24
$escaped = db()->connection()->real_escape_string($table);25
return db()->rawFetchOne("SHOW TABLES LIKE '{$escaped}';") !== false;26
}27
28
function acp_convert_column_exists(string $table, string $column): bool {29
$escaped = db()->connection()->real_escape_string($column);30
return db()->rawFetchOne(31
'SHOW COLUMNS FROM `' . acp_convert_identifier($table) . "` LIKE '{$escaped}';"32
) !== false;33
}34
35
function acp_convert_count(string $table, string $where = '1=1'): int {36
if (!acp_convert_table_exists($table)) {37
return 0;38
}39
return acp_count('SELECT COUNT(*) AS `c` FROM `' . acp_convert_identifier($table) . "` WHERE {$where};");40
}41
42
function acp_convert_scalar(string $sql, string $key = 'v') {43
$row = db()->fetchOne($sql);44
return is_array($row) ? ($row[$key] ?? reset($row)) : null;45
}46
47
function acp_convert_insert(string $table, array $data): bool {48
$fields = [];49
$params = [];50
foreach ($data as $key => $value) {51
$fields[] = '`' . acp_convert_identifier((string)$key) . '`';52
$params[] = $value;53
}54
$placeholders = implode(', ', array_fill(0, count($params), '?'));55
return db()->execute(56
'INSERT INTO `' . acp_convert_identifier($table) . '` (' . implode(', ', $fields) . ") VALUES ({$placeholders});",57
$params58
);59
}60
61
function acp_convert_ensure_tables(): void {62
db()->execute("63
CREATE TABLE IF NOT EXISTS `znote_pages` (64
`id` int NOT NULL AUTO_INCREMENT,65
`slug` varchar(64) NOT NULL,66
`title` varchar(100) NOT NULL,67
`body` mediumtext NOT NULL,68
`created` int NOT NULL DEFAULT '0',69
`updated` int NOT NULL DEFAULT '0',70
`player_id` int NOT NULL DEFAULT '0',71
`access` tinyint NOT NULL DEFAULT '0',72
`active` tinyint NOT NULL DEFAULT '1',73
PRIMARY KEY (`id`),74
UNIQUE KEY `slug` (`slug`)75
) ENGINE=InnoDB;76
");77
78
db()->execute("79
CREATE TABLE IF NOT EXISTS `znote_convert_map` (80
`id` int NOT NULL AUTO_INCREMENT,81
`source` varchar(32) NOT NULL,82
`source_table` varchar(64) NOT NULL,83
`source_id` varchar(64) NOT NULL,84
`target_table` varchar(64) NOT NULL,85
`target_id` int NOT NULL,86
`created` int NOT NULL,87
PRIMARY KEY (`id`),88
UNIQUE KEY `source_row` (`source`, `source_table`, `source_id`, `target_table`)89
) ENGINE=InnoDB;90
");91
92
db()->execute("93
CREATE TABLE IF NOT EXISTS `znote_legacy_tables` (94
`id` int NOT NULL AUTO_INCREMENT,95
`source` varchar(32) NOT NULL,96
`table_name` varchar(64) NOT NULL,97
`schema_sql` longtext NOT NULL,98
`row_count` int NOT NULL DEFAULT '0',99
`captured` int NOT NULL,100
PRIMARY KEY (`id`),101
UNIQUE KEY `source_table` (`source`, `table_name`)102
) ENGINE=InnoDB;103
");104
105
db()->execute("106
CREATE TABLE IF NOT EXISTS `znote_legacy_rows` (107
`id` bigint NOT NULL AUTO_INCREMENT,108
`source` varchar(32) NOT NULL,109
`table_name` varchar(64) NOT NULL,110
`source_pk` varchar(128) NOT NULL DEFAULT '',111
`row_json` longtext NOT NULL,112
`captured` int NOT NULL,113
PRIMARY KEY (`id`),114
KEY `source_table` (`source`, `table_name`),115
KEY `source_pk` (`source`, `table_name`, `source_pk`)116
) ENGINE=InnoDB;117
");118
119
if (!acp_convert_column_exists('znote_legacy_tables', 'schema_sql')) {120
db()->execute("121
ALTER TABLE `znote_legacy_tables`122
ADD `schema_sql` longtext NULL AFTER `table_name`;123
");124
}125
}126
127
function acp_convert_slug(string $value, string $fallback): string {128
$value = strtolower(trim($value));129
$value = preg_replace('/[^a-z0-9_-]+/', '-', $value);130
$value = trim((string)$value, '-_');131
if ($value === '') {132
$value = $fallback;133
}134
return substr($value, 0, 64);135
}136
137
function acp_convert_map_get(string $source, string $sourceTable, $sourceId, string $targetTable): int {138
if (!acp_convert_table_exists('znote_convert_map')) {139
return 0;140
}141
$row = db()->fetchOne("142
SELECT `target_id`143
FROM `znote_convert_map`144
WHERE `source` = ?145
AND `source_table` = ?146
AND `source_id` = ?147
AND `target_table` = ?148
LIMIT 1;149
", [$source, $sourceTable, (string)$sourceId, $targetTable]);150
return is_array($row) ? (int)$row['target_id'] : 0;151
}152
153
function acp_convert_map_set(string $source, string $sourceTable, $sourceId, string $targetTable, int $targetId): void {154
if ($targetId <= 0 || !acp_convert_table_exists('znote_convert_map')) {155
return;156
}157
db()->execute("158
INSERT INTO `znote_convert_map`159
(`source`, `source_table`, `source_id`, `target_table`, `target_id`, `created`)160
VALUES (?, ?, ?, ?, ?, ?)161
ON DUPLICATE KEY UPDATE `target_id` = VALUES(`target_id`);162
", [$source, $sourceTable, (string)$sourceId, $targetTable, $targetId, time()]);163
}164
165
function acp_convert_config_set(string $key, string $value): bool {166
if (!acp_convert_table_exists('znote_config')) {167
return false;168
}169
return db()->execute("170
INSERT INTO `znote_config` (`key`, `value`)171
VALUES (?, ?)172
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);173
", [substr($key, 0, 64), $value]);174
}175
176
function acp_convert_legacy_tables(string $source): array {177
$prefix = $source === 'myaac' ? 'myaac_' : 'z_';178
$rows = db()->fetchAll('SHOW TABLES;') ?: [];179
$tables = [];180
181
foreach ($rows as $row) {182
$table = (string)reset($row);183
if (strpos($table, $prefix) === 0) {184
$tables[] = $table;185
}186
}187
188
sort($tables);189
return $tables;190
}191
192
function acp_convert_row_pk(string $table, array $row): string {193
foreach (['id', 'account_id', 'player_id', 'name', 'key'] as $key) {194
if (array_key_exists($key, $row)) {195
return (string)$row[$key];196
}197
}198
return substr(sha1($table . '|' . json_encode($row)), 0, 40);199
}200
201
function acp_convert_archive_legacy(string $source): int {202
acp_convert_ensure_tables();203
$archived = 0;204
$captured = time();205
206
foreach (acp_convert_legacy_tables($source) as $table) {207
$count = acp_convert_count($table);208
db()->execute("209
INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`)210
VALUES (?, ?, '', ?, ?)211
ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);212
", [$source, $table, $count, $captured]);213
db()->execute("214
DELETE FROM `znote_legacy_rows`215
WHERE `source` = ?216
AND `table_name` = ?;217
", [$source, $table]);218
219
$rows = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($table) . '`;') ?: [];220
foreach ($rows as $row) {221
$pk = acp_convert_row_pk($table, $row);222
$json = json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);223
if ($json === false) {224
$json = '{}';225
}226
if (acp_convert_insert('znote_legacy_rows', [227
'source' => $source,228
'table_name' => $table,229
'source_pk' => substr($pk, 0, 128),230
'row_json' => $json,231
'captured' => $captured,232
])) {233
$archived++;234
}235
}236
}237
238
return $archived;239
}240
241
function acp_convert_sql_literal($value): string {242
return $value === null ? 'NULL' : "'" . esc((string)$value) . "'";243
}244
245
function acp_convert_menu_category_labels(int $category): array {246
return match ($category) {247
1 => ['Home', 'News', 'Latest News'],248
2 => ['Account'],249
3 => ['Community'],250
4 => ['Community', 'Forum'],251
5 => ['Library'],252
6 => ['Shop'],253
default => [],254
};255
}256
257
function acp_convert_dump_menu_parent_sql(int $category): string {258
$labels = acp_convert_menu_category_labels($category);259
if (!$labels) {260
return 'NULL';261
}262
263
$literals = array_map('acp_convert_sql_literal', $labels);264
return "(SELECT `p`.`id` FROM `znote_menu` AS `p`265
WHERE `p`.`location` = 'main'266
AND `p`.`parent_id` = 0267
AND `p`.`label` IN (" . implode(', ', $literals) . ")268
ORDER BY FIELD(`p`.`label`, " . implode(', ', $literals) . ")269
LIMIT 1)";270
}271
272
function acp_convert_menu_parent_id(int $category): int {273
$labels = acp_convert_menu_category_labels($category);274
if (!$labels) {275
return 0;276
}277
278
$literals = array_map('acp_convert_sql_literal', $labels);279
return (int)acp_convert_scalar("SELECT `id` AS `v`280
FROM `znote_menu`281
WHERE `location` = 'main'282
AND `parent_id` = 0283
AND `label` IN (" . implode(', ', $literals) . ")284
ORDER BY FIELD(`label`, " . implode(', ', $literals) . ")285
LIMIT 1;");286
}287
288
function acp_convert_dump_skip_comment(string $sql, int $index, int $len) {289
$ch = $sql[$index];290
$next = ($index + 1 < $len) ? $sql[$index + 1] : '';291
292
if (($ch === '-' && $next === '-') || $ch === '#') {293
while ($index < $len && $sql[$index] !== "\n") $index++;294
return $index;295
}296
297
if ($ch === '/' && $next === '*') {298
$index += 2;299
while ($index + 1 < $len && !($sql[$index] === '*' && $sql[$index + 1] === '/')) $index++;300
return $index + 1;301
}302
303
return false;304
}305
306
function acp_convert_dump_consume_quoted_char(string $sql, int &$index, int $len, string $quote, string &$buf): string {307
$ch = $sql[$index];308
$next = ($index + 1 < $len) ? $sql[$index + 1] : '';309
310
if ($ch === '\\') {311
if ($index + 1 < $len) {312
$buf .= $sql[++$index];313
}314
return $quote;315
}316
317
if ($ch !== $quote) {318
return $quote;319
}320
321
if ($quote === "'" && $next === "'") {322
$buf .= $sql[++$index];323
return $quote;324
}325
326
return '';327
}328
329
function acp_convert_dump_push_statement(array &$statements, string &$buf): void {330
$statement = trim(substr($buf, 0, -1));331
if ($statement !== '') {332
$statements[] = $statement;333
}334
$buf = '';335
}336
337
function acp_convert_dump_split(string $sql): array {338
$statements = [];339
$buf = '';340
$quote = '';341
$len = strlen($sql);342
343
for ($i = 0; $i < $len; $i++) {344
if ($quote === '') {345
$commentEnd = acp_convert_dump_skip_comment($sql, $i, $len);346
if ($commentEnd !== false) {347
$i = $commentEnd;348
continue;349
}350
}351
352
$ch = $sql[$i];353
$buf .= $ch;354
355
if ($quote !== '') {356
$quote = acp_convert_dump_consume_quoted_char($sql, $i, $len, $quote, $buf);357
continue;358
}359
360
if ($ch === "'" || $ch === '"' || $ch === '`') {361
$quote = $ch;362
continue;363
}364
365
if ($ch === ';') {366
acp_convert_dump_push_statement($statements, $buf);367
}368
}369
370
$tail = trim($buf);371
if ($tail !== '') {372
$statements[] = $tail;373
}374
375
return $statements;376
}377
378
function acp_convert_dump_columns(string $statement): array {379
if (!preg_match('/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([a-zA-Z0-9_]+)`?\s*\((.*)\)\s*(?:ENGINE|DEFAULT|CHARSET|COLLATE|$)/is', $statement, $m)) {380
return [];381
}382
383
$columns = [];384
foreach (preg_split('/\R/', $m[2]) ?: [] as $line) {385
if (preg_match('/^\s*`([^`]+)`/', $line, $cm)) {386
$columns[] = $cm[1];387
}388
}389
390
return [$m[1], $columns];391
}392
393
function acp_convert_dump_column_defs(string $statement): array {394
if (!preg_match('/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([a-zA-Z0-9_]+)`?\s*\((.*)\)\s*(?:ENGINE|DEFAULT|CHARSET|COLLATE|$)/is', $statement, $m)) {395
return [];396
}397
398
$defs = [];399
foreach (preg_split('/\R/', $m[2]) ?: [] as $line) {400
$line = trim($line);401
if (!preg_match('/^`([^`]+)`\s+(.+?)(?:,)?$/', $line, $cm)) {402
continue;403
}404
$defs[$cm[1]] = '`' . $cm[1] . '` ' . rtrim($cm[2], ',');405
}406
407
return [$m[1], $defs];408
}409
410
function acp_convert_dump_split_csv(string $text): array {411
$items = [];412
$buf = '';413
$quote = '';414
$depth = 0;415
$len = strlen($text);416
417
for ($i = 0; $i < $len; $i++) {418
$ch = $text[$i];419
$next = ($i + 1 < $len) ? $text[$i + 1] : '';420
421
if ($quote !== '') {422
$buf .= $ch;423
if ($ch === '\\') {424
if ($i + 1 < $len) {425
$buf .= $text[++$i];426
}427
continue;428
}429
if ($ch === $quote) {430
if ($quote === "'" && $next === "'") {431
$buf .= $text[++$i];432
continue;433
}434
$quote = '';435
}436
continue;437
}438
439
if ($ch === "'" || $ch === '"' || $ch === '`') {440
$quote = $ch;441
$buf .= $ch;442
continue;443
}444
if ($ch === '(') $depth++;445
if ($ch === ')') $depth--;446
447
if ($ch === ',' && $depth === 0) {448
$items[] = trim($buf);449
$buf = '';450
continue;451
}452
453
$buf .= $ch;454
}455
456
if (trim($buf) !== '') {457
$items[] = trim($buf);458
}459
460
return $items;461
}462
463
function acp_convert_dump_tuples(string $values): array {464
$tuples = [];465
$buf = '';466
$quote = '';467
$depth = 0;468
$len = strlen($values);469
470
for ($i = 0; $i < $len; $i++) {471
$ch = $values[$i];472
$next = ($i + 1 < $len) ? $values[$i + 1] : '';473
474
if ($quote !== '') {475
$buf .= $ch;476
if ($ch === '\\') {477
if ($i + 1 < $len) {478
$buf .= $values[++$i];479
}480
continue;481
}482
if ($ch === $quote) {483
if ($quote === "'" && $next === "'") {484
$buf .= $values[++$i];485
continue;486
}487
$quote = '';488
}489
continue;490
}491
492
if ($ch === "'" || $ch === '"') {493
$quote = $ch;494
$buf .= $ch;495
continue;496
}497
if ($ch === '(') {498
if ($depth > 0) $buf .= $ch;499
$depth++;500
continue;501
}502
if ($ch === ')') {503
$depth--;504
if ($depth === 0) {505
$tuples[] = $buf;506
$buf = '';507
continue;508
}509
$buf .= $ch;510
continue;511
}512
if ($depth > 0) {513
$buf .= $ch;514
}515
}516
517
return $tuples;518
}519
520
function acp_convert_dump_value(string $value) {521
$value = trim($value);522
if (strcasecmp($value, 'NULL') === 0) {523
return null;524
}525
if (preg_match('/^(?:0x[0-9a-f]+|x\'[0-9a-f]*\'|b\'[01]*\')$/i', $value)) {526
return ['sql' => $value, 'archive' => $value];527
}528
if (preg_match('/^-?\d+$/', $value)) {529
return (int)$value;530
}531
if (532
(strlen($value) >= 2) &&533
(($value[0] === "'" && substr($value, -1) === "'") || ($value[0] === '"' && substr($value, -1) === '"'))534
) {535
$value = substr($value, 1, -1);536
$value = str_replace(["\\r", "\\n", "\\t", "\\0", "\\'", '\\"', "\\\\"], ["\r", "\n", "\t", "\0", "'", '"', "\\"], $value);537
$value = str_replace("''", "'", $value);538
return $value;539
}540
return $value;541
}542
543
function acp_convert_dump_model(string $sql): array {544
$model = ['tables' => [], 'columns' => [], 'column_defs' => [], 'creates' => [], 'warnings' => []];545
546
foreach (acp_convert_dump_split($sql) as $statement) {547
$create = acp_convert_dump_columns($statement);548
if ($create) {549
$model['columns'][$create[0]] = $create[1];550
$defs = acp_convert_dump_column_defs($statement);551
if ($defs) {552
$model['column_defs'][$defs[0]] = $defs[1];553
}554
$model['creates'][$create[0]] = $statement . ';';555
if (!isset($model['tables'][$create[0]])) {556
$model['tables'][$create[0]] = [];557
}558
continue;559
}560
561
if (!preg_match('/INSERT\s+INTO\s+`?([a-zA-Z0-9_]+)`?\s*(?:\((.*?)\))?\s+VALUES\s*(.*)$/is', $statement, $m)) {562
continue;563
}564
565
$table = $m[1];566
$insertColumns = (string)($m[2] ?? '');567
$columns = [];568
if (trim($insertColumns) !== '') {569
foreach (acp_convert_dump_split_csv($insertColumns) as $col) {570
$columns[] = trim($col, " `\t\n\r\0\x0B");571
}572
} else {573
$columns = $model['columns'][$table] ?? [];574
}575
576
if (!$columns) {577
$model['warnings'][] = 'Skipped INSERT for ' . $table . ' because no column list was available.';578
continue;579
}580
581
foreach (acp_convert_dump_tuples((string)$m[3]) as $tuple) {582
$values = acp_convert_dump_split_csv($tuple);583
$row = [];584
foreach ($columns as $i => $column) {585
$row[$column] = array_key_exists($i, $values) ? acp_convert_dump_value($values[$i]) : null;586
}587
$model['tables'][$table][] = $row;588
}589
}590
591
return $model;592
}593
594
function acp_convert_row_value(array $row, array $keys, $default = '') {595
foreach ($keys as $key) {596
if (array_key_exists($key, $row) && $row[$key] !== null) {597
return $row[$key];598
}599
}600
return $default;601
}602
603
function acp_convert_dump_source(array $model, string $requested): string {604
if ($requested === 'myaac' || $requested === 'gesior') {605
return $requested;606
}607
foreach (array_keys($model['tables']) as $table) {608
if (strpos($table, 'myaac_') === 0) return 'myaac';609
if (strpos($table, 'z_') === 0) return 'gesior';610
}611
return 'legacy';612
}613
614
function acp_convert_dump_stats(array $model, string $source): array {615
$stats = ['tables' => count($model['tables']), 'rows' => 0, 'known' => 0, 'custom' => 0];616
$known = [617
'accounts', 'players', 'myaac_news', 'myaac_changelog', 'myaac_forum_boards',618
'myaac_forum', 'myaac_pages', 'myaac_gallery', 'myaac_menu', 'myaac_config',619
'myaac_settings', 'z_news_big', 'z_news_tickers', 'z_forum_boards', 'z_forum',620
'z_pages', 'z_config',621
];622
623
foreach ($model['tables'] as $table => $rows) {624
$count = count($rows);625
$stats['rows'] += $count;626
if (in_array($table, $known, true)) {627
$stats['known'] += $count;628
} else {629
$stats['custom'] += $count;630
}631
}632
633
return $stats;634
}635
636
function acp_convert_dump_player_name(array $model, int $playerId): string {637
if ($playerId <= 0 || empty($model['tables']['players'])) {638
return '';639
}640
641
foreach ($model['tables']['players'] as $player) {642
if ((int)acp_convert_row_value($player, ['id'], 0) === $playerId) {643
return substr((string)acp_convert_row_value($player, ['name'], ''), 0, 50);644
}645
}646
647
return '';648
}649
650
function acp_convert_dump_sql_values(array $rows, array $columns): string {651
$out = [];652
foreach ($rows as $row) {653
$values = [];654
foreach ($columns as $column) {655
$value = $row[$column] ?? null;656
$values[] = acp_convert_dump_sql_value($value);657
}658
$out[] = '(' . implode(', ', $values) . ')';659
}660
return implode(",\n", $out);661
}662
663
function acp_convert_dump_sql_value($value): string {664
if (is_array($value) && isset($value['sql'])) {665
return (string)$value['sql'];666
}667
return is_int($value) ? (string)$value : acp_convert_sql_literal($value);668
}669
670
function acp_convert_dump_archive_row(array $row): array {671
foreach ($row as $column => $value) {672
if (is_array($value) && isset($value['sql'])) {673
$row[$column] = (string)($value['archive'] ?? $value['sql']);674
}675
}676
return $row;677
}678
679
function acp_convert_dump_integer_definition(string $definition, string $column, array $rows): array {680
$pattern = '/^(`' . preg_quote($column, '/') . '`\s+)(tinyint|smallint|mediumint|int|bigint)(\s+unsigned)?(.*)$/i';681
if (!preg_match($pattern, $definition, $match)) {682
return ['definition' => $definition, 'type' => '', 'widened_from' => []];683
}684
685
$types = ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'];686
$signedMax = [127, 32767, 8388607, 2147483647, PHP_INT_MAX];687
$signedMin = [-128, -32768, -8388608, -2147483648, PHP_INT_MIN];688
$unsignedMax = [255, 65535, 16777215, 4294967295, PHP_INT_MAX];689
$type = strtolower($match[2]);690
$unsigned = trim((string)$match[3]) !== '';691
$index = array_search($type, $types, true);692
if ($index === false) {693
return ['definition' => $definition, 'type' => '', 'widened_from' => []];694
}695
696
$minimum = 0;697
$maximum = 0;698
foreach ($rows as $row) {699
$value = $row[$column] ?? null;700
if (!is_int($value)) {701
continue;702
}703
$minimum = min($minimum, $value);704
$maximum = max($maximum, $value);705
}706
707
$required = $index;708
while ($required < count($types) - 1) {709
$fitsMinimum = $unsigned ? $minimum >= 0 : $minimum >= $signedMin[$required];710
$fitsMaximum = $maximum <= ($unsigned ? $unsignedMax[$required] : $signedMax[$required]);711
if ($fitsMinimum && $fitsMaximum) {712
break;713
}714
$required++;715
}716
717
if ($required === $index) {718
return [719
'definition' => $definition,720
'type' => $type,721
'widened_from' => array_slice($types, 0, $index),722
];723
}724
725
$replacement = $match[1] . $types[$required] . ($unsigned ? ' unsigned' : '') . $match[4];726
return [727
'definition' => $replacement,728
'type' => $types[$required],729
'widened_from' => array_slice($types, 0, $required),730
];731
}732
733
function acp_convert_dump_sql_union(array $rows, array $columns): string {734
$out = [];735
foreach ($rows as $index => $row) {736
$values = [];737
foreach ($columns as $column) {738
$value = $row[$column] ?? null;739
$sqlValue = acp_convert_dump_sql_value($value);740
$values[] = $index === 0 ? $sqlValue . ' AS `' . $column . '`' : $sqlValue;741
}742
$out[] = 'SELECT ' . implode(', ', $values);743
}744
return implode("\nUNION ALL\n", $out);745
}746
747
function acp_convert_dump_mapped_id_sql(string $source, string $sourceTable, int $sourceId, string $targetTable): string {748
if ($sourceId <= 0) {749
return '0';750
}751
752
return "COALESCE((SELECT `target_id` FROM `znote_convert_map`"753
. " WHERE `source` = " . acp_convert_sql_literal($source)754
. " AND `source_table` = " . acp_convert_sql_literal($sourceTable)755
. " AND `source_id` = " . acp_convert_sql_literal((string)$sourceId)756
. " AND `target_table` = " . acp_convert_sql_literal($targetTable)757
. " LIMIT 1), {$sourceId})";758
}759
760
function acp_convert_dump_reference_value(string $source, string $table, string $column, $value) {761
if (is_array($value) && isset($value['sql'])) {762
return $value;763
}764
$id = (int)$value;765
if ($id <= 0) {766
return $value;767
}768
769
$accountColumns = ['account_id', 'author_aid', 'last_edit_aid', 'original_account_id', 'bidder_account_id'];770
$playerColumns = ['player_id', 'author_guid'];771
if ($table === 'players' && $column === 'account_id') {772
return ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')];773
}774
if ($table !== 'accounts' && in_array($column, $accountColumns, true)) {775
return ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')];776
}777
if ($table !== 'players' && in_array($column, $playerColumns, true)) {778
return ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $id, 'players')];779
}780
781
return $value;782
}783
784
function acp_convert_dump_ordered_tables(array $model): array {785
$orderedTables = array_keys($model['tables']);786
usort($orderedTables, static function (string $left, string $right): int {787
$priority = ['accounts' => 0, 'players' => 1];788
return ($priority[$left] ?? 2) <=> ($priority[$right] ?? 2);789
});790
791
return $orderedTables;792
}793
794
function acp_convert_dump_restore_add_column_sql(string $table, string $column, string $definition, int $guard): string {795
$checkVar = '@znote_restore_col_' . $guard;796
$stmtName = 'znote_restore_col_stmt_' . $guard;797
798
return "SET {$checkVar} := IF(799
(800
SELECT COUNT(*)801
FROM INFORMATION_SCHEMA.COLUMNS802
WHERE TABLE_SCHEMA = DATABASE()803
AND TABLE_NAME = " . acp_convert_sql_literal($table) . "804
AND COLUMN_NAME = " . acp_convert_sql_literal($column) . "805
) = 0,806
" . acp_convert_sql_literal('ALTER TABLE `' . $table . '` ADD ' . $definition) . ",807
'SELECT 1'808
);809
PREPARE {$stmtName} FROM {$checkVar};810
EXECUTE {$stmtName};811
DEALLOCATE PREPARE {$stmtName};";812
}813
814
function acp_convert_dump_restore_widen_column_sql(string $table, string $column, string $definition, array $smallerTypes, int $guard): string {815
$checkVar = '@znote_restore_widen_' . $guard;816
$stmtName = 'znote_restore_widen_stmt_' . $guard;817
$smallerTypesSql = implode(', ', array_map('acp_convert_sql_literal', $smallerTypes));818
819
return "SET {$checkVar} := IF(820
EXISTS(821
SELECT 1822
FROM INFORMATION_SCHEMA.COLUMNS823
WHERE TABLE_SCHEMA = DATABASE()824
AND TABLE_NAME = " . acp_convert_sql_literal($table) . "825
AND COLUMN_NAME = " . acp_convert_sql_literal($column) . "826
AND DATA_TYPE IN ({$smallerTypesSql})827
),828
" . acp_convert_sql_literal('ALTER TABLE `' . $table . '` MODIFY ' . $definition) . ",829
'SELECT 1'830
);831
PREPARE {$stmtName} FROM {$checkVar};832
EXECUTE {$stmtName};833
DEALLOCATE PREPARE {$stmtName};";834
}835
836
function acp_convert_dump_restore_schema_sql(string $table, array $columns, array $defs, array $rows, int &$guard): array {837
$out = [];838
foreach ($columns as $column) {839
if (!isset($defs[$column]) || !preg_match('/^[a-zA-Z0-9_]+$/', (string)$column)) {840
continue;841
}842
843
$definition = acp_convert_dump_integer_definition($defs[$column], $column, $rows);844
$effectiveDef = (string)$definition['definition'];845
$out[] = acp_convert_dump_restore_add_column_sql($table, $column, $effectiveDef, ++$guard);846
847
if (!empty($definition['widened_from'])) {848
$out[] = acp_convert_dump_restore_widen_column_sql($table, $column, $effectiveDef, $definition['widened_from'], ++$guard);849
}850
}851
852
return $out;853
}854
855
function acp_convert_dump_restore_updates(string $table, array $columns): array {856
$updates = [];857
foreach ($columns as $column) {858
if (($table === 'accounts' || $table === 'players') && $column === 'id') {859
continue;860
}861
$updates[] = '`' . $column . '` = VALUES(`' . $column . '`)';862
}863
864
return $updates;865
}866
867
function acp_convert_dump_restore_identity_row_sql(string $source, string $table, array $row, array $columns, array $quotedColumns, array $updates): array {868
$sourceId = (int)acp_convert_row_value($row, ['id'], 0);869
if ($sourceId <= 0) {870
return [];871
}872
873
$mapVariable = $table === 'accounts' ? '@znote_import_account_id' : '@znote_import_player_id';874
$sourceName = trim((string)acp_convert_row_value($row, ['name'], ''));875
$sameNameSql = $sourceName !== ''876
? "(SELECT `id` FROM `{$table}` WHERE `name` = " . acp_convert_sql_literal($sourceName) . " LIMIT 1),"877
: '';878
879
$out = [];880
$out[] = "SET {$mapVariable} := COALESCE(881
(SELECT `target_id` FROM `znote_convert_map`882
WHERE `source` = " . acp_convert_sql_literal($source) . "883
AND `source_table` = " . acp_convert_sql_literal($table) . "884
AND `source_id` = " . acp_convert_sql_literal((string)$sourceId) . "885
AND `target_table` = " . acp_convert_sql_literal($table) . " LIMIT 1),886
{$sameNameSql}887
IF(EXISTS(SELECT 1 FROM `{$table}` WHERE `id` = {$sourceId}),888
(SELECT `next_id` FROM (SELECT COALESCE(MAX(`id`), 0) + 1 AS `next_id` FROM `{$table}`) AS `available_id`),889
{$sourceId}890
)891
);";892
$out[] = "INSERT INTO `znote_convert_map`893
(`source`, `source_table`, `source_id`, `target_table`, `target_id`, `created`)894
VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", "895
. acp_convert_sql_literal((string)$sourceId) . ", " . acp_convert_sql_literal($table)896
. ", {$mapVariable}, UNIX_TIMESTAMP())897
ON DUPLICATE KEY UPDATE `target_id` = VALUES(`target_id`);";898
899
$mappedRow = $row;900
$mappedRow['id'] = ['sql' => $mapVariable];901
foreach ($columns as $column) {902
$mappedRow[$column] = acp_convert_dump_reference_value($source, $table, $column, $mappedRow[$column] ?? null);903
}904
$out[] = "INSERT INTO `{$table}` (" . implode(', ', $quotedColumns) . ") VALUES\n"905
. acp_convert_dump_sql_values([$mappedRow], $columns)906
. "\nON DUPLICATE KEY UPDATE " . implode(', ', $updates) . ';';907
908
return $out;909
}910
911
function acp_convert_dump_restore_identity_table_sql(string $source, string $table, array $rows, array $columns, array $quotedColumns, array $updates): array {912
$out = [];913
foreach ($rows as $row) {914
array_push($out, ...acp_convert_dump_restore_identity_row_sql($source, $table, $row, $columns, $quotedColumns, $updates));915
}916
917
return $out;918
}919
920
function acp_convert_dump_restore_regular_table_sql(string $source, string $table, array $rows, array $columns, array $quotedColumns, array $updates): array {921
$mappedRows = [];922
foreach ($rows as $row) {923
foreach ($columns as $column) {924
$row[$column] = acp_convert_dump_reference_value($source, $table, $column, $row[$column] ?? null);925
}926
$mappedRows[] = $row;927
}928
929
if (!$mappedRows) {930
return [];931
}932
933
return [934
"INSERT INTO `{$table}` (" . implode(', ', $quotedColumns) . ") VALUES\n"935
. acp_convert_dump_sql_values($mappedRows, $columns)936
. "\nON DUPLICATE KEY UPDATE " . implode(', ', $updates) . ';',937
];938
}939
940
function acp_convert_dump_restore_table_sql(array $model, string $source, string $table, int &$guard): array {941
if (!preg_match('/^[a-zA-Z0-9_]+$/', (string)$table)) {942
return [];943
}944
945
$rows = $model['tables'][$table];946
$columns = $model['columns'][$table] ?? [];947
$defs = $model['column_defs'][$table] ?? [];948
$create = trim((string)($model['creates'][$table] ?? ''));949
if ($create === '' || !$columns) {950
return [];951
}952
953
$out = ["-- Restore source table: {$table}", $create];954
array_push($out, ...acp_convert_dump_restore_schema_sql($table, $columns, $defs, $rows, $guard));955
if (!$rows) {956
return $out;957
}958
959
$quotedColumns = array_map(static fn($column) => '`' . $column . '`', $columns);960
$updates = acp_convert_dump_restore_updates($table, $columns);961
if ($table === 'accounts' || $table === 'players') {962
array_push($out, ...acp_convert_dump_restore_identity_table_sql($source, $table, $rows, $columns, $quotedColumns, $updates));963
return $out;964
}965
966
array_push($out, ...acp_convert_dump_restore_regular_table_sql($source, $table, $rows, $columns, $quotedColumns, $updates));967
return $out;968
}969
970
function acp_convert_dump_restore_sql(array $model, string $source): string {971
$out = [];972
$guard = 0;973
974
foreach (acp_convert_dump_ordered_tables($model) as $table) {975
$section = acp_convert_dump_restore_table_sql($model, $source, $table, $guard);976
if ($section) {977
array_push($out, ...$section);978
}979
}980
981
return trim(implode("\n\n", $out));982
}983
984
function acp_convert_dump_header_sql(array $model, string $source): array {985
$out = [986
"-- ZnoteX uploaded SQL conversion",987
"-- Source detected: {$source}",988
"-- Generated " . date('Y-m-d H:i:s'),989
"START TRANSACTION;",990
"",991
file_get_contents('SQL/migrations/2.0.0_pages_and_convert_map.sql') ?: '',992
file_get_contents('SQL/migrations/2.0.0_gallery_image_url.sql') ?: '',993
];994
995
$restoreSql = acp_convert_dump_restore_sql($model, $source);996
if ($restoreSql !== '') {997
$out[] = "";998
$out[] = "-- Restore original source tables, rows and custom columns";999
$out[] = $restoreSql;1000
}1001
1002
return $out;1003
}1004
1005
function acp_convert_dump_account_compat_sql(array $model, string $source): array {1006
if (!empty($model['tables']['accounts'])) {1007
$rows = [];1008
foreach ($model['tables']['accounts'] as $row) {1009
$id = (int)acp_convert_row_value($row, ['id'], 0);1010
if ($id <= 0) continue;1011
$created = (int)acp_convert_row_value($row, ['creation', 'created'], time());1012
$rows[] = [1013
'account_id' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')],1014
'ip' => 0,1015
'created' => $created > 0 ? $created : time(),1016
'flag' => '',1017
];1018
}1019
if ($rows) {1020
return [1021
"-- Account compatibility rows",1022
"INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)1023
SELECT `v`.`account_id`, `v`.`ip`, `v`.`created`, `v`.`flag`1024
FROM (1025
" . acp_convert_dump_sql_union($rows, ['account_id', 'ip', 'created', 'flag']) . "1026
) AS `v`1027
WHERE NOT EXISTS (1028
SELECT 1 FROM `znote_accounts` AS `z` WHERE `z`.`account_id` = `v`.`account_id`1029
);",1030
];1031
}1032
}1033
1034
return [];1035
}1036
1037
function acp_convert_dump_player_compat_sql(array $model, string $source): array {1038
if (!empty($model['tables']['players'])) {1039
$rows = [];1040
foreach ($model['tables']['players'] as $row) {1041
$id = (int)acp_convert_row_value($row, ['id'], 0);1042
if ($id <= 0) continue;1043
$rows[] = [1044
'player_id' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $id, 'players')],1045
'created' => time(),1046
'hide_char' => 0,1047
'comment' => '',1048
];1049
}1050
if ($rows) {1051
return [1052
"-- Player compatibility rows",1053
"INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)1054
SELECT `v`.`player_id`, `v`.`created`, `v`.`hide_char`, `v`.`comment`1055
FROM (1056
" . acp_convert_dump_sql_union($rows, ['player_id', 'created', 'hide_char', 'comment']) . "1057
) AS `v`1058
WHERE NOT EXISTS (1059
SELECT 1 FROM `znote_players` AS `z` WHERE `z`.`player_id` = `v`.`player_id`1060
);",1061
];1062
}1063
}1064
1065
return [];1066
}1067
1068
function acp_convert_dump_news_sql(array $model, string $source): array {1069
$newsTable = $source === 'gesior' ? 'z_news_big' : 'myaac_news';1070
if (!empty($model['tables'][$newsTable])) {1071
$rows = [];1072
foreach ($model['tables'][$newsTable] as $row) {1073
$hide = (int)acp_convert_row_value($row, ['hide', 'hide_news'], 0);1074
if ($hide !== 0) continue;1075
$title = substr(trim((string)acp_convert_row_value($row, ['title', 'topic', 'name'], 'Imported news')), 0, 30);1076
$body = (string)acp_convert_row_value($row, ['body', 'text'], '');1077
$date = (int)acp_convert_row_value($row, ['date', 'time'], time());1078
$pid = (int)acp_convert_row_value($row, ['player_id', 'author_id', 'pid'], 0);1079
$rows[] = [1080
'title' => $title,1081
'text' => acp_convert_news_text($body),1082
'date' => $date > 0 ? $date : time(),1083
'pid' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $pid, 'players')],1084
];1085
}1086
if ($rows) {1087
return [1088
"-- News",1089
"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)1090
SELECT `v`.`title`, `v`.`text`, `v`.`date`, `v`.`pid`1091
FROM (1092
" . acp_convert_dump_sql_union($rows, ['title', 'text', 'date', 'pid']) . "1093
) AS `v`1094
WHERE NOT EXISTS (1095
SELECT 1 FROM `znote_news` AS `z` WHERE `z`.`title` = `v`.`title` AND `z`.`date` = `v`.`date`1096
);",1097
];1098
}1099
}1100
1101
return [];1102
}1103
1104
function acp_convert_dump_changelog_sql(array $model, string $source): array {1105
$changeTable = $source === 'gesior' ? 'z_news_tickers' : 'myaac_changelog';1106
if (!empty($model['tables'][$changeTable])) {1107
$rows = [];1108
foreach ($model['tables'][$changeTable] as $row) {1109
$hide = (int)acp_convert_row_value($row, ['hide', 'hide_ticker'], 0);1110
if ($hide !== 0) continue;1111
$body = (string)acp_convert_row_value($row, ['body', 'text'], '');1112
$text = substr(acp_convert_strip_html($body), 0, 254);1113
if ($text === '') continue;1114
$date = (int)acp_convert_row_value($row, ['date', 'time'], time());1115
$rows[] = ['text' => $text, 'time' => $date > 0 ? $date : time(), 'report_id' => 0, 'status' => (int)acp_convert_row_value($row, ['type'], 0)];1116
}1117
if ($rows) {1118
return [1119
"-- Changelog / tickers",1120
"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)1121
SELECT `v`.`text`, `v`.`time`, `v`.`report_id`, `v`.`status`1122
FROM (1123
" . acp_convert_dump_sql_union($rows, ['text', 'time', 'report_id', 'status']) . "1124
) AS `v`1125
WHERE NOT EXISTS (1126
SELECT 1 FROM `znote_changelog` AS `z` WHERE `z`.`text` = `v`.`text` AND `z`.`time` = `v`.`time`1127
);",1128
];1129
}1130
}1131
1132
return [];1133
}1134
1135
function acp_convert_dump_pages_sql(array $model, string $source): array {1136
if (!empty($model['tables']['myaac_pages'])) {1137
$rows = [];1138
foreach ($model['tables']['myaac_pages'] as $row) {1139
if ((int)acp_convert_row_value($row, ['hide'], 0) !== 0) continue;1140
$id = (int)acp_convert_row_value($row, ['id'], 0);1141
$slug = acp_convert_slug((string)acp_convert_row_value($row, ['name', 'slug'], ''), 'imported-page-' . $id);1142
$title = substr(trim((string)acp_convert_row_value($row, ['title', 'name'], $slug)), 0, 100);1143
$date = (int)acp_convert_row_value($row, ['date'], time());1144
$rows[] = [1145
'slug' => $slug,1146
'title' => $title,1147
'body' => acp_convert_news_text((string)acp_convert_row_value($row, ['body', 'text'], '')),1148
'created' => $date > 0 ? $date : time(),1149
'updated' => $date > 0 ? $date : time(),1150
'player_id' => ['sql' => acp_convert_dump_mapped_id_sql(1151
$source,1152
'players',1153
(int)acp_convert_row_value($row, ['player_id'], 0),1154
'players'1155
)],1156
'access' => (int)acp_convert_row_value($row, ['access'], 0),1157
'active' => 1,1158
];1159
}1160
if ($rows) {1161
return [1162
"-- Custom pages",1163
"INSERT INTO `znote_pages` (`slug`, `title`, `body`, `created`, `updated`, `player_id`, `access`, `active`) VALUES\n"1164
. acp_convert_dump_sql_values($rows, ['slug', 'title', 'body', 'created', 'updated', 'player_id', 'access', 'active'])1165
. "\nON DUPLICATE KEY UPDATE `title` = VALUES(`title`), `body` = VALUES(`body`), `updated` = VALUES(`updated`);",1166
];1167
}1168
}1169
1170
return [];1171
}1172
1173
function acp_convert_dump_forum_boards_sql(array $model, string $source): array {1174
$boardTable = $source === 'gesior' ? 'z_forum_boards' : 'myaac_forum_boards';1175
$idOffset = $source === 'gesior' ? 2000000 : 1000000;1176
if (!empty($model['tables'][$boardTable])) {1177
$rows = [];1178
foreach ($model['tables'][$boardTable] as $row) {1179
$id = (int)acp_convert_row_value($row, ['id'], 0);1180
$name = substr(trim((string)acp_convert_row_value($row, ['name', 'title'], 'Imported board')), 0, 50);1181
if ($id <= 0 || $name === '') continue;1182
$rows[] = [1183
'id' => $idOffset + $id,1184
'name' => $name,1185
'access' => max(1, (int)acp_convert_row_value($row, ['access'], 1)),1186
'closed' => (int)acp_convert_row_value($row, ['closed'], 0),1187
'hidden' => (int)acp_convert_row_value($row, ['hide', 'hidden'], 0),1188
'guild_id' => (int)acp_convert_row_value($row, ['guild', 'guild_id'], 0),1189
];1190
}1191
if ($rows) {1192
return [1193
"-- Forum boards",1194
"INSERT INTO `znote_forum` (`id`, `name`, `access`, `closed`, `hidden`, `guild_id`) VALUES\n"1195
. acp_convert_dump_sql_values($rows, ['id', 'name', 'access', 'closed', 'hidden', 'guild_id'])1196
. "\nON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `access` = VALUES(`access`), `closed` = VALUES(`closed`), `hidden` = VALUES(`hidden`), `guild_id` = VALUES(`guild_id`);",1197
];1198
}1199
}1200
1201
return [];1202
}1203
1204
function acp_convert_dump_forum_sql(array $model, string $source): array {1205
$sql = [];1206
$idOffset = $source === 'gesior' ? 2000000 : 1000000;1207
$forumTable = !empty($model['tables']['myaac_forum']) ? 'myaac_forum' : (!empty($model['tables']['z_forum']) ? 'z_forum' : '');1208
if ($forumTable !== '') {1209
$threads = [];1210
$posts = [];1211
1212
foreach ($model['tables'][$forumTable] as $row) {1213
$id = (int)acp_convert_row_value($row, ['id'], 0);1214
$firstPost = (int)acp_convert_row_value($row, ['first_post'], 0);1215
$playerId = (int)acp_convert_row_value($row, ['author_guid', 'player_id'], 0);1216
$mappedPlayerId = ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $playerId, 'players')];1217
$created = (int)acp_convert_row_value($row, ['post_date', 'created', 'date'], time());1218
$updated = (int)acp_convert_row_value($row, ['edit_date', 'updated'], $created);1219
$text = sanitize((string)acp_convert_row_value($row, ['post_text', 'text'], ''));1220
1221
if ($id > 0 && ($firstPost === 0 || $firstPost === $id)) {1222
$oldBoard = (int)acp_convert_row_value($row, ['section', 'forum_id'], 1);1223
$title = substr(trim((string)acp_convert_row_value($row, ['post_topic', 'title'], 'Imported thread')), 0, 50);1224
$threads[] = [1225
'id' => $idOffset + $id,1226
'forum_id' => $idOffset + max(1, $oldBoard),1227
'player_id' => $mappedPlayerId,1228
'player_name' => (string)acp_convert_row_value($row, ['author', 'player_name'], acp_convert_dump_player_name($model, $playerId)),1229
'title' => $title !== '' ? $title : 'Imported thread',1230
'text' => $text,1231
'created' => $created > 0 ? $created : time(),1232
'updated' => $updated > 0 ? $updated : ($created > 0 ? $created : time()),1233
'sticky' => (int)acp_convert_row_value($row, ['sticked', 'sticky'], 0),1234
'hidden' => (int)acp_convert_row_value($row, ['hidden', 'hide'], 0),1235
'closed' => (int)acp_convert_row_value($row, ['closed'], 0),1236
];1237
} elseif ($id > 0 && $firstPost > 0) {1238
$posts[] = [1239
'thread_id' => $idOffset + $firstPost,1240
'player_id' => $mappedPlayerId,1241
'player_name' => (string)acp_convert_row_value($row, ['author', 'player_name'], acp_convert_dump_player_name($model, $playerId)),1242
'text' => $text,1243
'created' => $created > 0 ? $created : time(),1244
'updated' => $updated > 0 ? $updated : ($created > 0 ? $created : time()),1245
];1246
}1247
}1248
1249
if ($threads) {1250
$sql[] = "-- Forum threads";1251
$sql[] = "INSERT INTO `znote_forum_threads` (`id`, `forum_id`, `player_id`, `player_name`, `title`, `text`, `created`, `updated`, `sticky`, `hidden`, `closed`) VALUES\n"1252
. acp_convert_dump_sql_values($threads, ['id', 'forum_id', 'player_id', 'player_name', 'title', 'text', 'created', 'updated', 'sticky', 'hidden', 'closed'])1253
. "\nON DUPLICATE KEY UPDATE `forum_id` = VALUES(`forum_id`), `title` = VALUES(`title`), `text` = VALUES(`text`), `updated` = VALUES(`updated`);";1254
}1255
if ($posts) {1256
$sql[] = "-- Forum posts";1257
$sql[] = "INSERT INTO `znote_forum_posts` (`thread_id`, `player_id`, `player_name`, `text`, `created`, `updated`)1258
SELECT `v`.`thread_id`, `v`.`player_id`, `v`.`player_name`, `v`.`text`, `v`.`created`, `v`.`updated`1259
FROM (1260
" . acp_convert_dump_sql_union($posts, ['thread_id', 'player_id', 'player_name', 'text', 'created', 'updated']) . "1261
) AS `v`1262
WHERE NOT EXISTS (1263
SELECT 1 FROM `znote_forum_posts` AS `z`1264
WHERE `z`.`thread_id` = `v`.`thread_id`1265
AND `z`.`player_id` = `v`.`player_id`1266
AND `z`.`created` = `v`.`created`1267
AND `z`.`text` = `v`.`text`1268
);";1269
}1270
}1271
1272
return $sql;1273
}1274
1275
function acp_convert_dump_gallery_sql(array $model, string $source): array {1276
if (!empty($model['tables']['myaac_gallery'])) {1277
$rows = [];1278
foreach ($model['tables']['myaac_gallery'] as $row) {1279
if ((int)acp_convert_row_value($row, ['hide'], 0) !== 0) continue;1280
$image = trim((string)acp_convert_row_value($row, ['image', 'url'], ''));1281
if ($image === '') continue;1282
$comment = (string)acp_convert_row_value($row, ['comment', 'description', 'desc'], '');1283
$rows[] = [1284
'title' => substr($comment !== '' ? $comment : 'Imported image', 0, 30),1285
'desc' => $comment,1286
'date' => (int)acp_convert_row_value($row, ['date'], time()),1287
'status' => 2,1288
'image' => substr($image, 0, 255),1289
'delhash' => '',1290
'account_id' => ['sql' => acp_convert_dump_mapped_id_sql(1291
$source,1292
'accounts',1293
(int)acp_convert_row_value($row, ['account_id'], 0),1294
'accounts'1295
)],1296
];1297
}1298
if ($rows) {1299
return [1300
"-- Gallery",1301
"INSERT INTO `znote_images` (`title`, `desc`, `date`, `status`, `image`, `delhash`, `account_id`)1302
SELECT `v`.`title`, `v`.`desc`, `v`.`date`, `v`.`status`, `v`.`image`, `v`.`delhash`, `v`.`account_id`1303
FROM (1304
" . acp_convert_dump_sql_union($rows, ['title', 'desc', 'date', 'status', 'image', 'delhash', 'account_id']) . "1305
) AS `v`1306
WHERE NOT EXISTS (1307
SELECT 1 FROM `znote_images` AS `z` WHERE `z`.`image` = `v`.`image`1308
);",1309
];1310
}1311
}1312
1313
return [];1314
}1315
1316
function acp_convert_dump_menu_sql(array $model): array {1317
if (!empty($model['tables']['myaac_menu'])) {1318
$rows = [];1319
foreach ($model['tables']['myaac_menu'] as $row) {1320
if ((int)acp_convert_row_value($row, ['enabled'], 1) !== 1) continue;1321
$label = substr(trim((string)acp_convert_row_value($row, ['name', 'label'], '')), 0, 64);1322
$url = substr(trim((string)acp_convert_row_value($row, ['link', 'url'], '')), 0, 255);1323
if ($label === '' || $url === '') continue;1324
$rows[] = [1325
'location' => 'main',1326
'parent_id' => ['sql' => acp_convert_dump_menu_parent_sql((int)acp_convert_row_value($row, ['category'], 0))],1327
'label' => $label,1328
'url' => $url,1329
'icon' => '',1330
'target' => (int)acp_convert_row_value($row, ['blank'], 0) === 1 ? '_blank' : '',1331
'visibility' => 'all',1332
'sort_order' => (int)acp_convert_row_value($row, ['ordering', 'sort_order'], 0),1333
'active' => 1,1334
];1335
}1336
if ($rows) {1337
return [1338
"-- Menu",1339
"INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `target`, `visibility`, `sort_order`, `active`)1340
SELECT `v`.`location`, `v`.`parent_id`, `v`.`label`, `v`.`url`, `v`.`icon`, `v`.`target`, `v`.`visibility`, `v`.`sort_order`, `v`.`active`1341
FROM (1342
" . acp_convert_dump_sql_union($rows, ['location', 'parent_id', 'label', 'url', 'icon', 'target', 'visibility', 'sort_order', 'active']) . "1343
) AS `v`1344
WHERE `v`.`parent_id` IS NOT NULL1345
AND NOT EXISTS (1346
SELECT 1 FROM `znote_menu` AS `z`1347
WHERE `z`.`location` = `v`.`location`1348
AND `z`.`label` = `v`.`label`1349
AND `z`.`url` = `v`.`url`1350
);",1351
];1352
}1353
}1354
1355
return [];1356
}1357
1358
function acp_convert_dump_legacy_config_sql(array $model): array {1359
$sql = [];1360
foreach (['myaac_config' => 'legacy:myaac:', 'myaac_settings' => 'legacy:myaac_setting:', 'z_config' => 'legacy:gesior:'] as $configTable => $prefix) {1361
if (empty($model['tables'][$configTable])) continue;1362
$rows = [];1363
foreach ($model['tables'][$configTable] as $row) {1364
$key = (string)acp_convert_row_value($row, ['key', 'name', 'config'], '');1365
if ($key === '') continue;1366
$rows[] = ['key' => substr($prefix . $key, 0, 64), 'value' => (string)acp_convert_row_value($row, ['value'], '')];1367
}1368
if ($rows) {1369
$sql[] = "-- Legacy config: {$configTable}";1370
$sql[] = "INSERT INTO `znote_config` (`key`, `value`) VALUES\n"1371
. acp_convert_dump_sql_values($rows, ['key', 'value'])1372
. "\nON DUPLICATE KEY UPDATE `value` = VALUES(`value`);";1373
}1374
}1375
1376
return $sql;1377
}1378
1379
function acp_convert_dump_archive_sql(array $model, string $source, int $captured): array {1380
$sql = [];1381
foreach ($model['tables'] as $table => $rows) {1382
$sql[] = "-- Legacy archive: {$table}";1383
$schemaSql = (string)($model['creates'][$table] ?? '');1384
$sql[] = "INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`) VALUES ("1385
. acp_convert_sql_literal($source) . ', ' . acp_convert_sql_literal($table) . ', ' . acp_convert_sql_literal($schemaSql) . ', ' . count($rows) . ", {$captured})1386
ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);";1387
$sql[] = "DELETE FROM `znote_legacy_rows` WHERE `source` = " . acp_convert_sql_literal($source)1388
. " AND `table_name` = " . acp_convert_sql_literal($table) . ';';1389
foreach ($rows as $row) {1390
$pk = substr(acp_convert_row_pk($table, $row), 0, 128);1391
$json = json_encode(acp_convert_dump_archive_row($row), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);1392
if ($json === false) $json = '{}';1393
$sql[] = "INSERT INTO `znote_legacy_rows` (`source`, `table_name`, `source_pk`, `row_json`, `captured`) VALUES ("1394
. acp_convert_sql_literal($source) . ', '1395
. acp_convert_sql_literal($table) . ', '1396
. acp_convert_sql_literal($pk) . ', '1397
. acp_convert_sql_literal($json) . ', '1398
. $captured . ');';1399
}1400
}1401
1402
return $sql;1403
}1404
1405
function acp_convert_dump_script(array $model, string $requestedSource): string {1406
$source = acp_convert_dump_source($model, $requestedSource);1407
$captured = time();1408
$sql = [];1409
1410
foreach ([1411
acp_convert_dump_header_sql($model, $source),1412
acp_convert_dump_account_compat_sql($model, $source),1413
acp_convert_dump_player_compat_sql($model, $source),1414
acp_convert_dump_news_sql($model, $source),1415
acp_convert_dump_changelog_sql($model, $source),1416
acp_convert_dump_pages_sql($model, $source),1417
acp_convert_dump_forum_boards_sql($model, $source),1418
acp_convert_dump_forum_sql($model, $source),1419
acp_convert_dump_gallery_sql($model, $source),1420
acp_convert_dump_menu_sql($model),1421
acp_convert_dump_legacy_config_sql($model),1422
acp_convert_dump_archive_sql($model, $source, $captured),1423
] as $section) {1424
array_push($sql, ...$section);1425
}1426
1427
$sql[] = "COMMIT;";1428
return trim(implode("\n\n", $sql)) . "\n";1429
}1430
1431
function acp_convert_legacy_archive_sql(string $source): string {1432
$out = [];1433
$captured = time();1434
1435
foreach (acp_convert_legacy_tables($source) as $table) {1436
$count = acp_convert_count($table);1437
$out[] = "-- Preserve legacy table {$table}";1438
$out[] = "INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`)1439
VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", '', {$count}, {$captured})1440
ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);";1441
$out[] = "DELETE FROM `znote_legacy_rows`1442
WHERE `source` = " . acp_convert_sql_literal($source) . "1443
AND `table_name` = " . acp_convert_sql_literal($table) . ";";1444
1445
$rows = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($table) . '`;') ?: [];1446
foreach ($rows as $row) {1447
$pk = substr(acp_convert_row_pk($table, $row), 0, 128);1448
$json = json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);1449
if ($json === false) {1450
$json = '{}';1451
}1452
$out[] = "INSERT INTO `znote_legacy_rows` (`source`, `table_name`, `source_pk`, `row_json`, `captured`)1453
VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", " . acp_convert_sql_literal($pk) . ", " . acp_convert_sql_literal($json) . ", {$captured});";1454
}1455
}1456
1457
return implode("\n\n", $out);1458
}1459
1460
function acp_convert_player_name(int $playerId): string {1461
if ($playerId <= 0) {1462
return '';1463
}1464
$row = db()->fetchOne('SELECT `name` FROM `players` WHERE `id` = ? LIMIT 1;', [$playerId]);1465
return is_array($row) ? (string)$row['name'] : '';1466
}1467
1468
function acp_convert_strip_html(string $text): string {1469
$text = html_entity_decode($text, ENT_QUOTES, 'UTF-8');1470
$text = preg_replace('~<\s*br\s*/?\s*>~i', "\n", $text);1471
$text = preg_replace('~</\s*(h[1-6]|div|li|tr|td|th)\s*>~i', "\n", $text);1472
$text = preg_replace('~</\s*p\s*>~i', "\n\n", $text);1473
$text = trim(strip_tags((string)$text));1474
return preg_replace("/[ \t]*\n[ \t]*/", "\n", $text);1475
}1476
1477
function acp_convert_news_text(string $text): string {1478
// MyAAC and Gesior content is often HTML. ZnoteX stores BBCode/plain text.1479
return acp_convert_strip_html($text);1480
}1481
1482
function acp_convert_myaac_detect(): array {1483
return [1484
'accounts' => acp_convert_table_exists('accounts'),1485
'players' => acp_convert_table_exists('players'),1486
'news' => acp_convert_table_exists('myaac_news'),1487
'changelog' => acp_convert_table_exists('myaac_changelog'),1488
'boards' => acp_convert_table_exists('myaac_forum_boards'),1489
'forum' => acp_convert_table_exists('myaac_forum'),1490
'pages' => acp_convert_table_exists('myaac_pages'),1491
'gallery' => acp_convert_table_exists('myaac_gallery'),1492
'menu' => acp_convert_table_exists('myaac_menu'),1493
'config' => acp_convert_table_exists('myaac_config') || acp_convert_table_exists('myaac_settings'),1494
];1495
}1496
1497
function acp_convert_gesior_detect(): array {1498
return [1499
'accounts' => acp_convert_table_exists('accounts'),1500
'players' => acp_convert_table_exists('players'),1501
'news_big' => acp_convert_table_exists('z_news_big'),1502
'tickers' => acp_convert_table_exists('z_news_tickers'),1503
'boards' => acp_convert_table_exists('z_forum_boards'),1504
'forum' => acp_convert_table_exists('z_forum'),1505
'pages' => acp_convert_table_exists('z_pages'),1506
'config' => acp_convert_table_exists('z_config'),1507
];1508
}1509
1510
function acp_convert_compatibility(): array {1511
$report = ['accounts' => 0, 'players' => 0];1512
1513
if (acp_convert_table_exists('accounts') && acp_convert_table_exists('znote_accounts')) {1514
$before = acp_convert_count('znote_accounts');1515
db()->execute("1516
INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)1517
SELECT `a`.`id`, 0, UNIX_TIMESTAMP(CURDATE()), ''1518
FROM `accounts` AS `a`1519
LEFT JOIN `znote_accounts` AS `z` ON `z`.`account_id` = `a`.`id`1520
WHERE `z`.`id` IS NULL;1521
");1522
$report['accounts'] = max(0, acp_convert_count('znote_accounts') - $before);1523
}1524
1525
if (acp_convert_table_exists('players') && acp_convert_table_exists('znote_players')) {1526
$before = acp_convert_count('znote_players');1527
db()->execute("1528
INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)1529
SELECT `p`.`id`, UNIX_TIMESTAMP(CURDATE()), 0, ''1530
FROM `players` AS `p`1531
LEFT JOIN `znote_players` AS `z` ON `z`.`player_id` = `p`.`id`1532
WHERE `z`.`id` IS NULL;1533
");1534
$report['players'] = max(0, acp_convert_count('znote_players') - $before);1535
}1536
1537
return $report;1538
}1539
1540
function acp_convert_empty_report(): array {1541
return [1542
'accounts' => 0,1543
'players' => 0,1544
'news' => 0,1545
'changelog' => 0,1546
'boards' => 0,1547
'threads' => 0,1548
'posts' => 0,1549
'pages' => 0,1550
'gallery' => 0,1551
'menu' => 0,1552
'config' => 0,1553
'archived' => 0,1554
'warnings' => [],1555
];1556
}1557
1558
function acp_convert_prepare_report(string $source, bool $dryRun, array $report): array {1559
if (!$dryRun) {1560
acp_convert_ensure_tables();1561
$report = array_merge($report, acp_convert_compatibility());1562
$report['archived'] = acp_convert_archive_legacy($source);1563
} else {1564
foreach (acp_convert_legacy_tables($source) as $table) {1565
$report['archived'] += acp_convert_count($table);1566
}1567
}1568
1569
return $report;1570
}1571
1572
function acp_convert_report_empty(array $report, array $keys): bool {1573
foreach ($keys as $key) {1574
if (!empty($report[$key])) {1575
return false;1576
}1577
}1578
1579
return true;1580
}1581
1582
function acp_convert_myaac_news(bool $dryRun, array &$report): void {1583
if (acp_convert_table_exists('myaac_news') && acp_convert_table_exists('znote_news')) {1584
if ($dryRun) {1585
$report['news'] = acp_convert_count('myaac_news', '`hide` = 0 AND `type` IN (1, 3)');1586
return;1587
}1588
1589
$rows = db()->fetchAll("SELECT * FROM `myaac_news` WHERE `hide` = 0 AND `type` IN (1, 3) ORDER BY `id` ASC;") ?: [];1590
foreach ($rows as $row) {1591
$title = trim((string)$row['title']);1592
$date = (int)$row['date'];1593
$pid = (int)$row['player_id'];1594
$exists = db()->fetchOne("1595
SELECT `id` FROM `znote_news`1596
WHERE `title` = ?1597
AND `date` = ?1598
LIMIT 1;1599
", [substr($title, 0, 30), $date]);1600
if ($exists !== false) {1601
continue;1602
}1603
if (acp_convert_insert('znote_news', [1604
'title' => substr($title !== '' ? $title : 'Imported news', 0, 30),1605
'text' => acp_convert_news_text((string)$row['body']),1606
'date' => $date > 0 ? $date : time(),1607
'pid' => $pid,1608
])) {1609
$report['news']++;1610
}1611
}1612
}1613
}1614
1615
function acp_convert_myaac_changelog(bool $dryRun, array &$report): void {1616
if (acp_convert_table_exists('myaac_changelog') && acp_convert_table_exists('znote_changelog')) {1617
if ($dryRun) {1618
$report['changelog'] = acp_convert_count('myaac_changelog', '`hide` = 0');1619
return;1620
}1621
1622
$rows = db()->fetchAll("SELECT * FROM `myaac_changelog` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];1623
foreach ($rows as $row) {1624
$text = substr(acp_convert_strip_html((string)$row['body']), 0, 254);1625
$date = (int)$row['date'];1626
$exists = db()->fetchOne("1627
SELECT `id` FROM `znote_changelog`1628
WHERE `text` = ?1629
AND `time` = ?1630
LIMIT 1;1631
", [$text, $date]);1632
if ($text === '' || $exists !== false) {1633
continue;1634
}1635
if (acp_convert_insert('znote_changelog', [1636
'text' => $text,1637
'time' => $date > 0 ? $date : time(),1638
'report_id' => 0,1639
'status' => (int)$row['type'],1640
])) {1641
$report['changelog']++;1642
}1643
}1644
}1645
}1646
1647
function acp_convert_myaac_pages(bool $dryRun, array &$report): void {1648
if (acp_convert_table_exists('myaac_pages') && acp_convert_table_exists('znote_pages')) {1649
if ($dryRun) {1650
$report['pages'] = acp_convert_count('myaac_pages', '`hide` = 0');1651
return;1652
}1653
1654
$rows = db()->fetchAll("SELECT * FROM `myaac_pages` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];1655
foreach ($rows as $row) {1656
$mapped = acp_convert_map_get('myaac', 'myaac_pages', $row['id'], 'znote_pages');1657
if ($mapped > 0) {1658
continue;1659
}1660
$slug = acp_convert_slug((string)$row['name'], 'myaac-page-' . (int)$row['id']);1661
$title = substr(trim((string)$row['title']), 0, 100);1662
$exists = db()->fetchOne('SELECT `id` FROM `znote_pages` WHERE `slug` = ? LIMIT 1;', [$slug]);1663
if ($exists !== false) {1664
acp_convert_map_set('myaac', 'myaac_pages', $row['id'], 'znote_pages', (int)$exists['id']);1665
continue;1666
}1667
if (acp_convert_insert('znote_pages', [1668
'slug' => $slug,1669
'title' => $title !== '' ? $title : $slug,1670
'body' => acp_convert_news_text((string)$row['body']),1671
'created' => (int)$row['date'] > 0 ? (int)$row['date'] : time(),1672
'updated' => (int)$row['date'] > 0 ? (int)$row['date'] : time(),1673
'player_id' => (int)$row['player_id'],1674
'access' => (int)$row['access'],1675
'active' => 1,1676
])) {1677
$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');1678
acp_convert_map_set('myaac', 'myaac_pages', $row['id'], 'znote_pages', $newId);1679
$report['pages']++;1680
}1681
}1682
}1683
}1684
1685
function acp_convert_myaac_gallery(bool $dryRun, array &$report): void {1686
if (acp_convert_table_exists('myaac_gallery') && acp_convert_table_exists('znote_images')) {1687
if ($dryRun) {1688
$report['gallery'] = acp_convert_count('myaac_gallery', '`hide` = 0');1689
return;1690
}1691
1692
$rows = db()->fetchAll("SELECT * FROM `myaac_gallery` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];1693
foreach ($rows as $row) {1694
$mapped = acp_convert_map_get('myaac', 'myaac_gallery', $row['id'], 'znote_images');1695
if ($mapped > 0) {1696
continue;1697
}1698
$image = trim((string)$row['image']);1699
if ($image === '') {1700
continue;1701
}1702
$title = substr(trim((string)($row['comment'] ?: 'Imported image')), 0, 30);1703
$exists = db()->fetchOne('SELECT `id` FROM `znote_images` WHERE `image` = ? LIMIT 1;', [$image]);1704
if ($exists !== false) {1705
acp_convert_map_set('myaac', 'myaac_gallery', $row['id'], 'znote_images', (int)$exists['id']);1706
continue;1707
}1708
if (acp_convert_insert('znote_images', [1709
'title' => $title,1710
'desc' => (string)$row['comment'],1711
'date' => time(),1712
'status' => 2,1713
'image' => substr($image, 0, 255),1714
'delhash' => '',1715
'account_id' => 0,1716
])) {1717
$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');1718
acp_convert_map_set('myaac', 'myaac_gallery', $row['id'], 'znote_images', $newId);1719
$report['gallery']++;1720
}1721
}1722
}1723
}1724
1725
function acp_convert_myaac_menu(bool $dryRun, array &$report): void {1726
if (acp_convert_table_exists('myaac_menu') && acp_convert_table_exists('znote_menu')) {1727
if ($dryRun) {1728
$report['menu'] = acp_convert_count('myaac_menu', '`enabled` = 1');1729
return;1730
}1731
1732
$rows = db()->fetchAll("SELECT * FROM `myaac_menu` WHERE `enabled` = 1 ORDER BY `ordering` ASC, `id` ASC;") ?: [];1733
foreach ($rows as $row) {1734
$mapped = acp_convert_map_get('myaac', 'myaac_menu', $row['id'], 'znote_menu');1735
if ($mapped > 0) {1736
continue;1737
}1738
$label = substr(trim((string)$row['name']), 0, 64);1739
$url = substr(trim((string)$row['link']), 0, 255);1740
if ($label === '' || $url === '') {1741
continue;1742
}1743
$parentId = acp_convert_menu_parent_id((int)($row['category'] ?? 0));1744
if ($parentId <= 0) {1745
continue;1746
}1747
$exists = db()->fetchOne("1748
SELECT `id` FROM `znote_menu`1749
WHERE `location` = 'main'1750
AND `label` = ?1751
AND `url` = ?1752
LIMIT 1;1753
", [$label, $url]);1754
if ($exists !== false) {1755
acp_convert_map_set('myaac', 'myaac_menu', $row['id'], 'znote_menu', (int)$exists['id']);1756
continue;1757
}1758
if (acp_convert_insert('znote_menu', [1759
'location' => 'main',1760
'parent_id' => $parentId,1761
'label' => $label,1762
'url' => $url,1763
'icon' => '',1764
'target' => !empty($row['blank']) ? '_blank' : '',1765
'visibility' => 'all',1766
'sort_order' => (int)$row['ordering'],1767
'active' => 1,1768
])) {1769
$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');1770
acp_convert_map_set('myaac', 'myaac_menu', $row['id'], 'znote_menu', $newId);1771
$report['menu']++;1772
}1773
}1774
}1775
}1776
1777
function acp_convert_myaac_config(bool $dryRun, array &$report): void {1778
if (acp_convert_table_exists('znote_config')) {1779
if ($dryRun) {1780
$report['config'] =1781
acp_convert_count('myaac_config') +1782
acp_convert_count('myaac_settings');1783
return;1784
}1785
1786
if (acp_convert_table_exists('myaac_config')) {1787
$rows = db()->fetchAll("SELECT * FROM `myaac_config` ORDER BY `id` ASC;") ?: [];1788
foreach ($rows as $row) {1789
if (acp_convert_config_set('legacy:myaac:' . (string)$row['name'], (string)$row['value'])) {1790
$report['config']++;1791
}1792
}1793
}1794
if (acp_convert_table_exists('myaac_settings')) {1795
$rows = db()->fetchAll("SELECT * FROM `myaac_settings` ORDER BY `id` ASC;") ?: [];1796
foreach ($rows as $row) {1797
if (acp_convert_config_set('legacy:myaac_setting:' . (string)$row['key'], (string)$row['value'])) {1798
$report['config']++;1799
}1800
}1801
}1802
}1803
}1804
1805
function acp_convert_myaac_forum_boards(bool $dryRun, array &$report): array {1806
$boardMap = [];1807
if (acp_convert_table_exists('myaac_forum_boards') && acp_convert_table_exists('znote_forum')) {1808
$rows = db()->fetchAll("SELECT * FROM `myaac_forum_boards` ORDER BY `id` ASC;") ?: [];1809
if ($dryRun) {1810
$report['boards'] = count($rows);1811
} else {1812
foreach ($rows as $row) {1813
$name = substr(trim((string)$row['name']), 0, 50);1814
$existing = db()->fetchOne("1815
SELECT `id` FROM `znote_forum`1816
WHERE `name` = ?1817
LIMIT 1;1818
", [$name]);1819
if ($existing !== false) {1820
$boardMap[(int)$row['id']] = (int)$existing['id'];1821
continue;1822
}1823
if (acp_convert_insert('znote_forum', [1824
'name' => $name !== '' ? $name : 'Imported board',1825
'access' => max(1, (int)$row['access']),1826
'closed' => (int)$row['closed'],1827
'hidden' => (int)$row['hide'],1828
'guild_id' => (int)$row['guild'],1829
])) {1830
$boardMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');1831
$report['boards']++;1832
}1833
}1834
}1835
}1836
1837
return $boardMap;1838
}1839
1840
function acp_convert_myaac_forum_threads(array $boardMap, array &$report): array {1841
$threadMap = [];1842
$threads = db()->fetchAll("SELECT * FROM `myaac_forum` WHERE `id` = `first_post` ORDER BY `id` ASC;") ?: [];1843
foreach ($threads as $row) {1844
$oldBoard = (int)$row['section'];1845
$boardId = $boardMap[$oldBoard] ?? $oldBoard;1846
$title = substr(trim((string)$row['post_topic']), 0, 50);1847
$created = (int)$row['post_date'];1848
$playerId = (int)$row['author_guid'];1849
$existing = db()->fetchOne("1850
SELECT `id` FROM `znote_forum_threads`1851
WHERE `forum_id` = ?1852
AND `player_id` = ?1853
AND `created` = ?1854
AND `title` = ?1855
LIMIT 1;1856
", [$boardId, $playerId, $created, $title]);1857
if ($existing !== false) {1858
$threadMap[(int)$row['id']] = (int)$existing['id'];1859
continue;1860
}1861
if (acp_convert_insert('znote_forum_threads', [1862
'forum_id' => $boardId,1863
'player_id' => $playerId,1864
'player_name' => acp_convert_player_name($playerId),1865
'title' => $title !== '' ? $title : 'Imported thread',1866
'text' => sanitize((string)$row['post_text']),1867
'created' => $created > 0 ? $created : time(),1868
'updated' => (int)$row['edit_date'] > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),1869
'sticky' => (int)$row['sticked'],1870
'hidden' => 0,1871
'closed' => (int)$row['closed'],1872
])) {1873
$threadMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');1874
$report['threads']++;1875
}1876
}1877
1878
return $threadMap;1879
}1880
1881
function acp_convert_myaac_forum_posts(array $threadMap, array &$report): void {1882
$posts = db()->fetchAll("SELECT * FROM `myaac_forum` WHERE `id` <> `first_post` ORDER BY `id` ASC;") ?: [];1883
foreach ($posts as $row) {1884
$oldThread = (int)$row['first_post'];1885
$threadId = $threadMap[$oldThread] ?? 0;1886
if ($threadId <= 0) {1887
continue;1888
}1889
$created = (int)$row['post_date'];1890
$playerId = (int)$row['author_guid'];1891
$exists = db()->fetchOne("1892
SELECT `id` FROM `znote_forum_posts`1893
WHERE `thread_id` = ?1894
AND `player_id` = ?1895
AND `created` = ?1896
LIMIT 1;1897
", [$threadId, $playerId, $created]);1898
if ($exists !== false) {1899
continue;1900
}1901
if (acp_convert_insert('znote_forum_posts', [1902
'thread_id' => $threadId,1903
'player_id' => $playerId,1904
'player_name' => acp_convert_player_name($playerId),1905
'text' => sanitize((string)$row['post_text']),1906
'created' => $created > 0 ? $created : time(),1907
'updated' => (int)$row['edit_date'] > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),1908
])) {1909
$report['posts']++;1910
}1911
}1912
}1913
1914
function acp_convert_myaac_forum(bool $dryRun, array $boardMap, array &$report): void {1915
if (acp_convert_table_exists('myaac_forum') && acp_convert_table_exists('znote_forum_threads') && acp_convert_table_exists('znote_forum_posts')) {1916
if ($dryRun) {1917
$report['threads'] = acp_count("SELECT COUNT(*) AS `c` FROM `myaac_forum` WHERE `id` = `first_post`;");1918
$report['posts'] = acp_count("SELECT COUNT(*) AS `c` FROM `myaac_forum` WHERE `id` <> `first_post`;");1919
return;1920
}1921
1922
$threadMap = acp_convert_myaac_forum_threads($boardMap, $report);1923
acp_convert_myaac_forum_posts($threadMap, $report);1924
}1925
}1926
1927
function acp_convert_myaac_run(bool $dryRun = true): array {1928
$report = acp_convert_prepare_report('myaac', $dryRun, acp_convert_empty_report());1929
1930
acp_convert_myaac_news($dryRun, $report);1931
acp_convert_myaac_changelog($dryRun, $report);1932
acp_convert_myaac_pages($dryRun, $report);1933
acp_convert_myaac_gallery($dryRun, $report);1934
acp_convert_myaac_menu($dryRun, $report);1935
acp_convert_myaac_config($dryRun, $report);1936
$boardMap = acp_convert_myaac_forum_boards($dryRun, $report);1937
acp_convert_myaac_forum($dryRun, $boardMap, $report);1938
1939
if ($dryRun && acp_convert_report_empty($report, ['news', 'changelog', 'boards', 'threads', 'posts', 'pages', 'gallery', 'menu', 'config'])) {1940
$report['warnings'][] = 'No MyAAC content tables were found in this database.';1941
}1942
1943
return $report;1944
}1945
1946
function acp_convert_gesior_news(bool $dryRun, array &$report): void {1947
if (acp_convert_table_exists('z_news_big') && acp_convert_table_exists('znote_news')) {1948
$hideCol = acp_convert_column_exists('z_news_big', 'hide_news') ? 'hide_news' : 'hide';1949
if ($dryRun) {1950
$report['news'] += acp_convert_count('z_news_big', '`' . $hideCol . '` = 0');1951
return;1952
}1953
1954
$rows = db()->fetchAll('SELECT * FROM `z_news_big` WHERE `' . acp_convert_identifier($hideCol) . '` = 0 ORDER BY `date` ASC;') ?: [];1955
foreach ($rows as $row) {1956
$title = substr(trim((string)($row['topic'] ?? 'Imported news')), 0, 30);1957
$date = (int)($row['date'] ?? 0);1958
$pid = (int)($row['author_id'] ?? 0);1959
$exists = db()->fetchOne("1960
SELECT `id` FROM `znote_news`1961
WHERE `title` = ?1962
AND `date` = ?1963
LIMIT 1;1964
", [$title, $date]);1965
if ($exists !== false) {1966
continue;1967
}1968
if (acp_convert_insert('znote_news', [1969
'title' => $title,1970
'text' => acp_convert_news_text((string)($row['text'] ?? '')),1971
'date' => $date > 0 ? $date : time(),1972
'pid' => $pid,1973
])) {1974
$report['news']++;1975
}1976
}1977
}1978
}1979
1980
function acp_convert_gesior_changelog(bool $dryRun, array &$report): void {1981
if (acp_convert_table_exists('z_news_tickers') && acp_convert_table_exists('znote_changelog')) {1982
$hideWhere = acp_convert_column_exists('z_news_tickers', 'hide_ticker') ? '`hide_ticker` = 0' : '1=1';1983
$textCol = acp_convert_column_exists('z_news_tickers', 'text') ? 'text' : 'body';1984
if ($dryRun) {1985
$report['changelog'] += acp_convert_count('z_news_tickers', $hideWhere);1986
return;1987
}1988
1989
$rows = db()->fetchAll("SELECT * FROM `z_news_tickers` WHERE {$hideWhere} ORDER BY `date` ASC;") ?: [];1990
foreach ($rows as $row) {1991
$text = substr(acp_convert_strip_html((string)($row[$textCol] ?? '')), 0, 254);1992
$date = (int)($row['date'] ?? 0);1993
if ($text === '') {1994
continue;1995
}1996
$exists = db()->fetchOne("1997
SELECT `id` FROM `znote_changelog`1998
WHERE `text` = ?1999
AND `time` = ?2000
LIMIT 1;2001
", [$text, $date]);2002
if ($exists !== false) {2003
continue;2004
}2005
if (acp_convert_insert('znote_changelog', [2006
'text' => $text,2007
'time' => $date > 0 ? $date : time(),2008
'report_id' => 0,2009
'status' => 0,2010
])) {2011
$report['changelog']++;2012
}2013
}2014
}2015
}2016
2017
function acp_convert_gesior_config(bool $dryRun, array &$report): void {2018
if (acp_convert_table_exists('z_config') && acp_convert_table_exists('znote_config')) {2019
if ($dryRun) {2020
$report['config'] = acp_convert_count('z_config');2021
return;2022
}2023
2024
$rows = db()->fetchAll("SELECT * FROM `z_config`;") ?: [];2025
foreach ($rows as $row) {2026
$name = (string)($row['key'] ?? ($row['name'] ?? ($row['config'] ?? '')));2027
$value = (string)($row['value'] ?? '');2028
if ($name !== '' && acp_convert_config_set('legacy:gesior:' . $name, $value)) {2029
$report['config']++;2030
}2031
}2032
}2033
}2034
2035
function acp_convert_gesior_forum_threads(string $sourceForum, array &$report): array {2036
$threadMap = [];2037
$threads = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($sourceForum) . '` WHERE `id` = `first_post` ORDER BY `id` ASC;') ?: [];2038
foreach ($threads as $row) {2039
$boardId = max(1, (int)($row['section'] ?? 0));2040
$title = substr(trim((string)($row['post_topic'] ?? 'Imported thread')), 0, 50);2041
$created = (int)($row['post_date'] ?? 0);2042
$playerId = (int)($row['author_guid'] ?? 0);2043
$existing = db()->fetchOne("2044
SELECT `id` FROM `znote_forum_threads`2045
WHERE `forum_id` = ?2046
AND `player_id` = ?2047
AND `created` = ?2048
AND `title` = ?2049
LIMIT 1;2050
", [$boardId, $playerId, $created, $title]);2051
if ($existing !== false) {2052
$threadMap[(int)$row['id']] = (int)$existing['id'];2053
continue;2054
}2055
if (acp_convert_insert('znote_forum_threads', [2056
'forum_id' => $boardId,2057
'player_id' => $playerId,2058
'player_name' => acp_convert_player_name($playerId),2059
'title' => $title,2060
'text' => sanitize((string)($row['post_text'] ?? '')),2061
'created' => $created > 0 ? $created : time(),2062
'updated' => (int)($row['edit_date'] ?? 0) > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),2063
'sticky' => (int)($row['sticked'] ?? 0),2064
'hidden' => 0,2065
'closed' => (int)($row['closed'] ?? 0),2066
])) {2067
$threadMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');2068
$report['threads']++;2069
}2070
}2071
2072
return $threadMap;2073
}2074
2075
function acp_convert_gesior_forum_posts(string $sourceForum, array $threadMap, array &$report): void {2076
$posts = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($sourceForum) . '` WHERE `id` <> `first_post` ORDER BY `id` ASC;') ?: [];2077
foreach ($posts as $row) {2078
$threadId = $threadMap[(int)$row['first_post']] ?? 0;2079
if ($threadId <= 0) {2080
continue;2081
}2082
$created = (int)($row['post_date'] ?? 0);2083
$playerId = (int)($row['author_guid'] ?? 0);2084
$exists = db()->fetchOne("2085
SELECT `id` FROM `znote_forum_posts`2086
WHERE `thread_id` = ?2087
AND `player_id` = ?2088
AND `created` = ?2089
LIMIT 1;2090
", [$threadId, $playerId, $created]);2091
if ($exists !== false) {2092
continue;2093
}2094
if (acp_convert_insert('znote_forum_posts', [2095
'thread_id' => $threadId,2096
'player_id' => $playerId,2097
'player_name' => acp_convert_player_name($playerId),2098
'text' => sanitize((string)($row['post_text'] ?? '')),2099
'created' => $created > 0 ? $created : time(),2100
'updated' => (int)($row['edit_date'] ?? 0) > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),2101
])) {2102
$report['posts']++;2103
}2104
}2105
}2106
2107
function acp_convert_gesior_forum(bool $dryRun, array &$report): void {2108
$sourceForum = acp_convert_table_exists('z_forum') ? 'z_forum' : '';2109
if ($sourceForum === '' || !acp_convert_table_exists('znote_forum_threads') || !acp_convert_table_exists('znote_forum_posts')) {2110
return;2111
}2112
2113
if ($dryRun) {2114
$report['threads'] = acp_count("SELECT COUNT(*) AS `c` FROM `{$sourceForum}` WHERE `id` = `first_post`;");2115
$report['posts'] = acp_count("SELECT COUNT(*) AS `c` FROM `{$sourceForum}` WHERE `id` <> `first_post`;");2116
return;2117
}2118
2119
$threadMap = acp_convert_gesior_forum_threads($sourceForum, $report);2120
acp_convert_gesior_forum_posts($sourceForum, $threadMap, $report);2121
}2122
2123
function acp_convert_gesior_run(bool $dryRun = true): array {2124
$report = acp_convert_prepare_report('gesior', $dryRun, acp_convert_empty_report());2125
2126
acp_convert_gesior_news($dryRun, $report);2127
acp_convert_gesior_changelog($dryRun, $report);2128
acp_convert_gesior_config($dryRun, $report);2129
acp_convert_gesior_forum($dryRun, $report);2130
2131
if ($dryRun && acp_convert_report_empty($report, ['news', 'changelog', 'threads', 'posts', 'config'])) {2132
$report['warnings'][] = 'No Gesior2012 content tables were found in this database.';2133
}2134
2135
return $report;2136
}2137
2138
function acp_convert_refresh_cache(): void {2139
if (acp_convert_table_exists('znote_news')) {2140
$cache = new Cache('engine/cache/news');2141
$cache->setContent(fetchAllNews() ?: []);2142
$cache->save();2143
}2144
2145
if (acp_convert_table_exists('znote_changelog')) {2146
$cache = new Cache('engine/cache/changelog');2147
$cache->useMemory(false);2148
$cache->setContent(db()->fetchAll("2149
SELECT `id`, `text`, `time`, `report_id`, `status`2150
FROM `znote_changelog`2151
ORDER BY `id` DESC;2152
") ?: []);2153
$cache->save();2154
}2155
}2156
2157
function acp_convert_sql_preview(string $statement): string {2158
$statement = preg_replace('/\s+/', ' ', trim($statement));2159
if ($statement === null) {2160
return '';2161
}2162
return strlen($statement) > 500 ? substr($statement, 0, 500) . '...' : $statement;2163
}2164
2165
function acp_convert_run_sql_script(string $sql): array {2166
global $aacQueries, $accQueriesData;2167
$connect = db()->connection();2168
2169
$statements = acp_convert_dump_split($sql);2170
$report = [2171
'ok' => false,2172
'executed' => 0,2173
'total' => count($statements),2174
'errors' => [],2175
'rolled_back' => false,2176
'started' => date('Y-m-d H:i:s'),2177
'finished' => '',2178
];2179
2180
if (!$statements) {2181
$report['errors'][] = [2182
'statement' => 0,2183
'message' => 'No SQL statements were found in the uploaded file.',2184
'sql' => '',2185
];2186
$report['finished'] = date('Y-m-d H:i:s');2187
return $report;2188
}2189
2190
$inTransaction = false;2191
foreach ($statements as $index => $statement) {2192
$statement = trim($statement);2193
if ($statement === '') {2194
continue;2195
}2196
2197
try {2198
$aacQueries++;2199
$accQueriesData[] = "[" . elapsedTime() . "] " . $statement;2200
$result = mysqli_query($connect, $statement);2201
if ($result instanceof mysqli_result) {2202
mysqli_free_result($result);2203
}2204
$report['executed']++;2205
2206
if (preg_match('/^START\s+TRANSACTION\b/i', $statement)) {2207
$inTransaction = true;2208
} elseif (preg_match('/^(COMMIT|ROLLBACK)\b/i', $statement)) {2209
$inTransaction = false;2210
}2211
} catch (mysqli_sql_exception $e) {2212
$report['errors'][] = [2213
'statement' => $index + 1,2214
'message' => $e->getMessage(),2215
'sql' => acp_convert_sql_preview($statement),2216
];2217
2218
if ($inTransaction) {2219
try {2220
mysqli_query($connect, 'ROLLBACK');2221
$report['rolled_back'] = true;2222
} catch (mysqli_sql_exception $rollbackError) {2223
$report['errors'][] = [2224
'statement' => 0,2225
'message' => 'Rollback failed: ' . $rollbackError->getMessage(),2226
'sql' => 'ROLLBACK',2227
];2228
}2229
}2230
break;2231
}2232
}2233
2234
$report['ok'] = empty($report['errors']);2235
$report['finished'] = date('Y-m-d H:i:s');2236
return $report;2237
}2238
2239
function acp_convert_remap_report(string $sql): array {2240
if (!preg_match('/^-- Source detected:\s*([a-z0-9_-]+)/mi', $sql, $match)) {2241
return ['accounts' => 0, 'players' => 0];2242
}2243
2244
$source = (string)$match[1];2245
$rows = db()->fetchAll("2246
SELECT `source_table`, COUNT(*) AS `total`2247
FROM `znote_convert_map`2248
WHERE `source` = ?2249
AND `target_table` IN ('accounts', 'players')2250
AND CAST(`source_id` AS UNSIGNED) <> `target_id`2251
GROUP BY `source_table`;2252
", [$source]) ?: [];2253
$report = ['accounts' => 0, 'players' => 0];2254
foreach ($rows as $row) {2255
$table = (string)($row['source_table'] ?? '');2256
if (isset($report[$table])) {2257
$report[$table] = (int)$row['total'];2258
}2259
}2260
return $report;2261
}2262
2263
function acp_convert_current_account_snapshot(): array {2264
global $session_user_id;2265
2266
$accountId = (int)($session_user_id ?? 0);2267
if ($accountId <= 0 || !acp_convert_table_exists('accounts')) {2268
return [];2269
}2270
2271
$row = db()->fetchOne('SELECT * FROM `accounts` WHERE `id` = ? LIMIT 1;', [$accountId]);2272
return is_array($row) ? $row : [];2273
}2274
2275
function acp_convert_restore_account_snapshot(array $snapshot): bool {2276
if (!$snapshot || empty($snapshot['id']) || !acp_convert_table_exists('accounts')) {2277
return false;2278
}2279
2280
$columns = db()->fetchAll("SHOW COLUMNS FROM `accounts`;") ?: [];2281
$available = [];2282
foreach ($columns as $column) {2283
if (!empty($column['Field'])) {2284
$available[(string)$column['Field']] = true;2285
}2286
}2287
2288
$sets = [];2289
$params = [];2290
foreach ($snapshot as $column => $value) {2291
if ($column === 'id' || empty($available[$column])) {2292
continue;2293
}2294
$sets[] = '`' . acp_convert_identifier((string)$column) . '` = ?';2295
$params[] = $value;2296
}2297
2298
if (!$sets) {2299
return false;2300
}2301
2302
$params[] = (int)$snapshot['id'];2303
2304
return db()->execute("2305
UPDATE `accounts`2306
SET " . implode(', ', $sets) . "2307
WHERE `id` = ?2308
LIMIT 1;2309
", $params);2310
}2311
2312
function acp_convert_sql_script_header(string $source): array {2313
$now = date('Y-m-d H:i:s');2314
return [2315
"-- ZnoteX {$source} conversion SQL",2316
"-- Generated {$now}",2317
"-- Import this into a database that already contains the legacy {$source} tables and the ZnoteX schema.",2318
"START TRANSACTION;",2319
"",2320
file_get_contents('SQL/migrations/2.0.0_pages_and_convert_map.sql') ?: '',2321
];2322
}2323
2324
function acp_convert_sql_script_compatibility(): array {2325
return [2326
"",2327
"-- Accounts and players compatibility rows",2328
"INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)2329
SELECT `a`.`id`, 0, UNIX_TIMESTAMP(CURDATE()), ''2330
FROM `accounts` AS `a`2331
LEFT JOIN `znote_accounts` AS `z` ON `z`.`account_id` = `a`.`id`2332
WHERE `z`.`id` IS NULL;",2333
"INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)2334
SELECT `p`.`id`, UNIX_TIMESTAMP(CURDATE()), 0, ''2335
FROM `players` AS `p`2336
LEFT JOIN `znote_players` AS `z` ON `z`.`player_id` = `p`.`id`2337
WHERE `z`.`id` IS NULL;",2338
];2339
}2340
2341
function acp_convert_sql_script_myaac_news(): array {2342
if (!acp_convert_table_exists('myaac_news')) {2343
return [];2344
}2345
2346
return [2347
"",2348
"-- MyAAC news",2349
"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)2350
SELECT LEFT(`m`.`title`, 30), `m`.`body`, IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()), `m`.`player_id`2351
FROM `myaac_news` AS `m`2352
LEFT JOIN `znote_news` AS `z` ON `z`.`title` = LEFT(`m`.`title`, 30) AND `z`.`date` = `m`.`date`2353
WHERE `m`.`hide` = 0 AND `m`.`type` IN (1, 3) AND `z`.`id` IS NULL;",2354
];2355
}2356
2357
function acp_convert_sql_script_myaac_changelog(): array {2358
if (!acp_convert_table_exists('myaac_changelog')) {2359
return [];2360
}2361
2362
return [2363
"",2364
"-- MyAAC changelog",2365
"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)2366
SELECT LEFT(`m`.`body`, 254), IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()), 0, `m`.`type`2367
FROM `myaac_changelog` AS `m`2368
LEFT JOIN `znote_changelog` AS `z` ON `z`.`text` = LEFT(`m`.`body`, 254) AND `z`.`time` = `m`.`date`2369
WHERE `m`.`hide` = 0 AND `m`.`body` <> '' AND `z`.`id` IS NULL;",2370
];2371
}2372
2373
function acp_convert_sql_script_myaac_pages(): array {2374
if (!acp_convert_table_exists('myaac_pages')) {2375
return [];2376
}2377
2378
return [2379
"",2380
"-- MyAAC custom pages",2381
"INSERT INTO `znote_pages` (`slug`, `title`, `body`, `created`, `updated`, `player_id`, `access`, `active`)2382
SELECT2383
LEFT(LOWER(REPLACE(REPLACE(`m`.`name`, ' ', '-'), '/', '-')), 64),2384
LEFT(`m`.`title`, 100),2385
`m`.`body`,2386
IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()),2387
IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()),2388
`m`.`player_id`,2389
`m`.`access`,2390
12391
FROM `myaac_pages` AS `m`2392
LEFT JOIN `znote_pages` AS `z` ON `z`.`slug` = LEFT(LOWER(REPLACE(REPLACE(`m`.`name`, ' ', '-'), '/', '-')), 64)2393
WHERE `m`.`hide` = 0 AND `z`.`id` IS NULL;",2394
];2395
}2396
2397
function acp_convert_sql_script_myaac_gallery(): array {2398
if (!acp_convert_table_exists('myaac_gallery')) {2399
return [];2400
}2401
2402
return [2403
"",2404
"-- MyAAC gallery",2405
"INSERT INTO `znote_images` (`title`, `desc`, `date`, `status`, `image`, `delhash`, `account_id`)2406
SELECT LEFT(IF(`m`.`comment` <> '', `m`.`comment`, 'Imported image'), 30), `m`.`comment`, UNIX_TIMESTAMP(), 2, LEFT(`m`.`image`, 255), '', 02407
FROM `myaac_gallery` AS `m`2408
LEFT JOIN `znote_images` AS `z` ON `z`.`image` = LEFT(`m`.`image`, 255)2409
WHERE `m`.`hide` = 0 AND `m`.`image` <> '' AND `z`.`id` IS NULL;",2410
];2411
}2412
2413
function acp_convert_sql_script_myaac_menu(): array {2414
if (!acp_convert_table_exists('myaac_menu')) {2415
return [];2416
}2417
2418
return [2419
"",2420
"-- MyAAC menu",2421
"INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `target`, `visibility`, `sort_order`, `active`)2422
SELECT 'main', `p`.`id`, LEFT(`m`.`name`, 64), LEFT(`m`.`link`, 255), '', IF(`m`.`blank` = 1, '_blank', ''), 'all', `m`.`ordering`, 12423
FROM `myaac_menu` AS `m`2424
INNER JOIN `znote_menu` AS `p`2425
ON `p`.`location` = 'main'2426
AND `p`.`parent_id` = 02427
AND `p`.`label` = CASE `m`.`category`2428
WHEN 1 THEN 'Home'2429
WHEN 2 THEN 'Account'2430
WHEN 3 THEN 'Community'2431
WHEN 4 THEN 'Community'2432
WHEN 5 THEN 'Library'2433
WHEN 6 THEN 'Shop'2434
ELSE ''2435
END2436
LEFT JOIN `znote_menu` AS `z` ON `z`.`location` = 'main' AND `z`.`label` = LEFT(`m`.`name`, 64) AND `z`.`url` = LEFT(`m`.`link`, 255)2437
WHERE `m`.`enabled` = 1 AND `m`.`name` <> '' AND `m`.`link` <> '' AND `z`.`id` IS NULL;",2438
];2439
}2440
2441
function acp_convert_sql_script_myaac_config(): array {2442
if (!acp_convert_table_exists('myaac_config')) {2443
return [];2444
}2445
2446
return [2447
"",2448
"-- MyAAC config preserved as legacy keys",2449
"INSERT INTO `znote_config` (`key`, `value`)2450
SELECT LEFT(CONCAT('legacy:myaac:', `name`), 64), `value`2451
FROM `myaac_config`2452
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);",2453
];2454
}2455
2456
function acp_convert_sql_script_myaac_settings(): array {2457
if (!acp_convert_table_exists('myaac_settings')) {2458
return [];2459
}2460
2461
return [2462
"INSERT INTO `znote_config` (`key`, `value`)2463
SELECT LEFT(CONCAT('legacy:myaac_setting:', `key`), 64), `value`2464
FROM `myaac_settings`2465
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);",2466
];2467
}2468
2469
function acp_convert_sql_script_myaac_sections(): array {2470
$sql = [];2471
foreach ([2472
acp_convert_sql_script_myaac_news(),2473
acp_convert_sql_script_myaac_changelog(),2474
acp_convert_sql_script_myaac_pages(),2475
acp_convert_sql_script_myaac_gallery(),2476
acp_convert_sql_script_myaac_menu(),2477
acp_convert_sql_script_myaac_config(),2478
acp_convert_sql_script_myaac_settings(),2479
] as $section) {2480
array_push($sql, ...$section);2481
}2482
2483
return $sql;2484
}2485
2486
function acp_convert_sql_script_gesior_news(): array {2487
if (!acp_convert_table_exists('z_news_big')) {2488
return [];2489
}2490
2491
$hideCol = acp_convert_column_exists('z_news_big', 'hide_news') ? 'hide_news' : 'hide';2492
return [2493
"",2494
"-- Gesior big news",2495
"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)2496
SELECT LEFT(`g`.`topic`, 30), `g`.`text`, IF(`g`.`date` > 0, `g`.`date`, UNIX_TIMESTAMP()), IFNULL(`g`.`author_id`, 0)2497
FROM `z_news_big` AS `g`2498
LEFT JOIN `znote_news` AS `z` ON `z`.`title` = LEFT(`g`.`topic`, 30) AND `z`.`date` = `g`.`date`2499
WHERE `g`.`{$hideCol}` = 0 AND `z`.`id` IS NULL;",2500
];2501
}2502
2503
function acp_convert_sql_script_gesior_tickers(): array {2504
if (!acp_convert_table_exists('z_news_tickers')) {2505
return [];2506
}2507
2508
$hideWhere = acp_convert_column_exists('z_news_tickers', 'hide_ticker') ? "`g`.`hide_ticker` = 0" : '1=1';2509
$textCol = acp_convert_column_exists('z_news_tickers', 'text') ? 'text' : 'body';2510
return [2511
"",2512
"-- Gesior tickers as Znote changelog entries",2513
"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)2514
SELECT LEFT(`g`.`{$textCol}`, 254), IF(`g`.`date` > 0, `g`.`date`, UNIX_TIMESTAMP()), 0, 02515
FROM `z_news_tickers` AS `g`2516
LEFT JOIN `znote_changelog` AS `z` ON `z`.`text` = LEFT(`g`.`{$textCol}`, 254) AND `z`.`time` = `g`.`date`2517
WHERE {$hideWhere} AND `g`.`{$textCol}` <> '' AND `z`.`id` IS NULL;",2518
];2519
}2520
2521
function acp_convert_sql_script_gesior_sections(): array {2522
$sql = [];2523
foreach ([2524
acp_convert_sql_script_gesior_news(),2525
acp_convert_sql_script_gesior_tickers(),2526
] as $section) {2527
array_push($sql, ...$section);2528
}2529
2530
return $sql;2531
}2532
2533
function acp_convert_sql_script_archive_section(string $source): array {2534
$archive = acp_convert_legacy_archive_sql($source);2535
if ($archive !== '') {2536
return [2537
"",2538
"-- Lossless legacy archive for custom tables and columns",2539
$archive,2540
];2541
}2542
2543
return [];2544
}2545
2546
function acp_convert_sql_script(string $source): string {2547
$sql = [];2548
foreach ([2549
acp_convert_sql_script_header($source),2550
acp_convert_sql_script_compatibility(),2551
$source === 'myaac' ? acp_convert_sql_script_myaac_sections() : [],2552
$source === 'gesior' ? acp_convert_sql_script_gesior_sections() : [],2553
acp_convert_sql_script_archive_section($source),2554
["", "COMMIT;", "-- Rebuild the ZnoteX news/changelog cache from the admin panel after importing."],2555
] as $section) {2556
array_push($sql, ...$section);2557
}2558
2559
return trim(implode("\n\n", $sql)) . "\n";2560
}2561
2562
if ($_SERVER['REQUEST_METHOD'] === 'POST') {2563
$source = (string)($_POST['source'] ?? '');2564
$action = (string)($_POST['action'] ?? 'upload_download');2565
2566
if ($action === 'upload_download') {2567
if (!in_array($source, ['auto', 'myaac', 'gesior'], true)) {2568
acp_flash_error(t('acp.conv.err_choose_source'));2569
acp_redirect('convert');2570
}2571
2572
if (!isset($_FILES['sql_file']) || !is_uploaded_file($_FILES['sql_file']['tmp_name'])) {2573
acp_flash_error(t('acp.conv.err_no_dump'));2574
acp_redirect('convert');2575
}2576
2577
$size = (int)($_FILES['sql_file']['size'] ?? 0);2578
if ($size <= 0 || $size > 256 * 1024 * 1024) {2579
acp_flash_error(t('acp.conv.err_dump_size'));2580
acp_redirect('convert');2581
}2582
2583
$dump = (string)file_get_contents($_FILES['sql_file']['tmp_name']);2584
$model = acp_convert_dump_model($dump);2585
$converted = acp_convert_dump_script($model, $source);2586
$detected = acp_convert_dump_source($model, $source);2587
2588
header('Content-Type: application/sql; charset=UTF-8');2589
header('Content-Disposition: attachment; filename="znote_' . $detected . '_converted.sql"');2590
header('Content-Length: ' . strlen($converted));2591
echo $converted;2592
exit;2593
}2594
2595
if ($action === 'import_converted') {2596
if (!isset($_FILES['converted_sql_file']) || !is_uploaded_file($_FILES['converted_sql_file']['tmp_name'])) {2597
acp_flash_error(t('acp.conv.err_no_converted_file'));2598
acp_redirect('convert');2599
}2600
2601
$size = (int)($_FILES['converted_sql_file']['size'] ?? 0);2602
if ($size <= 0 || $size > 256 * 1024 * 1024) {2603
acp_flash_error(t('acp.conv.err_converted_size'));2604
acp_redirect('convert');2605
}2606
2607
$sql = (string)file_get_contents($_FILES['converted_sql_file']['tmp_name']);2608
if (strpos($sql, '-- ZnoteX uploaded SQL conversion') === false || strpos($sql, 'znote_legacy_tables') === false) {2609
acp_flash_error(t('acp.conv.err_not_generated'));2610
acp_redirect('convert');2611
}2612
2613
$currentAccount = acp_convert_current_account_snapshot();2614
acp_convert_ensure_tables();2615
$report = acp_convert_run_sql_script($sql);2616
$report['protected_account'] = acp_convert_restore_account_snapshot($currentAccount);2617
$report['remapped'] = acp_convert_remap_report($sql);2618
$report['file'] = (string)($_FILES['converted_sql_file']['name'] ?? 'converted.sql');2619
$report['size'] = $size;2620
$_SESSION['acp_convert_import_report'] = $report;2621
2622
if ($report['ok']) {2623
acp_convert_refresh_cache();2624
acp_log('convert.import', $report['file'], ['statements_executed' => (int)$report['executed']]);2625
acp_flash_success(t('acp.conv.imported_success', ['n' => (int)$report['executed']]));2626
} else {2627
$error = $report['errors'][0] ?? ['statement' => 0, 'message' => t('acp.conv.unknown_sql_error')];2628
acp_flash_error(t('acp.conv.import_stopped', ['n' => (int)$error['statement'], 'message' => h((string)$error['message'])]));2629
}2630
acp_redirect('convert');2631
}2632
2633
acp_flash_error(t('acp.conv.err_unknown_action'));2634
acp_redirect('convert');2635
}2636
2637
$importReport = $_SESSION['acp_convert_import_report'] ?? null;2638
unset($_SESSION['acp_convert_import_report']);2639
?>2640
2641
<style>2642
.acp-convert-progress { display: none; margin: 0 0 16px; }2643
.acp-convert-progress.is-active { display: block; }2644
.acp-convert-bar { height: 8px; overflow: hidden; border-radius: 4px; background: var(--acp-panel-2); }2645
.acp-convert-bar span { display: block; width: 35%; height: 100%; background: var(--acp-blue-600); animation: acpConvertMove 1.1s ease-in-out infinite; }2646
.acp-convert-terminal { margin-top: 10px; padding: 10px 12px; border-radius: var(--acp-radius); background: #101820; color: #c9f7d1; font-family: Consolas, Monaco, monospace; font-size: 12.5px; line-height: 1.45; }2647
.acp-convert-terminal div { white-space: pre-wrap; }2648
.acp-convert-terminal strong { color: #fff; }2649
.acp-convert-terminal--error { color: #ffd1d1; }2650
@keyframes acpConvertMove {2651
0% { transform: translateX(-110%); }2652
100% { transform: translateX(310%); }2653
}2654
</style>2655
2656
<div class="acp-convert-progress" id="convertProgress">2657
<div class="acp-convert-bar"><span></span></div>2658
<div class="acp-convert-terminal" id="convertTerminal">2659
<div>$ waiting for action...</div>2660
</div>2661
</div>2662
2663
<div class="acp-flash acp-flash--info">2664
<i class="fa fa-info-circle"></i>2665
<span><?= h(t('acp.conv.info_banner')) ?></span>2666
</div>2667
2668
<section class="acp-card">2669
<header class="acp-card-head">2670
<h2><?= h(t('acp.conv.convert_title')) ?></h2>2671
<p><?= h(t('acp.conv.convert_sub')) ?></p>2672
</header>2673
<div class="acp-card-body">2674
<form method="post" enctype="multipart/form-data" data-convert-form>2675
<?= acp_csrf_field() ?>2676
<input type="hidden" name="action" value="upload_download">2677
2678
<div class="acp-field">2679
<label class="acp-label" for="source"><?= h(t('acp.conv.source_label')) ?></label>2680
<select class="acp-select" id="source" name="source">2681
<option value="auto"><?= h(t('acp.conv.source_auto')) ?></option>2682
<option value="myaac"><?= h(t('acp.conv.source_myaac')) ?></option>2683
<option value="gesior"><?= h(t('acp.conv.source_gesior')) ?></option>2684
</select>2685
<p class="acp-hint"><?= t('acp.conv.source_hint', ['tag1' => '<code>myaac_news</code>', 'tag2' => '<code>z_news_big</code>']) ?></p>2686
</div>2687
2688
<div class="acp-field">2689
<label class="acp-label" for="sql_file"><?= h(t('acp.conv.sql_dump_label')) ?></label>2690
<input class="acp-input" id="sql_file" name="sql_file" type="file" accept=".sql,text/sql,text/plain" required>2691
<p class="acp-hint"><?= h(t('acp.conv.sql_dump_hint')) ?></p>2692
</div>2693
2694
<div class="acp-actions">2695
<button class="acp-btn acp-btn--blue" type="submit">2696
<i class="fa fa-download"></i> <?= h(t('acp.conv.convert_btn')) ?>2697
</button>2698
</div>2699
</form>2700
</div>2701
</section>2702
2703
<section class="acp-card">2704
<header class="acp-card-head">2705
<h2><?= h(t('acp.conv.import_title')) ?></h2>2706
<p><?= h(t('acp.conv.import_sub')) ?></p>2707
</header>2708
<div class="acp-card-body">2709
<form method="post" enctype="multipart/form-data" data-convert-form>2710
<?= acp_csrf_field() ?>2711
<input type="hidden" name="action" value="import_converted">2712
2713
<div class="acp-field">2714
<label class="acp-label" for="converted_sql_file"><?= h(t('acp.conv.converted_sql_label')) ?></label>2715
<input class="acp-input" id="converted_sql_file" name="converted_sql_file" type="file" accept=".sql,text/sql,text/plain" required>2716
<p class="acp-hint"><?= h(t('acp.conv.converted_sql_hint')) ?></p>2717
</div>2718
2719
<div class="acp-actions">2720
<button class="acp-btn acp-btn--green" type="submit">2721
<i class="fa fa-upload"></i> <?= h(t('acp.conv.import_btn')) ?>2722
</button>2723
</div>2724
</form>2725
2726
<?php if (is_array($importReport)): ?>2727
<div class="acp-convert-terminal<?= empty($importReport['errors']) ? '' : ' acp-convert-terminal--error' ?>">2728
<div><strong>$ <?= h(t('acp.conv.report_title')) ?></strong></div>2729
<div>$ <?= h(t('acp.conv.report_file')) ?>: <?= h((string)($importReport['file'] ?? 'converted.sql')) ?> (<?= number_format((int)($importReport['size'] ?? 0)) ?> bytes)</div>2730
<div>$ <?= h(t('acp.conv.report_started')) ?>: <?= h((string)($importReport['started'] ?? '')) ?></div>2731
<div>$ <?= h(t('acp.conv.report_finished')) ?>: <?= h((string)($importReport['finished'] ?? '')) ?></div>2732
<div>$ <?= h(t('acp.conv.report_statements')) ?>: <?= (int)($importReport['executed'] ?? 0) ?> / <?= (int)($importReport['total'] ?? 0) ?></div>2733
<div>$ <?= h(t('acp.conv.report_status')) ?>: <?= !empty($importReport['ok']) ? h(t('acp.conv.status_ok')) : h(t('acp.conv.status_error')) ?></div>2734
<div>$ <?= h(t('acp.conv.report_protected')) ?>: <?= !empty($importReport['protected_account']) ? h(t('acp.conv.yes')) : h(t('acp.conv.no')) ?></div>2735
<div>$ <?= h(t('acp.conv.report_remapped_accounts')) ?>: <?= (int)($importReport['remapped']['accounts'] ?? 0) ?></div>2736
<div>$ <?= h(t('acp.conv.report_remapped_players')) ?>: <?= (int)($importReport['remapped']['players'] ?? 0) ?></div>2737
<?php if (!empty($importReport['rolled_back'])): ?>2738
<div>$ <?= h(t('acp.conv.report_rollback')) ?></div>2739
<?php endif; ?>2740
<?php foreach (($importReport['errors'] ?? []) as $error): ?>2741
<div>$ <?= h(t('acp.conv.report_error_statement', ['n' => (int)($error['statement'] ?? 0), 'message' => (string)($error['message'] ?? t('acp.conv.unknown_sql_error'))])) ?></div>2742
<?php if (!empty($error['sql'])): ?>2743
<div>$ <?= h(t('acp.conv.report_sql')) ?>: <?= h((string)$error['sql']) ?></div>2744
<?php endif; ?>2745
<?php endforeach; ?>2746
</div>2747
<?php endif; ?>2748
</div>2749
</section>2750
2751
<section class="acp-card">2752
<header class="acp-card-head">2753
<h2><?= h(t('acp.conv.what_gets_converted_title')) ?></h2>2754
</header>2755
<div class="acp-card-body">2756
<p><?= h(t('acp.conv.info_p1')) ?></p>2757
<p><?= t('acp.conv.info_p2', [2758
'myaac_news' => '<code>myaac_news</code>',2759
'myaac_changelog' => '<code>myaac_changelog</code>',2760
'myaac_forum_boards' => '<code>myaac_forum_boards</code>',2761
'myaac_forum' => '<code>myaac_forum</code>',2762
'myaac_pages' => '<code>myaac_pages</code>',2763
'myaac_gallery' => '<code>myaac_gallery</code>',2764
'myaac_menu' => '<code>myaac_menu</code>',2765
'z_news_big' => '<code>z_news_big</code>',2766
'z_news_tickers' => '<code>z_news_tickers</code>',2767
'z_forum' => '<code>z_forum</code>',2768
'legacy' => '<code>legacy:*</code>',2769
'znote_config' => '<code>znote_config</code>',2770
'znote_legacy_tables' => '<code>znote_legacy_tables</code>',2771
'znote_legacy_rows' => '<code>znote_legacy_rows</code>',2772
]) ?></p>2773
</div>2774
</section>2775
2776
<script>2777
(function () {2778
var progress = document.getElementById('convertProgress');2779
var terminal = document.getElementById('convertTerminal');2780
if (!progress || !terminal) return;2781
2782
function line(text) {2783
var div = document.createElement('div');2784
div.textContent = text;2785
terminal.appendChild(div);2786
}2787
2788
document.addEventListener('submit', function (event) {2789
var form = event.target;2790
if (!form || !form.hasAttribute('data-convert-form')) return;2791
2792
var action = form.querySelector('input[name="action"]');2793
var source = form.querySelector('select[name="source"]');2794
var file = form.querySelector('input[type="file"]');2795
var actionValue = action ? action.value : 'convert';2796
progress.classList.add('is-active');2797
terminal.innerHTML = '';2798
line('$ file: ' + (file && file.files && file.files[0] ? file.files[0].name : 'uploaded sql'));2799
line('$ source: ' + (source ? source.value : 'converted znote sql'));2800
line('$ action: ' + actionValue);2801
if (actionValue === 'import_converted') {2802
line('$ executing converted SQL...');2803
line('$ errors will be printed here after redirect');2804
} else {2805
line('$ checking tables...');2806
line('$ preserving custom legacy rows...');2807
line('$ browser will receive the converted SQL file when finished');2808
}2809
}, true);2810
})();2811
</script>2812