KickJava   Java API By Example, From Geeks To Geeks.

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


1 package com.teamkonzept.webman.mainint.db.queries.postgresql;
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 final boolean ISPREPARED = true;
23     public static final String JavaDoc[] PARAMORDER = { "CONTENT_NODE_ID" };
24     public static final Object JavaDoc[][] TYPES = null;
25     public static final 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", queryParams.get("CONTENT_NODE_ID"));
81         queries[3].execute();
82         ResultSet rs = queries[3].fetchResultSet();
83         Object JavaDoc lnr = null;
84         Object JavaDoc rnr = null;
85         if (rs != null && rs.next())
86         {
87         lnr = rs.getObject(1);
88         rnr = rs.getObject(2);
89         }
90
91         // SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
92 queries[4].setQueryParams("LEFT_NR", lnr);
93         queries[4].setQueryParams("RIGHT_NR", rnr);
94         queries[4].execute();
95         rs = queries[4].fetchResultSet();
96         if (rs != null)
97         {
98             // INSERT INTO #temp (CONTENT_NODE_ID) VALUES (?)
99 while (rs.next())
100             {
101                 queries[5].setQueryParams("NODE_ID", rs.getObject(1));
102                 queries[5].execute();
103             }
104         }
105
106         /* INSERT INTO WEBMAN_TEMP2
107            (SITE_NODE_ID,
108              CONTENT_NODE_ID,
109              CONTENT_NODE_SHORTNAME,
110              CONTENT_NODE_TYPE,
111              DC_SITE_NODE_ID,
112              DC_SITE_NODE_DOC_IDX)
113
114            SELECT ST.SITE_NODE_ID,
115              CT.CONTENT_NODE_ID,
116              CT.CONTENT_NODE_SHORTNAME,
117              CT.CONTENT_NODE_TYPE,
118              DC.SITE_NODE_ID,
119              DC.SITE_NODE_DOC_IDX
120
121            FROM SITE_TREE ST, DOCUMENT_CONTENT DC, CONTENT_TREE CT, WEBMAN_TEMP T
122
123            WHERE T.CONTENT_NODE_ID = ST.PG_CONTENT_NODE (+)
124              AND T.CONTENT_NODE_ID = DC.CONTENT_NODE_ID (+)
125              AND T.CONTENT_NODE_ID = CT.CONTENT_NODE_ID (+)
126         */
127         queries[6].execute();
128
129
130         /* SELECT T2.SITE_NODE_ID AS ST_SITE_NODE_ID,
131              T2.CONTENT_NODE_ID AS CT_CONTENT_NODE_ID,
132              T2.CONTENT_NODE_SHORTNAME AS CT_CONTENT_NODE_SHORTNAME,
133              T2.CONTENT_NODE_TYPE AS CT_CONTENT_NODE_TYPE,
134              SD.SITE_NODE_ID AS SD_SITE_NODE_ID,
135              SD.SITE_NODE_DOC_IDX AS SD_SITE_NODE_DOC_IDX,
136              SD.DOCUMENT_NAME AS SD_DOCUMENT_NAME,
137              SD.DOCUMENT_SHORTNAME AS SD_DOCUMENT_SHORTNAME
138
139            FROM webman_temp2 T2, SITE_DOCUMENT SD
140
141            WHERE T2.DC_SITE_NODE_ID = SD.SITE_NODE_ID(+)
142              AND T2.DC_SITE_NODE_DOC_IDX = SD.SITE_NODE_DOC_IDX (+)
143
144            ORDER BY T2.CONTENT_NODE_SHORTNAME
145          */
146
147         queries[7].execute();
148         addResult(queries[7].fetchResultSet());
149
150         // drop temporary tables
151 queries[8].execute();
152         queries[9].execute();
153
154         if (isNotOpen)
155         {
156         aTKDBConnection.commitTransaction(); // commit all changes
157 }
158
159     }
160     catch(Throwable JavaDoc t)
161     {
162         TKDBManager.safeRollbackTransaction(t);
163     }
164     return hasResults();
165     }
166
167     public void initQuery(Connection con)
168     {
169         super.initQuery(con,ISPREPARED, PARAMORDER, TYPES, SETRELEVANTS, null);
170     }
171 }
172
Popular Tags