maintenance/mssql/archives/patch-oldimage-schema.sql

   1 -- MediaWiki looks for lines ending with semicolons and sends them as separate queries
   2 -- However here we *really* need this all to be sent as a single batch. As such, DO NOT
   3 -- remove the -- from the end of each statement.
   4
   5 DECLARE @temp table (
   6         oi_name varbinary(255),
   7         oi_archive_name varbinary(255),
   8         oi_size int,
   9         oi_width int,
  10         oi_height int,
  11         oi_bits int,
  12         oi_description nvarchar(255),
  13         oi_user int,
  14         oi_user_text nvarchar(255),
  15         oi_timestamp varchar(14),
  16         oi_metadata nvarchar(max),
  17         oi_media_type varchar(16),
  18         oi_major_mime varchar(16),
  19         oi_minor_mime nvarchar(100),
  20         oi_deleted tinyint,
  21         oi_sha1 nvarchar(32)
  22 );--
  23
  24 INSERT INTO @temp
  25 SELECT * FROM /*_*/oldimage;--
  26
  27 DROP TABLE /*_*/oldimage;--
  28
  29 CREATE TABLE /*_*/oldimage (
  30   oi_name nvarchar(255) NOT NULL default '',
  31   oi_archive_name nvarchar(255) NOT NULL default '',
  32   oi_size int NOT NULL default 0,
  33   oi_width int NOT NULL default 0,
  34   oi_height int NOT NULL default 0,
  35   oi_bits int NOT NULL default 0,
  36   oi_description nvarchar(255) NOT NULL,
  37   oi_user int REFERENCES /*_*/mwuser(user_id),
  38   oi_user_text nvarchar(255) NOT NULL,
  39   oi_timestamp varchar(14) NOT NULL default '',
  40   oi_metadata varbinary(max) NOT NULL,
  41   oi_media_type varchar(16) default null,
  42   oi_major_mime varchar(16) not null default 'unknown',
  43   oi_minor_mime nvarchar(100) NOT NULL default 'unknown',
  44   oi_deleted tinyint NOT NULL default 0,
  45   oi_sha1 nvarchar(32) NOT NULL default '',
  46   CONSTRAINT oi_major_mime_ckc check (oi_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
  47   CONSTRAINT oi_media_type_ckc check (oi_media_type IN('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
  48 );--
  49
  50 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text, oi_timestamp);--
  51 CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name, oi_timestamp);--
  52 CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name, oi_archive_name);--
  53 CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1);--
  54
  55 INSERT INTO /*_*/oldimage
  56 (
  57         oi_name,
  58         oi_archive_name,
  59         oi_size,
  60         oi_width,
  61         oi_height,
  62         oi_bits,
  63         oi_description,
  64         oi_user,
  65         oi_user_text,
  66         oi_timestamp,
  67         oi_metadata,
  68         oi_media_type,
  69         oi_major_mime,
  70         oi_minor_mime,
  71         oi_deleted,
  72         oi_sha1
  73 )
  74 SELECT
  75         oi_name,
  76         oi_archive_name,
  77         oi_size,
  78         oi_width,
  79         oi_height,
  80         oi_bits,
  81         oi_description,
  82         oi_user,
  83         oi_user_text,
  84         oi_timestamp,
  85         CONVERT(varbinary(max), oi_metadata, 0),
  86         oi_media_type,
  87         oi_major_mime,
  88         oi_minor_mime,
  89         oi_deleted,
  90         oi_sha1
  91 FROM @temp t;