Changeset 3370

Timestamp:

08/13/13 14:03:21 (11 years ago)

Author:

olhsha

Message:

filtered getting annotation is implemented and tested (except filtering by the logged-in user access mode)

Location:

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src

Files:

• main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java (modified) (5 diffs)
• main/java/eu/dasish/annotation/backend/dao/impl/JdbcResourceDao.java (modified) (4 diffs)
• main/java/eu/dasish/annotation/backend/dao/impl/JdbcUserDao.java (modified) (1 diff)
• test/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDaoTest.java (modified) (6 diffs)

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

@@ -71 +71 @@
     @Override
     public List<Number> getFilteredAnnotationIDs(String link, String text, String access, String namespace, UserIdentifier owner, Timestamp after, Timestamp before) {
-
         
-        String table;
-        
+        StringBuilder sql = new StringBuilder("SELECT DISTINCT ");
+        sql.append(annotation_id).append(" FROM ").append(annotationTableName).append(" WHERE TRUE ");        
+        Map<String, Object> params = new HashMap<String, Object>();
+       
          if (link != null) {
             List<Number> sourceIDs = jdbcSourceDao.getSourcesForLink(link);
@@ -80 +81 @@
             if (!annotationIDs.isEmpty()) {
                 String values = makeListOfValues(annotationIDs);
-                table = "( SELECT DISTINCT "+annotationStar + "WHERE "+ annotation_id +"IN " + values + ") ";
+                sql.append(" AND ").append(annotation_id).append(" IN ").append(values);
             }
             else{
@@ -86 +87 @@
             }
         }
-         else{
-             table = annotationTableName;
-         }
          
-       
-        StringBuilder sql = new StringBuilder("SELECT ");
-        sql.append(annotation_id).append(" FROM ").append(table).append(" WHERE TRUE ");
-        
-        Map<String, Object> params = new HashMap<String, Object>();
-        //TODO: optimizie String builder
-
 
         if (owner != null) {
-            sql.append(" AND ").append(principal_id).append(" = :owner ");
-            params.put("owner", owner.toString());
+            Number ownerID = jdbcUserDao.getInternalID(owner);
+            sql.append(" AND ").append(owner_id).append(" = :owner ");
+            params.put("owner", ownerID);
         }
 
@@ -114 +106 @@
 
         if (text != null) {
-            sql .append(" AND ").append(body_xml).append("  LIKE '% :text %'");
-            params.put("text", text);
-        }
+            sql.append(" AND ").append(body_xml).append("  LIKE '%").append(text).append("%'");
+        }
+       
        
         List<Number> result = getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, params);
@@ -174 +166 @@
      * from the input list if the input list is null or empty (zero elements)
      * returns an empty list
+     * there may be annotationIDs which are not in the DB (so that's why we need this method).
      */
+    
     @Override
     public List<ResourceREF> getAnnotationREFs(List<Number> annotationIDs) {

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

@@ -106 +106 @@
             return null;
         }
-       String sql = "SELECT "+internalIdName+" FROM "+resourceTableName+" WHERE "+external_id  +"= ?";
+       String sql = "SELECT "+internalIdName+" FROM "+resourceTableName+" WHERE "+external_id  +"= ? LIMIT 1";
        List<Number> sqlResult= getSimpleJdbcTemplate().query(sql, internalIDRowMapper, externalId.toString()); 
        
@@ -137 +137 @@
             return null;
         }
-        String sql = "SELECT " + external_id + " FROM " + resourceTableName + " WHERE " + internalIdName + "= ?";
+        String sql = "SELECT " + external_id + " FROM " + resourceTableName + " WHERE " + internalIdName + "= ? LIMIT 1";
         List<String> sqlResult = getSimpleJdbcTemplate().query(sql, externalIDRowMapper, internalId);
 
