aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data')
-rw-r--r--OpenSim/Data/IProfilesData.cs56
-rw-r--r--OpenSim/Data/MySQL/MySQLUserProfilesData.cs1096
-rw-r--r--OpenSim/Data/MySQL/Resources/UserProfiles.migrations83
3 files changed, 1235 insertions, 0 deletions
diff --git a/OpenSim/Data/IProfilesData.cs b/OpenSim/Data/IProfilesData.cs
new file mode 100644
index 0000000..0de7f68
--- /dev/null
+++ b/OpenSim/Data/IProfilesData.cs
@@ -0,0 +1,56 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using OpenMetaverse;
30using OpenMetaverse.StructuredData;
31using OpenSim.Framework;
32
33namespace OpenSim.Data
34{
35
36 public interface IProfilesData
37 {
38 OSDArray GetClassifiedRecords(UUID creatorId);
39 bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result);
40 bool DeleteClassifiedRecord(UUID recordId);
41 OSDArray GetAvatarPicks(UUID avatarId);
42 UserProfilePick GetPickInfo(UUID avatarId, UUID pickId);
43 bool UpdatePicksRecord(UserProfilePick pick);
44 bool DeletePicksRecord(UUID pickId);
45 bool GetAvatarNotes(ref UserProfileNotes note);
46 bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result);
47 bool GetAvatarProperties(ref UserProfileProperties props, ref string result);
48 bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result);
49 bool UpdateAvatarInterests(UserProfileProperties up, ref string result);
50 bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result);
51 bool GetUserAppData(ref UserAppData props, ref string result);
52 bool SetUserAppData(UserAppData props, ref string result);
53 OSDArray GetUserImageAssets(UUID avatarId);
54 }
55}
56
diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
new file mode 100644
index 0000000..4c6c8e3
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
@@ -0,0 +1,1096 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using System.Reflection;
31using OpenSim.Data;
32using OpenSim.Framework;
33using MySql.Data.MySqlClient;
34using OpenMetaverse;
35using OpenMetaverse.StructuredData;
36using log4net;
37
38namespace OpenSim.Data.MySQL
39{
40 public class UserProfilesData: IProfilesData
41 {
42 static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 #region Properites
45 string ConnectionString
46 {
47 get; set;
48 }
49
50 protected object Lock
51 {
52 get; set;
53 }
54
55 protected virtual Assembly Assembly
56 {
57 get { return GetType().Assembly; }
58 }
59
60 #endregion Properties
61
62 #region class Member Functions
63 public UserProfilesData(string connectionString)
64 {
65 ConnectionString = connectionString;
66 Init();
67 }
68
69 void Init()
70 {
71 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
72 {
73 dbcon.Open();
74
75 Migration m = new Migration(dbcon, Assembly, "UserProfiles");
76 m.Update();
77 }
78 }
79 #endregion Member Functions
80
81 #region Classifieds Queries
82 /// <summary>
83 /// Gets the classified records.
84 /// </summary>
85 /// <returns>
86 /// Array of classified records
87 /// </returns>
88 /// <param name='creatorId'>
89 /// Creator identifier.
90 /// </param>
91 public OSDArray GetClassifiedRecords(UUID creatorId)
92 {
93 OSDArray data = new OSDArray();
94
95 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
96 {
97 string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = ?Id";
98 dbcon.Open();
99 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
100 {
101 cmd.Parameters.AddWithValue("?Id", creatorId);
102 using( MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
103 {
104 if(reader.HasRows)
105 {
106 while (reader.Read())
107 {
108 OSDMap n = new OSDMap();
109 UUID Id = UUID.Zero;
110
111 string Name = null;
112 try
113 {
114 UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id);
115 Name = Convert.ToString(reader["name"]);
116 }
117 catch (Exception e)
118 {
119 m_log.DebugFormat("[PROFILES_DATA]" +
120 ": UserAccount exception {0}", e.Message);
121 }
122 n.Add("classifieduuid", OSD.FromUUID(Id));
123 n.Add("name", OSD.FromString(Name));
124 data.Add(n);
125 }
126 }
127 }
128 }
129 }
130 return data;
131 }
132
133 public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result)
134 {
135 string query = string.Empty;
136
137
138 query += "INSERT INTO classifieds (";
139 query += "`classifieduuid`,";
140 query += "`creatoruuid`,";
141 query += "`creationdate`,";
142 query += "`expirationdate`,";
143 query += "`category`,";
144 query += "`name`,";
145 query += "`description`,";
146 query += "`parceluuid`,";
147 query += "`parentestate`,";
148 query += "`snapshotuuid`,";
149 query += "`simname`,";
150 query += "`posglobal`,";
151 query += "`parcelname`,";
152 query += "`classifiedflags`,";
153 query += "`priceforlisting`) ";
154 query += "VALUES (";
155 query += "?ClassifiedId,";
156 query += "?CreatorId,";
157 query += "?CreatedDate,";
158 query += "?ExpirationDate,";
159 query += "?Category,";
160 query += "?Name,";
161 query += "?Description,";
162 query += "?ParcelId,";
163 query += "?ParentEstate,";
164 query += "?SnapshotId,";
165 query += "?SimName,";
166 query += "?GlobalPos,";
167 query += "?ParcelName,";
168 query += "?Flags,";
169 query += "?ListingPrice ) ";
170 query += "ON DUPLICATE KEY UPDATE ";
171 query += "category=?Category, ";
172 query += "expirationdate=?ExpirationDate, ";
173 query += "name=?Name, ";
174 query += "description=?Description, ";
175 query += "parentestate=?ParentEstate, ";
176 query += "posglobal=?GlobalPos, ";
177 query += "parcelname=?ParcelName, ";
178 query += "classifiedflags=?Flags, ";
179 query += "priceforlisting=?ListingPrice, ";
180 query += "snapshotuuid=?SnapshotId";
181
182 if(string.IsNullOrEmpty(ad.ParcelName))
183 ad.ParcelName = "Unknown";
184 if(ad.ParcelId == null)
185 ad.ParcelId = UUID.Zero;
186 if(string.IsNullOrEmpty(ad.Description))
187 ad.Description = "No Description";
188
189 DateTime epoch = new DateTime(1970, 1, 1);
190 DateTime now = DateTime.Now;
191 TimeSpan epochnow = now - epoch;
192 TimeSpan duration;
193 DateTime expiration;
194 TimeSpan epochexp;
195
196 if(ad.Flags == 2)
197 {
198 duration = new TimeSpan(7,0,0,0);
199 expiration = now.Add(duration);
200 epochexp = expiration - epoch;
201 }
202 else
203 {
204 duration = new TimeSpan(365,0,0,0);
205 expiration = now.Add(duration);
206 epochexp = expiration - epoch;
207 }
208 ad.CreationDate = (int)epochnow.TotalSeconds;
209 ad.ExpirationDate = (int)epochexp.TotalSeconds;
210
211 try
212 {
213 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
214 {
215 dbcon.Open();
216 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
217 {
218 cmd.Parameters.AddWithValue("?ClassifiedId", ad.ClassifiedId.ToString());
219 cmd.Parameters.AddWithValue("?CreatorId", ad.CreatorId.ToString());
220 cmd.Parameters.AddWithValue("?CreatedDate", ad.CreationDate.ToString());
221 cmd.Parameters.AddWithValue("?ExpirationDate", ad.ExpirationDate.ToString());
222 cmd.Parameters.AddWithValue("?Category", ad.Category.ToString());
223 cmd.Parameters.AddWithValue("?Name", ad.Name.ToString());
224 cmd.Parameters.AddWithValue("?Description", ad.Description.ToString());
225 cmd.Parameters.AddWithValue("?ParcelId", ad.ParcelId.ToString());
226 cmd.Parameters.AddWithValue("?ParentEstate", ad.ParentEstate.ToString());
227 cmd.Parameters.AddWithValue("?SnapshotId", ad.SnapshotId.ToString ());
228 cmd.Parameters.AddWithValue("?SimName", ad.SimName.ToString());
229 cmd.Parameters.AddWithValue("?GlobalPos", ad.GlobalPos.ToString());
230 cmd.Parameters.AddWithValue("?ParcelName", ad.ParcelName.ToString());
231 cmd.Parameters.AddWithValue("?Flags", ad.Flags.ToString());
232 cmd.Parameters.AddWithValue("?ListingPrice", ad.Price.ToString ());
233
234 cmd.ExecuteNonQuery();
235 }
236 }
237 }
238 catch (Exception e)
239 {
240 m_log.DebugFormat("[PROFILES_DATA]" +
241 ": ClassifiedesUpdate exception {0}", e.Message);
242 result = e.Message;
243 return false;
244 }
245 return true;
246 }
247
248 public bool DeleteClassifiedRecord(UUID recordId)
249 {
250 string query = string.Empty;
251
252 query += "DELETE FROM classifieds WHERE ";
253 query += "classifieduuid = ?ClasifiedId";
254
255 try
256 {
257 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
258 {
259 dbcon.Open();
260
261 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
262 {
263 cmd.Parameters.AddWithValue("?ClassifiedId", recordId.ToString());
264
265 lock(Lock)
266 {
267 cmd.ExecuteNonQuery();
268 }
269 }
270 }
271 }
272 catch (Exception e)
273 {
274 m_log.DebugFormat("[PROFILES_DATA]" +
275 ": DeleteClassifiedRecord exception {0}", e.Message);
276 return false;
277 }
278 return true;
279 }
280
281 public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result)
282 {
283 string query = string.Empty;
284
285 query += "SELECT * FROM classifieds WHERE ";
286 query += "classifieduuid = ?AdId";
287
288 try
289 {
290 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
291 {
292 dbcon.Open();
293 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
294 {
295 cmd.Parameters.AddWithValue("?AdId", ad.ClassifiedId.ToString());
296
297 using (MySqlDataReader reader = cmd.ExecuteReader())
298 {
299 if(reader.Read ())
300 {
301 ad.CreatorId = new UUID(reader.GetGuid("creatoruuid"));
302 ad.ParcelId = new UUID(reader.GetGuid("parceluuid"));
303 ad.SnapshotId = new UUID(reader.GetGuid("snapshotuuid"));
304 ad.CreationDate = Convert.ToInt32(reader["creationdate"]);
305 ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]);
306 ad.ParentEstate = Convert.ToInt32(reader["parentestate"]);
307 ad.Flags = (byte)reader.GetUInt32("classifiedflags");
308 ad.Category = reader.GetInt32("category");
309 ad.Price = reader.GetInt16("priceforlisting");
310 ad.Name = reader.GetString("name");
311 ad.Description = reader.GetString("description");
312 ad.SimName = reader.GetString("simname");
313 ad.GlobalPos = reader.GetString("posglobal");
314 ad.ParcelName = reader.GetString("parcelname");
315
316 }
317 }
318 }
319 dbcon.Close();
320 }
321 }
322 catch (Exception e)
323 {
324 m_log.DebugFormat("[PROFILES_DATA]" +
325 ": GetPickInfo exception {0}", e.Message);
326 }
327 return true;
328 }
329 #endregion Classifieds Queries
330
331 #region Picks Queries
332 public OSDArray GetAvatarPicks(UUID avatarId)
333 {
334 string query = string.Empty;
335
336 query += "SELECT `pickuuid`,`name` FROM userpicks WHERE ";
337 query += "creatoruuid = ?Id";
338 OSDArray data = new OSDArray();
339
340 try
341 {
342 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
343 {
344 dbcon.Open();
345 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
346 {
347 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
348
349 using (MySqlDataReader reader = cmd.ExecuteReader())
350 {
351 if(reader.HasRows)
352 {
353 while (reader.Read())
354 {
355 OSDMap record = new OSDMap();
356
357 record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"]));
358 record.Add("name",OSD.FromString((string)reader["name"]));
359 data.Add(record);
360 }
361 }
362 }
363 }
364 }
365 }
366 catch (Exception e)
367 {
368 m_log.DebugFormat("[PROFILES_DATA]" +
369 ": GetAvatarPicks exception {0}", e.Message);
370 }
371 return data;
372 }
373
374 public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId)
375 {
376 string query = string.Empty;
377 UserProfilePick pick = new UserProfilePick();
378
379 query += "SELECT * FROM userpicks WHERE ";
380 query += "creatoruuid = ?CreatorId AND ";
381 query += "pickuuid = ?PickId";
382
383 try
384 {
385 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
386 {
387 dbcon.Open();
388 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
389 {
390 cmd.Parameters.AddWithValue("?CreatorId", avatarId.ToString());
391 cmd.Parameters.AddWithValue("?PickId", pickId.ToString());
392
393 using (MySqlDataReader reader = cmd.ExecuteReader())
394 {
395 if(reader.HasRows)
396 {
397 reader.Read();
398
399 string description = (string)reader["description"];
400
401 if (string.IsNullOrEmpty(description))
402 description = "No description given.";
403
404 UUID.TryParse((string)reader["pickuuid"], out pick.PickId);
405 UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId);
406 UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId);
407 UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId);
408 pick.GlobalPos = (string)reader["posglobal"];
409 bool.TryParse((string)reader["toppick"], out pick.TopPick);
410 bool.TryParse((string)reader["enabled"], out pick.Enabled);
411 pick.Name = (string)reader["name"];
412 pick.Desc = description;
413 pick.User = (string)reader["user"];
414 pick.OriginalName = (string)reader["originalname"];
415 pick.SimName = (string)reader["simname"];
416 pick.SortOrder = (int)reader["sortorder"];
417 }
418 }
419 }
420 dbcon.Close();
421 }
422 }
423 catch (Exception e)
424 {
425 m_log.DebugFormat("[PROFILES_DATA]" +
426 ": GetPickInfo exception {0}", e.Message);
427 }
428 return pick;
429 }
430
431 public bool UpdatePicksRecord(UserProfilePick pick)
432 {
433 string query = string.Empty;
434
435 query += "INSERT INTO userpicks VALUES (";
436 query += "?PickId,";
437 query += "?CreatorId,";
438 query += "?TopPick,";
439 query += "?ParcelId,";
440 query += "?Name,";
441 query += "?Desc,";
442 query += "?SnapshotId,";
443 query += "?User,";
444 query += "?Original,";
445 query += "?SimName,";
446 query += "?GlobalPos,";
447 query += "?SortOrder,";
448 query += "?Enabled) ";
449 query += "ON DUPLICATE KEY UPDATE ";
450 query += "parceluuid=?ParcelId,";
451 query += "name=?Name,";
452 query += "description=?Desc,";
453 query += "snapshotuuid=?SnapshotId,";
454 query += "pickuuid=?PickId,";
455 query += "posglobal=?GlobalPos";
456
457 try
458 {
459 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
460 {
461 dbcon.Open();
462 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
463 {
464 cmd.Parameters.AddWithValue("?PickId", pick.PickId.ToString());
465 cmd.Parameters.AddWithValue("?CreatorId", pick.CreatorId.ToString());
466 cmd.Parameters.AddWithValue("?TopPick", pick.TopPick.ToString());
467 cmd.Parameters.AddWithValue("?ParcelId", pick.ParcelId.ToString());
468 cmd.Parameters.AddWithValue("?Name", pick.Name.ToString());
469 cmd.Parameters.AddWithValue("?Desc", pick.Desc.ToString());
470 cmd.Parameters.AddWithValue("?SnapshotId", pick.SnapshotId.ToString());
471 cmd.Parameters.AddWithValue("?User", pick.User.ToString());
472 cmd.Parameters.AddWithValue("?Original", pick.OriginalName.ToString());
473 cmd.Parameters.AddWithValue("?SimName",pick.SimName.ToString());
474 cmd.Parameters.AddWithValue("?GlobalPos", pick.GlobalPos);
475 cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ());
476 cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString());
477
478 cmd.ExecuteNonQuery();
479 }
480 }
481 }
482 catch (Exception e)
483 {
484 m_log.DebugFormat("[PROFILES_DATA]" +
485 ": UpdateAvatarNotes exception {0}", e.Message);
486 return false;
487 }
488 return true;
489 }
490
491 public bool DeletePicksRecord(UUID pickId)
492 {
493 string query = string.Empty;
494
495 query += "DELETE FROM userpicks WHERE ";
496 query += "pickuuid = ?PickId";
497
498 try
499 {
500 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
501 {
502 dbcon.Open();
503
504 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
505 {
506 cmd.Parameters.AddWithValue("?PickId", pickId.ToString());
507
508 cmd.ExecuteNonQuery();
509 }
510 }
511 }
512 catch (Exception e)
513 {
514 m_log.DebugFormat("[PROFILES_DATA]" +
515 ": DeleteUserPickRecord exception {0}", e.Message);
516 return false;
517 }
518 return true;
519 }
520 #endregion Picks Queries
521
522 #region Avatar Notes Queries
523 public bool GetAvatarNotes(ref UserProfileNotes notes)
524 { // WIP
525 string query = string.Empty;
526
527 query += "SELECT `notes` FROM usernotes WHERE ";
528 query += "useruuid = ?Id AND ";
529 query += "targetuuid = ?TargetId";
530 OSDArray data = new OSDArray();
531
532 try
533 {
534 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
535 {
536 dbcon.Open();
537 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
538 {
539 cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString());
540 cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString());
541
542 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
543 {
544 if(reader.HasRows)
545 {
546 reader.Read();
547 notes.Notes = OSD.FromString((string)reader["notes"]);
548 }
549 }
550 }
551 }
552 }
553 catch (Exception e)
554 {
555 m_log.DebugFormat("[PROFILES_DATA]" +
556 ": GetAvatarNotes exception {0}", e.Message);
557 }
558 return true;
559 }
560
561 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
562 {
563 string query = string.Empty;
564 bool remove;
565
566 if(string.IsNullOrEmpty(note.Notes))
567 {
568 remove = true;
569 query += "DELETE FROM usernotes WHERE ";
570 query += "useruuid=?UserId AND ";
571 query += "targetuuid=?TargetId";
572 }
573 else
574 {
575 remove = false;
576 query += "INSERT INTO usernotes VALUES ( ";
577 query += "?UserId,";
578 query += "?TargetId,";
579 query += "?Notes )";
580 query += "ON DUPLICATE KEY ";
581 query += "UPDATE ";
582 query += "notes=?Notes";
583 }
584
585 try
586 {
587 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
588 {
589 dbcon.Open();
590 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
591 {
592 if(!remove)
593 cmd.Parameters.AddWithValue("?Notes", note.Notes);
594 cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ());
595 cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString());
596
597 cmd.ExecuteNonQuery();
598 }
599 }
600 }
601 catch (Exception e)
602 {
603 m_log.DebugFormat("[PROFILES_DATA]" +
604 ": UpdateAvatarNotes exception {0}", e.Message);
605 return false;
606 }
607 return true;
608
609 }
610 #endregion Avatar Notes Queries
611
612 #region Avatar Properties
613 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
614 {
615 string query = string.Empty;
616
617 query += "SELECT * FROM userprofile WHERE ";
618 query += "useruuid = ?Id";
619
620 try
621 {
622 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
623 {
624 dbcon.Open();
625 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
626 {
627 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
628
629 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
630 {
631 if(reader.HasRows)
632 {
633 m_log.DebugFormat("[PROFILES_DATA]" +
634 ": Getting data for {0}.", props.UserId);
635 reader.Read();
636 props.WebUrl = (string)reader["profileURL"];
637 UUID.TryParse((string)reader["profileImage"], out props.ImageId);
638 props.AboutText = (string)reader["profileAboutText"];
639 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId);
640 props.FirstLifeText = (string)reader["profileFirstText"];
641 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId);
642 props.WantToMask = (int)reader["profileWantToMask"];
643 props.WantToText = (string)reader["profileWantToText"];
644 props.SkillsMask = (int)reader["profileSkillsMask"];
645 props.SkillsText = (string)reader["profileSkillsText"];
646 props.Language = (string)reader["profileLanguages"];
647 }
648 else
649 {
650 m_log.DebugFormat("[PROFILES_DATA]" +
651 ": No data for {0}", props.UserId);
652
653 props.WebUrl = string.Empty;
654 props.ImageId = UUID.Zero;
655 props.AboutText = string.Empty;
656 props.FirstLifeImageId = UUID.Zero;
657 props.FirstLifeText = string.Empty;
658 props.PartnerId = UUID.Zero;
659 props.WantToMask = 0;
660 props.WantToText = string.Empty;
661 props.SkillsMask = 0;
662 props.SkillsText = string.Empty;
663 props.Language = string.Empty;
664 props.PublishProfile = false;
665 props.PublishMature = false;
666
667 query = "INSERT INTO userprofile (";
668 query += "useruuid, ";
669 query += "profilePartner, ";
670 query += "profileAllowPublish, ";
671 query += "profileMaturePublish, ";
672 query += "profileURL, ";
673 query += "profileWantToMask, ";
674 query += "profileWantToText, ";
675 query += "profileSkillsMask, ";
676 query += "profileSkillsText, ";
677 query += "profileLanguages, ";
678 query += "profileImage, ";
679 query += "profileAboutText, ";
680 query += "profileFirstImage, ";
681 query += "profileFirstText) VALUES (";
682 query += "?userId, ";
683 query += "?profilePartner, ";
684 query += "?profileAllowPublish, ";
685 query += "?profileMaturePublish, ";
686 query += "?profileURL, ";
687 query += "?profileWantToMask, ";
688 query += "?profileWantToText, ";
689 query += "?profileSkillsMask, ";
690 query += "?profileSkillsText, ";
691 query += "?profileLanguages, ";
692 query += "?profileImage, ";
693 query += "?profileAboutText, ";
694 query += "?profileFirstImage, ";
695 query += "?profileFirstText)";
696
697 dbcon.Close();
698 dbcon.Open();
699
700 using (MySqlCommand put = new MySqlCommand(query, dbcon))
701 {
702 put.Parameters.AddWithValue("?userId", props.UserId.ToString());
703 put.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
704 put.Parameters.AddWithValue("?profileAllowPublish", props.PublishProfile);
705 put.Parameters.AddWithValue("?profileMaturePublish", props.PublishMature);
706 put.Parameters.AddWithValue("?profileURL", props.WebUrl);
707 put.Parameters.AddWithValue("?profileWantToMask", props.WantToMask);
708 put.Parameters.AddWithValue("?profileWantToText", props.WantToText);
709 put.Parameters.AddWithValue("?profileSkillsMask", props.SkillsMask);
710 put.Parameters.AddWithValue("?profileSkillsText", props.SkillsText);
711 put.Parameters.AddWithValue("?profileLanguages", props.Language);
712 put.Parameters.AddWithValue("?profileImage", props.ImageId.ToString());
713 put.Parameters.AddWithValue("?profileAboutText", props.AboutText);
714 put.Parameters.AddWithValue("?profileFirstImage", props.FirstLifeImageId.ToString());
715 put.Parameters.AddWithValue("?profileFirstText", props.FirstLifeText);
716
717 put.ExecuteNonQuery();
718 }
719 }
720 }
721 }
722 }
723 }
724 catch (Exception e)
725 {
726 m_log.DebugFormat("[PROFILES_DATA]" +
727 ": Requst properties exception {0}", e.Message);
728 result = e.Message;
729 return false;
730 }
731 return true;
732 }
733
734 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
735 {
736 string query = string.Empty;
737
738 query += "UPDATE userprofile SET ";
739 query += "profilePartner=?profilePartner, ";
740 query += "profileURL=?profileURL, ";
741 query += "profileImage=?image, ";
742 query += "profileAboutText=?abouttext,";
743 query += "profileFirstImage=?firstlifeimage,";
744 query += "profileFirstText=?firstlifetext ";
745 query += "WHERE useruuid=?uuid";
746
747 try
748 {
749 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
750 {
751 dbcon.Open();
752 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
753 {
754 cmd.Parameters.AddWithValue("?profileURL", props.WebUrl);
755 cmd.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
756 cmd.Parameters.AddWithValue("?image", props.ImageId.ToString());
757 cmd.Parameters.AddWithValue("?abouttext", props.AboutText);
758 cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString());
759 cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText);
760 cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString());
761
762 cmd.ExecuteNonQuery();
763 }
764 }
765 }
766 catch (Exception e)
767 {
768 m_log.DebugFormat("[PROFILES_DATA]" +
769 ": AgentPropertiesUpdate exception {0}", e.Message);
770
771 return false;
772 }
773 return true;
774 }
775 #endregion Avatar Properties
776
777 #region Avatar Interests
778 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
779 {
780 string query = string.Empty;
781
782 query += "UPDATE userprofile SET ";
783 query += "profileWantToMask=?WantMask, ";
784 query += "profileWantToText=?WantText,";
785 query += "profileSkillsMask=?SkillsMask,";
786 query += "profileSkillsText=?SkillsText, ";
787 query += "profileLanguages=?Languages ";
788 query += "WHERE useruuid=?uuid";
789
790 try
791 {
792 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
793 {
794 dbcon.Open();
795 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
796 {
797 cmd.Parameters.AddWithValue("?WantMask", up.WantToMask);
798 cmd.Parameters.AddWithValue("?WantText", up.WantToText);
799 cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask);
800 cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText);
801 cmd.Parameters.AddWithValue("?Languages", up.Language);
802 cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString());
803
804 cmd.ExecuteNonQuery();
805 }
806 }
807 }
808 catch (Exception e)
809 {
810 m_log.DebugFormat("[PROFILES_DATA]" +
811 ": AgentInterestsUpdate exception {0}", e.Message);
812 result = e.Message;
813 return false;
814 }
815 return true;
816 }
817 #endregion Avatar Interests
818
819 public OSDArray GetUserImageAssets(UUID avatarId)
820 {
821 OSDArray data = new OSDArray();
822 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id";
823
824 // Get classified image assets
825
826
827 try
828 {
829 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
830 {
831 dbcon.Open();
832
833 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon))
834 {
835 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
836
837 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
838 {
839 if(reader.HasRows)
840 {
841 while (reader.Read())
842 {
843 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
844 }
845 }
846 }
847 }
848
849 dbcon.Close();
850 dbcon.Open();
851
852 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
853 {
854 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
855
856 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
857 {
858 if(reader.HasRows)
859 {
860 while (reader.Read())
861 {
862 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
863 }
864 }
865 }
866 }
867
868 dbcon.Close();
869 dbcon.Open();
870
871 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id";
872
873 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
874 {
875 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
876
877 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
878 {
879 if(reader.HasRows)
880 {
881 while (reader.Read())
882 {
883 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
884 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
885 }
886 }
887 }
888 }
889 }
890 }
891 catch (Exception e)
892 {
893 m_log.DebugFormat("[PROFILES_DATA]" +
894 ": GetAvatarNotes exception {0}", e.Message);
895 }
896 return data;
897 }
898
899 #region User Preferences
900 public OSDArray GetUserPreferences(UUID avatarId)
901 {
902 string query = string.Empty;
903
904 query += "SELECT imviaemail,visible,email FROM ";
905 query += "usersettings WHERE ";
906 query += "useruuid = ?Id";
907
908 OSDArray data = new OSDArray();
909
910 try
911 {
912 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
913 {
914 dbcon.Open();
915 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
916 {
917 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
918
919 using (MySqlDataReader reader = cmd.ExecuteReader())
920 {
921 if(reader.HasRows)
922 {
923 reader.Read();
924 OSDMap record = new OSDMap();
925
926 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"]));
927 record.Add("visible",OSD.FromString((string)reader["visible"]));
928 record.Add("email",OSD.FromString((string)reader["email"]));
929 data.Add(record);
930 }
931 else
932 {
933 using (MySqlCommand put = new MySqlCommand(query, dbcon))
934 {
935 query = "INSERT INTO usersettings VALUES ";
936 query += "(?Id,'false','false', '')";
937
938 lock(Lock)
939 {
940 put.ExecuteNonQuery();
941 }
942 }
943 }
944 }
945 }
946 }
947 }
948 catch (Exception e)
949 {
950 m_log.DebugFormat("[PROFILES_DATA]" +
951 ": Get preferences exception {0}", e.Message);
952 }
953 return data;
954 }
955
956 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId )
957 {
958 string query = string.Empty;
959
960 query += "UPDATE userpsettings SET ";
961 query += "imviaemail=?ImViaEmail, ";
962 query += "visible=?Visible,";
963 query += "WHERE useruuid=?uuid";
964
965 try
966 {
967 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
968 {
969 dbcon.Open();
970 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
971 {
972 cmd.Parameters.AddWithValue("?ImViaEmail", emailIm.ToString().ToLower ());
973 cmd.Parameters.AddWithValue("?WantText", visible.ToString().ToLower ());
974 cmd.Parameters.AddWithValue("?uuid", avatarId.ToString());
975
976 lock(Lock)
977 {
978 cmd.ExecuteNonQuery();
979 }
980 }
981 }
982 }
983 catch (Exception e)
984 {
985 m_log.DebugFormat("[PROFILES_DATA]" +
986 ": AgentInterestsUpdate exception {0}", e.Message);
987 return false;
988 }
989 return true;
990 }
991 #endregion User Preferences
992
993 #region Integration
994 public bool GetUserAppData(ref UserAppData props, ref string result)
995 {
996 string query = string.Empty;
997
998 query += "SELECT * FROM `userdata` WHERE ";
999 query += "UserId = ?Id AND ";
1000 query += "TagId = ?TagId";
1001
1002 try
1003 {
1004 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1005 {
1006 dbcon.Open();
1007 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1008 {
1009 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
1010 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1011
1012 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1013 {
1014 if(reader.HasRows)
1015 {
1016 reader.Read();
1017 props.DataKey = (string)reader["DataKey"];
1018 props.DataVal = (string)reader["DataVal"];
1019 }
1020 else
1021 {
1022 query += "INSERT INTO userdata VALUES ( ";
1023 query += "?UserId,";
1024 query += "?TagId,";
1025 query += "?DataKey,";
1026 query += "?DataVal) ";
1027
1028 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1029 {
1030 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1031 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1032 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1033 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1034
1035 lock(Lock)
1036 {
1037 put.ExecuteNonQuery();
1038 }
1039 }
1040 }
1041 }
1042 }
1043 }
1044 }
1045 catch (Exception e)
1046 {
1047 m_log.DebugFormat("[PROFILES_DATA]" +
1048 ": Requst application data exception {0}", e.Message);
1049 result = e.Message;
1050 return false;
1051 }
1052 return true;
1053 }
1054
1055 public bool SetUserAppData(UserAppData props, ref string result)
1056 {
1057 string query = string.Empty;
1058
1059 query += "UPDATE userdata SET ";
1060 query += "TagId = ?TagId, ";
1061 query += "DataKey = ?DataKey, ";
1062 query += "DataVal = ?DataVal WHERE ";
1063 query += "UserId = ?UserId AND ";
1064 query += "TagId = ?TagId";
1065
1066 try
1067 {
1068 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1069 {
1070 dbcon.Open();
1071 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1072 {
1073 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1074 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ());
1075 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ());
1076 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ());
1077
1078 lock(Lock)
1079 {
1080 cmd.ExecuteNonQuery();
1081 }
1082 }
1083 }
1084 }
1085 catch (Exception e)
1086 {
1087 m_log.DebugFormat("[PROFILES_DATA]" +
1088 ": SetUserData exception {0}", e.Message);
1089 return false;
1090 }
1091 return true;
1092 }
1093 #endregion Integration
1094 }
1095}
1096
diff --git a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations
new file mode 100644
index 0000000..c29f1ab
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations
@@ -0,0 +1,83 @@
1:VERSION 1 # -------------------------------
2
3begin;
4
5CREATE TABLE IF NOT EXISTS `classifieds` (
6 `classifieduuid` char(36) NOT NULL,
7 `creatoruuid` char(36) NOT NULL,
8 `creationdate` int(20) NOT NULL,
9 `expirationdate` int(20) NOT NULL,
10 `category` varchar(20) NOT NULL,
11 `name` varchar(255) NOT NULL,
12 `description` text NOT NULL,
13 `parceluuid` char(36) NOT NULL,
14 `parentestate` int(11) NOT NULL,
15 `snapshotuuid` char(36) NOT NULL,
16 `simname` varchar(255) NOT NULL,
17 `posglobal` varchar(255) NOT NULL,
18 `parcelname` varchar(255) NOT NULL,
19 `classifiedflags` int(8) NOT NULL,
20 `priceforlisting` int(5) NOT NULL,
21 PRIMARY KEY (`classifieduuid`)
22) ENGINE=InnoDB DEFAULT CHARSET=latin1;
23
24
25CREATE TABLE IF NOT EXISTS `usernotes` (
26 `useruuid` varchar(36) NOT NULL,
27 `targetuuid` varchar(36) NOT NULL,
28 `notes` text NOT NULL,
29 UNIQUE KEY `useruuid` (`useruuid`,`targetuuid`)
30) ENGINE=MyISAM DEFAULT CHARSET=latin1;
31
32
33CREATE TABLE IF NOT EXISTS `userpicks` (
34 `pickuuid` varchar(36) NOT NULL,
35 `creatoruuid` varchar(36) NOT NULL,
36 `toppick` enum('true','false') NOT NULL,
37 `parceluuid` varchar(36) NOT NULL,
38 `name` varchar(255) NOT NULL,
39 `description` text NOT NULL,
40 `snapshotuuid` varchar(36) NOT NULL,
41 `user` varchar(255) NOT NULL,
42 `originalname` varchar(255) NOT NULL,
43 `simname` varchar(255) NOT NULL,
44 `posglobal` varchar(255) NOT NULL,
45 `sortorder` int(2) NOT NULL,
46 `enabled` enum('true','false') NOT NULL,
47 PRIMARY KEY (`pickuuid`)
48) ENGINE=MyISAM DEFAULT CHARSET=latin1;
49
50
51CREATE TABLE IF NOT EXISTS `userprofile` (
52 `useruuid` varchar(36) NOT NULL,
53 `profilePartner` varchar(36) NOT NULL,
54 `profileAllowPublish` binary(1) NOT NULL,
55 `profileMaturePublish` binary(1) NOT NULL,
56 `profileURL` varchar(255) NOT NULL,
57 `profileWantToMask` int(3) NOT NULL,
58 `profileWantToText` text NOT NULL,
59 `profileSkillsMask` int(3) NOT NULL,
60 `profileSkillsText` text NOT NULL,
61 `profileLanguages` text NOT NULL,
62 `profileImage` varchar(36) NOT NULL,
63 `profileAboutText` text NOT NULL,
64 `profileFirstImage` varchar(36) NOT NULL,
65 `profileFirstText` text NOT NULL,
66 PRIMARY KEY (`useruuid`)
67) ENGINE=MyISAM DEFAULT CHARSET=latin1;
68
69commit;
70
71:VERSION 2 # -------------------------------
72
73begin;
74CREATE TABLE IF NOT EXISTS `userdata` (
75 `UserId` char(36) NOT NULL,
76 `TagId` varchar(64) NOT NULL,
77 `DataKey` varchar(255),
78 `DataVal` varchar(255),
79 PRIMARY KEY (`UserId`,`TagId`)
80) ENGINE=MyISAM DEFAULT CHARSET=latin1;
81
82commit;
83
generated by cgit v1.1 at 2024-12-22 17:20:21 +0000