MySQLEstateData.cs 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587
  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. private EstateSettings DoLoad(MySqlCommand cmd, UUID regionID, bool create)
  129. {
  130. EstateSettings es = new EstateSettings();
  131. es.OnSave += StoreEstateSettings;
  132. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  133. {
  134. dbcon.Open();
  135. cmd.Connection = dbcon;
  136. bool found = false;
  137. using (IDataReader r = cmd.ExecuteReader())
  138. {
  139. if (r.Read())
  140. {
  141. found = true;
  142. foreach (string name in FieldList)
  143. {
  144. if (m_FieldMap[name].FieldType == typeof(bool))
  145. {
  146. m_FieldMap[name].SetValue(es, Convert.ToInt32(r[name]) != 0);
  147. }
  148. else if (m_FieldMap[name].FieldType == typeof(UUID))
  149. {
  150. m_FieldMap[name].SetValue(es, DBGuid.FromDB(r[name]));
  151. }
  152. else
  153. {
  154. m_FieldMap[name].SetValue(es, r[name]);
  155. }
  156. }
  157. }
  158. }
  159. if (!found && create)
  160. {
  161. // Migration case
  162. List<string> names = new List<string>(FieldList);
  163. names.Remove("EstateID");
  164. string sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
  165. using (MySqlCommand cmd2 = dbcon.CreateCommand())
  166. {
  167. cmd2.CommandText = sql;
  168. cmd2.Parameters.Clear();
  169. foreach (string name in FieldList)
  170. {
  171. if (m_FieldMap[name].GetValue(es) is bool)
  172. {
  173. if ((bool)m_FieldMap[name].GetValue(es))
  174. cmd2.Parameters.AddWithValue("?" + name, "1");
  175. else
  176. cmd2.Parameters.AddWithValue("?" + name, "0");
  177. }
  178. else
  179. {
  180. cmd2.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  181. }
  182. }
  183. cmd2.ExecuteNonQuery();
  184. cmd2.CommandText = "select LAST_INSERT_ID() as id";
  185. cmd2.Parameters.Clear();
  186. using (IDataReader r = cmd2.ExecuteReader())
  187. {
  188. r.Read();
  189. es.EstateID = Convert.ToUInt32(r["id"]);
  190. }
  191. cmd2.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
  192. cmd2.Parameters.AddWithValue("?RegionID", regionID.ToString());
  193. cmd2.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  194. // This will throw on dupe key
  195. try { cmd2.ExecuteNonQuery(); }
  196. catch (Exception) { }
  197. es.Save();
  198. }
  199. }
  200. }
  201. LoadBanList(es);
  202. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  203. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  204. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  205. return es;
  206. }
  207. public void StoreEstateSettings(EstateSettings es)
  208. {
  209. string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
  210. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  211. {
  212. dbcon.Open();
  213. using (MySqlCommand cmd = dbcon.CreateCommand())
  214. {
  215. cmd.CommandText = sql;
  216. foreach (string name in FieldList)
  217. {
  218. if (m_FieldMap[name].GetValue(es) is bool)
  219. {
  220. if ((bool)m_FieldMap[name].GetValue(es))
  221. cmd.Parameters.AddWithValue("?" + name, "1");
  222. else
  223. cmd.Parameters.AddWithValue("?" + name, "0");
  224. }
  225. else
  226. {
  227. cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  228. }
  229. }
  230. cmd.ExecuteNonQuery();
  231. }
  232. }
  233. SaveBanList(es);
  234. SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
  235. SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
  236. SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
  237. }
  238. private void LoadBanList(EstateSettings es)
  239. {
  240. es.ClearBans();
  241. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  242. {
  243. dbcon.Open();
  244. using (MySqlCommand cmd = dbcon.CreateCommand())
  245. {
  246. cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
  247. cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
  248. using (IDataReader r = cmd.ExecuteReader())
  249. {
  250. while (r.Read())
  251. {
  252. EstateBan eb = new EstateBan();
  253. UUID uuid = new UUID();
  254. UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
  255. eb.BannedUserID = uuid;
  256. eb.BannedHostAddress = "0.0.0.0";
  257. eb.BannedHostIPMask = "0.0.0.0";
  258. es.AddBan(eb);
  259. }
  260. }
  261. }
  262. }
  263. }
  264. private void SaveBanList(EstateSettings es)
  265. {
  266. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  267. {
  268. dbcon.Open();
  269. using (MySqlCommand cmd = dbcon.CreateCommand())
  270. {
  271. cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
  272. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  273. cmd.ExecuteNonQuery();
  274. cmd.Parameters.Clear();
  275. cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
  276. foreach (EstateBan b in es.EstateBans)
  277. {
  278. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  279. cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
  280. cmd.ExecuteNonQuery();
  281. cmd.Parameters.Clear();
  282. }
  283. }
  284. }
  285. }
  286. void SaveUUIDList(uint EstateID, string table, UUID[] data)
  287. {
  288. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  289. {
  290. dbcon.Open();
  291. using (MySqlCommand cmd = dbcon.CreateCommand())
  292. {
  293. cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
  294. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  295. cmd.ExecuteNonQuery();
  296. cmd.Parameters.Clear();
  297. cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
  298. foreach (UUID uuid in data)
  299. {
  300. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  301. cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
  302. cmd.ExecuteNonQuery();
  303. cmd.Parameters.Clear();
  304. }
  305. }
  306. }
  307. }
  308. UUID[] LoadUUIDList(uint EstateID, string table)
  309. {
  310. List<UUID> uuids = new List<UUID>();
  311. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  312. {
  313. dbcon.Open();
  314. using (MySqlCommand cmd = dbcon.CreateCommand())
  315. {
  316. cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
  317. cmd.Parameters.AddWithValue("?EstateID", EstateID);
  318. using (IDataReader r = cmd.ExecuteReader())
  319. {
  320. while (r.Read())
  321. {
  322. // EstateBan eb = new EstateBan();
  323. uuids.Add(DBGuid.FromDB(r["uuid"]));
  324. }
  325. }
  326. }
  327. }
  328. return uuids.ToArray();
  329. }
  330. public EstateSettings LoadEstateSettings(int estateID)
  331. {
  332. using (MySqlCommand cmd = new MySqlCommand())
  333. {
  334. string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = ?EstateID";
  335. cmd.CommandText = sql;
  336. cmd.Parameters.AddWithValue("?EstateID", estateID);
  337. return DoLoad(cmd, UUID.Zero, false);
  338. }
  339. }
  340. public List<EstateSettings> LoadEstateSettingsAll()
  341. {
  342. List<EstateSettings> allEstateSettings = new List<EstateSettings>();
  343. List<int> allEstateIds = GetEstatesAll();
  344. foreach (int estateId in allEstateIds)
  345. allEstateSettings.Add(LoadEstateSettings(estateId));
  346. return allEstateSettings;
  347. }
  348. public List<int> GetEstatesAll()
  349. {
  350. List<int> result = new List<int>();
  351. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  352. {
  353. dbcon.Open();
  354. using (MySqlCommand cmd = dbcon.CreateCommand())
  355. {
  356. cmd.CommandText = "select estateID from estate_settings";
  357. using (IDataReader reader = cmd.ExecuteReader())
  358. {
  359. while (reader.Read())
  360. {
  361. result.Add(Convert.ToInt32(reader["EstateID"]));
  362. }
  363. reader.Close();
  364. }
  365. }
  366. dbcon.Close();
  367. }
  368. return result;
  369. }
  370. public List<int> GetEstates(string search)
  371. {
  372. List<int> result = new List<int>();
  373. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  374. {
  375. dbcon.Open();
  376. using (MySqlCommand cmd = dbcon.CreateCommand())
  377. {
  378. cmd.CommandText = "select estateID from estate_settings where EstateName = ?EstateName";
  379. cmd.Parameters.AddWithValue("?EstateName", search);
  380. using (IDataReader reader = cmd.ExecuteReader())
  381. {
  382. while (reader.Read())
  383. {
  384. result.Add(Convert.ToInt32(reader["EstateID"]));
  385. }
  386. reader.Close();
  387. }
  388. }
  389. dbcon.Close();
  390. }
  391. return result;
  392. }
  393. public List<int> GetEstatesByOwner(UUID ownerID)
  394. {
  395. List<int> result = new List<int>();
  396. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  397. {
  398. dbcon.Open();
  399. using (MySqlCommand cmd = dbcon.CreateCommand())
  400. {
  401. cmd.CommandText = "select estateID from estate_settings where EstateOwner = ?EstateOwner";
  402. cmd.Parameters.AddWithValue("?EstateOwner", ownerID);
  403. using (IDataReader reader = cmd.ExecuteReader())
  404. {
  405. while (reader.Read())
  406. {
  407. result.Add(Convert.ToInt32(reader["EstateID"]));
  408. }
  409. reader.Close();
  410. }
  411. }
  412. dbcon.Close();
  413. }
  414. return result;
  415. }
  416. public bool LinkRegion(UUID regionID, int estateID)
  417. {
  418. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  419. {
  420. dbcon.Open();
  421. try
  422. {
  423. using (MySqlCommand cmd = dbcon.CreateCommand())
  424. {
  425. cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
  426. cmd.Parameters.AddWithValue("?RegionID", regionID);
  427. cmd.Parameters.AddWithValue("?EstateID", estateID);
  428. int ret = cmd.ExecuteNonQuery();
  429. dbcon.Close();
  430. return (ret != 0);
  431. }
  432. }
  433. catch (MySqlException ex)
  434. {
  435. m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
  436. }
  437. dbcon.Close();
  438. }
  439. return false;
  440. }
  441. public List<UUID> GetRegions(int estateID)
  442. {
  443. List<UUID> result = new List<UUID>();
  444. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  445. {
  446. dbcon.Open();
  447. try
  448. {
  449. using (MySqlCommand cmd = dbcon.CreateCommand())
  450. {
  451. cmd.CommandText = "select RegionID from estate_map where EstateID = ?EstateID";
  452. cmd.Parameters.AddWithValue("?EstateID", estateID.ToString());
  453. using (IDataReader reader = cmd.ExecuteReader())
  454. {
  455. while(reader.Read())
  456. result.Add(DBGuid.FromDB(reader["RegionID"]));
  457. reader.Close();
  458. }
  459. }
  460. }
  461. catch (Exception e)
  462. {
  463. m_log.Error("[REGION DB]: Error reading estate map. " + e.ToString());
  464. return result;
  465. }
  466. dbcon.Close();
  467. }
  468. return result;
  469. }
  470. public bool DeleteEstate(int estateID)
  471. {
  472. return false;
  473. }
  474. }
  475. }