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 the com.oreilly.servlet library, any library
22 * licensed under the Apache Software License, The Sun (tm) Java Advanced
23 * Imaging library (JAI), The Sun JIMI library (or with modified versions of
24 * the above that use the same license as the above), and distribute linked
25 * combinations including the two. You must obey the GNU General Public
26 * License in all respects for all of the code used other than the above
27 * mentioned libraries. If you modify this file, you may extend this exception
28 * to your version of the file, but you are not obligated to do so. If you do
29 * not wish to do so, delete this exception statement from your version.
32 package mircoders.storage;
34 import java.sql.Connection;
35 import java.sql.ResultSet;
36 import java.sql.Statement;
38 import mir.entity.EntityList;
39 import mir.log.LoggerWrapper;
40 import mir.storage.Database;
41 import mir.storage.StorageObject;
42 import mir.storage.StorageObjectExc;
43 import mir.storage.StorageObjectFailure;
44 import mircoders.entity.EntityContent;
45 import mircoders.entity.EntityUploadedMedia;
48 * <b>implements abstract DB connection to the content_x_media SQL table
50 * @author RK, mir-coders group
51 * @version $Id: DatabaseContentToMedia.java,v 1.17 2003/04/09 02:06:10 zapata Exp $
55 public class DatabaseContentToMedia extends Database implements StorageObject{
57 private static DatabaseContentToMedia instance;
59 public static DatabaseContentToMedia getInstance() {
60 if (instance == null) {
61 synchronized (DatabaseContentToMedia.class) {
62 if (instance == null) {
63 instance = new DatabaseContentToMedia();
64 instance.myselfDatabase = instance;
71 private DatabaseContentToMedia() {
74 logger = new LoggerWrapper("Database.ContentToMedia");
77 theTable = "content_x_media";
78 theEntityClass = mir.entity.GenericEntity.class;
82 * get all the media-files belonging to a content entity
85 public EntityList getMedia(EntityContent content) throws StorageObjectFailure {
86 EntityList returnList = null;
87 if (content != null) {
88 // get all to_topic from media_x_topic
89 String id = content.getId();
90 String subselect = "id in (select media_id from " + theTable +
91 " where content_id=" + id + ")";
94 // media should stay in uploaded order. this is especially important
95 // for photo stories which require a specific chronologic order.
96 // this is why we have the the second parameter "id"
97 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,
100 catch (Throwable e) {
101 logger.debug("-- get media failed " + e.toString());
102 throw new StorageObjectFailure("-- get media failed ", e);
108 public boolean hasMedia(EntityContent content) throws StorageObjectFailure,
110 if (content != null) {
112 if (selectByWhereClause("content_id=" + content.getId(), -1).size() ==
118 catch (Exception e) {
119 logger.error("DatabaseContentToMedia.hasMedia: " + e.toString());
120 throw new StorageObjectFailure("DatabaseContentToMedia.hasMedia: " +
125 logger.error("DatabaseContentToMedia.hasMedia: content == null");
126 throw new StorageObjectExc(
127 "DatabaseContentToMedia.hasMedia: content == null");
132 * get all the audio belonging to a content entity
135 public EntityList getAudio(EntityContent content) throws StorageObjectFailure {
136 EntityList returnList = null;
137 if (content != null) {
138 // get all to_topic from media_x_topic
139 String id = content.getId();
140 //this is not supported by mysql
141 String subselect = "id in (select media_id from " + theTable +
142 " where content_id=" + id + ")";
145 // media should stay in uploaded order. this is especially important
146 // for photo stories which require a specific chronologic order.
147 // this is why we have the the second parameter "id"
148 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
151 catch (Exception e) {
152 logger.error("DatabaseContentToMedia.getAudio: " + e.toString());
153 throw new StorageObjectFailure("DatabaseContentToMedia.getAudio: " +
161 * get all the video belonging to a content entity
164 public EntityList getVideo(EntityContent content) throws StorageObjectFailure {
165 EntityList returnList = null;
166 if (content != null) {
167 // get all to_topic from media_x_topic
168 String id = content.getId();
169 //this is not supported by mysql
170 String subselect = "id in (select media_id from " + theTable +
171 " where content_id=" + id + ")";
174 // media should stay in uploaded order. this is especially important
175 // for photo stories which require a specific chronologic order.
176 // this is why we have the the second parameter "id"
177 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
180 catch (Exception e) {
181 logger.error("DatabaseContentToMedia.getVideo: " + e.toString());
182 throw new StorageObjectFailure("DatabaseContentToMedia.getVideo: " +
190 * get all the images belonging to a content entity
193 public EntityList getImages(EntityContent content) throws
194 StorageObjectFailure {
195 EntityList returnList = null;
196 if (content != null) {
197 // get all to_topic from media_x_topic
198 String id = content.getId();
199 //this is not supported by mysql
200 String subselect = "id in (select media_id from " + theTable +
201 " where content_id=" + id + ")";
204 // media should stay in uploaded order. this is especially important
205 // for photo stories which require a specific chronologic order.
206 // this is why we have the the second parameter "id"
207 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
210 catch (Exception e) {
211 logger.error("DatabaseContentToMedia.getImages: " + e.toString());
212 throw new StorageObjectFailure("DatabaseContentToMedia.getImages: " +
220 * get all the uploaded/other Media belonging to a content entity
223 public EntityList getOther(EntityContent content) throws StorageObjectFailure {
224 /** @todo this should only fetch published media / rk */
226 EntityList returnList = null;
227 if (content != null) {
228 // get all to_topic from media_x_topic
229 String id = content.getId();
230 //this is not supported by mysql
231 String subselect = "id in (select media_id from " + theTable +
232 " where content_id=" + id + ")";
235 // media should stay in uploaded order. this is especially important
236 // for photo stories which require a specific chronologic order.
237 // this is why we have the the second parameter "id"
238 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
241 catch (Exception e) {
242 logger.error("DatabaseContentToMedia.getOther: " + e.toString());
243 throw new StorageObjectFailure("DatabaseContentToMedia.getOther: " + e.toString(), e);
250 * get all the uploaded/other Media belonging to a content entity
253 public EntityList getUploadedMedia(EntityContent content) throws
254 StorageObjectFailure {
255 /** @todo this should only fetch published media / rk */
257 EntityList returnList = null;
258 if (content != null) {
259 // get all to_topic from media_x_topic
260 String id = content.getId();
261 //this is not supported by mysql
262 String subselect = "id in (select media_id from " + theTable +
263 " where content_id=" + id + ")";
266 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
270 catch (Exception e) {
271 logger.error("DatabaseContentToMedia.getUploadedMedia: " + e.toString());
272 throw new StorageObjectFailure(
273 "DatabaseContentToMedia.getUploadedMedia: " + e.toString(), e);
279 public void setMedia(String contentId, String[] mediaId) throws
280 StorageObjectFailure {
281 if (contentId == null) {
284 if (mediaId == null || mediaId[0] == null) {
287 //first delete all row with content_id=contentId
288 String sql = "delete from " + theTable + " where content_id=" + contentId;
290 Connection con = null;
291 Statement stmt = null;
293 con = getPooledCon();
294 // should be a preparedStatement because is faster
295 stmt = con.createStatement();
296 ResultSet rs = executeSql(stmt, sql);
298 catch (Exception e) {
299 logger.error("-- set media failed -- delete");
300 throw new StorageObjectFailure("-- set media failed -- delete", e);
303 freeConnection(con, stmt);
307 //first delete all row with content_id=contentId
308 for (int i = 0; i < mediaId.length; i++) {
309 sql = "insert into " + theTable + " (content_id,media_id) values ("
310 + contentId + "," + mediaId[i] + ")";
312 con = getPooledCon();
313 // should be a preparedStatement because is faster
314 stmt = con.createStatement();
315 int rs = executeUpdate(stmt, sql);
317 catch (Exception e) {
318 logger.error("-- set topics failed -- insert");
319 throw new StorageObjectFailure("-- set topics failed -- insert ", e);
322 freeConnection(con, stmt);
327 public void addMedia(String contentId, String mediaId) throws
328 StorageObjectFailure {
329 if (contentId == null && mediaId == null) {
333 Connection con = null;
334 Statement stmt = null;
337 String sql = "insert into " + theTable + " (content_id,media_id) values ("
338 + contentId + "," + mediaId + ")";
340 con = getPooledCon();
341 // should be a preparedStatement because is faster
342 stmt = con.createStatement();
343 int rs = executeUpdate(stmt, sql);
345 catch (Exception e) {
346 logger.error("-- add media failed -- insert");
347 throw new StorageObjectFailure("-- add media failed -- insert ", e);
350 freeConnection(con, stmt);
354 public void setMedia(String contentId, String mediaId) throws
355 StorageObjectFailure {
356 if (contentId == null && mediaId == null) {
359 //first delete all row with content_id=contentId
360 String sql = "delete from " + theTable + " where content_id=" + contentId;
362 Connection con = null;
363 Statement stmt = null;
365 con = getPooledCon();
366 // should be a preparedStatement because is faster
367 stmt = con.createStatement();
368 int rs = executeUpdate(stmt, sql);
370 catch (Exception e) {
371 logger.error("-- set media failed -- delete");
372 throw new StorageObjectFailure("-- set media failed -- delete ", e);
375 freeConnection(con, stmt);
379 //first delete all row with content_id=contentId
381 sql = "insert into " + theTable + " (content_id,media_id) values ("
382 + contentId + "," + mediaId + ")";
384 con = getPooledCon();
385 // should be a preparedStatement because is faster
386 stmt = con.createStatement();
387 int rs = executeUpdate(stmt, sql);
389 catch (Exception e) {
390 logger.error("-- set media failed -- insert");
391 throw new StorageObjectFailure("-- set media failed -- insert ", e);
394 freeConnection(con, stmt);
398 public void deleteByContentId(String contentId) throws StorageObjectFailure {
399 if (contentId == null) {
400 //theLog.printDebugInfo("-- delete topics failed -- no content id");
403 //delete all row with content_id=contentId
404 String sql = "delete from " + theTable + " where content_id=" + contentId;
406 Connection con = null;
407 Statement stmt = null;
409 con = getPooledCon();
410 // should be a preparedStatement because is faster
411 stmt = con.createStatement();
412 int rs = executeUpdate(stmt, sql);
414 catch (Exception e) {
415 logger.error("-- delete by contentId failed ");
416 throw new StorageObjectFailure(
417 "-- delete by content id failed -- delete ", e);
420 freeConnection(con, stmt);
424 public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
425 if (mediaId == null) {
426 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
429 //delete all row with content_id=contentId
430 String sql = "delete from " + theTable + " where media_id=" + mediaId;
432 Connection con = null;
433 Statement stmt = null;
435 con = getPooledCon();
436 // should be a preparedStatement because is faster
437 stmt = con.createStatement();
438 int rs = executeUpdate(stmt, sql);
439 logger.debug("-- delete media success ");
441 catch (Exception e) {
442 logger.error("-- delete media failed ");
443 throw new StorageObjectFailure("-- delete by media id failed -- ", e);
446 freeConnection(con, stmt);
450 public void delete(String contentId, String mediaId) throws
451 StorageObjectFailure {
452 if (mediaId == null || contentId == null) {
453 logger.debug("-- delete media failed -- missing parameter");
456 //delete all row with content_id=contentId and media_id=mediaId
457 String sql = "delete from " + theTable + " where media_id=" + mediaId +
458 " and content_id= " + contentId;
460 Connection con = null;
461 Statement stmt = null;
463 con = getPooledCon();
464 // should be a preparedStatement because is faster
465 stmt = con.createStatement();
466 int rs = executeUpdate(stmt, sql);
467 logger.debug("-- delete content_x_media success ");
469 catch (Exception e) {
470 logger.error("-- delete content_x_media failed ");
471 throw new StorageObjectFailure("-- delete content_x_media failed -- ", e);
474 freeConnection(con, stmt);
478 public EntityList getContent(EntityUploadedMedia media) throws
479 StorageObjectFailure {
480 EntityList returnList = null;
482 String id = media.getId();
483 String select = "select content_id from " + theTable + " where media_id=" +
486 // execute select statement
487 Connection con = null;
488 Statement stmt = null;
490 con = getPooledCon();
491 // should be a preparedStatement because is faster
492 stmt = con.createStatement();
493 ResultSet rs = executeSql(stmt, select);
495 String mediaSelect = "id IN (";
496 boolean first = true;
500 mediaSelect += rs.getString(1);
505 returnList = DatabaseContent.getInstance().selectByWhereClause(
509 catch (Exception e) {
510 logger.error("-- get content failed");
511 throw new StorageObjectFailure("-- get content failed -- ", e);
514 freeConnection(con, stmt);
521 * Returns a EntityList with all content-objects having a relation to a media
524 public EntityList getContent() throws StorageObjectFailure {
525 EntityList returnList = null;
527 String select = "select distinct content_id from " + theTable;
528 // execute select statement
529 Connection con = null;
530 Statement stmt = null;
532 con = getPooledCon();
533 // should be a preparedStatement because is faster
534 stmt = con.createStatement();
535 ResultSet rs = executeSql(stmt, select);
537 String mediaSelect = "id IN (";
538 boolean first = true;
542 mediaSelect += rs.getString(1);
547 returnList = DatabaseContent.getInstance().selectByWhereClause(
548 mediaSelect, "webdb_lastchange desc");
551 catch (Exception e) {
552 logger.error("-- get content failed");
553 throw new StorageObjectFailure("-- get content failed -- ", e);
556 freeConnection(con, stmt);