GNATCOLL SQLite rollback vs cursor

GNATCOLL SQLite rollback vs cursor

[Stephen Leake]

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

Re: GNATCOLL SQLite rollback vs cursor

[briot.emmanuel]

It is quite possible that this issue has in fact already been fixed in the development version of GNATCOLL, because now with or without the line that frees the cursor, the result is the same:

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=columns First, Last are not unique
ID => 1
Insert Grace Hopper
ID => 3


Note also that we have improved the way we retrieve the error messages from sqlite, since the current one is clearer than just the "constraint error" you are getting.

Emmanuel

Re: GNATCOLL SQLite rollback vs cursor

[Stephen Leake]

briot.emmanuel@gmail.com writes:

> It is quite possible that this issue has in fact already been fixed in
> the development version of GNATCOLL, because now with or without the
> line that frees the cursor, the result is the same:
>
> 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=columns First, Last are not unique
> ID => 1
> Insert Grace Hopper
> ID => 3
>
>
> Note also that we have improved the way we retrieve the error messages
> from sqlite, since the current one is clearer than just the
> "constraint error" you are getting.

Excellent. 

-- 
-- Stephe