MSSQLInventoryData.cs 30 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726
  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 OpenSim 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. */
  28. using System;
  29. using System.Collections.Generic;
  30. using System.Data;
  31. using System.Data.SqlClient;
  32. using libsecondlife;
  33. using OpenSim.Framework.Console;
  34. namespace OpenSim.Framework.Data.MSSQL
  35. {
  36. /// <summary>
  37. /// A MySQL interface for the inventory server
  38. /// </summary>
  39. public class MSSQLInventoryData : IInventoryData
  40. {
  41. /// <summary>
  42. /// The database manager
  43. /// </summary>
  44. private MSSQLManager database;
  45. /// <summary>
  46. /// Loads and initialises this database plugin
  47. /// </summary>
  48. public void Initialise()
  49. {
  50. IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
  51. string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
  52. string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
  53. string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
  54. string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
  55. string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
  56. database =
  57. new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
  58. settingPassword);
  59. TestTables();
  60. }
  61. #region Test and initialization code
  62. private void UpgradeFoldersTable(string tableName)
  63. {
  64. // null as the version, indicates that the table didn't exist
  65. if (tableName == null)
  66. {
  67. database.ExecuteResourceSql("CreateFoldersTable.sql");
  68. //database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql");
  69. return;
  70. }
  71. }
  72. private void UpgradeItemsTable(string tableName)
  73. {
  74. // null as the version, indicates that the table didn't exist
  75. if (tableName == null)
  76. {
  77. database.ExecuteResourceSql("CreateItemsTable.sql");
  78. //database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql");
  79. return;
  80. }
  81. }
  82. private void TestTables()
  83. {
  84. Dictionary<string, string> tableList = new Dictionary<string, string>();
  85. tableList["inventoryfolders"] = null;
  86. tableList["inventoryitems"] = null;
  87. database.GetTableVersion(tableList);
  88. UpgradeFoldersTable(tableList["inventoryfolders"]);
  89. UpgradeItemsTable(tableList["inventoryitems"]);
  90. }
  91. #endregion
  92. /// <summary>
  93. /// The name of this DB provider
  94. /// </summary>
  95. /// <returns>Name of DB provider</returns>
  96. public string getName()
  97. {
  98. return "MSSQL Inventory Data Interface";
  99. }
  100. /// <summary>
  101. /// Closes this DB provider
  102. /// </summary>
  103. public void Close()
  104. {
  105. // Do nothing.
  106. }
  107. /// <summary>
  108. /// Returns the version of this DB provider
  109. /// </summary>
  110. /// <returns>A string containing the DB provider</returns>
  111. public string getVersion()
  112. {
  113. return database.getVersion();
  114. }
  115. /// <summary>
  116. /// Returns a list of items in a specified folder
  117. /// </summary>
  118. /// <param name="folderID">The folder to search</param>
  119. /// <returns>A list containing inventory items</returns>
  120. public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
  121. {
  122. try
  123. {
  124. lock (database)
  125. {
  126. List<InventoryItemBase> items = new List<InventoryItemBase>();
  127. Dictionary<string, string> param = new Dictionary<string, string>();
  128. param["parentFolderID"] = folderID.ToString();
  129. IDbCommand result =
  130. database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param);
  131. IDataReader reader = result.ExecuteReader();
  132. while (reader.Read())
  133. items.Add(readInventoryItem(reader));
  134. reader.Close();
  135. result.Dispose();
  136. return items;
  137. }
  138. }
  139. catch (Exception e)
  140. {
  141. database.Reconnect();
  142. MainLog.Instance.Error(e.ToString());
  143. return null;
  144. }
  145. }
  146. /// <summary>
  147. /// Returns a list of the root folders within a users inventory
  148. /// </summary>
  149. /// <param name="user">The user whos inventory is to be searched</param>
  150. /// <returns>A list of folder objects</returns>
  151. public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
  152. {
  153. try
  154. {
  155. lock (database)
  156. {
  157. Dictionary<string, string> param = new Dictionary<string, string>();
  158. param["uuid"] = user.ToString();
  159. param["zero"] = LLUUID.Zero.ToString();
  160. IDbCommand result =
  161. database.Query(
  162. "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
  163. IDataReader reader = result.ExecuteReader();
  164. List<InventoryFolderBase> items = new List<InventoryFolderBase>();
  165. while (reader.Read())
  166. items.Add(readInventoryFolder(reader));
  167. reader.Close();
  168. result.Dispose();
  169. return items;
  170. }
  171. }
  172. catch (Exception e)
  173. {
  174. database.Reconnect();
  175. MainLog.Instance.Error(e.ToString());
  176. return null;
  177. }
  178. }
  179. // see InventoryItemBase.getUserRootFolder
  180. public InventoryFolderBase getUserRootFolder(LLUUID user)
  181. {
  182. try
  183. {
  184. lock (database)
  185. {
  186. Dictionary<string, string> param = new Dictionary<string, string>();
  187. param["uuid"] = user.ToString();
  188. param["zero"] = LLUUID.Zero.ToString();
  189. IDbCommand result =
  190. database.Query(
  191. "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
  192. IDataReader reader = result.ExecuteReader();
  193. List<InventoryFolderBase> items = new List<InventoryFolderBase>();
  194. while (reader.Read())
  195. items.Add(readInventoryFolder(reader));
  196. InventoryFolderBase rootFolder = null;
  197. // There should only ever be one root folder for a user. However, if there's more
  198. // than one we'll simply use the first one rather than failing. It would be even
  199. // nicer to print some message to this effect, but this feels like it's too low a
  200. // to put such a message out, and it's too minor right now to spare the time to
  201. // suitably refactor.
  202. if (items.Count > 0)
  203. {
  204. rootFolder = items[0];
  205. }
  206. reader.Close();
  207. result.Dispose();
  208. return rootFolder;
  209. }
  210. }
  211. catch (Exception e)
  212. {
  213. database.Reconnect();
  214. MainLog.Instance.Error(e.ToString());
  215. return null;
  216. }
  217. }
  218. /// <summary>
  219. /// Returns a list of folders in a users inventory contained within the specified folder
  220. /// </summary>
  221. /// <param name="parentID">The folder to search</param>
  222. /// <returns>A list of inventory folders</returns>
  223. public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
  224. {
  225. try
  226. {
  227. lock (database)
  228. {
  229. Dictionary<string, string> param = new Dictionary<string, string>();
  230. param["parentFolderID"] = parentID.ToString();
  231. IDbCommand result =
  232. database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param);
  233. IDataReader reader = result.ExecuteReader();
  234. List<InventoryFolderBase> items = new List<InventoryFolderBase>();
  235. while (reader.Read())
  236. items.Add(readInventoryFolder(reader));
  237. reader.Close();
  238. result.Dispose();
  239. return items;
  240. }
  241. }
  242. catch (Exception e)
  243. {
  244. database.Reconnect();
  245. MainLog.Instance.Error(e.ToString());
  246. return null;
  247. }
  248. }
  249. /// <summary>
  250. /// Reads a one item from an SQL result
  251. /// </summary>
  252. /// <param name="reader">The SQL Result</param>
  253. /// <returns>the item read</returns>
  254. private InventoryItemBase readInventoryItem(IDataReader reader)
  255. {
  256. try
  257. {
  258. InventoryItemBase item = new InventoryItemBase();
  259. item.inventoryID = new LLUUID((string) reader["inventoryID"]);
  260. item.assetID = new LLUUID((string) reader["assetID"]);
  261. item.assetType = (int) reader["assetType"];
  262. item.parentFolderID = new LLUUID((string) reader["parentFolderID"]);
  263. item.avatarID = new LLUUID((string) reader["avatarID"]);
  264. item.inventoryName = (string) reader["inventoryName"];
  265. item.inventoryDescription = (string) reader["inventoryDescription"];
  266. item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
  267. item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
  268. item.invType = (int) reader["invType"];
  269. item.creatorsID = new LLUUID((string) reader["creatorID"]);
  270. item.inventoryBasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
  271. item.inventoryEveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
  272. return item;
  273. }
  274. catch (SqlException e)
  275. {
  276. MainLog.Instance.Error(e.ToString());
  277. }
  278. return null;
  279. }
  280. /// <summary>
  281. /// Returns a specified inventory item
  282. /// </summary>
  283. /// <param name="item">The item to return</param>
  284. /// <returns>An inventory item</returns>
  285. public InventoryItemBase getInventoryItem(LLUUID itemID)
  286. {
  287. try
  288. {
  289. lock (database)
  290. {
  291. Dictionary<string, string> param = new Dictionary<string, string>();
  292. param["inventoryID"] = itemID.ToString();
  293. IDbCommand result =
  294. database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param);
  295. IDataReader reader = result.ExecuteReader();
  296. InventoryItemBase item = null;
  297. if (reader.Read())
  298. item = readInventoryItem(reader);
  299. reader.Close();
  300. result.Dispose();
  301. return item;
  302. }
  303. }
  304. catch (Exception e)
  305. {
  306. database.Reconnect();
  307. MainLog.Instance.Error(e.ToString());
  308. }
  309. return null;
  310. }
  311. /// <summary>
  312. /// Reads a list of inventory folders returned by a query.
  313. /// </summary>
  314. /// <param name="reader">A MySQL Data Reader</param>
  315. /// <returns>A List containing inventory folders</returns>
  316. protected InventoryFolderBase readInventoryFolder(IDataReader reader)
  317. {
  318. try
  319. {
  320. InventoryFolderBase folder = new InventoryFolderBase();
  321. folder.agentID = new LLUUID((string) reader["agentID"]);
  322. folder.parentID = new LLUUID((string) reader["parentFolderID"]);
  323. folder.folderID = new LLUUID((string) reader["folderID"]);
  324. folder.name = (string) reader["folderName"];
  325. folder.type = (short) reader["type"];
  326. folder.version = (ushort) ((int) reader["version"]);
  327. return folder;
  328. }
  329. catch (Exception e)
  330. {
  331. MainLog.Instance.Error(e.ToString());
  332. }
  333. return null;
  334. }
  335. /// <summary>
  336. /// Returns a specified inventory folder
  337. /// </summary>
  338. /// <param name="folder">The folder to return</param>
  339. /// <returns>A folder class</returns>
  340. public InventoryFolderBase getInventoryFolder(LLUUID folderID)
  341. {
  342. try
  343. {
  344. lock (database)
  345. {
  346. Dictionary<string, string> param = new Dictionary<string, string>();
  347. param["uuid"] = folderID.ToString();
  348. IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param);
  349. IDataReader reader = result.ExecuteReader();
  350. reader.Read();
  351. InventoryFolderBase folder = readInventoryFolder(reader);
  352. reader.Close();
  353. result.Dispose();
  354. return folder;
  355. }
  356. }
  357. catch (Exception e)
  358. {
  359. database.Reconnect();
  360. MainLog.Instance.Error(e.ToString());
  361. return null;
  362. }
  363. }
  364. /// <summary>
  365. /// Adds a specified item to the database
  366. /// </summary>
  367. /// <param name="item">The inventory item</param>
  368. public void addInventoryItem(InventoryItemBase item)
  369. {
  370. string sql = "INSERT INTO inventoryitems";
  371. sql +=
  372. "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]) VALUES ";
  373. sql +=
  374. "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, @inventoryBasePermissions, @inventoryEveryOnePermissions);";
  375. try
  376. {
  377. Dictionary<string, string> param = new Dictionary<string, string>();
  378. param["inventoryID"] = item.inventoryID.ToString();
  379. param["assetID"] = item.assetID.ToString();
  380. param["assetType"] = item.assetType.ToString();
  381. param["parentFolderID"] = item.parentFolderID.ToString();
  382. param["avatarID"] = item.avatarID.ToString();
  383. param["inventoryName"] = item.inventoryName;
  384. param["inventoryDescription"] = item.inventoryDescription;
  385. param["inventoryNextPermissions"] = item.inventoryNextPermissions.ToString();
  386. param["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString();
  387. param["invType"] = Convert.ToString(item.invType);
  388. param["creatorID"] = item.creatorsID.ToString();
  389. param["inventoryBasePermissions"] = Convert.ToString(item.inventoryBasePermissions);
  390. param["inventoryEveryOnePermissions"] = Convert.ToString(item.inventoryEveryOnePermissions);
  391. IDbCommand result = database.Query(sql, param);
  392. result.ExecuteNonQuery();
  393. result.Dispose();
  394. }
  395. catch (SqlException e)
  396. {
  397. MainLog.Instance.Error(e.ToString());
  398. }
  399. }
  400. /// <summary>
  401. /// Updates the specified inventory item
  402. /// </summary>
  403. /// <param name="item">Inventory item to update</param>
  404. public void updateInventoryItem(InventoryItemBase item)
  405. {
  406. SqlCommand command = new SqlCommand("UPDATE inventoryitems set inventoryID = @inventoryID, " +
  407. "assetID = @assetID, " +
  408. "assetType = @assetType" +
  409. "parentFolderID = @parentFolderID" +
  410. "avatarID = @avatarID" +
  411. "inventoryName = @inventoryName" +
  412. "inventoryDescription = @inventoryDescription" +
  413. "inventoryNextPermissions = @inventoryNextPermissions" +
  414. "inventoryCurrentPermissions = @inventoryCurrentPermissions" +
  415. "invType = @invType" +
  416. "creatorID = @creatorID" +
  417. "inventoryBasePermissions = @inventoryBasePermissions" +
  418. "inventoryEveryOnePermissions = @inventoryEveryOnePermissions) where " +
  419. "inventoryID = @keyInventoryID;", database.getConnection());
  420. SqlParameter param1 = new SqlParameter("@inventoryID", item.inventoryID.ToString());
  421. SqlParameter param2 = new SqlParameter("@assetID", item.assetID);
  422. SqlParameter param3 = new SqlParameter("@assetType", item.assetType);
  423. SqlParameter param4 = new SqlParameter("@parentFolderID", item.parentFolderID);
  424. SqlParameter param5 = new SqlParameter("@avatarID", item.avatarID);
  425. SqlParameter param6 = new SqlParameter("@inventoryName", item.inventoryName);
  426. SqlParameter param7 = new SqlParameter("@inventoryDescription", item.inventoryDescription);
  427. SqlParameter param8 = new SqlParameter("@inventoryNextPermissions", item.inventoryNextPermissions);
  428. SqlParameter param9 = new SqlParameter("@inventoryCurrentPermissions", item.inventoryCurrentPermissions);
  429. SqlParameter param10 = new SqlParameter("@invType", item.invType);
  430. SqlParameter param11 = new SqlParameter("@creatorID", item.creatorsID);
  431. SqlParameter param12 = new SqlParameter("@inventoryBasePermissions", item.inventoryBasePermissions);
  432. SqlParameter param13 = new SqlParameter("@inventoryEveryOnePermissions", item.inventoryEveryOnePermissions);
  433. SqlParameter param14 = new SqlParameter("@keyInventoryID", item.inventoryID.ToString());
  434. command.Parameters.Add(param1);
  435. command.Parameters.Add(param2);
  436. command.Parameters.Add(param3);
  437. command.Parameters.Add(param4);
  438. command.Parameters.Add(param5);
  439. command.Parameters.Add(param6);
  440. command.Parameters.Add(param7);
  441. command.Parameters.Add(param8);
  442. command.Parameters.Add(param9);
  443. command.Parameters.Add(param10);
  444. command.Parameters.Add(param11);
  445. command.Parameters.Add(param12);
  446. command.Parameters.Add(param13);
  447. command.Parameters.Add(param14);
  448. try
  449. {
  450. command.ExecuteNonQuery();
  451. }
  452. catch (Exception e)
  453. {
  454. MainLog.Instance.Error(e.ToString());
  455. }
  456. }
  457. /// <summary>
  458. ///
  459. /// </summary>
  460. /// <param name="item"></param>
  461. public void deleteInventoryItem(LLUUID itemID)
  462. {
  463. try
  464. {
  465. Dictionary<string, string> param = new Dictionary<string, string>();
  466. param["uuid"] = itemID.ToString();
  467. IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param);
  468. cmd.ExecuteNonQuery();
  469. cmd.Dispose();
  470. }
  471. catch (SqlException e)
  472. {
  473. database.Reconnect();
  474. MainLog.Instance.Error(e.ToString());
  475. }
  476. }
  477. /// <summary>
  478. /// Creates a new inventory folder
  479. /// </summary>
  480. /// <param name="folder">Folder to create</param>
  481. public void addInventoryFolder(InventoryFolderBase folder)
  482. {
  483. string sql =
  484. "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES ";
  485. sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
  486. Dictionary<string, string> param = new Dictionary<string, string>();
  487. param["folderID"] = folder.folderID.ToString();
  488. param["agentID"] = folder.agentID.ToString();
  489. param["parentFolderID"] = folder.parentID.ToString();
  490. param["folderName"] = folder.name;
  491. param["type"] = Convert.ToString(folder.type);
  492. param["version"] = Convert.ToString(folder.version);
  493. try
  494. {
  495. IDbCommand result = database.Query(sql, param);
  496. result.ExecuteNonQuery();
  497. result.Dispose();
  498. }
  499. catch (Exception e)
  500. {
  501. MainLog.Instance.Error(e.ToString());
  502. }
  503. }
  504. /// <summary>
  505. /// Updates an inventory folder
  506. /// </summary>
  507. /// <param name="folder">Folder to update</param>
  508. public void updateInventoryFolder(InventoryFolderBase folder)
  509. {
  510. SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " +
  511. "agentID = @agentID, " +
  512. "parentFolderID = @parentFolderID," +
  513. "folderName = @folderName," +
  514. "type = @type," +
  515. "version = @version where " +
  516. "folderID = @keyFolderID;", database.getConnection());
  517. SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToString());
  518. SqlParameter param2 = new SqlParameter("@agentID", folder.agentID.ToString());
  519. SqlParameter param3 = new SqlParameter("@parentFolderID", folder.parentID.ToString());
  520. SqlParameter param4 = new SqlParameter("@folderName", folder.name);
  521. SqlParameter param5 = new SqlParameter("@type", folder.type);
  522. SqlParameter param6 = new SqlParameter("@version", folder.version);
  523. SqlParameter param7 = new SqlParameter("@keyFolderID", folder.folderID.ToString());
  524. command.Parameters.Add(param1);
  525. command.Parameters.Add(param2);
  526. command.Parameters.Add(param3);
  527. command.Parameters.Add(param4);
  528. command.Parameters.Add(param5);
  529. command.Parameters.Add(param6);
  530. command.Parameters.Add(param7);
  531. try
  532. {
  533. command.ExecuteNonQuery();
  534. }
  535. catch (Exception e)
  536. {
  537. MainLog.Instance.Error(e.ToString());
  538. }
  539. }
  540. /// <summary>
  541. /// Updates an inventory folder
  542. /// </summary>
  543. /// <param name="folder">Folder to update</param>
  544. public void moveInventoryFolder(InventoryFolderBase folder)
  545. {
  546. SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " +
  547. "parentFolderID = @parentFolderID," +
  548. "folderID = @keyFolderID;", database.getConnection());
  549. SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToString());
  550. SqlParameter param2 = new SqlParameter("@parentFolderID", folder.parentID.ToString());
  551. SqlParameter param3 = new SqlParameter("@keyFolderID", folder.folderID.ToString());
  552. command.Parameters.Add(param1);
  553. command.Parameters.Add(param2);
  554. command.Parameters.Add(param3);
  555. try
  556. {
  557. command.ExecuteNonQuery();
  558. }
  559. catch (Exception e)
  560. {
  561. MainLog.Instance.Error(e.ToString());
  562. }
  563. }
  564. /// <summary>
  565. /// Append a list of all the child folders of a parent folder
  566. /// </summary>
  567. /// <param name="folders">list where folders will be appended</param>
  568. /// <param name="parentID">ID of parent</param>
  569. protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
  570. {
  571. List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
  572. foreach (InventoryFolderBase f in subfolderList)
  573. folders.Add(f);
  574. }
  575. /// <summary>
  576. /// Returns all child folders in the hierarchy from the parent folder and down
  577. /// </summary>
  578. /// <param name="parentID">The folder to get subfolders for</param>
  579. /// <returns>A list of inventory folders</returns>
  580. protected List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
  581. {
  582. List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
  583. getInventoryFolders(ref folders, parentID);
  584. for (int i = 0; i < folders.Count; i++)
  585. getInventoryFolders(ref folders, folders[i].folderID);
  586. return folders;
  587. }
  588. protected void deleteOneFolder(LLUUID folderID)
  589. {
  590. try
  591. {
  592. Dictionary<string, string> param = new Dictionary<string, string>();
  593. param["folderID"] = folderID.ToString();
  594. IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param);
  595. cmd.ExecuteNonQuery();
  596. cmd.Dispose();
  597. }
  598. catch (SqlException e)
  599. {
  600. database.Reconnect();
  601. MainLog.Instance.Error(e.ToString());
  602. }
  603. }
  604. protected void deleteItemsInFolder(LLUUID folderID)
  605. {
  606. try
  607. {
  608. Dictionary<string, string> param = new Dictionary<string, string>();
  609. param["parentFolderID"] = folderID.ToString();
  610. IDbCommand cmd =
  611. database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param);
  612. cmd.ExecuteNonQuery();
  613. cmd.Dispose();
  614. }
  615. catch (SqlException e)
  616. {
  617. database.Reconnect();
  618. MainLog.Instance.Error(e.ToString());
  619. }
  620. }
  621. /// <summary>
  622. /// Delete an inventory folder
  623. /// </summary>
  624. /// <param name="folderId">Id of folder to delete</param>
  625. public void deleteInventoryFolder(LLUUID folderID)
  626. {
  627. lock (database)
  628. {
  629. List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
  630. //Delete all sub-folders
  631. foreach (InventoryFolderBase f in subFolders)
  632. {
  633. deleteOneFolder(f.folderID);
  634. deleteItemsInFolder(f.folderID);
  635. }
  636. //Delete the actual row
  637. deleteOneFolder(folderID);
  638. deleteItemsInFolder(folderID);
  639. }
  640. }
  641. }
  642. }