DuplicationPackageBody


1   package com.teamkonzept.webman.mainint.db.queries.duplication.oracle;
2   
3   
4   import com.teamkonzept.db.*;
5   import java.io.*;
6   import java.util.*;
7   import java.sql.*;
8   
9   
10      /**
11       *  @author marwan
12       *  @version 1.5
13       *  @see DuplicationPackageDeclaration
14      */
15  
16  public class DuplicationPackageBody extends TKPrepQuery{
17  
18      public static boolean isPrepared =
19          true;
20  
21      public static String  [] paramOrder =
22          null;
23  
24      public static Object  [][] paramTypes =
25          null;
26  
27      public static boolean[] setRelevants =
28          null;
29  
30      public static String   sqlString =
31  
32     "CREATE OR REPLACE PACKAGE BODY DUPLICATION IS "
33   + "     " /* Procedure to fill contenttree translation table */
34   + "    PROCEDURE ct_fill_transtable(src_id INTEGER) "
35   + "    AS "
36   + "    BEGIN "
37   + "         " /* fill in the ids of subtree */
38   + "        INSERT INTO CT_TRANSLATE (OLD_ID) "
39   + "        SELECT CONTENT_NODE_ID "
40   + "        FROM CONTENT_TREE "
41   + "        WHERE UTILITY_PROCEDURES.ct_subnode(src_id, CONTENT_NODE_ID) = 1; "
42   + "         " /* compute new ids */
43   + "        UPDATE CT_TRANSLATE "
44   + "        SET NEW_ID = DUP_SEQUENCE.NEXTVAL; "
45   + "    END ct_fill_transtable; "
46  
47   + "     " /* Procedure to fill sitetree translation table */
48   + "    PROCEDURE st_fill_transtable(src_id INTEGER) "
49   + "    AS "
50   + "    BEGIN "
51   + "         " /* fill in the ids of subtree */
52   + "        INSERT INTO ST_TRANSLATE (OLD_ID) "
53   + "        SELECT SITE_NODE_ID "
54   + "        FROM SITE_TREE "
55   + "        WHERE UTILITY_PROCEDURES.st_subnode(src_id, SITE_NODE_ID) = 1; "
56   + "         " /* compute new ids */
57   + "        UPDATE ST_TRANSLATE "
58   + "        SET NEW_ID = DUP_SEQUENCE.NEXTVAL; "
59   + "    END st_fill_transtable; "
60  
61  
62   + "     " /* Procedure to copy a subtree, rootet at src_id under dest_id.  */
63   + "     " /* Relies on the existence of a table that translates old ids to new ones. */
64   + "    FUNCTION ct_copy_subtree(src_id INTEGER, dest_id INTEGER) RETURN INTEGER "
65   + "    AS "
66   + "    src_left    INTEGER; "
67   + "    src_right   INTEGER; "
68   + "    shift       INTEGER; "
69   + "    dest_right  INTEGER; "
70   + "    ct_inc      INTEGER; "
71   + "    new_node_id INTEGER; "
72   + "    BEGIN "
73   + "         " /* calculate shift for making place in the tree */
74   + "        SELECT LEFT_NR INTO src_left FROM CONTENT_TREE WHERE CONTENT_NODE_ID = src_id; "
75   + "        SELECT RIGHT_NR INTO src_right FROM CONTENT_TREE WHERE CONTENT_NODE_ID = src_id; "
76   + "        shift := (src_right - src_left) +1; "
77  
78   + "         " /* calculate ct_inc, for moving a subtree */
79   + "        SELECT RIGHT_NR INTO dest_right FROM CONTENT_TREE WHERE CONTENT_NODE_ID = dest_id; "
80   + "        ct_inc := dest_right - src_left; "
81  
82   + "         " /* Insert new records */
83   + "        INSERT INTO CONTENT_TREE ( CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME, CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, CONTENT_FORM, CONTENT_NODE_PARENT, TREE_ID, PROTOTYPE_ID) "
84   + "        SELECT NEW_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME, CONTENT_NODE_TYPE, LEFT_NR, RIGHT_NR, CONTENT_FORM, CONTENT_NODE_PARENT, TREE_ID, PROTOTYPE_ID "
85   + "        FROM CONTENT_TREE, CT_TRANSLATE "
86   + "        WHERE CONTENT_NODE_ID = CT_TRANSLATE.OLD_ID; "
87  
88   + "         " /* Update left / right nrs  of insertet records */
89   + "        UPDATE CONTENT_TREE "
90   + "        SET LEFT_NR = LEFT_NR + ct_inc, RIGHT_NR = RIGHT_NR + ct_inc "
91   + "        WHERE CONTENT_NODE_ID IN (SELECT NEW_ID FROM CT_TRANSLATE); "
92  
93   + "        SELECT NEW_ID INTO new_node_id FROM CT_TRANSLATE WHERE OLD_ID = src_id; "
94  
95   + "        UPDATE CONTENT_TREE C_T "
96   + "        SET C_T.CONTENT_NODE_PARENT = (SELECT NEW_ID "
97   + "            FROM CT_TRANSLATE "
98   + "            WHERE OLD_ID = C_T.CONTENT_NODE_PARENT "
99   + "            ) "
100  + "        WHERE CONTENT_NODE_ID IN (SELECT NEW_ID FROM CT_TRANSLATE); "
101 
102 
103  + "         " /* Adjust new root's CONTENT_NODE_PARENT */
104  + "        UPDATE CONTENT_TREE  "
105  + "        SET CONTENT_NODE_PARENT = dest_id "
106  + "        WHERE CONTENT_NODE_ID = new_node_id; "
107 
108  + "         " /* make place */
109  + "        UPDATE CONTENT_TREE  "
110  + "        SET LEFT_NR = LEFT_NR +  shift  "
111  + "        WHERE LEFT_NR > dest_right "
112  + "        AND CONTENT_NODE_ID NOT IN (SELECT NEW_ID FROM CT_TRANSLATE) ; "
113 
114 
115  + "        UPDATE CONTENT_TREE   "
116  + "        SET RIGHT_NR = RIGHT_NR + shift  "
117  + "        WHERE RIGHT_NR >=  dest_right "
118  + "        AND CONTENT_NODE_ID NOT IN (SELECT NEW_ID FROM CT_TRANSLATE); "
119 
120  + "        RETURN new_node_id; "
121 
122  + "    END ct_copy_subtree; "
123 
124 
125  + "    FUNCTION duplicate_content(con_id INTEGER) RETURN INTEGER "
126  + "    AS "
127  + "        new_con_id  INTEGER; "
128  + "    BEGIN "
129  + "        SELECT C_SEQUENCE.NEXTVAL into new_con_id FROM DUAL; "
130 
131  + "        INSERT INTO CONTENT (CONTENT_ID, XML_TEXT)"
132 // + "      VALUES (new_con_id); "
133  + "        SELECT new_con_id, XML_TEXT FROM CONTENT WHERE CONTENT_ID = con_id;"
134 
135  + "        INSERT INTO CONTENT_NODE "
136  + "        SELECT new_con_id, C_N.CONTENT_NODE_ID, C_N.LEFT_NR, C_N.RIGHT_NR, C_N.NAME "
137  + "        FROM CONTENT_NODE C_N "
138  + "        WHERE CONTENT_ID = con_id; "
139 
140  + "        INSERT INTO CONTENT_VALUE "
141  + "        SELECT new_con_id, C_V.CONTENT_NODE_ID, C_V.IDX, C_V.VALUE, C_V.MEDIA_ID "
142  + "        FROM CONTENT_VALUE C_V "
143  + "        WHERE CONTENT_ID = con_id; "
144 
145  + "        RETURN new_con_id; "
146  + "    END duplicate_content; "
147 
148 
149 
150  + "    PROCEDURE duplicate_content_instances "
151  + "    AS "
152  + "        CURSOR ct_trans_cursor IS "
153  + "            SELECT * "
154  + "            FROM CT_TRANSLATE; "
155  + "        CURSOR ci_cursor (con_id INTEGER) IS "
156  + "            SELECT * "
157  + "            FROM CONTENT_INSTANCE  "
158  + "            WHERE CONTENT_NODE_ID = con_id; "
159 
160  + "        inst_id_new INTEGER; "
161  + "        con_id_new  INTEGER; "
162  + "    BEGIN "
163  + "         " /* Loop over CT_TRANSLATE */
164  + "        FOR ct_trans_rec IN ct_trans_cursor "
165  + "        LOOP "
166  + "             " /* Loop over CONTENT_INSTANCE */
167  + "            FOR ci_rec IN ci_cursor(ct_trans_rec.OLD_ID) "
168  + "            LOOP     "
169  + "                SELECT  CI_SEQUENCE.NEXTVAL INTO inst_id_new FROM DUAL; "
170 
171 
172  + "                INSERT INTO CONTENT_INSTANCE "
173  + "                VALUES (inst_id_new, ct_trans_rec.NEW_ID, ci_rec.NAME); "
174 
175 
176  + "                 " /* Duplicate CONTENT_VERSION and other CONTENT - tables */
177 
178  + "                INSERT INTO CONTENT_VERSION (VERSION_ID, INSTANCE_ID, CONTENT_ID, STATUS_ID, VERSION_DATE, INFO, AUTHOR, INTERESTING) "
179  + "                SELECT CV_SEQUENCE.NEXTVAL, inst_id_new, duplicate_content(CONTENT_ID), STATUS_ID, VERSION_DATE, INFO, AUTHOR, INTERESTING "
180  + "                FROM CONTENT_VERSION "
181  + "                WHERE INSTANCE_ID = ci_rec.INSTANCE_ID "
182  + "                AND "
183  + "                CONTENT_ID IS NOT NULL; "
184 
185  + "            END LOOP; "
186  + "        END LOOP; "
187  + "    END duplicate_content_instances; "
188 
189  + "    FUNCTION st_copy_subtree(src_id INTEGER, dest_id INTEGER) RETURN INTEGER "
190  + "    AS "
191  + "    src_left    INTEGER; "
192  + "    src_right   INTEGER; "
193  + "    shift       INTEGER; "
194  + "    dest_right  INTEGER; "
195  + "    st_inc      INTEGER; "
196  + "    new_node_id INTEGER; "
197  + "    BEGIN "
198  + "         " /* calculate shift for making place in the tree */
199  + "        SELECT LEFT_NR INTO src_left FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
200  + "        SELECT RIGHT_NR INTO src_right FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
201  + "        shift := (src_right - src_left) +1; "
202 
203  + "         " /* calculate ct_inc, for moving a subtree */
204  + "        SELECT RIGHT_NR INTO dest_right FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
205  + "        st_inc := dest_right - src_left; "
206 
207 
208 
209  + "         " /* Insert new records */
210  + "        INSERT INTO SITE_TREE  "
211  + "        SELECT ST_TRANSLATE.NEW_ID, SITE_NODE_PARENT, SITE_NODE_TYPE, SITE_NODE_NAME, SITE_NODE_SHORTNAME, LEFT_NR, RIGHT_NR, PG_CONTENT_NODE, PG_SELECTION_ID "
212  + "        FROM SITE_TREE, ST_TRANSLATE "
213  + "        WHERE  "
214  + "        SITE_TREE.SITE_NODE_ID = ST_TRANSLATE.OLD_ID ;"
215  //+ "      AND "
216  // + "         SITE_TREE.PG_CONTENT_NODE = CT_TRANSLATE.OLD_ID (+); "
217 
218  + " UPDATE SITE_TREE TREE"
219  + " SET TREE.PG_CONTENT_NODE = (SELECT NEW_ID FROM CT_TRANSLATE WHERE OLD_ID=TREE.PG_CONTENT_NODE) "
220  + " WHERE TREE.SITE_NODE_ID IN (SELECT NEW_ID   FROM ST_TRANSLATE)       " // only duplicated SITE_TREE nodes are considered 
221  + "       AND "
222  + "       TREE.PG_CONTENT_NODE IN (SELECT OLD_ID FROM CT_TRANSLATE);        " // join-condition 
223  
224 
225 
226  + "         " /* Update left / right nrs  of inserted records */
227  + "        UPDATE SITE_TREE "
228  + "        SET LEFT_NR = LEFT_NR + st_inc, RIGHT_NR = RIGHT_NR + st_inc "
229  + "        WHERE SITE_NODE_ID IN (SELECT NEW_ID FROM ST_TRANSLATE); "
230 
231  + "        SELECT NEW_ID INTO new_node_id FROM ST_TRANSLATE WHERE OLD_ID = src_id; "
232 
233  + "        UPDATE SITE_TREE S_T "
234  + "        SET S_T.SITE_NODE_PARENT = (SELECT NEW_ID "
235  + "            FROM ST_TRANSLATE "
236  + "            WHERE OLD_ID = S_T.SITE_NODE_PARENT "
237  + "            ) "
238  + "        WHERE SITE_NODE_ID IN (SELECT NEW_ID FROM ST_TRANSLATE); "
239 
240  + "         " /* Adjust new root's SITE_NODE_PARENT */
241  + "        UPDATE SITE_TREE  "
242  + "        SET SITE_NODE_PARENT = dest_id "
243  + "        WHERE SITE_NODE_ID = new_node_id; "
244 
245 
246  + "         " /* make place */
247  + "        UPDATE SITE_TREE   "
248  + "        SET LEFT_NR = LEFT_NR +  shift  "
249  + "        WHERE LEFT_NR > dest_right "
250  + "        AND SITE_NODE_ID NOT IN (SELECT NEW_ID FROM ST_TRANSLATE); "
251 
252  + "        UPDATE SITE_TREE   "
253  + "        SET RIGHT_NR = RIGHT_NR + shift  "
254  + "        WHERE RIGHT_NR >=  dest_right "
255  + "        AND SITE_NODE_ID NOT IN (SELECT NEW_ID FROM ST_TRANSLATE); "
256 
257 
258  + "         " /* Dupicate STURCTURED_CONTENT */
259  + "        INSERT INTO STRUCTURED_CONTENT "
260  + "        SELECT NEW_ID, FORM_ID, duplicate_content(CONTENT_ID) "
261  + "        FROM STRUCTURED_CONTENT, ST_TRANSLATE "
262  + "        WHERE STRUCTURED_CONTENT.SITE_NODE_ID = ST_TRANSLATE.OLD_ID; "
263 
264  + "        RETURN new_node_id; "
265  + "    END st_copy_subtree; "
266 
267  + "    PROCEDURE duplicate_documents "
268  + "    AS "
269  + "        new_dest_id INTEGER; "
270  + "        CURSOR doc_ref_cursor IS  "
271  + "            SELECT * "
272  + "            FROM DOCUMENT_REFERENCE, ST_TRANSLATE "
273  + "            WHERE SRC_SITE_NODE_ID = OLD_ID; "
274  + "    BEGIN "
275 
276  + "         " /* SITE_DOCUMENT */
277  + "        INSERT INTO SITE_DOCUMENT  "
278  + "        SELECT NEW_ID, SITE_NODE_DOC_IDX, PRESENTATION_ID, DOCUMENT_NAME, DOCUMENT_SHORTNAME, INHERITABLE, DOCUMENT_TYPE, EXTERNAL_URL, INHERIT_END_LEVEL, INHERIT_BEGIN_LEVEL "
279  + "        FROM SITE_DOCUMENT, ST_TRANSLATE "
280  + "        WHERE SITE_NODE_ID = OLD_ID; "
281 
282  + "         " /* DOCUMENT_CONTENT without adjustment of CONTENT_NODE_ID, see procedure "document_content_adjustref()" */
283  + "        INSERT INTO DOCUMENT_CONTENT "
284  + "        SELECT NEW_ID, SITE_NODE_DOC_IDX, PRESENTATION_COMPONENT_IDX, CONTENT_NODE_ID, PROTOTYPE_NODE_ID, SELECTION_ID "
285  + "        FROM DOCUMENT_CONTENT, ST_TRANSLATE "
286  + "        WHERE SITE_NODE_ID = OLD_ID; "
287 
288 
289  + "         " /* DOCUMENT_REFERENCE with adjustment of relative references, see function "utility_procedures.calc_rel_ref()" */
290 
291  + "        FOR d_rec IN doc_ref_cursor "
292  + "        LOOP "
293  + "            BEGIN "
294  + "                new_dest_id := UTILITY_PROCEDURES.calc_rel_ref(d_rec.SRC_SITE_NODE_ID, d_rec.DEST_SITE_NODE_ID, d_rec.NEW_ID, d_rec.REFERENCE_TYPE); "
295  + "            EXCEPTION "
296  + "                WHEN UTILITY_PROCEDURES.ILLEGAL_REL_REF THEN  "
297  + "                    DBMS_OUTPUT.PUT_LINE(' duplicate_documents cought exception ILLEGAL_REL_REF'); "
298  + "                    new_dest_id := NULL; "
299  + "                    EXIT; "
300  + "            END; "
301  + "             " /* if calc_rel_ref does not throw an exeption */
302  + "            INSERT INTO DOCUMENT_REFERENCE "
303  + "                VALUES( "
304  + "                d_rec.NEW_ID, d_rec.SRC_SITE_NODE_DOC_IDX, d_rec.SRC_PRESENTATION_COMPONENT_IDX, d_rec.REFERENCE_TYPE,  "
305  + "                new_dest_id, d_rec.DEST_SITE_NODE_DOC_SHORTNAME, d_rec.SELECTION_TYPE, d_rec.SELECTION_DATA "
306  + "                ) "
307  + "            ; "
308  + "        END LOOP; "
309  + "    END duplicate_documents; "
310 
311 
312 
313  + "    PROCEDURE document_content_adjustref "
314  + "    AS "
315  + "        con_id  INTEGER; "
316  + "        CURSOR dc_cursor IS SELECT * FROM DOCUMENT_CONTENT, ST_TRANSLATE  "
317  + "                 WHERE SITE_NODE_ID = OLD_ID "
318  + "                 FOR UPDATE; "
319  + "    BEGIN "
320  + "        FOR dc_rec IN dc_cursor LOOP "
321  + "            BEGIN "
322  + "                SELECT NEW_ID INTO con_id  "
323  + "                FROM CT_TRANSLATE WHERE OLD_ID = dc_rec.CONTENT_NODE_ID;  "
324  + "            EXCEPTION "
325  + "                WHEN NO_DATA_FOUND THEN "
326  + "                        con_id:=NULL; "
327  + "            END; "
328  + "            IF (con_id IS NOT NULL) THEN "
329  + "                UPDATE DOCUMENT_CONTENT  "
330  + "                SET CONTENT_NODE_ID = con_id "
331  + "                WHERE CURRENT OF dc_cursor; "
332  + "            END IF;  "
333  + "        END LOOP; "
334  + "    END document_content_adjustref; "
335 
336 
337 
338  + "    FUNCTION select_results(ct_id   INTEGER, st_id  INTEGER) RETURN cursor_type  "
339  + "    as  "
340  + "        l_cursor    cursor_type;  "
341  + "        ct_id_new   INTEGER; "
342  + "        st_id_new   INTEGER; "
343 
344  + "        ct_ex   INTEGER; "
345  + "        st_ex   INTEGER; "
346  + "    begin  "
347  + "        SELECT COUNT(*) INTO ct_ex FROM CT_TRANSLATE; "
348  + "        IF (ct_ex > 0) THEN "
349  + "            select NEW_ID into ct_id_new  FROM CT_TRANSLATE WHERE OLD_ID = ct_id; "
350  + "        END IF; "
351 
352  + "        SELECT COUNT(*) INTO st_ex FROM ST_TRANSLATE; "
353  + "        IF (st_ex > 0) THEN "
354  + "            select NEW_ID into st_id_new  FROM ST_TRANSLATE WHERE OLD_ID = st_id; "
355  + "        END IF;  "
356  + "        open l_cursor for select ct_id_new NEW_CONT_ROOT_ID, st_id_new NEW_SITE_ROOT_ID from dual;  "
357  + "        return l_cursor;  "
358  + "    end select_results;  "
359 
360 
361  + "    PROCEDURE duplicate_manager( "
362  + "        ct_src INTEGER, ct_dest INTEGER, ct_new_name VARCHAR2, ct_new_shortname VARCHAR2, "
363  + "        st_src INTEGER, st_dest INTEGER, st_new_name VARCHAR2, st_new_shortname VARCHAR2 "
364  + "    ) "
365  + "    AS "
366  + "    new_ct_id       INTEGER; "
367  + "    new_st_id       INTEGER; "
368  + "    contenttree_flag    BOOLEAN; "
369  + "    sitetree_flag       BOOLEAN; "
370  + "    BEGIN "
371  + "     " /* Decide which parts to copy */
372  + "     contenttree_flag := (ct_src IS NOT NULL) AND (ct_dest IS NOT NULL); "
373  + "     sitetree_flag    := (st_src IS NOT NULL) AND (st_dest IS NOT NULL); "
374 
375  + "     " /* Content Tree */
376  + "    IF(contenttree_flag) THEN "
377 
378  + "        ct_fill_transtable(ct_src); "
379  + "        new_ct_id := ct_copy_subtree(ct_src, ct_dest); "
380 
381  + "        UPDATE CONTENT_TREE "
382  + "        SET CONTENT_NODE_NAME = ct_new_name, CONTENT_NODE_SHORTNAME = ct_new_shortname  "
383  + "        WHERE CONTENT_NODE_ID = new_ct_id; "
384 
385  + "         " /* Content */
386  + "        duplicate_content_instances(); "
387  + "    END IF; "
388 
389  + "     " /* Site Tree */
390  + "    IF(sitetree_flag) THEN "
391 
392  + "        st_fill_transtable(st_src); "
393 
394  + "        new_st_id := st_copy_subtree(st_src, st_dest); "
395  + "         " /* Documents */
396 
397  + "        duplicate_documents(); "
398 
399  + "         " /* Could be optional */
400  + "        IF(contenttree_flag) THEN "
401  + "            document_content_adjustref(); "
402  + "        END IF; "
403 
404  + "        UPDATE SITE_TREE "
405  + "        SET SITE_NODE_NAME = st_new_name, SITE_NODE_SHORTNAME = st_new_shortname "
406  + "        WHERE SITE_NODE_ID = new_st_id;  "
407  + "    END IF;  "
408 
409  + "    END duplicate_manager; "
410 
411 
412  + " END DUPLICATION; "
413 ;
414     public void initQuery(Connection con) {
415         super.initQuery(
416             con,
417             isPrepared,
418             paramOrder,
419             paramTypes,
420             setRelevants,
421             sqlString );
422     }
423 }
424 
425
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags