Re: [sqlite] The SQL Guide to SQLite

[Rick Ratchford]

Greetings Neville-

(thanks for changing the subject. :)

I'm only a few pages into the book. Before I went deeper, I just wanted to
get a handle on the [...] notations.

The first few pages are quite informative. Let me get deeper into the book
and I'll be glad to share my thoughts on it here.

Best regards,
Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Neville Franks
#>Sent: Saturday, July 18, 2009 9:11 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>Rick,
#>Changing the conversation a little I would be interested to 
#>know your opinion of this book, as I'm sure would others. 
#>Many were disappointed with "The Definitive Guide to SQLite".
#>
#>
#>Sunday, July 19, 2009, 11:56:05 AM, you wrote:
#>
#>RR> Okay. We're talking two different things here.
#>
#>RR> One states "academic papers" and you state "technical documents".
#>
#>RR> This is a "book", not an "academic paper or technical document".
#>
#>RR> I'm all for Names and Dates. I'm quite familiar with 
#>(Williams and 
#>RR> Jones
#>RR> 1981) and other such references. They appear in most of 
#>the books I possess.
#>
#>RR> However, bracketed references such as [SMI01] do not. 
#>First time in 
#>RR> my 50 years I've come across this.
#>
#>RR> Are we assuming that everyone who buys this book attended 
#>University?
#>
#>RR> Another thing I'm familiar with are TAGS in documents. 
#>These looked 
#>RR> like TAGS to me. I immediately assumed the TAGS weren't replaced 
#>RR> with the actual material.
#>
#>RR> Anyway, I think enough has been said on this. One should never 
#>RR> ASSUME that a convention is understood by ALL readers. 
#>Apparently, it is not.
#>
#>RR> Best regards,
#>
#>RR> Rich
#>
#>
#>RR>  
#>
#>#>>-Original Message-
#>#>>From: sqlite-users-boun...@sqlite.org 
#>#>>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>#>>Sent: Saturday, July 18, 2009 8:00 PM
#>#>>To: General Discussion of SQLite Database
#>#>>Subject: Re: [sqlite] The SQL Guide to SQLite #>> #>>On 
#>Sat, 18 Jul 2009, Rick Ratchford wrote:
#>#>>
#>#>>> All I know is that this is a book. I have a vast library 
#>of #>>technical #>>> books and this is the ONLY one that uses 
#>this convention. 
#>#>>Even my copy
#>#>>> of "A New Kind of Science" by Wolfram doesn't use this 
#>#>>convention. :-b #>>
#>#>>   There are many conventions for citations in books, 
#>#>>reports, articles, and other documents that cite original 
#>#>>sources. When I was in academia, the ecological literature 
#>#>>(books, papers, etc.) used a (name date) format; e.g., 
#>(Smith #>>1962), or (Williams and Jones 1981), or (Foobar et 
#>al. 1954). 
#>#>>The bibliography or reference section (and there is a 
#>#>>difference between those
#>#>>two) was arranged in alphabetic order. Many other 
#>technical #>>books (including
#>#>>mine) use a numeric citation, e.g., [20], and the 
#>#>>bibliography is numeric rather than alphabetic. Still 
#>other #>>technical documents use the author abreviation plus 
#>two-digit #>>year system which is what you apparently 
#>encountered; e.g., #>>[ORA92] or [SMI01]. They are all common.
#>#>>
#>#>>   Personally, I like the author/year system because it's 
#>#>>explicit and easy to comprehend without requiring looking 
#>at #>>the references section.
#>#>>Regardless, it's up to the publisher, country, or the 
#>#>>practice of a particular discipline which one is used.
#>#>>
#>#>>   It's unfortunate that you had such difficulty figuring out 
#>#>>the citation system.
#>#>>
#>#>>Rich
#>#>>
#>#>>-- 
#>#>>Richard B. Shepard, Ph.D.   |  Integrity  
#>#>>  Credibility
#>#>>Applied Ecosystem Services, Inc.|Innovation
#>#>> Voice: 503-667-4517  
#>#>>Fax: 503-667-8863
#>#>>___
#>#>>sqlite-users mailing list
#>#>>sqlite-users@sqlite.org
#>#>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>#>>
#>#>>
#>
#>
#>RR> ___
#>RR> sqlite-users mailing list
#>RR> sqlite-users@sqlite.org
#>RR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>
#>
#>--
#>Best regards,
#>  Neville Franks, http://www.surfulater.com http://blog.surfulater.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

Re: [sqlite] The SQL Guide to SQLite

[Neville Franks]

Rick,
Changing the conversation a little I would be interested to know your
opinion of this book, as I'm sure would others. Many were disappointed
with "The Definitive Guide to SQLite".


Sunday, July 19, 2009, 11:56:05 AM, you wrote:

RR> Okay. We're talking two different things here.

RR> One states "academic papers" and you state "technical documents".

RR> This is a "book", not an "academic paper or technical document".

RR> I'm all for Names and Dates. I'm quite familiar with (Williams and Jones
RR> 1981) and other such references. They appear in most of the books I possess.

RR> However, bracketed references such as [SMI01] do not. First time in my 50
RR> years I've come across this.

RR> Are we assuming that everyone who buys this book attended University?

RR> Another thing I'm familiar with are TAGS in documents. These looked like
RR> TAGS to me. I immediately assumed the TAGS weren't replaced with the actual
RR> material.

RR> Anyway, I think enough has been said on this. One should never ASSUME that a
RR> convention is understood by ALL readers. Apparently, it is not.

RR> Best regards,

RR> Rich


RR>  

#>>-Original Message-
#>>From: sqlite-users-boun...@sqlite.org 
#>>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>>Sent: Saturday, July 18, 2009 8:00 PM
#>>To: General Discussion of SQLite Database
#>>Subject: Re: [sqlite] The SQL Guide to SQLite
#>>
#>>On Sat, 18 Jul 2009, Rick Ratchford wrote:
#>>
#>>> All I know is that this is a book. I have a vast library of 
#>>technical 
#>>> books and this is the ONLY one that uses this convention. 
#>>Even my copy 
#>>> of "A New Kind of Science" by Wolfram doesn't use this 
#>>convention. :-b
#>>
#>>   There are many conventions for citations in books, 
#>>reports, articles, and other documents that cite original 
#>>sources. When I was in academia, the ecological literature 
#>>(books, papers, etc.) used a (name date) format; e.g., (Smith 
#>>1962), or (Williams and Jones 1981), or (Foobar et al. 1954). 
#>>The bibliography or reference section (and there is a 
#>>difference between those
#>>two) was arranged in alphabetic order. Many other technical 
#>>books (including
#>>mine) use a numeric citation, e.g., [20], and the 
#>>bibliography is numeric rather than alphabetic. Still other 
#>>technical documents use the author abreviation plus two-digit 
#>>year system which is what you apparently encountered; e.g., 
#>>[ORA92] or [SMI01]. They are all common.
#>>
#>>   Personally, I like the author/year system because it's 
#>>explicit and easy to comprehend without requiring looking at 
#>>the references section.
#>>Regardless, it's up to the publisher, country, or the 
#>>practice of a particular discipline which one is used.
#>>
#>>   It's unfortunate that you had such difficulty figuring out 
#>>the citation system.
#>>
#>>Rich
#>>
#>>-- 
#>>Richard B. Shepard, Ph.D.   |  Integrity  
#>>  Credibility
#>>Applied Ecosystem Services, Inc.|Innovation
#>> Voice: 503-667-4517  
#>>Fax: 503-667-8863
#>>___
#>>sqlite-users mailing list
#>>sqlite-users@sqlite.org
#>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>>
#>>


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



-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] The SQL Guide to SQLite

[Rick Ratchford]

Okay. We're talking two different things here.

One states "academic papers" and you state "technical documents".

This is a "book", not an "academic paper or technical document".

I'm all for Names and Dates. I'm quite familiar with (Williams and Jones
1981) and other such references. They appear in most of the books I possess.

However, bracketed references such as [SMI01] do not. First time in my 50
years I've come across this.

Are we assuming that everyone who buys this book attended University?

Another thing I'm familiar with are TAGS in documents. These looked like
TAGS to me. I immediately assumed the TAGS weren't replaced with the actual
material.

Anyway, I think enough has been said on this. One should never ASSUME that a
convention is understood by ALL readers. Apparently, it is not.

Best regards,

Rich


 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Saturday, July 18, 2009 8:00 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>On Sat, 18 Jul 2009, Rick Ratchford wrote:
#>
#>> All I know is that this is a book. I have a vast library of 
#>technical 
#>> books and this is the ONLY one that uses this convention. 
#>Even my copy 
#>> of "A New Kind of Science" by Wolfram doesn't use this 
#>convention. :-b
#>
#>   There are many conventions for citations in books, 
#>reports, articles, and other documents that cite original 
#>sources. When I was in academia, the ecological literature 
#>(books, papers, etc.) used a (name date) format; e.g., (Smith 
#>1962), or (Williams and Jones 1981), or (Foobar et al. 1954). 
#>The bibliography or reference section (and there is a 
#>difference between those
#>two) was arranged in alphabetic order. Many other technical 
#>books (including
#>mine) use a numeric citation, e.g., [20], and the 
#>bibliography is numeric rather than alphabetic. Still other 
#>technical documents use the author abreviation plus two-digit 
#>year system which is what you apparently encountered; e.g., 
#>[ORA92] or [SMI01]. They are all common.
#>
#>   Personally, I like the author/year system because it's 
#>explicit and easy to comprehend without requiring looking at 
#>the references section.
#>Regardless, it's up to the publisher, country, or the 
#>practice of a particular discipline which one is used.
#>
#>   It's unfortunate that you had such difficulty figuring out 
#>the citation system.
#>
#>Rich
#>
#>-- 
#>Richard B. Shepard, Ph.D.   |  Integrity  
#>  Credibility
#>Applied Ecosystem Services, Inc.|Innovation
#> Voice: 503-667-4517  
#>Fax: 503-667-8863
#>___
#>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] The SQL Guide to SQLite

