29 using System.Collections.Generic;
32 using System.IO.Compression;
33 using System.Reflection;
34 using System.Security.Cryptography;
37 using MySql.Data.MySqlClient;
39 using OpenSim.Framework;
42 namespace OpenSim.Data.MySQL
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48 protected virtual Assembly Assembly
50 get {
return GetType().Assembly; }
56 private const int DaysBetweenAccessTimeUpdates = 30;
58 private bool m_enableCompression =
false;
59 private string m_connectionString;
64 private HashAlgorithm hasher =
new SHA256CryptoServiceProvider();
66 #region IPlugin Members
68 public string Version {
get {
return "1.0.0.0"; } }
83 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
84 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
85 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
86 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: THIS PLUGIN IS STRICTLY EXPERIMENTAL.");
87 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: DO NOT USE FOR ANY DATA THAT YOU DO NOT MIND LOSING.");
88 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: DATABASE TABLES CAN CHANGE AT ANY TIME, CAUSING EXISTING DATA TO BE LOST.");
89 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
90 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
91 m_log.ErrorFormat(
"[MYSQL XASSETDATA]: ***********************************************************");
93 m_connectionString = connect;
95 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
105 throw new NotImplementedException();
115 get {
return "MySQL XAsset storage engine"; }
120 #region IAssetDataPlugin Members
134 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
138 using (MySqlCommand cmd =
new MySqlCommand(
139 "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID",
142 cmd.Parameters.AddWithValue(
"?ID", assetID.ToString());
146 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
150 asset =
new AssetBase(assetID, (
string)dbReader[
"Name"], (sbyte)dbReader[
"AssetType"], dbReader[
"CreatorID"].ToString());
151 asset.Data = (byte[])dbReader[
"Data"];
152 asset.Description = (string)dbReader[
"Description"];
154 string local = dbReader[
"Local"].ToString();
155 if (local.Equals(
"1") || local.Equals(
"true", StringComparison.InvariantCultureIgnoreCase))
160 asset.Temporary = Convert.ToBoolean(dbReader[
"Temporary"]);
161 asset.Flags = (
AssetFlags)Convert.ToInt32(dbReader[
"AssetFlags"]);
163 if (m_enableCompression)
167 MemoryStream outputStream =
new MemoryStream();
168 WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
170 asset.Data = outputStream.ToArray();
178 UpdateAccessTime(asset.
Metadata, (
int)dbReader[
"AccessTime"]);
184 m_log.Error(string.Format(
"[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
201 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
205 using (MySqlTransaction transaction = dbcon.BeginTransaction())
207 string assetName = asset.Name;
210 assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
212 "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
213 asset.Name, asset.ID, asset.Name.Length, assetName.Length);
216 string assetDescription = asset.Description;
219 assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
221 "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
222 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
225 if (m_enableCompression)
227 MemoryStream outputStream =
new MemoryStream();
233 compressionStream.Close();
234 byte[] compressedData = outputStream.ToArray();
235 asset.Data = compressedData;
239 byte[] hash = hasher.ComputeHash(asset.Data);
247 using (MySqlCommand cmd =
249 "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" +
250 "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)",
254 int now = (int)
Utils.DateTimeToUnixTime(DateTime.UtcNow);
255 cmd.Parameters.AddWithValue(
"?ID", asset.ID);
256 cmd.Parameters.AddWithValue(
"?Hash", hash);
257 cmd.Parameters.AddWithValue(
"?Name", assetName);
258 cmd.Parameters.AddWithValue(
"?Description", assetDescription);
259 cmd.Parameters.AddWithValue(
"?AssetType", asset.Type);
260 cmd.Parameters.AddWithValue(
"?Local", asset.Local);
261 cmd.Parameters.AddWithValue(
"?Temporary", asset.Temporary);
262 cmd.Parameters.AddWithValue(
"?CreateTime", now);
263 cmd.Parameters.AddWithValue(
"?AccessTime", now);
264 cmd.Parameters.AddWithValue(
"?CreatorID", asset.Metadata.CreatorID);
265 cmd.Parameters.AddWithValue(
"?AssetFlags", (int)asset.
Flags);
266 cmd.ExecuteNonQuery();
271 m_log.ErrorFormat(
"[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
272 asset.FullID, asset.Name, e.Message);
274 transaction.Rollback();
279 if (!ExistsData(dbcon, transaction, hash))
283 using (MySqlCommand cmd =
285 "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)",
288 cmd.Parameters.AddWithValue(
"?Hash", hash);
289 cmd.Parameters.AddWithValue(
"?Data", asset.Data);
290 cmd.ExecuteNonQuery();
295 m_log.ErrorFormat(
"[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}",
296 asset.FullID, asset.Name, e.Message);
298 transaction.Rollback();
304 transaction.Commit();
318 private void UpdateAccessTime(
AssetMetadata assetMetadata,
int accessTime)
320 DateTime now = DateTime.UtcNow;
322 if ((now -
Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
325 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
329 new MySqlCommand(
"update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon);
336 cmd.Parameters.AddWithValue(
"?ID", assetMetadata.ID);
337 cmd.Parameters.AddWithValue(
"?AccessTime", (int)
Utils.DateTimeToUnixTime(now));
338 cmd.ExecuteNonQuery();
344 "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}",
345 assetMetadata.ID, assetMetadata.Name);
358 private bool ExistsData(MySqlConnection dbcon, MySqlTransaction transaction, byte[] hash)
364 using (MySqlCommand cmd =
new MySqlCommand(
"SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon))
366 cmd.Parameters.AddWithValue(
"?Hash", hash);
370 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
382 "[XASSETS DB]: MySql failure in ExistsData fetching hash {0}. Exception {1}{2}",
383 hash, e.Message, e.StackTrace);
397 if (uuids.Length == 0)
400 HashSet<UUID> exists =
new HashSet<UUID>();
402 string ids =
"'" + string.Join(
"','", uuids) +
"'";
403 string sql = string.Format(
"SELECT ID FROM assets WHERE ID IN ({0})", ids);
405 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
408 using (MySqlCommand cmd =
new MySqlCommand(sql, dbcon))
410 using (MySqlDataReader dbReader = cmd.ExecuteReader())
412 while (dbReader.Read())
414 UUID
id = DBGuid.FromDB(dbReader[
"ID"]);
421 bool[] results =
new bool[uuids.Length];
422 for (
int i = 0; i < uuids.Length; i++)
423 results[i] = exists.Contains(uuids[i]);
438 List<AssetMetadata> retList =
new List<AssetMetadata>(count);
440 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
443 MySqlCommand cmd =
new MySqlCommand(
"SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon);
444 cmd.Parameters.AddWithValue(
"?start", start);
445 cmd.Parameters.AddWithValue(
"?count", count);
449 using (MySqlDataReader dbReader = cmd.ExecuteReader())
451 while (dbReader.Read())
454 metadata.Name = (string)dbReader[
"Name"];
455 metadata.Description = (string)dbReader[
"Description"];
456 metadata.Type = (sbyte)dbReader[
"AssetType"];
457 metadata.Temporary = Convert.ToBoolean(dbReader[
"Temporary"]);
458 metadata.Flags = (
AssetFlags)Convert.ToInt32(dbReader[
"AssetFlags"]);
459 metadata.FullID = DBGuid.FromDB(dbReader[
"ID"]);
460 metadata.CreatorID = dbReader[
"CreatorID"].ToString();
465 UpdateAccessTime(metadata, (
int)dbReader[
"AccessTime"]);
467 retList.Add(metadata);
473 m_log.Error(
"[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
484 using (MySqlConnection dbcon =
new MySqlConnection(m_connectionString))
488 using (MySqlCommand cmd =
new MySqlCommand(
"delete from XAssetsMeta where ID=?ID", dbcon))
490 cmd.Parameters.AddWithValue(
"?ID", id);
491 cmd.ExecuteNonQuery();
void Initialise()
Default-initialises the plugin
OpenSim.Server.Handlers.Simulation.Utils Utils
bool[] AssetsExist(UUID[] uuids)
Check if the assets exist in the database.
Ionic.Zlib.GZipStream GZipStream
Asset class. All Assets are reference by this class or a class derived from this class ...
static readonly int MAX_ASSET_NAME
static readonly int MAX_ASSET_DESC
List< AssetMetadata > FetchAssetMetadataSet(int start, int count)
Returns a list of AssetMetadata objects. The list is a subset of the entire data set offset by start ...
void StoreAsset(AssetBase asset)
Create an asset in database, or update it if existing.
This interface exists to distinguish between the normal IAssetDataPlugin and the one used by XAssetSe...
Ionic.Zlib.CompressionMode CompressionMode
void Initialise(string connect)
AssetBase GetAsset(UUID assetID)
Fetch Asset assetID from database