From 2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf Mon Sep 17 00:00:00 2001 From: StrawberryFride Date: Wed, 24 Feb 2010 16:42:39 +0000 Subject: 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 --- OpenSim/Data/MSSQL/MSSQLInventoryData.cs | 206 +++++++++++++++---------------- 1 file changed, 103 insertions(+), 103 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLInventoryData.cs') 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 /// private MSSQLManager database; + private string m_connectionString; #region IPlugin members @@ -66,24 +67,9 @@ namespace OpenSim.Data.MSSQL /// use mssql_connection.ini 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 /// A folder class 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 folders = new List(); - - 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 tempFolders = new List(); 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 /// Id of folder to delete 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 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 /// A list containing inventory items public List 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 items = new List(); - using (SqlDataReader reader = command.ExecuteReader()) + using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { @@ -388,11 +376,13 @@ namespace OpenSim.Data.MSSQL /// An inventory item 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 /// the item UUID 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 /// public List 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 gestureList = new List(); while (reader.Read()) @@ -656,7 +654,7 @@ namespace OpenSim.Data.MSSQL /// parent ID. /// SQL command/connection to database /// - private static List getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command) + private static List getFolderHierarchy(UUID parentID, SqlCommand command) { command.Parameters["@parentID"].Value = parentID.Guid; //.ToString(); @@ -687,7 +685,9 @@ namespace OpenSim.Data.MSSQL /// private List 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 /// /// SQLcommand. /// - private static List getInventoryFolders(AutoClosingSqlCommand command) + private static List getInventoryFolders(SqlCommand command) { - using (IDataReader reader = command.ExecuteReader()) + using (SqlDataReader reader = command.ExecuteReader()) { List items = new List(); @@ -727,7 +727,7 @@ namespace OpenSim.Data.MSSQL /// /// A MSSQL Data Reader /// A List containing inventory folders - protected static InventoryFolderBase readInventoryFolder(IDataReader reader) + protected static InventoryFolderBase readInventoryFolder(SqlDataReader reader) { try { -- cgit v1.1