From 03798cba14d6d5690b2473b96291611855929902 Mon Sep 17 00:00:00 2001
From: Philipp Spitzer <philipp@spitzer.priv.at>
Date: Fri, 26 Jun 2020 18:14:56 +0200
Subject: [PATCH] Describe how to create the test database.

---
 tests/testdb.md | 185 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 185 insertions(+)
 create mode 100644 tests/testdb.md

diff --git a/tests/testdb.md b/tests/testdb.md
new file mode 100644
index 0000000..1a9da8d
--- /dev/null
+++ b/tests/testdb.md
@@ -0,0 +1,185 @@
+# How to create testdb.sql
+
+First import a "real" database snapshot in a temparary database on a non-production PC:
+
+    mysql philipp_winterrodeln_wiki < philipp_winterrodeln_wiki.sql
+
+
+Delete unnessary tables (execute it a few times until no more "Cannot delete or update a parent row: a foreign key constraint fails" errors occur):
+```{sql}
+DROP TABLE IF EXISTS
+wrintermapsreport,
+wrintermapsreporthistory,
+wrintermapssledrun,
+wrmappathcache,
+wrmappointcache,
+wrreportcache,
+
+account_credentials,
+account_requests,
+actor,
+archive,
+bot_passwords,
+category,
+change_tag,
+change_tag_def,
+comment,
+content,
+content_models,
+dpl_clview,
+external_user,
+externallinks,
+filearchive,
+image,
+image_comment_temp,
+imagelinks,
+interwiki,
+ip_changes,
+ipblocks,
+ipblocks_restrictions,
+iwlinks,
+job,
+l10n_cache,
+langlinks,
+log_search,
+logging,
+math,
+module_deps,
+objectcache,
+oldimage,
+page_props,
+page_restrictions,
+pagelinks,
+protected_titles,
+querycache,
+querycache_info,
+querycachetwo,
+recentchanges,
+redirect,
+revision_actor_temp,
+revision_comment_temp,
+searchindex,
+site_identifiers,
+site_stats,
+sites,
+slot_roles,
+slots,
+tag_summary,
+templatelinks,
+trackbacks,
+transcache,
+updatelog,
+uploadstash,
+user_former_groups,
+user_groups,
+user_newtalk,
+user_openid,
+user_properties,
+valid_tag,
+watchlist
+;
+```
+
+Should remain:
+```
+categorylinks
+page
+revision
+text
+user
+wrinncache
+wrregion
+wrregioncache
+wrreport
+wrsledruncache
+```
+
+Drop most of the rows
+```{sql}
+DELETE FROM page WHERE page_id NOT IN (
+5,
+228,
+237,
+251,
+283,
+355,
+356,
+357,
+358,
+359,
+360,
+361,
+362,
+363,
+364,
+365,
+395,
+396,
+397,
+761,
+793,
+833,
+834,
+835,
+836,
+837,
+838,
+839,
+879,
+880,
+881,
+882,
+926,
+1353,
+1354,
+1417,
+1804
+);
+```
+
+Only keep relevant data in table `categorylinks`:
+
+```{sql}
+DELETE FROM categorylinks WHERE cl_from NOT IN (SELECT page_id FROM page);
+DELETE FROM categorylinks WHERE cl_to NOT IN (
+'Gasthaus_(geschlossen)',
+'Gasthaus',
+'In_Arbeit',
+'Region',
+'Rodelbahn'
+);
+```
+
+Only keep relevant data in table `revision`:
+
+```{sql}
+DELETE FROM revision WHERE rev_page NOT IN (SELECT page_id FROM page);
+DELETE FROM revision WHERE rev_id NOT IN (select max(rev_id) from revision group by rev_page);
+```
+
+Only keep relevant data in table `text`:
+
+```{sql}
+DELETE FROM `text` WHERE old_id NOT IN (SELECT rev_text_id FROM revision);
+```
+
+Drop all entries from table `user`:
+```{sql}
+DELETE FROM `user`;
+```
+
+
+Drop entries from Winterrodeln related tables:
+
+```{sql}
+DELETE FROM wrreport;
+DELETE FROM wrsledruncache;
+DELETE FROM wrinncache;
+DELETE FROM wrregion;
+DELETE FROM wrregioncache;
+```
+
+
+Export data to SQL:
+
+    mysqldump philipp_winterrodeln_wiki --skip-extended-insert --skip-add-locks > philipp_winterrodeln_wiki.sql
\ No newline at end of file
-- 
2.39.5