aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.SQLite/SQLiteBase.cs
diff options
context:
space:
mode:
authorSean Dague2007-09-07 12:37:05 +0000
committerSean Dague2007-09-07 12:37:05 +0000
commit6a45a1ce9c7be613ebc7f16372acf27c991a245e (patch)
tree1baf266ca85789c400c5b281f4a059050f8c9c68 /OpenSim/Framework/Data.SQLite/SQLiteBase.cs
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
Diffstat (limited to 'OpenSim/Framework/Data.SQLite/SQLiteBase.cs')
-rw-r--r--OpenSim/Framework/Data.SQLite/SQLiteBase.cs230
1 files changed, 230 insertions, 0 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}