clockwerk-opensim-stable – 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 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 += "profilePartner=:profilePartner, ";
683 query += "profileURL=:profileURL, ";
684 query += "profileImage=:image, ";
685 query += "profileAboutText=:abouttext,";
686 query += "profileFirstImage=:firstlifeimage,";
687 query += "profileFirstText=:firstlifetext ";
688 query += "WHERE useruuid=:uuid";
689  
690 try
691 {
692 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
693 {
694 cmd.CommandText = query;
695 cmd.Parameters.AddWithValue(":profileURL", props.WebUrl);
696 cmd.Parameters.AddWithValue(":profilePartner", props.PartnerId.ToString());
697 cmd.Parameters.AddWithValue(":image", props.ImageId.ToString());
698 cmd.Parameters.AddWithValue(":abouttext", props.AboutText);
699 cmd.Parameters.AddWithValue(":firstlifeimage", props.FirstLifeImageId.ToString());
700 cmd.Parameters.AddWithValue(":firstlifetext", props.FirstLifeText);
701 cmd.Parameters.AddWithValue(":uuid", props.UserId.ToString());
702  
703 cmd.ExecuteNonQuery();
704 }
705 }
706 catch (Exception e)
707 {
708 m_log.DebugFormat("[PROFILES_DATA]" +
709 ": AgentPropertiesUpdate exception {0}", e.Message);
710  
711 return false;
712 }
713 return true;
714 }
715  
716 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
717 {
718 string query = string.Empty;
719  
720 query += "UPDATE userprofile SET ";
721 query += "profileWantToMask=:WantMask, ";
722 query += "profileWantToText=:WantText,";
723 query += "profileSkillsMask=:SkillsMask,";
724 query += "profileSkillsText=:SkillsText, ";
725 query += "profileLanguages=:Languages ";
726 query += "WHERE useruuid=:uuid";
727  
728 try
729 {
730 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
731 {
732 cmd.CommandText = query;
733 cmd.Parameters.AddWithValue(":WantMask", up.WantToMask);
734 cmd.Parameters.AddWithValue(":WantText", up.WantToText);
735 cmd.Parameters.AddWithValue(":SkillsMask", up.SkillsMask);
736 cmd.Parameters.AddWithValue(":SkillsText", up.SkillsText);
737 cmd.Parameters.AddWithValue(":Languages", up.Language);
738 cmd.Parameters.AddWithValue(":uuid", up.UserId.ToString());
739  
740 cmd.ExecuteNonQuery();
741 }
742 }
743 catch (Exception e)
744 {
745 m_log.DebugFormat("[PROFILES_DATA]" +
746 ": AgentInterestsUpdate exception {0}", e.Message);
747 result = e.Message;
748 return false;
749 }
750 return true;
751 }
752  
753 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
754 {
755 string query = string.Empty;
756  
757 query += "UPDATE usersettings SET ";
758 query += "imviaemail=:ImViaEmail, ";
759 query += "visible=:Visible ";
760 query += "WHERE useruuid=:uuid";
761  
762 try
763 {
764 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
765 {
766 cmd.CommandText = query;
767 cmd.Parameters.AddWithValue(":ImViaEmail", pref.IMViaEmail);
768 cmd.Parameters.AddWithValue(":Visible", pref.Visible);
769 cmd.Parameters.AddWithValue(":uuid", pref.UserId.ToString());
770  
771 cmd.ExecuteNonQuery();
772 }
773 }
774 catch (Exception e)
775 {
776 m_log.DebugFormat("[PROFILES_DATA]" +
777 ": AgentInterestsUpdate exception {0}", e.Message);
778 result = e.Message;
779 return false;
780 }
781 return true;
782 }
783  
784 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
785 {
786 IDataReader reader = null;
787 string query = string.Empty;
788  
789 query += "SELECT imviaemail,visible,email FROM ";
790 query += "usersettings WHERE ";
791 query += "useruuid = :Id";
792  
793 OSDArray data = new OSDArray();
794  
795 try
796 {
797 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
798 {
799 cmd.CommandText = query;
800 cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString());
801  
802 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
803 {
804 if(reader.Read())
805 {
806 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
807 bool.TryParse((string)reader["visible"], out pref.Visible);
808 pref.EMail = (string)reader["email"];
809 }
810 else
811 {
812 query = "INSERT INTO usersettings VALUES ";
813 query += "(:Id,'false','false', :Email)";
814  
815 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
816 {
817 put.Parameters.AddWithValue(":Id", pref.UserId.ToString());
818 put.Parameters.AddWithValue(":Email", pref.EMail);
819 put.ExecuteNonQuery();
820  
821 }
822 }
823 }
824 }
825 }
826 catch (Exception e)
827 {
828 m_log.DebugFormat("[PROFILES_DATA]" +
829 ": Get preferences exception {0}", e.Message);
830 result = e.Message;
831 return false;
832 }
833 return true;
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