maintenance/sqlite/archives/patch-add-3d.sql

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