clockwerk-opensim – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 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  
28 using System;
29 using System.Collections.Generic;
30 using System.Data;
31 using System.Reflection;
32 using log4net;
33 #if CSharpSqlite
34 using Community.CsharpSqlite.Sqlite;
35 #else
36 using Mono.Data.Sqlite;
37 #endif
38 using OpenMetaverse;
39 using OpenMetaverse.StructuredData;
40 using OpenSim.Framework;
41 using OpenSim.Region.Framework.Interfaces;
42  
43 namespace 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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.User = (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.DebugFormat("[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.User.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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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.DebugFormat("[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 += "WHERE useruuid=:uuid";
752  
753 try
754 {
755 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
756 {
757 cmd.CommandText = query;
758 cmd.Parameters.AddWithValue(":ImViaEmail", pref.IMViaEmail);
759 cmd.Parameters.AddWithValue(":Visible", pref.Visible);
760 cmd.Parameters.AddWithValue(":uuid", pref.UserId.ToString());
761  
762 cmd.ExecuteNonQuery();
763 }
764 }
765 catch (Exception e)
766 {
767 m_log.DebugFormat("[PROFILES_DATA]" +
768 ": AgentInterestsUpdate exception {0}", e.Message);
769 result = e.Message;
770 return false;
771 }
772 return true;
773 }
774  
775 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
776 {
777 IDataReader reader = null;
778 string query = string.Empty;
779  
780 query += "SELECT imviaemail,visible,email FROM ";
781 query += "usersettings WHERE ";
782 query += "useruuid = :Id";
783  
784 OSDArray data = new OSDArray();
785  
786 try
787 {
788 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
789 {
790 cmd.CommandText = query;
791 cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString());
792  
793 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
794 {
795 if(reader.Read())
796 {
797 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
798 bool.TryParse((string)reader["visible"], out pref.Visible);
799 pref.EMail = (string)reader["email"];
800 }
801 else
802 {
803 query = "INSERT INTO usersettings VALUES ";
804 query += "(:Id,'false','false', :Email)";
805  
806 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
807 {
808 put.Parameters.AddWithValue(":Id", pref.UserId.ToString());
809 put.Parameters.AddWithValue(":Email", pref.EMail);
810 put.ExecuteNonQuery();
811  
812 }
813 }
814 }
815 }
816 }
817 catch (Exception e)
818 {
819 m_log.DebugFormat("[PROFILES_DATA]" +
820 ": Get preferences exception {0}", e.Message);
821 result = e.Message;
822 return false;
823 }
824 return true;
825 }
826  
827 public bool GetUserAppData(ref UserAppData props, ref string result)
828 {
829 IDataReader reader = null;
830 string query = string.Empty;
831  
832 query += "SELECT * FROM `userdata` WHERE ";
833 query += "UserId = :Id AND ";
834 query += "TagId = :TagId";
835  
836 try
837 {
838 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
839 {
840 cmd.CommandText = query;
841 cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
842 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
843  
844 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
845 {
846 if(reader.Read())
847 {
848 props.DataKey = (string)reader["DataKey"];
849 props.DataVal = (string)reader["DataVal"];
850 }
851 else
852 {
853 query += "INSERT INTO userdata VALUES ( ";
854 query += ":UserId,";
855 query += ":TagId,";
856 query += ":DataKey,";
857 query += ":DataVal) ";
858  
859 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
860 {
861 put.Parameters.AddWithValue(":Id", props.UserId.ToString());
862 put.Parameters.AddWithValue(":TagId", props.TagId.ToString());
863 put.Parameters.AddWithValue(":DataKey", props.DataKey.ToString());
864 put.Parameters.AddWithValue(":DataVal", props.DataVal.ToString());
865  
866 put.ExecuteNonQuery();
867 }
868 }
869 }
870 }
871 }
872 catch (Exception e)
873 {
874 m_log.DebugFormat("[PROFILES_DATA]" +
875 ": Requst application data exception {0}", e.Message);
876 result = e.Message;
877 return false;
878 }
879 return true;
880 }
881 public bool SetUserAppData(UserAppData props, ref string result)
882 {
883 string query = string.Empty;
884  
885 query += "UPDATE userdata SET ";
886 query += "TagId = :TagId, ";
887 query += "DataKey = :DataKey, ";
888 query += "DataVal = :DataVal WHERE ";
889 query += "UserId = :UserId AND ";
890 query += "TagId = :TagId";
891  
892 try
893 {
894 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
895 {
896 cmd.CommandText = query;
897 cmd.Parameters.AddWithValue(":UserId", props.UserId.ToString());
898 cmd.Parameters.AddWithValue(":TagId", props.TagId.ToString ());
899 cmd.Parameters.AddWithValue(":DataKey", props.DataKey.ToString ());
900 cmd.Parameters.AddWithValue(":DataVal", props.DataKey.ToString ());
901  
902 cmd.ExecuteNonQuery();
903 }
904 }
905 catch (Exception e)
906 {
907 m_log.DebugFormat("[PROFILES_DATA]" +
908 ": SetUserData exception {0}", e.Message);
909 return false;
910 }
911 return true;
912 }
913 public OSDArray GetUserImageAssets(UUID avatarId)
914 {
915 IDataReader reader = null;
916 OSDArray data = new OSDArray();
917 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = :Id";
918  
919 // Get classified image assets
920  
921  
922 try
923 {
924 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
925 {
926 cmd.CommandText = query;
927 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
928  
929 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
930 {
931 while(reader.Read())
932 {
933 data.Add(new OSDString((string)reader["snapshotuuid"].ToString()));
934 }
935 }
936 }
937  
938 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
939 {
940 cmd.CommandText = query;
941 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
942  
943 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
944 {
945 if(reader.Read())
946 {
947 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
948 }
949 }
950 }
951  
952 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = :Id";
953  
954 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
955 {
956 cmd.CommandText = query;
957 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
958  
959 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
960 {
961 if(reader.Read())
962 {
963 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
964 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
965 }
966 }
967 }
968 }
969 catch (Exception e)
970 {
971 m_log.DebugFormat("[PROFILES_DATA]" +
972 ": GetAvatarNotes exception {0}", e.Message);
973 }
974 return data;
975 }
976 #endregion
977 }
978 }
979