From 4f20c12c9e09f778a03115594dcf5868dd74e7da Mon Sep 17 00:00:00 2001 From: Leons Petrazickis Date: Mon, 28 Sep 2009 20:27:30 +0000 Subject: [PATCH] MediaWiki database schema for IBM DB2 * removed all foreign keys not in MySQL schema * reordered implicit order of columns in archive table to match MySQL * added missing ss_active_users column to site_stats table * added missing ipb_allow_usertalk column to ipblocks table --- maintenance/ibm_db2/tables.sql | 96 ++++++++++++++++++++++------------ 1 file changed, 62 insertions(+), 34 deletions(-) diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 9b629c2926..659453c518 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -43,7 +43,8 @@ NULL, NULL, NULL, NULL, CURRENT_timestamp, 0); CREATE TABLE user_groups ( - ug_user INTEGER REFERENCES user(user_id) ON DELETE CASCADE, + ug_user INTEGER 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); @@ -55,7 +56,8 @@ CREATE UNIQUE INDEX user_groups_include_idx CREATE TABLE user_newtalk ( -- registered users key - user_id INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE, + user_id INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE CASCADE, -- anonymous users key user_ip VARCHAR(40), user_last_timestamp TIMESTAMP(3) @@ -95,10 +97,12 @@ CREATE UNIQUE INDEX page_name_include CREATE TABLE revision ( rev_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), - rev_page INTEGER REFERENCES page (page_id) ON DELETE CASCADE, + rev_page INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id INTEGER, -- FK rev_comment VARCHAR(1024), - rev_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE RESTRICT, + rev_user INTEGER 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, @@ -127,9 +131,9 @@ CREATE TABLE page_restrictions ( --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 + pr_page INTEGER NOT NULL DEFAULT 0, --(used to be nullable) - REFERENCES page (page_id) ON DELETE CASCADE, + -- REFERENCES page (page_id) ON DELETE CASCADE, pr_type VARCHAR(60) NOT NULL, pr_level VARCHAR(60) NOT NULL, pr_cascade SMALLINT NOT NULL, @@ -144,7 +148,8 @@ 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) INLINE LENGTH 4096 NOT NULL, PRIMARY KEY (pp_page,pp_propname) @@ -158,10 +163,10 @@ CREATE TABLE archive ( ar_namespace SMALLINT NOT NULL, ar_title VARCHAR(255) NOT NULL, ar_text CLOB(16M) INLINE LENGTH 4096, - ar_page_id INTEGER, - ar_parent_id INTEGER, ar_comment VARCHAR(1024), - ar_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL, + ar_user INTEGER 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(3) NOT NULL, ar_minor_edit SMALLINT NOT NULL DEFAULT 0, @@ -169,7 +174,9 @@ CREATE TABLE archive ( 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); @@ -177,7 +184,8 @@ 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_from INTEGER NOT NULL PRIMARY KEY, + --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), @@ -187,14 +195,16 @@ 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 INTEGER 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 INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE CASCADE, tl_namespace SMALLINT NOT NULL, tl_title VARCHAR(255) NOT NULL ); @@ -202,14 +212,16 @@ CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title 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 INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE CASCADE, il_to VARCHAR(255) NOT NULL ); 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 INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE CASCADE, cl_to VARCHAR(255) NOT NULL, cl_sortkey VARCHAR(70), cl_timestamp TIMESTAMP(3) NOT NULL @@ -220,7 +232,8 @@ 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_from INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE CASCADE, el_to VARCHAR(1024) NOT NULL, el_index VARCHAR(1024) NOT NULL ); @@ -248,7 +261,8 @@ CREATE UNIQUE INDEX eu_wiki_id_idx CREATE TABLE langlinks ( - ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, + ll_from INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page (page_id) ON DELETE CASCADE, ll_lang VARCHAR(20), ll_title VARCHAR(255) ); @@ -263,6 +277,7 @@ CREATE TABLE site_stats ( ss_good_articles INTEGER 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 ); @@ -275,8 +290,10 @@ CREATE TABLE ipblocks ( ipb_id INTEGER NOT NULL PRIMARY KEY, --DEFAULT nextval('ipblocks_ipb_id_val'), 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_user INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, + ipb_by INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE CASCADE, ipb_by_text VARCHAR(255) NOT NULL DEFAULT '', ipb_reason VARCHAR(1024) NOT NULL, ipb_timestamp TIMESTAMP(3) NOT NULL, @@ -288,7 +305,8 @@ CREATE TABLE ipblocks ( 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); @@ -308,7 +326,8 @@ CREATE TABLE image ( img_major_mime VARCHAR(255) DEFAULT 'unknown', 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 INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, img_user_text VARCHAR(255) NOT NULL DEFAULT '', img_timestamp TIMESTAMP(3), img_sha1 VARCHAR(255) NOT NULL DEFAULT '' @@ -318,14 +337,15 @@ CREATE INDEX img_timestamp_idx ON image (img_timestamp); 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_width INTEGER NOT NULL, oi_height INTEGER NOT NULL, oi_bits SMALLINT NOT NULL, oi_description VARCHAR(1024), - oi_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL, + oi_user INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, oi_user_text VARCHAR(255) NOT NULL, oi_timestamp TIMESTAMP(3) NOT NULL, oi_metadata CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '', @@ -333,8 +353,8 @@ CREATE TABLE oldimage ( 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); @@ -350,7 +370,8 @@ CREATE TABLE filearchive ( fa_archive_name VARCHAR(255), fa_storage_group VARCHAR(255), fa_storage_key VARCHAR(32), - fa_deleted_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL, + fa_deleted_user INTEGER 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, @@ -362,7 +383,8 @@ CREATE TABLE filearchive ( fa_major_mime VARCHAR(255) DEFAULT 'unknown', fa_minor_mime VARCHAR(255) DEFAULT 'unknown', fa_description VARCHAR(1024) NOT NULL, - fa_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL, + fa_user INTEGER NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, fa_user_text VARCHAR(255) NOT NULL, fa_timestamp TIMESTAMP(3), fa_deleted SMALLINT NOT NULL DEFAULT 0 @@ -378,7 +400,8 @@ CREATE TABLE recentchanges ( --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), 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 INTEGER 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, @@ -386,7 +409,8 @@ CREATE TABLE recentchanges ( 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_cur_id INTEGER NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE SET NULL, rc_this_oldid INTEGER NOT NULL, rc_last_oldid INTEGER NOT NULL, rc_type SMALLINT NOT NULL DEFAULT 0, @@ -412,7 +436,8 @@ CREATE INDEX rc_ip ON recentchanges (rc_ip); CREATE TABLE watchlist ( - wl_user INTEGER NOT NULL REFERENCES user(user_id) ON DELETE CASCADE, + wl_user INTEGER 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(3) @@ -487,7 +512,8 @@ CREATE TABLE logging ( 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, + log_user INTEGER 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, @@ -508,7 +534,8 @@ CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp); CREATE TABLE trackbacks ( tb_id INTEGER NOT NULL PRIMARY KEY, --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) INLINE LENGTH 4096 NOT NULL, tb_ex CLOB(64K) INLINE LENGTH 4096, @@ -598,7 +625,8 @@ CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); CREATE TABLE protected_titles ( pt_namespace SMALLINT NOT NULL, pt_title VARCHAR(255) NOT NULL, - pt_user INTEGER REFERENCES user(user_id) ON DELETE SET NULL, + pt_user INTEGER 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) , -- 2.20.1