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

[Richard Hipp]

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 file like:
>
>  /var/tmp/etilqs_**gblRd6vUPcx91Hl, the root partition of fills up and an
> error is raised.
>
> Why does SQLite 3.7.14.1 need to write at all when doing union all? It
> seems to me that there is no reason for doing so.
>

The reason for using a temp table for UNION ALL in a subquery is because
that is the path of least resistance.  The same identical code can handle
UNION ALL, UNION, INTERSECT, EXCEPT and various other cases.  Some queries
(such as your UNION ALL) can in fact do without the temp table.  But those
are special cases that have to coded separately.  Adding, testing, and
maintaining that extra code involves a lot of work.  And the extra code
risks introducing bugs that might appear even for people who are not doing
a UNION ALL in a subquery.  And in over 12 years of use, in over a million
different applications, with over 2 billion deployments, nobody has ever
before requested this optimization.

At http://www.sqlite.org/src/info/7af3acbbd4 there is a patch that adds the
optimization to avoid using a temp table for your UNION ALL queries.  This
one small patch represents about 12 hours of intense work, so far.  Much
more work will be required to get the patch performing to our release
standards.  All of this effort on your behalf you are receiving for free.
In return, we ask two things:

(1) Please download and test the patch and report any problems, including
performance problems.

(2) Please learn to be less grumpy, demanding, and condescending when
requesting help with software towards which you have contributed nothing.
You have received this latest patch, and indeed all of SQLite, by grace.
Therefore, please extend the same grace toward others.


>
> Best regards,
>
> lefteris.
> __**_
> 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] Union all writting on /var/tmp documentation warning

[Elefterios Stamatogiannakis]

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 
need it at all.


l.

On 29/10/2012 10:37 μμ, Clemens Ladisch wrote:

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);
   open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
   open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
   open("/tmp", O_RDONLY|O_CLOEXEC)= 5
   sqlite> begin;
   sqlite> insert into t values('long string');
   open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
   sqlite> insert into t select * from t;
   sqlite> insert into t select * from t;
   sqlite> insert into t select * from t;

... that becomes so big that the subquery overflows the cache:

   [...]
   sqlite> insert into t select * from t;
   open("/var/tmp/etilqs_Oekg82a6826YGdz", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
   sqlite> commit;

Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)

   sqlite> explain query plan select * from (select rowid, x from t union all 
select rowid, x from t) where x = '';
   1|0|0|SCAN TABLE t (~10 rows)
   2|0|0|SCAN TABLE t (~10 rows)
   0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
   sqlite> explain query plan select * from (select rowid, x from t union 
select rowid, x from t) where x = '';
   2|0|0|SCAN TABLE t (~100 rows)
   3|0|0|SCAN TABLE t (~100 rows)
   1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
   0|0|0|SCAN SUBQUERY 1 (~20 rows)

When executing them, only UNION uses temporary files:

   sqlite> select * from (select rowid, x from t union all select rowid, x from 
t) where x = '';
   sqlite> select * from (select rowid, x from t union select rowid, x from t) 
where x = '';
   open("/var/tmp/etilqs_QNvTpzSHSedfFFM", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
   open("/var/tmp/etilqs_RiTrAL6vrIxpnOu", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5


Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.


Regards,
Clemens
___
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] Union all writting on /var/tmp documentation warning

[Clemens Ladisch]

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);
  open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
  open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
  open("/tmp", O_RDONLY|O_CLOEXEC)= 5
  sqlite> begin;
  sqlite> insert into t values('long string');
  open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;
  sqlite> insert into t select * from t;

... that becomes so big that the subquery overflows the cache:

  [...]
  sqlite> insert into t select * from t;
  open("/var/tmp/etilqs_Oekg82a6826YGdz", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
  sqlite> commit;

Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)

  sqlite> explain query plan select * from (select rowid, x from t union all 
select rowid, x from t) where x = '';
  1|0|0|SCAN TABLE t (~10 rows)
  2|0|0|SCAN TABLE t (~10 rows)
  0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
  sqlite> explain query plan select * from (select rowid, x from t union select 
rowid, x from t) where x = '';
  2|0|0|SCAN TABLE t (~100 rows)
  3|0|0|SCAN TABLE t (~100 rows)
  1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
  0|0|0|SCAN SUBQUERY 1 (~20 rows)

