aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/PGSQLManager.cs
diff options
context:
space:
mode:
authorFernando Oliveira2013-10-12 16:33:45 -0500
committerfernando2013-10-12 16:33:45 -0500
commitff8a76825841533bdc5d534b6f58b2ab964ea6c6 (patch)
tree4ddde40916db04c1506486c9e7ba88b37f53e23e /OpenSim/Data/PGSQL/PGSQLManager.cs
parent* pushing test (diff)
downloadopensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.zip
opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.gz
opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.bz2
opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.xz
Fernando Oliveira's Postgress SQL Server Data Connector as a single commit.
* Added PostGreSQL support * Added MySQL/MySQLXGroupData.cs * PostgreSQL data access implementation * PostgreSQL dll binarie and RegionStore.migrations * Migrations Scripts from MSSQL to POSTGRES * Postgres SQL Type fixes * Postgres SQL Connection string * Data type issues * more fixes * tests and +tests * UUID x string - FIGHT! * Fixed PG types to internal csharp types * More data type fix (PostgreSQL fields are case sensitive) :( * more field case sensitive fixes * changed the migration files to be case sensitive for fields. * fixed fields case * finished converting, now search for hidden bugs. * some more fixes * bool type fixed * more case fixes; * creatorID case fixed * case fields fixed * fixed default now() for TMStamp fields with don't allow nulls. * fix case sensitve for Region name and Estate name * fixed case for names for search * fix class name Error * Bug fixed on select and migrations * Un-Reverting my change due to Postgres issue with the ILIKE function * Fixed some issued for Diva Distro * Fixes for integration with Diva Distro * Added System.Core to prebuild.xml for PG project * Configured to make DIff for Push to OpenSim Project * Diffs only to PostgreSQL mods.
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLManager.cs321
1 files changed, 321 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLManager.cs b/OpenSim/Data/PGSQL/PGSQLManager.cs
new file mode 100644
index 0000000..3ddaf38
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLManager.cs
@@ -0,0 +1,321 @@
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.Collections.Generic;
30using System.Data;
31using System.IO;
32using System.Reflection;
33using log4net;
34using OpenMetaverse;
35using Npgsql;
36using NpgsqlTypes;
37
38namespace OpenSim.Data.PGSQL
39{
40 /// <summary>
41 /// A management class for the MS SQL Storage Engine
42 /// </summary>
43 public class PGSQLManager
44 {
45// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 /// <summary>
48 /// Connection string for ADO.net
49 /// </summary>
50 private readonly string connectionString;
51
52 /// <summary>
53 /// Initialize the manager and set the connectionstring
54 /// </summary>
55 /// <param name="connection"></param>
56 public PGSQLManager(string connection)
57 {
58 connectionString = connection;
59 }
60
61 /// <summary>
62 /// Type conversion to a SQLDbType functions
63 /// </summary>
64 /// <param name="type"></param>
65 /// <returns></returns>
66 internal NpgsqlDbType DbtypeFromType(Type type)
67 {
68 if (type == typeof(string))
69 {
70 return NpgsqlDbType.Varchar;
71 }
72 if (type == typeof(double))
73 {
74 return NpgsqlDbType.Double;
75 }
76 if (type == typeof(Single))
77 {
78 return NpgsqlDbType.Double;
79 }
80 if (type == typeof(int))
81 {
82 return NpgsqlDbType.Integer;
83 }
84 if (type == typeof(bool))
85 {
86 return NpgsqlDbType.Boolean;
87 }
88 if (type == typeof(UUID))
89 {
90 return NpgsqlDbType.Uuid;
91 }
92 if (type == typeof(byte))
93 {
94 return NpgsqlDbType.Smallint;
95 }
96 if (type == typeof(sbyte))
97 {
98 return NpgsqlDbType.Integer;
99 }
100 if (type == typeof(Byte[]))
101 {
102 return NpgsqlDbType.Bytea;
103 }
104 if (type == typeof(uint) || type == typeof(ushort))
105 {
106 return NpgsqlDbType.Integer;
107 }
108 if (type == typeof(ulong))
109 {
110 return NpgsqlDbType.Bigint;
111 }
112 if (type == typeof(DateTime))
113 {
114 return NpgsqlDbType.Timestamp;
115 }
116
117 return NpgsqlDbType.Varchar;
118 }
119
120 internal NpgsqlDbType DbtypeFromString(Type type, string PGFieldType)
121 {
122 if (PGFieldType == "")
123 {
124 return DbtypeFromType(type);
125 }
126
127 if (PGFieldType == "character varying")
128 {
129 return NpgsqlDbType.Varchar;
130 }
131 if (PGFieldType == "double precision")
132 {
133 return NpgsqlDbType.Double;
134 }
135 if (PGFieldType == "integer")
136 {
137 return NpgsqlDbType.Integer;
138 }
139 if (PGFieldType == "smallint")
140 {
141 return NpgsqlDbType.Smallint;
142 }
143 if (PGFieldType == "boolean")
144 {
145 return NpgsqlDbType.Boolean;
146 }
147 if (PGFieldType == "uuid")
148 {
149 return NpgsqlDbType.Uuid;
150 }
151 if (PGFieldType == "bytea")
152 {
153 return NpgsqlDbType.Bytea;
154 }
155
156 return DbtypeFromType(type);
157 }
158
159 /// <summary>
160 /// Creates value for parameter.
161 /// </summary>
162 /// <param name="value">The value.</param>
163 /// <returns></returns>
164 private static object CreateParameterValue(object value)
165 {
166 Type valueType = value.GetType();
167
168 if (valueType == typeof(UUID)) //TODO check if this works
169 {
170 return ((UUID) value).Guid;
171 }
172 if (valueType == typeof(UUID))
173 {
174 return ((UUID)value).Guid;
175 }
176 if (valueType == typeof(bool))
177 {
178 return (bool)value;
179 }
180 if (valueType == typeof(Byte[]))
181 {
182 return value;
183 }
184 if (valueType == typeof(int))
185 {
186 return value;
187 }
188 return value;
189 }
190
191 /// <summary>
192 /// Create value for parameter based on PGSQL Schema
193 /// </summary>
194 /// <param name="value"></param>
195 /// <param name="PGFieldType"></param>
196 /// <returns></returns>
197 internal static object CreateParameterValue(object value, string PGFieldType)
198 {
199 if (PGFieldType == "uuid")
200 {
201 UUID uidout;
202 UUID.TryParse(value.ToString(), out uidout);
203 return uidout;
204 }
205 if (PGFieldType == "integer")
206 {
207 int intout;
208 int.TryParse(value.ToString(), out intout);
209 return intout;
210 }
211 if (PGFieldType == "boolean")
212 {
213 return (value.ToString() == "true");
214 }
215 if (PGFieldType == "timestamp with time zone")
216 {
217 return (DateTime)value;
218 }
219 if (PGFieldType == "timestamp without time zone")
220 {
221 return (DateTime)value;
222 }
223 return CreateParameterValue(value);
224 }
225
226 /// <summary>
227 /// Create a parameter for a command
228 /// </summary>
229 /// <param name="parameterName">Name of the parameter.</param>
230 /// <param name="parameterObject">parameter object.</param>
231 /// <returns></returns>
232 internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject)
233 {
234 return CreateParameter(parameterName, parameterObject, false);
235 }
236
237 /// <summary>
238 /// Creates the parameter for a command.
239 /// </summary>
240 /// <param name="parameterName">Name of the parameter.</param>
241 /// <param name="parameterObject">parameter object.</param>
242 /// <param name="parameterOut">if set to <c>true</c> parameter is a output parameter</param>
243 /// <returns></returns>
244 internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject, bool parameterOut)
245 {
246 //Tweak so we dont always have to add : sign
247 if (parameterName.StartsWith(":")) parameterName = parameterName.Replace(":","");
248
249 //HACK if object is null, it is turned into a string, there are no nullable type till now
250 if (parameterObject == null) parameterObject = "";
251
252 NpgsqlParameter parameter = new NpgsqlParameter(parameterName, DbtypeFromType(parameterObject.GetType()));
253
254 if (parameterOut)
255 {
256 parameter.Direction = ParameterDirection.Output;
257 }
258 else
259 {
260 parameter.Direction = ParameterDirection.Input;
261 parameter.Value = CreateParameterValue(parameterObject);
262 }
263
264 return parameter;
265 }
266
267 /// <summary>
268 /// Create a parameter with PGSQL schema type
269 /// </summary>
270 /// <param name="parameterName"></param>
271 /// <param name="parameterObject"></param>
272 /// <param name="PGFieldType"></param>
273 /// <returns></returns>
274 internal NpgsqlParameter CreateParameter(string parameterName, object parameterObject, string PGFieldType)
275 {
276 //Tweak so we dont always have to add : sign
277 if (parameterName.StartsWith(":")) parameterName = parameterName.Replace(":", "");
278
279 //HACK if object is null, it is turned into a string, there are no nullable type till now
280 if (parameterObject == null) parameterObject = "";
281
282 NpgsqlParameter parameter = new NpgsqlParameter(parameterName, DbtypeFromString(parameterObject.GetType(), PGFieldType));
283
284 parameter.Direction = ParameterDirection.Input;
285 parameter.Value = CreateParameterValue(parameterObject, PGFieldType);
286
287 return parameter;
288 }
289
290 /// <summary>
291 /// Checks if we need to do some migrations to the database
292 /// </summary>
293 /// <param name="migrationStore">migrationStore.</param>
294 public void CheckMigration(string migrationStore)
295 {
296 using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
297 {
298 connection.Open();
299 Assembly assem = GetType().Assembly;
300 PGSQLMigration migration = new PGSQLMigration(connection, assem, migrationStore);
301
302 migration.Update();
303 }
304 }
305
306 /// <summary>
307 /// Returns the version of this DB provider
308 /// </summary>
309 /// <returns>A string containing the DB provider</returns>
310 public string getVersion()
311 {
312 Module module = GetType().Module;
313 // string dllName = module.Assembly.ManifestModule.Name;
314 Version dllVersion = module.Assembly.GetName().Version;
315
316 return
317 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
318 dllVersion.Revision);
319 }
320 }
321}