diff options
author | Pierre Schmitz <pierre@archlinux.de> | 2011-06-22 11:28:20 +0200 |
---|---|---|
committer | Pierre Schmitz <pierre@archlinux.de> | 2011-06-22 11:28:20 +0200 |
commit | 9db190c7e736ec8d063187d4241b59feaf7dc2d1 (patch) | |
tree | 46d1a0dee7febef5c2d57a9f7b972be16a163b3d /maintenance/ibm_db2 | |
parent | 78677c7bbdcc9739f6c10c75935898a20e1acd9e (diff) |
update to MediaWiki 1.17.0
Diffstat (limited to 'maintenance/ibm_db2')
-rw-r--r-- | maintenance/ibm_db2/README | 3 | ||||
-rw-r--r-- | maintenance/ibm_db2/foreignkeys.sql | 107 | ||||
-rw-r--r-- | maintenance/ibm_db2/tables.sql | 103 |
3 files changed, 125 insertions, 88 deletions
diff --git a/maintenance/ibm_db2/README b/maintenance/ibm_db2/README deleted file mode 100644 index 3c3f381c..00000000 --- a/maintenance/ibm_db2/README +++ /dev/null @@ -1,3 +0,0 @@ -== See also == -*[http://www.mediawiki.org/wiki/Manual:IBM_DB2 Installation instructions] -*[http://ca.php.net/manual/en/function.db2-connect.php PHP Manual for DB2 functions]
\ No newline at end of file diff --git a/maintenance/ibm_db2/foreignkeys.sql b/maintenance/ibm_db2/foreignkeys.sql new file mode 100644 index 00000000..81a88eb9 --- /dev/null +++ b/maintenance/ibm_db2/foreignkeys.sql @@ -0,0 +1,107 @@ +-- good +ALTER TABLE user_groups ADD CONSTRAINT USER_GROUPS_FK1 FOREIGN KEY (ug_user) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE user_newtalk ADD CONSTRAINT USER_NEWTALK_FK1 FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- referenced value not found +ALTER TABLE revision ADD CONSTRAINT REVISION_PAGE_FK FOREIGN KEY (rev_page) REFERENCES page(page_id) ON DELETE CASCADE +; +-- referenced value not found +ALTER TABLE revision ADD CONSTRAINT REVISION_USER_FK FOREIGN KEY (rev_user) REFERENCES user(user_id) ON DELETE RESTRICT +; + +-- good +ALTER TABLE page_restrictions ADD CONSTRAINT PAGE_RESTRICTIONS_PAGE_FK FOREIGN KEY (pr_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE page_props ADD CONSTRAINT PAGE_PROPS_PAGE_FK FOREIGN KEY (pp_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE archive ADD CONSTRAINT ARCHIVE_USER_FK FOREIGN KEY (ar_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- referenced value not found +ALTER TABLE redirect ADD CONSTRAINT REDIRECT_FROM_FK FOREIGN KEY (rd_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- referenced value not found +ALTER TABLE pagelinks ADD CONSTRAINT PAGELINKS_FROM_FK FOREIGN KEY (pl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE templatelinks ADD CONSTRAINT TEMPLATELINKS_FROM_FK FOREIGN KEY (tl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE imagelinks ADD CONSTRAINT IMAGELINKS_FROM_FK FOREIGN KEY (il_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE categorylinks ADD CONSTRAINT CATEGORYLINKS_FROM_FK FOREIGN KEY (cl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE externallinks ADD CONSTRAINT EXTERNALLINKS_FROM_FK FOREIGN KEY (el_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE langlinks ADD CONSTRAINT LANGLINKS_FROM_FK FOREIGN KEY (ll_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_USER_FK FOREIGN KEY (ipb_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_BY_FK FOREIGN KEY (ipb_by) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE image ADD CONSTRAINT IMAGE_USER_FK FOREIGN KEY (img_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_USER_FK FOREIGN KEY (oi_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_NAME_FK FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_DELETED_USER_FK FOREIGN KEY (fa_deleted_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_USER_FK FOREIGN KEY (fa_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_USER_FK FOREIGN KEY (rc_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_CUR_ID_FK FOREIGN KEY (rc_cur_id) REFERENCES page(page_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE watchlist ADD CONSTRAINT WATCHLIST_USER_FK FOREIGN KEY (wl_user) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- good +-- already in MySQL schema +ALTER TABLE trackbacks ADD CONSTRAINT TRACKBACKS_PAGE_FK FOREIGN KEY (tb_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE protected_titles ADD CONSTRAINT PROTECTED_TITLES_USER_FK FOREIGN KEY (pt_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE logging ADD CONSTRAINT LOGGING_USER_FK FOREIGN KEY (log_user) REFERENCES user(user_id) ON DELETE SET NULL +--;
\ No newline at end of file diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 71c161c6..546c871d 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -8,6 +8,7 @@ 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), @@ -47,11 +48,7 @@ CREATE TABLE user_groups ( -- 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 INDEX user_groups_unique ON user_groups (ug_user, ug_group); CREATE TABLE user_newtalk ( @@ -71,7 +68,7 @@ CREATE UNIQUE INDEX user_newtalk_include_idx CREATE TABLE page ( - page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + 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 VARCHAR(1024), @@ -96,7 +93,7 @@ CREATE UNIQUE INDEX page_name_include CREATE TABLE revision ( - rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + 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 @@ -119,9 +116,7 @@ CREATE INDEX rev_user_text_idx ON revision (rev_user_text); 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_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), old_text CLOB(16M) INLINE LENGTH 4096, old_flags VARCHAR(1024) ); @@ -129,8 +124,8 @@ CREATE TABLE text ( -- replaces reserved word 'text' 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 BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + --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, @@ -184,7 +179,7 @@ CREATE INDEX archive_user_text ON archive (ar_user_text); CREATE TABLE redirect ( - rd_from BIGINT NOT NULL PRIMARY KEY, + 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 '', @@ -248,7 +243,7 @@ CREATE INDEX externallinks_index ON externallinks (el_index); -- CREATE TABLE external_user ( -- Foreign key to user_id - eu_local_id BIGINT NOT NULL PRIMARY KEY, + 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 @@ -289,7 +284,7 @@ CREATE TABLE hitcounter ( ); 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(1024), ipb_user BIGINT NOT NULL DEFAULT 0, @@ -358,7 +353,6 @@ CREATE TABLE oldimage ( 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); @@ -366,7 +360,7 @@ CREATE INDEX oi_sha1 ON oldimage (oi_sha1); 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), @@ -398,7 +392,7 @@ CREATE INDEX fa_nouser ON filearchive (fa_deleted_user); 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(3) NOT NULL, rc_cur_time TIMESTAMP(3) NOT NULL, @@ -509,7 +503,7 @@ CREATE TABLE transcache ( CREATE TABLE logging ( - log_id BIGINT 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(32) NOT NULL, log_action VARCHAR(32) NOT NULL, @@ -534,10 +528,11 @@ CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp); 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'), -- foreign key also in MySQL - tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, + 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, @@ -548,7 +543,7 @@ CREATE INDEX trackback_page ON trackbacks (tb_page); CREATE TABLE job ( - job_id BIGINT 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, @@ -558,47 +553,6 @@ CREATE TABLE job ( 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 text 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 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 text USING gin(textvector); - --TODO --CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS --$mw$ @@ -644,7 +598,7 @@ CREATE TABLE updatelog ( 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, @@ -721,27 +675,6 @@ CREATE TABLE log_search ( 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(1024) NOT NULL, - mw_version VARCHAR(1024) NOT NULL, - notes VARCHAR(1024) , - - 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(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 |