When executing them, only UNION uses temporary files:

  sqlite> select * from (select rowid, x from t union all select rowid, x from 
t) where x = '';
  sqlite> select * from (select rowid, x from t union select rowid, x from t) 
where x = '';
  open("/var/tmp/etilqs_QNvTpzSHSedfFFM", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
  open("/var/tmp/etilqs_RiTrAL6vrIxpnOu", 
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5


Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.


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

Re: [sqlite] SQLite, HTML5 and Javascript

[Alek Paunov]

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 database. We do not care of 
the database is on a server or local. We just want to put together a simple 
mechanism using javascript as a query interface to an SQLite database.

We have read a bit about WebSQL, Web Storage and IndexedDB. We are a bit 
confused.

Are one of those a requirement to interact with SQLite via Javascript? After a 
reasonable bit of googling we have not found a simple javascript/SQLite example.



I am assuming that you are talking about client side SQLite DBs.

Unfortunately, the sad reality is that Web SQL Database [1] (i.e. SQLite 
presence in any modern browser) has been abandoned as a standard in 
favor of IndexedDB.


The main reasons (cited in various official and semi-official sources by 
the Mozilla guys) are:


 * Internet badly needs browser convergence over HTML5, but Microsoft
   will never include exactly SQLite in IE for Windows, (the proposed
   standard [1] roughly says "WebSQL in terms of query language and
   behavior is ... SQLite 3.6.19")

 * SQL is not the perfect language for the average JS developer.

Mozilla (Firefox) continues to use (quite inefficiently) SQLite as 
IndexedDB backend, Chrome switched to LevelDB recently.


Meantime, you have the following options:

 * Stick with IndexedDB, which do not support any query language - you
   will need to render your queries to low level API calls manually,
   like in the pre-SQL dark ages :-) (but IndexedDB is already supported
   in all recent versions, natively [2] or by shim [3] on top of
   SQLite/WebSQL [4]).

 * Test for a project (and contribute to - e.g. filling bugs) Emscripten
   SQLite [5] (C SQLite code compiled as JS using the new HTML5 typed
   arrays as memory representation).

Sorry,
Alek

P.S. I am keeping the hope, that it is still possible to bring back 
SQLite in the standard JS APIs, will be glad to discuss how we could try 
to achieve the goal if anyone is interested.


[1] http://www.w3.org/TR/webdatabase/
[2] http://caniuse.com/#search=IndexedDB
[3] http://nparashuram.com/IndexedDBShim/
[4] http://caniuse.com/#search=WebSQL
[5] http://syntensity.com/static/sql.html

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

Re: [sqlite] SQLite, HTML5 and Javascript

[Simon Slavin]

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 an existing database. We do not care 
> of the database is on a server or local. We just want to put together a 
> simple mechanism using javascript as a query interface to an SQLite database.
> 
> We have read a bit about WebSQL, Web Storage and IndexedDB. We are a bit 
> confused.
> 
> Are one of those a requirement to interact with SQLite via Javascript? After 
> a reasonable bit of googling we have not found a simple javascript/SQLite 
> example.

Because there's no mechanism that lets JavaScript talk specifically to a SQLite 
database.  The HTML5 specification talks about SQL not specifically SQLite.  
Nevertheless, you may find this useful:



In current technology with pages running from a server, accessing a SQLite 
database is usually done using a shim: a little program running on the server 
which receives an AJAX call, executes an SQLite command, then returns the 
result.  These shims can be written in PHP or any number of other languages 
which can be run within a server process.

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

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

[Eleytherios Stamatogiannakis]

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 buffering is not needed.


lefteris.

On 29/10/12 20:41, Clemens Ladisch wrote:

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 TEMP B-TREE (UNION)
sqlite> explain query plan select 1 union all select 2;
sele  order  from  deta
  -    
0 0  0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

It does not.  (In the full "explain" output, "OpenEphemeral" is missing.)
Neither with real tables.

What particular query behaves unexpectedly for you?


Regards,
Clemens
___
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] SQLite, HTML5 and Javascript

[Rose, John B]

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 server or local. We just want to put together a simple 
mechanism using javascript as a query interface to an SQLite database.

We have read a bit about WebSQL, Web Storage and IndexedDB. We are a bit 
confused.

Are one of those a requirement to interact with SQLite via Javascript? After a 
reasonable bit of googling we have not found a simple javascript/SQLite example.


Could anyone link us to one or more examples of the above?

Thanks
John

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

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

[Clemens Ladisch]

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 TEMP B-TREE (UNION)
sqlite> explain query plan select 1 union all select 2;
sele  order  from  deta
  -    
0 0  0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

It does not.  (In the full "explain" output, "OpenEphemeral" is missing.)
Neither with real tables.

What particular query behaves unexpectedly for you?


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

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

[Alek Paunov]

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 that we're switching to SSDs, I expect they will hum even
better.  What problems would you expect me to be seeing that I can happily
report I'm not, or what problems have I not yet encountered but will -- at
100GB, or 1TB?


In your previous thread (2012-02), you have mentioned that you are about 
to open-source your replication method based on SQL statement 
distribution. Probably your work would be of interest for a huge number 
of sites managing data volumes around or bellow your current level, even 
if you switch to PostgreSQL at this point.


IMHO, there might be a future for your replication model, because I 
think that SQLite, can more easily (relative to other proven DB 
technologies e.g. PostgreSQL) be turned to DB engine for more query 
languages than SQL (thanks to his clever VM design).


Furthermore, AFAIK, PostgreSQL replicates at WAL distribution level, 
most NoSQL databases at keys distribution level, whereas your method 
seems more efficient as bandwidth.


Kind Regards,
Alek

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

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

[Eleytherios Stamatogiannakis]

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 composed.)"

