From 48be04df1d804137a736d83de157265555a63d31 Mon Sep 17 00:00:00 2001 From: Justin Clarke Casey Date: Fri, 11 Jan 2008 17:00:21 +0000 Subject: * Do database implementation for prim inventory items in mysql * Properly clean up items when a region object is deleted * Update persisted prim when an inventory script is changed * No user functionality yet --- OpenSim/Framework/Data.MySQL/MySQLDataStore.cs | 325 +++++++++++++++++++++++-- 1 file changed, 310 insertions(+), 15 deletions(-) (limited to 'OpenSim/Framework/Data.MySQL') diff --git a/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs b/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs index 3c25533..6d33a36 100644 --- a/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs +++ b/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs @@ -44,6 +44,7 @@ namespace OpenSim.Framework.Data.MySQL { 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"; @@ -51,15 +52,21 @@ namespace OpenSim.Framework.Data.MySQL 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; /*********************************************************************** * @@ -71,6 +78,7 @@ namespace OpenSim.Framework.Data.MySQL public void Initialise(string connectionstring, bool persistPrimInventories) { m_dataSet = new DataSet(); + this.persistPrimInventories = persistPrimInventories; MainLog.Instance.Verbose("DATASTORE", "MySql - connecting: " + connectionstring); m_connection = new MySqlConnection(connectionstring); @@ -80,6 +88,9 @@ namespace OpenSim.Framework.Data.MySQL 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); @@ -103,6 +114,14 @@ namespace OpenSim.Framework.Data.MySQL 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); @@ -146,6 +165,7 @@ namespace OpenSim.Framework.Data.MySQL { DataTable prims = m_primTable; DataTable shapes = m_shapeTable; + DataTable items = m_itemsTable; string selectExp = "SceneGroupID = '" + Util.ToRawUuidString(obj) + "'"; lock (m_dataSet) @@ -153,12 +173,27 @@ namespace OpenSim.Framework.Data.MySQL 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) + { + // Remove items rows + String sql = String.Format("primID = '{0}'", uuid); + DataRow[] itemRows = items.Select(sql); + + foreach (DataRow itemsRow in itemRows) + { + shapeRow.Delete(); + } + } + + // Remove prim row row.Delete(); } } @@ -166,6 +201,9 @@ namespace OpenSim.Framework.Data.MySQL Commit(); } + /// + /// Load persisted objects from region storage. + /// public List LoadObjects(LLUUID regionUUID) { Dictionary createdObjects = new Dictionary(); @@ -186,14 +224,17 @@ namespace OpenSim.Framework.Data.MySQL foreach (DataRow primRow in primsForRegion) { - try + 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(); - SceneObjectPart prim = buildPrim(primRow); + DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); if (shapeRow != null) { @@ -213,7 +254,6 @@ namespace OpenSim.Framework.Data.MySQL } else { - SceneObjectPart prim = buildPrim(primRow); DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID)); if (shapeRow != null) { @@ -227,6 +267,11 @@ namespace OpenSim.Framework.Data.MySQL } createdObjects[new LLUUID(objID)].AddPart(prim); } + + if (persistPrimInventories) + { + LoadItems(prim); + } } catch (Exception e) { @@ -241,7 +286,39 @@ namespace OpenSim.Framework.Data.MySQL } return retvals; } - + + /// + /// Load in a prim's persisted inventory. + /// + /// + private void LoadItems(SceneObjectPart prim) + { + MainLog.Instance.Verbose("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); + + MainLog.Instance.Verbose("DATASTORE", "Restored item {0}, {1}", item.name, item.item_id); + } + + prim.AddInventoryItems(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].parent_id; + } + } public void StoreTerrain(double[,] ter, LLUUID regionID) { @@ -437,6 +514,12 @@ namespace OpenSim.Framework.Data.MySQL 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); @@ -630,6 +713,38 @@ namespace OpenSim.Framework.Data.MySQL 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; + } /*********************************************************************** * @@ -727,6 +842,41 @@ namespace OpenSim.Framework.Data.MySQL } return prim; } + + + /// + /// Build a prim inventory item from the persisted data. + /// + /// + /// + private TaskInventoryItem buildItem(DataRow row) + { + TaskInventoryItem taskItem = new TaskInventoryItem(); + + taskItem.item_id = new LLUUID((String)row["itemID"]); + taskItem.ParentPartID = new LLUUID((String)row["primID"]); + taskItem.asset_id = new LLUUID((String)row["assetID"]); + taskItem.parent_id = new LLUUID((String)row["parentFolderID"]); + + taskItem.inv_type = Convert.ToInt32(row["invType"]); + taskItem.type = Convert.ToInt32(row["assetType"]); + + taskItem.name = (String)row["name"]; + taskItem.desc = (String)row["description"]; + taskItem.creation_date = Convert.ToUInt32(row["creationDate"]); + taskItem.creator_id = new LLUUID((String)row["creatorID"]); + taskItem.owner_id = new LLUUID((String)row["ownerID"]); + taskItem.last_owner_id = new LLUUID((String)row["lastOwnerID"]); + taskItem.group_id = new LLUUID((String)row["groupID"]); + + taskItem.next_owner_mask = Convert.ToUInt32(row["nextPermissions"]); + taskItem.owner_mask = Convert.ToUInt32(row["currentPermissions"]); + taskItem.base_mask = Convert.ToUInt32(row["basePermissions"]); + taskItem.everyone_mask = Convert.ToUInt32(row["everyonePermissions"]); + taskItem.group_mask = Convert.ToUInt32(row["groupPermissions"]); + + return taskItem; + } private LandData buildLandData(DataRow row) { @@ -870,6 +1020,30 @@ namespace OpenSim.Framework.Data.MySQL } } } + + private void fillItemRow(DataRow row, TaskInventoryItem taskItem) + { + row["itemID"] = taskItem.item_id; + row["primID"] = taskItem.ParentPartID; + row["assetID"] = taskItem.asset_id; + row["parentFolderID"] = taskItem.parent_id; + + row["invType"] = taskItem.inv_type; + row["assetType"] = taskItem.type; + + row["name"] = taskItem.name; + row["description"] = taskItem.desc; + row["creationDate"] = taskItem.creation_date; + row["creatorID"] = taskItem.creator_id; + row["ownerID"] = taskItem.owner_id; + row["lastOwnerID"] = taskItem.last_owner_id; + row["groupID"] = taskItem.group_id; + row["nextPermissions"] = taskItem.next_owner_mask; + row["currentPermissions"] = taskItem.owner_mask; + row["basePermissions"] = taskItem.base_mask; + row["everyonePermissions"] = taskItem.everyone_mask; + row["groupPermissions"] = taskItem.group_mask; + } private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID) { @@ -1017,7 +1191,79 @@ namespace OpenSim.Framework.Data.MySQL { fillShapeRow(shapeRow, prim); } + + if (persistPrimInventories) + { + addPrimInventory(prim.UUID, prim.TaskInventory); + } } + + /// + /// Persist prim inventory. Deletes, updates and inserts rows. + /// + /// + /// + /// + private void addPrimInventory(LLUUID primID, IDictionary items) + { + MainLog.Instance.Verbose("DATASTORE", "Entered addPrimInventory with prim ID {0}", primID); + + // Find all existing inventory rows for this prim + DataTable dbItems = m_itemsTable; + + String sql = String.Format("primID = '{0}'", primID); + DataRow[] dbItemRows = dbItems.Select(sql); + + // Build structures for manipulation purposes + IDictionary dbItemsToRemove = new Dictionary(); + ICollection itemsToAdd + = new List(); + + foreach (DataRow row in dbItemRows) + { + dbItemsToRemove.Add((String)row["itemID"], row); + } + + // Eliminate rows from the deletion set which already exist for this prim's inventory + // TODO Very temporary, need to take account of simple metadata changes soon + foreach (LLUUID itemId in items.Keys) + { + String rawItemId = itemId.ToString(); + + if (dbItemsToRemove.ContainsKey(rawItemId)) + { + dbItemsToRemove.Remove(rawItemId); + } + else + { + itemsToAdd.Add(items[itemId]); + } + } + + // Delete excess rows + foreach (DataRow row in dbItemsToRemove.Values) + { + MainLog.Instance.Verbose( + "DATASTORE", + "Removing item {0}, {1} from prim ID {2}", + row["name"], row["itemID"], row["primID"]); + + row.Delete(); + } + + // Insert items not already present + foreach (TaskInventoryItem newItem in itemsToAdd) + { + MainLog.Instance.Verbose( + "DATASTORE", + "Adding item {0}, {1} to prim ID {1}", + newItem.name, newItem.item_id, newItem.ParentPartID); + + DataRow newItemRow = dbItems.NewRow(); + fillItemRow(newItemRow, newItem); + dbItems.Rows.Add(newItemRow); + } + } /*********************************************************************** * @@ -1111,6 +1357,9 @@ namespace OpenSim.Framework.Data.MySQL } sql += subsql; sql += ")"; + + //MainLog.Instance.Verbose("DATASTORE", "defineTable() sql {0}", sql); + return sql; } @@ -1166,6 +1415,20 @@ namespace OpenSim.Framework.Data.MySQL 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) { @@ -1206,12 +1469,14 @@ namespace OpenSim.Framework.Data.MySQL { 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); @@ -1225,45 +1490,54 @@ namespace OpenSim.Framework.Data.MySQL { pcmd.ExecuteNonQuery(); } - catch (MySqlException) + catch (MySqlException e) { - MainLog.Instance.Warn("MySql", "Primitives Table Already Exists"); + MainLog.Instance.Warn("MySql", "Primitives Table Already Exists: {0}", e); } try { scmd.ExecuteNonQuery(); } - catch (MySqlException) + catch (MySqlException e) { - MainLog.Instance.Warn("MySql", "Shapes Table Already Exists"); + MainLog.Instance.Warn("MySql", "Shapes Table Already Exists: {0}", e); } + + try + { + icmd.ExecuteNonQuery(); + } + catch (MySqlException e) + { + MainLog.Instance.Warn("MySql", "Items Table Already Exists: {0}", e); + } try { tcmd.ExecuteNonQuery(); } - catch (MySqlException) + catch (MySqlException e) { - MainLog.Instance.Warn("MySql", "Terrain Table Already Exists"); + MainLog.Instance.Warn("MySql", "Terrain Table Already Exists: {0}", e); } try { lcmd.ExecuteNonQuery(); } - catch (MySqlException) + catch (MySqlException e) { - MainLog.Instance.Warn("MySql", "Land Table Already Exists"); + MainLog.Instance.Warn("MySql", "Land Table Already Exists: {0}", e); } try { lalcmd.ExecuteNonQuery(); } - catch (MySqlException) + catch (MySqlException e) { - MainLog.Instance.Warn("MySql", "LandAccessList Table Already Exists"); + MainLog.Instance.Warn("MySql", "LandAccessList Table Already Exists: {0}", e); } conn.Close(); } @@ -1274,6 +1548,8 @@ namespace OpenSim.Framework.Data.MySQL 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); @@ -1286,6 +1562,10 @@ namespace OpenSim.Framework.Data.MySQL { 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"); @@ -1298,6 +1578,10 @@ namespace OpenSim.Framework.Data.MySQL 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"); @@ -1310,6 +1594,7 @@ namespace OpenSim.Framework.Data.MySQL return false; } } + foreach (DataColumn col in createShapeTable().Columns) { if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName)) @@ -1318,6 +1603,9 @@ namespace OpenSim.Framework.Data.MySQL return false; } } + + // XXX primitems should probably go here eventually + foreach (DataColumn col in createTerrainTable().Columns) { if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName)) @@ -1326,6 +1614,7 @@ namespace OpenSim.Framework.Data.MySQL return false; } } + foreach (DataColumn col in createLandTable().Columns) { if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName)) @@ -1334,6 +1623,7 @@ namespace OpenSim.Framework.Data.MySQL return false; } } + foreach (DataColumn col in createLandAccessListTable().Columns) { if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName)) @@ -1342,6 +1632,7 @@ namespace OpenSim.Framework.Data.MySQL return false; } } + return true; } @@ -1395,6 +1686,10 @@ namespace OpenSim.Framework.Data.MySQL { return "integer"; } + else if (type == typeof (Int64)) + { + return "bigint"; + } else if (type == typeof (Double)) { return "float"; @@ -1409,4 +1704,4 @@ namespace OpenSim.Framework.Data.MySQL } } } -} \ No newline at end of file +} -- cgit v1.1