KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > oracle > TKDBContentTreeIsDependent


1 package com.teamkonzept.webman.mainint.db.queries.oracle;
2
3 import java.sql.*;
4 import java.util.Enumeration JavaDoc;
5 import com.teamkonzept.db.*;
6 import com.teamkonzept.webman.mainint.db.queries.content.*;
7 import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
8 import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
9
10 /*
11  * TKDBContentTreeIsDependent
12  * Schaut ob abhaengigkeiten CONTENT_TREE zu DOCUMENT_CONTENT oder
13  * SITE_TREE
14  * Input: CONTENT_NODE_ID
15  * Output: "ISDEPENDENT" = 0 || 1
16  * @author
17  * @version
18  */

19 public class TKDBContentTreeIsDependent extends TKExtendedPrepQuery
20 {
21
22     public static boolean isPrepared = true;
23     public static String JavaDoc[] paramOrder = { "CONTENT_NODE_ID" };
24     public static Object JavaDoc[][] types = null;
25     public static boolean[] setRelevants = { true };
26
27     protected static Class JavaDoc[] queryClasses = {
28       CheckTableExistence.class, // [0]
29
CreateTempTableContentNodeId.class, // [1]
30
CreateTempTableSiteNodeId.class, // [2]
31
SelectLeftRightNrFromContentTree.class, // [3]
32
SelectAllContentNodeId.class, // [4]
33
InsertTempTableContentNodeId.class, // [5]
34
InsertTempTableSiteNodeId.class, // [6]
35
SelectSiteNodeId.class // [7]
36
//DropTempTableSiteNodeId.class, // [8]
37
//DropTempTableContentNodeId.class, // [9]
38
};
39
40     public boolean execute()
41     {
42     try
43     {
44         init(queryClasses); // init query objects
45

46         boolean isNotOpen = aTKDBConnection.isAutoCommit();
47         if (isNotOpen)
48         {
49         TKDBManager.beginTransaction(); // begin transaction
50
}
51
52         // CLASS: CheckTableExistence.class
53
// PARAMS: TABLE_NAME
54
// [2]
55
queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP"); // check existence!!!
56
queries[0].execute();
57         ResultSet exist = queries[0].fetchResultSet();
58         if (exist == null || !exist.next())
59         {
60         // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
61
queries[1].execute();
62         }
63         queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP2");
64         queries[0].execute();
65         exist = queries[0].fetchResultSet();
66         if (exist == null || !exist.next())
67         {
68             /* CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP2
69                ( SITE_NODE_ID int null,
70                  CONTENT_NODE_ID int null,
71                  CONTENT_NODE_SHORTNAME VARCHAR(80) null,
72                  CONTENT_NODE_TYPE int null,
73                  DC_SITE_NODE_ID int null,
74                  DC_SITE_NODE_DOC_IDX int null
75                  ) ON COMMIT DELETE ROWS */

76             queries[2].execute();
77         }
78
79         // SELECT @L_NR = LEFT_NR, @R_NR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
80
queries[3].setQueryParams("NODE_ID",
81                       queryParams.get("CONTENT_NODE_ID"));
82         queries[3].execute();
83         ResultSet rs = queries[3].fetchResultSet();
84         Object JavaDoc lnr = null;
85         Object JavaDoc rnr = null;
86         if (rs != null && rs.next())
87         {
88         lnr = rs.getObject(1);
89         rnr = rs.getObject(2);
90         }
91
92         // SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
93
queries[4].setQueryParams("LEFT_NR", lnr);
94         queries[4].setQueryParams("RIGHT_NR", rnr);
95         queries[4].execute();
96         rs = queries[4].fetchResultSet();
97         if (rs != null)
98         {
99             // INSERT INTO #temp (CONTENT_NODE_ID) VALUES (?)
100
while (rs.next())
101             {
102                 queries[5].setQueryParams("NODE_ID", rs.getObject(1));
103                 queries[5].execute();
104             }
105         }
106
107         /* INSERT INTO WEBMAN_TEMP2
108            (SITE_NODE_ID,
109              CONTENT_NODE_ID,
110              CONTENT_NODE_SHORTNAME,
111              CONTENT_NODE_TYPE,
112              DC_SITE_NODE_ID,
113              DC_SITE_NODE_DOC_IDX)
114
115            SELECT ST.SITE_NODE_ID,
116              CT.CONTENT_NODE_ID,
117              CT.CONTENT_NODE_SHORTNAME,
118              CT.CONTENT_NODE_TYPE,
119              DC.SITE_NODE_ID,
120              DC.SITE_NODE_DOC_IDX
121
122            FROM SITE_TREE ST, DOCUMENT_CONTENT DC, CONTENT_TREE CT, WEBMAN_TEMP T
123
124            WHERE T.CONTENT_NODE_ID = ST.PG_CONTENT_NODE (+)
125              AND T.CONTENT_NODE_ID = DC.CONTENT_NODE_ID (+)
126              AND T.CONTENT_NODE_ID = CT.CONTENT_NODE_ID (+)
127         */

128         queries[6].execute();
129
130
131         /* SELECT T2.SITE_NODE_ID AS ST_SITE_NODE_ID,
132              T2.CONTENT_NODE_ID AS CT_CONTENT_NODE_ID,
133              T2.CONTENT_NODE_SHORTNAME AS CT_CONTENT_NODE_SHORTNAME,
134              T2.CONTENT_NODE_TYPE AS CT_CONTENT_NODE_TYPE,
135              SD.SITE_NODE_ID AS SD_SITE_NODE_ID,
136              SD.SITE_NODE_DOC_IDX AS SD_SITE_NODE_DOC_IDX,
137              SD.DOCUMENT_NAME AS SD_DOCUMENT_NAME,
138              SD.DOCUMENT_SHORTNAME AS SD_DOCUMENT_SHORTNAME
139
140            FROM webman_temp2 T2, SITE_DOCUMENT SD
141
142            WHERE T2.DC_SITE_NODE_ID = SD.SITE_NODE_ID(+)
143              AND T2.DC_SITE_NODE_DOC_IDX = SD.SITE_NODE_DOC_IDX (+)
144
145            ORDER BY T2.CONTENT_NODE_SHORTNAME
146          */

147
148         queries[7].execute();
149         addResult(queries[7].fetchResultSet());
150
151         // drop temporary tables
152
//queries[8].execute();
153
//queries[9].execute();
154

155         if (isNotOpen)
156         {
157         aTKDBConnection.commitTransaction(); // commit all changes
158
}
159
160     }
161     catch(Throwable JavaDoc t)
162     {
163         TKDBManager.safeRollbackTransaction(t);
164     }
165     return hasResults();
166     }
167
168     public void initQuery(Connection con)
169     {
170     super.initQuery(con,
171             isPrepared, paramOrder, types, setRelevants, null);
172     }
173 }
174
Popular Tags