[sqlite] Perticular Field encription in sqlite3 database

[Sambasivarao Vemula]

Hi,
I want to encrypt a perticular field in sqlite3 database.
For Example I want encrypt a password field in a table .
Is there any special commands or technics for this encryption.
Please let me know is there any solution for this .

Thanks and Regards
Samba


DISCLAIMER
==
This e-mail may contain privileged and confidential information which is the 
property of Persistent Systems Ltd. It is intended only for the use of the 
individual or entity to which it is addressed. If you are not the intended 
recipient, you are not authorized to read, retain, copy, print, distribute or 
use this message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent Systems 
Ltd. does not accept any liability for virus infected mails.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting last inserted rowid?

[Igor Tandetnik]

Nikolaus Rath wrote:
> "Igor Tandetnik"  writes:
>> Nikolaus Rath  wrote:
>>> How can I determine the rowid of the last insert if I am accessing
>>> the db from different threads? If I understand correctly,
>>> last_insert_rowid() won't work reliably in this case.
>>
>> Last inserted rowid is maintained per connection. Do your threads use
>> the same connection, or each create their own?
>
> Same connection, just different cursors.
>
>> If all threads share the same connection, it is your responsibility
>> to make "insert then retrieve last rowid" an atomic operation, using
>> thread synchronization mechanism of your choice. Just as with any
>> access to shared data.
>
> Is BEGIN ... COMMIT sufficient for that?

No. Transaction is also maintained per connection. Starting a 
transaction would prevent other connections from making concurrent 
changes, but wouldn't block other threads using the same connection.

> Or do I have to take care
> that no other thread is even attempting to execute some SQL?

It's OK to execute SQL - just not another INSERT.

Igor Tandetnik 



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

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

Nuno Lucas  writes:
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath  wrote:
>> Hello,
>>
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> It should work if you do:
>
> BEGIN
> INSERT ...
> last_insert_rowid()
> END

That would be very nice. But does "it should work" mean that you know
that it works (and it is documented and guaranteed)? The above sounds a
bit uncertain to me...

Thanks for the clarification,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

[sqlite] UPDATE while SELECT is active

[Nikolaus Rath]

Hello,

I am pretty sure that at some point I have read if the following code
might bring me into trouble:

cur1 = conn.get_cursor()
cur2 = conn.get_cursor()

res = cur1.execute("SELECT id FROM data WHERE enabled == 1")
for name in res:
cur2.execute("UPDATE data SET enabled = 0 WHERE id=?", name)


So I am changing the result set over which I am iterating. 

Unfortunately I can't find the document where I read about it - can
someone point me to the right resource?

Direct answers are of course also welcome :-)

Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] Getting last inserted rowid?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath  wrote:
>> How can I determine the rowid of the last insert if I am accessing the
>> db from different threads? If I understand correctly,
>> last_insert_rowid() won't work reliably in this case.
>
> Last inserted rowid is maintained per connection. Do your threads use 
> the same connection, or each create their own?

Same connection, just different cursors.

> If all threads share the same connection, it is your responsibility to
> make "insert then retrieve last rowid" an atomic operation, using
> thread synchronization mechanism of your choice. Just as with any
> access to shared data.

Is BEGIN ... COMMIT sufficient for that? Or do I have to take care that
no other thread is even attempting to execute some SQL?


>> I can't believe that I really have to do a SELECT on the data that I
>> just INSERTed only to get the rowid...
>
> I'm not sure how this helps, if another thread can insert more data 
> between your INSERT and SELECT. Wouldn't that suffer from the same 
> problem?

Not in my case, because I can uniquely select the data that I have just
inserted. 


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] UPDATE TRIGGER works in all records

[Oliver Peters]

Am Montag, den 01.06.2009, 07:38 -0400 schrieb Igor Tandetnik:
> Oliver Peters wrote:
> > After an UPDATE in a record I want the update time stored in a column
> > of this record - the problem is that the trigger I use doesn't work
> > only in this record but in all others
> >
> > CREATE TRIGGER IF NOT EXISTS t_update_a
> >  AFTER UPDATE ON t
> >   BEGIN
> > UPDATE t SET b = DATETIME('now','localtime') WHERE new.a != old.a;
> >   END;
> 
> Make it
> 
> CREATE TRIGGER IF NOT EXISTS t_update_a
>  AFTER UPDATE OF a ON t
>   BEGIN
> UPDATE t SET b = DATETIME('now','localtime') WHERE rowid=new.rowid;
>   END;
> 
> Igor Tandetnik 
> 
> 

Thanks - that was the solution

First I didn't understand why this could work because the rowid I
queried never changed after an update but after studying the
documentation I discovered that it is only an alias for the real rowid.
Very helpful to know.

Oliver Peters

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

Re: [sqlite] Db design question (so. like a tree)

[Jan]

Hi,

thanks. I'll investigate this possibility.

John Stanton schrieb:
> Something to investigate is to use an AVL tree structure with rowids as 
> the pointers.  It would stay balanced and you could present family trees 
> quite simply as well as use SQL to extract data on individuals and sets 
> of individuals.
> 
> Jay A. Kreibich wrote:
>> On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall:
>>   
>>> Hi,
>>>
>>> I am planning a database for animal breeding. I need to store the 
>>> relations between individuals and therefore I have to build something 
>>> like a tree structure. But of course with two parents (There wont be 
>>> cloned animals in the database .-) afaik)
>>> 
>>
>>
>>   
>>> - adjacency list (not very difficult to understand)
>>> 
>>   Also easy to work with two parents, just have a "father" column and a
>>   "mother" column.
>>
>>   Adjacency lists are quick to update, but many queries can't be done
>>   in (standard) SQL by itself.  That usually isn't a problem, and it
>>   doesn't sound like you'll have millions of records.
>>
>>   
>>> - nested sets (hm, more difficult)
>>> 
>>   Nested sets only work with one parent.  They're also extremely
>>   expensive to update, although you can do some interesting tricks with
>>   queries.
>>
>>   
>>> - b tree (to difficult)
>>> 
>>   B-trees are more of a way to sort and access large amount of linear
>>   data.  They're not really designed to represent data that is
>>   inherently tree like.
>>
>>   
>>> - ? (something I missed?)
>>> 
>>   You might want to see if you can find a copy of one of Joe Celko's
>>   books... either "SQL for Smarties" (which has a chapter on trees) or
>>   the book "Joe Celko's Trees and Hierarchies in SQL for Smarties"
>>   which is all about trees.
>>
>>   That said, it sounds like you'd be well serviced by a two parent
>>   adjacency list and a bit of custom programming/scripting.
>>
>>-j
>>
>>   
> 
> ___
> 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] Db design question (so. like a tree)

[Jan]

Hi,
> If you don't want to update, but you do want to query for entire
> subtrees, do give nested sets more consideration.

But as Jay pointed out: Nested sets only work with one parent. Do they?
> 
> The best encoding for intervals I've yet seen is here:
> http://arxiv.org/pdf/0806.3115v1
> 
> Best wishes,
> Hamish
> ___
> 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] Db design question (so. like a tree)

[John Stanton]

Something to investigate is to use an AVL tree structure with rowids as 
the pointers.  It would stay balanced and you could present family trees 
quite simply as well as use SQL to extract data on individuals and sets 
of individuals.

Jay A. Kreibich wrote:
> On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall:
>   
>> Hi,
>>
>> I am planning a database for animal breeding. I need to store the 
>> relations between individuals and therefore I have to build something 
>> like a tree structure. But of course with two parents (There wont be 
>> cloned animals in the database .-) afaik)
>> 
>
>
>
>   
>> - adjacency list (not very difficult to understand)
>> 
>
>   Also easy to work with two parents, just have a "father" column and a
>   "mother" column.
>
>   Adjacency lists are quick to update, but many queries can't be done
>   in (standard) SQL by itself.  That usually isn't a problem, and it
>   doesn't sound like you'll have millions of records.
>
>   
>> - nested sets (hm, more difficult)
>> 
>
>   Nested sets only work with one parent.  They're also extremely
>   expensive to update, although you can do some interesting tricks with
>   queries.
>
>   
>> - b tree (to difficult)
>> 
>
>   B-trees are more of a way to sort and access large amount of linear
>   data.  They're not really designed to represent data that is
>   inherently tree like.
>
>   
>> - ? (something I missed?)
>> 
>
>   You might want to see if you can find a copy of one of Joe Celko's
>   books... either "SQL for Smarties" (which has a chapter on trees) or
>   the book "Joe Celko's Trees and Hierarchies in SQL for Smarties"
>   which is all about trees.
>
>   That said, it sounds like you'd be well serviced by a two parent
>   adjacency list and a bit of custom programming/scripting.
>
>-j
>
>   

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

Re: [sqlite] Db design question (so. like a tree)

[Hamish Allan]

On Tue, Jun 2, 2009 at 10:45 PM, Jan  wrote:

> Sounds good. I think I try that. Although updating is usually not
> necessary (once you have a mother/father its usually difficult to get
> rid of/update them .-) I read that there is problem with queries that go
> deeper in grand-grand-.. parents structure. But I think I can do a loop
> in my code.

If you don't want to update, but you do want to query for entire
subtrees, do give nested sets more consideration.

The best encoding for intervals I've yet seen is here:
http://arxiv.org/pdf/0806.3115v1

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

Re: [sqlite] sqlite programmed in C++

[John Stanton]

A good tool ltries to be "bette"r, not "easier".  Libraries compiled 
from C are compatible with C++.  In general optimization is better with 
C compilers and the support libraries are more compact and effcient.  
Producing Sqlite in well written and documented ANSI C was a very sound 
decision.

If you want to incorporate Sqlite in a C++ environment use C++ wrappers 
for the API calls.

Sylvain Pointeau wrote:
> ... because I experienced C++ to be easier with the classes and resource
> management via the destructor.I was just wondering why C++ is not used?
>
> was it for a performance issue?
> or a compiler issue?
> or anything else?
>
> I just read the Linus Torvalds comment on the C++ for Git
> What do you think?
>
> Cheers,
> Sylvain
>
> On Tue, Jun 2, 2009 at 6:16 PM, John Stanton  wrote:
>
>   
>> Why wuld you want to do such a thing?
>>
>> Sylvain Pointeau wrote:
>> 
>>> Hello,
>>> I would like to know if someone already though about to introduce C++ in
>>> SQLite?
>>> I just think about a minimal subset of C++ that will not make any
>>> performance penalty
>>> (like C with classes)
>>>
>>> is it a performance issue?
>>> is it a deployment/compiler issue?
>>> or any issue?
>>>
>>> Please don't make any aggressive reply, I am a very nice guy :-)
>>>
>>> Cheers,
>>> Sylvain
>>> ___
>>> 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
>>
>> 
> ___
> 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] Db design question (so. like a tree)

[Jan]

>> - adjacency list (not very difficult to understand)
> 
>   Also easy to work with two parents, just have a "father" column and a
>   "mother" column.
> 
>   Adjacency lists are quick to update, but many queries can't be done
>   in (standard) SQL by itself.  That usually isn't a problem, and it
>   doesn't sound like you'll have millions of records.

Sounds good. I think I try that. Although updating is usually not 
necessary (once you have a mother/father its usually difficult to get 
rid of/update them .-) I read that there is problem with queries that go 
deeper in grand-grand-.. parents structure. But I think I can do a loop 
in my code.

Thanks for your help!
Cheers
Jan

