|
1
5 package com.teamkonzept.webman.mainint.db.queries.sybase;
6
7 import java.sql.*;
8
9 import com.teamkonzept.db.*;
10
11
18 public class TKDBSiteDocClone extends TKPrepQuery{
19
20 public final static boolean isPrepared =
21 true;
22
23 public final static String  [] paramOrder =
24 {
25 "SITE_NODE_ID", "NODE_ID", "NODE_DOC_IDX",
26 "PRESENTATION_ID",
27 "DOCUMENT_NAME", "DOCUMENT_SHORTNAME", "INHERITABLE",
28 "DOCUMENT_TYPE", "EXTERNAL_URL", "INHERIT_END_LEVEL", "INHERIT_BEGIN_LEVEL"
29 };
30
31 public final static Object [][] paramTypes =
32 {{"EXTERNAL_URL", new Integer (Types.VARCHAR)}};
33
34 public final static boolean[] setRelevants =
35 { true };
36
37 public final static String sqlString =
38 "DECLARE @NEWID int " +
39 "DECLARE @NEWIDX int " +
40 "DECLARE @NEWSID int " +
41 "DECLARE @OLDID int " +
42 "DECLARE @OLDIDX int " +
43 "DECLARE @DIFF int " +
44 "DECLARE @MAX_DIFF int " +
45 "DECLARE @I int " +
46
47 "SELECT @NEWID = ? " +
48 "SELECT @NEWSID = NULL " +
49 "SELECT @OLDID = ? " +
50 "SELECT @OLDIDX = ? " +
51
52 "CREATE TABLE #temp1 (SELECTION_ID int) " +
53 "CREATE TABLE #temp2 (DIST int, IDX int, ID int ) " +
54
55 "IF EXISTS ( " +
56 " SELECT 1 FROM SITE_DOCUMENT " +
57 " WHERE SITE_NODE_ID = @OLDID AND SITE_NODE_DOC_IDX = @OLDIDX " +
58 " ) " +
59 "BEGIN " +
60
61 "BEGIN TRANSACTION " +
62
63 "SELECT " +
64 " @NEWIDX=ISNULL(MAX(SITE_NODE_DOC_IDX)+1, 0) " +
65 "FROM " +
66 " SITE_DOCUMENT " +
67 "WHERE " +
68 " SITE_NODE_ID = @NEWID " +
69
70 "INSERT INTO " +
71 " SITE_DOCUMENT " +
72 " (SITE_NODE_ID, SITE_NODE_DOC_IDX, " +
73 " PRESENTATION_ID, " +
74 " DOCUMENT_NAME," +
75 " DOCUMENT_SHORTNAME, " +
76 " INHERITABLE, DOCUMENT_TYPE, EXTERNAL_URL, INHERIT_END_LEVEL, INHERIT_BEGIN_LEVEL) " +
77 "VALUES " +
78 " ( @NEWID, @NEWIDX, ?, ?, ?, ?, ?, ?, ?, ?) " +
79
80 "INSERT INTO " +
81 " DOCUMENT_REFERENCE " +
82 " (SRC_SITE_NODE_ID, SRC_SITE_NODE_DOC_IDX, " +
83 " SRC_PRESENTATION_COMPONENT_IDX, " +
84 " REFERENCE_TYPE, DEST_SITE_NODE_ID, " +
85 " DEST_SITE_NODE_DOC_SHORTNAME, SELECTION_TYPE, " +
86 " SELECTION_DATA ) " +
87 "SELECT " +
88 " @NEWID, " +
89 " @NEWIDX, " +
90 " SRC_PRESENTATION_COMPONENT_IDX, " +
91 " REFERENCE_TYPE, " +
92 " DEST_SITE_NODE_ID, " +
93 " DEST_SITE_NODE_DOC_SHORTNAME, " +
94 " SELECTION_TYPE, SELECTION_DATA " +
95 "FROM " +
96 " DOCUMENT_REFERENCE " +
97 "WHERE " +
98 " SRC_SITE_NODE_ID = @OLDID " +
99 "AND " +
100 " SRC_SITE_NODE_DOC_IDX = @OLDIDX " +
101
102
104 "INSERT INTO " +
105 " #temp2 " +
106 " (DIST, IDX, ID) " +
107 "SELECT " +
108 " COUNT(SRC_PRESENTATION_COMPONENT_IDX), SRC_PRESENTATION_COMPONENT_IDX, @NEWID " +
109 "FROM " +
110 " DOCUMENT_REFERENCE DR, SITE_TREE ST1, SITE_TREE ST2, SITE_TREE ST3 " +
111 "WHERE " +
112 " DR.REFERENCE_TYPE = 2 " +
113 "AND " +
114 " DR.SRC_SITE_NODE_ID = @NEWID " +
115 "AND " +
116 " DR.SRC_SITE_NODE_DOC_IDX = @NEWIDX " +
117 "AND " +
118 " DR.SRC_SITE_NODE_ID = ST1.SITE_NODE_ID " +
119 "AND " +
120 " DR.DEST_SITE_NODE_ID = ST3.SITE_NODE_ID " +
121 "AND " +
122 " ST2.LEFT_NR < ST1.LEFT_NR " +
123 "AND " +
124 " ST2.RIGHT_NR > ST1.RIGHT_NR " +
125 "AND " +
126 " ST2.LEFT_NR >= ST3.LEFT_NR " +
127 "AND " +
128 " ST2.RIGHT_NR <= ST3.RIGHT_NR " +
129 "GROUP BY " +
130 " SRC_PRESENTATION_COMPONENT_IDX " +
131
132 "SELECT @MAX_DIFF = ( SELECT MAX(DIST) FROM #temp2 ) " +
133 "SELECT @I = 1 " +
134
135 "WHILE (@I < @MAX_DIFF) " +
136 "BEGIN " +
137 "UPDATE " +
138 " #temp2 " +
139 "SET " +
140 " DIST = DIST - 1, " +
141 " ID = SITE_NODE_PARENT " +
142 "FROM " +
143 " SITE_TREE " +
144 "WHERE " +
145 " DIST > 0 " +
146 "AND " +
147 " ID = SITE_NODE_ID " +
148
149 "SELECT @I = @I + 1 " +
150 "END " +
151
152 "UPDATE " +
153 " DOCUMENT_REFERENCE " +
154 "SET " +
155 " DEST_SITE_NODE_ID = ID " +
156 "FROM " +
157 " #temp2 " +
158 "WHERE " +
159 " SRC_SITE_NODE_ID = @NEWID " +
160 "AND " +
161 " SRC_SITE_NODE_DOC_IDX = @NEWIDX " +
162 "AND " +
163 " SRC_PRESENTATION_COMPONENT_IDX = IDX " +
164
165
167 "INSERT INTO " +
168 " #temp1 " +
169 "SELECT " +
170 " SELECTION_ID " +
171 "FROM " +
172 " DOCUMENT_CONTENT " +
173 "WHERE " +
174 " SITE_NODE_ID = @OLDID " +
175 "AND " +
176 " SITE_NODE_DOC_IDX = @OLDIDX " +
177 "AND " +
178 " SELECTION_ID IS NOT NULL " +
179
180 "IF EXISTS ( SELECT 1 FROM #temp1 ) " +
181 "BEGIN " +
182 "SELECT " +
183 " @NEWSID = MAX(SELECTION_ID) " +
184 "FROM " +
185 " CONTENT_SELECTION " +
186
187 "SELECT " +
188 " @DIFF = @NEWSID - MIN(SELECTION_ID) + 1 " +
189 "FROM " +
190 " #temp1 "+
191
192 "INSERT INTO "+
193 " CONTENT_SELECTION " +
194 " (SELECTION_ID, SELECTION_TYPE, SELECTION_DATA) " +
195 "SELECT " +
196 " SELECTION_ID + @DIFF, SELECTION_TYPE, SELECTION_DATA " +
197 "FROM " +
198 " CONTENT_SELECTION " +
199 "WHERE " +
200 " SELECTION_ID IN (SELECT SELECTION_ID FROM #temp1) " +
201 "END " +
202
203 "INSERT INTO " +
204 " DOCUMENT_CONTENT " +
205 " (SITE_NODE_ID, SITE_NODE_DOC_IDX, " +
206 " PRESENTATION_COMPONENT_IDX, CONTENT_NODE_ID, " +
207 " SELECTION_ID ) " +
208 "SELECT " +
209 " @NEWID, " +
210 " @NEWIDX, " +
211 " PRESENTATION_COMPONENT_IDX, " +
212 " CONTENT_NODE_ID, " +
213 " SELECTION_ID + @DIFF " +
214 "FROM " +
215 " DOCUMENT_CONTENT " +
216 "WHERE " +
217 " SITE_NODE_ID = @OLDID " +
218 "AND " +
219 " SITE_NODE_DOC_IDX = @OLDIDX " +
220 "AND " +
221 " SELECTION_ID IS NOT NULL " +
222
223 "INSERT INTO " +
224 " DOCUMENT_CONTENT " +
225 " (SITE_NODE_ID, SITE_NODE_DOC_IDX, " +
226 " PRESENTATION_COMPONENT_IDX, CONTENT_NODE_ID ) " +
227 "SELECT " +
228 " @NEWID, " +
229 " @NEWIDX, " +
230 " PRESENTATION_COMPONENT_IDX, " +
231 " CONTENT_NODE_ID " +
232 "FROM " +
233 " DOCUMENT_CONTENT " +
234 "WHERE " +
235 " SITE_NODE_ID = @OLDID " +
236 "AND " +
237 " SITE_NODE_DOC_IDX = @OLDIDX " +
238 "AND " +
239 " SELECTION_ID IS NULL " +
240
241 "COMMIT TRANSACTION " +
242
243 "END " +
244
245 "DROP TABLE #temp1 " +
246 "DROP TABLE #temp2 " +
247
248 "SELECT " +
249 " * " +
250 "FROM " +
251 " SITE_DOCUMENT " +
252 "WHERE " +
253 " SITE_NODE_ID = @NEWID " +
254 "AND " +
255 " SITE_NODE_DOC_IDX = @NEWIDX ";
256
257 public void initQuery(Connection con) {
258 super.initQuery(
259 con,
260 isPrepared,
261 paramOrder,
262 paramTypes,
263 setRelevants,
264 sqlString );
265 }
266 }
267
268
269 |
Popular Tags |
Java API By Example, From Geeks To Geeks.