예제 #1
0
        public List <CameraOwner> ReadCameraMakeModelAndOwners()
        {
            if (cameraMakeModelAndOwnersCache == null)
            {
                cameraMakeModelAndOwnersCache = new List <CameraOwner>();

                Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect;
                do
                {
                    sqlTransactionSelect = dbTools.TransactionBeginSelect();

                    #region SELECT CameraMake, CameraModel, UserAccount FROM CameraOwner
                    string sqlCommand = "SELECT CameraMake, CameraModel, UserAccount FROM CameraOwner";

                    using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect))
                    {
                        //commandDatabase.Prepare();
                        using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                CameraOwner cameraOwner = new CameraOwner(
                                    dbTools.ConvertFromDBValString(reader["CameraMake"]),
                                    dbTools.ConvertFromDBValString(reader["CameraModel"]),
                                    dbTools.ConvertFromDBValString(reader["UserAccount"]));
                                cameraMakeModelAndOwnersCache.Add(cameraOwner);
                            }
                        }
                    }
                    #endregion
                } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect));
            }
            return(cameraMakeModelAndOwnersCache);
        }
예제 #2
0
        public List <CameraOwner> ReadCameraMakeModelAndOwnersThatNotExist(List <CameraOwner> cameraOwners)
        {
            Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect;
            do
            {
                sqlTransactionSelect = dbTools.TransactionBeginSelect();

                #region SELECT DISTINCT CameraMake, CameraModel, NULL as UserAccount FROM MediaMetadata
                string sqlCommand = "SELECT DISTINCT CameraMake, CameraModel, NULL as UserAccount FROM MediaMetadata";

                using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect))
                {
                    //commandDatabase.Prepare();
                    using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            CameraOwner cameraOwner = new CameraOwner(
                                dbTools.ConvertFromDBValString(reader["CameraMake"]),
                                dbTools.ConvertFromDBValString(reader["CameraModel"]),
                                dbTools.ConvertFromDBValString(reader["UserAccount"]));
                            if (!string.IsNullOrWhiteSpace(cameraOwner.Make) &&
                                !string.IsNullOrWhiteSpace(cameraOwner.Model) &&
                                !CameraOwner.CameraMakeModelExistInList(cameraOwners, cameraOwner))
                            {
                                cameraOwners.Add(cameraOwner);
                            }
                        }
                    }
                }
                #endregion
            } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect));

            return(cameraOwners);
        }
예제 #3
0
        public List <string> ReadCameraOwners()
        {
            if (cameraOwnerCache == null)
            {
                Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect;
                do
                {
                    sqlTransactionSelect = dbTools.TransactionBeginSelect();

                    #region SELECT DISTINCT UserAccount FROM LocationSource
                    string sqlCommand = "SELECT DISTINCT UserAccount FROM LocationSource";
                    using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect))
                    {
                        //commandDatabase.Prepare();
                        commandDatabase.ExecuteNonQuery();      // Execute the query

                        using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
                        {
                            cameraOwnerCache = new List <string>();
                            while (reader.Read())
                            {
                                cameraOwnerCache.Add(dbTools.ConvertFromDBValString(reader["UserAccount"]));
                            }
                        }
                        if (cameraOwnerCache.Count == 0)
                        {
                            cameraOwnerCache.Add(MissingLocationsOwners);
                        }
                    }
                    #endregion
                } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect));
            }
            return(cameraOwnerCache);
        }
        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));
        }
