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

2011-11-03 Thread 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

Re: [sqlite] Compiler Warnings building Win x64 with VS2010

2011-11-03 Thread 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

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

2011-11-03 Thread 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

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

2011-11-03 Thread 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] sql server management studio like tool for SQLite

2011-11-03 Thread 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

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

2011-11-03 Thread 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

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

2011-11-03 Thread 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 =

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

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

2011-11-03 Thread 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

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

2011-11-03 Thread 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 >

[sqlite] Compiler Warnings building Win x64 with VS2010

2011-11-03 Thread 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

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

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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,

Re: [sqlite] Slow INDEX

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] Slow INDEX

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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. > >

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] Progress callback and nested queries

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

Re: [sqlite] WAL mode is reliable?

2011-11-03 Thread 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

[sqlite] WAL mode is reliable?

2011-11-03 Thread 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

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

2011-11-03 Thread 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

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

2011-11-03 Thread 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

Re: [sqlite] Slow INDEX

2011-11-03 Thread 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

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

2011-11-03 Thread 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