[Rich Shepard]

On Sat, 18 Jul 2009, Rick Ratchford wrote:

> All I know is that this is a book. I have a vast library of technical
> books and this is the ONLY one that uses this convention. Even my copy of
> "A New Kind of Science" by Wolfram doesn't use this convention. :-b

   There are many conventions for citations in books, reports, articles, and
other documents that cite original sources. When I was in academia, the
ecological literature (books, papers, etc.) used a (name date) format; e.g.,
(Smith 1962), or (Williams and Jones 1981), or (Foobar et al. 1954). The
bibliography or reference section (and there is a difference between those
two) was arranged in alphabetic order. Many other technical books (including
mine) use a numeric citation, e.g., [20], and the bibliography is numeric
rather than alphabetic. Still other technical documents use the author
abreviation plus two-digit year system which is what you apparently
encountered; e.g., [ORA92] or [SMI01]. They are all common.

   Personally, I like the author/year system because it's explicit and easy
to comprehend without requiring looking at the references section.
Regardless, it's up to the publisher, country, or the practice of a
particular discipline which one is used.

   It's unfortunate that you had such difficulty figuring out the citation
system.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The SQL Guide to SQLite

[Rick Ratchford]

Perhaps its true that its common in academic papers. I wouldn't know as I've
never read one.

All I know is that this is a book. I have a vast library of technical books
and this is the ONLY one that uses this convention. Even my copy of "A New
Kind of Science" by Wolfram doesn't use this convention. :-b

So seeing it for the first time was very confusing. I flipped through the
last pages and didn't note the one page biblio. That's when I ran it through
'bing' before asking here.

Even doing a 'bing' on "square brackets" suggested they were placeholders
for other information during production. I think some kind of convention
explanation would have been appropriate at the beginning of this book like
most programming books provide rather than assuming everyone is familiar
with conventions used in "academic papers". 

Thanks. 
:-)
Rick

 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Thomas Briggs
#>Sent: Saturday, July 18, 2009 6:53 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>   That's pretty common in academic papers, actually.
#>
#>   -T
#>
#>
#>On 7/18/09, Rick Ratchford  wrote:
#>> Yes. You are correct. That is what they are.
#>>
#>> Thanks for pointing this out. It probably should have been 
#>made clear 
#>> at the beginning of the book since this is not common in 
#>the majority 
#>> of books I own purchased here in the US.
#>>
#>> Thanks again.
#>>
#>> Rick
#>>
#>>
#>> #>-Original Message-
#>> #>From: sqlite-users-boun...@sqlite.org 
#>> #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of 
#>Igor Tandetnik
#>> #>Sent: Saturday, July 18, 2009 10:42 AM
#>> #>To: sqlite-users@sqlite.org
#>> #>Subject: Re: [sqlite] The SQL Guide to SQLite #> #>Rick Ratchford 
#>> wrote:
#>> #>> I just received my copy of the new book "The SQL Guide to 
#>> #>SQLite" by #>> Rick F. van der Lans.
#>> #>>
#>> #>> There are many references within the book that are 
#>#>contained in 
#>> square #>> brackets and some sort of keyword or code.
#>> #>>
#>> #>> Example: "...written about SQLite; see for example [NEWM05] and 
#>> #>> [OWEN06]."
#>> #>
#>> #>These are most likely citations. See if there's a 
#>#>bibliography (a 
#>> list of other books, articles and such) at #>the end of the 
#>book, each 
#>> reference identified by one of these codes.
#>> #>
#>> #>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
#>>
#>
#>--
#>Sent from my mobile device
#>___
#>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] very large SQLite tables

[Christian Smith]

