01: /*
02: * $Header: /cvsroot/webman-cms/source/webman/com/teamkonzept/webman/mainint/db/queries/sitetree/Attic/CreateProcAdjustRefs.java,v 1.2 2000/12/13 11:29:31 marwan Exp $
03: *
04: */
05: package com.teamkonzept.webman.mainint.db.queries.sitetree;
06:
07: import java.sql.*;
08:
09: import com.teamkonzept.db.*;
10:
11: public class CreateProcAdjustRefs extends TKPrepQuery {
12:
13: public final static boolean isPrepared = true;
14:
15: public final static String[] paramOrder = null;
16: public final static Object[][] paramTypes = null;
17:
18: public final static boolean[] setRelevants = null;
19:
20: public final static String sqlString =
21:
22: /* procedure for adjusting relative references of a certain DOCUMENT_REFERENCE record */
23:
24: " CREATE OR REPLACE FUNCTION adjust_relative_refs(old_id INTEGER, old_idx INTEGER, component_idx INTEGER, new_id INTEGER, new_idx INTEGER) "
25: + " RETURN INTEGER IS "
26: + " new_dest INTEGER; " /* return value */
27: + " old_dest INTEGER; "
28: + " curr_id INTEGER; " /* running pointer */
29: + " ref_type INTEGER; "
30: + " BEGIN "
31: + " curr_id := old_id; "
32: + " new_dest := new_id; "
33:
34: + " " /* consider only REFERENCE_TYPE 2 */
35: + " SELECT REFERENCE_TYPE INTO ref_type FROM DOCUMENT_REFERENCE WHERE SRC_SITE_NODE_ID = new_id AND SRC_SITE_NODE_DOC_IDX = new_idx AND SRC_PRESENTATION_COMPONENT_IDX = component_idx; "
36: + " IF(ref_type != 2) "
37: + " THEN "
38: + " RETURN new_dest; "
39: + " END IF; "
40:
41: + " " /* find old destination */
42:
43: + " SELECT DEST_SITE_NODE_ID INTO old_dest "
44: + " FROM DOCUMENT_REFERENCE "
45: + " WHERE SRC_SITE_NODE_ID = old_id AND SRC_SITE_NODE_DOC_IDX = old_idx AND SRC_PRESENTATION_COMPONENT_IDX = component_idx; "
46:
47: + " WHILE(curr_id != old_dest) LOOP "
48: + " SELECT SITE_NODE_PARENT INTO curr_id FROM SITE_TREE WHERE SITE_NODE_ID = curr_id; "
49: + " SELECT SITE_NODE_PARENT INTO new_dest FROM SITE_TREE WHERE SITE_NODE_ID = new_dest; "
50:
51: + " END LOOP; "
52:
53: + " RETURN new_dest; " + " END adjust_relative_refs; ";
54:
55: public void initQuery(Connection con) {
56: super.initQuery(con, isPrepared, paramOrder, paramTypes,
57: setRelevants, sqlString);
58: }
59: }
|