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