On Wed, Jun 24, 2009 at 02:21:09PM -0500, Matthew O'Keefe wrote:
> 
> 
> We are using SQLite for indexing a huge number (i.e., 100 million to 1
> billion) of key pairs
> that are represented by an 88-byte key. We are using a single table with a
> very large number of rows (one for each data chunk), and two columns.
> 
> The table has two columns.  One is of type ³text² and the other is type
> ³integer².
> > 
> > The table is created with:
> > 
> > CREATE TABLE chunks
> > (
> >   name text primary key,
> >   pid integer not null
> );
> 
> As expected, as the
> table grows, the underlying B-tree implementation for SQLite means that the
> number of
> disks accesses to (a) find, and (b) add a chunk, grows larger and larger.
> We¹ve tested up
> to 20 million chunks represented in the table: as expected performance
> exponentially 
> decreases as the number of table entries grows.
> 
> We wanted to post to the mailing list to see if there are any obvious,
> first-order things
> we can try to improve performance for such a large table.

Bit late to the game...

Try increasing your page size. The larger page size will result in greater
fan out of the btree, resulting in a shallower tree and less IO requests.

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

Re: [sqlite] The SQL Guide to SQLite

[Thomas Briggs]

   That's pretty common in academic papers, actually.

   -T


On 7/18/09, Rick Ratchford  wrote:
> Yes. You are correct. That is what they are.
>
> Thanks for pointing this out. It probably should have been made clear at
> the
> beginning of the book since this is not common in the majority of books I
> own purchased here in the US.
>
> Thanks again.
>
> Rick
>
>
> #>-Original Message-
> #>From: sqlite-users-boun...@sqlite.org
> #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> #>Sent: Saturday, July 18, 2009 10:42 AM
> #>To: sqlite-users@sqlite.org
> #>Subject: Re: [sqlite] The SQL Guide to SQLite
> #>
> #>Rick Ratchford wrote:
> #>> I just received my copy of the new book "The SQL Guide to
> #>SQLite" by
> #>> Rick F. van der Lans.
> #>>
> #>> There are many references within the book that are
> #>contained in square
> #>> brackets and some sort of keyword or code.
> #>>
> #>> Example: "...written about SQLite; see for example [NEWM05] and
> #>> [OWEN06]."
> #>
> #>These are most likely citations. See if there's a
> #>bibliography (a list of other books, articles and such) at
> #>the end of the book, each reference identified by one of these codes.
> #>
> #>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
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Brian Dantes]

Thanks for the replies.

- All insertions are within a transaction
- Database was originally created on Linux (with a 1K page size)
  and copied to Windows
- Changing the page size to 4K (and vacuuming) lowered the index
  creation time on Windows to 50 seconds and on Linux to 5.5 minutes.
  However, there is still a huge disparity.
- Both Windows and Linux are doing nothing else and have ample memory.
  The disk on Linux is a 1 rpm fast SCSI disk on an HP blade.

Here is the Windows output of the analyzer for the table and its index:

*** Table XXX w/o any indices 

Percentage of total database..   8.1%
Number of entries. 5119477
Bytes of storage consumed. 87928832
Bytes of payload.. 5361732861.0%
Average payload per entry. 10.47
Average unused bytes per entry 0.04
Average fanout 363.00
Fragmentation.   0.81%
Maximum payload per entry. 11
Entries that use overflow. 00.0%
Index pages used.. 59
Primary pages used 21408
Overflow pages used... 0
Total pages used.. 21467
Unused bytes on index pages... 35210   14.6%
Unused bytes on primary pages. 175898   0.20%
Unused bytes on overflow pages 0
Unused bytes on all pages. 211108   0.24%

*** Indices of table XXX *

Percentage of total database..   8.6%
Number of entries. 5119477
Bytes of storage consumed. 93179904
Bytes of payload.. 6894286474.0%
Average payload per entry. 13.47
Average unused bytes per entry 1.68
Fragmentation.  99.14%
Maximum payload per entry. 14
Entries that use overflow. 00.0%
Primary pages used 22749
Overflow pages used... 0
Total pages used.. 22749
Unused bytes on primary pages. 8605625  9.2%
Unused bytes on overflow pages 0
Unused bytes on all pages. 8605625  9.2%


And here is the same output under Linux:

*** Table XXX w/o any indices 

Percentage of total database..   8.1%
Number of entries. 5119477   
Bytes of storage consumed. 87928832  
Bytes of payload.. 5361732861.0% 
Average payload per entry. 10.47 
Average unused bytes per entry 0.04  
Average fanout 363.00
Fragmentation.   0.81%   
Maximum payload per entry. 11
Entries that use overflow. 00.0% 
Index pages used.. 59
Primary pages used 21408 
Overflow pages used... 0 
Total pages used.. 21467 
Unused bytes on index pages... 35210   14.6% 
Unused bytes on primary pages. 175898   0.20% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 211108   0.24% 

*** Indices of table XXX *

