001/* 002 * Syncany, www.syncany.org 003 * Copyright (C) 2011-2016 Philipp C. Heckel <philipp.heckel@gmail.com> 004 * 005 * This program is free software: you can redistribute it and/or modify 006 * it under the terms of the GNU General Public License as published by 007 * the Free Software Foundation, either version 3 of the License, or 008 * (at your option) any later version. 009 * 010 * This program is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 013 * GNU General Public License for more details. 014 * 015 * You should have received a copy of the GNU General Public License 016 * along with this program. If not, see <http://www.gnu.org/licenses/>. 017 */ 018package org.syncany.database.dao; 019 020import java.sql.Connection; 021import java.sql.PreparedStatement; 022import java.sql.ResultSet; 023import java.sql.SQLException; 024import java.sql.Timestamp; 025import java.util.ArrayList; 026import java.util.Collection; 027import java.util.Date; 028import java.util.HashMap; 029import java.util.Iterator; 030import java.util.List; 031import java.util.Map; 032import java.util.Set; 033import java.util.TreeMap; 034import java.util.logging.Level; 035import java.util.logging.Logger; 036 037import org.syncany.database.FileContent.FileChecksum; 038import org.syncany.database.FileVersion; 039import org.syncany.database.FileVersion.FileStatus; 040import org.syncany.database.FileVersion.FileType; 041import org.syncany.database.PartialFileHistory; 042import org.syncany.database.PartialFileHistory.FileHistoryId; 043import org.syncany.operations.cleanup.CleanupOperationOptions.TimeUnit; 044import org.syncany.util.StringUtil; 045 046import com.google.common.collect.ImmutableMap; 047 048/** 049 * The file version DAO queries and modifies the <i>fileversion</i> in 050 * the SQL database. This table corresponds to the Java object {@link FileVersion}. 051 * 052 * @author Philipp C. Heckel (philipp.heckel@gmail.com) 053 */ 054public class FileVersionSqlDao extends AbstractSqlDao { 055 private static final Logger logger = Logger.getLogger(FileVersionSqlDao.class.getSimpleName()); 056 private static final Map<TimeUnit, String> timeUnitSqlTimeUnitMap = new ImmutableMap.Builder<TimeUnit, String>() 057 .put(TimeUnit.SECONDS, "SS") 058 .put(TimeUnit.MINUTES, "MI") 059 .put(TimeUnit.HOURS, "HH") 060 .put(TimeUnit.DAYS, "DD") 061 .put(TimeUnit.WEEKS, "WW") 062 .put(TimeUnit.MONTHS, "MM") 063 .put(TimeUnit.YEARS, "YYY") 064 .build(); 065 066 public FileVersionSqlDao(Connection connection) { 067 super(connection); 068 } 069 070 /** 071 * Writes a list of {@link FileVersion} to the database table <i>fileversion</i> using <code>INSERT</code>s 072 * and the given connection. 073 * 074 * <p><b>Note:</b> This method executes, but <b>does not commit</b> the queries. 075 * 076 * @param connection The connection used to execute the statements 077 * @param fileHistoryId References the {@link PartialFileHistory} to which the list of file versions belongs 078 * @param databaseVersionId References the {@link PartialFileHistory} to which the list of file versions belongs 079 * @param fileVersions List of {@link FileVersion}s to be written to the database 080 * @throws SQLException If the SQL statement fails 081 */ 082 public void writeFileVersions(Connection connection, FileHistoryId fileHistoryId, long databaseVersionId, Collection<FileVersion> fileVersions) 083 throws SQLException { 084 PreparedStatement preparedStatement = getStatement(connection, "fileversion.insert.writeFileVersions.sql"); 085 086 for (FileVersion fileVersion : fileVersions) { 087 String fileContentChecksumStr = (fileVersion.getChecksum() != null) ? fileVersion.getChecksum().toString() : null; 088 089 preparedStatement.setString(1, fileHistoryId.toString()); 090 preparedStatement.setInt(2, Integer.parseInt("" + fileVersion.getVersion())); 091 preparedStatement.setLong(3, databaseVersionId); 092 preparedStatement.setString(4, fileVersion.getPath()); 093 preparedStatement.setString(5, fileVersion.getType().toString()); 094 preparedStatement.setString(6, fileVersion.getStatus().toString()); 095 preparedStatement.setLong(7, fileVersion.getSize()); 096 preparedStatement.setTimestamp(8, new Timestamp(fileVersion.getLastModified().getTime())); 097 preparedStatement.setString(9, fileVersion.getLinkTarget()); 098 preparedStatement.setString(10, fileContentChecksumStr); 099 preparedStatement.setTimestamp(11, new Timestamp(fileVersion.getUpdated().getTime())); 100 preparedStatement.setString(12, fileVersion.getPosixPermissions()); 101 preparedStatement.setString(13, fileVersion.getDosAttributes()); 102 103 preparedStatement.addBatch(); 104 } 105 106 preparedStatement.executeBatch(); 107 preparedStatement.close(); 108 } 109 110 /** 111 * Removes {@link FileVersion}s from the database table <i>fileversion</i> for which the 112 * the corresponding database is marked <code>DIRTY</code>. 113 * 114 * <p><b>Note:</b> This method executes, but does not commit the query. 115 * 116 * @throws SQLException If the SQL statement fails 117 */ 118 public void removeDirtyFileVersions() throws SQLException { 119 try (PreparedStatement preparedStatement = getStatement("fileversion.delete.dirty.removeDirtyFileVersions.sql")) { 120 preparedStatement.executeUpdate(); 121 } 122 } 123 124 /** 125 * Removes all file versions with versions <b>lower or equal</b> than the given file version. 126 * 127 * <p>Note that this method does not just delete the given file version, but also all of its 128 * previous versions. 129 */ 130 public void removeFileVersions(Map<FileHistoryId, FileVersion> purgeFileVersions) throws SQLException { 131 if (purgeFileVersions.size() > 0) { 132 try (PreparedStatement preparedStatement = getStatement(connection, "fileversion.delete.all.removeFileVersionsByIds.sql")) { 133 for (Map.Entry<FileHistoryId, FileVersion> purgeFileVersionEntry : purgeFileVersions.entrySet()) { 134 FileHistoryId purgeFileHistoryId = purgeFileVersionEntry.getKey(); 135 FileVersion purgeFileVersion = purgeFileVersionEntry.getValue(); 136 137 preparedStatement.setString(1, purgeFileHistoryId.toString()); 138 preparedStatement.setLong(2, purgeFileVersion.getVersion()); 139 140 preparedStatement.addBatch(); 141 } 142 143 preparedStatement.executeBatch(); 144 } 145 } 146 } 147 148 public void removeSpecificFileVersions(Map<FileHistoryId, List<FileVersion>> purgeFileVersions) throws SQLException { 149 if (purgeFileVersions.size() > 0) { 150 try (PreparedStatement preparedStatement = getStatement(connection, "fileversion.delete.all.removeSpecificFileVersionsByIds.sql")) { 151 for (FileHistoryId purgeFileHistoryId : purgeFileVersions.keySet()) { 152 for (FileVersion purgeFileVersion : purgeFileVersions.get(purgeFileHistoryId)) { 153 preparedStatement.setString(1, purgeFileHistoryId.toString()); 154 preparedStatement.setLong(2, purgeFileVersion.getVersion()); 155 156 preparedStatement.addBatch(); 157 } 158 } 159 160 preparedStatement.executeBatch(); 161 } 162 } 163 } 164 165 /** 166 * Queries the database for the currently active {@link FileVersion}s and returns it 167 * as a map. If the current file tree (on the disk) has not changed, the result will 168 * match the files on the disk. 169 * 170 * <p>Keys in the returned map correspond to the file version's relative file path, 171 * and values to the actual {@link FileVersion} object. 172 * 173 * @return Returns the current file tree as a map of relative paths to {@link FileVersion} objects 174 */ 175 public Map<String, FileVersion> getCurrentFileTree() { 176 try (PreparedStatement preparedStatement = getStatement("fileversion.select.master.getCurrentFileTree.sql")) { 177 Map<String, FileVersion> fileTree = new TreeMap<>(); 178 List<FileVersion> fileList = getFileTree(preparedStatement); 179 180 for (FileVersion fileVersion : fileList) { 181 fileTree.put(fileVersion.getPath(), fileVersion); 182 } 183 184 return fileTree; 185 186 } 187 catch (SQLException e) { 188 throw new RuntimeException(e); 189 } 190 } 191 192 public List<FileVersion> getFileHistory(FileHistoryId fileHistoryId) { 193 try (PreparedStatement preparedStatement = getStatement("fileversion.select.master.getFileHistoryById.sql")) { 194 preparedStatement.setString(1, fileHistoryId.toString()); 195 196 List<FileVersion> fileTree = new ArrayList<FileVersion>(); 197 198 try (ResultSet resultSet = preparedStatement.executeQuery()) { 199 while (resultSet.next()) { 200 FileVersion fileVersion = createFileVersionFromRow(resultSet); 201 fileTree.add(fileVersion); 202 } 203 204 return fileTree; 205 } 206 catch (SQLException e) { 207 throw new RuntimeException(e); 208 } 209 } 210 catch (SQLException e) { 211 throw new RuntimeException(e); 212 } 213 } 214 215 public List<FileVersion> getFileList(String pathExpression, Date date, boolean fileHistoryId, boolean recursive, boolean deleted, 216 Set<FileType> fileTypes) { 217 218 // Determine sensible query parameters 219 // Basic idea: If null/empty given, match them all! 220 221 String fileHistoryPrefix = null; 222 223 if (fileHistoryId) { 224 fileHistoryPrefix = (pathExpression == null || "".equals(pathExpression)) ? "%" : pathExpression; 225 pathExpression = "%"; 226 } 227 else { 228 fileHistoryPrefix = "%"; 229 pathExpression = (pathExpression == null || "".equals(pathExpression)) ? "%" : pathExpression; 230 } 231 232 date = (date == null) ? new Date(4133984461000L) : date; 233 234 int slashCount = StringUtil.substrCount(pathExpression, "/"); 235 int filterMinSlashCount = (recursive || fileHistoryId) ? 0 : slashCount; 236 int filterMaxSlashCount = (recursive || fileHistoryId) ? Integer.MAX_VALUE : slashCount; 237 238 String[] fileTypesStr = createFileTypesArray(fileTypes); 239 String fileStatusNotEqualTo = (deleted) ? "INVALID" : FileStatus.DELETED.toString(); 240 241 if (logger.isLoggable(Level.INFO)) { 242 logger.log(Level.INFO, " getFileTree(path = " + pathExpression + ", history = " + fileHistoryPrefix + ", minSlash = " 243 + filterMinSlashCount + ", maxSlash = " + filterMaxSlashCount + ", date <= " + date + ", types = " 244 + StringUtil.join(fileTypesStr, ", ")); 245 } 246 247 try (PreparedStatement preparedStatement = getStatement("fileversion.select.master.getFilteredFileTree.sql")) { 248 preparedStatement.setString(1, fileStatusNotEqualTo); 249 preparedStatement.setString(2, pathExpression); 250 preparedStatement.setString(3, fileHistoryPrefix); 251 preparedStatement.setInt(4, filterMinSlashCount); 252 preparedStatement.setInt(5, filterMaxSlashCount); 253 preparedStatement.setArray(6, connection.createArrayOf("varchar", fileTypesStr)); 254 preparedStatement.setTimestamp(7, new Timestamp(date.getTime())); 255 256 return getFileTree(preparedStatement); 257 } 258 catch (SQLException e) { 259 throw new RuntimeException(e); 260 } 261 } 262 263 private String[] createFileTypesArray(Set<FileType> fileTypes) { 264 String[] fileTypesStr = null; 265 266 if (fileTypes != null) { 267 fileTypesStr = new String[fileTypes.size()]; 268 269 int i = 0; 270 271 for (Iterator<FileType> fileTypeIterator = fileTypes.iterator(); fileTypeIterator.hasNext();) { 272 fileTypesStr[i++] = fileTypeIterator.next().toString(); 273 } 274 } 275 else { 276 fileTypesStr = new String[] { FileType.FILE.toString(), FileType.FOLDER.toString(), FileType.SYMLINK.toString() }; 277 } 278 279 return fileTypesStr; 280 } 281 282 public Map<FileHistoryId, List<FileVersion>> getFileHistoriesToPurgeInInterval(long beginTimestamp, long endTimestamp, TimeUnit timeUnit) { 283 try (PreparedStatement preparedStatement = getStatement("fileversion.select.all.getPurgeVersionsByInterval.sql")) { 284 String timeUnitIdentifier = timeUnitSqlTimeUnitMap.get(timeUnit); 285 286 preparedStatement.setString(1, timeUnitIdentifier); 287 preparedStatement.setTimestamp(2, new Timestamp(beginTimestamp)); 288 preparedStatement.setTimestamp(3, new Timestamp(endTimestamp)); 289 290 return getAllVersionsInQuery(preparedStatement); 291 } 292 catch (SQLException e) { 293 throw new RuntimeException(e); 294 } 295 } 296 297 public Map<FileHistoryId, List<FileVersion>> getFileHistoriesToPurgeBefore(long timestamp) { 298 try (PreparedStatement preparedStatement = getStatement("fileversion.select.all.getPurgeVersionsBeforeTime.sql")) { 299 preparedStatement.setTimestamp(1, new Timestamp(timestamp)); 300 return getAllVersionsInQuery(preparedStatement); 301 } 302 catch (SQLException e) { 303 throw new RuntimeException(e); 304 } 305 } 306 307 public Map<FileHistoryId, FileVersion> getDeletedFileVersionsBefore(long timestamp) { 308 try (PreparedStatement preparedStatement = getStatement("fileversion.select.all.getDeletedFileVersionsBefore.sql")) { 309 preparedStatement.setTimestamp(1, new Timestamp(timestamp)); 310 return getSingleVersionInHistory(preparedStatement); 311 } 312 catch (SQLException e) { 313 throw new RuntimeException(e); 314 } 315 316 } 317 318 public FileVersion getFileVersion(FileHistoryId fileHistoryId, long version) { 319 try (PreparedStatement preparedStatement = getStatement("fileversion.select.master.getFileVersionByHistoryAndVersion.sql")) { 320 preparedStatement.setString(1, fileHistoryId.toString()); 321 preparedStatement.setLong(2, version); 322 323 return executeAndCreateFileVersion(preparedStatement); 324 } 325 catch (SQLException e) { 326 throw new RuntimeException(e); 327 } 328 } 329 330 private Map<FileHistoryId, FileVersion> getSingleVersionInHistory(PreparedStatement preparedStatement) throws SQLException { 331 try (ResultSet resultSet = preparedStatement.executeQuery()) { 332 Map<FileHistoryId, FileVersion> mostRecentPurgeFileVersions = new HashMap<FileHistoryId, FileVersion>(); 333 334 while (resultSet.next()) { 335 FileHistoryId fileHistoryId = FileHistoryId.parseFileId(resultSet.getString("filehistory_id")); 336 FileVersion fileVersion = createFileVersionFromRow(resultSet); 337 338 mostRecentPurgeFileVersions.put(fileHistoryId, fileVersion); 339 } 340 341 return mostRecentPurgeFileVersions; 342 } 343 } 344 345 private Map<FileHistoryId, List<FileVersion>> getAllVersionsInQuery(PreparedStatement preparedStatement) throws SQLException { 346 try (ResultSet resultSet = preparedStatement.executeQuery()) { 347 Map<FileHistoryId, List<FileVersion>> fileHistoryPurgeFileVersions = new HashMap<FileHistoryId, List<FileVersion>>(); 348 349 while (resultSet.next()) { 350 FileHistoryId fileHistoryId = FileHistoryId.parseFileId(resultSet.getString("filehistory_id")); 351 FileVersion fileVersion = createFileVersionFromRow(resultSet); 352 353 List<FileVersion> purgeFileVersions = fileHistoryPurgeFileVersions.get(fileHistoryId); 354 355 if (purgeFileVersions == null) { 356 purgeFileVersions = new ArrayList<FileVersion>(); 357 fileHistoryPurgeFileVersions.put(fileHistoryId, purgeFileVersions); 358 } 359 360 purgeFileVersions.add(fileVersion); 361 } 362 363 return fileHistoryPurgeFileVersions; 364 } 365 } 366 367 private List<FileVersion> getFileTree(PreparedStatement preparedStatement) { 368 List<FileVersion> fileTree = new ArrayList<>(); 369 370 try (ResultSet resultSet = preparedStatement.executeQuery()) { 371 while (resultSet.next()) { 372 FileVersion fileVersion = createFileVersionFromRow(resultSet); 373 fileTree.add(fileVersion); 374 } 375 376 return fileTree; 377 } 378 catch (SQLException e) { 379 throw new RuntimeException(e); 380 } 381 } 382 383 private FileVersion executeAndCreateFileVersion(PreparedStatement preparedStatement) { 384 try (ResultSet resultSet = preparedStatement.executeQuery()) { 385 if (resultSet.next()) { 386 return createFileVersionFromRow(resultSet); 387 } 388 else { 389 return null; 390 } 391 } 392 catch (SQLException e) { 393 throw new RuntimeException(e); 394 } 395 } 396 397 // TODO [low] This should be private; but it has to be public for a test 398 public FileVersion createFileVersionFromRow(ResultSet resultSet) throws SQLException { 399 FileVersion fileVersion = new FileVersion(); 400 401 fileVersion.setFileHistoryId(FileHistoryId.parseFileId(resultSet.getString("filehistory_id"))); 402 fileVersion.setVersion(resultSet.getLong("version")); 403 fileVersion.setPath(resultSet.getString("path")); 404 fileVersion.setType(FileType.valueOf(resultSet.getString("type"))); 405 fileVersion.setStatus(FileStatus.valueOf(resultSet.getString("status"))); 406 fileVersion.setSize(resultSet.getLong("size")); 407 fileVersion.setLastModified(new Date(resultSet.getTimestamp("lastmodified").getTime())); 408 409 if (resultSet.getString("linktarget") != null) { 410 fileVersion.setLinkTarget(resultSet.getString("linktarget")); 411 } 412 413 if (resultSet.getString("filecontent_checksum") != null) { 414 FileChecksum fileChecksum = FileChecksum.parseFileChecksum(resultSet.getString("filecontent_checksum")); 415 fileVersion.setChecksum(fileChecksum); 416 } 417 418 if (resultSet.getString("updated") != null) { 419 fileVersion.setUpdated(new Date(resultSet.getTimestamp("updated").getTime())); 420 } 421 422 if (resultSet.getString("posixperms") != null) { 423 fileVersion.setPosixPermissions(resultSet.getString("posixperms")); 424 } 425 426 if (resultSet.getString("dosattrs") != null) { 427 fileVersion.setDosAttributes(resultSet.getString("dosattrs")); 428 } 429 430 return fileVersion; 431 } 432 433 434}