aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MySQL/MySQLUserProfilesData.cs1102
1 files changed, 1102 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
new file mode 100644
index 0000000..8b50c54
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
@@ -0,0 +1,1102 @@
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 else
550 {
551 notes.Notes = OSD.FromString("");
552 }
553 }
554 }
555 }
556 }
557 catch (Exception e)
558 {
559 m_log.DebugFormat("[PROFILES_DATA]" +
560 ": GetAvatarNotes exception {0}", e.Message);
561 }
562 return true;
563 }
564
565 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
566 {
567 string query = string.Empty;
568 bool remove;
569
570 if(string.IsNullOrEmpty(note.Notes))
571 {
572 remove = true;
573 query += "DELETE FROM usernotes WHERE ";
574 query += "useruuid=?UserId AND ";
575 query += "targetuuid=?TargetId";
576 }
577 else
578 {
579 remove = false;
580 query += "INSERT INTO usernotes VALUES ( ";
581 query += "?UserId,";
582 query += "?TargetId,";
583 query += "?Notes )";
584 query += "ON DUPLICATE KEY ";
585 query += "UPDATE ";
586 query += "notes=?Notes";
587 }
588
589 try
590 {
591 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
592 {
593 dbcon.Open();
594 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
595 {
596 if(!remove)
597 cmd.Parameters.AddWithValue("?Notes", note.Notes);
598 cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ());
599 cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString());
600
601 cmd.ExecuteNonQuery();
602 }
603 }
604 }
605 catch (Exception e)
606 {
607 m_log.DebugFormat("[PROFILES_DATA]" +
608 ": UpdateAvatarNotes exception {0}", e.Message);
609 return false;
610 }
611 return true;
612
613 }
614 #endregion Avatar Notes Queries
615
616 #region Avatar Properties
617 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
618 {
619 string query = string.Empty;
620
621 query += "SELECT * FROM userprofile WHERE ";
622 query += "useruuid = ?Id";
623
624 try
625 {
626 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
627 {
628 dbcon.Open();
629 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
630 {
631 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
632
633 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
634 {
635 if(reader.HasRows)
636 {
637 m_log.DebugFormat("[PROFILES_DATA]" +
638 ": Getting data for {0}.", props.UserId);
639 reader.Read();
640 props.WebUrl = (string)reader["profileURL"];
641 UUID.TryParse((string)reader["profileImage"], out props.ImageId);
642 props.AboutText = (string)reader["profileAboutText"];
643 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId);
644 props.FirstLifeText = (string)reader["profileFirstText"];
645 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId);
646 props.WantToMask = (int)reader["profileWantToMask"];
647 props.WantToText = (string)reader["profileWantToText"];
648 props.SkillsMask = (int)reader["profileSkillsMask"];
649 props.SkillsText = (string)reader["profileSkillsText"];
650 props.Language = (string)reader["profileLanguages"];
651 }
652 else
653 {
654 m_log.DebugFormat("[PROFILES_DATA]" +
655 ": No data for {0}", props.UserId);
656
657 props.WebUrl = string.Empty;
658 props.ImageId = UUID.Zero;
659 props.AboutText = string.Empty;
660 props.FirstLifeImageId = UUID.Zero;
661 props.FirstLifeText = string.Empty;
662 props.PartnerId = UUID.Zero;
663 props.WantToMask = 0;
664 props.WantToText = string.Empty;
665 props.SkillsMask = 0;
666 props.SkillsText = string.Empty;
667 props.Language = string.Empty;
668 props.PublishProfile = false;
669 props.PublishMature = false;
670
671 query = "INSERT INTO userprofile (";
672 query += "useruuid, ";
673 query += "profilePartner, ";
674 query += "profileAllowPublish, ";
675 query += "profileMaturePublish, ";
676 query += "profileURL, ";
677 query += "profileWantToMask, ";
678 query += "profileWantToText, ";
679 query += "profileSkillsMask, ";
680 query += "profileSkillsText, ";
681 query += "profileLanguages, ";
682 query += "profileImage, ";
683 query += "profileAboutText, ";
684 query += "profileFirstImage, ";
685 query += "profileFirstText) VALUES (";
686 query += "?userId, ";
687 query += "?profilePartner, ";
688 query += "?profileAllowPublish, ";
689 query += "?profileMaturePublish, ";
690 query += "?profileURL, ";
691 query += "?profileWantToMask, ";
692 query += "?profileWantToText, ";
693 query += "?profileSkillsMask, ";
694 query += "?profileSkillsText, ";
695 query += "?profileLanguages, ";
696 query += "?profileImage, ";
697 query += "?profileAboutText, ";
698 query += "?profileFirstImage, ";
699 query += "?profileFirstText)";
700
701 dbcon.Close();
702 dbcon.Open();
703
704 using (MySqlCommand put = new MySqlCommand(query, dbcon))
705 {
706 put.Parameters.AddWithValue("?userId", props.UserId.ToString());
707 put.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
708 put.Parameters.AddWithValue("?profileAllowPublish", props.PublishProfile);
709 put.Parameters.AddWithValue("?profileMaturePublish", props.PublishMature);
710 put.Parameters.AddWithValue("?profileURL", props.WebUrl);
711 put.Parameters.AddWithValue("?profileWantToMask", props.WantToMask);
712 put.Parameters.AddWithValue("?profileWantToText", props.WantToText);
713 put.Parameters.AddWithValue("?profileSkillsMask", props.SkillsMask);
714 put.Parameters.AddWithValue("?profileSkillsText", props.SkillsText);
715 put.Parameters.AddWithValue("?profileLanguages", props.Language);
716 put.Parameters.AddWithValue("?profileImage", props.ImageId.ToString());
717 put.Parameters.AddWithValue("?profileAboutText", props.AboutText);
718 put.Parameters.AddWithValue("?profileFirstImage", props.FirstLifeImageId.ToString());
719 put.Parameters.AddWithValue("?profileFirstText", props.FirstLifeText);
720
721 put.ExecuteNonQuery();
722 }
723 }
724 }
725 }
726 }
727 }
728 catch (Exception e)
729 {
730 m_log.DebugFormat("[PROFILES_DATA]" +
731 ": Requst properties exception {0}", e.Message);
732 result = e.Message;
733 return false;
734 }
735 return true;
736 }
737
738 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
739 {
740 string query = string.Empty;
741
742 query += "UPDATE userprofile SET ";
743 query += "profileURL=?profileURL, ";
744 query += "profileImage=?image, ";
745 query += "profileAboutText=?abouttext,";
746 query += "profileFirstImage=?firstlifeimage,";
747 query += "profileFirstText=?firstlifetext ";
748 query += "WHERE useruuid=?uuid";
749
750 try
751 {
752 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
753 {
754 dbcon.Open();
755 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
756 {
757 cmd.Parameters.AddWithValue("?profileURL", props.WebUrl);
758 cmd.Parameters.AddWithValue("?image", props.ImageId.ToString());
759 cmd.Parameters.AddWithValue("?abouttext", props.AboutText);
760 cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString());
761 cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText);
762 cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString());
763
764 cmd.ExecuteNonQuery();
765 }
766 }
767 }
768 catch (Exception e)
769 {
770 m_log.DebugFormat("[PROFILES_DATA]" +
771 ": AgentPropertiesUpdate exception {0}", e.Message);
772
773 return false;
774 }
775 return true;
776 }
777 #endregion Avatar Properties
778
779 #region Avatar Interests
780 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
781 {
782 string query = string.Empty;
783
784 query += "UPDATE userprofile SET ";
785 query += "profileWantToMask=?WantMask, ";
786 query += "profileWantToText=?WantText,";
787 query += "profileSkillsMask=?SkillsMask,";
788 query += "profileSkillsText=?SkillsText, ";
789 query += "profileLanguages=?Languages ";
790 query += "WHERE useruuid=?uuid";
791
792 try
793 {
794 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
795 {
796 dbcon.Open();
797 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
798 {
799 cmd.Parameters.AddWithValue("?WantMask", up.WantToMask);
800 cmd.Parameters.AddWithValue("?WantText", up.WantToText);
801 cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask);
802 cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText);
803 cmd.Parameters.AddWithValue("?Languages", up.Language);
804 cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString());
805
806 cmd.ExecuteNonQuery();
807 }
808 }
809 }
810 catch (Exception e)
811 {
812 m_log.DebugFormat("[PROFILES_DATA]" +
813 ": AgentInterestsUpdate exception {0}", e.Message);
814 result = e.Message;
815 return false;
816 }
817 return true;
818 }
819 #endregion Avatar Interests
820
821 public OSDArray GetUserImageAssets(UUID avatarId)
822 {
823 OSDArray data = new OSDArray();
824 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id";
825
826 // Get classified image assets
827
828
829 try
830 {
831 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
832 {
833 dbcon.Open();
834
835 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon))
836 {
837 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
838
839 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
840 {
841 if(reader.HasRows)
842 {
843 while (reader.Read())
844 {
845 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
846 }
847 }
848 }
849 }
850
851 dbcon.Close();
852 dbcon.Open();
853
854 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
855 {
856 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
857
858 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
859 {
860 if(reader.HasRows)
861 {
862 while (reader.Read())
863 {
864 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
865 }
866 }
867 }
868 }
869
870 dbcon.Close();
871 dbcon.Open();
872
873 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id";
874
875 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
876 {
877 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
878
879 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
880 {
881 if(reader.HasRows)
882 {
883 while (reader.Read())
884 {
885 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
886 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
887 }
888 }
889 }
890 }
891 }
892 }
893 catch (Exception e)
894 {
895 m_log.DebugFormat("[PROFILES_DATA]" +
896 ": GetAvatarNotes exception {0}", e.Message);
897 }
898 return data;
899 }
900
901 #region User Preferences
902 public OSDArray GetUserPreferences(UUID avatarId)
903 {
904 string query = string.Empty;
905
906 query += "SELECT imviaemail,visible,email FROM ";
907 query += "usersettings WHERE ";
908 query += "useruuid = ?Id";
909
910 OSDArray data = new OSDArray();
911
912 try
913 {
914 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
915 {
916 dbcon.Open();
917 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
918 {
919 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
920
921 using (MySqlDataReader reader = cmd.ExecuteReader())
922 {
923 if(reader.HasRows)
924 {
925 reader.Read();
926 OSDMap record = new OSDMap();
927
928 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"]));
929 record.Add("visible",OSD.FromString((string)reader["visible"]));
930 record.Add("email",OSD.FromString((string)reader["email"]));
931 data.Add(record);
932 }
933 else
934 {
935 dbcon.Close();
936 dbcon.Open();
937
938 query = "INSERT INTO usersettings VALUES ";
939 query += "(?uuid,'false','false', ?Email)";
940
941 using (MySqlCommand put = new MySqlCommand(query, dbcon))
942 {
943
944// put.Parameters.AddWithValue("?Email", pref.EMail);
945// put.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
946
947 put.ExecuteNonQuery();
948 }
949 }
950 }
951 }
952 }
953 }
954 catch (Exception e)
955 {
956 m_log.DebugFormat("[PROFILES_DATA]" +
957 ": Get preferences exception {0}", e.Message);
958 }
959 return data;
960 }
961
962 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId )
963 {
964 string query = string.Empty;
965
966 query += "UPDATE userpsettings SET ";
967 query += "imviaemail=?ImViaEmail, ";
968 query += "visible=?Visible,";
969 query += "WHERE useruuid=?uuid";
970
971 try
972 {
973 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
974 {
975 dbcon.Open();
976 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
977 {
978 cmd.Parameters.AddWithValue("?ImViaEmail", emailIm.ToString().ToLower ());
979 cmd.Parameters.AddWithValue("?WantText", visible.ToString().ToLower ());
980 cmd.Parameters.AddWithValue("?uuid", avatarId.ToString());
981
982 lock(Lock)
983 {
984 cmd.ExecuteNonQuery();
985 }
986 }
987 }
988 }
989 catch (Exception e)
990 {
991 m_log.DebugFormat("[PROFILES_DATA]" +
992 ": AgentInterestsUpdate exception {0}", e.Message);
993 return false;
994 }
995 return true;
996 }
997 #endregion User Preferences
998
999 #region Integration
1000 public bool GetUserAppData(ref UserAppData props, ref string result)
1001 {
1002 string query = string.Empty;
1003
1004 query += "SELECT * FROM `userdata` WHERE ";
1005 query += "UserId = ?Id AND ";
1006 query += "TagId = ?TagId";
1007
1008 try
1009 {
1010 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1011 {
1012 dbcon.Open();
1013 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1014 {
1015 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
1016 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1017
1018 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1019 {
1020 if(reader.HasRows)
1021 {
1022 reader.Read();
1023 props.DataKey = (string)reader["DataKey"];
1024 props.DataVal = (string)reader["DataVal"];
1025 }
1026 else
1027 {
1028 query += "INSERT INTO userdata VALUES ( ";
1029 query += "?UserId,";
1030 query += "?TagId,";
1031 query += "?DataKey,";
1032 query += "?DataVal) ";
1033
1034 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1035 {
1036 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1037 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1038 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1039 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1040
1041 lock(Lock)
1042 {
1043 put.ExecuteNonQuery();
1044 }
1045 }
1046 }
1047 }
1048 }
1049 }
1050 }
1051 catch (Exception e)
1052 {
1053 m_log.DebugFormat("[PROFILES_DATA]" +
1054 ": Requst application data exception {0}", e.Message);
1055 result = e.Message;
1056 return false;
1057 }
1058 return true;
1059 }
1060
1061 public bool SetUserAppData(UserAppData props, ref string result)
1062 {
1063 string query = string.Empty;
1064
1065 query += "UPDATE userdata SET ";
1066 query += "TagId = ?TagId, ";
1067 query += "DataKey = ?DataKey, ";
1068 query += "DataVal = ?DataVal WHERE ";
1069 query += "UserId = ?UserId AND ";
1070 query += "TagId = ?TagId";
1071
1072 try
1073 {
1074 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1075 {
1076 dbcon.Open();
1077 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1078 {
1079 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1080 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ());
1081 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ());
1082 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ());
1083
1084 lock(Lock)
1085 {
1086 cmd.ExecuteNonQuery();
1087 }
1088 }
1089 }
1090 }
1091 catch (Exception e)
1092 {
1093 m_log.DebugFormat("[PROFILES_DATA]" +
1094 ": SetUserData exception {0}", e.Message);
1095 return false;
1096 }
1097 return true;
1098 }
1099 #endregion Integration
1100 }
1101}
1102