Re: [sqlite] Using "sub-select" to return limit

[Pavel Ivanov]

> What I ultimately want to do is iterate through a table that contains the> 
> limit and use that value to select all matching values from another table,> 
> limiting the number of records selected from the group to that defined by> 
> limit.
There's no way to do such thing using only SQL. You have to select
data from your table of limits into your programming language, iterate
through results and for each row issue separate select statement with
appropriate limit (which will be a constant not a nested select
query).


Pavel


On Thu, Nov 3, 2011 at 5:23 PM, Don V Nielsen  wrote:
> Given the following, I get an error that f.zip does not exist.  Obviously,
> I am mentally missing something contextually, but I'm not getting it.
>  Would someone work through the scope of things in this select.
>
> select p.*,pr.rowid from pool_wi as p
> inner join add_priorities as pr on pr.prty = p.prty
> where p.zip = '53005' and p.crrt = 'C022'
> order by pr.rowid
> limit (
>  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
> = p.crrt
> )
>
> What I ultimately want to do is iterate through a table that contains the
> limit and use that value to select all matching values from another table,
> limiting the number of records selected from the group to that defined by
> limit.  For example, a table would hold two rows of zip, route and limit,
> [53005,C020,1] & [53005,C022,2].  I want to use the zip and route to select
> all matching records from another table, but limit the result of the
> sub-select to the qty of records as defined by limit.
>
> Thanks for your time and consideration.
> dvn
> ___
> 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] Compiler Warnings building Win x64 with VS2010

[Richard Hipp]

On Thu, Nov 3, 2011 at 2:38 PM, GB  wrote:

> When building SQLite for Windows64 using Visual Studio 2010, I get lots of
> warnings concerning 64Bit Values being assigned to 32Bit Variables. These
> occur when the result of pointer arithmetics is assigned to int variables
> since in Windows64 ints are 32Bits wide while pointers are 64Bit. How can
> these warnings be avoided? Do other compilers also produce them? My builds
> enable options SQLITE_ENABLE_STAT3, *SQLITE_ENABLE_FTS3 and
> **SQLITE_ENABLE_FTS3_**PARENTHESIS.
>

http://www.sqlite.org/faq.html#q17
http://www.sqlite.org/testing.html


>
> regards,
> gerd
> *
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] sql server management studio like tool for SQLite

[Don V Nielsen]

If you are looking to manage a database file, such as view the contents of
tables, write and test scripts for processing, etc...there are a number of
management packages.  I like using Sqlite Expert.

If you are looking for something that is going to do DTS, bulk imports,
etc...I don't know what exists.  I don't think there is anything.

On Thu, Nov 3, 2011 at 6:41 PM, David Hubbard  wrote:

> Is there any tool for SQLite like sql server management studio? We are
> looking at using SQLite and I have no
> expireince with it but would like an easy to use tool to use with
> SQLite that can perform the same functions as SSMS.
> Or can you connect SQLite to SSMS?
>
> Thanks any help received will be appreciated.
> ___
> 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] sql server management studio like tool for SQLite

[Simon Slavin]

On 3 Nov 2011, at 11:41pm, David Hubbard wrote:

> Is there any tool for SQLite like sql server management studio?

Almost everything you use that program for doesn't exist in SQLite.

So tell us what you want to do with (to ?) SQLite and maybe someone can suggest 
something.

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

[sqlite] sql server management studio like tool for SQLite

[David Hubbard]

Is there any tool for SQLite like sql server management studio? We are
looking at using SQLite and I have no
expireince with it but would like an easy to use tool to use with
SQLite that can perform the same functions as SSMS.
Or can you connect SQLite to SSMS?

Thanks any help received will be appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[James Berry]

On Nov 3, 2011, at 6:19 AM, James Berry wrote:

> I've seen this same problem. It crashes seemingly due to bugs in llvm-clang 
> when compiled for arm6 if, as you say, optimizations at any level are turned 
> on. I've worked around this issue by turning off optimizations for arm6. 
> Sqlite3, by the way, is not the only bit of my iOS app that encounters 
> problems with that arm6 optimization combination. arm6, fortunately, is 
> required by fewer and fewer devices these days.
> 
> Report bugs to apple, if you care.

