Re: [sqlite] performance, transactions and wal checkpoints

2010-08-09 Thread Richard Hipp
On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner wrote: > Hello, > I have a questions about the correct use of transactions and WAL. > > I am writing an application that: > 1. should very fast > 2. should be very responsive > 3. don't care if the last N minutes of data will be lost (but DB should

[sqlite] performance, transactions and wal checkpoints

2010-08-09 Thread Yoni Londner
Hello, I have a questions about the correct use of transactions and WAL. I am writing an application that: 1. should very fast 2. should be very responsive 3. don't care if the last N minutes of data will be lost (but DB should never be corrupted) What I tried to do: 1. open a transaction

Re: [sqlite] Process memory space exhausted in 3.7.0

2010-08-09 Thread Victor Morales-Duarte
As it turns out, I can reproduce the failure using a single huge insert. The code that I'm including below compiles under bcc32 from Embarcadero's C++ Builder 2007 and cl from vs2005. Since it's more likely that people have MS compilers available, the compilation line having this source file and th

[sqlite] SQLite bug report: Bad truncation in column mode with non ASCII characters

2010-08-09 Thread François Bonzon
In ".mode column", the function used to compute the length of the string to print does not account for multi-byte characters, resulting in a shorter string, and all remaining fields in the line shifted to the left. Example: sqlite> select * from test limit 3; namename2 name3 z

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

2010-08-09 Thread Adrian Aichner
Here is the little patch verbatim. Regards, Adrian diff -u c:\Hacking\sqlite3\version3.html.~1~ c:\Hacking\sqlite3\version3.html --- c:\Hacking\sqlite3\version3.html.~1~ 2010-08-08 17:54:51.12500 +0200 +++ c:\Hacking\sqlite3\version3.html 2010-08-08 17:54:51.140625000 +0200 @@ -235,8 +235,8

Re: [sqlite] Incremental row number associated to the current Query?

2010-08-09 Thread Igor Tandetnik
Mike Henshaw wrote: > 1. The application is from a third party with no access to the source code > but the SQL query can be updated since the SQL query > is used in a custom HTML template which can also be updated. > 2. The calculations to the RowNum would then have to be in the C/CPP > applica

Re: [sqlite] Incremental row number associated to the current Query?

2010-08-09 Thread Mike Henshaw
>> Is there a way to create an incremental row or show a row number that is >> linked to the current select query that can be used in >> calculations? > >> Basically a row counter for the current query that can be used in >> calculations. >Your application makes a series of sqlite3_step calls,

Re: [sqlite] Incremental row number associated to the current Query?

2010-08-09 Thread Igor Tandetnik
Mike Henshaw wrote: > Is there a way to create an incremental row or show a row number that is > linked to the current select query that can be used in > calculations? > > Basically a row counter for the current query that can be used in > calculations. Your application makes a series of sqli

[sqlite] Incremental row number associated to the current Query?

2010-08-09 Thread Mike Henshaw
Is there a way to create an incremental row or show a row number that is linked to the current select query that can be used in calculations? Basically a row counter for the current query that can be used in calculations. Notes: 1. The query can be change at any time. 2. The application is writt

Re: [sqlite] commit transaction/savepoints on program crash

2010-08-09 Thread Simon Slavin
[New text /below/ any text you want to quote, please.] On 9 Aug 2010, at 9:09pm, Josh wrote: > Thanks for the answers, but I guess I should have made my question more > clear. I knew that you can commit every sql statement individually, then > the question would be, how can I roll them back? >

Re: [sqlite] commit transaction/savepoints on program crash

2010-08-09 Thread Josh
Thanks for the answers, but I guess I should have made my question more clear. I knew that you can commit every sql statement individually, then the question would be, how can I roll them back? In other words I'd like something like savepoint and rollback to savepoint, while not loosing the tra

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
Igor Tandetnik writes: [...] > > Why would you want to fail statements that end up not violating any > constraints? The idea is to prevent that somebody can change the table staff that is not already in the table. Therefore I create the first user by not switching on foreign keys. My idea was

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Igor Tandetnik
Oliver Peters wrote: > Please believe me I really try hard to understand - but what I don't > understand > is the fact that you can insert in this case: > > > PRAGMA foreign_keys = ON; > > /* > **success INSERT = yes > */ > > CREATE TABLE a( >id

Re: [sqlite] commit transaction/savepoints on program crash

