public static void createTable(Type ja)
{
try
{
SqliteCommand sql = new SqliteCommand("DROP TABLE " + ja.Name, SqliteManager.connection);
sql.ExecuteNonQuery();
}
catch (Exception e)
{
}
string tmp = "CREATE TABLE " + ja.Name + "(";
System.Reflection.FieldInfo[] fieldInfo = ja.GetFields();
bool was = false;
string primary = "";
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
Type temp = info.FieldType;
if (was) tmp += ", ";
tmp += info.Name + " ";
was = true;
if (temp == typeof(long))
{
tmp += " INT";
}
else
if (temp == typeof(string))
{
tmp += " TEXT";
}
else
if (temp == typeof(double))
{
tmp += " REAL";
}
else
if (temp == typeof(bool))
{
tmp += " INT";
}
else
{
tmp += " TEXT";
}
if (info.IsDefined(typeof(PrimaryKey), true))
{
if (primary.Length != 0) primary += ", ";
primary += info.Name;
}
}
if (primary.Length > 0)
{
tmp += ", PRIMARY KEY(" + primary + ") ";
}
tmp += ");";
SqliteCommand sql2 = new SqliteCommand(tmp, SqliteManager.connection);
sql2.ExecuteNonQuery();
}
public void InsertRow(string table, Dictionary <string, object> columnData, bool orIgnore, bool keepTrying)
{
string insertString = String.Format(
"INSERT{3}INTO {0} ({1}) VALUES ({2})",
table,
string.Join(",", columnData.Keys),
string.Join(",", columnData.Keys.Select(s => String.Format("@{0}_param", s)).ToArray()),
orIgnore ? " OR IGNORE " : " "
);
#if USE_SQLITE
int attempts = 0;
while (attempts < MAX_ATTEMPTS)
{
try {
using (SQLiteCommand insertCmd = new SQLiteCommand(insertString, this.database)) {
foreach (string columnKey in columnData.Keys)
{
#if USE_SQLITE_MANAGED
insertCmd.Parameters.Add(
String.Format("@{0}_param", columnKey), columnData[columnKey]
);
#else
insertCmd.Parameters.AddWithValue(
String.Format("@{0}_param", columnKey), columnData[columnKey]
);
#endif // USE_SQLITE_MANAGED
}
insertCmd.ExecuteNonQuery();
// We were successful, so just go ahead.
attempts = MAX_ATTEMPTS;
}
#if USE_SQLITE_MANAGED
} catch (SQLiteBusyException ex) {
if (keepTrying)
{
// Increment and try again.
attempts++;
Thread.Sleep(1000);
}
else
{
// Just throw it upwards if we're not to keep trying.
throw new SimpleDBLayerBusyException(ex.Message);
}
#endif // USE_SQLITE_MANAGED
} catch (SQLiteException ex) {
// TODO: Detect busy exception and handle with keepTrying if not managed.
throw new SimpleDBLayerException(ex.Message);
}
}
#endif
}
private void CreateHistoryTable()
{
string sql = @"
CREATE TABLE history (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
client_id varchar(32) NOT NULL,
from_jid varchar(3071),
to_jid varchar(3071),
timestamp INTEGER NOT NULL,
message TEXT
)";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public void DeleteRows(string table, DBCondition[] conditions, bool keepTrying)
{
#if USE_SQLITE
string[] conditionStrings = TransformConditionStrings(conditions);
string deleteString = String.Format(
"DELETE FROM {0} WHERE {1}",
table,
string.Join(",", conditionStrings)
);
int attempts = 0;
while (attempts < MAX_ATTEMPTS)
{
try {
using (SQLiteCommand deleteCommand = new SQLiteCommand(deleteString, this.database)) {
// Add the proxies.
foreach (DBCondition condition in conditions)
{
#if USE_SQLITE_MANAGED
trafficReadCommand.Parameters.Add(
TransformProxyToken(condition),
condition.TestValue
);
#else
deleteCommand.Parameters.AddWithValue(
TransformProxyToken(condition),
condition.TestValue
);
#endif // USE_SQLITE_MANAGED
}
deleteCommand.ExecuteNonQuery();
// We were successful, so just go ahead.
attempts = MAX_ATTEMPTS;
}
} catch (SQLiteException ex) {
// TODO: Detect busy exception and handle with keepTrying if not managed.
throw new SimpleDBLayerException(ex.Message);
}
}
#endif
}
private void CreateVcard()
{
string sql = @"
CREATE TABLE contact_details (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
jid varchar(2048) NOT NULL,
hash varchar(2048) NOT NULL,
fullname varchar(2048) NULL,
url varchar(2048) NULL,
birthday INTEGER NULL,
details TEXT NULL,
photoType varchar(2048) NULL,
photoBin BLOB NULL
);
CREATE INDEX IX_contact_details ON contact_details(jid, hash)
";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public void Compact(bool keepTrying)
{
#if USE_SQLITE
int attempts = 0;
while (attempts < MAX_ATTEMPTS)
{
try {
using (SQLiteCommand cleanCommand = new SQLiteCommand("VACUUM;", this.database)) {
cleanCommand.ExecuteNonQuery();
// We were successful, so just go ahead.
attempts = MAX_ATTEMPTS;
}
} catch (SQLiteException ex) {
// TODO: Detect busy exception and handle with keepTrying if not managed.
throw new SimpleDBLayerException(ex.Message);
}
}
#endif // USE_SQLITE
}
private void CreateDiscoCache()
{
string sql = @"
CREATE TABLE caps_info (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
node varchar(2048) NULL,
version varchar(2048) NULL,
hash varchar(2048) NULL
);
CREATE TABLE feature (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
uri varchar(2048) UNIQUE NOT NULL
);
CREATE TABLE caps_to_feature (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
caps_info_id int NOT NULL,
feature_id int NOT NULL,
FOREIGN KEY (caps_info_id) REFERENCES caps_info(id),
FOREIGN KEY (feature_id) REFERENCES feature(id)
)";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public void SetupVersion()
{
string sql = "insert into version (num) values (1)";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public async Task ExportToGDAK(List<Core.Data.Geocache> gcList, string targetFolder, int maxLogCount, bool offlineImages, int maxInImgFolder)
{
string filename = Path.Combine(targetFolder, "sqlite.db3");
await Task.Run(() =>
{
try
{
DateTime nextUpdate = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock("ExportingGDAK", "CreatingFile", 1, 0, true))
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
logTypes.Add(2, "Found it");
logTypes.Add(3, "Didn't find it");
logTypes.Add(4, "Write note");
logTypes.Add(5, "Archive");
logTypes.Add(7, "Needs Archived");
logTypes.Add(9, "Will Attend");
logTypes.Add(10, "Attended");
logTypes.Add(11, "Webcam Photo Taken");
logTypes.Add(12, "Unarchive");
logTypes.Add(22, "Temporarily Disable Listing");
logTypes.Add(23, "Enable Listing");
logTypes.Add(24, "Publish Listing");
logTypes.Add(25, "Retract Listing");
logTypes.Add(45, "Needs Maintenance");
logTypes.Add(46, "Owner Maintenance");
logTypes.Add(47, "Update Coordinates");
logTypes.Add(68, "Post Reviewer Note");
logTypes.Add(74, "Announcement");
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
SqliteConnection dbconFiles = null;
string basePath = null;
int imgFolderIndex = 0;
int imgInFolderCount = 0;
if (Core.Settings.Default.GDAKExportOfflineImages)
{
basePath = System.IO.Path.Combine(targetFolder, "GrabbedImages");
if (!System.IO.Directory.Exists(basePath))
{
System.IO.Directory.CreateDirectory(basePath);
}
if (Core.Settings.Default.GDAKMaxImagesInFolder > 0)
{
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
while (System.IO.Directory.Exists(imgSubFolder))
{
imgFolderIndex++;
imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
}
}
dbconFiles = new SqliteConnection(string.Format("data source=file:{0}", System.IO.Path.Combine(basePath, "files.db3")));
dbconFiles.Open();
using (SqliteCommand cmd = new SqliteCommand("", dbconFiles))
{
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='fdone'";
object o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE fdone (dlink text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifdone on fdone (dlink)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='files'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE files (Link text collate nocase, Fname text collate nocase, Found integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ilink on files (Link)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifname on files (Fname)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='purge'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE purge (pfile text)";
cmd.ExecuteNonQuery();
}
}
}
using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", filename)))
{
dbcon.Open();
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
{
foreach (string s in SQLCREATEDBTABLES)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
foreach (string s in SQLCREATEDBINDEXES)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
foreach (string s in SQLCREATEDBTRIGGERS)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
cmd.CommandText = "PRAGMA user_version = 5003";
cmd.ExecuteNonQuery();
}
DbParameter par;
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("SavingGeocaches", gcList.Count, 0))
{
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
using (SqliteCommand cmd2 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd3 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd4 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd5 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd6 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd7 = new SqliteCommand("", dbcon))
{
cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints, IsLite) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints, @IsLite)";
cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)";
cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)";
cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)";
cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)";
cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)";
cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)";
par = cmd7.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cPrefix";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cName";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cType";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLat";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLon";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cByuser";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cDate";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cFlag";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@sB1";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cComment";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cUrl";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lType";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lBy";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lDate";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLat";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLon";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lEncoded";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lownerid";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lHasHtml";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lIsowner";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lTime";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lText";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aCode";
par.DbType = DbType.String;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aId";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aInc";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@LongDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@ShortDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Url";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Hints";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@UserNote";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedBy";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheId";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheType";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Container";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasCorrected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasUserNote";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Latitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LongHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Longitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@OwnerName";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedDate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ShortHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@State";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@UserFlag";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsOwner";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LatOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LonOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Status";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@GcNote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsPremium";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@FavPoints";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsLite";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
cmd2.Prepare();
cmd3.Prepare();
cmd4.Prepare();
cmd5.Prepare();
cmd6.Prepare();
cmd7.Prepare();
//using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
foreach (Core.Data.Geocache gc in gcList)
{
cmd2.Parameters["@Code"].Value = gc.Code;
cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? "";
cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? "";
cmd2.Parameters["@Url"].Value = gc.Url ?? "";
cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? "";
cmd2.Parameters["@UserNote"].Value = string.IsNullOrEmpty(gc.Notes) ? "" : System.Web.HttpUtility.HtmlDecode(gc.Notes);
cmd.Parameters["@Code"].Value = gc.Code;
cmd.Parameters["@Name"].Value = gc.Name ?? "";
cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0;
cmd.Parameters["@CacheId"].Value = Utils.Conversion.GetCacheIDFromCacheCode(gc.Code);
cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType);
cmd.Parameters["@Container"].Value = getContainer(gc.Container);
cmd.Parameters["@Country"].Value = gc.Country ?? "";
cmd.Parameters["@Difficulty"].Value = gc.Difficulty;
cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@HasCorrected"].Value = gc.ContainsCustomLatLon ? 1 : 0;
cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0;
cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.');
if (gc.ContainsCustomLatLon)
{
cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.');
}
else
{
cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.');
}
cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0;
cmd.Parameters["@OwnerName"].Value = gc.Owner ?? "";
cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd");
cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@State"].Value = gc.State ?? "";
cmd.Parameters["@Terrain"].Value = gc.Terrain;
cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0;
cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T";
cmd.Parameters["@GcNote"].Value = gc.PersonalNote ?? "";
cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@FavPoints"].Value = gc.Favorites;
cmd.Parameters["@IsLite"].Value = 0;
cmd.ExecuteNonQuery();
//if (index == 256) System.Diagnostics.Debugger.Break();
cmd2.ExecuteNonQuery();
List<int> attr = gc.AttributeIds;
foreach (int att in attr)
{
cmd3.Parameters["@aCode"].Value = gc.Code;
cmd3.Parameters["@aId"].Value = Math.Abs(att);
cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1;
cmd3.ExecuteNonQuery();
}
List<Core.Data.Log> logs = Utils.DataAccess.GetLogs(gc.Database, gc.Code).Take(Core.Settings.Default.GDAKMaxLogCount).ToList();
foreach (Core.Data.Log l in logs)
{
try
{
int logid = 0;
if (!int.TryParse(l.ID, out logid))
{
logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID);
}
cmd4.Parameters["@lLogId"].Value = logid;
cmd4.Parameters["@lText"].Value = l.Text ?? "";
cmd4.Parameters["@lParent"].Value = gc.Code;
cmd4.ExecuteNonQuery();
cmd5.Parameters["@lLogId"].Value = logid;
cmd5.Parameters["@lParent"].Value = gc.Code;
object o = logTypes[l.LogType.ID];
if (o == null)
{
cmd5.Parameters["@lType"].Value = 4;
}
else
{
cmd5.Parameters["@lType"].Value = (string)o;
}
cmd5.Parameters["@lBy"].Value = l.Finder ?? "";
cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss");
cmd5.Parameters["@lLat"].Value = DBNull.Value;
cmd5.Parameters["@lLon"].Value = DBNull.Value;
cmd5.Parameters["@lEncoded"].Value = l.Encoded;
try
{
cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId);
}
catch
{
}
cmd5.Parameters["@lHasHtml"].Value = false;
cmd5.Parameters["@lIsowner"].Value = (l.Finder == Core.ApplicationData.Instance.AccountInfos.GetAccountInfo(gc.Code.Substring(0,2)).AccountName);
cmd5.Parameters["@lTime"].Value = "";
cmd5.ExecuteNonQuery();
}
catch(Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(this, e);
}
}
List<Core.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(gc.Database, gc.Code);
foreach (Core.Data.Waypoint w in wps)
{
try
{
cmd6.Parameters["@cParent"].Value = gc.Code;
cmd6.Parameters["@cCode"].Value = w.Code;
cmd6.Parameters["@cComment"].Value = w.Comment;
cmd6.Parameters["@cUrl"].Value = w.Url;
cmd7.Parameters["@cParent"].Value = gc.Code;
cmd7.Parameters["@cCode"].Value = w.Code;
cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2);
cmd7.Parameters["@cName"].Value = w.Name ?? "";
cmd7.Parameters["@cType"].Value = getWPType(w.WPType);
cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.');
cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.');
cmd7.Parameters["@cByuser"].Value = false;
cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd");
cmd7.Parameters["@cFlag"].Value = false;
cmd7.Parameters["@sB1"].Value = false;
cmd7.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
}
catch (Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(this, e);
}
}
if (dbconFiles != null && (gc.LongDescriptionInHtml || gc.ShortDescriptionInHtml))
{
try
{
List<string> imgs = Utils.DataAccess.GetImagesOfGeocache(gc.Database, gc.Code);
Dictionary<string, string> oimgs = ImageGrabber.OfflineImagesManager.Instance.GetImages(gc);
foreach (string im in imgs)
{
if (oimgs.ContainsKey(im))
{
string p = oimgs[im];
using (SqliteCommand filescmd = new SqliteCommand("", dbconFiles))
{
filescmd.CommandText = string.Format("SELECT Fname FROM files WHERE Link='{0}'", im.Replace("'", "''"));
object o = filescmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
filescmd.CommandText = string.Format("insert into files (Link, Fname, Found) values ('{0}', '{1}', 1)", im.Replace("'", "''"), System.IO.Path.GetFileName(p).Replace("'", "''"));
filescmd.ExecuteNonQuery();
}
}
if (Core.Settings.Default.GDAKMaxImagesInFolder > 0)
{
imgInFolderCount++;
if (imgInFolderCount > Core.Settings.Default.GDAKMaxImagesInFolder)
{
imgFolderIndex++;
imgInFolderCount = 1;
}
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
if (imgInFolderCount == 1)
{
if (!System.IO.Directory.Exists(imgSubFolder))
{
System.IO.Directory.CreateDirectory(imgSubFolder);
}
}
string dst = System.IO.Path.Combine(imgSubFolder, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
else
{
string dst = System.IO.Path.Combine(basePath, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
}
}
}
catch (Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(this, e);
}
}
index++;
if (DateTime.Now>=nextUpdate)
{
if (!progress.Update("SavingGeocaches", gcList.Count, index))
{
break;
}
nextUpdate = DateTime.Now.AddSeconds(1);
}
}
//trans.Commit();
}
}
}
}
if (dbconFiles != null)
{
dbconFiles.Dispose();
dbconFiles = null;
}
}
}
catch (Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(this, e);
}
});
}
public static void ExportToFile(string filename, List<Core.Data.Geocache> gcList)
{
try
{
DateTime nextUpdate = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock("ExportingiGeoKnife", "CreatingFile", 1, 0, true))
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
logTypes.Add(2, "Found it");
logTypes.Add(3, "Didn't find it");
logTypes.Add(4, "Write note");
logTypes.Add(5, "Archive");
logTypes.Add(7, "Needs Archived");
logTypes.Add(9, "Will Attend");
logTypes.Add(10, "Attended");
logTypes.Add(11, "Webcam Photo Taken");
logTypes.Add(12, "Unarchive");
logTypes.Add(22, "Temporarily Disable Listing");
logTypes.Add(23, "Enable Listing");
logTypes.Add(24, "Publish Listing");
logTypes.Add(25, "Retract Listing");
logTypes.Add(45, "Needs Maintenance");
logTypes.Add(46, "Owner Maintenance");
logTypes.Add(47, "Update Coordinates");
logTypes.Add(68, "Post Reviewer Note");
logTypes.Add(74, "Announcement");
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
Utils.ResourceHelper.SaveToFile("/iGeoKnife/sqlite.db3", filename, true);
using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", filename)))
{
dbcon.Open();
DbParameter par;
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ExportingiGeoKnife", "SavingGeocaches", gcList.Count, 0, true))
{
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
using (SqliteCommand cmd2 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd3 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd4 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd5 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd6 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd7 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd8 = new SqliteCommand("", dbcon))
{
cmd.CommandText = "drop index CachesSmart";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints)";
cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)";
cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)";
cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)";
cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)";
cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)";
cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)";
cmd8.CommandText = "insert into Corrected (kCode, kBeforeLat, kBeforeLon, kAfterLat, kAfterLon) values (@kCode, @kBeforeLat, @kBeforeLon, @kAfterLat, @kAfterLon)";
par = cmd8.CreateParameter();
par.ParameterName = "@kCode";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cPrefix";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cName";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cType";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLat";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLon";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cByuser";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cDate";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cFlag";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@sB1";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cComment";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cUrl";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lType";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lBy";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lDate";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLat";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLon";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lEncoded";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lownerid";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lHasHtml";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lIsowner";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lTime";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lText";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aCode";
par.DbType = DbType.String;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aId";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aInc";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@LongDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@ShortDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Url";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Hints";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@UserNote";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedBy";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheId";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheType";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Container";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasCorrected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasUserNote";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Latitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LongHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Longitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@OwnerName";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedDate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ShortHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@State";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@UserFlag";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsOwner";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LatOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LonOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Status";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@GcNote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsPremium";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@FavPoints";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
cmd2.Prepare();
cmd3.Prepare();
cmd4.Prepare();
cmd5.Prepare();
cmd6.Prepare();
cmd7.Prepare();
cmd8.Prepare();
//using (DbTransaction trans = dbcon.BeginTransaction())
//{
int index = 0;
foreach (Core.Data.Geocache gc in gcList)
{
string notes = "";
if (!string.IsNullOrEmpty(gc.Notes))
{
notes = System.Web.HttpUtility.HtmlDecode(gc.Notes);
}
if (!string.IsNullOrEmpty(gc.PersonalNote))
{
notes = string.Concat(notes, gc.PersonalNote);
}
cmd2.Parameters["@Code"].Value = gc.Code;
cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? "";
cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? "";
cmd2.Parameters["@Url"].Value = gc.Url ?? "";
cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? "";
cmd2.Parameters["@UserNote"].Value = notes;
cmd.Parameters["@Code"].Value = gc.Code;
cmd.Parameters["@Name"].Value = gc.Name ?? "";
cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0;
cmd.Parameters["@CacheId"].Value = Utils.Conversion.GetCacheIDFromCacheCode(gc.Code).ToString();
cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType);
cmd.Parameters["@Container"].Value = getContainer(gc.Container);
cmd.Parameters["@Country"].Value = gc.Country ?? "";
cmd.Parameters["@Difficulty"].Value = gc.Difficulty;
cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@HasCorrected"].Value = gc.ContainsCustomLatLon ? 1 : 0;
cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0;
cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.');
if (gc.ContainsCustomLatLon)
{
cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.');
}
else
{
cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.');
}
cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0;
cmd.Parameters["@OwnerName"].Value = gc.Owner ?? "";
cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd");
cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@State"].Value = gc.State ?? "";
cmd.Parameters["@Terrain"].Value = gc.Terrain;
cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0;
cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T";
cmd.Parameters["@GcNote"].Value = notes;
cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@FavPoints"].Value = gc.Favorites;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
if (gc.ContainsCustomLatLon)
{
cmd8.Parameters["@kCode"].Value = gc.Code;
cmd8.Parameters["@kBeforeLat"].Value = gc.Lat.ToString().Replace(',', '.');
cmd8.Parameters["@kBeforeLon"].Value = gc.Lon.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLat"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLon"].Value = gc.CustomLon.ToString().Replace(',', '.');
cmd8.ExecuteNonQuery();
}
List<int> attr = gc.AttributeIds;
foreach (int att in attr)
{
cmd3.Parameters["@aCode"].Value = gc.Code;
cmd3.Parameters["@aId"].Value = Math.Abs(att);
cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1;
cmd3.ExecuteNonQuery();
}
List<Core.Data.Log> logs = Utils.DataAccess.GetLogs(gc.Database, gc.Code).Take(Core.Settings.Default.IGeoKnifeMaxLogs).ToList();
foreach (Core.Data.Log l in logs)
{
try
{
int logid = 0;
if (!int.TryParse(l.ID, out logid))
{
logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID);
}
cmd4.Parameters["@lLogId"].Value = logid;
cmd4.Parameters["@lText"].Value = l.Text ?? "";
cmd4.Parameters["@lParent"].Value = gc.Code;
cmd4.ExecuteNonQuery();
cmd5.Parameters["@lLogId"].Value = logid;
cmd5.Parameters["@lParent"].Value = gc.Code;
object o = logTypes[l.LogType.ID];
if (o == null)
{
cmd5.Parameters["@lType"].Value = 4;
}
else
{
cmd5.Parameters["@lType"].Value = (string)o;
}
cmd5.Parameters["@lBy"].Value = l.Finder ?? "";
cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss");
cmd5.Parameters["@lLat"].Value = DBNull.Value;
cmd5.Parameters["@lLon"].Value = DBNull.Value;
cmd5.Parameters["@lEncoded"].Value = l.Encoded;
try
{
cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId);
}
catch
{
}
cmd5.Parameters["@lHasHtml"].Value = false;
cmd5.Parameters["@lIsowner"].Value = gc.IsOwn;
cmd5.Parameters["@lTime"].Value = "";
cmd5.ExecuteNonQuery();
}
catch(Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(new Export(), e);
}
}
List<Core.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(gc.Database, gc.Code);
foreach (Core.Data.Waypoint w in wps)
{
try
{
cmd6.Parameters["@cParent"].Value = gc.Code;
cmd6.Parameters["@cCode"].Value = w.Code;
cmd6.Parameters["@cComment"].Value = w.Comment;
cmd6.Parameters["@cUrl"].Value = w.Url;
cmd7.Parameters["@cParent"].Value = gc.Code;
cmd7.Parameters["@cCode"].Value = w.Code;
cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2);
cmd7.Parameters["@cName"].Value = w.Name ?? "";
cmd7.Parameters["@cType"].Value = getWPType(w.WPType);
cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.');
cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.');
cmd7.Parameters["@cByuser"].Value = false;
cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd");
cmd7.Parameters["@cFlag"].Value = false;
cmd7.Parameters["@sB1"].Value = false;
cmd7.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
}
catch(Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(new Export(), e);
}
}
index++;
if (DateTime.Now>=nextUpdate)
{
if (!progress.Update("SavingGeocaches", gcList.Count, index))
{
break;
}
nextUpdate = DateTime.Now.AddSeconds(1);
}
}
//trans.Commit();
}
}
}
}
}
catch(Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(new Export(), e);
}
}
public void save(SqliteConnection conn)
{
string command;
if (id == -1)
command = "insert into mediaitem (name, type, checked_to_patron_id, " +
" checkout_date, due_date) values (@name, @type, @patron_id, @co_date, @due_date);";
else
command = "update mediaitem set name=@name, type=@type, checked_to_patron_id=@patron_id, " +
" checkout_date=@co_date, due_date=@due_date where id=@id";
SqliteCommand cmd = new SqliteCommand(command, conn);
cmd.Parameters.Add("@id", id);
cmd.Parameters.Add("@name", name);
cmd.Parameters.Add("@type", type);
if (checked_to_patron_id > -1)
cmd.Parameters.Add("@patron_id", checked_to_patron_id);
else
cmd.Parameters.Add("@patron_id", null);
if (checkout_date != null)
cmd.Parameters.Add("@co_date", checkout_date.Value.ToString());
else
cmd.Parameters.Add("@co_date", null);
if (due_date != null)
cmd.Parameters.Add("@due_date", due_date);
else
cmd.Parameters.Add("@due_date", null);
cmd.ExecuteNonQuery();
//id = cmd.LastInsertRowID();
}
private void SetupVersion()
{
string sql = "update version set num = 3";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public bool Update(IList<FileMetaDataItem> items, SqliteConnection con)
{
string cmdText = string.Format("UPDATE {0} SET {1} = @hash, {2} = @lmf WHERE {3} = @rel AND {4} = @sourceId",
Configuration.TBL_METADATA, Configuration.COL_HASH_CODE,
Configuration.COL_LAST_MODIFIED_TIME, Configuration.COL_RELATIVE_PATH, Configuration.COL_SOURCE_ID);
try
{
foreach (FileMetaDataItem item in items)
{
using (var cmd = new SqliteCommand(cmdText, con))
{
cmd.Parameters.Add(new SqliteParameter("@hash", DbType.String) { Value = item.HashCode });
cmd.Parameters.Add(new SqliteParameter("@lmf", DbType.DateTime) { Value = item.LastModifiedTime });
cmd.Parameters.Add(new SqliteParameter("@rel", DbType.String) { Value = item.RelativePath });
cmd.Parameters.Add(new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId });
cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
return false;
}
return true;
}
void SetBlockState(byte[] blockId, bool[] status) {
UInt64 statusValue = 0;
UInt64 statusId = 0;
// Build 64bit value.
for (byte valuePos = 0; valuePos < 64; valuePos++) {
statusValue += (status[valuePos] ? 1ul : 0ul) << valuePos;
}
// Try and get a valid status id.
try {
parent.DatabaseMutex.WaitOne();
var selectStatusCmd = new SqliteCommand(String.Format(@STATUSSELECT,
ECCCracker.TABLESTATUSNAME,
statusValue), parent.Database);
var selectStatusReader = selectStatusCmd.ExecuteReader();
if (selectStatusReader.HasRows) {
selectStatusReader.NextResult();
statusId = (UInt64)selectStatusReader.GetInt64(0);
} else {
var insertStatusCmd = new SqliteCommand(String.Format(@STATUSINSERT,
ECCCracker.TABLESTATUSNAME,
statusValue), parent.Database);
insertStatusCmd.ExecuteNonQuery();
var lastInsertIdCmd = new SqliteCommand("SELECT last_insert_rowid();", parent.Database);
var lastInsertIdReader = lastInsertIdCmd.ExecuteReader();
lastInsertIdReader.NextResult();
statusId = (UInt64)lastInsertIdReader.GetInt64(0);
lastInsertIdReader.Close();
}
selectStatusReader.Close();
} catch (Exception e) {
Console.WriteLine("Thread {0}: Encountered Exception while marking block as complete:", threadId);
Console.WriteLine(e);
Console.ReadKey();
} finally {
parent.DatabaseMutex.ReleaseMutex();
}
// Try and mark block as complete.
try {
parent.DatabaseMutex.WaitOne();
var insertBlockCmd = new SqliteCommand(String.Format(@BLOCKINSERT,
ECCCracker.TABLEBLOCKSNAME,
BitConverter.ToUInt64(blockId, 0),
BitConverter.ToUInt64(blockId, 8),
BitConverter.ToUInt64(blockId, 16),
BitConverter.ToUInt64(blockId, 24),
BitConverter.ToUInt64(blockId, 32),
BitConverter.ToUInt64(blockId, 40),
statusId), parent.Database);
insertBlockCmd.ExecuteNonQuery();
var lastInsertIdCmd = new SqliteCommand("SELECT last_insert_rowid();", parent.Database);
var lastInsertIdReader = lastInsertIdCmd.ExecuteReader();
lastInsertIdReader.NextResult();
UInt64 blockRowId = (UInt64)lastInsertIdReader.GetInt64(0);
lastInsertIdReader.Close();
if (blockRowId == 0)
Console.WriteLine("Thread {0}: Failed to mark block as complete.", threadId);
} catch (Exception e) {
Console.WriteLine("Thread {0}: Encountered Exception while marking block as complete:", threadId);
Console.WriteLine(e);
Console.ReadKey();
} finally {
parent.DatabaseMutex.ReleaseMutex();
}
}
/// <summary>
/// Add a list of file metadata into database
/// Combinational transaction feature is supported
/// </summary>
/// <param name="mData"></param>
/// <param name="con"></param>
/// <returns></returns>
public bool Add(IList<FileMetaDataItem> mData, SqliteConnection con)
{
const string cmdText = "INSERT INTO " + Configuration.TBL_METADATA +
"( " + Configuration.COL_SOURCE_ID + "," + Configuration.COL_RELATIVE_PATH + "," +
Configuration.COL_HASH_CODE + "," +
Configuration.COL_LAST_MODIFIED_TIME + "," +
Configuration.COL_NTFS_ID1 + "," +
Configuration.COL_NTFS_ID2 + ")" +
"VALUES (@source_id , @relative_path, @hash_code, @last_modified_time, @ntfs_id1, @ntfs_id2) ";
try
{
foreach (FileMetaDataItem item in mData)
{
using (var cmd = new SqliteCommand(cmdText, con))
{
cmd.Parameters.Add(new SqliteParameter("@source_id", DbType.String) { Value = item.SourceId });
cmd.Parameters.Add(new SqliteParameter("@relative_path", DbType.String) { Value = item.RelativePath });
cmd.Parameters.Add(new SqliteParameter("@hash_code", DbType.String) { Value = item.HashCode });
cmd.Parameters.Add(new SqliteParameter("@last_modified_time", DbType.DateTime) { Value = item.LastModifiedTime });
cmd.Parameters.Add(new SqliteParameter("@ntfs_id1", DbType.Int32) { Value = item.NTFS_ID1 });
cmd.Parameters.Add(new SqliteParameter("@ntfs_id2", DbType.Int32) { Value = item.NTFS_ID2 });
cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
return false;
}
return true;
}
// TODO: As all exceptions apper to be caught here the ExecuteNonQuery() calls probably
// do not need to be in try/catch 's
protected int ExecuteNonQuery(SqliteCommand cmd)
{
int retries = 0;
restart:
try
{
lock (GetLock())
{
PrepReader(ref cmd);
UnescapeSql(cmd);
var value = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return value;
}
}
catch (SqliteBusyException ex)
{
if (retries++ > 5)
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
else
goto restart;
return 0;
}
catch (SqliteException ex)
{
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
}
catch (Exception ex)
{
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
}
return 0;
}
private bool InitDatabase(SqliteConnection dbcon)
{
bool result = false;
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='project_info'";
object o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'project_info' (item_name text, item_value text)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='geocache_cfields'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'geocache_cfields' (field_name text)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='geocache'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'geocache' (id text, code text, name text, datafromdate text, lat real, lon real, disttocent integer, angletocent integer, available integer, archived integer, country text, state text, cachetype integer, placedby text, owner text, ownerid text, container integer, terrain real, difficulty real, shortdescr text, shortdescrhtml integer, longdescr text, longdescrhtml integer, encodedhints text, url text, memberonly integer, customcoords integer, attrids text, favorites integer, selected integer, municipality text, city text, customlat real, customlon real, notes text, publiceddate text, personalnote text, flagged integer, found integer, locked integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_geocache on geocache (code)";
cmd.ExecuteNonQuery();
}
else if (!ColumnExists(dbcon,"geocache","locked"))
{
cmd.CommandText = "alter table geocache add locked integer";
cmd.ExecuteNonQuery();
cmd.CommandText = "update geocache set locked=0";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='log'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'log' (id text, gccode text, tbcode text, date text, finder text, finderid text, logtext text, encoded integer, datafromdate text, logtype integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_log on log (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='logimage'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'logimage' (id text, logid text, url text, name text, datafromdate text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_logimage on logimage (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='waypoint'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'waypoint' (id text, code text, geocachecode text, name text, datafromdate text, comment text, description text, url text, urlname text, wptype integer, lat real, lon real, time text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_waypoint on waypoint (code)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='userwaypoint'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'userwaypoint' (id integer, geocachecode text, description text, lat real, lon real, time text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_userwaypoint on userwaypoint (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='counter'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'counter' (geocache integer, log integer, waypoint integer, logimage integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into counter (geocache, log, waypoint, logimage) values (0, 0, 0, 0)";
cmd.ExecuteNonQuery();
}
}
result = true;
}
catch
{
}
return result;
}
/// <summary>
/// Gets the next block identifier.
/// </summary>
/// <returns>The next block identifier.</returns>
BigInteger GetNextBlockId() {
BigInteger newBlockId = -1;
Byte[] newBlockData = new Byte[48 + 1];
parent.DatabaseMutex.WaitOne();
bool isValid0 = false, isValid1 = false;
while ((isValid0 == false || isValid1 == false) && BigInteger.Compare(newBlockId, parent.BlockMaximum) <= 0) {
isValid0 = isValid1 = false;
// Generate random number.
randomizer.NextBytes(newBlockData);
newBlockData[47] = (byte)(newBlockData[47] & 0x03); // Using 6 bits less than maximum.
newBlockData[48] = 0x00;
newBlockId = new BigInteger(newBlockData);
// Check if the block id is already being used.
try { //Trycrash-Construct
var checkThreadUsageCmd = new SqliteCommand(String.Format(@THREADSELECT,
@ECCCracker.TABLETHREADSNAME,
BitConverter.ToUInt64(newBlockData, 0),
BitConverter.ToUInt64(newBlockData, 8),
BitConverter.ToUInt64(newBlockData, 16),
BitConverter.ToUInt64(newBlockData, 24),
BitConverter.ToUInt64(newBlockData, 32),
BitConverter.ToUInt64(newBlockData, 40)), parent.Database);
var checkThreadUsageReader = checkThreadUsageCmd.ExecuteReader();
if (checkThreadUsageReader.HasRows) {// If there are rows containing this item, continue with generating the next number
checkThreadUsageReader.Close();
continue;
}
checkThreadUsageReader.Close();
isValid0 = true;
} catch (Exception e) {
Console.WriteLine("Thread {0}: Encountered Exception while trying to get next block Id:", threadId);
Console.WriteLine(e);
Console.ReadKey();
}
// Check if the block id has already been used.
try {
var checkBlockDoneCmd = new SqliteCommand(String.Format(@BLOCKSELECT,
@ECCCracker.TABLEBLOCKSNAME,
BitConverter.ToUInt64(newBlockData, 0),
BitConverter.ToUInt64(newBlockData, 8),
BitConverter.ToUInt64(newBlockData, 16),
BitConverter.ToUInt64(newBlockData, 24),
BitConverter.ToUInt64(newBlockData, 32),
BitConverter.ToUInt64(newBlockData, 40)), parent.Database);
var checkThreadUsageReader = checkBlockDoneCmd.ExecuteReader();
if (checkThreadUsageReader.HasRows) {// If there are rows containing this item, continue with generating the next number
checkThreadUsageReader.Close();
continue;
}
checkThreadUsageReader.Close();
isValid1 = true;
} catch (Exception e) {
Console.WriteLine("Thread {0}: Encountered Exception while trying to get next block Id:", threadId);
Console.WriteLine(e);
Console.ReadKey();
}
}
// Now set the block id as being used.
try {
var setBlockUsedCmd = new SqliteCommand(String.Format(@THREADINSERT,
@ECCCracker.TABLETHREADSNAME,
threadId,
BitConverter.ToUInt64(newBlockData, 0),
BitConverter.ToUInt64(newBlockData, 8),
BitConverter.ToUInt64(newBlockData, 16),
BitConverter.ToUInt64(newBlockData, 24),
BitConverter.ToUInt64(newBlockData, 32),
BitConverter.ToUInt64(newBlockData, 40)), parent.Database);
setBlockUsedCmd.ExecuteNonQuery();
} catch (Exception e) {
Console.WriteLine("Thread {0}: Encountered Exception while trying to get next block Id:", threadId);
Console.WriteLine(e);
Console.ReadKey();
}
parent.DatabaseMutex.ReleaseMutex();
return newBlockId;
}
public void EnsureTablesAndIndexes(SimpleDBLayerTable[] tablesIn, SimpleDBLayerIndex[] indexesIn)
{
// TODO: Check version.
#if USE_SQLITE
try {
foreach (SimpleDBLayerTable table in tablesIn)
{
StringBuilder createQueryString = new StringBuilder();
createQueryString.Append("CREATE TABLE IF NOT EXISTS " + table.TableName + " (");
List <string> uniqueColumns = new List <string>();
Dictionary <string, SimpleDBLayerTableColumn> foreignKeys = new Dictionary <string, SimpleDBLayerTableColumn>();
foreach (SimpleDBLayerColumn column in table.TableColumns)
{
createQueryString.Append(String.Format(
"{0} {1} {2} {3}, ",
column.ColumnName,
SimpleDBLayerColumn.GetTypeString(column.ColumnType),
column.ColumnPrimaryKey ? "PRIMARY KEY" : "",
column.ColumnNotNull ? "NOT NULL" : "",
column.ColumnAutoIncrement ? "AUTOINCREMENT" : ""
));
// Save additional column information for the end of the statement.
if (column.ColumnUnique)
{
uniqueColumns.Add(column.ColumnName);
}
if (null != column.ColumnForeignKey)
{
foreignKeys.Add(column.ColumnName, column.ColumnForeignKey);
}
}
// Apply unique columns and foreign keys using informaton saved above.
foreach (string column in uniqueColumns)
{
createQueryString.Append(String.Format("UNIQUE({0}), ", column));
}
foreach (string foreignKey in foreignKeys.Keys)
{
createQueryString.Append(String.Format(
"FOREIGN KEY({0}) REFERENCES {1}({2}), ",
foreignKey,
foreignKeys[foreignKey].TableName,
foreignKeys[foreignKey].ColumnName
));
}
// Strip off last comma.
createQueryString.Remove(createQueryString.Length - 2, 2);
createQueryString.Append(")");
#if DEBUG
Debug.WriteLine(createQueryString.ToString());
#endif // DEBUG
using (SQLiteCommand createCmd = new SQLiteCommand(createQueryString.ToString(), this.database)) {
createCmd.ExecuteNonQuery();
}
}
foreach (SimpleDBLayerIndex index in indexesIn)
{
using (SQLiteCommand createCmd = new SQLiteCommand(
String.Format(
"CREATE INDEX IF NOT EXISTS {0} ON {1}({2})",
index.IndexName,
index.TableName,
index.ColumnName
),
this.database
)) {
createCmd.ExecuteNonQuery();
}
}
#if USE_SQLITE_MANAGED
} catch (SQLiteBusyException ex) {
throw new SimpleDBLayerBusyException(ex.Message);
#endif // USE_SQLITE_MANAGED
} catch (SQLiteException ex) {
throw new SimpleDBLayerException(ex.Message);
}
#endif // USE_SQLITE
}
public bool updateRecord()
{
Type ja = this.GetType();
string tmp = "UPDATE " + ja.Name + " SET ";
System.Reflection.FieldInfo[] fieldInfo = ja.GetFields();
bool was = false;
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
if (info.IsDefined(typeof(PrimaryKey), true)) continue;
if (was) tmp += ",";
tmp += info.Name+"=@"+info.Name;
was = true;
}
tmp+=" WHERE ";
string where = "";
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
if (info.IsDefined(typeof(PrimaryKey), true))
{
if (where.Length != 0) where += " AND ";
where += "("+info.Name+" = @"+info.Name+")";
}
}
tmp += where;
SqliteCommand sql = new SqliteCommand(tmp, SqliteManager.connection);
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
object temp = info.GetValue(this);
if (temp is long)
{
sql.Parameters.Add(new SqliteParameter("@"+info.Name, temp));
}
else
if (temp is string)
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp));
}
else
if (temp is double)
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp));
}
else
if (temp is bool)
{
if ((Boolean)temp)
sql.Parameters.Add(new SqliteParameter("@" + info.Name, "1"));
else
sql.Parameters.Add(new SqliteParameter("@" + info.Name, "0"));
}
else
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp.ToString()));
}
}
try
{
return sql.ExecuteNonQuery() != 0;
}
catch (Exception e)
{
MessageBox.Show("Error durring row update.\n" + sql.ToString() + "\n"+tmp+"\n" + e.ToString());
return false;
}
}
public bool insertRecord()
{
Type ja = this.GetType();
string tmp = "INSERT INTO " + ja.Name + "(";
System.Reflection.FieldInfo[] fieldInfo = ja.GetFields();
bool was = false;
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
if (was) tmp += ",";
tmp += info.Name;
was = true;
}
tmp += ") VALUES (";
was = false;
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
if (was) tmp += ",";
tmp += "@" + info.Name;
was = true;
}
tmp += ");";
SqliteCommand sql = new SqliteCommand(tmp, SqliteManager.connection);
foreach (System.Reflection.FieldInfo info in fieldInfo)
{
object temp = info.GetValue(this);
if (temp is long)
{
sql.Parameters.Add(new SqliteParameter("@"+info.Name, temp));
}
else
if (temp is string)
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp));
}
else
if (temp is double)
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp));
}
else
if (temp is bool)
{
if ((Boolean)temp)
sql.Parameters.Add(new SqliteParameter("@" + info.Name, "1"));
else
sql.Parameters.Add(new SqliteParameter("@" + info.Name, "0"));
}
else
{
sql.Parameters.Add(new SqliteParameter("@" + info.Name, temp.ToString()));
}
}
try
{
return sql.ExecuteNonQuery() != 0;
}
catch (Exception e)
{
MessageBox.Show("Error durring row insert.\n" + sql.ToString() + "\n" + e.ToString());
return false;
}
}
public bool Delete(IList<FolderMetadataItem> items, SqliteConnection con)
{
const string cmdText = "DELETE FROM " + Configuration.TLB_FOLDERMETADATA +
" WHERE " + Configuration.COL_SOURCE_ID + " = @sourceId AND " +
Configuration.COL_RELATIVE_PATH + " = @path";
try
{
foreach (FolderMetadataItem item in items)
{
using (var cmd = new SqliteCommand(cmdText, con))
{
cmd.Parameters.Add(new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId });
cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = item.RelativePath });
cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{ return false; }
return true;
}
private void CreateVersionTable()
{
string sql = "CREATE TABLE version (num INT NOT NULL)";
var command = new SqliteCommand(sql, _connection);
command.ExecuteNonQuery();
}
public void Open()
{
if (conn_str == null)
{
throw new InvalidOperationException("No database specified");
}
if (state != ConnectionState.Closed)
{
return;
}
IntPtr errmsg = IntPtr.Zero;
/*
* if (Version == 2){
* try {
* sqlite_handle = Sqlite.sqlite_open(db_file, db_mode, out errmsg);
* if (errmsg != IntPtr.Zero) {
* string msg = Marshal.PtrToStringAnsi (errmsg);
* Sqlite.sqliteFree (errmsg);
* throw new ApplicationException (msg);
* }
* } catch (DllNotFoundException) {
* db_version = 3;
* } catch (EntryPointNotFoundException) {
* db_version = 3;
* }
*
* if (busy_timeout != 0)
* Sqlite.sqlite_busy_timeout (sqlite_handle, busy_timeout);
* }
*/
if (Version == 3)
{
sqlite_handle = (IntPtr)1;
int flags = Sqlite3.SQLITE_OPEN_NOMUTEX | Sqlite3.SQLITE_OPEN_READWRITE | Sqlite3.SQLITE_OPEN_CREATE;
int err = Sqlite3.sqlite3_open_v2(db_file, ref sqlite_handle2, flags, null);
//int err = Sqlite.sqlite3_open16(db_file, out sqlite_handle);
if (err == (int)SqliteError.ERROR)
{
throw new ApplicationException(Sqlite3.sqlite3_errmsg(sqlite_handle2));
}
//throw new ApplicationException (Marshal.PtrToStringUni( Sqlite.sqlite3_errmsg16 (sqlite_handle)));
if (busy_timeout != 0)
{
Sqlite3.sqlite3_busy_timeout(sqlite_handle2, busy_timeout);
}
//Sqlite.sqlite3_busy_timeout (sqlite_handle, busy_timeout);
if (!String.IsNullOrEmpty(db_password))
{
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "pragma hexkey='" + db_password + "'";
cmd.ExecuteNonQuery();
}
}
else
{
}
state = ConnectionState.Open;
}
public bool Save(SqliteConnection dbcon, bool forceFullData)
{
bool result = true;
using (Utils.ProgressBlock fixpr = new ProgressBlock(this, STR_SAVING, STR_SAVINGDATA, 1, 0))
{
if (dbcon != null)
{
string[] custAttr = Core.Geocaches.CustomAttributes;
List<string> activeAttr = new List<string>();
using (SqliteCommand cmd = new SqliteCommand("select field_name from geocache_cfields", dbcon))
using (SqliteDataReader dr = cmd.ExecuteReader())
while (dr.Read())
{
activeAttr.Add(string.Format("{0}", dr["field_name"]));
}
foreach (string s in activeAttr)
{
if (!custAttr.Contains(s) && ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
{
//drop column not supported!
}
}
//geocache_cfields
using (SqliteCommand cmd = new SqliteCommand("delete from geocache_cfields", dbcon))
cmd.ExecuteNonQuery();
foreach (string s in custAttr)
{
if (!activeAttr.Contains(s))
{
using (SqliteCommand cmd = new SqliteCommand(string.Format("insert into geocache_cfields (field_name) values ('{0}')", s), dbcon))
cmd.ExecuteNonQuery();
}
if (!ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
{
using (SqliteCommand cmd = new SqliteCommand(string.Format("alter table geocache add _{0} text)", s), dbcon))
cmd.ExecuteNonQuery();
}
}
//delete geoacches that are not in the list anymore.
string[] c = (from string a in _geocachesInDB.Keys select a).ToArray();
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetGeocache(Core.Geocaches, c[i]) == null)
{
cmd.CommandText = string.Format("delete from geocache where code='{0}'", c[i]);
cmd.ExecuteNonQuery();
_geocachesInDB.Remove(c[i]);
}
}
//reset selection
using (SqliteCommand cmd = new SqliteCommand("update geocache set selected=0", dbcon))
cmd.ExecuteNonQuery();
//now get all the selected and data changed geocaches
List<Framework.Data.Geocache> gclist = (from Framework.Data.Geocache wp in Core.Geocaches
where wp.Selected || !wp.Saved
select wp).ToList();
if (gclist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, 0))
{
string updateSqlFull = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, shortdescr=@shortdescr, shortdescrhtml=@shortdescrhtml, longdescr=@longdescr, longdescrhtml=@longdescrhtml, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
string insertSqlFull = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, shortdescr, shortdescrhtml, longdescr, longdescrhtml, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @shortdescr, @shortdescrhtml, @longdescr, @longdescrhtml, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";
string updateSqlShort = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
string insertSqlShort = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandType = CommandType.Text;
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@disttocent";
par.DbType = DbType.Int64;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@angletocent";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@available";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@state";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@cachetype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@placedby";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@owner";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ownerid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@container";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@shortdescr";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@shortdescrhtml";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@longdescr";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@longdescrhtml";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@encodedhints";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@memberonly";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customcoords";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@attrids";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@favorites";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@selected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@municipality";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@city";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customlat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customlon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@notes";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@publiceddate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@personalnote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@flagged";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@locked";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
foreach (string s in custAttr)
{
par = cmd.CreateParameter();
par.ParameterName = string.Format("@_{0}", s);
par.DbType = DbType.String;
cmd.Parameters.Add(par);
}
cmd.Prepare();
StringBuilder sb = new StringBuilder();
using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Geocache gc in gclist)
{
index++;
procStep++;
if (!gc.Saved)
{
cmd.Parameters["@id"].Value = gc.ID ?? "";
cmd.Parameters["@code"].Value = gc.Code;
cmd.Parameters["@name"].Value = gc.Name ?? "";
cmd.Parameters["@datafromdate"].Value = gc.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@lat"].Value = gc.Lat;
cmd.Parameters["@lon"].Value = gc.Lon;
cmd.Parameters["@disttocent"].Value = gc.DistanceToCenter;
cmd.Parameters["@angletocent"].Value = gc.AngleToCenter;
cmd.Parameters["@available"].Value = gc.Available ? 1 : 0;
cmd.Parameters["@archived"].Value = gc.Archived ? 1 : 0;
cmd.Parameters["@country"].Value = gc.Country ?? "";
cmd.Parameters["@state"].Value = gc.State ?? "";
cmd.Parameters["@cachetype"].Value = gc.GeocacheType == null ? -1 : gc.GeocacheType.ID;
cmd.Parameters["@placedby"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@owner"].Value = gc.Owner ?? "";
cmd.Parameters["@ownerid"].Value = gc.OwnerId ?? "";
cmd.Parameters["@container"].Value = gc.Container == null ? -1 : gc.Container.ID;
cmd.Parameters["@terrain"].Value = gc.Terrain;
cmd.Parameters["@difficulty"].Value = gc.Difficulty;
if (forceFullData || gc.FullDataLoaded)
{
cmd.Parameters["@shortdescr"].Value = gc.ShortDescription ?? "";
cmd.Parameters["@shortdescrhtml"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@longdescr"].Value = gc.LongDescription ?? "";
cmd.Parameters["@longdescrhtml"].Value = gc.LongDescriptionInHtml ? 1 : 0;
}
cmd.Parameters["@encodedhints"].Value = gc.EncodedHints ?? "";
cmd.Parameters["@url"].Value = gc.Url ?? "";
cmd.Parameters["@memberonly"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@customcoords"].Value = gc.CustomCoords ? 1 : 0;
sb.Length = 0;
foreach (int attrId in gc.AttributeIds)
{
sb.AppendFormat("|{0}|", attrId);
}
cmd.Parameters["@attrids"].Value = sb.ToString();
cmd.Parameters["@favorites"].Value = gc.Favorites;
cmd.Parameters["@selected"].Value = gc.Selected ? 1 : 0;
cmd.Parameters["@municipality"].Value = gc.Municipality ?? "";
cmd.Parameters["@city"].Value = gc.City ?? "";
if (gc.CustomLat == null)
{
cmd.Parameters["@customlat"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@customlat"].Value = gc.CustomLat;
}
if (gc.CustomLon == null)
{
cmd.Parameters["@customlon"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@customlon"].Value = gc.CustomLon;
}
cmd.Parameters["@notes"].Value = gc.Notes ?? "";
cmd.Parameters["@publiceddate"].Value = gc.PublishedTime.ToUniversalTime().ToString("u");
cmd.Parameters["@personalnote"].Value = gc.PersonaleNote ?? "";
cmd.Parameters["@flagged"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@locked"].Value = gc.Locked ? 1 : 0;
foreach (string s in custAttr)
{
object o = gc.GetCustomAttribute(s);
if (o == null)
{
cmd.Parameters[string.Format("@_{0}", s)].Value = DBNull.Value;
}
else
{
cmd.Parameters[string.Format("@_{0}", s)].Value = o.ToString();
}
}
bool indb = _geocachesInDB[gc.Code] != null;
if (forceFullData || gc.FullDataLoaded)
{
cmd.CommandText = updateSqlFull;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlFull;
cmd.ExecuteNonQuery();
if (!indb)
{
_geocachesInDB[gc.Code] = gc.Code;
}
}
}
else
{
cmd.CommandText = updateSqlShort;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlShort;
cmd.ExecuteNonQuery();
if (!indb)
{
_geocachesInDB[gc.Code] = gc.Code;
}
}
}
gc.Saved = true;
}
else if (gc.Selected)
{
cmd.CommandText = string.Format("update geocache set selected=1 where code='{0}'", gc.Code);
cmd.ExecuteNonQuery();
}
if (procStep >= 200)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
//delete logs that are not in the list anymore.
c = (from string a in _logsInDB.Keys select a).ToArray();
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetLog(Core.Logs, c[i]) == null)
{
cmd.CommandText = string.Format("delete from log where id='{0}'", c[i]);
cmd.ExecuteNonQuery();
_logsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.Log> lglist = (from Framework.Data.Log wp in Core.Logs
where !wp.Saved
select wp).ToList();
if (lglist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGS, lglist.Count, 0))
{
// tbcode, finderid, logtext, encoded
string updateSqlFull = "update log set gccode=@gccode, tbcode=@tbcode, date=@date, finder=@finder, finderid=@finderid, logtext=@logtext, encoded=@encoded, datafromdate=@datafromdate, logtype=@logtype where id=@id";
string insertSqlFull = "insert into log (id, gccode, tbcode, date, finder, finderid, logtext, encoded, datafromdate, logtype) values (@id, @gccode, @tbcode, @date, @finder, @finderid, @logtext, @encoded, @datafromdate, @logtype)";
string updateSqlShort = "update log set gccode=@gccode, date=@date, finder=@finder, datafromdate=@datafromdate, logtype=@logtype where id=@id";
string insertSqlShort = "insert into log (id, gccode, date, finder, datafromdate, logtype) values (@id, @gccode, @date, @finder, @datafromdate, @logtype)";
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@gccode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@tbcode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@date";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@finder";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@finderid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logtext";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@encoded";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logtype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Log lg in lglist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = lg.ID;
cmd.Parameters["@gccode"].Value = lg.GeocacheCode;
cmd.Parameters["@date"].Value = lg.Date.ToUniversalTime().ToString("u");
cmd.Parameters["@finder"].Value = lg.Finder ?? "";
cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@logtype"].Value = lg.LogType == null ? -1 : lg.LogType.ID;
if (forceFullData || lg.FullDataLoaded)
{
cmd.Parameters["@tbcode"].Value = lg.TBCode ?? "";
cmd.Parameters["@finderid"].Value = lg.FinderId ?? "";
cmd.Parameters["@logtext"].Value = lg.Text ?? "";
cmd.Parameters["@encoded"].Value = lg.Encoded ? 1 : 0;
}
bool indb = _logsInDB[lg.ID] != null;
if (forceFullData || lg.FullDataLoaded)
{
cmd.CommandText = updateSqlFull;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlFull;
cmd.ExecuteNonQuery();
if (!indb)
{
_logsInDB[lg.ID] = lg.ID;
}
}
}
else
{
cmd.CommandText = updateSqlShort;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlShort;
cmd.ExecuteNonQuery();
if (!indb)
{
_logsInDB[lg.ID] = lg.ID;
}
}
}
lg.Saved = true;
if (procStep >= 2000)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGS, lglist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
//delete log images that are not in the list anymore.
c = (from string a in _logimgsInDB.Keys select a).ToArray();
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetLogImage(Core.LogImages, c[i]) == null)
{
cmd.CommandText = string.Format("delete from logimage where id='{0}'", c[i]);
cmd.ExecuteNonQuery();
_logimgsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.LogImage> imglist = (from Framework.Data.LogImage wp in Core.LogImages
where !wp.Saved
select wp).ToList();
if (imglist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, 0))
{
string updateSql = "update logimage set logid=@logid, url=@url, name=@name, datafromdate=@datafromdate where id=@id";
string insertSql = "insert into logimage (id, logid, url, name, datafromdate) values (@id, @logid, @url, @name, @datafromdate)";
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.LogImage lg in imglist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = lg.ID;
cmd.Parameters["@logid"].Value = lg.LogID ?? "";
cmd.Parameters["@url"].Value = lg.Url ?? "";
cmd.Parameters["@name"].Value = lg.Name ?? "";
cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");
bool indb = _logimgsInDB[lg.ID] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_logimgsInDB[lg.ID] = lg.ID;
}
}
lg.Saved = true;
if (procStep >= 500)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
c = (from string a in _wptsInDB.Keys select a).ToArray();
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetWaypoint(Core.Waypoints, c[i]) == null)
{
cmd.CommandText = string.Format("delete from waypoint where code='{0}'", c[i]);
cmd.ExecuteNonQuery();
_wptsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.Waypoint> wplist = (from Framework.Data.Waypoint wp in Core.Waypoints
where !wp.Saved
select wp).ToList();
if (wplist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, 0))
{
string updateSql = "update waypoint set id=@id, geocachecode=@geocachecode, name=@name, datafromdate=@datafromdate, comment=@comment, description=@description, url=@url, urlname=@urlname, wptype=@wptype, lat=@lat, lon=@lon, time=@time where code=@code";
string insertSql = "insert into waypoint (id, code, geocachecode, name, datafromdate, comment, description, url, urlname, wptype, lat, lon, time) values (@id, @code, @geocachecode, @name, @datafromdate, @comment, @description, @url, @urlname, @wptype, @lat, @lon, @time)";
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@geocachecode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@comment";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@description";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@urlname";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@wptype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@time";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Waypoint wp in wplist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = wp.ID;
cmd.Parameters["@code"].Value = wp.Code ?? "";
cmd.Parameters["@url"].Value = wp.Url ?? "";
cmd.Parameters["@urlname"].Value = wp.UrlName ?? "";
cmd.Parameters["@name"].Value = wp.Name ?? "";
cmd.Parameters["@comment"].Value = wp.Comment ?? "";
cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
cmd.Parameters["@description"].Value = wp.Description ?? "";
cmd.Parameters["@datafromdate"].Value = wp.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@time"].Value = wp.Time.ToUniversalTime().ToString("u");
cmd.Parameters["@wptype"].Value = wp.WPType.ID;
if (wp.Lat == null)
{
cmd.Parameters["@lat"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@lat"].Value = (double)wp.Lat;
}
if (wp.Lon == null)
{
cmd.Parameters["@lon"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@lon"].Value = wp.Lon;
}
bool indb = _wptsInDB[wp.Code] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_wptsInDB[wp.Code] = wp.Code;
}
}
wp.Saved = true;
if (procStep >= 500)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
int[] ci = (from int a in _usrwptsInDB.Keys select a).ToArray();
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
for (int i = 0; i < ci.Length; i++)
{
if (Utils.DataAccess.GetUserWaypoint(Core.UserWaypoints, ci[i]) == null)
{
cmd.CommandText = string.Format("delete from userwaypoint where id={0}", ci[i]);
cmd.ExecuteNonQuery();
_usrwptsInDB.Remove(ci[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.UserWaypoint> usrwplist = (from Framework.Data.UserWaypoint wp in Core.UserWaypoints
where !wp.Saved
select wp).ToList();
if (usrwplist.Count > 0)
{
string updateSql = "update userwaypoint set geocachecode=@geocachecode, description=@description, lat=@lat, lon=@lon, time=@time where id=@id";
string insertSql = "insert into userwaypoint (id, geocachecode, description, lat, lon, time) values (@id, @geocachecode, @description, @lat, @lon, @time)";
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@geocachecode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@description";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@time";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (DbTransaction trans = dbcon.BeginTransaction())
{
foreach (Framework.Data.UserWaypoint wp in usrwplist)
{
cmd.Parameters["@id"].Value = wp.ID;
cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
cmd.Parameters["@description"].Value = wp.Description ?? "";
cmd.Parameters["@time"].Value = wp.Date.ToUniversalTime().ToString("u");
cmd.Parameters["@lat"].Value = (double)wp.Lat;
cmd.Parameters["@lon"].Value = wp.Lon;
bool indb = _usrwptsInDB[wp.ID] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_usrwptsInDB[wp.ID] = wp.ID;
}
}
wp.Saved = true;
}
trans.Commit();
}
}
}
using (SqliteCommand cmd = new SqliteCommand(string.Format("update counter set geocache={0}, log={1}, waypoint={2}, logimage={3}", _geocachesInDB.Count, _logsInDB.Count, _wptsInDB.Count, _logimgsInDB.Count), dbcon))
cmd.ExecuteNonQuery();
}
}
return result;
}
protected int ExecuteNonQuery(SqliteCommand cmd)
{
int retries = 0;
restart:
try
{
PrepReader(ref cmd);
UnescapeSql(cmd);
var value = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return value;
}
catch (SqliteBusyException ex)
{
if (retries++ > 5)
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
else
goto restart;
return 0;
}
catch (SqliteException ex)
{
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
}
catch (Exception ex)
{
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
throw ex;
}
return 0;
}
protected override void ExportMethod()
{
try
{
using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock(this, STR_EXPORTINGGPX, STR_CREATINGFILE, 1, 0))
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
logTypes.Add(2, "Found it");
logTypes.Add(3, "Didn't find it");
logTypes.Add(4, "Write note");
logTypes.Add(5, "Archive");
logTypes.Add(7, "Needs Archived");
logTypes.Add(9, "Will Attend");
logTypes.Add(10, "Attended");
logTypes.Add(11, "Webcam Photo Taken");
logTypes.Add(12, "Unarchive");
logTypes.Add(22, "Temporarily Disable Listing");
logTypes.Add(23, "Enable Listing");
logTypes.Add(24, "Publish Listing");
logTypes.Add(25, "Retract Listing");
logTypes.Add(45, "Needs Maintenance");
logTypes.Add(46, "Owner Maintenance");
logTypes.Add(47, "Update Coordinates");
logTypes.Add(68, "Post Reviewer Note");
logTypes.Add(74, "Announcement");
if (System.IO.File.Exists(_filename))
{
System.IO.File.Delete(_filename);
}
using (var strm = Assembly.GetExecutingAssembly().GetManifestResourceStream("GlobalcachingApplication.Plugins.Locus.sqlite.db3"))
{
byte[] data = new byte[strm.Length];
strm.Read(data, 0, data.Length);
File.WriteAllBytes(_filename, data);
}
SqliteConnection dbconFiles = null;
string basePath = null;
int imgFolderIndex = 0;
int imgInFolderCount = 0;
if (Properties.Settings.Default.ExportGrabbedImages)
{
basePath = System.IO.Path.GetDirectoryName(_filename);
basePath = System.IO.Path.Combine(basePath, ".GrabbedImages");
if (!System.IO.Directory.Exists(basePath))
{
System.IO.Directory.CreateDirectory(basePath);
}
if (Properties.Settings.Default.MaxFilesInFolder > 0)
{
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
while (System.IO.Directory.Exists(imgSubFolder))
{
imgFolderIndex++;
imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
}
}
dbconFiles = new SqliteConnection(string.Format("data source=file:{0}", System.IO.Path.Combine(basePath, "files.db3")));
dbconFiles.Open();
using (SqliteCommand cmd = new SqliteCommand("", dbconFiles))
{
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='fdone'";
object o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE fdone (dlink text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifdone on fdone (dlink)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='files'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE files (Link text collate nocase, Fname text collate nocase, Found integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ilink on files (Link)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifname on files (Fname)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='purge'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE purge (pfile text)";
cmd.ExecuteNonQuery();
}
}
}
using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", _filename)))
{
dbcon.Open();
DbParameter par;
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, 0))
{
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
using (SqliteCommand cmd2 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd3 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd4 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd5 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd6 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd7 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd8 = new SqliteCommand("", dbcon))
{
cmd.CommandText = "drop index CachesSmart";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints)";
cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)";
cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)";
cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)";
cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)";
cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)";
cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)";
cmd8.CommandText = "insert into Corrected (kCode, kBeforeLat, kBeforeLon, kAfterLat, kAfterLon) values (@kCode, @kBeforeLat, @kBeforeLon, @kAfterLat, @kAfterLon)";
par = cmd8.CreateParameter();
par.ParameterName = "@kCode";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cPrefix";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cName";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cType";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLat";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLon";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cByuser";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cDate";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cFlag";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@sB1";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cComment";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cUrl";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lType";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lBy";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lDate";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLat";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLon";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lEncoded";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lownerid";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lHasHtml";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lIsowner";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lTime";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lText";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aCode";
par.DbType = DbType.String;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aId";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aInc";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@LongDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@ShortDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Url";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Hints";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@UserNote";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedBy";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheId";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheType";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Container";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasCorrected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasUserNote";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Latitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LongHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Longitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@OwnerName";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedDate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ShortHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@State";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@UserFlag";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsOwner";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LatOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LonOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Status";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@GcNote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsPremium";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@FavPoints";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
cmd2.Prepare();
cmd3.Prepare();
cmd4.Prepare();
cmd5.Prepare();
cmd6.Prepare();
cmd7.Prepare();
cmd8.Prepare();
//using (DbTransaction trans = dbcon.BeginTransaction())
//{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Geocache gc in _gcList)
{
string notes = "";
if (!string.IsNullOrEmpty(gc.Notes))
{
notes = System.Web.HttpUtility.HtmlDecode(gc.Notes);
}
if (!string.IsNullOrEmpty(gc.PersonaleNote))
{
notes = string.Concat(notes, gc.PersonaleNote);
}
cmd2.Parameters["@Code"].Value = gc.Code;
cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? "";
cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? "";
cmd2.Parameters["@Url"].Value = gc.Url ?? "";
cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? "";
cmd2.Parameters["@UserNote"].Value = notes;
cmd.Parameters["@Code"].Value = gc.Code;
cmd.Parameters["@Name"].Value = gc.Name ?? "";
cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@Archived"].Value = gc.Archived ? 1 : 0;
cmd.Parameters["@CacheId"].Value = gc.ID ?? "1";
cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType);
cmd.Parameters["@Container"].Value = getContainer(gc.Container);
cmd.Parameters["@Country"].Value = gc.Country ?? "";
cmd.Parameters["@Difficulty"].Value = gc.Difficulty;
cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@HasCorrected"].Value = (gc.CustomCoords || gc.ContainsCustomLatLon) ? 1 : 0;
cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0;
cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.');
if (gc.ContainsCustomLatLon)
{
cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.');
}
else
{
cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.');
}
cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0;
cmd.Parameters["@OwnerName"].Value = gc.Owner ?? "";
cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd");
cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@State"].Value = gc.State ?? "";
cmd.Parameters["@Terrain"].Value = gc.Terrain;
cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0;
cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T";
cmd.Parameters["@GcNote"].Value = notes;
cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@FavPoints"].Value = gc.Favorites;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
if (gc.ContainsCustomLatLon)
{
cmd8.Parameters["@kCode"].Value = gc.Code;
cmd8.Parameters["@kBeforeLat"].Value = gc.Lat.ToString().Replace(',', '.');
cmd8.Parameters["@kBeforeLon"].Value = gc.Lon.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLat"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLon"].Value = gc.CustomLon.ToString().Replace(',', '.');
cmd8.ExecuteNonQuery();
}
List<int> attr = gc.AttributeIds;
foreach (int att in attr)
{
cmd3.Parameters["@aCode"].Value = gc.Code;
cmd3.Parameters["@aId"].Value = Math.Abs(att);
cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1;
cmd3.ExecuteNonQuery();
}
List<Framework.Data.Log> logs = Utils.DataAccess.GetLogs(Core.Logs, gc.Code).Take(Properties.Settings.Default.MaxLogs).ToList();
foreach (Framework.Data.Log l in logs)
{
try
{
int logid = 0;
if (!int.TryParse(l.ID, out logid))
{
logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID);
}
cmd4.Parameters["@lLogId"].Value = logid;
cmd4.Parameters["@lText"].Value = l.Text ?? "";
cmd4.Parameters["@lParent"].Value = gc.Code;
cmd4.ExecuteNonQuery();
cmd5.Parameters["@lLogId"].Value = logid;
cmd5.Parameters["@lParent"].Value = gc.Code;
object o = logTypes[l.LogType.ID];
if (o == null)
{
cmd5.Parameters["@lType"].Value = 4;
}
else
{
cmd5.Parameters["@lType"].Value = (string)o;
}
cmd5.Parameters["@lBy"].Value = l.Finder ?? "";
cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss");
cmd5.Parameters["@lLat"].Value = DBNull.Value;
cmd5.Parameters["@lLon"].Value = DBNull.Value;
cmd5.Parameters["@lEncoded"].Value = l.Encoded;
try
{
cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId);
}
catch
{
}
cmd5.Parameters["@lHasHtml"].Value = false;
cmd5.Parameters["@lIsowner"].Value = (l.Finder == Core.GeocachingAccountNames.GetAccountName(gc.Code));
cmd5.Parameters["@lTime"].Value = "";
cmd5.ExecuteNonQuery();
}
catch
{
}
}
List<Framework.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(Core.Waypoints, gc.Code);
foreach (Framework.Data.Waypoint w in wps)
{
try
{
cmd6.Parameters["@cParent"].Value = gc.Code;
cmd6.Parameters["@cCode"].Value = w.Code;
cmd6.Parameters["@cComment"].Value = w.Comment;
cmd6.Parameters["@cUrl"].Value = w.Url;
cmd7.Parameters["@cParent"].Value = gc.Code;
cmd7.Parameters["@cCode"].Value = w.Code;
cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0, 2);
cmd7.Parameters["@cName"].Value = w.Name ?? "";
cmd7.Parameters["@cType"].Value = getWPType(w.WPType);
cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.');
cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.');
cmd7.Parameters["@cByuser"].Value = false;
cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd");
cmd7.Parameters["@cFlag"].Value = false;
cmd7.Parameters["@sB1"].Value = false;
cmd7.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
}
catch
{
}
}
if (dbconFiles != null && (gc.LongDescriptionInHtml || gc.ShortDescriptionInHtml))
{
try
{
List<string> linksInDescr = Utils.ImageSupport.GetImageUrlsFromGeocache(gc);
foreach (string link in linksInDescr)
{
string p = Utils.ImageSupport.Instance.GetImagePath(link);
if (!string.IsNullOrEmpty(p) && IsLocalFile(p))
{
using (SqliteCommand filescmd = new SqliteCommand("", dbconFiles))
{
filescmd.CommandText = string.Format("SELECT Fname FROM files WHERE Link='{0}'", link.Replace("'", "''"));
object o = filescmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
filescmd.CommandText = string.Format("insert into files (Link, Fname, Found) values ('{0}', '{1}', 1)", link.Replace("'", "''"), System.IO.Path.GetFileName(p).Replace("'", "''"));
filescmd.ExecuteNonQuery();
}
}
if (Properties.Settings.Default.MaxFilesInFolder > 0)
{
imgInFolderCount++;
if (imgInFolderCount > Properties.Settings.Default.MaxFilesInFolder)
{
imgFolderIndex++;
imgInFolderCount = 1;
}
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
if (imgInFolderCount == 1)
{
if (!System.IO.Directory.Exists(imgSubFolder))
{
System.IO.Directory.CreateDirectory(imgSubFolder);
}
}
string dst = System.IO.Path.Combine(imgSubFolder, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
else
{
string dst = System.IO.Path.Combine(basePath, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
}
}
}
catch
{
}
}
index++;
procStep++;
if (procStep >= 200)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, index);
procStep = 0;
}
}
//trans.Commit();
}
}
}
if (dbconFiles != null)
{
dbconFiles.Dispose();
dbconFiles = null;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, Utils.LanguageSupport.Instance.GetTranslation(Utils.LanguageSupport.Instance.GetTranslation(STR_ERROR)), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Add a list of folder metadata items into database
/// Combinational transaction feature is supported
/// </summary>
/// <param name="folders"></param>
/// <param name="con"></param>
/// <returns></returns>
public bool Add(IList<FolderMetadataItem> folders, SqliteConnection con)
{
const string cmdText = "INSERT INTO " + Configuration.TLB_FOLDERMETADATA +
"( " + Configuration.COL_SOURCE_ID + "," + Configuration.COL_FOLDER_RELATIVE_PATH + "," + Configuration.COL_IS_FOLDER_EMPTY + ")" +
"VALUES (@source_id , @relative_path, @isEmpty)";
try
{
foreach (FolderMetadataItem item in folders)
{
using (var cmd = new SqliteCommand(cmdText, con))
{
cmd.Parameters.Add(new SqliteParameter("@source_id", DbType.String) { Value = item.SourceId });
cmd.Parameters.Add(new SqliteParameter("@relative_path", DbType.String) { Value = item.RelativePath });
cmd.Parameters.Add(new SqliteParameter("@isEmpty", DbType.Int32){Value = item.IsEmpty});
cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{ return false; }
return true;
}