Hi,
The implementation of GetSchemaTable() in SQLiteDataReader seems to be based on
the properties of the columns used in the select. Obviously uniqueness,
nullness and primary key properties do not transfer when only a subset of the
columns is selected or combined with data from other tables. Unfortunately the
.Net Datatable::Load() command uses the information returned by
SQLiteDataReader::GetSchemaTable(). This means that the result of many queries
cannot be loaded into a Datatable using the Load() command because of
non-nullable columns being null (due to a left join) or even wrong (the
Datatable just drops "duplicate" rows when loading data). See the code below
that illustrates the problem.
I'm not sure how other DB drivers solve this problem, but it seems to me the
SQLite driver should stop trying to be clever and just always set the IsUnique
and IsKey columns to false and also always allow null values.
Jann
using System.Data;
using System.Data.SQLite;
using NUnit.Framework;
namespace SQLiteDemo
{
[TestFixture]
public class Demo
{
private SQLiteConnection m_connection;
[SetUp]
public void CreateDatabase()
{
m_connection = new SQLiteConnection("Data Source=:memory:");
m_connection.Open();
using (var command = new SQLiteCommand())
{
command.CommandText =
@"CREATE TABLE P (
Pid1 INT NOT NULL,
Pid2 INT NOT NULL,
Value TEXT NOT NULL,
CONSTRAINT PK_P PRIMARY KEY (Pid1, Pid2)
);
CREATE TABLE PM (
Pid1 INT NOT NULL,
Pid2 INT NOT NULL,
Value TEXT NOT NULL,
CONSTRAINT PK_PM PRIMARY KEY (Pid1, Pid2, Value)
);";
command.Connection = m_connection;
command.ExecuteNonQuery();
}
using (var command = new SQLiteCommand())
{
command.CommandText =
@"INSERT INTO P VALUES(1, 1, 'A');
INSERT INTO P VALUES(2, 1, 'A');
INSERT INTO P VALUES(3, 1, 'B');
INSERT INTO PM VALUES(1, 10, 'A');
INSERT INTO PM VALUES(3, 1, 'A');";
command.Connection = m_connection;
command.ExecuteNonQuery();
}
}
[TearDown]
public void CleanDatabase()
{
m_connection.Close();
m_connection = null;
}
[Test]
public void Case1()
{
var dt = new DataTable();
using (var command = new SQLiteCommand(@"SELECT P.Pid1, PM.Pid2
FROM P
LEFT JOIN PM ON P.Value =
PM.Value", m_connection))
{
using (var reader = command.ExecuteReader())
{
var schema = reader.GetSchemaTable();
dt.Load(reader);
}
}
Assert.That(dt.Rows.Count, Is.EqualTo(5));
}
[Test]
public void Case2()
{
var dt = new DataTable();
using (var command = new SQLiteCommand(@"SELECT P.Pid1,
CASE WHEN PM.Pid2 IS NULL
THEN P.Pid2 ELSE PM.Pid2 END
FROM P
LEFT JOIN PM ON P.Value =
PM.Value", m_connection))
{
using (var reader = command.ExecuteReader())
{
var schema = reader.GetSchemaTable();
dt.Load(reader);
}
}
Assert.That(dt.Rows.Count, Is.EqualTo(5));
}
}
}
Winton Capital Management Limited (?Winton?) is a limited company registered in
England and Wales with its registered offices at 16 Old Bailey, London, EC4M
7EG (Registered Company No. 3311531). Winton is authorised and regulated by the
Financial Conduct Authority in the United Kingdom, registered as an investment
adviser with the US Securities and Exchange Commission, registered with the US
Commodity Futures Trading Commission and a member of the National Futures
Association in the United States.
This communication, including any attachments, is confidential and may be
privileged. This email is for use by the intended recipient only. If you
receive it in error, please notify the sender and delete it. You should not
copy or disclose all or any part of this email.
This email does not constitute an offer or solicitation and nothing contained
in this email constitutes, and should not be construed as, investment advice.
Prospective investors should request offering materials and consult their own
advisers with respect to investment decisions and inform themselves as to
applicable legal requirements, exchange control regulations and taxes in the
countries of their citizenship, residence or domicile. Past performance is not
indicative of future results.
Winton takes reasonable steps to ensure the accuracy and integrity of its
communications, including emails. However Winton accepts no liability for any
materials transmitted. Emails are not secure and cannot be guaranteed to be
error free.
