-- not have to run it by itself unless doing a manual install.
-- This is the IBM DB2 version.
-- For information about each table, please see the notes in maintenance/tables.sql
--- Please make sure all dollar-quoting uses $mw$ at the start of the line
--- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code)
-
-
-CREATE SEQUENCE user_user_id_seq AS INTEGER START WITH 0 INCREMENT BY 1;
-CREATE TABLE mwuser ( -- replace reserved word 'user'
- user_id INTEGER NOT NULL PRIMARY KEY, -- DEFAULT nextval('user_user_id_seq'),
+CREATE TABLE user (
+ -- Needs to start with 0
+ user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
user_name VARCHAR(255) NOT NULL UNIQUE,
user_real_name VARCHAR(255),
- user_password clob(1K),
- user_newpassword clob(1K),
- user_newpass_time TIMESTAMP,
+ user_password VARCHAR(1024),
+ user_newpassword VARCHAR(1024),
+ user_newpass_time TIMESTAMP(3),
user_token VARCHAR(255),
- user_email VARCHAR(255),
+ user_email VARCHAR(1024),
user_email_token VARCHAR(255),
- user_email_token_expires TIMESTAMP,
- user_email_authenticated TIMESTAMP,
- user_options CLOB(64K),
- user_touched TIMESTAMP,
- user_registration TIMESTAMP,
+ user_email_token_expires TIMESTAMP(3),
+ user_email_authenticated TIMESTAMP(3),
+ -- obsolete, replace by user_properties table
+ user_options CLOB(64K) INLINE LENGTH 4096,
+ user_touched TIMESTAMP(3),
+ user_registration TIMESTAMP(3),
user_editcount INTEGER
);
-CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
+CREATE INDEX user_email_token_idx ON user (user_email_token);
+--leonsp:
+CREATE UNIQUE INDEX user_include_idx
+ ON user(user_id)
+ INCLUDE (user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_token,
+ user_email, user_email_token, user_email_token_expires, user_email_authenticated,
+ user_touched, user_registration, user_editcount);
-- Create a dummy user to satisfy fk contraints especially with revisions
-INSERT INTO mwuser
- VALUES (NEXTVAL FOR user_user_id_seq,'Anonymous','', NULL,NULL,CURRENT_TIMESTAMP,NULL, NULL,NULL,NULL,NULL, NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,0);
+INSERT INTO user(
+user_name, user_real_name, user_password, user_newpassword, user_newpass_time,
+user_email, user_email_authenticated, user_options, user_token, user_registration, user_editcount)
+VALUES (
+'Anonymous','', NULL, NULL, CURRENT_TIMESTAMP,
+NULL, NULL, NULL, NULL, CURRENT_timestamp, 0);
+
CREATE TABLE user_groups (
- ug_user INTEGER REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ ug_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE CASCADE,
ug_group VARCHAR(255) NOT NULL
);
-CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
+CREATE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
+
CREATE TABLE user_newtalk (
- user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
- user_ip VARCHAR(255),
- user_last_timestamp TIMESTAMP
+ -- registered users key
+ user_id BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE CASCADE,
+ -- anonymous users key
+ user_ip VARCHAR(40),
+ user_last_timestamp TIMESTAMP(3)
);
CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip);
+--leonsp:
+CREATE UNIQUE INDEX user_newtalk_include_idx
+ ON user_newtalk(user_id, user_ip)
+ INCLUDE (user_last_timestamp);
-CREATE SEQUENCE page_page_id_seq;
CREATE TABLE page (
- page_id INTEGER NOT NULL PRIMARY KEY, -- DEFAULT NEXT VALUE FOR user_user_id_seq,
+ page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
page_namespace SMALLINT NOT NULL,
page_title VARCHAR(255) NOT NULL,
- page_restrictions clob(1K),
+ page_restrictions VARCHAR(1024),
page_counter BIGINT NOT NULL DEFAULT 0,
page_is_redirect SMALLINT NOT NULL DEFAULT 0,
page_is_new SMALLINT NOT NULL DEFAULT 0,
page_random NUMERIC(15,14) NOT NULL,
- page_touched TIMESTAMP,
- page_latest INTEGER NOT NULL, -- FK?
- page_len INTEGER NOT NULL
+ page_touched TIMESTAMP(3),
+ page_latest BIGINT NOT NULL, -- FK?
+ page_len BIGINT NOT NULL
);
CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
---CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0;
---CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1;
---CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2;
---CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3;
---CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4;
CREATE INDEX page_random_idx ON page (page_random);
CREATE INDEX page_len_idx ON page (page_len);
+--leonsp:
+CREATE UNIQUE INDEX page_id_include
+ ON page (page_id)
+ INCLUDE (page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
+CREATE UNIQUE INDEX page_name_include
+ ON page (page_namespace, page_title)
+ INCLUDE (page_id, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_latest, page_len);
---CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
---$mw$
---BEGIN
---DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
---RETURN NULL;
---END;
---$mw$;
-
---CREATE TRIGGER page_deleted AFTER DELETE ON page
--- FOR EACH ROW EXECUTE PROCEDURE page_deleted();
-CREATE SEQUENCE rev_rev_id_val;
CREATE TABLE revision (
- rev_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('rev_rev_id_val'),
- rev_page INTEGER REFERENCES page (page_id) ON DELETE CASCADE,
- rev_text_id INTEGER, -- FK
- rev_comment clob(1K), -- changed from VARCHAR(255)
- rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT,
- rev_user_text VARCHAR(255) NOT NULL,
- rev_timestamp TIMESTAMP NOT NULL,
- rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
- rev_deleted SMALLINT NOT NULL DEFAULT 0,
- rev_len INTEGER,
- rev_parent_id INTEGER
+ rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+ rev_page BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page (page_id) ON DELETE CASCADE,
+ rev_text_id BIGINT, -- FK
+ rev_comment VARCHAR(1024),
+ rev_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE RESTRICT,
+ rev_user_text VARCHAR(255) NOT NULL,
+ rev_timestamp TIMESTAMP(3) NOT NULL,
+ rev_minor_edit SMALLINT NOT NULL DEFAULT 0,
+ rev_deleted SMALLINT NOT NULL DEFAULT 0,
+ rev_len BIGINT,
+ rev_parent_id BIGINT DEFAULT NULL
);
CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id);
CREATE INDEX rev_text_id_idx ON revision (rev_text_id);
CREATE INDEX rev_user_text_idx ON revision (rev_user_text);
-CREATE SEQUENCE text_old_id_val;
-CREATE TABLE pagecontent ( -- replaces reserved word 'text'
- old_id INTEGER NOT NULL,
- --PRIMARY KEY DEFAULT nextval('text_old_id_val'),
- old_text CLOB(16M),
- old_flags clob(1K)
+
+CREATE TABLE text ( -- replaces reserved word 'text'
+ old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+ old_text CLOB(16M) INLINE LENGTH 4096,
+ old_flags VARCHAR(1024)
);
-CREATE SEQUENCE pr_id_val;
+
CREATE TABLE page_restrictions (
- pr_id INTEGER NOT NULL UNIQUE,
- --DEFAULT nextval('pr_id_val'),
- pr_page INTEGER NOT NULL
+ --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),
+ --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+ pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+ pr_page INTEGER NOT NULL DEFAULT 0,
--(used to be nullable)
- REFERENCES page (page_id) ON DELETE CASCADE,
- pr_type VARCHAR(255) NOT NULL,
- pr_level VARCHAR(255) NOT NULL,
+ -- REFERENCES page (page_id) ON DELETE CASCADE,
+ pr_type VARCHAR(60) NOT NULL,
+ pr_level VARCHAR(60) NOT NULL,
pr_cascade SMALLINT NOT NULL,
pr_user INTEGER,
- pr_expiry TIMESTAMP,
- PRIMARY KEY (pr_page, pr_type)
+ pr_expiry TIMESTAMP(3)
+ --PRIMARY KEY (pr_page, pr_type)
);
--ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
+CREATE UNIQUE INDEX pr_pagetype ON page_restrictions (pr_page,pr_type);
+CREATE INDEX pr_typelevel ON page_restrictions (pr_type,pr_level);
+CREATE INDEX pr_level ON page_restrictions (pr_level);
+CREATE INDEX pr_cascade ON page_restrictions (pr_cascade);
CREATE TABLE page_props (
- pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
+ pp_page INTEGER NOT NULL DEFAULT 0,
+ -- REFERENCES page (page_id) ON DELETE CASCADE,
pp_propname VARCHAR(255) NOT NULL,
- pp_value CLOB(64K) NOT NULL,
+ pp_value CLOB(64K) INLINE LENGTH 4096 NOT NULL,
PRIMARY KEY (pp_page,pp_propname)
);
--ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
CREATE TABLE archive (
ar_namespace SMALLINT NOT NULL,
ar_title VARCHAR(255) NOT NULL,
- ar_text CLOB(16M),
- ar_page_id INTEGER,
- ar_parent_id INTEGER,
- ar_comment clob(1K),
- ar_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ ar_text CLOB(16M) INLINE LENGTH 4096,
+ ar_comment VARCHAR(1024),
+ ar_user BIGINT NOT NULL,
+ -- no foreign keys in MySQL
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
ar_user_text VARCHAR(255) NOT NULL,
- ar_timestamp TIMESTAMP NOT NULL,
+ ar_timestamp TIMESTAMP(3) NOT NULL,
ar_minor_edit SMALLINT NOT NULL DEFAULT 0,
- ar_flags clob(1K),
+ ar_flags VARCHAR(1024),
ar_rev_id INTEGER,
ar_text_id INTEGER,
ar_deleted SMALLINT NOT NULL DEFAULT 0,
- ar_len INTEGER
+ ar_len INTEGER,
+ ar_page_id INTEGER,
+ ar_parent_id INTEGER
);
CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
CREATE INDEX archive_user_text ON archive (ar_user_text);
CREATE TABLE redirect (
- rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
- rd_namespace SMALLINT NOT NULL,
- rd_title VARCHAR(255) NOT NULL
+ rd_from BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+ --REFERENCES page(page_id) ON DELETE CASCADE,
+ rd_namespace SMALLINT NOT NULL DEFAULT 0,
+ rd_title VARCHAR(255) NOT NULL DEFAULT '',
+ rd_interwiki varchar(32),
+ rd_fragment VARCHAR(255)
);
CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from);
CREATE TABLE pagelinks (
- pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ pl_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
pl_namespace SMALLINT NOT NULL,
pl_title VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
CREATE TABLE templatelinks (
- tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ tl_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
tl_namespace SMALLINT NOT NULL,
tl_title VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from);
+CREATE UNIQUE INDEX tl_from_idx ON templatelinks (tl_from,tl_namespace,tl_title);
CREATE TABLE imagelinks (
- il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ il_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
il_to VARCHAR(255) NOT NULL
);
-CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from);
+CREATE UNIQUE INDEX il_from_idx ON imagelinks (il_to,il_from);
+CREATE UNIQUE INDEX il_to_idx ON imagelinks (il_from,il_to);
CREATE TABLE categorylinks (
- cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
+ cl_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
cl_to VARCHAR(255) NOT NULL,
- cl_sortkey VARCHAR(255),
- cl_timestamp TIMESTAMP NOT NULL
+ -- cl_sortkey has to be at least 86 wide
+ -- in order to be compatible with the old MySQL schema from MW 1.10
+ cl_sortkey VARCHAR(86),
+ cl_timestamp TIMESTAMP(3) NOT NULL
);
CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to);
CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from);
CREATE TABLE externallinks (
- el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE,
- el_to VARCHAR(255) NOT NULL,
- el_index VARCHAR(255) NOT NULL
+ el_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
+ el_to VARCHAR(1024) NOT NULL,
+ el_index VARCHAR(1024) NOT NULL
);
CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to);
CREATE INDEX externallinks_index ON externallinks (el_index);
+
+--
+-- Track external user accounts, if ExternalAuth is used
+--
+CREATE TABLE external_user (
+ -- Foreign key to user_id
+ eu_local_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+
+ -- Some opaque identifier provided by the external database
+ eu_external_id VARCHAR(255) NOT NULL
+);
+CREATE UNIQUE INDEX eu_external_id_idx
+ ON external_user (eu_external_id)
+ INCLUDE (eu_local_id);
+CREATE UNIQUE INDEX eu_local_id_idx
+ ON external_user (eu_local_id)
+ INCLUDE (eu_external_id);
+
+
+
CREATE TABLE langlinks (
- ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE,
- ll_lang VARCHAR(255),
+ ll_from BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page (page_id) ON DELETE CASCADE,
+ ll_lang VARCHAR(20),
ll_title VARCHAR(255)
);
CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
CREATE TABLE site_stats (
- ss_row_id INTEGER NOT NULL UNIQUE,
- ss_total_views INTEGER DEFAULT 0,
- ss_total_edits INTEGER DEFAULT 0,
- ss_good_articles INTEGER DEFAULT 0,
+ ss_row_id BIGINT NOT NULL UNIQUE,
+ ss_total_views BIGINT DEFAULT 0,
+ ss_total_edits BIGINT DEFAULT 0,
+ ss_good_articles BIGINT DEFAULT 0,
ss_total_pages INTEGER DEFAULT -1,
ss_users INTEGER DEFAULT -1,
+ ss_active_users INTEGER DEFAULT -1,
ss_admins INTEGER DEFAULT -1,
ss_images INTEGER DEFAULT 0
);
hc_id BIGINT NOT NULL
);
-CREATE SEQUENCE ipblocks_ipb_id_val;
CREATE TABLE ipblocks (
- ipb_id INTEGER NOT NULL PRIMARY KEY,
+ ipb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--DEFAULT nextval('ipblocks_ipb_id_val'),
- ipb_address VARCHAR(255),
- ipb_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
- ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ ipb_address VARCHAR(1024),
+ ipb_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
+ ipb_by BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE CASCADE,
ipb_by_text VARCHAR(255) NOT NULL DEFAULT '',
- ipb_reason VARCHAR(255) NOT NULL,
- ipb_timestamp TIMESTAMP NOT NULL,
+ ipb_reason VARCHAR(1024) NOT NULL,
+ ipb_timestamp TIMESTAMP(3) NOT NULL,
ipb_auto SMALLINT NOT NULL DEFAULT 0,
ipb_anon_only SMALLINT NOT NULL DEFAULT 0,
ipb_create_account SMALLINT NOT NULL DEFAULT 1,
ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1,
- ipb_expiry TIMESTAMP NOT NULL,
- ipb_range_start VARCHAR(255),
- ipb_range_end VARCHAR(255),
+ ipb_expiry TIMESTAMP(3) NOT NULL,
+ ipb_range_start VARCHAR(1024),
+ ipb_range_end VARCHAR(1024),
ipb_deleted SMALLINT NOT NULL DEFAULT 0,
- ipb_block_email SMALLINT NOT NULL DEFAULT 0
+ ipb_block_email SMALLINT NOT NULL DEFAULT 0,
+ ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX ipb_address ON ipblocks (ipb_address);
CREATE TABLE image (
img_name VARCHAR(255) NOT NULL PRIMARY KEY,
- img_size INTEGER NOT NULL,
+ img_size BIGINT NOT NULL,
img_width INTEGER NOT NULL,
img_height INTEGER NOT NULL,
- img_metadata CLOB(16M) NOT NULL DEFAULT '',
+ img_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
img_bits SMALLINT,
img_media_type VARCHAR(255),
img_major_mime VARCHAR(255) DEFAULT 'unknown',
- img_minor_mime VARCHAR(255) DEFAULT 'unknown',
- img_description clob(1K) NOT NULL DEFAULT '',
- img_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ img_minor_mime VARCHAR(32) DEFAULT 'unknown',
+ img_description VARCHAR(1024) NOT NULL DEFAULT '',
+ img_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
img_user_text VARCHAR(255) NOT NULL DEFAULT '',
- img_timestamp TIMESTAMP,
+ img_timestamp TIMESTAMP(3),
img_sha1 VARCHAR(255) NOT NULL DEFAULT ''
);
CREATE INDEX img_size_idx ON image (img_size);
CREATE INDEX img_sha1 ON image (img_sha1);
CREATE TABLE oldimage (
- oi_name VARCHAR(255) NOT NULL,
+ oi_name VARCHAR(255) NOT NULL DEFAULT '',
oi_archive_name VARCHAR(255) NOT NULL,
- oi_size INTEGER NOT NULL,
+ oi_size BIGINT NOT NULL,
oi_width INTEGER NOT NULL,
oi_height INTEGER NOT NULL,
oi_bits SMALLINT NOT NULL,
- oi_description clob(1K),
- oi_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ oi_description VARCHAR(1024),
+ oi_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
oi_user_text VARCHAR(255) NOT NULL,
- oi_timestamp TIMESTAMP NOT NULL,
- oi_metadata CLOB(16M) NOT NULL DEFAULT '',
+ oi_timestamp TIMESTAMP(3) NOT NULL,
+ oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
oi_media_type VARCHAR(255) ,
oi_major_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
oi_minor_mime VARCHAR(255) NOT NULL DEFAULT 'unknown',
oi_deleted SMALLINT NOT NULL DEFAULT 0,
- oi_sha1 VARCHAR(255) NOT NULL DEFAULT '',
- FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
+ oi_sha1 VARCHAR(255) NOT NULL DEFAULT ''
+ --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
);
---ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE;
CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
-CREATE SEQUENCE filearchive_fa_id_seq;
+
CREATE TABLE filearchive (
- fa_id INTEGER NOT NULL PRIMARY KEY,
+ fa_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
fa_name VARCHAR(255) NOT NULL,
fa_archive_name VARCHAR(255),
fa_storage_group VARCHAR(255),
- fa_storage_key VARCHAR(255),
- fa_deleted_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
- fa_deleted_timestamp TIMESTAMP NOT NULL,
+ fa_storage_key VARCHAR(64) DEFAULT '',
+ fa_deleted_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
+ fa_deleted_timestamp TIMESTAMP(3) NOT NULL,
fa_deleted_reason VARCHAR(255),
- fa_size INTEGER NOT NULL,
+ fa_size BIGINT NOT NULL,
fa_width INTEGER NOT NULL,
fa_height INTEGER NOT NULL,
- fa_metadata CLOB(16M) NOT NULL DEFAULT '',
+ fa_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
fa_bits SMALLINT,
fa_media_type VARCHAR(255),
fa_major_mime VARCHAR(255) DEFAULT 'unknown',
fa_minor_mime VARCHAR(255) DEFAULT 'unknown',
- fa_description clob(1K) NOT NULL,
- fa_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ fa_description VARCHAR(1024) NOT NULL,
+ fa_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
fa_user_text VARCHAR(255) NOT NULL,
- fa_timestamp TIMESTAMP,
+ fa_timestamp TIMESTAMP(3),
fa_deleted SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp);
CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp);
CREATE INDEX fa_nouser ON filearchive (fa_deleted_user);
-CREATE SEQUENCE rc_rc_id_seq;
+
CREATE TABLE recentchanges (
- rc_id INTEGER NOT NULL PRIMARY KEY,
+ rc_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
- rc_timestamp TIMESTAMP NOT NULL,
- rc_cur_time TIMESTAMP NOT NULL,
- rc_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ rc_timestamp TIMESTAMP(3) NOT NULL,
+ rc_cur_time TIMESTAMP(3) NOT NULL,
+ rc_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
rc_user_text VARCHAR(255) NOT NULL,
rc_namespace SMALLINT NOT NULL,
rc_title VARCHAR(255) NOT NULL,
rc_minor SMALLINT NOT NULL DEFAULT 0,
rc_bot SMALLINT NOT NULL DEFAULT 0,
rc_new SMALLINT NOT NULL DEFAULT 0,
- rc_cur_id INTEGER REFERENCES page(page_id) ON DELETE SET NULL,
- rc_this_oldid INTEGER NOT NULL,
- rc_last_oldid INTEGER NOT NULL,
+ rc_cur_id BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES page(page_id) ON DELETE SET NULL,
+ rc_this_oldid BIGINT NOT NULL,
+ rc_last_oldid BIGINT NOT NULL,
rc_type SMALLINT NOT NULL DEFAULT 0,
rc_moved_to_ns SMALLINT,
rc_moved_to_title VARCHAR(255),
rc_patrolled SMALLINT NOT NULL DEFAULT 0,
- rc_ip VARCHAR(255), -- was CIDR type
+ rc_ip VARCHAR(40), -- was CIDR type
rc_old_len INTEGER,
rc_new_len INTEGER,
rc_deleted SMALLINT NOT NULL DEFAULT 0,
- rc_logid INTEGER NOT NULL DEFAULT 0,
+ rc_logid BIGINT NOT NULL DEFAULT 0,
rc_log_type VARCHAR(255),
rc_log_action VARCHAR(255),
- rc_params CLOB(64K)
+ rc_params CLOB(64K) INLINE LENGTH 4096
+
);
CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp);
CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title);
CREATE TABLE watchlist (
- wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE,
+ wl_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE CASCADE,
wl_namespace SMALLINT NOT NULL DEFAULT 0,
wl_title VARCHAR(255) NOT NULL,
- wl_notificationtimestamp TIMESTAMP
+ wl_notificationtimestamp TIMESTAMP(3)
);
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
CREATE TABLE math (
- math_inputhash VARGRAPHIC(255) NOT NULL UNIQUE,
- math_outputhash VARGRAPHIC(255) NOT NULL,
+ math_inputhash VARCHAR(16) FOR BIT DATA NOT NULL UNIQUE,
+ math_outputhash VARCHAR(16) FOR BIT DATA NOT NULL,
math_html_conservativeness SMALLINT NOT NULL,
- math_html VARCHAR(255),
- math_mathml VARCHAR(255)
+ math_html CLOB(64K) INLINE LENGTH 4096,
+ math_mathml CLOB(64K) INLINE LENGTH 4096
);
CREATE TABLE interwiki (
- iw_prefix VARCHAR(255) NOT NULL UNIQUE,
- iw_url CLOB(64K) NOT NULL,
+ iw_prefix VARCHAR(32) NOT NULL UNIQUE,
+ iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
iw_local SMALLINT NOT NULL,
iw_trans SMALLINT NOT NULL DEFAULT 0
);
CREATE TABLE querycache (
qc_type VARCHAR(255) NOT NULL,
- qc_value INTEGER NOT NULL,
- qc_namespace SMALLINT NOT NULL,
+ qc_value BIGINT NOT NULL,
+ qc_namespace INTEGER NOT NULL,
qc_title VARCHAR(255) NOT NULL
);
CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value);
CREATE TABLE querycache_info (
qci_type VARCHAR(255) UNIQUE NOT NULL,
- qci_timestamp TIMESTAMP
+ qci_timestamp TIMESTAMP(3)
);
CREATE TABLE querycachetwo (
qcc_type VARCHAR(255) NOT NULL,
- qcc_value INTEGER NOT NULL DEFAULT 0,
+ qcc_value BIGINT NOT NULL DEFAULT 0,
qcc_namespace INTEGER NOT NULL DEFAULT 0,
qcc_title VARCHAR(255) NOT NULL DEFAULT '',
qcc_namespacetwo INTEGER NOT NULL DEFAULT 0,
CREATE TABLE objectcache (
keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable
- value CLOB(16M) NOT NULL DEFAULT '',
- exptime TIMESTAMP NOT NULL
+ value CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '',
+ exptime TIMESTAMP(3) NOT NULL
);
CREATE INDEX objectcacache_exptime ON objectcache (exptime);
CREATE TABLE transcache (
tc_url VARCHAR(255) NOT NULL UNIQUE,
- tc_contents VARCHAR(255) NOT NULL,
- tc_time TIMESTAMP NOT NULL
+ tc_contents CLOB(64K) INLINE LENGTH 4096 NOT NULL,
+ tc_time TIMESTAMP(3) NOT NULL
);
-CREATE SEQUENCE log_log_id_seq;
+
CREATE TABLE logging (
- log_id INTEGER NOT NULL PRIMARY KEY,
+ log_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
- log_type VARCHAR(255) NOT NULL,
- log_action VARCHAR(255) NOT NULL,
- log_timestamp TIMESTAMP NOT NULL,
- log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
- log_namespace SMALLINT NOT NULL,
- log_title VARCHAR(255) NOT NULL,
- log_comment VARCHAR(255),
- log_params CLOB(64K),
- log_deleted SMALLINT NOT NULL DEFAULT 0
+ log_type VARCHAR(32) NOT NULL,
+ log_action VARCHAR(32) NOT NULL,
+ log_timestamp TIMESTAMP(3) NOT NULL,
+ log_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
+ -- Name of the user who performed this action
+ log_user_text VARCHAR(255) NOT NULL default '',
+ log_namespace SMALLINT NOT NULL,
+ log_title VARCHAR(255) NOT NULL,
+ log_page BIGINT,
+ log_comment VARCHAR(255),
+ log_params CLOB(64K) INLINE LENGTH 4096,
+ log_deleted SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX logging_type_name ON logging (log_type, log_timestamp);
CREATE INDEX logging_user_time ON logging (log_timestamp, log_user);
CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp);
+CREATE INDEX log_user_type_time ON logging (log_user, log_type, log_timestamp);
+CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp);
+
+
-CREATE SEQUENCE trackbacks_tb_id_seq;
CREATE TABLE trackbacks (
- tb_id INTEGER NOT NULL PRIMARY KEY,
+ tb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
- tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
+ -- foreign key also in MySQL
+ tb_page INTEGER,
+ -- REFERENCES page(page_id) ON DELETE CASCADE,
tb_title VARCHAR(255) NOT NULL,
- tb_url CLOB(64K) NOT NULL,
- tb_ex VARCHAR(255),
+ tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL,
+ tb_ex CLOB(64K) INLINE LENGTH 4096,
tb_name VARCHAR(255)
);
CREATE INDEX trackback_page ON trackbacks (tb_page);
-CREATE SEQUENCE job_job_id_seq;
+
CREATE TABLE job (
- job_id INTEGER NOT NULL PRIMARY KEY,
+ job_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
job_cmd VARCHAR(255) NOT NULL,
job_namespace SMALLINT NOT NULL,
job_title VARCHAR(255) NOT NULL,
- job_params CLOB(64K) NOT NULL
+ job_params CLOB(64K) INLINE LENGTH 4096 NOT NULL
);
CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
-
--- Postgres' Tsearch2 dropped
---ALTER TABLE page ADD titlevector tsvector;
---CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
---$mw$
---BEGIN
---IF TG_OP = 'INSERT' THEN
--- NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
---ELSIF NEW.page_title != OLD.page_title THEN
--- NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' '));
---END IF;
---RETURN NEW;
---END;
---$mw$;
-
---CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
--- FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
-
-
---ALTER TABLE pagecontent ADD textvector tsvector;
---CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
---$mw$
---BEGIN
---IF TG_OP = 'INSERT' THEN
--- NEW.textvector = to_tsvector('default',NEW.old_text);
---ELSIF NEW.old_text != OLD.old_text THEN
--- NEW.textvector := to_tsvector('default',NEW.old_text);
---END IF;
---RETURN NEW;
---END;
---$mw$;
-
---CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
--- FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
-
--- These are added by the setup script due to version compatibility issues
--- If using 8.1, we switch from "gin" to "gist"
-
---CREATE INDEX ts2_page_title ON page USING gin(titlevector);
---CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector);
-
--TODO
--CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
--$mw$
-- hack implementation
-- should be replaced with OmniFind, Contains(), etc
CREATE TABLE searchindex (
- si_page int NOT NULL,
+ si_page BIGINT NOT NULL,
si_title varchar(255) NOT NULL default '',
si_text clob NOT NULL
);
CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
CREATE TABLE protected_titles (
- pt_namespace SMALLINT NOT NULL,
+ pt_namespace INTEGER NOT NULL,
pt_title VARCHAR(255) NOT NULL,
- pt_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
- pt_reason clob(1K),
- pt_timestamp TIMESTAMP NOT NULL,
- pt_expiry TIMESTAMP ,
- pt_create_perm VARCHAR(255) NOT NULL DEFAULT ''
+ pt_user BIGINT NOT NULL DEFAULT 0,
+ -- REFERENCES user(user_id) ON DELETE SET NULL,
+ pt_reason VARCHAR(1024),
+ pt_timestamp TIMESTAMP(3) NOT NULL,
+ pt_expiry TIMESTAMP(3) ,
+ pt_create_perm VARCHAR(60) NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title);
ul_key VARCHAR(255) NOT NULL PRIMARY KEY
);
-CREATE SEQUENCE category_id_seq;
+
CREATE TABLE category (
- cat_id INTEGER NOT NULL PRIMARY KEY,
+ cat_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
--PRIMARY KEY DEFAULT nextval('category_id_seq'),
cat_title VARCHAR(255) NOT NULL,
cat_pages INTEGER NOT NULL DEFAULT 0,
CREATE UNIQUE INDEX category_title ON category(cat_title);
CREATE INDEX category_pages ON category(cat_pages);
-CREATE TABLE mediawiki_version (
- type VARCHAR(255) NOT NULL,
- mw_version VARCHAR(255) NOT NULL,
- notes VARCHAR(255) ,
-
- pg_version VARCHAR(255) ,
- pg_dbname VARCHAR(255) ,
- pg_user VARCHAR(255) ,
- pg_port VARCHAR(255) ,
- mw_schema VARCHAR(255) ,
- ts2_schema VARCHAR(255) ,
- ctype VARCHAR(255) ,
-
- sql_version VARCHAR(255) ,
- sql_date VARCHAR(255) ,
- cdate TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
-);
-
-INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
- VALUES ('Creation','??','$LastChangedRevision: 34049 $','$LastChangedDate: 2008-04-30 10:20:36 -0400 (Wed, 30 Apr 2008) $');
+-- added for 1.15
+
+-- A table to track tags for revisions, logs and recent changes.
+CREATE TABLE change_tag (
+ ct_rc_id INTEGER,
+ ct_log_id INTEGER,
+ ct_rev_id INTEGER,
+ ct_tag varchar(255) NOT NULL,
+ ct_params CLOB(64K) INLINE LENGTH 4096
+);
+CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag (ct_rc_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_log_tag ON change_tag (ct_log_id,ct_tag);
+CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag (ct_rev_id,ct_tag);
+-- Covering index, so we can pull all the info only out of the index.
+CREATE INDEX change_tag_tag_id ON change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
+
+
+-- Rollup table to pull a LIST of tags simply
+CREATE TABLE tag_summary (
+ ts_rc_id INTEGER,
+ ts_log_id INTEGER,
+ ts_rev_id INTEGER,
+ ts_tags CLOB(64K) INLINE LENGTH 4096 NOT NULL
+);
+CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary (ts_rc_id);
+CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary (ts_log_id);
+CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary (ts_rev_id);
+
+
+CREATE TABLE valid_tag (
+ vt_tag varchar(255) NOT NULL PRIMARY KEY
+);
+
+--
+-- User preferences and perhaps other fun stuff. :)
+-- Replaces the old user.user_options blob, with a couple nice properties:
+--
+-- 1) We only store non-default settings, so changes to the defaults
+-- are now reflected for everybody, not just new accounts.
+-- 2) We can more easily do bulk lookups, statistics, or modifications of
+-- saved options since it's a sane table structure.
+--
+CREATE TABLE user_properties (
+ -- Foreign key to user.user_id
+ up_user BIGINT NOT NULL,
+
+ -- Name of the option being saved. This is indexed for bulk lookup.
+ up_property VARCHAR(32) FOR BIT DATA NOT NULL,
+
+ -- Property value as a string.
+ up_value CLOB(64K) INLINE LENGTH 4096
+);
+CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property);
+CREATE INDEX user_properties_property ON user_properties (up_property);
+
+CREATE TABLE log_search (
+ -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username)
+ ls_field VARCHAR(32) FOR BIT DATA NOT NULL,
+ -- The value of the ID
+ ls_value varchar(255) NOT NULL,
+ -- Key to log_id
+ ls_log_id BIGINT NOT NULL default 0
+);
+CREATE UNIQUE INDEX ls_field_val ON log_search (ls_field,ls_value,ls_log_id);
+CREATE INDEX ls_log_id ON log_search (ls_log_id);
+
+-- Table for storing localisation data
+CREATE TABLE l10n_cache (
+ -- Language code
+ lc_lang VARCHAR(32) NOT NULL,
+ -- Cache key
+ lc_key VARCHAR(255) NOT NULL,
+ -- Value
+ lc_value CLOB(16M) INLINE LENGTH 4096 NOT NULL
+);
+CREATE INDEX lc_lang_key ON l10n_cache (lc_lang, lc_key);
scripts.mit.edu / autoinstallsdev / mediawiki.git / blobdiff