KickJava   Java API By Example, From Geeks To Geeks.

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


1 package com.teamkonzept.webman.mainint.db.queries.postgresql;
2
3 import java.util.Enumeration JavaDoc;
4 import java.sql.*;
5 import java.io.*;
6 import com.teamkonzept.db.*;
7 import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
8 import com.teamkonzept.webman.mainint.db.queries.content.*;
9 import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
10 import com.teamkonzept.lib.TKNull;
11
12 /*
13  * TKDBContentTreeGetOpen
14  * gibt eine teilweise geoeffneten Baum zurueck
15  * Input: NODE_ID des zu oefneten Knotens
16  * Output: CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME
17  * CONTENT_NODE_TYPE, CONTENT_FORM, CONTENT_NODE_PARENT, NODE_ID
18  * wenn die NODE_ID = CONTENT_NODE_ID dann ist dieser Knoten oeffenbar, aber geschlossen
19  * wenn die NODE_ID = NULL dann ist dieser ein Blatt
20  * wenn die NODE_ID = CONTENT_NODE_PARENT dann ist dieser Knoten oeffenbar und offen
21  *
22  * @author
23  * @version
24  */
25 public class TKDBContentTreeGetOpen extends TKExtendedPrepQuery
26 {
27
28     public final static boolean ISPREPARED = true;
29     public final static String JavaDoc[] PARAMORDER = { "NODE_ID" };
30     public final static Object JavaDoc[][] TYPES = null;
31     public final static boolean[] SETRELEVANTS = { true };
32
33     private final static int QUERY_CHECK_EXISTENCE = 0;
34     private final static int QUERY_SELECT_CONTENT_NODE_ID = 1;
35     private final static int QUERY_SELECT_LEFT_RIGHT = 2;
36     private final static int QUERY_CREATE_TEMP_CONTENT_NODE_ID = 3;
37     private final static int QUERY_CREATE_TEMP_CONTENT_NODE_ID_NODE_ID = 4;
38     private final static int QUERY_INSERT_INTO_TEMP = 5;
39     private final static int QUERY_INSERT_TEMP_TABLE = 6;
40     private final static int QUERY_UPDATE_TEMP = 7;
41     private final static int QUERY_UPDATE_TEMP_SECOND = 8;
42     private final static int QUERY_SELECT_FROM_CONTENT = 9;
43     private final static int QUERY_DROP_TEMP_CONTENT_NODE_ID = 10;
44     private final static int QUERY_DROP_TEMP_CONTENT_NODE_ID_NODE_ID = 11;
45
46     protected static Class JavaDoc[] queryClasses = {
47     CheckTableExistence.class, // [0]
48 SelectContentNodeIdFromContentTree.class, // [1]
49 SelectLeftRightNrFromContentTree.class, // [2]
50 CreateTempTableContentNodeId.class, // [3]
51 CreateTempTableContentNodeIdNodeId.class, // [4]
52 InsertIntoTempSelectContentNodeIdLeftRightFromContentTree.class, // [5]
53 InsertTempTableContentNodeIdNodeId.class, // [6]
54 UpdateTempTableContentNodeIdNodeId.class, // [7]
55 UpdateTempTableContentNodeIdNodeId2.class, // [8]
56 SelectFromContentTreeContentInstanceTempTable.class, // [9]
57 DropTempTableContentNodeId.class, // [10]
58 DropTempTableContentNodeIdNodeId.class // [11]
59 };
60
61     public boolean execute()
62     {
63     try
64     {
65         init(queryClasses); // init query objects
66
67         // SELECT @N_ID = ?
68 Object JavaDoc nid = queryParams.get("NODE_ID");
69
70         boolean isNotOpen = aTKDBConnection.isAutoCommit();
71
72         if (isNotOpen)
73         {
74         TKDBManager.beginTransaction(); // begin transaction
75 }
76
77         // IF (@N_ID IS NULL)
78 if (nid == null || nid instanceof TKNull)
79         {
80         // SELECT @N_ID = CONTENT_NODE_ID FROM CONTENT_TREE WHERE CONTENT_NODE_PARENT IS NULL
81 queries[QUERY_SELECT_CONTENT_NODE_ID].execute();
82         ResultSet rs = queries[QUERY_SELECT_CONTENT_NODE_ID].fetchResultSet();
83         if (rs != null && rs.next())
84         {
85             nid = rs.getObject(1);
86         }
87         }
88         // SELECT @LNR = LEFT_NR, @RNR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @N_ID
89 Object JavaDoc lnr = null;
90         Object JavaDoc rnr = null;
91         queries[QUERY_SELECT_LEFT_RIGHT].setQueryParams("NODE_ID", nid);
92         queries[QUERY_SELECT_LEFT_RIGHT].execute();
93         ResultSet rs = queries[QUERY_SELECT_LEFT_RIGHT].fetchResultSet();
94         if (rs != null && rs.next())
95         {
96         lnr = rs.getObject(1);
97         rnr = rs.getObject(2);
98         }
99
100         queries[QUERY_CHECK_EXISTENCE].setQueryParams("TABLE_NAME", "webman_temp"); // check existence!!!
101 queries[QUERY_CHECK_EXISTENCE].execute();
102         ResultSet exist = queries[QUERY_CHECK_EXISTENCE].fetchResultSet();
103         if (exist != null && exist.next())
104         {
105             queries[QUERY_DROP_TEMP_CONTENT_NODE_ID].execute();
106         }
107         // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
108 queries[QUERY_CREATE_TEMP_CONTENT_NODE_ID].execute();
109
110         queries[QUERY_CHECK_EXISTENCE].setQueryParams("TABLE_NAME", "webman_temp_2"); // check existence!!!
111 queries[QUERY_CHECK_EXISTENCE].execute();
112         exist = queries[QUERY_CHECK_EXISTENCE].fetchResultSet();
113         if (exist != null && exist.next())
114         {
115             queries[QUERY_DROP_TEMP_CONTENT_NODE_ID_NODE_ID].execute();
116         }
117         // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
118 queries[QUERY_CREATE_TEMP_CONTENT_NODE_ID_NODE_ID].execute();
119
120         // OLD:
121 // SELECT CONTENT_NODE_ID INTO #temp FROM CONTENT_TREE " +
122 // WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR " +
123
124         // NEW:
125 // INSERT INTO WEBMAN_TEMP SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR
126 queries[QUERY_INSERT_INTO_TEMP].setQueryParams("LEFT_NR", lnr);
127         queries[QUERY_INSERT_INTO_TEMP].setQueryParams("RIGHT_NR", rnr);
128         queries[QUERY_INSERT_INTO_TEMP].execute();
129
130         // INSERT INTO #temp2 SELECT CONTENT_NODE_ID, CONTENT_NODE_ID FROM CONTENT_TREE
131 // WHERE CONTENT_NODE_PARENT IN ( SELECT CONTENT_NODE_ID FROM #temp )
132 // OR
133 // CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp )
134 queries[QUERY_INSERT_TEMP_TABLE].execute();
135
136         // OLD:
137 // UPDATE #temp2 SET NODE_ID = CT.CONTENT_NODE_PARENT FROM
138 // #temp T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID
139 // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID
140 // NEW:
141 // UPDATE WEBMAN_TEMP_2 SET NODE_ID =
142 // (SELECT CT.CONTENT_NODE_PARENT FROM CONTENT_TREE CT, WEBMAN_TEMP T
143 // WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID
144 // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID)
145 // WHERE CONTENT_NODE_ID IN (SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP)
146 queries[QUERY_UPDATE_TEMP].execute();
147
148         // OLD:
149 // UPDATE #temp2 SET NODE_ID = NULL FROM CONTENT_TREE CT
150 // WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1
151 // NEW: UPDATE WEBMAN_TEMP_2 SET NODE_ID =
152 // (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)
153 queries[QUERY_UPDATE_TEMP_SECOND].execute();
154         // SELECT CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, CT.CONTENT_NODE_NAME,
155 // CT.CONTENT_NODE_SHORTNAME, CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR,
156 // CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, T.NODE_ID
157 // FROM CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T
158 // WHERE
159 // CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID
160 // ORDER BY
161 // CT.LEFT_NR
162
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].fetchResultSet());
170
171         if (isNotOpen)
172         {
173         aTKDBConnection.commitTransaction(); // commit all changes
174 }
175
176     }
177     catch(Throwable JavaDoc t)
178     {
179         TKDBManager.safeRollbackTransaction(t);
180     }
181     return hasResults();
182     }
183
184     public void initQuery(Connection con)
185     {
186     super.initQuery(con,
187             ISPREPARED, PARAMORDER, TYPES, SETRELEVANTS, null);
188     }
189 }
190
Popular Tags