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 Exception(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 Exception("Type not supported in binary format");
189 public static String ConvertNpgsqlParameterToBackendStringValue(NpgsqlParameter parameter)
191 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertNpgsqlParameterToBackendStringValue");
193 if ((parameter.Value == DBNull.Value) || (parameter.Value == null))
196 switch(parameter.DbType)
199 return "'" + ConvertByteArrayToBytea((Byte[])parameter.Value) + "'";
204 return parameter.Value.ToString();
207 // To not have a value implicitly converted to float8, we add quotes.
208 return "'" + ((Single)parameter.Value).ToString(NumberFormatInfo.InvariantInfo) + "'";
211 return ((Double)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
214 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd") + "'";
216 case DbType.DateTime:
217 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
220 return ((Decimal)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
223 case DbType.AnsiString:
224 case DbType.StringFixedLength:
225 return "'" + parameter.Value.ToString().Replace("'", "\\'") + "'";
228 return "'" + ((DateTime)parameter.Value).ToString("HH:mm:ss.ffff") + "'";
231 // This should not happen!
232 throw new Exception(String.Format(resman.GetString("Exception_TypeNotSupported"), parameter.DbType));
241 /// This method is responsible to convert the string received from the backend
242 /// to the corresponding NpgsqlType.
245 public static Object ConvertBackendStringToSystemType(Hashtable oidToNameMapping, String data, Int32 typeOid, Int32 typeModifier)
247 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendStringToSystemType");
248 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
249 // when connecting because we don't have yet loaded typeMapping. The switch below
250 // crashes with NullPointerReference when it can't find the typeOid.
252 if (!oidToNameMapping.ContainsKey(typeOid))
255 switch ((DbType)oidToNameMapping[typeOid])
258 return ConvertByteAToByteArray(data);
261 return (data.ToLower() == "t" ? true : false);
264 return Single.Parse(data, NumberFormatInfo.InvariantInfo);
267 return Double.Parse(data, NumberFormatInfo.InvariantInfo);
270 return Int16.Parse(data);
272 return Int32.Parse(data);
275 return Int64.Parse(data);
278 // Got this manipulation of typemodifier from jdbc driver - file AbstractJdbc1ResultSetMetaData.java.html method getColumnDisplaySize
281 //Console.WriteLine("Numeric from server: {0} digitos.digitos {1}.{2}", data, (typeModifier >> 16) & 0xffff, typeModifier & 0xffff);
282 return Decimal.Parse(data, NumberFormatInfo.InvariantInfo);
286 case DbType.DateTime:
288 // Get the date time parsed in all expected formats for timestamp.
289 return DateTime.ParseExact(data,
291 DateTimeFormatInfo.InvariantInfo,
292 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
295 return DateTime.ParseExact(data,
297 DateTimeFormatInfo.InvariantInfo,
298 DateTimeStyles.AllowWhiteSpaces);
302 return DateTime.ParseExact(data,
304 DateTimeFormatInfo.InvariantInfo,
305 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
308 case DbType.AnsiString:
309 case DbType.StringFixedLength:
312 throw new Exception(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
321 /// This method gets a type oid and return the equivalent
326 public static Type GetSystemTypeFromTypeOid(Hashtable oidToNameMapping, Int32 typeOid)
328 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetSystemTypeFromTypeOid");
329 // This method gets a db type identifier and return the equivalent
332 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
333 // when connecting because we don't have yet loaded typeMapping. The switch below
334 // crashes with NullPointerReference when it can't find the typeOid.
338 if (!oidToNameMapping.ContainsKey(typeOid))
339 return Type.GetType("System.String");
341 switch ((DbType)oidToNameMapping[typeOid])
344 return Type.GetType("System.Byte[]");
346 return Type.GetType("System.Boolean");
348 return Type.GetType("System.Int16");
350 return Type.GetType("System.Single");
352 return Type.GetType("System.Double");
354 return Type.GetType("System.Int32");
356 return Type.GetType("System.Int64");
358 return Type.GetType("System.Decimal");
359 case DbType.DateTime:
362 return Type.GetType("System.DateTime");
364 case DbType.AnsiString:
365 case DbType.StringFixedLength:
366 return Type.GetType("System.String");
368 throw new Exception(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
377 /// This method is responsible to send query to get the oid-to-name mapping.
378 /// This is needed as from one version to another, this mapping can be changed and
379 /// so we avoid hardcoding them.
381 public static Hashtable LoadTypesMapping(NpgsqlConnection conn)
383 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "LoadTypesMapping");
385 // [TODO] Verify another way to get higher concurrency.
386 lock(typeof(NpgsqlTypesHelper))
388 Hashtable oidToNameMapping = (Hashtable) _oidToNameMappings[conn.ServerVersion];
390 if (oidToNameMapping != null)
392 //conn.OidToNameMapping = oidToNameMapping;
393 return oidToNameMapping;
397 oidToNameMapping = new Hashtable();
398 //conn.OidToNameMapping = oidToNameMapping;
400 // Bootstrap value as the datareader below will use ConvertStringToNpgsqlType above.
401 //oidToNameMapping.Add(26, "oid");
403 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);
405 NpgsqlDataReader dr = command.ExecuteReader();
407 // Data was read. Clear the mapping from previous bootstrap value so we don't get
408 // exceptions trying to add duplicate key.
409 // oidToNameMapping.Clear();
413 // Add the key as a Int32 value so the switch in ConvertStringToNpgsqlType can use it
414 // in the search. If don't, the key is added as string and the switch doesn't work.
417 String typeName = (String) dr[1];
422 type = DbType.Boolean;
425 type = DbType.Binary;
431 type = DbType.Single;
434 type = DbType.Double;
446 type = DbType.Decimal;
454 type = DbType.DateTime;
457 type = DbType.String; // Default dbtype of the oid. Unsupported types will be returned as String.
462 oidToNameMapping.Add(Int32.Parse((String)dr[0]), type);
465 _oidToNameMappings.Add(conn.ServerVersion, oidToNameMapping);
466 return oidToNameMapping;
474 private static Byte[] ConvertByteAToByteArray(String byteA)
476 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteAToByteArray");
477 Int32 octalValue = 0;
478 Int32 byteAPosition = 0;
480 Int32 byteAStringLength = byteA.Length;
482 MemoryStream ms = new MemoryStream();
484 while (byteAPosition < byteAStringLength)
488 // The IsDigit is necessary in case we receive a \ as the octal value and not
489 // as the indicator of a following octal value in decimal format.
491 if (byteA[byteAPosition] == '\\')
493 if (byteAPosition + 1 == byteAStringLength)
498 else if (Char.IsDigit(byteA[byteAPosition + 1]))
500 octalValue = (Byte.Parse(byteA[byteAPosition + 1].ToString()) << 6);
501 octalValue |= (Byte.Parse(byteA[byteAPosition + 2].ToString()) << 3);
502 octalValue |= Byte.Parse(byteA[byteAPosition + 3].ToString());
515 octalValue = (Byte)byteA[byteAPosition];
520 ms.WriteByte((Byte)octalValue);
529 private static String ConvertByteArrayToBytea(Byte[] byteArray)
531 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteArrayToBytea");
532 int len = byteArray.Length;
533 char[] res = new char[len * 5];
534 for (int i=0, o=0; i<len; ++i, o += 5)
536 byte item = byteArray[i];
537 res[o] = res[o + 1] = '\\';
538 res[o + 2] = (char)('0' + (7 & (item >> 6)));
539 res[o + 3] = (char)('0' + (7 & (item >> 3)));
540 res[o + 4] = (char)('0' + (7 & item));
542 return new String(res);