01: /*
02: * $Header: /cvsroot/webman-cms/source/webman/com/teamkonzept/webman/mainint/db/queries/content/GetMediaIDsAccContTree.java,v 1.1 2001/05/23 09:12:49 marwan Exp $
03: *
04: */
05: package com.teamkonzept.webman.mainint.db.queries.content;
06:
07: import java.sql.*;
08:
09: import com.teamkonzept.db.*;
10:
11: public class GetMediaIDsAccContTree extends TKPrepQuery implements
12: QueryConstants {
13:
14: public final static boolean isPrepared = true;
15:
16: public final static String[] paramOrder = { CONTENT_TREE_ID,
17: CONTENT_TREE_ID, CONTENT_TREE_ID, CONTENT_TREE_ID };
18:
19: public final static Object[][] paramTypes = null;
20:
21: public final static boolean[] setRelevants = { true };
22:
23: public final static String sqlString = " SELECT DISTINCT C_VAL.MEDIA_ID "
24: /* All MEDIA_IDs that are referenced from a subtree of the content tree */
25: + " FROM "
26: + " CONTENT_VALUE C_VAL, CONTENT_VERSION C_VER, CONTENT_INSTANCE C_I, CONTENT_TREE C_T "
27: + " WHERE "
28: + " C_VER.CONTENT_ID = C_VAL.CONTENT_ID "
29: + " AND "
30: + " C_I.INSTANCE_ID = C_VER.INSTANCE_ID "
31: + " AND "
32: + " C_I.CONTENT_NODE_ID = C_T.CONTENT_NODE_ID "
33: + " AND "
34: + " C_T.LEFT_NR >= (SELECT LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) "
35: + " AND "
36: + " C_T.RIGHT_NR <= (SELECT RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) "
37: /* Exclude those that are referenced from outside this subtree */
38: + " AND NOT EXISTS "
39: + " (SELECT DISTINCT C_VAL_IN.MEDIA_ID "
40: + " FROM CONTENT_VALUE C_VAL_IN, CONTENT_VERSION C_VER_IN, CONTENT_INSTANCE C_I_IN, CONTENT_TREE C_T_IN "
41: + " WHERE "
42: + " C_VER_IN.CONTENT_ID = C_VAL_IN.CONTENT_ID "
43: + " AND "
44: + " C_I_IN.INSTANCE_ID = C_VER_IN.INSTANCE_ID "
45: + " AND "
46: + " C_I_IN.CONTENT_NODE_ID = C_T_IN.CONTENT_NODE_ID "
47: + " AND NOT "
48: + " ( "
49: + " C_T_IN.LEFT_NR >= (SELECT LEFT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) "
50: + " AND "
51: + " C_T_IN.RIGHT_NR <= (SELECT RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = ?) "
52: + " ) "
53: + " AND C_VAL_IN.MEDIA_ID = C_VAL.MEDIA_ID "
54: + " ) "
55:
56: ;
57:
58: public void initQuery(Connection con) {
59: super.initQuery(con, isPrepared, paramOrder, paramTypes,
60: setRelevants, sqlString);
61: }
62: }
|