1
0

MSSQLEstateData.cs 22 KB

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