MSSQL Additions for Presence Refactor branch. Most functionality tested and works, some outstanding issues around login location and border crossings on y axis.

Signed-off-by: Melanie <melanie@t-data.com>

1 files changed, 103 insertions, 103 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
index 1482184..4815700 100644
--- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
@@ -49,6 +49,7 @@ namespace OpenSim.Data.MSSQL
         /// The database manager
         /// </summary>
         private MSSQLManager database;
+        private string m_connectionString;
 
         #region IPlugin members
 
@@ -66,24 +67,9 @@ namespace OpenSim.Data.MSSQL
         /// <remarks>use mssql_connection.ini</remarks>
         public void Initialise(string connectionString)
         {
-            if (!string.IsNullOrEmpty(connectionString))
-            {
-                database = new MSSQLManager(connectionString);
-            }
-            else
-            {
-                IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
-                string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
-                string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
-                string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
-                string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
-                string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
-
-                database =
-                    new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
-                                     settingPassword);
-            }
-
+            m_connectionString = connectionString;
+            database = new MSSQLManager(connectionString);
+          
             //New migrations check of store
             database.CheckMigration(_migrationStore);
         }
@@ -169,11 +155,13 @@ namespace OpenSim.Data.MSSQL
         /// <returns>A folder class</returns>
         public InventoryFolderBase getInventoryFolder(UUID folderID)
         {
-            using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID"))
+            string sql = "SELECT * FROM inventoryfolders WHERE folderID = @folderID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("folderID", folderID));
-
-                using (IDataReader reader = command.ExecuteReader())
+                cmd.Parameters.Add(database.CreateParameter("folderID", folderID));
+                conn.Open();
+                using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     if (reader.Read())
                     {
@@ -197,18 +185,19 @@ namespace OpenSim.Data.MSSQL
             //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
 
             List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
-
-            using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID"))
+            string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("@parentID", parentID));
-
-                folders.AddRange(getInventoryFolders(command));
+                cmd.Parameters.Add(database.CreateParameter("@parentID", parentID));
+                conn.Open();
+                folders.AddRange(getInventoryFolders(cmd));
 
                 List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
 
                 foreach (InventoryFolderBase folderBase in folders)
                 {
-                    tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command));
+                    tempFolders.AddRange(getFolderHierarchy(folderBase.ID, cmd));
                 }
                 if (tempFolders.Count > 0)
                 {
@@ -233,20 +222,19 @@ namespace OpenSim.Data.MSSQL
                 folderName = folderName.Substring(0, 64);
                 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on add");
             }
