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