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