001: package com.teamkonzept.webman.mainint.db.queries.postgresql;
002:
003: import java.util.Enumeration;
004: import java.sql.*;
005: import java.io.*;
006: import com.teamkonzept.db.*;
007: import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
008: import com.teamkonzept.webman.mainint.db.queries.content.*;
009: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
010: import com.teamkonzept.lib.TKNull;
011:
012: /*
013: * TKDBContentTreeGetOpen
014: * gibt eine teilweise geoeffneten Baum zurueck
015: * Input: NODE_ID des zu oefneten Knotens
016: * Output: CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME
017: * CONTENT_NODE_TYPE, CONTENT_FORM, CONTENT_NODE_PARENT, NODE_ID
018: * wenn die NODE_ID = CONTENT_NODE_ID dann ist dieser Knoten oeffenbar, aber geschlossen
019: * wenn die NODE_ID = NULL dann ist dieser ein Blatt
020: * wenn die NODE_ID = CONTENT_NODE_PARENT dann ist dieser Knoten oeffenbar und offen
021: *
022: * @author
023: * @version
024: */
025: public class TKDBContentTreeGetOpen extends TKExtendedPrepQuery {
026:
027: public final static boolean ISPREPARED = true;
028: public final static String[] PARAMORDER = { "NODE_ID" };
029: public final static Object[][] TYPES = null;
030: public final static boolean[] SETRELEVANTS = { true };
031:
032: private final static int QUERY_CHECK_EXISTENCE = 0;
033: private final static int QUERY_SELECT_CONTENT_NODE_ID = 1;
034: private final static int QUERY_SELECT_LEFT_RIGHT = 2;
035: private final static int QUERY_CREATE_TEMP_CONTENT_NODE_ID = 3;
036: private final static int QUERY_CREATE_TEMP_CONTENT_NODE_ID_NODE_ID = 4;
037: private final static int QUERY_INSERT_INTO_TEMP = 5;
038: private final static int QUERY_INSERT_TEMP_TABLE = 6;
039: private final static int QUERY_UPDATE_TEMP = 7;
040: private final static int QUERY_UPDATE_TEMP_SECOND = 8;
041: private final static int QUERY_SELECT_FROM_CONTENT = 9;
042: private final static int QUERY_DROP_TEMP_CONTENT_NODE_ID = 10;
043: private final static int QUERY_DROP_TEMP_CONTENT_NODE_ID_NODE_ID = 11;
044:
045: protected static Class[] queryClasses = {
046: CheckTableExistence.class, // [0]
047: SelectContentNodeIdFromContentTree.class, // [1]
048: SelectLeftRightNrFromContentTree.class, // [2]
049: CreateTempTableContentNodeId.class, // [3]
050: CreateTempTableContentNodeIdNodeId.class, // [4]
051: InsertIntoTempSelectContentNodeIdLeftRightFromContentTree.class, // [5]
052: InsertTempTableContentNodeIdNodeId.class, // [6]
053: UpdateTempTableContentNodeIdNodeId.class, // [7]
054: UpdateTempTableContentNodeIdNodeId2.class, // [8]
055: SelectFromContentTreeContentInstanceTempTable.class, // [9]
056: DropTempTableContentNodeId.class, // [10]
057: DropTempTableContentNodeIdNodeId.class // [11]
058: };
059:
060: public boolean execute() {
061: try {
062: init(queryClasses); // init query objects
063:
064: // SELECT @N_ID = ?
065: Object nid = queryParams.get("NODE_ID");
066:
067: boolean isNotOpen = aTKDBConnection.isAutoCommit();
068:
069: if (isNotOpen) {
070: TKDBManager.beginTransaction(); // begin transaction
071: }
072:
073: // IF (@N_ID IS NULL)
074: if (nid == null || nid instanceof TKNull) {
075: // SELECT @N_ID = CONTENT_NODE_ID FROM CONTENT_TREE WHERE CONTENT_NODE_PARENT IS NULL
076: queries[QUERY_SELECT_CONTENT_NODE_ID].execute();
077: ResultSet rs = queries[QUERY_SELECT_CONTENT_NODE_ID]
078: .fetchResultSet();
079: if (rs != null && rs.next()) {
080: nid = rs.getObject(1);
081: }
082: }
083: // SELECT @LNR = LEFT_NR, @RNR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @N_ID
084: Object lnr = null;
085: Object rnr = null;
086: queries[QUERY_SELECT_LEFT_RIGHT].setQueryParams("NODE_ID",
087: nid);
088: queries[QUERY_SELECT_LEFT_RIGHT].execute();
089: ResultSet rs = queries[QUERY_SELECT_LEFT_RIGHT]
090: .fetchResultSet();
091: if (rs != null && rs.next()) {
092: lnr = rs.getObject(1);
093: rnr = rs.getObject(2);
094: }
095:
096: queries[QUERY_CHECK_EXISTENCE].setQueryParams("TABLE_NAME",
097: "webman_temp"); // check existence!!!
098: queries[QUERY_CHECK_EXISTENCE].execute();
099: ResultSet exist = queries[QUERY_CHECK_EXISTENCE]
100: .fetchResultSet();
101: if (exist != null && exist.next()) {
102: queries[QUERY_DROP_TEMP_CONTENT_NODE_ID].execute();
103: }
104: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
105: queries[QUERY_CREATE_TEMP_CONTENT_NODE_ID].execute();
106:
107: queries[QUERY_CHECK_EXISTENCE].setQueryParams("TABLE_NAME",
108: "webman_temp_2"); // check existence!!!
109: queries[QUERY_CHECK_EXISTENCE].execute();
110: exist = queries[QUERY_CHECK_EXISTENCE].fetchResultSet();
111: if (exist != null && exist.next()) {
112: queries[QUERY_DROP_TEMP_CONTENT_NODE_ID_NODE_ID]
113: .execute();
114: }
115: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
116: queries[QUERY_CREATE_TEMP_CONTENT_NODE_ID_NODE_ID]
117: .execute();
118:
119: // OLD:
120: // SELECT CONTENT_NODE_ID INTO #temp FROM CONTENT_TREE " +
121: // WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR " +
122:
123: // NEW:
124: // INSERT INTO WEBMAN_TEMP SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR
125: queries[QUERY_INSERT_INTO_TEMP].setQueryParams("LEFT_NR",
126: lnr);
127: queries[QUERY_INSERT_INTO_TEMP].setQueryParams("RIGHT_NR",
128: rnr);
129: queries[QUERY_INSERT_INTO_TEMP].execute();
130:
131: // INSERT INTO #temp2 SELECT CONTENT_NODE_ID, CONTENT_NODE_ID FROM CONTENT_TREE
132: // WHERE CONTENT_NODE_PARENT IN ( SELECT CONTENT_NODE_ID FROM #temp )
133: // OR
134: // CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp )
135: queries[QUERY_INSERT_TEMP_TABLE].execute();
136:
137: // OLD:
138: // UPDATE #temp2 SET NODE_ID = CT.CONTENT_NODE_PARENT FROM
139: // #temp T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID
140: // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID
141: // NEW:
142: // UPDATE WEBMAN_TEMP_2 SET NODE_ID =
143: // (SELECT CT.CONTENT_NODE_PARENT FROM CONTENT_TREE CT, WEBMAN_TEMP T
144: // WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID
145: // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID)
146: // WHERE CONTENT_NODE_ID IN (SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP)
147: queries[QUERY_UPDATE_TEMP].execute();
148:
149: // OLD:
150: // UPDATE #temp2 SET NODE_ID = NULL FROM CONTENT_TREE CT
151: // WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1
152: // NEW: UPDATE WEBMAN_TEMP_2 SET NODE_ID =
153: // (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)
154: queries[QUERY_UPDATE_TEMP_SECOND].execute();
155: // SELECT CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, CT.CONTENT_NODE_NAME,
156: // CT.CONTENT_NODE_SHORTNAME, CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR,
157: // CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, T.NODE_ID
158: // FROM CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T
159: // WHERE
160: // CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID
161: // ORDER BY
162: // CT.LEFT_NR
163:
164: queries[QUERY_SELECT_FROM_CONTENT].execute();
165:
166: queries[QUERY_DROP_TEMP_CONTENT_NODE_ID].execute();
167: queries[QUERY_DROP_TEMP_CONTENT_NODE_ID_NODE_ID].execute();
168:
169: addResult(queries[QUERY_SELECT_FROM_CONTENT]
170: .fetchResultSet());
171:
172: if (isNotOpen) {
173: aTKDBConnection.commitTransaction(); // commit all changes
174: }
175:
176: } catch (Throwable t) {
177: TKDBManager.safeRollbackTransaction(t);
178: }
179: return hasResults();
180: }
181:
182: public void initQuery(Connection con) {
183: super.initQuery(con, ISPREPARED, PARAMORDER, TYPES,
184: SETRELEVANTS, null);
185: }
186: }
|