clockwerk-opensim-stable – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 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 += "profilePartner=?profilePartner, ";
744 query += "profileURL=?profileURL, ";
745 query += "profileImage=?image, ";
746 query += "profileAboutText=?abouttext,";
747 query += "profileFirstImage=?firstlifeimage,";
748 query += "profileFirstText=?firstlifetext ";
749 query += "WHERE useruuid=?uuid";
750  
751 try
752 {
753 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
754 {
755 dbcon.Open();
756 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
757 {
758 cmd.Parameters.AddWithValue("?profileURL", props.WebUrl);
759 cmd.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
760 cmd.Parameters.AddWithValue("?image", props.ImageId.ToString());
761 cmd.Parameters.AddWithValue("?abouttext", props.AboutText);
762 cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString());
763 cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText);
764 cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString());
765  
766 cmd.ExecuteNonQuery();
767 }
768 }
769 }
770 catch (Exception e)
771 {
772 m_log.DebugFormat("[PROFILES_DATA]" +
773 ": AgentPropertiesUpdate exception {0}", e.Message);
774  
775 return false;
776 }
777 return true;
778 }
779 #endregion Avatar Properties
780  
781 #region Avatar Interests
782 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
783 {
784 string query = string.Empty;
785  
786 query += "UPDATE userprofile SET ";
787 query += "profileWantToMask=?WantMask, ";
788 query += "profileWantToText=?WantText,";
789 query += "profileSkillsMask=?SkillsMask,";
790 query += "profileSkillsText=?SkillsText, ";
791 query += "profileLanguages=?Languages ";
792 query += "WHERE useruuid=?uuid";
793  
794 try
795 {
796 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
797 {
798 dbcon.Open();
799 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
800 {
801 cmd.Parameters.AddWithValue("?WantMask", up.WantToMask);
802 cmd.Parameters.AddWithValue("?WantText", up.WantToText);
803 cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask);
804 cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText);
805 cmd.Parameters.AddWithValue("?Languages", up.Language);
806 cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString());
807  
808 cmd.ExecuteNonQuery();
809 }
810 }
811 }
812 catch (Exception e)
813 {
814 m_log.DebugFormat("[PROFILES_DATA]" +
815 ": AgentInterestsUpdate exception {0}", e.Message);
816 result = e.Message;
817 return false;
818 }
819 return true;
820 }
821 #endregion Avatar Interests
822  
823 public OSDArray GetUserImageAssets(UUID avatarId)
824 {
825 OSDArray data = new OSDArray();
826 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id";
827  
828 // Get classified image assets
829  
830  
831 try
832 {
833 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
834 {
835 dbcon.Open();
836  
837 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon))
838 {
839 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
840  
841 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
842 {
843 if(reader.HasRows)
844 {
845 while (reader.Read())
846 {
847 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
848 }
849 }
850 }
851 }
852  
853 dbcon.Close();
854 dbcon.Open();
855  
856 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
857 {
858 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
859  
860 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
861 {
862 if(reader.HasRows)
863 {
864 while (reader.Read())
865 {
866 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
867 }
868 }
869 }
870 }
871  
872 dbcon.Close();
873 dbcon.Open();
874  
875 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id";
876  
877 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
878 {
879 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
880  
881 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
882 {
883 if(reader.HasRows)
884 {
885 while (reader.Read())
886 {
887 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
888 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
889 }
890 }
891 }
892 }
893 }
894 }
895 catch (Exception e)
896 {
897 m_log.DebugFormat("[PROFILES_DATA]" +
898 ": GetAvatarNotes exception {0}", e.Message);
899 }
900 return data;
901 }
902  
903 #region User Preferences
904 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
905 {
906 string query = string.Empty;
907  
908 query += "SELECT imviaemail,visible,email FROM ";
909 query += "usersettings WHERE ";
910 query += "useruuid = ?Id";
911  
912 OSDArray data = new OSDArray();
913  
914 try
915 {
916 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
917 {
918 dbcon.Open();
919 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
920 {
921 cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString());
922  
923 using (MySqlDataReader reader = cmd.ExecuteReader())
924 {
925 if(reader.HasRows)
926 {
927 reader.Read();
928 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
929 bool.TryParse((string)reader["visible"], out pref.Visible);
930 pref.EMail = (string)reader["email"];
931 }
932 else
933 {
934 dbcon.Close();
935 dbcon.Open();
936  
937 query = "INSERT INTO usersettings VALUES ";
938 query += "(?uuid,'false','false', ?Email)";
939  
940 using (MySqlCommand put = new MySqlCommand(query, dbcon))
941 {
942  
943 put.Parameters.AddWithValue("?Email", pref.EMail);
944 put.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
945  
946 put.ExecuteNonQuery();
947 }
948 }
949 }
950 }
951 }
952 }
953 catch (Exception e)
954 {
955 m_log.DebugFormat("[PROFILES_DATA]" +
956 ": Get preferences exception {0}", e.Message);
957 result = e.Message;
958 return false;
959 }
960 return true;
961 }
962  
963 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
964 {
965 string query = string.Empty;
966  
967 query += "UPDATE usersettings SET ";
968 query += "imviaemail=?ImViaEmail, ";
969 query += "visible=?Visible ";
970 query += "WHERE useruuid=?uuid";
971  
972 try
973 {
974 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
975 {
976 dbcon.Open();
977 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
978 {
979 cmd.Parameters.AddWithValue("?ImViaEmail", pref.IMViaEmail);
980 cmd.Parameters.AddWithValue("?Visible", pref.Visible);
981 cmd.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
982  
983 cmd.ExecuteNonQuery();
984 }
985 }
986 }
987 catch (Exception e)
988 {
989 m_log.DebugFormat("[PROFILES_DATA]" +
990 ": AgentInterestsUpdate exception {0}", e.Message);
991 result = e.Message;
992 return false;
993 }
994 return true;
995 }
996 #endregion User Preferences
997  
998 #region Integration
999 public bool GetUserAppData(ref UserAppData props, ref string result)
1000 {
1001 string query = string.Empty;
1002  
1003 query += "SELECT * FROM `userdata` WHERE ";
1004 query += "UserId = ?Id AND ";
1005 query += "TagId = ?TagId";
1006  
1007 try
1008 {
1009 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1010 {
1011 dbcon.Open();
1012 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1013 {
1014 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
1015 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1016  
1017 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1018 {
1019 if(reader.HasRows)
1020 {
1021 reader.Read();
1022 props.DataKey = (string)reader["DataKey"];
1023 props.DataVal = (string)reader["DataVal"];
1024 }
1025 else
1026 {
1027 query += "INSERT INTO userdata VALUES ( ";
1028 query += "?UserId,";
1029 query += "?TagId,";
1030 query += "?DataKey,";
1031 query += "?DataVal) ";
1032  
1033 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1034 {
1035 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1036 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1037 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1038 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1039  
1040 lock(Lock)
1041 {
1042 put.ExecuteNonQuery();
1043 }
1044 }
1045 }
1046 }
1047 }
1048 }
1049 }
1050 catch (Exception e)
1051 {
1052 m_log.DebugFormat("[PROFILES_DATA]" +
1053 ": Requst application data exception {0}", e.Message);
1054 result = e.Message;
1055 return false;
1056 }
1057 return true;
1058 }
1059  
1060 public bool SetUserAppData(UserAppData props, ref string result)
1061 {
1062 string query = string.Empty;
1063  
1064 query += "UPDATE userdata SET ";
1065 query += "TagId = ?TagId, ";
1066 query += "DataKey = ?DataKey, ";
1067 query += "DataVal = ?DataVal WHERE ";
1068 query += "UserId = ?UserId AND ";
1069 query += "TagId = ?TagId";
1070  
1071 try
1072 {
1073 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1074 {
1075 dbcon.Open();
1076 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1077 {
1078 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1079 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ());
1080 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ());
1081 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ());
1082  
1083 lock(Lock)
1084 {
1085 cmd.ExecuteNonQuery();
1086 }
1087 }
1088 }
1089 }
1090 catch (Exception e)
1091 {
1092 m_log.DebugFormat("[PROFILES_DATA]" +
1093 ": SetUserData exception {0}", e.Message);
1094 return false;
1095 }
1096 return true;
1097 }
1098 #endregion Integration
1099 }
1100 }
1101