From 3aa9e786de1091bdff37404db4fb210105ff837a Mon Sep 17 00:00:00 2001 From: dahlia Date: Wed, 24 Jun 2015 04:08:34 -0700 Subject: Thanks Gavin Hird for a fix for Mantis 0007604: DeleteClassifiedRecord exception PGSQL --- OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs | 247 ++++++++++++++-------------- 1 file changed, 124 insertions(+), 123 deletions(-) (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs index a003e07..70c67dd 100644 --- a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs +++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs @@ -37,45 +37,46 @@ using Npgsql; namespace OpenSim.Data.PGSQL { - public class UserProfilesData: IProfilesData + public class UserProfilesData : IProfilesData { static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); protected PGSQLManager m_database; - + #region Properites string ConnectionString { - get; set; + get; + set; } - + protected virtual Assembly Assembly { get { return GetType().Assembly; } } - + #endregion Properties - + #region class Member Functions public UserProfilesData(string connectionString) { ConnectionString = connectionString; Init(); } - + void Init() { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) { dbcon.Open(); - + Migration m = new Migration(dbcon, Assembly, "UserProfiles"); m.Update(); m_database = new PGSQLManager(ConnectionString); } } #endregion Member Functions - + #region Classifieds Queries /// /// Gets the classified records. @@ -89,7 +90,7 @@ namespace OpenSim.Data.PGSQL public OSDArray GetClassifiedRecords(UUID creatorId) { OSDArray data = new OSDArray(); - + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) { string query = @"SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id"; @@ -97,15 +98,15 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", creatorId)); - using( NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) { - if(reader.HasRows) + if (reader.HasRows) { while (reader.Read()) { OSDMap n = new OSDMap(); UUID Id = UUID.Zero; - + string Name = null; try { @@ -127,7 +128,7 @@ namespace OpenSim.Data.PGSQL } return data; } - + public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) { string query = string.Empty; @@ -149,35 +150,35 @@ namespace OpenSim.Data.PGSQL WHERE NOT EXISTS ( SELECT * FROM upsert )"; - if(string.IsNullOrEmpty(ad.ParcelName)) + if (string.IsNullOrEmpty(ad.ParcelName)) ad.ParcelName = "Unknown"; - if(ad.ParcelId == null) + if (ad.ParcelId == null) ad.ParcelId = UUID.Zero; - if(string.IsNullOrEmpty(ad.Description)) + if (string.IsNullOrEmpty(ad.Description)) ad.Description = "No Description"; - + DateTime epoch = new DateTime(1970, 1, 1); DateTime now = DateTime.Now; TimeSpan epochnow = now - epoch; TimeSpan duration; DateTime expiration; TimeSpan epochexp; - - if(ad.Flags == 2) + + if (ad.Flags == 2) { - duration = new TimeSpan(7,0,0,0); + duration = new TimeSpan(7, 0, 0, 0); expiration = now.Add(duration); epochexp = expiration - epoch; } else { - duration = new TimeSpan(365,0,0,0); + duration = new TimeSpan(365, 0, 0, 0); expiration = now.Add(duration); epochexp = expiration - epoch; } ad.CreationDate = (int)epochnow.TotalSeconds; ad.ExpirationDate = (int)epochexp.TotalSeconds; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -200,7 +201,7 @@ namespace OpenSim.Data.PGSQL cmd.Parameters.Add(m_database.CreateParameter("ParcelName", ad.ParcelName.ToString())); cmd.Parameters.Add(m_database.CreateParameter("Flags", (int)Convert.ToInt32(ad.Flags))); cmd.Parameters.Add(m_database.CreateParameter("ListingPrice", (int)Convert.ToInt32(ad.Price))); - + cmd.ExecuteNonQuery(); } } @@ -214,19 +215,19 @@ namespace OpenSim.Data.PGSQL return true; } - + public bool DeleteClassifiedRecord(UUID recordId) { string query = string.Empty; - - query = @"DELETE FROM classifieds WHERE classifieduuid = :ClasifiedId ;"; - + + query = @"DELETE FROM classifieds WHERE classifieduuid = :ClassifiedId ;"; + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) { dbcon.Open(); - + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("ClassifiedId", recordId)); @@ -242,14 +243,14 @@ namespace OpenSim.Data.PGSQL return true; } - + public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result) { string query = string.Empty; - + query += "SELECT * FROM classifieds WHERE "; query += "classifieduuid = :AdId"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -258,10 +259,10 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("AdId", ad.ClassifiedId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader()) { - if(reader.Read ()) + if (reader.Read()) { ad.CreatorId = DBGuid.FromDB(reader["creatoruuid"]); ad.ParcelId = DBGuid.FromDB(reader["parceluuid"]); @@ -291,7 +292,7 @@ namespace OpenSim.Data.PGSQL return true; } - public static UUID GetUUID(object uuidValue) + public static UUID GetUUID(object uuidValue) { UUID ret = UUID.Zero; @@ -307,11 +308,11 @@ namespace OpenSim.Data.PGSQL public OSDArray GetAvatarPicks(UUID avatarId) { string query = string.Empty; - + query += "SELECT pickuuid, name FROM userpicks WHERE "; query += "creatoruuid = :Id"; OSDArray data = new OSDArray(); - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -320,17 +321,17 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader()) { - if(reader.HasRows) + if (reader.HasRows) { while (reader.Read()) { OSDMap record = new OSDMap(); - - record.Add("pickuuid",OSD.FromUUID(DBGuid.FromDB(reader["pickuuid"]))); - record.Add("name",OSD.FromString((string)reader["name"])); + + record.Add("pickuuid", OSD.FromUUID(DBGuid.FromDB(reader["pickuuid"]))); + record.Add("name", OSD.FromString((string)reader["name"])); data.Add(record); } } @@ -345,16 +346,16 @@ namespace OpenSim.Data.PGSQL return data; } - + public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId) { string query = string.Empty; UserProfilePick pick = new UserProfilePick(); - + query += "SELECT * FROM userpicks WHERE "; query += "creatoruuid = :CreatorId AND "; query += "pickuuid = :PickId"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -364,18 +365,18 @@ namespace OpenSim.Data.PGSQL { cmd.Parameters.Add(m_database.CreateParameter("CreatorId", avatarId)); cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader()) { - if(reader.HasRows) + if (reader.HasRows) { reader.Read(); - + string description = (string)reader["description"]; - + if (string.IsNullOrEmpty(description)) description = "No description given."; - + pick.PickId = DBGuid.FromDB(reader["pickuuid"]); pick.CreatorId = DBGuid.FromDB(reader["creatoruuid"]); pick.ParcelId = DBGuid.FromDB(reader["parceluuid"]); @@ -383,7 +384,7 @@ namespace OpenSim.Data.PGSQL pick.GlobalPos = (string)reader["posglobal"].ToString(); pick.TopPick = Convert.ToBoolean(reader["toppick"]); pick.Enabled = Convert.ToBoolean(reader["enabled"]); - pick.Name = reader["name"].ToString (); + pick.Name = reader["name"].ToString(); pick.Desc = reader["description"].ToString(); pick.ParcelName = reader["user"].ToString(); pick.OriginalName = reader["originalname"].ToString(); @@ -402,9 +403,9 @@ namespace OpenSim.Data.PGSQL return pick; } - + public bool UpdatePicksRecord(UserProfilePick pick) - { + { string query = string.Empty; @@ -422,7 +423,7 @@ namespace OpenSim.Data.PGSQL :Original,:SimName,:GlobalPos,:SortOrder,:Enabled WHERE NOT EXISTS ( SELECT * FROM upsert )"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -439,11 +440,11 @@ namespace OpenSim.Data.PGSQL cmd.Parameters.Add(m_database.CreateParameter("SnapshotId", pick.SnapshotId)); cmd.Parameters.Add(m_database.CreateParameter("User", pick.ParcelName)); cmd.Parameters.Add(m_database.CreateParameter("Original", pick.OriginalName)); - cmd.Parameters.Add(m_database.CreateParameter("SimName",pick.SimName)); + cmd.Parameters.Add(m_database.CreateParameter("SimName", pick.SimName)); cmd.Parameters.Add(m_database.CreateParameter("GlobalPos", pick.GlobalPos)); cmd.Parameters.Add(m_database.CreateParameter("SortOrder", pick.SortOrder)); cmd.Parameters.Add(m_database.CreateParameter("Enabled", pick.Enabled)); - + cmd.ExecuteNonQuery(); } } @@ -456,24 +457,24 @@ namespace OpenSim.Data.PGSQL return true; } - + public bool DeletePicksRecord(UUID pickId) { string query = string.Empty; - + query += "DELETE FROM userpicks WHERE "; query += "pickuuid = :PickId"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) { dbcon.Open(); - + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId)); - + cmd.ExecuteNonQuery(); } } @@ -488,18 +489,18 @@ namespace OpenSim.Data.PGSQL } #endregion Picks Queries - + #region Avatar Notes Queries public bool GetAvatarNotes(ref UserProfileNotes notes) { // WIP string query = string.Empty; - + query += "SELECT notes FROM usernotes WHERE "; query += "useruuid = :Id AND "; query += "targetuuid = :TargetId"; OSDArray data = new OSDArray(); - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -509,10 +510,10 @@ namespace OpenSim.Data.PGSQL { cmd.Parameters.Add(m_database.CreateParameter("Id", notes.UserId)); cmd.Parameters.Add(m_database.CreateParameter("TargetId", notes.TargetId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { reader.Read(); notes.Notes = OSD.FromString((string)reader["notes"]); @@ -528,12 +529,12 @@ namespace OpenSim.Data.PGSQL return true; } - + public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result) - { + { string query = string.Empty; bool remove; - + if (string.IsNullOrEmpty(note.Notes)) { remove = true; @@ -553,7 +554,7 @@ namespace OpenSim.Data.PGSQL SELECT * FROM upsert )"; } - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -561,12 +562,12 @@ namespace OpenSim.Data.PGSQL dbcon.Open(); using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { - if(!remove) + if (!remove) cmd.Parameters.Add(m_database.CreateParameter("Notes", note.Notes)); cmd.Parameters.Add(m_database.CreateParameter("TargetId", note.TargetId)); cmd.Parameters.Add(m_database.CreateParameter("UserId", note.UserId)); - + cmd.ExecuteNonQuery(); } } @@ -577,20 +578,20 @@ namespace OpenSim.Data.PGSQL return false; } - return true; + return true; } #endregion Avatar Notes Queries - + #region Avatar Properties public bool GetAvatarProperties(ref UserProfileProperties props, ref string result) { string query = string.Empty; - + query += "SELECT * FROM userprofile WHERE "; query += "useruuid = :Id"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -599,10 +600,10 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { // m_log.DebugFormat("[PROFILES_DATA]" + // ": Getting data for {0}.", props.UserId); @@ -623,7 +624,7 @@ namespace OpenSim.Data.PGSQL { //m_log.DebugFormat("[PROFILES_DATA]" + // ": No data for {0}", props.UserId); - + props.WebUrl = string.Empty; props.ImageId = UUID.Zero; props.AboutText = string.Empty; @@ -707,11 +708,11 @@ namespace OpenSim.Data.PGSQL return true; } - + public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result) - { + { string query = string.Empty; - + query += "UPDATE userprofile SET "; query += "\"profileURL\"=:profileURL, "; query += "\"profileImage\"=:image, "; @@ -719,7 +720,7 @@ namespace OpenSim.Data.PGSQL query += "\"profileFirstImage\"=:firstlifeimage,"; query += "\"profileFirstText\"=:firstlifetext "; query += "WHERE \"useruuid\"=:uuid"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -733,14 +734,14 @@ namespace OpenSim.Data.PGSQL cmd.Parameters.Add(m_database.CreateParameter("firstlifeimage", props.FirstLifeImageId)); cmd.Parameters.Add(m_database.CreateParameter("firstlifetext", props.FirstLifeText)); cmd.Parameters.Add(m_database.CreateParameter("uuid", props.UserId)); - + cmd.ExecuteNonQuery(); } } } catch (Exception e) { - m_log.Error("[PROFILES_DATA]: AgentPropertiesUpdate exception ", e); + m_log.Error("[PROFILES_DATA]: AgentPropertiesUpdate exception ", e); return false; } @@ -748,13 +749,13 @@ namespace OpenSim.Data.PGSQL } #endregion Avatar Properties - + #region Avatar Interests public bool UpdateAvatarInterests(UserProfileProperties up, ref string result) - { + { string query = string.Empty; - + query += "UPDATE userprofile SET "; query += "\"profileWantToMask\"=:WantMask, "; query += "\"profileWantToText\"=:WantText,"; @@ -762,7 +763,7 @@ namespace OpenSim.Data.PGSQL query += "\"profileSkillsText\"=:SkillsText, "; query += "\"profileLanguages\"=:Languages "; query += "WHERE \"useruuid\"=:uuid"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -776,7 +777,7 @@ namespace OpenSim.Data.PGSQL cmd.Parameters.Add(m_database.CreateParameter("SkillsText", up.SkillsText)); cmd.Parameters.Add(m_database.CreateParameter("Languages", up.Language)); cmd.Parameters.Add(m_database.CreateParameter("uuid", up.UserId)); - + cmd.ExecuteNonQuery(); } } @@ -804,13 +805,13 @@ namespace OpenSim.Data.PGSQL { dbcon.Open(); - using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon)) + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"classifieds\""), dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { while (reader.Read()) { @@ -823,13 +824,13 @@ namespace OpenSim.Data.PGSQL dbcon.Close(); dbcon.Open(); - using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"userpicks\""), dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { while (reader.Read()) { @@ -838,19 +839,19 @@ namespace OpenSim.Data.PGSQL } } } - + dbcon.Close(); dbcon.Open(); query = "SELECT \"profileImage\", \"profileFirstImage\" FROM \"userprofile\" WHERE \"useruuid\" = :Id"; - using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"userpicks\""), dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { while (reader.Read()) { @@ -869,19 +870,19 @@ namespace OpenSim.Data.PGSQL return data; } - + #region User Preferences public bool GetUserPreferences(ref UserPreferences pref, ref string result) { string query = string.Empty; - + query += "SELECT imviaemail,visible,email FROM "; query += "usersettings WHERE "; query += "useruuid = :Id"; - + OSDArray data = new OSDArray(); - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -890,10 +891,10 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Id", pref.UserId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader()) { - if(reader.HasRows) + if (reader.HasRows) { reader.Read(); bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail); @@ -924,16 +925,16 @@ namespace OpenSim.Data.PGSQL return true; } - public bool UpdateUserPreferences(ref UserPreferences pref, ref string result) - { + public bool UpdateUserPreferences(ref UserPreferences pref, ref string result) + { string query = string.Empty; - + query += "UPDATE usersettings SET "; query += "imviaemail=:ImViaEmail, "; query += "visible=:Visible, "; query += "email=:Email "; query += "WHERE useruuid=:uuid"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -961,17 +962,17 @@ namespace OpenSim.Data.PGSQL } #endregion User Preferences - + #region Integration public bool GetUserAppData(ref UserAppData props, ref string result) { string query = string.Empty; - + query += "SELECT * FROM userdata WHERE "; query += "\"UserId\" = :Id AND "; query += "\"TagId\" = :TagId"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -981,10 +982,10 @@ namespace OpenSim.Data.PGSQL { cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId)); cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId)); - + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - if(reader.HasRows) + if (reader.HasRows) { reader.Read(); props.DataKey = (string)reader["DataKey"]; @@ -996,8 +997,8 @@ namespace OpenSim.Data.PGSQL query += ":UserId,"; query += ":TagId,"; query += ":DataKey,"; - query += ":DataVal) "; - + query += ":DataVal) "; + using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) { put.Parameters.Add(m_database.CreateParameter("UserId", props.UserId)); @@ -1023,16 +1024,16 @@ namespace OpenSim.Data.PGSQL } public bool SetUserAppData(UserAppData props, ref string result) - { + { string query = string.Empty; - + query += "UPDATE userdata SET "; query += "\"TagId\" = :TagId, "; query += "\"DataKey\" = :DataKey, "; query += "\"DataVal\" = :DataVal WHERE "; query += "\"UserId\" = :UserId AND "; query += "\"TagId\" = :TagId"; - + try { using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) @@ -1041,9 +1042,9 @@ namespace OpenSim.Data.PGSQL using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("UserId", props.UserId.ToString())); - cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId.ToString ())); - cmd.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString ())); - cmd.Parameters.Add(m_database.CreateParameter("DataVal", props.DataKey.ToString ())); + cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId.ToString())); + cmd.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString())); + cmd.Parameters.Add(m_database.CreateParameter("DataVal", props.DataKey.ToString())); cmd.ExecuteNonQuery(); } -- cgit v1.1