From ee73d72f1df4773ce08c14e64d157b836671e6d9 Mon Sep 17 00:00:00 2001 From: Justin Clarke Casey Date: Mon, 9 Mar 2009 18:04:23 +0000 Subject: * Apply http://opensimulator.org/mantis/view.php?id=3280 * Some small syntax and refactoring tweaks for asset and inventory MSSQL * This means the MSSQL db plugin now requires SQL Server 2005 --- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 2 +- OpenSim/Data/MSSQL/MSSQLInventoryData.cs | 85 ++++++++++++++++---------------- 2 files changed, 44 insertions(+), 43 deletions(-) (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index edacf08..54578bc 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -258,7 +258,7 @@ namespace OpenSim.Data.MSSQL { List retList = new List(count); - using (AutoClosingSqlCommand command = database.Query("SELECT name,description,assetType,temporary,id FROM assets LIMIT @start, @count")) + using (AutoClosingSqlCommand command = database.Query("SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER() OVER (ORDER BY (some column to order by)) WHERE Row >= @Start AND Row < @Start + @Count")) { command.Parameters.Add(database.CreateParameter("start", start)); command.Parameters.Add(database.CreateParameter("count", count)); diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs index 4e7e322..348682c 100644 --- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs +++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs @@ -224,9 +224,8 @@ namespace OpenSim.Data.MSSQL /// Folder to create public void addInventoryFolder(InventoryFolderBase folder) { - string sql = - "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; - sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; + string sql = @"INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) + VALUES (@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; using (AutoClosingSqlCommand command = database.Query(sql)) @@ -256,13 +255,14 @@ namespace OpenSim.Data.MSSQL /// Folder to update public void updateInventoryFolder(InventoryFolderBase folder) { - using (AutoClosingSqlCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + - "agentID = @agentID, " + - "parentFolderID = @parentFolderID," + - "folderName = @folderName," + - "type = @type," + - "version = @version where " + - "folderID = @keyFolderID;")) + string sql = @"UPDATE inventoryfolders SET folderID = @folderID, + agentID = @agentID, + parentFolderID = @parentFolderID, + folderName = @folderName, + type = @type, + version = @version + WHERE folderID = @keyFolderID"; + using (AutoClosingSqlCommand command = database.Query(sql)) { command.Parameters.Add(database.CreateParameter("folderID", folder.ID)); command.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); @@ -288,9 +288,8 @@ namespace OpenSim.Data.MSSQL /// Folder to update public void moveInventoryFolder(InventoryFolderBase folder) { - using (IDbCommand command = database.Query("UPDATE inventoryfolders set " + - "parentFolderID = @parentFolderID where " + - "folderID = @folderID;")) + string sql = @"UPDATE inventoryfolders SET parentFolderID = @parentFolderID WHERE folderID = @folderID"; + using (IDbCommand command = database.Query(sql)) { command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); command.Parameters.Add(database.CreateParameter("@folderID", folder.ID)); @@ -402,15 +401,16 @@ namespace OpenSim.Data.MSSQL return; } - string sql = "INSERT INTO inventoryitems"; - sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]" - + ", [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions]" - + ", [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions], [inventoryGroupPermissions]" - + ", [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) VALUES "; - sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription" - + ", @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID" - + ", @inventoryBasePermissions, @inventoryEveryOnePermissions, @inventoryGroupPermissions, @salePrice, @saleType" - + ", @creationDate, @groupID, @groupOwned, @flags);"; + string sql = @"INSERT INTO inventoryitems + ([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], + [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], + [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions], [inventoryGroupPermissions], + [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) + VALUES + (@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, + @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, + @inventoryBasePermissions, @inventoryEveryOnePermissions, @inventoryGroupPermissions, @salePrice, @saleType, + @creationDate, @groupID, @groupOwned, @flags)"; using (AutoClosingSqlCommand command = database.Query(sql)) { @@ -453,26 +453,27 @@ namespace OpenSim.Data.MSSQL /// Inventory item to update public void updateInventoryItem(InventoryItemBase item) { - using (AutoClosingSqlCommand command = database.Query("UPDATE inventoryitems set inventoryID = @inventoryID, " + - "assetID = @assetID, " + - "assetType = @assetType," + - "parentFolderID = @parentFolderID," + - "avatarID = @avatarID," + - "inventoryName = @inventoryName," + - "inventoryDescription = @inventoryDescription," + - "inventoryNextPermissions = @inventoryNextPermissions," + - "inventoryCurrentPermissions = @inventoryCurrentPermissions," + - "invType = @invType," + - "creatorID = @creatorID," + - "inventoryBasePermissions = @inventoryBasePermissions," + - "inventoryEveryOnePermissions = @inventoryEveryOnePermissions," + - "salePrice = @salePrice," + - "saleType = @saleType," + - "creationDate = @creationDate," + - "groupID = @groupID," + - "groupOwned = @groupOwned," + - "flags = @flags where " + - "inventoryID = @keyInventoryID;")) + string sql = @"UPDATE inventoryitems SET inventoryID = @inventoryID, + assetID = @assetID, + assetType = @assetType, + parentFolderID = @parentFolderID, + avatarID = @avatarID, + inventoryName = @inventoryName, + inventoryDescription = @inventoryDescription, + inventoryNextPermissions = @inventoryNextPermissions, + inventoryCurrentPermissions = @inventoryCurrentPermissions, + invType = @invType, + creatorID = @creatorID, + inventoryBasePermissions = @inventoryBasePermissions, + inventoryEveryOnePermissions = @inventoryEveryOnePermissions, + salePrice = @salePrice, + saleType = @saleType, + creationDate = @creationDate, + groupID = @groupID, + groupOwned = @groupOwned, + flags = @flags + WHERE inventoryID = @keyInventoryID"; + using (AutoClosingSqlCommand command = database.Query(sql)) { command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); -- cgit v1.1