lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]


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>

--