X-Git-Url: http://git.cyclocoop.org/?a=blobdiff_plain;f=maintenance%2Fora%2Ftables.sql;h=c300e939b181cdc45910357121457c5f338288a6;hb=653d7a89a4454bc0d1edb3348a3aac038d443168;hp=f7dcad3c0610c4bb7346ab990a017f16bb638889;hpb=9f5b309aaac51b2c827a56a04c0e5150eaecb4bb;p=lhc%2Fweb%2Fwiklou.git

diff --git a/maintenance/ora/tables.sql b/maintenance/ora/tables.sql
index f7dcad3c06..c300e939b1 100644
--- a/maintenance/ora/tables.sql
+++ b/maintenance/ora/tables.sql
@@ -79,7 +79,7 @@ BEGIN
 END;
 /*$mw$*/
 
-CREATE SEQUENCE rev_rev_id_val;
+CREATE SEQUENCE revision_rev_id_seq;
 CREATE TABLE &mw_prefix.revision (
   rev_id          NUMBER      NOT NULL,
   rev_page        NUMBER          NULL  REFERENCES &mw_prefix.page (page_id) ON DELETE CASCADE,
@@ -100,7 +100,7 @@ CREATE INDEX &mw_prefix.revision_i02 ON &mw_prefix.revision (rev_page,rev_timest
 CREATE INDEX &mw_prefix.revision_i03 ON &mw_prefix.revision (rev_user,rev_timestamp);
 CREATE INDEX &mw_prefix.revision_i04 ON &mw_prefix.revision (rev_user_text,rev_timestamp);
 
-CREATE SEQUENCE text_old_id_val;
+CREATE SEQUENCE text_old_id_seq;
 CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text'
   old_id     NUMBER  NOT NULL,
   old_text   CLOB,
@@ -163,7 +163,7 @@ CREATE UNIQUE INDEX &mw_prefix.categorylinks_u01 ON &mw_prefix.categorylinks (cl
 CREATE INDEX &mw_prefix.categorylinks_i01 ON &mw_prefix.categorylinks (cl_to,cl_sortkey,cl_from);
 CREATE INDEX &mw_prefix.categorylinks_i02 ON &mw_prefix.categorylinks (cl_to,cl_timestamp);
 
-CREATE SEQUENCE category_id_seq;
+CREATE SEQUENCE category_cat_id_seq;
 CREATE TABLE &mw_prefix.category (
   cat_id NUMBER NOT NULL,
   cat_title VARCHAR2(255) NOT NULL,
@@ -210,7 +210,7 @@ CREATE TABLE &mw_prefix.hitcounter (
   hc_id  NUMBER  NOT NULL
 );
 
-CREATE SEQUENCE ipblocks_ipb_id_val;
+CREATE SEQUENCE ipblocks_ipb_id_seq;
 CREATE TABLE &mw_prefix.ipblocks (
   ipb_id                NUMBER      NOT NULL,
   ipb_address           VARCHAR2(255)     NULL,
@@ -314,7 +314,7 @@ CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_gr
 CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp);
 CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp);
 
-CREATE SEQUENCE rc_rc_id_seq;
+CREATE SEQUENCE recentchanges_rc_id_seq;
 CREATE TABLE &mw_prefix.recentchanges (
   rc_id              NUMBER      NOT NULL,
   rc_timestamp       TIMESTAMP(6) WITH TIME ZONE  NOT NULL,
@@ -409,7 +409,7 @@ CREATE TABLE &mw_prefix.transcache (
 CREATE UNIQUE INDEX &mw_prefix.transcache_u01 ON &mw_prefix.transcache (tc_url);
 
 
-CREATE SEQUENCE log_log_id_seq;
+CREATE SEQUENCE logging_log_id_seq;
 CREATE TABLE &mw_prefix.logging (
   log_id          NUMBER      NOT NULL,
   log_type        VARCHAR2(10)         NOT NULL,
@@ -488,7 +488,7 @@ CREATE INDEX &mw_prefix.querycachetwo_i01 ON &mw_prefix.querycachetwo (qcc_type,
 CREATE INDEX &mw_prefix.querycachetwo_i02 ON &mw_prefix.querycachetwo (qcc_type,qcc_namespace,qcc_title);
 CREATE INDEX &mw_prefix.querycachetwo_i03 ON &mw_prefix.querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
 
-CREATE SEQUENCE pr_id_val;
+CREATE SEQUENCE page_restrictions_pr_id_seq;
 CREATE TABLE &mw_prefix.page_restrictions (
   pr_id      NUMBER      NOT NULL,
   pr_page    NUMBER          NULL  REFERENCES &mw_prefix.page (page_id) ON DELETE CASCADE,
@@ -638,6 +638,77 @@ CREATE PROCEDURE &mw_prefix.fill_wiki_info IS
 END;
 /*$mw$*/
 
+/*$mw$*/
+CREATE OR REPLACE PROCEDURE duplicate_table(p_oldname IN VARCHAR2, p_newname IN VARCHAR2, p_temporary IN BOOLEAN)
+IS
+       v_oldname VARCHAR2(32) := RTRIM(LTRIM(p_oldname, '"'), '"');
+       v_newname VARCHAR2(32) := RTRIM(LTRIM(p_newname, '"'), '"');
+       v_prefix VARCHAR2(32) := SUBSTR(v_newname, 1, INSTR(v_newname, v_oldname, -1)-1);
+       e_table_not_exist EXCEPTION;
+       PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942);
+BEGIN
+       BEGIN
+           EXECUTE IMMEDIATE 'DROP TABLE '||v_newname||' CASCADE CONSTRAINTS';
+       EXCEPTION WHEN e_table_not_exist THEN NULL; END;
+
+       IF (p_temporary) THEN
+          EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE '||v_newname||
+                  ' AS SELECT * FROM '||v_oldname||' WHERE ROWNUM = 0';
+       ELSE
+          EXECUTE IMMEDIATE 'CREATE TABLE '||v_newname||
+                  ' AS SELECT * FROM '||v_oldname||' WHERE ROWNUM = 0';
+       END IF;
+
+       FOR rc IN (SELECT column_name, data_default 
+                    FROM user_tab_columns 
+                   WHERE table_name = v_oldname 
+                     AND data_default IS NOT NULL) LOOP
+          EXECUTE IMMEDIATE 'ALTER TABLE '||v_newname||' MODIFY '||rc.column_name||' DEFAULT '||substr(rc.data_default, 1, 2000);
+       END LOOP;
+       
+       FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name), 32767, 1),
+                                 USER||'"."', USER||'"."'||v_prefix),
+                                 '"'||constraint_name||'"', '"'||v_prefix||constraint_name||'"') DDLVC2
+                                 , constraint_name
+                    FROM user_constraints uc
+                    WHERE table_name = v_oldname
+                      AND constraint_type = 'P') LOOP
+          EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE')-1);
+       END LOOP;
+
+       FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl ('REF_CONSTRAINT', constraint_name), 32767, 1),
+                                 USER||'"."', USER||'"."'||v_prefix) DDLVC2
+                                 , constraint_name
+                    FROM user_constraints uc
+                    WHERE table_name = v_oldname
+                      AND constraint_type = 'R') LOOP
+          EXECUTE IMMEDIATE rc.ddlvc2;
+       END LOOP;
+
+       FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl ('INDEX', index_name), 32767, 1),
+                                 USER||'"."', USER||'"."'||v_prefix),
+                                 '"'||index_name||'"', '"'||v_prefix||index_name||'"') DDLVC2
+                                 , index_name
+                    FROM user_indexes ui
+                    WHERE table_name = v_oldname
+                      AND index_type != 'LOB'
+                      AND NOT EXISTS (SELECT NULL FROM user_constraints
+                                       WHERE table_name = ui.table_name
+                                         AND constraint_name = ui.index_name)) LOOP
+          EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE')-1);
+       END LOOP;
+
+       FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl ('TRIGGER', trigger_name), 32767, 1)),
+                                 USER||'"."', USER||'"."'||v_prefix),
+                                 ' ON '||v_oldname, ' ON '||v_newname) DDLVC2
+                                 , trigger_name
+                    FROM user_triggers
+                    WHERE table_name = v_oldname) LOOP
+          EXECUTE IMMEDIATE SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'ALTER ')-1);
+       END LOOP;
+END;
+/*$mw$*/
+
 /*$mw$*/
 BEGIN 
 	&mw_prefix.fill_wiki_info;
