From 71fd737a66c58faa6854ad1c1d8c58f64491fdb0 Mon Sep 17 00:00:00 2001 From: Teravus Ovares Date: Wed, 5 Dec 2007 15:53:58 +0000 Subject: * Applied MSSQL Patch from akokko, Thanks! akokko * This hasn't been tested in MSSQL mode, however it's been checked to make sure it doesn't cause any issues with mySQL/SQLlite --- OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs | 227 +++++++ OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs | 164 ++++- OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs | 697 +++++++++++++++++++++ OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs | 104 +++ OpenSim/Framework/Data.MSSQL/MSSQLManager.cs | 616 ++++++++++++++++-- OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 452 +++++++++++++ .../Data.MSSQL/Resources/CreateAssetsTable.sql | 19 + .../Data.MSSQL/Resources/CreateFoldersTable.sql | 27 + .../Data.MSSQL/Resources/CreateItemsTable.sql | 39 ++ .../Data.MSSQL/Resources/Mssql-agents.sql | 37 ++ .../Framework/Data.MSSQL/Resources/Mssql-logs.sql | 20 + .../Framework/Data.MSSQL/Resources/Mssql-users.sql | 41 ++ 12 files changed, 2369 insertions(+), 74 deletions(-) create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql (limited to 'OpenSim/Framework') diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs new file mode 100644 index 0000000..e933a5b --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs @@ -0,0 +1,227 @@ +/* +* 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.Data; +using System.Collections.Generic; +using System.Data.SqlClient; + +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MSSQL +{ + class MSSQLAssetData : IAssetProvider + { + MSSQLManager database; + #region IAssetProvider Members + + private void UpgradeAssetsTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + MainLog.Instance.Notice("ASSETS", "Creating new database tables"); + database.ExecuteResourceSql("CreateAssetsTable.sql"); + return; + } + } + + /// + /// Ensure that the assets related tables exists and are at the latest version + /// + private void TestTables() + { + + Dictionary tableList = new Dictionary(); + + tableList["assets"] = null; + database.GetTableVersion(tableList); + + UpgradeAssetsTable(tableList["assets"]); + + } + + public AssetBase FetchAsset(LLUUID assetID) + { + AssetBase asset = null; + + Dictionary param = new Dictionary(); + param["id"] = assetID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param); + IDataReader reader = result.ExecuteReader(); + + asset = database.getAssetRow(reader); + reader.Close(); + result.Dispose(); + + return asset; + } + + public void CreateAsset(AssetBase asset) + { + + if (ExistsAsset((LLUUID)asset.FullID)) + { + return; + } + + + + SqlCommand cmd = + new SqlCommand( + "INSERT INTO assets ([id], [name], [description], [assetType], [invType], [local], [temporary], [data])"+ + " VALUES "+ + "(@id, @name, @description, @assetType, @invType, @local, @temporary, @data)", + database.getConnection()); + + using (cmd) + { + + //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar); + //p.Value = asset.FullID.ToStringHyphenated(); + cmd.Parameters.AddWithValue("id", asset.FullID.ToStringHyphenated()); + cmd.Parameters.AddWithValue("name", asset.Name); + cmd.Parameters.AddWithValue("description", asset.Description); + SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt); + e.Value = asset.Type; + SqlParameter f = cmd.Parameters.Add("invType", SqlDbType.TinyInt); + f.Value = asset.InvType; + SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt); + g.Value = asset.Local; + SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt); + h.Value = asset.Temporary; + SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image); + i.Value = asset.Data; + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception) + { + throw; + } + + cmd.Dispose(); + } + + } + + + public void UpdateAsset(AssetBase asset) + { + SqlCommand command = new SqlCommand("UPDATE assets set id = @id, " + + "name = @name, " + + "description = @description," + + "assetType = @assetType," + + "invType = @invType," + + "local = @local,"+ + "temporary = @temporary," + + "data = @data where " + + "id = @keyId;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@name", asset.Name); + SqlParameter param3 = new SqlParameter("@description", asset.Description); + SqlParameter param4 = new SqlParameter("@assetType", asset.Type); + SqlParameter param5 = new SqlParameter("@invType", asset.InvType); + SqlParameter param6 = new SqlParameter("@local", asset.Local); + SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary); + SqlParameter param8 = new SqlParameter("@data", asset.Data); + SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + } + + public bool ExistsAsset(LLUUID uuid) + { + if (FetchAsset(uuid) != null) { + return true; + } + return false; + } + + /// + /// All writes are immediately commited to the database, so this is a no-op + /// + public void CommitAssets() + { + } + + #endregion + + #region IPlugin Members + + + + 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"); + + this.database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + + TestTables(); + } + + public string Version + { +// get { return database.getVersion(); } + get { return database.getVersion(); } + } + + public string Name + { + get { return "MSSQL Asset storage engine"; } + } + + #endregion + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs index 1516fec..0d294e7 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs @@ -28,11 +28,10 @@ using System; using System.Collections.Generic; using System.Data; -using OpenSim.Framework; using System.Security.Cryptography; using System.Text; using libsecondlife; - +using OpenSim.Framework.Console; namespace OpenSim.Framework.Data.MSSQL { @@ -44,14 +43,22 @@ namespace OpenSim.Framework.Data.MSSQL /// /// Database manager /// - private MSSqlManager database; + private MSSQLManager database; /// /// Initialises the Grid Interface /// public void Initialise() { - database = new MSSqlManager("localhost", "db", "user", "password", "false"); + 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"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } /// @@ -100,17 +107,115 @@ namespace OpenSim.Framework.Data.MSSQL /// Sim profile public RegionProfileData GetProfileByHandle(ulong handle) { - Dictionary param = new Dictionary(); - param["handle"] = handle.ToString(); + IDataReader reader = null; + try + { + Dictionary param = new Dictionary(); + param["handle"] = handle.ToString(); + IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = @handle", param); + reader = result.ExecuteReader(); - IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param); - IDataReader reader = result.ExecuteReader(); + RegionProfileData row = database.getRegionRow(reader); + reader.Close(); + result.Dispose(); - RegionProfileData row = database.getRow(reader); - reader.Close(); - result.Dispose(); + return row; - return row; + } + catch (Exception) + { + if (reader != null) { + reader.Close(); + } + + } + 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(); + MainLog.Instance.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(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + } + return returnlist; } /// @@ -120,28 +225,18 @@ namespace OpenSim.Framework.Data.MSSQL /// The sim profile public RegionProfileData GetProfileByLLUUID(LLUUID uuid) { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToStringHyphenated(); - + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToString(); IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); IDataReader reader = result.ExecuteReader(); - RegionProfileData row = database.getRow(reader); + RegionProfileData row = database.getRegionRow(reader); reader.Close(); result.Dispose(); return row; } - /// - /// // Returns a list of avatar and UUIDs that match the query - /// - public List GeneratePickerResults(LLUUID queryID, string query) - { - //Do nothing yet - List returnlist = new List(); - return returnlist; - } /// /// Adds a new specified region to the database /// @@ -149,7 +244,20 @@ namespace OpenSim.Framework.Data.MSSQL /// A dataresponse enum indicating success public DataResponse AddProfile(RegionProfileData profile) { - if (database.insertRow(profile)) + + try + { + if (GetProfileByLLUUID(profile.UUID) != null) + { + return DataResponse.RESPONSE_OK; + } + } + catch (Exception) + { + System.Console.WriteLine("No regions found. Create new one."); + } + + if (database.insertRegionRow(profile)) { return DataResponse.RESPONSE_OK; } @@ -201,7 +309,5 @@ namespace OpenSim.Framework.Data.MSSQL { return null; } - // This is here because MSSQL GridData only seems to know about itself o.O - } -} \ No newline at end of file +} diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs new file mode 100644 index 0000000..364e0f2 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs @@ -0,0 +1,697 @@ +/* +* 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.IO; +using System.Data; +using System.Data.SqlClient; +using System.Collections.Generic; +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MSSQL +{ + /// + /// A MySQL interface for the inventory server + /// + public class MSSQLInventoryData : IInventoryData + { + /// + /// The database manager + /// + private MSSQLManager database; + + /// + /// Loads and initialises this database plugin + /// + 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"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + TestTables(); + } + + #region Test and initialization code + + private void UpgradeFoldersTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + database.ExecuteResourceSql("CreateFoldersTable.sql"); + //database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql"); + return; + } + + } + + private void UpgradeItemsTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + database.ExecuteResourceSql("CreateItemsTable.sql"); + //database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql"); + return; + } + } + + private void TestTables() + { + + Dictionary tableList = new Dictionary(); + + tableList["inventoryfolders"] = null; + tableList["inventoryitems"] = null; + + database.GetTableVersion(tableList); + + UpgradeFoldersTable(tableList["inventoryfolders"]); + UpgradeItemsTable(tableList["inventoryitems"]); + } + #endregion + + /// + /// The name of this DB provider + /// + /// Name of DB provider + public string getName() + { + return "MSSQL Inventory Data Interface"; + } + + /// + /// Closes this DB provider + /// + public void Close() + { + // Do nothing. + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + return database.getVersion(); + } + + /// + /// Returns a list of items in a specified folder + /// + /// The folder to search + /// A list containing inventory items + public List getInventoryInFolder(LLUUID folderID) + { + try + { + lock (database) + { + List items = new List(); + + Dictionary param = new Dictionary(); + param["parentFolderID"] = folderID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param); + IDataReader reader = result.ExecuteReader(); + + while(reader.Read()) + items.Add(readInventoryItem(reader)); + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a list of the root folders within a users inventory + /// + /// The user whos inventory is to be searched + /// A list of folder objects + public List getUserRootFolders(LLUUID user) + { + try + { + lock (database) + { + + Dictionary param = new Dictionary(); + param["uuid"] = user.ToStringHyphenated(); + param["zero"] = LLUUID.Zero.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns the users inventory root folder. + /// + /// + /// + public InventoryFolderBase getUserRootFolder(LLUUID user) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = user.ToStringHyphenated(); + param["zero"] = LLUUID.Zero.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + InventoryFolderBase rootFolder = null; + if (items.Count > 0) { + rootFolder = items[0]; //should only be one folder with parent set to zero (the root one). + } + + reader.Close(); + result.Dispose(); + + return rootFolder; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a list of folders in a users inventory contained within the specified folder + /// + /// The folder to search + /// A list of inventory folders + public List getInventoryFolders(LLUUID parentID) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["parentFolderID"] = parentID.ToStringHyphenated(); + + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Reads a one item from an SQL result + /// + /// The SQL Result + /// the item read + public InventoryItemBase readInventoryItem(IDataReader reader) + { + try + { + InventoryItemBase item = new InventoryItemBase(); + + item.inventoryID = new LLUUID((string)reader["inventoryID"]); + item.assetID = new LLUUID((string)reader["assetID"]); + item.assetType = (int)reader["assetType"]; + item.parentFolderID = new LLUUID((string)reader["parentFolderID"]); + item.avatarID = new LLUUID((string)reader["avatarID"]); + item.inventoryName = (string)reader["inventoryName"]; + item.inventoryDescription = (string)reader["inventoryDescription"]; + item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]); + item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]); + item.invType = (int)reader["invType"]; + item.creatorsID = new LLUUID((string)reader["creatorID"]); + item.inventoryBasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); + item.inventoryEveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); + return item; + } + catch (SqlException e) + { + MainLog.Instance.Error(e.ToString()); + } + + return null; + } + + /// + /// Returns a specified inventory item + /// + /// The item to return + /// An inventory item + public InventoryItemBase getInventoryItem(LLUUID itemID) + { + + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["inventoryID"] = itemID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param); + IDataReader reader = result.ExecuteReader(); + + InventoryItemBase item = null; + if(reader.Read()) + item = readInventoryItem(reader); + + reader.Close(); + result.Dispose(); + + return item; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + return null; + } + + /// + /// Reads a list of inventory folders returned by a query. + /// + /// A MySQL Data Reader + /// A List containing inventory folders + protected InventoryFolderBase readInventoryFolder(IDataReader reader) + { + try + { + InventoryFolderBase folder = new InventoryFolderBase(); + folder.agentID = new LLUUID((string)reader["agentID"]); + folder.parentID = new LLUUID((string)reader["parentFolderID"]); + folder.folderID = new LLUUID((string)reader["folderID"]); + folder.name = (string)reader["folderName"]; + folder.type = (short)reader["type"]; + folder.version = (ushort)((int)reader["version"]); + return folder; + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + return null; + } + + + /// + /// Returns a specified inventory folder + /// + /// The folder to return + /// A folder class + public InventoryFolderBase getInventoryFolder(LLUUID folderID) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = folderID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + reader.Read(); + InventoryFolderBase folder = readInventoryFolder(reader); + reader.Close(); + result.Dispose(); + + return folder; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Adds a specified item to the database + /// + /// The inventory item + public void addInventoryItem(InventoryItemBase item) + { + string sql = "INSERT INTO inventoryitems"; + sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]) VALUES "; + sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, @inventoryBasePermissions, @inventoryEveryOnePermissions);"; + + try + { + Dictionary param = new Dictionary(); + param["inventoryID"] = item.inventoryID.ToStringHyphenated(); + param["assetID"] = item.assetID.ToStringHyphenated(); + param["assetType"] = item.assetType.ToString(); + param["parentFolderID"] = item.parentFolderID.ToStringHyphenated(); + param["avatarID"] = item.avatarID.ToStringHyphenated(); + param["inventoryName"] = item.inventoryName; + param["inventoryDescription"] = item.inventoryDescription; + param["inventoryNextPermissions"] = item.inventoryNextPermissions.ToString(); + param["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString(); + param["invType"] = Convert.ToString(item.invType); + param["creatorID"] = item.creatorsID.ToStringHyphenated(); + param["inventoryBasePermissions"] = Convert.ToString(item.inventoryBasePermissions); + param["inventoryEveryOnePermissions"] = Convert.ToString(item.inventoryEveryOnePermissions); + + IDbCommand result = database.Query(sql, param); + result.ExecuteNonQuery(); + result.Dispose(); + + } + catch (SqlException e) + { + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Updates the specified inventory item + /// + /// Inventory item to update + public void updateInventoryItem(InventoryItemBase item) + { + SqlCommand command = new SqlCommand("UPDATE inventoryitems set inventoryID = @inventoryID, " + + "assetID = @assetID, " + + "assetType = @assetType" + + "parentFolderID = @parentFolderID" + + "avatarID = @avatarID" + + "inventoryName = @inventoryName"+ + "inventoryDescription = @inventoryDescription" + + "inventoryNextPermissions = @inventoryNextPermissions" + + "inventoryCurrentPermissions = @inventoryCurrentPermissions" + + "invType = @invType" + + "creatorID = @creatorID" + + "inventoryBasePermissions = @inventoryBasePermissions" + + "inventoryEveryOnePermissions = @inventoryEveryOnePermissions) where " + + "invenoryID = @keyInventoryID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@inventoryID", item.inventoryID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@assetID", item.assetID); + SqlParameter param3 = new SqlParameter("@assetType", item.assetType); + SqlParameter param4 = new SqlParameter("@parentFolderID", item.parentFolderID); + SqlParameter param5 = new SqlParameter("@avatarID", item.avatarID); + SqlParameter param6 = new SqlParameter("@inventoryName", item.inventoryName); + SqlParameter param7 = new SqlParameter("@inventoryDescription", item.inventoryDescription); + SqlParameter param8 = new SqlParameter("@inventoryNextPermissions", item.inventoryNextPermissions); + SqlParameter param9 = new SqlParameter("@inventoryCurrentPermissions", item.inventoryCurrentPermissions); + SqlParameter param10 = new SqlParameter("@invType", item.invType); + SqlParameter param11 = new SqlParameter("@creatorID", item.creatorsID); + SqlParameter param12 = new SqlParameter("@inventoryBasePermissions", item.inventoryBasePermissions); + SqlParameter param13 = new SqlParameter("@inventoryEveryOnePermissions", item.inventoryEveryOnePermissions); + SqlParameter param14 = new SqlParameter("@keyInventoryID", item.inventoryID.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + } + + /// + /// + /// + /// + public void deleteInventoryItem(LLUUID itemID) + { + try + { + Dictionary param = new Dictionary(); + param["uuid"] = itemID.ToStringHyphenated(); + + IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + + + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Creates a new inventory folder + /// + /// Folder to create + public void addInventoryFolder(InventoryFolderBase folder) + { + string sql = "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; + sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; + + + Dictionary param = new Dictionary(); + param["folderID"] = folder.folderID.ToStringHyphenated(); + param["agentID"] = folder.agentID.ToStringHyphenated(); + param["parentFolderID"] = folder.parentID.ToStringHyphenated(); + param["folderName"] = folder.name; + param["type"] = Convert.ToString(folder.type); + param["version"] = Convert.ToString(folder.version); + + try + { + IDbCommand result = database.Query(sql, param); + result.ExecuteNonQuery(); + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + + public void updateInventoryFolder(InventoryFolderBase folder) + { + SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " + + "agentID = @agentID, " + + "parentFolderID = @parentFolderID," + + "folderName = @folderName," + + "type = @type," + + "version = @version where " + + "folderID = @keyFolderID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@agentID", folder.agentID.ToStringHyphenated()); + SqlParameter param3 = new SqlParameter("@parentFolderID", folder.parentID.ToStringHyphenated()); + SqlParameter param4 = new SqlParameter("@folderName", folder.name); + SqlParameter param5 = new SqlParameter("@type", folder.type); + SqlParameter param6 = new SqlParameter("@version", folder.version); + SqlParameter param7 = new SqlParameter("@keyFolderID", folder.folderID.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + + } + + + /// + /// Append a list of all the child folders of a parent folder + /// + /// list where folders will be appended + /// ID of parent + protected void getInventoryFolders(ref List folders, LLUUID parentID) + { + List subfolderList = getInventoryFolders(parentID); + + foreach (InventoryFolderBase f in subfolderList) + folders.Add(f); + } + + /// + /// Returns all child folders in the hierarchy from the parent folder and down + /// + /// The folder to get subfolders for + /// A list of inventory folders + protected List getFolderHierarchy(LLUUID parentID) + { + List folders = new List(); + getInventoryFolders(ref folders, parentID); + + for (int i = 0; i < folders.Count; i++) + getInventoryFolders(ref folders, folders[i].folderID); + + return folders; + } + + protected void deleteOneFolder(LLUUID folderID) + { + try + { + Dictionary param = new Dictionary(); + param["folderID"] = folderID.ToStringHyphenated(); + + IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + protected void deleteItemsInFolder(LLUUID folderID) + { + try + { + Dictionary param = new Dictionary(); + param["parentFolderID"] = folderID.ToStringHyphenated(); + + + IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + + /// + /// Delete an inventory folder + /// + /// Id of folder to delete + public void deleteInventoryFolder(LLUUID folderID) + { + lock (database) + { + List subFolders = getFolderHierarchy(folderID); + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) + { + deleteOneFolder(f.folderID); + deleteItemsInFolder(f.folderID); + } + + //Delete the actual row + deleteOneFolder(folderID); + deleteItemsInFolder(folderID); + } + } + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs new file mode 100644 index 0000000..8a59bca --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs @@ -0,0 +1,104 @@ +/* +* 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; + +namespace OpenSim.Framework.Data.MSSQL +{ + /// + /// An interface to the log database for MySQL + /// + class MSSQLLogData : ILogData + { + /// + /// The database manager + /// + public MSSQLManager database; + + /// + /// Artificial constructor called when the plugin is loaded + /// + 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"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } + + /// + /// Saves a log item to the database + /// + /// The daemon triggering the event + /// The target of the action (region / agent UUID, etc) + /// The method call where the problem occured + /// The arguments passed to the method + /// How critical is this? + /// The message to log + public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage) + { + try + { + database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage); + } + catch + { + database.Reconnect(); + } + } + + /// + /// Returns the name of this DB provider + /// + /// A string containing the DB provider name + public string getName() + { + return "MSSQL Logdata Interface"; + } + + /// + /// Closes the database provider + /// + public void Close() + { + // Do nothing. + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the provider version + public string getVersion() + { + return "0.1"; + } + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs index c0b2edf..4bca2e1 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs @@ -29,19 +29,28 @@ using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; +using System.IO; +using System.Reflection; using libsecondlife; +using OpenSim.Framework.Console; + namespace OpenSim.Framework.Data.MSSQL { /// /// A management class for the MS SQL Storage Engine /// - internal class MSSqlManager + class MSSQLManager { /// /// The database connection object /// - private IDbConnection dbcon; + IDbConnection dbcon; + + /// + /// Connection string for ADO.net + /// + private string connectionString; /// /// Initialises and creates a new Sql connection and maintains it. @@ -51,14 +60,14 @@ namespace OpenSim.Framework.Data.MSSQL /// 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 hostname, string database, string username, string password, string cpooling) + public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, string password) { try { - string connectionString = "Server=" + hostname + ";Database=" + database + ";User ID=" + username + - ";Password=" + password + ";Pooling=" + cpooling + ";"; + + connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + password+";"; dbcon = new SqlConnection(connectionString); - + TestTables(dbcon); dbcon.Open(); } catch (Exception e) @@ -67,6 +76,144 @@ namespace OpenSim.Framework.Data.MSSQL } } + private bool TestTables(IDbConnection conn) + { + IDbCommand cmd = this.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) + { + MainLog.Instance.Verbose("DATASTORE", "MSSQL Database doesn't exist... creating"); + InitDB(conn); + } + return true; + } + + private void InitDB(IDbConnection conn) + { + string createRegions = defineTable(createRegionsTable()); + Dictionary param = new Dictionary(); + IDbCommand pcmd = this.Query(createRegions, param); + if (conn.State == ConnectionState.Closed) { + conn.Open(); + } + pcmd.ExecuteNonQuery(); + pcmd.Dispose(); + + this.ExecuteResourceSql("Mssql-users.sql"); + this.ExecuteResourceSql("Mssql-agents.sql"); + this.ExecuteResourceSql("Mssql-logs.sql"); + + conn.Close(); + + } + + private DataTable createRegionsTable() + { + DataTable regions = new DataTable("regions"); + + createCol(regions, "regionHandle", typeof(ulong)); + createCol(regions, "regionName", typeof(System.String)); + createCol(regions, "uuid", typeof(System.String)); + + createCol(regions, "regionRecvKey", typeof(System.String)); + createCol(regions, "regionSecret", typeof(System.String)); + createCol(regions, "regionSendKey", typeof(System.String)); + + createCol(regions, "regionDataURI", typeof(System.String)); + createCol(regions, "serverIP", typeof(System.String)); + createCol(regions, "serverPort", typeof(System.String)); + createCol(regions, "serverURI", typeof(System.String)); + + + createCol(regions, "locX", typeof(uint)); + createCol(regions, "locY", typeof(uint)); + createCol(regions, "locZ", typeof(uint)); + + createCol(regions, "eastOverrideHandle", typeof(ulong)); + createCol(regions, "westOverrideHandle", typeof(ulong)); + createCol(regions, "southOverrideHandle", typeof(ulong)); + createCol(regions, "northOverrideHandle", typeof(ulong)); + + createCol(regions, "regionAssetURI", typeof(System.String)); + createCol(regions, "regionAssetRecvKey", typeof(System.String)); + createCol(regions, "regionAssetSendKey", typeof(System.String)); + + createCol(regions, "regionUserURI", typeof(System.String)); + createCol(regions, "regionUserRecvKey", typeof(System.String)); + createCol(regions, "regionUserSendKey", typeof(System.String)); + + createCol(regions, "regionMapTexture", typeof(System.String)); + createCol(regions, "serverHttpPort", typeof(System.String)); + createCol(regions, "serverRemotingPort", typeof(uint)); + + // Add in contraints + regions.PrimaryKey = new DataColumn[] { regions.Columns["UUID"] }; + return regions; + } + + protected static void createCol(DataTable dt, string name, System.Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + protected static string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = ""; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { // a map function would rock so much here + subsql += ",\n"; + } + + subsql += col.ColumnName + " " + SqlType(col.DataType); + if (col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + sql += subsql; + sql += ")"; + return sql; + } + + + // this is something we'll need to implement for each db + // slightly differently. + private static string SqlType(Type type) + { + if (type == typeof(System.String)) + { + return "varchar(255)"; + } + else if (type == typeof(System.Int32)) + { + return "integer"; + } + else if (type == typeof(System.Double)) + { + return "float"; + } + else if (type == typeof(System.Byte[])) + { + return "image"; + } + else + { + return "varchar(255)"; + } + } + /// /// Shuts down the database connection /// @@ -77,6 +224,29 @@ namespace OpenSim.Framework.Data.MSSQL } /// + /// Reconnects to the database + /// + public void Reconnect() + { + lock (dbcon) + { + 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 + dbcon = new SqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + MainLog.Instance.Error("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 @@ -84,14 +254,14 @@ namespace OpenSim.Framework.Data.MSSQL /// A Sql DB Command public IDbCommand Query(string sql, Dictionary parameters) { - SqlCommand dbcommand = (SqlCommand) dbcon.CreateCommand(); - dbcommand.CommandText = sql; + SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand(); + dbcommand.CommandText = sql; foreach (KeyValuePair param in parameters) { dbcommand.Parameters.AddWithValue(param.Key, param.Value); } - - return (IDbCommand) dbcommand; + + return (IDbCommand)dbcommand; } /// @@ -99,74 +269,201 @@ namespace OpenSim.Framework.Data.MSSQL /// /// An active database reader /// A region row - public RegionProfileData getRow(IDataReader reader) + public RegionProfileData getRegionRow(IDataReader reader) { RegionProfileData regionprofile = new RegionProfileData(); if (reader.Read()) { // Region Main - regionprofile.regionHandle = (ulong) reader["regionHandle"]; - regionprofile.regionName = (string) reader["regionName"]; - regionprofile.UUID = new LLUUID((string) reader["uuid"]); + regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]); + regionprofile.regionName = (string)reader["regionName"]; + regionprofile.UUID = new LLUUID((string)reader["uuid"]); // Secrets - regionprofile.regionRecvKey = (string) reader["regionRecvKey"]; - regionprofile.regionSecret = (string) reader["regionSecret"]; - regionprofile.regionSendKey = (string) reader["regionSendKey"]; + regionprofile.regionRecvKey = (string)reader["regionRecvKey"]; + regionprofile.regionSecret = (string)reader["regionSecret"]; + regionprofile.regionSendKey = (string)reader["regionSendKey"]; // Region Server - regionprofile.regionDataURI = (string) reader["regionDataURI"]; + regionprofile.regionDataURI = (string)reader["regionDataURI"]; regionprofile.regionOnline = false; // Needs to be pinged before this can be set. - regionprofile.serverIP = (string) reader["serverIP"]; - regionprofile.serverPort = (uint) reader["serverPort"]; - regionprofile.serverURI = (string) reader["serverURI"]; + regionprofile.serverIP = (string)reader["serverIP"]; + regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]); + regionprofile.serverURI = (string)reader["serverURI"]; + regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]); + regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]); + // Location - regionprofile.regionLocX = (uint) ((int) reader["locX"]); - regionprofile.regionLocY = (uint) ((int) reader["locY"]); - regionprofile.regionLocZ = (uint) ((int) reader["locZ"]); + regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]); + regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]); + regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]); // Neighbours - 0 = No Override - regionprofile.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; - regionprofile.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; - regionprofile.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; - regionprofile.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; + regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]); + regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]); + regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]); + regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]); // Assets - regionprofile.regionAssetURI = (string) reader["regionAssetURI"]; - regionprofile.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; - regionprofile.regionAssetSendKey = (string) reader["regionAssetSendKey"]; + regionprofile.regionAssetURI = (string)reader["regionAssetURI"]; + regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"]; + regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"]; // Userserver - regionprofile.regionUserURI = (string) reader["regionUserURI"]; - regionprofile.regionUserRecvKey = (string) reader["regionUserRecvKey"]; - regionprofile.regionUserSendKey = (string) reader["regionUserSendKey"]; + regionprofile.regionUserURI = (string)reader["regionUserURI"]; + regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"]; + regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"]; + + // World Map Addition + string tempRegionMap = reader["regionMapTexture"].ToString(); + if (tempRegionMap != "") + { + regionprofile.regionMapTextureID = new LLUUID(tempRegionMap); + } + else + { + regionprofile.regionMapTextureID = new LLUUID(); + } } else { + reader.Close(); throw new Exception("No rows to return"); + } return regionprofile; } /// + /// 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()) + { + retval.UUID = new 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 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()); + + retval.profileAboutText = (string)reader["profileAboutText"]; + retval.profileFirstText = (string)reader["profileFirstText"]; + + retval.profileImage = new LLUUID((string)reader["profileImage"]); + retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]); + + } + 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 + retval.UUID = new LLUUID((string)reader["UUID"]); + retval.sessionID = new LLUUID((string)reader["sessionID"]); + retval.secureSessionID = new 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()); + LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos); + } + else + { + return null; + } + return retval; + } + + public AssetBase getAssetRow(IDataReader reader) + { + AssetBase asset = new AssetBase(); + if (reader.Read()) + { + // Region Main + + asset = new AssetBase(); + asset.Data = (byte[])reader["data"]; + asset.Description = (string)reader["description"]; + asset.FullID = new LLUUID((string)reader["id"]); + asset.InvType = Convert.ToSByte(reader["invType"]); + asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false; + asset.Name = (string)reader["name"]; + asset.Type = Convert.ToSByte(reader["assetType"]); + + } + else + { + return null; // throw new Exception("No rows to return"); + } + return asset; + } + + /// /// Creates a new region in the database /// /// The region profile to insert /// Successful? - public bool insertRow(RegionProfileData profile) + public bool insertRegionRow(RegionProfileData profile) { - string sql = - "REPLACE INTO regions VALUES (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) VALUES "; + + //Insert new region + string sql = "INSERT 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], [serverHttpPort], [serverRemotingPort]) 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);"; + sql += "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; + sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);"; Dictionary parameters = new Dictionary(); @@ -174,6 +471,7 @@ namespace OpenSim.Framework.Data.MSSQL parameters["regionName"] = profile.regionName; parameters["uuid"] = profile.UUID.ToString(); parameters["regionRecvKey"] = profile.regionRecvKey; + parameters["regionSecret"] = profile.regionSecret; parameters["regionSendKey"] = profile.regionSendKey; parameters["regionDataURI"] = profile.regionDataURI; parameters["serverIP"] = profile.serverIP; @@ -192,6 +490,56 @@ namespace OpenSim.Framework.Data.MSSQL parameters["regionUserURI"] = profile.regionUserURI; parameters["regionUserRecvKey"] = profile.regionUserRecvKey; parameters["regionUserSendKey"] = profile.regionUserSendKey; + parameters["regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated(); + parameters["serverHttpPort"] = profile.httpPort.ToString(); + parameters["serverRemotingPort"] = profile.remotingPort.ToString(); + + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error("MSSQLManager : " + e.ToString()); + + } + + return returnval; + + } + + + + /// + /// 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; @@ -204,12 +552,190 @@ namespace OpenSim.Framework.Data.MSSQL result.Dispose(); } - catch (Exception) + catch (Exception e) { + MainLog.Instance.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(libsecondlife.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, + libsecondlife.LLUUID profileImage, libsecondlife.LLUUID firstImage) + { + string sql = "INSERT INTO users "; + 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]) 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);"; + + Dictionary parameters = new Dictionary(); + parameters["UUID"] = uuid.ToStringHyphenated(); + 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"] = ""; + parameters["userAssetURI"] = ""; + parameters["profileCanDoMask"] = "0"; + parameters["profileWantDoMask"] = "0"; + parameters["profileAboutText"] = ""; + parameters["profileFirstText"] = ""; + parameters["profileImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated(); + parameters["profileFirstImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated(); + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Execute a SQL statement stored in a resource, as a string + /// + /// + public void ExecuteResourceSql(string name) + { + try + { + + SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error("Unable to execute query " + e.ToString()); + } + } + + public SqlConnection getConnection() + { + return (SqlConnection)dbcon; + } + + /// + /// Given a list of tables, return the version of the tables, as seen in the database + /// + /// + public void GetTableVersion(Dictionary tableList) + { + lock (dbcon) + { + Dictionary param = new Dictionary(); + param["dbname"] = dbcon.Database; + IDbCommand tablesCmd = this.Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param); + using (IDataReader tables = tablesCmd.ExecuteReader()) + { + while (tables.Read()) + { + try + { + string tableName = (string)tables["TABLE_NAME"]; + if (tableList.ContainsKey(tableName)) + tableList[tableName] = tableName; + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + } + tables.Close(); + } + } + } + + private string getResourceString(string name) + { + Assembly assem = this.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)); + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + System.Reflection.Module module = this.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); + } + } -} \ No newline at end of file + + + +} diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs new file mode 100644 index 0000000..2c4dfdc --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs @@ -0,0 +1,452 @@ +/* +* 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 + /// + class MSSQLUserData : IUserData + { + /// + /// Database manager for MySQL + /// + public MSSQLManager database; + + /// + /// 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("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"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } + + /// + /// Searches the database for a specified user profile + /// + /// The account name of the user + /// A user profile + public UserProfileData GetUserByName(string name) + { + return GetUserByName(name.Split(' ')[0], name.Split(' ')[1]); + } + + /// + /// 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 + 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 users 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(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + 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()) + { + OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.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(); + MainLog.Instance.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()) + { + OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.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(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + } + return returnlist; + } + + /// + /// Searches the database for a specified user profile by UUID + /// + /// The account ID + /// The users profile + public UserProfileData GetUserByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM users WHERE UUID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserProfileData row = database.readUserRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a user session searching by name + /// + /// The account name + /// The users session + 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 + 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 + public UserAgentData GetAgentByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM agents WHERE UUID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserAgentData row = database.readAgentRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Creates a new users profile + /// + /// The user profile to create + public void AddNewUserProfile(UserProfileData user) + { + try + { + lock (database) + { + 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); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + + } + + /// + /// Creates a new agent + /// + /// The agent to create + public void AddNewUserAgent(UserAgentData agent) + { + // Do nothing. + } + + + public bool UpdateUserProfile(UserProfileData user) + { + + SqlCommand command = new SqlCommand("UPDATE users 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 where " + + "UUID = @keyUUUID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@uuid", user.UUID.ToStringHyphenated()); + 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", libsecondlife.LLUUID.Zero.ToStringHyphenated()); + SqlParameter param22 = new SqlParameter("@profileFirstImage", libsecondlife.LLUUID.Zero.ToStringHyphenated()); + SqlParameter param23 = new SqlParameter("@keyUUUID", user.UUID.ToStringHyphenated()); + 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); + try + { + int affected = command.ExecuteNonQuery(); + if (affected != 0) { + return true; + } else { + return false; + } + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + return false; + } + + /// + /// Performs a money transfer request between two accounts + /// + /// The senders account ID + /// The recievers account ID + /// The amount to transfer + /// Success? + 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 recievers account ID + /// The item to transfer + /// Success? + public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) + { + return false; + } + + /// + /// Database provider name + /// + /// Provider name + public string getName() + { + return "MSSQL Userdata Interface"; + } + + /// + /// Database provider version + /// + /// provider version + public string GetVersion() + { + return database.getVersion(); + } + + /// + /// Not implemented + /// + /// + public void runQuery(string query) + { + } + + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql new file mode 100644 index 0000000..c7cb21a --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql @@ -0,0 +1,19 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [invType] [tinyint] NOT NULL, + [local] [tinyint] NOT NULL, + [temporary] [tinyint] NOT NULL, + [data] [image] NOT NULL, +PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql new file mode 100644 index 0000000..95d183a --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql @@ -0,0 +1,27 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [inventoryfolders] ( + [folderID] [varchar](36) NOT NULL default '', + [agentID] [varchar](36) default NULL, + [parentFolderID] [varchar](36) default NULL, + [folderName] [varchar](64) default NULL, + [type] [smallint] NOT NULL default 0, + [version] [int] NOT NULL default 0, + PRIMARY KEY CLUSTERED +( + [folderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders] +( + [agentID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql new file mode 100644 index 0000000..40c047e --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql @@ -0,0 +1,39 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [inventoryitems] ( + [inventoryID] [varchar](36) NOT NULL default '', + [assetID] [varchar](36) default NULL, + [assetType] [int] default NULL, + [parentFolderID] [varchar](36) default NULL, + [avatarID] [varchar](36) default NULL, + [inventoryName] [varchar](64) default NULL, + [inventoryDescription] [varchar](64) default NULL, + [inventoryNextPermissions] [int] default NULL, + [inventoryCurrentPermissions] [int] default NULL, + [invType] [int] default NULL, + [creatorID] [varchar](36) default NULL, + [inventoryBasePermissions] [int] NOT NULL default 0, + [inventoryEveryOnePermissions] [int] NOT NULL default 0, + PRIMARY KEY CLUSTERED +( + [inventoryID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems] +( + [avatarID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql new file mode 100644 index 0000000..ad53173 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql @@ -0,0 +1,37 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [agents] ( + [UUID] [varchar](36) NOT NULL, + [sessionID] [varchar](36) NOT NULL, + [secureSessionID] [varchar](36) NOT NULL, + [agentIP] [varchar](16) NOT NULL, + [agentPort] [int] NOT NULL, + [agentOnline] [tinyint] NOT NULL, + [loginTime] [int] NOT NULL, + [logoutTime] [int] NOT NULL, + [currentRegion] [varchar](36) NOT NULL, + [currentHandle] [bigint] NOT NULL, + [currentPos] [varchar](64) NOT NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [session] ON [agents] +( + [sessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [ssession] ON [agents] +( + [secureSessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql new file mode 100644 index 0000000..3b747d8 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql @@ -0,0 +1,20 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [logs] ( + [logID] [int] NOT NULL, + [target] [varchar](36) default NULL, + [server] [varchar](64) default NULL, + [method] [varchar](64) default NULL, + [arguments] [varchar](255) default NULL, + [priority] [int] default NULL, + [message] [ntext], + PRIMARY KEY CLUSTERED +( + [logID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql new file mode 100644 index 0000000..3f5f896 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql @@ -0,0 +1,41 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [users] ( + [UUID] [varchar](36) NOT NULL default '', + [username] [varchar](32) NOT NULL, + [lastname] [varchar](32) NOT NULL, + [passwordHash] [varchar](32) NOT NULL, + [passwordSalt] [varchar](32) NOT NULL, + [homeRegion] [bigint] default NULL, + [homeLocationX] [float] default NULL, + [homeLocationY] [float] default NULL, + [homeLocationZ] [float] default NULL, + [homeLookAtX] [float] default NULL, + [homeLookAtY] [float] default NULL, + [homeLookAtZ] [float] default NULL, + [created] [int] NOT NULL, + [lastLogin] [int] NOT NULL, + [userInventoryURI] [varchar](255) default NULL, + [userAssetURI] [varchar](255) default NULL, + [profileCanDoMask] [int] default NULL, + [profileWantDoMask] [int] default NULL, + [profileAboutText] [ntext], + [profileFirstText] [ntext], + [profileImage] [varchar](36) default NULL, + [profileFirstImage] [varchar](36) default NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [usernames] ON [users] +( + [username] ASC, + [lastname] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -- cgit v1.1