1
0

PGSQLEstateData.cs 23 KB

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