1 // NpgsqlTypes.NpgsqlTypesHelper.cs
4 // Glen Parker <glenebob@nwlink.com>
6 // Copyright (C) 2004 The Npgsql Development Team
7 // npgsql-general@gborg.postgresql.org
8 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
10 // Permission to use, copy, modify, and distribute this software and its
11 // documentation for any purpose, without fee, and without a written
12 // agreement is hereby granted, provided that the above copyright notice
13 // and this paragraph and the following two paragraphs appear in all copies.
15 // IN NO EVENT SHALL THE NPGSQL DEVELOPMENT TEAM BE LIABLE TO ANY PARTY
16 // FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
17 // INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
18 // DOCUMENTATION, EVEN IF THE NPGSQL DEVELOPMENT TEAM HAS BEEN ADVISED OF
19 // THE POSSIBILITY OF SUCH DAMAGE.
21 // THE NPGSQL DEVELOPMENT TEAM SPECIFICALLY DISCLAIMS ANY WARRANTIES,
22 // INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
23 // AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
24 // ON AN "AS IS" BASIS, AND THE NPGSQL DEVELOPMENT TEAM HAS NO OBLIGATIONS
25 // TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
27 // This file provides data type converters between PostgreSQL representations
31 using System
.Collections
.Generic
;
32 using System
.Globalization
;
36 using System
.Text
.RegularExpressions
;
41 /// Provide event handlers to convert all native supported basic data types from their backend
42 /// text representation to a .NET object.
44 internal abstract class BasicBackendToNativeTypeConverter
46 private static readonly String
[] DateFormats
= new String
[] { "yyyy-MM-dd", }
;
48 private static readonly String
[] TimeFormats
=
51 "HH:mm:ss.ffffff", "HH:mm:ss", "HH:mm:ss.ffffffzz", "HH:mm:sszz", "HH:mm:ss.fffff", "HH:mm:ss.ffff", "HH:mm:ss.fff"
52 , "HH:mm:ss.ff", "HH:mm:ss.f", "HH:mm:ss.fffffzz", "HH:mm:ss.ffffzz", "HH:mm:ss.fffzz", "HH:mm:ss.ffzz",
56 private static readonly String
[] DateTimeFormats
=
59 "yyyy-MM-dd HH:mm:ss.ffffff", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.ffffffzz", "yyyy-MM-dd HH:mm:sszz",
60 "yyyy-MM-dd HH:mm:ss.fffff", "yyyy-MM-dd HH:mm:ss.ffff", "yyyy-MM-dd HH:mm:ss.fff", "yyyy-MM-dd HH:mm:ss.ff",
61 "yyyy-MM-dd HH:mm:ss.f", "yyyy-MM-dd HH:mm:ss.fffffzz", "yyyy-MM-dd HH:mm:ss.ffffzz", "yyyy-MM-dd HH:mm:ss.fffzz",
62 "yyyy-MM-dd HH:mm:ss.ffzz", "yyyy-MM-dd HH:mm:ss.fzz",
68 internal static Object
ToBinary(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
71 Int32 byteAPosition
= 0;
72 Int32 byteAStringLength
= BackendData
.Length
;
73 MemoryStream ms
= new MemoryStream();
75 while (byteAPosition
< byteAStringLength
)
77 // The IsDigit is necessary in case we receive a \ as the octal value and not
78 // as the indicator of a following octal value in decimal format.
80 if (BackendData
[byteAPosition
] == '\\')
82 if (byteAPosition
+ 1 == byteAStringLength
)
87 else if (Char
.IsDigit(BackendData
[byteAPosition
+ 1]))
89 octalValue
= Convert
.ToByte(BackendData
.Substring(byteAPosition
+ 1, 3), 8);
90 //octalValue = (Byte.Parse(BackendData[byteAPosition + 1].ToString()) << 6);
91 //octalValue |= (Byte.Parse(BackendData[byteAPosition + 2].ToString()) << 3);
92 //octalValue |= Byte.Parse(BackendData[byteAPosition + 3].ToString());
103 octalValue
= (Byte
)BackendData
[byteAPosition
];
108 ms
.WriteByte((Byte
)octalValue
);
115 /// Convert a postgresql boolean to a System.Boolean.
117 internal static Object
ToBoolean(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
,
120 return (BackendData
.ToLower() == "t" ? true : false);
125 /// Convert a postgresql bit to a System.Boolean.
127 internal static Object
ToBit(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
129 return (BackendData
.ToLower() == "1" ? true : false);
133 /// Convert a postgresql datetime to a System.DateTime.
135 internal static Object
ToDateTime(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
,
138 // Get the date time parsed in all expected formats for timestamp.
140 // First check for special values infinity and -infinity.
142 if (BackendData
== "infinity")
144 return DateTime
.MaxValue
;
147 if (BackendData
== "-infinity")
149 return DateTime
.MinValue
;
153 DateTime
.ParseExact(BackendData
, DateTimeFormats
, DateTimeFormatInfo
.InvariantInfo
,
154 DateTimeStyles
.NoCurrentDateDefault
| DateTimeStyles
.AllowWhiteSpaces
);
158 /// Convert a postgresql date to a System.DateTime.
160 internal static Object
ToDate(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
163 DateTime
.ParseExact(BackendData
, DateFormats
, DateTimeFormatInfo
.InvariantInfo
, DateTimeStyles
.AllowWhiteSpaces
);
167 /// Convert a postgresql time to a System.DateTime.
169 internal static Object
ToTime(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
172 DateTime
.ParseExact(BackendData
, TimeFormats
, DateTimeFormatInfo
.InvariantInfo
,
173 DateTimeStyles
.NoCurrentDateDefault
| DateTimeStyles
.AllowWhiteSpaces
);
177 /// Convert a postgresql money to a System.Decimal.
179 internal static Object
ToMoney(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
181 // It's a number with a $ on the beginning...
182 return Convert
.ToDecimal(BackendData
.Substring(1, BackendData
.Length
- 1), CultureInfo
.InvariantCulture
);
187 /// Provide event handlers to convert the basic native supported data types from
188 /// native form to backend representation.
190 internal abstract class BasicNativeToBackendTypeConverter
195 internal static String
ToBinary(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
197 Byte
[] byteArray
= (Byte
[])NativeData
;
198 int len
= byteArray
.Length
;
199 char[] res
= new char[len
* 5];
201 for (int i
= 0, o
= 0; i
< len
; ++i
, o
+= 5)
203 byte item
= byteArray
[i
];
204 res
[o
] = res
[o
+ 1] = '\\';
205 res
[o
+ 2] = (char)('0' + (7 & (item
>> 6)));
206 res
[o
+ 3] = (char)('0' + (7 & (item
>> 3)));
207 res
[o
+ 4] = (char)('0' + (7 & item
));
210 return new String(res
);
214 /// Convert to a postgresql boolean.
216 internal static String
ToBoolean(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
218 return ((bool)NativeData
) ? "TRUE" : "FALSE";
222 /// Convert to a postgresql bit.
224 internal static String
ToBit(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
226 // Convert boolean values to bit or convert int32 values to bit - odd values are 1 and
227 // even numbers are 0.
228 if (NativeData
is Boolean
)
230 return ((Boolean
)NativeData
) ? "1" : "0";
234 return (((Int32
)NativeData
) % 2 == 1) ? "1" : "0";
239 /// Convert to a postgresql timestamp.
241 internal static String
ToDateTime(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
243 if (!(NativeData
is DateTime
))
245 return ExtendedNativeToBackendTypeConverter
.ToTimeStamp(TypeInfo
, NativeData
);
247 if (DateTime
.MaxValue
.Equals(NativeData
))
251 if (DateTime
.MinValue
.Equals(NativeData
))
255 return ((DateTime
)NativeData
).ToString("yyyy-MM-dd HH:mm:ss.ffffff", DateTimeFormatInfo
.InvariantInfo
);
259 /// Convert to a postgresql date.
261 internal static String
ToDate(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
263 if (!(NativeData
is DateTime
))
265 return ExtendedNativeToBackendTypeConverter
.ToDate(TypeInfo
, NativeData
);
267 return ((DateTime
)NativeData
).ToString("yyyy-MM-dd", DateTimeFormatInfo
.InvariantInfo
);
271 /// Convert to a postgresql time.
273 internal static String
ToTime(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
275 if (!(NativeData
is DateTime
))
277 return ExtendedNativeToBackendTypeConverter
.ToTime(TypeInfo
, NativeData
);
281 return ((DateTime
)NativeData
).ToString("HH:mm:ss.ffffff", DateTimeFormatInfo
.InvariantInfo
);
286 /// Convert to a postgres money.
288 internal static String
ToMoney(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
290 return "$" + ((IFormattable
)NativeData
).ToString(null, CultureInfo
.InvariantCulture
.NumberFormat
);
296 /// Provide event handlers to convert extended native supported data types from their backend
297 /// text representation to a .NET object.
299 internal abstract class ExtendedBackendToNativeTypeConverter
301 private static readonly Regex pointRegex
= new Regex(@"\((-?\d+.?\d*),(-?\d+.?\d*)\)");
302 private static readonly Regex boxlsegRegex
= new Regex(@"\((-?\d+.?\d*),(-?\d+.?\d*)\),\((-?\d+.?\d*),(-?\d+.?\d*)\)");
303 private static readonly Regex pathpolygonRegex
= new Regex(@"\((-?\d+.?\d*),(-?\d+.?\d*)\)");
304 private static readonly Regex circleRegex
= new Regex(@"<\((-?\d+.?\d*),(-?\d+.?\d*)\),(\d+.?\d*)>");
308 /// Convert a postgresql point to a System.NpgsqlPoint.
310 internal static Object
ToPoint(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
312 Match m
= pointRegex
.Match(BackendData
);
315 new NpgsqlPoint(Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
316 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
));
320 /// Convert a postgresql point to a System.RectangleF.
322 internal static Object
ToBox(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
324 Match m
= boxlsegRegex
.Match(BackendData
);
328 new NpgsqlPoint(Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
329 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)),
330 new NpgsqlPoint(Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
331 Single
.Parse(m
.Groups
[4].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)));
337 internal static Object
ToLSeg(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
339 Match m
= boxlsegRegex
.Match(BackendData
);
343 new NpgsqlPoint(Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
344 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)),
345 new NpgsqlPoint(Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
346 Single
.Parse(m
.Groups
[4].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)));
352 internal static Object
ToPath(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
354 Match m
= pathpolygonRegex
.Match(BackendData
);
355 Boolean open
= (BackendData
[0] == '[');
356 List
<NpgsqlPoint
> points
= new List
<NpgsqlPoint
>();
364 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
365 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)));
369 // Here we have to do a little hack, because as of 2004-08-11 mono cvs version, the last group is returned with
370 // a trailling ')' only when the last character of the string is a ')' which is the case for closed paths
371 // returned by backend. This gives parsing exception when converting to single.
372 // I still don't know if this is a bug in mono or in my regular expression.
373 // Check if there is this character and remove it.
375 String group2
= m
.Groups
[2].ToString();
376 if (group2
.EndsWith(")"))
378 group2
= group2
.Remove(group2
.Length
- 1, 1);
383 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
384 Single
.Parse(group2
, NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)));
390 NpgsqlPath result
= new NpgsqlPath(points
.ToArray());
398 internal static Object
ToPolygon(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
,
401 Match m
= pathpolygonRegex
.Match(BackendData
);
402 List
<NpgsqlPoint
> points
= new List
<NpgsqlPoint
>();
406 // Here we have to do a little hack, because as of 2004-08-11 mono cvs version, the last group is returned with
407 // a trailling ')' only when the last character of the string is a ')' which is the case for closed paths
408 // returned by backend. This gives parsing exception when converting to single.
409 // I still don't know if this is a bug in mono or in my regular expression.
410 // Check if there is this character and remove it.
412 String group2
= m
.Groups
[2].ToString();
413 if (group2
.EndsWith(")"))
415 group2
= group2
.Remove(group2
.Length
- 1, 1);
419 new NpgsqlPoint(Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
420 Single
.Parse(group2
, NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)));
426 return new NpgsqlPolygon(points
);
432 internal static Object
ToCircle(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
434 Match m
= circleRegex
.Match(BackendData
);
437 new NpgsqlPoint(Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
),
438 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
)),
439 Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
, CultureInfo
.InvariantCulture
.NumberFormat
));
445 internal static Object
ToInet(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
447 return new NpgsqlInet(BackendData
);
450 internal static Object
ToGuid(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
452 return new Guid(BackendData
);
458 internal static object ToInterval(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
,
461 return NpgsqlInterval
.Parse(backendData
);
464 internal static object ToTime(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
, Int32 typeModifier
)
466 return NpgsqlTime
.Parse(backendData
);
469 internal static object ToTimeTZ(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
, Int32 typeModifier
)
471 return NpgsqlTimeTZ
.Parse(backendData
);
474 internal static object ToDate(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
, Int32 typeModifier
)
476 return NpgsqlDate
.Parse(backendData
);
479 internal static object ToTimeStamp(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
,
482 return NpgsqlTimeStamp
.Parse(backendData
);
485 internal static object ToTimeStampTZ(NpgsqlBackendTypeInfo typeInfo
, String backendData
, Int16 typeSize
,
488 return NpgsqlTimeStampTZ
.Parse(backendData
);
493 /// Provide event handlers to convert extended native supported data types from
494 /// native form to backend representation.
496 internal abstract class ExtendedNativeToBackendTypeConverter
501 internal static String
ToPoint(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
503 if (NativeData
is NpgsqlPoint
)
505 NpgsqlPoint P
= (NpgsqlPoint
)NativeData
;
506 return String
.Format(CultureInfo
.InvariantCulture
, "({0},{1})", P
.X
, P
.Y
);
510 throw new InvalidCastException("Unable to cast data to NpgsqlPoint type");
517 internal static String
ToBox(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
519 /*if (NativeData.GetType() == typeof(Rectangle)) {
520 Rectangle R = (Rectangle)NativeData;
521 return String.Format(CultureInfo.InvariantCulture, "({0},{1}),({2},{3})", R.Left, R.Top, R.Left + R.Width, R.Top + R.Height);
522 } else if (NativeData.GetType() == typeof(RectangleF)) {
523 RectangleF R = (RectangleF)NativeData;
524 return String.Format(CultureInfo.InvariantCulture, "({0},{1}),({2},{3})", R.Left, R.Top, R.Left + R.Width, R.Top + R.Height);*/
526 if (NativeData
is NpgsqlBox
)
528 NpgsqlBox box
= (NpgsqlBox
)NativeData
;
530 String
.Format(CultureInfo
.InvariantCulture
, "({0},{1}),({2},{3})", box
.LowerLeft
.X
, box
.LowerLeft
.Y
,
531 box
.UpperRight
.X
, box
.UpperRight
.Y
);
535 throw new InvalidCastException("Unable to cast data to Rectangle type");
542 internal static String
ToLSeg(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
544 NpgsqlLSeg S
= (NpgsqlLSeg
)NativeData
;
545 return String
.Format(CultureInfo
.InvariantCulture
, "{0},{1},{2},{3}", S
.Start
.X
, S
.Start
.Y
, S
.End
.X
, S
.End
.Y
);
551 internal static String
ToPath(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
553 StringBuilder B
= null;
556 B
=new StringBuilder();
558 foreach (NpgsqlPoint P
in ((NpgsqlPath
)NativeData
))
560 B
.AppendFormat(CultureInfo
.InvariantCulture
, "{0}({1},{2})", (B
.Length
> 0 ? "," : ""), P
.X
, P
.Y
);
563 return String
.Format("[{0}]", B
);
576 internal static String
ToPolygon(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
578 StringBuilder B
= new StringBuilder();
580 foreach (NpgsqlPoint P
in ((NpgsqlPolygon
)NativeData
))
582 B
.AppendFormat(CultureInfo
.InvariantCulture
, "{0}({1},{2})", (B
.Length
> 0 ? "," : ""), P
.X
, P
.Y
);
585 return String
.Format("({0})", B
);
591 internal static String
ToCircle(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
593 NpgsqlCircle C
= (NpgsqlCircle
)NativeData
;
594 return String
.Format(CultureInfo
.InvariantCulture
, "{0},{1},{2}", C
.Center
.X
, C
.Center
.Y
, C
.Radius
);
598 /// Convert to a postgres inet.
600 internal static String
ToIPAddress(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
602 if (NativeData
is NpgsqlInet
)
604 return ((NpgsqlInet
)NativeData
).ToString();
606 return NativeData
.ToString();
611 /// Convert to a postgres interval
613 internal static String
ToInterval(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
616 ((NativeData
is TimeSpan
)
617 ? ((NpgsqlInterval
)(TimeSpan
)NativeData
).ToString()
618 : ((NpgsqlInterval
)NativeData
).ToString());
621 internal static string ToTime(NpgsqlNativeTypeInfo typeInfo
, object nativeData
)
623 if (nativeData
is DateTime
)
625 return BasicNativeToBackendTypeConverter
.ToTime(typeInfo
, nativeData
);
628 if (nativeData
is TimeSpan
)
630 time
= (NpgsqlTime
)(TimeSpan
)nativeData
;
634 time
= (NpgsqlTime
)nativeData
;
636 return time
.ToString();
639 internal static string ToTimeTZ(NpgsqlNativeTypeInfo typeInfo
, object nativeData
)
641 if (nativeData
is DateTime
)
643 return BasicNativeToBackendTypeConverter
.ToTime(typeInfo
, nativeData
);
646 if (nativeData
is TimeSpan
)
648 time
= (NpgsqlTimeTZ
)(TimeSpan
)nativeData
;
652 time
= (NpgsqlTimeTZ
)nativeData
;
654 return time
.ToString();
657 internal static string ToDate(NpgsqlNativeTypeInfo typeInfo
, object nativeData
)
659 if (nativeData
is DateTime
)
661 return BasicNativeToBackendTypeConverter
.ToDate(typeInfo
, nativeData
);
665 return nativeData
.ToString();
669 internal static string ToTimeStamp(NpgsqlNativeTypeInfo typeInfo
, object nativeData
)
671 if (nativeData
is DateTime
)
673 return BasicNativeToBackendTypeConverter
.ToDateTime(typeInfo
, nativeData
);
677 return nativeData
.ToString();