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 Unified Diffs Show Whitespace Changes Patch

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

43
44
45
46
47
48
49




50
51
52
53
54
55
56
...
203
204
205
206
207
208
209

210
211
212
213
214
215
216
...
639
640
641
642
643
644
645
















646

647
648
649
650
651



652
653
654
655
656
657
658
...
660
661
662
663
664
665
666






































667
668

669
670
671

672



673
674
675
676


677
678
679
680
681
682
683
684
685
686
687
688



689
690
691
692
693
694
695
...
718
719
720
721
722
723
724


725
726
727
728
729
730
731
...
735
736
737
738
739
740
741



742
743
744
745
746
747
748
....
1211
1212
1213
1214
1215
1216
1217

1218
1219
1220
1221
1222
1223
1224
    /// Number of records affected by the insert/update statements executed on the command
    /// </summary>
    private int _rowsAffected;
    /// <summary>
    /// Count of fields (columns) in the row-returning statement currently being processed
    /// </summary>
    private int _fieldCount;




    /// <summary>
    /// Datatypes of active fields (columns) in the current statement, used for type-restricting data
    /// </summary>
    private SQLiteType[] _fieldTypeArray;

    /// <summary>
    /// The behavior of the datareader
................................................................................
            if (_disposeCommand)
              _command.Dispose();
          }
        }

        _command = null;
        _activeStatement = null;

        _fieldTypeArray = null;
      }
      finally
      {
        if (_keyInfo != null)
        {
          _keyInfo.Dispose();
................................................................................
    /// <returns>The int i of the column</returns>
    public override int GetOrdinal(string name)
    {
      CheckDisposed();
      CheckClosed();
      SQLiteCommand.Check(_command);

















      int r = _activeStatement._sql.ColumnIndex(_activeStatement, name);

      if (r == -1 && _keyInfo != null)
      {
        r = _keyInfo.GetOrdinal(name);
        if (r > -1) r += VisibleFieldCount;
      }




      return r;
    }

    /// <summary>
    /// Schema information in SQLite is difficult to map into .NET conventions, so a lot of work must be done
    /// to gather the necessary information so it can be represented in an ADO.NET manner.
................................................................................
    /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
    public override DataTable GetSchemaTable()
    {
      CheckDisposed();
      return GetSchemaTable(true, false);
    }







































    private class ColumnParent : IEqualityComparer<ColumnParent>
    {

        public string DatabaseName;
        public string TableName;
        public string ColumnName;





        public ColumnParent()
        {
            // do nothing.
        }



        public ColumnParent(
            string databaseName,
            string tableName,
            string columnName
            )
            : this()
        {
            this.DatabaseName = databaseName;
            this.TableName = tableName;
            this.ColumnName = columnName;
        }




        #region IEqualityComparer<ColumnParent> Members
        public bool Equals(ColumnParent x, ColumnParent y)
        {
            if ((x == null) && (y == null))
            {
                return true;
................................................................................
                    return false;
                }

                return true;
            }
        }



        public int GetHashCode(ColumnParent obj)
        {
            int result = 0;

            if ((obj != null) && (obj.DatabaseName != null))
                result ^= obj.DatabaseName.GetHashCode();

................................................................................
            if ((obj != null) && (obj.ColumnName != null))
                result ^= obj.ColumnName.GetHashCode();

            return result;
        }
        #endregion
    }




    private static void GetStatementColumnParents(
        SQLiteBase sql,
        SQLiteStatement stmt,
        int fieldCount,
        ref Dictionary<ColumnParent, List<int>> parentToColumns,
        ref Dictionary<int, ColumnParent> columnToParent
................................................................................
            _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false
          }
        }

        // Ahh, we found a row-returning resultset eligible to be returned!
        _activeStatement = stmt;
        _fieldCount = fieldCount;

        _fieldTypeArray = null;

        if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
          LoadKeyInfo();

        return true;
      }







>
>
>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>





>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
|
|
|
>
|
>
>
>




>
>












>
>
>







 







>
>







 







>
>
>







 







>







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
...
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
...
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
...
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
....
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
    /// Number of records affected by the insert/update statements executed on the command
    /// </summary>
    private int _rowsAffected;
    /// <summary>
    /// Count of fields (columns) in the row-returning statement currently being processed
    /// </summary>
    private int _fieldCount;
    /// <summary>
    /// Maps the field (column) names to their corresponding indexes within the results.
    /// </summary>
    private Dictionary<string, int> _fieldIndexes;
    /// <summary>
    /// Datatypes of active fields (columns) in the current statement, used for type-restricting data
    /// </summary>
    private SQLiteType[] _fieldTypeArray;

    /// <summary>
    /// The behavior of the datareader
