From 281955949910eb257b5f7e42e54535ba7812418e Mon Sep 17 00:00:00 2001 From: Melanie Thielker Date: Sun, 14 Sep 2008 13:23:02 +0000 Subject: Mantis #2124 Thank you, RuudL, for a patch that brings MSSQL up to the same implementation level as MySQL. --- OpenSim/Data/MSSQL/MSSQLInventoryData.cs | 818 ++++++++++++++++--------------- 1 file changed, 425 insertions(+), 393 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLInventoryData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs index e7df7c1..03600e2 100644 --- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs +++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs @@ -41,49 +41,53 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLInventoryData : IInventoryDataPlugin { - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private const string _migrationStore = "InventoryStore"; - #region Helper converters to preserve unsigned bitfield-type data in DB roundtrips via signed int32s - private static int ConvertUint32BitFieldToInt32(uint bitField) - { - return BitConverter.ToInt32(BitConverter.GetBytes(bitField), 0); - } - private static uint ConvertInt32BitFieldToUint32(int bitField) - { - return BitConverter.ToUInt32(BitConverter.GetBytes(bitField), 0); - } - #endregion + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// /// The database manager /// private MSSQLManager database; - public void Initialise() - { + #region IPlugin members + + public void Initialise() + { m_log.Info("[MSSQLInventoryData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); + throw new PluginNotInitialisedException(Name); } /// /// Loads and initialises the MSSQL inventory storage interface /// - /// connect string + /// connect string /// use mssql_connection.ini - public void Initialise(string connect) + public void Initialise(string connectionString) { - // TODO: actually use the provided connect string - IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); - string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source"); - string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog"); - string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info"); - string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id"); - string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password"); - - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); + if (string.IsNullOrEmpty(connectionString)) + { + database = new MSSQLManager(connectionString); + } + else + { + IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id"); + string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password"); + + database = + new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, + settingPassword); + } + + //TODO remove this at one point TestTables(); + + //New migrations check of store + database.CheckMigration(_migrationStore); } #region Test and initialization code @@ -132,6 +136,37 @@ namespace OpenSim.Data.MSSQL UpgradeFoldersTable(tableList["inventoryfolders"]); UpgradeItemsTable(tableList["inventoryitems"]); + + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'")) + { + //Special for Migrations to create backword compatible + try + { + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + catch + { + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } } #endregion @@ -150,7 +185,7 @@ namespace OpenSim.Data.MSSQL /// public void Dispose() { - // Do nothing. + database = null; } /// @@ -162,39 +197,9 @@ namespace OpenSim.Data.MSSQL get { return database.getVersion(); } } - /// - /// Returns a list of items in a specified folder - /// - /// The folder to search - /// A list containing inventory items - public List getInventoryInFolder(UUID folderID) - { - try - { - List items = new List(); - - Dictionary param = new Dictionary(); - param["parentFolderID"] = folderID.ToString(); - - using (IDbCommand result = - database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - while (reader.Read()) - items.Add(readInventoryItem(reader)); - - reader.Close(); - } + #endregion - return items; - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } + #region Folder methods /// /// Returns a list of the root folders within a users inventory @@ -203,31 +208,7 @@ namespace OpenSim.Data.MSSQL /// A list of folder objects public List getUserRootFolders(UUID user) { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = user.ToString(); - param["zero"] = UUID.Zero.ToString(); - - using (IDbCommand result = - database.Query( - "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - List items = new List(); - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - return items; - } - - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } + return getInventoryFolders(UUID.Zero, user); } /// @@ -237,43 +218,21 @@ namespace OpenSim.Data.MSSQL /// public InventoryFolderBase getUserRootFolder(UUID user) { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = user.ToString(); - param["zero"] = UUID.Zero.ToString(); - - using (IDbCommand result = - database.Query( - "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - List items = new List(); - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - InventoryFolderBase rootFolder = null; + List items = getUserRootFolders(user); - // There should only ever be one root folder for a user. However, if there's more - // than one we'll simply use the first one rather than failing. It would be even - // nicer to print some message to this effect, but this feels like it's too low a - // to put such a message out, and it's too minor right now to spare the time to - // suitably refactor. - if (items.Count > 0) - { - rootFolder = items[0]; - } - - return rootFolder; - } + InventoryFolderBase rootFolder = null; - } - catch (Exception e) + // There should only ever be one root folder for a user. However, if there's more + // than one we'll simply use the first one rather than failing. It would be even + // nicer to print some message to this effect, but this feels like it's too low a + // to put such a message out, and it's too minor right now to spare the time to + // suitably refactor. + if (items.Count > 0) { - m_log.Error(e.ToString()); - return null; + rootFolder = items[0]; } + + return rootFolder; } /// @@ -283,156 +242,235 @@ namespace OpenSim.Data.MSSQL /// A list of inventory folders public List getInventoryFolders(UUID parentID) { - try + return getInventoryFolders(parentID, UUID.Zero); + } + + /// + /// Returns a specified inventory folder + /// + /// The folder to return + /// A folder class + public InventoryFolderBase getInventoryFolder(UUID folderID) + { + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID")) { - Dictionary param = new Dictionary(); - param["parentFolderID"] = parentID.ToString(); + command.Parameters.Add(database.CreateParameter("folderID", folderID)); - using (IDbCommand result = - database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param)) - using (IDataReader reader = result.ExecuteReader()) + using (IDataReader reader = command.ExecuteReader()) { - List items = new List(); - - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - return items; + if (reader.Read()) + { + return readInventoryFolder(reader); + } } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } + m_log.InfoFormat("[INVENTORY DB] : FOund no inventory folder with ID : {0}", folderID); + return null; } /// - /// Reads a one item from an SQL result + /// Returns all child folders in the hierarchy from the parent folder and down. + /// Does not return the parent folder itself. /// - /// The SQL Result - /// the item read - private static InventoryItemBase readInventoryItem(IDataReader reader) + /// The folder to get subfolders for + /// A list of inventory folders + public List getFolderHierarchy(UUID parentID) { - try - { - InventoryItemBase item = new InventoryItemBase(); + //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way. + //Note this is changed so it opens only one connection to the database and not everytime it wants to get data. - item.ID = new UUID((string) reader["inventoryID"]); - item.AssetID = new UUID((string) reader["assetID"]); - item.AssetType = (int) reader["assetType"]; - item.Folder = new UUID((string) reader["parentFolderID"]); - item.Owner = new UUID((string) reader["avatarID"]); - item.Name = (string) reader["inventoryName"]; - item.Description = (string) reader["inventoryDescription"]; - item.NextPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryNextPermissions"]); - item.CurrentPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryCurrentPermissions"]); - item.InvType = (int) reader["invType"]; - item.Creator = new UUID((string) reader["creatorID"]); - item.BasePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryBasePermissions"]); - item.EveryOnePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryEveryOnePermissions"]); - item.SalePrice = (int) reader["salePrice"]; - item.SaleType = Convert.ToByte(reader["saleType"]); - item.CreationDate = (int) reader["creationDate"]; - item.GroupID = new UUID(reader["groupID"].ToString()); - item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); - item.Flags = ConvertInt32BitFieldToUint32((int)reader["flags"]); + List folders = new List(); - return item; - } - catch (SqlException e) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID")) { - m_log.Error(e.ToString()); - } + command.Parameters.Add(database.CreateParameter("@parentID", parentID)); - return null; + folders.AddRange(getInventoryFolders(command)); + + List tempFolders = new List(); + + foreach (InventoryFolderBase folderBase in folders) + { + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); + } + if (tempFolders.Count > 0) + { + folders.AddRange(tempFolders); + } + } + return folders; } /// - /// Returns a specified inventory item + /// Creates a new inventory folder /// - /// The item to return - /// An inventory item - public InventoryItemBase getInventoryItem(UUID itemID) + /// Folder to create + public void addInventoryFolder(InventoryFolderBase folder) { - try - { - Dictionary param = new Dictionary(); - param["inventoryID"] = itemID.ToString(); + string sql = + "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; + sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; - using (IDbCommand result = - database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param)) - using (IDataReader reader = result.ExecuteReader()) - { - InventoryItemBase item = null; - if (reader.Read()) - item = readInventoryItem(reader); + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("folderID", folder.ID)); + command.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); + command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + command.Parameters.Add(database.CreateParameter("folderName", folder.Name)); + command.Parameters.Add(database.CreateParameter("type", folder.Type)); + command.Parameters.Add(database.CreateParameter("version", folder.Version)); - return item; + try + { + //IDbCommand result = database.Query(sql, param); + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - return null; } /// - /// Reads a list of inventory folders returned by a query. + /// Updates an inventory folder /// - /// A MSSQL Data Reader - /// A List containing inventory folders - protected static InventoryFolderBase readInventoryFolder(IDataReader reader) + /// Folder to update + public void updateInventoryFolder(InventoryFolderBase folder) { - try + using (AutoClosingSqlCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + + "agentID = @agentID, " + + "parentFolderID = @parentFolderID," + + "folderName = @folderName," + + "type = @type," + + "version = @version where " + + "folderID = @keyFolderID;")) { - InventoryFolderBase folder = new InventoryFolderBase(); - folder.Owner = new UUID((string) reader["agentID"]); - folder.ParentID = new UUID((string) reader["parentFolderID"]); - folder.ID = new UUID((string) reader["folderID"]); - folder.Name = (string) reader["folderName"]; - folder.Type = (short) reader["type"]; - folder.Version = Convert.ToUInt16(reader["version"]); - return folder; + command.Parameters.Add(database.CreateParameter("folderID", folder.ID)); + command.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); + command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + command.Parameters.Add(database.CreateParameter("folderName", folder.Name)); + command.Parameters.Add(database.CreateParameter("type", folder.Type)); + command.Parameters.Add(database.CreateParameter("version", folder.Version)); + command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID)); + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); + } } - catch (Exception e) + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + public void moveInventoryFolder(InventoryFolderBase folder) + { + using (IDbCommand command = database.Query("UPDATE inventoryfolders set " + + "parentFolderID = @parentFolderID where " + + "folderID = @folderID;")) { - m_log.Error(e.ToString()); - } + command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + command.Parameters.Add(database.CreateParameter("@folderID", folder.ID)); - return null; + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); + } + } } /// - /// Returns a specified inventory folder + /// Delete an inventory folder /// - /// The folder to return - /// A folder class - public InventoryFolderBase getInventoryFolder(UUID folderID) + /// Id of folder to delete + public void deleteInventoryFolder(UUID folderID) { - try + using (SqlConnection connection = database.DatabaseConnection()) { - Dictionary param = new Dictionary(); - param["uuid"] = folderID.ToString(); + List subFolders; + using (SqlCommand command = new SqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID", connection)) + { + command.Parameters.Add(database.CreateParameter("@parentID", string.Empty)); - using (IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) + AutoClosingSqlCommand autoCommand = new AutoClosingSqlCommand(command); + + subFolders = getFolderHierarchy(folderID, autoCommand); + } + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) { + DeleteOneFolder(f.ID, connection); + DeleteItemsInFolder(f.ID, connection); + } - reader.Read(); + //Delete the actual row + DeleteOneFolder(folderID, connection); + DeleteItemsInFolder(folderID, connection); + + connection.Close(); + } + } + + #endregion - InventoryFolderBase folder = readInventoryFolder(reader); + #region Item Methods + + /// + /// Returns a list of items in a specified folder + /// + /// The folder to search + /// A list containing inventory items + public List getInventoryInFolder(UUID folderID) + { + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID")) + { + command.Parameters.Add(database.CreateParameter("parentFolderID", folderID)); + + List items = new List(); - return folder; + using (SqlDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + items.Add(readInventoryItem(reader)); + } } + return items; } - catch (Exception e) + } + + /// + /// Returns a specified inventory item + /// + /// The item ID + /// An inventory item + public InventoryItemBase getInventoryItem(UUID itemID) + { + using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID")) { - m_log.Error(e.ToString()); - return null; + result.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + + using (IDataReader reader = result.ExecuteReader()) + { + if (reader.Read()) + { + return readInventoryItem(reader); + } + } } + m_log.InfoFormat("[INVENTORY DB] : Found no inventory item with ID : {0}", itemID); + return null; } /// @@ -448,46 +486,44 @@ namespace OpenSim.Data.MSSQL } string sql = "INSERT INTO inventoryitems"; - sql += - "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]" + sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]" + ", [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions]" + ", [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]" + ", [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) VALUES "; - sql += - "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription" + sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription" + ", @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID" + ", @inventoryBasePermissions, @inventoryEveryOnePermissions, @salePrice, @saleType" + ", @creationDate, @groupID, @groupOwned, @flags);"; using (AutoClosingSqlCommand command = database.Query(sql)) { - command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); - command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); - command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); - command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); - command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); - command.Parameters.AddWithValue("inventoryName", item.Name); - command.Parameters.AddWithValue("inventoryDescription", item.Description); - command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); - command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); - command.Parameters.AddWithValue("invType", item.InvType); - command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); - command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); - command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); - command.Parameters.AddWithValue("salePrice", item.SalePrice); - command.Parameters.AddWithValue("saleType", item.SaleType); - command.Parameters.AddWithValue("creationDate", item.CreationDate); - command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); - command.Parameters.AddWithValue("groupOwned", item.GroupOwned); - command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); + command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); + command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); + command.Parameters.Add(database.CreateParameter("assetType", item.AssetType)); + command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder)); + command.Parameters.Add(database.CreateParameter("avatarID", item.Owner)); + command.Parameters.Add(database.CreateParameter("inventoryName", item.Name)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description)); + command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions)); + command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions)); + command.Parameters.Add(database.CreateParameter("invType", item.InvType)); + command.Parameters.Add(database.CreateParameter("creatorID", item.Creator)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice)); + command.Parameters.Add(database.CreateParameter("saleType", item.SaleType)); + command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate)); + command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); + command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); + command.Parameters.Add(database.CreateParameter("flags", item.Flags)); try { command.ExecuteNonQuery(); } - catch (SqlException e) + catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error inserting item :" + e.Message); } } @@ -520,26 +556,26 @@ namespace OpenSim.Data.MSSQL "flags = @flags where " + "inventoryID = @keyInventoryID;")) { - command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); - command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); - command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); - command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); - command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); - command.Parameters.AddWithValue("inventoryName", item.Name); - command.Parameters.AddWithValue("inventoryDescription", item.Description); - command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); - command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); - command.Parameters.AddWithValue("invType", item.InvType); - command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); - command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); - command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); - command.Parameters.AddWithValue("salePrice", item.SalePrice); - command.Parameters.AddWithValue("saleType", item.SaleType); - command.Parameters.AddWithValue("creationDate", item.CreationDate); - command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); - command.Parameters.AddWithValue("groupOwned", item.GroupOwned); - command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); - command.Parameters.AddWithValue("@keyInventoryID", item.ID.ToString()); + command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); + command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); + command.Parameters.Add(database.CreateParameter("assetType", item.AssetType)); + command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder)); + command.Parameters.Add(database.CreateParameter("avatarID", item.Owner)); + command.Parameters.Add(database.CreateParameter("inventoryName", item.Name)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description)); + command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions)); + command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions)); + command.Parameters.Add(database.CreateParameter("invType", item.InvType)); + command.Parameters.Add(database.CreateParameter("creatorID", item.Creator)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice)); + command.Parameters.Add(database.CreateParameter("saleType", item.SaleType)); + command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate)); + command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); + command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); + command.Parameters.Add(database.CreateParameter("flags", item.Flags)); + command.Parameters.Add(database.CreateParameter("@keyInventoryID", item.ID)); try { @@ -547,225 +583,221 @@ namespace OpenSim.Data.MSSQL } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error updating item :" + e.Message); } } } + // See IInventoryDataPlugin + /// /// Delete an item in inventory database /// - /// the item UUID + /// the item UUID public void deleteInventoryItem(UUID itemID) { - try + using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID")) { - Dictionary param = new Dictionary(); - param["uuid"] = itemID.ToString(); + command.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + try + { - using (IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param)) + command.ExecuteNonQuery(); + } + catch (Exception e) { - cmd.ExecuteNonQuery(); + m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message); } } - catch (SqlException e) - { - m_log.Error(e.ToString()); - } } + #endregion + + #region Private methods + /// - /// Creates a new inventory folder + /// Delete an item in inventory database /// - /// Folder to create - public void addInventoryFolder(InventoryFolderBase folder) + /// the item ID + /// connection to the database + private void DeleteItemsInFolder(UUID folderID, SqlConnection connection) { - string sql = - "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; - sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; - - - using (AutoClosingSqlCommand command = database.Query(sql)) + using (SqlCommand command = new SqlCommand("DELETE FROM inventoryitems WHERE folderID=@folderID", connection)) { - command.Parameters.AddWithValue("folderID", folder.ID.ToString()); - command.Parameters.AddWithValue("agentID", folder.Owner.ToString()); - command.Parameters.AddWithValue("parentFolderID", folder.ParentID.ToString()); - command.Parameters.AddWithValue("folderName", folder.Name); - command.Parameters.AddWithValue("type", folder.Type); - command.Parameters.AddWithValue("version", Convert.ToInt32(folder.Version)); + command.Parameters.Add(database.CreateParameter("folderID", folderID)); try { - //IDbCommand result = database.Query(sql, param); command.ExecuteNonQuery(); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message); } } } /// - /// Updates an inventory folder + /// Gets the folder hierarchy in a loop. /// - /// Folder to update - public void updateInventoryFolder(InventoryFolderBase folder) + /// parent ID. + /// SQL command/connection to database + /// + private static List getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command) { - using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + - "agentID = @agentID, " + - "parentFolderID = @parentFolderID," + - "folderName = @folderName," + - "type = @type," + - "version = @version where " + - "folderID = @keyFolderID;")) + command.Parameters["@parentID"].Value = parentID.ToString(); + + List folders = getInventoryFolders(command); + + if (folders.Count > 0) { - SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); - SqlParameter param2 = new SqlParameter("@agentID", folder.Owner.ToString()); - SqlParameter param3 = new SqlParameter("@parentFolderID", folder.ParentID.ToString()); - SqlParameter param4 = new SqlParameter("@folderName", folder.Name); - SqlParameter param5 = new SqlParameter("@type", folder.Type); - SqlParameter param6 = new SqlParameter("@version", Convert.ToInt32(folder.Version)); - SqlParameter param7 = new SqlParameter("@keyFolderID", folder.ID.ToString()); - 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); + List tempFolders = new List(); - try + foreach (InventoryFolderBase folderBase in folders) { - command.ExecuteNonQuery(); + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); } - catch (Exception e) + + if (tempFolders.Count > 0) { - m_log.Error(e.ToString()); + folders.AddRange(tempFolders); } } + return folders; } /// - /// Updates an inventory folder + /// Gets the inventory folders. /// - /// Folder to update - public void moveInventoryFolder(InventoryFolderBase folder) + /// parentID, use UUID.Zero to get root + /// user id, use UUID.Zero, if you want all folders from a parentID. + /// + private List getInventoryFolders(UUID parentID, UUID user) { - using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + - "parentFolderID = @parentFolderID," + - "folderID = @keyFolderID;")) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid")) { - SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); - SqlParameter param2 = new SqlParameter("@parentFolderID", folder.ParentID.ToString()); - SqlParameter param3 = new SqlParameter("@keyFolderID", folder.ID.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - - try + if (user == UUID.Zero) { - command.ExecuteNonQuery(); + command.Parameters.Add(database.CreateParameter("uuid", "%")); } - catch (Exception e) + else { - m_log.Error(e.ToString()); + command.Parameters.Add(database.CreateParameter("uuid", user)); } + command.Parameters.Add(database.CreateParameter("parentID", parentID)); + + return getInventoryFolders(command); } } /// - /// Append a list of all the child folders of a parent folder + /// Gets the inventory folders. /// - /// list where folders will be appended - /// ID of parent - protected void getInventoryFolders(ref List folders, UUID parentID) - { - List subfolderList = getInventoryFolders(parentID); - - foreach (InventoryFolderBase f in subfolderList) - folders.Add(f); - } - - // See IInventoryDataPlugin - public List getFolderHierarchy(UUID parentID) + /// SQLcommand. + /// + private static List getInventoryFolders(AutoClosingSqlCommand command) { - List folders = new List(); - getInventoryFolders(ref folders, parentID); - - for (int i = 0; i < folders.Count; i++) - getInventoryFolders(ref folders, folders[i].ID); + using (IDataReader reader = command.ExecuteReader()) + { - return folders; + List items = new List(); + while (reader.Read()) + { + items.Add(readInventoryFolder(reader)); + } + return items; + } } /// - /// Delete a folder in inventory databasae + /// Reads a list of inventory folders returned by a query. /// - /// the folder UUID - protected void deleteOneFolder(UUID folderID) + /// A MSSQL Data Reader + /// A List containing inventory folders + protected static InventoryFolderBase readInventoryFolder(IDataReader reader) { try { - Dictionary param = new Dictionary(); - param["folderID"] = folderID.ToString(); + InventoryFolderBase folder = new InventoryFolderBase(); + folder.Owner = new UUID((string)reader["agentID"]); + folder.ParentID = new UUID((string)reader["parentFolderID"]); + folder.ID = new UUID((string)reader["folderID"]); + folder.Name = (string)reader["folderName"]; + folder.Type = (short)reader["type"]; + folder.Version = Convert.ToUInt16(reader["version"]); - using (IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param)) - { - cmd.ExecuteNonQuery(); - } + return folder; } - catch (SqlException e) + catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error reading inventory folder :" + e.Message); } + + return null; } /// - /// Delete an item in inventory database + /// Reads a one item from an SQL result /// - /// the item ID - protected void deleteItemsInFolder(UUID folderID) + /// The SQL Result + /// the item read + private static InventoryItemBase readInventoryItem(IDataRecord reader) { try { - Dictionary param = new Dictionary(); - param["parentFolderID"] = folderID.ToString(); + InventoryItemBase item = new InventoryItemBase(); + item.ID = new UUID(reader["inventoryID"].ToString()); + item.AssetID = new UUID(reader["assetID"].ToString()); + item.AssetType = Convert.ToInt32(reader["assetType"].ToString()); + item.Folder = new UUID(reader["parentFolderID"].ToString()); + item.Owner = new UUID(reader["avatarID"].ToString()); + item.Name = reader["inventoryName"].ToString(); + item.Description = reader["inventoryDescription"].ToString(); + item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]); + item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]); + item.InvType = Convert.ToInt32(reader["invType"].ToString()); + item.Creator = new UUID(reader["creatorID"].ToString()); + item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); + item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); + item.SalePrice = Convert.ToInt32(reader["salePrice"]); + item.SaleType = Convert.ToByte(reader["saleType"]); + item.CreationDate = Convert.ToInt32(reader["creationDate"]); + item.GroupID = new UUID(reader["groupID"].ToString()); + item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); + item.Flags = Convert.ToUInt32(reader["flags"]); - using (IDbCommand cmd = - database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param)) - { - cmd.ExecuteNonQuery(); - } + return item; } catch (SqlException e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error reading inventory item :" + e.Message); } + + return null; } /// - /// Delete an inventory folder + /// Delete a folder in inventory databasae /// - /// Id of folder to delete - public void deleteInventoryFolder(UUID folderID) + /// the folder UUID + /// connection to database + private void DeleteOneFolder(UUID folderID, SqlConnection connection) { - // lock (database) + try { - List subFolders = getFolderHierarchy(folderID); - - //Delete all sub-folders - foreach (InventoryFolderBase f in subFolders) + using (SqlCommand command = new SqlCommand("DELETE FROM inventoryfolders WHERE folderID=@folderID", connection)) { - deleteOneFolder(f.ID); - deleteItemsInFolder(f.ID); - } + command.Parameters.Add(database.CreateParameter("folderID", folderID)); - //Delete the actual row - deleteOneFolder(folderID); - deleteItemsInFolder(folderID); + command.ExecuteNonQuery(); + } + } + catch (SqlException e) + { + m_log.Error("[INVENTORY DB] Error deleting folder :" + e.Message); } } + #endregion } } -- cgit v1.1