2 -- patch-actor-table.sql
4 -- T167246. Add an `actor` table and various columns (and temporary tables) to reference it.
5 -- Sigh, sqlite, such trouble just to change the default value of a column.
7 CREATE TABLE /*_*/actor (
8 actor_id
bigint unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
9 actor_user
int unsigned
,
10 actor_name
varchar(255) binary NOT NULL
11 ) /*$wgDBTableOptions*/;
12 CREATE UNIQUE INDEX /*i*/actor_user
ON /*_*/actor (actor_user
);
13 CREATE UNIQUE INDEX /*i*/actor_name
ON /*_*/actor (actor_name
);
15 CREATE TABLE /*_*/revision_actor_temp (
16 revactor_rev
int unsigned
NOT NULL,
17 revactor_actor
bigint unsigned
NOT NULL,
18 revactor_timestamp
binary(14) NOT NULL default '',
19 revactor_page
int unsigned
NOT NULL,
20 PRIMARY KEY (revactor_rev
, revactor_actor
)
21 ) /*$wgDBTableOptions*/;
22 CREATE UNIQUE INDEX /*i*/revactor_rev
ON /*_*/revision_actor_temp (revactor_rev
);
23 CREATE INDEX /*i*/actor_timestamp
ON /*_*/revision_actor_temp (revactor_actor
,revactor_timestamp
);
24 CREATE INDEX /*i*/page_actor_timestamp
ON /*_*/revision_actor_temp (revactor_page
,revactor_actor
,revactor_timestamp
);
28 DROP TABLE IF EXISTS /*_*/archive_tmp
;
29 CREATE TABLE /*_*/archive_tmp (
30 ar_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
31 ar_namespace
int NOT NULL default 0,
32 ar_title
varchar(255) binary NOT NULL default '',
33 ar_text mediumblob
NOT NULL,
34 ar_comment
varbinary(767) NOT NULL default '',
35 ar_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
36 ar_user
int unsigned
NOT NULL default 0,
37 ar_user_text
varchar(255) binary NOT NULL DEFAULT '',
38 ar_actor
bigint unsigned
NOT NULL DEFAULT 0,
39 ar_timestamp
binary(14) NOT NULL default '',
40 ar_minor_edit tinyint
NOT NULL default 0,
41 ar_flags tinyblob
NOT NULL,
42 ar_rev_id
int unsigned
,
43 ar_text_id
int unsigned
,
44 ar_deleted tinyint unsigned
NOT NULL default 0,
46 ar_page_id
int unsigned
,
47 ar_parent_id
int unsigned
default NULL,
48 ar_sha1
varbinary(32) NOT NULL default '',
49 ar_content_model
varbinary(32) DEFAULT NULL,
50 ar_content_format
varbinary(64) DEFAULT NULL
51 ) /*$wgDBTableOptions*/;
53 INSERT OR IGNORE INTO /*_*/archive_tmp (
54 ar_id
, ar_namespace
, ar_title
, ar_text
, ar_comment
, ar_user
, ar_user_text
,
55 ar_timestamp
, ar_minor_edit
, ar_flags
, ar_rev_id
, ar_text_id
, ar_deleted
,
56 ar_len
, ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
,
59 ar_id
, ar_namespace
, ar_title
, ar_text
, ar_comment
, ar_user
, ar_user_text
,
60 ar_timestamp
, ar_minor_edit
, ar_flags
, ar_rev_id
, ar_text_id
, ar_deleted
,
61 ar_len
, ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
,
65 DROP TABLE /*_*/archive
;
66 ALTER TABLE /*_*/archive_tmp
RENAME TO /*_*/archive
;
67 CREATE INDEX /*i*/name_title_timestamp
ON /*_*/archive (ar_namespace
,ar_title
,ar_timestamp
);
68 CREATE INDEX /*i*/ar_usertext_timestamp
ON /*_*/archive (ar_user_text
,ar_timestamp
);
69 CREATE INDEX /*i*/ar_revid
ON /*_*/archive (ar_rev_id
);
70 CREATE INDEX /*i*/ar_actor_timestamp
ON /*_*/archive (ar_actor
,ar_timestamp
);
76 DROP TABLE IF EXISTS ipblocks_tmp
;
77 CREATE TABLE /*_*/ipblocks_tmp (
78 ipb_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
79 ipb_address tinyblob
NOT NULL,
80 ipb_user
int unsigned
NOT NULL default 0,
81 ipb_by
int unsigned
NOT NULL default 0,
82 ipb_by_text
varchar(255) binary NOT NULL default '',
83 ipb_by_actor
bigint unsigned
NOT NULL DEFAULT 0,
84 ipb_reason
varbinary(767) NOT NULL default '',
85 ipb_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
86 ipb_timestamp
binary(14) NOT NULL default '',
87 ipb_auto bool
NOT NULL default 0,
88 ipb_anon_only bool
NOT NULL default 0,
89 ipb_create_account bool
NOT NULL default 1,
90 ipb_enable_autoblock bool
NOT NULL default '1',
91 ipb_expiry
varbinary(14) NOT NULL default '',
92 ipb_range_start tinyblob
NOT NULL,
93 ipb_range_end tinyblob
NOT NULL,
94 ipb_deleted bool
NOT NULL default 0,
95 ipb_block_email bool
NOT NULL default 0,
96 ipb_allow_usertalk bool
NOT NULL default 0,
97 ipb_parent_block_id
int default NULL
98 ) /*$wgDBTableOptions*/;
100 INSERT OR IGNORE INTO /*_*/ipblocks_tmp (
101 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
102 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
103 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
104 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
)
106 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
107 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
108 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
109 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
112 DROP TABLE /*_*/ipblocks
;
113 ALTER TABLE /*_*/ipblocks_tmp
RENAME TO /*_*/ipblocks
;
114 CREATE UNIQUE INDEX /*i*/ipb_address
ON /*_*/ipblocks (ipb_address(255), ipb_user
, ipb_auto
, ipb_anon_only
);
115 CREATE INDEX /*i*/ipb_user
ON /*_*/ipblocks (ipb_user
);
116 CREATE INDEX /*i*/ipb_range
ON /*_*/ipblocks (ipb_range_start(8), ipb_range_end(8));
117 CREATE INDEX /*i*/ipb_timestamp
ON /*_*/ipblocks (ipb_timestamp
);
118 CREATE INDEX /*i*/ipb_expiry
ON /*_*/ipblocks (ipb_expiry
);
119 CREATE INDEX /*i*/ipb_parent_block_id
ON /*_*/ipblocks (ipb_parent_block_id
);
125 DROP TABLE IF EXISTS /*_*/image_tmp
;
126 CREATE TABLE /*_*/image_tmp (
127 img_name
varchar(255) binary NOT NULL default '' PRIMARY KEY,
128 img_size
int unsigned
NOT NULL default 0,
129 img_width
int NOT NULL default 0,
130 img_height
int NOT NULL default 0,
131 img_metadata mediumblob
NOT NULL,
132 img_bits
int NOT NULL default 0,
133 img_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
134 img_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
135 img_minor_mime
varbinary(100) NOT NULL default "unknown",
136 img_description
varbinary(767) NOT NULL default '',
137 img_user
int unsigned
NOT NULL default 0,
138 img_user_text
varchar(255) binary NOT NULL DEFAULT '',
139 img_actor
bigint unsigned
NOT NULL DEFAULT 0,
140 img_timestamp
varbinary(14) NOT NULL default '',
141 img_sha1
varbinary(32) NOT NULL default ''
142 ) /*$wgDBTableOptions*/;
144 INSERT OR IGNORE INTO /*_*/image_tmp (
145 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
146 img_media_type
, img_major_mime
, img_minor_mime
, img_description
, img_user
,
147 img_user_text
, img_timestamp
, img_sha1
)
149 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
150 img_media_type
, img_major_mime
, img_minor_mime
, img_description
, img_user
,
151 img_user_text
, img_timestamp
, img_sha1
154 DROP TABLE /*_*/image
;
155 ALTER TABLE /*_*/image_tmp
RENAME TO /*_*/image
;
156 CREATE INDEX /*i*/img_user_timestamp
ON /*_*/image (img_user
,img_timestamp
);
157 CREATE INDEX /*i*/img_usertext_timestamp
ON /*_*/image (img_user_text
,img_timestamp
);
158 CREATE INDEX /*i*/img_actor_timestamp
ON /*_*/image (img_actor
,img_timestamp
);
159 CREATE INDEX /*i*/img_size
ON /*_*/image (img_size
);
160 CREATE INDEX /*i*/img_timestamp
ON /*_*/image (img_timestamp
);
161 CREATE INDEX /*i*/img_sha1
ON /*_*/image (img_sha1(10));
162 CREATE INDEX /*i*/img_media_mime
ON /*_*/image (img_media_type
,img_major_mime
,img_minor_mime
);
168 DROP TABLE IF EXISTS /*_*/oldimage_tmp
;
169 CREATE TABLE /*_*/oldimage_tmp (
170 oi_name
varchar(255) binary NOT NULL default '',
171 oi_archive_name
varchar(255) binary NOT NULL default '',
172 oi_size
int unsigned
NOT NULL default 0,
173 oi_width
int NOT NULL default 0,
174 oi_height
int NOT NULL default 0,
175 oi_bits
int NOT NULL default 0,
176 oi_description
varbinary(767) NOT NULL default '',
177 oi_description_id
bigint unsigned
NOT NULL DEFAULT 0,
178 oi_user
int unsigned
NOT NULL default 0,
179 oi_user_text
varchar(255) binary NOT NULL DEFAULT '',
180 oi_actor
bigint unsigned
NOT NULL DEFAULT 0,
181 oi_timestamp
binary(14) NOT NULL default '',
182 oi_metadata mediumblob
NOT NULL,
183 oi_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
184 oi_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
185 oi_minor_mime
varbinary(100) NOT NULL default "unknown",
186 oi_deleted tinyint unsigned
NOT NULL default 0,
187 oi_sha1
varbinary(32) NOT NULL default ''
188 ) /*$wgDBTableOptions*/;
190 INSERT OR IGNORE INTO /*_*/oldimage_tmp (
191 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
192 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
193 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
)
195 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
196 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
197 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
200 DROP TABLE /*_*/oldimage
;
201 ALTER TABLE /*_*/oldimage_tmp
RENAME TO /*_*/oldimage
;
202 CREATE INDEX /*i*/oi_usertext_timestamp
ON /*_*/oldimage (oi_user_text
,oi_timestamp
);
203 CREATE INDEX /*i*/oi_name_timestamp
ON /*_*/oldimage (oi_name
,oi_timestamp
);
204 CREATE INDEX /*i*/oi_name_archive_name
ON /*_*/oldimage (oi_name
,oi_archive_name(14));
205 CREATE INDEX /*i*/oi_sha1
ON /*_*/oldimage (oi_sha1(10));
206 CREATE INDEX /*i*/oi_actor_timestamp
ON /*_*/oldimage (oi_actor
,oi_timestamp
);
212 DROP TABLE IF EXISTS /*_*/filearchive_tmp
;
213 CREATE TABLE /*_*/filearchive_tmp (
214 fa_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
215 fa_name
varchar(255) binary NOT NULL default '',
216 fa_archive_name
varchar(255) binary default '',
217 fa_storage_group
varbinary(16),
218 fa_storage_key
varbinary(64) default '',
220 fa_deleted_timestamp
binary(14) default '',
221 fa_deleted_reason
varbinary(767) default '',
222 fa_deleted_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
223 fa_size
int unsigned
default 0,
224 fa_width
int default 0,
225 fa_height
int default 0,
226 fa_metadata mediumblob
,
227 fa_bits
int default 0,
228 fa_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
229 fa_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") default "unknown",
230 fa_minor_mime
varbinary(100) default "unknown",
231 fa_description
varbinary(767) default '',
232 fa_description_id
bigint unsigned
NOT NULL DEFAULT 0,
233 fa_user
int unsigned
default 0,
234 fa_user_text
varchar(255) binary DEFAULT '',
235 fa_actor
bigint unsigned
NOT NULL DEFAULT 0,
236 fa_timestamp
binary(14) default '',
237 fa_deleted tinyint unsigned
NOT NULL default 0,
238 fa_sha1
varbinary(32) NOT NULL default ''
239 ) /*$wgDBTableOptions*/;
241 INSERT OR IGNORE INTO /*_*/filearchive_tmp (
242 fa_id
, fa_name
, fa_archive_name
, fa_storage_group
, fa_storage_key
,
243 fa_deleted_user
, fa_deleted_timestamp
, fa_deleted_reason
, fa_size
,
244 fa_width
, fa_height
, fa_metadata
, fa_bits
, fa_media_type
, fa_major_mime
,
245 fa_minor_mime
, fa_description
, fa_user
, fa_user_text
, fa_timestamp
,
248 fa_id
, fa_name
, fa_archive_name
, fa_storage_group
, fa_storage_key
,
249 fa_deleted_user
, fa_deleted_timestamp
, fa_deleted_reason
, fa_size
,
250 fa_width
, fa_height
, fa_metadata
, fa_bits
, fa_media_type
, fa_major_mime
,
251 fa_minor_mime
, fa_description
, fa_user
, fa_user_text
, fa_timestamp
,
253 FROM /*_*/filearchive
;
255 DROP TABLE /*_*/filearchive
;
256 ALTER TABLE /*_*/filearchive_tmp
RENAME TO /*_*/filearchive
;
257 CREATE INDEX /*i*/fa_name
ON /*_*/filearchive (fa_name
, fa_timestamp
);
258 CREATE INDEX /*i*/fa_storage_group
ON /*_*/filearchive (fa_storage_group
, fa_storage_key
);
259 CREATE INDEX /*i*/fa_deleted_timestamp
ON /*_*/filearchive (fa_deleted_timestamp
);
260 CREATE INDEX /*i*/fa_user_timestamp
ON /*_*/filearchive (fa_user_text
,fa_timestamp
);
261 CREATE INDEX /*i*/fa_sha1
ON /*_*/filearchive (fa_sha1(10));
262 CREATE INDEX /*i*/fa_actor_timestamp
ON /*_*/filearchive (fa_actor
,fa_timestamp
);
268 DROP TABLE IF EXISTS /*_*/logging_tmp
;
269 CREATE TABLE /*_*/logging_tmp (
270 log_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
271 log_type
varbinary(32) NOT NULL default '',
272 log_action
varbinary(32) NOT NULL default '',
273 log_timestamp
binary(14) NOT NULL default '19700101000000',
274 log_user
int unsigned
NOT NULL default 0,
275 log_user_text
varchar(255) binary NOT NULL default '',
276 log_actor
bigint unsigned
NOT NULL DEFAULT 0,
277 log_namespace
int NOT NULL default 0,
278 log_title
varchar(255) binary NOT NULL default '',
279 log_page
int unsigned
NULL,
280 log_comment
varbinary(767) NOT NULL default '',
281 log_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
282 log_params
blob NOT NULL,
283 log_deleted tinyint unsigned
NOT NULL default 0
284 ) /*$wgDBTableOptions*/;
286 INSERT OR IGNORE INTO /*_*/logging_tmp (
287 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
288 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
289 log_params
, log_deleted
)
291 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
292 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
293 log_params
, log_deleted
296 DROP TABLE /*_*/logging
;
297 ALTER TABLE /*_*/logging_tmp
RENAME TO /*_*/logging
;
298 CREATE INDEX /*i*/type_time
ON /*_*/logging (log_type
, log_timestamp
);
299 CREATE INDEX /*i*/user_time
ON /*_*/logging (log_user
, log_timestamp
);
300 CREATE INDEX /*i*/actor_time
ON /*_*/logging (log_actor
, log_timestamp
);
301 CREATE INDEX /*i*/page_time
ON /*_*/logging (log_namespace
, log_title
, log_timestamp
);
302 CREATE INDEX /*i*/times
ON /*_*/logging (log_timestamp
);
303 CREATE INDEX /*i*/log_user_type_time
ON /*_*/logging (log_user
, log_type
, log_timestamp
);
304 CREATE INDEX /*i*/log_actor_type_time
ON /*_*/logging (log_actor
, log_type
, log_timestamp
);
305 CREATE INDEX /*i*/log_page_id_time
ON /*_*/logging (log_page
,log_timestamp
);
306 CREATE INDEX /*i*/type_action
ON /*_*/logging (log_type
, log_action
, log_timestamp
);
307 CREATE INDEX /*i*/log_user_text_type_time
ON /*_*/logging (log_user_text
, log_type
, log_timestamp
);
308 CREATE INDEX /*i*/log_user_text_time
ON /*_*/logging (log_user_text
, log_timestamp
);
314 DROP TABLE IF EXISTS /*_*/recentchanges_tmp
;
315 CREATE TABLE /*_*/recentchanges_tmp (
316 rc_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
317 rc_timestamp
varbinary(14) NOT NULL default '',
318 rc_user
int unsigned
NOT NULL default 0,
319 rc_user_text
varchar(255) binary NOT NULL DEFAULT '',
320 rc_actor
bigint unsigned
NOT NULL DEFAULT 0,
321 rc_namespace
int NOT NULL default 0,
322 rc_title
varchar(255) binary NOT NULL default '',
323 rc_comment
varbinary(767) NOT NULL default '',
324 rc_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
325 rc_minor tinyint unsigned
NOT NULL default 0,
326 rc_bot tinyint unsigned
NOT NULL default 0,
327 rc_new tinyint unsigned
NOT NULL default 0,
328 rc_cur_id
int unsigned
NOT NULL default 0,
329 rc_this_oldid
int unsigned
NOT NULL default 0,
330 rc_last_oldid
int unsigned
NOT NULL default 0,
331 rc_type tinyint unsigned
NOT NULL default 0,
332 rc_source
varchar(16) binary not null default '',
333 rc_patrolled tinyint unsigned
NOT NULL default 0,
334 rc_ip
varbinary(40) NOT NULL default '',
337 rc_deleted tinyint unsigned
NOT NULL default 0,
338 rc_logid
int unsigned
NOT NULL default 0,
339 rc_log_type
varbinary(255) NULL default NULL,
340 rc_log_action
varbinary(255) NULL default NULL,
342 ) /*$wgDBTableOptions*/;
344 INSERT OR IGNORE INTO /*_*/recentchanges_tmp (
345 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
346 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
347 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
348 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
351 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
352 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
353 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
354 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
356 FROM /*_*/recentchanges
;
358 DROP TABLE /*_*/recentchanges
;
359 ALTER TABLE /*_*/recentchanges_tmp
RENAME TO /*_*/recentchanges
;
360 CREATE INDEX /*i*/rc_timestamp
ON /*_*/recentchanges (rc_timestamp
);
361 CREATE INDEX /*i*/rc_namespace_title
ON /*_*/recentchanges (rc_namespace
, rc_title
);
362 CREATE INDEX /*i*/rc_cur_id
ON /*_*/recentchanges (rc_cur_id
);
363 CREATE INDEX /*i*/new_name_timestamp
ON /*_*/recentchanges (rc_new
,rc_namespace
,rc_timestamp
);
364 CREATE INDEX /*i*/rc_ip
ON /*_*/recentchanges (rc_ip
);
365 CREATE INDEX /*i*/rc_ns_usertext
ON /*_*/recentchanges (rc_namespace
, rc_user_text
);
366 CREATE INDEX /*i*/rc_ns_actor
ON /*_*/recentchanges (rc_namespace
, rc_actor
);
367 CREATE INDEX /*i*/rc_user_text
ON /*_*/recentchanges (rc_user_text
, rc_timestamp
);
368 CREATE INDEX /*i*/rc_actor
ON /*_*/recentchanges (rc_actor
, rc_timestamp
);
369 CREATE INDEX /*i*/rc_name_type_patrolled_timestamp
ON /*_*/recentchanges (rc_namespace
, rc_type
, rc_patrolled
, rc_timestamp
);