diff options
author | BlueWall | 2013-05-13 22:11:28 -0400 |
---|---|---|
committer | BlueWall | 2013-05-30 17:59:18 -0400 |
commit | 328883700a15e4216bf7b251ac099d38f413375e (patch) | |
tree | fc90ce9fe8f7b1c5caca393144eac5b2824a9f3a /OpenSim/Data/MySQL/MySQLUserProfilesData.cs | |
parent | minor: fix warnings in GodsModule that were due to duplicate using statements (diff) | |
download | opensim-SC-328883700a15e4216bf7b251ac099d38f413375e.zip opensim-SC-328883700a15e4216bf7b251ac099d38f413375e.tar.gz opensim-SC-328883700a15e4216bf7b251ac099d38f413375e.tar.bz2 opensim-SC-328883700a15e4216bf7b251ac099d38f413375e.tar.xz |
UserProfiles
UserProfiles for Robust and Standalone. Includes service and connectors for Robust and standalone opensim plus matching region module.
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/MySQL/MySQLUserProfilesData.cs | 1023 |
1 files changed, 1023 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs new file mode 100644 index 0000000..09bd448 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs | |||
@@ -0,0 +1,1023 @@ | |||
1 | using System; | ||
2 | using System.Data; | ||
3 | using System.Reflection; | ||
4 | using OpenSim.Data; | ||
5 | using OpenSim.Framework; | ||
6 | using MySql.Data.MySqlClient; | ||
7 | using OpenMetaverse; | ||
8 | using OpenMetaverse.StructuredData; | ||
9 | using log4net; | ||
10 | |||
11 | namespace OpenSim.Data.MySQL | ||
12 | { | ||
13 | public class UserProfilesData: IProfilesData | ||
14 | { | ||
15 | static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); | ||
16 | |||
17 | #region Properites | ||
18 | string ConnectionString | ||
19 | { | ||
20 | get; set; | ||
21 | } | ||
22 | |||
23 | protected object Lock | ||
24 | { | ||
25 | get; set; | ||
26 | } | ||
27 | |||
28 | protected virtual Assembly Assembly | ||
29 | { | ||
30 | get { return GetType().Assembly; } | ||
31 | } | ||
32 | |||
33 | #endregion Properties | ||
34 | |||
35 | #region class Member Functions | ||
36 | public UserProfilesData(string connectionString) | ||
37 | { | ||
38 | ConnectionString = connectionString; | ||
39 | Init(); | ||
40 | } | ||
41 | |||
42 | void Init() | ||
43 | { | ||
44 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
45 | { | ||
46 | dbcon.Open(); | ||
47 | |||
48 | Migration m = new Migration(dbcon, Assembly, "UserProfiles"); | ||
49 | m.Update(); | ||
50 | } | ||
51 | } | ||
52 | #endregion Member Functions | ||
53 | |||
54 | #region Classifieds Queries | ||
55 | /// <summary> | ||
56 | /// Gets the classified records. | ||
57 | /// </summary> | ||
58 | /// <returns> | ||
59 | /// Array of classified records | ||
60 | /// </returns> | ||
61 | /// <param name='creatorId'> | ||
62 | /// Creator identifier. | ||
63 | /// </param> | ||
64 | public OSDArray GetClassifiedRecords(UUID creatorId) | ||
65 | { | ||
66 | OSDArray data = new OSDArray(); | ||
67 | |||
68 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
69 | { | ||
70 | string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = ?Id"; | ||
71 | dbcon.Open(); | ||
72 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
73 | { | ||
74 | cmd.Parameters.AddWithValue("?Id", creatorId); | ||
75 | using( MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) | ||
76 | { | ||
77 | if(reader.HasRows) | ||
78 | { | ||
79 | while (reader.Read()) | ||
80 | { | ||
81 | OSDMap n = new OSDMap(); | ||
82 | UUID Id = UUID.Zero; | ||
83 | |||
84 | string Name = null; | ||
85 | try | ||
86 | { | ||
87 | UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id); | ||
88 | Name = Convert.ToString(reader["name"]); | ||
89 | } | ||
90 | catch (Exception e) | ||
91 | { | ||
92 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
93 | ": UserAccount exception {0}", e.Message); | ||
94 | } | ||
95 | n.Add("classifieduuid", OSD.FromUUID(Id)); | ||
96 | n.Add("name", OSD.FromString(Name)); | ||
97 | data.Add(n); | ||
98 | } | ||
99 | } | ||
100 | } | ||
101 | } | ||
102 | } | ||
103 | return data; | ||
104 | } | ||
105 | |||
106 | public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) | ||
107 | { | ||
108 | string query = string.Empty; | ||
109 | |||
110 | |||
111 | query += "INSERT INTO classifieds ("; | ||
112 | query += "`classifieduuid`,"; | ||
113 | query += "`creatoruuid`,"; | ||
114 | query += "`creationdate`,"; | ||
115 | query += "`expirationdate`,"; | ||
116 | query += "`category`,"; | ||
117 | query += "`name`,"; | ||
118 | query += "`description`,"; | ||
119 | query += "`parceluuid`,"; | ||
120 | query += "`parentestate`,"; | ||
121 | query += "`snapshotuuid`,"; | ||
122 | query += "`simname`,"; | ||
123 | query += "`posglobal`,"; | ||
124 | query += "`parcelname`,"; | ||
125 | query += "`classifiedflags`,"; | ||
126 | query += "`priceforlisting`) "; | ||
127 | query += "VALUES ("; | ||
128 | query += "?ClassifiedId,"; | ||
129 | query += "?CreatorId,"; | ||
130 | query += "?CreatedDate,"; | ||
131 | query += "?ExpirationDate,"; | ||
132 | query += "?Category,"; | ||
133 | query += "?Name,"; | ||
134 | query += "?Description,"; | ||
135 | query += "?ParcelId,"; | ||
136 | query += "?ParentEstate,"; | ||
137 | query += "?SnapshotId,"; | ||
138 | query += "?SimName,"; | ||
139 | query += "?GlobalPos,"; | ||
140 | query += "?ParcelName,"; | ||
141 | query += "?Flags,"; | ||
142 | query += "?ListingPrice ) "; | ||
143 | query += "ON DUPLICATE KEY UPDATE "; | ||
144 | query += "category=?Category, "; | ||
145 | query += "expirationdate=?ExpirationDate, "; | ||
146 | query += "name=?Name, "; | ||
147 | query += "description=?Description, "; | ||
148 | query += "parentestate=?ParentEstate, "; | ||
149 | query += "posglobal=?GlobalPos, "; | ||
150 | query += "parcelname=?ParcelName, "; | ||
151 | query += "classifiedflags=?Flags, "; | ||
152 | query += "priceforlisting=?ListingPrice, "; | ||
153 | query += "snapshotuuid=?SnapshotId"; | ||
154 | |||
155 | if(string.IsNullOrEmpty(ad.ParcelName)) | ||
156 | ad.ParcelName = "Unknown"; | ||
157 | if(ad.ParcelId == null) | ||
158 | ad.ParcelId = UUID.Zero; | ||
159 | if(string.IsNullOrEmpty(ad.Description)) | ||
160 | ad.Description = "No Description"; | ||
161 | |||
162 | DateTime epoch = new DateTime(1970, 1, 1); | ||
163 | DateTime now = DateTime.Now; | ||
164 | TimeSpan epochnow = now - epoch; | ||
165 | TimeSpan duration; | ||
166 | DateTime expiration; | ||
167 | TimeSpan epochexp; | ||
168 | |||
169 | if(ad.Flags == 2) | ||
170 | { | ||
171 | duration = new TimeSpan(7,0,0,0); | ||
172 | expiration = now.Add(duration); | ||
173 | epochexp = expiration - epoch; | ||
174 | } | ||
175 | else | ||
176 | { | ||
177 | duration = new TimeSpan(365,0,0,0); | ||
178 | expiration = now.Add(duration); | ||
179 | epochexp = expiration - epoch; | ||
180 | } | ||
181 | ad.CreationDate = (int)epochnow.TotalSeconds; | ||
182 | ad.ExpirationDate = (int)epochexp.TotalSeconds; | ||
183 | |||
184 | try | ||
185 | { | ||
186 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
187 | { | ||
188 | dbcon.Open(); | ||
189 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
190 | { | ||
191 | cmd.Parameters.AddWithValue("?ClassifiedId", ad.ClassifiedId.ToString()); | ||
192 | cmd.Parameters.AddWithValue("?CreatorId", ad.CreatorId.ToString()); | ||
193 | cmd.Parameters.AddWithValue("?CreatedDate", ad.CreationDate.ToString()); | ||
194 | cmd.Parameters.AddWithValue("?ExpirationDate", ad.ExpirationDate.ToString()); | ||
195 | cmd.Parameters.AddWithValue("?Category", ad.Category.ToString()); | ||
196 | cmd.Parameters.AddWithValue("?Name", ad.Name.ToString()); | ||
197 | cmd.Parameters.AddWithValue("?Description", ad.Description.ToString()); | ||
198 | cmd.Parameters.AddWithValue("?ParcelId", ad.ParcelId.ToString()); | ||
199 | cmd.Parameters.AddWithValue("?ParentEstate", ad.ParentEstate.ToString()); | ||
200 | cmd.Parameters.AddWithValue("?SnapshotId", ad.SnapshotId.ToString ()); | ||
201 | cmd.Parameters.AddWithValue("?SimName", ad.SimName.ToString()); | ||
202 | cmd.Parameters.AddWithValue("?GlobalPos", ad.GlobalPos.ToString()); | ||
203 | cmd.Parameters.AddWithValue("?ParcelName", ad.ParcelName.ToString()); | ||
204 | cmd.Parameters.AddWithValue("?Flags", ad.Flags.ToString()); | ||
205 | cmd.Parameters.AddWithValue("?ListingPrice", ad.Price.ToString ()); | ||
206 | |||
207 | cmd.ExecuteNonQuery(); | ||
208 | } | ||
209 | } | ||
210 | } | ||
211 | catch (Exception e) | ||
212 | { | ||
213 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
214 | ": ClassifiedesUpdate exception {0}", e.Message); | ||
215 | result = e.Message; | ||
216 | return false; | ||
217 | } | ||
218 | return true; | ||
219 | } | ||
220 | |||
221 | public bool DeleteClassifiedRecord(UUID recordId) | ||
222 | { | ||
223 | string query = string.Empty; | ||
224 | |||
225 | query += "DELETE FROM classifieds WHERE "; | ||
226 | query += "classifieduuid = ?ClasifiedId"; | ||
227 | |||
228 | try | ||
229 | { | ||
230 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
231 | { | ||
232 | dbcon.Open(); | ||
233 | |||
234 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
235 | { | ||
236 | cmd.Parameters.AddWithValue("?ClassifiedId", recordId.ToString()); | ||
237 | |||
238 | lock(Lock) | ||
239 | { | ||
240 | cmd.ExecuteNonQuery(); | ||
241 | } | ||
242 | } | ||
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 | string query = string.Empty; | ||
257 | |||
258 | query += "SELECT * FROM classifieds WHERE "; | ||
259 | query += "classifieduuid = ?AdId"; | ||
260 | |||
261 | try | ||
262 | { | ||
263 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
264 | { | ||
265 | dbcon.Open(); | ||
266 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
267 | { | ||
268 | cmd.Parameters.AddWithValue("?AdId", ad.ClassifiedId.ToString()); | ||
269 | |||
270 | using (MySqlDataReader reader = cmd.ExecuteReader()) | ||
271 | { | ||
272 | if(reader.Read ()) | ||
273 | { | ||
274 | ad.CreatorId = new UUID(reader.GetGuid("creatoruuid")); | ||
275 | ad.ParcelId = new UUID(reader.GetGuid("parceluuid")); | ||
276 | ad.SnapshotId = new UUID(reader.GetGuid("snapshotuuid")); | ||
277 | ad.CreationDate = Convert.ToInt32(reader["creationdate"]); | ||
278 | ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); | ||
279 | ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); | ||
280 | ad.Flags = (byte)reader.GetUInt32("classifiedflags"); | ||
281 | ad.Category = reader.GetInt32("category"); | ||
282 | ad.Price = reader.GetInt16("priceforlisting"); | ||
283 | ad.Name = reader.GetString("name"); | ||
284 | ad.Description = reader.GetString("description"); | ||
285 | ad.SimName = reader.GetString("simname"); | ||
286 | ad.GlobalPos = reader.GetString("posglobal"); | ||
287 | ad.ParcelName = reader.GetString("parcelname"); | ||
288 | |||
289 | } | ||
290 | } | ||
291 | } | ||
292 | dbcon.Close(); | ||
293 | } | ||
294 | } | ||
295 | catch (Exception e) | ||
296 | { | ||
297 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
298 | ": GetPickInfo exception {0}", e.Message); | ||
299 | } | ||
300 | return true; | ||
301 | } | ||
302 | #endregion Classifieds Queries | ||
303 | |||
304 | #region Picks Queries | ||
305 | public OSDArray GetAvatarPicks(UUID avatarId) | ||
306 | { | ||
307 | string query = string.Empty; | ||
308 | |||
309 | query += "SELECT `pickuuid`,`name` FROM userpicks WHERE "; | ||
310 | query += "creatoruuid = ?Id"; | ||
311 | OSDArray data = new OSDArray(); | ||
312 | |||
313 | try | ||
314 | { | ||
315 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
316 | { | ||
317 | dbcon.Open(); | ||
318 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
319 | { | ||
320 | cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); | ||
321 | |||
322 | using (MySqlDataReader reader = cmd.ExecuteReader()) | ||
323 | { | ||
324 | if(reader.HasRows) | ||
325 | { | ||
326 | while (reader.Read()) | ||
327 | { | ||
328 | OSDMap record = new OSDMap(); | ||
329 | |||
330 | record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"])); | ||
331 | record.Add("name",OSD.FromString((string)reader["name"])); | ||
332 | data.Add(record); | ||
333 | } | ||
334 | } | ||
335 | } | ||
336 | } | ||
337 | } | ||
338 | } | ||
339 | catch (Exception e) | ||
340 | { | ||
341 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
342 | ": GetAvatarPicks exception {0}", e.Message); | ||
343 | } | ||
344 | return data; | ||
345 | } | ||
346 | |||
347 | public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId) | ||
348 | { | ||
349 | string query = string.Empty; | ||
350 | UserProfilePick pick = new UserProfilePick(); | ||
351 | |||
352 | query += "SELECT * FROM userpicks WHERE "; | ||
353 | query += "creatoruuid = ?CreatorId AND "; | ||
354 | query += "pickuuid = ?PickId"; | ||
355 | |||
356 | try | ||
357 | { | ||
358 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
359 | { | ||
360 | dbcon.Open(); | ||
361 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
362 | { | ||
363 | cmd.Parameters.AddWithValue("?CreatorId", avatarId.ToString()); | ||
364 | cmd.Parameters.AddWithValue("?PickId", pickId.ToString()); | ||
365 | |||
366 | using (MySqlDataReader reader = cmd.ExecuteReader()) | ||
367 | { | ||
368 | if(reader.HasRows) | ||
369 | { | ||
370 | reader.Read(); | ||
371 | |||
372 | string description = (string)reader["description"]; | ||
373 | |||
374 | if (string.IsNullOrEmpty(description)) | ||
375 | description = "No description given."; | ||
376 | |||
377 | UUID.TryParse((string)reader["pickuuid"], out pick.PickId); | ||
378 | UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId); | ||
379 | UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId); | ||
380 | UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId); | ||
381 | pick.GlobalPos = (string)reader["posglobal"]; | ||
382 | bool.TryParse((string)reader["toppick"], out pick.TopPick); | ||
383 | bool.TryParse((string)reader["enabled"], out pick.Enabled); | ||
384 | pick.Name = (string)reader["name"]; | ||
385 | pick.Desc = description; | ||
386 | pick.User = (string)reader["user"]; | ||
387 | pick.OriginalName = (string)reader["originalname"]; | ||
388 | pick.SimName = (string)reader["simname"]; | ||
389 | pick.SortOrder = (int)reader["sortorder"]; | ||
390 | } | ||
391 | } | ||
392 | } | ||
393 | dbcon.Close(); | ||
394 | } | ||
395 | } | ||
396 | catch (Exception e) | ||
397 | { | ||
398 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
399 | ": GetPickInfo exception {0}", e.Message); | ||
400 | } | ||
401 | return pick; | ||
402 | } | ||
403 | |||
404 | public bool UpdatePicksRecord(UserProfilePick pick) | ||
405 | { | ||
406 | string query = string.Empty; | ||
407 | |||
408 | query += "INSERT INTO userpicks VALUES ("; | ||
409 | query += "?PickId,"; | ||
410 | query += "?CreatorId,"; | ||
411 | query += "?TopPick,"; | ||
412 | query += "?ParcelId,"; | ||
413 | query += "?Name,"; | ||
414 | query += "?Desc,"; | ||
415 | query += "?SnapshotId,"; | ||
416 | query += "?User,"; | ||
417 | query += "?Original,"; | ||
418 | query += "?SimName,"; | ||
419 | query += "?GlobalPos,"; | ||
420 | query += "?SortOrder,"; | ||
421 | query += "?Enabled) "; | ||
422 | query += "ON DUPLICATE KEY UPDATE "; | ||
423 | query += "parceluuid=?ParcelId,"; | ||
424 | query += "name=?Name,"; | ||
425 | query += "description=?Desc,"; | ||
426 | query += "snapshotuuid=?SnapshotId,"; | ||
427 | query += "pickuuid=?PickId,"; | ||
428 | query += "posglobal=?GlobalPos"; | ||
429 | |||
430 | try | ||
431 | { | ||
432 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
433 | { | ||
434 | dbcon.Open(); | ||
435 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
436 | { | ||
437 | cmd.Parameters.AddWithValue("?PickId", pick.PickId.ToString()); | ||
438 | cmd.Parameters.AddWithValue("?CreatorId", pick.CreatorId.ToString()); | ||
439 | cmd.Parameters.AddWithValue("?TopPick", pick.TopPick.ToString()); | ||
440 | cmd.Parameters.AddWithValue("?ParcelId", pick.ParcelId.ToString()); | ||
441 | cmd.Parameters.AddWithValue("?Name", pick.Name.ToString()); | ||
442 | cmd.Parameters.AddWithValue("?Desc", pick.Desc.ToString()); | ||
443 | cmd.Parameters.AddWithValue("?SnapshotId", pick.SnapshotId.ToString()); | ||
444 | cmd.Parameters.AddWithValue("?User", pick.User.ToString()); | ||
445 | cmd.Parameters.AddWithValue("?Original", pick.OriginalName.ToString()); | ||
446 | cmd.Parameters.AddWithValue("?SimName",pick.SimName.ToString()); | ||
447 | cmd.Parameters.AddWithValue("?GlobalPos", pick.GlobalPos); | ||
448 | cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ()); | ||
449 | cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString()); | ||
450 | |||
451 | cmd.ExecuteNonQuery(); | ||
452 | } | ||
453 | } | ||
454 | } | ||
455 | catch (Exception e) | ||
456 | { | ||
457 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
458 | ": UpdateAvatarNotes exception {0}", e.Message); | ||
459 | return false; | ||
460 | } | ||
461 | return true; | ||
462 | } | ||
463 | |||
464 | public bool DeletePicksRecord(UUID pickId) | ||
465 | { | ||
466 | string query = string.Empty; | ||
467 | |||
468 | query += "DELETE FROM userpicks WHERE "; | ||
469 | query += "pickuuid = ?PickId"; | ||
470 | |||
471 | try | ||
472 | { | ||
473 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
474 | { | ||
475 | dbcon.Open(); | ||
476 | |||
477 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
478 | { | ||
479 | cmd.Parameters.AddWithValue("?PickId", pickId.ToString()); | ||
480 | |||
481 | cmd.ExecuteNonQuery(); | ||
482 | } | ||
483 | } | ||
484 | } | ||
485 | catch (Exception e) | ||
486 | { | ||
487 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
488 | ": DeleteUserPickRecord exception {0}", e.Message); | ||
489 | return false; | ||
490 | } | ||
491 | return true; | ||
492 | } | ||
493 | #endregion Picks Queries | ||
494 | |||
495 | #region Avatar Notes Queries | ||
496 | public bool GetAvatarNotes(ref UserProfileNotes notes) | ||
497 | { // WIP | ||
498 | string query = string.Empty; | ||
499 | |||
500 | query += "SELECT `notes` FROM usernotes WHERE "; | ||
501 | query += "useruuid = ?Id AND "; | ||
502 | query += "targetuuid = ?TargetId"; | ||
503 | OSDArray data = new OSDArray(); | ||
504 | |||
505 | try | ||
506 | { | ||
507 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
508 | { | ||
509 | dbcon.Open(); | ||
510 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
511 | { | ||
512 | cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString()); | ||
513 | cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString()); | ||
514 | |||
515 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
516 | { | ||
517 | if(reader.HasRows) | ||
518 | { | ||
519 | reader.Read(); | ||
520 | notes.Notes = OSD.FromString((string)reader["notes"]); | ||
521 | } | ||
522 | } | ||
523 | } | ||
524 | } | ||
525 | } | ||
526 | catch (Exception e) | ||
527 | { | ||
528 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
529 | ": GetAvatarNotes exception {0}", e.Message); | ||
530 | } | ||
531 | return true; | ||
532 | } | ||
533 | |||
534 | public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result) | ||
535 | { | ||
536 | string query = string.Empty; | ||
537 | bool remove; | ||
538 | |||
539 | if(string.IsNullOrEmpty(note.Notes)) | ||
540 | { | ||
541 | remove = true; | ||
542 | query += "DELETE FROM usernotes WHERE "; | ||
543 | query += "useruuid=?UserId AND "; | ||
544 | query += "targetuuid=?TargetId"; | ||
545 | } | ||
546 | else | ||
547 | { | ||
548 | remove = false; | ||
549 | query += "INSERT INTO usernotes VALUES ( "; | ||
550 | query += "?UserId,"; | ||
551 | query += "?TargetId,"; | ||
552 | query += "?Notes )"; | ||
553 | query += "ON DUPLICATE KEY "; | ||
554 | query += "UPDATE "; | ||
555 | query += "notes=?Notes"; | ||
556 | } | ||
557 | |||
558 | try | ||
559 | { | ||
560 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
561 | { | ||
562 | dbcon.Open(); | ||
563 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
564 | { | ||
565 | if(!remove) | ||
566 | cmd.Parameters.AddWithValue("?Notes", note.Notes); | ||
567 | cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ()); | ||
568 | cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString()); | ||
569 | |||
570 | cmd.ExecuteNonQuery(); | ||
571 | } | ||
572 | } | ||
573 | } | ||
574 | catch (Exception e) | ||
575 | { | ||
576 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
577 | ": UpdateAvatarNotes exception {0}", e.Message); | ||
578 | return false; | ||
579 | } | ||
580 | return true; | ||
581 | |||
582 | } | ||
583 | #endregion Avatar Notes Queries | ||
584 | |||
585 | #region Avatar Properties | ||
586 | public bool GetAvatarProperties(ref UserProfileProperties props, ref string result) | ||
587 | { | ||
588 | string query = string.Empty; | ||
589 | |||
590 | query += "SELECT * FROM userprofile WHERE "; | ||
591 | query += "useruuid = ?Id"; | ||
592 | |||
593 | try | ||
594 | { | ||
595 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
596 | { | ||
597 | dbcon.Open(); | ||
598 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
599 | { | ||
600 | cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); | ||
601 | |||
602 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
603 | { | ||
604 | if(reader.HasRows) | ||
605 | { | ||
606 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
607 | ": Getting data for {0}.", props.UserId); | ||
608 | reader.Read(); | ||
609 | props.WebUrl = (string)reader["profileURL"]; | ||
610 | UUID.TryParse((string)reader["profileImage"], out props.ImageId); | ||
611 | props.AboutText = (string)reader["profileAboutText"]; | ||
612 | UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId); | ||
613 | props.FirstLifeText = (string)reader["profileFirstText"]; | ||
614 | UUID.TryParse((string)reader["profilePartner"], out props.PartnerId); | ||
615 | props.WantToMask = (int)reader["profileWantToMask"]; | ||
616 | props.WantToText = (string)reader["profileWantToText"]; | ||
617 | props.SkillsMask = (int)reader["profileSkillsMask"]; | ||
618 | props.SkillsText = (string)reader["profileSkillsText"]; | ||
619 | props.Language = (string)reader["profileLanguages"]; | ||
620 | } | ||
621 | else | ||
622 | { | ||
623 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
624 | ": No data for {0}", props.UserId); | ||
625 | |||
626 | props.WebUrl = string.Empty; | ||
627 | props.ImageId = UUID.Zero; | ||
628 | props.AboutText = string.Empty; | ||
629 | props.FirstLifeImageId = UUID.Zero; | ||
630 | props.FirstLifeText = string.Empty; | ||
631 | props.PartnerId = UUID.Zero; | ||
632 | props.WantToMask = 0; | ||
633 | props.WantToText = string.Empty; | ||
634 | props.SkillsMask = 0; | ||
635 | props.SkillsText = string.Empty; | ||
636 | props.Language = string.Empty; | ||
637 | |||
638 | query = "INSERT INTO userprofile (`useruuid`) VALUES (?userId)"; | ||
639 | |||
640 | dbcon.Close(); | ||
641 | dbcon.Open(); | ||
642 | |||
643 | using (MySqlCommand put = new MySqlCommand(query, dbcon)) | ||
644 | { | ||
645 | put.Parameters.AddWithValue("?userId", props.UserId.ToString()); | ||
646 | put.ExecuteNonQuery(); | ||
647 | } | ||
648 | } | ||
649 | } | ||
650 | } | ||
651 | } | ||
652 | } | ||
653 | catch (Exception e) | ||
654 | { | ||
655 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
656 | ": Requst properties exception {0}", e.Message); | ||
657 | result = e.Message; | ||
658 | return false; | ||
659 | } | ||
660 | return true; | ||
661 | } | ||
662 | |||
663 | public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result) | ||
664 | { | ||
665 | string query = string.Empty; | ||
666 | |||
667 | query += "UPDATE userprofile SET "; | ||
668 | query += "profileURL=?profileURL, "; | ||
669 | query += "profileImage=?image, "; | ||
670 | query += "profileAboutText=?abouttext,"; | ||
671 | query += "profileFirstImage=?firstlifeimage,"; | ||
672 | query += "profileFirstText=?firstlifetext "; | ||
673 | query += "WHERE useruuid=?uuid"; | ||
674 | |||
675 | try | ||
676 | { | ||
677 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
678 | { | ||
679 | dbcon.Open(); | ||
680 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
681 | { | ||
682 | cmd.Parameters.AddWithValue("?profileURL", props.WebUrl); | ||
683 | cmd.Parameters.AddWithValue("?image", props.ImageId.ToString()); | ||
684 | cmd.Parameters.AddWithValue("?abouttext", props.AboutText); | ||
685 | cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString()); | ||
686 | cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText); | ||
687 | cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString()); | ||
688 | |||
689 | cmd.ExecuteNonQuery(); | ||
690 | } | ||
691 | } | ||
692 | } | ||
693 | catch (Exception e) | ||
694 | { | ||
695 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
696 | ": AgentPropertiesUpdate exception {0}", e.Message); | ||
697 | |||
698 | return false; | ||
699 | } | ||
700 | return true; | ||
701 | } | ||
702 | #endregion Avatar Properties | ||
703 | |||
704 | #region Avatar Interests | ||
705 | public bool UpdateAvatarInterests(UserProfileProperties up, ref string result) | ||
706 | { | ||
707 | string query = string.Empty; | ||
708 | |||
709 | query += "UPDATE userprofile SET "; | ||
710 | query += "profileWantToMask=?WantMask, "; | ||
711 | query += "profileWantToText=?WantText,"; | ||
712 | query += "profileSkillsMask=?SkillsMask,"; | ||
713 | query += "profileSkillsText=?SkillsText, "; | ||
714 | query += "profileLanguages=?Languages "; | ||
715 | query += "WHERE useruuid=?uuid"; | ||
716 | |||
717 | try | ||
718 | { | ||
719 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
720 | { | ||
721 | dbcon.Open(); | ||
722 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
723 | { | ||
724 | cmd.Parameters.AddWithValue("?WantMask", up.WantToMask); | ||
725 | cmd.Parameters.AddWithValue("?WantText", up.WantToText); | ||
726 | cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask); | ||
727 | cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText); | ||
728 | cmd.Parameters.AddWithValue("?Languages", up.Language); | ||
729 | cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString()); | ||
730 | |||
731 | cmd.ExecuteNonQuery(); | ||
732 | } | ||
733 | } | ||
734 | } | ||
735 | catch (Exception e) | ||
736 | { | ||
737 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
738 | ": AgentInterestsUpdate exception {0}", e.Message); | ||
739 | result = e.Message; | ||
740 | return false; | ||
741 | } | ||
742 | return true; | ||
743 | } | ||
744 | #endregion Avatar Interests | ||
745 | |||
746 | public OSDArray GetUserImageAssets(UUID avatarId) | ||
747 | { | ||
748 | OSDArray data = new OSDArray(); | ||
749 | string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id"; | ||
750 | |||
751 | // Get classified image assets | ||
752 | |||
753 | |||
754 | try | ||
755 | { | ||
756 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
757 | { | ||
758 | dbcon.Open(); | ||
759 | |||
760 | using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon)) | ||
761 | { | ||
762 | cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); | ||
763 | |||
764 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
765 | { | ||
766 | if(reader.HasRows) | ||
767 | { | ||
768 | while (reader.Read()) | ||
769 | { | ||
770 | data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); | ||
771 | } | ||
772 | } | ||
773 | } | ||
774 | } | ||
775 | |||
776 | dbcon.Close(); | ||
777 | dbcon.Open(); | ||
778 | |||
779 | using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) | ||
780 | { | ||
781 | cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); | ||
782 | |||
783 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
784 | { | ||
785 | if(reader.HasRows) | ||
786 | { | ||
787 | while (reader.Read()) | ||
788 | { | ||
789 | data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); | ||
790 | } | ||
791 | } | ||
792 | } | ||
793 | } | ||
794 | |||
795 | dbcon.Close(); | ||
796 | dbcon.Open(); | ||
797 | |||
798 | query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id"; | ||
799 | |||
800 | using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) | ||
801 | { | ||
802 | cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); | ||
803 | |||
804 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
805 | { | ||
806 | if(reader.HasRows) | ||
807 | { | ||
808 | while (reader.Read()) | ||
809 | { | ||
810 | data.Add(new OSDString((string)reader["profileImage"].ToString ())); | ||
811 | data.Add(new OSDString((string)reader["profileFirstImage"].ToString ())); | ||
812 | } | ||
813 | } | ||
814 | } | ||
815 | } | ||
816 | } | ||
817 | } | ||
818 | catch (Exception e) | ||
819 | { | ||
820 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
821 | ": GetAvatarNotes exception {0}", e.Message); | ||
822 | } | ||
823 | return data; | ||
824 | } | ||
825 | |||
826 | #region User Preferences | ||
827 | public OSDArray GetUserPreferences(UUID avatarId) | ||
828 | { | ||
829 | string query = string.Empty; | ||
830 | |||
831 | query += "SELECT imviaemail,visible,email FROM "; | ||
832 | query += "usersettings WHERE "; | ||
833 | query += "useruuid = ?Id"; | ||
834 | |||
835 | OSDArray data = new OSDArray(); | ||
836 | |||
837 | try | ||
838 | { | ||
839 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
840 | { | ||
841 | dbcon.Open(); | ||
842 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
843 | { | ||
844 | cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); | ||
845 | |||
846 | using (MySqlDataReader reader = cmd.ExecuteReader()) | ||
847 | { | ||
848 | if(reader.HasRows) | ||
849 | { | ||
850 | reader.Read(); | ||
851 | OSDMap record = new OSDMap(); | ||
852 | |||
853 | record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"])); | ||
854 | record.Add("visible",OSD.FromString((string)reader["visible"])); | ||
855 | record.Add("email",OSD.FromString((string)reader["email"])); | ||
856 | data.Add(record); | ||
857 | } | ||
858 | else | ||
859 | { | ||
860 | using (MySqlCommand put = new MySqlCommand(query, dbcon)) | ||
861 | { | ||
862 | query = "INSERT INTO usersettings VALUES "; | ||
863 | query += "(?Id,'false','false', '')"; | ||
864 | |||
865 | lock(Lock) | ||
866 | { | ||
867 | put.ExecuteNonQuery(); | ||
868 | } | ||
869 | } | ||
870 | } | ||
871 | } | ||
872 | } | ||
873 | } | ||
874 | } | ||
875 | catch (Exception e) | ||
876 | { | ||
877 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
878 | ": Get preferences exception {0}", e.Message); | ||
879 | } | ||
880 | return data; | ||
881 | } | ||
882 | |||
883 | public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId ) | ||
884 | { | ||
885 | string query = string.Empty; | ||
886 | |||
887 | query += "UPDATE userpsettings SET "; | ||
888 | query += "imviaemail=?ImViaEmail, "; | ||
889 | query += "visible=?Visible,"; | ||
890 | query += "WHERE useruuid=?uuid"; | ||
891 | |||
892 | try | ||
893 | { | ||
894 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
895 | { | ||
896 | dbcon.Open(); | ||
897 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
898 | { | ||
899 | cmd.Parameters.AddWithValue("?ImViaEmail", emailIm.ToString().ToLower ()); | ||
900 | cmd.Parameters.AddWithValue("?WantText", visible.ToString().ToLower ()); | ||
901 | cmd.Parameters.AddWithValue("?uuid", avatarId.ToString()); | ||
902 | |||
903 | lock(Lock) | ||
904 | { | ||
905 | cmd.ExecuteNonQuery(); | ||
906 | } | ||
907 | } | ||
908 | } | ||
909 | } | ||
910 | catch (Exception e) | ||
911 | { | ||
912 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
913 | ": AgentInterestsUpdate exception {0}", e.Message); | ||
914 | return false; | ||
915 | } | ||
916 | return true; | ||
917 | } | ||
918 | #endregion User Preferences | ||
919 | |||
920 | #region Integration | ||
921 | public bool GetUserAppData(ref UserAppData props, ref string result) | ||
922 | { | ||
923 | string query = string.Empty; | ||
924 | |||
925 | query += "SELECT * FROM `userdata` WHERE "; | ||
926 | query += "UserId = ?Id AND "; | ||
927 | query += "TagId = ?TagId"; | ||
928 | |||
929 | try | ||
930 | { | ||
931 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
932 | { | ||
933 | dbcon.Open(); | ||
934 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
935 | { | ||
936 | cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); | ||
937 | cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString()); | ||
938 | |||
939 | using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | ||
940 | { | ||
941 | if(reader.HasRows) | ||
942 | { | ||
943 | reader.Read(); | ||
944 | props.DataKey = (string)reader["DataKey"]; | ||
945 | props.DataVal = (string)reader["DataVal"]; | ||
946 | } | ||
947 | else | ||
948 | { | ||
949 | query += "INSERT INTO userdata VALUES ( "; | ||
950 | query += "?UserId,"; | ||
951 | query += "?TagId,"; | ||
952 | query += "?DataKey,"; | ||
953 | query += "?DataVal) "; | ||
954 | |||
955 | using (MySqlCommand put = new MySqlCommand(query, dbcon)) | ||
956 | { | ||
957 | put.Parameters.AddWithValue("?Id", props.UserId.ToString()); | ||
958 | put.Parameters.AddWithValue("?TagId", props.TagId.ToString()); | ||
959 | put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString()); | ||
960 | put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString()); | ||
961 | |||
962 | lock(Lock) | ||
963 | { | ||
964 | put.ExecuteNonQuery(); | ||
965 | } | ||
966 | } | ||
967 | } | ||
968 | } | ||
969 | } | ||
970 | } | ||
971 | } | ||
972 | catch (Exception e) | ||
973 | { | ||
974 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
975 | ": Requst application data exception {0}", e.Message); | ||
976 | result = e.Message; | ||
977 | return false; | ||
978 | } | ||
979 | return true; | ||
980 | } | ||
981 | |||
982 | public bool SetUserAppData(UserAppData props, ref string result) | ||
983 | { | ||
984 | string query = string.Empty; | ||
985 | |||
986 | query += "UPDATE userdata SET "; | ||
987 | query += "TagId = ?TagId, "; | ||
988 | query += "DataKey = ?DataKey, "; | ||
989 | query += "DataVal = ?DataVal WHERE "; | ||
990 | query += "UserId = ?UserId AND "; | ||
991 | query += "TagId = ?TagId"; | ||
992 | |||
993 | try | ||
994 | { | ||
995 | using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) | ||
996 | { | ||
997 | dbcon.Open(); | ||
998 | using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) | ||
999 | { | ||
1000 | cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString()); | ||
1001 | cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ()); | ||
1002 | cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ()); | ||
1003 | cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ()); | ||
1004 | |||
1005 | lock(Lock) | ||
1006 | { | ||
1007 | cmd.ExecuteNonQuery(); | ||
1008 | } | ||
1009 | } | ||
1010 | } | ||
1011 | } | ||
1012 | catch (Exception e) | ||
1013 | { | ||
1014 | m_log.DebugFormat("[PROFILES_DATA]" + | ||
1015 | ": SetUserData exception {0}", e.Message); | ||
1016 | return false; | ||
1017 | } | ||
1018 | return true; | ||
1019 | } | ||
1020 | #endregion Integration | ||
1021 | } | ||
1022 | } | ||
1023 | |||