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

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

Overview
Comment:When generating the schema for the contents of a data reader, skip flagging columns as 'unique' if the data reader is holding the result of some kind of multi-table construct (i.e. a join) because we must allow duplicate values in that case. Fix for ticket [7e3fa93744]. Also, enhance vendor-specific initialization file for Eagle to report any test setting overrides being used.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-7e3fa93744
Files: files | file ages | folders
SHA1: da3e0bfb533f606ac9f06113d6dbfb912098ad71
User & Date: mistachkin 2011-11-13 05:55:32
References
2011-11-13
06:00 Ticket [7e3fa93744] Problem with 3 table query! status still Closed with 2 other changes artifact: 775bbfaaf6 user: mistachkin
06:00 Closed ticket [7e3fa93744]. artifact: 1f6c8a0538 user: mistachkin
Context
2011-11-13
06:24
Merge fix for ticket [7e3fa93744] and several unit test infrastructure enhancements to trunk. check-in: 8fe2a401d6 user: mistachkin tags: trunk
05:55
When generating the schema for the contents of a data reader, skip flagging columns as 'unique' if the data reader is holding the result of some kind of multi-table construct (i.e. a join) because we must allow duplicate values in that case. Fix for ticket [7e3fa93744]. Also, enhance vendor-specific initialization file for Eagle to report any test setting overrides being used. Closed-Leaf check-in: da3e0bfb53 user: mistachkin tags: tkt-7e3fa93744
01:06
Improve comments in the vendor specific interpreter initialization file. check-in: eb89cff67e user: mistachkin tags: tkt-7e3fa93744
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Externals/Eagle/lib/Eagle1.0/vendor.eagle.

116
117
118
119
120
121
122






















123
124
125
126
127
128
129
130
      }

      #
      # NOTE: Directory not found, return failure.
      #
      return false
    }























    addTestSuiteToAutoPath stdout false
  }
}
 
###############################################################################
############################### END VENDOR CODE ###############################
###############################################################################







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








116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
      }

      #
      # NOTE: Directory not found, return failure.
      #
      return false
    }
 
    proc checkForTestOverrides { channel varNames quiet } {
      set result 0

      foreach varName $varNames {
        if {[uplevel 1 [list info exists $varName]]} then {
          incr result

          if {!$quiet} then {
            puts -nonewline $channel [appendArgs \
                "Found vendor-specific test override \"" $varName "\".\n"]
          }
        }
      }

      return $result
    }
 
    checkForTestOverrides stdout \
        [list binary_directory build_base_directory build_directory \
              common_directory datetime_format test_configuration \
              test_year] false

    addTestSuiteToAutoPath stdout false
  }
}
 
###############################################################################
############################### END VENDOR CODE ###############################
###############################################################################

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

4
5
6
7
8
9
10

11
12
13
14
15
16
17
...
582
583
584
585
586
587
588
589













































































































590
591
592
593



















594
595
596
597
598
599
600
...
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
...
758
759
760
761
762
763
764






765
766
767
768
769
770
771
772
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
  using System;

  using System.Data;
  using System.Data.Common;
  using System.Globalization;

  /// <summary>
  /// SQLite implementation of DbDataReader.
  /// </summary>
