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_comment
varbinary(767) NOT NULL default '',
34 ar_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
35 ar_user
int unsigned
NOT NULL default 0,
36 ar_user_text
varchar(255) binary NOT NULL DEFAULT '',
37 ar_actor
bigint unsigned
NOT NULL DEFAULT 0,
38 ar_timestamp
binary(14) NOT NULL default '',
39 ar_minor_edit tinyint
NOT NULL default 0,
40 ar_rev_id
int unsigned
,
41 ar_text_id
int unsigned
NOT NULL default 0,
42 ar_deleted tinyint unsigned
NOT NULL default 0,
44 ar_page_id
int unsigned
,
45 ar_parent_id
int unsigned
default NULL,
46 ar_sha1
varbinary(32) NOT NULL default '',
47 ar_content_model
varbinary(32) DEFAULT NULL,
48 ar_content_format
varbinary(64) DEFAULT NULL
49 ) /*$wgDBTableOptions*/;
51 INSERT OR IGNORE INTO /*_*/archive_tmp (
52 ar_id
, ar_namespace
, ar_title
, ar_comment
, ar_user
, ar_user_text
,
53 ar_timestamp
, ar_minor_edit
, ar_rev_id
, ar_text_id
, ar_deleted
, ar_len
,
54 ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
, ar_content_format
)
56 ar_id
, ar_namespace
, ar_title
, ar_comment
, ar_user
, ar_user_text
,
57 ar_timestamp
, ar_minor_edit
, ar_rev_id
, ar_text_id
, ar_deleted
, ar_len
,
58 ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
, ar_content_format
61 DROP TABLE /*_*/archive
;
62 ALTER TABLE /*_*/archive_tmp
RENAME TO /*_*/archive
;
63 CREATE INDEX /*i*/name_title_timestamp
ON /*_*/archive (ar_namespace
,ar_title
,ar_timestamp
);
64 CREATE INDEX /*i*/ar_usertext_timestamp
ON /*_*/archive (ar_user_text
,ar_timestamp
);
65 CREATE INDEX /*i*/ar_revid
ON /*_*/archive (ar_rev_id
);
66 CREATE INDEX /*i*/ar_actor_timestamp
ON /*_*/archive (ar_actor
,ar_timestamp
);
72 DROP TABLE IF EXISTS ipblocks_tmp
;
73 CREATE TABLE /*_*/ipblocks_tmp (
74 ipb_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
75 ipb_address tinyblob
NOT NULL,
76 ipb_user
int unsigned
NOT NULL default 0,
77 ipb_by
int unsigned
NOT NULL default 0,
78 ipb_by_text
varchar(255) binary NOT NULL default '',
79 ipb_by_actor
bigint unsigned
NOT NULL DEFAULT 0,
80 ipb_reason
varbinary(767) NOT NULL default '',
81 ipb_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
82 ipb_timestamp
binary(14) NOT NULL default '',
83 ipb_auto bool
NOT NULL default 0,
84 ipb_anon_only bool
NOT NULL default 0,
85 ipb_create_account bool
NOT NULL default 1,
86 ipb_enable_autoblock bool
NOT NULL default '1',
87 ipb_expiry
varbinary(14) NOT NULL default '',
88 ipb_range_start tinyblob
NOT NULL,
89 ipb_range_end tinyblob
NOT NULL,
90 ipb_deleted bool
NOT NULL default 0,
91 ipb_block_email bool
NOT NULL default 0,
92 ipb_allow_usertalk bool
NOT NULL default 0,
93 ipb_parent_block_id
int default NULL
94 ) /*$wgDBTableOptions*/;
96 INSERT OR IGNORE INTO /*_*/ipblocks_tmp (
97 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
98 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
99 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
100 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
)
102 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
103 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
104 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
105 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
108 DROP TABLE /*_*/ipblocks
;
109 ALTER TABLE /*_*/ipblocks_tmp
RENAME TO /*_*/ipblocks
;
110 CREATE UNIQUE INDEX /*i*/ipb_address
ON /*_*/ipblocks (ipb_address(255), ipb_user
, ipb_auto
, ipb_anon_only
);
111 CREATE INDEX /*i*/ipb_user
ON /*_*/ipblocks (ipb_user
);
112 CREATE INDEX /*i*/ipb_range
ON /*_*/ipblocks (ipb_range_start(8), ipb_range_end(8));
113 CREATE INDEX /*i*/ipb_timestamp
ON /*_*/ipblocks (ipb_timestamp
);
114 CREATE INDEX /*i*/ipb_expiry
ON /*_*/ipblocks (ipb_expiry
);
115 CREATE INDEX /*i*/ipb_parent_block_id
ON /*_*/ipblocks (ipb_parent_block_id
);
121 DROP TABLE IF EXISTS /*_*/image_tmp
;
122 CREATE TABLE /*_*/image_tmp (
123 img_name
varchar(255) binary NOT NULL default '' PRIMARY KEY,
124 img_size
int unsigned
NOT NULL default 0,
125 img_width
int NOT NULL default 0,
126 img_height
int NOT NULL default 0,
127 img_metadata mediumblob
NOT NULL,
128 img_bits
int NOT NULL default 0,
129 img_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
130 img_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
131 img_minor_mime
varbinary(100) NOT NULL default "unknown",
132 img_description
varbinary(767) NOT NULL default '',
133 img_description_id
bigint unsigned
NOT NULL DEFAULT 0,
134 img_user
int unsigned
NOT NULL default 0,
135 img_user_text
varchar(255) binary NOT NULL DEFAULT '',
136 img_actor
bigint unsigned
NOT NULL DEFAULT 0,
137 img_timestamp
varbinary(14) NOT NULL default '',
138 img_sha1
varbinary(32) NOT NULL default ''
139 ) /*$wgDBTableOptions*/;
141 INSERT OR IGNORE INTO /*_*/image_tmp (
142 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
143 img_media_type
, img_major_mime
, img_minor_mime
, img_description
,
144 img_description_id
, img_user
, img_user_text
, img_timestamp
, img_sha1
)
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
151 DROP TABLE /*_*/image
;
152 ALTER TABLE /*_*/image_tmp
RENAME TO /*_*/image
;
153 CREATE INDEX /*i*/img_user_timestamp
ON /*_*/image (img_user
,img_timestamp
);
154 CREATE INDEX /*i*/img_usertext_timestamp
ON /*_*/image (img_user_text
,img_timestamp
);
155 CREATE INDEX /*i*/img_actor_timestamp
ON /*_*/image (img_actor
,img_timestamp
);
156 CREATE INDEX /*i*/img_size
ON /*_*/image (img_size
);
157 CREATE INDEX /*i*/img_timestamp
ON /*_*/image (img_timestamp
);
158 CREATE INDEX /*i*/img_sha1
ON /*_*/image (img_sha1(10));
159 CREATE INDEX /*i*/img_media_mime
ON /*_*/image (img_media_type
,img_major_mime
,img_minor_mime
);
165 DROP TABLE IF EXISTS /*_*/oldimage_tmp
;
166 CREATE TABLE /*_*/oldimage_tmp (
167 oi_name
varchar(255) binary NOT NULL default '',
168 oi_archive_name
varchar(255) binary NOT NULL default '',
169 oi_size
int unsigned
NOT NULL default 0,
170 oi_width
int NOT NULL default 0,
171 oi_height
int NOT NULL default 0,
172 oi_bits
int NOT NULL default 0,
173 oi_description
varbinary(767) NOT NULL default '',
174 oi_description_id
bigint unsigned
NOT NULL DEFAULT 0,
175 oi_user
int unsigned
NOT NULL default 0,
176 oi_user_text
varchar(255) binary NOT NULL DEFAULT '',
177 oi_actor
bigint unsigned
NOT NULL DEFAULT 0,
178 oi_timestamp
binary(14) NOT NULL default '',
179 oi_metadata mediumblob
NOT NULL,
180 oi_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
181 oi_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
182 oi_minor_mime
varbinary(100) NOT NULL default "unknown",
183 oi_deleted tinyint unsigned
NOT NULL default 0,
184 oi_sha1
varbinary(32) NOT NULL default ''
185 ) /*$wgDBTableOptions*/;
187 INSERT OR IGNORE INTO /*_*/oldimage_tmp (
188 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
189 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
190 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
)
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
197 DROP TABLE /*_*/oldimage
;
198 ALTER TABLE /*_*/oldimage_tmp
RENAME TO /*_*/oldimage
;
199 CREATE INDEX /*i*/oi_usertext_timestamp
ON /*_*/oldimage (oi_user_text
,oi_timestamp
);
200 CREATE INDEX /*i*/oi_name_timestamp
ON /*_*/oldimage (oi_name
,oi_timestamp
);
201 CREATE INDEX /*i*/oi_name_archive_name
ON /*_*/oldimage (oi_name
,oi_archive_name(14));
202 CREATE INDEX /*i*/oi_sha1
ON /*_*/oldimage (oi_sha1(10));
203 CREATE INDEX /*i*/oi_actor_timestamp
ON /*_*/oldimage (oi_actor
,oi_timestamp
);
207 -- filearchive is done in patch-filearchive-fa_actor.sql
211 DROP TABLE IF EXISTS /*_*/logging_tmp
;
212 CREATE TABLE /*_*/logging_tmp (
213 log_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
214 log_type
varbinary(32) NOT NULL default '',
215 log_action
varbinary(32) NOT NULL default '',
216 log_timestamp
binary(14) NOT NULL default '19700101000000',
217 log_user
int unsigned
NOT NULL default 0,
218 log_user_text
varchar(255) binary NOT NULL default '',
219 log_actor
bigint unsigned
NOT NULL DEFAULT 0,
220 log_namespace
int NOT NULL default 0,
221 log_title
varchar(255) binary NOT NULL default '',
222 log_page
int unsigned
NULL,
223 log_comment
varbinary(767) NOT NULL default '',
224 log_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
225 log_params
blob NOT NULL,
226 log_deleted tinyint unsigned
NOT NULL default 0
227 ) /*$wgDBTableOptions*/;
229 INSERT OR IGNORE INTO /*_*/logging_tmp (
230 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
231 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
232 log_params
, log_deleted
)
234 log_id
, log_type
, log_action
, log_timestamp
, log_user
, log_user_text
,
235 log_namespace
, log_title
, log_page
, log_comment
, log_comment_id
,
236 log_params
, log_deleted
239 DROP TABLE /*_*/logging
;
240 ALTER TABLE /*_*/logging_tmp
RENAME TO /*_*/logging
;
241 CREATE INDEX /*i*/type_time
ON /*_*/logging (log_type
, log_timestamp
);
242 CREATE INDEX /*i*/user_time
ON /*_*/logging (log_user
, log_timestamp
);
243 CREATE INDEX /*i*/actor_time
ON /*_*/logging (log_actor
, log_timestamp
);
244 CREATE INDEX /*i*/page_time
ON /*_*/logging (log_namespace
, log_title
, log_timestamp
);
245 CREATE INDEX /*i*/times
ON /*_*/logging (log_timestamp
);
246 CREATE INDEX /*i*/log_user_type_time
ON /*_*/logging (log_user
, log_type
, log_timestamp
);
247 CREATE INDEX /*i*/log_actor_type_time
ON /*_*/logging (log_actor
, log_type
, log_timestamp
);
248 CREATE INDEX /*i*/log_page_id_time
ON /*_*/logging (log_page
,log_timestamp
);
249 CREATE INDEX /*i*/log_type_action
ON /*_*/logging (log_type
, log_action
, log_timestamp
);
250 CREATE INDEX /*i*/log_user_text_type_time
ON /*_*/logging (log_user_text
, log_type
, log_timestamp
);
251 CREATE INDEX /*i*/log_user_text_time
ON /*_*/logging (log_user_text
, log_timestamp
);
257 DROP TABLE IF EXISTS /*_*/recentchanges_tmp
;
258 CREATE TABLE /*_*/recentchanges_tmp (
259 rc_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
260 rc_timestamp
varbinary(14) NOT NULL default '',
261 rc_user
int unsigned
NOT NULL default 0,
262 rc_user_text
varchar(255) binary NOT NULL DEFAULT '',
263 rc_actor
bigint unsigned
NOT NULL DEFAULT 0,
264 rc_namespace
int NOT NULL default 0,
265 rc_title
varchar(255) binary NOT NULL default '',
266 rc_comment
varbinary(767) NOT NULL default '',
267 rc_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
268 rc_minor tinyint unsigned
NOT NULL default 0,
269 rc_bot tinyint unsigned
NOT NULL default 0,
270 rc_new tinyint unsigned
NOT NULL default 0,
271 rc_cur_id
int unsigned
NOT NULL default 0,
272 rc_this_oldid
int unsigned
NOT NULL default 0,
273 rc_last_oldid
int unsigned
NOT NULL default 0,
274 rc_type tinyint unsigned
NOT NULL default 0,
275 rc_source
varchar(16) binary not null default '',
276 rc_patrolled tinyint unsigned
NOT NULL default 0,
277 rc_ip
varbinary(40) NOT NULL default '',
280 rc_deleted tinyint unsigned
NOT NULL default 0,
281 rc_logid
int unsigned
NOT NULL default 0,
282 rc_log_type
varbinary(255) NULL default NULL,
283 rc_log_action
varbinary(255) NULL default NULL,
285 ) /*$wgDBTableOptions*/;
287 INSERT OR IGNORE INTO /*_*/recentchanges_tmp (
288 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
289 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
290 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
291 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
294 rc_id
, rc_timestamp
, rc_user
, rc_user_text
, rc_namespace
, rc_title
,
295 rc_comment
, rc_comment_id
, rc_minor
, rc_bot
, rc_new
, rc_cur_id
,
296 rc_this_oldid
, rc_last_oldid
, rc_type
, rc_source
, rc_patrolled
, rc_ip
,
297 rc_old_len
, rc_new_len
, rc_deleted
, rc_logid
, rc_log_type
, rc_log_action
,
299 FROM /*_*/recentchanges
;
301 DROP TABLE /*_*/recentchanges
;
302 ALTER TABLE /*_*/recentchanges_tmp
RENAME TO /*_*/recentchanges
;
303 CREATE INDEX /*i*/rc_timestamp
ON /*_*/recentchanges (rc_timestamp
);
304 CREATE INDEX /*i*/rc_namespace_title
ON /*_*/recentchanges (rc_namespace
, rc_title
);
305 CREATE INDEX /*i*/rc_cur_id
ON /*_*/recentchanges (rc_cur_id
);
306 CREATE INDEX /*i*/new_name_timestamp
ON /*_*/recentchanges (rc_new
,rc_namespace
,rc_timestamp
);
307 CREATE INDEX /*i*/rc_ip
ON /*_*/recentchanges (rc_ip
);
308 CREATE INDEX /*i*/rc_ns_usertext
ON /*_*/recentchanges (rc_namespace
, rc_user_text
);
309 CREATE INDEX /*i*/rc_ns_actor
ON /*_*/recentchanges (rc_namespace
, rc_actor
);
310 CREATE INDEX /*i*/rc_user_text
ON /*_*/recentchanges (rc_user_text
, rc_timestamp
);
311 CREATE INDEX /*i*/rc_actor
ON /*_*/recentchanges (rc_actor
, rc_timestamp
);
312 CREATE INDEX /*i*/rc_name_type_patrolled_timestamp
ON /*_*/recentchanges (rc_namespace
, rc_type
, rc_patrolled
, rc_timestamp
);