Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > TKDBContentTreeMoveNode


1 package com.teamkonzept.webman.mainint.db.queries;
2
3 import java.sql.*;
4 import com.teamkonzept.db.*;
5 import com.teamkonzept.webman.mainint.db.queries.content.*;
6 import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
7
8 /*
9  * TKDBContentTreeMoveNode
10  * Fuegt einen neuen Knoten ueber/unterhalb eines Nodes ein
11  * Input:
12  * SRC_NODE_ID (Knoten des zu verschiebenen Knotens )
13  * DEST_NODE_ID (Knoten vor/nach dem eingefuegt werden soll)
14  * UPORDOWN UP = 1 Down = -1
15  * Output:NIX
16  */
17 public class TKDBContentTreeMoveNode extends TKExtendedPrepQuery
18 {
19
20     public final static boolean isPrepared = true;
21
22     public final static String [] paramOrder =
23     { "SRC_NODE_ID", "DEST_NODE_ID", "UPORDOWN"
24     };
25
26     public final static Object [][] types = null;
27
28     public final static boolean[] setRelevants = { false };
29
30 /* public final static String sqlString =
31         "DECLARE @SRC INT " +
32         "DECLARE @DEST INT " +
33         "DECLARE @UPORDOWN INT " +
34
35         "DECLARE @SRC_L INT " +
36         "DECLARE @SRC_R INT " +
37
38         "DECLARE @DEST_NR INT " +
39         "DECLARE @DIFF INT " +
40         "DECLARE @MAX_RIGHT INT " +
41
42         "SELECT @SRC = ? " +
43         "SELECT @DEST = ? " +
44         "SELECT @UPORDOWN = ? " +
45
46         "SELECT " +
47             "@SRC_L = LEFT_NR, " +
48             "@SRC_R = RIGHT_NR " +
49         "FROM " +
50             "CONTENT_TREE " +
51         "WHERE " +
52             "CONTENT_NODE_ID = @SRC " +
53
54         "IF ( " +
55                 "(@SRC != @DEST) " +
56             "AND NOT " +
57                 "( " +
58                     "@UPORDOWN = 0 " +
59                 "AND " +
60                     "EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 1 ) " +
61                 ")" +
62             "AND NOT " +
63                 "EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST) " +
64         ")" +
65         "BEGIN " +
66
67         "BEGIN TRANSACTION " +
68
69         "SELECT " +
70             "@MAX_RIGHT = MAX(RIGHT_NR) " +
71         "FROM " +
72             "CONTENT_TREE " +
73
74         "SELECT " +
75             "@SRC_L = LEFT_NR, " +
76             "@SRC_R = RIGHT_NR " +
77         "FROM " +
78             "CONTENT_TREE " +
79         "WHERE " +
80             "CONTENT_NODE_ID = @SRC " +
81
82 // Den Teilbaum (der verschoben werden soll) erst mal ganz raus
83
84         "UPDATE " +
85             "CONTENT_TREE " +
86         "SET " +
87             "RIGHT_NR = RIGHT_NR + @MAX_RIGHT, " +
88             "LEFT_NR = LEFT_NR + @MAX_RIGHT " +
89         "WHERE " +
90             "LEFT_NR >= @SRC_L " +
91         "AND " +
92             "RIGHT_NR <= @SRC_R " +
93
94         "SELECT @DIFF = (@SRC_R - @SRC_L) + 1 " +
95
96 //Denn restlichen Baum wieder zurecht ruecken
97         "UPDATE " +
98             "CONTENT_TREE " +
99         "SET " +
100             "LEFT_NR = LEFT_NR - @DIFF " +
101         "WHERE " +
102             "LEFT_NR >= @SRC_L " +
103         "AND " +
104             "RIGHT_NR <= @MAX_RIGHT " +
105
106         "UPDATE " +
107             "CONTENT_TREE " +
108         "SET " +
109             "RIGHT_NR = RIGHT_NR - @DIFF " +
110         "WHERE " +
111             "RIGHT_NR >= @SRC_L " +
112         "AND " +
113             "RIGHT_NR <= @MAX_RIGHT " +
114
115 // Ziel ist vor oder hinter Zielknoten ?
116         "IF (@UPORDOWN = 1) " +
117         "BEGIN " +
118             "SELECT " +
119                 "@DEST_NR = LEFT_NR " +
120             "FROM " +
121                 "CONTENT_TREE " +
122             "WHERE " +
123                 "CONTENT_NODE_ID = @DEST " +
124         "END ELSE BEGIN " +
125             "IF (@UPORDOWN = -1) " +
126             "BEGIN " +
127                 "SELECT " +
128                     "@DEST_NR = RIGHT_NR + 1 " +
129                 "FROM " +
130                     "CONTENT_TREE " +
131                 "WHERE " +
132                     "CONTENT_NODE_ID = @DEST " +
133             "END ELSE BEGIN " +
134                 "SELECT " +
135                     "@DEST_NR = LEFT_NR + 1 " +
136                 "FROM " +
137                     "CONTENT_TREE " +
138                 "WHERE " +
139                     "CONTENT_NODE_ID = @DEST " +
140             "END " +
141         "END " +
142
143 // An der richtigen Stelle Platz schaffen
144         "UPDATE " +
145             "CONTENT_TREE " +
146         "SET " +
147             "LEFT_NR = LEFT_NR + @DIFF " +
148         "WHERE " +
149             "LEFT_NR >= @DEST_NR " +
150         "AND " +
151             "RIGHT_NR <= @MAX_RIGHT " +
152
153         "UPDATE " +
154             "CONTENT_TREE " +
155         "SET " +
156             "RIGHT_NR = RIGHT_NR + @DIFF " +
157         "WHERE " +
158             "RIGHT_NR >= @DEST_NR " +
159         "AND " +
160             "RIGHT_NR <= @MAX_RIGHT " +
161
162         "SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT " +
163
164 // Src Knoten an diese Stelle einfuegen
165         "UPDATE " +
166             "CONTENT_TREE " +
167         "SET " +
168             "RIGHT_NR = RIGHT_NR + @DIFF, " +
169             "LEFT_NR = LEFT_NR + @DIFF " +
170         "WHERE " +
171             "RIGHT_NR > @MAX_RIGHT " +
172
173 // Und den neuen Parent des Src Knotens setzen
174         "IF (@UPORDOWN = 0) " +
175         "BEGIN " +
176             "UPDATE " +
177                 "CONTENT_TREE " +
178             "SET " +
179                 "CONTENT_NODE_PARENT = @DEST " +
180             "WHERE " +
181                 "CONTENT_NODE_ID = @SRC " +
182         "END ELSE BEGIN " +
183             "UPDATE " +
184                 "CONTENT_TREE " +
185             "SET " +
186                 "CONTENT_NODE_PARENT = " +
187                 "(SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST) " +
188             "WHERE " +
189                 "CONTENT_NODE_ID = @SRC " +
190         "END " +
191
192         "COMMIT TRANSACTION " +
193
194         "END ";
195 */
196
197     protected static Class [] queryClasses = {
198     SelectLeftRightNrFromContentTree.class, // [0]
199 SelectOneFromContentTreeContentType.class, // [1]
200 SelectOneFromContentTreeLeftRightNr.class, // [2]
201 SelectMaxFromContentTree.class, // [3]
202 SelectLeftRightNrFromContentTree.class, // [4]
203 UpdateContentTreeLeftRightNr.class, // [5]
204 UpdateContentTreeSetLeftNr.class, // [6]
205 UpdateContentTreeSetRightNr.class, // [7]
206 SelectLeftNrFromContentTree.class, // [8]
207 SelectRightNrPlusOneFromContentTree.class, // [9]
208 SelectLeftNrPlusOneFromContentTree.class, // [10]
209 UpdateContentTreeSetLeftNrPlus.class, // [11]
210 UpdateContentTreeSetRightNrPlus.class, // [12]
211 UpdateContentTreeSetRightNrGreater.class, // [13]
212 UpdateContentTreeSetContentNodeParent.class, // [14]
213 UpdateContentTreeSetContentNodeParentSelect.class // [15]
214 };
215
216     public boolean execute()
217     {
218     try
219     {
220         init(queryClasses); // init query objects
221
222         boolean isNotOpen = aTKDBConnection.isAutoCommit();
223         if (isNotOpen)
224         {
225         TKDBManager.beginTransaction(); // begin transaction
226 }
227
228         // SELECT @SRC = ?
229 // SELECT @DEST = ?
230 // SELECT @UPORDOWN = ?
231 Integer src = (Integer ) queryParams.get("SRC_NODE_ID");
232         Integer dest = (Integer ) queryParams.get("DEST_NODE_ID");
233         Integer upordown = (Integer ) queryParams.get("UPORDOWN");
234
235         // OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
236 // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
237 // CLASS: SelectLeftRightNrFromContentTree.class
238 // PARAMS: NODE_ID
239 // [0]
240 queries[0].setQueryParams("NODE_ID", src);
241         queries[0].execute();
242         Integer leftNr = null;
243         Integer rightNr = null;
244         int left_native = 0;
245         int right_native = 0;
246         ResultSet rs0 = queries[0].fetchResultSet();
247         if (rs0 != null && rs0.next())
248         {
249         left_native = rs0.getInt(1);
250         right_native = rs0.getInt(2);
251         }
252         
253         leftNr = new Integer (left_native);
254         rightNr = new Integer (right_native);
255         
256
257         // OLD:
258 // IF ( (@SRC != @DEST)
259 // AND NOT (
260 // @UPORDOWN = 0
261 // AND
262 // EXISTS (SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST AND CONTENT_NODE_TYPE > 2 )
263 // )
264 // AND NOT
265 // EXISTS (SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND CONTENT_NODE_ID = @DEST)
266 // )
267 // NEW:
268 // SELECT 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ? AND CONTENT_NODE_TYPE > 2
269 // CLASS: SelectOneFromContentTreeContentType.class
270 // PARAMS: NODE_ID
271 // [1]
272 queries[1].setQueryParams("NODE_ID", dest);
273         queries[1].execute();
274         ResultSet rs1 = queries[1].fetchResultSet();
275         // SELECT 1 FROM CONTENT_TREE WHERE LEFT_NR > ? AND RIGHT_NR < ? AND CONTENT_NODE_ID = ?
276 // CLASS: SelectOneFromContentTreeLeftRightNr.class
277 // PARAMS: LEFT_NR, RIGHT_NR, NODE_ID
278 // [2]
279 queries[2].setQueryParams("LEFT_NR", leftNr);
280         queries[2].setQueryParams("RIGHT_NR", rightNr);
281         queries[2].setQueryParams("NODE_ID", dest);
282         queries[2].execute();
283         ResultSet rs2 = queries[2].fetchResultSet();
284         
285         boolean hasResult1 = rs1 != null && rs1.next();
286         boolean hasResult2 = rs2 != null && rs2.next();
287         
288 // if ((src.intValue() != dest.intValue())
289 // && !(upordown.intValue() == 0
290 // &&
291 // (rs1 != null && rs1.next()))
292 // && !((rs2 != null && rs2.next())))
293 if ((src.intValue() != dest.intValue())
294             && !(upordown.intValue() == 0 && hasResult1)
295             && !hasResult2)
296         {
297
298         // OLD: SELECT @MAX_RIGHT = MAX(RIGHT_NR) FROM CONTENT_TREE
299 // NEW: SELECT MAX(RIGHT_NR) FROM CONTENT_TREE
300 // CLASS: SelectMaxFromContentTree.class
301 // [3]
302 Object maxRight = null;
303         queries[3].execute();
304         ResultSet rs3 = queries[3].fetchResultSet();
305         if (rs3 != null && rs3.next())
306         {
307             maxRight = rs3.getObject(1);
308         }
309
310         // OLD: SELECT @SRC_L = LEFT_NR, @SRC_R = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @SRC
311 // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
312 // CLASS: SelectLeftRightNrFromContentTree.class
313 // PARAMS: NODE_ID
314 // [4]
315 queries[4].setQueryParams("NODE_ID", src);
316         queries[4].execute();
317         leftNr = null;
318         rightNr = null;
319         left_native = 0;
320         right_native = 0;
321         ResultSet rs4 = queries[4].fetchResultSet();
322         if (rs4 != null && rs4.next())
323         {
324             left_native = rs4.getInt(1);
325             right_native = rs4.getInt(2);
326         }
327         leftNr = new Integer (left_native);
328         rightNr = new Integer (right_native);
329
330         // Den Teilbaum (der verschoben werden soll) erst mal ganz raus
331
332         // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @MAX_RIGHT,
333 // LEFT_NR = LEFT_NR + @MAX_RIGHT
334 // WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @SRC_R
335 // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
336 // CLASS: UpdateContentTreeLeftRightNr.class
337 // PARAMS: MAX_RIGHT, LEFT_NR, RIGHT_NR
338 // [5]
339 queries[5].setQueryParams("MAX_RIGHT", maxRight);
340         queries[5].setQueryParams("LEFT_NR", leftNr);
341         queries[5].setQueryParams("RIGHT_NR", rightNr);
342         queries[5].execute();
343
344
345         // SELECT @DIFF = (@SRC_R - @SRC_L) + 1
346 int leftVal = 0;
347         int rightVal = 0;
348         if (leftNr != null)
349         {
350             leftVal = leftNr.intValue();
351         }
352         if (rightNr != null)
353         {
354             rightVal = rightNr.intValue();
355         }
356         Integer diff = new Integer (rightVal - leftVal + 1);
357
358         //Denn restlichen Baum wieder zurecht ruecken
359
360         // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - @DIFF WHERE LEFT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
361 // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
362 // CLASS: UpdateContentTreeSetLeftNr.class
363 // PARAMS: DIFF, LEFT_NR, RIGHT_NR
364 // [6]
365 queries[6].setQueryParams("DIFF", diff);
366         queries[6].setQueryParams("LEFT_NR", leftNr);
367         queries[6].setQueryParams("RIGHT_NR", maxRight);
368         queries[6].execute();
369
370         // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - @DIFF WHERE RIGHT_NR >= @SRC_L AND RIGHT_NR <= @MAX_RIGHT
371 // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
372 // CLASS: UpdateContentTreeSetRightNr.class
373 // PARAMS: DIFF, LEFT_NR, RIGHT_NR
374 // [7]
375 queries[7].setQueryParams("DIFF", diff);
376         queries[7].setQueryParams("LEFT_NR", leftNr);
377         queries[7].setQueryParams("RIGHT_NR", maxRight);
378         queries[7].execute();
379
380         // Ziel ist vor oder hinter Zielknoten ?
381 // OLD: IF (@UPORDOWN = 1)
382 Object destNr = null;
383         if (upordown.intValue() == 1)
384         {
385             // OLD: SELECT @DEST_NR = LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
386 // NEW: SELECT LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
387 // CLASS: SelectLeftNrFromContentTree.class
388 // PARAMS: NODE_ID
389 // [8]
390 queries[8].setQueryParams("NODE_ID", dest);
391             queries[8].execute();
392             ResultSet rs8 = queries[8].fetchResultSet();
393             //destNr = null;
394 if (rs8 != null && rs8.next())
395             {
396             destNr = rs8.getObject(1);
397             }
398         }
399         // OLD: END ELSE BEGIN
400 else
401         {
402             // OLD: IF (@UPORDOWN = -1)
403 if (upordown.intValue() == -1)
404             {
405             // OLD: SELECT @DEST_NR = RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
406 // NEW: SELECT RIGHT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
407 // CLASS: SelectRightNrPlusOneFromContentTree.class
408 // PARAMS: NODE_ID
409 // [9]
410 queries[9].setQueryParams("NODE_ID", dest);
411             queries[9].execute();
412             destNr = null;
413             ResultSet rs9 = queries[9].fetchResultSet();
414             if (rs9 != null && rs9.next())
415             {
416                 destNr = rs9.getObject(1);
417             }
418             }
419             // END ELSE BEGIN
420 else
421             {
422             // OLD: SELECT @DEST_NR = LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @DEST
423 // NEW: SELECT LEFT_NR + 1 FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
424 // CLASS: SelectLeftNrPlusOneFromContentTree.class
425 // PARAMS: NODE_ID
426 // [10]
427 queries[10].setQueryParams("NODE_ID", dest);
428             queries[10].execute();
429             destNr = null;
430             ResultSet rs10 = queries[10].fetchResultSet();
431             if (rs10 != null && rs10.next())
432             {
433                 destNr = rs10.getObject(1);
434             }
435             }
436         }
437
438         // An der richtigen Stelle Platz schaffen
439 // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + @DIFF WHERE LEFT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
440 // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR + ? WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
441 // CLASS: UpdateContentTreeSetLeftNrPlus.class
442 // PARAMS: DIFF, LEFT_NR, RIGHT_NR
443 // [11]
444 queries[11].setQueryParams("DIFF", diff);
445         queries[11].setQueryParams("LEFT_NR", destNr);
446         queries[11].setQueryParams("RIGHT_NR", maxRight);
447         queries[11].execute();
448
449         // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF WHERE RIGHT_NR >= @DEST_NR AND RIGHT_NR <= @MAX_RIGHT
450 // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ? WHERE RIGHT_NR >= ? AND RIGHT_NR <= ?
451 // CLASS: UpdateContentTreeSetRightNrPlus.class
452 // PARAMS: DIFF, LEFT_NR, RIGHT_NR
453 // [12]
454 queries[12].setQueryParams("DIFF", diff);
455         queries[12].setQueryParams("LEFT_NR", destNr);
456         queries[12].setQueryParams("RIGHT_NR", maxRight);
457         queries[12].execute();
458
459         // SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT
460 int destVal = 0;
461         leftVal = 0; // defined above!
462 int maxVal = 0;
463         if (destNr != null && destNr instanceof Number )
464         {
465             destVal = ((Number ) destNr).intValue();
466         }
467         if (leftNr != null && leftNr instanceof Number )
468         {
469             leftVal = ((Number ) leftNr).intValue();
470         }
471         if (maxRight != null && maxRight instanceof Number )
472         {
473             maxVal = ((Number ) maxRight).intValue();
474         }
475         diff = new Integer (destVal - leftVal - maxVal);
476
477         // Src Knoten an diese Stelle einfuegen
478
479         // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + @DIFF, LEFT_NR = LEFT_NR + @DIFF WHERE RIGHT_NR > @MAX_RIGHT
480 // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR + ?, LEFT_NR = LEFT_NR + ? WHERE RIGHT_NR > ?
481 // CLASS: UpdateContentTreeSetRightNrGreater.class
482 // PARAMS: DIFF (2x), RIGHT_NR
483 // [13]
484 queries[13].setQueryParams("DIFF", diff);
485         queries[13].setQueryParams("RIGHT_NR", maxRight);
486         queries[13].execute();
487
488         // Und den neuen Parent des Src Knotens setzen
489 // IF (@UPORDOWN = 0)
490 if (upordown.intValue() == 0)
491         {
492             // OLD: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = @DEST WHERE CONTENT_NODE_ID = @SRC
493 // NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = ? WHERE CONTENT_NODE_ID = ?
494 // CLASS: UpdateContentTreeSetContentNodeParent.class
495 // PARAMS: CONTENT_NODE_PARENT, CONTENT_NODE_ID
496 // [14]
497 queries[14].setQueryParams("CONTENT_NODE_PARENT", dest);
498             queries[14].setQueryParams("CONTENT_NODE_ID", src);
499             queries[14].execute();
500         }
501         // END ELSE BEGIN
502 else
503         {
504             // 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
505 // NEW: UPDATE CONTENT_TREE SET CONTENT_NODE_PARENT = (SELECT CONTENT_NODE_PARENT FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) WHERE CONTENT_NODE_ID = ?
506 // CLASS: UpdateContentTreeSetContentNodeParentSelect.class
507 // PARAMS: DEST, SRC
508 // [15]
509 queries[15].setQueryParams("DEST", dest);
510             queries[15].setQueryParams("SRC", src);
511             queries[15].execute();
512         }
513
514         // END (of first if)
515 } // of first if
516
517         if (isNotOpen)
518         {
519         aTKDBConnection.commitTransaction(); // commit all changes
520 }
521
522     }
523     catch(Throwable t)
524     {
525         TKDBManager.safeRollbackTransaction(t);
526     }
527     return hasResults();
528     }
529
530     public void initQuery(Connection con)
531     {
532     super.initQuery(con,
533             isPrepared, paramOrder, types, setRelevants, null);
534     }
535 }
536

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags