$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")); } }