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 void ReadToCache(HashSet <FileEntry> fileEntries) { List <FileEntry> fileEntriesPutInCache = new List <FileEntry>(); foreach (FileEntry fileEntryToCheckInCache in fileEntries) { Image image = ReadThumbnailFromCacheOnly(fileEntryToCheckInCache); if (image == null) { fileEntriesPutInCache.Add(fileEntryToCheckInCache); } } Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT Image FROM MediaThumbnail string sqlCommand = "SELECT Image FROM MediaThumbnail WHERE FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { foreach (FileEntry fileEntry in fileEntriesPutInCache) { if (StopCaching) { StopCaching = false; return; } //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", fileEntry.Directory); commandDatabase.Parameters.AddWithValue("@FileName", fileEntry.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(fileEntry.LastWriteDateTime)); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { Image image = dbTools.ByteArrayToImage(dbTools.ConvertFromDBValByteArray(reader["Image"])); ThumbnailCacheUpdate(fileEntry, image); } } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); }
public void WriteLocationHistory(string userAccount, GoogleJsonLocations googleJsonLocations) { #region INSERT OR IGNORE INTO LocationHistory string sqlCommand = "INSERT OR IGNORE INTO LocationHistory (UserAccount, TimeStamp, Latitude, Longitude, Altitude, Accuracy) " + "Values (@UserAccount, @TimeStamp, @Latitude, @Longitude, @Altitude, @Accuracy) "; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase)) //, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@UserAccount", userAccount); commandDatabase.Parameters.AddWithValue("@TimeStamp", dbTools.ConvertFromDateTimeToDBVal(googleJsonLocations.Timestamp)); commandDatabase.Parameters.AddWithValue("@Latitude", googleJsonLocations.Latitude); commandDatabase.Parameters.AddWithValue("@Longitude", googleJsonLocations.Longitude); commandDatabase.Parameters.AddWithValue("@Altitude", googleJsonLocations.Altitude); commandDatabase.Parameters.AddWithValue("@Accuracy", googleJsonLocations.Accuracy); commandDatabase.ExecuteNonQuery(); // Execute the query } #endregion }
public HashSet <LocationsHistory> LoadLocationHistory(DateTime dateTimeFrom, DateTime dateTimeTo) { HashSet <LocationsHistory> locationsHistories = new HashSet <LocationsHistory>(); Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT UserAccount, TimeStamp, Latitude, Longitude, Altitude, Accuracy FROM LocationHistory string sqlCommand = "SELECT UserAccount, TimeStamp, Latitude, Longitude, Altitude, Accuracy FROM LocationHistory WHERE " + "TimeStamp >= @dateTimeFrom AND TimeStamp <= @dateTimeTo " + "ORDER BY TimeStamp LIMIT 5000"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@dateTimeFrom", dbTools.ConvertFromDateTimeToDBVal(dateTimeFrom)); commandDatabase.Parameters.AddWithValue("@dateTimeTo", dbTools.ConvertFromDateTimeToDBVal(dateTimeTo)); //commandDatabase.Parameters.AddWithValue("@UserAccount", userAccount); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { LocationsHistory locationsHistory = new LocationsHistory(); locationsHistory.UserAccount = dbTools.ConvertFromDBValString(reader["UserAccount"]); locationsHistory.Timestamp = (DateTime)dbTools.ConvertFromDBValDateTimeUtc(reader["TimeStamp"]); locationsHistory.LocationCoordinate = new LocationCoordinate( (float)dbTools.ConvertFromDBValFloat(reader["Latitude"]), (float)dbTools.ConvertFromDBValFloat(reader["Longitude"]) ); locationsHistory.Altitude = (float)dbTools.ConvertFromDBValFloat(reader["Altitude"]); locationsHistory.Accuracy = (float)dbTools.ConvertFromDBValFloat(reader["Accuracy"]); locationsHistories.Add(locationsHistory); } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(locationsHistories); }
public Dictionary <LocationCoordinate, LocationDescription> ReadAllLocationsData() { Dictionary <LocationCoordinate, LocationDescription> locations = new Dictionary <LocationCoordinate, LocationDescription>(); Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT Latitude, Longitude, Name, City, Province, Country FROM LocationName string sqlCommand = "SELECT Latitude, Longitude, Name, City, Province, Country FROM LocationName"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { LocationCoordinate locationCoordinate = new LocationCoordinate( (float)dbTools.ConvertFromDBValFloat(reader["Latitude"]), (float)dbTools.ConvertFromDBValFloat(reader["Longitude"])); LocationDescription locationDescription = new LocationDescription( dbTools.ConvertFromDBValString(reader["Name"]), dbTools.ConvertFromDBValString(reader["City"]), dbTools.ConvertFromDBValString(reader["Province"]), dbTools.ConvertFromDBValString(reader["Country"])); if (!locations.ContainsKey(locationCoordinate)) { locations.Add(locationCoordinate, locationDescription); } LocationCoordinateAndDescriptionUpdate(locationCoordinate, locationCoordinate, locationDescription); } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(locations); }
public bool Move(string oldDirectory, string oldFilename, string newDirectory, string newFilename) { bool movedOk = true; ThumnbailCacheRemove(oldDirectory, oldFilename); string oldPath = Path.Combine(oldDirectory, oldFilename).ToLower(); string newPath = Path.Combine(newDirectory, newFilename).ToLower(); if (string.Compare(oldPath, newPath, true) != 0) { Mono.Data.Sqlite.SqliteTransaction sqlTransaction; do { sqlTransaction = dbTools.TransactionBegin(); #region UPDATE MediaThumbnail string sqlCommand = "UPDATE MediaThumbnail SET " + "FileDirectory = @NewFileDirectory, FileName = @NewFileName " + "WHERE FileDirectory = @OldFileDirectory AND FileName = @OldFileName"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); //commandDatabase.Parameters.AddWithValue("@Broker", (int)broker); commandDatabase.Parameters.AddWithValue("@OldFileName", oldFilename); commandDatabase.Parameters.AddWithValue("@OldFileDirectory", oldDirectory); commandDatabase.Parameters.AddWithValue("@NewFileName", newFilename); commandDatabase.Parameters.AddWithValue("@NewFileDirectory", newDirectory); if (commandDatabase.ExecuteNonQuery() == -1) { movedOk = false; } } #endregion } while (!dbTools.TransactionCommit(sqlTransaction)); } return(movedOk); }
public int DeleteDirectoryAndHistory(string fileDirectory) { int recordAffected = 0; Mono.Data.Sqlite.SqliteTransaction sqlTransaction; do { sqlTransaction = dbTools.TransactionBegin(); #region DELETE FROM MediaExiftoolTags string sqlCommand = "DELETE FROM MediaExiftoolTags WHERE FileDirectory = @FileDirectory"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", fileDirectory); recordAffected = commandDatabase.ExecuteNonQuery(); // Execute the query } #endregion } while (!dbTools.TransactionCommit(sqlTransaction)); return(recordAffected); }
public List <FileEntry> ListFileEntryDateVersions(string fileDirectory, string fileName) { List <FileEntry> fileEntries = new List <FileEntry>(); Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT DISTINCT FileDirectory, FileName, FileDateModified FROM MediaThumbnail string sqlCommand = "SELECT DISTINCT FileDirectory, FileName, FileDateModified FROM MediaThumbnail " + "WHERE FileDirectory = @FileDirectory AND FileName = @FileName"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", fileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", fileName); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { FileEntry fileEntry = new FileEntry ( dbTools.ConvertFromDBValString(reader["FileDirectory"]), dbTools.ConvertFromDBValString(reader["FileName"]), (DateTime)dbTools.ConvertFromDBValDateTimeLocal(reader["FileDateModified"]) ); fileEntries.Add(fileEntry); } } //fileEntries.Sort(); } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(fileEntries); }
private void DeleteLocationName(LocationCoordinate locationCoordinateInDatabase, float locationAccuracyLatitude, float locationAccuracyLongitude) { Mono.Data.Sqlite.SqliteTransaction sqlTransaction; do { sqlTransaction = dbTools.TransactionBegin(); #region DELETE FROM LocationName string sqlCommand = "DELETE FROM LocationName " + //"WHERE Latitude = @Latitude AND Longitude = @Longitude"; "WHERE Latitude = (SELECT Latitude FROM (" + "SELECT Latitude, Name, Max(Abs(Latitude-@Latitude), Abs(Longitude - @Longitude)) AS Distance " + "FROM LocationName WHERE Latitude >= (@Latitude - @LocationAccuracyLatitude) AND Latitude <= (@Latitude + @LocationAccuracyLatitude) " + "AND Longitude >= (@Longitude - @LocationAccuracyLongitude) AND Longitude <= (@Longitude + @LocationAccuracyLongitude) " + "ORDER BY Distance DESC LIMIT 1 " + ")) AND " + "Longitude = (SELECT Longitude FROM (" + "SELECT Longitude, Name, Max(Abs(Latitude-@Latitude), Abs(Longitude - @Longitude)) AS Distance " + "FROM LocationName WHERE Latitude >= (@Latitude - @LocationAccuracyLatitude) AND Latitude <= (@Latitude + @LocationAccuracyLatitude) " + "AND Longitude >= (@Longitude - @LocationAccuracyLongitude) AND Longitude <= (@Longitude + @LocationAccuracyLongitude) " + "ORDER BY Distance DESC LIMIT 1))"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@Latitude", locationCoordinateInDatabase.Latitude); commandDatabase.Parameters.AddWithValue("@Longitude", locationCoordinateInDatabase.Longitude); commandDatabase.Parameters.AddWithValue("@LocationAccuracyLatitude", locationAccuracyLatitude); commandDatabase.Parameters.AddWithValue("@LocationAccuracyLongitude", locationAccuracyLongitude); int affectedRows = commandDatabase.ExecuteNonQuery(); // Execute the query if (affectedRows <= 0) { //DEBUG, This means problem with accurcy in number } LocationCoordinateAndDescriptionDelete(locationCoordinateInDatabase: locationCoordinateInDatabase); } #endregion } while (!dbTools.TransactionCommit(sqlTransaction)); }
public void Write(ExiftoolData exifToolData) { int resultRowsAffected = 1; Mono.Data.Sqlite.SqliteTransaction sqlTransaction = null; do { #region If failed to updated data, delete and retry if (resultRowsAffected == -1) { Logger.Error("Delete MediaExiftoolTags data due to previous application crash for file: " + exifToolData.FullFilePath); dbTools.TransactionRollback(sqlTransaction); DeleteFileEntryMediaExiftoolTags(new FileEntry(exifToolData.FileDirectory, exifToolData.FileName, exifToolData.FileDateModified)); } #endregion sqlTransaction = dbTools.TransactionBegin(); #region INSERT INTO MediaExiftoolTags string sqlCommand = "INSERT INTO MediaExiftoolTags (FileDirectory, FileName, FileDateModified, Region, Command, Parameter) " + "Values (@FileDirectory, @FileName, @FileDateModified, @Region, @Command, @Parameter)"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolData.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolData.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolData.FileDateModified)); commandDatabase.Parameters.AddWithValue("@Region", exifToolData.Region); commandDatabase.Parameters.AddWithValue("@Command", exifToolData.Command); commandDatabase.Parameters.AddWithValue("@Parameter", exifToolData.Parameter); resultRowsAffected = commandDatabase.ExecuteNonQuery(); } #endregion } while (resultRowsAffected == -1 || !dbTools.TransactionCommit(sqlTransaction)); }
public void DeleteThumbnails(List <FileEntry> fileEntries) { Mono.Data.Sqlite.SqliteTransaction sqlTransaction; do { sqlTransaction = dbTools.TransactionBegin(); #region DELETE FROM MediaThumbnail string sqlCommand = "DELETE FROM MediaThumbnail WHERE FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { foreach (FileEntry fileEntry in fileEntries) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", fileEntry.Directory); commandDatabase.Parameters.AddWithValue("@FileName", fileEntry.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(fileEntry.LastWriteDateTime)); commandDatabase.ExecuteNonQuery(); // Execute the query ThumnbailCacheRemove(fileEntry); } } #endregion } while (!dbTools.TransactionCommit(sqlTransaction)); }
public Metadata FindLocationBasedOnTime(String userAccount, DateTime?datetime, int acceptDiffrentSecound) { Metadata metadataResult = null; Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT UserAccount, TimeStamp, Latitude, Longitude, Altitude, Accuracy FROM LocationHistory //I could use pythagoras to get excact distance, but I don't see the point of doing that string sqlCommand = "SELECT UserAccount, TimeStamp, Latitude, Longitude, Altitude, Accuracy FROM LocationHistory WHERE " + "UserAccount = @UserAccount AND " + "( " + "TimeStamp = (SELECT MAX(TimeStamp) AS TimeStampPrevious FROM LocationHistory WHERE " + "TimeStamp <= @TimeStamp " + "AND UserAccount = @UserAccount) " + "OR " + "TimeStamp = " + "(SELECT MIN(TimeStamp) AS TimeStampNext FROM LocationHistory WHERE " + "TimeStamp >= @TimeStamp " + "AND UserAccount = @UserAccount) " + ")"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@TimeStamp", dbTools.ConvertFromDateTimeToDBVal(datetime)); commandDatabase.Parameters.AddWithValue("@UserAccount", userAccount); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { Metadata metadata = new Metadata(MetadataBrokerType.GoogleLocationHistory); DateTime?minTimeStamp = null; float? minLatitude = null; float? minLongitude = null; float? minAltitude = null; float? minAccuracy = null; if (reader.Read()) { #region Read minLatitude, minLongitude, minAltitude minTimeStamp = (DateTime)dbTools.ConvertFromDBValDateTimeUtc(reader["TimeStamp"]); minLatitude = dbTools.ConvertFromDBValFloat(reader["Latitude"]); minLongitude = dbTools.ConvertFromDBValFloat(reader["Longitude"]); minAltitude = dbTools.ConvertFromDBValFloat(reader["Altitude"]); minAccuracy = dbTools.ConvertFromDBValFloat(reader["Accuracy"]); if (minTimeStamp == datetime) { metadata.LocationLatitude = minLatitude; metadata.LocationLongitude = minLongitude; metadata.LocationAltitude = minAltitude; } #endregion if (reader.Read()) { #region Read maxLatitude, maxLongitude, maxAltitude, maxAccuracy DateTime?maxTimeStamp = (DateTime)dbTools.ConvertFromDBValDateTimeUtc(reader["TimeStamp"]); float? maxLatitude = dbTools.ConvertFromDBValFloat(reader["Latitude"]); float? maxLongitude = dbTools.ConvertFromDBValFloat(reader["Longitude"]); float? maxAltitude = dbTools.ConvertFromDBValFloat(reader["Altitude"]); float? maxAccuracy = dbTools.ConvertFromDBValFloat(reader["Accuracy"]); #endregion #region Calc diff Double lowDiffInSeconds = Math.Abs((minTimeStamp - datetime).Value.TotalSeconds); Double highDiffInSeconds = Math.Abs((maxTimeStamp - datetime).Value.TotalSeconds); Double totalDifInSeconds = lowDiffInSeconds + highDiffInSeconds; #endregion #region If diff < acceptDiffrentSecound - Set result metadata //if (Math.Min(lowDiffInSeconds, highDiffInSeconds) < acceptDiffrentSecound) if (totalDifInSeconds < acceptDiffrentSecound) { metadata.LocationAltitude = (float) (((minAltitude * (totalDifInSeconds - lowDiffInSeconds)) + (maxAltitude * (totalDifInSeconds - highDiffInSeconds))) / totalDifInSeconds); metadata.LocationLatitude = (float) (((minLatitude * (totalDifInSeconds - lowDiffInSeconds)) + (maxLatitude * (totalDifInSeconds - highDiffInSeconds))) / totalDifInSeconds); metadata.LocationLongitude = (float) (((minLongitude * (totalDifInSeconds - lowDiffInSeconds)) + (maxLongitude * (totalDifInSeconds - highDiffInSeconds))) / totalDifInSeconds); metadataResult = metadata; } #endregion } } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(metadataResult); }
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); }
public Metadata Read(MetadataBrokerType broker, string fullFilePath) { Metadata metadata = null; string fileDirectory = Path.GetDirectoryName(fullFilePath); string fileName = Path.GetFileName(fullFilePath); #region SELECT String query = "SELECT(WITH RECURSIVE " + "under_alice(folderid, folderlevel, foldername) AS( " + "VALUES(Item_ParentFolderId, 0, NULL) " + "UNION ALL " + "SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " + "FROM Folder JOIN under_alice ON Folder.Folder_Id = under_alice.folderid " + "), path_from_root AS( " + "SELECT folderlevel, foldername, folderid " + "FROM under_alice " + "ORDER BY folderlevel DESC " + ") " + "SELECT group_concat(foldername, '/') " + "FROM path_from_root " + "ORDER BY folderlevel DESC) " + "AS ItemPath, " + "Item_Filename, Item_ParentFolderId, " + "Item_Filename, Item_FileSize, " + "Location.Location_Name, LocationCountry.LocationCountry_Name, " + "LocationDistrict.LocationDistrict_Name, LocationRegion.LocationRegion_Name, " + "Item_DateTaken, Item_DateCreated, Item_DateModified, Item_Caption, Album.Album_Name, Item_SimpleRating, " + "Item_Width, Item_Height, " + "CameraManufacturer.CameraManufacturer_Text, CameraModel.CameraModel_Text, " + "Item_Latitude, Item_Longitude FROM Item " + "INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " + "LEFT OUTER JOIN Location ON Item.Item_LocationId = Location.Location_Id " + "LEFT OUTER JOIN LocationCountry ON Location.Location_LocationCountryId = LocationCountry.LocationCountry_Id " + "LEFT OUTER JOIN LocationDistrict ON Location.Location_LocationDistrictId = LocationDistrict.LocationDistrict_Id " + "LEFT OUTER JOIN LocationRegion ON Location.Location_LocationRegionId = LocationRegion.LocationRegion_Id " + "LEFT OUTER JOIN CameraManufacturer ON Item.Item_CameraManufacturerId = CameraManufacturer.CameraManufacturer_Id " + "LEFT OUTER JOIN CameraModel ON Item.Item_CameraModelId = CameraModel.CameraModel_Id " + "LEFT OUTER JOIN AlbumItemLink ON AlbumItemLink.AlbumItemLink_ItemId = Item.Item_Id " + "LEFT OUTER JOIN Album ON Album.Album_Id = AlbumItemLink.AlbumItemLink_AlbumId " + "WHERE Item_Filename LIKE @FileName"; //"WHERE Item_Filename = '" + fullFilePath + "'"; //"AND ItemPath = '" + path + "%'"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase)) { commandDatabase.Parameters.AddWithValue("@FileName", fileName); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]); itemPath = itemPath.Replace("/", "\\"); if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true) { //File metadata = new Metadata(broker); metadata.FileName = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Item_Filename"]); //metadata.FileDirectory = dbTools.ConvertFromDBValString(reader["ItemPath"]); metadata.FileDirectory = fileDirectory; //Override path from database, it's not a complete folder path, missing root path metadata.FileSize = dbToolsMicrosoftReader.ConvertFromDBValLong(reader["Item_FileSize"]); metadata.FileDateCreated = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateCreated"], DateTimeKind.Utc); metadata.FileDateModified = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateModified"], DateTimeKind.Utc); if (metadata.FileDateCreated == null || metadata.FileDateModified == null || metadata.FileDateAccessed == null || File.Exists(fullFilePath)) { try { //Due to sometimes NULL in Microsoft Database, I always use current file attributes. FileInfo fileInfo = new FileInfo(fullFilePath); metadata.FileDateCreated = fileInfo.CreationTime; //File.GetCreationTime(fullFilePath); metadata.FileDateModified = fileInfo.LastWriteTime; //File.GetLastWriteTime(fullFilePath); metadata.FileDateAccessed = fileInfo.LastAccessTime; //File.GetLastAccessTime(fullFilePath); } catch (Exception ex) { Logger.Error(ex); metadata.FileDateCreated = DateTime.Now; metadata.FileDateModified = metadata.FileDateCreated; //File.GetLastWriteTime(fullFilePath); metadata.FileDateAccessed = metadata.FileDateCreated; //File.GetLastAccessTime(fullFilePath); } } //Personal metadata.PersonalTitle = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Item_Caption"]); //metaData.PersonalDescription = dbTools.ConvertFromDBValString(reader["Item_Caption"]); metadata.PersonalRating = dbToolsMicrosoftReader.ConvertFromDBValByte(reader["Item_SimpleRating"]); //metaData.PersonalAuthor = dbTools.ConvertFromDBValString(reader["Unknown"]); metadata.PersonalAlbum = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Album_Name"]); //Media metadata.MediaWidth = dbToolsMicrosoftReader.ConvertFromDBValInt(reader["Item_Width"]); metadata.MediaHeight = dbToolsMicrosoftReader.ConvertFromDBValInt(reader["Item_Height"]); metadata.MediaDateTaken = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateTaken"], DateTimeKind.Local); //Camera metadata.CameraMake = dbToolsMicrosoftReader.ConvertFromDBValString(reader["CameraManufacturer_Text"]); metadata.CameraModel = dbToolsMicrosoftReader.ConvertFromDBValString(reader["CameraModel_Text"]); //Location metadata.LocationName = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Location_Name"]); metadata.LocationCountry = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationCountry_Name"]); metadata.LocationCity = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationDistrict_Name"]); metadata.LocationState = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationRegion_Name"]); metadata.LocationLatitude = dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Item_Latitude"]); metadata.LocationLongitude = dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Item_Longitude"]); if (metadata.LocationLatitude == 0 && metadata.LocationLongitude == 0) //Due to bug in Microsoft Photos Gallery { metadata.LocationLatitude = null; metadata.LocationLongitude = null; } break; } } } } #endregion if (metadata == null) { return(null); } #region SELECT query = "SELECT(WITH RECURSIVE " + "under_alice(folderid, folderlevel, foldername) AS( " + "VALUES(Item_ParentFolderId, 0, NULL) " + "UNION ALL " + "SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " + "FROM Folder JOIN under_alice ON Folder.Folder_Id = under_alice.folderid " + "), path_from_root AS( " + "SELECT folderlevel, foldername, folderid " + "FROM under_alice " + "ORDER BY folderlevel DESC " + ") " + "SELECT group_concat(foldername, '/') " + "FROM path_from_root " + "ORDER BY folderlevel DESC) " + "AS ItemPath, Item_Filename, Item_FileSize, " + "Person_Name, Face_Rect_Left, Face_Rect_Top, Face_Rect_Width, Face_Rect_Height " + "FROM Item " + "INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " + "INNER JOIN Face ON Face.Face_ItemId = Item.Item_Id " + "INNER JOIN Person ON Person.Person_Id = Face_PersonId " + "WHERE " + "Item.Item_Filename LIKE @FileName"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase)) { commandDatabase.Parameters.AddWithValue("@FileName", fileName); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]); itemPath = itemPath.Replace("/", "\\"); if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true) { RegionStructure region = new RegionStructure(); region.Name = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Person_Name"]); region.AreaX = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Left"]); region.AreaY = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Top"]); region.AreaWidth = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Width"]); region.AreaHeight = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Height"]); region.Type = "Face"; region.RegionStructureType = RegionStructureTypes.MicrosoftPhotosDatabase; metadata.PersonalRegionListAddIfNotExists(region); } } } } #endregion #region SELECT query = "SELECT (WITH RECURSIVE under_alice(folderid, folderlevel, foldername) AS(VALUES(Item_ParentFolderId, 0, NULL) " + "UNION ALL SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " + "FROM Folder JOIN under_alice " + "ON Folder.Folder_Id = under_alice.folderid), path_from_root AS " + "(SELECT folderlevel, foldername, folderid FROM under_alice ORDER BY folderlevel DESC) " + "SELECT group_concat(foldername, '/') FROM path_from_root ORDER BY folderlevel DESC) AS ItemPath, " + "Item_Filename, Item_FileSize, Item.Item_DateModified, Item.Item_DateCreated " + ", Item.Item_Id " + ", ItemTags1.ItemTags_TagId " + //Workaround, I have now clue why, when ItemTags_Confidence not selected this way, then indexes wasn't used ", (SELECT ItemTags_Confidence FROM ItemTags AS ItemTags2 Where ItemTags2.ItemTags_TagId = ItemTags1.ItemTags_TagId ) AS ItemTags_Confidence " + ", (SELECT TagVariant_Text FROM TagVariant Where TagVariant.TagVariant_TagResourceId = Tag.Tag_ResourceId ) AS TagVariant_Text " + "FROM Item " + "INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " + "INNER JOIN ItemTags AS ItemTags1 ON ItemTags1.ItemTags_ItemId = Item.Item_Id " + "INNER JOIN Tag ON ItemTags1.ItemTags_TagId = Tag.Tag_Id " + "WHERE Item.Item_Filename LIKE @FileName"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase)) { commandDatabase.Parameters.AddWithValue("@FileName", fileName); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]); itemPath = itemPath.Replace("/", "\\"); if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true) { KeywordTag keywordTag = new KeywordTag( dbToolsMicrosoftReader.ConvertFromDBValString(reader["TagVariant_Text"]), (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["ItemTags_Confidence"]) ); metadata.PersonalKeywordTagsAddIfNotExists(keywordTag); } } } } #endregion return(metadata); }
public void Write(ExiftoolData exifToolOldValue, ExiftoolData exifToolNewValue, string warning) { int resultRowsAffected = 1; Mono.Data.Sqlite.SqliteTransaction sqlTransaction = null; do { #region If failed to updated data, delete and retry if (resultRowsAffected == -1) { Logger.Error("Delete MediaExiftoolTagsWarning data due to previous application crash for file: " + exifToolNewValue.FullFilePath); try { dbTools.TransactionRollback(sqlTransaction); } catch { } //Delete all extries due to crash. DeleteFileEntryFromMediaExiftoolTagsWarning(new FileEntry(exifToolNewValue.FileDirectory, exifToolNewValue.FileName, exifToolNewValue.FileDateModified)); } #endregion sqlTransaction = dbTools.TransactionBegin(); #region SELECT Warning FROM MediaExiftoolTagsWarning warning = "(Logged: " + DateTime.Now.ToString() + ")\r\n" + warning; string sqlRead = "SELECT Warning FROM MediaExiftoolTagsWarning WHERE " + "FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified AND " + "OldRegion = @OldRegion AND OldCommand = @OldCommand AND " + "NewRegion = @NewRegion AND NewCommand = @NewCommand"; bool oldRecordFound = false; using (var commandDatabase = new CommonSqliteCommand(sqlRead, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { warning = warning + "\r\n" + dbTools.ConvertFromDBValString(reader["Warning"]); } } oldRecordFound = true; } #endregion #region DELETE FROM MediaExiftoolTagsWarning if (oldRecordFound) { string sqlDelete = "DELETE FROM MediaExiftoolTagsWarning WHERE " + "FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified AND " + "OldRegion = @OldRegion AND OldCommand = @OldCommand AND " + "NewRegion = @NewRegion AND NewCommand = @NewCommand"; using (var commandDatabase = new CommonSqliteCommand(sqlDelete, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); resultRowsAffected = commandDatabase.ExecuteNonQuery(); } } #endregion #region INSERT INTO MediaExiftoolTagsWarning if (resultRowsAffected != -1) { string sqlCommand = "INSERT INTO MediaExiftoolTagsWarning (FileDirectory, FileName, FileDateModified, OldRegion, OldCommand, OldParameter, NewRegion, NewCommand, NewParameter, Warning) " + "Values (@FileDirectory, @FileName, @FileDateModified, @OldRegion, @OldCommand, @OldParameter, @NewRegion, @NewCommand, @NewParameter, @Warning)"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@OldParameter", exifToolOldValue.Parameter); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); commandDatabase.Parameters.AddWithValue("@NewParameter", exifToolNewValue.Parameter); commandDatabase.Parameters.AddWithValue("@Warning", warning); resultRowsAffected = commandDatabase.ExecuteNonQuery(); } } #endregion } while (resultRowsAffected == -1 || !dbTools.TransactionCommit(sqlTransaction)); }