$pdo->queryDirect("DROP TRIGGER IF EXISTS insert_hashes");
$pdo->queryDirect("DROP TRIGGER IF EXISTS update_hashes");
// Insert and update table
$sqlInsert = <<<SQL_INSERT
INSERT INTO {$table} (title, nfo, size, files, filename, nuked, nukereason, category, predate, SOURCE, requestid, group_id)
SELECT t.title, t.nfo, t.size, t.files, t.filename, t.nuked, t.nukereason, t.category, t.predate, t.source, t.requestid, IF(g.id IS NOT NULL, g.id, 0)
FROM predb_imports AS t
\tLEFT OUTER JOIN groups g ON t.groupname = g.name ON DUPLICATE KEY UPDATE predb.nfo = IF(predb.nfo IS NULL, t.nfo, predb.nfo),
\t predb.size = IF(predb.size IS NULL, t.size, predb.size),
\t predb.files = IF(predb.files IS NULL, t.files, predb.files),
\t predb.filename = IF(predb.filename = '', t.filename, predb.filename),
\t predb.nuked = IF(t.nuked > 0, t.nuked, predb.nuked),
\t predb.nukereason = IF(t.nuked > 0, t.nukereason, predb.nukereason),
\t predb.category = IF(predb.category IS NULL, t.category, predb.category),
\t predb.requestid = IF(predb.requestid = 0, t.requestid, predb.requestid),
\t predb.group_id = IF(g.id IS NOT NULL, g.id, 0);
SQL_INSERT;
echo $pdo->log->primary($sqlInsert);
$pdo->queryDirect($sqlInsert);
// Add hashes
echo $pdo->log->info("Adding predb_hashes entries");
$pdo->queryDirect("INSERT IGNORE INTO predb_hashes (pre_id, hashes) (SELECT id, CONCAT_WS(',', MD5(title), MD5(MD5(title)), SHA1(title)) FROM predb)");
// Re-add triggers on predb
echo $pdo->log->info("Adding predb_hashes triggers");
$pdo->exec("CREATE TRIGGER insert_hashes AFTER INSERT ON predb FOR EACH ROW BEGIN INSERT INTO predb_hashes (pre_id, hashes) VALUES (NEW.id, CONCAT_WS(',', MD5(NEW.title), MD5(MD5(NEW.title)), SHA1(NEW.title))); END;");
$pdo->exec("CREATE TRIGGER update_hashes AFTER UPDATE ON predb FOR EACH ROW BEGIN IF NEW.title != OLD.title THEN UPDATE predb_hashes SET hashes = CONCAT_WS(',', MD5(NEW.title), MD5(MD5(NEW.title)), SHA1(NEW.title)) WHERE pre_id = OLD.id; END IF; END;");
$pdo->queryDirect("TRUNCATE TABLE predb_imports");
} else {
exit($pdo->log->error("\nThis script can export or import a predb dump file. You may use the full path, or a relative path.\n" . "For importing, the script insert new rows and update existing matched rows. For databases not on the local system, use remote, else use local.\n" . "For exporting, the path must be writeable by mysql, any existing file[predb_dump.csv] will be\n\t\t\t\t\toverwritten.\n\n" . "php dump_predb.php export /path/to/write/to ...: To export.\n" . "php dump_predb.php [remote | local] /path/to/filename ...: To import.\n"));
}
}
