KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > duplication > TKDBDuplicateQuery


1 package com.teamkonzept.webman.mainint.db.queries.duplication;
2
3 import com.teamkonzept.db.*;
4 import com.teamkonzept.webman.mainint.DatabaseDefaults;
5
6 import java.io.*;
7 import java.util.*;
8 import java.sql.*;
9
10
11
12 /* Version 07, mit der Mšglichkeit CONTENT CONTENT_NODE und CONTENT_VALUE mitzuduplizieren. */
13 /* Version 06, Schalter fŸr Site Tree Duplizierung eingebaut */
14 /* Version 05, @GLOBAL_SHIFT berechnung geŠndert ( +1 ) an ZWEI Stellen!! */
15
16
17 /* Mit den SQL - Statements in diesem Batch werden TeilbŠume von CONTENT_TREE und SITE_TREE */
18 /* dupliziert, und die Referenzen unter diesen bleiben erhalten. */
19
20 /* Im einzelnen passiert folgendes: */
21
22 /* a) ( 1 - 3 ) Ein Teilbaum A von CONTENT_TREE, mit Wurzel @TEMPLATE_ROOT_ID wird zu A' dupliziert */
23 /* und an den Knoten @TARGET_ROOT_ID angehŠngt. */
24
25 /* b) ( 4 - 6 ) Ein Teilbaum B von SITE_TREE wird zu B' dupliziert.*/
26 /* 4 - 11 lŠsst sich mit @SITE_FLAG ausschalten. */
27
28 /* c) ( 7 ) Wenn das Attribut PG_CONTENT_NODE_ID in B' auf A verweist, wird es auf den */
29 /* entsprechenden Knoten in A' umgehŠngt.*/
30
31 /* d) ( 8 ) Alle SITE_DOCUMENT records, deren SITE_NODE_ID auf B verweist, werden dupliziert */
32 /* und die duplizierten werden auf B' umgehŠngt. */
33
34 /* e) ( 9 ) In DOCUMENT_CONTENT werden alle Records, deren SITE_NODE_ID auf B zeigt dupliziert, */
35 /* und die Duplikate werden auf B' umgehŠngt. */
36 /* Die CONTENT_NODE_IDs der duplizierten Records werden, wenn sie auf A zeigen, auf A' */
37 /* umgehŠngt. */
38
39 /* f) ( 10 ) Alle DOCUMENT_REFERENCE Records deren SRC_SITE_NODE_ID auf B zeigt werden dupliziert,*/
40 /* die Duplikate werden umgehŠngt. */
41 /* Diejenigen DST_SITE_NODE_ID Werte in duplizierten Records, die auf A zeigen, werden auf */
42 /* A' umgehŠngt.*/
43
44 /* g) ( 11 )STRUCTURED_CONTENT Records deren SITE_NODE_ID auf B zeigt werden dupliziert, */
45 /* die Duplikate auf B' umgehŠngt. */
46 /* h) (12) Diejenigen CONTENT Records, die von einem duplizierten STRUCTURED_CONTENT Record referenziert werden, */
47 /* werden ihrerseits dupliziert. Auch der darean hŠngende Baum aus CONTENT_NODE und CONTENT_VALUE records. */
48
49
50
51 /* INPUT: TEMPLATE_ROOT_ID (Wurzelknoten ID des zu duplizierenden CONTENT_TREE Teilbaumes ) */
52 /* TARGET_ROOT_ID (An diesen CONTENT_TREE Node wird der duplizierte Teilbaum als rechtestes Kind angehängt ) */
53 /* ST_SUBTREE_ROOT_ID (Wurzelknoten ID des zu duplizierenden SITE_TREE Teilbaumes ) */
54 /* ST_TARGET_ROOT_ID (An diesen SITE_TREE Node wird der duplizierte Teilbaum als rechtestes Kind angehängt ) */
55 /* NEW_CONTENT_ROOT_NAME (Namen fŸr die Wurzelknoten der neuen SubbŠume) */
56 /* NEW_CONTENT_ROOT_SHORTNAME */
57 /* NEW_SITE_ROOT_NAME */
58 /* NEW_SITE_ROOT_SHORTNAME */
59 /* SITE_TREE_FLAG (Die Flags dienen zum an und ausschalten des Kopierens der Bereiche Site-Tree und Content.) */
60 /* CONTENT_FLAG Obsolete in the current version */
61
62
63 /*
64     * @author $Author: uli $
65     * @version $Revision: 1.15 $
66 */
67 public class TKDBDuplicateQuery extends TKPrepQuery implements DatabaseDefaults{
68
69     /** prepeared */
70     public final static boolean isPrepared =
71         true;
72
73     /**
74         Parameter Reihenfolge */
75     public final static String JavaDoc[] paramOrder =
76         { //"SITE_TREE_FLAG","CONTENT_FLAG",
77 "TEMPLATE_ROOT_ID", "TARGET_ROOT_ID",
78           "NEW_CONTENT_ROOT_NAME", "NEW_CONTENT_ROOT_SHORTNAME",
79           "ST_SUBTREE_ROOT_ID", "ST_TARGET_ROOT_ID",
80           "NEW_SITE_ROOT_NAME", "NEW_SITE_ROOT_SHORTNAME" };
81
82     public final static Object JavaDoc[][] paramTypes =
83         {{ "NEW_CONTENT_ROOT_NAME ", new Integer JavaDoc(Types.VARCHAR) },
84          { "NEW_CONTENT_ROOT_SHORTNAME", new Integer JavaDoc(Types.VARCHAR) },
85          { "NEW_SITE_ROOT_NAME", new Integer JavaDoc(Types.VARCHAR) },
86          { "NEW_SITE_ROOT_SHORTNAME", new Integer JavaDoc(Types.VARCHAR) },
87         };
88
89     public final static boolean[] setRelevants =
90         {true, true};
91
92     public final static String JavaDoc sqlString =
93
94 /* Temporary Tables: */
95
96
97 /* Sitetree tables */
98
99  " CREATE TABLE #ST_tmp ( "
100  + " SITE_NODE_ID int not null, "
101  + " SITE_NODE_PARENT int null , "
102  + " SITE_NODE_TYPE int not null, "
103  + " SITE_NODE_NAME varchar(254) not null, "
104  + " SITE_NODE_SHORTNAME varchar(80) not null, "
105  + " LEFT_NR int not null, "
106  + " RIGHT_NR int not null, "
107  + " PG_CONTENT_NODE int null , "
108  + " PG_SELECTION_ID int null , "
109  + " ) "
110
111  + " CREATE TABLE #ST_TRANSLATE(OLD_ID int, NEW_ID int NULL) "
112
113
114
115 /* Contenttree tables */
116
117 /*zna*/
118  + " CREATE TABLE #tmp (CONTENT_NODE_ID int, CONTENT_NODE_NAME varchar(254), CONTENT_NODE_SHORTNAME varchar(80), CONTENT_NODE_TYPE int, LEFT_NR int, RIGHT_NR int, CONTENT_FORM int NULL, CONTENT_NODE_PARENT int null, TREE_ID int, PROTOTYPE_ID int null) "
119
120  + " CREATE TABLE #TRANSLATE(OLD_ID int, NEW_ID int NULL) "
121
122
123
124
125
126
127 /* Content tables */
128
129  + " create table #CONTENT "
130  + " ( "
131  + " CONTENT_ID int not null, "
132  + " XML_TEXT text null "
133  // zna commented
134 // + " CONTENT_NODE_ID int null , "
135 // + " CONTENT_NAME varchar(254) null , "
136 // + " CONTENT_SHORTNAME varchar(80) null , "
137 // + " ORDER_IDX int null "
138 + " ) "
139
140
141  + " CREATE TABLE #CONTENT_TRANSLATE(OLD_ID int, NEW_ID int NULL, XML_TEXT text NULL) "
142
143  + " CREATE TABLE #CON_INS_TRAN(OLD_ID int, NEW_ID int NULL) "
144
145  + " CREATE TABLE #CON_VER_TRAN(OLD_ID int, NEW_ID int NULL) "
146
147  + " create table #CONTENT_NODE "
148  + " ( "
149  + " CONTENT_ID int not null, "
150  + " CONTENT_NODE_ID int not null, "
151  + " LEFT_NR int not null, "
152  + " RIGHT_NR int not null, "
153  + " NAME varchar(80) not null "
154  + " ) "
155
156
157  + " create table #CONTENT_VALUE "
158  + " ( "
159  + " CONTENT_ID int not null, "
160  + " CONTENT_NODE_ID int not null, "
161  + " IDX int not null, "
162  + " VALUE text not null, "
163  //zna
164 + " MEDIA_ID int null "
165  + " ) "
166
167
168  //zna
169 /*
170  + " create table #Content_Version "
171  + " ( "
172  + " VERSION_ID int not null, "
173  + " INSTANCE_ID int not null, "
174  + " CONTENT_ID int null , "
175  + " STATUS_ID int not null, "
176  + " VERSION_DATE datetime not null, "
177  + " INFO varchar(254) null , "
178  + " AUTHOR varchar(80) null "
179  + " ) "
180 */
181  //zna
182 + " create table #Content_Instance "
183  + " ( "
184  + " INSTANCE_ID int not null, "
185  + " CONTENT_NODE_ID int not null, "
186  + " NAME varchar(80) null "
187  + " ) "
188
189  //zna
190 + " create table #Content_Attribute_Value "
191  + " ( "
192  + " CONTENT_ID int not null, "
193  + " VALUE_ID int not null "
194  + " ) "
195
196
197     /* Relative references in DOCUMENT_REFERENCE, see 10.3) */
198  + " CREATE TABLE #REL_REFS (OLD_SRC INT, OLD_TAR INT, DOC_IDX INT, COMPONENT_IDX INT, DIST INT NULL, NEW_SRC INT, NEW_TAR INT NULL) "
199
200
201  + " BEGIN TRANSACTION "
202
203 /* INPUT VARIABLES */
204
205
206 /* Input Variables for CONTENT_TREE duplication */
207
208  + " DECLARE @TEMPLATE_ROOT int "
209  + " DECLARE @TARGET_ROOT int "
210  + " DECLARE @NEW_CONTENT_ROOT_NAME varchar(254) "
211  + " DECLARE @NEW_CONTENT_ROOT_SHORTNAME varchar(80) "
212
213 /* Input Variables for SITE_TREE duplication */
214
215  + " DECLARE @ST_SUBTREE_ROOT_ID int "
216  + " DECLARE @ST_TARGET_ROOT_ID int "
217  + " DECLARE @NEW_SITE_ROOT_NAME varchar(254) "
218  + " DECLARE @NEW_SITE_ROOT_SHORTNAME varchar(80) "
219
220
221 /* Input variables for flags */
222
223  + " DECLARE @SITE_TREE_FLAG int "
224  + " DECLARE @CONTENT_TREE_FLAG int "
225
226
227 // + " DECLARE @CONTENT_FLAG int "
228
229
230 /* Variables for creation of new IDs */
231
232
233  + " DECLARE @C_count int "
234  + " DECLARE @C_upper int "
235  + " DECLARE @C_i int "
236  + " DECLARE @C_j int "
237
238
239
240 /* Variables of IDs of new roots (for Resultsets) */
241
242
243  + " DECLARE @NEW_CONT_ROOT_ID int "
244  + " DECLARE @NEW_SITE_ROOT_ID int "
245
246
247
248
249
250 /* Root IDs and names of template (subtree) and target in the CONTENT_TREE*/
251
252  + " SELECT @TEMPLATE_ROOT = ? "
253  + " SELECT @TARGET_ROOT = ? "
254  + " SELECT @NEW_CONTENT_ROOT_NAME = ? "
255  + " SELECT @NEW_CONTENT_ROOT_SHORTNAME = ? "
256
257
258 /* Root IDs and names of subtree and target in the SITE_TREE*/
259
260
261
262
263 // + " IF (@SITE_TREE_FLAG = 1) "
264 // + " BEGIN "
265 + " SELECT @ST_SUBTREE_ROOT_ID = ? "
266  + " SELECT @ST_TARGET_ROOT_ID = ? "
267  + " SELECT @NEW_SITE_ROOT_NAME = ? "
268  + " SELECT @NEW_SITE_ROOT_SHORTNAME = ? "
269 // + " END "
270
271
272 /* Assign flags */
273
274  + " IF (@ST_SUBTREE_ROOT_ID != NULL AND @ST_TARGET_ROOT_ID != NULL ) "
275  + " SELECT @SITE_TREE_FLAG = 1 "
276  + " IF (@TEMPLATE_ROOT != NULL AND @TARGET_ROOT != NULL) "
277  + " SELECT @CONTENT_TREE_FLAG = 1 "
278 // + " SELECT @CONTENT_FLAG = ? "
279
280
281
282
283 /* 1) The followig batch copies a subtree of CONTENT_TREE into a table #tmp */
284 /* The CONTENT_NODE_ID of that subtree is in @TEMPLATE_ROOT_ID */
285 /* The values of L/R nr. are manipulatet to append the subtree below */
286 /* the node with CONTENT_NODE_ID @TARGET_ROOT_ID*/
287
288
289
290
291
292  + " DECLARE @TARGET_RIGHT int " /* RIGHT_NR of target node */
293  + " DECLARE @TEMPLATE_LEFT int "
294  + " DECLARE @TEMPLATE_RIGHT int "
295  + " DECLARE @NODE_COUNT int "
296  + " DECLARE @LOCAL_SHIFT int " /* This Number is added to all L/R numbers of the template tree */
297  + " DECLARE @GLOBAL_SHIFT int " /* This number is added to L/R numbers in CONTENT_TREE, bigger than @TARGET_RIGHT */
298
299
300
301 /* retrieve RIGHT_NR -LEFT_NR of template root */
302
303
304  + " SELECT @TEMPLATE_RIGHT = RIGHT_NR "
305  + " FROM CONTENT_TREE "
306  + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
307
308  + " SELECT @TEMPLATE_LEFT = LEFT_NR "
309  + " FROM CONTENT_TREE "
310  + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
311
312 /* Retrieve target node information :RIGHT_NR */
313
314  + " SELECT @TARGET_RIGHT = RIGHT_NR "
315  + " FROM CONTENT_TREE "
316  + " WHERE CONTENT_NODE_ID = @TARGET_ROOT "
317
318
319
320 /* Calculate the shift of the L/R numbers of duplicated records */
321
322  + " SELECT @LOCAL_SHIFT = @TARGET_RIGHT - @TEMPLATE_LEFT "
323
324
325 /* Write complete Vorlage Tree into #tmp */
326
327 //zna
328 + " insert into #tmp ( 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) "
329  + " select * FROM CONTENT_TREE "
330  + " WHERE LEFT_NR > = @TEMPLATE_LEFT AND RIGHT_NR <= @TEMPLATE_RIGHT "
331
332
333 /* Set the new names */
334
335  + " UPDATE #tmp "
336  + " SET CONTENT_NODE_NAME = @NEW_CONTENT_ROOT_NAME, CONTENT_NODE_SHORTNAME = @NEW_CONTENT_ROOT_SHORTNAME "
337  + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
338
339
340
341
342 /* Before re-insertion of the #tmp records into CONTENT_TREE their L/R Numbers are increased by @LOCAL_SHIFT */
343
344  + " UPDATE #tmp "
345  + " SET LEFT_NR = LEFT_NR + @LOCAL_SHIFT "
346
347  + " UPDATE #tmp "
348  + " SET RIGHT_NR = RIGHT_NR + @LOCAL_SHIFT "
349
350
351 /* Count records in #tmp to calculate @GLOBAL_SHIFT */
352
353
354  + " SELECT @GLOBAL_SHIFT = ((@TEMPLATE_RIGHT - @TEMPLATE_LEFT) + 1 ) "
355
356
357 /* Increment L/R Nrs beyond TARGET_RIGHT to "make place" for the new subtree */
358
359  + " UPDATE CONTENT_TREE "
360  + " SET RIGHT_NR = RIGHT_NR + @GLOBAL_SHIFT "
361  + " WHERE RIGHT_NR >= @TARGET_RIGHT "
362
363  + " UPDATE CONTENT_TREE "
364  + " SET LEFT_NR = LEFT_NR + @GLOBAL_SHIFT "
365  + " WHERE LEFT_NR > @TARGET_RIGHT "
366
367
368
369
370
371
372
373 /* 2) The following creates a table #TRANSLATE(OLD_ID int, NEW_ID int NULL) */
374 /* and fills into OLD_ID the values of #tmp.CONTENT_NODE_ID. It fills the column */
375 /* NEW_ID in #TRANSLATE with the values that can be used as new CONTENT_NODE_ID. */
376 /* The table CONTENT_TREE is scanned for unused IDs. */
377
378
379 /* Fill OLD_ID from #tmp */
380
381  + " INSERT INTO #TRANSLATE(OLD_ID) "
382  + " SELECT CONTENT_NODE_ID "
383  + " FROM #tmp "
384
385 /* Create NEW_ID entries */
386
387  + " DECLARE @count int "
388  + " DECLARE @upper int "
389  + " DECLARE @i int "
390  + " DECLARE @j int "
391
392  + " SELECT @i = 1 "
393
394  + " SELECT @j = 0 "
395
396
397  + " SELECT @count = COUNT(*) "
398  + " FROM #TRANSLATE "
399
400  + " WHILE (@j < @count) "
401  + " BEGIN "
402  + " IF NOT ( @i IN (SELECT CONTENT_NODE_ID "
403  + " FROM CONTENT_TREE "
404  + " ) "
405  + " ) "
406  + " BEGIN "
407  + " UPDATE #TRANSLATE "
408  + " SET NEW_ID = @i "
409  + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
410  + " FROM #TRANSLATE "
411  + " WHERE NEW_ID = NULL "
412  + " ) "
413  + " SELECT @j = @j + 1 "
414  + " END "
415  + " SELECT @i = @i +1 "
416  + " END "
417
418
419
420 /* 3) */
421 /* Code for insertion of the updated #tmp- records, with new IDs into CONTENT_TREE */
422 /* 3 Steps: */
423 /* FIRST!! The PARENT_NODE_ID of the Template_root is set to Target_root ID */
424 /* 3.2.The PARENT_NODE_ID of all the other records is updatet according to #TRANSLATE */
425 /* 3.3. CONTENT_NODE_ID is updated in #tmp according to #TRANSLATE, and #tmp copied into CONTENT_TREE */
426
427
428
429 /* 3.1) */
430
431  + " UPDATE #tmp "
432  + " SET CONTENT_NODE_PARENT = @TARGET_ROOT "
433  + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
434
435
436 /* 3.2) */
437
438
439  + " UPDATE #tmp "
440  + " SET CONTENT_NODE_PARENT = TL.NEW_ID "
441  + " FROM #TRANSLATE TL "
442  + " WHERE (#tmp.CONTENT_NODE_PARENT = TL.OLD_ID "
443  + " AND "
444  + " #tmp.CONTENT_NODE_ID != @TEMPLATE_ROOT "
445  + " ) "
446
447 /* 3.3) */
448
449
450  + " UPDATE #tmp "
451  + " SET CONTENT_NODE_ID = TL.NEW_ID "
452  + " FROM #TRANSLATE TL "
453  + " WHERE #tmp.CONTENT_NODE_ID = TL.OLD_ID "
454
455
456 //zna
457 + " 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) "
458  + " SELECT 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 "
459  + " FROM #tmp "
460
461
462
463 //marwan 9.2.
464
465 /* Clear the temporary tables for reuse! */
466
467  + " DELETE FROM #CONTENT_TRANSLATE "
468
469  + " DELETE FROM #CON_INS_TRAN "
470
471  + " DELETE FROM #CON_VER_TRAN "
472
473  + " DELETE FROM #CONTENT_NODE "
474
475  + " DELETE FROM #CONTENT_VALUE "
476
477
478 /* CONTENT */
479 /*
480  + " SELECT DISTINCT @i = C.CONTENT_ID "
481  + " FROM CONTENT C, CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
482  + " WHERE "
483  + " C.CONTENT_ID = C_V.CONTENT_ID "
484  + " AND "
485  + " C_V.INSTANCE_ID = C_I.INSTANCE_ID "
486  + " AND "
487  + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID"
488
489  + " INSERT INTO #CONTENT_TRANSLATE (OLD_ID, XML_TEXT) "
490  + " SELECT CONTENT_ID, XML_TEXT "
491  + " FROM CONTENT "
492  + " WHERE CONTENT_ID = @i "
493   */
494
495  + " INSERT INTO #CONTENT_TRANSLATE (OLD_ID) "
496  + " SELECT DISTINCT C.CONTENT_ID "
497  + " FROM CONTENT C,CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
498  + " WHERE "
499  + " C.CONTENT_ID = C_V.CONTENT_ID "
500  + " AND "
501  + " C_V.INSTANCE_ID = C_I.INSTANCE_ID "
502  + " AND "
503  + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
504
505
506
507  + " SELECT @i = 1 "
508  + " SELECT @j = 0 "
509
510
511  + " SELECT @count = COUNT(*) "
512  + " FROM #CONTENT_TRANSLATE "
513
514  + " WHILE (@j < @count) "
515  + " BEGIN "
516  + " IF NOT ( @i IN (SELECT CONTENT_ID "
517  + " FROM CONTENT "
518  + " ) "
519  + " ) "
520  + " BEGIN "
521  + " UPDATE #CONTENT_TRANSLATE "
522  + " SET NEW_ID = @i "
523  + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
524  + " FROM #CONTENT_TRANSLATE "
525  + " WHERE NEW_ID = NULL "
526  + " ) "
527  + " SELECT @j = @j + 1 "
528  + " END "
529  + " SELECT @i = @i +1 "
530  + " END "
531
532  + " INSERT INTO CONTENT "
533  + " SELECT NEW_ID, XML_TEXT FROM #CONTENT_TRANSLATE "
534
535
536
537
538 /* CONTENT_INSTANCE */
539
540  + " INSERT INTO #CON_INS_TRAN (OLD_ID) "
541  + " SELECT INSTANCE_ID "
542  + " FROM CONTENT_INSTANCE, #TRANSLATE "
543  + " WHERE CONTENT_INSTANCE.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
544
545
546  + " SELECT @i = 1 "
547
548  + " SELECT @j = 0 "
549
550
551  + " SELECT @count = COUNT(*) "
552  + " FROM #CON_INS_TRAN "
553
554  + " WHILE (@j < @count) "
555  + " BEGIN "
556  + " IF NOT ( @i IN (SELECT INSTANCE_ID "
557  + " FROM CONTENT_INSTANCE "
558  + " ) "
559  + " ) "
560  + " BEGIN "
561  + " UPDATE #CON_INS_TRAN "
562  + " SET NEW_ID = @i "
563  + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
564  + " FROM #CON_INS_TRAN "
565  + " WHERE NEW_ID = NULL "
566  + " ) "
567  + " SELECT @j = @j + 1 "
568  + " END "
569  + " SELECT @i = @i +1 "
570  + " END "
571
572
573  + " INSERT INTO CONTENT_INSTANCE (INSTANCE_ID, CONTENT_NODE_ID, NAME) "
574  + " SELECT #CON_INS_TRAN.NEW_ID, #TRANSLATE.NEW_ID, NAME "
575  + " FROM CONTENT_INSTANCE, #CON_INS_TRAN, #TRANSLATE "
576  + " WHERE "
577  + " CONTENT_INSTANCE.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
578  + " AND "
579  + " CONTENT_INSTANCE.INSTANCE_ID = #CON_INS_TRAN.OLD_ID "
580
581
582
583 /* CONTENT_VERSION */
584
585  + " INSERT INTO #CON_VER_TRAN (OLD_ID) "
586  + " SELECT C_V.VERSION_ID "
587  + " FROM CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
588  + " WHERE C_V.INSTANCE_ID = C_I.INSTANCE_ID "
589  + " AND "
590  + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
591
592
593
594  + " SELECT @i = 1 "
595
596  + " SELECT @j = 0 "
597
598
599  + " SELECT @count = COUNT(*) "
600  + " FROM #CON_VER_TRAN "
601
602  + " WHILE (@j < @count) "
603  + " BEGIN "
604  + " IF NOT ( @i IN (SELECT VERSION_ID "
605  + " FROM CONTENT_VERSION "
606  + " ) "
607  + " ) "
608  + " BEGIN "
609  + " UPDATE #CON_VER_TRAN "
610  + " SET NEW_ID = @i "
611  + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
612  + " FROM #CON_VER_TRAN "
613  + " WHERE NEW_ID = NULL "
614  + " ) "
615  + " SELECT @j = @j + 1 "
616  + " END "
617  + " SELECT @i = @i +1 "
618  + " END "
619
620
621
622  + " INSERT INTO CONTENT_VERSION (VERSION_ID, INSTANCE_ID, CONTENT_ID, STATUS_ID, VERSION_DATE, INFO, AUTHOR) "
623  + " SELECT #CON_VER_TRAN.NEW_ID, #CON_INS_TRAN.NEW_ID, #CONTENT_TRANSLATE.NEW_ID, C_V.STATUS_ID, C_V.VERSION_DATE, C_V.INFO, C_V.AUTHOR "
624  + " FROM CONTENT_VERSION C_V, #CON_VER_TRAN, #CON_INS_TRAN, #CONTENT_TRANSLATE "
625  + " WHERE "
626  + " C_V.VERSION_ID = #CON_VER_TRAN.OLD_ID "
627  + " AND "
628  + " C_V.INSTANCE_ID = #CON_INS_TRAN.OLD_ID "
629  + " AND "
630  + " C_V.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID "
631
632
633
634 /* CONTENT_NODE duplication */
635
636
637  + " INSERT INTO CONTENT_NODE "
638  + " SELECT C_T.NEW_ID, C_N.CONTENT_NODE_ID, C_N.LEFT_NR, C_N.RIGHT_NR, C_N.NAME "
639  + " FROM CONTENT_NODE C_N, #CONTENT_TRANSLATE C_T "
640  + " WHERE C_N.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
641
642
643
644
645
646 /* CONTENT_VALUE duplication */
647
648  + " INSERT INTO #CONTENT_VALUE "
649  + " SELECT C_T.NEW_ID, C_V.CONTENT_NODE_ID, C_V.IDX, C_V.VALUE, C_V.MEDIA_ID "
650  + " FROM CONTENT_VALUE C_V, #CONTENT_TRANSLATE C_T "
651  + " WHERE C_V.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
652
653
654
655
656  + " INSERT INTO CONTENT_VALUE "
657  + " SELECT * FROM #CONTENT_VALUE "
658
659
660
661
662 /* SITE TREE //////////////////////////////////////////////////////////////// */
663
664  + " IF (@SITE_TREE_FLAG = 1) "
665  + " BEGIN "
666 /* 4) - 6) Dupliziert einen Teilbaum des SITE_TREE. SUBTREEE_ROOT_ID bezeichnet die ID */
667 /* des Wurzelknotens des Teilbaums, TARGET_ROOT_ID die ID desjenigen Knotens */
668 /* als dessen rechtestes Kind die Kopie eingefŸgt wird. */
669
670
671
672
673 /* 4) The followig batch copies a subtree of SITE_TREE into a table #tmp */
674 /* The SITE_NODE_ID of that subtree is in @ST_SUBTREE_ROOT_ID */
675 /* The values of L/R nr. are manipulatet to append the subtree below */
676 /* the node with SITE_NODE_ID @ST_TARGET_ROOT_ID */
677
678
679
680
681  + " DECLARE @ST_TARGET_RIGHT int " /* RIGHT_NR of target node */
682  + " DECLARE @ST_SUBTREE_LEFT int "
683  + " DECLARE @ST_SUBTREE_RIGHT int "
684  + " DECLARE @ST_NODE_COUNT int "
685  + " DECLARE @ST_LOCAL_SHIFT int " /* This Number is added to all L/R numbers of the SUBTREE tree */
686  + " DECLARE @ST_GLOBAL_SHIFT int " /* This number is added to L/R numbers in SITE_TREE, bigger than @ST_TARGET_RIGHT */
687
688
689
690
691 /* retrieve RIGHT_NR -LEFT_NR of SUBTREE root */
692
693  + " SELECT @ST_SUBTREE_RIGHT = RIGHT_NR "
694  + " FROM SITE_TREE "
695  + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
696
697  + " SELECT @ST_SUBTREE_LEFT = LEFT_NR "
698  + " FROM SITE_TREE "
699  + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
700
701 /* Retrieve target node information :RIGHT_NR */
702
703  + " SELECT @ST_TARGET_RIGHT = RIGHT_NR "
704  + " FROM SITE_TREE "
705  + " WHERE SITE_NODE_ID = @ST_TARGET_ROOT_ID "
706
707
708
709 /* Calculate the shift of the L/R numbers of duplicated records */
710
711  + " SELECT @ST_LOCAL_SHIFT = @ST_TARGET_RIGHT - @ST_SUBTREE_LEFT "
712
713
714 /* Write complete subtree into #ST_tmp */
715
716
717  + " INSERT INTO #ST_tmp ( SITE_NODE_ID, SITE_NODE_PARENT, SITE_NODE_TYPE, SITE_NODE_NAME, SITE_NODE_SHORTNAME, LEFT_NR, RIGHT_NR, PG_CONTENT_NODE, PG_SELECTION_ID ) "
718  + " SELECT SITE_NODE_ID, SITE_NODE_PARENT, SITE_NODE_TYPE, SITE_NODE_NAME, SITE_NODE_SHORTNAME, LEFT_NR, RIGHT_NR, PG_CONTENT_NODE, PG_SELECTION_ID "
719  + " FROM SITE_TREE "
720  + " WHERE LEFT_NR > = @ST_SUBTREE_LEFT AND RIGHT_NR <= @ST_SUBTREE_RIGHT "
721
722
723
724
725 /* Set the new names */
726
727  + " UPDATE #ST_tmp "
728  + " SET SITE_NODE_NAME = @NEW_SITE_ROOT_NAME, SITE_NODE_SHORTNAME = @NEW_SITE_ROOT_SHORTNAME "
729  + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
730
731
732
733
734
735
736 /* Before re-insertion of the #tmp records into SITE_TREE their L/R Numbers are increased by @ST_LOCAL_SHIFT */
737
738  + " UPDATE #ST_tmp "
739  + " SET LEFT_NR = LEFT_NR + @ST_LOCAL_SHIFT "
740
741  + " UPDATE #ST_tmp "
742  + " SET RIGHT_NR = RIGHT_NR + @ST_LOCAL_SHIFT "
743
744
745 /* calculate @ST_GLOBAL_SHIFT */
746
747
748  + " SELECT @ST_GLOBAL_SHIFT = ((@ST_SUBTREE_RIGHT - @ST_SUBTREE_LEFT) +1 ) "
749
750
751 /* Increment L/R Nrs beyond TARGET_RIGHT to "make place" for the new subtree */
752
753  + " UPDATE SITE_TREE "
754  + " SET RIGHT_NR = RIGHT_NR + @ST_GLOBAL_SHIFT "
755  + " WHERE RIGHT_NR >= @ST_TARGET_RIGHT "
756
757  + " UPDATE SITE_TREE "
758  + " SET LEFT_NR = LEFT_NR + @ST_GLOBAL_SHIFT "
759  + " WHERE LEFT_NR > @ST_TARGET_RIGHT "
760
761
762
763
764
765
766
767 /* 5) The following creates a table #ST_TRANSLATE(OLD_ID int, NEW_ID int NULL) */
768 /* and fills into OLD_ID the values of #tmp.SITE_NODE_ID. It fills the column */
769 /* NEW_ID in #ST_TRANSLATE with the values that can be used as new SITE_NODE_ID. */
770 /* The table SITE_TREE is scanned for unused IDs. */
771
772
773 /* Fill OLD_ID from #tmp */
774
775  + " INSERT INTO #ST_TRANSLATE(OLD_ID) "
776  + " SELECT SITE_NODE_ID "
777  + " FROM #ST_tmp "
778
779 /* Create NEW_ID entries */
780
781  + " DECLARE @ST_count int "
782  + " DECLARE @ST_upper int "
783  + " DECLARE @ST_i int "
784  + " DECLARE @ST_j int "
785
786  + " SELECT @ST_i = 1 "
787
788  + " SELECT @ST_j = 0 "
789
790
791  + " SELECT @ST_count = COUNT(*) "
792  + " FROM #ST_TRANSLATE "
793
794  + " WHILE (@ST_j < @ST_count) "
795  + " BEGIN "
796  + " IF NOT ( @ST_i IN (SELECT SITE_NODE_ID "
797  + " FROM SITE_TREE "
798  + " ) "
799  + " ) "
800  + " BEGIN "
801  + " UPDATE #ST_TRANSLATE "
802  + " SET NEW_ID = @ST_i "
803  + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
804  + " FROM #ST_TRANSLATE "
805  + " WHERE NEW_ID = NULL "
806  + " ) "
807  + " SELECT @ST_j = @ST_j + 1 "
808  + " END "
809  + " SELECT @ST_i = @ST_i +1 "
810  + " END "
811
812
813
814 /* 6) */
815 /* Code for insertion of the updated #tmp- records, with new IDs into SITE_TREE */
816 /* 3 Steps: */
817 /* 6.1 FIRST!! The PARENT_NODE_ID of the SUBTREE_ROOT is set to Target_root ID */
818 /* 6.2.The PARENT_NODE_ID of all the other records is updatet according to #ST_TRANSLATE */
819 /* 6.3. SITE_NODE_ID is updated in #tmp2 according to #ST_TRANSLATE, and #tmp2 copied into SITE_TREE */
820
821
822
823 /* 6.1) */
824
825  + " UPDATE #ST_tmp "
826  + " SET SITE_NODE_PARENT = @ST_TARGET_ROOT_ID "
827  + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
828
829
830 /* 6.2) */
831
832
833  + " UPDATE #ST_tmp "
834  + " SET SITE_NODE_PARENT = TL.NEW_ID "
835  + " FROM #ST_TRANSLATE TL "
836  + " WHERE (#ST_tmp.SITE_NODE_PARENT = TL.OLD_ID "
837  + " AND "
838  + " SITE_NODE_ID != @ST_SUBTREE_ROOT_ID "
839  + " ) "
840
841 /* 6.3) */
842
843
844  + " UPDATE #ST_tmp "
845  + " SET SITE_NODE_ID = TL.NEW_ID "
846  + " FROM #ST_TRANSLATE TL "
847  + " WHERE #ST_tmp.SITE_NODE_ID = TL.OLD_ID "
848
849
850
851  + " INSERT INTO SITE_TREE ( SITE_NODE_ID, SITE_NODE_PARENT, SITE_NODE_TYPE, SITE_NODE_NAME, SITE_NODE_SHORTNAME, LEFT_NR, RIGHT_NR, PG_CONTENT_NODE, PG_SELECTION_ID ) "
852  + " SELECT SITE_NODE_ID, SITE_NODE_PARENT, SITE_NODE_TYPE, SITE_NODE_NAME, SITE_NODE_SHORTNAME, LEFT_NR, RIGHT_NR, PG_CONTENT_NODE, PG_SELECTION_ID "
853  + " FROM #ST_tmp "
854
855
856
857
858
859
860
861
862 /* 7) SITE_TREE FremdschlŸssel update: PG_CONTENT_NODE wird bei denjenigen Records verŠndert, */
863 /* die im Site tree dupliziert worden sind und deren PG_CONTENT_NODE auf ein CONTENT_TREE */
864 /* Record verweist, welches beim CONTENT_TREE dupliziert worden ist. */
865
866
867  + " UPDATE SITE_TREE "
868  + " SET ST.PG_CONTENT_NODE = CT_T.NEW_ID "
869  + " FROM SITE_TREE ST, #TRANSLATE CT_T "
870  + " WHERE ST.SITE_NODE_ID IN (SELECT NEW_ID " /* only duplicted SITE_TREE nodes are considered */
871  + " FROM #ST_TRANSLATE "
872  + " ) "
873  + " AND "
874  + " ST.PG_CONTENT_NODE = CT_T.OLD_ID " /* join-condition */
875
876
877 /* 8) SITE_DOCUMENT primary key insert, according to SITE_TREE duplication */
878
879 //zna
880 + " INSERT INTO SITE_DOCUMENT (SITE_NODE_ID, SITE_NODE_DOC_IDX, PRESENTATION_ID, DOCUMENT_NAME, DOCUMENT_SHORTNAME, INHERITABLE, DOCUMENT_TYPE, EXTERNAL_URL, INHERIT_BEGIN_LEVEL, INHERIT_END_LEVEL) "
881  + " SELECT ST_T.NEW_ID, SD.SITE_NODE_DOC_IDX, SD.PRESENTATION_ID, SD.DOCUMENT_NAME, SD.DOCUMENT_SHORTNAME, SD.INHERITABLE, SD.DOCUMENT_TYPE, SD.EXTERNAL_URL, SD.INHERIT_BEGIN_LEVEL, SD.INHERIT_END_LEVEL "
882  + " FROM #ST_TRANSLATE ST_T, SITE_DOCUMENT SD "
883  + " WHERE SD.SITE_NODE_ID = ST_T.OLD_ID "
884
885
886
887 /* 9) DOCUMENT_CONTENT duplication */
888
889 /* 9.1) Primary-Key: Insertion of records, whose SITE_NODE_ID points to a duplicated SITE_TREE node. */
890
891 //zna
892 + " INSERT INTO DOCUMENT_CONTENT (SITE_NODE_ID, SITE_NODE_DOC_IDX, PRESENTATION_COMPONENT_IDX, CONTENT_NODE_ID, PROTOTYPE_NODE_ID, SELECTION_ID) "
893  + " SELECT ST_T.NEW_ID, DC.SITE_NODE_DOC_IDX, DC.PRESENTATION_COMPONENT_IDX, DC.CONTENT_NODE_ID, DC.PROTOTYPE_NODE_ID, DC.SELECTION_ID "
894  + " FROM #ST_TRANSLATE ST_T, DOCUMENT_CONTENT DC "
895  + " WHERE DC.SITE_NODE_ID = ST_T.OLD_ID "
896
897
898 /* 9.2) Foreign-Key: update of CONTENT_NODE_ID in those records, which were duplicated in 9.1) */
899 /* and whose CONTENT_NODE_ID points to a duplicated CONTENT_TREE record. */
900
901  + " UPDATE DOCUMENT_CONTENT "
902  + " SET DC.CONTENT_NODE_ID = CT_T.NEW_ID "
903  + " FROM #TRANSLATE CT_T, DOCUMENT_CONTENT DC "
904  + " WHERE DC.SITE_NODE_ID IN (SELECT NEW_ID "
905  + " FROM #ST_TRANSLATE "
906  + " ) "
907  + " AND "
908  + " DC.CONTENT_NODE_ID = CT_T.OLD_ID "
909
910
911
912 /* 10) DOCUMENT_REFERENCE */
913
914 /* 10.1) Primary-Key Insert: Records whose SRC_SITE_NODE_ID points to a duplicated SITE_TREE record. */
915
916  + " INSERT INTO DOCUMENT_REFERENCE(SRC_SITE_NODE_ID, SRC_SITE_NODE_DOC_IDX, SRC_PRESENTATION_COMPONENT_IDX, REFERENCE_TYPE, DEST_SITE_NODE_ID, DEST_SITE_NODE_DOC_SHORTNAME, SELECTION_TYPE, SELECTION_DATA) "
917  + " SELECT ST_T.NEW_ID, DR.SRC_SITE_NODE_DOC_IDX, DR.SRC_PRESENTATION_COMPONENT_IDX, DR.REFERENCE_TYPE, DR.DEST_SITE_NODE_ID, DR.DEST_SITE_NODE_DOC_SHORTNAME, DR.SELECTION_TYPE, DR.SELECTION_DATA "
918  + " FROM #ST_TRANSLATE ST_T, DOCUMENT_REFERENCE DR "
919  + " WHERE DR.SRC_SITE_NODE_ID = ST_T.OLD_ID "
920
921
922 /* 10.2) Foreign-Key: Update of those records which were inserted in 10.1 and whose DST_SITE_NODE_ID */
923 /* points to a duplicated SITE_TREE node. */
924
925  + " UPDATE DOCUMENT_REFERENCE "
926  + " SET DR.DEST_SITE_NODE_ID = ST_T.NEW_ID "
927  + " FROM DOCUMENT_REFERENCE DR, #ST_TRANSLATE ST_T "
928  + " WHERE DR.DEST_SITE_NODE_ID = ST_T.OLD_ID "
929  + " AND "
930  + " DR.SRC_SITE_NODE_ID IN (SELECT NEW_ID "
931  + " FROM #ST_TRANSLATE "
932  + " ) "
933
934 /* 10.3) Relative references: */
935
936
937  + " INSERT INTO #REL_REFS (OLD_SRC, OLD_TAR, DOC_IDX, COMPONENT_IDX, NEW_SRC) "
938  + " SELECT SRC_SITE_NODE_ID, DEST_SITE_NODE_ID, SRC_SITE_NODE_DOC_IDX, SRC_PRESENTATION_COMPONENT_IDX, NEW_ID "
939  + " FROM DOCUMENT_REFERENCE, #ST_TRANSLATE "
940  + " WHERE REFERENCE_TYPE = " + REFERENCE_RELATIVE
941  + " AND "
942  + " SRC_SITE_NODE_ID = OLD_ID "
943
944 /* Calculate the "distance" */
945  + " UPDATE #REL_REFS "
946  + " SET DIST = "
947  + " ( "
948  + " SELECT COUNT(*) "
949  + " FROM SITE_TREE "
950  + " WHERE "
951  + " LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_SRC) "
952  + " AND "
953  + " LEFT_NR > (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_TAR) "
954  + " AND "
955  + " RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_SRC) "
956  + " AND "
957  + " RIGHT_NR < (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_TAR) "
958  + " ) "
959
960 /** Avoid references inside the same site_node*/
961 + " UPDATE DOCUMENT_REFERENCE "
962 + " SET DEST_SITE_NODE_ID = NEW_SRC "
963 + " FROM #REL_REFS "
964 + " WHERE "
965 + " NEW_SRC = DOCUMENT_REFERENCE.SRC_SITE_NODE_ID "
966 + " AND "
967 + " DOC_IDX = DOCUMENT_REFERENCE.SRC_SITE_NODE_DOC_IDX "
968 + " AND "
969 + " COMPONENT_IDX = DOCUMENT_REFERENCE.SRC_PRESENTATION_COMPONENT_IDX "
970 + " AND"
971 + " DIST=0"
972
973 + " DELETE FROM #REL_REFS WHERE DIST=0"
974
975 /* Find the new target, based on the distance */
976
977  + " UPDATE #REL_REFS "
978  + " SET NEW_TAR = "
979  + " ( "
980  + " SELECT SITE_NODE_ID "
981  + " FROM SITE_TREE OUTER "
982  + " WHERE "
983  + " #REL_REFS.DIST = "
984  + " ( "
985  + " SELECT COUNT(*) "
986  + " FROM SITE_TREE INNER "
987  + " WHERE "
988  + " INNER.LEFT_NR > OUTER.LEFT_NR "
989  + " AND "
990  + " INNER.RIGHT_NR < OUTER.RIGHT_NR "
991  + " AND "
992  + " INNER.LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.NEW_SRC) "
993  + " AND "
994  + " INNER.RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.NEW_SRC) "
995  + " ) "
996
997  + " ) "
998  + " WHERE DIST != 0 "
999
1000
1001 + " UPDATE DOCUMENT_REFERENCE "
1002 + " SET DEST_SITE_NODE_ID = NEW_TAR "
1003 + " FROM #REL_REFS "
1004 + " WHERE "
1005 + " NEW_SRC = DOCUMENT_REFERENCE.SRC_SITE_NODE_ID "
1006 + " AND "
1007 + " DOC_IDX = DOCUMENT_REFERENCE.SRC_SITE_NODE_DOC_IDX "
1008 + " AND "
1009 + " COMPONENT_IDX = DOCUMENT_REFERENCE.SRC_PRESENTATION_COMPONENT_IDX "
1010 + " AND "
1011 + " DIST != 0 "
1012
1013
1014
1015
1016/* 11) STRUCTURED_CONTENT Primary-Key Insert: records whose SITE_NODE_ID references a duplicated SITE_TREE record, are duplicated and updated. */
1017
1018
1019 + " INSERT INTO STRUCTURED_CONTENT(SITE_NODE_ID, FORM_ID, CONTENT_ID) "
1020 + " SELECT ST_T.NEW_ID, S_C.FORM_ID, S_C.CONTENT_ID "
1021 + " FROM #ST_TRANSLATE ST_T, STRUCTURED_CONTENT S_C "
1022 + " WHERE ST_T.OLD_ID = S_C.SITE_NODE_ID "
1023
1024
1025
1026/* 12) CONTENT duplication, according to duplication of STRUCTURED_CONTENT */
1027
1028
1029/* Clear the temporary tables for reuse! */
1030
1031 + " DELETE FROM #CONTENT "
1032
1033 + " DELETE FROM #CONTENT_TRANSLATE "
1034
1035 + " DELETE FROM #CONTENT_NODE "
1036
1037 + " DELETE FROM #CONTENT_VALUE "
1038
1039
1040
1041/* Initialize the #CONTENT_TRANSLATE */
1042
1043 + " INSERT INTO #CONTENT_TRANSLATE "
1044 + " SELECT STRUCTURED_CONTENT.CONTENT_ID, NULL, C.XML_TEXT "
1045 + " FROM STRUCTURED_CONTENT, #ST_TRANSLATE, CONTENT C "
1046 + " WHERE STRUCTURED_CONTENT.SITE_NODE_ID = #ST_TRANSLATE.OLD_ID "
1047 + " AND C.CONTENT_ID = STRUCTURED_CONTENT.CONTENT_ID "
1048
1049
1050
1051
1052/* Create NEW_ID entries in #CONTENT_TRANSLATE */
1053
1054 + " SELECT @C_i = 1 "
1055
1056 + " SELECT @C_j = 0 "
1057
1058
1059 + " SELECT @C_count = COUNT(*) "
1060 + " FROM #CONTENT_TRANSLATE "
1061
1062 + " WHILE (@C_j < @C_count) "
1063 + " BEGIN "
1064 + " IF NOT ( @C_i IN (SELECT CONTENT_ID "
1065 + " FROM CONTENT "
1066 + " ) "
1067 + " ) "
1068 + " BEGIN "
1069 + " UPDATE #CONTENT_TRANSLATE "
1070 + " SET NEW_ID = @C_i "
1071 + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
1072 + " FROM #CONTENT_TRANSLATE "
1073 + " WHERE NEW_ID = NULL "
1074 + " ) "
1075 + " SELECT @C_j = @C_j + 1 "
1076 + " END "
1077 + " SELECT @C_i = @C_i +1 "
1078 + " END "
1079
1080
1081
1082/* Duplicate CONTENT records */
1083
1084//zna MUST BE COMMENTED!!!!!!!
1085/* + " INSERT INTO #CONTENT (CONTENT_ID, CONTENT_NODE_ID, CONTENT_NAME, CONTENT_SHORTNAME, ORDER_IDX ) "
1086 + " SELECT #CONTENT_TRANSLATE.NEW_ID, C.CONTENT_NODE_ID, C.CONTENT_NAME, C.CONTENT_SHORTNAME, C.ORDER_IDX "
1087 + " FROM CONTENT C, #CONTENT_TRANSLATE "
1088 + " WHERE C.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID " */
1089
1090//zna
1091 + " INSERT INTO #CONTENT (CONTENT_ID, XML_TEXT ) "
1092 + " SELECT #CONTENT_TRANSLATE.NEW_ID, #CONTENT_TRANSLATE.XML_TEXT "
1093 + " FROM CONTENT C, #CONTENT_TRANSLATE "
1094 + " WHERE C.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID "
1095
1096
1097 + " INSERT INTO CONTENT "
1098 + " SELECT * FROM #CONTENT "
1099
1100 //zna - Duplicate Content_Attribute_Value, Content_Version & Content_Instance
1101 /*+ "INSERT INTO CONTENT_ATTRIBUTE_VALUE (CONTENT_ID, VALUE_ID) "
1102 + "SELECT ct.NEW_ID, cav.VALUE_ID "
1103 + "FROM Content_Attribute_Value cav, #CONTENT_TRANSLATE ct "
1104 + "where cav.content_id = ct.old_id "
1105
1106*/
1107/* Update the CONTENT_ID in STRUCTURED_CONTENT */
1108
1109 + " UPDATE STRUCTURED_CONTENT "
1110 + " SET STRUCTURED_CONTENT.CONTENT_ID = #CONTENT_TRANSLATE.NEW_ID "
1111 + " FROM #CONTENT_TRANSLATE, #ST_TRANSLATE "
1112 + " WHERE STRUCTURED_CONTENT.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID " /* Find the new CONTENT_ID */
1113 + " AND "
1114 + " STRUCTURED_CONTENT.SITE_NODE_ID = #ST_TRANSLATE.NEW_ID " /* Select the duplicated STRUCTURED_CONTENT records */
1115
1116
1117
1118/* Duplicate CONTENT_NODE records */
1119
1120 + " INSERT INTO #CONTENT_NODE "
1121 + " SELECT C_T.NEW_ID, C_N.CONTENT_NODE_ID, C_N.LEFT_NR, C_N.RIGHT_NR, C_N.NAME "
1122 + " FROM CONTENT_NODE C_N, #CONTENT_TRANSLATE C_T "
1123 + " WHERE C_N.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
1124
1125
1126 + " INSERT INTO CONTENT_NODE "
1127 + " SELECT * FROM #CONTENT_NODE "
1128
1129
1130/* CONTENT_VALUE duplication */
1131
1132// zna
1133 + " INSERT INTO #CONTENT_VALUE "
1134 + " SELECT C_T.NEW_ID, C_V.CONTENT_NODE_ID, C_V.IDX, C_V.VALUE, C_V.MEDIA_ID "
1135 + " FROM CONTENT_VALUE C_V, #CONTENT_TRANSLATE C_T "
1136 + " WHERE C_V.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
1137
1138
1139
1140 + " INSERT INTO CONTENT_VALUE "
1141 + " SELECT * FROM #CONTENT_VALUE "
1142
1143
1144
1145 + " END " /* IF (@SITE_TREE_FLAG =1) (11 / 12) */
1146
1147
1148
1149
1150/* Root IDs of created subtrees for return */
1151 + " SELECT @NEW_CONT_ROOT_ID = NEW_ID "
1152 + " FROM #TRANSLATE "
1153 + " WHERE OLD_ID = @TEMPLATE_ROOT "
1154
1155
1156 + " SELECT @NEW_SITE_ROOT_ID = NEW_ID "
1157 + " FROM #ST_TRANSLATE "
1158 + " WHERE OLD_ID = @ST_SUBTREE_ROOT_ID "
1159
1160 /* Resultsets */
1161
1162 + " SELECT @NEW_CONT_ROOT_ID AS NEW_CONT_ROOT_ID, @NEW_SITE_ROOT_ID AS NEW_SITE_ROOT_ID "
1163
1164
1165
1166/* Commit the whole batch! */
1167
1168 + " COMMIT "
1169
1170
1171
1172/* Cleaning up temporary tables */
1173
1174/* Clean SITE_TREE garbage */
1175
1176 + " drop table #ST_tmp "
1177 + " drop table #ST_TRANSLATE "
1178
1179
1180/* Clean CONTENT_TREE garbage */
1181
1182 + " drop table #tmp "
1183 + " drop table #TRANSLATE "
1184
1185
1186/* Clean CONTENT garbage */
1187 + " drop table #CONTENT "
1188 + " drop table #CONTENT_TRANSLATE "
1189 + " drop table #CON_INS_TRAN "
1190 + " drop table #CON_VER_TRAN "
1191 + " drop table #CONTENT_NODE "
1192 + " drop table #CONTENT_VALUE "
1193 + " drop table #Content_Instance "
1194 + " drop table #Content_Attribute_Value"
1195
1196/* DOCUMENT_REFERENCES */
1197 + " DROP TABLE #REL_REFS "
1198 ;
1199
1200
1201        public void initQuery(Connection con) {
1202        super.initQuery(
1203            con,
1204            isPrepared,
1205            paramOrder,
1206            paramTypes,
1207            setRelevants,
1208            sqlString );
1209    }
1210}
1211
1212
Popular Tags