001: package com.teamkonzept.webman.mainint.db.queries.postgresql;
002:
003: import java.sql.*;
004: import java.util.Enumeration;
005: import com.teamkonzept.db.*;
006: import com.teamkonzept.webman.mainint.db.queries.content.*;
007: import com.teamkonzept.webman.mainint.db.queries.CheckTableExistence;
008: import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
009:
010: /*
011: * TKDBContentTreeIsDependent
012: * Schaut ob abhaengigkeiten CONTENT_TREE zu DOCUMENT_CONTENT oder
013: * SITE_TREE
014: * Input: CONTENT_NODE_ID
015: * Output: "ISDEPENDENT" = 0 || 1
016: * @author
017: * @version
018: */
019: public class TKDBContentTreeIsDependent extends TKExtendedPrepQuery {
020:
021: public static final boolean ISPREPARED = true;
022: public static final String[] PARAMORDER = { "CONTENT_NODE_ID" };
023: public static final Object[][] TYPES = null;
024: public static final boolean[] SETRELEVANTS = { true };
025:
026: protected static Class[] queryClasses = {
027: CheckTableExistence.class, // [0]
028: CreateTempTableContentNodeId.class, // [1]
029: CreateTempTableSiteNodeId.class, // [2]
030: SelectLeftRightNrFromContentTree.class, // [3]
031: SelectAllContentNodeId.class, // [4]
032: InsertTempTableContentNodeId.class, // [5]
033: InsertTempTableSiteNodeId.class, // [6]
034: SelectSiteNodeId.class, // [7]
035: DropTempTableSiteNodeId.class, // [8]
036: DropTempTableContentNodeId.class, // [9]
037: };
038:
039: public boolean execute() {
040: try {
041: init(queryClasses); // init query objects
042:
043: boolean isNotOpen = aTKDBConnection.isAutoCommit();
044: if (isNotOpen) {
045: TKDBManager.beginTransaction(); // begin transaction
046: }
047:
048: // CLASS: CheckTableExistence.class
049: // PARAMS: TABLE_NAME
050: // [2]
051: queries[0].setQueryParams("TABLE_NAME", "webman_temp"); // check existence!!!
052: queries[0].execute();
053: ResultSet exist = queries[0].fetchResultSet();
054: if (exist == null || !exist.next()) {
055: // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
056: queries[1].execute();
057: }
058: queries[0].setQueryParams("TABLE_NAME", "webman_temp2");
059: queries[0].execute();
060: exist = queries[0].fetchResultSet();
061: if (exist == null || !exist.next()) {
062: /* CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP2
063: ( SITE_NODE_ID int null,
064: CONTENT_NODE_ID int null,
065: CONTENT_NODE_SHORTNAME VARCHAR(80) null,
066: CONTENT_NODE_TYPE int null,
067: DC_SITE_NODE_ID int null,
068: DC_SITE_NODE_DOC_IDX int null
069: ) ON COMMIT DELETE ROWS */
070: queries[2].execute();
071: }
072:
073: // SELECT @L_NR = LEFT_NR, @R_NR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?
074: queries[3].setQueryParams("NODE_ID", queryParams
075: .get("CONTENT_NODE_ID"));
076: queries[3].execute();
077: ResultSet rs = queries[3].fetchResultSet();
078: Object lnr = null;
079: Object rnr = null;
080: if (rs != null && rs.next()) {
081: lnr = rs.getObject(1);
082: rnr = rs.getObject(2);
083: }
084:
085: // SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR >= @L_NR AND RIGHT_NR <= @R_NR
086: queries[4].setQueryParams("LEFT_NR", lnr);
087: queries[4].setQueryParams("RIGHT_NR", rnr);
088: queries[4].execute();
089: rs = queries[4].fetchResultSet();
090: if (rs != null) {
091: // INSERT INTO #temp (CONTENT_NODE_ID) VALUES (?)
092: while (rs.next()) {
093: queries[5].setQueryParams("NODE_ID", rs
094: .getObject(1));
095: queries[5].execute();
096: }
097: }
098:
099: /* INSERT INTO WEBMAN_TEMP2
100: (SITE_NODE_ID,
101: CONTENT_NODE_ID,
102: CONTENT_NODE_SHORTNAME,
103: CONTENT_NODE_TYPE,
104: DC_SITE_NODE_ID,
105: DC_SITE_NODE_DOC_IDX)
106:
107: SELECT ST.SITE_NODE_ID,
108: CT.CONTENT_NODE_ID,
109: CT.CONTENT_NODE_SHORTNAME,
110: CT.CONTENT_NODE_TYPE,
111: DC.SITE_NODE_ID,
112: DC.SITE_NODE_DOC_IDX
113:
114: FROM SITE_TREE ST, DOCUMENT_CONTENT DC, CONTENT_TREE CT, WEBMAN_TEMP T
115:
116: WHERE T.CONTENT_NODE_ID = ST.PG_CONTENT_NODE (+)
117: AND T.CONTENT_NODE_ID = DC.CONTENT_NODE_ID (+)
118: AND T.CONTENT_NODE_ID = CT.CONTENT_NODE_ID (+)
119: */
120: queries[6].execute();
121:
122: /* SELECT T2.SITE_NODE_ID AS ST_SITE_NODE_ID,
123: T2.CONTENT_NODE_ID AS CT_CONTENT_NODE_ID,
124: T2.CONTENT_NODE_SHORTNAME AS CT_CONTENT_NODE_SHORTNAME,
125: T2.CONTENT_NODE_TYPE AS CT_CONTENT_NODE_TYPE,
126: SD.SITE_NODE_ID AS SD_SITE_NODE_ID,
127: SD.SITE_NODE_DOC_IDX AS SD_SITE_NODE_DOC_IDX,
128: SD.DOCUMENT_NAME AS SD_DOCUMENT_NAME,
129: SD.DOCUMENT_SHORTNAME AS SD_DOCUMENT_SHORTNAME
130:
131: FROM webman_temp2 T2, SITE_DOCUMENT SD
132:
133: WHERE T2.DC_SITE_NODE_ID = SD.SITE_NODE_ID(+)
134: AND T2.DC_SITE_NODE_DOC_IDX = SD.SITE_NODE_DOC_IDX (+)
135:
136: ORDER BY T2.CONTENT_NODE_SHORTNAME
137: */
138:
139: queries[7].execute();
140: addResult(queries[7].fetchResultSet());
141:
142: // drop temporary tables
143: queries[8].execute();
144: queries[9].execute();
145:
146: if (isNotOpen) {
147: aTKDBConnection.commitTransaction(); // commit all changes
148: }
149:
150: } catch (Throwable t) {
151: TKDBManager.safeRollbackTransaction(t);
152: }
153: return hasResults();
154: }
155:
156: public void initQuery(Connection con) {
157: super.initQuery(con, ISPREPARED, PARAMORDER, TYPES,
158: SETRELEVANTS, null);
159: }
160: }
|