From 01cf9d8b7ef3713cfb62c4c8b7a2087406d74e7f Mon Sep 17 00:00:00 2001 From: Teravus Ovares Date: Tue, 19 Feb 2008 00:08:28 +0000 Subject: * Committing Ahzz's patch number 620. * Description SQLITE Inventory Table locks. *Provided by Openlfiegrid.com * Provides missing inventory table locks in SQLite. --- .../Framework/Data.SQLite/SQLiteInventoryStore.cs | 312 ++++++++++++--------- 1 file changed, 177 insertions(+), 135 deletions(-) (limited to 'OpenSim/Framework/Data.SQLite') diff --git a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs index 5e5d1e4..23b4cb4 100644 --- a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs +++ b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs @@ -48,6 +48,11 @@ namespace OpenSim.Framework.Data.SQLite private SqliteDataAdapter invFoldersDa; /// + /// used to manage concurrent access tothe sqlite database files. Only one thread may open, read, write at a time. + /// + private object InventoryLock = new object(); + + /// /// Initialises the interface /// public void Initialise() @@ -126,58 +131,67 @@ namespace OpenSim.Framework.Data.SQLite private void addFolder(InventoryFolderBase folder) { - DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; - - DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); - if (inventoryRow == null) - { - inventoryRow = inventoryFolderTable.NewRow(); - fillFolderRow(inventoryRow, folder); - inventoryFolderTable.Rows.Add(inventoryRow); - } - else + lock (InventoryLock) { - fillFolderRow(inventoryRow, folder); - } + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + + DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); + if (inventoryRow == null) + { + inventoryRow = inventoryFolderTable.NewRow(); + fillFolderRow(inventoryRow, folder); + inventoryFolderTable.Rows.Add(inventoryRow); + } + else + { + fillFolderRow(inventoryRow, folder); + } - invFoldersDa.Update(ds, "inventoryfolders"); + invFoldersDa.Update(ds, "inventoryfolders"); + } } private void moveFolder(InventoryFolderBase folder) { - DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; - - DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); - if (inventoryRow == null) + lock (InventoryLock) { - inventoryRow = inventoryFolderTable.NewRow(); - fillFolderRow(inventoryRow, folder); - inventoryFolderTable.Rows.Add(inventoryRow); - } - else - { - moveFolderRow(inventoryRow, folder); - } + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + + DataRow inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folder.folderID)); + if (inventoryRow == null) + { + inventoryRow = inventoryFolderTable.NewRow(); + fillFolderRow(inventoryRow, folder); + inventoryFolderTable.Rows.Add(inventoryRow); + } + else + { + moveFolderRow(inventoryRow, folder); + } - invFoldersDa.Update(ds, "inventoryfolders"); + invFoldersDa.Update(ds, "inventoryfolders"); + } } private void addItem(InventoryItemBase item) { - DataTable inventoryItemTable = ds.Tables["inventoryitems"]; - - DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(item.inventoryID)); - if (inventoryRow == null) - { - inventoryRow = inventoryItemTable.NewRow(); - fillItemRow(inventoryRow, item); - inventoryItemTable.Rows.Add(inventoryRow); - } - else + lock (InventoryLock) { - fillItemRow(inventoryRow, item); + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; + + DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(item.inventoryID)); + if (inventoryRow == null) + { + inventoryRow = inventoryItemTable.NewRow(); + fillItemRow(inventoryRow, item); + inventoryItemTable.Rows.Add(inventoryRow); + } + else + { + fillItemRow(inventoryRow, item); + } + invItemsDa.Update(ds, "inventoryitems"); } - invItemsDa.Update(ds, "inventoryitems"); } public void Shutdown() @@ -224,16 +238,19 @@ namespace OpenSim.Framework.Data.SQLite /// A List of InventoryItemBase items public List getInventoryInFolder(LLUUID folderID) { - List retval = new List(); - DataTable inventoryItemTable = ds.Tables["inventoryitems"]; - string selectExp = "parentFolderID = '" + Util.ToRawUuidString(folderID) + "'"; - DataRow[] rows = inventoryItemTable.Select(selectExp); - foreach (DataRow row in rows) + lock (InventoryLock) { - retval.Add(buildItem(row)); - } + List retval = new List(); + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; + string selectExp = "parentFolderID = '" + Util.ToRawUuidString(folderID) + "'"; + DataRow[] rows = inventoryItemTable.Select(selectExp); + foreach (DataRow row in rows) + { + retval.Add(buildItem(row)); + } - return retval; + return retval; + } } /// @@ -249,27 +266,30 @@ namespace OpenSim.Framework.Data.SQLite // see InventoryItemBase.getUserRootFolder public InventoryFolderBase getUserRootFolder(LLUUID user) { - List folders = new List(); - DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; - string selectExp = "agentID = '" + Util.ToRawUuidString(user) + "' AND parentID = '" + - Util.ToRawUuidString(LLUUID.Zero) + "'"; - DataRow[] rows = inventoryFolderTable.Select(selectExp); - foreach (DataRow row in rows) + lock (InventoryLock) { - folders.Add(buildFolder(row)); - } + List folders = new List(); + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "agentID = '" + Util.ToRawUuidString(user) + "' AND parentID = '" + + Util.ToRawUuidString(LLUUID.Zero) + "'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(buildFolder(row)); + } - // 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 (folders.Count > 0) - { - return folders[0]; - } + // 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 (folders.Count > 0) + { + return folders[0]; + } - return null; + return null; + } } /// @@ -279,12 +299,15 @@ namespace OpenSim.Framework.Data.SQLite /// ID of parent protected void getInventoryFolders(ref List folders, LLUUID parentID) { - DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; - string selectExp = "parentID = '" + Util.ToRawUuidString(parentID) + "'"; - DataRow[] rows = inventoryFolderTable.Select(selectExp); - foreach (DataRow row in rows) + lock (InventoryLock) { - folders.Add(buildFolder(row)); + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "parentID = '" + Util.ToRawUuidString(parentID) + "'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(buildFolder(row)); + } } } @@ -323,14 +346,17 @@ namespace OpenSim.Framework.Data.SQLite /// A class containing item information public InventoryItemBase getInventoryItem(LLUUID item) { - DataRow row = ds.Tables["inventoryitems"].Rows.Find(Util.ToRawUuidString(item)); - if (row != null) - { - return buildItem(row); - } - else + lock (InventoryLock) { - return null; + DataRow row = ds.Tables["inventoryitems"].Rows.Find(Util.ToRawUuidString(item)); + if (row != null) + { + return buildItem(row); + } + else + { + return null; + } } } @@ -346,15 +372,17 @@ namespace OpenSim.Framework.Data.SQLite // better to leave multi region at this point. It does mean // that you don't get to see system textures why creating // clothes and the like. :( - - DataRow row = ds.Tables["inventoryfolders"].Rows.Find(Util.ToRawUuidString(folder)); - if (row != null) - { - return buildFolder(row); - } - else + lock (InventoryLock) { - return null; + DataRow row = ds.Tables["inventoryfolders"].Rows.Find(Util.ToRawUuidString(folder)); + if (row != null) + { + return buildFolder(row); + } + else + { + return null; + } } } @@ -382,17 +410,19 @@ namespace OpenSim.Framework.Data.SQLite /// public void deleteInventoryItem(LLUUID itemID) { - DataTable inventoryItemTable = ds.Tables["inventoryitems"]; - - DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(itemID)); - if (inventoryRow != null) + lock (InventoryLock) { - inventoryRow.Delete(); - } + DataTable inventoryItemTable = ds.Tables["inventoryitems"]; - invItemsDa.Update(ds, "inventoryitems"); - } + DataRow inventoryRow = inventoryItemTable.Rows.Find(Util.ToRawUuidString(itemID)); + if (inventoryRow != null) + { + inventoryRow.Delete(); + } + invItemsDa.Update(ds, "inventoryitems"); + } + } /// /// Delete all items in the specified folder @@ -443,31 +473,34 @@ namespace OpenSim.Framework.Data.SQLite /// public void deleteInventoryFolder(LLUUID folderID) { - List subFolders = getFolderHierarchy(Util.ToRawUuidString(folderID)); + lock (InventoryLock) + { + List subFolders = getFolderHierarchy(Util.ToRawUuidString(folderID)); - DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; - DataRow inventoryRow; + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + DataRow inventoryRow; - //Delete all sub-folders - foreach (InventoryFolderBase f in subFolders) - { - inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(f.folderID)); + //Delete all sub-folders + foreach (InventoryFolderBase f in subFolders) + { + inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(f.folderID)); + if (inventoryRow != null) + { + deleteItemsInFolder(Util.ToRawUuidString(f.folderID)); + inventoryRow.Delete(); + } + } + + //Delete the actual row + inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folderID)); if (inventoryRow != null) { - deleteItemsInFolder(Util.ToRawUuidString(f.folderID)); + deleteItemsInFolder(Util.ToRawUuidString(folderID)); inventoryRow.Delete(); } - } - //Delete the actual row - inventoryRow = inventoryFolderTable.Rows.Find(Util.ToRawUuidString(folderID)); - if (inventoryRow != null) - { - deleteItemsInFolder(Util.ToRawUuidString(folderID)); - inventoryRow.Delete(); + invFoldersDa.Update(ds, "inventoryfolders"); } - - invFoldersDa.Update(ds, "inventoryfolders"); } /*********************************************************************** @@ -517,30 +550,36 @@ namespace OpenSim.Framework.Data.SQLite private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn) { - da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]); - da.InsertCommand.Connection = conn; + lock (InventoryLock) + { + da.InsertCommand = createInsertCommand("inventoryitems", ds.Tables["inventoryitems"]); + da.InsertCommand.Connection = conn; - da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]); - da.UpdateCommand.Connection = conn; + da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", ds.Tables["inventoryitems"]); + da.UpdateCommand.Connection = conn; - SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID"); - delete.Parameters.Add(createSqliteParameter("UUID", typeof (String))); - delete.Connection = conn; - da.DeleteCommand = delete; + SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } } private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn) { - da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]); - da.InsertCommand.Connection = conn; + lock (InventoryLock) + { + da.InsertCommand = createInsertCommand("inventoryfolders", ds.Tables["inventoryfolders"]); + da.InsertCommand.Connection = conn; - da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]); - da.UpdateCommand.Connection = conn; + da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", ds.Tables["inventoryfolders"]); + da.UpdateCommand.Connection = conn; - SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID"); - delete.Parameters.Add(createSqliteParameter("UUID", typeof (String))); - delete.Connection = conn; - da.DeleteCommand = delete; + SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(String))); + delete.Connection = conn; + da.DeleteCommand = delete; + } } private InventoryFolderBase buildFolder(DataRow row) @@ -579,15 +618,18 @@ namespace OpenSim.Framework.Data.SQLite private void InitDB(SqliteConnection conn) { - string createInventoryItems = defineTable(createInventoryItemsTable()); - string createInventoryFolders = defineTable(createInventoryFoldersTable()); - - SqliteCommand pcmd = new SqliteCommand(createInventoryItems, conn); - SqliteCommand scmd = new SqliteCommand(createInventoryFolders, conn); - conn.Open(); - pcmd.ExecuteNonQuery(); - scmd.ExecuteNonQuery(); - conn.Close(); + lock (InventoryLock) + { + string createInventoryItems = defineTable(createInventoryItemsTable()); + string createInventoryFolders = defineTable(createInventoryFoldersTable()); + + SqliteCommand pcmd = new SqliteCommand(createInventoryItems, conn); + SqliteCommand scmd = new SqliteCommand(createInventoryFolders, conn); + conn.Open(); + pcmd.ExecuteNonQuery(); + scmd.ExecuteNonQuery(); + conn.Close(); + } } private bool TestTables(SqliteConnection conn) @@ -631,4 +673,4 @@ namespace OpenSim.Framework.Data.SQLite return true; } } -} \ No newline at end of file +} -- cgit v1.1