aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite
diff options
context:
space:
mode:
authorMelanie2013-06-11 01:03:15 +0100
committerMelanie2013-06-11 01:03:15 +0100
commita022ee576639c1bbceb5c4ba8de3cbd82e538bb3 (patch)
treefb0d2ee165b7c87e814fc35c6551a26c2c6a488a /OpenSim/Data/SQLite
parentMerge branch 'master' into careminster (diff)
parentCheck For NaN and Infinity in llRot2Axis/Angle Fixes mantis #6669 (diff)
downloadopensim-SC-a022ee576639c1bbceb5c4ba8de3cbd82e538bb3.zip
opensim-SC-a022ee576639c1bbceb5c4ba8de3cbd82e538bb3.tar.gz
opensim-SC-a022ee576639c1bbceb5c4ba8de3cbd82e538bb3.tar.bz2
opensim-SC-a022ee576639c1bbceb5c4ba8de3cbd82e538bb3.tar.xz
Merge branch 'master' into careminster
Conflicts: OpenSim/Capabilities/Handlers/GetTexture/GetTextureHandler.cs OpenSim/Framework/Servers/HttpServer/BaseHttpServer.cs OpenSim/Services/Connectors/Asset/AssetServicesConnector.cs
Diffstat (limited to 'OpenSim/Data/SQLite')
-rw-r--r--OpenSim/Data/SQLite/Resources/UserProfiles.migrations90
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserProfilesData.cs904
2 files changed, 994 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLite/Resources/UserProfiles.migrations b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
new file mode 100644
index 0000000..16581f6
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
@@ -0,0 +1,90 @@
1:VERSION 1 # -------------------------------
2
3begin;
4
5CREATE TABLE IF NOT EXISTS classifieds (
6 classifieduuid char(36) NOT NULL PRIMARY KEY,
7 creatoruuid char(36) NOT NULL,
8 creationdate int(20) NOT NULL,
9 expirationdate int(20) NOT NULL,
10 category varchar(20) NOT NULL,
11 name varchar(255) NOT NULL,
12 description text NOT NULL,
13 parceluuid char(36) NOT NULL,
14 parentestate int(11) NOT NULL,
15 snapshotuuid char(36) NOT NULL,
16 simname varchar(255) NOT NULL,
17 posglobal varchar(255) NOT NULL,
18 parcelname varchar(255) NOT NULL,
19 classifiedflags int(8) NOT NULL,
20 priceforlisting int(5) NOT NULL
21);
22
23commit;
24
25begin;
26
27CREATE TABLE IF NOT EXISTS usernotes (
28 useruuid varchar(36) NOT NULL,
29 targetuuid varchar(36) NOT NULL,
30 notes text NOT NULL,
31 UNIQUE (useruuid,targetuuid) ON CONFLICT REPLACE
32);
33
34commit;
35
36begin;
37
38CREATE TABLE IF NOT EXISTS userpicks (
39 pickuuid varchar(36) NOT NULL PRIMARY KEY,
40 creatoruuid varchar(36) NOT NULL,
41 toppick int NOT NULL,
42 parceluuid varchar(36) NOT NULL,
43 name varchar(255) NOT NULL,
44 description text NOT NULL,
45 snapshotuuid varchar(36) NOT NULL,
46 user varchar(255) NOT NULL,
47 originalname varchar(255) NOT NULL,
48 simname varchar(255) NOT NULL,
49 posglobal varchar(255) NOT NULL,
50 sortorder int(2) NOT NULL,
51 enabled int NOT NULL
52);
53
54commit;
55
56begin;
57
58CREATE TABLE IF NOT EXISTS userprofile (
59 useruuid varchar(36) NOT NULL PRIMARY KEY,
60 profilePartner varchar(36) NOT NULL,
61 profileAllowPublish binary(1) NOT NULL,
62 profileMaturePublish binary(1) NOT NULL,
63 profileURL varchar(255) NOT NULL,
64 profileWantToMask int(3) NOT NULL,
65 profileWantToText text NOT NULL,
66 profileSkillsMask int(3) NOT NULL,
67 profileSkillsText text NOT NULL,
68 profileLanguages text NOT NULL,
69 profileImage varchar(36) NOT NULL,
70 profileAboutText text NOT NULL,
71 profileFirstImage varchar(36) NOT NULL,
72 profileFirstText text NOT NULL
73);
74
75commit;
76
77:VERSION 2 # -------------------------------
78
79begin;
80
81CREATE TABLE IF NOT EXISTS userdata (
82 UserId char(36) NOT NULL,
83 TagId varchar(64) NOT NULL,
84 DataKey varchar(255),
85 DataVal varchar(255),
86 PRIMARY KEY (UserId,TagId)
87);
88
89commit;
90
diff --git a/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
new file mode 100644
index 0000000..cc1dac1
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
@@ -0,0 +1,904 @@
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 += "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 public bool GetUserAppData(ref UserAppData props, ref string result)
753 {
754 IDataReader reader = null;
755 string query = string.Empty;
756
757 query += "SELECT * FROM `userdata` WHERE ";
758 query += "UserId = :Id AND ";
759 query += "TagId = :TagId";
760
761 try
762 {
763 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
764 {
765 cmd.CommandText = query;
766 cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
767 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
768
769 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
770 {
771 if(reader.Read())
772 {
773 props.DataKey = (string)reader["DataKey"];
774 props.DataVal = (string)reader["DataVal"];
775 }
776 else
777 {
778 query += "INSERT INTO userdata VALUES ( ";
779 query += ":UserId,";
780 query += ":TagId,";
781 query += ":DataKey,";
782 query += ":DataVal) ";
783
784 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
785 {
786 put.Parameters.AddWithValue(":Id", props.UserId.ToString());
787 put.Parameters.AddWithValue(":TagId", props.TagId.ToString());
788 put.Parameters.AddWithValue(":DataKey", props.DataKey.ToString());
789 put.Parameters.AddWithValue(":DataVal", props.DataVal.ToString());
790
791 put.ExecuteNonQuery();
792 }
793 }
794 }
795 }
796 }
797 catch (Exception e)
798 {
799 m_log.DebugFormat("[PROFILES_DATA]" +
800 ": Requst application data exception {0}", e.Message);
801 result = e.Message;
802 return false;
803 }
804 return true;
805 }
806 public bool SetUserAppData(UserAppData props, ref string result)
807 {
808 string query = string.Empty;
809
810 query += "UPDATE userdata SET ";
811 query += "TagId = :TagId, ";
812 query += "DataKey = :DataKey, ";
813 query += "DataVal = :DataVal WHERE ";
814 query += "UserId = :UserId AND ";
815 query += "TagId = :TagId";
816
817 try
818 {
819 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
820 {
821 cmd.CommandText = query;
822 cmd.Parameters.AddWithValue(":UserId", props.UserId.ToString());
823 cmd.Parameters.AddWithValue(":TagId", props.TagId.ToString ());
824 cmd.Parameters.AddWithValue(":DataKey", props.DataKey.ToString ());
825 cmd.Parameters.AddWithValue(":DataVal", props.DataKey.ToString ());
826
827 cmd.ExecuteNonQuery();
828 }
829 }
830 catch (Exception e)
831 {
832 m_log.DebugFormat("[PROFILES_DATA]" +
833 ": SetUserData exception {0}", e.Message);
834 return false;
835 }
836 return true;
837 }
838 public OSDArray GetUserImageAssets(UUID avatarId)
839 {
840 IDataReader reader = null;
841 OSDArray data = new OSDArray();
842 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = :Id";
843
844 // Get classified image assets
845
846
847 try
848 {
849 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
850 {
851 cmd.CommandText = query;
852 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
853
854 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
855 {
856 while(reader.Read())
857 {
858 data.Add(new OSDString((string)reader["snapshotuuid"].ToString()));
859 }
860 }
861 }
862
863 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
864 {
865 cmd.CommandText = query;
866 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
867
868 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
869 {
870 if(reader.Read())
871 {
872 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
873 }
874 }
875 }
876
877 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = :Id";
878
879 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
880 {
881 cmd.CommandText = query;
882 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
883
884 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
885 {
886 if(reader.Read())
887 {
888 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
889 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
890 }
891 }
892 }
893 }
894 catch (Exception e)
895 {
896 m_log.DebugFormat("[PROFILES_DATA]" +
897 ": GetAvatarNotes exception {0}", e.Message);
898 }
899 return data;
900 }
901 #endregion
902 }
903}
904
generated by cgit v1.1 at 2024-12-13 21:34:40 +0000