KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > jresearch > gossip > dao > drivers > generic > GenericSqlForumQueries


1 /*
2  * $Id: GenericSqlForumQueries.java,v 1.3 2005/06/07 12:32:24 bel70 Exp $
3  *
4  * ***** BEGIN LICENSE BLOCK *****
5  * The contents of this file are subject to the Mozilla Public License
6  * Version 1.1 (the "License"); you may not use this file except in
7  * compliance with the License. You may obtain a copy of the License
8  * at http://www.mozilla.org/MPL/
9  *
10  * Software distributed under the License is distributed on an "AS IS"
11  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
12  * the License for the specific language governing rights and
13  * limitations under the License.
14  *
15  * The Original Code is JGossip forum code.
16  *
17  * The Initial Developer of the Original Code is the JResearch, Org.
18  * Portions created by the Initial Developer are Copyright (C) 2004
19  * the Initial Developer. All Rights Reserved.
20  *
21  * Contributor(s):
22  * Alexey Pavlov <alexnet@users.sourceforge.net>
23  *
24  * ***** END LICENSE BLOCK ***** */

25 package org.jresearch.gossip.dao.drivers.generic;
26
27 import org.jresearch.gossip.dao.drivers.ForumQueries;
28
29 /**
30  * GenericSqlForumQueries
31  *
32  * @author <a HREF="alexnet@sourceforge.net">A. Pavlov</a>
33  * @version $version$ $Date: 2005/06/07 12:32:24 $
34  */

