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