aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs1073
1 files changed, 1073 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
new file mode 100644
index 0000000..f4e41b4
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
@@ -0,0 +1,1073 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Data;
30using System.Reflection;
31using OpenSim.Data;
32using OpenSim.Framework;
33using OpenMetaverse;
34using OpenMetaverse.StructuredData;
35using log4net;
36using Npgsql;
37
38namespace 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 OSDArray GetUserPreferences(UUID avatarId)
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", avatarId.ToString());
895
896 using (NpgsqlDataReader reader = cmd.ExecuteReader())
897 {
898 if(reader.HasRows)
899 {
900 reader.Read();
901 OSDMap record = new OSDMap();
902
903 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"]));
904 record.Add("visible",OSD.FromString((string)reader["visible"]));
905 record.Add("email",OSD.FromString((string)reader["email"]));
906 data.Add(record);
907 }
908 else
909 {
910 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
911 {
912 query = "INSERT INTO usersettings VALUES ";
913 query += "(:Id,'false','false', '')";
914
915 lock(Lock)
916 {
917 put.ExecuteNonQuery();
918 }
919 }
920 }
921 }
922 }
923 }
924 }
925 catch (Exception e)
926 {
927 m_log.DebugFormat("[PROFILES_DATA]" +
928 ": Get preferences exception {0}", e.Message);
929 }
930 return data;
931 }
932
933 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId )
934 {
935 string query = string.Empty;
936
937 query += "UPDATE userpsettings SET ";
938 query += "imviaemail=:ImViaEmail, ";
939 query += "visible=:Visible,";
940 query += "WHERE useruuid=:uuid";
941
942 try
943 {
944 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
945 {
946 dbcon.Open();
947 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
948 {
949 cmd.Parameters.AddWithValue("ImViaEmail", emailIm.ToString().ToLower ());
950 cmd.Parameters.AddWithValue("WantText", visible.ToString().ToLower ());
951 cmd.Parameters.AddWithValue("uuid", avatarId.ToString());
952
953 lock(Lock)
954 {
955 cmd.ExecuteNonQuery();
956 }
957 }
958 }
959 }
960 catch (Exception e)
961 {
962 m_log.DebugFormat("[PROFILES_DATA]" +
963 ": AgentInterestsUpdate exception {0}", e.Message);
964 return false;
965 }
966 return true;
967 }
968 #endregion User Preferences
969
970 #region Integration
971 public bool GetUserAppData(ref UserAppData props, ref string result)
972 {
973 string query = string.Empty;
974
975 query += "SELECT * FROM userdata WHERE ";
976 query += "\"UserId\" = :Id AND ";
977 query += "\"TagId\" = :TagId";
978
979 try
980 {
981 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
982 {
983 dbcon.Open();
984 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
985 {
986 cmd.Parameters.AddWithValue("Id", props.UserId.ToString());
987 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
988
989 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
990 {
991 if(reader.HasRows)
992 {
993 reader.Read();
994 props.DataKey = (string)reader["DataKey"];
995 props.DataVal = (string)reader["DataVal"];
996 }
997 else
998 {
999 query += "INSERT INTO userdata VALUES ( ";
1000 query += ":UserId,";
1001 query += ":TagId,";
1002 query += ":DataKey,";
1003 query += ":DataVal) ";
1004
1005 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
1006 {
1007 put.Parameters.AddWithValue("Id", props.UserId.ToString());
1008 put.Parameters.AddWithValue("TagId", props.TagId.ToString());
1009 put.Parameters.AddWithValue("DataKey", props.DataKey.ToString());
1010 put.Parameters.AddWithValue("DataVal", props.DataVal.ToString());
1011
1012 lock(Lock)
1013 {
1014 put.ExecuteNonQuery();
1015 }
1016 }
1017 }
1018 }
1019 }
1020 }
1021 }
1022 catch (Exception e)
1023 {
1024 m_log.DebugFormat("[PROFILES_DATA]" +
1025 ": Requst application data exception {0}", e.Message);
1026 result = e.Message;
1027 return false;
1028 }
1029 return true;
1030 }
1031
1032 public bool SetUserAppData(UserAppData props, ref string result)
1033 {
1034 string query = string.Empty;
1035
1036 query += "UPDATE userdata SET ";
1037 query += "\"TagId\" = :TagId, ";
1038 query += "\"DataKey\" = :DataKey, ";
1039 query += "\"DataVal\" = :DataVal WHERE ";
1040 query += "\"UserId\" = :UserId AND ";
1041 query += "\"TagId\" = :TagId";
1042
1043 try
1044 {
1045 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
1046 {
1047 dbcon.Open();
1048 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
1049 {
1050 cmd.Parameters.AddWithValue("UserId", props.UserId.ToString());
1051 cmd.Parameters.AddWithValue("TagId", props.TagId.ToString ());
1052 cmd.Parameters.AddWithValue("DataKey", props.DataKey.ToString ());
1053 cmd.Parameters.AddWithValue("DataVal", props.DataKey.ToString ());
1054
1055 lock(Lock)
1056 {
1057 cmd.ExecuteNonQuery();
1058 }
1059 }
1060 }
1061 }
1062 catch (Exception e)
1063 {
1064 m_log.DebugFormat("[PROFILES_DATA]" +
1065 ": SetUserData exception {0}", e.Message);
1066 return false;
1067 }
1068 return true;
1069 }
1070 #endregion Integration
1071 }
1072}
1073
generated by cgit v1.1 at 2024-07-16 10:21:34 +0000