System.Data.SQLite
Check-in [bdd8e44fd0]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Implement column name/index caching in the SQLiteDataReader class.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bdd8e44fd06f964695538325dd763ff04a72834f
User & Date: mistachkin 2012-10-06 04:38:35
Context
2012-10-06
05:57
Make the SQLiteBase.ResetConnection method throw an exception if the connection handle is closed or invalid. check-in: 1045210882 user: mistachkin tags: trunk
04:38
Implement column name/index caching in the SQLiteDataReader class. check-in: bdd8e44fd0 user: mistachkin tags: trunk
2012-10-05
00:17
More refinements to disposal locking semantics on the .NET Compact Framework. check-in: ac5f4cc084 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to System.Data.SQLite/SQLiteDataReader.cs.

    43     43       /// Number of records affected by the insert/update statements executed on the command
    44     44       /// </summary>
    45     45       private int _rowsAffected;
    46     46       /// <summary>
    47     47       /// Count of fields (columns) in the row-returning statement currently being processed
    48     48       /// </summary>
    49     49       private int _fieldCount;
           50  +    /// <summary>
           51  +    /// Maps the field (column) names to their corresponding indexes within the results.
           52  +    /// </summary>
           53  +    private Dictionary<string, int> _fieldIndexes;
    50     54       /// <summary>
    51     55       /// Datatypes of active fields (columns) in the current statement, used for type-restricting data
    52     56       /// </summary>
    53     57       private SQLiteType[] _fieldTypeArray;
    54     58   
    55     59       /// <summary>
    56     60       /// The behavior of the datareader
................................................................................
   203    207               if (_disposeCommand)
   204    208                 _command.Dispose();
   205    209             }
   206    210           }
   207    211   
   208    212           _command = null;
   209    213           _activeStatement = null;
          214  +        _fieldIndexes = null;
   210    215           _fieldTypeArray = null;
   211    216         }
   212    217         finally
   213    218         {
   214    219           if (_keyInfo != null)
   215    220           {
   216    221             _keyInfo.Dispose();
................................................................................
   639    644       /// <returns>The int i of the column</returns>
   640    645       public override int GetOrdinal(string name)
   641    646       {
   642    647         CheckDisposed();
   643    648         CheckClosed();
   644    649         SQLiteCommand.Check(_command);
   645    650   
   646         -      int r = _activeStatement._sql.ColumnIndex(_activeStatement, name);
          651  +      //
          652  +      // NOTE: First, check if the column name cache has been initialized yet.
          653  +      //       If not, do it now.
          654  +      //
          655  +      if (_fieldIndexes == null)
          656  +          _fieldIndexes = new Dictionary<string, int>(new ColumnNameComparer());
          657  +
          658  +      //
          659  +      // NOTE: Next, see if the index for the requested column name has been
          660  +      //       cached already.  If so, return the cached value.  Otherwise,
          661  +      //       lookup the value and then cache the result for future use.
          662  +      //
          663  +      int r;
          664  +
          665  +      if (!_fieldIndexes.TryGetValue(name, out r))
          666  +      {
          667  +          r = _activeStatement._sql.ColumnIndex(_activeStatement, name);
          668  +
   647    669         if (r == -1 && _keyInfo != null)
   648    670         {
   649    671           r = _keyInfo.GetOrdinal(name);
   650    672           if (r > -1) r += VisibleFieldCount;
   651    673         }
          674  +
          675  +          _fieldIndexes.Add(name, r);
          676  +      }
   652    677   
   653    678         return r;
   654    679       }
   655    680   
   656    681       /// <summary>
   657    682       /// Schema information in SQLite is difficult to map into .NET conventions, so a lot of work must be done
   658    683       /// to gather the necessary information so it can be represented in an ADO.NET manner.
................................................................................
   660    685       /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
   661    686       public override DataTable GetSchemaTable()
   662    687       {
   663    688         CheckDisposed();
   664    689         return GetSchemaTable(true, false);
   665    690       }
   666    691   
   667         -    private class ColumnParent : IEqualityComparer<ColumnParent>
          692  +    ///////////////////////////////////////////////////////////////////////////
          693  +
          694  +    #region ColumnNameComparer Class
          695  +    private sealed class ColumnNameComparer : IEqualityComparer<string>
   668    696       {
          697  +        #region IEqualityComparer<string> Members
          698  +        public bool Equals(string x, string y)
          699  +        {
          700  +            if ((x == null) && (y == null))
          701  +            {
          702  +                return true;
          703  +            }
          704  +            else if ((x == null) || (y == null))
          705  +            {
          706  +                return false;
          707  +            }
          708  +            else
          709  +            {
          710  +                return String.Equals(x, y, StringComparison.OrdinalIgnoreCase);
          711  +            }
          712  +        }
          713  +
          714  +        ///////////////////////////////////////////////////////////////////////
          715  +
          716  +        public int GetHashCode(string obj)
          717  +        {
          718  +            if (obj == null)
          719  +                return 0;
          720  +
          721  +            return obj.GetHashCode();
          722  +        }
          723  +        #endregion
          724  +    }
          725  +    #endregion
          726  +
          727  +    ///////////////////////////////////////////////////////////////////////////
          728  +
          729  +    #region ColumnParent Class
          730  +    private sealed class ColumnParent : IEqualityComparer<ColumnParent>
          731  +    {
          732  +        #region Public Fields
   669    733           public string DatabaseName;
   670    734           public string TableName;
   671    735           public string ColumnName;
          736  +        #endregion
   672    737   
          738  +        ///////////////////////////////////////////////////////////////////////
          739  +
          740  +        #region Public Constructors
   673    741           public ColumnParent()
   674    742           {
   675    743               // do nothing.
   676    744           }
          745  +
          746  +        ///////////////////////////////////////////////////////////////////////
   677    747   
   678    748           public ColumnParent(
   679    749               string databaseName,
   680    750               string tableName,
   681    751               string columnName
   682    752               )
   683    753               : this()
   684    754           {
   685    755               this.DatabaseName = databaseName;
   686    756               this.TableName = tableName;
   687    757               this.ColumnName = columnName;
   688    758           }
          759  +        #endregion
          760  +
          761  +        ///////////////////////////////////////////////////////////////////////
   689    762   
   690    763           #region IEqualityComparer<ColumnParent> Members
   691    764           public bool Equals(ColumnParent x, ColumnParent y)
   692    765           {
   693    766               if ((x == null) && (y == null))
   694    767               {
   695    768                   return true;
................................................................................
   718    791                       return false;
   719    792                   }
   720    793   
   721    794                   return true;
   722    795               }
   723    796           }
   724    797   
          798  +        ///////////////////////////////////////////////////////////////////////
          799  +
   725    800           public int GetHashCode(ColumnParent obj)
   726    801           {
   727    802               int result = 0;
   728    803   
   729    804               if ((obj != null) && (obj.DatabaseName != null))
   730    805                   result ^= obj.DatabaseName.GetHashCode();
   731    806   
................................................................................
   735    810               if ((obj != null) && (obj.ColumnName != null))
   736    811                   result ^= obj.ColumnName.GetHashCode();
   737    812   
   738    813               return result;
   739    814           }
   740    815           #endregion
   741    816       }
          817  +    #endregion
          818  +
          819  +    ///////////////////////////////////////////////////////////////////////////
   742    820   
   743    821       private static void GetStatementColumnParents(
   744    822           SQLiteBase sql,
   745    823           SQLiteStatement stmt,
   746    824           int fieldCount,
   747    825           ref Dictionary<ColumnParent, List<int>> parentToColumns,
   748    826           ref Dictionary<int, ColumnParent> columnToParent
................................................................................
  1211   1289               _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false
  1212   1290             }
  1213   1291           }
  1214   1292   
  1215   1293           // Ahh, we found a row-returning resultset eligible to be returned!
  1216   1294           _activeStatement = stmt;
  1217   1295           _fieldCount = fieldCount;
         1296  +        _fieldIndexes = null;
  1218   1297           _fieldTypeArray = null;
  1219   1298   
  1220   1299           if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
  1221   1300             LoadKeyInfo();
  1222   1301   
  1223   1302           return true;
  1224   1303         }

