1 files changed, 203 insertions, 50 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs
index 4145d95..826c6fc 100644
--- a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
+++ b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs
@@ -30,30 +30,38 @@ using System.Collections.Generic;
 using System.Data;
 using System.Reflection;
 using log4net;
-using System.Data.SqlClient;
 using OpenMetaverse;
 using OpenSim.Framework;
 using OpenSim.Region.Framework.Interfaces;
 using System.Text;
+using Npgsql;
 
-namespace OpenSim.Data.MSSQL
+namespace OpenSim.Data.PGSQL
 {
-    public class MSSQLGenericTableHandler<T> where T : class, new()
+    public class PGSQLGenericTableHandler<T> : PGSqlFramework where T : class, new()
     {
-//        private static readonly ILog m_log =
-//            LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
+        private static readonly ILog m_log =
+            LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
 
         protected string m_ConnectionString;
-        protected MSSQLManager m_database; //used for parameter type translation
+        protected PGSQLManager m_database; //used for parameter type translation
         protected Dictionary<string, FieldInfo> m_Fields =
                 new Dictionary<string, FieldInfo>();
 
+        protected Dictionary<string, string> m_FieldTypes = new Dictionary<string, string>();
+
         protected List<string> m_ColumnNames = null;
         protected string m_Realm;
         protected FieldInfo m_DataField = null;
 
-        public MSSQLGenericTableHandler(string connectionString,
+        protected virtual Assembly Assembly
+        {
+            get { return GetType().Assembly; }
+        }
+
+        public PGSQLGenericTableHandler(string connectionString,
                 string realm, string storeName)
+            : base(connectionString)
         {
             m_Realm = realm;
             
@@ -61,7 +69,7 @@ namespace OpenSim.Data.MSSQL
 
             if (storeName != String.Empty)
             {
-                using (SqlConnection conn = new SqlConnection(m_ConnectionString))
+                using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
                 {
                     conn.Open();
                     Migration m = new Migration(conn, GetType().Assembly, storeName);
@@ -69,13 +77,15 @@ namespace OpenSim.Data.MSSQL
                 }
 
             }
-            m_database = new MSSQLManager(m_ConnectionString);
+            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;
 
@@ -89,7 +99,31 @@ namespace OpenSim.Data.MSSQL
 
         }
 
-        private void CheckColumnNames(SqlDataReader reader)
+        private void LoadFieldTypes()
+        {
+            m_FieldTypes = new Dictionary<string, string>();
+
+            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;
@@ -97,6 +131,7 @@ namespace OpenSim.Data.MSSQL
             m_ColumnNames = new List<string>();
 
             DataTable schemaTable = reader.GetSchemaTable();
+
             foreach (DataRow row in schemaTable.Rows)
             {
                 if (row["ColumnName"] != null &&
@@ -106,21 +141,37 @@ namespace OpenSim.Data.MSSQL
             }
         }
 
+        // TODO GET CONSTRAINTS FROM POSTGRESQL
         private List<string> GetConstraints()
         {
             List<string> constraints = new List<string>();
-            string query = string.Format(@"SELECT 
-                            COL_NAME(ic.object_id,ic.column_id) AS column_name
-                            FROM sys.indexes AS i
-                            INNER JOIN sys.index_columns AS ic 
-                              ON i.object_id = ic.object_id AND i.index_id = ic.index_id
-                            WHERE i.is_primary_key = 1 
-                            AND i.object_id = OBJECT_ID('{0}');", m_Realm);
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(query, conn))
+            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 (SqlDataReader rdr = cmd.ExecuteReader())
+                using (NpgsqlDataReader rdr = cmd.ExecuteReader())
                 {
                     while (rdr.Read())
                     {
@@ -144,14 +195,18 @@ namespace OpenSim.Data.MSSQL
 
             List<string> terms = new List<string>();
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand())
+            using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
+            using (NpgsqlCommand cmd = new NpgsqlCommand())
             {
 
                 for (int i = 0; i < fields.Length; i++)
                 {
-                    cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i]));
-                    terms.Add("[" + fields[i] + "] = @" + fields[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());
@@ -166,10 +221,18 @@ namespace OpenSim.Data.MSSQL
             }
         }
 
-        protected T[] DoQuery(SqlCommand cmd)
+        protected T[] DoQuery(NpgsqlCommand cmd)
         {
             List<T> result = new List<T>();
-            using (SqlDataReader reader = cmd.ExecuteReader())
+            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];
@@ -213,6 +276,7 @@ namespace OpenSim.Data.MSSQL
                         foreach (string col in m_ColumnNames)
                         {
                             data[col] = reader[col].ToString();
+
                             if (data[col] == null)
                                 data[col] = String.Empty;
                         }
@@ -228,16 +292,34 @@ namespace OpenSim.Data.MSSQL
 
         public virtual T[] Get(string where)
         {
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand())
+            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);
 
-                //m_log.WarnFormat("[MSSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where);
+                conn.Open();
+                return DoQuery(cmd);
+            }
+        }
+
+        public virtual T[] Get(string where, NpgsqlParameter parameter)
+        {
+            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);
+
+                cmd.Parameters.Add(parameter);
 
                 conn.Open();
                 return DoQuery(cmd);
@@ -249,8 +331,8 @@ namespace OpenSim.Data.MSSQL
             List<string> constraintFields = GetConstraints();
             List<KeyValuePair<string, string>> constraints = new List<KeyValuePair<string, string>>();
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand())
+            using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
+            using (NpgsqlCommand cmd = new NpgsqlCommand())
             {
 
                 StringBuilder query = new StringBuilder();
@@ -260,21 +342,24 @@ namespace OpenSim.Data.MSSQL
                 foreach (FieldInfo fi in m_Fields.Values)
                 {
                     names.Add(fi.Name);
-                    values.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(
-                                "[MSSQL GENERIC TABLE HANDLER]: Trying to store field {0} for {1} which is unexpectedly null",
+                                "[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<string, string>(fi.Name, fi.GetValue(row).ToString()));
+                        constraints.Add(new KeyValuePair<string, string>(fi.Name, fi.GetValue(row).ToString() ));
                     }
-                    cmd.Parameters.Add(m_database.CreateParameter(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)
@@ -289,8 +374,12 @@ namespace OpenSim.Data.MSSQL
                             constraints.Add(new KeyValuePair<string, string>(kvp.Key, kvp.Key));
                         }
                         names.Add(kvp.Key);
-                        values.Add("@" + kvp.Key);
-                        cmd.Parameters.Add(m_database.CreateParameter("@" + kvp.Key, kvp.Value));
+                        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));
                     }
 
                 }
