From f7bf3facff458325a59483d00d3743eba32679f1 Mon Sep 17 00:00:00 2001 From: AlexRa Date: Thu, 29 Apr 2010 12:26:09 +0300 Subject: MSSQLAssetData: fixed some weirdness Fixed unfinished SQL in FetchAssetMetadataSet, fixed SQL in UpdateAsset (must not modify ID). NOT tested! But apparently shouldn't work worse than the previous version, esp. the FetchMetadata thing. --- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLAssetData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index 8475b22..5d1e170 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -210,7 +210,7 @@ namespace OpenSim.Data.MSSQL /// the asset private void UpdateAsset(AssetBase asset) { - string sql = @"UPDATE assets set id = @id, name = @name, description = @description, assetType = @assetType, + string sql = @"UPDATE assets set name = @name, description = @description, assetType = @assetType, local = @local, temporary = @temporary, data = @data WHERE id = @keyId;"; @@ -231,14 +231,13 @@ namespace OpenSim.Data.MSSQL using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand command = new SqlCommand(sql, conn)) { - command.Parameters.Add(m_database.CreateParameter("id", asset.FullID)); + command.Parameters.Add(m_database.CreateParameter("keyId", asset.FullID)); command.Parameters.Add(m_database.CreateParameter("name", assetName)); command.Parameters.Add(m_database.CreateParameter("description", assetDescription)); command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type)); command.Parameters.Add(m_database.CreateParameter("local", asset.Local)); command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); - command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID)); conn.Open(); try { @@ -295,15 +294,21 @@ namespace OpenSim.Data.MSSQL public override List FetchAssetMetadataSet(int start, int count) { List retList = new List(count); - string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER() - OVER (ORDER BY (some column to order by)) - WHERE Row >= @Start AND Row < @Start + @Count"; + string sql = @"WITH OrderedAssets AS + ( + SELECT id, name, description, assetType, temporary, + Row = ROW_NUMBER() OVER (ORDER BY id) + FROM assets + ) + SELECT * + FROM OrderedAssets + WHERE RowNumber BETWEEN @start AND @stop;"; using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(m_database.CreateParameter("start", start)); - cmd.Parameters.Add(m_database.CreateParameter("count", count)); + cmd.Parameters.Add(m_database.CreateParameter("stop", start + count - 1)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { -- cgit v1.1 From 49f4cc424624c314066d154de54441344b99916c Mon Sep 17 00:00:00 2001 From: AlexRa Date: Wed, 19 May 2010 21:38:29 +0300 Subject: MSSQLAssetData updated to support [CreatorID], [asset_flags] --- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 16 +++++++++++----- 1 file changed, 11 insertions(+), 5 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLAssetData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index 5d1e170..ec9d4f6 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -121,15 +121,16 @@ namespace OpenSim.Data.MSSQL if (reader.Read()) { AssetBase asset = new AssetBase( - new UUID((Guid)reader["id"]), + DBGuid.FromDB(reader["id"]), (string)reader["name"], Convert.ToSByte(reader["assetType"]), - String.Empty + reader["creatorid"].ToString() ); // Region Main asset.Description = (string)reader["description"]; asset.Local = Convert.ToBoolean(reader["local"]); asset.Temporary = Convert.ToBoolean(reader["temporary"]); + asset.Flags = (AssetFlags)(Convert.ToInt32(reader["asset_flags"])); asset.Data = (byte[])reader["data"]; return asset; } @@ -160,10 +161,10 @@ namespace OpenSim.Data.MSSQL string sql = @"INSERT INTO assets ([id], [name], [description], [assetType], [local], - [temporary], [create_time], [access_time], [data]) + [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data]) VALUES (@id, @name, @description, @assetType, @local, - @temporary, @create_time, @access_time, @data)"; + @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)"; string assetName = asset.Name; if (asset.Name.Length > 64) @@ -191,6 +192,8 @@ namespace OpenSim.Data.MSSQL command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); command.Parameters.Add(m_database.CreateParameter("access_time", now)); command.Parameters.Add(m_database.CreateParameter("create_time", now)); + command.Parameters.Add(m_database.CreateParameter("asset_flags", (int)asset.Flags)); + command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID)); command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); conn.Open(); try @@ -212,6 +215,7 @@ namespace OpenSim.Data.MSSQL { string sql = @"UPDATE assets set name = @name, description = @description, assetType = @assetType, local = @local, temporary = @temporary, data = @data + , creatorid = @creatorid WHERE id = @keyId;"; string assetName = asset.Name; @@ -238,6 +242,7 @@ namespace OpenSim.Data.MSSQL command.Parameters.Add(m_database.CreateParameter("local", asset.Local)); command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); + command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID)); conn.Open(); try { @@ -296,7 +301,7 @@ namespace OpenSim.Data.MSSQL List retList = new List(count); string sql = @"WITH OrderedAssets AS ( - SELECT id, name, description, assetType, temporary, + SELECT id, name, description, assetType, temporary, creatorid, Row = ROW_NUMBER() OVER (ORDER BY id) FROM assets ) @@ -320,6 +325,7 @@ namespace OpenSim.Data.MSSQL metadata.Description = (string)reader["description"]; metadata.Type = Convert.ToSByte(reader["assetType"]); metadata.Temporary = Convert.ToBoolean(reader["temporary"]); + metadata.CreatorID = (string)reader["creatorid"]; } } } -- cgit v1.1 From 9976cb93ce351f45dea77e3389e0159b866757ae Mon Sep 17 00:00:00 2001 From: AlexRa Date: Sun, 23 May 2010 11:26:53 +0300 Subject: Further corrections to MS SQL stores (now passes all tests) Besides, AssetData is slightly optimized to StoreAsset in one request ("IF EXISTS() UPDATE ... ELSE INSERT ...") The main change in the MS SQL Inventory implem. is that it now return empty list (or whatever) when called with UUID.Zero, which is consistent with how the code for other DBs work. I did no changes at all in XInventory, as there is no test set for them. --- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 87 +++++++----------------------------- 1 file changed, 17 insertions(+), 70 deletions(-) (limited to 'OpenSim/Data/MSSQL/MSSQLAssetData.cs') diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index ec9d4f6..c7488d8 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -145,26 +145,19 @@ namespace OpenSim.Data.MSSQL /// the asset override public void StoreAsset(AssetBase asset) { - if (ExistsAsset(asset.FullID)) - UpdateAsset(asset); - else - InsertAsset(asset); - } - - - private void InsertAsset(AssetBase asset) - { - if (ExistsAsset(asset.FullID)) - { - return; - } - - string sql = @"INSERT INTO assets - ([id], [name], [description], [assetType], [local], - [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data]) - VALUES - (@id, @name, @description, @assetType, @local, - @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)"; + + string sql = + @"IF EXISTS(SELECT * FROM assets WHERE id=@id) + UPDATE assets set name = @name, description = @description, assetType = @assetType, + local = @local, temporary = @temporary, creatorid = @creatorid, data = @data + WHERE id=@id + ELSE + INSERT INTO assets + ([id], [name], [description], [assetType], [local], + [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data]) + VALUES + (@id, @name, @description, @assetType, @local, + @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)"; string assetName = asset.Name; if (asset.Name.Length > 64) @@ -202,58 +195,11 @@ namespace OpenSim.Data.MSSQL } catch(Exception e) { - m_log.Error("[ASSET DB]: Error inserting item :" + e.Message); + m_log.Error("[ASSET DB]: Error storing item :" + e.Message); } } } - /// - /// Update asset in m_database - /// - /// the asset - private void UpdateAsset(AssetBase asset) - { - string sql = @"UPDATE assets set name = @name, description = @description, assetType = @assetType, - local = @local, temporary = @temporary, data = @data - , creatorid = @creatorid - WHERE id = @keyId;"; - - string assetName = asset.Name; - if (asset.Name.Length > 64) - { - assetName = asset.Name.Substring(0, 64); - m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on update"); - } - - string assetDescription = asset.Description; - if (asset.Description.Length > 64) - { - assetDescription = asset.Description.Substring(0, 64); - m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update"); - } - - using (SqlConnection conn = new SqlConnection(m_connectionString)) - using (SqlCommand command = new SqlCommand(sql, conn)) - { - command.Parameters.Add(m_database.CreateParameter("keyId", asset.FullID)); - command.Parameters.Add(m_database.CreateParameter("name", assetName)); - command.Parameters.Add(m_database.CreateParameter("description", assetDescription)); - command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type)); - command.Parameters.Add(m_database.CreateParameter("local", asset.Local)); - command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); - command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); - command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID)); - conn.Open(); - try - { - command.ExecuteNonQuery(); - } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - } - } // Commented out since currently unused - this probably should be called in GetAsset() // private void UpdateAccessTime(AssetBase asset) @@ -302,7 +248,7 @@ namespace OpenSim.Data.MSSQL string sql = @"WITH OrderedAssets AS ( SELECT id, name, description, assetType, temporary, creatorid, - Row = ROW_NUMBER() OVER (ORDER BY id) + RowNumber = ROW_NUMBER() OVER (ORDER BY id) FROM assets ) SELECT * @@ -320,12 +266,13 @@ namespace OpenSim.Data.MSSQL while (reader.Read()) { AssetMetadata metadata = new AssetMetadata(); - metadata.FullID = new UUID((Guid)reader["id"]); + metadata.FullID = DBGuid.FromDB(reader["id"]); metadata.Name = (string)reader["name"]; metadata.Description = (string)reader["description"]; metadata.Type = Convert.ToSByte(reader["assetType"]); metadata.Temporary = Convert.ToBoolean(reader["temporary"]); metadata.CreatorID = (string)reader["creatorid"]; + retList.Add(metadata); } } } -- cgit v1.1