KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > sybase > TKDBSiteDocGetHeretable


1 package com.teamkonzept.webman.mainint.db.queries.sybase;
2
3 import java.sql.*;
4
5 import com.teamkonzept.db.*;
6
7 /**
8  * Diese Query ist Sybase spezifisch!
9  *
10  * TKDBSiteDocGetHeretable
11  * Alle Parents zum Doc die vererbt wurden
12  * input SITE_NODE_ID
13  * ouput alle DS aus SITEDOCUMENT
14  * order by LEFT_NR
15  * @author $Author: uli $
16  * @version $Revision: 1.10 $
17  */
18 public class TKDBSiteDocGetHeretable
19     extends TKPrepQuery
20 {
21     // $Id: TKDBSiteDocGetHeretable.java,v 1.10 2002/02/16 15:49:47 uli Exp $
22
23     /**
24      * The preparation state of the query.
25      */
26     public final static boolean IS_PREPARED = true;
27
28     /**
29      * The parameter order of the query.
30      */
31     public final static String JavaDoc[] PARAMETER_ORDER = {"SITE_NODE_ID", "SITE_NODE_ID", "SITE_NODE_ID", "SITE_NODE_ID"};
32
33     /**
34      * The parameter types of the query.
35      */
36     public final static Object JavaDoc[][] PARAMETER_TYPES = null;
37
38     /**
39      * The set relevance state of the query.
40      */
41     public final static boolean[] SET_RELEVANTS = {true};
42
43     /**
44      * The literal SQL statement of the query.
45      */
46     public final static String JavaDoc SQL_STRING =
47          " CREATE TABLE #PATH ( SITE_NODE_ID int, "
48          + " LEFT_NR int, "
49          + " RIGHT_NR int, "
50          + " SITE_NODE_DOC_IDX int null, "
51          + " PRESENTATION_ID int null, "
52          + " DOCUMENT_NAME varchar(254) null, "
53          + " DOCUMENT_SHORTNAME varchar(80) null, "
54          + " INHERITABLE int null, "
55          + " DOCUMENT_TYPE int null, "
56          + " EXTERNAL_URL varchar(254) null, "
57          + " INHERIT_BEGIN_LEVEL int null, "
58          + " INHERIT_END_LEVEL int null) "
59
60          + " INSERT INTO #PATH "
61          + " SELECT S_T.SITE_NODE_ID, S_T.LEFT_NR, S_T.RIGHT_NR, S_D.SITE_NODE_DOC_IDX, S_D.PRESENTATION_ID, S_D.DOCUMENT_NAME, S_D.DOCUMENT_SHORTNAME, S_D.INHERITABLE, S_D.DOCUMENT_TYPE, S_D.EXTERNAL_URL, S_D.INHERIT_BEGIN_LEVEL, S_D.INHERIT_END_LEVEL "
62          + " FROM SITE_TREE S_T, SITE_DOCUMENT S_D "
63          + " WHERE S_T.SITE_NODE_ID *= S_D.SITE_NODE_ID "
64          + " AND "
65          + " LEFT_NR <= (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = ? ) " /* SITE_NODE_ID */
66          + " AND "
67          + " RIGHT_NR >= (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = ? ) " /* SITE_NODE_ID */
68
69          + " SELECT OUTER.*, P.PRESENTATION_NAME FROM #PATH OUTER, PRESENTATION P "
70         /* Exclude overwritten documents */
71          + " WHERE NOT EXISTS "
72          + " ( "
73          + " SELECT 1 "
74          + " FROM #PATH INNER "
75          + " WHERE INNER.DOCUMENT_SHORTNAME = OUTER.DOCUMENT_SHORTNAME "
76          + " AND "
77          + " INNER.LEFT_NR > OUTER.LEFT_NR "
78          + " ) "
79         /* Check distance */
80          + " AND INHERIT_BEGIN_LEVEL <= "
81          + " ( "
82          + " SELECT COUNT(DISTINCT SITE_NODE_ID) "
83          + " FROM #PATH COUNTER "
84          + " WHERE COUNTER.LEFT_NR > OUTER.LEFT_NR "
85          + " ) "
86          + " AND ( "
87          + " INHERIT_END_LEVEL = -1 "
88          + " OR "
89          + " INHERIT_END_LEVEL >= "
90          + " ( "
91          + " SELECT COUNT(DISTINCT SITE_NODE_ID) "
92          + " FROM #PATH COUNTER "
93          + " WHERE COUNTER.LEFT_NR > OUTER.LEFT_NR "
94          + " ) "
95
96          + " ) "
97          + " AND OUTER.PRESENTATION_ID *= P.PRESENTATION_ID "
98          + " AND "
99          + " LEFT_NR < (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = ? ) " /* SITE_NODE_ID */
100          + " AND "
101          + " RIGHT_NR > (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = ? ) " /* SITE_NODE_ID */
102
103          + " DROP TABLE #PATH "
104          ;
105
106
107     public void initQuery (Connection connection)
108     {
109         super.initQuery(connection,
110                         IS_PREPARED,
111                         PARAMETER_ORDER,
112                         PARAMETER_TYPES,
113                         SET_RELEVANTS,
114                         SQL_STRING);
115     }
116
117 }
118
119 /* CREATE TABLE #PATH ( SITE_NODE_ID int, LEFT_NR int, RIGHT_NR int,SITE_NODE_DOC_IDX int null,PRESENTATION_ID int null,DOCUMENT_NAME varchar(254) null,DOCUMENT_SHORTNAME varchar(80) null,INHERITABLE int null,DOCUMENT_TYPE int null,EXTERNAL_URL varchar(254) null,INHERIT_BEGIN_LEVEL int null,INHERIT_END_LEVEL int null)
120
121          INSERT INTO #PATH SELECT S_T.SITE_NODE_ID, S_T.LEFT_NR, S_T.RIGHT_NR, S_D.SITE_NODE_DOC_IDX, S_D.PRESENTATION_ID, S_D.DOCUMENT_NAME, S_D.DOCUMENT_SHORTNAME, S_D.INHERITABLE, S_D.DOCUMENT_TYPE, S_D.EXTERNAL_URL, S_D.INHERIT_BEGIN_LEVEL, S_D.INHERIT_END_LEVEL FROM SITE_TREE S_T, SITE_DOCUMENT S_D WHERE S_T.SITE_NODE_ID *= S_D.SITE_NODE_ID AND LEFT_NR < (SELECT LEFT_NR FROM SITE_TREE WHERE SITE_NODE_ID = 2 ) AND RIGHT_NR > (SELECT RIGHT_NR FROM SITE_TREE WHERE SITE_NODE_ID = 2 )
122
123           SELECT OUTER.*, P.PRESENTATION_NAME FROM #PATH OUTER, PRESENTATION P WHERE NOT EXISTS (SELECT 1 FROM #PATH INNER WHERE INNER.DOCUMENT_SHORTNAME = OUTER.DOCUMENT_SHORTNAME AND INNER.LEFT_NR > OUTER.LEFT_NR ) AND INHERIT_BEGIN_LEVEL <= ( SELECT COUNT(SITE_NODE_ID) FROM #PATH COUNTER WHERE COUNTER.LEFT_NR > OUTER.LEFT_NR ) AND ( INHERIT_END_LEVEL = -1 OR INHERIT_END_LEVEL >=(SELECT COUNT(SITE_NODE_ID) FROM #PATH COUNTER WHERE COUNTER.LEFT_NR > OUTER.LEFT_NR) ) AND OUTER.PRESENTATION_ID *= P.PRESENTATION_ID
124 */
125
Popular Tags