-- 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 (
+ user_id INTEGER 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_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 INTEGER 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);
+--leonsp:
+CREATE UNIQUE INDEX user_groups_include_idx
+ ON user_groups(ug_user)
+ INCLUDE (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 INTEGER NOT NULL 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 INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
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_touched TIMESTAMP(3),
page_latest INTEGER NOT NULL, -- FK?
page_len INTEGER 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_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
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_comment VARCHAR(1024),
+ rev_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE RESTRICT,
rev_user_text VARCHAR(255) NOT NULL,
- rev_timestamp TIMESTAMP 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 INTEGER,
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,
+-- CREATE SEQUENCE text_old_id_val;
+CREATE TABLE text ( -- replaces reserved word 'text'
+ --old_id INTEGER NOT NULL,
+ old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
--PRIMARY KEY DEFAULT nextval('text_old_id_val'),
- old_text CLOB(16M),
- old_flags clob(1K)
+ old_text CLOB(16M) INLINE LENGTH 4096,
+ old_flags VARCHAR(1024)
);
-CREATE SEQUENCE pr_id_val;
+--CREATE SEQUENCE pr_id_val;
CREATE TABLE page_restrictions (
- pr_id INTEGER NOT NULL UNIQUE,
- --DEFAULT nextval('pr_id_val'),
+ --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'),
+ --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
+ pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
pr_page INTEGER NOT NULL
--(used to be nullable)
REFERENCES page (page_id) ON DELETE CASCADE,
- pr_type VARCHAR(255) NOT NULL,
- pr_level VARCHAR(255) NOT NULL,
+ 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_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_text CLOB(16M) INLINE LENGTH 4096,
ar_page_id INTEGER,
ar_parent_id INTEGER,
- ar_comment clob(1K),
- ar_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL,
+ ar_comment VARCHAR(1024),
+ ar_user INTEGER 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,
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_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);
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_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_to VARCHAR(255) NOT NULL,
- cl_sortkey VARCHAR(255),
- cl_timestamp TIMESTAMP NOT NULL
+ cl_sortkey VARCHAR(70),
+ 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_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_wiki_id INTEGER NOT NULL PRIMARY KEY,
+
+ -- 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_wiki_id);
+CREATE UNIQUE INDEX eu_wiki_id_idx
+ ON external_user (eu_wiki_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_lang VARCHAR(20),
ll_title VARCHAR(255)
);
CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang);
hc_id BIGINT NOT NULL
);
-CREATE SEQUENCE ipblocks_ipb_id_val;
CREATE TABLE ipblocks (
ipb_id INTEGER NOT NULL PRIMARY KEY,
--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 INTEGER REFERENCES user(user_id) ON DELETE SET NULL,
+ ipb_by INTEGER NOT NULL 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
img_size INTEGER 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 INTEGER 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);
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 INTEGER 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',
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(32),
+ fa_deleted_user INTEGER 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_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 INTEGER 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 TABLE recentchanges (
rc_id INTEGER NOT NULL PRIMARY KEY,
--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 INTEGER 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_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_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 INTEGER NOT NULL 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_info (
qci_type VARCHAR(255) UNIQUE NOT NULL,
- qci_timestamp TIMESTAMP
+ qci_timestamp TIMESTAMP(3)
);
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_time TIMESTAMP(3) NOT NULL
);
CREATE SEQUENCE log_log_id_seq;
CREATE TABLE logging (
- log_id INTEGER NOT NULL PRIMARY KEY,
+ log_id INTEGER NOT NULL PRIMARY KEY,
--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 INTEGER 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 INTEGER,
+ 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 (
--PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
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);
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);
-- FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
---ALTER TABLE pagecontent ADD textvector tsvector;
+--ALTER TABLE text ADD textvector tsvector;
--CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
--$mw$
--BEGIN
--END;
--$mw$;
---CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
+--CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text
-- 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);
+--CREATE INDEX ts2_page_text ON text USING gin(textvector);
--TODO
--CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
CREATE TABLE protected_titles (
pt_namespace SMALLINT 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 INTEGER 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 SEQUENCE category_id_seq;
CREATE TABLE category (
cat_id INTEGER NOT NULL PRIMARY KEY,
--PRIMARY KEY DEFAULT nextval('category_id_seq'),
CREATE UNIQUE INDEX category_title ON category(cat_title);
CREATE INDEX category_pages ON category(cat_pages);
+-- 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 INTEGER 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 INTEGER 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);
+
CREATE TABLE mediawiki_version (
- type VARCHAR(255) NOT NULL,
- mw_version VARCHAR(255) NOT NULL,
- notes VARCHAR(255) ,
+ type VARCHAR(1024) NOT NULL,
+ mw_version VARCHAR(1024) NOT NULL,
+ notes VARCHAR(1024) ,
- 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) ,
+ pg_version VARCHAR(1024) ,
+ pg_dbname VARCHAR(1024) ,
+ pg_user VARCHAR(1024) ,
+ pg_port VARCHAR(1024) ,
+ mw_schema VARCHAR(1024) ,
+ ts2_schema VARCHAR(1024) ,
+ ctype VARCHAR(1024) ,
- sql_version VARCHAR(255) ,
- sql_date VARCHAR(255) ,
- cdate TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
+ sql_version VARCHAR(1024) ,
+ sql_date VARCHAR(1024) ,
+ cdate TIMESTAMP(3) 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) $');
+-- 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);
+
dépôts / lhc / web / wiklou.git / blobdiff