public void ReadToCacheFolder(string directory) { if (readFolderToCacheCached.Contains(directory)) { return; } readFolderToCacheCached.Add(directory); Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT FileDirectory, FileName, FileDateModified, Image FROM MediaThumbnail string sqlCommand = "SELECT FileDirectory, FileName, FileDateModified, Image FROM MediaThumbnail"; if (!string.IsNullOrWhiteSpace(directory)) { sqlCommand += " WHERE FileDirectory = @FileDirectory"; } using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { if (!string.IsNullOrWhiteSpace(directory)) { commandDatabase.Parameters.AddWithValue("@FileDirectory", directory); } //commandDatabase.Prepare(); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { if (StopCaching) { try { readFolderToCacheCached.Remove(directory); } catch { } StopCaching = false; return; } FileEntry fileEntry = new FileEntry( dbTools.ConvertFromDBValString(reader["FileDirectory"]), dbTools.ConvertFromDBValString(reader["FileName"]), (DateTime)dbTools.ConvertFromDBValDateTimeLocal(reader["FileDateModified"]) ); Image image = dbTools.ByteArrayToImage(dbTools.ConvertFromDBValByteArray(reader["Image"])); ThumbnailCacheUpdate(fileEntry, image); } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); }
public List <ExiftoolWarningData> Read(FileEntry fileEntry) { List <ExiftoolWarningData> exifToolDataList = new List <ExiftoolWarningData>(); Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT MediaExiftoolTagsWarning string sqlCommand = "SELECT FileDirectory, FileName, FileDateModified, OldRegion, OldCommand, OldParameter, NewRegion, NewCommand, NewParameter, Warning FROM " + "MediaExiftoolTagsWarning WHERE FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified=@FileDateModified"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", Path.GetDirectoryName(fileEntry.FileFullPath)); commandDatabase.Parameters.AddWithValue("@FileName", Path.GetFileName(fileEntry.FileFullPath)); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(fileEntry.LastWriteDateTime)); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { ExiftoolWarningData exifToolWarningData = new ExiftoolWarningData(); exifToolWarningData.OldExiftoolData = new ExiftoolData( dbTools.ConvertFromDBValString(reader["FileDirectory"]), dbTools.ConvertFromDBValString(reader["FileName"]), (DateTime)dbTools.ConvertFromDBValDateTimeLocal(reader["FileDateModified"]), dbTools.ConvertFromDBValString(reader["OldRegion"]), dbTools.ConvertFromDBValString(reader["OldCommand"]), dbTools.ConvertFromDBValString(reader["OldParameter"]), null); exifToolWarningData.NewExiftoolData = new ExiftoolData( dbTools.ConvertFromDBValString(reader["FileName"]), dbTools.ConvertFromDBValString(reader["FileDirectory"]), (DateTime)dbTools.ConvertFromDBValDateTimeLocal(reader["FileDateModified"]), dbTools.ConvertFromDBValString(reader["NewRegion"]), dbTools.ConvertFromDBValString(reader["NewCommand"]), dbTools.ConvertFromDBValString(reader["NewParameter"]), null); exifToolWarningData.WarningMessage = dbTools.ConvertFromDBValString(reader["Warning"]); exifToolDataList.Add(exifToolWarningData); } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(exifToolDataList); }
public List <Metadata> FindLocationBasedOtherMediaFiles(DateTime?locationDateTime, DateTime?mediaDateTaken, DateTime?fileDate, int acceptDiffrentSecound) { if (locationDateTime == null && mediaDateTaken == null && fileDate == null) { return(null); } string sqlCommand = ""; #region LocationDateTime if (locationDateTime != null) { sqlCommand += "(SELECT 1 AS Priority, LocationDateTime AS Date, ABS(LocationDateTime - @LocationDateTime) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE LocationDateTime >= @LocationDateTime " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY LocationDateTime LIMIT 3) " + "UNION SELECT * FROM " + "(SELECT 1 AS Priority, LocationDateTime AS Date, ABS(LocationDateTime - @LocationDateTime) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE LocationDateTime <= @LocationDateTime " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY LocationDateTime DESC LIMIT 3) "; } #endregion #region MediaDateTaken if (mediaDateTaken != null) { sqlCommand += (sqlCommand == "" ? "" : "UNION SELECT * FROM ") + "(SELECT 2 AS Priority, MediaDateTaken AS Date, ABS(MediaDateTaken - @MediaDateTaken) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE MediaDateTaken >= @MediaDateTaken " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY MediaDateTaken LIMIT 3) " + "UNION SELECT * FROM " + "(SELECT 2 AS Priority, MediaDateTaken AS Date, ABS(MediaDateTaken - @MediaDateTaken) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE MediaDateTaken <= @MediaDateTaken " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY MediaDateTaken DESC LIMIT 3) "; } #endregion #region FileDateCreated if (fileDate != null) { sqlCommand += (sqlCommand == "" ? "" : "UNION SELECT * FROM ") + "(SELECT 3 AS Priority, FileDateCreated AS Date, ABS(FileDateCreated - @FileDateCreated) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE FileDateCreated >= @FileDateCreated " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY FileDateCreated LIMIT 3) " + "UNION SELECT * FROM " + "(SELECT 3 AS Priority, FileDateCreated AS Date, ABS(FileDateCreated - @FileDateCreated) AS TimeDistance, LocationLatitude, LocationLongitude FROM MediaMetadata " + "WHERE FileDateCreated <= @FileDateCreated " + "AND LocationLatitude != 0 AND LocationLongitude != 0 " + "AND (LocationLatitude IS NOT NULL OR LocationLongitude IS NOT NULL) " + "ORDER BY FileDateCreated DESC LIMIT 3) "; } #endregion List <Metadata> metadatas; Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT FROM MediaMetadata sqlCommand = "SELECT * FROM " + sqlCommand + "ORDER BY Priority, TimeDistance "; //"LIMIT 1"; metadatas = new List <Metadata>(); using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); if (locationDateTime != null) { commandDatabase.Parameters.AddWithValue("@LocationDateTime", dbTools.ConvertFromDateTimeToDBVal(locationDateTime)); } if (mediaDateTaken != null) { commandDatabase.Parameters.AddWithValue("@MediaDateTaken", dbTools.ConvertFromDateTimeToDBVal(mediaDateTaken)); } if (fileDate != null) { commandDatabase.Parameters.AddWithValue("@FileDateCreated", dbTools.ConvertFromDateTimeToDBVal(fileDate)); } //if (fileDateCreated != null) commandDatabase.Parameters.AddWithValue("@FileDateCreated", dbTools.ConvertFromDateTimeToDBVal(fileDateCreated)); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { long? priority = dbTools.ConvertFromDBValLong(reader["Priority"]); DateTime?date = dbTools.ConvertFromDBValDateTimeLocal(reader["Date"]); long? timeDistance = dbTools.ConvertFromDBValLong(reader["TimeDistance"]); float? locationLatitude = dbTools.ConvertFromDBValFloat(reader["LocationLatitude"]); float? locationLongitude = dbTools.ConvertFromDBValFloat(reader["LocationLongitude"]); if (timeDistance / 100000 < acceptDiffrentSecound) { Metadata metadata = new Metadata(MetadataBrokerType.GoogleLocationHistory); switch (priority) { case 3: metadata.FileDateCreated = date; break; case 2: metadata.MediaDateTaken = date; break; case 1: metadata.LocationDateTime = date; break; } metadata.LocationLatitude = locationLatitude; metadata.LocationLongitude = locationLongitude; bool doesCoordinatesExist = false; foreach (Metadata metadataToCheck in metadatas) { if (metadataToCheck.LocationLatitude == metadata.LocationLatitude && metadataToCheck.LocationLongitude == metadata.LocationLongitude) { doesCoordinatesExist = true; } } if (!doesCoordinatesExist) { metadatas.Add(metadata); } } } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(metadatas); }