From c52c68f314c67c76c7181a6d0828f476290fbd66 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 2 Apr 2008 15:24:31 +0000 Subject: whole lot more moving --- OpenSim/Data/MySQL/MySQLAssetData.cs | 198 +++ OpenSim/Data/MySQL/MySQLDataStore.cs | 1722 ++++++++++++++++++++ OpenSim/Data/MySQL/MySQLGridData.cs | 402 +++++ OpenSim/Data/MySQL/MySQLInventoryData.cs | 648 ++++++++ OpenSim/Data/MySQL/MySQLLogData.cs | 106 ++ OpenSim/Data/MySQL/MySQLManager.cs | 909 +++++++++++ OpenSim/Data/MySQL/MySQLUserData.cs | 643 ++++++++ OpenSim/Data/MySQL/Properties/AssemblyInfo.cs | 65 + OpenSim/Data/MySQL/Resources/AvatarAppearance.sql | 42 + OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql | 24 + OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql | 11 + .../Data/MySQL/Resources/CreateFoldersTable.sql | 11 + OpenSim/Data/MySQL/Resources/CreateItemsTable.sql | 18 + OpenSim/Data/MySQL/Resources/CreateLogsTable.sql | 10 + .../Data/MySQL/Resources/CreateRegionsTable.sql | 32 + .../MySQL/Resources/CreateUserFriendsTable.sql | 11 + OpenSim/Data/MySQL/Resources/CreateUsersTable.sql | 35 + .../Resources/UpgradeFoldersTableToVersion2.sql | 4 + .../Resources/UpgradeItemsTableToVersion2.sql | 9 + .../Resources/UpgradeRegionsTableToVersion2.sql | 4 + .../Resources/UpgradeRegionsTableToVersion3.sql | 18 + .../Resources/UpgradeUsersTableToVersion2.sql | 3 + 22 files changed, 4925 insertions(+) create mode 100644 OpenSim/Data/MySQL/MySQLAssetData.cs create mode 100644 OpenSim/Data/MySQL/MySQLDataStore.cs create mode 100644 OpenSim/Data/MySQL/MySQLGridData.cs create mode 100644 OpenSim/Data/MySQL/MySQLInventoryData.cs create mode 100644 OpenSim/Data/MySQL/MySQLLogData.cs create mode 100644 OpenSim/Data/MySQL/MySQLManager.cs create mode 100644 OpenSim/Data/MySQL/MySQLUserData.cs create mode 100644 OpenSim/Data/MySQL/Properties/AssemblyInfo.cs create mode 100644 OpenSim/Data/MySQL/Resources/AvatarAppearance.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateItemsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateLogsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/CreateUsersTable.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql create mode 100644 OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs new file mode 100644 index 0000000..79994ae --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -0,0 +1,198 @@ +/* + * 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 libsecondlife; +using MySql.Data.MySqlClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + internal class MySQLAssetData : AssetDataBase, IPlugin + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + private MySQLManager _dbConnection; + + #region IAssetProvider Members + + private void UpgradeAssetsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + m_log.Info("[ASSETS]: Creating new database tables"); + _dbConnection.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; + _dbConnection.GetTableVersion(tableList); + + UpgradeAssetsTable(tableList["assets"]); + } + + override public AssetBase FetchAsset(LLUUID assetID) + { + AssetBase asset = null; + lock (_dbConnection) + { + MySqlCommand cmd = + new MySqlCommand( + "SELECT name, description, assetType, invType, local, temporary, data FROM assets WHERE id=?id", + _dbConnection.Connection); + MySqlParameter p = cmd.Parameters.Add("?id", MySqlDbType.Binary, 16); + p.Value = assetID.GetBytes(); + + try + { + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if (dbReader.Read()) + { + asset = new AssetBase(); + asset.Data = (byte[]) dbReader["data"]; + asset.Description = (string) dbReader["description"]; + asset.FullID = assetID; + asset.InvType = (sbyte) dbReader["invType"]; + asset.Local = ((sbyte) dbReader["local"]) != 0 ? true : false; + asset.Name = (string) dbReader["name"]; + asset.Type = (sbyte) dbReader["assetType"]; + } + dbReader.Close(); + cmd.Dispose(); + } + } + catch (Exception e) + { + m_log.ErrorFormat( + "[ASSETS]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString() + + Environment.NewLine + "Attempting reconnection", assetID); + _dbConnection.Reconnect(); + } + } + return asset; + } + + override public void CreateAsset(AssetBase asset) + { + lock (_dbConnection) + { + MySqlCommand cmd = + new MySqlCommand( + "REPLACE INTO assets(id, name, description, assetType, invType, local, temporary, data)" + + "VALUES(?id, ?name, ?description, ?assetType, ?invType, ?local, ?temporary, ?data)", + _dbConnection.Connection); + + // need to ensure we dispose + try + { + using (cmd) + { + MySqlParameter p = cmd.Parameters.Add("?id", MySqlDbType.Binary, 16); + p.Value = asset.FullID.GetBytes(); + cmd.Parameters.AddWithValue("?name", asset.Name); + cmd.Parameters.AddWithValue("?description", asset.Description); + cmd.Parameters.AddWithValue("?assetType", asset.Type); + cmd.Parameters.AddWithValue("?invType", asset.InvType); + cmd.Parameters.AddWithValue("?local", asset.Local); + cmd.Parameters.AddWithValue("?temporary", asset.Temporary); + cmd.Parameters.AddWithValue("?data", asset.Data); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + } + catch (Exception e) + { + m_log.ErrorFormat( + "[ASSETS]: " + + "MySql failure creating asset {0} with name {1}" + Environment.NewLine + e.ToString() + + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); + _dbConnection.Reconnect(); + } + } + } + + override public void UpdateAsset(AssetBase asset) + { + CreateAsset(asset); + } + + override public bool ExistsAsset(LLUUID uuid) + { + throw new Exception("The method or operation is not implemented."); + } + + /// + /// All writes are immediately commited to the database, so this is a no-op + /// + override public void CommitAssets() + { + } + + #endregion + + #region IPlugin Members + + override public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); + string hostname = GridDataMySqlFile.ParseFileReadValue("hostname"); + string database = GridDataMySqlFile.ParseFileReadValue("database"); + string username = GridDataMySqlFile.ParseFileReadValue("username"); + string password = GridDataMySqlFile.ParseFileReadValue("password"); + string pooling = GridDataMySqlFile.ParseFileReadValue("pooling"); + string port = GridDataMySqlFile.ParseFileReadValue("port"); + + _dbConnection = new MySQLManager(hostname, database, username, password, pooling, port); + + TestTables(); + } + + override public string Version + { + get { return _dbConnection.getVersion(); } + } + + override public string Name + { + get { return "MySQL Asset storage engine"; } + } + + #endregion + } +} diff --git a/OpenSim/Data/MySQL/MySQLDataStore.cs b/OpenSim/Data/MySQL/MySQLDataStore.cs new file mode 100644 index 0000000..eaa7f14 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLDataStore.cs @@ -0,0 +1,1722 @@ +/* + * 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.Diagnostics; +using System.IO; +using libsecondlife; +using MySql.Data.MySqlClient; +using OpenSim.Framework.Console; +using OpenSim.Region.Environment.Interfaces; +using OpenSim.Region.Environment.Scenes; + +namespace OpenSim.Framework.Data.MySQL +{ + public class MySQLDataStore : IRegionDataStore + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + private const string m_primSelect = "select * from prims"; + private const string m_shapeSelect = "select * from primshapes"; + private const string m_itemsSelect = "select * from primitems"; + private const string m_terrainSelect = "select * from terrain limit 1"; + private const string m_landSelect = "select * from land"; + private const string m_landAccessListSelect = "select * from landaccesslist"; + + private DataSet m_dataSet; + private MySqlDataAdapter m_primDataAdapter; + private MySqlDataAdapter m_shapeDataAdapter; + private MySqlDataAdapter m_itemsDataAdapter; + private MySqlConnection m_connection; + private MySqlDataAdapter m_terrainDataAdapter; + private MySqlDataAdapter m_landDataAdapter; + private MySqlDataAdapter m_landAccessListDataAdapter; + + private DataTable m_primTable; + private DataTable m_shapeTable; + private DataTable m_itemsTable; + private DataTable m_terrainTable; + private DataTable m_landTable; + private DataTable m_landAccessListTable; + + // Temporary attribute while this is experimental + private bool persistPrimInventories; + + /*********************************************************************** + * + * Public Interface Functions + * + **********************************************************************/ + + // see IRegionDataStore + public void Initialise(string connectionstring, bool persistPrimInventories) + { + m_dataSet = new DataSet(); + this.persistPrimInventories = persistPrimInventories; + + m_log.Info("[DATASTORE]: MySql - connecting: " + connectionstring); + m_connection = new MySqlConnection(connectionstring); + + MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection); + m_primDataAdapter = new MySqlDataAdapter(primSelectCmd); + + MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, m_connection); + m_shapeDataAdapter = new MySqlDataAdapter(shapeSelectCmd); + + MySqlCommand itemsSelectCmd = new MySqlCommand(m_itemsSelect, m_connection); + m_itemsDataAdapter = new MySqlDataAdapter(itemsSelectCmd); + + MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, m_connection); + m_terrainDataAdapter = new MySqlDataAdapter(terrainSelectCmd); + + MySqlCommand landSelectCmd = new MySqlCommand(m_landSelect, m_connection); + m_landDataAdapter = new MySqlDataAdapter(landSelectCmd); + + MySqlCommand landAccessListSelectCmd = new MySqlCommand(m_landAccessListSelect, m_connection); + m_landAccessListDataAdapter = new MySqlDataAdapter(landAccessListSelectCmd); + + TestTables(m_connection); + + lock (m_dataSet) + { + m_primTable = createPrimTable(); + m_dataSet.Tables.Add(m_primTable); + SetupPrimCommands(m_primDataAdapter, m_connection); + m_primDataAdapter.Fill(m_primTable); + + m_shapeTable = createShapeTable(); + m_dataSet.Tables.Add(m_shapeTable); + SetupShapeCommands(m_shapeDataAdapter, m_connection); + m_shapeDataAdapter.Fill(m_shapeTable); + + if (persistPrimInventories) + { + m_itemsTable = createItemsTable(); + m_dataSet.Tables.Add(m_itemsTable); + SetupItemsCommands(m_itemsDataAdapter, m_connection); + m_itemsDataAdapter.Fill(m_itemsTable); + } + + m_terrainTable = createTerrainTable(); + m_dataSet.Tables.Add(m_terrainTable); + SetupTerrainCommands(m_terrainDataAdapter, m_connection); + m_terrainDataAdapter.Fill(m_terrainTable); + + m_landTable = createLandTable(); + m_dataSet.Tables.Add(m_landTable); + setupLandCommands(m_landDataAdapter, m_connection); + m_landDataAdapter.Fill(m_landTable); + + m_landAccessListTable = createLandAccessListTable(); + m_dataSet.Tables.Add(m_landAccessListTable); + setupLandAccessCommands(m_landAccessListDataAdapter, m_connection); + m_landAccessListDataAdapter.Fill(m_landAccessListTable); + } + } + + public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID) + { + lock (m_dataSet) + { + foreach (SceneObjectPart prim in obj.Children.Values) + { + if ((prim.ObjectFlags & (uint) LLObject.ObjectFlags.Physics) == 0) + { + m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID); + addPrim(prim, obj.UUID, regionUUID); + } + else + { + // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID); + } + } + Commit(); + } + } + + public void RemoveObject(LLUUID obj, LLUUID regionUUID) + { + m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID); + + DataTable prims = m_primTable; + DataTable shapes = m_shapeTable; + + string selectExp = "SceneGroupID = '" + Util.ToRawUuidString(obj) + "'"; + lock (m_dataSet) + { + DataRow[] primRows = prims.Select(selectExp); + foreach (DataRow row in primRows) + { + // Remove shapes row + LLUUID uuid = new LLUUID((string) row["UUID"]); + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(uuid)); + if (shapeRow != null) + { + shapeRow.Delete(); + } + + if (persistPrimInventories) + { + RemoveItems(uuid); + } + + // Remove prim row + row.Delete(); + } + Commit(); + } + } + + /// + /// Remove all persisted items of the given prim. + /// The caller must acquire the necessrary synchronization locks and commit or rollback changes. + /// + private void RemoveItems(LLUUID uuid) + { + String sql = String.Format("primID = '{0}'", uuid); + DataRow[] itemRows = m_itemsTable.Select(sql); + + foreach (DataRow itemRow in itemRows) + { + itemRow.Delete(); + } + } + + /// + /// Load persisted objects from region storage. + /// + public List LoadObjects(LLUUID regionUUID) + { + Dictionary createdObjects = new Dictionary(); + + List retvals = new List(); + + DataTable prims = m_primTable; + DataTable shapes = m_shapeTable; + + string byRegion = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'"; + string orderByParent = "ParentID ASC"; + + lock (m_dataSet) + { + DataRow[] primsForRegion = prims.Select(byRegion, orderByParent); + m_log.Info("[DATASTORE]: " + + "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID); + + foreach (DataRow primRow in primsForRegion) + { + try + { + string uuid = (string) primRow["UUID"]; + string objID = (string) primRow["SceneGroupID"]; + + SceneObjectPart prim = buildPrim(primRow); + + if (uuid == objID) //is new SceneObjectGroup ? + { + SceneObjectGroup group = new SceneObjectGroup(); + + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + m_log.Info( + "No shape found for prim in storage, so setting default box shape"); + prim.Shape = PrimitiveBaseShape.Default; + } + group.AddPart(prim); + group.RootPart = prim; + + createdObjects.Add(group.UUID, group); + retvals.Add(group); + } + else + { + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); + if (shapeRow != null) + { + prim.Shape = buildShape(shapeRow); + } + else + { + m_log.Info( + "No shape found for prim in storage, so setting default box shape"); + prim.Shape = PrimitiveBaseShape.Default; + } + createdObjects[new LLUUID(objID)].AddPart(prim); + } + + if (persistPrimInventories) + { + LoadItems(prim); + } + } + catch (Exception e) + { + m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows"); + m_log.Info("[DATASTORE]: " + e.ToString()); + foreach (DataColumn col in prims.Columns) + { + m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]); + } + } + } + } + return retvals; + } + + /// + /// Load in a prim's persisted inventory. + /// + /// + private void LoadItems(SceneObjectPart prim) + { + //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID); + + DataTable dbItems = m_itemsTable; + + String sql = String.Format("primID = '{0}'", prim.UUID.ToString()); + DataRow[] dbItemRows = dbItems.Select(sql); + + IList inventory = new List(); + + foreach (DataRow row in dbItemRows) + { + TaskInventoryItem item = buildItem(row); + inventory.Add(item); + + //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID); + } + + prim.RestoreInventoryItems(inventory); + + // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in + // every item). This data should really be stored in the prim table itself. + if (dbItemRows.Length > 0) + { + prim.FolderID = inventory[0].ParentID; + } + } + + public void StoreTerrain(double[,] ter, LLUUID regionID) + { + int revision = Util.UnixTimeSinceEpoch(); + m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString()); + + DataTable terrain = m_dataSet.Tables["terrain"]; + lock (m_dataSet) + { + MySqlCommand cmd = new MySqlCommand("insert into terrain(RegionUUID, Revision, Heightfield)" + + " values(?RegionUUID, ?Revision, ?Heightfield)", m_connection); + using (cmd) + { + cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID))); + cmd.Parameters.Add(new MySqlParameter("?Revision", revision)); + cmd.Parameters.Add(new MySqlParameter("?Heightfield", serializeTerrain(ter))); + cmd.ExecuteNonQuery(); + } + } + } + + public double[,] LoadTerrain(LLUUID regionID) + { + double[,] terret = new double[256,256]; + terret.Initialize(); + + MySqlCommand cmd = new MySqlCommand( + @"select RegionUUID, Revision, Heightfield from terrain + where RegionUUID=?RegionUUID order by Revision desc limit 1" + , m_connection); + + MySqlParameter param = new MySqlParameter(); + cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID))); + + if (m_connection.State != ConnectionState.Open) + { + m_connection.Open(); + } + + lock (m_dataSet) + { + using (MySqlDataReader row = cmd.ExecuteReader()) + { + int rev = 0; + if (row.Read()) + { + MemoryStream str = new MemoryStream((byte[]) row["Heightfield"]); + BinaryReader br = new BinaryReader(str); + for (int x = 0; x < 256; x++) + { + for (int y = 0; y < 256; y++) + { + terret[x, y] = br.ReadDouble(); + } + } + rev = (int) row["Revision"]; + } + else + { + m_log.Info("[DATASTORE]: No terrain found for region"); + return null; + } + + m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString()); + } + } + return terret; + } + + public void RemoveLandObject(LLUUID globalID) + { + lock (m_dataSet) + { + using (MySqlCommand cmd = new MySqlCommand("delete from land where UUID=?UUID", m_connection)) + { + cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID))); + cmd.ExecuteNonQuery(); + } + + using ( + MySqlCommand cmd = new MySqlCommand("delete from landaccesslist where LandUUID=?UUID", m_connection) + ) + { + cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID))); + cmd.ExecuteNonQuery(); + } + } + } + + public void StoreLandObject(ILandObject parcel) + { + lock (m_dataSet) + { + DataTable land = m_landTable; + DataTable landaccesslist = m_landAccessListTable; + + DataRow landRow = land.Rows.Find(Util.ToRawUuidString(parcel.landData.globalID)); + if (landRow == null) + { + landRow = land.NewRow(); + fillLandRow(landRow, parcel.landData, parcel.regionUUID); + land.Rows.Add(landRow); + } + else + { + fillLandRow(landRow, parcel.landData, parcel.regionUUID); + } + + using ( + MySqlCommand cmd = + new MySqlCommand("delete from landaccesslist where LandUUID=?LandUUID", m_connection)) + { + cmd.Parameters.Add(new MySqlParameter("?LandUUID", Util.ToRawUuidString(parcel.landData.globalID))); + cmd.ExecuteNonQuery(); + } + + foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList) + { + DataRow newAccessRow = landaccesslist.NewRow(); + fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID); + landaccesslist.Rows.Add(newAccessRow); + } + + Commit(); + } + } + + public List LoadLandObjects(LLUUID regionUUID) + { + List landDataForRegion = new List(); + lock (m_dataSet) + { + DataTable land = m_landTable; + DataTable landaccesslist = m_landAccessListTable; + string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'"; + DataRow[] rawDataForRegion = land.Select(searchExp); + foreach (DataRow rawDataLand in rawDataForRegion) + { + LandData newLand = buildLandData(rawDataLand); + string accessListSearchExp = "LandUUID = '" + Util.ToRawUuidString(newLand.globalID) + "'"; + DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp); + foreach (DataRow rawDataLandAccess in rawDataForLandAccessList) + { + newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess)); + } + + landDataForRegion.Add(newLand); + } + } + return landDataForRegion; + } + +// TODO: unused +// private void DisplayDataSet(DataSet ds, string title) +// { +// Debug.WriteLine(title); +// //--- Loop through the DataTables +// foreach (DataTable table in ds.Tables) +// { +// Debug.WriteLine("*** DataTable: " + table.TableName + "***"); +// //--- Loop through each DataTable's DataRows +// foreach (DataRow row in table.Rows) +// { +// //--- Display the original values, if there are any. +// if (row.HasVersion(DataRowVersion.Original)) +// { +// Debug.Write("Original Row Values ===> "); +// foreach (DataColumn column in table.Columns) +// Debug.Write(column.ColumnName + " = " + +// row[column, DataRowVersion.Original] + ", "); +// Debug.WriteLine(String.Empty); +// } +// //--- Display the current values, if there are any. +// if (row.HasVersion(DataRowVersion.Current)) +// { +// Debug.Write("Current Row Values ====> "); +// foreach (DataColumn column in table.Columns) +// Debug.Write(column.ColumnName + " = " + +// row[column, DataRowVersion.Current] + ", "); +// Debug.WriteLine(String.Empty); +// } +// Debug.WriteLine(String.Empty); +// } +// } +// } + + public void Commit() + { + if (m_connection.State != ConnectionState.Open) + { + m_connection.Open(); + } + + lock (m_dataSet) + { + // DisplayDataSet(m_dataSet, "Region DataSet"); + + m_primDataAdapter.Update(m_primTable); + m_shapeDataAdapter.Update(m_shapeTable); + + if (persistPrimInventories) + { + m_itemsDataAdapter.Update(m_itemsTable); + } + + m_terrainDataAdapter.Update(m_terrainTable); + m_landDataAdapter.Update(m_landTable); + m_landAccessListDataAdapter.Update(m_landAccessListTable); + + m_dataSet.AcceptChanges(); + } + } + + + public void Shutdown() + { + Commit(); + } + + /*********************************************************************** + * + * Database Definition Functions + * + * This should be db agnostic as we define them in ADO.NET terms + * + **********************************************************************/ + + private DataColumn createCol(DataTable dt, string name, Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + return col; + } + + private DataTable createTerrainTable() + { + DataTable terrain = new DataTable("terrain"); + + createCol(terrain, "RegionUUID", typeof (String)); + createCol(terrain, "Revision", typeof (Int32)); + DataColumn heightField = createCol(terrain, "Heightfield", typeof (Byte[])); + return terrain; + } + + private DataTable createPrimTable() + { + DataTable prims = new DataTable("prims"); + + createCol(prims, "UUID", typeof (String)); + createCol(prims, "RegionUUID", typeof (String)); + createCol(prims, "ParentID", typeof (Int32)); + createCol(prims, "CreationDate", typeof (Int32)); + createCol(prims, "Name", typeof (String)); + createCol(prims, "SceneGroupID", typeof (String)); + // various text fields + createCol(prims, "Text", typeof (String)); + createCol(prims, "Description", typeof (String)); + createCol(prims, "SitName", typeof (String)); + createCol(prims, "TouchName", typeof (String)); + // permissions + createCol(prims, "ObjectFlags", typeof (Int32)); + createCol(prims, "CreatorID", typeof (String)); + createCol(prims, "OwnerID", typeof (String)); + createCol(prims, "GroupID", typeof (String)); + createCol(prims, "LastOwnerID", typeof (String)); + createCol(prims, "OwnerMask", typeof (Int32)); + createCol(prims, "NextOwnerMask", typeof (Int32)); + createCol(prims, "GroupMask", typeof (Int32)); + createCol(prims, "EveryoneMask", typeof (Int32)); + createCol(prims, "BaseMask", typeof (Int32)); + // vectors + createCol(prims, "PositionX", typeof (Double)); + createCol(prims, "PositionY", typeof (Double)); + createCol(prims, "PositionZ", typeof (Double)); + createCol(prims, "GroupPositionX", typeof (Double)); + createCol(prims, "GroupPositionY", typeof (Double)); + createCol(prims, "GroupPositionZ", typeof (Double)); + createCol(prims, "VelocityX", typeof (Double)); + createCol(prims, "VelocityY", typeof (Double)); + createCol(prims, "VelocityZ", typeof (Double)); + createCol(prims, "AngularVelocityX", typeof (Double)); + createCol(prims, "AngularVelocityY", typeof (Double)); + createCol(prims, "AngularVelocityZ", typeof (Double)); + createCol(prims, "AccelerationX", typeof (Double)); + createCol(prims, "AccelerationY", typeof (Double)); + createCol(prims, "AccelerationZ", typeof (Double)); + // quaternions + createCol(prims, "RotationX", typeof (Double)); + createCol(prims, "RotationY", typeof (Double)); + createCol(prims, "RotationZ", typeof (Double)); + createCol(prims, "RotationW", typeof (Double)); + // sit target + createCol(prims, "SitTargetOffsetX", typeof (Double)); + createCol(prims, "SitTargetOffsetY", typeof (Double)); + createCol(prims, "SitTargetOffsetZ", typeof (Double)); + + createCol(prims, "SitTargetOrientW", typeof (Double)); + createCol(prims, "SitTargetOrientX", typeof (Double)); + createCol(prims, "SitTargetOrientY", typeof (Double)); + createCol(prims, "SitTargetOrientZ", typeof (Double)); + + + // Add in contraints + prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]}; + + return prims; + } + + private DataTable createLandTable() + { + DataTable land = new DataTable("land"); + createCol(land, "UUID", typeof (String)); + createCol(land, "RegionUUID", typeof (String)); + createCol(land, "LocalLandID", typeof (Int32)); + + // Bitmap is a byte[512] + createCol(land, "Bitmap", typeof (Byte[])); + + createCol(land, "Name", typeof (String)); + createCol(land, "Description", typeof (String)); + createCol(land, "OwnerUUID", typeof (String)); + createCol(land, "IsGroupOwned", typeof (Int32)); + createCol(land, "Area", typeof (Int32)); + createCol(land, "AuctionID", typeof (Int32)); //Unemplemented + createCol(land, "Category", typeof (Int32)); //Enum libsecondlife.Parcel.ParcelCategory + createCol(land, "ClaimDate", typeof (Int32)); + createCol(land, "ClaimPrice", typeof (Int32)); + createCol(land, "GroupUUID", typeof (String)); + createCol(land, "SalePrice", typeof (Int32)); + createCol(land, "LandStatus", typeof (Int32)); //Enum. libsecondlife.Parcel.ParcelStatus + createCol(land, "LandFlags", typeof (Int32)); + createCol(land, "LandingType", typeof (Int32)); + createCol(land, "MediaAutoScale", typeof (Int32)); + createCol(land, "MediaTextureUUID", typeof (String)); + createCol(land, "MediaURL", typeof (String)); + createCol(land, "MusicURL", typeof (String)); + createCol(land, "PassHours", typeof (Double)); + createCol(land, "PassPrice", typeof (Int32)); + createCol(land, "SnapshotUUID", typeof (String)); + createCol(land, "UserLocationX", typeof (Double)); + createCol(land, "UserLocationY", typeof (Double)); + createCol(land, "UserLocationZ", typeof (Double)); + createCol(land, "UserLookAtX", typeof (Double)); + createCol(land, "UserLookAtY", typeof (Double)); + createCol(land, "UserLookAtZ", typeof (Double)); + + land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]}; + + return land; + } + + private DataTable createLandAccessListTable() + { + DataTable landaccess = new DataTable("landaccesslist"); + createCol(landaccess, "LandUUID", typeof (String)); + createCol(landaccess, "AccessUUID", typeof (String)); + createCol(landaccess, "Flags", typeof (Int32)); + + return landaccess; + } + + private DataTable createShapeTable() + { + DataTable shapes = new DataTable("primshapes"); + createCol(shapes, "UUID", typeof (String)); + // shape is an enum + createCol(shapes, "Shape", typeof (Int32)); + // vectors + createCol(shapes, "ScaleX", typeof (Double)); + createCol(shapes, "ScaleY", typeof (Double)); + createCol(shapes, "ScaleZ", typeof (Double)); + // paths + createCol(shapes, "PCode", typeof (Int32)); + createCol(shapes, "PathBegin", typeof (Int32)); + createCol(shapes, "PathEnd", typeof (Int32)); + createCol(shapes, "PathScaleX", typeof (Int32)); + createCol(shapes, "PathScaleY", typeof (Int32)); + createCol(shapes, "PathShearX", typeof (Int32)); + createCol(shapes, "PathShearY", typeof (Int32)); + createCol(shapes, "PathSkew", typeof (Int32)); + createCol(shapes, "PathCurve", typeof (Int32)); + createCol(shapes, "PathRadiusOffset", typeof (Int32)); + createCol(shapes, "PathRevolutions", typeof (Int32)); + createCol(shapes, "PathTaperX", typeof (Int32)); + createCol(shapes, "PathTaperY", typeof (Int32)); + createCol(shapes, "PathTwist", typeof (Int32)); + createCol(shapes, "PathTwistBegin", typeof (Int32)); + // profile + createCol(shapes, "ProfileBegin", typeof (Int32)); + createCol(shapes, "ProfileEnd", typeof (Int32)); + createCol(shapes, "ProfileCurve", typeof (Int32)); + createCol(shapes, "ProfileHollow", typeof (Int32)); + createCol(shapes, "State", typeof(Int32)); + createCol(shapes, "Texture", typeof (Byte[])); + createCol(shapes, "ExtraParams", typeof (Byte[])); + + shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]}; + + return shapes; + } + + private DataTable createItemsTable() + { + DataTable items = new DataTable("primitems"); + + createCol(items, "itemID", typeof (String)); + createCol(items, "primID", typeof (String)); + createCol(items, "assetID", typeof (String)); + createCol(items, "parentFolderID", typeof (String)); + + createCol(items, "invType", typeof (Int32)); + createCol(items, "assetType", typeof (Int32)); + + createCol(items, "name", typeof (String)); + createCol(items, "description", typeof (String)); + + createCol(items, "creationDate", typeof (Int64)); + createCol(items, "creatorID", typeof (String)); + createCol(items, "ownerID", typeof (String)); + createCol(items, "lastOwnerID", typeof (String)); + createCol(items, "groupID", typeof (String)); + + createCol(items, "nextPermissions", typeof (Int32)); + createCol(items, "currentPermissions", typeof (Int32)); + createCol(items, "basePermissions", typeof (Int32)); + createCol(items, "everyonePermissions", typeof (Int32)); + createCol(items, "groupPermissions", typeof (Int32)); + + items.PrimaryKey = new DataColumn[] {items.Columns["itemID"]}; + + return items; + } + + /*********************************************************************** + * + * Convert between ADO.NET <=> OpenSim Objects + * + * These should be database independant + * + **********************************************************************/ + + private SceneObjectPart buildPrim(DataRow row) + { + 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"]) + ); + try + { + prim.SetSitTargetLL(new LLVector3( + Convert.ToSingle(row["SitTargetOffsetX"]), + Convert.ToSingle(row["SitTargetOffsetY"]), + Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion( + Convert.ToSingle( + row["SitTargetOrientX"]), + Convert.ToSingle( + row["SitTargetOrientY"]), + Convert.ToSingle( + row["SitTargetOrientZ"]), + Convert.ToSingle( + row["SitTargetOrientW"]))); + } + catch (InvalidCastException) + { + // Database table was created before we got here and needs to be created! :P + + using ( + MySqlCommand cmd = + new MySqlCommand( + "ALTER TABLE `prims` ADD COLUMN `SitTargetOffsetX` float NOT NULL default 0, ADD COLUMN `SitTargetOffsetY` float NOT NULL default 0, ADD COLUMN `SitTargetOffsetZ` float NOT NULL default 0, ADD COLUMN `SitTargetOrientW` float NOT NULL default 0, ADD COLUMN `SitTargetOrientX` float NOT NULL default 0, ADD COLUMN `SitTargetOrientY` float NOT NULL default 0, ADD COLUMN `SitTargetOrientZ` float NOT NULL default 0;", + m_connection)) + { + cmd.ExecuteNonQuery(); + } + } + return prim; + } + + + /// + /// Build a prim inventory item from the persisted data. + /// + /// + /// + private TaskInventoryItem buildItem(DataRow row) + { + TaskInventoryItem taskItem = new TaskInventoryItem(); + + taskItem.ItemID = new LLUUID((String)row["itemID"]); + taskItem.ParentPartID = new LLUUID((String)row["primID"]); + taskItem.AssetID = new LLUUID((String)row["assetID"]); + taskItem.ParentID = new LLUUID((String)row["parentFolderID"]); + + taskItem.InvType = Convert.ToInt32(row["invType"]); + taskItem.Type = Convert.ToInt32(row["assetType"]); + + taskItem.Name = (String)row["name"]; + taskItem.Description = (String)row["description"]; + taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]); + taskItem.CreatorID = new LLUUID((String)row["creatorID"]); + taskItem.OwnerID = new LLUUID((String)row["ownerID"]); + taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]); + taskItem.GroupID = new LLUUID((String)row["groupID"]); + + taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]); + taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]); + taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]); + taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]); + taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]); + + return taskItem; + } + + private LandData buildLandData(DataRow row) + { + LandData newData = new LandData(); + + newData.globalID = new LLUUID((String) row["UUID"]); + newData.localID = Convert.ToInt32(row["LocalLandID"]); + + // Bitmap is a byte[512] + newData.landBitmapByteArray = (Byte[]) row["Bitmap"]; + + newData.landName = (String) row["Name"]; + newData.landDesc = (String) row["Description"]; + newData.ownerID = (String) row["OwnerUUID"]; + newData.isGroupOwned = Convert.ToBoolean(row["IsGroupOwned"]); + newData.area = Convert.ToInt32(row["Area"]); + newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented + newData.category = (Parcel.ParcelCategory) Convert.ToInt32(row["Category"]); + //Enum libsecondlife.Parcel.ParcelCategory + newData.claimDate = Convert.ToInt32(row["ClaimDate"]); + newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]); + newData.groupID = new LLUUID((String) row["GroupUUID"]); + newData.salePrice = Convert.ToInt32(row["SalePrice"]); + newData.landStatus = (Parcel.ParcelStatus) Convert.ToInt32(row["LandStatus"]); + //Enum. libsecondlife.Parcel.ParcelStatus + newData.landFlags = Convert.ToUInt32(row["LandFlags"]); + newData.landingType = Convert.ToByte(row["LandingType"]); + newData.mediaAutoScale = Convert.ToByte(row["MediaAutoScale"]); + newData.mediaID = new LLUUID((String) row["MediaTextureUUID"]); + newData.mediaURL = (String) row["MediaURL"]; + newData.musicURL = (String) row["MusicURL"]; + newData.passHours = Convert.ToSingle(row["PassHours"]); + newData.passPrice = Convert.ToInt32(row["PassPrice"]); + newData.snapshotID = (String) row["SnapshotUUID"]; + + newData.userLocation = + new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]), + Convert.ToSingle(row["UserLocationZ"])); + newData.userLookAt = + new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]), + Convert.ToSingle(row["UserLookAtZ"])); + newData.parcelAccessList = new List(); + + return newData; + } + + private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row) + { + ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry(); + entry.AgentID = new LLUUID((string) row["AccessUUID"]); + entry.Flags = (ParcelManager.AccessList) Convert.ToInt32(row["Flags"]); + entry.Time = new DateTime(); + return entry; + } + + private Array serializeTerrain(double[,] val) + { + 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]); + + return str.ToArray(); + } + + private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + row["UUID"] = Util.ToRawUuidString(prim.UUID); + row["RegionUUID"] = Util.ToRawUuidString(regionUUID); + row["ParentID"] = prim.ParentID; + row["CreationDate"] = prim.CreationDate; + row["Name"] = prim.Name; + row["SceneGroupID"] = Util.ToRawUuidString(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"] = Util.ToRawUuidString(prim.CreatorID); + row["OwnerID"] = Util.ToRawUuidString(prim.OwnerID); + row["GroupID"] = Util.ToRawUuidString(prim.GroupID); + row["LastOwnerID"] = Util.ToRawUuidString(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; + + try + { + // Sit target + LLVector3 sitTargetPos = prim.GetSitTargetPositionLL(); + row["SitTargetOffsetX"] = sitTargetPos.X; + row["SitTargetOffsetY"] = sitTargetPos.Y; + row["SitTargetOffsetZ"] = sitTargetPos.Z; + + LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL(); + row["SitTargetOrientW"] = sitTargetOrient.W; + row["SitTargetOrientX"] = sitTargetOrient.X; + row["SitTargetOrientY"] = sitTargetOrient.Y; + row["SitTargetOrientZ"] = sitTargetOrient.Z; + } + catch (MySqlException) + { + // Database table was created before we got here and needs to be created! :P + + using ( + MySqlCommand cmd = + new MySqlCommand( + "ALTER TABLE `prims` ADD COLUMN `SitTargetOffsetX` float NOT NULL default 0, ADD COLUMN `SitTargetOffsetY` float NOT NULL default 0, ADD COLUMN `SitTargetOffsetZ` float NOT NULL default 0, ADD COLUMN `SitTargetOrientW` float NOT NULL default 0, ADD COLUMN `SitTargetOrientX` float NOT NULL default 0, ADD COLUMN `SitTargetOrientY` float NOT NULL default 0, ADD COLUMN `SitTargetOrientZ` float NOT NULL default 0;", + m_connection)) + { + cmd.ExecuteNonQuery(); + } + } + } + + private void fillItemRow(DataRow row, TaskInventoryItem taskItem) + { + row["itemID"] = taskItem.ItemID; + row["primID"] = taskItem.ParentPartID; + row["assetID"] = taskItem.AssetID; + row["parentFolderID"] = taskItem.ParentID; + + row["invType"] = taskItem.InvType; + row["assetType"] = taskItem.Type; + + row["name"] = taskItem.Name; + row["description"] = taskItem.Description; + row["creationDate"] = taskItem.CreationDate; + row["creatorID"] = taskItem.CreatorID; + row["ownerID"] = taskItem.OwnerID; + row["lastOwnerID"] = taskItem.LastOwnerID; + row["groupID"] = taskItem.GroupID; + row["nextPermissions"] = taskItem.NextOwnerMask; + row["currentPermissions"] = taskItem.OwnerMask; + row["basePermissions"] = taskItem.BaseMask; + row["everyonePermissions"] = taskItem.EveryoneMask; + row["groupPermissions"] = taskItem.GroupMask; + } + + private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID) + { + row["UUID"] = Util.ToRawUuidString(land.globalID); + row["RegionUUID"] = Util.ToRawUuidString(regionUUID); + row["LocalLandID"] = land.localID; + + // Bitmap is a byte[512] + row["Bitmap"] = land.landBitmapByteArray; + + row["Name"] = land.landName; + row["Description"] = land.landDesc; + row["OwnerUUID"] = Util.ToRawUuidString(land.ownerID); + row["IsGroupOwned"] = land.isGroupOwned; + row["Area"] = land.area; + row["AuctionID"] = land.auctionID; //Unemplemented + row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory + row["ClaimDate"] = land.claimDate; + row["ClaimPrice"] = land.claimPrice; + row["GroupUUID"] = Util.ToRawUuidString(land.groupID); + row["SalePrice"] = land.salePrice; + row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus + row["LandFlags"] = land.landFlags; + row["LandingType"] = land.landingType; + row["MediaAutoScale"] = land.mediaAutoScale; + row["MediaTextureUUID"] = Util.ToRawUuidString(land.mediaID); + row["MediaURL"] = land.mediaURL; + row["MusicURL"] = land.musicURL; + row["PassHours"] = land.passHours; + row["PassPrice"] = land.passPrice; + row["SnapshotUUID"] = Util.ToRawUuidString(land.snapshotID); + row["UserLocationX"] = land.userLocation.X; + row["UserLocationY"] = land.userLocation.Y; + row["UserLocationZ"] = land.userLocation.Z; + row["UserLookAtX"] = land.userLookAt.X; + row["UserLookAtY"] = land.userLookAt.Y; + row["UserLookAtZ"] = land.userLookAt.Z; + } + + private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID) + { + row["LandUUID"] = Util.ToRawUuidString(parcelID); + row["AccessUUID"] = Util.ToRawUuidString(entry.AgentID); + row["Flags"] = entry.Flags; + } + + 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"]); + + byte[] textureEntry = (byte[]) row["Texture"]; + s.TextureEntry = textureEntry; + + s.ExtraParams = (byte[]) row["ExtraParams"]; + + try + { + s.State = Convert.ToByte(row["State"]); + } + catch (InvalidCastException) + { + // Database table was created before we got here and needs to be created! :P + + using ( + MySqlCommand cmd = + new MySqlCommand( + "ALTER TABLE `primshapes` ADD COLUMN `State` int NOT NULL default 0;", + m_connection)) + { + cmd.ExecuteNonQuery(); + } + } + + return s; + } + + private void fillShapeRow(DataRow row, SceneObjectPart prim) + { + PrimitiveBaseShape s = prim.Shape; + row["UUID"] = Util.ToRawUuidString(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; + row["Texture"] = s.TextureEntry; + row["ExtraParams"] = s.ExtraParams; + try + { + row["State"] = s.State; + } + catch (MySqlException) + { + // Database table was created before we got here and needs to be created! :P + using ( + MySqlCommand cmd = + new MySqlCommand( + "ALTER TABLE `primshapes` ADD COLUMN `State` int NOT NULL default 0;", + m_connection)) + { + cmd.ExecuteNonQuery(); + } + } + } + + private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID) + { + DataTable prims = m_dataSet.Tables["prims"]; + DataTable shapes = m_dataSet.Tables["primshapes"]; + + DataRow primRow = prims.Rows.Find(Util.ToRawUuidString(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(Util.ToRawUuidString(prim.UUID)); + if (shapeRow == null) + { + shapeRow = shapes.NewRow(); + fillShapeRow(shapeRow, prim); + shapes.Rows.Add(shapeRow); + } + else + { + fillShapeRow(shapeRow, prim); + } + } + + // see IRegionDatastore + public void StorePrimInventory(LLUUID primID, ICollection items) + { + if (!persistPrimInventories) + return; + + m_log.InfoFormat("[DATASTORE]: Persisting Prim Inventory with prim ID {0}", primID); + + // For now, we're just going to crudely remove all the previous inventory items + // no matter whether they have changed or not, and replace them with the current set. + lock (m_dataSet) + { + RemoveItems(primID); + + // repalce with current inventory details + foreach (TaskInventoryItem newItem in items) + { +// m_log.InfoFormat( +// "[DATASTORE]: " + +// "Adding item {0}, {1} to prim ID {2}", +// newItem.Name, newItem.ItemID, newItem.ParentPartID); + + DataRow newItemRow = m_itemsTable.NewRow(); + fillItemRow(newItemRow, newItem); + m_itemsTable.Rows.Add(newItemRow); + } + } + + Commit(); + } + + /*********************************************************************** + * + * 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 MySqlCommand 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 += ")"; + MySqlCommand cmd = new MySqlCommand(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(createMySqlParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + private MySqlCommand createUpdateCommand(string table, string pk, DataTable dt) + { + string sql = "update " + table + " set "; + string subsql = String.Empty; + 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; + MySqlCommand cmd = new MySqlCommand(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(createMySqlParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + private string defineTable(DataTable dt) + { + string sql = "create table " + dt.TableName + "("; + string subsql = String.Empty; + foreach (DataColumn col in dt.Columns) + { + if (subsql.Length > 0) + { + // a map function would rock so much here + subsql += ",\n"; + } + subsql += col.ColumnName + " " + MySqlType(col.DataType); + if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0]) + { + subsql += " primary key"; + } + } + sql += subsql; + sql += ")"; + + //m_log.InfoFormat("[DATASTORE]: defineTable() sql {0}", sql); + + return sql; + } + + /*********************************************************************** + * + * 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 MySqlParameters 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 MySql parameter + private MySqlParameter createMySqlParameter(string name, Type type) + { + MySqlParameter param = new MySqlParameter(); + param.ParameterName = "?" + name; + param.DbType = dbtypeFromType(type); + param.SourceColumn = name; + param.SourceVersion = DataRowVersion.Current; + return param; + } + +// TODO: unused +// private MySqlParameter createParamWithValue(string name, Type type, Object o) +// { +// MySqlParameter param = createMySqlParameter(name, type); +// param.Value = o; +// return param; +// } + + private void SetupPrimCommands(MySqlDataAdapter da, MySqlConnection conn) + { + MySqlCommand insertCommand = createInsertCommand("prims", m_primTable); + insertCommand.Connection = conn; + da.InsertCommand = insertCommand; + + MySqlCommand updateCommand = createUpdateCommand("prims", "UUID=?UUID", m_primTable); + updateCommand.Connection = conn; + da.UpdateCommand = updateCommand; + + MySqlCommand delete = new MySqlCommand("delete from prims where UUID=?UUID"); + delete.Parameters.Add(createMySqlParameter("UUID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void SetupItemsCommands(MySqlDataAdapter da, MySqlConnection conn) + { + da.InsertCommand = createInsertCommand("primitems", m_itemsTable); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("primitems", "itemID = ?itemID", m_itemsTable); + da.UpdateCommand.Connection = conn; + + MySqlCommand delete = new MySqlCommand("delete from primitems where itemID = ?itemID"); + delete.Parameters.Add(createMySqlParameter("itemID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void SetupTerrainCommands(MySqlDataAdapter da, MySqlConnection conn) + { + da.InsertCommand = createInsertCommand("terrain", m_dataSet.Tables["terrain"]); + da.InsertCommand.Connection = conn; + } + + private void setupLandCommands(MySqlDataAdapter da, MySqlConnection conn) + { + da.InsertCommand = createInsertCommand("land", m_dataSet.Tables["land"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("land", "UUID=?UUID", m_dataSet.Tables["land"]); + da.UpdateCommand.Connection = conn; + } + + private void setupLandAccessCommands(MySqlDataAdapter da, MySqlConnection conn) + { + da.InsertCommand = createInsertCommand("landaccesslist", m_dataSet.Tables["landaccesslist"]); + da.InsertCommand.Connection = conn; + } + + private void SetupShapeCommands(MySqlDataAdapter da, MySqlConnection conn) + { + da.InsertCommand = createInsertCommand("primshapes", m_dataSet.Tables["primshapes"]); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("primshapes", "UUID=?UUID", m_dataSet.Tables["primshapes"]); + da.UpdateCommand.Connection = conn; + + MySqlCommand delete = new MySqlCommand("delete from primshapes where UUID = ?UUID"); + delete.Parameters.Add(createMySqlParameter("UUID", typeof (String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void InitDB(MySqlConnection conn) + { + string createPrims = defineTable(createPrimTable()); + string createShapes = defineTable(createShapeTable()); + string createItems = defineTable(createItemsTable()); + string createTerrain = defineTable(createTerrainTable()); + string createLand = defineTable(createLandTable()); + string createLandAccessList = defineTable(createLandAccessListTable()); + + MySqlCommand pcmd = new MySqlCommand(createPrims, conn); + MySqlCommand scmd = new MySqlCommand(createShapes, conn); + MySqlCommand icmd = new MySqlCommand(createItems, conn); + MySqlCommand tcmd = new MySqlCommand(createTerrain, conn); + MySqlCommand lcmd = new MySqlCommand(createLand, conn); + MySqlCommand lalcmd = new MySqlCommand(createLandAccessList, conn); + + if (conn.State != ConnectionState.Open) + { + try + { + conn.Open(); + } + catch (Exception ex) + { + m_log.Error("[MySql]: Error connecting to MySQL server: " + ex.Message); + m_log.Error("[MySql]: Application is terminating!"); + System.Threading.Thread.CurrentThread.Abort(); + } + } + + try + { + pcmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: Primitives Table Already Exists: {0}", e); + } + + try + { + scmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: Shapes Table Already Exists: {0}", e); + } + + try + { + icmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: Items Table Already Exists: {0}", e); + } + + try + { + tcmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: Terrain Table Already Exists: {0}", e); + } + + try + { + lcmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: Land Table Already Exists: {0}", e); + } + + try + { + lalcmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + m_log.WarnFormat("[MySql]: LandAccessList Table Already Exists: {0}", e); + } + conn.Close(); + } + + private bool TestTables(MySqlConnection conn) + { + MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, conn); + MySqlDataAdapter pDa = new MySqlDataAdapter(primSelectCmd); + MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, conn); + MySqlDataAdapter sDa = new MySqlDataAdapter(shapeSelectCmd); + MySqlCommand itemsSelectCmd = new MySqlCommand(m_itemsSelect, conn); + MySqlDataAdapter iDa = new MySqlDataAdapter(itemsSelectCmd); + MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, conn); + MySqlDataAdapter tDa = new MySqlDataAdapter(terrainSelectCmd); + MySqlCommand landSelectCmd = new MySqlCommand(m_landSelect, conn); + MySqlDataAdapter lDa = new MySqlDataAdapter(landSelectCmd); + MySqlCommand landAccessListSelectCmd = new MySqlCommand(m_landAccessListSelect, conn); + MySqlDataAdapter lalDa = new MySqlDataAdapter(landAccessListSelectCmd); + + DataSet tmpDS = new DataSet(); + try + { + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + + if (persistPrimInventories) + iDa.Fill(tmpDS, "primitems"); + + tDa.Fill(tmpDS, "terrain"); + lDa.Fill(tmpDS, "land"); + lalDa.Fill(tmpDS, "landaccesslist"); + } + catch (MySqlException) + { + m_log.Info("[DATASTORE]: MySql Database doesn't exist... creating"); + InitDB(conn); + } + + pDa.Fill(tmpDS, "prims"); + sDa.Fill(tmpDS, "primshapes"); + + if (persistPrimInventories) + iDa.Fill(tmpDS, "primitems"); + + tDa.Fill(tmpDS, "terrain"); + lDa.Fill(tmpDS, "land"); + lalDa.Fill(tmpDS, "landaccesslist"); + + foreach (DataColumn col in createPrimTable().Columns) + { + if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createShapeTable().Columns) + { + if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName); + return false; + } + } + + // XXX primitems should probably go here eventually + + foreach (DataColumn col in createTerrainTable().Columns) + { + if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createLandTable().Columns) + { + if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + foreach (DataColumn col in createLandAccessListTable().Columns) + { + if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName)) + { + m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName); + return false; + } + } + + return true; + } + + /*********************************************************************** + * + * Type conversion functions + * + **********************************************************************/ + + private DbType dbtypeFromType(Type type) + { + if (type == typeof (String)) + { + return DbType.String; + } + else if (type == typeof (Int32)) + { + return DbType.Int32; + } + else if (type == typeof (Double)) + { + return DbType.Double; + } + else if (type == typeof (Byte)) + { + return DbType.Byte; + } + else if (type == typeof (Double)) + { + return DbType.Double; + } + else if (type == typeof (Byte[])) + { + return DbType.Binary; + } + else + { + return DbType.String; + } + } + + // this is something we'll need to implement for each db + // slightly differently. + private string MySqlType(Type type) + { + if (type == typeof (String)) + { + return "varchar(255)"; + } + else if (type == typeof (Int32)) + { + return "integer"; + } + else if (type == typeof (Int64)) + { + return "bigint"; + } + else if (type == typeof (Double)) + { + return "float"; + } + else if (type == typeof (Byte[])) + { + return "longblob"; + } + else + { + return "string"; + } + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs new file mode 100644 index 0000000..61ab067 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLGridData.cs @@ -0,0 +1,402 @@ +/* + * 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.Security.Cryptography; +using System.Text; +using System.Text.RegularExpressions; +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// A MySQL Interface for the Grid Server + /// + public class MySQLGridData : GridDataBase + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// MySQL Database Manager + /// + private MySQLManager database; + + /// + /// Initialises the Grid Interface + /// + override public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); + string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname"); + string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database"); + string settingUsername = GridDataMySqlFile.ParseFileReadValue("username"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling"); + string settingPort = GridDataMySqlFile.ParseFileReadValue("port"); + + database = + new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling, + settingPort); + + TestTables(); + } + + #region Test and initialization code + + /// + /// Ensure that the user related tables exists and are at the latest version + /// + private void TestTables() + { + Dictionary tableList = new Dictionary(); + + tableList["regions"] = null; + database.GetTableVersion(tableList); + + UpgradeRegionsTable(tableList["regions"]); + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeRegionsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateRegionsTable.sql"); + return; + } + if (oldVersion.Contains("Rev. 1")) + { + database.ExecuteResourceSql("UpgradeRegionsTableToVersion2.sql"); + return; + } + if (oldVersion.Contains("Rev. 2")) + { + database.ExecuteResourceSql("UpgradeRegionsTableToVersion3.sql"); + return; + } + } + + #endregion + + /// + /// Shuts down the grid interface + /// + override public void Close() + { + database.Close(); + } + + /// + /// Returns the plugin name + /// + /// Plugin name + override public string getName() + { + return "MySql OpenGridData"; + } + + /// + /// Returns the plugin version + /// + /// Plugin version + override public string getVersion() + { + return "0.1"; + } + + /// + /// Returns all the specified region profiles within coordates -- coordinates are inclusive + /// + /// Minimum X coordinate + /// Minimum Y coordinate + /// Maximum X coordinate + /// Maximum Y coordinate + /// + override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?xmin"] = xmin.ToString(); + param["?ymin"] = ymin.ToString(); + param["?xmax"] = xmax.ToString(); + param["?ymax"] = ymax.ToString(); + + IDbCommand result = + database.Query( + "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax", + param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row; + + List rows = new List(); + + while ((row = database.readSimRow(reader)) != null) + { + rows.Add(row); + } + reader.Close(); + result.Dispose(); + + return rows.ToArray(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a sim profile from it's location + /// + /// Region location handle + /// Sim profile + override public RegionProfileData GetProfileByHandle(ulong handle) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?handle"] = handle.ToString(); + + IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row = database.readSimRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a sim profile from it's UUID + /// + /// The region UUID + /// The sim profile + override public RegionProfileData GetProfileByLLUUID(LLUUID uuid) + { + try + { + lock (database) + { + 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.readSimRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a sim profile from it's Region name string + /// + /// The region name search query + /// The sim profile + override public RegionProfileData GetProfileByString(string regionName) + { + if (regionName.Length > 2) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + // Add % because this is a like query. + param["?regionName"] = regionName + "%"; + // Order by statement will return shorter matches first. Only returns one record or no record. + IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1", param); + IDataReader reader = result.ExecuteReader(); + + RegionProfileData row = database.readSimRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + else + { + m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters"); + return null; + } + } + + /// + /// Adds a new profile to the database + /// + /// The profile to add + /// Successful? + override public DataResponse AddProfile(RegionProfileData profile) + { + lock (database) + { + if (database.insertRegion(profile)) + { + return DataResponse.RESPONSE_OK; + } + else + { + return DataResponse.RESPONSE_ERROR; + } + } + } + + /// + /// Deletes a profile from the database + /// + /// The profile to delete + /// Successful? + //public DataResponse DeleteProfile(RegionProfileData profile) + public DataResponse DeleteProfile(string uuid) + { + lock (database) + { + if (database.deleteRegion(uuid)) + { + return DataResponse.RESPONSE_OK; + } + else + { + return DataResponse.RESPONSE_ERROR; + } + } + } + + /// + /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret. + /// + /// The UUID of the challenger + /// The attempted regionHandle of the challenger + /// The secret + /// Whether the secret and regionhandle match the database entry for UUID + override public bool AuthenticateSim(LLUUID uuid, ulong handle, string authkey) + { + bool throwHissyFit = false; // Should be true by 1.0 + + if (throwHissyFit) + throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential."); + + RegionProfileData data = GetProfileByLLUUID(uuid); + + return (handle == data.regionHandle && authkey == data.regionSecret); + } + + /// + /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region + /// + /// This requires a security audit. + /// + /// + /// + /// + /// + public bool AuthenticateSim(LLUUID uuid, ulong handle, string authhash, string challenge) + { + SHA512Managed HashProvider = new SHA512Managed(); + ASCIIEncoding TextProvider = new ASCIIEncoding(); + + byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge); + byte[] hash = HashProvider.ComputeHash(stream); + + return false; + } + + override public ReservationData GetReservationAtPoint(uint x, uint y) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?x"] = x.ToString(); + param["?y"] = y.ToString(); + IDbCommand result = + database.Query( + "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y", + param); + IDataReader reader = result.ExecuteReader(); + + ReservationData row = database.readReservationRow(reader); + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs new file mode 100644 index 0000000..4165d8f --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs @@ -0,0 +1,648 @@ +/* + * 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 libsecondlife; +using MySql.Data.MySqlClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// A MySQL interface for the inventory server + /// + public class MySQLInventoryData : IInventoryData + { + private static readonly log4net.ILog m_log + = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database manager + /// + private MySQLManager database; + + /// + /// Loads and initialises this database plugin + /// + public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); + string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname"); + string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database"); + string settingUsername = GridDataMySqlFile.ParseFileReadValue("username"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling"); + string settingPort = GridDataMySqlFile.ParseFileReadValue("port"); + + database = + new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling, + settingPort); + TestTables(database.Connection); + } + + #region Test and initialization code + + private void UpgradeFoldersTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateFoldersTable.sql"); + return; + } + + // if the table is already at the current version, then we can exit immediately +// if (oldVersion == "Rev. 2") +// return; + +// database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql"); + } + + private void UpgradeItemsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateItemsTable.sql"); + return; + } + + // if the table is already at the current version, then we can exit immediately +// if (oldVersion == "Rev. 2") +// return; + +// database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql"); + } + + private void TestTables(MySqlConnection conn) + { + Dictionary tableList = new Dictionary(); + + tableList["inventoryfolders"] = null; + tableList["inventoryitems"] = null; + + database.GetTableVersion(tableList); + m_log.Info("[MYSQL]: Inventory Folder Version: " + tableList["inventoryfolders"]); + m_log.Info("[MYSQL]: Inventory Items Version: " + tableList["inventoryitems"]); + + UpgradeFoldersTable(tableList["inventoryfolders"]); + UpgradeItemsTable(tableList["inventoryitems"]); + } + + #endregion + + /// + /// The name of this DB provider + /// + /// Name of DB provider + public string getName() + { + return "MySQL 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(); + + MySqlCommand result = + new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid", + database.Connection); + result.Parameters.AddWithValue("?uuid", folderID.ToString()); + MySqlDataReader reader = result.ExecuteReader(); + + while (reader.Read()) + items.Add(readInventoryItem(reader)); + + reader.Close(); + result.Dispose(); + + return items; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.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) + { + MySqlCommand result = + new MySqlCommand( + "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", + database.Connection); + result.Parameters.AddWithValue("?uuid", user.ToString()); + result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString()); + MySqlDataReader 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(); + m_log.Error(e.ToString()); + return null; + } + } + + // see InventoryItemBase.getUserRootFolder + public InventoryFolderBase getUserRootFolder(LLUUID user) + { + try + { + lock (database) + { + MySqlCommand result = + new MySqlCommand( + "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", + database.Connection); + result.Parameters.AddWithValue("?uuid", user.ToString()); + result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString()); + + MySqlDataReader reader = result.ExecuteReader(); + + List items = new List(); + while (reader.Read()) + items.Add(readInventoryFolder(reader)); + + InventoryFolderBase rootFolder = null; + + // There should only ever be one root folder for a user. However, if there's more + // than one we'll simply use the first one rather than failing. It would be even + // nicer to print some message to this effect, but this feels like it's too low a + // to put such a message out, and it's too minor right now to spare the time to + // suitably refactor. + if (items.Count > 0) + { + rootFolder = items[0]; + } + + reader.Close(); + result.Dispose(); + + return rootFolder; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Return a list of folders in a users inventory contained within the specified folder. + /// This method is only used in tests - in normal operation the user always have one, + /// and only one, root folder. + /// + /// The folder to search + /// A list of inventory folders + public List getInventoryFolders(LLUUID parentID) + { + try + { + lock (database) + { + MySqlCommand result = + new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid", + database.Connection); + result.Parameters.AddWithValue("?uuid", parentID.ToString()); + MySqlDataReader 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(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Reads a one item from an SQL result + /// + /// The SQL Result + /// the item read + private InventoryItemBase readInventoryItem(MySqlDataReader 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 = (uint) reader["inventoryNextPermissions"]; + item.inventoryCurrentPermissions = (uint) reader["inventoryCurrentPermissions"]; + item.invType = (int) reader["invType"]; + item.creatorsID = new LLUUID((string) reader["creatorID"]); + item.inventoryBasePermissions = (uint) reader["inventoryBasePermissions"]; + item.inventoryEveryOnePermissions = (uint) reader["inventoryEveryOnePermissions"]; + return item; + } + catch (MySqlException e) + { + m_log.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(); + + MySqlCommand result = + new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection); + result.Parameters.AddWithValue("?uuid", itemID.ToString()); + MySqlDataReader reader = result.ExecuteReader(); + + InventoryItemBase item = null; + if (reader.Read()) + item = readInventoryItem(reader); + + reader.Close(); + result.Dispose(); + + return item; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.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(MySqlDataReader 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) + { + m_log.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) + { + MySqlCommand result = + new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection); + result.Parameters.AddWithValue("?uuid", folderID.ToString()); + MySqlDataReader reader = result.ExecuteReader(); + + reader.Read(); + InventoryFolderBase folder = readInventoryFolder(reader); + reader.Close(); + result.Dispose(); + + return folder; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Adds a specified item to the database + /// + /// The inventory item + public void addInventoryItem(InventoryItemBase item) + { + string sql = + "REPLACE INTO inventoryitems (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 + { + MySqlCommand result = new MySqlCommand(sql, database.Connection); + result.Parameters.AddWithValue("?inventoryID", item.inventoryID.ToString()); + result.Parameters.AddWithValue("?assetID", item.assetID.ToString()); + result.Parameters.AddWithValue("?assetType", item.assetType.ToString()); + result.Parameters.AddWithValue("?parentFolderID", item.parentFolderID.ToString()); + result.Parameters.AddWithValue("?avatarID", item.avatarID.ToString()); + result.Parameters.AddWithValue("?inventoryName", item.inventoryName); + result.Parameters.AddWithValue("?inventoryDescription", item.inventoryDescription); + result.Parameters.AddWithValue("?inventoryNextPermissions", item.inventoryNextPermissions.ToString()); + result.Parameters.AddWithValue("?inventoryCurrentPermissions", + item.inventoryCurrentPermissions.ToString()); + result.Parameters.AddWithValue("?invType", item.invType); + result.Parameters.AddWithValue("?creatorID", item.creatorsID.ToString()); + result.Parameters.AddWithValue("?inventoryBasePermissions", item.inventoryBasePermissions); + result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.inventoryEveryOnePermissions); + result.ExecuteNonQuery(); + result.Dispose(); + } + catch (MySqlException e) + { + m_log.Error(e.ToString()); + } + } + + /// + /// Updates the specified inventory item + /// + /// Inventory item to update + public void updateInventoryItem(InventoryItemBase item) + { + addInventoryItem(item); + } + + /// + /// + /// + /// + public void deleteInventoryItem(LLUUID itemID) + { + try + { + MySqlCommand cmd = + new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection); + cmd.Parameters.AddWithValue("?uuid", itemID.ToString()); + cmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + } + + /// + /// Creates a new inventory folder + /// + /// Folder to create + public void addInventoryFolder(InventoryFolderBase folder) + { + string sql = + "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES "; + sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)"; + + MySqlCommand cmd = new MySqlCommand(sql, database.Connection); + cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString()); + cmd.Parameters.AddWithValue("?agentID", folder.agentID.ToString()); + cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString()); + cmd.Parameters.AddWithValue("?folderName", folder.name); + cmd.Parameters.AddWithValue("?type", (short) folder.type); + cmd.Parameters.AddWithValue("?version", folder.version); + + try + { + lock (database) + { + cmd.ExecuteNonQuery(); + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + + /// + /// Updates an inventory folder + /// + /// Folder to update + public void updateInventoryFolder(InventoryFolderBase folder) + { + addInventoryFolder(folder); + } + + /// Creates a new inventory folder + /// + /// Folder to create + public void moveInventoryFolder(InventoryFolderBase folder) + { + string sql = + "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID"; + + MySqlCommand cmd = new MySqlCommand(sql, database.Connection); + cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString()); + cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString()); + + try + { + lock (database) + { + cmd.ExecuteNonQuery(); + } + } + catch (Exception e) + { + m_log.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); + } + + // See IInventoryData + public 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 + { + MySqlCommand cmd = + new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection); + cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); + + lock (database) + { + cmd.ExecuteNonQuery(); + } + } + catch (MySqlException e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + } + + protected void deleteItemsInFolder(LLUUID folderID) + { + try + { + MySqlCommand cmd = + new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection); + cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); + + lock (database) + { + cmd.ExecuteNonQuery(); + } + } + catch (MySqlException e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + } + + /// + /// Delete an inventory folder + /// + /// Id of folder to delete + public void deleteInventoryFolder(LLUUID folderID) + { + 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); + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLLogData.cs b/OpenSim/Data/MySQL/MySQLLogData.cs new file mode 100644 index 0000000..480446f --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLLogData.cs @@ -0,0 +1,106 @@ +/* + * 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. + */ + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// An interface to the log database for MySQL + /// + internal class MySQLLogData : ILogData + { + /// + /// The database manager + /// + public MySQLManager database; + + /// + /// Artificial constructor called when the plugin is loaded + /// + public void Initialise() + { + IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); + string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname"); + string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database"); + string settingUsername = GridDataMySqlFile.ParseFileReadValue("username"); + string settingPassword = GridDataMySqlFile.ParseFileReadValue("password"); + string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling"); + string settingPort = GridDataMySqlFile.ParseFileReadValue("port"); + + database = + new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling, + settingPort); + } + + /// + /// 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 "MySQL 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"; + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs new file mode 100644 index 0000000..579667b --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLManager.cs @@ -0,0 +1,909 @@ +/* + * 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 System.Reflection; +using libsecondlife; +using MySql.Data.MySqlClient; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// A MySQL Database manager + /// + internal class MySQLManager + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// The database connection object + /// + private MySqlConnection dbcon; + + /// + /// Connection string for ADO.net + /// + private string connectionString; + + /// + /// Initialises and creates a new MySQL connection and maintains it. + /// + /// The MySQL server being connected to + /// The name of the MySQL database being used + /// 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 MySQLManager(string hostname, string database, string username, string password, string cpooling, + string port) + { + try + { + connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + + username + ";Password=" + password + ";Pooling=" + cpooling + ";"; + dbcon = new MySqlConnection(connectionString); + + try + { + dbcon.Open(); + } + catch(Exception e) + { + throw new Exception( "Connection error while using connection string ["+connectionString+"]", e ); + } + + m_log.Info("[MYSQL]: Connection established"); + } + catch (Exception e) + { + throw new Exception("Error initialising MySql Database: " + e.ToString()); + } + } + + /// + /// Get the connection being used + /// + public MySqlConnection Connection + { + get { return dbcon; } + } + + /// + /// Shuts down the database connection + /// + public void Close() + { + dbcon.Close(); + dbcon = null; + } + + /// + /// Reconnects to the database + /// + public void Reconnect() + { + lock (dbcon) + { + try + { + // Close the DB connection + dbcon.Close(); + // Try reopen it + dbcon = new MySqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + m_log.Error("Unable to reconnect to database " + e.ToString()); + } + } + } + + /// + /// Returns the version of this DB provider + /// + /// A string containing the DB provider + public string getVersion() + { + Module module = 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); + } + + /// + /// Extract a named string resource from the embedded resources + /// + /// name of embedded resource + /// string contained within the embedded resource + private string getResourceString(string name) + { + Assembly assem = 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)); + } + + /// + /// Execute a SQL statement stored in a resource, as a string + /// + /// + public void ExecuteResourceSql(string name) + { + MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); + cmd.ExecuteNonQuery(); + } + + /// + /// Given a list of tables, return the version of the tables, as seen in the database + /// + /// + public void GetTableVersion(Dictionary tableList) + { + lock (dbcon) + { + MySqlCommand tablesCmd = + new MySqlCommand( + "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", + dbcon); + tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); + using (MySqlDataReader tables = tablesCmd.ExecuteReader()) + { + while (tables.Read()) + { + try + { + string tableName = (string) tables["TABLE_NAME"]; + string comment = (string) tables["TABLE_COMMENT"]; + if (tableList.ContainsKey(tableName)) + { + tableList[tableName] = comment; + } + } + catch (Exception e) + { + m_log.Error(e.ToString()); + } + } + tables.Close(); + } + } + } + + // TODO: at some time this code should be cleaned up + + /// + /// 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 + /// The parameters - index so that @y is indexed as 'y' + /// A MySQL DB Command + public IDbCommand Query(string sql, Dictionary parameters) + { + try + { + MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); + dbcommand.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + dbcommand.Parameters.AddWithValue(param.Key, param.Value); + } + + return (IDbCommand) dbcommand; + } + catch + { + lock (dbcon) + { + // Close the DB connection + try + { + dbcon.Close(); + } + catch + { + } + + // Try to reopen it + try + { + dbcon = new MySqlConnection(connectionString); + dbcon.Open(); + } + catch (Exception e) + { + m_log.Error("Unable to reconnect to database " + e.ToString()); + } + + // Run the query again + try + { + MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); + dbcommand.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + dbcommand.Parameters.AddWithValue(param.Key, param.Value); + } + + return (IDbCommand) dbcommand; + } + catch (Exception e) + { + // Return null if it fails. + m_log.Error("Failed during Query generation: " + e.ToString()); + return null; + } + } + } + } + + /// + /// Reads a region row from a database reader + /// + /// An active database reader + /// A region profile + public RegionProfileData readSimRow(IDataReader reader) + { + RegionProfileData retval = new RegionProfileData(); + + if (reader.Read()) + { + // Region Main gotta-have-or-we-return-null parts + if (!UInt64.TryParse(reader["regionHandle"].ToString(), out retval.regionHandle)) + return null; + if (!LLUUID.TryParse((string)reader["uuid"], out retval.UUID)) + return null; + + // non-critical parts + retval.regionName = (string)reader["regionName"]; + retval.originUUID = new LLUUID((string) reader["originUUID"]); + + // Secrets + retval.regionRecvKey = (string) reader["regionRecvKey"]; + retval.regionSecret = (string) reader["regionSecret"]; + retval.regionSendKey = (string) reader["regionSendKey"]; + + // Region Server + retval.regionDataURI = (string) reader["regionDataURI"]; + retval.regionOnline = false; // Needs to be pinged before this can be set. + retval.serverIP = (string) reader["serverIP"]; + retval.serverPort = (uint) reader["serverPort"]; + retval.serverURI = (string) reader["serverURI"]; + retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString()); + retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString()); + + // Location + retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString()); + retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString()); + retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString()); + + // Neighbours - 0 = No Override + retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString()); + retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString()); + retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString()); + retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString()); + + // Assets + retval.regionAssetURI = (string) reader["regionAssetURI"]; + retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; + retval.regionAssetSendKey = (string) reader["regionAssetSendKey"]; + + // Userserver + retval.regionUserURI = (string) reader["regionUserURI"]; + retval.regionUserRecvKey = (string) reader["regionUserRecvKey"]; + retval.regionUserSendKey = (string) reader["regionUserSendKey"]; + + // World Map Addition + LLUUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID); + LLUUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid); + } + else + { + return null; + } + return retval; + } + + /// + /// Reads a reservation row from a database reader + /// + /// An active database reader + /// A reservation data object + public ReservationData readReservationRow(IDataReader reader) + { + ReservationData retval = new ReservationData(); + if (reader.Read()) + { + retval.gridRecvKey = (string) reader["gridRecvKey"]; + retval.gridSendKey = (string) reader["gridSendKey"]; + retval.reservationCompany = (string) reader["resCompany"]; + retval.reservationMaxX = Convert.ToInt32(reader["resXMax"].ToString()); + retval.reservationMaxY = Convert.ToInt32(reader["resYMax"].ToString()); + retval.reservationMinX = Convert.ToInt32(reader["resXMin"].ToString()); + retval.reservationMinY = Convert.ToInt32(reader["resYMin"].ToString()); + retval.reservationName = (string) reader["resName"]; + retval.status = Convert.ToInt32(reader["status"].ToString()) == 1; + LLUUID.TryParse((string) reader["userUUID"], out retval.userUUID); + } + 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 + if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID)) + return null; + LLUUID.TryParse((string) reader["sessionID"], out retval.sessionID); + LLUUID.TryParse((string)reader["secureSessionID"], out retval.secureSessionID); + + // Agent Who? + retval.agentIP = (string) reader["agentIP"]; + retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString()); + retval.agentOnline = Convert.ToBoolean(Convert.ToInt16(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 = new LLUUID((string)reader["currentRegion"]); + retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); + LLVector3.TryParse((string) reader["currentPos"], out retval.currentPos); + } + else + { + return null; + } + return retval; + } + + /// + /// 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()) + { + if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID)) + return null; + 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()); + + if (reader.IsDBNull(reader.GetOrdinal("profileAboutText"))) + retval.profileAboutText = ""; + else + retval.profileAboutText = (string) reader["profileAboutText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstText"))) + retval.profileFirstText = ""; + else + retval.profileFirstText = (string)reader["profileFirstText"]; + + if (reader.IsDBNull(reader.GetOrdinal("profileImage"))) + retval.profileImage = LLUUID.Zero; + else + LLUUID.TryParse((string)reader["profileImage"], out retval.profileImage); + + if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage"))) + retval.profileFirstImage = LLUUID.Zero; + else + LLUUID.TryParse((string)reader["profileFirstImage"], out retval.profileFirstImage); + + if(reader.IsDBNull(reader.GetOrdinal("webLoginKey"))) + { + retval.webLoginKey = LLUUID.Zero; + } + else + { + LLUUID.TryParse((string)reader["webLoginKey"], out retval.webLoginKey); + } + } + else + { + return null; + } + return retval; + } + + /// + /// 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; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + m_log.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(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, + LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey) + { + m_log.Debug("[MySQLManager]: Fetching profile for " + uuid.ToString()); + string sql = + "INSERT INTO users (`UUID`, `username`, `lastname`, `passwordHash`, `passwordSalt`, `homeRegion`, "; + sql += + "`homeLocationX`, `homeLocationY`, `homeLocationZ`, `homeLookAtX`, `homeLookAtY`, `homeLookAtZ`, `created`, "; + sql += + "`lastLogin`, `userInventoryURI`, `userAssetURI`, `profileCanDoMask`, `profileWantDoMask`, `profileAboutText`, "; + sql += "`profileFirstText`, `profileImage`, `profileFirstImage`, `webLoginKey`) VALUES "; + + sql += "(?UUID, ?username, ?lastname, ?passwordHash, ?passwordSalt, ?homeRegion, "; + sql += + "?homeLocationX, ?homeLocationY, ?homeLocationZ, ?homeLookAtX, ?homeLookAtY, ?homeLookAtZ, ?created, "; + sql += + "?lastLogin, ?userInventoryURI, ?userAssetURI, ?profileCanDoMask, ?profileWantDoMask, ?profileAboutText, "; + sql += "?profileFirstText, ?profileImage, ?profileFirstImage, ?webLoginKey)"; + + Dictionary parameters = new Dictionary(); + parameters["?UUID"] = uuid.ToString(); + parameters["?username"] = username.ToString(); + parameters["?lastname"] = lastname.ToString(); + parameters["?passwordHash"] = passwordHash.ToString(); + parameters["?passwordSalt"] = passwordSalt.ToString(); + parameters["?homeRegion"] = homeRegion.ToString(); + parameters["?homeLocationX"] = homeLocX.ToString(); + parameters["?homeLocationY"] = homeLocY.ToString(); + parameters["?homeLocationZ"] = homeLocZ.ToString(); + parameters["?homeLookAtX"] = homeLookAtX.ToString(); + parameters["?homeLookAtY"] = homeLookAtY.ToString(); + parameters["?homeLookAtZ"] = homeLookAtZ.ToString(); + parameters["?created"] = created.ToString(); + parameters["?lastLogin"] = lastlogin.ToString(); + parameters["?userInventoryURI"] = String.Empty; + parameters["?userAssetURI"] = String.Empty; + parameters["?profileCanDoMask"] = "0"; + parameters["?profileWantDoMask"] = "0"; + parameters["?profileAboutText"] = aboutText; + parameters["?profileFirstText"] = firstText; + parameters["?profileImage"] = profileImage.ToString(); + parameters["?profileFirstImage"] = firstImage.ToString(); + parameters["?webLoginKey"] = string.Empty; + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + m_log.Debug("[MySQLManager]: Fetch user retval == " + returnval.ToString()); + 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 updateUserRow(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, + LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey) + { + string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname "; + sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , "; + sql += "`homeRegion` = ?homeRegion , `homeLocationX` = ?homeLocationX , "; + sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , "; + sql += "`homeLookAtX` = ?homeLookAtX , `homeLookAtY` = ?homeLookAtY , "; + sql += "`homeLookAtZ` = ?homeLookAtZ , `created` = ?created , `lastLogin` = ?lastLogin , "; + sql += "`userInventoryURI` = ?userInventoryURI , `userAssetURI` = ?userAssetURI , "; + sql += "`profileCanDoMask` = ?profileCanDoMask , `profileWantDoMask` = ?profileWantDoMask , "; + sql += "`profileAboutText` = ?profileAboutText , `profileFirstText` = ?profileFirstText, "; + sql += "`profileImage` = ?profileImage , `profileFirstImage` = ?profileFirstImage , "; + sql += "`webLoginKey` = ?webLoginKey WHERE UUID = ?UUID"; + + Dictionary parameters = new Dictionary(); + parameters["?UUID"] = uuid.ToString(); + parameters["?username"] = username.ToString(); + parameters["?lastname"] = lastname.ToString(); + parameters["?passwordHash"] = passwordHash.ToString(); + parameters["?passwordSalt"] = passwordSalt.ToString(); + parameters["?homeRegion"] = homeRegion.ToString(); + parameters["?homeLocationX"] = homeLocX.ToString(); + parameters["?homeLocationY"] = homeLocY.ToString(); + parameters["?homeLocationZ"] = homeLocZ.ToString(); + parameters["?homeLookAtX"] = homeLookAtX.ToString(); + parameters["?homeLookAtY"] = homeLookAtY.ToString(); + parameters["?homeLookAtZ"] = homeLookAtZ.ToString(); + parameters["?created"] = created.ToString(); + parameters["?lastLogin"] = lastlogin.ToString(); + parameters["?userInventoryURI"] = inventoryURI; + parameters["?userAssetURI"] = assetURI; + parameters["?profileCanDoMask"] = "0"; + parameters["?profileWantDoMask"] = "0"; + parameters["?profileAboutText"] = aboutText; + parameters["?profileFirstText"] = firstText; + parameters["?profileImage"] = profileImage.ToString(); + parameters["?profileFirstImage"] = firstImage.ToString(); + parameters["?webLoginKey"] = webLoginKey.ToString(); + + bool returnval = false; + try + { + IDbCommand result = Query(sql, parameters); + + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + m_log.Debug("[MySQLManager]: update user retval == " + returnval.ToString()); + return returnval; + } + + /// + /// Inserts a new region into the database + /// + /// The region to insert + /// Success? + public bool insertRegion(RegionProfileData regiondata) + { + bool GRID_ONLY_UPDATE_NECESSARY_DATA = false; + + string sql = String.Empty; + if (GRID_ONLY_UPDATE_NECESSARY_DATA) + { + sql += "INSERT INTO "; + } + else + { + sql += "REPLACE INTO "; + } + + sql += "regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; + sql += + "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; + + // part of an initial brutish effort to provide accurate information (as per the xml region spec) + // wrt the ownership of a given region + // the (very bad) assumption is that this value is being read and handled inconsistently or + // not at all. Current strategy is to put the code in place to support the validity of this information + // and to roll forward debugging any issues from that point + // + // this particular section of the mod attempts to implement the commit of a supplied value + // server for the UUID of the region's owner (master avatar). It consists of the addition of the column and value to the relevant sql, + // as well as the related parameterization + sql += + "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID) 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, ?regionMapTexture, ?serverHttpPort, ?serverRemotingPort, ?owner_uuid, ?originUUID)"; + + if (GRID_ONLY_UPDATE_NECESSARY_DATA) + { + sql += "ON DUPLICATE KEY UPDATE serverIP = ?serverIP, serverPort = ?serverPort, serverURI = ?serverURI, owner_uuid - ?owner_uuid;"; + } + else + { + sql += ";"; + } + + Dictionary parameters = new Dictionary(); + + parameters["?regionHandle"] = regiondata.regionHandle.ToString(); + parameters["?regionName"] = regiondata.regionName.ToString(); + parameters["?uuid"] = regiondata.UUID.ToString(); + parameters["?regionRecvKey"] = regiondata.regionRecvKey.ToString(); + parameters["?regionSecret"] = regiondata.regionSecret.ToString(); + parameters["?regionSendKey"] = regiondata.regionSendKey.ToString(); + parameters["?regionDataURI"] = regiondata.regionDataURI.ToString(); + parameters["?serverIP"] = regiondata.serverIP.ToString(); + parameters["?serverPort"] = regiondata.serverPort.ToString(); + parameters["?serverURI"] = regiondata.serverURI.ToString(); + parameters["?locX"] = regiondata.regionLocX.ToString(); + parameters["?locY"] = regiondata.regionLocY.ToString(); + parameters["?locZ"] = regiondata.regionLocZ.ToString(); + parameters["?eastOverrideHandle"] = regiondata.regionEastOverrideHandle.ToString(); + parameters["?westOverrideHandle"] = regiondata.regionWestOverrideHandle.ToString(); + parameters["?northOverrideHandle"] = regiondata.regionNorthOverrideHandle.ToString(); + parameters["?southOverrideHandle"] = regiondata.regionSouthOverrideHandle.ToString(); + parameters["?regionAssetURI"] = regiondata.regionAssetURI.ToString(); + parameters["?regionAssetRecvKey"] = regiondata.regionAssetRecvKey.ToString(); + parameters["?regionAssetSendKey"] = regiondata.regionAssetSendKey.ToString(); + parameters["?regionUserURI"] = regiondata.regionUserURI.ToString(); + parameters["?regionUserRecvKey"] = regiondata.regionUserRecvKey.ToString(); + parameters["?regionUserSendKey"] = regiondata.regionUserSendKey.ToString(); + parameters["?regionMapTexture"] = regiondata.regionMapTextureID.ToString(); + parameters["?serverHttpPort"] = regiondata.httpPort.ToString(); + parameters["?serverRemotingPort"] = regiondata.remotingPort.ToString(); + parameters["?owner_uuid"] = regiondata.owner_uuid.ToString(); + parameters["?originUUID"] = regiondata.originUUID.ToString(); + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + //Console.WriteLine(result.CommandText); + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Delete a region from the database + /// + /// The region to insert + /// Success? + //public bool deleteRegion(RegionProfileData regiondata) + public bool deleteRegion(string uuid) + { + bool returnval = false; + + string sql = "DELETE FROM regions WHERE uuid = ?uuid;"; + + Dictionary parameters = new Dictionary(); + + try + { + parameters["?uuid"] = uuid; + + IDbCommand result = Query(sql, parameters); + + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + + /// + /// Creates a new agent and inserts it into the database + /// + /// The agent data to be inserted + /// Success? + public bool insertAgentRow(UserAgentData agentdata) + { + string sql = String.Empty; + sql += "REPLACE INTO "; + sql += "agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES "; + sql += "(?UUID, ?sessionID, ?secureSessionID, ?agentIP, ?agentPort, ?agentOnline, ?loginTime, ?logoutTime, ?currentRegion, ?currentHandle, ?currentPos);"; + Dictionary parameters = new Dictionary(); + + parameters["?UUID"] = agentdata.UUID.ToString(); + parameters["?sessionID"] = agentdata.sessionID.ToString(); + parameters["?secureSessionID"] = agentdata.secureSessionID.ToString(); + parameters["?agentIP"] = agentdata.agentIP.ToString(); + parameters["?agentPort"] = agentdata.agentPort.ToString(); + parameters["?agentOnline"] = (agentdata.agentOnline == true) ? "1" : "0"; + parameters["?loginTime"] = agentdata.loginTime.ToString(); + parameters["?logoutTime"] = agentdata.logoutTime.ToString(); + parameters["?currentRegion"] = agentdata.currentRegion.ToString(); + parameters["?currentHandle"] = agentdata.currentHandle.ToString(); + parameters["?currentPos"] = "<" + ((int)agentdata.currentPos.X).ToString() + "," + ((int)agentdata.currentPos.Y).ToString() + "," + ((int)agentdata.currentPos.Z).ToString() + ">"; + + bool returnval = false; + + try + { + IDbCommand result = Query(sql, parameters); + + //Console.WriteLine(result.CommandText); + int x; + if ((x = result.ExecuteNonQuery()) > 0) + { + returnval = true; + } + result.Dispose(); + } + catch (Exception e) + { + m_log.Error(e.ToString()); + return false; + } + + return returnval; + } + } +} diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs new file mode 100644 index 0000000..fd640ec --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLUserData.cs @@ -0,0 +1,643 @@ +/* + * 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.Text.RegularExpressions; +using libsecondlife; +using OpenSim.Framework.Console; + +namespace OpenSim.Framework.Data.MySQL +{ + /// + /// A database interface class to a user profile storage system + /// + internal class MySQLUserData : UserDataBase + { + private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); + + /// + /// Database manager for MySQL + /// + public MySQLManager database; + + private string m_agentsTableName; + private string m_usersTableName; + private string m_userFriendsTableName; + + /// + /// Loads and initialises the MySQL storage plugin + /// + override public void Initialise() + { + // Load from an INI file connection details + // TODO: move this to XML? Yes, PLEASE! + + IniFile iniFile = new IniFile("mysql_connection.ini"); + string settingHostname = iniFile.ParseFileReadValue("hostname"); + string settingDatabase = iniFile.ParseFileReadValue("database"); + string settingUsername = iniFile.ParseFileReadValue("username"); + string settingPassword = iniFile.ParseFileReadValue("password"); + string settingPooling = iniFile.ParseFileReadValue("pooling"); + string settingPort = iniFile.ParseFileReadValue("port"); + + m_usersTableName = iniFile.ParseFileReadValue("userstablename"); + if( m_usersTableName == null ) + { + m_usersTableName = "users"; + } + + m_userFriendsTableName = iniFile.ParseFileReadValue("userfriendstablename"); + if (m_userFriendsTableName == null) + { + m_userFriendsTableName = "userfriends"; + } + + m_agentsTableName = iniFile.ParseFileReadValue("agentstablename"); + if (m_agentsTableName == null) + { + m_agentsTableName = "agents"; + } + + database = + new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling, + settingPort); + + TestTables(); + } + + #region Test and initialization code + + /// + /// Ensure that the user related tables exists and are at the latest version + /// + private void TestTables() + { + Dictionary tableList = new Dictionary(); + + tableList[m_agentsTableName] = null; + tableList[m_usersTableName] = null; + tableList[m_userFriendsTableName] = null; + database.GetTableVersion(tableList); + + UpgradeAgentsTable(tableList[m_agentsTableName]); + UpgradeUsersTable(tableList[m_usersTableName]); + UpgradeFriendsTable(tableList[m_userFriendsTableName]); + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeAgentsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateAgentsTable.sql"); + return; + } + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeUsersTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateUsersTable.sql"); + return; + } + else if (oldVersion.Contains("Rev. 1")) + { + database.ExecuteResourceSql("UpgradeUsersTableToVersion2.sql"); + return; + } + //m_log.Info("[DB]: DBVers:" + oldVersion); + } + + /// + /// Create or upgrade the table if necessary + /// + /// A null indicates that the table does not + /// currently exist + private void UpgradeFriendsTable(string oldVersion) + { + // null as the version, indicates that the table didn't exist + if (oldVersion == null) + { + database.ExecuteResourceSql("CreateUserFriendsTable.sql"); + return; + } + } + + #endregion + + // see IUserData + override 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 " + m_usersTableName + " 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(); + m_log.Error(e.ToString()); + return null; + } + } + + #region User Friends List Data + + override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) + { + int dtvalue = Util.UnixTimeSinceEpoch(); + + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + param["?friendPerms"] = perms.ToString(); + param["?datetimestamp"] = dtvalue.ToString(); + + try + { + lock (database) + { + IDbCommand adder = + database.Query( + "INSERT INTO `" + m_userFriendsTableName + "` " + + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + + "VALUES " + + "(?ownerID,?friendID,?friendPerms,?datetimestamp)", + param); + adder.ExecuteNonQuery(); + + adder = + database.Query( + "INSERT INTO `" + m_userFriendsTableName + "` " + + "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + + "VALUES " + + "(?friendID,?ownerID,?friendPerms,?datetimestamp)", + param); + adder.ExecuteNonQuery(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return; + } + } + + override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) + { + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + + try + { + lock (database) + { + IDbCommand updater = + database.Query( + "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID", + param); + updater.ExecuteNonQuery(); + + updater = + database.Query( + "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID", + param); + updater.ExecuteNonQuery(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return; + } + } + + override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) + { + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + param["?friendID"] = friend.UUID.ToString(); + param["?friendPerms"] = perms.ToString(); + + try + { + lock (database) + { + IDbCommand updater = + database.Query( + "update " + m_userFriendsTableName + + " SET friendPerms = ?friendPerms " + + "where ownerID = ?ownerID and friendID = ?friendID", + param); + updater.ExecuteNonQuery(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return; + } + } + + override public List GetUserFriendList(LLUUID friendlistowner) + { + List Lfli = new List(); + + Dictionary param = new Dictionary(); + param["?ownerID"] = friendlistowner.UUID.ToString(); + + try + { + lock (database) + { + //Left Join userfriends to itself + IDbCommand result = + database.Query( + "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + + " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", + param); + IDataReader reader = result.ExecuteReader(); + + while (reader.Read()) + { + FriendListItem fli = new FriendListItem(); + fli.FriendListOwner = new LLUUID((string)reader["ownerID"]); + fli.Friend = new LLUUID((string)reader["friendID"]); + fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]); + + // This is not a real column in the database table, it's a joined column from the opposite record + fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); + + Lfli.Add(fli); + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return Lfli; + } + + return Lfli; + } + + #endregion + + override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid) + { + m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); + } + + override public List GeneratePickerResults(LLUUID queryID, string query) + { + List returnlist = new List(); + + Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]"); + + string[] querysplit; + querysplit = query.Split(' '); + if (querysplit.Length == 2) + { + Dictionary param = new Dictionary(); + param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; + param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%"; + try + { + lock (database) + { + IDbCommand result = + database.Query( + "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first AND lastname like ?second LIMIT 100", + param); + IDataReader reader = result.ExecuteReader(); + + while (reader.Read()) + { + Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string) reader["UUID"]); + user.firstName = (string) reader["username"]; + user.lastName = (string) reader["lastname"]; + returnlist.Add(user); + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return returnlist; + } + } + else if (querysplit.Length == 1) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; + + IDbCommand result = + database.Query( + "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first OR lastname like ?first LIMIT 100", + param); + IDataReader reader = result.ExecuteReader(); + + while (reader.Read()) + { + Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar(); + user.AvatarID = new LLUUID((string) reader["UUID"]); + user.firstName = (string) reader["username"]; + user.lastName = (string) reader["lastname"]; + returnlist.Add(user); + } + reader.Close(); + result.Dispose(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return returnlist; + } + } + return returnlist; + } + + // see IUserData + override public UserProfileData GetUserByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?uuid"] = uuid.ToString(); + + IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserProfileData row = database.readUserRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Returns a user session searching by name + /// + /// The account name + /// The users session + override 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 + override public UserAgentData GetAgentByName(string user, string last) + { + UserProfileData profile = GetUserByName(user, last); + return GetAgentByUUID(profile.UUID); + } + + override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey) + { + Dictionary param = new Dictionary(); + param["?UUID"] = AgentID.UUID.ToString(); + param["?webLoginKey"] = WebLoginKey.UUID.ToString(); + + try + { + lock (database) + { + IDbCommand updater = + database.Query( + "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " + + "where UUID = ?UUID", + param); + updater.ExecuteNonQuery(); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return; + } + } + + /// + /// Returns an agent session by account UUID + /// + /// The accounts UUID + /// The users session + override public UserAgentData GetAgentByUUID(LLUUID uuid) + { + try + { + lock (database) + { + Dictionary param = new Dictionary(); + param["?uuid"] = uuid.ToString(); + + IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param); + IDataReader reader = result.ExecuteReader(); + + UserAgentData row = database.readAgentRow(reader); + + reader.Close(); + result.Dispose(); + + return row; + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + return null; + } + } + + /// + /// Creates a new users profile + /// + /// The user profile to create + override 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, user.webLoginKey); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + } + + /// + /// Creates a new agent + /// + /// The agent to create + override public void AddNewUserAgent(UserAgentData agent) + { + try + { + lock (database) + { + database.insertAgentRow(agent); + } + } + catch (Exception e) + { + database.Reconnect(); + m_log.Error(e.ToString()); + } + } + + /// + /// Updates a user profile stored in the DB + /// + /// The profile data to use to update the DB + override public bool UpdateUserProfile(UserProfileData user) + { + database.updateUserRow(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, user.webLoginKey); + return true; + } + + /// + /// Performs a money transfer request between two accounts + /// + /// The senders account ID + /// The receivers account ID + /// The amount to transfer + /// Success? + override 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 receivers account ID + /// The item to transfer + /// Success? + override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) + { + return false; + } + + /// + /// Database provider name + /// + /// Provider name + override public string getName() + { + return "MySQL Userdata Interface"; + } + + /// + /// Database provider version + /// + /// provider version + override public string GetVersion() + { + return "0.1"; + } + } +} diff --git a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs new file mode 100644 index 0000000..060e26c --- /dev/null +++ b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs @@ -0,0 +1,65 @@ +/* + * 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.Reflection; +using System.Runtime.InteropServices; + +// General Information about an assembly is controlled through the following +// set of attributes. Change these attribute values to modify the information +// associated with an assembly. + +[assembly : AssemblyTitle("OpenSim.Framework.Data.MySQL")] +[assembly : AssemblyDescription("")] +[assembly : AssemblyConfiguration("")] +[assembly : AssemblyCompany("")] +[assembly : AssemblyProduct("OpenSim.Framework.Data.MySQL")] +[assembly : AssemblyCopyright("Copyright (c) OpenSimulator.org Developers 2007-2008")] +[assembly : AssemblyTrademark("")] +[assembly : AssemblyCulture("")] + +// Setting ComVisible to false makes the types in this assembly not visible +// to COM components. If you need to access a type in this assembly from +// COM, set the ComVisible attribute to true on that type. + +[assembly : ComVisible(false)] + +// The following GUID is for the ID of the typelib if this project is exposed to COM + +[assembly : Guid("e49826b2-dcef-41be-a5bd-596733fa3304")] + +// Version information for an assembly consists of the following four values: +// +// Major Version +// Minor Version +// Build Number +// Revision +// +// You can specify all the values or you can default the Revision and Build Numbers +// by using the '*' as shown below: + +[assembly : AssemblyVersion("1.0.0.0")] +[assembly : AssemblyFileVersion("1.0.0.0")] diff --git a/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql new file mode 100644 index 0000000..b638ee2 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql @@ -0,0 +1,42 @@ +-- +-- Create schema avatar_appearance +-- + +CREATE DATABASE IF NOT EXISTS avatar_appearance; +USE avatar_appearance; + +DROP TABLE IF EXISTS `avatarappearance`; +CREATE TABLE `avatarappearance` ( + `UUID` char(36) NOT NULL, + `Serial` int(10) unsigned NOT NULL, + `WearableItem0` char(36) NOT NULL, + `WearableAsset0` char(36) NOT NULL, + `WearableItem1` char(36) NOT NULL, + `WearableAsset1` char(36) NOT NULL, + `WearableItem2` char(36) NOT NULL, + `WearableAsset2` char(36) NOT NULL, + `WearableItem3` char(36) NOT NULL, + `WearableAsset3` char(36) NOT NULL, + `WearableItem4` char(36) NOT NULL, + `WearableAsset4` char(36) NOT NULL, + `WearableItem5` char(36) NOT NULL, + `WearableAsset5` char(36) NOT NULL, + `WearableItem6` char(36) NOT NULL, + `WearableAsset6` char(36) NOT NULL, + `WearableItem7` char(36) NOT NULL, + `WearableAsset7` char(36) NOT NULL, + `WearableItem8` char(36) NOT NULL, + `WearableAsset8` char(36) NOT NULL, + `WearableItem9` char(36) NOT NULL, + `WearableAsset9` char(36) NOT NULL, + `WearableItem10` char(36) NOT NULL, + `WearableAsset10` char(36) NOT NULL, + `WearableItem11` char(36) NOT NULL, + `WearableAsset11` char(36) NOT NULL, + `WearableItem12` char(36) NOT NULL, + `WearableAsset12` char(36) NOT NULL, + + + PRIMARY KEY (`UUID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + diff --git a/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql new file mode 100644 index 0000000..3ef7bc9 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql @@ -0,0 +1,24 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for agents +-- ---------------------------- +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(11) NOT NULL, + `agentOnline` tinyint(4) NOT NULL, + `loginTime` int(11) NOT NULL, + `logoutTime` int(11) NOT NULL, + `currentRegion` varchar(36) NOT NULL, + `currentHandle` bigint(20) unsigned NOT NULL, + `currentPos` varchar(64) NOT NULL, + PRIMARY KEY (`UUID`), + UNIQUE KEY `session` (`sessionID`), + UNIQUE KEY `ssession` (`secureSessionID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; + +-- ---------------------------- +-- Records +-- ---------------------------- diff --git a/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql new file mode 100644 index 0000000..2c750fe --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql @@ -0,0 +1,11 @@ +CREATE TABLE `assets` ( + `id` binary(16) NOT NULL, + `name` varchar(64) NOT NULL, + `description` varchar(64) NOT NULL, + `assetType` tinyint(4) NOT NULL, + `invType` tinyint(4) NOT NULL, + `local` tinyint(1) NOT NULL, + `temporary` tinyint(1) NOT NULL, + `data` longblob NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql new file mode 100644 index 0000000..b5bddde --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql @@ -0,0 +1,11 @@ +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 (`folderID`), + KEY `owner` (`agentID`), + KEY `parent` (`parentFolderID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql new file mode 100644 index 0000000..1723ee3 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql @@ -0,0 +1,18 @@ +CREATE TABLE `inventoryitems` ( + `inventoryID` varchar(36) NOT NULL default '', + `assetID` varchar(36) default NULL, + `assetType` int(11) default NULL, + `parentFolderID` varchar(36) default NULL, + `avatarID` varchar(36) default NULL, + `inventoryName` varchar(64) default NULL, + `inventoryDescription` varchar(128) default NULL, + `inventoryNextPermissions` int(10) unsigned default NULL, + `inventoryCurrentPermissions` int(10) unsigned default NULL, + `invType` int(11) default NULL, + `creatorID` varchar(36) default NULL, + `inventoryBasePermissions` int(10) unsigned NOT NULL default 0, + `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0, + PRIMARY KEY (`inventoryID`), + KEY `owner` (`avatarID`), + KEY `folder` (`parentFolderID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql new file mode 100644 index 0000000..64b3a80 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql @@ -0,0 +1,10 @@ +CREATE TABLE `logs` ( + `logID` int(10) unsigned NOT NULL auto_increment, + `target` varchar(36) default NULL, + `server` varchar(64) default NULL, + `method` varchar(64) default NULL, + `arguments` varchar(255) default NULL, + `priority` int(11) default NULL, + `message` text, + PRIMARY KEY (`logID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; diff --git a/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql new file mode 100644 index 0000000..cb0f9bd --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql @@ -0,0 +1,32 @@ +CREATE TABLE `regions` ( + `uuid` varchar(36) NOT NULL, + `regionHandle` bigint(20) unsigned NOT NULL, + `regionName` varchar(32) default NULL, + `regionRecvKey` varchar(128) default NULL, + `regionSendKey` varchar(128) default NULL, + `regionSecret` varchar(128) default NULL, + `regionDataURI` varchar(255) default NULL, + `serverIP` varchar(64) default NULL, + `serverPort` int(10) unsigned default NULL, + `serverURI` varchar(255) default NULL, + `locX` int(10) unsigned default NULL, + `locY` int(10) unsigned default NULL, + `locZ` int(10) unsigned default NULL, + `eastOverrideHandle` bigint(20) unsigned default NULL, + `westOverrideHandle` bigint(20) unsigned default NULL, + `southOverrideHandle` bigint(20) unsigned default NULL, + `northOverrideHandle` bigint(20) unsigned default NULL, + `regionAssetURI` varchar(255) default NULL, + `regionAssetRecvKey` varchar(128) default NULL, + `regionAssetSendKey` varchar(128) default NULL, + `regionUserURI` varchar(255) default NULL, + `regionUserRecvKey` varchar(128) default NULL, + `regionUserSendKey` varchar(128) default NULL, `regionMapTexture` varchar(36) default NULL, + `serverHttpPort` int(10) default NULL, `serverRemotingPort` int(10) default NULL, + `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null, + `originUUID` varchar(36), + PRIMARY KEY (`uuid`), + KEY `regionName` (`regionName`), + KEY `regionHandle` (`regionHandle`), + KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3'; diff --git a/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql new file mode 100644 index 0000000..8480d48 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql @@ -0,0 +1,11 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for users +-- ---------------------------- +CREATE TABLE `userfriends` ( + `ownerID` VARCHAR(37) NOT NULL, + `friendID` VARCHAR(37) NOT NULL, + `friendPerms` INT NOT NULL, + `datetimestamp` INT NOT NULL, + UNIQUE KEY (`ownerID`, `friendID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev.1'; \ No newline at end of file diff --git a/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql new file mode 100644 index 0000000..d9e8ae2 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql @@ -0,0 +1,35 @@ +SET FOREIGN_KEY_CHECKS=0; +-- ---------------------------- +-- Table structure for users +-- ---------------------------- +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(20) unsigned 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(11) NOT NULL, + `lastLogin` int(11) NOT NULL, + `userInventoryURI` varchar(255) default NULL, + `userAssetURI` varchar(255) default NULL, + `profileCanDoMask` int(10) unsigned default NULL, + `profileWantDoMask` int(10) unsigned default NULL, + `profileAboutText` text, + `profileFirstText` text, + `profileImage` varchar(36) default NULL, + `profileFirstImage` varchar(36) default NULL, + `webLoginKey` varchar(36) default NULL, + PRIMARY KEY (`UUID`), + UNIQUE KEY `usernames` (`username`,`lastname`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2'; + +-- ---------------------------- +-- Records +-- ---------------------------- diff --git a/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql new file mode 100644 index 0000000..b5a7964 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql @@ -0,0 +1,4 @@ +ALTER TABLE `inventoryfolders` + ADD COLUMN `type` smallint NOT NULL default 0, + ADD COLUMN `version` int NOT NULL default 0, +COMMENT='Rev. 2'; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql new file mode 100644 index 0000000..d1ef504 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql @@ -0,0 +1,9 @@ +ALTER TABLE `inventoryitems` + CHANGE COLUMN `type` `assetType` int(11) default NULL, + ADD COLUMN `invType` int(11) default NULL, + ADD COLUMN `creatorID` varchar(36) default NULL, + ADD COLUMN `inventoryBasePermissions` int(10) unsigned NOT NULL default 0, + ADD COLUMN `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0, +COMMENT='Rev. 2'; + +UPDATE `inventoryitems` SET invType=assetType; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql new file mode 100644 index 0000000..034b755 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql @@ -0,0 +1,4 @@ +ALTER TABLE `regions` + ADD COLUMN `originUUID` varchar(36), +COMMENT='Rev. 2'; +UPDATE `regions` SET originUUID=uuid; diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql new file mode 100644 index 0000000..b48afec --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql @@ -0,0 +1,18 @@ +DROP PROCEDURE IF EXISTS upgraderegions3; + +create procedure upgraderegions3() +BEGIN +DECLARE db_name varchar(64); +select database() into db_name; +IF ((select count(*) from information_schema.columns where table_name='regions' and column_name='owner_uuid' and table_schema=db_name) > 0) +THEN + ALTER TABLE `regions`, COMMENT='Rev. 3'; +ELSE + ALTER TABLE `regions` + ADD COLUMN `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null after serverRemotingPort, COMMENT='Rev. 3'; +END IF; +END; + +call upgraderegions3(); + + diff --git a/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql new file mode 100644 index 0000000..dd21a66 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql @@ -0,0 +1,3 @@ +ALTER TABLE `users` + ADD COLUMN `webLoginKey` varchar(36) default '00000000-0000-0000-0000-000000000000' NOT NULL, +COMMENT='Rev. 2'; \ No newline at end of file -- cgit v1.1