-            
-            using (AutoClosingSqlCommand command = database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
-                command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
-                command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
-                command.Parameters.Add(database.CreateParameter("folderName", folderName));
-                command.Parameters.Add(database.CreateParameter("type", folder.Type));
-                command.Parameters.Add(database.CreateParameter("version", folder.Version));
-
+                cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
+                cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
+                cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
+                cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
+                cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
+                cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
+                conn.Open();
                 try
                 {
-                    //IDbCommand result = database.Query(sql, param);
-                    command.ExecuteNonQuery();
+                    cmd.ExecuteNonQuery();
                 }
                 catch (Exception e)
                 {
@@ -275,20 +263,20 @@ namespace OpenSim.Data.MSSQL
                 folderName = folderName.Substring(0, 64);
                 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on update");
             }
-            
-            using (AutoClosingSqlCommand command = database.Query(sql))
-            {
-                command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
-                command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
-                command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
-                command.Parameters.Add(database.CreateParameter("folderName", folderName));
-                command.Parameters.Add(database.CreateParameter("type", folder.Type));
-                command.Parameters.Add(database.CreateParameter("version", folder.Version));
-                command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
-                
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            {
+                cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
+                cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
+                cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
+                cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
+                cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
+                cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
+                cmd.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
+                conn.Open();
                 try
                 {
-                    command.ExecuteNonQuery();
+                    cmd.ExecuteNonQuery();
                 }
                 catch (Exception e)
                 {
@@ -304,14 +292,15 @@ namespace OpenSim.Data.MSSQL
         public void moveInventoryFolder(InventoryFolderBase folder)
         {
             string sql = @"UPDATE inventoryfolders SET parentFolderID = @parentFolderID WHERE folderID = @folderID";
-            using (IDbCommand command = database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
-                command.Parameters.Add(database.CreateParameter("@folderID", folder.ID));
-
+                cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
+                cmd.Parameters.Add(database.CreateParameter("@folderID", folder.ID));
+                conn.Open();
                 try
                 {
-                    command.ExecuteNonQuery();
+                    cmd.ExecuteNonQuery();
                 }
                 catch (Exception e)
                 {
@@ -326,30 +315,27 @@ namespace OpenSim.Data.MSSQL
         /// <param name="folderID">Id of folder to delete</param>
         public void deleteInventoryFolder(UUID folderID)
         {
-            using (SqlConnection connection = database.DatabaseConnection())
+            string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
+
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 List<InventoryFolderBase> subFolders;
-                using (SqlCommand command = new SqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID", connection))
-                {
-                    command.Parameters.Add(database.CreateParameter("@parentID", UUID.Zero));
-
-                    AutoClosingSqlCommand autoCommand = new AutoClosingSqlCommand(command);
-
-                    subFolders = getFolderHierarchy(folderID, autoCommand);
-                }
+                cmd.Parameters.Add(database.CreateParameter("@parentID", UUID.Zero));
+                conn.Open();
+                subFolders = getFolderHierarchy(folderID, cmd);
+                
 
                 //Delete all sub-folders
                 foreach (InventoryFolderBase f in subFolders)
                 {
-                    DeleteOneFolder(f.ID, connection);
-                    DeleteItemsInFolder(f.ID, connection);
+                    DeleteOneFolder(f.ID, conn);
+                    DeleteItemsInFolder(f.ID, conn);
                 }
 
                 //Delete the actual row
-                DeleteOneFolder(folderID, connection);
-                DeleteItemsInFolder(folderID, connection);
-
-                connection.Close();
+                DeleteOneFolder(folderID, conn);
+                DeleteItemsInFolder(folderID, conn);
             }
         }
 
@@ -364,13 +350,15 @@ namespace OpenSim.Data.MSSQL
         /// <returns>A list containing inventory items</returns>
         public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
         {
-            using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID"))
+            string sql = "SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
-
+                cmd.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
+                conn.Open();
                 List<InventoryItemBase> items = new List<InventoryItemBase>();
 
-                using (SqlDataReader reader = command.ExecuteReader())
+                using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     while (reader.Read())
                     {
@@ -388,11 +376,13 @@ namespace OpenSim.Data.MSSQL
         /// <returns>An inventory item</returns>
         public InventoryItemBase getInventoryItem(UUID itemID)
         {
-            using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID"))
+            string sql = "SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                result.Parameters.Add(database.CreateParameter("inventoryID", itemID));
-
-                using (IDataReader reader = result.ExecuteReader())
+                cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
+                conn.Open();
+                using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     if (reader.Read())
                     {
@@ -441,8 +431,9 @@ namespace OpenSim.Data.MSSQL
                 itemDesc = item.Description.Substring(0, 128);
                 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters");
             }
-            
-            using (AutoClosingSqlCommand command = database.Query(sql))
+
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand command = new SqlCommand(sql, conn))
             {
                 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
                 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
@@ -464,7 +455,7 @@ namespace OpenSim.Data.MSSQL
                 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
                 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
                 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
-
+                conn.Open();
                 try
                 {
                     command.ExecuteNonQuery();
@@ -476,9 +467,11 @@ namespace OpenSim.Data.MSSQL
             }
 
             sql = "UPDATE inventoryfolders SET version = version + 1 WHERE folderID = @folderID";
-            using (AutoClosingSqlCommand command = database.Query(sql))
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand command = new SqlCommand(sql, conn))
             {
                 command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString()));
+                conn.Open();
                 try
                 {
                     command.ExecuteNonQuery();
@@ -530,8 +523,9 @@ namespace OpenSim.Data.MSSQL
                 itemDesc = item.Description.Substring(0, 128);
                 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters on update");
             }
-            
-            using (AutoClosingSqlCommand command = database.Query(sql))
+
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand command = new SqlCommand(sql, conn))
             {
                 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
                 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
@@ -552,8 +546,8 @@ namespace OpenSim.Data.MSSQL
                 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
                 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
                 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
-                command.Parameters.Add(database.CreateParameter("@keyInventoryID", item.ID));
-
+                command.Parameters.Add(database.CreateParameter("keyInventoryID", item.ID));
+                conn.Open();
                 try
                 {
                     command.ExecuteNonQuery();
@@ -573,13 +567,15 @@ namespace OpenSim.Data.MSSQL
         /// <param name="itemID">the item UUID</param>
         public void deleteInventoryItem(UUID itemID)
         {
-            using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID"))
+            string sql = "DELETE FROM inventoryitems WHERE inventoryID=@inventoryID";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
-                command.Parameters.Add(database.CreateParameter("inventoryID", itemID));
+                cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
                 try
                 {
-
-                    command.ExecuteNonQuery();
+                    conn.Open();
+                    cmd.ExecuteNonQuery();
                 }
                 catch (Exception e)
                 {
@@ -607,12 +603,14 @@ namespace OpenSim.Data.MSSQL
         /// </returns>
         public List<InventoryItemBase> fetchActiveGestures(UUID avatarID)
         {
-            using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1"))
-            {
-                command.Parameters.Add(database.CreateParameter("uuid", avatarID));
-                command.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture));
-
-                using (IDataReader reader = command.ExecuteReader())
+            string sql = "SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand cmd = new SqlCommand(sql, conn))
+            {
+                cmd.Parameters.Add(database.CreateParameter("uuid", avatarID));
+                cmd.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture));
+                conn.Open();
+                using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     List<InventoryItemBase> gestureList = new List<InventoryItemBase>();
                     while (reader.Read())
@@ -656,7 +654,7 @@ namespace OpenSim.Data.MSSQL
         /// <param name="parentID">parent ID.</param>
         /// <param name="command">SQL command/connection to database</param>
         /// <returns></returns>
-        private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command)
+        private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, SqlCommand command)
         {
             command.Parameters["@parentID"].Value = parentID.Guid; //.ToString();
 
@@ -687,7 +685,9 @@ namespace OpenSim.Data.MSSQL
         /// <returns></returns>
         private List<InventoryFolderBase> getInventoryFolders(UUID parentID, UUID user)
         {
-            using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid"))
+            string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid";
+            using (SqlConnection conn = new SqlConnection(m_connectionString))
+            using (SqlCommand command = new SqlCommand(sql, conn))
             {
                 if (user == UUID.Zero)
                 {
@@ -698,7 +698,7 @@ namespace OpenSim.Data.MSSQL
                     command.Parameters.Add(database.CreateParameter("uuid", user));
                 }
                 command.Parameters.Add(database.CreateParameter("parentID", parentID));
-
+                conn.Open();
                 return getInventoryFolders(command);
             }
         }
@@ -708,9 +708,9 @@ namespace OpenSim.Data.MSSQL
         /// </summary>
         /// <param name="command">SQLcommand.</param>
         /// <returns></returns>
-        private static List<InventoryFolderBase> getInventoryFolders(AutoClosingSqlCommand command)
+        private static List<InventoryFolderBase> getInventoryFolders(SqlCommand command)
         {
-            using (IDataReader reader = command.ExecuteReader())
+            using (SqlDataReader reader = command.ExecuteReader())
             {
 
                 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
@@ -727,7 +727,7 @@ namespace OpenSim.Data.MSSQL
         /// </summary>
         /// <param name="reader">A MSSQL Data Reader</param>
         /// <returns>A List containing inventory folders</returns>
-        protected static InventoryFolderBase readInventoryFolder(IDataReader reader)
+        protected static InventoryFolderBase readInventoryFolder(SqlDataReader reader)
         {
             try
             {