From ff8a76825841533bdc5d534b6f58b2ab964ea6c6 Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Sat, 12 Oct 2013 16:33:45 -0500 Subject: Fernando Oliveira's Postgress SQL Server Data Connector as a single commit. * Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs * PostgreSQL data access implementation * PostgreSQL dll binarie and RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres SQL Type fixes * Postgres SQL Connection string * Data type issues * more fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to internal csharp types * More data type fix (PostgreSQL fields are case sensitive) :( * more field case sensitive fixes * changed the migration files to be case sensitive for fields. * fixed fields case * finished converting, now search for hidden bugs. * some more fixes * bool type fixed * more case fixes; * creatorID case fixed * case fields fixed * fixed default now() for TMStamp fields with don't allow nulls. * fix case sensitve for Region name and Estate name * fixed case for names for search * fix class name Error * Bug fixed on select and migrations * Un-Reverting my change due to Postgres issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for integration with Diva Distro * Added System.Core to prebuild.xml for PG project * Configured to make DIff for Push to OpenSim Project * Diffs only to PostgreSQL mods. --- OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs | 1075 +++++++++++++++++++++++++++ 1 file changed, 1075 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs (limited to 'OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs new file mode 100644 index 0000000..e3cbf7f --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs @@ -0,0 +1,1075 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Data; +using System.Reflection; +using OpenSim.Data; +using OpenSim.Framework; +using OpenMetaverse; +using OpenMetaverse.StructuredData; +using log4net; +using Npgsql; + +namespace OpenSim.Data.PGSQL +{ + public class UserProfilesData: IProfilesData + { + static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + #region Properites + string ConnectionString + { + get; set; + } + + protected object Lock + { + 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(); + } + } + #endregion Member Functions + + #region Classifieds Queries + /// + /// Gets the classified records. + /// + /// + /// Array of classified records + /// + /// + /// Creator identifier. + /// + 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"; + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", creatorId); + using( NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) + { + if(reader.HasRows) + { + while (reader.Read()) + { + OSDMap n = new OSDMap(); + UUID Id = UUID.Zero; + + string Name = null; + try + { + UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id); + Name = Convert.ToString(reader["name"]); + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": UserAccount exception {0}", e.Message); + } + n.Add("classifieduuid", OSD.FromUUID(Id)); + n.Add("name", OSD.FromString(Name)); + data.Add(n); + } + } + } + } + } + return data; + } + + public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) + { + string query = @"INSERT INTO classifieds ( ""classifieduuid"",""creatoruuid"", ""creationdate"", ""expirationdate"", ""category"", + ""name"", ""description"", ""parceluuid"", ""parentestate"", ""snapshotuuid"", ""simname"", + ""posglobal"", ""parcelname"", ""classifiedflags"", ""priceforlisting"") + Select :ClassifiedId, :CreatorId, :CreatedDate, :ExpirationDate, :Category, + :Name, :Description, :ParcelId, :ParentEstate, :SnapshotId, :SimName + :GlobalPos, :ParcelName, :Flags, :ListingPrice + Where not exists( Select ""classifieduuid"" from classifieds where ""classifieduuid"" = :ClassifiedId ); + + update classifieds + set category =:Category, + expirationdate = :ExpirationDate, + name = :Name, + description = :Description, + parentestate = :ParentEstate, + posglobal = :GlobalPos, + parcelname = :ParcelName, + classifiedflags = :Flags, + priceforlisting = :ListingPrice, + snapshotuuid = :SnapshotId + where classifieduuid = :ClassifiedId ; + "; + + if(string.IsNullOrEmpty(ad.ParcelName)) + ad.ParcelName = "Unknown"; + if(ad.ParcelId == null) + ad.ParcelId = UUID.Zero; + 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) + { + duration = new TimeSpan(7,0,0,0); + expiration = now.Add(duration); + epochexp = expiration - epoch; + } + else + { + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("ClassifiedId", ad.ClassifiedId.ToString()); + cmd.Parameters.AddWithValue("CreatorId", ad.CreatorId.ToString()); + cmd.Parameters.AddWithValue("CreatedDate", ad.CreationDate.ToString()); + cmd.Parameters.AddWithValue("ExpirationDate", ad.ExpirationDate.ToString()); + cmd.Parameters.AddWithValue("Category", ad.Category.ToString()); + cmd.Parameters.AddWithValue("Name", ad.Name.ToString()); + cmd.Parameters.AddWithValue("Description", ad.Description.ToString()); + cmd.Parameters.AddWithValue("ParcelId", ad.ParcelId.ToString()); + cmd.Parameters.AddWithValue("ParentEstate", ad.ParentEstate.ToString()); + cmd.Parameters.AddWithValue("SnapshotId", ad.SnapshotId.ToString ()); + cmd.Parameters.AddWithValue("SimName", ad.SimName.ToString()); + cmd.Parameters.AddWithValue("GlobalPos", ad.GlobalPos.ToString()); + cmd.Parameters.AddWithValue("ParcelName", ad.ParcelName.ToString()); + cmd.Parameters.AddWithValue("Flags", ad.Flags.ToString()); + cmd.Parameters.AddWithValue("ListingPrice", ad.Price.ToString ()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": ClassifiedesUpdate exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + + + public bool DeleteClassifiedRecord(UUID recordId) + { + string query = string.Empty; + + query = @"DELETE FROM classifieds WHERE classifieduuid = :ClasifiedId ;"; + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("ClassifiedId", recordId.ToString()); + + lock(Lock) + { + cmd.ExecuteNonQuery(); + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": DeleteClassifiedRecord exception {0}", e.Message); + return false; + } + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("AdId", ad.ClassifiedId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if(reader.Read ()) + { + ad.CreatorId = GetUUID(reader["creatoruuid"]); + ad.ParcelId = GetUUID(reader["parceluuid"]); + ad.SnapshotId = GetUUID(reader["snapshotuuid"]); + ad.CreationDate = Convert.ToInt32(reader["creationdate"]); + ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); + ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); + ad.Flags = (byte)Convert.ToInt16(reader["classifiedflags"]); + ad.Category = Convert.ToInt32(reader["category"]); + ad.Price = Convert.ToInt16(reader["priceforlisting"]); + ad.Name = reader["name"].ToString(); + ad.Description = reader["description"].ToString(); + ad.SimName = reader["simname"].ToString(); + ad.GlobalPos = reader["posglobal"].ToString(); + ad.ParcelName = reader["parcelname"].ToString(); + + } + } + } + dbcon.Close(); + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": GetPickInfo exception {0}", e.Message); + } + return true; + } + + public static UUID GetUUID( object uuidValue ) { + + UUID ret = UUID.Zero; + + UUID.TryParse(uuidValue.ToString(), out ret); + + return ret; + } + + + #endregion Classifieds Queries + + #region Picks Queries + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", avatarId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if(reader.HasRows) + { + while (reader.Read()) + { + OSDMap record = new OSDMap(); + + record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"])); + record.Add("name",OSD.FromString((string)reader["name"])); + data.Add(record); + } + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": GetAvatarPicks exception {0}", e.Message); + } + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("CreatorId", avatarId.ToString()); + cmd.Parameters.AddWithValue("PickId", pickId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if(reader.HasRows) + { + reader.Read(); + + string description = (string)reader["description"]; + + if (string.IsNullOrEmpty(description)) + description = "No description given."; + + UUID.TryParse((string)reader["pickuuid"], out pick.PickId); + UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId); + UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId); + UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId); + pick.GlobalPos = (string)reader["posglobal"]; + bool.TryParse((string)reader["toppick"], out pick.TopPick); + bool.TryParse((string)reader["enabled"], out pick.Enabled); + pick.Name = (string)reader["name"]; + pick.Desc = description; + pick.User = (string)reader["user"]; + pick.OriginalName = (string)reader["originalname"]; + pick.SimName = (string)reader["simname"]; + pick.SortOrder = (int)reader["sortorder"]; + } + } + } + dbcon.Close(); + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": GetPickInfo exception {0}", e.Message); + } + return pick; + } + + public bool UpdatePicksRecord(UserProfilePick pick) + { + string query = string.Empty; + + query = @"INSERT INTO userpicks VALUES ( :PickId, :CreatorId, :TopPick, :ParcelId,:Name, :Desc, :SnapshotId,:User, + :Original, :SimName, :GlobalPos, :SortOrder, :Enabled) + where not exists ( select pickid from userpicks where pickid = :pickid); + + Update userpicks + set parceluuid = :ParcelId, + name = :Name, + description = :Desc, + snapshotuuid = :SnapshotId, + pickuuid = :PickId, + posglobal = :GlobalPos + where pickid = :PickId; + "; + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("PickId", pick.PickId.ToString()); + cmd.Parameters.AddWithValue("CreatorId", pick.CreatorId.ToString()); + cmd.Parameters.AddWithValue("TopPick", pick.TopPick.ToString()); + cmd.Parameters.AddWithValue("ParcelId", pick.ParcelId.ToString()); + cmd.Parameters.AddWithValue("Name", pick.Name.ToString()); + cmd.Parameters.AddWithValue("Desc", pick.Desc.ToString()); + cmd.Parameters.AddWithValue("SnapshotId", pick.SnapshotId.ToString()); + cmd.Parameters.AddWithValue("User", pick.User.ToString()); + cmd.Parameters.AddWithValue("Original", pick.OriginalName.ToString()); + cmd.Parameters.AddWithValue("SimName",pick.SimName.ToString()); + cmd.Parameters.AddWithValue("GlobalPos", pick.GlobalPos); + cmd.Parameters.AddWithValue("SortOrder", pick.SortOrder.ToString ()); + cmd.Parameters.AddWithValue("Enabled", pick.Enabled.ToString()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": UpdateAvatarNotes exception {0}", e.Message); + return false; + } + 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.AddWithValue("PickId", pickId.ToString()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": DeleteUserPickRecord exception {0}", e.Message); + return false; + } + return true; + } + #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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", notes.UserId.ToString()); + cmd.Parameters.AddWithValue("TargetId", notes.TargetId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + reader.Read(); + notes.Notes = OSD.FromString((string)reader["notes"]); + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": GetAvatarNotes exception {0}", e.Message); + } + return true; + } + + public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result) + { + string query = string.Empty; + bool remove; + + if(string.IsNullOrEmpty(note.Notes)) + { + remove = true; + query += "DELETE FROM usernotes WHERE "; + query += "useruuid=:UserId AND "; + query += "targetuuid=:TargetId"; + } + else + { + remove = false; + query = @"INSERT INTO usernotes VALUES ( :UserId, :TargetId, :Notes ) + where not exists ( Select useruuid from usernotes where useruuid = :UserId and targetuuid = :TargetId ); + + update usernotes + set notes = :Notes + where useruuid = :UserId + and targetuuid = :TargetId; + "; + } + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + if(!remove) + cmd.Parameters.AddWithValue("Notes", note.Notes); + cmd.Parameters.AddWithValue("TargetId", note.TargetId.ToString ()); + cmd.Parameters.AddWithValue("UserId", note.UserId.ToString()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": UpdateAvatarNotes exception {0}", e.Message); + return false; + } + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": Getting data for {0}.", props.UserId); + reader.Read(); + props.WebUrl = (string)reader["profileURL"]; + UUID.TryParse((string)reader["profileImage"], out props.ImageId); + props.AboutText = (string)reader["profileAboutText"]; + UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId); + props.FirstLifeText = (string)reader["profileFirstText"]; + UUID.TryParse((string)reader["profilePartner"], out props.PartnerId); + props.WantToMask = (int)reader["profileWantToMask"]; + props.WantToText = (string)reader["profileWantToText"]; + props.SkillsMask = (int)reader["profileSkillsMask"]; + props.SkillsText = (string)reader["profileSkillsText"]; + props.Language = (string)reader["profileLanguages"]; + } + else + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": No data for {0}", props.UserId); + + props.WebUrl = string.Empty; + props.ImageId = UUID.Zero; + props.AboutText = string.Empty; + props.FirstLifeImageId = UUID.Zero; + props.FirstLifeText = string.Empty; + props.PartnerId = UUID.Zero; + props.WantToMask = 0; + props.WantToText = string.Empty; + props.SkillsMask = 0; + props.SkillsText = string.Empty; + props.Language = string.Empty; + props.PublishProfile = false; + props.PublishMature = false; + + query = "INSERT INTO userprofile ("; + query += "useruuid, "; + query += "profilePartner, "; + query += "profileAllowPublish, "; + query += "profileMaturePublish, "; + query += "profileURL, "; + query += "profileWantToMask, "; + query += "profileWantToText, "; + query += "profileSkillsMask, "; + query += "profileSkillsText, "; + query += "profileLanguages, "; + query += "profileImage, "; + query += "profileAboutText, "; + query += "profileFirstImage, "; + query += "profileFirstText) VALUES ("; + query += ":userId, "; + query += ":profilePartner, "; + query += ":profileAllowPublish, "; + query += ":profileMaturePublish, "; + query += ":profileURL, "; + query += ":profileWantToMask, "; + query += ":profileWantToText, "; + query += ":profileSkillsMask, "; + query += ":profileSkillsText, "; + query += ":profileLanguages, "; + query += ":profileImage, "; + query += ":profileAboutText, "; + query += ":profileFirstImage, "; + query += ":profileFirstText)"; + + dbcon.Close(); + dbcon.Open(); + + using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) + { + put.Parameters.AddWithValue("userId", props.UserId.ToString()); + put.Parameters.AddWithValue("profilePartner", props.PartnerId.ToString()); + put.Parameters.AddWithValue("profileAllowPublish", props.PublishProfile); + put.Parameters.AddWithValue("profileMaturePublish", props.PublishMature); + put.Parameters.AddWithValue("profileURL", props.WebUrl); + put.Parameters.AddWithValue("profileWantToMask", props.WantToMask); + put.Parameters.AddWithValue("profileWantToText", props.WantToText); + put.Parameters.AddWithValue("profileSkillsMask", props.SkillsMask); + put.Parameters.AddWithValue("profileSkillsText", props.SkillsText); + put.Parameters.AddWithValue("profileLanguages", props.Language); + put.Parameters.AddWithValue("profileImage", props.ImageId.ToString()); + put.Parameters.AddWithValue("profileAboutText", props.AboutText); + put.Parameters.AddWithValue("profileFirstImage", props.FirstLifeImageId.ToString()); + put.Parameters.AddWithValue("profileFirstText", props.FirstLifeText); + + put.ExecuteNonQuery(); + } + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": Requst properties exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + + public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result) + { + string query = string.Empty; + + query += "UPDATE userprofile SET "; + query += "profilePartner=:profilePartner, "; + query += "profileURL=:profileURL, "; + query += "profileImage=:image, "; + query += "profileAboutText=:abouttext,"; + query += "profileFirstImage=:firstlifeimage,"; + query += "profileFirstText=:firstlifetext "; + query += "WHERE useruuid=:uuid"; + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("profileURL", props.WebUrl); + cmd.Parameters.AddWithValue("profilePartner", props.PartnerId.ToString()); + cmd.Parameters.AddWithValue("image", props.ImageId.ToString()); + cmd.Parameters.AddWithValue("abouttext", props.AboutText); + cmd.Parameters.AddWithValue("firstlifeimage", props.FirstLifeImageId.ToString()); + cmd.Parameters.AddWithValue("firstlifetext", props.FirstLifeText); + cmd.Parameters.AddWithValue("uuid", props.UserId.ToString()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": AgentPropertiesUpdate exception {0}", e.Message); + + return false; + } + return true; + } + #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,"; + query += "profileSkillsMask=:SkillsMask,"; + query += "profileSkillsText=:SkillsText, "; + query += "profileLanguages=:Languages "; + query += "WHERE useruuid=:uuid"; + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("WantMask", up.WantToMask); + cmd.Parameters.AddWithValue("WantText", up.WantToText); + cmd.Parameters.AddWithValue("SkillsMask", up.SkillsMask); + cmd.Parameters.AddWithValue("SkillsText", up.SkillsText); + cmd.Parameters.AddWithValue("Languages", up.Language); + cmd.Parameters.AddWithValue("uuid", up.UserId.ToString()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": AgentInterestsUpdate exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + #endregion Avatar Interests + + public OSDArray GetUserImageAssets(UUID avatarId) + { + OSDArray data = new OSDArray(); + string query = "SELECT \"snapshotuuid\" FROM {0} WHERE \"creatoruuid\" = :Id"; + + // Get classified image assets + + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon)) + { + cmd.Parameters.AddWithValue("Id", avatarId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + while (reader.Read()) + { + data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); + } + } + } + } + + dbcon.Close(); + dbcon.Open(); + + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) + { + cmd.Parameters.AddWithValue("Id", avatarId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + while (reader.Read()) + { + data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); + } + } + } + } + + dbcon.Close(); + dbcon.Open(); + + query = "SELECT \"profileImage\", \"profileFirstImage\" FROM \"userprofile\" WHERE \"useruuid\" = :Id"; + + using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) + { + cmd.Parameters.AddWithValue("Id", avatarId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + while (reader.Read()) + { + data.Add(new OSDString((string)reader["profileImage"].ToString ())); + data.Add(new OSDString((string)reader["profileFirstImage"].ToString ())); + } + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": GetAvatarNotes exception {0}", e.Message); + } + return data; + } + + #region User Preferences + public OSDArray GetUserPreferences(UUID avatarId) + { + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", avatarId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if(reader.HasRows) + { + reader.Read(); + OSDMap record = new OSDMap(); + + record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"])); + record.Add("visible",OSD.FromString((string)reader["visible"])); + record.Add("email",OSD.FromString((string)reader["email"])); + data.Add(record); + } + else + { + using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) + { + query = "INSERT INTO usersettings VALUES "; + query += "(:Id,'false','false', '')"; + + lock(Lock) + { + put.ExecuteNonQuery(); + } + } + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": Get preferences exception {0}", e.Message); + } + return data; + } + + public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId ) + { + string query = string.Empty; + + query += "UPDATE userpsettings SET "; + query += "imviaemail=:ImViaEmail, "; + query += "visible=:Visible,"; + query += "WHERE useruuid=:uuid"; + + try + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("ImViaEmail", emailIm.ToString().ToLower ()); + cmd.Parameters.AddWithValue("WantText", visible.ToString().ToLower ()); + cmd.Parameters.AddWithValue("uuid", avatarId.ToString()); + + lock(Lock) + { + cmd.ExecuteNonQuery(); + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": AgentInterestsUpdate exception {0}", e.Message); + return false; + } + return true; + } + #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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); + cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString()); + + using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.HasRows) + { + reader.Read(); + props.DataKey = (string)reader["DataKey"]; + props.DataVal = (string)reader["DataVal"]; + } + else + { + query += "INSERT INTO userdata VALUES ( "; + query += ":UserId,"; + query += ":TagId,"; + query += ":DataKey,"; + query += ":DataVal) "; + + using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) + { + put.Parameters.AddWithValue("Id", props.UserId.ToString()); + put.Parameters.AddWithValue("TagId", props.TagId.ToString()); + put.Parameters.AddWithValue("DataKey", props.DataKey.ToString()); + put.Parameters.AddWithValue("DataVal", props.DataVal.ToString()); + + lock(Lock) + { + put.ExecuteNonQuery(); + } + } + } + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": Requst application data exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + + 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)) + { + dbcon.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("UserId", props.UserId.ToString()); + cmd.Parameters.AddWithValue("TagId", props.TagId.ToString ()); + cmd.Parameters.AddWithValue("DataKey", props.DataKey.ToString ()); + cmd.Parameters.AddWithValue("DataVal", props.DataKey.ToString ()); + + lock(Lock) + { + cmd.ExecuteNonQuery(); + } + } + } + } + catch (Exception e) + { + m_log.DebugFormat("[PROFILES_DATA]" + + ": SetUserData exception {0}", e.Message); + return false; + } + return true; + } + #endregion Integration + } +} + -- cgit v1.1