clockwerk-opensim – Blame information for rev 1

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