Re: Enabling the foreign key support in Sliqte3

[Luciano de Souza <luchyanus@...>]

With Luarocks, I compiled the version 2.2.0-1 of Luasql.sqlite3. The 
version of Sqlite 3.3.17 was replaced by 3.6.22.

According Sqlite homepage, the foreign key support is added in 3.6.19. 
Therefore, I could use it.

I have read the section of the Sqlite manual refering to foreign key 
support:
http://www.sqlite.org/draft/foreignkeys.html

If I understand, to use this feature is necesary:

1. to use a version 3.6.19 or newer. With the new version of 
Luasql-sqlite3, I have the 3.6.22 version.

2. To enable the foreign key support with: "pragma foreign_keys = on". 
If this command doesn't return 0 or 1, the library was compiled without 
this support. I have 0 or 1 as a return, so this is not the problem.

In the commandline interface of Sqlite, I did:

sqlite> pragma foreign_keys = on;
insert into annotation_master(entry, cathegory, subject, description) 
values(datetime('now'), 4, 'Subject 1', 'Description 1');

What is the result? A message informing the insertion can't be done 
because of a foreign key constraint. Yes, with Sqlite, everything seems 
to be OK.
However, running my Lua script, an error comes up.

I tried to add:

self.cur = self.con:execute('pragma foreign_keys = on')

If this definition is not save in the database and should be retrieved 
each time the database is opened, certainly it would be necessary.

Verifying if the support was enabled:

self.cur = self.con:execute('pragma foreign_keys')
print(self.cur:fetch())

the answer is 1, indicating the command was processed and the support 
was enabled.

Everything seems to be OK. It works in the commandline interface and 
doesn't work with my Lua script.

I tried to implement foreign keys with triggers, but the error is always 
the same:

"SQL logical error or missing database"

For these reasons, I also believe the problem is in Luasql-sqlite3 and 
not in Sqlite3.

Without foreign keys, Sqlite is still a good database for simple needs. 
However, with this support and regarding it doesn't need to be 
installed, it would be much more intersting.

Em 13/03/2011 14:14, Bob Chapman escreveu:
> On Sun, Mar 13, 2011 at 9:42 AM, Javier Guerra Giraldez
> <javier@guerrag.com>  wrote:
> > On Sun, Mar 13, 2011 at 1:23 AM, Luciano de Souza<luchyanus@gmail.com>  wrote:
> > > Is the conclusion that I can't use foreign keys with Luasqlite.sqlite3()?
> > you could recompile LuaSQLite with a newer SQLite library.
> I have recompiled LuaSQ 2.1.1 (as downloaded from
> <http://www.keplerproject.org>) using MingW and Sqlite 3.7.5. The
> resulting dll seems to work fine for the limited number of SQL
> statements that I have tried but NOT so hot with PRAGMAs. ;(
>
> Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio
> > require "luasql.sqlite3"
> > env = luasql.sqlite3()
> > con = env:connect("")
> > cur = con:execute("select sqlite_version()")
> > print(cur:fetch())
> 3.7.5
> > cur = con:execute("pragma foreign_keys")
> > print(cur)
> SQLite3 cursor (0037C118)
> > print(cur:fetch())
> nil     LuaSQL: out of memory
> > cur = con:execute("pragma foreign_keys=1")
> > print(cur)
> 0
> > cur = con:execute("pragma foreign_keys=0")
> > print(cur)
> 0
>
> This MAY be an artifact of my ignorance in building LuaSQL ;) but I am
> inclined to believe that it is a limitation of LuaSQL Version 2.1.1.
> See:
>
> <http://www.mail-archive.com/kepler-project@lists.luaforge.net/msg00752.html>
>
> --