From fa6da2f6c666e786513e4c822a37d6755c9ff716 Mon Sep 17 00:00:00 2001 From: Teravus Ovares (Dan Olivares) Date: Sat, 13 Feb 2010 05:09:15 -0500 Subject: * This is an attempt to resolve mantis 4437 by using SqliteAdapter type statements instead of blanket SQL statements. The hope is that this makes SQLite work on Linux/Mono again. Re: http://opensimulator.org/mantis/view.php?id=4437 * Added a 'Create Update Statement' method that takes two fields for a primary key * Added an Update and Delete command for parcels and land access list table rows. --- OpenSim/Data/SQLite/SQLiteRegionData.cs | 111 ++++++++++++++++++++++++++++---- 1 file changed, 100 insertions(+), 11 deletions(-) (limited to 'OpenSim/Data/SQLite/SQLiteRegionData.cs') diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs index b68de1a..1285064 100644 --- a/OpenSim/Data/SQLite/SQLiteRegionData.cs +++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs @@ -617,18 +617,42 @@ namespace OpenSim.Data.SQLite { lock (ds) { - using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn)) + // Can't use blanket SQL statements when using SqlAdapters unless you re-read the data into the adapter + // after you're done. + // replaced below code with the SqliteAdapter version. + //using (SqliteCommand cmd = new SqliteCommand("delete from land where UUID=:UUID", m_conn)) + //{ + // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString())); + // cmd.ExecuteNonQuery(); + //} + + //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn)) + //{ + // cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString())); + // cmd.ExecuteNonQuery(); + //} + + DataTable land = ds.Tables["land"]; + DataTable landaccesslist = ds.Tables["landaccesslist"]; + DataRow landRow = land.Rows.Find(globalID.ToString()); + if (landRow != null) { - cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString())); - cmd.ExecuteNonQuery(); + land.Rows.Remove(landRow); } - - using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:UUID", m_conn)) + List rowsToDelete = new List(); + foreach (DataRow rowToCheck in landaccesslist.Rows) { - cmd.Parameters.Add(new SqliteParameter(":UUID", globalID.ToString())); - cmd.ExecuteNonQuery(); + if (rowToCheck["LandUUID"].ToString() == globalID.ToString()) + rowsToDelete.Add(rowToCheck); + } + for (int iter = 0; iter < rowsToDelete.Count; iter++) + { + landaccesslist.Rows.Remove(rowsToDelete[iter]); } + + } + Commit(); } /// @@ -655,12 +679,27 @@ namespace OpenSim.Data.SQLite } // I know this caused someone issues before, but OpenSim is unusable if we leave this stuff around - using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn)) + //using (SqliteCommand cmd = new SqliteCommand("delete from landaccesslist where LandUUID=:LandUUID", m_conn)) + //{ + // cmd.Parameters.Add(new SqliteParameter(":LandUUID", parcel.LandData.GlobalID.ToString())); + // cmd.ExecuteNonQuery(); + +// } + + // This is the slower.. but more appropriate thing to do + + // We can't modify the table with direct queries before calling Commit() and re-filling them. + List rowsToDelete = new List(); + foreach (DataRow rowToCheck in landaccesslist.Rows) { - cmd.Parameters.Add(new SqliteParameter(":LandUUID", parcel.LandData.GlobalID.ToString())); - cmd.ExecuteNonQuery(); + if (rowToCheck["LandUUID"].ToString() == parcel.LandData.GlobalID.ToString()) + rowsToDelete.Add(rowToCheck); } - + for (int iter = 0; iter < rowsToDelete.Count; iter++) + { + landaccesslist.Rows.Remove(rowsToDelete[iter]); + } + rowsToDelete.Clear(); foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList) { DataRow newAccessRow = landaccesslist.NewRow(); @@ -1813,6 +1852,7 @@ namespace OpenSim.Data.SQLite /// private void addPrim(SceneObjectPart prim, UUID sceneGroupID, UUID regionUUID) { + DataTable prims = ds.Tables["prims"]; DataTable shapes = ds.Tables["primshapes"]; @@ -1962,6 +2002,40 @@ namespace OpenSim.Data.SQLite } /// + /// create an update command + /// + /// table name + /// + /// + /// the created command + private static SqliteCommand createUpdateCommand(string table, string pk1, string pk2, 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 " + pk1 + " and " + pk2; + SqliteCommand cmd = new SqliteCommand(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(createSqliteParameter(col.ColumnName, col.DataType)); + } + return cmd; + } + + /// /// /// /// Data Table @@ -2079,6 +2153,11 @@ namespace OpenSim.Data.SQLite da.UpdateCommand = createUpdateCommand("land", "UUID=:UUID", ds.Tables["land"]); da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from land where UUID=:UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(String))); + da.DeleteCommand = delete; + da.DeleteCommand.Connection = conn; } /// @@ -2090,6 +2169,16 @@ namespace OpenSim.Data.SQLite { da.InsertCommand = createInsertCommand("landaccesslist", ds.Tables["landaccesslist"]); da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("landaccesslist", "LandUUID=:landUUID", "AccessUUID=:AccessUUID", ds.Tables["landaccesslist"]); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from landaccesslist where LandUUID= :LandUUID and AccessUUID= :AccessUUID"); + delete.Parameters.Add(createSqliteParameter("LandUUID", typeof(String))); + delete.Parameters.Add(createSqliteParameter("AccessUUID", typeof(String))); + da.DeleteCommand = delete; + da.DeleteCommand.Connection = conn; + } private void setupRegionSettingsCommands(SqliteDataAdapter da, SqliteConnection conn) -- cgit v1.1 From 45493171b01c1a623edc70fcb92c48e307c7f89f Mon Sep 17 00:00:00 2001 From: Teravus Ovares (Dan Olivares) Date: Sat, 13 Feb 2010 05:50:57 -0500 Subject: * SQLite match code casing with regionsettings table field casing (what's with the upper case S in Sandbox while the rest is lower case 0.o) * It's doubtful that this will have any effect on mantis http://opensimulator.org/mantis/view.php?id=4577 . Sqlite 3 is required, Sqlite 2 was reported. --- OpenSim/Data/SQLite/SQLiteRegionData.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'OpenSim/Data/SQLite/SQLiteRegionData.cs') diff --git a/OpenSim/Data/SQLite/SQLiteRegionData.cs b/OpenSim/Data/SQLite/SQLiteRegionData.cs index 1285064..5a4ee2a 100644 --- a/OpenSim/Data/SQLite/SQLiteRegionData.cs +++ b/OpenSim/Data/SQLite/SQLiteRegionData.cs @@ -1750,7 +1750,7 @@ namespace OpenSim.Data.SQLite row["terrain_raise_limit"] = settings.TerrainRaiseLimit; row["terrain_lower_limit"] = settings.TerrainLowerLimit; row["use_estate_sun"] = settings.UseEstateSun; - row["sandbox"] = settings.Sandbox; + row["Sandbox"] = settings.Sandbox; // database uses upper case S for sandbox row["sunvectorx"] = settings.SunVector.X; row["sunvectory"] = settings.SunVector.Y; row["sunvectorz"] = settings.SunVector.Z; -- cgit v1.1