protected IDataReader ExecuteReader(SqliteCommand cmd)
{
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)m_Connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
return cmd.ExecuteReader();
}
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);
}
}
}
protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteReader();
}
}
public void Select()
{
SqliteCommand simpleSelect = new SqliteCommand("SELECT * FROM t1; ", _conn); // check trailing spaces
using(_conn)
{
_conn.Open();
SqliteDataReader dr = simpleSelect.ExecuteReader();
while(dr.Read())
{
string test = dr[0].ToString();
Assert.AreEqual(dr["T"], stringvalue); // also checks case-insensitive column
Assert.AreEqual(dr["F"], 123);
Assert.AreEqual(dr["I"], 123);
Assert.AreEqual(dr["B"], "123");
}
Assert.IsTrue(dr.FieldCount>0);
}
}
public string Get(string scope, string key)
{
string command = "SELECT `value` FROM `generic` WHERE `key` = @key";
if (!String.IsNullOrEmpty(scope))
command += " AND `scope` = @scope";
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(command, m_Connection))
{
cmd.Parameters.Add("@key", key);
if (!String.IsNullOrEmpty(scope))
cmd.Parameters.Add("@scope", scope);
using (IDataReader result = cmd.ExecuteReader())
{
if (result.Read())
return result.GetString(0);
else
return null;
}
}
}
}
// /// <summary>
// /// Some... logging functionnality
// /// </summary>
// /// <param name="asset"></param>
// private static void LogAssetLoad(AssetBase asset)
// {
// string temporary = asset.Temporary ? "Temporary" : "Stored";
// string local = asset.Local ? "Local" : "Remote";
//
// int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
//
// m_log.Debug("[ASSET DB]: " +
// string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
// asset.FullID, asset.Name, asset.Description, asset.Type,
// temporary, local, assetLength));
// }
/// <summary>
/// Check if an asset exist in database
/// </summary>
/// <param name="uuid">The asset UUID</param>
/// <returns>True if exist, or false.</returns>
override public bool ExistsAsset(UUID uuid)
{
lock (this) {
using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
reader.Close();
return true;
}
else
{
reader.Close();
return false;
}
}
}
}
}
// ============================================
// PUBLIC Methods
// ============================================
/// Return True if Specified Table Exists
public bool TableExists(string table)
{
string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=@Table;";
SqliteCommand sqlCmd = new SqliteCommand(sql, this);
sqlCmd.Parameters.Add("@Table", table);
SqliteDataReader sqlReader = sqlCmd.ExecuteReader();
bool tableExists = false;
if (sqlReader.Read())
tableExists = true;
sqlReader.Close();
return(tableExists);
}
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>
/// Fetches the entry at the contentId specified, and returns a ContentEntry
/// that is the row.
/// </summary>
/// <param name="contentId">The contentId to fetch.</param>
/// <param name="IncludeImageData">Should image data be included?</param>
/// <returns>A ContentEntry that is the row, or null if it wasn't found.</returns>
public ContentEntry GetEntry(uint contentId, bool IncludeImageData)
{
SqliteCommand cmd = new SqliteCommand("SELECT * FROM [content] WHERE [contentId] = @cid LIMIT 1", sqlite);
cmd.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
sqlite.Open();
ContentEntry entry;
SqliteDataReader reader = cmd.ExecuteReader();
reader.Read();
entry = this.GetEntryFromReader(reader, IncludeImageData);
sqlite.Close();
return entry;
}
/// <summary>
/// Checks if a record doesn't appear in the contentList table.
/// </summary>
/// <param name="contentId">The ContentID to check.</param>
/// <returns>true if the record is orphaned.</returns>
private bool CheckIfRecordIsOrphaned(uint contentId)
{
SqliteCommand query = new SqliteCommand(@"SELECT [contentId] FROM [contentlist] WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
SqliteDataReader r = query.ExecuteReader();
uint count = 0;
while (r.Read())
{
count++;
}
return count == 0;
}
private bool ExistsFirstLastName(String fname, String lname)
{
string FindUser = "******";
using (SqliteCommand cmd = new SqliteCommand(FindUser, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":username", fname));
cmd.Parameters.Add(new SqliteParameter(":surname", lname));
try
{
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
reader.Close();
return true;
}
else
{
reader.Close();
return false;
}
}
}
catch (Exception ex)
{
m_log.Error("[USER DB]: Exception searching for user's first and last name: " + ex.ToString());
return false;
}
}
}
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>
/// Get (fetch?) the friendlist for a user
/// </summary>
/// <param name="friendlistowner">UUID of the friendlist owner</param>
/// <returns>The friendlist list</returns>
override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
{
List<FriendListItem> returnlist = new List<FriendListItem>();
using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
try
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FriendListItem user = new FriendListItem();
user.FriendListOwner = friendlistowner;
user.Friend = new UUID((string)reader[0]);
user.FriendPerms = Convert.ToUInt32(reader[1]);
user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
returnlist.Add(user);
}
reader.Close();
}
}
catch (Exception ex)
{
m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
}
}
return returnlist;
}
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.");
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_assetConnectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "AssetStore");
if (m.Version == 0)
{
//fetch all assets with mediaurl and construct RexAssetData objects
List<RexAssetData> rexAssets = new List<RexAssetData>();
using (SqliteCommand cmd = new SqliteCommand(assetSelect, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (((String)reader["MediaURL"]) != "")
{
UUID id = new UUID((String) reader["UUID"]);
string mediaUrl = (String)reader["MediaURL"];
byte refreshRate = 0;
object refRate = reader["RefreshRate"];
if (refRate is byte)
{
refreshRate = (byte)refRate;
}
RexAssetData data = new RexAssetData(id, mediaUrl,refreshRate);
rexAssets.Add(data);
}
}
}
}
conn.Close();
//Now add them to ModreX database
NHibernateRexAssetData rexAssetManager = new NHibernateRexAssetData();
rexAssetManager.Initialise(m_rexConnectionString);
foreach (RexAssetData data in rexAssets)
{
rexAssetManager.StoreObject(data);
}
//finally remove realXtend properties and update version number
conn.Open();
//TODO: remove realXtend properties
// this is not done yet because SQLite is missing drop column feature
m.Version = 1;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[AssetStore] Migration failed. Reason: {0}", e);
return false;
}
}
/// <summary>
/// Load the latest terrain revision from region storage
/// </summary>
/// <param name="regionID">the region UUID</param>
/// <returns>Heightfield data</returns>
public double[,] LoadTerrain(UUID regionID)
{
lock (ds)
{
double[,] terret = new double[(int)Constants.RegionSize, (int)Constants.RegionSize];
terret.Initialize();
String sql = "select RegionUUID, Revision, Heightfield from terrain" +
" where RegionUUID=:RegionUUID order by Revision desc";
using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
using (IDataReader row = cmd.ExecuteReader())
{
int rev = 0;
if (row.Read())
{
// TODO: put this into a function
using (MemoryStream str = new MemoryStream((byte[])row["Heightfield"]))
{
using (BinaryReader br = new BinaryReader(str))
{
for (int x = 0; x < (int)Constants.RegionSize; x++)
{
for (int y = 0; y < (int)Constants.RegionSize; y++)
{
terret[x, y] = br.ReadDouble();
}
}
}
}
rev = (int) row["Revision"];
}
else
{
m_log.Info("[REGION DB]: No terrain found for region");
return null;
}
m_log.Info("[REGION DB]: Loaded terrain revision r" + rev.ToString());
}
}
return terret;
}
}
private bool ExistsFriend(UUID owner, UUID friend)
{
string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
using (SqliteCommand cmd = new SqliteCommand(FindFriends, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":ownerID", owner.ToString()));
cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
try
{
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
reader.Close();
return true;
}
else
{
reader.Close();
return false;
}
}
}
catch (Exception ex)
{
m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
return false;
}
}
}
/// <summary>
/// Returns a list of AssetMetadata objects. The list is a subset of
/// the entire data set offset by <paramref name="start" /> containing
/// <paramref name="count" /> elements.
/// </summary>
/// <param name="start">The number of results to discard from the total data set.</param>
/// <param name="count">The number of rows the returned list should contain.</param>
/// <returns>A list of AssetMetadata objects.</returns>
public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
{
List<AssetMetadata> retList = new List<AssetMetadata>(count);
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":start", start));
cmd.Parameters.Add(new SqliteParameter(":count", count));
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AssetMetadata metadata = buildAssetMetadata(reader);
retList.Add(metadata);
}
}
}
}
return retList;
}
/// <summary>
///
/// </summary>
/// <param name="queryID"></param>
/// <param name="query"></param>
/// <returns></returns>
override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
{
List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
string[] querysplit;
querysplit = query.Split(' ');
if (querysplit.Length == 2)
{
using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%"));
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AvatarPickerAvatar user = new AvatarPickerAvatar();
user.AvatarID = new UUID((string) reader["UUID"]);
user.firstName = (string) reader["username"];
user.lastName = (string) reader["surname"];
returnlist.Add(user);
}
reader.Close();
}
}
}
else if (querysplit.Length == 1)
{
using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn))
{
cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%"));
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AvatarPickerAvatar user = new AvatarPickerAvatar();
user.AvatarID = new UUID((string) reader["UUID"]);
user.firstName = (string) reader["username"];
user.lastName = (string) reader["surname"];
returnlist.Add(user);
}
reader.Close();
}
}
}
return returnlist;
}
/// <summary>
/// Fetch Asset
/// </summary>
/// <param name="uuid">UUID of ... ?</param>
/// <returns>Asset base</returns>
override public AssetBase GetAsset(UUID uuid)
{
lock (this)
{
using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
{
cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
AssetBase asset = buildAsset(reader);
reader.Close();
return asset;
}
else
{
reader.Close();
return null;
}
}
}
}
}
public stats_default_page_values rep_DefaultReport_data(SqliteConnection db, List<Scene> m_scene)
{
stats_default_page_values returnstruct = new stats_default_page_values();
returnstruct.all_scenes = m_scene.ToArray();
lock (db)
{
string SQL = @"SELECT COUNT(DISTINCT agent_id) as agents, COUNT(*) as sessions, AVG(avg_fps) as client_fps,
AVG(avg_sim_fps) as savg_sim_fps, AVG(avg_ping) as sav_ping, SUM(n_out_kb) as num_in_kb,
SUM(n_out_pk) as num_in_packets, SUM(n_in_kb) as num_out_kb, SUM(n_in_pk) as num_out_packets, AVG(mem_use) as sav_mem_use
FROM stats_session_data;";
SqliteCommand cmd = new SqliteCommand(SQL, db);
SqliteDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
sdr.Read();
returnstruct.total_num_users = Convert.ToInt32(sdr["agents"]);
returnstruct.total_num_sessions = Convert.ToInt32(sdr["sessions"]);
returnstruct.avg_client_fps = Convert.ToSingle(sdr["client_fps"]);
returnstruct.avg_sim_fps = Convert.ToSingle(sdr["savg_sim_fps"]);
returnstruct.avg_ping = Convert.ToSingle(sdr["sav_ping"]);
returnstruct.total_kb_out = Convert.ToSingle(sdr["num_out_kb"]);
returnstruct.total_kb_in = Convert.ToSingle(sdr["num_in_kb"]);
returnstruct.avg_client_mem_use = Convert.ToSingle(sdr["sav_mem_use"]);
}
}
return returnstruct;
}
public Hashtable ProcessModel(Hashtable pParams)
{
Hashtable modeldata = new Hashtable();
modeldata.Add("Scenes", pParams["Scenes"]);
modeldata.Add("Reports", pParams["Reports"]);
SqliteConnection dbConn = (SqliteConnection)pParams["DatabaseConnection"];
List<SessionList> lstSessions = new List<SessionList>();
Hashtable requestvars = (Hashtable) pParams["RequestVars"];
string puserUUID = string.Empty;
string clientVersionString = string.Empty;
int queryparams = 0;
if (requestvars != null)
{
if (requestvars.ContainsKey("UserID"))
{
UUID testUUID = UUID.Zero;
if (UUID.TryParse(requestvars["UserID"].ToString(), out testUUID))
{
puserUUID = requestvars["UserID"].ToString();
}
}
if (requestvars.ContainsKey("VersionString"))
{
clientVersionString = requestvars["VersionString"].ToString();
}
}
lock (dbConn)
{
string sql =
"SELECT distinct a.name_f, a.name_l, a.Agent_ID, b.Session_ID, b.client_version, b.last_updated, b.start_time FROM stats_session_data a LEFT OUTER JOIN stats_session_data b ON a.Agent_ID = b.Agent_ID";
if (puserUUID.Length > 0)
{
if (queryparams == 0)
sql += " WHERE";
else
sql += " AND";
sql += " b.agent_id=:agent_id";
queryparams++;
}
if (clientVersionString.Length > 0)
{
if (queryparams == 0)
sql += " WHERE";
else
sql += " AND";
sql += " b.client_version=:client_version";
queryparams++;
}
sql += " ORDER BY a.name_f, a.name_l, b.last_updated;";
SqliteCommand cmd = new SqliteCommand(sql, dbConn);
if (puserUUID.Length > 0)
cmd.Parameters.Add(new SqliteParameter(":agent_id", puserUUID));
if (clientVersionString.Length > 0)
cmd.Parameters.Add(new SqliteParameter(":client_version", clientVersionString));
SqliteDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
UUID userUUID = UUID.Zero;
SessionList activeSessionList = new SessionList();
activeSessionList.user_id=UUID.Random();
while (sdr.Read())
{
UUID readUUID = UUID.Parse(sdr["agent_id"].ToString());
if (readUUID != userUUID)
{
activeSessionList = new SessionList();
activeSessionList.user_id = readUUID;
activeSessionList.firstname = sdr["name_f"].ToString();
activeSessionList.lastname = sdr["name_l"].ToString();
activeSessionList.sessions = new List<ShortSessionData>();
lstSessions.Add(activeSessionList);
}
ShortSessionData ssd = new ShortSessionData();
ssd.last_update = Utils.UnixTimeToDateTime((uint)Convert.ToInt32(sdr["last_updated"]));
ssd.start_time = Utils.UnixTimeToDateTime((uint)Convert.ToInt32(sdr["start_time"]));
ssd.session_id = UUID.Parse(sdr["session_id"].ToString());
ssd.client_version = sdr["client_version"].ToString();
activeSessionList.sessions.Add(ssd);
userUUID = activeSessionList.user_id;
}
}
sdr.Close();
sdr.Dispose();
}
modeldata["SessionData"] = lstSessions;
return modeldata;
}
