/* * Copyright (c) Contributors, http://opensimulator.org/ * See CONTRIBUTORS.TXT for a full list of copyright holders. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * * Neither the name of the OpenSim Project nor the * names of its contributors may be used to endorse or promote products * derived from this software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Reflection; using libsecondlife; using log4net; using OpenSim.Framework; using OpenSim.Region.Environment.Interfaces; namespace OpenSim.Data.MSSQL { public class MSSQLEstateData : IEstateDataStore { private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); private MSSQLManager _Database; private FieldInfo[] _Fields; private Dictionary _FieldMap = new Dictionary(); #region Public methods /// /// Initialises the estatedata class. /// /// connectionString. public void Initialise(string connectionString) { if (string.IsNullOrEmpty(connectionString)) { _Database = new MSSQLManager(connectionString); } else { //TODO when can this be deleted IniFile iniFile = new IniFile("mssql_connection.ini"); string settingDataSource = iniFile.ParseFileReadValue("data_source"); string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); string settingUserId = iniFile.ParseFileReadValue("user_id"); string settingPassword = iniFile.ParseFileReadValue("password"); _Database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); } //Migration settings using (SqlConnection connection = _Database.DatabaseConnection()) { Assembly assem = GetType().Assembly; MSSQLMigration migration = new MSSQLMigration(connection, assem, "EstateStore"); migration.Update(); connection.Close(); } //Interesting way to get parameters! Maybe implement that also with other types Type t = typeof(EstateSettings); _Fields = t.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly); foreach (FieldInfo f in _Fields) { if (f.Name.Substring(0, 2) == "m_") _FieldMap[f.Name.Substring(2)] = f; } } /// /// Loads the estate settings. /// /// region ID. /// public EstateSettings LoadEstateSettings(LLUUID regionID) { EstateSettings es = new EstateSettings(); 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"; bool insertEstate = false; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.AddWithValue("@RegionID", regionID.ToString()); using (IDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { foreach (string name in FieldList) { if (_FieldMap[name].GetValue(es) is bool) { int v = Convert.ToInt32(reader[name]); if (v != 0) _FieldMap[name].SetValue(es, true); else _FieldMap[name].SetValue(es, false); } else if (_FieldMap[name].GetValue(es) is LLUUID) { LLUUID uuid; LLUUID.TryParse(reader[name].ToString(), out uuid); _FieldMap[name].SetValue(es, uuid); } else { _FieldMap[name].SetValue(es, reader[name]); } } } else { insertEstate = true; } } } if (insertEstate) { List names = new List(FieldList); names.Remove("EstateID"); sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray())); //_Log.Debug("[DB ESTATE]: SQL: " + sql); using (SqlConnection connection = _Database.DatabaseConnection()) { using (SqlCommand insertCommand = connection.CreateCommand()) { insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()"; foreach (string name in names) { if (_FieldMap[name].GetValue(es) is bool) { SqlParameter tempBool = new SqlParameter("@" + name, SqlDbType.Bit); if ((bool) _FieldMap[name].GetValue(es)) tempBool.Value = 1; else tempBool.Value = 0; insertCommand.Parameters.Add(tempBool); } else { //cmd.Parameters.AddWithValue("@" + name, _FieldMap[name].GetValue(es)); SqlParameter tempPar = new SqlParameter("@" + name, _Database.DbtypeFromType(_FieldMap[name].FieldType)); tempPar.Value = _FieldMap[name].GetValue(es).ToString(); insertCommand.Parameters.Add(tempPar); } } SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int); idParameter.Direction = ParameterDirection.Output; insertCommand.Parameters.Add(idParameter); insertCommand.ExecuteNonQuery(); es.EstateID = Convert.ToUInt32(idParameter.Value); } } using (AutoClosingSqlCommand cmd = _Database.Query("insert into estate_map values (@RegionID, @EstateID)")) { cmd.Parameters.AddWithValue("@RegionID", regionID.ToString()); cmd.Parameters.AddWithValue("@EstateID", es.EstateID); // This will throw on dupe key try { cmd.ExecuteNonQuery(); } catch (Exception) { _Log.Debug("[ESTATE DB]: Error inserting regionID and EstateID in estate_map"); } } // Munge and transfer the ban list // sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID); using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.AddWithValue("@UUID", regionID); try { cmd.ExecuteNonQuery(); } catch (Exception) { _Log.Debug("[ESTATE DB]: Error setting up estateban from regionban"); } } //TODO check if this is needed?? es.Save(); } LoadBanList(es); es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers"); es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users"); es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups"); //Set event es.OnSave += StoreEstateSettings; return es; } /// /// Stores the estate settings. /// /// estate settings public void StoreEstateSettings(EstateSettings es) { List names = new List(FieldList); names.Remove("EstateID"); string sql = string.Format("UPDATE estate_settings SET ") ; // ({0}) values ( @{1}) WHERE EstateID = @EstateID", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray())); foreach (string name in names) { sql += name + " = @" + name + ", "; } sql = sql.Remove(sql.LastIndexOf(",")); sql += " WHERE EstateID = @EstateID"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { foreach (string name in names) { if (_FieldMap[name].GetValue(es) is bool) { SqlParameter tempBool = new SqlParameter("@" + name, SqlDbType.Bit); if ((bool)_FieldMap[name].GetValue(es)) tempBool.Value = 1; else tempBool.Value = 0; cmd.Parameters.Add(tempBool); } else { //cmd.Parameters.AddWithValue("@" + name, _FieldMap[name].GetValue(es)); SqlParameter tempPar = new SqlParameter("@" + name, _Database.DbtypeFromType(_FieldMap[name].FieldType)); tempPar.Value = _FieldMap[name].GetValue(es).ToString(); cmd.Parameters.Add(tempPar); } } SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int); idParameter.Value = es.EstateID; cmd.Parameters.Add(idParameter); cmd.ExecuteNonQuery(); } SaveBanList(es); SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers); SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess); SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups); } #endregion #region Private methods private string[] FieldList { get { return new List(_FieldMap.Keys).ToArray(); } } private void LoadBanList(EstateSettings es) { es.ClearBans(); string sql = "select bannedUUID from estateban where EstateID = @EstateID"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int); idParameter.Value = es.EstateID; cmd.Parameters.Add(idParameter); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { EstateBan eb = new EstateBan(); LLUUID uuid; LLUUID.TryParse(reader["bannedUUID"].ToString(), out uuid); eb.bannedUUID = uuid; eb.bannedIP = "0.0.0.0"; eb.bannedIPHostMask = "0.0.0.0"; es.AddBan(eb); } } } } private LLUUID[] LoadUUIDList(uint estateID, string table) { List uuids = new List(); string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table); using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { // EstateBan eb = new EstateBan(); LLUUID uuid; LLUUID.TryParse(reader["uuid"].ToString(), out uuid); uuids.Add(uuid); } } } return uuids.ToArray(); } private void SaveBanList(EstateSettings es) { //Delete first string sql = "delete from estateban where EstateID = @EstateID"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); cmd.ExecuteNonQuery(); } //Insert after sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { foreach (EstateBan b in es.EstateBans) { cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.bannedUUID)); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } } } private void SaveUUIDList(uint estateID, string table, LLUUID[] data) { //Delete first string sql = string.Format("delete from {0} where EstateID = @EstateID", table); using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); cmd.ExecuteNonQuery(); } sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table); using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); bool createParamOnce = true; foreach (LLUUID uuid in data) { if (createParamOnce) { cmd.Parameters.Add(_Database.CreateParameter("@uuid", uuid)); createParamOnce = false; } else cmd.Parameters["@uuid"].Value = uuid.ToString(); cmd.ExecuteNonQuery(); } } } #endregion } }