Changeset 3365

Timestamp:

08/12/13 16:25:10 (11 years ago)

Author:

olhsha

Message:

getting the list of annotation IDs, where annotations are filtered by link, text, etc. So far body is considered as a text (got from xmls list of elements, vis get(0)). NOT TESTED yet!

Location:

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend

Files:

• BackendConstants.java (modified) (2 diffs)
• dao/AnnotationDao.java (modified) (2 diffs)
• dao/SourceDao.java (modified) (1 diff)
• dao/impl/JdbcAnnotationDao.java (modified) (5 diffs)
• dao/impl/JdbcSourceDao.java (modified) (2 diffs)

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/BackendConstants.java

@@ -31 +31 @@
     public static final int USER_HASH_PARAM_1 =  5;
     public static final int USER_HASH_PARAM_2 =  19;
-    
     // peter, is it okay?
     public static final int SOURCE_HASH_PARAM_1 =  7;
@@ -41 +40 @@
     
     public static final String regExpIdentifier = "[a-zA-Z0-9_-]*";
+    
 }

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/AnnotationDao.java

@@ -19 +19 @@
 
 import eu.dasish.annotation.backend.identifiers.AnnotationIdentifier;
-import eu.dasish.annotation.backend.identifiers.SourceIdentifier;
+import eu.dasish.annotation.backend.identifiers.UserIdentifier;
 import eu.dasish.annotation.schema.Annotation;
 import eu.dasish.annotation.schema.AnnotationInfo;
-import eu.dasish.annotation.schema.NewOrExistingSourceInfo;
 import eu.dasish.annotation.schema.ResourceREF;
 import java.sql.SQLException;
+import java.sql.Timestamp;
 import java.util.List;
 
@@ -73 +73 @@
     public Annotation addAnnotation(Annotation annotation, Number ownerID) throws SQLException;
  
+     
+    /**
+     * 
+     * @param link optional
+     * @param text optional
+     * @param access optional
+     * @param namespace optional TODO: do not know what to do with it 
+     * @param owner optional 
+     * @param after optional
+     * @param before optional 
+     * @return the list of internal annotation identifiers for annotations 
+     * -- referring to the "link", 
+     * -- bodies of which contain the "text", 
+     * -- to which inlogged user has "access", 
+     * -- owned by "owner", 
+     * -- added to the database between "before" and "after" time-dates.
+     */
+    public List<Number> getFilteredAnnotationIDs(String link, String text, String access, String namespace, UserIdentifier owner, Timestamp after, Timestamp before);
+    
    
-   
-    
+    /**
+     * 
+     * @param annotationIDs
+     * @return the list of annotationInfos (owner, headline, target sources, external_id) for the internal Ids from the  input list
+     * used on the second step for for GET api/annotations?<filters>
+     */
     public List<AnnotationInfo> getAnnotationInfos(List<Number> annotationIDs);    
      
    
     
-    public List<ResourceREF> getAnnotationREFs(List<Number> annotationIDs);    
+    public List<ResourceREF> getAnnotationREFs(List<Number> annotationIDs); 
+    
+    
+   
     
 }

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/SourceDao.java

@@ -108 +108 @@
     public Map<NewOrExistingSourceInfo, NewOrExistingSourceInfo> addTargetSources(Number annotationID, List<NewOrExistingSourceInfo> sources) throws SQLException;        
     
-        
+    /**
+     * 
+     * @param link
+     * @return the list source ID's which link-fields contain "link" as a substring
+     */ 
+    public List<Number> getSourcesForLink(String link);
 }

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java

@@ -25 +25 @@
 import eu.dasish.annotation.backend.dao.UserDao;
 import eu.dasish.annotation.backend.identifiers.AnnotationIdentifier;
+import eu.dasish.annotation.backend.identifiers.UserIdentifier;
 import eu.dasish.annotation.schema.Annotation;
 import eu.dasish.annotation.schema.AnnotationBody;
@@ -35 +36 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Timestamp;
 import java.util.ArrayList;
 import java.util.HashMap;
@@ -67 +69 @@
     }
 
