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 DeleteFileEntriesFromMediaExiftoolTags(List <FileEntry> fileEntries)
{
Mono.Data.Sqlite.SqliteTransaction sqlTransaction;
do
{
sqlTransaction = dbTools.TransactionBegin();
#region DELETE FROM MediaExiftoolTags
string sqlCommand = "DELETE FROM MediaExiftoolTags " +
"WHERE FileDirectory = @FileDirectory " +
"AND FileName = @FileName " +
"AND FileDateModified = @FileDateModified ";
using (var 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
}
}
#endregion
} while (!dbTools.TransactionCommit(sqlTransaction));
}
public void WriteLocationHistorySource(string userAccount, string fileNamePath)
{
if (File.Exists(fileNamePath))
{
Mono.Data.Sqlite.SqliteTransaction sqlTransaction;
do
{
sqlTransaction = dbTools.TransactionBegin();
#region INSERT INTO LocationSource
string sqlCommand =
"INSERT INTO LocationSource (UserAccount, FileDirectory, FileName, FileDateModified, FileDateImported) " +
"Values (@UserAccount, @FileDirectory, @FileName, @FileDateModified, @FileDateImported)";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@UserAccount", userAccount);
commandDatabase.Parameters.AddWithValue("@FileDirectory", Path.GetDirectoryName(fileNamePath));
commandDatabase.Parameters.AddWithValue("@FileName", Path.GetFileName(fileNamePath));
commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(File.GetLastWriteTimeUtc(fileNamePath)));
commandDatabase.Parameters.AddWithValue("@FileDateImported", dbTools.ConvertFromDateTimeToDBVal(DateTime.UtcNow));
commandDatabase.ExecuteNonQuery(); // Execute the query
}
#endregion
dbTools.TransactionCommit(sqlTransaction);
} while (!dbTools.TransactionCommit(sqlTransaction));
}
}
public void WriteLocationName(LocationCoordinate locationCoordinateSearch, LocationCoordinateAndDescription locationInDatabaseCoordinateAndDescription)
{
Mono.Data.Sqlite.SqliteTransaction sqlTransaction;
do
{
sqlTransaction = dbTools.TransactionBegin();
#region INSERT INTO LocationName
string sqlCommand =
"INSERT INTO LocationName (Latitude, Longitude, Name, City, Province, Country) " +
"Values (@Latitude, @Longitude, @Name, @City, @Province, @Country)";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
locationInDatabaseCoordinateAndDescription.Description.Name = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Name) ? null : locationInDatabaseCoordinateAndDescription.Description.Name;
locationInDatabaseCoordinateAndDescription.Description.Region = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Region) ? null : locationInDatabaseCoordinateAndDescription.Description.Region;
locationInDatabaseCoordinateAndDescription.Description.City = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.City) ? null : locationInDatabaseCoordinateAndDescription.Description.City;
locationInDatabaseCoordinateAndDescription.Description.Country = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Country) ? null : locationInDatabaseCoordinateAndDescription.Description.Country;
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@Latitude", locationInDatabaseCoordinateAndDescription.Coordinate.Latitude);
commandDatabase.Parameters.AddWithValue("@Longitude", locationInDatabaseCoordinateAndDescription.Coordinate.Longitude);
commandDatabase.Parameters.AddWithValue("@Name", locationInDatabaseCoordinateAndDescription.Description.Name);
commandDatabase.Parameters.AddWithValue("@City", locationInDatabaseCoordinateAndDescription.Description.City);
commandDatabase.Parameters.AddWithValue("@Province", locationInDatabaseCoordinateAndDescription.Description.Region);
commandDatabase.Parameters.AddWithValue("@Country", locationInDatabaseCoordinateAndDescription.Description.Country);
int rowAffted = commandDatabase.ExecuteNonQuery(); // Execute the query
LocationCoordinateAndDescriptionUpdate(locationCoordinateSearch, locationInDatabaseCoordinateAndDescription.Coordinate, locationInDatabaseCoordinateAndDescription.Description);
}
#endregion
} while (!dbTools.TransactionCommit(sqlTransaction));
}
public void WriteThumbnail(FileEntry fileEntry, Image image)
{
//Don't do DeleteThumbnail(fileDirectory, fileName, size); //It create a lot overhead
//Do to read thumbnail only write back what doesn't exist
Mono.Data.Sqlite.SqliteTransaction sqlTransaction;
do
{
sqlTransaction = dbTools.TransactionBegin();
#region INSERT INTO MediaThumbnail
string sqlCommand =
"INSERT INTO MediaThumbnail (FileDirectory, FileName, FileDateModified,Image) " +
"Values (@FileDirectory, @FileName, @FileDateModified, @Image)";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@FileDirectory", fileEntry.Directory);
commandDatabase.Parameters.AddWithValue("@FileName", fileEntry.FileName);
commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(fileEntry.LastWriteDateTime));
commandDatabase.Parameters.AddWithValue("@Image", dbTools.ImageToByteArray(image));
int rowsAffected = commandDatabase.ExecuteNonQuery();
if (rowsAffected != 1)
{
//DEBUG
} // Execute the query
}
#endregion
} while (!dbTools.TransactionCommit(sqlTransaction));
ThumbnailCacheUpdate(fileEntry, image);
}
public void SaveCameraMakeModelAndOwner(CameraOwner cameraOwner)
{
if (cameraOwner == null)
{
return;
}
if (string.IsNullOrWhiteSpace(cameraOwner.Make))
{
cameraOwner.Make = CameraOwner.UnknownMake;
}
if (string.IsNullOrWhiteSpace(cameraOwner.Model))
{
cameraOwner.Model = CameraOwner.UnknownModel;
}
//if (string.IsNullOrWhiteSpace(cameraOwner.Owner)) cameraOwner.Owner = CameraOwner.UnknownOwner;
Mono.Data.Sqlite.SqliteTransaction sqlTransaction;
do
{
sqlTransaction = dbTools.TransactionBegin();
#region DELETE FROM CameraOwner
string sqlCommand =
"DELETE FROM CameraOwner WHERE " +
"CameraMake = @CameraMake AND CameraModel = @CameraModel";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@CameraMake", cameraOwner.Make);
commandDatabase.Parameters.AddWithValue("@CameraModel", cameraOwner.Model);
commandDatabase.ExecuteNonQuery(); // Execute the query
}
#endregion
#region INSERT INTO CameraOwner
sqlCommand =
"INSERT INTO CameraOwner (CameraMake, CameraModel, UserAccount) " +
"Values (@CameraMake, @CameraModel, @UserAccount)";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@CameraMake", cameraOwner.Make);
commandDatabase.Parameters.AddWithValue("@CameraModel", cameraOwner.Model);
commandDatabase.Parameters.AddWithValue("@UserAccount", cameraOwner.Owner);
commandDatabase.ExecuteNonQuery(); // Execute the query
}
#endregion
} while (!dbTools.TransactionCommit(sqlTransaction));
MakeCameraOwnersDirty();
}
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 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);
}
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 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));
}