From 263633e274082135b21b8183b92280b768d18883 Mon Sep 17 00:00:00 2001 From: Melanie Thielker Date: Fri, 18 Jul 2008 02:40:47 +0000 Subject: Patch #9151 Makes the estate dialog fully functional. Implements all client facing functionality. Moves estate data from estate_settings.xml, which is used to provide defaults, to the region data store. Creates one estate for each region, and places the region in it. Converts all region bans to estate bans. --- OpenSim/Data/SQLite/Resources/006_RegionStore.sql | 92 ++++++ OpenSim/Data/SQLite/SQLiteEstateData.cs | 333 ++++++++++++++++++++++ OpenSim/Data/SQLite/SQLiteRegionData.cs | 114 -------- 3 files changed, 425 insertions(+), 114 deletions(-) create mode 100644 OpenSim/Data/SQLite/Resources/006_RegionStore.sql create mode 100644 OpenSim/Data/SQLite/SQLiteEstateData.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Resources/006_RegionStore.sql b/OpenSim/Data/SQLite/Resources/006_RegionStore.sql new file mode 100644 index 0000000..025f1a9 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/006_RegionStore.sql @@ -0,0 +1,92 @@ +CREATE TABLE `estate_groups` ( + `EstateID` int(10) NOT NULL, + `uuid` char(36) NOT NULL +); +CREATE TABLE `estate_managers` ( + `EstateID` int(10) NOT NULL, + `uuid` char(36) NOT NULL +); +CREATE TABLE `estate_map` ( + `RegionID` char(36) NOT NULL default '00000000-0000-0000-0000-000000000000', + `EstateID` int(11) NOT NULL +); +CREATE TABLE `estate_settings` ( + `EstateID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + `EstateName` varchar(64) default NULL, + `AbuseEmailToEstateOwner` tinyint(4) NOT NULL, + `DenyAnonymous` tinyint(4) NOT NULL, + `ResetHomeOnTeleport` tinyint(4) NOT NULL, + `FixedSun` tinyint(4) NOT NULL, + `DenyTransacted` tinyint(4) NOT NULL, + `BlockDwell` tinyint(4) NOT NULL, + `DenyIdentified` tinyint(4) NOT NULL, + `AllowVoice` tinyint(4) NOT NULL, + `UseGlobalTime` tinyint(4) NOT NULL, + `PricePerMeter` int(11) NOT NULL, + `TaxFree` tinyint(4) NOT NULL, + `AllowDirectTeleport` tinyint(4) NOT NULL, + `RedirectGridX` int(11) NOT NULL, + `RedirectGridY` int(11) NOT NULL, + `ParentEstateID` int(10) NOT NULL, + `SunPosition` double NOT NULL, + `EstateSkipScripts` tinyint(4) NOT NULL, + `BillableFactor` float NOT NULL, + `PublicAccess` tinyint(4) NOT NULL +); +insert into `estate_settings` (`EstateID`,`EstateName`,`AbuseEmailToEstateOwner`,`DenyAnonymous`,`ResetHomeOnTeleport`,`FixedSun`,`DenyTransacted`,`BlockDwell`,`DenyIdentified`,`AllowVoice`,`UseGlobalTime`,`PricePerMeter`,`TaxFree`,`AllowDirectTeleport`,`RedirectGridX`,`RedirectGridY`,`ParentEstateID`,`SunPosition`,`PublicAccess`,`EstateSkipScripts`,`BillableFactor`) values ( 99, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); +delete from `estate_settings`; +CREATE TABLE `estate_users` ( + `EstateID` int(10) NOT NULL, + `uuid` char(36) NOT NULL +); +CREATE TABLE `estateban` ( + `EstateID` int(10) NOT NULL, + `bannedUUID` varchar(36) NOT NULL, + `bannedIp` varchar(16) NOT NULL, + `bannedIpHostMask` varchar(16) NOT NULL, + `bannedNameMask` varchar(64) default NULL +); +drop table if exists `regionsettings`; +CREATE TABLE `regionsettings` ( + `regionUUID` char(36) NOT NULL, + `block_terraform` int(11) NOT NULL, + `block_fly` int(11) NOT NULL, + `allow_damage` int(11) NOT NULL, + `restrict_pushing` int(11) NOT NULL, + `allow_land_resell` int(11) NOT NULL, + `allow_land_join_divide` int(11) NOT NULL, + `block_show_in_search` int(11) NOT NULL, + `agent_limit` int(11) NOT NULL, + `object_bonus` float NOT NULL, + `maturity` int(11) NOT NULL, + `disable_scripts` int(11) NOT NULL, + `disable_collisions` int(11) NOT NULL, + `disable_physics` int(11) NOT NULL, + `terrain_texture_1` char(36) NOT NULL, + `terrain_texture_2` char(36) NOT NULL, + `terrain_texture_3` char(36) NOT NULL, + `terrain_texture_4` char(36) NOT NULL, + `elevation_1_nw` float NOT NULL, + `elevation_2_nw` float NOT NULL, + `elevation_1_ne` float NOT NULL, + `elevation_2_ne` float NOT NULL, + `elevation_1_se` float NOT NULL, + `elevation_2_se` float NOT NULL, + `elevation_1_sw` float NOT NULL, + `elevation_2_sw` float NOT NULL, + `water_height` float NOT NULL, + `terrain_raise_limit` float NOT NULL, + `terrain_lower_limit` float NOT NULL, + `use_estate_sun` int(11) NOT NULL, + `fixed_sun` int(11) NOT NULL, + `sun_position` float NOT NULL, + `covenant` char(36) default NULL, + `Sandbox` tinyint(4) NOT NULL, + PRIMARY KEY (`regionUUID`) +); +CREATE INDEX `estate_ban_estate_id` on `estateban`(`EstateID`); +CREATE INDEX `estate_groups_estate_id` on `estate_groups`(`EstateID`); +CREATE INDEX `estate_managers_estate_id` on `estate_managers`(`EstateID`); +CREATE INDEX `estate_map_estate_id` on `estate_map`(`EstateID`); +CREATE UNIQUE INDEX `estate_map_region)id` on `estate_map`(`RegionID`); +CREATE INDEX `estate_users_estate_id` on `estate_users`(`EstateID`); diff --git a/OpenSim/Data/SQLite/SQLiteEstateData.cs b/OpenSim/Data/SQLite/SQLiteEstateData.cs new file mode 100644 index 0000000..2d046f2 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteEstateData.cs @@ -0,0 +1,333 @@ +/* + * 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.IO; +using System.Reflection; +using System.Threading; +using libsecondlife; +using Mono.Data.SqliteClient; +using log4net; +using OpenSim.Framework; +using OpenSim.Region.Environment.Interfaces; +using OpenSim.Region.Environment.Scenes; + +namespace OpenSim.Data.MySQL +{ + public class MySQLEstateStore : IEstateDataStore + { + private static readonly ILog m_log = + LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + private SqliteConnection m_connection; + private string m_connectionString; + + private FieldInfo[] m_Fields; + private Dictionary m_FieldMap = + new Dictionary(); + + public void Initialise(string connectionString) + { + m_connectionString = connectionString; + + m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString); + + m_connection = new SqliteConnection(m_connectionString); + m_connection.Open(); + + Assembly assem = GetType().Assembly; + Migration m = new Migration(m_connection, assem, "EstateStore"); + m.Update(); + + m_connection.Close(); + m_connection.Open(); + + Type t = typeof(EstateSettings); + m_Fields = t.GetFields(BindingFlags.NonPublic | + BindingFlags.Instance | + BindingFlags.DeclaredOnly); + + foreach (FieldInfo f in m_Fields) + if(f.Name.Substring(0, 2) == "m_") + m_FieldMap[f.Name.Substring(2)] = f; + } + + private string[] FieldList + { + get { return new List(m_FieldMap.Keys).ToArray(); } + } + + 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"; + + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = sql; + cmd.Parameters.Add("@RegionID", regionID.ToString()); + + IDataReader r = cmd.ExecuteReader(); + + if(r.Read()) + { + foreach (string name in FieldList) + { + if(m_FieldMap[name].GetValue(es) is bool) + { + int v = Convert.ToInt32(r[name]); + if(v != 0) + m_FieldMap[name].SetValue(es, true); + else + m_FieldMap[name].SetValue(es, false); + } + else + { + m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType)); + } + } + r.Close(); + } + else + { + // Migration case + // + r.Close(); + + List names = new List(FieldList); + + names.Remove("EstateID"); + + sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( @"+String.Join(", @", names.ToArray())+")"; + + cmd.CommandText = sql; + cmd.Parameters.Clear(); + + foreach (string name in FieldList) + { + if(m_FieldMap[name].GetValue(es) is bool) + { + if((bool)m_FieldMap[name].GetValue(es)) + cmd.Parameters.Add("@"+name, "1"); + else + cmd.Parameters.Add("@"+name, "0"); + } + else + { + cmd.Parameters.Add("@"+name, m_FieldMap[name].GetValue(es).ToString()); + } + } + + cmd.ExecuteNonQuery(); + + cmd.CommandText = "select LAST_INSERT_ROWID() as id"; + cmd.Parameters.Clear(); + + r = cmd.ExecuteReader(); + + r.Read(); + + es.EstateID = Convert.ToUInt32(r["id"]); + + r.Close(); + + cmd.CommandText = "insert into estate_map values (@RegionID, @EstateID)"; + cmd.Parameters.Add("@RegionID", regionID.ToString()); + cmd.Parameters.Add("@EstateID", es.EstateID.ToString()); + + // This will throw on dupe key + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception) + { + } + + // Munge and transfer the ban list + // + cmd.Parameters.Clear(); + cmd.CommandText = "insert into estateban select "+es.EstateID.ToString()+", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID"; + cmd.Parameters.Add("@UUID", regionID.ToString()); + + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception) + { + } + } + + LoadBanList(es); + + es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers"); + es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users"); + es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups"); + return es; + } + + public void StoreEstateSettings(EstateSettings es) + { + List fields = new List(FieldList); + fields.Remove("EstateID"); + + List terms = new List(); + + foreach (string f in fields) + terms.Add(f+" = @"+f); + + string sql = "update estate_settings set "+String.Join(", ", terms.ToArray())+" where EstateID = @EstateID"; + + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = sql; + + foreach (string name in FieldList) + { + if(m_FieldMap[name].GetValue(es) is bool) + { + if((bool)m_FieldMap[name].GetValue(es)) + cmd.Parameters.Add("@"+name, "1"); + else + cmd.Parameters.Add("@"+name, "0"); + } + else + { + cmd.Parameters.Add("@"+name, m_FieldMap[name].GetValue(es).ToString()); + } + } + + 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); + } + + private void LoadBanList(EstateSettings es) + { + es.ClearBans(); + + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = "select bannedUUID from estateban where EstateID = @EstateID"; + cmd.Parameters.Add("@EstateID", es.EstateID); + + IDataReader r = cmd.ExecuteReader(); + + while(r.Read()) + { + EstateBan eb = new EstateBan(); + + LLUUID uuid = new LLUUID(); + LLUUID.TryParse(r["bannedUUID"].ToString(), out uuid); + + eb.bannedUUID = uuid; + eb.bannedIP = "0.0.0.0"; + eb.bannedIPHostMask = "0.0.0.0"; + es.AddBan(eb); + } + r.Close(); + } + + private void SaveBanList(EstateSettings es) + { + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = "delete from estateban where EstateID = @EstateID"; + cmd.Parameters.Add("@EstateID", es.EstateID.ToString()); + + cmd.ExecuteNonQuery(); + + cmd.Parameters.Clear(); + + cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( @EstateID, @bannedUUID, '', '', '' )"; + + foreach(EstateBan b in es.EstateBans) + { + cmd.Parameters.Add("@EstateID", es.EstateID.ToString()); + cmd.Parameters.Add("@bannedUUID", b.bannedUUID.ToString()); + + cmd.ExecuteNonQuery(); + cmd.Parameters.Clear(); + } + } + + void SaveUUIDList(uint EstateID, string table, LLUUID[] data) + { + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = "delete from "+table+" where EstateID = @EstateID"; + cmd.Parameters.Add("@EstateID", EstateID.ToString()); + + cmd.ExecuteNonQuery(); + + cmd.Parameters.Clear(); + + cmd.CommandText = "insert into "+table+" (EstateID, uuid) values ( @EstateID, @uuid )"; + + foreach(LLUUID uuid in data) + { + cmd.Parameters.Add("@EstateID", EstateID.ToString()); + cmd.Parameters.Add("@uuid", uuid.ToString()); + + cmd.ExecuteNonQuery(); + cmd.Parameters.Clear(); + } + } + + LLUUID[] LoadUUIDList(uint EstateID, string table) + { + List uuids = new List(); + + SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand(); + + cmd.CommandText = "select uuid from "+table+" where EstateID = @EstateID"; + cmd.Parameters.Add("@EstateID", EstateID); + + IDataReader r = cmd.ExecuteReader(); + + while(r.Read()) + { + EstateBan eb = new EstateBan(); + + LLUUID uuid = new LLUUID(); + LLUUID.TryParse(r["uuid"].ToString(), out uuid); + + uuids.Add(uuid); + } + r.Close(); + + return uuids.ToArray(); + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs index 0e67fb9..4e87e0d 100644 --- a/OpenSim/Data/SQLite/SQLiteRegionData.cs +++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs @@ -62,7 +62,6 @@ namespace OpenSim.Data.SQLite private SqliteDataAdapter terrainDa; private SqliteDataAdapter landDa; private SqliteDataAdapter landAccessListDa; - private SqliteDataAdapter regionBanListDa; private SqliteConnection m_conn; @@ -119,9 +118,6 @@ namespace OpenSim.Data.SQLite SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn); landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd); - SqliteCommand regionBanListSelectCmd = new SqliteCommand(regionbanListSelect, m_conn); - regionBanListDa = new SqliteDataAdapter(regionBanListSelectCmd); - // This actually does the roll forward assembly stuff Assembly assem = GetType().Assembly; Migration m = new Migration(m_conn, assem, "RegionStore"); @@ -157,10 +153,6 @@ namespace OpenSim.Data.SQLite ds.Tables.Add(createLandAccessListTable()); setupLandAccessCommands(landAccessListDa, m_conn); - ds.Tables.Add(createRegionBanListTable()); - setupRegionBanCommands(regionBanListDa, m_conn); - - // WORKAROUND: This is a work around for sqlite on // windows, which gets really unhappy with blob columns // that have no sample data in them. At some point we @@ -201,15 +193,6 @@ namespace OpenSim.Data.SQLite m_log.Info("[REGION DB]: Caught fill error on landaccesslist table"); } - try - { - regionBanListDa.Fill(ds.Tables["regionban"]); - } - catch (Exception) - { - m_log.Info("[REGION DB]: Caught fill error on regionban table"); - } - return; } } @@ -900,21 +883,6 @@ namespace OpenSim.Data.SQLite return landaccess; } - /// - /// create "regionban" table - /// - /// regionban datatable - private static DataTable createRegionBanListTable() - { - DataTable regionbanlist = new DataTable("regionban"); - createCol(regionbanlist, "regionUUID", typeof(String)); - createCol(regionbanlist, "bannedUUID", typeof(String)); - createCol(regionbanlist, "bannedIp", typeof(String)); - createCol(regionbanlist, "bannedIpHostMask", typeof(String)); - - return regionbanlist; - } - /*********************************************************************** * * Convert between ADO.NET <=> OpenSim Objects @@ -1178,74 +1146,6 @@ namespace OpenSim.Data.SQLite return entry; } - - /// - /// Load a region banlist - /// - /// the region UUID - /// The banlist - public List LoadRegionBanList(LLUUID regionUUID) - { - List regionbanlist = new List(); - lock (ds) - { - DataTable regionban = ds.Tables["regionban"]; - string searchExp = "regionUUID = '" + regionUUID.ToString() + "'"; - DataRow[] rawbanlist = regionban.Select(searchExp); - foreach (DataRow rawbanrow in rawbanlist) - { - RegionBanListItem rbli = new RegionBanListItem(); - LLUUID tmpvalue = LLUUID.Zero; - - rbli.regionUUID = regionUUID; - - if (Helpers.TryParse((string)rawbanrow["bannedUUID"], out tmpvalue)) - rbli.bannedUUID = tmpvalue; - - rbli.bannedIP = (string)rawbanrow["bannedIp"]; - rbli.bannedIPHostMask = (string)rawbanrow["bannedIpHostMask"]; - regionbanlist.Add(rbli); - } - } - return regionbanlist; - } - - /// - /// Add en entry into region banlist - /// - /// - public void AddToRegionBanlist(RegionBanListItem item) - { - lock (ds) - { - using (SqliteCommand cmd = new SqliteCommand("insert into regionban (regionUUID, bannedUUID, bannedIp, bannedIpHostMask) values (:regionUUID,:bannedUUID,:bannedIp,:bannedIpHostMask)", m_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":regionUUID", item.regionUUID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":bannedUUID", item.bannedUUID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":bannedIp", item.bannedIP)); - cmd.Parameters.Add(new SqliteParameter(":bannedIpHostMask", item.bannedIPHostMask)); - cmd.ExecuteNonQuery(); - } - } - } - - /// - /// remove an entry from the region banlist - /// - /// - public void RemoveFromRegionBanlist(RegionBanListItem item) - { - lock (ds) - { - using (SqliteCommand cmd = new SqliteCommand("delete from regionban where regionUUID=:regionUUID AND bannedUUID=:bannedUUID", m_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":regionUUID", item.regionUUID.ToString())); - cmd.Parameters.Add(new SqliteParameter(":bannedUUID", item.bannedUUID.ToString())); - cmd.ExecuteNonQuery(); - } - } - } - /// /// /// @@ -1827,20 +1727,6 @@ namespace OpenSim.Data.SQLite /// /// /// - private void setupRegionBanCommands(SqliteDataAdapter da, SqliteConnection conn) - { - da.InsertCommand = createInsertCommand("regionban", ds.Tables["regionban"]); - da.InsertCommand.Connection = conn; - - da.UpdateCommand = createUpdateCommand("regionban", "regionUUID=:regionUUID AND bannedUUID=:bannedUUID", ds.Tables["regionban"]); - da.UpdateCommand.Connection = conn; - } - - /// - /// - /// - /// - /// private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn) { da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]); -- cgit v1.1