Changeset 3367


Ignore:
Timestamp:
08/13/13 11:18:07 (11 years ago)
Author:
olhsha
Message:

correction 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!

Getting sourceIDs of the sources referreing to a given link

Location:
DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src
Files:
5 edited

Legend:

Unmodified
Added
Removed

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

    r3365 r3367  
    106106   
    107107   
     108    public List<Number> getAnnotationIDsForSources(List<Number> sourceIDs);
    108109   
    109110   

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

    r3365 r3367  
    7272    public List<Number> getFilteredAnnotationIDs(String link, String text, String access, String namespace, UserIdentifier owner, Timestamp after, Timestamp before) {
    7373
    74         String sql = "SELECT " + annotation_id + " FROM " + annotationTableName;
    75         boolean firstClauseIsBuild = false;
    76         //TODO: optimize List<Object> params = new ArrayList<Object>();
     74       
     75        String table;
     76       
     77         if (link != null) {
     78            List<Number> sourceIDs = jdbcSourceDao.getSourcesForLink(link);
     79            List<Number> annotationIDs = getAnnotationIDsForSources(sourceIDs);
     80            if (!annotationIDs.isEmpty()) {
     81                String values = makeListOfValues(annotationIDs);
     82                table = "( SELECT "+annotationStar + "WHERE "+ annotation_id +"IN " + values + ") ";
     83            }
     84            else{
     85                return new ArrayList<Number>();
     86            }
     87        }
     88         else{
     89             table = annotationTableName;
     90         }
     91         
     92       
     93        StringBuilder sql = new StringBuilder("SELECT ");
     94        sql.append(annotation_id).append(" FROM ").append(table).append(" WHERE TRUE ");
     95       
     96        Map<String, Object> params = new HashMap<String, Object>();
    7797        //TODO: optimizie String builder
    7898
    7999
    80 
    81100        if (owner != null) {
    82             sql = sql + " WHERE " + principal_id + "=:owner" + jdbcUserDao.getInternalID(owner);
    83             firstClauseIsBuild = true;
    84         }
    85 
    86 
     101            sql.append(" AND ").append(principal_id).append(" = :owner ");
     102            params.put("owner", owner.toString());
     103        }
    87104
    88105        if (after != null) {
    89             if (firstClauseIsBuild) {
    90                 sql = sql + " AND " + time_stamp + "  > " + after.toString();
    91             } else {
    92                 sql = sql + " WHERE " + time_stamp + "  > " + after.toString();
    93                 firstClauseIsBuild = true;
    94             }
     106            sql.append(" AND ").append(time_stamp).append("  > :after");
     107            params.put("after", after);
    95108        }
    96109
    97110        if (before != null) {
    98             if (firstClauseIsBuild) {
    99                 sql = sql + " AND " + time_stamp + "  < " + before.toString();
    100             } else {
    101                 sql = sql + " WHERE " + time_stamp + "  < " + before.toString();
    102                 firstClauseIsBuild = true;
    103             }
     111            sql.append(" AND ").append(time_stamp).append("  < :before");           
     112            params.put("before", before);
    104113        }
    105114
    106115        if (text != null) {
    107             if (firstClauseIsBuild) {
    108                 sql = sql + " AND " + body_xml + "  LIKE '%" + text + "%'";
    109             } else {
    110                 sql = sql + " WHERE " + body_xml + "  LIKE '%" + text + "%'";
    111                 firstClauseIsBuild = true;
    112             }
    113         }
    114 
    115         if (link != null) {
    116             List<Number> sourceIDs = jdbcSourceDao.getSourcesForLink(link);
    117             if (!sourceIDs.isEmpty()) {
    118                 String values = makeListOfValues(sourceIDs);
    119                 if (firstClauseIsBuild) {
    120                     sql = sql + " AND " + source_id + " IN " + values;
    121                 }
    122                  else {
    123                     sql = sql + " WHERE " + source_id + " IN " + values;
    124                     firstClauseIsBuild = true;
    125                 }
    126             }
    127         }
    128 
    129 
    130         List<Number> result = getSimpleJdbcTemplate().query(sql, internalIDRowMapper);
    131         return result;
    132     }
     116            sql .append(" AND ").append(body_xml).append("  LIKE '% :text %'");
     117            params.put("text", text);
     118        }
     119       
     120        List<Number> result = getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, params);
     121        return result;
     122    }
     123   
     124    //////////////////////////////
     125   
     126    @Override
     127    public List<Number> getAnnotationIDsForSources(List<Number> sourceIDs){       
     128        if (sourceIDs == null) {
     129            return null;
     130        }       
     131        if (sourceIDs.isEmpty()) {
     132           return new ArrayList<Number>();
     133        }       
     134        String values = makeListOfValues(sourceIDs);
     135        StringBuilder query = new StringBuilder("SELECT ");
     136        query.append(annotation_id).append(" FROM ").append(annotationsSourcesTableName).append(" WHERE ").append(source_id).append(" IN ");
     137        query.append(values);
     138        List<Number> result = getSimpleJdbcTemplate().query(query.toString(), internalIDRowMapper);
     139        return result;
     140    }
     141           
     142   
    133143
    134144    @Override

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

    r3365 r3367  
    260260    @Override
    261261    public List<Number> getSourcesForLink(String link){
    262       String sql = "SELECT "+source_id+" FROM "+sourceTableName+ "WHERE "+link_uri+" LIKE '%"+link+"%'";
    263       List<Number> result = getSimpleJdbcTemplate().query(sql, internalIDRowMapper);
     262      StringBuilder sql = new StringBuilder("SELECT ");
     263      sql.append(source_id).append(" FROM ").append(sourceTableName).append(" WHERE ").append(link_uri).append(" LIKE '%").append(link).append("%'");
     264      List<Number> result = getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper);
    264265      return result;
    265266    }

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

    r3348 r3367  
    128128        assertEquals(1, result);
    129129
    130         Number internalIDNoExist = 5;
     130        Number internalIDNoExist = 6;
    131131        int resultTwo = jdbcSourceDao.deleteSourceVersionRows(internalIDNoExist);
    132132        assertEquals(0, resultTwo);
    … …  
    139139    public void testDeleteSource() {
    140140        System.out.println("deleteSource");
     141       
     142        // test 1
    141143        Number internalID = 1;
    142144        int result = jdbcSourceDao.deleteSource(internalID);
    143145        assertEquals(0, result); // the source is in use, should not be deleted
    144146
    145         final Number internalIDToBeDeleted = 4;
     147        // test 2
    146148        final List<Number> versions = new ArrayList<Number>();
    147149        versions.add(5);
    148150        mockery.checking(new Expectations() {
    149151            {
    150                 oneOf(versionDao).retrieveVersionList(internalIDToBeDeleted);
     152                oneOf(versionDao).retrieveVersionList(5);
    151153                will(returnValue(versions));
    152154
    … …  
    156158        });
    157159
    158         int resultTwo = jdbcSourceDao.deleteSource(internalIDToBeDeleted);
     160        int resultTwo = jdbcSourceDao.deleteSource(5);
    159161        assertEquals(1, resultTwo); // the source will be deleted because it is not referred by any annotation
    160162    }
    … …  
    180182            assertEquals(link, result.getLink());
    181183            assertEquals(version, result.getVersion());
    182             assertEquals(5, jdbcSourceDao.getInternalID(new SourceIdentifier(result.getURI())));
     184            assertEquals(6, jdbcSourceDao.getInternalID(new SourceIdentifier(result.getURI())));
    183185
    184186            assertFalse(null==result.getTimeSatmp());
    … …  
    309311
    310312    }
     313   
     314    /**
     315     * test public List<Number> getSourcesForLink(String link)
     316     *
     317     **/
     318    @Test
     319    public void tesGetSourcesForLink(){
     320        System.out.println(" test getSourcesForLink");
     321       
     322        String substring = "http://nl.wikipedia.org";
     323        List<Number> result  = jdbcSourceDao.getSourcesForLink(substring);
     324        assertEquals(2, result.size());
     325        assertEquals(1, result.get(0));
     326        assertEquals(2, result.get(1));               
     327    }
     328   
    311329}

  • DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/test/resources/test-data/InsertTestData.sql

    r3361 r3367  
    6969INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000045', 'Art Nuveau wiki -version 2012'); --5
    7070INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000046', 'Art Nuveau wiki -version 2011'); --6  not used
     71INSERT INTO version (external_id, version) VALUES ('00000000-0000-0000-0000-000000000047', 'Art Nuveau wiki -version 2010'); --7
    7172
    7273-- CREATE TABLE target_source (
    … …  
    8586INSERT INTO target_source (external_id, link_uri, version_id) VALUES ('00000000-0000-0000-0000-000000000033', 'http://en.wikipedia.org/wiki/Art_Nouveau', 4); --3
    8687INSERT INTO target_source (external_id, link_uri, version_id) VALUES ('00000000-0000-0000-0000-000000000034', '???', 5); --4
     88INSERT INTO target_source (external_id, link_uri, version_id) VALUES ('00000000-0000-0000-0000-000000000035', '???', 5); --5
    8789
    8890-- CREATE TABLE annotations_target_sources (
    … …  
    9698INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (3, 2);
    9799INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (4, 3);
     100INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (5, 3); -- source 3 should not be deleted when annot 5 is deleted
     101INSERT INTO annotations_target_sources (annotation_id, source_id) VALUES (5, 4); -- source 4 to be deleted when annot 5 is deleted
    98102
    99103----------------------------------------------------------------
    … …  
    110114INSERT INTO sources_versions (source_id, version_id) VALUES (3, 4);
    111115INSERT INTO sources_versions (source_id, version_id) VALUES (4, 5);
     116INSERT INTO sources_versions (source_id, version_id) VALUES (5, 7);
    112117
    113118--------------------------------------------------------------
Note: See TracChangeset for help on using the changeset viewer.