X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;ds=sidebyside;f=source%2Fmircoders%2Fstorage%2FDatabaseCommentToMedia.java;h=8d6f0a129c71afa5ce33d72f347bdc4eee3b2c83;hb=d06e23e638f2538f263af76bd32da6b140f20ac6;hp=9868d6bcad24ee685e5e2f5db97ac0af57b1ba02;hpb=aa0e25363f8099782b07ad71a627da64ef65ba09;p=mir.git

diff --git a/source/mircoders/storage/DatabaseCommentToMedia.java b/source/mircoders/storage/DatabaseCommentToMedia.java
index 9868d6bc..8d6f0a12 100755
--- a/source/mircoders/storage/DatabaseCommentToMedia.java
+++ b/source/mircoders/storage/DatabaseCommentToMedia.java
@@ -31,27 +31,16 @@
 package mircoders.storage;
 
 import java.sql.Connection;
-import java.sql.ResultSet;
 import java.sql.Statement;
+import java.util.ArrayList;
 
 import mir.entity.EntityList;
 import mir.log.LoggerWrapper;
 import mir.storage.Database;
-import mir.storage.StorageObject;
-import mir.storage.StorageObjectExc;
-import mir.storage.StorageObjectFailure;
-import mircoders.entity.EntityComment;
+import mir.storage.DatabaseFailure;
 import mircoders.entity.EntityUploadedMedia;
 