> 
>> - nested sets (hm, more difficult)
> 
>   Nested sets only work with one parent.  They're also extremely
>   expensive to update, although you can do some interesting tricks with
>   queries.
> 
>> - b tree (to difficult)
> 
>   B-trees are more of a way to sort and access large amount of linear
>   data.  They're not really designed to represent data that is
>   inherently tree like.
> 
>> - ? (something I missed?)
> 
>   You might want to see if you can find a copy of one of Joe Celko's
>   books... either "SQL for Smarties" (which has a chapter on trees) or
>   the book "Joe Celko's Trees and Hierarchies in SQL for Smarties"
>   which is all about trees.
> 
>   That said, it sounds like you'd be well serviced by a two parent
>   adjacency list and a bit of custom programming/scripting.
> 
>-j
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

[Jay A. Kreibich]

On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall:
> Hi,
> 
> I am planning a database for animal breeding. I need to store the 
> relations between individuals and therefore I have to build something 
> like a tree structure. But of course with two parents (There wont be 
> cloned animals in the database .-) afaik)



> - adjacency list (not very difficult to understand)

  Also easy to work with two parents, just have a "father" column and a
  "mother" column.

  Adjacency lists are quick to update, but many queries can't be done
  in (standard) SQL by itself.  That usually isn't a problem, and it
  doesn't sound like you'll have millions of records.

> - nested sets (hm, more difficult)

  Nested sets only work with one parent.  They're also extremely
  expensive to update, although you can do some interesting tricks with
  queries.

> - b tree (to difficult)

  B-trees are more of a way to sort and access large amount of linear
  data.  They're not really designed to represent data that is
  inherently tree like.

> - ? (something I missed?)

  You might want to see if you can find a copy of one of Joe Celko's
  books... either "SQL for Smarties" (which has a chapter on trees) or
  the book "Joe Celko's Trees and Hierarchies in SQL for Smarties"
  which is all about trees.

  That said, it sounds like you'd be well serviced by a two parent
  adjacency list and a bit of custom programming/scripting.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Db design question (so. like a tree)

[Jan]

Hi,

I am planning a database for animal breeding. I need to store the 
relations between individuals and therefore I have to build something 
like a tree structure. But of course with two parents (There wont be 
cloned animals in the database .-) afaik)

I read a little bit about

- adjacency list (not very difficult to understand)
- nested sets (hm, more difficult)
- b tree (to difficult)
- ? (something I missed?)

Could anyone give me an advice what to use or what else to read? Maybe 
someone has already done something similar e.g. genealogy.

Bye
Jan

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

Re: [sqlite] sqlite programmed in C++

[Thomas Briggs]

   I think you have to factor the age of SQLite into that explanation
as well.  I think the first versions of SQLite were released about 10
years ago, at which point C++ compilers were even more non-standard
than they are today.  Then, once it's functional and stable in C, why
rewrite it?

On Tue, Jun 2, 2009 at 3:44 PM, Kees Nuyt  wrote:
> On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau
>  wrote:
>
>>... because I experienced C++ to be easier with the classes and resource
>>management via the destructor.I was just wondering why C++ is not used?
>>
>>was it for a performance issue?
>>or a compiler issue?
>
> What Virgilio said:
>
> : Because there are many platforms that
> : sqlite runs (and can run at some time)
> : that doesn't have a C++ compiler
> : available, but they always have a
> : C compiler.
>
>>or anything else?
>
> C is more portable than C++ (fewer dialects, more
> standardized).
>
> For ease of use on platforms with a decent C++ compiler,
> there are good C++ wrappers. So, there is a choice for
> application development.
>
>>I just read the Linus Torvalds comment on the C++ for Git
>>What do you think?
>>
>>Cheers,
>>Sylvain
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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] Before Update trigger question

[Kees Nuyt]

On Tue, 2 Jun 2009 12:28:31 -0700 (PDT), Boris Ioffe
 wrote:

>
> Kees, 
> Thank you very much for quick prototype. I will use single 
> quotes from now on. It turns out Igor was right. 
> I had another trigger.

Yes, always read Igor's replies first ;)

Triggers are very powerful, yet tricky. Nevertheless, I
think it's a good idea to use TRIGGERs (and CONSTRAINTs) as
much as possible instead of application code.

>CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
>BEGIN
>  UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
>  WHERE rowid = new.rowid;
>END; 
>
>
> it ticked another update trigger. I found workaround by
> adding UPDATE OF clause for a specific field 
>
>CREATE TRIGGER validate_players_update_role BEFORE UPDATE OF role ON players
>
>
>Now I even understand why it works. 
>Thanks a lot,

You're welcome, have fun.

>-B
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite programmed in C++

[Kees Nuyt]

On Tue, 2 Jun 2009 21:30:51 +0200, Sylvain Pointeau
 wrote:

>... because I experienced C++ to be easier with the classes and resource
>management via the destructor.I was just wondering why C++ is not used?
>
>was it for a performance issue?
>or a compiler issue?

What Virgilio said:

: Because there are many platforms that 
: sqlite runs (and can run at some time)
: that doesn't have a C++ compiler 
: available, but they always have a 
: C compiler.

>or anything else?

C is more portable than C++ (fewer dialects, more
standardized).

For ease of use on platforms with a decent C++ compiler,
there are good C++ wrappers. So, there is a choice for
application development.

>I just read the Linus Torvalds comment on the C++ for Git
>What do you think?
>
>Cheers,
>Sylvain
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite programmed in C++

[Sylvain Pointeau]

... because I experienced C++ to be easier with the classes and resource
management via the destructor.I was just wondering why C++ is not used?

was it for a performance issue?
or a compiler issue?
or anything else?

I just read the Linus Torvalds comment on the C++ for Git
What do you think?

Cheers,
Sylvain

On Tue, Jun 2, 2009 at 6:16 PM, John Stanton  wrote:

> Why wuld you want to do such a thing?
>
> Sylvain Pointeau wrote:
> > Hello,
> > I would like to know if someone already though about to introduce C++ in
> > SQLite?
> > I just think about a minimal subset of C++ that will not make any
> > performance penalty
> > (like C with classes)
> >
> > is it a performance issue?
> > is it a deployment/compiler issue?
> > or any issue?
> >
> > Please don't make any aggressive reply, I am a very nice guy :-)
> >
> > Cheers,
> > Sylvain
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Before Update trigger question

[Boris Ioffe]

Thanks for asking your crystal ball. it works rather well. 

--- On Tue, 6/2/09, Igor Tandetnik  wrote:

> From: Igor Tandetnik 
> Subject: Re: [sqlite] Before Update trigger question
> To: sqlite-users@sqlite.org
> Date: Tuesday, June 2, 2009, 2:48 PM
> Boris Ioffe 
> wrote:
> > --- On Tue, 6/2/09, Igor Tandetnik
> > 
> wrote:
> >
> >> From: Igor Tandetnik 
> >> Subject: Re: [sqlite] Before Update trigger
> question
> >> To: sqlite-users@sqlite.org
> >> Date: Tuesday, June 2, 2009, 2:11 PM
> >> Boris Ioffe 
> >> wrote:
> >>> This is my first question on this mail list. I
> noticed
> >> that BEFORE
> >>> UPDATE trigger goes off even for insert
> statements.
> >>
> >> My crystal ball shows you have an INSERT trigger
> that
> >> performs an
> >> UPDATE.
> >
> > Very intrigued. You ruined  some sql foundational
> priciples I lived
> > upon. How is it possible for insert statement to
> update? Even replace
> > is (delete on constraint then insert). Can you
> elaborate your answer
> > a bit ?
> 
> CREATE TRIGGER players_insert AFTER INSERT ON players
> BEGIN
>     UPDATE players ...;
> END;
> 
> Igor Tandetnik
> 
> 
> 
> ___
> 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] Before Update trigger question

[Boris Ioffe]

Kees, 
Thank you very much for quick prototype. I will use single quotes from now on. 
It turns out Igor was right. 

I had another trigger 

CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
BEGIN
  UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
  WHERE rowid = new.rowid;
END; 


it ticked another update trigger.  I found workaround by adding UPDATE OF 
clause for a specific field 

CREATE TRIGGER validate_players_update_role BEFORE UPDATE OF role ON players


Now I even understand why it works. 
Thanks a lot,
-B

--- On Tue, 6/2/09, Kees Nuyt  wrote:

> From: Kees Nuyt 
> Subject: Re: [sqlite] Before Update trigger question
> To: sqlite-users@sqlite.org
> Date: Tuesday, June 2, 2009, 2:35 PM
> On Tue, 2 Jun 2009 09:38:18 -0700
> (PDT), Boris Ioffe
> 
> wrote:
> 
> >
> >Hello Gang, 
> >This is my first question on this mail list. I noticed
> that BEFORE UPDATE trigger goes off even for insert
> statements.  
> >
> >My example:
> >CREATE TRIGGER validate_players_update BEFORE UPDATE ON
> players
> >        WHEN (new.role in (1,2)
> and
> >            (select
> count(*) from players where table_group_id =
> new.table_group_id
> >               DROP TRIGGER validate_players_update_role; 
CREATE TRIGGER validate_players_update_role BEFORE UPDATE ON players
WHEN (new.role in (1,2) and
(select count(*) from players where table_group_id = 
new.table_group_id
and table_id = new.table_id
and role = new.role))

BEGIN
SELECT RAISE(ROLLBACK, "1002: Can not change player to this role at 
the table");
END;e
>                
>                
>     and table_id = new.table_id
> >               
>                
>                
>     and role = new.role))
> >
> >        BEGIN
> >            SELECT
> RAISE(FAIL, "1002: Can not sit player at this role at the
> table");
> >        END;
> >
> >
> >INSERT INTO players
> (device_id,table_group_id,table_id,role ) VALUES((select
> device_id from registrations where mesg_token ="aa"), 1,
> 2 , 2);
> >2009-06-02 10:43:36,086
> SQLEngine->pysqlite2.dbapi2.IntegrityError
> >Traceback (most recent call last):
> >  File "SQLEngine.py", line 39, in executeUpdate
> >    self.cur.execute(SQL, args)
> >IntegrityError: 1002: Can not sit player at this role
> at the table
> >
> >
> >Can someone please shed a light on this issue? 
> >Thanks, 
> >Boris 
> 
> I couldn't reproduce your problem. See code below. 
> (By the way, you really shouldn't use double quotes for
> string literals!)
> 
> The validate_players_update never fires.
> The validate_players_insert does.
> If I comment the validate_players_insert trigger out, no
> trigger fires.
> 
> Please provide a script that demonstrates the problem.
> It should run against the command line tool, like the SQL
> below, so things aren't obfuscated by a wrapper.
> 
> 
> sqlite_version():3.6.13 -- yeah, I should update.
> 
> CREATE TABLE players (
>     table_group_id  INTEGER,
>     table_id       
> INTEGER,
>     device_id   
>    INTEGER,
>     role         
>   INTEGER
> );
> CREATE TABLE registrations (
>     mesg_token      TEXT,
>     device_id   
>    INTEGER
> );
> CREATE TRIGGER validate_players_insert
>     BEFORE INSERT ON players
>         WHEN (new.role IN
> (1,2) AND
>            
>     (SELECT count(*) FROM players
>            
> WHERE table_group_id = new.table_group_id
>            
>   AND table_id = new.table_id
>            
>   AND role = new.role))
>         BEGIN
>            
> SELECT RAISE(FAIL, '1001: Insert');
>         END;
> CREATE TRIGGER validate_players_update
>     BEFORE UPDATE ON players
>         WHEN (new.role IN
> (1,2) AND
>            
>     (SELECT count(*) FROM players
>            
> WHERE table_group_id = new.table_group_id
>            
>   AND table_id = new.table_id
>            
>   AND role = new.role))
>         BEGIN
>            
> SELECT RAISE(FAIL, '1002: Update');
>         END;
> INSERT INTO registrations VALUES ('aa',1);
> INSERT INTO registrations VALUES ('bb',2);
> INSERT INTO players
> (device_id,table_group_id,table_id,role
> ) VALUES (
>     (SELECT device_id FROM registrations
>         WHERE mesg_token =
> 'bb')
>     , 1, 2, 2);
> INSERT INTO players
> (device_id,table_group_id,table_id,role
> ) VALUES (
>     (SELECT device_id FROM registrations
>         WHERE mesg_token =
> 'aa')
>     , 1, 2, 2);
> SQL error near line 38: 1001: Insert
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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] Before Update trigger question

[Igor Tandetnik]

Boris Ioffe  wrote:
> --- On Tue, 6/2/09, Igor Tandetnik
>  wrote:
>
>> From: Igor Tandetnik 
>> Subject: Re: [sqlite] Before Update trigger question
>> To: sqlite-users@sqlite.org
>> Date: Tuesday, June 2, 2009, 2:11 PM
>> Boris Ioffe 
>> wrote:
>>> This is my first question on this mail list. I noticed
>> that BEFORE
>>> UPDATE trigger goes off even for insert statements.
>>
>> My crystal ball shows you have an INSERT trigger that
>> performs an
>> UPDATE.
>
> Very intrigued. You ruined  some sql foundational priciples I lived
> upon. How is it possible for insert statement to update? Even replace
> is (delete on constraint then insert). Can you elaborate your answer
> a bit ?

CREATE TRIGGER players_insert AFTER INSERT ON players
BEGIN
UPDATE players ...;
END;

Igor Tandetnik



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

Re: [sqlite] Before Update trigger question

[Boris Ioffe]

Very intrigued. You ruined  some sql foundational priciples I lived upon. How 
is it possible for insert statement to update? Even replace is (delete on 
constraint then insert). Can you elaborate your answer a bit ? 

THanks, 
Boris  

--- On Tue, 6/2/09, Igor Tandetnik  wrote:

> From: Igor Tandetnik 
> Subject: Re: [sqlite] Before Update trigger question
> To: sqlite-users@sqlite.org
> Date: Tuesday, June 2, 2009, 2:11 PM
> Boris Ioffe 
> wrote:
> > This is my first question on this mail list. I noticed
> that BEFORE
> > UPDATE trigger goes off even for insert statements.
> 
> My crystal ball shows you have an INSERT trigger that
> performs an 
> UPDATE.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> 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] Before Update trigger question

