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

[briot.e...@gmail.com]

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

[Stephen Leake]

Excellent.

--
-- Stephe