From 71fd737a66c58faa6854ad1c1d8c58f64491fdb0 Mon Sep 17 00:00:00 2001 From: Teravus Ovares Date: Wed, 5 Dec 2007 15:53:58 +0000 Subject: * Applied MSSQL Patch from akokko, Thanks! akokko * This hasn't been tested in MSSQL mode, however it's been checked to make sure it doesn't cause any issues with mySQL/SQLlite --- OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs | 227 +++++ OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs | 164 +++- OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs | 697 +++++++++++++ OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs | 104 ++ OpenSim/Framework/Data.MSSQL/MSSQLManager.cs | 616 +++++++++++- OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs | 452 +++++++++ .../Data.MSSQL/Resources/CreateAssetsTable.sql | 19 + .../Data.MSSQL/Resources/CreateFoldersTable.sql | 27 + .../Data.MSSQL/Resources/CreateItemsTable.sql | 39 + .../Data.MSSQL/Resources/Mssql-agents.sql | 37 + .../Framework/Data.MSSQL/Resources/Mssql-logs.sql | 20 + .../Framework/Data.MSSQL/Resources/Mssql-users.sql | 41 + OpenSim/Region/Application/OpenSimMain.cs | 7 + .../OpenSim.DataStore.MSSQL/MSSQLDataStore.cs | 1032 ++++++++++++++++++++ bin/mssql_connection.ini | 6 + prebuild.xml | 33 + share/sql/mssql-CreateAssetsTable.sql | 19 + share/sql/mssql-CreateFoldersTable.sql | 27 + share/sql/mssql-CreateItemsTable.sql | 39 + share/sql/mssql-agents.sql | 37 + share/sql/mssql-logs.sql | 20 + share/sql/mssql-users.sql | 41 + 22 files changed, 3630 insertions(+), 74 deletions(-) create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql create mode 100644 OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql create mode 100644 OpenSim/Region/Storage/OpenSim.DataStore.MSSQL/MSSQLDataStore.cs create mode 100644 bin/mssql_connection.ini create mode 100644 share/sql/mssql-CreateAssetsTable.sql create mode 100644 share/sql/mssql-CreateFoldersTable.sql create mode 100644 share/sql/mssql-CreateItemsTable.sql create mode 100644 share/sql/mssql-agents.sql create mode 100644 share/sql/mssql-logs.sql create mode 100644 share/sql/mssql-users.sql diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs new file mode 100644 index 0000000..e933a5b --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs @@ -0,0 +1,227 @@ +/* +* Copyright (c) Contributors, http://opensimulator.org/ +* See CONTRIBUTORS.TXT for a full list of copyright holders. +* +* Redistribution and use in source and binary forms, with or without +* modification, are permitted provided that the following conditions are met: +* * Redistributions of source code must retain the above copyright +* notice, this list of conditions and the following disclaimer. +* * Redistributions in binary form must reproduce the above copyright +* notice, this list of conditions and the following disclaimer in the +* documentation and/or other materials provided with the distribution. +* * Neither the name of the OpenSim Project nor the +* names of its contributors may be used to endorse or promote products +* derived from this software without specific prior written permission. +* +* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS AS IS AND ANY +* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY +* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS +* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +* +*/ + +using System; +using System.Data; +using System.Collections.Generic; +using System.Data.SqlClient; + +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MSSQL +{ + class MSSQLAssetData : IAssetProvider + { + MSSQLManager database; + #region IAssetProvider Members + + private void UpgradeAssetsTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + MainLog.Instance.Notice("ASSETS", "Creating new database tables"); + database.ExecuteResourceSql("CreateAssetsTable.sql"); + return; + } + } + + /// + /// Ensure that the assets related tables exists and are at the latest version + /// + private void TestTables() + { + + Dictionary tableList = new Dictionary(); + + tableList["assets"] = null; + database.GetTableVersion(tableList); + + UpgradeAssetsTable(tableList["assets"]); + + } + + public AssetBase FetchAsset(LLUUID assetID) + { + AssetBase asset = null; + + Dictionary param = new Dictionary(); + param["id"] = assetID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param); + IDataReader reader = result.ExecuteReader(); + + asset = database.getAssetRow(reader); + reader.Close(); + result.Dispose(); + + return asset; + } + + public void CreateAsset(AssetBase asset) + { + + if (ExistsAsset((LLUUID)asset.FullID)) + { + return; + } + + + + SqlCommand cmd = + new SqlCommand( + "INSERT INTO assets ([id], [name], [description], [assetType], [invType], [local], [temporary], [data])"+ + " VALUES "+ + "(@id, @name, @description, @assetType, @invType, @local, @temporary, @data)", + database.getConnection()); + + using (cmd) + { + + //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar); + //p.Value = asset.FullID.ToStringHyphenated(); + cmd.Parameters.AddWithValue("id", asset.FullID.ToStringHyphenated()); + 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 f = cmd.Parameters.Add("invType", SqlDbType.TinyInt); + f.Value = asset.InvType; + 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; + try + { + cmd.ExecuteNonQuery(); + } + catch (Exception) + { + throw; + } + + cmd.Dispose(); + } + + } + + + public void UpdateAsset(AssetBase asset) + { + SqlCommand command = new SqlCommand("UPDATE assets set id = @id, " + + "name = @name, " + + "description = @description," + + "assetType = @assetType," + + "invType = @invType," + + "local = @local,"+ + "temporary = @temporary," + + "data = @data where " + + "id = @keyId;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@name", asset.Name); + SqlParameter param3 = new SqlParameter("@description", asset.Description); + SqlParameter param4 = new SqlParameter("@assetType", asset.Type); + SqlParameter param5 = new SqlParameter("@invType", asset.InvType); + 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.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + } + + public bool ExistsAsset(LLUUID uuid) + { + if (FetchAsset(uuid) != null) { + return true; + } + return false; + } + + /// + /// All writes are immediately commited to the database, so this is a no-op + /// + public void CommitAssets() + { + } + + #endregion + + #region IPlugin Members + + + + public void Initialise() + { + + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + this.database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + + TestTables(); + } + + public string Version + { +// get { return database.getVersion(); } + get { return database.getVersion(); } + } + + public string Name + { + get { return "MSSQL Asset storage engine"; } + } + + #endregion + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs index 1516fec..0d294e7 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs @@ -28,11 +28,10 @@ using System; using System.Collections.Generic; using System.Data; -using OpenSim.Framework; using System.Security.Cryptography; using System.Text; using libsecondlife; - +using OpenSim.Framework.Console; namespace OpenSim.Framework.Data.MSSQL { @@ -44,14 +43,22 @@ namespace OpenSim.Framework.Data.MSSQL /// /// Database manager /// - private MSSqlManager database; + private MSSQLManager database; /// /// Initialises the Grid Interface /// public void Initialise() { - database = new MSSqlManager("localhost", "db", "user", "password", "false"); + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } /// @@ -100,17 +107,115 @@ namespace OpenSim.Framework.Data.MSSQL /// Sim profile public RegionProfileData GetProfileByHandle(ulong handle) { - Dictionary param = new Dictionary(); - param["handle"] = handle.ToString(); + IDataReader reader = null; + try + { + Dictionary param = new Dictionary(); + param["handle"] = handle.ToString(); + IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = @handle", param); + reader = result.ExecuteReader(); - IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param); - IDataReader reader = result.ExecuteReader(); + RegionProfileData row = database.getRegionRow(reader); + reader.Close(); + result.Dispose(); - RegionProfileData row = database.getRow(reader); - reader.Close(); - result.Dispose(); + return row; - return row; + } + catch (Exception) + { + if (reader != null) { + reader.Close(); + } + + } + return null; + } + + /// + /// // Returns a list of avatar and UUIDs that match the query + /// + + public List GeneratePickerResults(LLUUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit; + querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["first"] = querysplit[0]; + param["second"] = querysplit[1]; + + IDbCommand result = + database.Query("SELECT UUID,username,surname FROM users WHERE username = @first AND lastname = @second", param); + IDataReader reader = result.ExecuteReader(); + + + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; + returnlist.Add(user); + + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + + + + } + else if (querysplit.Length == 1) + { + + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["first"] = querysplit[0]; + param["second"] = querysplit[1]; + + IDbCommand result = + database.Query("SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", param); + IDataReader reader = result.ExecuteReader(); + + + while (reader.Read()) + { + AvatarPickerAvatar user = new AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; + returnlist.Add(user); + + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + } + return returnlist; } /// @@ -120,28 +225,18 @@ namespace OpenSim.Framework.Data.MSSQL /// The sim profile public RegionProfileData GetProfileByLLUUID(LLUUID uuid) { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToStringHyphenated(); - + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToString(); IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); IDataReader reader = result.ExecuteReader(); - RegionProfileData row = database.getRow(reader); + RegionProfileData row = database.getRegionRow(reader); reader.Close(); result.Dispose(); return row; } - /// - /// // Returns a list of avatar and UUIDs that match the query - /// - public List GeneratePickerResults(LLUUID queryID, string query) - { - //Do nothing yet - List returnlist = new List(); - return returnlist; - } /// /// Adds a new specified region to the database /// @@ -149,7 +244,20 @@ namespace OpenSim.Framework.Data.MSSQL /// A dataresponse enum indicating success public DataResponse AddProfile(RegionProfileData profile) { - if (database.insertRow(profile)) + + try + { + if (GetProfileByLLUUID(profile.UUID) != null) + { + return DataResponse.RESPONSE_OK; + } + } + catch (Exception) + { + System.Console.WriteLine("No regions found. Create new one."); + } + + if (database.insertRegionRow(profile)) { return DataResponse.RESPONSE_OK; } @@ -201,7 +309,5 @@ namespace OpenSim.Framework.Data.MSSQL { return null; } - // This is here because MSSQL GridData only seems to know about itself o.O - } -} \ No newline at end of file +} diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs new file mode 100644 index 0000000..364e0f2 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs @@ -0,0 +1,697 @@ +/* +* Copyright (c) Contributors, http://opensimulator.org/ +* See CONTRIBUTORS.TXT for a full list of copyright holders. +* +* Redistribution and use in source and binary forms, with or without +* modification, are permitted provided that the following conditions are met: +* * Redistributions of source code must retain the above copyright +* notice, this list of conditions and the following disclaimer. +* * Redistributions in binary form must reproduce the above copyright +* notice, this list of conditions and the following disclaimer in the +* documentation and/or other materials provided with the distribution. +* * Neither the name of the OpenSim Project nor the +* names of its contributors may be used to endorse or promote products +* derived from this software without specific prior written permission. +* +* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS AND ANY +* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY +* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS +* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +* +*/ +using System; +using System.IO; +using System.Data; +using System.Data.SqlClient; +using System.Collections.Generic; +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MSSQL +{ + /// + /// A MySQL interface for the inventory server + /// + public class MSSQLInventoryData : IInventoryData + { + /// + /// The database manager + /// + private MSSQLManager database; + + /// + /// Loads and initialises this database plugin + /// + public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + TestTables(); + } + + #region Test and initialization code + + private void UpgradeFoldersTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + database.ExecuteResourceSql("CreateFoldersTable.sql"); + //database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql"); + return; + } + + } + + private void UpgradeItemsTable(string tableName) + { + // null as the version, indicates that the table didn't exist + if (tableName == null) + { + database.ExecuteResourceSql("CreateItemsTable.sql"); + //database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql"); + return; + } + } + + private void TestTables() + { + + Dictionary tableList = new Dictionary(); + + tableList["inventoryfolders"] = null; + tableList["inventoryitems"] = null; + + database.GetTableVersion(tableList); + + UpgradeFoldersTable(tableList["inventoryfolders"]); + UpgradeItemsTable(tableList["inventoryitems"]); + } + #endregion + + /// + /// The name of this DB provider + /// + /// Name of DB provider + public string getName() + { + return "MSSQL Inventory Data Interface"; + } + + /// + /// Closes this DB provider + /// + public void Close() + { + // Do nothing. + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + return database.getVersion(); + } + + /// + /// Returns a list of items in a specified folder + /// + /// The folder to search + /// A list containing inventory items + public List getInventoryInFolder(LLUUID folderID) + { + try + { + lock (database) + { + List items = new List(); + + Dictionary param = new Dictionary(); + param["parentFolderID"] = folderID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param); + IDataReader reader = result.ExecuteReader(); + + while(reader.Read()) + items.Add(readInventoryItem(reader)); + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a list of the root folders within a users inventory + /// + /// The user whos inventory is to be searched + /// A list of folder objects + public List getUserRootFolders(LLUUID user) + { + try + { + lock (database) + { + + Dictionary param = new Dictionary(); + param["uuid"] = user.ToStringHyphenated(); + param["zero"] = LLUUID.Zero.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns the users inventory root folder. + /// + /// + /// + public InventoryFolderBase getUserRootFolder(LLUUID user) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = user.ToStringHyphenated(); + param["zero"] = LLUUID.Zero.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + InventoryFolderBase rootFolder = null; + if (items.Count > 0) { + rootFolder = items[0]; //should only be one folder with parent set to zero (the root one). + } + + reader.Close(); + result.Dispose(); + + return rootFolder; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a list of folders in a users inventory contained within the specified folder + /// + /// The folder to search + /// A list of inventory folders + public List getInventoryFolders(LLUUID parentID) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["parentFolderID"] = parentID.ToStringHyphenated(); + + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param); + IDataReader reader = result.ExecuteReader(); + + List items = new List(); + + while(reader.Read()) + items.Add(readInventoryFolder(reader)); + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Reads a one item from an SQL result + /// + /// The SQL Result + /// the item read + public InventoryItemBase readInventoryItem(IDataReader reader) + { + try + { + InventoryItemBase item = new InventoryItemBase(); + + item.inventoryID = new LLUUID((string)reader["inventoryID"]); + item.assetID = new LLUUID((string)reader["assetID"]); + item.assetType = (int)reader["assetType"]; + item.parentFolderID = new LLUUID((string)reader["parentFolderID"]); + item.avatarID = new LLUUID((string)reader["avatarID"]); + item.inventoryName = (string)reader["inventoryName"]; + item.inventoryDescription = (string)reader["inventoryDescription"]; + item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]); + item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]); + item.invType = (int)reader["invType"]; + item.creatorsID = new LLUUID((string)reader["creatorID"]); + item.inventoryBasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); + item.inventoryEveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); + return item; + } + catch (SqlException e) + { + MainLog.Instance.Error(e.ToString()); + } + + return null; + } + + /// + /// Returns a specified inventory item + /// + /// The item to return + /// An inventory item + public InventoryItemBase getInventoryItem(LLUUID itemID) + { + + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["inventoryID"] = itemID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param); + IDataReader reader = result.ExecuteReader(); + + InventoryItemBase item = null; + if(reader.Read()) + item = readInventoryItem(reader); + + reader.Close(); + result.Dispose(); + + return item; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + return null; + } + + /// + /// Reads a list of inventory folders returned by a query. + /// + /// A MySQL Data Reader + /// A List containing inventory folders + protected InventoryFolderBase readInventoryFolder(IDataReader reader) + { + try + { + InventoryFolderBase folder = new InventoryFolderBase(); + folder.agentID = new LLUUID((string)reader["agentID"]); + folder.parentID = new LLUUID((string)reader["parentFolderID"]); + folder.folderID = new LLUUID((string)reader["folderID"]); + folder.name = (string)reader["folderName"]; + folder.type = (short)reader["type"]; + folder.version = (ushort)((int)reader["version"]); + return folder; + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + return null; + } + + + /// + /// Returns a specified inventory folder + /// + /// The folder to return + /// A folder class + public InventoryFolderBase getInventoryFolder(LLUUID folderID) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = folderID.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + reader.Read(); + InventoryFolderBase folder = readInventoryFolder(reader); + reader.Close(); + result.Dispose(); + + return folder; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Adds a specified item to the database + /// + /// The inventory item + public void addInventoryItem(InventoryItemBase item) + { + string sql = "INSERT INTO inventoryitems"; + sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]) VALUES "; + sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, @inventoryBasePermissions, @inventoryEveryOnePermissions);"; + + try + { + Dictionary param = new Dictionary(); + param["inventoryID"] = item.inventoryID.ToStringHyphenated(); + param["assetID"] = item.assetID.ToStringHyphenated(); + param["assetType"] = item.assetType.ToString(); + param["parentFolderID"] = item.parentFolderID.ToStringHyphenated(); + param["avatarID"] = item.avatarID.ToStringHyphenated(); + param["inventoryName"] = item.inventoryName; + param["inventoryDescription"] = item.inventoryDescription; + param["inventoryNextPermissions"] = item.inventoryNextPermissions.ToString(); + param["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString(); + param["invType"] = Convert.ToString(item.invType); + param["creatorID"] = item.creatorsID.ToStringHyphenated(); + param["inventoryBasePermissions"] = Convert.ToString(item.inventoryBasePermissions); + param["inventoryEveryOnePermissions"] = Convert.ToString(item.inventoryEveryOnePermissions); + + IDbCommand result = database.Query(sql, param); + result.ExecuteNonQuery(); + result.Dispose(); + + } + catch (SqlException e) + { + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Updates the specified inventory item + /// + /// Inventory item to update + public void updateInventoryItem(InventoryItemBase item) + { + SqlCommand command = new SqlCommand("UPDATE inventoryitems set inventoryID = @inventoryID, " + + "assetID = @assetID, " + + "assetType = @assetType" + + "parentFolderID = @parentFolderID" + + "avatarID = @avatarID" + + "inventoryName = @inventoryName"+ + "inventoryDescription = @inventoryDescription" + + "inventoryNextPermissions = @inventoryNextPermissions" + + "inventoryCurrentPermissions = @inventoryCurrentPermissions" + + "invType = @invType" + + "creatorID = @creatorID" + + "inventoryBasePermissions = @inventoryBasePermissions" + + "inventoryEveryOnePermissions = @inventoryEveryOnePermissions) where " + + "invenoryID = @keyInventoryID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@inventoryID", item.inventoryID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@assetID", item.assetID); + SqlParameter param3 = new SqlParameter("@assetType", item.assetType); + SqlParameter param4 = new SqlParameter("@parentFolderID", item.parentFolderID); + SqlParameter param5 = new SqlParameter("@avatarID", item.avatarID); + SqlParameter param6 = new SqlParameter("@inventoryName", item.inventoryName); + SqlParameter param7 = new SqlParameter("@inventoryDescription", item.inventoryDescription); + SqlParameter param8 = new SqlParameter("@inventoryNextPermissions", item.inventoryNextPermissions); + SqlParameter param9 = new SqlParameter("@inventoryCurrentPermissions", item.inventoryCurrentPermissions); + SqlParameter param10 = new SqlParameter("@invType", item.invType); + SqlParameter param11 = new SqlParameter("@creatorID", item.creatorsID); + SqlParameter param12 = new SqlParameter("@inventoryBasePermissions", item.inventoryBasePermissions); + SqlParameter param13 = new SqlParameter("@inventoryEveryOnePermissions", item.inventoryEveryOnePermissions); + SqlParameter param14 = new SqlParameter("@keyInventoryID", item.inventoryID.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + } + + /// + /// + /// + /// + public void deleteInventoryItem(LLUUID itemID) + { + try + { + Dictionary param = new Dictionary(); + param["uuid"] = itemID.ToStringHyphenated(); + + IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + + + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Creates a new inventory folder + /// + /// Folder to create + public void addInventoryFolder(InventoryFolderBase folder) + { + string sql = "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES "; + sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);"; + + + Dictionary param = new Dictionary(); + param["folderID"] = folder.folderID.ToStringHyphenated(); + param["agentID"] = folder.agentID.ToStringHyphenated(); + param["parentFolderID"] = folder.parentID.ToStringHyphenated(); + param["folderName"] = folder.name; + param["type"] = Convert.ToString(folder.type); + param["version"] = Convert.ToString(folder.version); + + try + { + IDbCommand result = database.Query(sql, param); + result.ExecuteNonQuery(); + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + + public void updateInventoryFolder(InventoryFolderBase folder) + { + SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " + + "agentID = @agentID, " + + "parentFolderID = @parentFolderID," + + "folderName = @folderName," + + "type = @type," + + "version = @version where " + + "folderID = @keyFolderID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@agentID", folder.agentID.ToStringHyphenated()); + SqlParameter param3 = new SqlParameter("@parentFolderID", folder.parentID.ToStringHyphenated()); + SqlParameter param4 = new SqlParameter("@folderName", folder.name); + SqlParameter param5 = new SqlParameter("@type", folder.type); + SqlParameter param6 = new SqlParameter("@version", folder.version); + SqlParameter param7 = new SqlParameter("@keyFolderID", folder.folderID.ToStringHyphenated()); + 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); + + try + { + command.ExecuteNonQuery(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + + + } + + + /// + /// Append a list of all the child folders of a parent folder + /// + /// list where folders will be appended + /// ID of parent + protected void getInventoryFolders(ref List folders, LLUUID parentID) + { + List subfolderList = getInventoryFolders(parentID); + + foreach (InventoryFolderBase f in subfolderList) + folders.Add(f); + } + + /// + /// Returns all child folders in the hierarchy from the parent folder and down + /// + /// The folder to get subfolders for + /// A list of inventory folders + protected List getFolderHierarchy(LLUUID parentID) + { + List folders = new List(); + getInventoryFolders(ref folders, parentID); + + for (int i = 0; i < folders.Count; i++) + getInventoryFolders(ref folders, folders[i].folderID); + + return folders; + } + + protected void deleteOneFolder(LLUUID folderID) + { + try + { + Dictionary param = new Dictionary(); + param["folderID"] = folderID.ToStringHyphenated(); + + IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + protected void deleteItemsInFolder(LLUUID folderID) + { + try + { + Dictionary param = new Dictionary(); + param["parentFolderID"] = folderID.ToStringHyphenated(); + + + IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (SqlException e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + } + + + /// + /// Delete an inventory folder + /// + /// Id of folder to delete + public void deleteInventoryFolder(LLUUID folderID) + { + lock (database) + { + List subFolders = getFolderHierarchy(folderID); + + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) + { + deleteOneFolder(f.folderID); + deleteItemsInFolder(f.folderID); + } + + //Delete the actual row + deleteOneFolder(folderID); + deleteItemsInFolder(folderID); + } + } + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs new file mode 100644 index 0000000..8a59bca --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs @@ -0,0 +1,104 @@ +/* +* Copyright (c) Contributors, http://opensimulator.org/ +* See CONTRIBUTORS.TXT for a full list of copyright holders. +* +* Redistribution and use in source and binary forms, with or without +* modification, are permitted provided that the following conditions are met: +* * Redistributions of source code must retain the above copyright +* notice, this list of conditions and the following disclaimer. +* * Redistributions in binary form must reproduce the above copyright +* notice, this list of conditions and the following disclaimer in the +* documentation and/or other materials provided with the distribution. +* * Neither the name of the OpenSim Project nor the +* names of its contributors may be used to endorse or promote products +* derived from this software without specific prior written permission. +* +* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS AND ANY +* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY +* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS +* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +* +*/ +using System; + +namespace OpenSim.Framework.Data.MSSQL +{ + /// + /// An interface to the log database for MySQL + /// + class MSSQLLogData : ILogData + { + /// + /// The database manager + /// + public MSSQLManager database; + + /// + /// Artificial constructor called when the plugin is loaded + /// + public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } + + /// + /// Saves a log item to the database + /// + /// The daemon triggering the event + /// The target of the action (region / agent UUID, etc) + /// The method call where the problem occured + /// The arguments passed to the method + /// How critical is this? + /// The message to log + 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 + { + database.Reconnect(); + } + } + + /// + /// Returns the name of this DB provider + /// + /// A string containing the DB provider name + public string getName() + { + return "MSSQL Logdata Interface"; + } + + /// + /// Closes the database provider + /// + public void Close() + { + // Do nothing. + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the provider version + public string getVersion() + { + return "0.1"; + } + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs index c0b2edf..4bca2e1 100644 --- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs +++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs @@ -29,19 +29,28 @@ using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; +using System.IO; +using System.Reflection; using libsecondlife; +using OpenSim.Framework.Console; + namespace OpenSim.Framework.Data.MSSQL { /// /// A management class for the MS SQL Storage Engine /// - internal class MSSqlManager + class MSSQLManager { /// /// The database connection object /// - private IDbConnection dbcon; + IDbConnection dbcon; + + /// + /// Connection string for ADO.net + /// + private string connectionString; /// /// Initialises and creates a new Sql connection and maintains it. @@ -51,14 +60,14 @@ namespace OpenSim.Framework.Data.MSSQL /// The username logging into the database /// The password for the user logging in /// Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'. - public MSSqlManager(string hostname, string database, string username, string password, string cpooling) + public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, string password) { try { - string connectionString = "Server=" + hostname + ";Database=" + database + ";User ID=" + username + - ";Password=" + password + ";Pooling=" + cpooling + ";"; + + connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + password+";"; dbcon = new SqlConnection(connectionString); - + TestTables(dbcon); dbcon.Open(); } catch (Exception e) @@ -67,6 +76,144 @@ namespace OpenSim.Framework.Data.MSSQL } } + private bool TestTables(IDbConnection conn) + { + IDbCommand cmd = this.Query("SELECT * FROM regions", new Dictionary()); + //SqlCommand cmd = (SqlCommand)dbcon.CreateCommand(); + //cmd.CommandText = "SELECT * FROM regions"; + try + { + conn.Open(); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + conn.Close(); + } + catch (Exception) + { + MainLog.Instance.Verbose("DATASTORE", "MSSQL Database doesn't exist... creating"); + InitDB(conn); + } + return true; + } + + private void InitDB(IDbConnection conn) + { + string createRegions = defineTable(createRegionsTable()); + Dictionary param = new Dictionary(); + IDbCommand pcmd = this.Query(createRegions, param); + if (conn.State == ConnectionState.Closed) { + conn.Open(); + } + pcmd.ExecuteNonQuery(); + pcmd.Dispose(); + + this.ExecuteResourceSql("Mssql-users.sql"); + this.ExecuteResourceSql("Mssql-agents.sql"); + this.ExecuteResourceSql("Mssql-logs.sql"); + + conn.Close(); + + } + + private DataTable createRegionsTable() + { + DataTable regions = new DataTable("regions"); + + createCol(regions, "regionHandle", typeof(ulong)); + createCol(regions, "regionName", typeof(System.String)); + createCol(regions, "uuid", typeof(System.String)); + + createCol(regions, "regionRecvKey", typeof(System.String)); + createCol(regions, "regionSecret", typeof(System.String)); + createCol(regions, "regionSendKey", typeof(System.String)); + + createCol(regions, "regionDataURI", typeof(System.String)); + createCol(regions, "serverIP", typeof(System.String)); + createCol(regions, "serverPort", typeof(System.String)); + createCol(regions, "serverURI", typeof(System.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(System.String)); + createCol(regions, "regionAssetRecvKey", typeof(System.String)); + createCol(regions, "regionAssetSendKey", typeof(System.String)); + + createCol(regions, "regionUserURI", typeof(System.String)); + createCol(regions, "regionUserRecvKey", typeof(System.String)); + createCol(regions, "regionUserSendKey", typeof(System.String)); + + createCol(regions, "regionMapTexture", typeof(System.String)); + createCol(regions, "serverHttpPort", typeof(System.String)); + createCol(regions, "serverRemotingPort", typeof(uint)); + + // Add in contraints + regions.PrimaryKey = new DataColumn[] { regions.Columns["UUID"] }; + return regions; + } + + protected static void createCol(DataTable dt, string name, System.Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + protected static string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = ""; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { // a map function would rock so much here + subsql += ",\n"; + } + + subsql += col.ColumnName + " " + SqlType(col.DataType); + if (col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + sql += subsql; + sql += ")"; + return sql; + } + + + // this is something we'll need to implement for each db + // slightly differently. + private static string SqlType(Type type) + { + if (type == typeof(System.String)) + { + return "varchar(255)"; + } + else if (type == typeof(System.Int32)) + { + return "integer"; + } + else if (type == typeof(System.Double)) + { + return "float"; + } + else if (type == typeof(System.Byte[])) + { + return "image"; + } + else + { + return "varchar(255)"; + } + } + /// /// Shuts down the database connection /// @@ -77,6 +224,29 @@ namespace OpenSim.Framework.Data.MSSQL } /// + /// Reconnects to the database + /// + public void Reconnect() + { + lock (dbcon) + { + try + { + //string connectionString = "Data Source=WRK-OU-738\\SQLEXPRESS;Initial Catalog=rex;Persist Security Info=True;User ID=sa;Password=rex"; + // Close the DB connection + dbcon.Close(); + // Try reopen it + dbcon = new SqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + MainLog.Instance.Error("Unable to reconnect to database " + e.ToString()); + } + } + } + + /// /// Runs a query with protection against SQL Injection by using parameterised input. /// /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y @@ -84,14 +254,14 @@ namespace OpenSim.Framework.Data.MSSQL /// A Sql DB Command public IDbCommand Query(string sql, Dictionary parameters) { - SqlCommand dbcommand = (SqlCommand) dbcon.CreateCommand(); - dbcommand.CommandText = sql; + SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand(); + dbcommand.CommandText = sql; foreach (KeyValuePair param in parameters) { dbcommand.Parameters.AddWithValue(param.Key, param.Value); } - - return (IDbCommand) dbcommand; + + return (IDbCommand)dbcommand; } /// @@ -99,74 +269,201 @@ namespace OpenSim.Framework.Data.MSSQL /// /// An active database reader /// A region row - public RegionProfileData getRow(IDataReader reader) + public RegionProfileData getRegionRow(IDataReader reader) { RegionProfileData regionprofile = new RegionProfileData(); if (reader.Read()) { // Region Main - regionprofile.regionHandle = (ulong) reader["regionHandle"]; - regionprofile.regionName = (string) reader["regionName"]; - regionprofile.UUID = new LLUUID((string) reader["uuid"]); + regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]); + regionprofile.regionName = (string)reader["regionName"]; + regionprofile.UUID = new LLUUID((string)reader["uuid"]); // Secrets - regionprofile.regionRecvKey = (string) reader["regionRecvKey"]; - regionprofile.regionSecret = (string) reader["regionSecret"]; - regionprofile.regionSendKey = (string) reader["regionSendKey"]; + regionprofile.regionRecvKey = (string)reader["regionRecvKey"]; + regionprofile.regionSecret = (string)reader["regionSecret"]; + regionprofile.regionSendKey = (string)reader["regionSendKey"]; // Region Server - regionprofile.regionDataURI = (string) reader["regionDataURI"]; + regionprofile.regionDataURI = (string)reader["regionDataURI"]; regionprofile.regionOnline = false; // Needs to be pinged before this can be set. - regionprofile.serverIP = (string) reader["serverIP"]; - regionprofile.serverPort = (uint) reader["serverPort"]; - regionprofile.serverURI = (string) reader["serverURI"]; + 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 = (uint) ((int) reader["locX"]); - regionprofile.regionLocY = (uint) ((int) reader["locY"]); - regionprofile.regionLocZ = (uint) ((int) reader["locZ"]); + regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]); + regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]); + regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]); // Neighbours - 0 = No Override - regionprofile.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; - regionprofile.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; - regionprofile.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; - regionprofile.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; + 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"]; + 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.regionUserURI = (string)reader["regionUserURI"]; + regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"]; + regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"]; + + // World Map Addition + string tempRegionMap = reader["regionMapTexture"].ToString(); + if (tempRegionMap != "") + { + regionprofile.regionMapTextureID = new LLUUID(tempRegionMap); + } + else + { + regionprofile.regionMapTextureID = new LLUUID(); + } } else { + reader.Close(); throw new Exception("No rows to return"); + } return regionprofile; } /// + /// Reads a user profile from an active data reader + /// + /// An active database reader + /// A user profile + public UserProfileData readUserRow(IDataReader reader) + { + UserProfileData retval = new UserProfileData(); + + if (reader.Read()) + { + retval.UUID = new LLUUID((string)reader["UUID"]); + retval.username = (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 LLVector3( + Convert.ToSingle(reader["homeLocationX"].ToString()), + Convert.ToSingle(reader["homeLocationY"].ToString()), + Convert.ToSingle(reader["homeLocationZ"].ToString())); + retval.homeLookAt = new LLVector3( + 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.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); + retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); + + retval.profileAboutText = (string)reader["profileAboutText"]; + retval.profileFirstText = (string)reader["profileFirstText"]; + + retval.profileImage = new LLUUID((string)reader["profileImage"]); + retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]); + + } + else + { + return null; + } + return retval; + } + + /// + /// Reads an agent row from a database reader + /// + /// An active database reader + /// A user session agent + public UserAgentData readAgentRow(IDataReader reader) + { + UserAgentData retval = new UserAgentData(); + + if (reader.Read()) + { + // Agent IDs + retval.UUID = new LLUUID((string)reader["UUID"]); + retval.sessionID = new LLUUID((string)reader["sessionID"]); + retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]); + + // Agent Who? + retval.agentIP = (string)reader["agentIP"]; + retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString()); + retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString()); + + // Login/Logout times (UNIX Epoch) + retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString()); + retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); + + // Current position + retval.currentRegion = (string)reader["currentRegion"]; + retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); + LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos); + } + else + { + return null; + } + return retval; + } + + 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 LLUUID((string)reader["id"]); + asset.InvType = Convert.ToSByte(reader["invType"]); + asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false; + asset.Name = (string)reader["name"]; + asset.Type = Convert.ToSByte(reader["assetType"]); + + } + else + { + return null; // throw new Exception("No rows to return"); + } + return asset; + } + + /// /// Creates a new region in the database /// /// The region profile to insert /// Successful? - public bool insertRow(RegionProfileData profile) + public bool insertRegionRow(RegionProfileData profile) { - string sql = - "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; - sql += - "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; - sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES "; + + //Insert new region + string sql = "INSERT INTO regions ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], "; + sql += "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], "; + sql += "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort]) VALUES "; sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; - sql += - "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; - sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);"; + sql += "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; + sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);"; Dictionary parameters = new Dictionary(); @@ -174,6 +471,7 @@ namespace OpenSim.Framework.Data.MSSQL parameters["regionName"] = profile.regionName; parameters["uuid"] = profile.UUID.ToString(); parameters["regionRecvKey"] = profile.regionRecvKey; + parameters["regionSecret"] = profile.regionSecret; parameters["regionSendKey"] = profile.regionSendKey; parameters["regionDataURI"] = profile.regionDataURI; parameters["serverIP"] = profile.serverIP; @@ -192,6 +490,56 @@ namespace OpenSim.Framework.Data.MSSQL parameters["regionUserURI"] = profile.regionUserURI; parameters["regionUserRecvKey"] = profile.regionUserRecvKey; parameters["regionUserSendKey"] = profile.regionUserSendKey; + parameters["regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated(); + parameters["serverHttpPort"] = profile.httpPort.ToString(); + parameters["serverRemotingPort"] = profile.remotingPort.ToString(); + + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error("MSSQLManager : " + e.ToString()); + + } + + return returnval; + + } + + + + /// + /// 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; @@ -204,12 +552,190 @@ namespace OpenSim.Framework.Data.MSSQL result.Dispose(); } - catch (Exception) + catch (Exception e) { + MainLog.Instance.Error(e.ToString()); return false; } return returnval; } + + + /// + /// Creates a new user and inserts it into the database + /// + /// 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? + public bool insertUserRow(libsecondlife.LLUUID 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, + libsecondlife.LLUUID profileImage, libsecondlife.LLUUID firstImage) + { + string sql = "INSERT INTO users "; + 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]) 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);"; + + Dictionary parameters = new Dictionary(); + parameters["UUID"] = uuid.ToStringHyphenated(); + 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"] = ""; + parameters["userAssetURI"] = ""; + parameters["profileCanDoMask"] = "0"; + parameters["profileWantDoMask"] = "0"; + parameters["profileAboutText"] = ""; + parameters["profileFirstText"] = ""; + parameters["profileImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated(); + parameters["profileFirstImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated(); + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Execute a SQL statement stored in a resource, as a string + /// + /// + public void ExecuteResourceSql(string name) + { + try + { + + SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + catch (Exception e) + { + MainLog.Instance.Error("Unable to execute query " + e.ToString()); + } + } + + public SqlConnection getConnection() + { + return (SqlConnection)dbcon; + } + + /// + /// Given a list of tables, return the version of the tables, as seen in the database + /// + /// + public void GetTableVersion(Dictionary tableList) + { + lock (dbcon) + { + Dictionary param = new Dictionary(); + param["dbname"] = dbcon.Database; + IDbCommand tablesCmd = this.Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param); + using (IDataReader tables = tablesCmd.ExecuteReader()) + { + while (tables.Read()) + { + try + { + string tableName = (string)tables["TABLE_NAME"]; + if (tableList.ContainsKey(tableName)) + tableList[tableName] = tableName; + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + } + tables.Close(); + } + } + } + + private string getResourceString(string name) + { + Assembly assem = this.GetType().Assembly; + string[] names = assem.GetManifestResourceNames(); + + foreach (string s in names) + if (s.EndsWith(name)) + using (Stream resource = assem.GetManifestResourceStream(s)) + { + using (StreamReader resourceReader = new StreamReader(resource)) + { + string resourceString = resourceReader.ReadToEnd(); + return resourceString; + } + } + throw new Exception(string.Format("Resource '{0}' was not found", name)); + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + System.Reflection.Module module = this.GetType().Module; + string dllName = module.Assembly.ManifestModule.Name; + Version dllVersion = module.Assembly.GetName().Version; + + + return string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, dllVersion.Revision); + } + } -} \ No newline at end of file + + + +} diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs new file mode 100644 index 0000000..2c4dfdc --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs @@ -0,0 +1,452 @@ +/* +* Copyright (c) Contributors, http://opensimulator.org/ +* See CONTRIBUTORS.TXT for a full list of copyright holders. +* +* Redistribution and use in source and binary forms, with or without +* modification, are permitted provided that the following conditions are met: +* * Redistributions of source code must retain the above copyright +* notice, this list of conditions and the following disclaimer. +* * Redistributions in binary form must reproduce the above copyright +* notice, this list of conditions and the following disclaimer in the +* documentation and/or other materials provided with the distribution. +* * Neither the name of the OpenSim Project nor the +* names of its contributors may be used to endorse or promote products +* derived from this software without specific prior written permission. +* +* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS AND ANY +* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY +* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS +* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +* +*/ +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SqlClient; +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MSSQL +{ + /// + /// A database interface class to a user profile storage system + /// + class MSSQLUserData : IUserData + { + /// + /// Database manager for MySQL + /// + public MSSQLManager database; + + /// + /// Loads and initialises the MySQL storage plugin + /// + public void Initialise() + { + // Load from an INI file connection details + // TODO: move this to XML? + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword); + } + + /// + /// Searches the database for a specified user profile + /// + /// The account name of the user + /// A user profile + public UserProfileData GetUserByName(string name) + { + return GetUserByName(name.Split(' ')[0], name.Split(' ')[1]); + } + + /// + /// 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 + public UserProfileData GetUserByName(string user, string last) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["first"] = user; + param["second"] = last; + + IDbCommand result = database.Query("SELECT * FROM users WHERE username = @first AND lastname = @second", param); + IDataReader reader = result.ExecuteReader(); + + UserProfileData row = database.readUserRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + public List GeneratePickerResults(LLUUID queryID, string query) + { + List returnlist = new List(); + string[] querysplit; + querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["first"] = querysplit[0]; + param["second"] = querysplit[1]; + + IDbCommand result = + database.Query("SELECT UUID,username,surname FROM users WHERE username = @first AND lastname = @second", param); + IDataReader reader = result.ExecuteReader(); + + + while (reader.Read()) + { + OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; + returnlist.Add(user); + + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + + + + } + else if (querysplit.Length == 1) + { + + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["first"] = querysplit[0]; + param["second"] = querysplit[1]; + + IDbCommand result = + database.Query("SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", param); + IDataReader reader = result.ExecuteReader(); + + + while (reader.Read()) + { + OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string)reader["UUID"]); + user.firstName = (string)reader["username"]; + user.lastName = (string)reader["surname"]; + returnlist.Add(user); + + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return returnlist; + } + } + return returnlist; + } + + /// + /// Searches the database for a specified user profile by UUID + /// + /// The account ID + /// The users profile + public UserProfileData GetUserByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM users WHERE UUID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserProfileData row = database.readUserRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a user session searching by name + /// + /// The account name + /// The users session + public UserAgentData GetAgentByName(string name) + { + return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); + } + + /// + /// Returns a user session by account name + /// + /// First part of the users account name + /// Second part of the users account name + /// The users session + public UserAgentData GetAgentByName(string user, string last) + { + UserProfileData profile = GetUserByName(user, last); + return GetAgentByUUID(profile.UUID); + } + + /// + /// Returns an agent session by account UUID + /// + /// The accounts UUID + /// The users session + public UserAgentData GetAgentByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["uuid"] = uuid.ToStringHyphenated(); + + IDbCommand result = database.Query("SELECT * FROM agents WHERE UUID = @uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserAgentData row = database.readAgentRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + return null; + } + } + + /// + /// Creates a new users profile + /// + /// The user profile to create + public void AddNewUserProfile(UserProfileData user) + { + try + { + lock (database) + { + database.insertUserRow(user.UUID, user.username, 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.profileCanDoMask, user.profileWantDoMask, + user.profileAboutText, user.profileFirstText, user.profileImage, user.profileFirstImage); + } + } + catch (Exception e) + { + database.Reconnect(); + MainLog.Instance.Error(e.ToString()); + } + + } + + /// + /// Creates a new agent + /// + /// The agent to create + public void AddNewUserAgent(UserAgentData agent) + { + // Do nothing. + } + + + public bool UpdateUserProfile(UserProfileData user) + { + + SqlCommand command = new SqlCommand("UPDATE users 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 where " + + "UUID = @keyUUUID;", database.getConnection()); + SqlParameter param1 = new SqlParameter("@uuid", user.UUID.ToStringHyphenated()); + SqlParameter param2 = new SqlParameter("@username", user.username); + 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.profileCanDoMask)); + SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.profileWantDoMask)); + SqlParameter param19 = new SqlParameter("@profileAboutText", user.profileAboutText); + SqlParameter param20 = new SqlParameter("@profileFirstText", user.profileFirstText); + SqlParameter param21 = new SqlParameter("@profileImage", libsecondlife.LLUUID.Zero.ToStringHyphenated()); + SqlParameter param22 = new SqlParameter("@profileFirstImage", libsecondlife.LLUUID.Zero.ToStringHyphenated()); + SqlParameter param23 = new SqlParameter("@keyUUUID", user.UUID.ToStringHyphenated()); + 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); + try + { + int affected = command.ExecuteNonQuery(); + if (affected != 0) { + return true; + } else { + return false; + } + } + catch (Exception e) + { + MainLog.Instance.Error(e.ToString()); + } + return false; + } + + /// + /// Performs a money transfer request between two accounts + /// + /// The senders account ID + /// The recievers account ID + /// The amount to transfer + /// Success? + public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount) + { + return false; + } + + /// + /// Performs an inventory transfer request between two accounts + /// + /// TODO: Move to inventory server + /// The senders account ID + /// The recievers account ID + /// The item to transfer + /// Success? + public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) + { + return false; + } + + /// + /// Database provider name + /// + /// Provider name + public string getName() + { + return "MSSQL Userdata Interface"; + } + + /// + /// Database provider version + /// + /// provider version + public string GetVersion() + { + return database.getVersion(); + } + + /// + /// Not implemented + /// + /// + public void runQuery(string query) + { + } + + } +} \ No newline at end of file diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql new file mode 100644 index 0000000..c7cb21a --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql @@ -0,0 +1,19 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [invType] [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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql new file mode 100644 index 0000000..95d183a --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql @@ -0,0 +1,27 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql new file mode 100644 index 0000000..40c047e --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql @@ -0,0 +1,39 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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](64) 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, + 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] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql new file mode 100644 index 0000000..ad53173 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql @@ -0,0 +1,37 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql new file mode 100644 index 0000000..3b747d8 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql @@ -0,0 +1,20 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [logs] ( + [logID] [int] NOT NULL, + [target] [varchar](36) default NULL, + [server] [varchar](64) default NULL, + [method] [varchar](64) default NULL, + [arguments] [varchar](255) default NULL, + [priority] [int] default NULL, + [message] [ntext], + PRIMARY KEY CLUSTERED +( + [logID] 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/Framework/Data.MSSQL/Resources/Mssql-users.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql new file mode 100644 index 0000000..3f5f896 --- /dev/null +++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql @@ -0,0 +1,41 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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, + 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] diff --git a/OpenSim/Region/Application/OpenSimMain.cs b/OpenSim/Region/Application/OpenSimMain.cs index 2e089b8..b941e2d 100644 --- a/OpenSim/Region/Application/OpenSimMain.cs +++ b/OpenSim/Region/Application/OpenSimMain.cs @@ -426,6 +426,13 @@ namespace OpenSim { assetServer = new GridAssetClient(m_networkServersInfo.AssetURL); } + else if (m_assetStorage == "mssql") + { + SQLAssetServer sqlAssetServer = new SQLAssetServer("OpenSim.Framework.Data.MSSQL.dll"); + sqlAssetServer.LoadDefaultAssets(); + assetServer = sqlAssetServer; + //assetServer = new GridAssetClient(""); + } else { SQLAssetServer sqlAssetServer = new SQLAssetServer(m_standaloneAssetPlugin); diff --git a/OpenSim/Region/Storage/OpenSim.DataStore.MSSQL/MSSQLDataStore.cs b/OpenSim/Region/Storage/OpenSim.DataStore.MSSQL/MSSQLDataStore.cs new file mode 100644 index 0000000..5e4bb89 --- /dev/null +++ b/OpenSim/Region/Storage/OpenSim.DataStore.MSSQL/MSSQLDataStore.cs @@ -0,0 +1,1032 @@ +/* +* Copyright (c) Contributors, http://opensimulator.org/ +* See CONTRIBUTORS.TXT for a full list of copyright holders. +* +* Redistribution and use in source and binary forms, with or without +* modification, are permitted provided that the following conditions are met: +* * Redistributions of source code must retain the above copyright +* notice, this list of conditions and the following disclaimer. +* * Redistributions in binary form must reproduce the above copyright +* notice, this list of conditions and the following disclaimer in the +* documentation and/or other materials provided with the distribution. +* * Neither the name of the OpenSim Project nor the +* names of its contributors may be used to endorse or promote products +* derived from this software without specific prior written permission. +* +* THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS AS IS AND ANY +* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +* DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY +* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES +* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; +* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS +* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +* +*/ + +using System; +using System.Collections.Generic; +using System.Data; +using System.Data.SqlClient; +using System.IO; +using libsecondlife; +using OpenSim.Framework; +using OpenSim.Framework.Data; +using OpenSim.Framework.Console; +using OpenSim.Region.Environment.Interfaces; +using OpenSim.Region.Environment.LandManagement; +using OpenSim.Region.Environment.Scenes; + +namespace OpenSim.DataStore.MSSQL +{ + public class MSSQLDataStore : IRegionDataStore + { + private const string primSelect = "select * from prims"; + private const string shapeSelect = "select * from primshapes"; + private const string terrainSelect = "select * from terrain"; + + private DataSet ds; + private SqlDataAdapter primDa; + private SqlDataAdapter shapeDa; + private SqlDataAdapter terrainDa; + + /// + /// + /// + /// + /// + public void Initialise(string dbfile) + { + IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini"); + string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source"); + string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog"); + string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info"); + string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + + string connectionString = "Data Source=" + settingDataSource + ";Initial Catalog=" + settingInitialCatalog + ";Persist Security Info=" + settingPersistSecurityInfo + ";User ID=" + settingUserId + ";Password=" + settingPassword + ";"; + + ds = new DataSet(); + + MainLog.Instance.Verbose("DATASTORE", "MSSQL - connecting: " + settingInitialCatalog); + SqlConnection conn = new SqlConnection(connectionString); + SqlCommand primSelectCmd = new SqlCommand(primSelect, conn); + primDa = new SqlDataAdapter(primSelectCmd); + // SqlCommandBuilder primCb = new SqlCommandBuilder(primDa); + + SqlCommand shapeSelectCmd = new SqlCommand(shapeSelect, conn); + shapeDa = new SqlDataAdapter(shapeSelectCmd); + // SqlCommandBuilder shapeCb = new SqlCommandBuilder(shapeDa); + + SqlCommand terrainSelectCmd = new SqlCommand(terrainSelect, conn); + terrainDa = new SqlDataAdapter(terrainSelectCmd); + + + // We fill the data set, now we've got copies in memory for the information + // TODO: see if the linkage actually holds. + // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema"); + TestTables(conn); + + lock(ds) { + ds.Tables.Add(createPrimTable()); + setupPrimCommands(primDa, conn); + primDa.Fill(ds.Tables["prims"]); + + ds.Tables.Add(createShapeTable()); + setupShapeCommands(shapeDa, conn); + + ds.Tables.Add(createTerrainTable()); + setupTerrainCommands(terrainDa, conn); + + // WORKAROUND: This is a work around for Sql on + // windows, which gets really unhappy with blob columns + // that have no sample data in them. At some point we + // need to actually find a proper way to handle this. + try + { + shapeDa.Fill(ds.Tables["primshapes"]); + } + catch (Exception) + { + MainLog.Instance.Verbose("DATASTORE", "Caught fill error on primshapes table"); + } + try + { + terrainDa.Fill(ds.Tables["terrain"]); + } + catch (Exception) + { + MainLog.Instance.Verbose("DATASTORE", "Caught fill error on terrain table"); + } + return; + } + } + + public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID) + { + lock (ds) { + foreach (SceneObjectPart prim in obj.Children.Values) + { + MainLog.Instance.Verbose("DATASTORE", "Adding obj: " + obj.UUID + " to region: " + regionUUID); + addPrim(prim, obj.UUID, regionUUID); + } + } + + Commit(); + // MainLog.Instance.Verbose("Dump of prims:", ds.GetXml()); + } + + public void RemoveObject(LLUUID obj, LLUUID regionUUID) + { + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + string selectExp = "SceneGroupID = '" + obj.ToString() + "'"; + lock (ds) { + DataRow[] primRows = prims.Select(selectExp); + foreach (DataRow row in primRows) + { + LLUUID uuid = new LLUUID((string)row["UUID"]); + DataRow shapeRow = shapes.Rows.Find(uuid); + if (shapeRow != null) + { + shapeRow.Delete(); + } + row.Delete(); + } + } + + Commit(); + } + + public List LoadObjects(LLUUID regionUUID) + { + Dictionary createdObjects = new Dictionary(); + + List retvals = new List(); + + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + string byRegion = "RegionUUID = '" + regionUUID.ToString() + "'"; + string orderByParent = "ParentID ASC"; + + lock (ds) { + DataRow[] primsForRegion = prims.Select(byRegion, orderByParent); + MainLog.Instance.Verbose("DATASTORE", "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID); + + foreach (DataRow primRow in primsForRegion) + { + try + { + string uuid = (string)primRow["UUID"]; + string objID = (string)primRow["SceneGroupID"]; + if (uuid == objID) //is new SceneObjectGroup ? + { + SceneObjectGroup group = new SceneObjectGroup(); + SceneObjectPart prim = buildPrim(primRow); + DataRow shapeRow = shapes.Rows.Find(prim.UUID); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + MainLog.Instance.Notice("No shape found for prim in storage, so setting default box shape"); + prim.Shape = BoxShape.Default; + } + group.AddPart(prim); + group.RootPart = prim; + + createdObjects.Add(group.UUID, group); + retvals.Add(group); + } + else + { + SceneObjectPart prim = buildPrim(primRow); + DataRow shapeRow = shapes.Rows.Find(prim.UUID); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + MainLog.Instance.Notice("No shape found for prim in storage, so setting default box shape"); + prim.Shape = BoxShape.Default; + } + createdObjects[new LLUUID(objID)].AddPart(prim); + } + } + catch (Exception e) + { + MainLog.Instance.Error("DATASTORE", "Failed create prim object, exception and data follows"); + MainLog.Instance.Verbose("DATASTORE", e.ToString()); + foreach (DataColumn col in prims.Columns) + { + MainLog.Instance.Verbose("DATASTORE", "Col: " + col.ColumnName + " => " + primRow[col]); + } + } + } + } + return retvals; + } + + + public void StoreTerrain(double[,] ter, LLUUID regionID) + { + int revision = Util.UnixTimeSinceEpoch(); + + MainLog.Instance.Verbose("DATASTORE", "Storing terrain revision r" + revision.ToString()); + + DataTable terrain = ds.Tables["terrain"]; + lock (ds) + { + DataRow newrow = terrain.NewRow(); + fillTerrainRow(newrow, regionID, revision, ter); + terrain.Rows.Add(newrow); + + Commit(); + } + } + + public double[,] LoadTerrain(LLUUID regionID) + { + double[,] terret = new double[256, 256]; + terret.Initialize(); + + DataTable terrain = ds.Tables["terrain"]; + + lock (ds) + { + DataRow[] rows = terrain.Select("RegionUUID = '" + regionID.ToString() + "'", "Revision DESC"); + + int rev = 0; + + if (rows.Length > 0) + { + DataRow row = rows[0]; + + byte[] heightmap = (byte[])row["Heightfield"]; + for (int x = 0; x < 256; x++) + { + for (int y = 0; y < 256; y++) + { + terret[x, y] = BitConverter.ToDouble(heightmap, ((x * 256) + y) * 8); + } + } + + rev = (int)row["Revision"]; + } + else + { + MainLog.Instance.Verbose("DATASTORE", "No terrain found for region"); + return null; + } + + + MainLog.Instance.Verbose("DATASTORE", "Loaded terrain revision r" + rev.ToString()); + } + + return terret; + } + + public void RemoveLandObject(uint id) + { + + } + + public void StoreParcel(Land parcel) + { + + } + + public List LoadLandObjects() + { + return new List(); + } + + public void Commit() + { + lock (ds) { + primDa.Update(ds, "prims"); + shapeDa.Update(ds, "primshapes"); + terrainDa.Update(ds, "terrain"); + ds.AcceptChanges(); + } + } + + public void Shutdown() + { + Commit(); + } + + /*********************************************************************** + * + * Database Definition Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + private void createCol(DataTable dt, string name, System.Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + private DataTable createTerrainTable() + { + DataTable terrain = new DataTable("terrain"); + + createCol(terrain, "RegionUUID", typeof(String)); + createCol(terrain, "Revision", typeof(Int32)); + createCol(terrain, "Heightfield", typeof(Byte[])); + + return terrain; + } + + private DataTable createPrimTable() + { + DataTable prims = new DataTable("prims"); + + createCol(prims, "UUID", typeof(System.String)); + createCol(prims, "RegionUUID", typeof(System.String)); + createCol(prims, "ParentID", typeof(System.Int32)); + createCol(prims, "CreationDate", typeof(System.Int32)); + createCol(prims, "Name", typeof(System.String)); + createCol(prims, "SceneGroupID", typeof(System.String)); + // various text fields + createCol(prims, "Text", typeof(System.String)); + createCol(prims, "Description", typeof(System.String)); + createCol(prims, "SitName", typeof(System.String)); + createCol(prims, "TouchName", typeof(System.String)); + // permissions + createCol(prims, "ObjectFlags", typeof(System.Int32)); + createCol(prims, "CreatorID", typeof(System.String)); + createCol(prims, "OwnerID", typeof(System.String)); + createCol(prims, "GroupID", typeof(System.String)); + createCol(prims, "LastOwnerID", typeof(System.String)); + createCol(prims, "OwnerMask", typeof(System.Int32)); + createCol(prims, "NextOwnerMask", typeof(System.Int32)); + createCol(prims, "GroupMask", typeof(System.Int32)); + createCol(prims, "EveryoneMask", typeof(System.Int32)); + createCol(prims, "BaseMask", typeof(System.Int32)); + // vectors + createCol(prims, "PositionX", typeof(System.Double)); + createCol(prims, "PositionY", typeof(System.Double)); + createCol(prims, "PositionZ", typeof(System.Double)); + createCol(prims, "GroupPositionX", typeof(System.Double)); + createCol(prims, "GroupPositionY", typeof(System.Double)); + createCol(prims, "GroupPositionZ", typeof(System.Double)); + createCol(prims, "VelocityX", typeof(System.Double)); + createCol(prims, "VelocityY", typeof(System.Double)); + createCol(prims, "VelocityZ", typeof(System.Double)); + createCol(prims, "AngularVelocityX", typeof(System.Double)); + createCol(prims, "AngularVelocityY", typeof(System.Double)); + createCol(prims, "AngularVelocityZ", typeof(System.Double)); + createCol(prims, "AccelerationX", typeof(System.Double)); + createCol(prims, "AccelerationY", typeof(System.Double)); + createCol(prims, "AccelerationZ", typeof(System.Double)); + // quaternions + createCol(prims, "RotationX", typeof(System.Double)); + createCol(prims, "RotationY", typeof(System.Double)); + createCol(prims, "RotationZ", typeof(System.Double)); + createCol(prims, "RotationW", typeof(System.Double)); + + // Add in contraints + prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; + + return prims; + } + + private DataTable createShapeTable() + { + DataTable shapes = new DataTable("primshapes"); + createCol(shapes, "UUID", typeof(System.String)); + // shape is an enum + createCol(shapes, "Shape", typeof(System.Int32)); + // vectors + createCol(shapes, "ScaleX", typeof(System.Double)); + createCol(shapes, "ScaleY", typeof(System.Double)); + createCol(shapes, "ScaleZ", typeof(System.Double)); + // paths + createCol(shapes, "PCode", typeof(System.Int32)); + createCol(shapes, "PathBegin", typeof(System.Int32)); + createCol(shapes, "PathEnd", typeof(System.Int32)); + createCol(shapes, "PathScaleX", typeof(System.Int32)); + createCol(shapes, "PathScaleY", typeof(System.Int32)); + createCol(shapes, "PathShearX", typeof(System.Int32)); + createCol(shapes, "PathShearY", typeof(System.Int32)); + createCol(shapes, "PathSkew", typeof(System.Int32)); + createCol(shapes, "PathCurve", typeof(System.Int32)); + createCol(shapes, "PathRadiusOffset", typeof(System.Int32)); + createCol(shapes, "PathRevolutions", typeof(System.Int32)); + createCol(shapes, "PathTaperX", typeof(System.Int32)); + createCol(shapes, "PathTaperY", typeof(System.Int32)); + createCol(shapes, "PathTwist", typeof(System.Int32)); + createCol(shapes, "PathTwistBegin", typeof(System.Int32)); + // profile + createCol(shapes, "ProfileBegin", typeof(System.Int32)); + createCol(shapes, "ProfileEnd", typeof(System.Int32)); + createCol(shapes, "ProfileCurve", typeof(System.Int32)); + createCol(shapes, "ProfileHollow", typeof(System.Int32)); + // text TODO: this isn't right, but I'm not sure the right + // way to specify this as a blob atm + createCol(shapes, "Texture", typeof(System.Byte[])); + createCol(shapes, "ExtraParams", typeof(System.Byte[])); + + shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] }; + + return shapes; + } + + /*********************************************************************** + * + * Convert between ADO.NET <=> OpenSim Objects + * + * These should be database independant + * + **********************************************************************/ + + private SceneObjectPart buildPrim(DataRow row) + { + // TODO: this doesn't work yet because something more + // interesting has to be done to actually get these values + // back out. Not enough time to figure it out yet. + SceneObjectPart prim = new SceneObjectPart(); + prim.UUID = new LLUUID((String)row["UUID"]); + // explicit conversion of integers is required, which sort + // of sucks. No idea if there is a shortcut here or not. + prim.ParentID = Convert.ToUInt32(row["ParentID"]); + prim.CreationDate = Convert.ToInt32(row["CreationDate"]); + prim.Name = (String)row["Name"]; + // various text fields + prim.Text = (String)row["Text"]; + prim.Description = (String)row["Description"]; + prim.SitName = (String)row["SitName"]; + prim.TouchName = (String)row["TouchName"]; + // permissions + prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]); + prim.CreatorID = new LLUUID((String)row["CreatorID"]); + prim.OwnerID = new LLUUID((String)row["OwnerID"]); + prim.GroupID = new LLUUID((String)row["GroupID"]); + prim.LastOwnerID = new LLUUID((String)row["LastOwnerID"]); + prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]); + prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]); + prim.GroupMask = Convert.ToUInt32(row["GroupMask"]); + prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]); + prim.BaseMask = Convert.ToUInt32(row["BaseMask"]); + // vectors + prim.OffsetPosition = new LLVector3( + Convert.ToSingle(row["PositionX"]), + Convert.ToSingle(row["PositionY"]), + Convert.ToSingle(row["PositionZ"]) + ); + prim.GroupPosition = new LLVector3( + Convert.ToSingle(row["GroupPositionX"]), + Convert.ToSingle(row["GroupPositionY"]), + Convert.ToSingle(row["GroupPositionZ"]) + ); + prim.Velocity = new LLVector3( + Convert.ToSingle(row["VelocityX"]), + Convert.ToSingle(row["VelocityY"]), + Convert.ToSingle(row["VelocityZ"]) + ); + prim.AngularVelocity = new LLVector3( + Convert.ToSingle(row["AngularVelocityX"]), + Convert.ToSingle(row["AngularVelocityY"]), + Convert.ToSingle(row["AngularVelocityZ"]) + ); + prim.Acceleration = new LLVector3( + Convert.ToSingle(row["AccelerationX"]), + Convert.ToSingle(row["AccelerationY"]), + Convert.ToSingle(row["AccelerationZ"]) + ); + // quaternions + prim.RotationOffset = new LLQuaternion( + Convert.ToSingle(row["RotationX"]), + Convert.ToSingle(row["RotationY"]), + Convert.ToSingle(row["RotationZ"]), + Convert.ToSingle(row["RotationW"]) + ); + + return prim; + } + + private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + row["UUID"] = prim.UUID; + row["RegionUUID"] = regionUUID; + row["ParentID"] = prim.ParentID; + row["CreationDate"] = prim.CreationDate; + row["Name"] = prim.Name; + row["SceneGroupID"] = sceneGroupID; // the UUID of the root part for this SceneObjectGroup + // various text fields + row["Text"] = prim.Text; + row["Description"] = prim.Description; + row["SitName"] = prim.SitName; + row["TouchName"] = prim.TouchName; + // permissions + row["ObjectFlags"] = prim.ObjectFlags; + row["CreatorID"] = prim.CreatorID; + row["OwnerID"] = prim.OwnerID; + row["GroupID"] = prim.GroupID; + row["LastOwnerID"] = prim.LastOwnerID; + row["OwnerMask"] = prim.OwnerMask; + row["NextOwnerMask"] = prim.NextOwnerMask; + row["GroupMask"] = prim.GroupMask; + row["EveryoneMask"] = prim.EveryoneMask; + row["BaseMask"] = prim.BaseMask; + // vectors + row["PositionX"] = prim.OffsetPosition.X; + row["PositionY"] = prim.OffsetPosition.Y; + row["PositionZ"] = prim.OffsetPosition.Z; + row["GroupPositionX"] = prim.GroupPosition.X; + row["GroupPositionY"] = prim.GroupPosition.Y; + row["GroupPositionZ"] = prim.GroupPosition.Z; + row["VelocityX"] = prim.Velocity.X; + row["VelocityY"] = prim.Velocity.Y; + row["VelocityZ"] = prim.Velocity.Z; + row["AngularVelocityX"] = prim.AngularVelocity.X; + row["AngularVelocityY"] = prim.AngularVelocity.Y; + row["AngularVelocityZ"] = prim.AngularVelocity.Z; + row["AccelerationX"] = prim.Acceleration.X; + row["AccelerationY"] = prim.Acceleration.Y; + row["AccelerationZ"] = prim.Acceleration.Z; + // quaternions + row["RotationX"] = prim.RotationOffset.X; + row["RotationY"] = prim.RotationOffset.Y; + row["RotationZ"] = prim.RotationOffset.Z; + row["RotationW"] = prim.RotationOffset.W; + } + + private PrimitiveBaseShape buildShape(DataRow row) + { + PrimitiveBaseShape s = new PrimitiveBaseShape(); + s.Scale = new LLVector3( + Convert.ToSingle(row["ScaleX"]), + Convert.ToSingle(row["ScaleY"]), + Convert.ToSingle(row["ScaleZ"]) + ); + // paths + s.PCode = Convert.ToByte(row["PCode"]); + s.PathBegin = Convert.ToUInt16(row["PathBegin"]); + s.PathEnd = Convert.ToUInt16(row["PathEnd"]); + s.PathScaleX = Convert.ToByte(row["PathScaleX"]); + s.PathScaleY = Convert.ToByte(row["PathScaleY"]); + s.PathShearX = Convert.ToByte(row["PathShearX"]); + s.PathShearY = Convert.ToByte(row["PathShearY"]); + s.PathSkew = Convert.ToSByte(row["PathSkew"]); + s.PathCurve = Convert.ToByte(row["PathCurve"]); + s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]); + s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]); + s.PathTaperX = Convert.ToSByte(row["PathTaperX"]); + s.PathTaperY = Convert.ToSByte(row["PathTaperY"]); + s.PathTwist = Convert.ToSByte(row["PathTwist"]); + s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]); + // profile + s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]); + s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]); + s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]); + s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]); + // text TODO: this isn't right] = but I'm not sure the right + // way to specify this as a blob atm + s.TextureEntry = (byte[])row["Texture"]; + s.ExtraParams = (byte[])row["ExtraParams"]; + // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding(); + // string texture = encoding.GetString((Byte[])row["Texture"]); + // if (!texture.StartsWith("<")) + // { + // //here so that we can still work with old format database files (ie from before I added xml serialization) + // LLObject.TextureEntry textureEntry = null; + // textureEntry = new LLObject.TextureEntry(new LLUUID(texture)); + // s.TextureEntry = textureEntry.ToBytes(); + // } + // else + // { + // TextureBlock textureEntry = TextureBlock.FromXmlString(texture); + // s.TextureEntry = textureEntry.TextureData; + // s.ExtraParams = textureEntry.ExtraParams; + // } + + return s; + } + + private void fillShapeRow(DataRow row, SceneObjectPart prim) + { + PrimitiveBaseShape s = prim.Shape; + row["UUID"] = prim.UUID; + // shape is an enum + row["Shape"] = 0; + // vectors + row["ScaleX"] = s.Scale.X; + row["ScaleY"] = s.Scale.Y; + row["ScaleZ"] = s.Scale.Z; + // paths + row["PCode"] = s.PCode; + row["PathBegin"] = s.PathBegin; + row["PathEnd"] = s.PathEnd; + row["PathScaleX"] = s.PathScaleX; + row["PathScaleY"] = s.PathScaleY; + row["PathShearX"] = s.PathShearX; + row["PathShearY"] = s.PathShearY; + row["PathSkew"] = s.PathSkew; + row["PathCurve"] = s.PathCurve; + row["PathRadiusOffset"] = s.PathRadiusOffset; + row["PathRevolutions"] = s.PathRevolutions; + row["PathTaperX"] = s.PathTaperX; + row["PathTaperY"] = s.PathTaperY; + row["PathTwist"] = s.PathTwist; + row["PathTwistBegin"] = s.PathTwistBegin; + // profile + row["ProfileBegin"] = s.ProfileBegin; + row["ProfileEnd"] = s.ProfileEnd; + row["ProfileCurve"] = s.ProfileCurve; + row["ProfileHollow"] = s.ProfileHollow; + // text TODO: this isn't right] = but I'm not sure the right + // way to specify this as a blob atm + + // And I couldn't work out how to save binary data either + // seems that the texture colum is being treated as a string in the Datarow + // if you do a .getType() on it, it returns string, while the other columns return correct type + // MW[10-08-07] + // Added following xml hack but not really ideal , also ExtraParams isn't currently part of the database + // am a bit worried about adding it now as some people will have old format databases, so for now including that data in this xml data + // MW[17-08-07] + row["Texture"] = s.TextureEntry; + row["ExtraParams"] = s.ExtraParams; + // TextureBlock textureBlock = new TextureBlock(s.TextureEntry); + // textureBlock.ExtraParams = s.ExtraParams; + // System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding(); + // row["Texture"] = encoding.GetBytes(textureBlock.ToXMLString()); + } + + private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + DataTable prims = ds.Tables["prims"]; + DataTable shapes = ds.Tables["primshapes"]; + + DataRow primRow = prims.Rows.Find(prim.UUID); + if (primRow == null) + { + primRow = prims.NewRow(); + fillPrimRow(primRow, prim, sceneGroupID, regionUUID); + prims.Rows.Add(primRow); + } + else + { + fillPrimRow(primRow, prim, sceneGroupID, regionUUID); + } + + DataRow shapeRow = shapes.Rows.Find(prim.UUID); + if (shapeRow == null) + { + shapeRow = shapes.NewRow(); + fillShapeRow(shapeRow, prim); + shapes.Rows.Add(shapeRow); + } + else + { + fillShapeRow(shapeRow, prim); + } + } + + /*********************************************************************** + * + * SQL Statement Creation Functions + * + * These functions create SQL statements for update, insert, and create. + * They can probably be factored later to have a db independant + * portion and a db specific portion + * + **********************************************************************/ + + private SqlCommand createInsertCommand(string table, DataTable dt) + { + /** + * This is subtle enough to deserve some commentary. + * Instead of doing *lots* and *lots of hardcoded strings + * for database definitions we'll use the fact that + * realistically all insert statements look like "insert + * into A(b, c) values(:b, :c) on the parameterized query + * front. If we just have a list of b, c, etc... we can + * generate these strings instead of typing them out. + */ + string[] cols = new string[dt.Columns.Count]; + for (int i = 0; i < dt.Columns.Count; i++) + { + DataColumn col = dt.Columns[i]; + cols[i] = col.ColumnName; + } + + string sql = "insert into " + table + "("; + sql += String.Join(", ", cols); + // important, the first ':' needs to be here, the rest get added in the join + sql += ") values (@"; + sql += String.Join(", @", cols); + sql += ")"; + SqlCommand cmd = new SqlCommand(sql); + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + private SqlCommand createUpdateCommand(string table, string pk, DataTable dt) + { + string sql = "update " + table + " set "; + string subsql = ""; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { // a map function would rock so much here + subsql += ", "; + } + subsql += col.ColumnName + "= @" + col.ColumnName; + } + sql += subsql; + sql += " where " + pk; + SqlCommand cmd = new SqlCommand(sql); + // this provides the binding for all our parameters, so + // much less code than it used to be + + foreach (DataColumn col in dt.Columns) + { + cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + + private string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = ""; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { // a map function would rock so much here + subsql += ",\n"; + } + subsql += col.ColumnName + " " + SqlType(col.DataType); + if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + sql += subsql; + sql += ")"; + + return sql; + } + + private void fillTerrainRow(DataRow row, LLUUID regionUUID, int rev, double[,] val) + { + row["RegionUUID"] = regionUUID; + row["Revision"] = rev; + + MemoryStream str = new MemoryStream(65536 * sizeof(double)); + BinaryWriter bw = new BinaryWriter(str); + + // TODO: COMPATIBILITY - Add byte-order conversions + for (int x = 0; x < 256; x++) + for (int y = 0; y < 256; y++) + bw.Write(val[x, y]); + + row["Heightfield"] = str.ToArray(); + } + + /*********************************************************************** + * + * Database Binding functions + * + * These will be db specific due to typing, and minor differences + * in databases. + * + **********************************************************************/ + + /// + /// This is a convenience function that collapses 5 repetitive + /// lines for defining SqlParameters to 2 parameters: + /// column name and database type. + /// + /// It assumes certain conventions like :param as the param + /// name to replace in parametrized queries, and that source + /// version is always current version, both of which are fine + /// for us. + /// + ///a built Sql parameter + private SqlParameter createSqlParameter(string name, System.Type type) + { + SqlParameter param = new SqlParameter(); + param.ParameterName = "@" + name; + param.DbType = dbtypeFromType(type); + param.SourceColumn = name; + param.SourceVersion = DataRowVersion.Current; + return param; + } + + private void setupPrimCommands(SqlDataAdapter da, SqlConnection conn) + { + da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("prims", "UUID=@UUID", ds.Tables["prims"]); + da.UpdateCommand.Connection = conn; + + SqlCommand delete = new SqlCommand("delete from prims where UUID = @UUID"); + delete.Parameters.Add(createSqlParameter("UUID", typeof(System.String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupShapeCommands(SqlDataAdapter da, SqlConnection conn) + { + da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("primshapes", "UUID=@UUID", ds.Tables["primshapes"]); + da.UpdateCommand.Connection = conn; + + SqlCommand delete = new SqlCommand("delete from primshapes where UUID = @UUID"); + delete.Parameters.Add(createSqlParameter("UUID", typeof(System.String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupTerrainCommands(SqlDataAdapter da, SqlConnection conn) + { + da.InsertCommand = createInsertCommand("terrain", ds.Tables["terrain"]); + da.InsertCommand.Connection = conn; + } + + private void InitDB(SqlConnection conn) + { + string createPrims = defineTable(createPrimTable()); + string createShapes = defineTable(createShapeTable()); + string createTerrain = defineTable(createTerrainTable()); + + SqlCommand pcmd = new SqlCommand(createPrims, conn); + SqlCommand scmd = new SqlCommand(createShapes, conn); + SqlCommand tcmd = new SqlCommand(createTerrain, conn); + conn.Open(); + try + { + pcmd.ExecuteNonQuery(); + pcmd.Dispose(); + } + catch (SqlException) + { + MainLog.Instance.Warn("MSSQL", "Primitives Table Already Exists"); + } + + try + { + scmd.ExecuteNonQuery(); + scmd.Dispose(); + } + catch (SqlException) + { + MainLog.Instance.Warn("MSSQL", "Shapes Table Already Exists"); + } + + try + { + tcmd.ExecuteNonQuery(); + tcmd.Dispose(); + } + catch (SqlException) + { + MainLog.Instance.Warn("MSSQL", "Terrain Table Already Exists"); + } + + conn.Close(); + } + + private bool TestTables(SqlConnection conn) + { + + SqlCommand primSelectCmd = new SqlCommand(primSelect, conn); + SqlDataAdapter pDa = new SqlDataAdapter(primSelectCmd); + SqlCommand shapeSelectCmd = new SqlCommand(shapeSelect, conn); + SqlDataAdapter sDa = new SqlDataAdapter(shapeSelectCmd); + SqlCommand terrainSelectCmd = new SqlCommand(terrainSelect, conn); + SqlDataAdapter tDa = new SqlDataAdapter(terrainSelectCmd); + + DataSet tmpDS = new DataSet(); + try + { + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + tDa.Fill(tmpDS, "terrain"); + } + catch (SqlException) + { + MainLog.Instance.Verbose("DATASTORE", "MSSQL Database doesn't exist... creating"); + InitDB(conn); + } + + try + { + if (tmpDS.Tables == null || tmpDS.Tables.Count == 0) + { + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + tDa.Fill(tmpDS, "terrain"); + } + + } + catch (SqlException e) + { + MainLog.Instance.Verbose("DATASTORE", e.ToString()); + } + + foreach (DataColumn col in createPrimTable().Columns) + { + if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName)) + { + MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName); + return false; + } + } + foreach (DataColumn col in createShapeTable().Columns) + { + if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName)) + { + MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName); + return false; + } + } + foreach (DataColumn col in createTerrainTable().Columns) + { + if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName)) + { + MainLog.Instance.Verbose("DATASTORE", "Missing require column:" + col.ColumnName); + return false; + } + } + return true; + } + + /*********************************************************************** + * + * Type conversion functions + * + **********************************************************************/ + + private DbType dbtypeFromType(Type type) + { + if (type == typeof(System.String)) + { + return DbType.String; + } + else if (type == typeof(System.Int32)) + { + return DbType.Int32; + } + else if (type == typeof(System.Double)) + { + return DbType.Double; + } + else if (type == typeof(System.Byte[])) + { + return DbType.Binary; + } + else + { + return DbType.String; + } + } + + // this is something we'll need to implement for each db + // slightly differently. + private string SqlType(Type type) + { + if (type == typeof(System.String)) + { + return "varchar(255)"; + } + else if (type == typeof(System.Int32)) + { + return "integer"; + } + else if (type == typeof(System.Double)) + { + return "float"; + } + else if (type == typeof(System.Byte[])) + { + return "image"; + } + else + { + return "string"; + } + } + } +} \ No newline at end of file diff --git a/bin/mssql_connection.ini b/bin/mssql_connection.ini new file mode 100644 index 0000000..c63d008 --- /dev/null +++ b/bin/mssql_connection.ini @@ -0,0 +1,6 @@ +[mssqlconnection] +data_source=\SQLEXPRESS +initial_catalog=database +persist_security_info=True +user_id=username +password=password diff --git a/prebuild.xml b/prebuild.xml index 4705fc7..6606751 100644 --- a/prebuild.xml +++ b/prebuild.xml @@ -672,6 +672,33 @@ + + + + + ../../../../bin/ + + + + + ../../../../bin/ + + + + ../../../../bin/ + + + + + + + + + + + + + @@ -883,10 +910,14 @@ + + + + @@ -1191,3 +1222,5 @@ + + diff --git a/share/sql/mssql-CreateAssetsTable.sql b/share/sql/mssql-CreateAssetsTable.sql new file mode 100644 index 0000000..c7cb21a --- /dev/null +++ b/share/sql/mssql-CreateAssetsTable.sql @@ -0,0 +1,19 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [invType] [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] + +SET ANSI_PADDING OFF diff --git a/share/sql/mssql-CreateFoldersTable.sql b/share/sql/mssql-CreateFoldersTable.sql new file mode 100644 index 0000000..95d183a --- /dev/null +++ b/share/sql/mssql-CreateFoldersTable.sql @@ -0,0 +1,27 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +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] + +SET ANSI_PADDING OFF diff --git a/share/sql/mssql-CreateItemsTable.sql b/share/sql/mssql-CreateItemsTable.sql new file mode 100644 index 0000000..40c047e --- /dev/null +++ b/share/sql/mssql-CreateItemsTable.sql @@ -0,0 +1,39 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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](64) 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, + 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] + +SET ANSI_PADDING OFF + diff --git a/share/sql/mssql-agents.sql b/share/sql/mssql-agents.sql new file mode 100644 index 0000000..ad53173 --- /dev/null +++ b/share/sql/mssql-agents.sql @@ -0,0 +1,37 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] + +SET ANSI_PADDING OFF + diff --git a/share/sql/mssql-logs.sql b/share/sql/mssql-logs.sql new file mode 100644 index 0000000..3b747d8 --- /dev/null +++ b/share/sql/mssql-logs.sql @@ -0,0 +1,20 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [logs] ( + [logID] [int] NOT NULL, + [target] [varchar](36) default NULL, + [server] [varchar](64) default NULL, + [method] [varchar](64) default NULL, + [arguments] [varchar](255) default NULL, + [priority] [int] default NULL, + [message] [ntext], + PRIMARY KEY CLUSTERED +( + [logID] 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/share/sql/mssql-users.sql b/share/sql/mssql-users.sql new file mode 100644 index 0000000..3f5f896 --- /dev/null +++ b/share/sql/mssql-users.sql @@ -0,0 +1,41 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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, + 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] -- cgit v1.1