//////////////////////////////////////////////////////////////
//
// All non queries are funneled through one connection
// to increase performance a little
//
protected int ExecuteNonQuery(SqliteCommand cmd)
{
lock (m_Connection)
{
cmd.Connection = m_Connection;
return cmd.ExecuteNonQuery();
}
}
public void WrongSyntax()
{
SqliteCommand insertCmd = new SqliteCommand("INSERT INTO t1 VALUES (,')",_conn);
using(_conn)
{
_conn.Open();
int res = insertCmd.ExecuteNonQuery();
Assert.AreEqual(res,1);
}
}
public void InsertRandomValuesWithParameter()
{
SqliteParameter textP = new SqliteParameter();
textP.ParameterName = "textP";
textP.SourceColumn = "t";
SqliteParameter floatP = new SqliteParameter();
floatP.ParameterName = "floatP";
floatP.SourceColumn = "nu";
SqliteParameter integerP = new SqliteParameter();
integerP.ParameterName ="integerP";
integerP.SourceColumn = "i";
SqliteParameter blobP = new SqliteParameter();
blobP.ParameterName = "blobP";
blobP.SourceColumn = "b";
Random random = new Random();
StringBuilder builder = new StringBuilder();
for (int k=0; k < random.Next(0,100); k++)
{
builder.Append((char)random.Next(65536));
}
SqliteCommand insertCmd = new SqliteCommand("DELETE FROM t1; INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)",_conn);
insertCmd.Parameters.Add(textP);
insertCmd.Parameters.Add(floatP);
insertCmd.Parameters.Add(blobP);
insertCmd.Parameters.Add(integerP);
textP.Value=builder.ToString();
floatP.Value=Convert.ToInt64(random.Next(999));
integerP.Value=random.Next(999);
blobP.Value=System.Text.Encoding.UTF8.GetBytes("\u05D0\u05D1\u05D2" + builder.ToString());
SqliteCommand selectCmd = new SqliteCommand("SELECT * from t1", _conn);
using(_conn)
{
_conn.Open();
int res = insertCmd.ExecuteNonQuery();
Assert.AreEqual(res,1);
using (IDataReader reader = selectCmd.ExecuteReader()) {
Assert.AreEqual(reader.Read(), true);
Assert.AreEqual(reader["t"], textP.Value);
Assert.AreEqual(reader["f"], floatP.Value);
Assert.AreEqual(reader["i"], integerP.Value);
Assert.AreEqual(reader["b"], blobP.Value);
Assert.AreEqual(reader.Read(), false);
}
}
}
public new void ExecuteNonQuery(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
//////////////////////////////////////////////////////////////
//
// All non queries are funneled through one connection
// to increase performance a little
//
protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteNonQuery();
}
}
public Database(string file)
{
bool create = false;
if (!File.Exists (file)) {
create = true;
}
try {
StreamReader srVersion = new StreamReader (file+".version");
dbVersion = srVersion.ReadToEnd ();
if (dbVersion != null) {
dbVersion = dbVersion.Trim();
}
srVersion.Close();
}
catch {
dbVersion = null;
}
string connectionString = "URI=file:"+file;
connection = new SqliteConnection(connectionString);
connection.Open();
command = connection.CreateCommand();
if (create) {
Conf.EmptyCache();
Assembly thisAssembly = Assembly.GetEntryAssembly ();
Stream stream = thisAssembly.GetManifestResourceStream("Database.sql");
if (stream != null) {
StreamReader sr = new StreamReader (stream);
string sql = sr.ReadToEnd();
command.CommandText = sql;
command.ExecuteNonQuery();
StreamWriter swVersion = new StreamWriter (file+".version", false);
swVersion.Write (Defines.VERSION);
swVersion.Close();
dbVersion = Defines.VERSION;
}
else {
System.Console.WriteLine("Error creating the database");
}
}
if (dbVersion == null || !dbVersion.Equals (Defines.VERSION)) {
UpdateDatabase(file, dbVersion);
}
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "InventoryStore");
if (m.Version == 0)
{
//Apply all changes to db
SqliteCommand addSalePriceCmd = new SqliteCommand(addSalePrice, conn);
addSalePriceCmd.ExecuteNonQuery();
SqliteCommand addSaleTypeCmd = new SqliteCommand(addSaleType, conn);
addSaleTypeCmd.ExecuteNonQuery();
SqliteCommand addCreationDateCmd = new SqliteCommand(addCreationDate, conn);
addCreationDateCmd.ExecuteNonQuery();
SqliteCommand addGroupIDCmd = new SqliteCommand(addGroupID, conn);
addGroupIDCmd.ExecuteNonQuery();
SqliteCommand addGroupOwnedCmd = new SqliteCommand(addGroupOwned, conn);
addGroupOwnedCmd.ExecuteNonQuery();
SqliteCommand addFlagsCmd = new SqliteCommand(addFlags, conn);
addFlagsCmd.ExecuteNonQuery();
//then change version number
m.Version = 1;
}
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[InventoryStore] Migration failed. Reason: {0}", e);
return false;
}
}
public void Create()
{
try
{
if(File.Exists(_uri))
{
_conn.Dispose();
// We want to start with a fresh db for each full run
// The database is created on the first open()
File.Delete(_uri);
_conn = new SqliteConnection (_connectionString);
}
}
catch(Exception e)
{
throw e;
}
SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t TEXT, f FLOAT, i INTEGER, b TEXT);",_conn);
SqliteCommand insertCommand = new SqliteCommand("INSERT INTO t1 (t, f, i, b ) VALUES('" + stringvalue + "',123,123,'123')",_conn);
try
{
_conn.Open();
createCommand.ExecuteNonQuery();
insertCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw new AssertionException("Create table failed",e);
}
finally
{
_conn.Close();
}
}
/// <summary>
/// Creates a new entry in the cache file.
/// </summary>
/// <param name="entry">The entry to create.</param>
/// <returns>The new entry's contentId.</returns>
public uint NewEntry(ContentEntry entry)
{
uint active = 1;
if (!entry.Active)
{
active = 0;
}
uint activate = (uint)((TimeSpan)entry.Activate.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
uint expiry = 0;
if (entry.Expires)
{
expiry = (uint)((TimeSpan)entry.Expiry.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
}
String p = entry.GetPropsAsString();
SqliteCommand query = new SqliteCommand(@"INSERT INTO [content] ([active], [activate], [expire], [dayparts], [contentType], [descriptor], [size], [viewcount], [viewlimit], [displayafter], [props], [data]) VALUES (@active, @activate, @expire, @dayparts, @contentType, @descriptor, @size, @viewcount, @viewlimit, @displayafter, @props, @data); SELECT last_insert_rowid() AS contentId;", sqlite);
query.Parameters.Add(new SqliteParameter("@active", (int)active));
query.Parameters.Add(new SqliteParameter("@activate", (int)activate));
query.Parameters.Add(new SqliteParameter("@expire", (int)expiry));
query.Parameters.Add(new SqliteParameter("@dayparts", (int)entry.DayParts));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@descriptor", entry.Descriptor));
query.Parameters.Add(new SqliteParameter("@size", (int)entry.Size));
query.Parameters.Add(new SqliteParameter("@viewcount", (int)entry.ViewCount));
query.Parameters.Add(new SqliteParameter("@viewlimit", (int)entry.ViewLimit));
query.Parameters.Add(new SqliteParameter("@displayafter", (int)entry.DisplayAfter));
query.Parameters.Add(new SqliteParameter("@props", p));
query.Parameters.Add(new SqliteParameter("@data", entry.Data));
// get the contentId back
sqlite.Open();
Object cidR = query.ExecuteScalar();
uint cid = (uint)((long)cidR);
// shove cid into props
p = "contentId=" + cid.ToString() + "&" + p;
// reset connection
sqlite.Close();
sqlite.Open();
// shove in updated props
query = new SqliteCommand(@"UPDATE [content] SET [props]=@props WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@props", p));
query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
// reset connection
sqlite.Close();
sqlite.Open();
// shove in corresponding contentlist entry for record
query = new SqliteCommand(@"INSERT INTO [contentlist] ([appId], [contentType], [contentId], [userInfo], [zoneId]) VALUES (@appid, @contentType, @cid, @userinfo, @zoneid)", sqlite);
query.Parameters.Add(new SqliteParameter("@appid", this._appID));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
query.Parameters.Add(new SqliteParameter("@zoneid", entry.Properties["zoneId"]));
query.Parameters.Add(new SqliteParameter("@userinfo", entry.Properties["userInfo"]));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
return cid;
}
/// <summary>
/// Imports a single image into the database.
/// </summary>
/// <param name="contentId">The position to place the image.</param>
/// <param name="ddsimage">The image data.</param>
public void ImportImage(uint contentId, byte[] ddsimage)
{
sqlite.Open();
SqliteCommand query = new SqliteCommand(@"UPDATE [content] SET [data] = @data, [size] = @size WHERE [contentId] = @cid", sqlite);
query.Parameters.Add(new SqliteParameter("@data", ddsimage));
query.Parameters.Add(new SqliteParameter("@size", ddsimage.Length));
query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
}
/// <summary>
/// Updates the entry an the specified contentId.
/// </summary>
/// <param name="contentID">The contentId to update.</param>
/// <param name="entry">The new content of the row.</param>
/// <param name="UpdateData">Should the image/video data column be updated?</param>
public void EditEntry(uint contentID, ContentEntry entry, bool UpdateData)
{
uint active = 1;
if (!entry.Active)
{
active = 0;
}
uint activate = (uint)((TimeSpan)entry.Activate.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
uint expiry = 0;
if (entry.Expires)
{
expiry = (uint)((TimeSpan)entry.Expiry.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
}
String propS = entry.GetPropsAsString();
sqlite.Open();
SqliteCommand query = new SqliteCommand(@"UPDATE [content] SET [active]=@active, [activate]=@activate, [expire]=@expire, [dayparts]=@dayparts, [contentType]=@contentType, [descriptor]=@descriptor, [size]=@size, [viewcount]=@viewcount, [viewlimit]=@viewlimit, [displayafter]=@displayafter, [props]=@props WHERE [contentId]=@cid", sqlite);
//query = new SqliteCommand(@"UPDATE [content] SET [active]=@active, [activate]=@activate, [expire]=@expire, [props]=@props WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@cid", (int)contentID));
query.Parameters.Add(new SqliteParameter("@active", (int)active));
query.Parameters.Add(new SqliteParameter("@activate", (int)activate));
query.Parameters.Add(new SqliteParameter("@expire", (int)expiry));
query.Parameters.Add(new SqliteParameter("@dayparts", (int)entry.DayParts));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@descriptor", entry.Descriptor));
query.Parameters.Add(new SqliteParameter("@size", (int)entry.Size));
query.Parameters.Add(new SqliteParameter("@viewcount", (int)entry.ViewCount));
query.Parameters.Add(new SqliteParameter("@viewlimit", (int)entry.ViewLimit));
query.Parameters.Add(new SqliteParameter("@displayafter", (int)entry.DisplayAfter));
query.Parameters.Add(new SqliteParameter("@props", propS));
if (query.ExecuteNonQuery() != 1)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
sqlite.Open();
// update contentlist
query = new SqliteCommand(@"UPDATE [contentlist] SET [zoneId]=@zoneid, [contentType]=@contentType, [userInfo]=@userinfo WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@cid", (int)contentID));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@zoneid", entry.Properties["zoneId"]));
query.Parameters.Add(new SqliteParameter("@userinfo", entry.Properties["userInfo"]));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
if (UpdateData)
{
this.ImportImage(contentID, entry.Data);
}
}
/// <summary>
/// Deletes a record from the database.
/// </summary>
/// <param name="contentId">The record's contentId to delete.</param>
public void DeleteEntry(uint contentId)
{
sqlite.Open();
SqliteCommand query = new SqliteCommand(@"DELETE FROM [content] WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
if (query.ExecuteNonQuery() != 1)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
sqlite.Open();
query = new SqliteCommand(@"DELETE FROM [contentlist] WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
// don't error check here as old versions of the software never touched
// this table, so it may be inconsistant.
query.ExecuteNonQuery();
sqlite.Close();
}
public void UpdateUserStats(UserSessionID uid, SqliteConnection db)
{
if (uid.session_id == UUID.Zero)
return;
lock (db)
{
SqliteCommand updatecmd = new SqliteCommand(SQL_STATS_TABLE_UPDATE, db);
updatecmd.Parameters.Add(new SqliteParameter(":session_id", uid.session_data.session_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":agent_id", uid.session_data.agent_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":region_id", uid.session_data.region_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":last_updated", (int) uid.session_data.last_updated));
updatecmd.Parameters.Add(new SqliteParameter(":remote_ip", uid.session_data.remote_ip));
updatecmd.Parameters.Add(new SqliteParameter(":name_f", uid.session_data.name_f));
updatecmd.Parameters.Add(new SqliteParameter(":name_l", uid.session_data.name_l));
updatecmd.Parameters.Add(new SqliteParameter(":avg_agents_in_view", uid.session_data.avg_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":min_agents_in_view",
(int) uid.session_data.min_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":max_agents_in_view",
(int) uid.session_data.max_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":mode_agents_in_view",
(int) uid.session_data.mode_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":avg_fps", uid.session_data.avg_fps));
updatecmd.Parameters.Add(new SqliteParameter(":min_fps", uid.session_data.min_fps));
updatecmd.Parameters.Add(new SqliteParameter(":max_fps", uid.session_data.max_fps));
updatecmd.Parameters.Add(new SqliteParameter(":mode_fps", uid.session_data.mode_fps));
updatecmd.Parameters.Add(new SqliteParameter(":a_language", uid.session_data.a_language));
updatecmd.Parameters.Add(new SqliteParameter(":mem_use", uid.session_data.mem_use));
updatecmd.Parameters.Add(new SqliteParameter(":meters_traveled", uid.session_data.meters_traveled));
updatecmd.Parameters.Add(new SqliteParameter(":avg_ping", uid.session_data.avg_ping));
updatecmd.Parameters.Add(new SqliteParameter(":min_ping", uid.session_data.min_ping));
updatecmd.Parameters.Add(new SqliteParameter(":max_ping", uid.session_data.max_ping));
updatecmd.Parameters.Add(new SqliteParameter(":mode_ping", uid.session_data.mode_ping));
updatecmd.Parameters.Add(new SqliteParameter(":regions_visited", uid.session_data.regions_visited));
updatecmd.Parameters.Add(new SqliteParameter(":run_time", uid.session_data.run_time));
updatecmd.Parameters.Add(new SqliteParameter(":avg_sim_fps", uid.session_data.avg_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":min_sim_fps", uid.session_data.min_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":max_sim_fps", uid.session_data.max_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":mode_sim_fps", uid.session_data.mode_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":start_time", uid.session_data.start_time));
updatecmd.Parameters.Add(new SqliteParameter(":client_version", uid.session_data.client_version));
updatecmd.Parameters.Add(new SqliteParameter(":s_cpu", uid.session_data.s_cpu));
updatecmd.Parameters.Add(new SqliteParameter(":s_gpu", uid.session_data.s_gpu));
updatecmd.Parameters.Add(new SqliteParameter(":s_os", uid.session_data.s_os));
updatecmd.Parameters.Add(new SqliteParameter(":s_ram", uid.session_data.s_ram));
updatecmd.Parameters.Add(new SqliteParameter(":d_object_kb", uid.session_data.d_object_kb));
updatecmd.Parameters.Add(new SqliteParameter(":d_texture_kb", uid.session_data.d_texture_kb));
updatecmd.Parameters.Add(new SqliteParameter(":d_world_kb", uid.session_data.d_world_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_in_kb", uid.session_data.n_in_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_in_pk", uid.session_data.n_in_pk));
updatecmd.Parameters.Add(new SqliteParameter(":n_out_kb", uid.session_data.n_out_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_out_pk", uid.session_data.n_out_pk));
updatecmd.Parameters.Add(new SqliteParameter(":f_dropped", uid.session_data.f_dropped));
updatecmd.Parameters.Add(new SqliteParameter(":f_failed_resends", uid.session_data.f_failed_resends));
updatecmd.Parameters.Add(new SqliteParameter(":f_invalid", uid.session_data.f_invalid));
updatecmd.Parameters.Add(new SqliteParameter(":f_off_circuit", uid.session_data.f_off_circuit));
updatecmd.Parameters.Add(new SqliteParameter(":f_resent", uid.session_data.f_resent));
updatecmd.Parameters.Add(new SqliteParameter(":f_send_packet", uid.session_data.f_send_packet));
updatecmd.Parameters.Add(new SqliteParameter(":session_key", uid.session_data.session_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":agent_key", uid.session_data.agent_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":region_key", uid.session_data.region_id.ToString()));
m_log.Debug("UPDATE");
int result = updatecmd.ExecuteNonQuery();
if (result == 0)
{
m_log.Debug("INSERT");
updatecmd.CommandText = SQL_STATS_TABLE_INSERT;
try
{
updatecmd.ExecuteNonQuery();
}
catch
(SqliteExecutionException)
{
m_log.Warn("[WEBSTATS]: failed to write stats to storage Execution Exception");
}
catch (SqliteSyntaxException)
{
m_log.Warn("[WEBSTATS]: failed to write stats to storage SQL Syntax Exception");
}
}
}
}
private void CreateTables(SqliteConnection db)
{
using (SqliteCommand createcmd = new SqliteCommand(SQL_STATS_TABLE_CREATE, db))
{
createcmd.ExecuteNonQuery();
}
}
static void Test(bool v3, string encoding) {
if (!v3)
Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
else
Console.WriteLine("Testing Version 3");
System.IO.File.Delete("SqliteTest.db");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
if (v3)
connectionString += ",Version=3";
if (encoding != null)
connectionString += ",encoding=" + encoding;
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );
dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );
dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );
Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read())
for (int i = 0; i < reader.FieldCount; i++)
Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
Console.WriteLine("read and display data using DataAdapter/DataSet...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(" " + myRow[myColumn]);
}
}
}
/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
DataTable dt = new DataTable();
adapter.Fill(dt);
DataView dv = new DataView(dt);
foreach (DataRowView myRow in dv) {
foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
Console.WriteLine(" " + myRow[myColumn.ColumnName]);
}
}*/
try {
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Console.WriteLine("Should not reach here.");
} catch (Exception e) {
Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
/*try {
dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
} catch (Exception e) {
Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
}*/
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
/// <summary>
///
/// </summary>
/// <param name="parcel"></param>
public void StoreLandObject(ILandObject parcel)
{
lock (ds)
{
DataTable land = ds.Tables["land"];
DataTable landaccesslist = ds.Tables["landaccesslist"];
DataRow landRow = land.Rows.Find(parcel.LandData.GlobalID.ToString());
if (landRow == null)
{
landRow = land.NewRow();
fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
land.Rows.Add(landRow);
}
else
{
fillLandRow(landRow, parcel.LandData, parcel.RegionUUID);
}
// I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around
using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":LandUUID", parcel.LandData.GlobalID.ToString()));
cmd.ExecuteNonQuery();
}
foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList)
{
DataRow newAccessRow = landaccesslist.NewRow();
fillLandAccessRow(newAccessRow, entry, parcel.LandData.GlobalID);
landaccesslist.Rows.Add(newAccessRow);
}
}
Commit();
}
/// <summary>
///
/// </summary>
/// <param name="globalID"></param>
public void RemoveLandObject(UUID globalID)
{
lock (ds)
{
using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
cmd.ExecuteNonQuery();
}
using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString()));
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Store a terrain revision in region storage
/// </summary>
/// <param name="ter">terrain heightfield</param>
/// <param name="regionID">region UUID</param>
public void StoreTerrain(double[,] ter, UUID regionID)
{
lock (ds)
{
int revision = Util.UnixTimeSinceEpoch();
// This is added to get rid of the infinitely growing
// terrain databases which negatively impact on SQLite
// over time. Before reenabling this feature there
// needs to be a limitter put on the number of
// revisions in the database, as this old
// implementation is a DOS attack waiting to happen.
using (
SqliteCommand cmd =
new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision <= :Revision",
m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
cmd.ExecuteNonQuery();
}
// the following is an work around for .NET. The perf
// issues associated with it aren't as bad as you think.
m_log.Info("[REGION DB]: Storing terrain revision r" + revision.ToString());
String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" +
" values(:RegionUUID, :Revision, :Heightfield)";
using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter)));
cmd.ExecuteNonQuery();
}
}
}
private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
{
EstateSettings es = new EstateSettings();
es.OnSave += StoreEstateSettings;
IDataReader r = cmd.ExecuteReader();
if (r.Read())
{
foreach (string name in FieldList)
{
if (m_FieldMap[name].GetValue(es) is bool)
{
int v = Convert.ToInt32(r[name]);
if (v != 0)
m_FieldMap[name].SetValue(es, true);
else
m_FieldMap[name].SetValue(es, false);
}
else if (m_FieldMap[name].GetValue(es) is UUID)
{
UUID uuid = UUID.Zero;
UUID.TryParse(r[name].ToString(), out uuid);
m_FieldMap[name].SetValue(es, uuid);
}
else
{
m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
}
}
r.Close();
}
else if (create)
{
r.Close();
List<string> names = new List<string>(FieldList);
names.Remove("EstateID");
string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (string name in FieldList)
{
if (m_FieldMap[name].GetValue(es) is bool)
{
if ((bool)m_FieldMap[name].GetValue(es))
cmd.Parameters.Add(":"+name, "1");
else
cmd.Parameters.Add(":"+name, "0");
}
else
{
cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
}
}
cmd.ExecuteNonQuery();
cmd.CommandText = "select LAST_INSERT_ROWID() as id";
cmd.Parameters.Clear();
r = cmd.ExecuteReader();
r.Read();
es.EstateID = Convert.ToUInt32(r["id"]);
r.Close();
cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
cmd.Parameters.Add(":RegionID", regionID.ToString());
cmd.Parameters.Add(":EstateID", es.EstateID.ToString());
// This will throw on dupe key
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
es.Save();
}
LoadBanList(es);
es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
return es;
}
/// <summary>
/// Repairs an orphaned node by creating an entry in "contentlist" for the item that
/// is only listed in "content" table. Use the ContentEntry.Orphan property to
/// determine if a record is an orphan.
/// </summary>
/// <param name="contentId">The ContentID to repair.</param>
public void RepairOrphan(uint contentId)
{
sqlite.Open();
if (this.CheckIfRecordIsOrphaned(contentId))
{
// recreate contentlist entry
ContentEntry record = this.GetEntry(contentId, false);
sqlite.Close();
sqlite.Open();
// shove in corresponding contentlist entry for record
SqliteCommand query = new SqliteCommand(@"INSERT INTO [contentlist] ([appId], [contentType], [contentId], [userInfo], [zoneId]) VALUES (@appid, @contentType, @cid, @userinfo, @zoneid)", sqlite);
query.Parameters.Add(new SqliteParameter("@appid", this._appID));
query.Parameters.Add(new SqliteParameter("@contentType", (int)record.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
query.Parameters.Add(new SqliteParameter("@zoneid", record.Properties["zoneId"]));
query.Parameters.Add(new SqliteParameter("@userinfo", record.Properties["userInfo"]));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
}
sqlite.Close();
}
/// <summary>
/// Executes a "VACCUM" query on the database. This frees unused
/// space that the database has allocated.
/// </summary>
public void VaccumDatabase()
{
sqlite.Open();
SqliteCommand query = new SqliteCommand("VACUUM", sqlite);
query.ExecuteNonQuery();
sqlite.Close();
}
static void Main(string[] args)
{
Console.WriteLine("If this test works, you should get:");
Console.WriteLine("Data 1: 5");
Console.WriteLine("Data 2: Mono");
Console.WriteLine("create SqliteConnection...");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
Console.WriteLine("setting ConnectionString using: " +
connectionString);
dbcon.ConnectionString = connectionString;
Console.WriteLine("open the connection...");
dbcon.Open();
Console.WriteLine("create SqliteCommand to CREATE TABLE MONO_TEST");
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT )";
Console.WriteLine("execute command...");
dbcmd.ExecuteNonQuery();
Console.WriteLine("set and execute command to INSERT INTO MONO_TEST");
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC )"+
"VALUES(5,'Mono')";
dbcmd.ExecuteNonQuery();
Console.WriteLine("set command to SELECT FROM MONO_TEST");
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
Console.WriteLine("execute reader...");
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read()) {
Console.WriteLine("Data 1: " + reader[0].ToString());
Console.WriteLine("Data 2: " + reader[1].ToString());
}
Console.WriteLine("read and display data using DataAdapter...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(myRow[myColumn]);
}
}
}
Console.WriteLine("clean up...");
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
Console.WriteLine("Done.");
}
private void UpdateUserStats(UserSession uid, SqliteConnection db)
{
// m_log.DebugFormat(
// "[WEB STATS MODULE]: Updating user stats for {0} {1}, session {2}", uid.name_f, uid.name_l, uid.session_id);
if (uid.session_id == UUID.Zero)
return;
lock (db)
{
using (SqliteCommand updatecmd = new SqliteCommand(SQL_STATS_TABLE_INSERT, db))
{
updatecmd.Parameters.Add(new SqliteParameter(":session_id", uid.session_data.session_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":agent_id", uid.session_data.agent_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":region_id", uid.session_data.region_id.ToString()));
updatecmd.Parameters.Add(new SqliteParameter(":last_updated", (int) uid.session_data.last_updated));
updatecmd.Parameters.Add(new SqliteParameter(":remote_ip", uid.session_data.remote_ip));
updatecmd.Parameters.Add(new SqliteParameter(":name_f", uid.session_data.name_f));
updatecmd.Parameters.Add(new SqliteParameter(":name_l", uid.session_data.name_l));
updatecmd.Parameters.Add(new SqliteParameter(":avg_agents_in_view", uid.session_data.avg_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":min_agents_in_view",
(int) uid.session_data.min_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":max_agents_in_view",
(int) uid.session_data.max_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":mode_agents_in_view",
(int) uid.session_data.mode_agents_in_view));
updatecmd.Parameters.Add(new SqliteParameter(":avg_fps", uid.session_data.avg_fps));
updatecmd.Parameters.Add(new SqliteParameter(":min_fps", uid.session_data.min_fps));
updatecmd.Parameters.Add(new SqliteParameter(":max_fps", uid.session_data.max_fps));
updatecmd.Parameters.Add(new SqliteParameter(":mode_fps", uid.session_data.mode_fps));
updatecmd.Parameters.Add(new SqliteParameter(":a_language", uid.session_data.a_language));
updatecmd.Parameters.Add(new SqliteParameter(":mem_use", uid.session_data.mem_use));
updatecmd.Parameters.Add(new SqliteParameter(":meters_traveled", uid.session_data.meters_traveled));
updatecmd.Parameters.Add(new SqliteParameter(":avg_ping", uid.session_data.avg_ping));
updatecmd.Parameters.Add(new SqliteParameter(":min_ping", uid.session_data.min_ping));
updatecmd.Parameters.Add(new SqliteParameter(":max_ping", uid.session_data.max_ping));
updatecmd.Parameters.Add(new SqliteParameter(":mode_ping", uid.session_data.mode_ping));
updatecmd.Parameters.Add(new SqliteParameter(":regions_visited", uid.session_data.regions_visited));
updatecmd.Parameters.Add(new SqliteParameter(":run_time", uid.session_data.run_time));
updatecmd.Parameters.Add(new SqliteParameter(":avg_sim_fps", uid.session_data.avg_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":min_sim_fps", uid.session_data.min_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":max_sim_fps", uid.session_data.max_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":mode_sim_fps", uid.session_data.mode_sim_fps));
updatecmd.Parameters.Add(new SqliteParameter(":start_time", uid.session_data.start_time));
updatecmd.Parameters.Add(new SqliteParameter(":client_version", uid.session_data.client_version));
updatecmd.Parameters.Add(new SqliteParameter(":s_cpu", uid.session_data.s_cpu));
updatecmd.Parameters.Add(new SqliteParameter(":s_gpu", uid.session_data.s_gpu));
updatecmd.Parameters.Add(new SqliteParameter(":s_os", uid.session_data.s_os));
updatecmd.Parameters.Add(new SqliteParameter(":s_ram", uid.session_data.s_ram));
updatecmd.Parameters.Add(new SqliteParameter(":d_object_kb", uid.session_data.d_object_kb));
updatecmd.Parameters.Add(new SqliteParameter(":d_texture_kb", uid.session_data.d_texture_kb));
updatecmd.Parameters.Add(new SqliteParameter(":d_world_kb", uid.session_data.d_world_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_in_kb", uid.session_data.n_in_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_in_pk", uid.session_data.n_in_pk));
updatecmd.Parameters.Add(new SqliteParameter(":n_out_kb", uid.session_data.n_out_kb));
updatecmd.Parameters.Add(new SqliteParameter(":n_out_pk", uid.session_data.n_out_pk));
updatecmd.Parameters.Add(new SqliteParameter(":f_dropped", uid.session_data.f_dropped));
updatecmd.Parameters.Add(new SqliteParameter(":f_failed_resends", uid.session_data.f_failed_resends));
updatecmd.Parameters.Add(new SqliteParameter(":f_invalid", uid.session_data.f_invalid));
updatecmd.Parameters.Add(new SqliteParameter(":f_off_circuit", uid.session_data.f_off_circuit));
updatecmd.Parameters.Add(new SqliteParameter(":f_resent", uid.session_data.f_resent));
updatecmd.Parameters.Add(new SqliteParameter(":f_send_packet", uid.session_data.f_send_packet));
// StringBuilder parameters = new StringBuilder();
// SqliteParameterCollection spc = updatecmd.Parameters;
// foreach (SqliteParameter sp in spc)
// parameters.AppendFormat("{0}={1},", sp.ParameterName, sp.Value);
//
// m_log.DebugFormat("[WEB STATS MODULE]: Parameters {0}", parameters);
// m_log.DebugFormat("[WEB STATS MODULE]: Database stats update for {0}", uid.session_data.agent_id);
updatecmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Remove a user from the friendlist
/// </summary>
/// <param name="friendlistowner">UUID of the friendlist owner</param>
/// <param name="friend">UUID of the friend to remove</param>
override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
{
string DeletePerms = "delete from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Create an asset
/// </summary>
/// <param name="asset">Asset Base</param>
override public void StoreAsset(AssetBase asset)
{
//m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
if (ExistsAsset(asset.FullID))
{
//LogAssetLoad(asset);
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
cmd.ExecuteNonQuery();
}
}
}
else
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
cmd.ExecuteNonQuery();
}
}
}
}
public void CreateTables(SqliteConnection db)
{
SqliteCommand createcmd = new SqliteCommand(SQL_STATS_TABLE_CREATE, db);
createcmd.ExecuteNonQuery();
createcmd.CommandText = SQL_MIGRA_TABLE_CREATE;
createcmd.ExecuteNonQuery();
}
/// <summary>
/// Add a new friend in the friendlist
/// </summary>
/// <param name="friendlistowner">UUID of the friendlist owner</param>
/// <param name="friend">UUID of the friend to add</param>
/// <param name="perms">permission flag</param>
override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
{
if (ExistsFriend(friendlistowner, friend))
return;
string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
cmd.Parameters.Add(new SqliteParameter(":perms", perms));
cmd.ExecuteNonQuery();
}
using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.ToString()));
cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.ToString()));
cmd.Parameters.Add(new SqliteParameter(":perms", perms));
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Delete an asset from database
/// </summary>
/// <param name="uuid"></param>
public void DeleteAsset(UUID uuid)
{
using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Update the friendlist permission
/// </summary>
/// <param name="friendlistowner">UUID of the friendlist owner</param>
/// <param name="friend">UUID of the friend to modify</param>
/// <param name="perms">updated permission flag</param>
override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
{
string UpdatePerms = "update userfriends set friendPerms=:perms where ownerID=:ownerID and friendID=:friendID";
using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":perms", perms));
cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// This method changes the AppID of the active cache file.
/// </summary>
/// <param name="AppID">The new AppID of the program.</param>
public void ChangeAppID(int AppID)
{
// try to write back
SqliteCommand query = new SqliteCommand(@"UPDATE [contentlist] SET [appid]=@appid", sqlite);
query.Parameters.Add(new SqliteParameter("@appid", (int)AppID));
sqlite.Open();
if (query.ExecuteNonQuery() == 0)
{
// no data in contentlist... add new.
sqlite.Close();
query = new SqliteCommand(@"INSERT INTO [contentlist] ([appid]) VALUES (@appid)", sqlite);
query.Parameters.Add(new SqliteParameter("@appid", (int)AppID));
sqlite.Open();
query.ExecuteNonQuery();
}
sqlite.Close();
this.AppID = AppID;
}