-/**
- * <b>implements abstract DB connection to the comment_x_media SQL table
- *
- * @author RK, mir-coders group
- * @version $Id: DatabaseCommentToMedia.java,v 1.3 2003/05/03 00:21:22 zapata Exp $
- *
- */
-
-public class DatabaseCommentToMedia extends Database implements StorageObject{
+public class DatabaseCommentToMedia extends Database {
 
   private static DatabaseCommentToMedia instance;
 
@@ -66,260 +55,12 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     super();
 
     logger = new LoggerWrapper("Database.CommentToMedia");
-
-    hasTimestamp = false;
-    theTable = "comment_x_media";
-    theEntityClass = mir.entity.GenericEntity.class;
-  }
-
-  /**
-   * get all the media-files belonging to a comment entity
-   *
-   */
-  public EntityList getMedia(EntityComment comment) throws StorageObjectFailure {
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        // media should stay in uploaded order. this is especially important
-        // for photo stories which require a specific chronologic order.
-        // this is why we have the the second parameter "id"
-        returnList = DatabaseMedia.getInstance().selectByWhereClause(subselect,
-            "id", -1);
-      }
-      catch (Throwable e) {
-        logger.debug("-- get media failed " + e.toString());
-        throw new StorageObjectFailure("-- get media failed ", e);
-      }
-    }
-    return returnList;
-  }
-
-  public boolean hasMedia(EntityComment comment) throws StorageObjectFailure,
-      StorageObjectExc {
-    if (comment != null) {
-      try {
-        if (selectByWhereClause("comment_id=" + comment.getId(), -1).size() ==
-            0)
-          return false;
-        else
-          return true;
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.hasMedia: " + e.toString());
-        throw new StorageObjectFailure("DatabaseCommentToMedia.hasMedia: " +
-                                       e.toString(), e);
-      }
-    }
-    else {
-      logger.error("DatabaseCommentToMedia.hasMedia: comment == null");
-      throw new StorageObjectExc(
-          "DatabaseCommentToMedia.hasMedia: comment == null");
-    }
-  }
-
-  /**
-   * get all the audio belonging to a comment entity
-   *
-   */
-  public EntityList getAudio(EntityComment comment) throws StorageObjectFailure {
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      //this is not supported by mysql
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        // media should stay in uploaded order. this is especially important
-        // for photo stories which require a specific chronologic order.
-        // this is why we have the the second parameter "id"
-        returnList = DatabaseAudio.getInstance().selectByWhereClause(subselect,
-            "id", -1);
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.getAudio: " + e.toString());
-        throw new StorageObjectFailure("DatabaseCommentToMedia.getAudio: " +
-                                       e.toString(), e);
-      }
-    }
-    return returnList;
-  }
-
-  /**
-   * get all the video belonging to a comment entity
-   *
-   */
-  public EntityList getVideo(EntityComment comment) throws StorageObjectFailure {
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      //this is not supported by mysql
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        // media should stay in uploaded order. this is especially important
-        // for photo stories which require a specific chronologic order.
-        // this is why we have the the second parameter "id"
-        returnList = DatabaseVideo.getInstance().selectByWhereClause(subselect,
-            "id", -1);
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.getVideo: " + e.toString());
-        throw new StorageObjectFailure("DatabaseCommentToMedia.getVideo: " +
-                                       e.toString(), e);
-      }
-    }
-    return returnList;
-  }
-
-  /**
-   * get all the images belonging to a comment entity
-   *
-   */
-  public EntityList getImages(EntityComment comment) throws
-      StorageObjectFailure {
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      //this is not supported by mysql
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        // media should stay in uploaded order. this is especially important
-        // for photo stories which require a specific chronologic order.
-        // this is why we have the the second parameter "id"
-        returnList = DatabaseImages.getInstance().selectByWhereClause(subselect,
-            "id", -1);
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.getImages: " + e.toString());
-        throw new StorageObjectFailure("DatabaseCommentToMedia.getImages: " +
-                                       e.toString(), e);
-      }
-    }
-    return returnList;
-  }
-
-  /**
-   * get all the uploaded/other Media belonging to a comment entity
-   *
-   */
-  public EntityList getOther(EntityComment comment) throws StorageObjectFailure {
-    /** @todo this should only fetch published media / rk */
-
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      //this is not supported by mysql
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        // media should stay in uploaded order. this is especially important
-        // for photo stories which require a specific chronologic order.
-        // this is why we have the the second parameter "id"
-        returnList = DatabaseOther.getInstance().selectByWhereClause(subselect,
-            "id");
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.getOther: " + e.toString());
-        throw new StorageObjectFailure("DatabaseCommentToMedia.getOther: " + e.toString(), e);
-      }
-    }
-    return returnList;
-  }
-
-  /**
-   * get all the uploaded/other Media belonging to a comment entity
-   *
-   */
-  public EntityList getUploadedMedia(EntityComment comment) throws
-      StorageObjectFailure {
-    /** @todo this should only fetch published media / rk */
-
-    EntityList returnList = null;
-    if (comment != null) {
-      // get all to_topic from media_x_topic
-      String id = comment.getId();
-      //this is not supported by mysql
-      String subselect = "id in (select media_id from " + theTable +
-          " where comment_id=" + id + ")";
-
-      try {
-        returnList = DatabaseUploadedMedia.getInstance().selectByWhereClause(
-            subselect,
-            "id");
-      }
-      catch (Exception e) {
-        logger.error("DatabaseCommentToMedia.getUploadedMedia: " + e.toString());
-        throw new StorageObjectFailure(
-            "DatabaseCommentToMedia.getUploadedMedia: " + e.toString(), e);
-      }
-    }
-    return returnList;
-  }
-
-  public void setMedia(String commentId, String[] mediaId) throws
-      StorageObjectFailure {
-    if (commentId == null) {
-      return;
-    }
-    if (mediaId == null || mediaId[0] == null) {
-      return;
-    }
-    //first delete all row with comment_id=commentId
-    String sql = "delete from " + theTable + " where comment_id=" + commentId;
-
-    Connection con = null;
-    Statement stmt = null;
-    try {
-      con = getPooledCon();
-      // should be a preparedStatement because is faster
-      stmt = con.createStatement();
-      ResultSet rs = executeSql(stmt, sql);
-    }
-    catch (Exception e) {
-      logger.error("-- set media failed -- delete");
-      throw new StorageObjectFailure("-- set media failed -- delete", e);
-    }
-    finally {
-      freeConnection(con, stmt);
-    }
-
-    //now insert
-    //first delete all row with comment_id=commentId
-    for (int i = 0; i < mediaId.length; i++) {
-      sql = "insert into " + theTable + " (comment_id,media_id) values ("
-          + commentId + "," + mediaId[i] + ")";
-      try {
-        con = getPooledCon();
-        // should be a preparedStatement because is faster
-        stmt = con.createStatement();
-        int rs = executeUpdate(stmt, sql);
-      }
-      catch (Exception e) {
-        logger.error("-- set topics failed -- insert");
-        throw new StorageObjectFailure("-- set topics failed -- insert ", e);
-      }
-      finally {
-        freeConnection(con, stmt);
-      }
-    }
+    mainTable = "comment_x_media";
+    entityClass = mir.entity.GenericEntity.class;
   }
 
   public void addMedia(String commentId, String mediaId) throws