@@ -162 +162 @@
     /////////////////////////////////////////////////////
    protected XMLGregorianCalendar retrieveTimeStamp(Number internalID) {
-        String sqlTime = "SELECT " + time_stamp + " FROM " + resourceTableName + " WHERE " + internalIdName + "= ?";
+        String sqlTime = "SELECT " + time_stamp + " FROM " + resourceTableName + " WHERE " + internalIdName + "= ? LIMIT 1";
         List<XMLGregorianCalendar> timeStamp = getSimpleJdbcTemplate().query(sqlTime, timeStampRowMapper, internalID);
         if (timeStamp.isEmpty()) {
@@ -195 +195 @@
            return false;
        } 
-       String sql = "SELECT "+notebookNotebook_id+"  FROM notebook where "+notebook_id+" = ?";
+       String sql = "SELECT "+notebookNotebook_id+"  FROM notebook where "+notebook_id+" = ? LIMIT 1";
        List<Number> result=getSimpleJdbcTemplate().query(sql, isNotebookInTheDataBaseRowMapper, notebookID.toString());
        if (result == null) {

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

@@ -20 +20 @@
 import eu.dasish.annotation.backend.dao.UserDao;
 import eu.dasish.annotation.backend.identifiers.UserIdentifier;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.List;
 import javax.sql.DataSource;
-import org.springframework.jdbc.core.RowMapper;
 
 /**

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

@@ -23 +23 @@
 import eu.dasish.annotation.backend.dao.PermissionsDao;
 import eu.dasish.annotation.backend.dao.SourceDao;
+import eu.dasish.annotation.backend.dao.UserDao;
 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;
@@ -33 +35 @@
 import eu.dasish.annotation.schema.SourceInfo;
 import java.sql.SQLException;
+import java.sql.Timestamp;
 import java.util.ArrayList;
 import java.util.HashMap;
@@ -40 +43 @@
 import org.jmock.Mockery;
 import static org.junit.Assert.*;
-import org.junit.Ignore;
 import org.junit.Test;
 import org.junit.runner.RunWith;
@@ -64 +66 @@
     @Autowired
     private SourceDao sourceDao;
+    @Autowired
+    private UserDao userDao;    
     @Autowired
     private Mockery mockery;
@@ -376 +380 @@
         List<Number> result = jdbcAnnotationDao.getAnnotationIDsForSources(sources);
         assertEquals (2, result.size());
-        //assertEquals(2, result.get(0));
-        //assertEquals(3, result.get(1));
+        assertEquals(2, result.get(0));
+        assertEquals(3, result.get(1));
     }
     
     @Test    
     public void testGetExternalID() {
-        System.out.println("getAnnotationID");
+        System.out.println("getExternalID");
 
         final AnnotationIdentifier externalId = jdbcAnnotationDao.getExternalID(2);
@@ -392 +396 @@
 
     }
+    
+    
+    /** test
+     * public List<Number> getFilteredAnnotationIDs(String link, String text, String access, String namespace, UserIdentifier owner, Timestamp after, Timestamp before) {
+  **/
+    
+    @Test    
+    public void testGetFilteredAnnotationIDs(){
+        System.out.println(" test getFilteredAnnotationIDs");
+        
+        
+        //////////////////////////////////////////
+        // TEST 1 
+        final String link = "nl.wikipedia.org";
+        final List<Number> sourceIDs = new ArrayList<Number>();
+        sourceIDs.add(1);
+        sourceIDs.add(2);
+        
+        
+        mockery.checking(new Expectations() {
+            {
+                oneOf(sourceDao).getSourcesForLink(link);
+                will(returnValue(sourceIDs));
+            }
+        });
+        List<Number> result_1 = jdbcAnnotationDao.getFilteredAnnotationIDs(link, null, null, null, null, null, null);        
+        assertEquals(2, result_1.size());
+        assertEquals(2, result_1.get(0));
+        assertEquals(3, result_1.get(1));
+        
+        ///////////////////////////////////////////////
+        // TEST 2        
+        mockery.checking(new Expectations() {
+            {
+                oneOf(sourceDao).getSourcesForLink(link);
+                will(returnValue(sourceIDs));
+            }
+        });
+        List<Number> result_2 = jdbcAnnotationDao.getFilteredAnnotationIDs(link, "some html", null, null, null, null, null);        
+        assertEquals(2, result_2.size());
+        assertEquals(2, result_2.get(0));
+        assertEquals(3, result_2.get(1));
+        
+        ///////////////////////////////////////////////
+        // TEST 3
+        final UserIdentifier owner = new UserIdentifier("00000000-0000-0000-0000-000000000111");
+        
+        
+         mockery.checking(new Expectations() {
+            {
+                oneOf(sourceDao).getSourcesForLink(link);
+                will(returnValue(sourceIDs));
+                
+                oneOf(userDao).getInternalID(owner);
+                will(returnValue(3));
+            }
+        });
+       
+        List<Number> result_3 = jdbcAnnotationDao.getFilteredAnnotationIDs(link, "some html", null, null, owner, null, null);        
+        assertEquals(1, result_3.size());
+        assertEquals(2, result_3.get(0));
+        
+         ///////////////////////////////////////////////
+        // TEST 4
+       
+         mockery.checking(new Expectations() {
+            {
+                oneOf(sourceDao).getSourcesForLink(link);
+                will(returnValue(sourceIDs));
+                
+                oneOf(userDao).getInternalID(owner);
+                will(returnValue(3));
+            }
+        });
+        Timestamp after = new Timestamp(0); 
+        Timestamp before = new Timestamp(System.currentTimeMillis());  
+        List<Number> result_4 = jdbcAnnotationDao.getFilteredAnnotationIDs(link, "some html", null, null, owner, after, before);        
+        assertEquals(1, result_4.size());
+        assertEquals(2, result_4.get(0));
+        
+          ///////////////////////////////////////////////
+        // TEST 5
+       
+         mockery.checking(new Expectations() {
+            {
+                oneOf(sourceDao).getSourcesForLink(link);
+                will(returnValue(sourceIDs));
+                
+                oneOf(userDao).getInternalID(owner);
+                will(returnValue(3));
+            }
+        });
+        Timestamp after_1 = new Timestamp(System.currentTimeMillis());        
+        List<Number> result_5 = jdbcAnnotationDao.getFilteredAnnotationIDs(link, "some html", null, null, owner, after_1, null);        
+        assertEquals(0, result_5.size());
+        
+        
+    }
+    
     //////////// helpers //////////////////////
 }