X-Git-Url: https://git.cyclocoop.org/%28%28?a=blobdiff_plain;f=maintenance%2Foracle%2Ftables.sql;h=09d19220dba8fa9232721e12cdab317a43e41715;hb=58858df842f91d9ea1c9f9b6f3c767d8b204886b;hp=e6e2e5657cd9a44fb6d3b14912b181251568c342;hpb=732b5e2745ca8f6153e19cc10c3c9acb1b2a6331;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index e6e2e5657c..09d19220db 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -48,7 +48,7 @@ CREATE UNIQUE INDEX &mw_prefix.mwuser_u01 ON &mw_prefix.mwuser (user_name); CREATE INDEX &mw_prefix.mwuser_i01 ON &mw_prefix.mwuser (user_email_token); CREATE INDEX &mw_prefix.mwuser_i02 ON &mw_prefix.mwuser (user_email, user_name); /*$mw$*/ -CREATE TRIGGER &mw_prefix.mwuser_default_user_id BEFORE INSERT ON &mw_prefix.mwuser +CREATE TRIGGER &mw_prefix.mwuser_seq_trg BEFORE INSERT ON &mw_prefix.mwuser FOR EACH ROW WHEN (new.user_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(user_user_id_seq.nextval, :new.user_id); @@ -116,7 +116,7 @@ CREATE INDEX &mw_prefix.page_i01 ON &mw_prefix.page (page_random); CREATE INDEX &mw_prefix.page_i02 ON &mw_prefix.page (page_len); CREATE INDEX &mw_prefix.page_i03 ON &mw_prefix.page (page_is_redirect, page_namespace, page_len); /*$mw$*/ -CREATE TRIGGER &mw_prefix.page_default_page_id BEFORE INSERT ON &mw_prefix.page +CREATE TRIGGER &mw_prefix.page_seq_trg BEFORE INSERT ON &mw_prefix.page FOR EACH ROW WHEN (new.page_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(page_page_id_seq.nextval, :new.page_id); @@ -162,7 +162,7 @@ CREATE INDEX &mw_prefix.revision_i03 ON &mw_prefix.revision (rev_user,rev_timest CREATE INDEX &mw_prefix.revision_i04 ON &mw_prefix.revision (rev_user_text,rev_timestamp); CREATE INDEX &mw_prefix.revision_i05 ON &mw_prefix.revision (rev_page,rev_user,rev_timestamp); /*$mw$*/ -CREATE TRIGGER &mw_prefix.revision_default_rev_id BEFORE INSERT ON &mw_prefix.revision +CREATE TRIGGER &mw_prefix.revision_seq_trg BEFORE INSERT ON &mw_prefix.revision FOR EACH ROW WHEN (new.rev_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(revision_rev_id_seq.nextval, :new.rev_id); @@ -177,7 +177,7 @@ CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text' ); ALTER TABLE &mw_prefix.pagecontent ADD CONSTRAINT &mw_prefix.pagecontent_pk PRIMARY KEY (old_id); /*$mw$*/ -CREATE TRIGGER &mw_prefix.text_default_old_id BEFORE INSERT ON &mw_prefix.text +CREATE TRIGGER &mw_prefix.pagecontent_seq_trg BEFORE INSERT ON &mw_prefix.pagecontent FOR EACH ROW WHEN (new.old_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(text_old_id_seq.nextval, :new.old_id); @@ -212,13 +212,85 @@ CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp); CREATE INDEX &mw_prefix.archive_i03 ON &mw_prefix.archive (ar_rev_id); /*$mw$*/ -CREATE TRIGGER &mw_prefix.archive_default_ar_id BEFORE INSERT ON &mw_prefix.archive +CREATE TRIGGER &mw_prefix.archive_seq_trg BEFORE INSERT ON &mw_prefix.archive FOR EACH ROW WHEN (new.ar_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(archive_ar_id_seq.nextval, :new.ar_id); END; /*$mw$*/ + +CREATE TABLE &mw_prefix.slots ( + slot_revision_id NUMBER NOT NULL, + slot_role_id NUMBER NOT NULL, + slot_content_id NUMBER NOT NULL, + slot_inherited CHAR(1) DEFAULT '0' NOT NULL +); + +ALTER TABLE &mw_prefix.slots ADD CONSTRAINT &mw_prefix.slots_pk PRIMARY KEY (slot_revision_id, slot_role_id); + +CREATE INDEX &mw_prefix.slot_role_inherited ON &mw_prefix.slots (slot_revision_id, slot_role_id, slot_inherited); + + +CREATE SEQUENCE content_content_id_seq; +CREATE TABLE &mw_prefix.content ( + content_id NUMBER NOT NULL, + content_size NUMBER NOT NULL, + content_sha1 VARCHAR2(32) NOT NULL, + content_model NUMBER NOT NULL, + content_address VARCHAR2(255) NOT NULL +); + +ALTER TABLE &mw_prefix.content ADD CONSTRAINT &mw_prefix.content_pk PRIMARY KEY (content_id); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_seq_trg BEFORE INSERT ON &mw_prefix.content + FOR EACH ROW WHEN (new.content_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_content_id_seq.nextval, :new.content_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE slot_roles_role_id_seq; +CREATE TABLE &mw_prefix.slot_roles ( + role_id NUMBER NOT NULL, + role_name VARCHAR2(64) NOT NULL +); + +ALTER TABLE &mw_prefix.slot_roles ADD CONSTRAINT &mw_prefix.slot_roles_pk PRIMARY KEY (role_id); + +CREATE UNIQUE INDEX &mw_prefix.role_name_u01 ON &mw_prefix.slot_roles (role_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.slot_roles_seq_trg BEFORE INSERT ON &mw_prefix.slot_roles + FOR EACH ROW WHEN (new.role_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(slot_roles_role_id_seq.nextval, :new.role_id); +END; +/*$mw$*/ + + +CREATE SEQUENCE content_models_model_id_seq; +CREATE TABLE &mw_prefix.content_models ( + model_id NUMBER NOT NULL, + model_name VARCHAR2(64) NOT NULL +); + + +ALTER TABLE &mw_prefix.content_models ADD CONSTRAINT &mw_prefix.content_models_pk PRIMARY KEY (model_id); + +CREATE UNIQUE INDEX &mw_prefix.model_name_u01 ON &mw_prefix.content_models (model_name); + +/*$mw$*/ +CREATE TRIGGER &mw_prefix.content_models_seq_trg BEFORE INSERT ON &mw_prefix.content_models + FOR EACH ROW WHEN (new.model_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(content_models_model_id_seq.nextval, :new.model_id); +END; +/*$mw$*/ + + CREATE TABLE &mw_prefix.pagelinks ( pl_from NUMBER NOT NULL, pl_namespace NUMBER DEFAULT 0 NOT NULL, @@ -273,7 +345,7 @@ ALTER TABLE &mw_prefix.category ADD CONSTRAINT &mw_prefix.category_pk PRIMARY KE CREATE UNIQUE INDEX &mw_prefix.category_u01 ON &mw_prefix.category (cat_title); CREATE INDEX &mw_prefix.category_i01 ON &mw_prefix.category (cat_pages); /*$mw$*/ -CREATE TRIGGER &mw_prefix.category_default_cat_id BEFORE INSERT ON &mw_prefix.category +CREATE TRIGGER &mw_prefix.category_seq_trg BEFORE INSERT ON &mw_prefix.category FOR EACH ROW WHEN (new.cat_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(category_cat_id_seq.nextval, :new.cat_id); @@ -286,7 +358,7 @@ CREATE TABLE &mw_prefix.externallinks ( el_from NUMBER NOT NULL, el_to VARCHAR2(2048) NOT NULL, el_index VARCHAR2(2048) NOT NULL, - el_index_60 VARBINARY(60) NOT NULL DEFAULT '' + el_index_60 VARCHAR2(60) ); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_pk PRIMARY KEY (el_id); ALTER TABLE &mw_prefix.externallinks ADD CONSTRAINT &mw_prefix.externallinks_fk1 FOREIGN KEY (el_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; @@ -296,7 +368,7 @@ CREATE INDEX &mw_prefix.externallinks_i03 ON &mw_prefix.externallinks (el_index) CREATE INDEX &mw_prefix.externallinks_i04 ON &mw_prefix.externallinks (el_index_60, el_id); CREATE INDEX &mw_prefix.externallinks_i05 ON &mw_prefix.externallinks (el_from, el_index_60, el_id); /*$mw$*/ -CREATE TRIGGER &mw_prefix.externallinks_default_el_id BEFORE INSERT ON &mw_prefix.externallinks +CREATE TRIGGER &mw_prefix.externallinks_seq_trg BEFORE INSERT ON &mw_prefix.externallinks FOR EACH ROW WHEN (new.el_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(externallinks_el_id_seq.nextval, :new.el_id); @@ -361,7 +433,7 @@ CREATE INDEX &mw_prefix.ipblocks_i03 ON &mw_prefix.ipblocks (ipb_timestamp); CREATE INDEX &mw_prefix.ipblocks_i04 ON &mw_prefix.ipblocks (ipb_expiry); CREATE INDEX &mw_prefix.ipblocks_i05 ON &mw_prefix.ipblocks (ipb_parent_block_id); /*$mw$*/ -CREATE TRIGGER &mw_prefix.ipblocks_default_ipb_id BEFORE INSERT ON &mw_prefix.ipblocks +CREATE TRIGGER &mw_prefix.ipblocks_seq_trg BEFORE INSERT ON &mw_prefix.ipblocks FOR EACH ROW WHEN (new.ipb_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(ipblocks_ipb_id_seq.nextval, :new.ipb_id); @@ -452,7 +524,7 @@ CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_ti CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp); CREATE INDEX &mw_prefix.filearchive_i05 ON &mw_prefix.filearchive (fa_sha1); /*$mw$*/ -CREATE TRIGGER &mw_prefix.filearchive_default_fa_id BEFORE INSERT ON &mw_prefix.filearchive +CREATE TRIGGER &mw_prefix.filearchive_seq_trg BEFORE INSERT ON &mw_prefix.filearchive FOR EACH ROW WHEN (new.fa_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(filearchive_fa_id_seq.nextval, :new.fa_id); @@ -485,7 +557,7 @@ CREATE INDEX &mw_prefix.uploadstash_i01 ON &mw_prefix.uploadstash (us_user); CREATE INDEX &mw_prefix.uploadstash_i02 ON &mw_prefix.uploadstash (us_timestamp); CREATE UNIQUE INDEX &mw_prefix.uploadstash_u01 ON &mw_prefix.uploadstash (us_key); /*$mw$*/ -CREATE TRIGGER &mw_prefix.uploadstash_default_us_id BEFORE INSERT ON &mw_prefix.uploadstash +CREATE TRIGGER &mw_prefix.uploadstash_seq_trg BEFORE INSERT ON &mw_prefix.uploadstash FOR EACH ROW WHEN (new.us_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(uploadstash_us_id_seq.nextval, :new.us_id); @@ -532,7 +604,7 @@ CREATE INDEX &mw_prefix.recentchanges_i06 ON &mw_prefix.recentchanges (rc_namesp CREATE INDEX &mw_prefix.recentchanges_i07 ON &mw_prefix.recentchanges (rc_user_text, rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i08 ON &mw_prefix.recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp); /*$mw$*/ -CREATE TRIGGER &mw_prefix.recentchanges_default_rc_id BEFORE INSERT ON &mw_prefix.recentchanges +CREATE TRIGGER &mw_prefix.recentchanges_seq_trg BEFORE INSERT ON &mw_prefix.recentchanges FOR EACH ROW WHEN (new.rc_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(recentchanges_rc_id_seq.nextval, :new.rc_id); @@ -617,7 +689,7 @@ CREATE INDEX &mw_prefix.logging_i05 ON &mw_prefix.logging (log_type, log_action, CREATE INDEX &mw_prefix.logging_i06 ON &mw_prefix.logging (log_user_text, log_type, log_timestamp); CREATE INDEX &mw_prefix.logging_i07 ON &mw_prefix.logging (log_user_text, log_timestamp); /*$mw$*/ -CREATE TRIGGER &mw_prefix.logging_default_log_id BEFORE INSERT ON &mw_prefix.logging +CREATE TRIGGER &mw_prefix.logging_seq_trg BEFORE INSERT ON &mw_prefix.logging FOR EACH ROW WHEN (new.log_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(logging_log_id_seq.nextval, :new.log_id); @@ -654,7 +726,7 @@ CREATE INDEX &mw_prefix.job_i03 ON &mw_prefix.job (job_sha1); CREATE INDEX &mw_prefix.job_i04 ON &mw_prefix.job (job_cmd,job_token,job_random); CREATE INDEX &mw_prefix.job_i05 ON &mw_prefix.job (job_attempts); /*$mw$*/ -CREATE TRIGGER &mw_prefix.job_default_job_id BEFORE INSERT ON &mw_prefix.job +CREATE TRIGGER &mw_prefix.job_seq_trg BEFORE INSERT ON &mw_prefix.job FOR EACH ROW WHEN (new.job_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(job_job_id_seq.nextval, :new.job_id); @@ -706,7 +778,7 @@ CREATE INDEX &mw_prefix.page_restrictions_i01 ON &mw_prefix.page_restrictions (p CREATE INDEX &mw_prefix.page_restrictions_i02 ON &mw_prefix.page_restrictions (pr_level); CREATE INDEX &mw_prefix.page_restrictions_i03 ON &mw_prefix.page_restrictions (pr_cascade); /*$mw$*/ -CREATE TRIGGER &mw_prefix.page_restrictions_default_pr_id BEFORE INSERT ON &mw_prefix.page_restrictions +CREATE TRIGGER &mw_prefix.page_restrictions_seq_trg BEFORE INSERT ON &mw_prefix.page_restrictions FOR EACH ROW WHEN (new.pr_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(page_restrictions_pr_id_seq.nextval, :new.pr_id); @@ -821,7 +893,7 @@ CREATE INDEX &mw_prefix.sites_i05 ON &mw_prefix.sites (site_protocol); CREATE INDEX &mw_prefix.sites_i06 ON &mw_prefix.sites (site_domain); CREATE INDEX &mw_prefix.sites_i07 ON &mw_prefix.sites (site_forward); /*$mw$*/ -CREATE TRIGGER &mw_prefix.sites_default_site_id BEFORE INSERT ON &mw_prefix.sites +CREATE TRIGGER &mw_prefix.sites_seq_trg BEFORE INSERT ON &mw_prefix.sites FOR EACH ROW WHEN (new.site_id IS NULL) BEGIN &mw_prefix.lastval_pkg.setLastval(sites_site_id_seq.nextval, :new.site_id);