using System; using System.Collections.Generic; using System.Text; using System.Data; // MySQL Native using MySql; using MySql.Data; using MySql.Data.Types; using MySql.Data.MySqlClient; using OpenGrid.Framework.Data; namespace OpenGrid.Framework.Data.MySQL { class MySQLManager { IDbConnection dbcon; 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); dbcon.Open(); System.Console.WriteLine("MySQL connection established"); } catch (Exception e) { throw new Exception("Error initialising MySql Database: " + e.ToString()); } } /// /// 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) { Console.WriteLine("Unable to reconnect to database " + e.ToString()); } } } /// /// 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.Add(param.Key, param.Value); } return (IDbCommand)dbcommand; } catch { lock (dbcon) { // Close the DB connection try { dbcon.Close(); } catch { } // Try reopen it try { dbcon = new MySqlConnection(connectionString); dbcon.Open(); } catch (Exception e) { Console.WriteLine("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.Add(param.Key, param.Value); } return (IDbCommand)dbcommand; } catch (Exception e) { // Return null if it fails. Console.WriteLine("Failed during Query generation: " + e.ToString()); return null; } } } } public SimProfileData readSimRow(IDataReader reader) { SimProfileData retval = new SimProfileData(); if (reader.Read()) { // Region Main retval.regionHandle = Convert.ToUInt64(reader["regionHandle"].ToString()); retval.regionName = (string)reader["regionName"]; retval.UUID = new libsecondlife.LLUUID((string)reader["uuid"]); // 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"]; // 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 string tempRegionMap = reader["regionMapTexture"].ToString(); if (tempRegionMap != "") { retval.regionMapTextureID = new libsecondlife.LLUUID(tempRegionMap); } else { retval.regionMapTextureID = new libsecondlife.LLUUID(); } } else { return null; } return retval; } public UserAgentData readAgentRow(IDataReader reader) { UserAgentData retval = new UserAgentData(); if (reader.Read()) { // Agent IDs retval.UUID = new libsecondlife.LLUUID((string)reader["UUID"]); retval.sessionID = new libsecondlife.LLUUID((string)reader["sessionID"]); retval.secureSessionID = new libsecondlife.LLUUID((string)reader["secureSessionID"]); // Agent Who? retval.agentIP = (string)reader["agentIP"]; retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString()); retval.agentOnline = Convert.ToBoolean(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 = (string)reader["currentRegion"]; retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); libsecondlife.LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos); } else { return null; } return retval; } public UserProfileData readUserRow(IDataReader reader) { UserProfileData retval = new UserProfileData(); if (reader.Read()) { retval.UUID = new libsecondlife.LLUUID((string)reader["UUID"]); 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 libsecondlife.LLVector3( Convert.ToSingle(reader["homeLocationX"].ToString()), Convert.ToSingle(reader["homeLocationY"].ToString()), Convert.ToSingle(reader["homeLocationZ"].ToString())); retval.homeLookAt = new libsecondlife.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()); retval.profileAboutText = (string)reader["profileAboutText"]; retval.profileFirstText = (string)reader["profileFirstText"]; retval.profileImage = new libsecondlife.LLUUID((string)reader["profileImage"]); retval.profileFirstImage = new libsecondlife.LLUUID((string)reader["profileFirstImage"]); } else { return null; } return retval; } /// /// Reads a list of inventory folders returned by a query. /// /// A MySQL Data Reader /// A List containing inventory folders public List readInventoryFolders(IDataReader reader) { List rows = new List(); while(reader.Read()) { try { InventoryFolderBase folder = new InventoryFolderBase(); folder.agentID = new libsecondlife.LLUUID((string)reader["agentID"]); folder.parentID = new libsecondlife.LLUUID((string)reader["parentFolderID"]); folder.folderID = new libsecondlife.LLUUID((string)reader["folderID"]); folder.name = (string)reader["folderName"]; rows.Add(folder); } catch (Exception e) { Console.WriteLine(e.ToString()); } } return rows; } /// /// Reads a collection of items from an SQL result /// /// The SQL Result /// A List containing Inventory Items public List readInventoryItems(IDataReader reader) { List rows = new List(); while (reader.Read()) { try { InventoryItemBase item = new InventoryItemBase(); item.assetID = new libsecondlife.LLUUID((string)reader["assetID"]); item.avatarID = new libsecondlife.LLUUID((string)reader["avatarID"]); item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"].ToString()); item.inventoryDescription = (string)reader["inventoryDescription"]; item.inventoryID = new libsecondlife.LLUUID((string)reader["inventoryID"]); item.inventoryName = (string)reader["inventoryName"]; item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"].ToString()); item.parentFolderID = new libsecondlife.LLUUID((string)reader["parentFolderID"]); item.type = Convert.ToInt32(reader["type"].ToString()); rows.Add(item); } catch (Exception e) { Console.WriteLine(e.ToString()); } } return rows; } 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) { Console.WriteLine(e.ToString()); return false; } return returnval; } public bool insertItem(InventoryItemBase item) { string sql = "REPLACE INTO inventoryitems (inventoryID, assetID, type, parentFolderID, avatarID, inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions) VALUES "; sql += "(?inventoryID, ?assetID, ?type, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription, ?inventoryNextPermissions, ?inventoryCurrentPermissions)"; Dictionary parameters = new Dictionary(); parameters["?inventoryID"] = item.inventoryID.ToStringHyphenated(); parameters["?assetID"] = item.assetID.ToStringHyphenated(); parameters["?type"] = item.type.ToString(); parameters["?parentFolderID"] = item.parentFolderID.ToStringHyphenated(); parameters["?avatarID"] = item.avatarID.ToStringHyphenated(); parameters["?inventoryName"] = item.inventoryName; parameters["?inventoryDescription"] = item.inventoryDescription; parameters["?inventoryNextPermissions"] = item.inventoryNextPermissions.ToString(); parameters["?inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString(); bool returnval = false; try { IDbCommand result = Query(sql, parameters); if (result.ExecuteNonQuery() == 1) returnval = true; result.Dispose(); } catch (Exception e) { Console.WriteLine(e.ToString()); return false; } return returnval; } public bool insertFolder(InventoryFolderBase folder) { string sql = "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName) VALUES "; sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName)"; Dictionary parameters = new Dictionary(); parameters["?folderID"] = folder.folderID.ToStringHyphenated(); parameters["?agentID"] = folder.agentID.ToStringHyphenated(); parameters["?parentFolderID"] = folder.parentID.ToStringHyphenated(); parameters["?folderName"] = folder.name; bool returnval = false; try { IDbCommand result = Query(sql, parameters); if (result.ExecuteNonQuery() == 1) returnval = true; result.Dispose(); } catch (Exception e) { Console.WriteLine(e.ToString()); return false; } return returnval; } public bool insertRow(SimProfileData profile) { string sql = "REPLACE INTO regions (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) 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);"; Dictionary parameters = new Dictionary(); parameters["?regionHandle"] = profile.regionHandle.ToString(); parameters["?regionName"] = profile.regionName.ToString(); parameters["?uuid"] = profile.UUID.ToStringHyphenated(); parameters["?regionRecvKey"] = profile.regionRecvKey.ToString(); parameters["?regionSecret"] = profile.regionSecret.ToString(); parameters["?regionSendKey"] = profile.regionSendKey.ToString(); parameters["?regionDataURI"] = profile.regionDataURI.ToString(); parameters["?serverIP"] = profile.serverIP.ToString(); parameters["?serverPort"] = profile.serverPort.ToString(); parameters["?serverURI"] = profile.serverURI.ToString(); parameters["?locX"] = profile.regionLocX.ToString(); parameters["?locY"] = profile.regionLocY.ToString(); parameters["?locZ"] = profile.regionLocZ.ToString(); parameters["?eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString(); parameters["?westOverrideHandle"] = profile.regionWestOverrideHandle.ToString(); parameters["?northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString(); parameters["?southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString(); parameters["?regionAssetURI"] = profile.regionAssetURI.ToString(); parameters["?regionAssetRecvKey"] = profile.regionAssetRecvKey.ToString(); parameters["?regionAssetSendKey"] = profile.regionAssetSendKey.ToString(); parameters["?regionUserURI"] = profile.regionUserURI.ToString(); parameters["?regionUserRecvKey"] = profile.regionUserRecvKey.ToString(); parameters["?regionUserSendKey"] = profile.regionUserSendKey.ToString(); parameters["?regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated(); bool returnval = false; try { IDbCommand result = Query(sql, parameters); //Console.WriteLine(result.CommandText); if (result.ExecuteNonQuery() == 1) returnval = true; result.Dispose(); } catch (Exception e) { Console.WriteLine(e.ToString()); return false; } return returnval; } } }