diff options
author | BlueWall | 2013-06-07 20:14:39 -0400 |
---|---|---|
committer | BlueWall | 2013-06-07 20:14:39 -0400 |
commit | 1cb1245d847fad5a088e0bb727433ee1e53d81da (patch) | |
tree | 55ab2e5c7ab3d93e312f35cb38ac7d6fae365bce /OpenSim/Data/SQLite/SQLiteUserProfilesData.cs | |
parent | Ensure selected module is the only active one (diff) | |
download | opensim-SC_OLD-1cb1245d847fad5a088e0bb727433ee1e53d81da.zip opensim-SC_OLD-1cb1245d847fad5a088e0bb727433ee1e53d81da.tar.gz opensim-SC_OLD-1cb1245d847fad5a088e0bb727433ee1e53d81da.tar.bz2 opensim-SC_OLD-1cb1245d847fad5a088e0bb727433ee1e53d81da.tar.xz |
SQLite support for UserProfiles
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/SQLite/SQLiteUserProfilesData.cs | 904 |
1 files changed, 904 insertions, 0 deletions
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 | |||
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 | 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 | |||