2 * Copyright (C) 2001, 2002 The Mir-coders group
\r
4 * This file is part of Mir.
\r
6 * Mir is free software; you can redistribute it and/or modify
\r
7 * it under the terms of the GNU General Public License as published by
\r
8 * the Free Software Foundation; either version 2 of the License, or
\r
9 * (at your option) any later version.
\r
11 * Mir is distributed in the hope that it will be useful,
\r
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
\r
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
\r
14 * GNU General Public License for more details.
\r
16 * You should have received a copy of the GNU General Public License
\r
17 * along with Mir; if not, write to the Free Software
\r
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
\r
20 * In addition, as a special exception, The Mir-coders gives permission to link
\r
21 * the code of this program with the com.oreilly.servlet library, any library
\r
22 * licensed under the Apache Software License, The Sun (tm) Java Advanced
\r
23 * Imaging library (JAI), The Sun JIMI library (or with modified versions of
\r
24 * the above that use the same license as the above), and distribute linked
\r
25 * combinations including the two. You must obey the GNU General Public
\r
26 * License in all respects for all of the code used other than the above
\r
27 * mentioned libraries. If you modify this file, you may extend this exception
\r
28 * to your version of the file, but you are not obligated to do so. If you do
\r
29 * not wish to do so, delete this exception statement from your version.
\r
32 package mircoders.storage;
\r
34 import java.sql.Connection;
\r
35 import java.sql.ResultSet;
\r
36 import java.sql.Statement;
\r
38 import mir.entity.EntityList;
\r
39 import mir.log.LoggerWrapper;
\r
40 import mir.storage.Database;
\r
41 import mir.storage.StorageObject;
\r
42 import mir.storage.StorageObjectExc;
\r
43 import mir.storage.StorageObjectFailure;
\r
44 import mircoders.entity.EntityContent;
\r
45 import mircoders.entity.EntityUploadedMedia;
\r
48 * <b>implements abstract DB connection to the content_x_media SQL table
\r
50 * @author RK, mir-coders group
\r
51 * @version $Id: DatabaseContentToMedia.java,v 1.14 2003/03/05 19:23:17 idfx Exp $
\r
55 public class DatabaseContentToMedia extends Database implements StorageObject{
\r
57 private static DatabaseContentToMedia instance;
\r
59 // the following *has* to be sychronized cause this static method
\r
60 // could get preemted and we could end up with 2 instances of DatabaseFoo.
\r
61 // see the "Singletons with needles and thread" article at JavaWorld -mh
\r
62 public synchronized static DatabaseContentToMedia getInstance() throws
\r
63 StorageObjectFailure {
\r
64 if (instance == null) {
\r
65 instance = new DatabaseContentToMedia();
\r
66 instance.myselfDatabase = instance;
\r
71 private DatabaseContentToMedia() throws StorageObjectFailure {
\r
73 logger = new LoggerWrapper("Database.ContentToMedia");
\r
75 hasTimestamp = false;
\r
76 theTable = "content_x_media";
\r
78 theEntityClass = mir.entity.GenericEntity.class;
\r
82 * get all the media-files belonging to a content entity
\r
85 public EntityList getMedia(EntityContent content) throws StorageObjectFailure {
\r
86 EntityList returnList = null;
\r
87 if (content != null) {
\r
88 // get all to_topic from media_x_topic
\r
89 String id = content.getId();
\r
90 String subselect = "id in (select media_id from " + theTable +
\r
91 " where content_id=" + id + ")";
\r
94 // media should stay in uploaded order. this is especially important
\r
95 // for photo stories which require a specific chronologic order.
\r
96 // this is why we have the the second parameter "id"
\r
97 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,
\r
100 catch (Throwable e) {
\r
101 logger.debug("-- get media failed " + e.toString());
\r
102 throw new StorageObjectFailure("-- get media failed ", e);
\r
108 public boolean hasMedia(EntityContent content) throws StorageObjectFailure,
\r
110 if (content != null) {
\r
112 if (selectByWhereClause("content_id=" + content.getId(), -1).size() ==
\r
118 catch (Exception e) {
\r
119 logger.error("DatabaseContentToMedia.hasMedia: " + e.toString());
\r
120 throw new StorageObjectFailure("DatabaseContentToMedia.hasMedia: " +
\r
125 logger.error("DatabaseContentToMedia.hasMedia: content == null");
\r
126 throw new StorageObjectExc(
\r
127 "DatabaseContentToMedia.hasMedia: content == null");
\r
132 * get all the audio belonging to a content entity
\r
135 public EntityList getAudio(EntityContent content) throws StorageObjectFailure {
\r
136 EntityList returnList = null;
\r
137 if (content != null) {
\r
138 // get all to_topic from media_x_topic
\r
139 String id = content.getId();
\r
140 //this is not supported by mysql
\r
141 String subselect = "id in (select media_id from " + theTable +
\r
142 " where content_id=" + id + ")";
\r
145 // media should stay in uploaded order. this is especially important
\r
146 // for photo stories which require a specific chronologic order.
\r
147 // this is why we have the the second parameter "id"
\r
148 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
\r
151 catch (Exception e) {
\r
152 logger.error("DatabaseContentToMedia.getAudio: " + e.toString());
\r
153 throw new StorageObjectFailure("DatabaseContentToMedia.getAudio: " +
\r
161 * get all the video belonging to a content entity
\r
164 public EntityList getVideo(EntityContent content) throws StorageObjectFailure {
\r
165 EntityList returnList = null;
\r
166 if (content != null) {
\r
167 // get all to_topic from media_x_topic
\r
168 String id = content.getId();
\r
169 //this is not supported by mysql
\r
170 String subselect = "id in (select media_id from " + theTable +
\r
171 " where content_id=" + id + ")";
\r
174 // media should stay in uploaded order. this is especially important
\r
175 // for photo stories which require a specific chronologic order.
\r
176 // this is why we have the the second parameter "id"
\r
177 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
\r
180 catch (Exception e) {
\r
181 logger.error("DatabaseContentToMedia.getVideo: " + e.toString());
\r
182 throw new StorageObjectFailure("DatabaseContentToMedia.getVideo: " +
\r
190 * get all the images belonging to a content entity
\r
193 public EntityList getImages(EntityContent content) throws
\r
194 StorageObjectFailure {
\r
195 EntityList returnList = null;
\r
196 if (content != null) {
\r
197 // get all to_topic from media_x_topic
\r
198 String id = content.getId();
\r
199 //this is not supported by mysql
\r
200 String subselect = "id in (select media_id from " + theTable +
\r
201 " where content_id=" + id + ")";
\r
204 // media should stay in uploaded order. this is especially important
\r
205 // for photo stories which require a specific chronologic order.
\r
206 // this is why we have the the second parameter "id"
\r
207 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
\r
210 catch (Exception e) {
\r
211 logger.error("DatabaseContentToMedia.getImages: " + e.toString());
\r
212 throw new StorageObjectFailure("DatabaseContentToMedia.getImages: " +
\r
220 * get all the uploaded/other Media belonging to a content entity
\r
223 public EntityList getOther(EntityContent content) throws StorageObjectFailure {
\r
224 /** @todo this should only fetch published media / rk */
\r
226 EntityList returnList = null;
\r
227 if (content != null) {
\r
228 // get all to_topic from media_x_topic
\r
229 String id = content.getId();
\r
230 //this is not supported by mysql
\r
231 String subselect = "id in (select media_id from " + theTable +
\r
232 " where content_id=" + id + ")";
\r
235 // media should stay in uploaded order. this is especially important
\r
236 // for photo stories which require a specific chronologic order.
\r
237 // this is why we have the the second parameter "id"
\r
238 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
\r
241 catch (Exception e) {
\r
242 e.printStackTrace();
\r
243 logger.error("DatabaseContentToMedia.getOther: " + e.toString());
\r
244 throw new StorageObjectFailure("DatabaseContentToMedia.getOther: " +
\r
252 * get all the uploaded/other Media belonging to a content entity
\r
255 public EntityList getUploadedMedia(EntityContent content) throws
\r
256 StorageObjectFailure {
\r
257 /** @todo this should only fetch published media / rk */
\r
259 EntityList returnList = null;
\r
260 if (content != null) {
\r
261 // get all to_topic from media_x_topic
\r
262 String id = content.getId();
\r
263 //this is not supported by mysql
\r
264 String subselect = "id in (select media_id from " + theTable +
\r
265 " where content_id=" + id + ")";
\r
268 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
\r
272 catch (Exception e) {
\r
273 e.printStackTrace();
\r
274 logger.error("DatabaseContentToMedia.getUploadedMedia: " + e.toString());
\r
275 throw new StorageObjectFailure(
\r
276 "DatabaseContentToMedia.getUploadedMedia: " + e.toString(), e);
\r
282 public void setMedia(String contentId, String[] mediaId) throws
\r
283 StorageObjectFailure {
\r
284 if (contentId == null) {
\r
287 if (mediaId == null || mediaId[0] == null) {
\r
290 //first delete all row with content_id=contentId
\r
291 String sql = "delete from " + theTable + " where content_id=" + contentId;
\r
293 Connection con = null;
\r
294 Statement stmt = null;
\r
296 con = getPooledCon();
\r
297 // should be a preparedStatement because is faster
\r
298 stmt = con.createStatement();
\r
299 ResultSet rs = executeSql(stmt, sql);
\r
301 catch (Exception e) {
\r
302 logger.error("-- set media failed -- delete");
\r
303 throw new StorageObjectFailure("-- set media failed -- delete", e);
\r
306 freeConnection(con, stmt);
\r
310 //first delete all row with content_id=contentId
\r
311 for (int i = 0; i < mediaId.length; i++) {
\r
312 sql = "insert into " + theTable + " (content_id,media_id) values ("
\r
313 + contentId + "," + mediaId[i] + ")";
\r
315 con = getPooledCon();
\r
316 // should be a preparedStatement because is faster
\r
317 stmt = con.createStatement();
\r
318 int rs = executeUpdate(stmt, sql);
\r
320 catch (Exception e) {
\r
321 logger.error("-- set topics failed -- insert");
\r
322 throw new StorageObjectFailure("-- set topics failed -- insert ", e);
\r
325 freeConnection(con, stmt);
\r
330 public void addMedia(String contentId, String mediaId) throws
\r
331 StorageObjectFailure {
\r
332 if (contentId == null && mediaId == null) {
\r
336 Connection con = null;
\r
337 Statement stmt = null;
\r
340 String sql = "insert into " + theTable + " (content_id,media_id) values ("
\r
341 + contentId + "," + mediaId + ")";
\r
343 con = getPooledCon();
\r
344 // should be a preparedStatement because is faster
\r
345 stmt = con.createStatement();
\r
346 int rs = executeUpdate(stmt, sql);
\r
348 catch (Exception e) {
\r
349 logger.error("-- add media failed -- insert");
\r
350 throw new StorageObjectFailure("-- add media failed -- insert ", e);
\r
353 freeConnection(con, stmt);
\r
357 public void setMedia(String contentId, String mediaId) throws
\r
358 StorageObjectFailure {
\r
359 if (contentId == null && mediaId == null) {
\r
362 //first delete all row with content_id=contentId
\r
363 String sql = "delete from " + theTable + " where content_id=" + contentId;
\r
365 Connection con = null;
\r
366 Statement stmt = null;
\r
368 con = getPooledCon();
\r
369 // should be a preparedStatement because is faster
\r
370 stmt = con.createStatement();
\r
371 int rs = executeUpdate(stmt, sql);
\r
373 catch (Exception e) {
\r
374 logger.error("-- set media failed -- delete");
\r
375 throw new StorageObjectFailure("-- set media failed -- delete ", e);
\r
378 freeConnection(con, stmt);
\r
382 //first delete all row with content_id=contentId
\r
384 sql = "insert into " + theTable + " (content_id,media_id) values ("
\r
385 + contentId + "," + mediaId + ")";
\r
387 con = getPooledCon();
\r
388 // should be a preparedStatement because is faster
\r
389 stmt = con.createStatement();
\r
390 int rs = executeUpdate(stmt, sql);
\r
392 catch (Exception e) {
\r
393 logger.error("-- set media failed -- insert");
\r
394 throw new StorageObjectFailure("-- set media failed -- insert ", e);
\r
397 freeConnection(con, stmt);
\r
401 public void deleteByContentId(String contentId) throws StorageObjectFailure {
\r
402 if (contentId == null) {
\r
403 //theLog.printDebugInfo("-- delete topics failed -- no content id");
\r
406 //delete all row with content_id=contentId
\r
407 String sql = "delete from " + theTable + " where content_id=" + contentId;
\r
409 Connection con = null;
\r
410 Statement stmt = null;
\r
412 con = getPooledCon();
\r
413 // should be a preparedStatement because is faster
\r
414 stmt = con.createStatement();
\r
415 int rs = executeUpdate(stmt, sql);
\r
417 catch (Exception e) {
\r
418 logger.error("-- delete by contentId failed ");
\r
419 throw new StorageObjectFailure(
\r
420 "-- delete by content id failed -- delete ", e);
\r
423 freeConnection(con, stmt);
\r
427 public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
\r
428 if (mediaId == null) {
\r
429 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
\r
432 //delete all row with content_id=contentId
\r
433 String sql = "delete from " + theTable + " where media_id=" + mediaId;
\r
435 Connection con = null;
\r
436 Statement stmt = null;
\r
438 con = getPooledCon();
\r
439 // should be a preparedStatement because is faster
\r
440 stmt = con.createStatement();
\r
441 int rs = executeUpdate(stmt, sql);
\r
442 logger.debug("-- delete media success ");
\r
444 catch (Exception e) {
\r
445 logger.error("-- delete media failed ");
\r
446 throw new StorageObjectFailure("-- delete by media id failed -- ", e);
\r
449 freeConnection(con, stmt);
\r
453 public void delete(String contentId, String mediaId) throws
\r
454 StorageObjectFailure {
\r
455 if (mediaId == null || contentId == null) {
\r
456 logger.debug("-- delete media failed -- missing parameter");
\r
459 //delete all row with content_id=contentId and media_id=mediaId
\r
460 String sql = "delete from " + theTable + " where media_id=" + mediaId +
\r
461 " and content_id= " + contentId;
\r
463 Connection con = null;
\r
464 Statement stmt = null;
\r
466 con = getPooledCon();
\r
467 // should be a preparedStatement because is faster
\r
468 stmt = con.createStatement();
\r
469 int rs = executeUpdate(stmt, sql);
\r
470 logger.debug("-- delete content_x_media success ");
\r
472 catch (Exception e) {
\r
473 logger.error("-- delete content_x_media failed ");
\r
474 throw new StorageObjectFailure("-- delete content_x_media failed -- ", e);
\r
477 freeConnection(con, stmt);
\r
481 public EntityList getContent(EntityUploadedMedia media) throws
\r
482 StorageObjectFailure {
\r
483 EntityList returnList = null;
\r
484 if (media != null) {
\r
485 String id = media.getId();
\r
486 String select = "select content_id from " + theTable + " where media_id=" +
\r
489 // execute select statement
\r
490 Connection con = null;
\r
491 Statement stmt = null;
\r
493 con = getPooledCon();
\r
494 // should be a preparedStatement because is faster
\r
495 stmt = con.createStatement();
\r
496 ResultSet rs = executeSql(stmt, select);
\r
498 String mediaSelect = "id IN (";
\r
499 boolean first = true;
\r
500 while (rs.next()) {
\r
501 if (first == false)
\r
502 mediaSelect += ",";
\r
503 mediaSelect += rs.getString(1);
\r
506 mediaSelect += ")";
\r
507 if (first == false)
\r
508 returnList = DatabaseContent.getInstance().selectByWhereClause(
\r
512 catch (Exception e) {
\r
513 logger.error("-- get content failed");
\r
514 throw new StorageObjectFailure("-- get content failed -- ", e);
\r
517 freeConnection(con, stmt);
\r
524 * Returns a EntityList with all content-objects having a relation to a media
\r
527 public EntityList getContent() throws StorageObjectFailure {
\r
528 EntityList returnList = null;
\r
530 String select = "select distinct content_id from " + theTable;
\r
531 // execute select statement
\r
532 Connection con = null;
\r
533 Statement stmt = null;
\r
535 con = getPooledCon();
\r
536 // should be a preparedStatement because is faster
\r
537 stmt = con.createStatement();
\r
538 ResultSet rs = executeSql(stmt, select);
\r
540 String mediaSelect = "id IN (";
\r
541 boolean first = true;
\r
542 while (rs.next()) {
\r
543 if (first == false)
\r
544 mediaSelect += ",";
\r
545 mediaSelect += rs.getString(1);
\r
548 mediaSelect += ")";
\r
549 if (first == false)
\r
550 returnList = DatabaseContent.getInstance().selectByWhereClause(
\r
551 mediaSelect, "webdb_lastchange desc");
\r
554 catch (Exception e) {
\r
555 logger.error("-- get content failed");
\r
556 throw new StorageObjectFailure("-- get content failed -- ", e);
\r
559 freeConnection(con, stmt);
\r