opensim-development – Blame information for rev 1

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