From mboxrd@z Thu Jan  1 00:00:00 1970
X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me
X-Spam-Level: 
X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,WEIRD_QUOTING
	autolearn=ham autolearn_force=no version=3.4.4
X-Google-Thread: a07f3367d7,d654f990612c5da5,start
X-Google-Attributes: gida07f3367d7,public,usenet
X-Google-NewGroupId: yes
X-Google-Language: ENGLISH,ASCII-7-bit
Received: by 10.180.88.195 with SMTP id bi3mr1059254wib.3.1346980966657;
        Thu, 06 Sep 2012 18:22:46 -0700 (PDT)
Path: 
 q11ni5513255wiw.1!nntp.google.com!feeder3.cambriumusenet.nl!194.109.133.85.MISMATCH!newsfeed.xs4all.nl!newsfeed6.news.xs4all.nl!xs4all!border4.nntp.ams.giganews.com!border2.nntp.ams.giganews.com!border3.nntp.ams.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!newsreader4.netcologne.de!news.netcologne.de!xlned.com!feeder3.xlned.com!feeder1.cambriumusenet.nl!feed.tweaknews.nl!209.197.12.246.MISMATCH!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!post02.iad.highwinds-media.com!news.flashnewsgroups.com-b7.4zTQh5tI3A!not-for-mail
From: Stephen Leake <stephen_leake@stephe-leake.org>
Newsgroups: comp.lang.ada
Subject: GNATCOLL SQLite rollback vs cursor
Date: Fri, 31 Aug 2012 06:34:27 -0400
Message-ID: <85ipbzwdsc.fsf@stephe-leake.org>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.1 (windows-nt)
Cancel-Lock: sha1:Nkyum7SDUYPn5dDiZZvguegZ4gg=
MIME-Version: 1.0
X-Complaints-To: abuse@flashnewsgroups.com
Organization: FlashNewsgroups.com
X-Trace: 8448050409336e029e66123262
X-Received-Bytes: 4857
Content-Type: text/plain
Date: 2012-08-31T06:34:27-04:00
List-Id: <comp.lang.ada>

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