--/SNIP--

At least to my eyes, above says what i was expecting before realizing 
what actually happens, that "union all" tries to not materialize its 
results when possible.


What the truth is, concerning materialization, is that in SQLite "union 
all" works exactly like plain "union". It always materializes its input.


lefteris.

On 29/10/12 16:37, Clemens Ladisch wrote:

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-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] Union all writting on /var/tmp documentation warning

[Clemens Ladisch]

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-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Eleytherios Stamatogiannakis]

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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().  SQLite does a great job for roles such as
> > data storage for a desktop application, or for databases in cellphones or
> > other gadgets, or as a local cache to a enterprise network database.  But
> > SQLite was never designed or intended to replace an enterprise-level
> > RDBMS.  And I think you probably need an enterprise-level RDBMS at this
> > point.
> >
>
> So what specifically do you feel is the problem with sqlite at scale?
>
> And to be clear, I'd like to pre-empt "well it doesn't do X, which you'll
> probably also want to do" -- all those X's are already covered and working
> great.  I just mean, what in particular do you feel about sqlite works
> great for a 50MB database, but doesn't work at a 50GB database?  I'm very
> open to being convinced -- and you'd be the person to convince me.  But I
> don't like to make decisions based on vague fears.  "Best practices" often
> aren't.
>
> 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 that we're switching to SSDs, I expect they will hum even
> better.  What problems would you expect me to be seeing that I can happily
> report I'm not, or what problems have I not yet encountered but will -- at
> 100GB, or 1TB?
>

Generally the argument in favor of client/server databases versus SQLite
comes down to (1) Concurrency, (2) Replication, and (3) Network access.
The size of the database file shouldn't really be a factor.  Or it least it
hasn't as far as we know.  On the other hand, we haven't heard from people
pushing terabyte databases into SQLite before

SQLite supports some concurrency (multiple readers, one writer) but not as
much as typical client/server databases.  SQLite is optimized more for the
single-user case.

SQLite has its backup API, which provides some basic replication
capabilities.  But it isn't the same thing has having a full-blown
real-time replicator like you find in most client/server installations.

And finally, SQLite, really really prefers to have its content on the local
disk.  Any application that uses a database can be conceptually divided
into three pieces:  (A) the application (B) the database engine and (C) the
content on disk.  If you have to cross a network to go between A and C (if
the content is on a different machine from where the application is
running) then it is better to cross that network at the A-B junction rather
than at the B-C junction because the A-B junction requires less bandwidth.
Client/server database engines use the A-B junction whereas SQLite on a
network filesystem uses the B-C junction.


