SQLiteUserData.cs 53 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262
  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. using Mono.Data.SqliteClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. namespace OpenSim.Data.SQLite
  36. {
  37. /// <summary>
  38. /// A User storage interface for the SQLite database system
  39. /// </summary>
  40. public class SQLiteUserData : UserDataBase
  41. {
  42. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  43. /// <summary>
  44. /// The database manager
  45. /// </summary>
  46. /// <summary>
  47. /// Artificial constructor called upon plugin load
  48. /// </summary>
  49. private const string SelectUserByUUID = "select * from users where UUID=:UUID";
  50. private const string SelectUserByName = "select * from users where username=:username and surname=:surname";
  51. private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID";
  52. private const string userSelect = "select * from users";
  53. private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b";
  54. private const string userAgentSelect = "select * from useragents";
  55. private const string AvatarAppearanceSelect = "select * from avatarappearance";
  56. private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
  57. private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
  58. private DataSet ds;
  59. private SqliteDataAdapter da;
  60. private SqliteDataAdapter daf;
  61. private SqliteDataAdapter dua;
  62. private SqliteDataAdapter daa;
  63. SqliteConnection g_conn;
  64. public override void Initialise()
  65. {
  66. m_log.Info("[SQLiteUserData]: " + Name + " cannot be default-initialized!");
  67. throw new PluginNotInitialisedException (Name);
  68. }
  69. /// <summary>
  70. /// <list type="bullet">
  71. /// <item>Initialises User Interface</item>
  72. /// <item>Loads and initialises a new SQLite connection and maintains it.</item>
  73. /// <item>use default URI if connect string string is empty.</item>
  74. /// </list>
  75. /// </summary>
  76. /// <param name="connect">connect string</param>
  77. override public void Initialise(string connect)
  78. {
  79. // default to something sensible
  80. if (connect == "")
  81. connect = "URI=file:userprofiles.db,version=3";
  82. SqliteConnection conn = new SqliteConnection(connect);
  83. // This sucks, but It doesn't seem to work with the dataset Syncing :P
  84. g_conn = conn;
  85. g_conn.Open();
  86. Assembly assem = GetType().Assembly;
  87. Migration m = new Migration(g_conn, assem, "UserStore");
  88. m.Update();
  89. ds = new DataSet();
  90. da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
  91. dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn));
  92. daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
  93. daa = new SqliteDataAdapter(new SqliteCommand(AvatarAppearanceSelect, conn));
  94. //if (daa == null) m_log.Info("[SQLiteUserData]: daa = null");
  95. lock (ds)
  96. {
  97. ds.Tables.Add(createUsersTable());
  98. ds.Tables.Add(createUserAgentsTable());
  99. ds.Tables.Add(createUserFriendsTable());
  100. ds.Tables.Add(createAvatarAppearanceTable());
  101. setupUserCommands(da, conn);
  102. da.Fill(ds.Tables["users"]);
  103. setupAgentCommands(dua, conn);
  104. dua.Fill(ds.Tables["useragents"]);
  105. setupUserFriendsCommands(daf, conn);
  106. daf.Fill(ds.Tables["userfriends"]);
  107. setupAvatarAppearanceCommands(daa, conn);
  108. daa.Fill(ds.Tables["avatarappearance"]);
  109. }
  110. return;
  111. }
  112. public override void Dispose ()
  113. {
  114. if (g_conn != null)
  115. {
  116. g_conn.Close();
  117. g_conn = null;
  118. }
  119. if (ds != null)
  120. {
  121. ds.Dispose();
  122. ds = null;
  123. }
  124. if (da != null)
  125. {
  126. da.Dispose();
  127. da = null;
  128. }
  129. if (daf != null)
  130. {
  131. daf.Dispose();
  132. daf = null;
  133. }
  134. if (dua != null)
  135. {
  136. dua.Dispose();
  137. dua = null;
  138. }
  139. if (daa != null)
  140. {
  141. daa.Dispose();
  142. daa = null;
  143. }
  144. }
  145. /// <summary>
  146. /// see IUserDataPlugin,
  147. /// Get user data profile by UUID
  148. /// </summary>
  149. /// <param name="uuid">User UUID</param>
  150. /// <returns>user profile data</returns>
  151. override public UserProfileData GetUserByUUID(UUID uuid)
  152. {
  153. lock (ds)
  154. {
  155. DataRow row = ds.Tables["users"].Rows.Find(uuid.ToString());
  156. if (row != null)
  157. {
  158. UserProfileData user = buildUserProfile(row);
  159. return user;
  160. }
  161. else
  162. {
  163. return null;
  164. }
  165. }
  166. }
  167. /// <summary>
  168. /// see IUserDataPlugin,
  169. /// Get user data profile by name
  170. /// </summary>
  171. /// <param name="fname">first name</param>
  172. /// <param name="lname">last name</param>
  173. /// <returns>user profile data</returns>
  174. override public UserProfileData GetUserByName(string fname, string lname)
  175. {
  176. string select = "surname = '" + lname + "' and username = '" + fname + "'";
  177. lock (ds)
  178. {
  179. DataRow[] rows = ds.Tables["users"].Select(select);
  180. if (rows.Length > 0)
  181. {
  182. UserProfileData user = buildUserProfile(rows[0]);
  183. return user;
  184. }
  185. else
  186. {
  187. return null;
  188. }
  189. }
  190. }
  191. #region User Friends List Data
  192. private bool ExistsFriend(UUID owner, UUID friend)
  193. {
  194. string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
  195. using (SqliteCommand cmd = new SqliteCommand(FindFriends, g_conn))
  196. {
  197. cmd.Parameters.Add(new SqliteParameter(":ownerID", owner.ToString()));
  198. cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
  199. try
  200. {
  201. using (IDataReader reader = cmd.ExecuteReader())
  202. {
  203. if (reader.Read())
  204. {
  205. reader.Close();
  206. return true;
  207. }
  208. else
  209. {
  210. reader.Close();
  211. return false;
  212. }
  213. }
  214. }
  215. catch (Exception ex)
  216. {
  217. m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
  218. return false;
  219. }
  220. }
  221. }
  222. /// <summary>
  223. /// Add a new friend in the friendlist
  224. /// </summary>
  225. /// <param name="friendlistowner">UUID of the friendlist owner</param>
  226. /// <param name="friend">UUID of the friend to add</param>
  227. /// <param name="perms">permission flag</param>
  228. override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
  229. {
  230. if (ExistsFriend(friendlistowner, friend))
  231. return;
  232. string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
  233. using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
  234. {
  235. cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
  236. cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
  237. cmd.Parameters.Add(new SqliteParameter(":perms", perms));
  238. cmd.ExecuteNonQuery();
  239. }
  240. using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
  241. {
  242. cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.ToString()));
  243. cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.ToString()));
  244. cmd.Parameters.Add(new SqliteParameter(":perms", perms));
  245. cmd.ExecuteNonQuery();
  246. }
  247. }
  248. /// <summary>
  249. /// Remove a user from the friendlist
  250. /// </summary>
  251. /// <param name="friendlistowner">UUID of the friendlist owner</param>
  252. /// <param name="friend">UUID of the friend to remove</param>
  253. override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
  254. {
  255. string DeletePerms = "delete from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
  256. using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
  257. {
  258. cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
  259. cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
  260. cmd.ExecuteNonQuery();
  261. }
  262. }
  263. /// <summary>
  264. /// Update the friendlist permission
  265. /// </summary>
  266. /// <param name="friendlistowner">UUID of the friendlist owner</param>
  267. /// <param name="friend">UUID of the friend to modify</param>
  268. /// <param name="perms">updated permission flag</param>
  269. override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
  270. {
  271. string UpdatePerms = "update userfriends set friendPerms=:perms where ownerID=:ownerID and friendID=:friendID";
  272. using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
  273. {
  274. cmd.Parameters.Add(new SqliteParameter(":perms", perms));
  275. cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
  276. cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString()));
  277. cmd.ExecuteNonQuery();
  278. }
  279. }
  280. /// <summary>
  281. /// Get (fetch?) the friendlist for a user
  282. /// </summary>
  283. /// <param name="friendlistowner">UUID of the friendlist owner</param>
  284. /// <returns>The friendlist list</returns>
  285. override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
  286. {
  287. List<FriendListItem> returnlist = new List<FriendListItem>();
  288. using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
  289. {
  290. cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));
  291. try
  292. {
  293. using (IDataReader reader = cmd.ExecuteReader())
  294. {
  295. while (reader.Read())
  296. {
  297. FriendListItem user = new FriendListItem();
  298. user.FriendListOwner = friendlistowner;
  299. user.Friend = new UUID((string)reader[0]);
  300. user.FriendPerms = Convert.ToUInt32(reader[1]);
  301. user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
  302. returnlist.Add(user);
  303. }
  304. reader.Close();
  305. }
  306. }
  307. catch (Exception ex)
  308. {
  309. m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
  310. }
  311. }
  312. return returnlist;
  313. }
  314. override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
  315. {
  316. Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
  317. DataTable agents = ds.Tables["useragents"];
  318. foreach (UUID uuid in uuids)
  319. {
  320. lock (ds)
  321. {
  322. DataRow row = agents.Rows.Find(uuid.ToString());
  323. if (row == null) infos[uuid] = null;
  324. else
  325. {
  326. FriendRegionInfo fri = new FriendRegionInfo();
  327. fri.isOnline = (bool)row["agentOnline"];
  328. fri.regionHandle = Convert.ToUInt64(row["currentHandle"]);
  329. infos[uuid] = fri;
  330. }
  331. }
  332. }
  333. return infos;
  334. }
  335. #endregion
  336. /// <summary>
  337. ///
  338. /// </summary>
  339. /// <param name="queryID"></param>
  340. /// <param name="query"></param>
  341. /// <returns></returns>
  342. override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
  343. {
  344. List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
  345. string[] querysplit;
  346. querysplit = query.Split(' ');
  347. if (querysplit.Length == 2)
  348. {
  349. using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn))
  350. {
  351. cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
  352. cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%"));
  353. using (IDataReader reader = cmd.ExecuteReader())
  354. {
  355. while (reader.Read())
  356. {
  357. AvatarPickerAvatar user = new AvatarPickerAvatar();
  358. user.AvatarID = new UUID((string) reader["UUID"]);
  359. user.firstName = (string) reader["username"];
  360. user.lastName = (string) reader["surname"];
  361. returnlist.Add(user);
  362. }
  363. reader.Close();
  364. }
  365. }
  366. }
  367. else if (querysplit.Length == 1)
  368. {
  369. using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn))
  370. {
  371. cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
  372. cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%"));
  373. using (IDataReader reader = cmd.ExecuteReader())
  374. {
  375. while (reader.Read())
  376. {
  377. AvatarPickerAvatar user = new AvatarPickerAvatar();
  378. user.AvatarID = new UUID((string) reader["UUID"]);
  379. user.firstName = (string) reader["username"];
  380. user.lastName = (string) reader["surname"];
  381. returnlist.Add(user);
  382. }
  383. reader.Close();
  384. }
  385. }
  386. }
  387. return returnlist;
  388. }
  389. /// <summary>
  390. /// Returns a user by UUID direct
  391. /// </summary>
  392. /// <param name="uuid">The user's account ID</param>
  393. /// <returns>A matching user profile</returns>
  394. override public UserAgentData GetAgentByUUID(UUID uuid)
  395. {
  396. lock (ds)
  397. {
  398. DataRow row = ds.Tables["useragents"].Rows.Find(uuid.ToString());
  399. if (row != null)
  400. {
  401. return buildUserAgent(row);
  402. }
  403. else
  404. {
  405. return null;
  406. }
  407. }
  408. }
  409. /// <summary>
  410. /// Returns a session by account name
  411. /// </summary>
  412. /// <param name="name">The account name</param>
  413. /// <returns>The user's session agent</returns>
  414. override public UserAgentData GetAgentByName(string name)
  415. {
  416. return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
  417. }
  418. /// <summary>
  419. /// Returns a session by account name
  420. /// </summary>
  421. /// <param name="fname">The first part of the user's account name</param>
  422. /// <param name="lname">The second part of the user's account name</param>
  423. /// <returns>A user agent</returns>
  424. override public UserAgentData GetAgentByName(string fname, string lname)
  425. {
  426. UserAgentData agent = null;
  427. UserProfileData profile = GetUserByName(fname, lname);
  428. if (profile != null)
  429. {
  430. agent = GetAgentByUUID(profile.ID);
  431. }
  432. return agent;
  433. }
  434. /// <summary>
  435. /// DEPRECATED? Store the weblogin key
  436. /// </summary>
  437. /// <param name="AgentID">UUID of the user</param>
  438. /// <param name="WebLoginKey">UUID of the weblogin</param>
  439. override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
  440. {
  441. DataTable users = ds.Tables["users"];
  442. lock (ds)
  443. {
  444. DataRow row = users.Rows.Find(AgentID.ToString());
  445. if (row == null)
  446. {
  447. m_log.Warn("[USER DB]: Unable to store new web login key for non-existant user");
  448. }
  449. else
  450. {
  451. UserProfileData user = GetUserByUUID(AgentID);
  452. user.WebLoginKey = WebLoginKey;
  453. fillUserRow(row, user);
  454. da.Update(ds, "users");
  455. }
  456. }
  457. }
  458. private bool ExistsFirstLastName(String fname, String lname)
  459. {
  460. string FindUser = "select * from users where (username=:username and surname=:surname)";
  461. using (SqliteCommand cmd = new SqliteCommand(FindUser, g_conn))
  462. {
  463. cmd.Parameters.Add(new SqliteParameter(":username", fname));
  464. cmd.Parameters.Add(new SqliteParameter(":surname", lname));
  465. try
  466. {
  467. using (IDataReader reader = cmd.ExecuteReader())
  468. {
  469. if (reader.Read())
  470. {
  471. reader.Close();
  472. return true;
  473. }
  474. else
  475. {
  476. reader.Close();
  477. return false;
  478. }
  479. }
  480. }
  481. catch (Exception ex)
  482. {
  483. m_log.Error("[USER DB]: Exception searching for user's first and last name: " + ex.ToString());
  484. return false;
  485. }
  486. }
  487. }
  488. /// <summary>
  489. /// Creates a new user profile
  490. /// </summary>
  491. /// <param name="user">The profile to add to the database</param>
  492. override public void AddNewUserProfile(UserProfileData user)
  493. {
  494. DataTable users = ds.Tables["users"];
  495. UUID zero = UUID.Zero;
  496. if (ExistsFirstLastName(user.FirstName, user.SurName) || user.ID == zero)
  497. return;
  498. lock (ds)
  499. {
  500. DataRow row = users.Rows.Find(user.ID.ToString());
  501. if (row == null)
  502. {
  503. row = users.NewRow();
  504. fillUserRow(row, user);
  505. users.Rows.Add(row);
  506. m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
  507. // save changes off to disk
  508. da.Update(ds, "users");
  509. }
  510. else
  511. {
  512. m_log.WarnFormat("[USER DB]: Ignoring add since user with id {0} already exists", user.ID);
  513. }
  514. }
  515. }
  516. /// <summary>
  517. /// Creates a new user profile
  518. /// </summary>
  519. /// <param name="user">The profile to add to the database</param>
  520. /// <returns>True on success, false on error</returns>
  521. override public bool UpdateUserProfile(UserProfileData user)
  522. {
  523. DataTable users = ds.Tables["users"];
  524. lock (ds)
  525. {
  526. DataRow row = users.Rows.Find(user.ID.ToString());
  527. if (row == null)
  528. {
  529. return false;
  530. }
  531. else
  532. {
  533. fillUserRow(row, user);
  534. da.Update(ds, "users");
  535. }
  536. }
  537. //AddNewUserProfile(user);
  538. return true;
  539. }
  540. /// <summary>
  541. /// Creates a new user agent
  542. /// </summary>
  543. /// <param name="agent">The agent to add to the database</param>
  544. override public void AddNewUserAgent(UserAgentData agent)
  545. {
  546. UUID zero = UUID.Zero;
  547. if (agent.SessionID == zero || agent.ProfileID == zero)
  548. return;
  549. DataTable agents = ds.Tables["useragents"];
  550. lock (ds)
  551. {
  552. DataRow row = agents.Rows.Find(agent.ProfileID.ToString());
  553. if (row == null)
  554. {
  555. row = agents.NewRow();
  556. fillUserAgentRow(row, agent);
  557. agents.Rows.Add(row);
  558. }
  559. else
  560. {
  561. fillUserAgentRow(row, agent);
  562. }
  563. m_log.Info("[USER DB]: Syncing useragent database: " + ds.Tables["useragents"].Rows.Count + " agents stored");
  564. // save changes off to disk
  565. dua.Update(ds, "useragents");
  566. }
  567. }
  568. /// <summary>
  569. /// Transfers money between two user accounts
  570. /// </summary>
  571. /// <param name="from">Starting account</param>
  572. /// <param name="to">End account</param>
  573. /// <param name="amount">The amount to move</param>
  574. /// <returns>Success?</returns>
  575. override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
  576. {
  577. return false; // for consistency with the MySQL impl
  578. }
  579. /// <summary>
  580. /// Transfers inventory between two accounts
  581. /// </summary>
  582. /// <remarks>Move to inventory server</remarks>
  583. /// <param name="from">Senders account</param>
  584. /// <param name="to">Receivers account</param>
  585. /// <param name="item">Inventory item</param>
  586. /// <returns>Success?</returns>
  587. override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
  588. {
  589. return false; //for consistency with the MySQL impl
  590. }
  591. /// <summary>
  592. /// Appearance.
  593. /// TODO: stubs for now to do in memory appearance.
  594. /// </summary>
  595. /// <param name="user">The user UUID</param>
  596. /// <returns>Avatar Appearence</returns>
  597. override public AvatarAppearance GetUserAppearance(UUID user)
  598. {
  599. m_log.Info("[APPEARANCE] GetUserAppearance " + user.ToString());
  600. AvatarAppearance aa = new AvatarAppearance(user);
  601. //try {
  602. aa.Owner = user;
  603. DataTable aap = ds.Tables["avatarappearance"];
  604. lock (ds)
  605. {
  606. DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
  607. if (row == null)
  608. {
  609. m_log.Info("[APPEARANCE] Could not find appearance for " + user.ToString());
  610. //m_log.Debug("[USER DB]: Creating avatarappearance For: " + user.ToString());
  611. //row = aap.NewRow();
  612. //fillAvatarAppearanceRow(row, user, appearance);
  613. //aap.Rows.Add(row);
  614. // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
  615. // save changes off to disk
  616. //daa.Update(ds, "avatarappearance");
  617. }
  618. else
  619. {
  620. m_log.InfoFormat("[APPEARANCE] appearance found for {0}", user.ToString());
  621. aa.BodyAsset = new UUID((String)row["BodyAsset"]);
  622. aa.BodyItem = new UUID((String)row["BodyItem"]);
  623. aa.SkinItem = new UUID((String)row["SkinItem"]);
  624. aa.SkinAsset = new UUID((String)row["SkinAsset"]);
  625. aa.HairItem = new UUID((String)row["HairItem"]);
  626. aa.HairAsset = new UUID((String)row["HairAsset"]);
  627. aa.EyesItem = new UUID((String)row["EyesItem"]);
  628. aa.EyesAsset = new UUID((String)row["EyesAsset"]);
  629. aa.ShirtItem = new UUID((String)row["ShirtItem"]);
  630. aa.ShirtAsset = new UUID((String)row["ShirtAsset"]);
  631. aa.PantsItem = new UUID((String)row["PantsItem"]);
  632. aa.PantsAsset = new UUID((String)row["PantsAsset"]);
  633. aa.ShoesItem = new UUID((String)row["ShoesItem"]);
  634. aa.ShoesAsset = new UUID((String)row["ShoesAsset"]);
  635. aa.SocksItem = new UUID((String)row["SocksItem"]);
  636. aa.SocksAsset = new UUID((String)row["SocksAsset"]);
  637. aa.JacketItem = new UUID((String)row["JacketItem"]);
  638. aa.JacketAsset = new UUID((String)row["JacketAsset"]);
  639. aa.GlovesItem = new UUID((String)row["GlovesItem"]);
  640. aa.GlovesAsset = new UUID((String)row["GlovesAsset"]);
  641. aa.UnderShirtItem = new UUID((String)row["UnderShirtItem"]);
  642. aa.UnderShirtAsset = new UUID((String)row["UnderShirtAsset"]);
  643. aa.UnderPantsItem = new UUID((String)row["UnderPantsItem"]);
  644. aa.UnderPantsAsset = new UUID((String)row["UnderPantsAsset"]);
  645. aa.SkirtItem = new UUID((String)row["SkirtItem"]);
  646. aa.SkirtAsset = new UUID((String)row["SkirtAsset"]);
  647. // Ewe Loon
  648. // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
  649. String str = (String)row["Texture"];
  650. byte[] texture = Convert.FromBase64String(str);
  651. aa.Texture = new Primitive.TextureEntry(texture, 0, texture.Length);
  652. str = (String)row["VisualParams"];
  653. byte[] VisualParams = Convert.FromBase64String(str);
  654. aa.VisualParams = VisualParams;
  655. aa.Serial = Convert.ToInt32(row["Serial"]);
  656. aa.AvatarHeight = Convert.ToSingle(row["AvatarHeight"]);
  657. m_log.InfoFormat("[APPEARANCE] appearance set for {0}", user.ToString());
  658. }
  659. }
  660. // m_log.Info("[APPEARANCE] Found appearance for " + user.ToString() + aa.ToString());
  661. // } catch (KeyNotFoundException) {
  662. // m_log.InfoFormat("[APPEARANCE] No appearance found for {0}", user.ToString());
  663. // }
  664. return aa;
  665. }
  666. /// <summary>
  667. /// Update a user appearence
  668. /// </summary>
  669. /// <param name="user">the user UUID</param>
  670. /// <param name="appearance">appearence</param>
  671. override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
  672. {
  673. appearance.Owner = user;
  674. DataTable aap = ds.Tables["avatarappearance"];
  675. lock (ds)
  676. {
  677. DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
  678. if (row == null)
  679. {
  680. m_log.Debug("[USER DB]: Creating UserAppearance For: " + user.ToString());
  681. row = aap.NewRow();
  682. fillAvatarAppearanceRow(row, user, appearance);
  683. aap.Rows.Add(row);
  684. // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
  685. // save changes off to disk
  686. daa.Update(ds, "avatarappearance");
  687. }
  688. else
  689. {
  690. m_log.Debug("[USER DB]: Updating UserAppearance For: " + user.ToString());
  691. fillAvatarAppearanceRow(row, user, appearance);
  692. daa.Update(ds, "avatarappearance");
  693. }
  694. }
  695. }
  696. /// <summary>
  697. /// Returns the name of the storage provider
  698. /// </summary>
  699. /// <returns>Storage provider name</returns>
  700. override public string Name
  701. {
  702. get {return "Sqlite Userdata";}
  703. }
  704. /// <summary>
  705. /// Returns the version of the storage provider
  706. /// </summary>
  707. /// <returns>Storage provider version</returns>
  708. override public string Version
  709. {
  710. get {return "0.1";}
  711. }
  712. /***********************************************************************
  713. *
  714. * DataTable creation
  715. *
  716. **********************************************************************/
  717. /***********************************************************************
  718. *
  719. * Database Definition Functions
  720. *
  721. * This should be db agnostic as we define them in ADO.NET terms
  722. *
  723. **********************************************************************/
  724. /// <summary>
  725. /// Create the "users" table
  726. /// </summary>
  727. /// <returns>DataTable</returns>
  728. private static DataTable createUsersTable()
  729. {
  730. DataTable users = new DataTable("users");
  731. SQLiteUtil.createCol(users, "UUID", typeof (String));
  732. SQLiteUtil.createCol(users, "username", typeof (String));
  733. SQLiteUtil.createCol(users, "surname", typeof (String));
  734. SQLiteUtil.createCol(users, "email", typeof (String));
  735. SQLiteUtil.createCol(users, "passwordHash", typeof (String));
  736. SQLiteUtil.createCol(users, "passwordSalt", typeof (String));
  737. SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32));
  738. SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32));
  739. SQLiteUtil.createCol(users, "homeRegionID", typeof (String));
  740. SQLiteUtil.createCol(users, "homeLocationX", typeof (Double));
  741. SQLiteUtil.createCol(users, "homeLocationY", typeof (Double));
  742. SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double));
  743. SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double));
  744. SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double));
  745. SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double));
  746. SQLiteUtil.createCol(users, "created", typeof (Int32));
  747. SQLiteUtil.createCol(users, "lastLogin", typeof (Int32));
  748. //TODO: Please delete this column. It's now a brick
  749. SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String));
  750. SQLiteUtil.createCol(users, "userInventoryURI", typeof (String));
  751. SQLiteUtil.createCol(users, "userAssetURI", typeof (String));
  752. SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32));
  753. SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32));
  754. SQLiteUtil.createCol(users, "profileAboutText", typeof (String));
  755. SQLiteUtil.createCol(users, "profileFirstText", typeof (String));
  756. SQLiteUtil.createCol(users, "profileImage", typeof (String));
  757. SQLiteUtil.createCol(users, "profileFirstImage", typeof (String));
  758. SQLiteUtil.createCol(users, "webLoginKey", typeof(String));
  759. SQLiteUtil.createCol(users, "userFlags", typeof (Int32));
  760. SQLiteUtil.createCol(users, "godLevel", typeof (Int32));
  761. SQLiteUtil.createCol(users, "customType", typeof (String));
  762. SQLiteUtil.createCol(users, "partner", typeof (String));
  763. // Add in contraints
  764. users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]};
  765. return users;
  766. }
  767. /// <summary>
  768. /// Create the "useragents" table
  769. /// </summary>
  770. /// <returns>Data Table</returns>
  771. private static DataTable createUserAgentsTable()
  772. {
  773. DataTable ua = new DataTable("useragents");
  774. // this is the UUID of the user
  775. SQLiteUtil.createCol(ua, "UUID", typeof (String));
  776. SQLiteUtil.createCol(ua, "agentIP", typeof (String));
  777. SQLiteUtil.createCol(ua, "agentPort", typeof (Int32));
  778. SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean));
  779. SQLiteUtil.createCol(ua, "sessionID", typeof (String));
  780. SQLiteUtil.createCol(ua, "secureSessionID", typeof (String));
  781. SQLiteUtil.createCol(ua, "regionID", typeof (String));
  782. SQLiteUtil.createCol(ua, "loginTime", typeof (Int32));
  783. SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32));
  784. SQLiteUtil.createCol(ua, "currentRegion", typeof (String));
  785. SQLiteUtil.createCol(ua, "currentHandle", typeof (String));
  786. // vectors
  787. SQLiteUtil.createCol(ua, "currentPosX", typeof (Double));
  788. SQLiteUtil.createCol(ua, "currentPosY", typeof (Double));
  789. SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double));
  790. // constraints
  791. ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]};
  792. return ua;
  793. }
  794. /// <summary>
  795. /// Create the "userfriends" table
  796. /// </summary>
  797. /// <returns>Data Table</returns>
  798. private static DataTable createUserFriendsTable()
  799. {
  800. DataTable ua = new DataTable("userfriends");
  801. // table contains user <----> user relationship with perms
  802. SQLiteUtil.createCol(ua, "ownerID", typeof(String));
  803. SQLiteUtil.createCol(ua, "friendID", typeof(String));
  804. SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32));
  805. SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32));
  806. SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32));
  807. return ua;
  808. }
  809. /// <summary>
  810. /// Create the "avatarappearance" table
  811. /// </summary>
  812. /// <returns>Data Table</returns>
  813. private static DataTable createAvatarAppearanceTable()
  814. {
  815. DataTable aa = new DataTable("avatarappearance");
  816. // table contains user appearance items
  817. SQLiteUtil.createCol(aa, "Owner", typeof(String));
  818. SQLiteUtil.createCol(aa, "BodyItem", typeof(String));
  819. SQLiteUtil.createCol(aa, "BodyAsset", typeof(String));
  820. SQLiteUtil.createCol(aa, "SkinItem", typeof(String));
  821. SQLiteUtil.createCol(aa, "SkinAsset", typeof(String));
  822. SQLiteUtil.createCol(aa, "HairItem", typeof(String));
  823. SQLiteUtil.createCol(aa, "HairAsset", typeof(String));
  824. SQLiteUtil.createCol(aa, "EyesItem", typeof(String));
  825. SQLiteUtil.createCol(aa, "EyesAsset", typeof(String));
  826. SQLiteUtil.createCol(aa, "ShirtItem", typeof(String));
  827. SQLiteUtil.createCol(aa, "ShirtAsset", typeof(String));
  828. SQLiteUtil.createCol(aa, "PantsItem", typeof(String));
  829. SQLiteUtil.createCol(aa, "PantsAsset", typeof(String));
  830. SQLiteUtil.createCol(aa, "ShoesItem", typeof(String));
  831. SQLiteUtil.createCol(aa, "ShoesAsset", typeof(String));
  832. SQLiteUtil.createCol(aa, "SocksItem", typeof(String));
  833. SQLiteUtil.createCol(aa, "SocksAsset", typeof(String));
  834. SQLiteUtil.createCol(aa, "JacketItem", typeof(String));
  835. SQLiteUtil.createCol(aa, "JacketAsset", typeof(String));
  836. SQLiteUtil.createCol(aa, "GlovesItem", typeof(String));
  837. SQLiteUtil.createCol(aa, "GlovesAsset", typeof(String));
  838. SQLiteUtil.createCol(aa, "UnderShirtItem", typeof(String));
  839. SQLiteUtil.createCol(aa, "UnderShirtAsset", typeof(String));
  840. SQLiteUtil.createCol(aa, "UnderPantsItem", typeof(String));
  841. SQLiteUtil.createCol(aa, "UnderPantsAsset", typeof(String));
  842. SQLiteUtil.createCol(aa, "SkirtItem", typeof(String));
  843. SQLiteUtil.createCol(aa, "SkirtAsset", typeof(String));
  844. // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
  845. SQLiteUtil.createCol(aa, "Texture", typeof (String));
  846. SQLiteUtil.createCol(aa, "VisualParams", typeof (String));
  847. SQLiteUtil.createCol(aa, "Serial", typeof(Int32));
  848. SQLiteUtil.createCol(aa, "AvatarHeight", typeof(Double));
  849. aa.PrimaryKey = new DataColumn[] { aa.Columns["Owner"] };
  850. return aa;
  851. }
  852. /***********************************************************************
  853. *
  854. * Convert between ADO.NET <=> OpenSim Objects
  855. *
  856. * These should be database independant
  857. *
  858. **********************************************************************/
  859. /// <summary>
  860. /// TODO: this doesn't work yet because something more
  861. /// interesting has to be done to actually get these values
  862. /// back out. Not enough time to figure it out yet.
  863. /// </summary>
  864. /// <param name="row"></param>
  865. /// <returns></returns>
  866. private static UserProfileData buildUserProfile(DataRow row)
  867. {
  868. UserProfileData user = new UserProfileData();
  869. UUID tmp;
  870. UUID.TryParse((String)row["UUID"], out tmp);
  871. user.ID = tmp;
  872. user.FirstName = (String) row["username"];
  873. user.SurName = (String) row["surname"];
  874. user.Email = (row.IsNull("email")) ? "" : (String) row["email"];
  875. user.PasswordHash = (String) row["passwordHash"];
  876. user.PasswordSalt = (String) row["passwordSalt"];
  877. user.HomeRegionX = Convert.ToUInt32(row["homeRegionX"]);
  878. user.HomeRegionY = Convert.ToUInt32(row["homeRegionY"]);
  879. user.HomeLocation = new Vector3(
  880. Convert.ToSingle(row["homeLocationX"]),
  881. Convert.ToSingle(row["homeLocationY"]),
  882. Convert.ToSingle(row["homeLocationZ"])
  883. );
  884. user.HomeLookAt = new Vector3(
  885. Convert.ToSingle(row["homeLookAtX"]),
  886. Convert.ToSingle(row["homeLookAtY"]),
  887. Convert.ToSingle(row["homeLookAtZ"])
  888. );
  889. UUID regionID = UUID.Zero;
  890. UUID.TryParse(row["homeRegionID"].ToString(), out regionID); // it's ok if it doesn't work; just use UUID.Zero
  891. user.HomeRegionID = regionID;
  892. user.Created = Convert.ToInt32(row["created"]);
  893. user.LastLogin = Convert.ToInt32(row["lastLogin"]);
  894. user.UserInventoryURI = (String) row["userInventoryURI"];
  895. user.UserAssetURI = (String) row["userAssetURI"];
  896. user.CanDoMask = Convert.ToUInt32(row["profileCanDoMask"]);
  897. user.WantDoMask = Convert.ToUInt32(row["profileWantDoMask"]);
  898. user.AboutText = (String) row["profileAboutText"];
  899. user.FirstLifeAboutText = (String) row["profileFirstText"];
  900. UUID.TryParse((String)row["profileImage"], out tmp);
  901. user.Image = tmp;
  902. UUID.TryParse((String)row["profileFirstImage"], out tmp);
  903. user.FirstLifeImage = tmp;
  904. user.WebLoginKey = new UUID((String) row["webLoginKey"]);
  905. user.UserFlags = Convert.ToInt32(row["userFlags"]);
  906. user.GodLevel = Convert.ToInt32(row["godLevel"]);
  907. user.CustomType = row["customType"].ToString();
  908. user.Partner = new UUID((String) row["partner"]);
  909. return user;
  910. }
  911. /// <summary>
  912. /// Persist user profile data
  913. /// </summary>
  914. /// <param name="row"></param>
  915. /// <param name="user"></param>
  916. private void fillUserRow(DataRow row, UserProfileData user)
  917. {
  918. row["UUID"] = user.ID.ToString();
  919. row["username"] = user.FirstName;
  920. row["surname"] = user.SurName;
  921. row["email"] = user.Email;
  922. row["passwordHash"] = user.PasswordHash;
  923. row["passwordSalt"] = user.PasswordSalt;
  924. row["homeRegionX"] = user.HomeRegionX;
  925. row["homeRegionY"] = user.HomeRegionY;
  926. row["homeRegionID"] = user.HomeRegionID.ToString();
  927. row["homeLocationX"] = user.HomeLocation.X;
  928. row["homeLocationY"] = user.HomeLocation.Y;
  929. row["homeLocationZ"] = user.HomeLocation.Z;
  930. row["homeLookAtX"] = user.HomeLookAt.X;
  931. row["homeLookAtY"] = user.HomeLookAt.Y;
  932. row["homeLookAtZ"] = user.HomeLookAt.Z;
  933. row["created"] = user.Created;
  934. row["lastLogin"] = user.LastLogin;
  935. //TODO: Get rid of rootInventoryFolderID in a safe way.
  936. row["rootInventoryFolderID"] = UUID.Zero.ToString();
  937. row["userInventoryURI"] = user.UserInventoryURI;
  938. row["userAssetURI"] = user.UserAssetURI;
  939. row["profileCanDoMask"] = user.CanDoMask;
  940. row["profileWantDoMask"] = user.WantDoMask;
  941. row["profileAboutText"] = user.AboutText;
  942. row["profileFirstText"] = user.FirstLifeAboutText;
  943. row["profileImage"] = user.Image.ToString();
  944. row["profileFirstImage"] = user.FirstLifeImage.ToString();
  945. row["webLoginKey"] = user.WebLoginKey.ToString();
  946. row["userFlags"] = user.UserFlags;
  947. row["godLevel"] = user.GodLevel;
  948. row["customType"] = user.CustomType == null ? "" : user.CustomType;
  949. row["partner"] = user.Partner.ToString();
  950. // ADO.NET doesn't handle NULL very well
  951. foreach (DataColumn col in ds.Tables["users"].Columns)
  952. {
  953. if (row[col] == null)
  954. {
  955. row[col] = String.Empty;
  956. }
  957. }
  958. }
  959. /// <summary>
  960. ///
  961. /// </summary>
  962. /// <param name="row"></param>
  963. /// <param name="user"></param>
  964. private void fillAvatarAppearanceRow(DataRow row, UUID user, AvatarAppearance appearance)
  965. {
  966. row["Owner"] = Util.ToRawUuidString(user);
  967. row["BodyItem"] = appearance.BodyItem.ToString();
  968. row["BodyAsset"] = appearance.BodyAsset.ToString();
  969. row["SkinItem"] = appearance.SkinItem.ToString();
  970. row["SkinAsset"] = appearance.SkinAsset.ToString();
  971. row["HairItem"] = appearance.HairItem.ToString();
  972. row["HairAsset"] = appearance.HairAsset.ToString();
  973. row["EyesItem"] = appearance.EyesItem.ToString();
  974. row["EyesAsset"] = appearance.EyesAsset.ToString();
  975. row["ShirtItem"] = appearance.ShirtItem.ToString();
  976. row["ShirtAsset"] = appearance.ShirtAsset.ToString();
  977. row["PantsItem"] = appearance.PantsItem.ToString();
  978. row["PantsAsset"] = appearance.PantsAsset.ToString();
  979. row["ShoesItem"] = appearance.ShoesItem.ToString();
  980. row["ShoesAsset"] = appearance.ShoesAsset.ToString();
  981. row["SocksItem"] = appearance.SocksItem.ToString();
  982. row["SocksAsset"] = appearance.SocksAsset.ToString();
  983. row["JacketItem"] = appearance.JacketItem.ToString();
  984. row["JacketAsset"] = appearance.JacketAsset.ToString();
  985. row["GlovesItem"] = appearance.GlovesItem.ToString();
  986. row["GlovesAsset"] = appearance.GlovesAsset.ToString();
  987. row["UnderShirtItem"] = appearance.UnderShirtItem.ToString();
  988. row["UnderShirtAsset"] = appearance.UnderShirtAsset.ToString();
  989. row["UnderPantsItem"] = appearance.UnderPantsItem.ToString();
  990. row["UnderPantsAsset"] = appearance.UnderPantsAsset.ToString();
  991. row["SkirtItem"] = appearance.SkirtItem.ToString();
  992. row["SkirtAsset"] = appearance.SkirtAsset.ToString();
  993. // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
  994. row["Texture"] = Convert.ToBase64String(appearance.Texture.GetBytes());
  995. row["VisualParams"] = Convert.ToBase64String(appearance.VisualParams);
  996. row["Serial"] = appearance.Serial;
  997. row["AvatarHeight"] = appearance.AvatarHeight;
  998. // ADO.NET doesn't handle NULL very well
  999. foreach (DataColumn col in ds.Tables["avatarappearance"].Columns)
  1000. {
  1001. if (row[col] == null)
  1002. {
  1003. row[col] = String.Empty;
  1004. }
  1005. }
  1006. }
  1007. /// <summary>
  1008. ///
  1009. /// </summary>
  1010. /// <param name="row"></param>
  1011. /// <returns></returns>
  1012. private static UserAgentData buildUserAgent(DataRow row)
  1013. {
  1014. UserAgentData ua = new UserAgentData();
  1015. UUID tmp;
  1016. UUID.TryParse((String)row["UUID"], out tmp);
  1017. ua.ProfileID = tmp;
  1018. ua.AgentIP = (String)row["agentIP"];
  1019. ua.AgentPort = Convert.ToUInt32(row["agentPort"]);
  1020. ua.AgentOnline = Convert.ToBoolean(row["agentOnline"]);
  1021. ua.SessionID = new UUID((String) row["sessionID"]);
  1022. ua.SecureSessionID = new UUID((String) row["secureSessionID"]);
  1023. ua.InitialRegion = new UUID((String) row["regionID"]);
  1024. ua.LoginTime = Convert.ToInt32(row["loginTime"]);
  1025. ua.LogoutTime = Convert.ToInt32(row["logoutTime"]);
  1026. ua.Region = new UUID((String) row["currentRegion"]);
  1027. ua.Handle = Convert.ToUInt64(row["currentHandle"]);
  1028. ua.Position = new Vector3(
  1029. Convert.ToSingle(row["currentPosX"]),
  1030. Convert.ToSingle(row["currentPosY"]),
  1031. Convert.ToSingle(row["currentPosZ"])
  1032. );
  1033. ua.LookAt = new Vector3(
  1034. Convert.ToSingle(row["currentLookAtX"]),
  1035. Convert.ToSingle(row["currentLookAtY"]),
  1036. Convert.ToSingle(row["currentLookAtZ"])
  1037. );
  1038. return ua;
  1039. }
  1040. /// <summary>
  1041. ///
  1042. /// </summary>
  1043. /// <param name="row"></param>
  1044. /// <param name="ua"></param>
  1045. private static void fillUserAgentRow(DataRow row, UserAgentData ua)
  1046. {
  1047. row["UUID"] = ua.ProfileID.ToString();
  1048. row["agentIP"] = ua.AgentIP;
  1049. row["agentPort"] = ua.AgentPort;
  1050. row["agentOnline"] = ua.AgentOnline;
  1051. row["sessionID"] = ua.SessionID.ToString();
  1052. row["secureSessionID"] = ua.SecureSessionID.ToString();
  1053. row["regionID"] = ua.InitialRegion.ToString();
  1054. row["loginTime"] = ua.LoginTime;
  1055. row["logoutTime"] = ua.LogoutTime;
  1056. row["currentRegion"] = ua.Region.ToString();
  1057. row["currentHandle"] = ua.Handle.ToString();
  1058. // vectors
  1059. row["currentPosX"] = ua.Position.X;
  1060. row["currentPosY"] = ua.Position.Y;
  1061. row["currentPosZ"] = ua.Position.Z;
  1062. row["currentLookAtX"] = ua.LookAt.X;
  1063. row["currentLookAtY"] = ua.LookAt.Y;
  1064. row["currentLookAtZ"] = ua.LookAt.Z;
  1065. }
  1066. /***********************************************************************
  1067. *
  1068. * Database Binding functions
  1069. *
  1070. * These will be db specific due to typing, and minor differences
  1071. * in databases.
  1072. *
  1073. **********************************************************************/
  1074. /// <summary>
  1075. ///
  1076. /// </summary>
  1077. /// <param name="da"></param>
  1078. /// <param name="conn"></param>
  1079. private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn)
  1080. {
  1081. da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]);
  1082. da.InsertCommand.Connection = conn;
  1083. da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]);
  1084. da.UpdateCommand.Connection = conn;
  1085. SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID");
  1086. delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String)));
  1087. delete.Connection = conn;
  1088. da.DeleteCommand = delete;
  1089. }
  1090. private void setupAgentCommands(SqliteDataAdapter da, SqliteConnection conn)
  1091. {
  1092. da.InsertCommand = SQLiteUtil.createInsertCommand("useragents", ds.Tables["useragents"]);
  1093. da.InsertCommand.Connection = conn;
  1094. da.UpdateCommand = SQLiteUtil.createUpdateCommand("useragents", "UUID=:UUID", ds.Tables["useragents"]);
  1095. da.UpdateCommand.Connection = conn;
  1096. SqliteCommand delete = new SqliteCommand("delete from useragents where UUID = :ProfileID");
  1097. delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ProfileID", typeof(String)));
  1098. delete.Connection = conn;
  1099. da.DeleteCommand = delete;
  1100. }
  1101. /// <summary>
  1102. ///
  1103. /// </summary>
  1104. /// <param name="daf"></param>
  1105. /// <param name="conn"></param>
  1106. private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn)
  1107. {
  1108. daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]);
  1109. daf.InsertCommand.Connection = conn;
  1110. daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]);
  1111. daf.UpdateCommand.Connection = conn;
  1112. SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID");
  1113. delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String)));
  1114. delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String)));
  1115. delete.Connection = conn;
  1116. daf.DeleteCommand = delete;
  1117. }
  1118. /// <summary>
  1119. ///
  1120. /// </summary>
  1121. /// <param name="daf"></param>
  1122. /// <param name="conn"></param>
  1123. private void setupAvatarAppearanceCommands(SqliteDataAdapter daa, SqliteConnection conn)
  1124. {
  1125. daa.InsertCommand = SQLiteUtil.createInsertCommand("avatarappearance", ds.Tables["avatarappearance"]);
  1126. daa.InsertCommand.Connection = conn;
  1127. daa.UpdateCommand = SQLiteUtil.createUpdateCommand("avatarappearance", "Owner=:Owner", ds.Tables["avatarappearance"]);
  1128. daa.UpdateCommand.Connection = conn;
  1129. SqliteCommand delete = new SqliteCommand("delete from avatarappearance where Owner=:Owner");
  1130. delete.Parameters.Add(SQLiteUtil.createSqliteParameter("Owner", typeof(String)));
  1131. delete.Connection = conn;
  1132. daa.DeleteCommand = delete;
  1133. }
  1134. override public void ResetAttachments(UUID userID)
  1135. {
  1136. }
  1137. override public void LogoutUsers(UUID regionID)
  1138. {
  1139. }
  1140. }
  1141. }