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}