schema: Introduce file table

First step in reworking storage of files metadata in the database

Bug: T368113
Change-Id: I6e79c47f9b6a191a3599db5f0a1a44e73b89be22

7 files changed, 561 insertions, 0 deletions

diff --git a/sql/mysql/patch-file.sql b/sql/mysql/patch-file.sql
new file mode 100644
index 000000000000..7fce3c433eda
--- /dev/null
+++ b/sql/mysql/patch-file.sql
@@ -0,0 +1,48 @@
+
+CREATE TABLE /*_*/file (
+  file_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  file_name VARBINARY(255) NOT NULL,
+  file_latest BIGINT UNSIGNED NOT NULL,
+  file_type SMALLINT UNSIGNED NOT NULL,
+  file_deleted SMALLINT UNSIGNED NOT NULL,
+  UNIQUE INDEX file_name (file_name),
+  INDEX file_latest (file_latest),
+  PRIMARY KEY(file_id)
+) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/filerevision (
+  fr_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  fr_file BIGINT NOT NULL,
+  fr_size BIGINT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_width INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_height INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_metadata MEDIUMBLOB NOT NULL,
+  fr_bits INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT UNSIGNED NOT NULL,
+  fr_actor BIGINT UNSIGNED NOT NULL,
+  fr_timestamp BINARY(14) NOT NULL,
+  fr_sha1 VARBINARY(32) DEFAULT '' NOT NULL,
+  fr_archive_name VARBINARY(255) DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT UNSIGNED NOT NULL,
+  INDEX fr_actor_timestamp (fr_actor, fr_timestamp),
+  INDEX fr_size (fr_size),
+  INDEX fr_timestamp (fr_timestamp),
+  INDEX fr_sha1 (
+    fr_sha1(10)
+  ),
+  INDEX fr_file (fr_file),
+  PRIMARY KEY(fr_id)
+) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/filetypes (
+  ft_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  ft_media_type VARBINARY(255) NOT NULL,
+  ft_major_mime VARBINARY(255) NOT NULL,
+  ft_minor_mime VARBINARY(255) NOT NULL,
+  UNIQUE INDEX ft_media_mime (
+    ft_media_type, ft_major_mime, ft_minor_mime
+  ),
+  PRIMARY KEY(ft_id)
+) /*$wgDBTableOptions*/;
diff --git a/sql/mysql/tables-generated.sql b/sql/mysql/tables-generated.sql
index 09503a07c785..7696eb81f384 100644
--- a/sql/mysql/tables-generated.sql
+++ b/sql/mysql/tables-generated.sql
@@ -874,3 +874,52 @@ CREATE TABLE /*_*/linktarget (
   UNIQUE INDEX lt_namespace_title (lt_namespace, lt_title),
   PRIMARY KEY(lt_id)
 ) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/file (
+  file_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  file_name VARBINARY(255) NOT NULL,
+  file_latest BIGINT UNSIGNED NOT NULL,
+  file_type SMALLINT UNSIGNED NOT NULL,
+  file_deleted SMALLINT UNSIGNED NOT NULL,
+  UNIQUE INDEX file_name (file_name),
+  INDEX file_latest (file_latest),
+  PRIMARY KEY(file_id)
+) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/filerevision (
+  fr_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  fr_file BIGINT NOT NULL,
+  fr_size BIGINT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_width INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_height INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_metadata MEDIUMBLOB NOT NULL,
+  fr_bits INT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT UNSIGNED NOT NULL,
+  fr_actor BIGINT UNSIGNED NOT NULL,
+  fr_timestamp BINARY(14) NOT NULL,
+  fr_sha1 VARBINARY(32) DEFAULT '' NOT NULL,
+  fr_archive_name VARBINARY(255) DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT UNSIGNED NOT NULL,
+  INDEX fr_actor_timestamp (fr_actor, fr_timestamp),
+  INDEX fr_size (fr_size),
+  INDEX fr_timestamp (fr_timestamp),
+  INDEX fr_sha1 (
+    fr_sha1(10)
+  ),
+  INDEX fr_file (fr_file),
+  PRIMARY KEY(fr_id)
+) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/filetypes (
+  ft_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  ft_media_type VARBINARY(255) NOT NULL,
+  ft_major_mime VARBINARY(255) NOT NULL,
+  ft_minor_mime VARBINARY(255) NOT NULL,
+  UNIQUE INDEX ft_media_mime (
+    ft_media_type, ft_major_mime, ft_minor_mime
+  ),
+  PRIMARY KEY(ft_id)
+) /*$wgDBTableOptions*/;
diff --git a/sql/postgres/patch-file.sql b/sql/postgres/patch-file.sql
new file mode 100644
index 000000000000..332cc8df49e3
--- /dev/null
+++ b/sql/postgres/patch-file.sql
@@ -0,0 +1,54 @@
+
+CREATE TABLE file (
+  file_id BIGSERIAL NOT NULL,
+  file_name TEXT NOT NULL,
+  file_latest BIGINT NOT NULL,
+  file_type SMALLINT NOT NULL,
+  file_deleted SMALLINT NOT NULL,
+  PRIMARY KEY(file_id)
+);
+
+CREATE UNIQUE INDEX file_name ON file (file_name);
+
+CREATE INDEX file_latest ON file (file_latest);
+
+
+CREATE TABLE filerevision (
+  fr_id BIGSERIAL NOT NULL,
+  fr_file BIGINT NOT NULL,
+  fr_size BIGINT DEFAULT 0 NOT NULL,
+  fr_width INT DEFAULT 0 NOT NULL,
+  fr_height INT DEFAULT 0 NOT NULL,
+  fr_metadata TEXT NOT NULL,
+  fr_bits INT DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT NOT NULL,
+  fr_actor BIGINT NOT NULL,
+  fr_timestamp TIMESTAMPTZ NOT NULL,
+  fr_sha1 TEXT DEFAULT '' NOT NULL,
+  fr_archive_name TEXT DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT NOT NULL,
+  PRIMARY KEY(fr_id)
+);
+
+CREATE INDEX fr_actor_timestamp ON filerevision (fr_actor, fr_timestamp);
+
+CREATE INDEX fr_size ON filerevision (fr_size);
+
+CREATE INDEX fr_timestamp ON filerevision (fr_timestamp);
+
+CREATE INDEX fr_sha1 ON filerevision (fr_sha1);
+
+CREATE INDEX fr_file ON filerevision (fr_file);
+
+
+CREATE TABLE filetypes (
+  ft_id SMALLSERIAL NOT NULL,
+  ft_media_type TEXT NOT NULL,
+  ft_major_mime TEXT NOT NULL,
+  ft_minor_mime TEXT NOT NULL,
+  PRIMARY KEY(ft_id)
+);
+
+CREATE UNIQUE INDEX ft_media_mime ON filetypes (
+  ft_media_type, ft_major_mime, ft_minor_mime
+);
diff --git a/sql/postgres/tables-generated.sql b/sql/postgres/tables-generated.sql
index dc5176ee900b..45c563cd6d8c 100644
--- a/sql/postgres/tables-generated.sql
+++ b/sql/postgres/tables-generated.sql
@@ -938,3 +938,58 @@ CREATE TABLE linktarget (
 );
 
 CREATE UNIQUE INDEX lt_namespace_title ON linktarget (lt_namespace, lt_title);
