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/Data/MySQL/MySQLManager.cs | 909 +++++++++++++++++++++++++++++++++++++ 1 file changed, 909 insertions(+) create mode 100644 OpenSim/Data/MySQL/MySQLManager.cs (limited to 'OpenSim/Data/MySQL/MySQLManager.cs') diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs new file mode 100644 index 0000000..579667b --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLManager.cs @@ -0,0 +1,909 @@ +/* + * 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 System.IO; +using System.Reflection; +using libsecondlife; +using MySql.Data.MySqlClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// A MySQL Database manager + /// + internal class MySQLManager + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database connection object + /// + private MySqlConnection dbcon; + + /// + /// Connection string for ADO.net + /// + private string connectionString; + + /// + /// Initialises and creates a new MySQL connection and maintains it. + /// + /// The MySQL server being connected to + /// The name of the MySQL database being used + /// The username logging into the database + /// The password for the user logging in + /// Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'. + public MySQLManager(string hostname, string database, string username, string password, string cpooling, + string port) + { + try + { + connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + + username + ";Password=" + password + ";Pooling=" + cpooling + ";"; + dbcon = new MySqlConnection(connectionString); + + try + { + dbcon.Open(); + } + catch(Exception e) + { + throw new Exception( "Connection error while using connection string ["+connectionString+"]", e ); + } + + m_log.Info("[MYSQL]: Connection established"); + } + catch (Exception e) + { + throw new Exception("Error initialising MySql Database: " + e.ToString()); + } + } + + /// + /// Get the connection being used + /// + public MySqlConnection Connection + { + get { return dbcon; } + } + + /// + /// Shuts down the database connection + /// + public void Close() + { + dbcon.Close(); + dbcon = null; + } + + /// + /// Reconnects to the database + /// + public void Reconnect() + { + lock (dbcon) + { + try + { + // Close the DB connection + dbcon.Close(); + // Try reopen it + dbcon = new MySqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + m_log.Error("Unable to reconnect to database " + e.ToString()); + } + } + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + Module module = GetType().Module; + string dllName = module.Assembly.ManifestModule.Name; + Version dllVersion = module.Assembly.GetName().Version; + + return + string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, + dllVersion.Revision); + } + + /// + /// Extract a named string resource from the embedded resources + /// + /// name of embedded resource + /// string contained within the embedded resource + private string getResourceString(string name) + { + Assembly assem = GetType().Assembly; + string[] names = assem.GetManifestResourceNames(); + + foreach (string s in names) + { + if (s.EndsWith(name)) + { + using (Stream resource = assem.GetManifestResourceStream(s)) + { + using (StreamReader resourceReader = new StreamReader(resource)) + { + string resourceString = resourceReader.ReadToEnd(); + return resourceString; + } + } + } + } + throw new Exception(string.Format("Resource '{0}' was not found", name)); + } + + /// + /// Execute a SQL statement stored in a resource, as a string + /// + /// + public void ExecuteResourceSql(string name) + { + MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); + cmd.ExecuteNonQuery(); + } + + /// + /// Given a list of tables, return the version of the tables, as seen in the database + /// + /// + public void GetTableVersion(Dictionary tableList) + { + lock (dbcon) + { + MySqlCommand tablesCmd = + new MySqlCommand( + "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", + dbcon); + tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); + using (MySqlDataReader tables = tablesCmd.ExecuteReader()) + { + while (tables.Read()) + { + try + { + string tableName = (string) tables["TABLE_NAME"]; + string comment = (string) tables["TABLE_COMMENT"]; + if (tableList.ContainsKey(tableName)) + { + tableList[tableName] = comment; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + tables.Close(); + } + } + } + + // TODO: at some time this code should be cleaned up + + /// + /// Runs a query with protection against SQL Injection by using parameterised input. + /// + /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y + /// The parameters - index so that @y is indexed as 'y' + /// A MySQL DB Command + public IDbCommand Query(string sql, Dictionary parameters) + { + try + { + MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); + dbcommand.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + dbcommand.Parameters.AddWithValue(param.Key, param.Value); + } + + return (IDbCommand) dbcommand; + } + catch + { + lock (dbcon) + { + // Close the DB connection + try + { + dbcon.Close(); + } + catch + { + } + + // Try to reopen it + try + { + dbcon = new MySqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + m_log.Error("Unable to reconnect to database " + e.ToString()); + } + + // Run the query again + try + { + MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); + dbcommand.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + dbcommand.Parameters.AddWithValue(param.Key, param.Value); + } + + return (IDbCommand) dbcommand; + } + catch (Exception e) + { + // Return null if it fails. + m_log.Error("Failed during Query generation: " + e.ToString()); + return null; + } + } + } + } + + /// + /// Reads a region row from a database reader + /// + /// An active database reader + /// A region profile + public RegionProfileData readSimRow(IDataReader reader) + { + RegionProfileData retval = new RegionProfileData(); + + if (reader.Read()) + { + // Region Main gotta-have-or-we-return-null parts + if (!UInt64.TryParse(reader["regionHandle"].ToString(), out retval.regionHandle)) + return null; + if (!LLUUID.TryParse((string)reader["uuid"], out retval.UUID)) + return null; + + // non-critical parts + retval.regionName = (string)reader["regionName"]; + retval.originUUID = new LLUUID((string) reader["originUUID"]); + + // Secrets + retval.regionRecvKey = (string) reader["regionRecvKey"]; + retval.regionSecret = (string) reader["regionSecret"]; + retval.regionSendKey = (string) reader["regionSendKey"]; + + // Region Server + retval.regionDataURI = (string) reader["regionDataURI"]; + retval.regionOnline = false; // Needs to be pinged before this can be set. + retval.serverIP = (string) reader["serverIP"]; + retval.serverPort = (uint) reader["serverPort"]; + retval.serverURI = (string) reader["serverURI"]; + retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString()); + retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString()); + + // Location + retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString()); + retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString()); + retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString()); + + // Neighbours - 0 = No Override + retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString()); + retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString()); + retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString()); + retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString()); + + // Assets + retval.regionAssetURI = (string) reader["regionAssetURI"]; + retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; + retval.regionAssetSendKey = (string) reader["regionAssetSendKey"]; + + // Userserver + retval.regionUserURI = (string) reader["regionUserURI"]; + retval.regionUserRecvKey = (string) reader["regionUserRecvKey"]; + retval.regionUserSendKey = (string) reader["regionUserSendKey"]; + + // World Map Addition + LLUUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID); + LLUUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid); + } + else + { + return null; + } + return retval; + } + + /// + /// Reads a reservation row from a database reader + /// + /// An active database reader + /// A reservation data object + public ReservationData readReservationRow(IDataReader reader) + { + ReservationData retval = new ReservationData(); + if (reader.Read()) + { + retval.gridRecvKey = (string) reader["gridRecvKey"]; + retval.gridSendKey = (string) reader["gridSendKey"]; + retval.reservationCompany = (string) reader["resCompany"]; + retval.reservationMaxX = Convert.ToInt32(reader["resXMax"].ToString()); + retval.reservationMaxY = Convert.ToInt32(reader["resYMax"].ToString()); + retval.reservationMinX = Convert.ToInt32(reader["resXMin"].ToString()); + retval.reservationMinY = Convert.ToInt32(reader["resYMin"].ToString()); + retval.reservationName = (string) reader["resName"]; + retval.status = Convert.ToInt32(reader["status"].ToString()) == 1; + LLUUID.TryParse((string) reader["userUUID"], out retval.userUUID); + } + else + { + return null; + } + return retval; + } + + /// + /// Reads an agent row from a database reader + /// + /// An active database reader + /// A user session agent + public UserAgentData readAgentRow(IDataReader reader) + { + UserAgentData retval = new UserAgentData(); + + if (reader.Read()) + { + // Agent IDs + if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID)) + return null; + LLUUID.TryParse((string) reader["sessionID"], out retval.sessionID); + LLUUID.TryParse((string)reader["secureSessionID"], out retval.secureSessionID); + + // Agent Who? + retval.agentIP = (string) reader["agentIP"]; + retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString()); + retval.agentOnline = Convert.ToBoolean(Convert.ToInt16(reader["agentOnline"].ToString())); + + // Login/Logout times (UNIX Epoch) + retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString()); + retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); + + // Current position + retval.currentRegion = new LLUUID((string)reader["currentRegion"]); + retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); + LLVector3.TryParse((string) reader["currentPos"], out retval.currentPos); + } + else + { + return null; + } + return retval; + } + + /// + /// Reads a user profile from an active data reader + /// + /// An active database reader + /// A user profile + public UserProfileData readUserRow(IDataReader reader) + { + UserProfileData retval = new UserProfileData(); + + if (reader.Read()) + { + if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID)) + return null; + retval.username = (string) reader["username"]; + retval.surname = (string) reader["lastname"]; + + retval.passwordHash = (string) reader["passwordHash"]; + retval.passwordSalt = (string) reader["passwordSalt"]; + + retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString()); + retval.homeLocation = new LLVector3( + Convert.ToSingle(reader["homeLocationX"].ToString()), + Convert.ToSingle(reader["homeLocationY"].ToString()), + Convert.ToSingle(reader["homeLocationZ"].ToString())); + retval.homeLookAt = new LLVector3( + Convert.ToSingle(reader["homeLookAtX"].ToString()), + Convert.ToSingle(reader["homeLookAtY"].ToString()), + Convert.ToSingle(reader["homeLookAtZ"].ToString())); + + retval.created = Convert.ToInt32(reader["created"].ToString()); + retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); + + retval.userInventoryURI = (string) reader["userInventoryURI"]; + retval.userAssetURI = (string) reader["userAssetURI"]; + + retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); + retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); + + if (reader.IsDBNull(reader.GetOrdinal("profileAboutText"))) + retval.profileAboutText = ""; + else + retval.profileAboutText = (string) reader["profileAboutText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstText"))) + retval.profileFirstText = ""; + else + retval.profileFirstText = (string)reader["profileFirstText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileImage"))) + retval.profileImage = LLUUID.Zero; + else + LLUUID.TryParse((string)reader["profileImage"], out retval.profileImage); + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage"))) + retval.profileFirstImage = LLUUID.Zero; + else + LLUUID.TryParse((string)reader["profileFirstImage"], out retval.profileFirstImage); + + if(reader.IsDBNull(reader.GetOrdinal("webLoginKey"))) + { + retval.webLoginKey = LLUUID.Zero; + } + else + { + LLUUID.TryParse((string)reader["webLoginKey"], out retval.webLoginKey); + } + } + else + { + return null; + } + return retval; + } + + /// + /// Inserts a new row into the log database + /// + /// The daemon which triggered this event + /// Who were we operating on when this occured (region UUID, user UUID, etc) + /// The method call where the problem occured + /// The arguments passed to the method + /// How critical is this? + /// Extra message info + /// Saved successfully? + public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority, + string logMessage) + { + string sql = "INSERT INTO logs (`target`, `server`, `method`, `arguments`, `priority`, `message`) VALUES "; + sql += "(?target, ?server, ?method, ?arguments, ?priority, ?message)"; + + Dictionary parameters = new Dictionary(); + parameters["?server"] = serverDaemon; + parameters["?target"] = target; + parameters["?method"] = methodCall; + parameters["?arguments"] = arguments; + parameters["?priority"] = priority.ToString(); + parameters["?message"] = logMessage; + + bool returnval = false; + + try + { + IDbCommand result = 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 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? + public 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) + { + m_log.Debug("[MySQLManager]: Fetching profile for " + uuid.ToString()); + string sql = + "INSERT INTO users (`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"] = string.Empty; + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + m_log.Debug("[MySQLManager]: Fetch user retval == " + returnval.ToString()); + return returnval; + } + + /// + /// 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? + public bool updateUserRow(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 = "UPDATE users SET `username` = ?username , `lastname` = ?lastname "; + sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , "; + sql += "`homeRegion` = ?homeRegion , `homeLocationX` = ?homeLocationX , "; + sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , "; + sql += "`homeLookAtX` = ?homeLookAtX , `homeLookAtY` = ?homeLookAtY , "; + sql += "`homeLookAtZ` = ?homeLookAtZ , `created` = ?created , `lastLogin` = ?lastLogin , "; + sql += "`userInventoryURI` = ?userInventoryURI , `userAssetURI` = ?userAssetURI , "; + sql += "`profileCanDoMask` = ?profileCanDoMask , `profileWantDoMask` = ?profileWantDoMask , "; + sql += "`profileAboutText` = ?profileAboutText , `profileFirstText` = ?profileFirstText, "; + sql += "`profileImage` = ?profileImage , `profileFirstImage` = ?profileFirstImage , "; + sql += "`webLoginKey` = ?webLoginKey WHERE UUID = ?UUID"; + + 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"] = inventoryURI; + parameters["?userAssetURI"] = assetURI; + parameters["?profileCanDoMask"] = "0"; + parameters["?profileWantDoMask"] = "0"; + parameters["?profileAboutText"] = aboutText; + parameters["?profileFirstText"] = firstText; + parameters["?profileImage"] = profileImage.ToString(); + parameters["?profileFirstImage"] = firstImage.ToString(); + parameters["?webLoginKey"] = webLoginKey.ToString(); + + bool returnval = false; + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + m_log.Debug("[MySQLManager]: update user retval == " + returnval.ToString()); + return returnval; + } + + /// + /// Inserts a new region into the database + /// + /// The region to insert + /// Success? + public bool insertRegion(RegionProfileData regiondata) + { + bool GRID_ONLY_UPDATE_NECESSARY_DATA = false; + + string sql = String.Empty; + if (GRID_ONLY_UPDATE_NECESSARY_DATA) + { + sql += "INSERT INTO "; + } + else + { + sql += "REPLACE INTO "; + } + + sql += "regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; + sql += + "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; + + // part of an initial brutish effort to provide accurate information (as per the xml region spec) + // wrt the ownership of a given region + // the (very bad) assumption is that this value is being read and handled inconsistently or + // not at all. Current strategy is to put the code in place to support the validity of this information + // and to roll forward debugging any issues from that point + // + // this particular section of the mod attempts to implement the commit of a supplied value + // server for the UUID of the region's owner (master avatar). It consists of the addition of the column and value to the relevant sql, + // as well as the related parameterization + sql += + "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID) VALUES "; + + sql += "(?regionHandle, ?regionName, ?uuid, ?regionRecvKey, ?regionSecret, ?regionSendKey, ?regionDataURI, "; + sql += + "?serverIP, ?serverPort, ?serverURI, ?locX, ?locY, ?locZ, ?eastOverrideHandle, ?westOverrideHandle, ?southOverrideHandle, ?northOverrideHandle, ?regionAssetURI, ?regionAssetRecvKey, "; + sql += + "?regionAssetSendKey, ?regionUserURI, ?regionUserRecvKey, ?regionUserSendKey, ?regionMapTexture, ?serverHttpPort, ?serverRemotingPort, ?owner_uuid, ?originUUID)"; + + if (GRID_ONLY_UPDATE_NECESSARY_DATA) + { + sql += "ON DUPLICATE KEY UPDATE serverIP = ?serverIP, serverPort = ?serverPort, serverURI = ?serverURI, owner_uuid - ?owner_uuid;"; + } + else + { + sql += ";"; + } + + Dictionary parameters = new Dictionary(); + + parameters["?regionHandle"] = regiondata.regionHandle.ToString(); + parameters["?regionName"] = regiondata.regionName.ToString(); + parameters["?uuid"] = regiondata.UUID.ToString(); + parameters["?regionRecvKey"] = regiondata.regionRecvKey.ToString(); + parameters["?regionSecret"] = regiondata.regionSecret.ToString(); + parameters["?regionSendKey"] = regiondata.regionSendKey.ToString(); + parameters["?regionDataURI"] = regiondata.regionDataURI.ToString(); + parameters["?serverIP"] = regiondata.serverIP.ToString(); + parameters["?serverPort"] = regiondata.serverPort.ToString(); + parameters["?serverURI"] = regiondata.serverURI.ToString(); + parameters["?locX"] = regiondata.regionLocX.ToString(); + parameters["?locY"] = regiondata.regionLocY.ToString(); + parameters["?locZ"] = regiondata.regionLocZ.ToString(); + parameters["?eastOverrideHandle"] = regiondata.regionEastOverrideHandle.ToString(); + parameters["?westOverrideHandle"] = regiondata.regionWestOverrideHandle.ToString(); + parameters["?northOverrideHandle"] = regiondata.regionNorthOverrideHandle.ToString(); + parameters["?southOverrideHandle"] = regiondata.regionSouthOverrideHandle.ToString(); + parameters["?regionAssetURI"] = regiondata.regionAssetURI.ToString(); + parameters["?regionAssetRecvKey"] = regiondata.regionAssetRecvKey.ToString(); + parameters["?regionAssetSendKey"] = regiondata.regionAssetSendKey.ToString(); + parameters["?regionUserURI"] = regiondata.regionUserURI.ToString(); + parameters["?regionUserRecvKey"] = regiondata.regionUserRecvKey.ToString(); + parameters["?regionUserSendKey"] = regiondata.regionUserSendKey.ToString(); + parameters["?regionMapTexture"] = regiondata.regionMapTextureID.ToString(); + parameters["?serverHttpPort"] = regiondata.httpPort.ToString(); + parameters["?serverRemotingPort"] = regiondata.remotingPort.ToString(); + parameters["?owner_uuid"] = regiondata.owner_uuid.ToString(); + parameters["?originUUID"] = regiondata.originUUID.ToString(); + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + //Console.WriteLine(result.CommandText); + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Delete a region from the database + /// + /// The region to insert + /// Success? + //public bool deleteRegion(RegionProfileData regiondata) + public bool deleteRegion(string uuid) + { + bool returnval = false; + + string sql = "DELETE FROM regions WHERE uuid = ?uuid;"; + + Dictionary parameters = new Dictionary(); + + try + { + parameters["?uuid"] = uuid; + + IDbCommand result = Query(sql, parameters); + + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Creates a new agent and inserts it into the database + /// + /// The agent data to be inserted + /// Success? + public bool insertAgentRow(UserAgentData agentdata) + { + string sql = String.Empty; + sql += "REPLACE INTO "; + sql += "agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES "; + sql += "(?UUID, ?sessionID, ?secureSessionID, ?agentIP, ?agentPort, ?agentOnline, ?loginTime, ?logoutTime, ?currentRegion, ?currentHandle, ?currentPos);"; + Dictionary parameters = new Dictionary(); + + parameters["?UUID"] = agentdata.UUID.ToString(); + parameters["?sessionID"] = agentdata.sessionID.ToString(); + parameters["?secureSessionID"] = agentdata.secureSessionID.ToString(); + parameters["?agentIP"] = agentdata.agentIP.ToString(); + parameters["?agentPort"] = agentdata.agentPort.ToString(); + parameters["?agentOnline"] = (agentdata.agentOnline == true) ? "1" : "0"; + parameters["?loginTime"] = agentdata.loginTime.ToString(); + parameters["?logoutTime"] = agentdata.logoutTime.ToString(); + parameters["?currentRegion"] = agentdata.currentRegion.ToString(); + parameters["?currentHandle"] = agentdata.currentHandle.ToString(); + parameters["?currentPos"] = "<" + ((int)agentdata.currentPos.X).ToString() + "," + ((int)agentdata.currentPos.Y).ToString() + "," + ((int)agentdata.currentPos.Z).ToString() + ">"; + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + //Console.WriteLine(result.CommandText); + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + } +} -- cgit v1.1