Re: [sqlite] Problem getting trigger to work

[Simon Slavin]

Quick note:

Please do not set 'reply-to' like that when using a discussion list.   
It's important if a question is posted to the list, answers to it get  
posted to the list.  This is so that if the first answer to a question  
is wrong, someone else can see it and post a correction.  It also  
allows the readers of the list to see whether someone else has  
answered a question, which means they don't have to bother posting the  
same advice themselves.


On 18 Jul 2009, at 7:46am, Jim Showalter wrote:

> It's an update.

Good.  That's what I thought you wanted but I've seen people make that  
mistake before.

> I don't know how to test this with the SQLite console, because it's  
> actually running on the Android emulator.

Get your database file to the point where you're just about to do the  
UPDATE command then have your application close it and terminates  
without changing it again.

Copy the database file to your development platform (your Windows/Mac/ 
Linux computer).

Open the database file with the 'sqlite3' command (which you'll have  
to find yourself) in interactive mode:



Doing a couple of commands to make sure the schema and data got copied  
over correctly.

Try executing the UPDATE command and see what the command-line tool  
spits out.  If it spits out the RAISE message you wanted, the problem  
is with the API you're using, or how you're using it, so you need to  
consult someone who understands the Android API.  If it doesn't,  
there's something wrong with your how SQLite works, or your  
understanding of how SQLite works, and you can post again describing  
what went wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem getting trigger to work

[Kees Nuyt]

On Fri, 17 Jul 2009 23:46:31 -0700, "Jim Showalter"
 wrote:

>
>It's an update.
>
>The Java code for my DataAccessor (a lightweight wrapper over 
>Android's wrapper over SQLite) checks the ID. If the ID is set to -1, 
>it's an insert, otherwise it's an update.
>
>A Word (word2) has been previously saved, and its ID has been saved to 
>word2Id.
>
>The test code is doing this:
>
>Word differentTextWord = new Word();
>differentTextWord.setId(word2Id); <<< reuse existing ID
>differentTextWord.setText(word2.getText() + "_different");
>
>boolean caughtExpectedException = false;
>
>try
>{
>dataAccessor.saveWord(differentTextWord);
>}
>catch (SQLiteConstraintException e)
>{
>caughtExpectedException = true;
>}
>
>assertTrue(caughtExpectedException);  this fails


SQLiteConstraintException sounds like a Java thing,
implemented by the wrapper, not the SQLite library itself.

I don't think the RAISE() in the trigger would raise a Java
exception by itself. Probably the wrapper doesn't raise an
exception when the sqlite library returns an error.

>I don't know how to test this with the SQLite console, 
>because it's actually running on the Android emulator.

Indeed. IMHO this is a problem with the usage of the sqlite
API.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem getting trigger to work

[Jim Showalter]

It's an update.

The Java code for my DataAccessor (a lightweight wrapper over 
Android's wrapper over SQLite) checks the ID. If the ID is set to -1, 
it's an insert, otherwise it's an update.

A Word (word2) has been previously saved, and its ID has been saved to 
word2Id.

The test code is doing this:

Word differentTextWord = new Word();
differentTextWord.setId(word2Id); <<< reuse existing ID
differentTextWord.setText(word2.getText() + "_different");

boolean caughtExpectedException = false;

try
{
dataAccessor.saveWord(differentTextWord);
}
catch (SQLiteConstraintException e)
{
caughtExpectedException = true;
}

assertTrue(caughtExpectedException); <<<< this fails

I don't know how to test this with the SQLite console, because it's 
actually running on the Android emulator.


- Original Message - 
From: "Simon Slavin" <slav...@hearsay.demon.co.uk>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, July 17, 2009 11:00 PM
Subject: Re: [sqlite] Problem getting trigger to work


>
> On 18 Jul 2009, at 4:32am, Jim Showalter wrote:
>
>> create table words
>> (
>>_id integer primary key autoincrement,
>>wordText text not null unique
>> );
>>
>> I have triggers that work, which I got from
>> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers.
>>
>> Now I'm trying to modify the update trigger to prevent saving a 
>> word
>> with the same ID but different text:
>>
>> create trigger ut_words_cannotChangeWordTextOnUpdate
>> before update on words
>> for each row
>> begin
>>select raise(
>>rollback,
>>'update on table WORDS violates constraint
>> ut_words_cannotChangeWordTextOnUpdate')
>>where OLD.wordText <> NEW.wordText;
>> end;
>
> What command are you using to make the modification ?  Is it an 
> INSERT
> or an UPDATE ?
>
> Have you tried executing the appropriate command in the sqlite3
> command-line application ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem getting trigger to work

[Simon Slavin]

On 18 Jul 2009, at 4:32am, Jim Showalter wrote:

> create table words
> (
>_id integer primary key autoincrement,
>wordText text not null unique
> );
>
> I have triggers that work, which I got from
> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers.
>
> Now I'm trying to modify the update trigger to prevent saving a word
> with the same ID but different text:
>
> create trigger ut_words_cannotChangeWordTextOnUpdate
> before update on words
> for each row
> begin
>select raise(
>rollback,
>'update on table WORDS violates constraint
> ut_words_cannotChangeWordTextOnUpdate')
>where OLD.wordText <> NEW.wordText;
> end;

What command are you using to make the modification ?  Is it an INSERT  
or an UPDATE ?

Have you tried executing the appropriate command in the sqlite3  
command-line application ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem getting trigger to work

[Jim Showalter]

I have this simple schema:

create table words
(
_id integer primary key autoincrement,
wordText text not null unique
);

I have triggers that work, which I got from 
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers.

Now I'm trying to modify the update trigger to prevent saving a word 
with the same ID but different text:

create trigger ut_words_cannotChangeWordTextOnUpdate
before update on words
for each row
begin
select raise(
rollback,
'update on table WORDS violates constraint 
ut_words_cannotChangeWordTextOnUpdate')
where OLD.wordText <> NEW.wordText;
end;

The schema seems to load without a problem, and I'm able to run my 
unit tests up to the point where I save a word with the same ID but 
with the wordText set to the existing word's wordText + "_different" 
(in other words, different text).

I expect a SQLiteConstraintException to be thrown, but no exception is 
thrown. It saves the modified word with the same ID, which it 
shouldn't be doing.

Yes, the trigger is being installed.

Is the trigger written incorrectly, or should I be looking somewhere 
else for the cause? If elsewhere, where? 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users