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/PGSQLGenericTableHandler.cs | 519 +++++++++++++++++++++++++ 1 file changed, 519 insertions(+) create mode 100644 OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs (limited to 'OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs') diff --git a/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs new file mode 100644 index 0000000..2151568 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs @@ -0,0 +1,519 @@ +/* + * 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.Reflection; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using OpenSim.Region.Framework.Interfaces; +using System.Text; +using Npgsql; + +namespace OpenSim.Data.PGSQL +{ + public class PGSQLGenericTableHandler : PGSqlFramework where T : class, new() + { + private static readonly ILog m_log = + LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + protected string m_ConnectionString; + protected PGSQLManager m_database; //used for parameter type translation + protected Dictionary m_Fields = + new Dictionary(); + + protected Dictionary m_FieldTypes = new Dictionary(); + + protected List m_ColumnNames = null; + protected string m_Realm; + protected FieldInfo m_DataField = null; + + protected virtual Assembly Assembly + { + get { return GetType().Assembly; } + } + + public PGSQLGenericTableHandler(string connectionString, + string realm, string storeName) + : base(connectionString) + { + m_Realm = realm; + + m_ConnectionString = connectionString; + + if (storeName != String.Empty) + { + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, storeName); + m.Update(); + } + + } + m_database = new PGSQLManager(m_ConnectionString); + + Type t = typeof(T); + FieldInfo[] fields = t.GetFields(BindingFlags.Public | + BindingFlags.Instance | + BindingFlags.DeclaredOnly); + + LoadFieldTypes(); + + if (fields.Length == 0) + return; + + foreach (FieldInfo f in fields) + { + if (f.Name != "Data") + m_Fields[f.Name] = f; + else + m_DataField = f; + } + + } + + 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]); + } + } + } + } + + private void CheckColumnNames(NpgsqlDataReader reader) + { + if (m_ColumnNames != null) + return; + + m_ColumnNames = new List(); + + DataTable schemaTable = reader.GetSchemaTable(); + + foreach (DataRow row in schemaTable.Rows) + { + if (row["ColumnName"] != null && + (!m_Fields.ContainsKey(row["ColumnName"].ToString()))) + m_ColumnNames.Add(row["ColumnName"].ToString()); + + } + } + + // TODO GET CONSTRAINTS FROM POSTGRESQL + private List GetConstraints() + { + List constraints = new List(); + string query = string.Format(@"SELECT kcu.column_name + FROM information_schema.table_constraints tc + LEFT JOIN information_schema.key_column_usage kcu + ON tc.constraint_catalog = kcu.constraint_catalog + AND tc.constraint_schema = kcu.constraint_schema + AND tc.constraint_name = kcu.constraint_name + + LEFT JOIN information_schema.referential_constraints rc + ON tc.constraint_catalog = rc.constraint_catalog + AND tc.constraint_schema = rc.constraint_schema + AND tc.constraint_name = rc.constraint_name + + LEFT JOIN information_schema.constraint_column_usage ccu + ON rc.unique_constraint_catalog = ccu.constraint_catalog + AND rc.unique_constraint_schema = ccu.constraint_schema + AND rc.unique_constraint_name = ccu.constraint_name + + where tc.table_name = lower('{0}') + and lower(tc.constraint_type) in ('primary key') + and kcu.column_name is not null + ;", 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 + constraints.Add((string)rdr[0]); + } + } + return constraints; + } + } + + public virtual T[] Get(string field, string key) + { + return Get(new string[] { field }, new string[] { key }); + } + + public virtual T[] Get(string[] fields, string[] keys) + { + if (fields.Length != keys.Length) + return new T[0]; + + List terms = new List(); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + + for (int i = 0; i < fields.Length; i++) + { + if ( m_FieldTypes.ContainsKey(fields[i]) ) + cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i], m_FieldTypes[fields[i]])); + else + cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i])); + + terms.Add(" \"" + fields[i] + "\" = :" + fields[i]); + } + + string where = String.Join(" AND ", terms.ToArray()); + + string query = String.Format("SELECT * FROM {0} WHERE {1}", + m_Realm, where); + + cmd.Connection = conn; + cmd.CommandText = query; + conn.Open(); + return DoQuery(cmd); + } + } + + protected T[] DoQuery(NpgsqlCommand cmd) + { + List result = new List(); + if (cmd.Connection == null) + { + cmd.Connection = new NpgsqlConnection(m_connectionString); + } + if (cmd.Connection.State == ConnectionState.Closed) + { + cmd.Connection.Open(); + } + using (NpgsqlDataReader reader = cmd.ExecuteReader()) + { + if (reader == null) + return new T[0]; + + CheckColumnNames(reader); + + while (reader.Read()) + { + T row = new T(); + + foreach (string name in m_Fields.Keys) + { + if (m_Fields[name].GetValue(row) is bool) + { + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v != 0 ? true : false); + } + else if (m_Fields[name].GetValue(row) is UUID) + { + UUID uuid = UUID.Zero; + + UUID.TryParse(reader[name].ToString(), out uuid); + m_Fields[name].SetValue(row, uuid); + } + else if (m_Fields[name].GetValue(row) is int) + { + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v); + } + else + { + m_Fields[name].SetValue(row, reader[name]); + } + } + + if (m_DataField != null) + { + Dictionary data = + new Dictionary(); + + foreach (string col in m_ColumnNames) + { + data[col] = reader[col].ToString(); + + if (data[col] == null) + data[col] = String.Empty; + } + + m_DataField.SetValue(row, data); + } + + result.Add(row); + } + return result.ToArray(); + } + } + + public virtual T[] Get(string where) + { + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + + string query = String.Format("SELECT * FROM {0} WHERE {1}", + m_Realm, where); + cmd.Connection = conn; + cmd.CommandText = query; + + //m_log.WarnFormat("[PGSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where); + + conn.Open(); + return DoQuery(cmd); + } + } + + public virtual bool Store(T row) + { + List constraintFields = GetConstraints(); + List> constraints = new List>(); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + + StringBuilder query = new StringBuilder(); + List names = new List(); + List values = new List(); + + foreach (FieldInfo fi in m_Fields.Values) + { + names.Add(fi.Name); + values.Add(":" + fi.Name); + // Temporarily return more information about what field is unexpectedly null for + // http://opensimulator.org/mantis/view.php?id=5403. This might be due to a bug in the + // InventoryTransferModule or we may be required to substitute a DBNull here. + if (fi.GetValue(row) == null) + throw new NullReferenceException( + string.Format( + "[PGSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null", + fi.Name, row)); + + if (constraintFields.Count > 0 && constraintFields.Contains(fi.Name)) + { + constraints.Add(new KeyValuePair(fi.Name, fi.GetValue(row).ToString() )); + } + if (m_FieldTypes.ContainsKey(fi.Name)) + cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row), m_FieldTypes[fi.Name])); + else + cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row))); + } + + if (m_DataField != null) + { + Dictionary data = + (Dictionary)m_DataField.GetValue(row); + + foreach (KeyValuePair kvp in data) + { + if (constraintFields.Count > 0 && constraintFields.Contains(kvp.Key)) + { + constraints.Add(new KeyValuePair(kvp.Key, kvp.Key)); + } + names.Add(kvp.Key); + values.Add(":" + kvp.Key); + + if (m_FieldTypes.ContainsKey(kvp.Key)) + cmd.Parameters.Add(m_database.CreateParameter("" + kvp.Key, kvp.Value, m_FieldTypes[kvp.Key])); + else + cmd.Parameters.Add(m_database.CreateParameter("" + kvp.Key, kvp.Value)); + } + + } + + query.AppendFormat("UPDATE {0} SET ", m_Realm); + int i = 0; + for (i = 0; i < names.Count - 1; i++) + { + query.AppendFormat("\"{0}\" = {1}, ", names[i], values[i]); + } + query.AppendFormat("\"{0}\" = {1} ", names[i], values[i]); + if (constraints.Count > 0) + { + List terms = new List(); + for (int j = 0; j < constraints.Count; j++) + { + terms.Add(String.Format(" \"{0}\" = :{0}", constraints[j].Key)); + } + string where = String.Join(" AND ", terms.ToArray()); + query.AppendFormat(" WHERE {0} ", where); + + } + cmd.Connection = conn; + cmd.CommandText = query.ToString(); + + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + { + //m_log.WarnFormat("[PGSQLGenericTable]: Updating {0}", m_Realm); + return true; + } + else + { + // assume record has not yet been inserted + + query = new StringBuilder(); + query.AppendFormat("INSERT INTO {0} (\"", m_Realm); + query.Append(String.Join("\",\"", names.ToArray())); + query.Append("\") values (" + String.Join(",", values.ToArray()) + ")"); + cmd.Connection = conn; + cmd.CommandText = query.ToString(); + + // m_log.WarnFormat("[PGSQLGenericTable]: Inserting into {0} sql {1}", m_Realm, cmd.CommandText); + + if (conn.State != ConnectionState.Open) + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + return true; + } + + return false; + } + } + + public virtual bool Delete(string field, string key) + { + return Delete(new string[] { field }, new string[] { key }); + } + + public virtual bool Delete(string[] fields, string[] keys) + { + if (fields.Length != keys.Length) + return false; + + List terms = new List(); + + using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + for (int i = 0; i < fields.Length; i++) + { + if (m_FieldTypes.ContainsKey(fields[i])) + cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i], m_FieldTypes[fields[i]])); + else + cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i])); + + terms.Add(" \"" + fields[i] + "\" = :" + fields[i]); + } + + string where = String.Join(" AND ", terms.ToArray()); + + string query = String.Format("DELETE FROM {0} WHERE {1}", m_Realm, where); + + cmd.Connection = conn; + cmd.CommandText = query; + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + { + //m_log.Warn("[PGSQLGenericTable]: " + deleteCommand); + return true; + } + return false; + } + } + public long GetCount(string field, string key) + { + return GetCount(new string[] { field }, new string[] { key }); + } + + public long GetCount(string[] fields, string[] keys) + { + if (fields.Length != keys.Length) + return 0; + + List terms = new List(); + + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + for (int i = 0; i < fields.Length; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("\"" + fields[i] + "\" = :" + fields[i]); + } + + string where = String.Join(" and ", terms.ToArray()); + + string query = String.Format("select count(*) from {0} where {1}", + m_Realm, where); + + cmd.CommandText = query; + + Object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public long GetCount(string where) + { + using (NpgsqlCommand cmd = new NpgsqlCommand()) + { + string query = String.Format("select count(*) from {0} where {1}", + m_Realm, where); + + cmd.CommandText = query; + + object result = DoQueryScalar(cmd); + + return Convert.ToInt64(result); + } + } + + public object DoQueryScalar(NpgsqlCommand cmd) + { + using (NpgsqlConnection dbcon = new NpgsqlConnection(m_ConnectionString)) + { + dbcon.Open(); + cmd.Connection = dbcon; + + return cmd.ExecuteScalar(); + } + } + } +} -- cgit v1.1