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;
39 import freemarker.template.*;
45 import mircoders.entity.*;
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.11 2002/12/06 08:08:44 mh Exp $
55 public class DatabaseContentToMedia extends Database implements StorageObject{
57 private static DatabaseContentToMedia instance;
59 // the following *has* to be sychronized cause this static method
60 // could get preemted and we could end up with 2 instances of DatabaseFoo.
61 // see the "Singletons with needles and thread" article at JavaWorld -mh
62 public synchronized static DatabaseContentToMedia getInstance()
63 throws StorageObjectException {
64 if (instance == null) {
65 instance = new DatabaseContentToMedia();
66 instance.myselfDatabase = instance;
71 private DatabaseContentToMedia()
72 throws StorageObjectException {
75 this.hasTimestamp = false;
76 this.theTable="content_x_media";
77 try { this.theEntityClass = Class.forName("mir.entity.GenericEntity"); }
78 catch (Exception e) { throw new StorageObjectException(e.toString()); }
82 * get all the media-files belonging to a content entity
85 public EntityList getMedia(EntityContent content)
86 throws StorageObjectException {
87 EntityList returnList=null;
88 if (content != null) {
89 // get all to_topic from media_x_topic
90 String id = content.getId();
91 //this is not supported by mysql
92 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
95 // media should stay in uploaded order. this is especially important
96 // for photo stories which require a specific chronologic order.
97 // this is why we have the the second parameter "id"
98 returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,"id",-1);
99 } catch (Exception e) {
100 theLog.printDebugInfo("-- get media failed " + e.toString());
101 throw new StorageObjectException("-- get media failed " + e.toString());
107 public boolean hasMedia(EntityContent content)
108 throws StorageObjectException {
109 String wc = "content_id="+content.getId();
110 if( content != null) {
112 if(selectByWhereClause(wc,-1).size() == 0)
116 } catch (Exception e) {
117 theLog.printError("-- hasMedia failed " + e.toString());
118 throw new StorageObjectException("-- hasMedia failed " + e.toString());
121 theLog.printError("-- hasMedia failed: content is NULL");
122 throw new StorageObjectException("-- hasMedia failed: content is NULL");
130 * get all the audio belonging to a content entity
133 public EntityList getAudio(EntityContent content)
134 throws StorageObjectException {
135 EntityList returnList=null;
136 if (content != null) {
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 + " where content_id=" + id+")";
143 // media should stay in uploaded order. this is especially important
144 // for photo stories which require a specific chronologic order.
145 // this is why we have the the second parameter "id"
146 returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,"id",-1);
147 } catch (Exception e) {
148 theLog.printDebugInfo("-- get audio failed " + e.toString());
149 throw new StorageObjectException("-- get audio failed " + e.toString());
156 * get all the video belonging to a content entity
159 public EntityList getVideo(EntityContent content)
160 throws StorageObjectException {
161 EntityList returnList=null;
162 if (content != null) {
163 // get all to_topic from media_x_topic
164 String id = content.getId();
165 //this is not supported by mysql
166 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
169 // media should stay in uploaded order. this is especially important
170 // for photo stories which require a specific chronologic order.
171 // this is why we have the the second parameter "id"
172 returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,"id",-1);
173 } catch (Exception e) {
174 theLog.printDebugInfo("-- get video failed " + e.toString());
175 throw new StorageObjectException("-- get video failed " + e.toString());
182 * get all the images belonging to a content entity
185 public EntityList getImages(EntityContent content)
186 throws StorageObjectException {
187 EntityList returnList=null;
188 if (content != null) {
189 // get all to_topic from media_x_topic
190 String id = content.getId();
191 //this is not supported by mysql
192 String subselect = "id in (select media_id from " + theTable + " where content_id=" + id+")";
195 // media should stay in uploaded order. this is especially important
196 // for photo stories which require a specific chronologic order.
197 // this is why we have the the second parameter "id"
198 returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,"id",-1);
199 } catch (Exception e) {
200 theLog.printDebugInfo("-- get images failed " + e.toString());
201 throw new StorageObjectException("-- get images failed " + e.toString());
209 * get all the uploaded/other Media belonging to a content entity
212 public EntityList getOther(EntityContent content)
213 throws StorageObjectException
215 /** @todo this should only fetch published media / rk */
217 EntityList returnList=null;
218 if (content != null) {
219 // get all to_topic from media_x_topic
220 String id = content.getId();
221 //this is not supported by mysql
222 String subselect = "id in (select media_id from " + theTable +
223 " where content_id=" + id+")";
226 // media should stay in uploaded order. this is especially important
227 // for photo stories which require a specific chronologic order.
228 // this is why we have the the second parameter "id"
229 returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
231 } catch (Exception e) {
233 theLog.printDebugInfo("-- get Other failed " + e.toString());
234 throw new StorageObjectException("-- get Other failed "
242 * get all the uploaded/other Media belonging to a content entity
245 public EntityList getUploadedMedia(EntityContent content)
246 throws StorageObjectException
248 /** @todo this should only fetch published media / rk */
250 EntityList returnList=null;
251 if (content != null) {
252 // get all to_topic from media_x_topic
253 String id = content.getId();
254 //this is not supported by mysql
255 String subselect = "id in (select media_id from " + theTable +
256 " where content_id=" + id+")";
259 returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(subselect,
261 } catch (Exception e) {
263 theLog.printDebugInfo("-- get uploadedMedia failed " + e.toString());
264 throw new StorageObjectException("-- get uploadedMedia failed "
272 public void setMedia(String contentId, String[] mediaId)
273 throws StorageObjectException {
274 if (contentId == null){
277 if (mediaId==null || mediaId[0]==null) {
280 //first delete all row with content_id=contentId
281 String sql = "delete from "+ theTable +" where content_id=" + contentId;
283 Connection con=null;Statement stmt=null;
285 con = getPooledCon();
286 // should be a preparedStatement because is faster
287 stmt = con.createStatement();
288 ResultSet rs = executeSql(stmt,sql);
289 } catch (Exception e) {
290 theLog.printDebugInfo("-- set media failed -- delete");
291 throw new StorageObjectException("-- set media failed -- delete"+e.toString());
293 freeConnection(con,stmt);
297 //first delete all row with content_id=contentId
298 for (int i=0;i<mediaId.length;i++) {
299 sql = "insert into "+ theTable +" (content_id,media_id) values ("
300 + contentId + "," + mediaId[i] + ")";
302 con = getPooledCon();
303 // should be a preparedStatement because is faster
304 stmt = con.createStatement();
305 int rs = executeUpdate(stmt,sql);
306 } catch (Exception e) {
307 theLog.printDebugInfo("-- set topics failed -- insert");
308 throw new StorageObjectException("-- set topics failed -- insert "+e.toString());
310 freeConnection(con,stmt);
315 public void addMedia(String contentId, String mediaId)
316 throws StorageObjectException {
317 if (contentId == null && mediaId == null) {
321 Connection con=null;Statement stmt=null;
324 String sql = "insert into "+ theTable +" (content_id,media_id) values ("
325 + contentId + "," + mediaId + ")";
327 con = getPooledCon();
328 // should be a preparedStatement because is faster
329 stmt = con.createStatement();
330 int rs = executeUpdate(stmt,sql);
331 } catch (Exception e) {
332 theLog.printDebugInfo("-- add media failed -- insert");
333 throw new StorageObjectException("-- add media failed -- insert "
336 freeConnection(con,stmt);
340 public void setMedia(String contentId, String mediaId)
341 throws StorageObjectException {
342 if (contentId == null && mediaId == null) {
345 //first delete all row with content_id=contentId
346 String sql = "delete from "+ theTable +" where content_id=" + contentId;
348 Connection con=null;Statement stmt=null;
350 con = getPooledCon();
351 // should be a preparedStatement because is faster
352 stmt = con.createStatement();
353 int rs = executeUpdate(stmt,sql);
354 } catch (Exception e) {
355 theLog.printDebugInfo("-- set media failed -- delete");
356 throw new StorageObjectException("-- set media failed -- delete "
359 freeConnection(con,stmt);
363 //first delete all row with content_id=contentId
365 sql = "insert into "+ theTable +" (content_id,media_id) values ("
366 + contentId + "," + mediaId + ")";
368 con = getPooledCon();
369 // should be a preparedStatement because is faster
370 stmt = con.createStatement();
371 int rs = executeUpdate(stmt,sql);
372 } catch (Exception e) {
373 theLog.printDebugInfo("-- set media failed -- insert");
374 throw new StorageObjectException("-- set media failed -- insert "
377 freeConnection(con,stmt);
381 public void deleteByContentId(String contentId)
382 throws StorageObjectException {
383 if (contentId == null) {
384 //theLog.printDebugInfo("-- delete topics failed -- no content id");
387 //delete all row with content_id=contentId
388 String sql = "delete from "+ theTable +" where content_id=" + contentId;
390 Connection con=null;Statement stmt=null;
392 con = getPooledCon();
393 // should be a preparedStatement because is faster
394 stmt = con.createStatement();
395 int rs = executeUpdate(stmt,sql);
396 } catch (Exception e) {
397 theLog.printDebugInfo("-- delete by contentId failed ");
398 throw new StorageObjectException("-- delete by content id failed -- delete "
401 freeConnection(con,stmt);
405 public void deleteByMediaId(String mediaId)
406 throws StorageObjectException {
407 if (mediaId == null) {
408 //theLog.printDebugInfo("-- delete topics failed -- no topic id");
411 //delete all row with content_id=contentId
412 String sql = "delete from "+ theTable +" where media_id=" + mediaId;
414 Connection con=null;Statement stmt=null;
416 con = getPooledCon();
417 // should be a preparedStatement because is faster
418 stmt = con.createStatement();
419 int rs = executeUpdate(stmt,sql);
420 theLog.printDebugInfo("-- delete media success ");
421 } catch (Exception e) {
422 theLog.printDebugInfo("-- delete media failed ");
423 throw new StorageObjectException("-- delete by media id failed -- "
426 freeConnection(con,stmt);
430 public void delete(String contentId, String mediaId)
431 throws StorageObjectException {
432 if (mediaId == null || contentId==null) {
433 theLog.printDebugInfo("-- delete media failed -- missing parameter");
436 //delete all row with content_id=contentId and media_id=mediaId
437 String sql = "delete from "+ theTable +" where media_id=" + mediaId + " and content_id= "+contentId;
439 Connection con=null;Statement stmt=null;
441 con = getPooledCon();
442 // should be a preparedStatement because is faster
443 stmt = con.createStatement();
444 int rs = executeUpdate(stmt,sql);
445 theLog.printDebugInfo("-- delete content_x_media success ");
446 } catch (Exception e) {
447 theLog.printDebugInfo("-- delete content_x_media failed ");
448 throw new StorageObjectException("-- delete content_x_media failed -- "
451 freeConnection(con,stmt);
456 public EntityList getContent(EntityUploadedMedia media)
457 throws StorageObjectException {
458 EntityList returnList=null;
460 String id = media.getId();
461 String select = "select content_id from " + theTable + " where media_id=" + id;
463 // execute select statement
464 Connection con=null;Statement stmt=null;
466 con = getPooledCon();
467 // should be a preparedStatement because is faster
468 stmt = con.createStatement();
469 ResultSet rs = executeSql(stmt,select);
471 String mediaSelect= "id IN (";
474 if (first==false) mediaSelect+=",";
475 mediaSelect += rs.getString(1);
480 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,-1);
483 catch (Exception e) {
484 theLog.printDebugInfo("-- get content failed");
485 throw new StorageObjectException("-- get content failed -- "
488 finally { freeConnection(con,stmt);}
494 * Returns a EntityList with all content-objects having a relation to a media
497 public EntityList getContent()
498 throws StorageObjectException {
499 EntityList returnList=null;
501 String select = "select distinct content_id from " + theTable;
502 // execute select statement
503 Connection con=null;Statement stmt=null;
505 con = getPooledCon();
506 // should be a preparedStatement because is faster
507 stmt = con.createStatement();
508 ResultSet rs = executeSql(stmt,select);
510 String mediaSelect= "id IN (";
513 if (first==false) mediaSelect+=",";
514 mediaSelect += rs.getString(1);
519 returnList = DatabaseContent.getInstance().selectByWhereClause(mediaSelect,"webdb_lastchange desc");
522 catch (Exception e) {
523 theLog.printDebugInfo("-- get content failed");
524 throw new StorageObjectException("-- get content failed -- "
527 finally { freeConnection(con,stmt);}