2 -- patch-comment-table.sql
4 -- T166732. Add a `comment` 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 /*_*/comment (
8 comment_id
bigint unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
9 comment_hash
INT NOT NULL,
10 comment_text
BLOB NOT NULL,
12 ) /*$wgDBTableOptions*/;
13 CREATE INDEX /*i*/comment_hash
ON /*_*/comment (comment_hash
);
15 CREATE TABLE /*_*/revision_comment_temp (
16 revcomment_rev
int unsigned
NOT NULL,
17 revcomment_comment_id
bigint unsigned
NOT NULL,
18 PRIMARY KEY (revcomment_rev
, revcomment_comment_id
)
19 ) /*$wgDBTableOptions*/;
20 CREATE UNIQUE INDEX /*i*/revcomment_rev
ON /*_*/revision_comment_temp (revcomment_rev
);
22 CREATE TABLE /*_*/image_comment_temp (
23 imgcomment_name
varchar(255) binary NOT NULL,
24 imgcomment_description_id
bigint unsigned
NOT NULL,
25 PRIMARY KEY (imgcomment_name
, imgcomment_description_id
)
26 ) /*$wgDBTableOptions*/;
27 CREATE UNIQUE INDEX /*i*/imgcomment_name
ON /*_*/image_comment_temp (imgcomment_name
);
29 ALTER TABLE /*_*/recentchanges
30 ADD COLUMN rc_comment_id
bigint unsigned
NOT NULL DEFAULT 0;
32 ALTER TABLE /*_*/logging
33 ADD COLUMN log_comment_id
bigint unsigned
NOT NULL DEFAULT 0;
37 DROP TABLE IF EXISTS /*_*/revision_tmp
;
38 CREATE TABLE /*_*/revision_tmp (
39 rev_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
40 rev_page
int unsigned
NOT NULL,
41 rev_text_id
int unsigned
NOT NULL,
42 rev_comment
varbinary(767) NOT NULL default '',
43 rev_user
int unsigned
NOT NULL default 0,
44 rev_user_text
varchar(255) binary NOT NULL default '',
45 rev_timestamp
binary(14) NOT NULL default '',
46 rev_minor_edit tinyint unsigned
NOT NULL default 0,
47 rev_deleted tinyint unsigned
NOT NULL default 0,
49 rev_parent_id
int unsigned
default NULL,
50 rev_sha1
varbinary(32) NOT NULL default '',
51 rev_content_model
varbinary(32) DEFAULT NULL,
52 rev_content_format
varbinary(64) DEFAULT NULL
53 ) /*$wgDBTableOptions*/ MAX_ROWS
=10000000 AVG_ROW_LENGTH
=1024;
55 INSERT OR IGNORE INTO /*_*/revision_tmp (
56 rev_id
, rev_page
, rev_text_id
, rev_comment
, rev_user
, rev_user_text
,
57 rev_timestamp
, rev_minor_edit
, rev_deleted
, rev_len
, rev_parent_id
,
58 rev_sha1
, rev_content_model
, rev_content_format
)
60 rev_id
, rev_page
, rev_text_id
, rev_comment
, rev_user
, rev_user_text
,
61 rev_timestamp
, rev_minor_edit
, rev_deleted
, rev_len
, rev_parent_id
,
62 rev_sha1
, rev_content_model
, rev_content_format
65 DROP TABLE /*_*/revision
;
66 ALTER TABLE /*_*/revision_tmp
RENAME TO /*_*/revision
;
67 CREATE INDEX /*i*/rev_page_id
ON /*_*/revision (rev_page
, rev_id
);
68 CREATE INDEX /*i*/rev_timestamp
ON /*_*/revision (rev_timestamp
);
69 CREATE INDEX /*i*/page_timestamp
ON /*_*/revision (rev_page
,rev_timestamp
);
70 CREATE INDEX /*i*/user_timestamp
ON /*_*/revision (rev_user
,rev_timestamp
);
71 CREATE INDEX /*i*/usertext_timestamp
ON /*_*/revision (rev_user_text
,rev_timestamp
);
72 CREATE INDEX /*i*/page_user_timestamp
ON /*_*/revision (rev_page
,rev_user
,rev_timestamp
);
78 DROP TABLE IF EXISTS /*_*/archive_tmp
;
79 CREATE TABLE /*_*/archive_tmp (
80 ar_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
81 ar_namespace
int NOT NULL default 0,
82 ar_title
varchar(255) binary NOT NULL default '',
83 ar_text mediumblob
NOT NULL,
84 ar_comment
varbinary(767) NOT NULL default '',
85 ar_comment_id
bigint unsigned
NOT NULL DEFAULT 0,
86 ar_user
int unsigned
NOT NULL default 0,
87 ar_user_text
varchar(255) binary NOT NULL,
88 ar_timestamp
binary(14) NOT NULL default '',
89 ar_minor_edit tinyint
NOT NULL default 0,
90 ar_flags tinyblob
NOT NULL,
91 ar_rev_id
int unsigned
,
92 ar_text_id
int unsigned
,
93 ar_deleted tinyint unsigned
NOT NULL default 0,
95 ar_page_id
int unsigned
,
96 ar_parent_id
int unsigned
default NULL,
97 ar_sha1
varbinary(32) NOT NULL default '',
98 ar_content_model
varbinary(32) DEFAULT NULL,
99 ar_content_format
varbinary(64) DEFAULT NULL
100 ) /*$wgDBTableOptions*/;
102 INSERT OR IGNORE INTO /*_*/archive_tmp (
103 ar_id
, ar_namespace
, ar_title
, ar_text
, ar_comment
, ar_user
, ar_user_text
,
104 ar_timestamp
, ar_minor_edit
, ar_flags
, ar_rev_id
, ar_text_id
, ar_deleted
,
105 ar_len
, ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
,
108 ar_id
, ar_namespace
, ar_title
, ar_text
, ar_comment
, ar_user
, ar_user_text
,
109 ar_timestamp
, ar_minor_edit
, ar_flags
, ar_rev_id
, ar_text_id
, ar_deleted
,
110 ar_len
, ar_page_id
, ar_parent_id
, ar_sha1
, ar_content_model
,
114 DROP TABLE /*_*/archive
;
115 ALTER TABLE /*_*/archive_tmp
RENAME TO /*_*/archive
;
116 CREATE INDEX /*i*/name_title_timestamp
ON /*_*/archive (ar_namespace
,ar_title
,ar_timestamp
);
117 CREATE INDEX /*i*/ar_usertext_timestamp
ON /*_*/archive (ar_user_text
,ar_timestamp
);
118 CREATE INDEX /*i*/ar_revid
ON /*_*/archive (ar_rev_id
);
124 DROP TABLE IF EXISTS ipblocks_tmp
;
125 CREATE TABLE /*_*/ipblocks_tmp (
126 ipb_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
127 ipb_address tinyblob
NOT NULL,
128 ipb_user
int unsigned
NOT NULL default 0,
129 ipb_by
int unsigned
NOT NULL default 0,
130 ipb_by_text
varchar(255) binary NOT NULL default '',
131 ipb_reason
varbinary(767) NOT NULL default '',
132 ipb_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
133 ipb_timestamp
binary(14) NOT NULL default '',
134 ipb_auto bool
NOT NULL default 0,
135 ipb_anon_only bool
NOT NULL default 0,
136 ipb_create_account bool
NOT NULL default 1,
137 ipb_enable_autoblock bool
NOT NULL default '1',
138 ipb_expiry
varbinary(14) NOT NULL default '',
139 ipb_range_start tinyblob
NOT NULL,
140 ipb_range_end tinyblob
NOT NULL,
141 ipb_deleted bool
NOT NULL default 0,
142 ipb_block_email bool
NOT NULL default 0,
143 ipb_allow_usertalk bool
NOT NULL default 0,
144 ipb_parent_block_id
int default NULL
145 ) /*$wgDBTableOptions*/;
147 INSERT OR IGNORE INTO /*_*/ipblocks_tmp (
148 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
149 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
150 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
151 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
)
153 ipb_id
, ipb_address
, ipb_user
, ipb_by
, ipb_by_text
, ipb_reason
,
154 ipb_timestamp
, ipb_auto
, ipb_anon_only
, ipb_create_account
,
155 ipb_enable_autoblock
, ipb_expiry
, ipb_range_start
, ipb_range_end
,
156 ipb_deleted
, ipb_block_email
, ipb_allow_usertalk
, ipb_parent_block_id
159 DROP TABLE /*_*/ipblocks
;
160 ALTER TABLE /*_*/ipblocks_tmp
RENAME TO /*_*/ipblocks
;
161 CREATE UNIQUE INDEX /*i*/ipb_address
ON /*_*/ipblocks (ipb_address(255), ipb_user
, ipb_auto
, ipb_anon_only
);
162 CREATE INDEX /*i*/ipb_user
ON /*_*/ipblocks (ipb_user
);
163 CREATE INDEX /*i*/ipb_range
ON /*_*/ipblocks (ipb_range_start(8), ipb_range_end(8));
164 CREATE INDEX /*i*/ipb_timestamp
ON /*_*/ipblocks (ipb_timestamp
);
165 CREATE INDEX /*i*/ipb_expiry
ON /*_*/ipblocks (ipb_expiry
);
166 CREATE INDEX /*i*/ipb_parent_block_id
ON /*_*/ipblocks (ipb_parent_block_id
);
172 DROP TABLE IF EXISTS /*_*/image_tmp
;
173 CREATE TABLE /*_*/image_tmp (
174 img_name
varchar(255) binary NOT NULL default '' PRIMARY KEY,
175 img_size
int unsigned
NOT NULL default 0,
176 img_width
int NOT NULL default 0,
177 img_height
int NOT NULL default 0,
178 img_metadata mediumblob
NOT NULL,
179 img_bits
int NOT NULL default 0,
180 img_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
181 img_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
182 img_minor_mime
varbinary(100) NOT NULL default "unknown",
183 img_description
varbinary(767) NOT NULL default '',
184 img_user
int unsigned
NOT NULL default 0,
185 img_user_text
varchar(255) binary NOT NULL,
186 img_timestamp
varbinary(14) NOT NULL default '',
187 img_sha1
varbinary(32) NOT NULL default ''
188 ) /*$wgDBTableOptions*/;
190 INSERT OR IGNORE INTO /*_*/image_tmp (
191 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
192 img_media_type
, img_major_mime
, img_minor_mime
, img_description
, img_user
,
193 img_user_text
, img_timestamp
, img_sha1
)
195 img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
196 img_media_type
, img_major_mime
, img_minor_mime
, img_description
, img_user
,
197 img_user_text
, img_timestamp
, img_sha1
200 DROP TABLE /*_*/image
;
201 ALTER TABLE /*_*/image_tmp
RENAME TO /*_*/image
;
202 CREATE INDEX /*i*/img_user_timestamp
ON /*_*/image (img_user
,img_timestamp
);
203 CREATE INDEX /*i*/img_usertext_timestamp
ON /*_*/image (img_user_text
,img_timestamp
);
204 CREATE INDEX /*i*/img_size
ON /*_*/image (img_size
);
205 CREATE INDEX /*i*/img_timestamp
ON /*_*/image (img_timestamp
);
206 CREATE INDEX /*i*/img_sha1
ON /*_*/image (img_sha1(10));
207 CREATE INDEX /*i*/img_media_mime
ON /*_*/image (img_media_type
,img_major_mime
,img_minor_mime
);
213 DROP TABLE IF EXISTS /*_*/oldimage_tmp
;
214 CREATE TABLE /*_*/oldimage_tmp (
215 oi_name
varchar(255) binary NOT NULL default '',
216 oi_archive_name
varchar(255) binary NOT NULL default '',
217 oi_size
int unsigned
NOT NULL default 0,
218 oi_width
int NOT NULL default 0,
219 oi_height
int NOT NULL default 0,
220 oi_bits
int NOT NULL default 0,
221 oi_description
varbinary(767) NOT NULL default '',
222 oi_description_id
bigint unsigned
NOT NULL DEFAULT 0,
223 oi_user
int unsigned
NOT NULL default 0,
224 oi_user_text
varchar(255) binary NOT NULL,
225 oi_timestamp
binary(14) NOT NULL default '',
226 oi_metadata mediumblob
NOT NULL,
227 oi_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
228 oi_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
229 oi_minor_mime
varbinary(100) NOT NULL default "unknown",
230 oi_deleted tinyint unsigned
NOT NULL default 0,
231 oi_sha1
varbinary(32) NOT NULL default ''
232 ) /*$wgDBTableOptions*/;
234 INSERT OR IGNORE INTO /*_*/oldimage_tmp (
235 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
236 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
237 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
)
239 oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
240 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
241 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
244 DROP TABLE /*_*/oldimage
;
245 ALTER TABLE /*_*/oldimage_tmp
RENAME TO /*_*/oldimage
;
246 CREATE INDEX /*i*/oi_usertext_timestamp
ON /*_*/oldimage (oi_user_text
,oi_timestamp
);
247 CREATE INDEX /*i*/oi_name_timestamp
ON /*_*/oldimage (oi_name
,oi_timestamp
);
248 CREATE INDEX /*i*/oi_name_archive_name
ON /*_*/oldimage (oi_name
,oi_archive_name(14));
249 CREATE INDEX /*i*/oi_sha1
ON /*_*/oldimage (oi_sha1(10));
253 -- filearchive is done in patch-filearchive-fa_description_id.sql
257 DROP TABLE IF EXISTS /*_*/protected_titles_tmp
;
258 CREATE TABLE /*_*/protected_titles_tmp (
259 pt_namespace
int NOT NULL,
260 pt_title
varchar(255) binary NOT NULL,
261 pt_user
int unsigned
NOT NULL,
262 pt_reason
varbinary(767) default '',
263 pt_reason_id
bigint unsigned
NOT NULL DEFAULT 0,
264 pt_timestamp
binary(14) NOT NULL,
265 pt_expiry
varbinary(14) NOT NULL default '',
266 pt_create_perm
varbinary(60) NOT NULL
267 ) /*$wgDBTableOptions*/;
269 INSERT OR IGNORE INTO /*_*/protected_titles_tmp (
270 pt_namespace
, pt_title
, pt_user
, pt_reason
, pt_timestamp
, pt_expiry
, pt_create_perm
)
272 pt_namespace
, pt_title
, pt_user
, pt_reason
, pt_timestamp
, pt_expiry
, pt_create_perm
273 FROM /*_*/protected_titles
;
275 DROP TABLE /*_*/protected_titles
;
276 ALTER TABLE /*_*/protected_titles_tmp
RENAME TO /*_*/protected_titles
;
277 CREATE UNIQUE INDEX /*i*/pt_namespace_title
ON /*_*/protected_titles (pt_namespace
,pt_title
);
278 CREATE INDEX /*i*/pt_timestamp
ON /*_*/protected_titles (pt_timestamp
);