|
1 package com.teamkonzept.webman.mainint.db.queries.sybase;
2
3 import com.teamkonzept.db.*;
4 import java.sql.*;
5
6
15 public class TKDBContentTreeMoveNode extends TKPrepQuery {
16
17 public final static boolean isPrepared =
18 true;
19
20 public final static String  [] paramOrder =
21 { "SRC_NODE_ID" ,"DEST_NODE_ID" ,"UPORDOWN"
22 };
23
24 public final static Object [][] paramTypes =
25 null;
26
27 public final static boolean[] setRelevants =
28 { 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 > 2 ) " +
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
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 "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 "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 "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 "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 "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 public void initQuery(Connection con) {
198 super.initQuery(
199 con,
200 isPrepared,
201 paramOrder,
202 paramTypes,
203 setRelevants,
204 sqlString );
205 }
206 }
207 |
Popular Tags |
Java API By Example, From Geeks To Geeks.