r45764 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r45763‎ | r45764 | r45765 >
Date:06:56, 15 January 2009
Author:tstarling
Status:resolved (Comments)
Tags:
Comment:
For backport to 1.14.

* Made the upgrader work with SQLite
* Implemented missing schema info functionality in DatabaseSqlite
* Merged the SQLite and MySQL schemas into the one file with the help of some replaceVars() hacks.
* Moved all primary key definitions to the field definition, moved all indexes to CREATE INDEX statements, for best SQLite compatibility.
* Made all autoincrement fields primary keys, as required by SQLite.
* Removed meaningless buzzword from the category table comment
* tables.sql: s/'0'/0/
* In SQLite the index names have DB scope. Renamed archive.usertext_timestamp, user_newtalk.user_id, user_newtalk.user_ip
* SQLite does not support UPDATE with LIMIT by default. Removed all instances I could find.
* Made query errors work in the installer.
* Fixed DatabaseSqlite::lastErrno(), made SQLITE_SCHEMA errors automatically reissue the query as suggested on sqlite-users. Otherwise upgrade breaks.
* Removed miscellaneous status information from getServerVersion(), that's not the place to put it
Modified paths:
  • /trunk/phase3/config/index.php (modified) (history)
  • /trunk/phase3/includes/Exception.php (modified) (history)
  • /trunk/phase3/includes/SiteStats.php (modified) (history)
  • /trunk/phase3/includes/db/Database.php (modified) (history)
  • /trunk/phase3/includes/db/DatabaseSqlite.php (modified) (history)
  • /trunk/phase3/maintenance/sqlite/README (added) (history)
  • /trunk/phase3/maintenance/sqlite/archives (added) (history)
  • /trunk/phase3/maintenance/sqlite/archives/initial-indexes.sql (added) (history)
  • /trunk/phase3/maintenance/sqlite/tables.sql (deleted) (history)
  • /trunk/phase3/maintenance/tables.sql (modified) (history)
  • /trunk/phase3/maintenance/updaters.inc (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/sqlite/tables.sql
——@@ -1,340 +0,0 @@
2 -CREATE TABLE /*$wgDBprefix*/user (
3 - user_id INTEGER PRIMARY KEY AUTOINCREMENT,
4 - user_name varchar(255) default '',
5 - user_real_name varchar(255) default '',
6 - user_password tinyblob ,
7 - user_newpassword tinyblob ,
8 - user_newpass_time BLOB,
9 - user_email tinytext ,
10 - user_options blob ,
11 - user_touched BLOB default '',
12 - user_token BLOB default '',
13 - user_email_authenticated BLOB,
14 - user_email_token BLOB,
15 - user_email_token_expires BLOB,
16 - user_registration BLOB,
17 - user_editcount int) /*$wgDBTableOptions*/;
18 -
19 -CREATE TABLE /*$wgDBprefix*/user_groups (
20 - ug_user INTEGER default '0',
21 - ug_group varBLOB default '') /*$wgDBTableOptions*/;
22 -
23 -CREATE TABLE /*$wgDBprefix*/user_newtalk (
24 - user_id INTEGER default '0',
25 - user_ip varBLOB default '',
26 - user_last_timestamp BLOB default '') /*$wgDBTableOptions*/;
27 -
28 -CREATE TABLE /*$wgDBprefix*/page (
29 - page_id INTEGER PRIMARY KEY AUTOINCREMENT,
30 - page_namespace INTEGER ,
31 - page_title varchar(255) ,
32 - page_restrictions tinyblob ,
33 - page_counter bigint default '0',
34 - page_is_redirect tinyint default '0',
35 - page_is_new tinyint default '0',
36 - page_random real ,
37 - page_touched BLOB default '',
38 - page_latest INTEGER ,
39 - page_len INTEGER ) /*$wgDBTableOptions*/;
40 -
41 -CREATE TABLE /*$wgDBprefix*/revision (
42 - rev_id INTEGER PRIMARY KEY AUTOINCREMENT,
43 - rev_page INTEGER ,
44 - rev_text_id INTEGER ,
45 - rev_comment tinyblob ,
46 - rev_user INTEGER default '0',
47 - rev_user_text varchar(255) default '',
48 - rev_timestamp BLOB default '',
49 - rev_minor_edit tinyint default '0',
50 - rev_deleted tinyint default '0',
51 - rev_len int,
52 - rev_parent_id INTEGER default NULL) /*$wgDBTableOptions*/ ;
53 -
54 -CREATE TABLE /*$wgDBprefix*/text (
55 - old_id INTEGER PRIMARY KEY AUTOINCREMENT,
56 - old_text mediumblob ,
57 - old_flags tinyblob ) /*$wgDBTableOptions*/ ;
58 -
59 -CREATE TABLE /*$wgDBprefix*/archive (
60 - ar_namespace INTEGER default '0',
61 - ar_title varchar(255) default '',
62 - ar_text mediumblob ,
63 - ar_comment tinyblob ,
64 - ar_user INTEGER default '0',
65 - ar_user_text varchar(255) ,
66 - ar_timestamp BLOB default '',
67 - ar_minor_edit tinyint default '0',
68 - ar_flags tinyblob ,
69 - ar_rev_id int,
70 - ar_text_id int,
71 - ar_deleted tinyint default '0',
72 - ar_len int,
73 - ar_page_id int,
74 - ar_parent_id INTEGER default NULL) /*$wgDBTableOptions*/;
75 -
76 -CREATE TABLE /*$wgDBprefix*/pagelinks (
77 - pl_from INTEGER default '0',
78 - pl_namespace INTEGER default '0',
79 - pl_title varchar(255) default '') /*$wgDBTableOptions*/;
80 -
81 -CREATE TABLE /*$wgDBprefix*/templatelinks (
82 - tl_from INTEGER default '0',
83 - tl_namespace INTEGER default '0',
84 - tl_title varchar(255) default '') /*$wgDBTableOptions*/;
85 -
86 -CREATE TABLE /*$wgDBprefix*/imagelinks (
87 - il_from INTEGER default '0',
88 - il_to varchar(255) default '') /*$wgDBTableOptions*/;
89 -
90 -CREATE TABLE /*$wgDBprefix*/categorylinks (
91 - cl_from INTEGER default '0',
92 - cl_to varchar(255) default '',
93 - cl_sortkey varchar(70) default '',
94 - cl_timestamp timestamp ) /*$wgDBTableOptions*/;
95 -
96 -CREATE TABLE /*$wgDBprefix*/category (
97 - cat_id INTEGER PRIMARY KEY AUTOINCREMENT,
98 - cat_title varchar(255) ,
99 - cat_pages INTEGER signed default 0,
100 - cat_subcats INTEGER signed default 0,
101 - cat_files INTEGER signed default 0,
102 - cat_hidden tinyint default 0) /*$wgDBTableOptions*/;
103 -
104 -CREATE TABLE /*$wgDBprefix*/externallinks (
105 - el_from INTEGER default '0',
106 - el_to blob ,
107 - el_index blob ) /*$wgDBTableOptions*/;
108 -
109 -CREATE TABLE /*$wgDBprefix*/langlinks (
110 - ll_from INTEGER default '0',
111 - ll_lang varBLOB default '',
112 - ll_title varchar(255) default '') /*$wgDBTableOptions*/;
113 -
114 -CREATE TABLE /*$wgDBprefix*/site_stats (
115 - ss_row_id INTEGER ,
116 - ss_total_views bigint default '0',
117 - ss_total_edits bigint default '0',
118 - ss_good_articles bigint default '0',
119 - ss_total_pages bigint default '-1',
120 - ss_users bigint default '-1',
121 - ss_admins INTEGER default '-1',
122 - ss_images INTEGER default '0') /*$wgDBTableOptions*/;
123 -
124 -CREATE TABLE /*$wgDBprefix*/hitcounter (
125 - hc_id INTEGER
126 -) ;
127 -
128 -CREATE TABLE /*$wgDBprefix*/ipblocks (
129 - ipb_id INTEGER PRIMARY KEY AUTOINCREMENT,
130 - ipb_address tinyblob ,
131 - ipb_user INTEGER default '0',
132 - ipb_by INTEGER default '0',
133 - ipb_by_text varchar(255) default '',
134 - ipb_reason tinyblob ,
135 - ipb_timestamp BLOB default '',
136 - ipb_auto bool default 0,
137 - ipb_anon_only bool default 0,
138 - ipb_create_account bool default 1,
139 - ipb_enable_autoblock bool default '1',
140 - ipb_expiry varBLOB default '',
141 - ipb_range_start tinyblob ,
142 - ipb_range_end tinyblob ,
143 - ipb_deleted bool default 0,
144 - ipb_block_email bool default 0) /*$wgDBTableOptions*/;
145 -
146 -CREATE TABLE /*$wgDBprefix*/image (
147 - img_name varchar(255) default '',
148 - img_size INTEGER default '0',
149 - img_width INTEGER default '0',
150 - img_height INTEGER default '0',
151 - img_metadata mediumblob ,
152 - img_bits INTEGER default '0',
153 - img_media_type TEXT default NULL,
154 - img_major_mime TEXT default "unknown",
155 - img_minor_mime varBLOB default "unknown",
156 - img_description tinyblob ,
157 - img_user INTEGER default '0',
158 - img_user_text varchar(255) ,
159 - img_timestamp varBLOB default '',
160 - img_sha1 varBLOB default '') /*$wgDBTableOptions*/;
161 -
162 -CREATE TABLE /*$wgDBprefix*/oldimage (
163 - oi_name varchar(255) default '',
164 - oi_archive_name varchar(255) default '',
165 - oi_size INTEGER default 0,
166 - oi_width INTEGER default 0,
167 - oi_height INTEGER default 0,
168 - oi_bits INTEGER default 0,
169 - oi_description tinyblob ,
170 - oi_user INTEGER default '0',
171 - oi_user_text varchar(255) ,
172 - oi_timestamp BLOB default '',
173 - oi_metadata mediumblob ,
174 - oi_media_type TEXT default NULL,
175 - oi_major_mime TEXT default "unknown",
176 - oi_minor_mime varBLOB default "unknown",
177 - oi_deleted tinyint default '0',
178 - oi_sha1 varBLOB default '') /*$wgDBTableOptions*/;
179 -
180 -CREATE TABLE /*$wgDBprefix*/filearchive (
181 - fa_id INTEGER PRIMARY KEY AUTOINCREMENT,
182 - fa_name varchar(255) default '',
183 - fa_archive_name varchar(255) default '',
184 - fa_storage_group varBLOB,
185 - fa_storage_key varBLOB default '',
186 - fa_deleted_user int,
187 - fa_deleted_timestamp BLOB default '',
188 - fa_deleted_reason text,
189 - fa_size INTEGER default '0',
190 - fa_width INTEGER default '0',
191 - fa_height INTEGER default '0',
192 - fa_metadata mediumblob,
193 - fa_bits INTEGER default '0',
194 - fa_media_type TEXT default NULL,
195 - fa_major_mime TEXT default "unknown",
196 - fa_minor_mime varBLOB default "unknown",
197 - fa_description tinyblob,
198 - fa_user INTEGER default '0',
199 - fa_user_text varchar(255) ,
200 - fa_timestamp BLOB default '',
201 - fa_deleted tinyint default '0') /*$wgDBTableOptions*/;
202 -
203 -CREATE TABLE /*$wgDBprefix*/recentchanges (
204 - rc_id INTEGER PRIMARY KEY AUTOINCREMENT,
205 - rc_timestamp varBLOB default '',
206 - rc_cur_time varBLOB default '',
207 - rc_user INTEGER default '0',
208 - rc_user_text varchar(255) ,
209 - rc_namespace INTEGER default '0',
210 - rc_title varchar(255) default '',
211 - rc_comment varchar(255) default '',
212 - rc_minor tinyint default '0',
213 - rc_bot tinyint default '0',
214 - rc_new tinyint default '0',
215 - rc_cur_id INTEGER default '0',
216 - rc_this_oldid INTEGER default '0',
217 - rc_last_oldid INTEGER default '0',
218 - rc_type tinyint default '0',
219 - rc_moved_to_ns tinyint default '0',
220 - rc_moved_to_title varchar(255) default '',
221 - rc_patrolled tinyint default '0',
222 - rc_ip varBLOB default '',
223 - rc_old_len int,
224 - rc_new_len int,
225 - rc_deleted tinyint default '0',
226 - rc_logid INTEGER default '0',
227 - rc_log_type varBLOB NULL default NULL,
228 - rc_log_action varBLOB NULL default NULL,
229 - rc_params blob NULL) /*$wgDBTableOptions*/;
230 -
231 -CREATE TABLE /*$wgDBprefix*/watchlist (
232 - wl_user INTEGER ,
233 - wl_namespace INTEGER default '0',
234 - wl_title varchar(255) default '',
235 - wl_notificationtimestamp varBLOB) /*$wgDBTableOptions*/;
236 -
237 -CREATE TABLE /*$wgDBprefix*/math (
238 - math_inputhash varBLOB ,
239 - math_outputhash varBLOB ,
240 - math_html_conservativeness tinyint ,
241 - math_html text,
242 - math_mathml text) /*$wgDBTableOptions*/;
243 -
244 -CREATE TABLE /*$wgDBprefix*/searchindex (
245 - si_page INTEGER ,
246 - si_title varchar(255) default '',
247 - si_text mediumtext ) ;
248 -
249 -CREATE TABLE /*$wgDBprefix*/interwiki (
250 - iw_prefix varchar(32) ,
251 - iw_url blob ,
252 - iw_local bool ,
253 - iw_trans tinyint default 0) /*$wgDBTableOptions*/;
254 -
255 -CREATE TABLE /*$wgDBprefix*/querycache (
256 - qc_type varBLOB ,
257 - qc_value INTEGER default '0',
258 - qc_namespace INTEGER default '0',
259 - qc_title varchar(255) default '') /*$wgDBTableOptions*/;
260 -
261 -CREATE TABLE /*$wgDBprefix*/objectcache (
262 - keyname varBLOB default '',
263 - value mediumblob,
264 - exptime datetime) /*$wgDBTableOptions*/;
265 -
266 -CREATE TABLE /*$wgDBprefix*/transcache (
267 - tc_url varBLOB ,
268 - tc_contents text,
269 - tc_time INTEGER ) /*$wgDBTableOptions*/;
270 -
271 -CREATE TABLE /*$wgDBprefix*/logging (
272 - log_id INTEGER PRIMARY KEY AUTOINCREMENT,
273 - log_type varBLOB default '',
274 - log_action varBLOB default '',
275 - log_timestamp BLOB default '19700101000000',
276 - log_user INTEGER default 0,
277 - log_namespace INTEGER default 0,
278 - log_title varchar(255) default '',
279 - log_comment varchar(255) default '',
280 - log_params blob ,
281 - log_deleted tinyint default '0') /*$wgDBTableOptions*/;
282 -
283 -CREATE TABLE /*$wgDBprefix*/trackbacks (
284 - tb_id INTEGER PRIMARY KEY AUTOINCREMENT,
285 - tb_page INTEGER REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
286 - tb_title varchar(255) ,
287 - tb_url blob ,
288 - tb_ex text,
289 - tb_name varchar(255)) /*$wgDBTableOptions*/;
290 -
291 -CREATE TABLE /*$wgDBprefix*/job (
292 - job_id INTEGER PRIMARY KEY AUTOINCREMENT,
293 - job_cmd varBLOB default '',
294 - job_namespace INTEGER ,
295 - job_title varchar(255) ,
296 - job_params blob ) /*$wgDBTableOptions*/;
297 -
298 -CREATE TABLE /*$wgDBprefix*/querycache_info (
299 - qci_type varBLOB default '',
300 - qci_timestamp BLOB default '19700101000000') /*$wgDBTableOptions*/;
301 -
302 -CREATE TABLE /*$wgDBprefix*/redirect (
303 - rd_from INTEGER default '0',
304 - rd_namespace INTEGER default '0',
305 - rd_title varchar(255) default '') /*$wgDBTableOptions*/;
306 -
307 -CREATE TABLE /*$wgDBprefix*/querycachetwo (
308 - qcc_type varBLOB ,
309 - qcc_value INTEGER default '0',
310 - qcc_namespace INTEGER default '0',
311 - qcc_title varchar(255) default '',
312 - qcc_namespacetwo INTEGER default '0',
313 - qcc_titletwo varchar(255) default '') /*$wgDBTableOptions*/;
314 -
315 -CREATE TABLE /*$wgDBprefix*/page_restrictions (
316 - pr_page INTEGER ,
317 - pr_type varBLOB ,
318 - pr_level varBLOB ,
319 - pr_cascade tinyint ,
320 - pr_user INTEGER NULL,
321 - pr_expiry varBLOB NULL,
322 - pr_id INTEGER PRIMARY KEY AUTOINCREMENT) /*$wgDBTableOptions*/;
323 -
324 -CREATE TABLE /*$wgDBprefix*/protected_titles (
325 - pt_namespace INTEGER ,
326 - pt_title varchar(255) ,
327 - pt_user INTEGER ,
328 - pt_reason tinyblob,
329 - pt_timestamp BLOB ,
330 - pt_expiry varBLOB default '',
331 - pt_create_perm varBLOB ) /*$wgDBTableOptions*/;
332 -
333 -CREATE TABLE /*$wgDBprefix*/page_props (
334 - pp_page INTEGER ,
335 - pp_propname varBLOB ,
336 - pp_value blob ) /*$wgDBTableOptions*/;
337 -
338 -CREATE TABLE /*$wgDBprefix*/updatelog (
339 - ul_key varchar(255) ) /*$wgDBTableOptions*/;
340 -
341 -
Index: trunk/phase3/maintenance/sqlite/archives/initial-indexes.sql
——@@ -0,0 +1,417 @@
 2+-- Correct for the total lack of indexes in the MW 1.13 SQLite schema
 3+--
 4+-- Unique indexes need to be handled with INSERT SELECT since just running
 5+-- the CREATE INDEX statement will fail if there are duplicate values.
 6+--
 7+-- Ignore duplicates, several tables will have them (e.g. bug 16966) but in
 8+-- most cases it's harmless to discard them. We'll keep the old tables with
 9+-- duplicates in so that the user can recover them in case of disaster.
 10+
 11+--------------------------------------------------------------------------------
 12+-- Drop temporary tables from aborted runs
 13+--------------------------------------------------------------------------------
 14+
 15+DROP TABLE IF EXISTS /*_*/user_tmp;
 16+DROP TABLE IF EXISTS /*_*/user_groups_tmp;
 17+DROP TABLE IF EXISTS /*_*/page_tmp;
 18+DROP TABLE IF EXISTS /*_*/revision_tmp;
 19+DROP TABLE IF EXISTS /*_*/pagelinks_tmp;
 20+DROP TABLE IF EXISTS /*_*/templatelinks_tmp;
 21+DROP TABLE IF EXISTS /*_*/imagelinks_tmp;
 22+DROP TABLE IF EXISTS /*_*/categorylinks_tmp;
 23+DROP TABLE IF EXISTS /*_*/category_tmp;
 24+DROP TABLE IF EXISTS /*_*/langlinks_tmp;
 25+DROP TABLE IF EXISTS /*_*/site_stats_tmp;
 26+DROP TABLE IF EXISTS /*_*/ipblocks_tmp;
 27+DROP TABLE IF EXISTS /*_*/watchlist_tmp;
 28+DROP TABLE IF EXISTS /*_*/math_tmp;
 29+DROP TABLE IF EXISTS /*_*/interwiki_tmp;
 30+DROP TABLE IF EXISTS /*_*/page_restrictions_tmp;
 31+DROP TABLE IF EXISTS /*_*/protected_titles_tmp;
 32+DROP TABLE IF EXISTS /*_*/page_props_tmp;
 33+
 34+--------------------------------------------------------------------------------
 35+-- Create new tables
 36+--------------------------------------------------------------------------------
 37+
 38+CREATE TABLE /*_*/user_tmp (
 39+ user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 40+ user_name varchar(255) binary NOT NULL default '',
 41+ user_real_name varchar(255) binary NOT NULL default '',
 42+ user_password tinyblob NOT NULL,
 43+ user_newpassword tinyblob NOT NULL,
 44+ user_newpass_time binary(14),
 45+ user_email tinytext NOT NULL,
 46+ user_options blob NOT NULL,
 47+ user_touched binary(14) NOT NULL default '',
 48+ user_token binary(32) NOT NULL default '',
 49+ user_email_authenticated binary(14),
 50+ user_email_token binary(32),
 51+ user_email_token_expires binary(14),
 52+ user_registration binary(14),
 53+ user_editcount int
 54+);
 55+CREATE UNIQUE INDEX user_name ON /*_*/user_tmp (user_name);
 56+CREATE INDEX user_email_token ON /*_*/user_tmp (user_email_token);
 57+
 58+
 59+CREATE TABLE /*_*/user_groups_tmp (
 60+ ug_user int unsigned NOT NULL default 0,
 61+ ug_group varbinary(16) NOT NULL default ''
 62+);
 63+
 64+CREATE UNIQUE INDEX ug_user_group ON /*_*/user_groups_tmp (ug_user,ug_group);
 65+CREATE INDEX ug_group ON /*_*/user_groups_tmp (ug_group);
 66+
 67+CREATE TABLE /*_*/page_tmp (
 68+ page_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 69+ page_namespace int NOT NULL,
 70+ page_title varchar(255) binary NOT NULL,
 71+ page_restrictions tinyblob NOT NULL,
 72+ page_counter bigint unsigned NOT NULL default 0,
 73+ page_is_redirect tinyint unsigned NOT NULL default 0,
 74+ page_is_new tinyint unsigned NOT NULL default 0,
 75+ page_random real unsigned NOT NULL,
 76+ page_touched binary(14) NOT NULL default '',
 77+ page_latest int unsigned NOT NULL,
 78+ page_len int unsigned NOT NULL
 79+);
 80+
 81+CREATE UNIQUE INDEX name_title ON /*_*/page_tmp (page_namespace,page_title);
 82+CREATE INDEX page_random ON /*_*/page_tmp (page_random);
 83+CREATE INDEX page_len ON /*_*/page_tmp (page_len);
 84+
 85+
 86+CREATE TABLE /*_*/revision_tmp (
 87+ rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 88+ rev_page int unsigned NOT NULL,
 89+ rev_text_id int unsigned NOT NULL,
 90+ rev_comment tinyblob NOT NULL,
 91+ rev_user int unsigned NOT NULL default 0,
 92+ rev_user_text varchar(255) binary NOT NULL default '',
 93+ rev_timestamp binary(14) NOT NULL default '',
 94+ rev_minor_edit tinyint unsigned NOT NULL default 0,
 95+ rev_deleted tinyint unsigned NOT NULL default 0,
 96+ rev_len int unsigned,
 97+ rev_parent_id int unsigned default NULL
 98+);
 99+CREATE UNIQUE INDEX rev_page_id ON /*_*/revision_tmp (rev_page, rev_id);
 100+CREATE INDEX rev_timestamp ON /*_*/revision_tmp (rev_timestamp);
 101+CREATE INDEX page_timestamp ON /*_*/revision_tmp (rev_page,rev_timestamp);
 102+CREATE INDEX user_timestamp ON /*_*/revision_tmp (rev_user,rev_timestamp);
 103+CREATE INDEX usertext_timestamp ON /*_*/revision_tmp (rev_user_text,rev_timestamp);
 104+
 105+CREATE TABLE /*_*/pagelinks_tmp (
 106+ pl_from int unsigned NOT NULL default 0,
 107+ pl_namespace int NOT NULL default 0,
 108+ pl_title varchar(255) binary NOT NULL default ''
 109+);
 110+
 111+CREATE UNIQUE INDEX pl_from ON /*_*/pagelinks_tmp (pl_from,pl_namespace,pl_title);
 112+CREATE INDEX pl_namespace_title ON /*_*/pagelinks_tmp (pl_namespace,pl_title,pl_from);
 113+
 114+
 115+CREATE TABLE /*_*/templatelinks_tmp (
 116+ tl_from int unsigned NOT NULL default 0,
 117+ tl_namespace int NOT NULL default 0,
 118+ tl_title varchar(255) binary NOT NULL default ''
 119+);
 120+
 121+CREATE UNIQUE INDEX tl_from ON /*_*/templatelinks_tmp (tl_from,tl_namespace,tl_title);
 122+CREATE INDEX tl_namespace_title ON /*_*/templatelinks_tmp (tl_namespace,tl_title,tl_from);
 123+
 124+
 125+CREATE TABLE /*_*/imagelinks_tmp (
 126+ il_from int unsigned NOT NULL default 0,
 127+ il_to varchar(255) binary NOT NULL default ''
 128+) /*$wgDBTableOptions*/;
 129+CREATE UNIQUE INDEX il_from ON /*_*/imagelinks_tmp (il_from,il_to);
 130+CREATE INDEX il_to ON /*_*/imagelinks_tmp (il_to,il_from);
 131+
 132+
 133+CREATE TABLE /*_*/categorylinks_tmp (
 134+ cl_from int unsigned NOT NULL default 0,
 135+ cl_to varchar(255) binary NOT NULL default '',
 136+ cl_sortkey varchar(70) binary NOT NULL default '',
 137+ cl_timestamp timestamp NOT NULL
 138+);
 139+CREATE UNIQUE INDEX cl_from ON /*_*/categorylinks_tmp (cl_from,cl_to);
 140+CREATE INDEX cl_sortkey ON /*_*/categorylinks_tmp (cl_to,cl_sortkey,cl_from);
 141+CREATE INDEX cl_timestamp ON /*_*/categorylinks_tmp (cl_to,cl_timestamp);
 142+
 143+
 144+CREATE TABLE /*_*/category_tmp (
 145+ cat_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 146+ cat_title varchar(255) binary NOT NULL,
 147+ cat_pages int signed NOT NULL default 0,
 148+ cat_subcats int signed NOT NULL default 0,
 149+ cat_files int signed NOT NULL default 0,
 150+ cat_hidden tinyint unsigned NOT NULL default 0
 151+);
 152+CREATE UNIQUE INDEX cat_title ON /*_*/category_tmp (cat_title);
 153+CREATE INDEX cat_pages ON /*_*/category_tmp (cat_pages);
 154+
 155+CREATE TABLE /*_*/langlinks_tmp (
 156+ ll_from int unsigned NOT NULL default 0,
 157+ ll_lang varbinary(20) NOT NULL default '',
 158+ ll_title varchar(255) binary NOT NULL default ''
 159+);
 160+
 161+CREATE UNIQUE INDEX ll_from ON /*_*/langlinks_tmp (ll_from, ll_lang);
 162+CREATE INDEX ll_lang_title ON /*_*/langlinks_tmp (ll_lang, ll_title);
 163+
 164+
 165+CREATE TABLE /*_*/site_stats_tmp (
 166+ ss_row_id int unsigned NOT NULL,
 167+ ss_total_views bigint unsigned default 0,
 168+ ss_total_edits bigint unsigned default 0,
 169+ ss_good_articles bigint unsigned default 0,
 170+ ss_total_pages bigint default '-1',
 171+ ss_users bigint default '-1',
 172+ ss_active_users bigint default '-1',
 173+ ss_admins int default '-1',
 174+ ss_images int default 0
 175+);
 176+CREATE UNIQUE INDEX ss_row_id ON /*_*/site_stats_tmp (ss_row_id);
 177+
 178+
 179+CREATE TABLE /*_*/ipblocks_tmp (
 180+ ipb_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
 181+ ipb_address tinyblob NOT NULL,
 182+ ipb_user int unsigned NOT NULL default 0,
 183+ ipb_by int unsigned NOT NULL default 0,
 184+ ipb_by_text varchar(255) binary NOT NULL default '',
 185+ ipb_reason tinyblob NOT NULL,
 186+ ipb_timestamp binary(14) NOT NULL default '',
 187+ ipb_auto bool NOT NULL default 0,
 188+
 189+ -- If set to 1, block applies only to logged-out users
 190+ ipb_anon_only bool NOT NULL default 0,
 191+ ipb_create_account bool NOT NULL default 1,
 192+ ipb_enable_autoblock bool NOT NULL default '1',
 193+ ipb_expiry varbinary(14) NOT NULL default '',
 194+ ipb_range_start tinyblob NOT NULL,
 195+ ipb_range_end tinyblob NOT NULL,
 196+ ipb_deleted bool NOT NULL default 0,
 197+ ipb_block_email bool NOT NULL default 0,
 198+ ipb_allow_usertalk bool NOT NULL default 0
 199+);
 200+CREATE UNIQUE INDEX ipb_address ON /*_*/ipblocks_tmp (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only);
 201+CREATE INDEX ipb_user ON /*_*/ipblocks_tmp (ipb_user);
 202+CREATE INDEX ipb_range ON /*_*/ipblocks_tmp (ipb_range_start(8), ipb_range_end(8));
 203+CREATE INDEX ipb_timestamp ON /*_*/ipblocks_tmp (ipb_timestamp);
 204+CREATE INDEX ipb_expiry ON /*_*/ipblocks_tmp (ipb_expiry);
 205+
 206+
 207+CREATE TABLE /*_*/watchlist_tmp (
 208+ wl_user int unsigned NOT NULL,
 209+ wl_namespace int NOT NULL default 0,
 210+ wl_title varchar(255) binary NOT NULL default '',
 211+ wl_notificationtimestamp varbinary(14)
 212+);
 213+
 214+CREATE UNIQUE INDEX wl_user_namespace_title ON /*_*/watchlist_tmp (wl_user, wl_namespace, wl_title);
 215+CREATE INDEX namespace_title ON /*_*/watchlist_tmp (wl_namespace, wl_title);
 216+
 217+
 218+CREATE TABLE /*_*/math_tmp (
 219+ math_inputhash varbinary(16) NOT NULL,
 220+ math_outputhash varbinary(16) NOT NULL,
 221+ math_html_conservativeness tinyint NOT NULL,
 222+ math_html text,
 223+ math_mathml text
 224+);
 225+
 226+CREATE UNIQUE INDEX math_inputhash ON /*_*/math_tmp (math_inputhash);
 227+
 228+
 229+CREATE TABLE /*_*/interwiki_tmp (
 230+ iw_prefix varchar(32) NOT NULL,
 231+ iw_url blob NOT NULL,
 232+ iw_local bool NOT NULL,
 233+ iw_trans tinyint NOT NULL default 0
 234+);
 235+
 236+CREATE UNIQUE INDEX iw_prefix ON /*_*/interwiki_tmp (iw_prefix);
 237+
 238+
 239+CREATE TABLE /*_*/page_restrictions_tmp (
 240+ pr_page int NOT NULL,
 241+ pr_type varbinary(60) NOT NULL,
 242+ pr_level varbinary(60) NOT NULL,
 243+ pr_cascade tinyint NOT NULL,
 244+ pr_user int NULL,
 245+ pr_expiry varbinary(14) NULL,
 246+ pr_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT
 247+);
 248+
 249+CREATE UNIQUE INDEX pr_pagetype ON /*_*/page_restrictions_tmp (pr_page,pr_type);
 250+CREATE UNIQUE INDEX pr_typelevel ON /*_*/page_restrictions_tmp (pr_type,pr_level);
 251+CREATE UNIQUE INDEX pr_level ON /*_*/page_restrictions_tmp (pr_level);
 252+CREATE UNIQUE INDEX pr_cascade ON /*_*/page_restrictions_tmp (pr_cascade);
 253+
 254+CREATE TABLE /*_*/protected_titles_tmp (
 255+ pt_namespace int NOT NULL,
 256+ pt_title varchar(255) binary NOT NULL,
 257+ pt_user int unsigned NOT NULL,
 258+ pt_reason tinyblob,
 259+ pt_timestamp binary(14) NOT NULL,
 260+ pt_expiry varbinary(14) NOT NULL default '',
 261+ pt_create_perm varbinary(60) NOT NULL
 262+);
 263+CREATE UNIQUE INDEX pt_namespace_title ON /*_*/protected_titles_tmp (pt_namespace,pt_title);
 264+CREATE INDEX pt_timestamp ON /*_*/protected_titles_tmp (pt_timestamp);
 265+
 266+CREATE TABLE /*_*/page_props_tmp (
 267+ pp_page int NOT NULL,
 268+ pp_propname varbinary(60) NOT NULL,
 269+ pp_value blob NOT NULL
 270+);
 271+CREATE UNIQUE INDEX pp_page_propname ON /*_*/page_props_tmp (pp_page,pp_propname);
 272+
 273+--------------------------------------------------------------------------------
 274+-- Populate the new tables using INSERT SELECT
 275+--------------------------------------------------------------------------------
 276+
 277+INSERT OR IGNORE INTO /*_*/user_tmp SELECT * FROM /*_*/user;
 278+INSERT OR IGNORE INTO /*_*/user_groups_tmp SELECT * FROM /*_*/user_groups;
 279+INSERT OR IGNORE INTO /*_*/page_tmp SELECT * FROM /*_*/page;
 280+INSERT OR IGNORE INTO /*_*/revision_tmp SELECT * FROM /*_*/revision;
 281+INSERT OR IGNORE INTO /*_*/pagelinks_tmp SELECT * FROM /*_*/pagelinks;
 282+INSERT OR IGNORE INTO /*_*/templatelinks_tmp SELECT * FROM /*_*/templatelinks;
 283+INSERT OR IGNORE INTO /*_*/imagelinks_tmp SELECT * FROM /*_*/imagelinks;
 284+INSERT OR IGNORE INTO /*_*/categorylinks_tmp SELECT * FROM /*_*/categorylinks;
 285+INSERT OR IGNORE INTO /*_*/category_tmp SELECT * FROM /*_*/category;
 286+INSERT OR IGNORE INTO /*_*/langlinks_tmp SELECT * FROM /*_*/langlinks;
 287+INSERT OR IGNORE INTO /*_*/site_stats_tmp SELECT * FROM /*_*/site_stats;
 288+INSERT OR IGNORE INTO /*_*/ipblocks_tmp SELECT * FROM /*_*/ipblocks;
 289+INSERT OR IGNORE INTO /*_*/watchlist_tmp SELECT * FROM /*_*/watchlist;
 290+INSERT OR IGNORE INTO /*_*/math_tmp SELECT * FROM /*_*/math;
 291+INSERT OR IGNORE INTO /*_*/interwiki_tmp SELECT * FROM /*_*/interwiki;
 292+INSERT OR IGNORE INTO /*_*/page_restrictions_tmp SELECT * FROM /*_*/page_restrictions;
 293+INSERT OR IGNORE INTO /*_*/protected_titles_tmp SELECT * FROM /*_*/protected_titles;
 294+INSERT OR IGNORE INTO /*_*/page_props_tmp SELECT * FROM /*_*/page_props;
 295+
 296+--------------------------------------------------------------------------------
 297+-- Do the table renames
 298+--------------------------------------------------------------------------------
 299+
 300+ALTER TABLE /*_*/user RENAME TO /*_*/user_old_13;
 301+ALTER TABLE /*_*/user_tmp RENAME TO /*_*/user;
 302+ALTER TABLE /*_*/user_groups RENAME TO /*_*/user_groups_old_13;
 303+ALTER TABLE /*_*/user_groups_tmp RENAME TO /*_*/user_groups;
 304+ALTER TABLE /*_*/page RENAME TO /*_*/page_old_13;
 305+ALTER TABLE /*_*/page_tmp RENAME TO /*_*/page;
 306+ALTER TABLE /*_*/revision RENAME TO /*_*/revision_old_13;
 307+ALTER TABLE /*_*/revision_tmp RENAME TO /*_*/revision;
 308+ALTER TABLE /*_*/pagelinks RENAME TO /*_*/pagelinks_old_13;
 309+ALTER TABLE /*_*/pagelinks_tmp RENAME TO /*_*/pagelinks;
 310+ALTER TABLE /*_*/templatelinks RENAME TO /*_*/templatelinks_old_13;
 311+ALTER TABLE /*_*/templatelinks_tmp RENAME TO /*_*/templatelinks;
 312+ALTER TABLE /*_*/imagelinks RENAME TO /*_*/imagelinks_old_13;
 313+ALTER TABLE /*_*/imagelinks_tmp RENAME TO /*_*/imagelinks;
 314+ALTER TABLE /*_*/categorylinks RENAME TO /*_*/categorylinks_old_13;
 315+ALTER TABLE /*_*/categorylinks_tmp RENAME TO /*_*/categorylinks;
 316+ALTER TABLE /*_*/category RENAME TO /*_*/category_old_13;
 317+ALTER TABLE /*_*/category_tmp RENAME TO /*_*/category;
 318+ALTER TABLE /*_*/langlinks RENAME TO /*_*/langlinks_old_13;
 319+ALTER TABLE /*_*/langlinks_tmp RENAME TO /*_*/langlinks;
 320+ALTER TABLE /*_*/site_stats RENAME TO /*_*/site_stats_old_13;
 321+ALTER TABLE /*_*/site_stats_tmp RENAME TO /*_*/site_stats;
 322+ALTER TABLE /*_*/ipblocks RENAME TO /*_*/ipblocks_old_13;
 323+ALTER TABLE /*_*/ipblocks_tmp RENAME TO /*_*/ipblocks;
 324+ALTER TABLE /*_*/watchlist RENAME TO /*_*/watchlist_old_13;
 325+ALTER TABLE /*_*/watchlist_tmp RENAME TO /*_*/watchlist;
 326+ALTER TABLE /*_*/math RENAME TO /*_*/math_old_13;
 327+ALTER TABLE /*_*/math_tmp RENAME TO /*_*/math;
 328+ALTER TABLE /*_*/interwiki RENAME TO /*_*/interwiki_old_13;
 329+ALTER TABLE /*_*/interwiki_tmp RENAME TO /*_*/interwiki;
 330+ALTER TABLE /*_*/page_restrictions RENAME TO /*_*/page_restrictions_old_13;
 331+ALTER TABLE /*_*/page_restrictions_tmp RENAME TO /*_*/page_restrictions;
 332+ALTER TABLE /*_*/protected_titles RENAME TO /*_*/protected_titles_old_13;
 333+ALTER TABLE /*_*/protected_titles_tmp RENAME TO /*_*/protected_titles;
 334+ALTER TABLE /*_*/page_props RENAME TO /*_*/page_props_old_13;
 335+ALTER TABLE /*_*/page_props_tmp RENAME TO /*_*/page_props;
 336+
 337+--------------------------------------------------------------------------------
 338+-- Drop and create tables with unique indexes but no valuable data
 339+--------------------------------------------------------------------------------
 340+
 341+
 342+DROP TABLE IF EXISTS /*_*/searchindex;
 343+CREATE TABLE /*_*/searchindex (
 344+ si_page int unsigned NOT NULL,
 345+ si_title varchar(255) NOT NULL default '',
 346+ si_text mediumtext NOT NULL
 347+);
 348+CREATE UNIQUE INDEX si_page ON /*_*/searchindex (si_page);
 349+CREATE INDEX si_title ON /*_*/searchindex (si_title);
 350+CREATE INDEX si_text ON /*_*/searchindex (si_text);
 351+
 352+DROP TABLE IF EXISTS /*_*/transcache;
 353+CREATE TABLE /*_*/transcache (
 354+ tc_url varbinary(255) NOT NULL,
 355+ tc_contents text,
 356+ tc_time int NOT NULL
 357+) /*$wgDBTableOptions*/;
 358+CREATE UNIQUE INDEX tc_url_idx ON /*_*/transcache (tc_url);
 359+
 360+DROP TABLE IF EXISTS /*_*/querycache_info;
 361+CREATE TABLE /*_*/querycache_info (
 362+ qci_type varbinary(32) NOT NULL default '',
 363+ qci_timestamp binary(14) NOT NULL default '19700101000000'
 364+) /*$wgDBTableOptions*/;
 365+CREATE UNIQUE INDEX qci_type ON /*_*/querycache_info (qci_type);
 366+
 367+--------------------------------------------------------------------------------
 368+-- Empty some cache tables to make the update faster
 369+--------------------------------------------------------------------------------
 370+
 371+DELETE FROM /*_*/querycache;
 372+DELETE FROM /*_*/objectcache;
 373+DELETE FROM /*_*/querycachetwo;
 374+
 375+--------------------------------------------------------------------------------
 376+-- Add indexes to tables with no unique indexes
 377+--------------------------------------------------------------------------------
 378+
 379+CREATE INDEX un_user_id ON /*_*/user_newtalk (user_id);
 380+CREATE INDEX un_user_ip ON /*_*/user_newtalk (user_ip);
 381+CREATE INDEX name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
 382+CREATE INDEX ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
 383+CREATE INDEX el_from ON /*_*/externallinks (el_from, el_to(40));
 384+CREATE INDEX el_to ON /*_*/externallinks (el_to(60), el_from);
 385+CREATE INDEX el_index ON /*_*/externallinks (el_index(60));
 386+CREATE INDEX img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
 387+CREATE INDEX img_size ON /*_*/image (img_size);
 388+CREATE INDEX img_timestamp ON /*_*/image (img_timestamp);
 389+CREATE INDEX img_sha1 ON /*_*/image (img_sha1);
 390+CREATE INDEX oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
 391+CREATE INDEX oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp);
 392+CREATE INDEX oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name(14));
 393+CREATE INDEX oi_sha1 ON /*_*/oldimage (oi_sha1);
 394+CREATE INDEX fa_name ON /*_*/filearchive (fa_name, fa_timestamp);
 395+CREATE INDEX fa_group_key ON /*_*/filearchive (fa_storage_group, fa_storage_key);
 396+CREATE INDEX fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp);
 397+CREATE INDEX fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);
 398+CREATE INDEX rc_timestamp ON /*_*/recentchanges (rc_timestamp);
 399+CREATE INDEX rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title);
 400+CREATE INDEX rc_cur_id ON /*_*/recentchanges (rc_cur_id);
 401+CREATE INDEX new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp);
 402+CREATE INDEX rc_ip ON /*_*/recentchanges (rc_ip);
 403+CREATE INDEX rc_ns_usertext ON /*_*/recentchanges (rc_namespace, rc_user_text);
 404+CREATE INDEX rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp);
 405+CREATE INDEX qc_type_value ON /*_*/querycache (qc_type,qc_value);
 406+CREATE INDEX oc_exptime ON /*_*/objectcache (exptime);
 407+CREATE INDEX type_time ON /*_*/logging (log_type, log_timestamp);
 408+CREATE INDEX user_time ON /*_*/logging (log_user, log_timestamp);
 409+CREATE INDEX page_time ON /*_*/logging (log_namespace, log_title, log_timestamp);
 410+CREATE INDEX times ON /*_*/logging (log_timestamp);
 411+CREATE INDEX tb_page ON /*_*/trackbacks (tb_page);
 412+CREATE INDEX job_cmd_namespace_title ON /*_*/job (job_cmd, job_namespace, job_title);
 413+CREATE INDEX rd_ns_title ON /*_*/redirect (rd_namespace,rd_title,rd_from);
 414+CREATE INDEX qcc_type ON /*_*/querycachetwo (qcc_type,qcc_value);
 415+CREATE INDEX qcc_title ON /*_*/querycachetwo (qcc_type,qcc_namespace,qcc_title);
 416+CREATE INDEX qcc_titletwo ON /*_*/querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
 417+
 418+INSERT INTO /*_*/updatelog VALUES ('initial_indexes');
