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