3 CREATE TABLE /*_*/image_tmp (
5 -- This is also the title of the associated description page,
6 -- which will be in namespace 6 (NS_FILE).
7 img_name
varchar(255) binary NOT NULL default '' PRIMARY KEY,
10 img_size
int unsigned
NOT NULL default 0,
12 -- For images, size in pixels.
13 img_width
int NOT NULL default 0,
14 img_height
int NOT NULL default 0,
16 -- Extracted Exif metadata stored as a serialized PHP array.
17 img_metadata mediumblob
NOT NULL,
19 -- For images, bits per pixel if known.
20 img_bits
int NOT NULL default 0,
22 -- Media type as defined by the MEDIATYPE_xxx constants
23 img_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
25 -- major part of a MIME media type as defined by IANA
26 -- see https://www.iana.org/assignments/media-types/
27 -- for "chemical" cf. http://dx.doi.org/10.1021/ci9803233 by the ACS
28 img_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
30 -- minor part of a MIME media type as defined by IANA
31 -- the minor parts are not required to adher to any standard
32 -- but should be consistent throughout the database
33 -- see https://www.iana.org/assignments/media-types/
34 img_minor_mime
varbinary(100) NOT NULL default "unknown",
36 -- Description field as entered by the uploader.
37 -- This is displayed in image upload history and logs.
38 img_description
varbinary(767) NOT NULL,
40 -- user_id and user_name of uploader.
41 img_user
int unsigned
NOT NULL default 0,
42 img_user_text
varchar(255) binary NOT NULL,
44 -- Time of the upload.
45 img_timestamp
varbinary(14) NOT NULL default '',
47 -- SHA-1 content hash in base-36
48 img_sha1
varbinary(32) NOT NULL default ''
49 ) /*$wgDBTableOptions*/;
51 INSERT INTO /*_*/image_tmp
52 SELECT img_name
, img_size
, img_width
, img_height
, img_metadata
, img_bits
,
53 img_media_type
, img_major_mime
, img_minor_mime
, img_description
,
54 img_user
, img_user_text
, img_timestamp
, img_sha1
57 DROP TABLE /*_*/image
;
59 ALTER TABLE /*_*/image_tmp
RENAME TO /*_*/image
;
61 -- Used by Special:Newimages and ApiQueryAllImages
62 CREATE INDEX /*i*/img_user_timestamp
ON /*_*/image (img_user
,img_timestamp
);
63 CREATE INDEX /*i*/img_usertext_timestamp
ON /*_*/image (img_user_text
,img_timestamp
);
64 -- Used by Special:ListFiles for sort-by-size
65 CREATE INDEX /*i*/img_size
ON /*_*/image (img_size
);
66 -- Used by Special:Newimages and Special:ListFiles
67 CREATE INDEX /*i*/img_timestamp
ON /*_*/image (img_timestamp
);
68 -- Used in API and duplicate search
69 CREATE INDEX /*i*/img_sha1
ON /*_*/image (img_sha1(10));
70 -- Used to get media of one type
71 CREATE INDEX /*i*/img_media_mime
ON /*_*/image (img_media_type
,img_major_mime
,img_minor_mime
);
75 CREATE TABLE /*_*/oldimage_tmp (
76 -- Base filename: key to image.img_name
77 oi_name
varchar(255) binary NOT NULL default '',
79 -- Filename of the archived file.
80 -- This is generally a timestamp and '!' prepended to the base name.
81 oi_archive_name
varchar(255) binary NOT NULL default '',
83 -- Other fields as in image...
84 oi_size
int unsigned
NOT NULL default 0,
85 oi_width
int NOT NULL default 0,
86 oi_height
int NOT NULL default 0,
87 oi_bits
int NOT NULL default 0,
88 oi_description
varbinary(767) NOT NULL,
89 oi_user
int unsigned
NOT NULL default 0,
90 oi_user_text
varchar(255) binary NOT NULL,
91 oi_timestamp
binary(14) NOT NULL default '',
93 oi_metadata mediumblob
NOT NULL,
94 oi_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
95 oi_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
96 oi_minor_mime
varbinary(100) NOT NULL default "unknown",
97 oi_deleted tinyint unsigned
NOT NULL default 0,
98 oi_sha1
varbinary(32) NOT NULL default ''
99 ) /*$wgDBTableOptions*/;
101 INSERT INTO /*_*/oldimage_tmp
102 SELECT oi_name
, oi_archive_name
, oi_size
, oi_width
, oi_height
, oi_bits
,
103 oi_description
, oi_user
, oi_user_text
, oi_timestamp
, oi_metadata
,
104 oi_media_type
, oi_major_mime
, oi_minor_mime
, oi_deleted
, oi_sha1
107 DROP TABLE /*_*/oldimage
;
109 ALTER TABLE oldimage_tmp
RENAME TO /*_*/oldimage
;
111 CREATE INDEX /*i*/oi_usertext_timestamp
ON /*_*/oldimage (oi_user_text
,oi_timestamp
);
112 CREATE INDEX /*i*/oi_name_timestamp
ON /*_*/oldimage (oi_name
,oi_timestamp
);
113 -- oi_archive_name truncated to 14 to avoid key length overflow
114 CREATE INDEX /*i*/oi_name_archive_name
ON /*_*/oldimage (oi_name
,oi_archive_name(14));
115 CREATE INDEX /*i*/oi_sha1
ON /*_*/oldimage (oi_sha1(10));
119 CREATE TABLE /*_*/filearchive_tmp (
121 fa_id
int NOT NULL PRIMARY KEY AUTO_INCREMENT
,
123 -- Original base filename; key to image.img_name, page.page_title, etc
124 fa_name
varchar(255) binary NOT NULL default '',
126 -- Filename of archived file, if an old revision
127 fa_archive_name
varchar(255) binary default '',
129 -- Which storage bin (directory tree or object store) the file data
130 -- is stored in. Should be 'deleted' for files that have been deleted;
131 -- any other bin is not yet in use.
132 fa_storage_group
varbinary(16),
134 -- SHA-1 of the file contents plus extension, used as a key for storage.
135 -- eg 8f8a562add37052a1848ff7771a2c515db94baa9.jpg
137 -- If NULL, the file was missing at deletion time or has been purged
138 -- from the archival storage.
139 fa_storage_key
varbinary(64) default '',
141 -- Deletion information, if this file is deleted.
143 fa_deleted_timestamp
binary(14) default '',
144 fa_deleted_reason
varbinary(767) default '',
146 -- Duped fields from image
147 fa_size
int unsigned
default 0,
148 fa_width
int default 0,
149 fa_height
int default 0,
150 fa_metadata mediumblob
,
151 fa_bits
int default 0,
152 fa_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
153 fa_major_mime
ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") default "unknown",
154 fa_minor_mime
varbinary(100) default "unknown",
155 fa_description
varbinary(767),
156 fa_user
int unsigned
default 0,
157 fa_user_text
varchar(255) binary,
158 fa_timestamp
binary(14) default '',
160 -- Visibility of deleted revisions, bitfield
161 fa_deleted tinyint unsigned
NOT NULL default 0,
163 -- sha1 hash of file content
164 fa_sha1
varbinary(32) NOT NULL default ''
165 ) /*$wgDBTableOptions*/;
167 INSERT INTO /*_*/filearchive_tmp
168 SELECT fa_id
, fa_name
, fa_archive_name
, fa_storage_group
, fa_storage_key
, fa_deleted_user
, fa_deleted_timestamp
,
169 fa_deleted_reason
, fa_size
, fa_width
, fa_height
, fa_metadata
, fa_bits
, fa_media_type
, fa_major_mime
,
170 fa_minor_mime
, fa_description
, fa_user
, fa_user_text
, fa_timestamp
, fa_deleted
, fa_sha1
171 FROM /*_*/filearchive
;
173 DROP TABLE /*_*/filearchive
;
175 ALTER TABLE /*_*/filearchive_tmp
RENAME TO /*_*/filearchive
;
177 -- pick out by image name
178 CREATE INDEX /*i*/fa_name
ON /*_*/filearchive (fa_name
, fa_timestamp
);
179 -- pick out dupe files
180 CREATE INDEX /*i*/fa_storage_group
ON /*_*/filearchive (fa_storage_group
, fa_storage_key
);
181 -- sort by deletion time
182 CREATE INDEX /*i*/fa_deleted_timestamp
ON /*_*/filearchive (fa_deleted_timestamp
);
184 CREATE INDEX /*i*/fa_user_timestamp
ON /*_*/filearchive (fa_user_text
,fa_timestamp
);
185 -- find file by sha1, 10 bytes will be enough for hashes to be indexed
186 CREATE INDEX /*i*/fa_sha1
ON /*_*/filearchive (fa_sha1(10));
190 CREATE TABLE /*_*/uploadstash_tmp (
191 us_id
int unsigned
NOT NULL PRIMARY KEY AUTO_INCREMENT
,
193 -- the user who uploaded the file.
194 us_user
int unsigned
NOT NULL,
196 -- file key. this is how applications actually search for the file.
197 -- this might go away, or become the primary key.
198 us_key
varchar(255) NOT NULL,
201 us_orig_path
varchar(255) NOT NULL,
203 -- the temporary path at which the file is actually stored
204 us_path
varchar(255) NOT NULL,
206 -- which type of upload the file came from (sometimes)
207 us_source_type
varchar(50),
209 -- the date/time on which the file was added
210 us_timestamp
varbinary(14) NOT NULL,
212 us_status
varchar(50) NOT NULL,
214 -- chunk counter starts at 0, current offset is stored in us_size
215 us_chunk_inx
int unsigned
NULL,
217 -- Serialized file properties from FSFile::getProps()
220 -- file size in bytes
221 us_size
int unsigned
NOT NULL,
222 -- this hash comes from FSFile::getSha1Base36(), and is 31 characters
223 us_sha1
varchar(31) NOT NULL,
224 us_mime
varchar(255),
225 -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
226 us_media_type
ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
227 -- image-specific properties
228 us_image_width
int unsigned
,
229 us_image_height
int unsigned
,
230 us_image_bits
smallint unsigned
232 ) /*$wgDBTableOptions*/;
234 INSERT INTO /*_*/uploadstash_tmp
235 SELECT us_id
, us_user
, us_key
, us_orig_path
, us_path
, us_source_type
,
236 us_timestamp
, us_status
, us_chunk_inx
, us_props
, us_size
, us_sha1
, us_mime
,
237 us_media_type
, us_image_width
, us_image_height
, us_image_bits
238 FROM /*_*/uploadstash
;
240 DROP TABLE uploadstash
;
242 ALTER TABLE /*_*/uploadstash_tmp
RENAME TO /*_*/uploadstash
;
244 -- sometimes there's a delete for all of a user's stuff.
245 CREATE INDEX /*i*/us_user
ON /*_*/uploadstash (us_user
);
246 -- pick out files by key, enforce key uniqueness
247 CREATE UNIQUE INDEX /*i*/us_key
ON /*_*/uploadstash (us_key
);
248 -- the abandoned upload cleanup script needs this
249 CREATE INDEX /*i*/us_timestamp
ON /*_*/uploadstash (us_timestamp
);