From aba396cb329a359a0ff9d5e6596912079bfb3584 Mon Sep 17 00:00:00 2001 From: Charles Krinke Date: Wed, 11 Jun 2008 13:57:32 +0000 Subject: Mantis#1528. Thank you kindly, Boscata for: MSSQL Avatar appearance solved. Appearance functions and modified table. --- OpenSim/Data/MSSQL/MSSQLUserData.cs | 138 +++++++++++++++++++++- OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql | 59 ++++----- 2 files changed, 168 insertions(+), 29 deletions(-) (limited to 'OpenSim/Data/MSSQL') diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs index aa8c2ed..8714e2b 100644 --- a/OpenSim/Data/MSSQL/MSSQLUserData.cs +++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs @@ -749,11 +749,147 @@ namespace OpenSim.Data.MSSQL /// TODO: stubs for now to get us to a compiling state gently override public AvatarAppearance GetUserAppearance(LLUUID user) { - return new AvatarAppearance(); +// return new AvatarAppearance(); + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["@UUID"] = user.ToString(); + + IDbCommand result = + database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID", param); + IDataReader reader = result.ExecuteReader(); + + AvatarAppearance item = null; + if (reader.Read()) + item = readUserAppearance(reader); + + reader.Close(); + result.Dispose(); + + return item; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + return null; + } + + /// + /// Reads a one item from an SQL result + /// + /// The SQL Result + /// the item read + private static AvatarAppearance readUserAppearance(IDataReader reader) + { + try + { + AvatarAppearance appearance = new AvatarAppearance(); + + appearance.Owner = new LLUUID((string)reader["owner"]); + appearance.Serial = Convert.ToInt32(reader["serial"]); + appearance.VisualParams = (byte[])reader["visual_params"]; + appearance.Texture = new LLObject.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length); + appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]); + appearance.BodyItem = new LLUUID((string)reader["body_item"]); + appearance.BodyAsset = new LLUUID((string)reader["body_asset"]); + appearance.SkinItem = new LLUUID((string)reader["skin_item"]); + appearance.SkinAsset = new LLUUID((string)reader["skin_asset"]); + appearance.HairItem = new LLUUID((string)reader["hair_item"]); + appearance.HairAsset = new LLUUID((string)reader["hair_asset"]); + appearance.EyesItem = new LLUUID((string)reader["eyes_item"]); + appearance.EyesAsset = new LLUUID((string)reader["eyes_asset"]); + appearance.ShirtItem = new LLUUID((string)reader["shirt_item"]); + appearance.ShirtAsset = new LLUUID((string)reader["shirt_asset"]); + appearance.PantsItem = new LLUUID((string)reader["pants_item"]); + appearance.PantsAsset = new LLUUID((string)reader["pants_asset"]); + appearance.ShoesItem = new LLUUID((string)reader["shoes_item"]); + appearance.ShoesAsset = new LLUUID((string)reader["shoes_asset"]); + appearance.SocksItem = new LLUUID((string)reader["socks_item"]); + appearance.SocksAsset = new LLUUID((string)reader["socks_asset"]); + appearance.JacketItem = new LLUUID((string)reader["jacket_item"]); + appearance.JacketAsset = new LLUUID((string)reader["jacket_asset"]); + appearance.GlovesItem = new LLUUID((string)reader["gloves_item"]); + appearance.GlovesAsset = new LLUUID((string)reader["gloves_asset"]); + appearance.UnderShirtItem = new LLUUID((string)reader["undershirt_item"]); + appearance.UnderShirtAsset = new LLUUID((string)reader["undershirt_asset"]); + appearance.UnderPantsItem = new LLUUID((string)reader["underpants_item"]); + appearance.UnderPantsAsset = new LLUUID((string)reader["underpants_asset"]); + appearance.SkirtItem = new LLUUID((string)reader["skirt_item"]); + appearance.SkirtAsset = new LLUUID((string)reader["skirt_asset"]); + + return appearance; + } + catch (SqlException e) + { + m_log.Error(e.ToString()); + } + + return null; } override public void UpdateUserAppearance(LLUUID user, AvatarAppearance appearance) { + string sql = String.Empty; + sql += "DELETE FROM avatarappearance WHERE owner=@owner "; + sql += "INSERT INTO avatarappearance "; + sql += "(owner, serial, visual_params, texture, avatar_height, "; + sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, "; + sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, "; + sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, "; + sql += "skirt_item, skirt_asset) values ("; + sql += "@owner, @serial, @visual_params, @texture, @avatar_height, "; + sql += "@body_item, @body_asset, @skin_item, @skin_asset, @hair_item, @hair_asset, @eyes_item, @eyes_asset, "; + sql += "@shirt_item, @shirt_asset, @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, @socks_asset, "; + sql += "@jacket_item, @jacket_asset, @gloves_item, @gloves_asset, @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, "; + sql += "@skirt_item, @skirt_asset)"; + + SqlCommand cmd = new SqlCommand(sql, database.getConnection()); + cmd.Parameters.AddWithValue("@owner", appearance.Owner.ToString()); + cmd.Parameters.AddWithValue("@serial", appearance.Serial); + cmd.Parameters.AddWithValue("@visual_params", appearance.VisualParams); + cmd.Parameters.AddWithValue("@texture", appearance.Texture.ToBytes()); + cmd.Parameters.AddWithValue("@avatar_height", appearance.AvatarHeight); + cmd.Parameters.AddWithValue("@body_item", appearance.BodyItem.ToString()); + cmd.Parameters.AddWithValue("@body_asset", appearance.BodyAsset.ToString()); + cmd.Parameters.AddWithValue("@skin_item", appearance.SkinItem.ToString()); + cmd.Parameters.AddWithValue("@skin_asset", appearance.SkinAsset.ToString()); + cmd.Parameters.AddWithValue("@hair_item", appearance.HairItem.ToString()); + cmd.Parameters.AddWithValue("@hair_asset", appearance.HairAsset.ToString()); + cmd.Parameters.AddWithValue("@eyes_item", appearance.EyesItem.ToString()); + cmd.Parameters.AddWithValue("@eyes_asset", appearance.EyesAsset.ToString()); + cmd.Parameters.AddWithValue("@shirt_item", appearance.ShirtItem.ToString()); + cmd.Parameters.AddWithValue("@shirt_asset", appearance.ShirtAsset.ToString()); + cmd.Parameters.AddWithValue("@pants_item", appearance.PantsItem.ToString()); + cmd.Parameters.AddWithValue("@pants_asset", appearance.PantsAsset.ToString()); + cmd.Parameters.AddWithValue("@shoes_item", appearance.ShoesItem.ToString()); + cmd.Parameters.AddWithValue("@shoes_asset", appearance.ShoesAsset.ToString()); + cmd.Parameters.AddWithValue("@socks_item", appearance.SocksItem.ToString()); + cmd.Parameters.AddWithValue("@socks_asset", appearance.SocksAsset.ToString()); + cmd.Parameters.AddWithValue("@jacket_item", appearance.JacketItem.ToString()); + cmd.Parameters.AddWithValue("@jacket_asset", appearance.JacketAsset.ToString()); + cmd.Parameters.AddWithValue("@gloves_item", appearance.GlovesItem.ToString()); + cmd.Parameters.AddWithValue("@gloves_asset", appearance.GlovesAsset.ToString()); + cmd.Parameters.AddWithValue("@undershirt_item", appearance.UnderShirtItem.ToString()); + cmd.Parameters.AddWithValue("@undershirt_asset", appearance.UnderShirtAsset.ToString()); + cmd.Parameters.AddWithValue("@underpants_item", appearance.UnderPantsItem.ToString()); + cmd.Parameters.AddWithValue("@underpants_asset", appearance.UnderPantsAsset.ToString()); + cmd.Parameters.AddWithValue("@skirt_item", appearance.SkirtItem.ToString()); + cmd.Parameters.AddWithValue("@skirt_asset", appearance.SkirtAsset.ToString()); + + try + { + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } return; } diff --git a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql index f396005..1639f56 100644 --- a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql +++ b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql @@ -7,37 +7,40 @@ SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON CREATE TABLE [avatarappearance] ( - [UUID] uniqueidentifier NOT NULL, + [Owner] [varchar](36) NOT NULL, [Serial] int NOT NULL, - [WearableItem0] uniqueidentifier NOT NULL, - [WearableAsset0] uniqueidentifier NOT NULL, - [WearableItem1] uniqueidentifier NOT NULL, - [WearableAsset1] uniqueidentifier NOT NULL, - [WearableItem2] uniqueidentifier NOT NULL, - [WearableAsset2] uniqueidentifier NOT NULL, - [WearableItem3] uniqueidentifier NOT NULL, - [WearableAsset3] uniqueidentifier NOT NULL, - [WearableItem4] uniqueidentifier NOT NULL, - [WearableAsset4] uniqueidentifier NOT NULL, - [WearableItem5] uniqueidentifier NOT NULL, - [WearableAsset5] uniqueidentifier NOT NULL, - [WearableItem6] uniqueidentifier NOT NULL, - [WearableAsset6] uniqueidentifier NOT NULL, - [WearableItem7] uniqueidentifier NOT NULL, - [WearableAsset7] uniqueidentifier NOT NULL, - [WearableItem8] uniqueidentifier NOT NULL, - [WearableAsset8] uniqueidentifier NOT NULL, - [WearableItem9] uniqueidentifier NOT NULL, - [WearableAsset9] uniqueidentifier NOT NULL, - [WearableItem10] uniqueidentifier NOT NULL, - [WearableAsset10] uniqueidentifier NOT NULL, - [WearableItem11] uniqueidentifier NOT NULL, - [WearableAsset11] uniqueidentifier NOT NULL, - [WearableItem12] uniqueidentifier NOT NULL, - [WearableAsset12] uniqueidentifier NOT NULL + [Visual_Params] [image] NOT NULL, + [Texture] [image] NOT NULL, + [Avatar_Height] float NOT NULL, + [Body_Item] [varchar](36) NOT NULL, + [Body_Asset] [varchar](36) NOT NULL, + [Skin_Item] [varchar](36) NOT NULL, + [Skin_Asset] [varchar](36) NOT NULL, + [Hair_Item] [varchar](36) NOT NULL, + [Hair_Asset] [varchar](36) NOT NULL, + [Eyes_Item] [varchar](36) NOT NULL, + [Eyes_Asset] [varchar](36) NOT NULL, + [Shirt_Item] [varchar](36) NOT NULL, + [Shirt_Asset] [varchar](36) NOT NULL, + [Pants_Item] [varchar](36) NOT NULL, + [Pants_Asset] [varchar](36) NOT NULL, + [Shoes_Item] [varchar](36) NOT NULL, + [Shoes_Asset] [varchar](36) NOT NULL, + [Socks_Item] [varchar](36) NOT NULL, + [Socks_Asset] [varchar](36) NOT NULL, + [Jacket_Item] [varchar](36) NOT NULL, + [Jacket_Asset] [varchar](36) NOT NULL, + [Gloves_Item] [varchar](36) NOT NULL, + [Gloves_Asset] [varchar](36) NOT NULL, + [Undershirt_Item] [varchar](36) NOT NULL, + [Undershirt_Asset] [varchar](36) NOT NULL, + [Underpants_Item] [varchar](36) NOT NULL, + [Underpants_Asset] [varchar](36) NOT NULL, + [Skirt_Item] [varchar](36) NOT NULL, + [Skirt_Asset] [varchar](36) NOT NULL, PRIMARY KEY CLUSTERED ( - [UUID] + [Owner] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] -- cgit v1.1