tests/testdb.md

   1 # How to create testdb.sql
   2
   3 First import a "real" database snapshot in a temparary database on a non-production PC:
   4
   5     mysql philipp_winterrodeln_wiki < philipp_winterrodeln_wiki.sql
   6
   7
   8 Delete unnessary tables:
   9 ```{sql}
  10 DROP TABLE IF EXISTS
  11 wrintermapsreport,
  12 wrintermapsreporthistory,
  13 wrintermapssledrun,
  14 wrmappathcache,
  15 wrmappointcache,
  16
  17 account_credentials,
  18 account_requests,
  19 actor,
  20 archive,
  21 bot_passwords,
  22 category,
  23 change_tag,
  24 change_tag_def,
  25 comment,
  26 content,
  27 content_models,
  28 dpl_clview,
  29 external_user,
  30 externallinks,
  31 filearchive,
  32 image,
  33 image_comment_temp,
  34 imagelinks,
  35 interwiki,
  36 ip_changes,
  37 ipblocks,
  38 ipblocks_restrictions,
  39 iwlinks,
  40 job,
  41 l10n_cache,
  42 langlinks,
  43 log_search,
  44 logging,
  45 math,
  46 module_deps,
  47 objectcache,
  48 oldimage,
  49 page_props,
  50 page_restrictions,
  51 pagelinks,
  52 protected_titles,
  53 querycache,
  54 querycache_info,
  55 querycachetwo,
  56 recentchanges,
  57 redirect,
  58 revision_actor_temp,
  59 revision_comment_temp,
  60 searchindex,
  61 site_identifiers,
  62 site_stats,
  63 sites,
  64 slot_roles,
  65 slots,
  66 tag_summary,
  67 templatelinks,
  68 trackbacks,
  69 transcache,
  70 updatelog,
  71 uploadstash,
  72 user_former_groups,
  73 user_groups,
  74 user_newtalk,
  75 user_openid,
  76 user_properties,
  77 valid_tag,
  78 watchlist
  79 ;
  80 ```
  81
  82 The command
  83
  84     show tables;
  85
  86 should show the following remaining tables:
  87 ```
  88 categorylinks
  89 page
  90 revision
  91 text
  92 user
  93 wrinncache
  94 wrregion
  95 wrregioncache
  96 wrreport
  97 wrreportcache,
  98 wrsledruncache
  99 ```
 100
 101 Drop most of the rows
 102 ```{sql}
 103 DELETE FROM page WHERE page_id NOT IN (
 104 5,
 105 228,
 106 237,
 107 251,
 108 283,
 109 355,
 110 356,
 111 357,
 112 358,
 113 359,
 114 360,
 115 361,
 116 362,
 117 363,
 118 364,
 119 365,
 120 395,
 121 396,
 122 397,
 123 761,
 124 793,
 125 833,
 126 834,
 127 835,
 128 836,
 129 837,
 130 838,
 131 839,
 132 879,
 133 880,
 134 881,
 135 882,
 136 926,
 137 1353,
 138 1354,
 139 1417,
 140 1804
 141 );
 142 ```
 143
 144 Only keep relevant data in table `categorylinks`:
 145
 146 ```{sql}
 147 DELETE FROM categorylinks WHERE cl_from NOT IN (SELECT page_id FROM page);
 148 DELETE FROM categorylinks WHERE cl_to NOT IN (
 149 'Gasthaus_(geschlossen)',
 150 'Gasthaus',
 151 'In_Arbeit',
 152 'Region',
 153 'Rodelbahn'
 154 );
 155 ```
 156
 157 Only keep relevant data in table `revision`:
 158
 159 ```{sql}
 160 DELETE FROM revision WHERE rev_page NOT IN (SELECT page_id FROM page);
 161 DELETE FROM revision WHERE rev_id NOT IN (select max(rev_id) from revision group by rev_page);
 162 ```
 163
 164 Only keep relevant data in table `text`:
 165
 166 ```{sql}
 167 DELETE FROM `text` WHERE old_id NOT IN (SELECT rev_text_id FROM revision);
 168 ```
 169
 170 Drop all entries from table `user`:
 171 ```{sql}
 172 DELETE FROM `user` WHERE user_id != 0;
 173 ```
 174
 175
 176 Drop entries from Winterrodeln related tables:
 177
 178 ```{sql}
 179 DELETE FROM wrreportcache;
 180 DELETE FROM wrreport;
 181 DELETE FROM wrsledruncache;
 182 DELETE FROM wrinncache;
 183 DELETE FROM wrregioncache;
 184 DELETE FROM wrregion;
 185 ```
 186
 187 Insert previously deleted values:
 188 ```
 189 INSERT INTO `user` VALUES (2,'RedirectBot','','','','20080925192203','','20120902200108','ea19a572ecaf2a6d5934b153e52c3ab9','20120902200103',NULL,'20120902200103','20080925192203',1,NULL);
 190 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);
 191 ```
 192
 193
 194 Export data to SQL:
 195
 196     mysqldump philipp_winterrodeln_wiki --skip-extended-insert --skip-add-locks > testdb.sql