/* * 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 MySql.Data.MySqlClient; using OpenMetaverse; using OpenMetaverse.StructuredData; using log4net; namespace OpenSim.Data.MySQL { 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 (MySqlConnection dbcon = new MySqlConnection(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = ?Id"; dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", creatorId); using( MySqlDataReader 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 = string.Empty; query += "INSERT INTO classifieds ("; query += "`classifieduuid`,"; query += "`creatoruuid`,"; query += "`creationdate`,"; query += "`expirationdate`,"; query += "`category`,"; query += "`name`,"; query += "`description`,"; query += "`parceluuid`,"; query += "`parentestate`,"; query += "`snapshotuuid`,"; query += "`simname`,"; query += "`posglobal`,"; query += "`parcelname`,"; query += "`classifiedflags`,"; query += "`priceforlisting`) "; query += "VALUES ("; query += "?ClassifiedId,"; query += "?CreatorId,"; query += "?CreatedDate,"; query += "?ExpirationDate,"; query += "?Category,"; query += "?Name,"; query += "?Description,"; query += "?ParcelId,"; query += "?ParentEstate,"; query += "?SnapshotId,"; query += "?SimName,"; query += "?GlobalPos,"; query += "?ParcelName,"; query += "?Flags,"; query += "?ListingPrice ) "; query += "ON DUPLICATE KEY UPDATE "; query += "category=?Category, "; query += "expirationdate=?ExpirationDate, "; query += "name=?Name, "; query += "description=?Description, "; query += "parentestate=?ParentEstate, "; query += "posglobal=?GlobalPos, "; query += "parcelname=?ParcelName, "; query += "classifiedflags=?Flags, "; query += "priceforlisting=?ListingPrice, "; query += "snapshotuuid=?SnapshotId"; 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 "; query += "classifieduuid = ?ClasifiedId"; try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?AdId", ad.ClassifiedId.ToString()); using (MySqlDataReader reader = cmd.ExecuteReader()) { if(reader.Read ()) { ad.CreatorId = new UUID(reader.GetGuid("creatoruuid")); ad.ParcelId = new UUID(reader.GetGuid("parceluuid")); ad.SnapshotId = new UUID(reader.GetGuid("snapshotuuid")); ad.CreationDate = Convert.ToInt32(reader["creationdate"]); ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); ad.Flags = (byte)reader.GetUInt32("classifiedflags"); ad.Category = reader.GetInt32("category"); ad.Price = reader.GetInt16("priceforlisting"); ad.Name = reader.GetString("name"); ad.Description = reader.GetString("description"); ad.SimName = reader.GetString("simname"); ad.GlobalPos = reader.GetString("posglobal"); ad.ParcelName = reader.GetString("parcelname"); } } } dbcon.Close(); } } catch (Exception e) { m_log.DebugFormat("[PROFILES_DATA]" + ": GetPickInfo exception {0}", e.Message); } return true; } #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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); using (MySqlDataReader 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?CreatorId", avatarId.ToString()); cmd.Parameters.AddWithValue("?PickId", pickId.ToString()); using (MySqlDataReader 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 ("; query += "?PickId,"; query += "?CreatorId,"; query += "?TopPick,"; query += "?ParcelId,"; query += "?Name,"; query += "?Desc,"; query += "?SnapshotId,"; query += "?User,"; query += "?Original,"; query += "?SimName,"; query += "?GlobalPos,"; query += "?SortOrder,"; query += "?Enabled) "; query += "ON DUPLICATE KEY UPDATE "; query += "parceluuid=?ParcelId,"; query += "name=?Name,"; query += "description=?Desc,"; query += "snapshotuuid=?SnapshotId,"; query += "pickuuid=?PickId,"; query += "posglobal=?GlobalPos"; try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString()); cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString()); using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) { reader.Read(); notes.Notes = OSD.FromString((string)reader["notes"]); } else { notes.Notes = OSD.FromString(""); } } } } } 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 ( "; query += "?UserId,"; query += "?TargetId,"; query += "?Notes )"; query += "ON DUPLICATE KEY "; query += "UPDATE "; query += "notes=?Notes"; } try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); using (MySqlDataReader 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 (MySqlCommand put = new MySqlCommand(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 += "profileURL=?profileURL, "; query += "profileImage=?image, "; query += "profileAboutText=?abouttext,"; query += "profileFirstImage=?firstlifeimage,"; query += "profileFirstText=?firstlifetext "; query += "WHERE useruuid=?uuid"; try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?profileURL", props.WebUrl); 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) { while (reader.Read()) { data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); } } } } dbcon.Close(); dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); using (MySqlDataReader 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 (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); using (MySqlDataReader 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); using (MySqlDataReader 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 { dbcon.Close(); dbcon.Open(); query = "INSERT INTO usersettings VALUES "; query += "(?uuid,'false','false', ?Email)"; using (MySqlCommand put = new MySqlCommand(query, dbcon)) { put.Parameters.AddWithValue("?Email", pref.EMail); put.Parameters.AddWithValue("?uuid", pref.UserId.ToString()); 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString()); using (MySqlDataReader 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 (MySqlCommand put = new MySqlCommand(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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) { dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(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 } }