Re: [sqlite] Union all writting on /var/tmp

On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis < est...@gmail.com> wrote: > I have been observing the following freaky behaviour of SQLite. When i run: > > select count(*) from (select * from huge_table union all select * from > huge_table); > > Sqlite starts writting in /var/tmp/ a

Re: [sqlite] Union all writting on /var/tmp documentation warning

Sorry i didn't realize before that you had: select * from (... union all ...) Try with a count(*) as such: select count(*) from (... union all ...) And you'll see that both union and "union all" will create a temp file. Union needs the temp file to remove the duplicates. Union All doesn't ne

Re: [sqlite] Union all writting on /var/tmp documentation warning

Eleytherios Stamatogiannakis wrote: > My understanding (and what my experiments have shown) is that in both > cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp. Okay, let's create a test database ... $ strace -e trace=open sqlite3 test.db [...] sqlite> create table t(x)

Re: [sqlite] SQLite, HTML5 and Javascript

On 29.10.2012 20:44, Rose, John B wrote: 1) We are looking for simple examples, with source code, of a UI using HTML5 to query and add data to an SQLite database 2) We are also looking for examples using Javascript to query an existing SQLite database. And maybe add data to an existing databas

Re: [sqlite] SQLite, HTML5 and Javascript

On 29 Oct 2012, at 6:44pm, "Rose, John B" wrote: > 1) We are looking for simple examples, with source code, of a UI using HTML5 > to query and add data to an SQLite database > > 2) We are also looking for examples using Javascript to query an existing > SQLite database. And maybe add data to

Re: [sqlite] Union all writting on /var/tmp documentation warning

My understanding (and what my experiments have shown) is that in both cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp. IMHO, the documentation should warn about this writing behaviour, because for the second case (union all) it isn't expected/predictable because fully bu

[sqlite] SQLite, HTML5 and Javascript

1) We are looking for simple examples, with source code, of a UI using HTML5 to query and add data to an SQLite database 2) We are also looking for examples using Javascript to query an existing SQLite database. And maybe add data to an existing database. We do not care of the database is on a

Re: [sqlite] Union all writting on /var/tmp documentation warning

Eleytherios Stamatogiannakis wrote: > "union all" works exactly like plain "union". It always materializes its > input. sqlite> explain query plan select 1 union select 2; sele order from deta - 0 0 0 COMPOUND SUBQUERIES 1 AND 2 USING

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

Hi David, On 29.10.2012 11:58, David Barrett wrote: Because in practice, as someone actually doing it (as opposed to theorizing about it), it works great. The MySQL portions of our service are always in a semi-constant state of emergency, while our sqlite portions just hum along And given tha

Re: [sqlite] Union all writting on /var/tmp documentation warning

Look at what that page says about "union all" (emphasis on *all*) --SNIP-- "Note that the UNION ALL operator for compound queries does not use transient indices by itself (though of course the right and left subqueries of the UNION ALL might use transient indices depending on how they are comp

Re: [sqlite] Union all writting on /var/tmp documentation warning

Eleytherios Stamatogiannakis wrote: > Can a warning about "union all"'s behaviour of buffering everything in > /var/tmp be added in SQLite's documentation? Like this? http://www.sqlite.org/tempfiles.html Regards, Clemens ___ sqlite-users mailing list

[sqlite] Union all writting on /var/tmp documentation warning

Can a warning about "union all"'s behaviour of buffering everything in /var/tmp be added in SQLite's documentation? I think that such a warning could save a lot of time for other SQLite users that trip over the same thing as i did. Thank you, lefteris. __

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

On Mon, Oct 29, 2012 at 5:58 AM, David Barrett wrote: > On Mon, Oct 29, 2012 at 2:16 AM, Richard Hipp wrote: > > > It sounds like you are pushing SQLite well beyond what it was intended to > > do. Remember the motto: SQLite is not intended to replace Oracle, it is > > intended to replace fopen(

Re: [sqlite] sqlite3_column_name() contains quotes for views

This problem was fixed here http://www.sqlite.org/src/info/5526e0aa3c. It will appear in the next version of SQLite. Pavel On Mon, Oct 29, 2012 at 5:41 AM, NSRT Mail account. wrote: > In the example, I just realized something that makes matters worse. > > sqlite> SELECT "id" AS "id", "name" AS "

[sqlite] Minor documentation error

The page says "just copy the sqlite or sqlite.exe executable" near the bottom. I believe that this should be "just copy the sqlite3 or sqlite3.exe executable". The _Command-line dataset analysis tool_ section may be interpreted as having the same error,

Re: [sqlite] statement prepares OK but step returns SQLITE_NOTADB

Thanks for the clarification. Adam On Sat, Oct 27, 2012 at 12:13 PM, Simon Slavin wrote: > > On 27 Oct 2012, at 6:36am, Dan Kennedy wrote: > >> On 10/27/2012 07:06 AM, Simon Slavin wrote: >>> >>> On 26 Oct 2012, at 11:05pm, Clemens Ladisch >>> wrote: >>> Yes; sqlite3_finalize _always_ fr

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

On 29 Oct 2012, at 9:58am, David Barrett wrote: > So what specifically do you feel is the problem with sqlite at scale? I think it might help if you asked that question with particular reference to the points in particularly the points in the section _S

Re: [sqlite] sqlite3_column_name() contains quotes for views

In the example, I just realized something that makes matters worse. sqlite> SELECT "id" AS "id", "name" AS "name" FROM "names"; id|name 1|Linus 2|Bill 3|Steve 4|Richard 5|Ninjas Despite using a view, using AS seems to remove the quotes. From: NSRT Mail accoun

[sqlite] sqlite3_column_name() contains quotes for views

I believe I ran into a bug with SQLite, and would like to ensure the problem is not on my end. I created a simple table along with a view of it: SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .dump PRAGMA foreign_keys=

Re: [sqlite] Re; Subrank query

Rick Guizawa wrote: > i have a table like: > > score| rank | game > 98 | 1 | 1615 > 98 | 1 | 1615 > 92 | 2 | 1615 > 87 | 3 | 1615 > 87 | 3 | 1615 > 87 | 3 | 1615 > 112 | 1 | 1616 > 94 | 2 | 1616 > 94 | 2 | 1616 > > I want to have a query to pro

Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?

On Mon, Oct 29, 2012 at 2:16 AM, Richard Hipp wrote: > It sounds like you are pushing SQLite well beyond what it was intended to > do. Remember the motto: SQLite is not intended to replace Oracle, it is > intended to replace fopen(). SQLite does a great job for roles such as > data storage for