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_description_id
bigint unsigned
NOT NULL DEFAULT 0,
138 img_user
int unsigned
NOT NULL default 0,
139 img_user_text
varchar(255) binary NOT NULL DEFAULT '',
140 img_actor
bigint unsigned
NOT NULL DEFAULT 0,
141 img_timestamp
varbinary(14) NOT NULL default '',
142 img_sha1
varbinary(32) NOT NULL default ''
143 ) /*$wgDBTableOptions*/;
145 INSERT OR IGNORE INTO /*_*/image_tmp (
146 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
147 img_media_type
, img_major_mime
, img_minor_mime
, img_description
,
148 img_description_id
, img_user
, img_user_text
, img_timestamp
, img_sha1
)
150 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
151 img_media_type
, img_major_mime
, img_minor_mime
, img_description
,
152 img_description_id
, img_user
, img_user_text
, img_timestamp
, img_sha1
155 DROP TABLE /*_*/image
;
156 ALTER TABLE /*_*/image_tmp
RENAME TO /*_*/image
;
157 CREATE INDEX /*i*/img_user_timestamp
ON /*_*/image (img_user
,img_timestamp
);
158 CREATE INDEX /*i*/img_usertext_timestamp
ON /*_*/image (img_user_text
,img_timestamp
);
159 CREATE INDEX /*i*/img_actor_timestamp
ON /*_*/image (img_actor
,img_timestamp
);
160 CREATE INDEX /*i*/img_size
ON /*_*/image (img_size
);
161 CREATE INDEX /*i*/img_timestamp
ON /*_*/image (img_timestamp
);
162 CREATE INDEX /*i*/img_sha1
ON /*_*/image (img_sha1(10));
163 CREATE INDEX /*i*/img_media_mime
ON /*_*/image (img_media_type
,img_major_mime
,img_minor_mime
);
169 DROP TABLE IF EXISTS /*_*/oldimage_tmp
;
170 CREATE TABLE /*_*/oldimage_tmp (
171 oi_name
varchar(255) binary NOT NULL default '',
172 oi_archive_name
varchar(255) binary NOT NULL default '',
173 oi_size
int unsigned
NOT NULL default 0,
174 oi_width
int NOT NULL default 0,
175 oi_height
int NOT NULL default 0,
176 oi_bits
int NOT NULL default 0,
177 oi_description
varbinary(767) NOT NULL default '',
178 oi_description_id
bigint unsigned
NOT NULL DEFAULT 0,
179 oi_user
int unsigned
NOT NULL default 0,
180 oi_user_text
varchar(255) binary NOT NULL DEFAULT '',
181 oi_actor
bigint unsigned
NOT NULL DEFAULT 0,
182 oi_timestamp
binary(14) NOT NULL default '',
183 oi_metadata mediumblob
NOT NULL,
184 oi_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
185 oi_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
186 oi_minor_mime
varbinary(100) NOT NULL default "unknown",
187 oi_deleted tinyint unsigned
NOT NULL default 0,
188 oi_sha1
varbinary(32) NOT NULL default ''
189 ) /*$wgDBTableOptions*/;
191 INSERT OR IGNORE INTO /*_*/oldimage_tmp (
192 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
193 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
194 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
)
196 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
197 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
198 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
201 DROP TABLE /*_*/oldimage
;
202 ALTER TABLE /*_*/oldimage_tmp
RENAME TO /*_*/oldimage
;
203 CREATE INDEX /*i*/oi_usertext_timestamp
ON /*_*/oldimage (oi_user_text
,oi_timestamp
);
204 CREATE INDEX /*i*/oi_name_timestamp
ON /*_*/oldimage (oi_name
,oi_timestamp
);
205 CREATE INDEX /*i*/oi_name_archive_name
ON /*_*/oldimage (oi_name
,oi_archive_name(14));
206 CREATE INDEX /*i*/oi_sha1
ON /*_*/oldimage (oi_sha1(10));
207 CREATE INDEX /*i*/oi_actor_timestamp
ON /*_*/oldimage (oi_actor
,oi_timestamp
);
213 DROP TABLE IF EXISTS /*_*/filearchive_tmp
;
214 CREATE TABLE /*_*/filearchive_tmp (
215 fa_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
216 fa_name
varchar(255) binary NOT NULL default '',
217 fa_archive_name
varchar(255) binary default '',
218 fa_storage_group
varbinary(16),
219 fa_storage_key
varbinary(64) default '',
221 fa_deleted_timestamp
binary(14) default '',
222 fa_deleted_reason
varbinary(767) default '',
223 fa_deleted_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
224 fa_size
int unsigned
default 0,
225 fa_width
int default 0,
226 fa_height
int default 0,
227 fa_metadata mediumblob
,
228 fa_bits
int default 0,
229 fa_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
230 fa_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") default "unknown",
231 fa_minor_mime
varbinary(100) default "unknown",
232 fa_description
varbinary(767) default '',
233 fa_description_id
bigint unsigned
NOT NULL DEFAULT 0,
234 fa_user
int unsigned
default 0,
235 fa_user_text
varchar(255) binary DEFAULT '',
236 fa_actor
bigint unsigned
NOT NULL DEFAULT 0,
237 fa_timestamp
binary(14) default '',
238 fa_deleted tinyint unsigned
NOT NULL default 0,
239 fa_sha1
varbinary(32) NOT NULL default ''
240 ) /*$wgDBTableOptions*/;
242 INSERT OR IGNORE INTO /*_*/filearchive_tmp (
243 fa_id
, fa_name
, fa_archive_name
, fa_storage_group
, fa_storage_key
,
244 fa_deleted_user
, fa_deleted_timestamp
, fa_deleted_reason
, fa_size
,
245 fa_width
, fa_height
, fa_metadata
, fa_bits
, fa_media_type
, fa_major_mime
,
246 fa_minor_mime
, fa_description
, fa_user
, fa_user_text
, fa_timestamp
,
249 fa_id
, fa_name
, fa_archive_name
, fa_storage_group
, fa_storage_key
,
250 fa_deleted_user
, fa_deleted_timestamp
, fa_deleted_reason
, fa_size
,
251 fa_width
, fa_height
, fa_metadata
, fa_bits
, fa_media_type
, fa_major_mime
,
252 fa_minor_mime
, fa_description
, fa_user
, fa_user_text
, fa_timestamp
,
254 FROM /*_*/filearchive
;
256 DROP TABLE /*_*/filearchive
;
257 ALTER TABLE /*_*/filearchive_tmp
RENAME TO /*_*/filearchive
;
258 CREATE INDEX /*i*/fa_name
ON /*_*/filearchive (fa_name
, fa_timestamp
);
259 CREATE INDEX /*i*/fa_storage_group
ON /*_*/filearchive (fa_storage_group
, fa_storage_key
);
260 CREATE INDEX /*i*/fa_deleted_timestamp
ON /*_*/filearchive (fa_deleted_timestamp
);
261 CREATE INDEX /*i*/fa_user_timestamp
ON /*_*/filearchive (fa_user_text
,fa_timestamp
);
262 CREATE INDEX /*i*/fa_sha1
ON /*_*/filearchive (fa_sha1(10));
263 CREATE INDEX /*i*/fa_actor_timestamp
ON /*_*/filearchive (fa_actor
,fa_timestamp
);
269 DROP TABLE IF EXISTS /*_*/logging_tmp
;
270 CREATE TABLE /*_*/logging_tmp (
271 log_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
272 log_type
varbinary(32) NOT NULL default '',
273 log_action
varbinary(32) NOT NULL default '',
274 log_timestamp
binary(14) NOT NULL default '19700101000000',
275 log_user
int unsigned
NOT NULL default 0,
276 log_user_text
varchar(255) binary NOT NULL default '',
277 log_actor
bigint unsigned
NOT NULL DEFAULT 0,
278 log_namespace
int NOT NULL default 0,
279 log_title
varchar(255) binary NOT NULL default '',
280 log_page
int unsigned
NULL,
281 log_comment
varbinary(767) NOT NULL default '',
282 log_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
283 log_params
blob NOT NULL,
284 log_deleted tinyint unsigned
NOT NULL default 0
285 ) /*$wgDBTableOptions*/;
287 INSERT OR IGNORE INTO /*_*/logging_tmp (
288 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
289 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
290 log_params
, log_deleted
)
292 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
293 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
294 log_params
, log_deleted
297 DROP TABLE /*_*/logging
;
298 ALTER TABLE /*_*/logging_tmp
RENAME TO /*_*/logging
;
299 CREATE INDEX /*i*/type_time
ON /*_*/logging (log_type
, log_timestamp
);
300 CREATE INDEX /*i*/user_time
ON /*_*/logging (log_user
, log_timestamp
);
301 CREATE INDEX /*i*/actor_time
ON /*_*/logging (log_actor
, log_timestamp
);
302 CREATE INDEX /*i*/page_time
ON /*_*/logging (log_namespace
, log_title
, log_timestamp
);
303 CREATE INDEX /*i*/times
ON /*_*/logging (log_timestamp
);
304 CREATE INDEX /*i*/log_user_type_time
ON /*_*/logging (log_user
, log_type
, log_timestamp
);
305 CREATE INDEX /*i*/log_actor_type_time
ON /*_*/logging (log_actor
, log_type
, log_timestamp
);
306 CREATE INDEX /*i*/log_page_id_time
ON /*_*/logging (log_page
,log_timestamp
);
307 CREATE INDEX /*i*/type_action
ON /*_*/logging (log_type
, log_action
, log_timestamp
);
308 CREATE INDEX /*i*/log_user_text_type_time
ON /*_*/logging (log_user_text
, log_type
, log_timestamp
);
309 CREATE INDEX /*i*/log_user_text_time
ON /*_*/logging (log_user_text
, log_timestamp
);
315 DROP TABLE IF EXISTS /*_*/recentchanges_tmp
;
316 CREATE TABLE /*_*/recentchanges_tmp (
317 rc_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
318 rc_timestamp
varbinary(14) NOT NULL default '',
319 rc_user
int unsigned
NOT NULL default 0,
320 rc_user_text
varchar(255) binary NOT NULL DEFAULT '',
321 rc_actor
bigint unsigned
NOT NULL DEFAULT 0,
322 rc_namespace
int NOT NULL default 0,
323 rc_title
varchar(255) binary NOT NULL default '',
324 rc_comment
varbinary(767) NOT NULL default '',
325 rc_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
326 rc_minor tinyint unsigned
NOT NULL default 0,
327 rc_bot tinyint unsigned
NOT NULL default 0,
328 rc_new tinyint unsigned
NOT NULL default 0,
329 rc_cur_id
int unsigned
NOT NULL default 0,
330 rc_this_oldid
int unsigned
NOT NULL default 0,
331 rc_last_oldid
int unsigned
NOT NULL default 0,
332 rc_type tinyint unsigned
NOT NULL default 0,
333 rc_source
varchar(16) binary not null default '',
334 rc_patrolled tinyint unsigned
NOT NULL default 0,
335 rc_ip
varbinary(40) NOT NULL default '',
338 rc_deleted tinyint unsigned
NOT NULL default 0,
339 rc_logid
int unsigned
NOT NULL default 0,
340 rc_log_type
varbinary(255) NULL default NULL,
341 rc_log_action
varbinary(255) NULL default NULL,
343 ) /*$wgDBTableOptions*/;
345 INSERT OR IGNORE INTO /*_*/recentchanges_tmp (
346 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
347 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
348 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
349 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
352 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
353 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
354 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
355 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
357 FROM /*_*/recentchanges
;
359 DROP TABLE /*_*/recentchanges
;
360 ALTER TABLE /*_*/recentchanges_tmp
RENAME TO /*_*/recentchanges
;
361 CREATE INDEX /*i*/rc_timestamp
ON /*_*/recentchanges (rc_timestamp
);
362 CREATE INDEX /*i*/rc_namespace_title
ON /*_*/recentchanges (rc_namespace
, rc_title
);
363 CREATE INDEX /*i*/rc_cur_id
ON /*_*/recentchanges (rc_cur_id
);
364 CREATE INDEX /*i*/new_name_timestamp
ON /*_*/recentchanges (rc_new
,rc_namespace
,rc_timestamp
);
365 CREATE INDEX /*i*/rc_ip
ON /*_*/recentchanges (rc_ip
);
366 CREATE INDEX /*i*/rc_ns_usertext
ON /*_*/recentchanges (rc_namespace
, rc_user_text
);
367 CREATE INDEX /*i*/rc_ns_actor
ON /*_*/recentchanges (rc_namespace
, rc_actor
);
368 CREATE INDEX /*i*/rc_user_text
ON /*_*/recentchanges (rc_user_text
, rc_timestamp
);
369 CREATE INDEX /*i*/rc_actor
ON /*_*/recentchanges (rc_actor
, rc_timestamp
);
370 CREATE INDEX /*i*/rc_name_type_patrolled_timestamp
ON /*_*/recentchanges (rc_namespace
, rc_type
, rc_patrolled
, rc_timestamp
);