1
0

MySQLEstateData.cs 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504
  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. public void Initialise(string connectionString)
  52. {
  53. m_connectionString = connectionString;
  54. try
  55. {
  56. m_log.Info("[REGION DB]: MySql - connecting: " + Util.GetDisplayConnectionString(m_connectionString));
  57. }
  58. catch (Exception e)
  59. {
  60. m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
  61. }
  62. GetWaitTimeout();
  63. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  64. {
  65. dbcon.Open();
  66. Assembly assem = GetType().Assembly;
  67. Migration m = new Migration(dbcon, assem, "EstateStore");
  68. m.Update();
  69. Type t = typeof(EstateSettings);
  70. m_Fields = t.GetFields(BindingFlags.NonPublic |
  71. BindingFlags.Instance |
  72. BindingFlags.DeclaredOnly);
  73. foreach (FieldInfo f in m_Fields)
  74. {
  75. if (f.Name.Substring(0, 2) == "m_")
  76. m_FieldMap[f.Name.Substring(2)] = f;
  77. }
  78. }
  79. }
  80. private string[] FieldList
  81. {
  82. get { return new List<string>(m_FieldMap.Keys).ToArray(); }
  83. }
  84. protected void GetWaitTimeout()
  85. {
  86. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  87. {
  88. dbcon.Open();
  89. using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
  90. {
  91. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  92. {
  93. if (dbReader.Read())
  94. {
  95. m_waitTimeout
  96. = Convert.ToInt32(dbReader["@@wait_timeout"]) *
  97. TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
  98. }
  99. }
  100. }
  101. m_lastConnectionUse = DateTime.Now.Ticks;
  102. m_log.DebugFormat(
  103. "[REGION DB]: Connection wait timeout {0} seconds",
  104. m_waitTimeout / TimeSpan.TicksPerSecond);
  105. }
  106. }
  107. public EstateSettings LoadEstateSettings(UUID regionID, bool create)
  108. {
  109. string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
  110. " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
  111. using (MySqlCommand cmd = new MySqlCommand())
  112. {
  113. cmd.CommandText = sql;
  114. cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
  115. return DoLoad(cmd, regionID, create);
  116. }
  117. }
  118. private EstateSettings DoLoad(MySqlCommand cmd, UUID regionID, bool create)
  119. {
  120. EstateSettings es = new EstateSettings();
  121. es.OnSave += StoreEstateSettings;
  122. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  123. {
  124. dbcon.Open();
  125. cmd.Connection = dbcon;
  126. bool found = false;
  127. using (IDataReader r = cmd.ExecuteReader())
  128. {
  129. if (r.Read())
  130. {
  131. found = true;
  132. foreach (string name in FieldList)
  133. {
  134. if (m_FieldMap[name].FieldType == typeof(bool))
  135. {
  136. m_FieldMap[name].SetValue(es, Convert.ToInt32(r[name]) != 0);
  137. }
  138. else if (m_FieldMap[name].FieldType == typeof(UUID))
  139. {
  140. m_FieldMap[name].SetValue(es, DBGuid.FromDB(r[name]));
  141. }
  142. else
  143. {
  144. m_FieldMap[name].SetValue(es, r[name]);
  145. }
  146. }
  147. }
  148. }
  149. if (!found && create)
  150. {
  151. // Migration case
  152. List<string> names = new List<string>(FieldList);
  153. names.Remove("EstateID");
  154. string sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
  155. using (MySqlCommand cmd2 = dbcon.CreateCommand())
  156. {
  157. cmd2.CommandText = sql;
  158. cmd2.Parameters.Clear();
  159. foreach (string name in FieldList)
  160. {
  161. if (m_FieldMap[name].GetValue(es) is bool)
  162. {
  163. if ((bool)m_FieldMap[name].GetValue(es))
  164. cmd2.Parameters.AddWithValue("?" + name, "1");
  165. else
  166. cmd2.Parameters.AddWithValue("?" + name, "0");
  167. }
  168. else
  169. {
  170. cmd2.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  171. }
  172. }
  173. cmd2.ExecuteNonQuery();
  174. cmd2.CommandText = "select LAST_INSERT_ID() as id";
  175. cmd2.Parameters.Clear();
  176. using (IDataReader r = cmd2.ExecuteReader())
  177. {
  178. r.Read();
  179. es.EstateID = Convert.ToUInt32(r["id"]);
  180. }
  181. cmd2.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
  182. cmd2.Parameters.AddWithValue("?RegionID", regionID.ToString());
  183. cmd2.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  184. // This will throw on dupe key
  185. try { cmd2.ExecuteNonQuery(); }
  186. catch (Exception) { }
  187. es.Save();
  188. }
  189. }
  190. }
  191. LoadBanList(es);
  192. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  193. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  194. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  195. return es;
  196. }
  197. public void StoreEstateSettings(EstateSettings es)
  198. {
  199. string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
  200. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  201. {
  202. dbcon.Open();
  203. using (MySqlCommand cmd = dbcon.CreateCommand())
  204. {
  205. cmd.CommandText = sql;
  206. foreach (string name in FieldList)
  207. {
  208. if (m_FieldMap[name].GetValue(es) is bool)
  209. {
  210. if ((bool)m_FieldMap[name].GetValue(es))
  211. cmd.Parameters.AddWithValue("?" + name, "1");
  212. else
  213. cmd.Parameters.AddWithValue("?" + name, "0");
  214. }
  215. else
  216. {
  217. cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
  218. }
  219. }
  220. cmd.ExecuteNonQuery();
  221. }
  222. }
  223. SaveBanList(es);
  224. SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
  225. SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
  226. SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
  227. }
  228. private void LoadBanList(EstateSettings es)
  229. {
  230. es.ClearBans();
  231. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  232. {
  233. dbcon.Open();
  234. using (MySqlCommand cmd = dbcon.CreateCommand())
  235. {
  236. cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
  237. cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
  238. using (IDataReader r = cmd.ExecuteReader())
  239. {
  240. while (r.Read())
  241. {
  242. EstateBan eb = new EstateBan();
  243. UUID uuid = new UUID();
  244. UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
  245. eb.BannedUserID = uuid;
  246. eb.BannedHostAddress = "0.0.0.0";
  247. eb.BannedHostIPMask = "0.0.0.0";
  248. es.AddBan(eb);
  249. }
  250. }
  251. }
  252. }
  253. }
  254. private void SaveBanList(EstateSettings es)
  255. {
  256. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  257. {
  258. dbcon.Open();
  259. using (MySqlCommand cmd = dbcon.CreateCommand())
  260. {
  261. cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
  262. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  263. cmd.ExecuteNonQuery();
  264. cmd.Parameters.Clear();
  265. cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
  266. foreach (EstateBan b in es.EstateBans)
  267. {
  268. cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
  269. cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
  270. cmd.ExecuteNonQuery();
  271. cmd.Parameters.Clear();
  272. }
  273. }
  274. }
  275. }
  276. void SaveUUIDList(uint EstateID, string table, UUID[] data)
  277. {
  278. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  279. {
  280. dbcon.Open();
  281. using (MySqlCommand cmd = dbcon.CreateCommand())
  282. {
  283. cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
  284. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  285. cmd.ExecuteNonQuery();
  286. cmd.Parameters.Clear();
  287. cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
  288. foreach (UUID uuid in data)
  289. {
  290. cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
  291. cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
  292. cmd.ExecuteNonQuery();
  293. cmd.Parameters.Clear();
  294. }
  295. }
  296. }
  297. }
  298. UUID[] LoadUUIDList(uint EstateID, string table)
  299. {
  300. List<UUID> uuids = new List<UUID>();
  301. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  302. {
  303. dbcon.Open();
  304. using (MySqlCommand cmd = dbcon.CreateCommand())
  305. {
  306. cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
  307. cmd.Parameters.AddWithValue("?EstateID", EstateID);
  308. using (IDataReader r = cmd.ExecuteReader())
  309. {
  310. while (r.Read())
  311. {
  312. // EstateBan eb = new EstateBan();
  313. uuids.Add(DBGuid.FromDB(r["uuid"]));
  314. }
  315. }
  316. }
  317. }
  318. return uuids.ToArray();
  319. }
  320. public EstateSettings LoadEstateSettings(int estateID)
  321. {
  322. using (MySqlCommand cmd = new MySqlCommand())
  323. {
  324. string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = ?EstateID";
  325. cmd.CommandText = sql;
  326. cmd.Parameters.AddWithValue("?EstateID", estateID);
  327. return DoLoad(cmd, UUID.Zero, false);
  328. }
  329. }
  330. public List<int> GetEstates(string search)
  331. {
  332. List<int> result = new List<int>();
  333. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  334. {
  335. dbcon.Open();
  336. using (MySqlCommand cmd = dbcon.CreateCommand())
  337. {
  338. cmd.CommandText = "select estateID from estate_settings where EstateName = ?EstateName";
  339. cmd.Parameters.AddWithValue("?EstateName", search);
  340. using (IDataReader reader = cmd.ExecuteReader())
  341. {
  342. while (reader.Read())
  343. {
  344. result.Add(Convert.ToInt32(reader["EstateID"]));
  345. }
  346. reader.Close();
  347. }
  348. }
  349. dbcon.Close();
  350. }
  351. return result;
  352. }
  353. public bool LinkRegion(UUID regionID, int estateID)
  354. {
  355. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  356. {
  357. dbcon.Open();
  358. try
  359. {
  360. using (MySqlCommand cmd = dbcon.CreateCommand())
  361. {
  362. cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
  363. cmd.Parameters.AddWithValue("?RegionID", regionID);
  364. cmd.Parameters.AddWithValue("?EstateID", estateID);
  365. int ret = cmd.ExecuteNonQuery();
  366. dbcon.Close();
  367. return (ret != 0);
  368. }
  369. }
  370. catch (MySqlException ex)
  371. {
  372. m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message);
  373. }
  374. dbcon.Close();
  375. }
  376. return false;
  377. }
  378. public List<UUID> GetRegions(int estateID)
  379. {
  380. List<UUID> result = new List<UUID>();
  381. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  382. {
  383. dbcon.Open();
  384. try
  385. {
  386. using (MySqlCommand cmd = dbcon.CreateCommand())
  387. {
  388. cmd.CommandText = "select RegionID from estate_map where EstateID = ?EstateID";
  389. cmd.Parameters.AddWithValue("?EstateID", estateID.ToString());
  390. using (IDataReader reader = cmd.ExecuteReader())
  391. {
  392. while(reader.Read())
  393. result.Add(DBGuid.FromDB(reader["RegionID"]));
  394. reader.Close();
  395. }
  396. }
  397. }
  398. catch (Exception e)
  399. {
  400. m_log.Error("[REGION DB]: Error reading estate map. " + e.ToString());
  401. return result;
  402. }
  403. dbcon.Close();
  404. }
  405. return result;
  406. }
  407. public bool DeleteEstate(int estateID)
  408. {
  409. return false;
  410. }
  411. }
  412. }