From 1cb1245d847fad5a088e0bb727433ee1e53d81da Mon Sep 17 00:00:00 2001
From: BlueWall
Date: Fri, 7 Jun 2013 20:14:39 -0400
Subject: SQLite support for UserProfiles
---
.../Data/SQLite/Resources/UserProfiles.migrations | 90 ++
OpenSim/Data/SQLite/SQLiteUserProfilesData.cs | 904 +++++++++++++++++++++
2 files changed, 994 insertions(+)
create mode 100644 OpenSim/Data/SQLite/Resources/UserProfiles.migrations
create mode 100644 OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
(limited to 'OpenSim/Data')
diff --git a/OpenSim/Data/SQLite/Resources/UserProfiles.migrations b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
new file mode 100644
index 0000000..16581f6
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
@@ -0,0 +1,90 @@
+:VERSION 1 # -------------------------------
+
+begin;
+
+CREATE TABLE IF NOT EXISTS classifieds (
+ classifieduuid char(36) NOT NULL PRIMARY KEY,
+ creatoruuid char(36) NOT NULL,
+ creationdate int(20) NOT NULL,
+ expirationdate int(20) NOT NULL,
+ category varchar(20) NOT NULL,
+ name varchar(255) NOT NULL,
+ description text NOT NULL,
+ parceluuid char(36) NOT NULL,
+ parentestate int(11) NOT NULL,
+ snapshotuuid char(36) NOT NULL,
+ simname varchar(255) NOT NULL,
+ posglobal varchar(255) NOT NULL,
+ parcelname varchar(255) NOT NULL,
+ classifiedflags int(8) NOT NULL,
+ priceforlisting int(5) NOT NULL
+);
+
+commit;
+
+begin;
+
+CREATE TABLE IF NOT EXISTS usernotes (
+ useruuid varchar(36) NOT NULL,
+ targetuuid varchar(36) NOT NULL,
+ notes text NOT NULL,
+ UNIQUE (useruuid,targetuuid) ON CONFLICT REPLACE
+);
+
+commit;
+
+begin;
+
+CREATE TABLE IF NOT EXISTS userpicks (
+ pickuuid varchar(36) NOT NULL PRIMARY KEY,
+ creatoruuid varchar(36) NOT NULL,
+ toppick int NOT NULL,
+ parceluuid varchar(36) NOT NULL,
+ name varchar(255) NOT NULL,
+ description text NOT NULL,
+ snapshotuuid varchar(36) NOT NULL,
+ user varchar(255) NOT NULL,
+ originalname varchar(255) NOT NULL,
+ simname varchar(255) NOT NULL,
+ posglobal varchar(255) NOT NULL,
+ sortorder int(2) NOT NULL,
+ enabled int NOT NULL
+);
+
+commit;
+
+begin;
+
+CREATE TABLE IF NOT EXISTS userprofile (
+ useruuid varchar(36) NOT NULL PRIMARY KEY,
+ profilePartner varchar(36) NOT NULL,
+ profileAllowPublish binary(1) NOT NULL,
+ profileMaturePublish binary(1) NOT NULL,
+ profileURL varchar(255) NOT NULL,
+ profileWantToMask int(3) NOT NULL,
+ profileWantToText text NOT NULL,
+ profileSkillsMask int(3) NOT NULL,
+ profileSkillsText text NOT NULL,
+ profileLanguages text NOT NULL,
+ profileImage varchar(36) NOT NULL,
+ profileAboutText text NOT NULL,
+ profileFirstImage varchar(36) NOT NULL,
+ profileFirstText text NOT NULL
+);
+
+commit;
+
+:VERSION 2 # -------------------------------
+
+begin;
+
+CREATE TABLE IF NOT EXISTS userdata (
+ UserId char(36) NOT NULL,
+ TagId varchar(64) NOT NULL,
+ DataKey varchar(255),
+ DataVal varchar(255),
+ PRIMARY KEY (UserId,TagId)
+);
+
+commit;
+
diff --git a/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
new file mode 100644
index 0000000..cc1dac1
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
@@ -0,0 +1,904 @@
+/*
+ * 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.Collections.Generic;
+using System.Data;
+using System.Reflection;
+using log4net;
+#if CSharpSqlite
+using Community.CsharpSqlite.Sqlite;
+#else
+using Mono.Data.Sqlite;
+#endif
+using OpenMetaverse;
+using OpenMetaverse.StructuredData;
+using OpenSim.Framework;
+using OpenSim.Region.Framework.Interfaces;
+
+namespace OpenSim.Data.SQLite
+{
+ public class SQLiteUserProfilesData: IProfilesData
+ {
+ private static readonly ILog m_log =
+ LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
+
+ private SqliteConnection m_connection;
+ private string m_connectionString;
+
+ private FieldInfo[] m_Fields;
+ private Dictionary<string, FieldInfo> m_FieldMap =
+ new Dictionary<string, FieldInfo>();
+
+ protected virtual Assembly Assembly
+ {
+ get { return GetType().Assembly; }
+ }
+
+ public SQLiteUserProfilesData()
+ {
+ }
+
+ public SQLiteUserProfilesData(string connectionString)
+ {
+ Initialise(connectionString);
+ }
+
+ public void Initialise(string connectionString)
+ {
+ if (Util.IsWindows())
+ Util.LoadArchSpecificWindowsDll("sqlite3.dll");
+
+ m_connectionString = connectionString;
+
+ m_log.Info("[PROFILES_DATA]: Sqlite - connecting: "+m_connectionString);
+
+ m_connection = new SqliteConnection(m_connectionString);
+ m_connection.Open();
+
+ Migration m = new Migration(m_connection, Assembly, "UserProfiles");
+ m.Update();
+ }
+
+ private string[] FieldList
+ {
+ get { return new List<string>(m_FieldMap.Keys).ToArray(); }
+ }
+
+ #region IProfilesData implementation
+ public OSDArray GetClassifiedRecords(UUID creatorId)
+ {
+ OSDArray data = new OSDArray();
+ string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id";
+ IDataReader reader = null;
+
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", creatorId);
+ reader = cmd.ExecuteReader();
+ }
+
+ 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);
+ }
+
+ reader.Close();
+
+ return data;
+ }
+ public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result)
+ {
+ string query = string.Empty;
+
+ query += "INSERT OR REPLACE 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 ) ";
+
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":ClassifiedId", recordId.ToString());
+
+ 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)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+
+ query += "SELECT * FROM classifieds WHERE ";
+ query += "classifieduuid = :AdId";
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":AdId", ad.ClassifiedId.ToString());
+
+ using (reader = cmd.ExecuteReader())
+ {
+ if(reader.Read ())
+ {
+ ad.CreatorId = new UUID(reader["creatoruuid"].ToString());
+ ad.ParcelId = new UUID(reader["parceluuid"].ToString ());
+ ad.SnapshotId = new UUID(reader["snapshotuuid"].ToString ());
+ ad.CreationDate = Convert.ToInt32(reader["creationdate"]);
+ ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]);
+ ad.ParentEstate = Convert.ToInt32(reader["parentestate"]);
+ ad.Flags = (byte) Convert.ToUInt32(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();
+ }
+ }
+ }
+ }
+ catch (Exception e)
+ {
+ m_log.DebugFormat("[PROFILES_DATA]" +
+ ": GetPickInfo exception {0}", e.Message);
+ }
+ return true;
+ }
+
+ public OSDArray GetAvatarPicks(UUID avatarId)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+
+ query += "SELECT `pickuuid`,`name` FROM userpicks WHERE ";
+ query += "creatoruuid = :Id";
+ OSDArray data = new OSDArray();
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
+
+ using (reader = cmd.ExecuteReader())
+ {
+ 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)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+ UserProfilePick pick = new UserProfilePick();
+
+ query += "SELECT * FROM userpicks WHERE ";
+ query += "creatoruuid = :CreatorId AND ";
+ query += "pickuuid = :PickId";
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":CreatorId", avatarId.ToString());
+ cmd.Parameters.AddWithValue(":PickId", pickId.ToString());
+
+ using (reader = cmd.ExecuteReader())
+ {
+
+ while (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"].ToString(), out pick.TopPick);
+ bool.TryParse((string)reader["enabled"].ToString(), 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"];
+ }
+ }
+ }
+ }
+ 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 OR REPLACE INTO userpicks (";
+ query += "pickuuid, ";
+ query += "creatoruuid, ";
+ query += "toppick, ";
+ query += "parceluuid, ";
+ query += "name, ";
+ query += "description, ";
+ query += "snapshotuuid, ";
+ query += "user, ";
+ query += "originalname, ";
+ query += "simname, ";
+ query += "posglobal, ";
+ query += "sortorder, ";
+ query += "enabled ) ";
+ query += "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) ";
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ int top_pick;
+ int.TryParse(pick.TopPick.ToString(), out top_pick);
+ int enabled;
+ int.TryParse(pick.Enabled.ToString(), out enabled);
+
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":PickId", pick.PickId.ToString());
+ cmd.Parameters.AddWithValue(":CreatorId", pick.CreatorId.ToString());
+ cmd.Parameters.AddWithValue(":TopPick", top_pick);
+ 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", enabled);
+
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ 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;
+ }
+
+ public bool GetAvatarNotes(ref UserProfileNotes notes)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+
+ query += "SELECT `notes` FROM usernotes WHERE ";
+ query += "useruuid = :Id AND ";
+ query += "targetuuid = :TargetId";
+ OSDArray data = new OSDArray();
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", notes.UserId.ToString());
+ cmd.Parameters.AddWithValue(":TargetId", notes.TargetId.ToString());
+
+ using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ {
+ while (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 OR REPLACE INTO usernotes VALUES ( ";
+ query += ":UserId,";
+ query += ":TargetId,";
+ query += ":Notes )";
+ }
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+
+ 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;
+ }
+
+ public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+
+ query += "SELECT * FROM userprofile WHERE ";
+ query += "useruuid = :Id";
+
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
+
+
+ try
+ {
+ reader = cmd.ExecuteReader();
+ }
+ catch(Exception e)
+ {
+ m_log.DebugFormat("[PROFILES_DATA]" +
+ ": GetAvatarProperties exception {0}", e.Message);
+ result = e.Message;
+ return false;
+ }
+ if(reader != null && reader.Read())
+ {
+ m_log.DebugFormat("[PROFILES_DATA]" +
+ ": Getting data for {0}.", props.UserId);
+
+ 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)";
+
+ using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
+ {
+ put.CommandText = query;
+ 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();
+ }
+ }
+ }
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ 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;
+ }
+
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ 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;
+ }
+ public bool GetUserAppData(ref UserAppData props, ref string result)
+ {
+ IDataReader reader = null;
+ string query = string.Empty;
+
+ query += "SELECT * FROM `userdata` WHERE ";
+ query += "UserId = :Id AND ";
+ query += "TagId = :TagId";
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
+ cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
+
+ using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ {
+ if(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 (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
+ {
+ 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());
+
+ 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ 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 ());
+
+ cmd.ExecuteNonQuery();
+ }
+ }
+ catch (Exception e)
+ {
+ m_log.DebugFormat("[PROFILES_DATA]" +
+ ": SetUserData exception {0}", e.Message);
+ return false;
+ }
+ return true;
+ }
+ public OSDArray GetUserImageAssets(UUID avatarId)
+ {
+ IDataReader reader = null;
+ OSDArray data = new OSDArray();
+ string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = :Id";
+
+ // Get classified image assets
+
+
+ try
+ {
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
+
+ using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ {
+ while(reader.Read())
+ {
+ data.Add(new OSDString((string)reader["snapshotuuid"].ToString()));
+ }
+ }
+ }
+
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
+
+ using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ {
+ if(reader.Read())
+ {
+ data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
+ }
+ }
+ }
+
+ query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = :Id";
+
+ using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
+ {
+ cmd.CommandText = query;
+ cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
+
+ using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ {
+ if(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;
+ }
+ #endregion
+ }
+}
+
--
cgit v1.1