[Kees Nuyt]

On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe
 wrote:

>
>Hello Gang, 
>This is my first question on this mail list. I noticed that BEFORE UPDATE 
>trigger goes off even for insert statements.  
>
>My example:
>CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
>WHEN (new.role in (1,2) and
>(select count(*) from players where table_group_id = 
> new.table_group_id
>and table_id = new.table_id
>and role = new.role))
>
>BEGIN
>SELECT RAISE(FAIL, "1002: Can not sit player at this role at the 
> table");
>END;
>
>
>INSERT INTO players (device_id,table_group_id,table_id,role ) VALUES((select 
>device_id from registrations where mesg_token ="aa"), 1, 2 , 2);
>2009-06-02 10:43:36,086 SQLEngine->pysqlite2.dbapi2.IntegrityError
>Traceback (most recent call last):
>  File "SQLEngine.py", line 39, in executeUpdate
>self.cur.execute(SQL, args)
>IntegrityError: 1002: Can not sit player at this role at the table
>
>
>Can someone please shed a light on this issue? 
>Thanks, 
>Boris 

I couldn't reproduce your problem. See code below. 
(By the way, you really shouldn't use double quotes for
string literals!)

The validate_players_update never fires.
The validate_players_insert does.
If I comment the validate_players_insert trigger out, no
trigger fires.

Please provide a script that demonstrates the problem.
It should run against the command line tool, like the SQL
below, so things aren't obfuscated by a wrapper.


sqlite_version():3.6.13 -- yeah, I should update.

CREATE TABLE players (
table_group_id  INTEGER,
table_idINTEGER,
device_id   INTEGER,
roleINTEGER
);
CREATE TABLE registrations (
mesg_token  TEXT,
device_id   INTEGER
);
CREATE TRIGGER validate_players_insert
BEFORE INSERT ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
  AND table_id = new.table_id
  AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1001: Insert');
END;
CREATE TRIGGER validate_players_update
BEFORE UPDATE ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
  AND table_id = new.table_id
  AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1002: Update');
END;
INSERT INTO registrations VALUES ('aa',1);
INSERT INTO registrations VALUES ('bb',2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'bb')
, 1, 2, 2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'aa')
, 1, 2, 2);
SQL error near line 38: 1001: Insert
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Before Update trigger question

[Igor Tandetnik]

Boris Ioffe  wrote:
> This is my first question on this mail list. I noticed that BEFORE
> UPDATE trigger goes off even for insert statements.

My crystal ball shows you have an INSERT trigger that performs an 
UPDATE.

Igor Tandetnik 



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

[sqlite] FTS3

[Martin Pfeifle]

Some further question regarding FTS3.

Am I correct that a doclist of a certain term is never split onto two blocks 
(BLOBs)?
Can we somehow limit the size of such BLOBs?
I did some tests where I inserted Millions of addresses into FTS3 and all 
contained a certain term.
I ended up with some Blobs bigger than 1MByte.
Can I somehow avoid this?
Best Martin


 




Von: Martin Pfeifle 
An: General Discussion of SQLite Database 
Gesendet: Freitag, den 29. Mai 2009, 08:59:45 Uhr
Betreff: Re: [sqlite] FTS3

One further question:

In fts3.c, a comment is found which describes the file format dependent on the 
different compiler settings.
* Result formats differ with the setting of DL_DEFAULTS.  Examples:
**
** DL_DOCIDS: [1] [3] [7]
** DL_POSITIONS: [1 0[0 4] 1[17]] [3 1[5]]
** DL_POSITIONS_OFFSETS: [1 0[0,0,3 4,23,26] 1[17,102,105]] [3 1[5,20,23]]
 
I also found one functional limitation if we use only  DL_DOCIDS, in order to 
reduce the overall size.
 
/*
** By default, only positions and not offsets are stored in the doclists.
** To change this so that offsets are stored too, compile with
**
**  -DDL_DEFAULT=DL_POSITIONS_OFFSETS
**
** If DL_DEFAULT is set to DL_DOCIDS, your table can only be inserted
** into (no deletes or updates).
*/

Are there any other functional drawbacks if we go for DOCIDS only, e.g. search 
for "term1 term2" in a document?

Best Martin




Von: D. Richard Hipp 
An: General Discussion of SQLite Database 
Gesendet: Dienstag, den 26. Mai 2009, 12:27:59 Uhr
Betreff: Re: [sqlite] FTS3


On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote:

> Dear all,
> we need full and fuzzy text search for addresses.
> Currently we are looking into Lucene and SQLite's FTS extension.
> For us it is crucial to understand the file structures and the  
> concepts behind the libraries.
> Is there a self-contained, comprehensive document for FTS3 (besides  
> the comments in fts3.c) ?

There is no information on FTS3 apart from the code comments and the  
README files in the source tree.

The file formats for FTS3 and lucene are completely different at the  
byte level.  But if you dig deeper, you will find that they both use  
the same underlying concepts and ideas and really are two different  
implementations of the same algorithm.  During development, we were  
constantly testing the performance and index size of FTS3 against  
CLucene using the Enron email corpus.  Our goal was for FTS3 to run  
significantly faster than CLucene and to generate an index that was no  
larger in size.  That goal was easily met at the time, though we have  
not tested FTS3 against CLucene lately to see if anything has changed.

One of the issues with CLucene that FTS3 sought to address was that  
when inserting new elements into the index, the insertion time was  
unpredictable.  Usually the insertions would be very fast.  But lucene  
will occasionally take a very long time for a single insertion in  
order to merge multiple smaller indices into larger indices.  This was  
seen as undesirable.  FTS3 strives to give much better worst-case  
insertion times by doing index merges incrementally and spreading the  
cost of index merges across many inserts.

D. Richard Hipp
d...@hwaci.com



___
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



      
___
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] sqlite, MPI and PostgreSQL

[J Glassy]

Hello Rogrigo,
 I'll take a shot at a response to this, bearing in mind there are quite a
few salient details you haven't
yet provided in this post.

 --As a principle, I'd recommend keeping  your solutions architecture as
simple as possible,
and use either PostgreSQL or SQLite, but not both, barring really good
reasons to the contrary.

--Integrating MPI (MPICH2 etc) with any relational database begs for very
careful partitioning of the
problem and the workflows used to address the problem; yes, storing
computational results in a
either SQLite or PostgreSQL makes perfect sense, but I'd look for clear
process-boundaries and
checkpoint/milestone events (say, after a salvo of concurrent tasks have
been synchronized) to perform your
updates to any database, and assure that any application I/O buffers are
completely flushed when
integrating any relational database within any parallel or concurrent
application.  PostgreSQL is
similar to many other sophisticated RDBMS in that it maintains its own
internal, multi-level caches.

