mcs/class/Npgsql/Npgsql/NpgsqlDataReader.cs

   1
   2 // Npgsql.NpgsqlDataReader.cs
   3 //
   4 // Author:
   5 //      Francisco Jr. (fxjrlists@yahoo.com.br)
   6 //
   7 //      Copyright (C) 2002 The Npgsql Development Team
   8 //      npgsql-general@gborg.postgresql.org
   9 //      http://gborg.postgresql.org/project/npgsql/projdisplay.php
  10 //
  11 //
  12 // This library is free software; you can redistribute it and/or
  13 // modify it under the terms of the GNU Lesser General Public
  14 // License as published by the Free Software Foundation; either
  15 // version 2.1 of the License, or (at your option) any later version.
  16 //
  17 // This library is distributed in the hope that it will be useful,
  18 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  20 // Lesser General Public License for more details.
  21 //
  22 // You should have received a copy of the GNU Lesser General Public
  23 // License along with this library; if not, write to the Free Software
  24 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  25
  26 using System;
  27 using System.Data;
  28 using System.Collections;
  29 using System.Text;
  30
  31 using NpgsqlTypes;
  32
  33 namespace Npgsql
  34 {
  35     /// <summary>
  36     /// Provides a means of reading a forward-only stream of rows from a PostgreSQL backend.  This class cannot be inherited.
  37     /// </summary>
  38     public sealed class NpgsqlDataReader : IDataReader, IEnumerable
  39     {
  40         private NpgsqlConnection        _connection;
  41         private ArrayList                       _resultsets;
  42         private ArrayList                       _responses;
  43         private Int32                           _rowIndex;
  44         private Int32                           _resultsetIndex;
  45         private Int32               _recordsAffected;
  46         private NpgsqlResultSet         _currentResultset;
  47         private DataTable                       _currentResultsetSchema;
  48         private CommandBehavior     _behavior;
  49         private Boolean             _isClosed;
  50         private NpgsqlCommand       _command;
  51
  52
  53         // Logging related values
  54         private static readonly String CLASSNAME = "NpgsqlDataReader";
  55
  56         internal NpgsqlDataReader( ArrayList resultsets, ArrayList responses, CommandBehavior behavior, NpgsqlCommand command)
  57         {
  58             _resultsets = resultsets;
  59             _responses = responses;
  60             _connection = command.Connection;
  61             _rowIndex = -1;
  62             _resultsetIndex = -1;
  63             _recordsAffected = -1;
  64
  65             // positioned before the first results.
  66             // move to the first results
  67             NextResult();
  68
  69             _behavior = behavior;
  70             _isClosed = false;
  71             _command = command;
  72         }
  73
  74         private Boolean HaveResultSet()
  75         {
  76             return (_currentResultset != null);
  77         }
  78
  79         private Boolean HaveRow()
  80         {
  81             return (HaveResultSet() && _rowIndex >= 0 && _rowIndex < _currentResultset.Count);
  82         }
  83
  84         private void CheckHaveResultSet()
  85         {
  86             if (! HaveResultSet())
  87             {
  88                 throw new InvalidOperationException("Cannot read data. No result set.");
  89             }
  90         }
  91
  92         private void CheckHaveRow()
  93         {
  94             CheckHaveResultSet();
  95
  96             if (_rowIndex < 0)
  97             {
  98                 throw new InvalidOperationException("DataReader positioned before beginning of result set. Did you call Read()?");
  99             }
 100             else if (_rowIndex >= _currentResultset.Count)
 101             {
 102                 throw new InvalidOperationException("DataReader positioned beyond end of result set.");
 103             }
 104         }
 105
 106
 107         /// <summary>
 108         /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 109         /// </summary>
 110         public void Dispose()
 111         {
 112             Dispose(true);
 113         }
 114
 115         /// <summary>
 116         /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
 117         /// </summary>
 118         protected void Dispose (bool disposing)
 119         {
 120             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
 121             if (disposing)
 122             {
 123                 this.Close();
 124             }
 125         }
 126
 127         /// <summary>
 128         /// Gets a value indicating the depth of nesting for the current row.  Always returns zero.
 129         /// </summary>
 130         public Int32 Depth
 131         {
 132             get
 133             {
 134                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Depth");
 135                 return 0;
 136             }
 137         }
 138
 139         /// <summary>
 140         /// Gets a value indicating whether the data reader is closed.
 141         /// </summary>
 142         public Boolean IsClosed
 143         {
 144             get
 145             {
 146                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "IsClosed");
 147                 return _isClosed;
 148             }
 149         }
 150
 151         /// <summary>
 152         /// Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
 153         /// </summary>
 154         public Int32 RecordsAffected
 155         {
 156             get
 157             {
 158                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "RecordsAffected");
 159                 return _recordsAffected;
 160             }
 161         }
 162
 163         /// <summary>
 164         /// Indicates if NpgsqlDatareader has rows to be read.
 165         /// </summary>
 166
 167         public Boolean HasRows
 168         {
 169             get
 170             {
 171                 return (HaveResultSet() ? _currentResultset.Count > 0 : false);
 172             }
 173
 174         }
 175
 176         /// <summary>
 177         /// Closes the data reader object.
 178         /// </summary>
 179         public void Close()
 180         {
 181             if ((_behavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection)
 182             {
 183                 _connection.Close();
 184             }
 185
 186             _isClosed = true;
 187             if (this.ReaderClosed != null)
 188                     this.ReaderClosed(this, EventArgs.Empty);
 189         }
 190
 191         /// <summary>
 192         /// Is raised whenever Close() is called.
 193         /// </summary>
 194         public event EventHandler ReaderClosed;
 195
 196         /// <summary>
 197         /// Advances the data reader to the next result, when multiple result sets were returned by the PostgreSQL backend.
 198         /// </summary>
 199         /// <returns>True if the reader was advanced, otherwise false.</returns>
 200         public Boolean NextResult()
 201         {
 202             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "NextResult");
 203
 204             _currentResultset = null;
 205             while((_resultsetIndex + 1) < _resultsets.Count && !HaveResultSet())
 206             {
 207                 _resultsetIndex++;
 208                 _rowIndex = -1;
 209                 _currentResultset = (NpgsqlResultSet)_resultsets[_resultsetIndex];
 210
 211                 if (!HaveResultSet())
 212                 {
 213                     String[] _returnStringTokens = ((String)_responses[_resultsetIndex]).Split(null);   // whitespace separator.
 214                     int responseAffectedRows = 0;
 215
 216                     try
 217                     {
 218                         responseAffectedRows = Int32.Parse(_returnStringTokens[_returnStringTokens.Length - 1]);
 219                     }
 220                     catch (FormatException)
 221                     {
 222                         responseAffectedRows = -1;
 223                     }
 224
 225                     if (responseAffectedRows != -1)
 226                     {
 227                         if (_recordsAffected == -1)
 228                         {
 229                             _recordsAffected = responseAffectedRows;
 230                         }
 231                         else
 232                         {
 233                             _recordsAffected += responseAffectedRows;
 234                         }
 235                     }
 236                 }
 237             }
 238             return HaveResultSet();
 239
 240         }
 241
 242         /// <summary>
 243         /// Advances the data reader to the next row.
 244         /// </summary>
 245         /// <returns>True if the reader was advanced, otherwise false.</returns>
 246         public Boolean Read()
 247         {
 248             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Read");
 249
 250             if (!HaveResultSet())
 251                 return false;
 252
 253             if (_rowIndex < _currentResultset.Count)
 254             {
 255                 _rowIndex++;
 256                 return (_rowIndex < _currentResultset.Count);
 257             }
 258             else
 259             {
 260                 return false;
 261             }
 262
 263
 264         }
 265
 266         /// <summary>
 267         /// Returns a System.Data.DataTable that describes the column metadata of the DataReader.
 268         /// </summary>
 269         public DataTable GetSchemaTable()
 270         {
 271
 272             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetSchemaTable");
 273
 274             if(_currentResultsetSchema == null)
 275                 _currentResultsetSchema = GetResultsetSchema();
 276
 277             return _currentResultsetSchema;
 278         }
 279
 280         /// <summary>
 281         /// Gets the number of columns in the current row.
 282         /// </summary>
 283         public Int32 FieldCount
 284         {
 285             get
 286             {
 287
 288                 NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "FieldCount");
 289
 290                 if (! HaveResultSet()) //Executed a non return rows query.
 291                     return -1;
 292                 else
 293                     return _currentResultset.RowDescription.NumFields;
 294
 295
 296             }
 297
 298         }
 299
 300         /// <summary>
 301         /// Return the column name of the column at index <param name="Index"></param>.
 302         /// </summary>
 303         public String GetName(Int32 Index)
 304         {
 305             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetName");
 306
 307             CheckHaveResultSet();
 308
 309             return _currentResultset.RowDescription[Index].name;
 310         }
 311
 312         /// <summary>
 313         /// Return the data type OID of the column at index <param name="Index"></param>.
 314         /// </summary>
 315         /// FIXME: Why this method returns String?
 316         public String GetDataTypeOID(Int32 Index)
 317         {
 318             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetDataTypeName");
 319
 320             CheckHaveResultSet();
 321
 322             NpgsqlBackendTypeInfo  TI = GetTypeInfo(Index);
 323
 324             return _currentResultset.RowDescription[Index].type_oid.ToString();
 325         }
 326
 327         /// <summary>
 328         /// Return the data type name of the column at index <param name="Index"></param>.
 329         /// </summary>
 330         public String GetDataTypeName(Int32 Index)
 331         {
 332             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetDataTypeName");
 333
 334             CheckHaveResultSet();
 335
 336             NpgsqlBackendTypeInfo  TI = GetTypeInfo(Index);
 337
 338             if (TI == null)
 339             {
 340                 return _currentResultset.RowDescription[Index].type_oid.ToString();
 341             }
 342             else
 343             {
 344                 return TI.Name;
 345             }
 346         }
 347
 348         /// <summary>
 349         /// Return the data type of the column at index <param name="Index"></param>.
 350         /// </summary>
 351         public Type GetFieldType(Int32 Index)
 352         {
 353             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetFieldType");
 354
 355             CheckHaveResultSet();
 356
 357             NpgsqlBackendTypeInfo  TI = GetTypeInfo(Index);
 358
 359             if (TI == null)
 360             {
 361                 return typeof(String);  //Default type is string.
 362             }
 363             else
 364             {
 365                 return TI.Type;
 366             }
 367         }
 368
 369         /// <summary>
 370         /// Return the data DbType of the column at index <param name="Index"></param>.
 371         /// </summary>
 372         public DbType GetFieldDbType(Int32 Index)
 373         {
 374             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetFieldType");
 375
 376             CheckHaveResultSet();
 377
 378             NpgsqlBackendTypeInfo  TI = GetTypeInfo(Index);
 379
 380             if (TI == null)
 381             {
 382                 return DbType.String;
 383             }
 384             else
 385             {
 386                 //return TI.DBType;
 387                 return DbType.String;
 388             }
 389         }
 390
 391         /// <summary>
 392         /// Return the data NpgsqlDbType of the column at index <param name="Index"></param>.
 393         /// </summary>
 394         public NpgsqlDbType GetFieldNpgsqlDbType(Int32 Index)
 395         {
 396             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetFieldType");
 397
 398             CheckHaveResultSet();
 399
 400             NpgsqlBackendTypeInfo  TI = GetTypeInfo(Index);
 401
 402             if (TI == null)
 403             {
 404                 return NpgsqlDbType.Text;
 405             }
 406             else
 407             {
 408                 return TI.NpgsqlDbType;
 409
 410             }
 411         }
 412
 413
 414         /// <summary>
 415         /// Return the value of the column at index <param name="Index"></param>.
 416         /// </summary>
 417         public Object GetValue(Int32 Index)
 418         {
 419             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetValue");
 420
 421             if (Index < 0 || Index >= _currentResultset.RowDescription.NumFields)
 422             {
 423                 throw new IndexOutOfRangeException("Column index out of range");
 424             }
 425
 426             CheckHaveRow();
 427
 428             return ((NpgsqlAsciiRow)_currentResultset[_rowIndex])[Index];
 429         }
 430
 431         /// <summary>
 432         /// Copy values from each column in the current row into <param name="Values"></param>.
 433         /// </summary>
 434         /// <returns>The number of column values copied.</returns>
 435         public Int32 GetValues(Object[] Values)
 436         {
 437             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetValues");
 438
 439             CheckHaveRow();
 440
 441             // Only the number of elements in the array are filled.
 442             // It's also possible to pass an array with more that FieldCount elements.
 443             Int32 maxColumnIndex = (Values.Length < FieldCount) ? Values.Length : FieldCount;
 444
 445             for (Int32 i = 0; i < maxColumnIndex; i++)
 446             {
 447                 Values[i] = GetValue(i);
 448             }
 449
 450             return maxColumnIndex;
 451
 452         }
 453
 454         /// <summary>
 455         /// Return the column name of the column named <param name="Name"></param>.
 456         /// </summary>
 457         public Int32 GetOrdinal(String Name)
 458         {
 459             CheckHaveResultSet();
 460             return _currentResultset.RowDescription.FieldIndex(Name);
 461         }
 462
 463         /// <summary>
 464         /// Gets the value of a column in its native format.
 465         /// </summary>
 466         public Object this [ Int32 i ]
 467         {
 468             get
 469             {
 470                 NpgsqlEventLog.LogIndexerGet(LogLevel.Debug, CLASSNAME, i);
 471                 return GetValue(i);
 472             }
 473         }
 474
 475         /// <summary>
 476         /// Gets the value of a column in its native format.
 477         /// </summary>
 478         public Object this [ String name ]
 479         {
 480             get
 481             {
 482                 NpgsqlEventLog.LogIndexerGet(LogLevel.Debug, CLASSNAME, name);
 483                 Int32 fieldIndex = _currentResultset.RowDescription.FieldIndex(name);
 484                 if (fieldIndex == -1)
 485                     throw new IndexOutOfRangeException("Field not found");
 486                 return GetValue(fieldIndex);
 487             }
 488         }
 489
 490         /// <summary>
 491         /// Gets the value of a column as Boolean.
 492         /// </summary>
 493         public Boolean GetBoolean(Int32 i)
 494         {
 495             // Should this be done using the GetValue directly and not by converting to String
 496             // and parsing from there?
 497             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetBoolean");
 498
 499             return (Boolean) GetValue(i);
 500         }
 501
 502         /// <summary>
 503         /// Gets the value of a column as Byte.  Not implemented.
 504         /// </summary>
 505         public Byte GetByte(Int32 i)
 506         {
 507             throw new NotImplementedException();
 508         }
 509
 510         /// <summary>
 511         /// Gets raw data from a column.
 512         /// </summary>
 513         public Int64 GetBytes(Int32 i, Int64 fieldOffset, Byte[] buffer, Int32 bufferoffset, Int32 length)
 514         {
 515
 516             Byte[] result;
 517
 518             result = (Byte[]) GetValue(i);
 519
 520             if (buffer == null)
 521                 return result.Length;
 522
 523
 524             // We just support read all the field for while. So, any fieldOffset value other than 0 will not read
 525             // anything and return 0.
 526
 527             if (fieldOffset != 0)
 528                 return 0;
 529
 530             // [TODO] Implement blob support.
 531
 532             result.CopyTo(buffer, 0);
 533
 534
 535             return result.Length;
 536
 537         }
 538
 539         /// <summary>
 540         /// Gets the value of a column as Char.  Not implemented.
 541         /// </summary>
 542         public Char GetChar(Int32 i)
 543         {
 544             throw new NotImplementedException();
 545         }
 546
 547         /// <summary>
 548         /// Gets raw data from a column.
 549         /// </summary>
 550         public Int64 GetChars(Int32 i, Int64 fieldoffset, Char[] buffer, Int32 bufferoffset, Int32 length)
 551         {
 552             String              str;
 553
 554             str = GetString(i);
 555             if (buffer == null)
 556                 return str.Length;
 557
 558             str.ToCharArray(bufferoffset, length).CopyTo(buffer, 0);
 559             return buffer.GetLength(0);
 560         }
 561
 562         /// <summary>
 563         /// Gets the value of a column converted to a Guid.  Not implemented.
 564         /// </summary>
 565         public Guid GetGuid(Int32 i)
 566         {
 567             throw new NotImplementedException();
 568         }
 569
 570         /// <summary>
 571         /// Gets the value of a column as Int16.
 572         /// </summary>
 573         public Int16 GetInt16(Int32 i)
 574         {
 575             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetInt16");
 576
 577             return (Int16) GetValue(i);
 578         }
 579
 580         /// <summary>
 581         /// Gets the value of a column as Int32.
 582         /// </summary>
 583         public Int32 GetInt32(Int32 i)
 584         {
 585             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetInt32");
 586
 587             return (Int32) GetValue(i);
 588         }
 589
 590         /// <summary>
 591         /// Gets the value of a column as Int64.
 592         /// </summary>
 593         public Int64 GetInt64(Int32 i)
 594         {
 595             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetInt64");
 596
 597             return (Int64) GetValue(i);
 598         }
 599
 600         /// <summary>
 601         /// Gets the value of a column as Single.
 602         /// </summary>
 603         public Single GetFloat(Int32 i)
 604         {
 605             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetFloat");
 606
 607             return (Single) GetValue(i);
 608         }
 609
 610         /// <summary>
 611         /// Gets the value of a column as Double.
 612         /// </summary>
 613         public Double GetDouble(Int32 i)
 614         {
 615             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetDouble");
 616
 617             return (Double) GetValue(i);
 618         }
 619
 620         /// <summary>
 621         /// Gets the value of a column as String.
 622         /// </summary>
 623         public String GetString(Int32 i)
 624         {
 625             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetString");
 626
 627             return (String) GetValue(i);
 628         }
 629
 630         /// <summary>
 631         /// Gets the value of a column as Decimal.
 632         /// </summary>
 633         public Decimal GetDecimal(Int32 i)
 634         {
 635             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetDecimal");
 636
 637             return (Decimal) GetValue(i);
 638         }
 639
 640         /// <summary>
 641         /// Gets the value of a column as DateTime.
 642         /// </summary>
 643         public DateTime GetDateTime(Int32 i)
 644         {
 645             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetDateTime");
 646
 647             return (DateTime) GetValue(i);
 648         }
 649
 650         /// <summary>
 651         /// Not implemented.
 652         /// </summary>
 653         public IDataReader GetData(Int32 i)
 654         {
 655             throw new NotImplementedException();
 656         }
 657
 658         /// <summary>
 659         /// Report whether the value in a column is DBNull.
 660         /// </summary>
 661         public Boolean IsDBNull(Int32 i)
 662         {
 663             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IsDBNull");
 664
 665             return (GetValue(i) == DBNull.Value);
 666         }
 667
 668         internal NpgsqlBackendTypeInfo GetTypeInfo(Int32 FieldIndex)
 669         {
 670             return _currentResultset.RowDescription[FieldIndex].type_info;
 671         }
 672
 673         private DataTable GetResultsetSchema()
 674         {
 675             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetResultsetSchema");
 676             DataTable result = null;
 677
 678             NpgsqlRowDescription rd = _currentResultset.RowDescription;
 679
 680             Int16 numFields = rd.NumFields;
 681             if(numFields > 0)
 682             {
 683                 result = new DataTable("SchemaTable");
 684
 685                 result.Columns.Add ("ColumnName", typeof (string));
 686                 result.Columns.Add ("ColumnOrdinal", typeof (int));
 687                 result.Columns.Add ("ColumnSize", typeof (int));
 688                 result.Columns.Add ("NumericPrecision", typeof (int));
 689                 result.Columns.Add ("NumericScale", typeof (int));
 690                 result.Columns.Add ("IsUnique", typeof (bool));
 691                 result.Columns.Add ("IsKey", typeof (bool));
 692                 result.Columns.Add ("BaseCatalogName", typeof (string));
 693                 result.Columns.Add ("BaseColumnName", typeof (string));
 694                 result.Columns.Add ("BaseSchemaName", typeof (string));
 695                 result.Columns.Add ("BaseTableName", typeof (string));
 696                 result.Columns.Add ("DataType", typeof(Type));
 697                 result.Columns.Add ("AllowDBNull", typeof (bool));
 698                 result.Columns.Add ("ProviderType", typeof (string));
 699                 result.Columns.Add ("IsAliased", typeof (bool));
 700                 result.Columns.Add ("IsExpression", typeof (bool));
 701                 result.Columns.Add ("IsIdentity", typeof (bool));
 702                 result.Columns.Add ("IsAutoIncrement", typeof (bool));
 703                 result.Columns.Add ("IsRowVersion", typeof (bool));
 704                 result.Columns.Add ("IsHidden", typeof (bool));
 705                 result.Columns.Add ("IsLong", typeof (bool));
 706                 result.Columns.Add ("IsReadOnly", typeof (bool));
 707
 708                 if (_connection.Connector.BackendProtocolVersion == ProtocolVersion.Version2)
 709                 {
 710                     FillSchemaTable_v2(result);
 711                 }
 712                 else if (_connection.Connector.BackendProtocolVersion == ProtocolVersion.Version3)
 713                 {
 714                     FillSchemaTable_v3(result);
 715                 }
 716             }
 717
 718             return result;
 719
 720         }
 721
 722         private void FillSchemaTable_v2(DataTable schema)
 723         {
 724             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "FillSchemaTable_v2");
 725             NpgsqlRowDescription rd = _currentResultset.RowDescription;
 726             ArrayList keyList = null;
 727
 728                         if ((_behavior & CommandBehavior.KeyInfo) == CommandBehavior.KeyInfo)
 729                         {
 730                                 keyList = GetPrimaryKeys(GetTableNameFromQuery());
 731                         }
 732
 733             DataRow row;
 734
 735             for (Int16 i = 0; i < rd.NumFields; i++)
 736             {
 737                 row = schema.NewRow();
 738
 739                 row["ColumnName"] = GetName(i);
 740                 row["ColumnOrdinal"] = i + 1;
 741                 if (rd[i].type_modifier != -1 && rd[i].type_info != null && (rd[i].type_info.Name == "varchar" || rd[i].type_info.Name == "bpchar"))
 742                     row["ColumnSize"] = rd[i].type_modifier - 4;
 743                 else if (rd[i].type_modifier != -1 && rd[i].type_info != null && (rd[i].type_info.Name == "bit" || rd[i].type_info.Name == "varbit"))
 744                     row["ColumnSize"] = rd[i].type_modifier;
 745                 else
 746                     row["ColumnSize"] = (int) rd[i].type_size;
 747                 if (rd[i].type_modifier != -1 && rd[i].type_info != null && rd[i].type_info.Name == "numeric")
 748                 {
 749                     row["NumericPrecision"] = ((rd[i].type_modifier-4)>>16)&ushort.MaxValue;
 750                     row["NumericScale"] = (rd[i].type_modifier-4)&ushort.MaxValue;
 751                 }
 752                 else
 753                 {
 754                     row["NumericPrecision"] = 0;
 755                     row["NumericScale"] = 0;
 756                 }
 757                 row["IsUnique"] = false;
 758                 row["IsKey"] = IsKey(GetName(i), keyList);
 759                 row["BaseCatalogName"] = "";
 760                 row["BaseSchemaName"] = "";
 761                 row["BaseTableName"] = "";
 762                 row["BaseColumnName"] = GetName(i);
 763                 row["DataType"] = GetFieldType(i);
 764                 row["AllowDBNull"] = IsNullable(null, i);
 765                 if (rd[i].type_info != null)
 766                 {
 767                     row["ProviderType"] = rd[i].type_info.Name;
 768                 }
 769                 row["IsAliased"] = false;
 770                 row["IsExpression"] = false;
 771                 row["IsIdentity"] = false;
 772                 row["IsAutoIncrement"] = false;
 773                 row["IsRowVersion"] = false;
 774                 row["IsHidden"] = false;
 775                 row["IsLong"] = false;
 776                 row["IsReadOnly"] = false;
 777
 778                 schema.Rows.Add(row);
 779             }
 780         }
 781
 782         private void FillSchemaTable_v3(DataTable schema)
 783         {
 784             NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "FillSchemaTable_v3");
 785             NpgsqlRowDescription rd = _currentResultset.RowDescription;
 786
 787                         Hashtable oidTableLookup = null;
 788                         KeyLookup keyLookup = new KeyLookup();
 789                         Hashtable columnLookup = null;
 790
 791                         if ((_behavior & CommandBehavior.KeyInfo) == CommandBehavior.KeyInfo)
 792                         {
 793                                 ArrayList tableOids = new ArrayList();
 794                                 for(short i=0; i<rd.NumFields; ++i)
 795                                 {
 796                                         if (rd[i].table_oid != 0 && !tableOids.Contains(rd[i].table_oid))
 797                                                 tableOids.Add(rd[i].table_oid);
 798                                 }
 799                                 oidTableLookup = GetTablesFromOids(tableOids);
 800
 801                                 if (oidTableLookup != null && oidTableLookup.Count == 1)
 802                                 {
 803                                         // only 1, but we can't index into the Hashtable
 804                                         foreach(DictionaryEntry entry in oidTableLookup)
 805                                         {
 806                                                 keyLookup = GetKeys((Int32)entry.Key);
 807                                         }
 808                                 }
 809
 810                                 columnLookup = GetColumns();
 811                         }
 812
 813             DataRow row;
 814             for (Int16 i = 0; i < rd.NumFields; i++)
 815             {
 816                 row = schema.NewRow();
 817
 818                                 string baseColumnName = GetBaseColumnName(columnLookup, i);
 819
 820                 row["ColumnName"] = GetName(i);
 821                 row["ColumnOrdinal"] = i + 1;
 822                 if (rd[i].type_modifier != -1 && rd[i].type_info != null && (rd[i].type_info.Name == "varchar" || rd[i].type_info.Name == "bpchar"))
 823                     row["ColumnSize"] = rd[i].type_modifier - 4;
 824                 else if (rd[i].type_modifier != -1 && rd[i].type_info != null && (rd[i].type_info.Name == "bit" || rd[i].type_info.Name == "varbit"))
 825                     row["ColumnSize"] = rd[i].type_modifier;
 826                 else
 827                     row["ColumnSize"] = (int) rd[i].type_size;
 828                 if (rd[i].type_modifier != -1 && rd[i].type_info != null && rd[i].type_info.Name == "numeric")
 829                 {
 830                     row["NumericPrecision"] = ((rd[i].type_modifier-4)>>16)&ushort.MaxValue;
 831                     row["NumericScale"] = (rd[i].type_modifier-4)&ushort.MaxValue;
 832                 }
 833                 else
 834                 {
 835                     row["NumericPrecision"] = 0;
 836                     row["NumericScale"] = 0;
 837                 }
 838                 row["IsUnique"] = IsUnique(keyLookup, baseColumnName);
 839                 row["IsKey"] = IsKey(keyLookup, baseColumnName);
 840                 if (rd[i].table_oid != 0 && oidTableLookup != null)
 841                 {
 842                     row["BaseCatalogName"] = ((object[])oidTableLookup[rd[i].table_oid])[Tables.table_catalog];
 843                     row["BaseSchemaName"] = ((object[])oidTableLookup[rd[i].table_oid])[Tables.table_schema];
 844                     row["BaseTableName"] = ((object[])oidTableLookup[rd[i].table_oid])[Tables.table_name];
 845                 }
 846                 else
 847                 {
 848                     row["BaseCatalogName"] = "";
 849                     row["BaseSchemaName"] = "";
 850                     row["BaseTableName"] = "";
 851                 }
 852                 row["BaseColumnName"] = baseColumnName;
 853                 row["DataType"] = GetFieldType(i);
 854                 row["AllowDBNull"] = IsNullable(columnLookup, i);
 855                 if (rd[i].type_info != null)
 856                 {
 857                     row["ProviderType"] = rd[i].type_info.Name;
 858                 }
 859                 row["IsAliased"] = string.CompareOrdinal((string)row["ColumnName"], baseColumnName) != 0;
 860                 row["IsExpression"] = false;
 861                 row["IsIdentity"] = false;
 862                 row["IsAutoIncrement"] = IsAutoIncrement(columnLookup, i);
 863                 row["IsRowVersion"] = false;
 864                 row["IsHidden"] = false;
 865                 row["IsLong"] = false;
 866                 row["IsReadOnly"] = false;
 867
 868                 schema.Rows.Add(row);
 869             }
 870         }
 871
 872
 873         private Boolean IsKey(String ColumnName, ArrayList ListOfKeys)
 874         {
 875             if (ListOfKeys == null || ListOfKeys.Count == 0)
 876                 return false;
 877
 878             foreach(String s in ListOfKeys)
 879             {
 880
 881                 if (s == ColumnName)
 882                     return true;
 883             }
 884
 885             return false;
 886         }
 887
 888         private ArrayList GetPrimaryKeys(String tablename)
 889         {
 890
 891             if (tablename == String.Empty)
 892                 return null;
 893
 894             String getPKColumns = "select a.attname from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i  WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid  AND a.attrelid=ci.oid AND i.indisprimary AND ct.relname = :tablename";
 895
 896             ArrayList result = new ArrayList();
 897             NpgsqlConnection metadataConn = _connection.Clone();
 898
 899             NpgsqlCommand c = new NpgsqlCommand(getPKColumns, metadataConn);
 900             c.Parameters.Add(new NpgsqlParameter("tablename", NpgsqlDbType.Text));
 901             c.Parameters["tablename"].Value = tablename;
 902
 903
 904             NpgsqlDataReader dr = c.ExecuteReader();
 905
 906
 907             while (dr.Read())
 908                 result.Add(dr[0]);
 909
 910
 911             metadataConn.Close();
 912
 913             return result;
 914         }
 915
 916                 private bool IsKey(KeyLookup keyLookup, string fieldName)
 917                 {
 918                         if (keyLookup.primaryKey == null || keyLookup.primaryKey.Count == 0)
 919                                 return false;
 920
 921                         for (int i=0; i<keyLookup.primaryKey.Count; ++i)
 922                         {
 923                 if (fieldName == (String)keyLookup.primaryKey[i])
 924                                         return true;
 925                         }
 926
 927                         return false;
 928                 }
 929
 930                 private bool IsUnique(KeyLookup keyLookup, string fieldName)
 931                 {
 932                         if (keyLookup.uniqueColumns == null || keyLookup.uniqueColumns.Count == 0)
 933                                 return false;
 934
 935                         for (int i=0; i<keyLookup.uniqueColumns.Count; ++i)
 936                         {
 937                 if (fieldName == (String)keyLookup.uniqueColumns[i])
 938                                         return true;
 939                         }
 940
 941                         return false;
 942                 }
 943
 944                 private struct KeyLookup
 945                 {
 946                         /// <summary>
 947                         /// Contains the column names as the keys
 948                         /// </summary>
 949                         public ArrayList primaryKey;
 950                         /// <summary>
 951                         /// Contains all unique columns
 952                         /// </summary>
 953                         public ArrayList uniqueColumns;
 954                 }
 955
 956                 private KeyLookup GetKeys(Int32 tableOid)
 957                 {
 958
 959                         string getKeys = "select a.attname, ci.relname, i.indisprimary from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisunique AND ct.oid = :tableOid order by ci.relname";
 960
 961                         KeyLookup lookup = new KeyLookup();
 962                         lookup.primaryKey = new ArrayList();
 963                         lookup.uniqueColumns = new ArrayList();
 964
 965                         using (NpgsqlConnection metadataConn = _connection.Clone())
 966                         {
 967                                 NpgsqlCommand c = new NpgsqlCommand(getKeys, metadataConn);
 968                                 c.Parameters.Add(new NpgsqlParameter("tableOid", NpgsqlDbType.Integer)).Value = tableOid;
 969
 970                                 using (NpgsqlDataReader dr = c.ExecuteReader())
 971                                 {
 972                                         string previousKeyName = null;
 973                                         string possiblyUniqueColumn = null;
 974                                         string columnName;
 975                                         string currentKeyName;
 976                                         // loop through adding any column that is primary to the primary key list
 977                                         // add any column that is the only column for that key to the unique list
 978                                         // unique here doesn't mean general unique constraint (with possibly multiple columns)
 979                                         // it means all values in this single column must be unique
 980                                         while (dr.Read())
 981                                         {
 982
 983                                                 columnName = dr.GetString(0);
 984                                                 currentKeyName = dr.GetString(1);
 985                                                 // if i.indisprimary
 986                                                 if (dr.GetBoolean(2))
 987                                                 {
 988                                                         // add column name as part of the primary key
 989                                                         lookup.primaryKey.Add(columnName);
 990                                                 }
 991                                                 if (currentKeyName != previousKeyName)
 992                                                 {
 993                                                         if (possiblyUniqueColumn != null)
 994                                                         {
 995                                                                 lookup.uniqueColumns.Add(possiblyUniqueColumn);
 996                                                         }
 997                                                         possiblyUniqueColumn = columnName;
 998                                                 }
 999                                                 else
1000                                                 {
1001                                                         possiblyUniqueColumn = null;
1002                                                 }
1003                                                 previousKeyName = currentKeyName;
1004                                         }
1005                                         // if finished reading and have a possiblyUniqueColumn name that is
1006                                         // not null, then it is the name of a unique column
1007                                         if (possiblyUniqueColumn != null)
1008                                                 lookup.uniqueColumns.Add(possiblyUniqueColumn);
1009                                 }
1010                         }
1011
1012                         return lookup;
1013                 }
1014
1015         private Boolean IsNullable(Hashtable columnLookup, Int32 FieldIndex)
1016         {
1017             if (columnLookup == null || _currentResultset.RowDescription[FieldIndex].table_oid == 0)
1018                 return true;
1019
1020             string lookupKey = _currentResultset.RowDescription[FieldIndex].table_oid.ToString() + "," + _currentResultset.RowDescription[FieldIndex].column_attribute_number;
1021             object[] row = (object[])columnLookup[lookupKey];
1022             if (row != null)
1023                 return !(bool)row[Columns.column_notnull];
1024             else
1025                 return true;
1026         }
1027
1028         private string GetBaseColumnName(Hashtable columnLookup, Int32 FieldIndex)
1029         {
1030             if (columnLookup == null || _currentResultset.RowDescription[FieldIndex].table_oid == 0)
1031                 return GetName(FieldIndex);
1032
1033             string lookupKey = _currentResultset.RowDescription[FieldIndex].table_oid.ToString() + "," + _currentResultset.RowDescription[FieldIndex].column_attribute_number;
1034             object[] row = (object[])columnLookup[lookupKey];
1035             if (row != null)
1036                 return (string)row[Columns.column_name];
1037             else
1038                 return GetName(FieldIndex);
1039         }
1040
1041                 private bool IsAutoIncrement(Hashtable columnLookup, Int32 FieldIndex)
1042                 {
1043                         if (columnLookup == null || _currentResultset.RowDescription[FieldIndex].table_oid == 0)
1044                                 return false;
1045
1046                         string lookupKey = _currentResultset.RowDescription[FieldIndex].table_oid.ToString() + "," + _currentResultset.RowDescription[FieldIndex].column_attribute_number;
1047                         object[] row = (object[])columnLookup[lookupKey];
1048                         if (row != null)
1049                                 return row[Columns.column_default].ToString().StartsWith("nextval(");
1050                         else
1051                                 return true;
1052                 }
1053
1054
1055         ///<summary>
1056         /// This methods parses the command text and tries to get the tablename
1057         /// from it.
1058         ///</summary>
1059         private String GetTableNameFromQuery()
1060         {
1061             Int32 fromClauseIndex = _command.CommandText.ToLower().IndexOf("from");
1062
1063             String tableName = _command.CommandText.Substring(fromClauseIndex + 4).Trim();
1064
1065             if (tableName == String.Empty)
1066                 return String.Empty;
1067
1068             /*if (tableName.EndsWith("."));
1069                 return String.Empty;
1070               */
1071             foreach (Char c in tableName.Substring (0, tableName.Length - 1))
1072             if (!Char.IsLetterOrDigit (c) && c != '_' && c != '.')
1073                 return String.Empty;
1074
1075
1076             return tableName;
1077
1078         }
1079
1080         private struct Tables
1081         {
1082             public const int table_catalog = 0;
1083             public const int table_schema = 1;
1084             public const int table_name = 2;
1085             public const int table_id = 3;
1086         }
1087
1088         private Hashtable GetTablesFromOids(ArrayList oids)
1089         {
1090             if (oids.Count == 0)
1091                 return null;
1092
1093             StringBuilder sb = new StringBuilder();
1094
1095             // the column index is used to find data.
1096             // any changes to the order of the columns needs to be reflected in struct Tables
1097             sb.Append("SELECT current_database() AS table_catalog, nc.nspname AS table_schema, c.relname AS table_name, c.oid as table_id");
1098             sb.Append(" FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND (c.relkind = 'r' OR c.relkind = 'v') AND c.oid IN (");
1099             bool first = true;
1100             foreach(int oid in oids)
1101             {
1102                 if (!first)
1103                     sb.Append(',');
1104                 sb.Append(oid);
1105                 first = false;
1106             }
1107             sb.Append(')');
1108
1109             using (NpgsqlConnection connection = _connection.Clone())
1110             using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
1111             using (NpgsqlDataReader reader = command.ExecuteReader())
1112             {
1113                 Hashtable oidLookup = new Hashtable();
1114                 int columnCount = reader.FieldCount;
1115                 while (reader.Read())
1116                 {
1117                     object[] values = new object[columnCount];
1118                     reader.GetValues(values);
1119                     oidLookup[Convert.ToInt32(reader[Tables.table_id])] = values;
1120                 }
1121                 return oidLookup;
1122             }
1123         }
1124
1125         private struct Columns
1126         {
1127             public const int column_name = 0;
1128             public const int column_notnull = 1;
1129             public const int table_id = 2;
1130             public const int column_num = 3;
1131                         public const int column_default = 4;
1132         }
1133
1134         private Hashtable GetColumns()
1135         {
1136             StringBuilder sb = new StringBuilder();
1137
1138             // the column index is used to find data.
1139             // any changes to the order of the columns needs to be reflected in struct Columns
1140             sb.Append("SELECT a.attname AS column_name, a.attnotnull AS column_notnull, a.attrelid AS table_id, a.attnum AS column_num, d.adsrc as column_default");
1141             sb.Append(" FROM pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND (");
1142             bool first = true;
1143             for(int i=0; i<_currentResultset.RowDescription.NumFields; ++i)
1144             {
1145                 if (_currentResultset.RowDescription[i].table_oid != 0)
1146                 {
1147                     if (!first)
1148                         sb.Append(" OR ");
1149                     sb.AppendFormat("(a.attrelid={0} AND a.attnum={1})", _currentResultset.RowDescription[i].table_oid, _currentResultset.RowDescription[i].column_attribute_number);
1150                     first = false;
1151                 }
1152             }
1153             sb.Append(')');
1154
1155             // if the loop ended without setting first to false, then there will be no results from the query
1156             if (first)
1157                 return null;
1158
1159             using (NpgsqlConnection connection = _connection.Clone())
1160             using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
1161             using (NpgsqlDataReader reader = command.ExecuteReader())
1162             {
1163                 Hashtable columnLookup = new Hashtable();
1164                 int columnCount = reader.FieldCount;
1165                 while(reader.Read())
1166                 {
1167                     object[] values = new object[columnCount];
1168                     reader.GetValues(values);
1169                     columnLookup[reader[Columns.table_id].ToString() + "," + reader[Columns.column_num].ToString()] = values;
1170                 }
1171                 return columnLookup;
1172             }
1173         }
1174
1175         IEnumerator IEnumerable.GetEnumerator ()
1176         {
1177             return new System.Data.Common.DbEnumerator (this);
1178         }
1179     }
1180 }