From bcd3a5b28bc63426e5ac1f4642afca4a391ece28 Mon Sep 17 00:00:00 2001 From: lbsa71 Date: Wed, 13 Feb 2008 12:06:09 +0000 Subject: * Introduced customizable table names on MySQL and MSSQL UserData plug-ins. [Provided by openlifegrid.com] --- OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 46 +++++++++++---- OpenSim/Framework/Data.MySQL/MySQLManager.cs | 2 + OpenSim/Framework/Data.MySQL/MySQLUserData.cs | 80 +++++++++++++++++---------- 3 files changed, 86 insertions(+), 42 deletions(-) (limited to 'OpenSim') diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs index ed9929c..60e6df4 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs @@ -46,6 +46,10 @@ namespace OpenSim.Framework.Data.MSSQL /// public MSSQLManager database; + private string m_agentsTableName; + private string m_usersTableName; + private string m_userFriendsTableName; + /// /// Loads and initialises the MySQL storage plugin /// @@ -53,12 +57,30 @@ namespace OpenSim.Framework.Data.MSSQL { // Load from an INI file connection details // TODO: move this to XML? - IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); - string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); - string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); - string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); - string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); - string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + 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, @@ -82,7 +104,7 @@ namespace OpenSim.Framework.Data.MSSQL param["second"] = last; IDbCommand result = - database.Query("SELECT * FROM users WHERE username = @first AND lastname = @second", param); + database.Query("SELECT * FROM "+m_usersTableName+" WHERE username = @first AND lastname = @second", param); IDataReader reader = result.ExecuteReader(); UserProfileData row = database.readUserRow(reader); @@ -150,7 +172,7 @@ namespace OpenSim.Framework.Data.MSSQL IDbCommand result = database.Query( - "SELECT UUID,username,surname FROM users WHERE username = @first AND lastname = @second", + "SELECT UUID,username,surname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param); IDataReader reader = result.ExecuteReader(); @@ -186,7 +208,7 @@ namespace OpenSim.Framework.Data.MSSQL IDbCommand result = database.Query( - "SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", + "SELECT UUID,username,surname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @second", param); IDataReader reader = result.ExecuteReader(); @@ -223,7 +245,7 @@ namespace OpenSim.Framework.Data.MSSQL Dictionary param = new Dictionary(); param["uuid"] = uuid.ToString(); - IDbCommand result = database.Query("SELECT * FROM users WHERE UUID = @uuid", param); + IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param); IDataReader reader = result.ExecuteReader(); UserProfileData row = database.readUserRow(reader); @@ -278,7 +300,7 @@ namespace OpenSim.Framework.Data.MSSQL Dictionary param = new Dictionary(); param["uuid"] = uuid.ToString(); - IDbCommand result = database.Query("SELECT * FROM agents WHERE UUID = @uuid", param); + IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param); IDataReader reader = result.ExecuteReader(); UserAgentData row = database.readAgentRow(reader); @@ -342,7 +364,7 @@ namespace OpenSim.Framework.Data.MSSQL public bool UpdateUserProfile(UserProfileData user) { - SqlCommand command = new SqlCommand("UPDATE users set UUID = @uuid, " + + SqlCommand command = new SqlCommand("UPDATE " + m_usersTableName + " set UUID = @uuid, " + "username = @username, " + "lastname = @lastname," + "passwordHash = @passwordHash," + diff --git a/OpenSim/Framework/Data.MySQL/MySQLManager.cs b/OpenSim/Framework/Data.MySQL/MySQLManager.cs index e763bfa..0ec976c 100644 --- a/OpenSim/Framework/Data.MySQL/MySQLManager.cs +++ b/OpenSim/Framework/Data.MySQL/MySQLManager.cs @@ -200,7 +200,9 @@ namespace OpenSim.Framework.Data.MySQL string tableName = (string) tables["TABLE_NAME"]; string comment = (string) tables["TABLE_COMMENT"]; if (tableList.ContainsKey(tableName)) + { tableList[tableName] = comment; + } } catch (Exception e) { diff --git a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs index 0db727c..bc18376 100644 --- a/OpenSim/Framework/Data.MySQL/MySQLUserData.cs +++ b/OpenSim/Framework/Data.MySQL/MySQLUserData.cs @@ -46,20 +46,43 @@ namespace OpenSim.Framework.Data.MySQL /// public MySQLManager database; + private string m_agentsTableName; + private string m_usersTableName; + private string m_userFriendsTableName; + /// /// Loads and initialises the MySQL storage plugin /// public void Initialise() { // Load from an INI file connection details - // TODO: move this to XML? - IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); - string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname"); - string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database"); - string settingUsername = GridDataMySqlFile.ParseFileReadValue("username"); - string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); - string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling"); - string settingPort = GridDataMySqlFile.ParseFileReadValue("port"); + // TODO: move this to XML? Yes, PLEASE! + + IniFile iniFile = new IniFile("mysql_connection.ini"); + string settingHostname = iniFile.ParseFileReadValue("hostname"); + string settingDatabase = iniFile.ParseFileReadValue("database"); + string settingUsername = iniFile.ParseFileReadValue("username"); + string settingPassword = iniFile.ParseFileReadValue("password"); + string settingPooling = iniFile.ParseFileReadValue("pooling"); + string settingPort = iniFile.ParseFileReadValue("port"); + + 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 MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling, @@ -77,14 +100,14 @@ namespace OpenSim.Framework.Data.MySQL { Dictionary tableList = new Dictionary(); - tableList["agents"] = null; - tableList["users"] = null; - tableList["userfriends"] = null; + tableList[m_agentsTableName] = null; + tableList[m_usersTableName] = null; + tableList[m_userFriendsTableName] = null; database.GetTableVersion(tableList); - UpgradeAgentsTable(tableList["agents"]); - UpgradeUsersTable(tableList["users"]); - UpgradeFriendsTable(tableList["userfriends"]); + UpgradeAgentsTable(tableList[m_agentsTableName]); + UpgradeUsersTable(tableList[m_usersTableName]); + UpgradeFriendsTable(tableList[m_userFriendsTableName]); } @@ -153,7 +176,7 @@ namespace OpenSim.Framework.Data.MySQL param["?second"] = last; IDbCommand result = - database.Query("SELECT * FROM users WHERE username = ?first AND lastname = ?second", param); + database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param); IDataReader reader = result.ExecuteReader(); UserProfileData row = database.readUserRow(reader); @@ -189,7 +212,7 @@ namespace OpenSim.Framework.Data.MySQL { IDbCommand adder = database.Query( - "INSERT INTO `userfriends` " + + "INSERT INTO `" + m_userFriendsTableName + "` " + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + "VALUES " + "(?ownerID,?friendID,?friendPerms,?datetimestamp)", @@ -198,7 +221,7 @@ namespace OpenSim.Framework.Data.MySQL adder = database.Query( - "INSERT INTO `userfriends` " + + "INSERT INTO `" + m_userFriendsTableName + "` " + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + "VALUES " + "(?friendID,?ownerID,?friendPerms,?datetimestamp)", @@ -228,15 +251,13 @@ namespace OpenSim.Framework.Data.MySQL { IDbCommand updater = database.Query( - "delete from userfriends " + - "where ownerID = ?ownerID and friendID = ?friendID", + "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID", param); updater.ExecuteNonQuery(); updater = database.Query( - "delete from userfriends " + - "where ownerID = ?friendID and friendID = ?ownerID", + "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID", param); updater.ExecuteNonQuery(); @@ -263,8 +284,8 @@ namespace OpenSim.Framework.Data.MySQL { IDbCommand updater = database.Query( - "update userfriends " + - "SET friendPerms = ?friendPerms " + + "update " + m_userFriendsTableName + + " SET friendPerms = ?friendPerms " + "where ownerID = ?ownerID and friendID = ?friendID", param); updater.ExecuteNonQuery(); @@ -294,7 +315,7 @@ namespace OpenSim.Framework.Data.MySQL //Left Join userfriends to itself IDbCommand result = database.Query( - "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from userfriends as a, userfriends as b" + + "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(); @@ -353,7 +374,7 @@ namespace OpenSim.Framework.Data.MySQL { IDbCommand result = database.Query( - "SELECT UUID,username,lastname FROM users WHERE username like ?first AND lastname like ?second LIMIT 100", + "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first AND lastname like ?second LIMIT 100", param); IDataReader reader = result.ExecuteReader(); @@ -388,7 +409,7 @@ namespace OpenSim.Framework.Data.MySQL IDbCommand result = database.Query( - "SELECT UUID,username,lastname FROM users WHERE username like ?first OR lastname like ?first LIMIT 100", + "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first OR lastname like ?first LIMIT 100", param); IDataReader reader = result.ExecuteReader(); @@ -425,7 +446,7 @@ namespace OpenSim.Framework.Data.MySQL Dictionary param = new Dictionary(); param["?uuid"] = uuid.ToString(); - IDbCommand result = database.Query("SELECT * FROM users WHERE UUID = ?uuid", param); + IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param); IDataReader reader = result.ExecuteReader(); UserProfileData row = database.readUserRow(reader); @@ -479,8 +500,7 @@ namespace OpenSim.Framework.Data.MySQL { IDbCommand updater = database.Query( - "update users " + - "SET webLoginKey = ?webLoginKey " + + "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " + "where UUID = ?UUID", param); updater.ExecuteNonQuery(); @@ -513,7 +533,7 @@ namespace OpenSim.Framework.Data.MySQL Dictionary param = new Dictionary(); param["?uuid"] = uuid.ToString(); - IDbCommand result = database.Query("SELECT * FROM agents WHERE UUID = ?uuid", param); + IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param); IDataReader reader = result.ExecuteReader(); UserAgentData row = database.readAgentRow(reader); -- cgit v1.1