diff options
author | Sean Dague | 2007-09-07 12:37:05 +0000 |
---|---|---|
committer | Sean Dague | 2007-09-07 12:37:05 +0000 |
commit | 6a45a1ce9c7be613ebc7f16372acf27c991a245e (patch) | |
tree | 1baf266ca85789c400c5b281f4a059050f8c9c68 | |
parent | minor change to CONTRIBUTORS, and a quick test of the (diff) | |
download | opensim-SC-6a45a1ce9c7be613ebc7f16372acf27c991a245e.zip opensim-SC-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.gz opensim-SC-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.bz2 opensim-SC-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.xz |
factor out common methods to SQLiteBase
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Framework/Data.SQLite/SQLiteBase.cs | 230 | ||||
-rw-r--r-- | OpenSim/Framework/Data.SQLite/SQLiteUserData.cs | 170 |
2 files changed, 231 insertions, 169 deletions
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteBase.cs b/OpenSim/Framework/Data.SQLite/SQLiteBase.cs new file mode 100644 index 0000000..54bb719 --- /dev/null +++ b/OpenSim/Framework/Data.SQLite/SQLiteBase.cs | |||
@@ -0,0 +1,230 @@ | |||
1 | /* | ||
2 | * Copyright (c) Contributors, http://www.openmetaverse.org/ | ||
3 | * See CONTRIBUTORS.TXT for a full list of copyright holders. | ||
4 | * | ||
5 | * Redistribution and use in source and binary forms, with or without | ||
6 | * modification, are permitted provided that the following conditions are met: | ||
7 | * * Redistributions of source code must retain the above copyright | ||
8 | * notice, this list of conditions and the following disclaimer. | ||
9 | * * Redistributions in binary form must reproduce the above copyright | ||
10 | * notice, this list of conditions and the following disclaimer in the | ||
11 | * documentation and/or other materials provided with the distribution. | ||
12 | * * Neither the name of the OpenSim Project nor the | ||
13 | * names of its contributors may be used to endorse or promote products | ||
14 | * derived from this software without specific prior written permission. | ||
15 | * | ||
16 | * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS AND ANY | ||
17 | * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED | ||
18 | * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE | ||
19 | * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY | ||
20 | * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES | ||
21 | * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; | ||
22 | * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND | ||
23 | * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT | ||
24 | * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS | ||
25 | * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. | ||
26 | * | ||
27 | */ | ||
28 | using System; | ||
29 | using System.IO; | ||
30 | using libsecondlife; | ||
31 | using OpenSim.Framework.Utilities; | ||
32 | using System.Data; | ||
33 | using System.Data.SqlTypes; | ||
34 | using Mono.Data.SqliteClient; | ||
35 | using OpenSim.Framework.Console; | ||
36 | |||
37 | namespace OpenSim.Framework.Data.SQLite | ||
38 | { | ||
39 | /// <summary> | ||
40 | /// A base class for methods needed by all SQLite database classes | ||
41 | /// </summary> | ||
42 | public class SQLiteBase | ||
43 | { | ||
44 | /*********************************************************************** | ||
45 | * | ||
46 | * Database Definition Functions | ||
47 | * | ||
48 | * This should be db agnostic as we define them in ADO.NET terms | ||
49 | * | ||
50 | **********************************************************************/ | ||
51 | |||
52 | protected static void createCol(DataTable dt, string name, System.Type type) | ||
53 | { | ||
54 | DataColumn col = new DataColumn(name, type); | ||
55 | dt.Columns.Add(col); | ||
56 | } | ||
57 | |||
58 | /*********************************************************************** | ||
59 | * | ||
60 | * SQL Statement Creation Functions | ||
61 | * | ||
62 | * These functions create SQL statements for update, insert, and create. | ||
63 | * They can probably be factored later to have a db independant | ||
64 | * portion and a db specific portion | ||
65 | * | ||
66 | **********************************************************************/ | ||
67 | |||
68 | protected static SqliteCommand createInsertCommand(string table, DataTable dt) | ||
69 | { | ||
70 | /** | ||
71 | * This is subtle enough to deserve some commentary. | ||
72 | * Instead of doing *lots* and *lots of hardcoded strings | ||
73 | * for database definitions we'll use the fact that | ||
74 | * realistically all insert statements look like "insert | ||
75 | * into A(b, c) values(:b, :c) on the parameterized query | ||
76 | * front. If we just have a list of b, c, etc... we can | ||
77 | * generate these strings instead of typing them out. | ||
78 | */ | ||
79 | string[] cols = new string[dt.Columns.Count]; | ||
80 | for (int i = 0; i < dt.Columns.Count; i++) { | ||
81 | DataColumn col = dt.Columns[i]; | ||
82 | cols[i] = col.ColumnName; | ||
83 | } | ||
84 | |||
85 | string sql = "insert into " + table + "("; | ||
86 | sql += String.Join(", ", cols); | ||
87 | // important, the first ':' needs to be here, the rest get added in the join | ||
88 | sql += ") values (:"; | ||
89 | sql += String.Join(", :", cols); | ||
90 | sql += ")"; | ||
91 | SqliteCommand cmd = new SqliteCommand(sql); | ||
92 | |||
93 | // this provides the binding for all our parameters, so | ||
94 | // much less code than it used to be | ||
95 | foreach (DataColumn col in dt.Columns) | ||
96 | { | ||
97 | cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); | ||
98 | } | ||
99 | return cmd; | ||
100 | } | ||
101 | |||
102 | protected static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt) | ||
103 | { | ||
104 | string sql = "update " + table + " set "; | ||
105 | string subsql = ""; | ||
106 | foreach (DataColumn col in dt.Columns) | ||
107 | { | ||
108 | if (subsql.Length > 0) | ||
109 | { // a map function would rock so much here | ||
110 | subsql += ", "; | ||
111 | } | ||
112 | subsql += col.ColumnName + "= :" + col.ColumnName; | ||
113 | } | ||
114 | sql += subsql; | ||
115 | sql += " where " + pk; | ||
116 | SqliteCommand cmd = new SqliteCommand(sql); | ||
117 | |||
118 | // this provides the binding for all our parameters, so | ||
119 | // much less code than it used to be | ||
120 | |||
121 | foreach (DataColumn col in dt.Columns) | ||
122 | { | ||
123 | cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); | ||
124 | } | ||
125 | return cmd; | ||
126 | } | ||
127 | |||
128 | |||
129 | protected static string defineTable(DataTable dt) | ||
130 | { | ||
131 | string sql = "create table " + dt.TableName + "("; | ||
132 | string subsql = ""; | ||
133 | foreach (DataColumn col in dt.Columns) | ||
134 | { | ||
135 | if (subsql.Length > 0) | ||
136 | { // a map function would rock so much here | ||
137 | subsql += ",\n"; | ||
138 | } | ||
139 | subsql += col.ColumnName + " " + sqliteType(col.DataType); | ||
140 | if(col == dt.PrimaryKey[0]) | ||
141 | { | ||
142 | subsql += " primary key"; | ||
143 | } | ||
144 | } | ||
145 | sql += subsql; | ||
146 | sql += ")"; | ||
147 | return sql; | ||
148 | } | ||
149 | |||
150 | /*********************************************************************** | ||
151 | * | ||
152 | * Database Binding functions | ||
153 | * | ||
154 | * These will be db specific due to typing, and minor differences | ||
155 | * in databases. | ||
156 | * | ||
157 | **********************************************************************/ | ||
158 | |||
159 | ///<summary> | ||
160 | /// This is a convenience function that collapses 5 repetitive | ||
161 | /// lines for defining SqliteParameters to 2 parameters: | ||
162 | /// column name and database type. | ||
163 | /// | ||
164 | /// It assumes certain conventions like :param as the param | ||
165 | /// name to replace in parametrized queries, and that source | ||
166 | /// version is always current version, both of which are fine | ||
167 | /// for us. | ||
168 | ///</summary> | ||
169 | ///<returns>a built sqlite parameter</returns> | ||
170 | protected static SqliteParameter createSqliteParameter(string name, System.Type type) | ||
171 | { | ||
172 | SqliteParameter param = new SqliteParameter(); | ||
173 | param.ParameterName = ":" + name; | ||
174 | param.DbType = dbtypeFromType(type); | ||
175 | param.SourceColumn = name; | ||
176 | param.SourceVersion = DataRowVersion.Current; | ||
177 | return param; | ||
178 | } | ||
179 | |||
180 | /*********************************************************************** | ||
181 | * | ||
182 | * Type conversion functions | ||
183 | * | ||
184 | **********************************************************************/ | ||
185 | |||
186 | protected static DbType dbtypeFromType(Type type) | ||
187 | { | ||
188 | if (type == typeof(System.String)) { | ||
189 | return DbType.String; | ||
190 | } else if (type == typeof(System.Int32)) { | ||
191 | return DbType.Int32; | ||
192 | } else if (type == typeof(System.UInt32)) { | ||
193 | return DbType.UInt32; | ||
194 | } else if (type == typeof(System.Int64)) { | ||
195 | return DbType.Int64; | ||
196 | } else if (type == typeof(System.UInt64)) { | ||
197 | return DbType.UInt64; | ||
198 | } else if (type == typeof(System.Double)) { | ||
199 | return DbType.Double; | ||
200 | } else if (type == typeof(System.Byte[])) { | ||
201 | return DbType.Binary; | ||
202 | } else { | ||
203 | return DbType.String; | ||
204 | } | ||
205 | } | ||
206 | |||
207 | // this is something we'll need to implement for each db | ||
208 | // slightly differently. | ||
209 | protected static string sqliteType(Type type) | ||
210 | { | ||
211 | if (type == typeof(System.String)) { | ||
212 | return "varchar(255)"; | ||
213 | } else if (type == typeof(System.Int32)) { | ||
214 | return "integer"; | ||
215 | } else if (type == typeof(System.UInt32)) { | ||
216 | return "integer"; | ||
217 | } else if (type == typeof(System.Int64)) { | ||
218 | return "varchar(255)"; | ||
219 | } else if (type == typeof(System.UInt64)) { | ||
220 | return "varchar(255)"; | ||
221 | } else if (type == typeof(System.Double)) { | ||
222 | return "float"; | ||
223 | } else if (type == typeof(System.Byte[])) { | ||
224 | return "blob"; | ||
225 | } else { | ||
226 | return "string"; | ||
227 | } | ||
228 | } | ||
229 | } | ||
230 | } | ||
diff --git a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs index b09354d..26c832d 100644 --- a/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs +++ b/OpenSim/Framework/Data.SQLite/SQLiteUserData.cs | |||
@@ -39,7 +39,7 @@ namespace OpenSim.Framework.Data.SQLite | |||
39 | /// <summary> | 39 | /// <summary> |
40 | /// A User storage interface for the DB4o database system | 40 | /// A User storage interface for the DB4o database system |
41 | /// </summary> | 41 | /// </summary> |
42 | public class SQLiteUserData : IUserData | 42 | public class SQLiteUserData : SQLiteBase, IUserData |
43 | { | 43 | { |
44 | /// <summary> | 44 | /// <summary> |
45 | /// The database manager | 45 | /// The database manager |
@@ -284,12 +284,6 @@ namespace OpenSim.Framework.Data.SQLite | |||
284 | * | 284 | * |
285 | **********************************************************************/ | 285 | **********************************************************************/ |
286 | 286 | ||
287 | private void createCol(DataTable dt, string name, System.Type type) | ||
288 | { | ||
289 | DataColumn col = new DataColumn(name, type); | ||
290 | dt.Columns.Add(col); | ||
291 | } | ||
292 | |||
293 | private DataTable createUsersTable() | 287 | private DataTable createUsersTable() |
294 | { | 288 | { |
295 | DataTable users = new DataTable("users"); | 289 | DataTable users = new DataTable("users"); |
@@ -477,98 +471,6 @@ namespace OpenSim.Framework.Data.SQLite | |||
477 | 471 | ||
478 | /*********************************************************************** | 472 | /*********************************************************************** |
479 | * | 473 | * |
480 | * SQL Statement Creation Functions | ||
481 | * | ||
482 | * These functions create SQL statements for update, insert, and create. | ||
483 | * They can probably be factored later to have a db independant | ||
484 | * portion and a db specific portion | ||
485 | * | ||
486 | **********************************************************************/ | ||
487 | |||
488 | private SqliteCommand createInsertCommand(string table, DataTable dt) | ||
489 | { | ||
490 | /** | ||
491 | * This is subtle enough to deserve some commentary. | ||
492 | * Instead of doing *lots* and *lots of hardcoded strings | ||
493 | * for database definitions we'll use the fact that | ||
494 | * realistically all insert statements look like "insert | ||
495 | * into A(b, c) values(:b, :c) on the parameterized query | ||
496 | * front. If we just have a list of b, c, etc... we can | ||
497 | * generate these strings instead of typing them out. | ||
498 | */ | ||
499 | string[] cols = new string[dt.Columns.Count]; | ||
500 | for (int i = 0; i < dt.Columns.Count; i++) { | ||
501 | DataColumn col = dt.Columns[i]; | ||
502 | cols[i] = col.ColumnName; | ||
503 | } | ||
504 | |||
505 | string sql = "insert into " + table + "("; | ||
506 | sql += String.Join(", ", cols); | ||
507 | // important, the first ':' needs to be here, the rest get added in the join | ||
508 | sql += ") values (:"; | ||
509 | sql += String.Join(", :", cols); | ||
510 | sql += ")"; | ||
511 | SqliteCommand cmd = new SqliteCommand(sql); | ||
512 | |||
513 | // this provides the binding for all our parameters, so | ||
514 | // much less code than it used to be | ||
515 | foreach (DataColumn col in dt.Columns) | ||
516 | { | ||
517 | cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); | ||
518 | } | ||
519 | return cmd; | ||
520 | } | ||
521 | |||
522 | private SqliteCommand createUpdateCommand(string table, string pk, DataTable dt) | ||
523 | { | ||
524 | string sql = "update " + table + " set "; | ||
525 | string subsql = ""; | ||
526 | foreach (DataColumn col in dt.Columns) | ||
527 | { | ||
528 | if (subsql.Length > 0) | ||
529 | { // a map function would rock so much here | ||
530 | subsql += ", "; | ||
531 | } | ||
532 | subsql += col.ColumnName + "= :" + col.ColumnName; | ||
533 | } | ||
534 | sql += subsql; | ||
535 | sql += " where " + pk; | ||
536 | SqliteCommand cmd = new SqliteCommand(sql); | ||
537 | |||
538 | // this provides the binding for all our parameters, so | ||
539 | // much less code than it used to be | ||
540 | |||
541 | foreach (DataColumn col in dt.Columns) | ||
542 | { | ||
543 | cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType)); | ||
544 | } | ||
545 | return cmd; | ||
546 | } | ||
547 | |||
548 | |||
549 | private string defineTable(DataTable dt) | ||
550 | { | ||
551 | string sql = "create table " + dt.TableName + "("; | ||
552 | string subsql = ""; | ||
553 | foreach (DataColumn col in dt.Columns) | ||
554 | { | ||
555 | if (subsql.Length > 0) | ||
556 | { // a map function would rock so much here | ||
557 | subsql += ",\n"; | ||
558 | } | ||
559 | subsql += col.ColumnName + " " + sqliteType(col.DataType); | ||
560 | if(col == dt.PrimaryKey[0]) | ||
561 | { | ||
562 | subsql += " primary key"; | ||
563 | } | ||
564 | } | ||
565 | sql += subsql; | ||
566 | sql += ")"; | ||
567 | return sql; | ||
568 | } | ||
569 | |||
570 | /*********************************************************************** | ||
571 | * | ||
572 | * Database Binding functions | 474 | * Database Binding functions |
573 | * | 475 | * |
574 | * These will be db specific due to typing, and minor differences | 476 | * These will be db specific due to typing, and minor differences |
@@ -576,27 +478,6 @@ namespace OpenSim.Framework.Data.SQLite | |||
576 | * | 478 | * |
577 | **********************************************************************/ | 479 | **********************************************************************/ |
578 | 480 | ||
579 | ///<summary> | ||
580 | /// This is a convenience function that collapses 5 repetitive | ||
581 | /// lines for defining SqliteParameters to 2 parameters: | ||
582 | /// column name and database type. | ||
583 | /// | ||
584 | /// It assumes certain conventions like :param as the param | ||
585 | /// name to replace in parametrized queries, and that source | ||
586 | /// version is always current version, both of which are fine | ||
587 | /// for us. | ||
588 | ///</summary> | ||
589 | ///<returns>a built sqlite parameter</returns> | ||
590 | private SqliteParameter createSqliteParameter(string name, System.Type type) | ||
591 | { | ||
592 | SqliteParameter param = new SqliteParameter(); | ||
593 | param.ParameterName = ":" + name; | ||
594 | param.DbType = dbtypeFromType(type); | ||
595 | param.SourceColumn = name; | ||
596 | param.SourceVersion = DataRowVersion.Current; | ||
597 | return param; | ||
598 | } | ||
599 | |||
600 | private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn) | 481 | private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn) |
601 | { | 482 | { |
602 | da.InsertCommand = createInsertCommand("users", ds.Tables["users"]); | 483 | da.InsertCommand = createInsertCommand("users", ds.Tables["users"]); |
@@ -634,54 +515,5 @@ namespace OpenSim.Framework.Data.SQLite | |||
634 | return true; | 515 | return true; |
635 | } | 516 | } |
636 | 517 | ||
637 | /*********************************************************************** | ||
638 | * | ||
639 | * Type conversion functions | ||
640 | * | ||
641 | **********************************************************************/ | ||
642 | |||
643 | private DbType dbtypeFromType(Type type) | ||
644 | { | ||
645 | if (type == typeof(System.String)) { | ||
646 | return DbType.String; | ||
647 | } else if (type == typeof(System.Int32)) { | ||
648 | return DbType.Int32; | ||
649 | } else if (type == typeof(System.UInt32)) { | ||
650 | return DbType.UInt32; | ||
651 | } else if (type == typeof(System.Int64)) { | ||
652 | return DbType.Int64; | ||
653 | } else if (type == typeof(System.UInt64)) { | ||
654 | return DbType.UInt64; | ||
655 | } else if (type == typeof(System.Double)) { | ||
656 | return DbType.Double; | ||
657 | } else if (type == typeof(System.Byte[])) { | ||
658 | return DbType.Binary; | ||
659 | } else { | ||
660 | return DbType.String; | ||
661 | } | ||
662 | } | ||
663 | |||
664 | // this is something we'll need to implement for each db | ||
665 | // slightly differently. | ||
666 | private string sqliteType(Type type) | ||
667 | { | ||
668 | if (type == typeof(System.String)) { | ||
669 | return "varchar(255)"; | ||
670 | } else if (type == typeof(System.Int32)) { | ||
671 | return "integer"; | ||
672 | } else if (type == typeof(System.UInt32)) { | ||
673 | return "integer"; | ||
674 | } else if (type == typeof(System.Int64)) { | ||
675 | return "varchar(255)"; | ||
676 | } else if (type == typeof(System.UInt64)) { | ||
677 | return "varchar(255)"; | ||
678 | } else if (type == typeof(System.Double)) { | ||
679 | return "float"; | ||
680 | } else if (type == typeof(System.Byte[])) { | ||
681 | return "blob"; | ||
682 | } else { | ||
683 | return "string"; | ||
684 | } | ||
685 | } | ||
686 | } | 518 | } |
687 | } | 519 | } |