+
+
+CREATE TABLE file (
+  file_id BIGSERIAL NOT NULL,
+  file_name TEXT NOT NULL,
+  file_latest BIGINT NOT NULL,
+  file_type SMALLINT NOT NULL,
+  file_deleted SMALLINT NOT NULL,
+  PRIMARY KEY(file_id)
+);
+
+CREATE UNIQUE INDEX file_name ON file (file_name);
+
+CREATE INDEX file_latest ON file (file_latest);
+
+
+CREATE TABLE filerevision (
+  fr_id BIGSERIAL NOT NULL,
+  fr_file BIGINT NOT NULL,
+  fr_size BIGINT DEFAULT 0 NOT NULL,
+  fr_width INT DEFAULT 0 NOT NULL,
+  fr_height INT DEFAULT 0 NOT NULL,
+  fr_metadata TEXT NOT NULL,
+  fr_bits INT DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT NOT NULL,
+  fr_actor BIGINT NOT NULL,
+  fr_timestamp TIMESTAMPTZ NOT NULL,
+  fr_sha1 TEXT DEFAULT '' NOT NULL,
+  fr_archive_name TEXT DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT NOT NULL,
+  PRIMARY KEY(fr_id)
+);
+
+CREATE INDEX fr_actor_timestamp ON filerevision (fr_actor, fr_timestamp);
+
+CREATE INDEX fr_size ON filerevision (fr_size);
+
+CREATE INDEX fr_timestamp ON filerevision (fr_timestamp);
+
+CREATE INDEX fr_sha1 ON filerevision (fr_sha1);
+
+CREATE INDEX fr_file ON filerevision (fr_file);
+
+
+CREATE TABLE filetypes (
+  ft_id SMALLSERIAL NOT NULL,
+  ft_media_type TEXT NOT NULL,
+  ft_major_mime TEXT NOT NULL,
+  ft_minor_mime TEXT NOT NULL,
+  PRIMARY KEY(ft_id)
+);
+
+CREATE UNIQUE INDEX ft_media_mime ON filetypes (
+  ft_media_type, ft_major_mime, ft_minor_mime
+);
diff --git a/sql/sqlite/patch-file.sql b/sql/sqlite/patch-file.sql
new file mode 100644
index 000000000000..5b711270ab46
--- /dev/null
+++ b/sql/sqlite/patch-file.sql
@@ -0,0 +1,46 @@
+
+CREATE TABLE /*_*/file (
+  file_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  file_name BLOB NOT NULL, file_latest BIGINT UNSIGNED NOT NULL,
+  file_type SMALLINT UNSIGNED NOT NULL,
+  file_deleted SMALLINT UNSIGNED NOT NULL
+);
+
+CREATE UNIQUE INDEX file_name ON /*_*/file (file_name);
+
+CREATE INDEX file_latest ON /*_*/file (file_latest);
+
+
+CREATE TABLE /*_*/filerevision (
+  fr_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  fr_file BIGINT NOT NULL, fr_size BIGINT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_width INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_height INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_metadata BLOB NOT NULL, fr_bits INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT UNSIGNED NOT NULL,
+  fr_actor BIGINT UNSIGNED NOT NULL,
+  fr_timestamp BLOB NOT NULL, fr_sha1 BLOB DEFAULT '' NOT NULL,
+  fr_archive_name BLOB DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT UNSIGNED NOT NULL
+);
+
+CREATE INDEX fr_actor_timestamp ON /*_*/filerevision (fr_actor, fr_timestamp);
+
+CREATE INDEX fr_size ON /*_*/filerevision (fr_size);
+
+CREATE INDEX fr_timestamp ON /*_*/filerevision (fr_timestamp);
+
+CREATE INDEX fr_sha1 ON /*_*/filerevision (fr_sha1);
+
+CREATE INDEX fr_file ON /*_*/filerevision (fr_file);
+
+
+CREATE TABLE /*_*/filetypes (
+  ft_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  ft_media_type BLOB NOT NULL, ft_major_mime BLOB NOT NULL,
+  ft_minor_mime BLOB NOT NULL
+);
+
+CREATE UNIQUE INDEX ft_media_mime ON /*_*/filetypes (
+  ft_media_type, ft_major_mime, ft_minor_mime
+);
diff --git a/sql/sqlite/tables-generated.sql b/sql/sqlite/tables-generated.sql
index cd1a747afb9c..c558b59fd381 100644
--- a/sql/sqlite/tables-generated.sql
+++ b/sql/sqlite/tables-generated.sql
@@ -869,3 +869,50 @@ CREATE TABLE /*_*/linktarget (
 );
 
 CREATE UNIQUE INDEX lt_namespace_title ON /*_*/linktarget (lt_namespace, lt_title);
