public OurMediaItem(Community.CsharpSqlite.SQLiteClient.SqliteConnection conn, int id)
{
// TODO: Complete member initialization
this.conn = conn;
this.id = id;
checked_to_patron_id = -1;
}
public void Update(SqliteConnection connection, int currentVersion)
{
foreach (var script in _scripts.Skip(currentVersion))
{
script.Apply(connection);
}
}
public OurMediaItem(Community.CsharpSqlite.SQLiteClient.SqliteConnection conn, int id)
{
// TODO: Complete member initialization
this.conn = conn;
this.id = id;
checked_to_patron_id = -1;
}
/// <summary>
/// Execute command on the database, commands are serialized
/// </summary>
/// <param name="database"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public static IEnumerable<SqliteDataReader> ExecuteCommand(string database, string commandText)
{
SqliteConnection conn;
if (!_Connection.TryGetValue(database, out conn))
{
conn = new SqliteConnection(SqliteConnectionManager.GetConnectionString(database));
conn.Open();
_Connection.Add(database, conn);
}
lock (conn)
{
var command = conn.CreateCommand();
command.CommandText = commandText;
using (var result = command.ExecuteReader())
{
while (result.Read())
{
yield return result;
}
}
command.Dispose();
}
}
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();
}
public CodeProjectDatabase()
{
dbPath = "items.db3";
IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
bool exists = isf.FileExists(dbPath);
var connection = new SqliteConnection("data source=" + dbPath);
connection.Open();
if (!exists)
{
var commands = new[]{
"CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);"
};
foreach (var command in commands)
{
using (var c = connection.CreateCommand())
{
c.CommandText = command;
c.ExecuteNonQuery();
}
}
}
}
public SimpleDBLayer(string dbPath, bool useWall)
{
#if USE_SQLITE
string journalMode = "";
if (useWall)
{
journalMode = " Journal Mode=WAL;";
}
string connectionString = String.Format("Data Source={0}; Version=3;{1}", dbPath, journalMode);
#if !USE_SQLITE_MANAGED
try {
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
}
} catch (IOException ex) {
throw new SimpleDBLayerException(ex.Message);
}
#endif // !USE_SQLITE_MANAGED
this.database = new SQLiteConnection(connectionString);
this.database.Open();
#endif // USE_SQLITE_MANAGED, USE_SQLITE
}
protected override ICSDbConnection CreateConnection()
{
SqliteConnection conn = new SqliteConnection(ConnectionString);
conn.Open();
return new CSSqliteConnection(conn);
}
/// <summary>
/// Create schema of sync source table
/// supports transaction
/// </summary>
/// <param name="con"></param>
public static void CreateSchema(SqliteConnection con)
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_DATASOURCE_INFO +
" ( " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " TEXT, " +
Configuration.COL_SOURCE_ID + " TEXT PRIMARY KEY)";
cmd.ExecuteNonQuery();
}
}
public static int ExecuteNonQuery(string sql)
{
using (SqliteConnection connection = new SqliteConnection())
{
connection.ConnectionString = ConnectionString;
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
connection.Open();
return cmd.ExecuteNonQuery();
}
}
public static void CloseDatabase()
{
try
{
Log.log("Closing sqlight database connection");
trans.Commit();
connection.Close();
connection = null;
}
catch (Exception e)
{
}
}
/// <summary>
/// Update details of sync source
/// Pass SQLiteConnection object to make atomic action
/// </summary>
/// <param name="source"></param>
/// <param name="con"></param>
/// <returns></returns>
public static bool Update(SyncSource source, SqliteConnection con )
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO +
" SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE "
+ Configuration.COL_SOURCE_ID + " = @id";
cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID });
cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = source.Path });
cmd.ExecuteNonQuery();
return true;
}
}
/// <summary>
/// This method takes in SQLiteConnection object as a parameter
/// </summary>
/// <param name="s"></param>
/// <param name="con"></param>
/// <returns></returns>
public static bool Add(SyncSource s, SqliteConnection con)
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO +
"(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH +
") VALUES (@id, @path)";
cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = s.ID });
cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = s.Path });
cmd.ExecuteNonQuery();
}
return true;
}
public override bool Initialize(Framework.Interfaces.ICore core)
{
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADING));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGDATA));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGGEOCACHES));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGLOGS));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGLOGIMAGES));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGWAYPOINTS));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVING));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGDATA));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGGEOCACHES));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGS));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGIMAGES));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGIMAGES));
core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGWAYPOINTS));
if (Properties.Settings.Default.UpgradeNeeded)
{
Properties.Settings.Default.Upgrade();
Properties.Settings.Default.UpgradeNeeded = false;
Properties.Settings.Default.Save();
}
if (string.IsNullOrEmpty(Properties.Settings.Default.ActiveDataFile))
{
Properties.Settings.Default.ActiveDataFile = System.IO.Path.Combine(core.PluginDataPath, "InternalStorage.db3" );
Properties.Settings.Default.Save();
}
try
{
string fld = System.IO.Path.GetDirectoryName(Properties.Settings.Default.ActiveDataFile);
if (!System.IO.Directory.Exists(fld))
{
System.IO.Directory.CreateDirectory(fld);
}
_dbcon = new SqliteConnection(string.Format("data source=file:{0}", Properties.Settings.Default.ActiveDataFile));
InitDatabase(_dbcon);
}
catch
{
_dbcon = null;
}
SetDataSourceName(Properties.Settings.Default.ActiveDataFile);
core.Logs.LoadFullData += new Framework.EventArguments.LogEventHandler(Logs_LoadFullData);
core.Geocaches.LoadFullData += new Framework.EventArguments.GeocacheEventHandler(Geocaches_LoadFullData);
bool result = base.Initialize(core);
return result;
}
/// <summary>
/// Open a connection to the database.
/// </summary>
private void Open()
{
if (db == null)
{
try
{
db = new SqliteConnection(Constants.Database.connectionString);
db.Open();
}
catch (Exception e)
{
System.Windows.MessageBox.Show("Error while connecting to Database: \n" + e.Message + "\n" + e.StackTrace);
}
}
}
internal static Patron get(int patron_id, Community.CsharpSqlite.SQLiteClient.SqliteConnection conn)
{
SqliteCommand cmd = new SqliteCommand(
string.Format("select id, name, type from patron where id = {0}", patron_id), conn);
SqliteDataReader rdr = cmd.ExecuteReader();
while (rdr.NextResult())
{
OurPatron p = new OurPatron(conn, rdr.GetInt32(0));
p.name = rdr.GetString(1);
p.type = (PatronType)rdr.GetInt32(2);
return(p);
}
return(null);
}
public bool Add(SyncAction action, SqliteConnection con)
{
switch (action.ChangeType)
{
case ChangeType.DELETED:
return InsertDeleteAction((DeleteAction)action);
case ChangeType.NEWLY_CREATED:
return InsertCreateAction((CreateAction)action, con);
case ChangeType.RENAMED:
return InsertRenameAction((RenameAction)action);
default:
// Log error? Throw ex?
return false;
}
}
public static bool OpenDatabase(string dbFileName)
{
try
{
Log.log("Opening sqlite database connection: "+dbFileName);
string cs = string.Format("Version=3,uri=file:{0}", dbFileName);
connection = new SqliteConnection(cs);
connection.Open();
trans = connection.BeginTransaction();
return true;
}
catch (Exception e)
{
return false;
}
}
internal static List<Patron> getAll(SqliteConnection conn)
{
List<Patron> plist = new List<Patron>();
SqliteCommand cmd = new SqliteCommand("select id, name, type from patron order by name", conn);
SqliteDataReader rdr = cmd.ExecuteReader();
while (rdr.NextResult())
{
OurPatron p = new OurPatron(conn, rdr.GetInt32(0));
p.name = rdr.GetString(1);
p.type = (PatronType)rdr.GetInt32(2);
plist.Add(p);
}
return plist;
}
/// <summary>
/// Close the connection (if there is one) to the database and clear out the SQLite HandleTracker.
/// </summary>
private void Close()
{
if (db != null)
{
try
{
db.Dispose();
db = null;
}
catch (Exception e)
{
System.Windows.MessageBox.Show("Error while closing DB connection: \n" + e.Message + "\n" + e.StackTrace);
}
}
Community.CsharpSqlite.FileStream.HandleTracker.Clear();
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] {"Data Source=", ","}, StringSplitOptions.RemoveEmptyEntries);
s1[0] = s1[0].Remove(0, 7);
_fileName = Path.GetFileName(s1[0]);
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = string.Format("Data Source=file://{0}", Path.Combine(Util.BasePathCombine(""), _fileName));
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
}
/// <summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, string commandText, object[] paramList)
{
SqliteConnection cn = new SqliteConnection(connectionString);
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
cmd.CommandText = string.Format(commandText, paramList);
}
if (cn.State == ConnectionState.Closed)
cn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return result;
}
public bool AddMember(CodeProjectMember member)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key] FROM [Member] WHERE [Key]=" + member.Id;
var r = command.ExecuteReader();
if (r.HasRows)
{
using (var uc = connection.CreateCommand())
{
uc.CommandText = "UPDATE [Member] SET " +
" [Name] = '" + member.Name + "'," +
" [ArticleCnt] = '" + member.ArticleCount + "'," +
" [BlogCnt] = '" + member.BlogCount + "'," +
" [Reputation] = '" + member.Reputation + "'," +
" WHERE [Key]=" + member.Id;
uc.ExecuteNonQuery();
}
}
else
{
using (var ic = connection.CreateCommand())
{
ic.CommandText = "INSERT INTO [Member] ([Key], [Name], [ArticleCnt], [BlogCnt], [Reputation])"
+ " VALUES(" + member.Id + ", '" + member.Name + "', '" + member.ArticleCount + "', '" + member.BlogCount + "', '" + member.Reputation + "')";
ic.ExecuteNonQuery();
}
}
}
connection.Close();
FileStorageService storage = new FileStorageService();
if (member.Avatar != null)
{
storage.WriteBytes(member.Avatar, member.Id.ToString());
}
return true;
}
public void Apply(SqliteConnection connection)
{
_connection = connection;
_log.Info("Start updating db");
_connection.Open();
try
{
CreateVcard();
SetupVersion();
}
catch (Exception e)
{
_log.Fatal("Unexpected error", e);
throw;
}
finally
{
_connection.Close();
}
}
private DatabaseManager(string dbName, bool test)
{
TestMode = test;
string finalDatabaseName = string.Format("{0}.{1}", dbName, DB_FINAL_EXT);
using (IsolatedStorageFile dataStorage = IsolatedStorageFile.GetUserStoreForApplication())
{
if (!dataStorage.FileExists(finalDatabaseName))
mountDatabase(dataStorage, finalDatabaseName);
database = new SqliteConnection(string.Format("Version=3,uri=file:{0}", finalDatabaseName));
idSessionColumn = string.Format("@{0}", SessionViewModel.ID_COLUMN_NAME);
titleSessionColumn = string.Format("@{0}", SessionViewModel.TITLE_COLUMN_NAME);
detailsSessionColumn = string.Format("@{0}", SessionViewModel.DETAILS_COLUMN_NAME);
distanceSessionColumn = string.Format("@{0}", SessionViewModel.DISTANCE_COLUMN_NAME);
timeSessionColumn = string.Format("@{0}", SessionViewModel.DURATION_COLUMN_NAME);
averageSpeedSessionColumn = string.Format("@{0}", SessionViewModel.AVERAGE_SPEED_COLUMN_NAME);
maxSpeedSessionColumn = string.Format("@{0}", SessionViewModel.MAX_SPEED_COLUMN_NAME);
kcalSessionColumn = string.Format("@{0}", SessionViewModel.KCAL_COLUMN_NAME);
sportSessionColumn = string.Format("@{0}", SessionViewModel.SPORT_COLUMN_NAME);
idSessionLocationColumn = string.Format("@{0}", LocationService.SESSION_ID_COLUMN_NAME);
latLocationColumn = string.Format("@{0}", LocationService.LAT_COLUMN_NAME);
lngLocationColumn = string.Format("@{0}", LocationService.LNG_COLUMN_NAME);
dateSessionColumn = string.Format("@{0}", SessionViewModel.DATE_COLUMN_NAME);
cmd = database.CreateCommand();
cmd.Parameters.Add(idSessionColumn, null);
cmd.Parameters.Add(titleSessionColumn, null);
cmd.Parameters.Add(detailsSessionColumn, null);
cmd.Parameters.Add(distanceSessionColumn, null);
cmd.Parameters.Add(timeSessionColumn, null);
cmd.Parameters.Add(averageSpeedSessionColumn, null);
cmd.Parameters.Add(maxSpeedSessionColumn, null);
cmd.Parameters.Add(kcalSessionColumn, null);
cmd.Parameters.Add(sportSessionColumn, null);
cmd.Parameters.Add(idSessionLocationColumn, null);
cmd.Parameters.Add(latLocationColumn, null);
cmd.Parameters.Add(lngLocationColumn, null);
cmd.Parameters.Add(dateSessionColumn, null);
}
}
protected void MainPage_Loaded(object sender, EventArgs e)
{
//SQLiteClientTests.SQLiteClientTestDriver.Main(null);
IDbConnection cnn;
try
{
System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication().DeleteFile("test.db3");
}
catch { }
using (cnn = new SqliteConnection())
{
TestCases tests = new TestCases();
cnn.ConnectionString = "data source=test.db3,password=0x01010101010101010101010101010101";
cnn.Open();
tests.Run(cnn, this);
}
}
private int QueryVersion(SqliteConnection connection)
{
connection.Open();
try
{
var tableExists = connection.CreateCommand();
tableExists.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='version';";
var versionNumber = connection.CreateCommand();
versionNumber.CommandText = "SELECT num FROM version;";
_log.Info("Query version table");
using (var reader = tableExists.ExecuteReader())
{
if (!reader.HasRows)
{
_log.Info("Database is empty");
return 0;
}
}
_log.Info("Query version number");
using (var reader = versionNumber.ExecuteReader())
{
reader.Read();
var numb = (int) reader.GetValue(0);
_log.Info(string.Format("Database version is {0}", numb));
return numb;
}
}
catch (Exception e)
{
_log.Fatal("Unexpected exception", e);
throw;
}
finally
{
connection.Close();
}
}
public void save(SqliteConnection conn)
{
throw new Exception("The method or operation is not implemented.");
}
public OurPatron(Community.CsharpSqlite.SQLiteClient.SqliteConnection conn, int id)
{
this.conn = conn;
this.id = id;
}
public QueryContext(SqliteConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection", "connection cannot be null.");
Connection = connection;
}
public OurPatron(Community.CsharpSqlite.SQLiteClient.SqliteConnection conn, int id)
{
this.conn = conn;
this.id = id;
}
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);
}
}
public CSSqliteConnection(SqliteConnection connection)
{
Connection = connection;
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
// connection string in the format...
// Data Source=File:<db_filename>
m_connectionString = connectionString;
string[] s1 = m_connectionString.Split(new[] {"Data Source=", ";", ","}, StringSplitOptions.RemoveEmptyEntries);
// first element should be file:<db_filename>
s1[0] = s1[0].Remove(0, 5);
m_fileName = s1 [0];
// some sanity checks
string filePath = Path.GetDirectoryName (s1[0]);
string fileName = Path.GetFileName (s1[0]);
if (filePath == "") //Only add this if we aren't an absolute path already eg file:data.db
{
m_connectionString = string.Format ("Data Source=file://{0}", Path.Combine (m_defaultDataPath, fileName));
filePath = m_defaultDataPath;
m_fileName = Path.Combine (m_defaultDataPath, fileName);
}
if (!Directory.Exists (filePath))
Directory.CreateDirectory (filePath); // directory does not exist!
if (!File.Exists(m_fileName))
File.Create(m_fileName).Dispose(); // database file does not exist, create an empty one to use
SqliteConnection connection = new SqliteConnection(m_connectionString);
try {
connection.Open ();
var migrationManager = new MigrationManager (this, migratorName, validateTables);
migrationManager.DetermineOperation ();
migrationManager.ExecuteOperation ();
} catch {
MainConsole.Instance.Warn ("[Sqlite]: Unable to connect to database ("+m_connectionString+")");
}
connection.Close();
}
