mcs/class/Mono.Data.PostgreSqlClient/Mono.Data.PostgreSqlClient/PostgresTypes.cs

   1 //\r
   2 // PostgresTypes.cs - holding methods to convert \r
   3 //                    between PostgreSQL types and .NET types\r
   4 //\r
   5 // Author:\r
   6 //     Daniel Morgan <danmorg@sc.rr.com>\r
   7 //\r
   8 // (c)copyright 2002 Daniel Morgan\r
   9 //\r
  10
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31 \r
  32 // Note: this might become PostgresType and PostgresTypeCollection\r
  33 //       also, the PostgresTypes that exist as an inner internal class\r
  34 //       within PgSqlConnection maybe moved here in the future\r
  35 \r
  36 using System;\r
  37 using System.Collections;\r
  38 using System.Data;\r
  39 using System.Data.Common;\r
  40 using System.Text;\r
  41 \r
  42 namespace Mono.Data.PostgreSqlClient {\r
  43 \r
  44         internal struct PostgresType {\r
  45                 public int oid;\r
  46                 public string typname;\r
  47                 public DbType dbType;\r
  48         }\r
  49
  50         sealed internal class PostgresHelper {
  51
  52                 // translates the PostgreSQL typname to System.Data.DbType
  53                 public static DbType TypnameToSqlDbType(string typname) {\r
  54                         DbType sqlType;\r
  55                         \r
  56                         // FIXME: use hashtable here?\r
  57 \r
  58                         switch(typname) {\r
  59 \r
  60                         case "abstime":\r
  61                                 sqlType = DbType.Int32;\r
  62                                 break;\r
  63 \r
  64                         case "aclitem":\r
  65                                 sqlType = DbType.String;\r
  66                                 break;\r
  67 \r
  68                         case "bit":\r
  69                                 sqlType = DbType.String;\r
  70                                 break;\r
  71 \r
  72                         case "bool":\r
  73                                 sqlType = DbType.Boolean;\r
  74                                 break;\r
  75 \r
  76                         case "box":\r
  77                                 sqlType = DbType.String;\r
  78                                 break;\r
  79 \r
  80                         case "bpchar":\r
  81                                 sqlType = DbType.String;\r
  82                                 break;\r
  83 \r
  84                         case "bytea":\r
  85                                 sqlType = DbType.String;\r
  86                                 break;\r
  87 \r
  88                         case "char":\r
  89                                 sqlType = DbType.String;\r
  90                                 break;\r
  91 \r
  92                         case "cidr":\r
  93                                 sqlType = DbType.String;\r
  94                                 break;\r
  95 \r
  96                         case "circle":\r
  97                                 sqlType = DbType.String;\r
  98                                 break;\r
  99 \r
 100                         case "date":\r
 101                                 sqlType = DbType.Date;\r
 102                                 break;\r
 103 \r
 104                         case "float4":\r
 105                                 sqlType = DbType.Single;\r
 106                                 break;\r
 107 \r
 108                         case "float8":\r
 109                                 sqlType = DbType.Double;\r
 110                                 break;\r
 111 \r
 112                         case "inet":\r
 113                                 sqlType = DbType.String;\r
 114                                 break;\r
 115 \r
 116                         case "int2":\r
 117                                 sqlType = DbType.Int16;\r
 118                                 break;\r
 119 \r
 120                         case "int4":\r
 121                                 sqlType = DbType.Int32;\r
 122                                 break;\r
 123 \r
 124                         case "int8":\r
 125                                 sqlType = DbType.Int64;\r
 126                                 break;\r
 127 \r
 128                         case "interval":\r
 129                                 sqlType = DbType.String;\r
 130                                 break;\r
 131 \r
 132                         case "line":\r
 133                                 sqlType = DbType.String;\r
 134                                 break;\r
 135 \r
 136                         case "lseg":\r
 137                                 sqlType = DbType.String;\r
 138                                 break;\r
 139 \r
 140                         case "macaddr":\r
 141                                 sqlType = DbType.String;\r
 142                                 break;\r
 143 \r
 144                         case "money":\r
 145                                 sqlType = DbType.Decimal;\r
 146                                 break;\r
 147 \r
 148                         case "name":\r
 149                                 sqlType = DbType.String;\r
 150                                 break;\r
 151 \r
 152                         case "numeric":\r
 153                                 sqlType = DbType.Decimal;\r
 154                                 break;\r
 155 \r
 156                         case "oid":\r
 157                                 sqlType = DbType.Int32;\r
 158                                 break;\r
 159 \r
 160                         case "path":\r
 161                                 sqlType = DbType.String;\r
 162                                 break;\r
 163 \r
 164                         case "point":\r
 165                                 sqlType = DbType.String;\r
 166                                 break;\r
 167 \r
 168                         case "polygon":\r
 169                                 sqlType = DbType.String;\r
 170                                 break;\r
 171 \r
 172                         case "refcursor":\r
 173                                 sqlType = DbType.String;\r
 174                                 break;\r
 175 \r
 176                         case "reltime":\r
 177                                 sqlType = DbType.String;\r
 178                                 break;\r
 179 \r
 180                         case "text":\r
 181                                 sqlType = DbType.String;\r
 182                                 break;\r
 183 \r
 184                         case "time":\r
 185                                 sqlType = DbType.Time;\r
 186                                 break;\r
 187 \r
 188                         case "timestamp":\r
 189                                 sqlType = DbType.DateTime;\r
 190                                 break;\r
 191 \r
 192                         case "timestamptz":\r
 193                                 sqlType = DbType.DateTime;\r
 194                                 break;\r
 195 \r
 196                         case "timetz":\r
 197                                 sqlType = DbType.DateTime;\r
 198                                 break;\r
 199 \r
 200                         case "tinterval":\r
 201                                 sqlType = DbType.String;\r
 202                                 break;\r
 203 \r
 204                         case "varbit":\r
 205                                 sqlType = DbType.String;\r
 206                                 break;\r
 207 \r
 208                         case "varchar":\r
 209                                 sqlType = DbType.String;\r
 210                                 break;\r
 211 \r
 212                         default:\r
 213                                 sqlType = DbType.String;\r
 214                                 break;\r
 215                         }\r
 216                         return sqlType;\r
 217                 }\r
 218                 \r
 219                 // Converts data value from database to .NET System type.\r
 220                 public static object ConvertDbTypeToSystem (DbType typ, String value) {
 221                         object obj = null;
 222
 223                         // FIXME: more types need
 224                         //        to be converted
 225                         //        from PostgreSQL oid type
 226                         //        to .NET System.<type>
 227
 228                         // FIXME: need to handle a NULL for each type
 229                         //       maybe setting obj to System.DBNull.Value ?
 230
 231
 232                         if(value == null) {
 233                                 //Console.WriteLine("ConvertDbTypeToSystemDbType typ: " +
 234                                 //      typ + " value is null");
 235                                 return null;
 236                         }
 237                         else if(value.Equals("")) {
 238                                 //Console.WriteLine("ConvertDbTypeToSystemDbType typ: " +
 239                                 //      typ + " value is string empty");
 240                                 return null;
 241                         }
 242
 243                         //Console.WriteLine("ConvertDbTypeToSystemDbType typ: " +
 244                         //      typ + " value: " + value);
 245
 246                         // Date, Time, and DateTime
 247                         // are parsed based on ISO format
 248                         // "YYYY-MM-DD hh:mi:ss.ms"
 249
 250                         switch(typ) {
 251                         case DbType.String:
 252                                 obj = String.Copy(value);
 253                                 break;
 254                         case DbType.Boolean:
 255                                 obj = value.Equals("t");
 256                                 break;
 257                         case DbType.Int16:\r
 258                                 obj = Int16.Parse(value);\r
 259                                 break;\r
 260                         case DbType.Int32:\r
 261                                 obj = Int32.Parse(value);\r
 262                                 break;\r
 263                         case DbType.Int64:\r
 264                                 obj = Int64.Parse(value);\r
 265                                 break;\r
 266                         case DbType.Decimal:\r
 267                                 obj = Decimal.Parse(value);\r
 268                                 break;\r
 269                         case DbType.Single:\r
 270                                 obj = Single.Parse(value);\r
 271                                 break;\r
 272                         case DbType.Double:\r
 273                                 obj = Double.Parse(value);\r
 274                                 break;\r
 275                         case DbType.Date:\r
 276                                 String[] sd = value.Split(new Char[] {'-'});\r
 277                                 obj = new DateTime(\r
 278                                         Int32.Parse(sd[0]), Int32.Parse(sd[1]), Int32.Parse(sd[2]),\r
 279                                         0,0,0);\r
 280                                 break;\r
 281                         case DbType.Time:\r
 282                                 String[] st = value.Split(new Char[] {':'});\r
 283                                 obj = new DateTime(0001,01,01,\r
 284                                         Int32.Parse(st[0]),Int32.Parse(st[1]),Int32.Parse(st[2]));\r
 285                                 break;\r
 286                         case DbType.DateTime:\r
 287                                 Int32 YYYY,MM,DD,hh,mi,ss,ms;\r
 288                                 YYYY = Int32.Parse(value.Substring(0,4));\r
 289                                 MM = Int32.Parse(value.Substring(5,2));\r
 290                                 DD = Int32.Parse(value.Substring(8,2));\r
 291                                 hh = Int32.Parse(value.Substring(11,2));\r
 292                                 mi = Int32.Parse(value.Substring(14,2));\r
 293                                 ss = Int32.Parse(value.Substring(17,2));\r
 294                                 ms = Int32.Parse(value.Substring(20,2));\r
 295                                 obj = new DateTime(YYYY,MM,DD,hh,mi,ss,ms);\r
 296                                 break;\r
 297                         default:\r
 298                                 obj = String.Copy(value);\r
 299                                 break;\r
 300                         }
 301
 302                         return obj;
 303                 }
 304
 305                 // Translates System.Data.DbType to System.Type
 306                 public static Type DbTypeToSystemType (DbType dType) {
 307                         // FIXME: more types need
 308                         //        to be mapped
 309                         //        from PostgreSQL oid type
 310                         //        to .NET System.<type>
 311
 312                         Type typ = null;
 313
 314                         switch(dType) {
 315                         case DbType.String:
 316                                 typ = typeof(String);
 317                                 break;
 318                         case DbType.Boolean:
 319                                 typ = typeof(Boolean);
 320                                 break;
 321                         case DbType.Int16: \r
 322                                 typ = typeof(Int16);\r
 323                                 break;\r
 324                         case DbType.Int32:\r
 325                                 typ = typeof(Int32);\r
 326                                 break;\r
 327                         case DbType.Int64:\r
 328                                 typ = typeof(Int64);\r
 329                                 break;\r
 330                         case DbType.Decimal:\r
 331                                 typ = typeof(Decimal);\r
 332                                 break;\r
 333                         case DbType.Single:\r
 334                                 typ = typeof(Single);\r
 335                                 break;\r
 336                         case DbType.Double:\r
 337                                 typ = typeof(Double);\r
 338                                 break;\r
 339                         case DbType.Date:\r
 340                         case DbType.Time:\r
 341                         case DbType.DateTime:\r
 342                                 typ = typeof(DateTime);\r
 343                                 break;\r
 344                         default:\r
 345                                 typ = typeof(String);\r
 346                                 break;\r
 347                         }
 348                         return typ;
 349                 }
 350
 351                 // Find DbType for oid
 352                 // which requires a look up of PostgresTypes
 353                 // DbType <-> typname <-> oid
 354                 public static string OidToTypname (int oid, ArrayList pgTypes) {
 355                         // FIXME: more types need
 356                         //        to be mapped
 357                         //        from PostgreSQL oid type
 358                         //        to .NET System.<type>
 359
 360                         string typname = "text"; // default
 361                         int i;
 362                         for(i = 0; i < pgTypes.Count; i++) {
 363                                 PostgresType pt = (PostgresType) pgTypes[i];
 364                                 if(pt.oid == oid) {
 365                                         typname = pt.typname;
 366                                         break;
 367                                 }
 368                         }
 369
 370                         return typname;
 371                 }
 372
 373                 // Convert a .NET System value type (Int32, String, Boolean, etc)
 374                 // to a string that can be included within a SQL statement.
 375                 // This is to methods provides the parameters support
 376                 // for the PostgreSQL .NET Data provider
 377                 public static string ObjectToString(DbType dbtype, object obj) {\r
 378                         \r
 379                         // TODO: how do we handle a NULL?\r
 380                         //if(isNull == true)\r
 381                         //      return "NULL";\r
 382 \r
 383                         string s;\r
 384 \r
 385                         // Date, Time, and DateTime are expressed in ISO format\r
 386                         // which is "YYYY-MM-DD hh:mm:ss.ms";\r
 387                         DateTime dt;\r
 388                         StringBuilder sb;\r
 389 \r
 390                         const string zero = "0";\r
 391 \r
 392                         switch(dbtype) {\r
 393                         case DbType.String:
 394                                 s = "'" + obj + "'";
 395                                 break;
 396                         case DbType.Boolean:
 397                                 if((bool)obj == true)
 398                                         s = "'t'";
 399                                 else
 400                                         s = "'f'";
 401                                 break;
 402                         case DbType.Int16:\r
 403                                 s = obj.ToString();\r
 404                                 break;\r
 405                         case DbType.Int32:\r
 406                                 s = obj.ToString();\r
 407                                 break;\r
 408                         case DbType.Int64:\r
 409                                 s = obj.ToString();\r
 410                                 break;\r
 411                         case DbType.Decimal:\r
 412                                 s = obj.ToString();\r
 413                                 break;\r
 414                         case DbType.Single:\r
 415                                 s = obj.ToString();\r
 416                                 break;\r
 417                         case DbType.Double:\r
 418                                 s = obj.ToString();\r
 419                                 break;\r
 420                         case DbType.Date:\r
 421                                 dt = (DateTime) obj;\r
 422                                 sb = new StringBuilder();\r
 423                                 sb.Append('\'');\r
 424                                 // year\r
 425                                 if(dt.Year < 10)\r
 426                                         sb.Append("000" + dt.Year);\r
 427                                 else if(dt.Year < 100)\r
 428                                         sb.Append("00" + dt.Year);\r
 429                                 else if(dt.Year < 1000)\r
 430                                         sb.Append("0" + dt.Year);\r
 431                                 else\r
 432                                         sb.Append(dt.Year);\r
 433                                 sb.Append("-");\r
 434                                 // month\r
 435                                 if(dt.Month < 10)\r
 436                                         sb.Append(zero + dt.Month);\r
 437                                 else\r
 438                                         sb.Append(dt.Month);\r
 439                                 sb.Append("-");\r
 440                                 // day\r
 441                                 if(dt.Day < 10)\r
 442                                         sb.Append(zero + dt.Day);\r
 443                                 else\r
 444                                         sb.Append(dt.Day);\r
 445                                 sb.Append('\'');\r
 446                                 s = sb.ToString();\r
 447                                 break;\r
 448                         case DbType.Time:\r
 449                                 dt = (DateTime) obj;\r
 450                                 sb = new StringBuilder();\r
 451                                 sb.Append('\'');\r
 452                                 // hour\r
 453                                 if(dt.Hour < 10)\r
 454                                         sb.Append(zero + dt.Hour);\r
 455                                 else\r
 456                                         sb.Append(dt.Hour);\r
 457                                 sb.Append(":");\r
 458                                 // minute\r
 459                                 if(dt.Minute < 10)\r
 460                                         sb.Append(zero + dt.Minute);\r
 461                                 else\r
 462                                         sb.Append(dt.Minute);\r
 463                                 sb.Append(":");\r
 464                                 // second\r
 465                                 if(dt.Second < 10)\r
 466                                         sb.Append(zero + dt.Second);\r
 467                                 else\r
 468                                         sb.Append(dt.Second);\r
 469                                 sb.Append('\'');\r
 470                                 s = sb.ToString();\r
 471                                 break;\r
 472                         case DbType.DateTime:\r
 473                                 dt = (DateTime) obj;\r
 474                                 sb = new StringBuilder();\r
 475                                 sb.Append('\'');\r
 476                                 // year\r
 477                                 if(dt.Year < 10)\r
 478                                         sb.Append("000" + dt.Year);\r
 479                                 else if(dt.Year < 100)\r
 480                                         sb.Append("00" + dt.Year);\r
 481                                 else if(dt.Year < 1000)\r
 482                                         sb.Append("0" + dt.Year);\r
 483                                 else\r
 484                                         sb.Append(dt.Year);\r
 485                                 sb.Append("-");\r
 486                                 // month\r
 487                                 if(dt.Month < 10)\r
 488                                         sb.Append(zero + dt.Month);\r
 489                                 else\r
 490                                         sb.Append(dt.Month);\r
 491                                 sb.Append("-");\r
 492                                 // day\r
 493                                 if(dt.Day < 10)\r
 494                                         sb.Append(zero + dt.Day);\r
 495                                 else\r
 496                                         sb.Append(dt.Day);\r
 497                                 sb.Append(" ");\r
 498                                 // hour\r
 499                                 if(dt.Hour < 10)\r
 500                                         sb.Append(zero + dt.Hour);\r
 501                                 else\r
 502                                         sb.Append(dt.Hour);\r
 503                                 sb.Append(":");\r
 504                                 // minute\r
 505                                 if(dt.Minute < 10)\r
 506                                         sb.Append(zero + dt.Minute);\r
 507                                 else\r
 508                                         sb.Append(dt.Minute);\r
 509                                 sb.Append(":");\r
 510                                 // second\r
 511                                 if(dt.Second < 10)\r
 512                                         sb.Append(zero + dt.Second);\r
 513                                 else\r
 514                                         sb.Append(dt.Second);\r
 515                                 sb.Append(".");\r
 516                                 // millisecond\r
 517                                 if(dt.Millisecond < 10)\r
 518                                         sb.Append(zero + dt.Millisecond);\r
 519                                 else\r
 520                                         sb.Append(dt.Millisecond);\r
 521                                 sb.Append('\'');\r
 522                                 s = sb.ToString();\r
 523                                 break;\r
 524                         default:\r
 525                                 // default to DbType.String\r
 526                                 s = "'" + obj + "'";\r
 527                                 break;\r
 528                         }
 529                         return s;
 530                 }
 531         }
 532 }