35 class GenericSqlForumQueries extends ForumQueries {
36
37     /**
38      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_COUNT_LOG_ENTRIES()
39      */

40     public String JavaDoc getSql_COUNT_LOG_ENTRIES() {
41         return COUNT_LOG_ENTRIES;
42     }
43
44     /**
45      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_LAST_MESS()
46      */

47     public String JavaDoc getSql_GET_LAST_MESS() {
48         return GET_LAST_MESS;
49     }
50
51     /**
52      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_ROOT_MESS()
53      */

54     public String JavaDoc getSql_GET_ROOT_MESS() {
55         return GET_ROOT_MESS;
56     }
57
58     /**
59      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_LAST_TOPICS_IN_FORUM()
60      */

61     public String JavaDoc getSql_GET_LAST_TOPICS_IN_FORUM() {
62         // TODO: Optimize query, change selection criteria for intime of root
63
// message.
64
return GET_LAST_TOPICS_IN_FORUM;
65     }
66
67     /**
68      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_LAST_TOPICS()
69      */

70     public String JavaDoc getSql_GET_LAST_TOPICS() {
71         // TODO: Optimize query, change selection criteria for intime of root
72
// message.
73
return GET_LAST_TOPICS;
74     }
75
76     /**
77      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_NEW_THREADS_ALL()
78      */

79     public String JavaDoc getSql_GET_LAST_UPDATED_TOPICS_ALL() {
80         // TODO: Fix bug for grouping over threadid(behaves incorrectly).
81
return GET_NEW_THREADS_ALL;
82     }
83
84     /**
85      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_NEW_THREADS()
86      */

87     public String JavaDoc getSql_GET_LAST_UPDATED_TOPICS() {
88         // TODO: Fix bug for grouping over threadid(behaves incorrectly).
89
return GET_NEW_THREADS;
90     }
91
92     /**
93      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_LOG_ENTRIES_ASC()
94      */

95     public String JavaDoc getSql_GET_LOG_ENTRIES_ASC() {
96         return GET_LOG_ENTRIES_ASC;
97     }
98
99     /**
100      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_LOG_ENTRIES_DESC()
101      */

102     public String JavaDoc getSql_GET_LOG_ENTRIES_DESC() {
103         return GET_LOG_ENTRIES_DESC;
104     }
105
106     /**
107      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_OLD_TOPICS()
108      */

109     public String JavaDoc getSql_GET_OLD_TOPICS() {
110         return GET_OLD_TOPICS;
111     }
112
113     /**
114      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_THREAD_LAST_INTIME()
115      */

116     public String JavaDoc getSql_GET_THREAD_LAST_INTIME() {
117         return GET_THREAD_LAST_INTIME;
118     }
119
120     /**
121      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_THREAD_LAST_MESS()
122      */

123     public String JavaDoc getSql_GET_THREAD_LAST_MESS() {
124         return GET_THREAD_LAST_MESS;
125     }
126
127     /**
128      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_THREAD_MESSAGES()
129      */

130     public String JavaDoc getSql_GET_THREAD_MESSAGES() {
131         return GET_THREAD_MESSAGES;
132     }
133
134     /**
135      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_THREAD_SUBJ()
136      */

137     public String JavaDoc getSql_GET_THREAD_SUBJ() {
138         return GET_THREAD_SUBJ;
139     }
140
141     /**
142      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_THREADS()
143      */

144     public String JavaDoc getSql_GET_THREADS() {
145         return GET_THREADS;
146     }
147
148     /**
149      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_GET_USER_SUBSCRIPTIONS()
150      */

151     public String JavaDoc getSql_GET_USER_SUBSCRIPTIONS() {
152         return GET_USER_SUBSCRIPTIONS;
153     }
154
155     /**
156      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_SEARCH_QUERY_END()
157      */

158     public String JavaDoc getSql_SEARCH_QUERY_END() {
159         return SEARCH_QUERY_END;
160     }
161
162     /**
163      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_SEARCH_QUERY_SUFF_COUNT()
164      */

165     public String JavaDoc getSql_SEARCH_QUERY_SUFF_COUNT() {
166         return SEARCH_QUERY_SUFF_COUNT;
167     }
168
169     /**
170      * @see org.jresearch.gossip.dao.drivers.ForumQueries#getSql_SEARCH_QUERY_SUFF()
171      */

172     public String JavaDoc getSql_SEARCH_QUERY_SUFF() {
173         return SEARCH_QUERY_SUFF;
174     }
175
176     private static final String JavaDoc COUNT_LOG_ENTRIES = "SELECT count(log_date) as tot_log_entries "
177             + " FROM jrf_audit_log "
178             + " WHERE TO_DATE(SUBSTR(log_date, 0, 19), 'YYYY-MM-DD HH24:MI:SS') BETWEEN ? AND ? AND "
179             + " logger LIKE ? AND "
180             + " log_level LIKE ? AND "
181             + " remote_ip LIKE ? AND "
182             + " session_id LIKE ? AND "
183             + " user_name LIKE ? " + " ORDER BY log_date ";
184
185     private static final String JavaDoc GET_LAST_MESS = "select * from ( "
186             + " SELECT jrf_message.centents AS cont, jrf_message.sender AS m_from,jrf_message.id, "
187             + " jrf_thread.lintime AS t_stamp, jrf_thread.threadid AS tid ,jrf_thread.sortby, "
188             + " ROW_NUMBER() OVER(ORDER BY jrf_thread.lintime DESC) AS R"
189             + " FROM jrf_thread, jrf_message "
190             + " WHERE jrf_thread.forumid = ? AND jrf_message.threadid = jrf_thread.threadid AND "
191             + " jrf_thread.lintime = jrf_message.intime "
192             + " ORDER BY jrf_thread.lintime DESC " + ") WHERE R = 1";
193
194     private static final String JavaDoc GET_ROOT_MESS = "select * from ( "
195             + " SELECT "
196             + " jrf_message.heading as subject, jrf_message.centents, jrf_message.sender, "
197             + " jrf_message.id, jrf_thread.lintime AS t_stamp, jrf_message.ip, jrf_thread.threadid AS tid , "
198             + " jrf_thread.sortby, ROW_NUMBER() OVER(ORDER BY jrf_thread.lintime ASC) AS R "
199             + " FROM jrf_thread, jrf_message " + " WHERE "
200             + " jrf_thread.threadid = ? AND "
201             + " jrf_message.threadid = jrf_thread.threadid "
202             + " ORDER BY jrf_thread.lintime ASC " + ") WHERE R = 1";
203
204     private static final String JavaDoc GET_LAST_TOPICS_IN_FORUM = "select * from ( "
205             + " SELECT jrf_thread.threadid AS tid ,jrf_thread.sortby, "
206             + " jrf_thread.forumid as fid, jrf_thread.LOCKED as locked, "
207             + " count(jrf_message.threadid) AS tot_mes, jrf_forum.FORUMTITLE as forumtitle, "
208             + " ROW_NUMBER() OVER(ORDER BY jrf_thread.lintime ASC) AS R "
209             + " FROM jrf_thread, jrf_forum, jrf_message "
210             + " WHERE "
211             + " jrf_forum.forumid = ? AND"
212             + " jrf_thread.forumid = jrf_forum.forumid AND "
213             + " jrf_message.threadid = jrf_thread.threadid AND "
214             + " jrf_message.intime > ? "
215             + " GROUP BY "
216             + " jrf_thread.threadid, jrf_message.threadid, jrf_thread.sortby, jrf_thread.forumid, "
217             + " jrf_thread.LOCKED, jrf_thread.lintime, jrf_forum.FORUMTITLE "
218             + " ORDER BY jrf_thread.lintime ASC "
219             + ") WHERE R between 0 and ?";
220
221     private static final String JavaDoc GET_LAST_TOPICS = "select * from ( "
222             + " SELECT jrf_thread.threadid AS tid ,jrf_thread.sortby, "
223             + " jrf_thread.forumid as fid, jrf_thread.LOCKED as locked, "
224             + " count(jrf_message.threadid) AS tot_mes, jrf_forum.FORUMTITLE as forumtitle, "
225             + " ROW_NUMBER() OVER(ORDER BY jrf_thread.lintime ASC) AS R "
226             + " FROM jrf_thread, jrf_forum, jrf_message "
227             + " WHERE "
228             + " jrf_thread.forumid = jrf_forum.forumid AND "
229             + " jrf_message.threadid = jrf_thread.threadid AND "
230             + " jrf_message.intime > ? "
231             + " GROUP BY "
232             + " jrf_thread.threadid, jrf_message.threadid, jrf_thread.sortby, jrf_thread.forumid, "
233             + " jrf_thread.LOCKED, jrf_thread.lintime, jrf_forum.FORUMTITLE "
234             + " ORDER BY jrf_thread.lintime ASC "
235             + ") WHERE R between 0 and ?";
236
237     private static final String JavaDoc GET_LOG_ENTRIES_ASC = "SELECT * FROM ("
238             + "SELECT log_date, logger, log_level, message, remote_ip, session_id, user_name, "
239             + " ROW_NUMBER() OVER(ORDER BY log_date ASC ) AS R"
240             + " FROM jrf_audit_log "
241             + " WHERE TO_DATE(SUBSTR(log_date, 0, 19), 'YYYY-MM-DD HH24:MI:SS') BETWEEN ? AND ? AND "
242             + " logger LIKE ? AND " + " log_level LIKE ? AND "
243             + " remote_ip LIKE ? AND " + " session_id LIKE ? AND "
244             + " user_name LIKE ? " + " ORDER BY log_date ASC "
245             + ") WHERE R BETWEEN ? AND ? ";
246
247     private static final String JavaDoc GET_LOG_ENTRIES_DESC = "SELECT * FROM ("
248             + "SELECT log_date, logger, log_level, message, remote_ip, session_id, user_name, "
249             + " ROW_NUMBER() OVER(ORDER BY log_date DESC ) AS R"
250             + " FROM jrf_audit_log "
251             + " WHERE TO_DATE(SUBSTR(log_date, 0, 19), 'YYYY-MM-DD HH24:MI:SS') BETWEEN ? AND ? AND "
252             + " logger LIKE ? AND " + " log_level LIKE ? AND "
253             + " remote_ip LIKE ? AND " + " session_id LIKE ? AND "
254             + " user_name LIKE ? " + " ORDER BY log_date DESC "
255             + ") WHERE R BETWEEN ? AND ? ";
256
257     private static final String JavaDoc GET_NEW_THREADS = "SELECT * from "
258             + " (SELECT "
259             + " jrf_thread.threadid AS id, jrf_thread.sortby AS sortby, jrf_thread.locked AS locked, jrf_message.heading AS subject, "
260             + " jrf_thread.forumid AS fid, count(jrf_message.threadid) AS tot_mes, jrf_thread.lintime AS lintime, "
261             + " ROW_NUMBER() OVER(ORDER BY sortby, jrf_thread.lintime DESC) AS R "
262             + " FROM "
263             + " jrf_thread, jrf_message, jrf_forum "
264             + " WHERE "
265             + " jrf_thread.forumid = jrf_forum.forumid AND "
266             + " jrf_thread.threadid = jrf_message.threadid AND "
267             + " jrf_thread.lintime > ? AND jrf_forum.locked < 3 "
268             + " GROUP BY "
269             + " jrf_thread.threadid, jrf_thread.sortby, jrf_thread.locked, jrf_message.heading, jrf_thread.forumid, jrf_thread.lintime "
270             + " ORDER BY " + " jrf_thread.sortby, jrf_thread.lintime DESC "
271             + ") WHERE R BETWEEN ? AND ? ORDER BY sortby, lintime";
272
273     private static final String JavaDoc GET_NEW_THREADS_ALL = "select * from ( "
274             + " SELECT "
275             + " jrf_thread.threadid AS id, jrf_thread.sortby AS sortby, jrf_thread.locked AS locked, jrf_message.heading AS subject, "
276             + " jrf_thread.forumid AS fid, count(jrf_message.threadid) AS tot_mes, jrf_thread.lintime AS lintime, "
277             + " ROW_NUMBER() OVER(ORDER BY sortby, lintime) AS R "
278             + " FROM "
279             + " jrf_thread, jrf_message "
280             + " WHERE "
281             + " jrf_thread.threadid = jrf_message.threadid AND "
282             + " jrf_thread.lintime > ? "
283             + " GROUP BY "
284             + " jrf_thread.threadid, jrf_thread.sortby, jrf_thread.locked, jrf_message.heading, jrf_thread.forumid, jrf_thread.lintime "
285             + " ORDER BY " + " jrf_thread.sortby, jrf_thread.lintime DESC"
286             + ") WHERE R BETWEEN ? AND ? ORDER BY sortby, lintime";
287
288     private static final String JavaDoc GET_OLD_TOPICS = "SELECT jrf_thread.threadid, count(jrf_message.id) AS cc "
289             + "FROM jrf_thread, jrf_message "
290             + "WHERE jrf_thread.threadid = jrf_message.threadid AND (sysdate - TO_DATE(TO_CHAR(jrf_thread.lintime, 'MM-dd-YYYY'), 'MM-dd-YYYY')) > ? "
291             + "GROUP BY jrf_thread.threadid";
292
293     private static final String JavaDoc GET_THREAD_LAST_INTIME = "SELECT * from "
294             + " (SELECT intime, ROW_NUMBER() OVER(ORDER BY intime DESC) AS R FROM jrf_message WHERE threadid = ? ORDER BY intime DESC "
295             + " ) WHERE R = 1";
296
297     private static final String JavaDoc GET_THREAD_LAST_MESS = "SELECT * FROM "
298             + " (SELECT centents, intime, sender, id, "
299             + " ROW_NUMBER() OVER(ORDER BY intime DESC) AS R "
300             + " FROM jrf_message WHERE jrf_message.threadid = ? "
301             + ") WHERE R = 1";
302
303     private static final String JavaDoc GET_THREAD_MESSAGES = "select * from "
304             + " (SELECT ID, SENDER, CENTENTS, INTIME, HEADING, THREADID, IP,"
305             + " ROW_NUMBER() OVER(ORDER BY intime) AS R "
306             + " FROM jrf_message " + " WHERE threadid = ? "
307             + " ORDER BY intime" + " ) WHERE R BETWEEN ? AND ?";
308
309     private static final String JavaDoc GET_THREAD_SUBJ = "SELECT * FROM"
310             + " (SELECT heading, ROW_NUMBER() OVER(ORDER BY intime) AS R "
311             + " FROM jrf_message WHERE threadid = ? " + " ORDER BY intime"
312             + ") WHERE R = 1";
313
314     private static final String JavaDoc GET_THREADS = "SELECT * FROM "
315             + " (SELECT "
316             + " t1.threadid as id, t1.sortby as sortby, t1.locked as locked, "
317             + " t1.forumid as fid, t1.lintime as lintime, m2.heading as subject, "
318             + " t2.mes_cnt as tot_mes, "
319             + " ROW_NUMBER() OVER(ORDER BY t1.sortby, t1.lintime DESC) AS R "
320             + " FROM jrf_thread t1, jrf_message m2, "
321             + " (SELECT "
322             + " jrf_thread.threadid AS id, count(jrf_thread.threadid) AS mes_cnt "
323             + " FROM "
324             + " jrf_thread, jrf_message "
325             + " WHERE "
326             + " jrf_thread.threadid = jrf_message.threadid AND "
327             + " jrf_thread.forumid = ?"
328             + " GROUP BY "
329             + " jrf_thread.threadid "
330             + " ORDER BY"
331             + " jrf_thread.threadid "
332             + " ) t2 "
333             + " WHERE "
334             + " t1.threadid=t2.id AND m2.threadid=t2.id AND m2.intime=t1.lintime "
335             + ") master " + "WHERE " + " master.R BETWEEN ? AND ?";
336
337     private static final String JavaDoc GET_USER_SUBSCRIPTIONS = "select * from "
338             + " (SELECT DISTINCT "
339             + " t1.threadid, jrf_message.heading, t1.intime, jrf_thread.forumid, "
340             + " ROW_NUMBER() OVER(ORDER BY jrf_message.intime) AS R "
341             + " FROM jrf_message, jrf_thread, " + " (SELECT DISTINCT "
342             + " jrf_message.threadid, MIN(jrf_message.intime) as intime "
343             + " FROM jrf_subscribe, jrf_message " + " WHERE "
344             + " jrf_message.threadid = jrf_subscribe.threadid AND "
345             + " user_name = ? " + " GROUP BY jrf_message.threadid "
346             + " ) t1 " + " WHERE "
347             + " jrf_message.intime = t1.intime AND "
348             + " jrf_message.threadid = t1.threadid AND "
349             + " jrf_thread.threadid = t1.threadid "
350             + " ORDER BY t1.intime " + ") WHERE R BETWEEN ? AND ? ";
351
352     private static final String JavaDoc SEARCH_QUERY_END = " AND jrf_thread.threadid = jrf_message.THREADID AND jrf_forum.forumid = jrf_thread.FORUMID AND ROWNUM >= 1 AND ROWNUM <= 50 ORDER BY jrf_message.intime DESC";
353
354     private static final String JavaDoc SEARCH_QUERY_SUFF = "SELECT jrf_message.id, jrf_message.centents, jrf_message.heading, jrf_message.sender, jrf_message.intime, jrf_thread.forumid, jrf_thread.threadid, jrf_forum.locked FROM jrf_message, jrf_thread, jrf_forum ";
355
356     private static final String JavaDoc SEARCH_QUERY_SUFF_COUNT = "SELECT count(jrf_message.id) FROM jrf_message, jrf_thread, jrf_forum ";
357
358 }
359
Popular Tags