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 From d6bcc229eba25ae240322f48ea3f31b832d55ab4 Mon Sep 17 00:00:00 2001 From: onefang Date: Sun, 19 May 2019 21:56:12 +1000 Subject: Various database fixups. InnoDB -> MyISAM utf8 if not exists white space --- OpenSim/Data/MySQL/Resources/AgentPrefs.migrations | 4 ++-- OpenSim/Data/MySQL/Resources/AssetStore.migrations | 2 +- OpenSim/Data/MySQL/Resources/AuthStore.migrations | 4 ++-- OpenSim/Data/MySQL/Resources/Avatar.migrations | 2 +- .../Data/MySQL/Resources/EstateStore.migrations | 12 +++++----- .../Data/MySQL/Resources/FSAssetStore.migrations | 6 ++--- .../Data/MySQL/Resources/FriendsStore.migrations | 2 +- OpenSim/Data/MySQL/Resources/GridStore.migrations | 2 +- .../Data/MySQL/Resources/GridUserStore.migrations | 9 ++----- .../Data/MySQL/Resources/HGTravelStore.migrations | 23 +++++++++--------- OpenSim/Data/MySQL/Resources/IM_Store.migrations | 2 +- .../Data/MySQL/Resources/InventoryStore.migrations | 4 ++-- OpenSim/Data/MySQL/Resources/LogStore.migrations | 4 ++-- .../Data/MySQL/Resources/MuteListStore.migrations | 4 ++-- OpenSim/Data/MySQL/Resources/Presence.migrations | 2 +- .../Data/MySQL/Resources/RegionStore.migrations | 26 ++++++++++---------- .../Data/MySQL/Resources/UserAccount.migrations | 2 +- .../Data/MySQL/Resources/UserProfiles.migrations | 12 +++++----- .../Data/MySQL/Resources/XAssetStore.migrations | 8 +++---- OpenSim/Data/MySQL/Resources/XMute.migrations | 4 ++-- .../MySQL/Resources/os_groups_Store.migrations | 28 +++++++++++----------- 21 files changed, 78 insertions(+), 84 deletions(-) (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations index e496f72..d41ae66 100644 --- a/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations +++ b/OpenSim/Data/MySQL/Resources/AgentPrefs.migrations @@ -2,7 +2,7 @@ BEGIN; -CREATE TABLE `AgentPrefs` ( +CREATE TABLE IF NOT EXISTS `AgentPrefs` ( `PrincipalID` CHAR(36) NOT NULL, `AccessPrefs` CHAR(2) NOT NULL DEFAULT 'M', `HoverHeight` DOUBLE(30, 27) NOT NULL DEFAULT 0, @@ -13,6 +13,6 @@ CREATE TABLE `AgentPrefs` ( `PermNextOwner` INT(6) NOT NULL DEFAULT 532480, UNIQUE KEY `PrincipalID` (`PrincipalID`), PRIMARY KEY(`PrincipalID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/AssetStore.migrations b/OpenSim/Data/MySQL/Resources/AssetStore.migrations index 820799d..07f521b 100644 --- a/OpenSim/Data/MySQL/Resources/AssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AssetStore.migrations @@ -16,6 +16,6 @@ CREATE TABLE IF NOT EXISTS `assets` ( `asset_flags` int(11) NOT NULL DEFAULT '0', `CreatorID` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/AuthStore.migrations b/OpenSim/Data/MySQL/Resources/AuthStore.migrations index f00979f..8d24fbd 100644 --- a/OpenSim/Data/MySQL/Resources/AuthStore.migrations +++ b/OpenSim/Data/MySQL/Resources/AuthStore.migrations @@ -9,7 +9,7 @@ CREATE TABLE IF NOT EXISTS `auth` ( `webLoginKey` varchar(255) NOT NULL DEFAULT '', `accountType` varchar(32) NOT NULL DEFAULT 'UserAccount', PRIMARY KEY (`UUID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `tokens` ( `UUID` char(36) NOT NULL, @@ -19,6 +19,6 @@ CREATE TABLE IF NOT EXISTS `tokens` ( KEY `UUID` (`UUID`), KEY `token` (`token`), KEY `validity` (`validity`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/Avatar.migrations b/OpenSim/Data/MySQL/Resources/Avatar.migrations index c5ec9ca..66f75b2 100644 --- a/OpenSim/Data/MySQL/Resources/Avatar.migrations +++ b/OpenSim/Data/MySQL/Resources/Avatar.migrations @@ -8,6 +8,6 @@ CREATE TABLE IF NOT EXISTS `Avatars` ( `Value` text, PRIMARY KEY (`PrincipalID`,`Name`), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/EstateStore.migrations b/OpenSim/Data/MySQL/Resources/EstateStore.migrations index 615af95..9c1ed83 100644 --- a/OpenSim/Data/MySQL/Resources/EstateStore.migrations +++ b/OpenSim/Data/MySQL/Resources/EstateStore.migrations @@ -6,20 +6,20 @@ CREATE TABLE IF NOT EXISTS `estate_groups` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `estate_managers` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM 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`), KEY `EstateID` (`EstateID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `estate_settings` ( `EstateID` int(10) unsigned NOT NULL AUTO_INCREMENT, @@ -50,13 +50,13 @@ CREATE TABLE IF NOT EXISTS `estate_settings` ( `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; +) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `estate_users` ( `EstateID` int(10) unsigned NOT NULL, `uuid` char(36) NOT NULL, KEY `EstateID` (`EstateID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `estateban` ( `EstateID` int(10) unsigned NOT NULL, @@ -65,7 +65,7 @@ CREATE TABLE IF NOT EXISTS `estateban` ( `bannedIpHostMask` varchar(16) NOT NULL, `bannedNameMask` varchar(64) DEFAULT NULL, KEY `estateban_EstateID` (`EstateID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations b/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations index 87d08c6..3f65d9e 100644 --- a/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/FSAssetStore.migrations @@ -3,7 +3,7 @@ BEGIN; -CREATE TABLE `fsassets` ( +CREATE TABLE IF NOT EXISTS `fsassets` ( `id` char(36) NOT NULL, `name` varchar(64) NOT NULL DEFAULT '', `description` varchar(64) NOT NULL DEFAULT '', @@ -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=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -COMMIT; \ No newline at end of file +COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations index 6840f07..7dc7607 100644 --- a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations +++ b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations @@ -9,6 +9,6 @@ CREATE TABLE IF NOT EXISTS `Friends` ( `Offered` varchar(32) NOT NULL DEFAULT '0', PRIMARY KEY (`PrincipalID`(36),`Friend`(36)), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/GridStore.migrations b/OpenSim/Data/MySQL/Resources/GridStore.migrations index e4c8fc3..4116235 100644 --- a/OpenSim/Data/MySQL/Resources/GridStore.migrations +++ b/OpenSim/Data/MySQL/Resources/GridStore.migrations @@ -46,7 +46,7 @@ CREATE TABLE IF NOT EXISTS `regions` ( KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`), KEY `ScopeID` (`ScopeID`), KEY `flags` (`flags`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/GridUserStore.migrations b/OpenSim/Data/MySQL/Resources/GridUserStore.migrations index d08e096..dd73974 100644 --- a/OpenSim/Data/MySQL/Resources/GridUserStore.migrations +++ b/OpenSim/Data/MySQL/Resources/GridUserStore.migrations @@ -2,7 +2,7 @@ BEGIN; -CREATE TABLE `GridUser` ( +CREATE TABLE IF NOT EXISTS `GridUser` ( `UserID` VARCHAR(255) NOT NULL, `HomeRegionID` CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `HomePosition` CHAR(64) NOT NULL DEFAULT '<0,0,0>', @@ -14,11 +14,6 @@ CREATE TABLE `GridUser` ( `Login` CHAR(16) NOT NULL DEFAULT '0', `Logout` CHAR(16) NOT NULL DEFAULT '0', PRIMARY KEY (`UserID`) -) ENGINE=InnoDB; - -COMMIT; - -:VERSION 2 # -------------------------- -BEGIN; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations index b4e4422..ed1fede 100644 --- a/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations +++ b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations @@ -2,17 +2,16 @@ BEGIN; -CREATE TABLE `hg_traveling_data` ( - `SessionID` VARCHAR(36) NOT NULL, - `UserID` VARCHAR(36) NOT NULL, - `GridExternalName` VARCHAR(255) NOT NULL DEFAULT '', - `ServiceToken` VARCHAR(255) NOT NULL DEFAULT '', - `ClientIPAddress` VARCHAR(16) NOT NULL DEFAULT '', - `MyIPAddress` VARCHAR(16) NOT NULL DEFAULT '', - `TMStamp` timestamp NOT NULL, - PRIMARY KEY (`SessionID`), - KEY (`UserID`) -) ENGINE=InnoDB; +CREATE TABLE IF NOT EXISTS `hg_traveling_data` ( + `SessionID` VARCHAR(36) NOT NULL, + `UserID` VARCHAR(36) NOT NULL, + `GridExternalName` VARCHAR(255) NOT NULL DEFAULT '', + `ServiceToken` VARCHAR(255) NOT NULL DEFAULT '', + `ClientIPAddress` VARCHAR(16) NOT NULL DEFAULT '', + `MyIPAddress` VARCHAR(16) NOT NULL DEFAULT '', + `TMStamp` timestamp NOT NULL, + PRIMARY KEY (`SessionID`), + KEY (`UserID`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; - diff --git a/OpenSim/Data/MySQL/Resources/IM_Store.migrations b/OpenSim/Data/MySQL/Resources/IM_Store.migrations index 4f14826..e271fcc 100644 --- a/OpenSim/Data/MySQL/Resources/IM_Store.migrations +++ b/OpenSim/Data/MySQL/Resources/IM_Store.migrations @@ -11,6 +11,6 @@ CREATE TABLE IF NOT EXISTS `im_offline` ( PRIMARY KEY (`ID`), KEY `PrincipalID` (`PrincipalID`), KEY `FromID` (`FromID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations index 2d4384c..7283e41 100644 --- a/OpenSim/Data/MySQL/Resources/InventoryStore.migrations +++ b/OpenSim/Data/MySQL/Resources/InventoryStore.migrations @@ -25,7 +25,7 @@ CREATE TABLE IF NOT EXISTS `inventoryitems` ( PRIMARY KEY (`inventoryID`), KEY `inventoryitems_avatarid` (`avatarID`), KEY `inventoryitems_parentFolderid` (`parentFolderID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `inventoryfolders` ( `folderName` varchar(64) DEFAULT NULL, @@ -37,6 +37,6 @@ CREATE TABLE IF NOT EXISTS `inventoryfolders` ( PRIMARY KEY (`folderID`), KEY `inventoryfolders_agentid` (`agentID`), KEY `inventoryfolders_parentFolderid` (`parentFolderID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/LogStore.migrations b/OpenSim/Data/MySQL/Resources/LogStore.migrations index 9ac26ac..f2990cc 100644 --- a/OpenSim/Data/MySQL/Resources/LogStore.migrations +++ b/OpenSim/Data/MySQL/Resources/LogStore.migrations @@ -1,7 +1,7 @@  :VERSION 1 -CREATE TABLE `logs` ( +CREATE TABLE IF NOT EXISTS `logs` ( `logID` int(10) unsigned NOT NULL auto_increment, `target` varchar(36) default NULL, `server` varchar(64) default NULL, @@ -10,4 +10,4 @@ CREATE TABLE `logs` ( `priority` int(11) default NULL, `message` text, PRIMARY KEY (`logID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; diff --git a/OpenSim/Data/MySQL/Resources/MuteListStore.migrations b/OpenSim/Data/MySQL/Resources/MuteListStore.migrations index 5bde63e..164dc51 100644 --- a/OpenSim/Data/MySQL/Resources/MuteListStore.migrations +++ b/OpenSim/Data/MySQL/Resources/MuteListStore.migrations @@ -2,7 +2,7 @@ BEGIN; -CREATE TABLE `MuteList` ( +CREATE TABLE IF NOT EXISTS `MuteList` ( `AgentID` char(36) NOT NULL, `MuteID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `MuteName` varchar(64) NOT NULL DEFAULT '', @@ -11,6 +11,6 @@ CREATE TABLE `MuteList` ( `Stamp` int(11) NOT NULL, UNIQUE KEY `AgentID_2` (`AgentID`,`MuteID`,`MuteName`), KEY `AgentID` (`AgentID`) -); +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/Presence.migrations b/OpenSim/Data/MySQL/Resources/Presence.migrations index 50aa756..4d14b9d 100644 --- a/OpenSim/Data/MySQL/Resources/Presence.migrations +++ b/OpenSim/Data/MySQL/Resources/Presence.migrations @@ -11,6 +11,6 @@ CREATE TABLE IF NOT EXISTS `Presence` ( UNIQUE KEY `SessionID` (`SessionID`), KEY `UserID` (`UserID`), KEY `RegionID` (`RegionID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/RegionStore.migrations b/OpenSim/Data/MySQL/Resources/RegionStore.migrations index 0577392..fb7862a 100644 --- a/OpenSim/Data/MySQL/Resources/RegionStore.migrations +++ b/OpenSim/Data/MySQL/Resources/RegionStore.migrations @@ -97,7 +97,7 @@ CREATE TABLE IF NOT EXISTS `prims` ( PRIMARY KEY (`UUID`), KEY `prims_regionuuid` (`RegionUUID`), KEY `prims_scenegroupid` (`SceneGroupID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `primshapes` ( `Shape` int(11) DEFAULT NULL, @@ -130,7 +130,7 @@ CREATE TABLE IF NOT EXISTS `primshapes` ( `Media` text, `LastAttachPoint` int(4) NOT NULL DEFAULT '0', PRIMARY KEY (`UUID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `primitems` ( `invType` int(11) DEFAULT NULL, @@ -154,13 +154,13 @@ CREATE TABLE IF NOT EXISTS `primitems` ( `lastOwnerID` char(36) DEFAULT NULL, PRIMARY KEY (`itemID`), KEY `primitems_primid` (`primID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `terrain` ( `RegionUUID` varchar(255) DEFAULT NULL, `Revision` int(11) DEFAULT NULL, `Heightfield` longblob -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `land` ( `UUID` varchar(255) NOT NULL, @@ -204,21 +204,21 @@ CREATE TABLE IF NOT EXISTS `land` ( `ObscureMusic` tinyint(1) NOT NULL DEFAULT '0', `ObscureMedia` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`UUID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM 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; +) ENGINE=MyISAM 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; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `regionsettings` ( `regionUUID` char(36) NOT NULL, @@ -265,7 +265,7 @@ CREATE TABLE IF NOT EXISTS `regionsettings` ( `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; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `regionwindlight` ( `region_id` varchar(36) NOT NULL DEFAULT '000000-0000-0000-0000-000000000000', @@ -332,7 +332,7 @@ CREATE TABLE IF NOT EXISTS `regionwindlight` ( `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`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `spawn_points` ( `RegionID` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, @@ -340,20 +340,20 @@ CREATE TABLE IF NOT EXISTS `spawn_points` ( `Pitch` float NOT NULL, `Distance` float NOT NULL, KEY `RegionID` (`RegionID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `regionenvironment` ( `region_id` varchar(36) NOT NULL, `llsd_settings` text NOT NULL, PRIMARY KEY (`region_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; @@ -387,7 +387,7 @@ CREATE TABLE IF NOT EXISTS `bakedterrain` ( `RegionUUID` varchar(255) DEFAULT NULL, `Revision` int(11) DEFAULT NULL, `Heightfield` longblob -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/UserAccount.migrations b/OpenSim/Data/MySQL/Resources/UserAccount.migrations index 98be097..dcb375e 100644 --- a/OpenSim/Data/MySQL/Resources/UserAccount.migrations +++ b/OpenSim/Data/MySQL/Resources/UserAccount.migrations @@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS `UserAccounts` ( KEY `FirstName` (`FirstName`), KEY `LastName` (`LastName`), KEY `Name` (`FirstName`,`LastName`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations index cfcc18b..512b6ed 100644 --- a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations +++ b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations @@ -19,7 +19,7 @@ CREATE TABLE IF NOT EXISTS `classifieds` ( `classifiedflags` int(8) NOT NULL, `priceforlisting` int(5) NOT NULL, PRIMARY KEY (`classifieduuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM 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=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `userpicks` ( @@ -46,7 +46,7 @@ CREATE TABLE IF NOT EXISTS `userpicks` ( `enabled` enum('true','false') NOT NULL, `gatekeeper` varchar(255), PRIMARY KEY (`pickuuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `userprofile` ( @@ -65,7 +65,7 @@ CREATE TABLE IF NOT EXISTS `userprofile` ( `profileFirstImage` varchar(36) NOT NULL, `profileFirstText` text NOT NULL, PRIMARY KEY (`useruuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `userdata` ( `UserId` char(36) NOT NULL, @@ -73,7 +73,7 @@ CREATE TABLE IF NOT EXISTS `userdata` ( `DataKey` varchar(255), `DataVal` varchar(255), PRIMARY KEY (`UserId`,`TagId`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `usersettings` ( `useruuid` varchar(36) NOT NULL, @@ -81,6 +81,6 @@ CREATE TABLE IF NOT EXISTS `usersettings` ( `visible` enum('true','false') NOT NULL, `email` varchar(254) NOT NULL, PRIMARY KEY (`useruuid`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; +) ENGINE=MyISAM DEFAULT CHARSET=latin1; commit; diff --git a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations index 9459e3e..7641a97 100644 --- a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations +++ b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations @@ -3,7 +3,7 @@ BEGIN; -CREATE TABLE `XAssetsMeta` ( +CREATE TABLE IF NOT EXISTS `XAssetsMeta` ( `ID` char(36) NOT NULL, `Hash` binary(32) NOT NULL, `Name` varchar(64) NOT NULL, @@ -16,13 +16,13 @@ CREATE TABLE `XAssetsMeta` ( `AssetFlags` int(11) NOT NULL, `CreatorID` varchar(128) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Version 1'; -CREATE TABLE `XAssetsData` ( +CREATE TABLE IF NOT EXISTS `XAssetsData` ( `Hash` binary(32) NOT NULL, `Data` longblob NOT NULL, PRIMARY KEY (`hash`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Version 1'; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/XMute.migrations b/OpenSim/Data/MySQL/Resources/XMute.migrations index 4ac7f82..084c67b 100644 --- a/OpenSim/Data/MySQL/Resources/XMute.migrations +++ b/OpenSim/Data/MySQL/Resources/XMute.migrations @@ -2,7 +2,7 @@ BEGIN; -CREATE TABLE `XMute` ( +CREATE TABLE IF NOT EXISTS `XMute` ( `AgentID` char(36) NOT NULL, `MuteID` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', `MuteName` varchar(64) NOT NULL DEFAULT '', @@ -11,6 +11,6 @@ CREATE TABLE `XMute` ( `Stamp` int(11) NOT NULL, UNIQUE KEY `AgentID_2` (`AgentID`,`MuteID`,`MuteName`), KEY `AgentID` (`AgentID`) -); +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; diff --git a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations index 6ec8914..0b605ab 100644 --- a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations +++ b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations @@ -2,7 +2,7 @@ BEGIN; -CREATE TABLE `os_groups_groups` ( +CREATE TABLE IF NOT EXISTS `os_groups_groups` ( `GroupID` char(36) NOT NULL default '', `Location` varchar(255) NOT NULL default '', `Name` varchar(255) NOT NULL default '', @@ -18,10 +18,10 @@ CREATE TABLE `os_groups_groups` ( PRIMARY KEY (`GroupID`), UNIQUE KEY `Name` (`Name`), FULLTEXT KEY `Name_2` (`Name`) -) ENGINE=MyISAM; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_membership` ( +CREATE TABLE IF NOT EXISTS `os_groups_membership` ( `GroupID`char(36) NOT NULL default '', `PrincipalID` VARCHAR(255) NOT NULL default '', `SelectedRoleID` char(36) NOT NULL default '', @@ -31,10 +31,10 @@ CREATE TABLE `os_groups_membership` ( `AccessToken` char(36) NOT NULL default '', PRIMARY KEY (`GroupID`,`PrincipalID`), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_roles` ( +CREATE TABLE IF NOT EXISTS `os_groups_roles` ( `GroupID` char(36) NOT NULL default '', `RoleID` char(36) NOT NULL default '', `Name` varchar(255) NOT NULL default '', @@ -43,19 +43,19 @@ CREATE TABLE `os_groups_roles` ( `Powers` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`GroupID`,`RoleID`), KEY `GroupID` (`GroupID`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_rolemembership` ( +CREATE TABLE IF NOT EXISTS `os_groups_rolemembership` ( `GroupID` char(36) NOT NULL default '', `RoleID` char(36) NOT NULL default '', `PrincipalID` VARCHAR(255) NOT NULL default '', PRIMARY KEY (`GroupID`,`RoleID`,`PrincipalID`), KEY `PrincipalID` (`PrincipalID`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_invites` ( +CREATE TABLE IF NOT EXISTS `os_groups_invites` ( `InviteID` char(36) NOT NULL default '', `GroupID` char(36) NOT NULL default '', `RoleID` char(36) NOT NULL default '', @@ -63,10 +63,10 @@ CREATE TABLE `os_groups_invites` ( `TMStamp` timestamp NOT NULL, PRIMARY KEY (`InviteID`), UNIQUE KEY `PrincipalGroup` (`GroupID`,`PrincipalID`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_notices` ( +CREATE TABLE IF NOT EXISTS `os_groups_notices` ( `GroupID` char(36) NOT NULL default '', `NoticeID` char(36) NOT NULL default '', `TMStamp` int(10) unsigned NOT NULL default '0', @@ -81,13 +81,13 @@ CREATE TABLE `os_groups_notices` ( PRIMARY KEY (`NoticeID`), KEY `GroupID` (`GroupID`), KEY `TMStamp` (`TMStamp`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -CREATE TABLE `os_groups_principals` ( +CREATE TABLE IF NOT EXISTS `os_groups_principals` ( `PrincipalID` VARCHAR(255) NOT NULL default '', `ActiveGroupID` char(36) NOT NULL default '', PRIMARY KEY (`PrincipalID`) -) ENGINE=InnoDB; +) ENGINE=MyISAM DEFAULT CHARSET=utf8; COMMIT; -- cgit v1.1 From bc7039d7c4bd13b5000011aaa306688bb29529ea Mon Sep 17 00:00:00 2001 From: onefang Date: Tue, 6 Aug 2019 01:07:54 +1000 Subject: Add a more direct MySQL handler. --- OpenSim/Data/MySQL/MySQLGenericHandler.cs | 353 ++++++++++++++++++++++++++++++ 1 file changed, 353 insertions(+) create mode 100644 OpenSim/Data/MySQL/MySQLGenericHandler.cs (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLGenericHandler.cs b/OpenSim/Data/MySQL/MySQLGenericHandler.cs new file mode 100644 index 0000000..4f84641 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLGenericHandler.cs @@ -0,0 +1,353 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections.Generic; +using System.Data; +using System.Reflection; +using log4net; +using MySql.Data.MySqlClient; +using OpenMetaverse; +using OpenSim.Framework; +using OpenSim.Region.Framework.Interfaces; + +namespace OpenSim.Data.MySQL +{ +// public class MySQLGenericHandler : MySqlFramework where T: class, new() + public class MySQLGenericHandler : MySqlFramework + { +// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + protected virtual Assembly Assembly + { + get { return GetType().Assembly; } + } + + public MySQLGenericHandler(MySqlTransaction trans) : base(trans) + { + CommonConstruct(); + } + + public MySQLGenericHandler(string connectionString) : base(connectionString) + { + CommonConstruct(); + } + + protected void CommonConstruct() + { + // We always use a new connection for any Migrations + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + } + } +/* + private void CheckColumnNames(IDataReader reader) + { + if (m_ColumnNames != null) + return; + + List columnNames = new List(); + + DataTable schemaTable = reader.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + { + if (row["ColumnName"] != null) + columnNames.Add(row["ColumnName"].ToString()); + } + + m_ColumnNames = columnNames; + } +*/ +/* + public virtual T[] Get(string table, string field, string key) + { + return Get(table, new string[] { field }, new string[] { key }); + } + + public virtual T[] Get(string table, string[] fields, string[] keys) + { + return Get(table, fields, keys, String.Empty); + } + + public virtual T[] Get(string table, string[] fields, string[] keys, string options) + { + if (fields.Length != keys.Length) + return new T[0]; + + List terms = new List(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + for (int i = 0 ; i < fields.Length ; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("`" + fields[i] + "` = ?" + fields[i]); + } + + string where = String.Join(" and ", terms.ToArray()); + + string query = String.Format("select * from {0} where {1} {2}", + table, 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(); + + cmd.Connection = dbcon; + + using (IDataReader reader = cmd.ExecuteReader()) + { + if (reader == null) + return new T[0]; +/* + + CheckColumnNames(reader); + + while (reader.Read()) + { + 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)) + { + 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 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]); + } + } + + if (m_DataField != null) + { + Dictionary data = + new Dictionary(); + + foreach (string col in m_ColumnNames) + { + data[col] = reader[col].ToString(); + if (data[col] == null) + data[col] = String.Empty; + } + + m_DataField.SetValue(row, data); + } + result.Add(row); + } +*/ +/* + } + cmd.Connection = null; + return result.ToArray(); + } + + public virtual T[] Get(string table, string where) + { + using (MySqlCommand cmd = new MySqlCommand()) + { + string query = String.Format("select * from {0} where {1}", + table, where); + + cmd.CommandText = query; + + return DoQuery(cmd); + } + } +*/ + public virtual bool Delete(string table, string field, string key) + { + return Delete(table, new string[] { field }, new string[] { key }); + } + + public virtual bool Delete(string table, string[] fields, string[] keys) + { +// m_log.DebugFormat( +// "[MYSQL GENERIC TABLE HANDLER]: Delete(string[] fields, string[] keys) invoked with {0}:{1}", +// string.Join(",", fields), string.Join(",", keys)); + + if (fields.Length != keys.Length) + return false; + + List terms = new List(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + for (int i = 0 ; i < fields.Length ; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("`" + fields[i] + "` = ?" + fields[i]); + } + + string where = String.Join(" and ", terms.ToArray()); + + string query = String.Format("delete from {0} where {1}", table, where); + + cmd.CommandText = query; + + return ExecuteNonQuery(cmd) > 0; + } + } + + public long GetCount(string table, string field, string key) + { + return GetCount(table, new string[] { field }, new string[] { key }); + } + + public long GetCount(string table, string[] fields, string[] keys) + { + if (fields.Length != keys.Length) + return 0; + + List terms = new List(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + for (int i = 0; i < fields.Length; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("`" + fields[i] + "` = ?" + fields[i]); + } + + string where = String.Join(" and ", terms.ToArray()); + + string query = String.Format("select count(*) from {0} where {1}", + table, where); + + cmd.CommandText = query; + + Object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public long GetCount(string table, string where) + { + using (MySqlCommand cmd = new MySqlCommand()) + { + string query = String.Format("select count(*) from {0} where {1}", + table, where); + + cmd.CommandText = query; + + object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public long GetCount(string table) + { + using (MySqlCommand cmd = new MySqlCommand()) + { + string query = String.Format("select count(*) from {0}", table); + + cmd.CommandText = query; + + object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public object DoQueryScalar(MySqlCommand cmd) + { + if (m_trans == null) + { + 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(); + } + } + } +} -- cgit v1.1 From 1613ca58be0cc3a4c53e72b63899b7fa4492ed7b Mon Sep 17 00:00:00 2001 From: onefang Date: Wed, 7 Aug 2019 07:44:25 +1000 Subject: Swap out MySQLGenericHandler and swap in MySQLRaw. Smaller, simpler, works better. --- OpenSim/Data/MySQL/MySQLGenericHandler.cs | 353 ------------------------------ OpenSim/Data/MySQL/MySQLRaw.cs | 142 ++++++++++++ 2 files changed, 142 insertions(+), 353 deletions(-) delete mode 100644 OpenSim/Data/MySQL/MySQLGenericHandler.cs create mode 100644 OpenSim/Data/MySQL/MySQLRaw.cs (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLGenericHandler.cs b/OpenSim/Data/MySQL/MySQLGenericHandler.cs deleted file mode 100644 index 4f84641..0000000 --- a/OpenSim/Data/MySQL/MySQLGenericHandler.cs +++ /dev/null @@ -1,353 +0,0 @@ -/* - * Copyright (c) Contributors, http://opensimulator.org/ - * See CONTRIBUTORS.TXT for a full list of copyright holders. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * * Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. - * * Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. - * * Neither the name of the OpenSimulator Project nor the - * names of its contributors may be used to endorse or promote products - * derived from this software without specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY - * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY - * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND - * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -using System; -using System.Collections.Generic; -using System.Data; -using System.Reflection; -using log4net; -using MySql.Data.MySqlClient; -using OpenMetaverse; -using OpenSim.Framework; -using OpenSim.Region.Framework.Interfaces; - -namespace OpenSim.Data.MySQL -{ -// public class MySQLGenericHandler : MySqlFramework where T: class, new() - public class MySQLGenericHandler : MySqlFramework - { -// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - protected virtual Assembly Assembly - { - get { return GetType().Assembly; } - } - - public MySQLGenericHandler(MySqlTransaction trans) : base(trans) - { - CommonConstruct(); - } - - public MySQLGenericHandler(string connectionString) : base(connectionString) - { - CommonConstruct(); - } - - protected void CommonConstruct() - { - // We always use a new connection for any Migrations - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - } - } -/* - private void CheckColumnNames(IDataReader reader) - { - if (m_ColumnNames != null) - return; - - List columnNames = new List(); - - DataTable schemaTable = reader.GetSchemaTable(); - foreach (DataRow row in schemaTable.Rows) - { - if (row["ColumnName"] != null) - columnNames.Add(row["ColumnName"].ToString()); - } - - m_ColumnNames = columnNames; - } -*/ -/* - public virtual T[] Get(string table, string field, string key) - { - return Get(table, new string[] { field }, new string[] { key }); - } - - public virtual T[] Get(string table, string[] fields, string[] keys) - { - return Get(table, fields, keys, String.Empty); - } - - public virtual T[] Get(string table, string[] fields, string[] keys, string options) - { - if (fields.Length != keys.Length) - return new T[0]; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0 ; i < fields.Length ; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("select * from {0} where {1} {2}", - table, 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(); - - cmd.Connection = dbcon; - - using (IDataReader reader = cmd.ExecuteReader()) - { - if (reader == null) - return new T[0]; -/* - - CheckColumnNames(reader); - - while (reader.Read()) - { - 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)) - { - 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 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]); - } - } - - if (m_DataField != null) - { - Dictionary data = - new Dictionary(); - - foreach (string col in m_ColumnNames) - { - data[col] = reader[col].ToString(); - if (data[col] == null) - data[col] = String.Empty; - } - - m_DataField.SetValue(row, data); - } - result.Add(row); - } -*/ -/* - } - cmd.Connection = null; - return result.ToArray(); - } - - public virtual T[] Get(string table, string where) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select * from {0} where {1}", - table, where); - - cmd.CommandText = query; - - return DoQuery(cmd); - } - } -*/ - public virtual bool Delete(string table, string field, string key) - { - return Delete(table, new string[] { field }, new string[] { key }); - } - - public virtual bool Delete(string table, string[] fields, string[] keys) - { -// m_log.DebugFormat( -// "[MYSQL GENERIC TABLE HANDLER]: Delete(string[] fields, string[] keys) invoked with {0}:{1}", -// string.Join(",", fields), string.Join(",", keys)); - - if (fields.Length != keys.Length) - return false; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0 ; i < fields.Length ; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("delete from {0} where {1}", table, where); - - cmd.CommandText = query; - - return ExecuteNonQuery(cmd) > 0; - } - } - - public long GetCount(string table, string field, string key) - { - return GetCount(table, new string[] { field }, new string[] { key }); - } - - public long GetCount(string table, string[] fields, string[] keys) - { - if (fields.Length != keys.Length) - return 0; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0; i < fields.Length; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("select count(*) from {0} where {1}", - table, where); - - cmd.CommandText = query; - - Object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public long GetCount(string table, string where) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select count(*) from {0} where {1}", - table, where); - - cmd.CommandText = query; - - object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public long GetCount(string table) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select count(*) from {0}", table); - - cmd.CommandText = query; - - object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public object DoQueryScalar(MySqlCommand cmd) - { - if (m_trans == null) - { - 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(); - } - } - } -} diff --git a/OpenSim/Data/MySQL/MySQLRaw.cs b/OpenSim/Data/MySQL/MySQLRaw.cs new file mode 100644 index 0000000..4be6d90 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLRaw.cs @@ -0,0 +1,142 @@ +// https://dev.mysql.com/doc/connector-net/en/ + + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using System.Reflection; +using System.Text; +using log4net; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLRaw + { + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private string m_connectString; + + public MySQLRaw(string connect) + { + m_connectString = connect; + } + + public int Count(string table) + { + return Count(table, ""); + } + public int Count(string table, string wher) + { + string query = "SELECT Count(*) FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + int result = -1; + + object r = doScalarQuery(query); + if (r != null) + result = Convert.ToInt32(r); + + return result; + } + + public List< Hashtable > Select(string table, string wher) + { + string query = "SELECT * FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + MySqlDataReader rdr = cmd.ExecuteReader(); + List names = new List(); + DataTable schema = rdr.GetSchemaTable(); + List< Hashtable > list = new List< Hashtable >(); + + foreach (DataRow row in schema.Rows) + { + string tbl = ""; + string nm = ""; + string tp = ""; + foreach (DataColumn col in schema.Columns) + { + if ("BaseTableName" == col.ColumnName) tbl = row[col].ToString(); + if ("ColumnName" == col.ColumnName) nm = row[col].ToString(); + if ("DataType" == col.ColumnName) tp = row[col].ToString(); + } + names.Add(nm); + } + + while (rdr.Read()) + { + Hashtable r = new Hashtable(); + foreach (string name in names) + { + r[name] = rdr[name]; + } + list.Add(r); + } + + rdr.Close(); + dbcon.Close(); + return list; + } + } + + private object doScalarQuery(string query) + { + try + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + Object ret = cmd.ExecuteScalar(); + dbcon.Close(); + return ret; + } + } + catch (MySqlException e) + { + m_log.ErrorFormat("[MYSQL RAW]: Problem connecting to the database {0}", e.Message); + return null; + } + } + + private void doNonQuery(string query) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + cmd.ExecuteNonQuery(); + dbcon.Close(); + } + } + + public void Insert(string table) + { + string query = "INSERT INTO " + table + " (name, age) VALUES('John Smith', '33')"; + doNonQuery(query); + } + + public void Update(string table, string wher) + { + string query = "UPDATE " + table + " SET name='Joe', age='22'"; + if ("" != wher) + query = query + " WHERE " + wher; + doNonQuery(query); + } + + public void Delete(string table, string wher) + { + string query = "DELETE FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + doNonQuery(query); + } + + } +} -- cgit v1.1 From afddecb2830f6afa73f869e120002d12803b3266 Mon Sep 17 00:00:00 2001 From: onefang Date: Wed, 7 Aug 2019 22:12:46 +1000 Subject: More account manager. Select with more parameters. Tables, with member accounts. Print the query values. --- OpenSim/Data/MySQL/MySQLRaw.cs | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLRaw.cs b/OpenSim/Data/MySQL/MySQLRaw.cs index 4be6d90..e8f80aa 100644 --- a/OpenSim/Data/MySQL/MySQLRaw.cs +++ b/OpenSim/Data/MySQL/MySQLRaw.cs @@ -40,11 +40,15 @@ namespace OpenSim.Data.MySQL return result; } - public List< Hashtable > Select(string table, string wher) + public List< Hashtable > Select(string table, string select, string wher, string order) { - string query = "SELECT * FROM " + table; + if ("" == select) + select = "*"; + string query = "SELECT " + select + " FROM " + table; if ("" != wher) query = query + " WHERE " + wher; + if ("" != order) + query = query + " ORDER BY " + order; using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) { -- cgit v1.1 From 7aecb3c7d9262b7dcd43b45736bb008a2f1ca742 Mon Sep 17 00:00:00 2001 From: onefang Date: Thu, 15 Aug 2019 02:18:35 +1000 Subject: Bandaid for ghost users, check and remove them from Presence when they log on. Once again OOP proves it's worse that spaghetti, but cutting up the spaghetti and hiding it all over the kitchen. Note the commented out bits from GateKeeperService.cs, this stuff is checked TWICE, though this time is for HGers, or not. --- OpenSim/Data/MySQL/MySQLPresenceData.cs | 13 +++++++++++-- 1 file changed, 11 insertions(+), 2 deletions(-) (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLPresenceData.cs b/OpenSim/Data/MySQL/MySQLPresenceData.cs index 70aca5f..b9114eb 100644 --- a/OpenSim/Data/MySQL/MySQLPresenceData.cs +++ b/OpenSim/Data/MySQL/MySQLPresenceData.cs @@ -52,8 +52,17 @@ namespace OpenSim.Data.MySQL public PresenceData Get(UUID sessionID) { - PresenceData[] ret = Get("SessionID", - sessionID.ToString()); + PresenceData[] ret = Get("SessionID", sessionID.ToString()); + + if (ret.Length == 0) + return null; + + return ret[0]; + } + + public PresenceData GetByUser(UUID userID) + { + PresenceData[] ret = Get("UserID", userID.ToString()); if (ret.Length == 0) return null; -- cgit v1.1 From fe55626fcf7c1fda244db5e0c4f91ad7d0704c64 Mon Sep 17 00:00:00 2001 From: onefang Date: Thu, 22 Aug 2019 15:46:57 +1000 Subject: Function for MYSQL joins. --- OpenSim/Data/MySQL/MySQLRaw.cs | 51 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 51 insertions(+) (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLRaw.cs b/OpenSim/Data/MySQL/MySQLRaw.cs index e8f80aa..bb8c96c 100644 --- a/OpenSim/Data/MySQL/MySQLRaw.cs +++ b/OpenSim/Data/MySQL/MySQLRaw.cs @@ -40,6 +40,57 @@ namespace OpenSim.Data.MySQL return result; } + public List< Hashtable > Join(string table, string select, string join, string wher, string order) + { + if ("" == select) + select = "*"; + string query = "SELECT " + select + " FROM " + table; + if ("" != join) + query = query + " " + join; + if ("" != wher) + query = query + " WHERE " + wher; + if ("" != order) + query = query + " ORDER BY " + order; + + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + MySqlDataReader rdr = cmd.ExecuteReader(); + List names = new List(); + DataTable schema = rdr.GetSchemaTable(); + List< Hashtable > list = new List< Hashtable >(); + + foreach (DataRow row in schema.Rows) + { + string tbl = ""; + string nm = ""; + string tp = ""; + foreach (DataColumn col in schema.Columns) + { + if ("BaseTableName" == col.ColumnName) tbl = row[col].ToString(); + if ("ColumnName" == col.ColumnName) nm = row[col].ToString(); + if ("DataType" == col.ColumnName) tp = row[col].ToString(); + } + names.Add(nm); + } + + while (rdr.Read()) + { + Hashtable r = new Hashtable(); + foreach (string name in names) + { + r[name] = rdr[name]; + } + list.Add(r); + } + + rdr.Close(); + dbcon.Close(); + return list; + } + } + public List< Hashtable > Select(string table, string select, string wher, string order) { if ("" == select) -- cgit v1.1