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