aboutsummaryrefslogtreecommitdiffstatshomepage
diff options
context:
space:
mode:
authorSean Dague2007-09-07 12:37:05 +0000
committerSean Dague2007-09-07 12:37:05 +0000
commit6a45a1ce9c7be613ebc7f16372acf27c991a245e (patch)
tree1baf266ca85789c400c5b281f4a059050f8c9c68
parentminor change to CONTRIBUTORS, and a quick test of the (diff)
downloadopensim-SC_OLD-6a45a1ce9c7be613ebc7f16372acf27c991a245e.zip
opensim-SC_OLD-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.gz
opensim-SC_OLD-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.bz2
opensim-SC_OLD-6a45a1ce9c7be613ebc7f16372acf27c991a245e.tar.xz
factor out common methods to SQLiteBase
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteBase.cs230
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteUserData.cs170
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*/
28using System;
29using System.IO;
30using libsecondlife;
31using OpenSim.Framework.Utilities;
32using System.Data;
33using System.Data.SqlTypes;
34using Mono.Data.SqliteClient;
35using OpenSim.Framework.Console;
36
37namespace 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}