From 134f86e8d5c414409631b25b8c6f0ee45fbd8631 Mon Sep 17 00:00:00 2001 From: David Walter Seikel Date: Thu, 3 Nov 2016 21:44:39 +1000 Subject: Initial update to OpenSim 0.8.2.1 source code. --- OpenSim/Data/SQLite/Properties/AssemblyInfo.cs | 4 +- .../Data/SQLite/Resources/AgentPrefs.migrations | 36 + .../Data/SQLite/Resources/EstateStore.migrations | 9 + .../Data/SQLite/Resources/HGTravelStore.migrations | 18 + .../Data/SQLite/Resources/RegionStore.migrations | 37 + .../Data/SQLite/Resources/UserProfiles.migrations | 102 +++ OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs | 60 ++ OpenSim/Data/SQLite/SQLiteAssetData.cs | 67 +- OpenSim/Data/SQLite/SQLiteFriendsData.cs | 2 +- OpenSim/Data/SQLite/SQLiteGridUserData.cs | 4 + OpenSim/Data/SQLite/SQLiteHGTravelData.cs | 82 ++ OpenSim/Data/SQLite/SQLiteInventoryStore.cs | 4 +- OpenSim/Data/SQLite/SQLiteSimulationData.cs | 183 ++-- OpenSim/Data/SQLite/SQLiteUserProfilesData.cs | 981 +++++++++++++++++++++ 14 files changed, 1491 insertions(+), 98 deletions(-) create mode 100644 OpenSim/Data/SQLite/Resources/AgentPrefs.migrations create mode 100644 OpenSim/Data/SQLite/Resources/HGTravelStore.migrations create mode 100644 OpenSim/Data/SQLite/Resources/UserProfiles.migrations create mode 100644 OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteHGTravelData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteUserProfilesData.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs index c9a8553..d2e62d2 100644 --- a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs +++ b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs @@ -61,5 +61,5 @@ using System.Runtime.InteropServices; // You can specify all the values or you can default the Revision and Build Numbers // by using the '*' as shown below: -[assembly : AssemblyVersion("0.7.5.*")] -[assembly : AssemblyFileVersion("0.6.5.0")] +[assembly : AssemblyVersion("0.8.2.*")] + diff --git a/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations b/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations new file mode 100644 index 0000000..7e0525d --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations @@ -0,0 +1,36 @@ +:VERSION 1 + +BEGIN TRANSACTION; + +CREATE TABLE `AgentPrefs` ( + `PrincipalID` CHAR(36) NOT NULL, + `AccessPrefs` CHAR(2) NOT NULL DEFAULT 'M', + `HoverHeight` DOUBLE(30, 27) NOT NULL DEFAULT 0, + `Language` CHAR(5) NOT NULL DEFAULT 'en-us', + `LanguageIsPublic` BOOLEAN NOT NULL DEFAULT 1, + `PermEveryone` INT(6) NOT NULL DEFAULT 0, + `PermGroup` INT(6) NOT NULL DEFAULT 0, + `PermNextOwner` INT(6) NOT NULL DEFAULT 532480, + UNIQUE KEY `PrincipalID` (`PrincipalID`), + PRIMARY KEY(`PrincipalID`)); + +COMMIT; + +:VERSION 2 + +BEGIN; + +CREATE TABLE AgentPrefs( + PrincipalID CHAR(36) NOT NULL, + AccessPrefs CHAR(2) NOT NULL DEFAULT 'M', + HoverHeight DOUBLE(30, 27) NOT NULL DEFAULT 0, + Language CHAR(5) NOT NULL DEFAULT 'en-us', + LanguageIsPublic BOOLEAN NOT NULL DEFAULT 1, + PermEveryone INT(6) NOT NULL DEFAULT 0, + PermGroup INT(6) NOT NULL DEFAULT 0, + PermNextOwner INT(6) NOT NULL DEFAULT 532480, + UNIQUE(PrincipalID), + PRIMARY KEY(PrincipalID) +); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/EstateStore.migrations b/OpenSim/Data/SQLite/Resources/EstateStore.migrations index 62f6464..0aec49b 100644 --- a/OpenSim/Data/SQLite/Resources/EstateStore.migrations +++ b/OpenSim/Data/SQLite/Resources/EstateStore.migrations @@ -86,3 +86,12 @@ begin; alter table estate_settings add column DenyMinors tinyint not null default 0; commit; + +:VERSION 9 + +begin; +alter table estate_settings add column AllowLandmark tinyint not null default '1'; +alter table estate_settings add column AllowParcelChanges tinyint not null default '1'; +alter table estate_settings add column AllowSetHome tinyint not null default '1'; +commit; + diff --git a/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations b/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations new file mode 100644 index 0000000..02612ce --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations @@ -0,0 +1,18 @@ +:VERSION 2 # -------------------------- + +BEGIN; + +CREATE TABLE hg_traveling_data( + SessionID VARCHAR(36) NOT NULL, + UserID VARCHAR(36) NOT NULL, + GridExternalName VARCHAR(255) NOT NULL DEFAULT "", + ServiceToken VARCHAR(255) NOT NULL DEFAULT "", + ClientIPAddress VARCHAR(16) NOT NULL DEFAULT "", + MyIPAddress VARCHAR(16) NOT NULL DEFAULT "", + TMStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(SessionID), + UNIQUE(UserID) +); + +COMMIT; + diff --git a/OpenSim/Data/SQLite/Resources/RegionStore.migrations b/OpenSim/Data/SQLite/Resources/RegionStore.migrations index e872977..901068f 100644 --- a/OpenSim/Data/SQLite/Resources/RegionStore.migrations +++ b/OpenSim/Data/SQLite/Resources/RegionStore.migrations @@ -575,3 +575,40 @@ CREATE TABLE `regionenvironment` ( ); COMMIT; + +:VERSION 27 +BEGIN; +ALTER TABLE prims ADD COLUMN DynAttrs TEXT; +COMMIT; + +:VERSION 28 + +BEGIN; + +ALTER TABLE prims ADD COLUMN `PhysicsShapeType` tinyint(4) NOT NULL default '0'; +ALTER TABLE prims ADD COLUMN `Density` double NOT NULL default '1000'; +ALTER TABLE prims ADD COLUMN `GravityModifier` double NOT NULL default '1'; +ALTER TABLE prims ADD COLUMN `Friction` double NOT NULL default '0.6'; +ALTER TABLE prims ADD COLUMN `Restitution` double NOT NULL default '0.5'; + +COMMIT; + +:VERSION 29 #---------------- Keyframes + +BEGIN; + +ALTER TABLE prims ADD COLUMN `KeyframeMotion` blob; + +COMMIT; + +:VERSION 30 #---------------- Save Attachment info + +BEGIN; + +ALTER TABLE prims ADD COLUMN AttachedPosX double default '0'; +ALTER TABLE prims ADD COLUMN AttachedPosY double default '0'; +ALTER TABLE prims ADD COLUMN AttachedPosZ double default '0'; +ALTER TABLE primshapes ADD COLUMN LastAttachPoint int not null default '0'; + +COMMIT; + diff --git a/OpenSim/Data/SQLite/Resources/UserProfiles.migrations b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations new file mode 100644 index 0000000..86434e8 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations @@ -0,0 +1,102 @@ +: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; + + +:VERSION 3 # ------------------------------- + +begin; +CREATE TABLE IF NOT EXISTS usersettings ( + useruuid char(36) NOT NULL, + imviaemail binary(1) NOT NULL, + visible binary(1) NOT NULL, + email varchar(254) NOT NULL, + PRIMARY KEY (useruuid) +) +commit; \ No newline at end of file diff --git a/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs b/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs new file mode 100644 index 0000000..d22393d --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs @@ -0,0 +1,60 @@ +/* + * 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; +using System.Collections.Generic; +using System.Data; +using OpenMetaverse; +using OpenSim.Framework; +#if CSharpSqlite + using Community.CsharpSqlite.Sqlite; +#else + using Mono.Data.Sqlite; +#endif + +namespace OpenSim.Data.SQLite +{ + public class SQLiteAgentPreferencesData : SQLiteGenericTableHandler, IAgentPreferencesData + { + public SQLiteAgentPreferencesData(string connectionString, string realm) + : base(connectionString, realm, "AgentPrefs") + { + } + + public AgentPreferencesData GetPrefs(UUID agentID) + { + AgentPreferencesData[] ret = Get("PrincipalID", agentID.ToString()); + + if (ret.Length == 0) + return null; + + return ret[0]; + } + + } +} diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs index 61e7aaf..f0dda64 100644 --- a/OpenSim/Data/SQLite/SQLiteAssetData.cs +++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs @@ -46,7 +46,7 @@ namespace OpenSim.Data.SQLite /// public class SQLiteAssetData : AssetDataBase { -// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); private const string SelectAssetSQL = "select * from assets where UUID=:UUID"; private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, asset_flags, UUID, CreatorID from assets limit :start, :count"; @@ -133,8 +133,26 @@ namespace OpenSim.Data.SQLite /// Asset Base override public void StoreAsset(AssetBase asset) { + string assetName = asset.Name; + if (asset.Name.Length > AssetBase.MAX_ASSET_NAME) + { + assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME); + m_log.WarnFormat( + "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Name, asset.ID, asset.Name.Length, assetName.Length); + } + + string assetDescription = asset.Description; + if (asset.Description.Length > AssetBase.MAX_ASSET_DESC) + { + assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC); + m_log.WarnFormat( + "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); + } + //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString()); - if (ExistsAsset(asset.FullID)) + if (AssetsExist(new[] { asset.FullID })[0]) { //LogAssetLoad(asset); @@ -143,8 +161,8 @@ namespace OpenSim.Data.SQLite using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn)) { cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); - cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); + cmd.Parameters.Add(new SqliteParameter(":Name", assetName)); + cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription)); cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); @@ -163,8 +181,8 @@ namespace OpenSim.Data.SQLite using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn)) { cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); - cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); + cmd.Parameters.Add(new SqliteParameter(":Name", assetName)); + cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription)); cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); @@ -196,32 +214,39 @@ namespace OpenSim.Data.SQLite // } /// - /// Check if an asset exist in database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// True if exist, or false. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { - lock (this) + if (uuids.Length == 0) + return new bool[0]; + + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("select UUID from assets where UUID in ({0})", ids); + + lock (this) { - using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn)) + using (SqliteCommand cmd = new SqliteCommand(sql, m_conn)) { - cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString())); using (IDataReader reader = cmd.ExecuteReader()) { - if (reader.Read()) - { - reader.Close(); - return true; - } - else + while (reader.Read()) { - reader.Close(); - return false; + UUID id = new UUID((string)reader["UUID"]); + exist.Add(id); } } } } + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + return results; } /// diff --git a/OpenSim/Data/SQLite/SQLiteFriendsData.cs b/OpenSim/Data/SQLite/SQLiteFriendsData.cs index cab85eb..331f426 100644 --- a/OpenSim/Data/SQLite/SQLiteFriendsData.cs +++ b/OpenSim/Data/SQLite/SQLiteFriendsData.cs @@ -67,7 +67,7 @@ namespace OpenSim.Data.SQLite return Delete(principalID.ToString(), friend); } - public bool Delete(string principalID, string friend) + public override bool Delete(string principalID, string friend) { using (SqliteCommand cmd = new SqliteCommand()) { diff --git a/OpenSim/Data/SQLite/SQLiteGridUserData.cs b/OpenSim/Data/SQLite/SQLiteGridUserData.cs index 1bb5ed8..d8c52f8 100644 --- a/OpenSim/Data/SQLite/SQLiteGridUserData.cs +++ b/OpenSim/Data/SQLite/SQLiteGridUserData.cs @@ -56,6 +56,10 @@ namespace OpenSim.Data.SQLite return ret[0]; } + public GridUserData[] GetAll(string userID) + { + return base.Get(String.Format("UserID LIKE '{0}%'", userID)); + } } } \ No newline at end of file diff --git a/OpenSim/Data/SQLite/SQLiteHGTravelData.cs b/OpenSim/Data/SQLite/SQLiteHGTravelData.cs new file mode 100644 index 0000000..db288b2 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteHGTravelData.cs @@ -0,0 +1,82 @@ +/* + * 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 System.Threading; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using Mono.Data.Sqlite; + +namespace OpenSim.Data.SQLite +{ + /// + /// A SQL Interface for user grid data + /// + public class SQLiteHGTravelData : SQLiteGenericTableHandler, IHGTravelingData + { +// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + public SQLiteHGTravelData(string connectionString, string realm) + : base(connectionString, realm, "HGTravelStore") {} + + public HGTravelingData Get(UUID sessionID) + { + HGTravelingData[] ret = Get("SessionID", sessionID.ToString()); + + if (ret.Length == 0) + return null; + + return ret[0]; + } + + public HGTravelingData[] GetSessions(UUID userID) + { + return base.Get("UserID", userID.ToString()); + } + + public bool Delete(UUID sessionID) + { + return Delete("SessionID", sessionID.ToString()); + } + + public void DeleteOld() + { + using (SqliteCommand cmd = new SqliteCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < datetime('now', '-2 day') ", m_Realm); + + DoQuery(cmd); + } + + } + + } +} \ No newline at end of file diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs index ccbd154..7d493ca 100644 --- a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs +++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs @@ -239,7 +239,7 @@ namespace OpenSim.Data.SQLite if (inventoryRow == null) { if (! add) - m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existant inventory folder: {0}", folder.ID); + m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existent inventory folder: {0}", folder.ID); inventoryRow = inventoryFolderTable.NewRow(); fillFolderRow(inventoryRow, folder); @@ -298,7 +298,7 @@ namespace OpenSim.Data.SQLite if (inventoryRow == null) { if (!add) - m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existant inventory item: {0}", item.ID); + m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existent inventory item: {0}", item.ID); inventoryRow = inventoryItemTable.NewRow(); fillItemRow(inventoryRow, item); diff --git a/OpenSim/Data/SQLite/SQLiteSimulationData.cs b/OpenSim/Data/SQLite/SQLiteSimulationData.cs index 29cac3c..6ed3d40 100644 --- a/OpenSim/Data/SQLite/SQLiteSimulationData.cs +++ b/OpenSim/Data/SQLite/SQLiteSimulationData.cs @@ -51,6 +51,7 @@ namespace OpenSim.Data.SQLite public class SQLiteSimulationData : ISimulationDataStore { private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private static readonly string LogHeader = "[REGION DB SQLITE]"; private const string primSelect = "select * from prims"; private const string shapeSelect = "select * from primshapes"; @@ -732,9 +733,12 @@ namespace OpenSim.Data.SQLite } SceneObjectGroup group = new SceneObjectGroup(prim); + createdObjects.Add(group.UUID, group); retvals.Add(group); LoadItems(prim); + + } } catch (Exception e) @@ -816,45 +820,44 @@ namespace OpenSim.Data.SQLite prim.Inventory.RestoreInventoryItems(inventory); } + // Legacy entry point for when terrain was always a 256x256 hieghtmap + public void StoreTerrain(double[,] ter, UUID regionID) + { + StoreTerrain(new HeightmapTerrainData(ter), regionID); + } + /// /// Store a terrain revision in region storage /// /// terrain heightfield /// region UUID - public void StoreTerrain(double[,] ter, UUID regionID) + public void StoreTerrain(TerrainData terrData, UUID regionID) { lock (ds) { - int revision = Util.UnixTimeSinceEpoch(); - - // This is added to get rid of the infinitely growing - // terrain databases which negatively impact on SQLite - // over time. Before reenabling this feature there - // needs to be a limitter put on the number of - // revisions in the database, as this old - // implementation is a DOS attack waiting to happen. - using ( - SqliteCommand cmd = - new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision <= :Revision", - m_conn)) + SqliteCommand cmd = new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID", m_conn)) { cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":Revision", revision)); cmd.ExecuteNonQuery(); } // the following is an work around for .NET. The perf // issues associated with it aren't as bad as you think. - m_log.Debug("[SQLITE REGION DB]: Storing terrain revision r" + revision.ToString()); String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" + " values(:RegionUUID, :Revision, :Heightfield)"; + int terrainDBRevision; + Array terrainDBblob; + terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); + + m_log.DebugFormat("{0} Storing terrain revision r {1}", LogHeader, terrainDBRevision); + using (SqliteCommand cmd = new SqliteCommand(sql, m_conn)) { cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":Revision", revision)); - cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter))); + cmd.Parameters.Add(new SqliteParameter(":Revision", terrainDBRevision)); + cmd.Parameters.Add(new SqliteParameter(":Heightfield", terrainDBblob)); cmd.ExecuteNonQuery(); } } @@ -867,11 +870,20 @@ namespace OpenSim.Data.SQLite /// Heightfield data public double[,] LoadTerrain(UUID regionID) { + double[,] ret = null; + TerrainData terrData = LoadTerrain(regionID, (int)Constants.RegionSize, (int)Constants.RegionSize, (int)Constants.RegionHeight); + if (terrData != null) + ret = terrData.GetDoubles(); + return ret; + } + + // Returns 'null' if region not found + public TerrainData LoadTerrain(UUID regionID, int pSizeX, int pSizeY, int pSizeZ) + { + TerrainData terrData = null; + lock (ds) { - double[,] terret = new double[(int)Constants.RegionSize, (int)Constants.RegionSize]; - terret.Initialize(); - String sql = "select RegionUUID, Revision, Heightfield from terrain" + " where RegionUUID=:RegionUUID order by Revision desc"; @@ -884,21 +896,9 @@ namespace OpenSim.Data.SQLite int rev = 0; if (row.Read()) { - // TODO: put this into a function - using (MemoryStream str = new MemoryStream((byte[])row["Heightfield"])) - { - using (BinaryReader br = new BinaryReader(str)) - { - for (int x = 0; x < (int)Constants.RegionSize; x++) - { - for (int y = 0; y < (int)Constants.RegionSize; y++) - { - terret[x, y] = br.ReadDouble(); - } - } - } - } rev = Convert.ToInt32(row["Revision"]); + byte[] blob = (byte[])row["Heightfield"]; + terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob); } else { @@ -909,8 +909,8 @@ namespace OpenSim.Data.SQLite m_log.Debug("[SQLITE REGION DB]: Loaded terrain revision r" + rev.ToString()); } } - return terret; } + return terrData; } public void RemoveLandObject(UUID globalID) @@ -1232,7 +1232,20 @@ namespace OpenSim.Data.SQLite createCol(prims, "VolumeDetect", typeof(Int16)); createCol(prims, "MediaURL", typeof(String)); + + createCol(prims, "AttachedPosX", typeof(Double)); + createCol(prims, "AttachedPosY", typeof(Double)); + createCol(prims, "AttachedPosZ", typeof(Double)); + + createCol(prims, "DynAttrs", typeof(String)); + + createCol(prims, "PhysicsShapeType", typeof(Byte)); + createCol(prims, "Density", typeof(Double)); + createCol(prims, "GravityModifier", typeof(Double)); + createCol(prims, "Friction", typeof(Double)); + createCol(prims, "Restitution", typeof(Double)); + createCol(prims, "KeyframeMotion", typeof(Byte[])); // Add in contraints prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; @@ -1592,7 +1605,7 @@ namespace OpenSim.Data.SQLite prim.SitName = (String)row["SitName"]; prim.TouchName = (String)row["TouchName"]; // permissions - prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]); + prim.Flags = (PrimFlags)Convert.ToUInt32(row["ObjectFlags"]); prim.CreatorIdentification = (String)row["CreatorID"]; prim.OwnerID = new UUID((String)row["OwnerID"]); prim.GroupID = new UUID((String)row["GroupID"]); @@ -1711,7 +1724,43 @@ namespace OpenSim.Data.SQLite // m_log.DebugFormat("[SQLITE]: MediaUrl type [{0}]", row["MediaURL"].GetType()); prim.MediaUrl = (string)row["MediaURL"]; } + + prim.AttachedPos = new Vector3( + Convert.ToSingle(row["AttachedPosX"]), + Convert.ToSingle(row["AttachedPosY"]), + Convert.ToSingle(row["AttachedPosZ"]) + ); + + if (!(row["DynAttrs"] is System.DBNull)) + { + //m_log.DebugFormat("[SQLITE]: DynAttrs type [{0}]", row["DynAttrs"].GetType()); + prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]); + } + else + { + prim.DynAttrs = new DAMap(); + } + + prim.PhysicsShapeType = Convert.ToByte(row["PhysicsShapeType"]); + prim.Density = Convert.ToSingle(row["Density"]); + prim.GravityModifier = Convert.ToSingle(row["GravityModifier"]); + prim.Friction = Convert.ToSingle(row["Friction"]); + prim.Restitution = Convert.ToSingle(row["Restitution"]); + + if (!(row["KeyframeMotion"] is DBNull)) + { + Byte[] data = (byte[])row["KeyframeMotion"]; + if (data.Length > 0) + prim.KeyframeMotion = KeyframeMotion.FromData(null, data); + else + prim.KeyframeMotion = null; + } + else + { + prim.KeyframeMotion = null; + } + return prim; } @@ -1967,40 +2016,6 @@ namespace OpenSim.Data.SQLite /// /// /// - /// - /// - private static Array serializeTerrain(double[,] val) - { - MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize) * sizeof(double)); - BinaryWriter bw = new BinaryWriter(str); - - // TODO: COMPATIBILITY - Add byte-order conversions - for (int x = 0; x < (int)Constants.RegionSize; x++) - for (int y = 0; y < (int)Constants.RegionSize; y++) - bw.Write(val[x, y]); - - return str.ToArray(); - } - - // private void fillTerrainRow(DataRow row, UUID regionUUID, int rev, double[,] val) - // { - // row["RegionUUID"] = regionUUID; - // row["Revision"] = rev; - - // MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize)*sizeof (double)); - // BinaryWriter bw = new BinaryWriter(str); - - // // TODO: COMPATIBILITY - Add byte-order conversions - // for (int x = 0; x < (int)Constants.RegionSize; x++) - // for (int y = 0; y < (int)Constants.RegionSize; y++) - // bw.Write(val[x, y]); - - // row["Heightfield"] = str.ToArray(); - // } - - /// - /// - /// /// /// /// @@ -2019,7 +2034,7 @@ namespace OpenSim.Data.SQLite row["SitName"] = prim.SitName; row["TouchName"] = prim.TouchName; // permissions - row["ObjectFlags"] = prim.ObjectFlags; + row["ObjectFlags"] = (uint)prim.Flags; row["CreatorID"] = prim.CreatorIdentification.ToString(); row["OwnerID"] = prim.OwnerID.ToString(); row["GroupID"] = prim.GroupID.ToString(); @@ -2133,6 +2148,28 @@ namespace OpenSim.Data.SQLite row["VolumeDetect"] = 0; row["MediaURL"] = prim.MediaUrl; + + row["AttachedPosX"] = prim.AttachedPos.X; + row["AttachedPosY"] = prim.AttachedPos.Y; + row["AttachedPosZ"] = prim.AttachedPos.Z; + + if (prim.DynAttrs.CountNamespaces > 0) + row["DynAttrs"] = prim.DynAttrs.ToXml(); + else + row["DynAttrs"] = null; + + row["PhysicsShapeType"] = prim.PhysicsShapeType; + row["Density"] = (double)prim.Density; + row["GravityModifier"] = (double)prim.GravityModifier; + row["Friction"] = (double)prim.Friction; + row["Restitution"] = (double)prim.Restitution; + + if (prim.KeyframeMotion != null) + row["KeyframeMotion"] = prim.KeyframeMotion.Serialize(); + else + row["KeyframeMotion"] = new Byte[0]; + + } /// @@ -2384,6 +2421,7 @@ namespace OpenSim.Data.SQLite s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]); s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]); s.State = Convert.ToByte(row["State"]); + s.LastAttachPoint = Convert.ToByte(row["LastAttachPoint"]); byte[] textureEntry = (byte[])row["Texture"]; s.TextureEntry = textureEntry; @@ -2392,7 +2430,7 @@ namespace OpenSim.Data.SQLite if (!(row["Media"] is System.DBNull)) s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]); - + return s; } @@ -2433,6 +2471,7 @@ namespace OpenSim.Data.SQLite row["ProfileCurve"] = s.ProfileCurve; row["ProfileHollow"] = s.ProfileHollow; row["State"] = s.State; + row["LastAttachPoint"] = s.LastAttachPoint; row["Texture"] = s.TextureEntry; row["ExtraParams"] = s.ExtraParams; diff --git a/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs new file mode 100644 index 0000000..cd3e8b6 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs @@ -0,0 +1,981 @@ +/* + * 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 Dictionary m_FieldMap = + new Dictionary(); + + 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(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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ParcelName = (string)reader["user"]; + pick.OriginalName = (string)reader["originalname"]; + pick.SimName = (string)reader["simname"]; + pick.SortOrder = (int)reader["sortorder"]; + } + } + } + } + catch (Exception e) + { + m_log.ErrorFormat("[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.ParcelName.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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[PROFILES_DATA]" + + ": GetAvatarProperties exception {0}", e.Message); + result = e.Message; + return false; + } + if(reader != null && 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 + { + 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 += "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(":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.ErrorFormat("[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.ErrorFormat("[PROFILES_DATA]" + + ": AgentInterestsUpdate exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + + 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 (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand()) + { + cmd.CommandText = query; + cmd.Parameters.AddWithValue(":ImViaEmail", pref.IMViaEmail); + cmd.Parameters.AddWithValue(":Visible", pref.Visible); + cmd.Parameters.AddWithValue(":EMail", pref.EMail); + cmd.Parameters.AddWithValue(":uuid", pref.UserId.ToString()); + + cmd.ExecuteNonQuery(); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[PROFILES_DATA]" + + ": AgentInterestsUpdate exception {0}", e.Message); + result = e.Message; + return false; + } + return true; + } + + public bool GetUserPreferences(ref UserPreferences pref, ref string result) + { + IDataReader reader = null; + string query = string.Empty; + + query += "SELECT imviaemail,visible,email FROM "; + query += "usersettings WHERE "; + query += "useruuid = :Id"; + + OSDArray data = new OSDArray(); + + try + { + using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand()) + { + cmd.CommandText = query; + cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString()); + + using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if(reader.Read()) + { + bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail); + bool.TryParse((string)reader["visible"], out pref.Visible); + pref.EMail = (string)reader["email"]; + } + else + { + query = "INSERT INTO usersettings VALUES "; + query += "(:Id,'false','false', :Email)"; + + using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand()) + { + put.Parameters.AddWithValue(":Id", pref.UserId.ToString()); + put.Parameters.AddWithValue(":Email", pref.EMail); + put.ExecuteNonQuery(); + + } + } + } + } + } + catch (Exception e) + { + m_log.ErrorFormat("[PROFILES_DATA]" + + ": Get preferences 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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[PROFILES_DATA]" + + ": GetAvatarNotes exception {0}", e.Message); + } + return data; + } + #endregion + } +} + -- cgit v1.1