aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite
diff options
context:
space:
mode:
authorSean Dague2007-08-22 21:09:38 +0000
committerSean Dague2007-08-22 21:09:38 +0000
commit1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7 (patch)
tree5d289ee859b4a03a281d33e9380ea8fa643f3f74 /OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite
parentDebug shows how many bytes (total) a script (assembly) uses after compile and... (diff)
downloadopensim-SC_OLD-1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7.zip
opensim-SC_OLD-1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7.tar.gz
opensim-SC_OLD-1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7.tar.bz2
opensim-SC_OLD-1fa2d487aa89bea9cc5f03b6bb5ed88b319243c7.tar.xz
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.
Diffstat (limited to 'OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite')
-rw-r--r--OpenSim/Region/Storage/OpenSim.DataStore.MonoSqlite/MonoSqliteDataStore.cs237
1 files changed, 187 insertions, 50 deletions
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
52 // We fill the data set, now we've got copies in memory for the information 52 // We fill the data set, now we've got copies in memory for the information
53 // TODO: see if the linkage actually holds. 53 // TODO: see if the linkage actually holds.
54 // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema"); 54 // primDa.FillSchema(ds, SchemaType.Source, "PrimSchema");
55 try { 55 TestPrimsTable(conn);
56 primDa.Fill(ds, "prims"); 56
57 } catch (Mono.Data.SqliteClient.SqliteSyntaxException) { 57 ds.Tables.Add(createPrimTable());
58 InitDB(conn); 58 DataTable prims = ds.Tables["prims"];
59 primDa.Fill(ds, "prims"); 59 primDa.Fill(prims);
60 } 60 MainLog.Instance.Verbose(ds.GetXmlSchema());
61 61
62 shapeDa.Fill(ds, "primshapes"); 62 shapeDa.Fill(ds, "primshapes");
63 ds.AcceptChanges(); 63 ds.AcceptChanges();
64 64
65 DataTable prims = ds.Tables["prims"];
66 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; 65 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
67 setupPrimCommands(primDa, conn); 66 setupPrimCommands(primDa, conn);
68 67
@@ -85,18 +84,32 @@ namespace OpenSim.DataStore.MonoSqliteStorage
85 /// for us. 84 /// for us.
86 ///</summary> 85 ///</summary>
87 ///<returns>a built sqlite parameter</returns> 86 ///<returns>a built sqlite parameter</returns>
88 private SqliteParameter createSqliteParameter(string name, DbType type) 87 private SqliteParameter createSqliteParameter(string name, System.Type type)
89 { 88 {
90 SqliteParameter param = new SqliteParameter(); 89 SqliteParameter param = new SqliteParameter();
91 param.ParameterName = ":" + name; 90 param.ParameterName = ":" + name;
92 param.DbType = type; 91 param.DbType = dbtypeFromType(type);
93 param.SourceColumn = name; 92 param.SourceColumn = name;
94 param.SourceVersion = DataRowVersion.Current; 93 param.SourceVersion = DataRowVersion.Current;
95 return param; 94 return param;
96 } 95 }
97 96
97 private DbType dbtypeFromType(Type type)
98 {
99 if (type == typeof(System.String)) {
100 return DbType.String;
101 } else if (type == typeof(System.Int32)) {
102 return DbType.Int32;
103 } else if (type == typeof(System.Double)) {
104 return DbType.Double;
105 } else if (type == typeof(System.Byte[])) {
106 return DbType.Binary;
107 } else {
108 return DbType.String;
109 }
110 }
98 111
99 private SqliteCommand createInsertCommand(string table, Dictionary<string, DbType> defs) 112 private SqliteCommand createInsertCommand(string table, DataTable dt)
100 { 113 {
101 /** 114 /**
102 * This is subtle enough to deserve some commentary. 115 * This is subtle enough to deserve some commentary.
@@ -107,8 +120,11 @@ namespace OpenSim.DataStore.MonoSqliteStorage
107 * front. If we just have a list of b, c, etc... we can 120 * front. If we just have a list of b, c, etc... we can
108 * generate these strings instead of typing them out. 121 * generate these strings instead of typing them out.
109 */ 122 */
110 string[] cols = new string[defs.Keys.Count]; 123 string[] cols = new string[dt.Columns.Count];
111 defs.Keys.CopyTo(cols, 0); 124 for (int i = 0; i < dt.Columns.Count; i++) {
125 DataColumn col = dt.Columns[i];
126 cols[i] = col.ColumnName;
127 }
112 128
113 string sql = "insert into " + table + "("; 129 string sql = "insert into " + table + "(";
114 sql += String.Join(", ", cols); 130 sql += String.Join(", ", cols);
@@ -120,24 +136,28 @@ namespace OpenSim.DataStore.MonoSqliteStorage
120 136
121 // this provides the binding for all our parameters, so 137 // this provides the binding for all our parameters, so
122 // much less code than it used to be 138 // much less code than it used to be
123 foreach (KeyValuePair<string, DbType> kvp in defs) 139 foreach (DataColumn col in dt.Columns)
124 { 140 {
125 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); 141 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
126 } 142 }
143 // foreach (KeyValuePair<string, DbType> kvp in defs)
144 // {
145 // cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value));
146 // }
127 return cmd; 147 return cmd;
128 } 148 }
129 149
130 private SqliteCommand createUpdateCommand(string table, string pk, Dictionary<string, DbType> defs) 150 private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
131 { 151 {
132 string sql = "update " + table + " set "; 152 string sql = "update " + table + " set ";
133 string subsql = ""; 153 string subsql = "";
134 foreach (string key in defs.Keys) 154 foreach (DataColumn col in dt.Columns)
135 { 155 {
136 if (subsql.Length > 0) 156 if (subsql.Length > 0)
137 { // a map function would rock so much here 157 { // a map function would rock so much here
138 subsql += ", "; 158 subsql += ", ";
139 } 159 }
140 subsql += key + "= :" + key; 160 subsql += col.ColumnName + "= :" + col.ColumnName;
141 } 161 }
142 sql += subsql; 162 sql += subsql;
143 sql += " where " + pk; 163 sql += " where " + pk;
@@ -145,41 +165,38 @@ namespace OpenSim.DataStore.MonoSqliteStorage
145 165
146 // this provides the binding for all our parameters, so 166 // this provides the binding for all our parameters, so
147 // much less code than it used to be 167 // much less code than it used to be
148 foreach (KeyValuePair<string, DbType> kvp in defs) 168
169 foreach (DataColumn col in dt.Columns)
149 { 170 {
150 cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); 171 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
151 } 172 }
152 return cmd; 173 return cmd;
153 } 174 }
154 175
155 private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn) 176 private void setupPrimCommands(SqliteDataAdapter da, SqliteConnection conn)
156 { 177 {
157 Dictionary<string, DbType> primDataDefs = createPrimDataDefs(); 178 da.InsertCommand = createInsertCommand("prims", ds.Tables["prims"]);
158
159 da.InsertCommand = createInsertCommand("prims", primDataDefs);
160 da.InsertCommand.Connection = conn; 179 da.InsertCommand.Connection = conn;
161 180
162 da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", primDataDefs); 181 da.UpdateCommand = createUpdateCommand("prims", "UUID=:UUID", ds.Tables["prims"]);
163 da.UpdateCommand.Connection = conn; 182 da.UpdateCommand.Connection = conn;
164 183
165 SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID"); 184 SqliteCommand delete = new SqliteCommand("delete from prims where UUID = :UUID");
166 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); 185 delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String)));
167 delete.Connection = conn; 186 delete.Connection = conn;
168 da.DeleteCommand = delete; 187 da.DeleteCommand = delete;
169 } 188 }
170 189
171 private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn) 190 private void setupShapeCommands(SqliteDataAdapter da, SqliteConnection conn)
172 { 191 {
173 Dictionary<string, DbType> shapeDataDefs = createShapeDataDefs(); 192 da.InsertCommand = createInsertCommand("primshapes", ds.Tables["primshapes"]);
174
175 da.InsertCommand = createInsertCommand("primshapes", shapeDataDefs);
176 da.InsertCommand.Connection = conn; 193 da.InsertCommand.Connection = conn;
177 194
178 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", shapeDataDefs); 195 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=:UUID", ds.Tables["primshapes"]);
179 da.UpdateCommand.Connection = conn; 196 da.UpdateCommand.Connection = conn;
180 197
181 SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID"); 198 SqliteCommand delete = new SqliteCommand("delete from primshapes where UUID = :UUID");
182 delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); 199 delete.Parameters.Add(createSqliteParameter("UUID", typeof(System.String)));
183 delete.Connection = conn; 200 delete.Connection = conn;
184 da.DeleteCommand = delete; 201 da.DeleteCommand = delete;
185 } 202 }
@@ -578,8 +595,8 @@ namespace OpenSim.DataStore.MonoSqliteStorage
578 595
579 private void InitDB(SqliteConnection conn) 596 private void InitDB(SqliteConnection conn)
580 { 597 {
581 string createPrims = defineTable("prims", "UUID", createPrimDataDefs()); 598 string createPrims = defineTable(createPrimTable());
582 string createShapes = defineTable("primshapes", "UUID", createShapeDataDefs()); 599 string createShapes = defineTable(createShapeTable());
583 600
584 SqliteCommand pcmd = new SqliteCommand(createPrims, conn); 601 SqliteCommand pcmd = new SqliteCommand(createPrims, conn);
585 SqliteCommand scmd = new SqliteCommand(createShapes, conn); 602 SqliteCommand scmd = new SqliteCommand(createShapes, conn);
@@ -589,18 +606,18 @@ namespace OpenSim.DataStore.MonoSqliteStorage
589 conn.Close(); 606 conn.Close();
590 } 607 }
591 608
592 private string defineTable(string name, string primkey, Dictionary<string, DbType> cols) 609 private string defineTable(DataTable dt)
593 { 610 {
594 string sql = "create table " + name + "("; 611 string sql = "create table " + dt.TableName + "(";
595 string subsql = ""; 612 string subsql = "";
596 foreach (string key in cols.Keys) 613 foreach (DataColumn col in dt.Columns)
597 { 614 {
598 if (subsql.Length > 0) 615 if (subsql.Length > 0)
599 { // a map function would rock so much here 616 { // a map function would rock so much here
600 subsql += ",\n"; 617 subsql += ",\n";
601 } 618 }
602 subsql += key + " " + sqliteType(cols[key]); 619 subsql += col.ColumnName + " " + sqliteType(col.DataType);
603 if(key == primkey) 620 if(col == dt.PrimaryKey[0])
604 { 621 {
605 subsql += " primary key"; 622 subsql += " primary key";
606 } 623 }
@@ -610,29 +627,108 @@ namespace OpenSim.DataStore.MonoSqliteStorage
610 return sql; 627 return sql;
611 } 628 }
612 629
613 private string sqliteType(DbType type) 630 private string sqliteType(Type type)
614 { 631 {
615 switch(type) { 632 if (type == typeof(System.String)) {
616 case DbType.String:
617 return "varchar(255)"; 633 return "varchar(255)";
618 634 } else if (type == typeof(System.Int32)) {
619 case DbType.Int32:
620 return "integer"; 635 return "integer";
621 636 } else if (type == typeof(System.Double)) {
622 case DbType.Double:
623 return "float"; 637 return "float";
624 638 } else if (type == typeof(System.Byte[])) {
625 case DbType.Binary:
626 return "blob"; 639 return "blob";
627 640 } else {
628 default: 641 return "string";
629 return "varchar(255)";
630 } 642 }
631 } 643 }
632 644
645 private bool TestPrimsTable(SqliteConnection conn)
646 {
647 SqliteCommand primSelectCmd = new SqliteCommand(primSelect, conn);
648 SqliteDataAdapter da = new SqliteDataAdapter(primSelectCmd);
649 DataSet tmp = new DataSet();
650 try {
651 da.Fill(tmp, "prims");
652 } catch (Mono.Data.SqliteClient.SqliteSyntaxException) {
653 MainLog.Instance.Verbose("SQLite Database does exist... creating");
654 InitDB(conn);
655 }
656
657 // Dictionary<string, DbType> defs = createPrimDataDefs();
658 // // da.FillSchema(ds, SchemaType.Mapped, "prims");
659 da.Fill(tmp, "prims");
660 MainLog.Instance.Verbose("DATASTORE", "Filled prims...");
661 // DataTable prims = ds.Tables["prims"];
662 // foreach (DataColumn col in prims.Columns)
663 // {
664 // MainLog.Instance.Verbose("Found: " + col);
665 // }
666 // return true;
667 return true;
668 }
669
633 /// Methods after this point are big data definition 670 /// Methods after this point are big data definition
634 /// methods, and aren't really interesting unless you are 671 /// methods, and aren't really interesting unless you are
635 /// adjusting the schema. 672 /// adjusting the schema.
673
674 private void createCol(DataTable dt, string name, System.Type type)
675 {
676 DataColumn col = new DataColumn(name, type);
677 dt.Columns.Add(col);
678 }
679
680 private DataTable createPrimTable()
681 {
682 DataTable prims = new DataTable("prims");
683
684 createCol(prims, "UUID", typeof(System.String));
685 createCol(prims, "ParentID", typeof(System.Int32));
686 createCol(prims, "CreationDate", typeof(System.Int32));
687 createCol(prims, "Name", typeof(System.String));
688 createCol(prims, "SceneGroupID", typeof(System.String));
689 // various text fields
690 createCol(prims, "Text", typeof(System.String));
691 createCol(prims, "Description", typeof(System.String));
692 createCol(prims, "SitName", typeof(System.String));
693 createCol(prims, "TouchName", typeof(System.String));
694 // permissions
695 createCol(prims, "CreatorID", typeof(System.String));
696 createCol(prims, "OwnerID", typeof(System.String));
697 createCol(prims, "GroupID", typeof(System.String));
698 createCol(prims, "LastOwnerID", typeof(System.String));
699 createCol(prims, "OwnerMask", typeof(System.Int32));
700 createCol(prims, "NextOwnerMask", typeof(System.Int32));
701 createCol(prims, "GroupMask", typeof(System.Int32));
702 createCol(prims, "EveryoneMask", typeof(System.Int32));
703 createCol(prims, "BaseMask", typeof(System.Int32));
704 // vectors
705 createCol(prims, "PositionX", typeof(System.Double));
706 createCol(prims, "PositionY", typeof(System.Double));
707 createCol(prims, "PositionZ", typeof(System.Double));
708 createCol(prims, "GroupPositionX", typeof(System.Double));
709 createCol(prims, "GroupPositionY", typeof(System.Double));
710 createCol(prims, "GroupPositionZ", typeof(System.Double));
711 createCol(prims, "VelocityX", typeof(System.Double));
712 createCol(prims, "VelocityY", typeof(System.Double));
713 createCol(prims, "VelocityZ", typeof(System.Double));
714 createCol(prims, "AngularVelocityX", typeof(System.Double));
715 createCol(prims, "AngularVelocityY", typeof(System.Double));
716 createCol(prims, "AngularVelocityZ", typeof(System.Double));
717 createCol(prims, "AccelerationX", typeof(System.Double));
718 createCol(prims, "AccelerationY", typeof(System.Double));
719 createCol(prims, "AccelerationZ", typeof(System.Double));
720 // quaternions
721 createCol(prims, "RotationX", typeof(System.Double));
722 createCol(prims, "RotationY", typeof(System.Double));
723 createCol(prims, "RotationZ", typeof(System.Double));
724 createCol(prims, "RotationW", typeof(System.Double));
725
726 // Add in contraints
727 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
728
729 return prims;
730 }
731
636 private Dictionary<string, DbType> createPrimDataDefs() 732 private Dictionary<string, DbType> createPrimDataDefs()
637 { 733 {
638 Dictionary<string, DbType> data = new Dictionary<string, DbType>(); 734 Dictionary<string, DbType> data = new Dictionary<string, DbType>();
@@ -680,6 +776,47 @@ namespace OpenSim.DataStore.MonoSqliteStorage
680 return data; 776 return data;
681 } 777 }
682 778
779
780 private DataTable createShapeTable()
781 {
782 DataTable shapes = new DataTable("primshapes");
783 createCol(shapes, "UUID", typeof(System.String));
784 // shape is an enum
785 createCol(shapes, "Shape", typeof(System.Int32));
786 // vectors
787 createCol(shapes, "ScaleX", typeof(System.Double));
788 createCol(shapes, "ScaleY", typeof(System.Double));
789 createCol(shapes, "ScaleZ", typeof(System.Double));
790 // paths
791 createCol(shapes, "PCode", typeof(System.Int32));
792 createCol(shapes, "PathBegin", typeof(System.Int32));
793 createCol(shapes, "PathEnd", typeof(System.Int32));
794 createCol(shapes, "PathScaleX", typeof(System.Int32));
795 createCol(shapes, "PathScaleY", typeof(System.Int32));
796 createCol(shapes, "PathShearX", typeof(System.Int32));
797 createCol(shapes, "PathShearY", typeof(System.Int32));
798 createCol(shapes, "PathSkew", typeof(System.Int32));
799 createCol(shapes, "PathCurve", typeof(System.Int32));
800 createCol(shapes, "PathRadiusOffset", typeof(System.Int32));
801 createCol(shapes, "PathRevolutions", typeof(System.Int32));
802 createCol(shapes, "PathTaperX", typeof(System.Int32));
803 createCol(shapes, "PathTaperY", typeof(System.Int32));
804 createCol(shapes, "PathTwist", typeof(System.Int32));
805 createCol(shapes, "PathTwistBegin", typeof(System.Int32));
806 // profile
807 createCol(shapes, "ProfileBegin", typeof(System.Int32));
808 createCol(shapes, "ProfileEnd", typeof(System.Int32));
809 createCol(shapes, "ProfileCurve", typeof(System.Int32));
810 createCol(shapes, "ProfileHollow", typeof(System.Int32));
811 // text TODO: this isn't right, but I'm not sure the right
812 // way to specify this as a blob atm
813 createCol(shapes, "Texture", typeof(System.Byte[]));
814
815 shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] };
816
817 return shapes;
818 }
819
683 private Dictionary<string, DbType> createShapeDataDefs() 820 private Dictionary<string, DbType> createShapeDataDefs()
684 { 821 {
685 Dictionary<string, DbType> data = new Dictionary<string, DbType>(); 822 Dictionary<string, DbType> data = new Dictionary<string, DbType>();