1
0

MySQLEstateData.cs 21 KB

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