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;
\r
34 import java.sql.ResultSet;
\r
35 import java.sql.Statement;
\r
37 import mir.entity.EntityList;
\r
38 import mir.log.LoggerWrapper;
\r
39 import mir.storage.Database;
\r
40 import mir.storage.StorageObject;
\r
41 import mir.storage.StorageObjectExc;
\r
42 import mir.storage.StorageObjectFailure;
\r
43 import mircoders.entity.EntityContent;
\r
44 import mircoders.entity.EntityUploadedMedia;
47 * <b>implements abstract DB connection to the content_x_media SQL table
49 * @author RK, mir-coders group
50 * @version $Id: DatabaseContentToMedia.java,v 1.20 2003/09/03 18:29:05 zapata Exp $
54 public class DatabaseContentToMedia extends Database implements StorageObject{
56 private static DatabaseContentToMedia instance;
58 public synchronized static DatabaseContentToMedia getInstance() {
59 if (instance == null) {
60 instance = new DatabaseContentToMedia();
65 private DatabaseContentToMedia() {
68 logger = new LoggerWrapper("Database.ContentToMedia");
71 theTable = "content_x_media";
72 theEntityClass = mir.entity.GenericEntity.class;
75 public boolean hasMedia(EntityContent content) throws StorageObjectFailure,
77 if (content != null) {
79 if (selectByWhereClause("content_id=" + content.getId(), -1).size() ==
86 logger.error("DatabaseContentToMedia.hasMedia: " + e.toString());
87 throw new StorageObjectFailure("DatabaseContentToMedia.hasMedia: " +
92 logger.error("DatabaseContentToMedia.hasMedia: content == null");
93 throw new StorageObjectExc(
94 "DatabaseContentToMedia.hasMedia: content == null");
99 * get all the audio belonging to a content entity
102 public EntityList getAudio(EntityContent content) throws StorageObjectFailure {
103 EntityList returnList = null;
104 if (content != null) {
105 // get all to_topic from media_x_topic
106 String id = content.getId();
107 //this is not supported by mysql
108 String subselect = "id in (select media_id from " + theTable +
109 " where content_id=" + id + ")";
112 // media should stay in uploaded order. this is especially important
113 // for photo stories which require a specific chronologic order.
114 // this is why we have the the second parameter "id"
115 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
118 catch (Exception e) {
119 logger.error("DatabaseContentToMedia.getAudio: " + e.toString());
120 throw new StorageObjectFailure("DatabaseContentToMedia.getAudio: " +
128 * get all the video belonging to a content entity
131 public EntityList getVideo(EntityContent content) throws StorageObjectFailure {
132 EntityList returnList = null;
133 if (content != null) {
134 // get all to_topic from media_x_topic
135 String id = content.getId();
136 //this is not supported by mysql
137 String subselect = "id in (select media_id from " + theTable +
138 " where content_id=" + id + ")";
141 // media should stay in uploaded order. this is especially important
142 // for photo stories which require a specific chronologic order.
143 // this is why we have the the second parameter "id"
144 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
147 catch (Exception e) {
148 logger.error("DatabaseContentToMedia.getVideo: " + e.toString());
149 throw new StorageObjectFailure("DatabaseContentToMedia.getVideo: " +
157 * get all the images belonging to a content entity
160 public EntityList getImages(EntityContent content) throws
161 StorageObjectFailure {
162 EntityList returnList = null;
163 if (content != null) {
164 // get all to_topic from media_x_topic
165 String id = content.getId();
166 //this is not supported by mysql
167 String subselect = "id in (select media_id from " + theTable +
168 " where content_id=" + id + ")";
171 // media should stay in uploaded order. this is especially important
172 // for photo stories which require a specific chronologic order.
173 // this is why we have the the second parameter "id"
174 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
177 catch (Exception e) {
178 logger.error("DatabaseContentToMedia.getImages: " + e.toString());
179 throw new StorageObjectFailure("DatabaseContentToMedia.getImages: " +
187 * get all the uploaded/other Media belonging to a content entity
190 public EntityList getOther(EntityContent content) throws StorageObjectFailure {
191 /** @todo this should only fetch published media / rk */
193 EntityList returnList = null;
194 if (content != null) {
195 // get all to_topic from media_x_topic
196 String id = content.getId();
197 //this is not supported by mysql
198 String subselect = "id in (select media_id from " + theTable +
199 " where content_id=" + id + ")";
202 // media should stay in uploaded order. this is especially important
203 // for photo stories which require a specific chronologic order.
204 // this is why we have the the second parameter "id"
205 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
208 catch (Exception e) {
209 logger.error("DatabaseContentToMedia.getOther: " + e.toString());
210 throw new StorageObjectFailure("DatabaseContentToMedia.getOther: " + e.toString(), e);
217 * get all the uploaded/other Media belonging to a content entity
220 public EntityList getUploadedMedia(EntityContent content) throws
221 StorageObjectFailure {
222 /** @todo this should only fetch published media / rk */
224 EntityList returnList = null;
225 if (content != null) {
226 // get all to_topic from media_x_topic
227 String id = content.getId();
228 //this is not supported by mysql
229 String subselect = "id in (select media_id from " + theTable +
230 " where content_id=" + id + ")";
233 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
237 catch (Exception e) {
238 logger.error("DatabaseContentToMedia.getUploadedMedia: " + e.toString());
239 throw new StorageObjectFailure(
240 "DatabaseContentToMedia.getUploadedMedia: " + e.toString(), e);
246 public void setMedia(String contentId, String[] mediaId) throws
247 StorageObjectFailure {
248 if (contentId == null) {
251 if (mediaId == null || mediaId[0] == null) {
254 //first delete all row with content_id=contentId
255 String sql = "delete from " + theTable + " where content_id=" + contentId;
257 Connection con = null;
258 Statement stmt = null;
260 con = getPooledCon();
261 // should be a preparedStatement because is faster
262 stmt = con.createStatement();
263 ResultSet rs = executeSql(stmt, sql);
265 catch (Exception e) {
266 logger.error("-- set media failed -- delete");
267 throw new StorageObjectFailure("-- set media failed -- delete", e);
270 freeConnection(con, stmt);
274 //first delete all row with content_id=contentId
275 for (int i = 0; i < mediaId.length; i++) {
276 sql = "insert into " + theTable + " (content_id,media_id) values ("
277 + contentId + "," + mediaId[i] + ")";
279 con = getPooledCon();
280 // should be a preparedStatement because is faster
281 stmt = con.createStatement();
282 int rs = executeUpdate(stmt, sql);
284 catch (Exception e) {
285 logger.error("-- set topics failed -- insert");
286 throw new StorageObjectFailure("-- set topics failed -- insert ", e);
289 freeConnection(con, stmt);
294 public void addMedia(String contentId, String mediaId) throws
295 StorageObjectFailure {
296 if (contentId == null && mediaId == null) {
300 Connection con = null;
301 Statement stmt = null;
304 String sql = "insert into " + theTable + " (content_id,media_id) values ("
305 + contentId + "," + mediaId + ")";
307 con = getPooledCon();
308 // should be a preparedStatement because is faster
309 stmt = con.createStatement();
310 int rs = executeUpdate(stmt, sql);
312 catch (Exception e) {
313 logger.error("-- add media failed -- insert");
314 throw new StorageObjectFailure("-- add media failed -- insert ", e);
317 freeConnection(con, stmt);
321 public void setMedia(String contentId, String mediaId) throws
322 StorageObjectFailure {
323 if (contentId == null && mediaId == null) {
326 //first delete all row with content_id=contentId
327 String sql = "delete from " + theTable + " where content_id=" + contentId;
329 Connection con = null;
330 Statement stmt = null;
332 con = getPooledCon();
333 // should be a preparedStatement because is faster
334 stmt = con.createStatement();
335 int rs = executeUpdate(stmt, sql);
337 catch (Exception e) {
338 logger.error("-- set media failed -- delete");
339 throw new StorageObjectFailure("-- set media failed -- delete ", e);
342 freeConnection(con, stmt);
346 //first delete all row with content_id=contentId
348 sql = "insert into " + theTable + " (content_id,media_id) values ("
349 + contentId + "," + mediaId + ")";
351 con = getPooledCon();
352 // should be a preparedStatement because is faster
353 stmt = con.createStatement();
354 int rs = executeUpdate(stmt, sql);
356 catch (Exception e) {
357 logger.error("-- set media failed -- insert");
358 throw new StorageObjectFailure("-- set media failed -- insert ", e);
361 freeConnection(con, stmt);
365 public void deleteByContentId(String contentId) throws StorageObjectFailure {
366 if (contentId == null) {
367 //theLog.printDebugInfo("-- delete topics failed -- no content id");
370 //delete all row with content_id=contentId
371 String sql = "delete from " + theTable + " where content_id=" + contentId;
373 Connection con = null;
374 Statement stmt = null;
376 con = getPooledCon();
377 // should be a preparedStatement because is faster
378 stmt = con.createStatement();
379 int rs = executeUpdate(stmt, sql);
381 catch (Exception e) {
382 logger.error("-- delete by contentId failed ");
383 throw new StorageObjectFailure(
384 "-- delete by content id failed -- delete ", e);
387 freeConnection(con, stmt);
391 public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
392 if (mediaId == null) {
393 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
396 //delete all row with content_id=contentId
397 String sql = "delete from " + theTable + " where media_id=" + mediaId;
399 Connection con = null;
400 Statement stmt = null;
402 con = getPooledCon();
403 // should be a preparedStatement because is faster
404 stmt = con.createStatement();
405 int rs = executeUpdate(stmt, sql);
406 logger.debug("-- delete media success ");
408 catch (Exception e) {
409 logger.error("-- delete media failed ");
410 throw new StorageObjectFailure("-- delete by media id failed -- ", e);
413 freeConnection(con, stmt);
417 public void delete(String contentId, String mediaId) throws
418 StorageObjectFailure {
419 if (mediaId == null || contentId == null) {
420 logger.debug("-- delete media failed -- missing parameter");
423 //delete all row with content_id=contentId and media_id=mediaId
424 String sql = "delete from " + theTable + " where media_id=" + mediaId +
425 " and content_id= " + contentId;
427 Connection con = null;
428 Statement stmt = null;
430 con = getPooledCon();
431 // should be a preparedStatement because is faster
432 stmt = con.createStatement();
433 int rs = executeUpdate(stmt, sql);
434 logger.debug("-- delete content_x_media success ");
436 catch (Exception e) {
437 logger.error("-- delete content_x_media failed ");
438 throw new StorageObjectFailure("-- delete content_x_media failed -- ", e);
441 freeConnection(con, stmt);
445 public EntityList getContent(EntityUploadedMedia media) throws
446 StorageObjectFailure {
447 EntityList returnList = null;
449 String id = media.getId();
450 String select = "select content_id from " + theTable + " where media_id=" +
453 // execute select statement
454 Connection con = null;
455 Statement stmt = null;
457 con = getPooledCon();
458 // should be a preparedStatement because is faster
459 stmt = con.createStatement();
460 ResultSet rs = executeSql(stmt, select);
462 String mediaSelect = "id IN (";
463 boolean first = true;
467 mediaSelect += rs.getString(1);
472 returnList = DatabaseContent.getInstance().selectByWhereClause(
476 catch (Exception e) {
477 logger.error("-- get content failed");
478 throw new StorageObjectFailure("-- get content failed -- ", e);
481 freeConnection(con, stmt);
488 * Returns a EntityList with all content-objects having a relation to a media
491 public EntityList getContent() throws StorageObjectFailure {
492 EntityList returnList = null;
494 String select = "select distinct content_id from " + theTable;
495 // execute select statement
496 Connection con = null;
497 Statement stmt = null;
499 con = getPooledCon();
500 // should be a preparedStatement because is faster
501 stmt = con.createStatement();
502 ResultSet rs = executeSql(stmt, select);
504 String mediaSelect = "id IN (";
505 boolean first = true;
509 mediaSelect += rs.getString(1);
514 returnList = DatabaseContent.getInstance().selectByWhereClause(
515 mediaSelect, "webdb_lastchange desc");
518 catch (Exception e) {
519 logger.error("-- get content failed");
520 throw new StorageObjectFailure("-- get content failed -- ", e);
523 freeConnection(con, stmt);