aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLUserData.cs
diff options
context:
space:
mode:
authorStrawberryFride2010-02-24 16:42:39 +0000
committerMelanie2010-02-24 15:50:44 +0000
commit2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf (patch)
tree672ec2bb3e64b8f930faa9a87801ba9042e2d4f7 /OpenSim/Data/MSSQL/MSSQLUserData.cs
parentFixed typo that was affecting the BasicInventoryAccessModule (diff)
downloadopensim-SC_OLD-2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf.zip
opensim-SC_OLD-2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf.tar.gz
opensim-SC_OLD-2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf.tar.bz2
opensim-SC_OLD-2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf.tar.xz
MSSQL Additions for Presence Refactor branch. Most functionality tested and works, some outstanding issues around login location and border crossings on y axis.
Signed-off-by: Melanie <melanie@t-data.com>
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs1238
1 files changed, 1238 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
new file mode 100644
index 0000000..6bdb559
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs
@@ -0,0 +1,1238 @@
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
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using System.Data.SqlClient;
33using System.Reflection;
34using log4net;
35using OpenMetaverse;
36using OpenSim.Framework;
37
38namespace OpenSim.Data.MSSQL
39{
40 /// <summary>
41 /// A database interface class to a user profile storage system
42 /// </summary>
43 public class MSSQLUserData : UserDataBase
44 {
45 private const string _migrationStore = "UserStore";
46
47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48
49 /// <summary>
50 /// Database manager for MSSQL
51 /// </summary>
52 public MSSQLManager database;
53 private string m_connectionString;
54
55 private const string m_agentsTableName = "agents";
56 private const string m_usersTableName = "users";
57 private const string m_userFriendsTableName = "userfriends";
58
59 // [Obsolete("Cannot be default-initialized!")]
60 override public void Initialise()
61 {
62 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
63 throw new PluginNotInitialisedException(Name);
64 }
65
66 /// <summary>
67 /// Loads and initialises the MSSQL storage plugin
68 /// </summary>
69 /// <param name="connect">connectionstring</param>
70 /// <remarks>use mssql_connection.ini</remarks>
71 override public void Initialise(string connect)
72 {
73 m_connectionString = connect;
74 database = new MSSQLManager(connect);
75
76
77 //Check migration on DB
78 database.CheckMigration(_migrationStore);
79 }
80
81 /// <summary>
82 /// Releases unmanaged and - optionally - managed resources
83 /// </summary>
84 override public void Dispose() { }
85
86 #region User table methods
87
88 /// <summary>
89 /// Searches the database for a specified user profile by name components
90 /// </summary>
91 /// <param name="user">The first part of the account name</param>
92 /// <param name="last">The second part of the account name</param>
93 /// <returns>A user profile</returns>
94 override public UserProfileData GetUserByName(string user, string last)
95 {
96 string sql = string.Format(@"SELECT * FROM {0}
97 WHERE username = @first AND lastname = @second", m_usersTableName);
98 using (SqlConnection conn = new SqlConnection(m_connectionString))
99 using (SqlCommand cmd = new SqlCommand(sql, conn))
100 {
101 cmd.Parameters.Add(database.CreateParameter("first", user));
102 cmd.Parameters.Add(database.CreateParameter("second", last));
103 try
104 {
105 conn.Open();
106 using (SqlDataReader reader = cmd.ExecuteReader())
107 {
108 return ReadUserRow(reader);
109 }
110 }
111 catch (Exception e)
112 {
113 m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message);
114 return null;
115 }
116 }
117 }
118
119 /// <summary>
120 /// See IUserDataPlugin
121 /// </summary>
122 /// <param name="uuid"></param>
123 /// <returns></returns>
124 override public UserProfileData GetUserByUUID(UUID uuid)
125 {
126 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName);
127 using (SqlConnection conn = new SqlConnection(m_connectionString))
128 using (SqlCommand cmd = new SqlCommand(sql, conn))
129 {
130 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
131 conn.Open();
132 try
133 {
134 using (SqlDataReader reader = cmd.ExecuteReader())
135 {
136 return ReadUserRow(reader);
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message);
142 return null;
143 }
144 }
145 }
146
147
148 /// <summary>
149 /// Creates a new users profile
150 /// </summary>
151 /// <param name="user">The user profile to create</param>
152 override public void AddNewUserProfile(UserProfileData user)
153 {
154 try
155 {
156 InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
157 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
158 user.HomeLocation.Z,
159 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
160 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
161 user.CanDoMask, user.WantDoMask,
162 user.AboutText, user.FirstLifeAboutText, user.Image,
163 user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID,
164 user.GodLevel, user.UserFlags, user.CustomType, user.Partner);
165 }
166 catch (Exception e)
167 {
168 m_log.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message);
169 }
170 }
171
172 /// <summary>
173 /// update a user profile
174 /// </summary>
175 /// <param name="user">the profile to update</param>
176 /// <returns></returns>
177 override public bool UpdateUserProfile(UserProfileData user)
178 {
179 string sql = string.Format(@"UPDATE {0}
180 SET UUID = @uuid,
181 username = @username,
182 lastname = @lastname,
183 email = @email,
184 passwordHash = @passwordHash,
185 passwordSalt = @passwordSalt,
186 homeRegion = @homeRegion,
187 homeLocationX = @homeLocationX,
188 homeLocationY = @homeLocationY,
189 homeLocationZ = @homeLocationZ,
190 homeLookAtX = @homeLookAtX,
191 homeLookAtY = @homeLookAtY,
192 homeLookAtZ = @homeLookAtZ,
193 created = @created,
194 lastLogin = @lastLogin,
195 userInventoryURI = @userInventoryURI,
196 userAssetURI = @userAssetURI,
197 profileCanDoMask = @profileCanDoMask,
198 profileWantDoMask = @profileWantDoMask,
199 profileAboutText = @profileAboutText,
200 profileFirstText = @profileFirstText,
201 profileImage = @profileImage,
202 profileFirstImage = @profileFirstImage,
203 webLoginKey = @webLoginKey,
204 homeRegionID = @homeRegionID,
205 userFlags = @userFlags,
206 godLevel = @godLevel,
207 customType = @customType,
208 partner = @partner WHERE UUID = @keyUUUID;", m_usersTableName);
209 using (SqlConnection conn = new SqlConnection(m_connectionString))
210 using (SqlCommand command = new SqlCommand(sql, conn))
211 {
212 command.Parameters.Add(database.CreateParameter("uuid", user.ID));
213 command.Parameters.Add(database.CreateParameter("username", user.FirstName));
214 command.Parameters.Add(database.CreateParameter("lastname", user.SurName));
215 command.Parameters.Add(database.CreateParameter("email", user.Email));
216 command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash));
217 command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt));
218 command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion));
219 command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X));
220 command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y));
221 command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z));
222 command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X));
223 command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y));
224 command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z));
225 command.Parameters.Add(database.CreateParameter("created", user.Created));
226 command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin));
227 command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI));
228 command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI));
229 command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask));
230 command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask));
231 command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText));
232 command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText));
233 command.Parameters.Add(database.CreateParameter("profileImage", user.Image));
234 command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage));
235 command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey));
236 command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID));
237 command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags));
238 command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel));
239 command.Parameters.Add(database.CreateParameter("customType", user.CustomType));
240 command.Parameters.Add(database.CreateParameter("partner", user.Partner));
241 command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID));
242 conn.Open();
243 try
244 {
245 int affected = command.ExecuteNonQuery();
246 return (affected != 0);
247 }
248 catch (Exception e)
249 {
250 m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message);
251 }
252 }
253 return false;
254 }
255
256 #endregion
257
258 #region Agent table methods
259
260 /// <summary>
261 /// Returns a user session searching by name
262 /// </summary>
263 /// <param name="name">The account name</param>
264 /// <returns>The users session</returns>
265 override public UserAgentData GetAgentByName(string name)
266 {
267 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
268 }
269
270 /// <summary>
271 /// Returns a user session by account name
272 /// </summary>
273 /// <param name="user">First part of the users account name</param>
274 /// <param name="last">Second part of the users account name</param>
275 /// <returns>The users session</returns>
276 override public UserAgentData GetAgentByName(string user, string last)
277 {
278 UserProfileData profile = GetUserByName(user, last);
279 return GetAgentByUUID(profile.ID);
280 }
281
282 /// <summary>
283 /// Returns an agent session by account UUID
284 /// </summary>
285 /// <param name="uuid">The accounts UUID</param>
286 /// <returns>The users session</returns>
287 override public UserAgentData GetAgentByUUID(UUID uuid)
288 {
289 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName);
290 using (SqlConnection conn = new SqlConnection(m_connectionString))
291 using (SqlCommand cmd = new SqlCommand(sql, conn))
292 {
293 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
294 conn.Open();
295 try
296 {
297 using (SqlDataReader reader = cmd.ExecuteReader())
298 {
299 return readAgentRow(reader);
300 }
301 }
302 catch (Exception e)
303 {
304 m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message);
305 return null;
306 }
307 }
308 }
309
310 /// <summary>
311 /// Creates a new agent
312 /// </summary>
313 /// <param name="agent">The agent to create</param>
314 override public void AddNewUserAgent(UserAgentData agent)
315 {
316 try
317 {
318 InsertUpdateAgentRow(agent);
319 }
320 catch (Exception e)
321 {
322 m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message);
323 }
324 }
325
326 #endregion
327
328 #region User Friends List Data
329
330 /// <summary>
331 /// Add a new friend in the friendlist
332 /// </summary>
333 /// <param name="friendlistowner">UUID of the friendlist owner</param>
334 /// <param name="friend">Friend's UUID</param>
335 /// <param name="perms">Permission flag</param>
336 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
337 {
338 int dtvalue = Util.UnixTimeSinceEpoch();
339 string sql = string.Format(@"INSERT INTO {0}
340 (ownerID,friendID,friendPerms,datetimestamp)
341 VALUES
342 (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
343 using (SqlConnection conn = new SqlConnection(m_connectionString))
344 using (SqlCommand cmd = new SqlCommand(sql, conn))
345 {
346 cmd.Parameters.Add(database.CreateParameter("ownerID", friendlistowner));
347 cmd.Parameters.Add(database.CreateParameter("friendID", friend));
348 cmd.Parameters.Add(database.CreateParameter("friendPerms", perms));
349 cmd.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue));
350 conn.Open();
351 cmd.ExecuteNonQuery();
352
353 try
354 {
355 sql = string.Format(@"INSERT INTO {0}
356 (ownerID,friendID,friendPerms,datetimestamp)
357 VALUES
358 (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
359 cmd.CommandText = sql;
360 cmd.ExecuteNonQuery();
361 }
362 catch (Exception e)
363 {
364 m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message);
365 return;
366 }
367 }
368 }
369
370 /// <summary>
371 /// Remove an friend from the friendlist
372 /// </summary>
373 /// <param name="friendlistowner">UUID of the friendlist owner</param>
374 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param>
375 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
376 {
377 string sql = string.Format(@"DELETE from {0}
378 WHERE ownerID = @ownerID
379 AND friendID = @friendID", m_userFriendsTableName);
380 using (SqlConnection conn = new SqlConnection(m_connectionString))
381 using (SqlCommand cmd = new SqlCommand(sql, conn))
382 {
383 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
384 cmd.Parameters.Add(database.CreateParameter("@friendID", friend));
385 cmd.ExecuteNonQuery();
386 sql = string.Format(@"DELETE from {0}
387 WHERE ownerID = @friendID
388 AND friendID = @ownerID", m_userFriendsTableName);
389 cmd.CommandText = sql;
390 conn.Open();
391 try
392 {
393 cmd.ExecuteNonQuery();
394 }
395 catch (Exception e)
396 {
397 m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message);
398 }
399 }
400 }
401
402 /// <summary>
403 /// Update friendlist permission flag for a friend
404 /// </summary>
405 /// <param name="friendlistowner">UUID of the friendlist owner</param>
406 /// <param name="friend">UUID of the friend</param>
407 /// <param name="perms">new permission flag</param>
408 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
409 {
410 string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms
411 WHERE ownerID = @ownerID
412 AND friendID = @friendID", m_userFriendsTableName);
413 using (SqlConnection conn = new SqlConnection(m_connectionString))
414 using (SqlCommand cmd = new SqlCommand(sql, conn))
415 {
416 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
417 cmd.Parameters.Add(database.CreateParameter("@friendID", friend));
418 cmd.Parameters.Add(database.CreateParameter("@friendPerms", perms));
419 conn.Open();
420 try
421 {
422 cmd.ExecuteNonQuery();
423 }
424 catch (Exception e)
425 {
426 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
427 }
428 }
429 }
430
431 /// <summary>
432 /// Get (fetch?) the user's friendlist
433 /// </summary>
434 /// <param name="friendlistowner">UUID of the friendlist owner</param>
435 /// <returns>Friendlist list</returns>
436 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
437 {
438 List<FriendListItem> friendList = new List<FriendListItem>();
439
440 //Left Join userfriends to itself
441 string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms
442 FROM {0} as a, {0} as b
443 WHERE a.ownerID = @ownerID
444 AND b.ownerID = a.friendID
445 AND b.friendID = a.ownerID", m_userFriendsTableName);
446 using (SqlConnection conn = new SqlConnection(m_connectionString))
447 using (SqlCommand cmd = new SqlCommand(sql, conn))
448 {
449 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
450 conn.Open();
451 try
452 {
453 using (SqlDataReader reader = cmd.ExecuteReader())
454 {
455 while (reader.Read())
456 {
457 FriendListItem fli = new FriendListItem();
458 fli.FriendListOwner = new UUID((Guid)reader["ownerID"]);
459 fli.Friend = new UUID((Guid)reader["friendID"]);
460 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
461
462 // This is not a real column in the database table, it's a joined column from the opposite record
463 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
464 friendList.Add(fli);
465 }
466 }
467 }
468 catch (Exception e)
469 {
470 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
471 }
472 }
473 return friendList;
474 }
475
476 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos(List<UUID> uuids)
477 {
478 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID, FriendRegionInfo>();
479 try
480 {
481 foreach (UUID uuid in uuids)
482 {
483 string sql = string.Format(@"SELECT agentOnline,currentHandle
484 FROM {0} WHERE UUID = @uuid", m_agentsTableName);
485 using (SqlConnection conn = new SqlConnection(m_connectionString))
486 using (SqlCommand cmd = new SqlCommand(sql, conn))
487 {
488
489 cmd.Parameters.Add(database.CreateParameter("@uuid", uuid));
490 conn.Open();
491 using (SqlDataReader reader = cmd.ExecuteReader())
492 {
493 while (reader.Read())
494 {
495 FriendRegionInfo fri = new FriendRegionInfo();
496 fri.isOnline = (byte)reader["agentOnline"] != 0;
497 fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
498
499 infos[uuid] = fri;
500 }
501 }
502 }
503 }
504 }
505 catch (Exception e)
506 {
507 m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e);
508 }
509
510 return infos;
511 }
512 #endregion
513
514 #region Money functions (not used)
515
516 /// <summary>
517 /// Performs a money transfer request between two accounts
518 /// </summary>
519 /// <param name="from">The senders account ID</param>
520 /// <param name="to">The receivers account ID</param>
521 /// <param name="amount">The amount to transfer</param>
522 /// <returns>false</returns>
523 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
524 {
525 return false;
526 }
527
528 /// <summary>
529 /// Performs an inventory transfer request between two accounts
530 /// </summary>
531 /// <remarks>TODO: Move to inventory server</remarks>
532 /// <param name="from">The senders account ID</param>
533 /// <param name="to">The receivers account ID</param>
534 /// <param name="item">The item to transfer</param>
535 /// <returns>false</returns>
536 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
537 {
538 return false;
539 }
540
541 #endregion
542
543 #region Appearance methods
544
545 /// <summary>
546 /// Gets the user appearance.
547 /// </summary>
548 /// <param name="user">The user.</param>
549 /// <returns></returns>
550 override public AvatarAppearance GetUserAppearance(UUID user)
551 {
552 try
553 {
554 AvatarAppearance appearance = new AvatarAppearance();
555 string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID";
556 using (SqlConnection conn = new SqlConnection(m_connectionString))
557 using (SqlCommand cmd = new SqlCommand(sql, conn))
558 {
559
560 cmd.Parameters.Add(database.CreateParameter("@UUID", user));
561 conn.Open();
562 using (SqlDataReader reader = cmd.ExecuteReader())
563 {
564 if (reader.Read())
565 appearance = readUserAppearance(reader);
566 else
567 {
568 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
569 return null;
570 }
571
572 }
573 }
574
575 appearance.SetAttachments(GetUserAttachments(user));
576
577 return appearance;
578 }
579 catch (Exception e)
580 {
581 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
582 }
583 return null;
584 }
585
586 /// <summary>
587 /// Update a user appearence into database
588 /// </summary>
589 /// <param name="user">the used UUID</param>
590 /// <param name="appearance">the appearence</param>
591 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
592 {
593 string sql = @"DELETE FROM avatarappearance WHERE owner=@owner;
594 INSERT INTO avatarappearance
595 (owner, serial, visual_params, texture, avatar_height,
596 body_item, body_asset, skin_item, skin_asset, hair_item,
597 hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset,
598 pants_item, pants_asset, shoes_item, shoes_asset, socks_item,
599 socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset,
600 undershirt_item, undershirt_asset, underpants_item, underpants_asset,
601 skirt_item, skirt_asset)
602 VALUES
603 (@owner, @serial, @visual_params, @texture, @avatar_height,
604 @body_item, @body_asset, @skin_item, @skin_asset, @hair_item,
605 @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset,
606 @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item,
607 @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset,
608 @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset,
609 @skirt_item, @skirt_asset)";
610
611 using (SqlConnection conn = new SqlConnection(m_connectionString))
612 using (SqlCommand cmd = new SqlCommand(sql, conn))
613 {
614 cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner));
615 cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial));
616 cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams));
617 cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.GetBytes()));
618 cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight));
619 cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem));
620 cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset));
621 cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem));
622 cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset));
623 cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem));
624 cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset));
625 cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem));
626 cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset));
627 cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem));
628 cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset));
629 cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem));
630 cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset));
631 cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem));
632 cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset));
633 cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem));
634 cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset));
635 cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem));
636 cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset));
637 cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem));
638 cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset));
639 cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem));
640 cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset));
641 cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem));
642 cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset));
643 cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem));
644 cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset));
645 conn.Open();
646 try
647 {
648 cmd.ExecuteNonQuery();
649 }
650 catch (Exception e)
651 {
652 m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", e.Message);
653 }
654 }
655 UpdateUserAttachments(user, appearance.GetAttachments());
656 }
657
658 #endregion
659
660 #region Attachment methods
661
662 /// <summary>
663 /// Gets all attachment of a agent.
664 /// </summary>
665 /// <param name="agentID">agent ID.</param>
666 /// <returns></returns>
667 public Hashtable GetUserAttachments(UUID agentID)
668 {
669 Hashtable returnTable = new Hashtable();
670 string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid";
671 using (SqlConnection conn = new SqlConnection(m_connectionString))
672 using (SqlCommand cmd = new SqlCommand(sql, conn))
673 {
674 cmd.Parameters.Add(database.CreateParameter("@uuid", agentID));
675 conn.Open();
676 using (SqlDataReader reader = cmd.ExecuteReader())
677 {
678 while (reader.Read())
679 {
680 int attachpoint = Convert.ToInt32(reader["attachpoint"]);
681 if (returnTable.ContainsKey(attachpoint))
682 continue;
683 Hashtable item = new Hashtable();
684 item.Add("item", reader["item"].ToString());
685 item.Add("asset", reader["asset"].ToString());
686
687 returnTable.Add(attachpoint, item);
688 }
689 }
690 }
691 return returnTable;
692 }
693
694 /// <summary>
695 /// Updates all attachments of the agent.
696 /// </summary>
697 /// <param name="agentID">agentID.</param>
698 /// <param name="data">data with all items on attachmentpoints</param>
699 public void UpdateUserAttachments(UUID agentID, Hashtable data)
700 {
701 string sql = "DELETE FROM avatarattachments WHERE UUID = @uuid";
702
703 using (SqlConnection conn = new SqlConnection(m_connectionString))
704 using (SqlCommand cmd = new SqlCommand(sql, conn))
705 {
706 cmd.Parameters.Add(database.CreateParameter("uuid", agentID));
707 conn.Open();
708 cmd.ExecuteNonQuery();
709 }
710 if (data == null)
711 return;
712
713 sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset)
714 VALUES (@uuid, @attachpoint, @item, @asset)";
715
716 using (SqlConnection conn = new SqlConnection(m_connectionString))
717 using (SqlCommand cmd = new SqlCommand(sql, conn))
718 {
719 bool firstTime = true;
720 foreach (DictionaryEntry e in data)
721 {
722 int attachpoint = Convert.ToInt32(e.Key);
723
724 Hashtable item = (Hashtable)e.Value;
725
726 if (firstTime)
727 {
728 cmd.Parameters.Add(database.CreateParameter("@uuid", agentID));
729 cmd.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint));
730 cmd.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString())));
731 cmd.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString())));
732 firstTime = false;
733 }
734 cmd.Parameters["@uuid"].Value = agentID.Guid; //.ToString();
735 cmd.Parameters["@attachpoint"].Value = attachpoint;
736 cmd.Parameters["@item"].Value = new Guid(item["item"].ToString());
737 cmd.Parameters["@asset"].Value = new Guid(item["asset"].ToString());
738
739 try
740 {
741 conn.Open();
742 cmd.ExecuteNonQuery();
743 }
744 catch (Exception ex)
745 {
746 m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message);
747 }
748 }
749 }
750 }
751
752 /// <summary>
753 /// Resets all attachments of a agent in the database.
754 /// </summary>
755 /// <param name="agentID">agentID.</param>
756 override public void ResetAttachments(UUID agentID)
757 {
758 string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid";
759 using (SqlConnection conn = new SqlConnection(m_connectionString))
760 using (SqlCommand cmd = new SqlCommand(sql, conn))
761 {
762 cmd.Parameters.Add(database.CreateParameter("uuid", agentID));
763 conn.Open();
764 cmd.ExecuteNonQuery();
765 }
766 }
767
768 override public void LogoutUsers(UUID regionID)
769 {
770 }
771
772 #endregion
773
774 #region Other public methods
775
776 /// <summary>
777 ///
778 /// </summary>
779 /// <param name="queryID"></param>
780 /// <param name="query"></param>
781 /// <returns></returns>
782 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
783 {
784 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
785 string[] querysplit = query.Split(' ');
786 if (querysplit.Length == 2)
787 {
788 try
789 {
790 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
791 WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName);
792 using (SqlConnection conn = new SqlConnection(m_connectionString))
793 using (SqlCommand cmd = new SqlCommand(sql, conn))
794 {
795 //Add wildcard to the search
796 cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
797 cmd.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%"));
798 conn.Open();
799 using (SqlDataReader reader = cmd.ExecuteReader())
800 {
801 while (reader.Read())
802 {
803 AvatarPickerAvatar user = new AvatarPickerAvatar();
804 user.AvatarID = new UUID((Guid)reader["UUID"]);
805 user.firstName = (string)reader["username"];
806 user.lastName = (string)reader["lastname"];
807 returnlist.Add(user);
808 }
809 }
810 }
811 }
812 catch (Exception e)
813 {
814 m_log.Error(e.ToString());
815 }
816 }
817 else if (querysplit.Length == 1)
818 {
819 try
820 {
821 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
822 WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName);
823 using (SqlConnection conn = new SqlConnection(m_connectionString))
824 using (SqlCommand cmd = new SqlCommand(sql, conn))
825 {
826 cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
827 conn.Open();
828 using (SqlDataReader reader = cmd.ExecuteReader())
829 {
830 while (reader.Read())
831 {
832 AvatarPickerAvatar user = new AvatarPickerAvatar();
833 user.AvatarID = new UUID((Guid)reader["UUID"]);
834 user.firstName = (string)reader["username"];
835 user.lastName = (string)reader["lastname"];
836 returnlist.Add(user);
837 }
838 }
839 }
840 }
841 catch (Exception e)
842 {
843 m_log.Error(e.ToString());
844 }
845 }
846 return returnlist;
847 }
848
849 /// <summary>
850 /// Store a weblogin key
851 /// </summary>
852 /// <param name="AgentID">The agent UUID</param>
853 /// <param name="WebLoginKey">the WebLogin Key</param>
854 /// <remarks>unused ?</remarks>
855 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
856 {
857 UserProfileData user = GetUserByUUID(AgentID);
858 user.WebLoginKey = WebLoginKey;
859 UpdateUserProfile(user);
860 }
861
862 /// <summary>
863 /// Database provider name
864 /// </summary>
865 /// <returns>Provider name</returns>
866 override public string Name
867 {
868 get { return "MSSQL Userdata Interface"; }
869 }
870
871 /// <summary>
872 /// Database provider version
873 /// </summary>
874 /// <returns>provider version</returns>
875 override public string Version
876 {
877 get { return database.getVersion(); }
878 }
879
880 #endregion
881
882 #region Private functions
883
884 /// <summary>
885 /// Reads a one item from an SQL result
886 /// </summary>
887 /// <param name="reader">The SQL Result</param>
888 /// <returns>the item read</returns>
889 private static AvatarAppearance readUserAppearance(SqlDataReader reader)
890 {
891 try
892 {
893 AvatarAppearance appearance = new AvatarAppearance();
894
895 appearance.Owner = new UUID((Guid)reader["owner"]);
896 appearance.Serial = Convert.ToInt32(reader["serial"]);
897 appearance.VisualParams = (byte[])reader["visual_params"];
898 appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length);
899 appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]);
900 appearance.BodyItem = new UUID((Guid)reader["body_item"]);
901 appearance.BodyAsset = new UUID((Guid)reader["body_asset"]);
902 appearance.SkinItem = new UUID((Guid)reader["skin_item"]);
903 appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]);
904 appearance.HairItem = new UUID((Guid)reader["hair_item"]);
905 appearance.HairAsset = new UUID((Guid)reader["hair_asset"]);
906 appearance.EyesItem = new UUID((Guid)reader["eyes_item"]);
907 appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]);
908 appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]);
909 appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]);
910 appearance.PantsItem = new UUID((Guid)reader["pants_item"]);
911 appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]);
912 appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]);
913 appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]);
914 appearance.SocksItem = new UUID((Guid)reader["socks_item"]);
915 appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]);
916 appearance.JacketItem = new UUID((Guid)reader["jacket_item"]);
917 appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]);
918 appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]);
919 appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]);
920 appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]);
921 appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]);
922 appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]);
923 appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]);
924 appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]);
925 appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]);
926
927 return appearance;
928 }
929 catch (SqlException e)
930 {
931 m_log.Error(e.ToString());
932 }
933
934 return null;
935 }
936
937 /// <summary>
938 /// Insert/Update a agent row in the DB.
939 /// </summary>
940 /// <param name="agentdata">agentdata.</param>
941 private void InsertUpdateAgentRow(UserAgentData agentdata)
942 {
943 string sql = @"
944
945IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
946 BEGIN
947 UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos
948 WHERE UUID = @UUID
949 END
950ELSE
951 BEGIN
952 INSERT INTO
953 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
954 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
955 END
956";
957
958 using (SqlConnection conn = new SqlConnection(m_connectionString))
959 using (SqlCommand command = new SqlCommand(sql, conn))
960 {
961 command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID));
962 command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID));
963 command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID));
964 command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP));
965 command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort));
966 command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline));
967 command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime));
968 command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime));
969 command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region));
970 command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle));
971 command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">"));
972 conn.Open();
973
974 command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
975 try
976 {
977 if (command.ExecuteNonQuery() > 0)
978 {
979 command.Transaction.Commit();
980 return;
981 }
982
983 command.Transaction.Rollback();
984 return;
985 }
986 catch (Exception e)
987 {
988 command.Transaction.Rollback();
989 m_log.Error(e.ToString());
990 return;
991 }
992 }
993
994 }
995
996 /// <summary>
997 /// Reads an agent row from a database reader
998 /// </summary>
999 /// <param name="reader">An active database reader</param>
1000 /// <returns>A user session agent</returns>
1001 private UserAgentData readAgentRow(SqlDataReader reader)
1002 {
1003 UserAgentData retval = new UserAgentData();
1004
1005 if (reader.Read())
1006 {
1007 // Agent IDs
1008 retval.ProfileID = new UUID((Guid)reader["UUID"]);
1009 retval.SessionID = new UUID((Guid)reader["sessionID"]);
1010 retval.SecureSessionID = new UUID((Guid)reader["secureSessionID"]);
1011
1012 // Agent Who?
1013 retval.AgentIP = (string)reader["agentIP"];
1014 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
1015 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
1016
1017 // Login/Logout times (UNIX Epoch)
1018 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
1019 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
1020
1021 // Current position
1022 retval.Region = new UUID((Guid)reader["currentRegion"]);
1023 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
1024 Vector3 tmp_v;
1025 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
1026 retval.Position = tmp_v;
1027
1028 }
1029 else
1030 {
1031 return null;
1032 }
1033 return retval;
1034 }
1035
1036 /// <summary>
1037 /// Creates a new user and inserts it into the database
1038 /// </summary>
1039 /// <param name="uuid">User ID</param>
1040 /// <param name="username">First part of the login</param>
1041 /// <param name="lastname">Second part of the login</param>
1042 /// <param name="email">Email of person</param>
1043 /// <param name="passwordHash">A salted hash of the users password</param>
1044 /// <param name="passwordSalt">The salt used for the password hash</param>
1045 /// <param name="homeRegion">A regionHandle of the users home region</param>
1046 /// <param name="homeLocX">Home region position vector</param>
1047 /// <param name="homeLocY">Home region position vector</param>
1048 /// <param name="homeLocZ">Home region position vector</param>
1049 /// <param name="homeLookAtX">Home region 'look at' vector</param>
1050 /// <param name="homeLookAtY">Home region 'look at' vector</param>
1051 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
1052 /// <param name="created">Account created (unix timestamp)</param>
1053 /// <param name="lastlogin">Last login (unix timestamp)</param>
1054 /// <param name="inventoryURI">Users inventory URI</param>
1055 /// <param name="assetURI">Users asset URI</param>
1056 /// <param name="canDoMask">I can do mask</param>
1057 /// <param name="wantDoMask">I want to do mask</param>
1058 /// <param name="aboutText">Profile text</param>
1059 /// <param name="firstText">Firstlife text</param>
1060 /// <param name="profileImage">UUID for profile image</param>
1061 /// <param name="firstImage">UUID for firstlife image</param>
1062 /// <param name="webLoginKey">web login key</param>
1063 /// <param name="homeRegionID">homeregion UUID</param>
1064 /// <param name="godLevel">has the user godlevel</param>
1065 /// <param name="userFlags">unknown</param>
1066 /// <param name="customType">unknown</param>
1067 /// <param name="partnerID">UUID of partner</param>
1068 /// <returns>Success?</returns>
1069 private void InsertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
1070 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
1071 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
1072 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
1073 string aboutText, string firstText,
1074 UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID,
1075 int godLevel, int userFlags, string customType, UUID partnerID)
1076 {
1077 string sql = string.Format(@"INSERT INTO {0}
1078 ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt],
1079 [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX],
1080 [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI],
1081 [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText],
1082 [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey],
1083 [homeRegionID], [userFlags], [godLevel], [customType], [partner])
1084 VALUES
1085 (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt,
1086 @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX,
1087 @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI,
1088 @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText,
1089 @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey,
1090 @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName);
1091
1092 try
1093 {
1094 using (SqlConnection conn = new SqlConnection(m_connectionString))
1095 using (SqlCommand command = new SqlCommand(sql, conn))
1096 {
1097 command.Parameters.Add(database.CreateParameter("UUID", uuid));
1098 command.Parameters.Add(database.CreateParameter("username", username));
1099 command.Parameters.Add(database.CreateParameter("lastname", lastname));
1100 command.Parameters.Add(database.CreateParameter("email", email));
1101 command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash));
1102 command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt));
1103 command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion));
1104 command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX));
1105 command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY));
1106 command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ));
1107 command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX));
1108 command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY));
1109 command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ));
1110 command.Parameters.Add(database.CreateParameter("created", created));
1111 command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin));
1112 command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI));
1113 command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI));
1114 command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask));
1115 command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask));
1116 command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText));
1117 command.Parameters.Add(database.CreateParameter("profileFirstText", firstText));
1118 command.Parameters.Add(database.CreateParameter("profileImage", profileImage));
1119 command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage));
1120 command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey));
1121 command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID));
1122 command.Parameters.Add(database.CreateParameter("userFlags", userFlags));
1123 command.Parameters.Add(database.CreateParameter("godLevel", godLevel));
1124 command.Parameters.Add(database.CreateParameter("customType", customType));
1125 command.Parameters.Add(database.CreateParameter("partner", partnerID));
1126 conn.Open();
1127 command.ExecuteNonQuery();
1128 return;
1129 }
1130 }
1131 catch (Exception e)
1132 {
1133 m_log.Error(e.ToString());
1134 return;
1135 }
1136 }
1137
1138 /// <summary>
1139 /// Reads a user profile from an active data reader
1140 /// </summary>
1141 /// <param name="reader">An active database reader</param>
1142 /// <returns>A user profile</returns>
1143 private static UserProfileData ReadUserRow(SqlDataReader reader)
1144 {
1145 UserProfileData retval = new UserProfileData();
1146
1147 if (reader.Read())
1148 {
1149 retval.ID = new UUID((Guid)reader["UUID"]);
1150 retval.FirstName = (string)reader["username"];
1151 retval.SurName = (string)reader["lastname"];
1152 if (reader.IsDBNull(reader.GetOrdinal("email")))
1153 retval.Email = "";
1154 else
1155 retval.Email = (string)reader["email"];
1156
1157 retval.PasswordHash = (string)reader["passwordHash"];
1158 retval.PasswordSalt = (string)reader["passwordSalt"];
1159
1160 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
1161 retval.HomeLocation = new Vector3(
1162 Convert.ToSingle(reader["homeLocationX"].ToString()),
1163 Convert.ToSingle(reader["homeLocationY"].ToString()),
1164 Convert.ToSingle(reader["homeLocationZ"].ToString()));
1165 retval.HomeLookAt = new Vector3(
1166 Convert.ToSingle(reader["homeLookAtX"].ToString()),
1167 Convert.ToSingle(reader["homeLookAtY"].ToString()),
1168 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
1169
1170 if (reader.IsDBNull(reader.GetOrdinal("homeRegionID")))
1171 retval.HomeRegionID = UUID.Zero;
1172 else
1173 retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]);
1174
1175 retval.Created = Convert.ToInt32(reader["created"].ToString());
1176 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
1177
1178 if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI")))
1179 retval.UserInventoryURI = "";
1180 else
1181 retval.UserInventoryURI = (string)reader["userInventoryURI"];
1182
1183 if (reader.IsDBNull(reader.GetOrdinal("userAssetURI")))
1184 retval.UserAssetURI = "";
1185 else
1186 retval.UserAssetURI = (string)reader["userAssetURI"];
1187
1188 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
1189 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
1190
1191
1192 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
1193 retval.AboutText = "";
1194 else
1195 retval.AboutText = (string)reader["profileAboutText"];
1196
1197 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
1198 retval.FirstLifeAboutText = "";
1199 else
1200 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
1201
1202 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
1203 retval.Image = UUID.Zero;
1204 else
1205 retval.Image = new UUID((Guid)reader["profileImage"]);
1206
1207 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
1208 retval.Image = UUID.Zero;
1209 else
1210 retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]);
1211
1212 if (reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
1213 retval.WebLoginKey = UUID.Zero;
1214 else
1215 retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]);
1216
1217 retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString());
1218 retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString());
1219 if (reader.IsDBNull(reader.GetOrdinal("customType")))
1220 retval.CustomType = "";
1221 else
1222 retval.CustomType = reader["customType"].ToString();
1223
1224 if (reader.IsDBNull(reader.GetOrdinal("partner")))
1225 retval.Partner = UUID.Zero;
1226 else
1227 retval.Partner = new UUID((Guid)reader["partner"]);
1228 }
1229 else
1230 {
1231 return null;
1232 }
1233 return retval;
1234 }
1235 #endregion
1236 }
1237
1238}