schema: Introduce file table

First step in reworking storage of files metadata in the database

Bug: T368113
Change-Id: I6e79c47f9b6a191a3599db5f0a1a44e73b89be22

2 files changed, 109 insertions, 0 deletions

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
+);