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