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/PGSQLRegionData.cs | 392 ++++++++++++++++++++++++++++++++++ 1 file changed, 392 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLRegionData.cs (limited to 'OpenSim/Data/PGSQL/PGSQLRegionData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs new file mode 100644 index 0000000..8a46559 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs @@ -0,0 +1,392 @@ +/* + * 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.Data; +using System.Drawing; +using System.IO; +using System.Reflection; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using OpenSim.Region.Framework.Interfaces; +using OpenSim.Region.Framework.Scenes; +using RegionFlags = OpenSim.Framework.RegionFlags; +using Npgsql; + +namespace OpenSim.Data.PGSQL +{ + /// + /// A PGSQL Interface for the Region Server. + /// + public class PGSQLRegionData : IRegionData + { + private string m_Realm; + private List m_ColumnNames = null; + private string m_ConnectionString; + private PGSQLManager m_database; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + protected Dictionary m_FieldTypes = new Dictionary(); + + protected virtual Assembly Assembly + { + get { return GetType().Assembly; } + } + + public PGSQLRegionData(string connectionString, string realm) + { + m_Realm = realm; + m_ConnectionString = connectionString; + m_database = new PGSQLManager(connectionString); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "GridStore"); + m.Update(); + } + LoadFieldTypes(); + } + + private void LoadFieldTypes() + { + m_FieldTypes = new Dictionary(); + + string query = string.Format(@"select column_name,data_type + from INFORMATION_SCHEMA.COLUMNS + where table_name = lower('{0}'); + + ", m_Realm); + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn)) + { + conn.Open(); + using (NpgsqlDataReader rdr = cmd.ExecuteReader()) + { + while (rdr.Read()) + { + // query produces 0 to many rows of single column, so always add the first item in each row + m_FieldTypes.Add((string)rdr[0], (string)rdr[1]); + } + } + } + } + + public List Get(string regionName, UUID scopeID) + { + string sql = "select * from "+m_Realm+" where lower(\"regionName\") like lower(:regionName) "; + if (scopeID != UUID.Zero) + sql += " and \"ScopeID\" = :scopeID"; + sql += " order by lower(\"regionName\")"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("regionName", regionName)); + if (scopeID != UUID.Zero) + cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); + conn.Open(); + return RunCommand(cmd); + } + } + + public RegionData Get(int posX, int posY, UUID scopeID) + { + string sql = "select * from "+m_Realm+" where \"locX\" = :posX and \"locY\" = :posY"; + if (scopeID != UUID.Zero) + sql += " and \"ScopeID\" = :scopeID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("posX", posX)); + cmd.Parameters.Add(m_database.CreateParameter("posY", posY)); + if (scopeID != UUID.Zero) + cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); + conn.Open(); + List ret = RunCommand(cmd); + if (ret.Count == 0) + return null; + + return ret[0]; + } + } + + public RegionData Get(UUID regionID, UUID scopeID) + { + string sql = "select * from "+m_Realm+" where uuid = :regionID"; + if (scopeID != UUID.Zero) + sql += " and \"ScopeID\" = :scopeID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("regionID", regionID)); + if (scopeID != UUID.Zero) + cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); + conn.Open(); + List ret = RunCommand(cmd); + if (ret.Count == 0) + return null; + + return ret[0]; + } + } + + public List Get(int startX, int startY, int endX, int endY, UUID scopeID) + { + string sql = "select * from "+m_Realm+" where \"locX\" between :startX and :endX and \"locY\" between :startY and :endY"; + if (scopeID != UUID.Zero) + sql += " and \"ScopeID\" = :scopeID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("startX", startX)); + cmd.Parameters.Add(m_database.CreateParameter("startY", startY)); + cmd.Parameters.Add(m_database.CreateParameter("endX", endX)); + cmd.Parameters.Add(m_database.CreateParameter("endY", endY)); + cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); + conn.Open(); + return RunCommand(cmd); + } + } + + public List RunCommand(NpgsqlCommand cmd) + { + List retList = new List(); + + NpgsqlDataReader result = cmd.ExecuteReader(); + + while (result.Read()) + { + RegionData ret = new RegionData(); + ret.Data = new Dictionary(); + + UUID regionID; + UUID.TryParse(result["uuid"].ToString(), out regionID); + ret.RegionID = regionID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + ret.RegionName = result["regionName"].ToString(); + ret.posX = Convert.ToInt32(result["locX"]); + ret.posY = Convert.ToInt32(result["locY"]); + ret.sizeX = Convert.ToInt32(result["sizeX"]); + ret.sizeY = Convert.ToInt32(result["sizeY"]); + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "uuid") + continue; + if (s == "ScopeID") + continue; + if (s == "regionName") + continue; + if (s == "locX") + continue; + if (s == "locY") + continue; + + ret.Data[s] = result[s].ToString(); + } + + retList.Add(ret); + } + return retList; + } + + public bool Store(RegionData data) + { + if (data.Data.ContainsKey("uuid")) + data.Data.Remove("uuid"); + if (data.Data.ContainsKey("ScopeID")) + data.Data.Remove("ScopeID"); + if (data.Data.ContainsKey("regionName")) + data.Data.Remove("regionName"); + if (data.Data.ContainsKey("posX")) + data.Data.Remove("posX"); + if (data.Data.ContainsKey("posY")) + data.Data.Remove("posY"); + if (data.Data.ContainsKey("sizeX")) + data.Data.Remove("sizeX"); + if (data.Data.ContainsKey("sizeY")) + data.Data.Remove("sizeY"); + if (data.Data.ContainsKey("locX")) + data.Data.Remove("locX"); + if (data.Data.ContainsKey("locY")) + data.Data.Remove("locY"); + + string[] fields = new List(data.Data.Keys).ToArray(); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + + string update = "update " + m_Realm + " set \"locX\"=:posX, \"locY\"=:posY, \"sizeX\"=:sizeX, \"sizeY\"=:sizeY "; + + foreach (string field in fields) + { + + update += ", "; + update += " \"" + field + "\" = :" + field; + + if (m_FieldTypes.ContainsKey(field)) + cmd.Parameters.Add(m_database.CreateParameter(field, data.Data[field], m_FieldTypes[field])); + else + cmd.Parameters.Add(m_database.CreateParameter(field, data.Data[field])); + } + + update += " where uuid = :regionID"; + + if (data.ScopeID != UUID.Zero) + update += " and \"ScopeID\" = :scopeID"; + + cmd.CommandText = update; + cmd.Connection = conn; + cmd.Parameters.Add(m_database.CreateParameter("regionID", data.RegionID)); + cmd.Parameters.Add(m_database.CreateParameter("regionName", data.RegionName)); + cmd.Parameters.Add(m_database.CreateParameter("scopeID", data.ScopeID)); + cmd.Parameters.Add(m_database.CreateParameter("posX", data.posX)); + cmd.Parameters.Add(m_database.CreateParameter("posY", data.posY)); + cmd.Parameters.Add(m_database.CreateParameter("sizeX", data.sizeX)); + cmd.Parameters.Add(m_database.CreateParameter("sizeY", data.sizeY)); + conn.Open(); + try + { + if (cmd.ExecuteNonQuery() < 1) + { + string insert = "insert into " + m_Realm + " (uuid, \"ScopeID\", \"locX\", \"locY\", \"sizeX\", \"sizeY\", \"regionName\", \"" + + String.Join("\", \"", fields) + + "\") values (:regionID, :scopeID, :posX, :posY, :sizeX, :sizeY, :regionName, :" + String.Join(", :", fields) + ")"; + + cmd.CommandText = insert; + + try + { + if (cmd.ExecuteNonQuery() < 1) + { + return false; + } + } + catch (Exception ex) + { + m_log.Warn("[PGSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert); + } + } + } + catch (Exception ex) + { + m_log.Warn("[PGSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update); + } + } + + return true; + } + + public bool SetDataItem(UUID regionID, string item, string value) + { + string sql = "update " + m_Realm + + " set \"" + item + "\" = :" + item + " where uuid = :UUID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("" + item, value)); + cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID)); + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + + public bool Delete(UUID regionID) + { + string sql = "delete from " + m_Realm + + " where uuid = :UUID"; + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID)); + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + + public List GetDefaultRegions(UUID scopeID) + { + return Get((int)RegionFlags.DefaultRegion, scopeID); + } + + public List GetDefaultHypergridRegions(UUID scopeID) + { + return Get((int)RegionFlags.DefaultHGRegion, scopeID); + } + + public List GetFallbackRegions(UUID scopeID, int x, int y) + { + List regions = Get((int)RegionFlags.FallbackRegion, scopeID); + RegionDataDistanceCompare distanceComparer = new RegionDataDistanceCompare(x, y); + regions.Sort(distanceComparer); + + return regions; + } + + public List GetHyperlinks(UUID scopeID) + { + return Get((int)RegionFlags.Hyperlink, scopeID); + } + + private List Get(int regionFlags, UUID scopeID) + { + string sql = "SELECT * FROM " + m_Realm + " WHERE (flags & " + regionFlags.ToString() + ") <> 0"; + if (scopeID != UUID.Zero) + sql += " AND \"ScopeID\" = :scopeID"; + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) + { + cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); + conn.Open(); + return RunCommand(cmd); + } + } + } +} -- cgit v1.1 From 0ad45531a18ad577b36818fbe5f51f23ff4a2a9b Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Mon, 25 Nov 2013 21:20:56 -0200 Subject: Corrected case to get columns from Regions table from PostgreSQL http://opensimulator.org/mantis/view.php?id=6865 Signed-off-by: Michael Cerquoni --- OpenSim/Data/PGSQL/PGSQLRegionData.cs | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'OpenSim/Data/PGSQL/PGSQLRegionData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs index 8a46559..f3e4064 100644 --- a/OpenSim/Data/PGSQL/PGSQLRegionData.cs +++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs @@ -206,7 +206,7 @@ namespace OpenSim.Data.PGSQL DataTable schemaTable = result.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) - m_ColumnNames.Add(row["ColumnName"].ToString()); + m_ColumnNames.Add(row["column_name"].ToString()); } foreach (string s in m_ColumnNames) @@ -376,7 +376,7 @@ namespace OpenSim.Data.PGSQL private List Get(int regionFlags, UUID scopeID) { - string sql = "SELECT * FROM " + m_Realm + " WHERE (flags & " + regionFlags.ToString() + ") <> 0"; + string sql = "SELECT * FROM " + m_Realm + " WHERE (\"flags\" & " + regionFlags.ToString() + ") <> 0"; if (scopeID != UUID.Zero) sql += " AND \"ScopeID\" = :scopeID"; -- cgit v1.1 From 9b76a46df0b93e57a1bcb2cfa28248998b8ed841 Mon Sep 17 00:00:00 2001 From: Fernando Oliveira Date: Thu, 28 Nov 2013 00:10:36 -0200 Subject: Reversing back to the row["ColumnName"] case field name. http://opensimulator.org/mantis/view.php?id=6868 --- OpenSim/Data/PGSQL/PGSQLRegionData.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'OpenSim/Data/PGSQL/PGSQLRegionData.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs index f3e4064..b3076f0 100644 --- a/OpenSim/Data/PGSQL/PGSQLRegionData.cs +++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs @@ -206,7 +206,7 @@ namespace OpenSim.Data.PGSQL DataTable schemaTable = result.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) - m_ColumnNames.Add(row["column_name"].ToString()); + m_ColumnNames.Add(row["ColumnName"].ToString()); } foreach (string s in m_ColumnNames) -- cgit v1.1