2010-08-09 Thread Simon Slavin
On 9 Aug 2010, at 5:05pm, Josh wrote: > I'm new to the list and had a question. I know the default behavior for > savepoints (or any transactions) is that if they have not been committed, if > the program crashes, they are lost. Is there any way to have them committed > by > default? Basicall

Re: [sqlite] commit transaction/savepoints on program crash

2010-08-09 Thread Pavel Ivanov
> Is there any way to have them committed by > default? Basically I *only* want the transaction rolled back in case of an > explicit rollback statement, not due to program crash/power failure, etc. Does > anyone know of a way of doing this? You can avoid transaction begin/commit statements, so tha

[sqlite] commit transaction/savepoints on program crash

2010-08-09 Thread Josh
Hello all, I'm new to the list and had a question. I know the default behavior for savepoints (or any transactions) is that if they have not been committed, if the program crashes, they are lost. Is there any way to have them committed by default? Basically I *only* want the transaction rolled

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
Dan Kennedy writes: > > > why returns the "INSERT INTO a" not an error while the "INSERT INTO > > b" does? How > > corresponds this behaviour to the concept of FOREIGN KEYS? > > > CREATE TABLE a( > >idINTEGER PRIMARY > > KEY > > AUTOINCREM

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Martin.Engelschalk
Am 09.08.2010 16:57, schrieb Oliver Peters: > [...] > > To my mind the simplified question is: > > why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? > How > corresponds this behaviour to the concept of FOREIGN KEYS? > > I wrote the code into file.sql (encoding=utf8, if

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Dan Kennedy
> why returns the "INSERT INTO a" not an error while the "INSERT INTO > b" does? How > corresponds this behaviour to the concept of FOREIGN KEYS? > CREATE TABLE a( >idINTEGER PRIMARY > KEY > AUTOINCREMENT, >id_staff_editor

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
[...] To my mind the simplified question is: why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? How corresponds this behaviour to the concept of FOREIGN KEYS? I wrote the code into file.sql (encoding=utf8, if this matters) and started sqlite3 -bail test.db3 < file.

Re: [sqlite] Mistake in documentation and question

2010-08-09 Thread Richard Hipp
On Mon, Aug 9, 2010 at 8:38 AM, Ioannis Epaminonda wrote: > > In page http://www.sqlite.org/datatype3.html > under section 3.2 Affinity Of Comparison Operands 2nd bullet point should > read: > An expression "CAST(expr AS type)" instead of "CAST(expr TO > type)" > > In addition a quick qu

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Black, Michael (IS)
I found the (apparent) problem. This works and throws the constraint violation: PRAGMA foreign_keys=OFF; CREATE TABLE staff_01( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, code CHAR(2) NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES staff(id) ); INSERT INTO "staff_

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
I use the standard windows binary from http://www.sqlite.org/sqlite-3_7_0_1.zip and if I do the recommended test I get >sqlite3 SQLite version 3.7.0.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma foreign_keys; 0 sqlite> pragma foreign_keys=ON; sqlite> pr

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Simon Davies
On 9 August 2010 14:56, Black, Michael (IS) wrote: > From http://www.sqlite.org/foreignkeys.html > > > > Although I believe I compiled my shell WITHOUT these defined but I still > don't get enforcement of the foreign key constraint. > > It appears that foreign keys are fully constrained by defaul

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Black, Michael (IS)
>From http://www.sqlite.org/foreignkeys.html Although I believe I compiled my shell WITHOUT these defined but I still don't get enforcement of the foreign key constraint. It appears that foreign keys are fully constrained by default. Is there any way in the shell to find out if it's enabled

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
Igor Tandetnik writes: > > Oliver Peters wrote: > > This sql code works in the two cases but AFAIK it shouldn't in the second > > > > PRAGMA foreign_keys = ON; > > > > CREATE TABLE staff_02( > > idINTEGER PRIMARY KEY AUTOINCREMENT, > > id_staff_editor

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Igor Tandetnik
Oliver Peters wrote: > This sql code works in the two cases but AFAIK ;-) it shouldn't in the second > > PRAGMA foreign_keys = ON; > > CREATE TABLE staff_02( > idINTEGER PRIMARY KEY AUTOINCREMENT, > id_staff_editor INTEGER NOT NULL, > cod

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
and the added , after UNIQUE(code) doesn't change anything (oh what a day ;-) ) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign key the true error - sqlite 3.7.0.1

2010-08-09 Thread Oliver Peters
This sql code works in the two cases but AFAIK ;-) it shouldn't in the second Am I right? CREATE TABLE staff_01( idINTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL,

Re: [sqlite] Round was Mistake in documentation and question

2010-08-09 Thread Roger Andersson
> In addition a quick question. > Is there a way to perform a division of two columns (real > type) and force the result to be presented/rounded in 2 > decimal places ? > ROUND should do the trick ;-) http://www.sqlite.org/lang_corefunc.html#round Please note http://www.sqlite.org/faq.html#q16