--Bear in mind, I'm a huge fan of SQLite for many applications, but its a
question of balancing its
relative strengths and weaknesses over PostgreSQL, and of course, assessing
which environment you
know better as well (or which you could learn the necessary subtlies, if you
don't really know either).
If PostgreSQL serves some roles that you find SQLite cannot easily serve,
I'd just go with an all PostgreSQL
solution. I suspect you'll have plenty of other technical and scientific
areas of your work to ascertain the
correctness of (and possibly debug), than to have to also take on the added
complexity of understanding
the interaction effects of two different SQL engines, if a problem emerges.

--I would say it is "possible" to "employ SQLite" in an MPI application, but
again, I think it depends a
lot on how you are structuring your particular problem. If the runtime
platform is "dual-core", I presume
you're at least planning to deploy on  (2) cores concurrently, but there are
different mechanisms in
MPI addressing for addressing multi-core architectures (on the same CPU and
memory bus) than those
addressing parallelism via interconnects between many separate CPU/memory
buses (or blades, racks etc).
Absent more information on your execution architecture, I'm guessing you're
looking at some sort
of multi-threaded (or multi heavy-weight process) design on a master bus.
If so, you'll definitely want
to factor in some very deterministic thread synchronization relative to when
you perform writes to
any database, whether it is SQLite or PostgreSQL. Generally speaking,
concurrent reads on a backing
store (SQL database) are often not as critical to schedule, but still bear
careful consideration if you use
these to trigger the start of a subsequent parallel process gang.

OK, I'll stop there, but just wanted to address the question in overview.
Some of the others on this list
may be able to address this from a more SQLite centric perspective. good
luck.

Joe

On Tue, Jun 2, 2009 at 10:33 AM, Rodrigo Faccioli <
faccioli.postgre...@gmail.com> wrote:

> Hello,
>
> I'm very new user about SQLite. I'm working with Structural Bioinformatics
> and everybody knows that this area requires a lot of computational
> resources. So, I'm developing an Evolutionary Algorithms (EA) for some
> protein analysis (I don't know what will do exactly, because I've just
> started my PhD).
>
> However, I know that I'll work with C++ and mpi. I've worked with
> PostgreSQL
> and Python with SQLAlmy and I've enjoyed. I've known about sqlite in
> SQLAlmy
> website. I'm enjoying the sqlite although I have a question about sqlite
> and
> mpi application.
>
> Is it possible to employ sqlite in mpi application? Example: I have a
> computer which is a dual-core and my program works with mpi. Its goal is to
> calculate and store it on text file. So, I want to change from text file to
> SQLite.
>
> Another question refers to PostgreSQL and SQLite. I've read that SQLite
> doesn't work with client-server applications. So, if my first question was
> correctly, I have the idea using SQLite and PostgreSQL. The goal of
> PostgreSQL is to attend my web-service applications and SQLite goal is to
> store the results of calculation. It's clean that PostgreSQL will store the
> results were created by my program which works with SQLite. Therefore, I
> would know good way to work PostgreSQL and SQLite.
>
> I thanks for any help.
>
> Regards,
>
> Rodrigo Antonio Faccioli
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

---
Joseph Glassy
Lead Software Engineer (contractor)
NASA Measures (Freeze/Thaw),Rm CFC 424
CFC, Univ. Montana, Missoula, MT 59812
and:
Research Analyst/Programmer
University of Montana NSF EPSCoR Program
Missoula, MT 59812

Re: [sqlite] sqlite, MPI and PostgreSQL

[Simon Slavin]

On 2 Jun 2009, at 5:33pm, Rodrigo Faccioli wrote:

> mpi

Which of the many things called 'mpi' are you talking about ?  A URL  
will be good.

> Is it possible to employ sqlite in mpi application? Example: I have a
> computer which is a dual-core and my program works with mpi. Its  
> goal is to
> calculate and store it on text file. So, I want to change from text  
> file to
> SQLite.

Anything that can do library calls can do SQLite calls.  Can you  
compile the SQLite library for your platform ?  Or find a precompiled  
binary ?

> Another question refers to PostgreSQL and SQLite. I've read that  
> SQLite
> doesn't work with client-server applications.

You may have not have understood this correctly.  PostgreSQL itself  
runs as a client-server application.  The SQL server runs on one  
computer and many client computers talk give it SQL commands.  All the  
SQL data is stored on the server, not the client computers.  This is  
very useful if you have lots of different computers that need to  
access the same data at the same time.

SQLite is a stand-alone library: each application accesses it's own  
data kept on the computer the application is running on.  There's  
nothing to stop you from using SQLite in either part of a client- 
server application you are writing: either of those programs might  
need to store data locally.  It works fine.

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

[sqlite] Before Update trigger question

[Boris Ioffe]

Hello Gang, 
This is my first question on this mail list. I noticed that BEFORE UPDATE 
trigger goes off even for insert statements.  

My example:
CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
WHEN (new.role in (1,2) and
(select count(*) from players where table_group_id = 
new.table_group_id
and table_id = new.table_id
and role = new.role))

BEGIN
SELECT RAISE(FAIL, "1002: Can not sit player at this role at the 
table");
END;


INSERT INTO players (device_id,table_group_id,table_id,role ) VALUES((select 
device_id from registrations where mesg_token ="aa"), 1, 2 , 2);
2009-06-02 10:43:36,086 SQLEngine->pysqlite2.dbapi2.IntegrityError
Traceback (most recent call last):
  File "SQLEngine.py", line 39, in executeUpdate
self.cur.execute(SQL, args)
IntegrityError: 1002: Can not sit player at this role at the table


Can someone please shed a light on this issue? 
Thanks, 
Boris 



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

[sqlite] sqlite, MPI and PostgreSQL

[Rodrigo Faccioli]

Hello,

I'm very new user about SQLite. I'm working with Structural Bioinformatics
and everybody knows that this area requires a lot of computational
resources. So, I'm developing an Evolutionary Algorithms (EA) for some
protein analysis (I don't know what will do exactly, because I've just
started my PhD).

However, I know that I'll work with C++ and mpi. I've worked with PostgreSQL
and Python with SQLAlmy and I've enjoyed. I've known about sqlite in SQLAlmy
website. I'm enjoying the sqlite although I have a question about sqlite and
mpi application.

Is it possible to employ sqlite in mpi application? Example: I have a
computer which is a dual-core and my program works with mpi. Its goal is to
calculate and store it on text file. So, I want to change from text file to
SQLite.

Another question refers to PostgreSQL and SQLite. I've read that SQLite
doesn't work with client-server applications. So, if my first question was
correctly, I have the idea using SQLite and PostgreSQL. The goal of
PostgreSQL is to attend my web-service applications and SQLite goal is to
store the results of calculation. It's clean that PostgreSQL will store the
results were created by my program which works with SQLite. Therefore, I
would know good way to work PostgreSQL and SQLite.

I thanks for any help.

Regards,

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

Re: [sqlite] sqlite programmed in C++

[John Stanton]

Why wuld you want to do such a thing?

Sylvain Pointeau wrote:
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)
>
> is it a performance issue?
> is it a deployment/compiler issue?
> or any issue?
>
> Please don't make any aggressive reply, I am a very nice guy :-)
>
> Cheers,
> Sylvain
> ___
> 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] Types for strings, non-expert question

[Nicolas Williams]

On Mon, Jun 01, 2009 at 08:56:57PM -0700, Roger Binns wrote:
> Dennis Cote wrote:
> > Do you have a list of such changes that should be implemented in the 
> > next breaking release of SQLite?
> 
> I assume you are talking about a major release (ie SQLite v4 not 3.7).
> 
> > I'm thinking of things like renaming the _v2 API functions (in 
> > particular prepare_v2) to drop the suffix, 
> 
> That would change the ABI breaking any existing shared library linking.
>  It would be ok if there were separate SQLite v3 and v4 shared libraries.

I agree.  The ABI must remain stable in 3.x, modulo some obsolescence
policy (e.g., two minor releases between announcement of interface
obsolescence and removal).

Source compatibility (e.g., changing "char" to/from "unsigned char") is
somewhat less important than ABI compatibility, but still a big deal.

> My list of good changes to make are:

+1

> Some nice to haves:
> 
> - - Provide access to the SQL parse tree so that interactive tools can
> provide a richer user experience.  It is probably going too far from
> *lite* but being able to change the tree would also allow external code
> to perform query optimisation.

This could be a separate library too, using shared source code.

> - - Rejig the threading model so that each connection can only be used in
> one thread.  Provide some way to clone an existing open connection so
> that the new cloned connection can be used in the other thread.  Then
> remove almost all of the mutexes etc since they are increasing hurtful
> on modern machines (see memory write barriers as an example of why).

+1

(Many libraries take that approach, and SQLite3 ought to as well, IMO.)

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

Re: [sqlite] sqlite programmed in C++

[Virgilio Fornazin]

Because there are many platforms that sqlite runs (and can run at some time)

that doesn't have a C++ compiler available, but they always have a C
compiler.

[]'s

On Tue, Jun 2, 2009 at 12:33, Sylvain Pointeau
wrote:

> I didn't mean to program with sqlite in C++ ...
> I just meant to introduce C++ inside SQLite instead of C...
> I would like to understand why SQLite is programmed only in C 
>
> Cheers,
> Sylvain
>
> On Tue, Jun 2, 2009 at 5:15 PM,  wrote:
>
> > One of the best and worst things about C++ is its ability to use C code
> > (almost) transparently.  Best because it allows you to use things like
> > SQLite seamlessly in your C++ code. Worst because it allows some
> > programming idioms that are in opposition to good object oriented
> > design.  But let's concentrate on the up-side.
> >
> > There are C++ wrappers to SQLite out there if you care to look for them,
> > but depending upon the complexity of your application you may find you
> > don't need them.  It is easy to contain a SQLite connection inside a
> > class object, easy to compose your queries in a std::string (or a
> > CString depending on your environment and preferences) and fairly easy
> > to get your query results into a container class.
> >
> > While I would never dream of starting from scratch on anything of the
> > size and complexity of SQLite without C++, the STL, and probably Boost,
> > I also would never advocate fixing something that ain't broke. As an
> > applications programmer you can get all the advantages of C++ without
> > any changes to SQLite as written.
> >
> > Just my $0.02 worth.
> >
> > Logan Ratner | Software Engineer | Gas Chromatographs
> > Emerson Process Management | 5650 Brittmoore | Houston | TX | 77041 |
> > USA
> > T +1 713 839 9656 | F +1 713 827 3807
> >
> >
> > logan.rat...@emerson.com-original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
> > Sent: Tuesday, June 02, 2009 9:58 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] sqlite programmed in C++
> >
> > Hello,
> > I would like to know if someone already though about to introduce C++ in
> > SQLite?
> > I just think about a minimal subset of C++ that will not make any
> > performance penalty
> > (like C with classes)
> >
> > is it a performance issue?
> > is it a deployment/compiler issue?
> > or any issue?
> >
> > Please don't make any aggressive reply, I am a very nice guy :-)
> >
> > Cheers,
> > Sylvain
> > ___
> > 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
> >
> ___
> 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] Syntax to set the conflict resolution of a transaction

[Karl Brandt]

2009/6/2 Kees Nuyt :
> On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
>  wrote:
>>
>>Let me explain the complete picture so someone can help me.
>>
>>I develop a wrapper around sqlite that tracks the changed records and
>>than save the changes to the database by building and executing a SQL
>>query (a transaction).
>>
>>Currently it executes the SQL and check the return value.
>>If the return value is different from SQLITE_OK it executes a
>>separated ROLLBACK command so another transaction can be started.
>>
>>The problem is that after the ROLLBACK command, sqlite3_errmsg will
>>return "no error", giving the user no clues of what happened.
>>
>>I tried INSERT OR ROLLBACK syntax but it will work only for
>>SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.
>>
>>So there's a way to check if a transaction failed (for constraint or
>>another error) and than rollback without clearing the error message
>>returned by sqlite3_errmsg?
>
> After a ROLLBACK; there is no error (ROLLBACK is succesful),
> so the error message will be cleared.
>
> You can use INSERT  ON CONFLICT ABORT ... ;
> Catch the constraint error, fetch the sqlite3_errmsg() and
> ROLLBACK yourself.
>

Thanks.
I'm aware of this solution. The problem is that storing the
sqlite3_errmsg result is not doable because i expose the return (or
error) message in a separated function, so i'd need to store the msg
in the other functions where a sql is executed leading to poor
performance (not always the user check for the return string).

It works like that:

ApplyUpdates executes the query
ReturnString returns the return/error string by calling sqlite3_errmsg

call ApplyUpdates
if something got wrong check ReturnString value

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

Re: [sqlite] sqlite programmed in C++

[Fred Williams]

I figure the only reasons it is written in C is for portability and
Assembler programming is a bitch!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of P Kishor
Sent: Tuesday, June 02, 2009 10:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite programmed in C++


On Tue, Jun 2, 2009 at 10:33 AM, Sylvain Pointeau
 wrote:
> I didn't mean to program with sqlite in C++ ...
> I just meant to introduce C++ inside SQLite instead of C...
> I would like to understand why SQLite is programmed only in C 

Definitively only DRH can answer. My speculation is -- because ANSI
standard C is the cleanest, most portable language across all the
variety of hardware/OS platforms to which SQLite is ported.

It works, fast, quick, cheap as is evident by, well, by evidence. Why
bother with something more complex, as unnecessary complexity is a
sure recipe for disaster.

