aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite/SQLiteUtils.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/SQLite/SQLiteUtils.cs')
-rw-r--r--OpenSim/Data/SQLite/SQLiteUtils.cs269
1 files changed, 269 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLite/SQLiteUtils.cs b/OpenSim/Data/SQLite/SQLiteUtils.cs
new file mode 100644
index 0000000..1334e53
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUtils.cs
@@ -0,0 +1,269 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.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.Data;
30using Mono.Data.SqliteClient;
31
32namespace OpenSim.Framework.Data.SQLite
33{
34 /// <summary>
35 /// A base class for methods needed by all SQLite database classes
36 /// </summary>
37 public class SQLiteUtil
38 {
39 /***********************************************************************
40 *
41 * Database Definition Helper Functions
42 *
43 * This should be db agnostic as we define them in ADO.NET terms
44 *
45 **********************************************************************/
46
47 public static void createCol(DataTable dt, string name, Type type)
48 {
49 DataColumn col = new DataColumn(name, type);
50 dt.Columns.Add(col);
51 }
52
53 /***********************************************************************
54 *
55 * SQL Statement Creation Functions
56 *
57 * These functions create SQL statements for update, insert, and create.
58 * They can probably be factored later to have a db independant
59 * portion and a db specific portion
60 *
61 **********************************************************************/
62
63 public static SqliteCommand createInsertCommand(string table, DataTable dt)
64 {
65 /**
66 * This is subtle enough to deserve some commentary.
67 * Instead of doing *lots* and *lots of hardcoded strings
68 * for database definitions we'll use the fact that
69 * realistically all insert statements look like "insert
70 * into A(b, c) values(:b, :c) on the parameterized query
71 * front. If we just have a list of b, c, etc... we can
72 * generate these strings instead of typing them out.
73 */
74 string[] cols = new string[dt.Columns.Count];
75 for (int i = 0; i < dt.Columns.Count; i++)
76 {
77 DataColumn col = dt.Columns[i];
78 cols[i] = col.ColumnName;
79 }
80
81 string sql = "insert into " + table + "(";
82 sql += String.Join(", ", cols);
83 // important, the first ':' needs to be here, the rest get added in the join
84 sql += ") values (:";
85 sql += String.Join(", :", cols);
86 sql += ")";
87 SqliteCommand cmd = new SqliteCommand(sql);
88
89 // this provides the binding for all our parameters, so
90 // much less code than it used to be
91 foreach (DataColumn col in dt.Columns)
92 {
93 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
94 }
95 return cmd;
96 }
97
98 public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
99 {
100 string sql = "update " + table + " set ";
101 string subsql = String.Empty;
102 foreach (DataColumn col in dt.Columns)
103 {
104 if (subsql.Length > 0)
105 {
106 // a map function would rock so much here
107 subsql += ", ";
108 }
109 subsql += col.ColumnName + "= :" + col.ColumnName;
110 }
111 sql += subsql;
112 sql += " where " + pk;
113 SqliteCommand cmd = new SqliteCommand(sql);
114
115 // this provides the binding for all our parameters, so
116 // much less code than it used to be
117
118 foreach (DataColumn col in dt.Columns)
119 {
120 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
121 }
122 return cmd;
123 }
124
125
126 public static string defineTable(DataTable dt)
127 {
128 string sql = "create table " + dt.TableName + "(";
129 string subsql = String.Empty;
130 foreach (DataColumn col in dt.Columns)
131 {
132 if (subsql.Length > 0)
133 {
134 // a map function would rock so much here
135 subsql += ",\n";
136 }
137 subsql += col.ColumnName + " " + sqliteType(col.DataType);
138 if (dt.PrimaryKey.Length > 0)
139 {
140 if (col == dt.PrimaryKey[0])
141 {
142 subsql += " primary key";
143 }
144 }
145 }
146 sql += subsql;
147 sql += ")";
148 return sql;
149 }
150
151 /***********************************************************************
152 *
153 * Database Binding functions
154 *
155 * These will be db specific due to typing, and minor differences
156 * in databases.
157 *
158 **********************************************************************/
159
160 ///<summary>
161 /// This is a convenience function that collapses 5 repetitive
162 /// lines for defining SqliteParameters to 2 parameters:
163 /// column name and database type.
164 ///
165 /// It assumes certain conventions like :param as the param
166 /// name to replace in parametrized queries, and that source
167 /// version is always current version, both of which are fine
168 /// for us.
169 ///</summary>
170 ///<returns>a built sqlite parameter</returns>
171 public static SqliteParameter createSqliteParameter(string name, Type type)
172 {
173 SqliteParameter param = new SqliteParameter();
174 param.ParameterName = ":" + name;
175 param.DbType = dbtypeFromType(type);
176 param.SourceColumn = name;
177 param.SourceVersion = DataRowVersion.Current;
178 return param;
179 }
180
181 /***********************************************************************
182 *
183 * Type conversion functions
184 *
185 **********************************************************************/
186
187 public static DbType dbtypeFromType(Type type)
188 {
189 if (type == typeof (String))
190 {
191 return DbType.String;
192 }
193 else if (type == typeof (Int32))
194 {
195 return DbType.Int32;
196 }
197 else if (type == typeof (UInt32))
198 {
199 return DbType.UInt32;
200 }
201 else if (type == typeof (Int64))
202 {
203 return DbType.Int64;
204 }
205 else if (type == typeof (UInt64))
206 {
207 return DbType.UInt64;
208 }
209 else if (type == typeof (Double))
210 {
211 return DbType.Double;
212 }
213 else if (type == typeof (Boolean))
214 {
215 return DbType.Boolean;
216 }
217 else if (type == typeof (Byte[]))
218 {
219 return DbType.Binary;
220 }
221 else
222 {
223 return DbType.String;
224 }
225 }
226
227 // this is something we'll need to implement for each db
228 // slightly differently.
229 public static string sqliteType(Type type)
230 {
231 if (type == typeof (String))
232 {
233 return "varchar(255)";
234 }
235 else if (type == typeof (Int32))
236 {
237 return "integer";
238 }
239 else if (type == typeof (UInt32))
240 {
241 return "integer";
242 }
243 else if (type == typeof (Int64))
244 {
245 return "varchar(255)";
246 }
247 else if (type == typeof (UInt64))
248 {
249 return "varchar(255)";
250 }
251 else if (type == typeof (Double))
252 {
253 return "float";
254 }
255 else if (type == typeof (Boolean))
256 {
257 return "integer";
258 }
259 else if (type == typeof (Byte[]))
260 {
261 return "blob";
262 }
263 else
264 {
265 return "string";
266 }
267 }
268 }
269}