lua-users home
lua-l archive

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


Reading a little bit more, I knew triggers can be used to implement foreign keys when Sqlite version does not allow this feature. This is my attempt:

begin transaction;

create table annotation_master
(
id integer primary key not null,
entry datetime not null,
cathegory integer not null,
subject varchar(40) not null,
description text not null
);

create table annotation_cathegory
(
id integer primary key not null,
title varchar(30) not null
);

create trigger fkcathegory_id
before insert on annotation_master
for each row
begin
select case
when((select id from annotation_cathegory where id = new.cathegory) is null)
then
raise(ABORT, 'This cathegory does not exist')
end;
end;

commit;

And the Lua script:

require('luasql.sqlite3')
require('classlib')
require('date')

module('dbr', package.seeall)

filename = 'calau.dbl'

class.annotation()

function annotation:__init()
obj = self
self.env = luasql.sqlite3()
self.con = self.env:connect(filename)
self.cur = self.con:execute('select sqlite_version()')
end

function annotation:quit()
obj.cur:close()
obj.con:close()
obj.env:close()
end

function annotation:append(cathegory, subject, description)
local err, msg = obj.con:execute(string.format("insert into annotation_master(entry, cathegory, subject, description) values(datetime('now'), %d, '%s', '%s')", cathegory, subject, description))
return err, msg
end

reg = annotation()
err, msg = reg:append(3, 'Assunto 1', 'Descrição 1')
print(msg)
reg:quit()

When I print msg, Lua says that a Sqlite error occurs or because a database is missing or because of a logical Sqlite error. The database is correctly referenced and printing the schema in database, I can see the trigger and all the structure shown before was created.

As a logical error is a general message, I could not identify my error. This is the first time I use triggers, so it is not properly surprising if I understand something completely wrong.

In Sqlite website, I knew trigger support was added in 2.5 version and regarding Luasql works with 3.3.17, I presume this strategy can be used.


-------- Mensagem original --------
Return-Path: <luchyanus@gmail.com>
Received: from [127.0.0.1] (189001136238.usr.predialnet.com.br [189.1.136.238]) by mx.google.com with ESMTPS id i10sm8008562anh.12.2011.03.12.22.23.40 (version=SSLv3 cipher=OTHER); Sat, 12 Mar 2011 22:23:41 -0800 (PST)
Message-ID: 4D7C62C9.2000905@gmail.com"><4D7C62C9.2000905@gmail.com>
Date: Sun, 13 Mar 2011 03:23:05 -0300
From: Luciano de Souza <luchyanus@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; pt-BR; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: Lua mailing list <lua-l@lists.lua.org>
Subject: Re: Enabling the foreign key support in Sliqte3
References: 4D7C0DDF.5030207@gmail.com"><4D7C0DDF.5030207@gmail.com> AANLkTin9-RaZSMB9qfSgy3rjk3JkDas2F6BNaV5sZHVM@mail.gmail.com"><AANLkTin9-RaZSMB9qfSgy3rjk3JkDas2F6BNaV5sZHVM@mail.gmail.com>
In-Reply-To: AANLkTin9-RaZSMB9qfSgy3rjk3JkDas2F6BNaV5sZHVM@mail.gmail.com"><AANLkTin9-RaZSMB9qfSgy3rjk3JkDas2F6BNaV5sZHVM@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Yes, you are right. Using sqlite3.exe, I had created the database with 
these commands:

sqlite3 test.dbl
sqlite> pragma foreign_keys = on;
sqlite> .read test.sql
sqlite> sqlite_version();
3.7.5

and after:

lua test.lua
3.3.17

I created the database with 3.7.5 version, but when running the called 
version was 3.3.17. I placed sqlite3.exe and Sqlite3.dll in ./clibs and 
I though it would be sufficient to update my version. However, if I 
understood the version embedded in luasql is older and there is no 
support for foreign keys.

In Lua script, I tried also:

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

But this attempt to enable via code the foreign key for the section 
doesn't result. Yes, the problem seems to be the old version of Sqlite 
used by Luasql.

Is the conclusion that I can't use foreign keys with 
Luasqlite.sqlite3()? If the answer is yes, the alternatives could be:

1. To manage relations among tables not in the database, but in the Lua 
code. When the number of tables or the number of relations are few, 
perhaps it would be the easier approach.

2. To change to another database more powerful, supporting foreign keys. 
But in this case, I don't want a separated process like Mysql.

Regarding a database which can be distributed together with the program 
without additional instalations and foreign keys support, is there 
another option of database? Could I use Luasql or should use another Lua 
module?

Em 13/03/2011 00:11, Bob Chapman escreveu:
> On Sat, Mar 12, 2011 at 6:20 PM, Luciano de Souza<luchyanus@gmail.com>  wrote:
>> Hi all,
>>
>> I want to use foreign keys in Sqlite3. I am using the modules available in
>> Lua for Windows and I downloaded the DLL and the commandline tool for
>> Sqlite3.
>> Does someone has an idea why I can't use foreign keys?
>>
> Does your example work for the command line tool? If so, this might be
> the problem:
>
> 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.3.17<====
>
> [Run using the Lua for Windows package]
>
> --
>