|
1
5 package com.teamkonzept.webman.mainint.db.queries.sybase;
6
7 import java.sql.*;
8
9 import com.teamkonzept.db.*;
10
11
20 public class TKDBSiteTreeMoveNode extends TKPrepQuery{
21
22 public final static boolean isPrepared =
23 true;
24
25 public final static String  [] paramOrder =
26 { "SRC_NODE_ID" ,"DEST_NODE_ID" ,"UPORDOWN"
27 };
28
29 public final static Object [][] paramTypes =
30 null;
31
32 public final static boolean[] setRelevants =
33 { false };
34
35 public final static String sqlString =
36 "DECLARE @SRC INT " +
37 "DECLARE @DEST INT " +
38 "DECLARE @UPORDOWN INT " +
39
40 "DECLARE @SRC_L INT " +
41 "DECLARE @SRC_R INT " +
42
43 "DECLARE @DEST_NR INT " +
44 "DECLARE @DIFF INT " +
45 "DECLARE @MAX_RIGHT INT " +
46
47 "SELECT @SRC = ? " +
48 "SELECT @DEST = ? " +
49 "SELECT @UPORDOWN = ? " +
50
51 "SELECT " +
52 "@SRC_L = LEFT_NR, " +
53 "@SRC_R = RIGHT_NR " +
54 "FROM " +
55 "SITE_TREE " +
56 "WHERE " +
57 "SITE_NODE_ID = @SRC " +
58
59 "IF ( " +
60 "(@SRC != @DEST) " +
61 "AND NOT " +
62 "( " +
63 "@UPORDOWN = 0 " +
64 "AND " +
65 "EXISTS (SELECT 1 FROM SITE_TREE WHERE SITE_NODE_ID = @DEST AND SITE_NODE_TYPE < 3 ) " +
66 ")" +
67 "AND NOT " +
68 "EXISTS (SELECT 1 FROM SITE_TREE WHERE LEFT_NR > @SRC_L AND RIGHT_NR < @SRC_R AND SITE_NODE_ID = @DEST) " +
69 ")" +
70 "BEGIN " +
71
72 "BEGIN TRANSACTION " +
73
74 "SELECT " +
75 "@MAX_RIGHT = MAX(RIGHT_NR) " +
76 "FROM " +
77 "SITE_TREE " +
78
79 "SELECT " +
80 "@SRC_L = LEFT_NR, " +
81 "@SRC_R = RIGHT_NR " +
82 "FROM " +
83 "SITE_TREE " +
84 "WHERE " +
85 "SITE_NODE_ID = @SRC " +
86
87
89 "UPDATE " +
90 "SITE_TREE " +
91 "SET " +
92 "RIGHT_NR = RIGHT_NR + @MAX_RIGHT, " +
93 "LEFT_NR = LEFT_NR + @MAX_RIGHT " +
94 "WHERE " +
95 "LEFT_NR >= @SRC_L " +
96 "AND " +
97 "RIGHT_NR <= @SRC_R " +
98
99 "SELECT @DIFF = (@SRC_R - @SRC_L) + 1 " +
100
101 "UPDATE " +
103 "SITE_TREE " +
104 "SET " +
105 "LEFT_NR = LEFT_NR - @DIFF " +
106 "WHERE " +
107 "LEFT_NR >= @SRC_L " +
108 "AND " +
109 "RIGHT_NR <= @MAX_RIGHT " +
110
111 "UPDATE " +
112 "SITE_TREE " +
113 "SET " +
114 "RIGHT_NR = RIGHT_NR - @DIFF " +
115 "WHERE " +
116 "RIGHT_NR >= @SRC_L " +
117 "AND " +
118 "RIGHT_NR <= @MAX_RIGHT " +
119
120 "IF (@UPORDOWN = 1) " +
122 "BEGIN " +
123 "SELECT " +
124 "@DEST_NR = LEFT_NR " +
125 "FROM " +
126 "SITE_TREE " +
127 "WHERE " +
128 "SITE_NODE_ID = @DEST " +
129 "END ELSE BEGIN " +
130 "IF (@UPORDOWN = -1) " +
131 "BEGIN " +
132 "SELECT " +
133 "@DEST_NR = RIGHT_NR + 1 " +
134 "FROM " +
135 "SITE_TREE " +
136 "WHERE " +
137 "SITE_NODE_ID = @DEST " +
138 "END ELSE BEGIN " +
139 "SELECT " +
140 "@DEST_NR = LEFT_NR + 1 " +
141 "FROM " +
142 "SITE_TREE " +
143 "WHERE " +
144 "SITE_NODE_ID = @DEST " +
145 "END " +
146 "END " +
147
148 "UPDATE " +
150 "SITE_TREE " +
151 "SET " +
152 "LEFT_NR = LEFT_NR + @DIFF " +
153 "WHERE " +
154 "LEFT_NR >= @DEST_NR " +
155 "AND " +
156 "RIGHT_NR <= @MAX_RIGHT " +
157
158 "UPDATE " +
159 "SITE_TREE " +
160 "SET " +
161 "RIGHT_NR = RIGHT_NR + @DIFF " +
162 "WHERE " +
163 "RIGHT_NR >= @DEST_NR " +
164 "AND " +
165 "RIGHT_NR <= @MAX_RIGHT " +
166
167 "SELECT @DIFF = @DEST_NR - @SRC_L - @MAX_RIGHT " +
168
169 "UPDATE " +
171 "SITE_TREE " +
172 "SET " +
173 "RIGHT_NR = RIGHT_NR + @DIFF, " +
174 "LEFT_NR = LEFT_NR + @DIFF " +
175 "WHERE " +
176 "RIGHT_NR > @MAX_RIGHT " +
177
178 "IF (@UPORDOWN = 0) " +
180 "BEGIN " +
181 "UPDATE " +
182 "SITE_TREE " +
183 "SET " +
184 "SITE_NODE_PARENT = @DEST " +
185 "WHERE " +
186 "SITE_NODE_ID = @SRC " +
187 "END ELSE BEGIN " +
188 "UPDATE " +
189 "SITE_TREE " +
190 "SET " +
191 "SITE_NODE_PARENT = " +
192 "(SELECT SITE_NODE_PARENT FROM SITE_TREE WHERE SITE_NODE_ID = @DEST) " +
193 "WHERE " +
194 "SITE_NODE_ID = @SRC " +
195 "END " +
196
197 "COMMIT TRANSACTION " +
198
199 "END ";
200
201
202 public void initQuery(Connection con) {
203 super.initQuery(
204 con,
205 isPrepared,
206 paramOrder,
207 paramTypes,
208 setRelevants,
209 sqlString );
210 }
211 }
212
213 |
Popular Tags |
Java API By Example, From Geeks To Geeks.