MSSQLEstateData.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369
  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 MSSQLEstateData : IEstateDataStore
  39. {
  40. private const string _migrationStore = "EstateStore";
  41. private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  42. private MSSQLManager _Database;
  43. private FieldInfo[] _Fields;
  44. private Dictionary<string, FieldInfo> _FieldMap = new Dictionary<string, FieldInfo>();
  45. #region Public methods
  46. /// <summary>
  47. /// Initialises the estatedata class.
  48. /// </summary>
  49. /// <param name="connectionString">connectionString.</param>
  50. public void Initialise(string connectionString)
  51. {
  52. if (!string.IsNullOrEmpty(connectionString))
  53. {
  54. _Database = new MSSQLManager(connectionString);
  55. }
  56. else
  57. {
  58. //TODO when can this be deleted
  59. IniFile iniFile = new IniFile("mssql_connection.ini");
  60. string settingDataSource = iniFile.ParseFileReadValue("data_source");
  61. string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
  62. string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
  63. string settingUserId = iniFile.ParseFileReadValue("user_id");
  64. string settingPassword = iniFile.ParseFileReadValue("password");
  65. _Database =
  66. new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
  67. settingPassword);
  68. }
  69. //Migration settings
  70. _Database.CheckMigration(_migrationStore);
  71. //Interesting way to get parameters! Maybe implement that also with other types
  72. Type t = typeof(EstateSettings);
  73. _Fields = t.GetFields(BindingFlags.NonPublic |
  74. BindingFlags.Instance |
  75. BindingFlags.DeclaredOnly);
  76. foreach (FieldInfo f in _Fields)
  77. {
  78. if (f.Name.Substring(0, 2) == "m_")
  79. _FieldMap[f.Name.Substring(2)] = f;
  80. }
  81. }
  82. /// <summary>
  83. /// Loads the estate settings.
  84. /// </summary>
  85. /// <param name="regionID">region ID.</param>
  86. /// <returns></returns>
  87. public EstateSettings LoadEstateSettings(UUID regionID)
  88. {
  89. EstateSettings es = new EstateSettings();
  90. 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";
  91. bool insertEstate = false;
  92. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  93. {
  94. cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
  95. using (SqlDataReader reader = cmd.ExecuteReader())
  96. {
  97. if (reader.Read())
  98. {
  99. foreach (string name in FieldList)
  100. {
  101. if (_FieldMap[name].GetValue(es) is bool)
  102. {
  103. int v = Convert.ToInt32(reader[name]);
  104. if (v != 0)
  105. _FieldMap[name].SetValue(es, true);
  106. else
  107. _FieldMap[name].SetValue(es, false);
  108. }
  109. else if (_FieldMap[name].GetValue(es) is UUID)
  110. {
  111. _FieldMap[name].SetValue(es, new UUID((Guid) reader[name])); // uuid);
  112. }
  113. else
  114. {
  115. es.EstateID = Convert.ToUInt32(reader["EstateID"].ToString());
  116. }
  117. }
  118. }
  119. else
  120. {
  121. insertEstate = true;
  122. }
  123. }
  124. }
  125. if (insertEstate)
  126. {
  127. List<string> names = new List<string>(FieldList);
  128. names.Remove("EstateID");
  129. sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray()));
  130. //_Log.Debug("[DB ESTATE]: SQL: " + sql);
  131. using (SqlConnection connection = _Database.DatabaseConnection())
  132. {
  133. using (SqlCommand insertCommand = connection.CreateCommand())
  134. {
  135. insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
  136. foreach (string name in names)
  137. {
  138. insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
  139. }
  140. SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
  141. idParameter.Direction = ParameterDirection.Output;
  142. insertCommand.Parameters.Add(idParameter);
  143. insertCommand.ExecuteNonQuery();
  144. es.EstateID = Convert.ToUInt32(idParameter.Value);
  145. }
  146. }
  147. using (AutoClosingSqlCommand cmd = _Database.Query("INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)"))
  148. {
  149. cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
  150. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
  151. // This will throw on dupe key
  152. try
  153. {
  154. cmd.ExecuteNonQuery();
  155. }
  156. catch (Exception e)
  157. {
  158. _Log.DebugFormat("[ESTATE DB]: Error inserting regionID and EstateID in estate_map: {0}", e);
  159. }
  160. }
  161. // Munge and transfer the ban list
  162. sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID);
  163. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  164. {
  165. cmd.Parameters.Add(_Database.CreateParameter("@UUID", regionID));
  166. try
  167. {
  168. cmd.ExecuteNonQuery();
  169. }
  170. catch (Exception)
  171. {
  172. _Log.Debug("[ESTATE DB]: Error setting up estateban from regionban");
  173. }
  174. }
  175. //TODO check if this is needed??
  176. es.Save();
  177. }
  178. LoadBanList(es);
  179. es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
  180. es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
  181. es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
  182. //Set event
  183. es.OnSave += StoreEstateSettings;
  184. return es;
  185. }
  186. /// <summary>
  187. /// Stores the estate settings.
  188. /// </summary>
  189. /// <param name="es">estate settings</param>
  190. public void StoreEstateSettings(EstateSettings es)
  191. {
  192. List<string> names = new List<string>(FieldList);
  193. names.Remove("EstateID");
  194. string sql = string.Format("UPDATE estate_settings SET ") ;
  195. foreach (string name in names)
  196. {
  197. sql += name + " = @" + name + ", ";
  198. }
  199. sql = sql.Remove(sql.LastIndexOf(","));
  200. sql += " WHERE EstateID = @EstateID";
  201. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  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. 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 (AutoClosingSqlCommand cmd = _Database.Query(sql))
  226. {
  227. SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int);
  228. idParameter.Value = es.EstateID;
  229. cmd.Parameters.Add(idParameter);
  230. using (SqlDataReader reader = cmd.ExecuteReader())
  231. {
  232. while (reader.Read())
  233. {
  234. EstateBan eb = new EstateBan();
  235. eb.BannedUserID = new UUID((Guid)reader["bannedUUID"]); //uuid;
  236. eb.BannedHostAddress = "0.0.0.0";
  237. eb.BannedHostIPMask = "0.0.0.0";
  238. es.AddBan(eb);
  239. }
  240. }
  241. }
  242. }
  243. private UUID[] LoadUUIDList(uint estateID, string table)
  244. {
  245. List<UUID> uuids = new List<UUID>();
  246. string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table);
  247. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  248. {
  249. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
  250. using (SqlDataReader reader = cmd.ExecuteReader())
  251. {
  252. while (reader.Read())
  253. {
  254. uuids.Add(new UUID((Guid)reader["uuid"])); //uuid);
  255. }
  256. }
  257. }
  258. return uuids.ToArray();
  259. }
  260. private void SaveBanList(EstateSettings es)
  261. {
  262. //Delete first
  263. string sql = "delete from estateban where EstateID = @EstateID";
  264. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  265. {
  266. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
  267. cmd.ExecuteNonQuery();
  268. }
  269. //Insert after
  270. sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )";
  271. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  272. {
  273. foreach (EstateBan b in es.EstateBans)
  274. {
  275. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
  276. cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.BannedUserID));
  277. cmd.ExecuteNonQuery();
  278. cmd.Parameters.Clear();
  279. }
  280. }
  281. }
  282. private void SaveUUIDList(uint estateID, string table, UUID[] data)
  283. {
  284. //Delete first
  285. string sql = string.Format("delete from {0} where EstateID = @EstateID", table);
  286. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  287. {
  288. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
  289. cmd.ExecuteNonQuery();
  290. }
  291. sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table);
  292. using (AutoClosingSqlCommand cmd = _Database.Query(sql))
  293. {
  294. cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
  295. bool createParamOnce = true;
  296. foreach (UUID uuid in data)
  297. {
  298. if (createParamOnce)
  299. {
  300. cmd.Parameters.Add(_Database.CreateParameter("@uuid", uuid));
  301. createParamOnce = false;
  302. }
  303. else
  304. cmd.Parameters["@uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
  305. cmd.ExecuteNonQuery();
  306. }
  307. }
  308. }
  309. #endregion
  310. }
  311. }