................................................................................
            if (_disposeCommand)
              _command.Dispose();
          }
        }

        _command = null;
        _activeStatement = null;
        _fieldIndexes = null;
        _fieldTypeArray = null;
      }
      finally
      {
        if (_keyInfo != null)
        {
          _keyInfo.Dispose();
................................................................................
    /// <returns>The int i of the column</returns>
    public override int GetOrdinal(string name)
    {
      CheckDisposed();
      CheckClosed();
      SQLiteCommand.Check(_command);

      //
      // NOTE: First, check if the column name cache has been initialized yet.
      //       If not, do it now.
      //
      if (_fieldIndexes == null)
          _fieldIndexes = new Dictionary<string, int>(new ColumnNameComparer());

      //
      // NOTE: Next, see if the index for the requested column name has been
      //       cached already.  If so, return the cached value.  Otherwise,
      //       lookup the value and then cache the result for future use.
      //
      int r;

      if (!_fieldIndexes.TryGetValue(name, out r))
      {
          r = _activeStatement._sql.ColumnIndex(_activeStatement, name);

          if (r == -1 && _keyInfo != null)
          {
              r = _keyInfo.GetOrdinal(name);
              if (r > -1) r += VisibleFieldCount;
          }

          _fieldIndexes.Add(name, r);
      }

      return r;
    }

    /// <summary>
    /// Schema information in SQLite is difficult to map into .NET conventions, so a lot of work must be done
    /// to gather the necessary information so it can be represented in an ADO.NET manner.
................................................................................
    /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
    public override DataTable GetSchemaTable()
    {
      CheckDisposed();
      return GetSchemaTable(true, false);
    }

    ///////////////////////////////////////////////////////////////////////////

    #region ColumnNameComparer Class
    private sealed class ColumnNameComparer : IEqualityComparer<string>
    {
        #region IEqualityComparer<string> Members
        public bool Equals(string x, string y)
        {
            if ((x == null) && (y == null))
            {
                return true;
            }
            else if ((x == null) || (y == null))
            {
                return false;
            }
            else
            {
                return String.Equals(x, y, StringComparison.OrdinalIgnoreCase);
            }
        }

        ///////////////////////////////////////////////////////////////////////

        public int GetHashCode(string obj)
        {
            if (obj == null)
                return 0;

            return obj.GetHashCode();
        }
        #endregion
    }
    #endregion

    ///////////////////////////////////////////////////////////////////////////

    #region ColumnParent Class
    private sealed class ColumnParent : IEqualityComparer<ColumnParent>
    {
        #region Public Fields
        public string DatabaseName;
        public string TableName;
        public string ColumnName;
        #endregion

        ///////////////////////////////////////////////////////////////////////

        #region Public Constructors
        public ColumnParent()
        {
            // do nothing.
        }

        ///////////////////////////////////////////////////////////////////////

        public ColumnParent(
            string databaseName,
            string tableName,
            string columnName
            )
            : this()
        {
            this.DatabaseName = databaseName;
            this.TableName = tableName;
            this.ColumnName = columnName;
        }
        #endregion

        ///////////////////////////////////////////////////////////////////////

        #region IEqualityComparer<ColumnParent> Members
        public bool Equals(ColumnParent x, ColumnParent y)
        {
            if ((x == null) && (y == null))
            {
                return true;
................................................................................
                    return false;
                }

                return true;
            }
        }

        ///////////////////////////////////////////////////////////////////////

        public int GetHashCode(ColumnParent obj)
        {
            int result = 0;

            if ((obj != null) && (obj.DatabaseName != null))
                result ^= obj.DatabaseName.GetHashCode();

................................................................................
            if ((obj != null) && (obj.ColumnName != null))
                result ^= obj.ColumnName.GetHashCode();

            return result;
        }
        #endregion
    }
    #endregion

    ///////////////////////////////////////////////////////////////////////////

    private static void GetStatementColumnParents(
        SQLiteBase sql,
        SQLiteStatement stmt,
        int fieldCount,
        ref Dictionary<ColumnParent, List<int>> parentToColumns,
        ref Dictionary<int, ColumnParent> columnToParent
................................................................................
            _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false
          }
        }

        // Ahh, we found a row-returning resultset eligible to be returned!
        _activeStatement = stmt;
        _fieldCount = fieldCount;
        _fieldIndexes = null;
        _fieldTypeArray = null;

        if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
          LoadKeyInfo();

        return true;
      }

Changes to Tests/basic.eagle.

1924
1925
1926
1927
1928
1929
1930




































1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
  cleanupDb $fileName

  unset -nocomplain output result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^0 12341234 1 \{System\.Data\.SQLite\.SQLiteException\
\(0x80004005\): SQL logic error or missing database.*?\} 0 1234123412341234$}}





































###############################################################################

unset -nocomplain systemDataSQLiteDllFile systemDataSQLiteLinqDllFile \
    testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

###############################################################################

runSQLiteTestEpilogue
runTestEpilogue







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>










1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
  cleanupDb $fileName

  unset -nocomplain output result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^0 12341234 1 \{System\.Data\.SQLite\.SQLiteException\
\(0x80004005\): SQL logic error or missing database.*?\} 0 1234123412341234$}}

###############################################################################

runTest {test data-1.39 {column name and index lookup} -setup {
  setupDb [set fileName data-1.39.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(x, y, z);
    INSERT INTO t1 (x, y, z) VALUES(1, 'foo', 1234);
  }

  set dataReader [sql execute -execute reader -format datareader \
      -alias $db "SELECT x, y, z FROM t1;"]

  set result [list]

  while {[$dataReader Read]} {
    lappend result \
        [list [$dataReader GetName 0] [$dataReader GetOrdinal x] \
            [$dataReader Item x]] \
        [list [$dataReader GetName 1] [$dataReader GetOrdinal y] \
            [$dataReader Item y]] \
        [list [$dataReader GetName 2] [$dataReader GetOrdinal z] \
            [$dataReader Item z]]
  }

  set result
} -cleanup {
  unset -nocomplain dataReader

  cleanupDb $fileName

  unset -nocomplain result db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \
{{x 0 1} {y 1 foo} {z 2 1234}}}

###############################################################################

unset -nocomplain systemDataSQLiteDllFile systemDataSQLiteLinqDllFile \
    testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

###############################################################################

runSQLiteTestEpilogue
runTestEpilogue