From c52c68f314c67c76c7181a6d0828f476290fbd66 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 2 Apr 2008 15:24:31 +0000 Subject: whole lot more moving --- OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 771 -------------------------- 1 file changed, 771 deletions(-) delete mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs (limited to 'OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs') diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs deleted file mode 100644 index be0417d..0000000 --- a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs +++ /dev/null @@ -1,771 +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 OpenSim 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.Generic; -using System.Data; -using System.Data.SqlClient; -using libsecondlife; -using OpenSim.Framework.Console; - -namespace OpenSim.Framework.Data.MSSQL -{ - /// - /// A database interface class to a user profile storage system - /// - public class MSSQLUserData : UserDataBase - { - private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); - - /// - /// Database manager for MySQL - /// - public MSSQLManager database; - - private string m_agentsTableName; - private string m_usersTableName; - private string m_userFriendsTableName; - - /// - /// Loads and initialises the MySQL storage plugin - /// - override public void Initialise() - { - // Load from an INI file connection details - // TODO: move this to XML? - 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"); - - m_usersTableName = iniFile.ParseFileReadValue("userstablename"); - if (m_usersTableName == null) - { - m_usersTableName = "users"; - } - - m_userFriendsTableName = iniFile.ParseFileReadValue("userfriendstablename"); - if (m_userFriendsTableName == null) - { - m_userFriendsTableName = "userfriends"; - } - - m_agentsTableName = iniFile.ParseFileReadValue("agentstablename"); - if (m_agentsTableName == null) - { - m_agentsTableName = "agents"; - } - - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); - - TestTables(); - } - - private bool TestTables() - { - IDbCommand cmd; - - cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary()); - try - { - cmd.ExecuteNonQuery(); - } - catch - { - database.ExecuteResourceSql("Mssql-users.sql"); - } - - cmd = database.Query("select top 1 * from " + m_agentsTableName, new Dictionary()); - try - { - cmd.ExecuteNonQuery(); - } - catch - { - database.ExecuteResourceSql("Mssql-agents.sql"); - } - - cmd = database.Query("select top 1 * from " + m_userFriendsTableName, new Dictionary()); - try - { - cmd.ExecuteNonQuery(); - } - catch - { - database.ExecuteResourceSql("CreateUserFriendsTable.sql"); - } - - return true; - } - /// - /// Searches the database for a specified user profile by name components - /// - /// The first part of the account name - /// The second part of the account name - /// A user profile - override public UserProfileData GetUserByName(string user, string last) - { - try - { - lock (database) - { - Dictionary param = new Dictionary(); - param["first"] = user; - param["second"] = last; - - IDbCommand result = - database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param); - IDataReader reader = result.ExecuteReader(); - - UserProfileData row = database.readUserRow(reader); - - reader.Close(); - result.Dispose(); - - return row; - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return null; - } - } - - #region User Friends List Data - - override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) - { - int dtvalue = Util.UnixTimeSinceEpoch(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.UUID.ToString(); - param["@friendID"] = friend.UUID.ToString(); - param["@friendPerms"] = perms.ToString(); - param["@datetimestamp"] = dtvalue.ToString(); - - try - { - lock (database) - { - IDbCommand adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@ownerID,@friendID,@friendPerms,@datetimestamp)", - param); - - adder.ExecuteNonQuery(); - - adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@friendID,@ownerID,@friendPerms,@datetimestamp)", - param); - adder.ExecuteNonQuery(); - - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return; - } - } - - override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) - { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.UUID.ToString(); - param["@friendID"] = friend.UUID.ToString(); - - - try - { - lock (database) - { - IDbCommand updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID", - param); - updater.ExecuteNonQuery(); - - updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @friendID and friendID = @ownerID", - param); - updater.ExecuteNonQuery(); - - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return; - } - } - - override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) - { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.UUID.ToString(); - param["@friendID"] = friend.UUID.ToString(); - param["@friendPerms"] = perms.ToString(); - - - try - { - lock (database) - { - IDbCommand updater = - database.Query( - "update " + m_userFriendsTableName + - " SET friendPerms = @friendPerms " + - "where ownerID = @ownerID and friendID = @friendID", - param); - - updater.ExecuteNonQuery(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return; - } - } - - - override public List GetUserFriendList(LLUUID friendlistowner) - { - List Lfli = new List(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.UUID.ToString(); - - try - { - lock (database) - { - //Left Join userfriends to itself - IDbCommand result = - database.Query( - "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + - " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - FriendListItem fli = new FriendListItem(); - fli.FriendListOwner = new LLUUID((string)reader["ownerID"]); - fli.Friend = new LLUUID((string)reader["friendID"]); - fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]); - - // This is not a real column in the database table, it's a joined column from the opposite record - fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); - - Lfli.Add(fli); - } - reader.Close(); - result.Dispose(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return Lfli; - } - - return Lfli; - } - - #endregion - - override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) - { - m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); - } - - - - override public List GeneratePickerResults(LLUUID queryID, string query) - { - List returnlist = new List(); - string[] querysplit; - querysplit = query.Split(' '); - if (querysplit.Length == 2) - { - try - { - lock (database) - { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - param["second"] = querysplit[1]; - - IDbCommand result = - database.Query( - "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - reader.Close(); - result.Dispose(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return returnlist; - } - } - else if (querysplit.Length == 1) - { - try - { - lock (database) - { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - - IDbCommand result = - database.Query( - "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @first", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - reader.Close(); - result.Dispose(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return returnlist; - } - } - return returnlist; - } - - // See IUserData - override public UserProfileData GetUserByUUID(LLUUID uuid) - { - try - { - lock (database) - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param); - IDataReader reader = result.ExecuteReader(); - - UserProfileData row = database.readUserRow(reader); - - reader.Close(); - result.Dispose(); - - return row; - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return null; - } - } - - /// - /// 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.UUID); - } - - /// - /// Returns an agent session by account UUID - /// - /// The accounts UUID - /// The users session - override public UserAgentData GetAgentByUUID(LLUUID uuid) - { - try - { - lock (database) - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param); - IDataReader reader = result.ExecuteReader(); - - UserAgentData row = database.readAgentRow(reader); - - reader.Close(); - result.Dispose(); - - return row; - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return null; - } - } - override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey) - { - UserProfileData user = GetUserByUUID(AgentID); - user.webLoginKey = WebLoginKey; - UpdateUserProfile(user); - - } - /// - /// Creates a new users profile - /// - /// The user profile to create - override public void AddNewUserProfile(UserProfileData user) - { - try - { - lock (database) - { - InsertUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt, - user.homeRegion, user.homeLocation.X, user.homeLocation.Y, - user.homeLocation.Z, - user.homeLookAt.X, user.homeLookAt.Y, user.homeLookAt.Z, user.created, - user.lastLogin, user.userInventoryURI, user.userAssetURI, - user.profileCanDoMask, user.profileWantDoMask, - user.profileAboutText, user.profileFirstText, user.profileImage, - user.profileFirstImage, user.webLoginKey); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - } - } - - /// - /// Creates a new user and inserts it into the database - /// - /// User ID - /// First part of the login - /// Second part of the login - /// A salted hash of the users password - /// The salt used for the password hash - /// A regionHandle of the users home region - /// Home region position vector - /// Home region position vector - /// Home region position vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Account created (unix timestamp) - /// Last login (unix timestamp) - /// Users inventory URI - /// Users asset URI - /// I can do mask - /// I want to do mask - /// Profile text - /// Firstlife text - /// UUID for profile image - /// UUID for firstlife image - /// Success? - private bool InsertUserRow(LLUUID uuid, string username, string lastname, string passwordHash, - string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, - float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, - string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, - string aboutText, string firstText, - LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey) - { - string sql = "INSERT INTO "+m_usersTableName; - sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], "; - sql += - "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], "; - sql += - "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], "; - sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey]) VALUES "; - - sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; - sql += - "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; - sql += - "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; - sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey);"; - - Dictionary parameters = new Dictionary(); - parameters["UUID"] = uuid.ToString(); - parameters["username"] = username.ToString(); - parameters["lastname"] = lastname.ToString(); - parameters["passwordHash"] = passwordHash.ToString(); - parameters["passwordSalt"] = passwordSalt.ToString(); - parameters["homeRegion"] = homeRegion.ToString(); - parameters["homeLocationX"] = homeLocX.ToString(); - parameters["homeLocationY"] = homeLocY.ToString(); - parameters["homeLocationZ"] = homeLocZ.ToString(); - parameters["homeLookAtX"] = homeLookAtX.ToString(); - parameters["homeLookAtY"] = homeLookAtY.ToString(); - parameters["homeLookAtZ"] = homeLookAtZ.ToString(); - parameters["created"] = created.ToString(); - parameters["lastLogin"] = lastlogin.ToString(); - parameters["userInventoryURI"] = String.Empty; - parameters["userAssetURI"] = String.Empty; - parameters["profileCanDoMask"] = "0"; - parameters["profileWantDoMask"] = "0"; - parameters["profileAboutText"] = aboutText; - parameters["profileFirstText"] = firstText; - parameters["profileImage"] = profileImage.ToString(); - parameters["profileFirstImage"] = firstImage.ToString(); - parameters["webLoginKey"] = LLUUID.Random().ToString(); - - bool returnval = false; - - try - { - IDbCommand result = database.Query(sql, parameters); - - if (result.ExecuteNonQuery() == 1) - returnval = true; - - result.Dispose(); - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return false; - } - - return returnval; - } - - /// - /// Creates a new agent - /// - /// The agent to create - override public void AddNewUserAgent(UserAgentData agent) - { - // Do nothing. - } - - - override public bool UpdateUserProfile(UserProfileData user) - { - SqlCommand command = new SqlCommand("UPDATE " + m_usersTableName + " set UUID = @uuid, " + - "username = @username, " + - "lastname = @lastname," + - "passwordHash = @passwordHash," + - "passwordSalt = @passwordSalt," + - "homeRegion = @homeRegion," + - "homeLocationX = @homeLocationX," + - "homeLocationY = @homeLocationY," + - "homeLocationZ = @homeLocationZ," + - "homeLookAtX = @homeLookAtX," + - "homeLookAtY = @homeLookAtY," + - "homeLookAtZ = @homeLookAtZ," + - "created = @created," + - "lastLogin = @lastLogin," + - "userInventoryURI = @userInventoryURI," + - "userAssetURI = @userAssetURI," + - "profileCanDoMask = @profileCanDoMask," + - "profileWantDoMask = @profileWantDoMask," + - "profileAboutText = @profileAboutText," + - "profileFirstText = @profileFirstText," + - "profileImage = @profileImage," + - "profileFirstImage = @profileFirstImage, " + - "webLoginKey = @webLoginKey where " + - "UUID = @keyUUUID;", database.getConnection()); - SqlParameter param1 = new SqlParameter("@uuid", user.UUID.ToString()); - SqlParameter param2 = new SqlParameter("@username", user.username); - SqlParameter param3 = new SqlParameter("@lastname", user.surname); - SqlParameter param4 = new SqlParameter("@passwordHash", user.passwordHash); - SqlParameter param5 = new SqlParameter("@passwordSalt", user.passwordSalt); - SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.homeRegion)); - SqlParameter param7 = new SqlParameter("@homeLocationX", user.homeLocation.X); - SqlParameter param8 = new SqlParameter("@homeLocationY", user.homeLocation.Y); - SqlParameter param9 = new SqlParameter("@homeLocationZ", user.homeLocation.Y); - SqlParameter param10 = new SqlParameter("@homeLookAtX", user.homeLookAt.X); - SqlParameter param11 = new SqlParameter("@homeLookAtY", user.homeLookAt.Y); - SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.homeLookAt.Z); - SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.created)); - SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.lastLogin)); - SqlParameter param15 = new SqlParameter("@userInventoryURI", user.userInventoryURI); - SqlParameter param16 = new SqlParameter("@userAssetURI", user.userAssetURI); - SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.profileCanDoMask)); - SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.profileWantDoMask)); - SqlParameter param19 = new SqlParameter("@profileAboutText", user.profileAboutText); - SqlParameter param20 = new SqlParameter("@profileFirstText", user.profileFirstText); - SqlParameter param21 = new SqlParameter("@profileImage", user.profileImage.ToString()); - SqlParameter param22 = new SqlParameter("@profileFirstImage", user.profileFirstImage.ToString()); - SqlParameter param23 = new SqlParameter("@keyUUUID", user.UUID.ToString()); - SqlParameter param24 = new SqlParameter("@webLoginKey", user.webLoginKey.UUID.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - command.Parameters.Add(param4); - command.Parameters.Add(param5); - command.Parameters.Add(param6); - command.Parameters.Add(param7); - command.Parameters.Add(param8); - command.Parameters.Add(param9); - command.Parameters.Add(param10); - command.Parameters.Add(param11); - command.Parameters.Add(param12); - command.Parameters.Add(param13); - command.Parameters.Add(param14); - command.Parameters.Add(param15); - command.Parameters.Add(param16); - command.Parameters.Add(param17); - command.Parameters.Add(param18); - command.Parameters.Add(param19); - command.Parameters.Add(param20); - command.Parameters.Add(param21); - command.Parameters.Add(param22); - command.Parameters.Add(param23); - command.Parameters.Add(param24); - try - { - int affected = command.ExecuteNonQuery(); - if (affected != 0) - { - return true; - } - else - { - return false; - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - return false; - } - - /// - /// Performs a money transfer request between two accounts - /// - /// The senders account ID - /// The receivers account ID - /// The amount to transfer - /// Success? - override public bool MoneyTransferRequest(LLUUID from, LLUUID 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 - /// Success? - override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) - { - return false; - } - - /// - /// Database provider name - /// - /// Provider name - override public string getName() - { - return "MSSQL Userdata Interface"; - } - - /// - /// Database provider version - /// - /// provider version - override public string GetVersion() - { - return database.getVersion(); - } - - /// - /// Not implemented - /// - /// - public void runQuery(string query) - { - } - } -} -- cgit v1.1