MySQLGridData.cs 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521
  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.Reflection;
  31. using System.Threading;
  32. using log4net;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. namespace OpenSim.Data.MySQL
  36. {
  37. /// <summary>
  38. /// A MySQL Interface for the Grid Server
  39. /// </summary>
  40. public class MySQLGridData : GridDataBase
  41. {
  42. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  43. /// <summary>
  44. /// MySQL Database Manager
  45. /// </summary>
  46. private MySQLManager database;
  47. /// <summary>
  48. /// Better DB manager. Swap-in replacement too.
  49. /// </summary>
  50. public Dictionary<int, MySQLSuperManager> m_dbconnections = new Dictionary<int, MySQLSuperManager>();
  51. public int m_maxConnections = 10;
  52. public int m_lastConnect;
  53. public MySQLSuperManager GetLockedConnection()
  54. {
  55. int lockedCons = 0;
  56. while (true)
  57. {
  58. m_lastConnect++;
  59. // Overflow protection
  60. if (m_lastConnect == int.MaxValue)
  61. m_lastConnect = 0;
  62. MySQLSuperManager x = m_dbconnections[m_lastConnect % m_maxConnections];
  63. if (!x.Locked)
  64. {
  65. x.GetLock();
  66. return x;
  67. }
  68. lockedCons++;
  69. if (lockedCons > m_maxConnections)
  70. {
  71. lockedCons = 0;
  72. Thread.Sleep(1000); // Wait some time before searching them again.
  73. m_log.Debug(
  74. "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
  75. }
  76. }
  77. }
  78. override public void Initialise()
  79. {
  80. m_log.Info("[MySQLGridData]: " + Name + " cannot be default-initialized!");
  81. throw new PluginNotInitialisedException (Name);
  82. }
  83. /// <summary>
  84. /// <para>Initialises Grid interface</para>
  85. /// <para>
  86. /// <list type="bullet">
  87. /// <item>Loads and initialises the MySQL storage plugin</item>
  88. /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
  89. /// <item>Check for migration</item>
  90. /// </list>
  91. /// </para>
  92. /// </summary>
  93. /// <param name="connect">connect string.</param>
  94. override public void Initialise(string connect)
  95. {
  96. if (connect != String.Empty)
  97. {
  98. database = new MySQLManager(connect);
  99. m_log.Info("Creating " + m_maxConnections + " DB connections...");
  100. for (int i = 0; i < m_maxConnections; i++)
  101. {
  102. m_log.Info("Connecting to DB... [" + i + "]");
  103. MySQLSuperManager msm = new MySQLSuperManager();
  104. msm.Manager = new MySQLManager(connect);
  105. m_dbconnections.Add(i, msm);
  106. }
  107. }
  108. else
  109. {
  110. m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead");
  111. IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
  112. string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
  113. string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
  114. string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
  115. string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
  116. string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
  117. string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
  118. database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
  119. settingPooling, settingPort);
  120. m_log.Info("Creating " + m_maxConnections + " DB connections...");
  121. for (int i = 0; i < m_maxConnections; i++)
  122. {
  123. m_log.Info("Connecting to DB... [" + i + "]");
  124. MySQLSuperManager msm = new MySQLSuperManager();
  125. msm.Manager = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
  126. settingPooling, settingPort);
  127. m_dbconnections.Add(i, msm);
  128. }
  129. }
  130. // This actually does the roll forward assembly stuff
  131. Assembly assem = GetType().Assembly;
  132. Migration m = new Migration(database.Connection, assem, "GridStore");
  133. m.Update();
  134. }
  135. /// <summary>
  136. /// Shuts down the grid interface
  137. /// </summary>
  138. override public void Dispose()
  139. {
  140. database.Close();
  141. }
  142. /// <summary>
  143. /// Returns the plugin name
  144. /// </summary>
  145. /// <returns>Plugin name</returns>
  146. override public string Name
  147. {
  148. get { return "MySql OpenGridData"; }
  149. }
  150. /// <summary>
  151. /// Returns the plugin version
  152. /// </summary>
  153. /// <returns>Plugin version</returns>
  154. override public string Version
  155. {
  156. get { return "0.1"; }
  157. }
  158. /// <summary>
  159. /// Returns all the specified region profiles within coordates -- coordinates are inclusive
  160. /// </summary>
  161. /// <param name="xmin">Minimum X coordinate</param>
  162. /// <param name="ymin">Minimum Y coordinate</param>
  163. /// <param name="xmax">Maximum X coordinate</param>
  164. /// <param name="ymax">Maximum Y coordinate</param>
  165. /// <returns>Array of sim profiles</returns>
  166. override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
  167. {
  168. MySQLSuperManager dbm = GetLockedConnection();
  169. try
  170. {
  171. Dictionary<string, object> param = new Dictionary<string, object>();
  172. param["?xmin"] = xmin.ToString();
  173. param["?ymin"] = ymin.ToString();
  174. param["?xmax"] = xmax.ToString();
  175. param["?ymax"] = ymax.ToString();
  176. IDbCommand result =
  177. dbm.Manager.Query(
  178. "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
  179. param);
  180. IDataReader reader = result.ExecuteReader();
  181. RegionProfileData row;
  182. List<RegionProfileData> rows = new List<RegionProfileData>();
  183. while ((row = dbm.Manager.readSimRow(reader)) != null)
  184. {
  185. rows.Add(row);
  186. }
  187. reader.Close();
  188. result.Dispose();
  189. return rows.ToArray();
  190. }
  191. catch (Exception e)
  192. {
  193. dbm.Manager.Reconnect();
  194. m_log.Error(e.ToString());
  195. return null;
  196. }
  197. finally
  198. {
  199. dbm.Release();
  200. }
  201. }
  202. /// <summary>
  203. /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
  204. /// </summary>
  205. /// <param name="name">The name to match against</param>
  206. /// <param name="maxNum">Maximum number of profiles to return</param>
  207. /// <returns>A list of sim profiles</returns>
  208. override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum)
  209. {
  210. MySQLSuperManager dbm = GetLockedConnection();
  211. try
  212. {
  213. Dictionary<string, object> param = new Dictionary<string, object>();
  214. param["?name"] = namePrefix + "%";
  215. IDbCommand result =
  216. dbm.Manager.Query(
  217. "SELECT * FROM regions WHERE regionName LIKE ?name",
  218. param);
  219. IDataReader reader = result.ExecuteReader();
  220. RegionProfileData row;
  221. List<RegionProfileData> rows = new List<RegionProfileData>();
  222. while (rows.Count < maxNum && (row = dbm.Manager.readSimRow(reader)) != null)
  223. {
  224. rows.Add(row);
  225. }
  226. reader.Close();
  227. result.Dispose();
  228. return rows;
  229. }
  230. catch (Exception e)
  231. {
  232. dbm.Manager.Reconnect();
  233. m_log.Error(e.ToString());
  234. return null;
  235. }
  236. finally
  237. {
  238. dbm.Release();
  239. }
  240. }
  241. /// <summary>
  242. /// Returns a sim profile from it's location
  243. /// </summary>
  244. /// <param name="handle">Region location handle</param>
  245. /// <returns>Sim profile</returns>
  246. override public RegionProfileData GetProfileByHandle(ulong handle)
  247. {
  248. MySQLSuperManager dbm = GetLockedConnection();
  249. try
  250. {
  251. Dictionary<string, object> param = new Dictionary<string, object>();
  252. param["?handle"] = handle.ToString();
  253. IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param);
  254. IDataReader reader = result.ExecuteReader();
  255. RegionProfileData row = dbm.Manager.readSimRow(reader);
  256. reader.Close();
  257. result.Dispose();
  258. return row;
  259. }
  260. catch (Exception e)
  261. {
  262. dbm.Manager.Reconnect();
  263. m_log.Error(e.ToString());
  264. return null;
  265. }
  266. finally
  267. {
  268. dbm.Release();
  269. }
  270. }
  271. /// <summary>
  272. /// Returns a sim profile from it's UUID
  273. /// </summary>
  274. /// <param name="uuid">The region UUID</param>
  275. /// <returns>The sim profile</returns>
  276. override public RegionProfileData GetProfileByUUID(UUID uuid)
  277. {
  278. MySQLSuperManager dbm = GetLockedConnection();
  279. try
  280. {
  281. Dictionary<string, object> param = new Dictionary<string, object>();
  282. param["?uuid"] = uuid.ToString();
  283. IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE uuid = ?uuid", param);
  284. IDataReader reader = result.ExecuteReader();
  285. RegionProfileData row = dbm.Manager.readSimRow(reader);
  286. reader.Close();
  287. result.Dispose();
  288. return row;
  289. }
  290. catch (Exception e)
  291. {
  292. dbm.Manager.Reconnect();
  293. m_log.Error(e.ToString());
  294. return null;
  295. } finally
  296. {
  297. dbm.Release();
  298. }
  299. }
  300. /// <summary>
  301. /// Returns a sim profile from it's Region name string
  302. /// </summary>
  303. /// <returns>The sim profile</returns>
  304. override public RegionProfileData GetProfileByString(string regionName)
  305. {
  306. if (regionName.Length > 2)
  307. {
  308. MySQLSuperManager dbm = GetLockedConnection();
  309. try
  310. {
  311. Dictionary<string, object> param = new Dictionary<string, object>();
  312. // Add % because this is a like query.
  313. param["?regionName"] = regionName + "%";
  314. // Order by statement will return shorter matches first. Only returns one record or no record.
  315. IDbCommand result =
  316. dbm.Manager.Query(
  317. "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
  318. param);
  319. IDataReader reader = result.ExecuteReader();
  320. RegionProfileData row = dbm.Manager.readSimRow(reader);
  321. reader.Close();
  322. result.Dispose();
  323. return row;
  324. }
  325. catch (Exception e)
  326. {
  327. dbm.Manager.Reconnect();
  328. m_log.Error(e.ToString());
  329. return null;
  330. }
  331. finally
  332. {
  333. dbm.Release();
  334. }
  335. }
  336. m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
  337. return null;
  338. }
  339. /// <summary>
  340. /// Adds a new profile to the database
  341. /// </summary>
  342. /// <param name="profile">The profile to add</param>
  343. /// <returns>Successful?</returns>
  344. override public DataResponse AddProfile(RegionProfileData profile)
  345. {
  346. MySQLSuperManager dbm = GetLockedConnection();
  347. try {
  348. if (dbm.Manager.insertRegion(profile))
  349. {
  350. return DataResponse.RESPONSE_OK;
  351. }
  352. return DataResponse.RESPONSE_ERROR;
  353. }
  354. finally
  355. {
  356. dbm.Release();
  357. }
  358. }
  359. /// <summary>
  360. /// Update a sim profile
  361. /// </summary>
  362. /// <param name="profile">The profile to update</param>
  363. /// <returns>Sucessful?</returns>
  364. /// <remarks>Same as AddProfile</remarks>
  365. override public DataResponse UpdateProfile(RegionProfileData profile)
  366. {
  367. return AddProfile(profile);
  368. }
  369. /// <summary>
  370. /// Deletes a sim profile from the database
  371. /// </summary>
  372. /// <param name="uuid">the sim UUID</param>
  373. /// <returns>Successful?</returns>
  374. //public DataResponse DeleteProfile(RegionProfileData profile)
  375. override public DataResponse DeleteProfile(string uuid)
  376. {
  377. MySQLSuperManager dbm = GetLockedConnection();
  378. try {
  379. if (dbm.Manager.deleteRegion(uuid))
  380. {
  381. return DataResponse.RESPONSE_OK;
  382. }
  383. return DataResponse.RESPONSE_ERROR;
  384. } finally
  385. {
  386. dbm.Release();
  387. }
  388. }
  389. /// <summary>
  390. /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
  391. /// </summary>
  392. /// <param name="uuid">The UUID of the challenger</param>
  393. /// <param name="handle">The attempted regionHandle of the challenger</param>
  394. /// <param name="authkey">The secret</param>
  395. /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
  396. override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
  397. {
  398. bool throwHissyFit = false; // Should be true by 1.0
  399. if (throwHissyFit)
  400. throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
  401. RegionProfileData data = GetProfileByUUID(uuid);
  402. return (handle == data.regionHandle && authkey == data.regionSecret);
  403. }
  404. /// <summary>
  405. /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
  406. /// </summary>
  407. /// <remarks>This requires a security audit.</remarks>
  408. /// <param name="uuid"></param>
  409. /// <param name="handle"></param>
  410. /// <param name="authhash"></param>
  411. /// <param name="challenge"></param>
  412. /// <returns></returns>
  413. public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
  414. {
  415. // SHA512Managed HashProvider = new SHA512Managed();
  416. // Encoding TextProvider = new UTF8Encoding();
  417. // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
  418. // byte[] hash = HashProvider.ComputeHash(stream);
  419. return false;
  420. }
  421. /// <summary>
  422. /// Adds a location reservation
  423. /// </summary>
  424. /// <param name="x">x coordinate</param>
  425. /// <param name="y">y coordinate</param>
  426. /// <returns></returns>
  427. override public ReservationData GetReservationAtPoint(uint x, uint y)
  428. {
  429. MySQLSuperManager dbm = GetLockedConnection();
  430. try
  431. {
  432. Dictionary<string, object> param = new Dictionary<string, object>();
  433. param["?x"] = x.ToString();
  434. param["?y"] = y.ToString();
  435. IDbCommand result =
  436. dbm.Manager.Query(
  437. "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
  438. param);
  439. IDataReader reader = result.ExecuteReader();
  440. ReservationData row = dbm.Manager.readReservationRow(reader);
  441. reader.Close();
  442. result.Dispose();
  443. return row;
  444. }
  445. catch (Exception e)
  446. {
  447. dbm.Manager.Reconnect();
  448. m_log.Error(e.ToString());
  449. return null;
  450. } finally
  451. {
  452. dbm.Release();
  453. }
  454. }
  455. }
  456. }