MySQLEstateData.cs 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622
  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 log4net;
  32. using MySql.Data.MySqlClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Region.Framework.Interfaces;
  36. using OpenSim.Data;
  37. namespace OpenSim.Data.MySQL
  38. {
  39. public class MySQLEstateStore : IEstateDataStore
  40. {
  41. private static readonly ILog m_log =
  42. LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  43. private const string m_waitTimeoutSelect = "select @@wait_timeout";
  44. private string m_connectionString;
  45. private long m_waitTimeout;
  46. private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
  47. // private long m_lastConnectionUse;
  48. private FieldInfo[] m_Fields;
  49. private Dictionary<string, FieldInfo> m_FieldMap =
  50. new Dictionary<string, FieldInfo>();
  51. protected virtual Assembly Assembly
  52. {
  53. get { return GetType().Assembly; }
  54. }
  55. public MySQLEstateStore()
  56. {
  57. }
  58. public MySQLEstateStore(string connectionString)
  59. {
  60. Initialise(connectionString);
  61. }
  62. public void Initialise(string connectionString)
  63. {
  64. m_connectionString = connectionString;
  65. try
  66. {
  67. m_log.Info("[REGION DB]: MySql - connecting: " + Util.GetDisplayConnectionString(m_connectionString));
  68. }
  69. catch (Exception e)
  70. {
  71. m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
  72. }
  73. GetWaitTimeout();
  74. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  75. {
  76. dbcon.Open();
  77. Migration m = new Migration(dbcon, Assembly, "EstateStore");
  78. m.Update();
  79. Type t = typeof(EstateSettings);
  80. m_Fields = t.GetFields(BindingFlags.NonPublic |
  81. BindingFlags.Instance |
  82. BindingFlags.DeclaredOnly);
  83. foreach (FieldInfo f in m_Fields)
  84. {
  85. if (f.Name.Substring(0, 2) == "m_")
  86. m_FieldMap[f.Name.Substring(2)] = f;
  87. }
  88. }
  89. }
  90. private string[] FieldList
  91. {
  92. get { return new List<string>(m_FieldMap.Keys).ToArray(); }
  93. }
  94. protected void GetWaitTimeout()
  95. {
  96. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  97. {
  98. dbcon.Open();
  99. using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
  100. {
  101. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  102. {
  103. if (dbReader.Read())
  104. {
  105. m_waitTimeout
  106. = Convert.ToInt32(dbReader["@@wait_timeout"]) *
  107. TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
  108. }
  109. }
  110. }
  111. // m_lastConnectionUse = DateTime.Now.Ticks;
  112. m_log.DebugFormat(
  113. "[REGION DB]: Connection wait timeout {0} seconds",
  114. m_waitTimeout / TimeSpan.TicksPerSecond);
  115. }
  116. }
  117. public EstateSettings LoadEstateSettings(UUID regionID, bool create)
  118. {
  119. string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
  120. " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
  121. using (MySqlCommand cmd = new MySqlCommand())
  122. {
  123. cmd.CommandText = sql;
  124. cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
  125. return DoLoad(cmd, regionID, create);
  126. }
  127. }
  128. public EstateSettings CreateNewEstate()
  129. {
  130. EstateSettings es = new EstateSettings();
  131. es.OnSave += StoreEstateSettings;
  132. DoCreate(es);
  133. LoadBanList(es);
  134. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  135. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  136. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  137. return es;
  138. }
  139. private EstateSettings DoLoad(MySqlCommand cmd, UUID regionID, bool create)
  140. {
  141. EstateSettings es = new EstateSettings();
  142. es.OnSave += StoreEstateSettings;
  143. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  144. {
  145. dbcon.Open();
  146. cmd.Connection = dbcon;
  147. bool found = false;
  148. using (IDataReader r = cmd.ExecuteReader())
  149. {
  150. if (r.Read())
  151. {
  152. found = true;
  153. foreach (string name in FieldList)
  154. {
  155. if (m_FieldMap[name].FieldType == typeof(bool))
  156. {
  157. m_FieldMap[name].SetValue(es, Convert.ToInt32(r[name]) != 0);
  158. }
  159. else if (m_FieldMap[name].FieldType == typeof(UUID))
  160. {
  161. m_FieldMap[name].SetValue(es, DBGuid.FromDB(r[name]));
  162. }
  163. else
  164. {
  165. m_FieldMap[name].SetValue(es, r[name]);
  166. }
  167. }
  168. }
  169. }
  170. if (!found && create)
  171. {
  172. DoCreate(es);
  173. LinkRegion(regionID, (int)es.EstateID);
  174. }
  175. }
  176. LoadBanList(es);
  177. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  178. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  179. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  180. return es;
  181. }
  182. private void DoCreate(EstateSettings es)
  183. {
  184. // Migration case
  185. List<string> names = new List<string>(FieldList);
  186. names.Remove("EstateID");
  187. string sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
  188. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  189. {
  190. dbcon.Open();
  191. using (MySqlCommand cmd2 = dbcon.CreateCommand())
  192. {
  193. cmd2.CommandText = sql;
  194. cmd2.Parameters.Clear();
  195. foreach (string name in FieldList)
  196. {
  197. if (m_FieldMap[name].GetValue(es) is bool)
  198. {
  199. if ((bool)m_FieldMap[name].GetValue(es))
  200. cmd2.Parameters.AddWithValue("?" + name, "1");
  201. else
  202. cmd2.Parameters.AddWithValue("?" + name, "0");
  203. }
  204. else
  205. {
  206. cmd2.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  207. }
  208. }
  209. cmd2.ExecuteNonQuery();
  210. cmd2.CommandText = "select LAST_INSERT_ID() as id";
  211. cmd2.Parameters.Clear();
  212. using (IDataReader r = cmd2.ExecuteReader())
  213. {
  214. r.Read();
  215. es.EstateID = Convert.ToUInt32(r["id"]);
  216. }
  217. es.Save();
  218. }
  219. }
  220. }
  221. public void StoreEstateSettings(EstateSettings es)
  222. {
  223. string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
  224. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  225. {
  226. dbcon.Open();
  227. using (MySqlCommand cmd = dbcon.CreateCommand())
  228. {
  229. cmd.CommandText = sql;
  230. foreach (string name in FieldList)
  231. {
  232. if (m_FieldMap[name].GetValue(es) is bool)
  233. {
  234. if ((bool)m_FieldMap[name].GetValue(es))
  235. cmd.Parameters.AddWithValue("?" + name, "1");
  236. else
  237. cmd.Parameters.AddWithValue("?" + name, "0");
  238. }
  239. else
  240. {
  241. cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  242. }
  243. }
  244. cmd.ExecuteNonQuery();
  245. }
  246. }
  247. SaveBanList(es);
  248. SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
  249. SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
  250. SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
  251. }
  252. private void LoadBanList(EstateSettings es)
  253. {
  254. es.ClearBans();
  255. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  256. {
  257. dbcon.Open();
  258. using (MySqlCommand cmd = dbcon.CreateCommand())
  259. {
  260. cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
  261. cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
  262. using (IDataReader r = cmd.ExecuteReader())
  263. {
  264. while (r.Read())
  265. {
  266. EstateBan eb = new EstateBan();
  267. UUID uuid = new UUID();
  268. UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
  269. eb.BannedUserID = uuid;
  270. eb.BannedHostAddress = "0.0.0.0";
  271. eb.BannedHostIPMask = "0.0.0.0";
  272. es.AddBan(eb);
  273. }
  274. }
  275. }
  276. }
  277. }
  278. private void SaveBanList(EstateSettings es)
  279. {
  280. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  281. {
  282. dbcon.Open();
  283. using (MySqlCommand cmd = dbcon.CreateCommand())
  284. {
  285. cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
  286. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  287. cmd.ExecuteNonQuery();
  288. cmd.Parameters.Clear();
  289. cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
  290. foreach (EstateBan b in es.EstateBans)
  291. {
  292. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  293. cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
  294. cmd.ExecuteNonQuery();
  295. cmd.Parameters.Clear();
  296. }
  297. }
  298. }
  299. }
  300. void SaveUUIDList(uint EstateID, string table, UUID[] data)
  301. {
  302. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  303. {
  304. dbcon.Open();
  305. using (MySqlCommand cmd = dbcon.CreateCommand())
  306. {
  307. cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
  308. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  309. cmd.ExecuteNonQuery();
  310. cmd.Parameters.Clear();
  311. cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
  312. foreach (UUID uuid in data)
  313. {
  314. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  315. cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
  316. cmd.ExecuteNonQuery();
  317. cmd.Parameters.Clear();
  318. }
  319. }
  320. }
  321. }
  322. UUID[] LoadUUIDList(uint EstateID, string table)
  323. {
  324. List<UUID> uuids = new List<UUID>();
  325. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  326. {
  327. dbcon.Open();
  328. using (MySqlCommand cmd = dbcon.CreateCommand())
  329. {
  330. cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
  331. cmd.Parameters.AddWithValue("?EstateID", EstateID);
  332. using (IDataReader r = cmd.ExecuteReader())
  333. {
  334. while (r.Read())
  335. {
  336. // EstateBan eb = new EstateBan();
  337. uuids.Add(DBGuid.FromDB(r["uuid"]));
  338. }
  339. }
  340. }
  341. }
  342. return uuids.ToArray();
  343. }
  344. public EstateSettings LoadEstateSettings(int estateID)
  345. {
  346. using (MySqlCommand cmd = new MySqlCommand())
  347. {
  348. string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = ?EstateID";
  349. cmd.CommandText = sql;
  350. cmd.Parameters.AddWithValue("?EstateID", estateID);
  351. return DoLoad(cmd, UUID.Zero, false);
  352. }
  353. }
  354. public List<EstateSettings> LoadEstateSettingsAll()
  355. {
  356. List<EstateSettings> allEstateSettings = new List<EstateSettings>();
  357. List<int> allEstateIds = GetEstatesAll();
  358. foreach (int estateId in allEstateIds)
  359. allEstateSettings.Add(LoadEstateSettings(estateId));
  360. return allEstateSettings;
  361. }
  362. public List<int> GetEstatesAll()
  363. {
  364. List<int> result = new List<int>();
  365. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  366. {
  367. dbcon.Open();
  368. using (MySqlCommand cmd = dbcon.CreateCommand())
  369. {
  370. cmd.CommandText = "select estateID from estate_settings";
  371. using (IDataReader reader = cmd.ExecuteReader())
  372. {
  373. while (reader.Read())
  374. {
  375. result.Add(Convert.ToInt32(reader["EstateID"]));
  376. }
  377. reader.Close();
  378. }
  379. }
  380. dbcon.Close();
  381. }
  382. return result;
  383. }
  384. public List<int> GetEstates(string search)
  385. {
  386. List<int> result = new List<int>();
  387. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  388. {
  389. dbcon.Open();
  390. using (MySqlCommand cmd = dbcon.CreateCommand())
  391. {
  392. cmd.CommandText = "select estateID from estate_settings where EstateName = ?EstateName";
  393. cmd.Parameters.AddWithValue("?EstateName", search);
  394. using (IDataReader reader = cmd.ExecuteReader())
  395. {
  396. while (reader.Read())
  397. {
  398. result.Add(Convert.ToInt32(reader["EstateID"]));
  399. }
  400. reader.Close();
  401. }
  402. }
  403. dbcon.Close();
  404. }
  405. return result;
  406. }
  407. public List<int> GetEstatesByOwner(UUID ownerID)
  408. {
  409. List<int> result = new List<int>();
  410. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  411. {
  412. dbcon.Open();
  413. using (MySqlCommand cmd = dbcon.CreateCommand())
  414. {
  415. cmd.CommandText = "select estateID from estate_settings where EstateOwner = ?EstateOwner";
  416. cmd.Parameters.AddWithValue("?EstateOwner", ownerID);
  417. using (IDataReader reader = cmd.ExecuteReader())
  418. {
  419. while (reader.Read())
  420. {
  421. result.Add(Convert.ToInt32(reader["EstateID"]));
  422. }
  423. reader.Close();
  424. }
  425. }
  426. dbcon.Close();
  427. }
  428. return result;
  429. }
  430. public bool LinkRegion(UUID regionID, int estateID)
  431. {
  432. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  433. {
  434. dbcon.Open();
  435. MySqlTransaction transaction = dbcon.BeginTransaction();
  436. try
  437. {
  438. // Delete any existing association of this region with an estate.
  439. using (MySqlCommand cmd = dbcon.CreateCommand())
  440. {
  441. cmd.Transaction = transaction;
  442. cmd.CommandText = "delete from estate_map where RegionID = ?RegionID";
  443. cmd.Parameters.AddWithValue("?RegionID", regionID);
  444. cmd.ExecuteNonQuery();
  445. }
  446. using (MySqlCommand cmd = dbcon.CreateCommand())
  447. {
  448. cmd.Transaction = transaction;
  449. cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
  450. cmd.Parameters.AddWithValue("?RegionID", regionID);
  451. cmd.Parameters.AddWithValue("?EstateID", estateID);
  452. int ret = cmd.ExecuteNonQuery();
  453. if (ret != 0)
  454. transaction.Commit();
  455. else
  456. transaction.Rollback();
  457. dbcon.Close();
  458. return (ret != 0);
  459. }
  460. }
  461. catch (MySqlException ex)
  462. {
  463. m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
  464. transaction.Rollback();
  465. }
  466. dbcon.Close();
  467. }
  468. return false;
  469. }
  470. public List<UUID> GetRegions(int estateID)
  471. {
  472. List<UUID> result = new List<UUID>();
  473. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  474. {
  475. dbcon.Open();
  476. try
  477. {
  478. using (MySqlCommand cmd = dbcon.CreateCommand())
  479. {
  480. cmd.CommandText = "select RegionID from estate_map where EstateID = ?EstateID";
  481. cmd.Parameters.AddWithValue("?EstateID", estateID.ToString());
  482. using (IDataReader reader = cmd.ExecuteReader())
  483. {
  484. while(reader.Read())
  485. result.Add(DBGuid.FromDB(reader["RegionID"]));
  486. reader.Close();
  487. }
  488. }
  489. }
  490. catch (Exception e)
  491. {
  492. m_log.Error("[REGION DB]: Error reading estate map. " + e.ToString());
  493. return result;
  494. }
  495. dbcon.Close();
  496. }
  497. return result;
  498. }
  499. public bool DeleteEstate(int estateID)
  500. {
  501. return false;
  502. }
  503. }
  504. }