1
0

MSSQLInventoryData.cs 37 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections.Generic;
  29. using System.Data;
  30. using System.Data.SqlClient;
  31. using System.Reflection;
  32. using log4net;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. namespace OpenSim.Data.MSSQL
  36. {
  37. /// <summary>
  38. /// A MSSQL interface for the inventory server
  39. /// </summary>
  40. public class MSSQLInventoryData : IInventoryDataPlugin
  41. {
  42. private const string _migrationStore = "InventoryStore";
  43. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  44. /// <summary>
  45. /// The database manager
  46. /// </summary>
  47. private MSSQLManager database;
  48. private string m_connectionString;
  49. #region IPlugin members
  50. [Obsolete("Cannot be default-initialized!")]
  51. public void Initialise()
  52. {
  53. m_log.Info("[MSSQLInventoryData]: " + Name + " cannot be default-initialized!");
  54. throw new PluginNotInitialisedException(Name);
  55. }
  56. /// <summary>
  57. /// Loads and initialises the MSSQL inventory storage interface
  58. /// </summary>
  59. /// <param name="connectionString">connect string</param>
  60. /// <remarks>use mssql_connection.ini</remarks>
  61. public void Initialise(string connectionString)
  62. {
  63. m_connectionString = connectionString;
  64. database = new MSSQLManager(connectionString);
  65. //New migrations check of store
  66. database.CheckMigration(_migrationStore);
  67. }
  68. /// <summary>
  69. /// The name of this DB provider
  70. /// </summary>
  71. /// <returns>A string containing the name of the DB provider</returns>
  72. public string Name
  73. {
  74. get { return "MSSQL Inventory Data Interface"; }
  75. }
  76. /// <summary>
  77. /// Closes this DB provider
  78. /// </summary>
  79. public void Dispose()
  80. {
  81. database = null;
  82. }
  83. /// <summary>
  84. /// Returns the version of this DB provider
  85. /// </summary>
  86. /// <returns>A string containing the DB provider</returns>
  87. public string Version
  88. {
  89. get { return database.getVersion(); }
  90. }
  91. #endregion
  92. #region Folder methods
  93. /// <summary>
  94. /// Returns a list of the root folders within a users inventory
  95. /// </summary>
  96. /// <param name="user">The user whos inventory is to be searched</param>
  97. /// <returns>A list of folder objects</returns>
  98. public List<InventoryFolderBase> getUserRootFolders(UUID user)
  99. {
  100. if (user == UUID.Zero)
  101. return new List<InventoryFolderBase>();
  102. return getInventoryFolders(UUID.Zero, user);
  103. }
  104. /// <summary>
  105. /// see InventoryItemBase.getUserRootFolder
  106. /// </summary>
  107. /// <param name="user">the User UUID</param>
  108. /// <returns></returns>
  109. public InventoryFolderBase getUserRootFolder(UUID user)
  110. {
  111. List<InventoryFolderBase> items = getUserRootFolders(user);
  112. InventoryFolderBase rootFolder = null;
  113. // There should only ever be one root folder for a user. However, if there's more
  114. // than one we'll simply use the first one rather than failing. It would be even
  115. // nicer to print some message to this effect, but this feels like it's too low a
  116. // to put such a message out, and it's too minor right now to spare the time to
  117. // suitably refactor.
  118. if (items.Count > 0)
  119. {
  120. rootFolder = items[0];
  121. }
  122. return rootFolder;
  123. }
  124. /// <summary>
  125. /// Returns a list of folders in a users inventory contained within the specified folder
  126. /// </summary>
  127. /// <param name="parentID">The folder to search</param>
  128. /// <returns>A list of inventory folders</returns>
  129. public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
  130. {
  131. return getInventoryFolders(parentID, UUID.Zero);
  132. }
  133. /// <summary>
  134. /// Returns a specified inventory folder
  135. /// </summary>
  136. /// <param name="folderID">The folder to return</param>
  137. /// <returns>A folder class</returns>
  138. public InventoryFolderBase getInventoryFolder(UUID folderID)
  139. {
  140. string sql = "SELECT * FROM inventoryfolders WHERE folderID = @folderID";
  141. using (SqlConnection conn = new SqlConnection(m_connectionString))
  142. using (SqlCommand cmd = new SqlCommand(sql, conn))
  143. {
  144. cmd.Parameters.Add(database.CreateParameter("folderID", folderID));
  145. conn.Open();
  146. using (SqlDataReader reader = cmd.ExecuteReader())
  147. {
  148. if (reader.Read())
  149. {
  150. return readInventoryFolder(reader);
  151. }
  152. }
  153. }
  154. m_log.InfoFormat("[INVENTORY DB] : Found no inventory folder with ID : {0}", folderID);
  155. return null;
  156. }
  157. /// <summary>
  158. /// Returns all child folders in the hierarchy from the parent folder and down.
  159. /// Does not return the parent folder itself.
  160. /// </summary>
  161. /// <param name="parentID">The folder to get subfolders for</param>
  162. /// <returns>A list of inventory folders</returns>
  163. public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
  164. {
  165. //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way.
  166. //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
  167. /* NOTE: the implementation below is very inefficient (makes a separate request to get subfolders for
  168. * every found folder, recursively). Inventory code for other DBs has been already rewritten to get ALL
  169. * inventory for a specific user at once.
  170. *
  171. * Meanwhile, one little thing is corrected: getFolderHierarchy(UUID.Zero) doesn't make sense and should never
  172. * be used, so check for that and return an empty list.
  173. */
  174. List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
  175. if (parentID == UUID.Zero)
  176. return folders;
  177. string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
  178. using (SqlConnection conn = new SqlConnection(m_connectionString))
  179. using (SqlCommand cmd = new SqlCommand(sql, conn))
  180. {
  181. cmd.Parameters.Add(database.CreateParameter("@parentID", parentID));
  182. conn.Open();
  183. folders.AddRange(getInventoryFolders(cmd));
  184. List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
  185. foreach (InventoryFolderBase folderBase in folders)
  186. {
  187. tempFolders.AddRange(getFolderHierarchy(folderBase.ID, cmd));
  188. }
  189. if (tempFolders.Count > 0)
  190. {
  191. folders.AddRange(tempFolders);
  192. }
  193. }
  194. return folders;
  195. }
  196. /// <summary>
  197. /// Creates a new inventory folder
  198. /// </summary>
  199. /// <param name="folder">Folder to create</param>
  200. public void addInventoryFolder(InventoryFolderBase folder)
  201. {
  202. string sql = @"INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version])
  203. VALUES (@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
  204. string folderName = folder.Name;
  205. if (folderName.Length > 64)
  206. {
  207. folderName = folderName.Substring(0, 64);
  208. m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on add");
  209. }
  210. using (SqlConnection conn = new SqlConnection(m_connectionString))
  211. using (SqlCommand cmd = new SqlCommand(sql, conn))
  212. {
  213. cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
  214. cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
  215. cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
  216. cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
  217. cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
  218. cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
  219. conn.Open();
  220. try
  221. {
  222. cmd.ExecuteNonQuery();
  223. }
  224. catch (Exception e)
  225. {
  226. m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message);
  227. }
  228. }
  229. }
  230. /// <summary>
  231. /// Updates an inventory folder
  232. /// </summary>
  233. /// <param name="folder">Folder to update</param>
  234. public void updateInventoryFolder(InventoryFolderBase folder)
  235. {
  236. string sql = @"UPDATE inventoryfolders SET agentID = @agentID,
  237. parentFolderID = @parentFolderID,
  238. folderName = @folderName,
  239. type = @type,
  240. version = @version
  241. WHERE folderID = @folderID";
  242. string folderName = folder.Name;
  243. if (folderName.Length > 64)
  244. {
  245. folderName = folderName.Substring(0, 64);
  246. m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on update");
  247. }
  248. using (SqlConnection conn = new SqlConnection(m_connectionString))
  249. using (SqlCommand cmd = new SqlCommand(sql, conn))
  250. {
  251. cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
  252. cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
  253. cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
  254. cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
  255. cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
  256. cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
  257. conn.Open();
  258. try
  259. {
  260. cmd.ExecuteNonQuery();
  261. }
  262. catch (Exception e)
  263. {
  264. m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message);
  265. }
  266. }
  267. }
  268. /// <summary>
  269. /// Updates an inventory folder
  270. /// </summary>
  271. /// <param name="folder">Folder to update</param>
  272. public void moveInventoryFolder(InventoryFolderBase folder)
  273. {
  274. string sql = @"UPDATE inventoryfolders SET parentFolderID = @parentFolderID WHERE folderID = @folderID";
  275. using (SqlConnection conn = new SqlConnection(m_connectionString))
  276. using (SqlCommand cmd = new SqlCommand(sql, conn))
  277. {
  278. cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
  279. cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
  280. conn.Open();
  281. try
  282. {
  283. cmd.ExecuteNonQuery();
  284. }
  285. catch (Exception e)
  286. {
  287. m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message);
  288. }
  289. }
  290. }
  291. /// <summary>
  292. /// Delete an inventory folder
  293. /// </summary>
  294. /// <param name="folderID">Id of folder to delete</param>
  295. public void deleteInventoryFolder(UUID folderID)
  296. {
  297. string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
  298. using (SqlConnection conn = new SqlConnection(m_connectionString))
  299. using (SqlCommand cmd = new SqlCommand(sql, conn))
  300. {
  301. List<InventoryFolderBase> subFolders;
  302. cmd.Parameters.Add(database.CreateParameter("@parentID", UUID.Zero));
  303. conn.Open();
  304. subFolders = getFolderHierarchy(folderID, cmd);
  305. //Delete all sub-folders
  306. foreach (InventoryFolderBase f in subFolders)
  307. {
  308. DeleteOneFolder(f.ID, conn);
  309. DeleteItemsInFolder(f.ID, conn);
  310. }
  311. //Delete the actual row
  312. DeleteOneFolder(folderID, conn);
  313. DeleteItemsInFolder(folderID, conn);
  314. }
  315. }
  316. #endregion
  317. #region Item Methods
  318. /// <summary>
  319. /// Returns a list of items in a specified folder
  320. /// </summary>
  321. /// <param name="folderID">The folder to search</param>
  322. /// <returns>A list containing inventory items</returns>
  323. public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
  324. {
  325. string sql = "SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID";
  326. using (SqlConnection conn = new SqlConnection(m_connectionString))
  327. using (SqlCommand cmd = new SqlCommand(sql, conn))
  328. {
  329. cmd.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
  330. conn.Open();
  331. List<InventoryItemBase> items = new List<InventoryItemBase>();
  332. using (SqlDataReader reader = cmd.ExecuteReader())
  333. {
  334. while (reader.Read())
  335. {
  336. items.Add(readInventoryItem(reader));
  337. }
  338. }
  339. return items;
  340. }
  341. }
  342. /// <summary>
  343. /// Returns a specified inventory item
  344. /// </summary>
  345. /// <param name="itemID">The item ID</param>
  346. /// <returns>An inventory item</returns>
  347. public InventoryItemBase getInventoryItem(UUID itemID)
  348. {
  349. string sql = "SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID";
  350. using (SqlConnection conn = new SqlConnection(m_connectionString))
  351. using (SqlCommand cmd = new SqlCommand(sql, conn))
  352. {
  353. cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
  354. conn.Open();
  355. using (SqlDataReader reader = cmd.ExecuteReader())
  356. {
  357. if (reader.Read())
  358. {
  359. return readInventoryItem(reader);
  360. }
  361. }
  362. }
  363. m_log.InfoFormat("[INVENTORY DB]: Found no inventory item with ID : {0}", itemID);
  364. return null;
  365. }
  366. /// <summary>
  367. /// Adds a specified item to the database
  368. /// </summary>
  369. /// <param name="item">The inventory item</param>
  370. public void addInventoryItem(InventoryItemBase item)
  371. {
  372. if (getInventoryItem(item.ID) != null)
  373. {
  374. updateInventoryItem(item);
  375. return;
  376. }
  377. string sql = @"INSERT INTO inventoryitems
  378. ([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName],
  379. [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions],
  380. [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions], [inventoryGroupPermissions],
  381. [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags])
  382. VALUES
  383. (@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription,
  384. @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID,
  385. @inventoryBasePermissions, @inventoryEveryOnePermissions, @inventoryGroupPermissions, @salePrice, @saleType,
  386. @creationDate, @groupID, @groupOwned, @flags)";
  387. string itemName = item.Name;
  388. if (item.Name.Length > 64)
  389. {
  390. itemName = item.Name.Substring(0, 64);
  391. m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length.ToString() + " to " + itemName.Length.ToString() + " characters");
  392. }
  393. string itemDesc = item.Description;
  394. if (item.Description.Length > 128)
  395. {
  396. itemDesc = item.Description.Substring(0, 128);
  397. m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters");
  398. }
  399. using (SqlConnection conn = new SqlConnection(m_connectionString))
  400. using (SqlCommand command = new SqlCommand(sql, conn))
  401. {
  402. command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
  403. command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
  404. command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
  405. command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
  406. command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
  407. command.Parameters.Add(database.CreateParameter("inventoryName", itemName));
  408. command.Parameters.Add(database.CreateParameter("inventoryDescription", itemDesc));
  409. command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
  410. command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
  411. command.Parameters.Add(database.CreateParameter("invType", item.InvType));
  412. command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorId));
  413. command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
  414. command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
  415. command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions));
  416. command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
  417. command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
  418. command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
  419. command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
  420. command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
  421. command.Parameters.Add(database.CreateParameter("flags", item.Flags));
  422. conn.Open();
  423. try
  424. {
  425. command.ExecuteNonQuery();
  426. }
  427. catch (Exception e)
  428. {
  429. m_log.Error("[INVENTORY DB]: Error inserting item :" + e.Message);
  430. }
  431. }
  432. sql = "UPDATE inventoryfolders SET version = version + 1 WHERE folderID = @folderID";
  433. using (SqlConnection conn = new SqlConnection(m_connectionString))
  434. using (SqlCommand command = new SqlCommand(sql, conn))
  435. {
  436. command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString()));
  437. conn.Open();
  438. try
  439. {
  440. command.ExecuteNonQuery();
  441. }
  442. catch (Exception e)
  443. {
  444. m_log.Error("[INVENTORY DB] Error updating inventory folder for new item :" + e.Message);
  445. }
  446. }
  447. }
  448. /// <summary>
  449. /// Updates the specified inventory item
  450. /// </summary>
  451. /// <param name="item">Inventory item to update</param>
  452. public void updateInventoryItem(InventoryItemBase item)
  453. {
  454. string sql = @"UPDATE inventoryitems SET assetID = @assetID,
  455. assetType = @assetType,
  456. parentFolderID = @parentFolderID,
  457. avatarID = @avatarID,
  458. inventoryName = @inventoryName,
  459. inventoryDescription = @inventoryDescription,
  460. inventoryNextPermissions = @inventoryNextPermissions,
  461. inventoryCurrentPermissions = @inventoryCurrentPermissions,
  462. invType = @invType,
  463. creatorID = @creatorID,
  464. inventoryBasePermissions = @inventoryBasePermissions,
  465. inventoryEveryOnePermissions = @inventoryEveryOnePermissions,
  466. inventoryGroupPermissions = @inventoryGroupPermissions,
  467. salePrice = @salePrice,
  468. saleType = @saleType,
  469. creationDate = @creationDate,
  470. groupID = @groupID,
  471. groupOwned = @groupOwned,
  472. flags = @flags
  473. WHERE inventoryID = @inventoryID";
  474. string itemName = item.Name;
  475. if (item.Name.Length > 64)
  476. {
  477. itemName = item.Name.Substring(0, 64);
  478. m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length.ToString() + " to " + itemName.Length.ToString() + " characters on update");
  479. }
  480. string itemDesc = item.Description;
  481. if (item.Description.Length > 128)
  482. {
  483. itemDesc = item.Description.Substring(0, 128);
  484. m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters on update");
  485. }
  486. using (SqlConnection conn = new SqlConnection(m_connectionString))
  487. using (SqlCommand command = new SqlCommand(sql, conn))
  488. {
  489. command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
  490. command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
  491. command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
  492. command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
  493. command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
  494. command.Parameters.Add(database.CreateParameter("inventoryName", itemName));
  495. command.Parameters.Add(database.CreateParameter("inventoryDescription", itemDesc));
  496. command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
  497. command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
  498. command.Parameters.Add(database.CreateParameter("invType", item.InvType));
  499. command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorId));
  500. command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
  501. command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
  502. command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions));
  503. command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
  504. command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
  505. command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
  506. command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
  507. command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
  508. command.Parameters.Add(database.CreateParameter("flags", item.Flags));
  509. conn.Open();
  510. try
  511. {
  512. command.ExecuteNonQuery();
  513. }
  514. catch (Exception e)
  515. {
  516. m_log.Error("[INVENTORY DB]: Error updating item :" + e.Message);
  517. }
  518. }
  519. }
  520. // See IInventoryDataPlugin
  521. /// <summary>
  522. /// Delete an item in inventory database
  523. /// </summary>
  524. /// <param name="itemID">the item UUID</param>
  525. public void deleteInventoryItem(UUID itemID)
  526. {
  527. string sql = "DELETE FROM inventoryitems WHERE inventoryID=@inventoryID";
  528. using (SqlConnection conn = new SqlConnection(m_connectionString))
  529. using (SqlCommand cmd = new SqlCommand(sql, conn))
  530. {
  531. cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
  532. try
  533. {
  534. conn.Open();
  535. cmd.ExecuteNonQuery();
  536. }
  537. catch (Exception e)
  538. {
  539. m_log.Error("[INVENTORY DB]: Error deleting item :" + e.Message);
  540. }
  541. }
  542. }
  543. public InventoryItemBase queryInventoryItem(UUID itemID)
  544. {
  545. return getInventoryItem(itemID);
  546. }
  547. public InventoryFolderBase queryInventoryFolder(UUID folderID)
  548. {
  549. return getInventoryFolder(folderID);
  550. }
  551. /// <summary>
  552. /// Returns all activated gesture-items in the inventory of the specified avatar.
  553. /// </summary>
  554. /// <param name="avatarID">The <see cref="UUID"/> of the avatar</param>
  555. /// <returns>
  556. /// The list of gestures (<see cref="InventoryItemBase"/>s)
  557. /// </returns>
  558. public List<InventoryItemBase> fetchActiveGestures(UUID avatarID)
  559. {
  560. string sql = "SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1";
  561. using (SqlConnection conn = new SqlConnection(m_connectionString))
  562. using (SqlCommand cmd = new SqlCommand(sql, conn))
  563. {
  564. cmd.Parameters.Add(database.CreateParameter("uuid", avatarID));
  565. cmd.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture));
  566. conn.Open();
  567. using (SqlDataReader reader = cmd.ExecuteReader())
  568. {
  569. List<InventoryItemBase> gestureList = new List<InventoryItemBase>();
  570. while (reader.Read())
  571. {
  572. gestureList.Add(readInventoryItem(reader));
  573. }
  574. return gestureList;
  575. }
  576. }
  577. }
  578. #endregion
  579. #region Private methods
  580. /// <summary>
  581. /// Delete an item in inventory database
  582. /// </summary>
  583. /// <param name="folderID">the item ID</param>
  584. /// <param name="connection">connection to the database</param>
  585. private void DeleteItemsInFolder(UUID folderID, SqlConnection connection)
  586. {
  587. using (SqlCommand command = new SqlCommand("DELETE FROM inventoryitems WHERE folderID=@folderID", connection))
  588. {
  589. command.Parameters.Add(database.CreateParameter("folderID", folderID));
  590. try
  591. {
  592. command.ExecuteNonQuery();
  593. }
  594. catch (Exception e)
  595. {
  596. m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message);
  597. }
  598. }
  599. }
  600. /// <summary>
  601. /// Gets the folder hierarchy in a loop.
  602. /// </summary>
  603. /// <param name="parentID">parent ID.</param>
  604. /// <param name="command">SQL command/connection to database</param>
  605. /// <returns></returns>
  606. private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, SqlCommand command)
  607. {
  608. command.Parameters["@parentID"].Value = parentID.Guid; //.ToString();
  609. List<InventoryFolderBase> folders = getInventoryFolders(command);
  610. if (folders.Count > 0)
  611. {
  612. List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
  613. foreach (InventoryFolderBase folderBase in folders)
  614. {
  615. tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command));
  616. }
  617. if (tempFolders.Count > 0)
  618. {
  619. folders.AddRange(tempFolders);
  620. }
  621. }
  622. return folders;
  623. }
  624. /// <summary>
  625. /// Gets the inventory folders.
  626. /// </summary>
  627. /// <param name="parentID">parentID, use UUID.Zero to get root</param>
  628. /// <param name="user">user id, use UUID.Zero, if you want all folders from a parentID.</param>
  629. /// <returns></returns>
  630. private List<InventoryFolderBase> getInventoryFolders(UUID parentID, UUID user)
  631. {
  632. string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid";
  633. using (SqlConnection conn = new SqlConnection(m_connectionString))
  634. using (SqlCommand command = new SqlCommand(sql, conn))
  635. {
  636. if (user == UUID.Zero)
  637. {
  638. command.Parameters.Add(database.CreateParameter("uuid", "%"));
  639. }
  640. else
  641. {
  642. command.Parameters.Add(database.CreateParameter("uuid", user));
  643. }
  644. command.Parameters.Add(database.CreateParameter("parentID", parentID));
  645. conn.Open();
  646. return getInventoryFolders(command);
  647. }
  648. }
  649. /// <summary>
  650. /// Gets the inventory folders.
  651. /// </summary>
  652. /// <param name="command">SQLcommand.</param>
  653. /// <returns></returns>
  654. private static List<InventoryFolderBase> getInventoryFolders(SqlCommand command)
  655. {
  656. using (SqlDataReader reader = command.ExecuteReader())
  657. {
  658. List<InventoryFolderBase> items = new List<InventoryFolderBase>();
  659. while (reader.Read())
  660. {
  661. items.Add(readInventoryFolder(reader));
  662. }
  663. return items;
  664. }
  665. }
  666. /// <summary>
  667. /// Reads a list of inventory folders returned by a query.
  668. /// </summary>
  669. /// <param name="reader">A MSSQL Data Reader</param>
  670. /// <returns>A List containing inventory folders</returns>
  671. protected static InventoryFolderBase readInventoryFolder(SqlDataReader reader)
  672. {
  673. try
  674. {
  675. InventoryFolderBase folder = new InventoryFolderBase();
  676. folder.Owner = DBGuid.FromDB(reader["agentID"]);
  677. folder.ParentID = DBGuid.FromDB(reader["parentFolderID"]);
  678. folder.ID = DBGuid.FromDB(reader["folderID"]);
  679. folder.Name = (string)reader["folderName"];
  680. folder.Type = (short)reader["type"];
  681. folder.Version = Convert.ToUInt16(reader["version"]);
  682. return folder;
  683. }
  684. catch (Exception e)
  685. {
  686. m_log.Error("[INVENTORY DB] Error reading inventory folder :" + e.Message);
  687. }
  688. return null;
  689. }
  690. /// <summary>
  691. /// Reads a one item from an SQL result
  692. /// </summary>
  693. /// <param name="reader">The SQL Result</param>
  694. /// <returns>the item read</returns>
  695. private static InventoryItemBase readInventoryItem(IDataRecord reader)
  696. {
  697. try
  698. {
  699. InventoryItemBase item = new InventoryItemBase();
  700. item.ID = DBGuid.FromDB(reader["inventoryID"]);
  701. item.AssetID = DBGuid.FromDB(reader["assetID"]);
  702. item.AssetType = Convert.ToInt32(reader["assetType"].ToString());
  703. item.Folder = DBGuid.FromDB(reader["parentFolderID"]);
  704. item.Owner = DBGuid.FromDB(reader["avatarID"]);
  705. item.Name = reader["inventoryName"].ToString();
  706. item.Description = reader["inventoryDescription"].ToString();
  707. item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
  708. item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
  709. item.InvType = Convert.ToInt32(reader["invType"].ToString());
  710. item.CreatorId = reader["creatorID"].ToString();
  711. item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
  712. item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
  713. item.GroupPermissions = Convert.ToUInt32(reader["inventoryGroupPermissions"]);
  714. item.SalePrice = Convert.ToInt32(reader["salePrice"]);
  715. item.SaleType = Convert.ToByte(reader["saleType"]);
  716. item.CreationDate = Convert.ToInt32(reader["creationDate"]);
  717. item.GroupID = DBGuid.FromDB(reader["groupID"]);
  718. item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
  719. item.Flags = Convert.ToUInt32(reader["flags"]);
  720. return item;
  721. }
  722. catch (SqlException e)
  723. {
  724. m_log.Error("[INVENTORY DB]: Error reading inventory item :" + e.Message);
  725. }
  726. return null;
  727. }
  728. /// <summary>
  729. /// Delete a folder in inventory databasae
  730. /// </summary>
  731. /// <param name="folderID">the folder UUID</param>
  732. /// <param name="connection">connection to database</param>
  733. private void DeleteOneFolder(UUID folderID, SqlConnection connection)
  734. {
  735. try
  736. {
  737. using (SqlCommand command = new SqlCommand("DELETE FROM inventoryfolders WHERE folderID=@folderID and type=-1", connection))
  738. {
  739. command.Parameters.Add(database.CreateParameter("folderID", folderID));
  740. command.ExecuteNonQuery();
  741. }
  742. }
  743. catch (SqlException e)
  744. {
  745. m_log.Error("[INVENTORY DB]: Error deleting folder :" + e.Message);
  746. }
  747. }
  748. #endregion
  749. }
  750. }