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 (execute it a few times until no more "Cannot delete or update a parent row: a foreign key constraint fails" errors occur):
   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 Should remain:
  84 ```
  85 categorylinks
  86 page
  87 revision
  88 text
  89 user
  90 wrinncache
  91 wrregion
  92 wrregioncache
  93 wrreport
  94 wrsledruncache
  95 ```
  96
  97 Drop most of the rows
  98 ```{sql}
  99 DELETE FROM page WHERE page_id NOT IN (
 100 5,
 101 228,
 102 237,
 103 251,
 104 283,
 105 355,
 106 356,
 107 357,
 108 358,
 109 359,
 110 360,
 111 361,
 112 362,
 113 363,
 114 364,
 115 365,
 116 395,
 117 396,
 118 397,
 119 761,
 120 793,
 121 833,
 122 834,
 123 835,
 124 836,
 125 837,
 126 838,
 127 839,
 128 879,
 129 880,
 130 881,
 131 882,
 132 926,
 133 1353,
 134 1354,
 135 1417,
 136 1804
 137 );
 138 ```
 139
 140 Only keep relevant data in table `categorylinks`:
 141
 142 ```{sql}
 143 DELETE FROM categorylinks WHERE cl_from NOT IN (SELECT page_id FROM page);
 144 DELETE FROM categorylinks WHERE cl_to NOT IN (
 145 'Gasthaus_(geschlossen)',
 146 'Gasthaus',
 147 'In_Arbeit',
 148 'Region',
 149 'Rodelbahn'
 150 );
 151 ```
 152
 153 Only keep relevant data in table `revision`:
 154
 155 ```{sql}
 156 DELETE FROM revision WHERE rev_page NOT IN (SELECT page_id FROM page);
 157 DELETE FROM revision WHERE rev_id NOT IN (select max(rev_id) from revision group by rev_page);
 158 ```
 159
 160 Only keep relevant data in table `text`:
 161
 162 ```{sql}
 163 DELETE FROM `text` WHERE old_id NOT IN (SELECT rev_text_id FROM revision);
 164 ```
 165
 166 Drop all entries from table `user`:
 167 ```{sql}
 168 DELETE FROM `user`;
 169 ```
 170
 171
 172 Drop entries from Winterrodeln related tables:
 173
 174 ```{sql}
 175 DELETE FROM wrreport;
 176 DELETE FROM wrsledruncache;
 177 DELETE FROM wrinncache;
 178 DELETE FROM wrregion;
 179 DELETE FROM wrregioncache;
 180 ```
 181
 182
 183 Export data to SQL:
 184
 185     mysqldump philipp_winterrodeln_wiki --skip-extended-insert --skip-add-locks > philipp_winterrodeln_wiki.sql