>
> Thanks!
>
> -david
>
> PS: Also, if anybody does know anything about MySQL fragmentation, I'd
> still love some pointers.  I'm not sure how my casual request became
> interpreted as an obsession, but either way, I'd still love the benefit of
> your knowledge.
>
>
>
>
>
> >
> > MySQL is a good choice.  But here is another data point to consider:
>  When
> > we were writing the SqlLogicTest test suite for SQLite, we ran the test
> > vectors on a wide variety of server-class database engines in addition to
> > SQLite.  And in every case (including SQLite) we found cases that would
> > crash the server.  Every case, that is, except one.  We were never able
> to
> > crash PostgreSQL, nor find a case where PostgreSQL gave the wrong answer.
> >
> > Furthermore, whenever there is a question about what the behavior of some
> > obscure SQL construct ought to be and whether or not SQLite is doing it
> > right, usually the first thing we check is how PostgreSQL responds to the
> > same query.  When in doubt, we try to get SQLite to do the same thing as
> > PostgreSQL.
> >
> > Far be it from me to recommend one client/server database engine over
> > another.  But in my experience.  well, you can fill in the rest,
> > probably...
> >
> > On Sun, Oct 28, 2012 at 10:48 AM, David Barrett  > >wrote:
> >
> > > Wow, I didn't realize this was such a controversial question.
> > >
> > > I'm a huge sqlite fan.  Expensify is built on sqlite.  We have a 40GB
> > > database, replicated using our custom distributed transaction layer
> > across
> > > 5 severs in three different datacenters.[1]  It's been powering all of
> > > Expensify (including our direct deposit reimbursement engine and credit
> > > card import layer -- both of which contain incredibly sensitive
> > > information, with mistakes causing millions of dollars to move in the
> > wrong
> > > direction).  On the 

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

[Pavel Ivanov]

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 "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 account. 
> To: "sqlite-users@sqlite.org" 
> Sent: Monday, October 29, 2012 2:33 PM
> Subject: [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=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE "namesReal" (
>   "id" INTEGER PRIMARY KEY AUTOINCREMENT,
>   "name" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
> );
> INSERT INTO "namesReal" VALUES(1,'Linus');
> INSERT INTO "namesReal" VALUES(2,'Bill');
> INSERT INTO "namesReal" VALUES(3,'Steve');
> INSERT INTO "namesReal" VALUES(4,'Richard');
> INSERT INTO "namesReal" VALUES(5,'Ninjas');
> DELETE FROM sqlite_sequence;
> INSERT INTO "sqlite_sequence" VALUES('namesReal',10);
> CREATE VIEW "names" AS SELECT * FROM "namesReal";
> COMMIT;
> -
> At this point selecting from names or namesReal should generate the same data:
> sqlite> .header on
> sqlite> SELECT "id", "name" FROM "namesReal";
> id|name
> 1|Linus
> 2|Bill
> 3|Steve
> 4|Richard
> 5|Ninjas
> -
> The data above is good, the column names, as well as the row values. But look 
> what happens when selecting from the view:
> sqlite> SELECT "id", "name" FROM "names";
> "id"|"name"
> 1|Linus
> 2|Bill
> 3|Steve
> 4|Richard
> 5|Ninjas
> -
> The quotes are being includes in the column names unlike the prior case. 
> However when selecting via wildcard, this happens:
> sqlite> SELECT * FROM "names";
> id|name
> 1|Linus
> 2|Bill
> 3|Steve
> 4|Richard
> 5|Ninjas
> -
>
> It appears from these examples, that SQLite mistakenly? is including the 
> decorations around column names as used by the query for views. Unless I'm 
> mistaken, column names are supposed to be quoted in SQL in order to prevent 
> conflict with reserved words.
>
> When trying to query this database with the API, sqlite3_column_name() 
> includes the quotes around the column name in the second select statement, 
> but not in the first or third. So it seems the issue is with that function, 
> and not some quirk of the command line client.
>
>
> Is this a bug? Or am I doing something wrong?
> ___
> 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] Minor documentation error

