From 281955949910eb257b5f7e42e54535ba7812418e Mon Sep 17 00:00:00 2001 From: Melanie Thielker Date: Sun, 14 Sep 2008 13:23:02 +0000 Subject: Mantis #2124 Thank you, RuudL, for a patch that brings MSSQL up to the same implementation level as MySQL. --- OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs | 27 +- OpenSim/Data/MSSQL/MSSQLAssetData.cs | 245 ++-- OpenSim/Data/MSSQL/MSSQLEstateData.cs | 12 +- OpenSim/Data/MSSQL/MSSQLGridData.cs | 579 ++++++--- OpenSim/Data/MSSQL/MSSQLInventoryData.cs | 818 ++++++------ OpenSim/Data/MSSQL/MSSQLLogData.cs | 106 +- OpenSim/Data/MSSQL/MSSQLManager.cs | 374 +----- OpenSim/Data/MSSQL/MSSQLRegionData.cs | 206 +-- OpenSim/Data/MSSQL/MSSQLUserData.cs | 1368 ++++++++++++-------- OpenSim/Data/MSSQL/Resources/001_AssetStore.sql | 13 + OpenSim/Data/MSSQL/Resources/001_GridStore.sql | 37 + .../Data/MSSQL/Resources/001_InventoryStore.sql | 64 + OpenSim/Data/MSSQL/Resources/001_UserStore.sql | 224 ++++ OpenSim/Data/MSSQL/Resources/002_AssetStore.sql | 29 + OpenSim/Data/MSSQL/Resources/002_UserStore.sql | 18 + OpenSim/Data/MSSQL/Resources/003_GridStore.sql | 22 + OpenSim/Data/MSSQL/Resources/003_UserStore.sql | 30 + OpenSim/Data/MSSQL/Resources/004_RegionStore.sql | 12 +- OpenSim/Data/MSSQL/Resources/004_UserStore.sql | 58 + OpenSim/Data/MSSQL/Resources/005_RegionStore.sql | 16 +- 20 files changed, 2538 insertions(+), 1720 deletions(-) create mode 100644 OpenSim/Data/MSSQL/Resources/001_AssetStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_GridStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/002_AssetStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/002_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/003_GridStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/003_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/004_UserStore.sql (limited to 'OpenSim/Data/MSSQL') diff --git a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs index cae864b..9211747 100644 --- a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs +++ b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs @@ -25,9 +25,6 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ -using System; -using System.Collections.Generic; -using System.Text; using System.Data.SqlClient; using System.Data; @@ -163,16 +160,22 @@ namespace OpenSim.Data.MSSQL realCommand.Prepare(); } - IDbTransaction IDbCommand.Transaction +// IDbTransaction IDbCommand.Transaction +// { +// get +// { +// return realCommand.Transaction; +// } +// set +// { +// realCommand.Transaction = (SqlTransaction) value; +// } +// } + + public IDbTransaction Transaction { - get - { - return realCommand.Transaction; - } - set - { - realCommand.Transaction = (SqlTransaction) value; - } + get { return realCommand.Transaction; } + set { realCommand.Transaction = (SqlTransaction)value; } } UpdateRowSource IDbCommand.UpdatedRowSource diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs index ed1c91b..4d5ca1c 100644 --- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs +++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs @@ -41,44 +41,83 @@ namespace OpenSim.Data.MSSQL /// internal class MSSQLAssetData : AssetDataBase { + private const string _migrationStore = "AssetStore"; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + /// + /// Database manager + /// private MSSQLManager database; - #region IAssetProviderPlugin Members + #region IPlugin Members + + override public void Dispose() { } /// - /// Migration method - /// - /// Execute "CreateAssetsTable.sql" if tableName == null - /// + /// Initialises asset interface /// - /// Name of table - private void UpgradeAssetsTable(string tableName) + override public void Initialise() { - // null as the version, indicates that the table didn't exist - if (tableName == null) + m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); + throw new PluginNotInitialisedException(Name); + } + + /// + /// Initialises asset interface + /// + /// + /// a string instead of file, if someone writes the support + /// + /// connect string + override public void Initialise(string connectionString) + { + if (string.IsNullOrEmpty(connectionString)) { - m_log.Info("[ASSET DB]: Creating new database tables"); - database.ExecuteResourceSql("CreateAssetsTable.sql"); - return; + 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); } + //TODO can be removed at some time!! + TestTables(); + + //New migration to check for DB changes + database.CheckMigration(_migrationStore); } /// - /// Ensure that the assets related tables exists and are at the latest version + /// Database provider version. /// - private void TestTables() + override public string Version { - Dictionary tableList = new Dictionary(); - - tableList["assets"] = null; - database.GetTableVersion(tableList); + get { return database.getVersion(); } + } - UpgradeAssetsTable(tableList["assets"]); + /// + /// The name of this DB provider. + /// + override public string Name + { + get { return "MSSQL Asset storage engine"; } } + #endregion + + #region IAssetProviderPlugin Members + /// /// Fetch Asset from database /// @@ -86,13 +125,26 @@ namespace OpenSim.Data.MSSQL /// override public AssetBase FetchAsset(UUID assetID) { - Dictionary param = new Dictionary(); - param["id"] = assetID.ToString(); - - using (IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param)) - using (IDataReader reader = result.ExecuteReader()) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM assets WHERE id = @id")) { - return database.getAssetRow(reader); + command.Parameters.Add(database.CreateParameter("id", assetID)); + using (IDataReader reader = command.ExecuteReader()) + { + if (reader.Read()) + { + AssetBase asset = new AssetBase(); + // Region Main + asset.FullID = new UUID((string)reader["id"]); + asset.Name = (string)reader["name"]; + asset.Description = (string)reader["description"]; + asset.Type = Convert.ToSByte(reader["assetType"]); + asset.Local = Convert.ToBoolean(reader["local"]); + asset.Temporary = Convert.ToBoolean(reader["temporary"]); + asset.Data = (byte[])reader["data"]; + return asset; + } + return null; // throw new Exception("No rows to return"); + } } } @@ -102,34 +154,27 @@ namespace OpenSim.Data.MSSQL /// the asset override public void CreateAsset(AssetBase asset) { - if (ExistsAsset((UUID) asset.FullID)) + if (ExistsAsset(asset.FullID)) { return; } - - using (AutoClosingSqlCommand cmd = - database.Query( + using (AutoClosingSqlCommand command = database.Query( "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" + " VALUES " + "(@id, @name, @description, @assetType, @local, @temporary, @data)")) { - //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar); //p.Value = asset.FullID.ToString(); - cmd.Parameters.AddWithValue("id", asset.FullID.ToString()); - cmd.Parameters.AddWithValue("name", asset.Name); - cmd.Parameters.AddWithValue("description", asset.Description); - SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt); - e.Value = asset.Type; - SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt); - g.Value = asset.Local; - SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt); - h.Value = asset.Temporary; - SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image); - i.Value = asset.Data; - - cmd.ExecuteNonQuery(); + command.Parameters.Add(database.CreateParameter("id", asset.FullID)); + command.Parameters.Add(database.CreateParameter("name", asset.Name)); + command.Parameters.Add(database.CreateParameter("description", asset.Description)); + command.Parameters.Add(database.CreateParameter("assetType", asset.Type)); + command.Parameters.Add(database.CreateParameter("local", asset.Local)); + command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary)); + command.Parameters.Add(database.CreateParameter("data", asset.Data)); + + command.ExecuteNonQuery(); } } @@ -139,7 +184,7 @@ namespace OpenSim.Data.MSSQL /// the asset override public void UpdateAsset(AssetBase asset) { - using (IDbCommand command = database.Query("UPDATE assets set id = @id, " + + using (AutoClosingSqlCommand command = database.Query("UPDATE assets set id = @id, " + "name = @name, " + "description = @description," + "assetType = @assetType," + @@ -148,22 +193,14 @@ namespace OpenSim.Data.MSSQL "data = @data where " + "id = @keyId;")) { - SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToString()); - SqlParameter param2 = new SqlParameter("@name", asset.Name); - SqlParameter param3 = new SqlParameter("@description", asset.Description); - SqlParameter param4 = new SqlParameter("@assetType", asset.Type); - SqlParameter param6 = new SqlParameter("@local", asset.Local); - SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary); - SqlParameter param8 = new SqlParameter("@data", asset.Data); - SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - command.Parameters.Add(param4); - command.Parameters.Add(param6); - command.Parameters.Add(param7); - command.Parameters.Add(param8); - command.Parameters.Add(param9); + command.Parameters.Add(database.CreateParameter("id", asset.FullID)); + command.Parameters.Add(database.CreateParameter("name", asset.Name)); + command.Parameters.Add(database.CreateParameter("description", asset.Description)); + command.Parameters.Add(database.CreateParameter("assetType", asset.Type)); + command.Parameters.Add(database.CreateParameter("local", asset.Local)); + command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary)); + command.Parameters.Add(database.CreateParameter("data", asset.Data)); + command.Parameters.Add(database.CreateParameter("@keyId", asset.FullID)); try { @@ -192,57 +229,69 @@ namespace OpenSim.Data.MSSQL #endregion - #region IPlugin Members - - override public void Dispose() { } + #region Private Methods /// - /// Initialises asset interface - /// - /// TODO: this would allow you to pass in connnect info as - /// a string instead of file, if someone writes the support - /// + /// Migration method + /// + /// Execute "CreateAssetsTable.sql" if tableName == null + /// /// - /// connect string - override public void Initialise(string connect) + /// Name of table + private void UpgradeAssetsTable(string tableName) { - Initialise(); + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + m_log.Info("[ASSET DB]: Creating new database tables"); + database.ExecuteResourceSql("CreateAssetsTable.sql"); + return; + } } /// - /// Initialises asset interface + /// Ensure that the assets related tables exists and are at the latest version /// - /// it use mssql_connection.ini - override public void Initialise() + private void TestTables() { - 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"); + Dictionary tableList = new Dictionary(); - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); + tableList["assets"] = null; + database.GetTableVersion(tableList); - TestTables(); - } + UpgradeAssetsTable(tableList["assets"]); - /// - /// Database provider version. - /// - override public string Version - { - get { return database.getVersion(); } - } + //Special for Migrations + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'")) + { + try + { + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + catch + { + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } - /// - /// The name of this DB provider. - /// - override public string Name - { - get { return "MSSQL Asset storage engine"; } } #endregion diff --git a/OpenSim/Data/MSSQL/MSSQLEstateData.cs b/OpenSim/Data/MSSQL/MSSQLEstateData.cs index b42c67f..b3595d9 100644 --- a/OpenSim/Data/MSSQL/MSSQLEstateData.cs +++ b/OpenSim/Data/MSSQL/MSSQLEstateData.cs @@ -39,6 +39,8 @@ namespace OpenSim.Data.MSSQL { public class MSSQLEstateData : IEstateDataStore { + private const string _migrationStore = "EstateStore"; + private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); private MSSQLManager _Database; @@ -74,15 +76,7 @@ namespace OpenSim.Data.MSSQL } //Migration settings - using (SqlConnection connection = _Database.DatabaseConnection()) - { - Assembly assem = GetType().Assembly; - MSSQLMigration migration = new MSSQLMigration(connection, assem, "EstateStore"); - - migration.Update(); - - connection.Close(); - } + _Database.CheckMigration(_migrationStore); //Interesting way to get parameters! Maybe implement that also with other types Type t = typeof(EstateSettings); diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs index 47edc4b..552dc04 100644 --- a/OpenSim/Data/MSSQL/MSSQLGridData.cs +++ b/OpenSim/Data/MSSQL/MSSQLGridData.cs @@ -28,9 +28,8 @@ using System; using System.Collections.Generic; using System.Data; +using System.Data.SqlClient; using System.Reflection; -using System.Security.Cryptography; -using System.Text; using OpenMetaverse; using log4net; using OpenSim.Framework; @@ -42,6 +41,8 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLGridData : GridDataBase { + private const string _migrationStore = "GridStore"; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// @@ -49,49 +50,62 @@ namespace OpenSim.Data.MSSQL /// private MSSQLManager database; - private string m_regionsTableName; + private string m_regionsTableName = "regions"; + + #region IPlugin Members override public void Initialise() { - m_log.Info("[MSSQLGridData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); + m_log.Info("[GRID DB]: " + Name + " cannot be default-initialized!"); + throw new PluginNotInitialisedException(Name); } /// /// Initialises the Grid Interface /// - /// connect string + /// connect string /// use mssql_connection.ini - override public void Initialise(string connect) + override public void Initialise(string connectionString) { - // TODO: make the connect string actually do something - IniFile iniFile = new IniFile("mssql_connection.ini"); - - string settingDataSource = iniFile.ParseFileReadValue("data_source"); - string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); - string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); - string settingUserId = iniFile.ParseFileReadValue("user_id"); - string settingPassword = iniFile.ParseFileReadValue("password"); - - m_regionsTableName = iniFile.ParseFileReadValue("regionstablename"); - if (m_regionsTableName == null) + if (string.IsNullOrEmpty(connectionString)) { - m_regionsTableName = "regions"; + database = new MSSQLManager(connectionString); } + else + { + // TODO: make the connect string actually do something + IniFile iniFile = new IniFile("mssql_connection.ini"); + + string settingDataSource = iniFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); + string settingUserId = iniFile.ParseFileReadValue("user_id"); + string settingPassword = iniFile.ParseFileReadValue("password"); - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); + m_regionsTableName = iniFile.ParseFileReadValue("regionstablename"); + if (m_regionsTableName == null) + { + m_regionsTableName = "regions"; + } + + database = + new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, + settingPassword); + } + //TODO this can be removed at a certain time TestTables(); + + //New migrations check of store + database.CheckMigration(_migrationStore); } /// - /// + /// Test is region /// private void TestTables() { - using (IDbCommand cmd = database.Query("SELECT TOP 1 * FROM " + m_regionsTableName, new Dictionary())) + using (AutoClosingSqlCommand cmd = database.Query("SELECT TOP 1 * FROM " + m_regionsTableName, new Dictionary())) { try { @@ -103,6 +117,36 @@ namespace OpenSim.Data.MSSQL database.ExecuteResourceSql("Mssql-regions.sql"); } } + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'")) + { + //Special for Migrations to create backword compatible + try + { + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + catch + { + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } } /// @@ -110,7 +154,7 @@ namespace OpenSim.Data.MSSQL /// override public void Dispose() { - // nothing to close + database = null; } /// @@ -131,18 +175,44 @@ namespace OpenSim.Data.MSSQL get { return "0.1"; } } + #endregion + + #region Public override GridDataBase methods + /// - /// NOT IMPLEMENTED, /// Returns a list of regions within the specified ranges /// - /// minimum X coordinate - /// minimum Y coordinate - /// maximum X coordinate - /// maximum Y coordinate + /// minimum X coordinate + /// minimum Y coordinate + /// maximum X coordinate + /// maximum Y coordinate /// null /// always return null - override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d) + override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax) { + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax")) + { + command.Parameters.Add(database.CreateParameter("xmin", xmin)); + command.Parameters.Add(database.CreateParameter("ymin", ymin)); + command.Parameters.Add(database.CreateParameter("xmax", xmax)); + command.Parameters.Add(database.CreateParameter("ymax", ymax)); + + List rows = new List(); + + using (SqlDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + rows.Add(ReadSimRow(reader)); + } + } + + if (rows.Count > 0) + { + return rows.ToArray(); + } + } + m_log.Info("[GRID DB] : Found no regions within range."); return null; } @@ -153,22 +223,20 @@ namespace OpenSim.Data.MSSQL /// Sim profile override public RegionProfileData GetProfileByHandle(ulong handle) { - - Dictionary param = new Dictionary(); - param["handle"] = handle.ToString(); - - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle")) { - using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param)) - using (IDataReader reader = result.ExecuteReader()) + command.Parameters.Add(database.CreateParameter("handle", handle)); + + using (SqlDataReader reader = command.ExecuteReader()) { - return database.getRegionRow(reader); + if (reader.Read()) + { + return ReadSimRow(reader); + } } } - catch - { - return null; - } + m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle); + return null; } /// @@ -178,50 +246,49 @@ namespace OpenSim.Data.MSSQL /// The sim profile override public RegionProfileData GetProfileByUUID(UUID uuid) { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid")) { - return database.getRegionRow(reader); - } + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + using (SqlDataReader reader = command.ExecuteReader()) + { + if (reader.Read()) + { + return ReadSimRow(reader); + } + } + } + m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid); + return null; } /// /// Returns a sim profile from it's Region name string /// - /// The region name search query + /// The region name search query /// The sim profile override public RegionProfileData GetProfileByString(string regionName) { if (regionName.Length > 2) { - try + using (AutoClosingSqlCommand command = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName")) { - Dictionary param = new Dictionary(); - // Add % because this is a like query. - param["?regionName"] = regionName + "%"; - // Order by statement will return shorter matches first. Only returns one record or no record. - using (IDbCommand result = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like ?regionName order by regionName", param)) - using (IDataReader reader = result.ExecuteReader()) + command.Parameters.Add(database.CreateParameter("regionName", regionName + "%")); + + using (SqlDataReader reader = command.ExecuteReader()) { - return database.getRegionRow(reader); + if (reader.Read()) + { + return ReadSimRow(reader); + } } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } - else - { - m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters"); + m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName); return null; } + + m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters"); + return null; } /// @@ -231,14 +298,11 @@ namespace OpenSim.Data.MSSQL /// A dataresponse enum indicating success override public DataResponse AddProfile(RegionProfileData profile) { - if (insertRegionRow(profile)) + if (InsertRegionRow(profile)) { return DataResponse.RESPONSE_OK; } - else - { - return DataResponse.RESPONSE_ERROR; - } + return DataResponse.RESPONSE_ERROR; } /// @@ -246,156 +310,43 @@ namespace OpenSim.Data.MSSQL /// /// The profile to update /// A dataresponse enum indicating success - public override DataResponse UpdateProfile(RegionProfileData profile) + override public DataResponse UpdateProfile(RegionProfileData profile) { - if (updateRegionRow(profile)) + if (UpdateRegionRow(profile)) { return DataResponse.RESPONSE_OK; } - else - { - return DataResponse.RESPONSE_ERROR; - } + return DataResponse.RESPONSE_ERROR; } /// - /// Update the specified region in the database + /// Deletes a sim profile from the database /// - /// The profile to update - /// success ? - public bool updateRegionRow(RegionProfileData profile) + /// the sim UUID + /// Successful? + //public DataResponse DeleteProfile(RegionProfileData profile) + override public DataResponse DeleteProfile(string uuid) { - //Insert new region - string sql = - "UPDATE " + m_regionsTableName + @" SET - [regionHandle]=@regionHandle, [regionName]=@regionName, - [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey, - [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI, - [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle, - [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle, - [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI, - [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey, - [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey, - [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort, - [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid - where [uuid]=@uuid"; - - Dictionary parameters = new Dictionary(); - - parameters["regionHandle"] = profile.regionHandle.ToString(); - parameters["regionName"] = profile.regionName; - parameters["uuid"] = profile.ToString(); - parameters["regionRecvKey"] = profile.regionRecvKey; - parameters["regionSecret"] = profile.regionSecret; - parameters["regionSendKey"] = profile.regionSendKey; - parameters["regionDataURI"] = profile.regionDataURI; - parameters["serverIP"] = profile.serverIP; - parameters["serverPort"] = profile.serverPort.ToString(); - parameters["serverURI"] = profile.serverURI; - parameters["locX"] = profile.regionLocX.ToString(); - parameters["locY"] = profile.regionLocY.ToString(); - parameters["locZ"] = profile.regionLocZ.ToString(); - parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString(); - parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString(); - parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString(); - parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString(); - parameters["regionAssetURI"] = profile.regionAssetURI; - parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey; - parameters["regionAssetSendKey"] = profile.regionAssetSendKey; - parameters["regionUserURI"] = profile.regionUserURI; - parameters["regionUserRecvKey"] = profile.regionUserRecvKey; - parameters["regionUserSendKey"] = profile.regionUserSendKey; - parameters["regionMapTexture"] = profile.regionMapTextureID.ToString(); - parameters["serverHttpPort"] = profile.httpPort.ToString(); - parameters["serverRemotingPort"] = profile.remotingPort.ToString(); - parameters["owner_uuid"] = profile.owner_uuid.ToString(); - - bool returnval = false; - - try + using (AutoClosingSqlCommand command = database.Query("DELETE FROM regions WHERE uuid = @uuid;")) { - using (IDbCommand result = database.Query(sql, parameters)) + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + try { - - if (result.ExecuteNonQuery() == 1) - returnval = true; - + command.ExecuteNonQuery(); + return DataResponse.RESPONSE_OK; } - } - catch (Exception e) - { - m_log.Error("MSSQLManager : " + e.ToString()); - } - - return returnval; - } - /// - /// Creates a new region in the database - /// - /// The region profile to insert - /// Successful? - public bool insertRegionRow(RegionProfileData profile) - { - //Insert new region - string sql = - "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], - [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], - [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey], - [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], - [serverRemotingPort], [owner_uuid]) - VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, - @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, - @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey, - @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid);"; - - Dictionary parameters = new Dictionary(); - - parameters["regionHandle"] = profile.regionHandle.ToString(); - parameters["regionName"] = profile.regionName; - parameters["uuid"] = profile.ToString(); - parameters["regionRecvKey"] = profile.regionRecvKey; - parameters["regionSecret"] = profile.regionSecret; - parameters["regionSendKey"] = profile.regionSendKey; - parameters["regionDataURI"] = profile.regionDataURI; - parameters["serverIP"] = profile.serverIP; - parameters["serverPort"] = profile.serverPort.ToString(); - parameters["serverURI"] = profile.serverURI; - parameters["locX"] = profile.regionLocX.ToString(); - parameters["locY"] = profile.regionLocY.ToString(); - parameters["locZ"] = profile.regionLocZ.ToString(); - parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString(); - parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString(); - parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString(); - parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString(); - parameters["regionAssetURI"] = profile.regionAssetURI; - parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey; - parameters["regionAssetSendKey"] = profile.regionAssetSendKey; - parameters["regionUserURI"] = profile.regionUserURI; - parameters["regionUserRecvKey"] = profile.regionUserRecvKey; - parameters["regionUserSendKey"] = profile.regionUserSendKey; - parameters["regionMapTexture"] = profile.regionMapTextureID.ToString(); - parameters["serverHttpPort"] = profile.httpPort.ToString(); - parameters["serverRemotingPort"] = profile.remotingPort.ToString(); - parameters["owner_uuid"] = profile.owner_uuid.ToString(); - - bool returnval = false; - - try - { - using (IDbCommand result = database.Query(sql, parameters)) + catch (Exception e) { - if (result.ExecuteNonQuery() == 1) - returnval = true; + m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message); + return DataResponse.RESPONSE_ERROR; } } - catch (Exception e) - { - m_log.Error("[GRID DB]: " + e.ToString()); - } - - return returnval; } + #endregion + + #region Methods that are not used or deprecated (still needed because of base class) + /// /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret. /// @@ -436,6 +387,7 @@ namespace OpenSim.Data.MSSQL /// /// NOT IMPLEMENTED + /// WHEN IS THIS GONNA BE IMPLEMENTED. /// /// /// @@ -444,5 +396,218 @@ namespace OpenSim.Data.MSSQL { return null; } + + #endregion + + #region private methods + + /// + /// Reads a region row from a database reader + /// + /// An active database reader + /// A region profile + private static RegionProfileData ReadSimRow(IDataRecord reader) + { + RegionProfileData retval = new RegionProfileData(); + + // Region Main gotta-have-or-we-return-null parts + UInt64 tmp64; + if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64)) + { + return null; + } + + retval.regionHandle = tmp64; + + UUID tmp_uuid; + if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid)) + { + return null; + } + + retval.UUID = tmp_uuid; + + // non-critical parts + retval.regionName = reader["regionName"].ToString(); + retval.originUUID = new UUID((string)reader["originUUID"]); + + // Secrets + retval.regionRecvKey = reader["regionRecvKey"].ToString(); + retval.regionSecret = reader["regionSecret"].ToString(); + retval.regionSendKey = reader["regionSendKey"].ToString(); + + // Region Server + retval.regionDataURI = reader["regionDataURI"].ToString(); + retval.regionOnline = false; // Needs to be pinged before this can be set. + retval.serverIP = reader["serverIP"].ToString(); + retval.serverPort = Convert.ToUInt32(reader["serverPort"]); + retval.serverURI = reader["serverURI"].ToString(); + retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString()); + retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString()); + + // Location + retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString()); + retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString()); + retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString()); + + // Neighbours - 0 = No Override + retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString()); + retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString()); + retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString()); + retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString()); + + // Assets + retval.regionAssetURI = reader["regionAssetURI"].ToString(); + retval.regionAssetRecvKey = reader["regionAssetRecvKey"].ToString(); + retval.regionAssetSendKey = reader["regionAssetSendKey"].ToString(); + + // Userserver + retval.regionUserURI = reader["regionUserURI"].ToString(); + retval.regionUserRecvKey = reader["regionUserRecvKey"].ToString(); + retval.regionUserSendKey = reader["regionUserSendKey"].ToString(); + + // World Map Addition + UUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID); + UUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid); + + return retval; + } + + /// + /// Update the specified region in the database + /// + /// The profile to update + /// success ? + private bool UpdateRegionRow(RegionProfileData profile) + { + bool returnval = false; + + //Insert new region + string sql = + "UPDATE " + m_regionsTableName + @" SET + [regionHandle]=@regionHandle, [regionName]=@regionName, + [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey, + [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI, + [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle, + [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle, + [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI, + [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey, + [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey, + [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort, + [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid , [originUUID]=@originUUID + where [uuid]=@uuid"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle)); + command.Parameters.Add(database.CreateParameter("regionName", profile.regionName)); + command.Parameters.Add(database.CreateParameter("uuid", profile.UUID)); + command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey)); + command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret)); + command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey)); + command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI)); + command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP)); + command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort)); + command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI)); + command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX)); + command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY)); + command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ)); + command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle)); + command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle)); + command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle)); + command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle)); + command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI)); + command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey)); + command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey)); + command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI)); + command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey)); + command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey)); + command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID)); + command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort)); + command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort)); + command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid)); + command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID)); + + try + { + command.ExecuteNonQuery(); + returnval = true; + } + catch (Exception e) + { + m_log.Error("[GRID DB] : Error updating region, error: " + e.Message); + } + } + + return returnval; + } + + /// + /// Creates a new region in the database + /// + /// The region profile to insert + /// Successful? + private bool InsertRegionRow(RegionProfileData profile) + { + bool returnval = false; + + //Insert new region + string sql = + "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], + [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], + [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey], + [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], + [serverRemotingPort], [owner_uuid], [originUUID]) + VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, + @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, + @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey, + @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID);"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle)); + command.Parameters.Add(database.CreateParameter("regionName", profile.regionName)); + command.Parameters.Add(database.CreateParameter("uuid", profile.UUID)); + command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey)); + command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret)); + command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey)); + command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI)); + command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP)); + command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort)); + command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI)); + command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX)); + command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY)); + command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ)); + command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle)); + command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle)); + command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle)); + command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle)); + command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI)); + command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey)); + command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey)); + command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI)); + command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey)); + command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey)); + command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID)); + command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort)); + command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort)); + command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid)); + command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID)); + + try + { + command.ExecuteNonQuery(); + returnval = true; + } + catch (Exception e) + { + m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message); + } + } + + return returnval; + } + + #endregion } } diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs index e7df7c1..03600e2 100644 --- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs +++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs @@ -41,49 +41,53 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLInventoryData : IInventoryDataPlugin { - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private const string _migrationStore = "InventoryStore"; - #region Helper converters to preserve unsigned bitfield-type data in DB roundtrips via signed int32s - private static int ConvertUint32BitFieldToInt32(uint bitField) - { - return BitConverter.ToInt32(BitConverter.GetBytes(bitField), 0); - } - private static uint ConvertInt32BitFieldToUint32(int bitField) - { - return BitConverter.ToUInt32(BitConverter.GetBytes(bitField), 0); - } - #endregion + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// /// The database manager /// private MSSQLManager database; - public void Initialise() - { + #region IPlugin members + + public void Initialise() + { m_log.Info("[MSSQLInventoryData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); + throw new PluginNotInitialisedException(Name); } /// /// Loads and initialises the MSSQL inventory storage interface /// - /// connect string + /// connect string /// use mssql_connection.ini - public void Initialise(string connect) + public void Initialise(string connectionString) { - // TODO: actually use the provided connect string - 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); + 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); + } + + //TODO remove this at one point TestTables(); + + //New migrations check of store + database.CheckMigration(_migrationStore); } #region Test and initialization code @@ -132,6 +136,37 @@ namespace OpenSim.Data.MSSQL UpgradeFoldersTable(tableList["inventoryfolders"]); UpgradeItemsTable(tableList["inventoryitems"]); + + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'")) + { + //Special for Migrations to create backword compatible + try + { + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + catch + { + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } } #endregion @@ -150,7 +185,7 @@ namespace OpenSim.Data.MSSQL /// public void Dispose() { - // Do nothing. + database = null; } /// @@ -162,39 +197,9 @@ namespace OpenSim.Data.MSSQL get { return database.getVersion(); } } - /// - /// Returns a list of items in a specified folder - /// - /// The folder to search - /// A list containing inventory items - public List getInventoryInFolder(UUID folderID) - { - try - { - List items = new List(); - - Dictionary param = new Dictionary(); - param["parentFolderID"] = folderID.ToString(); - - using (IDbCommand result = - database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - while (reader.Read()) - items.Add(readInventoryItem(reader)); - - reader.Close(); - } + #endregion - return items; - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } + #region Folder methods /// /// Returns a list of the root folders within a users inventory @@ -203,31 +208,7 @@ namespace OpenSim.Data.MSSQL /// A list of folder objects public List getUserRootFolders(UUID user) { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = user.ToString(); - param["zero"] = UUID.Zero.ToString(); - - using (IDbCommand result = - database.Query( - "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - List items = new List(); - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - return items; - } - - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } + return getInventoryFolders(UUID.Zero, user); } /// @@ -237,43 +218,21 @@ namespace OpenSim.Data.MSSQL /// public InventoryFolderBase getUserRootFolder(UUID user) { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = user.ToString(); - param["zero"] = UUID.Zero.ToString(); - - using (IDbCommand result = - database.Query( - "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - - List items = new List(); - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - InventoryFolderBase rootFolder = null; + List items = getUserRootFolders(user); - // There should only ever be one root folder for a user. However, if there's more - // than one we'll simply use the first one rather than failing. It would be even - // nicer to print some message to this effect, but this feels like it's too low a - // to put such a message out, and it's too minor right now to spare the time to - // suitably refactor. - if (items.Count > 0) - { - rootFolder = items[0]; - } - - return rootFolder; - } + InventoryFolderBase rootFolder = null; - } - catch (Exception e) + // There should only ever be one root folder for a user. However, if there's more + // than one we'll simply use the first one rather than failing. It would be even + // nicer to print some message to this effect, but this feels like it's too low a + // to put such a message out, and it's too minor right now to spare the time to + // suitably refactor. + if (items.Count > 0) { - m_log.Error(e.ToString()); - return null; + rootFolder = items[0]; } + + return rootFolder; } /// @@ -283,156 +242,235 @@ namespace OpenSim.Data.MSSQL /// A list of inventory folders public List getInventoryFolders(UUID parentID) { - try + return getInventoryFolders(parentID, UUID.Zero); + } + + /// + /// Returns a specified inventory folder + /// + /// The folder to return + /// A folder class + public InventoryFolderBase getInventoryFolder(UUID folderID) + { + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID")) { - Dictionary param = new Dictionary(); - param["parentFolderID"] = parentID.ToString(); + command.Parameters.Add(database.CreateParameter("folderID", folderID)); - using (IDbCommand result = - database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param)) - using (IDataReader reader = result.ExecuteReader()) + using (IDataReader reader = command.ExecuteReader()) { - List items = new List(); - - while (reader.Read()) - items.Add(readInventoryFolder(reader)); - - return items; + if (reader.Read()) + { + return readInventoryFolder(reader); + } } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } + m_log.InfoFormat("[INVENTORY DB] : FOund no inventory folder with ID : {0}", folderID); + return null; } /// - /// Reads a one item from an SQL result + /// Returns all child folders in the hierarchy from the parent folder and down. + /// Does not return the parent folder itself. /// - /// The SQL Result - /// the item read - private static InventoryItemBase readInventoryItem(IDataReader reader) + /// The folder to get subfolders for + /// A list of inventory folders + public List getFolderHierarchy(UUID parentID) { - try - { - InventoryItemBase item = new InventoryItemBase(); + //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way. + //Note this is changed so it opens only one connection to the database and not everytime it wants to get data. - item.ID = new UUID((string) reader["inventoryID"]); - item.AssetID = new UUID((string) reader["assetID"]); - item.AssetType = (int) reader["assetType"]; - item.Folder = new UUID((string) reader["parentFolderID"]); - item.Owner = new UUID((string) reader["avatarID"]); - item.Name = (string) reader["inventoryName"]; - item.Description = (string) reader["inventoryDescription"]; - item.NextPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryNextPermissions"]); - item.CurrentPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryCurrentPermissions"]); - item.InvType = (int) reader["invType"]; - item.Creator = new UUID((string) reader["creatorID"]); - item.BasePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryBasePermissions"]); - item.EveryOnePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryEveryOnePermissions"]); - item.SalePrice = (int) reader["salePrice"]; - item.SaleType = Convert.ToByte(reader["saleType"]); - item.CreationDate = (int) reader["creationDate"]; - item.GroupID = new UUID(reader["groupID"].ToString()); - item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); - item.Flags = ConvertInt32BitFieldToUint32((int)reader["flags"]); + List folders = new List(); - return item; - } - catch (SqlException e) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID")) { - m_log.Error(e.ToString()); - } + command.Parameters.Add(database.CreateParameter("@parentID", parentID)); - return null; + folders.AddRange(getInventoryFolders(command)); + + List tempFolders = new List(); + + foreach (InventoryFolderBase folderBase in folders) + { + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); + } + if (tempFolders.Count > 0) + { + folders.AddRange(tempFolders); + } + } + return folders; } /// - /// Returns a specified inventory item + /// Creates a new inventory folder /// - /// The item to return - /// An inventory item - public InventoryItemBase getInventoryItem(UUID itemID) + /// Folder to create + public void addInventoryFolder(InventoryFolderBase folder) { - try - { - Dictionary param = new Dictionary(); - param["inventoryID"] = itemID.ToString(); + string sql = + "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; + sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; - using (IDbCommand result = - database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param)) - using (IDataReader reader = result.ExecuteReader()) - { - InventoryItemBase item = null; - if (reader.Read()) - item = readInventoryItem(reader); + 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", folder.Name)); + command.Parameters.Add(database.CreateParameter("type", folder.Type)); + command.Parameters.Add(database.CreateParameter("version", folder.Version)); - return item; + try + { + //IDbCommand result = database.Query(sql, param); + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - return null; } /// - /// Reads a list of inventory folders returned by a query. + /// Updates an inventory folder /// - /// A MSSQL Data Reader - /// A List containing inventory folders - protected static InventoryFolderBase readInventoryFolder(IDataReader reader) + /// Folder to update + public void updateInventoryFolder(InventoryFolderBase folder) { - try + using (AutoClosingSqlCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + + "agentID = @agentID, " + + "parentFolderID = @parentFolderID," + + "folderName = @folderName," + + "type = @type," + + "version = @version where " + + "folderID = @keyFolderID;")) { - InventoryFolderBase folder = new InventoryFolderBase(); - folder.Owner = new UUID((string) reader["agentID"]); - folder.ParentID = new UUID((string) reader["parentFolderID"]); - folder.ID = new UUID((string) reader["folderID"]); - folder.Name = (string) reader["folderName"]; - folder.Type = (short) reader["type"]; - folder.Version = Convert.ToUInt16(reader["version"]); - return folder; + 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", folder.Name)); + command.Parameters.Add(database.CreateParameter("type", folder.Type)); + command.Parameters.Add(database.CreateParameter("version", folder.Version)); + command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID)); + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); + } } - catch (Exception e) + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + public void moveInventoryFolder(InventoryFolderBase folder) + { + using (IDbCommand command = database.Query("UPDATE inventoryfolders set " + + "parentFolderID = @parentFolderID where " + + "folderID = @folderID;")) { - m_log.Error(e.ToString()); - } + command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); + command.Parameters.Add(database.CreateParameter("@folderID", folder.ID)); - return null; + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message); + } + } } /// - /// Returns a specified inventory folder + /// Delete an inventory folder /// - /// The folder to return - /// A folder class - public InventoryFolderBase getInventoryFolder(UUID folderID) + /// Id of folder to delete + public void deleteInventoryFolder(UUID folderID) { - try + using (SqlConnection connection = database.DatabaseConnection()) { - Dictionary param = new Dictionary(); - param["uuid"] = folderID.ToString(); + List subFolders; + using (SqlCommand command = new SqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID", connection)) + { + command.Parameters.Add(database.CreateParameter("@parentID", string.Empty)); - using (IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) + AutoClosingSqlCommand autoCommand = new AutoClosingSqlCommand(command); + + subFolders = getFolderHierarchy(folderID, autoCommand); + } + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) { + DeleteOneFolder(f.ID, connection); + DeleteItemsInFolder(f.ID, connection); + } - reader.Read(); + //Delete the actual row + DeleteOneFolder(folderID, connection); + DeleteItemsInFolder(folderID, connection); + + connection.Close(); + } + } + + #endregion - InventoryFolderBase folder = readInventoryFolder(reader); + #region Item Methods + + /// + /// Returns a list of items in a specified folder + /// + /// The folder to search + /// A list containing inventory items + public List getInventoryInFolder(UUID folderID) + { + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID")) + { + command.Parameters.Add(database.CreateParameter("parentFolderID", folderID)); + + List items = new List(); - return folder; + using (SqlDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + items.Add(readInventoryItem(reader)); + } } + return items; } - catch (Exception e) + } + + /// + /// Returns a specified inventory item + /// + /// The item ID + /// An inventory item + public InventoryItemBase getInventoryItem(UUID itemID) + { + using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID")) { - m_log.Error(e.ToString()); - return null; + result.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + + using (IDataReader reader = result.ExecuteReader()) + { + if (reader.Read()) + { + return readInventoryItem(reader); + } + } } + m_log.InfoFormat("[INVENTORY DB] : Found no inventory item with ID : {0}", itemID); + return null; } /// @@ -448,46 +486,44 @@ namespace OpenSim.Data.MSSQL } string sql = "INSERT INTO inventoryitems"; - sql += - "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]" + sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]" + ", [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions]" + ", [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]" + ", [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) VALUES "; - sql += - "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription" + sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription" + ", @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID" + ", @inventoryBasePermissions, @inventoryEveryOnePermissions, @salePrice, @saleType" + ", @creationDate, @groupID, @groupOwned, @flags);"; using (AutoClosingSqlCommand command = database.Query(sql)) { - command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); - command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); - command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); - command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); - command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); - command.Parameters.AddWithValue("inventoryName", item.Name); - command.Parameters.AddWithValue("inventoryDescription", item.Description); - command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); - command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); - command.Parameters.AddWithValue("invType", item.InvType); - command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); - command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); - command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); - command.Parameters.AddWithValue("salePrice", item.SalePrice); - command.Parameters.AddWithValue("saleType", item.SaleType); - command.Parameters.AddWithValue("creationDate", item.CreationDate); - command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); - command.Parameters.AddWithValue("groupOwned", item.GroupOwned); - command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); + command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); + command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); + command.Parameters.Add(database.CreateParameter("assetType", item.AssetType)); + command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder)); + command.Parameters.Add(database.CreateParameter("avatarID", item.Owner)); + command.Parameters.Add(database.CreateParameter("inventoryName", item.Name)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description)); + command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions)); + command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions)); + command.Parameters.Add(database.CreateParameter("invType", item.InvType)); + command.Parameters.Add(database.CreateParameter("creatorID", item.Creator)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice)); + command.Parameters.Add(database.CreateParameter("saleType", item.SaleType)); + command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate)); + command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); + command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); + command.Parameters.Add(database.CreateParameter("flags", item.Flags)); try { command.ExecuteNonQuery(); } - catch (SqlException e) + catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error inserting item :" + e.Message); } } @@ -520,26 +556,26 @@ namespace OpenSim.Data.MSSQL "flags = @flags where " + "inventoryID = @keyInventoryID;")) { - command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); - command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); - command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); - command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); - command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); - command.Parameters.AddWithValue("inventoryName", item.Name); - command.Parameters.AddWithValue("inventoryDescription", item.Description); - command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); - command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); - command.Parameters.AddWithValue("invType", item.InvType); - command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); - command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); - command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); - command.Parameters.AddWithValue("salePrice", item.SalePrice); - command.Parameters.AddWithValue("saleType", item.SaleType); - command.Parameters.AddWithValue("creationDate", item.CreationDate); - command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); - command.Parameters.AddWithValue("groupOwned", item.GroupOwned); - command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); - command.Parameters.AddWithValue("@keyInventoryID", item.ID.ToString()); + command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); + command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); + command.Parameters.Add(database.CreateParameter("assetType", item.AssetType)); + command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder)); + command.Parameters.Add(database.CreateParameter("avatarID", item.Owner)); + command.Parameters.Add(database.CreateParameter("inventoryName", item.Name)); + command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description)); + command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions)); + command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions)); + command.Parameters.Add(database.CreateParameter("invType", item.InvType)); + command.Parameters.Add(database.CreateParameter("creatorID", item.Creator)); + command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); + command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); + command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice)); + command.Parameters.Add(database.CreateParameter("saleType", item.SaleType)); + command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate)); + 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)); try { @@ -547,225 +583,221 @@ namespace OpenSim.Data.MSSQL } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error updating item :" + e.Message); } } } + // See IInventoryDataPlugin + /// /// Delete an item in inventory database /// - /// the item UUID + /// the item UUID public void deleteInventoryItem(UUID itemID) { - try + using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID")) { - Dictionary param = new Dictionary(); - param["uuid"] = itemID.ToString(); + command.Parameters.Add(database.CreateParameter("inventoryID", itemID)); + try + { - using (IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param)) + command.ExecuteNonQuery(); + } + catch (Exception e) { - cmd.ExecuteNonQuery(); + m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message); } } - catch (SqlException e) - { - m_log.Error(e.ToString()); - } } + #endregion + + #region Private methods + /// - /// Creates a new inventory folder + /// Delete an item in inventory database /// - /// Folder to create - public void addInventoryFolder(InventoryFolderBase folder) + /// the item ID + /// connection to the database + private void DeleteItemsInFolder(UUID folderID, SqlConnection connection) { - string sql = - "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; - sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; - - - using (AutoClosingSqlCommand command = database.Query(sql)) + using (SqlCommand command = new SqlCommand("DELETE FROM inventoryitems WHERE folderID=@folderID", connection)) { - command.Parameters.AddWithValue("folderID", folder.ID.ToString()); - command.Parameters.AddWithValue("agentID", folder.Owner.ToString()); - command.Parameters.AddWithValue("parentFolderID", folder.ParentID.ToString()); - command.Parameters.AddWithValue("folderName", folder.Name); - command.Parameters.AddWithValue("type", folder.Type); - command.Parameters.AddWithValue("version", Convert.ToInt32(folder.Version)); + command.Parameters.Add(database.CreateParameter("folderID", folderID)); try { - //IDbCommand result = database.Query(sql, param); command.ExecuteNonQuery(); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message); } } } /// - /// Updates an inventory folder + /// Gets the folder hierarchy in a loop. /// - /// Folder to update - public void updateInventoryFolder(InventoryFolderBase folder) + /// parent ID. + /// SQL command/connection to database + /// + private static List getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command) { - using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + - "agentID = @agentID, " + - "parentFolderID = @parentFolderID," + - "folderName = @folderName," + - "type = @type," + - "version = @version where " + - "folderID = @keyFolderID;")) + command.Parameters["@parentID"].Value = parentID.ToString(); + + List folders = getInventoryFolders(command); + + if (folders.Count > 0) { - SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); - SqlParameter param2 = new SqlParameter("@agentID", folder.Owner.ToString()); - SqlParameter param3 = new SqlParameter("@parentFolderID", folder.ParentID.ToString()); - SqlParameter param4 = new SqlParameter("@folderName", folder.Name); - SqlParameter param5 = new SqlParameter("@type", folder.Type); - SqlParameter param6 = new SqlParameter("@version", Convert.ToInt32(folder.Version)); - SqlParameter param7 = new SqlParameter("@keyFolderID", folder.ID.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - command.Parameters.Add(param4); - command.Parameters.Add(param5); - command.Parameters.Add(param6); - command.Parameters.Add(param7); + List tempFolders = new List(); - try + foreach (InventoryFolderBase folderBase in folders) { - command.ExecuteNonQuery(); + tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); } - catch (Exception e) + + if (tempFolders.Count > 0) { - m_log.Error(e.ToString()); + folders.AddRange(tempFolders); } } + return folders; } /// - /// Updates an inventory folder + /// Gets the inventory folders. /// - /// Folder to update - public void moveInventoryFolder(InventoryFolderBase folder) + /// parentID, use UUID.Zero to get root + /// user id, use UUID.Zero, if you want all folders from a parentID. + /// + private List getInventoryFolders(UUID parentID, UUID user) { - using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + - "parentFolderID = @parentFolderID," + - "folderID = @keyFolderID;")) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid")) { - SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); - SqlParameter param2 = new SqlParameter("@parentFolderID", folder.ParentID.ToString()); - SqlParameter param3 = new SqlParameter("@keyFolderID", folder.ID.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - - try + if (user == UUID.Zero) { - command.ExecuteNonQuery(); + command.Parameters.Add(database.CreateParameter("uuid", "%")); } - catch (Exception e) + else { - m_log.Error(e.ToString()); + command.Parameters.Add(database.CreateParameter("uuid", user)); } + command.Parameters.Add(database.CreateParameter("parentID", parentID)); + + return getInventoryFolders(command); } } /// - /// Append a list of all the child folders of a parent folder + /// Gets the inventory folders. /// - /// list where folders will be appended - /// ID of parent - protected void getInventoryFolders(ref List folders, UUID parentID) - { - List subfolderList = getInventoryFolders(parentID); - - foreach (InventoryFolderBase f in subfolderList) - folders.Add(f); - } - - // See IInventoryDataPlugin - public List getFolderHierarchy(UUID parentID) + /// SQLcommand. + /// + private static List getInventoryFolders(AutoClosingSqlCommand command) { - List folders = new List(); - getInventoryFolders(ref folders, parentID); - - for (int i = 0; i < folders.Count; i++) - getInventoryFolders(ref folders, folders[i].ID); + using (IDataReader reader = command.ExecuteReader()) + { - return folders; + List items = new List(); + while (reader.Read()) + { + items.Add(readInventoryFolder(reader)); + } + return items; + } } /// - /// Delete a folder in inventory databasae + /// Reads a list of inventory folders returned by a query. /// - /// the folder UUID - protected void deleteOneFolder(UUID folderID) + /// A MSSQL Data Reader + /// A List containing inventory folders + protected static InventoryFolderBase readInventoryFolder(IDataReader reader) { try { - Dictionary param = new Dictionary(); - param["folderID"] = folderID.ToString(); + InventoryFolderBase folder = new InventoryFolderBase(); + folder.Owner = new UUID((string)reader["agentID"]); + folder.ParentID = new UUID((string)reader["parentFolderID"]); + folder.ID = new UUID((string)reader["folderID"]); + folder.Name = (string)reader["folderName"]; + folder.Type = (short)reader["type"]; + folder.Version = Convert.ToUInt16(reader["version"]); - using (IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param)) - { - cmd.ExecuteNonQuery(); - } + return folder; } - catch (SqlException e) + catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error reading inventory folder :" + e.Message); } + + return null; } /// - /// Delete an item in inventory database + /// Reads a one item from an SQL result /// - /// the item ID - protected void deleteItemsInFolder(UUID folderID) + /// The SQL Result + /// the item read + private static InventoryItemBase readInventoryItem(IDataRecord reader) { try { - Dictionary param = new Dictionary(); - param["parentFolderID"] = folderID.ToString(); + InventoryItemBase item = new InventoryItemBase(); + item.ID = new UUID(reader["inventoryID"].ToString()); + item.AssetID = new UUID(reader["assetID"].ToString()); + item.AssetType = Convert.ToInt32(reader["assetType"].ToString()); + item.Folder = new UUID(reader["parentFolderID"].ToString()); + item.Owner = new UUID(reader["avatarID"].ToString()); + item.Name = reader["inventoryName"].ToString(); + item.Description = reader["inventoryDescription"].ToString(); + item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]); + item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]); + item.InvType = Convert.ToInt32(reader["invType"].ToString()); + item.Creator = new UUID(reader["creatorID"].ToString()); + item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); + item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); + item.SalePrice = Convert.ToInt32(reader["salePrice"]); + item.SaleType = Convert.ToByte(reader["saleType"]); + item.CreationDate = Convert.ToInt32(reader["creationDate"]); + item.GroupID = new UUID(reader["groupID"].ToString()); + item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); + item.Flags = Convert.ToUInt32(reader["flags"]); - using (IDbCommand cmd = - database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param)) - { - cmd.ExecuteNonQuery(); - } + return item; } catch (SqlException e) { - m_log.Error(e.ToString()); + m_log.Error("[INVENTORY DB] Error reading inventory item :" + e.Message); } + + return null; } /// - /// Delete an inventory folder + /// Delete a folder in inventory databasae /// - /// Id of folder to delete - public void deleteInventoryFolder(UUID folderID) + /// the folder UUID + /// connection to database + private void DeleteOneFolder(UUID folderID, SqlConnection connection) { - // lock (database) + try { - List subFolders = getFolderHierarchy(folderID); - - //Delete all sub-folders - foreach (InventoryFolderBase f in subFolders) + using (SqlCommand command = new SqlCommand("DELETE FROM inventoryfolders WHERE folderID=@folderID", connection)) { - deleteOneFolder(f.ID); - deleteItemsInFolder(f.ID); - } + command.Parameters.Add(database.CreateParameter("folderID", folderID)); - //Delete the actual row - deleteOneFolder(folderID); - deleteItemsInFolder(folderID); + command.ExecuteNonQuery(); + } + } + catch (SqlException e) + { + m_log.Error("[INVENTORY DB] Error deleting folder :" + e.Message); } } + #endregion } } diff --git a/OpenSim/Data/MSSQL/MSSQLLogData.cs b/OpenSim/Data/MSSQL/MSSQLLogData.cs index b284c02..a617cea 100644 --- a/OpenSim/Data/MSSQL/MSSQLLogData.cs +++ b/OpenSim/Data/MSSQL/MSSQLLogData.cs @@ -26,6 +26,7 @@ */ using System; +using System.Data.SqlClient; using System.Reflection; using System.Collections.Generic; using System.Data; @@ -39,6 +40,8 @@ namespace OpenSim.Data.MSSQL /// internal class MSSQLLogData : ILogDataPlugin { + private const string _migrationStore = "LogStore"; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// @@ -48,7 +51,7 @@ namespace OpenSim.Data.MSSQL public void Initialise() { - m_log.Info("[MSSQLLogData]: " + Name + " cannot be default-initialized!"); + m_log.Info("[LOG DB]: " + Name + " cannot be default-initialized!"); throw new PluginNotInitialisedException (Name); } @@ -57,18 +60,37 @@ namespace OpenSim.Data.MSSQL /// public void Initialise(string connect) { - // TODO: do something with the connect string - 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); + if (string.IsNullOrEmpty(connect)) + { + database = new MSSQLManager(connect); + } + else + { + // TODO: do something with the connect string + 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); + } + + //TODO when can this be removed + TestTable(); + + //Updating mechanisme + database.CheckMigration(_migrationStore); + } + /// + /// Can be removed someday!!! + /// + private void TestTable() + { using (IDbCommand cmd = database.Query("select top 1 * from logs", new Dictionary())) { try @@ -80,9 +102,38 @@ namespace OpenSim.Data.MSSQL database.ExecuteResourceSql("Mssql-logs.sql"); } } + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'")) + { + //Special for Migrations to create backword compatible + try + { + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + catch + { + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); - } + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)"; + cmd.ExecuteNonQuery(); + } + } + } + /// /// Saves a log item to the database /// @@ -95,13 +146,28 @@ namespace OpenSim.Data.MSSQL public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage) { - try - { - database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage); - } - catch + string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES "; + sql += "(@target, @server, @method, @arguments, @priority, @message);"; + + using (AutoClosingSqlCommand command = database.Query(sql)) { - // it didn't log, don't worry about it + command.Parameters.Add(database.CreateParameter("server", serverDaemon)); + command.Parameters.Add(database.CreateParameter("target",target)); + command.Parameters.Add(database.CreateParameter("method", methodCall)); + command.Parameters.Add(database.CreateParameter("arguments", arguments)); + command.Parameters.Add(database.CreateParameter("priority", priority.ToString())); + command.Parameters.Add(database.CreateParameter("message", logMessage)); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + //Are we not in a loop here + //m_log.Error(e.ToString()); + Console.WriteLine("[LOG DB] Error logging : " + e.Message); + } } } @@ -119,7 +185,7 @@ namespace OpenSim.Data.MSSQL /// public void Dispose() { - // Do nothing. + database = null; } /// diff --git a/OpenSim/Data/MSSQL/MSSQLManager.cs b/OpenSim/Data/MSSQL/MSSQLManager.cs index abbcddf..b3e9b6e 100644 --- a/OpenSim/Data/MSSQL/MSSQLManager.cs +++ b/OpenSim/Data/MSSQL/MSSQLManager.cs @@ -33,7 +33,6 @@ using System.IO; using System.Reflection; using OpenMetaverse; using log4net; -using OpenSim.Framework; namespace OpenSim.Data.MSSQL { @@ -83,49 +82,7 @@ namespace OpenSim.Data.MSSQL return conn; } - //private DataTable createRegionsTable() - //{ - // DataTable regions = new DataTable("regions"); - - // createCol(regions, "regionHandle", typeof (ulong)); - // createCol(regions, "regionName", typeof (String)); - // createCol(regions, "uuid", typeof (String)); - - // createCol(regions, "regionRecvKey", typeof (String)); - // createCol(regions, "regionSecret", typeof (String)); - // createCol(regions, "regionSendKey", typeof (String)); - - // createCol(regions, "regionDataURI", typeof (String)); - // createCol(regions, "serverIP", typeof (String)); - // createCol(regions, "serverPort", typeof (String)); - // createCol(regions, "serverURI", typeof (String)); - - - // createCol(regions, "locX", typeof (uint)); - // createCol(regions, "locY", typeof (uint)); - // createCol(regions, "locZ", typeof (uint)); - - // createCol(regions, "eastOverrideHandle", typeof (ulong)); - // createCol(regions, "westOverrideHandle", typeof (ulong)); - // createCol(regions, "southOverrideHandle", typeof (ulong)); - // createCol(regions, "northOverrideHandle", typeof (ulong)); - - // createCol(regions, "regionAssetURI", typeof (String)); - // createCol(regions, "regionAssetRecvKey", typeof (String)); - // createCol(regions, "regionAssetSendKey", typeof (String)); - - // createCol(regions, "regionUserURI", typeof (String)); - // createCol(regions, "regionUserRecvKey", typeof (String)); - // createCol(regions, "regionUserSendKey", typeof (String)); - - // createCol(regions, "regionMapTexture", typeof (String)); - // createCol(regions, "serverHttpPort", typeof (String)); - // createCol(regions, "serverRemotingPort", typeof (uint)); - - // // Add in contraints - // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]}; - // return regions; - //} + #region Obsolete functions, can be removed! /// /// @@ -133,6 +90,7 @@ namespace OpenSim.Data.MSSQL /// /// /// + [Obsolete("Do not use!")] protected static void createCol(DataTable dt, string name, Type type) { DataColumn col = new DataColumn(name, type); @@ -144,6 +102,7 @@ namespace OpenSim.Data.MSSQL /// /// /// + [Obsolete("Do not use!")] protected static string defineTable(DataTable dt) { string sql = "create table " + dt.TableName + "("; @@ -167,34 +126,34 @@ namespace OpenSim.Data.MSSQL return sql; } + #endregion + /// /// Type conversion function /// /// a type /// a sqltype /// this is something we'll need to implement for each db slightly differently. + [Obsolete("Used by a obsolete methods")] public static string SqlType(Type type) { if (type == typeof(String)) { return "varchar(255)"; } - else if (type == typeof(Int32)) + if (type == typeof(Int32)) { return "integer"; } - else if (type == typeof(Double)) + if (type == typeof(Double)) { return "float"; } - else if (type == typeof(Byte[])) + if (type == typeof(Byte[])) { return "image"; } - else - { - return "varchar(255)"; - } + return "varchar(255)"; } /// @@ -224,14 +183,22 @@ namespace OpenSim.Data.MSSQL { return SqlDbType.VarChar; } + if (type == typeof(sbyte)) + { + return SqlDbType.TinyInt; + } if (type == typeof(Byte[])) { return SqlDbType.Image; } - if (type == typeof(uint)) + if (type == typeof(uint) || type == typeof(ushort)) { return SqlDbType.Int; } + if (type == typeof(ulong)) + { + return SqlDbType.BigInt; + } return SqlDbType.VarChar; } @@ -282,6 +249,9 @@ namespace OpenSim.Data.MSSQL //Tweak so we dont always have to add @ sign if (!parameterName.StartsWith("@")) parameterName = "@" + parameterName; + //HACK if object is null, it is turned into a string, there are no nullable type till now + if (parameterObject == null) parameterObject = ""; + SqlParameter parameter = new SqlParameter(parameterName, DbtypeFromType(parameterObject.GetType())); if (parameterOut) @@ -298,6 +268,7 @@ namespace OpenSim.Data.MSSQL } private static readonly Dictionary emptyDictionary = new Dictionary(); + internal AutoClosingSqlCommand Query(string sql) { return Query(sql, emptyDictionary); @@ -322,238 +293,38 @@ namespace OpenSim.Data.MSSQL } /// - /// Runs a database reader object and returns a region row - /// - /// An active database reader - /// A region row - public RegionProfileData getRegionRow(IDataReader reader) - { - RegionProfileData regionprofile = new RegionProfileData(); - - if (reader.Read()) - { - // Region Main - regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]); - regionprofile.regionName = (string)reader["regionName"]; - regionprofile.UUID = new UUID((string)reader["uuid"]); - - // Secrets - regionprofile.regionRecvKey = (string)reader["regionRecvKey"]; - regionprofile.regionSecret = (string)reader["regionSecret"]; - regionprofile.regionSendKey = (string)reader["regionSendKey"]; - - // Region Server - regionprofile.regionDataURI = (string)reader["regionDataURI"]; - regionprofile.regionOnline = false; // Needs to be pinged before this can be set. - regionprofile.serverIP = (string)reader["serverIP"]; - regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]); - regionprofile.serverURI = (string)reader["serverURI"]; - regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]); - regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]); - - - // Location - regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]); - regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]); - regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]); - - // Neighbours - 0 = No Override - regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]); - regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]); - regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]); - regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]); - - // Assets - regionprofile.regionAssetURI = (string)reader["regionAssetURI"]; - regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"]; - regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"]; - - // Userserver - regionprofile.regionUserURI = (string)reader["regionUserURI"]; - regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"]; - regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"]; - regionprofile.owner_uuid = new UUID((string) reader["owner_uuid"]); - // World Map Addition - string tempRegionMap = reader["regionMapTexture"].ToString(); - if (tempRegionMap != String.Empty) - { - regionprofile.regionMapTextureID = new UUID(tempRegionMap); - } - else - { - regionprofile.regionMapTextureID = new UUID(); - } - } - else - { - reader.Close(); - throw new Exception("No rows to return"); - } - return regionprofile; - } - - /// - /// Reads a user profile from an active data reader + /// Runs a query with protection against SQL Injection by using parameterised input. /// - /// An active database reader - /// A user profile - public UserProfileData readUserRow(IDataReader reader) + /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y + /// A parameter - use createparameter to create parameter + /// + internal AutoClosingSqlCommand Query(string sql, SqlParameter sqlParameter) { - UserProfileData retval = new UserProfileData(); + SqlCommand dbcommand = DatabaseConnection().CreateCommand(); + dbcommand.CommandText = sql; + dbcommand.Parameters.Add(sqlParameter); - if (reader.Read()) - { - retval.ID = new UUID((string)reader["UUID"]); - retval.FirstName = (string)reader["username"]; - retval.SurName = (string)reader["lastname"]; - - retval.PasswordHash = (string)reader["passwordHash"]; - retval.PasswordSalt = (string)reader["passwordSalt"]; - - retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString()); - retval.HomeLocation = new Vector3( - Convert.ToSingle(reader["homeLocationX"].ToString()), - Convert.ToSingle(reader["homeLocationY"].ToString()), - Convert.ToSingle(reader["homeLocationZ"].ToString())); - retval.HomeLookAt = new Vector3( - Convert.ToSingle(reader["homeLookAtX"].ToString()), - Convert.ToSingle(reader["homeLookAtY"].ToString()), - Convert.ToSingle(reader["homeLookAtZ"].ToString())); - - retval.Created = Convert.ToInt32(reader["created"].ToString()); - retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); - - retval.UserInventoryURI = (string)reader["userInventoryURI"]; - retval.UserAssetURI = (string)reader["userAssetURI"]; - - retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); - retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); - - retval.AboutText = (string)reader["profileAboutText"]; - retval.FirstLifeAboutText = (string)reader["profileFirstText"]; - - retval.Image = new UUID((string)reader["profileImage"]); - retval.FirstLifeImage = new UUID((string)reader["profileFirstImage"]); - retval.WebLoginKey = new UUID((string)reader["webLoginKey"]); - } - else - { - return null; - } - return retval; + return new AutoClosingSqlCommand(dbcommand); } /// - /// Reads an agent row from a database reader + /// Checks if we need to do some migrations to the database /// - /// An active database reader - /// A user session agent - public UserAgentData readAgentRow(IDataReader reader) + /// migrationStore. + public void CheckMigration(string migrationStore) { - UserAgentData retval = new UserAgentData(); - - if (reader.Read()) + using (SqlConnection connection = DatabaseConnection()) { - // Agent IDs - retval.ProfileID = new UUID((string)reader["UUID"]); - retval.SessionID = new UUID((string)reader["sessionID"]); - retval.SecureSessionID = new UUID((string)reader["secureSessionID"]); - - // Agent Who? - retval.AgentIP = (string)reader["agentIP"]; - retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString()); - retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0; - - // Login/Logout times (UNIX Epoch) - retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString()); - retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); - - // Current position - retval.Region = (string)reader["currentRegion"]; - retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString()); - Vector3 tmp_v; - Vector3.TryParse((string)reader["currentPos"], out tmp_v); - retval.Position = tmp_v; + Assembly assem = GetType().Assembly; + MSSQLMigration migration = new MSSQLMigration(connection, assem, migrationStore); - } - else - { - return null; - } - return retval; - } + migration.Update(); - /// - /// - /// - /// - /// - public AssetBase getAssetRow(IDataReader reader) - { - AssetBase asset = new AssetBase(); - if (reader.Read()) - { - // Region Main - asset = new AssetBase(); - asset.Data = (byte[])reader["data"]; - asset.Description = (string)reader["description"]; - asset.FullID = new UUID((string)reader["id"]); - asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false; - asset.Name = (string)reader["name"]; - asset.Type = Convert.ToSByte(reader["assetType"]); + connection.Close(); } - else - { - return null; // throw new Exception("No rows to return"); - } - return asset; } - - /// - /// Inserts a new row into the log database - /// - /// The daemon which triggered this event - /// Who were we operating on when this occured (region UUID, user UUID, etc) - /// The method call where the problem occured - /// The arguments passed to the method - /// How critical is this? - /// Extra message info - /// Saved successfully? - public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority, - string logMessage) - { - string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES "; - sql += "(@target, @server, @method, @arguments, @priority, @message);"; - - Dictionary parameters = new Dictionary(); - parameters["server"] = serverDaemon; - parameters["target"] = target; - parameters["method"] = methodCall; - parameters["arguments"] = arguments; - parameters["priority"] = priority.ToString(); - parameters["message"] = logMessage; - - bool returnval = false; - - using (IDbCommand result = Query(sql, parameters)) - { - try - { - - if (result.ExecuteNonQuery() == 1) - returnval = true; - - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return false; - } - } - - return returnval; - } + #region Old Testtable functions /// /// Execute a SQL statement stored in a resource, as a string @@ -561,13 +332,12 @@ namespace OpenSim.Data.MSSQL /// the ressource string public void ExecuteResourceSql(string name) { - using (IDbCommand cmd = Query(getResourceString(name), new Dictionary())) + using (IDbCommand cmd = Query(getResourceString(name), new Dictionary())) { cmd.ExecuteNonQuery(); } } - /// /// Given a list of tables, return the version of the tables, as seen in the database /// @@ -622,6 +392,8 @@ namespace OpenSim.Data.MSSQL throw new Exception(string.Format("Resource '{0}' was not found", name)); } + #endregion + /// /// Returns the version of this DB provider /// @@ -636,63 +408,5 @@ namespace OpenSim.Data.MSSQL string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, dllVersion.Revision); } - - public bool insertAgentRow(UserAgentData agentdata) - { - string sql = @" - -IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID) - BEGIN - UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos - WHERE UUID = @UUID - END -ELSE - BEGIN - INSERT INTO - agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES - (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos) - END -"; - - Dictionary parameters = new Dictionary(); - - parameters["@UUID"] = agentdata.ProfileID.ToString(); - parameters["@sessionID"] = agentdata.SessionID.ToString(); - parameters["@secureSessionID"] = agentdata.SecureSessionID.ToString(); - parameters["@agentIP"] = agentdata.AgentIP.ToString(); - parameters["@agentPort"] = agentdata.AgentPort.ToString(); - parameters["@agentOnline"] = (agentdata.AgentOnline == true) ? "1" : "0"; - parameters["@loginTime"] = agentdata.LoginTime.ToString(); - parameters["@logoutTime"] = agentdata.LogoutTime.ToString(); - parameters["@currentRegion"] = agentdata.Region.ToString(); - parameters["@currentHandle"] = agentdata.Handle.ToString(); - parameters["@currentPos"] = "<" + ((int)agentdata.Position.X).ToString() + "," + ((int)agentdata.Position.Y).ToString() + "," + ((int)agentdata.Position.Z).ToString() + ">"; - - - using (IDbCommand result = Query(sql, parameters)) - { - result.Transaction = result.Connection.BeginTransaction(IsolationLevel.Serializable); - try - { - if (result.ExecuteNonQuery() > 0) - { - result.Transaction.Commit(); - return true; - } - else - { - result.Transaction.Rollback(); - return false; - } - } - catch (Exception e) - { - result.Transaction.Rollback(); - m_log.Error(e.ToString()); - return false; - } - } - - } } } diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/MSSQL/MSSQLRegionData.cs index b942f00..8b808d3 100644 --- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs +++ b/OpenSim/Data/MSSQL/MSSQLRegionData.cs @@ -44,14 +44,19 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLRegionDataStore : IRegionDataStore { + private const string _migrationStore = "RegionStore"; + // private static FileSystemDataStore Instance = new FileSystemDataStore(); private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + /// + /// The database manager + /// private MSSQLManager _Database; -// private const string _PrimSelect = "SELECT * FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID)"; -// private const string _ShapeSelect = "SELECT * FROM PRIMSHAPES WHERE UUID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID))"; -// private const string _ItemsSelect = "SELECT * FROM PRIMITEMS WHERE primID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID))"; + /// + /// Const for the prim store.. + /// private const string _PrimSelect = "SELECT * FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID))"; private const string _ShapeSelect = "SELECT * FROM PRIMSHAPES WHERE UUID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))"; private const string _ItemsSelect = "SELECT * FROM PRIMITEMS WHERE primID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))"; @@ -81,31 +86,14 @@ namespace OpenSim.Data.MSSQL string settingUserId = iniFile.ParseFileReadValue("user_id"); string settingPassword = iniFile.ParseFileReadValue("password"); - _Database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); - - - SqlConnectionStringBuilder conBuilder = new SqlConnectionStringBuilder(); - conBuilder.DataSource = settingDataSource; - conBuilder.InitialCatalog = settingInitialCatalog; - conBuilder.PersistSecurityInfo = Convert.ToBoolean(settingPersistSecurityInfo); - conBuilder.UserID = settingUserId; - conBuilder.Password = settingPassword; - conBuilder.ApplicationName = Assembly.GetEntryAssembly().Location; - - connectionString = conBuilder.ToString(); + _Database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); } //Migration settings - Assembly assem = GetType().Assembly; + _Database.CheckMigration(_migrationStore); using (SqlConnection connection = _Database.DatabaseConnection()) { - MSSQLMigration m = new MSSQLMigration(connection, assem, "RegionStore"); - - m.Update(); - //Create Dataset. Not filled!!! _PrimsDataSet = new DataSet("primsdata"); @@ -243,7 +231,7 @@ namespace OpenSim.Data.MSSQL catch (Exception e) { _Log.Error("[REGION DB]: Failed create prim object, exception and data follows"); - _Log.Info("[REGION DB]: " + e.ToString()); + _Log.Info("[REGION DB]: " + e.Message); foreach (DataColumn col in prims.Columns) { _Log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]); @@ -259,71 +247,76 @@ namespace OpenSim.Data.MSSQL #region Experimental -// -// //Get all prims -// string sql = "select * from prims where RegionUUID = @RegionUUID"; -// -// using (AutoClosingSqlCommand cmdPrims = _Database.Query(sql)) -// { -// cmdPrims.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString()); -// using (SqlDataReader readerPrims = cmdPrims.ExecuteReader()) -// { -// while (readerPrims.Read()) -// { -// string uuid = (string)readerPrims["UUID"]; -// string objID = (string)readerPrims["SceneGroupID"]; -// SceneObjectPart prim = buildPrim(readerPrims); -// -// //Setting default shape, will change shape ltr -// prim.Shape = PrimitiveBaseShape.Default; -// -// //Load inventory items of prim -// //LoadItems(prim); -// -// if (uuid == objID) -// { -// SceneObjectGroup group = new SceneObjectGroup(); -// -// group.AddPart(prim); -// group.RootPart = prim; -// -// createdObjects.Add(group.UUID, group); -// retvals.Add(group); -// } -// else -// { -// createdObjects[new UUID(objID)].AddPart(prim); -// } -// } -// } -// } -// m_log.Info("[REGION DB]: Loaded " + retvals.Count + " prim objects for region: " + regionUUID); -// -// //Find all shapes related with prims -// sql = "select * from primshapes"; -// using (AutoClosingSqlCommand cmdShapes = _Database.Query(sql)) -// { -// using (SqlDataReader readerShapes = cmdShapes.ExecuteReader()) -// { -// while (readerShapes.Read()) -// { -// UUID UUID = new UUID((string) readerShapes["UUID"]); -// -// foreach (SceneObjectGroup objectGroup in createdObjects.Values) -// { -// if (objectGroup.Children.ContainsKey(UUID)) -// { -// objectGroup.Children[UUID].Shape = buildShape(readerShapes); -// } -// } -// } -// } -// } -// return retvals; + // + // //Get all prims + // string sql = "select * from prims where RegionUUID = @RegionUUID"; + // + // using (AutoClosingSqlCommand cmdPrims = _Database.Query(sql)) + // { + // cmdPrims.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString()); + // using (SqlDataReader readerPrims = cmdPrims.ExecuteReader()) + // { + // while (readerPrims.Read()) + // { + // string uuid = (string)readerPrims["UUID"]; + // string objID = (string)readerPrims["SceneGroupID"]; + // SceneObjectPart prim = buildPrim(readerPrims); + // + // //Setting default shape, will change shape ltr + // prim.Shape = PrimitiveBaseShape.Default; + // + // //Load inventory items of prim + // //LoadItems(prim); + // + // if (uuid == objID) + // { + // SceneObjectGroup group = new SceneObjectGroup(); + // + // group.AddPart(prim); + // group.RootPart = prim; + // + // createdObjects.Add(group.UUID, group); + // retvals.Add(group); + // } + // else + // { + // createdObjects[new UUID(objID)].AddPart(prim); + // } + // } + // } + // } + // m_log.Info("[REGION DB]: Loaded " + retvals.Count + " prim objects for region: " + regionUUID); + // + // //Find all shapes related with prims + // sql = "select * from primshapes"; + // using (AutoClosingSqlCommand cmdShapes = _Database.Query(sql)) + // { + // using (SqlDataReader readerShapes = cmdShapes.ExecuteReader()) + // { + // while (readerShapes.Read()) + // { + // UUID UUID = new UUID((string) readerShapes["UUID"]); + // + // foreach (SceneObjectGroup objectGroup in createdObjects.Values) + // { + // if (objectGroup.Children.ContainsKey(UUID)) + // { + // objectGroup.Children[UUID].Shape = buildShape(readerShapes); + // } + // } + // } + // } + // } + // return retvals; #endregion } + /// + /// Stores all object's details apart from inventory + /// + /// + /// public void StoreObject(SceneObjectGroup obj, UUID regionUUID) { //Retrieve all values of current region, and current scene/or prims @@ -375,10 +368,6 @@ namespace OpenSim.Data.MSSQL } } } - else - { - // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID); - } } //Save changes @@ -430,7 +419,7 @@ namespace OpenSim.Data.MSSQL //TODO add index on PrimID in DB, if not already exist using (AutoClosingSqlCommand cmd = _Database.Query("DELETE PRIMITEMS WHERE primID = @primID")) { - cmd.Parameters.AddWithValue("@primID", primID.ToString()); + cmd.Parameters.Add(_Database.CreateParameter("@primID", primID)); cmd.ExecuteNonQuery(); } @@ -470,7 +459,7 @@ namespace OpenSim.Data.MSSQL using (SqlDataReader reader = cmd.ExecuteReader()) { - int rev = 0; + int rev; if (reader.Read()) { MemoryStream str = new MemoryStream((byte[])reader["Heightfield"]); @@ -509,22 +498,21 @@ namespace OpenSim.Data.MSSQL string sql = "delete from terrain where RegionUUID=@RegionUUID"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { - cmd.Parameters.AddWithValue("@RegionUUID", regionID.ToString()); + cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID)); cmd.ExecuteNonQuery(); } - sql = "insert into terrain(RegionUUID, Revision, Heightfield)" + - " values(@RegionUUID, @Revision, @Heightfield)"; + sql = "insert into terrain(RegionUUID, Revision, Heightfield) values(@RegionUUID, @Revision, @Heightfield)"; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { - cmd.Parameters.AddWithValue("@RegionUUID", regionID.ToString()); - cmd.Parameters.AddWithValue("@Revision", revision); - cmd.Parameters.AddWithValue("@Heightfield", serializeTerrain(terrain)); + cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID)); + cmd.Parameters.Add(_Database.CreateParameter("@Revision", revision)); + cmd.Parameters.Add(_Database.CreateParameter("@Heightfield", serializeTerrain(terrain))); cmd.ExecuteNonQuery(); } - _Log.Info("[REGION DB]: Stored terrain revision r" + revision); + _Log.Info("[REGION DB]: Stored terrain revision r " + revision); } /// @@ -541,12 +529,13 @@ namespace OpenSim.Data.MSSQL //Retrieve all land data from region using (AutoClosingSqlCommand cmdLandData = _Database.Query(sql)) { - cmdLandData.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString()); + cmdLandData.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionUUID)); using (SqlDataReader readerLandData = cmdLandData.ExecuteReader()) { while (readerLandData.Read()) { + // LandData data = buildLandData(readerLandData); landDataForRegion.Add(buildLandData(readerLandData)); } } @@ -558,7 +547,7 @@ namespace OpenSim.Data.MSSQL sql = "select * from landaccesslist where LandUUID = @LandUUID"; using (AutoClosingSqlCommand cmdAccessList = _Database.Query(sql)) { - cmdAccessList.Parameters.AddWithValue("@LandUUID", landData.GlobalID.ToString()); + cmdAccessList.Parameters.Add(_Database.CreateParameter("@LandUUID", landData.GlobalID)); using (SqlDataReader readerAccessList = cmdAccessList.ExecuteReader()) { while (readerAccessList.Read()) @@ -643,7 +632,7 @@ VALUES RegionSettings regionSettings; using (AutoClosingSqlCommand cmd = _Database.Query(sql)) { - cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID.ToString())); + cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID)); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) @@ -674,7 +663,7 @@ VALUES public void StoreRegionSettings(RegionSettings regionSettings) { //Little check if regionUUID already exist in DB - string regionUUID = null; + string regionUUID; using (AutoClosingSqlCommand cmd = _Database.Query("SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID")) { cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID)); @@ -724,7 +713,7 @@ VALUES { DataTable dbItems = _PrimsDataSet.Tables["primitems"]; - String sql = String.Format("primID = '{0}'", prim.UUID.ToString()); + String sql = String.Format("primID = '{0}'", prim.UUID); DataRow[] dbItemRows = dbItems.Select(sql); IList inventory = new List(); @@ -1446,6 +1435,12 @@ VALUES #endregion + /// + /// Retrieves the prims data for region. + /// + /// The region UUID. + /// The scene group ID. + /// The prim ID. private void RetrievePrimsDataForRegion(UUID regionUUID, UUID sceneGroupID, string primID) { using (SqlConnection connection = _Database.DatabaseConnection()) @@ -1482,6 +1477,9 @@ VALUES } } + /// + /// Commits the dataset. + /// private void CommitDataSet() { lock (_PrimsDataSet) @@ -1513,6 +1511,10 @@ VALUES } } + /// + /// Create commands for a dataadapter. + /// + /// The data adapter. private static void SetupCommands(SqlDataAdapter dataAdapter) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter); diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs index 9118012..6352cce 100644 --- a/OpenSim/Data/MSSQL/MSSQLUserData.cs +++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs @@ -26,6 +26,7 @@ */ using System; +using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; @@ -41,6 +42,8 @@ namespace OpenSim.Data.MSSQL /// public class MSSQLUserData : UserDataBase { + private const string _migrationStore = "UserStore"; + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// @@ -52,28 +55,35 @@ namespace OpenSim.Data.MSSQL private string m_usersTableName; private string m_userFriendsTableName; - public override void Initialise() + override public void Initialise() { m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); + throw new PluginNotInitialisedException(Name); } /// /// Loads and initialises the MSSQL storage plugin /// - /// TODO: do something with the connect string instead of ignoring it. + /// connectionstring /// use mssql_connection.ini override public void Initialise(string connect) { - // TODO: do something with the connect string instead of - // ignoring it. - IniFile iniFile = new IniFile("mssql_connection.ini"); - string settingDataSource = iniFile.ParseFileReadValue("data_source"); - string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); - string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); - string settingUserId = iniFile.ParseFileReadValue("user_id"); - string settingPassword = iniFile.ParseFileReadValue("password"); + + if (string.IsNullOrEmpty(connect)) + { + database = new MSSQLManager(connect); + } + else + { + string settingDataSource = iniFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); + string settingUserId = iniFile.ParseFileReadValue("user_id"); + string settingPassword = iniFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } m_usersTableName = iniFile.ParseFileReadValue("userstablename"); if (m_usersTableName == null) @@ -93,23 +103,22 @@ namespace OpenSim.Data.MSSQL m_agentsTableName = "agents"; } - database = - new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, - settingPassword); - + //TODO this can be removed at one time!!!!! TestTables(); + + //Check migration on DB + database.CheckMigration(_migrationStore); } - public override void Dispose () {} + override public void Dispose() { } /// - /// + /// Can be deleted at one time! /// /// - private bool TestTables() + private void TestTables() { - - using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary())) + using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName)) { try { @@ -145,300 +154,216 @@ namespace OpenSim.Data.MSSQL } } - return true; - } - - /// - /// Searches the database for a specified user profile by name components - /// - /// The first part of the account name - /// The second part of the account name - /// A user profile - override public UserProfileData GetUserByName(string user, string last) - { - try + using (IDbCommand cmd = database.Query("select top 1 * from avatarappearance", new Dictionary())) { - Dictionary param = new Dictionary(); - param["first"] = user; - param["second"] = last; - - using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param)) - using (IDataReader reader = result.ExecuteReader()) + try { - return database.readUserRow(reader); + cmd.ExecuteNonQuery(); + } + catch + { + database.ExecuteResourceSql("AvatarAppearance.sql"); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } - - #region User Friends List Data - - /// - /// Add a new friend in the friendlist - /// - /// UUID of the friendlist owner - /// Friend's UUID - /// Permission flag - override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms) - { - int dtvalue = Util.UnixTimeSinceEpoch(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - param["@friendPerms"] = perms.ToString(); - param["@datetimestamp"] = dtvalue.ToString(); - try + //Special for Migrations + using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = 'UserStore'")) { - using (IDbCommand adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@ownerID,@friendID,@friendPerms,@datetimestamp)", - param)) + try { - adder.ExecuteNonQuery(); + bool insert = true; + using (SqlDataReader reader = cmd.ExecuteReader()) + { + if (reader.Read()) insert = false; + } + if (insert) + { + cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)"; + cmd.ExecuteNonQuery(); + } } - - using (IDbCommand adder = - database.Query( - "INSERT INTO " + m_userFriendsTableName + " " + - "(ownerID,friendID,friendPerms,datetimestamp) " + - "VALUES " + - "(@friendID,@ownerID,@friendPerms,@datetimestamp)", - param)) + catch { - adder.ExecuteNonQuery(); + //No migrations table + //HACK create one and add data + cmd.CommandText = "create table migrations(name varchar(100), version int)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)"; + cmd.ExecuteNonQuery(); + + cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)"; + cmd.ExecuteNonQuery(); } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return; - } + return; } + #region User table methods + /// - /// Remove an friend from the friendlist + /// Searches the database for a specified user profile by name components /// - /// UUID of the friendlist owner - /// UUID of the not-so-friendly user to remove from the list - override public void RemoveUserFriend(UUID friendlistowner, UUID friend) + /// The first part of the account name + /// The second part of the account name + /// A user profile + override public UserProfileData GetUserByName(string user, string last) { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - - - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second")) { - using (IDbCommand updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID", - param)) + command.Parameters.Add(database.CreateParameter("first", user)); + command.Parameters.Add(database.CreateParameter("second", last)); + + try { - updater.ExecuteNonQuery(); + using (SqlDataReader reader = command.ExecuteReader()) + { + return ReadUserRow(reader); + } } - - using (IDbCommand updater = - database.Query( - "delete from " + m_userFriendsTableName + " where ownerID = @friendID and friendID = @ownerID", - param)) + catch (Exception e) { - updater.ExecuteNonQuery(); + m_log.Error("[USER DB] Error getting user profile, error: " + e.Message); + return null; } - - } - catch (Exception e) - { - m_log.Error(e.ToString()); } } /// - /// Update friendlist permission flag for a friend + /// See IUserDataPlugin /// - /// UUID of the friendlist owner - /// UUID of the friend - /// new permission flag - override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) + /// + /// + override public UserProfileData GetUserByUUID(UUID uuid) { - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - param["@friendID"] = friend.ToString(); - param["@friendPerms"] = perms.ToString(); - - - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid")) { - using (IDbCommand updater = - database.Query( - "update " + m_userFriendsTableName + - " SET friendPerms = @friendPerms " + - "where ownerID = @ownerID and friendID = @friendID", - param)) + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + + try { - updater.ExecuteNonQuery(); + using (IDataReader reader = command.ExecuteReader()) + { + return ReadUserRow(reader); + } + } + catch (Exception e) + { + m_log.Error("[USER DB] Error getting user profile by UUID, error: " + e.Message); + return null; } - } - catch (Exception e) - { - m_log.Error(e.ToString()); } } + /// - /// Get (fetch?) the user's friendlist + /// Creates a new users profile /// - /// UUID of the friendlist owner - /// Friendlist list - override public List GetUserFriendList(UUID friendlistowner) + /// The user profile to create + override public void AddNewUserProfile(UserProfileData user) { - List Lfli = new List(); - - Dictionary param = new Dictionary(); - param["@ownerID"] = friendlistowner.ToString(); - try { - //Left Join userfriends to itself - using (IDbCommand result = - database.Query( - "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + - " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", - param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - FriendListItem fli = new FriendListItem(); - fli.FriendListOwner = new UUID((string)reader["ownerID"]); - fli.Friend = new UUID((string)reader["friendID"]); - fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]); - - // This is not a real column in the database table, it's a joined column from the opposite record - fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); - - Lfli.Add(fli); - } - } + InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, + user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, + user.HomeLocation.Z, + user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, + user.LastLogin, user.UserInventoryURI, user.UserAssetURI, + user.CanDoMask, user.WantDoMask, + user.AboutText, user.FirstLifeAboutText, user.Image, + user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID, + user.GodLevel, user.UserFlags, user.CustomType, user.Partner); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error adding new profile, error: " + e.Message); } - - return Lfli; - } - - #endregion - - /// - /// STUB ! Update current region - /// - /// avatar uuid - /// region uuid - /// region handle - override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle) - { - //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); } /// - /// + /// update a user profile /// - /// - /// + /// the profile to update /// - override public List GeneratePickerResults(UUID queryID, string query) + override public bool UpdateUserProfile(UserProfileData user) { - List returnlist = new List(); - string[] querysplit; - querysplit = query.Split(' '); - if (querysplit.Length == 2) + using (AutoClosingSqlCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " + + "username = @username, " + + "lastname = @lastname," + + "passwordHash = @passwordHash," + + "passwordSalt = @passwordSalt," + + "homeRegion = @homeRegion," + + "homeLocationX = @homeLocationX," + + "homeLocationY = @homeLocationY," + + "homeLocationZ = @homeLocationZ," + + "homeLookAtX = @homeLookAtX," + + "homeLookAtY = @homeLookAtY," + + "homeLookAtZ = @homeLookAtZ," + + "created = @created," + + "lastLogin = @lastLogin," + + "userInventoryURI = @userInventoryURI," + + "userAssetURI = @userAssetURI," + + "profileCanDoMask = @profileCanDoMask," + + "profileWantDoMask = @profileWantDoMask," + + "profileAboutText = @profileAboutText," + + "profileFirstText = @profileFirstText," + + "profileImage = @profileImage," + + "profileFirstImage = @profileFirstImage, " + + "webLoginKey = @webLoginKey, " + + "homeRegionID = @homeRegionID, " + + "userFlags = @userFlags, " + + "godLevel = @godLevel, " + + "customType = @customType, " + + "partner = @partner where " + + "UUID = @keyUUUID;")) { - try - { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - param["second"] = querysplit[1]; + command.Parameters.Add(database.CreateParameter("uuid", user.ID)); + command.Parameters.Add(database.CreateParameter("username", user.FirstName)); + command.Parameters.Add(database.CreateParameter("lastname", user.SurName)); + command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash)); + command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt)); + command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion)); + command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X)); + command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y)); + command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z)); + command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X)); + command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y)); + command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z)); + command.Parameters.Add(database.CreateParameter("created", user.Created)); + command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin)); + command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI)); + command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI)); + command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask)); + command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask)); + command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText)); + command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText)); + command.Parameters.Add(database.CreateParameter("profileImage", user.Image)); + command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage)); + command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey)); + // + command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID)); + command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags)); + command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel)); + command.Parameters.Add(database.CreateParameter("customType", user.CustomType)); + command.Parameters.Add(database.CreateParameter("partner", user.Partner)); + command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID)); - using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - } - } - else if (querysplit.Length == 1) - { try { - Dictionary param = new Dictionary(); - param["first"] = querysplit[0]; - - using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @first", param)) - using (IDataReader reader = result.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string)reader["UUID"]); - user.firstName = (string)reader["username"]; - user.lastName = (string)reader["lastname"]; - returnlist.Add(user); - } - } + int affected = command.ExecuteNonQuery(); + return (affected != 0); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating profile, error: " + e.Message); } } - return returnlist; + return false; } - /// - /// See IUserDataPlugin - /// - /// - /// - override public UserProfileData GetUserByUUID(UUID uuid) - { - try - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); + #endregion - using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) - { - return database.readUserRow(reader); - } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; - } - } + #region Agent table methods /// /// Returns a user session searching by name @@ -469,258 +394,182 @@ namespace OpenSim.Data.MSSQL /// The users session override public UserAgentData GetAgentByUUID(UUID uuid) { - try + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid")) { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - using (IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param)) - using (IDataReader reader = result.ExecuteReader()) + command.Parameters.Add(database.CreateParameter("uuid", uuid)); + try { - return database.readAgentRow(reader); + using (SqlDataReader reader = command.ExecuteReader()) + { + return readAgentRow(reader); + } + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating agentdata by UUID, error: " + e.Message); + return null; } - } - catch (Exception e) - { - m_log.Error(e.ToString()); - return null; } } /// - /// Store a weblogin key - /// - /// The agent UUID - /// the WebLogin Key - /// unused ? - override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey) - { - UserProfileData user = GetUserByUUID(AgentID); - user.WebLoginKey = WebLoginKey; - UpdateUserProfile(user); - - } - /// - /// Creates a new users profile + /// Creates a new agent /// - /// The user profile to create - override public void AddNewUserProfile(UserProfileData user) + /// The agent to create + override public void AddNewUserAgent(UserAgentData agent) { try { - InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, - user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, - user.HomeLocation.Z, - user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, - user.LastLogin, user.UserInventoryURI, user.UserAssetURI, - user.CanDoMask, user.WantDoMask, - user.AboutText, user.FirstLifeAboutText, user.Image, - user.FirstLifeImage, user.WebLoginKey); + InsertUpdateAgentRow(agent); } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error adding new agentdata, error: " + e.Message); } } + #endregion + + #region User Friends List Data + /// - /// Creates a new user and inserts it into the database + /// Add a new friend in the friendlist /// - /// User ID - /// First part of the login - /// Second part of the login - /// A salted hash of the users password - /// The salt used for the password hash - /// A regionHandle of the users home region - /// Home region position vector - /// Home region position vector - /// Home region position vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Home region 'look at' vector - /// Account created (unix timestamp) - /// Last login (unix timestamp) - /// Users inventory URI - /// Users asset URI - /// I can do mask - /// I want to do mask - /// Profile text - /// Firstlife text - /// UUID for profile image - /// UUID for firstlife image - /// Success? - private bool InsertUserRow(UUID uuid, string username, string lastname, string passwordHash, - string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, - float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, - string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, - string aboutText, string firstText, - UUID profileImage, UUID firstImage, UUID webLoginKey) - { - string sql = "INSERT INTO "+m_usersTableName; - sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], "; - sql += - "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], "; - sql += - "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], "; - sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey]) VALUES "; + /// UUID of the friendlist owner + /// Friend's UUID + /// Permission flag + override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms) + { + int dtvalue = Util.UnixTimeSinceEpoch(); - sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; - sql += - "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; - sql += - "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; - sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey);"; - - Dictionary parameters = new Dictionary(); - parameters["UUID"] = uuid.ToString(); - parameters["username"] = username.ToString(); - parameters["lastname"] = lastname.ToString(); - parameters["passwordHash"] = passwordHash.ToString(); - parameters["passwordSalt"] = passwordSalt.ToString(); - parameters["homeRegion"] = homeRegion.ToString(); - parameters["homeLocationX"] = homeLocX.ToString(); - parameters["homeLocationY"] = homeLocY.ToString(); - parameters["homeLocationZ"] = homeLocZ.ToString(); - parameters["homeLookAtX"] = homeLookAtX.ToString(); - parameters["homeLookAtY"] = homeLookAtY.ToString(); - parameters["homeLookAtZ"] = homeLookAtZ.ToString(); - parameters["created"] = created.ToString(); - parameters["lastLogin"] = lastlogin.ToString(); - parameters["userInventoryURI"] = String.Empty; - parameters["userAssetURI"] = String.Empty; - parameters["profileCanDoMask"] = "0"; - parameters["profileWantDoMask"] = "0"; - parameters["profileAboutText"] = aboutText; - parameters["profileFirstText"] = firstText; - parameters["profileImage"] = profileImage.ToString(); - parameters["profileFirstImage"] = firstImage.ToString(); - parameters["webLoginKey"] = UUID.Random().ToString(); + using (AutoClosingSqlCommand command = database.Query( + "INSERT INTO " + m_userFriendsTableName + " " + + "(ownerID,friendID,friendPerms,datetimestamp) " + + "VALUES " + + "(@ownerID,@friendID,@friendPerms,@datetimestamp)")) + { + command.Parameters.Add(database.CreateParameter("ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("friendID", friend)); + command.Parameters.Add(database.CreateParameter("friendPerms", perms)); + command.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue)); + command.ExecuteNonQuery(); + try + { + command.CommandText = string.Format("INSERT INTO {0} (ownerID,friendID,friendPerms,datetimestamp) VALUES (@friendID,@ownerID,@friendPerms,@datetimestamp)", + m_userFriendsTableName); - try - { - using (IDbCommand result = database.Query(sql, parameters)) + command.ExecuteNonQuery(); + } + catch (Exception e) { - return (result.ExecuteNonQuery() == 1); + m_log.Error("[USER DB] Error adding new userfriend, error: " + e.Message); + return; } } - catch (Exception e) - { - m_log.Error(e.ToString()); - return false; - } - } /// - /// Creates a new agent + /// Remove an friend from the friendlist /// - /// The agent to create - override public void AddNewUserAgent(UserAgentData agent) + /// UUID of the friendlist owner + /// UUID of the not-so-friendly user to remove from the list + override public void RemoveUserFriend(UUID friendlistowner, UUID friend) { - try + using (AutoClosingSqlCommand command = database.Query("delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID")) { - database.insertAgentRow(agent); + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("@friendID", friend)); + command.ExecuteNonQuery(); + + command.CommandText = "delete from " + m_userFriendsTableName + + " where ownerID = @friendID and friendID = @ownerID"; + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error removing userfriend, error: " + e.Message); + } } - catch (Exception e) + } + + /// + /// Update friendlist permission flag for a friend + /// + /// UUID of the friendlist owner + /// UUID of the friend + /// new permission flag + override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) + { + using (AutoClosingSqlCommand command = database.Query( + "update " + m_userFriendsTableName + + " SET friendPerms = @friendPerms " + + "where ownerID = @ownerID and friendID = @friendID")) { - m_log.Error(e.ToString()); + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + command.Parameters.Add(database.CreateParameter("@friendID", friend)); + command.Parameters.Add(database.CreateParameter("@friendPerms", perms)); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); + } } } /// - /// update a user profile + /// Get (fetch?) the user's friendlist /// - /// the profile to update - /// - override public bool UpdateUserProfile(UserProfileData user) + /// UUID of the friendlist owner + /// Friendlist list + override public List GetUserFriendList(UUID friendlistowner) { - using (IDbCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " + - "username = @username, " + - "lastname = @lastname," + - "passwordHash = @passwordHash," + - "passwordSalt = @passwordSalt," + - "homeRegion = @homeRegion," + - "homeLocationX = @homeLocationX," + - "homeLocationY = @homeLocationY," + - "homeLocationZ = @homeLocationZ," + - "homeLookAtX = @homeLookAtX," + - "homeLookAtY = @homeLookAtY," + - "homeLookAtZ = @homeLookAtZ," + - "created = @created," + - "lastLogin = @lastLogin," + - "userInventoryURI = @userInventoryURI," + - "userAssetURI = @userAssetURI," + - "profileCanDoMask = @profileCanDoMask," + - "profileWantDoMask = @profileWantDoMask," + - "profileAboutText = @profileAboutText," + - "profileFirstText = @profileFirstText," + - "profileImage = @profileImage," + - "profileFirstImage = @profileFirstImage, " + - "webLoginKey = @webLoginKey where " + - "UUID = @keyUUUID;")) - { - SqlParameter param1 = new SqlParameter("@uuid", user.ID.ToString()); - SqlParameter param2 = new SqlParameter("@username", user.FirstName); - SqlParameter param3 = new SqlParameter("@lastname", user.SurName); - SqlParameter param4 = new SqlParameter("@passwordHash", user.PasswordHash); - SqlParameter param5 = new SqlParameter("@passwordSalt", user.PasswordSalt); - SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.HomeRegion)); - SqlParameter param7 = new SqlParameter("@homeLocationX", user.HomeLocation.X); - SqlParameter param8 = new SqlParameter("@homeLocationY", user.HomeLocation.Y); - SqlParameter param9 = new SqlParameter("@homeLocationZ", user.HomeLocation.Y); - SqlParameter param10 = new SqlParameter("@homeLookAtX", user.HomeLookAt.X); - SqlParameter param11 = new SqlParameter("@homeLookAtY", user.HomeLookAt.Y); - SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.HomeLookAt.Z); - SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.Created)); - SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.LastLogin)); - SqlParameter param15 = new SqlParameter("@userInventoryURI", user.UserInventoryURI); - SqlParameter param16 = new SqlParameter("@userAssetURI", user.UserAssetURI); - SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.CanDoMask)); - SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.WantDoMask)); - SqlParameter param19 = new SqlParameter("@profileAboutText", user.AboutText); - SqlParameter param20 = new SqlParameter("@profileFirstText", user.FirstLifeAboutText); - SqlParameter param21 = new SqlParameter("@profileImage", user.Image.ToString()); - SqlParameter param22 = new SqlParameter("@profileFirstImage", user.FirstLifeImage.ToString()); - SqlParameter param23 = new SqlParameter("@keyUUUID", user.ID.ToString()); - SqlParameter param24 = new SqlParameter("@webLoginKey", user.WebLoginKey.ToString()); - command.Parameters.Add(param1); - command.Parameters.Add(param2); - command.Parameters.Add(param3); - command.Parameters.Add(param4); - command.Parameters.Add(param5); - command.Parameters.Add(param6); - command.Parameters.Add(param7); - command.Parameters.Add(param8); - command.Parameters.Add(param9); - command.Parameters.Add(param10); - command.Parameters.Add(param11); - command.Parameters.Add(param12); - command.Parameters.Add(param13); - command.Parameters.Add(param14); - command.Parameters.Add(param15); - command.Parameters.Add(param16); - command.Parameters.Add(param17); - command.Parameters.Add(param18); - command.Parameters.Add(param19); - command.Parameters.Add(param20); - command.Parameters.Add(param21); - command.Parameters.Add(param22); - command.Parameters.Add(param23); - command.Parameters.Add(param24); + List friendList = new List(); + + //Left Join userfriends to itself + using (AutoClosingSqlCommand command = database.Query( + "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + + " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID")) + { + command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner)); + try { - int affected = command.ExecuteNonQuery(); - return (affected != 0); + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + FriendListItem fli = new FriendListItem(); + fli.FriendListOwner = new UUID((string)reader["ownerID"]); + fli.Friend = new UUID((string)reader["friendID"]); + fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]); + + // This is not a real column in the database table, it's a joined column from the opposite record + fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); + + friendList.Add(fli); + } + } } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); } } - return false; + + return friendList; } + #endregion + + #region Money functions (not used) + /// /// Performs a money transfer request between two accounts /// @@ -746,33 +595,327 @@ namespace OpenSim.Data.MSSQL return false; } - /// Appearance + #endregion + + #region Appearance methods + + /// + /// Gets the user appearance. + /// + /// The user. + /// /// TODO: stubs for now to get us to a compiling state gently override public AvatarAppearance GetUserAppearance(UUID user) { -// return new AvatarAppearance(); try { - Dictionary param = new Dictionary(); - param["@UUID"] = user.ToString(); + AvatarAppearance appearance = new AvatarAppearance(); - using (IDbCommand result = - database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID", param)) - using (IDataReader reader = result.ExecuteReader()) + using (AutoClosingSqlCommand command = database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID")) + { + command.Parameters.Add(database.CreateParameter("@UUID", user)); + using (IDataReader reader = command.ExecuteReader()) { - AvatarAppearance item = null; if (reader.Read()) - item = readUserAppearance(reader); - return item; + appearance = readUserAppearance(reader); } + } + + appearance.SetAttachments(GetUserAttachments(user)); + + return appearance; } catch (Exception e) { - m_log.Error(e.ToString()); + m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message); } return null; } + + /// + /// Update a user appearence into database + /// + /// the used UUID + /// the appearence + override public void UpdateUserAppearance(UUID 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)"; + + using (AutoClosingSqlCommand cmd = database.Query(sql)) + { + cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner)); + cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial)); + cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams)); + cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.ToBytes())); + cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight)); + cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem)); + cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset)); + cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem)); + cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset)); + cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem)); + cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset)); + cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem)); + cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset)); + cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem)); + cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset)); + cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem)); + cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset)); + cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem)); + cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset)); + cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem)); + cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset)); + cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem)); + cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset)); + cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem)); + cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset)); + cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem)); + cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset)); + cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem)); + cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset)); + cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem)); + cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset)); + + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception e) + { + m_log.Error("[USER DB] Error updating user appearance, error: " + e.Message); + } + } + + UpdateUserAttachments(user, appearance.GetAttachments()); + } + + #endregion + + #region Attachment methods + + /// + /// Gets all attachment of a agent. + /// + /// agent ID. + /// + public Hashtable GetUserAttachments(UUID agentID) + { + Hashtable returnTable = new Hashtable(); + using (AutoClosingSqlCommand command = database.Query("select attachpoint, item, asset from avatarattachments where UUID = @uuid", database.CreateParameter("@uuid", agentID))) + { + using (SqlDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + int attachpoint = Convert.ToInt32(reader["attachpoint"]); + if (returnTable.ContainsKey(attachpoint)) + continue; + Hashtable item = new Hashtable(); + item.Add("item", reader["item"].ToString()); + item.Add("asset", reader["asset"].ToString()); + + returnTable.Add(attachpoint, item); + } + } + } + return returnTable; + } + + /// + /// Updates all attachments of the agent. + /// + /// agentID. + /// data with all items on attachmentpoints + public void UpdateUserAttachments(UUID agentID, Hashtable data) + { + string sql = "delete from avatarattachments where UUID = @uuid"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("uuid", agentID)); + command.ExecuteNonQuery(); + } + if (data == null) + return; + + sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (@uuid, @attachpoint, @item, @asset)"; + + using (AutoClosingSqlCommand command = database.Query(sql)) + { + bool firstTime = true; + foreach (DictionaryEntry e in data) + { + int attachpoint = Convert.ToInt32(e.Key); + + Hashtable item = (Hashtable)e.Value; + + if (firstTime) + { + command.Parameters.Add(database.CreateParameter("@uuid", agentID)); + command.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint)); + command.Parameters.Add(database.CreateParameter("@item", item["item"].ToString())); + command.Parameters.Add(database.CreateParameter("@asset", item["asset"].ToString())); + firstTime = false; + } + command.Parameters["@uuid"].Value = agentID.ToString(); + command.Parameters["@attachpoint"].Value = attachpoint; + command.Parameters["@item"].Value = item["item"].ToString(); + command.Parameters["@asset"].Value = item["asset"].ToString(); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception ex) + { + m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message); + } + } + } + } + + /// + /// Resets all attachments of a agent in the database. + /// + /// agentID. + override public void ResetAttachments(UUID agentID) + { + using (AutoClosingSqlCommand command = database.Query("update avatarattachments set asset = '00000000-0000-0000-0000-000000000000' where UUID = @uuid")) + { + command.Parameters.Add(database.CreateParameter("uuid", agentID)); + command.ExecuteNonQuery(); + } + } + + #endregion + + #region Other public methods + + /// + /// STUB ! Update current region + /// + /// avatar uuid + /// region uuid + /// region handle + override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle) + { + //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); + } + + /// + /// + /// + /// + /// + /// + override public List GeneratePickerResults(UUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + try + { + using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first AND lastname LIKE @second")) + { + //Add wildcard to the search + command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%")); + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["lastname"]; + returnlist.Add(user); + } + } + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + else if (querysplit.Length == 1) + { + try + { + using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first OR lastname LIKE @first")) + { + command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%")); + + using (IDataReader reader = command.ExecuteReader()) + { + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new UUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["lastname"]; + returnlist.Add(user); + } + } + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + return returnlist; + } + + /// + /// Store a weblogin key + /// + /// The agent UUID + /// the WebLogin Key + /// unused ? + override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey) + { + UserProfileData user = GetUserByUUID(AgentID); + user.WebLoginKey = WebLoginKey; + UpdateUserProfile(user); + + } + + /// + /// Database provider name + /// + /// Provider name + override public string Name + { + get { return "MSSQL Userdata Interface"; } + } + + /// + /// Database provider version + /// + /// provider version + override public string Version + { + get { return database.getVersion(); } + } + + #endregion + + #region Private functions + /// /// Reads a one item from an SQL result /// @@ -827,99 +970,254 @@ namespace OpenSim.Data.MSSQL } /// - /// Update a user appearence into database + /// Insert/Update a agent row in the DB. /// - /// the used UUID - /// the appearence - override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance) + /// agentdata. + private void InsertUpdateAgentRow(UserAgentData agentdata) { - 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)"; - - using (AutoClosingSqlCommand cmd = database.Query(sql)) + string sql = @" + +IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID) + BEGIN + UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos + WHERE UUID = @UUID + END +ELSE + BEGIN + INSERT INTO + agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES + (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos) + END +"; + + using (AutoClosingSqlCommand command = database.Query(sql)) { - 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()); - + command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID)); + command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID)); + command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID)); + command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP)); + command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort)); + command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline)); + command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime)); + command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime)); + command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region)); + command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle)); + command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">")); + + command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable); try { - cmd.ExecuteNonQuery(); + if (command.ExecuteNonQuery() > 0) + { + command.Transaction.Commit(); + return; + } + + command.Transaction.Rollback(); + return; } catch (Exception e) { + command.Transaction.Rollback(); m_log.Error(e.ToString()); + return; } } + } /// - /// Database provider name + /// Reads an agent row from a database reader /// - /// Provider name - override public string Name + /// An active database reader + /// A user session agent + private UserAgentData readAgentRow(IDataReader reader) { - get {return "MSSQL Userdata Interface";} + UserAgentData retval = new UserAgentData(); + + if (reader.Read()) + { + // Agent IDs + retval.ProfileID = new UUID((string)reader["UUID"]); + retval.SessionID = new UUID((string)reader["sessionID"]); + retval.SecureSessionID = new UUID((string)reader["secureSessionID"]); + + // Agent Who? + retval.AgentIP = (string)reader["agentIP"]; + retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString()); + retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0; + + // Login/Logout times (UNIX Epoch) + retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString()); + retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); + + // Current position + retval.Region = (string)reader["currentRegion"]; + retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString()); + Vector3 tmp_v; + Vector3.TryParse((string)reader["currentPos"], out tmp_v); + retval.Position = tmp_v; + + } + else + { + return null; + } + return retval; } /// - /// Database provider version + /// Creates a new user and inserts it into the database /// - /// provider version - override public string Version + /// User ID + /// First part of the login + /// Second part of the login + /// A salted hash of the users password + /// The salt used for the password hash + /// A regionHandle of the users home region + /// Home region position vector + /// Home region position vector + /// Home region position vector + /// Home region 'look at' vector + /// Home region 'look at' vector + /// Home region 'look at' vector + /// Account created (unix timestamp) + /// Last login (unix timestamp) + /// Users inventory URI + /// Users asset URI + /// I can do mask + /// I want to do mask + /// Profile text + /// Firstlife text + /// UUID for profile image + /// UUID for firstlife image + /// web login key + /// homeregion UUID + /// has the user godlevel + /// unknown + /// unknown + /// UUID of partner + /// Success? + private void InsertUserRow(UUID uuid, string username, string lastname, string passwordHash, + string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ, + float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, + string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, + string aboutText, string firstText, + UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID, + int godLevel, int userFlags, string customType, UUID partnerID) { - get {return database.getVersion();} + string sql = "INSERT INTO " + m_usersTableName; + sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], "; + sql += "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], "; + sql += "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], "; + sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], "; + sql += "[homeRegionID], [userFlags], [godLevel], [customType], [partner]) VALUES "; + + sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; + sql += "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; + sql += "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; + sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, "; + sql += "@homeRegionID, @userFlags, @godLevel, @customType, @partner)"; + + try + { + using (AutoClosingSqlCommand command = database.Query(sql)) + { + command.Parameters.Add(database.CreateParameter("UUID", uuid)); + command.Parameters.Add(database.CreateParameter("username", username)); + command.Parameters.Add(database.CreateParameter("lastname", lastname)); + command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash)); + command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt)); + command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion)); + command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX)); + command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY)); + command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ)); + command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX)); + command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY)); + command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ)); + command.Parameters.Add(database.CreateParameter("created", created)); + command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin)); + command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI)); + command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI)); + command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask)); + command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask)); + command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText)); + command.Parameters.Add(database.CreateParameter("profileFirstText", firstText)); + command.Parameters.Add(database.CreateParameter("profileImage", profileImage)); + command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage)); + command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey)); + // + command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID)); + command.Parameters.Add(database.CreateParameter("userFlags", userFlags)); + command.Parameters.Add(database.CreateParameter("godLevel", godLevel)); + command.Parameters.Add(database.CreateParameter("customType", customType)); + command.Parameters.Add(database.CreateParameter("partner", partnerID)); + + + command.ExecuteNonQuery(); + return; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return; + } + } /// - /// Not implemented + /// Reads a user profile from an active data reader /// - /// - public void runQuery(string query) + /// An active database reader + /// A user profile + private static UserProfileData ReadUserRow(IDataReader reader) { - } + UserProfileData retval = new UserProfileData(); - override public void ResetAttachments(UUID userID) - { + if (reader.Read()) + { + retval.ID = new UUID((string)reader["UUID"]); + retval.FirstName = (string)reader["username"]; + retval.SurName = (string)reader["lastname"]; + + retval.PasswordHash = (string)reader["passwordHash"]; + retval.PasswordSalt = (string)reader["passwordSalt"]; + + retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString()); + retval.HomeLocation = new Vector3( + Convert.ToSingle(reader["homeLocationX"].ToString()), + Convert.ToSingle(reader["homeLocationY"].ToString()), + Convert.ToSingle(reader["homeLocationZ"].ToString())); + retval.HomeLookAt = new Vector3( + Convert.ToSingle(reader["homeLookAtX"].ToString()), + Convert.ToSingle(reader["homeLookAtY"].ToString()), + Convert.ToSingle(reader["homeLookAtZ"].ToString())); + + retval.Created = Convert.ToInt32(reader["created"].ToString()); + retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); + + retval.UserInventoryURI = (string)reader["userInventoryURI"]; + retval.UserAssetURI = (string)reader["userAssetURI"]; + + retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); + retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); + + retval.AboutText = (string)reader["profileAboutText"]; + retval.FirstLifeAboutText = (string)reader["profileFirstText"]; + + retval.Image = new UUID((string)reader["profileImage"]); + retval.FirstLifeImage = new UUID((string)reader["profileFirstImage"]); + retval.WebLoginKey = new UUID((string)reader["webLoginKey"]); + } + else + { + return null; + } + return retval; } + + #endregion } + } diff --git a/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql new file mode 100644 index 0000000..2b293c7 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql @@ -0,0 +1,13 @@ +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [local] [tinyint] NOT NULL, + [temporary] [tinyint] NOT NULL, + [data] [image] NOT NULL, +PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] diff --git a/OpenSim/Data/MSSQL/Resources/001_GridStore.sql b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql new file mode 100644 index 0000000..ff15f54 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql @@ -0,0 +1,37 @@ +BEGIN TRANSACTION + +CREATE TABLE [dbo].[regions]( + [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL, + [regionRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionSecret] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionDataURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverIP] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locX] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locY] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [locZ] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [eastOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [westOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [southOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [northOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionAssetSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionUserSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [regionMapTexture] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverHttpPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [serverRemotingPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL, + [owner_uuid] [varchar](36) COLLATE Latin1_General_CI_AS NULL, +PRIMARY KEY CLUSTERED +( + [uuid] ASC +)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] +) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql new file mode 100644 index 0000000..836d2d1 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql @@ -0,0 +1,64 @@ +BEGIN TRANSACTION + +CREATE TABLE [inventoryfolders] ( + [folderID] [varchar](36) NOT NULL default '', + [agentID] [varchar](36) default NULL, + [parentFolderID] [varchar](36) default NULL, + [folderName] [varchar](64) default NULL, + [type] [smallint] NOT NULL default 0, + [version] [int] NOT NULL default 0, + PRIMARY KEY CLUSTERED +( + [folderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders] +( + [agentID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [inventoryitems] ( + [inventoryID] [varchar](36) NOT NULL default '', + [assetID] [varchar](36) default NULL, + [assetType] [int] default NULL, + [parentFolderID] [varchar](36) default NULL, + [avatarID] [varchar](36) default NULL, + [inventoryName] [varchar](64) default NULL, + [inventoryDescription] [varchar](128) default NULL, + [inventoryNextPermissions] [int] default NULL, + [inventoryCurrentPermissions] [int] default NULL, + [invType] [int] default NULL, + [creatorID] [varchar](36) default NULL, + [inventoryBasePermissions] [int] NOT NULL default 0, + [inventoryEveryOnePermissions] [int] NOT NULL default 0, + [salePrice] [int] default NULL, + [saleType] [tinyint] default NULL, + [creationDate] [int] default NULL, + [groupID] [varchar](36) default NULL, + [groupOwned] [bit] default NULL, + [flags] [int] default NULL, + PRIMARY KEY CLUSTERED +( + [inventoryID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems] +( + [avatarID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/001_UserStore.sql b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql new file mode 100644 index 0000000..abd6ff2 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql @@ -0,0 +1,224 @@ +CREATE TABLE [users] ( + [UUID] [varchar](36) NOT NULL default '', + [username] [varchar](32) NOT NULL, + [lastname] [varchar](32) NOT NULL, + [passwordHash] [varchar](32) NOT NULL, + [passwordSalt] [varchar](32) NOT NULL, + [homeRegion] [bigint] default NULL, + [homeLocationX] [float] default NULL, + [homeLocationY] [float] default NULL, + [homeLocationZ] [float] default NULL, + [homeLookAtX] [float] default NULL, + [homeLookAtY] [float] default NULL, + [homeLookAtZ] [float] default NULL, + [created] [int] NOT NULL, + [lastLogin] [int] NOT NULL, + [userInventoryURI] [varchar](255) default NULL, + [userAssetURI] [varchar](255) default NULL, + [profileCanDoMask] [int] default NULL, + [profileWantDoMask] [int] default NULL, + [profileAboutText] [ntext], + [profileFirstText] [ntext], + [profileImage] [varchar](36) default NULL, + [profileFirstImage] [varchar](36) default NULL, + [webLoginKey] [varchar](36) default NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [usernames] ON [users] +( + [username] ASC, + [lastname] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [agents] ( + [UUID] [varchar](36) NOT NULL, + [sessionID] [varchar](36) NOT NULL, + [secureSessionID] [varchar](36) NOT NULL, + [agentIP] [varchar](16) NOT NULL, + [agentPort] [int] NOT NULL, + [agentOnline] [tinyint] NOT NULL, + [loginTime] [int] NOT NULL, + [logoutTime] [int] NOT NULL, + [currentRegion] [varchar](36) NOT NULL, + [currentHandle] [bigint] NOT NULL, + [currentPos] [varchar](64) NOT NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [session] ON [agents] +( + [sessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [ssession] ON [agents] +( + [secureSessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [dbo].[userfriends]( + [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, + [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL +) ON [PRIMARY] + +CREATE TABLE [avatarappearance] ( + [Owner] [varchar](36) NOT NULL, + [Serial] int 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 ( + [Owner] + ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] +CREATE TABLE [users] ( + [UUID] [varchar](36) NOT NULL default '', + [username] [varchar](32) NOT NULL, + [lastname] [varchar](32) NOT NULL, + [passwordHash] [varchar](32) NOT NULL, + [passwordSalt] [varchar](32) NOT NULL, + [homeRegion] [bigint] default NULL, + [homeLocationX] [float] default NULL, + [homeLocationY] [float] default NULL, + [homeLocationZ] [float] default NULL, + [homeLookAtX] [float] default NULL, + [homeLookAtY] [float] default NULL, + [homeLookAtZ] [float] default NULL, + [created] [int] NOT NULL, + [lastLogin] [int] NOT NULL, + [userInventoryURI] [varchar](255) default NULL, + [userAssetURI] [varchar](255) default NULL, + [profileCanDoMask] [int] default NULL, + [profileWantDoMask] [int] default NULL, + [profileAboutText] [ntext], + [profileFirstText] [ntext], + [profileImage] [varchar](36) default NULL, + [profileFirstImage] [varchar](36) default NULL, + [webLoginKey] [varchar](36) default NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [usernames] ON [users] +( + [username] ASC, + [lastname] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [agents] ( + [UUID] [varchar](36) NOT NULL, + [sessionID] [varchar](36) NOT NULL, + [secureSessionID] [varchar](36) NOT NULL, + [agentIP] [varchar](16) NOT NULL, + [agentPort] [int] NOT NULL, + [agentOnline] [tinyint] NOT NULL, + [loginTime] [int] NOT NULL, + [logoutTime] [int] NOT NULL, + [currentRegion] [varchar](36) NOT NULL, + [currentHandle] [bigint] NOT NULL, + [currentPos] [varchar](64) NOT NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [session] ON [agents] +( + [sessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [ssession] ON [agents] +( + [secureSessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [dbo].[userfriends]( + [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, + [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL +) ON [PRIMARY] + +CREATE TABLE [avatarappearance] ( + [Owner] [varchar](36) NOT NULL, + [Serial] int 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 ( + [Owner] + ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] diff --git a/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql new file mode 100644 index 0000000..3e24543 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql @@ -0,0 +1,29 @@ +BEGIN TRANSACTION + +CREATE TABLE Tmp_assets + ( + id varchar(36) NOT NULL, + name varchar(64) NOT NULL, + description varchar(64) NOT NULL, + assetType tinyint NOT NULL, + local bit NOT NULL, + temporary bit NOT NULL, + data image NOT NULL + ) ON [PRIMARY] + TEXTIMAGE_ON [PRIMARY] + +IF EXISTS(SELECT * FROM assets) + EXEC('INSERT INTO Tmp_assets (id, name, description, assetType, local, temporary, data) + SELECT id, name, description, assetType, CONVERT(bit, local), CONVERT(bit, temporary), data FROM assets WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE assets + +EXECUTE sp_rename N'Tmp_assets', N'assets', 'OBJECT' + +ALTER TABLE dbo.assets ADD CONSTRAINT + PK__assets__id PRIMARY KEY CLUSTERED + ( + id + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/002_UserStore.sql b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql new file mode 100644 index 0000000..a3b5868 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION + +ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000'; +ALTER TABLE users ADD userFlags int NOT NULL default 0; +ALTER TABLE users ADD godLevel int NOT NULL default 0; +ALTER TABLE users ADD customType varchar(32) not null default ''; +ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000'; + +COMMIT +BEGIN TRANSACTION + +ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000'; +ALTER TABLE users ADD userFlags int NOT NULL default 0; +ALTER TABLE users ADD godLevel int NOT NULL default 0; +ALTER TABLE users ADD customType varchar(32) not null default ''; +ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000'; + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/003_GridStore.sql b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql new file mode 100644 index 0000000..e080947 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql @@ -0,0 +1,22 @@ +BEGIN TRANSACTION + +CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions + ( + regionName + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX IX_regions_handle ON dbo.regions + ( + regionHandle + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX IX_regions_override ON dbo.regions + ( + eastOverrideHandle, + westOverrideHandle, + southOverrideHandle, + northOverrideHandle + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/003_UserStore.sql b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql new file mode 100644 index 0000000..c2821e6 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql @@ -0,0 +1,30 @@ +BEGIN TRANSACTION + +CREATE TABLE [avatarattachments] ( + [UUID] varchar(36) NOT NULL + , [attachpoint] int NOT NULL + , [item] varchar(36) NOT NULL + , [asset] varchar(36) NOT NULL) + +CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments + ( + UUID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +COMMIT +BEGIN TRANSACTION + +CREATE TABLE [avatarattachments] ( + [UUID] varchar(36) NOT NULL + , [attachpoint] int NOT NULL + , [item] varchar(36) NOT NULL + , [asset] varchar(36) NOT NULL) + +CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments + ( + UUID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql index 4e64901..15b39a7 100644 --- a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql +++ b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql @@ -1,6 +1,6 @@ BEGIN TRANSACTION -CREATE TABLE dbo.Tmp_primitems +CREATE TABLE Tmp_primitems ( itemID varchar(36) NOT NULL, primID varchar(36) NULL, @@ -22,15 +22,15 @@ CREATE TABLE dbo.Tmp_primitems groupPermissions int NULL ) ON [PRIMARY] -IF EXISTS(SELECT * FROM dbo.primitems) - EXEC('INSERT INTO dbo.Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions) +IF EXISTS(SELECT * FROM primitems) + EXEC('INSERT INTO Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions) SELECT CONVERT(varchar(36), itemID), CONVERT(varchar(36), primID), CONVERT(varchar(36), assetID), CONVERT(varchar(36), parentFolderID), invType, assetType, name, description, creationDate, CONVERT(varchar(36), creatorID), CONVERT(varchar(36), ownerID), CONVERT(varchar(36), lastOwnerID), CONVERT(varchar(36), groupID), nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions') -DROP TABLE dbo.primitems +DROP TABLE primitems -EXECUTE sp_rename N'dbo.Tmp_primitems', N'primitems', 'OBJECT' +EXECUTE sp_rename N'Tmp_primitems', N'primitems', 'OBJECT' -ALTER TABLE dbo.primitems ADD CONSTRAINT +ALTER TABLE primitems ADD CONSTRAINT PK__primitems__0A688BB1 PRIMARY KEY CLUSTERED ( itemID diff --git a/OpenSim/Data/MSSQL/Resources/004_UserStore.sql b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql new file mode 100644 index 0000000..f3e223e --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql @@ -0,0 +1,58 @@ +BEGIN TRANSACTION + +CREATE TABLE Tmp_userfriends + ( + ownerID varchar(36) NOT NULL, + friendID varchar(36) NOT NULL, + friendPerms int NOT NULL, + datetimestamp int NOT NULL + ) ON [PRIMARY] + +IF EXISTS(SELECT * FROM userfriends) + EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp) + SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE dbo.userfriends + +EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT' + +CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends + ( + ownerID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends + ( + friendID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT +BEGIN TRANSACTION + +CREATE TABLE Tmp_userfriends + ( + ownerID varchar(36) NOT NULL, + friendID varchar(36) NOT NULL, + friendPerms int NOT NULL, + datetimestamp int NOT NULL + ) ON [PRIMARY] + +IF EXISTS(SELECT * FROM dbo.userfriends) + EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp) + SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE dbo.userfriends + +EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT' + +CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends + ( + ownerID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends + ( + friendID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql index 74e9d85..eb0862c 100644 --- a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql +++ b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql @@ -1,6 +1,6 @@ BEGIN TRANSACTION -CREATE TABLE dbo.Tmp_primshapes +CREATE TABLE Tmp_primshapes ( UUID varchar(36) NOT NULL, Shape int NULL, @@ -32,18 +32,18 @@ CREATE TABLE dbo.Tmp_primshapes ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] -IF EXISTS(SELECT * FROM dbo.primshapes) - EXEC('INSERT INTO dbo.Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams) - SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM dbo.primshapes WITH (HOLDLOCK TABLOCKX)') +IF EXISTS(SELECT * FROM primshapes) + EXEC('INSERT INTO Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams) + SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM primshapes WITH (HOLDLOCK TABLOCKX)') -DROP TABLE dbo.primshapes +DROP TABLE primshapes -EXECUTE sp_rename N'dbo.Tmp_primshapes', N'primshapes', 'OBJECT' +EXECUTE sp_rename N'Tmp_primshapes', N'primshapes', 'OBJECT' -ALTER TABLE dbo.primshapes ADD CONSTRAINT +ALTER TABLE primshapes ADD CONSTRAINT PK__primshapes__0880433F PRIMARY KEY CLUSTERED ( UUID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -COMMIT \ No newline at end of file +COMMIT -- cgit v1.1