diff options
Diffstat (limited to 'maintenance/ibm_db2/tables.sql')
-rw-r--r-- | maintenance/ibm_db2/tables.sql | 103 |
1 files changed, 18 insertions, 85 deletions
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 |