[Simon Slavin]

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, but I'm less certain about it.

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

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

[Adam DeVita]

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_ frees the statement.
>>>
>>> And if the statement is already finalized (due to an earlier error,
>>> perhaps) then it is a harmless noop.  So you can do it near the end
>>> of your routine harmlessly.
>>
>> That's a bit deceptive. Passing the same pointer to sqlite3_finalize()
>> twice is undefined behavior. You might get an SQLITE_MISUSE error, but
>> you also might get a segfault.
>
> Oh, right.  It releases the memory the statement was using.  Sorry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 _Situations Where Another RDBMS May Work 
Better_.  Or perhaps something on that page might answer your question.

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

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

[NSRT Mail account.]

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 account. 
To: "sqlite-users@sqlite.org"  
Sent: Monday, October 29, 2012 2:33 PM
Subject: [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=OFF;
BEGIN TRANSACTION;
CREATE TABLE "namesReal" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO "namesReal" VALUES(1,'Linus');
INSERT INTO "namesReal" VALUES(2,'Bill');
INSERT INTO "namesReal" VALUES(3,'Steve');
INSERT INTO "namesReal" VALUES(4,'Richard');
INSERT INTO "namesReal" VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('namesReal',10);
CREATE VIEW "names" AS SELECT * FROM "namesReal";
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite> .header on
sqlite> SELECT "id", "name" FROM "namesReal";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look 
what happens when selecting from the view:
sqlite> SELECT "id", "name" FROM "names";
"id"|"name"
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
- 
The quotes are being includes in the column names unlike the prior case. 
However when selecting via wildcard, this happens:
sqlite> SELECT * FROM "names";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
- 

It appears from these examples, that SQLite mistakenly? is including the 
decorations around column names as used by the query for views. Unless I'm 
mistaken, column names are supposed to be quoted in SQL in order to prevent 
conflict with reserved words.

When trying to query this database with the API, sqlite3_column_name() includes 
the quotes around the column name in the second select statement, but not in 
the first or third. So it seems the issue is with that function, and not some 
quirk of the command line client.


Is this a bug? Or am I doing something wrong?
___
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] sqlite3_column_name() contains quotes for views

[NSRT Mail account.]

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=OFF;
BEGIN TRANSACTION;
CREATE TABLE "namesReal" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO "namesReal" VALUES(1,'Linus');
INSERT INTO "namesReal" VALUES(2,'Bill');
INSERT INTO "namesReal" VALUES(3,'Steve');
INSERT INTO "namesReal" VALUES(4,'Richard');
INSERT INTO "namesReal" VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('namesReal',10);
CREATE VIEW "names" AS SELECT * FROM "namesReal";
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite> .header on
sqlite> SELECT "id", "name" FROM "namesReal";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look 
what happens when selecting from the view:
sqlite> SELECT "id", "name" FROM "names";
"id"|"name"
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
- 
The quotes are being includes in the column names unlike the prior case. 
However when selecting via wildcard, this happens:
sqlite> SELECT * FROM "names";
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
- 

It appears from these examples, that SQLite mistakenly? is including the 
decorations around column names as used by the query for views. Unless I'm 
mistaken, column names are supposed to be quoted in SQL in order to prevent 
conflict with reserved words.

When trying to query this database with the API, sqlite3_column_name() includes 
the quotes around the column name in the second select statement, but not in 
the first or third. So it seems the issue is with that function, and not some 
quirk of the command line client.


Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re; Subrank query

[Clemens Ladisch]

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 produce :
>
> score | rank  |  subrank  |  game
> 98|   1   | 1 |  1615
> 98|   1   | 2 |  1615
> 92|   2   | 1 |  1615
> 87|   3   | 1 |  1615
> 87|   3   | 2 |  1615
> 87|   3   | 3 |  1615
> 112   |   1   | 1 |  1616
> 94|   2   | 1 |  1616
> 94|   2   | 2 |  1616

