001: package com.teamkonzept.webman.mainint.db.queries.oracle;
002:
003: import java.util.Enumeration;
004: import java.sql.*;
005: import java.io.*;
006: import com.teamkonzept.db.*; //import com.teamkonzept.db.atomic.content.*;
007: //import com.teamkonzept.db.atomic.CheckTableExistence;
008: import com.teamkonzept.webman.mainint.db.queries.oracle.helper.*;
009: import com.teamkonzept.webman.mainint.db.queries.content.*;
010: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
011: import com.teamkonzept.lib.TKNull;
012:
013: /*
014: * TKDBContentTreeGetOpen
015: * gibt eine teilweise geoeffneten Baum zurueck
016: * Input: NODE_ID des zu oefneten Knotens
017: * Output: CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME
018: * CONTENT_NODE_TYPE, CONTENT_FORM, CONTENT_NODE_PARENT, NODE_ID
019: * wenn die NODE_ID = CONTENT_NODE_ID dann ist dieser Knoten oeffenbar, aber geschlossen
020: * wenn die NODE_ID = NULL dann ist dieser ein Blatt
021: * wenn die NODE_ID = CONTENT_NODE_PARENT dann ist dieser Knoten oeffenbar und offen
022: */
023: public class TKDBContentTreeGetOpen extends TKExtendedPrepQuery {
024:
025: public final static boolean isPrepared = true;
026:
027: public final static String[] paramOrder = { "NODE_ID" };
028:
029: public final static Object[][] types = null;
030:
031: public final static boolean[] setRelevants = { true };
032:
033: /* public final static String sqlString =
034: "DECLARE @N_ID int " +
035: "DECLARE @LNR int " +
036: "DECLARE @RNR int " +
037:
038: "SELECT @N_ID = ? " +
039:
040: "IF (@N_ID IS NULL) " +
041: "BEGIN " +
042: "SELECT " +
043: " @N_ID = CONTENT_NODE_ID " +
044: "FROM " +
045: " CONTENT_TREE " +
046: "WHERE " +
047: " CONTENT_NODE_PARENT = NULL " +
048: "END " +
049:
050: "SELECT " +
051: " @LNR = LEFT_NR, " +
052: " @RNR = RIGHT_NR " +
053: "FROM " +
054: " CONTENT_TREE " +
055: "WHERE " +
056: " CONTENT_NODE_ID = @N_ID " +
057:
058: "SELECT " +
059: " CONTENT_NODE_ID " +
060: "INTO " +
061: " #temp " +
062: "FROM " +
063: " CONTENT_TREE " +
064: "WHERE " +
065: " LEFT_NR <= @LNR " +
066: "AND " +
067: " RIGHT_NR >= @RNR " +
068:
069: "CREATE TABLE " +
070: " #temp2 " +
071: " (CONTENT_NODE_ID int null, NODE_ID int null) " +
072:
073: "INSERT INTO " +
074: " #temp2 " +
075: "SELECT " +
076: " CONTENT_NODE_ID, CONTENT_NODE_ID " +
077: "FROM " +
078: " CONTENT_TREE " +
079: "WHERE " +
080: " CONTENT_NODE_PARENT IN " +
081: "( SELECT CONTENT_NODE_ID FROM #temp ) " +
082: "OR " +
083: " CONTENT_NODE_ID IN " +
084: "( SELECT CONTENT_NODE_ID FROM #temp ) " +
085:
086: "UPDATE " +
087: " #temp2 " +
088: "SET " +
089: " NODE_ID = CT.CONTENT_NODE_PARENT " +
090: "FROM " +
091: " #temp T, CONTENT_TREE CT " +
092: "WHERE " +
093: " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
094: "AND " +
095: " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID " +
096:
097: "UPDATE " +
098: " #temp2 " +
099: "SET " +
100: " NODE_ID = NULL " +
101: "FROM " +
102: " CONTENT_TREE CT " +
103: "WHERE " +
104: " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
105: "AND " +
106: " CT.CONTENT_NODE_TYPE > 2 " +
107:
108: "SELECT " +
109: " CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, " +
110: " CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_SHORTNAME, " +
111: " CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR, CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, " +
112: " T.NODE_ID " +
113: "FROM " +
114: " CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T " +
115: "WHERE " +
116: " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID " +
117: "ORDER BY " +
118: " CT.LEFT_NR " +
119:
120: "DROP TABLE #temp " +
121: "DROP TABLE #temp2 "; */
122:
123: protected static Class[] queryClasses = {
124: CheckTableExistence.class, // [0]
125: SelectContentNodeIdFromContentTree.class, // [1]
126: SelectLeftRightNrFromContentTree.class, // [2]
127: CreateTempTableContentNodeId.class, // [3]
128: CreateTempTableContentNodeIdNodeId.class, // [4]
129: InsertIntoTempSelectContentNodeIdLeftRightFromContentTree.class, // [5]
130: InsertTempTableContentNodeIdNodeId.class, // [6]
131: UpdateTempTableContentNodeIdNodeId.class, // [7]
132: UpdateTempTableContentNodeIdNodeId2.class, // [8]
133: SelectFromContentTreeContentInstanceTempTable.class, // [9]
134: };
135:
136: public boolean execute() {
137: try {
138: init(queryClasses); // init query objects
139:
140: // SELECT @N_ID = ?
141: Object nid = queryParams.get("NODE_ID");
142:
143: boolean isNotOpen = aTKDBConnection.isAutoCommit();
144:
145: if (isNotOpen) {
146: TKDBManager.beginTransaction(); // begin transaction
147: }
148:
149: // IF (@N_ID IS NULL)
150: if (nid == null || nid instanceof TKNull) {
151: // SELECT @N_ID = CONTENT_NODE_ID FROM CONTENT_TREE WHERE CONTENT_NODE_PARENT IS NULL
152: queries[1].execute();
153: ResultSet rs = queries[1].fetchResultSet();
154: if (rs != null && rs.next()) {
155: nid = rs.getObject(1);
156: }
157: }
158: // SELECT @LNR = LEFT_NR, @RNR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @N_ID
159: Object lnr = null;
160: Object rnr = null;
161: queries[2].setQueryParams("NODE_ID", nid);
162: queries[2].execute();
163: ResultSet rs = queries[2].fetchResultSet();
164: if (rs != null && rs.next()) {
165: lnr = rs.getObject(1);
166: rnr = rs.getObject(2);
167: }
168:
169: queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP"); // check existence!!!
170: queries[0].execute();
171: ResultSet exist = queries[0].fetchResultSet();
172: if (exist != null && exist.next()) {
173: } else {
174: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
175: queries[3].execute();
176: }
177:
178: queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP_2"); // check existence!!!
179: queries[0].execute();
180: exist = queries[0].fetchResultSet();
181: if (exist != null && exist.next()) {
182: } else {
183: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
184: queries[4].execute();
185: }
186:
187: // OLD:
188: // SELECT CONTENT_NODE_ID INTO #temp FROM CONTENT_TREE " +
189: // WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR " +
190:
191: // NEW:
192: // INSERT INTO WEBMAN_TEMP SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR
193: queries[5].setQueryParams("LEFT_NR", lnr);
194: queries[5].setQueryParams("RIGHT_NR", rnr);
195: queries[5].execute();
196:
197: // INSERT INTO #temp2 SELECT CONTENT_NODE_ID, CONTENT_NODE_ID FROM CONTENT_TREE
198: // WHERE CONTENT_NODE_PARENT IN ( SELECT CONTENT_NODE_ID FROM #temp )
199: // OR
200: // CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp )
201: queries[6].execute();
202:
203: // OLD:
204: // UPDATE #temp2 SET NODE_ID = CT.CONTENT_NODE_PARENT FROM
205: // #temp T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID
206: // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID
207: // NEW:
208: // UPDATE WEBMAN_TEMP_2 SET NODE_ID =
209: // (SELECT CT.CONTENT_NODE_PARENT FROM CONTENT_TREE CT, WEBMAN_TEMP T
210: // WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID
211: // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID)
212: // WHERE CONTENT_NODE_ID IN (SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP)
213: queries[7].execute();
214:
215: // OLD:
216: // UPDATE #temp2 SET NODE_ID = NULL FROM CONTENT_TREE CT
217: // WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1
218: // NEW: UPDATE WEBMAN_TEMP_2 SET NODE_ID =
219: // (SELECT NULL FROM CONTENT_TREE CT, WEBMAN_TEMP T WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1) WHERE CONTENT_NODE_ID IN (SELECT T.CONTENT_NODE_ID FROM WEBMAN_TEMP T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1)
220: queries[8].execute();
221: // SELECT CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, CT.CONTENT_NODE_NAME,
222: // CT.CONTENT_NODE_SHORTNAME, CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR,
223: // CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, T.NODE_ID
224: // FROM CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T
225: // WHERE
226: // CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID
227: // ORDER BY
228: // CT.LEFT_NR
229:
230: queries[9].execute();
231: addResult(queries[9].fetchResultSet());
232:
233: if (isNotOpen) {
234: aTKDBConnection.commitTransaction(); // commit all changes
235: }
236:
237: } catch (Throwable t) {
238: TKDBManager.safeRollbackTransaction(t);
239: }
240: return hasResults();
241: }
242:
243: public void initQuery(Connection con) {
244: super.initQuery(con, isPrepared, paramOrder, types,
245: setRelevants, null);
246: }
247: }
|