clockwerk-opensim – 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 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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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 bool.TryParse((string)reader["toppick"], out pick.TopPick);
401 bool.TryParse((string)reader["enabled"], out pick.Enabled);
402 pick.Name = (string)reader["name"];
403 pick.Desc = description;
404 pick.User = (string)reader["user"];
405 pick.OriginalName = (string)reader["originalname"];
406 pick.SimName = (string)reader["simname"];
407 pick.SortOrder = (int)reader["sortorder"];
408 }
409 }
410 }
411 dbcon.Close();
412 }
413 }
414 catch (Exception e)
415 {
416 m_log.DebugFormat("[PROFILES_DATA]" +
417 ": GetPickInfo exception {0}", e.Message);
418 }
419 return pick;
420 }
421  
422 public bool UpdatePicksRecord(UserProfilePick pick)
423 {
424 string query = string.Empty;
425  
426 query += "INSERT INTO userpicks VALUES (";
427 query += "?PickId,";
428 query += "?CreatorId,";
429 query += "?TopPick,";
430 query += "?ParcelId,";
431 query += "?Name,";
432 query += "?Desc,";
433 query += "?SnapshotId,";
434 query += "?User,";
435 query += "?Original,";
436 query += "?SimName,";
437 query += "?GlobalPos,";
438 query += "?SortOrder,";
439 query += "?Enabled) ";
440 query += "ON DUPLICATE KEY UPDATE ";
441 query += "parceluuid=?ParcelId,";
442 query += "name=?Name,";
443 query += "description=?Desc,";
444 query += "snapshotuuid=?SnapshotId,";
445 query += "pickuuid=?PickId,";
446 query += "posglobal=?GlobalPos";
447  
448 try
449 {
450 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
451 {
452 dbcon.Open();
453 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
454 {
455 cmd.Parameters.AddWithValue("?PickId", pick.PickId.ToString());
456 cmd.Parameters.AddWithValue("?CreatorId", pick.CreatorId.ToString());
457 cmd.Parameters.AddWithValue("?TopPick", pick.TopPick.ToString());
458 cmd.Parameters.AddWithValue("?ParcelId", pick.ParcelId.ToString());
459 cmd.Parameters.AddWithValue("?Name", pick.Name.ToString());
460 cmd.Parameters.AddWithValue("?Desc", pick.Desc.ToString());
461 cmd.Parameters.AddWithValue("?SnapshotId", pick.SnapshotId.ToString());
462 cmd.Parameters.AddWithValue("?User", pick.User.ToString());
463 cmd.Parameters.AddWithValue("?Original", pick.OriginalName.ToString());
464 cmd.Parameters.AddWithValue("?SimName",pick.SimName.ToString());
465 cmd.Parameters.AddWithValue("?GlobalPos", pick.GlobalPos);
466 cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ());
467 cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString());
468  
469 cmd.ExecuteNonQuery();
470 }
471 }
472 }
473 catch (Exception e)
474 {
475 m_log.DebugFormat("[PROFILES_DATA]" +
476 ": UpdateAvatarNotes exception {0}", e.Message);
477 return false;
478 }
479 return true;
480 }
481  
482 public bool DeletePicksRecord(UUID pickId)
483 {
484 string query = string.Empty;
485  
486 query += "DELETE FROM userpicks WHERE ";
487 query += "pickuuid = ?PickId";
488  
489 try
490 {
491 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
492 {
493 dbcon.Open();
494  
495 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
496 {
497 cmd.Parameters.AddWithValue("?PickId", pickId.ToString());
498  
499 cmd.ExecuteNonQuery();
500 }
501 }
502 }
503 catch (Exception e)
504 {
505 m_log.DebugFormat("[PROFILES_DATA]" +
506 ": DeleteUserPickRecord exception {0}", e.Message);
507 return false;
508 }
509 return true;
510 }
511 #endregion Picks Queries
512  
513 #region Avatar Notes Queries
514 public bool GetAvatarNotes(ref UserProfileNotes notes)
515 { // WIP
516 string query = string.Empty;
517  
518 query += "SELECT `notes` FROM usernotes WHERE ";
519 query += "useruuid = ?Id AND ";
520 query += "targetuuid = ?TargetId";
521 OSDArray data = new OSDArray();
522  
523 try
524 {
525 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
526 {
527 dbcon.Open();
528 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
529 {
530 cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString());
531 cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString());
532  
533 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
534 {
535 if(reader.HasRows)
536 {
537 reader.Read();
538 notes.Notes = OSD.FromString((string)reader["notes"]);
539 }
540 else
541 {
542 notes.Notes = OSD.FromString("");
543 }
544 }
545 }
546 }
547 }
548 catch (Exception e)
549 {
550 m_log.DebugFormat("[PROFILES_DATA]" +
551 ": GetAvatarNotes exception {0}", e.Message);
552 }
553 return true;
554 }
555  
556 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
557 {
558 string query = string.Empty;
559 bool remove;
560  
561 if(string.IsNullOrEmpty(note.Notes))
562 {
563 remove = true;
564 query += "DELETE FROM usernotes WHERE ";
565 query += "useruuid=?UserId AND ";
566 query += "targetuuid=?TargetId";
567 }
568 else
569 {
570 remove = false;
571 query += "INSERT INTO usernotes VALUES ( ";
572 query += "?UserId,";
573 query += "?TargetId,";
574 query += "?Notes )";
575 query += "ON DUPLICATE KEY ";
576 query += "UPDATE ";
577 query += "notes=?Notes";
578 }
579  
580 try
581 {
582 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
583 {
584 dbcon.Open();
585 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
586 {
587 if(!remove)
588 cmd.Parameters.AddWithValue("?Notes", note.Notes);
589 cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ());
590 cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString());
591  
592 cmd.ExecuteNonQuery();
593 }
594 }
595 }
596 catch (Exception e)
597 {
598 m_log.DebugFormat("[PROFILES_DATA]" +
599 ": UpdateAvatarNotes exception {0}", e.Message);
600 return false;
601 }
602 return true;
603  
604 }
605 #endregion Avatar Notes Queries
606  
607 #region Avatar Properties
608 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
609 {
610 string query = string.Empty;
611  
612 query += "SELECT * FROM userprofile WHERE ";
613 query += "useruuid = ?Id";
614  
615 try
616 {
617 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
618 {
619 dbcon.Open();
620 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
621 {
622 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
623  
624 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
625 {
626 if(reader.HasRows)
627 {
628 reader.Read();
629 props.WebUrl = (string)reader["profileURL"];
630 UUID.TryParse((string)reader["profileImage"], out props.ImageId);
631 props.AboutText = (string)reader["profileAboutText"];
632 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId);
633 props.FirstLifeText = (string)reader["profileFirstText"];
634 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId);
635 props.WantToMask = (int)reader["profileWantToMask"];
636 props.WantToText = (string)reader["profileWantToText"];
637 props.SkillsMask = (int)reader["profileSkillsMask"];
638 props.SkillsText = (string)reader["profileSkillsText"];
639 props.Language = (string)reader["profileLanguages"];
640 }
641 else
642 {
643 props.WebUrl = string.Empty;
644 props.ImageId = UUID.Zero;
645 props.AboutText = string.Empty;
646 props.FirstLifeImageId = UUID.Zero;
647 props.FirstLifeText = string.Empty;
648 props.PartnerId = UUID.Zero;
649 props.WantToMask = 0;
650 props.WantToText = string.Empty;
651 props.SkillsMask = 0;
652 props.SkillsText = string.Empty;
653 props.Language = string.Empty;
654 props.PublishProfile = false;
655 props.PublishMature = false;
656  
657 query = "INSERT INTO userprofile (";
658 query += "useruuid, ";
659 query += "profilePartner, ";
660 query += "profileAllowPublish, ";
661 query += "profileMaturePublish, ";
662 query += "profileURL, ";
663 query += "profileWantToMask, ";
664 query += "profileWantToText, ";
665 query += "profileSkillsMask, ";
666 query += "profileSkillsText, ";
667 query += "profileLanguages, ";
668 query += "profileImage, ";
669 query += "profileAboutText, ";
670 query += "profileFirstImage, ";
671 query += "profileFirstText) VALUES (";
672 query += "?userId, ";
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)";
686  
687 dbcon.Close();
688 dbcon.Open();
689  
690 using (MySqlCommand put = new MySqlCommand(query, dbcon))
691 {
692 put.Parameters.AddWithValue("?userId", props.UserId.ToString());
693 put.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
694 put.Parameters.AddWithValue("?profileAllowPublish", props.PublishProfile);
695 put.Parameters.AddWithValue("?profileMaturePublish", props.PublishMature);
696 put.Parameters.AddWithValue("?profileURL", props.WebUrl);
697 put.Parameters.AddWithValue("?profileWantToMask", props.WantToMask);
698 put.Parameters.AddWithValue("?profileWantToText", props.WantToText);
699 put.Parameters.AddWithValue("?profileSkillsMask", props.SkillsMask);
700 put.Parameters.AddWithValue("?profileSkillsText", props.SkillsText);
701 put.Parameters.AddWithValue("?profileLanguages", props.Language);
702 put.Parameters.AddWithValue("?profileImage", props.ImageId.ToString());
703 put.Parameters.AddWithValue("?profileAboutText", props.AboutText);
704 put.Parameters.AddWithValue("?profileFirstImage", props.FirstLifeImageId.ToString());
705 put.Parameters.AddWithValue("?profileFirstText", props.FirstLifeText);
706  
707 put.ExecuteNonQuery();
708 }
709 }
710 }
711 }
712 }
713 }
714 catch (Exception e)
715 {
716 m_log.DebugFormat("[PROFILES_DATA]" +
717 ": Requst properties exception {0}", e.Message);
718 result = e.Message;
719 return false;
720 }
721 return true;
722 }
723  
724 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
725 {
726 string query = string.Empty;
727  
728 query += "UPDATE userprofile SET ";
729 query += "profileURL=?profileURL, ";
730 query += "profileImage=?image, ";
731 query += "profileAboutText=?abouttext,";
732 query += "profileFirstImage=?firstlifeimage,";
733 query += "profileFirstText=?firstlifetext ";
734 query += "WHERE useruuid=?uuid";
735  
736 try
737 {
738 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
739 {
740 dbcon.Open();
741 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
742 {
743 cmd.Parameters.AddWithValue("?profileURL", props.WebUrl);
744 cmd.Parameters.AddWithValue("?image", props.ImageId.ToString());
745 cmd.Parameters.AddWithValue("?abouttext", props.AboutText);
746 cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString());
747 cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText);
748 cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString());
749  
750 cmd.ExecuteNonQuery();
751 }
752 }
753 }
754 catch (Exception e)
755 {
756 m_log.DebugFormat("[PROFILES_DATA]" +
757 ": AgentPropertiesUpdate exception {0}", e.Message);
758  
759 return false;
760 }
761 return true;
762 }
763 #endregion Avatar Properties
764  
765 #region Avatar Interests
766 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
767 {
768 string query = string.Empty;
769  
770 query += "UPDATE userprofile SET ";
771 query += "profileWantToMask=?WantMask, ";
772 query += "profileWantToText=?WantText,";
773 query += "profileSkillsMask=?SkillsMask,";
774 query += "profileSkillsText=?SkillsText, ";
775 query += "profileLanguages=?Languages ";
776 query += "WHERE useruuid=?uuid";
777  
778 try
779 {
780 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
781 {
782 dbcon.Open();
783 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
784 {
785 cmd.Parameters.AddWithValue("?WantMask", up.WantToMask);
786 cmd.Parameters.AddWithValue("?WantText", up.WantToText);
787 cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask);
788 cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText);
789 cmd.Parameters.AddWithValue("?Languages", up.Language);
790 cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString());
791  
792 cmd.ExecuteNonQuery();
793 }
794 }
795 }
796 catch (Exception e)
797 {
798 m_log.DebugFormat("[PROFILES_DATA]" +
799 ": AgentInterestsUpdate exception {0}", e.Message);
800 result = e.Message;
801 return false;
802 }
803 return true;
804 }
805 #endregion Avatar Interests
806  
807 public OSDArray GetUserImageAssets(UUID avatarId)
808 {
809 OSDArray data = new OSDArray();
810 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id";
811  
812 // Get classified image assets
813  
814  
815 try
816 {
817 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
818 {
819 dbcon.Open();
820  
821 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon))
822 {
823 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
824  
825 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
826 {
827 if(reader.HasRows)
828 {
829 while (reader.Read())
830 {
831 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
832 }
833 }
834 }
835 }
836  
837 dbcon.Close();
838 dbcon.Open();
839  
840 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
841 {
842 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
843  
844 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
845 {
846 if(reader.HasRows)
847 {
848 while (reader.Read())
849 {
850 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
851 }
852 }
853 }
854 }
855  
856 dbcon.Close();
857 dbcon.Open();
858  
859 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id";
860  
861 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
862 {
863 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
864  
865 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
866 {
867 if(reader.HasRows)
868 {
869 while (reader.Read())
870 {
871 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
872 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
873 }
874 }
875 }
876 }
877 }
878 }
879 catch (Exception e)
880 {
881 m_log.DebugFormat("[PROFILES_DATA]" +
882 ": GetAvatarNotes exception {0}", e.Message);
883 }
884 return data;
885 }
886  
887 #region User Preferences
888 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
889 {
890 string query = string.Empty;
891  
892 query += "SELECT imviaemail,visible,email FROM ";
893 query += "usersettings WHERE ";
894 query += "useruuid = ?Id";
895  
896 OSDArray data = new OSDArray();
897  
898 try
899 {
900 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
901 {
902 dbcon.Open();
903 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
904 {
905 cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString());
906  
907 using (MySqlDataReader reader = cmd.ExecuteReader())
908 {
909 if(reader.HasRows)
910 {
911 reader.Read();
912 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
913 bool.TryParse((string)reader["visible"], out pref.Visible);
914 pref.EMail = (string)reader["email"];
915 }
916 else
917 {
918 dbcon.Close();
919 dbcon.Open();
920  
921 query = "INSERT INTO usersettings VALUES ";
922 query += "(?uuid,'false','false', ?Email)";
923  
924 using (MySqlCommand put = new MySqlCommand(query, dbcon))
925 {
926  
927 put.Parameters.AddWithValue("?Email", pref.EMail);
928 put.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
929  
930 put.ExecuteNonQuery();
931 }
932 }
933 }
934 }
935 }
936 }
937 catch (Exception e)
938 {
939 m_log.DebugFormat("[PROFILES_DATA]" +
940 ": Get preferences exception {0}", e.Message);
941 result = e.Message;
942 return false;
943 }
944 return true;
945 }
946  
947 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
948 {
949 string query = string.Empty;
950  
951 query += "UPDATE usersettings SET ";
952 query += "imviaemail=?ImViaEmail, ";
953 query += "visible=?Visible ";
954 query += "WHERE useruuid=?uuid";
955  
956 try
957 {
958 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
959 {
960 dbcon.Open();
961 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
962 {
963 cmd.Parameters.AddWithValue("?ImViaEmail", pref.IMViaEmail.ToString().ToLower());
964 cmd.Parameters.AddWithValue("?Visible", pref.Visible.ToString().ToLower());
965 cmd.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
966  
967 cmd.ExecuteNonQuery();
968 }
969 }
970 }
971 catch (Exception e)
972 {
973 m_log.DebugFormat("[PROFILES_DATA]" +
974 ": AgentInterestsUpdate exception {0}", e.Message);
975 result = e.Message;
976 return false;
977 }
978 return true;
979 }
980 #endregion User Preferences
981  
982 #region Integration
983 public bool GetUserAppData(ref UserAppData props, ref string result)
984 {
985 string query = string.Empty;
986  
987 query += "SELECT * FROM `userdata` WHERE ";
988 query += "UserId = ?Id AND ";
989 query += "TagId = ?TagId";
990  
991 try
992 {
993 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
994 {
995 dbcon.Open();
996 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
997 {
998 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
999 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1000  
1001 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1002 {
1003 if(reader.HasRows)
1004 {
1005 reader.Read();
1006 props.DataKey = (string)reader["DataKey"];
1007 props.DataVal = (string)reader["DataVal"];
1008 }
1009 else
1010 {
1011 query += "INSERT INTO userdata VALUES ( ";
1012 query += "?UserId,";
1013 query += "?TagId,";
1014 query += "?DataKey,";
1015 query += "?DataVal) ";
1016  
1017 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1018 {
1019 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1020 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1021 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1022 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1023  
1024 put.ExecuteNonQuery();
1025 }
1026 }
1027 }
1028 }
1029 }
1030 }
1031 catch (Exception e)
1032 {
1033 m_log.DebugFormat("[PROFILES_DATA]" +
1034 ": Requst application data exception {0}", e.Message);
1035 result = e.Message;
1036 return false;
1037 }
1038 return true;
1039 }
1040  
1041 public bool SetUserAppData(UserAppData props, ref string result)
1042 {
1043 string query = string.Empty;
1044  
1045 query += "UPDATE userdata SET ";
1046 query += "TagId = ?TagId, ";
1047 query += "DataKey = ?DataKey, ";
1048 query += "DataVal = ?DataVal WHERE ";
1049 query += "UserId = ?UserId AND ";
1050 query += "TagId = ?TagId";
1051  
1052 try
1053 {
1054 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1055 {
1056 dbcon.Open();
1057 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1058 {
1059 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1060 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1061 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1062 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString());
1063  
1064 cmd.ExecuteNonQuery();
1065 }
1066 }
1067 }
1068 catch (Exception e)
1069 {
1070 m_log.DebugFormat("[PROFILES_DATA]" +
1071 ": SetUserData exception {0}", e.Message);
1072 return false;
1073 }
1074 return true;
1075 }
1076 #endregion Integration
1077 }
1078 }