001: package com.teamkonzept.webman.mainint.db.queries.oracle;
002:
003: import java.sql.*;
004: import com.teamkonzept.db.*;
005: import com.teamkonzept.webman.mainint.db.queries.content.*;
006: import com.teamkonzept.webman.mainint.db.queries.oracle.helper.CheckTableExistence;
007: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
008:
009: /*
010: * TKDBContentTreeDeleteDependentDC
011: * loescht alle Eintraeg in DC die abhaengkeiten in CONTENT_TREE haben
012: * Input: CONTENT_NODE_ID
013: * Output: none
014: */
015: public class TKDBContentTreeDeleteDependentDC extends
016: TKExtendedPrepQuery {
017: public final static boolean isPrepared = true;
018:
019: public final static String[] paramOrder = { "CONTENT_NODE_ID" };
020:
021: public final static Object[][] types = null;
022:
023: public final static boolean[] setRelevants = { false };
024:
025: /* public final static String sqlString =
026: "DECLARE @L_NR INT " +
027: "DECLARE @R_NR INT " +
028:
029:
030: "CREATE TABLE #temp " +
031: "( " +
032: " CONTENT_NODE_ID int " +
033: ") " +
034:
035: "BEGIN TRANSACTION " +
036:
037: "SELECT " +
038: " @L_NR = LEFT_NR, @R_NR = RIGHT_NR " +
039: "FROM " +
040: " CONTENT_TREE " +
041: "WHERE " +
042: " CONTENT_NODE_ID = ? " +
043:
044:
045: "INSERT INTO " +
046: " #temp " +
047: " (CONTENT_NODE_ID) " +
048: "SELECT " +
049: " (CONTENT_NODE_ID) " +
050: "FROM " +
051: " CONTENT_TREE " +
052: "WHERE " +
053: " LEFT_NR >= @L_NR " +
054: "AND " +
055: " RIGHT_NR <= @R_NR " +
056:
057: "DELETE FROM " +
058: " CONTENT_SELECTION " +
059: "WHERE " +
060: " SELECTION_ID IN ( " +
061: "SELECT " +
062: " SELECTION_ID " +
063: "FROM " +
064: " DOCUMENT_CONTENT " +
065: "WHERE " +
066: " CONTENT_NODE_ID IN ( " +
067: "SELECT " +
068: " CONTENT_NODE_ID " +
069: "FROM " +
070: " #temp " +
071: ") " +
072: "AND " +
073: " SELECTION_ID IS NOT NULL " +
074: ") " +
075:
076: "DELETE FROM " +
077: " DOCUMENT_CONTENT " +
078: "WHERE " +
079: " CONTENT_NODE_ID IN ( " +
080: "SELECT " +
081: " CONTENT_NODE_ID " +
082: "FROM " +
083: " #temp " +
084: ") " +
085:
086: "COMMIT TRANSACTION " +
087:
088: "DROP TABLE #temp "; */
089:
090: protected static Class[] queryClasses = {
091: CheckTableExistence.class, // [0]
092: CreateTempTableContentNodeId.class, // [1]
093: SelectLeftRightNrFromContentTree.class, // [2]
094: InsertIntoTempTableContentNodeId.class, // [3]
095: DeleteFromContentSelection2.class, // [4]
096: DeleteFromDocumentContent2.class // [5]
097: };
098:
099: public boolean execute() {
100: try {
101:
102: init(queryClasses); // init query objects
103:
104: Object contentNodeId = queryParams.get("CONTENT_NODE_ID");
105:
106: boolean isNotOpen = aTKDBConnection.isAutoCommit();
107: if (isNotOpen) {
108: TKDBManager.beginTransaction(); // begin transaction
109: }
110:
111: // CLASS: CheckTableExistence.class
112: // PARAMS: TABLE_NAME (=WEBMAN_TEMP)
113: // [0]
114: queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP"); // check existence!!!
115: queries[0].execute();
116: ResultSet exist = queries[0].fetchResultSet();
117: if (exist != null && exist.next()) {
118: } else {
119: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
120: queries[1].execute();
121: }
122:
123: // OLD: SELECT @L_NR = LEFT_NR, @R_NR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
124: // NEW: SELECT LEFT_NR, RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
125: // CLASS: SelectLeftRightNrFromContentTree.class
126: // PARAMS: NODE_ID
127: // [2]
128: queries[2].setQueryParams("NODE_ID", contentNodeId);
129: queries[2].execute();
130: ResultSet rs2 = queries[2].fetchResultSet();
131: Object leftNr = null;
132: Object rightNr = null;
133: if (rs2 != null && rs2.next()) {
134: leftNr = rs2.getObject(1);
135: rightNr = rs2.getObject(2);
136: }
137:
138: // OLD: INSERT INTO #temp (CONTENT_NODE_ID) SELECT (CONTENT_NODE_ID) FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
139: // NEW: INSERT INTO WEBMAN_TEMP SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR >= ? AND RIGHT_NR <= ?
140: // CLASS: InsertTempTable1ContentNodeId.class
141: // PARAMS: LEFT_NR, RIGHT_NR
142: // [3]
143: queries[3].setQueryParams("LEFT_NR", leftNr);
144: queries[3].setQueryParams("RIGHT_NR", rightNr);
145: queries[3].execute();
146:
147: // OLD: DELETE FROM CONTENT_SELECTION WHERE SELECTION_ID IN ( SELECT SELECTION_ID FROM DOCUMENT_CONTENT WHERE CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp ) AND SELECTION_ID IS NOT NULL )
148: // NEW: DELETE FROM CONTENT_SELECTION WHERE SELECTION_ID IN ( SELECT SELECTION_ID FROM DOCUMENT_CONTENT WHERE CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP ) AND SELECTION_ID IS NOT NULL )
149: // CLASS: DeleteFromContentSelection2.class
150: // [4]
151: queries[4].execute();
152:
153: // OLD: DELETE FROM DOCUMENT_CONTENT WHERE CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp )
154: // NEW: DELETE FROM DOCUMENT_CONTENT WHERE CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP )
155: // CLASS: DeleteFromDocumentContent2.class
156: // [5]
157: queries[5].execute();
158:
159: // COMMIT TRANSACTION " +
160: if (isNotOpen) {
161: aTKDBConnection.commitTransaction(); // commit all changes
162: }
163:
164: } catch (Throwable t) {
165: TKDBManager.safeRollbackTransaction(t);
166: }
167: return hasResults();
168: }
169:
170: public void initQuery(Connection con) {
171: super.initQuery(con, isPrepared, paramOrder, types,
172: setRelevants, null);
173: }
174: }
|