1 files changed, 334 insertions, 126 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLEstateData.cs b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
index c0c6349..1faa249 100644
--- a/OpenSim/Data/MSSQL/MSSQLEstateData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
@@ -37,19 +37,28 @@ using OpenSim.Region.Framework.Interfaces;
 
 namespace OpenSim.Data.MSSQL
 {
-    public class MSSQLEstateData : IEstateDataStore
+    public class MSSQLEstateStore : IEstateDataStore
     {
         private const string _migrationStore = "EstateStore";
 
-        private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
+        private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
 
         private MSSQLManager _Database;
-
+        private string m_connectionString;
         private FieldInfo[] _Fields;
         private Dictionary<string, FieldInfo> _FieldMap = new Dictionary<string, FieldInfo>();
 
         #region Public methods
 
+        public MSSQLEstateStore()
+        {
+        }
+
+        public MSSQLEstateStore(string connectionString)
+        {
+            Initialise(connectionString);
+        }
+
         /// <summary>
         /// Initialises the estatedata class.
         /// </summary>
@@ -58,25 +67,17 @@ namespace OpenSim.Data.MSSQL
         {
             if (!string.IsNullOrEmpty(connectionString))
             {
+                m_connectionString = connectionString;
                 _Database = new MSSQLManager(connectionString);
             }
-            else
-            {
-                //TODO when can this be deleted 
-                IniFile iniFile = new IniFile("mssql_connection.ini");
-                string settingDataSource = iniFile.ParseFileReadValue("data_source");
-                string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
-                string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
-                string settingUserId = iniFile.ParseFileReadValue("user_id");
-                string settingPassword = iniFile.ParseFileReadValue("password");
-
-                _Database =
-                    new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
-                                     settingPassword);
-            }
 
             //Migration settings
-            _Database.CheckMigration(_migrationStore);
+            using (SqlConnection conn = new SqlConnection(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);
@@ -96,40 +97,48 @@ namespace OpenSim.Data.MSSQL
         /// </summary>
         /// <param name="regionID">region ID.</param>
         /// <returns></returns>
-        public EstateSettings LoadEstateSettings(UUID regionID)
+        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 (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
-
+                conn.Open();
                 using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     if (reader.Read())
                     {
                         foreach (string name in FieldList)
                         {
-                            if (_FieldMap[name].GetValue(es) is bool)
+                            FieldInfo f = _FieldMap[name];
+                            object v = reader[name];
+                            if (f.FieldType == typeof(bool))
                             {
-                                int v = Convert.ToInt32(reader[name]);
-                                if (v != 0)
-                                    _FieldMap[name].SetValue(es, true);
-                                else
-                                    _FieldMap[name].SetValue(es, false);
+                                f.SetValue(es, Convert.ToInt32(v) != 0);
                             }
-                            else if (_FieldMap[name].GetValue(es) is UUID)
+                            else if (f.FieldType == typeof(UUID))
                             {
-                                _FieldMap[name].SetValue(es, new UUID((Guid) reader[name])); // uuid);
+                                f.SetValue(es, new UUID((Guid)v)); // uuid);
                             }
-                            else
+                            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))
                             {
-                                es.EstateID = Convert.ToUInt32(reader["EstateID"].ToString());
+                                f.SetValue(es, Convert.ToSingle(v));
                             }
+                            else
+                                f.SetValue(es, v);
                         }
                     }
                     else
@@ -139,81 +148,68 @@ namespace OpenSim.Data.MSSQL
                 }
             }
 
-
-            if (insertEstate)
+            if (insertEstate && create)
             {
-                List<string> names = new List<string>(FieldList);
+                DoCreate(es);
+                LinkRegion(regionID, (int)es.EstateID);
+            }
 
-                names.Remove("EstateID");
+            LoadBanList(es);
 
-                sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray()));
+            es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
+            es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
+            es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
 