Btw, somebody mentioned to me this morning that these issues can be worked 
around by disabling thumb mode when compiling for arm6 (as an alternative to 
disabling optimizations). That would be the -mno-thumb switch to the compiler. 
Note that these issues only manifest when compiling using the llvm/clang 
compiler.

James

> 
> 
> On Nov 3, 2011, at 1:39 AM, Price,Ray wrote:
> 
>> Hi All,
>> 
>> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
>> application that works fine and has been working find for over a year, but 
>> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
>> ONLY on older devices still running iOS 3.1.3.
>> 
>> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
>> goes away, but this is obviously FAR from ideal.
>> 
>> Has anyone else experienced this?  Is there anything I can do to narrow down 
>> the problem, or is there anyone I could report this to?
>> 
>> Thanks
>> Ray
>> 
>> 
>> 
>> 
>> This e-mail message, including any attachments, is for the sole use of the 
>> person to whom it has been sent, and may contain information that is 
>> confidential or legally protected. If you are not the intended recipient or 
>> have received this message in error, you are not authorized to copy, 
>> distribute, or otherwise use this message or its attachments. Please notify 
>> the sender immediately by return e-mail and permanently delete this message 
>> and any attachments. Gartner makes no warranty that this e-mail is error or 
>> virus free.
>> ___
>> 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] Using "sub-select" to return limit

[Don V Nielsen]

Given the following, I get an error that f.zip does not exist.  Obviously,
I am mentally missing something contextually, but I'm not getting it.
 Would someone work through the scope of things in this select.

select p.*,pr.rowid from pool_wi as p
inner join add_priorities as pr on pr.prty = p.prty
where p.zip = '53005' and p.crrt = 'C022'
order by pr.rowid
limit (
  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
= p.crrt
)

What I ultimately want to do is iterate through a table that contains the
limit and use that value to select all matching values from another table,
limiting the number of records selected from the group to that defined by
limit.  For example, a table would hold two rows of zip, route and limit,
[53005,C020,1] & [53005,C022,2].  I want to use the zip and route to select
all matching records from another table, but limit the result of the
sub-select to the qty of records as defined by limit.

Thanks for your time and consideration.
dvn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode is reliable?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/11/11 11:32, Paxdo Presse wrote:
> Roger, I meant: I feel that WAL is rarely used with its new
> capabilities.

Outside almost 100 million Android devices?

> SQLite + WAL may, perhaps, now compete with Mysql / postgres to handle
> web apps with thousands of users. But what is its limit?

Limit of what?  The number of concurrent requests you can serve will be
limited by hardware, operating system, and all the other code that runs in
order to service a request.  SQLite could be anywhere from 95% of the code
run to service a request to 2%.

You can of course find out exactly what happens in your exact setup by
implementing a representative benchmark.  There are plenty of testing
tools (eg ab).

> In this context, there is currently not a lot of feedback, I feel. 
> There are not enough people know about these new opportunities.

I'm still very confused what exactly it is you want.  Do you want some
reassurance that SQLite will never give wrong answers?

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

Do you want reassurance it can handle any load thrown at it?  The code is
performant, but what can be handled will depend a lot more on the
non-SQLite code.

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

iEYEARECAAYFAk6y7UgACgkQmOOfHg372QTi7wCfdlTRXDdTUwfNZ9ndJfxKXGRg
CMIAnj7X1GZXvLHMnyY6CO7K/jqkhjqp
=kTgr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> David wrote:
>> Simon L wrote 2011-10-25 06:20:
>>> To reproduce this problem, enter the following 5 SQL statements at the
>>> SQLite command line.
>>>
>>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>>> insert into X values (1);
>>> insert into Y select * from X;
>>> insert into Y select * from X;
>>>
>>>
>>> When I tried to run the last SQL statement twice,  SQLite produced the
>>> following error message.
>>> Error: PRIMARY KEY must be unique
>>>
>>>
>>> Is this a bug? Please advise. Thank you.

