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/MySQL/MySQLAgentPreferencesData.cs | 56 + OpenSim/Data/MySQL/MySQLAssetData.cs | 324 +++--- OpenSim/Data/MySQL/MySQLEstateData.cs | 45 +- OpenSim/Data/MySQL/MySQLFSAssetData.cs | 414 ++++++++ OpenSim/Data/MySQL/MySQLFramework.cs | 33 +- OpenSim/Data/MySQL/MySQLFriendsData.cs | 2 +- OpenSim/Data/MySQL/MySQLGenericTableHandler.cs | 60 ++ OpenSim/Data/MySQL/MySQLGridUserData.cs | 7 +- OpenSim/Data/MySQL/MySQLGroupsData.cs | 484 +++++++++ OpenSim/Data/MySQL/MySQLHGTravelData.cs | 80 ++ OpenSim/Data/MySQL/MySQLOfflineIMData.cs | 59 ++ OpenSim/Data/MySQL/MySQLRegionData.cs | 5 + OpenSim/Data/MySQL/MySQLSimulationData.cs | 805 ++++++++------- OpenSim/Data/MySQL/MySQLUserProfilesData.cs | 1086 ++++++++++++++++++++ OpenSim/Data/MySQL/MySQLXAssetData.cs | 431 ++++---- OpenSim/Data/MySQL/Properties/AssemblyInfo.cs | 4 +- OpenSim/Data/MySQL/Resources/AgentPrefs.migrations | 18 + OpenSim/Data/MySQL/Resources/AssetStore.migrations | 6 +- OpenSim/Data/MySQL/Resources/AuthStore.migrations | 4 +- .../Data/MySQL/Resources/EstateStore.migrations | 18 +- .../Data/MySQL/Resources/FSAssetStore.migrations | 18 + .../Data/MySQL/Resources/FriendsStore.migrations | 2 +- OpenSim/Data/MySQL/Resources/GridStore.migrations | 6 +- .../Data/MySQL/Resources/GridUserStore.migrations | 2 +- .../Data/MySQL/Resources/HGTravelStore.migrations | 18 + OpenSim/Data/MySQL/Resources/IM_Store.migrations | 42 + .../Data/MySQL/Resources/InventoryStore.migrations | 4 +- OpenSim/Data/MySQL/Resources/LogStore.migrations | 2 +- OpenSim/Data/MySQL/Resources/Presence.migrations | 12 +- .../Data/MySQL/Resources/RegionStore.migrations | 70 +- .../Data/MySQL/Resources/UserAccount.migrations | 2 +- .../Data/MySQL/Resources/UserProfiles.migrations | 98 ++ OpenSim/Data/MySQL/Resources/UserStore.migrations | 10 +- .../Data/MySQL/Resources/XAssetStore.migrations | 41 +- .../MySQL/Resources/os_groups_Store.migrations | 115 +++ 35 files changed, 3504 insertions(+), 879 deletions(-) create mode 100644 OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs create mode 100644 OpenSim/Data/MySQL/MySQLFSAssetData.cs create mode 100644 OpenSim/Data/MySQL/MySQLGroupsData.cs create mode 100644 OpenSim/Data/MySQL/MySQLHGTravelData.cs create mode 100644 OpenSim/Data/MySQL/MySQLOfflineIMData.cs create mode 100644 OpenSim/Data/MySQL/MySQLUserProfilesData.cs create mode 100644 OpenSim/Data/MySQL/Resources/AgentPrefs.migrations create mode 100644 OpenSim/Data/MySQL/Resources/FSAssetStore.migrations create mode 100644 OpenSim/Data/MySQL/Resources/HGTravelStore.migrations create mode 100644 OpenSim/Data/MySQL/Resources/IM_Store.migrations create mode 100644 OpenSim/Data/MySQL/Resources/UserProfiles.migrations create mode 100644 OpenSim/Data/MySQL/Resources/os_groups_Store.migrations (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs b/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs new file mode 100644 index 0000000..ed0ab98 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs @@ -0,0 +1,56 @@ +/* + * 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; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLAgentPreferencesData : MySQLGenericTableHandler, IAgentPreferencesData + { + public MySQLAgentPreferencesData(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/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs index 73de64b..5d8da17 100644 --- a/OpenSim/Data/MySQL/MySQLAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -45,7 +45,6 @@ namespace OpenSim.Data.MySQL private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); private string m_connectionString; - private object m_dbLock = new object(); protected virtual Assembly Assembly { @@ -107,46 +106,46 @@ namespace OpenSim.Data.MySQL override public AssetBase GetAsset(UUID assetID) { AssetBase asset = null; - lock (m_dbLock) + + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = new MySqlCommand( + "SELECT name, description, assetType, local, temporary, asset_flags, CreatorID, data FROM assets WHERE id=?id", + dbcon)) { - dbcon.Open(); + cmd.Parameters.AddWithValue("?id", assetID.ToString()); - using (MySqlCommand cmd = new MySqlCommand( - "SELECT name, description, assetType, local, temporary, asset_flags, CreatorID, data FROM assets WHERE id=?id", - dbcon)) + try { - cmd.Parameters.AddWithValue("?id", assetID.ToString()); - - try + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + if (dbReader.Read()) { - if (dbReader.Read()) - { - asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"], dbReader["CreatorID"].ToString()); - asset.Data = (byte[])dbReader["data"]; - asset.Description = (string)dbReader["description"]; - - string local = dbReader["local"].ToString(); - if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) - asset.Local = true; - else - asset.Local = false; - - asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); - asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); - } + asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"], dbReader["CreatorID"].ToString()); + asset.Data = (byte[])dbReader["data"]; + asset.Description = (string)dbReader["description"]; + + string local = dbReader["local"].ToString(); + if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) + asset.Local = true; + else + asset.Local = false; + + asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); + asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); } } - catch (Exception e) - { - m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message); - } + } + catch (Exception e) + { + m_log.Error( + string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", assetID), e); } } } + return asset; } @@ -157,137 +156,134 @@ namespace OpenSim.Data.MySQL /// On failure : Throw an exception and attempt to reconnect to database override public void StoreAsset(AssetBase asset) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = + new MySqlCommand( + "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, asset_flags, CreatorID, data)" + + "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?CreatorID, ?data)", + dbcon)) { - dbcon.Open(); + 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); + } - using (MySqlCommand cmd = - new MySqlCommand( - "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, asset_flags, CreatorID, data)" + - "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?CreatorID, ?data)", - dbcon)) + string assetDescription = asset.Description; + if (asset.Description.Length > AssetBase.MAX_ASSET_DESC) { - string assetName = asset.Name; - if (asset.Name.Length > 64) - { - assetName = asset.Name.Substring(0, 64); - m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add"); - } - - string assetDescription = asset.Description; - if (asset.Description.Length > 64) - { - assetDescription = asset.Description.Substring(0, 64); - m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); - } - - try - { - using (cmd) - { - // create unix epoch time - int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); - cmd.Parameters.AddWithValue("?id", asset.ID); - cmd.Parameters.AddWithValue("?name", assetName); - cmd.Parameters.AddWithValue("?description", assetDescription); - cmd.Parameters.AddWithValue("?assetType", asset.Type); - cmd.Parameters.AddWithValue("?local", asset.Local); - cmd.Parameters.AddWithValue("?temporary", asset.Temporary); - cmd.Parameters.AddWithValue("?create_time", now); - cmd.Parameters.AddWithValue("?access_time", now); - cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID); - cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags); - cmd.Parameters.AddWithValue("?data", asset.Data); - cmd.ExecuteNonQuery(); - } - } - catch (Exception e) + 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); + } + + try + { + using (cmd) { - m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}", - asset.FullID, asset.Name, e.Message); + // create unix epoch time + int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); + cmd.Parameters.AddWithValue("?id", asset.ID); + cmd.Parameters.AddWithValue("?name", assetName); + cmd.Parameters.AddWithValue("?description", assetDescription); + cmd.Parameters.AddWithValue("?assetType", asset.Type); + cmd.Parameters.AddWithValue("?local", asset.Local); + cmd.Parameters.AddWithValue("?temporary", asset.Temporary); + cmd.Parameters.AddWithValue("?create_time", now); + cmd.Parameters.AddWithValue("?access_time", now); + cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID); + cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags); + cmd.Parameters.AddWithValue("?data", asset.Data); + cmd.ExecuteNonQuery(); } } + catch (Exception e) + { + m_log.Error( + string.Format( + "[ASSET DB]: MySQL failure creating asset {0} with name {1}. Exception ", + asset.FullID, asset.Name) + , e); + } } } } private void UpdateAccessTime(AssetBase asset) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + dbcon.Open(); - using (MySqlCommand cmd - = new MySqlCommand("update assets set access_time=?access_time where id=?id", dbcon)) + using (MySqlCommand cmd + = new MySqlCommand("update assets set access_time=?access_time where id=?id", dbcon)) + { + try { - try - { - using (cmd) - { - // create unix epoch time - int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); - cmd.Parameters.AddWithValue("?id", asset.ID); - cmd.Parameters.AddWithValue("?access_time", now); - cmd.ExecuteNonQuery(); - } - } - catch (Exception e) + using (cmd) { - m_log.ErrorFormat( - "[ASSETS DB]: " + - "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() - + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); + // create unix epoch time + int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); + cmd.Parameters.AddWithValue("?id", asset.ID); + cmd.Parameters.AddWithValue("?access_time", now); + cmd.ExecuteNonQuery(); } } + catch (Exception e) + { + m_log.Error( + string.Format( + "[ASSETS DB]: Failure updating access_time for asset {0} with name {1}. Exception ", + asset.FullID, asset.Name), + e); + } } } } /// - /// Check if the asset exists in the database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// true if it exists, false otherwise. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { -// m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid); + if (uuids.Length == 0) + return new bool[0]; - bool assetExists = false; + HashSet exist = new HashSet(); - lock (m_dbLock) + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids); + + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + using (MySqlCommand cmd = new MySqlCommand(sql, dbcon)) { - dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon)) + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - cmd.Parameters.AddWithValue("?id", uuid.ToString()); - - try - { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) - { - if (dbReader.Read()) - { -// m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid); - assetExists = true; - } - } - } - catch (Exception e) + while (dbReader.Read()) { - m_log.ErrorFormat( - "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid); + UUID id = DBGuid.FromDB(dbReader["id"]); + exist.Add(id); } } } } - return assetExists; + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + + return results; } /// @@ -302,46 +298,47 @@ namespace OpenSim.Data.MySQL { List retList = new List(count); - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd + = new MySqlCommand( + "SELECT name,description,assetType,temporary,id,asset_flags,CreatorID FROM assets LIMIT ?start, ?count", + dbcon)) { - dbcon.Open(); + cmd.Parameters.AddWithValue("?start", start); + cmd.Parameters.AddWithValue("?count", count); - using (MySqlCommand cmd - = new MySqlCommand( - "SELECT name,description,assetType,temporary,id,asset_flags,CreatorID FROM assets LIMIT ?start, ?count", - dbcon)) + try { - cmd.Parameters.AddWithValue("?start", start); - cmd.Parameters.AddWithValue("?count", count); - - try + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - using (MySqlDataReader dbReader = cmd.ExecuteReader()) + while (dbReader.Read()) { - while (dbReader.Read()) - { - AssetMetadata metadata = new AssetMetadata(); - metadata.Name = (string)dbReader["name"]; - metadata.Description = (string)dbReader["description"]; - metadata.Type = (sbyte)dbReader["assetType"]; - metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. - metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); - metadata.FullID = DBGuid.FromDB(dbReader["id"]); - metadata.CreatorID = dbReader["CreatorID"].ToString(); - - // Current SHA1s are not stored/computed. - metadata.SHA1 = new byte[] { }; - - retList.Add(metadata); - } + AssetMetadata metadata = new AssetMetadata(); + metadata.Name = (string)dbReader["name"]; + metadata.Description = (string)dbReader["description"]; + metadata.Type = (sbyte)dbReader["assetType"]; + metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. + metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); + metadata.FullID = DBGuid.FromDB(dbReader["id"]); + metadata.CreatorID = dbReader["CreatorID"].ToString(); + + // Current SHA1s are not stored/computed. + metadata.SHA1 = new byte[] { }; + + retList.Add(metadata); } } - catch (Exception e) - { - m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString()); - } + } + catch (Exception e) + { + m_log.Error( + string.Format( + "[ASSETS DB]: MySql failure fetching asset set from {0}, count {1}. Exception ", + start, count), + e); } } } @@ -351,17 +348,14 @@ namespace OpenSim.Data.MySQL public override bool Delete(string id) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand("delete from assets where id=?id", dbcon)) - { - cmd.Parameters.AddWithValue("?id", id); - cmd.ExecuteNonQuery(); - } + using (MySqlCommand cmd = new MySqlCommand("delete from assets where id=?id", dbcon)) + { + cmd.Parameters.AddWithValue("?id", id); + cmd.ExecuteNonQuery(); } } diff --git a/OpenSim/Data/MySQL/MySQLEstateData.cs b/OpenSim/Data/MySQL/MySQLEstateData.cs index 3dd46cb..fe1487b 100644 --- a/OpenSim/Data/MySQL/MySQLEstateData.cs +++ b/OpenSim/Data/MySQL/MySQLEstateData.cs @@ -43,12 +43,7 @@ namespace OpenSim.Data.MySQL private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - private const string m_waitTimeoutSelect = "select @@wait_timeout"; - private string m_connectionString; - private long m_waitTimeout; - private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond; -// private long m_lastConnectionUse; private FieldInfo[] m_Fields; private Dictionary m_FieldMap = @@ -81,8 +76,6 @@ namespace OpenSim.Data.MySQL m_log.Debug("Exception: password not found in connection string\n" + e.ToString()); } - GetWaitTimeout(); - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); @@ -108,33 +101,6 @@ namespace OpenSim.Data.MySQL get { return new List(m_FieldMap.Keys).ToArray(); } } - protected void GetWaitTimeout() - { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - - using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon)) - { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) - { - if (dbReader.Read()) - { - m_waitTimeout - = Convert.ToInt32(dbReader["@@wait_timeout"]) * - TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; - } - } - } - -// m_lastConnectionUse = DateTime.Now.Ticks; - - m_log.DebugFormat( - "[REGION DB]: Connection wait timeout {0} seconds", - m_waitTimeout / TimeSpan.TicksPerSecond); - } - } - public EstateSettings LoadEstateSettings(UUID regionID, bool create) { string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + @@ -145,7 +111,11 @@ namespace OpenSim.Data.MySQL cmd.CommandText = sql; cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - return DoLoad(cmd, regionID, create); + EstateSettings e = DoLoad(cmd, regionID, create); + if (!create && e.EstateID == 0) // Not found + return null; + + return e; } } @@ -427,7 +397,10 @@ namespace OpenSim.Data.MySQL cmd.CommandText = sql; cmd.Parameters.AddWithValue("?EstateID", estateID); - return DoLoad(cmd, UUID.Zero, false); + EstateSettings e = DoLoad(cmd, UUID.Zero, false); + if (e.EstateID != estateID) + return null; + return e; } } diff --git a/OpenSim/Data/MySQL/MySQLFSAssetData.cs b/OpenSim/Data/MySQL/MySQLFSAssetData.cs new file mode 100644 index 0000000..19e23b5 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLFSAssetData.cs @@ -0,0 +1,414 @@ +/* + * 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.Reflection; +using System.Collections.Generic; +using System.Data; +using OpenSim.Framework; +using OpenSim.Framework.Console; +using log4net; +using MySql.Data.MySqlClient; +using OpenMetaverse; + +namespace OpenSim.Data.MySQL +{ + public class MySQLFSAssetData : IFSAssetDataPlugin + { + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + protected MySqlConnection m_Connection = null; + protected string m_ConnectionString; + protected string m_Table; + protected Object m_connLock = new Object(); + + /// + /// Number of days that must pass before we update the access time on an asset when it has been fetched + /// Config option to change this is "DaysBetweenAccessTimeUpdates" + /// + private int DaysBetweenAccessTimeUpdates = 0; + + protected virtual Assembly Assembly + { + get { return GetType().Assembly; } + } + + public MySQLFSAssetData() + { + } + + #region IPlugin Members + + public string Version { get { return "1.0.0.0"; } } + + // Loads and initialises the MySQL storage plugin and checks for migrations + public void Initialise(string connect, string realm, int UpdateAccessTime) + { + m_ConnectionString = connect; + m_Table = realm; + + DaysBetweenAccessTimeUpdates = UpdateAccessTime; + + try + { + OpenDatabase(); + + Migration m = new Migration(m_Connection, Assembly, "FSAssetStore"); + m.Update(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", e.Message.ToString()); + } + } + + public void Initialise() + { + throw new NotImplementedException(); + } + + public void Dispose() { } + + public string Name + { + get { return "MySQL FSAsset storage engine"; } + } + + #endregion + + private bool OpenDatabase() + { + try + { + m_Connection = new MySqlConnection(m_ConnectionString); + + m_Connection.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", + e.Message.ToString()); + + return false; + } + + return true; + } + + private IDataReader ExecuteReader(MySqlCommand c) + { + IDataReader r = null; + MySqlConnection connection = (MySqlConnection) ((ICloneable)m_Connection).Clone(); + connection.Open(); + c.Connection = connection; + + r = c.ExecuteReader(); + + return r; + } + + private void ExecuteNonQuery(MySqlCommand c) + { + lock (m_connLock) + { + bool errorSeen = false; + + while (true) + { + try + { + c.ExecuteNonQuery(); + } + catch (MySqlException) + { + System.Threading.Thread.Sleep(500); + + m_Connection.Close(); + m_Connection = (MySqlConnection) ((ICloneable)m_Connection).Clone(); + m_Connection.Open(); + c.Connection = m_Connection; + + if (!errorSeen) + { + errorSeen = true; + continue; + } + m_log.ErrorFormat("[FSASSETS] MySQL command: {0}", c.CommandText); + throw; + } + + break; + } + } + } + + #region IFSAssetDataPlugin Members + + public AssetMetadata Get(string id, out string hash) + { + hash = String.Empty; + + MySqlCommand cmd = new MySqlCommand(); + + cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, access_time, asset_flags from {0} where id = ?id", m_Table); + cmd.Parameters.AddWithValue("?id", id); + + IDataReader reader = ExecuteReader(cmd); + + if (!reader.Read()) + { + reader.Close(); + FreeCommand(cmd); + return null; + } + + AssetMetadata meta = new AssetMetadata(); + + hash = reader["hash"].ToString(); + + meta.ID = id; + meta.FullID = new UUID(id); + + meta.Name = reader["name"].ToString(); + meta.Description = reader["description"].ToString(); + meta.Type = (sbyte)Convert.ToInt32(reader["type"]); + meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type); + meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"])); + meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]); + + int AccessTime = Convert.ToInt32(reader["access_time"]); + + reader.Close(); + + UpdateAccessTime(AccessTime, cmd); + + FreeCommand(cmd); + + return meta; + } + + private void UpdateAccessTime(int AccessTime, MySqlCommand cmd) + { + // Reduce DB work by only updating access time if asset hasn't recently been accessed + // 0 By Default, Config option is "DaysBetweenAccessTimeUpdates" + if (DaysBetweenAccessTimeUpdates > 0 && (DateTime.UtcNow - Utils.UnixTimeToDateTime(AccessTime)).TotalDays < DaysBetweenAccessTimeUpdates) + return; + + cmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table); + + cmd.ExecuteNonQuery(); + } + + protected void FreeCommand(MySqlCommand cmd) + { + MySqlConnection c = cmd.Connection; + cmd.Dispose(); + c.Close(); + c.Dispose(); + } + + public bool Store(AssetMetadata meta, string hash) + { + try + { + string oldhash; + AssetMetadata existingAsset = Get(meta.ID, out oldhash); + + MySqlCommand cmd = m_Connection.CreateCommand(); + + cmd.Parameters.AddWithValue("?id", meta.ID); + cmd.Parameters.AddWithValue("?name", meta.Name); + cmd.Parameters.AddWithValue("?description", meta.Description); + cmd.Parameters.AddWithValue("?type", meta.Type.ToString()); + cmd.Parameters.AddWithValue("?hash", hash); + cmd.Parameters.AddWithValue("?asset_flags", meta.Flags); + + if (existingAsset == null) + { + cmd.CommandText = String.Format("insert into {0} (id, name, description, type, hash, asset_flags, create_time, access_time) values ( ?id, ?name, ?description, ?type, ?hash, ?asset_flags, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())", m_Table); + + ExecuteNonQuery(cmd); + + cmd.Dispose(); + + return true; + } + + //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table); + + //ExecuteNonQuery(cmd); + + cmd.Dispose(); + return false; + } + catch(Exception e) + { + m_log.Error("[FSAssets] Failed to store asset with ID " + meta.ID); + m_log.Error(e.ToString()); + return false; + } + } + + /// + /// Check if the assets exist in the database. + /// + /// The asset UUID's + /// For each asset: true if it exists, false otherwise + public bool[] AssetsExist(UUID[] uuids) + { + if (uuids.Length == 0) + return new bool[0]; + + HashSet exists = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("select id from {1} where id in ({0})", ids, m_Table); + + using (MySqlCommand cmd = m_Connection.CreateCommand()) + { + cmd.CommandText = sql; + + using (MySqlDataReader dbReader = cmd.ExecuteReader()) + { + while (dbReader.Read()) + { + UUID id = DBGuid.FromDB(dbReader["ID"]); + exists.Add(id); + } + } + } + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exists.Contains(uuids[i]); + return results; + } + + public int Count() + { + MySqlCommand cmd = m_Connection.CreateCommand(); + + cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table); + + IDataReader reader = ExecuteReader(cmd); + + reader.Read(); + + int count = Convert.ToInt32(reader["count"]); + + reader.Close(); + FreeCommand(cmd); + + return count; + } + + public bool Delete(string id) + { + using (MySqlCommand cmd = m_Connection.CreateCommand()) + { + cmd.CommandText = String.Format("delete from {0} where id = ?id", m_Table); + + cmd.Parameters.AddWithValue("?id", id); + + ExecuteNonQuery(cmd); + } + + return true; + } + + public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store) + { + MySqlConnection importConn; + + try + { + importConn = new MySqlConnection(conn); + + importConn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", + e.Message.ToString()); + + return; + } + + int imported = 0; + + MySqlCommand cmd = importConn.CreateCommand(); + + string limit = String.Empty; + if (count != -1) + { + limit = String.Format(" limit {0},{1}", start, count); + } + + cmd.CommandText = String.Format("select * from {0}{1}", table, limit); + + MainConsole.Instance.Output("Querying database"); + IDataReader reader = cmd.ExecuteReader(); + + MainConsole.Instance.Output("Reading data"); + + while (reader.Read()) + { + if ((imported % 100) == 0) + { + MainConsole.Instance.Output(String.Format("{0} assets imported so far", imported)); + } + + AssetBase asset = new AssetBase(); + AssetMetadata meta = new AssetMetadata(); + + meta.ID = reader["id"].ToString(); + meta.FullID = new UUID(meta.ID); + + meta.Name = reader["name"].ToString(); + meta.Description = reader["description"].ToString(); + meta.Type = (sbyte)Convert.ToInt32(reader["assetType"]); + meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type); + meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"])); + + asset.Metadata = meta; + asset.Data = (byte[])reader["data"]; + + store(asset, force); + + imported++; + } + + reader.Close(); + cmd.Dispose(); + importConn.Close(); + + MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported)); + } + + #endregion + } +} diff --git a/OpenSim/Data/MySQL/MySQLFramework.cs b/OpenSim/Data/MySQL/MySQLFramework.cs index 3fdcf1e..5820a90 100644 --- a/OpenSim/Data/MySQL/MySQLFramework.cs +++ b/OpenSim/Data/MySQL/MySQLFramework.cs @@ -45,38 +45,29 @@ namespace OpenSim.Data.MySQL System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); protected string m_connectionString; - protected object m_dbLock = new object(); protected MySqlFramework(string connectionString) { m_connectionString = connectionString; } - ////////////////////////////////////////////////////////////// - // - // All non queries are funneled through one connection - // to increase performance a little - // protected int ExecuteNonQuery(MySqlCommand cmd) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - cmd.Connection = dbcon; + dbcon.Open(); + cmd.Connection = dbcon; - try - { - return cmd.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.Error(e.Message, e); - return 0; - } + try + { + return cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error(e.Message, e); + return 0; } } } } -} +} \ No newline at end of file diff --git a/OpenSim/Data/MySQL/MySQLFriendsData.cs b/OpenSim/Data/MySQL/MySQLFriendsData.cs index 3cd6b8f..6ba9fbd 100644 --- a/OpenSim/Data/MySQL/MySQLFriendsData.cs +++ b/OpenSim/Data/MySQL/MySQLFriendsData.cs @@ -47,7 +47,7 @@ namespace OpenSim.Data.MySQL return Delete(principalID.ToString(), friend); } - public bool Delete(string principalID, string friend) + public override bool Delete(string principalID, string friend) { using (MySqlCommand cmd = new MySqlCommand()) { diff --git a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs index 995c6a5..35fa89f 100644 --- a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs +++ b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs @@ -301,5 +301,65 @@ namespace OpenSim.Data.MySQL return ExecuteNonQuery(cmd) > 0; } } + + public long GetCount(string field, string key) + { + return GetCount(new string[] { field }, new string[] { key }); + } + + public long GetCount(string[] fields, string[] keys) + { + if (fields.Length != keys.Length) + return 0; + + List terms = new List(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + for (int i = 0; i < fields.Length; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("`" + fields[i] + "` = ?" + fields[i]); + } + + string where = String.Join(" and ", terms.ToArray()); + + string query = String.Format("select count(*) from {0} where {1}", + m_Realm, where); + + cmd.CommandText = query; + + Object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public long GetCount(string where) + { + using (MySqlCommand cmd = new MySqlCommand()) + { + string query = String.Format("select count(*) from {0} where {1}", + m_Realm, where); + + cmd.CommandText = query; + + object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public object DoQueryScalar(MySqlCommand cmd) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + cmd.Connection = dbcon; + + return cmd.ExecuteScalar(); + } + } + } } \ No newline at end of file diff --git a/OpenSim/Data/MySQL/MySQLGridUserData.cs b/OpenSim/Data/MySQL/MySQLGridUserData.cs index a9ce94d..00560c1 100644 --- a/OpenSim/Data/MySQL/MySQLGridUserData.cs +++ b/OpenSim/Data/MySQL/MySQLGridUserData.cs @@ -46,7 +46,7 @@ namespace OpenSim.Data.MySQL public MySQLGridUserData(string connectionString, string realm) : base(connectionString, realm, "GridUserStore") {} - public GridUserData Get(string userID) + public new GridUserData Get(string userID) { GridUserData[] ret = Get("UserID", userID); @@ -56,6 +56,9 @@ namespace OpenSim.Data.MySQL 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/MySQL/MySQLGroupsData.cs b/OpenSim/Data/MySQL/MySQLGroupsData.cs new file mode 100644 index 0000000..afa499e --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLGroupsData.cs @@ -0,0 +1,484 @@ +/* + * 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.Reflection; + +using OpenSim.Framework; +using OpenSim.Data.MySQL; + +using OpenMetaverse; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLGroupsData : IGroupsData + { + private MySqlGroupsGroupsHandler m_Groups; + private MySqlGroupsMembershipHandler m_Membership; + private MySqlGroupsRolesHandler m_Roles; + private MySqlGroupsRoleMembershipHandler m_RoleMembership; + private MySqlGroupsInvitesHandler m_Invites; + private MySqlGroupsNoticesHandler m_Notices; + private MySqlGroupsPrincipalsHandler m_Principals; + + public MySQLGroupsData(string connectionString, string realm) + { + m_Groups = new MySqlGroupsGroupsHandler(connectionString, realm + "_groups", realm + "_Store"); + m_Membership = new MySqlGroupsMembershipHandler(connectionString, realm + "_membership"); + m_Roles = new MySqlGroupsRolesHandler(connectionString, realm + "_roles"); + m_RoleMembership = new MySqlGroupsRoleMembershipHandler(connectionString, realm + "_rolemembership"); + m_Invites = new MySqlGroupsInvitesHandler(connectionString, realm + "_invites"); + m_Notices = new MySqlGroupsNoticesHandler(connectionString, realm + "_notices"); + m_Principals = new MySqlGroupsPrincipalsHandler(connectionString, realm + "_principals"); + } + + #region groups table + public bool StoreGroup(GroupData data) + { + return m_Groups.Store(data); + } + + public GroupData RetrieveGroup(UUID groupID) + { + GroupData[] groups = m_Groups.Get("GroupID", groupID.ToString()); + if (groups.Length > 0) + return groups[0]; + + return null; + } + + public GroupData RetrieveGroup(string name) + { + GroupData[] groups = m_Groups.Get("Name", name); + if (groups.Length > 0) + return groups[0]; + + return null; + } + + public GroupData[] RetrieveGroups(string pattern) + { + if (string.IsNullOrEmpty(pattern)) + pattern = "1"; + else + pattern = string.Format("Name LIKE '%{0}%'", MySqlHelper.EscapeString(pattern)); + + return m_Groups.Get(string.Format("ShowInList=1 AND ({0}) ORDER BY Name LIMIT 100", pattern)); + } + + public bool DeleteGroup(UUID groupID) + { + return m_Groups.Delete("GroupID", groupID.ToString()); + } + + public int GroupsCount() + { + return (int)m_Groups.GetCount("Location=\"\""); + } + + #endregion + + #region membership table + public MembershipData[] RetrieveMembers(UUID groupID) + { + return m_Membership.Get("GroupID", groupID.ToString()); + } + + public MembershipData RetrieveMember(UUID groupID, string pricipalID) + { + MembershipData[] m = m_Membership.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), pricipalID }); + if (m != null && m.Length > 0) + return m[0]; + + return null; + } + + public MembershipData[] RetrieveMemberships(string pricipalID) + { + return m_Membership.Get("PrincipalID", pricipalID.ToString()); + } + + public bool StoreMember(MembershipData data) + { + return m_Membership.Store(data); + } + + public bool DeleteMember(UUID groupID, string pricipalID) + { + return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), pricipalID }); + } + + public int MemberCount(UUID groupID) + { + return (int)m_Membership.GetCount("GroupID", groupID.ToString()); + } + #endregion + + #region roles table + public bool StoreRole(RoleData data) + { + return m_Roles.Store(data); + } + + public RoleData RetrieveRole(UUID groupID, UUID roleID) + { + RoleData[] data = m_Roles.Get(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + + if (data != null && data.Length > 0) + return data[0]; + + return null; + } + + public RoleData[] RetrieveRoles(UUID groupID) + { + //return m_Roles.RetrieveRoles(groupID); + return m_Roles.Get("GroupID", groupID.ToString()); + } + + public bool DeleteRole(UUID groupID, UUID roleID) + { + return m_Roles.Delete(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + } + + public int RoleCount(UUID groupID) + { + return (int)m_Roles.GetCount("GroupID", groupID.ToString()); + } + + + #endregion + + #region rolememberhip table + public RoleMembershipData[] RetrieveRolesMembers(UUID groupID) + { + RoleMembershipData[] data = m_RoleMembership.Get("GroupID", groupID.ToString()); + + return data; + } + + public RoleMembershipData[] RetrieveRoleMembers(UUID groupID, UUID roleID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + + return data; + } + + public RoleMembershipData[] RetrieveMemberRoles(UUID groupID, string principalID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID.ToString() }); + + return data; + } + + public RoleMembershipData RetrieveRoleMember(UUID groupID, UUID roleID, string principalID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID", "PrincipalID" }, + new string[] { groupID.ToString(), roleID.ToString(), principalID.ToString() }); + + if (data != null && data.Length > 0) + return data[0]; + + return null; + } + + public int RoleMemberCount(UUID groupID, UUID roleID) + { + return (int)m_RoleMembership.GetCount(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + } + + public bool StoreRoleMember(RoleMembershipData data) + { + return m_RoleMembership.Store(data); + } + + public bool DeleteRoleMember(RoleMembershipData data) + { + return m_RoleMembership.Delete(new string[] { "GroupID", "RoleID", "PrincipalID"}, + new string[] { data.GroupID.ToString(), data.RoleID.ToString(), data.PrincipalID }); + } + + public bool DeleteMemberAllRoles(UUID groupID, string principalID) + { + return m_RoleMembership.Delete(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID }); + } + + #endregion + + #region principals table + public bool StorePrincipal(PrincipalData data) + { + return m_Principals.Store(data); + } + + public PrincipalData RetrievePrincipal(string principalID) + { + PrincipalData[] p = m_Principals.Get("PrincipalID", principalID); + if (p != null && p.Length > 0) + return p[0]; + + return null; + } + + public bool DeletePrincipal(string principalID) + { + return m_Principals.Delete("PrincipalID", principalID); + } + #endregion + + #region invites table + + public bool StoreInvitation(InvitationData data) + { + return m_Invites.Store(data); + } + + public InvitationData RetrieveInvitation(UUID inviteID) + { + InvitationData[] invites = m_Invites.Get("InviteID", inviteID.ToString()); + + if (invites != null && invites.Length > 0) + return invites[0]; + + return null; + } + + public InvitationData RetrieveInvitation(UUID groupID, string principalID) + { + InvitationData[] invites = m_Invites.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID }); + + if (invites != null && invites.Length > 0) + return invites[0]; + + return null; + } + + public bool DeleteInvite(UUID inviteID) + { + return m_Invites.Delete("InviteID", inviteID.ToString()); + } + + public void DeleteOldInvites() + { + m_Invites.DeleteOld(); + } + + #endregion + + #region notices table + + public bool StoreNotice(NoticeData data) + { + return m_Notices.Store(data); + } + + public NoticeData RetrieveNotice(UUID noticeID) + { + NoticeData[] notices = m_Notices.Get("NoticeID", noticeID.ToString()); + + if (notices != null && notices.Length > 0) + return notices[0]; + + return null; + } + + public NoticeData[] RetrieveNotices(UUID groupID) + { + NoticeData[] notices = m_Notices.Get("GroupID", groupID.ToString()); + + return notices; + } + + public bool DeleteNotice(UUID noticeID) + { + return m_Notices.Delete("NoticeID", noticeID.ToString()); + } + + public void DeleteOldNotices() + { + m_Notices.DeleteOld(); + } + + #endregion + + #region combinations + public MembershipData RetrievePrincipalGroupMembership(string principalID, UUID groupID) + { + // TODO + return null; + } + public MembershipData[] RetrievePrincipalGroupMemberships(string principalID) + { + // TODO + return null; + } + + #endregion + } + + public class MySqlGroupsGroupsHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsGroupsHandler(string connectionString, string realm, string store) + : base(connectionString, realm, store) + { + } + + } + + public class MySqlGroupsMembershipHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsMembershipHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsRolesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsRolesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsRoleMembershipHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsRoleMembershipHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsInvitesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsInvitesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + public void DeleteOld() + { + uint now = (uint)Util.UnixTimeSinceEpoch(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm); + cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old + + ExecuteNonQuery(cmd); + } + + } + } + + public class MySqlGroupsNoticesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsNoticesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + public void DeleteOld() + { + uint now = (uint)Util.UnixTimeSinceEpoch(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm); + cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old + + ExecuteNonQuery(cmd); + } + + } + } + + public class MySqlGroupsPrincipalsHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsPrincipalsHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLHGTravelData.cs b/OpenSim/Data/MySQL/MySQLHGTravelData.cs new file mode 100644 index 0000000..e81b880 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLHGTravelData.cs @@ -0,0 +1,80 @@ +/* + * 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 MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + /// + /// A MySQL Interface for user grid data + /// + public class MySQLHGTravelData : MySQLGenericTableHandler, IHGTravelingData + { +// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + public MySQLHGTravelData(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 (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 DAY", m_Realm); + + ExecuteNonQuery(cmd); + } + + } + } +} \ No newline at end of file diff --git a/OpenSim/Data/MySQL/MySQLOfflineIMData.cs b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs new file mode 100644 index 0000000..bafd204 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs @@ -0,0 +1,59 @@ +/* + * 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.Reflection; + +using OpenSim.Framework; +using OpenSim.Data.MySQL; + +using OpenMetaverse; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLOfflineIMData : MySQLGenericTableHandler, IOfflineIMData + { + public MySQLOfflineIMData(string connectionString, string realm) + : base(connectionString, realm, "IM_Store") + { + } + + public void DeleteOld() + { + using (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 WEEK", m_Realm); + + ExecuteNonQuery(cmd); + } + + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs index a2d4ae4..2ad7590 100644 --- a/OpenSim/Data/MySQL/MySQLRegionData.cs +++ b/OpenSim/Data/MySQL/MySQLRegionData.cs @@ -310,6 +310,11 @@ namespace OpenSim.Data.MySQL return Get((int)RegionFlags.DefaultRegion, scopeID); } + public List GetDefaultHypergridRegions(UUID scopeID) + { + return Get((int)RegionFlags.DefaultHGRegion, scopeID); + } + public List GetFallbackRegions(UUID scopeID, int x, int y) { List regions = Get((int)RegionFlags.FallbackRegion, scopeID); diff --git a/OpenSim/Data/MySQL/MySQLSimulationData.cs b/OpenSim/Data/MySQL/MySQLSimulationData.cs index d562783..bb0ab75 100644 --- a/OpenSim/Data/MySQL/MySQLSimulationData.cs +++ b/OpenSim/Data/MySQL/MySQLSimulationData.cs @@ -48,8 +48,18 @@ namespace OpenSim.Data.MySQL public class MySQLSimulationData : ISimulationDataStore { private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private static string LogHeader = "[REGION DB MYSQL]"; private string m_connectionString; + + /// + /// This lock was being used to serialize database operations when the connection was shared, but this has + /// been unnecessary for a long time after we switched to using MySQL's underlying connection pooling instead. + /// FIXME: However, the locks remain in many places since they are effectively providing a level of + /// transactionality. This should be replaced by more efficient database transactions which would not require + /// unrelated operations to block each other or unrelated operations on the same tables from blocking each + /// other. + /// private object m_dbLock = new object(); protected virtual Assembly Assembly @@ -91,7 +101,7 @@ namespace OpenSim.Data.MySQL } catch (Exception e) { - m_log.Error("[REGION DB]: MySQL error in ExecuteReader: " + e.Message); + m_log.ErrorFormat("{0} MySQL error in ExecuteReader: {1}", LogHeader, e); throw; } @@ -119,8 +129,10 @@ namespace OpenSim.Data.MySQL // Eligibility check // - if ((flags & (uint)PrimFlags.Temporary) != 0) - return; + // PrimFlags.Temporary is not used in OpenSim code and cannot + // be guaranteed to always be clear. Don't check it. +// if ((flags & (uint)PrimFlags.Temporary) != 0) +// return; if ((flags & (uint)PrimFlags.TemporaryOnRez) != 0) return; @@ -135,7 +147,7 @@ namespace OpenSim.Data.MySQL foreach (SceneObjectPart prim in obj.Parts) { cmd.Parameters.Clear(); - + cmd.CommandText = "replace into prims (" + "UUID, CreationDate, " + "Name, Text, Description, " + @@ -171,7 +183,11 @@ namespace OpenSim.Data.MySQL "ParticleSystem, ClickAction, Material, " + "CollisionSound, CollisionSoundVolume, " + "PassTouches, " + - "LinkNumber, MediaURL) values (" + "?UUID, " + + "LinkNumber, MediaURL, AttachedPosX, " + + "AttachedPosY, AttachedPosZ, KeyframeMotion, " + + "PhysicsShapeType, Density, GravityModifier, " + + "Friction, Restitution, DynAttrs " + + ") values (" + "?UUID, " + "?CreationDate, ?Name, ?Text, " + "?Description, ?SitName, ?TouchName, " + "?ObjectFlags, ?OwnerMask, ?NextOwnerMask, " + @@ -202,14 +218,18 @@ namespace OpenSim.Data.MySQL "?SaleType, ?ColorR, ?ColorG, " + "?ColorB, ?ColorA, ?ParticleSystem, " + "?ClickAction, ?Material, ?CollisionSound, " + - "?CollisionSoundVolume, ?PassTouches, ?LinkNumber, ?MediaURL)"; - + "?CollisionSoundVolume, ?PassTouches, " + + "?LinkNumber, ?MediaURL, ?AttachedPosX, " + + "?AttachedPosY, ?AttachedPosZ, ?KeyframeMotion, " + + "?PhysicsShapeType, ?Density, ?GravityModifier, " + + "?Friction, ?Restitution, ?DynAttrs)"; + FillPrimCommand(cmd, prim, obj.UUID, regionUUID); - + ExecuteNonQuery(cmd); - + cmd.Parameters.Clear(); - + cmd.CommandText = "replace into primshapes (" + "UUID, Shape, ScaleX, ScaleY, " + "ScaleZ, PCode, PathBegin, PathEnd, " + @@ -219,7 +239,8 @@ namespace OpenSim.Data.MySQL "PathTaperX, PathTaperY, PathTwist, " + "PathTwistBegin, ProfileBegin, ProfileEnd, " + "ProfileCurve, ProfileHollow, Texture, " + - "ExtraParams, State, Media) values (?UUID, " + + "ExtraParams, State, LastAttachPoint, Media) " + + "values (?UUID, " + "?Shape, ?ScaleX, ?ScaleY, ?ScaleZ, " + "?PCode, ?PathBegin, ?PathEnd, " + "?PathScaleX, ?PathScaleY, " + @@ -230,10 +251,10 @@ namespace OpenSim.Data.MySQL "?PathTwistBegin, ?ProfileBegin, " + "?ProfileEnd, ?ProfileCurve, " + "?ProfileHollow, ?Texture, ?ExtraParams, " + - "?State, ?Media)"; - + "?State, ?LastAttachPoint, ?Media)"; + FillShapeCommand(cmd, prim); - + ExecuteNonQuery(cmd); } } @@ -446,7 +467,9 @@ namespace OpenSim.Data.MySQL foreach (SceneObjectPart prim in prims.Values) { if (prim.ParentUUID == UUID.Zero) + { objects[prim.UUID] = new SceneObjectGroup(prim); + } } // Add all of the children objects to the SOGs @@ -559,10 +582,14 @@ namespace OpenSim.Data.MySQL } } + // Legacy entry point for when terrain was always a 256x256 hieghtmap public void StoreTerrain(double[,] ter, UUID regionID) { - m_log.Info("[REGION DB]: Storing terrain"); + StoreTerrain(new HeightmapTerrainData(ter), regionID); + } + public void StoreTerrain(TerrainData terrData, UUID regionID) + { lock (m_dbLock) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) @@ -576,11 +603,18 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); - cmd.CommandText = "insert into terrain (RegionUUID, " + - "Revision, Heightfield) values (?RegionUUID, " + - "1, ?Heightfield)"; + int terrainDBRevision; + Array terrainDBblob; + terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); + + m_log.InfoFormat("{0} Storing terrain. X={1}, Y={2}, rev={3}", + LogHeader, terrData.SizeX, terrData.SizeY, terrainDBRevision); - cmd.Parameters.AddWithValue("Heightfield", SerializeTerrain(ter)); + cmd.CommandText = "insert into terrain (RegionUUID, Revision, Heightfield)" + + "values (?RegionUUID, ?Revision, ?Heightfield)"; + + cmd.Parameters.AddWithValue("Revision", terrainDBRevision); + cmd.Parameters.AddWithValue("Heightfield", terrainDBblob); ExecuteNonQuery(cmd); } @@ -588,9 +622,20 @@ namespace OpenSim.Data.MySQL } } + // Legacy region loading public double[,] LoadTerrain(UUID regionID) { - double[,] terrain = null; + 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 (m_dbLock) { @@ -610,32 +655,15 @@ namespace OpenSim.Data.MySQL while (reader.Read()) { int rev = Convert.ToInt32(reader["Revision"]); - - terrain = new double[(int)Constants.RegionSize, (int)Constants.RegionSize]; - terrain.Initialize(); - - using (MemoryStream mstr = new MemoryStream((byte[])reader["Heightfield"])) - { - using (BinaryReader br = new BinaryReader(mstr)) - { - for (int x = 0; x < (int)Constants.RegionSize; x++) - { - for (int y = 0; y < (int)Constants.RegionSize; y++) - { - terrain[x, y] = br.ReadDouble(); - } - } - } - - m_log.InfoFormat("[REGION DB]: Loaded terrain revision r{0}", rev); - } + byte[] blob = (byte[])reader["Heightfield"]; + terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob); } } } } } - return terrain; + return terrData; } public void RemoveLandObject(UUID globalID) @@ -676,7 +704,7 @@ namespace OpenSim.Data.MySQL "MusicURL, PassHours, PassPrice, SnapshotUUID, " + "UserLocationX, UserLocationY, UserLocationZ, " + "UserLookAtX, UserLookAtY, UserLookAtZ, " + - "AuthbuyerID, OtherCleanTime, MediaType, MediaDescription, " + + "AuthbuyerID, OtherCleanTime, Dwell, MediaType, MediaDescription, " + "MediaSize, MediaLoop, ObscureMusic, ObscureMedia) values (" + "?UUID, ?RegionUUID, " + "?LocalLandID, ?Bitmap, ?Name, ?Description, " + @@ -687,7 +715,7 @@ namespace OpenSim.Data.MySQL "?MusicURL, ?PassHours, ?PassPrice, ?SnapshotUUID, " + "?UserLocationX, ?UserLocationY, ?UserLocationZ, " + "?UserLookAtX, ?UserLookAtY, ?UserLookAtZ, " + - "?AuthbuyerID, ?OtherCleanTime, ?MediaType, ?MediaDescription, "+ + "?AuthbuyerID, ?OtherCleanTime, ?Dwell, ?MediaType, ?MediaDescription, "+ "CONCAT(?MediaWidth, ',', ?MediaHeight), ?MediaLoop, ?ObscureMusic, ?ObscureMedia)"; FillLandCommand(cmd, parcel.LandData, parcel.RegionUUID); @@ -719,95 +747,92 @@ namespace OpenSim.Data.MySQL RegionLightShareData nWP = new RegionLightShareData(); nWP.OnSave += StoreRegionWindlightSettings; - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + string command = "select * from `regionwindlight` where region_id = ?regionID"; + + using (MySqlCommand cmd = new MySqlCommand(command)) { - dbcon.Open(); - - string command = "select * from `regionwindlight` where region_id = ?regionID"; - - using (MySqlCommand cmd = new MySqlCommand(command)) + cmd.Connection = dbcon; + + cmd.Parameters.AddWithValue("?regionID", regionUUID.ToString()); + + IDataReader result = ExecuteReader(cmd); + if (!result.Read()) { - cmd.Connection = dbcon; - - cmd.Parameters.AddWithValue("?regionID", regionUUID.ToString()); - - IDataReader result = ExecuteReader(cmd); - if (!result.Read()) - { - //No result, so store our default windlight profile and return it - nWP.regionID = regionUUID; - StoreRegionWindlightSettings(nWP); - return nWP; - } - else - { - nWP.regionID = DBGuid.FromDB(result["region_id"]); - nWP.waterColor.X = Convert.ToSingle(result["water_color_r"]); - nWP.waterColor.Y = Convert.ToSingle(result["water_color_g"]); - nWP.waterColor.Z = Convert.ToSingle(result["water_color_b"]); - nWP.waterFogDensityExponent = Convert.ToSingle(result["water_fog_density_exponent"]); - nWP.underwaterFogModifier = Convert.ToSingle(result["underwater_fog_modifier"]); - nWP.reflectionWaveletScale.X = Convert.ToSingle(result["reflection_wavelet_scale_1"]); - nWP.reflectionWaveletScale.Y = Convert.ToSingle(result["reflection_wavelet_scale_2"]); - nWP.reflectionWaveletScale.Z = Convert.ToSingle(result["reflection_wavelet_scale_3"]); - nWP.fresnelScale = Convert.ToSingle(result["fresnel_scale"]); - nWP.fresnelOffset = Convert.ToSingle(result["fresnel_offset"]); - nWP.refractScaleAbove = Convert.ToSingle(result["refract_scale_above"]); - nWP.refractScaleBelow = Convert.ToSingle(result["refract_scale_below"]); - nWP.blurMultiplier = Convert.ToSingle(result["blur_multiplier"]); - nWP.bigWaveDirection.X = Convert.ToSingle(result["big_wave_direction_x"]); - nWP.bigWaveDirection.Y = Convert.ToSingle(result["big_wave_direction_y"]); - nWP.littleWaveDirection.X = Convert.ToSingle(result["little_wave_direction_x"]); - nWP.littleWaveDirection.Y = Convert.ToSingle(result["little_wave_direction_y"]); - UUID.TryParse(result["normal_map_texture"].ToString(), out nWP.normalMapTexture); - nWP.horizon.X = Convert.ToSingle(result["horizon_r"]); - nWP.horizon.Y = Convert.ToSingle(result["horizon_g"]); - nWP.horizon.Z = Convert.ToSingle(result["horizon_b"]); - nWP.horizon.W = Convert.ToSingle(result["horizon_i"]); - nWP.hazeHorizon = Convert.ToSingle(result["haze_horizon"]); - nWP.blueDensity.X = Convert.ToSingle(result["blue_density_r"]); - nWP.blueDensity.Y = Convert.ToSingle(result["blue_density_g"]); - nWP.blueDensity.Z = Convert.ToSingle(result["blue_density_b"]); - nWP.blueDensity.W = Convert.ToSingle(result["blue_density_i"]); - nWP.hazeDensity = Convert.ToSingle(result["haze_density"]); - nWP.densityMultiplier = Convert.ToSingle(result["density_multiplier"]); - nWP.distanceMultiplier = Convert.ToSingle(result["distance_multiplier"]); - nWP.maxAltitude = Convert.ToUInt16(result["max_altitude"]); - nWP.sunMoonColor.X = Convert.ToSingle(result["sun_moon_color_r"]); - nWP.sunMoonColor.Y = Convert.ToSingle(result["sun_moon_color_g"]); - nWP.sunMoonColor.Z = Convert.ToSingle(result["sun_moon_color_b"]); - nWP.sunMoonColor.W = Convert.ToSingle(result["sun_moon_color_i"]); - nWP.sunMoonPosition = Convert.ToSingle(result["sun_moon_position"]); - nWP.ambient.X = Convert.ToSingle(result["ambient_r"]); - nWP.ambient.Y = Convert.ToSingle(result["ambient_g"]); - nWP.ambient.Z = Convert.ToSingle(result["ambient_b"]); - nWP.ambient.W = Convert.ToSingle(result["ambient_i"]); - nWP.eastAngle = Convert.ToSingle(result["east_angle"]); - nWP.sunGlowFocus = Convert.ToSingle(result["sun_glow_focus"]); - nWP.sunGlowSize = Convert.ToSingle(result["sun_glow_size"]); - nWP.sceneGamma = Convert.ToSingle(result["scene_gamma"]); - nWP.starBrightness = Convert.ToSingle(result["star_brightness"]); - nWP.cloudColor.X = Convert.ToSingle(result["cloud_color_r"]); - nWP.cloudColor.Y = Convert.ToSingle(result["cloud_color_g"]); - nWP.cloudColor.Z = Convert.ToSingle(result["cloud_color_b"]); - nWP.cloudColor.W = Convert.ToSingle(result["cloud_color_i"]); - nWP.cloudXYDensity.X = Convert.ToSingle(result["cloud_x"]); - nWP.cloudXYDensity.Y = Convert.ToSingle(result["cloud_y"]); - nWP.cloudXYDensity.Z = Convert.ToSingle(result["cloud_density"]); - nWP.cloudCoverage = Convert.ToSingle(result["cloud_coverage"]); - nWP.cloudScale = Convert.ToSingle(result["cloud_scale"]); - nWP.cloudDetailXYDensity.X = Convert.ToSingle(result["cloud_detail_x"]); - nWP.cloudDetailXYDensity.Y = Convert.ToSingle(result["cloud_detail_y"]); - nWP.cloudDetailXYDensity.Z = Convert.ToSingle(result["cloud_detail_density"]); - nWP.cloudScrollX = Convert.ToSingle(result["cloud_scroll_x"]); - nWP.cloudScrollXLock = Convert.ToBoolean(result["cloud_scroll_x_lock"]); - nWP.cloudScrollY = Convert.ToSingle(result["cloud_scroll_y"]); - nWP.cloudScrollYLock = Convert.ToBoolean(result["cloud_scroll_y_lock"]); - nWP.drawClassicClouds = Convert.ToBoolean(result["draw_classic_clouds"]); - nWP.valid = true; - } + //No result, so store our default windlight profile and return it + nWP.regionID = regionUUID; +// StoreRegionWindlightSettings(nWP); + return nWP; + } + else + { + nWP.regionID = DBGuid.FromDB(result["region_id"]); + nWP.waterColor.X = Convert.ToSingle(result["water_color_r"]); + nWP.waterColor.Y = Convert.ToSingle(result["water_color_g"]); + nWP.waterColor.Z = Convert.ToSingle(result["water_color_b"]); + nWP.waterFogDensityExponent = Convert.ToSingle(result["water_fog_density_exponent"]); + nWP.underwaterFogModifier = Convert.ToSingle(result["underwater_fog_modifier"]); + nWP.reflectionWaveletScale.X = Convert.ToSingle(result["reflection_wavelet_scale_1"]); + nWP.reflectionWaveletScale.Y = Convert.ToSingle(result["reflection_wavelet_scale_2"]); + nWP.reflectionWaveletScale.Z = Convert.ToSingle(result["reflection_wavelet_scale_3"]); + nWP.fresnelScale = Convert.ToSingle(result["fresnel_scale"]); + nWP.fresnelOffset = Convert.ToSingle(result["fresnel_offset"]); + nWP.refractScaleAbove = Convert.ToSingle(result["refract_scale_above"]); + nWP.refractScaleBelow = Convert.ToSingle(result["refract_scale_below"]); + nWP.blurMultiplier = Convert.ToSingle(result["blur_multiplier"]); + nWP.bigWaveDirection.X = Convert.ToSingle(result["big_wave_direction_x"]); + nWP.bigWaveDirection.Y = Convert.ToSingle(result["big_wave_direction_y"]); + nWP.littleWaveDirection.X = Convert.ToSingle(result["little_wave_direction_x"]); + nWP.littleWaveDirection.Y = Convert.ToSingle(result["little_wave_direction_y"]); + UUID.TryParse(result["normal_map_texture"].ToString(), out nWP.normalMapTexture); + nWP.horizon.X = Convert.ToSingle(result["horizon_r"]); + nWP.horizon.Y = Convert.ToSingle(result["horizon_g"]); + nWP.horizon.Z = Convert.ToSingle(result["horizon_b"]); + nWP.horizon.W = Convert.ToSingle(result["horizon_i"]); + nWP.hazeHorizon = Convert.ToSingle(result["haze_horizon"]); + nWP.blueDensity.X = Convert.ToSingle(result["blue_density_r"]); + nWP.blueDensity.Y = Convert.ToSingle(result["blue_density_g"]); + nWP.blueDensity.Z = Convert.ToSingle(result["blue_density_b"]); + nWP.blueDensity.W = Convert.ToSingle(result["blue_density_i"]); + nWP.hazeDensity = Convert.ToSingle(result["haze_density"]); + nWP.densityMultiplier = Convert.ToSingle(result["density_multiplier"]); + nWP.distanceMultiplier = Convert.ToSingle(result["distance_multiplier"]); + nWP.maxAltitude = Convert.ToUInt16(result["max_altitude"]); + nWP.sunMoonColor.X = Convert.ToSingle(result["sun_moon_color_r"]); + nWP.sunMoonColor.Y = Convert.ToSingle(result["sun_moon_color_g"]); + nWP.sunMoonColor.Z = Convert.ToSingle(result["sun_moon_color_b"]); + nWP.sunMoonColor.W = Convert.ToSingle(result["sun_moon_color_i"]); + nWP.sunMoonPosition = Convert.ToSingle(result["sun_moon_position"]); + nWP.ambient.X = Convert.ToSingle(result["ambient_r"]); + nWP.ambient.Y = Convert.ToSingle(result["ambient_g"]); + nWP.ambient.Z = Convert.ToSingle(result["ambient_b"]); + nWP.ambient.W = Convert.ToSingle(result["ambient_i"]); + nWP.eastAngle = Convert.ToSingle(result["east_angle"]); + nWP.sunGlowFocus = Convert.ToSingle(result["sun_glow_focus"]); + nWP.sunGlowSize = Convert.ToSingle(result["sun_glow_size"]); + nWP.sceneGamma = Convert.ToSingle(result["scene_gamma"]); + nWP.starBrightness = Convert.ToSingle(result["star_brightness"]); + nWP.cloudColor.X = Convert.ToSingle(result["cloud_color_r"]); + nWP.cloudColor.Y = Convert.ToSingle(result["cloud_color_g"]); + nWP.cloudColor.Z = Convert.ToSingle(result["cloud_color_b"]); + nWP.cloudColor.W = Convert.ToSingle(result["cloud_color_i"]); + nWP.cloudXYDensity.X = Convert.ToSingle(result["cloud_x"]); + nWP.cloudXYDensity.Y = Convert.ToSingle(result["cloud_y"]); + nWP.cloudXYDensity.Z = Convert.ToSingle(result["cloud_density"]); + nWP.cloudCoverage = Convert.ToSingle(result["cloud_coverage"]); + nWP.cloudScale = Convert.ToSingle(result["cloud_scale"]); + nWP.cloudDetailXYDensity.X = Convert.ToSingle(result["cloud_detail_x"]); + nWP.cloudDetailXYDensity.Y = Convert.ToSingle(result["cloud_detail_y"]); + nWP.cloudDetailXYDensity.Z = Convert.ToSingle(result["cloud_detail_density"]); + nWP.cloudScrollX = Convert.ToSingle(result["cloud_scroll_x"]); + nWP.cloudScrollXLock = Convert.ToBoolean(result["cloud_scroll_x_lock"]); + nWP.cloudScrollY = Convert.ToSingle(result["cloud_scroll_y"]); + nWP.cloudScrollYLock = Convert.ToBoolean(result["cloud_scroll_y_lock"]); + nWP.drawClassicClouds = Convert.ToBoolean(result["draw_classic_clouds"]); + nWP.valid = true; } } } @@ -857,124 +882,118 @@ namespace OpenSim.Data.MySQL public void StoreRegionWindlightSettings(RegionLightShareData wl) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) { - dbcon.Open(); - - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "REPLACE INTO `regionwindlight` (`region_id`, `water_color_r`, `water_color_g`, "; - cmd.CommandText += "`water_color_b`, `water_fog_density_exponent`, `underwater_fog_modifier`, "; - cmd.CommandText += "`reflection_wavelet_scale_1`, `reflection_wavelet_scale_2`, `reflection_wavelet_scale_3`, "; - cmd.CommandText += "`fresnel_scale`, `fresnel_offset`, `refract_scale_above`, `refract_scale_below`, "; - cmd.CommandText += "`blur_multiplier`, `big_wave_direction_x`, `big_wave_direction_y`, `little_wave_direction_x`, "; - cmd.CommandText += "`little_wave_direction_y`, `normal_map_texture`, `horizon_r`, `horizon_g`, `horizon_b`, "; - cmd.CommandText += "`horizon_i`, `haze_horizon`, `blue_density_r`, `blue_density_g`, `blue_density_b`, "; - cmd.CommandText += "`blue_density_i`, `haze_density`, `density_multiplier`, `distance_multiplier`, `max_altitude`, "; - cmd.CommandText += "`sun_moon_color_r`, `sun_moon_color_g`, `sun_moon_color_b`, `sun_moon_color_i`, `sun_moon_position`, "; - cmd.CommandText += "`ambient_r`, `ambient_g`, `ambient_b`, `ambient_i`, `east_angle`, `sun_glow_focus`, `sun_glow_size`, "; - cmd.CommandText += "`scene_gamma`, `star_brightness`, `cloud_color_r`, `cloud_color_g`, `cloud_color_b`, `cloud_color_i`, "; - cmd.CommandText += "`cloud_x`, `cloud_y`, `cloud_density`, `cloud_coverage`, `cloud_scale`, `cloud_detail_x`, "; - cmd.CommandText += "`cloud_detail_y`, `cloud_detail_density`, `cloud_scroll_x`, `cloud_scroll_x_lock`, `cloud_scroll_y`, "; - cmd.CommandText += "`cloud_scroll_y_lock`, `draw_classic_clouds`) VALUES (?region_id, ?water_color_r, "; - cmd.CommandText += "?water_color_g, ?water_color_b, ?water_fog_density_exponent, ?underwater_fog_modifier, ?reflection_wavelet_scale_1, "; - cmd.CommandText += "?reflection_wavelet_scale_2, ?reflection_wavelet_scale_3, ?fresnel_scale, ?fresnel_offset, ?refract_scale_above, "; - cmd.CommandText += "?refract_scale_below, ?blur_multiplier, ?big_wave_direction_x, ?big_wave_direction_y, ?little_wave_direction_x, "; - cmd.CommandText += "?little_wave_direction_y, ?normal_map_texture, ?horizon_r, ?horizon_g, ?horizon_b, ?horizon_i, ?haze_horizon, "; - cmd.CommandText += "?blue_density_r, ?blue_density_g, ?blue_density_b, ?blue_density_i, ?haze_density, ?density_multiplier, "; - cmd.CommandText += "?distance_multiplier, ?max_altitude, ?sun_moon_color_r, ?sun_moon_color_g, ?sun_moon_color_b, "; - cmd.CommandText += "?sun_moon_color_i, ?sun_moon_position, ?ambient_r, ?ambient_g, ?ambient_b, ?ambient_i, ?east_angle, "; - cmd.CommandText += "?sun_glow_focus, ?sun_glow_size, ?scene_gamma, ?star_brightness, ?cloud_color_r, ?cloud_color_g, "; - cmd.CommandText += "?cloud_color_b, ?cloud_color_i, ?cloud_x, ?cloud_y, ?cloud_density, ?cloud_coverage, ?cloud_scale, "; - cmd.CommandText += "?cloud_detail_x, ?cloud_detail_y, ?cloud_detail_density, ?cloud_scroll_x, ?cloud_scroll_x_lock, "; - cmd.CommandText += "?cloud_scroll_y, ?cloud_scroll_y_lock, ?draw_classic_clouds)"; - - cmd.Parameters.AddWithValue("region_id", wl.regionID); - cmd.Parameters.AddWithValue("water_color_r", wl.waterColor.X); - cmd.Parameters.AddWithValue("water_color_g", wl.waterColor.Y); - cmd.Parameters.AddWithValue("water_color_b", wl.waterColor.Z); - cmd.Parameters.AddWithValue("water_fog_density_exponent", wl.waterFogDensityExponent); - cmd.Parameters.AddWithValue("underwater_fog_modifier", wl.underwaterFogModifier); - cmd.Parameters.AddWithValue("reflection_wavelet_scale_1", wl.reflectionWaveletScale.X); - cmd.Parameters.AddWithValue("reflection_wavelet_scale_2", wl.reflectionWaveletScale.Y); - cmd.Parameters.AddWithValue("reflection_wavelet_scale_3", wl.reflectionWaveletScale.Z); - cmd.Parameters.AddWithValue("fresnel_scale", wl.fresnelScale); - cmd.Parameters.AddWithValue("fresnel_offset", wl.fresnelOffset); - cmd.Parameters.AddWithValue("refract_scale_above", wl.refractScaleAbove); - cmd.Parameters.AddWithValue("refract_scale_below", wl.refractScaleBelow); - cmd.Parameters.AddWithValue("blur_multiplier", wl.blurMultiplier); - cmd.Parameters.AddWithValue("big_wave_direction_x", wl.bigWaveDirection.X); - cmd.Parameters.AddWithValue("big_wave_direction_y", wl.bigWaveDirection.Y); - cmd.Parameters.AddWithValue("little_wave_direction_x", wl.littleWaveDirection.X); - cmd.Parameters.AddWithValue("little_wave_direction_y", wl.littleWaveDirection.Y); - cmd.Parameters.AddWithValue("normal_map_texture", wl.normalMapTexture); - cmd.Parameters.AddWithValue("horizon_r", wl.horizon.X); - cmd.Parameters.AddWithValue("horizon_g", wl.horizon.Y); - cmd.Parameters.AddWithValue("horizon_b", wl.horizon.Z); - cmd.Parameters.AddWithValue("horizon_i", wl.horizon.W); - cmd.Parameters.AddWithValue("haze_horizon", wl.hazeHorizon); - cmd.Parameters.AddWithValue("blue_density_r", wl.blueDensity.X); - cmd.Parameters.AddWithValue("blue_density_g", wl.blueDensity.Y); - cmd.Parameters.AddWithValue("blue_density_b", wl.blueDensity.Z); - cmd.Parameters.AddWithValue("blue_density_i", wl.blueDensity.W); - cmd.Parameters.AddWithValue("haze_density", wl.hazeDensity); - cmd.Parameters.AddWithValue("density_multiplier", wl.densityMultiplier); - cmd.Parameters.AddWithValue("distance_multiplier", wl.distanceMultiplier); - cmd.Parameters.AddWithValue("max_altitude", wl.maxAltitude); - cmd.Parameters.AddWithValue("sun_moon_color_r", wl.sunMoonColor.X); - cmd.Parameters.AddWithValue("sun_moon_color_g", wl.sunMoonColor.Y); - cmd.Parameters.AddWithValue("sun_moon_color_b", wl.sunMoonColor.Z); - cmd.Parameters.AddWithValue("sun_moon_color_i", wl.sunMoonColor.W); - cmd.Parameters.AddWithValue("sun_moon_position", wl.sunMoonPosition); - cmd.Parameters.AddWithValue("ambient_r", wl.ambient.X); - cmd.Parameters.AddWithValue("ambient_g", wl.ambient.Y); - cmd.Parameters.AddWithValue("ambient_b", wl.ambient.Z); - cmd.Parameters.AddWithValue("ambient_i", wl.ambient.W); - cmd.Parameters.AddWithValue("east_angle", wl.eastAngle); - cmd.Parameters.AddWithValue("sun_glow_focus", wl.sunGlowFocus); - cmd.Parameters.AddWithValue("sun_glow_size", wl.sunGlowSize); - cmd.Parameters.AddWithValue("scene_gamma", wl.sceneGamma); - cmd.Parameters.AddWithValue("star_brightness", wl.starBrightness); - cmd.Parameters.AddWithValue("cloud_color_r", wl.cloudColor.X); - cmd.Parameters.AddWithValue("cloud_color_g", wl.cloudColor.Y); - cmd.Parameters.AddWithValue("cloud_color_b", wl.cloudColor.Z); - cmd.Parameters.AddWithValue("cloud_color_i", wl.cloudColor.W); - cmd.Parameters.AddWithValue("cloud_x", wl.cloudXYDensity.X); - cmd.Parameters.AddWithValue("cloud_y", wl.cloudXYDensity.Y); - cmd.Parameters.AddWithValue("cloud_density", wl.cloudXYDensity.Z); - cmd.Parameters.AddWithValue("cloud_coverage", wl.cloudCoverage); - cmd.Parameters.AddWithValue("cloud_scale", wl.cloudScale); - cmd.Parameters.AddWithValue("cloud_detail_x", wl.cloudDetailXYDensity.X); - cmd.Parameters.AddWithValue("cloud_detail_y", wl.cloudDetailXYDensity.Y); - cmd.Parameters.AddWithValue("cloud_detail_density", wl.cloudDetailXYDensity.Z); - cmd.Parameters.AddWithValue("cloud_scroll_x", wl.cloudScrollX); - cmd.Parameters.AddWithValue("cloud_scroll_x_lock", wl.cloudScrollXLock); - cmd.Parameters.AddWithValue("cloud_scroll_y", wl.cloudScrollY); - cmd.Parameters.AddWithValue("cloud_scroll_y_lock", wl.cloudScrollYLock); - cmd.Parameters.AddWithValue("draw_classic_clouds", wl.drawClassicClouds); - - ExecuteNonQuery(cmd); - } + cmd.CommandText = "REPLACE INTO `regionwindlight` (`region_id`, `water_color_r`, `water_color_g`, "; + cmd.CommandText += "`water_color_b`, `water_fog_density_exponent`, `underwater_fog_modifier`, "; + cmd.CommandText += "`reflection_wavelet_scale_1`, `reflection_wavelet_scale_2`, `reflection_wavelet_scale_3`, "; + cmd.CommandText += "`fresnel_scale`, `fresnel_offset`, `refract_scale_above`, `refract_scale_below`, "; + cmd.CommandText += "`blur_multiplier`, `big_wave_direction_x`, `big_wave_direction_y`, `little_wave_direction_x`, "; + cmd.CommandText += "`little_wave_direction_y`, `normal_map_texture`, `horizon_r`, `horizon_g`, `horizon_b`, "; + cmd.CommandText += "`horizon_i`, `haze_horizon`, `blue_density_r`, `blue_density_g`, `blue_density_b`, "; + cmd.CommandText += "`blue_density_i`, `haze_density`, `density_multiplier`, `distance_multiplier`, `max_altitude`, "; + cmd.CommandText += "`sun_moon_color_r`, `sun_moon_color_g`, `sun_moon_color_b`, `sun_moon_color_i`, `sun_moon_position`, "; + cmd.CommandText += "`ambient_r`, `ambient_g`, `ambient_b`, `ambient_i`, `east_angle`, `sun_glow_focus`, `sun_glow_size`, "; + cmd.CommandText += "`scene_gamma`, `star_brightness`, `cloud_color_r`, `cloud_color_g`, `cloud_color_b`, `cloud_color_i`, "; + cmd.CommandText += "`cloud_x`, `cloud_y`, `cloud_density`, `cloud_coverage`, `cloud_scale`, `cloud_detail_x`, "; + cmd.CommandText += "`cloud_detail_y`, `cloud_detail_density`, `cloud_scroll_x`, `cloud_scroll_x_lock`, `cloud_scroll_y`, "; + cmd.CommandText += "`cloud_scroll_y_lock`, `draw_classic_clouds`) VALUES (?region_id, ?water_color_r, "; + cmd.CommandText += "?water_color_g, ?water_color_b, ?water_fog_density_exponent, ?underwater_fog_modifier, ?reflection_wavelet_scale_1, "; + cmd.CommandText += "?reflection_wavelet_scale_2, ?reflection_wavelet_scale_3, ?fresnel_scale, ?fresnel_offset, ?refract_scale_above, "; + cmd.CommandText += "?refract_scale_below, ?blur_multiplier, ?big_wave_direction_x, ?big_wave_direction_y, ?little_wave_direction_x, "; + cmd.CommandText += "?little_wave_direction_y, ?normal_map_texture, ?horizon_r, ?horizon_g, ?horizon_b, ?horizon_i, ?haze_horizon, "; + cmd.CommandText += "?blue_density_r, ?blue_density_g, ?blue_density_b, ?blue_density_i, ?haze_density, ?density_multiplier, "; + cmd.CommandText += "?distance_multiplier, ?max_altitude, ?sun_moon_color_r, ?sun_moon_color_g, ?sun_moon_color_b, "; + cmd.CommandText += "?sun_moon_color_i, ?sun_moon_position, ?ambient_r, ?ambient_g, ?ambient_b, ?ambient_i, ?east_angle, "; + cmd.CommandText += "?sun_glow_focus, ?sun_glow_size, ?scene_gamma, ?star_brightness, ?cloud_color_r, ?cloud_color_g, "; + cmd.CommandText += "?cloud_color_b, ?cloud_color_i, ?cloud_x, ?cloud_y, ?cloud_density, ?cloud_coverage, ?cloud_scale, "; + cmd.CommandText += "?cloud_detail_x, ?cloud_detail_y, ?cloud_detail_density, ?cloud_scroll_x, ?cloud_scroll_x_lock, "; + cmd.CommandText += "?cloud_scroll_y, ?cloud_scroll_y_lock, ?draw_classic_clouds)"; + + cmd.Parameters.AddWithValue("region_id", wl.regionID); + cmd.Parameters.AddWithValue("water_color_r", wl.waterColor.X); + cmd.Parameters.AddWithValue("water_color_g", wl.waterColor.Y); + cmd.Parameters.AddWithValue("water_color_b", wl.waterColor.Z); + cmd.Parameters.AddWithValue("water_fog_density_exponent", wl.waterFogDensityExponent); + cmd.Parameters.AddWithValue("underwater_fog_modifier", wl.underwaterFogModifier); + cmd.Parameters.AddWithValue("reflection_wavelet_scale_1", wl.reflectionWaveletScale.X); + cmd.Parameters.AddWithValue("reflection_wavelet_scale_2", wl.reflectionWaveletScale.Y); + cmd.Parameters.AddWithValue("reflection_wavelet_scale_3", wl.reflectionWaveletScale.Z); + cmd.Parameters.AddWithValue("fresnel_scale", wl.fresnelScale); + cmd.Parameters.AddWithValue("fresnel_offset", wl.fresnelOffset); + cmd.Parameters.AddWithValue("refract_scale_above", wl.refractScaleAbove); + cmd.Parameters.AddWithValue("refract_scale_below", wl.refractScaleBelow); + cmd.Parameters.AddWithValue("blur_multiplier", wl.blurMultiplier); + cmd.Parameters.AddWithValue("big_wave_direction_x", wl.bigWaveDirection.X); + cmd.Parameters.AddWithValue("big_wave_direction_y", wl.bigWaveDirection.Y); + cmd.Parameters.AddWithValue("little_wave_direction_x", wl.littleWaveDirection.X); + cmd.Parameters.AddWithValue("little_wave_direction_y", wl.littleWaveDirection.Y); + cmd.Parameters.AddWithValue("normal_map_texture", wl.normalMapTexture); + cmd.Parameters.AddWithValue("horizon_r", wl.horizon.X); + cmd.Parameters.AddWithValue("horizon_g", wl.horizon.Y); + cmd.Parameters.AddWithValue("horizon_b", wl.horizon.Z); + cmd.Parameters.AddWithValue("horizon_i", wl.horizon.W); + cmd.Parameters.AddWithValue("haze_horizon", wl.hazeHorizon); + cmd.Parameters.AddWithValue("blue_density_r", wl.blueDensity.X); + cmd.Parameters.AddWithValue("blue_density_g", wl.blueDensity.Y); + cmd.Parameters.AddWithValue("blue_density_b", wl.blueDensity.Z); + cmd.Parameters.AddWithValue("blue_density_i", wl.blueDensity.W); + cmd.Parameters.AddWithValue("haze_density", wl.hazeDensity); + cmd.Parameters.AddWithValue("density_multiplier", wl.densityMultiplier); + cmd.Parameters.AddWithValue("distance_multiplier", wl.distanceMultiplier); + cmd.Parameters.AddWithValue("max_altitude", wl.maxAltitude); + cmd.Parameters.AddWithValue("sun_moon_color_r", wl.sunMoonColor.X); + cmd.Parameters.AddWithValue("sun_moon_color_g", wl.sunMoonColor.Y); + cmd.Parameters.AddWithValue("sun_moon_color_b", wl.sunMoonColor.Z); + cmd.Parameters.AddWithValue("sun_moon_color_i", wl.sunMoonColor.W); + cmd.Parameters.AddWithValue("sun_moon_position", wl.sunMoonPosition); + cmd.Parameters.AddWithValue("ambient_r", wl.ambient.X); + cmd.Parameters.AddWithValue("ambient_g", wl.ambient.Y); + cmd.Parameters.AddWithValue("ambient_b", wl.ambient.Z); + cmd.Parameters.AddWithValue("ambient_i", wl.ambient.W); + cmd.Parameters.AddWithValue("east_angle", wl.eastAngle); + cmd.Parameters.AddWithValue("sun_glow_focus", wl.sunGlowFocus); + cmd.Parameters.AddWithValue("sun_glow_size", wl.sunGlowSize); + cmd.Parameters.AddWithValue("scene_gamma", wl.sceneGamma); + cmd.Parameters.AddWithValue("star_brightness", wl.starBrightness); + cmd.Parameters.AddWithValue("cloud_color_r", wl.cloudColor.X); + cmd.Parameters.AddWithValue("cloud_color_g", wl.cloudColor.Y); + cmd.Parameters.AddWithValue("cloud_color_b", wl.cloudColor.Z); + cmd.Parameters.AddWithValue("cloud_color_i", wl.cloudColor.W); + cmd.Parameters.AddWithValue("cloud_x", wl.cloudXYDensity.X); + cmd.Parameters.AddWithValue("cloud_y", wl.cloudXYDensity.Y); + cmd.Parameters.AddWithValue("cloud_density", wl.cloudXYDensity.Z); + cmd.Parameters.AddWithValue("cloud_coverage", wl.cloudCoverage); + cmd.Parameters.AddWithValue("cloud_scale", wl.cloudScale); + cmd.Parameters.AddWithValue("cloud_detail_x", wl.cloudDetailXYDensity.X); + cmd.Parameters.AddWithValue("cloud_detail_y", wl.cloudDetailXYDensity.Y); + cmd.Parameters.AddWithValue("cloud_detail_density", wl.cloudDetailXYDensity.Z); + cmd.Parameters.AddWithValue("cloud_scroll_x", wl.cloudScrollX); + cmd.Parameters.AddWithValue("cloud_scroll_x_lock", wl.cloudScrollXLock); + cmd.Parameters.AddWithValue("cloud_scroll_y", wl.cloudScrollY); + cmd.Parameters.AddWithValue("cloud_scroll_y_lock", wl.cloudScrollYLock); + cmd.Parameters.AddWithValue("draw_classic_clouds", wl.drawClassicClouds); + + ExecuteNonQuery(cmd); } } } public void RemoveRegionWindlightSettings(UUID regionID) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) { - dbcon.Open(); - - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "delete from `regionwindlight` where `region_id`=?regionID"; - cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); - ExecuteNonQuery(cmd); - } + cmd.CommandText = "delete from `regionwindlight` where `region_id`=?regionID"; + cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); + ExecuteNonQuery(cmd); } } } @@ -982,29 +1001,26 @@ namespace OpenSim.Data.MySQL #region RegionEnvironmentSettings public string LoadRegionEnvironmentSettings(UUID regionUUID) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + string command = "select * from `regionenvironment` where region_id = ?region_id"; + + using (MySqlCommand cmd = new MySqlCommand(command)) { - dbcon.Open(); - - string command = "select * from `regionenvironment` where region_id = ?region_id"; - - using (MySqlCommand cmd = new MySqlCommand(command)) + cmd.Connection = dbcon; + + cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); + + IDataReader result = ExecuteReader(cmd); + if (!result.Read()) { - cmd.Connection = dbcon; - - cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); - - IDataReader result = ExecuteReader(cmd); - if (!result.Read()) - { - return String.Empty; - } - else - { - return Convert.ToString(result["llsd_settings"]); - } + return String.Empty; + } + else + { + return Convert.ToString(result["llsd_settings"]); } } } @@ -1012,39 +1028,33 @@ namespace OpenSim.Data.MySQL public void StoreRegionEnvironmentSettings(UUID regionUUID, string settings) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) { - dbcon.Open(); - - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "REPLACE INTO `regionenvironment` (`region_id`, `llsd_settings`) VALUES (?region_id, ?llsd_settings)"; - - cmd.Parameters.AddWithValue("region_id", regionUUID); - cmd.Parameters.AddWithValue("llsd_settings", settings); - - ExecuteNonQuery(cmd); - } + cmd.CommandText = "REPLACE INTO `regionenvironment` (`region_id`, `llsd_settings`) VALUES (?region_id, ?llsd_settings)"; + + cmd.Parameters.AddWithValue("region_id", regionUUID); + cmd.Parameters.AddWithValue("llsd_settings", settings); + + ExecuteNonQuery(cmd); } } } public void RemoveRegionEnvironmentSettings(UUID regionUUID) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) { - dbcon.Open(); - - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "delete from `regionenvironment` where region_id = ?region_id"; - cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); - ExecuteNonQuery(cmd); - } + cmd.CommandText = "delete from `regionenvironment` where region_id = ?region_id"; + cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); + ExecuteNonQuery(cmd); } } } @@ -1052,56 +1062,55 @@ namespace OpenSim.Data.MySQL public void StoreRegionSettings(RegionSettings rs) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "replace into regionsettings (regionUUID, " + - "block_terraform, block_fly, allow_damage, " + - "restrict_pushing, allow_land_resell, " + - "allow_land_join_divide, block_show_in_search, " + - "agent_limit, object_bonus, maturity, " + - "disable_scripts, disable_collisions, " + - "disable_physics, terrain_texture_1, " + - "terrain_texture_2, terrain_texture_3, " + - "terrain_texture_4, elevation_1_nw, " + - "elevation_2_nw, elevation_1_ne, " + - "elevation_2_ne, elevation_1_se, " + - "elevation_2_se, elevation_1_sw, " + - "elevation_2_sw, water_height, " + - "terrain_raise_limit, terrain_lower_limit, " + - "use_estate_sun, fixed_sun, sun_position, " + - "covenant, covenant_datetime, Sandbox, sunvectorx, sunvectory, " + - "sunvectorz, loaded_creation_datetime, " + - "loaded_creation_id, map_tile_ID, " + - "TelehubObject, parcel_tile_ID) " + - "values (?RegionUUID, ?BlockTerraform, " + - "?BlockFly, ?AllowDamage, ?RestrictPushing, " + - "?AllowLandResell, ?AllowLandJoinDivide, " + - "?BlockShowInSearch, ?AgentLimit, ?ObjectBonus, " + - "?Maturity, ?DisableScripts, ?DisableCollisions, " + - "?DisablePhysics, ?TerrainTexture1, " + - "?TerrainTexture2, ?TerrainTexture3, " + - "?TerrainTexture4, ?Elevation1NW, ?Elevation2NW, " + - "?Elevation1NE, ?Elevation2NE, ?Elevation1SE, " + - "?Elevation2SE, ?Elevation1SW, ?Elevation2SW, " + - "?WaterHeight, ?TerrainRaiseLimit, " + - "?TerrainLowerLimit, ?UseEstateSun, ?FixedSun, " + - "?SunPosition, ?Covenant, ?CovenantChangedDateTime, ?Sandbox, " + - "?SunVectorX, ?SunVectorY, ?SunVectorZ, " + - "?LoadedCreationDateTime, ?LoadedCreationID, " + - "?TerrainImageID, ?TelehubObject, ?ParcelImageID) "; - - FillRegionSettingsCommand(cmd, rs); + dbcon.Open(); - ExecuteNonQuery(cmd); - } + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "replace into regionsettings (regionUUID, " + + "block_terraform, block_fly, allow_damage, " + + "restrict_pushing, allow_land_resell, " + + "allow_land_join_divide, block_show_in_search, " + + "agent_limit, object_bonus, maturity, " + + "disable_scripts, disable_collisions, " + + "disable_physics, terrain_texture_1, " + + "terrain_texture_2, terrain_texture_3, " + + "terrain_texture_4, elevation_1_nw, " + + "elevation_2_nw, elevation_1_ne, " + + "elevation_2_ne, elevation_1_se, " + + "elevation_2_se, elevation_1_sw, " + + "elevation_2_sw, water_height, " + + "terrain_raise_limit, terrain_lower_limit, " + + "use_estate_sun, fixed_sun, sun_position, " + + "covenant, covenant_datetime, Sandbox, sunvectorx, sunvectory, " + + "sunvectorz, loaded_creation_datetime, " + + "loaded_creation_id, map_tile_ID, " + + "TelehubObject, parcel_tile_ID) " + + "values (?RegionUUID, ?BlockTerraform, " + + "?BlockFly, ?AllowDamage, ?RestrictPushing, " + + "?AllowLandResell, ?AllowLandJoinDivide, " + + "?BlockShowInSearch, ?AgentLimit, ?ObjectBonus, " + + "?Maturity, ?DisableScripts, ?DisableCollisions, " + + "?DisablePhysics, ?TerrainTexture1, " + + "?TerrainTexture2, ?TerrainTexture3, " + + "?TerrainTexture4, ?Elevation1NW, ?Elevation2NW, " + + "?Elevation1NE, ?Elevation2NE, ?Elevation1SE, " + + "?Elevation2SE, ?Elevation1SW, ?Elevation2SW, " + + "?WaterHeight, ?TerrainRaiseLimit, " + + "?TerrainLowerLimit, ?UseEstateSun, ?FixedSun, " + + "?SunPosition, ?Covenant, ?CovenantChangedDateTime, ?Sandbox, " + + "?SunVectorX, ?SunVectorY, ?SunVectorZ, " + + "?LoadedCreationDateTime, ?LoadedCreationID, " + + "?TerrainImageID, " + + "?TelehubObject, ?ParcelImageID)"; + + FillRegionSettingsCommand(cmd, rs); + + ExecuteNonQuery(cmd); } } + SaveSpawnPoints(rs); } @@ -1292,6 +1301,39 @@ namespace OpenSim.Data.MySQL if (!(row["MediaURL"] is System.DBNull)) prim.MediaUrl = (string)row["MediaURL"]; + if (!(row["AttachedPosX"] is System.DBNull)) + { + prim.AttachedPos = new Vector3( + (float)(double)row["AttachedPosX"], + (float)(double)row["AttachedPosY"], + (float)(double)row["AttachedPosZ"] + ); + } + + if (!(row["DynAttrs"] is System.DBNull)) + prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]); + else + prim.DynAttrs = new DAMap(); + + 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; + } + + prim.PhysicsShapeType = (byte)Convert.ToInt32(row["PhysicsShapeType"].ToString()); + prim.Density = (float)(double)row["Density"]; + prim.GravityModifier = (float)(double)row["GravityModifier"]; + prim.Friction = (float)(double)row["Friction"]; + prim.Restitution = (float)(double)row["Restitution"]; + return prim; } @@ -1431,6 +1473,7 @@ namespace OpenSim.Data.MySQL UUID.TryParse((string)row["AuthBuyerID"], out authedbuyer); UUID.TryParse((string)row["SnapshotUUID"], out snapshotID); newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]); + newData.Dwell = Convert.ToSingle(row["Dwell"]); newData.AuthBuyerID = authedbuyer; newData.SnapshotID = snapshotID; @@ -1478,30 +1521,6 @@ namespace OpenSim.Data.MySQL } /// - /// - /// - /// - /// - 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++) - { - double height = val[x, y]; - if (height == 0.0) - height = double.Epsilon; - - bw.Write(height); - } - - return str.ToArray(); - } - - /// /// Fill the prim command with prim values /// /// @@ -1637,6 +1656,28 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("LinkNumber", prim.LinkNum); cmd.Parameters.AddWithValue("MediaURL", prim.MediaUrl); + if (prim.AttachedPos != null) + { + cmd.Parameters.AddWithValue("AttachedPosX", (double)prim.AttachedPos.X); + cmd.Parameters.AddWithValue("AttachedPosY", (double)prim.AttachedPos.Y); + cmd.Parameters.AddWithValue("AttachedPosZ", (double)prim.AttachedPos.Z); + } + + if (prim.KeyframeMotion != null) + cmd.Parameters.AddWithValue("KeyframeMotion", prim.KeyframeMotion.Serialize()); + else + cmd.Parameters.AddWithValue("KeyframeMotion", new Byte[0]); + + if (prim.DynAttrs.CountNamespaces > 0) + cmd.Parameters.AddWithValue("DynAttrs", prim.DynAttrs.ToXml()); + else + cmd.Parameters.AddWithValue("DynAttrs", null); + + cmd.Parameters.AddWithValue("PhysicsShapeType", prim.PhysicsShapeType); + cmd.Parameters.AddWithValue("Density", (double)prim.Density); + cmd.Parameters.AddWithValue("GravityModifier", (double)prim.GravityModifier); + cmd.Parameters.AddWithValue("Friction", (double)prim.Friction); + cmd.Parameters.AddWithValue("Restitution", (double)prim.Restitution); } /// @@ -1715,6 +1756,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("LoadedCreationDateTime", settings.LoadedCreationDateTime); cmd.Parameters.AddWithValue("LoadedCreationID", settings.LoadedCreationID); cmd.Parameters.AddWithValue("TerrainImageID", settings.TerrainImageID); + cmd.Parameters.AddWithValue("ParcelImageID", settings.ParcelImageID); cmd.Parameters.AddWithValue("TelehubObject", settings.TelehubObject); } @@ -1763,6 +1805,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("UserLookAtZ", land.UserLookAt.Z); cmd.Parameters.AddWithValue("AuthBuyerID", land.AuthBuyerID); cmd.Parameters.AddWithValue("OtherCleanTime", land.OtherCleanTime); + cmd.Parameters.AddWithValue("Dwell", land.Dwell); cmd.Parameters.AddWithValue("MediaDescription", land.MediaDescription); cmd.Parameters.AddWithValue("MediaType", land.MediaType); cmd.Parameters.AddWithValue("MediaWidth", land.MediaWidth); @@ -1770,7 +1813,6 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("MediaLoop", land.MediaLoop); cmd.Parameters.AddWithValue("ObscureMusic", land.ObscureMusic); cmd.Parameters.AddWithValue("ObscureMedia", land.ObscureMedia); - } /// @@ -1826,6 +1868,7 @@ namespace OpenSim.Data.MySQL s.ExtraParams = (byte[])row["ExtraParams"]; s.State = (byte)(int)row["State"]; + s.LastAttachPoint = (byte)(int)row["LastAttachPoint"]; if (!(row["Media"] is System.DBNull)) s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]); @@ -1872,6 +1915,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("Texture", s.TextureEntry); cmd.Parameters.AddWithValue("ExtraParams", s.ExtraParams); cmd.Parameters.AddWithValue("State", s.State); + cmd.Parameters.AddWithValue("LastAttachPoint", s.LastAttachPoint); cmd.Parameters.AddWithValue("Media", null == s.Media ? null : s.Media.ToXml()); } @@ -1988,41 +2032,35 @@ namespace OpenSim.Data.MySQL public void SaveExtra(UUID regionID, string name, string val) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + dbcon.Open(); - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "replace into regionextra values (?RegionID, ?Name, ?value)"; - cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - cmd.Parameters.AddWithValue("?Name", name); - cmd.Parameters.AddWithValue("?value", val); + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "replace into regionextra values (?RegionID, ?Name, ?value)"; + cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); + cmd.Parameters.AddWithValue("?Name", name); + cmd.Parameters.AddWithValue("?value", val); - cmd.ExecuteNonQuery(); - } + cmd.ExecuteNonQuery(); } } } public void RemoveExtra(UUID regionID, string name) { - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + dbcon.Open(); - using (MySqlCommand cmd = dbcon.CreateCommand()) - { - cmd.CommandText = "delete from regionextra where RegionID=?RegionID and Name=?Name"; - cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - cmd.Parameters.AddWithValue("?Name", name); + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "delete from regionextra where RegionID=?RegionID and Name=?Name"; + cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); + cmd.Parameters.AddWithValue("?Name", name); - cmd.ExecuteNonQuery(); - } + cmd.ExecuteNonQuery(); } } } @@ -2031,22 +2069,19 @@ namespace OpenSim.Data.MySQL { Dictionary ret = new Dictionary(); - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + dbcon.Open(); - using (MySqlCommand cmd = dbcon.CreateCommand()) + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "select * from regionextra where RegionID=?RegionID"; + cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); + using (IDataReader r = cmd.ExecuteReader()) { - cmd.CommandText = "select * from regionextra where RegionID=?RegionID"; - cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - using (IDataReader r = cmd.ExecuteReader()) + while (r.Read()) { - while (r.Read()) - { - ret[r["Name"].ToString()] = r["value"].ToString(); - } + ret[r["Name"].ToString()] = r["value"].ToString(); } } } diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs new file mode 100644 index 0000000..b35595d --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs @@ -0,0 +1,1086 @@ +/* + * 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 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.ErrorFormat("[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.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 = ?recordId"; + + try + { + using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) + { + dbcon.Open(); + + using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("?recordId", 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) + { + 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.ErrorFormat("[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.ErrorFormat("[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"]; + pick.Gatekeeper = (string)reader["gatekeeper"]; + 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.ParcelName = (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.ErrorFormat("[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 += "?Gatekeeper)"; + query += "ON DUPLICATE KEY UPDATE "; + query += "parceluuid=?ParcelId,"; + query += "name=?Name,"; + query += "description=?Desc,"; + query += "user=?User,"; + query += "simname=?SimName,"; + query += "snapshotuuid=?SnapshotId,"; + query += "pickuuid=?PickId,"; + query += "posglobal=?GlobalPos,"; + query += "gatekeeper=?Gatekeeper"; + + 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.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("?Gatekeeper",pick.Gatekeeper); + cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ()); + cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString()); + + 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 (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.ErrorFormat("[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.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 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.ErrorFormat("[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) + { + 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)"; + + 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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[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.ErrorFormat("[PROFILES_DATA]" + + ": GetAvatarNotes exception {0}", e.Message); + } + return data; + } + + #region User Preferences + public bool GetUserPreferences(ref UserPreferences pref, ref string result) + { + string query = string.Empty; + + query += "SELECT imviaemail,visible,email FROM "; + query += "usersettings WHERE "; + query += "useruuid = ?Id"; + + OSDArray data = new OSDArray(); + + try + { + using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) + { + dbcon.Open(); + using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString()); + + using (MySqlDataReader reader = cmd.ExecuteReader()) + { + if(reader.HasRows) + { + reader.Read(); + bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail); + bool.TryParse((string)reader["visible"], out pref.Visible); + pref.EMail = (string)reader["email"]; + } + 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.ErrorFormat("[PROFILES_DATA]" + + ": Get preferences 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 (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) + { + dbcon.Open(); + using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) + { + cmd.Parameters.AddWithValue("?ImViaEmail", pref.IMViaEmail.ToString().ToLower()); + cmd.Parameters.AddWithValue("?Visible", pref.Visible.ToString().ToLower()); + cmd.Parameters.AddWithValue("?uuid", pref.UserId.ToString()); + cmd.Parameters.AddWithValue("?EMail", pref.EMail.ToString().ToLower()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.ErrorFormat("[PROFILES_DATA]" + + ": UserPreferencesUpdate exception {0} {1}", e.Message, e.InnerException); + result = 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("?UserId", 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 (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()); + + cmd.ExecuteNonQuery(); + } + } + } + catch (Exception e) + { + m_log.ErrorFormat("[PROFILES_DATA]" + + ": SetUserData exception {0}", e.Message); + return false; + } + return true; + } + #endregion Integration + } +} \ No newline at end of file diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs index e6ac22e..af7e876 100644 --- a/OpenSim/Data/MySQL/MySQLXAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLXAssetData.cs @@ -50,9 +50,13 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } + /// + /// Number of days that must pass before we update the access time on an asset when it has been fetched. + /// + private const int DaysBetweenAccessTimeUpdates = 30; + private bool m_enableCompression = false; private string m_connectionString; - private object m_dbLock = new object(); /// /// We can reuse this for all hashing since all methods are single-threaded through m_dbBLock @@ -126,58 +130,58 @@ namespace OpenSim.Data.MySQL // m_log.DebugFormat("[MYSQL XASSET DATA]: Looking for asset {0}", assetID); AssetBase asset = null; - lock (m_dbLock) + + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlCommand cmd = new MySqlCommand( + "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID", + dbcon)) { - dbcon.Open(); + cmd.Parameters.AddWithValue("?ID", assetID.ToString()); - using (MySqlCommand cmd = new MySqlCommand( - "SELECT name, description, asset_type, local, temporary, asset_flags, creator_id, data FROM xassetsmeta JOIN xassetsdata ON xassetsmeta.hash = xassetsdata.hash WHERE id=?id", - dbcon)) + try { - cmd.Parameters.AddWithValue("?id", assetID.ToString()); - - try + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + if (dbReader.Read()) { - if (dbReader.Read()) - { - asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["asset_type"], dbReader["creator_id"].ToString()); - asset.Data = (byte[])dbReader["data"]; - asset.Description = (string)dbReader["description"]; + asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString()); + asset.Data = (byte[])dbReader["Data"]; + asset.Description = (string)dbReader["Description"]; - string local = dbReader["local"].ToString(); - if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) - asset.Local = true; - else - asset.Local = false; + string local = dbReader["Local"].ToString(); + if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) + asset.Local = true; + else + asset.Local = false; - asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); - asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); + asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]); + asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); - if (m_enableCompression) + if (m_enableCompression) + { + using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress)) { - using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress)) - { - MemoryStream outputStream = new MemoryStream(); - WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue); - // int compressedLength = asset.Data.Length; - asset.Data = outputStream.ToArray(); - - // m_log.DebugFormat( - // "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}", - // asset.ID, asset.Name, asset.Data.Length, compressedLength); - } + MemoryStream outputStream = new MemoryStream(); + WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue); +// int compressedLength = asset.Data.Length; + asset.Data = outputStream.ToArray(); + +// m_log.DebugFormat( +// "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}", +// asset.ID, asset.Name, asset.Data.Length, compressedLength); } } + + UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]); } } - catch (Exception e) - { - m_log.Error("[MYSQL XASSET DATA]: MySql failure fetching asset " + assetID + ": " + e.Message); - } + } + catch (Exception e) + { + m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e); } } } @@ -192,148 +196,156 @@ namespace OpenSim.Data.MySQL /// On failure : Throw an exception and attempt to reconnect to database public void StoreAsset(AssetBase asset) { - lock (m_dbLock) +// m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID); + + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + + using (MySqlTransaction transaction = dbcon.BeginTransaction()) { - dbcon.Open(); + string assetName = asset.Name; + if (asset.Name.Length > AssetBase.MAX_ASSET_NAME) + { + assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME); + m_log.WarnFormat( + "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Name, asset.ID, asset.Name.Length, assetName.Length); + } - using (MySqlTransaction transaction = dbcon.BeginTransaction()) + string assetDescription = asset.Description; + if (asset.Description.Length > AssetBase.MAX_ASSET_DESC) { - string assetName = asset.Name; - if (asset.Name.Length > 64) - { - assetName = asset.Name.Substring(0, 64); - m_log.Warn("[XASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add"); - } - - string assetDescription = asset.Description; - if (asset.Description.Length > 64) - { - assetDescription = asset.Description.Substring(0, 64); - m_log.Warn("[XASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); - } + assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC); + m_log.WarnFormat( + "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); + } - if (m_enableCompression) - { - MemoryStream outputStream = new MemoryStream(); + if (m_enableCompression) + { + MemoryStream outputStream = new MemoryStream(); - using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false)) - { - // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue)); - // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream. - compressionStream.Close(); - byte[] compressedData = outputStream.ToArray(); - asset.Data = compressedData; - } + using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false)) + { +// Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue)); + // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream. + compressionStream.Close(); + byte[] compressedData = outputStream.ToArray(); + asset.Data = compressedData; } + } - byte[] hash = hasher.ComputeHash(asset.Data); + byte[] hash = hasher.ComputeHash(asset.Data); // m_log.DebugFormat( // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}", // asset.ID, asset.Name, hash, compressedData.Length); + try + { + using (MySqlCommand cmd = + new MySqlCommand( + "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" + + "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)", + dbcon)) + { + // create unix epoch time + int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); + cmd.Parameters.AddWithValue("?ID", asset.ID); + cmd.Parameters.AddWithValue("?Hash", hash); + cmd.Parameters.AddWithValue("?Name", assetName); + cmd.Parameters.AddWithValue("?Description", assetDescription); + cmd.Parameters.AddWithValue("?AssetType", asset.Type); + cmd.Parameters.AddWithValue("?Local", asset.Local); + cmd.Parameters.AddWithValue("?Temporary", asset.Temporary); + cmd.Parameters.AddWithValue("?CreateTime", now); + cmd.Parameters.AddWithValue("?AccessTime", now); + cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID); + cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags); + cmd.ExecuteNonQuery(); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}", + asset.FullID, asset.Name, e.Message); + + transaction.Rollback(); + + return; + } + + if (!ExistsData(dbcon, transaction, hash)) + { try { using (MySqlCommand cmd = new MySqlCommand( - "replace INTO xassetsmeta(id, hash, name, description, asset_type, local, temporary, create_time, access_time, asset_flags, creator_id)" + - "VALUES(?id, ?hash, ?name, ?description, ?asset_type, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?creator_id)", + "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)", dbcon)) { - // create unix epoch time - int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); - cmd.Parameters.AddWithValue("?id", asset.ID); - cmd.Parameters.AddWithValue("?hash", hash); - cmd.Parameters.AddWithValue("?name", assetName); - cmd.Parameters.AddWithValue("?description", assetDescription); - cmd.Parameters.AddWithValue("?asset_type", asset.Type); - cmd.Parameters.AddWithValue("?local", asset.Local); - cmd.Parameters.AddWithValue("?temporary", asset.Temporary); - cmd.Parameters.AddWithValue("?create_time", now); - cmd.Parameters.AddWithValue("?access_time", now); - cmd.Parameters.AddWithValue("?creator_id", asset.Metadata.CreatorID); - cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags); + cmd.Parameters.AddWithValue("?Hash", hash); + cmd.Parameters.AddWithValue("?Data", asset.Data); cmd.ExecuteNonQuery(); } } catch (Exception e) { - m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}", + m_log.ErrorFormat("[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}", asset.FullID, asset.Name, e.Message); transaction.Rollback(); return; } - - if (!ExistsData(dbcon, transaction, hash)) - { - try - { - using (MySqlCommand cmd = - new MySqlCommand( - "INSERT INTO xassetsdata(hash, data) VALUES(?hash, ?data)", - dbcon)) - { - cmd.Parameters.AddWithValue("?hash", hash); - cmd.Parameters.AddWithValue("?data", asset.Data); - cmd.ExecuteNonQuery(); - } - } - catch (Exception e) - { - m_log.ErrorFormat("[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}", - asset.FullID, asset.Name, e.Message); - - transaction.Rollback(); - - return; - } - } - - transaction.Commit(); } + + transaction.Commit(); } } } -// private void UpdateAccessTime(AssetBase asset) -// { -// lock (m_dbLock) -// { -// using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) -// { -// dbcon.Open(); -// MySqlCommand cmd = -// new MySqlCommand("update assets set access_time=?access_time where id=?id", -// dbcon); -// -// // need to ensure we dispose -// try -// { -// using (cmd) -// { -// // create unix epoch time -// int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); -// cmd.Parameters.AddWithValue("?id", asset.ID); -// cmd.Parameters.AddWithValue("?access_time", now); -// cmd.ExecuteNonQuery(); -// cmd.Dispose(); -// } -// } -// catch (Exception e) -// { -// m_log.ErrorFormat( -// "[ASSETS DB]: " + -// "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() -// + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); -// } -// } -// } -// -// } + /// + /// Updates the access time of the asset if it was accessed above a given threshhold amount of time. + /// + /// + /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done + /// over the threshold time to avoid excessive database writes as assets are fetched. + /// + /// + /// + private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime) + { + DateTime now = DateTime.UtcNow; + + if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates) + return; + + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + MySqlCommand cmd = + new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon); + + try + { + using (cmd) + { + // create unix epoch time + cmd.Parameters.AddWithValue("?ID", assetMetadata.ID); + cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now)); + cmd.ExecuteNonQuery(); + } + } + catch (Exception) + { + m_log.ErrorFormat( + "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}", + assetMetadata.ID, assetMetadata.Name); + } + } + } /// /// We assume we already have the m_dbLock. @@ -349,9 +361,9 @@ namespace OpenSim.Data.MySQL bool exists = false; - using (MySqlCommand cmd = new MySqlCommand("SELECT hash FROM xassetsdata WHERE hash=?hash", dbcon)) + using (MySqlCommand cmd = new MySqlCommand("SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon)) { - cmd.Parameters.AddWithValue("?hash", hash); + cmd.Parameters.AddWithValue("?Hash", hash); try { @@ -376,48 +388,43 @@ namespace OpenSim.Data.MySQL } /// - /// Check if the asset exists in the database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// true if it exists, false otherwise. - public bool ExistsAsset(UUID uuid) + /// The asset UUID's + /// For each asset: true if it exists, false otherwise + public bool[] AssetsExist(UUID[] uuids) { -// m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid); + if (uuids.Length == 0) + return new bool[0]; + + HashSet exists = new HashSet(); - bool assetExists = false; + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT ID FROM assets WHERE ID IN ({0})", ids); - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + dbcon.Open(); + using (MySqlCommand cmd = new MySqlCommand(sql, dbcon)) { - dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM xassetsmeta WHERE id=?id", dbcon)) + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - cmd.Parameters.AddWithValue("?id", uuid.ToString()); - - try - { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) - { - if (dbReader.Read()) - { -// m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid); - assetExists = true; - } - } - } - catch (Exception e) + while (dbReader.Read()) { - m_log.ErrorFormat( - "[XASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid); + UUID id = DBGuid.FromDB(dbReader["ID"]); + exists.Add(id); } } } } - return assetExists; + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exists.Contains(uuids[i]); + return results; } + /// /// Returns a list of AssetMetadata objects. The list is a subset of /// the entire data set offset by containing @@ -430,41 +437,40 @@ namespace OpenSim.Data.MySQL { List retList = new List(count); - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - MySqlCommand cmd = new MySqlCommand("SELECT name,description,asset_type,temporary,id,asset_flags,creator_id FROM xassetsmeta LIMIT ?start, ?count", dbcon); - cmd.Parameters.AddWithValue("?start", start); - cmd.Parameters.AddWithValue("?count", count); + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon); + cmd.Parameters.AddWithValue("?start", start); + cmd.Parameters.AddWithValue("?count", count); - try + try + { + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - using (MySqlDataReader dbReader = cmd.ExecuteReader()) + while (dbReader.Read()) { - while (dbReader.Read()) - { - AssetMetadata metadata = new AssetMetadata(); - metadata.Name = (string)dbReader["name"]; - metadata.Description = (string)dbReader["description"]; - metadata.Type = (sbyte)dbReader["asset_type"]; - metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. - metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); - metadata.FullID = DBGuid.FromDB(dbReader["id"]); - metadata.CreatorID = dbReader["creator_id"].ToString(); - - // We'll ignore this for now - it appears unused! + AssetMetadata metadata = new AssetMetadata(); + metadata.Name = (string)dbReader["Name"]; + metadata.Description = (string)dbReader["Description"]; + metadata.Type = (sbyte)dbReader["AssetType"]; + metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct. + metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); + metadata.FullID = DBGuid.FromDB(dbReader["ID"]); + metadata.CreatorID = dbReader["CreatorID"].ToString(); + + // We'll ignore this for now - it appears unused! // metadata.SHA1 = dbReader["hash"]); - retList.Add(metadata); - } + UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); + + retList.Add(metadata); } } - catch (Exception e) - { - m_log.Error("[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString()); - } + } + catch (Exception e) + { + m_log.Error("[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString()); } } @@ -475,21 +481,18 @@ namespace OpenSim.Data.MySQL { // m_log.DebugFormat("[XASSETS DB]: Deleting asset {0}", id); - lock (m_dbLock) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - - using (MySqlCommand cmd = new MySqlCommand("delete from xassetsmeta where id=?id", dbcon)) - { - cmd.Parameters.AddWithValue("?id", id); - cmd.ExecuteNonQuery(); - } + dbcon.Open(); - // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we - // keep a reference count (?) + using (MySqlCommand cmd = new MySqlCommand("delete from XAssetsMeta where ID=?ID", dbcon)) + { + cmd.Parameters.AddWithValue("?ID", id); + cmd.ExecuteNonQuery(); } + + // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we + // keep a reference count (?) } return true; diff --git a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs index ab3fe36..b46d175 100644 --- a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs +++ b/OpenSim/Data/MySQL/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/MySQL/Resources/AgentPrefs.migrations b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations new file mode 100644 index 0000000..e496f72 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations @@ -0,0 +1,18 @@ +:VERSION 1 # ------------------------- + +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 KEY `PrincipalID` (`PrincipalID`), + PRIMARY KEY(`PrincipalID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/AssetStore.migrations b/OpenSim/Data/MySQL/Resources/AssetStore.migrations index f7211c2..661d825 100644 --- a/OpenSim/Data/MySQL/Resources/AssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AssetStore.migrations @@ -13,7 +13,7 @@ CREATE TABLE `assets` ( `temporary` tinyint(1) NOT NULL, `data` longblob NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; COMMIT; @@ -75,3 +75,7 @@ ALTER TABLE assets ADD COLUMN asset_flags INTEGER NOT NULL DEFAULT 0; ALTER TABLE assets ADD COLUMN CreatorID varchar(128) NOT NULL DEFAULT ''; +:VERSION 9 + +BEGIN; +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/AuthStore.migrations b/OpenSim/Data/MySQL/Resources/AuthStore.migrations index 9450940..023c786 100644 --- a/OpenSim/Data/MySQL/Resources/AuthStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AuthStore.migrations @@ -8,7 +8,7 @@ CREATE TABLE `auth` ( `passwordSalt` char(32) NOT NULL default '', `webLoginKey` varchar(255) NOT NULL default '', PRIMARY KEY (`UUID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `tokens` ( `UUID` char(36) NOT NULL, @@ -18,7 +18,7 @@ CREATE TABLE `tokens` ( KEY `UUID` (`UUID`), KEY `token` (`token`), KEY `validity` (`validity`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; commit; diff --git a/OpenSim/Data/MySQL/Resources/EstateStore.migrations b/OpenSim/Data/MySQL/Resources/EstateStore.migrations index 6ef92ee..2d1c2b5 100644 --- a/OpenSim/Data/MySQL/Resources/EstateStore.migrations +++ b/OpenSim/Data/MySQL/Resources/EstateStore.migrations @@ -10,19 +10,19 @@ CREATE TABLE IF NOT EXISTS `estate_managers` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `estate_groups` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `estate_users` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `estateban` ( `EstateID` int(10) unsigned NOT NULL, @@ -31,7 +31,7 @@ CREATE TABLE IF NOT EXISTS `estateban` ( `bannedIpHostMask` varchar(16) NOT NULL, `bannedNameMask` varchar(64) default NULL, KEY `estateban_EstateID` (`EstateID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `estate_settings` ( `EstateID` int(10) unsigned NOT NULL auto_increment, @@ -60,14 +60,14 @@ CREATE TABLE IF NOT EXISTS `estate_settings` ( `DenyMinors` tinyint not null, PRIMARY KEY (`EstateID`) -) ENGINE=MyISAM AUTO_INCREMENT=100; +) ENGINE=InnoDB AUTO_INCREMENT=100; CREATE TABLE IF NOT EXISTS `estate_map` ( `RegionID` char(36) NOT NULL default '00000000-0000-0000-0000-000000000000', `EstateID` int(11) NOT NULL, PRIMARY KEY (`RegionID`), KEY `EstateID` (`EstateID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; COMMIT; @@ -77,5 +77,11 @@ BEGIN; ALTER TABLE estate_settings AUTO_INCREMENT = 100; COMMIT; +:VERSION 33 #--------------------- +BEGIN; +ALTER TABLE estate_settings ADD COLUMN `AllowLandmark` tinyint(4) NOT NULL default '1'; +ALTER TABLE estate_settings ADD COLUMN `AllowParcelChanges` tinyint(4) NOT NULL default '1'; +ALTER TABLE estate_settings ADD COLUMN `AllowSetHome` tinyint(4) NOT NULL default '1'; +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations b/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations new file mode 100644 index 0000000..87d08c6 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations @@ -0,0 +1,18 @@ +# ----------------- +:VERSION 1 + +BEGIN; + +CREATE TABLE `fsassets` ( + `id` char(36) NOT NULL, + `name` varchar(64) NOT NULL DEFAULT '', + `description` varchar(64) NOT NULL DEFAULT '', + `type` int(11) NOT NULL, + `hash` char(80) NOT NULL, + `create_time` int(11) NOT NULL DEFAULT '0', + `access_time` int(11) NOT NULL DEFAULT '0', + `asset_flags` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +COMMIT; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations index 55d82ec..5faf956 100644 --- a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations +++ b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations @@ -9,7 +9,7 @@ CREATE TABLE `Friends` ( `Offered` VARCHAR(32) NOT NULL DEFAULT 0, PRIMARY KEY(`PrincipalID`, `Friend`), KEY(`PrincipalID`) -); +) ENGINE=InnoDB; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/GridStore.migrations b/OpenSim/Data/MySQL/Resources/GridStore.migrations index b010c63..b8d287a 100644 --- a/OpenSim/Data/MySQL/Resources/GridStore.migrations +++ b/OpenSim/Data/MySQL/Resources/GridStore.migrations @@ -1,5 +1,7 @@ :VERSION 1 +BEGIN; + CREATE TABLE `regions` ( `uuid` varchar(36) NOT NULL, `regionHandle` bigint(20) unsigned NOT NULL, @@ -31,7 +33,9 @@ CREATE TABLE `regions` ( KEY `regionName` (`regionName`), KEY `regionHandle` (`regionHandle`), KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +COMMIT; :VERSION 2 diff --git a/OpenSim/Data/MySQL/Resources/GridUserStore.migrations b/OpenSim/Data/MySQL/Resources/GridUserStore.migrations index e2be27e..d08e096 100644 --- a/OpenSim/Data/MySQL/Resources/GridUserStore.migrations +++ b/OpenSim/Data/MySQL/Resources/GridUserStore.migrations @@ -14,7 +14,7 @@ CREATE TABLE `GridUser` ( `Login` CHAR(16) NOT NULL DEFAULT '0', `Logout` CHAR(16) NOT NULL DEFAULT '0', PRIMARY KEY (`UserID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations new file mode 100644 index 0000000..b4e4422 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations @@ -0,0 +1,18 @@ +:VERSION 1 # -------------------------- + +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 NOT NULL, + PRIMARY KEY (`SessionID`), + KEY (`UserID`) +) ENGINE=InnoDB; + +COMMIT; + diff --git a/OpenSim/Data/MySQL/Resources/IM_Store.migrations b/OpenSim/Data/MySQL/Resources/IM_Store.migrations new file mode 100644 index 0000000..79ead98 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/IM_Store.migrations @@ -0,0 +1,42 @@ +:VERSION 1 # -------------------------- + +BEGIN; + +CREATE TABLE `im_offline` ( + `ID` MEDIUMINT NOT NULL AUTO_INCREMENT, + `PrincipalID` char(36) NOT NULL default '', + `Message` text NOT NULL, + `TMStamp` timestamp NOT NULL, + PRIMARY KEY (`ID`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=MyISAM; + +COMMIT; + +:VERSION 2 # -------------------------- + +BEGIN; + +INSERT INTO `im_offline` SELECT * from `diva_im_offline`; +DROP TABLE `diva_im_offline`; +DELETE FROM `migrations` WHERE name='diva_im_Store'; + +COMMIT; + +:VERSION 3 # -------------------------- + +BEGIN; + +ALTER TABLE `im_offline` + ADD `FromID` char(36) NOT NULL default '' AFTER `PrincipalID`, + ADD KEY `FromID` (`FromID`); + +COMMIT; + +:VERSION 4 # -------------------------- + +BEGIN; + +ALTER TABLE im_offline CONVERT TO CHARACTER SET utf8; + +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations index ca2fe11..993a5a0 100644 --- a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations +++ b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations @@ -11,7 +11,7 @@ CREATE TABLE `inventoryfolders` ( PRIMARY KEY (`folderID`), KEY `owner` (`agentID`), KEY `parent` (`parentFolderID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `inventoryitems` ( `inventoryID` varchar(36) NOT NULL default '', @@ -36,7 +36,7 @@ CREATE TABLE `inventoryitems` ( PRIMARY KEY (`inventoryID`), KEY `owner` (`avatarID`), KEY `folder` (`parentFolderID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/LogStore.migrations b/OpenSim/Data/MySQL/Resources/LogStore.migrations index b572411..9ac26ac 100644 --- a/OpenSim/Data/MySQL/Resources/LogStore.migrations +++ b/OpenSim/Data/MySQL/Resources/LogStore.migrations @@ -10,4 +10,4 @@ CREATE TABLE `logs` ( `priority` int(11) default NULL, `message` text, PRIMARY KEY (`logID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/OpenSim/Data/MySQL/Resources/Presence.migrations b/OpenSim/Data/MySQL/Resources/Presence.migrations index 5dc96d2..c4e40fa 100644 --- a/OpenSim/Data/MySQL/Resources/Presence.migrations +++ b/OpenSim/Data/MySQL/Resources/Presence.migrations @@ -1,4 +1,4 @@ -:VERSION 1 # -------------------------- +:VERSION 1 # -------------------------- BEGIN; @@ -7,7 +7,7 @@ CREATE TABLE `Presence` ( `RegionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `SessionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `SecureSessionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE UNIQUE INDEX SessionID ON Presence(SessionID); CREATE INDEX UserID ON Presence(UserID); @@ -21,3 +21,11 @@ BEGIN; ALTER TABLE `Presence` ADD COLUMN LastSeen timestamp; COMMIT; + +:VERSION 3 # -------------------------- + +BEGIN; + +CREATE INDEX RegionID ON Presence(RegionID); + +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/RegionStore.migrations b/OpenSim/Data/MySQL/Resources/RegionStore.migrations index 071f64e..ac31380 100644 --- a/OpenSim/Data/MySQL/Resources/RegionStore.migrations +++ b/OpenSim/Data/MySQL/Resources/RegionStore.migrations @@ -146,7 +146,7 @@ CREATE TABLE `land` ( `UserLookAtZ` float default NULL, `AuthbuyerID` varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000', PRIMARY KEY (`UUID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `landaccesslist` ( `LandUUID` varchar(255) default NULL, @@ -168,7 +168,7 @@ COMMIT; :VERSION 3 #--------------------- BEGIN; - CREATE TABLE regionban (regionUUID VARCHAR(36) NOT NULL, bannedUUID VARCHAR(36) NOT NULL, bannedIp VARCHAR(16) NOT NULL, bannedIpHostMask VARCHAR(16) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + CREATE TABLE regionban (regionUUID VARCHAR(36) NOT NULL, bannedUUID VARCHAR(36) NOT NULL, bannedIp VARCHAR(16) NOT NULL, bannedIpHostMask VARCHAR(16) NOT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; COMMIT; :VERSION 4 #--------------------- @@ -226,13 +226,13 @@ COMMIT; BEGIN; -alter table landaccesslist ENGINE = MyISAM; -alter table migrations ENGINE = MyISAM; -alter table primitems ENGINE = MyISAM; -alter table prims ENGINE = MyISAM; -alter table primshapes ENGINE = MyISAM; -alter table regionsettings ENGINE = MyISAM; -alter table terrain ENGINE = MyISAM; +alter table landaccesslist ENGINE = InnoDB; +alter table migrations ENGINE = InnoDB; +alter table primitems ENGINE = InnoDB; +alter table prims ENGINE = InnoDB; +alter table primshapes ENGINE = InnoDB; +alter table regionsettings ENGINE = InnoDB; +alter table terrain ENGINE = InnoDB; COMMIT; @@ -384,7 +384,7 @@ CREATE TABLE `regionsettings` ( `covenant` char(36) default NULL, `Sandbox` tinyint(4) NOT NULL, PRIMARY KEY (`regionUUID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; commit; @@ -852,7 +852,7 @@ CREATE TABLE IF NOT EXISTS `spawn_points` ( `Pitch` float NOT NULL, `Distance` float NOT NULL, KEY `RegionID` (`RegionID`) -) ENGINE=MyISAM; +) ENGINE=Innodb; ALTER TABLE `regionsettings` ADD COLUMN `TelehubObject` varchar(36) NOT NULL; COMMIT; @@ -891,7 +891,7 @@ CREATE TABLE `regionenvironment` ( `region_id` varchar(36) NOT NULL, `llsd_settings` TEXT NOT NULL, PRIMARY KEY (`region_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; @@ -902,3 +902,49 @@ BEGIN; CREATE TABLE `regionextra` (`RegionID` char(36) not null, `Name` varchar(32) not null, `value` text, primary key(`RegionID`, `Name`)); COMMIT; + +:VERSION 46 #---------------- Dynamic attributes + +BEGIN; + +ALTER TABLE prims ADD COLUMN DynAttrs TEXT; + +COMMIT; + +:VERSION 47 #---------------- Extra physics params + +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 48 #---------------- Keyframes + +BEGIN; + +ALTER TABLE prims ADD COLUMN `KeyframeMotion` blob; + +COMMIT; + +:VERSION 49 #--------------------- 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(4) not null default '0'; +COMMIT; + +:VERSION 50 #---- Change LandFlags to unsigned + +BEGIN; + +ALTER TABLE land CHANGE COLUMN LandFlags LandFlags int unsigned default null; + +COMMIT; + diff --git a/OpenSim/Data/MySQL/Resources/UserAccount.migrations b/OpenSim/Data/MySQL/Resources/UserAccount.migrations index 97e5e4f..84011e6 100644 --- a/OpenSim/Data/MySQL/Resources/UserAccount.migrations +++ b/OpenSim/Data/MySQL/Resources/UserAccount.migrations @@ -10,7 +10,7 @@ CREATE TABLE `UserAccounts` ( `Email` VARCHAR(64), `ServiceURLs` TEXT, `Created` INT(11) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations new file mode 100644 index 0000000..87e99fa --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations @@ -0,0 +1,98 @@ +:VERSION 1 # ------------------------------- + +begin; + +CREATE TABLE IF NOT EXISTS `classifieds` ( + `classifieduuid` char(36) NOT NULL, + `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, + PRIMARY KEY (`classifieduuid`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE IF NOT EXISTS `usernotes` ( + `useruuid` varchar(36) NOT NULL, + `targetuuid` varchar(36) NOT NULL, + `notes` text NOT NULL, + UNIQUE KEY `useruuid` (`useruuid`,`targetuuid`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +CREATE TABLE IF NOT EXISTS `userpicks` ( + `pickuuid` varchar(36) NOT NULL, + `creatoruuid` varchar(36) NOT NULL, + `toppick` enum('true','false') 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` enum('true','false') NOT NULL, + PRIMARY KEY (`pickuuid`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +CREATE TABLE IF NOT EXISTS `userprofile` ( + `useruuid` varchar(36) NOT NULL, + `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, + PRIMARY KEY (`useruuid`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +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`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +commit; + +:VERSION 3 # ------------------------------- +begin; +CREATE TABLE IF NOT EXISTS `usersettings` ( + `useruuid` varchar(36) NOT NULL, + `imviaemail` enum('true','false') NOT NULL, + `visible` enum('true','false') NOT NULL, + `email` varchar(254) NOT NULL, + PRIMARY KEY (`useruuid`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +commit; + +:VERSION 4 # ------------------------------- +begin; +ALTER TABLE userpicks ADD COLUMN gatekeeper varchar(255); +commit; diff --git a/OpenSim/Data/MySQL/Resources/UserStore.migrations b/OpenSim/Data/MySQL/Resources/UserStore.migrations index 9129075..f054611 100644 --- a/OpenSim/Data/MySQL/Resources/UserStore.migrations +++ b/OpenSim/Data/MySQL/Resources/UserStore.migrations @@ -21,7 +21,7 @@ CREATE TABLE `agents` ( PRIMARY KEY (`UUID`), UNIQUE KEY `session` (`sessionID`), UNIQUE KEY `ssession` (`secureSessionID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Create schema avatar_appearance -- @@ -59,7 +59,7 @@ CREATE TABLE `avatarappearance` ( Skirt_Item char(36) NOT NULL, Skirt_Asset char(36) NOT NULL, PRIMARY KEY (`Owner`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- @@ -71,7 +71,7 @@ CREATE TABLE `userfriends` ( `friendPerms` INT NOT NULL, `datetimestamp` INT NOT NULL, UNIQUE KEY (`ownerID`, `friendID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for users -- ---------------------------- @@ -101,7 +101,7 @@ CREATE TABLE `users` ( `webLoginKey` varchar(36) default NULL, PRIMARY KEY (`UUID`), UNIQUE KEY `usernames` (`username`,`lastname`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records @@ -138,7 +138,7 @@ COMMIT; BEGIN; -CREATE TABLE `avatarattachments` (`UUID` char(36) NOT NULL, `attachpoint` int(11) NOT NULL, `item` char(36) NOT NULL, `asset` char(36) NOT NULL) ENGINE=MyISAM; +CREATE TABLE `avatarattachments` (`UUID` char(36) NOT NULL, `attachpoint` int(11) NOT NULL, `item` char(36) NOT NULL, `asset` char(36) NOT NULL) ENGINE=InnoDB; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations index bb58c39..9459e3e 100644 --- a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations @@ -3,25 +3,30 @@ BEGIN; -CREATE TABLE `xassetsmeta` ( - `id` char(36) NOT NULL, - `hash` binary(32) NOT NULL, - `name` varchar(64) NOT NULL, - `description` varchar(64) NOT NULL, - `asset_type` tinyint(4) NOT NULL, - `local` tinyint(1) NOT NULL, - `temporary` tinyint(1) NOT NULL, - `create_time` int(11) NOT NULL, - `access_time` int(11) NOT NULL, - `asset_flags` int(11) NOT NULL, - `creator_id` varchar(128) NOT NULL, +CREATE TABLE `XAssetsMeta` ( + `ID` char(36) NOT NULL, + `Hash` binary(32) NOT NULL, + `Name` varchar(64) NOT NULL, + `Description` varchar(64) NOT NULL, + `AssetType` tinyint(4) NOT NULL, + `Local` tinyint(1) NOT NULL, + `Temporary` tinyint(1) NOT NULL, + `CreateTime` int(11) NOT NULL, + `AccessTime` int(11) NOT NULL, + `AssetFlags` int(11) NOT NULL, + `CreatorID` varchar(128) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Version 1'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; -CREATE TABLE `xassetsdata` ( - `hash` binary(32) NOT NULL, - `data` longblob NOT NULL, +CREATE TABLE `XAssetsData` ( + `Hash` binary(32) NOT NULL, + `Data` longblob NOT NULL, PRIMARY KEY (`hash`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Version 1'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; -COMMIT; \ No newline at end of file +COMMIT; + +:VERSION 2 + +BEGIN; +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations new file mode 100644 index 0000000..9e6f1c1 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations @@ -0,0 +1,115 @@ +:VERSION 1 # -------------------------- + +BEGIN; + +CREATE TABLE `os_groups_groups` ( + `GroupID` char(36) NOT NULL default '', + `Location` varchar(255) NOT NULL default '', + `Name` varchar(255) NOT NULL default '', + `Charter` text NOT NULL, + `InsigniaID` char(36) NOT NULL default '', + `FounderID` char(36) NOT NULL default '', + `MembershipFee` int(11) NOT NULL default '0', + `OpenEnrollment` varchar(255) NOT NULL default '', + `ShowInList` int(4) NOT NULL default '0', + `AllowPublish` int(4) NOT NULL default '0', + `MaturePublish` int(4) NOT NULL default '0', + `OwnerRoleID` char(36) NOT NULL default '', + PRIMARY KEY (`GroupID`), + UNIQUE KEY `Name` (`Name`), + FULLTEXT KEY `Name_2` (`Name`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_membership` ( + `GroupID`char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + `SelectedRoleID` char(36) NOT NULL default '', + `Contribution` int(11) NOT NULL default '0', + `ListInProfile` int(4) NOT NULL default '1', + `AcceptNotices` int(4) NOT NULL default '1', + `AccessToken` char(36) NOT NULL default '', + PRIMARY KEY (`GroupID`,`PrincipalID`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_roles` ( + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `Name` varchar(255) NOT NULL default '', + `Description` varchar(255) NOT NULL default '', + `Title` varchar(255) NOT NULL default '', + `Powers` bigint(20) unsigned NOT NULL default '0', + PRIMARY KEY (`GroupID`,`RoleID`), + KEY `GroupID` (`GroupID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_rolemembership` ( + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + PRIMARY KEY (`GroupID`,`RoleID`,`PrincipalID`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_invites` ( + `InviteID` char(36) NOT NULL default '', + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + `TMStamp` timestamp NOT NULL, + PRIMARY KEY (`InviteID`), + UNIQUE KEY `PrincipalGroup` (`GroupID`,`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_notices` ( + `GroupID` char(36) NOT NULL default '', + `NoticeID` char(36) NOT NULL default '', + `TMStamp` int(10) unsigned NOT NULL default '0', + `FromName` varchar(255) NOT NULL default '', + `Subject` varchar(255) NOT NULL default '', + `Message` text NOT NULL, + `HasAttachment` int(4) NOT NULL default '0', + `AttachmentType` int(4) NOT NULL default '0', + `AttachmentName` varchar(128) NOT NULL default '', + `AttachmentItemID` char(36) NOT NULL default '', + `AttachmentOwnerID` varchar(255) NOT NULL default '', + PRIMARY KEY (`NoticeID`), + KEY `GroupID` (`GroupID`), + KEY `TMStamp` (`TMStamp`) +) ENGINE=MyISAM; + +CREATE TABLE `os_groups_principals` ( + `PrincipalID` VARCHAR(255) NOT NULL default '', + `ActiveGroupID` char(36) NOT NULL default '', + PRIMARY KEY (`PrincipalID`) +) ENGINE=MyISAM; + +COMMIT; + +:VERSION 2 # -------------------------- + +BEGIN; + +INSERT INTO `os_groups_groups` SELECT * from `diva_groups_groups`; +DROP TABLE `diva_groups_groups`; +INSERT INTO `os_groups_membership` SELECT * from `diva_groups_membership`; +DROP TABLE `diva_groups_membership`; +INSERT INTO `os_groups_roles` SELECT * from `diva_groups_roles`; +DROP TABLE `diva_groups_roles`; +INSERT INTO `os_groups_rolemembership` SELECT * from `diva_groups_rolemembership`; +DROP TABLE `diva_groups_rolemembership`; +INSERT INTO `os_groups_invites` SELECT * from `diva_groups_invites`; +DROP TABLE `diva_groups_invites`; +INSERT INTO `os_groups_notices` SELECT * from `diva_groups_notices`; +DROP TABLE `diva_groups_notices`; +INSERT INTO `os_groups_principals` SELECT * from `diva_groups_principals`; +DROP TABLE `diva_groups_principals`; + +DELETE FROM `migrations` WHERE name='diva_im_Store'; + +COMMIT; \ No newline at end of file -- cgit v1.1