From 943c724198f1226286c7cefc0cbf80675240bf07 Mon Sep 17 00:00:00 2001 From: daniel Date: Mon, 18 Sep 2017 17:00:26 +0200 Subject: [PATCH] MCR database schema Schema additions/updates for MySQL, SQLite, PostgreSQL, MSSQL and Oracle to add new tables: * content * content_models * slots * slot_roles See also https://www.mediawiki.org/wiki/Multi-Content_Revisions/Database_Schema Bug: T174028 Change-Id: I30a3a9834d54d0e6957553d91908b2b73b2c802f --- includes/installer/MssqlUpdater.php | 6 ++ includes/installer/MysqlUpdater.php | 6 ++ includes/installer/OracleUpdater.php | 6 ++ includes/installer/PostgresUpdater.php | 6 ++ includes/installer/SqliteUpdater.php | 6 ++ maintenance/archives/patch-content.sql | 21 ++++++ maintenance/archives/patch-content_models.sql | 10 +++ maintenance/archives/patch-slot_roles.sql | 10 +++ maintenance/archives/patch-slots.sql | 24 +++++++ maintenance/mssql/archives/patch-content.sql | 21 ++++++ .../mssql/archives/patch-content_models.sql | 11 +++ .../mssql/archives/patch-slot_roles.sql | 10 +++ maintenance/mssql/archives/patch-slots.sql | 24 +++++++ maintenance/mssql/tables.sql | 70 ++++++++++++++++++ maintenance/oracle/archives/patch-content.sql | 18 +++++ .../oracle/archives/patch-content_models.sql | 18 +++++ .../oracle/archives/patch-slot_roles.sql | 17 +++++ maintenance/oracle/archives/patch-slots.sql | 10 +++ maintenance/oracle/tables.sql | 72 +++++++++++++++++++ .../postgres/archives/patch-content-table.sql | 8 +++ .../archives/patch-content_models-table.sql | 7 ++ .../archives/patch-slot_roles-table.sql | 7 ++ .../postgres/archives/patch-slots-table.sql | 9 +++ maintenance/postgres/tables.sql | 39 ++++++++++ maintenance/tables.sql | 68 ++++++++++++++++++ 25 files changed, 504 insertions(+) create mode 100644 maintenance/archives/patch-content.sql create mode 100644 maintenance/archives/patch-content_models.sql create mode 100644 maintenance/archives/patch-slot_roles.sql create mode 100644 maintenance/archives/patch-slots.sql create mode 100644 maintenance/mssql/archives/patch-content.sql create mode 100644 maintenance/mssql/archives/patch-content_models.sql create mode 100644 maintenance/mssql/archives/patch-slot_roles.sql create mode 100644 maintenance/mssql/archives/patch-slots.sql create mode 100644 maintenance/oracle/archives/patch-content.sql create mode 100644 maintenance/oracle/archives/patch-content_models.sql create mode 100644 maintenance/oracle/archives/patch-slot_roles.sql create mode 100644 maintenance/oracle/archives/patch-slots.sql create mode 100644 maintenance/postgres/archives/patch-content-table.sql create mode 100644 maintenance/postgres/archives/patch-content_models-table.sql create mode 100644 maintenance/postgres/archives/patch-slot_roles-table.sql create mode 100644 maintenance/postgres/archives/patch-slots-table.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 411d2c8cb2..cb7a6ba895 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -105,6 +105,12 @@ class MssqlUpdater extends DatabaseUpdater { // 1.30 [ 'modifyField', 'image', 'img_media_type', 'patch-add-3d.sql' ], [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ], + + // 1.31 + [ 'addTable', 'slots', 'patch-slots.sql' ], + [ 'addTable', 'content', 'patch-content.sql' ], + [ 'addTable', 'slot_roles', 'patch-slot_roles.sql' ], + [ 'addTable', 'content_models', 'patch-content_models.sql' ], ]; } diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index 466ad0f074..bc7725e93f 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -329,6 +329,12 @@ class MysqlUpdater extends DatabaseUpdater { [ 'renameIndex', 'l10n_cache', 'lc_lang_key', 'PRIMARY', false, 'patch-l10n_cache-primary-key.sql' ], [ 'doUnsignedSyncronisation' ], + + // 1.31 + [ 'addTable', 'slots', 'patch-slots.sql' ], + [ 'addTable', 'content', 'patch-content.sql' ], + [ 'addTable', 'slot_roles', 'patch-slot_roles.sql' ], + [ 'addTable', 'content_models', 'patch-content_models.sql' ], ]; } diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 040b54a124..67150ee7b1 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -127,6 +127,12 @@ class OracleUpdater extends DatabaseUpdater { [ 'doAutoIncrementTriggers' ], [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ], + // 1.31 + [ 'addTable', 'slots', 'patch-slots.sql' ], + [ 'addTable', 'content', 'patch-content.sql' ], + [ 'addTable', 'slot_roles', 'patch-slot_roles.sql' ], + [ 'addTable', 'content_models', 'patch-content_models.sql' ], + // KEEP THIS AT THE BOTTOM!! [ 'doRebuildDuplicateFunction' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 393c2e1641..e920fb7ae0 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -483,6 +483,12 @@ class PostgresUpdater extends DatabaseUpdater { [ 'addTable', 'comment', 'patch-comment-table.sql' ], [ 'addIndex', 'site_stats', 'site_stats_pkey', 'patch-site_stats-pk.sql' ], [ 'addTable', 'ip_changes', 'patch-ip_changes.sql' ], + + // 1.31 + [ 'addTable', 'slots', 'patch-slots-table.sql' ], + [ 'addTable', 'content', 'patch-content-table.sql' ], + [ 'addTable', 'content_moddels', 'patch-content_models-table.sql' ], + [ 'addTable', 'slot_roles', 'patch-slot_roles-table.sql' ], ]; } diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 9f71001441..88dfa6cf4e 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -193,6 +193,12 @@ class SqliteUpdater extends DatabaseUpdater { [ 'migrateComments' ], [ 'renameIndex', 'l10n_cache', 'lc_lang_key', 'PRIMARY', false, 'patch-l10n_cache-primary-key.sql' ], + + // 1.31 + [ 'addTable', 'content', 'patch-content.sql' ], + [ 'addTable', 'content_models', 'patch-content_models.sql' ], + [ 'addTable', 'slots', 'patch-slots.sql' ], + [ 'addTable', 'slot_roles', 'patch-slot_roles.sql' ] ]; } diff --git a/maintenance/archives/patch-content.sql b/maintenance/archives/patch-content.sql new file mode 100644 index 0000000000..2cc4de8ce1 --- /dev/null +++ b/maintenance/archives/patch-content.sql @@ -0,0 +1,21 @@ +-- +-- The content table represents content objects. It's primary purpose is to provide the necessary +-- meta-data for loading and interpreting a serialized data blob to create a content object. +-- +CREATE TABLE /*_*/content ( + + -- ID of the content object + content_id bigint unsigned PRIMARY KEY AUTO_INCREMENT, + + -- Nominal size of the content object (not necessarily of the serialized blob) + content_size int unsigned NOT NULL, + + -- Nominal hash of the content object (not necessarily of the serialized blob) + content_sha1 varbinary(32) NOT NULL, + + -- reference to model_id + content_model smallint unsigned NOT NULL, + + -- URL-like address of the content blob + content_address varbinary(255) NOT NULL +) /*$wgDBTableOptions*/; \ No newline at end of file diff --git a/maintenance/archives/patch-content_models.sql b/maintenance/archives/patch-content_models.sql new file mode 100644 index 0000000000..12c4c5bbf0 --- /dev/null +++ b/maintenance/archives/patch-content_models.sql @@ -0,0 +1,10 @@ +-- +-- Normalization table for content model names +-- +CREATE TABLE /*_*/content_models ( + model_id smallint PRIMARY KEY AUTO_INCREMENT, + model_name varbinary(64) NOT NULL +) /*$wgDBTableOptions*/; + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name); \ No newline at end of file diff --git a/maintenance/archives/patch-slot_roles.sql b/maintenance/archives/patch-slot_roles.sql new file mode 100644 index 0000000000..0b13caa8d0 --- /dev/null +++ b/maintenance/archives/patch-slot_roles.sql @@ -0,0 +1,10 @@ +-- +-- Normalization table for role names +-- +CREATE TABLE /*_*/slot_roles ( + role_id smallint PRIMARY KEY AUTO_INCREMENT, + role_name varbinary(64) NOT NULL +) /*$wgDBTableOptions*/; + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name); \ No newline at end of file diff --git a/maintenance/archives/patch-slots.sql b/maintenance/archives/patch-slots.sql new file mode 100644 index 0000000000..1a51bb9ef3 --- /dev/null +++ b/maintenance/archives/patch-slots.sql @@ -0,0 +1,24 @@ +-- +-- Slots represent an n:m relation between revisions and content objects. +-- A content object can have a specific "role" in one or more revisions. +-- Each revision can have multiple content objects, each having a different role. +-- +CREATE TABLE /*_*/slots ( + + -- reference to rev_id + slot_revision_id bigint unsigned NOT NULL, + + -- reference to role_id + slot_role_id smallint unsigned NOT NULL, + + -- reference to content_id + slot_content_id bigint unsigned NOT NULL, + + -- whether the content is inherited (1) or new in this revision (0) + slot_inherited tinyint unsigned NOT NULL DEFAULT 0, + + PRIMARY KEY ( slot_revision_id, slot_role_id ) +) /*$wgDBTableOptions*/; + +-- Index for finding revisions that modified a specific slot +CREATE INDEX /*i*/slot_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-content.sql b/maintenance/mssql/archives/patch-content.sql new file mode 100644 index 0000000000..c5b079ab58 --- /dev/null +++ b/maintenance/mssql/archives/patch-content.sql @@ -0,0 +1,21 @@ +-- +-- The content table represents content objects. It's primary purpose is to provide the necessary +-- meta-data for loading and interpreting a serialized data blob to create a content object. +-- +CREATE TABLE /*_*/content ( + + -- ID of the content object + content_id bigint unsigned NOT NULL CONSTRAINT PK_content PRIMARY KEY IDENTITY, + + -- Nominal size of the content object (not necessarily of the serialized blob) + content_size int unsigned NOT NULL, + + -- Nominal hash of the content object (not necessarily of the serialized blob) + content_sha1 varchar(32) NOT NULL, + + -- reference to model_id + content_model smallint unsigned NOT NULL CONSTRAINT FK_content_content_models FOREIGN KEY REFERENCES /*_*/content_models(model_id), + + -- URL-like address of the content blob + content_address nvarchar(255) NOT NULL +); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-content_models.sql b/maintenance/mssql/archives/patch-content_models.sql new file mode 100644 index 0000000000..b94de0b39a --- /dev/null +++ b/maintenance/mssql/archives/patch-content_models.sql @@ -0,0 +1,11 @@ + +-- +-- Normalization table for content model names +-- +CREATE TABLE /*_*/content_models ( + model_id smallint NOT NULL CONSTRAINT PK_content_models PRIMARY KEY IDENTITY, + model_name nvarchar(64) NOT NULL +); + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-slot_roles.sql b/maintenance/mssql/archives/patch-slot_roles.sql new file mode 100644 index 0000000000..228510cd03 --- /dev/null +++ b/maintenance/mssql/archives/patch-slot_roles.sql @@ -0,0 +1,10 @@ +-- +-- Normalization table for role names +-- +CREATE TABLE /*_*/slot_roles ( + role_id smallint NOT NULL CONSTRAINT PK_slot_roles PRIMARY KEY IDENTITY, + role_name nvarchar(64) NOT NULL +); + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name); \ No newline at end of file diff --git a/maintenance/mssql/archives/patch-slots.sql b/maintenance/mssql/archives/patch-slots.sql new file mode 100644 index 0000000000..91d3168238 --- /dev/null +++ b/maintenance/mssql/archives/patch-slots.sql @@ -0,0 +1,24 @@ +-- +-- Slots represent an n:m relation between revisions and content objects. +-- A content object can have a specific "role" in one or more revisions. +-- Each revision can have multiple content objects, each having a different role. +-- +CREATE TABLE /*_*/slots ( + + -- reference to rev_id + slot_revision_id bigint unsigned NOT NULL, + + -- reference to role_id + slot_role_id smallint unsigned NOT NULL CONSTRAINT FK_slots_slot_role FOREIGN KEY REFERENCES slot_roles(role_id), + + -- reference to content_id + slot_content_id bigint unsigned NOT NULL CONSTRAINT FK_slots_content_id FOREIGN KEY REFERENCES content(content_id), + + -- whether the content is inherited (1) or new in this revision (0) + slot_inherited tinyint unsigned NOT NULL CONSTRAINT DF_slot_inherited DEFAULT 0, + + CONSTRAINT PK_slots PRIMARY KEY (slot_revision_id, slot_role_id) +); + +-- Index for finding revisions that modified a specific slot +CREATE INDEX /*i*/slot_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited); \ No newline at end of file diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 119cd5b874..1d5abd0b87 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -228,6 +228,76 @@ CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timesta CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id); +-- +-- Slots represent an n:m relation between revisions and content objects. +-- A content object can have a specific "role" in one or more revisions. +-- Each revision can have multiple content objects, each having a different role. +-- +CREATE TABLE /*_*/slots ( + + -- reference to rev_id + slot_revision_id bigint unsigned NOT NULL, + + -- reference to role_id + slot_role_id smallint unsigned NOT NULL CONSTRAINT FK_slots_slot_role FOREIGN KEY REFERENCES slot_roles(role_id), + + -- reference to content_id + slot_content_id bigint unsigned NOT NULL CONSTRAINT FK_slots_content_id FOREIGN KEY REFERENCES content(content_id), + + -- whether the content is inherited (1) or new in this revision (0) + slot_inherited tinyint unsigned NOT NULL CONSTRAINT DF_slot_inherited DEFAULT 0, + + CONSTRAINT PK_slots PRIMARY KEY (slot_revision_id, slot_role_id) +); + +-- Index for finding revisions that modified a specific slot +CREATE INDEX /*i*/slot_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited); + +-- +-- The content table represents content objects. It's primary purpose is to provide the necessary +-- meta-data for loading and interpreting a serialized data blob to create a content object. +-- +CREATE TABLE /*_*/content ( + + -- ID of the content object + content_id bigint unsigned NOT NULL CONSTRAINT PK_content PRIMARY KEY IDENTITY, + + -- Nominal size of the content object (not necessarily of the serialized blob) + content_size int unsigned NOT NULL, + + -- Nominal hash of the content object (not necessarily of the serialized blob) + content_sha1 varchar(32) NOT NULL, + + -- reference to model_id + content_model smallint unsigned NOT NULL CONSTRAINT FK_content_content_models FOREIGN KEY REFERENCES /*_*/content_models(model_id), + + -- URL-like address of the content blob + content_address nvarchar(255) NOT NULL +); + +-- +-- Normalization table for role names +-- +CREATE TABLE /*_*/slot_roles ( + role_id smallint NOT NULL CONSTRAINT PK_slot_roles PRIMARY KEY IDENTITY, + role_name nvarchar(64) NOT NULL +); + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name); + +-- +-- Normalization table for content model names +-- +CREATE TABLE /*_*/content_models ( + model_id smallint NOT NULL CONSTRAINT PK_content_models PRIMARY KEY IDENTITY, + model_name nvarchar(64) NOT NULL +); + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name); + + -- -- Track page-to-page hyperlinks within the wiki. -- diff --git a/maintenance/oracle/archives/patch-content.sql b/maintenance/oracle/archives/patch-content.sql new file mode 100644 index 0000000000..17d76ae649 --- /dev/null +++ b/maintenance/oracle/archives/patch-content.sql @@ -0,0 +1,18 @@ +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE &mw_prefix.content ( + content_id NUMBER NOT NULL, + content_size NUMBER NOT NULL, + content_sha1 VARCHAR2(32) NOT NULL, + content_model NUMBER NOT NULL, + content_address VARCHAR2(255) NOT NULL +); + +ALTER TABLE &mw_prefix.content ADD CONSTRAINT &mw_prefix.content_pk PRIMARY KEY (content_id); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_seq_trg BEFORE INSERT ON &mw_prefix.content + FOR EACH ROW WHEN (new.content_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_content_id_seq.nextval, :new.content_id); +END; +/*$mw$*/ \ No newline at end of file diff --git a/maintenance/oracle/archives/patch-content_models.sql b/maintenance/oracle/archives/patch-content_models.sql new file mode 100644 index 0000000000..49b912711d --- /dev/null +++ b/maintenance/oracle/archives/patch-content_models.sql @@ -0,0 +1,18 @@ +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE &mw_prefix.content_models ( + model_id NUMBER NOT NULL, + model_name VARCHAR2(64) NOT NULL +); + + +ALTER TABLE &mw_prefix.content_models ADD CONSTRAINT &mw_prefix.content_models_pk PRIMARY KEY (model_id); + +CREATE UNIQUE INDEX &mw_prefix.model_name_u01 ON &mw_prefix.content_models (model_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_models_seq_trg BEFORE INSERT ON &mw_prefix.content_models + FOR EACH ROW WHEN (new.model_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_models_model_id_seq.nextval, :new.model_id); +END; +/*$mw$*/ \ No newline at end of file diff --git a/maintenance/oracle/archives/patch-slot_roles.sql b/maintenance/oracle/archives/patch-slot_roles.sql new file mode 100644 index 0000000000..960cfbf0dc --- /dev/null +++ b/maintenance/oracle/archives/patch-slot_roles.sql @@ -0,0 +1,17 @@ +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE &mw_prefix.slot_roles ( + role_id NUMBER NOT NULL, + role_name VARCHAR2(64) NOT NULL +); + +ALTER TABLE &mw_prefix.slot_roles ADD CONSTRAINT &mw_prefix.slot_roles_pk PRIMARY KEY (role_id); + +CREATE UNIQUE INDEX &mw_prefix.role_name_u01 ON &mw_prefix.slot_roles (role_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.slot_roles_seq_trg BEFORE INSERT ON &mw_prefix.slot_roles + FOR EACH ROW WHEN (new.role_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(slot_roles_role_id_seq.nextval, :new.role_id); +END; +/*$mw$*/ \ No newline at end of file diff --git a/maintenance/oracle/archives/patch-slots.sql b/maintenance/oracle/archives/patch-slots.sql new file mode 100644 index 0000000000..094ab68dca --- /dev/null +++ b/maintenance/oracle/archives/patch-slots.sql @@ -0,0 +1,10 @@ +CREATE TABLE &mw_prefix.slots ( + slot_revision_id NUMBER NOT NULL, + slot_role_id NUMBER NOT NULL, + slot_content_id NUMBER NOT NULL, + slot_inherited CHAR(1) DEFAULT '0' NOT NULL +); + +ALTER TABLE &mw_prefix.slots ADD CONSTRAINT &mw_prefix.slots_pk PRIMARY KEY (slot_revision_id, slot_role_id); + +CREATE INDEX &mw_prefix.slot_role_inherited ON &mw_prefix.slots (slot_revision_id, slot_role_id, slot_inherited); \ No newline at end of file diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index d588e3a67c..09d19220db 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -219,6 +219,78 @@ BEGIN END; /*$mw$*/ + +CREATE TABLE &mw_prefix.slots ( + slot_revision_id NUMBER NOT NULL, + slot_role_id NUMBER NOT NULL, + slot_content_id NUMBER NOT NULL, + slot_inherited CHAR(1) DEFAULT '0' NOT NULL +); + +ALTER TABLE &mw_prefix.slots ADD CONSTRAINT &mw_prefix.slots_pk PRIMARY KEY (slot_revision_id, slot_role_id); + +CREATE INDEX &mw_prefix.slot_role_inherited ON &mw_prefix.slots (slot_revision_id, slot_role_id, slot_inherited); + + +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE &mw_prefix.content ( + content_id NUMBER NOT NULL, + content_size NUMBER NOT NULL, + content_sha1 VARCHAR2(32) NOT NULL, + content_model NUMBER NOT NULL, + content_address VARCHAR2(255) NOT NULL +); + +ALTER TABLE &mw_prefix.content ADD CONSTRAINT &mw_prefix.content_pk PRIMARY KEY (content_id); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_seq_trg BEFORE INSERT ON &mw_prefix.content + FOR EACH ROW WHEN (new.content_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_content_id_seq.nextval, :new.content_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE &mw_prefix.slot_roles ( + role_id NUMBER NOT NULL, + role_name VARCHAR2(64) NOT NULL +); + +ALTER TABLE &mw_prefix.slot_roles ADD CONSTRAINT &mw_prefix.slot_roles_pk PRIMARY KEY (role_id); + +CREATE UNIQUE INDEX &mw_prefix.role_name_u01 ON &mw_prefix.slot_roles (role_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.slot_roles_seq_trg BEFORE INSERT ON &mw_prefix.slot_roles + FOR EACH ROW WHEN (new.role_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(slot_roles_role_id_seq.nextval, :new.role_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE &mw_prefix.content_models ( + model_id NUMBER NOT NULL, + model_name VARCHAR2(64) NOT NULL +); + + +ALTER TABLE &mw_prefix.content_models ADD CONSTRAINT &mw_prefix.content_models_pk PRIMARY KEY (model_id); + +CREATE UNIQUE INDEX &mw_prefix.model_name_u01 ON &mw_prefix.content_models (model_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_models_seq_trg BEFORE INSERT ON &mw_prefix.content_models + FOR EACH ROW WHEN (new.model_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_models_model_id_seq.nextval, :new.model_id); +END; +/*$mw$*/ + + CREATE TABLE &mw_prefix.pagelinks ( pl_from NUMBER NOT NULL, pl_namespace NUMBER DEFAULT 0 NOT NULL, diff --git a/maintenance/postgres/archives/patch-content-table.sql b/maintenance/postgres/archives/patch-content-table.sql new file mode 100644 index 0000000000..268db8bb16 --- /dev/null +++ b/maintenance/postgres/archives/patch-content-table.sql @@ -0,0 +1,8 @@ +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE content ( + content_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('content_content_id_seq'), + content_size INTEGER NOT NULL, + content_sha1 TEXT NOT NULL, + content_model SMALLINT NOT NULL, + content_address TEXT NOT NULL +); diff --git a/maintenance/postgres/archives/patch-content_models-table.sql b/maintenance/postgres/archives/patch-content_models-table.sql new file mode 100644 index 0000000000..c2509d246e --- /dev/null +++ b/maintenance/postgres/archives/patch-content_models-table.sql @@ -0,0 +1,7 @@ +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE content_models ( + model_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('content_models_model_id_seq'), + model_name TEXT NOT NULL +); + +CREATE UNIQUE INDEX model_name ON content_models (model_name); \ No newline at end of file diff --git a/maintenance/postgres/archives/patch-slot_roles-table.sql b/maintenance/postgres/archives/patch-slot_roles-table.sql new file mode 100644 index 0000000000..3e71abaf5e --- /dev/null +++ b/maintenance/postgres/archives/patch-slot_roles-table.sql @@ -0,0 +1,7 @@ +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE slot_roles ( + role_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('slot_roles_role_id_seq'), + role_name TEXT NOT NULL +); + +CREATE UNIQUE INDEX role_name ON slot_roles (role_name); \ No newline at end of file diff --git a/maintenance/postgres/archives/patch-slots-table.sql b/maintenance/postgres/archives/patch-slots-table.sql new file mode 100644 index 0000000000..9cad0d19b8 --- /dev/null +++ b/maintenance/postgres/archives/patch-slots-table.sql @@ -0,0 +1,9 @@ +CREATE TABLE slots ( + slot_revision_id INTEGER NOT NULL, + slot_role_id SMALLINT NOT NULL, + slot_content_id INTEGER NOT NULL, + slot_inherited SMALLINT NOT NULL DEFAULT 0, + PRIMARY KEY (slot_revision_id, slot_role_id) +); + +CREATE INDEX slot_role_inherited ON slots (slot_revision_id, slot_role_id, slot_inherited); \ No newline at end of file diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index da9c86486a..34de2cb2a6 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -237,6 +237,45 @@ CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_t CREATE INDEX archive_user_text ON archive (ar_user_text); +CREATE TABLE slots ( + slot_revision_id INTEGER NOT NULL, + slot_role_id SMALLINT NOT NULL, + slot_content_id INTEGER NOT NULL, + slot_inherited SMALLINT NOT NULL DEFAULT 0, + PRIMARY KEY (slot_revision_id, slot_role_id) +); + +CREATE INDEX slot_role_inherited ON slots (slot_revision_id, slot_role_id, slot_inherited); + + +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE content ( + content_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('content_content_id_seq'), + content_size INTEGER NOT NULL, + content_sha1 TEXT NOT NULL, + content_model SMALLINT NOT NULL, + content_address TEXT NOT NULL +); + + +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE slot_roles ( + role_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('slot_roles_role_id_seq'), + role_name TEXT NOT NULL +); + +CREATE UNIQUE INDEX role_name ON slot_roles (role_name); + + +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE content_models ( + model_id SMALLINT NOT NULL PRIMARY KEY DEFAULT nextval('content_models_model_id_seq'), + model_name TEXT NOT NULL +); + +CREATE UNIQUE INDEX model_name ON content_models (model_name); + + CREATE TABLE redirect ( rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, rd_namespace SMALLINT NOT NULL, diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 1813f6cdf0..b881d7e0f1 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -611,6 +611,74 @@ CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timesta -- rows, such as change_tag. CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id); +-- +-- Slots represent an n:m relation between revisions and content objects. +-- A content object can have a specific "role" in one or more revisions. +-- Each revision can have multiple content objects, each having a different role. +-- +CREATE TABLE /*_*/slots ( + + -- reference to rev_id + slot_revision_id bigint unsigned NOT NULL, + + -- reference to role_id + slot_role_id smallint unsigned NOT NULL, + + -- reference to content_id + slot_content_id bigint unsigned NOT NULL, + + -- whether the content is inherited (1) or new in this revision (0) + slot_inherited tinyint unsigned NOT NULL DEFAULT 0, + + PRIMARY KEY ( slot_revision_id, slot_role_id ) +) /*$wgDBTableOptions*/; + +-- Index for finding revisions that modified a specific slot +CREATE INDEX /*i*/slot_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited); + +-- +-- The content table represents content objects. It's primary purpose is to provide the necessary +-- meta-data for loading and interpreting a serialized data blob to create a content object. +-- +CREATE TABLE /*_*/content ( + + -- ID of the content object + content_id bigint unsigned PRIMARY KEY AUTO_INCREMENT, + + -- Nominal size of the content object (not necessarily of the serialized blob) + content_size int unsigned NOT NULL, + + -- Nominal hash of the content object (not necessarily of the serialized blob) + content_sha1 varbinary(32) NOT NULL, + + -- reference to model_id + content_model smallint unsigned NOT NULL, + + -- URL-like address of the content blob + content_address varbinary(255) NOT NULL +) /*$wgDBTableOptions*/; + +-- +-- Normalization table for role names +-- +CREATE TABLE /*_*/slot_roles ( + role_id smallint PRIMARY KEY AUTO_INCREMENT, + role_name varbinary(64) NOT NULL +) /*$wgDBTableOptions*/; + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name); + +-- +-- Normalization table for content model names +-- +CREATE TABLE /*_*/content_models ( + model_id smallint PRIMARY KEY AUTO_INCREMENT, + model_name varbinary(64) NOT NULL +) /*$wgDBTableOptions*/; + +-- Index for looking of the internal ID of for a name +CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name); -- -- Track page-to-page hyperlinks within the wiki. -- 2.20.1