|
1
25 package org.jresearch.gossip.dao.drivers.generic;
26
27 import org.jresearch.gossip.dao.drivers.ForumQueries;
28
29
35 class GenericSqlForumQueries extends ForumQueries {
36
37
40 public String  getSql_COUNT_LOG_ENTRIES() {
41 return COUNT_LOG_ENTRIES;
42 }
43
44
47 public String getSql_GET_LAST_MESS() {
48 return GET_LAST_MESS;
49 }
50
51
54 public String getSql_GET_ROOT_MESS() {
55 return GET_ROOT_MESS;
56 }
57
58
61 public String getSql_GET_LAST_TOPICS_IN_FORUM() {
62 return GET_LAST_TOPICS_IN_FORUM;
65 }
66
67
70 public String getSql_GET_LAST_TOPICS() {
71 return GET_LAST_TOPICS;
74 }
75
76
79 public String getSql_GET_LAST_UPDATED_TOPICS_ALL() {
80 return GET_NEW_THREADS_ALL;
82 }
83
84
87 public String getSql_GET_LAST_UPDATED_TOPICS() {
88 return GET_NEW_THREADS;
90 }
91
92
95 public String getSql_GET_LOG_ENTRIES_ASC() {
96 return GET_LOG_ENTRIES_ASC;
97 }
98
99
102 public String getSql_GET_LOG_ENTRIES_DESC() {
103 return GET_LOG_ENTRIES_DESC;
104 }
105
106
109 public String getSql_GET_OLD_TOPICS() {
110 return GET_OLD_TOPICS;
111 }
112
113
116 public String getSql_GET_THREAD_LAST_INTIME() {
117 return GET_THREAD_LAST_INTIME;
118 }
119
120
123 public String getSql_GET_THREAD_LAST_MESS() {
124 return GET_THREAD_LAST_MESS;
125 }
126
127
130 public String getSql_GET_THREAD_MESSAGES() {
131 return GET_THREAD_MESSAGES;
132 }
133
134
137 public String getSql_GET_THREAD_SUBJ() {
138 return GET_THREAD_SUBJ;
139 }
140
141
144 public String getSql_GET_THREADS() {
145 return GET_THREADS;
146 }
147
148
151 public String getSql_GET_USER_SUBSCRIPTIONS() {
152 return GET_USER_SUBSCRIPTIONS;
153 }
154
155
158 public String getSql_SEARCH_QUERY_END() {
159 return SEARCH_QUERY_END;
160 }
161
162
165 public String getSql_SEARCH_QUERY_SUFF_COUNT() {
166 return SEARCH_QUERY_SUFF_COUNT;
167 }
168
169
172 public String getSql_SEARCH_QUERY_SUFF() {
173 return SEARCH_QUERY_SUFF;
174 }
175
176 private static final String 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 SEARCH_QUERY_SUFF_COUNT = "SELECT count(jrf_message.id) FROM jrf_message, jrf_thread, jrf_forum ";
357
358 }
359 |
Popular Tags |
Java API By Example, From Geeks To Geeks.