From bb171717ceaef37b022a135209c2e0bf031d21f9 Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sun, 21 Feb 2010 15:38:52 -0800 Subject: Deleted obsolete files in the Data layer. Compiles. --- OpenSim/Data/MSSQL/MSSQLUserData.cs | 1214 ----------------------------------- 1 file changed, 1214 deletions(-) delete mode 100644 OpenSim/Data/MSSQL/MSSQLUserData.cs (limited to 'OpenSim/Data/MSSQL/MSSQLUserData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs deleted file mode 100644 index 3ef1053..0000000 --- a/OpenSim/Data/MSSQL/MSSQLUserData.cs +++ /dev/null @@ -1,1214 +0,0 @@ -/* - * Copyright (c) Contributors, http://opensimulator.org/ - * See CONTRIBUTORS.TXT for a full list of copyright holders. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * * Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. - * * Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. - * * Neither the name of the OpenSimulator Project nor the - * names of its contributors may be used to endorse or promote products - * derived from this software without specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY - * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY - * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND - * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -using System; -using System.Collections; -using System.Collections.Generic; -using System.Data; -using System.Data.SqlClient; -using System.Reflection; -using log4net; -using OpenMetaverse; -using OpenSim.Framework; - -namespace OpenSim.Data.MSSQL -{ - /// - /// A database interface class to a user profile storage system - /// - public class MSSQLUserData : UserDataBase - { - private const string _migrationStore = "UserStore"; - - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - /// - /// Database manager for MSSQL - /// - public MSSQLManager database; - - private const string m_agentsTableName = "agents"; - private const string m_usersTableName = "users"; - private const string m_userFriendsTableName = "userfriends"; - - // [Obsolete("Cannot be default-initialized!")] - override public void Initialise() - { - m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException(Name); - } - - /// - /// Loads and initialises the MSSQL storage plugin - /// - /// connectionstring - /// use mssql_connection.ini - override public void Initialise(string connect) - { - if (!string.IsNullOrEmpty(connect)) - { - database = new MSSQLManager(connect); - } - else - { - 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"); - - database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); - } - - //Check migration on DB - database.CheckMigration(_migrationStore); - } - - /// - /// Releases unmanaged and - optionally - managed resources - /// - override public void Dispose() { } - - #region User table methods - - /// - /// 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) - { - string sql = string.Format(@"SELECT * FROM {0} - WHERE username = @first AND lastname = @second", m_usersTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("first", user)); - command.Parameters.Add(database.CreateParameter("second", last)); - try - { - using (SqlDataReader reader = command.ExecuteReader()) - { - return ReadUserRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message); - return null; - } - } - } - - /// - /// See IUserDataPlugin - /// - /// - /// - override public UserProfileData GetUserByUUID(UUID uuid) - { - string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("uuid", uuid)); - try - { - using (SqlDataReader reader = command.ExecuteReader()) - { - return ReadUserRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message); - return null; - } - } - } - - - /// - /// Creates a new users profile - /// - /// The user profile to create - override public void AddNewUserProfile(UserProfileData user) - { - try - { - InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, 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.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message); - } - } - - /// - /// update a user profile - /// - /// the profile to update - /// - override public bool UpdateUserProfile(UserProfileData user) - { - string sql = string.Format(@"UPDATE {0} - SET UUID = @uuid, - username = @username, - lastname = @lastname, - email = @email, - 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;",m_usersTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - 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("email", user.Email)); - 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)); - - try - { - int affected = command.ExecuteNonQuery(); - return (affected != 0); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message); - } - } - return false; - } - - #endregion - - #region Agent table methods - - /// - /// Returns a user session searching by name - /// - /// The account name - /// The users session - override public UserAgentData GetAgentByName(string name) - { - return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); - } - - /// - /// Returns a user session by account name - /// - /// First part of the users account name - /// Second part of the users account name - /// The users session - override public UserAgentData GetAgentByName(string user, string last) - { - UserProfileData profile = GetUserByName(user, last); - return GetAgentByUUID(profile.ID); - } - - /// - /// Returns an agent session by account UUID - /// - /// The accounts UUID - /// The users session - override public UserAgentData GetAgentByUUID(UUID uuid) - { - string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("uuid", uuid)); - try - { - using (SqlDataReader reader = command.ExecuteReader()) - { - return readAgentRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message); - return null; - } - } - } - - /// - /// Creates a new agent - /// - /// The agent to create - override public void AddNewUserAgent(UserAgentData agent) - { - try - { - InsertUpdateAgentRow(agent); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message); - } - } - - #endregion - - #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(); - string sql = string.Format(@"INSERT INTO {0} - (ownerID,friendID,friendPerms,datetimestamp) - VALUES - (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - 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 - { - sql = string.Format(@"INSERT INTO {0} - (ownerID,friendID,friendPerms,datetimestamp) - VALUES - (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName); - command.CommandText = sql; - command.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message); - return; - } - } - } - - /// - /// Remove an friend from the friendlist - /// - /// 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) - { - string sql = string.Format(@"DELETE from {0} - WHERE ownerID = @ownerID - AND friendID = @friendID", m_userFriendsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); - command.Parameters.Add(database.CreateParameter("@friendID", friend)); - command.ExecuteNonQuery(); - sql = string.Format(@"DELETE from {0} - WHERE ownerID = @friendID - AND friendID = @ownerID", m_userFriendsTableName); - command.CommandText = sql; - try - { - command.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message); - } - } - } - - /// - /// 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) - { - string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms - WHERE ownerID = @ownerID - AND friendID = @friendID", m_userFriendsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - 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.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); - } - } - } - - /// - /// Get (fetch?) the user's friendlist - /// - /// UUID of the friendlist owner - /// Friendlist list - override public List GetUserFriendList(UUID friendlistowner) - { - List friendList = new List(); - - //Left Join userfriends to itself - string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms - FROM {0} as a, {0} as b - WHERE a.ownerID = @ownerID - AND b.ownerID = a.friendID - AND b.friendID = a.ownerID", m_userFriendsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); - try - { - using (SqlDataReader reader = command.ExecuteReader()) - { - while (reader.Read()) - { - FriendListItem fli = new FriendListItem(); - fli.FriendListOwner = new UUID((Guid)reader["ownerID"]); - fli.Friend = new UUID((Guid)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.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); - } - } - return friendList; - } - - override public Dictionary GetFriendRegionInfos (List uuids) - { - Dictionary infos = new Dictionary(); - try - { - foreach (UUID uuid in uuids) - { - string sql = string.Format(@"SELECT agentOnline,currentHandle - FROM {0} WHERE UUID = @uuid", m_agentsTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("@uuid", uuid)); - using (SqlDataReader reader = command.ExecuteReader()) - { - while (reader.Read()) - { - FriendRegionInfo fri = new FriendRegionInfo(); - fri.isOnline = (byte)reader["agentOnline"] != 0; - fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); - - infos[uuid] = fri; - } - } - } - } - } - catch (Exception e) - { - m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e); - } - - return infos; - } - #endregion - - #region Money functions (not used) - - /// - /// Performs a money transfer request between two accounts - /// - /// The senders account ID - /// The receivers account ID - /// The amount to transfer - /// false - override public bool MoneyTransferRequest(UUID from, UUID to, uint amount) - { - return false; - } - - /// - /// Performs an inventory transfer request between two accounts - /// - /// TODO: Move to inventory server - /// The senders account ID - /// The receivers account ID - /// The item to transfer - /// false - override public bool InventoryTransferRequest(UUID from, UUID to, UUID item) - { - return false; - } - - #endregion - - #region Appearance methods - - /// - /// Gets the user appearance. - /// - /// The user. - /// - override public AvatarAppearance GetUserAppearance(UUID user) - { - try - { - AvatarAppearance appearance = new AvatarAppearance(); - string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID"; - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("@UUID", user)); - using (SqlDataReader reader = command.ExecuteReader()) - { - if (reader.Read()) - appearance = readUserAppearance(reader); - else - { - m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString()); - return null; - } - - } - } - - appearance.SetAttachments(GetUserAttachments(user)); - - return appearance; - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", 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 = @"DELETE FROM avatarappearance WHERE owner=@owner; - INSERT INTO avatarappearance - (owner, serial, visual_params, texture, avatar_height, - body_item, body_asset, skin_item, skin_asset, hair_item, - hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset, - pants_item, pants_asset, shoes_item, shoes_asset, socks_item, - socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset, - undershirt_item, undershirt_asset, underpants_item, underpants_asset, - skirt_item, skirt_asset) - VALUES - (@owner, @serial, @visual_params, @texture, @avatar_height, - @body_item, @body_asset, @skin_item, @skin_asset, @hair_item, - @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset, - @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, - @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset, - @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, - @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.GetBytes())); - 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.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", 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(); - string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid"; - using (AutoClosingSqlCommand command = database.Query(sql, 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", new UUID(item["item"].ToString()))); - command.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString()))); - firstTime = false; - } - command.Parameters["@uuid"].Value = agentID.Guid; //.ToString(); - command.Parameters["@attachpoint"].Value = attachpoint; - command.Parameters["@item"].Value = new Guid(item["item"].ToString()); - command.Parameters["@asset"].Value = new Guid(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) - { - string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid"; - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("uuid", agentID)); - command.ExecuteNonQuery(); - } - } - - override public void LogoutUsers(UUID regionID) - { - } - - #endregion - - #region Other public methods - - /// - /// - /// - /// - /// - /// - override public List GeneratePickerResults(UUID queryID, string query) - { - List returnlist = new List(); - string[] querysplit = query.Split(' '); - if (querysplit.Length == 2) - { - try - { - string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} - WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - //Add wildcard to the search - command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); - command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%")); - using (SqlDataReader reader = command.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((Guid)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 - { - string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} - WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName); - using (AutoClosingSqlCommand command = database.Query(sql)) - { - command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); - - using (SqlDataReader reader = command.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((Guid)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 - /// - /// The SQL Result - /// the item read - private static AvatarAppearance readUserAppearance(SqlDataReader reader) - { - try - { - AvatarAppearance appearance = new AvatarAppearance(); - - appearance.Owner = new UUID((Guid)reader["owner"]); - appearance.Serial = Convert.ToInt32(reader["serial"]); - appearance.VisualParams = (byte[])reader["visual_params"]; - appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length); - appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]); - appearance.BodyItem = new UUID((Guid)reader["body_item"]); - appearance.BodyAsset = new UUID((Guid)reader["body_asset"]); - appearance.SkinItem = new UUID((Guid)reader["skin_item"]); - appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]); - appearance.HairItem = new UUID((Guid)reader["hair_item"]); - appearance.HairAsset = new UUID((Guid)reader["hair_asset"]); - appearance.EyesItem = new UUID((Guid)reader["eyes_item"]); - appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]); - appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]); - appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]); - appearance.PantsItem = new UUID((Guid)reader["pants_item"]); - appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]); - appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]); - appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]); - appearance.SocksItem = new UUID((Guid)reader["socks_item"]); - appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]); - appearance.JacketItem = new UUID((Guid)reader["jacket_item"]); - appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]); - appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]); - appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]); - appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]); - appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]); - appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]); - appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]); - appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]); - appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]); - - return appearance; - } - catch (SqlException e) - { - m_log.Error(e.ToString()); - } - - return null; - } - - /// - /// Insert/Update a agent row in the DB. - /// - /// agentdata. - private void InsertUpdateAgentRow(UserAgentData agentdata) - { - 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)) - { - 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 - { - if (command.ExecuteNonQuery() > 0) - { - command.Transaction.Commit(); - return; - } - - command.Transaction.Rollback(); - return; - } - catch (Exception e) - { - command.Transaction.Rollback(); - m_log.Error(e.ToString()); - return; - } - } - - } - - /// - /// Reads an agent row from a database reader - /// - /// An active database reader - /// A user session agent - private UserAgentData readAgentRow(SqlDataReader reader) - { - UserAgentData retval = new UserAgentData(); - - if (reader.Read()) - { - // Agent IDs - retval.ProfileID = new UUID((Guid)reader["UUID"]); - retval.SessionID = new UUID((Guid)reader["sessionID"]); - retval.SecureSessionID = new UUID((Guid)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 = new UUID((Guid)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; - } - - /// - /// Creates a new user and inserts it into the database - /// - /// User ID - /// First part of the login - /// Second part of the login - /// Email of person - /// 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 email, 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) - { - string sql = string.Format(@"INSERT INTO {0} - ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt], - [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], - [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI], - [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], - [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], - [homeRegionID], [userFlags], [godLevel], [customType], [partner]) - VALUES - (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt, - @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, - @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI, - @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, - @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, - @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName); - - 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("email", email)); - 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; - } - } - - /// - /// Reads a user profile from an active data reader - /// - /// An active database reader - /// A user profile - private static UserProfileData ReadUserRow(SqlDataReader reader) - { - UserProfileData retval = new UserProfileData(); - - if (reader.Read()) - { - retval.ID = new UUID((Guid)reader["UUID"]); - retval.FirstName = (string)reader["username"]; - retval.SurName = (string)reader["lastname"]; - if (reader.IsDBNull(reader.GetOrdinal("email"))) - retval.Email = ""; - else - retval.Email = (string)reader["email"]; - - 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())); - - if (reader.IsDBNull(reader.GetOrdinal("homeRegionID"))) - retval.HomeRegionID = UUID.Zero; - else - retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]); - - retval.Created = Convert.ToInt32(reader["created"].ToString()); - retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); - - if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI"))) - retval.UserInventoryURI = ""; - else - retval.UserInventoryURI = (string)reader["userInventoryURI"]; - - if (reader.IsDBNull(reader.GetOrdinal("userAssetURI"))) - retval.UserAssetURI = ""; - else - retval.UserAssetURI = (string)reader["userAssetURI"]; - - retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); - retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); - - - if (reader.IsDBNull(reader.GetOrdinal("profileAboutText"))) - retval.AboutText = ""; - else - retval.AboutText = (string)reader["profileAboutText"]; - - if (reader.IsDBNull(reader.GetOrdinal("profileFirstText"))) - retval.FirstLifeAboutText = ""; - else - retval.FirstLifeAboutText = (string)reader["profileFirstText"]; - - if (reader.IsDBNull(reader.GetOrdinal("profileImage"))) - retval.Image = UUID.Zero; - else - retval.Image = new UUID((Guid)reader["profileImage"]); - - if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage"))) - retval.Image = UUID.Zero; - else - retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]); - - if (reader.IsDBNull(reader.GetOrdinal("webLoginKey"))) - retval.WebLoginKey = UUID.Zero; - else - retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]); - - retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString()); - retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString()); - if (reader.IsDBNull(reader.GetOrdinal("customType"))) - retval.CustomType = ""; - else - retval.CustomType = reader["customType"].ToString(); - - if (reader.IsDBNull(reader.GetOrdinal("partner"))) - retval.Partner = UUID.Zero; - else - retval.Partner = new UUID((Guid)reader["partner"]); - } - else - { - return null; - } - return retval; - } - #endregion - } - -} -- cgit v1.1 From 2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf Mon Sep 17 00:00:00 2001 From: StrawberryFride Date: Wed, 24 Feb 2010 16:42:39 +0000 Subject: MSSQL Additions for Presence Refactor branch. Most functionality tested and works, some outstanding issues around login location and border crossings on y axis. Signed-off-by: Melanie --- OpenSim/Data/MSSQL/MSSQLUserData.cs | 1238 +++++++++++++++++++++++++++++++++++ 1 file changed, 1238 insertions(+) create mode 100644 OpenSim/Data/MSSQL/MSSQLUserData.cs (limited to 'OpenSim/Data/MSSQL/MSSQLUserData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs new file mode 100644 index 0000000..6bdb559 --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs @@ -0,0 +1,1238 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using System.Data.SqlClient; +using System.Reflection; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; + +namespace OpenSim.Data.MSSQL +{ + /// + /// A database interface class to a user profile storage system + /// + public class MSSQLUserData : UserDataBase + { + private const string _migrationStore = "UserStore"; + + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// Database manager for MSSQL + /// + public MSSQLManager database; + private string m_connectionString; + + private const string m_agentsTableName = "agents"; + private const string m_usersTableName = "users"; + private const string m_userFriendsTableName = "userfriends"; + + // [Obsolete("Cannot be default-initialized!")] + override public void Initialise() + { + m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); + throw new PluginNotInitialisedException(Name); + } + + /// + /// Loads and initialises the MSSQL storage plugin + /// + /// connectionstring + /// use mssql_connection.ini + override public void Initialise(string connect) + { + m_connectionString = connect; + database = new MSSQLManager(connect); + + + //Check migration on DB + database.CheckMigration(_migrationStore); + } + + /// + /// Releases unmanaged and - optionally - managed resources + /// + override public void Dispose() { } + + #region User table methods + + /// + /// 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) + { + string sql = string.Format(@"SELECT * FROM {0} + WHERE username = @first AND lastname = @second", m_usersTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("first", user)); + cmd.Parameters.Add(database.CreateParameter("second", last)); + try + { + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + return ReadUserRow(reader); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message); + return null; + } + } + } + + /// + /// See IUserDataPlugin + /// + /// + /// + override public UserProfileData GetUserByUUID(UUID uuid) + { + string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("uuid", uuid)); + conn.Open(); + try + { + using (SqlDataReader reader = cmd.ExecuteReader()) + { + return ReadUserRow(reader); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message); + return null; + } + } + } + + + /// + /// Creates a new users profile + /// + /// The user profile to create + override public void AddNewUserProfile(UserProfileData user) + { + try + { + InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, 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.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message); + } + } + + /// + /// update a user profile + /// + /// the profile to update + /// + override public bool UpdateUserProfile(UserProfileData user) + { + string sql = string.Format(@"UPDATE {0} + SET UUID = @uuid, + username = @username, + lastname = @lastname, + email = @email, + 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;", m_usersTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand command = new SqlCommand(sql, conn)) + { + 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("email", user.Email)); + 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)); + conn.Open(); + try + { + int affected = command.ExecuteNonQuery(); + return (affected != 0); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message); + } + } + return false; + } + + #endregion + + #region Agent table methods + + /// + /// Returns a user session searching by name + /// + /// The account name + /// The users session + override public UserAgentData GetAgentByName(string name) + { + return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); + } + + /// + /// Returns a user session by account name + /// + /// First part of the users account name + /// Second part of the users account name + /// The users session + override public UserAgentData GetAgentByName(string user, string last) + { + UserProfileData profile = GetUserByName(user, last); + return GetAgentByUUID(profile.ID); + } + + /// + /// Returns an agent session by account UUID + /// + /// The accounts UUID + /// The users session + override public UserAgentData GetAgentByUUID(UUID uuid) + { + string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("uuid", uuid)); + conn.Open(); + try + { + using (SqlDataReader reader = cmd.ExecuteReader()) + { + return readAgentRow(reader); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message); + return null; + } + } + } + + /// + /// Creates a new agent + /// + /// The agent to create + override public void AddNewUserAgent(UserAgentData agent) + { + try + { + InsertUpdateAgentRow(agent); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message); + } + } + + #endregion + + #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(); + string sql = string.Format(@"INSERT INTO {0} + (ownerID,friendID,friendPerms,datetimestamp) + VALUES + (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("ownerID", friendlistowner)); + cmd.Parameters.Add(database.CreateParameter("friendID", friend)); + cmd.Parameters.Add(database.CreateParameter("friendPerms", perms)); + cmd.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue)); + conn.Open(); + cmd.ExecuteNonQuery(); + + try + { + sql = string.Format(@"INSERT INTO {0} + (ownerID,friendID,friendPerms,datetimestamp) + VALUES + (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName); + cmd.CommandText = sql; + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message); + return; + } + } + } + + /// + /// Remove an friend from the friendlist + /// + /// 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) + { + string sql = string.Format(@"DELETE from {0} + WHERE ownerID = @ownerID + AND friendID = @friendID", m_userFriendsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + cmd.Parameters.Add(database.CreateParameter("@friendID", friend)); + cmd.ExecuteNonQuery(); + sql = string.Format(@"DELETE from {0} + WHERE ownerID = @friendID + AND friendID = @ownerID", m_userFriendsTableName); + cmd.CommandText = sql; + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message); + } + } + } + + /// + /// 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) + { + string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms + WHERE ownerID = @ownerID + AND friendID = @friendID", m_userFriendsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + cmd.Parameters.Add(database.CreateParameter("@friendID", friend)); + cmd.Parameters.Add(database.CreateParameter("@friendPerms", perms)); + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); + } + } + } + + /// + /// Get (fetch?) the user's friendlist + /// + /// UUID of the friendlist owner + /// Friendlist list + override public List GetUserFriendList(UUID friendlistowner) + { + List friendList = new List(); + + //Left Join userfriends to itself + string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms + FROM {0} as a, {0} as b + WHERE a.ownerID = @ownerID + AND b.ownerID = a.friendID + AND b.friendID = a.ownerID", m_userFriendsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + conn.Open(); + try + { + using (SqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + FriendListItem fli = new FriendListItem(); + fli.FriendListOwner = new UUID((Guid)reader["ownerID"]); + fli.Friend = new UUID((Guid)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.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); + } + } + return friendList; + } + + override public Dictionary GetFriendRegionInfos(List uuids) + { + Dictionary infos = new Dictionary(); + try + { + foreach (UUID uuid in uuids) + { + string sql = string.Format(@"SELECT agentOnline,currentHandle + FROM {0} WHERE UUID = @uuid", m_agentsTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + + cmd.Parameters.Add(database.CreateParameter("@uuid", uuid)); + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + FriendRegionInfo fri = new FriendRegionInfo(); + fri.isOnline = (byte)reader["agentOnline"] != 0; + fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); + + infos[uuid] = fri; + } + } + } + } + } + catch (Exception e) + { + m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e); + } + + return infos; + } + #endregion + + #region Money functions (not used) + + /// + /// Performs a money transfer request between two accounts + /// + /// The senders account ID + /// The receivers account ID + /// The amount to transfer + /// false + override public bool MoneyTransferRequest(UUID from, UUID to, uint amount) + { + return false; + } + + /// + /// Performs an inventory transfer request between two accounts + /// + /// TODO: Move to inventory server + /// The senders account ID + /// The receivers account ID + /// The item to transfer + /// false + override public bool InventoryTransferRequest(UUID from, UUID to, UUID item) + { + return false; + } + + #endregion + + #region Appearance methods + + /// + /// Gets the user appearance. + /// + /// The user. + /// + override public AvatarAppearance GetUserAppearance(UUID user) + { + try + { + AvatarAppearance appearance = new AvatarAppearance(); + string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID"; + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + + cmd.Parameters.Add(database.CreateParameter("@UUID", user)); + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + appearance = readUserAppearance(reader); + else + { + m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString()); + return null; + } + + } + } + + appearance.SetAttachments(GetUserAttachments(user)); + + return appearance; + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", 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 = @"DELETE FROM avatarappearance WHERE owner=@owner; + INSERT INTO avatarappearance + (owner, serial, visual_params, texture, avatar_height, + body_item, body_asset, skin_item, skin_asset, hair_item, + hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset, + pants_item, pants_asset, shoes_item, shoes_asset, socks_item, + socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset, + undershirt_item, undershirt_asset, underpants_item, underpants_asset, + skirt_item, skirt_asset) + VALUES + (@owner, @serial, @visual_params, @texture, @avatar_height, + @body_item, @body_asset, @skin_item, @skin_asset, @hair_item, + @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset, + @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, + @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset, + @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, + @skirt_item, @skirt_asset)"; + + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + 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.GetBytes())); + 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)); + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", 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(); + string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid"; + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("@uuid", agentID)); + conn.Open(); + using (SqlDataReader reader = cmd.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 (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("uuid", agentID)); + conn.Open(); + cmd.ExecuteNonQuery(); + } + if (data == null) + return; + + sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset) + VALUES (@uuid, @attachpoint, @item, @asset)"; + + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + bool firstTime = true; + foreach (DictionaryEntry e in data) + { + int attachpoint = Convert.ToInt32(e.Key); + + Hashtable item = (Hashtable)e.Value; + + if (firstTime) + { + cmd.Parameters.Add(database.CreateParameter("@uuid", agentID)); + cmd.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint)); + cmd.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString()))); + cmd.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString()))); + firstTime = false; + } + cmd.Parameters["@uuid"].Value = agentID.Guid; //.ToString(); + cmd.Parameters["@attachpoint"].Value = attachpoint; + cmd.Parameters["@item"].Value = new Guid(item["item"].ToString()); + cmd.Parameters["@asset"].Value = new Guid(item["asset"].ToString()); + + try + { + conn.Open(); + cmd.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) + { + string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid"; + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("uuid", agentID)); + conn.Open(); + cmd.ExecuteNonQuery(); + } + } + + override public void LogoutUsers(UUID regionID) + { + } + + #endregion + + #region Other public methods + + /// + /// + /// + /// + /// + /// + override public List GeneratePickerResults(UUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + try + { + string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} + WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + //Add wildcard to the search + cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + cmd.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%")); + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((Guid)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 + { + string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} + WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName); + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((Guid)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 + /// + /// The SQL Result + /// the item read + private static AvatarAppearance readUserAppearance(SqlDataReader reader) + { + try + { + AvatarAppearance appearance = new AvatarAppearance(); + + appearance.Owner = new UUID((Guid)reader["owner"]); + appearance.Serial = Convert.ToInt32(reader["serial"]); + appearance.VisualParams = (byte[])reader["visual_params"]; + appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length); + appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]); + appearance.BodyItem = new UUID((Guid)reader["body_item"]); + appearance.BodyAsset = new UUID((Guid)reader["body_asset"]); + appearance.SkinItem = new UUID((Guid)reader["skin_item"]); + appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]); + appearance.HairItem = new UUID((Guid)reader["hair_item"]); + appearance.HairAsset = new UUID((Guid)reader["hair_asset"]); + appearance.EyesItem = new UUID((Guid)reader["eyes_item"]); + appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]); + appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]); + appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]); + appearance.PantsItem = new UUID((Guid)reader["pants_item"]); + appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]); + appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]); + appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]); + appearance.SocksItem = new UUID((Guid)reader["socks_item"]); + appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]); + appearance.JacketItem = new UUID((Guid)reader["jacket_item"]); + appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]); + appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]); + appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]); + appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]); + appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]); + appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]); + appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]); + appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]); + appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]); + + return appearance; + } + catch (SqlException e) + { + m_log.Error(e.ToString()); + } + + return null; + } + + /// + /// Insert/Update a agent row in the DB. + /// + /// agentdata. + private void InsertUpdateAgentRow(UserAgentData agentdata) + { + 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 (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand command = new SqlCommand(sql, conn)) + { + 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) + ">")); + conn.Open(); + + command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable); + try + { + if (command.ExecuteNonQuery() > 0) + { + command.Transaction.Commit(); + return; + } + + command.Transaction.Rollback(); + return; + } + catch (Exception e) + { + command.Transaction.Rollback(); + m_log.Error(e.ToString()); + return; + } + } + + } + + /// + /// Reads an agent row from a database reader + /// + /// An active database reader + /// A user session agent + private UserAgentData readAgentRow(SqlDataReader reader) + { + UserAgentData retval = new UserAgentData(); + + if (reader.Read()) + { + // Agent IDs + retval.ProfileID = new UUID((Guid)reader["UUID"]); + retval.SessionID = new UUID((Guid)reader["sessionID"]); + retval.SecureSessionID = new UUID((Guid)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 = new UUID((Guid)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; + } + + /// + /// Creates a new user and inserts it into the database + /// + /// User ID + /// First part of the login + /// Second part of the login + /// Email of person + /// 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 email, 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) + { + string sql = string.Format(@"INSERT INTO {0} + ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt], + [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], + [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI], + [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], + [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], + [homeRegionID], [userFlags], [godLevel], [customType], [partner]) + VALUES + (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt, + @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, + @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI, + @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, + @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, + @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName); + + try + { + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand command = new SqlCommand(sql, conn)) + { + 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("email", email)); + 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)); + conn.Open(); + command.ExecuteNonQuery(); + return; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return; + } + } + + /// + /// Reads a user profile from an active data reader + /// + /// An active database reader + /// A user profile + private static UserProfileData ReadUserRow(SqlDataReader reader) + { + UserProfileData retval = new UserProfileData(); + + if (reader.Read()) + { + retval.ID = new UUID((Guid)reader["UUID"]); + retval.FirstName = (string)reader["username"]; + retval.SurName = (string)reader["lastname"]; + if (reader.IsDBNull(reader.GetOrdinal("email"))) + retval.Email = ""; + else + retval.Email = (string)reader["email"]; + + 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())); + + if (reader.IsDBNull(reader.GetOrdinal("homeRegionID"))) + retval.HomeRegionID = UUID.Zero; + else + retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]); + + retval.Created = Convert.ToInt32(reader["created"].ToString()); + retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); + + if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI"))) + retval.UserInventoryURI = ""; + else + retval.UserInventoryURI = (string)reader["userInventoryURI"]; + + if (reader.IsDBNull(reader.GetOrdinal("userAssetURI"))) + retval.UserAssetURI = ""; + else + retval.UserAssetURI = (string)reader["userAssetURI"]; + + retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); + retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); + + + if (reader.IsDBNull(reader.GetOrdinal("profileAboutText"))) + retval.AboutText = ""; + else + retval.AboutText = (string)reader["profileAboutText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstText"))) + retval.FirstLifeAboutText = ""; + else + retval.FirstLifeAboutText = (string)reader["profileFirstText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileImage"))) + retval.Image = UUID.Zero; + else + retval.Image = new UUID((Guid)reader["profileImage"]); + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage"))) + retval.Image = UUID.Zero; + else + retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]); + + if (reader.IsDBNull(reader.GetOrdinal("webLoginKey"))) + retval.WebLoginKey = UUID.Zero; + else + retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]); + + retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString()); + retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString()); + if (reader.IsDBNull(reader.GetOrdinal("customType"))) + retval.CustomType = ""; + else + retval.CustomType = reader["customType"].ToString(); + + if (reader.IsDBNull(reader.GetOrdinal("partner"))) + retval.Partner = UUID.Zero; + else + retval.Partner = new UUID((Guid)reader["partner"]); + } + else + { + return null; + } + return retval; + } + #endregion + } + +} -- cgit v1.1 From 92029dc171b466f4bdaf0a882426f4202f3e6162 Mon Sep 17 00:00:00 2001 From: Melanie Date: Wed, 24 Feb 2010 16:00:06 +0000 Subject: Remove some obsolete files from MSSQL. Fix a missing constructor arg that was introdiced by the latest jhurlipatch --- OpenSim/Data/MSSQL/MSSQLUserData.cs | 1238 ----------------------------------- 1 file changed, 1238 deletions(-) delete mode 100644 OpenSim/Data/MSSQL/MSSQLUserData.cs (limited to 'OpenSim/Data/MSSQL/MSSQLUserData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs deleted file mode 100644 index 6bdb559..0000000 --- a/OpenSim/Data/MSSQL/MSSQLUserData.cs +++ /dev/null @@ -1,1238 +0,0 @@ -/* - * Copyright (c) Contributors, http://opensimulator.org/ - * See CONTRIBUTORS.TXT for a full list of copyright holders. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * * Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. - * * Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. - * * Neither the name of the OpenSimulator Project nor the - * names of its contributors may be used to endorse or promote products - * derived from this software without specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY - * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY - * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND - * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -using System; -using System.Collections; -using System.Collections.Generic; -using System.Data; -using System.Data.SqlClient; -using System.Reflection; -using log4net; -using OpenMetaverse; -using OpenSim.Framework; - -namespace OpenSim.Data.MSSQL -{ - /// - /// A database interface class to a user profile storage system - /// - public class MSSQLUserData : UserDataBase - { - private const string _migrationStore = "UserStore"; - - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - /// - /// Database manager for MSSQL - /// - public MSSQLManager database; - private string m_connectionString; - - private const string m_agentsTableName = "agents"; - private const string m_usersTableName = "users"; - private const string m_userFriendsTableName = "userfriends"; - - // [Obsolete("Cannot be default-initialized!")] - override public void Initialise() - { - m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException(Name); - } - - /// - /// Loads and initialises the MSSQL storage plugin - /// - /// connectionstring - /// use mssql_connection.ini - override public void Initialise(string connect) - { - m_connectionString = connect; - database = new MSSQLManager(connect); - - - //Check migration on DB - database.CheckMigration(_migrationStore); - } - - /// - /// Releases unmanaged and - optionally - managed resources - /// - override public void Dispose() { } - - #region User table methods - - /// - /// 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) - { - string sql = string.Format(@"SELECT * FROM {0} - WHERE username = @first AND lastname = @second", m_usersTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("first", user)); - cmd.Parameters.Add(database.CreateParameter("second", last)); - try - { - conn.Open(); - using (SqlDataReader reader = cmd.ExecuteReader()) - { - return ReadUserRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message); - return null; - } - } - } - - /// - /// See IUserDataPlugin - /// - /// - /// - override public UserProfileData GetUserByUUID(UUID uuid) - { - string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("uuid", uuid)); - conn.Open(); - try - { - using (SqlDataReader reader = cmd.ExecuteReader()) - { - return ReadUserRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message); - return null; - } - } - } - - - /// - /// Creates a new users profile - /// - /// The user profile to create - override public void AddNewUserProfile(UserProfileData user) - { - try - { - InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, 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.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message); - } - } - - /// - /// update a user profile - /// - /// the profile to update - /// - override public bool UpdateUserProfile(UserProfileData user) - { - string sql = string.Format(@"UPDATE {0} - SET UUID = @uuid, - username = @username, - lastname = @lastname, - email = @email, - 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;", m_usersTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand command = new SqlCommand(sql, conn)) - { - 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("email", user.Email)); - 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)); - conn.Open(); - try - { - int affected = command.ExecuteNonQuery(); - return (affected != 0); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message); - } - } - return false; - } - - #endregion - - #region Agent table methods - - /// - /// Returns a user session searching by name - /// - /// The account name - /// The users session - override public UserAgentData GetAgentByName(string name) - { - return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); - } - - /// - /// Returns a user session by account name - /// - /// First part of the users account name - /// Second part of the users account name - /// The users session - override public UserAgentData GetAgentByName(string user, string last) - { - UserProfileData profile = GetUserByName(user, last); - return GetAgentByUUID(profile.ID); - } - - /// - /// Returns an agent session by account UUID - /// - /// The accounts UUID - /// The users session - override public UserAgentData GetAgentByUUID(UUID uuid) - { - string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("uuid", uuid)); - conn.Open(); - try - { - using (SqlDataReader reader = cmd.ExecuteReader()) - { - return readAgentRow(reader); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message); - return null; - } - } - } - - /// - /// Creates a new agent - /// - /// The agent to create - override public void AddNewUserAgent(UserAgentData agent) - { - try - { - InsertUpdateAgentRow(agent); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message); - } - } - - #endregion - - #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(); - string sql = string.Format(@"INSERT INTO {0} - (ownerID,friendID,friendPerms,datetimestamp) - VALUES - (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("ownerID", friendlistowner)); - cmd.Parameters.Add(database.CreateParameter("friendID", friend)); - cmd.Parameters.Add(database.CreateParameter("friendPerms", perms)); - cmd.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue)); - conn.Open(); - cmd.ExecuteNonQuery(); - - try - { - sql = string.Format(@"INSERT INTO {0} - (ownerID,friendID,friendPerms,datetimestamp) - VALUES - (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName); - cmd.CommandText = sql; - cmd.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message); - return; - } - } - } - - /// - /// Remove an friend from the friendlist - /// - /// 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) - { - string sql = string.Format(@"DELETE from {0} - WHERE ownerID = @ownerID - AND friendID = @friendID", m_userFriendsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); - cmd.Parameters.Add(database.CreateParameter("@friendID", friend)); - cmd.ExecuteNonQuery(); - sql = string.Format(@"DELETE from {0} - WHERE ownerID = @friendID - AND friendID = @ownerID", m_userFriendsTableName); - cmd.CommandText = sql; - conn.Open(); - try - { - cmd.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message); - } - } - } - - /// - /// 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) - { - string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms - WHERE ownerID = @ownerID - AND friendID = @friendID", m_userFriendsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); - cmd.Parameters.Add(database.CreateParameter("@friendID", friend)); - cmd.Parameters.Add(database.CreateParameter("@friendPerms", perms)); - conn.Open(); - try - { - cmd.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); - } - } - } - - /// - /// Get (fetch?) the user's friendlist - /// - /// UUID of the friendlist owner - /// Friendlist list - override public List GetUserFriendList(UUID friendlistowner) - { - List friendList = new List(); - - //Left Join userfriends to itself - string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms - FROM {0} as a, {0} as b - WHERE a.ownerID = @ownerID - AND b.ownerID = a.friendID - AND b.friendID = a.ownerID", m_userFriendsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); - conn.Open(); - try - { - using (SqlDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - FriendListItem fli = new FriendListItem(); - fli.FriendListOwner = new UUID((Guid)reader["ownerID"]); - fli.Friend = new UUID((Guid)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.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message); - } - } - return friendList; - } - - override public Dictionary GetFriendRegionInfos(List uuids) - { - Dictionary infos = new Dictionary(); - try - { - foreach (UUID uuid in uuids) - { - string sql = string.Format(@"SELECT agentOnline,currentHandle - FROM {0} WHERE UUID = @uuid", m_agentsTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - - cmd.Parameters.Add(database.CreateParameter("@uuid", uuid)); - conn.Open(); - using (SqlDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - FriendRegionInfo fri = new FriendRegionInfo(); - fri.isOnline = (byte)reader["agentOnline"] != 0; - fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); - - infos[uuid] = fri; - } - } - } - } - } - catch (Exception e) - { - m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e); - } - - return infos; - } - #endregion - - #region Money functions (not used) - - /// - /// Performs a money transfer request between two accounts - /// - /// The senders account ID - /// The receivers account ID - /// The amount to transfer - /// false - override public bool MoneyTransferRequest(UUID from, UUID to, uint amount) - { - return false; - } - - /// - /// Performs an inventory transfer request between two accounts - /// - /// TODO: Move to inventory server - /// The senders account ID - /// The receivers account ID - /// The item to transfer - /// false - override public bool InventoryTransferRequest(UUID from, UUID to, UUID item) - { - return false; - } - - #endregion - - #region Appearance methods - - /// - /// Gets the user appearance. - /// - /// The user. - /// - override public AvatarAppearance GetUserAppearance(UUID user) - { - try - { - AvatarAppearance appearance = new AvatarAppearance(); - string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID"; - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - - cmd.Parameters.Add(database.CreateParameter("@UUID", user)); - conn.Open(); - using (SqlDataReader reader = cmd.ExecuteReader()) - { - if (reader.Read()) - appearance = readUserAppearance(reader); - else - { - m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString()); - return null; - } - - } - } - - appearance.SetAttachments(GetUserAttachments(user)); - - return appearance; - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", 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 = @"DELETE FROM avatarappearance WHERE owner=@owner; - INSERT INTO avatarappearance - (owner, serial, visual_params, texture, avatar_height, - body_item, body_asset, skin_item, skin_asset, hair_item, - hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset, - pants_item, pants_asset, shoes_item, shoes_asset, socks_item, - socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset, - undershirt_item, undershirt_asset, underpants_item, underpants_asset, - skirt_item, skirt_asset) - VALUES - (@owner, @serial, @visual_params, @texture, @avatar_height, - @body_item, @body_asset, @skin_item, @skin_asset, @hair_item, - @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset, - @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, - @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset, - @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, - @skirt_item, @skirt_asset)"; - - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - 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.GetBytes())); - 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)); - conn.Open(); - try - { - cmd.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", 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(); - string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid"; - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("@uuid", agentID)); - conn.Open(); - using (SqlDataReader reader = cmd.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 (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("uuid", agentID)); - conn.Open(); - cmd.ExecuteNonQuery(); - } - if (data == null) - return; - - sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset) - VALUES (@uuid, @attachpoint, @item, @asset)"; - - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - bool firstTime = true; - foreach (DictionaryEntry e in data) - { - int attachpoint = Convert.ToInt32(e.Key); - - Hashtable item = (Hashtable)e.Value; - - if (firstTime) - { - cmd.Parameters.Add(database.CreateParameter("@uuid", agentID)); - cmd.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint)); - cmd.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString()))); - cmd.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString()))); - firstTime = false; - } - cmd.Parameters["@uuid"].Value = agentID.Guid; //.ToString(); - cmd.Parameters["@attachpoint"].Value = attachpoint; - cmd.Parameters["@item"].Value = new Guid(item["item"].ToString()); - cmd.Parameters["@asset"].Value = new Guid(item["asset"].ToString()); - - try - { - conn.Open(); - cmd.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) - { - string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid"; - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("uuid", agentID)); - conn.Open(); - cmd.ExecuteNonQuery(); - } - } - - override public void LogoutUsers(UUID regionID) - { - } - - #endregion - - #region Other public methods - - /// - /// - /// - /// - /// - /// - override public List GeneratePickerResults(UUID queryID, string query) - { - List returnlist = new List(); - string[] querysplit = query.Split(' '); - if (querysplit.Length == 2) - { - try - { - string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} - WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - //Add wildcard to the search - cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); - cmd.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%")); - conn.Open(); - using (SqlDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((Guid)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 - { - string sql = string.Format(@"SELECT UUID,username,lastname FROM {0} - WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName); - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand cmd = new SqlCommand(sql, conn)) - { - cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); - conn.Open(); - using (SqlDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((Guid)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 - /// - /// The SQL Result - /// the item read - private static AvatarAppearance readUserAppearance(SqlDataReader reader) - { - try - { - AvatarAppearance appearance = new AvatarAppearance(); - - appearance.Owner = new UUID((Guid)reader["owner"]); - appearance.Serial = Convert.ToInt32(reader["serial"]); - appearance.VisualParams = (byte[])reader["visual_params"]; - appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length); - appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]); - appearance.BodyItem = new UUID((Guid)reader["body_item"]); - appearance.BodyAsset = new UUID((Guid)reader["body_asset"]); - appearance.SkinItem = new UUID((Guid)reader["skin_item"]); - appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]); - appearance.HairItem = new UUID((Guid)reader["hair_item"]); - appearance.HairAsset = new UUID((Guid)reader["hair_asset"]); - appearance.EyesItem = new UUID((Guid)reader["eyes_item"]); - appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]); - appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]); - appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]); - appearance.PantsItem = new UUID((Guid)reader["pants_item"]); - appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]); - appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]); - appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]); - appearance.SocksItem = new UUID((Guid)reader["socks_item"]); - appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]); - appearance.JacketItem = new UUID((Guid)reader["jacket_item"]); - appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]); - appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]); - appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]); - appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]); - appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]); - appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]); - appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]); - appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]); - appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]); - - return appearance; - } - catch (SqlException e) - { - m_log.Error(e.ToString()); - } - - return null; - } - - /// - /// Insert/Update a agent row in the DB. - /// - /// agentdata. - private void InsertUpdateAgentRow(UserAgentData agentdata) - { - 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 (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand command = new SqlCommand(sql, conn)) - { - 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) + ">")); - conn.Open(); - - command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable); - try - { - if (command.ExecuteNonQuery() > 0) - { - command.Transaction.Commit(); - return; - } - - command.Transaction.Rollback(); - return; - } - catch (Exception e) - { - command.Transaction.Rollback(); - m_log.Error(e.ToString()); - return; - } - } - - } - - /// - /// Reads an agent row from a database reader - /// - /// An active database reader - /// A user session agent - private UserAgentData readAgentRow(SqlDataReader reader) - { - UserAgentData retval = new UserAgentData(); - - if (reader.Read()) - { - // Agent IDs - retval.ProfileID = new UUID((Guid)reader["UUID"]); - retval.SessionID = new UUID((Guid)reader["sessionID"]); - retval.SecureSessionID = new UUID((Guid)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 = new UUID((Guid)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; - } - - /// - /// Creates a new user and inserts it into the database - /// - /// User ID - /// First part of the login - /// Second part of the login - /// Email of person - /// 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 email, 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) - { - string sql = string.Format(@"INSERT INTO {0} - ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt], - [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], - [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI], - [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], - [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], - [homeRegionID], [userFlags], [godLevel], [customType], [partner]) - VALUES - (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt, - @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, - @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI, - @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, - @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, - @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName); - - try - { - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand command = new SqlCommand(sql, conn)) - { - 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("email", email)); - 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)); - conn.Open(); - command.ExecuteNonQuery(); - return; - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return; - } - } - - /// - /// Reads a user profile from an active data reader - /// - /// An active database reader - /// A user profile - private static UserProfileData ReadUserRow(SqlDataReader reader) - { - UserProfileData retval = new UserProfileData(); - - if (reader.Read()) - { - retval.ID = new UUID((Guid)reader["UUID"]); - retval.FirstName = (string)reader["username"]; - retval.SurName = (string)reader["lastname"]; - if (reader.IsDBNull(reader.GetOrdinal("email"))) - retval.Email = ""; - else - retval.Email = (string)reader["email"]; - - 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())); - - if (reader.IsDBNull(reader.GetOrdinal("homeRegionID"))) - retval.HomeRegionID = UUID.Zero; - else - retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]); - - retval.Created = Convert.ToInt32(reader["created"].ToString()); - retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); - - if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI"))) - retval.UserInventoryURI = ""; - else - retval.UserInventoryURI = (string)reader["userInventoryURI"]; - - if (reader.IsDBNull(reader.GetOrdinal("userAssetURI"))) - retval.UserAssetURI = ""; - else - retval.UserAssetURI = (string)reader["userAssetURI"]; - - retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); - retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); - - - if (reader.IsDBNull(reader.GetOrdinal("profileAboutText"))) - retval.AboutText = ""; - else - retval.AboutText = (string)reader["profileAboutText"]; - - if (reader.IsDBNull(reader.GetOrdinal("profileFirstText"))) - retval.FirstLifeAboutText = ""; - else - retval.FirstLifeAboutText = (string)reader["profileFirstText"]; - - if (reader.IsDBNull(reader.GetOrdinal("profileImage"))) - retval.Image = UUID.Zero; - else - retval.Image = new UUID((Guid)reader["profileImage"]); - - if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage"))) - retval.Image = UUID.Zero; - else - retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]); - - if (reader.IsDBNull(reader.GetOrdinal("webLoginKey"))) - retval.WebLoginKey = UUID.Zero; - else - retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]); - - retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString()); - retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString()); - if (reader.IsDBNull(reader.GetOrdinal("customType"))) - retval.CustomType = ""; - else - retval.CustomType = reader["customType"].ToString(); - - if (reader.IsDBNull(reader.GetOrdinal("partner"))) - retval.Partner = UUID.Zero; - else - retval.Partner = new UUID((Guid)reader["partner"]); - } - else - { - return null; - } - return retval; - } - #endregion - } - -} -- cgit v1.1