aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLInventoryData.cs
diff options
context:
space:
mode:
authorSean Dague2008-04-02 15:24:31 +0000
committerSean Dague2008-04-02 15:24:31 +0000
commitc52c68f314c67c76c7181a6d0828f476290fbd66 (patch)
tree66ab347502892902a096fa985f31b25738eb1381 /OpenSim/Data/MySQL/MySQLInventoryData.cs
parentreorganizing namespaces to put all the Data stuff into it's own namespace (diff)
downloadopensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.zip
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.gz
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.bz2
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.xz
whole lot more moving
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLInventoryData.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLInventoryData.cs648
1 files changed, 648 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs
new file mode 100644
index 0000000..4165d8f
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs
@@ -0,0 +1,648 @@
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 OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using libsecondlife;
31using MySql.Data.MySqlClient;
32using OpenSim.Framework.Console;
33
34namespace OpenSim.Framework.Data.MySQL
35{
36 /// <summary>
37 /// A MySQL interface for the inventory server
38 /// </summary>
39 public class MySQLInventoryData : IInventoryData
40 {
41 private static readonly log4net.ILog m_log
42 = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 private MySQLManager database;
48
49 /// <summary>
50 /// Loads and initialises this database plugin
51 /// </summary>
52 public void Initialise()
53 {
54 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
55 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
56 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
57 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
58 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
59 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
60 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
61
62 database =
63 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
64 settingPort);
65 TestTables(database.Connection);
66 }
67
68 #region Test and initialization code
69
70 private void UpgradeFoldersTable(string oldVersion)
71 {
72 // null as the version, indicates that the table didn't exist
73 if (oldVersion == null)
74 {
75 database.ExecuteResourceSql("CreateFoldersTable.sql");
76 return;
77 }
78
79 // if the table is already at the current version, then we can exit immediately
80// if (oldVersion == "Rev. 2")
81// return;
82
83// database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql");
84 }
85
86 private void UpgradeItemsTable(string oldVersion)
87 {
88 // null as the version, indicates that the table didn't exist
89 if (oldVersion == null)
90 {
91 database.ExecuteResourceSql("CreateItemsTable.sql");
92 return;
93 }
94
95 // if the table is already at the current version, then we can exit immediately
96// if (oldVersion == "Rev. 2")
97// return;
98
99// database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql");
100 }
101
102 private void TestTables(MySqlConnection conn)
103 {
104 Dictionary<string, string> tableList = new Dictionary<string, string>();
105
106 tableList["inventoryfolders"] = null;
107 tableList["inventoryitems"] = null;
108
109 database.GetTableVersion(tableList);
110 m_log.Info("[MYSQL]: Inventory Folder Version: " + tableList["inventoryfolders"]);
111 m_log.Info("[MYSQL]: Inventory Items Version: " + tableList["inventoryitems"]);
112
113 UpgradeFoldersTable(tableList["inventoryfolders"]);
114 UpgradeItemsTable(tableList["inventoryitems"]);
115 }
116
117 #endregion
118
119 /// <summary>
120 /// The name of this DB provider
121 /// </summary>
122 /// <returns>Name of DB provider</returns>
123 public string getName()
124 {
125 return "MySQL Inventory Data Interface";
126 }
127
128 /// <summary>
129 /// Closes this DB provider
130 /// </summary>
131 public void Close()
132 {
133 // Do nothing.
134 }
135
136 /// <summary>
137 /// Returns the version of this DB provider
138 /// </summary>
139 /// <returns>A string containing the DB provider</returns>
140 public string getVersion()
141 {
142 return database.getVersion();
143 }
144
145 /// <summary>
146 /// Returns a list of items in a specified folder
147 /// </summary>
148 /// <param name="folderID">The folder to search</param>
149 /// <returns>A list containing inventory items</returns>
150 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
151 {
152 try
153 {
154 lock (database)
155 {
156 List<InventoryItemBase> items = new List<InventoryItemBase>();
157
158 MySqlCommand result =
159 new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid",
160 database.Connection);
161 result.Parameters.AddWithValue("?uuid", folderID.ToString());
162 MySqlDataReader reader = result.ExecuteReader();
163
164 while (reader.Read())
165 items.Add(readInventoryItem(reader));
166
167 reader.Close();
168 result.Dispose();
169
170 return items;
171 }
172 }
173 catch (Exception e)
174 {
175 database.Reconnect();
176 m_log.Error(e.ToString());
177 return null;
178 }
179 }
180
181 /// <summary>
182 /// Returns a list of the root folders within a users inventory
183 /// </summary>
184 /// <param name="user">The user whos inventory is to be searched</param>
185 /// <returns>A list of folder objects</returns>
186 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
187 {
188 try
189 {
190 lock (database)
191 {
192 MySqlCommand result =
193 new MySqlCommand(
194 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
195 database.Connection);
196 result.Parameters.AddWithValue("?uuid", user.ToString());
197 result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString());
198 MySqlDataReader reader = result.ExecuteReader();
199
200 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
201 while (reader.Read())
202 items.Add(readInventoryFolder(reader));
203
204
205 reader.Close();
206 result.Dispose();
207
208 return items;
209 }
210 }
211 catch (Exception e)
212 {
213 database.Reconnect();
214 m_log.Error(e.ToString());
215 return null;
216 }
217 }
218
219 // see InventoryItemBase.getUserRootFolder
220 public InventoryFolderBase getUserRootFolder(LLUUID user)
221 {
222 try
223 {
224 lock (database)
225 {
226 MySqlCommand result =
227 new MySqlCommand(
228 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
229 database.Connection);
230 result.Parameters.AddWithValue("?uuid", user.ToString());
231 result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString());
232
233 MySqlDataReader reader = result.ExecuteReader();
234
235 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
236 while (reader.Read())
237 items.Add(readInventoryFolder(reader));
238
239 InventoryFolderBase rootFolder = null;
240
241 // There should only ever be one root folder for a user. However, if there's more
242 // than one we'll simply use the first one rather than failing. It would be even
243 // nicer to print some message to this effect, but this feels like it's too low a
244 // to put such a message out, and it's too minor right now to spare the time to
245 // suitably refactor.
246 if (items.Count > 0)
247 {
248 rootFolder = items[0];
249 }
250
251 reader.Close();
252 result.Dispose();
253
254 return rootFolder;
255 }
256 }
257 catch (Exception e)
258 {
259 database.Reconnect();
260 m_log.Error(e.ToString());
261 return null;
262 }
263 }
264
265 /// <summary>
266 /// Return a list of folders in a users inventory contained within the specified folder.
267 /// This method is only used in tests - in normal operation the user always have one,
268 /// and only one, root folder.
269 /// </summary>
270 /// <param name="parentID">The folder to search</param>
271 /// <returns>A list of inventory folders</returns>
272 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
273 {
274 try
275 {
276 lock (database)
277 {
278 MySqlCommand result =
279 new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid",
280 database.Connection);
281 result.Parameters.AddWithValue("?uuid", parentID.ToString());
282 MySqlDataReader reader = result.ExecuteReader();
283
284 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
285
286 while (reader.Read())
287 items.Add(readInventoryFolder(reader));
288
289 reader.Close();
290 result.Dispose();
291
292 return items;
293 }
294 }
295 catch (Exception e)
296 {
297 database.Reconnect();
298 m_log.Error(e.ToString());
299 return null;
300 }
301 }
302
303 /// <summary>
304 /// Reads a one item from an SQL result
305 /// </summary>
306 /// <param name="reader">The SQL Result</param>
307 /// <returns>the item read</returns>
308 private InventoryItemBase readInventoryItem(MySqlDataReader reader)
309 {
310 try
311 {
312 InventoryItemBase item = new InventoryItemBase();
313
314 item.inventoryID = new LLUUID((string) reader["inventoryID"]);
315 item.assetID = new LLUUID((string) reader["assetID"]);
316 item.assetType = (int) reader["assetType"];
317 item.parentFolderID = new LLUUID((string) reader["parentFolderID"]);
318 item.avatarID = new LLUUID((string) reader["avatarID"]);
319 item.inventoryName = (string) reader["inventoryName"];
320 item.inventoryDescription = (string) reader["inventoryDescription"];
321 item.inventoryNextPermissions = (uint) reader["inventoryNextPermissions"];
322 item.inventoryCurrentPermissions = (uint) reader["inventoryCurrentPermissions"];
323 item.invType = (int) reader["invType"];
324 item.creatorsID = new LLUUID((string) reader["creatorID"]);
325 item.inventoryBasePermissions = (uint) reader["inventoryBasePermissions"];
326 item.inventoryEveryOnePermissions = (uint) reader["inventoryEveryOnePermissions"];
327 return item;
328 }
329 catch (MySqlException e)
330 {
331 m_log.Error(e.ToString());
332 }
333
334 return null;
335 }
336
337 /// <summary>
338 /// Returns a specified inventory item
339 /// </summary>
340 /// <param name="item">The item to return</param>
341 /// <returns>An inventory item</returns>
342 public InventoryItemBase getInventoryItem(LLUUID itemID)
343 {
344 try
345 {
346 lock (database)
347 {
348 Dictionary<string, string> param = new Dictionary<string, string>();
349
350 MySqlCommand result =
351 new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection);
352 result.Parameters.AddWithValue("?uuid", itemID.ToString());
353 MySqlDataReader reader = result.ExecuteReader();
354
355 InventoryItemBase item = null;
356 if (reader.Read())
357 item = readInventoryItem(reader);
358
359 reader.Close();
360 result.Dispose();
361
362 return item;
363 }
364 }
365 catch (Exception e)
366 {
367 database.Reconnect();
368 m_log.Error(e.ToString());
369 }
370 return null;
371 }
372
373 /// <summary>
374 /// Reads a list of inventory folders returned by a query.
375 /// </summary>
376 /// <param name="reader">A MySQL Data Reader</param>
377 /// <returns>A List containing inventory folders</returns>
378 protected InventoryFolderBase readInventoryFolder(MySqlDataReader reader)
379 {
380 try
381 {
382 InventoryFolderBase folder = new InventoryFolderBase();
383 folder.agentID = new LLUUID((string) reader["agentID"]);
384 folder.parentID = new LLUUID((string) reader["parentFolderID"]);
385 folder.folderID = new LLUUID((string) reader["folderID"]);
386 folder.name = (string) reader["folderName"];
387 folder.type = (short) reader["type"];
388 folder.version = (ushort) ((int) reader["version"]);
389 return folder;
390 }
391 catch (Exception e)
392 {
393 m_log.Error(e.ToString());
394 }
395
396 return null;
397 }
398
399
400 /// <summary>
401 /// Returns a specified inventory folder
402 /// </summary>
403 /// <param name="folder">The folder to return</param>
404 /// <returns>A folder class</returns>
405 public InventoryFolderBase getInventoryFolder(LLUUID folderID)
406 {
407 try
408 {
409 lock (database)
410 {
411 MySqlCommand result =
412 new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection);
413 result.Parameters.AddWithValue("?uuid", folderID.ToString());
414 MySqlDataReader reader = result.ExecuteReader();
415
416 reader.Read();
417 InventoryFolderBase folder = readInventoryFolder(reader);
418 reader.Close();
419 result.Dispose();
420
421 return folder;
422 }
423 }
424 catch (Exception e)
425 {
426 database.Reconnect();
427 m_log.Error(e.ToString());
428 return null;
429 }
430 }
431
432 /// <summary>
433 /// Adds a specified item to the database
434 /// </summary>
435 /// <param name="item">The inventory item</param>
436 public void addInventoryItem(InventoryItemBase item)
437 {
438 string sql =
439 "REPLACE INTO inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType, creatorID, inventoryBasePermissions, inventoryEveryOnePermissions) VALUES ";
440 sql +=
441 "(?inventoryID, ?assetID, ?assetType, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription, ?inventoryNextPermissions, ?inventoryCurrentPermissions, ?invType, ?creatorID, ?inventoryBasePermissions, ?inventoryEveryOnePermissions)";
442
443 try
444 {
445 MySqlCommand result = new MySqlCommand(sql, database.Connection);
446 result.Parameters.AddWithValue("?inventoryID", item.inventoryID.ToString());
447 result.Parameters.AddWithValue("?assetID", item.assetID.ToString());
448 result.Parameters.AddWithValue("?assetType", item.assetType.ToString());
449 result.Parameters.AddWithValue("?parentFolderID", item.parentFolderID.ToString());
450 result.Parameters.AddWithValue("?avatarID", item.avatarID.ToString());
451 result.Parameters.AddWithValue("?inventoryName", item.inventoryName);
452 result.Parameters.AddWithValue("?inventoryDescription", item.inventoryDescription);
453 result.Parameters.AddWithValue("?inventoryNextPermissions", item.inventoryNextPermissions.ToString());
454 result.Parameters.AddWithValue("?inventoryCurrentPermissions",
455 item.inventoryCurrentPermissions.ToString());
456 result.Parameters.AddWithValue("?invType", item.invType);
457 result.Parameters.AddWithValue("?creatorID", item.creatorsID.ToString());
458 result.Parameters.AddWithValue("?inventoryBasePermissions", item.inventoryBasePermissions);
459 result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.inventoryEveryOnePermissions);
460 result.ExecuteNonQuery();
461 result.Dispose();
462 }
463 catch (MySqlException e)
464 {
465 m_log.Error(e.ToString());
466 }
467 }
468
469 /// <summary>
470 /// Updates the specified inventory item
471 /// </summary>
472 /// <param name="item">Inventory item to update</param>
473 public void updateInventoryItem(InventoryItemBase item)
474 {
475 addInventoryItem(item);
476 }
477
478 /// <summary>
479 ///
480 /// </summary>
481 /// <param name="item"></param>
482 public void deleteInventoryItem(LLUUID itemID)
483 {
484 try
485 {
486 MySqlCommand cmd =
487 new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection);
488 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
489 cmd.ExecuteNonQuery();
490 }
491 catch (MySqlException e)
492 {
493 database.Reconnect();
494 m_log.Error(e.ToString());
495 }
496 }
497
498 /// <summary>
499 /// Creates a new inventory folder
500 /// </summary>
501 /// <param name="folder">Folder to create</param>
502 public void addInventoryFolder(InventoryFolderBase folder)
503 {
504 string sql =
505 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
506 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)";
507
508 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
509 cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString());
510 cmd.Parameters.AddWithValue("?agentID", folder.agentID.ToString());
511 cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString());
512 cmd.Parameters.AddWithValue("?folderName", folder.name);
513 cmd.Parameters.AddWithValue("?type", (short) folder.type);
514 cmd.Parameters.AddWithValue("?version", folder.version);
515
516 try
517 {
518 lock (database)
519 {
520 cmd.ExecuteNonQuery();
521 }
522 }
523 catch (Exception e)
524 {
525 m_log.Error(e.ToString());
526 }
527 }
528
529 /// <summary>
530 /// Updates an inventory folder
531 /// </summary>
532 /// <param name="folder">Folder to update</param>
533 public void updateInventoryFolder(InventoryFolderBase folder)
534 {
535 addInventoryFolder(folder);
536 }
537
538 /// Creates a new inventory folder
539 /// </summary>
540 /// <param name="folder">Folder to create</param>
541 public void moveInventoryFolder(InventoryFolderBase folder)
542 {
543 string sql =
544 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
545
546 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
547 cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString());
548 cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString());
549
550 try
551 {
552 lock (database)
553 {
554 cmd.ExecuteNonQuery();
555 }
556 }
557 catch (Exception e)
558 {
559 m_log.Error(e.ToString());
560 }
561 }
562
563 /// <summary>
564 /// Append a list of all the child folders of a parent folder
565 /// </summary>
566 /// <param name="folders">list where folders will be appended</param>
567 /// <param name="parentID">ID of parent</param>
568 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
569 {
570 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
571
572 foreach (InventoryFolderBase f in subfolderList)
573 folders.Add(f);
574 }
575
576 // See IInventoryData
577 public List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
578 {
579 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
580 getInventoryFolders(ref folders, parentID);
581
582 for (int i = 0; i < folders.Count; i++)
583 getInventoryFolders(ref folders, folders[i].folderID);
584
585 return folders;
586 }
587
588 protected void deleteOneFolder(LLUUID folderID)
589 {
590 try
591 {
592 MySqlCommand cmd =
593 new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection);
594 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
595
596 lock (database)
597 {
598 cmd.ExecuteNonQuery();
599 }
600 }
601 catch (MySqlException e)
602 {
603 database.Reconnect();
604 m_log.Error(e.ToString());
605 }
606 }
607
608 protected void deleteItemsInFolder(LLUUID folderID)
609 {
610 try
611 {
612 MySqlCommand cmd =
613 new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection);
614 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
615
616 lock (database)
617 {
618 cmd.ExecuteNonQuery();
619 }
620 }
621 catch (MySqlException e)
622 {
623 database.Reconnect();
624 m_log.Error(e.ToString());
625 }
626 }
627
628 /// <summary>
629 /// Delete an inventory folder
630 /// </summary>
631 /// <param name="folderId">Id of folder to delete</param>
632 public void deleteInventoryFolder(LLUUID folderID)
633 {
634 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
635
636 //Delete all sub-folders
637 foreach (InventoryFolderBase f in subFolders)
638 {
639 deleteOneFolder(f.folderID);
640 deleteItemsInFolder(f.folderID);
641 }
642
643 //Delete the actual row
644 deleteOneFolder(folderID);
645 deleteItemsInFolder(folderID);
646 }
647 }
648}