................................................................................
    /// to gather the necessary information so it can be represented in an ADO.NET manner.
    /// </summary>
    /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
    public override DataTable GetSchemaTable()
    {
      return GetSchemaTable(true, false);
    }














































































































    internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue)
    {
      CheckClosed();




















      DataTable tbl = new DataTable("SchemaTable");
      DataTable tblIndexes = null;
      DataTable tblIndexColumns;
      DataRow row;
      string temp;
      string strCatalog = "";
      string strTable = "";
................................................................................
        row[SchemaTableColumn.IsUnique] = false;
        row[SchemaTableColumn.IsKey] = false;
        row[SchemaTableOptionalColumn.IsAutoIncrement] = false;
        row[SchemaTableColumn.DataType] = GetFieldType(n);
        row[SchemaTableOptionalColumn.IsHidden] = false;
        row[SchemaTableColumn.BaseSchemaName] = _baseSchemaName;

        strColumn = _command.Connection._sql.ColumnOriginalName(_activeStatement, n);
        if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn;

        row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn);
        row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, StringComparison.OrdinalIgnoreCase) != 0);

        temp = _command.Connection._sql.ColumnTableName(_activeStatement, n);
        if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp;

        temp = _command.Connection._sql.ColumnDatabaseName(_activeStatement, n);
        if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp;

        string dataType = null;
        // If we have a table-bound column, extract the extra information from it
        if (String.IsNullOrEmpty(strColumn) == false)
        {
          string collSeq;
................................................................................
                (string)rowIndexes["INDEX_NAME"],
                null
                });
              foreach (DataRow rowColumnIndex in tblIndexColumns.Rows)
              {
                if (String.Compare((string)rowColumnIndex["COLUMN_NAME"], strColumn, StringComparison.OrdinalIgnoreCase) == 0)
                {






                  if (tblIndexColumns.Rows.Count == 1 && (bool)row[SchemaTableColumn.AllowDBNull] == false)
                    row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"];

                  // If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement
                  // NOTE:  Currently commented out because this is not always the desired behavior.  For example, a 1:1 relationship with
                  //        another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other.
                  //        It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column.








>







 







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


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







 







|





|
|
|
|







 







>
>
>
>
>
>
|







4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
...
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
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
684
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
...
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
...
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace System.Data.SQLite
{
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Common;
  using System.Globalization;

  /// <summary>
  /// SQLite implementation of DbDataReader.
  /// </summary>
................................................................................
    /// to gather the necessary information so it can be represented in an ADO.NET manner.
    /// </summary>
    /// <returns>Returns a DataTable containing the schema information for the active SELECT statement being processed.</returns>
    public override DataTable GetSchemaTable()
    {
      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;
            }
            else if ((x == null) || (y == null))
            {
                return false;
            }
            else
            {
                if (!String.Equals(x.DatabaseName, y.DatabaseName,
                        StringComparison.OrdinalIgnoreCase))
                {
                    return false;
                }

                if (!String.Equals(x.TableName, y.TableName,
                        StringComparison.OrdinalIgnoreCase))
                {
                    return false;
                }

                if (!String.Equals(x.ColumnName, y.ColumnName,
                        StringComparison.OrdinalIgnoreCase))
                {
                    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.TableName != null))
                result ^= obj.TableName.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
        )
    {
        if (parentToColumns == null)
            parentToColumns = new Dictionary<ColumnParent, List<int>>(
                new ColumnParent());

        if (columnToParent == null)
            columnToParent = new Dictionary<int, ColumnParent>();

        for (int n = 0; n < fieldCount; n++)
        {
            string databaseName = sql.ColumnDatabaseName(stmt, n);
            string tableName = sql.ColumnTableName(stmt, n);
            string columnName = sql.ColumnOriginalName(stmt, n);

            ColumnParent key = new ColumnParent(databaseName, tableName, null);
            ColumnParent value = new ColumnParent(databaseName, tableName, columnName);

            if (!parentToColumns.ContainsKey(key))
                parentToColumns.Add(key, new List<int>(new int[] { n }));
            else
                parentToColumns[key].Add(n);

            columnToParent.Add(n, value);
        }
    }

    internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue)
    {
      CheckClosed();

     //
     // BUGFIX: We need to quickly scan all the fields in the current
     //         "result set" to see how many distinct tables are actually
     //         involved.  This information is necessary so that some
     //         intelligent decisions can be made when constructing the
     //         metadata below.  For example, we need to be very careful
     //         about flagging a particular column as "unique" just
     //         because it was in its original underlying database table
     //         if there are now multiple tables involved in the
     //         "result set".  See ticket [7e3fa93744] for more detailed
     //         information.
     //
      Dictionary<ColumnParent, List<int>> parentToColumns = null;
      Dictionary<int, ColumnParent> columnToParent = null;

      GetStatementColumnParents(
          _command.Connection._sql, _activeStatement, _fieldCount,
          ref parentToColumns, ref columnToParent);

      DataTable tbl = new DataTable("SchemaTable");
      DataTable tblIndexes = null;
      DataTable tblIndexColumns;
      DataRow row;
      string temp;
      string strCatalog = "";
      string strTable = "";
................................................................................
        row[SchemaTableColumn.IsUnique] = false;
        row[SchemaTableColumn.IsKey] = false;
        row[SchemaTableOptionalColumn.IsAutoIncrement] = false;
        row[SchemaTableColumn.DataType] = GetFieldType(n);
        row[SchemaTableOptionalColumn.IsHidden] = false;
        row[SchemaTableColumn.BaseSchemaName] = _baseSchemaName;

        strColumn = columnToParent[n].ColumnName;
        if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn;

        row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn);
        row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, StringComparison.OrdinalIgnoreCase) != 0);

        temp = columnToParent[n].TableName;
        if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp;

        temp = columnToParent[n].DatabaseName;
        if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp;

        string dataType = null;
        // If we have a table-bound column, extract the extra information from it
        if (String.IsNullOrEmpty(strColumn) == false)
        {
          string collSeq;
................................................................................
                (string)rowIndexes["INDEX_NAME"],
                null
                });
              foreach (DataRow rowColumnIndex in tblIndexColumns.Rows)
              {
                if (String.Compare((string)rowColumnIndex["COLUMN_NAME"], strColumn, StringComparison.OrdinalIgnoreCase) == 0)
                {
                  //
                  // BUGFIX: Make sure that we only flag this column as "unique"
                  //         if we are not processing of some kind of multi-table
                  //         construct (i.e. a join) because in that case we must
                  //         allow duplicate values (refer to ticket [7e3fa93744]).
                  //
                  if (parentToColumns.Count == 1 && tblIndexColumns.Rows.Count == 1 && (bool)row[SchemaTableColumn.AllowDBNull] == false)
                    row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"];

                  // If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement
                  // NOTE:  Currently commented out because this is not always the desired behavior.  For example, a 1:1 relationship with
                  //        another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other.
                  //        It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column.

Changes to Tests/tkt-7e3fa93744.eagle.

16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
..
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue

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

runTest {test tkt-7e3fa93744-1.1 {composite primary key} -setup {
  setupDb [set fileName tkt-7e3fa93744-1.1.db]
} -body {
  set sql {
    CREATE TABLE t1 (
      id1 INTEGER PRIMARY KEY
    );

................................................................................

runTest {test tkt-7e3fa93744-1.2 {composite primary key, DataTable} -setup {
  setupDb [set fileName tkt-7e3fa93744-1.2.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getTemporaryPath] $fileName]

  set sql(1) {
    CREATE TABLE t1 (
      id1 INTEGER PRIMARY KEY NOT NULL
    );

    CREATE TABLE t2 (
      id1 INTEGER NOT NULL,
      id2 INTEGER NOT NULL,
      PRIMARY KEY (id1, id2)
    );

    INSERT INTO t1 (id1) VALUES (1);
    INSERT INTO t1 (id1) VALUES (2);

    INSERT INTO t2 (id1, id2) VALUES (1, 1);
    INSERT INTO t2 (id1, id2) VALUES (1, 2);
    INSERT INTO t2 (id1, id2) VALUES (2, 1);
    INSERT INTO t2 (id1, id2) VALUES (2, 2);
  }

  set sql(2) { \
    SELECT t1.id1, t2.id1, t2.id2 \
    FROM t1, t2 \
    ORDER BY t1.id1, t2.id1, t2.id2; \
  }

  sql execute $db $sql(1)

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System;
    using System.Data;
    using System.Data.SQLite;

................................................................................
          using (SQLiteConnection connection = new SQLiteConnection(
              "Data Source=${dataSource};"))
          {
            connection.Open();

            using (SQLiteCommand command = connection.CreateCommand())
            {
              command.CommandText = "${sql(2)}";

              using (SQLiteDataReader dataReader = command.ExecuteReader())
              {
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);

                return dataTable.Rows.Count;







|







 







|
|
|
|
<
|
|
|
|
|
<
|
|
<
|
|
|
|
<
<
<





<
<







 







|







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
..
60
61
62
63
64
65
66
67
68
69
70

71
72
73
74
75

76
77

78
79
80
81



82
83
84
85
86


87
88
89
90
91
92
93
...
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue

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

runTest {test tkt-7e3fa93744-1.1 {composite primary key, baseline} -setup {
  setupDb [set fileName tkt-7e3fa93744-1.1.db]
} -body {
  set sql {
    CREATE TABLE t1 (
      id1 INTEGER PRIMARY KEY
    );

................................................................................

runTest {test tkt-7e3fa93744-1.2 {composite primary key, DataTable} -setup {
  setupDb [set fileName tkt-7e3fa93744-1.2.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getTemporaryPath] $fileName]

  set sql { \
    CREATE TABLE t1 ( \
      id1 INTEGER PRIMARY KEY NOT NULL \
    ); \

    CREATE TABLE t2 ( \
      id1 INTEGER NOT NULL, \
      id2 INTEGER NOT NULL, \
      PRIMARY KEY (id1, id2) \
    ); \

    INSERT INTO t1 (id1) VALUES (1); \
    INSERT INTO t1 (id1) VALUES (2); \

    INSERT INTO t2 (id1, id2) VALUES (1, 1); \
    INSERT INTO t2 (id1, id2) VALUES (1, 2); \
    INSERT INTO t2 (id1, id2) VALUES (2, 1); \
    INSERT INTO t2 (id1, id2) VALUES (2, 2); \



    SELECT t1.id1, t2.id1, t2.id2 \
    FROM t1, t2 \
    ORDER BY t1.id1, t2.id1, t2.id2; \
  }



  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System;
    using System.Data;
    using System.Data.SQLite;

................................................................................
          using (SQLiteConnection connection = new SQLiteConnection(
              "Data Source=${dataSource};"))
          {
            connection.Open();

            using (SQLiteCommand command = connection.CreateCommand())
            {
              command.CommandText = "${sql}";

              using (SQLiteDataReader dataReader = command.ExecuteReader())
              {
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);

                return dataTable.Rows.Count;