opensim – Blame information for rev 1
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | eva | 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 | } |