MSSQLEstateData.cs 21 KB

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