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