From ff8a76825841533bdc5d534b6f58b2ab964ea6c6 Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Sat, 12 Oct 2013 16:33:45 -0500 Subject: Fernando Oliveira's Postgress SQL Server Data Connector as a single commit. * Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs * PostgreSQL data access implementation * PostgreSQL dll binarie and RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres SQL Type fixes * Postgres SQL Connection string * Data type issues * more fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to internal csharp types * More data type fix (PostgreSQL fields are case sensitive) :( * more field case sensitive fixes * changed the migration files to be case sensitive for fields. * fixed fields case * finished converting, now search for hidden bugs. * some more fixes * bool type fixed * more case fixes; * creatorID case fixed * case fields fixed * fixed default now() for TMStamp fields with don't allow nulls. * fix case sensitve for Region name and Estate name * fixed case for names for search * fix class name Error * Bug fixed on select and migrations * Un-Reverting my change due to Postgres issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for integration with Diva Distro * Added System.Core to prebuild.xml for PG project * Configured to make DIff for Push to OpenSim Project * Diffs only to PostgreSQL mods. --- OpenSim/Data/PGSQL/PGSQLEstateData.cs | 598 ++++++++++++++++++++++++++++++++++ 1 file changed, 598 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLEstateData.cs (limited to 'OpenSim/Data/PGSQL/PGSQLEstateData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs new file mode 100644 index 0000000..347baf3 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs @@ -0,0 +1,598 @@ +/* + * 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 OpenSimulator 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.Reflection; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using OpenSim.Region.Framework.Interfaces; +using System.Data; +using Npgsql; +using NpgsqlTypes; + +namespace OpenSim.Data.PGSQL +{ + public class PGSQLEstateStore : IEstateDataStore + { + private const string _migrationStore = "EstateStore"; + + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + private PGSQLManager _Database; + private string m_connectionString; + private FieldInfo[] _Fields; + private Dictionary _FieldMap = new Dictionary(); + + #region Public methods + + public PGSQLEstateStore() + { + } + + public PGSQLEstateStore(string connectionString) + { + Initialise(connectionString); + } + + protected virtual Assembly Assembly + { + get { return GetType().Assembly; } + } + + /// + /// Initialises the estatedata class. + /// + /// connectionString. + public void Initialise(string connectionString) + { + if (!string.IsNullOrEmpty(connectionString)) + { + m_connectionString = connectionString; + _Database = new PGSQLManager(connectionString); + } + + //Migration settings + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "EstateStore"); + m.Update(); + } + + //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(UUID regionID, bool create) + { + 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 (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(_Database.CreateParameter("RegionID", regionID)); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + { + foreach (string name in FieldList) + { + FieldInfo f = _FieldMap[name]; + object v = reader[name]; + if (f.FieldType == typeof(bool)) + { + f.SetValue(es, v); + } + else if (f.FieldType == typeof(UUID)) + { + UUID estUUID = UUID.Zero; + + UUID.TryParse(v.ToString(), out estUUID); + + f.SetValue(es, estUUID); + } + else if (f.FieldType == typeof(string)) + { + f.SetValue(es, v.ToString()); + } + else if (f.FieldType == typeof(UInt32)) + { + f.SetValue(es, Convert.ToUInt32(v)); + } + else if (f.FieldType == typeof(Single)) + { + f.SetValue(es, Convert.ToSingle(v)); + } + else + f.SetValue(es, v); + } + } + else + { + insertEstate = true; + } + } + } + + if (insertEstate && create) + { + DoCreate(es); + LinkRegion(regionID, (int)es.EstateID); + } + + 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; + } + + public EstateSettings CreateNewEstate() + { + EstateSettings es = new EstateSettings(); + es.OnSave += StoreEstateSettings; + + DoCreate(es); + + 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; + } + + private void DoCreate(EstateSettings es) + { + List names = new List(FieldList); + + names.Remove("EstateID"); + + string sql = string.Format("insert into estate_settings (\"{0}\") values ( :{1} )", String.Join("\",\"", names.ToArray()), String.Join(", :", names.ToArray())); + + m_log.Debug("[DB ESTATE]: SQL: " + sql); + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand insertCommand = new NpgsqlCommand(sql, conn)) + { + insertCommand.CommandText = sql + "; Select cast(lastval() as int) as ID ;"; + + foreach (string name in names) + { + insertCommand.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es))); + } + //NpgsqlParameter idParameter = new NpgsqlParameter("ID", SqlDbType.Int); + //idParameter.Direction = ParameterDirection.Output; + //insertCommand.Parameters.Add(idParameter); + conn.Open(); + + es.EstateID = 100; + + using (NpgsqlDataReader result = insertCommand.ExecuteReader()) + { + if (result.Read()) + { + es.EstateID = (uint)result.GetInt32(0); + } + } + + } + + //TODO check if this is needed?? + es.Save(); + } + + /// + /// 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 "); + foreach (string name in names) + { + sql += "\"" + name + "\" = :" + name + ", "; + } + sql = sql.Remove(sql.LastIndexOf(",")); + sql += " WHERE \"EstateID\" = :EstateID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + foreach (string name in names) + { + cmd.Parameters.Add(_Database.CreateParameter("" + name, _FieldMap[name].GetValue(es))); + } + + cmd.Parameters.Add(_Database.CreateParameter("EstateID", es.EstateID)); + conn.Open(); + 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 (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + NpgsqlParameter idParameter = new NpgsqlParameter("EstateID", DbType.Int32); + idParameter.Value = es.EstateID; + cmd.Parameters.Add(idParameter); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + EstateBan eb = new EstateBan(); + + eb.BannedUserID = new UUID((Guid)reader["bannedUUID"]); //uuid; + eb.BannedHostAddress = "0.0.0.0"; + eb.BannedHostIPMask = "0.0.0.0"; + es.AddBan(eb); + } + } + } + } + + private UUID[] LoadUUIDList(uint estateID, string table) + { + List uuids = new List(); + + string sql = string.Format("select uuid from {0} where \"EstateID\" = :EstateID", table); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(_Database.CreateParameter("EstateID", estateID)); + conn.Open(); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + uuids.Add(new UUID((Guid)reader["uuid"])); //uuid); + } + } + } + + return uuids.ToArray(); + } + + private void SaveBanList(EstateSettings es) + { + //Delete first + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = conn.CreateCommand()) + { + cmd.CommandText = "delete from estateban where \"EstateID\" = :EstateID"; + cmd.Parameters.AddWithValue("EstateID", (int)es.EstateID); + cmd.ExecuteNonQuery(); + + //Insert after + cmd.CommandText = "insert into estateban (\"EstateID\", \"bannedUUID\",\"bannedIp\", \"bannedIpHostMask\", \"bannedNameMask\") values ( :EstateID, :bannedUUID, '','','' )"; + cmd.Parameters.AddWithValue("bannedUUID", Guid.Empty); + foreach (EstateBan b in es.EstateBans) + { + cmd.Parameters["bannedUUID"].Value = b.BannedUserID.Guid; + cmd.ExecuteNonQuery(); + } + } + } + } + + private void SaveUUIDList(uint estateID, string table, UUID[] data) + { + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = conn.CreateCommand()) + { + cmd.Parameters.AddWithValue("EstateID", (int)estateID); + cmd.CommandText = string.Format("delete from {0} where \"EstateID\" = :EstateID", table); + cmd.ExecuteNonQuery(); + + cmd.CommandText = string.Format("insert into {0} (\"EstateID\", uuid) values ( :EstateID, :uuid )", table); + cmd.Parameters.AddWithValue("uuid", Guid.Empty); + foreach (UUID uuid in data) + { + cmd.Parameters["uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works + cmd.ExecuteNonQuery(); + } + } + } + } + + public EstateSettings LoadEstateSettings(int estateID) + { + EstateSettings es = new EstateSettings(); + string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) + "\" from estate_settings where \"EstateID\" = :EstateID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("EstateID", (int)estateID); + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) + { + foreach (string name in FieldList) + { + FieldInfo f = _FieldMap[name]; + object v = reader[name]; + if (f.FieldType == typeof(bool)) + { + f.SetValue(es, Convert.ToInt32(v) != 0); + } + else if (f.FieldType == typeof(UUID)) + { + f.SetValue(es, new UUID((Guid)v)); // uuid); + } + else if (f.FieldType == typeof(string)) + { + f.SetValue(es, v.ToString()); + } + else if (f.FieldType == typeof(UInt32)) + { + f.SetValue(es, Convert.ToUInt32(v)); + } + else if (f.FieldType == typeof(Single)) + { + f.SetValue(es, Convert.ToSingle(v)); + } + else + f.SetValue(es, v); + } + } + + } + } + } + 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; + + } + + public List LoadEstateSettingsAll() + { + List allEstateSettings = new List(); + + List allEstateIds = GetEstatesAll(); + + foreach (int estateId in allEstateIds) + allEstateSettings.Add(LoadEstateSettings(estateId)); + + return allEstateSettings; + } + + public List GetEstates(string search) + { + List result = new List(); + string sql = "select \"estateID\" from estate_settings where lower(\"EstateName\") = lower(:EstateName)"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("EstateName", search); + + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + result.Add(Convert.ToInt32(reader["EstateID"])); + } + reader.Close(); + } + } + } + + return result; + } + + public List GetEstatesAll() + { + List result = new List(); + string sql = "select \"EstateID\" from estate_settings"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + result.Add(Convert.ToInt32(reader["EstateID"])); + } + reader.Close(); + } + } + } + + return result; + } + + public List GetEstatesByOwner(UUID ownerID) + { + List result = new List(); + string sql = "select \"estateID\" from estate_settings where \"EstateOwner\" = :EstateOwner"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("EstateOwner", ownerID); + + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + result.Add(Convert.ToInt32(reader["EstateID"])); + } + reader.Close(); + } + } + } + + return result; + } + + public bool LinkRegion(UUID regionID, int estateID) + { + string deleteSQL = "delete from estate_map where \"RegionID\" = :RegionID"; + string insertSQL = "insert into estate_map values (:RegionID, :EstateID)"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + + NpgsqlTransaction transaction = conn.BeginTransaction(); + + try + { + using (NpgsqlCommand cmd = new NpgsqlCommand(deleteSQL, conn)) + { + cmd.Transaction = transaction; + cmd.Parameters.AddWithValue("RegionID", regionID.Guid); + + cmd.ExecuteNonQuery(); + } + + using (NpgsqlCommand cmd = new NpgsqlCommand(insertSQL, conn)) + { + cmd.Transaction = transaction; + cmd.Parameters.AddWithValue("RegionID", regionID.Guid); + cmd.Parameters.AddWithValue("EstateID", estateID); + + int ret = cmd.ExecuteNonQuery(); + + if (ret != 0) + transaction.Commit(); + else + transaction.Rollback(); + + return (ret != 0); + } + } + catch (Exception ex) + { + m_log.Error("[REGION DB]: LinkRegion failed: " + ex.Message); + transaction.Rollback(); + } + } + return false; + } + + public List GetRegions(int estateID) + { + List result = new List(); + string sql = "select \"RegionID\" from estate_map where \"EstateID\" = :EstateID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) + { + conn.Open(); + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("EstateID", estateID); + + using (IDataReader reader = cmd.ExecuteReader()) + { + while (reader.Read()) + { + result.Add(DBGuid.FromDB(reader["RegionID"])); + } + reader.Close(); + } + } + } + + return result; + } + + public bool DeleteEstate(int estateID) + { + // TODO: Implementation! + return false; + } + #endregion + } +} -- cgit v1.1 From 3e1ca2bd2136a51f23dff0d31f4725b4e05c3f7c Mon Sep 17 00:00:00 2001 From: fernando Date: Tue, 15 Oct 2013 11:55:08 -0500 Subject: * Fixes mantis #6802 Simulator crashes whist loading (lighshare enabled) * Please test --- OpenSim/Data/PGSQL/PGSQLEstateData.cs | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) (limited to 'OpenSim/Data/PGSQL/PGSQLEstateData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs index 347baf3..141b8ed 100644 --- a/OpenSim/Data/PGSQL/PGSQLEstateData.cs +++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs @@ -201,11 +201,10 @@ namespace OpenSim.Data.PGSQL string sql = string.Format("insert into estate_settings (\"{0}\") values ( :{1} )", String.Join("\",\"", names.ToArray()), String.Join(", :", names.ToArray())); - m_log.Debug("[DB ESTATE]: SQL: " + sql); using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) using (NpgsqlCommand insertCommand = new NpgsqlCommand(sql, conn)) { - insertCommand.CommandText = sql + "; Select cast(lastval() as int) as ID ;"; + insertCommand.CommandText = sql; foreach (string name in names) { @@ -218,11 +217,16 @@ namespace OpenSim.Data.PGSQL es.EstateID = 100; - using (NpgsqlDataReader result = insertCommand.ExecuteReader()) + if (insertCommand.ExecuteNonQuery() > 0) { - if (result.Read()) + insertCommand.CommandText = "Select cast(lastval() as int) as ID ;"; + + using (NpgsqlDataReader result = insertCommand.ExecuteReader()) { - es.EstateID = (uint)result.GetInt32(0); + if (result.Read()) + { + es.EstateID = (uint)result.GetInt32(0); + } } } -- cgit v1.1 From 67ffb64764aa109eee479444318b095730644c6d Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Wed, 16 Oct 2013 23:38:13 -0300 Subject: Corrected estateID to EstateID on getEstates function at PGSQLEstateData.cs --- OpenSim/Data/PGSQL/PGSQLEstateData.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'OpenSim/Data/PGSQL/PGSQLEstateData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs index 141b8ed..5ad0eaa 100644 --- a/OpenSim/Data/PGSQL/PGSQLEstateData.cs +++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs @@ -452,7 +452,7 @@ namespace OpenSim.Data.PGSQL public List GetEstates(string search) { List result = new List(); - string sql = "select \"estateID\" from estate_settings where lower(\"EstateName\") = lower(:EstateName)"; + string sql = "select \"EstateID\" from estate_settings where lower(\"EstateName\") = lower(:EstateName)"; using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) { conn.Open(); -- cgit v1.1