maintenance/mssql/archives/patch-filearchive-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         fa_id int,
   7         fa_name nvarchar(255),
   8         fa_archive_name nvarchar(255),
   9         fa_storage_group nvarchar(16),
  10         fa_storage_key nvarchar(64),
  11         fa_deleted_user int,
  12         fa_deleted_timestamp varchar(14),
  13         fa_deleted_reason nvarchar(max),
  14         fa_size int,
  15         fa_width int,
  16         fa_height int,
  17         fa_metadata nvarchar(max),
  18         fa_bits int,
  19         fa_media_type varchar(16),
  20         fa_major_mime varchar(16),
  21         fa_minor_mime nvarchar(100),
  22         fa_description nvarchar(255),
  23         fa_user int,
  24         fa_user_text nvarchar(255),
  25         fa_timestamp varchar(14),
  26         fa_deleted tinyint,
  27         fa_sha1 nvarchar(32)
  28 );--
  29
  30 INSERT INTO @temp
  31 SELECT * FROM /*_*/filearchive;--
  32
  33 DROP TABLE /*_*/filearchive;--
  34
  35 CREATE TABLE /*_*/filearchive (
  36   fa_id int NOT NULL PRIMARY KEY IDENTITY,
  37   fa_name nvarchar(255) NOT NULL default '',
  38   fa_archive_name nvarchar(255) default '',
  39   fa_storage_group nvarchar(16),
  40   fa_storage_key nvarchar(64) default '',
  41   fa_deleted_user int,
  42   fa_deleted_timestamp varchar(14) default '',
  43   fa_deleted_reason nvarchar(max),
  44   fa_size int default 0,
  45   fa_width int default 0,
  46   fa_height int default 0,
  47   fa_metadata varbinary(max),
  48   fa_bits int default 0,
  49   fa_media_type varchar(16) default null,
  50   fa_major_mime varchar(16) not null default 'unknown',
  51   fa_minor_mime nvarchar(100) default 'unknown',
  52   fa_description nvarchar(255),
  53   fa_user int default 0 REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
  54   fa_user_text nvarchar(255),
  55   fa_timestamp varchar(14) default '',
  56   fa_deleted tinyint NOT NULL default 0,
  57   fa_sha1 nvarchar(32) NOT NULL default '',
  58   CONSTRAINT fa_major_mime_ckc check (fa_major_mime in('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
  59   CONSTRAINT fa_media_type_ckc check (fa_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
  60 );--
  61
  62 CREATE INDEX /*i*/fa_name ON /*_*/filearchive (fa_name, fa_timestamp);--
  63 CREATE INDEX /*i*/fa_storage_group ON /*_*/filearchive (fa_storage_group, fa_storage_key);--
  64 CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp);--
  65 CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);--
  66 CREATE INDEX /*i*/fa_sha1 ON /*_*/filearchive (fa_sha1);--
  67
  68 SET IDENTITY_INSERT /*_*/filearchive ON;--
  69
  70 INSERT INTO /*_*/filearchive
  71 (
  72         fa_id,
  73         fa_name,
  74         fa_archive_name,
  75         fa_storage_group,
  76         fa_storage_key,
  77         fa_deleted_user,
  78         fa_deleted_timestamp,
  79         fa_deleted_reason,
  80         fa_size,
  81         fa_width,
  82         fa_height,
  83         fa_metadata,
  84         fa_bits,
  85         fa_media_type,
  86         fa_major_mime,
  87         fa_minor_mime,
  88         fa_description,
  89         fa_user,
  90         fa_user_text,
  91         fa_timestamp,
  92         fa_deleted,
  93         fa_sha1
  94 )
  95 SELECT
  96         fa_id,
  97         fa_name,
  98         fa_archive_name,
  99         fa_storage_group,
 100         fa_storage_key,
 101         fa_deleted_user,
 102         fa_deleted_timestamp,
 103         fa_deleted_reason,
 104         fa_size,
 105         fa_width,
 106         fa_height,
 107         CONVERT(varbinary(max), fa_metadata, 0),
 108         fa_bits,
 109         fa_media_type,
 110         fa_major_mime,
 111         fa_minor_mime,
 112         fa_description,
 113         fa_user,
 114         fa_user_text,
 115         fa_timestamp,
 116         fa_deleted,
 117         fa_sha1
 118 FROM @temp t;--
 119
 120 SET IDENTITY_INSERT /*_*/filearchive OFF;