>
> Cheers,
> Sylvain
>
> On Tue, Jun 2, 2009 at 5:15 PM,  wrote:
>
>> One of the best and worst things about C++ is its ability to use C code
>> (almost) transparently.  Best because it allows you to use things like
>> SQLite seamlessly in your C++ code. Worst because it allows some
>> programming idioms that are in opposition to good object oriented
>> design.  But let's concentrate on the up-side.
>>
>> There are C++ wrappers to SQLite out there if you care to look for them,
>> but depending upon the complexity of your application you may find you
>> don't need them.  It is easy to contain a SQLite connection inside a
>> class object, easy to compose your queries in a std::string (or a
>> CString depending on your environment and preferences) and fairly easy
>> to get your query results into a container class.
>>
>> While I would never dream of starting from scratch on anything of the
>> size and complexity of SQLite without C++, the STL, and probably Boost,
>> I also would never advocate fixing something that ain't broke. As an
>> applications programmer you can get all the advantages of C++ without
>> any changes to SQLite as written.
>>
>> Just my $0.02 worth.
>>
>> Logan Ratner | Software Engineer | Gas Chromatographs
>> Emerson Process Management | 5650 Brittmoore | Houston | TX | 77041 |
>> USA
>> T +1 713 839 9656 | F +1 713 827 3807
>>
>>
>> logan.rat...@emerson.com-original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
>> Sent: Tuesday, June 02, 2009 9:58 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] sqlite programmed in C++
>>
>> Hello,
>> I would like to know if someone already though about to introduce C++ in
>> SQLite?
>> I just think about a minimal subset of C++ that will not make any
>> performance penalty
>> (like C with classes)
>>
>> is it a performance issue?
>> is it a deployment/compiler issue?
>> or any issue?
>>
>> Please don't make any aggressive reply, I am a very nice guy :-)
>>
>> Cheers,
>> Sylvain
>> ___
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Puneet Kishor
___
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] help,low RAM problem

[Christopher Taylor]

I have used sqlite on a similar platform.  I use the GHS Integrity operating
system.  With a few tweaks I was able to get it to run well.  The flash will
keep things on the slower side, but I see my inserts (with idexes) taking a
little as 0.05 seconds.  Queries of 250 out of 10K sorted taking less than 1
second.  Of course we are not talking apples to apples.  I do not know you
application and cannot tell you how it will perform on your app.

Hope this helps some.

Chris

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

Re: [sqlite] sqlite programmed in C++

[P Kishor]

On Tue, Jun 2, 2009 at 10:33 AM, Sylvain Pointeau
 wrote:
> I didn't mean to program with sqlite in C++ ...
> I just meant to introduce C++ inside SQLite instead of C...
> I would like to understand why SQLite is programmed only in C 

Definitively only DRH can answer. My speculation is -- because ANSI
standard C is the cleanest, most portable language across all the
variety of hardware/OS platforms to which SQLite is ported.

It works, fast, quick, cheap as is evident by, well, by evidence. Why
bother with something more complex, as unnecessary complexity is a
sure recipe for disaster.

>
> Cheers,
> Sylvain
>
> On Tue, Jun 2, 2009 at 5:15 PM,  wrote:
>
>> One of the best and worst things about C++ is its ability to use C code
>> (almost) transparently.  Best because it allows you to use things like
>> SQLite seamlessly in your C++ code. Worst because it allows some
>> programming idioms that are in opposition to good object oriented
>> design.  But let's concentrate on the up-side.
>>
>> There are C++ wrappers to SQLite out there if you care to look for them,
>> but depending upon the complexity of your application you may find you
>> don't need them.  It is easy to contain a SQLite connection inside a
>> class object, easy to compose your queries in a std::string (or a
>> CString depending on your environment and preferences) and fairly easy
>> to get your query results into a container class.
>>
>> While I would never dream of starting from scratch on anything of the
>> size and complexity of SQLite without C++, the STL, and probably Boost,
>> I also would never advocate fixing something that ain't broke. As an
>> applications programmer you can get all the advantages of C++ without
>> any changes to SQLite as written.
>>
>> Just my $0.02 worth.
>>
>> Logan Ratner | Software Engineer | Gas Chromatographs
>> Emerson Process Management | 5650 Brittmoore | Houston | TX | 77041 |
>> USA
>> T +1 713 839 9656 | F +1 713 827 3807
>>
>>
>> logan.rat...@emerson.com-original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
>> Sent: Tuesday, June 02, 2009 9:58 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] sqlite programmed in C++
>>
>> Hello,
>> I would like to know if someone already though about to introduce C++ in
>> SQLite?
>> I just think about a minimal subset of C++ that will not make any
>> performance penalty
>> (like C with classes)
>>
>> is it a performance issue?
>> is it a deployment/compiler issue?
>> or any issue?
>>
>> Please don't make any aggressive reply, I am a very nice guy :-)
>>
>> Cheers,
>> Sylvain
>> ___
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] sqlite programmed in C++

[Sylvain Pointeau]

I didn't mean to program with sqlite in C++ ...
I just meant to introduce C++ inside SQLite instead of C...
I would like to understand why SQLite is programmed only in C 

Cheers,
Sylvain

On Tue, Jun 2, 2009 at 5:15 PM,  wrote:

> One of the best and worst things about C++ is its ability to use C code
> (almost) transparently.  Best because it allows you to use things like
> SQLite seamlessly in your C++ code. Worst because it allows some
> programming idioms that are in opposition to good object oriented
> design.  But let's concentrate on the up-side.
>
> There are C++ wrappers to SQLite out there if you care to look for them,
> but depending upon the complexity of your application you may find you
> don't need them.  It is easy to contain a SQLite connection inside a
> class object, easy to compose your queries in a std::string (or a
> CString depending on your environment and preferences) and fairly easy
> to get your query results into a container class.
>
> While I would never dream of starting from scratch on anything of the
> size and complexity of SQLite without C++, the STL, and probably Boost,
> I also would never advocate fixing something that ain't broke. As an
> applications programmer you can get all the advantages of C++ without
> any changes to SQLite as written.
>
> Just my $0.02 worth.
>
> Logan Ratner | Software Engineer | Gas Chromatographs
> Emerson Process Management | 5650 Brittmoore | Houston | TX | 77041 |
> USA
> T +1 713 839 9656 | F +1 713 827 3807
>
>
> logan.rat...@emerson.com-original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
> Sent: Tuesday, June 02, 2009 9:58 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite programmed in C++
>
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)
>
> is it a performance issue?
> is it a deployment/compiler issue?
> or any issue?
>
> Please don't make any aggressive reply, I am a very nice guy :-)
>
> Cheers,
> Sylvain
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax to set the conflict resolution of a transaction

[Kees Nuyt]

On Tue, 2 Jun 2009 10:35:12 -0300, Karl Brandt
 wrote:

>2009/6/2 J. King 
>>
>> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt 
>> wrote:
>>
>> > I'm trying to set the conflict resolution of an entire transaction by
>> > using the ON CONFLICT clause without success.
>> >
>> > [...]
>> >
>> > Is there a way to set the conflict resolution for an entire transaction?
>>
>> Such a thing is not possible.  You may specify a conflict resolution on a
>> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,
>> NOT NULL and UNIQUE constraints), but not on a transaction.
>
>Thanks for the info.
>
>Let me explain the complete picture so someone can help me.
>
>I develop a wrapper around sqlite that tracks the changed records and
>than save the changes to the database by building and executing a SQL
>query (a transaction).
>
>Currently it executes the SQL and check the return value.
>If the return value is different from SQLITE_OK it executes a
>separated ROLLBACK command so another transaction can be started.
>
>The problem is that after the ROLLBACK command, sqlite3_errmsg will
>return "no error", giving the user no clues of what happened.
>
>I tried INSERT OR ROLLBACK syntax but it will work only for
>SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.
>
>So there's a way to check if a transaction failed (for constraint or
>another error) and than rollback without clearing the error message
>returned by sqlite3_errmsg?

After a ROLLBACK; there is no error (ROLLBACK is succesful),
so the error message will be cleared.

You can use INSERT  ON CONFLICT ABORT ... ;
Catch the constraint error, fetch the sqlite3_errmsg() and
ROLLBACK yourself.

http://www.sqlite.org/lang_conflict.html

(untested)

>Thanks in advance.
>
>Luiz
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite programmed in C++

[Logan.Ratner]

One of the best and worst things about C++ is its ability to use C code
(almost) transparently.  Best because it allows you to use things like
SQLite seamlessly in your C++ code. Worst because it allows some
programming idioms that are in opposition to good object oriented
design.  But let's concentrate on the up-side.

There are C++ wrappers to SQLite out there if you care to look for them,
but depending upon the complexity of your application you may find you
don't need them.  It is easy to contain a SQLite connection inside a
class object, easy to compose your queries in a std::string (or a
CString depending on your environment and preferences) and fairly easy
to get your query results into a container class.

While I would never dream of starting from scratch on anything of the
size and complexity of SQLite without C++, the STL, and probably Boost,
I also would never advocate fixing something that ain't broke. As an
applications programmer you can get all the advantages of C++ without
any changes to SQLite as written.

Just my $0.02 worth.

Logan Ratner | Software Engineer | Gas Chromatographs
Emerson Process Management | 5650 Brittmoore | Houston | TX | 77041 |
USA
T +1 713 839 9656 | F +1 713 827 3807


logan.rat...@emerson.com-original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
Sent: Tuesday, June 02, 2009 9:58 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite programmed in C++

Hello,
I would like to know if someone already though about to introduce C++ in
SQLite?
I just think about a minimal subset of C++ that will not make any
performance penalty
(like C with classes)

is it a performance issue?
is it a deployment/compiler issue?
or any issue?

Please don't make any aggressive reply, I am a very nice guy :-)

Cheers,
Sylvain
___
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] sqlite programmed in C++

[Simon Davies]

2009/6/2 Sylvain Pointeau :
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)

Prob good idea to look through these:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>
> is it a performance issue?
> is it a deployment/compiler issue?
> or any issue?
>
> Please don't make any aggressive reply, I am a very nice guy :-)
>
> Cheers,
> Sylvain

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

Re: [sqlite] sqlite programmed in C++

[Christopher Taylor]

I have used sqlite for an embedded application using c++.  The sqlite
library is all in c and compiles on its own.  I then created several classes
that use the sqlite api.  The first is a DbHandler class.  This opens the
database and provides the public accessors to the rest of application.  I
then created a class for each table.  Each of these checks for existence and
creates the table if needed.  These also contain the query logic expected
for table.  It is a simple approach as I do not have a lot of interaction
between tables.  The public methods of the DbHandler pass in the database
pointer to methods of the table classes.  I have made sure to include
indexes with the larger tables and the benefit has been considerable.

Chris

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

[sqlite] sqlite programmed in C++

[Sylvain Pointeau]

Hello,
I would like to know if someone already though about to introduce C++ in
SQLite?
I just think about a minimal subset of C++ that will not make any
performance penalty
(like C with classes)

is it a performance issue?
is it a deployment/compiler issue?
or any issue?

Please don't make any aggressive reply, I am a very nice guy :-)

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

thanx a lot dude

