0001: package com.teamkonzept.webman.mainint.db.queries.duplication;
0002:
0003: import com.teamkonzept.db.*;
0004: import com.teamkonzept.webman.mainint.DatabaseDefaults;
0005:
0006: import java.io.*;
0007: import java.util.*;
0008: import java.sql.*;
0009:
0010: /* Version 07, mit der Mšglichkeit CONTENT CONTENT_NODE und CONTENT_VALUE mitzuduplizieren. */
0011: /* Version 06, Schalter fŸr Site Tree Duplizierung eingebaut */
0012: /* Version 05, @GLOBAL_SHIFT berechnung geŠndert ( +1 ) an ZWEI Stellen!! */
0013:
0014: /* Mit den SQL - Statements in diesem Batch werden TeilbŠume von CONTENT_TREE und SITE_TREE */
0015: /* dupliziert, und die Referenzen unter diesen bleiben erhalten. */
0016:
0017: /* Im einzelnen passiert folgendes: */
0018:
0019: /* a) ( 1 - 3 ) Ein Teilbaum A von CONTENT_TREE, mit Wurzel @TEMPLATE_ROOT_ID wird zu A' dupliziert */
0020: /* und an den Knoten @TARGET_ROOT_ID angehŠngt. */
0021:
0022: /* b) ( 4 - 6 ) Ein Teilbaum B von SITE_TREE wird zu B' dupliziert.*/
0023: /* 4 - 11 lŠsst sich mit @SITE_FLAG ausschalten. */
0024:
0025: /* c) ( 7 ) Wenn das Attribut PG_CONTENT_NODE_ID in B' auf A verweist, wird es auf den */
0026: /* entsprechenden Knoten in A' umgehŠngt.*/
0027:
0028: /* d) ( 8 ) Alle SITE_DOCUMENT records, deren SITE_NODE_ID auf B verweist, werden dupliziert */
0029: /* und die duplizierten werden auf B' umgehŠngt. */
0030:
0031: /* e) ( 9 ) In DOCUMENT_CONTENT werden alle Records, deren SITE_NODE_ID auf B zeigt dupliziert, */
0032: /* und die Duplikate werden auf B' umgehŠngt. */
0033: /* Die CONTENT_NODE_IDs der duplizierten Records werden, wenn sie auf A zeigen, auf A' */
0034: /* umgehŠngt. */
0035:
0036: /* f) ( 10 ) Alle DOCUMENT_REFERENCE Records deren SRC_SITE_NODE_ID auf B zeigt werden dupliziert,*/
0037: /* die Duplikate werden umgehŠngt. */
0038: /* Diejenigen DST_SITE_NODE_ID Werte in duplizierten Records, die auf A zeigen, werden auf */
0039: /* A' umgehŠngt.*/
0040:
0041: /* g) ( 11 )STRUCTURED_CONTENT Records deren SITE_NODE_ID auf B zeigt werden dupliziert, */
0042: /* die Duplikate auf B' umgehŠngt. */
0043: /* h) (12) Diejenigen CONTENT Records, die von einem duplizierten STRUCTURED_CONTENT Record referenziert werden, */
0044: /* werden ihrerseits dupliziert. Auch der darean hŠngende Baum aus CONTENT_NODE und CONTENT_VALUE records. */
0045:
0046: /* INPUT: TEMPLATE_ROOT_ID (Wurzelknoten ID des zu duplizierenden CONTENT_TREE Teilbaumes ) */
0047: /* TARGET_ROOT_ID (An diesen CONTENT_TREE Node wird der duplizierte Teilbaum als rechtestes Kind angehängt ) */
0048: /* ST_SUBTREE_ROOT_ID (Wurzelknoten ID des zu duplizierenden SITE_TREE Teilbaumes ) */
0049: /* ST_TARGET_ROOT_ID (An diesen SITE_TREE Node wird der duplizierte Teilbaum als rechtestes Kind angehängt ) */
0050: /* NEW_CONTENT_ROOT_NAME (Namen fŸr die Wurzelknoten der neuen SubbŠume) */
0051: /* NEW_CONTENT_ROOT_SHORTNAME */
0052: /* NEW_SITE_ROOT_NAME */
0053: /* NEW_SITE_ROOT_SHORTNAME */
0054: /* SITE_TREE_FLAG (Die Flags dienen zum an und ausschalten des Kopierens der Bereiche Site-Tree und Content.) */
0055: /* CONTENT_FLAG Obsolete in the current version */
0056:
0057: /*
0058: * @author $Author: uli $
0059: * @version $Revision: 1.15 $
0060: */
0061: public class TKDBDuplicateQuery extends TKPrepQuery implements
0062: DatabaseDefaults {
0063:
0064: /** prepeared */
0065: public final static boolean isPrepared = true;
0066:
0067: /**
0068: Parameter Reihenfolge */
0069: public final static String[] paramOrder = { //"SITE_TREE_FLAG","CONTENT_FLAG",
0070: "TEMPLATE_ROOT_ID", "TARGET_ROOT_ID", "NEW_CONTENT_ROOT_NAME",
0071: "NEW_CONTENT_ROOT_SHORTNAME", "ST_SUBTREE_ROOT_ID",
0072: "ST_TARGET_ROOT_ID", "NEW_SITE_ROOT_NAME",
0073: "NEW_SITE_ROOT_SHORTNAME" };
0074:
0075: public final static Object[][] paramTypes = {
0076: { "NEW_CONTENT_ROOT_NAME ", new Integer(Types.VARCHAR) },
0077: { "NEW_CONTENT_ROOT_SHORTNAME", new Integer(Types.VARCHAR) },
0078: { "NEW_SITE_ROOT_NAME", new Integer(Types.VARCHAR) },
0079: { "NEW_SITE_ROOT_SHORTNAME", new Integer(Types.VARCHAR) }, };
0080:
0081: public final static boolean[] setRelevants = { true, true };
0082:
0083: public final static String sqlString =
0084:
0085: /* Temporary Tables: */
0086:
0087: /* Sitetree tables */
0088:
0089: " CREATE TABLE #ST_tmp ( "
0090: + " SITE_NODE_ID int not null, "
0091: + " SITE_NODE_PARENT int null , "
0092: + " SITE_NODE_TYPE int not null, "
0093: + " SITE_NODE_NAME varchar(254) not null, "
0094: + " SITE_NODE_SHORTNAME varchar(80) not null, "
0095: + " LEFT_NR int not null, "
0096: + " RIGHT_NR int not null, "
0097: + " PG_CONTENT_NODE int null , "
0098: + " PG_SELECTION_ID int null , "
0099: + " ) "
0100:
0101: + " CREATE TABLE #ST_TRANSLATE(OLD_ID int, NEW_ID int NULL) "
0102:
0103: /* Contenttree tables */
0104:
0105: /*zna*/
0106: + " 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) "
0107:
0108: + " CREATE TABLE #TRANSLATE(OLD_ID int, NEW_ID int NULL) "
0109:
0110: /* Content tables */
0111:
0112: + " create table #CONTENT "
0113: + " ( "
0114: + " CONTENT_ID int not null, "
0115: + " XML_TEXT text null "
0116: // zna commented
0117: // + " CONTENT_NODE_ID int null , "
0118: // + " CONTENT_NAME varchar(254) null , "
0119: // + " CONTENT_SHORTNAME varchar(80) null , "
0120: // + " ORDER_IDX int null "
0121: + " ) "
0122:
0123: + " CREATE TABLE #CONTENT_TRANSLATE(OLD_ID int, NEW_ID int NULL, XML_TEXT text NULL) "
0124:
0125: + " CREATE TABLE #CON_INS_TRAN(OLD_ID int, NEW_ID int NULL) "
0126:
0127: + " CREATE TABLE #CON_VER_TRAN(OLD_ID int, NEW_ID int NULL) "
0128:
0129: + " create table #CONTENT_NODE "
0130: + " ( "
0131: + " CONTENT_ID int not null, "
0132: + " CONTENT_NODE_ID int not null, "
0133: + " LEFT_NR int not null, "
0134: + " RIGHT_NR int not null, "
0135: + " NAME varchar(80) not null "
0136: + " ) "
0137:
0138: + " create table #CONTENT_VALUE "
0139: + " ( "
0140: + " CONTENT_ID int not null, "
0141: + " CONTENT_NODE_ID int not null, "
0142: + " IDX int not null, "
0143: + " VALUE text not null, "
0144: //zna
0145: + " MEDIA_ID int null "
0146: + " ) "
0147:
0148: //zna
0149: /*
0150: + " create table #Content_Version "
0151: + " ( "
0152: + " VERSION_ID int not null, "
0153: + " INSTANCE_ID int not null, "
0154: + " CONTENT_ID int null , "
0155: + " STATUS_ID int not null, "
0156: + " VERSION_DATE datetime not null, "
0157: + " INFO varchar(254) null , "
0158: + " AUTHOR varchar(80) null "
0159: + " ) "
0160: */
0161: //zna
0162: + " create table #Content_Instance "
0163: + " ( "
0164: + " INSTANCE_ID int not null, "
0165: + " CONTENT_NODE_ID int not null, "
0166: + " NAME varchar(80) null "
0167: + " ) "
0168:
0169: //zna
0170: + " create table #Content_Attribute_Value "
0171: + " ( "
0172: + " CONTENT_ID int not null, "
0173: + " VALUE_ID int not null "
0174: + " ) "
0175:
0176: /* Relative references in DOCUMENT_REFERENCE, see 10.3) */
0177: + " 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) "
0178:
0179: + " BEGIN TRANSACTION "
0180:
0181: /* INPUT VARIABLES */
0182:
0183: /* Input Variables for CONTENT_TREE duplication */
0184:
0185: + " DECLARE @TEMPLATE_ROOT int "
0186: + " DECLARE @TARGET_ROOT int "
0187: + " DECLARE @NEW_CONTENT_ROOT_NAME varchar(254) "
0188: + " DECLARE @NEW_CONTENT_ROOT_SHORTNAME varchar(80) "
0189:
0190: /* Input Variables for SITE_TREE duplication */
0191:
0192: + " DECLARE @ST_SUBTREE_ROOT_ID int "
0193: + " DECLARE @ST_TARGET_ROOT_ID int "
0194: + " DECLARE @NEW_SITE_ROOT_NAME varchar(254) "
0195: + " DECLARE @NEW_SITE_ROOT_SHORTNAME varchar(80) "
0196:
0197: /* Input variables for flags */
0198:
0199: + " DECLARE @SITE_TREE_FLAG int "
0200: + " DECLARE @CONTENT_TREE_FLAG int "
0201:
0202: // + " DECLARE @CONTENT_FLAG int "
0203:
0204: /* Variables for creation of new IDs */
0205:
0206: + " DECLARE @C_count int "
0207: + " DECLARE @C_upper int "
0208: + " DECLARE @C_i int "
0209: + " DECLARE @C_j int "
0210:
0211: /* Variables of IDs of new roots (for Resultsets) */
0212:
0213: + " DECLARE @NEW_CONT_ROOT_ID int "
0214: + " DECLARE @NEW_SITE_ROOT_ID int "
0215:
0216: /* Root IDs and names of template (subtree) and target in the CONTENT_TREE*/
0217:
0218: + " SELECT @TEMPLATE_ROOT = ? "
0219: + " SELECT @TARGET_ROOT = ? "
0220: + " SELECT @NEW_CONTENT_ROOT_NAME = ? "
0221: + " SELECT @NEW_CONTENT_ROOT_SHORTNAME = ? "
0222:
0223: /* Root IDs and names of subtree and target in the SITE_TREE*/
0224:
0225: // + " IF (@SITE_TREE_FLAG = 1) "
0226: // + " BEGIN "
0227: + " SELECT @ST_SUBTREE_ROOT_ID = ? "
0228: + " SELECT @ST_TARGET_ROOT_ID = ? "
0229: + " SELECT @NEW_SITE_ROOT_NAME = ? "
0230: + " SELECT @NEW_SITE_ROOT_SHORTNAME = ? "
0231: // + " END "
0232:
0233: /* Assign flags */
0234:
0235: + " IF (@ST_SUBTREE_ROOT_ID != NULL AND @ST_TARGET_ROOT_ID != NULL ) "
0236: + " SELECT @SITE_TREE_FLAG = 1 "
0237: + " IF (@TEMPLATE_ROOT != NULL AND @TARGET_ROOT != NULL) "
0238: + " SELECT @CONTENT_TREE_FLAG = 1 "
0239: // + " SELECT @CONTENT_FLAG = ? "
0240:
0241: /* 1) The followig batch copies a subtree of CONTENT_TREE into a table #tmp */
0242: /* The CONTENT_NODE_ID of that subtree is in @TEMPLATE_ROOT_ID */
0243: /* The values of L/R nr. are manipulatet to append the subtree below */
0244: /* the node with CONTENT_NODE_ID @TARGET_ROOT_ID*/
0245:
0246: + " DECLARE @TARGET_RIGHT int " /* RIGHT_NR of target node */
0247: + " DECLARE @TEMPLATE_LEFT int "
0248: + " DECLARE @TEMPLATE_RIGHT int "
0249: + " DECLARE @NODE_COUNT int "
0250: + " DECLARE @LOCAL_SHIFT int " /* This Number is added to all L/R numbers of the template tree */
0251: + " DECLARE @GLOBAL_SHIFT int " /* This number is added to L/R numbers in CONTENT_TREE, bigger than @TARGET_RIGHT */
0252:
0253: /* retrieve RIGHT_NR -LEFT_NR of template root */
0254:
0255: + " SELECT @TEMPLATE_RIGHT = RIGHT_NR "
0256: + " FROM CONTENT_TREE "
0257: + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
0258:
0259: + " SELECT @TEMPLATE_LEFT = LEFT_NR "
0260: + " FROM CONTENT_TREE "
0261: + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
0262:
0263: /* Retrieve target node information :RIGHT_NR */
0264:
0265: + " SELECT @TARGET_RIGHT = RIGHT_NR "
0266: + " FROM CONTENT_TREE "
0267: + " WHERE CONTENT_NODE_ID = @TARGET_ROOT "
0268:
0269: /* Calculate the shift of the L/R numbers of duplicated records */
0270:
0271: + " SELECT @LOCAL_SHIFT = @TARGET_RIGHT - @TEMPLATE_LEFT "
0272:
0273: /* Write complete Vorlage Tree into #tmp */
0274:
0275: //zna
0276: + " 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) "
0277: + " select * FROM CONTENT_TREE "
0278: + " WHERE LEFT_NR > = @TEMPLATE_LEFT AND RIGHT_NR <= @TEMPLATE_RIGHT "
0279:
0280: /* Set the new names */
0281:
0282: + " UPDATE #tmp "
0283: + " SET CONTENT_NODE_NAME = @NEW_CONTENT_ROOT_NAME, CONTENT_NODE_SHORTNAME = @NEW_CONTENT_ROOT_SHORTNAME "
0284: + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
0285:
0286: /* Before re-insertion of the #tmp records into CONTENT_TREE their L/R Numbers are increased by @LOCAL_SHIFT */
0287:
0288: + " UPDATE #tmp "
0289: + " SET LEFT_NR = LEFT_NR + @LOCAL_SHIFT "
0290:
0291: + " UPDATE #tmp "
0292: + " SET RIGHT_NR = RIGHT_NR + @LOCAL_SHIFT "
0293:
0294: /* Count records in #tmp to calculate @GLOBAL_SHIFT */
0295:
0296: + " SELECT @GLOBAL_SHIFT = ((@TEMPLATE_RIGHT - @TEMPLATE_LEFT) + 1 ) "
0297:
0298: /* Increment L/R Nrs beyond TARGET_RIGHT to "make place" for the new subtree */
0299:
0300: + " UPDATE CONTENT_TREE "
0301: + " SET RIGHT_NR = RIGHT_NR + @GLOBAL_SHIFT "
0302: + " WHERE RIGHT_NR >= @TARGET_RIGHT "
0303:
0304: + " UPDATE CONTENT_TREE "
0305: + " SET LEFT_NR = LEFT_NR + @GLOBAL_SHIFT "
0306: + " WHERE LEFT_NR > @TARGET_RIGHT "
0307:
0308: /* 2) The following creates a table #TRANSLATE(OLD_ID int, NEW_ID int NULL) */
0309: /* and fills into OLD_ID the values of #tmp.CONTENT_NODE_ID. It fills the column */
0310: /* NEW_ID in #TRANSLATE with the values that can be used as new CONTENT_NODE_ID. */
0311: /* The table CONTENT_TREE is scanned for unused IDs. */
0312:
0313: /* Fill OLD_ID from #tmp */
0314:
0315: + " INSERT INTO #TRANSLATE(OLD_ID) "
0316: + " SELECT CONTENT_NODE_ID "
0317: + " FROM #tmp "
0318:
0319: /* Create NEW_ID entries */
0320:
0321: + " DECLARE @count int "
0322: + " DECLARE @upper int "
0323: + " DECLARE @i int "
0324: + " DECLARE @j int "
0325:
0326: + " SELECT @i = 1 "
0327:
0328: + " SELECT @j = 0 "
0329:
0330: + " SELECT @count = COUNT(*) "
0331: + " FROM #TRANSLATE "
0332:
0333: + " WHILE (@j < @count) "
0334: + " BEGIN "
0335: + " IF NOT ( @i IN (SELECT CONTENT_NODE_ID "
0336: + " FROM CONTENT_TREE "
0337: + " ) "
0338: + " ) "
0339: + " BEGIN "
0340: + " UPDATE #TRANSLATE "
0341: + " SET NEW_ID = @i "
0342: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0343: + " FROM #TRANSLATE "
0344: + " WHERE NEW_ID = NULL "
0345: + " ) "
0346: + " SELECT @j = @j + 1 "
0347: + " END "
0348: + " SELECT @i = @i +1 "
0349: + " END "
0350:
0351: /* 3) */
0352: /* Code for insertion of the updated #tmp- records, with new IDs into CONTENT_TREE */
0353: /* 3 Steps: */
0354: /* FIRST!! The PARENT_NODE_ID of the Template_root is set to Target_root ID */
0355: /* 3.2.The PARENT_NODE_ID of all the other records is updatet according to #TRANSLATE */
0356: /* 3.3. CONTENT_NODE_ID is updated in #tmp according to #TRANSLATE, and #tmp copied into CONTENT_TREE */
0357:
0358: /* 3.1) */
0359:
0360: + " UPDATE #tmp "
0361: + " SET CONTENT_NODE_PARENT = @TARGET_ROOT "
0362: + " WHERE CONTENT_NODE_ID = @TEMPLATE_ROOT "
0363:
0364: /* 3.2) */
0365:
0366: + " UPDATE #tmp "
0367: + " SET CONTENT_NODE_PARENT = TL.NEW_ID "
0368: + " FROM #TRANSLATE TL "
0369: + " WHERE (#tmp.CONTENT_NODE_PARENT = TL.OLD_ID "
0370: + " AND "
0371: + " #tmp.CONTENT_NODE_ID != @TEMPLATE_ROOT "
0372: + " ) "
0373:
0374: /* 3.3) */
0375:
0376: + " UPDATE #tmp "
0377: + " SET CONTENT_NODE_ID = TL.NEW_ID "
0378: + " FROM #TRANSLATE TL "
0379: + " WHERE #tmp.CONTENT_NODE_ID = TL.OLD_ID "
0380:
0381: //zna
0382: + " 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) "
0383: + " 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 "
0384: + " FROM #tmp "
0385:
0386: //marwan 9.2.
0387:
0388: /* Clear the temporary tables for reuse! */
0389:
0390: + " DELETE FROM #CONTENT_TRANSLATE "
0391:
0392: + " DELETE FROM #CON_INS_TRAN "
0393:
0394: + " DELETE FROM #CON_VER_TRAN "
0395:
0396: + " DELETE FROM #CONTENT_NODE "
0397:
0398: + " DELETE FROM #CONTENT_VALUE "
0399:
0400: /* CONTENT */
0401: /*
0402: + " SELECT DISTINCT @i = C.CONTENT_ID "
0403: + " FROM CONTENT C, CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
0404: + " WHERE "
0405: + " C.CONTENT_ID = C_V.CONTENT_ID "
0406: + " AND "
0407: + " C_V.INSTANCE_ID = C_I.INSTANCE_ID "
0408: + " AND "
0409: + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID"
0410:
0411: + " INSERT INTO #CONTENT_TRANSLATE (OLD_ID, XML_TEXT) "
0412: + " SELECT CONTENT_ID, XML_TEXT "
0413: + " FROM CONTENT "
0414: + " WHERE CONTENT_ID = @i "
0415: */
0416:
0417: + " INSERT INTO #CONTENT_TRANSLATE (OLD_ID) "
0418: + " SELECT DISTINCT C.CONTENT_ID "
0419: + " FROM CONTENT C,CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
0420: + " WHERE "
0421: + " C.CONTENT_ID = C_V.CONTENT_ID "
0422: + " AND "
0423: + " C_V.INSTANCE_ID = C_I.INSTANCE_ID "
0424: + " AND "
0425: + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
0426:
0427: + " SELECT @i = 1 "
0428: + " SELECT @j = 0 "
0429:
0430: + " SELECT @count = COUNT(*) "
0431: + " FROM #CONTENT_TRANSLATE "
0432:
0433: + " WHILE (@j < @count) "
0434: + " BEGIN "
0435: + " IF NOT ( @i IN (SELECT CONTENT_ID "
0436: + " FROM CONTENT "
0437: + " ) "
0438: + " ) "
0439: + " BEGIN "
0440: + " UPDATE #CONTENT_TRANSLATE "
0441: + " SET NEW_ID = @i "
0442: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0443: + " FROM #CONTENT_TRANSLATE "
0444: + " WHERE NEW_ID = NULL "
0445: + " ) "
0446: + " SELECT @j = @j + 1 "
0447: + " END "
0448: + " SELECT @i = @i +1 "
0449: + " END "
0450:
0451: + " INSERT INTO CONTENT "
0452: + " SELECT NEW_ID, XML_TEXT FROM #CONTENT_TRANSLATE "
0453:
0454: /* CONTENT_INSTANCE */
0455:
0456: + " INSERT INTO #CON_INS_TRAN (OLD_ID) "
0457: + " SELECT INSTANCE_ID "
0458: + " FROM CONTENT_INSTANCE, #TRANSLATE "
0459: + " WHERE CONTENT_INSTANCE.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
0460:
0461: + " SELECT @i = 1 "
0462:
0463: + " SELECT @j = 0 "
0464:
0465: + " SELECT @count = COUNT(*) "
0466: + " FROM #CON_INS_TRAN "
0467:
0468: + " WHILE (@j < @count) "
0469: + " BEGIN "
0470: + " IF NOT ( @i IN (SELECT INSTANCE_ID "
0471: + " FROM CONTENT_INSTANCE "
0472: + " ) "
0473: + " ) "
0474: + " BEGIN "
0475: + " UPDATE #CON_INS_TRAN "
0476: + " SET NEW_ID = @i "
0477: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0478: + " FROM #CON_INS_TRAN "
0479: + " WHERE NEW_ID = NULL "
0480: + " ) "
0481: + " SELECT @j = @j + 1 "
0482: + " END "
0483: + " SELECT @i = @i +1 "
0484: + " END "
0485:
0486: + " INSERT INTO CONTENT_INSTANCE (INSTANCE_ID, CONTENT_NODE_ID, NAME) "
0487: + " SELECT #CON_INS_TRAN.NEW_ID, #TRANSLATE.NEW_ID, NAME "
0488: + " FROM CONTENT_INSTANCE, #CON_INS_TRAN, #TRANSLATE "
0489: + " WHERE "
0490: + " CONTENT_INSTANCE.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
0491: + " AND "
0492: + " CONTENT_INSTANCE.INSTANCE_ID = #CON_INS_TRAN.OLD_ID "
0493:
0494: /* CONTENT_VERSION */
0495:
0496: + " INSERT INTO #CON_VER_TRAN (OLD_ID) "
0497: + " SELECT C_V.VERSION_ID "
0498: + " FROM CONTENT_VERSION C_V, CONTENT_INSTANCE C_I, #TRANSLATE "
0499: + " WHERE C_V.INSTANCE_ID = C_I.INSTANCE_ID "
0500: + " AND "
0501: + " C_I.CONTENT_NODE_ID = #TRANSLATE.OLD_ID "
0502:
0503: + " SELECT @i = 1 "
0504:
0505: + " SELECT @j = 0 "
0506:
0507: + " SELECT @count = COUNT(*) "
0508: + " FROM #CON_VER_TRAN "
0509:
0510: + " WHILE (@j < @count) "
0511: + " BEGIN "
0512: + " IF NOT ( @i IN (SELECT VERSION_ID "
0513: + " FROM CONTENT_VERSION "
0514: + " ) "
0515: + " ) "
0516: + " BEGIN "
0517: + " UPDATE #CON_VER_TRAN "
0518: + " SET NEW_ID = @i "
0519: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0520: + " FROM #CON_VER_TRAN "
0521: + " WHERE NEW_ID = NULL "
0522: + " ) "
0523: + " SELECT @j = @j + 1 "
0524: + " END "
0525: + " SELECT @i = @i +1 "
0526: + " END "
0527:
0528: + " INSERT INTO CONTENT_VERSION (VERSION_ID, INSTANCE_ID, CONTENT_ID, STATUS_ID, VERSION_DATE, INFO, AUTHOR) "
0529: + " 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 "
0530: + " FROM CONTENT_VERSION C_V, #CON_VER_TRAN, #CON_INS_TRAN, #CONTENT_TRANSLATE "
0531: + " WHERE "
0532: + " C_V.VERSION_ID = #CON_VER_TRAN.OLD_ID "
0533: + " AND "
0534: + " C_V.INSTANCE_ID = #CON_INS_TRAN.OLD_ID "
0535: + " AND "
0536: + " C_V.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID "
0537:
0538: /* CONTENT_NODE duplication */
0539:
0540: + " INSERT INTO CONTENT_NODE "
0541: + " SELECT C_T.NEW_ID, C_N.CONTENT_NODE_ID, C_N.LEFT_NR, C_N.RIGHT_NR, C_N.NAME "
0542: + " FROM CONTENT_NODE C_N, #CONTENT_TRANSLATE C_T "
0543: + " WHERE C_N.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
0544:
0545: /* CONTENT_VALUE duplication */
0546:
0547: + " INSERT INTO #CONTENT_VALUE "
0548: + " SELECT C_T.NEW_ID, C_V.CONTENT_NODE_ID, C_V.IDX, C_V.VALUE, C_V.MEDIA_ID "
0549: + " FROM CONTENT_VALUE C_V, #CONTENT_TRANSLATE C_T "
0550: + " WHERE C_V.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
0551:
0552: + " INSERT INTO CONTENT_VALUE "
0553: + " SELECT * FROM #CONTENT_VALUE "
0554:
0555: /* SITE TREE //////////////////////////////////////////////////////////////// */
0556:
0557: + " IF (@SITE_TREE_FLAG = 1) "
0558: + " BEGIN "
0559: /* 4) - 6) Dupliziert einen Teilbaum des SITE_TREE. SUBTREEE_ROOT_ID bezeichnet die ID */
0560: /* des Wurzelknotens des Teilbaums, TARGET_ROOT_ID die ID desjenigen Knotens */
0561: /* als dessen rechtestes Kind die Kopie eingefŸgt wird. */
0562:
0563: /* 4) The followig batch copies a subtree of SITE_TREE into a table #tmp */
0564: /* The SITE_NODE_ID of that subtree is in @ST_SUBTREE_ROOT_ID */
0565: /* The values of L/R nr. are manipulatet to append the subtree below */
0566: /* the node with SITE_NODE_ID @ST_TARGET_ROOT_ID */
0567:
0568: + " DECLARE @ST_TARGET_RIGHT int " /* RIGHT_NR of target node */
0569: + " DECLARE @ST_SUBTREE_LEFT int "
0570: + " DECLARE @ST_SUBTREE_RIGHT int "
0571: + " DECLARE @ST_NODE_COUNT int "
0572: + " DECLARE @ST_LOCAL_SHIFT int " /* This Number is added to all L/R numbers of the SUBTREE tree */
0573: + " DECLARE @ST_GLOBAL_SHIFT int " /* This number is added to L/R numbers in SITE_TREE, bigger than @ST_TARGET_RIGHT */
0574:
0575: /* retrieve RIGHT_NR -LEFT_NR of SUBTREE root */
0576:
0577: + " SELECT @ST_SUBTREE_RIGHT = RIGHT_NR "
0578: + " FROM SITE_TREE "
0579: + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
0580:
0581: + " SELECT @ST_SUBTREE_LEFT = LEFT_NR "
0582: + " FROM SITE_TREE "
0583: + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
0584:
0585: /* Retrieve target node information :RIGHT_NR */
0586:
0587: + " SELECT @ST_TARGET_RIGHT = RIGHT_NR "
0588: + " FROM SITE_TREE "
0589: + " WHERE SITE_NODE_ID = @ST_TARGET_ROOT_ID "
0590:
0591: /* Calculate the shift of the L/R numbers of duplicated records */
0592:
0593: + " SELECT @ST_LOCAL_SHIFT = @ST_TARGET_RIGHT - @ST_SUBTREE_LEFT "
0594:
0595: /* Write complete subtree into #ST_tmp */
0596:
0597: + " 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 ) "
0598: + " 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 "
0599: + " FROM SITE_TREE "
0600: + " WHERE LEFT_NR > = @ST_SUBTREE_LEFT AND RIGHT_NR <= @ST_SUBTREE_RIGHT "
0601:
0602: /* Set the new names */
0603:
0604: + " UPDATE #ST_tmp "
0605: + " SET SITE_NODE_NAME = @NEW_SITE_ROOT_NAME, SITE_NODE_SHORTNAME = @NEW_SITE_ROOT_SHORTNAME "
0606: + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
0607:
0608: /* Before re-insertion of the #tmp records into SITE_TREE their L/R Numbers are increased by @ST_LOCAL_SHIFT */
0609:
0610: + " UPDATE #ST_tmp "
0611: + " SET LEFT_NR = LEFT_NR + @ST_LOCAL_SHIFT "
0612:
0613: + " UPDATE #ST_tmp "
0614: + " SET RIGHT_NR = RIGHT_NR + @ST_LOCAL_SHIFT "
0615:
0616: /* calculate @ST_GLOBAL_SHIFT */
0617:
0618: + " SELECT @ST_GLOBAL_SHIFT = ((@ST_SUBTREE_RIGHT - @ST_SUBTREE_LEFT) +1 ) "
0619:
0620: /* Increment L/R Nrs beyond TARGET_RIGHT to "make place" for the new subtree */
0621:
0622: + " UPDATE SITE_TREE "
0623: + " SET RIGHT_NR = RIGHT_NR + @ST_GLOBAL_SHIFT "
0624: + " WHERE RIGHT_NR >= @ST_TARGET_RIGHT "
0625:
0626: + " UPDATE SITE_TREE "
0627: + " SET LEFT_NR = LEFT_NR + @ST_GLOBAL_SHIFT "
0628: + " WHERE LEFT_NR > @ST_TARGET_RIGHT "
0629:
0630: /* 5) The following creates a table #ST_TRANSLATE(OLD_ID int, NEW_ID int NULL) */
0631: /* and fills into OLD_ID the values of #tmp.SITE_NODE_ID. It fills the column */
0632: /* NEW_ID in #ST_TRANSLATE with the values that can be used as new SITE_NODE_ID. */
0633: /* The table SITE_TREE is scanned for unused IDs. */
0634:
0635: /* Fill OLD_ID from #tmp */
0636:
0637: + " INSERT INTO #ST_TRANSLATE(OLD_ID) "
0638: + " SELECT SITE_NODE_ID "
0639: + " FROM #ST_tmp "
0640:
0641: /* Create NEW_ID entries */
0642:
0643: + " DECLARE @ST_count int "
0644: + " DECLARE @ST_upper int "
0645: + " DECLARE @ST_i int "
0646: + " DECLARE @ST_j int "
0647:
0648: + " SELECT @ST_i = 1 "
0649:
0650: + " SELECT @ST_j = 0 "
0651:
0652: + " SELECT @ST_count = COUNT(*) "
0653: + " FROM #ST_TRANSLATE "
0654:
0655: + " WHILE (@ST_j < @ST_count) "
0656: + " BEGIN "
0657: + " IF NOT ( @ST_i IN (SELECT SITE_NODE_ID "
0658: + " FROM SITE_TREE "
0659: + " ) "
0660: + " ) "
0661: + " BEGIN "
0662: + " UPDATE #ST_TRANSLATE "
0663: + " SET NEW_ID = @ST_i "
0664: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0665: + " FROM #ST_TRANSLATE "
0666: + " WHERE NEW_ID = NULL "
0667: + " ) "
0668: + " SELECT @ST_j = @ST_j + 1 "
0669: + " END "
0670: + " SELECT @ST_i = @ST_i +1 "
0671: + " END "
0672:
0673: /* 6) */
0674: /* Code for insertion of the updated #tmp- records, with new IDs into SITE_TREE */
0675: /* 3 Steps: */
0676: /* 6.1 FIRST!! The PARENT_NODE_ID of the SUBTREE_ROOT is set to Target_root ID */
0677: /* 6.2.The PARENT_NODE_ID of all the other records is updatet according to #ST_TRANSLATE */
0678: /* 6.3. SITE_NODE_ID is updated in #tmp2 according to #ST_TRANSLATE, and #tmp2 copied into SITE_TREE */
0679:
0680: /* 6.1) */
0681:
0682: + " UPDATE #ST_tmp "
0683: + " SET SITE_NODE_PARENT = @ST_TARGET_ROOT_ID "
0684: + " WHERE SITE_NODE_ID = @ST_SUBTREE_ROOT_ID "
0685:
0686: /* 6.2) */
0687:
0688: + " UPDATE #ST_tmp "
0689: + " SET SITE_NODE_PARENT = TL.NEW_ID "
0690: + " FROM #ST_TRANSLATE TL "
0691: + " WHERE (#ST_tmp.SITE_NODE_PARENT = TL.OLD_ID "
0692: + " AND "
0693: + " SITE_NODE_ID != @ST_SUBTREE_ROOT_ID "
0694: + " ) "
0695:
0696: /* 6.3) */
0697:
0698: + " UPDATE #ST_tmp "
0699: + " SET SITE_NODE_ID = TL.NEW_ID "
0700: + " FROM #ST_TRANSLATE TL "
0701: + " WHERE #ST_tmp.SITE_NODE_ID = TL.OLD_ID "
0702:
0703: + " 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 ) "
0704: + " 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 "
0705: + " FROM #ST_tmp "
0706:
0707: /* 7) SITE_TREE FremdschlŸssel update: PG_CONTENT_NODE wird bei denjenigen Records verŠndert, */
0708: /* die im Site tree dupliziert worden sind und deren PG_CONTENT_NODE auf ein CONTENT_TREE */
0709: /* Record verweist, welches beim CONTENT_TREE dupliziert worden ist. */
0710:
0711: + " UPDATE SITE_TREE "
0712: + " SET ST.PG_CONTENT_NODE = CT_T.NEW_ID "
0713: + " FROM SITE_TREE ST, #TRANSLATE CT_T "
0714: + " WHERE ST.SITE_NODE_ID IN (SELECT NEW_ID " /* only duplicted SITE_TREE nodes are considered */
0715: + " FROM #ST_TRANSLATE "
0716: + " ) "
0717: + " AND "
0718: + " ST.PG_CONTENT_NODE = CT_T.OLD_ID " /* join-condition */
0719:
0720: /* 8) SITE_DOCUMENT primary key insert, according to SITE_TREE duplication */
0721:
0722: //zna
0723: + " 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) "
0724: + " 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 "
0725: + " FROM #ST_TRANSLATE ST_T, SITE_DOCUMENT SD "
0726: + " WHERE SD.SITE_NODE_ID = ST_T.OLD_ID "
0727:
0728: /* 9) DOCUMENT_CONTENT duplication */
0729:
0730: /* 9.1) Primary-Key: Insertion of records, whose SITE_NODE_ID points to a duplicated SITE_TREE node. */
0731:
0732: //zna
0733: + " INSERT INTO DOCUMENT_CONTENT (SITE_NODE_ID, SITE_NODE_DOC_IDX, PRESENTATION_COMPONENT_IDX, CONTENT_NODE_ID, PROTOTYPE_NODE_ID, SELECTION_ID) "
0734: + " 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 "
0735: + " FROM #ST_TRANSLATE ST_T, DOCUMENT_CONTENT DC "
0736: + " WHERE DC.SITE_NODE_ID = ST_T.OLD_ID "
0737:
0738: /* 9.2) Foreign-Key: update of CONTENT_NODE_ID in those records, which were duplicated in 9.1) */
0739: /* and whose CONTENT_NODE_ID points to a duplicated CONTENT_TREE record. */
0740:
0741: + " UPDATE DOCUMENT_CONTENT "
0742: + " SET DC.CONTENT_NODE_ID = CT_T.NEW_ID "
0743: + " FROM #TRANSLATE CT_T, DOCUMENT_CONTENT DC "
0744: + " WHERE DC.SITE_NODE_ID IN (SELECT NEW_ID "
0745: + " FROM #ST_TRANSLATE "
0746: + " ) "
0747: + " AND "
0748: + " DC.CONTENT_NODE_ID = CT_T.OLD_ID "
0749:
0750: /* 10) DOCUMENT_REFERENCE */
0751:
0752: /* 10.1) Primary-Key Insert: Records whose SRC_SITE_NODE_ID points to a duplicated SITE_TREE record. */
0753:
0754: + " 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) "
0755: + " 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 "
0756: + " FROM #ST_TRANSLATE ST_T, DOCUMENT_REFERENCE DR "
0757: + " WHERE DR.SRC_SITE_NODE_ID = ST_T.OLD_ID "
0758:
0759: /* 10.2) Foreign-Key: Update of those records which were inserted in 10.1 and whose DST_SITE_NODE_ID */
0760: /* points to a duplicated SITE_TREE node. */
0761:
0762: + " UPDATE DOCUMENT_REFERENCE "
0763: + " SET DR.DEST_SITE_NODE_ID = ST_T.NEW_ID "
0764: + " FROM DOCUMENT_REFERENCE DR, #ST_TRANSLATE ST_T "
0765: + " WHERE DR.DEST_SITE_NODE_ID = ST_T.OLD_ID "
0766: + " AND "
0767: + " DR.SRC_SITE_NODE_ID IN (SELECT NEW_ID "
0768: + " FROM #ST_TRANSLATE "
0769: + " ) "
0770:
0771: /* 10.3) Relative references: */
0772:
0773: + " INSERT INTO #REL_REFS (OLD_SRC, OLD_TAR, DOC_IDX, COMPONENT_IDX, NEW_SRC) "
0774: + " SELECT SRC_SITE_NODE_ID, DEST_SITE_NODE_ID, SRC_SITE_NODE_DOC_IDX, SRC_PRESENTATION_COMPONENT_IDX, NEW_ID "
0775: + " FROM DOCUMENT_REFERENCE, #ST_TRANSLATE "
0776: + " WHERE REFERENCE_TYPE = "
0777: + REFERENCE_RELATIVE
0778: + " AND "
0779: + " SRC_SITE_NODE_ID = OLD_ID "
0780:
0781: /* Calculate the "distance" */
0782: + " UPDATE #REL_REFS "
0783: + " SET DIST = "
0784: + " ( "
0785: + " SELECT COUNT(*) "
0786: + " FROM SITE_TREE "
0787: + " WHERE "
0788: + " LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_SRC) "
0789: + " AND "
0790: + " LEFT_NR > (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_TAR) "
0791: + " AND "
0792: + " RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_SRC) "
0793: + " AND "
0794: + " RIGHT_NR < (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.OLD_TAR) "
0795: + " ) "
0796:
0797: /** Avoid references inside the same site_node*/
0798: + " UPDATE DOCUMENT_REFERENCE "
0799: + " SET DEST_SITE_NODE_ID = NEW_SRC "
0800: + " FROM #REL_REFS "
0801: + " WHERE "
0802: + " NEW_SRC = DOCUMENT_REFERENCE.SRC_SITE_NODE_ID "
0803: + " AND "
0804: + " DOC_IDX = DOCUMENT_REFERENCE.SRC_SITE_NODE_DOC_IDX "
0805: + " AND "
0806: + " COMPONENT_IDX = DOCUMENT_REFERENCE.SRC_PRESENTATION_COMPONENT_IDX "
0807: + " AND"
0808: + " DIST=0"
0809:
0810: + " DELETE FROM #REL_REFS WHERE DIST=0"
0811:
0812: /* Find the new target, based on the distance */
0813:
0814: + " UPDATE #REL_REFS "
0815: + " SET NEW_TAR = "
0816: + " ( "
0817: + " SELECT SITE_NODE_ID "
0818: + " FROM SITE_TREE OUTER "
0819: + " WHERE "
0820: + " #REL_REFS.DIST = "
0821: + " ( "
0822: + " SELECT COUNT(*) "
0823: + " FROM SITE_TREE INNER "
0824: + " WHERE "
0825: + " INNER.LEFT_NR > OUTER.LEFT_NR "
0826: + " AND "
0827: + " INNER.RIGHT_NR < OUTER.RIGHT_NR "
0828: + " AND "
0829: + " INNER.LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.NEW_SRC) "
0830: + " AND "
0831: + " INNER.RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = #REL_REFS.NEW_SRC) "
0832: + " ) "
0833:
0834: + " ) "
0835: + " WHERE DIST != 0 "
0836:
0837: + " UPDATE DOCUMENT_REFERENCE "
0838: + " SET DEST_SITE_NODE_ID = NEW_TAR "
0839: + " FROM #REL_REFS "
0840: + " WHERE "
0841: + " NEW_SRC = DOCUMENT_REFERENCE.SRC_SITE_NODE_ID "
0842: + " AND "
0843: + " DOC_IDX = DOCUMENT_REFERENCE.SRC_SITE_NODE_DOC_IDX "
0844: + " AND "
0845: + " COMPONENT_IDX = DOCUMENT_REFERENCE.SRC_PRESENTATION_COMPONENT_IDX "
0846: + " AND "
0847: + " DIST != 0 "
0848:
0849: /* 11) STRUCTURED_CONTENT Primary-Key Insert: records whose SITE_NODE_ID references a duplicated SITE_TREE record, are duplicated and updated. */
0850:
0851: + " INSERT INTO STRUCTURED_CONTENT(SITE_NODE_ID, FORM_ID, CONTENT_ID) "
0852: + " SELECT ST_T.NEW_ID, S_C.FORM_ID, S_C.CONTENT_ID "
0853: + " FROM #ST_TRANSLATE ST_T, STRUCTURED_CONTENT S_C "
0854: + " WHERE ST_T.OLD_ID = S_C.SITE_NODE_ID "
0855:
0856: /* 12) CONTENT duplication, according to duplication of STRUCTURED_CONTENT */
0857:
0858: /* Clear the temporary tables for reuse! */
0859:
0860: + " DELETE FROM #CONTENT "
0861:
0862: + " DELETE FROM #CONTENT_TRANSLATE "
0863:
0864: + " DELETE FROM #CONTENT_NODE "
0865:
0866: + " DELETE FROM #CONTENT_VALUE "
0867:
0868: /* Initialize the #CONTENT_TRANSLATE */
0869:
0870: + " INSERT INTO #CONTENT_TRANSLATE "
0871: + " SELECT STRUCTURED_CONTENT.CONTENT_ID, NULL, C.XML_TEXT "
0872: + " FROM STRUCTURED_CONTENT, #ST_TRANSLATE, CONTENT C "
0873: + " WHERE STRUCTURED_CONTENT.SITE_NODE_ID = #ST_TRANSLATE.OLD_ID "
0874: + " AND C.CONTENT_ID = STRUCTURED_CONTENT.CONTENT_ID "
0875:
0876: /* Create NEW_ID entries in #CONTENT_TRANSLATE */
0877:
0878: + " SELECT @C_i = 1 "
0879:
0880: + " SELECT @C_j = 0 "
0881:
0882: + " SELECT @C_count = COUNT(*) "
0883: + " FROM #CONTENT_TRANSLATE "
0884:
0885: + " WHILE (@C_j < @C_count) "
0886: + " BEGIN "
0887: + " IF NOT ( @C_i IN (SELECT CONTENT_ID "
0888: + " FROM CONTENT "
0889: + " ) "
0890: + " ) "
0891: + " BEGIN "
0892: + " UPDATE #CONTENT_TRANSLATE "
0893: + " SET NEW_ID = @C_i "
0894: + " WHERE OLD_ID = (SELECT MIN (OLD_ID) "
0895: + " FROM #CONTENT_TRANSLATE "
0896: + " WHERE NEW_ID = NULL "
0897: + " ) "
0898: + " SELECT @C_j = @C_j + 1 "
0899: + " END "
0900: + " SELECT @C_i = @C_i +1 "
0901: + " END "
0902:
0903: /* Duplicate CONTENT records */
0904:
0905: //zna MUST BE COMMENTED!!!!!!!
0906: /* + " INSERT INTO #CONTENT (CONTENT_ID, CONTENT_NODE_ID, CONTENT_NAME, CONTENT_SHORTNAME, ORDER_IDX ) "
0907: + " SELECT #CONTENT_TRANSLATE.NEW_ID, C.CONTENT_NODE_ID, C.CONTENT_NAME, C.CONTENT_SHORTNAME, C.ORDER_IDX "
0908: + " FROM CONTENT C, #CONTENT_TRANSLATE "
0909: + " WHERE C.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID " */
0910:
0911: //zna
0912: + " INSERT INTO #CONTENT (CONTENT_ID, XML_TEXT ) "
0913: + " SELECT #CONTENT_TRANSLATE.NEW_ID, #CONTENT_TRANSLATE.XML_TEXT "
0914: + " FROM CONTENT C, #CONTENT_TRANSLATE "
0915: + " WHERE C.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID "
0916:
0917: + " INSERT INTO CONTENT "
0918: + " SELECT * FROM #CONTENT "
0919:
0920: //zna - Duplicate Content_Attribute_Value, Content_Version & Content_Instance
0921: /*+ "INSERT INTO CONTENT_ATTRIBUTE_VALUE (CONTENT_ID, VALUE_ID) "
0922: + "SELECT ct.NEW_ID, cav.VALUE_ID "
0923: + "FROM Content_Attribute_Value cav, #CONTENT_TRANSLATE ct "
0924: + "where cav.content_id = ct.old_id "
0925:
0926: */
0927: /* Update the CONTENT_ID in STRUCTURED_CONTENT */
0928:
0929: + " UPDATE STRUCTURED_CONTENT "
0930: + " SET STRUCTURED_CONTENT.CONTENT_ID = #CONTENT_TRANSLATE.NEW_ID "
0931: + " FROM #CONTENT_TRANSLATE, #ST_TRANSLATE "
0932: + " WHERE STRUCTURED_CONTENT.CONTENT_ID = #CONTENT_TRANSLATE.OLD_ID " /* Find the new CONTENT_ID */
0933: + " AND "
0934: + " STRUCTURED_CONTENT.SITE_NODE_ID = #ST_TRANSLATE.NEW_ID " /* Select the duplicated STRUCTURED_CONTENT records */
0935:
0936: /* Duplicate CONTENT_NODE records */
0937:
0938: + " INSERT INTO #CONTENT_NODE "
0939: + " SELECT C_T.NEW_ID, C_N.CONTENT_NODE_ID, C_N.LEFT_NR, C_N.RIGHT_NR, C_N.NAME "
0940: + " FROM CONTENT_NODE C_N, #CONTENT_TRANSLATE C_T "
0941: + " WHERE C_N.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
0942:
0943: + " INSERT INTO CONTENT_NODE "
0944: + " SELECT * FROM #CONTENT_NODE "
0945:
0946: /* CONTENT_VALUE duplication */
0947:
0948: // zna
0949: + " INSERT INTO #CONTENT_VALUE "
0950: + " SELECT C_T.NEW_ID, C_V.CONTENT_NODE_ID, C_V.IDX, C_V.VALUE, C_V.MEDIA_ID "
0951: + " FROM CONTENT_VALUE C_V, #CONTENT_TRANSLATE C_T "
0952: + " WHERE C_V.CONTENT_ID = C_T.OLD_ID " /* join condition: exactly those records are duplicated, that point to a duplicated CONTENT record */
0953:
0954: + " INSERT INTO CONTENT_VALUE "
0955: + " SELECT * FROM #CONTENT_VALUE "
0956:
0957: + " END " /* IF (@SITE_TREE_FLAG =1) (11 / 12) */
0958:
0959: /* Root IDs of created subtrees for return */
0960: + " SELECT @NEW_CONT_ROOT_ID = NEW_ID "
0961: + " FROM #TRANSLATE "
0962: + " WHERE OLD_ID = @TEMPLATE_ROOT "
0963:
0964: + " SELECT @NEW_SITE_ROOT_ID = NEW_ID "
0965: + " FROM #ST_TRANSLATE "
0966: + " WHERE OLD_ID = @ST_SUBTREE_ROOT_ID "
0967:
0968: /* Resultsets */
0969:
0970: + " SELECT @NEW_CONT_ROOT_ID AS NEW_CONT_ROOT_ID, @NEW_SITE_ROOT_ID AS NEW_SITE_ROOT_ID "
0971:
0972: /* Commit the whole batch! */
0973:
0974: + " COMMIT "
0975:
0976: /* Cleaning up temporary tables */
0977:
0978: /* Clean SITE_TREE garbage */
0979:
0980: + " drop table #ST_tmp "
0981: + " drop table #ST_TRANSLATE "
0982:
0983: /* Clean CONTENT_TREE garbage */
0984:
0985: + " drop table #tmp "
0986: + " drop table #TRANSLATE "
0987:
0988: /* Clean CONTENT garbage */
0989: + " drop table #CONTENT "
0990: + " drop table #CONTENT_TRANSLATE "
0991: + " drop table #CON_INS_TRAN "
0992: + " drop table #CON_VER_TRAN "
0993: + " drop table #CONTENT_NODE "
0994: + " drop table #CONTENT_VALUE "
0995: + " drop table #Content_Instance "
0996: + " drop table #Content_Attribute_Value"
0997:
0998: /* DOCUMENT_REFERENCES */
0999: + " DROP TABLE #REL_REFS ";
1000:
1001: public void initQuery(Connection con) {
1002: super.initQuery(con, isPrepared, paramOrder, paramTypes,
1003: setRelevants, sqlString);
1004: }
1005: }
|