System.Data.SQLite

anonymous added on 2015-08-04 17:50:47:

Hi,

I have the problem, that I cannot use GUID PKs in EF6, because EF6 creates SQL statements like:

INSERT INTO [TESTTABLE] ([ITEM]) VALUES ('test2');
SELECT [TESTTABLEID] FROM [TESTTABLE] WHERE last_rows_affected() > 0 AND [TESTTABLEID] = last_insert_rowid();

The table is defined as:

CREATE TABLE [TESTTABLE] (
    [TESTTABLEID] BLOB(16) PRIMARY KEY DEFAULT (GEN_UUID()),
    [ITEM] TEXT NOT NULL
);

The GEN_UUID function is a user defined scalar function.

I also tried the development version as of dc0e4e31d4fe0575

.

This is the complete source code:

    class Program
    {
        static void Main(string[] args)
        {
            Environment.SetEnvironmentVariable("AppendManifestToken_SQLiteProviderManifest", ";BinaryGUID=True;");
            TestGenUuid();
        }

        private static void TestGenUuid()
        {
            using (var conn = new SQLiteConnection("Data Source=:memory:;BinaryGUID=True"))
            {
                conn.Open();
                conn.BindFunction(new SQLiteFunctionAttribute("GEN_UUID", 0, FunctionType.Scalar), new GenUuidFunction());
                ExecuteScript(conn, @"
CREATE TABLE [TESTTABLE] (
    [TESTTABLEID] BLOB(16) PRIMARY KEY DEFAULT (GEN_UUID()),
    [ITEM] TEXT NOT NULL
);
INSERT INTO [TESTTABLE] ([ITEM]) VALUES ('test');
");
                var temp = ExecuteScalar(conn, "SELECT [TESTTABLEID] FROM [TESTTABLE] WHERE [ITEM]='test'");
                Debug.Assert(temp != null);
                var result = new Guid((byte[])temp);
                Debug.Assert(result != Guid.Empty);
                temp = ExecuteScalar(conn, @"
INSERT INTO [TESTTABLE] ([ITEM]) VALUES ('test2');
SELECT [TESTTABLEID] FROM [TESTTABLE] WHERE last_rows_affected() > 0 AND [TESTTABLEID] = last_insert_rowid();");
                Debug.Assert(temp != null);
                result = new Guid((byte[])temp);
                Debug.Assert(result != Guid.Empty);
            }
        }

        private static object ExecuteScalar(SQLiteConnection conn, string script)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = script;
                cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                return cmd.ExecuteScalar();
            }
        }

        private static void ExecuteScript(SQLiteConnection conn, string script)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = script;
                cmd.ExecuteNonQuery();
            }
        }

        public class GenUuidFunction : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                return Guid.NewGuid().ToByteArray();
            }
        }
    }

mistachkin added on 2015-08-04 22:56:17:

To clarify: Did this used to work in a previous version of System.Data.SQLite?

mistachkin added on 2015-08-18 18:45:01:

This query is incorrect:

SELECT [TESTTABLEID]
FROM [TESTTABLE]
WHERE last_rows_affected() > 0
AND [TESTTABLEID] = last_insert_rowid();

The type of TESTTABLEID is BLOB (i.e. GUID) and the type of last_insert_rowid()
is *always* INT64.  This query can never produce a valid result.

mistachkin added on 2015-08-18 18:47:14:

Try this instead:

SELECT [TESTTABLEID] FROM [TESTTABLE] WHERE rowid = last_insert_rowid();

mistachkin added on 2015-08-18 18:54:02:

Since this query is actually generated by the LINQ assembly, this is a problem;
however, this is not a new issue.

mistachkin added on 2015-08-18 19:03:29:

The solution to this problem is to modify the
DmlSqlGenerator.GenerateReturningSql method to
detect whether or not a table has a simple int64
primary key.  If it does, the last_insert_rowid()
function can be used; otherwise, all the key columns
for the primary key (composite or otherwise) must be
used to return the appropriate single row.

mistachkin added on 2015-08-19 04:24:19:

Fixed via branch check-in [8ea5fcd6b9d89425].  This fix will be included in the
1.0.98.0 release.

mistachkin added on 2015-08-19 04:32:06:

Now fixed on "preRelease" branch via check-in [c1baff799b6affc3].