Changes to Tests/basic.eagle.

  1924   1924     cleanupDb $fileName
  1925   1925   
  1926   1926     unset -nocomplain output result db fileName
  1927   1927   } -constraints \
  1928   1928   {eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
  1929   1929   regexp -result {^0 12341234 1 \{System\.Data\.SQLite\.SQLiteException\
  1930   1930   \(0x80004005\): SQL logic error or missing database.*?\} 0 1234123412341234$}}
         1931  +
         1932  +###############################################################################
         1933  +
         1934  +runTest {test data-1.39 {column name and index lookup} -setup {
         1935  +  setupDb [set fileName data-1.39.db]
         1936  +} -body {
         1937  +  sql execute $db {
         1938  +    CREATE TABLE t1(x, y, z);
         1939  +    INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);
         1940  +  }
         1941  +
         1942  +  set dataReader [sql execute -execute reader -format datareader \
         1943  +      -alias $db "SELECT x, y, z FROM t1;"]
         1944  +
         1945  +  set result [list]
         1946  +
         1947  +  while {[$dataReader Read]} {
         1948  +    lappend result \
         1949  +        [list [$dataReader GetName 0] [$dataReader GetOrdinal x] \
         1950  +            [$dataReader Item x]] \
         1951  +        [list [$dataReader GetName 1] [$dataReader GetOrdinal y] \
         1952  +            [$dataReader Item y]] \
         1953  +        [list [$dataReader GetName 2] [$dataReader GetOrdinal z] \
         1954  +            [$dataReader Item z]]
         1955  +  }
         1956  +
         1957  +  set result
         1958  +} -cleanup {
         1959  +  unset -nocomplain dataReader
         1960  +
         1961  +  cleanupDb $fileName
         1962  +
         1963  +  unset -nocomplain result db fileName
         1964  +} -constraints \
         1965  +{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
         1966  +{{x 0 1} {y 1 foo} {z 2 1234}}}
  1931   1967   
  1932   1968   ###############################################################################
  1933   1969   
  1934   1970   unset -nocomplain systemDataSQLiteDllFile systemDataSQLiteLinqDllFile \
  1935   1971       testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile
  1936   1972   
  1937   1973   ###############################################################################
  1938   1974   
  1939   1975   runSQLiteTestEpilogue
  1940   1976   runTestEpilogue