@@ -658,3 +729,33 @@ BEGIN
 END;
 /*$mw$*/
 
+/*$mw$*/
+CREATE OR REPLACE TYPE GET_OUTPUT_TYPE IS TABLE OF VARCHAR2(255);
+/*$mw$*/
+
+/*$mw$*/
+CREATE OR REPLACE FUNCTION GET_OUTPUT_LINES RETURN GET_OUTPUT_TYPE PIPELINED AS
+  v_line VARCHAR2(255);
+  v_status INTEGER := 0;
+BEGIN
+
+  LOOP
+    DBMS_OUTPUT.GET_LINE(v_line, v_status);
+    IF (v_status = 0) THEN RETURN; END IF;
+    PIPE ROW (v_line);
+  END LOOP;
+  RETURN;
+EXCEPTION
+  WHEN OTHERS THEN
+    RETURN;
+END;
+/*$mw$*/
+
+/*$mw$*/
+CREATE OR REPLACE FUNCTION GET_SEQUENCE_VALUE(seq IN VARCHAR2) RETURN NUMBER AS
+	v_value NUMBER;
+BEGIN
+	EXECUTE IMMEDIATE 'SELECT '||seq||'.NEXTVAL INTO :outVar FROM DUAL' INTO v_value;
+	RETURN v_value;
+END;
+/*$mw$*/