SQLiteUserProfilesData.cs 38 KB

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