From ff8a76825841533bdc5d534b6f58b2ab964ea6c6 Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Sat, 12 Oct 2013 16:33:45 -0500 Subject: Fernando Oliveira's Postgress SQL Server Data Connector as a single commit. * Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs * PostgreSQL data access implementation * PostgreSQL dll binarie and RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres SQL Type fixes * Postgres SQL Connection string * Data type issues * more fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to internal csharp types * More data type fix (PostgreSQL fields are case sensitive) :( * more field case sensitive fixes * changed the migration files to be case sensitive for fields. * fixed fields case * finished converting, now search for hidden bugs. * some more fixes * bool type fixed * more case fixes; * creatorID case fixed * case fields fixed * fixed default now() for TMStamp fields with don't allow nulls. * fix case sensitve for Region name and Estate name * fixed case for names for search * fix class name Error * Bug fixed on select and migrations * Un-Reverting my change due to Postgres issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for integration with Diva Distro * Added System.Core to prebuild.xml for PG project * Configured to make DIff for Push to OpenSim Project * Diffs only to PostgreSQL mods. --- OpenSim/Data/PGSQL/PGSQLInventoryData.cs | 831 +++++++++++++++++++++++++++++++ 1 file changed, 831 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLInventoryData.cs (limited to 'OpenSim/Data/PGSQL/PGSQLInventoryData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLInventoryData.cs b/OpenSim/Data/PGSQL/PGSQLInventoryData.cs new file mode 100644 index 0000000..c999433 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLInventoryData.cs @@ -0,0 +1,831 @@ +/* + * 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 OpenSimulator 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.Reflection; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using Npgsql; + +namespace OpenSim.Data.PGSQL +{ + /// + /// A PGSQL interface for the inventory server + /// + public class PGSQLInventoryData : IInventoryDataPlugin + { + private const string _migrationStore = "InventoryStore"; + + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database manager + /// + private PGSQLManager database; + private string m_connectionString; + + #region IPlugin members + + [Obsolete("Cannot be default-initialized!")] + public void Initialise() + { + m_log.Info("[PGSQLInventoryData]: " + Name + " cannot be default-initialized!"); + throw new PluginNotInitialisedException(Name); + } + + /// + /// Loads and initialises the PGSQL inventory storage interface + /// + /// connect string + /// use PGSQL_connection.ini + public void Initialise(string connectionString) + { + m_connectionString = connectionString; + database = new PGSQLManager(connectionString); + + //New migrations check of store + database.CheckMigration(_migrationStore); + } + + /// + /// The name of this DB provider + /// + /// A string containing the name of the DB provider + public string Name + { + get { return "PGSQL Inventory Data Interface"; } + } + + /// + /// Closes this DB provider + /// + public void Dispose() + { + database = null; + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string Version + { + get { return database.getVersion(); } + } + + #endregion + + #region Folder methods + + /// + /// 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(UUID user) + { + if (user == UUID.Zero) + return new List(); + + return getInventoryFolders(UUID.Zero, user); + } + + /// + /// see InventoryItemBase.getUserRootFolder + /// + /// the User UUID + /// + public InventoryFolderBase getUserRootFolder(UUID user) + { + List items = getUserRootFolders(user); + + InventoryFolderBase rootFolder = null; + + // 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; + } + + /// + /// 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(UUID parentID) + { + return getInventoryFolders(parentID, UUID.Zero); + } + + /// + /// Returns a specified inventory folder + /// + /// The folder to return + /// A folder class + public InventoryFolderBase getInventoryFolder(UUID folderID) + { + string sql = "SELECT * FROM inventoryfolders WHERE \"folderID\" = :folderID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("folderID", folderID)); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + { + return readInventoryFolder(reader); + } + } + } + m_log.InfoFormat("[INVENTORY DB] : Found no inventory folder with ID : {0}", folderID); + return null; + } + + /// + /// Returns all child folders in the hierarchy from the parent folder and down. + /// Does not return the parent folder itself. + /// + /// The folder to get subfolders for + /// A list of inventory folders + public List getFolderHierarchy(UUID parentID) + { + //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. + + /* NOTE: the implementation below is very inefficient (makes a separate request to get subfolders for + * every found folder, recursively). Inventory code for other DBs has been already rewritten to get ALL + * inventory for a specific user at once. + * + * Meanwhile, one little thing is corrected: getFolderHierarchy(UUID.Zero) doesn't make sense and should never + * be used, so check for that and return an empty list. + */ + + List folders = new List(); + + if (parentID == UUID.Zero) + return folders; + + string sql = "SELECT * FROM inventoryfolders WHERE \"parentFolderID\" = :parentID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("parentID", parentID)); + conn.Open(); + folders.AddRange(getInventoryFolders(cmd)); + + List tempFolders = new List(); + + foreach (InventoryFolderBase folderBase in folders) + { + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, cmd)); + } + if (tempFolders.Count > 0) + { + folders.AddRange(tempFolders); + } + } + return folders; + } + + /// + /// 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 (:folderID, :agentID, :parentFolderID, :folderName, :type, :version);"; + + string folderName = folder.Name; + if (folderName.Length > 64) + { + folderName = folderName.Substring(0, 64); + m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on add"); + } + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID)); + cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); + cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + cmd.Parameters.Add(database.CreateParameter("folderName", folderName)); + cmd.Parameters.Add(database.CreateParameter("type", folder.Type)); + cmd.Parameters.Add(database.CreateParameter("version", folder.Version)); + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message); + } + } + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + public void updateInventoryFolder(InventoryFolderBase folder) + { + string sql = @"UPDATE inventoryfolders SET ""agentID"" = :agentID, + ""parentFolderID"" = :parentFolderID, + ""folderName"" = :folderName, + type = :type, + version = :version + WHERE folderID = :folderID"; + + string folderName = folder.Name; + if (folderName.Length > 64) + { + folderName = folderName.Substring(0, 64); + m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on update"); + } + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID)); + cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); + cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + cmd.Parameters.Add(database.CreateParameter("folderName", folderName)); + cmd.Parameters.Add(database.CreateParameter("type", folder.Type)); + cmd.Parameters.Add(database.CreateParameter("version", folder.Version)); + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message); + } + } + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + public void moveInventoryFolder(InventoryFolderBase folder) + { + string sql = @"UPDATE inventoryfolders SET ""parentFolderID"" = :parentFolderID WHERE ""folderID"" = :folderID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID)); + conn.Open(); + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message); + } + } + } + + /// + /// Delete an inventory folder + /// + /// Id of folder to delete + public void deleteInventoryFolder(UUID folderID) + { + string sql = @"SELECT * FROM inventoryfolders WHERE ""parentFolderID"" = :parentID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + List subFolders; + cmd.Parameters.Add(database.CreateParameter("parentID", UUID.Zero)); + conn.Open(); + subFolders = getFolderHierarchy(folderID, cmd); + + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) + { + DeleteOneFolder(f.ID, conn); + DeleteItemsInFolder(f.ID, conn); + } + + //Delete the actual row + DeleteOneFolder(folderID, conn); + DeleteItemsInFolder(folderID, conn); + } + } + + #endregion + + #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) + { + string sql = @"SELECT * FROM inventoryitems WHERE ""parentFolderID"" = :parentFolderID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("parentFolderID", folderID)); + conn.Open(); + List items = new List(); + + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + items.Add(readInventoryItem(reader)); + } + } + return items; + } + } + + /// + /// Returns a specified inventory item + /// + /// The item ID + /// An inventory item + public InventoryItemBase getInventoryItem(UUID itemID) + { + string sql = @"SELECT * FROM inventoryitems WHERE ""inventoryID"" = :inventoryID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + { + return readInventoryItem(reader); + } + } + } + + m_log.InfoFormat("[INVENTORY DB]: Found no inventory item with ID : {0}", itemID); + return null; + } + + /// + /// Adds a specified item to the database + /// + /// The inventory item + public void addInventoryItem(InventoryItemBase item) + { + if (getInventoryItem(item.ID) != null) + { + updateInventoryItem(item); + return; + } + + string sql = @"INSERT INTO inventoryitems + (""inventoryID"", ""assetID"", ""assetType"", ""parentFolderID"", ""avatarID"", ""inventoryName"", + ""inventoryDescription"", ""inventoryNextPermissions"", ""inventoryCurrentPermissions"", + ""invType"", ""creatorID"", ""inventoryBasePermissions"", ""inventoryEveryOnePermissions"", ""inventoryGroupPermissions"", + ""salePrice"", ""SaleType"", ""creationDate"", ""groupID"", ""groupOwned"", flags) + VALUES + (:inventoryID, :assetID, :assetType, :parentFolderID, :avatarID, :inventoryName, :inventoryDescription, + :inventoryNextPermissions, :inventoryCurrentPermissions, :invType, :creatorID, + :inventoryBasePermissions, :inventoryEveryOnePermissions, :inventoryGroupPermissions, :SalePrice, :SaleType, + :creationDate, :groupID, :groupOwned, :flags)"; + + string itemName = item.Name; + if (item.Name.Length > 64) + { + itemName = item.Name.Substring(0, 64); + m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length.ToString() + " to " + itemName.Length.ToString() + " characters"); + } + + string itemDesc = item.Description; + if (item.Description.Length > 128) + { + itemDesc = item.Description.Substring(0, 128); + m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters"); + } + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand command = new NpgsqlCommand(sql, conn)) + { + 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", itemName)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", itemDesc)); + 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.CreatorId)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions)); + 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)); + conn.Open(); + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB]: Error inserting item :" + e.Message); + } + } + + sql = @"UPDATE inventoryfolders SET version = version + 1 WHERE ""folderID"" = @folderID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand command = new NpgsqlCommand(sql, conn)) + { + command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString())); + conn.Open(); + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB] Error updating inventory folder for new item :" + e.Message); + } + } + } + + /// + /// Updates the specified inventory item + /// + /// Inventory item to update + public void updateInventoryItem(InventoryItemBase item) + { + string sql = @"UPDATE inventoryitems SET ""assetID"" = :assetID, + ""assetType"" = :assetType, + ""parentFolderID"" = :parentFolderID, + ""avatarID"" = :avatarID, + ""inventoryName"" = :inventoryName, + ""inventoryDescription"" = :inventoryDescription, + ""inventoryNextPermissions"" = :inventoryNextPermissions, + ""inventoryCurrentPermissions"" = :inventoryCurrentPermissions, + ""invType"" = :invType, + ""creatorID"" = :creatorID, + ""inventoryBasePermissions"" = :inventoryBasePermissions, + ""inventoryEveryOnePermissions"" = :inventoryEveryOnePermissions, + ""inventoryGroupPermissions"" = :inventoryGroupPermissions, + ""salePrice"" = :SalePrice, + ""saleType"" = :SaleType, + ""creationDate"" = :creationDate, + ""groupID"" = :groupID, + ""groupOwned"" = :groupOwned, + flags = :flags + WHERE ""inventoryID"" = :inventoryID"; + + string itemName = item.Name; + if (item.Name.Length > 64) + { + itemName = item.Name.Substring(0, 64); + m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length.ToString() + " to " + itemName.Length.ToString() + " characters on update"); + } + + string itemDesc = item.Description; + if (item.Description.Length > 128) + { + itemDesc = item.Description.Substring(0, 128); + m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters on update"); + } + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand command = new NpgsqlCommand(sql, conn)) + { + 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", itemName)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", itemDesc)); + 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.CreatorId)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions)); + 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)); + conn.Open(); + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB]: Error updating item :" + e.Message); + } + } + } + + // See IInventoryDataPlugin + + /// + /// Delete an item in inventory database + /// + /// the item UUID + public void deleteInventoryItem(UUID itemID) + { + string sql = @"DELETE FROM inventoryitems WHERE ""inventoryID""=:inventoryID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + try + { + conn.Open(); + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB]: Error deleting item :" + e.Message); + } + } + } + + public InventoryItemBase queryInventoryItem(UUID itemID) + { + return getInventoryItem(itemID); + } + + public InventoryFolderBase queryInventoryFolder(UUID folderID) + { + return getInventoryFolder(folderID); + } + + /// + /// Returns all activated gesture-items in the inventory of the specified avatar. + /// + /// The of the avatar + /// + /// The list of gestures (s) + /// + public List fetchActiveGestures(UUID avatarID) + { + string sql = @"SELECT * FROM inventoryitems WHERE ""avatarID"" = :uuid AND ""assetType"" = :assetType and flags = 1"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(database.CreateParameter("uuid", avatarID)); + cmd.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture)); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + List gestureList = new List(); + while (reader.Read()) + { + gestureList.Add(readInventoryItem(reader)); + } + return gestureList; + } + } + } + + #endregion + + #region Private methods + + /// + /// Delete an item in inventory database + /// + /// the item ID + /// connection to the database + private void DeleteItemsInFolder(UUID folderID, NpgsqlConnection connection) + { + using (NpgsqlCommand command = new NpgsqlCommand(@"DELETE FROM inventoryitems WHERE ""folderID""=:folderID", connection)) + { + command.Parameters.Add(database.CreateParameter("folderID", folderID)); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message); + } + } + } + + /// + /// Gets the folder hierarchy in a loop. + /// + /// parent ID. + /// SQL command/connection to database + /// + private static List getFolderHierarchy(UUID parentID, NpgsqlCommand command) + { + command.Parameters["parentID"].Value = parentID.Guid; //.ToString(); + + List folders = getInventoryFolders(command); + + if (folders.Count > 0) + { + List tempFolders = new List(); + + foreach (InventoryFolderBase folderBase in folders) + { + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); + } + + if (tempFolders.Count > 0) + { + folders.AddRange(tempFolders); + } + } + return folders; + } + + /// + /// Gets the inventory folders. + /// + /// 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) + { + string sql = @"SELECT * FROM inventoryfolders WHERE ""parentFolderID"" = :parentID AND ""agentID"" = :uuid"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand command = new NpgsqlCommand(sql, conn)) + { + if (user == UUID.Zero) + { + command.Parameters.Add(database.CreateParameter("uuid", "%")); + } + else + { + command.Parameters.Add(database.CreateParameter("uuid", user)); + } + command.Parameters.Add(database.CreateParameter("parentID", parentID)); + conn.Open(); + return getInventoryFolders(command); + } + } + + /// + /// Gets the inventory folders. + /// + /// SQLcommand. + /// + private static List getInventoryFolders(NpgsqlCommand command) + { + using (NpgsqlDataReader reader = command.ExecuteReader()) + { + + List items = new List(); + while (reader.Read()) + { + items.Add(readInventoryFolder(reader)); + } + return items; + } + } + + /// + /// Reads a list of inventory folders returned by a query. + /// + /// A PGSQL Data Reader + /// A List containing inventory folders + protected static InventoryFolderBase readInventoryFolder(NpgsqlDataReader reader) + { + try + { + InventoryFolderBase folder = new InventoryFolderBase(); + folder.Owner = DBGuid.FromDB(reader["agentID"]); + folder.ParentID = DBGuid.FromDB(reader["parentFolderID"]); + folder.ID = DBGuid.FromDB(reader["folderID"]); + folder.Name = (string)reader["folderName"]; + folder.Type = (short)reader["type"]; + folder.Version = Convert.ToUInt16(reader["version"]); + + return folder; + } + catch (Exception e) + { + m_log.Error("[INVENTORY DB] Error reading inventory folder :" + e.Message); + } + + return null; + } + + /// + /// Reads a one item from an SQL result + /// + /// The SQL Result + /// the item read + private static InventoryItemBase readInventoryItem(IDataRecord reader) + { + try + { + InventoryItemBase item = new InventoryItemBase(); + + item.ID = DBGuid.FromDB(reader["inventoryID"]); + item.AssetID = DBGuid.FromDB(reader["assetID"]); + item.AssetType = Convert.ToInt32(reader["assetType"].ToString()); + item.Folder = DBGuid.FromDB(reader["parentFolderID"]); + item.Owner = DBGuid.FromDB(reader["avatarID"]); + 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.CreatorId = reader["creatorID"].ToString(); + item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); + item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); + item.GroupPermissions = Convert.ToUInt32(reader["inventoryGroupPermissions"]); + item.SalePrice = Convert.ToInt32(reader["salePrice"]); + item.SaleType = Convert.ToByte(reader["saleType"]); + item.CreationDate = Convert.ToInt32(reader["creationDate"]); + item.GroupID = DBGuid.FromDB(reader["groupID"]); + item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); + item.Flags = Convert.ToUInt32(reader["flags"]); + + return item; + } + catch (NpgsqlException e) + { + m_log.Error("[INVENTORY DB]: Error reading inventory item :" + e.Message); + } + + return null; + } + + /// + /// Delete a folder in inventory databasae + /// + /// the folder UUID + /// connection to database + private void DeleteOneFolder(UUID folderID, NpgsqlConnection connection) + { + try + { + using (NpgsqlCommand command = new NpgsqlCommand(@"DELETE FROM inventoryfolders WHERE ""folderID""=:folderID and type=-1", connection)) + { + command.Parameters.Add(database.CreateParameter("folderID", folderID)); + + command.ExecuteNonQuery(); + } + } + catch (NpgsqlException e) + { + m_log.Error("[INVENTORY DB]: Error deleting folder :" + e.Message); + } + } + + #endregion + } +} -- cgit v1.1