GNATCOLL SQLite rollback vs cursor

[Stephen Leake <stephen_leake@stephe-leake.org>]

I've come across a strange problem using GNATCOLL with SQLite, and I'm
hoping someone can shed some light on it.

Here's code illustrating the problem:

--  Fixes bug
with Ada.Directories;
with Ada.Text_IO; use Ada.Text_IO;
with Ada.Unchecked_Deallocation;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Sqlite;
procedure Rollback_Bug_3
is
   Db_Filename : constant String := "books.db";

   Connection : Database_Connection;

   type Cursor_Access_Type is access all Forward_Cursor;
   Cursor : Cursor_Access_Type;

   procedure Free is new Ada.Unchecked_Deallocation (Forward_Cursor, Cursor_Access_Type);

   procedure Insert
     (First : in String;
      Last  : in String)
   is
   begin
      Put_Line ("Insert " & First & " " & Last);
      Execute
        (Connection,
         "INSERT INTO Author (First, Last) VALUES (""" & First & """, """ & Last & """)");

      if not Connection.Success then
         --  GNATCOLL SQLite has obscure behavior with respect to
         --  cursors and rollback; if a cursor is active, it prevents
         --  Rollback from working (SQLite reports "database locked").
         --  It doesn't prevent a successful INSERT. So we have to
         --  Finalize any cursor before calling Rollback. Another
         --  GNATCOLL quirk makes Finalize (Cursor) not visible, so we
         --  use allocations. Sigh.

         Free (Cursor); --  delete this to see the problem
         Rollback (Connection);
      else
         Commit (Connection);
      end if;

      --  Find the just inserted ID for mapping (not using
      --  Exec.Last_ID becuase I didn't know about it :)

      if Cursor = null then
         Cursor := new Forward_Cursor;
      end if;

      Cursor.Fetch
        (Connection,
         "SELECT ID, First, Last FROM Author WHERE First = """ & First & """ and Last = """ & Last & """");

      if Cursor.Has_Row then
         Put_Line ("ID => " & Cursor.Value (0));
      end if;
   end Insert;


begin
   --  Create db from scratch
   if Ada.Directories.Exists (Db_Filename) then
      Ada.Directories.Delete_File (Db_Filename);
   end if;

   Connection := Build_Connection (GNATCOLL.SQL.Sqlite.Setup (Db_Filename));

   --  Create Author table
   Execute
     (Connection,
      "CREATE TABLE Author (ID INTEGER PRIMARY KEY, First TEXT, Last TEXT)");

   Execute
     (Connection,
      "CREATE UNIQUE INDEX Author_Last on Author (Last, First)");

   Execute
     (Connection,
      "CREATE UNIQUE INDEX Author_First on Author (First, Last)");

   --  insert a couple of items, checking for success
   Insert ("Ada", "Lovelace");

   Insert ("Charles", "Babbage");

   --  So far so good. Now try to insert Ada Lovelace again; fails
   --  because of 'unique' constraint on Author_Name index

   Insert ("Ada", "Lovelace");

   --  Database be ok; try to add another name

   Insert ("Grace", "Hopper");

end Rollback_Bug_3;

This code works properly, giving a reasonable error message for the
attempt to insert a duplicate message:

./rollback_bug_3.exe 
Insert Ada Lovelace
ID => 1
Insert Charles Babbage
ID => 2
Insert Ada Lovelace
[SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES ("Ada", "Lovelace")  error=constraint failed
ID => 1
Insert Grace Hopper
ID => 3

However, my first version of this did not free the cursor; commenting
that out gives:

./rollback_bug_3.exe 
Insert Ada Lovelace
ID => 1
Insert Charles Babbage
ID => 2
Insert Ada Lovelace
[SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES ("Ada", "Lovelace")  error=constraint failed
[SQL.ERROR] Failed to execute ROLLBACK  error=database is locked
ID => 1
Insert Grace Hopper
[SQL.ERROR] Failed to execute BEGIN  error=SQL logic error or missing database
[SQL.ERROR] Failed to execute ROLLBACK  error=database is locked


Apparently the cursor holds some sort of lock on the database. But I can
insert new records while the cursor is active; the only thing I can't do
is rollback.

This seems to be an SQLite issue, not a GNATCOLL issue, so perhaps I
should take this to an SQLite mailing list.
-- 
-- Stephe