HeiseForenWiki:Datenrekonstruktion
Durch den Verlust von Verwaltungsinformationen bei der HeiseForenWiki:Downtime war die Kombination eines Backups des Wiki in einer 1.3er Version mit Daten aus der Version 1.4.5 erforderlich.
Ausserdem sollten die Spameinträge automatisch beseitigt werden.
Dazu wurde ein SQL-Script erstellt, dass diese Schritte ausführt, auch um die Änderungen nachvollziehbar zu machen.
Zuerst wurde festgelegt, dass alle Einträge ab dem 26.9.05 23:00:00 zu verwerfen waren. Es mag sein, dass einzelne Änderungen nach diesem Datum nicht von Bots oder Spammern stammen und eigentlich gutgemeint waren; es wurde aber darauf verzichtet, das genauer aufzudröseln.
Aus dieser Festlegung lässt sich daher die Liste der Spam-Einträge wie folgt erstellen:
create table spamed_pages as select old_title as title, old_namespace as ns,
count(old_title) as n_attacks,cur_id as spamed_id
from old join cur on old_title = cur_title
where cur_namespace = old_namespace and old_timestamp > 20050926230000 group by old_title, old_namespace;
Das ergab folgende Liste:
+------------------------------------+----+-----------+-----------+ | title | ns | n_attacks | spamed_id | +------------------------------------+----+-----------+-----------+ | Anmeldung | 4 | 79 | 700 | | Editierhilfe | 4 | 30 | 656 | | Erste_Schritte | 4 | 37 | 659 | | GNU_Freie_Dokumentationslizenz | 0 | 45 | 694 | | Gut | 0 | 20 | 1401 | | Hauptseite | 1 | 51 | 941 | | Hilfe | 4 | 42 | 657 | | Hommingberger_Gepardenforelle | 0 | 56 | 1381 | | Inhalt:Allgemein_Forenspezifisches | 0 | 20 | 696 | | Inhalt:Allgemeines | 0 | 21 | 695 | | Inhalt:Heise-Forenspezifisches | 0 | 21 | 697 | | Inhalt:Technisches | 0 | 17 | 698 | | Jm | 2 | 23 | 1023 | | Lizenzbestimmungen | 4 | 20 | 1688 | | Mediawiki_145 | 4 | 16 | 1648 | | Newbie | 0 | 39 | 623 | | Wikipedia | 0 | 44 | 699 | | Wunschliste | 4 | 22 | 690 | | Über_HeiseForenWiki | 5 | 34 | 691 | +------------------------------------+----+-----------+-----------+
jetzt braucht man noch die id desjenigen Eintrags aus der Tabelle old, der den letzten gültigen Eintrag vor der Spamattacke hat:
create table to_replace as select max(old_timestamp) as good_time, old_title as title,
ns, n_attacks, spamed_id
from old join spamed_pages on old_title = title
where old_namespace = ns and old_timestamp < 20050926230000
group by old_title;
leider lässt sich so noch nicht die old_id des letzten guten Eintrags aus der old herausfinden, die wird jetzt noch zuerst als Spalte (lg_id) hinzugefügt und dann anhand der bereits bekannten 'good_time' eingetragen:
alter table to_replace add lg_id integer; update to_replace set lg_id = (select old_id from old where old_timestamp = good_time and old_title = title and old_namespace = ns);
sodass sich insgesamt diese Liste ergibt:
+----------------+------------------------------------+----+-----------+-----------+-------+ | good_time | title | ns | n_attacks | spamed_id | lg_id | +----------------+------------------------------------+----+-----------+-----------+-------+ | 20040311184023 | Anmeldung | 4 | 79 | 700 | 6741 | | 20050416182413 | Editierhilfe | 4 | 30 | 656 | 6740 | | 20041219104846 | Erste_Schritte | 4 | 37 | 659 | 6732 | | 20050624000344 | GNU_Freie_Dokumentationslizenz | 0 | 45 | 694 | 6731 | | 20050701163645 | Gut | 0 | 20 | 1401 | 6762 | | 20050401162822 | Hauptseite | 1 | 51 | 941 | 6739 | | 20040409055542 | Hilfe | 4 | 42 | 657 | 6735 | | 20050701091623 | Hommingberger_Gepardenforelle | 0 | 56 | 1381 | 6734 | | 20050825093256 | Inhalt:Allgemein_Forenspezifisches | 0 | 20 | 696 | 6759 | | 20040921202718 | Inhalt:Allgemeines | 0 | 21 | 695 | 6756 | | 20050831144546 | Inhalt:Heise-Forenspezifisches | 0 | 21 | 697 | 6730 | | 20040531214350 | Inhalt:Technisches | 0 | 17 | 698 | 6749 | | 20050719121959 | Jm | 2 | 23 | 1023 | 6745 | | 20050826160145 | Lizenzbestimmungen | 4 | 20 | 1688 | 6755 | | 20050705214127 | Mediawiki_145 | 4 | 16 | 1648 | 6747 | | 20040813120217 | Newbie | 0 | 39 | 623 | 6733 | | 20050624000100 | Wikipedia | 0 | 44 | 699 | 6746 | | 20050826234332 | Wunschliste | 4 | 22 | 690 | 6750 | | 20040608234835 | Über_HeiseForenWiki | 5 | 34 | 691 | 6737 | +----------------+------------------------------------+----+-----------+-----------+-------+
jetzt ist das gröbste geschafft - weil sich leider updates mit mehreren Feldern direkt in SQL eigentlich nicht vernünftig erstellen lassen - in PostGres ist das über eine eingebaute Scriptsprache einfacher - wird zunächst eine Tabelle erzeugt, die die beabsichtigten Änderungen schon enthält:
create table newcur as select cur_id, cur_namespace, cur_title, old_text as cur_text,
old_comment as cur_comment, old_user as cur_user, old_user_text as cur_user_text,
old_timestamp as cur_timestamp, cur_restrictions, cur_counter, cur_is_redirect,
old_minor_edit as cur_minor_edit, cur_is_new, cur_random,
20051003120000 as cur_touched, 99999999999999 - old_timestamp as inverse_timestamp
from to_replace
join cur on spamed_id = cur_id
join old on lg_id = old_id;
Die Ausgabe dieser Tabelle sparen wir uns, weil es wortwörtlich den Rahmen sprengen würde ;).
Die geplanten Löschungen werden jetzt in der Tabelle cur2delete gesichert, und dann gleich auch durchgeführt:
create table cur2delete as select * from cur where cur_id in (select spamed_id from to_replace); delete from cur where cur_id in (select spamed_id from to_replace);
gleich gefolgt vom Insert aus der schon vorbereiteten newcur:
insert into cur select * from newcur;
jetzt werden noch die gespammten Einträge aus der old weggeschmissen, als Kriterium einfach der Zeitstempel:
delete from old where old_timestamp > 20050926230000;
was bleibt ist noch, die Einträge aus der old zu löschen, die jetzt bereits in der cur stehen:
delete from old where old_id in (select lg_id from to_replace);
Ohne diesen letzten Schritt würde die Liste der letzten Änderungen nicht richti funktionieren.
alles ganz einfach eigentlich...