+
+
+CREATE TABLE /*_*/file (
+  file_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  file_name BLOB NOT NULL, file_latest BIGINT UNSIGNED NOT NULL,
+  file_type SMALLINT UNSIGNED NOT NULL,
+  file_deleted SMALLINT UNSIGNED NOT NULL
+);
+
+CREATE UNIQUE INDEX file_name ON /*_*/file (file_name);
+
+CREATE INDEX file_latest ON /*_*/file (file_latest);
+
+
+CREATE TABLE /*_*/filerevision (
+  fr_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  fr_file BIGINT NOT NULL, fr_size BIGINT UNSIGNED DEFAULT 0 NOT NULL,
+  fr_width INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_height INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_metadata BLOB NOT NULL, fr_bits INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+  fr_description_id BIGINT UNSIGNED NOT NULL,
+  fr_actor BIGINT UNSIGNED NOT NULL,
+  fr_timestamp BLOB NOT NULL, fr_sha1 BLOB DEFAULT '' NOT NULL,
+  fr_archive_name BLOB DEFAULT '' NOT NULL,
+  fr_deleted SMALLINT UNSIGNED NOT NULL
+);
+
+CREATE INDEX fr_actor_timestamp ON /*_*/filerevision (fr_actor, fr_timestamp);
+
+CREATE INDEX fr_size ON /*_*/filerevision (fr_size);
+
+CREATE INDEX fr_timestamp ON /*_*/filerevision (fr_timestamp);
+
+CREATE INDEX fr_sha1 ON /*_*/filerevision (fr_sha1);
+
+CREATE INDEX fr_file ON /*_*/filerevision (fr_file);
+
+
+CREATE TABLE /*_*/filetypes (
+  ft_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  ft_media_type BLOB NOT NULL, ft_major_mime BLOB NOT NULL,
+  ft_minor_mime BLOB NOT NULL
+);
+
+CREATE UNIQUE INDEX ft_media_mime ON /*_*/filetypes (
+  ft_media_type, ft_major_mime, ft_minor_mime
+);
diff --git a/sql/tables.json b/sql/tables.json
index 5a1bd74e0165..a557a287da17 100644
--- a/sql/tables.json
+++ b/sql/tables.json
@@ -3715,5 +3715,267 @@
 			{ "name": "lt_namespace_title", "columns": [ "lt_namespace", "lt_title" ], "unique": true }
 		],
 		"pk": [ "lt_id" ]