@@ -299,15 +388,15 @@ namespace OpenSim.Data.MSSQL
                 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]);
                 }
-                query.AppendFormat("[{0}] = {1} ", names[i], values[i]);
+                query.AppendFormat("\"{0}\" = {1} ", names[i], values[i]);
                 if (constraints.Count > 0)
                 {
                     List<string> terms = new List<string>();
                     for (int j = 0; j < constraints.Count; j++)
                     {
-                        terms.Add(" [" + constraints[j].Key + "] = @" + constraints[j].Key);
+                        terms.Add(String.Format(" \"{0}\" = :{0}", constraints[j].Key));
                     }
                     string where = String.Join(" AND ", terms.ToArray());
                     query.AppendFormat(" WHERE {0} ", where);
@@ -319,7 +408,7 @@ namespace OpenSim.Data.MSSQL
                 conn.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                 {
-                    //m_log.WarnFormat("[MSSQLGenericTable]: Updating {0}", m_Realm);
+                    //m_log.WarnFormat("[PGSQLGenericTable]: Updating {0}", m_Realm);
                     return true;
                 }
                 else
@@ -327,12 +416,14 @@ namespace OpenSim.Data.MSSQL
                     // 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()) + ")");
+                    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("[MSSQLGenericTable]: Inserting into {0}", m_Realm);
+                    
+                    // m_log.WarnFormat("[PGSQLGenericTable]: Inserting into {0} sql {1}", m_Realm, cmd.CommandText);
+
                     if (conn.State != ConnectionState.Open)
                         conn.Open();
                     if (cmd.ExecuteNonQuery() > 0)
@@ -355,13 +446,17 @@ namespace OpenSim.Data.MSSQL
 
             List<string> terms = new List<string>();
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand())
+            using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
+            using (NpgsqlCommand cmd = new NpgsqlCommand())
             {
                 for (int i = 0; i < fields.Length; i++)
                 {
-                    cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i]));
-                    terms.Add("[" + fields[i] + "] = @" + fields[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());
@@ -374,11 +469,69 @@ namespace OpenSim.Data.MSSQL
 
                 if (cmd.ExecuteNonQuery() > 0)
                 {
-                    //m_log.Warn("[MSSQLGenericTable]: " + deleteCommand);
+                    //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<string> terms = new List<string>();
+
+            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();
+            }
+        }
     }
 }