Percentage of total database..   8.6%
Number of entries. 5119477   
Bytes of storage consumed. 93179904  
Bytes of payload.. 6894286474.0% 
Average payload per entry. 13.47 
Average unused bytes per entry 1.68  
Fragmentation.  99.08%   
Maximum payload per entry. 14
Entries that use overflow. 00.0% 
Primary pages used 22749 
Overflow pages used... 0 
Total pages used.. 22749 
Unused bytes on primary pages. 8605625  9.2% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 8605625  9.2% 



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

Re: [sqlite] SQLite3 replication

[Simon Slavin]

On 18 Jul 2009, at 8:27pm, Ken wrote:

> The "journal" file for sqlite is not a redo journal but rather an  
> undo journal. So it is not really possible to use the journal to  
> replicate.

I agree.

> However, if one were to hack the code and open a redo file along  
> with the journal file. Then write the source blocks out as well.  
> This could then be copied and written against a target database on a  
> remote server.

I know a bit about the problems of synching two copies of the same  
relational database.  It is possible to do the job with a journal, but  
you need a journal at each site, they need more data in than SQLite  
journals, and you need some sort of intelligence about how to  
reconcile them.  There's no "once size fits all" solution.

To answer the original question: if only one site can make changes,  
the problem can be solved any number of ways, including rsync, ditto,  
making your own journal using TRIGGER, and many others.  If more than  
one site can make changes then you're in for a world of hurt until  
you've read a few books or consulted someone with experience.

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

[sqlite] Resolve: problem getting triggers to work

[Jim Showalter]

Android team screwed up.

I filed:

http://code.google.com/p/android/issues/detail?id=3302
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple Writers and Database is Locked Problems

[Ken]

I like to start each of my transactions with a "Begin Immediate" that way the 
database file is locked at that point. And its relatively simple to test for 
the DB locked at that stage and handle waiting or returning an error.

HTH

--- On Fri, 7/17/09, Cole  wrote:

> From: Cole 
> Subject: [sqlite] Multiple Writers and Database is Locked Problems
> To: sqlite-users@sqlite.org
> Date: Friday, July 17, 2009, 6:38 AM
> Hi.
> 
> Im hoping someone might be able to help me with the
> problems im having, or
> suggest a better method of doing what im trying to achieve.
> Currently im
> using sqlite3 3.6.10. I don't mind updating or downgrading
> it if needed.
> 
> I have a program that I run multiple instances of. When
> they start, they
> parse the config file, and open a connection to each
> database that is
> listed. Each database only has a single table in it. They
> then receive
> requests, do a select on the database, parse the returned
> data, modify the
> data, then update the data to the database. However, I am
> running into the
> "database is locked" error when trying to update the
> database. I fully
> understand that 2 or more instances might be trying to
> update the same
> table, but is this the only reason this might happen now?
> Or are there also
> other scenarios where this might happen?
> 
> Ive searched the mailing list and I see that you mention
> using random seed
> and time to handle the SQLITE_BUSY return value, and then
> trying to perform
> the update again. I was perhaps wondering if there are any
> other suggestions
> for dealing with this scenario where there might be
> multiple writers to the
> same database at the same time?
> 
> Regards
> /Cole
> 
> ___
> 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] SQLite3 replication

[Ken]

Kelly,

The "journal" file for sqlite is not a redo journal but rather an undo journal. 
So it is not really possible to use the journal to replicate.

However, if one were to hack the code and open a redo file along with the 
journal file. Then write the source blocks out as well. This could then be 
copied and written against a target database on a remote server.

Ultimately this would be what rsync provides without the rescans... You could 
also look into some form of filesystem replication as well. I think that would 
be more reliable.

Just my .02
hth, ken

--- On Sat, 7/18/09, Kelly Jones  wrote:

> From: Kelly Jones 
> Subject: [sqlite] SQLite3 replication
> To: sqlite-users@sqlite.org
> Date: Saturday, July 18, 2009, 12:18 PM
> Is there any way to real-time
> replicate SQLite3 dbs across servers?
> 
> I realize I could just rsync constantly, but this seems
> inefficient.
> 
> I know SQLite3 uses a journal when making changes: could I
> use this
> journal for replication, similar to how MySQL uses
> bin-logging for
> replication?
> 
> -- 
> We're just a Bunch Of Regular Guys, a collective group
> that's trying
> to understand and assimilate technology. We feel that
> resistance to
> new ideas and technology is unwise and ultimately futile.
> ___
> 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] SQLite3 immune from injection attacks if file isreadonly?

[Igor Tandetnik]

