1
0

PGSQLUserProfilesData.cs 47 KB

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