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