+	},
+	{
+		"name": "file",
+		"comment": "Uploaded images and other files.",
+		"columns": [
+			{
+				"name": "file_id",
+				"comment": "primary key",
+				"type": "bigint",
+				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
+			},
+			{
+				"name": "file_name",
+				"comment": "Name of the file",
+				"type": "binary",
+				"options": { "length": 255, "notnull": true }
+			},
+			{
+				"name": "file_latest",
+				"comment": "FK to fr_id",
+				"type": "bigint",
+				"options": { "unsigned": true, "notnull": true }
+			},
+			{
+				"name": "file_type",
+				"comment": "FK to file_types.ft_id",
+				"type": "smallint",
+				"options": { "unsigned": true, "notnull": true }
+			},
+			{
+				"name": "file_deleted",
+				"comment": "Whether the file is deleted",
+				"type": "smallint",
+				"options": { "unsigned": true, "notnull": true }
+			}
+		],
+		"indexes": [
+			{
+				"name": "file_name",
+				"comment": "To find files based on their name",
+				"columns": [
+					"file_name"
+				],
+				"unique": true
+			},
+			{
+				"name": "file_latest",
+				"comment": "To allow join with filerevision table",
+				"columns": [
+					"file_latest"
+				],
+				"unique": false
+			}
+		],
+		"pk": [ "file_id" ]
+	},
+	{
+		"name": "filerevision",
+		"comment": "Revisions of the files",
+		"columns": [
+			{
+				"name": "fr_id",
+				"comment": "primary key",
+				"type": "bigint",
+				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
+			},
+			{
+				"name": "fr_file",
+				"comment": "FK to file_id",
+				"type": "bigint",
+				"options": { "notnull": true }
+			},
+			{
+				"name": "fr_size",
+				"comment": "File size in bytes.",
+				"type": "bigint",
+				"options": {
+					"unsigned": true,
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "fr_width",
+				"comment": "For images, width in pixels.",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "fr_height",
+				"comment": "For images, height in pixels.",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "fr_metadata",
+				"comment": "Extracted Exif metadata stored as a json array (new system) or serialized PHP array (old system). The json array can contain an address in the text table or external storage.",
+				"type": "blob",
+				"options": {
+					"notnull": true,
+					"length": 16777215
+				}
+			},
+			{
+				"name": "fr_bits",
+				"comment": "For images, bits per pixel if known.",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "fr_description_id",
+				"comment": "Foreign key to comment table, which contains the description field as entered by the uploader. This is displayed in image upload history and logs.",
+				"type": "bigint",
+				"options": {
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "fr_actor",
+				"comment": "actor_id of the uploader.",
+				"type": "bigint",
+				"options": {
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "fr_timestamp",
+				"comment": "Time of the upload.",
+				"type": "mwtimestamp",
+				"options": {
+					"notnull": true
+				}
+			},
+			{
+				"name": "fr_sha1",
+				"comment": "SHA-1 content hash in base-36",
+				"type": "binary",
+				"options": {
+					"notnull": true,
+					"default": "",
+					"length": 32
+				}
+			},
+			{
+				"name": "fr_archive_name",
+				"comment": "Filename of the archived file. This is generally a timestamp and '!' prepended to the base name.",
+				"type": "binary",
+				"options": {
+					"notnull": true,
+					"default": "",
+					"length": 255
+				}
+			},
+			{
+				"name": "fr_deleted",
+				"comment": "Whether the file is deleted",
+				"type": "smallint",
+				"options": { "unsigned": true, "notnull": true }
+			}
+		],
+		"indexes": [
+			{
+				"name": "fr_actor_timestamp",
+				"comment": "Used by Special:Newimages and ApiQueryAllImages",
+				"columns": [
+					"fr_actor",
+					"fr_timestamp"
+				],
+				"unique": false
+			},
+			{
+				"name": "fr_size",
+				"comment": "Used by Special:ListFiles for sort-by-size",
+				"columns": [
+					"fr_size"
+				],
+				"unique": false
+			},
+			{
+				"name": "fr_timestamp",
+				"comment": "Used by Special:Newimages and Special:ListFiles",
+				"columns": [
+					"fr_timestamp"
+				],
+				"unique": false
+			},
+			{
+				"name": "fr_sha1",
+				"comment": "Used in API and duplicate search",
+				"columns": [
+					"fr_sha1"
+				],
+				"unique": false,
+				"options": { "lengths": [ 10 ] }
+			},
+			{
+				"name": "fr_file",
+				"comment": "To find all filerevisions belonging to one file",
+				"columns": [
+					"fr_file"
+				],
+				"unique": false
+			}
+		],
+		"pk": [ "fr_id" ]
+	},
+	{
+		"name": "filetypes",
+		"comment": "Valid file types",
+		"columns": [
+			{
+				"name": "ft_id",
+				"comment": "primary key",
+				"type": "smallint",
+				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
+			},
+			{
+				"name": "ft_media_type",
+				"comment": "Media type as defined by the MEDIATYPE_xxx constants",
+				"type": "binary",
+				"options": { "length": 255, "notnull": true }
+			},
+			{
+				"name": "ft_major_mime",
+				"comment": "major part of a MIME media type as defined by IANA see https://www.iana.org/assignments/media-types/ for \"chemical\" cf. http://dx.doi.org/10.1021/ci9803233 by the ACS",
+				"type": "binary",
+				"options": { "length": 255, "notnull": true }
+			},
+			{
+				"name": "ft_minor_mime",
+				"comment": "minor part of a MIME media type as defined by IANA the minor parts are not required to adhere to any standard but should be consistent throughout the database see https://www.iana.org/assignments/media-types/",
+				"type": "binary",
+				"options": { "length": 255, "notnull": true }
+			}
+		],
+		"indexes": [
+			{
+				"name": "ft_media_mime",
+				"comment": "Used to get media of one type",
+				"columns": [
+					"ft_media_type",
+					"ft_major_mime",
+					"ft_minor_mime"
+				],
+				"unique": true
+			}
+		],
+		"pk": [ "ft_id" ]
 	}
 ]