-    ////////////////////////////////////////////////////////////////////////////
-    /**
-     *
-     * @param annotationIDs is a list of internal annotation identifiers
-     * @return the list of the corresponding annotation-infos for the annotation
-     * identifiers from the list; if the input list is null return null if the
-     * input list is empty (zero elements) returns an empty list
-     */
+    @Override
+    public List<Number> getFilteredAnnotationIDs(String link, String text, String access, String namespace, UserIdentifier owner, Timestamp after, Timestamp before) {
+
+        String sql = "SELECT " + annotation_id + " FROM " + annotationTableName;
+        boolean firstClauseIsBuild = false;
+        //TODO: optimize List<Object> params = new ArrayList<Object>();
+        //TODO: optimizie String builder
+
+
+
+        if (owner != null) {
+            sql = sql + " WHERE " + principal_id + "=:owner" + jdbcUserDao.getInternalID(owner);
+            firstClauseIsBuild = true;
+        }
+
+
+
+        if (after != null) {
+            if (firstClauseIsBuild) {
+                sql = sql + " AND " + time_stamp + "  > " + after.toString();
+            } else {
+                sql = sql + " WHERE " + time_stamp + "  > " + after.toString();
+                firstClauseIsBuild = true;
+            }
+        }
+
+        if (before != null) {
+            if (firstClauseIsBuild) {
+                sql = sql + " AND " + time_stamp + "  < " + before.toString();
+            } else {
+                sql = sql + " WHERE " + time_stamp + "  < " + before.toString();
+                firstClauseIsBuild = true;
+            }
+        }
+
+        if (text != null) {
+            if (firstClauseIsBuild) {
+                sql = sql + " AND " + body_xml + "  LIKE '%" + text + "%'";
+            } else {
+                sql = sql + " WHERE " + body_xml + "  LIKE '%" + text + "%'";
+                firstClauseIsBuild = true;
+            }
+        }
+
+        if (link != null) {
+            List<Number> sourceIDs = jdbcSourceDao.getSourcesForLink(link);
+            if (!sourceIDs.isEmpty()) {
+                String values = makeListOfValues(sourceIDs);
+                if (firstClauseIsBuild) {
+                    sql = sql + " AND " + source_id + " IN " + values;
+                }
+                 else {
+                    sql = sql + " WHERE " + source_id + " IN " + values;
+                    firstClauseIsBuild = true;
+                }
+            }
+        }
+
+
+        List<Number> result = getSimpleJdbcTemplate().query(sql, internalIDRowMapper);
+        return result;
+    }
+
     @Override
     public List<AnnotationInfo> getAnnotationInfos(List<Number> annotationIDs) {
@@ -197 +254 @@
         int affectedNotebooks = getSimpleJdbcTemplate().update(sqlNotebooks, annotationId);
 
-         String sqlPermissions = "DELETE FROM " + permissionsTableName + " where " + annotation_id + " = ?";
+        String sqlPermissions = "DELETE FROM " + permissionsTableName + " where " + annotation_id + " = ?";
         int affectedPermissions = getSimpleJdbcTemplate().update(sqlPermissions, annotationId);
-        
+
         // safe removing sources
         List<Number> sourceIDs = jdbcSourceDao.retrieveSourceIDs(annotationId);
@@ -205 +262 @@
         int affectedAnnotationsSources = getSimpleJdbcTemplate().update(sqlTargetSources, annotationId);
         int affectedSources;
-        for (Number sourceID : sourceIDs) {            
-         // call  the method in sources DAO that handles removal of a source which is not refered by other annotations
+        for (Number sourceID : sourceIDs) {
+            // call  the method in sources DAO that handles removal of a source which is not refered by other annotations
             affectedSources = jdbcSourceDao.deleteSource(sourceID);
         }

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcSourceDao.java

@@ -223 +223 @@
     
     
-
+    
     
     
@@ -258 +258 @@
     }
 
-    
-    
+    @Override
+    public List<Number> getSourcesForLink(String link){
+      String sql = "SELECT "+source_id+" FROM "+sourceTableName+ "WHERE "+link_uri+" LIKE '%"+link+"%'";
+      List<Number> result = getSimpleJdbcTemplate().query(sql, internalIDRowMapper);
+      return result;
+    }
 
     //////// HELPERS //////////////////////