[3220] | 1 | /* |
---|
| 2 | * Copyright (C) 2013 DASISH |
---|
| 3 | * |
---|
| 4 | * This program is free software; you can redistribute it and/or |
---|
| 5 | * modify it under the terms of the GNU General Public License |
---|
| 6 | * as published by the Free Software Foundation; either version 2 |
---|
| 7 | * of the License, or (at your option) any later version. |
---|
| 8 | * |
---|
| 9 | * This program is distributed in the hope that it will be useful, |
---|
| 10 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
---|
| 11 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
---|
| 12 | * GNU General Public License for more details. |
---|
| 13 | * |
---|
| 14 | * You should have received a copy of the GNU General Public License |
---|
| 15 | * along with this program; if not, write to the Free Software |
---|
| 16 | * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
---|
| 17 | */ |
---|
| 18 | package eu.dasish.annotation.backend.dao.impl; |
---|
| 19 | |
---|
[3781] | 20 | import eu.dasish.annotation.backend.dao.TargetDao; |
---|
| 21 | import eu.dasish.annotation.schema.Target; |
---|
| 22 | import eu.dasish.annotation.schema.TargetInfo; |
---|
[3220] | 23 | import java.sql.ResultSet; |
---|
| 24 | import java.sql.SQLException; |
---|
| 25 | import java.util.ArrayList; |
---|
[3298] | 26 | import java.util.HashMap; |
---|
[3220] | 27 | import java.util.List; |
---|
[3298] | 28 | import java.util.Map; |
---|
[3455] | 29 | import java.util.UUID; |
---|
[3220] | 30 | import javax.sql.DataSource; |
---|
[3292] | 31 | import javax.xml.datatype.DatatypeConfigurationException; |
---|
| 32 | import javax.xml.datatype.XMLGregorianCalendar; |
---|
[3220] | 33 | import org.springframework.jdbc.core.RowMapper; |
---|
| 34 | |
---|
| 35 | /** |
---|
| 36 | * |
---|
| 37 | * @author olhsha |
---|
| 38 | */ |
---|
[3781] | 39 | public class JdbcTargetDao extends JdbcResourceDao implements TargetDao { |
---|
[3299] | 40 | |
---|
[3781] | 41 | public JdbcTargetDao(DataSource dataTarget) { |
---|
| 42 | setDataSource(dataTarget); |
---|
| 43 | internalIdName = target_id; |
---|
[3793] | 44 | resourceTableName = targetTableName; |
---|
[3220] | 45 | } |
---|
[4146] | 46 | |
---|
| 47 | @Override |
---|
| 48 | public void setServiceURI(String serviceURI) { |
---|
[3646] | 49 | _serviceURI = serviceURI; |
---|
[3639] | 50 | } |
---|
[3299] | 51 | |
---|
[3459] | 52 | //////////////////////// GETTERS /////////////////////////////////// |
---|
[3292] | 53 | @Override |
---|
[3781] | 54 | public Target getTarget(Number internalID) { |
---|
[3464] | 55 | StringBuilder sql = new StringBuilder("SELECT "); |
---|
[3793] | 56 | sql.append(TargetStar).append(" FROM ").append(targetTableName).append(" WHERE ").append(target_id).append("= ? LIMIT 1"); |
---|
[3781] | 57 | List<Target> result = getSimpleJdbcTemplate().query(sql.toString(), TargetRowMapper, internalID); |
---|
[3463] | 58 | return (!result.isEmpty() ? result.get(0) : null); |
---|
[3279] | 59 | } |
---|
[3781] | 60 | private final RowMapper<Target> TargetRowMapper = new RowMapper<Target>() { |
---|
[3292] | 61 | @Override |
---|
[3781] | 62 | public Target mapRow(ResultSet rs, int rowNumber) throws SQLException { |
---|
[4146] | 63 | XMLGregorianCalendar xmlDate = timeStampToXMLGregorianCalendar(rs); |
---|
| 64 | Target result = |
---|
| 65 | constructTarget(rs.getString(external_id), rs.getString(link_uri), rs.getString(version), xmlDate, rs.getString(fragment_descriptor)); |
---|
| 66 | return result; |
---|
[3292] | 67 | } |
---|
[3299] | 68 | }; |
---|
[4146] | 69 | |
---|
| 70 | @Override |
---|
[3793] | 71 | public String getLink(Number internalID) { |
---|
| 72 | StringBuilder sql = new StringBuilder("SELECT "); |
---|
| 73 | sql.append(TargetStar).append(" FROM ").append(targetTableName).append(" WHERE ").append(target_id).append("= ? LIMIT 1"); |
---|
| 74 | List<String> result = getSimpleJdbcTemplate().query(sql.toString(), linkRowMapper, internalID); |
---|
| 75 | return (!result.isEmpty() ? result.get(0) : null); |
---|
[3633] | 76 | } |
---|
[3793] | 77 | private final RowMapper<String> linkRowMapper = new RowMapper<String>() { |
---|
[3633] | 78 | @Override |
---|
[3793] | 79 | public String mapRow(ResultSet rs, int rowNumber) throws SQLException { |
---|
| 80 | return rs.getString(link_uri); |
---|
[3633] | 81 | } |
---|
| 82 | }; |
---|
[4146] | 83 | |
---|
| 84 | ///////////////////////////////////////// |
---|
[3633] | 85 | @Override |
---|
[3793] | 86 | public List<Number> getCachedRepresentations(Number targetID) { |
---|
[4146] | 87 | |
---|
[3936] | 88 | String sql = "SELECT " + cached_representation_id + " FROM " + targetsCachedRepresentationsTableName + " WHERE " + target_id + " = ?"; |
---|
[4146] | 89 | return getSimpleJdbcTemplate().query(sql, cachedIDRowMapper, targetID); |
---|
[3395] | 90 | } |
---|
| 91 | |
---|
[4146] | 92 | @Override |
---|
| 93 | public Map<Number, String> getCachedRepresentationFragmentPairs(Number targetID) { |
---|
| 94 | Map<Number, String> result = new HashMap<Number, String>(); |
---|
| 95 | String sql = "SELECT " + cached_representation_id + "," + fragment_descriptor_in_cached + " FROM " + targetsCachedRepresentationsTableName + " WHERE " + target_id + " = ?"; |
---|
| 96 | List<Map<Number, String>> respond = getSimpleJdbcTemplate().query(sql, cachedFragmentRowMapper, targetID); |
---|
| 97 | for (Map<Number, String> pair : respond) { |
---|
[3865] | 98 | result.putAll(pair); |
---|
| 99 | } |
---|
| 100 | return result; |
---|
[4146] | 101 | } |
---|
| 102 | private final RowMapper<Map<Number, String>> cachedFragmentRowMapper = new RowMapper<Map<Number, String>>() { |
---|
[3865] | 103 | @Override |
---|
| 104 | public Map<Number, String> mapRow(ResultSet rs, int rowNumber) throws SQLException { |
---|
[4146] | 105 | Map<Number, String> result = new HashMap<Number, String>(); |
---|
[3865] | 106 | result.put(rs.getInt(cached_representation_id), rs.getString(fragment_descriptor_in_cached)); |
---|
| 107 | return result; |
---|
| 108 | } |
---|
| 109 | }; |
---|
[4146] | 110 | |
---|
| 111 | /////////////////////////////////////////////////////////////////// |
---|
[3298] | 112 | @Override |
---|
[3865] | 113 | public List<TargetInfo> getTargetInfos(List<Number> targets) { |
---|
| 114 | if (targets == null) { |
---|
[3299] | 115 | return null; |
---|
| 116 | } |
---|
[3865] | 117 | if (targets.isEmpty()) { |
---|
[3781] | 118 | return new ArrayList<TargetInfo>(); |
---|
[3299] | 119 | } |
---|
[3395] | 120 | |
---|
[3865] | 121 | String targetIDs = makeListOfValues(targets); |
---|
[4146] | 122 | |
---|
[3464] | 123 | StringBuilder sql = new StringBuilder("SELECT "); |
---|
| 124 | sql.append(external_id).append(",").append(link_uri).append(",").append(version). |
---|
[3865] | 125 | append(" FROM ").append(targetTableName).append(" WHERE ").append(target_id).append(" IN ").append(targetIDs); |
---|
[3793] | 126 | return getSimpleJdbcTemplate().query(sql.toString(), targetInfoRowMapper); |
---|
[3220] | 127 | } |
---|
[3793] | 128 | private final RowMapper<TargetInfo> targetInfoRowMapper = new RowMapper<TargetInfo>() { |
---|
[3220] | 129 | @Override |
---|
[3781] | 130 | public TargetInfo mapRow(ResultSet rs, int rowNumber) throws SQLException { |
---|
| 131 | return constructTargetInfo(rs.getString(external_id), rs.getString(link_uri), rs.getString(version)); |
---|
[3220] | 132 | } |
---|
[3299] | 133 | }; |
---|
| 134 | |
---|
[3395] | 135 | ///////////////////////////////////////////////////// |
---|
[3220] | 136 | @Override |
---|
[3793] | 137 | public List<Number> getTargetsReferringTo(String word) { |
---|
[4146] | 138 | String searchTerm = "%" + word + "%"; |
---|
| 139 | StringBuilder sql = new StringBuilder("SELECT "); |
---|
[3804] | 140 | sql.append(target_id).append(" FROM ").append(targetTableName).append(" WHERE ").append(link_uri).append(" LIKE ? "); |
---|
| 141 | return getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, searchTerm); |
---|
[3299] | 142 | } |
---|
| 143 | |
---|
[4146] | 144 | ///////////////////////////////////////////////////// |
---|
[3793] | 145 | @Override |
---|
| 146 | public List<Number> getTargetsForLink(String link) { |
---|
| 147 | StringBuilder sql = new StringBuilder("SELECT "); |
---|
| 148 | sql.append(target_id).append(" FROM ").append(targetTableName).append(" WHERE ").append(link_uri).append(" = ? "); |
---|
| 149 | return getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, link); |
---|
| 150 | } |
---|
| 151 | |
---|
[3459] | 152 | ///////////////////////////////////////////////// |
---|
[3411] | 153 | @Override |
---|
[3793] | 154 | public boolean targetIsInUse(Number targetID) { |
---|
[3464] | 155 | StringBuilder sqlAnnotations = new StringBuilder("SELECT "); |
---|
[3781] | 156 | sqlAnnotations.append(annotation_id).append(" FROM ").append(annotationsTargetsTableName).append(" WHERE ").append(target_id).append(" = ? LIMIT 1"); |
---|
[3793] | 157 | List<Number> resultAnnotations = getSimpleJdbcTemplate().query(sqlAnnotations.toString(), annotationIDRowMapper, targetID); |
---|
[3633] | 158 | if (resultAnnotations == null) { |
---|
| 159 | return false; |
---|
| 160 | } |
---|
[4146] | 161 | return (resultAnnotations.size() > 0); |
---|
[3365] | 162 | } |
---|
[3459] | 163 | |
---|
| 164 | ///////////////////////// ADDERS ///////////////////////////////// |
---|
| 165 | @Override |
---|
[4146] | 166 | public Number addTarget(Target target) { |
---|
| 167 | UUID externalID = UUID.randomUUID(); |
---|
[3459] | 168 | Map<String, Object> params = new HashMap<String, Object>(); |
---|
[3463] | 169 | params.put("externalId", externalID.toString()); |
---|
[4010] | 170 | params.put("linkUri", target.getLink()); |
---|
| 171 | params.put("version", target.getVersion()); |
---|
[3464] | 172 | StringBuilder sql = new StringBuilder("INSERT INTO "); |
---|
[4146] | 173 | sql.append(targetTableName).append("(").append(external_id).append(",").append(link_uri).append(",").append(version).append(",").append(last_modified).append(" ) VALUES (:externalId, :linkUri, :version, current_timestamp AT TIME ZONE INTERVAL '00:00')"); |
---|
| 174 | final int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); |
---|
| 175 | return (affectedRows > 0 ? getInternalID(UUID.fromString(externalID.toString())) : null); |
---|
[3459] | 176 | } |
---|
[4146] | 177 | |
---|
[3459] | 178 | /////////////////////////////////////////////////////////////////// |
---|
| 179 | @Override |
---|
[4146] | 180 | public int addTargetCachedRepresentation(Number targetID, Number cachedID, String fragmentDescriptor) { |
---|
[3459] | 181 | Map<String, Object> paramsJoint = new HashMap<String, Object>(); |
---|
[3936] | 182 | paramsJoint.put("targetId", targetID); |
---|
[3633] | 183 | paramsJoint.put("cachedId", cachedID); |
---|
[3936] | 184 | paramsJoint.put("fragmentDescriptor", fragmentDescriptor); |
---|
| 185 | StringBuilder sqlJoint = new StringBuilder("INSERT INTO ").append(targetsCachedRepresentationsTableName).append("(").append(target_id).append(",").append(cached_representation_id).append(",").append(fragment_descriptor_in_cached).append(" ) VALUES (:targetId, :cachedId, :fragmentDescriptor)"); |
---|
[3464] | 186 | return getSimpleJdbcTemplate().update(sqlJoint.toString(), paramsJoint); |
---|
[3459] | 187 | } |
---|
[4146] | 188 | |
---|
| 189 | /////////////////////////////////////////////////////////////////// |
---|
[3633] | 190 | @Override |
---|
[4146] | 191 | public int updateSiblingClass(Number TargetID, int classID) { |
---|
[3781] | 192 | if (TargetID == null) { |
---|
[3633] | 193 | return 0; |
---|
| 194 | } |
---|
| 195 | StringBuilder sql = new StringBuilder("UPDATE "); |
---|
[3793] | 196 | sql.append(targetTableName).append(" SET ").append(sibling_Target_class).append("= '").append(classID).append("' WHERE ").append(target_id).append("= ?"); |
---|
[3781] | 197 | return getSimpleJdbcTemplate().update(sql.toString(), TargetID); |
---|
[3633] | 198 | } |
---|
[4146] | 199 | |
---|
[3459] | 200 | ////////////////////// DELETERS //////////////////////// |
---|
| 201 | @Override |
---|
[3781] | 202 | public int deleteTarget(Number internalID) { |
---|
[4146] | 203 | if (targetIsInUse(internalID)) { |
---|
[3459] | 204 | return 0; |
---|
[3395] | 205 | } |
---|
[3781] | 206 | StringBuilder sqlTargetsVersions = new StringBuilder("DELETE FROM "); |
---|
[3793] | 207 | sqlTargetsVersions.append(targetTableName).append(" WHERE ").append(target_id).append(" = ? "); |
---|
[3781] | 208 | return getSimpleJdbcTemplate().update(sqlTargetsVersions.toString(), internalID); |
---|
[3459] | 209 | |
---|
| 210 | } |
---|
| 211 | |
---|
| 212 | /////////////////////////////////////////////////////////////////// |
---|
| 213 | @Override |
---|
[4146] | 214 | public int deleteTargetCachedRepresentation(Number targetID, Number cachedID) { |
---|
[3781] | 215 | if (targetID == null || cachedID == null) { |
---|
[3633] | 216 | return 0; |
---|
| 217 | } |
---|
| 218 | Map<String, Object> paramsJoint = new HashMap<String, Object>(); |
---|
[3781] | 219 | paramsJoint.put("targetId", targetID); |
---|
[3633] | 220 | paramsJoint.put("cachedId", cachedID); |
---|
[3781] | 221 | StringBuilder sqlTargetsVersions = new StringBuilder("DELETE FROM "); |
---|
[3936] | 222 | sqlTargetsVersions.append(targetsCachedRepresentationsTableName).append(" WHERE ").append(target_id).append(" = :targetId"). |
---|
[3633] | 223 | append(" AND ").append(cached_representation_id).append(" = :cachedId"); |
---|
[3929] | 224 | return getSimpleJdbcTemplate().update(sqlTargetsVersions.toString(), paramsJoint); |
---|
[3459] | 225 | |
---|
| 226 | } |
---|
[3395] | 227 | |
---|
[4146] | 228 | /////////// HELPERS //////////////// |
---|
[3781] | 229 | private TargetInfo constructTargetInfo(String externalID, String link, String version) { |
---|
[4146] | 230 | TargetInfo targetInfo = new TargetInfo(); |
---|
| 231 | targetInfo.setRef(externalIDtoURI(externalID)); |
---|
| 232 | targetInfo.setLink(link); |
---|
| 233 | targetInfo.setVersion(version); |
---|
| 234 | return targetInfo; |
---|
[3220] | 235 | } |
---|
[3299] | 236 | |
---|
[3865] | 237 | private Target constructTarget(String externalID, String link, String version, XMLGregorianCalendar xmlTimeStamp, String fragment) { |
---|
[4146] | 238 | Target target = new Target(); |
---|
| 239 | target.setURI(externalIDtoURI(externalID)); |
---|
| 240 | target.setLastModified(xmlTimeStamp); |
---|
| 241 | target.setLink(link); |
---|
| 242 | target.setVersion(version); |
---|
| 243 | target.setFragmentDescriptor(fragment); |
---|
| 244 | return target; |
---|
[3292] | 245 | } |
---|
[3220] | 246 | } |
---|