001: package com.teamkonzept.webman.mainint.db.queries;
002:
003: import java.sql.*;
004: import com.teamkonzept.db.*;
005: import com.teamkonzept.webman.mainint.db.queries.content.*;
006: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
007:
008: /*
009: * TKDBContentTreeMoveNode
010: * Fuegt einen neuen Knoten ueber/unterhalb eines Nodes ein
011: * Input:
012: * SRC_NODE_ID (Knoten des zu verschiebenen Knotens )
013: * DEST_NODE_ID (Knoten vor/nach dem eingefuegt werden soll)
014: * UPORDOWN UP = 1 Down = -1
015: * Output:NIX
016: */
017: public class TKDBContentTreeMoveNode extends TKExtendedPrepQuery {
018:
019: public final static boolean isPrepared = true;
020:
021: public final static String[] paramOrder = { "SRC_NODE_ID",
022: "DEST_NODE_ID", "UPORDOWN" };
023:
024: public final static Object[][] types = null;
025:
026: public final static boolean[] setRelevants = { false };
027:
028: /* public final static String sqlString =
029: "DECLARE @SRC INT " +
030: "DECLARE @DEST INT " +
031: "DECLARE @UPORDOWN INT " +
032:
033: "DECLARE @SRC_L INT " +
034: "DECLARE @SRC_R INT " +
035:
036: "DECLARE @DEST_NR INT " +
037: "DECLARE @DIFF INT " +
038: "DECLARE @MAX_RIGHT INT " +
039:
040: "SELECT @SRC = ? " +
041: "SELECT @DEST = ? " +
042: "SELECT @UPORDOWN = ? " +
043:
044: "SELECT " +
045: "@SRC_L = LEFT_NR, " +
046: "@SRC_R = RIGHT_NR " +
047: "FROM " +
048: "CONTENT_TREE " +
049: "WHERE " +
050: "CONTENT_NODE_ID = @SRC " +
051:
052: "IF ( " +
053: "(@SRC != @DEST) " +
054: "AND NOT " +
055: "( " +
056: "@UPORDOWN = 0 " +
057: "AND " +
058: "EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 1 ) " +
059: ")" +
060: "AND NOT " +
061: "EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST) " +
062: ")" +
063: "BEGIN " +
064:
065: "BEGIN TRANSACTION " +
066:
067: "SELECT " +
068: "@MAX_RIGHT = MAX(RIGHT_NR) " +
069: "FROM " +
070: "CONTENT_TREE " +
071:
072: "SELECT " +
073: "@SRC_L = LEFT_NR, " +
074: "@SRC_R = RIGHT_NR " +
075: "FROM " +
076: "CONTENT_TREE " +
077: "WHERE " +
078: "CONTENT_NODE_ID = @SRC " +
079:
080: // Den Teilbaum (der verschoben werden soll) erst mal ganz raus
081:
082: "UPDATE " +
083: "CONTENT_TREE " +
084: "SET " +
085: "RIGHT_NR = RIGHT_NR + @MAX_RIGHT, " +
086: "LEFT_NR = LEFT_NR + @MAX_RIGHT " +
087: "WHERE " +
088: "LEFT_NR >= @SRC_L " +
089: "AND " +
090: "RIGHT_NR <= @SRC_R " +
091:
092: "SELECT @DIFF = (@SRC_R - @SRC_L) + 1 " +
093:
094: //Denn restlichen Baum wieder zurecht ruecken
095: "UPDATE " +
096: "CONTENT_TREE " +
097: "SET " +
098: "LEFT_NR = LEFT_NR - @DIFF " +
099: "WHERE " +
100: "LEFT_NR >= @SRC_L " +
101: "AND " +
102: "RIGHT_NR <= @MAX_RIGHT " +
103:
104: "UPDATE " +
105: "CONTENT_TREE " +
106: "SET " +
107: "RIGHT_NR = RIGHT_NR - @DIFF " +
108: "WHERE " +
109: "RIGHT_NR >= @SRC_L " +
110: "AND " +
111: "RIGHT_NR <= @MAX_RIGHT " +
112:
113: // Ziel ist vor oder hinter Zielknoten ?
114: "IF (@UPORDOWN = 1) " +
115: "BEGIN " +
116: "SELECT " +
117: "@DEST_NR = LEFT_NR " +
118: "FROM " +
119: "CONTENT_TREE " +
120: "WHERE " +
121: "CONTENT_NODE_ID = @DEST " +
122: "END ELSE BEGIN " +
123: "IF (@UPORDOWN = -1) " +
124: "BEGIN " +
125: "SELECT " +
126: "@DEST_NR = RIGHT_NR + 1 " +
127: "FROM " +
128: "CONTENT_TREE " +
129: "WHERE " +
130: "CONTENT_NODE_ID = @DEST " +
131: "END ELSE BEGIN " +
132: "SELECT " +
133: "@DEST_NR = LEFT_NR + 1 " +
134: "FROM " +
135: "CONTENT_TREE " +
136: "WHERE " +
137: "CONTENT_NODE_ID = @DEST " +
138: "END " +
139: "END " +
140:
141: // An der richtigen Stelle Platz schaffen
142: "UPDATE " +
143: "CONTENT_TREE " +
144: "SET " +
145: "LEFT_NR = LEFT_NR + @DIFF " +
146: "WHERE " +
147: "LEFT_NR >= @DEST_NR " +
148: "AND " +
149: "RIGHT_NR <= @MAX_RIGHT " +
150:
151: "UPDATE " +
152: "CONTENT_TREE " +
153: "SET " +
154: "RIGHT_NR = RIGHT_NR + @DIFF " +
155: "WHERE " +
156: "RIGHT_NR >= @DEST_NR " +
157: "AND " +
158: "RIGHT_NR <= @MAX_RIGHT " +
159:
160: "SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT " +
161:
162: // Src Knoten an diese Stelle einfuegen
163: "UPDATE " +
164: "CONTENT_TREE " +
165: "SET " +
166: "RIGHT_NR = RIGHT_NR + @DIFF, " +
167: "LEFT_NR = LEFT_NR + @DIFF " +
168: "WHERE " +
169: "RIGHT_NR > @MAX_RIGHT " +
170:
171: // Und den neuen Parent des Src Knotens setzen
172: "IF (@UPORDOWN = 0) " +
173: "BEGIN " +
174: "UPDATE " +
175: "CONTENT_TREE " +
176: "SET " +
177: "CONTENT_NODE_PARENT = @DEST " +
178: "WHERE " +
179: "CONTENT_NODE_ID = @SRC " +
180: "END ELSE BEGIN " +
181: "UPDATE " +
182: "CONTENT_TREE " +
183: "SET " +
184: "CONTENT_NODE_PARENT = " +
185: "(SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST) " +
186: "WHERE " +
187: "CONTENT_NODE_ID = @SRC " +
188: "END " +
189:
190: "COMMIT TRANSACTION " +
191:
192: "END ";
193: */
194:
195: protected static Class[] queryClasses = {
196: SelectLeftRightNrFromContentTree.class, // [0]
197: SelectOneFromContentTreeContentType.class, // [1]
198: SelectOneFromContentTreeLeftRightNr.class, // [2]
199: SelectMaxFromContentTree.class, // [3]
200: SelectLeftRightNrFromContentTree.class, // [4]
201: UpdateContentTreeLeftRightNr.class, // [5]
202: UpdateContentTreeSetLeftNr.class, // [6]
203: UpdateContentTreeSetRightNr.class, // [7]
204: SelectLeftNrFromContentTree.class, // [8]
205: SelectRightNrPlusOneFromContentTree.class, // [9]
206: SelectLeftNrPlusOneFromContentTree.class, // [10]
207: UpdateContentTreeSetLeftNrPlus.class, // [11]
208: UpdateContentTreeSetRightNrPlus.class, // [12]
209: UpdateContentTreeSetRightNrGreater.class, // [13]
210: UpdateContentTreeSetContentNodeParent.class, // [14]
211: UpdateContentTreeSetContentNodeParentSelect.class // [15]
212: };
213:
214: public boolean execute() {
215: try {
216: init(queryClasses); // init query objects
217:
218: boolean isNotOpen = aTKDBConnection.isAutoCommit();
219: if (isNotOpen) {
220: TKDBManager.beginTransaction(); // begin transaction
221: }
222:
223: // SELECT @SRC = ?
224: // SELECT @DEST = ?
225: // SELECT @UPORDOWN = ?
226: Integer src = (Integer) queryParams.get("SRC_NODE_ID");
227: Integer dest = (Integer) queryParams.get("DEST_NODE_ID");
228: Integer upordown = (Integer) queryParams.get("UPORDOWN");
229:
230: // OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
231: // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
232: // CLASS: SelectLeftRightNrFromContentTree.class
233: // PARAMS: NODE_ID
234: // [0]
235: queries[0].setQueryParams("NODE_ID", src);
236: queries[0].execute();
237: Integer leftNr = null;
238: Integer rightNr = null;
239: int left_native = 0;
240: int right_native = 0;
241: ResultSet rs0 = queries[0].fetchResultSet();
242: if (rs0 != null && rs0.next()) {
243: left_native = rs0.getInt(1);
244: right_native = rs0.getInt(2);
245: }
246:
247: leftNr = new Integer(left_native);
248: rightNr = new Integer(right_native);
249:
250: // OLD:
251: // IF ( (@SRC != @DEST)
252: // AND NOT (
253: // @UPORDOWN = 0
254: // AND
255: // EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 2 )
256: // )
257: // AND NOT
258: // EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST)
259: // )
260: // NEW:
261: // SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ? AND CONTENT_NODE_TYPE > 2
262: // CLASS: SelectOneFromContentTreeContentType.class
263: // PARAMS: NODE_ID
264: // [1]
265: queries[1].setQueryParams("NODE_ID", dest);
266: queries[1].execute();
267: ResultSet rs1 = queries[1].fetchResultSet();
268: // SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > ? AND RIGHT_NR < ? AND CONTENT_NODE_ID = ?
269: // CLASS: SelectOneFromContentTreeLeftRightNr.class
270: // PARAMS: LEFT_NR, RIGHT_NR, NODE_ID
271: // [2]
272: queries[2].setQueryParams("LEFT_NR", leftNr);
273: queries[2].setQueryParams("RIGHT_NR", rightNr);
274: queries[2].setQueryParams("NODE_ID", dest);
275: queries[2].execute();
276: ResultSet rs2 = queries[2].fetchResultSet();
277:
278: boolean hasResult1 = rs1 != null && rs1.next();
279: boolean hasResult2 = rs2 != null && rs2.next();
280:
281: // if ((src.intValue() != dest.intValue())
282: // && !(upordown.intValue() == 0
283: // &&
284: // (rs1 != null && rs1.next()))
285: // && !((rs2 != null && rs2.next())))
286: if ((src.intValue() != dest.intValue())
287: && !(upordown.intValue() == 0 && hasResult1)
288: && !hasResult2) {
289:
290: // OLD: SELECT @MAX_RIGHT = MAX(RIGHT_NR) FROM CONTENT_TREE
291: // NEW: SELECT MAX(RIGHT_NR) FROM CONTENT_TREE
292: // CLASS: SelectMaxFromContentTree.class
293: // [3]
294: Object maxRight = null;
295: queries[3].execute();
296: ResultSet rs3 = queries[3].fetchResultSet();
297: if (rs3 != null && rs3.next()) {
298: maxRight = rs3.getObject(1);
299: }
300:
301: // OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
302: // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
303: // CLASS: SelectLeftRightNrFromContentTree.class
304: // PARAMS: NODE_ID
305: // [4]
306: queries[4].setQueryParams("NODE_ID", src);
307: queries[4].execute();
308: leftNr = null;
309: rightNr = null;
310: left_native = 0;
311: right_native = 0;
312: ResultSet rs4 = queries[4].fetchResultSet();
313: if (rs4 != null && rs4.next()) {
314: left_native = rs4.getInt(1);
315: right_native = rs4.getInt(2);
316: }
317: leftNr = new Integer(left_native);
318: rightNr = new Integer(right_native);
319:
320: // Den Teilbaum (der verschoben werden soll) erst mal ganz raus
321:
322: // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @MAX_RIGHT,
323: // LEFT_NR = LEFT_NR + @MAX_RIGHT
324: // WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @SRC_R
325: // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
326: // CLASS: UpdateContentTreeLeftRightNr.class
327: // PARAMS: MAX_RIGHT, LEFT_NR, RIGHT_NR
328: // [5]
329: queries[5].setQueryParams("MAX_RIGHT", maxRight);
330: queries[5].setQueryParams("LEFT_NR", leftNr);
331: queries[5].setQueryParams("RIGHT_NR", rightNr);
332: queries[5].execute();
333:
334: // SELECT @DIFF = (@SRC_R - @SRC_L) + 1
335: int leftVal = 0;
336: int rightVal = 0;
337: if (leftNr != null) {
338: leftVal = leftNr.intValue();
339: }
340: if (rightNr != null) {
341: rightVal = rightNr.intValue();
342: }
343: Integer diff = new Integer(rightVal - leftVal + 1);
344:
345: //Denn restlichen Baum wieder zurecht ruecken
346:
347: // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - @DIFF WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
348: // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
349: // CLASS: UpdateContentTreeSetLeftNr.class
350: // PARAMS: DIFF, LEFT_NR, RIGHT_NR
351: // [6]
352: queries[6].setQueryParams("DIFF", diff);
353: queries[6].setQueryParams("LEFT_NR", leftNr);
354: queries[6].setQueryParams("RIGHT_NR", maxRight);
355: queries[6].execute();
356:
357: // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - @DIFF WHERE RIGHT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
358: // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
359: // CLASS: UpdateContentTreeSetRightNr.class
360: // PARAMS: DIFF, LEFT_NR, RIGHT_NR
361: // [7]
362: queries[7].setQueryParams("DIFF", diff);
363: queries[7].setQueryParams("LEFT_NR", leftNr);
364: queries[7].setQueryParams("RIGHT_NR", maxRight);
365: queries[7].execute();
366:
367: // Ziel ist vor oder hinter Zielknoten ?
368: // OLD: IF (@UPORDOWN = 1)
369: Object destNr = null;
370: if (upordown.intValue() == 1) {
371: // OLD: SELECT @DEST_NR = LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
372: // NEW: SELECT LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
373: // CLASS: SelectLeftNrFromContentTree.class
374: // PARAMS: NODE_ID
375: // [8]
376: queries[8].setQueryParams("NODE_ID", dest);
377: queries[8].execute();
378: ResultSet rs8 = queries[8].fetchResultSet();
379: //destNr = null;
380: if (rs8 != null && rs8.next()) {
381: destNr = rs8.getObject(1);
382: }
383: }
384: // OLD: END ELSE BEGIN
385: else {
386: // OLD: IF (@UPORDOWN = -1)
387: if (upordown.intValue() == -1) {
388: // OLD: SELECT @DEST_NR = RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
389: // NEW: SELECT RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
390: // CLASS: SelectRightNrPlusOneFromContentTree.class
391: // PARAMS: NODE_ID
392: // [9]
393: queries[9].setQueryParams("NODE_ID", dest);
394: queries[9].execute();
395: destNr = null;
396: ResultSet rs9 = queries[9].fetchResultSet();
397: if (rs9 != null && rs9.next()) {
398: destNr = rs9.getObject(1);
399: }
400: }
401: // END ELSE BEGIN
402: else {
403: // OLD: SELECT @DEST_NR = LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
404: // NEW: SELECT LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
405: // CLASS: SelectLeftNrPlusOneFromContentTree.class
406: // PARAMS: NODE_ID
407: // [10]
408: queries[10].setQueryParams("NODE_ID", dest);
409: queries[10].execute();
410: destNr = null;
411: ResultSet rs10 = queries[10].fetchResultSet();
412: if (rs10 != null && rs10.next()) {
413: destNr = rs10.getObject(1);
414: }
415: }
416: }
417:
418: // An der richtigen Stelle Platz schaffen
419: // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + @DIFF WHERE LEFT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
420: // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
421: // CLASS: UpdateContentTreeSetLeftNrPlus.class
422: // PARAMS: DIFF, LEFT_NR, RIGHT_NR
423: // [11]
424: queries[11].setQueryParams("DIFF", diff);
425: queries[11].setQueryParams("LEFT_NR", destNr);
426: queries[11].setQueryParams("RIGHT_NR", maxRight);
427: queries[11].execute();
428:
429: // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF WHERE RIGHT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
430: // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
431: // CLASS: UpdateContentTreeSetRightNrPlus.class
432: // PARAMS: DIFF, LEFT_NR, RIGHT_NR
433: // [12]
434: queries[12].setQueryParams("DIFF", diff);
435: queries[12].setQueryParams("LEFT_NR", destNr);
436: queries[12].setQueryParams("RIGHT_NR", maxRight);
437: queries[12].execute();
438:
439: // SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT
440: int destVal = 0;
441: leftVal = 0; // defined above!
442: int maxVal = 0;
443: if (destNr != null && destNr instanceof Number) {
444: destVal = ((Number) destNr).intValue();
445: }
446: if (leftNr != null && leftNr instanceof Number) {
447: leftVal = ((Number) leftNr).intValue();
448: }
449: if (maxRight != null && maxRight instanceof Number) {
450: maxVal = ((Number) maxRight).intValue();
451: }
452: diff = new Integer(destVal - leftVal - maxVal);
453:
454: // Src Knoten an diese Stelle einfuegen
455:
456: // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF, LEFT_NR = LEFT_NR + @DIFF WHERE RIGHT_NR > @MAX_RIGHT
457: // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE RIGHT_NR > ?
458: // CLASS: UpdateContentTreeSetRightNrGreater.class
459: // PARAMS: DIFF (2x), RIGHT_NR
460: // [13]
461: queries[13].setQueryParams("DIFF", diff);
462: queries[13].setQueryParams("RIGHT_NR", maxRight);
463: queries[13].execute();
464:
465: // Und den neuen Parent des Src Knotens setzen
466: // IF (@UPORDOWN = 0)
467: if (upordown.intValue() == 0) {
468: // OLD: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = @DEST WHERE CONTENT_NODE_ID = @SRC
469: // NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = ? WHERE CONTENT_NODE_ID = ?
470: // CLASS: UpdateContentTreeSetContentNodeParent.class
471: // PARAMS: CONTENT_NODE_PARENT, CONTENT_NODE_ID
472: // [14]
473: queries[14].setQueryParams("CONTENT_NODE_PARENT",
474: dest);
475: queries[14].setQueryParams("CONTENT_NODE_ID", src);
476: queries[14].execute();
477: }
478: // END ELSE BEGIN
479: else {
480: // OLD: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = (SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST) WHERE CONTENT_NODE_ID = @SRC
481: // NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = (SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) WHERE CONTENT_NODE_ID = ?
482: // CLASS: UpdateContentTreeSetContentNodeParentSelect.class
483: // PARAMS: DEST, SRC
484: // [15]
485: queries[15].setQueryParams("DEST", dest);
486: queries[15].setQueryParams("SRC", src);
487: queries[15].execute();
488: }
489:
490: // END (of first if)
491: } // of first if
492:
493: if (isNotOpen) {
494: aTKDBConnection.commitTransaction(); // commit all changes
495: }
496:
497: } catch (Throwable t) {
498: TKDBManager.safeRollbackTransaction(t);
499: }
500: return hasResults();
501: }
502:
503: public void initQuery(Connection con) {
504: super.initQuery(con, isPrepared, paramOrder, types,
505: setRelevants, null);
506: }
507: }
|