1
0

MySQLAssetData.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365
  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.Data;
  29. using System.Reflection;
  30. using System.Collections.Generic;
  31. using log4net;
  32. using MySql.Data.MySqlClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Data;
  36. namespace OpenSim.Data.MySQL
  37. {
  38. /// <summary>
  39. /// A MySQL Interface for the Asset Server
  40. /// </summary>
  41. public class MySQLAssetData : AssetDataBase
  42. {
  43. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  44. private string m_connectionString;
  45. private object m_dbLock = new object();
  46. #region IPlugin Members
  47. public override string Version { get { return "1.0.0.0"; } }
  48. /// <summary>
  49. /// <para>Initialises Asset interface</para>
  50. /// <para>
  51. /// <list type="bullet">
  52. /// <item>Loads and initialises the MySQL storage plugin.</item>
  53. /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
  54. /// <item>Check for migration</item>
  55. /// </list>
  56. /// </para>
  57. /// </summary>
  58. /// <param name="connect">connect string</param>
  59. public override void Initialise(string connect)
  60. {
  61. m_connectionString = connect;
  62. // This actually does the roll forward assembly stuff
  63. Assembly assem = GetType().Assembly;
  64. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  65. {
  66. dbcon.Open();
  67. Migration m = new Migration(dbcon, assem, "AssetStore");
  68. m.Update();
  69. }
  70. }
  71. public override void Initialise()
  72. {
  73. throw new NotImplementedException();
  74. }
  75. public override void Dispose() { }
  76. /// <summary>
  77. /// The name of this DB provider
  78. /// </summary>
  79. override public string Name
  80. {
  81. get { return "MySQL Asset storage engine"; }
  82. }
  83. #endregion
  84. #region IAssetDataPlugin Members
  85. /// <summary>
  86. /// Fetch Asset <paramref name="assetID"/> from database
  87. /// </summary>
  88. /// <param name="assetID">Asset UUID to fetch</param>
  89. /// <returns>Return the asset</returns>
  90. /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
  91. override public AssetBase GetAsset(UUID assetID)
  92. {
  93. AssetBase asset = null;
  94. lock (m_dbLock)
  95. {
  96. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  97. {
  98. dbcon.Open();
  99. using (MySqlCommand cmd = new MySqlCommand(
  100. "SELECT name, description, assetType, local, temporary, asset_flags, CreatorID, data FROM assets WHERE id=?id",
  101. dbcon))
  102. {
  103. cmd.Parameters.AddWithValue("?id", assetID.ToString());
  104. try
  105. {
  106. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  107. {
  108. if (dbReader.Read())
  109. {
  110. asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"], dbReader["CreatorID"].ToString());
  111. asset.Data = (byte[])dbReader["data"];
  112. asset.Description = (string)dbReader["description"];
  113. string local = dbReader["local"].ToString();
  114. if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
  115. asset.Local = true;
  116. else
  117. asset.Local = false;
  118. asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
  119. asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
  120. }
  121. }
  122. }
  123. catch (Exception e)
  124. {
  125. m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message);
  126. }
  127. }
  128. }
  129. }
  130. return asset;
  131. }
  132. /// <summary>
  133. /// Create an asset in database, or update it if existing.
  134. /// </summary>
  135. /// <param name="asset">Asset UUID to create</param>
  136. /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
  137. override public void StoreAsset(AssetBase asset)
  138. {
  139. lock (m_dbLock)
  140. {
  141. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  142. {
  143. dbcon.Open();
  144. MySqlCommand cmd =
  145. new MySqlCommand(
  146. "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, asset_flags, CreatorID, data)" +
  147. "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?CreatorID, ?data)",
  148. dbcon);
  149. string assetName = asset.Name;
  150. if (asset.Name.Length > 64)
  151. {
  152. assetName = asset.Name.Substring(0, 64);
  153. m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
  154. }
  155. string assetDescription = asset.Description;
  156. if (asset.Description.Length > 64)
  157. {
  158. assetDescription = asset.Description.Substring(0, 64);
  159. m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
  160. }
  161. // need to ensure we dispose
  162. try
  163. {
  164. using (cmd)
  165. {
  166. // create unix epoch time
  167. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  168. cmd.Parameters.AddWithValue("?id", asset.ID);
  169. cmd.Parameters.AddWithValue("?name", assetName);
  170. cmd.Parameters.AddWithValue("?description", assetDescription);
  171. cmd.Parameters.AddWithValue("?assetType", asset.Type);
  172. cmd.Parameters.AddWithValue("?local", asset.Local);
  173. cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
  174. cmd.Parameters.AddWithValue("?create_time", now);
  175. cmd.Parameters.AddWithValue("?access_time", now);
  176. cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
  177. cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags);
  178. cmd.Parameters.AddWithValue("?data", asset.Data);
  179. cmd.ExecuteNonQuery();
  180. cmd.Dispose();
  181. }
  182. }
  183. catch (Exception e)
  184. {
  185. m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}",
  186. asset.FullID, asset.Name, e.Message);
  187. }
  188. }
  189. }
  190. }
  191. private void UpdateAccessTime(AssetBase asset)
  192. {
  193. // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph
  194. return;
  195. lock (m_dbLock)
  196. {
  197. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  198. {
  199. dbcon.Open();
  200. MySqlCommand cmd =
  201. new MySqlCommand("update assets set access_time=?access_time where id=?id",
  202. dbcon);
  203. // need to ensure we dispose
  204. try
  205. {
  206. using (cmd)
  207. {
  208. // create unix epoch time
  209. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  210. cmd.Parameters.AddWithValue("?id", asset.ID);
  211. cmd.Parameters.AddWithValue("?access_time", now);
  212. cmd.ExecuteNonQuery();
  213. cmd.Dispose();
  214. }
  215. }
  216. catch (Exception e)
  217. {
  218. m_log.ErrorFormat(
  219. "[ASSETS DB]: " +
  220. "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
  221. + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
  222. }
  223. }
  224. }
  225. }
  226. /// <summary>
  227. /// check if the asset UUID exist in database
  228. /// </summary>
  229. /// <param name="uuid">The asset UUID</param>
  230. /// <returns>true if exist.</returns>
  231. override public bool ExistsAsset(UUID uuid)
  232. {
  233. bool assetExists = false;
  234. lock (m_dbLock)
  235. {
  236. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  237. {
  238. dbcon.Open();
  239. using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon))
  240. {
  241. cmd.Parameters.AddWithValue("?id", uuid.ToString());
  242. try
  243. {
  244. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  245. {
  246. if (dbReader.Read())
  247. assetExists = true;
  248. }
  249. }
  250. catch (Exception e)
  251. {
  252. m_log.ErrorFormat(
  253. "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid);
  254. }
  255. }
  256. }
  257. }
  258. return assetExists;
  259. }
  260. /// <summary>
  261. /// Returns a list of AssetMetadata objects. The list is a subset of
  262. /// the entire data set offset by <paramref name="start" /> containing
  263. /// <paramref name="count" /> elements.
  264. /// </summary>
  265. /// <param name="start">The number of results to discard from the total data set.</param>
  266. /// <param name="count">The number of rows the returned list should contain.</param>
  267. /// <returns>A list of AssetMetadata objects.</returns>
  268. public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  269. {
  270. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  271. lock (m_dbLock)
  272. {
  273. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  274. {
  275. dbcon.Open();
  276. MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id,asset_flags,CreatorID FROM assets LIMIT ?start, ?count", dbcon);
  277. cmd.Parameters.AddWithValue("?start", start);
  278. cmd.Parameters.AddWithValue("?count", count);
  279. try
  280. {
  281. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  282. {
  283. while (dbReader.Read())
  284. {
  285. AssetMetadata metadata = new AssetMetadata();
  286. metadata.Name = (string)dbReader["name"];
  287. metadata.Description = (string)dbReader["description"];
  288. metadata.Type = (sbyte)dbReader["assetType"];
  289. metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct.
  290. metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
  291. metadata.FullID = DBGuid.FromDB(dbReader["id"]);
  292. metadata.CreatorID = dbReader["CreatorID"].ToString();
  293. // Current SHA1s are not stored/computed.
  294. metadata.SHA1 = new byte[] { };
  295. retList.Add(metadata);
  296. }
  297. }
  298. }
  299. catch (Exception e)
  300. {
  301. m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
  302. }
  303. }
  304. }
  305. return retList;
  306. }
  307. public override bool Delete(string id)
  308. {
  309. lock (m_dbLock)
  310. {
  311. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  312. {
  313. dbcon.Open();
  314. MySqlCommand cmd = new MySqlCommand("delete from assets where id=?id", dbcon);
  315. cmd.Parameters.AddWithValue("?id", id);
  316. cmd.ExecuteNonQuery();
  317. cmd.Dispose();
  318. }
  319. }
  320. return true;
  321. }
  322. #endregion
  323. }
  324. }