diff options
author | David Walter Seikel | 2016-11-03 21:44:39 +1000 |
---|---|---|
committer | David Walter Seikel | 2016-11-03 21:44:39 +1000 |
commit | 134f86e8d5c414409631b25b8c6f0ee45fbd8631 (patch) | |
tree | 216b89d3fb89acfb81be1e440c25c41ab09fa96d /OpenSim/Data/PGSQL/PGSQLInventoryData.cs | |
parent | More changing to production grid. Double oops. (diff) | |
download | opensim-SC-134f86e8d5c414409631b25b8c6f0ee45fbd8631.zip opensim-SC-134f86e8d5c414409631b25b8c6f0ee45fbd8631.tar.gz opensim-SC-134f86e8d5c414409631b25b8c6f0ee45fbd8631.tar.bz2 opensim-SC-134f86e8d5c414409631b25b8c6f0ee45fbd8631.tar.xz |
Initial update to OpenSim 0.8.2.1 source code.
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLInventoryData.cs')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLInventoryData.cs | 831 |
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 | |||
28 | using System; | ||
29 | using System.Collections.Generic; | ||
30 | using System.Data; | ||
31 | using System.Reflection; | ||
32 | using log4net; | ||
33 | using OpenMetaverse; | ||
34 | using OpenSim.Framework; | ||
35 | using Npgsql; | ||
36 | |||
37 | namespace 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 | } | ||