>> This certainly looks like a bug. I got a constraint failure when I tried
>> it in sqlite 3.7.8.
>>
>> But it works fine when you state the column name explicitly in the
>> select clause.
>>
>> Like this:
>>
>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>> insert into X values (1);
>> insert into Y select id from X;
>> insert into Y select id from X;
>>
>> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
>> key checks,
>> which was reported on the mailing list earlier this year:
>>
>> http://www.sqlite.org/src/tktview?name=6284df89de
>>
>> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
> 
> Thanks for pointer; root cause, indeed, transfer optimization (it ignores 
> table
> INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use 
> table's
> ON CONFLICT clause by default; falls back to regular transfer if destination
> table is not empty and we cannot handle ON CONFLICT resolution);
> 
> Index: sqlite3-3.7.8/src/insert.c
> ===
> --- sqlite3-3.7.8.orig/src/insert.c   2011-10-25 15:20:26.0 +0400
> +++ sqlite3-3.7.8/src/insert.c2011-10-25 15:54:54.0 +0400

Ping.

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

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Two alternative patches, choose whichever you like.
> 
> Alternative 1: (IMO, preferred; tested)
> Don't lowercase argument of .schema.
> With PRAGMA case_sensitive_like = ON, you just need to use right case for 
> table
> names.
> 
> Index: sqlite3-3.7.8/src/shell.c
> ===
> --- sqlite3-3.7.8.orig/src/shell.c2011-10-23 14:00:50.0 +0400
> +++ sqlite3-3.7.8/src/shell.c 2011-10-23 14:01:14.0 +0400
> @@ -2018,9 +2018,7 @@ static int do_meta_command(char *zLine,
>  data.showHeader = 0;
>  data.mode = MODE_Semi;
>  if( nArg>1 ){
> -  int i;
> -  for(i=0; azArg[1][i]; i++) azArg[1][i] = (char)tolower(azArg[1][i]);
> -  if( strcmp(azArg[1],"sqlite_master")==0 ){
> +  if( sqlite3_strnicmp(azArg[1],"sqlite_master",13+1)==0 ){
>  char *new_argv[2], *new_colv[2];
>  new_argv[0] = "CREATE TABLE sqlite_master (\n"
>"  type text,\n"
> @@ -2034,7 +2032,7 @@ static int do_meta_command(char *zLine,
>  new_colv[1] = 0;
>  callback(&data, 1, new_argv, new_colv);
>  rc = SQLITE_OK;
> -  }else if( strcmp(azArg[1],"sqlite_temp_master")==0 ){
> +  }else if( sqlite3_strnicmp(azArg[1],"sqlite_temp_master",18+1)==0 ){
>  char *new_argv[2], *new_colv[2];
>  new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
>"  type text,\n"
> =

Ping.

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

[sqlite] Compiler Warnings building Win x64 with VS2010

[GB]

When building SQLite for Windows64 using Visual Studio 2010, I get lots 
of warnings concerning 64Bit Values being assigned to 32Bit Variables. 
These occur when the result of pointer arithmetics is assigned to int 
variables since in Windows64 ints are 32Bits wide while pointers are 
64Bit. How can these warnings be avoided? Do other compilers also 
produce them? My builds enable options SQLITE_ENABLE_STAT3, 
*SQLITE_ENABLE_FTS3 and **SQLITE_ENABLE_FTS3_PARENTHESIS.


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

[sqlite] [patch] shell.c: make written history size tuneable

[Yuriy Kaminskiy]

... with $SQLITE3_HISTSIZE. Positive numbers limits history size, zero - don't
write to history at all (but read existing and keep in memory), negative -
always append to history file (useful when you run few instances of sqlite3 at
time and want to save history from all).
Default - 100, same as currently hardwired.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.7/sqlite3.1
===
--- sqlite3-3.7.7.orig/sqlite3.12011-08-31 23:03:27.0 +0400
+++ sqlite3-3.7.7/sqlite3.1 2011-08-31 23:03:27.0 +0400
@@ -234,6 +234,14 @@ o If the -init option is present, the sp

 o All other command line options are processed.

+.SH ENVIRONMENT
+.TP
+.B $SQLITE3_HISTSIZE
+The maximum number of lines contained in the history file.
+When negative, only append new commands to the history file (useful to prevent
lossing some history when running few sqlite3 instances in parallel).
+When zero, history is not saved.
+The default value is 100.
+
 .SH SEE ALSO
 http://www.sqlite.org/
 .br
Index: sqlite3-3.7.7/src/shell.c
===
--- sqlite3-3.7.7.orig/src/shell.c  2011-08-31 23:02:39.0 +0400
+++ sqlite3-3.7.7/src/shell.c   2011-08-31 23:05:07.0 +0400
@@ -51,6 +51,9 @@
 # define read_history(X)
 # define write_history(X)
 # define stifle_history(X)
+# define append_history(X,Y)
+# define where_history() (0)
+# define using_history()
 #endif

 #if defined(_WIN32) || defined(WIN32)
@@ -2900,11 +2902,23 @@ int main(int argc, char **argv){
   }
 #if defined(HAVE_READLINE) && HAVE_READLINE==1
   if( zHistory ) read_history(zHistory);
 #endif
+  using_history(); /* initialize where_history() */
+  nHistory = where_history();
   rc = process_input(&data, 0);
   if( zHistory ){
-stifle_history(100);
+const char *history_size_str = getenv("SQLITE3_HISTSIZE");
+int history_size = 100;
+if( history_size_str!=NULL )
+  history_size = strtol(history_size_str, NULL, 0);
+if( history_size>0 ){
+  stifle_history(history_size);
 write_history(zHistory);
+}else if( history_size==0 ){
+  /* do nothing */
+}else{
+  append_history(where_history()-nHistory, zHistory);
+}
 free(zHistory);
   }
   free(zHome);


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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

> 
>> I feel that WAL is rarely used now.
> 
> It is used on my Android phone running 2.3.  That is almost 100 million
> devices with all sorts of programs and crash scenarios.  Is that rare?


Roger, I meant: I feel that WAL is rarely used with its new capabilities.

SQLite + WAL may, perhaps, now compete with Mysql / postgres to handle web apps 
with thousands of users. But what is its limit?

In this context, there is currently not a lot of feedback, I feel. 
There are not enough people know about these new opportunities.

Feedback is welcome!

Thank you Simon for your answer.

Olivier Vidal


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

Re: [sqlite] Slow INDEX

[Nico Williams]

On Thu, Nov 3, 2011 at 12:39 PM, Fabian  wrote:
> I just tested it, and it made no difference. The root cause of the problem
> is most likely not slow writes, because inserting duplicate values (which
> are ignored instead of written to disk) are just as slow.

If you could use DTrace you could really find out, but since we have
to guess, the best (almost certainly correct) guess is that it's the
*read* I/Os that are killing you, not the writes.

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

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 6:05pm, Paxdo Presse wrote:

> You think SQLite can handle a website / web app that has 1000 readers per 
> day, including 200 to 300 who will write regularly?

You should be able to handle 10,000 posts a day without problems.  A single 
INSERT command keeps the database locked only for a very short time.

Your bone of contention is when you have lots of readers all logged in at the 
same time, all loading pages which list forum threads and clicking 'next ... 
next ... next'.  If you have 200 or 300 of those all logged on at the same time 
(not at different times of the same day), you may have problems.  But you 
should be able to deal with even that if you write your code carefully, so as 
not to keep the database locked for longer than necessary.

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

Re: [sqlite] WAL mode is reliable?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/11/11 11:05, Paxdo Presse wrote:
> I feel that WAL is rarely used now.

It is used on my Android phone running 2.3.  That is almost 100 million
devices with all sorts of programs and crash scenarios.  Is that rare?

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

iEYEARECAAYFAk6y2lMACgkQmOOfHg372QSCgQCgzcD037y//w2RnBRGbzm1db+W
Ka4AoJI/1nt24age9bjrS+WQovI8IaK6
=f2ac
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 5:44pm, Richard Hipp wrote:

> On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:
> 
>> 
>> If I start getting contention issues (locks failing because of too many
>> concurrent attempts at access) then I'll look into using some PRAGMAs,
>> possibly switching to WAL.
>> 
>> 
> Several points:

Fair enough.  So if I have a database created with version 3.6.x, but are now 
using 3.7.3, all I need to convert it to WAL mode is to open it, then issue

PRAGMA journal_mode = WAL

, then close it properly, right ?

(I can't update past 3.7.3 for SQLite as I know you're bursting to tell me: 
it's the version compiled into PHP 5.3.4 on that server.  It will change when 
they update the version of PHP.)

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

Thank you M. Hipp !

I feel that WAL is rarely used now. 

It is difficult to get opinions / feedback on this feature. 

And for users of database servers (MySQL, Postgres, etc.). It is impossible to 
have a reliable database without a server.

You think SQLite can handle a website / web app that has 1000 readers per day, 
including 200 to 300 who will write regularly?

Olivier Vidal


Le 3 nov. 2011 à 18:44, Richard Hipp a écrit :

> On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:
> 
>> 
>> If I start getting contention issues (locks failing because of too many
>> concurrent attempts at access) then I'll look into using some PRAGMAs,
>> possibly switching to WAL.
>> 
>> 
> Several points:
> 
> (1) You only have to set WAL mode once for the database.  The database
> remembers that it is in WAL mode and all subsequent opens bring it back up
> in WAL mode again (until you deliberately change it out of WAL mode.)  You
> do *not* have to set WAL mode every time you open the database file.
> 
> (2) WAL really does give better concurrency since it allows other processes
> to continue reading while a single process is writing to the database.   In
> rollback mode you can have one writer *or* multiple readers.  In WAL mode
> you can have one writer *and* multiple readers.  On a busy system, or in a
> system with long-running transactions, the difference can be significant.
> 
> (3) Transactions commit a lot faster with WAL.  Depending on your app, you
> might see a measurable performance improvement just by switching to WAL.
> 
> (4) WAL mode is much more resistant to corruption following a power failure
> that occurs soon after the disk drive lied and said that content was
> committed to oxide when in fact it was still in a volatile track buffer.
> And pretty much all consumer-grade disk drives tell such lies these days.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> When WHERE condition is constant, there are no need to evaluate and check it 
>> for
>> each row. It works, but only partially:
> ...
>> [In fact, you can move out out loop not only *whole* constant WHERE, but also
>> all constant AND terms of WHERE, like this:
>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>> I'll take a shot on that later.]
> 
> Here it goes.
> 
> Prerequisite: previous patch.
> Passes quick regression test (make test).
> Possible problem: short-circuits evaluation. Should not be problem, IMO, as 
> only
> constants references? Please verify.

Ping.

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

Re: [sqlite] WAL mode is reliable?

[Richard Hipp]

On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin  wrote:

>
> If I start getting contention issues (locks failing because of too many
> concurrent attempts at access) then I'll look into using some PRAGMAs,
> possibly switching to WAL.
>
>
Several points:

(1) You only have to set WAL mode once for the database.  The database
remembers that it is in WAL mode and all subsequent opens bring it back up
in WAL mode again (until you deliberately change it out of WAL mode.)  You
do *not* have to set WAL mode every time you open the database file.

(2) WAL really does give better concurrency since it allows other processes
to continue reading while a single process is writing to the database.   In
rollback mode you can have one writer *or* multiple readers.  In WAL mode
you can have one writer *and* multiple readers.  On a busy system, or in a
system with long-running transactions, the difference can be significant.

(3) Transactions commit a lot faster with WAL.  Depending on your app, you
might see a measurable performance improvement just by switching to WAL.

(4) WAL mode is much more resistant to corruption following a power failure
that occurs soon after the disk drive lied and said that content was
committed to oxide when in fact it was still in a volatile track buffer.
And pretty much all consumer-grade disk drives tell such lies these days.

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

Re: [sqlite] Slow INDEX

[Fabian]

I just tested it, and it made no difference. The root cause of the problem
is most likely not slow writes, because inserting duplicate values (which
are ignored instead of written to disk) are just as slow.

But your suggestion may help me with another problem: when I fill an empty
database with million of rows, they are written to disk in the order I
inserted them, not in the order a VACUUM would put them (sequential per
table). If I can force an atomic commit by enabling a journal_mode (MEMORY
for example), I would possibly end up with much better structured database
file.

So your idea is appreciated, but for the problem reported in this thread,
it had no effect.

2011/11/3 nobre 

>
> Have you tried setting journal_mode to the default DELETE option ?
> Without atommic commits, maybe your inserts are going to disk one by one
> instead of in a single step, when commiting your transactions, thus slowing
> down disk writes.
>
>
> Fabian-40 wrote:
> >
> > 2011/11/2 Black, Michael (IS)
> > I do not use WAL, since I have turned 'journal_mode' off (to improve
> > insert
> > performance), and as far as I know WAL is only usefull when you need to
> > keep a journal?
> >
> > I also have 'synchronous' off, so SQLite shouldn't be waiting for any
> > filesystem flushes. I hoped it was writing all the newly inserted rows to
> > disk using a single operation, as soon as I call 'commit'. But I observed
> > it, and found out its already writing the rows as soon as soon as they
> are
> > inserted, not batching them for when I call commit.
> >
> > So that could be part of the problem. I don't have a real Windows machine
> > at hand, so I will build one tomorrow, but if your expectations are
> > correct, than it will be even slower than inside a virtual machine,
> > because
> > it will do individiual writes for the 1 million rows too, making
> > performance even worse than it is now.
> >
> > Anothing thing is that I don't expect the slow performance have anything
> > to
> > do with slow disk writes, only with disk reads. I know this because when
> I
> > make the index UNIQUE, and try to insert 10.000 duplicate rows (which are
> > all ignored), it has the same bad performance, even though there are zero
> > bytes written to disk. So it points in the direction of the reads making
> > it
> > slow, not the writes.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Slow-INDEX-tp32766886p32772266.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 3:57pm, Paxdo Presse wrote:

> Even if the requests are very fast, it seems risky not to use WAL?

As opposed to no journal ?  Yes, using no journal at all is risky.  But the 
older style rollback journal system was about as fast and as trustworthy as WAL 
journals.  I see no reason not to use it as that's what SQLite defaults to 
using.

If I start getting contention issues (locks failing because of too many 
concurrent attempts at access) then I'll look into using some PRAGMAs, possibly 
switching to WAL.

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

Re: [sqlite] WAL mode is reliable?

[Tim Streater]

On 03 Nov 2011 at 14:02, Simon Slavin  wrote: 

> On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote:
>
>> How did you get concurrent connections? (about)
>
> This is related specifically to the web language I use (PHP) so it won't help
> you if you're using something else.
>
> Actually my system works like this: the web pages themselves are '.html' files
> do most of their work in JavaScript.  When they want to talk to the database
> they use XMLHTTPRequest to call other utility '.php' files which have the job
> of just a single query or execution.  Though they actually talk JSON to
> one-another, not XML.

This is pretty much what I do too. Except I don't even use JSON; I invented my 
own.

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

> For concurrency it works fine.  The amount of time a connection takes is very 
> small: just long enough to open, set timeout, do a single query/exec, and 
> close the connection.  So even with lots of users there aren't that many 
> concurrent connections to the database file.
> 
> Looking back at the 'Subject' header, I note that I don't use a PRAGMA to 
> specify journal mode.  It's using whatever journal mode is default for a 
> database created in the sqlite3 shell tool.


Thank you Simon.

Even if the requests are very fast, it seems risky not to use WAL?

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

Re: [sqlite] Progress callback and nested queries

[Duquette, William H (318K)]

On 11/2/11 10:01 PM, "Dan Kennedy"  wrote:


>On 11/03/2011 01:11 AM, Duquette, William H (318K) wrote:
>> I'm pretty sure I know the answer to this.
>>
>> Sqlite3 allows you to define a "progress" callback, which will be
>>called every so many byte-code instructions during a long-running query,
>>so that you can update a progress bar or like that.
>>
>> I'm assuming that querying the same database using the same handle
>>during a "progress" callback would be a *bad* idea.  Am I mistaken?
>
>The docs here say you're not supposed to (last paragraph):
>
>   http://www.sqlite.org/c3ref/progress_handler.html
>
>Dan.

Yeah, I thought as much.  I had a wild quick-and-dirty idea that I thought
better of almost immediately, but it got me curious.

Thanks very much!

Will


>___
>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] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote:

> How did you get concurrent connections? (about)

This is related specifically to the web language I use (PHP) so it won't help 
you if you're using something else.

Actually my system works like this: the web pages themselves are '.html' files 
do most of their work in JavaScript.  When they want to talk to the database 
they use XMLHTTPRequest to call other utility '.php' files which have the job 
of just a single query or execution.  Though they actually talk JSON to 
one-another, not XML.

For concurrency it works fine.  The amount of time a connection takes is very 
small: just long enough to open, set timeout, do a single query/exec, and close 
the connection.  So even with lots of users there aren't that many concurrent 
connections to the database file.

Looking back at the 'Subject' header, I note that I don't use a PRAGMA to 
specify journal mode.  It's using whatever journal mode is default for a 
database created in the sqlite3 shell tool.

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

Re: [sqlite] WAL mode is reliable?

[Paxdo Presse]

Thank you Simon!

How did you get concurrent connections? (about)

Le 3 nov. 2011 à 14:32, Simon Slavin a écrit :

> 
> On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote:
> 
>> What do you think of SQLite with WAL mode? 
>> 
>> Is it reliable? 
>> 
>> It is for a web application. 
>> 
>> The database is hosted on a single computer, but there may be multiple 
>> simultaneous connections via Internet (and LAN).
>> All users access to database via a web browser.
>> 
>> In your opinion, how about concurrents access can handle this database? 
> 
> All my experience with this is using PHP's 'sqlite3' object interface.  That 
> works fine for me (once I put the TimeOutWait up to 1000 milliseconds).  But 
> my server is for internal use only and has hundreds, not tens of thousands of 
> users.
> 
> 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] WAL mode is reliable?

[Simon Slavin]

On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote:

> What do you think of SQLite with WAL mode? 
> 
> Is it reliable? 
> 
> It is for a web application. 
> 
> The database is hosted on a single computer, but there may be multiple 
> simultaneous connections via Internet (and LAN).
> All users access to database via a web browser.
> 
> In your opinion, how about concurrents access can handle this database? 

All my experience with this is using PHP's 'sqlite3' object interface.  That 
works fine for me (once I put the TimeOutWait up to 1000 milliseconds).  But my 
server is for internal use only and has hundreds, not tens of thousands of 
users.

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

[sqlite] WAL mode is reliable?

[Paxdo Presse]

Hi,

What do you think of SQLite with WAL mode? 

Is it reliable? 

It is for a web application. 

The database is hosted on a single computer, but there may be multiple 
simultaneous connections via Internet (and LAN).
All users access to database via a web browser.

In your opinion, how about concurrents access can handle this database? 

Thank you,

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

Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[James Berry]

I've seen this same problem. It crashes seemingly due to bugs in llvm-clang 
when compiled for arm6 if, as you say, optimizations at any level are turned 
on. I've worked around this issue by turning off optimizations for arm6. 
Sqlite3, by the way, is not the only bit of my iOS app that encounters problems 
with that arm6 optimization combination. arm6, fortunately, is required by 
fewer and fewer devices these days.

Report bugs to apple, if you care.

James


On Nov 3, 2011, at 1:39 AM, Price,Ray wrote:

> Hi All,
> 
> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
> application that works fine and has been working find for over a year, but 
> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
> ONLY on older devices still running iOS 3.1.3.
> 
> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
> goes away, but this is obviously FAR from ideal.
> 
> Has anyone else experienced this?  Is there anything I can do to narrow down 
> the problem, or is there anyone I could report this to?
> 
> Thanks
> Ray
> 
> 
> 
> 
> This e-mail message, including any attachments, is for the sole use of the 
> person to whom it has been sent, and may contain information that is 
> confidential or legally protected. If you are not the intended recipient or 
> have received this message in error, you are not authorized to copy, 
> distribute, or otherwise use this message or its attachments. Please notify 
> the sender immediately by return e-mail and permanently delete this message 
> and any attachments. Gartner makes no warranty that this e-mail is error or 
> virus free.
> ___
> 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] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[Simon Slavin]

On 3 Nov 2011, at 8:39am, Price,Ray wrote:

> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
> application that works fine and has been working find for over a year, but 
> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
> ONLY on older devices still running iOS 3.1.3.
> 
> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
> goes away, but this is obviously FAR from ideal.
> 
> Has anyone else experienced this?

First, well done for identifying your workaround.  I use Xcode and iOS and I've 
had situations where compiling without optimization cures crashes.  But they 
haven't involved SQLite code.  I don't know how much optimization improves 
sqlite's speed.  Might help a lot, might not.

One problem with sqlite is that it has to compile on a lot of platforms with a 
lot of compilers and it's impossible to get rid of all warnings under those 
conditions.

If possible play with each of the optimization levels and find out which level 
is causing the problems.  If you can figure out what part of the C code is 
causing the problem, or post an extract from the crash log which gives symbol 
names, someone here might be able to figure out what to fix in the code.

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

Re: [sqlite] Slow INDEX

[nobre]

Have you tried setting journal_mode to the default DELETE option ?
Without atommic commits, maybe your inserts are going to disk one by one
instead of in a single step, when commiting your transactions, thus slowing
down disk writes.


Fabian-40 wrote:
> 
> 2011/11/2 Black, Michael (IS)
> I do not use WAL, since I have turned 'journal_mode' off (to improve
> insert
> performance), and as far as I know WAL is only usefull when you need to
> keep a journal?
> 
> I also have 'synchronous' off, so SQLite shouldn't be waiting for any
> filesystem flushes. I hoped it was writing all the newly inserted rows to
> disk using a single operation, as soon as I call 'commit'. But I observed
> it, and found out its already writing the rows as soon as soon as they are
> inserted, not batching them for when I call commit.
> 
> So that could be part of the problem. I don't have a real Windows machine
> at hand, so I will build one tomorrow, but if your expectations are
> correct, than it will be even slower than inside a virtual machine,
> because
> it will do individiual writes for the 1 million rows too, making
> performance even worse than it is now.
> 
> Anothing thing is that I don't expect the slow performance have anything
> to
> do with slow disk writes, only with disk reads. I know this because when I
> make the index UNIQUE, and try to insert 10.000 duplicate rows (which are
> all ignored), it has the same bad performance, even though there are zero
> bytes written to disk. So it points in the direction of the reads making
> it
> slow, not the writes.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Slow-INDEX-tp32766886p32772266.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

[sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[Price,Ray]

Hi All,

I'm having a VERY odd problem with Sqlite at the moment.  I have an application 
that works fine and has been working find for over a year, but since updating 
to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but ONLY on older 
devices still running iOS 3.1.3.

However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
goes away, but this is obviously FAR from ideal.

Has anyone else experienced this?  Is there anything I can do to narrow down 
the problem, or is there anyone I could report this to?

Thanks
Ray




This e-mail message, including any attachments, is for the sole use of the 
person to whom it has been sent, and may contain information that is 
confidential or legally protected. If you are not the intended recipient or 
have received this message in error, you are not authorized to copy, 
distribute, or otherwise use this message or its attachments. Please notify the 
sender immediately by return e-mail and permanently delete this message and any 
attachments. Gartner makes no warranty that this e-mail is error or virus free.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users