opensim-development – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 eva 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  
28 using System;
29 using System.Data;
30 using System.Reflection;
31 using OpenSim.Data;
32 using OpenSim.Framework;
33 using MySql.Data.MySqlClient;
34 using OpenMetaverse;
35 using OpenMetaverse.StructuredData;
36 using log4net;
37  
38 namespace 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 bool GetUserPreferences(ref UserPreferences pref, ref string result)
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", pref.UserId.ToString());
920  
921 using (MySqlDataReader reader = cmd.ExecuteReader())
922 {
923 if(reader.HasRows)
924 {
925 reader.Read();
926 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
927 bool.TryParse((string)reader["visible"], out pref.Visible);
928 pref.EMail = (string)reader["email"];
929 }
930 else
931 {
932 dbcon.Close();
933 dbcon.Open();
934  
935 query = "INSERT INTO usersettings VALUES ";
936 query += "(?uuid,'false','false', ?Email)";
937  
938 using (MySqlCommand put = new MySqlCommand(query, dbcon))
939 {
940  
941 put.Parameters.AddWithValue("?Email", pref.EMail);
942 put.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
943  
944 put.ExecuteNonQuery();
945 }
946 }
947 }
948 }
949 }
950 }
951 catch (Exception e)
952 {
953 m_log.DebugFormat("[PROFILES_DATA]" +
954 ": Get preferences exception {0}", e.Message);
955 result = e.Message;
956 return false;
957 }
958 return true;
959 }
960  
961 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
962 {
963 string query = string.Empty;
964  
965 query += "UPDATE usersettings SET ";
966 query += "imviaemail=?ImViaEmail, ";
967 query += "visible=?Visible ";
968 query += "WHERE useruuid=?uuid";
969  
970 try
971 {
972 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
973 {
974 dbcon.Open();
975 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
976 {
977 cmd.Parameters.AddWithValue("?ImViaEmail", pref.IMViaEmail);
978 cmd.Parameters.AddWithValue("?Visible", pref.Visible);
979 cmd.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
980  
981 cmd.ExecuteNonQuery();
982 }
983 }
984 }
985 catch (Exception e)
986 {
987 m_log.DebugFormat("[PROFILES_DATA]" +
988 ": AgentInterestsUpdate exception {0}", e.Message);
989 result = e.Message;
990 return false;
991 }
992 return true;
993 }
994 #endregion User Preferences
995  
996 #region Integration
997 public bool GetUserAppData(ref UserAppData props, ref string result)
998 {
999 string query = string.Empty;
1000  
1001 query += "SELECT * FROM `userdata` WHERE ";
1002 query += "UserId = ?Id AND ";
1003 query += "TagId = ?TagId";
1004  
1005 try
1006 {
1007 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1008 {
1009 dbcon.Open();
1010 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1011 {
1012 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
1013 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1014  
1015 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1016 {
1017 if(reader.HasRows)
1018 {
1019 reader.Read();
1020 props.DataKey = (string)reader["DataKey"];
1021 props.DataVal = (string)reader["DataVal"];
1022 }
1023 else
1024 {
1025 query += "INSERT INTO userdata VALUES ( ";
1026 query += "?UserId,";
1027 query += "?TagId,";
1028 query += "?DataKey,";
1029 query += "?DataVal) ";
1030  
1031 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1032 {
1033 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1034 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1035 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1036 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1037  
1038 lock(Lock)
1039 {
1040 put.ExecuteNonQuery();
1041 }
1042 }
1043 }
1044 }
1045 }
1046 }
1047 }
1048 catch (Exception e)
1049 {
1050 m_log.DebugFormat("[PROFILES_DATA]" +
1051 ": Requst application data exception {0}", e.Message);
1052 result = e.Message;
1053 return false;
1054 }
1055 return true;
1056 }
1057  
1058 public bool SetUserAppData(UserAppData props, ref string result)
1059 {
1060 string query = string.Empty;
1061  
1062 query += "UPDATE userdata SET ";
1063 query += "TagId = ?TagId, ";
1064 query += "DataKey = ?DataKey, ";
1065 query += "DataVal = ?DataVal WHERE ";
1066 query += "UserId = ?UserId AND ";
1067 query += "TagId = ?TagId";
1068  
1069 try
1070 {
1071 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1072 {
1073 dbcon.Open();
1074 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1075 {
1076 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1077 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ());
1078 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ());
1079 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ());
1080  
1081 lock(Lock)
1082 {
1083 cmd.ExecuteNonQuery();
1084 }
1085 }
1086 }
1087 }
1088 catch (Exception e)
1089 {
1090 m_log.DebugFormat("[PROFILES_DATA]" +
1091 ": SetUserData exception {0}", e.Message);
1092 return false;
1093 }
1094 return true;
1095 }
1096 #endregion Integration
1097 }
1098 }
1099