SQLiteUserProfilesData.cs 41 KB

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