aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
diff options
context:
space:
mode:
authorFernando Oliveira2013-10-12 16:33:45 -0500
committerfernando2013-10-12 16:33:45 -0500
commitff8a76825841533bdc5d534b6f58b2ab964ea6c6 (patch)
tree4ddde40916db04c1506486c9e7ba88b37f53e23e /OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
parent* pushing test (diff)
downloadopensim-SC-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.zip
opensim-SC-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.gz
opensim-SC-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.bz2
opensim-SC-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.xz
Fernando Oliveira's Postgress SQL Server Data Connector as a single commit.
* Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs * PostgreSQL data access implementation * PostgreSQL dll binarie and RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres SQL Type fixes * Postgres SQL Connection string * Data type issues * more fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to internal csharp types * More data type fix (PostgreSQL fields are case sensitive) :( * more field case sensitive fixes * changed the migration files to be case sensitive for fields. * fixed fields case * finished converting, now search for hidden bugs. * some more fixes * bool type fixed * more case fixes; * creatorID case fixed * case fields fixed * fixed default now() for TMStamp fields with don't allow nulls. * fix case sensitve for Region name and Estate name * fixed case for names for search * fix class name Error * Bug fixed on select and migrations * Un-Reverting my change due to Postgres issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for integration with Diva Distro * Added System.Core to prebuild.xml for PG project * Configured to make DIff for Push to OpenSim Project * Diffs only to PostgreSQL mods.
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs1075
1 files changed, 1075 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
new file mode 100644
index 0000000..e3cbf7f
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
@@ -0,0 +1,1075 @@
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 += "profilePartner=:profilePartner, ";
719 query += "profileURL=:profileURL, ";
720 query += "profileImage=:image, ";
721 query += "profileAboutText=:abouttext,";
722 query += "profileFirstImage=:firstlifeimage,";
723 query += "profileFirstText=:firstlifetext ";
724 query += "WHERE useruuid=:uuid";
725
726 try
727 {
728 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
729 {
730 dbcon.Open();
731 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
732 {
733 cmd.Parameters.AddWithValue("profileURL", props.WebUrl);
734 cmd.Parameters.AddWithValue("profilePartner", props.PartnerId.ToString());
735 cmd.Parameters.AddWithValue("image", props.ImageId.ToString());
736 cmd.Parameters.AddWithValue("abouttext", props.AboutText);
737 cmd.Parameters.AddWithValue("firstlifeimage", props.FirstLifeImageId.ToString());
738 cmd.Parameters.AddWithValue("firstlifetext", props.FirstLifeText);
739 cmd.Parameters.AddWithValue("uuid", props.UserId.ToString());
740
741 cmd.ExecuteNonQuery();
742 }
743 }
744 }
745 catch (Exception e)
746 {
747 m_log.DebugFormat("[PROFILES_DATA]" +
748 ": AgentPropertiesUpdate exception {0}", e.Message);
749
750 return false;
751 }
752 return true;
753 }
754 #endregion Avatar Properties
755
756 #region Avatar Interests
757 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
758 {
759 string query = string.Empty;
760
761 query += "UPDATE userprofile SET ";
762 query += "profileWantToMask=:WantMask, ";
763 query += "profileWantToText=:WantText,";
764 query += "profileSkillsMask=:SkillsMask,";
765 query += "profileSkillsText=:SkillsText, ";
766 query += "profileLanguages=:Languages ";
767 query += "WHERE useruuid=:uuid";
768
769 try
770 {
771 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
772 {
773 dbcon.Open();
774 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
775 {
776 cmd.Parameters.AddWithValue("WantMask", up.WantToMask);
777 cmd.Parameters.AddWithValue("WantText", up.WantToText);
778 cmd.Parameters.AddWithValue("SkillsMask", up.SkillsMask);
779 cmd.Parameters.AddWithValue("SkillsText", up.SkillsText);
780 cmd.Parameters.AddWithValue("Languages", up.Language);
781 cmd.Parameters.AddWithValue("uuid", up.UserId.ToString());
782
783 cmd.ExecuteNonQuery();
784 }
785 }
786 }
787 catch (Exception e)
788 {
789 m_log.DebugFormat("[PROFILES_DATA]" +
790 ": AgentInterestsUpdate exception {0}", e.Message);
791 result = e.Message;
792 return false;
793 }
794 return true;
795 }
796 #endregion Avatar Interests
797
798 public OSDArray GetUserImageAssets(UUID avatarId)
799 {
800 OSDArray data = new OSDArray();
801 string query = "SELECT \"snapshotuuid\" FROM {0} WHERE \"creatoruuid\" = :Id";
802
803 // Get classified image assets
804
805
806 try
807 {
808 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
809 {
810 dbcon.Open();
811
812 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon))
813 {
814 cmd.Parameters.AddWithValue("Id", avatarId.ToString());
815
816 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
817 {
818 if(reader.HasRows)
819 {
820 while (reader.Read())
821 {
822 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
823 }
824 }
825 }
826 }
827
828 dbcon.Close();
829 dbcon.Open();
830
831 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon))
832 {
833 cmd.Parameters.AddWithValue("Id", avatarId.ToString());
834
835 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
836 {
837 if(reader.HasRows)
838 {
839 while (reader.Read())
840 {
841 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
842 }
843 }
844 }
845 }
846
847 dbcon.Close();
848 dbcon.Open();
849
850 query = "SELECT \"profileImage\", \"profileFirstImage\" FROM \"userprofile\" WHERE \"useruuid\" = :Id";
851
852 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon))
853 {
854 cmd.Parameters.AddWithValue("Id", avatarId.ToString());
855
856 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
857 {
858 if(reader.HasRows)
859 {
860 while (reader.Read())
861 {
862 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
863 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
864 }
865 }
866 }
867 }
868 }
869 }
870 catch (Exception e)
871 {
872 m_log.DebugFormat("[PROFILES_DATA]" +
873 ": GetAvatarNotes exception {0}", e.Message);
874 }
875 return data;
876 }
877
878 #region User Preferences
879 public OSDArray GetUserPreferences(UUID avatarId)
880 {
881 string query = string.Empty;
882
883 query += "SELECT imviaemail,visible,email FROM ";
884 query += "usersettings WHERE ";
885 query += "useruuid = :Id";
886
887 OSDArray data = new OSDArray();
888
889 try
890 {
891 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
892 {
893 dbcon.Open();
894 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
895 {
896 cmd.Parameters.AddWithValue("Id", avatarId.ToString());
897
898 using (NpgsqlDataReader reader = cmd.ExecuteReader())
899 {
900 if(reader.HasRows)
901 {
902 reader.Read();
903 OSDMap record = new OSDMap();
904
905 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"]));
906 record.Add("visible",OSD.FromString((string)reader["visible"]));
907 record.Add("email",OSD.FromString((string)reader["email"]));
908 data.Add(record);
909 }
910 else
911 {
912 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
913 {
914 query = "INSERT INTO usersettings VALUES ";
915 query += "(:Id,'false','false', '')";
916
917 lock(Lock)
918 {
919 put.ExecuteNonQuery();
920 }
921 }
922 }
923 }
924 }
925 }
926 }
927 catch (Exception e)
928 {
929 m_log.DebugFormat("[PROFILES_DATA]" +
930 ": Get preferences exception {0}", e.Message);
931 }
932 return data;
933 }
934
935 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId )
936 {
937 string query = string.Empty;
938
939 query += "UPDATE userpsettings SET ";
940 query += "imviaemail=:ImViaEmail, ";
941 query += "visible=:Visible,";
942 query += "WHERE useruuid=:uuid";
943
944 try
945 {
946 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
947 {
948 dbcon.Open();
949 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
950 {
951 cmd.Parameters.AddWithValue("ImViaEmail", emailIm.ToString().ToLower ());
952 cmd.Parameters.AddWithValue("WantText", visible.ToString().ToLower ());
953 cmd.Parameters.AddWithValue("uuid", avatarId.ToString());
954
955 lock(Lock)
956 {
957 cmd.ExecuteNonQuery();
958 }
959 }
960 }
961 }
962 catch (Exception e)
963 {
964 m_log.DebugFormat("[PROFILES_DATA]" +
965 ": AgentInterestsUpdate exception {0}", e.Message);
966 return false;
967 }
968 return true;
969 }
970 #endregion User Preferences
971
972 #region Integration
973 public bool GetUserAppData(ref UserAppData props, ref string result)
974 {
975 string query = string.Empty;
976
977 query += "SELECT * FROM userdata WHERE ";
978 query += "\"UserId\" = :Id AND ";
979 query += "\"TagId\" = :TagId";
980
981 try
982 {
983 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
984 {
985 dbcon.Open();
986 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
987 {
988 cmd.Parameters.AddWithValue("Id", props.UserId.ToString());
989 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
990
991 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
992 {
993 if(reader.HasRows)
994 {
995 reader.Read();
996 props.DataKey = (string)reader["DataKey"];
997 props.DataVal = (string)reader["DataVal"];
998 }
999 else
1000 {
1001 query += "INSERT INTO userdata VALUES ( ";
1002 query += ":UserId,";
1003 query += ":TagId,";
1004 query += ":DataKey,";
1005 query += ":DataVal) ";
1006
1007 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
1008 {
1009 put.Parameters.AddWithValue("Id", props.UserId.ToString());
1010 put.Parameters.AddWithValue("TagId", props.TagId.ToString());
1011 put.Parameters.AddWithValue("DataKey", props.DataKey.ToString());
1012 put.Parameters.AddWithValue("DataVal", props.DataVal.ToString());
1013
1014 lock(Lock)
1015 {
1016 put.ExecuteNonQuery();
1017 }
1018 }
1019 }
1020 }
1021 }
1022 }
1023 }
1024 catch (Exception e)
1025 {
1026 m_log.DebugFormat("[PROFILES_DATA]" +
1027 ": Requst application data exception {0}", e.Message);
1028 result = e.Message;
1029 return false;
1030 }
1031 return true;
1032 }
1033
1034 public bool SetUserAppData(UserAppData props, ref string result)
1035 {
1036 string query = string.Empty;
1037
1038 query += "UPDATE userdata SET ";
1039 query += "\"TagId\" = :TagId, ";
1040 query += "\"DataKey\" = :DataKey, ";
1041 query += "\"DataVal\" = :DataVal WHERE ";
1042 query += "\"UserId\" = :UserId AND ";
1043 query += "\"TagId\" = :TagId";
1044
1045 try
1046 {
1047 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
1048 {
1049 dbcon.Open();
1050 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
1051 {
1052 cmd.Parameters.AddWithValue("UserId", props.UserId.ToString());
1053 cmd.Parameters.AddWithValue("TagId", props.TagId.ToString ());
1054 cmd.Parameters.AddWithValue("DataKey", props.DataKey.ToString ());
1055 cmd.Parameters.AddWithValue("DataVal", props.DataKey.ToString ());
1056
1057 lock(Lock)
1058 {
1059 cmd.ExecuteNonQuery();
1060 }
1061 }
1062 }
1063 }
1064 catch (Exception e)
1065 {
1066 m_log.DebugFormat("[PROFILES_DATA]" +
1067 ": SetUserData exception {0}", e.Message);
1068 return false;
1069 }
1070 return true;
1071 }
1072 #endregion Integration
1073 }
1074}
1075
generated by cgit v1.1 at 2024-11-19 20:40:41 +0000