From 8ebaf7aa416ca86a881dc15f2154c59354f1f7e9 Mon Sep 17 00:00:00 2001
From: Sean Dague
Date: Thu, 10 Jan 2008 21:32:22 +0000
Subject: move Friends list to not use ADO.NET layer, it now hits the sqlite db
directly when needed
---
OpenSim/Framework/Data.SQLite/SQLiteUserData.cs | 139 ++++++++----------------
1 file changed, 43 insertions(+), 96 deletions(-)
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs
index f1f76c4..9a4a6e7 100644
--- a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs
+++ b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs
@@ -45,6 +45,10 @@ namespace OpenSim.Framework.Data.SQLite
///
/// Artificial constructor called upon plugin load
///
+ private const string SelectUserByUUID = "select * from users where UUID=:UUID";
+ private const string SelectUserByName = "select * from users where username=:username and surname=:surname";
+ private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID";
+
private const string userSelect = "select * from users";
private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b";
@@ -142,126 +146,69 @@ namespace OpenSim.Framework.Data.SQLite
public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
{
- //do stuff;
- MainLog.Instance.Verbose("FRIEND", "Stub AddNewUserFriend called");
- DataTable friends = ds.Tables["userfriends"];
- DataTable ua = ds.Tables["userfriends"];
- lock (ds)
+ string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
+
+ using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
{
-
-
- DataRow row = friends.NewRow();
- fillFriendRow(row, friendlistowner.UUID.ToString(),friend.UUID.ToString(),perms);
- friends.Rows.Add(row);
-
- row = friends.NewRow();
- fillFriendRow(row, friend.UUID.ToString(), friendlistowner.UUID.ToString(), perms);
- friends.Rows.Add(row);
-
- MainLog.Instance.Verbose("SQLITE",
- "Adding Friend: " + ds.Tables["userfriends"].Rows.Count + " friends stored");
- // save changes off to disk
- daf.Update(ds, "userfriends");
+ cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
+ cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.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.UUID.ToString()));
+ cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.UUID.ToString()));
+ cmd.Parameters.Add(new SqliteParameter(":perms", perms));
+ cmd.ExecuteNonQuery();
}
}
-
+
public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
{
- DataTable ua = ds.Tables["userfriends"];
- string select = "`ownerID` ='" + friendlistowner.UUID.ToString() + "' and `friendID` ='" + friend.UUID.ToString() + "'";
- lock (ds)
+ string DeletePerms = "delete from friendlist where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
+ using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
{
- DataRow[] rows = ds.Tables["userfriends"].Select(select);
-
- if ( rows != null)
- {
- if (rows.Length > 0)
- {
- for (int i = 0; i < rows.Length; i++)
- {
- DataRow row = rows[i];
- row.Delete();
- }
-
- }
- }
- }
- select = "`ownerID` ='" + friend.UUID.ToString() + "' and `friendID` ='" + friendlistowner.UUID.ToString() + "'";
- lock (ds)
- {
- DataRow[] rows = ds.Tables["userfriends"].Select(select);
-
- if (rows != null)
- {
- if (rows.Length > 0)
- {
- for (int i = 0; i < rows.Length; i++)
- {
- DataRow row = rows[i];
- row.Delete();
- }
-
- }
- }
+ cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
+ cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
+ cmd.ExecuteNonQuery();
}
- SqliteCommand deletecommand = new SqliteCommand("delete from userfriends where `ownerID`='" + friendlistowner.UUID.ToString() + "' and `friendID` ='" + friend.UUID.ToString() + "'", g_conn);
- g_conn.Open();
- deletecommand.ExecuteNonQuery();
- deletecommand = new SqliteCommand("delete from userfriends where `ownerID`='" + friend.UUID.ToString() + "' and `friendID` ='" + friendlistowner.UUID.ToString() + "'", g_conn);
- deletecommand.ExecuteNonQuery();
- g_conn.Close();
-
- MainLog.Instance.Verbose("FRIEND", "Stub RemoveUserFriend called");
}
+
public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
{
- DataTable ua = ds.Tables["userfriends"];
- string select = "a.ownerID ='" + friendlistowner.UUID.ToString() + "' and b.friendID ='" + friend.UUID.ToString() + "'";
- lock (ds)
+ string UpdatePerms = "update friendlist set perms=:perms where ownerID=:ownerID and friendID=:friendID";
+ using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
{
- DataRow[] rows = ds.Tables["userfriends"].Select(select);
-
- if ( rows != null)
- {
- if (rows.Length > 0)
- {
- for (int i = 0; i < rows.Length; i++)
- {
- FriendListItem user = new FriendListItem();
- DataRow row = rows[i];
- row["friendPerms"] = Convert.ToInt32(perms);
- }
- daf.Update(ds, "userfriends");
- }
- }
+ cmd.Parameters.Add(new SqliteParameter(":perms", perms));
+ cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
+ cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
+ cmd.ExecuteNonQuery();
}
- MainLog.Instance.Verbose("FRIEND", "Stub UpdateUserFriendPerms called");
}
-
public List GetUserFriendList(LLUUID friendlistowner)
{
List returnlist = new List();
-
- string select = "ownerID = '" + friendlistowner.UUID.ToString() + "' and fownerID = friendID and ffriendID = ownerID";
- lock (ds)
+
+ using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
{
- DataRow[] rows = ds.Tables["userfriends"].Select(select);
-
- if (rows.Length > 0)
+ cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
+ using (IDataReader reader = cmd.ExecuteReader())
{
- for (int i = 0; i < rows.Length; i++)
+ while(reader.Read())
{
FriendListItem user = new FriendListItem();
- DataRow row = rows[i];
- user.FriendListOwner = new LLUUID((string)row[0]);
- user.Friend = new LLUUID((string)row[1]);
- user.FriendPerms = Convert.ToUInt32(row[2]);
- user.FriendListOwnerPerms = Convert.ToUInt32(row[3]);
+ user.FriendListOwner = friendlistowner;
+ user.Friend = new LLUUID((string)reader[0]);
+ user.FriendPerms = Convert.ToUInt32(reader[1]);
+ user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
returnlist.Add(user);
}
+ reader.Close();
}
- }
+ }
+
return returnlist;
}
--
cgit v1.1