-      StorageObjectFailure {
+      DatabaseFailure {
     if (commentId == null && mediaId == null) {
       return;
     }
@@ -328,17 +69,17 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     Statement stmt = null;
     //now insert
 
-    String sql = "insert into " + theTable + " (comment_id,media_id) values ("
+    String sql = "insert into " + mainTable + " (comment_id,media_id) values ("
         + commentId + "," + mediaId + ")";
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
     }
     catch (Exception e) {
       logger.error("-- add media failed -- insert");
-      throw new StorageObjectFailure("-- add media failed -- insert ", e);
+      throw new DatabaseFailure("-- add media failed -- insert ", e);
     }
     finally {
       freeConnection(con, stmt);
@@ -346,24 +87,24 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
   }
 
   public void setMedia(String commentId, String mediaId) throws
-      StorageObjectFailure {
+      DatabaseFailure {
     if (commentId == null && mediaId == null) {
       return;
     }
     //first delete all row with comment_id=commentId
-    String sql = "delete from " + theTable + " where comment_id=" + commentId;
+    String sql = "delete from " + mainTable + " where comment_id=" + commentId;
 
     Connection con = null;
     Statement stmt = null;
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
     }
     catch (Exception e) {
       logger.error("-- set media failed -- delete");
-      throw new StorageObjectFailure("-- set media failed -- delete ", e);
+      throw new DatabaseFailure("-- set media failed -- delete ", e);
     }
     finally {
       freeConnection(con, stmt);
@@ -372,42 +113,42 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     //now insert
     //first delete all row with comment_id=commentId
 
-    sql = "insert into " + theTable + " (comment_id,media_id) values ("
+    sql = "insert into " + mainTable + " (comment_id,media_id) values ("
         + commentId + "," + mediaId + ")";
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
     }
     catch (Exception e) {
       logger.error("-- set media failed -- insert");
-      throw new StorageObjectFailure("-- set media failed -- insert ", e);
+      throw new DatabaseFailure("-- set media failed -- insert ", e);
     }
     finally {
       freeConnection(con, stmt);
     }
   }
 
-  public void deleteByCommentId(String commentId) throws StorageObjectFailure {
+  public void deleteByCommentId(String commentId) throws DatabaseFailure {
     if (commentId == null) {
       //theLog.printDebugInfo("-- delete topics failed -- no comment id");
       return;
     }
     //delete all row with comment_id=commentId
-    String sql = "delete from " + theTable + " where comment_id=" + commentId;
+    String sql = "delete from " + mainTable + " where comment_id=" + commentId;
 
     Connection con = null;
     Statement stmt = null;
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
     }
     catch (Exception e) {
       logger.error("-- delete by commentId failed  ");
-      throw new StorageObjectFailure(
+      throw new DatabaseFailure(
           "-- delete by comment id failed -- delete ", e);
     }
     finally {
@@ -415,18 +156,18 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     }
   }
 
-  public void deleteByMediaId(String mediaId) throws StorageObjectFailure {
+  public void deleteByMediaId(String mediaId) throws DatabaseFailure {
     if (mediaId == null) {
       //theLog.printDebugInfo("-- delete topics failed -- no topic id");
       return;
     }
     //delete all row with comment_id=commentId
-    String sql = "delete from " + theTable + " where media_id=" + mediaId;
+    String sql = "delete from " + mainTable + " where media_id=" + mediaId;
 
     Connection con = null;
     Statement stmt = null;
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
@@ -434,7 +175,7 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     }
     catch (Exception e) {
       logger.error("-- delete media failed ");
-      throw new StorageObjectFailure("-- delete by media id failed -- ", e);
+      throw new DatabaseFailure("-- delete by media id failed -- ", e);
     }
     finally {
       freeConnection(con, stmt);
@@ -442,19 +183,19 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
   }
 
   public void delete(String commentId, String mediaId) throws
-      StorageObjectFailure {
+      DatabaseFailure {
     if (mediaId == null || commentId == null) {
       logger.debug("-- delete media failed -- missing parameter");
       return;
     }
     //delete all row with comment_id=commentId and media_id=mediaId
-    String sql = "delete from " + theTable + " where media_id=" + mediaId +
+    String sql = "delete from " + mainTable + " where media_id=" + mediaId +
         " and comment_id= " + commentId;
 
     Connection con = null;
     Statement stmt = null;
     try {
-      con = getPooledCon();
+      con = obtainConnection();
       // should be a preparedStatement because is faster
       stmt = con.createStatement();
       int rs = executeUpdate(stmt, sql);
@@ -462,7 +203,7 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
     }
     catch (Exception e) {
       logger.error("-- delete comment_x_media failed ");
-      throw new StorageObjectFailure("-- delete comment_x_media failed -- ", e);
+      throw new DatabaseFailure("-- delete comment_x_media failed -- ", e);
     }
     finally {
       freeConnection(con, stmt);
@@ -470,87 +211,52 @@ public class DatabaseCommentToMedia extends Database implements StorageObject{
   }
 
   public EntityList getComment(EntityUploadedMedia media) throws
-      StorageObjectFailure {
+      DatabaseFailure {
+
     EntityList returnList = null;
     if (media != null) {
+
       String id = media.getId();
-      String select = "select comment_id from " + theTable + " where media_id=" +
-          id;
+      ArrayList extraTables = new ArrayList();
+      extraTables.add(mainTable + " cxm");
 
-      // execute select statement
-      Connection con = null;
-      Statement stmt = null;
+      String mediaSelect = "cxm.comment_id=c.id and cxm.media_id="+id;
       try {
-        con = getPooledCon();
-        // should be a preparedStatement because is faster
-        stmt = con.createStatement();
-        ResultSet rs = executeSql(stmt, select);
-        if (rs != null) {
-          String mediaSelect = "id IN (";
-          boolean first = true;
-          while (rs.next()) {
-            if (first == false)
-              mediaSelect += ",";
-            mediaSelect += rs.getString(1);
-            first = false;
-          }
-          mediaSelect += ")";
-          if (first == false)
-            returnList = DatabaseComment.getInstance().selectByWhereClause(
-                mediaSelect, -1);
-        }
+        returnList = DatabaseComment.getInstance().selectByWhereClause("c",
+          extraTables, mediaSelect, "c.id" );
+
       }
       catch (Exception e) {
         logger.error("-- get comment failed");
-        throw new StorageObjectFailure("-- get comment failed -- ", e);
-      }
-      finally {
-        freeConnection(con, stmt);
+        throw new DatabaseFailure("-- get comment failed -- ", e);
       }
     }
     return returnList;
   }
 
   /**
-   * Returns a EntityList with all comment-objects having a relation to a media
+   * Returns a EntityList with all comment-objects having
+   *  a relation to a media
    */
 
-  public EntityList getComment() throws StorageObjectFailure {
+  public EntityList getComment() throws DatabaseFailure {
     EntityList returnList = null;
 
-    String select = "select distinct comment_id from " + theTable;
-    // execute select statement
-    Connection con = null;
-    Statement stmt = null;
+    ArrayList extraTables = new ArrayList();
+    extraTables.add(mainTable + " cxm");
+
+    String mediaSelect = "cxm.comment_id=c.id";
     try {
-      con = getPooledCon();
-      // should be a preparedStatement because is faster
-      stmt = con.createStatement();
-      ResultSet rs = executeSql(stmt, select);
-      if (rs != null) {
-        String mediaSelect = "id IN (";
-        boolean first = true;
-        while (rs.next()) {
-          if (first == false)
-            mediaSelect += ",";
-          mediaSelect += rs.getString(1);
-          first = false;
-        }
-        mediaSelect += ")";
-        if (first == false)
-          returnList = DatabaseComment.getInstance().selectByWhereClause(
-              mediaSelect, "webdb_lastchange desc");
-      }
+      returnList = DatabaseComment.getInstance().selectByWhereClause("c",
+        extraTables, mediaSelect, "c.webdb_lastchange desc" );
+
     }
     catch (Exception e) {
       logger.error("-- get comment failed");
-      throw new StorageObjectFailure("-- get comment failed -- ", e);
-    }
-    finally {
-      freeConnection(con, stmt);
+      throw new DatabaseFailure("-- get comment failed -- ", e);
     }
-
     return returnList;
+
   }
 
 }