001: /*
002: * $Header: /cvsroot/webman-cms/source/webman/com/teamkonzept/webman/mainint/db/queries/oracle/Attic/TKDBSiteTreeMoveNode.java,v 1.2 2001/04/03 12:31:35 marwan Exp $
003: *
004: */
005: package com.teamkonzept.webman.mainint.db.queries.oracle;
006:
007: import java.sql.*;
008:
009: import com.teamkonzept.webman.mainint.DatabaseDefaults;
010: import com.teamkonzept.db.*;
011:
012: /*
013: * TKDBSiteTreeMoveNode
014: * Fuegt einen neuen Knoten unterhalb eines Parent Nodes ein
015: * Input:
016: * SRC_NODE_ID (Knoten des zu verschiebenen Knotens )
017: * DEST_NODE_ID (Knoten vor/nach dem eingefuegt werden soll)
018: * UPORDOWN UP = 1 Down = -1
019: * Output:NIX
020: */
021: public class TKDBSiteTreeMoveNode extends TKPrepQuery implements
022: DatabaseDefaults {
023:
024: public final static boolean isPrepared = true;
025:
026: public final static String[] paramOrder = { "SRC_NODE_ID",
027: "DEST_NODE_ID", "UPORDOWN" };
028:
029: public final static Object[][] paramTypes = null;
030:
031: public final static boolean[] setRelevants = { false };
032:
033: public final static String sqlString =
034:
035: " DECLARE "
036: + " src_id INTEGER; "
037: + " dest_id INTEGER; "
038: + " up_down INTEGER; " /* if 0 src becomes a child of dest, -1 src is inserted after dest, 1 src is inserted before dest */
039: + " pred INTEGER; " /* the predecessor of the "insertion point" in the sequence of left and right numbers */
040: + " src_left INTEGER; "
041: + " src_right INTEGER; "
042: + " dest_left INTEGER; "
043: + " dest_right INTEGER; "
044: + " node_type INTEGER; "
045: + " new_parent INTEGER; "
046: + " shift INTEGER; "
047: + " inc INTEGER; "
048: + " BEGIN "
049: + " src_id := ? ; " /* SRC_NODE_ID */
050: + " dest_id := ? ; " /* DEST_NODE_ID */
051: + " up_down := ? ; " /* UPORDOWN */
052:
053: + " SELECT LEFT_NR INTO src_left FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
054: + " SELECT RIGHT_NR INTO src_right FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
055:
056: + " SELECT LEFT_NR INTO dest_left FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
057: + " SELECT RIGHT_NR INTO dest_right FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
058:
059: + " SELECT SITE_NODE_TYPE INTO node_type FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
060:
061: + " IF " + " ( " + " ( " /* Dest may not be a child of src */
062: + " src_left <= dest_left AND src_right >= dest_right "
063: + " ) " + " OR " + " ( " /* Not into a group-node */
064: + " up_down = 0 AND node_type < "
065: + SITE_DIRECTORY
066: + " ) "
067: + " ) "
068: + " THEN "
069: + " RETURN; "
070: + " END IF; "
071:
072: + " IF(up_down = 0) THEN "
073: + " new_parent := dest_id; "
074: + " SELECT LEFT_NR INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
075: + " ELSIF (up_down = 1)THEN "
076: + " SELECT SITE_NODE_PARENT INTO new_parent FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
077: + " SELECT (LEFT_NR -1) INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
078: + " ELSIF (up_down = -1) THEN "
079: + " SELECT SITE_NODE_PARENT INTO new_parent FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
080: + " SELECT RIGHT_NR INTO pred FROM SITE_TREE WHERE SITE_NODE_ID = dest_id; "
081: + " END IF; "
082:
083: + " shift := src_right - src_left +1; "
084:
085: + " " /* Make space */
086:
087: + " UPDATE SITE_TREE "
088: + " SET LEFT_NR = LEFT_NR + shift "
089: + " WHERE LEFT_NR > pred; "
090:
091: + " UPDATE SITE_TREE "
092: + " set RIGHT_NR = RIGHT_NR + shift "
093: + " WHERE RIGHT_NR > pred; "
094:
095: + " " /* Move source subtree */
096:
097: + " " /* New values after shift */
098: + " SELECT LEFT_NR INTO src_left FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
099: + " SELECT RIGHT_NR INTO src_right FROM SITE_TREE WHERE SITE_NODE_ID = src_id; "
100:
101: + " inc := pred - src_left +1; "
102:
103: + " UPDATE SITE_TREE "
104: + " SET LEFT_NR = LEFT_NR + inc, RIGHT_NR = RIGHT_NR + inc "
105: + " WHERE "
106: + " LEFT_NR >= src_left "
107: + " AND "
108: + " RIGHT_NR <= src_right; "
109:
110: + " " /* Adjust SITE_NODE_PARENT */
111:
112: + " UPDATE SITE_TREE "
113: + " SET SITE_NODE_PARENT = new_parent "
114: + " WHERE SITE_NODE_ID = src_id; " + " END; ";
115:
116: public void initQuery(Connection con) {
117: super.initQuery(con, isPrepared, paramOrder, paramTypes,
118: setRelevants, sqlString);
119: }
120: }
|