SQLiteInventoryStore.cs 37 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916
  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.Reflection;
  31. using log4net;
  32. #if CSharpSqlite
  33. using Community.CsharpSqlite.Sqlite;
  34. #else
  35. using Mono.Data.Sqlite;
  36. #endif
  37. using OpenMetaverse;
  38. using OpenSim.Framework;
  39. namespace OpenSim.Data.SQLite
  40. {
  41. /// <summary>
  42. /// An Inventory Interface to the SQLite database
  43. /// </summary>
  44. public class SQLiteInventoryStore : SQLiteUtil, IInventoryDataPlugin
  45. {
  46. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  47. private const string invItemsSelect = "select * from inventoryitems";
  48. private const string invFoldersSelect = "select * from inventoryfolders";
  49. private static SqliteConnection conn;
  50. private static DataSet ds;
  51. private static SqliteDataAdapter invItemsDa;
  52. private static SqliteDataAdapter invFoldersDa;
  53. private static bool m_Initialized = false;
  54. public void Initialise()
  55. {
  56. m_log.Info("[SQLiteInventoryData]: " + Name + " cannot be default-initialized!");
  57. throw new PluginNotInitialisedException(Name);
  58. }
  59. /// <summary>
  60. /// <list type="bullet">
  61. /// <item>Initialises Inventory interface</item>
  62. /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
  63. /// <item>use default URI if connect string string is empty.</item>
  64. /// </list>
  65. /// </summary>
  66. /// <param name="dbconnect">connect string</param>
  67. public void Initialise(string dbconnect)
  68. {
  69. if (!m_Initialized)
  70. {
  71. m_Initialized = true;
  72. if (Util.IsWindows())
  73. Util.LoadArchSpecificWindowsDll("sqlite3.dll");
  74. if (dbconnect == string.Empty)
  75. {
  76. dbconnect = "URI=file:inventoryStore.db,version=3";
  77. }
  78. m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
  79. conn = new SqliteConnection(dbconnect);
  80. conn.Open();
  81. Assembly assem = GetType().Assembly;
  82. Migration m = new Migration(conn, assem, "InventoryStore");
  83. m.Update();
  84. SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
  85. invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
  86. // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
  87. SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
  88. invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
  89. ds = new DataSet();
  90. ds.Tables.Add(createInventoryFoldersTable());
  91. invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
  92. setupFoldersCommands(invFoldersDa, conn);
  93. CreateDataSetMapping(invFoldersDa, "inventoryfolders");
  94. m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
  95. ds.Tables.Add(createInventoryItemsTable());
  96. invItemsDa.Fill(ds.Tables["inventoryitems"]);
  97. setupItemsCommands(invItemsDa, conn);
  98. CreateDataSetMapping(invItemsDa, "inventoryitems");
  99. m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
  100. ds.AcceptChanges();
  101. }
  102. }
  103. /// <summary>
  104. /// Closes the inventory interface
  105. /// </summary>
  106. public void Dispose()
  107. {
  108. if (conn != null)
  109. {
  110. conn.Close();
  111. conn = null;
  112. }
  113. if (invItemsDa != null)
  114. {
  115. invItemsDa.Dispose();
  116. invItemsDa = null;
  117. }
  118. if (invFoldersDa != null)
  119. {
  120. invFoldersDa.Dispose();
  121. invFoldersDa = null;
  122. }
  123. if (ds != null)
  124. {
  125. ds.Dispose();
  126. ds = null;
  127. }
  128. }
  129. /// <summary>
  130. ///
  131. /// </summary>
  132. /// <param name="row"></param>
  133. /// <returns></returns>
  134. public InventoryItemBase buildItem(DataRow row)
  135. {
  136. InventoryItemBase item = new InventoryItemBase();
  137. item.ID = new UUID((string) row["UUID"]);
  138. item.AssetID = new UUID((string) row["assetID"]);
  139. item.AssetType = Convert.ToInt32(row["assetType"]);
  140. item.InvType = Convert.ToInt32(row["invType"]);
  141. item.Folder = new UUID((string) row["parentFolderID"]);
  142. item.Owner = new UUID((string) row["avatarID"]);
  143. item.CreatorIdentification = (string)row["creatorsID"];
  144. item.Name = (string) row["inventoryName"];
  145. item.Description = (string) row["inventoryDescription"];
  146. item.NextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]);
  147. item.CurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]);
  148. item.BasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]);
  149. item.EveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]);
  150. item.GroupPermissions = Convert.ToUInt32(row["inventoryGroupPermissions"]);
  151. // new fields
  152. if (!Convert.IsDBNull(row["salePrice"]))
  153. item.SalePrice = Convert.ToInt32(row["salePrice"]);
  154. if (!Convert.IsDBNull(row["saleType"]))
  155. item.SaleType = Convert.ToByte(row["saleType"]);
  156. if (!Convert.IsDBNull(row["creationDate"]))
  157. item.CreationDate = Convert.ToInt32(row["creationDate"]);
  158. if (!Convert.IsDBNull(row["groupID"]))
  159. item.GroupID = new UUID((string)row["groupID"]);
  160. if (!Convert.IsDBNull(row["groupOwned"]))
  161. item.GroupOwned = Convert.ToBoolean(row["groupOwned"]);
  162. if (!Convert.IsDBNull(row["Flags"]))
  163. item.Flags = Convert.ToUInt32(row["Flags"]);
  164. return item;
  165. }
  166. /// <summary>
  167. /// Fill a database row with item data
  168. /// </summary>
  169. /// <param name="row"></param>
  170. /// <param name="item"></param>
  171. private static void fillItemRow(DataRow row, InventoryItemBase item)
  172. {
  173. row["UUID"] = item.ID.ToString();
  174. row["assetID"] = item.AssetID.ToString();
  175. row["assetType"] = item.AssetType;
  176. row["invType"] = item.InvType;
  177. row["parentFolderID"] = item.Folder.ToString();
  178. row["avatarID"] = item.Owner.ToString();
  179. row["creatorsID"] = item.CreatorIdentification.ToString();
  180. row["inventoryName"] = item.Name;
  181. row["inventoryDescription"] = item.Description;
  182. row["inventoryNextPermissions"] = item.NextPermissions;
  183. row["inventoryCurrentPermissions"] = item.CurrentPermissions;
  184. row["inventoryBasePermissions"] = item.BasePermissions;
  185. row["inventoryEveryOnePermissions"] = item.EveryOnePermissions;
  186. row["inventoryGroupPermissions"] = item.GroupPermissions;
  187. // new fields
  188. row["salePrice"] = item.SalePrice;
  189. row["saleType"] = item.SaleType;
  190. row["creationDate"] = item.CreationDate;
  191. row["groupID"] = item.GroupID.ToString();
  192. row["groupOwned"] = item.GroupOwned;
  193. row["flags"] = item.Flags;
  194. }
  195. /// <summary>
  196. /// Add inventory folder
  197. /// </summary>
  198. /// <param name="folder">Folder base</param>
  199. /// <param name="add">true=create folder. false=update existing folder</param>
  200. /// <remarks>nasty</remarks>
  201. private void addFolder(InventoryFolderBase folder, bool add)
  202. {
  203. lock (ds)
  204. {
  205. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  206. DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
  207. if (inventoryRow == null)
  208. {
  209. if (! add)
  210. m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existant inventory folder: {0}", folder.ID);
  211. inventoryRow = inventoryFolderTable.NewRow();
  212. fillFolderRow(inventoryRow, folder);
  213. inventoryFolderTable.Rows.Add(inventoryRow);
  214. }
  215. else
  216. {
  217. if (add)
  218. m_log.ErrorFormat("Interface Misuse: Attempting to Add inventory folder that already exists: {0}", folder.ID);
  219. fillFolderRow(inventoryRow, folder);
  220. }
  221. invFoldersDa.Update(ds, "inventoryfolders");
  222. }
  223. }
  224. /// <summary>
  225. /// Move an inventory folder
  226. /// </summary>
  227. /// <param name="folder">folder base</param>
  228. private void moveFolder(InventoryFolderBase folder)
  229. {
  230. lock (ds)
  231. {
  232. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  233. DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.ID.ToString());
  234. if (inventoryRow == null)
  235. {
  236. inventoryRow = inventoryFolderTable.NewRow();
  237. fillFolderRow(inventoryRow, folder);
  238. inventoryFolderTable.Rows.Add(inventoryRow);
  239. }
  240. else
  241. {
  242. moveFolderRow(inventoryRow, folder);
  243. }
  244. invFoldersDa.Update(ds, "inventoryfolders");
  245. }
  246. }
  247. /// <summary>
  248. /// add an item in inventory
  249. /// </summary>
  250. /// <param name="item">the item</param>
  251. /// <param name="add">true=add item ; false=update existing item</param>
  252. private void addItem(InventoryItemBase item, bool add)
  253. {
  254. lock (ds)
  255. {
  256. DataTable inventoryItemTable = ds.Tables["inventoryitems"];
  257. DataRow inventoryRow = inventoryItemTable.Rows.Find(item.ID.ToString());
  258. if (inventoryRow == null)
  259. {
  260. if (!add)
  261. m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existant inventory item: {0}", item.ID);
  262. inventoryRow = inventoryItemTable.NewRow();
  263. fillItemRow(inventoryRow, item);
  264. inventoryItemTable.Rows.Add(inventoryRow);
  265. }
  266. else
  267. {
  268. if (add)
  269. m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Add inventory item that already exists: {0}", item.ID);
  270. fillItemRow(inventoryRow, item);
  271. }
  272. invItemsDa.Update(ds, "inventoryitems");
  273. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  274. inventoryRow = inventoryFolderTable.Rows.Find(item.Folder.ToString());
  275. if (inventoryRow != null) //MySQL doesn't throw an exception here, so sqlite shouldn't either.
  276. inventoryRow["version"] = (int)inventoryRow["version"] + 1;
  277. invFoldersDa.Update(ds, "inventoryfolders");
  278. }
  279. }
  280. /// <summary>
  281. /// TODO : DataSet commit
  282. /// </summary>
  283. public void Shutdown()
  284. {
  285. // TODO: DataSet commit
  286. }
  287. /// <summary>
  288. /// The name of this DB provider
  289. /// </summary>
  290. /// <returns>Name of DB provider</returns>
  291. public string Name
  292. {
  293. get { return "SQLite Inventory Data Interface"; }
  294. }
  295. /// <summary>
  296. /// Returns the version of this DB provider
  297. /// </summary>
  298. /// <returns>A string containing the DB provider version</returns>
  299. public string Version
  300. {
  301. get
  302. {
  303. Module module = GetType().Module;
  304. // string dllName = module.Assembly.ManifestModule.Name;
  305. Version dllVersion = module.Assembly.GetName().Version;
  306. return
  307. string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
  308. dllVersion.Revision);
  309. }
  310. }
  311. /// <summary>
  312. /// Returns a list of inventory items contained within the specified folder
  313. /// </summary>
  314. /// <param name="folderID">The UUID of the target folder</param>
  315. /// <returns>A List of InventoryItemBase items</returns>
  316. public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
  317. {
  318. lock (ds)
  319. {
  320. List<InventoryItemBase> retval = new List<InventoryItemBase>();
  321. DataTable inventoryItemTable = ds.Tables["inventoryitems"];
  322. string selectExp = "parentFolderID = '" + folderID + "'";
  323. DataRow[] rows = inventoryItemTable.Select(selectExp);
  324. foreach (DataRow row in rows)
  325. {
  326. retval.Add(buildItem(row));
  327. }
  328. return retval;
  329. }
  330. }
  331. /// <summary>
  332. /// Returns a list of the root folders within a users inventory
  333. /// </summary>
  334. /// <param name="user">The user whos inventory is to be searched</param>
  335. /// <returns>A list of folder objects</returns>
  336. public List<InventoryFolderBase> getUserRootFolders(UUID user)
  337. {
  338. return new List<InventoryFolderBase>();
  339. }
  340. // see InventoryItemBase.getUserRootFolder
  341. public InventoryFolderBase getUserRootFolder(UUID user)
  342. {
  343. lock (ds)
  344. {
  345. List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
  346. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  347. string selectExp = "agentID = '" + user + "' AND parentID = '" + UUID.Zero + "'";
  348. DataRow[] rows = inventoryFolderTable.Select(selectExp);
  349. foreach (DataRow row in rows)
  350. {
  351. folders.Add(buildFolder(row));
  352. }
  353. // There should only ever be one root folder for a user. However, if there's more
  354. // than one we'll simply use the first one rather than failing. It would be even
  355. // nicer to print some message to this effect, but this feels like it's too low a
  356. // to put such a message out, and it's too minor right now to spare the time to
  357. // suitably refactor.
  358. if (folders.Count > 0)
  359. {
  360. return folders[0];
  361. }
  362. return null;
  363. }
  364. }
  365. /// <summary>
  366. /// Append a list of all the child folders of a parent folder
  367. /// </summary>
  368. /// <param name="folders">list where folders will be appended</param>
  369. /// <param name="parentID">ID of parent</param>
  370. protected void getInventoryFolders(ref List<InventoryFolderBase> folders, UUID parentID)
  371. {
  372. lock (ds)
  373. {
  374. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  375. string selectExp = "parentID = '" + parentID + "'";
  376. DataRow[] rows = inventoryFolderTable.Select(selectExp);
  377. foreach (DataRow row in rows)
  378. {
  379. folders.Add(buildFolder(row));
  380. }
  381. }
  382. }
  383. /// <summary>
  384. /// Returns a list of inventory folders contained in the folder 'parentID'
  385. /// </summary>
  386. /// <param name="parentID">The folder to get subfolders for</param>
  387. /// <returns>A list of inventory folders</returns>
  388. public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
  389. {
  390. List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
  391. getInventoryFolders(ref folders, parentID);
  392. return folders;
  393. }
  394. /// <summary>
  395. /// See IInventoryDataPlugin
  396. /// </summary>
  397. /// <param name="parentID"></param>
  398. /// <returns></returns>
  399. public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
  400. {
  401. /* Note: There are subtle changes between this implementation of getFolderHierarchy and the previous one
  402. * - We will only need to hit the database twice instead of n times.
  403. * - We assume the database is well-formed - no stranded/dangling folders, all folders in heirarchy owned
  404. * by the same person, each user only has 1 inventory heirarchy
  405. * - The returned list is not ordered, instead of breadth-first ordered
  406. There are basically 2 usage cases for getFolderHeirarchy:
  407. 1) Getting the user's entire inventory heirarchy when they log in
  408. 2) Finding a subfolder heirarchy to delete when emptying the trash.
  409. This implementation will pull all inventory folders from the database, and then prune away any folder that
  410. is not part of the requested sub-heirarchy. The theory is that it is cheaper to make 1 request from the
  411. database than to make n requests. This pays off only if requested heirarchy is large.
  412. By making this choice, we are making the worst case better at the cost of making the best case worse
  413. - Francis
  414. */
  415. List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
  416. DataRow[] folderRows = null, parentRow;
  417. InventoryFolderBase parentFolder = null;
  418. lock (ds)
  419. {
  420. /* Fetch the parent folder from the database to determine the agent ID.
  421. * Then fetch all inventory folders for that agent from the agent ID.
  422. */
  423. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  424. string selectExp = "UUID = '" + parentID + "'";
  425. parentRow = inventoryFolderTable.Select(selectExp); // Assume at most 1 result
  426. if (parentRow.GetLength(0) >= 1) // No result means parent folder does not exist
  427. {
  428. parentFolder = buildFolder(parentRow[0]);
  429. UUID agentID = parentFolder.Owner;
  430. selectExp = "agentID = '" + agentID + "'";
  431. folderRows = inventoryFolderTable.Select(selectExp);
  432. }
  433. if (folderRows != null && folderRows.GetLength(0) >= 1) // No result means parent folder does not exist
  434. { // or has no children
  435. /* if we're querying the root folder, just return an unordered list of all folders in the user's
  436. * inventory
  437. */
  438. if (parentFolder.ParentID == UUID.Zero)
  439. {
  440. foreach (DataRow row in folderRows)
  441. {
  442. InventoryFolderBase curFolder = buildFolder(row);
  443. if (curFolder.ID != parentID) // Return all folders except the parent folder of heirarchy
  444. folders.Add(buildFolder(row));
  445. }
  446. } // If requesting root folder
  447. /* else we are querying a non-root folder. We currently have a list of all of the user's folders,
  448. * we must construct a list of all folders in the heirarchy below parentID.
  449. * Our first step will be to construct a hash table of all folders, indexed by parent ID.
  450. * Once we have constructed the hash table, we will do a breadth-first traversal on the tree using the
  451. * hash table to find child folders.
  452. */
  453. else
  454. { // Querying a non-root folder
  455. // Build a hash table of all user's inventory folders, indexed by each folder's parent ID
  456. Dictionary<UUID, List<InventoryFolderBase>> hashtable =
  457. new Dictionary<UUID, List<InventoryFolderBase>>(folderRows.GetLength(0));
  458. foreach (DataRow row in folderRows)
  459. {
  460. InventoryFolderBase curFolder = buildFolder(row);
  461. if (curFolder.ParentID != UUID.Zero) // Discard root of tree - not needed
  462. {
  463. if (hashtable.ContainsKey(curFolder.ParentID))
  464. {
  465. // Current folder already has a sibling - append to sibling list
  466. hashtable[curFolder.ParentID].Add(curFolder);
  467. }
  468. else
  469. {
  470. List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>();
  471. siblingList.Add(curFolder);
  472. // Current folder has no known (yet) siblings
  473. hashtable.Add(curFolder.ParentID, siblingList);
  474. }
  475. }
  476. } // For all inventory folders
  477. // Note: Could release the ds lock here - we don't access folderRows or the database anymore.
  478. // This is somewhat of a moot point as the callers of this function usually lock db anyways.
  479. if (hashtable.ContainsKey(parentID)) // if requested folder does have children
  480. folders.AddRange(hashtable[parentID]);
  481. // BreadthFirstSearch build inventory tree **Note: folders.Count is *not* static
  482. for (int i = 0; i < folders.Count; i++)
  483. if (hashtable.ContainsKey(folders[i].ID))
  484. folders.AddRange(hashtable[folders[i].ID]);
  485. } // if requesting a subfolder heirarchy
  486. } // if folder parentID exists and has children
  487. } // lock ds
  488. return folders;
  489. }
  490. /// <summary>
  491. /// Returns an inventory item by its UUID
  492. /// </summary>
  493. /// <param name="item">The UUID of the item to be returned</param>
  494. /// <returns>A class containing item information</returns>
  495. public InventoryItemBase getInventoryItem(UUID item)
  496. {
  497. lock (ds)
  498. {
  499. DataRow row = ds.Tables["inventoryitems"].Rows.Find(item.ToString());
  500. if (row != null)
  501. {
  502. return buildItem(row);
  503. }
  504. else
  505. {
  506. return null;
  507. }
  508. }
  509. }
  510. /// <summary>
  511. /// Returns a specified inventory folder by its UUID
  512. /// </summary>
  513. /// <param name="folder">The UUID of the folder to be returned</param>
  514. /// <returns>A class containing folder information</returns>
  515. public InventoryFolderBase getInventoryFolder(UUID folder)
  516. {
  517. // TODO: Deep voodoo here. If you enable this code then
  518. // multi region breaks. No idea why, but I figured it was
  519. // better to leave multi region at this point. It does mean
  520. // that you don't get to see system textures why creating
  521. // clothes and the like. :(
  522. lock (ds)
  523. {
  524. DataRow row = ds.Tables["inventoryfolders"].Rows.Find(folder.ToString());
  525. if (row != null)
  526. {
  527. return buildFolder(row);
  528. }
  529. else
  530. {
  531. return null;
  532. }
  533. }
  534. }
  535. /// <summary>
  536. /// Creates a new inventory item based on item
  537. /// </summary>
  538. /// <param name="item">The item to be created</param>
  539. public void addInventoryItem(InventoryItemBase item)
  540. {
  541. addItem(item, true);
  542. }
  543. /// <summary>
  544. /// Updates an inventory item with item (updates based on ID)
  545. /// </summary>
  546. /// <param name="item">The updated item</param>
  547. public void updateInventoryItem(InventoryItemBase item)
  548. {
  549. addItem(item, false);
  550. }
  551. /// <summary>
  552. /// Delete an inventory item
  553. /// </summary>
  554. /// <param name="item">The item UUID</param>
  555. public void deleteInventoryItem(UUID itemID)
  556. {
  557. lock (ds)
  558. {
  559. DataTable inventoryItemTable = ds.Tables["inventoryitems"];
  560. DataRow inventoryRow = inventoryItemTable.Rows.Find(itemID.ToString());
  561. if (inventoryRow != null)
  562. {
  563. inventoryRow.Delete();
  564. }
  565. invItemsDa.Update(ds, "inventoryitems");
  566. }
  567. }
  568. public InventoryItemBase queryInventoryItem(UUID itemID)
  569. {
  570. return getInventoryItem(itemID);
  571. }
  572. public InventoryFolderBase queryInventoryFolder(UUID folderID)
  573. {
  574. return getInventoryFolder(folderID);
  575. }
  576. /// <summary>
  577. /// Delete all items in the specified folder
  578. /// </summary>
  579. /// <param name="folderId">id of the folder, whose item content should be deleted</param>
  580. /// <todo>this is horribly inefficient, but I don't want to ruin the overall structure of this implementation</todo>
  581. private void deleteItemsInFolder(UUID folderId)
  582. {
  583. List<InventoryItemBase> items = getInventoryInFolder(folderId);
  584. foreach (InventoryItemBase i in items)
  585. deleteInventoryItem(i.ID);
  586. }
  587. /// <summary>
  588. /// Adds a new folder specified by folder
  589. /// </summary>
  590. /// <param name="folder">The inventory folder</param>
  591. public void addInventoryFolder(InventoryFolderBase folder)
  592. {
  593. addFolder(folder, true);
  594. }
  595. /// <summary>
  596. /// Updates a folder based on its ID with folder
  597. /// </summary>
  598. /// <param name="folder">The inventory folder</param>
  599. public void updateInventoryFolder(InventoryFolderBase folder)
  600. {
  601. addFolder(folder, false);
  602. }
  603. /// <summary>
  604. /// Moves a folder based on its ID with folder
  605. /// </summary>
  606. /// <param name="folder">The inventory folder</param>
  607. public void moveInventoryFolder(InventoryFolderBase folder)
  608. {
  609. moveFolder(folder);
  610. }
  611. /// <summary>
  612. /// Delete a folder
  613. /// </summary>
  614. /// <remarks>
  615. /// This will clean-up any child folders and child items as well
  616. /// </remarks>
  617. /// <param name="folderID">the folder UUID</param>
  618. public void deleteInventoryFolder(UUID folderID)
  619. {
  620. lock (ds)
  621. {
  622. List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
  623. DataTable inventoryFolderTable = ds.Tables["inventoryfolders"];
  624. DataRow inventoryRow;
  625. //Delete all sub-folders
  626. foreach (InventoryFolderBase f in subFolders)
  627. {
  628. inventoryRow = inventoryFolderTable.Rows.Find(f.ID.ToString());
  629. if (inventoryRow != null)
  630. {
  631. deleteItemsInFolder(f.ID);
  632. inventoryRow.Delete();
  633. }
  634. }
  635. //Delete the actual row
  636. inventoryRow = inventoryFolderTable.Rows.Find(folderID.ToString());
  637. if (inventoryRow != null)
  638. {
  639. deleteItemsInFolder(folderID);
  640. inventoryRow.Delete();
  641. }
  642. invFoldersDa.Update(ds, "inventoryfolders");
  643. }
  644. }
  645. /***********************************************************************
  646. *
  647. * Data Table definitions
  648. *
  649. **********************************************************************/
  650. protected void CreateDataSetMapping(IDataAdapter da, string tableName)
  651. {
  652. ITableMapping dbMapping = da.TableMappings.Add(tableName, tableName);
  653. foreach (DataColumn col in ds.Tables[tableName].Columns)
  654. {
  655. dbMapping.ColumnMappings.Add(col.ColumnName, col.ColumnName);
  656. }
  657. }
  658. /// <summary>
  659. /// Create the "inventoryitems" table
  660. /// </summary>
  661. private static DataTable createInventoryItemsTable()
  662. {
  663. DataTable inv = new DataTable("inventoryitems");
  664. createCol(inv, "UUID", typeof (String)); //inventoryID
  665. createCol(inv, "assetID", typeof (String));
  666. createCol(inv, "assetType", typeof (Int32));
  667. createCol(inv, "invType", typeof (Int32));
  668. createCol(inv, "parentFolderID", typeof (String));
  669. createCol(inv, "avatarID", typeof (String));
  670. createCol(inv, "creatorsID", typeof (String));
  671. createCol(inv, "inventoryName", typeof (String));
  672. createCol(inv, "inventoryDescription", typeof (String));
  673. // permissions
  674. createCol(inv, "inventoryNextPermissions", typeof (Int32));
  675. createCol(inv, "inventoryCurrentPermissions", typeof (Int32));
  676. createCol(inv, "inventoryBasePermissions", typeof (Int32));
  677. createCol(inv, "inventoryEveryOnePermissions", typeof (Int32));
  678. createCol(inv, "inventoryGroupPermissions", typeof (Int32));
  679. // sale info
  680. createCol(inv, "salePrice", typeof(Int32));
  681. createCol(inv, "saleType", typeof(Byte));
  682. // creation date
  683. createCol(inv, "creationDate", typeof(Int32));
  684. // group info
  685. createCol(inv, "groupID", typeof(String));
  686. createCol(inv, "groupOwned", typeof(Boolean));
  687. // Flags
  688. createCol(inv, "flags", typeof(UInt32));
  689. inv.PrimaryKey = new DataColumn[] { inv.Columns["UUID"] };
  690. return inv;
  691. }
  692. /// <summary>
  693. /// Creates the "inventoryfolders" table
  694. /// </summary>
  695. /// <returns></returns>
  696. private static DataTable createInventoryFoldersTable()
  697. {
  698. DataTable fol = new DataTable("inventoryfolders");
  699. createCol(fol, "UUID", typeof (String)); //folderID
  700. createCol(fol, "name", typeof (String));
  701. createCol(fol, "agentID", typeof (String));
  702. createCol(fol, "parentID", typeof (String));
  703. createCol(fol, "type", typeof (Int32));
  704. createCol(fol, "version", typeof (Int32));
  705. fol.PrimaryKey = new DataColumn[] {fol.Columns["UUID"]};
  706. return fol;
  707. }
  708. /// <summary>
  709. ///
  710. /// </summary>
  711. /// <param name="da"></param>
  712. /// <param name="conn"></param>
  713. private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn)
  714. {
  715. lock (ds)
  716. {
  717. da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]);
  718. da.InsertCommand.Connection = conn;
  719. da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]);
  720. da.UpdateCommand.Connection = conn;
  721. SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID");
  722. delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
  723. delete.Connection = conn;
  724. da.DeleteCommand = delete;
  725. }
  726. }
  727. /// <summary>
  728. ///
  729. /// </summary>
  730. /// <param name="da"></param>
  731. /// <param name="conn"></param>
  732. private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn)
  733. {
  734. lock (ds)
  735. {
  736. da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]);
  737. da.InsertCommand.Connection = conn;
  738. da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]);
  739. da.UpdateCommand.Connection = conn;
  740. SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID");
  741. delete.Parameters.Add(createSqliteParameter("UUID", typeof(String)));
  742. delete.Connection = conn;
  743. da.DeleteCommand = delete;
  744. }
  745. }
  746. /// <summary>
  747. ///
  748. /// </summary>
  749. /// <param name="row"></param>
  750. /// <returns></returns>
  751. private static InventoryFolderBase buildFolder(DataRow row)
  752. {
  753. InventoryFolderBase folder = new InventoryFolderBase();
  754. folder.ID = new UUID((string) row["UUID"]);
  755. folder.Name = (string) row["name"];
  756. folder.Owner = new UUID((string) row["agentID"]);
  757. folder.ParentID = new UUID((string) row["parentID"]);
  758. folder.Type = Convert.ToInt16(row["type"]);
  759. folder.Version = Convert.ToUInt16(row["version"]);
  760. return folder;
  761. }
  762. /// <summary>
  763. ///
  764. /// </summary>
  765. /// <param name="row"></param>
  766. /// <param name="folder"></param>
  767. private static void fillFolderRow(DataRow row, InventoryFolderBase folder)
  768. {
  769. row["UUID"] = folder.ID.ToString();
  770. row["name"] = folder.Name;
  771. row["agentID"] = folder.Owner.ToString();
  772. row["parentID"] = folder.ParentID.ToString();
  773. row["type"] = folder.Type;
  774. row["version"] = folder.Version;
  775. }
  776. /// <summary>
  777. ///
  778. /// </summary>
  779. /// <param name="row"></param>
  780. /// <param name="folder"></param>
  781. private static void moveFolderRow(DataRow row, InventoryFolderBase folder)
  782. {
  783. row["UUID"] = folder.ID.ToString();
  784. row["parentID"] = folder.ParentID.ToString();
  785. }
  786. public List<InventoryItemBase> fetchActiveGestures (UUID avatarID)
  787. {
  788. lock (ds)
  789. {
  790. List<InventoryItemBase> items = new List<InventoryItemBase>();
  791. DataTable inventoryItemTable = ds.Tables["inventoryitems"];
  792. string selectExp
  793. = "avatarID = '" + avatarID + "' AND assetType = " + (int)AssetType.Gesture + " AND flags = 1";
  794. //m_log.DebugFormat("[SQL]: sql = " + selectExp);
  795. DataRow[] rows = inventoryItemTable.Select(selectExp);
  796. foreach (DataRow row in rows)
  797. {
  798. items.Add(buildItem(row));
  799. }
  800. return items;
  801. }
  802. }
  803. }
  804. }