PGSQLXAssetData.cs 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections.Generic;
  29. using System.Data;
  30. using System.IO;
  31. using System.IO.Compression;
  32. using System.Reflection;
  33. using System.Security.Cryptography;
  34. using System.Text;
  35. using log4net;
  36. using OpenMetaverse;
  37. using OpenSim.Framework;
  38. using OpenSim.Data;
  39. using Npgsql;
  40. namespace OpenSim.Data.PGSQL
  41. {
  42. public class PGSQLXAssetData : IXAssetDataPlugin
  43. {
  44. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  45. protected virtual Assembly Assembly
  46. {
  47. get { return GetType().Assembly; }
  48. }
  49. /// <summary>
  50. /// Number of days that must pass before we update the access time on an asset when it has been fetched.
  51. /// </summary>
  52. private const int DaysBetweenAccessTimeUpdates = 30;
  53. private bool m_enableCompression = false;
  54. private string m_connectionString;
  55. private object m_dbLock = new object();
  56. /// <summary>
  57. /// We can reuse this for all hashing since all methods are single-threaded through m_dbBLock
  58. /// </summary>
  59. private HashAlgorithm hasher = new SHA256CryptoServiceProvider();
  60. #region IPlugin Members
  61. public string Version { get { return "1.0.0.0"; } }
  62. /// <summary>
  63. /// <para>Initialises Asset interface</para>
  64. /// <para>
  65. /// <list type="bullet">
  66. /// <item>Loads and initialises the PGSQL storage plugin.</item>
  67. /// <item>Warns and uses the obsolete pgsql_connection.ini if connect string is empty.</item>
  68. /// <item>Check for migration</item>
  69. /// </list>
  70. /// </para>
  71. /// </summary>
  72. /// <param name="connect">connect string</param>
  73. public void Initialise(string connect)
  74. {
  75. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  76. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  77. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  78. m_log.ErrorFormat("[PGSQL XASSETDATA]: THIS PLUGIN IS STRICTLY EXPERIMENTAL.");
  79. m_log.ErrorFormat("[PGSQL XASSETDATA]: DO NOT USE FOR ANY DATA THAT YOU DO NOT MIND LOSING.");
  80. m_log.ErrorFormat("[PGSQL XASSETDATA]: DATABASE TABLES CAN CHANGE AT ANY TIME, CAUSING EXISTING DATA TO BE LOST.");
  81. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  82. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  83. m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
  84. m_connectionString = connect;
  85. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  86. {
  87. dbcon.Open();
  88. Migration m = new Migration(dbcon, Assembly, "XAssetStore");
  89. m.Update();
  90. }
  91. }
  92. public void Initialise()
  93. {
  94. throw new NotImplementedException();
  95. }
  96. public void Dispose() { }
  97. /// <summary>
  98. /// The name of this DB provider
  99. /// </summary>
  100. public string Name
  101. {
  102. get { return "PGSQL XAsset storage engine"; }
  103. }
  104. #endregion
  105. #region IAssetDataPlugin Members
  106. /// <summary>
  107. /// Fetch Asset <paramref name="assetID"/> from database
  108. /// </summary>
  109. /// <param name="assetID">Asset UUID to fetch</param>
  110. /// <returns>Return the asset</returns>
  111. /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
  112. public AssetBase GetAsset(UUID assetID)
  113. {
  114. // m_log.DebugFormat("[PGSQL XASSET DATA]: Looking for asset {0}", assetID);
  115. AssetBase asset = null;
  116. lock (m_dbLock)
  117. {
  118. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  119. {
  120. dbcon.Open();
  121. using (NpgsqlCommand cmd = new NpgsqlCommand(
  122. @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Local"", ""Temporary"", ""AssetFlags"", ""CreatorID"", ""Data""
  123. FROM XAssetsMeta
  124. JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ""ID""=:ID",
  125. dbcon))
  126. {
  127. cmd.Parameters.AddWithValue("ID", assetID.ToString());
  128. try
  129. {
  130. using (NpgsqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  131. {
  132. if (dbReader.Read())
  133. {
  134. asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString());
  135. asset.Data = (byte[])dbReader["Data"];
  136. asset.Description = (string)dbReader["Description"];
  137. string local = dbReader["Local"].ToString();
  138. if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
  139. asset.Local = true;
  140. else
  141. asset.Local = false;
  142. asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
  143. asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
  144. if (m_enableCompression)
  145. {
  146. using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress))
  147. {
  148. MemoryStream outputStream = new MemoryStream();
  149. WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
  150. // int compressedLength = asset.Data.Length;
  151. asset.Data = outputStream.ToArray();
  152. // m_log.DebugFormat(
  153. // "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}",
  154. // asset.ID, asset.Name, asset.Data.Length, compressedLength);
  155. }
  156. }
  157. UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
  158. }
  159. }
  160. }
  161. catch (Exception e)
  162. {
  163. m_log.Error(string.Format("[PGSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
  164. }
  165. }
  166. }
  167. }
  168. return asset;
  169. }
  170. /// <summary>
  171. /// Create an asset in database, or update it if existing.
  172. /// </summary>
  173. /// <param name="asset">Asset UUID to create</param>
  174. /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
  175. public void StoreAsset(AssetBase asset)
  176. {
  177. // m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
  178. lock (m_dbLock)
  179. {
  180. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  181. {
  182. dbcon.Open();
  183. using (NpgsqlTransaction transaction = dbcon.BeginTransaction())
  184. {
  185. string assetName = asset.Name;
  186. if (asset.Name.Length > 64)
  187. {
  188. assetName = asset.Name.Substring(0, 64);
  189. m_log.WarnFormat(
  190. "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
  191. asset.Name, asset.ID, asset.Name.Length, assetName.Length);
  192. }
  193. string assetDescription = asset.Description;
  194. if (asset.Description.Length > 64)
  195. {
  196. assetDescription = asset.Description.Substring(0, 64);
  197. m_log.WarnFormat(
  198. "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
  199. asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
  200. }
  201. if (m_enableCompression)
  202. {
  203. MemoryStream outputStream = new MemoryStream();
  204. using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false))
  205. {
  206. // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue));
  207. // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream.
  208. compressionStream.Close();
  209. byte[] compressedData = outputStream.ToArray();
  210. asset.Data = compressedData;
  211. }
  212. }
  213. byte[] hash = hasher.ComputeHash(asset.Data);
  214. // m_log.DebugFormat(
  215. // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
  216. // asset.ID, asset.Name, hash, compressedData.Length);
  217. try
  218. {
  219. using (NpgsqlCommand cmd =
  220. new NpgsqlCommand(
  221. @"insert INTO XAssetsMeta(""ID"", ""Hash"", ""Name"", ""Description"", ""AssetType"", ""Local"", ""Temporary"", ""CreateTime"", ""AccessTime"", ""AssetFlags"", ""CreatorID"")
  222. Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
  223. where not exists( Select ""ID"" from XAssetsMeta where ""ID"" = :ID ;
  224. update XAssetsMeta
  225. set ""ID"" = :ID, ""Hash"" = :Hash, ""Name"" = :Name, ""Description"" = :Description,
  226. ""AssetType"" = :AssetType, ""Local"" = :Local, ""Temporary"" = :Temporary, ""CreateTime"" = :CreateTime,
  227. ""AccessTime"" = :AccessTime, ""AssetFlags"" = :AssetFlags, ""CreatorID"" = :CreatorID
  228. where ""ID"" = :ID;
  229. ",
  230. dbcon))
  231. {
  232. // create unix epoch time
  233. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  234. cmd.Parameters.AddWithValue("ID", asset.ID);
  235. cmd.Parameters.AddWithValue("Hash", hash);
  236. cmd.Parameters.AddWithValue("Name", assetName);
  237. cmd.Parameters.AddWithValue("Description", assetDescription);
  238. cmd.Parameters.AddWithValue("AssetType", asset.Type);
  239. cmd.Parameters.AddWithValue("Local", asset.Local);
  240. cmd.Parameters.AddWithValue("Temporary", asset.Temporary);
  241. cmd.Parameters.AddWithValue("CreateTime", now);
  242. cmd.Parameters.AddWithValue("AccessTime", now);
  243. cmd.Parameters.AddWithValue("CreatorID", asset.Metadata.CreatorID);
  244. cmd.Parameters.AddWithValue("AssetFlags", (int)asset.Flags);
  245. cmd.ExecuteNonQuery();
  246. }
  247. }
  248. catch (Exception e)
  249. {
  250. m_log.ErrorFormat("[ASSET DB]: PGSQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
  251. asset.FullID, asset.Name, e.Message);
  252. transaction.Rollback();
  253. return;
  254. }
  255. if (!ExistsData(dbcon, transaction, hash))
  256. {
  257. try
  258. {
  259. using (NpgsqlCommand cmd =
  260. new NpgsqlCommand(
  261. @"INSERT INTO XAssetsData(""Hash"", ""Data"") VALUES(:Hash, :Data)",
  262. dbcon))
  263. {
  264. cmd.Parameters.AddWithValue("Hash", hash);
  265. cmd.Parameters.AddWithValue("Data", asset.Data);
  266. cmd.ExecuteNonQuery();
  267. }
  268. }
  269. catch (Exception e)
  270. {
  271. m_log.ErrorFormat("[XASSET DB]: PGSQL failure creating asset data {0} with name \"{1}\". Error: {2}",
  272. asset.FullID, asset.Name, e.Message);
  273. transaction.Rollback();
  274. return;
  275. }
  276. }
  277. transaction.Commit();
  278. }
  279. }
  280. }
  281. }
  282. /// <summary>
  283. /// Updates the access time of the asset if it was accessed above a given threshhold amount of time.
  284. /// </summary>
  285. /// <remarks>
  286. /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done
  287. /// over the threshold time to avoid excessive database writes as assets are fetched.
  288. /// </remarks>
  289. /// <param name='asset'></param>
  290. /// <param name='accessTime'></param>
  291. private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime)
  292. {
  293. DateTime now = DateTime.UtcNow;
  294. if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
  295. return;
  296. lock (m_dbLock)
  297. {
  298. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  299. {
  300. dbcon.Open();
  301. NpgsqlCommand cmd =
  302. new NpgsqlCommand(@"update XAssetsMeta set ""AccessTime""=:AccessTime where ID=:ID", dbcon);
  303. try
  304. {
  305. using (cmd)
  306. {
  307. // create unix epoch time
  308. cmd.Parameters.AddWithValue("ID", assetMetadata.ID);
  309. cmd.Parameters.AddWithValue("AccessTime", (int)Utils.DateTimeToUnixTime(now));
  310. cmd.ExecuteNonQuery();
  311. }
  312. }
  313. catch (Exception e)
  314. {
  315. m_log.ErrorFormat(
  316. "[XASSET PGSQL DB]: Failure updating access_time for asset {0} with name {1} : {2}",
  317. assetMetadata.ID, assetMetadata.Name, e.Message);
  318. }
  319. }
  320. }
  321. }
  322. /// <summary>
  323. /// We assume we already have the m_dbLock.
  324. /// </summary>
  325. /// TODO: need to actually use the transaction.
  326. /// <param name="dbcon"></param>
  327. /// <param name="transaction"></param>
  328. /// <param name="hash"></param>
  329. /// <returns></returns>
  330. private bool ExistsData(NpgsqlConnection dbcon, NpgsqlTransaction transaction, byte[] hash)
  331. {
  332. // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
  333. bool exists = false;
  334. using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""Hash"" FROM XAssetsData WHERE ""Hash""=:Hash", dbcon))
  335. {
  336. cmd.Parameters.AddWithValue("Hash", hash);
  337. try
  338. {
  339. using (NpgsqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  340. {
  341. if (dbReader.Read())
  342. {
  343. // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
  344. exists = true;
  345. }
  346. }
  347. }
  348. catch (Exception e)
  349. {
  350. m_log.ErrorFormat(
  351. "[XASSETS DB]: PGSql failure in ExistsData fetching hash {0}. Exception {1}{2}",
  352. hash, e.Message, e.StackTrace);
  353. }
  354. }
  355. return exists;
  356. }
  357. /// <summary>
  358. /// Check if the asset exists in the database
  359. /// </summary>
  360. /// <param name="uuid">The asset UUID</param>
  361. /// <returns>true if it exists, false otherwise.</returns>
  362. public bool ExistsAsset(UUID uuid)
  363. {
  364. // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
  365. bool assetExists = false;
  366. lock (m_dbLock)
  367. {
  368. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  369. {
  370. dbcon.Open();
  371. using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID""=:ID", dbcon))
  372. {
  373. cmd.Parameters.AddWithValue("ID", uuid.ToString());
  374. try
  375. {
  376. using (NpgsqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  377. {
  378. if (dbReader.Read())
  379. {
  380. // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
  381. assetExists = true;
  382. }
  383. }
  384. }
  385. catch (Exception e)
  386. {
  387. m_log.Error(string.Format("[XASSETS DB]: PGSql failure fetching asset {0}", uuid), e);
  388. }
  389. }
  390. }
  391. }
  392. return assetExists;
  393. }
  394. /// <summary>
  395. /// Returns a list of AssetMetadata objects. The list is a subset of
  396. /// the entire data set offset by <paramref name="start" /> containing
  397. /// <paramref name="count" /> elements.
  398. /// </summary>
  399. /// <param name="start">The number of results to discard from the total data set.</param>
  400. /// <param name="count">The number of rows the returned list should contain.</param>
  401. /// <returns>A list of AssetMetadata objects.</returns>
  402. public List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  403. {
  404. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  405. lock (m_dbLock)
  406. {
  407. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  408. {
  409. dbcon.Open();
  410. NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Temporary"", ""ID"", ""AssetFlags"", ""CreatorID""
  411. FROM XAssetsMeta
  412. LIMIT :start, :count", dbcon);
  413. cmd.Parameters.AddWithValue("start", start);
  414. cmd.Parameters.AddWithValue("count", count);
  415. try
  416. {
  417. using (NpgsqlDataReader dbReader = cmd.ExecuteReader())
  418. {
  419. while (dbReader.Read())
  420. {
  421. AssetMetadata metadata = new AssetMetadata();
  422. metadata.Name = (string)dbReader["Name"];
  423. metadata.Description = (string)dbReader["Description"];
  424. metadata.Type = (sbyte)dbReader["AssetType"];
  425. metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
  426. metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
  427. metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
  428. metadata.CreatorID = dbReader["CreatorID"].ToString();
  429. // We'll ignore this for now - it appears unused!
  430. // metadata.SHA1 = dbReader["hash"]);
  431. UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
  432. retList.Add(metadata);
  433. }
  434. }
  435. }
  436. catch (Exception e)
  437. {
  438. m_log.Error("[XASSETS DB]: PGSql failure fetching asset set" + Environment.NewLine + e.ToString());
  439. }
  440. }
  441. }
  442. return retList;
  443. }
  444. public bool Delete(string id)
  445. {
  446. // m_log.DebugFormat("[XASSETS DB]: Deleting asset {0}", id);
  447. lock (m_dbLock)
  448. {
  449. using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
  450. {
  451. dbcon.Open();
  452. using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where ""ID""=:ID", dbcon))
  453. {
  454. cmd.Parameters.AddWithValue("ID", id);
  455. cmd.ExecuteNonQuery();
  456. }
  457. // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we
  458. // keep a reference count (?)
  459. }
  460. }
  461. return true;
  462. }
  463. #endregion
  464. }
  465. }