Initial update to OpenSim 0.8.2.1 source code.

1 files changed, 119 insertions, 74 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/PGSQL/PGSQLRegionData.cs
index 0d89706..b3076f0 100644
--- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLRegionData.cs
@@ -28,7 +28,6 @@
 using System;
 using System.Collections.Generic;
 using System.Data;
-using System.Data.SqlClient;
 using System.Drawing;
 using System.IO;
 using System.Reflection;
@@ -38,45 +37,80 @@ using OpenSim.Framework;
 using OpenSim.Region.Framework.Interfaces;
 using OpenSim.Region.Framework.Scenes;
 using RegionFlags = OpenSim.Framework.RegionFlags;
+using Npgsql;
 
-namespace OpenSim.Data.MSSQL
+namespace OpenSim.Data.PGSQL
 {
     /// <summary>
-    /// A MSSQL Interface for the Region Server.
+    /// A PGSQL Interface for the Region Server.
     /// </summary>
-    public class MSSQLRegionData : IRegionData
+    public class PGSQLRegionData : IRegionData
     {
         private string m_Realm;
         private List<string> m_ColumnNames = null;
         private string m_ConnectionString;
-        private MSSQLManager m_database;
+        private PGSQLManager m_database;
         private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
 
-        public MSSQLRegionData(string connectionString, string realm) 
+        protected Dictionary<string, string> m_FieldTypes = new Dictionary<string, string>();
+
+        protected virtual Assembly Assembly
+        {
+            get { return GetType().Assembly; }
+        }
+
+        public PGSQLRegionData(string connectionString, string realm) 
         {
             m_Realm = realm;
             m_ConnectionString = connectionString;
-            m_database = new MSSQLManager(connectionString);
+            m_database = new PGSQLManager(connectionString);
 
-            using (SqlConnection conn = new SqlConnection(m_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, 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]);
+                    }
+                }
+            }
+        }
+
         public List<RegionData> Get(string regionName, UUID scopeID)
         {
-            string sql = "select * from ["+m_Realm+"] where regionName like @regionName";
+            string sql = "select * from "+m_Realm+" where lower(\"regionName\") like lower(:regionName) ";
             if (scopeID != UUID.Zero)
-                sql += " and ScopeID = @scopeID";
-            sql += " order by regionName";
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+                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));
-                cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
+                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);
             }
@@ -84,16 +118,17 @@ namespace OpenSim.Data.MSSQL
 
         public RegionData Get(int posX, int posY, UUID scopeID)
         {
-            string sql = "select * from ["+m_Realm+"] where locX = @posX and locY = @posY";
+            string sql = "select * from "+m_Realm+" where \"locX\" = :posX and \"locY\" = :posY";
             if (scopeID != UUID.Zero)
-                sql += " and ScopeID = @scopeID";
+                sql += " and \"ScopeID\" = :scopeID";
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
+            using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
             {
-                cmd.Parameters.Add(m_database.CreateParameter("@posX", posX.ToString()));
-                cmd.Parameters.Add(m_database.CreateParameter("@posY", posY.ToString()));
-                cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
+                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<RegionData> ret = RunCommand(cmd);
                 if (ret.Count == 0)
@@ -105,14 +140,15 @@ namespace OpenSim.Data.MSSQL
 
         public RegionData Get(UUID regionID, UUID scopeID)
         {
-            string sql = "select * from ["+m_Realm+"] where uuid = @regionID";
+            string sql = "select * from "+m_Realm+" where uuid = :regionID";
             if (scopeID != UUID.Zero)
-                sql += " and ScopeID = @scopeID";
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+                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));
-                cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
+                cmd.Parameters.Add(m_database.CreateParameter("regionID", regionID));
+                if (scopeID != UUID.Zero) 
+                    cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
                 conn.Open();
                 List<RegionData> ret = RunCommand(cmd);
                 if (ret.Count == 0)
@@ -124,28 +160,28 @@ namespace OpenSim.Data.MSSQL
 
         public List<RegionData> 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";
+            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";
+                sql += " and \"ScopeID\" = :scopeID";
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            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));
+                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<RegionData> RunCommand(SqlCommand cmd)
+        public List<RegionData> RunCommand(NpgsqlCommand cmd)
         {
             List<RegionData> retList = new List<RegionData>();
 
-            SqlDataReader result = cmd.ExecuteReader();
+            NpgsqlDataReader result = cmd.ExecuteReader();
 
             while (result.Read())
             {
@@ -217,43 +253,46 @@ namespace OpenSim.Data.MSSQL
 
             string[] fields = new List<string>(data.Data.Keys).ToArray();
 
-            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 update = "update [" + m_Realm + "] set locX=@posX, locY=@posY, sizeX=@sizeX, sizeY=@sizeY ";
+                string update = "update " + m_Realm + " set \"locX\"=:posX, \"locY\"=:posY, \"sizeX\"=:sizeX, \"sizeY\"=:sizeY ";
                 
                 foreach (string field in fields)
                 {
 
                     update += ", ";
-                    update += "[" + field + "] = @" + field;
+                    update += " \"" + field + "\" = :" + field;
 
-                    cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[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";
+                update += " where uuid = :regionID";
 
                 if (data.ScopeID != UUID.Zero)
-                    update += " and ScopeID = @scopeID";
+                    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));
+                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) + ")";
+                        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;
 
@@ -266,13 +305,13 @@ namespace OpenSim.Data.MSSQL
                         }
                         catch (Exception ex)
                         {
-                            m_log.Warn("[MSSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert);
+                            m_log.Warn("[PGSQL Grid]: Error inserting into Regions table: " + ex.Message + ", INSERT sql: " + insert);
                         }
                     }
                 }
                 catch (Exception ex)
                 {
-                    m_log.Warn("[MSSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update);
+                    m_log.Warn("[PGSQL Grid]: Error updating Regions table: " + ex.Message + ", UPDATE sql: " + update);
                 }
             }
 
@@ -281,13 +320,14 @@ namespace OpenSim.Data.MSSQL
 
         public bool SetDataItem(UUID regionID, string item, string value)
         {
-            string sql = "update [" + m_Realm +
-                    "] set [" + item + "] = @" + item + " where uuid = @UUID";
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            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));
+                cmd.Parameters.Add(m_database.CreateParameter("" + item, value));
+                cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID));
                 conn.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                     return true;
@@ -297,12 +337,12 @@ namespace OpenSim.Data.MSSQL
 
         public bool Delete(UUID regionID)
         {
-            string sql = "delete from [" + m_Realm +
-                    "] where uuid = @UUID";
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            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));
+                cmd.Parameters.Add(m_database.CreateParameter("UUID", regionID));
                 conn.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                     return true;
@@ -315,6 +355,11 @@ namespace OpenSim.Data.MSSQL
             return Get((int)RegionFlags.DefaultRegion, scopeID);
         }
 
+        public List<RegionData> GetDefaultHypergridRegions(UUID scopeID)
+        {
+            return Get((int)RegionFlags.DefaultHGRegion, scopeID);
+        }
+
         public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
         {
             List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
@@ -331,14 +376,14 @@ namespace OpenSim.Data.MSSQL
 
         private List<RegionData> 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";
+                sql += " AND \"ScopeID\" = :scopeID";
 
-            using (SqlConnection conn = new SqlConnection(m_ConnectionString))
-            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
+            using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
             {
-                cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
+                cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
                 conn.Open();
                 return RunCommand(cmd);
             }