-                //_Log.Debug("[DB ESTATE]: SQL: " + sql);
-                using (SqlConnection connection = _Database.DatabaseConnection())
-                {
-                    using (SqlCommand insertCommand = connection.CreateCommand())
-                    {
-                        insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
+            //Set event
+            es.OnSave += StoreEstateSettings;
+            return es;
+        }
 
-                        foreach (string name in names)
-                        {
-                            insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
-                        }
-                        SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
-                        idParameter.Direction = ParameterDirection.Output;
-                        insertCommand.Parameters.Add(idParameter);
+        public EstateSettings CreateNewEstate()
+        {
+            EstateSettings es = new EstateSettings();
+            es.OnSave += StoreEstateSettings;
 
-                        insertCommand.ExecuteNonQuery();
+            DoCreate(es);
 
-                        es.EstateID = Convert.ToUInt32(idParameter.Value);
-                    }
-                }
+            LoadBanList(es);
 
-                using (AutoClosingSqlCommand cmd = _Database.Query("INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)"))
-                {
-                    cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
-                    cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
-                    // This will throw on dupe key
-                    try
-                    {
-                       cmd.ExecuteNonQuery();
-                    }
-                    catch (Exception e)
-                    {
-                        _Log.DebugFormat("[ESTATE DB]: Error inserting regionID and EstateID in estate_map: {0}", e);
-                    }
-                }
+            es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
+            es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
+            es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
 
-                // Munge and transfer the ban list
+            return es;
+        }
 
-                sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID);
-                using (AutoClosingSqlCommand cmd = _Database.Query(sql))
-                {
-                    cmd.Parameters.Add(_Database.CreateParameter("@UUID", regionID));
-                    try
-                    {
+        private void DoCreate(EstateSettings es)
+        {
+            List<string> names = new List<string>(FieldList);
 
-                        cmd.ExecuteNonQuery();
-                    }
-                    catch (Exception)
-                    {
-                        _Log.Debug("[ESTATE DB]: Error setting up estateban from regionban");
-                    }
-                }
+            names.Remove("EstateID");
 
-                //TODO check if this is needed??
-                es.Save();
-            }
+            string sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray()));
 
-            LoadBanList(es);
+            //_Log.Debug("[DB ESTATE]: SQL: " + sql);
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand insertCommand = new SqlCommand(sql, conn))
+            {
+                insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
 
-            es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
-            es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
-            es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
+                foreach (string name in names)
+                {
+                    insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
+                }
+                SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
+                idParameter.Direction = ParameterDirection.Output;
+                insertCommand.Parameters.Add(idParameter);
+                conn.Open();
+                insertCommand.ExecuteNonQuery();
 
-            //Set event
-            es.OnSave += StoreEstateSettings;
-            return es;
+                es.EstateID = Convert.ToUInt32(idParameter.Value);
+            }
+
+            //TODO check if this is needed??
+            es.Save();
         }
 
         /// <summary>
@@ -226,7 +222,7 @@ namespace OpenSim.Data.MSSQL
 
             names.Remove("EstateID");
 
-            string sql = string.Format("UPDATE estate_settings SET ") ; 
+            string sql = string.Format("UPDATE estate_settings SET ");
             foreach (string name in names)
             {
                 sql += name + " = @" + name + ", ";
@@ -234,7 +230,8 @@ namespace OpenSim.Data.MSSQL
             sql = sql.Remove(sql.LastIndexOf(","));
             sql += " WHERE EstateID = @EstateID";
 
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 foreach (string name in names)
                 {
@@ -242,6 +239,7 @@ namespace OpenSim.Data.MSSQL
                 }
 
                 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
+                conn.Open();
                 cmd.ExecuteNonQuery();
             }
 
@@ -266,12 +264,13 @@ namespace OpenSim.Data.MSSQL
 
             string sql = "select bannedUUID from estateban where EstateID = @EstateID";
 
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int);
                 idParameter.Value = es.EstateID;
                 cmd.Parameters.Add(idParameter);
-
+                conn.Open();
                 using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     while (reader.Read())
@@ -293,10 +292,11 @@ namespace OpenSim.Data.MSSQL
 
             string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table);
 
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
-
+                conn.Open();
                 using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     while (reader.Read())
@@ -312,57 +312,265 @@ namespace OpenSim.Data.MSSQL
         private void SaveBanList(EstateSettings es)
         {
             //Delete first
-            string sql = "delete from estateban where EstateID = @EstateID";
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
             {
-                cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
-                cmd.ExecuteNonQuery();
+                conn.Open();
+                using (SqlCommand 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();
+                    }
+                }
             }
+        }
 
