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 // This library is free software; you can redistribute it and/or
11 // modify it under the terms of the GNU Lesser General Public
12 // License as published by the Free Software Foundation; either
13 // version 2.1 of the License, or (at your option) any later version.
15 // This library is distributed in the hope that it will be useful,
16 // but WITHOUT ANY WARRANTY; without even the implied warranty of
17 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18 // Lesser General Public License for more details.
20 // You should have received a copy of the GNU Lesser General Public
21 // License along with this library; if not, write to the Free Software
22 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
24 // This file provides data type converters between PostgreSQL representations
28 using System
.Collections
;
29 using System
.Globalization
;
32 using System
.Text
.RegularExpressions
;
39 /// Provide event handlers to convert all native supported basic data types from their backend
40 /// text representation to a .NET object.
42 internal abstract class BasicBackendToNativeTypeConverter
44 private static readonly String
[] DateFormats
= new String
[]
49 private static readonly String
[] TimeFormats
= new String
[]
67 private static readonly String
[] DateTimeFormats
= new String
[]
69 "yyyy-MM-dd HH:mm:ss.ffffff",
70 "yyyy-MM-dd HH:mm:ss",
71 "yyyy-MM-dd HH:mm:ss.ffffffzz",
72 "yyyy-MM-dd HH:mm:sszz",
73 "yyyy-MM-dd HH:mm:ss.fffff",
74 "yyyy-MM-dd HH:mm:ss.ffff",
75 "yyyy-MM-dd HH:mm:ss.fff",
76 "yyyy-MM-dd HH:mm:ss.ff",
77 "yyyy-MM-dd HH:mm:ss.f",
78 "yyyy-MM-dd HH:mm:ss.fffffzz",
79 "yyyy-MM-dd HH:mm:ss.ffffzz",
80 "yyyy-MM-dd HH:mm:ss.fffzz",
81 "yyyy-MM-dd HH:mm:ss.ffzz",
82 "yyyy-MM-dd HH:mm:ss.fzz",
88 internal static Object
ToBinary(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
91 Int32 byteAPosition
= 0;
92 Int32 byteAStringLength
= BackendData
.Length
;
93 MemoryStream ms
= new MemoryStream();
95 while (byteAPosition
< byteAStringLength
)
97 // The IsDigit is necessary in case we receive a \ as the octal value and not
98 // as the indicator of a following octal value in decimal format.
100 if (BackendData
[byteAPosition
] == '\\') {
102 if (byteAPosition
+ 1 == byteAStringLength
)
107 else if (Char
.IsDigit(BackendData
[byteAPosition
+ 1]))
109 octalValue
= (Byte
.Parse(BackendData
[byteAPosition
+ 1].ToString()) << 6);
110 octalValue
|= (Byte
.Parse(BackendData
[byteAPosition
+ 2].ToString()) << 3);
111 octalValue
|= Byte
.Parse(BackendData
[byteAPosition
+ 3].ToString());
122 octalValue
= (Byte
)BackendData
[byteAPosition
];
127 ms
.WriteByte((Byte
)octalValue
);
135 /// Convert a postgresql boolean to a System.Boolean.
137 internal static Object
ToBoolean(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
139 return (BackendData
.ToLower() == "t" ? true : false);
144 /// Convert a postgresql bit to a System.Boolean.
146 internal static Object
ToBit(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
148 return (BackendData
.ToLower() == "1" ? true : false);
152 /// Convert a postgresql datetime to a System.DateTime.
154 internal static Object
ToDateTime(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
157 // Get the date time parsed in all expected formats for timestamp.
159 // First check for special values infinity and -infinity.
161 if (BackendData
== "infinity")
162 return DateTime
.MaxValue
;
164 if (BackendData
== "-infinity")
165 return DateTime
.MinValue
;
167 return DateTime
.ParseExact(BackendData
,
169 DateTimeFormatInfo
.InvariantInfo
,
170 DateTimeStyles
.NoCurrentDateDefault
| DateTimeStyles
.AllowWhiteSpaces
);
174 /// Convert a postgresql date to a System.DateTime.
176 internal static Object
ToDate(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
178 return DateTime
.ParseExact(BackendData
,
180 DateTimeFormatInfo
.InvariantInfo
,
181 DateTimeStyles
.AllowWhiteSpaces
);
185 /// Convert a postgresql time to a System.DateTime.
187 internal static Object
ToTime(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
189 return DateTime
.ParseExact(BackendData
,
191 DateTimeFormatInfo
.InvariantInfo
,
192 DateTimeStyles
.NoCurrentDateDefault
| DateTimeStyles
.AllowWhiteSpaces
);
196 /// Convert a postgresql money to a System.Decimal.
198 internal static Object
ToMoney(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
200 // It's a number with a $ on the beginning...
201 return Convert
.ToDecimal(BackendData
.Substring(1, BackendData
.Length
- 1), CultureInfo
.InvariantCulture
);
207 /// Provide event handlers to convert the basic native supported data types from
208 /// native form to backend representation.
210 internal abstract class BasicNativeToBackendTypeConverter
215 internal static String
ToBinary(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
217 Byte
[] byteArray
= (Byte
[])NativeData
;
218 int len
= byteArray
.Length
;
219 char[] res
= new char[len
* 5];
221 for (int i
= 0, o
= 0; i
< len
; ++i
, o
+= 5)
223 byte item
= byteArray
[i
];
224 res
[o
] = res
[o
+ 1] = '\\';
225 res
[o
+ 2] = (char)('0' + (7 & (item
>> 6)));
226 res
[o
+ 3] = (char)('0' + (7 & (item
>> 3)));
227 res
[o
+ 4] = (char)('0' + (7 & item
));
230 return new String(res
);
234 /// Convert to a postgresql boolean.
236 internal static String
ToBoolean(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
238 return ((bool)NativeData
) ? "TRUE" : "FALSE";
242 /// Convert to a postgresql bit.
244 internal static String
ToBit(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
246 // Convert boolean values to bit or convert int32 values to bit - odd values are 1 and
247 // even numbers are 0.
248 if (NativeData
is Boolean
)
249 return ((Boolean
)NativeData
) ? "1" : "0";
251 return (((Int32
)NativeData
) % 2 == 1) ? "1" : "0";
255 /// Convert to a postgresql timestamp.
257 internal static String
ToDateTime(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
259 if (DateTime
.MaxValue
.Equals(NativeData
))
261 if (DateTime
.MinValue
.Equals(NativeData
))
263 return ((DateTime
)NativeData
).ToString("yyyy-MM-dd HH:mm:ss.ffffff", DateTimeFormatInfo
.InvariantInfo
);
267 /// Convert to a postgresql date.
269 internal static String
ToDate(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
271 return ((DateTime
)NativeData
).ToString("yyyy-MM-dd", DateTimeFormatInfo
.InvariantInfo
);
275 /// Convert to a postgresql time.
277 internal static String
ToTime(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
279 return ((DateTime
)NativeData
).ToString("HH:mm:ss.ffffff", DateTimeFormatInfo
.InvariantInfo
);
283 /// Convert to a postgres money.
285 internal static String
ToMoney(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
287 return "$" + ((IFormattable
)NativeData
).ToString(null, CultureInfo
.InvariantCulture
.NumberFormat
);
295 /// Provide event handlers to convert extended native supported data types from their backend
296 /// text representation to a .NET object.
298 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
)
313 Match m
= pointRegex
.Match(BackendData
);
315 return new NpgsqlPoint(
316 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
317 CultureInfo
.InvariantCulture
.NumberFormat
),
318 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
,
319 CultureInfo
.InvariantCulture
.NumberFormat
));
326 /// Convert a postgresql point to a System.RectangleF.
328 internal static Object
ToBox(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
331 Match m
= boxlsegRegex
.Match(BackendData
);
333 return new NpgsqlBox(
335 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
336 CultureInfo
.InvariantCulture
.NumberFormat
),
337 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
,
338 CultureInfo
.InvariantCulture
.NumberFormat
)),
340 Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
,
341 CultureInfo
.InvariantCulture
.NumberFormat
),
342 Single
.Parse(m
.Groups
[4].ToString(), NumberStyles
.Any
,
343 CultureInfo
.InvariantCulture
.NumberFormat
)));
349 internal static Object
ToLSeg(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
351 Match m
= boxlsegRegex
.Match(BackendData
);
353 return new NpgsqlLSeg(
355 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
356 CultureInfo
.InvariantCulture
.NumberFormat
),
357 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
,
358 CultureInfo
.InvariantCulture
.NumberFormat
)),
360 Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
,
361 CultureInfo
.InvariantCulture
.NumberFormat
),
362 Single
.Parse(m
.Groups
[4].ToString(), NumberStyles
.Any
,
363 CultureInfo
.InvariantCulture
.NumberFormat
)));
369 internal static Object
ToPath(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
372 Match m
= pathpolygonRegex
.Match(BackendData
);
373 Boolean open
= (BackendData
[0] == '[');
374 ArrayList points
= new ArrayList();
380 points
.Add(new NpgsqlPoint(
381 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
382 CultureInfo
.InvariantCulture
.NumberFormat
),
383 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
,
384 CultureInfo
.InvariantCulture
.NumberFormat
)));
387 // Here we have to do a little hack, because as of 2004-08-11 mono cvs version, the last group is returned with
388 // a trailling ')' only when the last character of the string is a ')' which is the case for closed paths
389 // returned by backend. This gives parsing exception when converting to single.
390 // I still don't know if this is a bug in mono or in my regular expression.
391 // Check if there is this character and remove it.
393 String group2
= m
.Groups
[2].ToString();
394 if (group2
.EndsWith(")"))
395 group2
= group2
.Remove(group2
.Length
- 1, 1);
397 points
.Add(new NpgsqlPoint(
398 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
399 CultureInfo
.InvariantCulture
.NumberFormat
),
400 Single
.Parse(group2
, NumberStyles
.Any
,
401 CultureInfo
.InvariantCulture
.NumberFormat
)));
408 NpgsqlPath result
= new NpgsqlPath((NpgsqlPoint
[]) points
.ToArray(typeof(NpgsqlPoint
)));
409 result
.IsOpen
= open
;
418 internal static Object
ToPolygon(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
421 Match m
= pathpolygonRegex
.Match(BackendData
);
422 ArrayList points
= new ArrayList();
427 // Here we have to do a little hack, because as of 2004-08-11 mono cvs version, the last group is returned with
428 // a trailling ')' only when the last character of the string is a ')' which is the case for closed paths
429 // returned by backend. This gives parsing exception when converting to single.
430 // I still don't know if this is a bug in mono or in my regular expression.
431 // Check if there is this character and remove it.
433 String group2
= m
.Groups
[2].ToString();
434 if (group2
.EndsWith(")"))
435 group2
= group2
.Remove(group2
.Length
- 1, 1);
437 points
.Add(new NpgsqlPoint(
438 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
439 CultureInfo
.InvariantCulture
.NumberFormat
),
440 Single
.Parse(group2
, NumberStyles
.Any
,
441 CultureInfo
.InvariantCulture
.NumberFormat
)));
448 return new NpgsqlPolygon((NpgsqlPoint
[]) points
.ToArray(typeof(NpgsqlPoint
)));
455 internal static Object
ToCircle(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
457 Match m
= circleRegex
.Match(BackendData
);
458 return new NpgsqlCircle(
460 Single
.Parse(m
.Groups
[1].ToString(), NumberStyles
.Any
,
461 CultureInfo
.InvariantCulture
.NumberFormat
),
462 Single
.Parse(m
.Groups
[2].ToString(), NumberStyles
.Any
,
463 CultureInfo
.InvariantCulture
.NumberFormat
)),
464 Single
.Parse(m
.Groups
[3].ToString(), NumberStyles
.Any
,
465 CultureInfo
.InvariantCulture
.NumberFormat
));
472 internal static Object
ToInet(NpgsqlBackendTypeInfo TypeInfo
, String BackendData
, Int16 TypeSize
, Int32 TypeModifier
)
474 return new NpgsqlInet(BackendData
);
480 /// Provide event handlers to convert extended native supported data types from
481 /// native form to backend representation.
483 internal abstract class ExtendedNativeToBackendTypeConverter
488 internal static String
ToPoint(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
490 if (NativeData
is NpgsqlPoint
)
492 NpgsqlPoint P
= (NpgsqlPoint
)NativeData
;
493 return String
.Format(CultureInfo
.InvariantCulture
, "({0},{1})", P
.X
, P
.Y
);
497 throw new InvalidCastException("Unable to cast data to NpgsqlPoint type");
504 internal static String
ToBox(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
506 /*if (NativeData.GetType() == typeof(Rectangle)) {
507 Rectangle R = (Rectangle)NativeData;
508 return String.Format(CultureInfo.InvariantCulture, "({0},{1}),({2},{3})", R.Left, R.Top, R.Left + R.Width, R.Top + R.Height);
509 } else if (NativeData.GetType() == typeof(RectangleF)) {
510 RectangleF R = (RectangleF)NativeData;
511 return String.Format(CultureInfo.InvariantCulture, "({0},{1}),({2},{3})", R.Left, R.Top, R.Left + R.Width, R.Top + R.Height);*/
513 if (NativeData
is NpgsqlBox
)
515 NpgsqlBox box
= (NpgsqlBox
) NativeData
;
516 return String
.Format(CultureInfo
.InvariantCulture
, "({0},{1}),({2},{3})", box
.LowerLeft
.X
, box
.LowerLeft
.Y
, box
.UpperRight
.X
, box
.UpperRight
.Y
);
519 throw new InvalidCastException("Unable to cast data to Rectangle type");
526 internal static String
ToLSeg(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
528 NpgsqlLSeg S
= (NpgsqlLSeg
)NativeData
;
529 return String
.Format(CultureInfo
.InvariantCulture
, "{0},{1},{2},{3}", S
.Start
.X
, S
.Start
.Y
, S
.End
.X
, S
.End
.Y
);
535 internal static String
ToPath(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
537 StringBuilder B
= new StringBuilder();
539 foreach (NpgsqlPoint P
in ((NpgsqlPath
)NativeData
).Points
) {
540 B
.AppendFormat(CultureInfo
.InvariantCulture
, "{0}({1},{2})", (B
.Length
> 0 ? "," : ""), P
.X
, P
.Y
);
543 return String
.Format("[{0}]", B
.ToString());
549 internal static String
ToPolygon(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
551 StringBuilder B
= new StringBuilder();
553 foreach (NpgsqlPoint P
in ((NpgsqlPolygon
)NativeData
).Points
) {
554 B
.AppendFormat(CultureInfo
.InvariantCulture
, "{0}({1},{2})", (B
.Length
> 0 ? "," : ""), P
.X
, P
.Y
);
557 return String
.Format("({0})", B
.ToString());
563 internal static String
ToCircle(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
565 NpgsqlCircle C
= (NpgsqlCircle
)NativeData
;
566 return String
.Format(CultureInfo
.InvariantCulture
, "{0},{1},{2}", C
.Center
.X
, C
.Center
.Y
, C
.Radius
);
570 /// Convert to a postgres inet.
572 internal static String
ToIPAddress(NpgsqlNativeTypeInfo TypeInfo
, Object NativeData
)
574 if (NativeData
is NpgsqlInet
)
575 return ((NpgsqlInet
)NativeData
).ToString();
577 return ((System
.Net
.IPAddress
)NativeData
).ToString();