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 wrreportcache,
  17
  18 account_credentials,
  19 account_requests,
  20 actor,
  21 archive,
  22 bot_passwords,
  23 category,
  24 change_tag,
  25 change_tag_def,
  26 comment,
  27 content,
  28 content_models,
  29 dpl_clview,
  30 external_user,
  31 externallinks,
  32 filearchive,
  33 image,
  34 image_comment_temp,
  35 imagelinks,
  36 interwiki,
  37 ip_changes,
  38 ipblocks,
  39 ipblocks_restrictions,
  40 iwlinks,
  41 job,
  42 l10n_cache,
  43 langlinks,
  44 log_search,
  45 logging,
  46 math,
  47 module_deps,
  48 objectcache,
  49 oldimage,
  50 page_props,
  51 page_restrictions,
  52 pagelinks,
  53 protected_titles,
  54 querycache,
  55 querycache_info,
  56 querycachetwo,
  57 recentchanges,
  58 redirect,
  59 revision_actor_temp,
  60 revision_comment_temp,
  61 searchindex,
  62 site_identifiers,
  63 site_stats,
  64 sites,
  65 slot_roles,
  66 slots,
  67 tag_summary,
  68 templatelinks,
  69 trackbacks,
  70 transcache,
  71 updatelog,
  72 uploadstash,
  73 user_former_groups,
  74 user_groups,
  75 user_newtalk,
  76 user_openid,
  77 user_properties,
  78 valid_tag,
  79 watchlist
  80 ;
  81 ```
  82
  83 The command
  84
  85     show tables;
  86
  87 should show the following remaining tables:
  88 ```
  89 categorylinks
  90 page
  91 revision
  92 text
  93 user
  94 wrinncache
  95 wrregion
  96 wrregioncache
  97 wrreport
  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 wrreport;
 180 DELETE FROM wrsledruncache;
 181 DELETE FROM wrinncache;
 182 DELETE FROM wrregioncache;
 183 DELETE FROM wrregion;
 184 ```
 185
 186 Insert previously deleted values:
 187 ```
 188 INSERT INTO `user` VALUES (2,'RedirectBot','','','','20080925192203','','20120902200108','ea19a572ecaf2a6d5934b153e52c3ab9','20120902200103',NULL,'20120902200103','20080925192203',1,NULL);
 189 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);
 190 ```
 191
 192
 193 Export data to SQL:
 194
 195     mysqldump philipp_winterrodeln_wiki --skip-extended-insert --skip-add-locks > testdb.sql