1
0

MySQLFSAssetData.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433
  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.Reflection;
  29. using System.Collections.Generic;
  30. using System.Data;
  31. using OpenSim.Framework;
  32. using OpenSim.Framework.Console;
  33. using log4net;
  34. using MySql.Data.MySqlClient;
  35. using OpenMetaverse;
  36. namespace OpenSim.Data.MySQL
  37. {
  38. public class MySQLFSAssetData : IFSAssetDataPlugin
  39. {
  40. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  41. protected string m_ConnectionString;
  42. protected string m_Table;
  43. /// <summary>
  44. /// Number of days that must pass before we update the access time on an asset when it has been fetched
  45. /// Config option to change this is "DaysBetweenAccessTimeUpdates"
  46. /// </summary>
  47. private int DaysBetweenAccessTimeUpdates = 0;
  48. protected virtual Assembly Assembly
  49. {
  50. get { return GetType().Assembly; }
  51. }
  52. public MySQLFSAssetData()
  53. {
  54. }
  55. #region IPlugin Members
  56. public string Version { get { return "1.0.0.0"; } }
  57. // Loads and initialises the MySQL storage plugin and checks for migrations
  58. public void Initialise(string connect, string realm, int UpdateAccessTime)
  59. {
  60. m_ConnectionString = connect;
  61. m_Table = realm;
  62. DaysBetweenAccessTimeUpdates = UpdateAccessTime;
  63. try
  64. {
  65. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  66. {
  67. conn.Open();
  68. Migration m = new Migration(conn, Assembly, "FSAssetStore");
  69. m.Update();
  70. conn.Close();
  71. }
  72. }
  73. catch (MySqlException e)
  74. {
  75. m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", e.Message.ToString());
  76. }
  77. }
  78. public void Initialise()
  79. {
  80. throw new NotImplementedException();
  81. }
  82. public void Dispose() { }
  83. public string Name
  84. {
  85. get { return "MySQL FSAsset storage engine"; }
  86. }
  87. #endregion
  88. private bool ExecuteNonQuery(MySqlCommand cmd)
  89. {
  90. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  91. {
  92. try
  93. {
  94. conn.Open();
  95. }
  96. catch (MySqlException e)
  97. {
  98. m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
  99. return false;
  100. }
  101. cmd.Connection = conn;
  102. try
  103. {
  104. cmd.ExecuteNonQuery();
  105. }
  106. catch (MySqlException e)
  107. {
  108. cmd.Connection = null;
  109. conn.Close();
  110. m_log.ErrorFormat("[FSASSETS]: Query {0} failed with {1}", cmd.CommandText, e.ToString());
  111. return false;
  112. }
  113. conn.Close();
  114. cmd.Connection = null;
  115. }
  116. return true;
  117. }
  118. #region IFSAssetDataPlugin Members
  119. public AssetMetadata Get(string id, out string hash)
  120. {
  121. hash = String.Empty;
  122. AssetMetadata meta = new AssetMetadata();
  123. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  124. {
  125. try
  126. {
  127. conn.Open();
  128. }
  129. catch (MySqlException e)
  130. {
  131. m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
  132. return null;
  133. }
  134. using (MySqlCommand cmd = conn.CreateCommand())
  135. {
  136. cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, asset_flags, access_time from {0} where id = ?id", m_Table);
  137. cmd.Parameters.AddWithValue("?id", id);
  138. using (IDataReader reader = cmd.ExecuteReader())
  139. {
  140. if (!reader.Read())
  141. return null;
  142. hash = reader["hash"].ToString();
  143. meta.ID = id;
  144. meta.FullID = new UUID(id);
  145. meta.Name = reader["name"].ToString();
  146. meta.Description = reader["description"].ToString();
  147. meta.Type = (sbyte)Convert.ToInt32(reader["type"]);
  148. meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
  149. meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
  150. meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]);
  151. int AccessTime = Convert.ToInt32(reader["access_time"]);
  152. UpdateAccessTime(id, AccessTime);
  153. }
  154. }
  155. conn.Close();
  156. }
  157. return meta;
  158. }
  159. private void UpdateAccessTime(string AssetID, int AccessTime)
  160. {
  161. // Reduce DB work by only updating access time if asset hasn't recently been accessed
  162. // 0 By Default, Config option is "DaysBetweenAccessTimeUpdates"
  163. if (DaysBetweenAccessTimeUpdates > 0 && (DateTime.UtcNow - Utils.UnixTimeToDateTime(AccessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
  164. return;
  165. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  166. {
  167. try
  168. {
  169. conn.Open();
  170. }
  171. catch (MySqlException e)
  172. {
  173. m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
  174. return;
  175. }
  176. using (MySqlCommand cmd = conn.CreateCommand())
  177. {
  178. cmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table);
  179. cmd.Parameters.AddWithValue("?id", AssetID);
  180. cmd.ExecuteNonQuery();
  181. }
  182. conn.Close();
  183. }
  184. }
  185. public bool Store(AssetMetadata meta, string hash)
  186. {
  187. try
  188. {
  189. string oldhash;
  190. AssetMetadata existingAsset = Get(meta.ID, out oldhash);
  191. using (MySqlCommand cmd = new MySqlCommand())
  192. {
  193. cmd.Parameters.AddWithValue("?id", meta.ID);
  194. cmd.Parameters.AddWithValue("?name", meta.Name);
  195. cmd.Parameters.AddWithValue("?description", meta.Description);
  196. // cmd.Parameters.AddWithValue("?type", meta.Type.ToString());
  197. cmd.Parameters.AddWithValue("?type", meta.Type);
  198. cmd.Parameters.AddWithValue("?hash", hash);
  199. cmd.Parameters.AddWithValue("?asset_flags", meta.Flags);
  200. if (existingAsset == null)
  201. {
  202. 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);
  203. ExecuteNonQuery(cmd);
  204. return true;
  205. }
  206. //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table);
  207. //ExecuteNonQuery(cmd);
  208. }
  209. // return false;
  210. // if the asset already exits
  211. // assume it was already correctly stored
  212. // or regions will keep retry.
  213. return true;
  214. }
  215. catch(Exception e)
  216. {
  217. m_log.Error("[FSAssets] Failed to store asset with ID " + meta.ID);
  218. m_log.Error(e.ToString());
  219. return false;
  220. }
  221. }
  222. /// <summary>
  223. /// Check if the assets exist in the database.
  224. /// </summary>
  225. /// <param name="uuids">The asset UUID's</param>
  226. /// <returns>For each asset: true if it exists, false otherwise</returns>
  227. public bool[] AssetsExist(UUID[] uuids)
  228. {
  229. if (uuids.Length == 0)
  230. return new bool[0];
  231. bool[] results = new bool[uuids.Length];
  232. for (int i = 0; i < uuids.Length; i++)
  233. results[i] = false;
  234. HashSet<UUID> exists = new HashSet<UUID>();
  235. string ids = "'" + string.Join("','", uuids) + "'";
  236. string sql = string.Format("select id from {1} where id in ({0})", ids, m_Table);
  237. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  238. {
  239. try
  240. {
  241. conn.Open();
  242. }
  243. catch (MySqlException e)
  244. {
  245. m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString());
  246. return results;
  247. }
  248. using (MySqlCommand cmd = conn.CreateCommand())
  249. {
  250. cmd.CommandText = sql;
  251. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  252. {
  253. while (dbReader.Read())
  254. {
  255. UUID id = DBGuid.FromDB(dbReader["ID"]);
  256. exists.Add(id);
  257. }
  258. }
  259. }
  260. conn.Close();
  261. }
  262. for (int i = 0; i < uuids.Length; i++)
  263. results[i] = exists.Contains(uuids[i]);
  264. return results;
  265. }
  266. public int Count()
  267. {
  268. int count = 0;
  269. using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
  270. {
  271. try
  272. {
  273. conn.Open();
  274. }
  275. catch (MySqlException e)
  276. {
  277. m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString());
  278. return 0;
  279. }
  280. using(MySqlCommand cmd = conn.CreateCommand())
  281. {
  282. cmd.CommandText = String.Format("select count(*) as count from {0}",m_Table);
  283. using (IDataReader reader = cmd.ExecuteReader())
  284. {
  285. reader.Read();
  286. count = Convert.ToInt32(reader["count"]);
  287. }
  288. }
  289. conn.Close();
  290. }
  291. return count;
  292. }
  293. public bool Delete(string id)
  294. {
  295. using(MySqlCommand cmd = new MySqlCommand())
  296. {
  297. cmd.CommandText = String.Format("delete from {0} where id = ?id",m_Table);
  298. cmd.Parameters.AddWithValue("?id", id);
  299. ExecuteNonQuery(cmd);
  300. }
  301. return true;
  302. }
  303. public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store)
  304. {
  305. int imported = 0;
  306. using (MySqlConnection importConn = new MySqlConnection(conn))
  307. {
  308. try
  309. {
  310. importConn.Open();
  311. }
  312. catch (MySqlException e)
  313. {
  314. m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}",
  315. e.Message.ToString());
  316. return;
  317. }
  318. using (MySqlCommand cmd = importConn.CreateCommand())
  319. {
  320. string limit = String.Empty;
  321. if (count != -1)
  322. {
  323. limit = String.Format(" limit {0},{1}", start, count);
  324. }
  325. cmd.CommandText = String.Format("select * from {0}{1}", table, limit);
  326. MainConsole.Instance.Output("Querying database");
  327. using (IDataReader reader = cmd.ExecuteReader())
  328. {
  329. MainConsole.Instance.Output("Reading data");
  330. while (reader.Read())
  331. {
  332. if ((imported % 100) == 0)
  333. {
  334. MainConsole.Instance.Output(String.Format("{0} assets imported so far", imported));
  335. }
  336. AssetBase asset = new AssetBase();
  337. AssetMetadata meta = new AssetMetadata();
  338. meta.ID = reader["id"].ToString();
  339. meta.FullID = new UUID(meta.ID);
  340. meta.Name = reader["name"].ToString();
  341. meta.Description = reader["description"].ToString();
  342. meta.Type = (sbyte)Convert.ToInt32(reader["assetType"]);
  343. meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
  344. meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
  345. asset.Metadata = meta;
  346. asset.Data = (byte[])reader["data"];
  347. store(asset, force);
  348. imported++;
  349. }
  350. }
  351. }
  352. importConn.Close();
  353. }
  354. MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported));
  355. }
  356. #endregion
  357. }
  358. }