Re: [sqlite] foreign key error 01

2010-08-09 Thread Igor Tandetnik
Oliver Peters wrote: > reproduction > > > CREATE TABLE staff_01( > idINTEGER PRIMARY KEY AUTOINCREMENT, > id_staff_editor INTEGER NOT NULL, > code CHAR(2) NOT NULL, > FOREIGN KEY(id_staff_edito

Re: [sqlite] foreign key error 01

2010-08-09 Thread Black, Michael (IS)
Instead of REFERENCES staff(id) Try REFERENCES staff_01(id) That spelling thing will get you every time...:-) Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on beha

Re: [sqlite] foreign key error 01

2010-08-09 Thread Oliver Peters
Oliver Peters writes: sorry, my FK-clause was wrong (forgot the _01 & 0_2) my mistake (but the next thread will show the true error) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-

[sqlite] foreign key error 01

2010-08-09 Thread Oliver Peters
Hello environment --- OS : Win XP (every patch) sqlite : 3.7.0.1 reproduction CREATE TABLE staff_01( idINTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor I

[sqlite] Mistake in documentation and question

2010-08-09 Thread Ioannis Epaminonda
In page http://www.sqlite.org/datatype3.html under section 3.2 Affinity Of Comparison Operands 2nd bullet point should read: An expression "CAST(expr AS type)" instead of "CAST(expr TO type)" In addition a quick question. Is there a way to perform a division of two columns (real type) a

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-09 Thread Black, Michael (IS)
Sounds to me like Boyer-Moore is needed http://en.wikipedia.org/wiki/Boyer%E2%80%93Moore_string_search_algorithm And...I would probably pre-load the database table into 26 seperate memory tables to avoid any SQL interactivity at all other than the initial loading. Adding the SQL layer slows th

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

2010-08-09 Thread Tim Romano
First, permit me a little rant. As a user, I dislike this kind of incremental search feature if there's no easy way to toggle it or to configure it and the list of items will be large enough to cause a typing lag. The feature can become an intrusive nuisance, the opposite of what is intended. Brow

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

2010-08-09 Thread Simon Slavin
On 9 Aug 2010, at 12:37pm, Richard Hipp wrote: > On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin wrote: > >> On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: >> >>> I hope you'll find this little patch useful. >> >> Attachments are stripped from messages to this list. Please describe what >> you

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

2010-08-09 Thread Richard Hipp
On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin wrote: > > On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: > > > I hope you'll find this little patch useful. > > Attachments are stripped from messages to this list. Please describe what > your patch does in your message, and post the URL where inter

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

2010-08-09 Thread Simon Slavin
On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote: > I hope you'll find this little patch useful. Attachments are stripped from messages to this list. Please describe what your patch does in your message, and post the URL where interested people can download it. Simon. _

[sqlite] typo fixing patch for http://www.sqlite.org/version3.html

2010-08-09 Thread Adrian Aichner
Greetings! I hope you'll find this little patch useful. I'm not sure whether this webpage is auto-generated. In that case the patch should still be useful to spot the typos. Thanks for this great SQL database engine! -- Adrian Aichner mailto:adrian.aich...@gmail.com _

Re: [sqlite] Slowdown when adding terms to query

2010-08-09 Thread Edward Hawke
Thank you all for your responses, Igor - using a UNION made a huge difference, thank you. I have a quick query on from the suggestion of adding indices for anything that appears either side of an equals sign. I have many user-selectable terms that can be added to the query. Is it worth adding

Re: [sqlite] synchronous issue

2010-08-09 Thread Lei, Rick (GE EntSol, SensInsp)
Hi, Simon, Yes, I agree that a journal file in memory will lead some risk when I set synchronous as off. However the speed is a much critical requirement for me. I have tried to close and reopen the file. However the time to open a file and prepare the first query need more time. Maybe I need sp

Re: [sqlite] synchronous issue

2010-08-09 Thread Simon Slavin
On 9 Aug 2010, at 7:13am, Lei, Rick (GE EntSol, SensInsp) wrote: > In order to reduce the operation time, I set synchronous as OFF and > journal_mode as MEMORY by PRAGMA command. However I found that the data > may be lost after inserting a BLOB data, even when I use a commit > action. Yes, tran