예제 #5
0
        public List <FileEntryAttribute> ListFileEntryDateVersions(string fileDirectory, string fileName)
        {
            List <FileEntryAttribute> exifToolDates = new List <FileEntryAttribute>();

            Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect;
            do
            {
                sqlTransactionSelect = dbTools.TransactionBeginSelect();

                #region SELECT DISTINCT FileDirectory, FileName, FileDateModified FROM MediaExiftoolTagsWarning
                string sqlCommand = "SELECT DISTINCT FileDirectory, FileName, FileDateModified FROM MediaExiftoolTagsWarning " +
                                    "WHERE FileDirectory = @FileDirectory AND FileName = @FileName";
                using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect))
                {
                    FileEntryAttribute newstFileEntryAttributeForEdit = null;

                    //commandDatabase.Prepare();
                    commandDatabase.Parameters.AddWithValue("@FileDirectory", fileDirectory);
                    commandDatabase.Parameters.AddWithValue("@FileName", fileName);

                    using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bool     isErrorVersion      = false;
                            DateTime currentMetadataDate = (DateTime)dbTools.ConvertFromDBValDateTimeLocal(reader["FileDateModified"]);

                            FileEntryAttribute fileEntryAttribute = new FileEntryAttribute
                                                                    (
                                dbTools.ConvertFromDBValString(reader["FileDirectory"]),
                                dbTools.ConvertFromDBValString(reader["FileName"]),
                                currentMetadataDate,
                                FileEntryVersion.Historical
                                                                    );
                            exifToolDates.Add(fileEntryAttribute);

                            if (!isErrorVersion && (newstFileEntryAttributeForEdit == null || currentMetadataDate > newstFileEntryAttributeForEdit.LastWriteDateTime))
                            {
                                newstFileEntryAttributeForEdit = new FileEntryAttribute((FileEntry)fileEntryAttribute, FileEntryVersion.CurrentVersionInDatabase);
                            }
                        }

                        if (newstFileEntryAttributeForEdit != null)
                        {
                            exifToolDates.Add(newstFileEntryAttributeForEdit);
                        }
                    }

                    exifToolDates.Sort();
                }
                #endregion
            } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect));

            return(exifToolDates);
        }
        private LocationCoordinateAndDescription ReadLocationName(LocationCoordinate locationCoordinateSearch, float locationAccuracyLatitude, float locationAccuracyLongitude)
        {
            LocationCoordinateAndDescription locationCoordinateAndDescriptionInDatabase = null;

            Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect;
            do
            {
                sqlTransactionSelect = dbTools.TransactionBeginSelect();

                #region SELECT FROM LocationName
                string sqlCommand = "SELECT Latitude, Longitude, Name, City, Province, Country, " +
                                    "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 LIMIT 1";

                using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect))
                {
                    //commandDatabase.Prepare();
                    commandDatabase.Parameters.AddWithValue("@Latitude", locationCoordinateSearch.Latitude);
                    commandDatabase.Parameters.AddWithValue("@Longitude", locationCoordinateSearch.Longitude);
                    commandDatabase.Parameters.AddWithValue("@LocationAccuracyLatitude", locationAccuracyLatitude);
                    commandDatabase.Parameters.AddWithValue("@LocationAccuracyLongitude", locationAccuracyLongitude);

                    using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            locationCoordinateAndDescriptionInDatabase = new LocationCoordinateAndDescription();
                            locationCoordinateAndDescriptionInDatabase.Coordinate.Latitude  = (float)dbTools.ConvertFromDBValFloat(reader["Latitude"]);
                            locationCoordinateAndDescriptionInDatabase.Coordinate.Longitude = (float)dbTools.ConvertFromDBValFloat(reader["Longitude"]);
                            locationCoordinateAndDescriptionInDatabase.Description.Name     = dbTools.ConvertFromDBValString(reader["Name"]);
                            locationCoordinateAndDescriptionInDatabase.Description.City     = dbTools.ConvertFromDBValString(reader["City"]);
                            locationCoordinateAndDescriptionInDatabase.Description.Region   = dbTools.ConvertFromDBValString(reader["Province"]);
                            locationCoordinateAndDescriptionInDatabase.Description.Country  = dbTools.ConvertFromDBValString(reader["Country"]);

                            locationCoordinateAndDescriptionInDatabase.Description.Name    = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Name) ? null : locationCoordinateAndDescriptionInDatabase.Description.Name;
                            locationCoordinateAndDescriptionInDatabase.Description.Region  = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Region) ? null : locationCoordinateAndDescriptionInDatabase.Description.Region;
                            locationCoordinateAndDescriptionInDatabase.Description.City    = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.City) ? null : locationCoordinateAndDescriptionInDatabase.Description.City;
                            locationCoordinateAndDescriptionInDatabase.Description.Country = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Country) ? null : locationCoordinateAndDescriptionInDatabase.Description.Country;
                            LocationCoordinateAndDescriptionUpdate(locationCoordinateSearch, locationCoordinateAndDescriptionInDatabase.Coordinate, locationCoordinateAndDescriptionInDatabase.Description);
                        }
                    }
                }
                #endregion
            } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect));

            return(locationCoordinateAndDescriptionInDatabase);
        }
예제 #7
0
        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 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 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);
        }
        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);
        }
예제 #15
0
        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));
        }