aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs
diff options
context:
space:
mode:
authorJustin Clark-Casey (justincc)2010-04-30 17:45:00 +0100
committerJustin Clark-Casey (justincc)2010-04-30 17:45:00 +0100
commitcc67de5b86ebcebadbe2ea46872a0dc63d99cae7 (patch)
tree1607da4eee9e63fe45e0abcf05da37ae5e239d70 /OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs
parentMake SQLiteNG the default since it actually does work with Mono 2.4 on Linux. (diff)
downloadopensim-SC_OLD-cc67de5b86ebcebadbe2ea46872a0dc63d99cae7.zip
opensim-SC_OLD-cc67de5b86ebcebadbe2ea46872a0dc63d99cae7.tar.gz
opensim-SC_OLD-cc67de5b86ebcebadbe2ea46872a0dc63d99cae7.tar.bz2
opensim-SC_OLD-cc67de5b86ebcebadbe2ea46872a0dc63d99cae7.tar.xz
rename SQLiteNG to SQLite and SQLite to SQLiteLegacy
this seems the least evil way forward since mono 2.6 and later will see increasing usage, and this only works with what was SQLiteNG MAC USERS WILL NEED TO CHANGE REFERENCES TO "OpenSim.Data.SQLite.dll" to "OpenSim.Data.SQLiteLegacy.dll" in OpenSim.ini and config-include/StandaloneCommon.ini (if using standalone) See the OpenSim.ini.example and StandaloneCommon.ini.example files for more details This commit also temporarily changes unsigned ParentEstateID values in the OpenSim.Data.Tests to signed temporarily, since the new plugin enforces creation of signed fields in the database (which is what the SQL actually specifies). And change data columns in sqlite is a pita.
Diffstat (limited to 'OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs')
-rw-r--r--OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs307
1 files changed, 307 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs b/OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs
new file mode 100644
index 0000000..095a262
--- /dev/null
+++ b/OpenSim/Data/SQLiteLegacy/SQLiteUtils.cs
@@ -0,0 +1,307 @@
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
28using System;
29using System.Data;
30using Mono.Data.SqliteClient;
31
32namespace OpenSim.Data.SQLiteLegacy
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 /// <summary>
48 ///
49 /// </summary>
50 /// <param name="dt"></param>
51 /// <param name="name"></param>
52 /// <param name="type"></param>
53 public static void createCol(DataTable dt, string name, Type type)
54 {
55 DataColumn col = new DataColumn(name, type);
56 dt.Columns.Add(col);
57 }
58
59 /***********************************************************************
60 *
61 * SQL Statement Creation Functions
62 *
63 * These functions create SQL statements for update, insert, and create.
64 * They can probably be factored later to have a db independant
65 * portion and a db specific portion
66 *
67 **********************************************************************/
68
69 /// <summary>
70 /// Create an insert command
71 /// </summary>
72 /// <param name="table">table name</param>
73 /// <param name="dt">data table</param>
74 /// <returns>the created command</returns>
75 /// <remarks>
76 /// This is subtle enough to deserve some commentary.
77 /// Instead of doing *lots* and *lots of hardcoded strings
78 /// for database definitions we'll use the fact that
79 /// realistically all insert statements look like "insert
80 /// into A(b, c) values(:b, :c) on the parameterized query
81 /// front. If we just have a list of b, c, etc... we can
82 /// generate these strings instead of typing them out.
83 /// </remarks>
84 public static SqliteCommand createInsertCommand(string table, DataTable dt)
85 {
86
87 string[] cols = new string[dt.Columns.Count];
88 for (int i = 0; i < dt.Columns.Count; i++)
89 {
90 DataColumn col = dt.Columns[i];
91 cols[i] = col.ColumnName;
92 }
93
94 string sql = "insert into " + table + "(";
95 sql += String.Join(", ", cols);
96 // important, the first ':' needs to be here, the rest get added in the join
97 sql += ") values (:";
98 sql += String.Join(", :", cols);
99 sql += ")";
100 SqliteCommand cmd = new SqliteCommand(sql);
101
102 // this provides the binding for all our parameters, so
103 // much less code than it used to be
104 foreach (DataColumn col in dt.Columns)
105 {
106 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
107 }
108 return cmd;
109 }
110
111 /// <summary>
112 /// create an update command
113 /// </summary>
114 /// <param name="table">table name</param>
115 /// <param name="pk"></param>
116 /// <param name="dt"></param>
117 /// <returns>the created command</returns>
118 public static SqliteCommand createUpdateCommand(string table, string pk, DataTable dt)
119 {
120 string sql = "update " + table + " set ";
121 string subsql = String.Empty;
122 foreach (DataColumn col in dt.Columns)
123 {
124 if (subsql.Length > 0)
125 {
126 // a map function would rock so much here
127 subsql += ", ";
128 }
129 subsql += col.ColumnName + "= :" + col.ColumnName;
130 }
131 sql += subsql;
132 sql += " where " + pk;
133 SqliteCommand cmd = new SqliteCommand(sql);
134
135 // this provides the binding for all our parameters, so
136 // much less code than it used to be
137
138 foreach (DataColumn col in dt.Columns)
139 {
140 cmd.Parameters.Add(createSqliteParameter(col.ColumnName, col.DataType));
141 }
142 return cmd;
143 }
144
145 /// <summary>
146 ///
147 /// </summary>
148 /// <param name="dt">Data Table</param>
149 /// <returns></returns>
150 public static string defineTable(DataTable dt)
151 {
152 string sql = "create table " + dt.TableName + "(";
153 string subsql = String.Empty;
154 foreach (DataColumn col in dt.Columns)
155 {
156 if (subsql.Length > 0)
157 {
158 // a map function would rock so much here
159 subsql += ",\n";
160 }
161 subsql += col.ColumnName + " " + sqliteType(col.DataType);
162 if (dt.PrimaryKey.Length > 0)
163 {
164 if (col == dt.PrimaryKey[0])
165 {
166 subsql += " primary key";
167 }
168 }
169 }
170 sql += subsql;
171 sql += ")";
172 return sql;
173 }
174
175 /***********************************************************************
176 *
177 * Database Binding functions
178 *
179 * These will be db specific due to typing, and minor differences
180 * in databases.
181 *
182 **********************************************************************/
183
184 ///<summary>
185 /// <para>
186 /// This is a convenience function that collapses 5 repetitive
187 /// lines for defining SqliteParameters to 2 parameters:
188 /// column name and database type.
189 /// </para>
190 ///
191 /// <para>
192 /// It assumes certain conventions like :param as the param
193 /// name to replace in parametrized queries, and that source
194 /// version is always current version, both of which are fine
195 /// for us.
196 /// </para>
197 ///</summary>
198 /// <param name="name"></param>
199 /// <param name="type"></param>
200 ///<returns>a built sqlite parameter</returns>
201 public static SqliteParameter createSqliteParameter(string name, Type type)
202 {
203 SqliteParameter param = new SqliteParameter();
204 param.ParameterName = ":" + name;
205 param.DbType = dbtypeFromType(type);
206 param.SourceColumn = name;
207 param.SourceVersion = DataRowVersion.Current;
208 return param;
209 }
210
211 /***********************************************************************
212 *
213 * Type conversion functions
214 *
215 **********************************************************************/
216
217 /// <summary>
218 /// Type conversion function
219 /// </summary>
220 /// <param name="type">a type</param>
221 /// <returns>a DbType</returns>
222 public static DbType dbtypeFromType(Type type)
223 {
224 if (type == typeof (String))
225 {
226 return DbType.String;
227 }
228 else if (type == typeof (Int32))
229 {
230 return DbType.Int32;
231 }
232 else if (type == typeof (UInt32))
233 {
234 return DbType.UInt32;
235 }
236 else if (type == typeof (Int64))
237 {
238 return DbType.Int64;
239 }
240 else if (type == typeof (UInt64))
241 {
242 return DbType.UInt64;
243 }
244 else if (type == typeof (Double))
245 {
246 return DbType.Double;
247 }
248 else if (type == typeof (Boolean))
249 {
250 return DbType.Boolean;
251 }
252 else if (type == typeof (Byte[]))
253 {
254 return DbType.Binary;
255 }
256 else
257 {
258 return DbType.String;
259 }
260 }
261
262 /// <summary>
263 /// </summary>
264 /// <param name="type">a Type</param>
265 /// <returns>a string</returns>
266 /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks>
267 public static string sqliteType(Type type)
268 {
269 if (type == typeof (String))
270 {
271 return "varchar(255)";
272 }
273 else if (type == typeof (Int32))
274 {
275 return "integer";
276 }
277 else if (type == typeof (UInt32))
278 {
279 return "integer";
280 }
281 else if (type == typeof (Int64))
282 {
283 return "varchar(255)";
284 }
285 else if (type == typeof (UInt64))
286 {
287 return "varchar(255)";
288 }
289 else if (type == typeof (Double))
290 {
291 return "float";
292 }
293 else if (type == typeof (Boolean))
294 {
295 return "integer";
296 }
297 else if (type == typeof (Byte[]))
298 {
299 return "blob";
300 }
301 else
302 {
303 return "string";
304 }
305 }
306 }
307}