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.EntityContent;
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.19.2.2 2003/11/24 23:37:18 rk 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 // TODO make this a count statement
80 if (selectByWhereClause("content_id=" + content.getId(), -1).size() ==
87 logger.error("DatabaseContentToMedia.hasMedia: " + e.toString());
88 throw new StorageObjectFailure("DatabaseContentToMedia.hasMedia: " +
93 logger.error("DatabaseContentToMedia.hasMedia: content == null");
94 throw new StorageObjectExc(
95 "DatabaseContentToMedia.hasMedia: content == null");
100 * get all the audio belonging to a content entity
103 public EntityList getAudio(EntityContent content) throws StorageObjectFailure {
104 EntityList returnList = null;
105 if (content != null) {
106 // TODO rewrite as relational sql
107 // get all to_topic from media_x_topic
108 String id = content.getId();
109 //this is not supported by mysql
110 String subselect = "id in (select media_id from " + theTable +
111 " where content_id=" + id + ")";
114 // media should stay in uploaded order. this is especially important
115 // for photo stories which require a specific chronologic order.
116 // this is why we have the the second parameter "id"
117 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
120 catch (Exception e) {
121 logger.error("DatabaseContentToMedia.getAudio: " + e.toString());
122 throw new StorageObjectFailure("DatabaseContentToMedia.getAudio: " +
130 * get all the video belonging to a content entity
133 public EntityList getVideo(EntityContent content) throws StorageObjectFailure {
134 EntityList returnList = null;
135 if (content != null) {
136 // TODO rewrite as relational sql
137 // get all to_topic from media_x_topic
138 String id = content.getId();
139 //this is not supported by mysql
140 String subselect = "id in (select media_id from " + theTable +
141 " where content_id=" + id + ")";
144 // media should stay in uploaded order. this is especially important
145 // for photo stories which require a specific chronologic order.
146 // this is why we have the the second parameter "id"
147 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
150 catch (Exception e) {
151 logger.error("DatabaseContentToMedia.getVideo: " + e.toString());
152 throw new StorageObjectFailure("DatabaseContentToMedia.getVideo: " +
160 * get all the images belonging to a content entity
163 public EntityList getImages(EntityContent content) throws
164 StorageObjectFailure {
165 EntityList returnList = null;
166 if (content != null) {
167 // TODO rewrite as relational sql
168 // get all to_topic from media_x_topic
169 String id = content.getId();
170 //this is not supported by mysql
171 String subselect = "id in (select media_id from " + theTable +
172 " where content_id=" + id + ")";
175 // media should stay in uploaded order. this is especially important
176 // for photo stories which require a specific chronologic order.
177 // this is why we have the the second parameter "id"
178 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
181 catch (Exception e) {
182 logger.error("DatabaseContentToMedia.getImages: " + e.toString());
183 throw new StorageObjectFailure("DatabaseContentToMedia.getImages: " +
191 * get all the uploaded/other Media belonging to a content entity
194 public EntityList getOther(EntityContent content) throws StorageObjectFailure {
195 /** @todo this should only fetch published media / rk */
197 EntityList returnList = null;
198 if (content != null) {
199 // TODO rewrite as relational sql
200 // get all to_topic from media_x_topic
201 String id = content.getId();
202 //this is not supported by mysql
203 String subselect = "id in (select media_id from " + theTable +
204 " where content_id=" + id + ")";
207 // media should stay in uploaded order. this is especially important
208 // for photo stories which require a specific chronologic order.
209 // this is why we have the the second parameter "id"
210 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
213 catch (Exception e) {
214 logger.error("DatabaseContentToMedia.getOther: " + e.toString());
215 throw new StorageObjectFailure("DatabaseContentToMedia.getOther: " + e.toString(), e);
222 * get all the uploaded/other Media belonging to a content entity
225 public EntityList getUploadedMedia(EntityContent content) throws
226 StorageObjectFailure {
227 /** @todo this should only fetch published media / rk */
229 EntityList returnList = null;
230 if (content != null) {
231 // TODO rewrite as relational sql
232 // get all to_topic from media_x_topic
233 String id = content.getId();
234 //this is not supported by mysql
235 String subselect = "id in (select media_id from " + theTable +
236 " where content_id=" + id + ")";
239 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
243 catch (Exception e) {
244 logger.error("DatabaseContentToMedia.getUploadedMedia: " + e.toString());
245 throw new StorageObjectFailure(
246 "DatabaseContentToMedia.getUploadedMedia: " + e.toString(), e);
252 public void setMedia(String contentId, String[] mediaId) throws
253 StorageObjectFailure {
254 if (contentId == null) {
257 if (mediaId == null || mediaId[0] == null) {
260 //first delete all row with content_id=contentId
261 String sql = "delete from " + theTable + " where content_id=" + contentId;
263 Connection con = null;
264 Statement stmt = null;
266 con = getPooledCon();
267 // should be a preparedStatement because is faster
268 stmt = con.createStatement();
269 ResultSet rs = executeSql(stmt, sql);
271 catch (Exception e) {
272 logger.error("-- set media failed -- delete");
273 throw new StorageObjectFailure("-- set media failed -- delete", e);
276 freeConnection(con, stmt);
280 //first delete all row with content_id=contentId
281 for (int i = 0; i < mediaId.length; i++) {
282 sql = "insert into " + theTable + " (content_id,media_id) values ("
283 + contentId + "," + mediaId[i] + ")";
285 con = getPooledCon();
286 // should be a preparedStatement because is faster
287 stmt = con.createStatement();
288 int rs = executeUpdate(stmt, sql);
290 catch (Exception e) {
291 logger.error("-- set topics failed -- insert");
292 throw new StorageObjectFailure("-- set topics failed -- insert ", e);
295 freeConnection(con, stmt);
300 public void addMedia(String contentId, String mediaId) throws
301 StorageObjectFailure {
302 if (contentId == null && mediaId == null) {
306 Connection con = null;
307 Statement stmt = null;
310 String sql = "insert into " + theTable + " (content_id,media_id) values ("
311 + contentId + "," + mediaId + ")";
313 con = getPooledCon();
314 // should be a preparedStatement because is faster
315 stmt = con.createStatement();
316 int rs = executeUpdate(stmt, sql);
318 catch (Exception e) {
319 logger.error("-- add media failed -- insert");
320 throw new StorageObjectFailure("-- add media failed -- insert ", e);
323 freeConnection(con, stmt);
327 public void setMedia(String contentId, String mediaId) throws
328 StorageObjectFailure {
329 if (contentId == null && mediaId == null) {
332 //first delete all row with content_id=contentId
333 String sql = "delete from " + theTable + " where content_id=" + contentId;
335 Connection con = null;
336 Statement stmt = null;
338 con = getPooledCon();
339 // should be a preparedStatement because is faster
340 stmt = con.createStatement();
341 int rs = executeUpdate(stmt, sql);
343 catch (Exception e) {
344 logger.error("-- set media failed -- delete");
345 throw new StorageObjectFailure("-- set media failed -- delete ", e);
348 freeConnection(con, stmt);
352 //first delete all row with content_id=contentId
354 sql = "insert into " + theTable + " (content_id,media_id) values ("
355 + contentId + "," + mediaId + ")";
357 con = getPooledCon();
358 // should be a preparedStatement because is faster
359 stmt = con.createStatement();
360 int rs = executeUpdate(stmt, sql);
362 catch (Exception e) {
363 logger.error("-- set media failed -- insert");
364 throw new StorageObjectFailure("-- set media failed -- insert ", e);
367 freeConnection(con, stmt);
371 public void deleteByContentId(String contentId) throws StorageObjectFailure {
372 if (contentId == null) {
373 //theLog.printDebugInfo("-- delete topics failed -- no content id");
376 //delete all row with content_id=contentId
377 String sql = "delete from " + theTable + " where content_id=" + contentId;
379 Connection con = null;
380 Statement stmt = null;
382 con = getPooledCon();
383 // should be a preparedStatement because is faster
384 stmt = con.createStatement();
385 int rs = executeUpdate(stmt, sql);
387 catch (Exception e) {
388 logger.error("-- delete by contentId failed ");
389 throw new StorageObjectFailure(
390 "-- delete by content id failed -- delete ", e);
393 freeConnection(con, stmt);
397 public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
398 if (mediaId == null) {
399 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
402 //delete all row with content_id=contentId
403 String sql = "delete from " + theTable + " where media_id=" + mediaId;
405 Connection con = null;
406 Statement stmt = null;
408 con = getPooledCon();
409 // should be a preparedStatement because is faster
410 stmt = con.createStatement();
411 int rs = executeUpdate(stmt, sql);
412 logger.debug("-- delete media success ");
414 catch (Exception e) {
415 logger.error("-- delete media failed ");
416 throw new StorageObjectFailure("-- delete by media id failed -- ", e);
419 freeConnection(con, stmt);
423 public void delete(String contentId, String mediaId) throws
424 StorageObjectFailure {
425 if (mediaId == null || contentId == null) {
426 logger.debug("-- delete media failed -- missing parameter");
429 //delete all row with content_id=contentId and media_id=mediaId
430 String sql = "delete from " + theTable + " where media_id=" + mediaId +
431 " and content_id= " + contentId;
433 Connection con = null;
434 Statement stmt = null;
436 con = getPooledCon();
437 // should be a preparedStatement because is faster
438 stmt = con.createStatement();
439 int rs = executeUpdate(stmt, sql);
440 logger.debug("-- delete content_x_media success ");
442 catch (Exception e) {
443 logger.error("-- delete content_x_media failed ");
444 throw new StorageObjectFailure("-- delete content_x_media failed -- ", e);
447 freeConnection(con, stmt);
451 public EntityList getContent(EntityUploadedMedia media) throws
452 StorageObjectFailure {
453 EntityList returnList = null;
455 // TODO rewrite as relational sql
456 String id = media.getId();
457 String select = "select content_id from " + theTable + " where media_id=" +
460 // execute select statement
461 Connection con = null;
462 Statement stmt = null;
464 con = getPooledCon();
465 // should be a preparedStatement because is faster
466 stmt = con.createStatement();
467 ResultSet rs = executeSql(stmt, select);
469 String mediaSelect = "id IN (";
470 boolean first = true;
474 mediaSelect += rs.getString(1);
479 returnList = DatabaseContent.getInstance().selectByWhereClause(
483 catch (Exception e) {
484 logger.error("-- get content failed");
485 throw new StorageObjectFailure("-- get content failed -- ", e);
488 freeConnection(con, stmt);
495 * Returns a EntityList with all content-objects having a relation to a media
498 public EntityList getContent() throws StorageObjectFailure {
499 EntityList returnList = null;
501 String select = "select distinct content_id from " + theTable;
502 // execute select statement
503 Connection con = null;
504 Statement stmt = null;
506 // TODO rewrite as relational sql
507 con = getPooledCon();
508 // should be a preparedStatement because is faster
509 stmt = con.createStatement();
510 ResultSet rs = executeSql(stmt, select);
512 String mediaSelect = "id IN (";
513 boolean first = true;
517 mediaSelect += rs.getString(1);
522 returnList = DatabaseContent.getInstance().selectByWhereClause(
523 mediaSelect, "webdb_lastchange desc");
526 catch (Exception e) {
527 logger.error("-- get content failed");
528 throw new StorageObjectFailure("-- get content failed -- ", e);
531 freeConnection(con, stmt);