1
0

MySQLUserProfilesData.cs 44 KB

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