2 // NpgsqlTypes.NpgsqlTypesHelper.cs
5 // Francisco Jr. (fxjrlists@yahoo.com.br)
7 // Copyright (C) 2002 The Npgsql Development Team
8 // npgsql-general@gborg.postgresql.org
9 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
11 // This library is free software; you can redistribute it and/or
12 // modify it under the terms of the GNU Lesser General Public
13 // License as published by the Free Software Foundation; either
14 // version 2.1 of the License, or (at your option) any later version.
16 // This library is distributed in the hope that it will be useful,
17 // but WITHOUT ANY WARRANTY; without even the implied warranty of
18 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
19 // Lesser General Public License for more details.
21 // You should have received a copy of the GNU Lesser General Public
22 // License along with this library; if not, write to the Free Software
23 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
26 using System.Collections;
27 using System.Globalization;
33 using System.Resources;
39 /*internal struct NpgsqlTypeMapping
41 public String _backendTypeName;
42 public Type _frameworkType;
43 public Int32 _typeOid;
44 public NpgsqlDbType _npgsqlDbType;
46 public NpgsqlTypeMapping(String backendTypeName, Type frameworkType, Int32 typeOid, NpgsqlDbType npgsqlDbType)
48 _backendTypeName = backendTypeName;
49 _frameworkType = frameworkType;
51 _npgsqlDbType = npgsqlDbType;
58 /// This class contains helper methods for type conversion between
59 /// the .Net type system and postgresql.
61 internal class NpgsqlTypesHelper
64 private static Hashtable _oidToNameMappings = new Hashtable();
66 // Logging related values
67 private static readonly String CLASSNAME = "NpgsqlDataReader";
68 private static ResourceManager resman = new ResourceManager(typeof(NpgsqlTypesHelper));
70 // From include/utils/datetime.h. Thanks to Carlos Guzman Alvarez
71 private static readonly DateTime postgresEpoch = new DateTime(2000, 1, 1);
73 private static readonly string[] DateFormats = new String[]
78 private static readonly string[] TimeFormats = new String[]
96 private static readonly string[] DateTimeFormats = new String[]
98 "yyyy-MM-dd HH:mm:ss.ffffff",
99 "yyyy-MM-dd HH:mm:ss.fffff",
100 "yyyy-MM-dd HH:mm:ss.ffff",
101 "yyyy-MM-dd HH:mm:ss.fff",
102 "yyyy-MM-dd HH:mm:ss.ff",
103 "yyyy-MM-dd HH:mm:ss.f",
104 "yyyy-MM-dd HH:mm:ss",
105 "yyyy-MM-dd HH:mm:ss.ffffffzz",
106 "yyyy-MM-dd HH:mm:ss.fffffzz",
107 "yyyy-MM-dd HH:mm:ss.ffffzz",
108 "yyyy-MM-dd HH:mm:ss.fffzz",
109 "yyyy-MM-dd HH:mm:ss.ffzz",
110 "yyyy-MM-dd HH:mm:ss.fzz",
111 "yyyy-MM-dd HH:mm:sszz"
114 public static String GetBackendTypeNameFromDbType(DbType dbType)
116 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetBackendTypeNameFromDbType");
137 case DbType.AnsiString:
139 case DbType.DateTime:
146 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), dbType));
151 public static Object ConvertBackendBytesToStytemType(Hashtable oidToNameMapping, Byte[] data, Encoding encoding, Int32 fieldValueSize, Int32 typeOid, Int32 typeModifier)
153 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendBytesToStytemType");
154 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
155 // when connecting because we don't have yet loaded typeMapping. The switch below
156 // crashes with NullPointerReference when it can't find the typeOid.
158 if (!oidToNameMapping.ContainsKey(typeOid))
161 switch ((DbType)oidToNameMapping[typeOid])
166 return BitConverter.ToBoolean(data, 0);
167 case DbType.DateTime:
168 return DateTime.MinValue.AddTicks(IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0)));
171 return IPAddress.NetworkToHostOrder(BitConverter.ToInt16(data, 0));
173 return IPAddress.NetworkToHostOrder(BitConverter.ToInt32(data, 0));
175 return IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0));
177 case DbType.AnsiString:
178 case DbType.StringFixedLength:
179 return encoding.GetString(data, 0, fieldValueSize);
181 throw new InvalidCastException("Type not supported in binary format");
187 private static string QuoteString(bool Quote, string S)
190 return string.Format("'{0}'", S);
196 public static String ConvertNpgsqlParameterToBackendStringValue(NpgsqlParameter parameter, Boolean QuoteStrings)
199 // glenebob@nwlink.com 05/20/2004
200 // bool QuoteString is a bit of a hack.
201 // When using the version 3 extended query support, we do not need to do quoting of parameters.
202 // The backend handles that properly.
204 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertNpgsqlParameterToBackendStringValue");
206 if ((parameter.Value == DBNull.Value) || (parameter.Value == null))
209 switch(parameter.DbType)
212 return QuoteString(QuoteStrings, ConvertByteArrayToBytea((Byte[])parameter.Value));
215 return ((bool)parameter.Value) ? "TRUE" : "FALSE";
220 return parameter.Value.ToString();
223 // To not have a value implicitly converted to float8, we add quotes.
224 return QuoteString(QuoteStrings, ((Single)parameter.Value).ToString(NumberFormatInfo.InvariantInfo));
227 return ((Double)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
230 return QuoteString(QuoteStrings, ((DateTime)parameter.Value).ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo));
233 return QuoteString(QuoteStrings, ((DateTime)parameter.Value).ToString("HH:mm:ss.fff", DateTimeFormatInfo.InvariantInfo));
235 case DbType.DateTime:
236 return QuoteString(QuoteStrings, ((DateTime)parameter.Value).ToString("yyyy-MM-dd HH:mm:ss.fff", DateTimeFormatInfo.InvariantInfo));
239 return ((Decimal)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
242 case DbType.AnsiString:
243 case DbType.StringFixedLength:
244 return QuoteString(QuoteStrings, parameter.Value.ToString().Replace("'", "''"));
247 // This should not happen!
248 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), parameter.DbType));
256 /// This method is responsible to convert the string received from the backend
257 /// to the corresponding NpgsqlType.
260 public static Object ConvertBackendStringToSystemType(Hashtable oidToNameMapping, String data, Int32 typeOid, Int32 typeModifier)
262 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendStringToSystemType");
263 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
264 // when connecting because we don't have yet loaded typeMapping. The switch below
265 // crashes with NullPointerReference when it can't find the typeOid.
267 if (!oidToNameMapping.ContainsKey(typeOid))
270 switch ((DbType)oidToNameMapping[typeOid])
273 return ConvertByteAToByteArray(data);
276 return (data.ToLower() == "t" ? true : false);
279 return Single.Parse(data, NumberFormatInfo.InvariantInfo);
282 return Double.Parse(data, NumberFormatInfo.InvariantInfo);
285 return Int16.Parse(data);
287 return Int32.Parse(data);
290 return Int64.Parse(data);
293 // Got this manipulation of typemodifier from jdbc driver - file AbstractJdbc1ResultSetMetaData.java.html method getColumnDisplaySize
296 //Console.WriteLine("Numeric from server: {0} digitos.digitos {1}.{2}", data, (typeModifier >> 16) & 0xffff, typeModifier & 0xffff);
297 return Decimal.Parse(data, NumberFormatInfo.InvariantInfo);
301 case DbType.DateTime:
303 // Get the date time parsed in all expected formats for timestamp.
304 return DateTime.ParseExact(data,
306 DateTimeFormatInfo.InvariantInfo,
307 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
310 return DateTime.ParseExact(data,
312 DateTimeFormatInfo.InvariantInfo,
313 DateTimeStyles.AllowWhiteSpaces);
317 return DateTime.ParseExact(data,
319 DateTimeFormatInfo.InvariantInfo,
320 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
323 case DbType.AnsiString:
324 case DbType.StringFixedLength:
327 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
336 /// This method gets a type oid and return the equivalent
341 public static Type GetSystemTypeFromTypeOid(Hashtable oidToNameMapping, Int32 typeOid)
343 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetSystemTypeFromTypeOid");
344 // This method gets a db type identifier and return the equivalent
347 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
348 // when connecting because we don't have yet loaded typeMapping. The switch below
349 // crashes with NullPointerReference when it can't find the typeOid.
353 if (!oidToNameMapping.ContainsKey(typeOid))
354 return Type.GetType("System.String");
356 switch ((DbType)oidToNameMapping[typeOid])
359 return Type.GetType("System.Byte[]");
361 return Type.GetType("System.Boolean");
363 return Type.GetType("System.Int16");
365 return Type.GetType("System.Single");
367 return Type.GetType("System.Double");
369 return Type.GetType("System.Int32");
371 return Type.GetType("System.Int64");
373 return Type.GetType("System.Decimal");
374 case DbType.DateTime:
377 return Type.GetType("System.DateTime");
379 case DbType.AnsiString:
380 case DbType.StringFixedLength:
381 return Type.GetType("System.String");
383 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
392 /// This method is responsible to send query to get the oid-to-name mapping.
393 /// This is needed as from one version to another, this mapping can be changed and
394 /// so we avoid hardcoding them.
396 public static Hashtable LoadTypesMapping(NpgsqlConnection conn)
398 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "LoadTypesMapping");
400 // [TODO] Verify another way to get higher concurrency.
401 lock(typeof(NpgsqlTypesHelper))
403 Hashtable oidToNameMapping = (Hashtable) _oidToNameMappings[conn.ServerVersion];
405 if (oidToNameMapping != null)
407 //conn.OidToNameMapping = oidToNameMapping;
408 return oidToNameMapping;
412 oidToNameMapping = new Hashtable();
413 //conn.OidToNameMapping = oidToNameMapping;
415 // Bootstrap value as the datareader below will use ConvertStringToNpgsqlType above.
416 //oidToNameMapping.Add(26, "oid");
418 NpgsqlCommand command = new NpgsqlCommand("select oid, typname from pg_type where typname in ('bool', 'bytea', 'date', 'float4', 'float8', 'int2', 'int4', 'int8', 'numeric', 'text', 'time', 'timestamp', 'timestamptz', 'timetz');", conn);
420 NpgsqlDataReader dr = command.ExecuteReader();
422 // Data was read. Clear the mapping from previous bootstrap value so we don't get
423 // exceptions trying to add duplicate key.
424 // oidToNameMapping.Clear();
428 // Add the key as a Int32 value so the switch in ConvertStringToNpgsqlType can use it
429 // in the search. If don't, the key is added as string and the switch doesn't work.
432 String typeName = (String) dr[1];
437 type = DbType.Boolean;
440 type = DbType.Binary;
446 type = DbType.Single;
449 type = DbType.Double;
461 type = DbType.Decimal;
469 type = DbType.DateTime;
472 type = DbType.String; // Default dbtype of the oid. Unsupported types will be returned as String.
477 oidToNameMapping.Add(Int32.Parse((String)dr[0]), type);
480 _oidToNameMappings.Add(conn.ServerVersion, oidToNameMapping);
481 return oidToNameMapping;
489 private static Byte[] ConvertByteAToByteArray(String byteA)
491 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteAToByteArray");
492 Int32 octalValue = 0;
493 Int32 byteAPosition = 0;
495 Int32 byteAStringLength = byteA.Length;
497 MemoryStream ms = new MemoryStream();
499 while (byteAPosition < byteAStringLength)
503 // The IsDigit is necessary in case we receive a \ as the octal value and not
504 // as the indicator of a following octal value in decimal format.
506 if (byteA[byteAPosition] == '\\')
508 if (byteAPosition + 1 == byteAStringLength)
513 else if (Char.IsDigit(byteA[byteAPosition + 1]))
515 octalValue = (Byte.Parse(byteA[byteAPosition + 1].ToString()) << 6);
516 octalValue |= (Byte.Parse(byteA[byteAPosition + 2].ToString()) << 3);
517 octalValue |= Byte.Parse(byteA[byteAPosition + 3].ToString());
530 octalValue = (Byte)byteA[byteAPosition];
535 ms.WriteByte((Byte)octalValue);
544 private static String ConvertByteArrayToBytea(Byte[] byteArray)
546 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteArrayToBytea");
547 int len = byteArray.Length;
548 char[] res = new char[len * 5];
549 for (int i=0, o=0; i<len; ++i, o += 5)
551 byte item = byteArray[i];
552 res[o] = res[o + 1] = '\\';
553 res[o + 2] = (char)('0' + (7 & (item >> 6)));
554 res[o + 3] = (char)('0' + (7 & (item >> 3)));
555 res[o + 4] = (char)('0' + (7 & item));
557 return new String(res);