1
0

PGSQLUserProfilesData.cs 45 KB

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