[Date Prev][Date Next][Thread Prev][Thread Next]
- Subject: Re: Enabling the foreign key support in Sliqte3
- From: Luciano de Souza <luchyanus@...>
- Date: Sun, 13 Mar 2011 15:24:03 -0300
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
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')
the answer is 1, indicating the command was processed and the support
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
"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
On Sun, Mar 13, 2011 at 1:23 AM, Luciano de Souza<email@example.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
env = luasql.sqlite3()
con = env:connect("")
cur = con:execute("select sqlite_version()")
cur = con:execute("pragma foreign_keys")
SQLite3 cursor (0037C118)
nil LuaSQL: out of memory
cur = con:execute("pragma foreign_keys=1")
cur = con:execute("pragma foreign_keys=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.