From 702249358badda5413e67ee0267063c2e2a61498 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Tue, 4 Nov 2008 14:54:42 +0000 Subject: implement email field for MySQL and SQLite From: Sean Dague --- OpenSim/Data/MySQL/MySQLManager.cs | 29 ++++++++++++++----------- OpenSim/Data/MySQL/MySQLUserData.cs | 4 ++-- OpenSim/Data/MySQL/Resources/007_UserStore.sql | 5 +++++ OpenSim/Data/SQLite/Resources/008_UserStore.sql | 5 +++++ OpenSim/Data/SQLite/SQLiteUserData.cs | 3 +++ OpenSim/Data/Tests/BasicUserTest.cs | 6 ++++- 6 files changed, 36 insertions(+), 16 deletions(-) create mode 100644 OpenSim/Data/MySQL/Resources/007_UserStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/008_UserStore.sql (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs index 5f830bb..7096efa 100644 --- a/OpenSim/Data/MySQL/MySQLManager.cs +++ b/OpenSim/Data/MySQL/MySQLManager.cs @@ -540,6 +540,7 @@ namespace OpenSim.Data.MySQL retval.ID = id; retval.FirstName = (string) reader["username"]; retval.SurName = (string) reader["lastname"]; + retval.Email = (string) reader["email"]; retval.PasswordHash = (string) reader["passwordHash"]; retval.PasswordSalt = (string) reader["passwordSalt"]; @@ -768,7 +769,7 @@ namespace OpenSim.Data.MySQL /// UUID for firstlife image /// Ignored /// Success? - public bool insertUserRow(UUID uuid, string username, string lastname, string passwordHash, + public bool insertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash, string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, @@ -777,14 +778,14 @@ namespace OpenSim.Data.MySQL { m_log.Debug("[MySQLManager]: Fetching profile for " + uuid.ToString()); string sql = - "INSERT INTO users (`UUID`, `username`, `lastname`, `passwordHash`, `passwordSalt`, `homeRegion`, "; + "INSERT INTO users (`UUID`, `username`, `lastname`, `email`, `passwordHash`, `passwordSalt`, `homeRegion`, "; sql += "`homeLocationX`, `homeLocationY`, `homeLocationZ`, `homeLookAtX`, `homeLookAtY`, `homeLookAtZ`, `created`, "; sql += "`lastLogin`, `userInventoryURI`, `userAssetURI`, `profileCanDoMask`, `profileWantDoMask`, `profileAboutText`, "; sql += "`profileFirstText`, `profileImage`, `profileFirstImage`, `webLoginKey`, `userFlags`, `godLevel`, `customType`, `partner`) VALUES "; - sql += "(?UUID, ?username, ?lastname, ?passwordHash, ?passwordSalt, ?homeRegion, "; + sql += "(?UUID, ?username, ?lastname, ?email, ?passwordHash, ?passwordSalt, ?homeRegion, "; sql += "?homeLocationX, ?homeLocationY, ?homeLocationZ, ?homeLookAtX, ?homeLookAtY, ?homeLookAtZ, ?created, "; sql += @@ -793,10 +794,11 @@ namespace OpenSim.Data.MySQL Dictionary parameters = new Dictionary(); parameters["?UUID"] = uuid.ToString(); - parameters["?username"] = username.ToString(); - parameters["?lastname"] = lastname.ToString(); - parameters["?passwordHash"] = passwordHash.ToString(); - parameters["?passwordSalt"] = passwordSalt.ToString(); + parameters["?username"] = username; + parameters["?lastname"] = lastname; + parameters["?email"] = email; + parameters["?passwordHash"] = passwordHash; + parameters["?passwordSalt"] = passwordSalt; parameters["?homeRegion"] = homeRegion.ToString(); parameters["?homeLocationX"] = homeLocX.ToString(); parameters["?homeLocationY"] = homeLocY.ToString(); @@ -869,14 +871,14 @@ namespace OpenSim.Data.MySQL /// UUID for firstlife image /// UUID for weblogin Key /// Success? - public bool updateUserRow(UUID uuid, string username, string lastname, string passwordHash, + public bool updateUserRow(UUID uuid, string username, string lastname, string email, string passwordHash, string passwordSalt, UInt64 homeRegion, UUID homeRegionID, float homeLocX, float homeLocY, float homeLocZ, float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, string aboutText, string firstText, UUID profileImage, UUID firstImage, UUID webLoginKey, int userFlags, int godLevel, string customType, UUID partner) { - string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname "; + string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname, `email` = ?email "; sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , "; sql += "`homeRegion` = ?homeRegion , `homeRegionID` = ?homeRegionID, `homeLocationX` = ?homeLocationX , "; sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , "; @@ -892,10 +894,11 @@ namespace OpenSim.Data.MySQL Dictionary parameters = new Dictionary(); parameters["?UUID"] = uuid.ToString(); - parameters["?username"] = username.ToString(); - parameters["?lastname"] = lastname.ToString(); - parameters["?passwordHash"] = passwordHash.ToString(); - parameters["?passwordSalt"] = passwordSalt.ToString(); + parameters["?username"] = username; + parameters["?lastname"] = lastname; + parameters["?email"] = email; + parameters["?passwordHash"] = passwordHash; + parameters["?passwordSalt"] = passwordSalt; parameters["?homeRegion"] = homeRegion.ToString(); parameters["?homeRegionID"] = homeRegionID.ToString(); parameters["?homeLocationX"] = homeLocX.ToString(); diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs index 6baf4b6..47670d2 100644 --- a/OpenSim/Data/MySQL/MySQLUserData.cs +++ b/OpenSim/Data/MySQL/MySQLUserData.cs @@ -642,7 +642,7 @@ namespace OpenSim.Data.MySQL try { - dbm.Manager.insertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, + dbm.Manager.insertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt, user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, user.HomeLocation.Z, user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, @@ -697,7 +697,7 @@ namespace OpenSim.Data.MySQL MySQLSuperManager dbm = GetLockedConnection("UpdateUserProfile"); try { - dbm.Manager.updateUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, + dbm.Manager.updateUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt, user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y, user.HomeLocation.Z, user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin, diff --git a/OpenSim/Data/MySQL/Resources/007_UserStore.sql b/OpenSim/Data/MySQL/Resources/007_UserStore.sql new file mode 100644 index 0000000..3ab5261 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/007_UserStore.sql @@ -0,0 +1,5 @@ +BEGIN; + +ALTER TABLE users add email varchar(250); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/008_UserStore.sql b/OpenSim/Data/SQLite/Resources/008_UserStore.sql new file mode 100644 index 0000000..97da818 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/008_UserStore.sql @@ -0,0 +1,5 @@ +BEGIN TRANSACTION; + +ALTER TABLE users add email varchar(250); + +COMMIT; diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs index 2f0863e..50a1d73 100644 --- a/OpenSim/Data/SQLite/SQLiteUserData.cs +++ b/OpenSim/Data/SQLite/SQLiteUserData.cs @@ -710,6 +710,7 @@ namespace OpenSim.Data.SQLite SQLiteUtil.createCol(users, "UUID", typeof (String)); SQLiteUtil.createCol(users, "username", typeof (String)); SQLiteUtil.createCol(users, "surname", typeof (String)); + SQLiteUtil.createCol(users, "email", typeof (String)); SQLiteUtil.createCol(users, "passwordHash", typeof (String)); SQLiteUtil.createCol(users, "passwordSalt", typeof (String)); @@ -812,6 +813,7 @@ namespace OpenSim.Data.SQLite user.ID = tmp; user.FirstName = (String) row["username"]; user.SurName = (String) row["surname"]; + user.Email = (String) row["email"]; user.PasswordHash = (String) row["passwordHash"]; user.PasswordSalt = (String) row["passwordSalt"]; @@ -864,6 +866,7 @@ namespace OpenSim.Data.SQLite row["UUID"] = Util.ToRawUuidString(user.ID); row["username"] = user.FirstName; row["surname"] = user.SurName; + row["email"] = user.Email; row["passwordHash"] = user.PasswordHash; row["passwordSalt"] = user.PasswordSalt; diff --git a/OpenSim/Data/Tests/BasicUserTest.cs b/OpenSim/Data/Tests/BasicUserTest.cs index 4ca7290..5e6e4da 100644 --- a/OpenSim/Data/Tests/BasicUserTest.cs +++ b/OpenSim/Data/Tests/BasicUserTest.cs @@ -135,6 +135,9 @@ namespace OpenSim.Data.Tests Assert.That(user1,Is.EqualTo(u1a.ID)); Assert.That(user2,Is.EqualTo(u2a.ID)); Assert.That(user3,Is.EqualTo(u3a.ID)); + + // and one email test + Assert.That(u1.Email, Is.EqualTo(u1a.Email)); } [Test] @@ -296,7 +299,8 @@ namespace OpenSim.Data.Tests u.FirstName = fname; u.SurName = lname; u.PasswordHash = "NOTAHASH"; - u.PasswordSalt = "NOTSALT"; + u.PasswordSalt = "NOTSALT"; + u.Email = "nobody@nodomain.nocc"; // MUST specify at least these 5 parameters or an exception is raised return u; -- cgit v1.1