KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > postgresql > TKDBContentTreeDeleteNode


1 package com.teamkonzept.webman.mainint.db.queries.postgresql;
2
3 import com.teamkonzept.db.*;
4 import com.teamkonzept.webman.mainint.db.queries.content.*;
5 import com.teamkonzept.webman.db.TKWebmanDBManager;
6
7 import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
8 import com.teamkonzept.webman.mainint.db.queries.content.CreateTempTableInstanceId;
9 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContent;
10 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentAttribute;
11 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentAttributeValue;
12 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentNode;
13 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentInstance;
14 import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentTree;
15
16 import java.sql.*;
17
18 /**
19  * TKDBContentTreeDeleteNode
20  * Loescht einen Nodes und alle Seine Kinder, sowie alle
21  * Eintraege in den entspr. CONTENT Tabellen
22  * Input: CONTENT_NODE_ID
23  * Output: Keiner
24  *
25  * @author $Author: ralf $
26  * @version $Revision: 1.1.2.1 $
27  */
28 public class TKDBContentTreeDeleteNode extends TKExtendedPrepQuery
29 {
30     /** flag indicating a prepared query */
31     public final static boolean ISPREPARED = true;
32     /** order of params */
33     public final static String JavaDoc[] PARAMORDER = { "CONTENT_NODE_ID" };
34     /** type specification of parameters */
35     public final static Object JavaDoc[][] TYPES = null;
36
37     /** Resultset erwuenscht ? */
38     public final static boolean[] SETRELEVANTS = { false };
39     /** index of specific sub query */
40     private final static int TABLE_EXIST_QUERY = 0;
41
42
43     /** sub queries */
44     protected static Class JavaDoc[] queryClasses = {
45
46         CheckTableExistence.class, // [0]
47 CreateTempTableContentId.class, // [1]
48 CreateTempTableInstanceId.class, // [2]
49
50         SelectLeftRightNrFromContentTree.class, // [3]
51
52         InsertTempTable4InstanceId.class, // [4]
53 InsertTempTable3ContentId.class, // [5]
54
55         DeleteFromContentValue.class, // [6]
56 DeleteFromContentNode.class, // [7]
57 DeleteFromStructuredContent.class, // [8]
58 DeleteFromContentVersion.class, // [9]
59 DeleteFromContentInstance.class, // [10]
60 DeleteFromContentAttribute.class, // [11]
61 DeleteFromContentAttributeValue.class, // [12]
62 DeleteFromContent.class, // [13]
63 DeleteFromContentTree.class, // [14]
64
65         UpdateContentTreeRightNr.class, // [15]
66 UpdateContentTreeLeftNr.class, // [16]
67
68         DropTempTable3.class, // [17]
69 DropTempTable4.class // [18]
70
71     };
72
73     /**
74      * @see com.teamkonzept.db.TKExtendedPrepQuery#execute
75      */
76     public boolean execute()
77     {
78         int i = 1;
79     try
80     {
81         init(queryClasses); // init query objects
82
83
84         // SELECT @N_ID = ?
85 Integer JavaDoc nodeId = (Integer JavaDoc) queryParams.get("CONTENT_NODE_ID");
86
87         boolean isNotOpen = aTKDBConnection.isAutoCommit();
88         if (isNotOpen)
89         {
90         TKDBManager.beginTransaction(); // begin transaction
91 }
92
93         //create temporary tables WEBMAN_TEMP3 and WEBMAN_TEMP4
94
95         queries[TABLE_EXIST_QUERY].setQueryParams("TABLE_NAME",
96               TKWebmanDBManager.getDBVendor()==QueryConstants.POSTGRESQL?"webman_temp_3":"WEBMAN_TEMP_3"); // check existence!!!
97 queries[TABLE_EXIST_QUERY].execute();
98         ResultSet exist = queries[TABLE_EXIST_QUERY].fetchResultSet();
99         if (exist == null || !exist.next())
100         {
101           queries[i].execute(); // [1]
102 }
103
104         i++; // [2]
105 queries[TABLE_EXIST_QUERY].setQueryParams("TABLE_NAME",
106               TKWebmanDBManager.getDBVendor()==QueryConstants.POSTGRESQL?"webman_temp_4":"WEBMAN_TEMP_4"); // check existence!!!
107 queries[TABLE_EXIST_QUERY].execute();
108         exist = queries[TABLE_EXIST_QUERY].fetchResultSet();
109         if (exist == null || !exist.next())
110         {
111           queries[i].execute();
112         }
113         // OLD: SELECT @L_NR = LEFT_NR, @R_NR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
114 // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
115 // CLASS: SelectLeftRightNrFromContentTree.class
116 // PARAMS: NODE_ID
117 Object JavaDoc leftNr = null;
118         Object JavaDoc rightNr = null;
119         ResultSet rs = null;
120         i++; // [3]
121 queries[i].setQueryParams("NODE_ID", nodeId);
122         queries[i].execute();
123         rs = queries[i].fetchResultSet();
124         if (rs != null && rs.next())
125         {
126         leftNr = rs.getObject(1);
127         rightNr = rs.getObject(2);
128         }
129
130         // SELECT @DIFF = @R_NR - @L_NR + 1
131 int leftVal = 0;
132         int rightVal = 0;
133         if (leftNr != null && leftNr instanceof Number JavaDoc)
134         {
135         leftVal = ((Number JavaDoc) leftNr).intValue();
136         }
137         if (rightNr != null && rightNr instanceof Number JavaDoc)
138         {
139         rightVal = ((Number JavaDoc) rightNr).intValue();
140         }
141         Integer JavaDoc diff = new Integer JavaDoc(rightVal - leftVal + 1);
142
143         //INSERT INTO WEBMAN_TEMP4 (INSTANCE_ID) SELECT CI.INSTANCE_ID FROM CONTENT_TREE CT, CONTENT_INSTANCE CI
144 //WHERE CT.CONTENT_NODE_ID = CI.CONTENT_NODE_ID AND CT.LEFT_NR >= ? AND CT.RIGHT_NR <= ?
145 i++; // [4]
146 queries[i].setQueryParams("LEFT_ID",leftNr);
147         queries[i].setQueryParams("RIGHT_ID",rightNr);
148         queries[i].execute();
149
150         //INSERT INTO WEBMAN_TEMP3 (CONTENT_ID) SELECT C.CONTENT_ID FROM WEBMAN_TEMP4 T, CONTENT_VERSION CV, CONTENT C
151 //WHERE T.INSTANCE_ID = CV.INSTANCE_ID AND CV.CONTENT_ID = C.CONTENT_ID
152 i++; // [5]
153 queries[i].execute();
154
155         // OLD: DELETE FROM CONTENT_VALUE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
156 // NEW: DELETE FROM CONTENT_VALUE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
157 // CLASS: DeleteFromContentValue.class
158 // PARAMS: LEFT_NR, RIGHT_NR
159 i++; // [6]
160 queries[i].execute();
161
162         // OLD: DELETE FROM CONTENT_NODE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
163 // NEW: DELETE FROM CONTENT_NODE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
164 // CLASS: DeleteFromContentNode.class
165 // PARAMS: LEFT_NR, RIGHT_NR
166 i++; // [7]
167 queries[i].execute();
168
169         // OLD: DELETE FROM STRUCTURED_CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
170 // NEW: DELETE FROM STRUCTURED_CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
171 // CLASS: DeleteFromStructuredContent.class
172 // PARAMS: LEFT_NR, RIGHT_NR
173 i++; // [8]
174 queries[i].execute();
175
176         // OLD: DELETE FROM CONTENT_VERSION WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM #temp2)
177 // NEW: DELETE FROM CONTENT_VERSION WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM WEBMAN_TEMP_4)
178 // CLASS: DeleteFromContentVersion.class
179 // PARAMS: LEFT_NR, RIGHT_NR
180 i++; // [9]
181 queries[i].execute();
182
183         // OLD: DELETE FROM CONTENT_INSTANCE WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM #temp2)
184 // NEW: DELETE FROM CONTENT_INSTANCE WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM WEBMAN_TEMP_4)
185 // CLASS: DeleteFromContentInstance.class
186 // PARAMS: LEFT_NR, RIGHT_NR
187 i++; // [10]
188 queries[i].execute();
189
190         // CLASS: DeleteFromContentAttribute.class
191 i++; // [11]
192 queries[i].execute();
193
194         // CLASS: DeleteFromContentAttributeValue.class
195 i++; // [12]
196 queries[i].execute();
197
198         // OLD: DELETE FROM CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
199 // NEW: DELETE FROM CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
200 // CLASS: DeleteFromContent.class
201 // PARAMS: LEFT_NR, RIGHT_NR
202 i++; // [13]
203 queries[i].execute();
204
205         // OLD: DELETE FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
206 // NEW: DELETE FROM CONTENT_TREE WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
207 // CLASS: DeleteFromContentTree.class
208 // PARAMS: LEFT_NR, RIGHT_NR
209 i++; // [14]
210 queries[i].setQueryParams("LEFT_NR", leftNr);
211         queries[i].setQueryParams("RIGHT_NR", rightNr);
212         queries[i].execute();
213
214         // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - @DIFF WHERE RIGHT_NR > @R_NR
215 // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - ? WHERE RIGHT_NR > ?
216 // CLASS: UpdateContentTreeRightNr.class
217 // PARAMS: DIFF, RIGHT_NR
218 i++; // [15]
219 queries[i].setQueryParams("DIFF", diff);
220         queries[i].setQueryParams("RIGHT_NR", rightNr);
221         queries[i].execute();
222
223         // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - @DIFF WHERE LEFT_NR > @L_NR
224 // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - ? WHERE LEFT_NR > ?
225 // CLASS: UpdateContentTreeLeftNr.class
226 // PARAMS: DIFF, LEFT_NR
227 i++; // [16]
228 queries[i].setQueryParams("DIFF", diff);
229         queries[i].setQueryParams("LEFT_NR", leftNr);
230         queries[i].execute();
231
232         // drop 'temporary' tables WEBMAN_TEMP3 and WEBMAN_TEMP4
233 i++; // [17]
234 queries[i].execute();
235         i++; // [18]
236 queries[i].execute();
237
238         // COMMIT TRANSACTION
239
240         if (isNotOpen)
241         {
242         aTKDBConnection.commitTransaction(); // commit all changes
243 }
244
245     }
246     catch(Throwable JavaDoc t)
247     {
248         TKDBManager.safeRollbackTransaction(t);
249     }
250     return hasResults();
251     }
252
253     /**
254      * @see com.teamkonzept.db.TKQuery#initQuery
255      */
256     public void initQuery(Connection con)
257     {
258     super.initQuery(con,
259             ISPREPARED,
260             PARAMORDER, TYPES, SETRELEVANTS, null);
261     }
262 }
263
Popular Tags