summaryrefslogtreecommitdiff
path: root/maintenance/ibm_db2/tables.sql
diff options
context:
space:
mode:
Diffstat (limited to 'maintenance/ibm_db2/tables.sql')
-rw-r--r--maintenance/ibm_db2/tables.sql103
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