From b097d89f38d24e9e5ceac6bf3d0d641d7559514d Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Thu, 9 Aug 2007 02:26:34 +0000 Subject: decrease insanity level significantly by factoring all the columns into a Dictionary, and programatically building up sql commands for our dataset. Prims done, prim shapes next --- .../MonoSqliteDataStore.cs | 148 +++++++-------------- 1 file changed, 46 insertions(+), 102 deletions(-) diff --git a/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs b/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs index 0a73880..60736aa 100644 --- a/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs +++ b/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs @@ -114,11 +114,50 @@ namespace OpenSim.DataStore.MonoSqliteStorage return data; } - private SqliteCommand createInsertCommand(Dictionary defs) + private SqliteCommand createPrimInsertCommand(Dictionary defs) { - SqliteCommand cmd = new SqliteCommand(); + /** + * 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[defs.Keys.Count]; + defs.Keys.CopyTo(cols, 0); + string sql = "insert into prims("; + 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 += ")"; + SqliteCommand cmd = new SqliteCommand(sql); + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (KeyValuePair kvp in defs) { + cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + } + return cmd; + } + + private SqliteCommand createPrimUpdateCommand(Dictionary defs) + { + string sql = "update prims set "; + foreach (string key in defs.Keys) { + sql += key + "= :" + key + ", "; + } + sql += " where UUID=:UUID"; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (KeyValuePair kvp in defs) { + cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + } return cmd; } @@ -126,111 +165,16 @@ namespace OpenSim.DataStore.MonoSqliteStorage { Dictionary primDataDefs = createPrimDataDefs(); - da.InsertCommand = createInsertCommand(primDataDefs); - /* - * Create all the bound parameters. Try to keep these in the same order - * as the sql file, with comments in the same places, or your head will probably - * explode trying to do corolations - */ - SqliteParameter UUID = createSqliteParameter("UUID", DbType.String); - SqliteParameter ParentID = createSqliteParameter("ParentID", DbType.Int32); - SqliteParameter CreationDate = createSqliteParameter("CreationDate", DbType.Int32); - SqliteParameter Name = createSqliteParameter("Name", DbType.String); - // various text fields - SqliteParameter Text = createSqliteParameter("Text", DbType.String); - SqliteParameter Description = createSqliteParameter("Description", DbType.String); - SqliteParameter SitName = createSqliteParameter("SitName", DbType.String); - SqliteParameter TouchName = createSqliteParameter("TouchName", DbType.String); - // permissions - SqliteParameter CreatorID = createSqliteParameter("CreatorID", DbType.String); - SqliteParameter OwnerID = createSqliteParameter("OwnerID", DbType.String); - SqliteParameter GroupID = createSqliteParameter("GroupID", DbType.String); - SqliteParameter LastOwnerID = createSqliteParameter("LastOwnerID", DbType.String); - SqliteParameter OwnerMask = createSqliteParameter("OwnerMask", DbType.Int32); - SqliteParameter NextOwnerMask = createSqliteParameter("NextOwnerMask", DbType.Int32); - SqliteParameter GroupMask = createSqliteParameter("GroupMask", DbType.Int32); - SqliteParameter EveryoneMask = createSqliteParameter("EveryoneMask", DbType.Int32); - SqliteParameter BaseMask = createSqliteParameter("BaseMask", DbType.Int32); - // vectors - SqliteParameter PositionX = createSqliteParameter("PositionX", DbType.Double); - SqliteParameter PositionY = createSqliteParameter("PositionY", DbType.Double); - SqliteParameter PositionZ = createSqliteParameter("PositionZ", DbType.Double); - SqliteParameter VelocityX = createSqliteParameter("VelocityX", DbType.Double); - SqliteParameter VelocityY = createSqliteParameter("VelocityY", DbType.Double); - SqliteParameter VelocityZ = createSqliteParameter("VelocityZ", DbType.Double); - SqliteParameter AngularVelocityX = createSqliteParameter("AngularVelocityX", DbType.Double); - SqliteParameter AngularVelocityY = createSqliteParameter("AngularVelocityY", DbType.Double); - SqliteParameter AngularVelocityZ = createSqliteParameter("AngularVelocityZ", DbType.Double); - SqliteParameter AccelerationX = createSqliteParameter("AccelerationX", DbType.Double); - SqliteParameter AccelerationY = createSqliteParameter("AccelerationY", DbType.Double); - SqliteParameter AccelerationZ = createSqliteParameter("AccelerationZ", DbType.Double); - // quaternions - SqliteParameter RotationX = createSqliteParameter("RotationX", DbType.Double); - SqliteParameter RotationY = createSqliteParameter("RotationY", DbType.Double); - SqliteParameter RotationZ = createSqliteParameter("RotationZ", DbType.Double); - SqliteParameter RotationW = createSqliteParameter("RotationW", DbType.Double); + da.InsertCommand = createPrimInsertCommand(primDataDefs); + da.InsertCommand.Connection = conn; + da.UpdateCommand = createPrimUpdateCommand(primDataDefs); + da.UpdateCommand.Connection = conn; SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); delete.Connection = conn; - - SqliteCommand insert = - new SqliteCommand("insert into prims(" + - "UUID, ParentID, CreationDate, Name, " + - "Text, Description, SitName, TouchName, " + - "CreatorID, OwnerID, GroupID, LastOwnerID, " + - "OwnerMask, NextOwnerMask, GroupMask, EveryoneMask, BaseMask, " + - "PositionX, PositionY, PositionZ, " + - "VelocityX, VelocityY, VelocityZ, " + - "AngularVelocityX, AngularVelocityY, AngularVelocityZ, " + - "AccelerationX, AccelerationY, AccelerationZ, " + - "RotationX, RotationY, RotationZ, RotationW" + - ") values (" + - ":UUID, :ParentID, :CreationDate, :Name, " + - ":Text, :Description, :SitName, :TouchName, " + - ":CreatorID, :OwnerID, :GroupID, :LastOwnerID, " + - ":OwnerMask, :NextOwnerMask, :GroupMask, :EveryoneMask, :BaseMask, " + - ":PositionX, :PositionY, :PositionZ, " + - ":VelocityX, :VelocityY, :VelocityZ, " + - ":AngularVelocityX, :AngularVelocityY, :AngularVelocityZ, " + - ":AccelerationX, :AccelerationY, :AccelerationZ, " + - ":RotationX, :RotationY, :RotationZ, :RotationW)"); - - insert.Connection = conn; - - SqliteCommand update = - new SqliteCommand("update prims set " + - "UUID = :UUID, ParentID = :ParentID, CreationDate = :CreationDate, Name = :Name, " + - "Text = :Text, Description = :Description, SitName = :SitName, TouchName = :TouchName, " + - "CreatorID = :CreatorID, OwnerID = :OwnerID, GroupID = :GroupID, LastOwnerID = :LastOwnerID, " + - "OwnerMask = :OwnerMask, NextOwnerMask = :NextOwnerMask, GroupMask = :GroupMask, = :EveryoneMask, = :BaseMask, " + - " = :PositionX, = :PositionY, = :PositionZ, " + - " = :VelocityX, = :VelocityY, = :VelocityZ, " + - " = :AngularVelocityX, = :AngularVelocityY, = :AngularVelocityZ, " + - " = :AccelerationX, = :AccelerationY, = :AccelerationZ, " + - " = :RotationX, = :RotationY, = :RotationZ, = :RotationW " + - "where UUID = :UUID"); - update.Connection = conn; - - delete.Parameters.Add(UUID); - - insert.Parameters.Add(UUID); - insert.Parameters.Add(Name); - insert.Parameters.Add(CreationDate); - insert.Parameters.Add(PositionX); - insert.Parameters.Add(PositionY); - insert.Parameters.Add(PositionZ); - - update.Parameters.Add(UUID); - update.Parameters.Add(Name); - update.Parameters.Add(CreationDate); - update.Parameters.Add(PositionX); - update.Parameters.Add(PositionY); - update.Parameters.Add(PositionZ); - da.DeleteCommand = delete; - da.InsertCommand = insert; - da.UpdateCommand = update; } private void StoreSceneObject(SceneObject obj) -- cgit v1.1