|
1
25 package org.jresearch.gossip.dao.drivers.mysql;
26
27 import org.jresearch.gossip.dao.drivers.ForumQueries;
28
29
35 class MySqlForumQueries 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;
63 }
64
65
68 public String getSql_GET_LAST_TOPICS() {
69 return GET_LAST_TOPICS;
70 }
71
72
75 public String getSql_GET_LAST_UPDATED_TOPICS_ALL() {
76 return GET_NEW_THREADS_ALL;
77 }
78
79
82 public String getSql_GET_LAST_UPDATED_TOPICS() {
83 return GET_NEW_THREADS;
84 }
85
86
89 public String getSql_GET_LOG_ENTRIES_ASC() {
90 return GET_LOG_ENTRIES_ASC;
91 }
92
93
96 public String getSql_GET_LOG_ENTRIES_DESC() {
97 return GET_LOG_ENTRIES_DESC;
98 }
99
100
103 public String getSql_GET_OLD_TOPICS() {
104 return GET_OLD_TOPICS;
105 }
106
107
110 public String getSql_GET_THREAD_LAST_INTIME() {
111 return GET_THREAD_LAST_INTIME;
112 }
113
114
117 public String getSql_GET_THREAD_LAST_MESS() {
118 return GET_THREAD_LAST_MESS;
119 }
120
121
124 public String getSql_GET_THREAD_MESSAGES() {
125 return GET_THREAD_MESSAGES;
126 }
127
128
131 public String getSql_GET_THREAD_SUBJ() {
132 return GET_THREAD_SUBJ;
133 }
134
135
138 public String getSql_GET_THREADS() {
139 return GET_THREADS;
140 }
141
142
145 public String getSql_GET_USER_SUBSCRIPTIONS() {
146 return GET_USER_SUBSCRIPTIONS;
147 }
148
149
152 public String getSql_SEARCH_QUERY_END() {
153 return SEARCH_QUERY_END;
154 }
155
156
159 public String getSql_SEARCH_QUERY_SUFF_COUNT() {
160 return SEARCH_QUERY_SUFF_COUNT;
161 }
162
163
166 public String getSql_SEARCH_QUERY_SUFF() {
167 return SEARCH_QUERY_SUFF;
168 }
169
170 private static final String COUNT_LOG_ENTRIES = "SELECT count(log_date) as tot_log_entries "
171 + " FROM jrf_audit_log "
172 + " WHERE "
173 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) >= ? AND "
174 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) <= ? AND "
175 + " logger LIKE ? AND "
176 + " log_level LIKE ? AND "
177 + " remote_ip LIKE ? AND "
178 + " session_id LIKE ? AND "
179 + " user_name LIKE ? " + " ORDER BY log_date ";
180
181 private static final String GET_LAST_MESS = "SELECT jrf_message.centents AS cont, jrf_message.sender AS m_from,jrf_message.id,"
182 + "jrf_thread.lintime AS t_stamp, jrf_thread.threadid AS tid ,jrf_thread.sortby FROM jrf_message, jrf_thread WHERE "
183 + "jrf_thread.lintime = jrf_message.intime AND jrf_thread.threadid = jrf_message.threadid "
184 + "AND jrf_thread.forumid =? ORDER BY lintime DESC LIMIT 1";
185
186 private static final String GET_ROOT_MESS = "SELECT "
187 + " jrf_message.heading as subject, jrf_message.centents, jrf_message.sender, "
188 + " jrf_message.id, jrf_thread.lintime AS t_stamp, jrf_message.ip, jrf_thread.threadid AS tid , jrf_thread.sortby "
189 + "FROM jrf_thread, jrf_message " + "WHERE "
190 + " jrf_thread.threadid = ? AND "
191 + " jrf_message.threadid = jrf_thread.threadid "
192 + "ORDER BY jrf_thread.lintime ASC " + "LIMIT 1";
193
194 private static final String GET_LAST_TOPICS_IN_FORUM = "SELECT jrf_thread.threadid AS tid ,jrf_thread.sortby, "
195 + " jrf_thread.forumid as fid, jrf_thread.LOCKED as locked, "
196 + " count(jrf_message.threadid) AS tot_mes, jrf_forum.FORUMTITLE as forumtitle "
197 + "FROM jrf_thread, jrf_forum, jrf_message "
198 + "WHERE "
199 + " jrf_forum.forumid = ? AND"
200 + " jrf_thread.forumid = jrf_forum.forumid AND "
201 + " jrf_message.threadid = jrf_thread.threadid AND "
202 + " jrf_message.intime > ? "
203 + "GROUP BY jrf_message.threadid "
204 + "ORDER BY lintime ASC " + "LIMIT ?";
205
206 private static final String GET_LAST_TOPICS = "SELECT jrf_thread.threadid AS tid ,jrf_thread.sortby, "
207 + " jrf_thread.forumid as fid, jrf_thread.LOCKED as locked, "
208 + " count(jrf_message.threadid) AS tot_mes, jrf_forum.FORUMTITLE as forumtitle "
209 + "FROM jrf_thread, jrf_forum, jrf_message "
210 + "WHERE "
211 + " jrf_thread.forumid = jrf_forum.forumid AND "
212 + " jrf_message.threadid = jrf_thread.threadid AND "
213 + " jrf_message.intime > ? "
214 + "GROUP BY jrf_message.threadid "
215 + "ORDER BY lintime ASC " + "LIMIT ?";
216
217 private static final String GET_LOG_ENTRIES_ASC = "SELECT log_date, logger, log_level, message, remote_ip, user_name, session_id "
218 + " FROM jrf_audit_log "
219 + " WHERE "
220 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) >= ? AND "
221 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) <= ? AND "
222 + " logger LIKE ? AND "
223 + " log_level LIKE ? AND "
224 + " remote_ip LIKE ? AND "
225 + " session_id LIKE ? AND "
226 + " user_name LIKE ? " + " ORDER BY log_date ASC " + " LIMIT ?, ?";
227
228 private static final String GET_LOG_ENTRIES_DESC = "SELECT log_date, logger, log_level, message, remote_ip, user_name, session_id "
229 + " FROM jrf_audit_log "
230 + " WHERE "
231 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) >= ? AND "
232 + " CAST(SUBSTRING(log_date, 1, 19) AS DATETIME) <= ? AND "
233 + " logger LIKE ? AND "
234 + " log_level LIKE ? AND "
235 + " remote_ip LIKE ? AND "
236 + " session_id LIKE ? AND "
237 + " user_name LIKE ? "
238 + " ORDER BY log_date DESC "
239 + " LIMIT ?, ?";
240
241 private static final String GET_NEW_THREADS = "SELECT jrf_thread.threadid AS id,jrf_thread.sortby AS sortby, jrf_thread.locked AS locked, "
242 + "jrf_message.heading AS subject,jrf_thread.forumid AS fid,count(jrf_message.threadid) AS tot_mes "
243 + "FROM jrf_thread, jrf_message LEFT JOIN jrf_forum ON (jrf_thread.forumid = jrf_forum.forumid) "
244 + "WHERE jrf_thread.threadid = jrf_message.threadid AND jrf_thread.lintime > ? "
245 + "GROUP BY jrf_thread.threadid ORDER BY jrf_thread.sortby, jrf_thread.lintime DESC LIMIT ?,?";
246
247 private static final String GET_NEW_THREADS_ALL = "SELECT jrf_thread.threadid AS id,jrf_thread.sortby AS sortby, jrf_thread.locked AS locked, "
248 + "jrf_message.heading AS subject,jrf_thread.forumid AS fid,count(jrf_message.threadid) AS tot_mes "
249 + "FROM jrf_thread, jrf_message LEFT JOIN jrf_forum ON (jrf_thread.forumid = jrf_forum.forumid) "
250 + "WHERE jrf_thread.threadid = jrf_message.threadid AND jrf_thread.lintime > ? AND jrf_forum.locked < 3 "
251 + "GROUP BY jrf_thread.threadid ORDER BY jrf_thread.sortby, jrf_thread.lintime DESC LIMIT ?,?";
252
253 private static final String GET_OLD_TOPICS = "SELECT jrf_thread.threadid, count(jrf_message.id) AS cc FROM jrf_thread, jrf_message WHERE jrf_thread.threadid = jrf_message.threadid AND TO_DAYS(NOW()) - TO_DAYS(jrf_thread.lintime) > ? GROUP BY jrf_thread.threadid";
254
255 private static final String GET_THREAD_LAST_INTIME = "SELECT intime FROM jrf_message WHERE threadid = ? ORDER BY intime DESC LIMIT 1";
256
257 private static final String GET_THREAD_LAST_MESS = "SELECT centents, intime, sender,id FROM jrf_message WHERE jrf_message.threadid =? ORDER BY intime DESC LIMIT 1";
258
259 private static final String GET_THREAD_MESSAGES = "SELECT * FROM jrf_message WHERE threadid = ? ORDER BY intime LIMIT ?,?";
260
261 private static final String GET_THREAD_SUBJ = "SELECT heading FROM jrf_message WHERE threadid = ? ORDER BY intime LIMIT 1";
262
263 private static final String GET_THREADS = "SELECT jrf_thread.threadid AS id, jrf_thread.sortby AS sortby, jrf_thread.locked AS locked,"
264 + " jrf_message.heading AS subject,count(jrf_thread.threadid) AS tot_mes "
265 + "FROM jrf_thread, jrf_message WHERE jrf_thread.threadid = jrf_message.threadid AND jrf_thread.forumid =?"
266 + " GROUP BY jrf_thread.threadid ORDER BY jrf_thread.sortby, jrf_thread.lintime DESC LIMIT ?,?";
267
268 private static final String GET_USER_SUBSCRIPTIONS = "SELECT DISTINCT jrf_message.heading, jrf_thread.forumid, jrf_subscribe.threadid "
269 + "FROM jrf_subscribe, jrf_message,jrf_thread "
270 + "WHERE user_name = ? AND jrf_message.threadid = jrf_subscribe.threadid AND jrf_thread.threadid = jrf_subscribe.threadid "
271 + "GROUP BY jrf_subscribe.threadid ORDER BY jrf_message.intime ASC LIMIT ?,?";
272
273 private static final String SEARCH_QUERY_END = " ORDER BY jrf_message.intime DESC LIMIT 50";
274
275 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 LEFT JOIN jrf_thread USING(threadid) LEFT JOIN jrf_forum USING(forumid)";
276
277 private static final String SEARCH_QUERY_SUFF_COUNT = "SELECT count(jrf_message.id) FROM jrf_message LEFT JOIN jrf_thread USING(threadid) LEFT JOIN jrf_forum USING(forumid)";
278 }
279 |
Popular Tags |
Java API By Example, From Geeks To Geeks.