2 * Copyright (C) 2001, 2002 The Mir-coders group
4 * This file is part of Mir.
6 * Mir is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
11 * Mir is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with Mir; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 * In addition, as a special exception, The Mir-coders gives permission to link
21 * the code of this program with any library licensed under the Apache Software License,
22 * The Sun (tm) Java Advanced Imaging library (JAI), The Sun JIMI library
23 * (or with modified versions of the above that use the same license as the above),
24 * and distribute linked combinations including the two. You must obey the
25 * GNU General Public License in all respects for all of the code used other than
26 * the above mentioned libraries. If you modify this file, you may extend this
27 * exception to your version of the file, but you are not obligated to do so.
28 * If you do not wish to do so, delete this exception statement from your version.
31 package mircoders.storage;
33 import java.sql.Connection;
34 import java.sql.ResultSet;
35 import java.sql.Statement;
37 import mir.entity.EntityList;
38 import mir.log.LoggerWrapper;
39 import mir.storage.Database;
40 import mir.storage.StorageObject;
41 import mir.storage.StorageObjectExc;
42 import mir.storage.StorageObjectFailure;
43 import mircoders.entity.EntityComment;
44 import mircoders.entity.EntityUploadedMedia;
47 * <b>implements abstract DB connection to the comment_x_media SQL table
49 * @author RK, mir-coders group
50 * @version $Id: DatabaseCommentToMedia.java,v 1.3 2003/05/03 00:21:22 zapata Exp $
54 public class DatabaseCommentToMedia extends Database implements StorageObject{
56 private static DatabaseCommentToMedia instance;
58 public static synchronized DatabaseCommentToMedia getInstance() {
59 if (instance == null) {
60 instance = new DatabaseCommentToMedia();
65 private DatabaseCommentToMedia() {
68 logger = new LoggerWrapper("Database.CommentToMedia");
71 theTable = "comment_x_media";
72 theEntityClass = mir.entity.GenericEntity.class;
76 * get all the media-files belonging to a comment entity
79 public EntityList getMedia(EntityComment comment) throws StorageObjectFailure {
80 EntityList returnList = null;
81 if (comment != null) {
82 // get all to_topic from media_x_topic
83 String id = comment.getId();
84 String subselect = "id in (select media_id from " + theTable +
85 " where comment_id=" + id + ")";
88 // media should stay in uploaded order. this is especially important
89 // for photo stories which require a specific chronologic order.
90 // this is why we have the the second parameter "id"
91 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,
95 logger.debug("-- get media failed " + e.toString());
96 throw new StorageObjectFailure("-- get media failed ", e);
102 public boolean hasMedia(EntityComment comment) throws StorageObjectFailure,
104 if (comment != null) {
106 if (selectByWhereClause("comment_id=" + comment.getId(), -1).size() ==
112 catch (Exception e) {
113 logger.error("DatabaseCommentToMedia.hasMedia: " + e.toString());
114 throw new StorageObjectFailure("DatabaseCommentToMedia.hasMedia: " +
119 logger.error("DatabaseCommentToMedia.hasMedia: comment == null");
120 throw new StorageObjectExc(
121 "DatabaseCommentToMedia.hasMedia: comment == null");
126 * get all the audio belonging to a comment entity
129 public EntityList getAudio(EntityComment comment) throws StorageObjectFailure {
130 EntityList returnList = null;
131 if (comment != null) {
132 // get all to_topic from media_x_topic
133 String id = comment.getId();
134 //this is not supported by mysql
135 String subselect = "id in (select media_id from " + theTable +
136 " where comment_id=" + id + ")";
139 // media should stay in uploaded order. this is especially important
140 // for photo stories which require a specific chronologic order.
141 // this is why we have the the second parameter "id"
142 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
145 catch (Exception e) {
146 logger.error("DatabaseCommentToMedia.getAudio: " + e.toString());
147 throw new StorageObjectFailure("DatabaseCommentToMedia.getAudio: " +
155 * get all the video belonging to a comment entity
158 public EntityList getVideo(EntityComment comment) throws StorageObjectFailure {
159 EntityList returnList = null;
160 if (comment != null) {
161 // get all to_topic from media_x_topic
162 String id = comment.getId();
163 //this is not supported by mysql
164 String subselect = "id in (select media_id from " + theTable +
165 " where comment_id=" + id + ")";
168 // media should stay in uploaded order. this is especially important
169 // for photo stories which require a specific chronologic order.
170 // this is why we have the the second parameter "id"
171 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
174 catch (Exception e) {
175 logger.error("DatabaseCommentToMedia.getVideo: " + e.toString());
176 throw new StorageObjectFailure("DatabaseCommentToMedia.getVideo: " +
184 * get all the images belonging to a comment entity
187 public EntityList getImages(EntityComment comment) throws
188 StorageObjectFailure {
189 EntityList returnList = null;
190 if (comment != null) {
191 // get all to_topic from media_x_topic
192 String id = comment.getId();
193 //this is not supported by mysql
194 String subselect = "id in (select media_id from " + theTable +
195 " where comment_id=" + id + ")";
198 // media should stay in uploaded order. this is especially important
199 // for photo stories which require a specific chronologic order.
200 // this is why we have the the second parameter "id"
201 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
204 catch (Exception e) {
205 logger.error("DatabaseCommentToMedia.getImages: " + e.toString());
206 throw new StorageObjectFailure("DatabaseCommentToMedia.getImages: " +
214 * get all the uploaded/other Media belonging to a comment entity
217 public EntityList getOther(EntityComment comment) throws StorageObjectFailure {
218 /** @todo this should only fetch published media / rk */
220 EntityList returnList = null;
221 if (comment != null) {
222 // get all to_topic from media_x_topic
223 String id = comment.getId();
224 //this is not supported by mysql
225 String subselect = "id in (select media_id from " + theTable +
226 " where comment_id=" + id + ")";
229 // media should stay in uploaded order. this is especially important
230 // for photo stories which require a specific chronologic order.
231 // this is why we have the the second parameter "id"
232 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
235 catch (Exception e) {
236 logger.error("DatabaseCommentToMedia.getOther: " + e.toString());
237 throw new StorageObjectFailure("DatabaseCommentToMedia.getOther: " + e.toString(), e);
244 * get all the uploaded/other Media belonging to a comment entity
247 public EntityList getUploadedMedia(EntityComment comment) throws
248 StorageObjectFailure {
249 /** @todo this should only fetch published media / rk */
251 EntityList returnList = null;
252 if (comment != null) {
253 // get all to_topic from media_x_topic
254 String id = comment.getId();
255 //this is not supported by mysql
256 String subselect = "id in (select media_id from " + theTable +
257 " where comment_id=" + id + ")";
260 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
264 catch (Exception e) {
265 logger.error("DatabaseCommentToMedia.getUploadedMedia: " + e.toString());
266 throw new StorageObjectFailure(
267 "DatabaseCommentToMedia.getUploadedMedia: " + e.toString(), e);
273 public void setMedia(String commentId, String[] mediaId) throws
274 StorageObjectFailure {
275 if (commentId == null) {
278 if (mediaId == null || mediaId[0] == null) {
281 //first delete all row with comment_id=commentId
282 String sql = "delete from " + theTable + " where comment_id=" + commentId;
284 Connection con = null;
285 Statement stmt = null;
287 con = getPooledCon();
288 // should be a preparedStatement because is faster
289 stmt = con.createStatement();
290 ResultSet rs = executeSql(stmt, sql);
292 catch (Exception e) {
293 logger.error("-- set media failed -- delete");
294 throw new StorageObjectFailure("-- set media failed -- delete", e);
297 freeConnection(con, stmt);
301 //first delete all row with comment_id=commentId
302 for (int i = 0; i < mediaId.length; i++) {
303 sql = "insert into " + theTable + " (comment_id,media_id) values ("
304 + commentId + "," + mediaId[i] + ")";
306 con = getPooledCon();
307 // should be a preparedStatement because is faster
308 stmt = con.createStatement();
309 int rs = executeUpdate(stmt, sql);
311 catch (Exception e) {
312 logger.error("-- set topics failed -- insert");
313 throw new StorageObjectFailure("-- set topics failed -- insert ", e);
316 freeConnection(con, stmt);
321 public void addMedia(String commentId, String mediaId) throws
322 StorageObjectFailure {
323 if (commentId == null && mediaId == null) {
327 Connection con = null;
328 Statement stmt = null;
331 String sql = "insert into " + theTable + " (comment_id,media_id) values ("
332 + commentId + "," + mediaId + ")";
334 con = getPooledCon();
335 // should be a preparedStatement because is faster
336 stmt = con.createStatement();
337 int rs = executeUpdate(stmt, sql);
339 catch (Exception e) {
340 logger.error("-- add media failed -- insert");
341 throw new StorageObjectFailure("-- add media failed -- insert ", e);
344 freeConnection(con, stmt);
348 public void setMedia(String commentId, String mediaId) throws
349 StorageObjectFailure {
350 if (commentId == null && mediaId == null) {
353 //first delete all row with comment_id=commentId
354 String sql = "delete from " + theTable + " where comment_id=" + commentId;
356 Connection con = null;
357 Statement stmt = null;
359 con = getPooledCon();
360 // should be a preparedStatement because is faster
361 stmt = con.createStatement();
362 int rs = executeUpdate(stmt, sql);
364 catch (Exception e) {
365 logger.error("-- set media failed -- delete");
366 throw new StorageObjectFailure("-- set media failed -- delete ", e);
369 freeConnection(con, stmt);
373 //first delete all row with comment_id=commentId
375 sql = "insert into " + theTable + " (comment_id,media_id) values ("
376 + commentId + "," + mediaId + ")";
378 con = getPooledCon();
379 // should be a preparedStatement because is faster
380 stmt = con.createStatement();
381 int rs = executeUpdate(stmt, sql);
383 catch (Exception e) {
384 logger.error("-- set media failed -- insert");
385 throw new StorageObjectFailure("-- set media failed -- insert ", e);
388 freeConnection(con, stmt);
392 public void deleteByCommentId(String commentId) throws StorageObjectFailure {
393 if (commentId == null) {
394 //theLog.printDebugInfo("-- delete topics failed -- no comment id");
397 //delete all row with comment_id=commentId
398 String sql = "delete from " + theTable + " where comment_id=" + commentId;
400 Connection con = null;
401 Statement stmt = null;
403 con = getPooledCon();
404 // should be a preparedStatement because is faster
405 stmt = con.createStatement();
406 int rs = executeUpdate(stmt, sql);
408 catch (Exception e) {
409 logger.error("-- delete by commentId failed ");
410 throw new StorageObjectFailure(
411 "-- delete by comment id failed -- delete ", e);
414 freeConnection(con, stmt);
418 public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
419 if (mediaId == null) {
420 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
423 //delete all row with comment_id=commentId
424 String sql = "delete from " + theTable + " where media_id=" + mediaId;
426 Connection con = null;
427 Statement stmt = null;
429 con = getPooledCon();
430 // should be a preparedStatement because is faster
431 stmt = con.createStatement();
432 int rs = executeUpdate(stmt, sql);
433 logger.debug("-- delete media success ");
435 catch (Exception e) {
436 logger.error("-- delete media failed ");
437 throw new StorageObjectFailure("-- delete by media id failed -- ", e);
440 freeConnection(con, stmt);
444 public void delete(String commentId, String mediaId) throws
445 StorageObjectFailure {
446 if (mediaId == null || commentId == null) {
447 logger.debug("-- delete media failed -- missing parameter");
450 //delete all row with comment_id=commentId and media_id=mediaId
451 String sql = "delete from " + theTable + " where media_id=" + mediaId +
452 " and comment_id= " + commentId;
454 Connection con = null;
455 Statement stmt = null;
457 con = getPooledCon();
458 // should be a preparedStatement because is faster
459 stmt = con.createStatement();
460 int rs = executeUpdate(stmt, sql);
461 logger.debug("-- delete comment_x_media success ");
463 catch (Exception e) {
464 logger.error("-- delete comment_x_media failed ");
465 throw new StorageObjectFailure("-- delete comment_x_media failed -- ", e);
468 freeConnection(con, stmt);
472 public EntityList getComment(EntityUploadedMedia media) throws
473 StorageObjectFailure {
474 EntityList returnList = null;
476 String id = media.getId();
477 String select = "select comment_id from " + theTable + " where media_id=" +
480 // execute select statement
481 Connection con = null;
482 Statement stmt = null;
484 con = getPooledCon();
485 // should be a preparedStatement because is faster
486 stmt = con.createStatement();
487 ResultSet rs = executeSql(stmt, select);
489 String mediaSelect = "id IN (";
490 boolean first = true;
494 mediaSelect += rs.getString(1);
499 returnList = DatabaseComment.getInstance().selectByWhereClause(
503 catch (Exception e) {
504 logger.error("-- get comment failed");
505 throw new StorageObjectFailure("-- get comment failed -- ", e);
508 freeConnection(con, stmt);
515 * Returns a EntityList with all comment-objects having a relation to a media
518 public EntityList getComment() throws StorageObjectFailure {
519 EntityList returnList = null;
521 String select = "select distinct comment_id from " + theTable;
522 // execute select statement
523 Connection con = null;
524 Statement stmt = null;
526 con = getPooledCon();
527 // should be a preparedStatement because is faster
528 stmt = con.createStatement();
529 ResultSet rs = executeSql(stmt, select);
531 String mediaSelect = "id IN (";
532 boolean first = true;
536 mediaSelect += rs.getString(1);
541 returnList = DatabaseComment.getInstance().selectByWhereClause(
542 mediaSelect, "webdb_lastchange desc");
545 catch (Exception e) {
546 logger.error("-- get comment failed");
547 throw new StorageObjectFailure("-- get comment failed -- ", e);
550 freeConnection(con, stmt);