Igor Tandetnik wrote:
> Kelly Jones wrote:
>> On a website, I want to take a user's query "as is", save it to a
>> userquery.txt, and then do:
>>
>> sqlite3 /path/to/mydb < userquery.txt
>>
>> where /path/to/mydb is a *read-only* file.
>>
>> Is there *any* risk of an injection attack here?
>>
>> Specifically, does sqlite3 have any shell escapes or any way to
>> change the Unix permissions of the file it's accessing?
>
> The user may issue an ATTACH statement to open a connection to a
> different file, and then modify that one.

Oh, and there's .load command instructing sqlite3 to load a given shared 
library and call a given function in it - basically, to run arbitrary 
code. And .backup and .output commands can overwrite an arbitrary file.

Igor Tandetnik 



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

[sqlite] Updated info on getting trigger to work

[Jim Showalter]

I dumped the SQL being executed to a file:

drop table if exists Words;
create table Words (_id integer primary key autoincrement, text text 
not null unique);
create trigger ut_Words_cannotChangeWordTextOnUpdate before update on 
Words for each row begin select raise(rollback, 'update on table Words 
violates constraint ut_Words_cannotChangeWordTextOnUpdate') where 
OLD.text <> NEW.text;end;
insert into Words (text) values ("word1");
select * from Words;
update Words set text='different_word' where _id=1;

and then ran sqlite3, sending it the contents of the file:

C:\Installs\SQLite>sqlite3 db2 < schema2.txt

The trigger works as expected.

1|word1
SQL error near line 6: update on table Words violates constraint 
ut_Words_cannotChangeWordTextOnUpdate

Unfortunately, it still doesn't work when called via the Android Java 
API over SQLite. Digging into that now. 

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

Re: [sqlite] SQLite3 immune from injection attacks if file is readonly?

[Igor Tandetnik]

Kelly Jones wrote:
> On a website, I want to take a user's query "as is", save it to a
> userquery.txt, and then do:
>
> sqlite3 /path/to/mydb < userquery.txt
>
> where /path/to/mydb is a *read-only* file.
>
> Is there *any* risk of an injection attack here?
>
> Specifically, does sqlite3 have any shell escapes or any way to change
> the Unix permissions of the file it's accessing?

The user may issue an ATTACH statement to open a connection to a 
different file, and then modify that one.

Igor Tandetnik 



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

[sqlite] SQLite3 replication

[Kelly Jones]

Is there any way to real-time replicate SQLite3 dbs across servers?

I realize I could just rsync constantly, but this seems inefficient.

I know SQLite3 uses a journal when making changes: could I use this
journal for replication, similar to how MySQL uses bin-logging for
replication?

-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 immune from injection attacks if file is readonly?

[Kelly Jones]

On a website, I want to take a user's query "as is", save it to a
userquery.txt, and then do:

sqlite3 /path/to/mydb < userquery.txt

where /path/to/mydb is a *read-only* file.

Is there *any* risk of an injection attack here?

Specifically, does sqlite3 have any shell escapes or any way to change
the Unix permissions of the file it's accessing?

-- 
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The SQL Guide to SQLite

[Rick Ratchford]

Yes. You are correct. That is what they are.

Thanks for pointing this out. It probably should have been made clear at the
beginning of the book since this is not common in the majority of books I
own purchased here in the US.

Thanks again.
 
Rick
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Saturday, July 18, 2009 10:42 AM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>Rick Ratchford wrote:
#>> I just received my copy of the new book "The SQL Guide to 
#>SQLite" by 
#>> Rick F. van der Lans.
#>>
#>> There are many references within the book that are 
#>contained in square 
#>> brackets and some sort of keyword or code.
#>>
#>> Example: "...written about SQLite; see for example [NEWM05] and 
#>> [OWEN06]."
#>
#>These are most likely citations. See if there's a 
#>bibliography (a list of other books, articles and such) at 
#>the end of the book, each reference identified by one of these codes.
#>
#>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] The SQL Guide to SQLite

[Igor Tandetnik]

Rick Ratchford wrote:
> I just received my copy of the new book "The SQL Guide to SQLite" by
> Rick F. van der Lans.
>
> There are many references within the book that are contained in square
> brackets and some sort of keyword or code.
>
> Example: "...written about SQLite; see for example [NEWM05] and
> [OWEN06]."

These are most likely citations. See if there's a bibliography (a list 
of other books, articles and such) at the end of the book, each 
reference identified by one of these codes.

Igor Tandetnik 



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

[sqlite] The SQL Guide to SQLite

[Rick Ratchford]

Greetings!
 
I just received my copy of the new book "The SQL Guide to SQLite" by Rick F.
van der Lans.
 
There are many references within the book that are contained in square
brackets and some sort of keyword or code.
 
Example: "...written about SQLite; see for example [NEWM05] and [OWEN06]."
 