Pavel Ivanov-2 wrote:
> 
> If you have unique index on stock_tab.prod_batch_code then you can
> re-write your trigger as this:
> 
> INSERT OR REPLACE INTO stock_tab
>   (stock_id, prod_batch_code, stock_qty, stock_date)
> SELECT new.purchase_id+2, new.prod_batch_code,
>   new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date
>FROM (SELECT new.prod_batch_code) a
>  LEFT JOIN stock_tab b ON b.prod_batch_code =
> a.prod_batch_code
> 
> 
> Pavel
> 
> On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew 
> wrote:
>>
>> guys i ll clarify the problem
>> this is the purchase table here purchase id is PK
>>
>> purchase_id  prod_batch_code  vendor_type_code  purchase_qty
>>  purchase_date
>> ---  ---    
>> ---
>> 1            1000             1             100            
>> 2009-05-26
>> 18:19:27
>> 2            1001             1             100            
>> 2009-05-26
>> 18:19:31
>> 3            1002             1             100            
>> 2009-05-26
>> 18:19:35
>> 4            1003             1             100            
>> 2009-05-26
>> 18:19:49
>>
>> this is the stock table here stock_id is PK and prod_batch_code is FK
>>
>> stock_id    prod_batch_code  stock_qty   stock_date
>> --  ---  --  ---
>> 20001       1001             105         2009-05-26 18:19:27
>> 20002       1002             100ps       2009-05-26 18:19:31
>> 20003       1003             100ps       2009-05-26 18:19:35
>> 20004       1003             100ps       2009-05-26 18:19:43
>> 20005       1002             100ps       2009-05-26 18:19:44
>> 20006       1001             100ps       2009-05-26 18:19:49
>> 20007       1000             85          2009-05-26 18:19:50
>> 20008       1000             85          2009-05-26 18:19:51
>>
>> i wrote a trigger
>> CREATE TRIGGER insert_stock_from_product
>>        AFTER INSERT ON purchase_tab
>>        BEGIN
>>        INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date)
>>        values (new.purchase_id+2, new.prod_batch_code,
>> new.purchase_qty,
>> new.purchase_date );
>>        END;
>>
>> instead of inserting the same products repeatedly in the stock table i
>> jus
>> want the quantity as well as the dates to be updated . and wen i insert a
>> new product_batch_code to the purchase table its shuld be inserted in the
>> stock table also...
>>
>> Edzard Pasma wrote:
>>>
>>> Sorry, this was written down without testing. I see now that
>>> prod_batch_code must be the primary key, instead of stock_id, for the
>>> REPLACE to work as expected. Then some other expression must be used to
>>> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this
>>> message crosses Kees Nuyt's idea which may be more comfortable if you
>>> like
>>> to keep the SQL simple..
>>> Edzard
>>>
>>> --- edz...@volcanomail.com wrote:
>>>
>>> From: "Edzard Pasma" 
>>> To: "General Discussion of SQLite Database" 
>>> Cc: 
>>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>>> Date: Tue, 2 Jun 2009 04:19:33 -0700
>>>
>>> Hello, you are cleverer than you think. Your initial idea to use INSERT
>>> OR
>>> REPLACE might look like:
>>>
>>> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date)
>>> SELECT
>>>     s.stock_id,
>>>     p.prod_batch_code,
>>>     IF_NULL (s.stock_qty, 0) + p.purchase_qty
>>>     DATETIME('NOW')
>>> FROM purchase_tab p
>>> LEFT OUTER JOIN stock_tab s
>>> ON s.prod_batch_code = p.prod_batch_code
>>> WHERE p.product_batch_code=1000
>>> /
>>> (assuming stock_id PRIMARY KEY)
>>>
>>> Best regards, Edzard
>>>
>>> --- engelsch...@codeswift.com wrote:
>>>
>>> From: Martin Engelschalk 
>>> To: General Discussion of SQLite Database 
>>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>>> Date: Tue, 02 Jun 2009 12:46:58 +0200
>>>
>>> Hi,
>>>
>>> as far as I know, you cannot do what you want to do in pure SQL.
>>> However, perhaps someone cleverer can contradict me.
>>>
>>> You could first execute the update statement, check if there was a row
>>> which was updated using sqlite3_changes() (see
>>> http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the
>>> insert if there was none.
>>>
>>> Martin
>>>
>>> robinsmathew wrote:
 hey thanx for the reply... u leave the things happening inside.. wat i
 jus
 wanna do is i wanna insert a new row to a table
 the table will be like this
 stock_id PK        product_id FK       quantity    stock_date
 1                 1000                    10            28-05-2009
 10001                 1001                      5            27-05-2009

 and wen i insert a new row with values  NULL,   1000,   

Re: [sqlite] how can we solve IF EXIST in SQLite

[Edzard Pasma]

If prod_batch_code is not a unique key (which is surprising as you may be 
updating more than one row), we can still write a pseudo INSERT OR REPLACE in 
the form of both an update and an insert statement. The update can go 
unchanged. The insert should not use values () but a query that only yields a 
row if the update failed:

INSERT INTO stock_table (..)
SELECT new.purchase_id+2, new.prod_batch_code, new.purchase_qty, 
new.purchase_date
WHERE NOT EXISTS (
SELECT NULL
FROM stock_table
WHERE prod_batch_code = new.prod_batch_code);

--- paiva...@gmail.com wrote:

From: Pavel Ivanov 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
Date: Tue, 2 Jun 2009 09:01:08 -0400

If you have unique index on stock_tab.prod_batch_code then you can
re-write your trigger as this:

INSERT OR REPLACE INTO stock_tab
  (stock_id, prod_batch_code, stock_qty, stock_date)
SELECT new.purchase_id+2, new.prod_batch_code,
  new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date
   FROM (SELECT new.prod_batch_code) a
 LEFT JOIN stock_tab b ON b.prod_batch_code = a.prod_batch_code


Pavel

On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew  wrote:
>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id  prod_batch_code  vendor_type_code  purchase_qty  purchase_date
> ---  ---    
> ---
> 1            1000             1             100             2009-05-26
> 18:19:27
> 2            1001             1             100             2009-05-26
> 18:19:31
> 3            1002             1             100             2009-05-26
> 18:19:35
> 4            1003             1             100             2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_id    prod_batch_code  stock_qty   stock_date
> --  ---  --  ---
> 20001       1001             105         2009-05-26 18:19:27
> 20002       1002             100ps       2009-05-26 18:19:31
> 20003       1003             100ps       2009-05-26 18:19:35
> 20004       1003             100ps       2009-05-26 18:19:43
> 20005       1002             100ps       2009-05-26 18:19:44
> 20006       1001             100ps       2009-05-26 18:19:49
> 20007       1000             85          2009-05-26 18:19:50
> 20008       1000             85          2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
>        AFTER INSERT ON purchase_tab
>        BEGIN
>        INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date)
>        values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty,
> new.purchase_date );
>        END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
> Edzard Pasma wrote:
>>
>> Sorry, this was written down without testing. I see now that
>> prod_batch_code must be the primary key, instead of stock_id, for the
>> REPLACE to work as expected. Then some other expression must be used to
>> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this
>> message crosses Kees Nuyt's idea which may be more comfortable if you like
>> to keep the SQL simple..
>> Edzard
>>
>> --- edz...@volcanomail.com wrote:
>>
>> From: "Edzard Pasma" 
>> To: "General Discussion of SQLite Database" 
>> Cc: 
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 2 Jun 2009 04:19:33 -0700
>>
>> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
>> REPLACE might look like:
>>
>> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date)
>> SELECT
>>     s.stock_id,
>>     p.prod_batch_code,
>>     IF_NULL (s.stock_qty, 0) + p.purchase_qty
>>     DATETIME('NOW')
>> FROM purchase_tab p
>> LEFT OUTER JOIN stock_tab s
>> ON s.prod_batch_code = p.prod_batch_code
>> WHERE p.product_batch_code=1000
>> /
>> (assuming stock_id PRIMARY KEY)
>>
>> Best regards, Edzard
>>
>> --- engelsch...@codeswift.com wrote:
>>
>> From: Martin Engelschalk 
>> To: General Discussion of SQLite Database 
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 02 Jun 2009 12:46:58 +0200
>>
>> Hi,
>>
>> as far as I know, you cannot do what you want to do in pure SQL.
>> However, perhaps someone cleverer can contradict me.
>>
>> You could first execute the update statement, check if there was a row
>> which was updated using sqlite3_changes() (see
>> 

Re: [sqlite] Syntax to set the conflict resolution of a transaction

[Karl Brandt]

2009/6/2 J. King 
>
> On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt 
> wrote:
>
> > I'm trying to set the conflict resolution of an entire transaction by
> > using the ON CONFLICT clause without success.
> >
> > [...]
> >
> > Is there a way to set the conflict resolution for an entire transaction?
>
> Such a thing is not possible.  You may specify a conflict resolution on a
> given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,
> NOT NULL and UNIQUE constraints), but not on a transaction.

Thanks for the info.

Let me explain the complete picture so someone can help me.

I develop a wrapper around sqlite that tracks the changed records and
than save the changes to the database by building and executing a SQL
query (a transaction).

Currently it executes the SQL and check the return value.
If the return value is different from SQLITE_OK it executes a
separated ROLLBACK command so another transaction can be started.

The problem is that after the ROLLBACK command, sqlite3_errmsg will
return "no error", giving the user no clues of what happened.

I tried INSERT OR ROLLBACK syntax but it will work only for
SQLITE_CONSTRAINT. I would need to handle also SQLITE_ERROR.

So there's a way to check if a transaction failed (for constraint or
another error) and than rollback without clearing the error message
returned by sqlite3_errmsg?

Thanks in advance.

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

[sqlite] Why does this sql error

[Tom Shaw]

I use the following sql

INSERT INTO malware (file, location, md5, size, sig, sig_name, cnt, 
clam_result, date_found, date_removed, ref) VALUES 
('Setup.exe-IRAD0n', '/Users/tshaw/malware/Setup.exe-IRAD0n', 
'1186b3a97de73f924dcfb12cba0bb1bf', 15360, '', '', 1, 
'/Users/tshaw/virus_archive/Setup.exe-IRAD0n: Worm.Koobface-20 FOUND 
', 1243947206, 1243947206, NULL);

and get the following error

Error!: SQLSTATE[HY000]: General error: 1 near ",": syntax error code:HY000

This occur whenever I try to do a second insert with a unique 
variable which is fine but why the error message above and not a 
message about duplicate uniques?

TIA,

Tom

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Pavel Ivanov]

If you have unique index on stock_tab.prod_batch_code then you can
re-write your trigger as this:

INSERT OR REPLACE INTO stock_tab
  (stock_id, prod_batch_code, stock_qty, stock_date)
SELECT new.purchase_id+2, new.prod_batch_code,
  new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date
   FROM (SELECT new.prod_batch_code) a
 LEFT JOIN stock_tab b ON b.prod_batch_code = a.prod_batch_code


Pavel

