aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLInventoryData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLInventoryData.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLInventoryData.cs902
1 files changed, 902 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs
new file mode 100644
index 0000000..e9b10f3
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs
@@ -0,0 +1,902 @@
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
28using System;
29using System.Collections.Generic;
30using System.Reflection;
31using log4net;
32using MySql.Data.MySqlClient;
33using OpenMetaverse;
34using OpenSim.Framework;
35using OpenSim.Data;
36
37namespace OpenSim.Data.MySQL
38{
39 /// <summary>
40 /// A MySQL interface for the inventory server
41 /// </summary>
42 public class MySQLInventoryData : IInventoryDataPlugin
43 {
44 private static readonly ILog m_log
45 = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 private string m_connectionString;
48 private object m_dbLock = new object();
49
50 public string Version { get { return "1.0.0.0"; } }
51
52 public void Initialise()
53 {
54 m_log.Info("[MySQLInventoryData]: " + Name + " cannot be default-initialized!");
55 throw new PluginNotInitialisedException (Name);
56 }
57
58 /// <summary>
59 /// <para>Initialises Inventory interface</para>
60 /// <para>
61 /// <list type="bullet">
62 /// <item>Loads and initialises the MySQL storage plugin</item>
63 /// <item>warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
64 /// <item>Check for migration</item>
65 /// </list>
66 /// </para>
67 /// </summary>
68 /// <param name="connect">connect string</param>
69 public void Initialise(string connect)
70 {
71 m_connectionString = connect;
72
73 // This actually does the roll forward assembly stuff
74 Assembly assem = GetType().Assembly;
75
76 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
77 {
78 dbcon.Open();
79 Migration m = new Migration(dbcon, assem, "InventoryStore");
80 m.Update();
81 }
82 }
83
84 /// <summary>
85 /// The name of this DB provider
86 /// </summary>
87 /// <returns>Name of DB provider</returns>
88 public string Name
89 {
90 get { return "MySQL Inventory Data Interface"; }
91 }
92
93 /// <summary>
94 /// Closes this DB provider
95 /// </summary>
96 /// <remarks>do nothing</remarks>
97 public void Dispose()
98 {
99 // Do nothing.
100 }
101
102 /// <summary>
103 /// Returns a list of items in a specified folder
104 /// </summary>
105 /// <param name="folderID">The folder to search</param>
106 /// <returns>A list containing inventory items</returns>
107 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
108 {
109 try
110 {
111 lock (m_dbLock)
112 {
113 List<InventoryItemBase> items = new List<InventoryItemBase>();
114
115 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
116 {
117 dbcon.Open();
118
119 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid", dbcon))
120 {
121 result.Parameters.AddWithValue("?uuid", folderID.ToString());
122
123 using (MySqlDataReader reader = result.ExecuteReader())
124 {
125 while (reader.Read())
126 {
127 // A null item (because something went wrong) breaks everything in the folder
128 InventoryItemBase item = readInventoryItem(reader);
129 if (item != null)
130 items.Add(item);
131 }
132
133 return items;
134 }
135 }
136 }
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.Error(e.Message, e);
142 return null;
143 }
144 }
145
146 /// <summary>
147 /// Returns a list of the root folders within a users inventory
148 /// </summary>
149 /// <param name="user">The user whose inventory is to be searched</param>
150 /// <returns>A list of folder objects</returns>
151 public List<InventoryFolderBase> getUserRootFolders(UUID user)
152 {
153 try
154 {
155 lock (m_dbLock)
156 {
157 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
158 {
159 dbcon.Open();
160
161 using (MySqlCommand result = new MySqlCommand(
162 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", dbcon))
163 {
164 result.Parameters.AddWithValue("?uuid", user.ToString());
165 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
166
167 using (MySqlDataReader reader = result.ExecuteReader())
168 {
169 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
170 while (reader.Read())
171 items.Add(readInventoryFolder(reader));
172
173 return items;
174 }
175 }
176 }
177 }
178 }
179 catch (Exception e)
180 {
181 m_log.Error(e.Message, e);
182 return null;
183 }
184 }
185
186
187 /// <summary>
188 /// see <see cref="InventoryItemBase.getUserRootFolder"/>
189 /// </summary>
190 /// <param name="user">The user UUID</param>
191 /// <returns></returns>
192 public InventoryFolderBase getUserRootFolder(UUID user)
193 {
194 try
195 {
196 lock (m_dbLock)
197 {
198 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
199 {
200 dbcon.Open();
201
202 using (MySqlCommand result = new MySqlCommand(
203 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", dbcon))
204 {
205 result.Parameters.AddWithValue("?uuid", user.ToString());
206 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
207
208 using (MySqlDataReader reader = result.ExecuteReader())
209 {
210 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
211 while (reader.Read())
212 items.Add(readInventoryFolder(reader));
213
214 InventoryFolderBase rootFolder = null;
215
216 // There should only ever be one root folder for a user. However, if there's more
217 // than one we'll simply use the first one rather than failing. It would be even
218 // nicer to print some message to this effect, but this feels like it's too low a
219 // to put such a message out, and it's too minor right now to spare the time to
220 // suitably refactor.
221 if (items.Count > 0)
222 rootFolder = items[0];
223
224 return rootFolder;
225 }
226 }
227 }
228 }
229 }
230 catch (Exception e)
231 {
232 m_log.Error(e.Message, e);
233 return null;
234 }
235 }
236
237 /// <summary>
238 /// Return a list of folders in a users inventory contained within the specified folder.
239 /// This method is only used in tests - in normal operation the user always have one,
240 /// and only one, root folder.
241 /// </summary>
242 /// <param name="parentID">The folder to search</param>
243 /// <returns>A list of inventory folders</returns>
244 public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
245 {
246 try
247 {
248 lock (m_dbLock)
249 {
250 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
251 {
252 dbcon.Open();
253
254 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid", dbcon))
255 {
256 result.Parameters.AddWithValue("?uuid", parentID.ToString());
257 using (MySqlDataReader reader = result.ExecuteReader())
258 {
259 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
260
261 while (reader.Read())
262 items.Add(readInventoryFolder(reader));
263
264 return items;
265 }
266 }
267 }
268 }
269 }
270 catch (Exception e)
271 {
272 m_log.Error(e.Message, e);
273 return null;
274 }
275 }
276
277 /// <summary>
278 /// Reads a one item from an SQL result
279 /// </summary>
280 /// <param name="reader">The SQL Result</param>
281 /// <returns>the item read</returns>
282 private static InventoryItemBase readInventoryItem(MySqlDataReader reader)
283 {
284 try
285 {
286 InventoryItemBase item = new InventoryItemBase();
287
288 // TODO: this is to handle a case where NULLs creep in there, which we are not sure is endemic to the system, or legacy. It would be nice to live fix these.
289 // (DBGuid.FromDB() reads db NULLs as well, returns UUID.Zero)
290 item.CreatorId = reader["creatorID"].ToString();
291
292 // Be a bit safer in parsing these because the
293 // database doesn't enforce them to be not null, and
294 // the inventory still works if these are weird in the
295 // db
296
297 // (Empty is Ok, but "weird" will throw!)
298 item.Owner = DBGuid.FromDB(reader["avatarID"]);
299 item.GroupID = DBGuid.FromDB(reader["groupID"]);
300
301 // Rest of the parsing. If these UUID's fail, we're dead anyway
302 item.ID = DBGuid.FromDB(reader["inventoryID"]);
303 item.AssetID = DBGuid.FromDB(reader["assetID"]);
304 item.AssetType = (int) reader["assetType"];
305 item.Folder = DBGuid.FromDB(reader["parentFolderID"]);
306 item.Name = (string)(reader["inventoryName"] ?? String.Empty);
307 item.Description = (string)(reader["inventoryDescription"] ?? String.Empty);
308 item.NextPermissions = (uint) reader["inventoryNextPermissions"];
309 item.CurrentPermissions = (uint) reader["inventoryCurrentPermissions"];
310 item.InvType = (int) reader["invType"];
311 item.BasePermissions = (uint) reader["inventoryBasePermissions"];
312 item.EveryOnePermissions = (uint) reader["inventoryEveryOnePermissions"];
313 item.GroupPermissions = (uint) reader["inventoryGroupPermissions"];
314 item.SalePrice = (int) reader["salePrice"];
315 item.SaleType = unchecked((byte)(Convert.ToSByte(reader["saleType"])));
316 item.CreationDate = (int) reader["creationDate"];
317 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
318 item.Flags = (uint) reader["flags"];
319
320 return item;
321 }
322 catch (MySqlException e)
323 {
324 m_log.Error(e.ToString());
325 }
326
327 return null;
328 }
329
330 /// <summary>
331 /// Returns a specified inventory item
332 /// </summary>
333 /// <param name="item">The item to return</param>
334 /// <returns>An inventory item</returns>
335 public InventoryItemBase getInventoryItem(UUID itemID)
336 {
337 try
338 {
339 lock (m_dbLock)
340 {
341 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
342 {
343 dbcon.Open();
344
345 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", dbcon))
346 {
347 result.Parameters.AddWithValue("?uuid", itemID.ToString());
348
349 using (MySqlDataReader reader = result.ExecuteReader())
350 {
351 InventoryItemBase item = null;
352 if (reader.Read())
353 item = readInventoryItem(reader);
354
355 return item;
356 }
357 }
358 }
359 }
360 }
361 catch (Exception e)
362 {
363 m_log.Error(e.Message, e);
364 }
365 return null;
366 }
367
368 /// <summary>
369 /// Reads a list of inventory folders returned by a query.
370 /// </summary>
371 /// <param name="reader">A MySQL Data Reader</param>
372 /// <returns>A List containing inventory folders</returns>
373 protected static InventoryFolderBase readInventoryFolder(MySqlDataReader reader)
374 {
375 try
376 {
377 InventoryFolderBase folder = new InventoryFolderBase();
378 folder.Owner = DBGuid.FromDB(reader["agentID"]);
379 folder.ParentID = DBGuid.FromDB(reader["parentFolderID"]);
380 folder.ID = DBGuid.FromDB(reader["folderID"]);
381 folder.Name = (string) reader["folderName"];
382 folder.Type = (short) reader["type"];
383 folder.Version = (ushort) ((int) reader["version"]);
384 return folder;
385 }
386 catch (Exception e)
387 {
388 m_log.Error(e.Message, e);
389 }
390
391 return null;
392 }
393
394
395 /// <summary>
396 /// Returns a specified inventory folder
397 /// </summary>
398 /// <param name="folderID">The folder to return</param>
399 /// <returns>A folder class</returns>
400 public InventoryFolderBase getInventoryFolder(UUID folderID)
401 {
402 try
403 {
404 lock (m_dbLock)
405 {
406 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
407 {
408 dbcon.Open();
409
410 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", dbcon))
411 {
412 result.Parameters.AddWithValue("?uuid", folderID.ToString());
413
414 using (MySqlDataReader reader = result.ExecuteReader())
415 {
416 InventoryFolderBase folder = null;
417 if (reader.Read())
418 folder = readInventoryFolder(reader);
419
420 return folder;
421 }
422 }
423 }
424 }
425 }
426 catch (Exception e)
427 {
428 m_log.Error(e.Message, e);
429 return null;
430 }
431 }
432
433 /// <summary>
434 /// Adds a specified item to the database
435 /// </summary>
436 /// <param name="item">The inventory item</param>
437 public void addInventoryItem(InventoryItemBase item)
438 {
439 string sql =
440 "REPLACE INTO inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName"
441 + ", inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType"
442 + ", creatorID, inventoryBasePermissions, inventoryEveryOnePermissions, inventoryGroupPermissions, salePrice, saleType"
443 + ", creationDate, groupID, groupOwned, flags) VALUES ";
444 sql +=
445 "(?inventoryID, ?assetID, ?assetType, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription"
446 + ", ?inventoryNextPermissions, ?inventoryCurrentPermissions, ?invType, ?creatorID"
447 + ", ?inventoryBasePermissions, ?inventoryEveryOnePermissions, ?inventoryGroupPermissions, ?salePrice, ?saleType, ?creationDate"
448 + ", ?groupID, ?groupOwned, ?flags)";
449
450 string itemName = item.Name;
451 if (item.Name.Length > 64)
452 {
453 itemName = item.Name.Substring(0, 64);
454 m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length + " to " + itemName.Length + " characters on add item");
455 }
456
457 string itemDesc = item.Description;
458 if (item.Description.Length > 128)
459 {
460 itemDesc = item.Description.Substring(0, 128);
461 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length + " to " + itemDesc.Length + " characters on add item");
462 }
463
464 try
465 {
466 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
467 {
468 dbcon.Open();
469
470 using (MySqlCommand result = new MySqlCommand(sql, dbcon))
471 {
472 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString());
473 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString());
474 result.Parameters.AddWithValue("?assetType", item.AssetType.ToString());
475 result.Parameters.AddWithValue("?parentFolderID", item.Folder.ToString());
476 result.Parameters.AddWithValue("?avatarID", item.Owner.ToString());
477 result.Parameters.AddWithValue("?inventoryName", itemName);
478 result.Parameters.AddWithValue("?inventoryDescription", itemDesc);
479 result.Parameters.AddWithValue("?inventoryNextPermissions", item.NextPermissions.ToString());
480 result.Parameters.AddWithValue("?inventoryCurrentPermissions",
481 item.CurrentPermissions.ToString());
482 result.Parameters.AddWithValue("?invType", item.InvType);
483 result.Parameters.AddWithValue("?creatorID", item.CreatorId);
484 result.Parameters.AddWithValue("?inventoryBasePermissions", item.BasePermissions);
485 result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.EveryOnePermissions);
486 result.Parameters.AddWithValue("?inventoryGroupPermissions", item.GroupPermissions);
487 result.Parameters.AddWithValue("?salePrice", item.SalePrice);
488 result.Parameters.AddWithValue("?saleType", unchecked((sbyte)item.SaleType));
489 result.Parameters.AddWithValue("?creationDate", item.CreationDate);
490 result.Parameters.AddWithValue("?groupID", item.GroupID);
491 result.Parameters.AddWithValue("?groupOwned", item.GroupOwned);
492 result.Parameters.AddWithValue("?flags", item.Flags);
493
494 lock (m_dbLock)
495 result.ExecuteNonQuery();
496
497 result.Dispose();
498 }
499
500 using (MySqlCommand result = new MySqlCommand("update inventoryfolders set version=version+1 where folderID = ?folderID", dbcon))
501 {
502 result.Parameters.AddWithValue("?folderID", item.Folder.ToString());
503
504 lock (m_dbLock)
505 result.ExecuteNonQuery();
506 }
507 }
508 }
509 catch (MySqlException e)
510 {
511 m_log.Error(e.ToString());
512 }
513 }
514
515 /// <summary>
516 /// Updates the specified inventory item
517 /// </summary>
518 /// <param name="item">Inventory item to update</param>
519 public void updateInventoryItem(InventoryItemBase item)
520 {
521 addInventoryItem(item);
522 }
523
524 /// <summary>
525 /// Detele the specified inventory item
526 /// </summary>
527 /// <param name="item">The inventory item UUID to delete</param>
528 public void deleteInventoryItem(UUID itemID)
529 {
530 try
531 {
532 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
533 {
534 dbcon.Open();
535
536 using (MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", dbcon))
537 {
538 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
539
540 lock (m_dbLock)
541 cmd.ExecuteNonQuery();
542 }
543 }
544 }
545 catch (MySqlException e)
546 {
547 m_log.Error(e.Message, e);
548 }
549 }
550
551 public InventoryItemBase queryInventoryItem(UUID itemID)
552 {
553 return getInventoryItem(itemID);
554 }
555
556 public InventoryFolderBase queryInventoryFolder(UUID folderID)
557 {
558 return getInventoryFolder(folderID);
559 }
560
561 /// <summary>
562 /// Creates a new inventory folder
563 /// </summary>
564 /// <param name="folder">Folder to create</param>
565 public void addInventoryFolder(InventoryFolderBase folder)
566 {
567 string sql =
568 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
569 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)";
570
571 string folderName = folder.Name;
572 if (folderName.Length > 64)
573 {
574 folderName = folderName.Substring(0, 64);
575 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length + " to " + folderName.Length + " characters on add folder");
576 }
577
578 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
579 {
580 dbcon.Open();
581
582 using (MySqlCommand cmd = new MySqlCommand(sql, dbcon))
583 {
584 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
585 cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString());
586 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
587 cmd.Parameters.AddWithValue("?folderName", folderName);
588 cmd.Parameters.AddWithValue("?type", folder.Type);
589 cmd.Parameters.AddWithValue("?version", folder.Version);
590
591 try
592 {
593 lock (m_dbLock)
594 {
595 cmd.ExecuteNonQuery();
596 }
597 }
598 catch (Exception e)
599 {
600 m_log.Error(e.ToString());
601 }
602 }
603 }
604 }
605
606 /// <summary>
607 /// Updates an inventory folder
608 /// </summary>
609 /// <param name="folder">Folder to update</param>
610 public void updateInventoryFolder(InventoryFolderBase folder)
611 {
612 addInventoryFolder(folder);
613 }
614
615 /// <summary>
616 /// Move an inventory folder
617 /// </summary>
618 /// <param name="folder">Folder to move</param>
619 /// <remarks>UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID</remarks>
620 public void moveInventoryFolder(InventoryFolderBase folder)
621 {
622 string sql =
623 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
624
625 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
626 {
627 dbcon.Open();
628
629 using (MySqlCommand cmd = new MySqlCommand(sql, dbcon))
630 {
631 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
632 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
633
634 try
635 {
636 lock (m_dbLock)
637 {
638 cmd.ExecuteNonQuery();
639 }
640 }
641 catch (Exception e)
642 {
643 m_log.Error(e.ToString());
644 }
645 }
646 }
647 }
648
649 /// <summary>
650 /// Append a list of all the child folders of a parent folder
651 /// </summary>
652 /// <param name="folders">list where folders will be appended</param>
653 /// <param name="parentID">ID of parent</param>
654 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, UUID parentID)
655 {
656 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
657
658 foreach (InventoryFolderBase f in subfolderList)
659 folders.Add(f);
660 }
661
662
663 /// <summary>
664 /// See IInventoryDataPlugin
665 /// </summary>
666 /// <param name="parentID"></param>
667 /// <returns></returns>
668 public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
669 {
670 /* Note: There are subtle changes between this implementation of getFolderHierarchy and the previous one
671 * - We will only need to hit the database twice instead of n times.
672 * - We assume the database is well-formed - no stranded/dangling folders, all folders in heirarchy owned
673 * by the same person, each user only has 1 inventory heirarchy
674 * - The returned list is not ordered, instead of breadth-first ordered
675 There are basically 2 usage cases for getFolderHeirarchy:
676 1) Getting the user's entire inventory heirarchy when they log in
677 2) Finding a subfolder heirarchy to delete when emptying the trash.
678 This implementation will pull all inventory folders from the database, and then prune away any folder that
679 is not part of the requested sub-heirarchy. The theory is that it is cheaper to make 1 request from the
680 database than to make n requests. This pays off only if requested heirarchy is large.
681 By making this choice, we are making the worst case better at the cost of making the best case worse.
682 This way is generally better because we don't have to rebuild the connection/sql query per subfolder,
683 even if we end up getting more data from the SQL server than we need.
684 - Francis
685 */
686 try
687 {
688 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
689 Dictionary<UUID, List<InventoryFolderBase>> hashtable = new Dictionary<UUID, List<InventoryFolderBase>>(); ;
690 List<InventoryFolderBase> parentFolder = new List<InventoryFolderBase>();
691 bool buildResultsFromHashTable = false;
692
693 lock (m_dbLock)
694 {
695 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
696 {
697 dbcon.Open();
698
699 /* Fetch the parent folder from the database to determine the agent ID, and if
700 * we're querying the root of the inventory folder tree */
701 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", dbcon))
702 {
703 result.Parameters.AddWithValue("?uuid", parentID.ToString());
704
705 using (MySqlDataReader reader = result.ExecuteReader())
706 {
707 // Should be at most 1 result
708 while (reader.Read())
709 parentFolder.Add(readInventoryFolder(reader));
710 }
711 }
712
713 if (parentFolder.Count >= 1) // No result means parent folder does not exist
714 {
715 if (parentFolder[0].ParentID == UUID.Zero) // We are querying the root folder
716 {
717 /* Get all of the agent's folders from the database, put them in a list and return it */
718 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid", dbcon))
719 {
720 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
721
722 using (MySqlDataReader reader = result.ExecuteReader())
723 {
724 while (reader.Read())
725 {
726 InventoryFolderBase curFolder = readInventoryFolder(reader);
727 if (curFolder.ID != parentID) // Do not need to add the root node of the tree to the list
728 folders.Add(curFolder);
729 }
730 }
731 }
732 } // if we are querying the root folder
733 else // else we are querying a subtree of the inventory folder tree
734 {
735 /* Get all of the agent's folders from the database, put them all in a hash table
736 * indexed by their parent ID */
737 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid", dbcon))
738 {
739 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
740
741 using (MySqlDataReader reader = result.ExecuteReader())
742 {
743 while (reader.Read())
744 {
745 InventoryFolderBase curFolder = readInventoryFolder(reader);
746 if (hashtable.ContainsKey(curFolder.ParentID)) // Current folder already has a sibling
747 hashtable[curFolder.ParentID].Add(curFolder); // append to sibling list
748 else // else current folder has no known (yet) siblings
749 {
750 List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>();
751 siblingList.Add(curFolder);
752 // Current folder has no known (yet) siblings
753 hashtable.Add(curFolder.ParentID, siblingList);
754 }
755 } // while more items to read from the database
756 }
757 }
758
759 // Set flag so we know we need to build the results from the hash table after
760 // we unlock the database
761 buildResultsFromHashTable = true;
762
763 } // else we are querying a subtree of the inventory folder tree
764 } // if folder parentID exists
765
766 if (buildResultsFromHashTable)
767 {
768 /* We have all of the user's folders stored in a hash table indexed by their parent ID
769 * and we need to return the requested subtree. We will build the requested subtree
770 * by performing a breadth-first-search on the hash table */
771 if (hashtable.ContainsKey(parentID))
772 folders.AddRange(hashtable[parentID]);
773 for (int i = 0; i < folders.Count; i++) // **Note: folders.Count is *not* static
774 if (hashtable.ContainsKey(folders[i].ID))
775 folders.AddRange(hashtable[folders[i].ID]);
776 }
777 }
778 } // lock (database)
779
780 return folders;
781 }
782 catch (Exception e)
783 {
784 m_log.Error(e.Message, e);
785 return null;
786 }
787 }
788
789 /// <summary>
790 /// Delete a folder from database
791 /// </summary>
792 /// <param name="folderID">the folder UUID</param>
793 protected void deleteOneFolder(UUID folderID)
794 {
795 try
796 {
797 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
798 {
799 dbcon.Open();
800
801 // System folders can never be deleted. Period.
802 using (MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid and type=-1", dbcon))
803 {
804 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
805
806 lock (m_dbLock)
807 cmd.ExecuteNonQuery();
808 }
809 }
810 }
811 catch (MySqlException e)
812 {
813 m_log.Error(e.Message, e);
814 }
815 }
816
817 /// <summary>
818 /// Delete all item in a folder
819 /// </summary>
820 /// <param name="folderID">the folder UUID</param>
821 protected void deleteItemsInFolder(UUID folderID)
822 {
823 try
824 {
825 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
826 {
827 dbcon.Open();
828
829 using (MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", dbcon))
830 {
831 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
832
833 lock (m_dbLock)
834 cmd.ExecuteNonQuery();
835 }
836 }
837 }
838 catch (MySqlException e)
839 {
840 m_log.Error(e.ToString());
841 }
842 }
843
844 /// <summary>
845 /// Deletes an inventory folder
846 /// </summary>
847 /// <param name="folderId">Id of folder to delete</param>
848 public void deleteInventoryFolder(UUID folderID)
849 {
850 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
851
852 //Delete all sub-folders
853 foreach (InventoryFolderBase f in subFolders)
854 {
855 deleteOneFolder(f.ID);
856 deleteItemsInFolder(f.ID);
857 }
858
859 //Delete the actual row
860 deleteOneFolder(folderID);
861 deleteItemsInFolder(folderID);
862 }
863
864 public List<InventoryItemBase> fetchActiveGestures(UUID avatarID)
865 {
866 lock (m_dbLock)
867 {
868 try
869 {
870 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
871 {
872 dbcon.Open();
873
874 using (MySqlCommand sqlCmd = new MySqlCommand(
875 "SELECT * FROM inventoryitems WHERE avatarId = ?uuid AND assetType = ?type and flags & 1", dbcon))
876 {
877 sqlCmd.Parameters.AddWithValue("?uuid", avatarID.ToString());
878 sqlCmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture);
879
880 using (MySqlDataReader result = sqlCmd.ExecuteReader())
881 {
882 List<InventoryItemBase> list = new List<InventoryItemBase>();
883 while (result.Read())
884 {
885 InventoryItemBase item = readInventoryItem(result);
886 if (item != null)
887 list.Add(item);
888 }
889 return list;
890 }
891 }
892 }
893 }
894 catch (Exception e)
895 {
896 m_log.Error(e.Message, e);
897 return null;
898 }
899 }
900 }
901 }
902}
generated by cgit v1.1 at 2025-02-02 10:19:38 +0000