These bracketed keywords have no references within the book at all. I've
searched the internet on the subject of square brackets and find that they
are often used during the production of the documents, but should be
replaced with actual information when the final print is made.
 
Did anyone else get this book and end up with these square bracket
references? I'm wondering if I just received a copy that was not intended to
be sent out.
 
Thanks.
 
 

Cheers!

 

Rick

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

Re: [sqlite] SQLite 3.6.16 and the Asynchronous I/O transaction fails with multiple database files

[Dan]

On Jul 18, 2009, at 1:28 AM, Alessandro Merolli wrote:

> I'm trying to use the asynchronous I/O extension with the latest
> SQLite version for the first time in my project.
> This project is using database files attached into one database
> connection.
> I start a transaction which involves two different database files but,
> the commit operation is failing with the SQLITE_CANTOPEN (14) error.
> The transaction with only one database file is working fine.
> As far as I could debug, it seems that it's failing to open/create the
> master journal file (sqlite3async.c:1069).
> Does anybody knows if the asynchronous I/O feature was supposed to
> work in this scenario (database connection with multiple database
> files attached)?
>
> Any suggestion/help is appreciated.

Grab the new sqlite3async.c. Hopefully this will fix the problem:

   http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/async/sqlite3async.c=1.7

Dan.

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

Re: [sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Jay A. Kreibich]

On Sat, Jul 18, 2009 at 12:22:08AM -0700, Brian Dantes scratched on the wall:
> I have a largish DB around 1GB in size. There is a table with 5 million rows
> in it that has a 3-key index on it. This database file is fragmented -- to
> what degree I'm not sure.
> 
> Using sqlite 3.6.14, dropping and recreating the index under WinXP Pro (on a
> local disk) with no other activity takes about 90 seconds. With the same
> database on Red Hat Linux 64-bit with no other activity and a local disk,
> the index recreation takes almost 30 *minutes*. The activity is completely
> I/O bound. If I vacuum the database, the Windows time drops to 70 seconds,
> and the Linux time drops to 7 minutes.

  Assuming your data import is semi-random (in terms of index order)
  and assuming you haven't raised the default cache size, chances are
  you're going to be thrashing the page cache.  On nearly any system,
  bumping up the page cache size anywhere from 2x to 100x will almost
  always have a dramatic effect on the index build speed.

  If you're using a more modern version of SQLite, the Windows database
  will default the page size to the block size of the file system
  you're using.  I understand on most Windows systems that is 4K.  The
  UNIX system is very likely using the default 1K page size, unless
  you've changed it.  Since the page cache is defined by page numbers
  and not memory size, the page cache on the Windows box is likely to
  be 4x bigger.  An index is also a fairly efficient use of page space
  (assuming one of your index fields is not large strings) so the
  Windows box will be doing about 1/4th the I/O.  Matching the pages to
  blocks may also help with I/O performance.

  There is also the question of how much memory both systems have and
  how much the OS can use for a file cache, I'm guessing that's not as
  critical as the difference in page size.

  The other issue is disk speed.  Because SQLite waits for the drive to
  indicate the data has been physically written to the platters, there
  is a direct link between disk rotation speed and I/O performance.  If
  one system has a 5200 RPM disk and one has a 7200, you're likely to
  notice a difference.

> Conversely, with this same table and index starting from empty, if I start
> inserting rows with the index in place, on Windows the insertion speed drops
> dramatically after about 100-200K rows and takes about 6-7 *hours* to
> complete. On Linux 64-bit, the same experiment takes less than an hour to
> complete and the insertion speed seems fairly constant.

  It sounds like you're not using transactions to batch the inserts,
  requiring I/O transactions for each row.  For bulk inserts, it is
  much more efficient to batch 100 to 1+ inserts into a transaction
  (BEGIN; INSERT...; INSERT...; ... COMMIT;) to reduce the I/O load.
  That should reduce your import times on both platforms.

  I'd also hope the times became similar if you use transactions.  I'm
  a lot more surprised by this set of results, but I'm guessing the
  root of it is still the difference in page size.  There might be some
  issue on the Windows platform that is getting uncovered by the
  different access patterns that further limits performance.




  Try bumping the page size on the Linux DB up to 4K and see what
  happens.  Also, try using transactions for the inserts and see what
  happens, as well as verify the disk speeds are similar (although
  using transactions should help with this).


-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

Re: [sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Simon Slavin]

On 18 Jul 2009, at 8:22am, Brian Dantes wrote:

> I have a largish DB around 1GB in size. There is a table with 5  
> million rows
> in it that has a 3-key index on it. This database file is fragmented  
> -- to
> what degree I'm not sure.
>
> Using sqlite 3.6.14, dropping and recreating the index under WinXP  
> Pro (on a
> local disk) with no other activity takes about 90 seconds. With the  
> same
> database on Red Hat Linux 64-bit with no other activity and a local  
> disk,
> the index recreation takes almost 30 *minutes*. The activity is  
> completely
> I/O bound. If I vacuum the database, the Windows time drops to 70  
> seconds,
> and the Linux time drops to 7 minutes.

All the long timings you list suggest something is wrong somewhere,  
just as you thought.

When you describe doing the same activity on the two platforms, are  
you building the database separately on each platform, or are you  
copying the built database from one platform to the other before  
recreating the index ?  I'm trying to figure out whether the problem  
might be with the database file.

> Conversely, with this same table and index starting from empty, if I  
> start
> inserting rows with the index in place, on Windows the insertion  
> speed drops
> dramatically after about 100-200K rows and takes about 6-7 *hours* to
> complete. On Linux 64-bit, the same experiment takes less than an  
> hour to
> complete and the insertion speed seems fairly constant.

Are all these INSERT commands done as one transaction (using BEGIN and  
COMMIT) or separate ones ?   If the separate, use a transaction instead.

Are you doing these INSERT commands in your own application  
(presumably compiled both for Windows and Linux) ?  If so, try writing  
them all to a text file, then using the sqlite3 command-line tool to  
execute the text file.  This will take all your own programming and  
use of APIs out of consideration and say definitely whether the  
problem is with SQLite or not.

You can have the SQLite command-line tool itself prepare the text file  
for you: see the '.dump' command in

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

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

[sqlite] Error:1067

[avir]

Hi!!
i am making a desktop application(offline) using Adobe AIR1.5,Flex builder.
i am storing data locally ,but i am getting error which is:
Error #1067 implicit coercion of a value of type Class to an unrelated type
flash.events:SQLEvent
The code for this is as follows: 



http://www.adobe.com/2006/mxml;
creationComplete="init()"
layout="vertical">



















help me regarding this

-- 
View this message in context: 
http://www.nabble.com/Error%3A1067-tp24547952p24547952.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] Problem getting trigger to work

[Simon Slavin]

Quick note:

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


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

> It's an update.

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

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

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

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

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



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

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

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

Re: [sqlite] Problem getting trigger to work

[Kees Nuyt]

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

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


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

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

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

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

Re: [sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brian Dantes wrote:
> Can anyone offer any explanations for these huge disparities in sqlite
> performance on these two platforms? 

Run the SQLite analyser (available from the download page) on the
resulting databases and see what it says is different.  An example of
something that could lead to disparities is if the database is using
different encodings.  An ASCII string in a UTF16 encoded database will
take up twice as much space as a UTF8 encoded database (which means
twice the I/O) plus the indexed strings being twice the size (another
doubling of I/O) plus the cache available for other information will be
reduced.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkphf90ACgkQmOOfHg372QRHWACbBJjTvvCDk2Xqqufu7r7HvO3n
lw0Anjh0UxhczDmgfaGalIwuCOiO++kP
=FlEd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange index creation/insertion performance between Windows and Linux 64-bit

[Brian Dantes]

I have a largish DB around 1GB in size. There is a table with 5 million rows
in it that has a 3-key index on it. This database file is fragmented -- to
what degree I'm not sure.

Using sqlite 3.6.14, dropping and recreating the index under WinXP Pro (on a
local disk) with no other activity takes about 90 seconds. With the same
database on Red Hat Linux 64-bit with no other activity and a local disk,
the index recreation takes almost 30 *minutes*. The activity is completely
I/O bound. If I vacuum the database, the Windows time drops to 70 seconds,
and the Linux time drops to 7 minutes.

Conversely, with this same table and index starting from empty, if I start
inserting rows with the index in place, on Windows the insertion speed drops
dramatically after about 100-200K rows and takes about 6-7 *hours* to
complete. On Linux 64-bit, the same experiment takes less than an hour to
complete and the insertion speed seems fairly constant.

Can anyone offer any explanations for these huge disparities in sqlite
performance on these two platforms? And why Windows does badly on index
insertions and well on rebuilding it from scratch, and Linux 64-bit has the
exact opposite behavior?

Brian Dantes

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

Re: [sqlite] Problem getting trigger to work

[Jim Showalter]

It's an update.

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

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

The test code is doing this:

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

boolean caughtExpectedException = false;

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

assertTrue(caughtExpectedException);  this fails

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


- Original Message - 
From: "Simon Slavin" 
To: "General Discussion of SQLite Database" 
Sent: Friday, July 17, 2009 11:00 PM
Subject: Re: [sqlite] Problem getting trigger to work


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

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

Re: [sqlite] Problem getting trigger to work

[Simon Slavin]

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

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

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

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

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