From 281955949910eb257b5f7e42e54535ba7812418e Mon Sep 17 00:00:00 2001 From: Melanie Thielker Date: Sun, 14 Sep 2008 13:23:02 +0000 Subject: Mantis #2124 Thank you, RuudL, for a patch that brings MSSQL up to the same implementation level as MySQL. --- OpenSim/Data/MSSQL/MSSQLUserData.cs | 1368 +++++++++++++++++++++-------------- 1 file changed, 833 insertions(+), 535 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLUserData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs index 9118012..6352cce 100644 --- a/OpenSim/Data/MSSQL/MSSQLUserData.cs +++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs @@ -26,6 +26,7 @@ */ using System; +using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; @@ -41,6 +42,8 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLUserData : UserDataBase { + private const string _migrationStore = "UserStore"; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// @@ -52,28 +55,35 @@ namespace OpenSim.Data.MSSQL private string m_usersTableName; private string m_userFriendsTableName; - public override void Initialise() + override public void Initialise() { m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); + throw new PluginNotInitialisedException(Name); } /// /// Loads and initialises the MSSQL storage plugin /// - /// TODO: do something with the connect string instead of ignoring it. + /// connectionstring /// use mssql_connection.ini override public void Initialise(string connect) { - // TODO: do something with the connect string instead of - // ignoring it. - IniFile iniFile = new IniFile("mssql_connection.ini"); - string settingDataSource = iniFile.ParseFileReadValue("data_source"); - string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); - string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); - string settingUserId = iniFile.ParseFileReadValue("user_id"); - string settingPassword = iniFile.ParseFileReadValue("password"); + + if (string.IsNullOrEmpty(connect)) + { + database = new MSSQLManager(connect); + } + else + { + string settingDataSource = iniFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); + string settingUserId = iniFile.ParseFileReadValue("user_id"); + string settingPassword = iniFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } m_usersTableName = iniFile.ParseFileReadValue("userstablename"); if (m_usersTableName == null) @@ -93,23 +103,22 @@ namespace OpenSim.Data.MSSQL m_agentsTableName = "agents"; } - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); - + //TODO this can be removed at one time!!!!! TestTables(); + + //Check migration on DB + database.CheckMigration(_migrationStore); } - public override void Dispose () {} + override public void Dispose() { } /// - /// + /// Can be deleted at one time! /// /// - private bool TestTables() + private void TestTables() { - - using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary())) + using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName)) { try { @@ -145,300 +154,216 @@ namespace OpenSim.Data.MSSQL } } - return true; - } - - /// - /// Searches the database for a specified user profile by name components - /// - /// The first part of the account name - /// The second part of the account name - /// A user profile - override public UserProfileData GetUserByName(string user, string last) - { - try + using (IDbCommand cmd = database.Query("select top 1 * from avatarappearance", new Dictionary())) { - Dictionary param = new Dictionary(); - param["first"] = user; - param["second"] = last; - - using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param)) - using (IDataReader reader = result.ExecuteReader()) + try { - return database.readUserRow(reader); + cmd.ExecuteNonQuery(); + } + catch + { + database.ExecuteResourceSql("AvatarAppearance.sql"); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } - - #region User Friends List Data - - /// - /// Add a new friend in the friendlist - /// - /// UUID of the friendlist owner - /// Friend's UUID - /// Permission flag - override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms) - { - int dtvalue = Util.UnixTimeSinceEpoch(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - param["@friendPerms"] = perms.ToString(); - param["@datetimestamp"] = dtvalue.ToString(); - try + //Special for Migrations + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = 'UserStore'")) { - using (IDbCommand adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@ownerID,@friendID,@friendPerms,@datetimestamp)", - param)) + try { - adder.ExecuteNonQuery(); + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)"; + cmd.ExecuteNonQuery(); + } } - - using (IDbCommand adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@friendID,@ownerID,@friendPerms,@datetimestamp)", - param)) + catch { - adder.ExecuteNonQuery(); + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)"; + cmd.ExecuteNonQuery(); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return; - } + return; } + #region User table methods + /// - /// Remove an friend from the friendlist + /// Searches the database for a specified user profile by name components /// - /// UUID of the friendlist owner - /// UUID of the not-so-friendly user to remove from the list - override public void RemoveUserFriend(UUID friendlistowner, UUID friend) + /// The first part of the account name + /// The second part of the account name + /// A user profile + override public UserProfileData GetUserByName(string user, string last) { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - - - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second")) { - using (IDbCommand updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID", - param)) + command.Parameters.Add(database.CreateParameter("first", user)); + command.Parameters.Add(database.CreateParameter("second", last)); + + try { - updater.ExecuteNonQuery(); + using (SqlDataReader reader = command.ExecuteReader()) + { + return ReadUserRow(reader); + } } - - using (IDbCommand updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @friendID and friendID = @ownerID", - param)) + catch (Exception e) { - updater.ExecuteNonQuery(); + m_log.Error("[USER DB] Error getting user profile, error: " + e.Message); + return null; } - - } - catch (Exception e) - { - m_log.Error(e.ToString()); } } /// - /// Update friendlist permission flag for a friend + /// See IUserDataPlugin /// - /// UUID of the friendlist owner - /// UUID of the friend - /// new permission flag - override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) + /// + /// + override public UserProfileData GetUserByUUID(UUID uuid) { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - param["@friendPerms"] = perms.ToString(); - - - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid")) { - using (IDbCommand updater = - database.Query( - "update " + m_userFriendsTableName + - " SET friendPerms = @friendPerms " + - "where ownerID = @ownerID and friendID = @friendID", - param)) + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + + try { - updater.ExecuteNonQuery(); + using (IDataReader reader = command.ExecuteReader()) + { + return ReadUserRow(reader); + } + } + catch (Exception e) + { + m_log.Error("[USER DB] Error getting user profile by UUID, error: " + e.Message); + return null; } - } - catch (Exception e) - { - m_log.Error(e.ToString()); } } + /// - /// Get (fetch?) the user's friendlist + /// Creates a new users profile /// - /// UUID of the friendlist owner - /// Friendlist list - override public List GetUserFriendList(UUID friendlistowner) + /// The user profile to create + override public void AddNewUserProfile(UserProfileData user) { - List Lfli = new List(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - try { - //Left Join userfriends to itself - using (IDbCommand result = - database.Query( - "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + - " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", - param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - FriendListItem fli = new FriendListItem(); - fli.FriendListOwner = new UUID((string)reader["ownerID"]); - fli.Friend = new UUID((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); - } - } + InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, + user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, + user.HomeLocation.Z, + user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, + user.LastLogin, user.UserInventoryURI, user.UserAssetURI, + user.CanDoMask, user.WantDoMask, + user.AboutText, user.FirstLifeAboutText, user.Image, + user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID, + user.GodLevel, user.UserFlags, user.CustomType, user.Partner); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error adding new profile, error: " + e.Message); } - - return Lfli; - } - - #endregion - - /// - /// STUB ! Update current region - /// - /// avatar uuid - /// region uuid - /// region handle - override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle) - { - //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); } /// - /// + /// update a user profile /// - /// - /// + /// the profile to update /// - override public List GeneratePickerResults(UUID queryID, string query) + override public bool UpdateUserProfile(UserProfileData user) { - List returnlist = new List(); - string[] querysplit; - querysplit = query.Split(' '); - if (querysplit.Length == 2) + using (AutoClosingSqlCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " + + "username = @username, " + + "lastname = @lastname," + + "passwordHash = @passwordHash," + + "passwordSalt = @passwordSalt," + + "homeRegion = @homeRegion," + + "homeLocationX = @homeLocationX," + + "homeLocationY = @homeLocationY," + + "homeLocationZ = @homeLocationZ," + + "homeLookAtX = @homeLookAtX," + + "homeLookAtY = @homeLookAtY," + + "homeLookAtZ = @homeLookAtZ," + + "created = @created," + + "lastLogin = @lastLogin," + + "userInventoryURI = @userInventoryURI," + + "userAssetURI = @userAssetURI," + + "profileCanDoMask = @profileCanDoMask," + + "profileWantDoMask = @profileWantDoMask," + + "profileAboutText = @profileAboutText," + + "profileFirstText = @profileFirstText," + + "profileImage = @profileImage," + + "profileFirstImage = @profileFirstImage, " + + "webLoginKey = @webLoginKey, " + + "homeRegionID = @homeRegionID, " + + "userFlags = @userFlags, " + + "godLevel = @godLevel, " + + "customType = @customType, " + + "partner = @partner where " + + "UUID = @keyUUUID;")) { - try - { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - param["second"] = querysplit[1]; + command.Parameters.Add(database.CreateParameter("uuid", user.ID)); + command.Parameters.Add(database.CreateParameter("username", user.FirstName)); + command.Parameters.Add(database.CreateParameter("lastname", user.SurName)); + command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash)); + command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt)); + command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion)); + command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X)); + command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y)); + command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z)); + command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X)); + command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y)); + command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z)); + command.Parameters.Add(database.CreateParameter("created", user.Created)); + command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin)); + command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI)); + command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI)); + command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask)); + command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask)); + command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText)); + command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText)); + command.Parameters.Add(database.CreateParameter("profileImage", user.Image)); + command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage)); + command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey)); + // + command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID)); + command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags)); + command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel)); + command.Parameters.Add(database.CreateParameter("customType", user.CustomType)); + command.Parameters.Add(database.CreateParameter("partner", user.Partner)); + command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID)); - using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - } - else if (querysplit.Length == 1) - { try { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - - using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @first", param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - } + int affected = command.ExecuteNonQuery(); + return (affected != 0); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating profile, error: " + e.Message); } } - return returnlist; + return false; } - /// - /// See IUserDataPlugin - /// - /// - /// - override public UserProfileData GetUserByUUID(UUID uuid) - { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); + #endregion - using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - return database.readUserRow(reader); - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } + #region Agent table methods /// /// Returns a user session searching by name @@ -469,258 +394,182 @@ namespace OpenSim.Data.MSSQL /// The users session override public UserAgentData GetAgentByUUID(UUID uuid) { - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid")) { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - using (IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + try { - return database.readAgentRow(reader); + using (SqlDataReader reader = command.ExecuteReader()) + { + return readAgentRow(reader); + } + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating agentdata by UUID, error: " + e.Message); + return null; } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; } } /// - /// Store a weblogin key - /// - /// The agent UUID - /// the WebLogin Key - /// unused ? - override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey) - { - UserProfileData user = GetUserByUUID(AgentID); - user.WebLoginKey = WebLoginKey; - UpdateUserProfile(user); - - } - /// - /// Creates a new users profile + /// Creates a new agent /// - /// The user profile to create - override public void AddNewUserProfile(UserProfileData user) + /// The agent to create + override public void AddNewUserAgent(UserAgentData agent) { try { - InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, - user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, - user.HomeLocation.Z, - user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, - user.LastLogin, user.UserInventoryURI, user.UserAssetURI, - user.CanDoMask, user.WantDoMask, - user.AboutText, user.FirstLifeAboutText, user.Image, - user.FirstLifeImage, user.WebLoginKey); + InsertUpdateAgentRow(agent); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error adding new agentdata, error: " + e.Message); } } + #endregion + + #region User Friends List Data + /// - /// Creates a new user and inserts it into the database + /// Add a new friend in the friendlist /// - /// User ID - /// First part of the login - /// Second part of the login - /// A salted hash of the users password - /// The salt used for the password hash - /// A regionHandle of the users home region - /// Home region position vector - /// Home region position vector - /// Home region position vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Account created (unix timestamp) - /// Last login (unix timestamp) - /// Users inventory URI - /// Users asset URI - /// I can do mask - /// I want to do mask - /// Profile text - /// Firstlife text - /// UUID for profile image - /// UUID for firstlife image - /// Success? - private bool InsertUserRow(UUID uuid, string username, string lastname, string passwordHash, - string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, - float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, - string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, - string aboutText, string firstText, - UUID profileImage, UUID firstImage, UUID webLoginKey) - { - string sql = "INSERT INTO "+m_usersTableName; - sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], "; - sql += - "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], "; - sql += - "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], "; - sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey]) VALUES "; + /// UUID of the friendlist owner + /// Friend's UUID + /// Permission flag + override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms) + { + int dtvalue = Util.UnixTimeSinceEpoch(); - sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; - sql += - "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; - sql += - "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; - sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey);"; - - Dictionary parameters = new Dictionary(); - parameters["UUID"] = uuid.ToString(); - parameters["username"] = username.ToString(); - parameters["lastname"] = lastname.ToString(); - parameters["passwordHash"] = passwordHash.ToString(); - parameters["passwordSalt"] = passwordSalt.ToString(); - parameters["homeRegion"] = homeRegion.ToString(); - parameters["homeLocationX"] = homeLocX.ToString(); - parameters["homeLocationY"] = homeLocY.ToString(); - parameters["homeLocationZ"] = homeLocZ.ToString(); - parameters["homeLookAtX"] = homeLookAtX.ToString(); - parameters["homeLookAtY"] = homeLookAtY.ToString(); - parameters["homeLookAtZ"] = homeLookAtZ.ToString(); - parameters["created"] = created.ToString(); - parameters["lastLogin"] = lastlogin.ToString(); - parameters["userInventoryURI"] = String.Empty; - parameters["userAssetURI"] = String.Empty; - parameters["profileCanDoMask"] = "0"; - parameters["profileWantDoMask"] = "0"; - parameters["profileAboutText"] = aboutText; - parameters["profileFirstText"] = firstText; - parameters["profileImage"] = profileImage.ToString(); - parameters["profileFirstImage"] = firstImage.ToString(); - parameters["webLoginKey"] = UUID.Random().ToString(); + using (AutoClosingSqlCommand command = database.Query( + "INSERT INTO " + m_userFriendsTableName + " " + + "(ownerID,friendID,friendPerms,datetimestamp) " + + "VALUES " + + "(@ownerID,@friendID,@friendPerms,@datetimestamp)")) + { + command.Parameters.Add(database.CreateParameter("ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("friendID", friend)); + command.Parameters.Add(database.CreateParameter("friendPerms", perms)); + command.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue)); + command.ExecuteNonQuery(); + try + { + command.CommandText = string.Format("INSERT INTO {0} (ownerID,friendID,friendPerms,datetimestamp) VALUES (@friendID,@ownerID,@friendPerms,@datetimestamp)", + m_userFriendsTableName); - try - { - using (IDbCommand result = database.Query(sql, parameters)) + command.ExecuteNonQuery(); + } + catch (Exception e) { - return (result.ExecuteNonQuery() == 1); + m_log.Error("[USER DB] Error adding new userfriend, error: " + e.Message); + return; } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return false; - } - } /// - /// Creates a new agent + /// Remove an friend from the friendlist /// - /// The agent to create - override public void AddNewUserAgent(UserAgentData agent) + /// UUID of the friendlist owner + /// UUID of the not-so-friendly user to remove from the list + override public void RemoveUserFriend(UUID friendlistowner, UUID friend) { - try + using (AutoClosingSqlCommand command = database.Query("delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID")) { - database.insertAgentRow(agent); + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("@friendID", friend)); + command.ExecuteNonQuery(); + + command.CommandText = "delete from " + m_userFriendsTableName + + " where ownerID = @friendID and friendID = @ownerID"; + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error removing userfriend, error: " + e.Message); + } } - catch (Exception e) + } + + /// + /// Update friendlist permission flag for a friend + /// + /// UUID of the friendlist owner + /// UUID of the friend + /// new permission flag + override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) + { + using (AutoClosingSqlCommand command = database.Query( + "update " + m_userFriendsTableName + + " SET friendPerms = @friendPerms " + + "where ownerID = @ownerID and friendID = @friendID")) { - m_log.Error(e.ToString()); + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("@friendID", friend)); + command.Parameters.Add(database.CreateParameter("@friendPerms", perms)); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); + } } } /// - /// update a user profile + /// Get (fetch?) the user's friendlist /// - /// the profile to update - /// - override public bool UpdateUserProfile(UserProfileData user) + /// UUID of the friendlist owner + /// Friendlist list + override public List GetUserFriendList(UUID friendlistowner) { - using (IDbCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " + - "username = @username, " + - "lastname = @lastname," + - "passwordHash = @passwordHash," + - "passwordSalt = @passwordSalt," + - "homeRegion = @homeRegion," + - "homeLocationX = @homeLocationX," + - "homeLocationY = @homeLocationY," + - "homeLocationZ = @homeLocationZ," + - "homeLookAtX = @homeLookAtX," + - "homeLookAtY = @homeLookAtY," + - "homeLookAtZ = @homeLookAtZ," + - "created = @created," + - "lastLogin = @lastLogin," + - "userInventoryURI = @userInventoryURI," + - "userAssetURI = @userAssetURI," + - "profileCanDoMask = @profileCanDoMask," + - "profileWantDoMask = @profileWantDoMask," + - "profileAboutText = @profileAboutText," + - "profileFirstText = @profileFirstText," + - "profileImage = @profileImage," + - "profileFirstImage = @profileFirstImage, " + - "webLoginKey = @webLoginKey where " + - "UUID = @keyUUUID;")) - { - SqlParameter param1 = new SqlParameter("@uuid", user.ID.ToString()); - SqlParameter param2 = new SqlParameter("@username", user.FirstName); - SqlParameter param3 = new SqlParameter("@lastname", user.SurName); - SqlParameter param4 = new SqlParameter("@passwordHash", user.PasswordHash); - SqlParameter param5 = new SqlParameter("@passwordSalt", user.PasswordSalt); - SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.HomeRegion)); - SqlParameter param7 = new SqlParameter("@homeLocationX", user.HomeLocation.X); - SqlParameter param8 = new SqlParameter("@homeLocationY", user.HomeLocation.Y); - SqlParameter param9 = new SqlParameter("@homeLocationZ", user.HomeLocation.Y); - SqlParameter param10 = new SqlParameter("@homeLookAtX", user.HomeLookAt.X); - SqlParameter param11 = new SqlParameter("@homeLookAtY", user.HomeLookAt.Y); - SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.HomeLookAt.Z); - SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.Created)); - SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.LastLogin)); - SqlParameter param15 = new SqlParameter("@userInventoryURI", user.UserInventoryURI); - SqlParameter param16 = new SqlParameter("@userAssetURI", user.UserAssetURI); - SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.CanDoMask)); - SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.WantDoMask)); - SqlParameter param19 = new SqlParameter("@profileAboutText", user.AboutText); - SqlParameter param20 = new SqlParameter("@profileFirstText", user.FirstLifeAboutText); - SqlParameter param21 = new SqlParameter("@profileImage", user.Image.ToString()); - SqlParameter param22 = new SqlParameter("@profileFirstImage", user.FirstLifeImage.ToString()); - SqlParameter param23 = new SqlParameter("@keyUUUID", user.ID.ToString()); - SqlParameter param24 = new SqlParameter("@webLoginKey", user.WebLoginKey.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - command.Parameters.Add(param4); - command.Parameters.Add(param5); - command.Parameters.Add(param6); - command.Parameters.Add(param7); - command.Parameters.Add(param8); - command.Parameters.Add(param9); - command.Parameters.Add(param10); - command.Parameters.Add(param11); - command.Parameters.Add(param12); - command.Parameters.Add(param13); - command.Parameters.Add(param14); - command.Parameters.Add(param15); - command.Parameters.Add(param16); - command.Parameters.Add(param17); - command.Parameters.Add(param18); - command.Parameters.Add(param19); - command.Parameters.Add(param20); - command.Parameters.Add(param21); - command.Parameters.Add(param22); - command.Parameters.Add(param23); - command.Parameters.Add(param24); + List friendList = new List(); + + //Left Join userfriends to itself + using (AutoClosingSqlCommand command = database.Query( + "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + + " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID")) + { + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + try { - int affected = command.ExecuteNonQuery(); - return (affected != 0); + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + FriendListItem fli = new FriendListItem(); + fli.FriendListOwner = new UUID((string)reader["ownerID"]); + fli.Friend = new UUID((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"]); + + friendList.Add(fli); + } + } } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); } } - return false; + + return friendList; } + #endregion + + #region Money functions (not used) + /// /// Performs a money transfer request between two accounts /// @@ -746,33 +595,327 @@ namespace OpenSim.Data.MSSQL return false; } - /// Appearance + #endregion + + #region Appearance methods + + /// + /// Gets the user appearance. + /// + /// The user. + /// /// TODO: stubs for now to get us to a compiling state gently override public AvatarAppearance GetUserAppearance(UUID user) { -// return new AvatarAppearance(); try { - Dictionary param = new Dictionary(); - param["@UUID"] = user.ToString(); + AvatarAppearance appearance = new AvatarAppearance(); - using (IDbCommand result = - database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID", param)) - using (IDataReader reader = result.ExecuteReader()) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID")) + { + command.Parameters.Add(database.CreateParameter("@UUID", user)); + using (IDataReader reader = command.ExecuteReader()) { - AvatarAppearance item = null; if (reader.Read()) - item = readUserAppearance(reader); - return item; + appearance = readUserAppearance(reader); } + } + + appearance.SetAttachments(GetUserAttachments(user)); + + return appearance; } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); } return null; } + + /// + /// Update a user appearence into database + /// + /// the used UUID + /// the appearence + override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance) + { + string sql = String.Empty; + sql += "DELETE FROM avatarappearance WHERE owner=@owner "; + sql += "INSERT INTO avatarappearance "; + sql += "(owner, serial, visual_params, texture, avatar_height, "; + sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, "; + sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, "; + sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, "; + sql += "skirt_item, skirt_asset) values ("; + sql += "@owner, @serial, @visual_params, @texture, @avatar_height, "; + sql += "@body_item, @body_asset, @skin_item, @skin_asset, @hair_item, @hair_asset, @eyes_item, @eyes_asset, "; + sql += "@shirt_item, @shirt_asset, @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, @socks_asset, "; + sql += "@jacket_item, @jacket_asset, @gloves_item, @gloves_asset, @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, "; + sql += "@skirt_item, @skirt_asset)"; + + using (AutoClosingSqlCommand cmd = database.Query(sql)) + { + cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner)); + cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial)); + cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams)); + cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.ToBytes())); + cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight)); + cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem)); + cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset)); + cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem)); + cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset)); + cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem)); + cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset)); + cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem)); + cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset)); + cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem)); + cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset)); + cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem)); + cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset)); + cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem)); + cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset)); + cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem)); + cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset)); + cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem)); + cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset)); + cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem)); + cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset)); + cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem)); + cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset)); + cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem)); + cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset)); + cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem)); + cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset)); + + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating user appearance, error: " + e.Message); + } + } + + UpdateUserAttachments(user, appearance.GetAttachments()); + } + + #endregion + + #region Attachment methods + + /// + /// Gets all attachment of a agent. + /// + /// agent ID. + /// + public Hashtable GetUserAttachments(UUID agentID) + { + Hashtable returnTable = new Hashtable(); + using (AutoClosingSqlCommand command = database.Query("select attachpoint, item, asset from avatarattachments where UUID = @uuid", database.CreateParameter("@uuid", agentID))) + { + using (SqlDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + int attachpoint = Convert.ToInt32(reader["attachpoint"]); + if (returnTable.ContainsKey(attachpoint)) + continue; + Hashtable item = new Hashtable(); + item.Add("item", reader["item"].ToString()); + item.Add("asset", reader["asset"].ToString()); + + returnTable.Add(attachpoint, item); + } + } + } + return returnTable; + } + + /// + /// Updates all attachments of the agent. + /// + /// agentID. + /// data with all items on attachmentpoints + public void UpdateUserAttachments(UUID agentID, Hashtable data) + { + string sql = "delete from avatarattachments where UUID = @uuid"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("uuid", agentID)); + command.ExecuteNonQuery(); + } + if (data == null) + return; + + sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (@uuid, @attachpoint, @item, @asset)"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + bool firstTime = true; + foreach (DictionaryEntry e in data) + { + int attachpoint = Convert.ToInt32(e.Key); + + Hashtable item = (Hashtable)e.Value; + + if (firstTime) + { + command.Parameters.Add(database.CreateParameter("@uuid", agentID)); + command.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint)); + command.Parameters.Add(database.CreateParameter("@item", item["item"].ToString())); + command.Parameters.Add(database.CreateParameter("@asset", item["asset"].ToString())); + firstTime = false; + } + command.Parameters["@uuid"].Value = agentID.ToString(); + command.Parameters["@attachpoint"].Value = attachpoint; + command.Parameters["@item"].Value = item["item"].ToString(); + command.Parameters["@asset"].Value = item["asset"].ToString(); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception ex) + { + m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message); + } + } + } + } + + /// + /// Resets all attachments of a agent in the database. + /// + /// agentID. + override public void ResetAttachments(UUID agentID) + { + using (AutoClosingSqlCommand command = database.Query("update avatarattachments set asset = '00000000-0000-0000-0000-000000000000' where UUID = @uuid")) + { + command.Parameters.Add(database.CreateParameter("uuid", agentID)); + command.ExecuteNonQuery(); + } + } + + #endregion + + #region Other public methods + + /// + /// STUB ! Update current region + /// + /// avatar uuid + /// region uuid + /// region handle + override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle) + { + //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); + } + + /// + /// + /// + /// + /// + /// + override public List GeneratePickerResults(UUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + try + { + using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first AND lastname LIKE @second")) + { + //Add wildcard to the search + command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%")); + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["lastname"]; + returnlist.Add(user); + } + } + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + else if (querysplit.Length == 1) + { + try + { + using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first OR lastname LIKE @first")) + { + command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["lastname"]; + returnlist.Add(user); + } + } + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + return returnlist; + } + + /// + /// Store a weblogin key + /// + /// The agent UUID + /// the WebLogin Key + /// unused ? + override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey) + { + UserProfileData user = GetUserByUUID(AgentID); + user.WebLoginKey = WebLoginKey; + UpdateUserProfile(user); + + } + + /// + /// Database provider name + /// + /// Provider name + override public string Name + { + get { return "MSSQL Userdata Interface"; } + } + + /// + /// Database provider version + /// + /// provider version + override public string Version + { + get { return database.getVersion(); } + } + + #endregion + + #region Private functions + /// /// Reads a one item from an SQL result /// @@ -827,99 +970,254 @@ namespace OpenSim.Data.MSSQL } /// - /// Update a user appearence into database + /// Insert/Update a agent row in the DB. /// - /// the used UUID - /// the appearence - override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance) + /// agentdata. + private void InsertUpdateAgentRow(UserAgentData agentdata) { - string sql = String.Empty; - sql += "DELETE FROM avatarappearance WHERE owner=@owner "; - sql += "INSERT INTO avatarappearance "; - sql += "(owner, serial, visual_params, texture, avatar_height, "; - sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, "; - sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, "; - sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, "; - sql += "skirt_item, skirt_asset) values ("; - sql += "@owner, @serial, @visual_params, @texture, @avatar_height, "; - sql += "@body_item, @body_asset, @skin_item, @skin_asset, @hair_item, @hair_asset, @eyes_item, @eyes_asset, "; - sql += "@shirt_item, @shirt_asset, @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, @socks_asset, "; - sql += "@jacket_item, @jacket_asset, @gloves_item, @gloves_asset, @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, "; - sql += "@skirt_item, @skirt_asset)"; - - using (AutoClosingSqlCommand cmd = database.Query(sql)) + string sql = @" + +IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID) + BEGIN + UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos + WHERE UUID = @UUID + END +ELSE + BEGIN + INSERT INTO + agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES + (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos) + END +"; + + using (AutoClosingSqlCommand command = database.Query(sql)) { - cmd.Parameters.AddWithValue("@owner", appearance.Owner.ToString()); - cmd.Parameters.AddWithValue("@serial", appearance.Serial); - cmd.Parameters.AddWithValue("@visual_params", appearance.VisualParams); - cmd.Parameters.AddWithValue("@texture", appearance.Texture.ToBytes()); - cmd.Parameters.AddWithValue("@avatar_height", appearance.AvatarHeight); - cmd.Parameters.AddWithValue("@body_item", appearance.BodyItem.ToString()); - cmd.Parameters.AddWithValue("@body_asset", appearance.BodyAsset.ToString()); - cmd.Parameters.AddWithValue("@skin_item", appearance.SkinItem.ToString()); - cmd.Parameters.AddWithValue("@skin_asset", appearance.SkinAsset.ToString()); - cmd.Parameters.AddWithValue("@hair_item", appearance.HairItem.ToString()); - cmd.Parameters.AddWithValue("@hair_asset", appearance.HairAsset.ToString()); - cmd.Parameters.AddWithValue("@eyes_item", appearance.EyesItem.ToString()); - cmd.Parameters.AddWithValue("@eyes_asset", appearance.EyesAsset.ToString()); - cmd.Parameters.AddWithValue("@shirt_item", appearance.ShirtItem.ToString()); - cmd.Parameters.AddWithValue("@shirt_asset", appearance.ShirtAsset.ToString()); - cmd.Parameters.AddWithValue("@pants_item", appearance.PantsItem.ToString()); - cmd.Parameters.AddWithValue("@pants_asset", appearance.PantsAsset.ToString()); - cmd.Parameters.AddWithValue("@shoes_item", appearance.ShoesItem.ToString()); - cmd.Parameters.AddWithValue("@shoes_asset", appearance.ShoesAsset.ToString()); - cmd.Parameters.AddWithValue("@socks_item", appearance.SocksItem.ToString()); - cmd.Parameters.AddWithValue("@socks_asset", appearance.SocksAsset.ToString()); - cmd.Parameters.AddWithValue("@jacket_item", appearance.JacketItem.ToString()); - cmd.Parameters.AddWithValue("@jacket_asset", appearance.JacketAsset.ToString()); - cmd.Parameters.AddWithValue("@gloves_item", appearance.GlovesItem.ToString()); - cmd.Parameters.AddWithValue("@gloves_asset", appearance.GlovesAsset.ToString()); - cmd.Parameters.AddWithValue("@undershirt_item", appearance.UnderShirtItem.ToString()); - cmd.Parameters.AddWithValue("@undershirt_asset", appearance.UnderShirtAsset.ToString()); - cmd.Parameters.AddWithValue("@underpants_item", appearance.UnderPantsItem.ToString()); - cmd.Parameters.AddWithValue("@underpants_asset", appearance.UnderPantsAsset.ToString()); - cmd.Parameters.AddWithValue("@skirt_item", appearance.SkirtItem.ToString()); - cmd.Parameters.AddWithValue("@skirt_asset", appearance.SkirtAsset.ToString()); - + command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID)); + command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID)); + command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID)); + command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP)); + command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort)); + command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline)); + command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime)); + command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime)); + command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region)); + command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle)); + command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">")); + + command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable); try { - cmd.ExecuteNonQuery(); + if (command.ExecuteNonQuery() > 0) + { + command.Transaction.Commit(); + return; + } + + command.Transaction.Rollback(); + return; } catch (Exception e) { + command.Transaction.Rollback(); m_log.Error(e.ToString()); + return; } } + } /// - /// Database provider name + /// Reads an agent row from a database reader /// - /// Provider name - override public string Name + /// An active database reader + /// A user session agent + private UserAgentData readAgentRow(IDataReader reader) { - get {return "MSSQL Userdata Interface";} + UserAgentData retval = new UserAgentData(); + + if (reader.Read()) + { + // Agent IDs + retval.ProfileID = new UUID((string)reader["UUID"]); + retval.SessionID = new UUID((string)reader["sessionID"]); + retval.SecureSessionID = new UUID((string)reader["secureSessionID"]); + + // Agent Who? + retval.AgentIP = (string)reader["agentIP"]; + retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString()); + retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0; + + // Login/Logout times (UNIX Epoch) + retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString()); + retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); + + // Current position + retval.Region = (string)reader["currentRegion"]; + retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString()); + Vector3 tmp_v; + Vector3.TryParse((string)reader["currentPos"], out tmp_v); + retval.Position = tmp_v; + + } + else + { + return null; + } + return retval; } /// - /// Database provider version + /// Creates a new user and inserts it into the database /// - /// provider version - override public string Version + /// User ID + /// First part of the login + /// Second part of the login + /// A salted hash of the users password + /// The salt used for the password hash + /// A regionHandle of the users home region + /// Home region position vector + /// Home region position vector + /// Home region position vector + /// Home region 'look at' vector + /// Home region 'look at' vector + /// Home region 'look at' vector + /// Account created (unix timestamp) + /// Last login (unix timestamp) + /// Users inventory URI + /// Users asset URI + /// I can do mask + /// I want to do mask + /// Profile text + /// Firstlife text + /// UUID for profile image + /// UUID for firstlife image + /// web login key + /// homeregion UUID + /// has the user godlevel + /// unknown + /// unknown + /// UUID of partner + /// Success? + private void InsertUserRow(UUID uuid, string username, string lastname, string passwordHash, + string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, + float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, + string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, + string aboutText, string firstText, + UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID, + int godLevel, int userFlags, string customType, UUID partnerID) { - get {return database.getVersion();} + string sql = "INSERT INTO " + m_usersTableName; + sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], "; + sql += "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], "; + sql += "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], "; + sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], "; + sql += "[homeRegionID], [userFlags], [godLevel], [customType], [partner]) VALUES "; + + sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; + sql += "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; + sql += "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; + sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, "; + sql += "@homeRegionID, @userFlags, @godLevel, @customType, @partner)"; + + try + { + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("UUID", uuid)); + command.Parameters.Add(database.CreateParameter("username", username)); + command.Parameters.Add(database.CreateParameter("lastname", lastname)); + command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash)); + command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt)); + command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion)); + command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX)); + command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY)); + command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ)); + command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX)); + command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY)); + command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ)); + command.Parameters.Add(database.CreateParameter("created", created)); + command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin)); + command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI)); + command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI)); + command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask)); + command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask)); + command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText)); + command.Parameters.Add(database.CreateParameter("profileFirstText", firstText)); + command.Parameters.Add(database.CreateParameter("profileImage", profileImage)); + command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage)); + command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey)); + // + command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID)); + command.Parameters.Add(database.CreateParameter("userFlags", userFlags)); + command.Parameters.Add(database.CreateParameter("godLevel", godLevel)); + command.Parameters.Add(database.CreateParameter("customType", customType)); + command.Parameters.Add(database.CreateParameter("partner", partnerID)); + + + command.ExecuteNonQuery(); + return; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return; + } + } /// - /// Not implemented + /// Reads a user profile from an active data reader /// - /// - public void runQuery(string query) + /// An active database reader + /// A user profile + private static UserProfileData ReadUserRow(IDataReader reader) { - } + UserProfileData retval = new UserProfileData(); - override public void ResetAttachments(UUID userID) - { + if (reader.Read()) + { + retval.ID = new UUID((string)reader["UUID"]); + retval.FirstName = (string)reader["username"]; + retval.SurName = (string)reader["lastname"]; + + retval.PasswordHash = (string)reader["passwordHash"]; + retval.PasswordSalt = (string)reader["passwordSalt"]; + + retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString()); + retval.HomeLocation = new Vector3( + Convert.ToSingle(reader["homeLocationX"].ToString()), + Convert.ToSingle(reader["homeLocationY"].ToString()), + Convert.ToSingle(reader["homeLocationZ"].ToString())); + retval.HomeLookAt = new Vector3( + Convert.ToSingle(reader["homeLookAtX"].ToString()), + Convert.ToSingle(reader["homeLookAtY"].ToString()), + Convert.ToSingle(reader["homeLookAtZ"].ToString())); + + retval.Created = Convert.ToInt32(reader["created"].ToString()); + retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); + + retval.UserInventoryURI = (string)reader["userInventoryURI"]; + retval.UserAssetURI = (string)reader["userAssetURI"]; + + retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); + retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); + + retval.AboutText = (string)reader["profileAboutText"]; + retval.FirstLifeAboutText = (string)reader["profileFirstText"]; + + retval.Image = new UUID((string)reader["profileImage"]); + retval.FirstLifeImage = new UUID((string)reader["profileFirstImage"]); + retval.WebLoginKey = new UUID((string)reader["webLoginKey"]); + } + else + { + return null; + } + return retval; } + + #endregion } + } -- cgit v1.1