From 3180432debcd9078e8e838d4bbe3ddaf9cdfe110 Mon Sep 17 00:00:00 2001 From: Teravus Ovares Date: Mon, 31 Dec 2007 22:56:43 +0000 Subject: * Added database and UserManagerBase glue for FriendsList management * Don't forget to run prebuild --- OpenSim/Framework/Communications/LoginService.cs | 15 ++ .../Framework/Communications/UserManagerBase.cs | 74 ++++++++ OpenSim/Framework/Data.DB4o/DB4oUserData.cs | 10 ++ OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 10 ++ OpenSim/Framework/Data.MySQL/MySQLUserData.cs | 159 ++++++++++++++++- .../Resources/CreateUserFriendsTable.sql | 11 ++ OpenSim/Framework/Data.SQLite/SQLiteBase.cs | 7 +- OpenSim/Framework/Data.SQLite/SQLiteUserData.cs | 190 ++++++++++++++++++++- OpenSim/Framework/IUserData.cs | 13 ++ 9 files changed, 482 insertions(+), 7 deletions(-) create mode 100644 OpenSim/Framework/Data.MySQL/Resources/CreateUserFriendsTable.sql diff --git a/OpenSim/Framework/Communications/LoginService.cs b/OpenSim/Framework/Communications/LoginService.cs index 2ac34b1..afc6c9a 100644 --- a/OpenSim/Framework/Communications/LoginService.cs +++ b/OpenSim/Framework/Communications/LoginService.cs @@ -168,6 +168,7 @@ namespace OpenSim.Framework.UserManagement //logResponse.SimAddress = "127.0.0.1"; //overwritten //logResponse.SimPort = 0; //overwritten logResponse.Message = GetMessage(); + logResponse.BuddList = ConvertFriendListItem(m_userManager.GetUserFriendList(agentID)); try { @@ -265,6 +266,20 @@ namespace OpenSim.Framework.UserManagement return m_welcomeMessage; } + private LoginResponse.BuddyList ConvertFriendListItem(List LFL) + { + LoginResponse.BuddyList buddylistreturn = new LoginResponse.BuddyList(); + foreach (FriendListItem fl in LFL) + { + LoginResponse.BuddyList.BuddyInfo buddyitem = new LoginResponse.BuddyList.BuddyInfo(fl.Friend); + buddyitem.BuddyID = fl.Friend; + buddyitem.BuddyRightsHave = (int)fl.FriendListOwnerPerms; + buddyitem.BuddyRightsGiven = (int) fl.FriendPerms; + buddylistreturn.AddNewBuddy(buddyitem); + + } + return buddylistreturn; + } /// /// Converts the inventory library skeleton into the form required by the rpc request. /// diff --git a/OpenSim/Framework/Communications/UserManagerBase.cs b/OpenSim/Framework/Communications/UserManagerBase.cs index f913d2c..5d62e5e 100644 --- a/OpenSim/Framework/Communications/UserManagerBase.cs +++ b/OpenSim/Framework/Communications/UserManagerBase.cs @@ -197,6 +197,80 @@ namespace OpenSim.Framework.UserManagement } /// + /// Loads a user's friend list + /// + /// the UUID of the friend list owner + /// A List of FriendListItems that contains info about the user's friends + public List GetUserFriendList(LLUUID ownerID) + { + + foreach (KeyValuePair plugin in _plugins) + { + try + { + return plugin.Value.GetUserFriendList(ownerID); + } + catch (Exception e) + { + MainLog.Instance.Verbose("USERSTORAGE", + "Unable to GetUserFriendList via " + plugin.Key + "(" + e.ToString() + ")"); + } + } + + return null; + + } + + public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) + { + foreach (KeyValuePair plugin in _plugins) + { + try + { + plugin.Value.AddNewUserFriend(friendlistowner,friend,perms); + } + catch (Exception e) + { + MainLog.Instance.Verbose("USERSTORAGE", + "Unable to AddNewUserFriend via " + plugin.Key + "(" + e.ToString() + ")"); + } + } + + } + + + public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) + { + foreach (KeyValuePair plugin in _plugins) + { + try + { + plugin.Value.RemoveUserFriend(friendlistowner, friend); + } + catch (Exception e) + { + MainLog.Instance.Verbose("USERSTORAGE", + "Unable to RemoveUserFriend via " + plugin.Key + "(" + e.ToString() + ")"); + } + } + } + + public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) + { + foreach (KeyValuePair plugin in _plugins) + { + try + { + plugin.Value.UpdateUserFriendPerms(friendlistowner, friend, perms); + } + catch (Exception e) + { + MainLog.Instance.Verbose("USERSTORAGE", + "Unable to UpdateUserFriendPerms via " + plugin.Key + "(" + e.ToString() + ")"); + } + } + } + /// /// Loads a user agent by name (not called directly) /// /// The agent's name diff --git a/OpenSim/Framework/Data.DB4o/DB4oUserData.cs b/OpenSim/Framework/Data.DB4o/DB4oUserData.cs index acd14fa..c6ac526 100644 --- a/OpenSim/Framework/Data.DB4o/DB4oUserData.cs +++ b/OpenSim/Framework/Data.DB4o/DB4oUserData.cs @@ -158,6 +158,16 @@ namespace OpenSim.Framework.Data.DB4o #endregion + public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + //MainLog.Instance.Verbose("USER", "Stub UpdateUserCUrrentRegion called"); + } + + public void LogOffUser(LLUUID avatarid) + { + //MainLog.Instance.Verbose("USER", "Stub LogOffUser called"); + } + public List GeneratePickerResults(LLUUID queryID, string query) { //Do nothing yet diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs index 2c1d6d5..f8a951e 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs @@ -124,6 +124,16 @@ namespace OpenSim.Framework.Data.MSSQL #endregion + public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + MainLog.Instance.Verbose("USER", "Stub UpdateUserCUrrentRegion called"); + } + + public void LogOffUser(LLUUID avatarid) + { + MainLog.Instance.Verbose("USER", "Stub LogOffUser called"); + } + public List GeneratePickerResults(LLUUID queryID, string query) { List returnlist = new List(); diff --git a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs index c8f0517..779d050 100644 --- a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs +++ b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs @@ -77,10 +77,13 @@ namespace OpenSim.Framework.Data.MySQL tableList["agents"] = null; tableList["users"] = null; + tableList["userfriends"] = null; database.GetTableVersion(tableList); UpgradeAgentsTable(tableList["agents"]); UpgradeUsersTable(tableList["users"]); + UpgradeFriendsTable(tableList["userfriends"]); + } /// @@ -113,6 +116,21 @@ namespace OpenSim.Framework.Data.MySQL } } + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeFriendsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateUserFriendsTable.sql"); + return; + } + } + #endregion // see IUserData @@ -149,27 +167,166 @@ namespace OpenSim.Framework.Data.MySQL public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) { + int dtvalue = Util.UnixTimeSinceEpoch(); + + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + param["?friendPerms"] = perms.ToString(); + param["?datetimestamp"] = dtvalue.ToString(); + + try + { + lock (database) + { + IDbCommand adder = + database.Query( + "INSERT INTO `userfriends` " + + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + + "VALUES " + + "(?ownerID,?friendID,?friendPerms,?datetimestamp)", + param); + adder.ExecuteNonQuery(); + + adder = + database.Query( + "INSERT INTO `userfriends` " + + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + + "VALUES " + + "(?friendID,?ownerID,?friendPerms,?datetimestamp)", + param); + adder.ExecuteNonQuery(); + + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return; + } MainLog.Instance.Verbose("FRIEND", "Stub AddNewUserFriend called"); } public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) { + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + + + try + { + lock (database) + { + IDbCommand updater = + database.Query( + "delete from userfriends " + + "where ownerID = ?ownerID and friendID = ?friendID", + param); + updater.ExecuteNonQuery(); + + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return; + } MainLog.Instance.Verbose("FRIEND", "Stub RemoveUserFriend called"); } public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) { + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + param["?friendPerms"] = perms.ToString(); + + + try + { + lock (database) + { + IDbCommand updater = + database.Query( + "update userfriends " + + "SET friendPerms = ?friendPerms " + + "where ownerID = ?ownerID and friendID = ?friendID", + param); + updater.ExecuteNonQuery(); + + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return; + } MainLog.Instance.Verbose("FRIEND", "Stub UpdateUserFriendPerms called"); } public List GetUserFriendList(LLUUID friendlistowner) { + List Lfli = new List(); + + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + + try + { + lock (database) + { + //Left Join userfriends to itself + IDbCommand result = + database.Query( + "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from userfriends as a, userfriends as b" + + " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", + param); + IDataReader reader = result.ExecuteReader(); + + + while (reader.Read()) + { + FriendListItem fli = new FriendListItem(); + fli.FriendListOwner = new LLUUID((string)reader["ownerID"]); + fli.Friend = new LLUUID((string)reader["friendID"]); + fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]); + + // This is not a real column in the database table, it's a joined column from the opposite record + fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); + + Lfli.Add(fli); + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return Lfli; + } + MainLog.Instance.Verbose("FRIEND", "Stub GetUserFriendList called"); - return new List(); + return Lfli; } #endregion + public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + MainLog.Instance.Verbose("USER", "Stub UpdateUserCUrrentRegion called"); + } + + public void LogOffUser(LLUUID avatarid) + { + MainLog.Instance.Verbose("USER", "Stub LogOffUser called"); + } + public List GeneratePickerResults(LLUUID queryID, string query) { List returnlist = new List(); diff --git a/OpenSim/Framework/Data.MySQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Framework/Data.MySQL/Resources/CreateUserFriendsTable.sql new file mode 100644 index 0000000..157fe4b --- /dev/null +++ b/OpenSim/Framework/Data.MySQL/Resources/CreateUserFriendsTable.sql @@ -0,0 +1,11 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for users +-- ---------------------------- +CREATE TABLE `userfriends` ( + `ownerID` VARCHAR(37) NOT NULL, + `friendID` VARCHAR(47) NOT NULL, + `friendPerms` INT NOT NULL, + `datetimestamp` INT NOT NULL, + UNIQUE KEY (`ownerID`, `friendID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev.1'; \ No newline at end of file diff --git a/OpenSim/Framework/Data.SQLite/SQLiteBase.cs b/OpenSim/Framework/Data.SQLite/SQLiteBase.cs index 2e8d341..9131a41 100644 --- a/OpenSim/Framework/Data.SQLite/SQLiteBase.cs +++ b/OpenSim/Framework/Data.SQLite/SQLiteBase.cs @@ -135,9 +135,12 @@ namespace OpenSim.Framework.Data.SQLite subsql += ",\n"; } subsql += col.ColumnName + " " + sqliteType(col.DataType); - if (col == dt.PrimaryKey[0]) + if (dt.PrimaryKey.Length > 0) { - subsql += " primary key"; + if (col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } } } sql += subsql; diff --git a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs index 48a09d3..c97dc52 100644 --- a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs +++ b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs @@ -46,9 +46,11 @@ namespace OpenSim.Framework.Data.SQLite /// Artificial constructor called upon plugin load /// 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"; private DataSet ds; private SqliteDataAdapter da; + private SqliteDataAdapter daf; public void Initialise() { @@ -57,14 +59,29 @@ namespace OpenSim.Framework.Data.SQLite ds = new DataSet(); da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); + daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); lock (ds) { ds.Tables.Add(createUsersTable()); ds.Tables.Add(createUserAgentsTable()); + ds.Tables.Add(createUserFriendsTable()); setupUserCommands(da, conn); da.Fill(ds.Tables["users"]); + + setupUserFriendsCommands(daf, conn); + try + { + daf.Fill(ds.Tables["userfriends"]); + } + catch (SqliteSyntaxException) + { + MainLog.Instance.Verbose("SQLITE", "userfriends table not found, creating.... "); + InitDB(conn); + daf.Fill(ds.Tables["userfriends"]); + } + } return; @@ -121,27 +138,120 @@ 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) + { + + + DataRow row = friends.NewRow(); + fillFriendRow(row, friendlistowner,friend,perms); + friends.Rows.Add(row); + + row = friends.NewRow(); + fillFriendRow(row, friend, friendlistowner, 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"); + } } public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) { + DataTable ua = ds.Tables["userfriends"]; + string select = "a.ownernID '" + friendlistowner.UUID.ToString() + "' and b.friendID ='" + friend.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++) + { + FriendListItem user = new FriendListItem(); + DataRow row = rows[i]; + row.Delete(); + } + daf.Update(ds, "userfriends"); + } + } + } MainLog.Instance.Verbose("FRIEND", "Stub RemoveUserFriend called"); } public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) { + DataTable ua = ds.Tables["userfriends"]; + string select = "a.ownernID '" + friendlistowner.UUID.ToString() + "' and b.friendID ='" + friend.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++) + { + FriendListItem user = new FriendListItem(); + DataRow row = rows[i]; + row["friendPerms"] = Convert.ToInt32(perms); + } + daf.Update(ds, "userfriends"); + } + } + } MainLog.Instance.Verbose("FRIEND", "Stub UpdateUserFriendPerms called"); } public List GetUserFriendList(LLUUID friendlistowner) { - MainLog.Instance.Verbose("FRIEND", "Stub GetUserFriendList called"); - return new List(); + List returnlist = new List(); + + string select = "ownerID = '" + friendlistowner.UUID.ToString() + "' and fownerID = friendID and ffriendID = ownerID"; + lock (ds) + { + DataRow[] rows = ds.Tables["userfriends"].Select(select); + + if (rows.Length > 0) + { + for (int i = 0; i < rows.Length; i++) + { + 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]); + returnlist.Add(user); + } + } + } + return returnlist; } + + + #endregion + public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + MainLog.Instance.Verbose("USER", "Stub UpdateUserCUrrentRegion called"); + } + + public void LogOffUser(LLUUID avatarid) + { + MainLog.Instance.Verbose("USER", "Stub LogOffUser called"); + } + public List GeneratePickerResults(LLUUID queryID, string query) { List returnlist = new List(); @@ -441,6 +551,19 @@ namespace OpenSim.Framework.Data.SQLite return ua; } + private DataTable createUserFriendsTable() + { + DataTable ua = new DataTable("userfriends"); + // table contains user <----> user relationship with perms + createCol(ua, "ownerID", typeof(String)); + createCol(ua, "friendID", typeof(String)); + createCol(ua, "friendPerms", typeof(Int32)); + createCol(ua, "ownerPerms", typeof(Int32)); + createCol(ua, "datetimestamp", typeof(Int32)); + + return ua; + } + /*********************************************************************** * * Convert between ADO.NET <=> OpenSim Objects @@ -448,7 +571,7 @@ namespace OpenSim.Framework.Data.SQLite * These should be database independant * **********************************************************************/ - + private UserProfileData buildUserProfile(DataRow row) { // TODO: this doesn't work yet because something more @@ -487,6 +610,20 @@ namespace OpenSim.Framework.Data.SQLite return user; } + private void fillFriendRow(DataRow row, LLUUID ownerID, LLUUID friendID, uint perms) + { + row["ownerID"] = ownerID.UUID.ToString(); + row["friendID"] = friendID.UUID.ToString(); + row["friendPerms"] = perms; + foreach (DataColumn col in ds.Tables["userfriends"].Columns) + { + if (row[col] == null) + { + row[col] = ""; + } + } + } + private void fillUserRow(DataRow row, UserProfileData user) { row["UUID"] = Util.ToRawUuidString(user.UUID); @@ -592,23 +729,68 @@ namespace OpenSim.Framework.Data.SQLite da.DeleteCommand = delete; } + private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn) + { + daf.InsertCommand = createInsertCommand("userfriends", ds.Tables["userfriends"]); + daf.InsertCommand.Connection = conn; + + daf.UpdateCommand = createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]); + daf.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from users where ownerID=:ownerID and friendID=:friendID"); + delete.Parameters.Add(createSqliteParameter("ownerID", typeof(String))); + delete.Parameters.Add(createSqliteParameter("friendID", typeof(String))); + delete.Connection = conn; + daf.DeleteCommand = delete; + + } + private void InitDB(SqliteConnection conn) { string createUsers = defineTable(createUsersTable()); + string createFriends = defineTable(createUserFriendsTable()); + SqliteCommand pcmd = new SqliteCommand(createUsers, conn); + SqliteCommand fcmd = new SqliteCommand(createFriends, conn); + conn.Open(); - pcmd.ExecuteNonQuery(); + + try + { + + pcmd.ExecuteNonQuery(); + } + catch (System.Exception) + { + MainLog.Instance.Verbose("USERS", "users table already exists"); + } + + try + { + fcmd.ExecuteNonQuery(); + } + catch (System.Exception) + { + MainLog.Instance.Verbose("USERS", "userfriends table already exists"); + } + conn.Close(); } private bool TestTables(SqliteConnection conn) { SqliteCommand cmd = new SqliteCommand(userSelect, conn); + SqliteCommand fcmd = new SqliteCommand(userFriendsSelect, conn); SqliteDataAdapter pDa = new SqliteDataAdapter(cmd); + SqliteDataAdapter fDa = new SqliteDataAdapter(cmd); + DataSet tmpDS = new DataSet(); + DataSet tmpDS2 = new DataSet(); + try { pDa.Fill(tmpDS, "users"); + fDa.Fill(tmpDS2, "userfriends"); } catch (SqliteSyntaxException) { diff --git a/OpenSim/Framework/IUserData.cs b/OpenSim/Framework/IUserData.cs index 4e576b9..eba2329 100644 --- a/OpenSim/Framework/IUserData.cs +++ b/OpenSim/Framework/IUserData.cs @@ -93,6 +93,19 @@ namespace OpenSim.Framework bool UpdateUserProfile(UserProfileData user); /// + /// Updates the current region the User is in + /// + /// User Region the Avatar is IN + /// User Region the Avatar is IN + void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid); + + /// + /// Log User Off + /// + /// avatar to log off + void LogOffUser(LLUUID avatarid); + + /// /// Adds a new agent to the database /// /// The agent to add -- cgit v1.1