public SqliteCommand(string commandText, Mono.Data.Sqlite.SqliteConnection connection, Mono.Data.Sqlite.SqliteTransaction transaction)
{
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if (!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if (ret)
{
using (SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if (cn1.State == System.Data.ConnectionState.Open)
{
using (SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if (cn2.State == System.Data.ConnectionState.Open)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
using (SQLiteTransaction tr = cn2.BeginTransaction())
{
try
{
List <long> add = new List <long>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
long id = rd.GetInt64(0);
using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using (SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if (!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach (long id in add)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch (Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return(ret);
}
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));
}
