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