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);
76 public static String GetBackendTypeNameFromDbType(DbType dbType)
78 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetBackendTypeNameFromDbType");
99 case DbType.AnsiString:
101 case DbType.DateTime:
108 throw new NpgsqlException(String.Format(resman.GetString("Exception_TypeNotSupported"), dbType));
113 public static Object ConvertBackendBytesToStytemType(Hashtable oidToNameMapping, Byte[] data, Encoding encoding, Int32 fieldValueSize, Int32 typeOid, Int32 typeModifier)
115 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendBytesToStytemType");
116 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
117 // when connecting because we don't have yet loaded typeMapping. The switch below
118 // crashes with NullPointerReference when it can't find the typeOid.
120 if (!oidToNameMapping.ContainsKey(typeOid))
123 switch ((DbType)oidToNameMapping[typeOid])
128 return BitConverter.ToBoolean(data, 0);
129 case DbType.DateTime:
130 return DateTime.MinValue.AddTicks(IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0)));
133 return IPAddress.NetworkToHostOrder(BitConverter.ToInt16(data, 0));
135 return IPAddress.NetworkToHostOrder(BitConverter.ToInt32(data, 0));
137 return IPAddress.NetworkToHostOrder(BitConverter.ToInt64(data, 0));
139 case DbType.AnsiString:
140 return encoding.GetString(data, 0, fieldValueSize);
142 throw new NpgsqlException("Type not supported in binary format");
149 public static String ConvertNpgsqlParameterToBackendStringValue(NpgsqlParameter parameter)
151 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertNpgsqlParameterToBackendStringValue");
153 if ((parameter.Value == DBNull.Value) || (parameter.Value == null))
156 switch(parameter.DbType)
159 return "'" + ConvertByteArrayToBytea((Byte[])parameter.Value) + "'";
164 return parameter.Value.ToString();
167 // To not have a value implicitly converted to float8, we add quotes.
168 return "'" + ((Single)parameter.Value).ToString(NumberFormatInfo.InvariantInfo) + "'";
171 return ((Double)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
174 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd") + "'";
176 case DbType.DateTime:
177 return "'" + ((DateTime)parameter.Value).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
180 return ((Decimal)parameter.Value).ToString(NumberFormatInfo.InvariantInfo);
183 case DbType.AnsiString:
184 case DbType.StringFixedLength:
185 return "'" + parameter.Value.ToString().Replace("'", "\\'") + "'";
188 return "'" + ((DateTime)parameter.Value).ToString("HH:mm:ss.ffff") + "'";
191 // This should not happen!
192 throw new NpgsqlException(String.Format(resman.GetString("Exception_TypeNotSupported"), parameter.DbType));
201 /// This method is responsible to convert the string received from the backend
202 /// to the corresponding NpgsqlType.
205 public static Object ConvertBackendStringToSystemType(Hashtable oidToNameMapping, String data, Int32 typeOid, Int32 typeModifier)
207 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertBackendStringToSystemType");
208 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
209 // when connecting because we don't have yet loaded typeMapping. The switch below
210 // crashes with NullPointerReference when it can't find the typeOid.
212 if (!oidToNameMapping.ContainsKey(typeOid))
215 switch ((DbType)oidToNameMapping[typeOid])
218 return ConvertByteAToByteArray(data);
221 return (data.ToLower() == "t" ? true : false);
224 return Single.Parse(data, NumberFormatInfo.InvariantInfo);
227 return Double.Parse(data, NumberFormatInfo.InvariantInfo);
230 return Int16.Parse(data);
232 return Int32.Parse(data);
235 return Int64.Parse(data);
238 // Got this manipulation of typemodifier from jdbc driver - file AbstractJdbc1ResultSetMetaData.java.html method getColumnDisplaySize
241 //Console.WriteLine("Numeric from server: {0} digitos.digitos {1}.{2}", data, (typeModifier >> 16) & 0xffff, typeModifier & 0xffff);
242 return Decimal.Parse(data, NumberFormatInfo.InvariantInfo);
246 case DbType.DateTime:
248 // Get the date time parsed in all expected formats for timestamp.
249 return DateTime.ParseExact(data,
250 new String[] {"yyyy-MM-dd HH:mm:ss.fff", "yyyy-MM-dd HH:mm:ss.ff", "yyyy-MM-dd HH:mm:ss.f", "yyyy-MM-dd HH:mm:ss"},
251 DateTimeFormatInfo.InvariantInfo,
252 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
255 return DateTime.ParseExact(data,
257 DateTimeFormatInfo.InvariantInfo,
258 DateTimeStyles.AllowWhiteSpaces);
262 return DateTime.ParseExact(data,
263 new String[] {"HH:mm:ss.ffff", "HH:mm:ss.fff", "HH:mm:ss.ff", "HH:mm:ss.f", "HH:mm:ss"},
264 DateTimeFormatInfo.InvariantInfo,
265 DateTimeStyles.NoCurrentDateDefault | DateTimeStyles.AllowWhiteSpaces);
268 case DbType.AnsiString:
271 throw new NpgsqlException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
280 /// This method gets a type oid and return the equivalent
285 public static Type GetSystemTypeFromTypeOid(Hashtable oidToNameMapping, Int32 typeOid)
287 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetSystemTypeFromTypeOid");
288 // This method gets a db type identifier and return the equivalent
291 //[TODO] Find a way to eliminate this checking. It is just used at bootstrap time
292 // when connecting because we don't have yet loaded typeMapping. The switch below
293 // crashes with NullPointerReference when it can't find the typeOid.
297 if (!oidToNameMapping.ContainsKey(typeOid))
298 return Type.GetType("System.String");
300 switch ((DbType)oidToNameMapping[typeOid])
303 return Type.GetType("System.Byte[]");
305 return Type.GetType("System.Boolean");
307 return Type.GetType("System.Int16");
309 return Type.GetType("System.Single");
311 return Type.GetType("System.Double");
313 return Type.GetType("System.Int32");
315 return Type.GetType("System.Int64");
317 return Type.GetType("System.Decimal");
318 case DbType.DateTime:
321 return Type.GetType("System.DateTime");
323 case DbType.AnsiString:
324 return Type.GetType("System.String");
326 throw new NpgsqlException(String.Format(resman.GetString("Exception_TypeNotSupported"), oidToNameMapping[typeOid]));
335 /// This method is responsible to send query to get the oid-to-name mapping.
336 /// This is needed as from one version to another, this mapping can be changed and
337 /// so we avoid hardcoding them.
339 public static Hashtable LoadTypesMapping(NpgsqlConnection conn)
341 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "LoadTypesMapping");
343 // [TODO] Verify another way to get higher concurrency.
344 lock(typeof(NpgsqlTypesHelper))
346 Hashtable oidToNameMapping = (Hashtable) _oidToNameMappings[conn.ServerVersion];
348 if (oidToNameMapping != null)
350 //conn.OidToNameMapping = oidToNameMapping;
351 return oidToNameMapping;
355 oidToNameMapping = new Hashtable();
356 //conn.OidToNameMapping = oidToNameMapping;
358 // Bootstrap value as the datareader below will use ConvertStringToNpgsqlType above.
359 //oidToNameMapping.Add(26, "oid");
361 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');", conn);
363 NpgsqlDataReader dr = command.ExecuteReader();
365 // Data was read. Clear the mapping from previous bootstrap value so we don't get
366 // exceptions trying to add duplicate key.
367 // oidToNameMapping.Clear();
371 // Add the key as a Int32 value so the switch in ConvertStringToNpgsqlType can use it
372 // in the search. If don't, the key is added as string and the switch doesn't work.
375 String typeName = (String) dr[1];
380 type = DbType.Boolean;
383 type = DbType.Binary;
389 type = DbType.Single;
392 type = DbType.Double;
404 type = DbType.Decimal;
410 type = DbType.DateTime;
413 type = DbType.String; // Default dbtype of the oid. Unsupported types will be returned as String.
418 oidToNameMapping.Add(Int32.Parse((String)dr[0]), type);
421 _oidToNameMappings.Add(conn.ServerVersion, oidToNameMapping);
422 return oidToNameMapping;
430 private static Byte[] ConvertByteAToByteArray(String byteA)
432 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteAToByteArray");
433 Int32 octalValue = 0;
434 Int32 byteAPosition = 0;
436 Int32 byteAStringLength = byteA.Length;
438 MemoryStream ms = new MemoryStream();
440 while (byteAPosition < byteAStringLength)
444 // The IsDigit is necessary in case we receive a \ as the octal value and not
445 // as the indicator of a following octal value in decimal format.
447 if (byteA[byteAPosition] == '\\')
449 if (byteAPosition + 1 == byteAStringLength)
454 else if (Char.IsDigit(byteA[byteAPosition + 1]))
456 octalValue = (Byte.Parse(byteA[byteAPosition + 1].ToString()) << 6);
457 octalValue |= (Byte.Parse(byteA[byteAPosition + 2].ToString()) << 3);
458 octalValue |= Byte.Parse(byteA[byteAPosition + 3].ToString());
471 octalValue = (Byte)byteA[byteAPosition];
476 ms.WriteByte((Byte)octalValue);
485 private static String ConvertByteArrayToBytea(Byte[] byteArray)
487 NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ConvertByteArrayToBytea");
488 Int32 len = byteArray.Length;
489 Char[] res = new Char [len * 5];
490 for (Int32 i = 0; i <len; i++)
493 res [(i*5)+1] = '\\';
494 res [(i*5)+2] = (Char) (((byteArray[i] & 0xC0) >> 6) + '0');
495 res [(i*5)+3] = (Char) (((byteArray[i] & 0x38) >> 3) + '0');
496 res [(i*5)+4] = (Char) ((byteArray[i] & 0x07) + '0');
499 return new String (res);