On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew  wrote:
>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id  prod_batch_code  vendor_type_code  purchase_qty  purchase_date
> ---  ---    
> ---
> 1            1000             1             100             2009-05-26
> 18:19:27
> 2            1001             1             100             2009-05-26
> 18:19:31
> 3            1002             1             100             2009-05-26
> 18:19:35
> 4            1003             1             100             2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_id    prod_batch_code  stock_qty   stock_date
> --  ---  --  ---
> 20001       1001             105         2009-05-26 18:19:27
> 20002       1002             100ps       2009-05-26 18:19:31
> 20003       1003             100ps       2009-05-26 18:19:35
> 20004       1003             100ps       2009-05-26 18:19:43
> 20005       1002             100ps       2009-05-26 18:19:44
> 20006       1001             100ps       2009-05-26 18:19:49
> 20007       1000             85          2009-05-26 18:19:50
> 20008       1000             85          2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
>        AFTER INSERT ON purchase_tab
>        BEGIN
>        INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date)
>        values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty,
> new.purchase_date );
>        END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
> Edzard Pasma wrote:
>>
>> Sorry, this was written down without testing. I see now that
>> prod_batch_code must be the primary key, instead of stock_id, for the
>> REPLACE to work as expected. Then some other expression must be used to
>> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this
>> message crosses Kees Nuyt's idea which may be more comfortable if you like
>> to keep the SQL simple..
>> Edzard
>>
>> --- edz...@volcanomail.com wrote:
>>
>> From: "Edzard Pasma" 
>> To: "General Discussion of SQLite Database" 
>> Cc: 
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 2 Jun 2009 04:19:33 -0700
>>
>> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
>> REPLACE might look like:
>>
>> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date)
>> SELECT
>>     s.stock_id,
>>     p.prod_batch_code,
>>     IF_NULL (s.stock_qty, 0) + p.purchase_qty
>>     DATETIME('NOW')
>> FROM purchase_tab p
>> LEFT OUTER JOIN stock_tab s
>> ON s.prod_batch_code = p.prod_batch_code
>> WHERE p.product_batch_code=1000
>> /
>> (assuming stock_id PRIMARY KEY)
>>
>> Best regards, Edzard
>>
>> --- engelsch...@codeswift.com wrote:
>>
>> From: Martin Engelschalk 
>> To: General Discussion of SQLite Database 
>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
>> Date: Tue, 02 Jun 2009 12:46:58 +0200
>>
>> Hi,
>>
>> as far as I know, you cannot do what you want to do in pure SQL.
>> However, perhaps someone cleverer can contradict me.
>>
>> You could first execute the update statement, check if there was a row
>> which was updated using sqlite3_changes() (see
>> http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the
>> insert if there was none.
>>
>> Martin
>>
>> robinsmathew wrote:
>>> hey thanx for the reply... u leave the things happening inside.. wat i
>>> jus
>>> wanna do is i wanna insert a new row to a table
>>> the table will be like this
>>> stock_id PK        product_id FK       quantity    stock_date
>>> 1                 1000                    10            28-05-2009
>>> 10001                 1001                      5            27-05-2009
>>>
>>> and wen i insert a new row with values  NULL,   1000,   15,    30-05-2009
>>> i dont want want it as a new recorde i jus want to update the first row
>>> coz
>>> its also having the same product id i jus want set the quantity = 10+15
>>> and
>>> the date new date that is 

Re: [sqlite] Syntax to set the conflict resolution of a transaction

[J. King]

On Tue, 02 Jun 2009 07:40:01 -0400, Karl Brandt   
wrote:

> I'm trying to set the conflict resolution of an entire transaction by
> using the ON CONFLICT clause without success.
>
> [...]
>
> Is there a way to set the conflict resolution for an entire transaction?

Such a thing is not possible.  You may specify a conflict resolution on a  
given statement (eg. 'INSERT OR ROLLBACK') or on a table (on PRIMARY KEY,  
NOT NULL and UNIQUE constraints), but not on a transaction.

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

Re: [sqlite] Syntax to set the conflict resolution of a transaction

[Kees Nuyt]

On Tue, 2 Jun 2009 08:40:01 -0300, Karl Brandt
 wrote:

>I'm trying to set the conflict resolution of an entire transaction by
>using the ON CONFLICT clause without success.
>
>I'm using the following syntax:
>
>BEGIN ON CONFLICT ROLLBACK;
>INSERT INTO TableX (Id) Values (1);
>INSERT INTO TableX (Id) Values (2);
>INSERT INTO TableX (Id) Values (3);
>COMMIT;
>
>But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error
>
>I found that syntax at the mail archives:
>
>http://thread.gmane.org/gmane.comp.db.sqlite.general/1563
>http://thread.gmane.org/gmane.comp.db.sqlite.general/5200
>http://thread.gmane.org/gmane.comp.db.sqlite.general/2276
>http://thread.gmane.org/gmane.comp.db.sqlite.general/1562
>
>I also tried the syntax found in the SQL wikipedia page:
>
>BEGIN;
>[..]
>IF ERRORS=0 COMMIT;
>IF ERRORS<>0 ROLLBACK;
>
>Also no luck.
>
>Is there a way to set the conflict resolution for an entire transaction?

It's not part of the syntax of BEGIN.
http://www.sqlite.org/lang_transaction.html

As far as I can tell you'll have to use it in every INSERT
statement, which has implications for your program flow.
http://www.sqlite.org/lang_insert.html
http://www.sqlite.org/lang_conflict.html

(You probably already read those pages, I included the links
for the convenience of other readers)

>Luiz
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

guys i ll clarify the problem
this is the purchase table here purchase id is PK

purchase_id  prod_batch_code  vendor_type_code  purchase_qty  purchase_date
---  ---     
---
11000 1 100 2009-05-26
18:19:27
21001 1 100 2009-05-26
18:19:31
31002 1 100 2009-05-26
18:19:35
41003 1 100 2009-05-26
18:19:49

this is the stock table here stock_id is PK and prod_batch_code is FK

stock_idprod_batch_code  stock_qty   stock_date
--  ---  --  ---
20001   1001 105 2009-05-26 18:19:27
20002   1002 100ps   2009-05-26 18:19:31
20003   1003 100ps   2009-05-26 18:19:35
20004   1003 100ps   2009-05-26 18:19:43
20005   1002 100ps   2009-05-26 18:19:44
20006   1001 100ps   2009-05-26 18:19:49
20007   1000 85  2009-05-26 18:19:50
20008   1000 85  2009-05-26 18:19:51

i wrote a trigger 
CREATE TRIGGER insert_stock_from_product
AFTER INSERT ON purchase_tab
BEGIN
INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date)
values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty,
new.purchase_date );
END;

instead of inserting the same products repeatedly in the stock table i jus
want the quantity as well as the dates to be updated . and wen i insert a
new product_batch_code to the purchase table its shuld be inserted in the
stock table also...

Edzard Pasma wrote:
> 
> Sorry, this was written down without testing. I see now that
> prod_batch_code must be the primary key, instead of stock_id, for the
> REPLACE to work as expected. Then some other expression must be used to
> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this
> message crosses Kees Nuyt's idea which may be more comfortable if you like
> to keep the SQL simple..
> Edzard
> 
> --- edz...@volcanomail.com wrote:
> 
> From: "Edzard Pasma" 
> To: "General Discussion of SQLite Database" 
> Cc: 
> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
> Date: Tue, 2 Jun 2009 04:19:33 -0700
> 
> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
> REPLACE might look like:
> 
> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
> SELECT 
> s.stock_id, 
> p.prod_batch_code, 
> IF_NULL (s.stock_qty, 0) + p.purchase_qty 
> DATETIME('NOW')
> FROM purchase_tab p
> LEFT OUTER JOIN stock_tab s
> ON s.prod_batch_code = p.prod_batch_code
> WHERE p.product_batch_code=1000
> /
> (assuming stock_id PRIMARY KEY)
> 
> Best regards, Edzard
> 
> --- engelsch...@codeswift.com wrote:
> 
> From: Martin Engelschalk 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
> Date: Tue, 02 Jun 2009 12:46:58 +0200
> 
> Hi,
> 
> as far as I know, you cannot do what you want to do in pure SQL. 
> However, perhaps someone cleverer can contradict me.
> 
> You could first execute the update statement, check if there was a row 
> which was updated using sqlite3_changes() (see 
> http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
> insert if there was none.
> 
> Martin
> 
> robinsmathew wrote:
>> hey thanx for the reply... u leave the things happening inside.. wat i
>> jus
>> wanna do is i wanna insert a new row to a table
>> the table will be like this
>> stock_id PKproduct_id FK   quantitystock_date
>> 1 10001028-05-2009
>> 10001 1001  527-05-2009
>>
>> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009
>>
>> i dont want want it as a new recorde i jus want to update the first row
>> coz
>> its also having the same product id i jus want set the quantity = 10+15
>> and
>> the date new date that is 30-05-2009
>> and suppose if i insert row with different product_id it should be
>> inserted
>> as it is..
>>
>> Martin Engelschalk wrote:
>>   
>>> Hi,
>>>
>>> what language is this? it certainly is not SQL or a "query".
>>> I suspect that you can not use "insert or replace" (see 
>>> http://www.sqlite.org/lang_insert.html), because you look first for a 
>>> record with prod_batch_code=1000, and if you do not find it you insert 
>>> one with prod_batch_code = 1003.
>>> S,. it seems to me that you have to implement the logic in your
>>> application.
>>>
>>> Martin
>>>
>>> robinsmathew wrote:
>>> 
 hi am new to SQLite 

[sqlite] Syntax to set the conflict resolution of a transaction

[Karl Brandt]

I'm trying to set the conflict resolution of an entire transaction by
using the ON CONFLICT clause without success.

I'm using the following syntax:

BEGIN ON CONFLICT ROLLBACK;
INSERT INTO TableX (Id) Values (1);
INSERT INTO TableX (Id) Values (2);
INSERT INTO TableX (Id) Values (3);
COMMIT;

But get the error (using 3.6.14.2): SQLITE_ERROR - near "on": syntax error

I found that syntax at the mail archives:

http://thread.gmane.org/gmane.comp.db.sqlite.general/1563
http://thread.gmane.org/gmane.comp.db.sqlite.general/5200
http://thread.gmane.org/gmane.comp.db.sqlite.general/2276
http://thread.gmane.org/gmane.comp.db.sqlite.general/1562

I also tried the syntax found in the SQL wikipedia page:

BEGIN;
[..]
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Also no luck.

Is there a way to set the conflict resolution for an entire transaction?

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

thanx for ur reply dude.. but its showing an error " no such function:
NULL_IF"

Edzard Pasma wrote:
> 
> Hello, you are cleverer than you think. Your initial idea to use INSERT OR
> REPLACE might look like:
> 
> INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
> SELECT 
> s.stock_id, 
> p.prod_batch_code, 
> IF_NULL (s.stock_qty, 0) + p.purchase_qty 
> DATETIME('NOW')
> FROM purchase_tab p
> LEFT OUTER JOIN stock_tab s
> ON s.prod_batch_code = p.prod_batch_code
> WHERE p.product_batch_code=1000
> /
> (assuming stock_id PRIMARY KEY)
> 
> Best regards, Edzard
> 
> --- engelsch...@codeswift.com wrote:
> 
> From: Martin Engelschalk 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
> Date: Tue, 02 Jun 2009 12:46:58 +0200
> 
> Hi,
> 
> as far as I know, you cannot do what you want to do in pure SQL. 
> However, perhaps someone cleverer can contradict me.
> 
> You could first execute the update statement, check if there was a row 
> which was updated using sqlite3_changes() (see 
> http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
> insert if there was none.
> 
> Martin
> 
> robinsmathew wrote:
>> hey thanx for the reply... u leave the things happening inside.. wat i
>> jus
>> wanna do is i wanna insert a new row to a table
>> the table will be like this
>> stock_id PKproduct_id FK   quantitystock_date
>> 1 10001028-05-2009
>> 10001 1001  527-05-2009
>>
>> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009
>>
>> i dont want want it as a new recorde i jus want to update the first row
>> coz
>> its also having the same product id i jus want set the quantity = 10+15
>> and
>> the date new date that is 30-05-2009
>> and suppose if i insert row with different product_id it should be
>> inserted
>> as it is..
>>
>> Martin Engelschalk wrote:
>>   
>>> Hi,
>>>
>>> what language is this? it certainly is not SQL or a "query".
>>> I suspect that you can not use "insert or replace" (see 
>>> http://www.sqlite.org/lang_insert.html), because you look first for a 
>>> record with prod_batch_code=1000, and if you do not find it you insert 
>>> one with prod_batch_code = 1003.
>>> S,. it seems to me that you have to implement the logic in your
>>> application.
>>>
>>> Martin
>>>
>>> robinsmathew wrote:
>>> 
 hi am new to SQLite can anybody please tell me how this query can be
 solved
 in SQLite?

 IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
 prod_batch_code=1000) 
 UPDATE stock_tab 
 SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
 WHERE
 oduct_batch_code=1000 ) 
 WHERE prod_batch_code=1000
 ELSE 
 INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
 stock_date) values (20009, 1003, 200,  
 DATETIME('NOW') );
   
   
>>> ___
>>> 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
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23830855.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Edzard Pasma]

