001: package com.teamkonzept.webman.mainint.db.queries.postgresql;
002:
003: import com.teamkonzept.db.*;
004: import com.teamkonzept.webman.mainint.db.queries.content.*;
005: import com.teamkonzept.webman.db.TKWebmanDBManager;
006:
007: import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
008: import com.teamkonzept.webman.mainint.db.queries.content.CreateTempTableInstanceId;
009: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContent;
010: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentAttribute;
011: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentAttributeValue;
012: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentNode;
013: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentInstance;
014: import com.teamkonzept.webman.mainint.db.queries.content.DeleteFromContentTree;
015:
016: import java.sql.*;
017:
018: /**
019: * TKDBContentTreeDeleteNode
020: * Loescht einen Nodes und alle Seine Kinder, sowie alle
021: * Eintraege in den entspr. CONTENT Tabellen
022: * Input: CONTENT_NODE_ID
023: * Output: Keiner
024: *
025: * @author $Author: ralf $
026: * @version $Revision: 1.1.2.1 $
027: */
028: public class TKDBContentTreeDeleteNode extends TKExtendedPrepQuery {
029: /** flag indicating a prepared query */
030: public final static boolean ISPREPARED = true;
031: /** order of params */
032: public final static String[] PARAMORDER = { "CONTENT_NODE_ID" };
033: /** type specification of parameters */
034: public final static Object[][] TYPES = null;
035:
036: /** Resultset erwuenscht ? */
037: public final static boolean[] SETRELEVANTS = { false };
038: /** index of specific sub query */
039: private final static int TABLE_EXIST_QUERY = 0;
040:
041: /** sub queries */
042: protected static Class[] queryClasses = {
043:
044: CheckTableExistence.class, // [0]
045: CreateTempTableContentId.class, // [1]
046: CreateTempTableInstanceId.class, // [2]
047:
048: SelectLeftRightNrFromContentTree.class, // [3]
049:
050: InsertTempTable4InstanceId.class, // [4]
051: InsertTempTable3ContentId.class, // [5]
052:
053: DeleteFromContentValue.class, // [6]
054: DeleteFromContentNode.class, // [7]
055: DeleteFromStructuredContent.class, // [8]
056: DeleteFromContentVersion.class, // [9]
057: DeleteFromContentInstance.class, // [10]
058: DeleteFromContentAttribute.class, // [11]
059: DeleteFromContentAttributeValue.class, // [12]
060: DeleteFromContent.class, // [13]
061: DeleteFromContentTree.class, // [14]
062:
063: UpdateContentTreeRightNr.class, // [15]
064: UpdateContentTreeLeftNr.class, // [16]
065:
066: DropTempTable3.class, // [17]
067: DropTempTable4.class // [18]
068:
069: };
070:
071: /**
072: * @see com.teamkonzept.db.TKExtendedPrepQuery#execute
073: */
074: public boolean execute() {
075: int i = 1;
076: try {
077: init(queryClasses); // init query objects
078:
079: // SELECT @N_ID = ?
080: Integer nodeId = (Integer) queryParams
081: .get("CONTENT_NODE_ID");
082:
083: boolean isNotOpen = aTKDBConnection.isAutoCommit();
084: if (isNotOpen) {
085: TKDBManager.beginTransaction(); // begin transaction
086: }
087:
088: //create temporary tables WEBMAN_TEMP3 and WEBMAN_TEMP4
089:
090: queries[TABLE_EXIST_QUERY]
091: .setQueryParams(
092: "TABLE_NAME",
093: TKWebmanDBManager.getDBVendor() == QueryConstants.POSTGRESQL ? "webman_temp_3"
094: : "WEBMAN_TEMP_3"); // check existence!!!
095: queries[TABLE_EXIST_QUERY].execute();
096: ResultSet exist = queries[TABLE_EXIST_QUERY]
097: .fetchResultSet();
098: if (exist == null || !exist.next()) {
099: queries[i].execute(); // [1]
100: }
101:
102: i++; // [2]
103: queries[TABLE_EXIST_QUERY]
104: .setQueryParams(
105: "TABLE_NAME",
106: TKWebmanDBManager.getDBVendor() == QueryConstants.POSTGRESQL ? "webman_temp_4"
107: : "WEBMAN_TEMP_4"); // check existence!!!
108: queries[TABLE_EXIST_QUERY].execute();
109: exist = queries[TABLE_EXIST_QUERY].fetchResultSet();
110: if (exist == null || !exist.next()) {
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 leftNr = null;
118: Object 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: leftNr = rs.getObject(1);
126: rightNr = rs.getObject(2);
127: }
128:
129: // SELECT @DIFF = @R_NR - @L_NR + 1
130: int leftVal = 0;
131: int rightVal = 0;
132: if (leftNr != null && leftNr instanceof Number) {
133: leftVal = ((Number) leftNr).intValue();
134: }
135: if (rightNr != null && rightNr instanceof Number) {
136: rightVal = ((Number) rightNr).intValue();
137: }
138: Integer diff = new Integer(rightVal - leftVal + 1);
139:
140: //INSERT INTO WEBMAN_TEMP4 (INSTANCE_ID) SELECT CI.INSTANCE_ID FROM CONTENT_TREE CT, CONTENT_INSTANCE CI
141: //WHERE CT.CONTENT_NODE_ID = CI.CONTENT_NODE_ID AND CT.LEFT_NR >= ? AND CT.RIGHT_NR <= ?
142: i++; // [4]
143: queries[i].setQueryParams("LEFT_ID", leftNr);
144: queries[i].setQueryParams("RIGHT_ID", rightNr);
145: queries[i].execute();
146:
147: //INSERT INTO WEBMAN_TEMP3 (CONTENT_ID) SELECT C.CONTENT_ID FROM WEBMAN_TEMP4 T, CONTENT_VERSION CV, CONTENT C
148: //WHERE T.INSTANCE_ID = CV.INSTANCE_ID AND CV.CONTENT_ID = C.CONTENT_ID
149: i++; // [5]
150: queries[i].execute();
151:
152: // OLD: DELETE FROM CONTENT_VALUE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
153: // NEW: DELETE FROM CONTENT_VALUE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
154: // CLASS: DeleteFromContentValue.class
155: // PARAMS: LEFT_NR, RIGHT_NR
156: i++; // [6]
157: queries[i].execute();
158:
159: // OLD: DELETE FROM CONTENT_NODE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
160: // NEW: DELETE FROM CONTENT_NODE WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
161: // CLASS: DeleteFromContentNode.class
162: // PARAMS: LEFT_NR, RIGHT_NR
163: i++; // [7]
164: queries[i].execute();
165:
166: // OLD: DELETE FROM STRUCTURED_CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
167: // NEW: DELETE FROM STRUCTURED_CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
168: // CLASS: DeleteFromStructuredContent.class
169: // PARAMS: LEFT_NR, RIGHT_NR
170: i++; // [8]
171: queries[i].execute();
172:
173: // OLD: DELETE FROM CONTENT_VERSION WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM #temp2)
174: // NEW: DELETE FROM CONTENT_VERSION WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM WEBMAN_TEMP_4)
175: // CLASS: DeleteFromContentVersion.class
176: // PARAMS: LEFT_NR, RIGHT_NR
177: i++; // [9]
178: queries[i].execute();
179:
180: // OLD: DELETE FROM CONTENT_INSTANCE WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM #temp2)
181: // NEW: DELETE FROM CONTENT_INSTANCE WHERE INSTANCE_ID IN (SELECT INSTANCE_ID FROM WEBMAN_TEMP_4)
182: // CLASS: DeleteFromContentInstance.class
183: // PARAMS: LEFT_NR, RIGHT_NR
184: i++; // [10]
185: queries[i].execute();
186:
187: // CLASS: DeleteFromContentAttribute.class
188: i++; // [11]
189: queries[i].execute();
190:
191: // CLASS: DeleteFromContentAttributeValue.class
192: i++; // [12]
193: queries[i].execute();
194:
195: // OLD: DELETE FROM CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM #temp)
196: // NEW: DELETE FROM CONTENT WHERE CONTENT_ID IN (SELECT CONTENT_ID FROM WEBMAN_TEMP_3)
197: // CLASS: DeleteFromContent.class
198: // PARAMS: LEFT_NR, RIGHT_NR
199: i++; // [13]
200: queries[i].execute();
201:
202: // OLD: DELETE FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
203: // NEW: DELETE FROM CONTENT_TREE WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
204: // CLASS: DeleteFromContentTree.class
205: // PARAMS: LEFT_NR, RIGHT_NR
206: i++; // [14]
207: queries[i].setQueryParams("LEFT_NR", leftNr);
208: queries[i].setQueryParams("RIGHT_NR", rightNr);
209: queries[i].execute();
210:
211: // OLD: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - @DIFF WHERE RIGHT_NR > @R_NR
212: // NEW: UPDATE CONTENT_TREE SET RIGHT_NR = RIGHT_NR - ? WHERE RIGHT_NR > ?
213: // CLASS: UpdateContentTreeRightNr.class
214: // PARAMS: DIFF, RIGHT_NR
215: i++; // [15]
216: queries[i].setQueryParams("DIFF", diff);
217: queries[i].setQueryParams("RIGHT_NR", rightNr);
218: queries[i].execute();
219:
220: // OLD: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - @DIFF WHERE LEFT_NR > @L_NR
221: // NEW: UPDATE CONTENT_TREE SET LEFT_NR = LEFT_NR - ? WHERE LEFT_NR > ?
222: // CLASS: UpdateContentTreeLeftNr.class
223: // PARAMS: DIFF, LEFT_NR
224: i++; // [16]
225: queries[i].setQueryParams("DIFF", diff);
226: queries[i].setQueryParams("LEFT_NR", leftNr);
227: queries[i].execute();
228:
229: // drop 'temporary' tables WEBMAN_TEMP3 and WEBMAN_TEMP4
230: i++; // [17]
231: queries[i].execute();
232: i++; // [18]
233: queries[i].execute();
234:
235: // COMMIT TRANSACTION
236:
237: if (isNotOpen) {
238: aTKDBConnection.commitTransaction(); // commit all changes
239: }
240:
241: } catch (Throwable t) {
242: TKDBManager.safeRollbackTransaction(t);
243: }
244: return hasResults();
245: }
246:
247: /**
248: * @see com.teamkonzept.db.TKQuery#initQuery
249: */
250: public void initQuery(Connection con) {
251: super.initQuery(con, ISPREPARED, PARAMORDER, TYPES,
252: SETRELEVANTS, null);
253: }
254: }
|