So, the subrank is the number of records up until the current record
in the same rank:

  SELECT score,
 rank,
 (SELECT count(*)
  FROM scores s2
  WHERE s2.rank = s1.rank
AND s2.game = s1.game
AND s2.rowid <= s1.rowid) AS subrank,
 game
  FROM scores s1


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

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

[David Barrett]

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 a desktop application, or for databases in cellphones or
> other gadgets, or as a local cache to a enterprise network database.  But
> SQLite was never designed or intended to replace an enterprise-level
> RDBMS.  And I think you probably need an enterprise-level RDBMS at this
> point.
>

So what specifically do you feel is the problem with sqlite at scale?

And to be clear, I'd like to pre-empt "well it doesn't do X, which you'll
probably also want to do" -- all those X's are already covered and working
great.  I just mean, what in particular do you feel about sqlite works
great for a 50MB database, but doesn't work at a 50GB database?  I'm very
open to being convinced -- and you'd be the person to convince me.  But I
don't like to make decisions based on vague fears.  "Best practices" often
aren't.

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 that we're switching to SSDs, I expect they will hum even
better.  What problems would you expect me to be seeing that I can happily
report I'm not, or what problems have I not yet encountered but will -- at
100GB, or 1TB?

Thanks!

-david

PS: Also, if anybody does know anything about MySQL fragmentation, I'd
still love some pointers.  I'm not sure how my casual request became
interpreted as an obsession, but either way, I'd still love the benefit of
your knowledge.





>
> MySQL is a good choice.  But here is another data point to consider:  When
> we were writing the SqlLogicTest test suite for SQLite, we ran the test
> vectors on a wide variety of server-class database engines in addition to
> SQLite.  And in every case (including SQLite) we found cases that would
> crash the server.  Every case, that is, except one.  We were never able to
> crash PostgreSQL, nor find a case where PostgreSQL gave the wrong answer.
>
> Furthermore, whenever there is a question about what the behavior of some
> obscure SQL construct ought to be and whether or not SQLite is doing it
> right, usually the first thing we check is how PostgreSQL responds to the
> same query.  When in doubt, we try to get SQLite to do the same thing as
> PostgreSQL.
>
> Far be it from me to recommend one client/server database engine over
> another.  But in my experience.  well, you can fill in the rest,
> probably...
>
> On Sun, Oct 28, 2012 at 10:48 AM, David Barrett  >wrote:
>
> > Wow, I didn't realize this was such a controversial question.
> >
> > I'm a huge sqlite fan.  Expensify is built on sqlite.  We have a 40GB
> > database, replicated using our custom distributed transaction layer
> across
> > 5 severs in three different datacenters.[1]  It's been powering all of
> > Expensify (including our direct deposit reimbursement engine and credit
> > card import layer -- both of which contain incredibly sensitive
> > information, with mistakes causing millions of dollars to move in the
> wrong
> > direction).  On the back of sqlite, we've grown to over million users,
> > processing millions of dollars in expense reports every day.
> >
> > However, we're starting to see problems.  There is so much activity on
> some
> > servers that there is never a chance for our checkpointing thread to do
> its
> > thing, so our WAL file often ballons up to 30GB or more.  This makes
> query
> > times plummet.  We regularly checkpoint manually, and often vacuum, all
> in
> > an effort to keep queries moving quick.  We also do things to trick out
> our
> > indexes in order to ensure proper disk ordering, pay particular attention
> > to block and cache amounts, etc.  This isn't premature optimization for
> the
> > sake of having fun, these are in response to real performance problems
> > affecting our product.
> >
> > In light of that, there is a contingent pushing to drop sqlite in favor
> of
> > MySQL.  There are a wide range of reasons -- it has its own replication,
> > better write concurrency, clustered indexes, and better edge-case data
> > integrity (because we use 2 DBs and WAL, ATTACH doesn't provide atomic
> > commit advantages).  And for each I have a corresponding answer --
> MySQL's
> > replication isn't as good as ours, concurrency doesn't matter because we
> > serialize writes and have a single threaded server anyway, clustered
> > indexes would be nice but we can get close enough with custom ROWIDs, and
> > the extremely rare situation where there's a cross-database integrity
> > problem, we can detect and recover from any of the other slaves.  And I
> > also add in