From d1c3f8eef58b29eb8760eeb1ac03852a2387f927 Mon Sep 17 00:00:00 2001 From: Oren Hurvitz Date: Mon, 31 Mar 2014 11:53:12 +0300 Subject: Added assets service method AssetsExist(), which returns whether the given list of assets exist. This method is used to optimize sending assets with embedded assets: e.g., when a Hypergrid visitor takes an item into the inventory. --- OpenSim/Data/AssetDataBase.cs | 3 +-- OpenSim/Data/IAssetData.cs | 2 +- OpenSim/Data/IXAssetDataPlugin.cs | 2 +- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 35 ++++++++++++++++++++++------ OpenSim/Data/MySQL/MySQLAssetData.cs | 42 ++++++++++++++++------------------ OpenSim/Data/MySQL/MySQLXAssetData.cs | 40 +++++++++++++++----------------- OpenSim/Data/PGSQL/PGSQLAssetData.cs | 35 ++++++++++++++++++++++------ OpenSim/Data/PGSQL/PGSQLXAssetData.cs | 37 ++++++++++++++++++++++++++++++ OpenSim/Data/SQLite/SQLiteAssetData.cs | 37 ++++++++++++++++++------------ OpenSim/Data/Tests/AssetTests.cs | 16 +++++++------ 10 files changed, 166 insertions(+), 83 deletions(-) (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/AssetDataBase.cs b/OpenSim/Data/AssetDataBase.cs index e1a810c..1bb432c 100644 --- a/OpenSim/Data/AssetDataBase.cs +++ b/OpenSim/Data/AssetDataBase.cs @@ -37,9 +37,8 @@ namespace OpenSim.Data public abstract class AssetDataBase : IAssetDataPlugin { public abstract AssetBase GetAsset(UUID uuid); - public abstract void StoreAsset(AssetBase asset); - public abstract bool ExistsAsset(UUID uuid); + public abstract bool[] AssetsExist(UUID[] uuids); public abstract List FetchAssetMetadataSet(int start, int count); diff --git a/OpenSim/Data/IAssetData.cs b/OpenSim/Data/IAssetData.cs index f31b215c..a41e310 100644 --- a/OpenSim/Data/IAssetData.cs +++ b/OpenSim/Data/IAssetData.cs @@ -35,7 +35,7 @@ namespace OpenSim.Data { AssetBase GetAsset(UUID uuid); void StoreAsset(AssetBase asset); - bool ExistsAsset(UUID uuid); + bool[] AssetsExist(UUID[] uuids); List FetchAssetMetadataSet(int start, int count); void Initialise(string connect); bool Delete(string id); diff --git a/OpenSim/Data/IXAssetDataPlugin.cs b/OpenSim/Data/IXAssetDataPlugin.cs index 74ad6f4..2d24797 100644 --- a/OpenSim/Data/IXAssetDataPlugin.cs +++ b/OpenSim/Data/IXAssetDataPlugin.cs @@ -39,7 +39,7 @@ namespace OpenSim.Data { AssetBase GetAsset(UUID uuid); void StoreAsset(AssetBase asset); - bool ExistsAsset(UUID uuid); + bool[] AssetsExist(UUID[] uuids); List FetchAssetMetadataSet(int start, int count); void Initialise(string connect); bool Delete(string id); diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index f3e008d..ce70396 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -225,17 +225,38 @@ namespace OpenSim.Data.MSSQL // } /// - /// Check if asset exist in m_database + /// Check if the assets exist in the database. /// - /// - /// true if exist. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { - if (GetAsset(uuid) != null) + if (uuids.Length == 0) + return new bool[0]; + + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids); + + using (SqlConnection conn = new SqlConnection(m_connectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) { - return true; + conn.Open(); + using (SqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + UUID id = DBGuid.FromDB(reader["id"]); + exist.Add(id); + } + } } - return false; + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + return results; } /// diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs index 21362b9..c96139d 100644 --- a/OpenSim/Data/MySQL/MySQLAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -257,46 +257,44 @@ namespace OpenSim.Data.MySQL } /// - /// Check if the asset exists in the database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// true if it exists, false otherwise. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { -// m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid); + if (uuids.Length == 0) + return new bool[0]; - bool assetExists = false; + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids); lock (m_dbLock) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon)) + using (MySqlCommand cmd = new MySqlCommand(sql, dbcon)) { - cmd.Parameters.AddWithValue("?id", uuid.ToString()); - - try + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + while (dbReader.Read()) { - if (dbReader.Read()) - { -// m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid); - assetExists = true; - } + UUID id = DBGuid.FromDB(dbReader["id"]); + exist.Add(id); } } - catch (Exception e) - { - m_log.Error( - string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", uuid), e); - } } } } - return assetExists; + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + + return results; } /// diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs index 91389ce..1bf6a9a 100644 --- a/OpenSim/Data/MySQL/MySQLXAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLXAssetData.cs @@ -397,45 +397,43 @@ namespace OpenSim.Data.MySQL } /// - /// Check if the asset exists in the database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// true if it exists, false otherwise. - public bool ExistsAsset(UUID uuid) + /// The asset UUID's + /// For each asset: true if it exists, false otherwise + public bool[] AssetsExist(UUID[] uuids) { -// m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid); + if (uuids.Length == 0) + return new bool[0]; + + HashSet exists = new HashSet(); - bool assetExists = false; + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT ID FROM assets WHERE ID IN ({0})", ids); lock (m_dbLock) { using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { dbcon.Open(); - using (MySqlCommand cmd = new MySqlCommand("SELECT ID FROM XAssetsMeta WHERE ID=?ID", dbcon)) + using (MySqlCommand cmd = new MySqlCommand(sql, dbcon)) { - cmd.Parameters.AddWithValue("?ID", uuid.ToString()); - - try + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + while (dbReader.Read()) { - if (dbReader.Read()) - { -// m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid); - assetExists = true; - } + UUID id = DBGuid.FromDB(dbReader["ID"]); + exists.Add(id); } } - catch (Exception e) - { - m_log.Error(string.Format("[XASSETS DB]: MySql failure fetching asset {0}", uuid), e); - } } } } - return assetExists; + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exists.Contains(uuids[i]); + return results; } diff --git a/OpenSim/Data/PGSQL/PGSQLAssetData.cs b/OpenSim/Data/PGSQL/PGSQLAssetData.cs index ab74856..ca18dc9 100644 --- a/OpenSim/Data/PGSQL/PGSQLAssetData.cs +++ b/OpenSim/Data/PGSQL/PGSQLAssetData.cs @@ -231,17 +231,38 @@ namespace OpenSim.Data.PGSQL // } /// - /// Check if asset exist in m_database + /// Check if the assets exist in the database. /// - /// - /// true if exist. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { - if (GetAsset(uuid) != null) + if (uuids.Length == 0) + return new bool[0]; + + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) { - return true; + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + UUID id = DBGuid.FromDB(reader["id"]); + exist.Add(id); + } + } } - return false; + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + return results; } /// diff --git a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs index e959619..c6cebff 100644 --- a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs +++ b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs @@ -407,6 +407,43 @@ namespace OpenSim.Data.PGSQL } /// + /// Check if the assets exist in the database. + /// + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public bool[] AssetsExist(UUID[] uuids) + { + if (uuids.Length == 0) + return new bool[0]; + + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID"" IN ({0})", ids); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + UUID id = DBGuid.FromDB(reader["id"]); + exist.Add(id); + } + } + } + } + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + return results; + } + + /// /// Check if the asset exists in the database /// /// The asset UUID diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs index c32982e..1f32376 100644 --- a/OpenSim/Data/SQLite/SQLiteAssetData.cs +++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs @@ -152,7 +152,7 @@ namespace OpenSim.Data.SQLite } //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString()); - if (ExistsAsset(asset.FullID)) + if (AssetsExist(new[] { asset.FullID })[0]) { //LogAssetLoad(asset); @@ -214,32 +214,39 @@ namespace OpenSim.Data.SQLite // } /// - /// Check if an asset exist in database + /// Check if the assets exist in the database. /// - /// The asset UUID - /// True if exist, or false. - override public bool ExistsAsset(UUID uuid) + /// The assets' IDs + /// For each asset: true if it exists, false otherwise + public override bool[] AssetsExist(UUID[] uuids) { - lock (this) + if (uuids.Length == 0) + return new bool[0]; + + HashSet exist = new HashSet(); + + string ids = "'" + string.Join("','", uuids) + "'"; + string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids); + + lock (this) { using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn)) { - cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString())); using (IDataReader reader = cmd.ExecuteReader()) { - if (reader.Read()) - { - reader.Close(); - return true; - } - else + while (reader.Read()) { - reader.Close(); - return false; + UUID id = new UUID((string)reader["UUID"]); + exist.Add(id); } } } } + + bool[] results = new bool[uuids.Length]; + for (int i = 0; i < uuids.Length; i++) + results[i] = exist.Contains(uuids[i]); + return results; } /// diff --git a/OpenSim/Data/Tests/AssetTests.cs b/OpenSim/Data/Tests/AssetTests.cs index 8cb2ee0..d778d1c 100644 --- a/OpenSim/Data/Tests/AssetTests.cs +++ b/OpenSim/Data/Tests/AssetTests.cs @@ -107,10 +107,11 @@ namespace OpenSim.Data.Tests public void T001_LoadEmpty() { TestHelpers.InMethod(); - - Assert.That(m_db.ExistsAsset(uuid1), Is.False); - Assert.That(m_db.ExistsAsset(uuid2), Is.False); - Assert.That(m_db.ExistsAsset(uuid3), Is.False); + + bool[] exist = m_db.AssetsExist(new[] { uuid1, uuid2, uuid3 }); + Assert.IsFalse(exist[0]); + Assert.IsFalse(exist[1]); + Assert.IsFalse(exist[2]); } [Test] @@ -159,9 +160,10 @@ namespace OpenSim.Data.Tests AssetBase a3b = m_db.GetAsset(uuid3); Assert.That(a3b, Constraints.PropertyCompareConstraint(a3a)); - Assert.That(m_db.ExistsAsset(uuid1), Is.True); - Assert.That(m_db.ExistsAsset(uuid2), Is.True); - Assert.That(m_db.ExistsAsset(uuid3), Is.True); + bool[] exist = m_db.AssetsExist(new[] { uuid1, uuid2, uuid3 }); + Assert.IsTrue(exist[0]); + Assert.IsTrue(exist[1]); + Assert.IsTrue(exist[2]); List metadatas = m_db.FetchAssetMetadataSet(0, 1000); -- cgit v1.1