From 4880bd121e737e0a5c384fddf61bd220c77ef88a Mon Sep 17 00:00:00 2001 From: lbsa71 Date: Wed, 13 Feb 2008 13:39:51 +0000 Subject: * Split out MSSQLManager Test/Init into each provider. * Made regions table name configurable (MSSQL only) * Added a note in ini.example pointing out that the sql resources have to change if you change table names * Removed duplicate picker method from GridData interface [Provided by openlifegrid.com] --- OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs | 127 ++++++--------------- OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs | 15 +++ OpenSim/Framework/Data.MSSQL/MSSQLManager.cs | 67 +---------- OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 54 +++++++-- .../Data.MSSQL/Resources/Mssql-regions.sql | 41 +++++++ OpenSim/Framework/Data.MySQL/MySQLGridData.cs | 86 +------------- OpenSim/Framework/Data/GridData.cs | 3 - 7 files changed, 138 insertions(+), 255 deletions(-) create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql (limited to 'OpenSim/Framework') diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs index 28eec3e..0df88b6 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs @@ -47,21 +47,48 @@ namespace OpenSim.Framework.Data.MSSQL /// private MSSQLManager database; + private string m_regionsTableName; + /// /// Initialises the Grid Interface /// public void Initialise() { - 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_regionsTableName = iniFile.ParseFileReadValue("regionstablename"); + if (m_regionsTableName == null) + { + m_regionsTableName = "regions"; + } database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + + TestTables(); + } + + private void TestTables() + { + IDbCommand cmd = database.Query("SELECT * FROM "+m_regionsTableName, new Dictionary()); + + try + { + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (Exception) + { + m_log.Info("[DATASTORE]: MSSQL Database doesn't exist... creating"); + database.ExecuteResourceSql("Mssql-regions.sql"); + } } /// @@ -115,7 +142,7 @@ namespace OpenSim.Framework.Data.MSSQL { Dictionary param = new Dictionary(); param["handle"] = handle.ToString(); - IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = @handle", param); + IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param); reader = result.ExecuteReader(); RegionProfileData row = database.getRegionRow(reader); @@ -134,89 +161,7 @@ namespace OpenSim.Framework.Data.MSSQL return null; } - /// - /// // Returns a list of avatar and UUIDs that match the query - /// - 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,surname FROM users WHERE username = @first AND lastname = @second", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - 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]; - param["second"] = querysplit[1]; - - IDbCommand result = - database.Query( - "SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - returnlist.Add(user); - } - reader.Close(); - result.Dispose(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return returnlist; - } - } - return returnlist; - } - + /// /// Returns a sim profile from it's UUID /// @@ -226,7 +171,7 @@ namespace OpenSim.Framework.Data.MSSQL { Dictionary param = new Dictionary(); param["uuid"] = uuid.ToString(); - IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); + IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param); IDataReader reader = result.ExecuteReader(); RegionProfileData row = database.getRegionRow(reader); diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs index c3ec7c6..1e83496 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs @@ -25,6 +25,9 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * */ +using System.Collections.Generic; +using System.Data; + namespace OpenSim.Framework.Data.MSSQL { /// @@ -52,6 +55,18 @@ namespace OpenSim.Framework.Data.MSSQL database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + + IDbCommand cmd = database.Query("select top 1 * from logs", new Dictionary()); + try + { + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch + { + database.ExecuteResourceSql("Mssql-logs.sql"); + } + } /// diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs index e54cde1..5c3e94f 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs @@ -52,16 +52,8 @@ namespace OpenSim.Framework.Data.MSSQL /// /// Connection string for ADO.net /// - private string connectionString; + private readonly string connectionString; - /// - /// Initialises and creates a new Sql connection and maintains it. - /// - /// The Sql server being connected to - /// The name of the Sql 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 MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, string password) { @@ -71,7 +63,6 @@ namespace OpenSim.Framework.Data.MSSQL ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + password + ";"; dbcon = new SqlConnection(connectionString); - TestTables(dbcon); dbcon.Open(); } catch (Exception e) @@ -80,61 +71,6 @@ namespace OpenSim.Framework.Data.MSSQL } } - private bool TestTables(IDbConnection conn) - { - IDbCommand cmd = Query("SELECT * FROM regions", new Dictionary()); - //SqlCommand cmd = (SqlCommand)dbcon.CreateCommand(); - //cmd.CommandText = "SELECT * FROM regions"; - try - { - conn.Open(); - cmd.ExecuteNonQuery(); - cmd.Dispose(); - conn.Close(); - } - catch (Exception) - { - m_log.Info("[DATASTORE]: MSSQL Database doesn't exist... creating"); - InitDB(conn); - } - cmd = Query("select top 1 webLoginKey from users", new Dictionary()); - try - { - conn.Open(); - cmd.ExecuteNonQuery(); - cmd.Dispose(); - conn.Close(); - } - catch (Exception) - { - conn.Open(); - cmd = Query("alter table users add column [webLoginKey] varchar(36) default NULL", new Dictionary()); - cmd.ExecuteNonQuery(); - cmd.Dispose(); - conn.Close(); - } - return true; - } - - private void InitDB(IDbConnection conn) - { - string createRegions = defineTable(createRegionsTable()); - Dictionary param = new Dictionary(); - IDbCommand pcmd = Query(createRegions, param); - if (conn.State == ConnectionState.Closed) - { - conn.Open(); - } - pcmd.ExecuteNonQuery(); - pcmd.Dispose(); - - ExecuteResourceSql("Mssql-users.sql"); - ExecuteResourceSql("Mssql-agents.sql"); - ExecuteResourceSql("Mssql-logs.sql"); - - conn.Close(); - } - private DataTable createRegionsTable() { DataTable regions = new DataTable("regions"); @@ -253,7 +189,6 @@ namespace OpenSim.Framework.Data.MSSQL { try { - //string connectionString = "Data Source=WRK-OU-738\\SQLEXPRESS;Initial Catalog=rex;Persist Security Info=True;User ID=sa;Password=rex"; // Close the DB connection dbcon.Close(); // Try reopen it diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs index 60e6df4..aacbb2d 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs @@ -85,8 +85,42 @@ namespace OpenSim.Framework.Data.MSSQL database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + + if (!TestTables()) + { + database.ExecuteResourceSql("Mssql-agents.sql"); + database.ExecuteResourceSql("Mssql-users.sql"); + database.ExecuteResourceSql("Mssql-userfriends.sql"); + } } + private bool TestTables() + { + IDbCommand cmd = database.Query("select top 1 webLoginKey from "+m_usersTableName, new Dictionary()); + try + { + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch + { + database.Query("alter table "+m_usersTableName+" add column [webLoginKey] varchar(36) default NULL", new Dictionary()); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + + cmd = database.Query("select top 1 * from "+m_usersTableName, new Dictionary()); + try + { + cmd.ExecuteNonQuery(); + } + catch + { + return false; + } + + return true; + } /// /// Searches the database for a specified user profile by name components /// @@ -104,7 +138,7 @@ namespace OpenSim.Framework.Data.MSSQL param["second"] = last; IDbCommand result = - database.Query("SELECT * FROM "+m_usersTableName+" 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); @@ -153,7 +187,7 @@ namespace OpenSim.Framework.Data.MSSQL m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); } - + public List GeneratePickerResults(LLUUID queryID, string query) { @@ -180,9 +214,9 @@ namespace OpenSim.Framework.Data.MSSQL 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["surname"]; + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; returnlist.Add(user); } reader.Close(); @@ -216,9 +250,9 @@ namespace OpenSim.Framework.Data.MSSQL 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["surname"]; + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; returnlist.Add(user); } reader.Close(); @@ -323,7 +357,7 @@ namespace OpenSim.Framework.Data.MSSQL UserProfileData user = GetUserByUUID(AgentID); user.webLoginKey = WebLoginKey; UpdateUserProfile(user); - + } /// /// Creates a new users profile @@ -342,7 +376,7 @@ namespace OpenSim.Framework.Data.MSSQL user.lastLogin, user.userInventoryURI, user.userAssetURI, user.profileCanDoMask, user.profileWantDoMask, user.profileAboutText, user.profileFirstText, user.profileImage, - user.profileFirstImage,user.webLoginKey); + user.profileFirstImage, user.webLoginKey); } } catch (Exception e) diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql new file mode 100644 index 0000000..b46da16 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql @@ -0,0 +1,41 @@ +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [db_owner].[regions]( + [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL, + [regionRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionSecret] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionDataURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverIP] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locX] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locY] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locZ] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [eastOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [westOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [southOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [northOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionMapTexture] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverHttpPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverRemotingPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, +PRIMARY KEY CLUSTERED +( + [uuid] ASC +)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] + +GO +SET ANSI_PADDING OFF diff --git a/OpenSim/Framework/Data.MySQL/MySQLGridData.cs b/OpenSim/Framework/Data.MySQL/MySQLGridData.cs index d62c286..3737e48 100644 --- a/OpenSim/Framework/Data.MySQL/MySQLGridData.cs +++ b/OpenSim/Framework/Data.MySQL/MySQLGridData.cs @@ -206,91 +206,7 @@ namespace OpenSim.Framework.Data.MySQL return null; } } - - /// - /// // Returns a list of avatar and UUIDs that match the query - /// - public List GeneratePickerResults(LLUUID queryID, string query) - { - List returnlist = new List(); - - Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]"); - - string[] querysplit; - querysplit = query.Split(' '); - if (querysplit.Length == 2) - { - Dictionary param = new Dictionary(); - param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; - param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%"; - try - { - lock (database) - { - IDbCommand result = - database.Query( - "SELECT UUID,username,surname FROM users WHERE username like ?first AND lastname like ?second LIMIT 100", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - 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"] = objAlphaNumericPattern.Replace(querysplit[0], System.String.Empty) + "%"; - - IDbCommand result = - database.Query( - "SELECT UUID,username,surname FROM users WHERE username like ?first OR lastname like ?second", - param); - IDataReader reader = result.ExecuteReader(); - - - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new LLUUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - returnlist.Add(user); - } - reader.Close(); - result.Dispose(); - } - } - catch (Exception e) - { - database.Reconnect(); - m_log.Error(e.ToString()); - return returnlist; - } - } - return returnlist; - } - + /// /// Returns a sim profile from it's UUID /// diff --git a/OpenSim/Framework/Data/GridData.cs b/OpenSim/Framework/Data/GridData.cs index 6072600..ee6f18a 100644 --- a/OpenSim/Framework/Data/GridData.cs +++ b/OpenSim/Framework/Data/GridData.cs @@ -78,9 +78,6 @@ namespace OpenSim.Framework.Data /// An array containing all the sim profiles in the specified range RegionProfileData[] GetProfilesInRange(uint Xmin, uint Ymin, uint Xmax, uint Ymax); - - List GeneratePickerResults(LLUUID queryID, string query); - /// /// Authenticates a sim by use of its recv key. /// WARNING: Insecure -- cgit v1.1