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;
38 /// This class contains helper methods for type conversion between
39 /// the .Net type system and postgresql.
44 /*internal struct NpgsqlTypeMapping
46 public String _backendTypeName;
47 public Type _frameworkType;
48 public Int32 _typeOid;
49 public NpgsqlDbType _npgsqlDbType;
51 public NpgsqlTypeMapping(String backendTypeName, Type frameworkType, Int32 typeOid, NpgsqlDbType npgsqlDbType)
53 _backendTypeName = backendTypeName;
54 _frameworkType = frameworkType;
56 _npgsqlDbType = npgsqlDbType;
62 internal class NpgsqlTypesHelper
65 private static Hashtable _oidToNameMappings = new Hashtable();
67 // Logging related values
68 private static readonly String CLASSNAME = "NpgsqlDataReader";
69 private static ResourceManager resman = new ResourceManager(typeof(NpgsqlTypesHelper));
71 // From include/utils/datetime.h. Thanks to Carlos Guzman Alvarez
72 private static readonly DateTime postgresEpoch = new DateTime(2000, 1, 1);
74 private static readonly string[] DateFormats = new String[]
79 private static readonly string[] TimeFormats = new String[]
97 private static readonly string[] DateTimeFormats = new String[]
99 "yyyy-MM-dd HH:mm:ss.ffffff",
100 "yyyy-MM-dd HH:mm:ss.fffff",
101 "yyyy-MM-dd HH:mm:ss.ffff",
102 "yyyy-MM-dd HH:mm:ss.fff",
103 "yyyy-MM-dd HH:mm:ss.ff",
104 "yyyy-MM-dd HH:mm:ss.f",
105 "yyyy-MM-dd HH:mm:ss",
106 "yyyy-MM-dd HH:mm:ss.ffffffzz",
107 "yyyy-MM-dd HH:mm:ss.fffffzz",
108 "yyyy-MM-dd HH:mm:ss.ffffzz",
109 "yyyy-MM-dd HH:mm:ss.fffzz",
110 "yyyy-MM-dd HH:mm:ss.ffzz",
111 "yyyy-MM-dd HH:mm:ss.fzz",
112 "yyyy-MM-dd HH:mm:sszz"
115 public static String GetBackendTypeNameFromDbType(DbType dbType)
117 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetBackendTypeNameFromDbType");
138 case DbType.AnsiString:
140 case DbType.DateTime:
147 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), dbType));
152 public static Object ConvertBackendBytesToStytemType(Hashtable oidToNameMapping, Byte[] data, Encoding encoding, Int32 fieldValueSize, Int32 typeOid, Int32 typeModifier)
154 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendBytesToStytemType");
155 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
156 // when connecting because we don't have yet loaded typeMapping. The switch below
157 // crashes with NullPointerReference when it can't find the typeOid.
159 if (!oidToNameMapping.ContainsKey(typeOid))
162 switch ((DbType)oidToNameMapping[typeOid])
167 return BitConverter.ToBoolean(data, 0);
168 case DbType.DateTime:
169 return DateTime.MinValue.AddTicks(IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0)));
172 return IPAddress.NetworkToHostOrder(BitConverter.ToInt16(data, 0));
174 return IPAddress.NetworkToHostOrder(BitConverter.ToInt32(data, 0));
176 return IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0));
178 case DbType.AnsiString:
179 case DbType.StringFixedLength:
180 return encoding.GetString(data, 0, fieldValueSize);
182 throw new InvalidCastException("Type not supported in binary format");
189 public static String ConvertNpgsqlParameterToBackendStringValue(NpgsqlParameter parameter, Boolean QuoteStrings)
192 // glenebob@nwlink.com 05/20/2004
193 // bool QuoteString is a bit of a hack.
194 // When using the version 3 extended query support, we do not need to do quoting of parameters.
195 // The backend handles that properly.
197 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertNpgsqlParameterToBackendStringValue");
199 if ((parameter.Value == DBNull.Value) || (parameter.Value == null))
202 switch(parameter.DbType)
206 return "'" + ConvertByteArrayToBytea((Byte[])parameter.Value) + "'";
208 return ConvertByteArrayToBytea((Byte[])parameter.Value);
215 return parameter.Value.ToString();
218 // To not have a value implicitly converted to float8, we add quotes.
220 return "'" + ((Single)parameter.Value).ToString(NumberFormatInfo.InvariantInfo) + "'";
222 return ((Single)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
226 return ((Double)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
230 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd") + "'";
232 return ((DateTime)parameter.Value).ToString("yyyy-MM-dd");
235 case DbType.DateTime:
237 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
239 return ((DateTime)parameter.Value).ToString("yyyy-MM-dd HH:mm:ss.fff");
243 return ((Decimal)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
246 case DbType.AnsiString:
247 case DbType.StringFixedLength:
249 return "'" + parameter.Value.ToString().Replace("'", "\\'") + "'";
251 return parameter.Value.ToString();
256 return "'" + ((DateTime)parameter.Value).ToString("HH:mm:ss.ffff") + "'";
258 return ((DateTime)parameter.Value).ToString("HH:mm:ss.ffff");
262 // This should not happen!
263 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), parameter.DbType));
272 /// This method is responsible to convert the string received from the backend
273 /// to the corresponding NpgsqlType.
276 public static Object ConvertBackendStringToSystemType(Hashtable oidToNameMapping, String data, Int32 typeOid, Int32 typeModifier)
278 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendStringToSystemType");
279 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
280 // when connecting because we don't have yet loaded typeMapping. The switch below
281 // crashes with NullPointerReference when it can't find the typeOid.
283 if (!oidToNameMapping.ContainsKey(typeOid))
286 switch ((DbType)oidToNameMapping[typeOid])
289 return ConvertByteAToByteArray(data);
292 return (data.ToLower() == "t" ? true : false);
295 return Single.Parse(data, NumberFormatInfo.InvariantInfo);
298 return Double.Parse(data, NumberFormatInfo.InvariantInfo);
301 return Int16.Parse(data);
303 return Int32.Parse(data);
306 return Int64.Parse(data);
309 // Got this manipulation of typemodifier from jdbc driver - file AbstractJdbc1ResultSetMetaData.java.html method getColumnDisplaySize
312 //Console.WriteLine("Numeric from server: {0} digitos.digitos {1}.{2}", data, (typeModifier >> 16) & 0xffff, typeModifier & 0xffff);
313 return Decimal.Parse(data, NumberFormatInfo.InvariantInfo);
317 case DbType.DateTime:
319 // Get the date time parsed in all expected formats for timestamp.
320 return DateTime.ParseExact(data,
322 DateTimeFormatInfo.InvariantInfo,
323 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
326 return DateTime.ParseExact(data,
328 DateTimeFormatInfo.InvariantInfo,
329 DateTimeStyles.AllowWhiteSpaces);
333 return DateTime.ParseExact(data,
335 DateTimeFormatInfo.InvariantInfo,
336 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
339 case DbType.AnsiString:
340 case DbType.StringFixedLength:
343 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
352 /// This method gets a type oid and return the equivalent
357 public static Type GetSystemTypeFromTypeOid(Hashtable oidToNameMapping, Int32 typeOid)
359 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetSystemTypeFromTypeOid");
360 // This method gets a db type identifier and return the equivalent
363 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
364 // when connecting because we don't have yet loaded typeMapping. The switch below
365 // crashes with NullPointerReference when it can't find the typeOid.
369 if (!oidToNameMapping.ContainsKey(typeOid))
370 return Type.GetType("System.String");
372 switch ((DbType)oidToNameMapping[typeOid])
375 return Type.GetType("System.Byte[]");
377 return Type.GetType("System.Boolean");
379 return Type.GetType("System.Int16");
381 return Type.GetType("System.Single");
383 return Type.GetType("System.Double");
385 return Type.GetType("System.Int32");
387 return Type.GetType("System.Int64");
389 return Type.GetType("System.Decimal");
390 case DbType.DateTime:
393 return Type.GetType("System.DateTime");
395 case DbType.AnsiString:
396 case DbType.StringFixedLength:
397 return Type.GetType("System.String");
399 throw new InvalidCastException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
408 /// This method is responsible to send query to get the oid-to-name mapping.
409 /// This is needed as from one version to another, this mapping can be changed and
410 /// so we avoid hardcoding them.
412 public static Hashtable LoadTypesMapping(NpgsqlConnection conn)
414 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "LoadTypesMapping");
416 // [TODO] Verify another way to get higher concurrency.
417 lock(typeof(NpgsqlTypesHelper))
419 Hashtable oidToNameMapping = (Hashtable) _oidToNameMappings[conn.ServerVersion];
421 if (oidToNameMapping != null)
423 //conn.OidToNameMapping = oidToNameMapping;
424 return oidToNameMapping;
428 oidToNameMapping = new Hashtable();
429 //conn.OidToNameMapping = oidToNameMapping;
431 // Bootstrap value as the datareader below will use ConvertStringToNpgsqlType above.
432 //oidToNameMapping.Add(26, "oid");
434 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);
436 NpgsqlDataReader dr = command.ExecuteReader();
438 // Data was read. Clear the mapping from previous bootstrap value so we don't get
439 // exceptions trying to add duplicate key.
440 // oidToNameMapping.Clear();
444 // Add the key as a Int32 value so the switch in ConvertStringToNpgsqlType can use it
445 // in the search. If don't, the key is added as string and the switch doesn't work.
448 String typeName = (String) dr[1];
453 type = DbType.Boolean;
456 type = DbType.Binary;
462 type = DbType.Single;
465 type = DbType.Double;
477 type = DbType.Decimal;
485 type = DbType.DateTime;
488 type = DbType.String; // Default dbtype of the oid. Unsupported types will be returned as String.
493 oidToNameMapping.Add(Int32.Parse((String)dr[0]), type);
496 _oidToNameMappings.Add(conn.ServerVersion, oidToNameMapping);
497 return oidToNameMapping;
505 private static Byte[] ConvertByteAToByteArray(String byteA)
507 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteAToByteArray");
508 Int32 octalValue = 0;
509 Int32 byteAPosition = 0;
511 Int32 byteAStringLength = byteA.Length;
513 MemoryStream ms = new MemoryStream();
515 while (byteAPosition < byteAStringLength)
519 // The IsDigit is necessary in case we receive a \ as the octal value and not
520 // as the indicator of a following octal value in decimal format.
522 if (byteA[byteAPosition] == '\\')
524 if (byteAPosition + 1 == byteAStringLength)
529 else if (Char.IsDigit(byteA[byteAPosition + 1]))
531 octalValue = (Byte.Parse(byteA[byteAPosition + 1].ToString()) << 6);
532 octalValue |= (Byte.Parse(byteA[byteAPosition + 2].ToString()) << 3);
533 octalValue |= Byte.Parse(byteA[byteAPosition + 3].ToString());
546 octalValue = (Byte)byteA[byteAPosition];
551 ms.WriteByte((Byte)octalValue);
560 private static String ConvertByteArrayToBytea(Byte[] byteArray)
562 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteArrayToBytea");
563 int len = byteArray.Length;
564 char[] res = new char[len * 5];
565 for (int i=0, o=0; i<len; ++i, o += 5)
567 byte item = byteArray[i];
568 res[o] = res[o + 1] = '\\';
569 res[o + 2] = (char)('0' + (7 & (item >> 6)));
570 res[o + 3] = (char)('0' + (7 & (item >> 3)));
571 res[o + 4] = (char)('0' + (7 & item));
573 return new String(res);