From 5e4d6cab00cb29cd088ab7b62ab13aff103b64cb Mon Sep 17 00:00:00 2001 From: onefang Date: Sun, 19 May 2019 21:24:15 +1000 Subject: Dump OpenSim 0.9.0.1 into it's own branch. --- OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs | 2 +- OpenSim/Data/MySQL/MySQLAssetData.cs | 112 +- OpenSim/Data/MySQL/MySQLAuthenticationData.cs | 58 +- OpenSim/Data/MySQL/MySQLAvatarData.cs | 2 +- OpenSim/Data/MySQL/MySQLEstateData.cs | 26 +- OpenSim/Data/MySQL/MySQLFSAssetData.cs | 397 +++---- OpenSim/Data/MySQL/MySQLFramework.cs | 60 +- OpenSim/Data/MySQL/MySQLGenericTableHandler.cs | 184 +-- OpenSim/Data/MySQL/MySQLGroupsData.cs | 23 +- OpenSim/Data/MySQL/MySQLInventoryData.cs | 26 +- OpenSim/Data/MySQL/MySQLMigrations.cs | 6 +- OpenSim/Data/MySQL/MySQLMuteListData.cs | 67 ++ OpenSim/Data/MySQL/MySQLOfflineIMData.cs | 2 +- OpenSim/Data/MySQL/MySQLPresenceData.cs | 8 +- OpenSim/Data/MySQL/MySQLRegionData.cs | 78 +- OpenSim/Data/MySQL/MySQLSimulationData.cs | 710 ++++++++---- OpenSim/Data/MySQL/MySQLUserAccountData.cs | 40 +- OpenSim/Data/MySQL/MySQLUserProfilesData.cs | 588 +++++----- OpenSim/Data/MySQL/MySQLXAssetData.cs | 113 +- OpenSim/Data/MySQL/MySQLXInventoryData.cs | 16 +- OpenSim/Data/MySQL/Properties/AssemblyInfo.cs | 2 +- OpenSim/Data/MySQL/Resources/AgentPrefs.migrations | 2 +- OpenSim/Data/MySQL/Resources/AssetStore.migrations | 76 +- OpenSim/Data/MySQL/Resources/AuthStore.migrations | 37 +- OpenSim/Data/MySQL/Resources/Avatar.migrations | 23 +- .../Data/MySQL/Resources/EstateStore.migrations | 80 +- .../Data/MySQL/Resources/FSAssetStore.migrations | 2 +- .../Data/MySQL/Resources/FriendsStore.migrations | 36 +- OpenSim/Data/MySQL/Resources/GridStore.migrations | 141 +-- .../Data/MySQL/Resources/GridUserStore.migrations | 2 +- .../Data/MySQL/Resources/HGTravelStore.migrations | 2 +- OpenSim/Data/MySQL/Resources/IM_Store.migrations | 46 +- .../Data/MySQL/Resources/InventoryStore.migrations | 143 +-- OpenSim/Data/MySQL/Resources/LogStore.migrations | 2 +- .../Data/MySQL/Resources/MuteListStore.migrations | 16 + OpenSim/Data/MySQL/Resources/Presence.migrations | 37 +- .../Data/MySQL/Resources/RegionStore.migrations | 1183 ++++++-------------- .../Data/MySQL/Resources/UserAccount.migrations | 56 +- .../Data/MySQL/Resources/UserProfiles.migrations | 28 +- OpenSim/Data/MySQL/Resources/UserStore.migrations | 168 --- .../Data/MySQL/Resources/XAssetStore.migrations | 4 +- OpenSim/Data/MySQL/Resources/XMute.migrations | 16 + .../MySQL/Resources/os_groups_Store.migrations | 14 +- 43 files changed, 2114 insertions(+), 2520 deletions(-) create mode 100644 OpenSim/Data/MySQL/MySQLMuteListData.cs create mode 100644 OpenSim/Data/MySQL/Resources/MuteListStore.migrations delete mode 100644 OpenSim/Data/MySQL/Resources/UserStore.migrations create mode 100644 OpenSim/Data/MySQL/Resources/XMute.migrations (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs b/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs index ed0ab98..17f1374 100644 --- a/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs +++ b/OpenSim/Data/MySQL/MySQLAgentPreferencesData.cs @@ -1,4 +1,4 @@ -/* +/* * Copyright (c) Contributors, http://opensimulator.org/ * See CONTRIBUTORS.TXT for a full list of copyright holders. * diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs index 5d8da17..8569c90 100644 --- a/OpenSim/Data/MySQL/MySQLAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -75,6 +75,7 @@ namespace OpenSim.Data.MySQL dbcon.Open(); Migration m = new Migration(dbcon, Assembly, "AssetStore"); m.Update(); + dbcon.Close(); } } @@ -144,6 +145,7 @@ namespace OpenSim.Data.MySQL string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", assetID), e); } } + dbcon.Close(); } return asset; @@ -154,65 +156,62 @@ namespace OpenSim.Data.MySQL /// /// Asset UUID to create /// On failure : Throw an exception and attempt to reconnect to database - override public void StoreAsset(AssetBase asset) + override public bool StoreAsset(AssetBase asset) { + string assetName = asset.Name; + if (asset.Name.Length > AssetBase.MAX_ASSET_NAME) + { + assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME); + m_log.WarnFormat( + "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Name, asset.ID, asset.Name.Length, assetName.Length); + } + + string assetDescription = asset.Description; + if (asset.Description.Length > AssetBase.MAX_ASSET_DESC) + { + assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC); + m_log.WarnFormat( + "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", + asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); + } + 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)) { - string assetName = asset.Name; - if (asset.Name.Length > AssetBase.MAX_ASSET_NAME) - { - assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME); - m_log.WarnFormat( - "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", - asset.Name, asset.ID, asset.Name.Length, assetName.Length); - } - - string assetDescription = asset.Description; - if (asset.Description.Length > AssetBase.MAX_ASSET_DESC) - { - assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC); - m_log.WarnFormat( - "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", - asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); - } - 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(); - } + // 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(); + dbcon.Close(); + return true; } catch (Exception e) { - m_log.Error( - string.Format( - "[ASSET DB]: MySQL failure creating asset {0} with name {1}. Exception ", - asset.FullID, asset.Name) - , e); + m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}", + asset.FullID, asset.Name, e.Message); + dbcon.Close(); + return false; } - } + } } } @@ -227,24 +226,22 @@ namespace OpenSim.Data.MySQL { 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(); - } + // 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), + "[ASSETS DB]: Failure updating access_time for asset {0} with name {1}. Exception ", + asset.FullID, asset.Name), e); } } + dbcon.Close(); } } @@ -277,6 +274,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } bool[] results = new bool[uuids.Length]; @@ -336,11 +334,12 @@ namespace OpenSim.Data.MySQL { m_log.Error( string.Format( - "[ASSETS DB]: MySql failure fetching asset set from {0}, count {1}. Exception ", - start, count), + "[ASSETS DB]: MySql failure fetching asset set from {0}, count {1}. Exception ", + start, count), e); } } + dbcon.Close(); } return retList; @@ -357,6 +356,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?id", id); cmd.ExecuteNonQuery(); } + dbcon.Close(); } return true; @@ -364,4 +364,4 @@ namespace OpenSim.Data.MySQL #endregion } -} \ No newline at end of file +} diff --git a/OpenSim/Data/MySQL/MySQLAuthenticationData.cs b/OpenSim/Data/MySQL/MySQLAuthenticationData.cs index 7627497..fef582e 100644 --- a/OpenSim/Data/MySQL/MySQLAuthenticationData.cs +++ b/OpenSim/Data/MySQL/MySQLAuthenticationData.cs @@ -59,6 +59,7 @@ namespace OpenSim.Data.MySQL dbcon.Open(); Migration m = new Migration(dbcon, Assembly, "AuthStore"); m.Update(); + dbcon.Close(); } } @@ -76,27 +77,30 @@ namespace OpenSim.Data.MySQL { cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); - IDataReader result = cmd.ExecuteReader(); - - if (result.Read()) + using(IDataReader result = cmd.ExecuteReader()) { - ret.PrincipalID = principalID; - - CheckColumnNames(result); - - foreach (string s in m_ColumnNames) + if(result.Read()) { - if (s == "UUID") - continue; - - ret.Data[s] = result[s].ToString(); + ret.PrincipalID = principalID; + + CheckColumnNames(result); + + foreach(string s in m_ColumnNames) + { + if(s == "UUID") + continue; + + ret.Data[s] = result[s].ToString(); + } + + dbcon.Close(); + return ret; + } + else + { + dbcon.Close(); + return null; } - - return ret; - } - else - { - return null; } } } @@ -132,25 +136,25 @@ namespace OpenSim.Data.MySQL if (!first) update += ", "; update += "`" + field + "` = ?"+field; - + first = false; - + cmd.Parameters.AddWithValue("?"+field, data.Data[field]); } - + update += " where UUID = ?principalID"; - + cmd.CommandText = update; cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString()); - + if (ExecuteNonQuery(cmd) < 1) { string insert = "insert into `" + m_Realm + "` (`UUID`, `" + String.Join("`, `", fields) + "`) values (?principalID, ?" + String.Join(", ?", fields) + ")"; - + cmd.CommandText = insert; - + if (ExecuteNonQuery(cmd) < 1) return false; } @@ -166,7 +170,7 @@ namespace OpenSim.Data.MySQL { cmd.Parameters.AddWithValue("?"+item, value); cmd.Parameters.AddWithValue("?UUID", principalID.ToString()); - + if (ExecuteNonQuery(cmd) > 0) return true; } @@ -186,7 +190,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); cmd.Parameters.AddWithValue("?token", token); cmd.Parameters.AddWithValue("?lifetime", lifetime.ToString()); - + if (ExecuteNonQuery(cmd) > 0) return true; } diff --git a/OpenSim/Data/MySQL/MySQLAvatarData.cs b/OpenSim/Data/MySQL/MySQLAvatarData.cs index 6a2f5d8..63e8020 100644 --- a/OpenSim/Data/MySQL/MySQLAvatarData.cs +++ b/OpenSim/Data/MySQL/MySQLAvatarData.cs @@ -57,7 +57,7 @@ namespace OpenSim.Data.MySQL cmd.CommandText = String.Format("delete from {0} where `PrincipalID` = ?PrincipalID and `Name` = ?Name", m_Realm); cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString()); cmd.Parameters.AddWithValue("?Name", name); - + if (ExecuteNonQuery(cmd) > 0) return true; } diff --git a/OpenSim/Data/MySQL/MySQLEstateData.cs b/OpenSim/Data/MySQL/MySQLEstateData.cs index fe1487b..eeedf02 100644 --- a/OpenSim/Data/MySQL/MySQLEstateData.cs +++ b/OpenSim/Data/MySQL/MySQLEstateData.cs @@ -82,6 +82,7 @@ namespace OpenSim.Data.MySQL Migration m = new Migration(dbcon, Assembly, "EstateStore"); m.Update(); + dbcon.Close(); Type t = typeof(EstateSettings); m_Fields = t.GetFields(BindingFlags.NonPublic | @@ -143,7 +144,6 @@ namespace OpenSim.Data.MySQL using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); - cmd.Connection = dbcon; bool found = false; @@ -171,6 +171,8 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); + cmd.Connection = null; if (!found && create) { @@ -231,6 +233,7 @@ namespace OpenSim.Data.MySQL es.Save(); } + dbcon.Close(); } } @@ -263,6 +266,7 @@ namespace OpenSim.Data.MySQL cmd.ExecuteNonQuery(); } + dbcon.Close(); } SaveBanList(es); @@ -300,6 +304,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } @@ -329,6 +334,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.Clear(); } } + dbcon.Close(); } } @@ -358,6 +364,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.Clear(); } } + dbcon.Close(); } } @@ -383,6 +390,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } return uuids.ToArray(); @@ -403,19 +411,19 @@ namespace OpenSim.Data.MySQL return e; } } - + public List LoadEstateSettingsAll() { - List allEstateSettings = new List(); - + List allEstateSettings = new List(); + List allEstateIds = GetEstatesAll(); - + foreach (int estateId in allEstateIds) allEstateSettings.Add(LoadEstateSettings(estateId)); - + return allEstateSettings; } - + public List GetEstatesAll() { List result = new List(); @@ -437,11 +445,10 @@ namespace OpenSim.Data.MySQL reader.Close(); } } - dbcon.Close(); } - return result; + return result; } public List GetEstates(string search) @@ -466,7 +473,6 @@ namespace OpenSim.Data.MySQL reader.Close(); } } - dbcon.Close(); } diff --git a/OpenSim/Data/MySQL/MySQLFSAssetData.cs b/OpenSim/Data/MySQL/MySQLFSAssetData.cs index 19e23b5..6c48607 100644 --- a/OpenSim/Data/MySQL/MySQLFSAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLFSAssetData.cs @@ -41,10 +41,8 @@ namespace OpenSim.Data.MySQL { 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 @@ -56,7 +54,7 @@ namespace OpenSim.Data.MySQL { get { return GetType().Assembly; } } - + public MySQLFSAssetData() { } @@ -75,10 +73,13 @@ namespace OpenSim.Data.MySQL try { - OpenDatabase(); - - Migration m = new Migration(m_Connection, Assembly, "FSAssetStore"); - m.Update(); + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, Assembly, "FSAssetStore"); + m.Update(); + conn.Close(); + } } catch (MySqlException e) { @@ -100,70 +101,37 @@ namespace OpenSim.Data.MySQL #endregion - private bool OpenDatabase() + private bool ExecuteNonQuery(MySqlCommand cmd) { - try - { - m_Connection = new MySqlConnection(m_ConnectionString); - - m_Connection.Open(); - } - catch (MySqlException e) + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) { - 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 { - 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; - } + conn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString()); + return false; + } - break; + cmd.Connection = conn; + try + { + cmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + cmd.Connection = null; + conn.Close(); + m_log.ErrorFormat("[FSASSETS]: Query {0} failed with {1}", cmd.CommandText, e.ToString()); + return false; } + conn.Close(); + cmd.Connection = null; } + + return true; } #region IFSAssetDataPlugin Members @@ -172,63 +140,79 @@ namespace OpenSim.Data.MySQL { 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(); + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) + { + try + { + conn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString()); + return null; + } - meta.ID = id; - meta.FullID = new UUID(id); + using (MySqlCommand cmd = conn.CreateCommand()) + { + cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, asset_flags, access_time from {0} where id = ?id", m_Table); + cmd.Parameters.AddWithValue("?id", 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"]); + using (IDataReader reader = cmd.ExecuteReader()) + { + if (!reader.Read()) + return null; - int AccessTime = Convert.ToInt32(reader["access_time"]); + hash = reader["hash"].ToString(); - reader.Close(); + meta.ID = id; + meta.FullID = new UUID(id); - UpdateAccessTime(AccessTime, cmd); + 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"]); - FreeCommand(cmd); + int AccessTime = Convert.ToInt32(reader["access_time"]); + UpdateAccessTime(id, AccessTime); + } + } + conn.Close(); + } return meta; } - private void UpdateAccessTime(int AccessTime, MySqlCommand cmd) + private void UpdateAccessTime(string AssetID, int AccessTime) { // 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(); - } + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) + { + try + { + conn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString()); + return; + } - protected void FreeCommand(MySqlCommand cmd) - { - MySqlConnection c = cmd.Connection; - cmd.Dispose(); - c.Close(); - c.Dispose(); + using (MySqlCommand cmd = conn.CreateCommand()) + { + cmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table); + cmd.Parameters.AddWithValue("?id", AssetID); + cmd.ExecuteNonQuery(); + } + conn.Close(); + } } public bool Store(AssetMetadata meta, string hash) @@ -238,37 +222,41 @@ namespace OpenSim.Data.MySQL 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) + using (MySqlCommand cmd = new MySqlCommand()) { - 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); + 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("?type", meta.Type); + 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); + ExecuteNonQuery(cmd); - cmd.Dispose(); + return true; + } - return true; - } + //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table); - //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table); + //ExecuteNonQuery(cmd); - //ExecuteNonQuery(cmd); + } - cmd.Dispose(); - return false; +// return false; + // if the asset already exits + // assume it was already correctly stored + // or regions will keep retry. + return true; } catch(Exception e) { m_log.Error("[FSAssets] Failed to store asset with ID " + meta.ID); - m_log.Error(e.ToString()); + m_log.Error(e.ToString()); return false; } } @@ -283,26 +271,43 @@ namespace OpenSim.Data.MySQL if (uuids.Length == 0) return new bool[0]; + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = false; + 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()) + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) { - cmd.CommandText = sql; + try + { + conn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString()); + return results; + } - using (MySqlDataReader dbReader = cmd.ExecuteReader()) + using (MySqlCommand cmd = conn.CreateCommand()) { - while (dbReader.Read()) + cmd.CommandText = sql; + + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - UUID id = DBGuid.FromDB(dbReader["ID"]); - exists.Add(id); + while (dbReader.Read()) + { + UUID id = DBGuid.FromDB(dbReader["ID"]); + exists.Add(id); + } } } + conn.Close(); } - bool[] results = new bool[uuids.Length]; for (int i = 0; i < uuids.Length; i++) results[i] = exists.Contains(uuids[i]); return results; @@ -310,27 +315,43 @@ namespace OpenSim.Data.MySQL public int Count() { - MySqlCommand cmd = m_Connection.CreateCommand(); + int count = 0; - cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table); - - IDataReader reader = ExecuteReader(cmd); + using (MySqlConnection conn = new MySqlConnection(m_ConnectionString)) + { + try + { + conn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString()); + return 0; + } - reader.Read(); + using(MySqlCommand cmd = conn.CreateCommand()) + { + cmd.CommandText = String.Format("select count(*) as count from {0}",m_Table); - int count = Convert.ToInt32(reader["count"]); + using (IDataReader reader = cmd.ExecuteReader()) + { + reader.Read(); - reader.Close(); - FreeCommand(cmd); + count = Convert.ToInt32(reader["count"]); + } + } + conn.Close(); + } return count; } public bool Delete(string id) { - using (MySqlCommand cmd = m_Connection.CreateCommand()) + using(MySqlCommand cmd = new MySqlCommand()) { - cmd.CommandText = String.Format("delete from {0} where id = ?id", m_Table); + + cmd.CommandText = String.Format("delete from {0} where id = ?id",m_Table); cmd.Parameters.AddWithValue("?id", id); @@ -342,70 +363,68 @@ namespace OpenSim.Data.MySQL public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store) { - MySqlConnection importConn; - - try - { - importConn = new MySqlConnection(conn); + int imported = 0; - importConn.Open(); - } - catch (MySqlException e) + using (MySqlConnection importConn = new MySqlConnection(conn)) { - m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", - e.Message.ToString()); - - return; - } + try + { + importConn.Open(); + } + catch (MySqlException e) + { + m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", + e.Message.ToString()); - int imported = 0; + return; + } - MySqlCommand cmd = importConn.CreateCommand(); + using (MySqlCommand cmd = importConn.CreateCommand()) + { + string limit = String.Empty; + if (count != -1) + { + limit = String.Format(" limit {0},{1}", start, count); + } - 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); + cmd.CommandText = String.Format("select * from {0}{1}", table, limit); - MainConsole.Instance.Output("Querying database"); - IDataReader reader = cmd.ExecuteReader(); + MainConsole.Instance.Output("Querying database"); + using (IDataReader reader = cmd.ExecuteReader()) + { + MainConsole.Instance.Output("Reading data"); - MainConsole.Instance.Output("Reading data"); + while (reader.Read()) + { + if ((imported % 100) == 0) + { + MainConsole.Instance.Output(String.Format("{0} assets imported so far", imported)); + } - 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(); + AssetBase asset = new AssetBase(); + AssetMetadata meta = new AssetMetadata(); - meta.ID = reader["id"].ToString(); - meta.FullID = new UUID(meta.ID); + 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"])); + 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"]; + asset.Metadata = meta; + asset.Data = (byte[])reader["data"]; - store(asset, force); + store(asset, force); - imported++; + imported++; + } + } + } + importConn.Close(); } - reader.Close(); - cmd.Dispose(); - importConn.Close(); - MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported)); } diff --git a/OpenSim/Data/MySQL/MySQLFramework.cs b/OpenSim/Data/MySQL/MySQLFramework.cs index 5820a90..98106f0 100644 --- a/OpenSim/Data/MySQL/MySQLFramework.cs +++ b/OpenSim/Data/MySQL/MySQLFramework.cs @@ -36,7 +36,7 @@ using MySql.Data.MySqlClient; namespace OpenSim.Data.MySQL { /// - /// A database interface class to a user profile storage system + /// Common code for a number of database modules /// public class MySqlFramework { @@ -44,30 +44,78 @@ namespace OpenSim.Data.MySQL log4net.LogManager.GetLogger( System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); - protected string m_connectionString; + protected string m_connectionString = String.Empty; + protected MySqlTransaction m_trans = null; + // Constructor using a connection string. Instances constructed + // this way will open a new connection for each call. protected MySqlFramework(string connectionString) { m_connectionString = connectionString; } + // Constructor using a connection object. Instances constructed + // this way will use the connection object and never create + // new connections. + protected MySqlFramework(MySqlTransaction trans) + { + m_trans = trans; + } + + ////////////////////////////////////////////////////////////// + // + // All non queries are funneled through one connection + // to increase performance a little + // protected int ExecuteNonQuery(MySqlCommand cmd) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + if (m_trans == null) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + int ret = ExecuteNonQueryWithConnection(cmd, dbcon); + dbcon.Close(); + return ret; + } + } + else + { + return ExecuteNonQueryWithTransaction(cmd, m_trans); + } + } + + private int ExecuteNonQueryWithTransaction(MySqlCommand cmd, MySqlTransaction trans) + { + cmd.Transaction = trans; + return ExecuteNonQueryWithConnection(cmd, trans.Connection); + } + + private int ExecuteNonQueryWithConnection(MySqlCommand cmd, MySqlConnection dbcon) + { + try { - dbcon.Open(); cmd.Connection = dbcon; try { - return cmd.ExecuteNonQuery(); + int ret = cmd.ExecuteNonQuery(); + cmd.Connection = null; + return ret; } catch (Exception e) { m_log.Error(e.Message, e); + m_log.Error(Environment.StackTrace.ToString()); + cmd.Connection = null; return 0; } } + catch (Exception e) + { + m_log.Error(e.Message, e); + return 0; + } } } -} \ No newline at end of file +} diff --git a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs index 35fa89f..9bd3c0c 100644 --- a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs +++ b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs @@ -40,7 +40,7 @@ namespace OpenSim.Data.MySQL public class MySQLGenericTableHandler : MySqlFramework where T: class, new() { // private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - + protected Dictionary m_Fields = new Dictionary(); @@ -53,14 +53,27 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } + public MySQLGenericTableHandler(MySqlTransaction trans, + string realm, string storeName) : base(trans) + { + m_Realm = realm; + + CommonConstruct(storeName); + } + public MySQLGenericTableHandler(string connectionString, string realm, string storeName) : base(connectionString) { m_Realm = realm; - m_connectionString = connectionString; - + + CommonConstruct(storeName); + } + + protected void CommonConstruct(string storeName) + { if (storeName != String.Empty) { + // We always use a new connection for any Migrations using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); @@ -111,6 +124,11 @@ namespace OpenSim.Data.MySQL public virtual T[] Get(string[] fields, string[] keys) { + return Get(fields, keys, String.Empty); + } + + public virtual T[] Get(string[] fields, string[] keys, string options) + { if (fields.Length != keys.Length) return new T[0]; @@ -126,81 +144,107 @@ namespace OpenSim.Data.MySQL string where = String.Join(" and ", terms.ToArray()); - string query = String.Format("select * from {0} where {1}", - m_Realm, where); + string query = String.Format("select * from {0} where {1} {2}", + m_Realm, where, options); cmd.CommandText = query; - + return DoQuery(cmd); } } protected T[] DoQuery(MySqlCommand cmd) { + if (m_trans == null) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + T[] ret = DoQueryWithConnection(cmd, dbcon); + dbcon.Close(); + return ret; + } + } + else + { + return DoQueryWithTransaction(cmd, m_trans); + } + } + + protected T[] DoQueryWithTransaction(MySqlCommand cmd, MySqlTransaction trans) + { + cmd.Transaction = trans; + + return DoQueryWithConnection(cmd, trans.Connection); + } + + protected T[] DoQueryWithConnection(MySqlCommand cmd, MySqlConnection dbcon) + { List result = new List(); - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + cmd.Connection = dbcon; + + using (IDataReader reader = cmd.ExecuteReader()) { - dbcon.Open(); - cmd.Connection = dbcon; + if (reader == null) + return new T[0]; - using (IDataReader reader = cmd.ExecuteReader()) - { - if (reader == null) - return new T[0]; + CheckColumnNames(reader); - CheckColumnNames(reader); + while (reader.Read()) + { + T row = new T(); - while (reader.Read()) + foreach (string name in m_Fields.Keys) { - T row = new T(); - - foreach (string name in m_Fields.Keys) + if (reader[name] is DBNull) + { + continue; + } + if (m_Fields[name].FieldType == typeof(bool)) + { + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v != 0 ? true : false); + } + else if (m_Fields[name].FieldType == typeof(UUID)) { - if (reader[name] is DBNull) - { - continue; - } - if (m_Fields[name].FieldType == typeof(bool)) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v != 0 ? true : false); - } - else if (m_Fields[name].FieldType == typeof(UUID)) - { - m_Fields[name].SetValue(row, DBGuid.FromDB(reader[name])); - } - else if (m_Fields[name].FieldType == typeof(int)) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v); - } - else - { - m_Fields[name].SetValue(row, reader[name]); - } + m_Fields[name].SetValue(row, DBGuid.FromDB(reader[name])); } - - if (m_DataField != null) + else if (m_Fields[name].FieldType == typeof(int)) { - Dictionary data = - new Dictionary(); + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v); + } + else if (m_Fields[name].FieldType == typeof(uint)) + { + uint v = Convert.ToUInt32(reader[name]); + m_Fields[name].SetValue(row, v); + } + else + { + m_Fields[name].SetValue(row, reader[name]); + } + } - foreach (string col in m_ColumnNames) - { - data[col] = reader[col].ToString(); - if (data[col] == null) - data[col] = String.Empty; - } + if (m_DataField != null) + { + Dictionary data = + new Dictionary(); - m_DataField.SetValue(row, data); + foreach (string col in m_ColumnNames) + { + data[col] = reader[col].ToString(); + if (data[col] == null) + data[col] = String.Empty; } - result.Add(row); + m_DataField.SetValue(row, data); } + + result.Add(row); } } - + cmd.Connection = null; return result.ToArray(); } @@ -210,9 +254,9 @@ namespace OpenSim.Data.MySQL { string query = String.Format("select * from {0} where {1}", m_Realm, where); - + cmd.CommandText = query; - + return DoQuery(cmd); } } @@ -231,16 +275,16 @@ namespace OpenSim.Data.MySQL { names.Add(fi.Name); values.Add("?" + fi.Name); - + // Temporarily return more information about what field is unexpectedly null for - // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the + // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the // InventoryTransferModule or we may be required to substitute a DBNull here. if (fi.GetValue(row) == null) throw new NullReferenceException( string.Format( - "[MYSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null", + "[MYSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null", fi.Name, row)); - + cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString()); } @@ -352,14 +396,26 @@ namespace OpenSim.Data.MySQL public object DoQueryScalar(MySqlCommand cmd) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + if (m_trans == null) { - dbcon.Open(); - cmd.Connection = dbcon; + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + cmd.Connection = dbcon; + + Object ret = cmd.ExecuteScalar(); + cmd.Connection = null; + dbcon.Close(); + return ret; + } + } + else + { + cmd.Connection = m_trans.Connection; + cmd.Transaction = m_trans; return cmd.ExecuteScalar(); } } - } -} \ No newline at end of file +} diff --git a/OpenSim/Data/MySQL/MySQLGroupsData.cs b/OpenSim/Data/MySQL/MySQLGroupsData.cs index afa499e..4e73ee7 100644 --- a/OpenSim/Data/MySQL/MySQLGroupsData.cs +++ b/OpenSim/Data/MySQL/MySQLGroupsData.cs @@ -133,10 +133,10 @@ namespace OpenSim.Data.MySQL public bool DeleteMember(UUID groupID, string pricipalID) { - return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" }, + 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()); @@ -168,7 +168,7 @@ namespace OpenSim.Data.MySQL public bool DeleteRole(UUID groupID, UUID roleID) { - return m_Roles.Delete(new string[] { "GroupID", "RoleID" }, + return m_Roles.Delete(new string[] { "GroupID", "RoleID" }, new string[] { groupID.ToString(), roleID.ToString() }); } @@ -360,7 +360,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsGroupsHandler(string connectionString, string realm, string store) + public MySqlGroupsGroupsHandler(string connectionString, string realm, string store) : base(connectionString, realm, store) { } @@ -375,7 +375,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsMembershipHandler(string connectionString, string realm) + public MySqlGroupsMembershipHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } @@ -390,7 +390,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsRolesHandler(string connectionString, string realm) + public MySqlGroupsRolesHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } @@ -405,7 +405,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsRoleMembershipHandler(string connectionString, string realm) + public MySqlGroupsRoleMembershipHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } @@ -420,7 +420,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsInvitesHandler(string connectionString, string realm) + public MySqlGroupsInvitesHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } @@ -431,8 +431,7 @@ namespace OpenSim.Data.MySQL 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 + cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 WEEK", m_Realm); ExecuteNonQuery(cmd); } @@ -448,7 +447,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsNoticesHandler(string connectionString, string realm) + public MySqlGroupsNoticesHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } @@ -476,7 +475,7 @@ namespace OpenSim.Data.MySQL get { return GetType().Assembly; } } - public MySqlGroupsPrincipalsHandler(string connectionString, string realm) + public MySqlGroupsPrincipalsHandler(string connectionString, string realm) : base(connectionString, realm, string.Empty) { } diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs index e9b10f3..cc787cc 100644 --- a/OpenSim/Data/MySQL/MySQLInventoryData.cs +++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs @@ -78,6 +78,7 @@ namespace OpenSim.Data.MySQL dbcon.Open(); Migration m = new Migration(dbcon, assem, "InventoryStore"); m.Update(); + dbcon.Close(); } } @@ -130,6 +131,7 @@ namespace OpenSim.Data.MySQL items.Add(item); } + dbcon.Close(); return items; } } @@ -170,6 +172,7 @@ namespace OpenSim.Data.MySQL while (reader.Read()) items.Add(readInventoryFolder(reader)); + dbcon.Close(); return items; } } @@ -221,6 +224,7 @@ namespace OpenSim.Data.MySQL if (items.Count > 0) rootFolder = items[0]; + dbcon.Close(); return rootFolder; } } @@ -261,6 +265,7 @@ namespace OpenSim.Data.MySQL while (reader.Read()) items.Add(readInventoryFolder(reader)); + dbcon.Close(); return items; } } @@ -288,7 +293,7 @@ namespace OpenSim.Data.MySQL // TODO: this is to handle a case where NULLs creep in there, which we are not sure is endemic to the system, or legacy. It would be nice to live fix these. // (DBGuid.FromDB() reads db NULLs as well, returns UUID.Zero) item.CreatorId = reader["creatorID"].ToString(); - + // Be a bit safer in parsing these because the // database doesn't enforce them to be not null, and // the inventory still works if these are weird in the @@ -352,6 +357,7 @@ namespace OpenSim.Data.MySQL if (reader.Read()) item = readInventoryItem(reader); + dbcon.Close(); return item; } } @@ -417,6 +423,7 @@ namespace OpenSim.Data.MySQL if (reader.Read()) folder = readInventoryFolder(reader); + dbcon.Close(); return folder; } } @@ -453,7 +460,7 @@ namespace OpenSim.Data.MySQL itemName = item.Name.Substring(0, 64); m_log.Warn("[INVENTORY DB]: Name field truncated from " + item.Name.Length + " to " + itemName.Length + " characters on add item"); } - + string itemDesc = item.Description; if (item.Description.Length > 128) { @@ -490,10 +497,10 @@ namespace OpenSim.Data.MySQL result.Parameters.AddWithValue("?groupID", item.GroupID); result.Parameters.AddWithValue("?groupOwned", item.GroupOwned); result.Parameters.AddWithValue("?flags", item.Flags); - + lock (m_dbLock) result.ExecuteNonQuery(); - + result.Dispose(); } @@ -504,6 +511,7 @@ namespace OpenSim.Data.MySQL lock (m_dbLock) result.ExecuteNonQuery(); } + dbcon.Close(); } } catch (MySqlException e) @@ -540,6 +548,7 @@ namespace OpenSim.Data.MySQL lock (m_dbLock) cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (MySqlException e) @@ -600,6 +609,7 @@ namespace OpenSim.Data.MySQL m_log.Error(e.ToString()); } } + dbcon.Close(); } } @@ -630,7 +640,7 @@ namespace OpenSim.Data.MySQL { cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString()); - + try { lock (m_dbLock) @@ -643,6 +653,7 @@ namespace OpenSim.Data.MySQL m_log.Error(e.ToString()); } } + dbcon.Close(); } } @@ -806,6 +817,7 @@ namespace OpenSim.Data.MySQL lock (m_dbLock) cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (MySqlException e) @@ -833,6 +845,7 @@ namespace OpenSim.Data.MySQL lock (m_dbLock) cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (MySqlException e) @@ -860,7 +873,7 @@ namespace OpenSim.Data.MySQL deleteOneFolder(folderID); deleteItemsInFolder(folderID); } - + public List fetchActiveGestures(UUID avatarID) { lock (m_dbLock) @@ -886,6 +899,7 @@ namespace OpenSim.Data.MySQL if (item != null) list.Add(item); } + dbcon.Close(); return list; } } diff --git a/OpenSim/Data/MySQL/MySQLMigrations.cs b/OpenSim/Data/MySQL/MySQLMigrations.cs index 81a0e83..2043dae 100644 --- a/OpenSim/Data/MySQL/MySQLMigrations.cs +++ b/OpenSim/Data/MySQL/MySQLMigrations.cs @@ -39,16 +39,16 @@ namespace OpenSim.Data.MySQL { /// This is a MySQL-customized migration processor. The only difference is in how /// it executes SQL scripts (using MySqlScript instead of MyCommand) - /// + /// /// public class MySqlMigration : Migration { public MySqlMigration() : base() - { + { } - public MySqlMigration(DbConnection conn, Assembly assem, string subtype, string type) : + public MySqlMigration(DbConnection conn, Assembly assem, string subtype, string type) : base(conn, assem, subtype, type) { } diff --git a/OpenSim/Data/MySQL/MySQLMuteListData.cs b/OpenSim/Data/MySQL/MySQLMuteListData.cs new file mode 100644 index 0000000..a5935a3 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLMuteListData.cs @@ -0,0 +1,67 @@ +/* + * 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 MySqlMuteListData : MySQLGenericTableHandler, IMuteListData + { + public MySqlMuteListData(string connectionString) + : base(connectionString, "MuteList", "MuteListStore") + { + } + + public MuteData[] Get(UUID agentID) + { + MuteData[] data = base.Get("AgentID", agentID.ToString()); + return data; + } + + public bool Delete(UUID agentID, UUID muteID, string muteName) + { + string cmnd ="delete from MuteList where AgentID = ?AgentID and MuteID = ?MuteID and MuteName = ?MuteName"; + + using (MySqlCommand cmd = new MySqlCommand(cmnd)) + { + cmd.Parameters.AddWithValue("?AgentID", agentID.ToString()); + cmd.Parameters.AddWithValue("?MuteID", muteID.ToString()); + cmd.Parameters.AddWithValue("?MuteName", muteName); + + if (ExecuteNonQuery(cmd) > 0) + return true; + return false; + } + } + } +} \ No newline at end of file diff --git a/OpenSim/Data/MySQL/MySQLOfflineIMData.cs b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs index bafd204..7608858 100644 --- a/OpenSim/Data/MySQL/MySQLOfflineIMData.cs +++ b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs @@ -50,7 +50,7 @@ namespace OpenSim.Data.MySQL 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/MySQLPresenceData.cs b/OpenSim/Data/MySQL/MySQLPresenceData.cs index 3f90639..70aca5f 100644 --- a/OpenSim/Data/MySQL/MySQLPresenceData.cs +++ b/OpenSim/Data/MySQL/MySQLPresenceData.cs @@ -66,9 +66,9 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand()) { cmd.CommandText = String.Format("delete from {0} where `RegionID`=?RegionID", m_Realm); - + cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - + ExecuteNonQuery(cmd); } } @@ -85,10 +85,10 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand()) { cmd.CommandText = String.Format("update {0} set RegionID=?RegionID, LastSeen=NOW() where `SessionID`=?SessionID", m_Realm); - + cmd.Parameters.AddWithValue("?SessionID", sessionID.ToString()); cmd.Parameters.AddWithValue("?RegionID", regionID.ToString()); - + if (ExecuteNonQuery(cmd) == 0) return false; } diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs index 2ad7590..46df421 100644 --- a/OpenSim/Data/MySQL/MySQLRegionData.cs +++ b/OpenSim/Data/MySQL/MySQLRegionData.cs @@ -60,6 +60,7 @@ namespace OpenSim.Data.MySQL dbcon.Open(); Migration m = new Migration(dbcon, Assembly, "GridStore"); m.Update(); + dbcon.Close(); } } @@ -82,6 +83,7 @@ namespace OpenSim.Data.MySQL public RegionData Get(int posX, int posY, UUID scopeID) { +/* fixed size regions string command = "select * from `"+m_Realm+"` where locX = ?posX and locY = ?posY"; if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; @@ -98,6 +100,45 @@ namespace OpenSim.Data.MySQL return ret[0]; } +*/ + // extend database search for maximum region size area + string command = "select * from `" + m_Realm + "` where locX between ?startX and ?endX and locY between ?startY and ?endY"; + if (scopeID != UUID.Zero) + command += " and ScopeID = ?scopeID"; + + int startX = posX - (int)Constants.MaximumRegionSize; + int startY = posY - (int)Constants.MaximumRegionSize; + int endX = posX; + int endY = posY; + + List ret; + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?startX", startX.ToString()); + cmd.Parameters.AddWithValue("?startY", startY.ToString()); + cmd.Parameters.AddWithValue("?endX", endX.ToString()); + cmd.Parameters.AddWithValue("?endY", endY.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + + ret = RunCommand(cmd); + } + + if (ret.Count == 0) + return null; + + // find the first that contains pos + RegionData rg = null; + foreach (RegionData r in ret) + { + if (posX >= r.posX && posX < r.posX + r.sizeX + && posY >= r.posY && posY < r.posY + r.sizeY) + { + rg = r; + break; + } + } + + return rg; } public RegionData Get(UUID regionID, UUID scopeID) @@ -121,6 +162,7 @@ namespace OpenSim.Data.MySQL public List Get(int startX, int startY, int endX, int endY, UUID scopeID) { +/* fix size regions string command = "select * from `"+m_Realm+"` where locX between ?startX and ?endX and locY between ?startY and ?endY"; if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; @@ -135,6 +177,38 @@ namespace OpenSim.Data.MySQL return RunCommand(cmd); } + */ + string command = "select * from `" + m_Realm + "` where locX between ?startX and ?endX and locY between ?startY and ?endY"; + if (scopeID != UUID.Zero) + command += " and ScopeID = ?scopeID"; + + int qstartX = startX - (int)Constants.MaximumRegionSize; + int qstartY = startY - (int)Constants.MaximumRegionSize; + + List dbret; + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?startX", qstartX.ToString()); + cmd.Parameters.AddWithValue("?startY", qstartY.ToString()); + cmd.Parameters.AddWithValue("?endX", endX.ToString()); + cmd.Parameters.AddWithValue("?endY", endY.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + + dbret = RunCommand(cmd); + } + + List ret = new List(); + + if (dbret.Count == 0) + return ret; + + foreach (RegionData r in dbret) + { + if (r.posX + r.sizeX > startX && r.posX <= endX + && r.posY + r.sizeY > startY && r.posY <= endY) + ret.Add(r); + } + return ret; } public List RunCommand(MySqlCommand cmd) @@ -187,6 +261,8 @@ namespace OpenSim.Data.MySQL retList.Add(ret); } } + cmd.Connection = null; + dbcon.Close(); } return retList; @@ -337,7 +413,7 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand(command)) { cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - + return RunCommand(cmd); } } diff --git a/OpenSim/Data/MySQL/MySQLSimulationData.cs b/OpenSim/Data/MySQL/MySQLSimulationData.cs index bb0ab75..e754522 100644 --- a/OpenSim/Data/MySQL/MySQLSimulationData.cs +++ b/OpenSim/Data/MySQL/MySQLSimulationData.cs @@ -55,7 +55,7 @@ namespace OpenSim.Data.MySQL /// /// 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 + /// 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. @@ -76,7 +76,7 @@ namespace OpenSim.Data.MySQL Initialise(connectionString); } - public void Initialise(string connectionString) + public virtual void Initialise(string connectionString) { m_connectionString = connectionString; @@ -88,6 +88,7 @@ namespace OpenSim.Data.MySQL // Migration m = new Migration(dbcon, Assembly, "RegionStore"); m.Update(); + dbcon.Close(); } } @@ -123,7 +124,7 @@ namespace OpenSim.Data.MySQL public void Dispose() {} - public void StoreObject(SceneObjectGroup obj, UUID regionUUID) + public virtual void StoreObject(SceneObjectGroup obj, UUID regionUUID) { uint flags = obj.RootPart.GetEffectiveObjectFlags(); @@ -167,7 +168,7 @@ namespace OpenSim.Data.MySQL "SitTargetOrientY, SitTargetOrientZ, " + "RegionUUID, CreatorID, " + "OwnerID, GroupID, " + - "LastOwnerID, SceneGroupID, " + + "LastOwnerID, RezzerID, SceneGroupID, " + "PayPrice, PayButton1, " + "PayButton2, PayButton3, " + "PayButton4, LoopedSound, " + @@ -183,10 +184,12 @@ namespace OpenSim.Data.MySQL "ParticleSystem, ClickAction, Material, " + "CollisionSound, CollisionSoundVolume, " + "PassTouches, " + - "LinkNumber, MediaURL, AttachedPosX, " + - "AttachedPosY, AttachedPosZ, KeyframeMotion, " + + "PassCollisions, " + + "LinkNumber, MediaURL, KeyframeMotion, AttachedPosX, " + + "AttachedPosY, AttachedPosZ, " + "PhysicsShapeType, Density, GravityModifier, " + - "Friction, Restitution, DynAttrs " + + "Friction, Restitution, Vehicle, PhysInertia, DynAttrs, " + + "RotationAxisLocks" + ") values (" + "?UUID, " + "?CreationDate, ?Name, ?Text, " + "?Description, ?SitName, ?TouchName, " + @@ -205,7 +208,7 @@ namespace OpenSim.Data.MySQL "?SitTargetOrientW, ?SitTargetOrientX, " + "?SitTargetOrientY, ?SitTargetOrientZ, " + "?RegionUUID, ?CreatorID, ?OwnerID, " + - "?GroupID, ?LastOwnerID, ?SceneGroupID, " + + "?GroupID, ?LastOwnerID, ?RezzerID, ?SceneGroupID, " + "?PayPrice, ?PayButton1, ?PayButton2, " + "?PayButton3, ?PayButton4, ?LoopedSound, " + "?LoopedSoundGain, ?TextureAnimation, " + @@ -218,11 +221,12 @@ namespace OpenSim.Data.MySQL "?SaleType, ?ColorR, ?ColorG, " + "?ColorB, ?ColorA, ?ParticleSystem, " + "?ClickAction, ?Material, ?CollisionSound, " + - "?CollisionSoundVolume, ?PassTouches, " + - "?LinkNumber, ?MediaURL, ?AttachedPosX, " + - "?AttachedPosY, ?AttachedPosZ, ?KeyframeMotion, " + + "?CollisionSoundVolume, ?PassTouches, ?PassCollisions, " + + "?LinkNumber, ?MediaURL, ?KeyframeMotion, ?AttachedPosX, " + + "?AttachedPosY, ?AttachedPosZ, " + "?PhysicsShapeType, ?Density, ?GravityModifier, " + - "?Friction, ?Restitution, ?DynAttrs)"; + "?Friction, ?Restitution, ?Vehicle, ?PhysInertia, ?DynAttrs," + + "?RotationAxisLocks)"; FillPrimCommand(cmd, prim, obj.UUID, regionUUID); @@ -258,14 +262,15 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } } + dbcon.Close(); } } } - public void RemoveObject(UUID obj, UUID regionUUID) + public virtual void RemoveObject(UUID obj, UUID regionUUID) { // m_log.DebugFormat("[REGION DB]: Deleting scene object {0} from {1} in database", obj, regionUUID); - + List uuids = new List(); // Formerly, this used to check the region UUID. @@ -297,6 +302,7 @@ namespace OpenSim.Data.MySQL cmd.CommandText = "delete from prims where SceneGroupID= ?UUID"; ExecuteNonQuery(cmd); } + dbcon.Close(); } } @@ -317,7 +323,8 @@ namespace OpenSim.Data.MySQL /// the Item UUID private void RemoveItems(UUID uuid) { - lock (m_dbLock) + // locked by caller +// lock (m_dbLock) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { @@ -330,6 +337,7 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } + dbcon.Close(); } } } @@ -368,6 +376,7 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } + dbcon.Close(); } } } @@ -407,11 +416,12 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } + dbcon.Close(); } } } - public List LoadObjects(UUID regionID) + public virtual List LoadObjects(UUID regionID) { const int ROWS_PER_QUERY = 5000; @@ -456,6 +466,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } @@ -505,7 +516,7 @@ namespace OpenSim.Data.MySQL #region Prim Inventory Loading // Instead of attempting to LoadItems on every prim, - // most of which probably have no items... get a + // most of which probably have no items... get a // list from DB of all prims which have items and // LoadItems only on those List primsWithInventory = new List(); @@ -531,6 +542,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } @@ -576,6 +588,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } prim.Inventory.RestoreInventoryItems(inventory); @@ -590,40 +603,102 @@ namespace OpenSim.Data.MySQL public void StoreTerrain(TerrainData terrData, UUID regionID) { - lock (m_dbLock) + Util.FireAndForget(delegate(object x) { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); + m_log.Info("[REGION DB]: Storing terrain"); - using (MySqlCommand cmd = dbcon.CreateCommand()) + int terrainDBRevision; + Array terrainDBblob; + terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); + + lock (m_dbLock) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - cmd.CommandText = "delete from terrain where RegionUUID = ?RegionUUID"; - cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + dbcon.Open(); - ExecuteNonQuery(cmd); + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "delete from terrain where RegionUUID = ?RegionUUID"; + cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString()); - int terrainDBRevision; - Array terrainDBblob; - terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); + using (MySqlCommand cmd2 = dbcon.CreateCommand()) + { + try + { + cmd2.CommandText = "insert into terrain (RegionUUID, " + + "Revision, Heightfield) values (?RegionUUID, " + + "?Revision, ?Heightfield)"; - m_log.InfoFormat("{0} Storing terrain. X={1}, Y={2}, rev={3}", - LogHeader, terrData.SizeX, terrData.SizeY, terrainDBRevision); + cmd2.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + cmd2.Parameters.AddWithValue("Revision", terrainDBRevision); + cmd2.Parameters.AddWithValue("Heightfield", terrainDBblob); - cmd.CommandText = "insert into terrain (RegionUUID, Revision, Heightfield)" - + "values (?RegionUUID, ?Revision, ?Heightfield)"; + ExecuteNonQuery(cmd); + ExecuteNonQuery(cmd2); + } + catch (Exception e) + { + m_log.ErrorFormat(e.ToString()); + } + } + } + dbcon.Close(); + } + } + }); + } - cmd.Parameters.AddWithValue("Revision", terrainDBRevision); - cmd.Parameters.AddWithValue("Heightfield", terrainDBblob); + public void StoreBakedTerrain(TerrainData terrData, UUID regionID) + { + Util.FireAndForget(delegate(object x) + { + m_log.Info("[REGION DB]: Storing Baked terrain"); - ExecuteNonQuery(cmd); + int terrainDBRevision; + Array terrainDBblob; + terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); + + lock (m_dbLock) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "delete from bakedterrain where RegionUUID = ?RegionUUID"; + cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + + using (MySqlCommand cmd2 = dbcon.CreateCommand()) + { + try + { + cmd2.CommandText = "insert into bakedterrain (RegionUUID, " + + "Revision, Heightfield) values (?RegionUUID, " + + "?Revision, ?Heightfield)"; + + cmd2.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + cmd2.Parameters.AddWithValue("Revision", terrainDBRevision); + cmd2.Parameters.AddWithValue("Heightfield", terrainDBblob); + + ExecuteNonQuery(cmd); + ExecuteNonQuery(cmd2); + } + catch (Exception e) + { + m_log.ErrorFormat(e.ToString()); + } + } + } + dbcon.Close(); } } - } + }); } // Legacy region loading - public double[,] LoadTerrain(UUID regionID) + public virtual double[,] LoadTerrain(UUID regionID) { double[,] ret = null; TerrainData terrData = LoadTerrain(regionID, (int)Constants.RegionSize, (int)Constants.RegionSize, (int)Constants.RegionHeight); @@ -636,9 +711,12 @@ namespace OpenSim.Data.MySQL public TerrainData LoadTerrain(UUID regionID, int pSizeX, int pSizeY, int pSizeZ) { TerrainData terrData = null; + byte[] blob = null; + int rev = 0; lock (m_dbLock) { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); @@ -654,19 +732,64 @@ namespace OpenSim.Data.MySQL { while (reader.Read()) { - int rev = Convert.ToInt32(reader["Revision"]); - byte[] blob = (byte[])reader["Heightfield"]; - terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob); + rev = Convert.ToInt32(reader["Revision"]); + if ((reader["Heightfield"] != DBNull.Value)) + { + blob = (byte[])reader["Heightfield"]; + } + } + } + } + dbcon.Close(); + } + } + + if(blob != null) + terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob); + + return terrData; + } + + public TerrainData LoadBakedTerrain(UUID regionID, int pSizeX, int pSizeY, int pSizeZ) + { + TerrainData terrData = null; + byte[] blob = null; + int rev = 0; + + lock (m_dbLock) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "select RegionUUID, Revision, Heightfield " + + "from bakedterrain where RegionUUID = ?RegionUUID "; + cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + + using (IDataReader reader = ExecuteReader(cmd)) + { + while (reader.Read()) + { + rev = Convert.ToInt32(reader["Revision"]); + if ((reader["Heightfield"] != DBNull.Value)) + { + blob = (byte[])reader["Heightfield"]; + } } } } + dbcon.Close(); } } + if(blob != null) + terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob); return terrData; } - public void RemoveLandObject(UUID globalID) + public virtual void RemoveLandObject(UUID globalID) { lock (m_dbLock) { @@ -681,11 +804,12 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } + dbcon.Close(); } } } - public void StoreLandObject(ILandObject parcel) + public virtual void StoreLandObject(ILandObject parcel) { lock (m_dbLock) { @@ -705,7 +829,8 @@ namespace OpenSim.Data.MySQL "UserLocationX, UserLocationY, UserLocationZ, " + "UserLookAtX, UserLookAtY, UserLookAtZ, " + "AuthbuyerID, OtherCleanTime, Dwell, MediaType, MediaDescription, " + - "MediaSize, MediaLoop, ObscureMusic, ObscureMedia) values (" + + "MediaSize, MediaLoop, ObscureMusic, ObscureMedia, " + + "SeeAVs, AnyAVSounds, GroupAVSounds) values (" + "?UUID, ?RegionUUID, " + "?LocalLandID, ?Bitmap, ?Name, ?Description, " + "?OwnerUUID, ?IsGroupOwned, ?Area, ?AuctionID, " + @@ -716,7 +841,8 @@ namespace OpenSim.Data.MySQL "?UserLocationX, ?UserLocationY, ?UserLocationZ, " + "?UserLookAtX, ?UserLookAtY, ?UserLookAtZ, " + "?AuthbuyerID, ?OtherCleanTime, ?Dwell, ?MediaType, ?MediaDescription, "+ - "CONCAT(?MediaWidth, ',', ?MediaHeight), ?MediaLoop, ?ObscureMusic, ?ObscureMedia)"; + "CONCAT(?MediaWidth, ',', ?MediaHeight), ?MediaLoop, ?ObscureMusic, ?ObscureMedia, " + + "?SeeAVs, ?AnyAVSounds, ?GroupAVSounds)"; FillLandCommand(cmd, parcel.LandData, parcel.RegionUUID); @@ -738,11 +864,12 @@ namespace OpenSim.Data.MySQL cmd.Parameters.Clear(); } } + dbcon.Close(); } } } - public RegionLightShareData LoadRegionWindlightSettings(UUID regionUUID) + public virtual RegionLightShareData LoadRegionWindlightSettings(UUID regionUUID) { RegionLightShareData nWP = new RegionLightShareData(); nWP.OnSave += StoreRegionWindlightSettings; @@ -759,90 +886,94 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?regionID", regionUUID.ToString()); - IDataReader result = ExecuteReader(cmd); - if (!result.Read()) + using(IDataReader result = ExecuteReader(cmd)) { - //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; + 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; + } } } + dbcon.Close(); } return nWP; } - public RegionSettings LoadRegionSettings(UUID regionUUID) + public virtual RegionSettings LoadRegionSettings(UUID regionUUID) { RegionSettings rs = null; + bool needStore = false; lock (m_dbLock) { @@ -868,19 +999,23 @@ namespace OpenSim.Data.MySQL rs.RegionUUID = regionUUID; rs.OnSave += StoreRegionSettings; - StoreRegionSettings(rs); + needStore = true; } } } + dbcon.Close(); } } + if(needStore) + StoreRegionSettings(rs); + LoadSpawnPoints(rs); return rs; } - public void StoreRegionWindlightSettings(RegionLightShareData wl) + public virtual void StoreRegionWindlightSettings(RegionLightShareData wl) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { @@ -888,31 +1023,32 @@ namespace OpenSim.Data.MySQL 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.CommandText = "REPLACE INTO `regionwindlight` (`region_id`, `water_color_r`, `water_color_g`, " + + "`water_color_b`, `water_fog_density_exponent`, `underwater_fog_modifier`, " + + "`reflection_wavelet_scale_1`, `reflection_wavelet_scale_2`, `reflection_wavelet_scale_3`, " + + "`fresnel_scale`, `fresnel_offset`, `refract_scale_above`, `refract_scale_below`, " + + "`blur_multiplier`, `big_wave_direction_x`, `big_wave_direction_y`, `little_wave_direction_x`, " + + "`little_wave_direction_y`, `normal_map_texture`, `horizon_r`, `horizon_g`, `horizon_b`, " + + "`horizon_i`, `haze_horizon`, `blue_density_r`, `blue_density_g`, `blue_density_b`, " + + "`blue_density_i`, `haze_density`, `density_multiplier`, `distance_multiplier`, `max_altitude`, " + + "`sun_moon_color_r`, `sun_moon_color_g`, `sun_moon_color_b`, `sun_moon_color_i`, `sun_moon_position`, " + + "`ambient_r`, `ambient_g`, `ambient_b`, `ambient_i`, `east_angle`, `sun_glow_focus`, `sun_glow_size`, " + + "`scene_gamma`, `star_brightness`, `cloud_color_r`, `cloud_color_g`, `cloud_color_b`, `cloud_color_i`, " + + "`cloud_x`, `cloud_y`, `cloud_density`, `cloud_coverage`, `cloud_scale`, `cloud_detail_x`, " + + "`cloud_detail_y`, `cloud_detail_density`, `cloud_scroll_x`, `cloud_scroll_x_lock`, `cloud_scroll_y`, " + + "`cloud_scroll_y_lock`, `draw_classic_clouds`) VALUES (?region_id, ?water_color_r, " + + "?water_color_g, ?water_color_b, ?water_fog_density_exponent, ?underwater_fog_modifier, ?reflection_wavelet_scale_1, " + + "?reflection_wavelet_scale_2, ?reflection_wavelet_scale_3, ?fresnel_scale, ?fresnel_offset, ?refract_scale_above, " + + "?refract_scale_below, ?blur_multiplier, ?big_wave_direction_x, ?big_wave_direction_y, ?little_wave_direction_x, " + + "?little_wave_direction_y, ?normal_map_texture, ?horizon_r, ?horizon_g, ?horizon_b, ?horizon_i, ?haze_horizon, " + + "?blue_density_r, ?blue_density_g, ?blue_density_b, ?blue_density_i, ?haze_density, ?density_multiplier, " + + "?distance_multiplier, ?max_altitude, ?sun_moon_color_r, ?sun_moon_color_g, ?sun_moon_color_b, " + + "?sun_moon_color_i, ?sun_moon_position, ?ambient_r, ?ambient_g, ?ambient_b, ?ambient_i, ?east_angle, " + + "?sun_glow_focus, ?sun_glow_size, ?scene_gamma, ?star_brightness, ?cloud_color_r, ?cloud_color_g, " + + "?cloud_color_b, ?cloud_color_i, ?cloud_x, ?cloud_y, ?cloud_density, ?cloud_coverage, ?cloud_scale, " + + "?cloud_detail_x, ?cloud_detail_y, ?cloud_detail_density, ?cloud_scroll_x, ?cloud_scroll_x_lock, " + + "?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); @@ -977,13 +1113,14 @@ namespace OpenSim.Data.MySQL 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); } + dbcon.Close(); } } - public void RemoveRegionWindlightSettings(UUID regionID) + public virtual void RemoveRegionWindlightSettings(UUID regionID) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { @@ -995,6 +1132,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); ExecuteNonQuery(cmd); } + dbcon.Close(); } } @@ -1013,14 +1151,19 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); - IDataReader result = ExecuteReader(cmd); - if (!result.Read()) + using(IDataReader result = ExecuteReader(cmd)) { - return String.Empty; - } - else - { - return Convert.ToString(result["llsd_settings"]); + if(!result.Read()) + { + dbcon.Close(); + return String.Empty; + } + else + { + string ret = Convert.ToString(result["llsd_settings"]); + dbcon.Close(); + return ret; + } } } } @@ -1041,6 +1184,7 @@ namespace OpenSim.Data.MySQL ExecuteNonQuery(cmd); } + dbcon.Close(); } } @@ -1056,11 +1200,12 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?region_id", regionUUID.ToString()); ExecuteNonQuery(cmd); } + dbcon.Close(); } } #endregion - public void StoreRegionSettings(RegionSettings rs) + public virtual void StoreRegionSettings(RegionSettings rs) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { @@ -1069,52 +1214,51 @@ namespace OpenSim.Data.MySQL 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)"; + "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, block_search, casino, " + + "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, ?block_search, ?casino, " + + "?TelehubObject, ?ParcelImageID)"; FillRegionSettingsCommand(cmd, rs); - ExecuteNonQuery(cmd); } + dbcon.Close(); + SaveSpawnPoints(rs); } - - SaveSpawnPoints(rs); } - public List LoadLandObjects(UUID regionUUID) + public virtual List LoadLandObjects(UUID regionUUID) { List landData = new List(); @@ -1156,6 +1300,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } @@ -1170,12 +1315,16 @@ namespace OpenSim.Data.MySQL { SceneObjectPart prim = new SceneObjectPart(); - // depending on the MySQL connector version, CHAR(36) may be already converted to Guid! + // depending on the MySQL connector version, CHAR(36) may be already converted to Guid! prim.UUID = DBGuid.FromDB(row["UUID"]); prim.CreatorIdentification = (string)row["CreatorID"]; prim.OwnerID = DBGuid.FromDB(row["OwnerID"]); prim.GroupID = DBGuid.FromDB(row["GroupID"]); prim.LastOwnerID = DBGuid.FromDB(row["LastOwnerID"]); + if (row["RezzerID"] != DBNull.Value) + prim.RezzerID = DBGuid.FromDB(row["RezzerID"]); + else + prim.RezzerID = UUID.Zero; // explicit conversion of integers is required, which sort // of sucks. No idea if there is a shortcut here or not. @@ -1294,10 +1443,11 @@ namespace OpenSim.Data.MySQL prim.CollisionSound = DBGuid.FromDB(row["CollisionSound"]); prim.CollisionSoundVolume = (float)(double)row["CollisionSoundVolume"]; - + prim.PassTouches = ((sbyte)row["PassTouches"] != 0); + prim.PassCollisions = ((sbyte)row["PassCollisions"] != 0); prim.LinkNum = (int)row["LinkNumber"]; - + if (!(row["MediaURL"] is System.DBNull)) prim.MediaUrl = (string)row["MediaURL"]; @@ -1313,7 +1463,7 @@ namespace OpenSim.Data.MySQL if (!(row["DynAttrs"] is System.DBNull)) prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]); else - prim.DynAttrs = new DAMap(); + prim.DynAttrs = new DAMap(); if (!(row["KeyframeMotion"] is DBNull)) { @@ -1333,7 +1483,22 @@ namespace OpenSim.Data.MySQL prim.GravityModifier = (float)(double)row["GravityModifier"]; prim.Friction = (float)(double)row["Friction"]; prim.Restitution = (float)(double)row["Restitution"]; - + prim.RotationAxisLocks = (byte)Convert.ToInt32(row["RotationAxisLocks"].ToString()); + + SOPVehicle vehicle = null; + + if (row["Vehicle"].ToString() != String.Empty) + { + vehicle = SOPVehicle.FromXml2(row["Vehicle"].ToString()); + if (vehicle != null) + prim.VehicleParams = vehicle; + } + + PhysicsInertiaData pdata = null; + if (row["PhysInertia"].ToString() != String.Empty) + pdata = PhysicsInertiaData.FromXml2(row["PhysInertia"].ToString()); + prim.PhysicsInertia = pdata; + return prim; } @@ -1344,32 +1509,40 @@ namespace OpenSim.Data.MySQL /// private static TaskInventoryItem BuildItem(IDataReader row) { - TaskInventoryItem taskItem = new TaskInventoryItem(); - - taskItem.ItemID = DBGuid.FromDB(row["itemID"]); - taskItem.ParentPartID = DBGuid.FromDB(row["primID"]); - taskItem.AssetID = DBGuid.FromDB(row["assetID"]); - taskItem.ParentID = DBGuid.FromDB(row["parentFolderID"]); - - taskItem.InvType = Convert.ToInt32(row["invType"]); - taskItem.Type = Convert.ToInt32(row["assetType"]); - - taskItem.Name = (String)row["name"]; - taskItem.Description = (String)row["description"]; - taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]); - taskItem.CreatorIdentification = (String)row["creatorID"]; - taskItem.OwnerID = DBGuid.FromDB(row["ownerID"]); - taskItem.LastOwnerID = DBGuid.FromDB(row["lastOwnerID"]); - taskItem.GroupID = DBGuid.FromDB(row["groupID"]); - - taskItem.NextPermissions = Convert.ToUInt32(row["nextPermissions"]); - taskItem.CurrentPermissions = Convert.ToUInt32(row["currentPermissions"]); - taskItem.BasePermissions = Convert.ToUInt32(row["basePermissions"]); - taskItem.EveryonePermissions = Convert.ToUInt32(row["everyonePermissions"]); - taskItem.GroupPermissions = Convert.ToUInt32(row["groupPermissions"]); - taskItem.Flags = Convert.ToUInt32(row["flags"]); - - return taskItem; + try + { + TaskInventoryItem taskItem = new TaskInventoryItem(); + + taskItem.ItemID = DBGuid.FromDB(row["itemID"]); + taskItem.ParentPartID = DBGuid.FromDB(row["primID"]); + taskItem.AssetID = DBGuid.FromDB(row["assetID"]); + taskItem.ParentID = DBGuid.FromDB(row["parentFolderID"]); + + taskItem.InvType = Convert.ToInt32(row["invType"]); + taskItem.Type = Convert.ToInt32(row["assetType"]); + + taskItem.Name = (String)row["name"]; + taskItem.Description = (String)row["description"]; + taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]); + taskItem.CreatorIdentification = (String)row["creatorID"]; + taskItem.OwnerID = DBGuid.FromDB(row["ownerID"]); + taskItem.LastOwnerID = DBGuid.FromDB(row["lastOwnerID"]); + taskItem.GroupID = DBGuid.FromDB(row["groupID"]); + + taskItem.NextPermissions = Convert.ToUInt32(row["nextPermissions"]); + taskItem.CurrentPermissions = Convert.ToUInt32(row["currentPermissions"]); + taskItem.BasePermissions = Convert.ToUInt32(row["basePermissions"]); + taskItem.EveryonePermissions = Convert.ToUInt32(row["everyonePermissions"]); + taskItem.GroupPermissions = Convert.ToUInt32(row["groupPermissions"]); + taskItem.Flags = Convert.ToUInt32(row["flags"]); + + return taskItem; + } + catch + { + m_log.ErrorFormat("[MYSQL DB]: Error reading task inventory: itemID was {0}, primID was {1}", row["itemID"].ToString(), row["primID"].ToString()); + throw; + } } private static RegionSettings BuildRegionSettings(IDataReader row) @@ -1417,16 +1590,19 @@ namespace OpenSim.Data.MySQL newSettings.Covenant = DBGuid.FromDB(row["covenant"]); newSettings.CovenantChangedDateTime = Convert.ToInt32(row["covenant_datetime"]); newSettings.LoadedCreationDateTime = Convert.ToInt32(row["loaded_creation_datetime"]); - + if (row["loaded_creation_id"] is DBNull) newSettings.LoadedCreationID = ""; - else + else newSettings.LoadedCreationID = (String) row["loaded_creation_id"]; newSettings.TerrainImageID = DBGuid.FromDB(row["map_tile_ID"]); newSettings.ParcelImageID = DBGuid.FromDB(row["parcel_tile_ID"]); newSettings.TelehubObject = DBGuid.FromDB(row["TelehubObject"]); + newSettings.GodBlockSearch = Convert.ToBoolean(row["block_search"]); + newSettings.Casino = Convert.ToBoolean(row["casino"]); + return newSettings; } @@ -1503,6 +1679,13 @@ namespace OpenSim.Data.MySQL newData.ParcelAccessList = new List(); + if (!(row["SeeAVs"] is System.DBNull)) + newData.SeeAVs = Convert.ToInt32(row["SeeAVs"]) != 0 ? true : false; + if (!(row["AnyAVSounds"] is System.DBNull)) + newData.AnyAVSounds = Convert.ToInt32(row["AnyAVSounds"]) != 0 ? true : false; + if (!(row["GroupAVSounds"] is System.DBNull)) + newData.GroupAVSounds = Convert.ToInt32(row["GroupAVSounds"]) != 0 ? true : false; + return newData; } @@ -1550,6 +1733,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("OwnerID", prim.OwnerID.ToString()); cmd.Parameters.AddWithValue("GroupID", prim.GroupID.ToString()); cmd.Parameters.AddWithValue("LastOwnerID", prim.LastOwnerID.ToString()); + cmd.Parameters.AddWithValue("RezzerID", prim.RezzerID.ToString()); cmd.Parameters.AddWithValue("OwnerMask", prim.OwnerMask); cmd.Parameters.AddWithValue("NextOwnerMask", prim.NextOwnerMask); cmd.Parameters.AddWithValue("GroupMask", prim.GroupMask); @@ -1654,6 +1838,11 @@ namespace OpenSim.Data.MySQL else cmd.Parameters.AddWithValue("PassTouches", 0); + if (prim.PassCollisions) + cmd.Parameters.AddWithValue("PassCollisions", 1); + else + cmd.Parameters.AddWithValue("PassCollisions", 0); + cmd.Parameters.AddWithValue("LinkNumber", prim.LinkNum); cmd.Parameters.AddWithValue("MediaURL", prim.MediaUrl); if (prim.AttachedPos != null) @@ -1668,6 +1857,16 @@ namespace OpenSim.Data.MySQL else cmd.Parameters.AddWithValue("KeyframeMotion", new Byte[0]); + if (prim.PhysicsInertia != null) + cmd.Parameters.AddWithValue("PhysInertia", prim.PhysicsInertia.ToXml2()); + else + cmd.Parameters.AddWithValue("PhysInertia", String.Empty); + + if (prim.VehicleParams != null) + cmd.Parameters.AddWithValue("Vehicle", prim.VehicleParams.ToXml2()); + else + cmd.Parameters.AddWithValue("Vehicle", String.Empty); + if (prim.DynAttrs.CountNamespaces > 0) cmd.Parameters.AddWithValue("DynAttrs", prim.DynAttrs.ToXml()); else @@ -1678,6 +1877,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("GravityModifier", (double)prim.GravityModifier); cmd.Parameters.AddWithValue("Friction", (double)prim.Friction); cmd.Parameters.AddWithValue("Restitution", (double)prim.Restitution); + cmd.Parameters.AddWithValue("RotationAxisLocks", prim.RotationAxisLocks); } /// @@ -1756,6 +1956,8 @@ 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("block_search", settings.GodBlockSearch); + cmd.Parameters.AddWithValue("casino", settings.Casino); cmd.Parameters.AddWithValue("ParcelImageID", settings.ParcelImageID); cmd.Parameters.AddWithValue("TelehubObject", settings.TelehubObject); @@ -1813,6 +2015,10 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("MediaLoop", land.MediaLoop); cmd.Parameters.AddWithValue("ObscureMusic", land.ObscureMusic); cmd.Parameters.AddWithValue("ObscureMedia", land.ObscureMedia); + cmd.Parameters.AddWithValue("SeeAVs", land.SeeAVs ? 1 : 0); + cmd.Parameters.AddWithValue("AnyAVSounds", land.AnyAVSounds ? 1 : 0); + cmd.Parameters.AddWithValue("GroupAVSounds", land.GroupAVSounds ? 1 : 0); + } /// @@ -1869,7 +2075,7 @@ namespace OpenSim.Data.MySQL 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"]); @@ -1919,7 +2125,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("Media", null == s.Media ? null : s.Media.ToXml()); } - public void StorePrimInventory(UUID primID, ICollection items) + public virtual void StorePrimInventory(UUID primID, ICollection items) { lock (m_dbLock) { @@ -1949,20 +2155,53 @@ namespace OpenSim.Data.MySQL "?flags, ?itemID, ?primID, ?assetID, " + "?parentFolderID, ?creatorID, ?ownerID, " + "?groupID, ?lastOwnerID)"; - + foreach (TaskInventoryItem item in items) { cmd.Parameters.Clear(); - + FillItemCommand(cmd, item); - + ExecuteNonQuery(cmd); } } + dbcon.Close(); } } } + public UUID[] GetObjectIDs(UUID regionID) + { + List uuids = new List(); + + lock (m_dbLock) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + + using (MySqlCommand cmd = dbcon.CreateCommand()) + { + cmd.CommandText = "select UUID from prims where RegionUUID = ?RegionUUID and SceneGroupID = UUID"; + cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString()); + + using (IDataReader reader = ExecuteReader(cmd)) + { + while (reader.Read()) + { + UUID id = new UUID(reader["UUID"].ToString()); + + uuids.Add(id); + } + } + } + dbcon.Close(); + } + } + + return uuids.ToArray(); + } + private void LoadSpawnPoints(RegionSettings rs) { rs.ClearSpawnPoints(); @@ -1992,6 +2231,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } } @@ -2026,6 +2266,7 @@ namespace OpenSim.Data.MySQL cmd.Parameters.Clear(); } } + dbcon.Close(); } } } @@ -2045,6 +2286,7 @@ namespace OpenSim.Data.MySQL cmd.ExecuteNonQuery(); } + dbcon.Close(); } } @@ -2062,6 +2304,7 @@ namespace OpenSim.Data.MySQL cmd.ExecuteNonQuery(); } + dbcon.Close(); } } @@ -2085,6 +2328,7 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } return ret; diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs index e964295..59cfe70 100644 --- a/OpenSim/Data/MySQL/MySQLUserAccountData.cs +++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs @@ -46,17 +46,21 @@ namespace OpenSim.Data.MySQL { string[] words = query.Split(new char[] {' '}); + bool valid = false; + for (int i = 0 ; i < words.Length ; i++) { - if (words[i].Length < 3) - { - if (i != words.Length - 1) - Array.Copy(words, i + 1, words, i, words.Length - i - 1); - Array.Resize(ref words, words.Length - 1); - } + if (words[i].Length > 2) + valid = true; +// if (words[i].Length < 3) +// { +// if (i != words.Length - 1) +// Array.Copy(words, i + 1, words, i, words.Length - i - 1); +// Array.Resize(ref words, words.Length - 1); +// } } - if (words.Length == 0) + if ((!valid) || words.Length == 0) return new UserAccountData[0]; if (words.Length > 2) @@ -66,13 +70,13 @@ namespace OpenSim.Data.MySQL { if (words.Length == 1) { - cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?search or LastName like ?search)", m_Realm); + cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?search or LastName like ?search) and active=1", m_Realm); cmd.Parameters.AddWithValue("?search", "%" + words[0] + "%"); cmd.Parameters.AddWithValue("?ScopeID", scopeID.ToString()); } else { - cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?searchFirst or LastName like ?searchLast)", m_Realm); + cmd.CommandText = String.Format("select * from {0} where (ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like ?searchFirst and LastName like ?searchLast) and active=1", m_Realm); cmd.Parameters.AddWithValue("?searchFirst", "%" + words[0] + "%"); cmd.Parameters.AddWithValue("?searchLast", "%" + words[1] + "%"); cmd.Parameters.AddWithValue("?ScopeID", scopeID.ToString()); @@ -81,5 +85,21 @@ namespace OpenSim.Data.MySQL return DoQuery(cmd); } } + + public UserAccountData[] GetUsersWhere(UUID scopeID, string where) + { + using (MySqlCommand cmd = new MySqlCommand()) + { + if (scopeID != UUID.Zero) + { + where = "(ScopeID=?ScopeID or ScopeID='00000000-0000-0000-0000-000000000000') and (" + where + ")"; + cmd.Parameters.AddWithValue("?ScopeID", scopeID.ToString()); + } + + cmd.CommandText = String.Format("select * from {0} where " + where, m_Realm); + + return DoQuery(cmd); + } + } } -} \ No newline at end of file +} diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs index b35595d..16637c3 100644 --- a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs +++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs @@ -40,39 +40,40 @@ 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(); + dbcon.Close(); } } #endregion Member Functions - + #region Classifieds Queries /// /// Gets the classified records. @@ -86,10 +87,10 @@ namespace OpenSim.Data.MySQL 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"; + const string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = ?Id"; dbcon.Open(); using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { @@ -102,7 +103,7 @@ namespace OpenSim.Data.MySQL { OSDMap n = new OSDMap(); UUID Id = UUID.Zero; - + string Name = null; try { @@ -111,8 +112,7 @@ namespace OpenSim.Data.MySQL } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": UserAccount exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA] GetClassifiedRecords exception {0}", e.Message); } n.Add("classifieduuid", OSD.FromUUID(Id)); n.Add("name", OSD.FromString(Name)); @@ -121,73 +121,73 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } 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"; - + const string query = + "INSERT INTO classifieds (" + + "`classifieduuid`," + + "`creatoruuid`," + + "`creationdate`," + + "`expirationdate`," + + "`category`," + + "`name`," + + "`description`," + + "`parceluuid`," + + "`parentestate`," + + "`snapshotuuid`," + + "`simname`," + + "`posglobal`," + + "`parcelname`," + + "`classifiedflags`," + + "`priceforlisting`) " + + "VALUES (" + + "?ClassifiedId," + + "?CreatorId," + + "?CreatedDate," + + "?ExpirationDate," + + "?Category," + + "?Name," + + "?Description," + + "?ParcelId," + + "?ParentEstate," + + "?SnapshotId," + + "?SimName," + + "?GlobalPos," + + "?ParcelName," + + "?Flags," + + "?ListingPrice ) " + + "ON DUPLICATE KEY UPDATE " + + "category=?Category, " + + "expirationdate=?ExpirationDate, " + + "name=?Name, " + + "description=?Description, " + + "parentestate=?ParentEstate, " + + "posglobal=?GlobalPos, " + + "parcelname=?ParcelName, " + + "classifiedflags=?Flags, " + + "priceforlisting=?ListingPrice, " + + "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); @@ -202,7 +202,7 @@ namespace OpenSim.Data.MySQL } ad.CreationDate = (int)epochnow.TotalSeconds; ad.ExpirationDate = (int)epochexp.TotalSeconds; - + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -225,57 +225,52 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?ParcelName", ad.ParcelName.ToString()); cmd.Parameters.AddWithValue("?Flags", ad.Flags.ToString()); cmd.Parameters.AddWithValue("?ListingPrice", ad.Price.ToString ()); - + cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": ClassifiedesUpdate exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: UpdateClassifiedRecord 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"; - + const string query = "DELETE FROM classifieds WHERE 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(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": DeleteClassifiedRecord exception {0}", e.Message); + 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"; - + + const string query = "SELECT * FROM classifieds WHERE classifieduuid = ?AdId"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -284,7 +279,7 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?AdId", ad.ClassifiedId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader()) { if(reader.Read ()) @@ -303,7 +298,7 @@ namespace OpenSim.Data.MySQL ad.SimName = reader.GetString("simname"); ad.GlobalPos = reader.GetString("posglobal"); ad.ParcelName = reader.GetString("parcelname"); - + } } } @@ -312,22 +307,19 @@ namespace OpenSim.Data.MySQL } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": GetPickInfo exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: GetClassifiedInfo 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"; + const string query = "SELECT `pickuuid`,`name` FROM userpicks WHERE creatoruuid = ?Id"; + OSDArray data = new OSDArray(); - + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -336,7 +328,7 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader()) { if(reader.HasRows) @@ -344,7 +336,7 @@ namespace OpenSim.Data.MySQL 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); @@ -352,25 +344,21 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": GetAvatarPicks exception {0}", e.Message); + 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"; - + const string query = "SELECT * FROM userpicks WHERE creatoruuid = ?CreatorId AND pickuuid = ?PickId"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -380,18 +368,18 @@ namespace OpenSim.Data.MySQL { 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); @@ -414,42 +402,41 @@ namespace OpenSim.Data.MySQL } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": GetPickInfo exception {0}", e.Message); + 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"; - + { + const string query = + "INSERT INTO userpicks VALUES (" + + "?PickId," + + "?CreatorId," + + "?TopPick," + + "?ParcelId," + + "?Name," + + "?Desc," + + "?SnapshotId," + + "?User," + + "?Original," + + "?SimName," + + "?GlobalPos," + + "?SortOrder," + + "?Enabled," + + "?Gatekeeper)" + + "ON DUPLICATE KEY UPDATE " + + "parceluuid=?ParcelId," + + "name=?Name," + + "description=?Desc," + + "user=?User," + + "simname=?SimName," + + "snapshotuuid=?SnapshotId," + + "pickuuid=?PickId," + + "posglobal=?GlobalPos," + + "gatekeeper=?Gatekeeper" + ; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -471,61 +458,53 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?Gatekeeper",pick.Gatekeeper); cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ()); cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString()); - + cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": UpdateAvatarNotes exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: UpdatePicksRecord 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"; - + string query = "DELETE FROM userpicks WHERE 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(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": DeleteUserPickRecord exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: DeletePicksRecord 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(); - + const string query = "SELECT `notes` FROM usernotes WHERE useruuid = ?Id AND targetuuid = ?TargetId"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -535,7 +514,7 @@ namespace OpenSim.Data.MySQL { cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString()); cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) @@ -549,40 +528,39 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": GetAvatarNotes exception {0}", e.Message); + 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; + { + string query; bool remove; - + if(string.IsNullOrEmpty(note.Notes)) { remove = true; - query += "DELETE FROM usernotes WHERE "; - query += "useruuid=?UserId AND "; - query += "targetuuid=?TargetId"; + query = "DELETE FROM usernotes WHERE useruuid=?UserId AND 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"; + query = "INSERT INTO usernotes VALUES (" + + "?UserId," + + "?TargetId," + + "?Notes )" + + "ON DUPLICATE KEY " + + "UPDATE " + + "notes=?Notes" + ; } - + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -594,30 +572,27 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?Notes", note.Notes); cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ()); cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString()); - + cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": UpdateAvatarNotes exception {0}", e.Message); + 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"; - + string query = "SELECT * FROM userprofile WHERE useruuid = ?Id"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -626,11 +601,13 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) { + m_log.DebugFormat("[PROFILES_DATA]" + + ": Getting data for {0}.", props.UserId); reader.Read(); props.WebUrl = (string)reader["profileURL"]; UUID.TryParse((string)reader["profileImage"], out props.ImageId); @@ -646,6 +623,9 @@ namespace OpenSim.Data.MySQL } else { + m_log.DebugFormat("[PROFILES_DATA]" + + ": No data for {0}", props.UserId); + props.WebUrl = string.Empty; props.ImageId = UUID.Zero; props.AboutText = string.Empty; @@ -660,35 +640,36 @@ namespace OpenSim.Data.MySQL 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)"; + query = "INSERT INTO userprofile (" + + "useruuid, " + + "profilePartner, " + + "profileAllowPublish, " + + "profileMaturePublish, " + + "profileURL, " + + "profileWantToMask, " + + "profileWantToText, " + + "profileSkillsMask, " + + "profileSkillsText, " + + "profileLanguages, " + + "profileImage, " + + "profileAboutText, " + + "profileFirstImage, " + + "profileFirstText) VALUES (" + + "?userId, " + + "?profilePartner, " + + "?profileAllowPublish, " + + "?profileMaturePublish, " + + "?profileURL, " + + "?profileWantToMask, " + + "?profileWantToText, " + + "?profileSkillsMask, " + + "?profileSkillsText, " + + "?profileLanguages, " + + "?profileImage, " + + "?profileAboutText, " + + "?profileFirstImage, " + + "?profileFirstText)" + ; dbcon.Close(); dbcon.Open(); @@ -715,30 +696,25 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": Requst properties exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: GetAvatarProperties 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"; - + { + const string query = "UPDATE userprofile SET profileURL=?profileURL," + + "profileImage=?image, profileAboutText=?abouttext," + + "profileFirstImage=?firstlifeimage, profileFirstText=?firstlifetext " + + "WHERE useruuid=?uuid"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -752,35 +728,33 @@ namespace OpenSim.Data.MySQL cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString()); cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText); cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString()); - + cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": AgentPropertiesUpdate exception {0}", e.Message); - + m_log.ErrorFormat("[PROFILES_DATA]: UpdateAvatarProperties 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"; - + { + const string query = "UPDATE userprofile SET " + + "profileWantToMask=?WantMask, " + + "profileWantToText=?WantText," + + "profileSkillsMask=?SkillsMask," + + "profileSkillsText=?SkillsText, " + + "profileLanguages=?Languages " + + "WHERE useruuid=?uuid"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -794,15 +768,14 @@ namespace OpenSim.Data.MySQL 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); + m_log.ErrorFormat("[PROFILES_DATA]: UpdateAvatarInterests exception {0}", e.Message); result = e.Message; return false; } @@ -813,21 +786,20 @@ namespace OpenSim.Data.MySQL public OSDArray GetUserImageAssets(UUID avatarId) { OSDArray data = new OSDArray(); - string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id"; + const string queryA = "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)) + using (MySqlCommand cmd = new MySqlCommand(string.Format (queryA,"`classifieds`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) @@ -843,10 +815,10 @@ namespace OpenSim.Data.MySQL dbcon.Close(); dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) + using (MySqlCommand cmd = new MySqlCommand(string.Format (queryA,"`userpicks`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) @@ -858,16 +830,16 @@ namespace OpenSim.Data.MySQL } } } - + dbcon.Close(); dbcon.Open(); - query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id"; + const string queryB = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id"; - using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon)) + using (MySqlCommand cmd = new MySqlCommand(string.Format (queryB,"`userpicks`"), dbcon)) { cmd.Parameters.AddWithValue("?Id", avatarId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) @@ -880,27 +852,21 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": GetAvatarNotes exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: GetUserImageAssets 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(); - + const string query = "SELECT imviaemail,visible,email FROM usersettings WHERE useruuid = ?Id"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -909,10 +875,9 @@ namespace OpenSim.Data.MySQL using (MySqlCommand cmd = new MySqlCommand(query, dbcon)) { cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString()); - using (MySqlDataReader reader = cmd.ExecuteReader()) { - if(reader.HasRows) + if (reader.HasRows) { reader.Read(); bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail); @@ -923,13 +888,12 @@ namespace OpenSim.Data.MySQL { dbcon.Close(); dbcon.Open(); - - query = "INSERT INTO usersettings VALUES "; - query += "(?uuid,'false','false', ?Email)"; - using (MySqlCommand put = new MySqlCommand(query, dbcon)) + const string queryB = "INSERT INTO usersettings VALUES (?uuid,'false','false', ?Email)"; + + using (MySqlCommand put = new MySqlCommand(queryB, dbcon)) { - + put.Parameters.AddWithValue("?Email", pref.EMail); put.Parameters.AddWithValue("?uuid", pref.UserId.ToString()); @@ -938,28 +902,24 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": Get preferences exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: GetUserPreferences 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"; - + { + const string query = "UPDATE usersettings SET imviaemail=?ImViaEmail," + + "visible=?Visible, email=?EMail " + + "WHERE useruuid=?uuid"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -974,28 +934,24 @@ namespace OpenSim.Data.MySQL cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": UserPreferencesUpdate exception {0} {1}", e.Message, e.InnerException); + m_log.ErrorFormat("[PROFILES_DATA]: UpdateUserPreferences 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"; - + const string query = "SELECT * FROM `userdata` WHERE UserId = ?Id AND TagId = ?TagId"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -1005,7 +961,7 @@ namespace OpenSim.Data.MySQL { cmd.Parameters.AddWithValue("?Id", props.UserId.ToString()); cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString()); - + using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if(reader.HasRows) @@ -1016,13 +972,8 @@ namespace OpenSim.Data.MySQL } else { - query += "INSERT INTO userdata VALUES ( "; - query += "?UserId,"; - query += "?TagId,"; - query += "?DataKey,"; - query += "?DataVal) "; - - using (MySqlCommand put = new MySqlCommand(query, dbcon)) + const string queryB = "INSERT INTO userdata VALUES (?UserId, ?TagId, ?DataKey, ?DataVal)"; + using (MySqlCommand put = new MySqlCommand(queryB, dbcon)) { put.Parameters.AddWithValue("?UserId", props.UserId.ToString()); put.Parameters.AddWithValue("?TagId", props.TagId.ToString()); @@ -1034,12 +985,12 @@ namespace OpenSim.Data.MySQL } } } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": Requst application data exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: GetUserAppData exception {0}", e.Message); result = e.Message; return false; } @@ -1047,16 +998,9 @@ namespace OpenSim.Data.MySQL } 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"; - + { + const string query = "UPDATE userdata SET TagId = ?TagId, DataKey = ?DataKey, DataVal = ?DataVal WHERE UserId = ?UserId AND TagId = ?TagId"; + try { using (MySqlConnection dbcon = new MySqlConnection(ConnectionString)) @@ -1071,12 +1015,12 @@ namespace OpenSim.Data.MySQL cmd.ExecuteNonQuery(); } + dbcon.Close(); } } catch (Exception e) { - m_log.ErrorFormat("[PROFILES_DATA]" + - ": SetUserData exception {0}", e.Message); + m_log.ErrorFormat("[PROFILES_DATA]: SetUserAppData exception {0}", e.Message); return false; } return true; diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs index af7e876..9f9c9cf 100644 --- a/OpenSim/Data/MySQL/MySQLXAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLXAssetData.cs @@ -97,6 +97,7 @@ namespace OpenSim.Data.MySQL dbcon.Open(); Migration m = new Migration(dbcon, Assembly, "XAssetStore"); m.Update(); + dbcon.Close(); } } @@ -130,6 +131,7 @@ namespace OpenSim.Data.MySQL // m_log.DebugFormat("[MYSQL XASSET DATA]: Looking for asset {0}", assetID); AssetBase asset = null; + int accessTime = 0; using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { @@ -140,7 +142,6 @@ namespace OpenSim.Data.MySQL dbcon)) { cmd.Parameters.AddWithValue("?ID", assetID.ToString()); - try { using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) @@ -159,23 +160,7 @@ namespace OpenSim.Data.MySQL asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]); asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); - - if (m_enableCompression) - { - 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); - } - } - - UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]); + accessTime = (int)dbReader["AccessTime"]; } } } @@ -184,9 +169,38 @@ namespace OpenSim.Data.MySQL m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e); } } + dbcon.Close(); } - return asset; + if(asset == null) + return asset; + + if(accessTime > 0) + { + try + { + UpdateAccessTime(asset.Metadata, accessTime); + } + catch { } + } + + if (m_enableCompression && asset.Data != null) + { + using(MemoryStream ms = new MemoryStream(asset.Data)) + using(GZipStream decompressionStream = new GZipStream(ms, CompressionMode.Decompress)) + { + using(MemoryStream outputStream = new MemoryStream()) + { + decompressionStream.CopyTo(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); + } + } + return asset; } /// @@ -209,7 +223,7 @@ namespace OpenSim.Data.MySQL { 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", + "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", asset.Name, asset.ID, asset.Name.Length, assetName.Length); } @@ -218,7 +232,7 @@ namespace OpenSim.Data.MySQL { 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", + "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); } @@ -303,6 +317,7 @@ namespace OpenSim.Data.MySQL transaction.Commit(); } + dbcon.Close(); } } @@ -341,9 +356,10 @@ namespace OpenSim.Data.MySQL catch (Exception) { m_log.ErrorFormat( - "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}", + "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}", assetMetadata.ID, assetMetadata.Name); } + dbcon.Close(); } } @@ -440,38 +456,41 @@ namespace OpenSim.Data.MySQL using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { 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 + using(MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count",dbcon)) { - using (MySqlDataReader dbReader = cmd.ExecuteReader()) + cmd.Parameters.AddWithValue("?start",start); + cmd.Parameters.AddWithValue("?count", count); + + try { - while (dbReader.Read()) + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - 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(); + 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["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"]); + // We'll ignore this for now - it appears unused! + // metadata.SHA1 = dbReader["hash"]); - UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); + UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); - retList.Add(metadata); + 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()); - } + dbcon.Close(); } return retList; @@ -490,9 +509,9 @@ namespace OpenSim.Data.MySQL 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 (?) + dbcon.Close(); } return true; @@ -500,4 +519,4 @@ namespace OpenSim.Data.MySQL #endregion } -} \ No newline at end of file +} diff --git a/OpenSim/Data/MySQL/MySQLXInventoryData.cs b/OpenSim/Data/MySQL/MySQLXInventoryData.cs index c74033e..5019994 100644 --- a/OpenSim/Data/MySQL/MySQLXInventoryData.cs +++ b/OpenSim/Data/MySQL/MySQLXInventoryData.cs @@ -80,7 +80,7 @@ namespace OpenSim.Data.MySQL return m_Items.Store(item); } - + public bool DeleteFolders(string field, string val) { return m_Folders.Delete(field, val); @@ -193,7 +193,9 @@ namespace OpenSim.Data.MySQL { using (MySqlCommand cmd = new MySqlCommand()) { - cmd.CommandText = String.Format("select * from inventoryitems where avatarId = ?uuid and assetType = ?type and flags & 1", m_Realm); +// cmd.CommandText = String.Format("select * from inventoryitems where avatarId = ?uuid and assetType = ?type and flags & 1", m_Realm); + + cmd.CommandText = String.Format("select * from inventoryitems where avatarId = ?uuid and assetType = ?type and flags & 1"); cmd.Parameters.AddWithValue("?uuid", principalID.ToString()); cmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture); @@ -212,15 +214,18 @@ namespace OpenSim.Data.MySQL { cmd.Connection = dbcon; - cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = ?PrincipalID and assetID = ?AssetID group by assetID", m_Realm); +// cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = ?PrincipalID and assetID = ?AssetID group by assetID", m_Realm); + + cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = ?PrincipalID and assetID = ?AssetID group by assetID"); + cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString()); cmd.Parameters.AddWithValue("?AssetID", assetID.ToString()); - + using (IDataReader reader = cmd.ExecuteReader()) { int perms = 0; - + if (reader.Read()) { perms = Convert.ToInt32(reader["inventoryCurrentPermissions"]); @@ -323,7 +328,6 @@ namespace OpenSim.Data.MySQL { return false; } - cmd.Dispose(); } dbcon.Close(); diff --git a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs index b46d175..6507a37 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.8.2.*")] +[assembly : AssemblyVersion(OpenSim.VersionInfo.AssemblyVersionNumber)] diff --git a/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations index e76db07..e496f72 100644 --- a/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations +++ b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations @@ -13,6 +13,6 @@ CREATE TABLE `AgentPrefs` ( `PermNextOwner` INT(6) NOT NULL DEFAULT 532480, UNIQUE KEY `PrincipalID` (`PrincipalID`), PRIMARY KEY(`PrincipalID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/AssetStore.migrations b/OpenSim/Data/MySQL/Resources/AssetStore.migrations index 52715fd..820799d 100644 --- a/OpenSim/Data/MySQL/Resources/AssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AssetStore.migrations @@ -1,81 +1,21 @@ # ----------------- -:VERSION 1 +:VERSION 10 BEGIN; -CREATE TABLE `assets` ( - `id` binary(16) NOT NULL, +CREATE TABLE IF NOT EXISTS `assets` ( `name` varchar(64) NOT NULL, `description` varchar(64) NOT NULL, `assetType` tinyint(4) NOT NULL, - `invType` tinyint(4) NOT NULL, `local` tinyint(1) NOT NULL, `temporary` tinyint(1) NOT NULL, `data` longblob NOT NULL, + `id` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `create_time` int(11) DEFAULT '0', + `access_time` int(11) DEFAULT '0', + `asset_flags` int(11) NOT NULL DEFAULT '0', + `CreatorID` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; - -# ----------------- -:VERSION 2 - -BEGIN; - -ALTER TABLE assets change id oldid binary(16); -ALTER TABLE assets add id varchar(36) not null default ''; -UPDATE assets set id = concat(substr(hex(oldid),1,8),"-",substr(hex(oldid),9,4),"-",substr(hex(oldid),13,4),"-",substr(hex(oldid),17,4),"-",substr(hex(oldid),21,12)); -ALTER TABLE assets drop oldid; -ALTER TABLE assets add constraint primary key(id); - -COMMIT; - -# ----------------- -:VERSION 3 - -BEGIN; - -ALTER TABLE assets change id oldid varchar(36); -ALTER TABLE assets add id char(36) not null default '00000000-0000-0000-0000-000000000000'; -UPDATE assets set id = oldid; -ALTER TABLE assets drop oldid; -ALTER TABLE assets add constraint primary key(id); - -COMMIT; - -# ----------------- -:VERSION 4 - -BEGIN; - -ALTER TABLE assets drop InvType; - -COMMIT; - -# ----------------- -:VERSION 5 - -BEGIN; - -ALTER TABLE assets add create_time integer default 0; -ALTER TABLE assets add access_time integer default 0; - -COMMIT; - -# ----------------- -:VERSION 6 - -DELETE FROM assets WHERE id = 'dc4b9f0b-d008-45c6-96a4-01dd947ac621' - -:VERSION 7 - -ALTER TABLE assets ADD COLUMN asset_flags INTEGER NOT NULL DEFAULT 0; - -:VERSION 8 - -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..f00979f 100644 --- a/OpenSim/Data/MySQL/Resources/AuthStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AuthStore.migrations @@ -1,16 +1,17 @@ -:VERSION 1 # ------------------------------- +:VERSION 4 # ------------------------------- begin; -CREATE TABLE `auth` ( +CREATE TABLE IF NOT EXISTS `auth` ( `UUID` char(36) NOT NULL, - `passwordHash` char(32) NOT NULL default '', - `passwordSalt` char(32) NOT NULL default '', - `webLoginKey` varchar(255) NOT NULL default '', - PRIMARY KEY (`UUID`) -) ENGINE=MyISAM; - -CREATE TABLE `tokens` ( + `passwordHash` char(32) NOT NULL DEFAULT '', + `passwordSalt` char(32) NOT NULL DEFAULT '', + `webLoginKey` varchar(255) NOT NULL DEFAULT '', + `accountType` varchar(32) NOT NULL DEFAULT 'UserAccount', + PRIMARY KEY (`UUID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `tokens` ( `UUID` char(36) NOT NULL, `token` varchar(255) NOT NULL, `validity` datetime NOT NULL, @@ -18,22 +19,6 @@ CREATE TABLE `tokens` ( KEY `UUID` (`UUID`), KEY `token` (`token`), KEY `validity` (`validity`) -) ENGINE=MyISAM; - -commit; - -:VERSION 2 # ------------------------------- - -BEGIN; - -INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey) SELECT `UUID` AS UUID, `passwordHash` AS passwordHash, `passwordSalt` AS passwordSalt, `webLoginKey` AS webLoginKey FROM users; - -COMMIT; - -:VERSION 3 # ------------------------------- - -BEGIN; - -ALTER TABLE `auth` ADD COLUMN `accountType` VARCHAR(32) NOT NULL DEFAULT 'UserAccount'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/Avatar.migrations b/OpenSim/Data/MySQL/Resources/Avatar.migrations index f7cf176..c5ec9ca 100644 --- a/OpenSim/Data/MySQL/Resources/Avatar.migrations +++ b/OpenSim/Data/MySQL/Resources/Avatar.migrations @@ -1,20 +1,13 @@ -:VERSION 1 +:VERSION 3 BEGIN; -CREATE TABLE Avatars ( - PrincipalID CHAR(36) NOT NULL, - Name VARCHAR(32) NOT NULL, - Value VARCHAR(255) NOT NULL DEFAULT '', - PRIMARY KEY(PrincipalID, Name), - KEY(PrincipalID)); - -COMMIT; - -:VERSION 2 - -BEGIN; - -alter table Avatars change column Value Value text; +CREATE TABLE IF NOT EXISTS `Avatars` ( + `PrincipalID` char(36) NOT NULL, + `Name` varchar(32) NOT NULL, + `Value` text, + PRIMARY KEY (`PrincipalID`,`Name`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/EstateStore.migrations b/OpenSim/Data/MySQL/Resources/EstateStore.migrations index 9dfb77b..615af95 100644 --- a/OpenSim/Data/MySQL/Resources/EstateStore.migrations +++ b/OpenSim/Data/MySQL/Resources/EstateStore.migrations @@ -1,41 +1,29 @@ -:VERSION 13 - -# The estate migrations used to be in Region store -# here they will do nothing (bad) if the tables are already there, -# just update the store version. +:VERSION 34 BEGIN; -CREATE TABLE IF NOT EXISTS `estate_managers` ( - `EstateID` int(10) unsigned NOT NULL, - `uuid` char(36) NOT NULL, - KEY `EstateID` (`EstateID`) -) ENGINE=MyISAM; - 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 DEFAULT CHARSET=utf8; -CREATE TABLE IF NOT EXISTS `estate_users` ( +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 DEFAULT CHARSET=utf8; -CREATE TABLE IF NOT EXISTS `estateban` ( - `EstateID` int(10) unsigned NOT NULL, - `bannedUUID` varchar(36) NOT NULL, - `bannedIp` varchar(16) NOT NULL, - `bannedIpHostMask` varchar(16) NOT NULL, - `bannedNameMask` varchar(64) default NULL, - KEY `estateban_EstateID` (`EstateID`) -) ENGINE=MyISAM; +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=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `estate_settings` ( - `EstateID` int(10) unsigned NOT NULL auto_increment, - `EstateName` varchar(64) default NULL, + `EstateID` int(10) unsigned NOT NULL AUTO_INCREMENT, + `EstateName` varchar(64) DEFAULT NULL, `AbuseEmailToEstateOwner` tinyint(4) NOT NULL, `DenyAnonymous` tinyint(4) NOT NULL, `ResetHomeOnTeleport` tinyint(4) NOT NULL, @@ -55,33 +43,29 @@ CREATE TABLE IF NOT EXISTS `estate_settings` ( `EstateSkipScripts` tinyint(4) NOT NULL, `BillableFactor` float NOT NULL, `PublicAccess` tinyint(4) NOT NULL, - `AbuseEmail` varchar(255) not null, - `EstateOwner` varchar(36) not null, - `DenyMinors` tinyint not null, - - PRIMARY KEY (`EstateID`) -) ENGINE=MyISAM AUTO_INCREMENT=100; + `AbuseEmail` varchar(255) NOT NULL, + `EstateOwner` varchar(36) NOT NULL, + `DenyMinors` tinyint(4) NOT NULL, + `AllowLandmark` tinyint(4) NOT NULL DEFAULT '1', + `AllowParcelChanges` tinyint(4) NOT NULL DEFAULT '1', + `AllowSetHome` tinyint(4) NOT NULL DEFAULT '1', + PRIMARY KEY (`EstateID`) +) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8; -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`), +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 DEFAULT CHARSET=utf8; -COMMIT; - -:VERSION 32 #--------------------- (moved from RegionStore migr, just in case) - -BEGIN; -ALTER TABLE estate_settings AUTO_INCREMENT = 100; -COMMIT; - -:VERSION 33 #--------------------- +CREATE TABLE IF NOT EXISTS `estateban` ( + `EstateID` int(10) unsigned NOT NULL, + `bannedUUID` varchar(36) NOT NULL, + `bannedIp` varchar(16) NOT NULL, + `bannedIpHostMask` varchar(16) NOT NULL, + `bannedNameMask` varchar(64) DEFAULT NULL, + KEY `estateban_EstateID` (`EstateID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -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 index 4194af3..87d08c6 100644 --- a/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations @@ -13,6 +13,6 @@ CREATE TABLE `fsassets` ( `access_time` int(11) NOT NULL DEFAULT '0', `asset_flags` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +) 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 5de0e35..6840f07 100644 --- a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations +++ b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations @@ -1,32 +1,14 @@ -:VERSION 1 # ------------------------- +:VERSION 4 # ------------------------- BEGIN; -CREATE TABLE `Friends` ( - `PrincipalID` CHAR(36) NOT NULL, - `Friend` VARCHAR(255) NOT NULL, - `Flags` VARCHAR(16) NOT NULL DEFAULT 0, - `Offered` VARCHAR(32) NOT NULL DEFAULT 0, - PRIMARY KEY(`PrincipalID`, `Friend`), - KEY(`PrincipalID`) -) ENGINE=MyISAM; - -COMMIT; - -:VERSION 2 # ------------------------- - -BEGIN; - -INSERT INTO `Friends` SELECT `ownerID`, `friendID`, `friendPerms`, 0 FROM `userfriends`; - -COMMIT; - -:VERSION 3 # ------------------------- - -BEGIN; - -ALTER TABLE `Friends` MODIFY COLUMN PrincipalID varchar(255) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; -ALTER TABLE `Friends` DROP PRIMARY KEY; -ALTER TABLE `Friends` ADD PRIMARY KEY(PrincipalID(36), Friend(36)); +CREATE TABLE IF NOT EXISTS `Friends` ( + `PrincipalID` varchar(255) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `Friend` varchar(255) NOT NULL, + `Flags` varchar(16) NOT NULL DEFAULT '0', + `Offered` varchar(32) NOT NULL DEFAULT '0', + PRIMARY KEY (`PrincipalID`(36),`Friend`(36)), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/GridStore.migrations b/OpenSim/Data/MySQL/Resources/GridStore.migrations index 52ced24..e4c8fc3 100644 --- a/OpenSim/Data/MySQL/Resources/GridStore.migrations +++ b/OpenSim/Data/MySQL/Resources/GridStore.migrations @@ -1,109 +1,52 @@ -:VERSION 1 +:VERSION 10 BEGIN; -CREATE TABLE `regions` ( +CREATE TABLE IF NOT EXISTS `regions` ( `uuid` varchar(36) NOT NULL, `regionHandle` bigint(20) unsigned NOT NULL, - `regionName` varchar(32) default NULL, - `regionRecvKey` varchar(128) default NULL, - `regionSendKey` varchar(128) default NULL, - `regionSecret` varchar(128) default NULL, - `regionDataURI` varchar(255) default NULL, - `serverIP` varchar(64) default NULL, - `serverPort` int(10) unsigned default NULL, - `serverURI` varchar(255) default NULL, - `locX` int(10) unsigned default NULL, - `locY` int(10) unsigned default NULL, - `locZ` int(10) unsigned default NULL, - `eastOverrideHandle` bigint(20) unsigned default NULL, - `westOverrideHandle` bigint(20) unsigned default NULL, - `southOverrideHandle` bigint(20) unsigned default NULL, - `northOverrideHandle` bigint(20) unsigned default NULL, - `regionAssetURI` varchar(255) default NULL, - `regionAssetRecvKey` varchar(128) default NULL, - `regionAssetSendKey` varchar(128) default NULL, - `regionUserURI` varchar(255) default NULL, - `regionUserRecvKey` varchar(128) default NULL, - `regionUserSendKey` varchar(128) default NULL, `regionMapTexture` varchar(36) default NULL, - `serverHttpPort` int(10) default NULL, `serverRemotingPort` int(10) default NULL, - `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null, - `originUUID` varchar(36), - PRIMARY KEY (`uuid`), + `regionName` varchar(128) DEFAULT NULL, + `regionRecvKey` varchar(128) DEFAULT NULL, + `regionSendKey` varchar(128) DEFAULT NULL, + `regionSecret` varchar(128) DEFAULT NULL, + `regionDataURI` varchar(255) DEFAULT NULL, + `serverIP` varchar(64) DEFAULT NULL, + `serverPort` int(10) unsigned DEFAULT NULL, + `serverURI` varchar(255) DEFAULT NULL, + `locX` int(10) unsigned DEFAULT NULL, + `locY` int(10) unsigned DEFAULT NULL, + `locZ` int(10) unsigned DEFAULT NULL, + `eastOverrideHandle` bigint(20) unsigned DEFAULT NULL, + `westOverrideHandle` bigint(20) unsigned DEFAULT NULL, + `southOverrideHandle` bigint(20) unsigned DEFAULT NULL, + `northOverrideHandle` bigint(20) unsigned DEFAULT NULL, + `regionAssetURI` varchar(255) DEFAULT NULL, + `regionAssetRecvKey` varchar(128) DEFAULT NULL, + `regionAssetSendKey` varchar(128) DEFAULT NULL, + `regionUserURI` varchar(255) DEFAULT NULL, + `regionUserRecvKey` varchar(128) DEFAULT NULL, + `regionUserSendKey` varchar(128) DEFAULT NULL, + `regionMapTexture` varchar(36) DEFAULT NULL, + `serverHttpPort` int(10) DEFAULT NULL, + `serverRemotingPort` int(10) DEFAULT NULL, + `owner_uuid` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `originUUID` varchar(36) DEFAULT NULL, + `access` int(10) unsigned DEFAULT '1', + `ScopeID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `sizeX` int(11) NOT NULL DEFAULT '0', + `sizeY` int(11) NOT NULL DEFAULT '0', + `flags` int(11) NOT NULL DEFAULT '0', + `last_seen` int(11) NOT NULL DEFAULT '0', + `PrincipalID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `Token` varchar(255) NOT NULL, + `parcelMapTexture` varchar(36) DEFAULT NULL, + PRIMARY KEY (`uuid`), KEY `regionName` (`regionName`), KEY `regionHandle` (`regionHandle`), - KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -COMMIT; - -:VERSION 2 - -BEGIN; - -ALTER TABLE regions add column access integer unsigned default 1; - -COMMIT; - -:VERSION 3 - -BEGIN; - -ALTER TABLE regions add column ScopeID char(36) not null default '00000000-0000-0000-0000-000000000000'; - -create index ScopeID on regions(ScopeID); - -COMMIT; - -:VERSION 4 - -BEGIN; - -ALTER TABLE regions add column sizeX integer not null default 0; -ALTER TABLE regions add column sizeY integer not null default 0; - -COMMIT; - -:VERSION 5 - -BEGIN; - -ALTER TABLE `regions` ADD COLUMN `flags` integer NOT NULL DEFAULT 0; -CREATE INDEX flags ON regions(flags); - -COMMIT; - -:VERSION 6 - -BEGIN; - -ALTER TABLE `regions` ADD COLUMN `last_seen` integer NOT NULL DEFAULT 0; - -COMMIT; - -:VERSION 7 - -BEGIN; - -ALTER TABLE `regions` ADD COLUMN `PrincipalID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; -ALTER TABLE `regions` ADD COLUMN `Token` varchar(255) NOT NULL; - -COMMIT; - - -:VERSION 8 # ------------ - -BEGIN; - -alter table regions modify column regionName varchar(128) default NULL; - -COMMIT; - -:VERSION 9 # ------------ - -BEGIN; - -alter table regions add column `parcelMapTexture` varchar(36) default NULL; + KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`), + KEY `ScopeID` (`ScopeID`), + KEY `flags` (`flags`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; 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 index 06ca29d..b4e4422 100644 --- a/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations +++ b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations @@ -12,7 +12,7 @@ CREATE TABLE `hg_traveling_data` ( `TMStamp` timestamp NOT NULL, PRIMARY KEY (`SessionID`), KEY (`UserID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/IM_Store.migrations b/OpenSim/Data/MySQL/Resources/IM_Store.migrations index 79ead98..4f14826 100644 --- a/OpenSim/Data/MySQL/Resources/IM_Store.migrations +++ b/OpenSim/Data/MySQL/Resources/IM_Store.migrations @@ -1,42 +1,16 @@ -:VERSION 1 # -------------------------- +:VERSION 5 # -------------------------- BEGIN; -CREATE TABLE `im_offline` ( - `ID` MEDIUMINT NOT NULL AUTO_INCREMENT, - `PrincipalID` char(36) NOT NULL default '', +CREATE TABLE IF NOT EXISTS `im_offline` ( + `ID` mediumint(9) NOT NULL AUTO_INCREMENT, + `PrincipalID` char(36) NOT NULL DEFAULT '', + `FromID` 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; + `TMStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`ID`), + KEY `PrincipalID` (`PrincipalID`), + KEY `FromID` (`FromID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations index ca2fe11..2d4384c 100644 --- a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations +++ b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations @@ -1,109 +1,42 @@ -:VERSION 1 # ------------ +:VERSION 7 # ------------ BEGIN; -CREATE TABLE `inventoryfolders` ( - `folderID` varchar(36) NOT NULL default '', - `agentID` varchar(36) default NULL, - `parentFolderID` varchar(36) default NULL, - `folderName` varchar(64) default NULL, - `type` smallint NOT NULL default 0, - `version` int NOT NULL default 0, - PRIMARY KEY (`folderID`), - KEY `owner` (`agentID`), - KEY `parent` (`parentFolderID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE `inventoryitems` ( - `inventoryID` varchar(36) NOT NULL default '', - `assetID` varchar(36) default NULL, - `assetType` int(11) default NULL, - `parentFolderID` varchar(36) default NULL, - `avatarID` varchar(36) default NULL, - `inventoryName` varchar(64) default NULL, - `inventoryDescription` varchar(128) default NULL, - `inventoryNextPermissions` int(10) unsigned default NULL, - `inventoryCurrentPermissions` int(10) unsigned default NULL, - `invType` int(11) default NULL, - `creatorID` varchar(36) default NULL, - `inventoryBasePermissions` int(10) unsigned NOT NULL default 0, - `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0, - `salePrice` int(11) NOT NULL default 0, - `saleType` tinyint(4) NOT NULL default 0, - `creationDate` int(11) NOT NULL default 0, - `groupID` varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000', - `groupOwned` tinyint(4) NOT NULL default 0, - `flags` int(11) unsigned NOT NULL default 0, - PRIMARY KEY (`inventoryID`), - KEY `owner` (`avatarID`), - KEY `folder` (`parentFolderID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -COMMIT; - -:VERSION 2 # ------------ - -BEGIN; - -ALTER TABLE inventoryfolders change folderID folderIDold varchar(36); -ALTER TABLE inventoryfolders change agentID agentIDold varchar(36); -ALTER TABLE inventoryfolders change parentFolderID parentFolderIDold varchar(36); -ALTER TABLE inventoryfolders add folderID char(36) not null default '00000000-0000-0000-0000-000000000000'; -ALTER TABLE inventoryfolders add agentID char(36) default NULL; -ALTER TABLE inventoryfolders add parentFolderID char(36) default NULL; -UPDATE inventoryfolders set folderID = folderIDold, agentID = agentIDold, parentFolderID = parentFolderIDold; -ALTER TABLE inventoryfolders drop folderIDold; -ALTER TABLE inventoryfolders drop agentIDold; -ALTER TABLE inventoryfolders drop parentFolderIDold; -ALTER TABLE inventoryfolders add constraint primary key(folderID); -ALTER TABLE inventoryfolders add index inventoryfolders_agentid(agentID); -ALTER TABLE inventoryfolders add index inventoryfolders_parentFolderid(parentFolderID); - -ALTER TABLE inventoryitems change inventoryID inventoryIDold varchar(36); -ALTER TABLE inventoryitems change avatarID avatarIDold varchar(36); -ALTER TABLE inventoryitems change parentFolderID parentFolderIDold varchar(36); -ALTER TABLE inventoryitems add inventoryID char(36) not null default '00000000-0000-0000-0000-000000000000'; -ALTER TABLE inventoryitems add avatarID char(36) default NULL; -ALTER TABLE inventoryitems add parentFolderID char(36) default NULL; -UPDATE inventoryitems set inventoryID = inventoryIDold, avatarID = avatarIDold, parentFolderID = parentFolderIDold; -ALTER TABLE inventoryitems drop inventoryIDold; -ALTER TABLE inventoryitems drop avatarIDold; -ALTER TABLE inventoryitems drop parentFolderIDold; -ALTER TABLE inventoryitems add constraint primary key(inventoryID); -ALTER TABLE inventoryitems add index inventoryitems_avatarid(avatarID); -ALTER TABLE inventoryitems add index inventoryitems_parentFolderid(parentFolderID); - -COMMIT; - -:VERSION 3 # ------------ - -BEGIN; - -alter table inventoryitems add column inventoryGroupPermissions integer unsigned not null default 0; - -COMMIT; - -:VERSION 4 # ------------ - -BEGIN; - -update inventoryitems set creatorID = '00000000-0000-0000-0000-000000000000' where creatorID is NULL; -update inventoryitems set creatorID = '00000000-0000-0000-0000-000000000000' where creatorID = ''; -alter table inventoryitems modify column creatorID varchar(36) not NULL default '00000000-0000-0000-0000-000000000000'; - -COMMIT; - -:VERSION 5 # ------------ - -BEGIN; - -alter table inventoryitems modify column creatorID varchar(128) not NULL default '00000000-0000-0000-0000-000000000000'; - -COMMIT; - -:VERSION 6 # ------------ - -BEGIN; - -alter table inventoryitems modify column creatorID varchar(255) not NULL default '00000000-0000-0000-0000-000000000000'; +CREATE TABLE IF NOT EXISTS `inventoryitems` ( + `assetID` varchar(36) DEFAULT NULL, + `assetType` int(11) DEFAULT NULL, + `inventoryName` varchar(64) DEFAULT NULL, + `inventoryDescription` varchar(128) DEFAULT NULL, + `inventoryNextPermissions` int(10) unsigned DEFAULT NULL, + `inventoryCurrentPermissions` int(10) unsigned DEFAULT NULL, + `invType` int(11) DEFAULT NULL, + `creatorID` varchar(255) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `inventoryBasePermissions` int(10) unsigned NOT NULL DEFAULT '0', + `inventoryEveryOnePermissions` int(10) unsigned NOT NULL DEFAULT '0', + `salePrice` int(11) NOT NULL DEFAULT '0', + `saleType` tinyint(4) NOT NULL DEFAULT '0', + `creationDate` int(11) NOT NULL DEFAULT '0', + `groupID` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `groupOwned` tinyint(4) NOT NULL DEFAULT '0', + `flags` int(11) unsigned NOT NULL DEFAULT '0', + `inventoryID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `avatarID` char(36) DEFAULT NULL, + `parentFolderID` char(36) DEFAULT NULL, + `inventoryGroupPermissions` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`inventoryID`), + KEY `inventoryitems_avatarid` (`avatarID`), + KEY `inventoryitems_parentFolderid` (`parentFolderID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `inventoryfolders` ( + `folderName` varchar(64) DEFAULT NULL, + `type` smallint(6) NOT NULL DEFAULT '0', + `version` int(11) NOT NULL DEFAULT '0', + `folderID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `agentID` char(36) DEFAULT NULL, + `parentFolderID` char(36) DEFAULT NULL, + PRIMARY KEY (`folderID`), + KEY `inventoryfolders_agentid` (`agentID`), + KEY `inventoryfolders_parentFolderid` (`parentFolderID`) +) 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/MuteListStore.migrations b/OpenSim/Data/MySQL/Resources/MuteListStore.migrations new file mode 100644 index 0000000..5bde63e --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/MuteListStore.migrations @@ -0,0 +1,16 @@ +:VERSION 1 + +BEGIN; + +CREATE TABLE `MuteList` ( + `AgentID` char(36) NOT NULL, + `MuteID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `MuteName` varchar(64) NOT NULL DEFAULT '', + `MuteType` int(11) NOT NULL DEFAULT '1', + `MuteFlags` int(11) NOT NULL DEFAULT '0', + `Stamp` int(11) NOT NULL, + UNIQUE KEY `AgentID_2` (`AgentID`,`MuteID`,`MuteName`), + KEY `AgentID` (`AgentID`) +); + +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/Presence.migrations b/OpenSim/Data/MySQL/Resources/Presence.migrations index 6dc9384..50aa756 100644 --- a/OpenSim/Data/MySQL/Resources/Presence.migrations +++ b/OpenSim/Data/MySQL/Resources/Presence.migrations @@ -1,31 +1,16 @@ -:VERSION 1 # -------------------------- +:VERSION 4 # -------------------------- BEGIN; -CREATE TABLE `Presence` ( - `UserID` VARCHAR(255) NOT NULL, - `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; - -CREATE UNIQUE INDEX SessionID ON Presence(SessionID); -CREATE INDEX UserID ON Presence(UserID); - -COMMIT; - -:VERSION 2 # -------------------------- - -BEGIN; - -ALTER TABLE `Presence` ADD COLUMN LastSeen timestamp; - -COMMIT; - -:VERSION 3 # -------------------------- - -BEGIN; - -CREATE INDEX RegionID ON Presence(RegionID); +CREATE TABLE IF NOT EXISTS `Presence` ( + `UserID` varchar(255) NOT NULL, + `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', + `LastSeen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `SessionID` (`SessionID`), + KEY `UserID` (`UserID`), + KEY `RegionID` (`RegionID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/RegionStore.migrations b/OpenSim/Data/MySQL/Resources/RegionStore.migrations index 834d249..0577392 100644 --- a/OpenSim/Data/MySQL/Resources/RegionStore.migrations +++ b/OpenSim/Data/MySQL/Resources/RegionStore.migrations @@ -1,354 +1,226 @@ -:VERSION 1 #--------------------- - -BEGIN; - -CREATE TABLE `prims` ( - `UUID` varchar(255) NOT NULL, - `RegionUUID` varchar(255) default NULL, - `ParentID` int(11) default NULL, - `CreationDate` int(11) default NULL, - `Name` varchar(255) default NULL, - `SceneGroupID` varchar(255) default NULL, - `Text` varchar(255) default NULL, - `Description` varchar(255) default NULL, - `SitName` varchar(255) default NULL, - `TouchName` varchar(255) default NULL, - `ObjectFlags` int(11) default NULL, - `CreatorID` varchar(255) default NULL, - `OwnerID` varchar(255) default NULL, - `GroupID` varchar(255) default NULL, - `LastOwnerID` varchar(255) default NULL, - `OwnerMask` int(11) default NULL, - `NextOwnerMask` int(11) default NULL, - `GroupMask` int(11) default NULL, - `EveryoneMask` int(11) default NULL, - `BaseMask` int(11) default NULL, - `PositionX` float default NULL, - `PositionY` float default NULL, - `PositionZ` float default NULL, - `GroupPositionX` float default NULL, - `GroupPositionY` float default NULL, - `GroupPositionZ` float default NULL, - `VelocityX` float default NULL, - `VelocityY` float default NULL, - `VelocityZ` float default NULL, - `AngularVelocityX` float default NULL, - `AngularVelocityY` float default NULL, - `AngularVelocityZ` float default NULL, - `AccelerationX` float default NULL, - `AccelerationY` float default NULL, - `AccelerationZ` float default NULL, - `RotationX` float default NULL, - `RotationY` float default NULL, - `RotationZ` float default NULL, - `RotationW` float default NULL, - `SitTargetOffsetX` float default NULL, - `SitTargetOffsetY` float default NULL, - `SitTargetOffsetZ` float default NULL, - `SitTargetOrientW` float default NULL, - `SitTargetOrientX` float default NULL, - `SitTargetOrientY` float default NULL, - `SitTargetOrientZ` float default NULL, - PRIMARY KEY (`UUID`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - -CREATE TABLE `primshapes` ( - `UUID` varchar(255) NOT NULL, - `Shape` int(11) default NULL, - `ScaleX` float default NULL, - `ScaleY` float default NULL, - `ScaleZ` float default NULL, - `PCode` int(11) default NULL, - `PathBegin` int(11) default NULL, - `PathEnd` int(11) default NULL, - `PathScaleX` int(11) default NULL, - `PathScaleY` int(11) default NULL, - `PathShearX` int(11) default NULL, - `PathShearY` int(11) default NULL, - `PathSkew` int(11) default NULL, - `PathCurve` int(11) default NULL, - `PathRadiusOffset` int(11) default NULL, - `PathRevolutions` int(11) default NULL, - `PathTaperX` int(11) default NULL, - `PathTaperY` int(11) default NULL, - `PathTwist` int(11) default NULL, - `PathTwistBegin` int(11) default NULL, - `ProfileBegin` int(11) default NULL, - `ProfileEnd` int(11) default NULL, - `ProfileCurve` int(11) default NULL, - `ProfileHollow` int(11) default NULL, - `State` int(11) default NULL, +:VERSION 51 #--------------------- + +BEGIN; + +CREATE TABLE IF NOT EXISTS `prims` ( + `CreationDate` int(11) DEFAULT NULL, + `Name` varchar(255) DEFAULT NULL, + `Text` varchar(255) DEFAULT NULL, + `Description` varchar(255) DEFAULT NULL, + `SitName` varchar(255) DEFAULT NULL, + `TouchName` varchar(255) DEFAULT NULL, + `ObjectFlags` int(11) DEFAULT NULL, + `OwnerMask` int(11) DEFAULT NULL, + `NextOwnerMask` int(11) DEFAULT NULL, + `GroupMask` int(11) DEFAULT NULL, + `EveryoneMask` int(11) DEFAULT NULL, + `BaseMask` int(11) DEFAULT NULL, + `PositionX` double DEFAULT NULL, + `PositionY` double DEFAULT NULL, + `PositionZ` double DEFAULT NULL, + `GroupPositionX` double DEFAULT NULL, + `GroupPositionY` double DEFAULT NULL, + `GroupPositionZ` double DEFAULT NULL, + `VelocityX` double DEFAULT NULL, + `VelocityY` double DEFAULT NULL, + `VelocityZ` double DEFAULT NULL, + `AngularVelocityX` double DEFAULT NULL, + `AngularVelocityY` double DEFAULT NULL, + `AngularVelocityZ` double DEFAULT NULL, + `AccelerationX` double DEFAULT NULL, + `AccelerationY` double DEFAULT NULL, + `AccelerationZ` double DEFAULT NULL, + `RotationX` double DEFAULT NULL, + `RotationY` double DEFAULT NULL, + `RotationZ` double DEFAULT NULL, + `RotationW` double DEFAULT NULL, + `SitTargetOffsetX` double DEFAULT NULL, + `SitTargetOffsetY` double DEFAULT NULL, + `SitTargetOffsetZ` double DEFAULT NULL, + `SitTargetOrientW` double DEFAULT NULL, + `SitTargetOrientX` double DEFAULT NULL, + `SitTargetOrientY` double DEFAULT NULL, + `SitTargetOrientZ` double DEFAULT NULL, + `UUID` char(36) NOT NULL DEFAULT '', + `RegionUUID` char(36) DEFAULT NULL, + `CreatorID` varchar(255) NOT NULL DEFAULT '', + `OwnerID` char(36) DEFAULT NULL, + `GroupID` char(36) DEFAULT NULL, + `LastOwnerID` char(36) DEFAULT NULL, + `SceneGroupID` char(36) DEFAULT NULL, + `PayPrice` int(11) NOT NULL DEFAULT '0', + `PayButton1` int(11) NOT NULL DEFAULT '0', + `PayButton2` int(11) NOT NULL DEFAULT '0', + `PayButton3` int(11) NOT NULL DEFAULT '0', + `PayButton4` int(11) NOT NULL DEFAULT '0', + `LoopedSound` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `LoopedSoundGain` double NOT NULL DEFAULT '0', + `TextureAnimation` blob, + `OmegaX` double NOT NULL DEFAULT '0', + `OmegaY` double NOT NULL DEFAULT '0', + `OmegaZ` double NOT NULL DEFAULT '0', + `CameraEyeOffsetX` double NOT NULL DEFAULT '0', + `CameraEyeOffsetY` double NOT NULL DEFAULT '0', + `CameraEyeOffsetZ` double NOT NULL DEFAULT '0', + `CameraAtOffsetX` double NOT NULL DEFAULT '0', + `CameraAtOffsetY` double NOT NULL DEFAULT '0', + `CameraAtOffsetZ` double NOT NULL DEFAULT '0', + `ForceMouselook` tinyint(4) NOT NULL DEFAULT '0', + `ScriptAccessPin` int(11) NOT NULL DEFAULT '0', + `AllowedDrop` tinyint(4) NOT NULL DEFAULT '0', + `DieAtEdge` tinyint(4) NOT NULL DEFAULT '0', + `SalePrice` int(11) NOT NULL DEFAULT '10', + `SaleType` tinyint(4) NOT NULL DEFAULT '0', + `ColorR` int(11) NOT NULL DEFAULT '0', + `ColorG` int(11) NOT NULL DEFAULT '0', + `ColorB` int(11) NOT NULL DEFAULT '0', + `ColorA` int(11) NOT NULL DEFAULT '0', + `ParticleSystem` blob, + `ClickAction` tinyint(4) NOT NULL DEFAULT '0', + `Material` tinyint(4) NOT NULL DEFAULT '3', + `CollisionSound` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `CollisionSoundVolume` double NOT NULL DEFAULT '0', + `LinkNumber` int(11) NOT NULL DEFAULT '0', + `PassTouches` tinyint(4) NOT NULL DEFAULT '0', + `MediaURL` varchar(255) DEFAULT NULL, + `DynAttrs` text, + `PhysicsShapeType` tinyint(4) NOT NULL DEFAULT '0', + `Density` double NOT NULL DEFAULT '1000', + `GravityModifier` double NOT NULL DEFAULT '1', + `Friction` double NOT NULL DEFAULT '0.6', + `Restitution` double NOT NULL DEFAULT '0.5', + `KeyframeMotion` blob, + `AttachedPosX` double DEFAULT '0', + `AttachedPosY` double DEFAULT '0', + `AttachedPosZ` double DEFAULT '0', + PRIMARY KEY (`UUID`), + KEY `prims_regionuuid` (`RegionUUID`), + KEY `prims_scenegroupid` (`SceneGroupID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `primshapes` ( + `Shape` int(11) DEFAULT NULL, + `ScaleX` double NOT NULL DEFAULT '0', + `ScaleY` double NOT NULL DEFAULT '0', + `ScaleZ` double NOT NULL DEFAULT '0', + `PCode` int(11) DEFAULT NULL, + `PathBegin` int(11) DEFAULT NULL, + `PathEnd` int(11) DEFAULT NULL, + `PathScaleX` int(11) DEFAULT NULL, + `PathScaleY` int(11) DEFAULT NULL, + `PathShearX` int(11) DEFAULT NULL, + `PathShearY` int(11) DEFAULT NULL, + `PathSkew` int(11) DEFAULT NULL, + `PathCurve` int(11) DEFAULT NULL, + `PathRadiusOffset` int(11) DEFAULT NULL, + `PathRevolutions` int(11) DEFAULT NULL, + `PathTaperX` int(11) DEFAULT NULL, + `PathTaperY` int(11) DEFAULT NULL, + `PathTwist` int(11) DEFAULT NULL, + `PathTwistBegin` int(11) DEFAULT NULL, + `ProfileBegin` int(11) DEFAULT NULL, + `ProfileEnd` int(11) DEFAULT NULL, + `ProfileCurve` int(11) DEFAULT NULL, + `ProfileHollow` int(11) DEFAULT NULL, + `State` int(11) DEFAULT NULL, `Texture` longblob, `ExtraParams` longblob, - PRIMARY KEY (`UUID`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - -CREATE TABLE `primitems` ( - `itemID` varchar(255) NOT NULL, - `primID` varchar(255) default NULL, - `assetID` varchar(255) default NULL, - `parentFolderID` varchar(255) default NULL, - `invType` int(11) default NULL, - `assetType` int(11) default NULL, - `name` varchar(255) default NULL, - `description` varchar(255) default NULL, - `creationDate` bigint(20) default NULL, - `creatorID` varchar(255) default NULL, - `ownerID` varchar(255) default NULL, - `lastOwnerID` varchar(255) default NULL, - `groupID` varchar(255) default NULL, - `nextPermissions` int(11) default NULL, - `currentPermissions` int(11) default NULL, - `basePermissions` int(11) default NULL, - `everyonePermissions` int(11) default NULL, - `groupPermissions` int(11) default NULL, - PRIMARY KEY (`itemID`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - -CREATE TABLE `terrain` ( - `RegionUUID` varchar(255) default NULL, - `Revision` int(11) default NULL, + `UUID` char(36) NOT NULL DEFAULT '', + `Media` text, + `LastAttachPoint` int(4) NOT NULL DEFAULT '0', + PRIMARY KEY (`UUID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `primitems` ( + `invType` int(11) DEFAULT NULL, + `assetType` int(11) DEFAULT NULL, + `name` varchar(255) DEFAULT NULL, + `description` varchar(255) DEFAULT NULL, + `creationDate` bigint(20) DEFAULT NULL, + `nextPermissions` int(11) DEFAULT NULL, + `currentPermissions` int(11) DEFAULT NULL, + `basePermissions` int(11) DEFAULT NULL, + `everyonePermissions` int(11) DEFAULT NULL, + `groupPermissions` int(11) DEFAULT NULL, + `flags` int(11) NOT NULL DEFAULT '0', + `itemID` char(36) NOT NULL DEFAULT '', + `primID` char(36) DEFAULT NULL, + `assetID` char(36) DEFAULT NULL, + `parentFolderID` char(36) DEFAULT NULL, + `CreatorID` varchar(255) NOT NULL DEFAULT '', + `ownerID` char(36) DEFAULT NULL, + `groupID` char(36) DEFAULT NULL, + `lastOwnerID` char(36) DEFAULT NULL, + PRIMARY KEY (`itemID`), + KEY `primitems_primid` (`primID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `terrain` ( + `RegionUUID` varchar(255) DEFAULT NULL, + `Revision` int(11) DEFAULT NULL, `Heightfield` longblob -) ENGINE=MyISAM DEFAULT CHARSET=latin1; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; -CREATE TABLE `land` ( +CREATE TABLE IF NOT EXISTS `land` ( `UUID` varchar(255) NOT NULL, - `RegionUUID` varchar(255) default NULL, - `LocalLandID` int(11) default NULL, + `RegionUUID` varchar(255) DEFAULT NULL, + `LocalLandID` int(11) DEFAULT NULL, `Bitmap` longblob, - `Name` varchar(255) default NULL, - `Description` varchar(255) default NULL, - `OwnerUUID` varchar(255) default NULL, - `IsGroupOwned` int(11) default NULL, - `Area` int(11) default NULL, - `AuctionID` int(11) default NULL, - `Category` int(11) default NULL, - `ClaimDate` int(11) default NULL, - `ClaimPrice` int(11) default NULL, - `GroupUUID` varchar(255) default NULL, - `SalePrice` int(11) default NULL, - `LandStatus` int(11) default NULL, - `LandFlags` int(11) default NULL, - `LandingType` int(11) default NULL, - `MediaAutoScale` int(11) default NULL, - `MediaTextureUUID` varchar(255) default NULL, - `MediaURL` varchar(255) default NULL, - `MusicURL` varchar(255) default NULL, - `PassHours` float default NULL, - `PassPrice` int(11) default NULL, - `SnapshotUUID` varchar(255) default NULL, - `UserLocationX` float default NULL, - `UserLocationY` float default NULL, - `UserLocationZ` float default NULL, - `UserLookAtX` float default NULL, - `UserLookAtY` float default NULL, - `UserLookAtZ` float default NULL, - `AuthbuyerID` varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000', - PRIMARY KEY (`UUID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -CREATE TABLE `landaccesslist` ( - `LandUUID` varchar(255) default NULL, - `AccessUUID` varchar(255) default NULL, - `Flags` int(11) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - -COMMIT; - -:VERSION 2 #--------------------- - -BEGIN; - -CREATE index prims_regionuuid on prims(RegionUUID); -CREATE index primitems_primid on primitems(primID); - -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'; -COMMIT; - -:VERSION 4 #--------------------- - -BEGIN; - -ALTER TABLE primitems add flags integer not null default 0; - -COMMIT; - -:VERSION 5 #--------------------- -BEGIN; - -create table regionsettings ( - regionUUID char(36) not null, - block_terraform integer not null, - block_fly integer not null, - allow_damage integer not null, - restrict_pushing integer not null, - allow_land_resell integer not null, - allow_land_join_divide integer not null, - block_show_in_search integer not null, - agent_limit integer not null, - object_bonus float not null, - maturity integer not null, - disable_scripts integer not null, - disable_collisions integer not null, - disable_physics integer not null, - terrain_texture_1 char(36) not null, - terrain_texture_2 char(36) not null, - terrain_texture_3 char(36) not null, - terrain_texture_4 char(36) not null, - elevation_1_nw float not null, - elevation_2_nw float not null, - elevation_1_ne float not null, - elevation_2_ne float not null, - elevation_1_se float not null, - elevation_2_se float not null, - elevation_1_sw float not null, - elevation_2_sw float not null, - water_height float not null, - terrain_raise_limit float not null, - terrain_lower_limit float not null, - use_estate_sun integer not null, - fixed_sun integer not null, - sun_position float not null, - covenant char(36), - primary key(regionUUID) -); - -COMMIT; - - -:VERSION 6 #--------------------- - -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; - -COMMIT; - -:VERSION 7 #--------------------- - -BEGIN; - -ALTER TABLE prims change UUID UUIDold varchar(255); -ALTER TABLE prims change RegionUUID RegionUUIDold varchar(255); -ALTER TABLE prims change CreatorID CreatorIDold varchar(255); -ALTER TABLE prims change OwnerID OwnerIDold varchar(255); -ALTER TABLE prims change GroupID GroupIDold varchar(255); -ALTER TABLE prims change LastOwnerID LastOwnerIDold varchar(255); -ALTER TABLE prims add UUID char(36); -ALTER TABLE prims add RegionUUID char(36); -ALTER TABLE prims add CreatorID char(36); -ALTER TABLE prims add OwnerID char(36); -ALTER TABLE prims add GroupID char(36); -ALTER TABLE prims add LastOwnerID char(36); -UPDATE prims set UUID = UUIDold, RegionUUID = RegionUUIDold, CreatorID = CreatorIDold, OwnerID = OwnerIDold, GroupID = GroupIDold, LastOwnerID = LastOwnerIDold; -ALTER TABLE prims drop UUIDold; -ALTER TABLE prims drop RegionUUIDold; -ALTER TABLE prims drop CreatorIDold; -ALTER TABLE prims drop OwnerIDold; -ALTER TABLE prims drop GroupIDold; -ALTER TABLE prims drop LastOwnerIDold; -ALTER TABLE prims add constraint primary key(UUID); -ALTER TABLE prims add index prims_regionuuid(RegionUUID); - -COMMIT; - -:VERSION 8 #--------------------- - -BEGIN; - -ALTER TABLE primshapes change UUID UUIDold varchar(255); -ALTER TABLE primshapes add UUID char(36); -UPDATE primshapes set UUID = UUIDold; -ALTER TABLE primshapes drop UUIDold; -ALTER TABLE primshapes add constraint primary key(UUID); - -COMMIT; - -:VERSION 9 #--------------------- - -BEGIN; - -ALTER TABLE primitems change itemID itemIDold varchar(255); -ALTER TABLE primitems change primID primIDold varchar(255); -ALTER TABLE primitems change assetID assetIDold varchar(255); -ALTER TABLE primitems change parentFolderID parentFolderIDold varchar(255); -ALTER TABLE primitems change creatorID creatorIDold varchar(255); -ALTER TABLE primitems change ownerID ownerIDold varchar(255); -ALTER TABLE primitems change groupID groupIDold varchar(255); -ALTER TABLE primitems change lastOwnerID lastOwnerIDold varchar(255); -ALTER TABLE primitems add itemID char(36); -ALTER TABLE primitems add primID char(36); -ALTER TABLE primitems add assetID char(36); -ALTER TABLE primitems add parentFolderID char(36); -ALTER TABLE primitems add creatorID char(36); -ALTER TABLE primitems add ownerID char(36); -ALTER TABLE primitems add groupID char(36); -ALTER TABLE primitems add lastOwnerID char(36); -UPDATE primitems set itemID = itemIDold, primID = primIDold, assetID = assetIDold, parentFolderID = parentFolderIDold, creatorID = creatorIDold, ownerID = ownerIDold, groupID = groupIDold, lastOwnerID = lastOwnerIDold; -ALTER TABLE primitems drop itemIDold; -ALTER TABLE primitems drop primIDold; -ALTER TABLE primitems drop assetIDold; -ALTER TABLE primitems drop parentFolderIDold; -ALTER TABLE primitems drop creatorIDold; -ALTER TABLE primitems drop ownerIDold; -ALTER TABLE primitems drop groupIDold; -ALTER TABLE primitems drop lastOwnerIDold; -ALTER TABLE primitems add constraint primary key(itemID); -ALTER TABLE primitems add index primitems_primid(primID); - -COMMIT; - -:VERSION 10 #--------------------- - -# 1 "010_RegionStore.sql" -# 1 "" -# 1 "" -# 1 "010_RegionStore.sql" -BEGIN; - -DELETE FROM regionsettings; - -COMMIT; - - -:VERSION 11 #--------------------- - -BEGIN; - -ALTER TABLE prims change SceneGroupID SceneGroupIDold varchar(255); -ALTER TABLE prims add SceneGroupID char(36); -UPDATE prims set SceneGroupID = SceneGroupIDold; -ALTER TABLE prims drop SceneGroupIDold; -ALTER TABLE prims add index prims_scenegroupid(SceneGroupID); - -COMMIT; - -:VERSION 12 #--------------------- - -BEGIN; - -ALTER TABLE prims add index prims_parentid(ParentID); - -COMMIT; - -:VERSION 13 #--------------------- -begin; - -drop table regionsettings; - -CREATE TABLE `regionsettings` ( + `Name` varchar(255) DEFAULT NULL, + `Description` varchar(255) DEFAULT NULL, + `OwnerUUID` varchar(255) DEFAULT NULL, + `IsGroupOwned` int(11) DEFAULT NULL, + `Area` int(11) DEFAULT NULL, + `AuctionID` int(11) DEFAULT NULL, + `Category` int(11) DEFAULT NULL, + `ClaimDate` int(11) DEFAULT NULL, + `ClaimPrice` int(11) DEFAULT NULL, + `GroupUUID` varchar(255) DEFAULT NULL, + `SalePrice` int(11) DEFAULT NULL, + `LandStatus` int(11) DEFAULT NULL, + `LandFlags` int(10) unsigned DEFAULT NULL, + `LandingType` int(11) DEFAULT NULL, + `MediaAutoScale` int(11) DEFAULT NULL, + `MediaTextureUUID` varchar(255) DEFAULT NULL, + `MediaURL` varchar(255) DEFAULT NULL, + `MusicURL` varchar(255) DEFAULT NULL, + `PassHours` float DEFAULT NULL, + `PassPrice` int(11) DEFAULT NULL, + `SnapshotUUID` varchar(255) DEFAULT NULL, + `UserLocationX` float DEFAULT NULL, + `UserLocationY` float DEFAULT NULL, + `UserLocationZ` float DEFAULT NULL, + `UserLookAtX` float DEFAULT NULL, + `UserLookAtY` float DEFAULT NULL, + `UserLookAtZ` float DEFAULT NULL, + `AuthbuyerID` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `OtherCleanTime` int(11) NOT NULL DEFAULT '0', + `Dwell` int(11) NOT NULL DEFAULT '0', + `MediaType` varchar(32) NOT NULL DEFAULT 'none/none', + `MediaDescription` varchar(255) NOT NULL DEFAULT '', + `MediaSize` varchar(16) NOT NULL DEFAULT '0,0', + `MediaLoop` tinyint(1) NOT NULL DEFAULT '0', + `ObscureMusic` tinyint(1) NOT NULL DEFAULT '0', + `ObscureMedia` tinyint(1) NOT NULL DEFAULT '0', + PRIMARY KEY (`UUID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `landaccesslist` ( + `LandUUID` varchar(255) DEFAULT NULL, + `AccessUUID` varchar(255) DEFAULT NULL, + `Flags` int(11) DEFAULT NULL, + `Expires` int(11) NOT NULL DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `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; + +CREATE TABLE IF NOT EXISTS `regionsettings` ( `regionUUID` char(36) NOT NULL, `block_terraform` int(11) NOT NULL, `block_fly` int(11) NOT NULL, @@ -358,7 +230,7 @@ CREATE TABLE `regionsettings` ( `allow_land_join_divide` int(11) NOT NULL, `block_show_in_search` int(11) NOT NULL, `agent_limit` int(11) NOT NULL, - `object_bonus` float NOT NULL, + `object_bonus` double NOT NULL, `maturity` int(11) NOT NULL, `disable_scripts` int(11) NOT NULL, `disable_collisions` int(11) NOT NULL, @@ -367,360 +239,35 @@ CREATE TABLE `regionsettings` ( `terrain_texture_2` char(36) NOT NULL, `terrain_texture_3` char(36) NOT NULL, `terrain_texture_4` char(36) NOT NULL, - `elevation_1_nw` float NOT NULL, - `elevation_2_nw` float NOT NULL, - `elevation_1_ne` float NOT NULL, - `elevation_2_ne` float NOT NULL, - `elevation_1_se` float NOT NULL, - `elevation_2_se` float NOT NULL, - `elevation_1_sw` float NOT NULL, - `elevation_2_sw` float NOT NULL, - `water_height` float NOT NULL, - `terrain_raise_limit` float NOT NULL, - `terrain_lower_limit` float NOT NULL, + `elevation_1_nw` double NOT NULL, + `elevation_2_nw` double NOT NULL, + `elevation_1_ne` double NOT NULL, + `elevation_2_ne` double NOT NULL, + `elevation_1_se` double NOT NULL, + `elevation_2_se` double NOT NULL, + `elevation_1_sw` double NOT NULL, + `elevation_2_sw` double NOT NULL, + `water_height` double NOT NULL, + `terrain_raise_limit` double NOT NULL, + `terrain_lower_limit` double NOT NULL, `use_estate_sun` int(11) NOT NULL, `fixed_sun` int(11) NOT NULL, - `sun_position` float NOT NULL, - `covenant` char(36) default NULL, + `sun_position` double NOT NULL, + `covenant` char(36) DEFAULT NULL, `Sandbox` tinyint(4) NOT NULL, - PRIMARY KEY (`regionUUID`) -) ENGINE=MyISAM; - -commit; - -:VERSION 16 #--------------------- - -BEGIN; - -ALTER TABLE prims ADD COLUMN PayPrice integer not null default 0; -ALTER TABLE prims ADD COLUMN PayButton1 integer not null default 0; -ALTER TABLE prims ADD COLUMN PayButton2 integer not null default 0; -ALTER TABLE prims ADD COLUMN PayButton3 integer not null default 0; -ALTER TABLE prims ADD COLUMN PayButton4 integer not null default 0; -ALTER TABLE prims ADD COLUMN LoopedSound char(36) not null default '00000000-0000-0000-0000-000000000000'; -ALTER TABLE prims ADD COLUMN LoopedSoundGain float not null default 0.0; -ALTER TABLE prims ADD COLUMN TextureAnimation blob; -ALTER TABLE prims ADD COLUMN OmegaX float not null default 0.0; -ALTER TABLE prims ADD COLUMN OmegaY float not null default 0.0; -ALTER TABLE prims ADD COLUMN OmegaZ float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraEyeOffsetX float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraEyeOffsetY float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraEyeOffsetZ float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraAtOffsetX float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraAtOffsetY float not null default 0.0; -ALTER TABLE prims ADD COLUMN CameraAtOffsetZ float not null default 0.0; -ALTER TABLE prims ADD COLUMN ForceMouselook tinyint not null default 0; -ALTER TABLE prims ADD COLUMN ScriptAccessPin integer not null default 0; -ALTER TABLE prims ADD COLUMN AllowedDrop tinyint not null default 0; -ALTER TABLE prims ADD COLUMN DieAtEdge tinyint not null default 0; -ALTER TABLE prims ADD COLUMN SalePrice integer not null default 10; -ALTER TABLE prims ADD COLUMN SaleType tinyint not null default 0; - -COMMIT; - - -:VERSION 17 #--------------------- - -BEGIN; - -ALTER TABLE prims ADD COLUMN ColorR integer not null default 0; -ALTER TABLE prims ADD COLUMN ColorG integer not null default 0; -ALTER TABLE prims ADD COLUMN ColorB integer not null default 0; -ALTER TABLE prims ADD COLUMN ColorA integer not null default 0; -ALTER TABLE prims ADD COLUMN ParticleSystem blob; - -COMMIT; - - -:VERSION 18 #--------------------- - -begin; - -ALTER TABLE prims ADD COLUMN ClickAction tinyint NOT NULL default 0; - -commit; - -:VERSION 19 #--------------------- - -begin; - -ALTER TABLE prims ADD COLUMN Material tinyint NOT NULL default 3; - -commit; - - -:VERSION 20 #--------------------- - -begin; - -ALTER TABLE land ADD COLUMN OtherCleanTime integer NOT NULL default 0; -ALTER TABLE land ADD COLUMN Dwell integer NOT NULL default 0; - -commit; - -:VERSION 21 #--------------------- - -begin; - -ALTER TABLE regionsettings ADD COLUMN sunvectorx double NOT NULL default 0; -ALTER TABLE regionsettings ADD COLUMN sunvectory double NOT NULL default 0; -ALTER TABLE regionsettings ADD COLUMN sunvectorz double NOT NULL default 0; - -commit; - - -:VERSION 22 #--------------------- - -BEGIN; - -ALTER TABLE prims ADD COLUMN CollisionSound char(36) not null default '00000000-0000-0000-0000-000000000000'; -ALTER TABLE prims ADD COLUMN CollisionSoundVolume float not null default 0.0; - -COMMIT; - -:VERSION 23 #--------------------- - -BEGIN; - -ALTER TABLE prims ADD COLUMN LinkNumber integer not null default 0; - -COMMIT; - -:VERSION 24 #--------------------- - -BEGIN; - -alter table regionsettings change column `object_bonus` `object_bonus` double NOT NULL; -alter table regionsettings change column `elevation_1_nw` `elevation_1_nw` double NOT NULL; -alter table regionsettings change column `elevation_2_nw` `elevation_2_nw` double NOT NULL; -alter table regionsettings change column `elevation_1_ne` `elevation_1_ne` double NOT NULL; -alter table regionsettings change column `elevation_2_ne` `elevation_2_ne` double NOT NULL; -alter table regionsettings change column `elevation_1_se` `elevation_1_se` double NOT NULL; -alter table regionsettings change column `elevation_2_se` `elevation_2_se` double NOT NULL; -alter table regionsettings change column `elevation_1_sw` `elevation_1_sw` double NOT NULL; -alter table regionsettings change column `elevation_2_sw` `elevation_2_sw` double NOT NULL; -alter table regionsettings change column `water_height` `water_height` double NOT NULL; -alter table regionsettings change column `terrain_raise_limit` `terrain_raise_limit` double NOT NULL; -alter table regionsettings change column `terrain_lower_limit` `terrain_lower_limit` double NOT NULL; -alter table regionsettings change column `sun_position` `sun_position` double NOT NULL; - -COMMIT; - - -:VERSION 25 #--------------------- - -BEGIN; - -alter table prims change column `PositionX` `PositionX` double default NULL; -alter table prims change column `PositionY` `PositionY` double default NULL; -alter table prims change column `PositionZ` `PositionZ` double default NULL; -alter table prims change column `GroupPositionX` `GroupPositionX` double default NULL; -alter table prims change column `GroupPositionY` `GroupPositionY` double default NULL; -alter table prims change column `GroupPositionZ` `GroupPositionZ` double default NULL; -alter table prims change column `VelocityX` `VelocityX` double default NULL; -alter table prims change column `VelocityY` `VelocityY` double default NULL; -alter table prims change column `VelocityZ` `VelocityZ` double default NULL; -alter table prims change column `AngularVelocityX` `AngularVelocityX` double default NULL; -alter table prims change column `AngularVelocityY` `AngularVelocityY` double default NULL; -alter table prims change column `AngularVelocityZ` `AngularVelocityZ` double default NULL; -alter table prims change column `AccelerationX` `AccelerationX` double default NULL; -alter table prims change column `AccelerationY` `AccelerationY` double default NULL; -alter table prims change column `AccelerationZ` `AccelerationZ` double default NULL; -alter table prims change column `RotationX` `RotationX` double default NULL; -alter table prims change column `RotationY` `RotationY` double default NULL; -alter table prims change column `RotationZ` `RotationZ` double default NULL; -alter table prims change column `RotationW` `RotationW` double default NULL; -alter table prims change column `SitTargetOffsetX` `SitTargetOffsetX` double default NULL; -alter table prims change column `SitTargetOffsetY` `SitTargetOffsetY` double default NULL; -alter table prims change column `SitTargetOffsetZ` `SitTargetOffsetZ` double default NULL; -alter table prims change column `SitTargetOrientW` `SitTargetOrientW` double default NULL; -alter table prims change column `SitTargetOrientX` `SitTargetOrientX` double default NULL; -alter table prims change column `SitTargetOrientY` `SitTargetOrientY` double default NULL; -alter table prims change column `SitTargetOrientZ` `SitTargetOrientZ` double default NULL; -alter table prims change column `LoopedSoundGain` `LoopedSoundGain` double NOT NULL default '0'; -alter table prims change column `OmegaX` `OmegaX` double NOT NULL default '0'; -alter table prims change column `OmegaY` `OmegaY` double NOT NULL default '0'; -alter table prims change column `OmegaZ` `OmegaZ` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetX` `CameraEyeOffsetX` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetY` `CameraEyeOffsetY` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetZ` `CameraEyeOffsetZ` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetX` `CameraAtOffsetX` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetY` `CameraAtOffsetY` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetZ` `CameraAtOffsetZ` double NOT NULL default '0'; -alter table prims change column `CollisionSoundVolume` `CollisionSoundVolume` double NOT NULL default '0'; - -alter table primshapes change column `ScaleX` `ScaleX` double NOT NULL default '0'; -alter table primshapes change column `ScaleY` `ScaleY` double NOT NULL default '0'; -alter table primshapes change column `ScaleZ` `ScaleZ` double NOT NULL default '0'; - -COMMIT; - -:VERSION 26 #--------------------- - -begin; - -alter table prims change column `PositionX` `PositionX` double default NULL; -alter table prims change column `PositionY` `PositionY` double default NULL; -alter table prims change column `PositionZ` `PositionZ` double default NULL; -alter table prims change column `GroupPositionX` `GroupPositionX` double default NULL; -alter table prims change column `GroupPositionY` `GroupPositionY` double default NULL; -alter table prims change column `GroupPositionZ` `GroupPositionZ` double default NULL; -alter table prims change column `VelocityX` `VelocityX` double default NULL; -alter table prims change column `VelocityY` `VelocityY` double default NULL; -alter table prims change column `VelocityZ` `VelocityZ` double default NULL; -alter table prims change column `AngularVelocityX` `AngularVelocityX` double default NULL; -alter table prims change column `AngularVelocityY` `AngularVelocityY` double default NULL; -alter table prims change column `AngularVelocityZ` `AngularVelocityZ` double default NULL; -alter table prims change column `AccelerationX` `AccelerationX` double default NULL; -alter table prims change column `AccelerationY` `AccelerationY` double default NULL; -alter table prims change column `AccelerationZ` `AccelerationZ` double default NULL; -alter table prims change column `RotationX` `RotationX` double default NULL; -alter table prims change column `RotationY` `RotationY` double default NULL; -alter table prims change column `RotationZ` `RotationZ` double default NULL; -alter table prims change column `RotationW` `RotationW` double default NULL; -alter table prims change column `SitTargetOffsetX` `SitTargetOffsetX` double default NULL; -alter table prims change column `SitTargetOffsetY` `SitTargetOffsetY` double default NULL; -alter table prims change column `SitTargetOffsetZ` `SitTargetOffsetZ` double default NULL; -alter table prims change column `SitTargetOrientW` `SitTargetOrientW` double default NULL; -alter table prims change column `SitTargetOrientX` `SitTargetOrientX` double default NULL; -alter table prims change column `SitTargetOrientY` `SitTargetOrientY` double default NULL; -alter table prims change column `SitTargetOrientZ` `SitTargetOrientZ` double default NULL; -alter table prims change column `LoopedSoundGain` `LoopedSoundGain` double NOT NULL default '0'; -alter table prims change column `OmegaX` `OmegaX` double NOT NULL default '0'; -alter table prims change column `OmegaY` `OmegaY` double NOT NULL default '0'; -alter table prims change column `OmegaZ` `OmegaZ` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetX` `CameraEyeOffsetX` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetY` `CameraEyeOffsetY` double NOT NULL default '0'; -alter table prims change column `CameraEyeOffsetZ` `CameraEyeOffsetZ` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetX` `CameraAtOffsetX` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetY` `CameraAtOffsetY` double NOT NULL default '0'; -alter table prims change column `CameraAtOffsetZ` `CameraAtOffsetZ` double NOT NULL default '0'; -alter table prims change column `CollisionSoundVolume` `CollisionSoundVolume` double NOT NULL default '0'; - -commit; - -:VERSION 27 #--------------------- - -BEGIN; - -ALTER TABLE prims DROP COLUMN ParentID; - -COMMIT; - -:VERSION 28 #--------------------- - -BEGIN; - -update terrain - set RegionUUID = concat(substr(RegionUUID, 1, 8), "-", substr(RegionUUID, 9, 4), "-", substr(RegionUUID, 13, 4), "-", substr(RegionUUID, 17, 4), "-", substr(RegionUUID, 21, 12)) - where RegionUUID not like '%-%'; - - -update landaccesslist - set LandUUID = concat(substr(LandUUID, 1, 8), "-", substr(LandUUID, 9, 4), "-", substr(LandUUID, 13, 4), "-", substr(LandUUID, 17, 4), "-", substr(LandUUID, 21, 12)) - where LandUUID not like '%-%'; - -update landaccesslist - set AccessUUID = concat(substr(AccessUUID, 1, 8), "-", substr(AccessUUID, 9, 4), "-", substr(AccessUUID, 13, 4), "-", substr(AccessUUID, 17, 4), "-", substr(AccessUUID, 21, 12)) - where AccessUUID not like '%-%'; - - -update prims - set UUID = concat(substr(UUID, 1, 8), "-", substr(UUID, 9, 4), "-", substr(UUID, 13, 4), "-", substr(UUID, 17, 4), "-", substr(UUID, 21, 12)) - where UUID not like '%-%'; - -update prims - set RegionUUID = concat(substr(RegionUUID, 1, 8), "-", substr(RegionUUID, 9, 4), "-", substr(RegionUUID, 13, 4), "-", substr(RegionUUID, 17, 4), "-", substr(RegionUUID, 21, 12)) - where RegionUUID not like '%-%'; - -update prims - set SceneGroupID = concat(substr(SceneGroupID, 1, 8), "-", substr(SceneGroupID, 9, 4), "-", substr(SceneGroupID, 13, 4), "-", substr(SceneGroupID, 17, 4), "-", substr(SceneGroupID, 21, 12)) - where SceneGroupID not like '%-%'; - -update prims - set CreatorID = concat(substr(CreatorID, 1, 8), "-", substr(CreatorID, 9, 4), "-", substr(CreatorID, 13, 4), "-", substr(CreatorID, 17, 4), "-", substr(CreatorID, 21, 12)) - where CreatorID not like '%-%'; - -update prims - set OwnerID = concat(substr(OwnerID, 1, 8), "-", substr(OwnerID, 9, 4), "-", substr(OwnerID, 13, 4), "-", substr(OwnerID, 17, 4), "-", substr(OwnerID, 21, 12)) - where OwnerID not like '%-%'; - -update prims - set GroupID = concat(substr(GroupID, 1, 8), "-", substr(GroupID, 9, 4), "-", substr(GroupID, 13, 4), "-", substr(GroupID, 17, 4), "-", substr(GroupID, 21, 12)) - where GroupID not like '%-%'; - -update prims - set LastOwnerID = concat(substr(LastOwnerID, 1, 8), "-", substr(LastOwnerID, 9, 4), "-", substr(LastOwnerID, 13, 4), "-", substr(LastOwnerID, 17, 4), "-", substr(LastOwnerID, 21, 12)) - where LastOwnerID not like '%-%'; - - -update primshapes - set UUID = concat(substr(UUID, 1, 8), "-", substr(UUID, 9, 4), "-", substr(UUID, 13, 4), "-", substr(UUID, 17, 4), "-", substr(UUID, 21, 12)) - where UUID not like '%-%'; - - -update land - set UUID = concat(substr(UUID, 1, 8), "-", substr(UUID, 9, 4), "-", substr(UUID, 13, 4), "-", substr(UUID, 17, 4), "-", substr(UUID, 21, 12)) - where UUID not like '%-%'; - -update land - set RegionUUID = concat(substr(RegionUUID, 1, 8), "-", substr(RegionUUID, 9, 4), "-", substr(RegionUUID, 13, 4), "-", substr(RegionUUID, 17, 4), "-", substr(RegionUUID, 21, 12)) - where RegionUUID not like '%-%'; - -update land - set OwnerUUID = concat(substr(OwnerUUID, 1, 8), "-", substr(OwnerUUID, 9, 4), "-", substr(OwnerUUID, 13, 4), "-", substr(OwnerUUID, 17, 4), "-", substr(OwnerUUID, 21, 12)) - where OwnerUUID not like '%-%'; - -update land - set GroupUUID = concat(substr(GroupUUID, 1, 8), "-", substr(GroupUUID, 9, 4), "-", substr(GroupUUID, 13, 4), "-", substr(GroupUUID, 17, 4), "-", substr(GroupUUID, 21, 12)) - where GroupUUID not like '%-%'; - -update land - set MediaTextureUUID = concat(substr(MediaTextureUUID, 1, 8), "-", substr(MediaTextureUUID, 9, 4), "-", substr(MediaTextureUUID, 13, 4), "-", substr(MediaTextureUUID, 17, 4), "-", substr(MediaTextureUUID, 21, 12)) - where MediaTextureUUID not like '%-%'; - -update land - set SnapshotUUID = concat(substr(SnapshotUUID, 1, 8), "-", substr(SnapshotUUID, 9, 4), "-", substr(SnapshotUUID, 13, 4), "-", substr(SnapshotUUID, 17, 4), "-", substr(SnapshotUUID, 21, 12)) - where SnapshotUUID not like '%-%'; - -update land - set AuthbuyerID = concat(substr(AuthbuyerID, 1, 8), "-", substr(AuthbuyerID, 9, 4), "-", substr(AuthbuyerID, 13, 4), "-", substr(AuthbuyerID, 17, 4), "-", substr(AuthbuyerID, 21, 12)) - where AuthbuyerID not like '%-%'; - -COMMIT; - -:VERSION 29 #--------------------- - -BEGIN; - -ALTER TABLE prims ADD COLUMN PassTouches tinyint not null default 0; - -COMMIT; - -:VERSION 30 #--------------------- - -BEGIN; - -ALTER TABLE regionsettings ADD COLUMN loaded_creation_date varchar(20) default NULL; -ALTER TABLE regionsettings ADD COLUMN loaded_creation_time varchar(20) default NULL; -ALTER TABLE regionsettings ADD COLUMN loaded_creation_id varchar(64) default NULL; - -COMMIT; - -:VERSION 31 #--------------------- - -BEGIN; - -ALTER TABLE regionsettings DROP COLUMN loaded_creation_date; -ALTER TABLE regionsettings DROP COLUMN loaded_creation_time; -ALTER TABLE regionsettings ADD COLUMN loaded_creation_datetime int unsigned NOT NULL default 0; - -COMMIT; - -:VERSION 32 - -BEGIN; -CREATE TABLE `regionwindlight` ( + `sunvectorx` double NOT NULL DEFAULT '0', + `sunvectory` double NOT NULL DEFAULT '0', + `sunvectorz` double NOT NULL DEFAULT '0', + `loaded_creation_id` varchar(64) DEFAULT NULL, + `loaded_creation_datetime` int(10) unsigned NOT NULL DEFAULT '0', + `map_tile_ID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `TelehubObject` varchar(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `parcel_tile_ID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `covenant_datetime` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`regionUUID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `regionwindlight` ( `region_id` varchar(36) NOT NULL DEFAULT '000000-0000-0000-0000-000000000000', `water_color_r` float(9,6) unsigned NOT NULL DEFAULT '4.000000', `water_color_g` float(9,6) unsigned NOT NULL DEFAULT '38.000000', @@ -779,172 +326,144 @@ CREATE TABLE `regionwindlight` ( `cloud_detail_x` float(3,2) unsigned NOT NULL DEFAULT '1.00', `cloud_detail_y` float(3,2) unsigned NOT NULL DEFAULT '0.53', `cloud_detail_density` float(3,2) unsigned NOT NULL DEFAULT '0.12', - `cloud_scroll_x` float(3,2) unsigned NOT NULL DEFAULT '0.20', + `cloud_scroll_x` float(4,2) NOT NULL DEFAULT '0.20', `cloud_scroll_x_lock` tinyint(1) unsigned NOT NULL DEFAULT '0', - `cloud_scroll_y` float(3,2) unsigned NOT NULL DEFAULT '0.01', + `cloud_scroll_y` float(4,2) NOT NULL DEFAULT '0.01', `cloud_scroll_y_lock` tinyint(1) unsigned NOT NULL DEFAULT '0', `draw_classic_clouds` tinyint(1) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`region_id`) -); - - -:VERSION 33 #--------------------- - -BEGIN; -ALTER TABLE regionsettings ADD map_tile_ID CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; -COMMIT; - -:VERSION 34 #--------------------- - -BEGIN; -ALTER TABLE `regionwindlight` CHANGE COLUMN `cloud_scroll_x` `cloud_scroll_x` FLOAT(4,2) NOT NULL DEFAULT '0.20' AFTER `cloud_detail_density`, CHANGE COLUMN `cloud_scroll_y` `cloud_scroll_y` FLOAT(4,2) NOT NULL DEFAULT '0.01' AFTER `cloud_scroll_x_lock`; -COMMIT; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -:VERSION 35 #--------------------- - -BEGIN; -ALTER TABLE prims ADD COLUMN MediaURL varchar(255); -ALTER TABLE primshapes ADD COLUMN Media TEXT; -COMMIT; - -:VERSION 36 #--------------------- - -BEGIN; -ALTER TABLE `land` ADD COLUMN `MediaType` VARCHAR(32) NOT NULL DEFAULT 'none/none' ; -ALTER TABLE `land` ADD COLUMN `MediaDescription` VARCHAR(255) NOT NULL DEFAULT ''; -ALTER TABLE `land` ADD COLUMN `MediaSize` VARCHAR(16) NOT NULL DEFAULT '0,0'; -ALTER TABLE `land` ADD COLUMN `MediaLoop` BOOLEAN NOT NULL DEFAULT FALSE; -ALTER TABLE `land` ADD COLUMN `ObscureMusic` BOOLEAN NOT NULL DEFAULT FALSE; -ALTER TABLE `land` ADD COLUMN `ObscureMedia` BOOLEAN NOT NULL DEFAULT FALSE; -COMMIT; - -:VERSION 37 #--------------------- - -BEGIN; - -ALTER TABLE `prims` MODIFY COLUMN `CreatorID` VARCHAR(255) NOT NULL DEFAULT ''; -ALTER TABLE `primitems` MODIFY COLUMN `CreatorID` VARCHAR(255) NOT NULL DEFAULT ''; - -COMMIT; - -:VERSION 38 #--------------------- - -BEGIN; - -alter table land ENGINE = MyISAM; -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 regionban ENGINE = MyISAM; -alter table regionsettings ENGINE = MyISAM; -alter table terrain ENGINE = MyISAM; - -COMMIT; - -:VERSION 39 #--------------- Telehub support - -BEGIN; CREATE TABLE IF NOT EXISTS `spawn_points` ( - `RegionID` varchar(36) COLLATE utf8_unicode_ci NOT NULL, + `RegionID` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Yaw` float NOT NULL, `Pitch` float NOT NULL, `Distance` float NOT NULL, KEY `RegionID` (`RegionID`) -) ENGINE=MyISAM; - -ALTER TABLE `regionsettings` ADD COLUMN `TelehubObject` varchar(36) NOT NULL; -COMMIT; - -:VERSION 40 #---------------- Parcels for sale - -BEGIN; -ALTER TABLE `regionsettings` ADD COLUMN `parcel_tile_ID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; -COMMIT; - -:VERSION 41 #---------------- Timed bans/access - -BEGIN; -ALTER TABLE `landaccesslist` ADD COLUMN `Expires` INTEGER NOT NULL DEFAULT 0; -COMMIT; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -:VERSION 42 #--------------------- Region Covenant changed time - -BEGIN; -ALTER TABLE regionsettings ADD COLUMN covenant_datetime int unsigned NOT NULL DEFAULT '0'; -COMMIT; - -:VERSION 43 #--------------------- - -BEGIN; - -ALTER TABLE `regionsettings` MODIFY COLUMN `TelehubObject` VARCHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; - -COMMIT; - -:VERSION 44 #--------------------- Environment Settings - -BEGIN; - -CREATE TABLE `regionenvironment` ( +CREATE TABLE IF NOT EXISTS `regionenvironment` ( `region_id` varchar(36) NOT NULL, - `llsd_settings` TEXT NOT NULL, + `llsd_settings` text NOT NULL, PRIMARY KEY (`region_id`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -COMMIT; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -:VERSION 45 - -BEGIN; - -CREATE TABLE `regionextra` (`RegionID` char(36) not null, `Name` varchar(32) not null, `value` text, primary key(`RegionID`, `Name`)); +CREATE TABLE IF NOT EXISTS `regionextra` ( + `RegionID` char(36) NOT NULL, + `Name` varchar(32) NOT NULL, + `value` text, + PRIMARY KEY (`RegionID`,`Name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; -:VERSION 46 #---------------- Dynamic attributes +:VERSION 52 #---- avination fields BEGIN; -ALTER TABLE prims ADD COLUMN DynAttrs TEXT; +ALTER TABLE `prims` ADD COLUMN `PassCollisions` tinyint(4) NOT NULL default '0'; +ALTER TABLE `prims` ADD COLUMN `Vehicle` TEXT default NULL; +ALTER TABLE `regionsettings` ADD COLUMN `block_search` tinyint(4) NOT NULL default '0'; +ALTER TABLE `regionsettings` ADD COLUMN `casino` tinyint(4) NOT NULL default '0'; +ALTER TABLE `land` ADD COLUMN `SeeAVs` tinyint(4) NOT NULL default '1'; +ALTER TABLE `land` ADD COLUMN `AnyAVSounds` tinyint(4) NOT NULL default '1'; +ALTER TABLE `land` ADD COLUMN `GroupAVSounds` tinyint(4) NOT NULL default '1'; COMMIT; -:VERSION 47 #---------------- Extra physics params +:VERSION 53 #---- STATUS ROTATION axis locks 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'; +ALTER TABLE `prims` ADD COLUMN `RotationAxisLocks` tinyint(4) NOT NULL default '0'; COMMIT; -:VERSION 48 #---------------- Keyframes +:VERSION 54 #----- add baked terrain store 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; - +CREATE TABLE IF NOT EXISTS `bakedterrain` ( + `RegionUUID` varchar(255) DEFAULT NULL, + `Revision` int(11) DEFAULT NULL, + `Heightfield` longblob +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +COMMIT; + +:VERSION 55 #----- Increase float precision for windlight needed by scripts + +BEGIN; + +ALTER TABLE `regionwindlight` + +MODIFY `water_fog_density_exponent` float(9,7) unsigned NOT NULL DEFAULT '4.0', +MODIFY `underwater_fog_modifier` float(9,8) unsigned NOT NULL DEFAULT '0.25', +MODIFY `reflection_wavelet_scale_1` float(9,7) unsigned NOT NULL DEFAULT '2.0', +MODIFY `reflection_wavelet_scale_2` float(9,7) unsigned NOT NULL DEFAULT '2.0', +MODIFY `reflection_wavelet_scale_3` float(9,7) unsigned NOT NULL DEFAULT '2.0', +MODIFY `fresnel_scale` float(9,8) unsigned NOT NULL DEFAULT '0.40', +MODIFY `fresnel_offset` float(9,8) unsigned NOT NULL DEFAULT '0.50', +MODIFY `refract_scale_above` float(9,8) unsigned NOT NULL DEFAULT '0.03', +MODIFY `refract_scale_below` float(9,8) unsigned NOT NULL DEFAULT '0.20', +MODIFY `blur_multiplier` float(9,8) unsigned NOT NULL DEFAULT '0.040', +MODIFY `big_wave_direction_x` float(9,8) NOT NULL DEFAULT '1.05', +MODIFY `big_wave_direction_y` float(9,8) NOT NULL DEFAULT '-0.42', +MODIFY `little_wave_direction_x` float(9,8) NOT NULL DEFAULT '1.11', +MODIFY `little_wave_direction_y` float(9,8) NOT NULL DEFAULT '-1.16', +MODIFY `horizon_r` float(9,8) unsigned NOT NULL DEFAULT '0.25', +MODIFY `horizon_g` float(9,8) unsigned NOT NULL DEFAULT '0.25', +MODIFY `horizon_b` float(9,8) unsigned NOT NULL DEFAULT '0.32', +MODIFY `horizon_i` float(9,8) unsigned NOT NULL DEFAULT '0.32', +MODIFY `haze_horizon` float(9,8) unsigned NOT NULL DEFAULT '0.19', +MODIFY `blue_density_r` float(9,8) unsigned NOT NULL DEFAULT '0.12', +MODIFY `blue_density_g` float(9,8) unsigned NOT NULL DEFAULT '0.22', +MODIFY `blue_density_b` float(9,8) unsigned NOT NULL DEFAULT '0.38', +MODIFY `blue_density_i` float(9,8) unsigned NOT NULL DEFAULT '0.38', +MODIFY `haze_density` float(9,8) unsigned NOT NULL DEFAULT '0.70', +MODIFY `density_multiplier` float(9,8) unsigned NOT NULL DEFAULT '0.18', +MODIFY `distance_multiplier` float(9,6) unsigned NOT NULL DEFAULT '0.8', +MODIFY `sun_moon_color_r` float(9,8) unsigned NOT NULL DEFAULT '0.24', +MODIFY `sun_moon_color_g` float(9,8) unsigned NOT NULL DEFAULT '0.26', +MODIFY `sun_moon_color_b` float(9,8) unsigned NOT NULL DEFAULT '0.30', +MODIFY `sun_moon_color_i` float(9,8) unsigned NOT NULL DEFAULT '0.30', +MODIFY `sun_moon_position` float(9,8) unsigned NOT NULL DEFAULT '0.317', +MODIFY `ambient_r` float(9,8) unsigned NOT NULL DEFAULT '0.35', +MODIFY `ambient_g` float(9,8) unsigned NOT NULL DEFAULT '0.35', +MODIFY `ambient_b` float(9,8) unsigned NOT NULL DEFAULT '0.35', +MODIFY `ambient_i` float(9,8) unsigned NOT NULL DEFAULT '0.35', +MODIFY `east_angle` float(9,8) unsigned NOT NULL DEFAULT '0.00', +MODIFY `sun_glow_focus` float(9,8) unsigned NOT NULL DEFAULT '0.10', +MODIFY `sun_glow_size` float(9,8) unsigned NOT NULL DEFAULT '1.75', +MODIFY `scene_gamma` float(9,7) unsigned NOT NULL DEFAULT '1.00', +MODIFY `star_brightness` float(9,8) unsigned NOT NULL DEFAULT '0.00', +MODIFY `cloud_color_r` float(9,8) unsigned NOT NULL DEFAULT '0.41', +MODIFY `cloud_color_g` float(9,8) unsigned NOT NULL DEFAULT '0.41', +MODIFY `cloud_color_b` float(9,8) unsigned NOT NULL DEFAULT '0.41', +MODIFY `cloud_color_i` float(9,8) unsigned NOT NULL DEFAULT '0.41', +MODIFY `cloud_x` float(9,8) unsigned NOT NULL DEFAULT '1.00', +MODIFY `cloud_y` float(9,8) unsigned NOT NULL DEFAULT '0.53', +MODIFY `cloud_density` float(9,8) unsigned NOT NULL DEFAULT '1.00', +MODIFY `cloud_coverage` float(9,8) unsigned NOT NULL DEFAULT '0.27', +MODIFY `cloud_scale` float(9,8) unsigned NOT NULL DEFAULT '0.42', +MODIFY `cloud_detail_x` float(9,8) unsigned NOT NULL DEFAULT '1.00', +MODIFY `cloud_detail_y` float(9,8) unsigned NOT NULL DEFAULT '0.53', +MODIFY `cloud_detail_density` float(9,8) unsigned NOT NULL DEFAULT '0.12', +MODIFY `cloud_scroll_x` float(9,7) NOT NULL DEFAULT '0.20', +MODIFY `cloud_scroll_y` float(9,7) NOT NULL DEFAULT '0.01'; + +COMMIT; + +:VERSION 56 #----- Add RezzerID field in table prims + +BEGIN; + +ALTER TABLE `prims` ADD COLUMN `RezzerID` char(36) DEFAULT NULL; + +COMMIT; + +:VERSION 57 #----- Add physics inertia data + +BEGIN; +ALTER TABLE `prims` ADD COLUMN `PhysInertia` TEXT default NULL; COMMIT; - diff --git a/OpenSim/Data/MySQL/Resources/UserAccount.migrations b/OpenSim/Data/MySQL/Resources/UserAccount.migrations index 97e5e4f..98be097 100644 --- a/OpenSim/Data/MySQL/Resources/UserAccount.migrations +++ b/OpenSim/Data/MySQL/Resources/UserAccount.migrations @@ -1,47 +1,31 @@ -:VERSION 1 # ------------------------- +:VERSION 5 # ------------------------- BEGIN; -CREATE TABLE `UserAccounts` ( - `PrincipalID` CHAR(36) NOT NULL, - `ScopeID` CHAR(36) NOT NULL, - `FirstName` VARCHAR(64) NOT NULL, - `LastName` VARCHAR(64) NOT NULL, - `Email` VARCHAR(64), - `ServiceURLs` TEXT, - `Created` INT(11) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; +CREATE TABLE IF NOT EXISTS `UserAccounts` ( + `PrincipalID` char(36) NOT NULL, + `ScopeID` char(36) NOT NULL, + `FirstName` varchar(64) NOT NULL, + `LastName` varchar(64) NOT NULL, + `Email` varchar(64) DEFAULT NULL, + `ServiceURLs` text, + `Created` int(11) DEFAULT NULL, + `UserLevel` int(11) NOT NULL DEFAULT '0', + `UserFlags` int(11) NOT NULL DEFAULT '0', + `UserTitle` varchar(64) NOT NULL DEFAULT '', + UNIQUE KEY `PrincipalID` (`PrincipalID`), + KEY `Email` (`Email`), + KEY `FirstName` (`FirstName`), + KEY `LastName` (`LastName`), + KEY `Name` (`FirstName`,`LastName`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; COMMIT; -:VERSION 2 # ------------------------- +:VERSION 6 # ------------------------- BEGIN; -INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, lastname AS LastName, email as Email, CONCAT('AssetServerURI=', userAssetURI, ' InventoryServerURI=', userInventoryURI, ' GatewayURI= HomeURI=') AS ServiceURLs, created as Created FROM users; +ALTER TABLE `UserAccounts` ADD `active` INT NOT NULL DEFAULT '1'; COMMIT; - -:VERSION 3 # ------------------------- - -BEGIN; - -CREATE UNIQUE INDEX PrincipalID ON UserAccounts(PrincipalID); -CREATE INDEX Email ON UserAccounts(Email); -CREATE INDEX FirstName ON UserAccounts(FirstName); -CREATE INDEX LastName ON UserAccounts(LastName); -CREATE INDEX Name ON UserAccounts(FirstName,LastName); - -COMMIT; - -:VERSION 4 # ------------------------- - -BEGIN; - -ALTER TABLE UserAccounts ADD COLUMN UserLevel integer NOT NULL DEFAULT 0; -ALTER TABLE UserAccounts ADD COLUMN UserFlags integer NOT NULL DEFAULT 0; -ALTER TABLE UserAccounts ADD COLUMN UserTitle varchar(64) NOT NULL DEFAULT ''; - -COMMIT; - - diff --git a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations index 0759b26..cfcc18b 100644 --- a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations +++ b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations @@ -1,4 +1,4 @@ -:VERSION 1 # ------------------------------- +:VERSION 5 # ------------------------------- begin; @@ -19,7 +19,7 @@ CREATE TABLE IF NOT EXISTS `classifieds` ( `classifiedflags` int(8) NOT NULL, `priceforlisting` int(5) NOT NULL, PRIMARY KEY (`classifieduuid`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `usernotes` ( @@ -27,7 +27,7 @@ CREATE TABLE IF NOT EXISTS `usernotes` ( `targetuuid` varchar(36) NOT NULL, `notes` text NOT NULL, UNIQUE KEY `useruuid` (`useruuid`,`targetuuid`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `userpicks` ( @@ -44,8 +44,9 @@ CREATE TABLE IF NOT EXISTS `userpicks` ( `posglobal` varchar(255) NOT NULL, `sortorder` int(2) NOT NULL, `enabled` enum('true','false') NOT NULL, + `gatekeeper` varchar(255), PRIMARY KEY (`pickuuid`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `userprofile` ( @@ -64,35 +65,22 @@ CREATE TABLE IF NOT EXISTS `userprofile` ( `profileFirstImage` varchar(36) NOT NULL, `profileFirstText` text NOT NULL, PRIMARY KEY (`useruuid`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; +) ENGINE=InnoDB 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; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; -: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; +) ENGINE=InnoDB DEFAULT CHARSET=latin1; -: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 deleted file mode 100644 index 9129075..0000000 --- a/OpenSim/Data/MySQL/Resources/UserStore.migrations +++ /dev/null @@ -1,168 +0,0 @@ -:VERSION 1 # ----------------------------- - -BEGIN; - -SET FOREIGN_KEY_CHECKS=0; --- ---------------------------- --- Table structure for agents --- ---------------------------- -CREATE TABLE `agents` ( - `UUID` varchar(36) NOT NULL, - `sessionID` varchar(36) NOT NULL, - `secureSessionID` varchar(36) NOT NULL, - `agentIP` varchar(16) NOT NULL, - `agentPort` int(11) NOT NULL, - `agentOnline` tinyint(4) NOT NULL, - `loginTime` int(11) NOT NULL, - `logoutTime` int(11) NOT NULL, - `currentRegion` varchar(36) NOT NULL, - `currentHandle` bigint(20) unsigned NOT NULL, - `currentPos` varchar(64) NOT NULL, - PRIMARY KEY (`UUID`), - UNIQUE KEY `session` (`sessionID`), - UNIQUE KEY `ssession` (`secureSessionID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - --- Create schema avatar_appearance --- - -CREATE TABLE `avatarappearance` ( - Owner char(36) NOT NULL, - Serial int(10) unsigned NOT NULL, - Visual_Params blob NOT NULL, - Texture blob NOT NULL, - Avatar_Height float NOT NULL, - Body_Item char(36) NOT NULL, - Body_Asset char(36) NOT NULL, - Skin_Item char(36) NOT NULL, - Skin_Asset char(36) NOT NULL, - Hair_Item char(36) NOT NULL, - Hair_Asset char(36) NOT NULL, - Eyes_Item char(36) NOT NULL, - Eyes_Asset char(36) NOT NULL, - Shirt_Item char(36) NOT NULL, - Shirt_Asset char(36) NOT NULL, - Pants_Item char(36) NOT NULL, - Pants_Asset char(36) NOT NULL, - Shoes_Item char(36) NOT NULL, - Shoes_Asset char(36) NOT NULL, - Socks_Item char(36) NOT NULL, - Socks_Asset char(36) NOT NULL, - Jacket_Item char(36) NOT NULL, - Jacket_Asset char(36) NOT NULL, - Gloves_Item char(36) NOT NULL, - Gloves_Asset char(36) NOT NULL, - Undershirt_Item char(36) NOT NULL, - Undershirt_Asset char(36) NOT NULL, - Underpants_Item char(36) NOT NULL, - Underpants_Asset char(36) NOT NULL, - Skirt_Item char(36) NOT NULL, - Skirt_Asset char(36) NOT NULL, - PRIMARY KEY (`Owner`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - -SET FOREIGN_KEY_CHECKS=0; --- ---------------------------- --- Table structure for users --- ---------------------------- -CREATE TABLE `userfriends` ( - `ownerID` VARCHAR(37) NOT NULL, - `friendID` VARCHAR(37) NOT NULL, - `friendPerms` INT NOT NULL, - `datetimestamp` INT NOT NULL, - UNIQUE KEY (`ownerID`, `friendID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; --- ---------------------------- --- Table structure for users --- ---------------------------- -CREATE TABLE `users` ( - `UUID` varchar(36) NOT NULL default '', - `username` varchar(32) NOT NULL, - `lastname` varchar(32) NOT NULL, - `passwordHash` varchar(32) NOT NULL, - `passwordSalt` varchar(32) NOT NULL, - `homeRegion` bigint(20) unsigned default NULL, - `homeLocationX` float default NULL, - `homeLocationY` float default NULL, - `homeLocationZ` float default NULL, - `homeLookAtX` float default NULL, - `homeLookAtY` float default NULL, - `homeLookAtZ` float default NULL, - `created` int(11) NOT NULL, - `lastLogin` int(11) NOT NULL, - `userInventoryURI` varchar(255) default NULL, - `userAssetURI` varchar(255) default NULL, - `profileCanDoMask` int(10) unsigned default NULL, - `profileWantDoMask` int(10) unsigned default NULL, - `profileAboutText` text, - `profileFirstText` text, - `profileImage` varchar(36) default NULL, - `profileFirstImage` varchar(36) default NULL, - `webLoginKey` varchar(36) default NULL, - PRIMARY KEY (`UUID`), - UNIQUE KEY `usernames` (`username`,`lastname`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8; - --- ---------------------------- --- Records --- ---------------------------- -COMMIT; - -:VERSION 2 # ----------------------------- - -BEGIN; - -ALTER TABLE users add homeRegionID char(36) NOT NULL default '00000000-0000-0000-0000-000000000000'; - -COMMIT; - -:VERSION 3 # ----------------------------- - -BEGIN; - -ALTER TABLE users add userFlags integer NOT NULL default 0; -ALTER TABLE users add godLevel integer NOT NULL default 0; - -COMMIT; - -:VERSION 4 # ----------------------------- - -BEGIN; - -ALTER TABLE users add customType varchar(32) not null default ''; -ALTER TABLE users add partner char(36) not null default '00000000-0000-0000-0000-000000000000'; - -COMMIT; - -:VERSION 5 # ----------------------------- - -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; - -COMMIT; - -:VERSION 6 # ----------------------------- - -BEGIN; - -ALTER TABLE agents add currentLookAt varchar(36) not null default ''; - -COMMIT; - -:VERSION 7 # ----------------------------- - -BEGIN; - -ALTER TABLE users add email varchar(250); - -COMMIT; - -:VERSION 8 # ----------------------------- - -BEGIN; - -ALTER TABLE users add scopeID char(36) not null default '00000000-0000-0000-0000-000000000000'; - -COMMIT; - diff --git a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations index 78d6e51..9459e3e 100644 --- a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations @@ -16,13 +16,13 @@ CREATE TABLE `XAssetsMeta` ( `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, PRIMARY KEY (`hash`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Version 1'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/XMute.migrations b/OpenSim/Data/MySQL/Resources/XMute.migrations new file mode 100644 index 0000000..4ac7f82 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/XMute.migrations @@ -0,0 +1,16 @@ +:VERSION 1 + +BEGIN; + +CREATE TABLE `XMute` ( + `AgentID` char(36) NOT NULL, + `MuteID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', + `MuteName` varchar(64) NOT NULL DEFAULT '', + `MuteType` int(11) NOT NULL DEFAULT '1', + `MuteFlags` int(11) NOT NULL DEFAULT '0', + `Stamp` int(11) NOT NULL, + UNIQUE KEY `AgentID_2` (`AgentID`,`MuteID`,`MuteName`), + KEY `AgentID` (`AgentID`) +); + +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations index 9e6f1c1..6ec8914 100644 --- a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations +++ b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations @@ -31,7 +31,7 @@ CREATE TABLE `os_groups_membership` ( `AccessToken` char(36) NOT NULL default '', PRIMARY KEY (`GroupID`,`PrincipalID`), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `os_groups_roles` ( @@ -43,7 +43,7 @@ CREATE TABLE `os_groups_roles` ( `Powers` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`GroupID`,`RoleID`), KEY `GroupID` (`GroupID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `os_groups_rolemembership` ( @@ -52,7 +52,7 @@ CREATE TABLE `os_groups_rolemembership` ( `PrincipalID` VARCHAR(255) NOT NULL default '', PRIMARY KEY (`GroupID`,`RoleID`,`PrincipalID`), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `os_groups_invites` ( @@ -63,7 +63,7 @@ CREATE TABLE `os_groups_invites` ( `TMStamp` timestamp NOT NULL, PRIMARY KEY (`InviteID`), UNIQUE KEY `PrincipalGroup` (`GroupID`,`PrincipalID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `os_groups_notices` ( @@ -81,13 +81,13 @@ CREATE TABLE `os_groups_notices` ( PRIMARY KEY (`NoticeID`), KEY `GroupID` (`GroupID`), KEY `TMStamp` (`TMStamp`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; CREATE TABLE `os_groups_principals` ( `PrincipalID` VARCHAR(255) NOT NULL default '', `ActiveGroupID` char(36) NOT NULL default '', PRIMARY KEY (`PrincipalID`) -) ENGINE=MyISAM; +) ENGINE=InnoDB; COMMIT; @@ -112,4 +112,4 @@ DROP TABLE `diva_groups_principals`; DELETE FROM `migrations` WHERE name='diva_im_Store'; -COMMIT; \ No newline at end of file +COMMIT; -- cgit v1.1