# 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:
```{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
;
```

The command

    show tables;

should show the following remaining tables:
```
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` WHERE user_id != 0;
```


Drop entries from Winterrodeln related tables:

```{sql}
DELETE FROM wrreport;
DELETE FROM wrsledruncache;
DELETE FROM wrinncache;
DELETE FROM wrregioncache;
DELETE FROM wrregion;
```

Insert previously deleted values:
```
INSERT INTO `user` VALUES (2,'RedirectBot','','','','20080925192203','','20120902200108','ea19a572ecaf2a6d5934b153e52c3ab9','20120902200103',NULL,'20120902200103','20080925192203',1,NULL);
INSERT INTO `user` VALUES (504,'Johndoe','John Doe',':pbkdf2:sha512:30000:64:hvqT9kNNazXmOigsCc4ewQ==:hytd5I3h38y7jVmqXrv6IKD1zzJYp8ZrUyodInP49VbtKWfQAOYvhmbVFCdl7JK/suPmFLKbe309/jlta2Gk4A==','',NULL,'john.doe@example.com','20191129090658','f0a018e0cf3384411373d83b212128f0',NULL,'bdb23486b4efcab13063fabacc109bae','20191206090650','20191129090650',0,NULL);
```


Export data to SQL:

    mysqldump philipp_winterrodeln_wiki --skip-extended-insert --skip-add-locks > testdb.sql