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