-            //Insert after
-            sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )";
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+        private void SaveUUIDList(uint estateID, string table, UUID[] data)
+        {
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
             {
-                foreach (EstateBan b in es.EstateBans)
+                conn.Open();
+                using (SqlCommand cmd = conn.CreateCommand())
                 {
-                    cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
-                    cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.BannedUserID));
+                    cmd.Parameters.AddWithValue("@EstateID", (int)estateID);
+                    cmd.CommandText = string.Format("delete from {0} where EstateID = @EstateID", table);
                     cmd.ExecuteNonQuery();
-                    cmd.Parameters.Clear();
+
+                    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();
+                    }
                 }
             }
         }
 
-        private void SaveUUIDList(uint estateID, string table, UUID[] data)
+        public EstateSettings LoadEstateSettings(int estateID)
         {
-            //Delete first
-            string sql = string.Format("delete from {0} where EstateID = @EstateID", table);
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            EstateSettings es = new EstateSettings();
+            string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_settings where EstateID = @EstateID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
             {
-                cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
-                cmd.ExecuteNonQuery();
+                conn.Open();
+                using (SqlCommand cmd = new SqlCommand(sql, conn))
+                {
+                    cmd.Parameters.AddWithValue("@EstateID", (int)estateID);
+                    using (SqlDataReader 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<EstateSettings> LoadEstateSettingsAll()
+        {
+            List<EstateSettings> allEstateSettings = new List<EstateSettings>();
+
+            List<int> allEstateIds = GetEstatesAll();
+
+            foreach (int estateId in allEstateIds)
+                allEstateSettings.Add(LoadEstateSettings(estateId));
 
-            sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table);
-            using (AutoClosingSqlCommand cmd = _Database.Query(sql))
+            return allEstateSettings;
+        }
+
+        public List<int> GetEstates(string search)
+        {
+            List<int> result = new List<int>();
+            string sql = "select estateID from estate_settings where EstateName = @EstateName";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
             {
-                cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
+                conn.Open();
+                using (SqlCommand cmd = new SqlCommand(sql, conn))
+                {
+                    cmd.Parameters.AddWithValue("@EstateName", search);
+
+                    using (IDataReader reader = cmd.ExecuteReader())
+                    {
+                        while (reader.Read())
+                        {
+                            result.Add(Convert.ToInt32(reader["EstateID"]));
+                        }
+                        reader.Close();
+                    }
+                }
+            }
 
-                bool createParamOnce = true;
+            return result;
+        }
 
-                foreach (UUID uuid in data)
+        public List<int> GetEstatesAll()
+        {
+            List<int> result = new List<int>();
+            string sql = "select estateID from estate_settings";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            {
+                conn.Open();
+                using (SqlCommand cmd = new SqlCommand(sql, conn))
                 {
-                    if (createParamOnce)
+                    using (IDataReader reader = cmd.ExecuteReader())
                     {
-                        cmd.Parameters.Add(_Database.CreateParameter("@uuid", uuid));
-                        createParamOnce = false;
+                        while (reader.Read())
+                        {
+                            result.Add(Convert.ToInt32(reader["EstateID"]));
+                        }
+                        reader.Close();
                     }
-                    else
-                        cmd.Parameters["@uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
+                }
+            }
 
-                    cmd.ExecuteNonQuery();
+            return result;
+        }
+
+        public List<int> GetEstatesByOwner(UUID ownerID)
+        {
+            List<int> result = new List<int>();
+            string sql = "select estateID from estate_settings where EstateOwner = @EstateOwner";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            {
+                conn.Open();
+                using (SqlCommand cmd = new SqlCommand(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 (SqlConnection conn = new SqlConnection(m_connectionString))
+            {
+                conn.Open();
+                SqlTransaction transaction = conn.BeginTransaction();
+
+                try
+                {
+                    using (SqlCommand cmd = new SqlCommand(deleteSQL, conn))
+                    {
+                        cmd.Transaction = transaction;
+                        cmd.Parameters.AddWithValue("@RegionID", regionID.Guid);
+
+                        cmd.ExecuteNonQuery();
+                    }
+
+                    using (SqlCommand cmd = new SqlCommand(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<UUID> GetRegions(int estateID)
+        {
+            List<UUID> result = new List<UUID>();
+            string sql = "select RegionID from estate_map where EstateID = @EstateID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            {
+                conn.Open();
+                using (SqlCommand cmd = new SqlCommand(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
     }