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
}
internal SqliteDataReader (SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
{
command = cmd;
rows = new List<object[]>();
column_names_sens = new Dictionary<String, Object>();
column_names_insens = new Dictionary<String, Object>( StringComparer.InvariantCultureIgnoreCase );
closed = false;
current_row = -1;
reading = true;
ReadpVm (pVm, version, cmd);
ReadingDone ();
}
internal SqliteDataReader(SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
{
command = cmd;
rows = new ArrayList ();
column_names_sens = new Hashtable ();
column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
closed = false;
current_row = -1;
reading = true;
ReadpVm (pVm, version, cmd);
ReadingDone ();
}
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();
}
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;
}
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;
}
internal SqliteDataReader (SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
{
command = cmd;
rows = new ArrayList ();
column_names_sens = new Hashtable ();
#if NET_2_0
column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
#else
column_names_insens = new Hashtable (CaseInsensitiveHashCodeProvider.DefaultInvariant,
CaseInsensitiveComparer.DefaultInvariant);
#endif
closed = false;
current_row = -1;
reading = true;
ReadpVm (pVm, version, cmd);
ReadingDone ();
}
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 object SelectObject(string column, string table, string keyColumn, string comparer)
{
string selectString = String.Format(
"SELECT {0} FROM {1} WHERE {2}=@comparerParam",
column, table, keyColumn
);
object selectOut = null;
#if USE_SQLITE_MANAGED
//using( Sqlite3
#elif USE_SQLITE
using (SQLiteCommand readCommand = new SQLiteCommand(selectString, this.database)) {
readCommand.Parameters.AddWithValue("@comparerParam", comparer);
selectOut = readCommand.ExecuteScalar();
}
#endif
return(selectOut);
}
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();
}
/// <summary>
/// Select a single result using the passed in value to formulate a query.
/// </summary>
/// <param name="statement"></param>
/// <returns></returns>
public Model.Item Select(String statement)
{
Open();
Model.Item result = null;
try
{
SqliteCommand cmd = new SqliteCommand(statement, db);
SqliteDataReader reader = cmd.ExecuteReader(); //all rows exist in this object only right now
while (reader.Read())
{
//value 0 = id
//value 1 = name
//value 2 = html
result = new Model.Item((int)reader.GetValue(0), (string)reader.GetValue(1), (string)reader.GetValue(2));
}
}
catch (SqliteExecutionException ee)
{
System.Windows.MessageBox.Show("Error while querying Database: \n" + ee.Message + "\n" + ee.StackTrace);
}
catch (SqliteSyntaxException se)
{
System.Windows.MessageBox.Show("Error while querying Database: \n" + se.Message + "\n" + se.StackTrace);
}
catch (ArgumentException ae)
{
System.Windows.MessageBox.Show("Error while adding value to list: \n" + ae.Message + "\n" + ae.StackTrace);
}
finally
{
Close();
}
return result;
}
private void CreateNewCommand (ref SqliteCommand command)
{
SqliteCommand sourceCommand = SourceCommand;
if (command == null) {
command = sourceCommand.Connection.CreateCommand () as SqliteCommand;
command.CommandTimeout = sourceCommand.CommandTimeout;
command.Transaction = sourceCommand.Transaction;
}
command.CommandType = CommandType.Text;
command.UpdatedRowSource = UpdateRowSource.None;
command.Parameters.Clear ();
}
public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions,
Dictionary<string, string> renameColumns)
{
if (!TableExists(table))
{
MainConsole.Instance.WarnFormat ("[SQLite]: Trying to update a table {0} that does not exist.", table);
return;
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> sameColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in oldColumns)
{
if (columns.Any(innercolumn => innercolumn.Name.ToLower() == column.Name.ToLower() ||
renameColumns.ContainsKey(column.Name) &&
renameColumns[column.Name].ToLower() == innercolumn.Name.ToLower()))
{
sameColumns.Add(column.Name, column);
}
}
string renamedTempTableColumnDefinition = string.Empty;
string renamedTempTableColumn = string.Empty;
foreach (ColumnDefinition column in oldColumns)
{
if (renamedTempTableColumnDefinition != string.Empty)
{
renamedTempTableColumnDefinition += ", ";
renamedTempTableColumn += ", ";
}
renamedTempTableColumn += column.Name;
renamedTempTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type);
}
var cmd = new SqliteCommand
{
CommandText =
"CREATE TABLE " + table + "__temp(" + renamedTempTableColumnDefinition + ");"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create temporary table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText =
"INSERT INTO " + table + "__temp SELECT " + renamedTempTableColumn + " from " + table +
";"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Copy to tempory table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText = "drop table " + table
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Drop table, " + e);
}
CloseReaderCommand(cmd);
List<string> newTableColumnDefinition = new List<string>(columns.Length);
IndexDefinition primary = null;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
has_auto_increment |= column.Type.auto_increment;
newTableColumnDefinition.Add(column.Name + " " + GetColumnTypeStringSymbol(column.Type));
}
if (!has_auto_increment && primary != null && primary.Fields.Length > 0)
{
newTableColumnDefinition.Add("PRIMARY KEY (" + string.Join(", ", primary.Fields) + ")");
}
cmd = new SqliteCommand
{
CommandText =
string.Format("create table " + table + " ({0}) ",
string.Join(", ", newTableColumnDefinition.ToArray()))
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create new table, " + e);
}
CloseReaderCommand(cmd);
if (indexDefinitions.Length >= 1 && (primary == null || indexDefinitions.Length >= 2))
{
newTableColumnDefinition = new List<string>(primary != null ? indexDefinitions.Length : indexDefinitions.Length - 1);
// reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary || index.Fields.Length < 1)
{
continue;
}
i++;
newTableColumnDefinition.Add("CREATE " + (index.Type == IndexType.Unique ? "UNIQUE " : string.Empty) +
"INDEX idx_" + table + "_" + i + " ON " + table + "(" +
string.Join(", ", index.Fields) + ")");
}
foreach (string query in newTableColumnDefinition)
{
cmd = new SqliteCommand
{
CommandText = query
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create new index, " + e);
}
CloseReaderCommand(cmd);
}
}
string InsertFromTempTableColumnDefinition = string.Empty;
string InsertIntoFromTempTableColumnDefinition = string.Empty;
foreach (ColumnDefinition column in sameColumns.Values)
{
if (InsertFromTempTableColumnDefinition != string.Empty)
InsertFromTempTableColumnDefinition += ", ";
if (InsertIntoFromTempTableColumnDefinition != string.Empty)
InsertIntoFromTempTableColumnDefinition += ", ";
if (renameColumns.ContainsKey(column.Name))
InsertIntoFromTempTableColumnDefinition += renameColumns[column.Name];
else
InsertIntoFromTempTableColumnDefinition += column.Name;
InsertFromTempTableColumnDefinition += column.Name;
}
cmd = new SqliteCommand
{
CommandText =
"INSERT INTO " + table + " (" + InsertIntoFromTempTableColumnDefinition + ") SELECT " +
InsertFromTempTableColumnDefinition + " from " + table + "__temp;"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Copy to new table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText = "drop table " + table + "__temp"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Drop temporary table, " + e);
}
CloseReaderCommand(cmd);
}
public override bool Delete(string table, QueryFilter queryFilter)
{
Dictionary<string, object> ps = new Dictionary<string, object>();
string query = "DELETE FROM " + table +
(queryFilter != null ? (" WHERE " + queryFilter.ToSQL(':', out ps)) : "");
SqliteCommand cmd = new SqliteCommand(query);
AddParams(ref cmd, ps);
try
{
ExecuteNonQuery(cmd);
}
catch (Exception e)
{
MainConsole.Instance.Error("[Sqlite]: Delete(" + query + "), " + e);
return false;
}
CloseReaderCommand(cmd);
return true;
}
bool InsertOrReplace(string table, Dictionary<string, object> row, bool insert)
{
SqliteCommand cmd = new SqliteCommand();
string query = (insert ? "INSERT" : "REPLACE") + " INTO " + table + " (" +
string.Join(", ", row.Keys.ToArray ()) + ")";
List<string> ps = new List<string>();
foreach (KeyValuePair<string, object> field in row)
{
string key = ":" + field.Key.Replace("`", "");
ps.Add(key);
AddParam(ref cmd, key, field.Value);
}
query += " VALUES( " + string.Join(", ", ps.ToArray<string>()) + " )";
cmd.CommandText = query;
try
{
ExecuteNonQuery(cmd);
}
catch (Exception e)
{
MainConsole.Instance.Error("[Sqlite]: " + (insert ? "Insert" : "Replace") + "(" + query + "), " + e);
}
CloseReaderCommand(cmd);
return true;
}
public override bool Update(string table, Dictionary<string, object> values,
Dictionary<string, int> incrementValue, QueryFilter queryFilter,
uint? start, uint? count)
{
if ((values == null || values.Count < 1) && (incrementValue == null || incrementValue.Count < 1))
{
MainConsole.Instance.Warn("[Sqlite]: Update attempted with no values");
return false;
}
string query = string.Format("UPDATE {0}", table);
Dictionary<string, object> ps = new Dictionary<string, object>();
string filter = "";
if (queryFilter != null && queryFilter.Count > 0)
{
filter = " WHERE " + queryFilter.ToSQL(':', out ps);
}
List<string> parts = new List<string>();
if (values != null)
{
foreach (KeyValuePair<string, object> value in values)
{
string key = ":updateSet_" + value.Key.Replace("`", "");
ps[key] = value.Value;
parts.Add(string.Format("{0} = {1}", value.Key, key));
}
}
if (incrementValue != null)
{
foreach (KeyValuePair<string, int> value in incrementValue)
{
string key = ":updateSet_increment_" + value.Key.Replace("`", "");
ps[key] = value.Value;
parts.Add(string.Format("{0} = {0} + {1}", value.Key, key));
}
}
query += " SET " + string.Join(", ", parts.ToArray()) + filter;
if (start.HasValue)
{
query += " LIMIT " + start.Value;
if (count.HasValue)
{
query += ", " + count.Value;
}
}
SqliteCommand cmd = new SqliteCommand(query);
AddParams(ref cmd, ps);
try
{
ExecuteNonQuery(cmd);
}
catch (SqliteException e)
{
MainConsole.Instance.Error("[Sqlite]: Update (" + query + "), " + e);
}
CloseReaderCommand(cmd);
return true;
}
protected void PrepReader(ref SqliteCommand cmd)
{
int retries = 0;
restart:
try
{
SqliteConnection connection = new SqliteConnection(m_connectionString);
connection.Open();
cmd.Connection = connection;
}
catch (SqliteBusyException ex)
{
if (retries++ > 5)
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
else
goto restart;
}
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);
}
}
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
}
void AddParam(ref SqliteCommand cmd, string key, object value)
{
AddParam(ref cmd, key, value, false);
}
public override void DropTable(string tableName)
{
var cmd = new SqliteCommand {CommandText = string.Format("drop table {0}", tableName)};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
void AddParam(ref SqliteCommand cmd, string key, object value, bool convertByteString)
{
if (value is UUID)
cmd.Parameters.Add(key, value.ToString());
else if (value is Vector3)
cmd.Parameters.Add(key, value.ToString());
else if (value is Quaternion)
cmd.Parameters.Add(key, value.ToString());
else if (value is byte[] && convertByteString)
cmd.Parameters.Add(key, Utils.BytesToString((byte[]) value));
else if (value is ulong)
cmd.Parameters.Add(key, value.ToString());
else
cmd.Parameters.Add(key, value);
}
public override void ForceRenameTable(string oldTableName, string newTableName)
{
var cmd = new SqliteCommand
{
CommandText =
string.Format("ALTER TABLE {0} RENAME TO {1}", oldTableName,
newTableName + "_renametemp")
};
ExecuteNonQuery(cmd);
cmd.CommandText = string.Format("ALTER TABLE {0} RENAME TO {1}", newTableName + "_renametemp", newTableName);
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
public override bool Insert(string table, object[] values)
{
var cmd = new SqliteCommand();
string query;
query = string.Format("insert into {0} values(", table);
int a = 0;
foreach (object value in values)
{
query += ":" + Util.ConvertDecString(a) + ",";
AddParam(ref cmd, Util.ConvertDecString(a++), value, true);
}
query = query.Remove(query.Length - 1);
query += ")";
cmd.CommandText = query;
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Insert (" + query + "), " + e);
}
CloseReaderCommand(cmd);
return true;
}
internal void ReadpVm(Sqlite3.Vdbe pVm, int version, SqliteCommand cmd)
{
int pN;
IntPtr pazValue;
IntPtr pazColName;
bool first = true;
int[] declmode = null;
while (true)
{
bool hasdata = cmd.ExecuteStatement(pVm, out pN, out pazValue, out pazColName);
// For the first row, get the column information
if (first)
{
first = false;
if (version == 3)
{
// A decltype might be null if the type is unknown to sqlite.
decltypes = new string[pN];
declmode = new int[pN]; // 1 == integer, 2 == datetime
for (int i = 0; i < pN; i++)
{
string decl = Sqlite3.sqlite3_column_decltype(pVm, i);
if (decl != null)
{
decltypes[i] = decl.ToLower(System.Globalization.CultureInfo.InvariantCulture);
if (decltypes[i] == "int" || decltypes[i] == "integer")
{
declmode[i] = 1;
}
else if (decltypes[i] == "date" || decltypes[i] == "datetime")
{
declmode[i] = 2;
}
}
}
}
columns = new string[pN];
for (int i = 0; i < pN; i++)
{
string colName;
//if (version == 2) {
// IntPtr fieldPtr = Marshal.ReadIntPtr (pazColName, i*IntPtr.Size);
// colName = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding);
//} else {
colName = Sqlite3.sqlite3_column_name(pVm, i);
//}
columns[i] = colName;
column_names_sens [colName] = i;
column_names_insens [colName] = i;
}
}
if (!hasdata)
{
break;
}
object[] data_row = new object [pN];
for (int i = 0; i < pN; i++)
{
/*
* if (version == 2) {
* IntPtr fieldPtr = Marshal.ReadIntPtr (pazValue, i*IntPtr.Size);
* data_row[i] = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding);
* } else {
*/
switch (Sqlite3.sqlite3_column_type(pVm, i))
{
case 1:
long val = Sqlite3.sqlite3_column_int64(pVm, i);
// If the column was declared as an 'int' or 'integer', let's play
// nice and return an int (version 3 only).
if (declmode[i] == 1 && val >= int.MinValue && val <= int.MaxValue)
{
data_row[i] = (int)val;
}
// Or if it was declared a date or datetime, do the reverse of what we
// do for DateTime parameters.
else if (declmode[i] == 2)
{
data_row[i] = DateTime.FromFileTime(val);
}
else
{
data_row[i] = val;
}
break;
case 2:
data_row[i] = Sqlite3.sqlite3_column_double(pVm, i);
break;
case 3:
data_row[i] = Sqlite3.sqlite3_column_text(pVm, i);
// If the column was declared as a 'date' or 'datetime', let's play
// nice and return a DateTime (version 3 only).
if (declmode[i] == 2)
{
if (data_row[i] == null)
{
data_row[i] = null;
}
else
{
data_row[i] = DateTime.Parse((string)data_row[i], System.Globalization.CultureInfo.InvariantCulture);
}
}
break;
case 4:
int blobbytes = Sqlite3.sqlite3_column_bytes16(pVm, i);
byte[] blob = Sqlite3.sqlite3_column_blob(pVm, i);
//byte[] blob = new byte[blobbytes];
//Marshal.Copy (blobptr, blob, 0, blobbytes);
data_row[i] = blob;
break;
case 5:
data_row[i] = null;
break;
default:
throw new ApplicationException("FATAL: Unknown sqlite3_column_type");
//}
}
}
rows.Add(data_row);
}
}
public override bool Insert(string table, object[] values, string updateKey, object updateValue)
{
var cmd = new SqliteCommand();
Dictionary<string, object> ps = new Dictionary<string, object>();
string query;
query = string.Format("insert into {0} values (", table);
int i = 0;
foreach (object value in values)
{
ps[":" + Util.ConvertDecString(i)] = value;
query = string.Format(query + ":{0},", Util.ConvertDecString(i++));
}
query = query.Remove(query.Length - 1);
query += ")";
cmd.CommandText = query;
AddParams(ref cmd, ps);
try
{
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
//Execute the update then...
catch (Exception)
{
cmd = new SqliteCommand();
query = string.Format("UPDATE {0} SET {1} = '{2}'", table, updateKey, updateValue);
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
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;
}
public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions)
{
if (TableExists(table))
{
MainConsole.Instance.WarnFormat("[SQLite]: Trying to create a table '{0}' that already exists.", table);
return;
}
IndexDefinition primary = null;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
List<string> columnDefinition = new List<string>();
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
has_auto_increment |= column.Type.auto_increment;
columnDefinition.Add(column.Name + " " + GetColumnTypeStringSymbol(column.Type));
}
if (!has_auto_increment && primary != null && primary.Fields.Length > 0)
{
columnDefinition.Add("PRIMARY KEY (" + string.Join(", ", primary.Fields) + ")");
}
var cmd = new SqliteCommand
{
CommandText =
string.Format ("create table " + table + " ({0})",
string.Join (", ", columnDefinition.ToArray ()))
};
try {
ExecuteNonQuery (cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create table (" + table + "), " + e);
}
CloseReaderCommand(cmd);
if (indexDefinitions.Length >= 1 && (primary == null || indexDefinitions.Length >= 2))
{
columnDefinition = new List<string>(primary != null ? indexDefinitions.Length : indexDefinitions.Length - 1);
// reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary || index.Fields.Length < 1)
{
continue;
}
i++;
columnDefinition.Add("CREATE " + (index.Type == IndexType.Unique ? "UNIQUE " : string.Empty) +
"INDEX idx_" + table + "_" + i + " ON " + table + "(" +
string.Join(", ", index.Fields) + ")");
}
foreach (string query in columnDefinition)
{
cmd = new SqliteCommand
{
CommandText = query
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create index (" + query + "), " + e);
}
CloseReaderCommand(cmd);
}
}
}
static void UnescapeSql(SqliteCommand cmd)
{
foreach (SqliteParameter v in cmd.Parameters)
{
if (v.Value == null)
v.Value = "";
if (v.Value.ToString().Contains("\\'"))
v.Value = v.Value.ToString().Replace("\\'", "\'");
if (v.Value.ToString().Contains("\\\""))
v.Value = v.Value.ToString().Replace("\\\"", "\"");
}
}
public CSSqliteCommand(SqliteCommand command)
{
Command = command;
}
protected void CloseReaderCommand(SqliteCommand cmd)
{
cmd.Connection.Close();
cmd.Parameters.Clear();
cmd.Dispose (); // previously commented but this is a resource leak if not cleared out - greythane - 20160427
}
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);
}
}
void AddParams(ref SqliteCommand cmd, Dictionary<string, object> ps)
{
foreach (KeyValuePair<string, object> p in ps)
AddParam(ref cmd, p.Key, p.Value);
}
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions,
IndexDefinition[] indexDefinitions)
{
var cmd = new SqliteCommand
{
CommandText =
string.Format("insert into {0} select * from {1}", destinationTableName,
sourceTableName)
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
public DBRow[] SelectRows(
DBColumn[] columnSelections, Tuple <string, string> tableSelection, DBJoinTable[] tableJoins,
DBCondition[] conditions
)
{
List <DBRow> rowsOut = new List <DBRow>();
List <string> columnSelectionStrings = new List <string>();
List <string> tableJoinStrings = new List <string>();
#if USE_SQLITE
foreach (DBColumn column in columnSelections)
{
columnSelectionStrings.Add(
column.TableChar + "." + column.Name
);
}
// Convert the join objects into DB-specific join strings.
foreach (DBJoinTable join in tableJoins)
{
tableJoinStrings.Add(String.Format(
"LEFT OUTER JOIN {0} AS {1} ON {2}.{3}={4}.{5} ",
join.TableName,
join.TableChar,
tableSelection.Item2,
join.MainKey,
join.TableChar,
join.ForeignKey
));
}
string[] conditionStrings = TransformConditionStrings(conditions);
string selectQueryString = String.Format(
"SELECT {0} FROM {1} AS {2} {3} WHERE {4} ORDER BY t.src_ip ASC",
string.Join(",", columnSelectionStrings),
tableSelection.Item1,
tableSelection.Item2,
string.Join(" ", tableJoinStrings),
string.Join(" AND ", conditionStrings)
);
using (SQLiteCommand trafficReadCommand = new SQLiteCommand(
selectQueryString, database
)) {
/*
* trafficReadCommand.Parameters.AddWithValue( "@ipLowParameter", DBConversions.IP_LOCAL_LOW );
* trafficReadCommand.Parameters.AddWithValue( "@ipHighParameter", DBConversions.IP_LOCAL_HIGH );
* trafficReadCommand.Parameters.AddWithValue( "@timeStartParameter", this.startTime );
* trafficReadCommand.Parameters.AddWithValue( "@timeEndParameter", this.endTime );
*/
// Add the proxies.
foreach (DBCondition condition in conditions)
{
#if USE_SQLITE_MANAGED
trafficReadCommand.Parameters.Add(
TransformProxyToken(condition),
condition.TestValue
);
#else
trafficReadCommand.Parameters.AddWithValue(
TransformProxyToken(condition),
condition.TestValue
);
#endif // USE_SQLITE_MANAGED
}
using (SQLiteDataReader reader = trafficReadCommand.ExecuteReader()) {
while (reader.Read())
{
int columnIndexIter = 0;
DBRow rowIter = new DBRow();
foreach (DBColumn column in columnSelections)
{
string columnKey = column.TableChar + "." + column.Name;
switch (column.Type)
{
case DBDataType.STRING:
rowIter.Set(columnKey, "");
if (!reader.IsDBNull(columnIndexIter))
{
rowIter.Set(columnKey, reader.GetString(columnIndexIter));
}
break;
case DBDataType.INT:
rowIter.Set(columnKey, reader.GetInt32(columnIndexIter));
break;
case DBDataType.DOUBLE:
rowIter.Set(columnKey, reader.GetDouble(columnIndexIter));
break;
}
columnIndexIter++;
}
rowsOut.Add(rowIter);
}
}
}
#endif
return(rowsOut.ToArray());
}