Property changes on: trunk/phase3/maintenance/sqlite/archives/initial-indexes.sql
___________________________________________________________________
Name: svn:eol-style
1419 + native
Index: trunk/phase3/maintenance/sqlite/README
——@@ -0,0 +1,12 @@
 2+SQLite shares the MySQL schema file at maintenance/tables.sql, with a set of
 3+compatibility regexes to convert MySQL syntax to SQLite syntax:
 4+
 5+* BINARY() and VARBINARY() fields are converted to BLOB
 6+* the UNSIGNED modifier is removed
 7+* "INT" fields are converted to "INTEGER"
 8+* ENUM is converted to BLOB
 9+* the BINARY collation modifier is removed
 10+* AUTO_INCREMENT is converted to AUTOINCREMENT
 11+* Any table options are removed
 12+* Truncated indexes are upgraded to full-width indexes
 13+* FULLTEXT indexes are converted to ordinary indexes
Property changes on: trunk/phase3/maintenance/sqlite/README
___________________________________________________________________
Name: svn:eol-style
114 + native
Index: trunk/phase3/maintenance/updaters.inc
——@@ -16,138 +16,148 @@
1717 require_once( "$IP/includes/Hooks.php" );
1818
1919 /**
20 - * List of update functions to call on a MySQL-based MediaWiki installation,
21 - * in sequence. First item is function name, rest are parameters to pass.
 20+ * List of update functions to call for each DB type, in sequence. First item
 21+ * is function name, rest are parameters to pass.
2222 */
23 -$wgMysqlUpdates = array(
24 - // 1.2
25 - // update_passwords obsolete
26 - array( 'add_field', 'ipblocks', 'ipb_id', 'patch-ipblocks.sql' ),
27 - array( 'add_field', 'ipblocks', 'ipb_expiry', 'patch-ipb_expiry.sql' ),
28 - array( 'do_interwiki_update' ),
29 - array( 'do_index_update' ),
30 - // do_linkscc_update obsolete
31 - array( 'add_table', 'hitcounter', 'patch-hitcounter.sql' ),
32 - array( 'add_field', 'recentchanges', 'rc_type', 'patch-rc_type.sql' ),
33 -
34 - // 1.3
35 - array( 'add_field', 'user', 'user_real_name', 'patch-user-realname.sql' ),
36 - array( 'add_table', 'querycache', 'patch-querycache.sql' ),
37 - array( 'add_table', 'objectcache', 'patch-objectcache.sql' ),
38 - array( 'add_table', 'categorylinks', 'patch-categorylinks.sql' ),
39 - // do_linkscc_1_3_update obsolete
40 - array( 'do_old_links_update' ),
41 - array( 'add_field', 'recentchanges', 'rc_ip', 'patch-rc_ip.sql' ),
42 -
43 - // 1.4
44 - array( 'do_image_name_unique_update' ),
45 - array( 'add_field', 'recentchanges', 'rc_id', 'patch-rc_id.sql' ),
46 - array( 'add_field', 'recentchanges', 'rc_patrolled', 'patch-rc-patrol.sql' ),
47 - array( 'add_table', 'logging', 'patch-logging.sql' ),
48 - // do_user_rights_update obsolete
49 - array( 'add_field', 'user', 'user_token', 'patch-user_token.sql' ),
50 - // old, old_articleid, patch-remove-old-title-namespace.sql obsolete
51 - // user_groups, patch-userlevels.sql obsolete
52 - // do_group_update() obsolete
53 - array( 'do_watchlist_update' ),
54 - array( 'do_user_update' ),
55 - // do_copy_newtalk_to_watchlist obsolete
56 -
57 - // 1.5
58 - array( 'do_schema_restructuring' ),
59 - array( 'add_field', 'logging', 'log_params', 'patch-log_params.sql' ),
60 - array( 'check_bin', 'logging', 'log_title', 'patch-logging-title.sql', ),
61 - array( 'add_field', 'archive', 'ar_rev_id', 'patch-archive-rev_id.sql' ),
62 - array( 'add_field', 'page', 'page_len', 'patch-page_len.sql' ),
63 - array( 'do_inverse_timestamp' ),
64 - array( 'do_text_id' ),
65 - array( 'add_field', 'revision', 'rev_deleted', 'patch-rev_deleted.sql' ),
66 - array( 'add_field', 'image', 'img_width', 'patch-img_width.sql' ),
67 - array( 'add_field', 'image', 'img_metadata', 'patch-img_metadata.sql' ),
68 - array( 'add_field', 'user', 'user_email_token', 'patch-user_email_token.sql' ),
69 - array( 'add_field', 'archive', 'ar_text_id', 'patch-archive-text_id.sql' ),
70 - array( 'do_namespace_size' ),
71 - array( 'add_field', 'image', 'img_media_type', 'patch-img_media_type.sql' ),
72 - array( 'do_pagelinks_update' ),
73 - array( 'do_drop_img_type' ),
74 - array( 'do_user_unique_update' ),
75 - array( 'do_user_groups_update' ),
76 - array( 'add_field', 'site_stats', 'ss_total_pages', 'patch-ss_total_articles.sql' ),
77 - array( 'add_table', 'user_newtalk', 'patch-usernewtalk2.sql' ),
78 - array( 'add_table', 'transcache', 'patch-transcache.sql' ),
79 - array( 'add_field', 'interwiki', 'iw_trans', 'patch-interwiki-trans.sql' ),
80 - array( 'add_table', 'trackbacks', 'patch-trackbacks.sql' ),
81 -
82 - // 1.6
83 - array( 'do_watchlist_null' ),
84 - // do_image_index_update obsolete
85 - array( 'do_logging_timestamp_index' ),
86 - array( 'add_field', 'ipblocks', 'ipb_range_start', 'patch-ipb_range_start.sql' ),
87 - array( 'do_page_random_update' ),
88 - array( 'add_field', 'user', 'user_registration','patch-user_registration.sql' ),
89 - array( 'do_templatelinks_update' ),
90 - array( 'add_table', 'externallinks', 'patch-externallinks.sql' ),
91 - array( 'add_table', 'job', 'patch-job.sql' ),
92 - array( 'add_field', 'site_stats', 'ss_images', 'patch-ss_images.sql' ),
93 - array( 'add_table', 'langlinks', 'patch-langlinks.sql' ),
94 - array( 'add_table', 'querycache_info', 'patch-querycacheinfo.sql' ),
95 - array( 'add_table', 'filearchive', 'patch-filearchive.sql' ),
96 - array( 'add_field', 'ipblocks', 'ipb_anon_only', 'patch-ipb_anon_only.sql' ),
97 - array( 'do_rc_indices_update' ),
98 -
99 - // 1.9
100 - array( 'add_field', 'user', 'user_newpass_time', 'patch-user_newpass_time.sql' ),
101 - array( 'add_table', 'redirect', 'patch-redirect.sql' ),
102 - array( 'add_table', 'querycachetwo', 'patch-querycachetwo.sql' ),
103 - array( 'add_field', 'ipblocks', 'ipb_enable_autoblock', 'patch-ipb_optional_autoblock.sql' ),
104 - array( 'do_backlinking_indices_update' ),
105 - array( 'add_field', 'recentchanges', 'rc_old_len', 'patch-rc_len.sql' ),
106 - array( 'add_field', 'user', 'user_editcount', 'patch-user_editcount.sql' ),
107 -
108 - // 1.10
109 - array( 'do_restrictions_update' ),
110 - array( 'add_field', 'logging', 'log_id', 'patch-log_id.sql' ),
111 - array( 'add_field', 'revision', 'rev_parent_id', 'patch-rev_parent_id.sql' ),
112 - array( 'add_field', 'page_restrictions', 'pr_id', 'patch-page_restrictions_sortkey.sql' ),
113 - array( 'add_field', 'revision', 'rev_len', 'patch-rev_len.sql' ),
114 - array( 'add_field', 'recentchanges', 'rc_deleted', 'patch-rc_deleted.sql' ),
115 - array( 'add_field', 'logging', 'log_deleted', 'patch-log_deleted.sql' ),
116 - array( 'add_field', 'archive', 'ar_deleted', 'patch-ar_deleted.sql' ),
117 - array( 'add_field', 'ipblocks', 'ipb_deleted', 'patch-ipb_deleted.sql' ),
118 - array( 'add_field', 'filearchive', 'fa_deleted', 'patch-fa_deleted.sql' ),
119 - array( 'add_field', 'archive', 'ar_len', 'patch-ar_len.sql' ),
120 -
121 - // 1.11
122 - array( 'add_field', 'ipblocks', 'ipb_block_email', 'patch-ipb_emailban.sql' ),
123 - array( 'do_categorylinks_indices_update' ),
124 - array( 'add_field', 'oldimage', 'oi_metadata', 'patch-oi_metadata.sql'),
125 - array( 'do_archive_user_index' ),
126 - array( 'do_image_user_index' ),
127 - array( 'do_oldimage_user_index' ),
128 - array( 'add_field', 'archive', 'ar_page_id', 'patch-archive-page_id.sql'),
129 - array( 'add_field', 'image', 'img_sha1', 'patch-img_sha1.sql' ),
 23+$wgUpdates = array(
 24+ 'mysql' => array(
 25+ // 1.2
 26+ // update_passwords obsolete
 27+ array( 'add_field', 'ipblocks', 'ipb_id', 'patch-ipblocks.sql' ),
 28+ array( 'add_field', 'ipblocks', 'ipb_expiry', 'patch-ipb_expiry.sql' ),
 29+ array( 'do_interwiki_update' ),
 30+ array( 'do_index_update' ),
 31+ // do_linkscc_update obsolete
 32+ array( 'add_table', 'hitcounter', 'patch-hitcounter.sql' ),
 33+ array( 'add_field', 'recentchanges', 'rc_type', 'patch-rc_type.sql' ),
 34+
 35+ // 1.3
 36+ array( 'add_field', 'user', 'user_real_name', 'patch-user-realname.sql' ),
 37+ array( 'add_table', 'querycache', 'patch-querycache.sql' ),
 38+ array( 'add_table', 'objectcache', 'patch-objectcache.sql' ),
 39+ array( 'add_table', 'categorylinks', 'patch-categorylinks.sql' ),
 40+ // do_linkscc_1_3_update obsolete
 41+ array( 'do_old_links_update' ),
 42+ array( 'add_field', 'recentchanges', 'rc_ip', 'patch-rc_ip.sql' ),
 43+
 44+ // 1.4
 45+ array( 'do_image_name_unique_update' ),
 46+ array( 'add_field', 'recentchanges', 'rc_id', 'patch-rc_id.sql' ),
 47+ array( 'add_field', 'recentchanges', 'rc_patrolled', 'patch-rc-patrol.sql' ),
 48+ array( 'add_table', 'logging', 'patch-logging.sql' ),
 49+ // do_user_rights_update obsolete
 50+ array( 'add_field', 'user', 'user_token', 'patch-user_token.sql' ),
 51+ // old, old_articleid, patch-remove-old-title-namespace.sql obsolete
 52+ // user_groups, patch-userlevels.sql obsolete
 53+ // do_group_update() obsolete
 54+ array( 'do_watchlist_update' ),
 55+ array( 'do_user_update' ),
 56+ // do_copy_newtalk_to_watchlist obsolete
 57+
 58+ // 1.5
 59+ array( 'do_schema_restructuring' ),
 60+ array( 'add_field', 'logging', 'log_params', 'patch-log_params.sql' ),
 61+ array( 'check_bin', 'logging', 'log_title', 'patch-logging-title.sql', ),
 62+ array( 'add_field', 'archive', 'ar_rev_id', 'patch-archive-rev_id.sql' ),
 63+ array( 'add_field', 'page', 'page_len', 'patch-page_len.sql' ),
 64+ array( 'do_inverse_timestamp' ),
 65+ array( 'do_text_id' ),
 66+ array( 'add_field', 'revision', 'rev_deleted', 'patch-rev_deleted.sql' ),
 67+ array( 'add_field', 'image', 'img_width', 'patch-img_width.sql' ),
 68+ array( 'add_field', 'image', 'img_metadata', 'patch-img_metadata.sql' ),
 69+ array( 'add_field', 'user', 'user_email_token', 'patch-user_email_token.sql' ),
 70+ array( 'add_field', 'archive', 'ar_text_id', 'patch-archive-text_id.sql' ),
 71+ array( 'do_namespace_size' ),
 72+ array( 'add_field', 'image', 'img_media_type', 'patch-img_media_type.sql' ),
 73+ array( 'do_pagelinks_update' ),
 74+ array( 'do_drop_img_type' ),
 75+ array( 'do_user_unique_update' ),
 76+ array( 'do_user_groups_update' ),
 77+ array( 'add_field', 'site_stats', 'ss_total_pages', 'patch-ss_total_articles.sql' ),
 78+ array( 'add_table', 'user_newtalk', 'patch-usernewtalk2.sql' ),
 79+ array( 'add_table', 'transcache', 'patch-transcache.sql' ),
 80+ array( 'add_field', 'interwiki', 'iw_trans', 'patch-interwiki-trans.sql' ),
 81+ array( 'add_table', 'trackbacks', 'patch-trackbacks.sql' ),
 82+
 83+ // 1.6
 84+ array( 'do_watchlist_null' ),
 85+ // do_image_index_update obsolete
 86+ array( 'do_logging_timestamp_index' ),
 87+ array( 'add_field', 'ipblocks', 'ipb_range_start', 'patch-ipb_range_start.sql' ),
 88+ array( 'do_page_random_update' ),
 89+ array( 'add_field', 'user', 'user_registration','patch-user_registration.sql' ),
 90+ array( 'do_templatelinks_update' ),
 91+ array( 'add_table', 'externallinks', 'patch-externallinks.sql' ),
 92+ array( 'add_table', 'job', 'patch-job.sql' ),
 93+ array( 'add_field', 'site_stats', 'ss_images', 'patch-ss_images.sql' ),
 94+ array( 'add_table', 'langlinks', 'patch-langlinks.sql' ),
 95+ array( 'add_table', 'querycache_info', 'patch-querycacheinfo.sql' ),
 96+ array( 'add_table', 'filearchive', 'patch-filearchive.sql' ),
 97+ array( 'add_field', 'ipblocks', 'ipb_anon_only', 'patch-ipb_anon_only.sql' ),
 98+ array( 'do_rc_indices_update' ),
 99+
 100+ // 1.9
 101+ array( 'add_field', 'user', 'user_newpass_time', 'patch-user_newpass_time.sql' ),
 102+ array( 'add_table', 'redirect', 'patch-redirect.sql' ),
 103+ array( 'add_table', 'querycachetwo', 'patch-querycachetwo.sql' ),
 104+ array( 'add_field', 'ipblocks', 'ipb_enable_autoblock', 'patch-ipb_optional_autoblock.sql' ),
 105+ array( 'do_backlinking_indices_update' ),
 106+ array( 'add_field', 'recentchanges', 'rc_old_len', 'patch-rc_len.sql' ),
 107+ array( 'add_field', 'user', 'user_editcount', 'patch-user_editcount.sql' ),
 108+
 109+ // 1.10
 110+ array( 'do_restrictions_update' ),
 111+ array( 'add_field', 'logging', 'log_id', 'patch-log_id.sql' ),
 112+ array( 'add_field', 'revision', 'rev_parent_id', 'patch-rev_parent_id.sql' ),
 113+ array( 'add_field', 'page_restrictions', 'pr_id', 'patch-page_restrictions_sortkey.sql' ),
 114+ array( 'add_field', 'revision', 'rev_len', 'patch-rev_len.sql' ),
 115+ array( 'add_field', 'recentchanges', 'rc_deleted', 'patch-rc_deleted.sql' ),
 116+ array( 'add_field', 'logging', 'log_deleted', 'patch-log_deleted.sql' ),
 117+ array( 'add_field', 'archive', 'ar_deleted', 'patch-ar_deleted.sql' ),
 118+ array( 'add_field', 'ipblocks', 'ipb_deleted', 'patch-ipb_deleted.sql' ),
 119+ array( 'add_field', 'filearchive', 'fa_deleted', 'patch-fa_deleted.sql' ),
 120+ array( 'add_field', 'archive', 'ar_len', 'patch-ar_len.sql' ),
 121+
 122+ // 1.11
 123+ array( 'add_field', 'ipblocks', 'ipb_block_email', 'patch-ipb_emailban.sql' ),
 124+ array( 'do_categorylinks_indices_update' ),
 125+ array( 'add_field', 'oldimage', 'oi_metadata', 'patch-oi_metadata.sql'),
 126+ array( 'do_archive_user_index' ),
 127+ array( 'do_image_user_index' ),
 128+ array( 'do_oldimage_user_index' ),
 129+ array( 'add_field', 'archive', 'ar_page_id', 'patch-archive-page_id.sql'),
 130+ array( 'add_field', 'image', 'img_sha1', 'patch-img_sha1.sql' ),
130131
131 - // 1.12
132 - array( 'add_table', 'protected_titles', 'patch-protected_titles.sql' ),
133 -
134 - // 1.13
135 - array( 'add_field', 'ipblocks', 'ipb_by_text', 'patch-ipb_by_text.sql' ),
136 - array( 'add_table', 'page_props', 'patch-page_props.sql' ),
137 - array( 'add_table', 'updatelog', 'patch-updatelog.sql' ),
138 - array( 'add_table', 'category', 'patch-category.sql' ),
139 - array( 'do_category_population' ),
140 - array( 'add_field', 'archive', 'ar_parent_id', 'patch-ar_parent_id.sql'),
141 - array( 'add_field', 'user_newtalk', 'user_last_timestamp', 'patch-user_last_timestamp.sql'),
142 - array( 'do_populate_parent_id' ),
143 - array( 'check_bin', 'protected_titles', 'pt_title', 'patch-pt_title-encoding.sql', ),
144 - array( 'maybe_do_profiling_memory_update' ),
145 - array( 'do_filearchive_indices_update' ),
146 - array( 'update_password_format' ),
147 -
148 - // 1.14
149 - array( 'add_field', 'site_stats', 'ss_active_users', 'patch-ss_active_users.sql' ),
150 - array( 'do_active_users_init' ),
151 - array( 'add_field', 'ipblocks', 'ipb_allow_usertalk', 'patch-ipb_allow_usertalk.sql' )
 132+ // 1.12
 133+ array( 'add_table', 'protected_titles', 'patch-protected_titles.sql' ),
 134+
 135+ // 1.13
 136+ array( 'add_field', 'ipblocks', 'ipb_by_text', 'patch-ipb_by_text.sql' ),
 137+ array( 'add_table', 'page_props', 'patch-page_props.sql' ),
 138+ array( 'add_table', 'updatelog', 'patch-updatelog.sql' ),
 139+ array( 'add_table', 'category', 'patch-category.sql' ),
 140+ array( 'do_category_population' ),
 141+ array( 'add_field', 'archive', 'ar_parent_id', 'patch-ar_parent_id.sql'),
 142+ array( 'add_field', 'user_newtalk', 'user_last_timestamp', 'patch-user_last_timestamp.sql'),
 143+ array( 'do_populate_parent_id' ),
 144+ array( 'check_bin', 'protected_titles', 'pt_title', 'patch-pt_title-encoding.sql', ),
 145+ array( 'maybe_do_profiling_memory_update' ),
 146+ array( 'do_filearchive_indices_update' ),
 147+ array( 'update_password_format' ),
 148+
 149+ // 1.14
 150+ array( 'add_field', 'site_stats', 'ss_active_users', 'patch-ss_active_users.sql' ),
 151+ array( 'do_active_users_init' ),
 152+ array( 'add_field', 'ipblocks', 'ipb_allow_usertalk', 'patch-ipb_allow_usertalk.sql' ),
 153+ ),
 154+
 155+ 'sqlite' => array(
 156+ // 1.14
 157+ array( 'add_field', 'site_stats', 'ss_active_users', 'patch-ss_active_users.sql' ),
 158+ array( 'do_active_users_init' ),
 159+ array( 'add_field', 'ipblocks', 'ipb_allow_usertalk', 'patch-ipb_allow_usertalk.sql' ),
 160+ array( 'sqlite_initial_indexes' ),
 161+ ),
152162 );
153163
154164
——@@ -1070,11 +1080,13 @@
10711081 }
10721082
10731083 # Run core updates in sequence...
1074 - global $wgMysqlUpdates;
1075 - foreach( $wgMysqlUpdates as $params ) {
1076 - $func = array_shift( $params );
1077 - call_user_func_array( $func, $params );
1078 - flush();
 1084+ global $wgUpdates;
 1085+ if ( isset( $wgUpdates[$wgDBtype] ) ) {
 1086+ foreach( $wgUpdates[$wgDBtype] as $params ) {
 1087+ $func = array_shift( $params );
 1088+ call_user_func_array( $func, $params );
 1089+ flush();
 1090+ }
10791091 }
10801092
10811093 /// @fixme clean up this mess too!
——@@ -1112,10 +1124,9 @@
11131125
11141126 function archive($name) {
11151127 global $wgDBtype, $IP;
1116 - switch ($wgDBtype) {
1117 - case "postgres":
1118 - return "$IP/maintenance/postgres/archives/$name";
1119 - default:
 1128+ if ( file_exists( "$IP/maintenance/$wgDBtype/archives/$name" ) ) {
 1129+ return "$IP/maintenance/$wgDBtype/archives/$name";
 1130+ } else {
11201131 return "$IP/maintenance/archives/$name";
11211132 }
11221133 }
——@@ -1243,6 +1254,23 @@
12441255 echo "done\n";
12451256 }
12461257
 1258+function sqlite_initial_indexes() {
 1259+ global $wgDatabase;
 1260+ if ( update_row_exists( 'initial_indexes' ) ) {
 1261+ echo "...have initial indexes\n";
 1262+ return;
 1263+ }
 1264+ echo "Adding initial indexes...";
 1265+ $wgDatabase->sourceFile( archive( 'initial-indexes.sql' ) );
 1266+ echo "done\n";
 1267+}
 1268+
 1269+
 1270+/***********************************************************************
 1271+ * Start PG crap
 1272+ * TODO: merge with above
 1273+ ***********************************************************************/
 1274+
12471275 function
12481276 pg_describe_table($table)
12491277 {
Index: trunk/phase3/maintenance/tables.sql
——@@ -2,6 +2,8 @@
33 -- This is read and executed by the install script; you should
44 -- not have to run it by itself unless doing a manual install.
55
 6+-- This is a shared schema file used for both MySQL and SQLite installs.
 7+
68 --
79 -- General notes:
810 --
——@@ -28,7 +30,7 @@
2931 -- well.
3032 --
3133 --
 34+-- The /*_*/ comments in this and other files are
3235 -- replaced with the defined table prefix by the installer
3336 -- and updater scripts. If you are installing or running
3437 -- updates manually, you will need to manually insert the
——@@ -48,8 +50,8 @@
4951 -- preferences and to key tracking information in the other
5052 -- tables.
5153 --
52 -CREATE TABLE /*$wgDBprefix*/user (
53 - user_id int unsigned NOT NULL auto_increment,
 54+CREATE TABLE /*_*/user (
 55+ user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
5456
5557 -- Usernames must be unique, must not be in the form of
5658 -- an IP address. _Shouldn't_ allow slashes or case
——@@ -124,13 +126,12 @@
125127 -- Meant primarily for heuristic checks to give an impression of whether
126128 -- the account has been used much.
127129 --
128 - user_editcount int,
 130+ user_editcount int
 131+) /*$wgDBTableOptions*/;
129132
130 - PRIMARY KEY user_id (user_id),
131 - UNIQUE INDEX user_name (user_name),
132 - INDEX (user_email_token)
 133+CREATE UNIQUE INDEX user_name ON /*_*/user (user_name);
 134+CREATE INDEX user_email_token ON /*_*/user (user_email_token);
133135
134 -) /*$wgDBTableOptions*/;
135136
136137 --
137138 -- User permissions have been broken out to a separate table;
——@@ -140,9 +141,9 @@
141142 -- This table replaces the old user_rights field which used a
142143 -- comma-separated blob.
143144 --
144 -CREATE TABLE /*$wgDBprefix*/user_groups (
 145+CREATE TABLE /*_*/user_groups (
145146 -- Key to user_id
146 - ug_user int unsigned NOT NULL default '0',
 147+ ug_user int unsigned NOT NULL default 0,
147148
148149 -- Group names are short symbolic string keys.
149150 -- The set of group names is open-ended, though in practice
——@@ -152,37 +153,39 @@
153154 -- with particular permissions. A user will have the combined
154155 -- permissions of any group they're explicitly in, plus
155156 -- the implicit '*' and 'user' groups.
156 - ug_group varbinary(16) NOT NULL default '',
157 -
158 - PRIMARY KEY (ug_user,ug_group),
159 - KEY (ug_group)
 157+ ug_group varbinary(16) NOT NULL default ''
160158 ) /*$wgDBTableOptions*/;
161159
 160+CREATE UNIQUE INDEX ug_user_group ON /*_*/user_groups (ug_user,ug_group);
 161+CREATE INDEX ug_group ON /*_*/user_groups (ug_group);
 162+
 163+
162164 -- Stores notifications of user talk page changes, for the display
163165 -- of the "you have new messages" box
164 -CREATE TABLE /*$wgDBprefix*/user_newtalk (
 166+CREATE TABLE /*_*/user_newtalk (
165167 -- Key to user.user_id
166 - user_id int NOT NULL default '0',
 168+ user_id int NOT NULL default 0,
167169 -- If the user is an anonymous user their IP address is stored here
168170 -- since the user_id of 0 is ambiguous
169171 user_ip varbinary(40) NOT NULL default '',
170172 -- The highest timestamp of revisions of the talk page viewed
171173 -- by this user
172 - user_last_timestamp binary(14) NOT NULL default '',
173 - INDEX user_id (user_id),
174 - INDEX user_ip (user_ip)
175 -
 174+ user_last_timestamp binary(14) NOT NULL default ''
176175 ) /*$wgDBTableOptions*/;
177176
 177+-- Indexes renamed for SQLite in 1.14
 178+CREATE INDEX un_user_id ON /*_*/user_newtalk (user_id);
 179+CREATE INDEX un_user_ip ON /*_*/user_newtalk (user_ip);
178180
 181+
179182 --
180183 -- Core of the wiki: each page has an entry here which identifies
181184 -- it by title and contains some essential metadata.
182185 --
183 -CREATE TABLE /*$wgDBprefix*/page (
 186+CREATE TABLE /*_*/page (
184187 -- Unique identifier number. The page_id will be preserved across
185188 -- edits and rename operations, but not deletions and recreations.
186 - page_id int unsigned NOT NULL auto_increment,
 189+ page_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
187190
188191 -- A page name is broken into a namespace and a title.
189192 -- The namespace keys are UI-language-independent constants,
——@@ -198,14 +201,14 @@
199202 page_restrictions tinyblob NOT NULL,
200203
201204 -- Number of times this page has been viewed.
202 - page_counter bigint unsigned NOT NULL default '0',
 205+ page_counter bigint unsigned NOT NULL default 0,
203206
204207 -- 1 indicates the article is a redirect.
205 - page_is_redirect tinyint unsigned NOT NULL default '0',
 208+ page_is_redirect tinyint unsigned NOT NULL default 0,
206209
207210 -- 1 indicates this is a new entry, with only one edit.
208211 -- Not all pages with one edit are new pages.
209 - page_is_new tinyint unsigned NOT NULL default '0',
 212+ page_is_new tinyint unsigned NOT NULL default 0,
210213
211214 -- Random value between 0 and 1, used for Special:Randompage
212215 page_random real unsigned NOT NULL,
——@@ -223,24 +226,21 @@
224227 page_latest int unsigned NOT NULL,
225228
226229 -- Uncompressed length in bytes of the page's current source text.
227 - page_len int unsigned NOT NULL,
 230+ page_len int unsigned NOT NULL
 231+) /*$wgDBTableOptions*/;
228232
229 - PRIMARY KEY page_id (page_id),
230 - UNIQUE INDEX name_title (page_namespace,page_title),
231 -
232 - -- Special-purpose indexes
233 - INDEX (page_random),
234 - INDEX (page_len)
 233+CREATE UNIQUE INDEX name_title ON /*_*/page (page_namespace,page_title);
 234+CREATE INDEX page_random ON /*_*/page (page_random);
 235+CREATE INDEX page_len ON /*_*/page (page_len);
235236
236 -) /*$wgDBTableOptions*/;
237237
238238 --
239239 -- Every edit of a page creates also a revision row.
240240 -- This stores metadata about the revision, and a reference
241241 -- to the text storage backend.
242242 --
243 -CREATE TABLE /*$wgDBprefix*/revision (
244 - rev_id int unsigned NOT NULL auto_increment,
 243+CREATE TABLE /*_*/revision (
 244+ rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
245245
246246 -- Key to page_id. This should _never_ be invalid.
247247 rev_page int unsigned NOT NULL,
——@@ -258,7 +258,7 @@
259259
260260 -- Key to user.user_id of the user who made this edit.
261261 -- Stores 0 for anonymous edits and for some mass imports.
262 - rev_user int unsigned NOT NULL default '0',
 262+ rev_user int unsigned NOT NULL default 0,
263263
264264 -- Text username or IP address of the editor.
265265 rev_user_text varchar(255) binary NOT NULL default '',
——@@ -268,28 +268,27 @@
269269
270270 -- Records whether the user marked the 'minor edit' checkbox.
271271 -- Many automated edits are marked as minor.
272 - rev_minor_edit tinyint unsigned NOT NULL default '0',
 272+ rev_minor_edit tinyint unsigned NOT NULL default 0,
273273
274274 -- Not yet used; reserved for future changes to the deletion system.
275 - rev_deleted tinyint unsigned NOT NULL default '0',
 275+ rev_deleted tinyint unsigned NOT NULL default 0,
276276
277277 -- Length of this revision in bytes
278278 rev_len int unsigned,
279279
280280 -- Key to revision.rev_id
281281 -- This field is used to add support for a tree structure (The Adjacency List Model)
282 - rev_parent_id int unsigned default NULL,
 282+ rev_parent_id int unsigned default NULL
283283
284 - PRIMARY KEY rev_page_id (rev_page, rev_id),
285 - UNIQUE INDEX rev_id (rev_id),
286 - INDEX rev_timestamp (rev_timestamp),
287 - INDEX page_timestamp (rev_page,rev_timestamp),
288 - INDEX user_timestamp (rev_user,rev_timestamp),
289 - INDEX usertext_timestamp (rev_user_text,rev_timestamp)
290 -
291284 ) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;
292285 -- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit
293286
 287+CREATE UNIQUE INDEX rev_page_id ON /*_*/revision (rev_page, rev_id);
 288+CREATE INDEX rev_timestamp ON /*_*/revision (rev_timestamp);
 289+CREATE INDEX page_timestamp ON /*_*/revision (rev_page,rev_timestamp);
 290+CREATE INDEX user_timestamp ON /*_*/revision (rev_user,rev_timestamp);
 291+CREATE INDEX usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
 292+
294293 --
295294 -- Holds text of individual page revisions.
296295 --
——@@ -298,13 +297,13 @@
299298 -- table into the 'text' table to minimize unnecessary churning
300299 -- and downtime. If upgrading, the other fields will be left unused.
301300 --
302 -CREATE TABLE /*$wgDBprefix*/text (
 301+CREATE TABLE /*_*/text (
303302 -- Unique text storage key number.
304303 -- Note that the 'oldid' parameter used in URLs does *not*
305304 -- refer to this number anymore, but to rev_id.
306305 --
307306 -- revision.rev_text_id is a key to this column
308 - old_id int unsigned NOT NULL auto_increment,
 307+ old_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
309308
310309 -- Depending on the contents of the old_flags field, the text
311310 -- may be convenient plain text, or it may be funkily encoded.
——@@ -319,21 +318,19 @@
320319 -- The object either contains multiple versions compressed
321320 -- together to achieve a better compression ratio, or it refers
322321 -- to another row where the text can be found.
323 - old_flags tinyblob NOT NULL,
324 -
325 - PRIMARY KEY old_id (old_id)
326 -
 322+ old_flags tinyblob NOT NULL
327323 ) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=10240;
328324 -- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit
329325
 326+
330327 --
331328 -- Holding area for deleted articles, which may be viewed
332329 -- or restored by admins through the Special:Undelete interface.
333330 -- The fields generally correspond to the page, revision, and text
334331 -- fields, with several caveats.
335332 --
336 -CREATE TABLE /*$wgDBprefix*/archive (
337 - ar_namespace int NOT NULL default '0',
 333+CREATE TABLE /*_*/archive (
 334+ ar_namespace int NOT NULL default 0,
338335 ar_title varchar(255) binary NOT NULL default '',
339336
340337 -- Newly deleted pages will not store text in this table,
——@@ -346,10 +343,10 @@
347344
348345 -- Basic revision stuff...
349346 ar_comment tinyblob NOT NULL,
350 - ar_user int unsigned NOT NULL default '0',
 347+ ar_user int unsigned NOT NULL default 0,
351348 ar_user_text varchar(255) binary NOT NULL,
352349 ar_timestamp binary(14) NOT NULL default '',
353 - ar_minor_edit tinyint NOT NULL default '0',
 350+ ar_minor_edit tinyint NOT NULL default 0,
354351
355352 -- See ar_text note.
356353 ar_flags tinyblob NOT NULL,
——@@ -375,7 +372,7 @@
376373 ar_text_id int unsigned,
377374
378375 -- rev_deleted for archives
379 - ar_deleted tinyint unsigned NOT NULL default '0',
 376+ ar_deleted tinyint unsigned NOT NULL default 0,
380377
381378 -- Length of this revision in bytes
382379 ar_len int unsigned,
——@@ -388,80 +385,77 @@
389386 ar_page_id int unsigned,
390387
391388 -- Original previous revision
392 - ar_parent_id int unsigned default NULL,
393 -
394 - KEY name_title_timestamp (ar_namespace,ar_title,ar_timestamp),
395 - KEY usertext_timestamp (ar_user_text,ar_timestamp)
396 -
 389+ ar_parent_id int unsigned default NULL
397390 ) /*$wgDBTableOptions*/;
398391
 392+CREATE INDEX name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
 393+CREATE INDEX ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
399394
 395+
400396 --
401397 -- Track page-to-page hyperlinks within the wiki.
402398 --
403 -CREATE TABLE /*$wgDBprefix*/pagelinks (
 399+CREATE TABLE /*_*/pagelinks (
404400 -- Key to the page_id of the page containing the link.
405 - pl_from int unsigned NOT NULL default '0',
 401+ pl_from int unsigned NOT NULL default 0,
406402
407403 -- Key to page_namespace/page_title of the target page.
408404 -- The target page may or may not exist, and due to renames
409405 -- and deletions may refer to different page records as time
410406 -- goes by.
411 - pl_namespace int NOT NULL default '0',
412 - pl_title varchar(255) binary NOT NULL default '',
413 -
414 - UNIQUE KEY pl_from (pl_from,pl_namespace,pl_title),
415 - KEY (pl_namespace,pl_title,pl_from)
416 -
 407+ pl_namespace int NOT NULL default 0,
 408+ pl_title varchar(255) binary NOT NULL default ''
417409 ) /*$wgDBTableOptions*/;
418410
 411+CREATE UNIQUE INDEX pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
 412+CREATE INDEX pl_namespace_title ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);
419413
 414+
420415 --
421416 -- Track template inclusions.
422417 --
423 -CREATE TABLE /*$wgDBprefix*/templatelinks (
 418+CREATE TABLE /*_*/templatelinks (
424419 -- Key to the page_id of the page containing the link.
425 - tl_from int unsigned NOT NULL default '0',
 420+ tl_from int unsigned NOT NULL default 0,
426421
427422 -- Key to page_namespace/page_title of the target page.
428423 -- The target page may or may not exist, and due to renames
429424 -- and deletions may refer to different page records as time
430425 -- goes by.
431 - tl_namespace int NOT NULL default '0',
432 - tl_title varchar(255) binary NOT NULL default '',
433 -
434 - UNIQUE KEY tl_from (tl_from,tl_namespace,tl_title),
435 - KEY (tl_namespace,tl_title,tl_from)
436 -
 426+ tl_namespace int NOT NULL default 0,
 427+ tl_title varchar(255) binary NOT NULL default ''
437428 ) /*$wgDBTableOptions*/;
438429
 430+CREATE UNIQUE INDEX tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
 431+CREATE INDEX tl_namespace_title ON /*_*/templatelinks (tl_namespace,tl_title,tl_from);
 432+
 433+
439434 --
440435 -- Track links to images *used inline*
441436 -- We don't distinguish live from broken links here, so
442437 -- they do not need to be changed on upload/removal.
443438 --
444 -CREATE TABLE /*$wgDBprefix*/imagelinks (
 439+CREATE TABLE /*_*/imagelinks (
445440 -- Key to page_id of the page containing the image / media link.
446 - il_from int unsigned NOT NULL default '0',
 441+ il_from int unsigned NOT NULL default 0,
447442
448443 -- Filename of target image.
449444 -- This is also the page_title of the file's description page;
450445 -- all such pages are in namespace 6 (NS_FILE).
451 - il_to varchar(255) binary NOT NULL default '',
452 -
453 - UNIQUE KEY il_from (il_from,il_to),
454 - KEY (il_to,il_from)
455 -
 446+ il_to varchar(255) binary NOT NULL default ''
456447 ) /*$wgDBTableOptions*/;
457448
 449+CREATE UNIQUE INDEX il_from ON /*_*/imagelinks (il_from,il_to);
 450+CREATE INDEX il_to ON /*_*/imagelinks (il_to,il_from);
 451+
 452+
458453 --
459454 -- Track category inclusions *used inline*
460455 -- This tracks a single level of category membership
461456 --
462 -CREATE TABLE /*$wgDBprefix*/categorylinks (
 457+CREATE TABLE /*_*/categorylinks (
463458 -- Key to page_id of the page defined as a category member.
464 - cl_from int unsigned NOT NULL default '0',
 459+ cl_from int unsigned NOT NULL default 0,
465460
466461 -- Name of the category.
467462 -- This is also the page_title of the category's description page;
——@@ -480,26 +474,26 @@
481475
482476 -- This isn't really used at present. Provided for an optional
483477 -- sorting method by approximate addition time.
484 - cl_timestamp timestamp NOT NULL,
485 -
486 - UNIQUE KEY cl_from (cl_from,cl_to),
487 -
488 - -- We always sort within a given category...
489 - KEY cl_sortkey (cl_to,cl_sortkey,cl_from),
490 -
491 - -- Not really used?
492 - KEY cl_timestamp (cl_to,cl_timestamp)
493 -
 478+ cl_timestamp timestamp NOT NULL
494479 ) /*$wgDBTableOptions*/;
495480
 481+CREATE UNIQUE INDEX cl_from ON /*_*/categorylinks (cl_from,cl_to);
 482+
 483+-- We always sort within a given category...
 484+CREATE INDEX cl_sortkey ON /*_*/categorylinks (cl_to,cl_sortkey,cl_from);
 485+
 486+-- Not really used?
 487+CREATE INDEX cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
 488+
 489+
496490 --
497491 -- Track all existing categories. Something is a category if 1) it has an en-
498492 -- try somewhere in categorylinks, or 2) it once did. Categories might not
499493 -- have corresponding pages, so they need to be tracked separately.
500494 --
501 -CREATE TABLE /*$wgDBprefix*/category (
 495+CREATE TABLE /*_*/category (
502496 -- Primary key
503 - cat_id int unsigned NOT NULL auto_increment,
 497+ cat_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
504498
505499 -- Name of the category, in the same form as page_title (with underscores).
506500 -- If there is a category page corresponding to this category, by definition,
——@@ -516,21 +510,21 @@
517511 cat_files int signed NOT NULL default 0,
518512
519513 -- Reserved for future use
520 - cat_hidden tinyint unsigned NOT NULL default 0,
521 -
522 - PRIMARY KEY (cat_id),
523 - UNIQUE KEY (cat_title),
524 -
525 - -- For Special:Mostlinkedcategories
526 - KEY (cat_pages)
 514+ cat_hidden tinyint unsigned NOT NULL default 0
527515 ) /*$wgDBTableOptions*/;
528516
 517+CREATE UNIQUE INDEX cat_title ON /*_*/category (cat_title);
 518+
 519+-- For Special:Mostlinkedcategories
 520+CREATE INDEX cat_pages ON /*_*/category (cat_pages);
 521+
 522+
529523 --
530524 -- Track links to external URLs
531525 --
532 -CREATE TABLE /*$wgDBprefix*/externallinks (
 526+CREATE TABLE /*_*/externallinks (
533527 -- page_id of the referring page
534 - el_from int unsigned NOT NULL default '0',
 528+ el_from int unsigned NOT NULL default 0,
535529
536530 -- The URL
537531 el_to blob NOT NULL,
——@@ -546,50 +540,52 @@
547541 -- which allows for fast searching for all pages under example.com with the
548542 -- clause:
549543 -- WHERE el_index LIKE 'http://com.example.%'
550 - el_index blob NOT NULL,
551 -
552 - KEY (el_from, el_to(40)),
553 - KEY (el_to(60), el_from),
554 - KEY (el_index(60))
 544+ el_index blob NOT NULL
555545 ) /*$wgDBTableOptions*/;
556546
 547+CREATE INDEX el_from ON /*_*/externallinks (el_from, el_to(40));
 548+CREATE INDEX el_to ON /*_*/externallinks (el_to(60), el_from);
 549+CREATE INDEX el_index ON /*_*/externallinks (el_index(60));
 550+
 551+
557552 --
558553 -- Track interlanguage links
559554 --
560 -CREATE TABLE /*$wgDBprefix*/langlinks (
 555+CREATE TABLE /*_*/langlinks (
561556 -- page_id of the referring page
562 - ll_from int unsigned NOT NULL default '0',
 557+ ll_from int unsigned NOT NULL default 0,
563558
564559 -- Language code of the target
565560 ll_lang varbinary(20) NOT NULL default '',
566561
567562 -- Title of the target, including namespace
568 - ll_title varchar(255) binary NOT NULL default '',
569 -
570 - UNIQUE KEY (ll_from, ll_lang),
571 - KEY (ll_lang, ll_title)
 563+ ll_title varchar(255) binary NOT NULL default ''
572564 ) /*$wgDBTableOptions*/;
573565
 566+CREATE UNIQUE INDEX ll_from ON /*_*/langlinks (ll_from, ll_lang);
 567+CREATE INDEX ll_lang_title ON /*_*/langlinks (ll_lang, ll_title);
 568+
 569+
574570 --
575571 -- Contains a single row with some aggregate info
576572 -- on the state of the site.
577573 --
578 -CREATE TABLE /*$wgDBprefix*/site_stats (
 574+CREATE TABLE /*_*/site_stats (
579575 -- The single row should contain 1 here.
580576 ss_row_id int unsigned NOT NULL,
581577
582578 -- Total number of page views, if hit counters are enabled.
583 - ss_total_views bigint unsigned default '0',
 579+ ss_total_views bigint unsigned default 0,
584580
585581 -- Total number of edits performed.
586 - ss_total_edits bigint unsigned default '0',
 582+ ss_total_edits bigint unsigned default 0,
587583
588584 -- An approximate count of pages matching the following criteria:
589585 -- * in namespace 0
590586 -- * not a redirect
591587 -- * contains the text '[['
592588 -- See Article::isCountable() in includes/Article.php
593 - ss_good_articles bigint unsigned default '0',
 589+ ss_good_articles bigint unsigned default 0,
594590
595591 -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except faster
596592 ss_total_pages bigint default '-1',
——@@ -604,11 +600,12 @@
605601 ss_admins int default '-1',
606602
607603 -- Number of images, equivalent to SELECT COUNT(*) FROM image
608 - ss_images int default '0',
 604+ ss_images int default 0
 605+) /*$wgDBTableOptions*/;
609606
610 - UNIQUE KEY ss_row_id (ss_row_id)
 607+-- Pointless index to assuage developer superstitions
 608+CREATE UNIQUE INDEX ss_row_id ON /*_*/site_stats (ss_row_id);
611609
612 -) /*$wgDBTableOptions*/;
613610
614611 --
615612 -- Stores an ID for every time any article is visited;
——@@ -617,7 +614,7 @@
618615 -- in the page table updated for the all articles
619616 -- that have been visited.)
620617 --
621 -CREATE TABLE /*$wgDBprefix*/hitcounter (
 618+CREATE TABLE /*_*/hitcounter (
622619 hc_id int unsigned NOT NULL
623620 ) ENGINE=HEAP MAX_ROWS=25000;
624621
——@@ -626,18 +623,18 @@
627624 -- The internet is full of jerks, alas. Sometimes it's handy
628625 -- to block a vandal or troll account.
629626 --
630 -CREATE TABLE /*$wgDBprefix*/ipblocks (
 627+CREATE TABLE /*_*/ipblocks (
631628 -- Primary key, introduced for privacy.
632 - ipb_id int NOT NULL auto_increment,
 629+ ipb_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
633630
634631 -- Blocked IP address in dotted-quad form or user name.
635632 ipb_address tinyblob NOT NULL,
636633
637634 -- Blocked user ID or 0 for IP blocks.
638 - ipb_user int unsigned NOT NULL default '0',
 635+ ipb_user int unsigned NOT NULL default 0,
639636
640637 -- User ID who made the block.
641 - ipb_by int unsigned NOT NULL default '0',
 638+ ipb_by int unsigned NOT NULL default 0,
642639
643640 -- User name of blocker
644641 ipb_by_text varchar(255) binary NOT NULL default '',
——@@ -679,43 +676,41 @@
680677 ipb_block_email bool NOT NULL default 0,
681678
682679 -- Block allows user to edit their own talk page
683 - ipb_allow_usertalk bool NOT NULL default 0,
684 -
685 - PRIMARY KEY ipb_id (ipb_id),
 680+ ipb_allow_usertalk bool NOT NULL default 0
686681
687 - -- Unique index to support "user already blocked" messages
688 - -- Any new options which prevent collisions should be included
689 - UNIQUE INDEX ipb_address (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only),
690 -
691 - INDEX ipb_user (ipb_user),
692 - INDEX ipb_range (ipb_range_start(8), ipb_range_end(8)),
693 - INDEX ipb_timestamp (ipb_timestamp),
694 - INDEX ipb_expiry (ipb_expiry)
695 -
696682 ) /*$wgDBTableOptions*/;
 683+
 684+-- Unique index to support "user already blocked" messages
 685+-- Any new options which prevent collisions should be included
 686+CREATE UNIQUE INDEX ipb_address ON /*_*/ipblocks (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only);
697687
 688+CREATE INDEX ipb_user ON /*_*/ipblocks (ipb_user);
 689+CREATE INDEX ipb_range ON /*_*/ipblocks (ipb_range_start(8), ipb_range_end(8));
 690+CREATE INDEX ipb_timestamp ON /*_*/ipblocks (ipb_timestamp);
 691+CREATE INDEX ipb_expiry ON /*_*/ipblocks (ipb_expiry);
698692
 693+
699694 --
700695 -- Uploaded images and other files.
701696 --
702 -CREATE TABLE /*$wgDBprefix*/image (
 697+CREATE TABLE /*_*/image (
703698 -- Filename.
704699 -- This is also the title of the associated description page,
705700 -- which will be in namespace 6 (NS_FILE).
706 - img_name varchar(255) binary NOT NULL default '',
 701+ img_name varchar(255) binary NOT NULL default '' primary key,
707702
708703 -- File size in bytes.
709 - img_size int unsigned NOT NULL default '0',
 704+ img_size int unsigned NOT NULL default 0,
710705
711706 -- For images, size in pixels.
712 - img_width int NOT NULL default '0',
713 - img_height int NOT NULL default '0',
 707+ img_width int NOT NULL default 0,
 708+ img_height int NOT NULL default 0,
714709
715710 -- Extracted EXIF metadata stored as a serialized PHP array.
716711 img_metadata mediumblob NOT NULL,
717712
718713 -- For images, bits per pixel if known.
719 - img_bits int NOT NULL default '0',
 714+ img_bits int NOT NULL default 0,
720715
721716 -- Media type as defined by the MEDIATYPE_xxx constants
722717 img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
——@@ -735,34 +730,31 @@
736731 img_description tinyblob NOT NULL,
737732
738733 -- user_id and user_name of uploader.
739 - img_user int unsigned NOT NULL default '0',
 734+ img_user int unsigned NOT NULL default 0,
740735 img_user_text varchar(255) binary NOT NULL,
741736
742737 -- Time of the upload.
743738 img_timestamp varbinary(14) NOT NULL default '',
744739
745740 -- SHA-1 content hash in base-36
746 - img_sha1 varbinary(32) NOT NULL default '',
 741+ img_sha1 varbinary(32) NOT NULL default ''
 742+) /*$wgDBTableOptions*/;
747743
748 - PRIMARY KEY img_name (img_name),
749 -
750 - INDEX img_usertext_timestamp (img_user_text,img_timestamp),
751 - -- Used by Special:Imagelist for sort-by-size
752 - INDEX img_size (img_size),
753 - -- Used by Special:Newimages and Special:Imagelist
754 - INDEX img_timestamp (img_timestamp),
755 - -- Used in API and duplicate search
756 - INDEX img_sha1 (img_sha1)
 744+CREATE INDEX img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
 745+-- Used by Special:Imagelist for sort-by-size
 746+CREATE INDEX img_size ON /*_*/image (img_size);
 747+-- Used by Special:Newimages and Special:Imagelist
 748+CREATE INDEX img_timestamp ON /*_*/image (img_timestamp);
 749+-- Used in API and duplicate search
 750+CREATE INDEX img_sha1 ON /*_*/image (img_sha1);
757751
758752
759 -) /*$wgDBTableOptions*/;
760 -
761753 --
762754 -- Previous revisions of uploaded files.
763755 -- Awkwardly, image rows have to be moved into
764756 -- this table at re-upload time.
765757 --
766 -CREATE TABLE /*$wgDBprefix*/oldimage (
 758+CREATE TABLE /*_*/oldimage (
767759 -- Base filename: key to image.img_name
768760 oi_name varchar(255) binary NOT NULL default '',
769761
——@@ -776,7 +768,7 @@
777769 oi_height int NOT NULL default 0,
778770 oi_bits int NOT NULL default 0,
779771 oi_description tinyblob NOT NULL,
780 - oi_user int unsigned NOT NULL default '0',
 772+ oi_user int unsigned NOT NULL default 0,
781773 oi_user_text varchar(255) binary NOT NULL,
782774 oi_timestamp binary(14) NOT NULL default '',
783775
——@@ -784,23 +776,23 @@
785777 oi_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
786778 oi_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart") NOT NULL default "unknown",
787779 oi_minor_mime varbinary(32) NOT NULL default "unknown",
788 - oi_deleted tinyint unsigned NOT NULL default '0',
789 - oi_sha1 varbinary(32) NOT NULL default '',
790 -
791 - INDEX oi_usertext_timestamp (oi_user_text,oi_timestamp),
792 - INDEX oi_name_timestamp (oi_name,oi_timestamp),
793 - -- oi_archive_name truncated to 14 to avoid key length overflow
794 - INDEX oi_name_archive_name (oi_name,oi_archive_name(14)),
795 - INDEX oi_sha1 (oi_sha1)
796 -
 780+ oi_deleted tinyint unsigned NOT NULL default 0,
 781+ oi_sha1 varbinary(32) NOT NULL default ''
797782 ) /*$wgDBTableOptions*/;
798783
 784+CREATE INDEX oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
 785+CREATE INDEX oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp);
 786+-- oi_archive_name truncated to 14 to avoid key length overflow
 787+CREATE INDEX oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name(14));
 788+CREATE INDEX oi_sha1 ON /*_*/oldimage (oi_sha1);
 789+
 790+
799791 --
800792 -- Record of deleted file data
801793 --
802 -CREATE TABLE /*$wgDBprefix*/filearchive (
 794+CREATE TABLE /*_*/filearchive (
803795 -- Unique row id
804 - fa_id int NOT NULL auto_increment,
 796+ fa_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
805797
806798 -- Original base filename; key to image.img_name, page.page_title, etc
807799 fa_name varchar(255) binary NOT NULL default '',
——@@ -826,80 +818,83 @@
827819 fa_deleted_reason text,
828820
829821 -- Duped fields from image
830 - fa_size int unsigned default '0',
831 - fa_width int default '0',
832 - fa_height int default '0',
 822+ fa_size int unsigned default 0,
 823+ fa_width int default 0,
 824+ fa_height int default 0,
833825 fa_metadata mediumblob,
834 - fa_bits int default '0',
 826+ fa_bits int default 0,
835827 fa_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
836828 fa_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart") default "unknown",
837829 fa_minor_mime varbinary(32) default "unknown",
838830 fa_description tinyblob,
839 - fa_user int unsigned default '0',
 831+ fa_user int unsigned default 0,
840832 fa_user_text varchar(255) binary,
841833 fa_timestamp binary(14) default '',
842834
843835 -- Visibility of deleted revisions, bitfield
844 - fa_deleted tinyint unsigned NOT NULL default '0',
845 -
846 - PRIMARY KEY (fa_id),
847 - INDEX (fa_name, fa_timestamp), -- pick out by image name
848 - INDEX (fa_storage_group, fa_storage_key), -- pick out dupe files
849 - INDEX (fa_deleted_timestamp), -- sort by deletion time
850 - INDEX fa_user_timestamp (fa_user_text,fa_timestamp) -- sort by uploader
851 -
 836+ fa_deleted tinyint unsigned NOT NULL default 0
852837 ) /*$wgDBTableOptions*/;
853838
 839+-- pick out by image name
 840+CREATE INDEX fa_name ON /*_*/filearchive (fa_name, fa_timestamp);
 841+-- pick out dupe files
 842+CREATE INDEX fa_group_key ON /*_*/filearchive (fa_storage_group, fa_storage_key);
 843+-- sort by deletion time
 844+CREATE INDEX fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp);
 845+-- sort by uploader
 846+CREATE INDEX fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);
 847+
 848+
854849 --
855850 -- Primarily a summary table for Special:Recentchanges,
856851 -- this table contains some additional info on edits from
857852 -- the last few days, see Article::editUpdates()
858853 --
859 -CREATE TABLE /*$wgDBprefix*/recentchanges (
860 - rc_id int NOT NULL auto_increment,
 854+CREATE TABLE /*_*/recentchanges (
 855+ rc_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
861856 rc_timestamp varbinary(14) NOT NULL default '',
862857 rc_cur_time varbinary(14) NOT NULL default '',
863858
864859 -- As in revision
865 - rc_user int unsigned NOT NULL default '0',
 860+ rc_user int unsigned NOT NULL default 0,
866861 rc_user_text varchar(255) binary NOT NULL,
867862
868863 -- When pages are renamed, their RC entries do _not_ change.
869 - rc_namespace int NOT NULL default '0',
 864+ rc_namespace int NOT NULL default 0,
870865 rc_title varchar(255) binary NOT NULL default '',
871866
872867 -- as in revision...
873868 rc_comment varchar(255) binary NOT NULL default '',
874 - rc_minor tinyint unsigned NOT NULL default '0',
 869+ rc_minor tinyint unsigned NOT NULL default 0,
875870
876871 -- Edits by user accounts with the 'bot' rights key are
877872 -- marked with a 1 here, and will be hidden from the
878873 -- default view.
879 - rc_bot tinyint unsigned NOT NULL default '0',
 874+ rc_bot tinyint unsigned NOT NULL default 0,
880875
881 - rc_new tinyint unsigned NOT NULL default '0',
 876+ rc_new tinyint unsigned NOT NULL default 0,
882877
883878 -- Key to page_id (was cur_id prior to 1.5).
884879 -- This will keep links working after moves while
885880 -- retaining the at-the-time name in the changes list.
886 - rc_cur_id int unsigned NOT NULL default '0',
 881+ rc_cur_id int unsigned NOT NULL default 0,
887882
888883 -- rev_id of the given revision
889 - rc_this_oldid int unsigned NOT NULL default '0',
 884+ rc_this_oldid int unsigned NOT NULL default 0,
890885
891886 -- rev_id of the prior revision, for generating diff links.
892 - rc_last_oldid int unsigned NOT NULL default '0',
 887+ rc_last_oldid int unsigned NOT NULL default 0,
893888
894889 -- These may no longer be used, with the new move log.
895 - rc_type tinyint unsigned NOT NULL default '0',
896 - rc_moved_to_ns tinyint unsigned NOT NULL default '0',
 890+ rc_type tinyint unsigned NOT NULL default 0,
 891+ rc_moved_to_ns tinyint unsigned NOT NULL default 0,
897892 rc_moved_to_title varchar(255) binary NOT NULL default '',
898893
899894 -- If the Recent Changes Patrol option is enabled,
900895 -- users may mark edits as having been reviewed to
901896 -- remove a warning flag on the RC list.
902897 -- A value of 1 indicates the page has been reviewed.
903 - rc_patrolled tinyint unsigned NOT NULL default '0',
 898+ rc_patrolled tinyint unsigned NOT NULL default 0,
904899
905900 -- Recorded IP address the edit was made from, if the
906901 -- $wgPutIPinRC option is enabled.
——@@ -911,53 +906,52 @@
912907 rc_new_len int,
913908
914909 -- Visibility of recent changes items, bitfield
915 - rc_deleted tinyint unsigned NOT NULL default '0',
 910+ rc_deleted tinyint unsigned NOT NULL default 0,
916911
917912 -- Value corresonding to log_id, specific log entries
918 - rc_logid int unsigned NOT NULL default '0',
 913+ rc_logid int unsigned NOT NULL default 0,
919914 -- Store log type info here, or null
920915 rc_log_type varbinary(255) NULL default NULL,
921916 -- Store log action or null
922917 rc_log_action varbinary(255) NULL default NULL,
923918 -- Log params
924 - rc_params blob NULL,
925 -
926 - PRIMARY KEY rc_id (rc_id),
927 - INDEX rc_timestamp (rc_timestamp),
928 - INDEX rc_namespace_title (rc_namespace, rc_title),
929 - INDEX rc_cur_id (rc_cur_id),
930 - INDEX new_name_timestamp (rc_new,rc_namespace,rc_timestamp),
931 - INDEX rc_ip (rc_ip),
932 - INDEX rc_ns_usertext (rc_namespace, rc_user_text),
933 - INDEX rc_user_text (rc_user_text, rc_timestamp)
934 -
 919+ rc_params blob NULL
935920 ) /*$wgDBTableOptions*/;
936921
937 -CREATE TABLE /*$wgDBprefix*/watchlist (
 922+CREATE INDEX rc_timestamp ON /*_*/recentchanges (rc_timestamp);
 923+CREATE INDEX rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title);
 924+CREATE INDEX rc_cur_id ON /*_*/recentchanges (rc_cur_id);
 925+CREATE INDEX new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp);
 926+CREATE INDEX rc_ip ON /*_*/recentchanges (rc_ip);
 927+CREATE INDEX rc_ns_usertext ON /*_*/recentchanges (rc_namespace, rc_user_text);
 928+CREATE INDEX rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp);
 929+
 930+
 931+CREATE TABLE /*_*/watchlist (
938932 -- Key to user.user_id
939933 wl_user int unsigned NOT NULL,
940934
941935 -- Key to page_namespace/page_title
942936 -- Note that users may watch pages which do not exist yet,
943937 -- or existed in the past but have been deleted.
944 - wl_namespace int NOT NULL default '0',
 938+ wl_namespace int NOT NULL default 0,
945939 wl_title varchar(255) binary NOT NULL default '',
946940
947941 -- Timestamp when user was last sent a notification e-mail;
948942 -- cleared when the user visits the page.
949 - wl_notificationtimestamp varbinary(14),
 943+ wl_notificationtimestamp varbinary(14)
950944
951 - UNIQUE KEY (wl_user, wl_namespace, wl_title),
952 - KEY namespace_title (wl_namespace, wl_title)
953 -
954945 ) /*$wgDBTableOptions*/;
955946
 947+CREATE UNIQUE INDEX wl_user_namespace_title ON /*_*/watchlist (wl_user, wl_namespace, wl_title);
 948+CREATE INDEX namespace_title ON /*_*/watchlist (wl_namespace, wl_title);
956949
 950+
957951 --
958952 -- Used by the math module to keep track
959953 -- of previously-rendered items.
960954 --
961 -CREATE TABLE /*$wgDBprefix*/math (
 955+CREATE TABLE /*_*/math (
962956 -- Binary MD5 hash of the latex fragment, used as an identifier key.
963957 math_inputhash varbinary(16) NOT NULL,
964958
——@@ -972,12 +966,12 @@
973967 math_html text,
974968
975969 -- MathML output from texvc, if any
976 - math_mathml text,
977 -
978 - UNIQUE KEY math_inputhash (math_inputhash)
979 -
 970+ math_mathml text
980971 ) /*$wgDBTableOptions*/;
981972
 973+CREATE UNIQUE INDEX math_inputhash ON /*_*/math (math_inputhash);
 974+
 975+
982976 --
983977 -- When using the default MySQL search backend, page titles
984978 -- and text are munged to strip markup, do Unicode case folding,
——@@ -986,7 +980,7 @@
987981 -- This table must be MyISAM; InnoDB does not support the needed
988982 -- fulltext index.
989983 --
990 -CREATE TABLE /*$wgDBprefix*/searchindex (
 984+CREATE TABLE /*_*/searchindex (
991985 -- Key to page_id
992986 si_page int unsigned NOT NULL,
993987
——@@ -994,18 +988,18 @@
995989 si_title varchar(255) NOT NULL default '',
996990
997991 -- Munged version of body text
998 - si_text mediumtext NOT NULL,
999 -
1000 - UNIQUE KEY (si_page),
1001 - FULLTEXT si_title (si_title),
1002 - FULLTEXT si_text (si_text)
1003 -
 992+ si_text mediumtext NOT NULL
1004993 ) ENGINE=MyISAM;
1005994
 995+CREATE UNIQUE INDEX si_page ON /*_*/searchindex (si_page);
 996+CREATE FULLTEXT INDEX si_title ON /*_*/searchindex (si_title);
 997+CREATE FULLTEXT INDEX si_text ON /*_*/searchindex (si_text);
 998+
 999+
10061000 --
10071001 -- Recognized interwiki link prefixes
10081002 --
1009 -CREATE TABLE /*$wgDBprefix*/interwiki (
 1003+CREATE TABLE /*_*/interwiki (
10101004 -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de")
10111005 iw_prefix varchar(32) NOT NULL,
10121006
——@@ -1019,55 +1013,56 @@
10201014 iw_local bool NOT NULL,
10211015
10221016 -- Boolean value indicating whether interwiki transclusions are allowed.
1023 - iw_trans tinyint NOT NULL default 0,
1024 -
1025 - UNIQUE KEY iw_prefix (iw_prefix)
1026 -
 1017+ iw_trans tinyint NOT NULL default 0
10271018 ) /*$wgDBTableOptions*/;
10281019
 1020+CREATE UNIQUE INDEX iw_prefix ON /*_*/interwiki (iw_prefix);
 1021+
 1022+
10291023 --
10301024 -- Used for caching expensive grouped queries
10311025 --
1032 -CREATE TABLE /*$wgDBprefix*/querycache (
 1026+CREATE TABLE /*_*/querycache (
10331027 -- A key name, generally the base name of of the special page.
10341028 qc_type varbinary(32) NOT NULL,
10351029
10361030 -- Some sort of stored value. Sizes, counts...
1037 - qc_value int unsigned NOT NULL default '0',
 1031+ qc_value int unsigned NOT NULL default 0,
10381032
10391033 -- Target namespace+title
1040 - qc_namespace int NOT NULL default '0',
1041 - qc_title varchar(255) binary NOT NULL default '',
1042 -
1043 - KEY (qc_type,qc_value)
1044 -
 1034+ qc_namespace int NOT NULL default 0,
 1035+ qc_title varchar(255) binary NOT NULL default ''
10451036 ) /*$wgDBTableOptions*/;
10461037
 1038+CREATE INDEX qc_type_value ON /*_*/querycache (qc_type,qc_value);
 1039+
 1040+
10471041 --
10481042 -- For a few generic cache operations if not using Memcached
10491043 --
1050 -CREATE TABLE /*$wgDBprefix*/objectcache (
1051 - keyname varbinary(255) NOT NULL default '',
 1044+CREATE TABLE /*_*/objectcache (
 1045+ keyname varbinary(255) NOT NULL default '' primary key,
10521046 value mediumblob,
1053 - exptime datetime,
1054 - PRIMARY KEY (keyname),
1055 - KEY (exptime)
1056 -
 1047+ exptime datetime
10571048 ) /*$wgDBTableOptions*/;
 1049+CREATE INDEX oc_exptime ON /*_*/objectcache (exptime);
10581050
 1051+
10591052 --
10601053 -- Cache of interwiki transclusion
10611054 --
1062 -CREATE TABLE /*$wgDBprefix*/transcache (
 1055+CREATE TABLE /*_*/transcache (
10631056 tc_url varbinary(255) NOT NULL,
10641057 tc_contents text,
1065 - tc_time int NOT NULL,
1066 - UNIQUE INDEX tc_url_idx (tc_url)
 1058+ tc_time int NOT NULL
10671059 ) /*$wgDBTableOptions*/;
10681060
1069 -CREATE TABLE /*$wgDBprefix*/logging (
 1061+CREATE UNIQUE INDEX tc_url_idx ON /*_*/transcache (tc_url);
 1062+
 1063+
 1064+CREATE TABLE /*_*/logging (
10701065 -- Log ID, for referring to this specific log entry, probably for deletion and such.
1071 - log_id int unsigned NOT NULL auto_increment,
 1066+ log_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
10721067
10731068 -- Symbolic keys for the general log type and the action type
10741069 -- within the log. The output format will be controlled by the
——@@ -1093,32 +1088,29 @@
10941089 log_params blob NOT NULL,
10951090
10961091 -- rev_deleted for logs
1097 - log_deleted tinyint unsigned NOT NULL default '0',
 1092+ log_deleted tinyint unsigned NOT NULL default 0
 1093+) /*$wgDBTableOptions*/;
10981094
1099 - PRIMARY KEY log_id (log_id),
1100 - KEY type_time (log_type, log_timestamp),
1101 - KEY user_time (log_user, log_timestamp),
1102 - KEY page_time (log_namespace, log_title, log_timestamp),
1103 - KEY times (log_timestamp)
 1095+CREATE INDEX type_time ON /*_*/logging (log_type, log_timestamp);
 1096+CREATE INDEX user_time ON /*_*/logging (log_user, log_timestamp);
 1097+CREATE INDEX page_time ON /*_*/logging (log_namespace, log_title, log_timestamp);
 1098+CREATE INDEX times ON /*_*/logging (log_timestamp);
11041099
1105 -) /*$wgDBTableOptions*/;
11061100
1107 -CREATE TABLE /*$wgDBprefix*/trackbacks (
1108 - tb_id int auto_increment,
1109 - tb_page int REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
 1101+CREATE TABLE /*_*/trackbacks (
 1102+ tb_id int PRIMARY KEY AUTO_INCREMENT,
 1103+ tb_page int REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
11101104 tb_title varchar(255) NOT NULL,
11111105 tb_url blob NOT NULL,
11121106 tb_ex text,
1113 - tb_name varchar(255),
1114 -
1115 - PRIMARY KEY (tb_id),
1116 - INDEX (tb_page)
 1107+ tb_name varchar(255)
11171108 ) /*$wgDBTableOptions*/;
 1109+CREATE INDEX tb_page ON /*_*/trackbacks (tb_page);
11181110
11191111
11201112 -- Jobs performed by parallel apache threads or a command-line daemon
1121 -CREATE TABLE /*$wgDBprefix*/job (
1122 - job_id int unsigned NOT NULL auto_increment,
 1113+CREATE TABLE /*_*/job (
 1114+ job_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
11231115
11241116 -- Command name
11251117 -- Limited to 60 to prevent key length overflow
——@@ -1130,68 +1122,66 @@
11311123 job_title varchar(255) binary NOT NULL,
11321124
11331125 -- Any other parameters to the command
1134 - -- Presently unused, format undefined
1135 - job_params blob NOT NULL,
1136 -
1137 - PRIMARY KEY job_id (job_id),
1138 - KEY (job_cmd, job_namespace, job_title)
 1126+ -- Stored as a PHP serialized array, or an empty string if there are no parameters
 1127+ job_params blob NOT NULL
11391128 ) /*$wgDBTableOptions*/;
11401129
 1130+CREATE INDEX job_cmd_namespace_title ON /*_*/job (job_cmd, job_namespace, job_title);
11411131
1142 -CREATE TABLE /*$wgDBprefix*/querycache_info (
11431132
 1133+-- Details of updates to cached special pages
 1134+CREATE TABLE /*_*/querycache_info (
11441135 -- Special page name
11451136 -- Corresponds to a qc_type value
11461137 qci_type varbinary(32) NOT NULL default '',
11471138
11481139 -- Timestamp of last update
1149 - qci_timestamp binary(14) NOT NULL default '19700101000000',
 1140+ qci_timestamp binary(14) NOT NULL default '19700101000000'
 1141+) /*$wgDBTableOptions*/;
11501142
1151 - UNIQUE KEY ( qci_type )
 1143+CREATE UNIQUE INDEX qci_type ON /*_*/querycache_info (qci_type);
11521144
1153 -) /*$wgDBTableOptions*/;
11541145
11551146 -- For each redirect, this table contains exactly one row defining its target
1156 -CREATE TABLE /*$wgDBprefix*/redirect (
 1147+CREATE TABLE /*_*/redirect (
11571148 -- Key to the page_id of the redirect page
1158 - rd_from int unsigned NOT NULL default '0',
 1149+ rd_from int unsigned NOT NULL default 0 primary key,
11591150
11601151 -- Key to page_namespace/page_title of the target page.
11611152 -- The target page may or may not exist, and due to renames
11621153 -- and deletions may refer to different page records as time
11631154 -- goes by.
1164 - rd_namespace int NOT NULL default '0',
1165 - rd_title varchar(255) binary NOT NULL default '',
1166 -
1167 - PRIMARY KEY rd_from (rd_from),
1168 - KEY rd_ns_title (rd_namespace,rd_title,rd_from)
 1155+ rd_namespace int NOT NULL default 0,
 1156+ rd_title varchar(255) binary NOT NULL default ''
11691157 ) /*$wgDBTableOptions*/;
11701158
 1159+CREATE INDEX rd_ns_title ON /*_*/redirect (rd_namespace,rd_title,rd_from);
 1160+
 1161+
11711162 -- Used for caching expensive grouped queries that need two links (for example double-redirects)
1172 -CREATE TABLE /*$wgDBprefix*/querycachetwo (
 1163+CREATE TABLE /*_*/querycachetwo (
11731164 -- A key name, generally the base name of of the special page.
11741165 qcc_type varbinary(32) NOT NULL,
11751166
11761167 -- Some sort of stored value. Sizes, counts...
1177 - qcc_value int unsigned NOT NULL default '0',
 1168+ qcc_value int unsigned NOT NULL default 0,
11781169
11791170 -- Target namespace+title
1180 - qcc_namespace int NOT NULL default '0',
 1171+ qcc_namespace int NOT NULL default 0,
11811172 qcc_title varchar(255) binary NOT NULL default '',
11821173
11831174 -- Target namespace+title2
1184 - qcc_namespacetwo int NOT NULL default '0',
1185 - qcc_titletwo varchar(255) binary NOT NULL default '',
 1175+ qcc_namespacetwo int NOT NULL default 0,
 1176+ qcc_titletwo varchar(255) binary NOT NULL default ''
 1177+) /*$wgDBTableOptions*/;
11861178
1187 - KEY qcc_type (qcc_type,qcc_value),
1188 - KEY qcc_title (qcc_type,qcc_namespace,qcc_title),
1189 - KEY qcc_titletwo (qcc_type,qcc_namespacetwo,qcc_titletwo)
 1179+CREATE INDEX qcc_type ON /*_*/querycachetwo (qcc_type,qcc_value);
 1180+CREATE INDEX qcc_title ON /*_*/querycachetwo (qcc_type,qcc_namespace,qcc_title);
 1181+CREATE INDEX qcc_titletwo ON /*_*/querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
11901182
1191 -) /*$wgDBTableOptions*/;
11921183
11931184 -- Used for storing page restrictions (i.e. protection levels)
1194 -CREATE TABLE /*$wgDBprefix*/page_restrictions (
 1185+CREATE TABLE /*_*/page_restrictions (
11951186 -- Page to apply restrictions to (Foreign Key to page).
11961187 pr_page int NOT NULL,
11971188 -- The protection type (edit, move, etc)
——@@ -1205,42 +1195,43 @@
12061196 -- Field for time-limited protection.
12071197 pr_expiry varbinary(14) NULL,
12081198 -- Field for an ID for this restrictions row (sort-key for Special:ProtectedPages)
1209 - pr_id int unsigned NOT NULL auto_increment,
 1199+ pr_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT
 1200+) /*$wgDBTableOptions*/;
12101201
1211 - PRIMARY KEY pr_pagetype (pr_page,pr_type),
 1202+CREATE UNIQUE INDEX pr_pagetype ON /*_*/page_restrictions (pr_page,pr_type);
 1203+CREATE UNIQUE INDEX pr_typelevel ON /*_*/page_restrictions (pr_type,pr_level);
 1204+CREATE UNIQUE INDEX pr_level ON /*_*/page_restrictions (pr_level);
 1205+CREATE UNIQUE INDEX pr_cascade ON /*_*/page_restrictions (pr_cascade);
12121206
1213 - UNIQUE KEY pr_id (pr_id),
1214 - KEY pr_typelevel (pr_type,pr_level),
1215 - KEY pr_level (pr_level),
1216 - KEY pr_cascade (pr_cascade)
1217 -) /*$wgDBTableOptions*/;
12181207
12191208 -- Protected titles - nonexistent pages that have been protected
1220 -CREATE TABLE /*$wgDBprefix*/protected_titles (
 1209+CREATE TABLE /*_*/protected_titles (
12211210 pt_namespace int NOT NULL,
12221211 pt_title varchar(255) binary NOT NULL,
12231212 pt_user int unsigned NOT NULL,
12241213 pt_reason tinyblob,
12251214 pt_timestamp binary(14) NOT NULL,
12261215 pt_expiry varbinary(14) NOT NULL default '',
1227 - pt_create_perm varbinary(60) NOT NULL,
1228 - PRIMARY KEY (pt_namespace,pt_title),
1229 - KEY pt_timestamp (pt_timestamp)
 1216+ pt_create_perm varbinary(60) NOT NULL
12301217 ) /*$wgDBTableOptions*/;
12311218
 1219+CREATE UNIQUE INDEX pt_namespace_title ON /*_*/protected_titles (pt_namespace,pt_title);
 1220+CREATE INDEX pt_timestamp ON /*_*/protected_titles (pt_timestamp);
 1221+
 1222+
12321223 -- Name/value pairs indexed by page_id
1233 -CREATE TABLE /*$wgDBprefix*/page_props (
 1224+CREATE TABLE /*_*/page_props (
12341225 pp_page int NOT NULL,
12351226 pp_propname varbinary(60) NOT NULL,
1236 - pp_value blob NOT NULL,
1237 -
1238 - PRIMARY KEY (pp_page,pp_propname)
 1227+ pp_value blob NOT NULL
12391228 ) /*$wgDBTableOptions*/;
12401229
 1230+CREATE UNIQUE INDEX pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
 1231+
 1232+
12411233 -- A table to log updates, one text key row per update.
1242 -CREATE TABLE /*$wgDBprefix*/updatelog (
1243 - ul_key varchar(255) NOT NULL,
1244 - PRIMARY KEY (ul_key)
 1234+CREATE TABLE /*_*/updatelog (
 1235+ ul_key varchar(255) NOT NULL primary key
12451236 ) /*$wgDBTableOptions*/;
12461237
12471238 -- vim: sw=2 sts=2 et
Index: trunk/phase3/includes/SiteStats.php
——@@ -222,7 +222,7 @@
223223
224224 if ( $updates ) {
225225 $site_stats = $dbw->tableName( 'site_stats' );
226 - $sql = $dbw->limitResultForUpdate("UPDATE $site_stats SET $updates", 1);
 226+ $sql = "UPDATE $site_stats SET $updates";
227227
228228 # Need a separate transaction because this a global lock
229229 $dbw->begin();
——@@ -240,7 +240,7 @@
241241 __METHOD__ );
242242 $dbw->update( 'site_stats',
243243 array( 'ss_active_users' => intval($activeUsers) ),
244 - array( 'ss_row_id' => 1 ), __METHOD__, array( 'LIMIT' => 1 )
 244+ array( 'ss_row_id' => 1 ), __METHOD__
245245 );
246246 }
247247 }
Index: trunk/phase3/includes/db/Database.php
——@@ -570,7 +570,7 @@
571571 $ret = $this->doQuery( $commentedSql );
572572
573573 # Try reconnecting if the connection was lost
574 - if ( false === $ret && ( $this->lastErrno() == 2013 || $this->lastErrno() == 2006 ) ) {
 574+ if ( false === $ret && $this->wasErrorReissuable() ) {
575575 # Transaction is gone, like it or not
576576 $this->mTrxLevel = 0;
577577 wfDebug( "Connection lost, reconnecting...\n" );
——@@ -1817,6 +1817,14 @@
18181818 }
18191819
18201820 /**
 1821+ * Determines if the last query error was something that should be dealt
 1822+ * with by pinging the connection and reissuing the query
 1823+ */
 1824+ function wasErrorReissuable() {
 1825+ return $this->lastErrno() == 2013 || $this->lastErrno() == 2006;
 1826+ }
 1827+
 1828+ /**
18211829 * Perform a deadlock-prone transaction.
18221830 *
18231831 * This function invokes a callback function to perform a set of write
Index: trunk/phase3/includes/db/DatabaseSqlite.php
——@@ -15,6 +15,7 @@
1616 var $mAffectedRows;
1717 var $mLastResult;
1818 var $mDatabaseFile;
 19+ var $mName;
1920
2021 /**
2122 * Constructor
——@@ -26,6 +27,7 @@
2728 $this->mFailFunction = $failFunction;
2829 $this->mFlags = $flags;
2930 $this->mDatabaseFile = "$wgSQLiteDataDir/$dbName.sqlite";
 31+ $this->mName = $dbName;
3032 $this->open($server, $user, $password, $dbName);
3133 }
3234
——@@ -89,8 +91,9 @@
9092 */
9193 function doQuery($sql) {
9294 $res = $this->mConn->query($sql);
93 - if ($res === false) $this->reportQueryError($this->lastError(),$this->lastErrno(),$sql,__FUNCTION__);
94 - else {
 95+ if ($res === false) {
 96+ return false;
 97+ } else {
9598 $r = $res instanceof ResultWrapper ? $res->result : $res;
9699 $this->mAffectedRows = $r->rowCount();
97100 $res = new ResultWrapper($this,$r->fetchAll());
——@@ -173,8 +176,12 @@
174177 }
175178
176179 function lastErrno() {
177 - if (!is_object($this->mConn)) return "Cannot return last error, no db connection";
178 - return $this->mConn->errorCode();
 180+ if (!is_object($this->mConn)) {
 181+ return "Cannot return last error, no db connection";
 182+ } else {
 183+ $info = $this->mConn->errorInfo();
 184+ return $info[1];
 185+ }
179186 }
180187
181188 function affectedRows() {
——@@ -183,14 +190,43 @@
184191
185192 /**
186193 * Returns information about an index
 194+ * Returns false if the index does not exist
187195 * - if errors are explicitly ignored, returns NULL on failure
188196 */
189197 function indexInfo($table, $index, $fname = 'Database::indexExists') {
190 - return false;
 198+ $sql = 'PRAGMA index_info(' . $this->addQuotes( $index ) . ')';
 199+ $res = $this->query( $sql, $fname );
 200+ if ( !$res ) {
 201+ return null;
 202+ }
 203+ if ( $res->numRows() == 0 ) {
 204+ return false;
 205+ }
 206+ $info = array();
 207+ foreach ( $res as $row ) {
 208+ $info[] = $row->name;
 209+ }
 210+ return $info;
191211 }
192212
193213 function indexUnique($table, $index, $fname = 'Database::indexUnique') {
194 - return false;
 214+ $row = $this->selectRow( 'sqlite_master', '*',
 215+ array(
 216+ 'type' => 'index',
 217+ 'name' => $index,
 218+ ), $fname );
 219+ if ( !$row || !isset( $row->sql ) ) {
 220+ return null;
 221+ }
 222+
 223+ // $row->sql will be of the form CREATE [UNIQUE] INDEX ...
 224+ $indexPos = strpos( $row->sql, 'INDEX' );
 225+ if ( $indexPos === false ) {
 226+ return null;
 227+ }
 228+ $firstPart = substr( $row->sql, 0, $indexPos );
 229+ $options = explode( ' ', $firstPart );
 230+ return in_array( 'UNIQUE', $options );
195231 }
196232
197233 /**
——@@ -228,7 +264,10 @@
229265 return '';
230266 }
231267
232 - # Returns the size of a text field, or -1 for "unlimited"
 268+ /**
 269+ * Returns the size of a text field, or -1 for "unlimited"
 270+ * In SQLite this is SQLITE_MAX_LENGTH, by default 1GB. No way to query it though.
 271+ */
233272 function textFieldSize($table, $field) {
234273 return -1;
235274 }
——@@ -252,6 +291,10 @@
253292 return $this->lastErrno() == SQLITE_BUSY;
254293 }
255294
 295+ function wasErrorReissuable() {
 296+ return $this->lastErrno() == SQLITE_SCHEMA;
 297+ }
 298+
256299 /**
257300 * @return string wikitext of a link to the server software's web site
258301 */
——@@ -265,17 +308,32 @@
266309 function getServerVersion() {
267310 global $wgContLang;
268311 $ver = $this->mConn->getAttribute(PDO::ATTR_SERVER_VERSION);
269 - $size = $wgContLang->formatSize(filesize($this->mDatabaseFile));
270 - $file = basename($this->mDatabaseFile);
271 - return $ver." ($file: $size)";
 312+ return $ver;
272313 }
273314
274315 /**
275316 * Query whether a given column exists in the mediawiki schema
276317 */
277 - function fieldExists($table, $field) { return true; }
 318+ function fieldExists($table, $field) {
 319+ $info = $this->fieldInfo( $table, $field );
 320+ return (bool)$info;
 321+ }
278322
279 - function fieldInfo($table, $field) { return SQLiteField::fromText($this, $table, $field); }
 323+ /**
 324+ * Get information about a given field
 325+ * Returns false if the field does not exist.
 326+ */
 327+ function fieldInfo($table, $field) {
 328+ $tableName = $this->tableName( $table );
 329+ $sql = 'PRAGMA table_info(' . $this->addQuotes( $tableName ) . ')';
 330+ $res = $this->query( $sql, __METHOD__ );
 331+ foreach ( $res as $row ) {
 332+ if ( $row->name == $field ) {
 333+ return new SQLiteField( $row, $tableName );
 334+ }
 335+ }
 336+ return false;
 337+ }
280338
281339 function begin() {
282340 if ($this->mTrxLevel == 1) $this->commit();
——@@ -296,7 +354,7 @@
297355 }
298356
299357 function limitResultForUpdate($sql, $num) {
300 - return $sql;
 358+ return $this->limitResult( $sql, $num );
301359 }
302360
303361 function strencode($s) {
——@@ -325,17 +383,25 @@
326384 function quote_ident($s) { return $s; }
327385
328386 /**
329 - * For now, does nothing
 387+ * Not possible in SQLite
 388+ * We have ATTACH_DATABASE but that requires database selectors before the
 389+ * table names and in any case is really a different concept to MySQL's USE
330390 */
331 - function selectDB($db) { return true; }
 391+ function selectDB($db) {
 392+ if ( $db != $this->mName ) {
 393+ throw new MWException( 'selectDB is not implemented in SQLite' );
 394+ }
 395+ }
332396
333397 /**
334398 * not done
335399 */
336400 public function setTimeout($timeout) { return; }
337401
 402+ /**
 403+ * No-op for a non-networked database
 404+ */
338405 function ping() {
339 - wfDebug("Function ping() not written for SQLite yet");
340406 return true;
341407 }
342408
——@@ -353,39 +419,16 @@
354420 public function setup_database() {
355421 global $IP,$wgSQLiteDataDir,$wgDBTableOptions;
356422 $wgDBTableOptions = '';
357 - $mysql_tmpl = "$IP/maintenance/tables.sql";
358 - $mysql_iw = "$IP/maintenance/interwiki.sql";
359 - $sqlite_tmpl = "$IP/maintenance/sqlite/tables.sql";
360423
361 - # Make an SQLite template file if it doesn't exist (based on the same one MySQL uses to create a new wiki db)
362 - if (!file_exists($sqlite_tmpl)) {
363 - $sql = file_get_contents($mysql_tmpl);
364 - $sql = preg_replace('/^\s*--.*?$/m','',$sql); # strip comments
365 - $sql = preg_replace('/^\s*(UNIQUE)?\s*(PRIMARY)?\s*KEY.+?$/m','',$sql);
366 - $sql = preg_replace('/^\s*(UNIQUE )?INDEX.+?$/m','',$sql); # These indexes should be created with a CREATE INDEX query
367 - $sql = preg_replace('/^\s*FULLTEXT.+?$/m','',$sql); # Full text indexes
368 - $sql = preg_replace('/ENUM\(.+?\)/','TEXT',$sql); # Make ENUM's into TEXT's
369 - $sql = preg_replace('/binary\(\d+\)/','BLOB',$sql);
370 - $sql = preg_replace('/(TYPE|MAX_ROWS|AVG_ROW_LENGTH)=\w+/','',$sql);
371 - $sql = preg_replace('/,\s*\)/s',')',$sql); # removing previous items may leave a trailing comma
372 - $sql = str_replace('binary','',$sql);
373 - $sql = str_replace('auto_increment','PRIMARY KEY AUTOINCREMENT',$sql);
374 - $sql = str_replace(' unsigned','',$sql);
375 - $sql = str_replace(' int ',' INTEGER ',$sql);
376 - $sql = str_replace('NOT NULL','',$sql);
377 -
378 - # Tidy up and write file
379 - $sql = preg_replace('/^\s*^/m','',$sql); # Remove empty lines
380 - $sql = preg_replace('/;$/m',";\n",$sql); # Separate each statement with an empty line
381 - file_put_contents($sqlite_tmpl,$sql);
 424+ # Process common MySQL/SQLite table definitions
 425+ $err = $this->sourceFile( "$IP/maintenance/tables.sql" );
 426+ if ($err !== true) {
 427+ $this->reportQueryError($err,0,$sql,__FUNCTION__);
 428+ exit( 1 );
382429 }
383430
384 - # Parse the SQLite template replacing inline variables such as /*$wgDBprefix*/
385 - $err = $this->sourceFile($sqlite_tmpl);
386 - if ($err !== true) $this->reportQueryError($err,0,$sql,__FUNCTION__);
387 -
388431 # Use DatabasePostgres's code to populate interwiki from MySQL template
389 - $f = fopen($mysql_iw,'r');
 432+ $f = fopen("$IP/maintenance/interwiki.sql",'r');
390433 if ($f == false) dieout("<li>Could not find the interwiki.sql file");
391434 $sql = "INSERT INTO interwiki(iw_prefix,iw_url,iw_local) VALUES ";
392435 while (!feof($f)) {
——@@ -418,22 +461,80 @@
419462 $function = array_shift( $args );
420463 return call_user_func_array( $function, $args );
421464 }
422 -}
423465
 466+ protected function replaceVars( $s ) {
 467+ $s = parent::replaceVars( $s );
 468+ if ( preg_match( '/^\s*CREATE TABLE/i', $s ) ) {
 469+ // CREATE TABLE hacks to allow schema file sharing with MySQL
 470+
 471+ // binary/varbinary column type -> blob
 472+ $s = preg_replace( '/\b(var)?binary(\(\d+\))/i', 'blob\1', $s );
 473+ // no such thing as unsigned
 474+ $s = preg_replace( '/\bunsigned\b/i', '', $s );
 475+ // INT -> INTEGER for primary keys
 476+ $s = preg_replacE( '/\bint\b/i', 'integer', $s );
 477+ // No ENUM type
 478+ $s = preg_replace( '/enum\([^)]*\)/i', 'blob', $s );
 479+ // binary collation type -> nothing
 480+ $s = preg_replace( '/\bbinary\b/i', '', $s );
 481+ // auto_increment -> autoincrement
 482+ $s = preg_replace( '/\bauto_increment\b/i', 'autoincrement', $s );
 483+ // No explicit options
 484+ $s = preg_replace( '/\)[^)]*$/', ')', $s );
 485+ } elseif ( preg_match( '/^\s*CREATE (\s*(?:UNIQUE|FULLTEXT)\s+)?INDEX/i', $s ) ) {
 486+ // No truncated indexes
 487+ $s = preg_replace( '/\(\d+\)/', '', $s );
 488+ // No FULLTEXT
 489+ $s = preg_replace( '/\bfulltext\b/i', '', $s );
 490+ }
 491+ return $s;
 492+ }
 493+
 494+} // end DatabaseSqlite class
 495+
424496 /**
425497 * @ingroup Database
426498 */
427 -class SQLiteField extends MySQLField {
 499+class SQLiteField {
 500+ private $info, $tableName;
 501+ function __construct( $info, $tableName ) {
 502+ $this->info = $info;
 503+ $this->tableName = $tableName;
 504+ }
428505
429 - function __construct() {
 506+ function name() {
 507+ return $this->info->name;
430508 }
431509
432 - static function fromText($db, $table, $field) {
433 - $n = new SQLiteField;
434 - $n->name = $field;
435 - $n->tablename = $table;
436 - return $n;
 510+ function tableName() {
 511+ return $this->tableName;
437512 }
438513
439 -} // end DatabaseSqlite class
 514+ function defaultValue() {
 515+ if ( is_string( $this->info->dflt_value ) ) {
 516+ // Typically quoted
 517+ if ( preg_match( '/^\'(.*)\'$', $this->info->dflt_value ) ) {
 518+ return str_replace( "''", "'", $this->info->dflt_value );
 519+ }
 520+ }
 521+ return $this->info->dflt_value;
 522+ }
440523
 524+ function maxLength() {
 525+ return -1;
 526+ }
 527+
 528+ function nullable() {
 529+ // SQLite dynamic types are always nullable
 530+ return true;
 531+ }
 532+
 533+ # isKey(), isMultipleKey() not implemented, MySQL-specific concept.
 534+ # Suggest removal from base class [TS]
 535+
 536+ function type() {
 537+ return $this->info->type;
 538+ }
 539+
 540+} // end SQLiteField
 541+
Index: trunk/phase3/includes/Exception.php
——@@ -161,23 +161,26 @@
162162 if( $hookResult = $this->runHooks( get_class( $this ) . "Raw" ) ) {
163163 die( $hookResult );
164164 }
165 - echo $this->htmlHeader();
166 - echo $this->getHTML();
167 - echo $this->htmlFooter();
 165+ if ( defined( 'MEDIAWIKI_INSTALL' ) ) {
 166+ echo $this->getHTML();
 167+ } else {
 168+ echo $this->htmlHeader();
 169+ echo $this->getHTML();
 170+ echo $this->htmlFooter();
 171+ }
168172 }
169173 }
170174
171175 /**
172176 * Output a report about the exception and takes care of formatting.
173 - * It will be either HTML or plain text based on $wgCommandLineMode.
 177+ * It will be either HTML or plain text based on isCommandLine().
174178 */
175179 function report() {
176 - global $wgCommandLineMode;
177180 $log = $this->getLogMessage();
178181 if ( $log ) {
179182 wfDebugLog( 'exception', $log );
180183 }
181 - if ( $wgCommandLineMode ) {
 184+ if ( self::isCommandLine() ) {
182185 wfPrintError( $this->getText() );
183186 } else {
184187 $this->reportHTML();
——@@ -214,6 +217,10 @@
215218 function htmlFooter() {
216219 echo "</body></html>";
217220 }
 221+
 222+ static function isCommandLine() {
 223+ return !empty( $GLOBALS['wgCommandLineMode'] ) && !defined( 'MEDIAWIKI_INSTALL' );
 224+ }
218225 }
219226
220227 /**
——@@ -264,6 +271,7 @@
265272 * Report an exception to the user
266273 */
267274 function wfReportException( Exception $e ) {
 275+ $cmdLine = MWException::isCommandLine();
268276 if ( $e instanceof MWException ) {
269277 try {
270278 $e->report();
——@@ -276,7 +284,7 @@
277285 "\n\nException caught inside exception handler: " .
278286 $e2->__toString() . "\n";
279287
280 - if ( !empty( $GLOBALS['wgCommandLineMode'] ) ) {
 288+ if ( $cmdLine ) {
281289 wfPrintError( $message );
282290 } else {
283291 echo nl2br( htmlspecialchars( $message ) ). "\n";
——@@ -288,7 +296,7 @@
289297 if ( $GLOBALS['wgShowExceptionDetails'] ) {
290298 $message .= "\n" . $e->getTraceAsString() ."\n";
291299 }
292 - if ( !empty( $GLOBALS['wgCommandLineMode'] ) ) {
 300+ if ( $cmdLine ) {
293301 wfPrintError( $message );
294302 } else {
295303 echo nl2br( htmlspecialchars( $message ) ). "\n";
——@@ -298,7 +306,7 @@
299307
300308 /**
301309 * Print a message, if possible to STDERR.
302 - * Use this in command line mode only (see wgCommandLineMode)
 310+ * Use this in command line mode only (see isCommandLine)
303311 */
304312 function wfPrintError( $message ) {
305313 #NOTE: STDERR may not be available, especially if php-cgi is used from the command line (bug #15602).
Index: trunk/phase3/config/index.php
——@@ -47,11 +47,13 @@
4848 require_once( "$IP/includes/ProfilerStub.php" );
4949 require_once( "$IP/includes/GlobalFunctions.php" );
5050 require_once( "$IP/includes/Hooks.php" );
 51+require_once( "$IP/includes/Exception.php" );
5152
5253 # If we get an exception, the user needs to know
5354 # all the details
5455 $wgShowExceptionDetails = true;
55 -
 56+$wgShowSQLErrors = true;
 57+wfInstallExceptionHandler();
5658 ## Databases we support:
5759
5860 $ourdb = array();

Follow-up revisions

RevisionCommit summaryAuthorDate
r45819Attempt at cleaning up r45764, r45769 (renamed indices for SQLite compat):...catrope23:02, 16 January 2009
r45894Partial redo of r45819:...catrope14:12, 19 January 2009
r47250Fix r45764, which made a whole bunch of random indices UNIQUE for no apparent...werdna06:10, 14 February 2009
r47889* Removed force index broken by r45764; shouldn't be needed anyway...aaron11:58, 28 February 2009

Comments

#Comment by Tim Starling (talk | contribs)   07:04, 15 January 2009

You'll note that I resisted the temptation and did not refactor anything. Well, except for tables.sql. But it's pretty close to minimal for SQLite support that's not totally broken.

#Comment by Catrope (talk | contribs)   09:49, 15 January 2009

Note that maintenance/tables.sql (the MySQL tables.sql) was changed to be SQLLite-compatible. This means the index names change for MySQL, which means a lot of hassle with changing USE INDEX clauses all over the place and creating a maintenance script to apply the index renames to existing MySQL installs (this commit does neither). While the idea of a shared MySQL/SQLLite tables.sql is nice, it's too intrusive on MySQL IMO.

#Comment by Tim Starling (talk | contribs)   12:57, 15 January 2009

I'm not going to keep releasing MediaWiki with broken DBMSes. The schemas all need to be merged, one way or another, and independent of that, the index names need to be unique.

#Comment by Catrope (talk | contribs)   13:00, 15 January 2009

OK, but that still means that index references in the code have to be changed, and that a migration script needs to be written.

#Comment by Tim Starling (talk | contribs)   13:14, 15 January 2009

Yes. Or the index name be made DBMS-dependent. Either way, the schema can stay merged.

#Comment by Catrope (talk | contribs)   13:18, 15 January 2009

Making the index name DBMS-dependent doesn't make much sense considering this commit seems to strive to make them DBMS-independent.

The point here is that this commit is broken because it doesn't change USE INDEX usage in the code and doesn't add a maintenance script to apply the changes to older wikis. This means it's essentially impossible to install a working MW from this revision and up.

#Comment by Tim Starling (talk | contribs)   14:22, 15 January 2009

Fixed in r45769. It was on my todo list, my apologies for forgetting it.

#Comment by Werdna (talk | contribs)   06:10, 14 February 2009 
+CREATE UNIQUE INDEX pr_typelevel ON /*_*/page_restrictions (pr_type,pr_level);
+CREATE UNIQUE INDEX pr_level ON /*_*/page_restrictions (pr_level);
+CREATE UNIQUE INDEX pr_cascade ON /*_*/page_restrictions (pr_cascade);

Broke page protection on new wikis, fixed in r47250.

#Comment by Aaron Schulz (talk | contribs)   19:50, 14 February 2009

Note that nothing can reference the the rev_id INDEX without breaking newer installs, since it is now the PRIMARY and there is no index named "rev_id". Referencing "PRIMARY" for the rev id index breaks newer installs (like WMF sites) since on them, the PRIMARY is still rev_page,rev_id.

#Comment by Catrope (talk | contribs)   19:54, 14 February 2009

That means the same is true for PRIMARY (renamed to rev_page), right? (Although it'd probably fail in more interesting ways.)

Status & tagging log