Sorry, this was written down without testing. I see now that prod_batch_code 
must be the primary key, instead of stock_id, for the REPLACE to work as 
expected. Then some other expression must be used to fill stock_id, e.g. 
IF_NULL (s.stock_id, 29). I also see that this message crosses Kees Nuyt's 
idea which may be more comfortable if you like to keep the SQL simple..
Edzard

--- edz...@volcanomail.com wrote:

From: "Edzard Pasma" 
To: "General Discussion of SQLite Database" 
Cc: 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
Date: Tue, 2 Jun 2009 04:19:33 -0700

Hello, you are cleverer than you think. Your initial idea to use INSERT OR 
REPLACE might look like:

INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, 
stock_date)
SELECT 
s.stock_id, 
p.prod_batch_code, 
IF_NULL (s.stock_qty, 0) + p.purchase_qty 
DATETIME('NOW')
FROM purchase_tab p
LEFT OUTER JOIN stock_tab s
ON s.prod_batch_code = p.prod_batch_code
WHERE p.product_batch_code=1000
/
(assuming stock_id PRIMARY KEY)

Best regards, Edzard

--- engelsch...@codeswift.com wrote:

From: Martin Engelschalk 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
Date: Tue, 02 Jun 2009 12:46:58 +0200

Hi,

as far as I know, you cannot do what you want to do in pure SQL. 
However, perhaps someone cleverer can contradict me.

You could first execute the update statement, check if there was a row 
which was updated using sqlite3_changes() (see 
http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
insert if there was none.

Martin

robinsmathew wrote:
> hey thanx for the reply... u leave the things happening inside.. wat i jus
> wanna do is i wanna insert a new row to a table
> the table will be like this
> stock_id PKproduct_id FK   quantitystock_date
> 1 10001028-05-2009
> 10001 1001  527-05-2009
>
> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009 
>   
> i dont want want it as a new recorde i jus want to update the first row coz
> its also having the same product id i jus want set the quantity = 10+15 and
> the date new date that is 30-05-2009
> and suppose if i insert row with different product_id it should be inserted
> as it is..
>
> Martin Engelschalk wrote:
>   
>> Hi,
>>
>> what language is this? it certainly is not SQL or a "query".
>> I suspect that you can not use "insert or replace" (see 
>> http://www.sqlite.org/lang_insert.html), because you look first for a 
>> record with prod_batch_code=1000, and if you do not find it you insert 
>> one with prod_batch_code = 1003.
>> S,. it seems to me that you have to implement the logic in your
>> application.
>>
>> Martin
>>
>> robinsmathew wrote:
>> 
>>> hi am new to SQLite can anybody please tell me how this query can be
>>> solved
>>> in SQLite?
>>>
>>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>>> prod_batch_code=1000) 
>>> UPDATE stock_tab 
>>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>>> WHERE
>>> oduct_batch_code=1000 ) 
>>> WHERE prod_batch_code=1000
>>> ELSE 
>>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date) values (20009, 1003, 200,  
>>> DATETIME('NOW') );
>>>   
>>>   
>> ___
>> 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


___
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] how can we solve IF EXIST in SQLite

[Kees Nuyt]

On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew
 wrote:

>
>hey thanx for the reply... u leave the things happening inside.. wat i jus
>wanna do is i wanna insert a new row to a table
>the table will be like this
>stock_id PKproduct_id FK   quantitystock_date
>1 10001028-05-2009
>10001 1001  527-05-2009
>
>and wen i insert a new row with values  NULL,   1000,   15,30-05-2009  
> 
>i dont want want it as a new recorde i jus want to update the first row coz
>its also having the same product id i jus want set the quantity = 10+15 and
>the date new date that is 30-05-2009
>and suppose if i insert row with different product_id it should be inserted
>as it is..

Pseudocode:
BEGIN;
UPDATE stock_tab SET . WHERE stock_id = 1;
if sqlite_error()
INSERT INTO stock_tab SET (...) VALUES (...);
endif
COMMIT;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how can we solve IF EXIST in SQLite

[Edzard Pasma]

Hello, you are cleverer than you think. Your initial idea to use INSERT OR 
REPLACE might look like:

INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, 
stock_date)
SELECT 
s.stock_id, 
p.prod_batch_code, 
IF_NULL (s.stock_qty, 0) + p.purchase_qty 
DATETIME('NOW')
FROM purchase_tab p
LEFT OUTER JOIN stock_tab s
ON s.prod_batch_code = p.prod_batch_code
WHERE p.product_batch_code=1000
/
(assuming stock_id PRIMARY KEY)

Best regards, Edzard

--- engelsch...@codeswift.com wrote:

From: Martin Engelschalk 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
Date: Tue, 02 Jun 2009 12:46:58 +0200

Hi,

as far as I know, you cannot do what you want to do in pure SQL. 
However, perhaps someone cleverer can contradict me.

You could first execute the update statement, check if there was a row 
which was updated using sqlite3_changes() (see 
http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
insert if there was none.

Martin

robinsmathew wrote:
> hey thanx for the reply... u leave the things happening inside.. wat i jus
> wanna do is i wanna insert a new row to a table
> the table will be like this
> stock_id PKproduct_id FK   quantitystock_date
> 1 10001028-05-2009
> 10001 1001  527-05-2009
>
> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009 
>   
> i dont want want it as a new recorde i jus want to update the first row coz
> its also having the same product id i jus want set the quantity = 10+15 and
> the date new date that is 30-05-2009
> and suppose if i insert row with different product_id it should be inserted
> as it is..
>
> Martin Engelschalk wrote:
>   
>> Hi,
>>
>> what language is this? it certainly is not SQL or a "query".
>> I suspect that you can not use "insert or replace" (see 
>> http://www.sqlite.org/lang_insert.html), because you look first for a 
>> record with prod_batch_code=1000, and if you do not find it you insert 
>> one with prod_batch_code = 1003.
>> S,. it seems to me that you have to implement the logic in your
>> application.
>>
>> Martin
>>
>> robinsmathew wrote:
>> 
>>> hi am new to SQLite can anybody please tell me how this query can be
>>> solved
>>> in SQLite?
>>>
>>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>>> prod_batch_code=1000) 
>>> UPDATE stock_tab 
>>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>>> WHERE
>>> oduct_batch_code=1000 ) 
>>> WHERE prod_batch_code=1000
>>> ELSE 
>>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date) values (20009, 1003, 200,  
>>> DATETIME('NOW') );
>>>   
>>>   
>> ___
>> 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


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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Martin Engelschalk]

Hi,

as far as I know, you cannot do what you want to do in pure SQL. 
However, perhaps someone cleverer can contradict me.

You could first execute the update statement, check if there was a row 
which was updated using sqlite3_changes() (see 
http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
insert if there was none.

Martin

robinsmathew wrote:
> hey thanx for the reply... u leave the things happening inside.. wat i jus
> wanna do is i wanna insert a new row to a table
> the table will be like this
> stock_id PKproduct_id FK   quantitystock_date
> 1 10001028-05-2009
> 10001 1001  527-05-2009
>
> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009 
>   
> i dont want want it as a new recorde i jus want to update the first row coz
> its also having the same product id i jus want set the quantity = 10+15 and
> the date new date that is 30-05-2009
> and suppose if i insert row with different product_id it should be inserted
> as it is..
>
> Martin Engelschalk wrote:
>   
>> Hi,
>>
>> what language is this? it certainly is not SQL or a "query".
>> I suspect that you can not use "insert or replace" (see 
>> http://www.sqlite.org/lang_insert.html), because you look first for a 
>> record with prod_batch_code=1000, and if you do not find it you insert 
>> one with prod_batch_code = 1003.
>> S,. it seems to me that you have to implement the logic in your
>> application.
>>
>> Martin
>>
>> robinsmathew wrote:
>> 
>>> hi am new to SQLite can anybody please tell me how this query can be
>>> solved
>>> in SQLite?
>>>
>>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>>> prod_batch_code=1000) 
>>> UPDATE stock_tab 
>>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>>> WHERE
>>> oduct_batch_code=1000 ) 
>>> WHERE prod_batch_code=1000
>>> ELSE 
>>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date) values (20009, 1003, 200,  
>>> DATETIME('NOW') );
>>>   
>>>   
>> ___
>> 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] how can we solve IF EXIST in SQLite

[robinsmathew]

hey thanx for the reply... u leave the things happening inside.. wat i jus
wanna do is i wanna insert a new row to a table
the table will be like this
stock_id PKproduct_id FK   quantitystock_date
1 10001028-05-2009
10001 1001  527-05-2009

and wen i insert a new row with values  NULL,   1000,   15,30-05-2009   

i dont want want it as a new recorde i jus want to update the first row coz
its also having the same product id i jus want set the quantity = 10+15 and
the date new date that is 30-05-2009
and suppose if i insert row with different product_id it should be inserted
as it is..

Martin Engelschalk wrote:
> 
> Hi,
> 
> what language is this? it certainly is not SQL or a "query".
> I suspect that you can not use "insert or replace" (see 
> http://www.sqlite.org/lang_insert.html), because you look first for a 
> record with prod_batch_code=1000, and if you do not find it you insert 
> one with prod_batch_code = 1003.
> S,. it seems to me that you have to implement the logic in your
> application.
> 
> Martin
> 
> robinsmathew wrote:
>> hi am new to SQLite can anybody please tell me how this query can be
>> solved
>> in SQLite?
>>
>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>> prod_batch_code=1000) 
>> UPDATE stock_tab 
>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>> WHERE
>> oduct_batch_code=1000 ) 
>> WHERE prod_batch_code=1000
>> ELSE 
>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>> stock_date) values (20009, 1003, 200,  
>> DATETIME('NOW') );
>>   
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23830090.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Martin Engelschalk]

Hi,

what language is this? it certainly is not SQL or a "query".
I suspect that you can not use "insert or replace" (see 
http://www.sqlite.org/lang_insert.html), because you look first for a 
record with prod_batch_code=1000, and if you do not find it you insert 
one with prod_batch_code = 1003.
S,. it seems to me that you have to implement the logic in your application.

Martin

robinsmathew wrote:
> hi am new to SQLite can anybody please tell me how this query can be solved
> in SQLite?
>
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000) 
> UPDATE stock_tab 
> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
> oduct_batch_code=1000 ) 
> WHERE prod_batch_code=1000
> ELSE 
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,  
> DATETIME('NOW') );
>   

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

[sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

hi am new to SQLite can anybody please tell me how this query can be solved
in SQLite?

IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000) 
UPDATE stock_tab 
SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
oduct_batch_code=1000 ) 
WHERE prod_batch_code=1000
ELSE 
INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
stock_date) values (20009, 1003, 200,  
DATETIME('NOW') );
-- 
View this message in context: 
http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23828274.html
Sent from the SQLite mailing list archive at Nabble.com.

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