From 1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 22 Aug 2007 21:09:38 +0000 Subject: Bit of refactoring of the sqlite storage code to build the data definition in ado.net objects up front. This makes auto generating the sql commands work a lot more reliably. --- .../MonoSqliteDataStore.cs | 237 ++++++++++++++++----- 1 file changed, 187 insertions(+), 50 deletions(-) (limited to 'OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite') diff --git a/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs b/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs index aa90eac..2d52a2a 100644 --- a/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs +++ b/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs @@ -52,17 +52,16 @@ namespace OpenSim.DataStore.MonoSqliteStorage // We fill the data set, now we've got copies in memory for the information // TODO: see if the linkage actually holds. // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema"); - try { - primDa.Fill(ds, "prims"); - } catch (Mono.Data.SqliteClient.SqliteSyntaxException) { - InitDB(conn); - primDa.Fill(ds, "prims"); - } - + TestPrimsTable(conn); + + ds.Tables.Add(createPrimTable()); + DataTable prims = ds.Tables["prims"]; + primDa.Fill(prims); + MainLog.Instance.Verbose(ds.GetXmlSchema()); + shapeDa.Fill(ds, "primshapes"); ds.AcceptChanges(); - DataTable prims = ds.Tables["prims"]; prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; setupPrimCommands(primDa, conn); @@ -85,18 +84,32 @@ namespace OpenSim.DataStore.MonoSqliteStorage /// for us. /// ///a built sqlite parameter - private SqliteParameter createSqliteParameter(string name, DbType type) + private SqliteParameter createSqliteParameter(string name, System.Type type) { SqliteParameter param = new SqliteParameter(); param.ParameterName = ":" + name; - param.DbType = type; + param.DbType = dbtypeFromType(type); param.SourceColumn = name; param.SourceVersion = DataRowVersion.Current; return param; } + private DbType dbtypeFromType(Type type) + { + if (type == typeof(System.String)) { + return DbType.String; + } else if (type == typeof(System.Int32)) { + return DbType.Int32; + } else if (type == typeof(System.Double)) { + return DbType.Double; + } else if (type == typeof(System.Byte[])) { + return DbType.Binary; + } else { + return DbType.String; + } + } - private SqliteCommand createInsertCommand(string table, Dictionary defs) + private SqliteCommand createInsertCommand(string table, DataTable dt) { /** * This is subtle enough to deserve some commentary. @@ -107,8 +120,11 @@ namespace OpenSim.DataStore.MonoSqliteStorage * 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[] 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); @@ -120,24 +136,28 @@ namespace OpenSim.DataStore.MonoSqliteStorage // this provides the binding for all our parameters, so // much less code than it used to be - foreach (KeyValuePair kvp in defs) + foreach (DataColumn col in dt.Columns) { - cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); } + // foreach (KeyValuePair kvp in defs) + // { + // cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + // } return cmd; } - private SqliteCommand createUpdateCommand(string table, string pk, Dictionary defs) + private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt) { string sql = "update " + table + " set "; string subsql = ""; - foreach (string key in defs.Keys) + foreach (DataColumn col in dt.Columns) { if (subsql.Length > 0) { // a map function would rock so much here subsql += ", "; } - subsql += key + "= :" + key; + subsql += col.ColumnName + "= :" + col.ColumnName; } sql += subsql; sql += " where " + pk; @@ -145,41 +165,38 @@ namespace OpenSim.DataStore.MonoSqliteStorage // this provides the binding for all our parameters, so // much less code than it used to be - foreach (KeyValuePair kvp in defs) + + foreach (DataColumn col in dt.Columns) { - cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); } return cmd; } private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn) { - Dictionary primDataDefs = createPrimDataDefs(); - - da.InsertCommand = createInsertCommand("prims", primDataDefs); + da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]); da.InsertCommand.Connection = conn; - da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", primDataDefs); + da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]); da.UpdateCommand.Connection = conn; SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID"); - delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String))); delete.Connection = conn; da.DeleteCommand = delete; } private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn) { - Dictionary shapeDataDefs = createShapeDataDefs(); - - da.InsertCommand = createInsertCommand("primshapes", shapeDataDefs); + da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]); da.InsertCommand.Connection = conn; - da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", shapeDataDefs); + da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]); da.UpdateCommand.Connection = conn; SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID"); - delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); + delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String))); delete.Connection = conn; da.DeleteCommand = delete; } @@ -578,8 +595,8 @@ namespace OpenSim.DataStore.MonoSqliteStorage private void InitDB(SqliteConnection conn) { - string createPrims = defineTable("prims", "UUID", createPrimDataDefs()); - string createShapes = defineTable("primshapes", "UUID", createShapeDataDefs()); + string createPrims = defineTable(createPrimTable()); + string createShapes = defineTable(createShapeTable()); SqliteCommand pcmd = new SqliteCommand(createPrims, conn); SqliteCommand scmd = new SqliteCommand(createShapes, conn); @@ -589,18 +606,18 @@ namespace OpenSim.DataStore.MonoSqliteStorage conn.Close(); } - private string defineTable(string name, string primkey, Dictionary cols) + private string defineTable(DataTable dt) { - string sql = "create table " + name + "("; + string sql = "create table " + dt.TableName + "("; string subsql = ""; - foreach (string key in cols.Keys) + foreach (DataColumn col in dt.Columns) { if (subsql.Length > 0) { // a map function would rock so much here subsql += ",\n"; } - subsql += key + " " + sqliteType(cols[key]); - if(key == primkey) + subsql += col.ColumnName + " " + sqliteType(col.DataType); + if(col == dt.PrimaryKey[0]) { subsql += " primary key"; } @@ -610,29 +627,108 @@ namespace OpenSim.DataStore.MonoSqliteStorage return sql; } - private string sqliteType(DbType type) + private string sqliteType(Type type) { - switch(type) { - case DbType.String: + if (type == typeof(System.String)) { return "varchar(255)"; - - case DbType.Int32: + } else if (type == typeof(System.Int32)) { return "integer"; - - case DbType.Double: + } else if (type == typeof(System.Double)) { return "float"; - - case DbType.Binary: + } else if (type == typeof(System.Byte[])) { return "blob"; - - default: - return "varchar(255)"; + } else { + return "string"; } } + private bool TestPrimsTable(SqliteConnection conn) + { + SqliteCommand primSelectCmd = new SqliteCommand(primSelect, conn); + SqliteDataAdapter da = new SqliteDataAdapter(primSelectCmd); + DataSet tmp = new DataSet(); + try { + da.Fill(tmp, "prims"); + } catch (Mono.Data.SqliteClient.SqliteSyntaxException) { + MainLog.Instance.Verbose("SQLite Database does exist... creating"); + InitDB(conn); + } + + // Dictionary defs = createPrimDataDefs(); + // // da.FillSchema(ds, SchemaType.Mapped, "prims"); + da.Fill(tmp, "prims"); + MainLog.Instance.Verbose("DATASTORE", "Filled prims..."); + // DataTable prims = ds.Tables["prims"]; + // foreach (DataColumn col in prims.Columns) + // { + // MainLog.Instance.Verbose("Found: " + col); + // } + // return true; + return true; + } + /// Methods after this point are big data definition - /// methods, and aren't really interesting unless you are + /// methods, and aren't really interesting unless you are /// adjusting the schema. + + private void createCol(DataTable dt, string name, System.Type type) + { + DataColumn col = new DataColumn(name, type); + dt.Columns.Add(col); + } + + private DataTable createPrimTable() + { + DataTable prims = new DataTable("prims"); + + createCol(prims, "UUID", typeof(System.String)); + createCol(prims, "ParentID", typeof(System.Int32)); + createCol(prims, "CreationDate", typeof(System.Int32)); + createCol(prims, "Name", typeof(System.String)); + createCol(prims, "SceneGroupID", typeof(System.String)); + // various text fields + createCol(prims, "Text", typeof(System.String)); + createCol(prims, "Description", typeof(System.String)); + createCol(prims, "SitName", typeof(System.String)); + createCol(prims, "TouchName", typeof(System.String)); + // permissions + createCol(prims, "CreatorID", typeof(System.String)); + createCol(prims, "OwnerID", typeof(System.String)); + createCol(prims, "GroupID", typeof(System.String)); + createCol(prims, "LastOwnerID", typeof(System.String)); + createCol(prims, "OwnerMask", typeof(System.Int32)); + createCol(prims, "NextOwnerMask", typeof(System.Int32)); + createCol(prims, "GroupMask", typeof(System.Int32)); + createCol(prims, "EveryoneMask", typeof(System.Int32)); + createCol(prims, "BaseMask", typeof(System.Int32)); + // vectors + createCol(prims, "PositionX", typeof(System.Double)); + createCol(prims, "PositionY", typeof(System.Double)); + createCol(prims, "PositionZ", typeof(System.Double)); + createCol(prims, "GroupPositionX", typeof(System.Double)); + createCol(prims, "GroupPositionY", typeof(System.Double)); + createCol(prims, "GroupPositionZ", typeof(System.Double)); + createCol(prims, "VelocityX", typeof(System.Double)); + createCol(prims, "VelocityY", typeof(System.Double)); + createCol(prims, "VelocityZ", typeof(System.Double)); + createCol(prims, "AngularVelocityX", typeof(System.Double)); + createCol(prims, "AngularVelocityY", typeof(System.Double)); + createCol(prims, "AngularVelocityZ", typeof(System.Double)); + createCol(prims, "AccelerationX", typeof(System.Double)); + createCol(prims, "AccelerationY", typeof(System.Double)); + createCol(prims, "AccelerationZ", typeof(System.Double)); + // quaternions + createCol(prims, "RotationX", typeof(System.Double)); + createCol(prims, "RotationY", typeof(System.Double)); + createCol(prims, "RotationZ", typeof(System.Double)); + createCol(prims, "RotationW", typeof(System.Double)); + + // Add in contraints + prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; + + return prims; + } + private Dictionary createPrimDataDefs() { Dictionary data = new Dictionary(); @@ -680,6 +776,47 @@ namespace OpenSim.DataStore.MonoSqliteStorage return data; } + + private DataTable createShapeTable() + { + DataTable shapes = new DataTable("primshapes"); + createCol(shapes, "UUID", typeof(System.String)); + // shape is an enum + createCol(shapes, "Shape", typeof(System.Int32)); + // vectors + createCol(shapes, "ScaleX", typeof(System.Double)); + createCol(shapes, "ScaleY", typeof(System.Double)); + createCol(shapes, "ScaleZ", typeof(System.Double)); + // paths + createCol(shapes, "PCode", typeof(System.Int32)); + createCol(shapes, "PathBegin", typeof(System.Int32)); + createCol(shapes, "PathEnd", typeof(System.Int32)); + createCol(shapes, "PathScaleX", typeof(System.Int32)); + createCol(shapes, "PathScaleY", typeof(System.Int32)); + createCol(shapes, "PathShearX", typeof(System.Int32)); + createCol(shapes, "PathShearY", typeof(System.Int32)); + createCol(shapes, "PathSkew", typeof(System.Int32)); + createCol(shapes, "PathCurve", typeof(System.Int32)); + createCol(shapes, "PathRadiusOffset", typeof(System.Int32)); + createCol(shapes, "PathRevolutions", typeof(System.Int32)); + createCol(shapes, "PathTaperX", typeof(System.Int32)); + createCol(shapes, "PathTaperY", typeof(System.Int32)); + createCol(shapes, "PathTwist", typeof(System.Int32)); + createCol(shapes, "PathTwistBegin", typeof(System.Int32)); + // profile + createCol(shapes, "ProfileBegin", typeof(System.Int32)); + createCol(shapes, "ProfileEnd", typeof(System.Int32)); + createCol(shapes, "ProfileCurve", typeof(System.Int32)); + createCol(shapes, "ProfileHollow", typeof(System.Int32)); + // text TODO: this isn't right, but I'm not sure the right + // way to specify this as a blob atm + createCol(shapes, "Texture", typeof(System.Byte[])); + + shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] }; + + return shapes; + } + private Dictionary createShapeDataDefs() { Dictionary data = new Dictionary(); -- cgit v1.1