Re: [sqlite] Hi, new member here (and also my first question)

[benang]

Sorry for the long reply. Been busy with other things. Yeah I initialize my
sqlite3_stmt* pointer in the constructor and de-initialize it in the
destructor. BTW, here's a snippet of my program:

QueryStatement::QueryStatement(sqlite3* dbaseConn, const char* syntax):
m_SQL_syntax(""),
m_QueryStmtHandler(NULL),
m_QueryIndex(0),
m_IsRow(false),
m_IsRowDone(false),
m_IsBoundable(false),
m_IsResetable(false)
{
  int result = prepare(dbaseConn, syntax); // The usual sqlite3_prepare.
Pointer is stored in: m_QueryStmtHandler.
  if (result != SQLITE_OK)
  {
  throw result;
  }

}

QueryStatement::~QueryStatement()
{
finalize(); // The usual sqlite3_finalize.
}

I don't see anything wrong with it IMHO. BTW, the error usually occurs if
I combine between sqlite3_exec and the usual sqlite3_prepare -> step ->
finalize steps in the same method. For example: I use sqlite3_exec with
"CREATE TABLE stamps (name char(100) NOT NULL, thumbName char(100),
category_id int NOT NULL, number_picked FLOAT, PRIMARY KEY( name ),
FOREIGN KEY (category_id) REFERENCES asset_category(id_asset) )" and then
initialize the items inside the table with sqlite3_prepare like this:
"INSERT INTO stamps (name, thumbName, category_id, number_picked) values
(?, ?, ?, ?)", bind the values and use a loop to insert all the items. It
usually crashes during the insertion. But if I close the db and re-opened
it again before preparing the "INSERT" statement, it worked flawlessly.
Weird.

Any idea to trace this bug? Anything would be appreciated because I don't
have a clue whatsoever. Thanks.

Pavel Ivanov wrote:
> Do you initialize your sqlite3_stmt* pointer in constructor? Is there
> any corrupting memory code in other parts of your application?
> You know, it's pretty hard to read and debug your application without
> seeing it. But believe us there's nothing wrong with SQLite and
> sqlite3MemFree(), something wrong with your application and so start
> looking from this point of view.
> You can easily debug the problem as long as you already started
> reading SQLite's code: just look what pointer causes the problem, look
> what value it has at the statement initialization, put breakpoint at
> its change and put breakpoint at sqlite3MemFree with this pointer
> value...
>
>
> Pavel
>
> On Tue, Oct 13, 2009 at 11:46 PM,   wrote:
>> Oh yeah, I forgot to tell you that I'm using Visual C++ 2008
>> professional
>> and it always crashes at this:
>>
>> C:\Program Files\Microsoft Visual Studio 9.0\VC\crt\src\dbgheap.c -
>> function "_free_dbg_nolock", line 1317:
>>        /*
>>         * If this ASSERT fails, a bad pointer has been passed in. It may
>> be
>>         * totally bogus, or it may have been allocated from another
>> heap.
>>         * The pointer MUST come from the 'local' heap.
>>         */
>>        _ASSERTE(_CrtIsValidHeapPointer(pUserData));
>>
>>
>>
>> ben...@cs.its.ac.id wrote:
>>> Well, I'm pretty sure I haven't. FYI, I wrapped the sqlite3_stmt into a
>>> class and only call its sqlite3_finalize on its destructor. So there's
>>> no
>>> way that it would be called twice. Or so I think.
>>>
>>> Pavel Ivanov wrote:
> The pPrior or p pointer isn't null so it should've been
> freed without error IMHO. Can anybody tell me what's wrong with it?
> Thanks
> a lot in advance.

 If "pPrior or p pointer" isn't null but was already freed then double
 free can cause segmentation fault. In other words most probably you're
 calling sqlite3_finalize on already finalized statement.

 Pavel

 On Tue, Oct 13, 2009 at 5:58 AM,   wrote:
> Hi there, I'm a new member of the mailing list. Nice to meet you all.
>
> BTW, I've got one problem that's been bugging me for weeks.
>
> Occasionally (not always), I got a seg fault at "static void
> sqlite3MemFree(void *pPrior)". It happened when I do sqlite3_reset or
> sqlite3_finalize. The pPrior or p pointer isn't null so it should've
> been
> freed without error IMHO. Can anybody tell me what's wrong with it?
> Thanks
> a lot in advance.
>
>
>>
>>
>> Fare thee well,
>> Bawenang R. P. P.
>>
>> 
>> "If a picture is worth a thousand words, an animations is worth a
>> thousand
>> pictures. And to take that a step further, a game is worth a thousand
>> animations." – Peter Raad, Executive Director, The Guildhall at SMU
>>
>>
>> --
>>
>> http://www.its.ac.id
>> ___
>> 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
>


Fare thee well,
Bawenang R. P. P.


"If a 

Re: [sqlite] SQLite encription

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> It happened again.  DRH explained to me last time I asked.  If someone  
> posts from an address that isn't on this list, there's a delay before  
> the post shows up because it waits for moderator approval.  In the  
> meantime the original poster often posts the same question again.

It didn't happen again - yes I checked before responding.  The original post
made it to the list several days ago.  This post was a brand new one - look
at the headers and you can see.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrb3BkACgkQmOOfHg372QTwZgCgoUnLkoaB0jEgCiGd0kAvi+pH
oQIAoLc/iTrQ3oP6HIbMo/7frlOS5RTo
=WQYU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite encription

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Francisc Romano wrote:
> Is it possible to encript SQLite databases so that they cannot be referenced
> outside the program that uses it?
> If not, can you set a user and password for a SQLite database?

The answer has not changed from when you asked a few days ago.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrbtgkACgkQmOOfHg372QQ93wCgliiiP+VGTvLLPVK5Lg6gVlZQ
FR0An1bKG+4jzHQHdVgknuN/V0Hedii0
=bLiU
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Olaf Schmidt]

"Ron Arts"  schrieb im
Newsbeitrag news:4adac5c1.5010...@arts-betel.org...

> Then my program opens a socket, and starts accepting connections,
> those connections are long lasting, and send messages that need
> a fast reply. Many of the messages result in messages being send
> to all other clients. The messages require on average 10 lookups
> in the memory db, each by oid.

Is the "socket-listener-thread" already decoupled from the
thread which hosts your sqlite-connection-handle?

If not done already, you should try it (that will not speedup
the sqlite-performance, but the overall-performance of your
"broadcasting-dispatcher-app").
Additionally you should decouple the "sqlite-thread" also from
the "reply-sender-threads" (placing the sqlite-query-results
in some structures, where the sender-threads are able to find
them).

That would ensure, that the sqlite-engine can always run
fullspeed, not waiting for potentially "slow, or blocking
socket-transfers".

In such a design you could also try another thing, which
maybe speeds up your selects - meaning, maybe "oID-
aggregation" can help.

If you receive in your socket-listener-thread  approx.
5 requests per second (and nothing will intermit this
receiver-thread now, since sqlite-queries run elsewhere) ...
then we talk about 50 incoming messages per milisecond.
Now, since the sqlite-thread is running elsewhere already
... why not aggregate the incoming oIDs in a comma-
separated list (in a simple charbuffer, shared with the
sqlite-thread - and flagged with a "next-job-descriptor").

Each 1 msec (to keep the latency low), you should end
gathering oIDs in such a "next-job" charbuffer and set
the finalized-flag in the job-descriptor-structure (after
that you could start gathering oIDs in your listener-thread
on a different charbuf-allocation immediately).

The sqlite-thread should look for new, flagged as "ready to
proceed" charbuffers on its own, and start its work in a more
"aggregated fashion" then - and maybe the engine-overhead
gets a bit reduced, if sqlite now performs *one* (larger)
select (only each 1 msec), but returning more than only
one single record in its step-loop then.
i.e. per:
Select * from Table Where oID In YourGathered_IDList

Just an idea - I've not yet tested here, if the throughput
would be better this way instead of performing single-record-
selects only ... you probably lose the advantage of the
precompiled "single-record-statement", but could gain
over all, as soon as you reach the step-loop, which does
then more than just one record with probably less overhead
overall.

Maybe that worth' a try.

Olaf



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

Re: [sqlite] "x NATURAL JOIN x" BUG

[Darren Duncan]

Kristoffer Danielsson wrote:
> NATURAL INNER JOIN yields a cartesian product too. Just tried it!

Well then, *surely* that at least has to be a SQLite bug.

That said, it would be useful for clarity if you posted to the list exactly how 
you wrote your NATURAL INNER, what the exact SQL you used was, with test data 
and result, so we know you didn't just make a typo in your test.

Use the simplest possible example that illustrates the point, such as with 
Jay's 
example.

And I would expect NATURAL JOIN to default to INNER even if JOIN defaults to 
CROSS, just because that makes the most sense.  When people specify NATURAL, 
then what they expect is INNER semantics in the general case.

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

[sqlite] SQLite encription

[Francisc Romano]

 Hello!

Is it possible to encript SQLite databases so that they cannot be referenced
outside the program that uses it?
If not, can you set a user and password for a SQLite database?

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

Re: [sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

NATURAL INNER JOIN yields a cartesian product too. Just tried it!
 
> Date: Sun, 18 Oct 2009 15:54:11 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
> 
> Jay A. Kreibich wrote:
> > On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched 
> > on the wall:
> >> Clearly, SQLite executes a cartesian product!
> > 
> > Look at the output. It does not produce a Cartesian product. All
> > the rows are valid:
> > 
> > SQLite version 3.6.19
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table t (c1, c2);
> > sqlite> insert into t values ( 1, 2 );
> > sqlite> insert into t values ( 3, 4 );
> > sqlite> insert into t values ( 5, 6 );
> > sqlite> select * from t natural join t; 
> > 1|2
> > 1|2
> > 1|2
> > 3|4
> > 3|4
> > 3|4
> > 5|6
> > 5|6
> > 5|6
> > 
> > I'm not sure I'd call it correct, but it isn't a product.
> 
> Jay, you've just proven Kristoffer's point. That result demonstrates that a 
> cartesian product *was* produced. The table t had 3 rows, and the result had 
> 3*3 rows, which is a cartesian product by definition.
> 
> Your query should have produced the same result as this query:
> 
> select t1.* from t as t1 inner join t as t2 using (c1,c2);
> 
> ... but instead it produced the same result as this query:
> 
> select t1.* from t as t1 cross join t as t2 using (c1,c2);
> 
> Now I would be ready to consider that SQLite has a bug, but then looking at 
> the 
> syntax at http://sqlite.org/lang_select.html I see that SQLite defines 
> multiple 
> versions of natural join; it has *both* NATURAL INNER JOIN and NATURAL CROSS 
> JOIN, and I'm guessing that if you leave the middle word out it is using 
> CROSS 
> by default, ostensibly for consistency for when you simply say JOIN.
> 
> So if that is the case, then the current behavior is clearly documented as 
> expected and so not an implementation bug. And so then you would have to say 
> this:
> 
> select * from t natural inner join t;
> 
> ... to get the expected result of 3 rows.
> 
> This all being said, the whole mess strikes me as a *design bug*. It simply 
> doesn't make sense to have both NATURAL INNER and NATURAL CROSS syntax. One 
> should simply be able to say NATURAL and it would do the right thing, which 
> is a 
> cartesian product when no column names are the same, an intersect when all 
> column names are the same, and an inner join otherwise.
> 
> My proposal is certainly logically sound. A natural join by definition only 
> has 
> a result row for each distinct pair of source rows that have the same values 
> for 
> the subset of their columns with the same names; a cartesian product is a 
> degenerate case where that subset of columns has zero members, and so since 
> the 
> empty set matches the empty set every row from each source rowsets would 
> match 
> every row from the other rowsets.
> 
> The only variations that make sense on a natural join is OUTER.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på 
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Infinity

[John Crenshaw]

SQLite stores the data however you give it. I'm not aware of any
documentation requiring that 9e999 be considered infinity, nor any
requiring that the command line treat invalid numbers as null. Most
likely, treating NaN as null is simply a way for the command line to
behave reasonably in an otherwise undefined situation.

Practically, 9e999 is beyond the "infinity" limit for doubles on
whatever compiler was used to build the command line. I think this limit
is technically arbitrary, so on some compilers, either now, or in the
future, 9e999 could very possibly NOT be infinity.
std::numeric_limits::max() should be a standard (read "safe")
way of getting the "infinity" value in C++.

In the Visual C++ 2005 compiler, the max double is
1.7976931348623158e+308. I'm not sure that this is constant however, so
don't count on it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen
Sent: Sunday, October 18, 2009 4:19 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Infinity


 I am looking for the answer too. anybody know it?

On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop 
wrote:

> I've noticed that I can use IEEE Infinity values in SQLite by writing
> any literal too big for a double.
>
> sqlite> CREATE TABLE foo (x REAL);
> sqlite> INSERT INTO foo VALUES (9e999); -- +Inf
> sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf
> sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted
> to NULL
> sqlite> .null NULL
> sqlite> select * FROM foo;
> Inf
> -Inf
> NULL
> sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999;
> Inf
> -Inf
>
> Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS
> TEXT) = 'Inf'?  What's the preferred SQL syntax for infinity?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
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] "x NATURAL JOIN x" BUG

[Darren Duncan]

Jay A. Kreibich wrote:
> On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched on 
> the wall:
>> Clearly, SQLite executes a cartesian product!
> 
>   Look at the output.  It does not produce a Cartesian product.  All
>   the rows are valid:
> 
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t (c1, c2);
> sqlite> insert into t values ( 1, 2 );
> sqlite> insert into t values ( 3, 4 );
> sqlite> insert into t values ( 5, 6 );
> sqlite> select * from t natural join t; 
> 1|2
> 1|2
> 1|2
> 3|4
> 3|4
> 3|4
> 5|6
> 5|6
> 5|6
> 
>   I'm not sure I'd call it correct, but it isn't a product.

Jay, you've just proven Kristoffer's point.  That result demonstrates that a 
cartesian product *was* produced.  The table t had 3 rows, and the result had 
3*3 rows, which is a cartesian product by definition.

Your query should have produced the same result as this query:

   select t1.* from t as t1 inner join t as t2 using (c1,c2);

... but instead it produced the same result as this query:

   select t1.* from t as t1 cross join t as t2 using (c1,c2);

Now I would be ready to consider that SQLite has a bug, but then looking at the 
syntax at http://sqlite.org/lang_select.html I see that SQLite defines multiple 
versions of natural join; it has *both* NATURAL INNER JOIN and NATURAL CROSS 
JOIN, and I'm guessing that if you leave the middle word out it is using CROSS 
by default, ostensibly for consistency for when you simply say JOIN.

So if that is the case, then the current behavior is clearly documented as 
expected and so not an implementation bug.  And so then you would have to say 
this:

   select * from t natural inner join t;

... to get the expected result of 3 rows.

This all being said, the whole mess strikes me as a *design bug*.  It simply 
doesn't make sense to have both NATURAL INNER and NATURAL CROSS syntax.  One 
should simply be able to say NATURAL and it would do the right thing, which is 
a 
cartesian product when no column names are the same, an intersect when all 
column names are the same, and an inner join otherwise.

My proposal is certainly logically sound.  A natural join by definition only 
has 
a result row for each distinct pair of source rows that have the same values 
for 
the subset of their columns with the same names; a cartesian product is a 
degenerate case where that subset of columns has zero members, and so since the 
empty set matches the empty set every row from each source rowsets would match 
every row from the other rowsets.

The only variations that make sense on a natural join is OUTER.

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

Re: [sqlite] Infinity

[Michael Chen]

 I am looking for the answer too. anybody know it?

On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop  wrote:

> I've noticed that I can use IEEE Infinity values in SQLite by writing
> any literal too big for a double.
>
> sqlite> CREATE TABLE foo (x REAL);
> sqlite> INSERT INTO foo VALUES (9e999); -- +Inf
> sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf
> sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted
> to NULL
> sqlite> .null NULL
> sqlite> select * FROM foo;
> Inf
> -Inf
> NULL
> sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999;
> Inf
> -Inf
>
> Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS
> TEXT) = 'Inf'?  What's the preferred SQL syntax for infinity?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards,
Michael Chen
Google Voice Phone.: 847-448-0647
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTERSECT?

[Olaf Schmidt]

"Igor Tandetnik"  schrieb im
Newsbeitrag news:hbfjnu$v...@ger.gmane.org...
> Olaf Schmidt wrote:
> > Just to add another one to the pile, any flaws I overlooked here...?
> >
> > select *, count(b) c_b from foo
> > where b in (...)
> > group by a
> > having c_b = length_of_b_list
>
> The OP apparently wanted actual (a, b) pairs for those a's
> that satisfy the condition, not just a list of a's.

Oops, I had only the OPs:

"I want the values of 'a' for which 'b' = 3 AND 'b' = 4"

in mind - and forgot about the additional "b-column-listing"
(which nonetheless should be somehow "redundant", since all
 the possible values of b were already explicitely specified as
 the filter-condition).

maybe that small enhancement does it, to deliver the
given condition back to the caller, contained within the resultset:

select a, group_concat(b) from foo where b in (3, 4)
group by a having count(b) = 2

as said, returning the b-values would be redundant in this
special case - maybe more interesting for "app-usage" would
be this slightly changed variant:

select a, group_concat(RowID) from foo where b in (3, 4)
group by a having count(b) = 2

Olaf



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

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Clark Christensen]

>Ajax is always asynchronous. That is what the first "A" in Ajax is.

Well, not quite always.  The open() method of the XMLHttpRequest object lets 
you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
it's async.  Good choice.  Love jQuery.

>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>when the script finishes. Don't have any issues other than during
>these Ajax calls.

I agree.  It should.  But I wonder if it takes more time for the forked Perl 
process to clean-up and close than you're expecting.  I never did figure out 
the exact cause and effect.  I just $dbh->disconnect (before printing the 
response where appropriate) and the issue usually seems to resolve itself.



>I don't have any timeout set at all. Roger Binns suggestion a
>busy_timeout, but I am not sure how to even use that. I am using DBI.
>Where do I set sqlite busy_timeout? Besides, how would that help me?

If you're currently set to a short value, lengthening it might help narrow-down 
the real culprit (or prove a timing issue).

DBD-SQLite adds this driver private method

   $dbh->func(  'busy_timeout' );  # getter
   $dbh->func( $ms, 'busy_timeout' );  # setter

I seem to think it defaults to 30 seconds, but I don't see it documented now.

If you move the second AJAX request from the onBlur event, to an
explicit user click (button, link, etc), do you still get the locked
DB?  If not, I think it would help prove it's a timing issue (or not).



- Original Message 
From: P Kishor 
To: General Discussion of SQLite Database 
Sent: Sat, October 17, 2009 8:07:09 PM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen  wrote:
> Sorry for top-posting...
>
> What's running on the the server?  A Perl CGI script?  Apache HTTPD?  
> mod-perl?

Although I have Apache mod_perl installed, I am running a plain
vanilla Perl cgi script for now.

>
> Is the AJAX exchange asyncronous?

Ajax is always asynchronous. That is what the first "A" in Ajax is.

> Are you sure the first AJAX exchange is finished when the second one fires?

Am I sure? Not really. I guess it is not finished when the second one
fires, which is what causes the database lock, no?

> Does the AJAX request wait for a 200 response?

I am using jQuery. It does what it does. I don't do anything special.

>
> Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
> opposed to $dbh = undef)?  Does the script end with an exit instruction?

I am not doing $dbh->disconnect. DBI is supposed to that automatically
when the script finishes. Don't have any issues other than during
these Ajax calls.

>  I'm guessing you're sure there's no writer or writers that jumped-in.

I am sure no other process is interfering other than what I have
specified. I am sure because I am the only one using my laptop on
which I am doing the development. :-)


> Are you doing anything like modifying the default 30 second busy_timeout?  
> Perhaps to too short a timeout?

I don't have any timeout set at all. Roger Binns suggestion a
busy_timeout, but I am not sure how to even use that. I am using DBI.
Where do I set sqlite busy_timeout? Besides, how would that help me?

>
> Sometimes it's useful to $dbh->disconnect before you print the response.
>
> I've seen this happen from time to time in my own environment.  It's annoying 
> as hell.   It seems to always come down to when and how I disconnect from the 
> DB file.
>
> I seldom see it in regular CGI scripts.  More often, I see it with 
> mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
> module where $dbh is exported into the main package.

No, as I described, what I think is happening is that two Ajax events
are hitting the db at the nearly the same time. The first one is a
select (checking the db if the user exists), and the second is an
insert (creating a user because the user didn't exist). Even though
Javascript has gotten the response from the first event, and started
on the second process. sqlite is still busy from the first process, so
throws a lockout.

Yes, it is very annoying. I have to figure out some other shenanigans
to prevent this from happening.

>
>  -Clark
>
>
>
> - Original Message 
> From: P Kishor 
> To: General Discussion of SQLite Database 
> Sent: Fri, October 16, 2009 12:53:28 PM
> Subject: [sqlite] suggestions for avoiding "database locked" on ajax
>
> Yes, I know, if it "hurts when I press here," then I shouldn't "press
> here," but, so it goes...
>
> I have an account creation form -- users enter their email and their
> desired username, and the form --
>
> onblur from the username field, sends off an ajax request to see if
> the desired username already exists in the db, and if yes, it sends
> back a suggestion;
>
> onblur 

Re: [sqlite] "x NATURAL JOIN x" BUG

[Jay A. Kreibich]

On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched on 
the wall:


> Blistering fast:
> SELECT COUNT(*) FROM Item;
> Result: 1
>
> Slow! WRONG result:
> SELECT COUNT(*) FROM Item NATURAL JOIN Item;
> Result: 1
> 
> Clearly, SQLite executes a cartesian product!

  Look at the output.  It does not produce a Cartesian product.  All
  the rows are valid:

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (c1, c2);
sqlite> insert into t values ( 1, 2 );
sqlite> insert into t values ( 3, 4 );
sqlite> insert into t values ( 5, 6 );
sqlite> select * from t natural join t; 
1|2
1|2
1|2
3|4
3|4
3|4
5|6
5|6
5|6

  I'm not sure I'd call it correct, but it isn't a product.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 7:23pm, Ron Arts wrote:

> because the application is evolving, columns
> get added/changed on a regular basis. Currently this means adding
> the new columns to my C-structures, writing access functions, and
> recompiling. I don't want to do that, because this means my appl  
> *must*
> be replaced on every database change, and I'd like to be able to
> run different versions of it in the wild. I was hoping to make it
> more dynamic and adaptive to database layout changes.

Then what you need is a flexible system that can cope with schema and  
index changes.  And that's SQLite.  Whatever you write yourself, if it  
has to have the same flexibility as SQLIte does, won't be much faster  
than SQLite.

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Kees Nuyt]

On Sun, 18 Oct 2009 17:37:57 +0200, 
Ron Arts  wrote:

>Very true Simon,
>
>this has been the fastest way so far and I can do around
>35 selects/second this way, using prepared statements
>(on my machine at least), but I need more speed.
>
>That's why I want to skip the SQL processing entirely
>and write a C function that reaches directly into the
>internal memory structures to gets my record from there.

You will have to implement BTree code to walk the index
pages. You'll have a hard time to make your code more
efficient than the SQLite BTree code.

Luckily the BTree code and the Virtual machine interpreter
are in the SQLite source repository, which is public domain.
EXPLAIN SELECT colX FROM tableY WHERE tableY.id = your key;
yields the VM instructions.

So, yes, you can do it.

Note: the SQLite BTree code may change without notice,
because it isn't a public API.

In your case I'd either go for a hash table, without SQL, or
solve the speed problem with hardware.

>thanks,
>Ron
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

P Kishor schreef:
> On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
>> Very true Simon,
>>
>> this has been the fastest way so far and I can do around
>> 35 selects/second this way, using prepared statements
>> (on my machine at least), but I need more speed.
>>
>> That's why I want to skip the SQL processing entirely
>> and write a C function that reaches directly into the
>> internal memory structures to gets my record from there.
> 
> I might have missed the discussion, but... why don't you ditch SQLite
> and use something like Berkeley DB? Sounds to me you need a hash db
> instead of an rdbms, especially since you have no need for SQL.
> 

Well, that's what I use at the moment (I use glib2 g_hash_table()),
but now the requirement to accept SQL queries for that database
has come up. And I don't want to write my own SQL parser..

Next I tried to add a virtual table driver to SQLite for my database.
That worked, but because the application is evolving, columns
get added/changed on a regular basis. Currently this means adding
the new columns to my C-structures, writing access functions, and
recompiling. I don't want to do that, because this means my appl *must*
be replaced on every database change, and I'd like to be able to
run different versions of it in the wild. I was hoping to make it
more dynamic and adaptive to database layout changes.

Thanks,
Ron


> 
>> thanks,
>> Ron
>>
>> Simon Slavin schreef:
>>> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
>>>
 Is there a way to bypass the virtual machine altogether and reach
 directly
 into the btree and just retrieve one record by it's oid (primary
 integer key),
 and return it in a form that would allow taking out the column
 values by name?
>>> The primary integer key column can always be referred to as the
>>> special name ROWID, even if you have assigned it a column name of your
>>> own.  So you can do
>>>
>>> SELECT ROWID,myCol1,myCol2 FROM myTable
>>>
>>> as long as you don't explicitly declare a primary integer key column
>>> and then change the values in it.
>>>
>>> Simon.
> 
> 
> 
> 
> 

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

Re: [sqlite] INTERSECT?

[Igor Tandetnik]

Olaf Schmidt wrote:
> Just to add another one to the pile, any flaws I overlooked here...?
> 
> select *, count(b) c_b from foo
> where b in (...)
> group by a
> having c_b = length_of_b_list

The OP apparently wanted actual (a, b) pairs for those a's that satisfy the 
condition, not just a list of a's.

Igor Tandetnik


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

Re: [sqlite] INTERSECT?

[Olaf Schmidt]

"Pavel Ivanov"  schrieb im
Newsbeitrag
news:f3d9d2130910170753k6e680ecdtcb892f05b21cc...@mail.gmail.com...

> > select * from foo f1 where
> > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a
and f2.b in (...) )) =
> >length_of_b_list
> > and b in (...);

> Shouldn't this be simplified like this?
>
> select * from foo f1 where
> (select count(distinct b) from foo f2 where f1.a = f2.a and f2.b in
(...) ) =
>length_of_b_list
> and b in (...);

Just to add another one to the pile, any flaws I overlooked here...?

select *, count(b) c_b from foo
where b in (...)
group by a
having c_b = length_of_b_list

Olaf



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

Re: [sqlite] getting hung up on LEFT JOIN

[P Kishor]

On Sun, Oct 18, 2009 at 12:06 PM, Igor Tandetnik  wrote:
> P Kishor wrote:
>> On Sun, Oct 18, 2009 at 11:49 AM, Igor Tandetnik  wrote:
>>> P Kishor wrote:
 Given a specific user_id (say, user_id = 1) how on earth do I get the 
 following?
 idea_id idea created_by_name rating
 -- --- --- --
 1 free coffee jim 100
 2 long breaks jim 100
 3 quit early joe NULL
>>>
>>> Describe in plain English precisely what you are trying to achieve. It's 
>>> not obvious to me what question this resultset is the
>>> answer for.
>>>
>>
>>
>> Given a specific user, show me all the ideas, and, if the specific
>> user has voted for an idea, show me the rating given to that idea by
>> that user.
>>
>> So, in the above result set, all three ideas are shown, and Jim's
>> ratings are shown. Since Jim didn't rate idea 3, 'NULL' is returned.
>
> Did too:
>
> INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 3, 50);
>

That was a typo on my part.


> Anyway:
>
> select * from ideas left join users_ideas
>    on (ideas.idea_id = users.idea_id and users.user_id = 1);
>

Thanks. The above works.


> Igor Tandetnik
>
>




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

Re: [sqlite] getting hung up on LEFT JOIN

[Igor Tandetnik]

P Kishor wrote:
> On Sun, Oct 18, 2009 at 11:49 AM, Igor Tandetnik  wrote:
>> P Kishor wrote:
>>> Given a specific user_id (say, user_id = 1) how on earth do I get the 
>>> following?
>>> idea_id idea created_by_name rating
>>> -- --- --- --
>>> 1 free coffee jim 100
>>> 2 long breaks jim 100
>>> 3 quit early joe NULL
>> 
>> Describe in plain English precisely what you are trying to achieve. It's not 
>> obvious to me what question this resultset is the
>> answer for. 
>> 
> 
> 
> Given a specific user, show me all the ideas, and, if the specific
> user has voted for an idea, show me the rating given to that idea by
> that user.
> 
> So, in the above result set, all three ideas are shown, and Jim's
> ratings are shown. Since Jim didn't rate idea 3, 'NULL' is returned.

Did too:

INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 3, 50);

Anyway:

select * from ideas left join users_ideas
on (ideas.idea_id = users.idea_id and users.user_id = 1);

Igor Tandetnik


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

Re: [sqlite] getting hung up on LEFT JOIN

[P Kishor]

On Sun, Oct 18, 2009 at 11:49 AM, Igor Tandetnik  wrote:
> P Kishor wrote:
>> Given a specific user_id (say, user_id = 1) how on earth do I get the 
>> following?
>> idea_id     idea         created_by_name  rating
>> --  ---  ---  --
>> 1           free coffee  jim              100
>> 2           long breaks  jim              100
>> 3           quit early   joe              NULL
>
> Describe in plain English precisely what you are trying to achieve. It's not 
> obvious to me what question this resultset is the answer for.
>


Given a specific user, show me all the ideas, and, if the specific
user has voted for an idea, show me the rating given to that idea by
that user.

So, in the above result set, all three ideas are shown, and Jim's
ratings are shown. Since Jim didn't rate idea 3, 'NULL' is returned.


Since I couldn't figure out a SQL for the above, for now, I am pulling
all the ideas, then pulling all the ratings that belong to "Jim", then
re-upping the ideas result set with Jim's ratings in a Perl sub.

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

Re: [sqlite] Why FTS3 has the limitations it does

[Roger Binns]

Wanadoo Hartwig wrote:
> Slightly different question but related to FTS3. Does anybody know why  
> this fails using FTS3?

It isn't failing.  Behind the scenes FTS3 is implemented using 3 other
tables (try .dump to see).  You are indeed seeing the last inserted rowid.

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

Re: [sqlite] getting hung up on LEFT JOIN

[Igor Tandetnik]

P Kishor wrote:
> Given a specific user_id (say, user_id = 1) how on earth do I get the 
> following?
> idea_id idea created_by_name  rating
> --  ---  ---  --
> 1   free coffee  jim  100
> 2   long breaks  jim  100
> 3   quit early   joe  NULL

Describe in plain English precisely what you are trying to achieve. It's not 
obvious to me what question this resultset is the answer for.

Igor Tandetnik

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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[P Kishor]

On Sun, Oct 18, 2009 at 10:37 AM, Ron Arts  wrote:
> Very true Simon,
>
> this has been the fastest way so far and I can do around
> 35 selects/second this way, using prepared statements
> (on my machine at least), but I need more speed.
>
> That's why I want to skip the SQL processing entirely
> and write a C function that reaches directly into the
> internal memory structures to gets my record from there.

I might have missed the discussion, but... why don't you ditch SQLite
and use something like Berkeley DB? Sounds to me you need a hash db
instead of an rdbms, especially since you have no need for SQL.


>
> thanks,
> Ron
>
> Simon Slavin schreef:
>> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
>>
>>> Is there a way to bypass the virtual machine altogether and reach
>>> directly
>>> into the btree and just retrieve one record by it's oid (primary
>>> integer key),
>>> and return it in a form that would allow taking out the column
>>> values by name?
>>
>> The primary integer key column can always be referred to as the
>> special name ROWID, even if you have assigned it a column name of your
>> own.  So you can do
>>
>> SELECT ROWID,myCol1,myCol2 FROM myTable
>>
>> as long as you don't explicitly declare a primary integer key column
>> and then change the values in it.
>>
>> Simon.





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] getting hung up on LEFT JOIN

[P Kishor]

CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT
);

INSERT INTO users (username) VALUES ('jim');
INSERT INTO users (username) VALUES ('bob');
INSERT INTO users (username) VALUES ('joe');

CREATE TABLE ideas (
idea_id INTEGER PRIMARY KEY,
idea TEXT,
created_by INTEGER
);

INSERT INTO ideas (idea, created_by) VALUES ('free coffee', 1);
INSERT INTO ideas (idea, created_by) VALUES ('long breaks', 1);
INSERT INTO ideas (idea, created_by) VALUES ('quit early', 3);

CREATE TABLE users_ideas (
user_id INTEGER,
idea_id INTEGER,
rating INTEGER,
PRIMARY KEY (user_id, idea_id)
);

INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 1, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 2, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (3, 3, 100);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (1, 3, 50);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (3, 1, 25);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (2, 1, 75);
INSERT INTO users_ideas (user_id, idea_id, rating) VALUES (2, 2, 85);

SELECT i.idea_id, i.idea, u.username AS created_by_name FROM ideas i
JOIN users u ON i.created_by = u.user_id;
idea_id idea created_by_name
--  ---  ---
1   free coffee  jim
2   long breaks  jim
3   quit early   joe


SELECT i.idea_id, i.idea, u.username AS created_by_name FROM ideas i
JOIN users u ON i.created_by = u.user_id WHERE u.user_id = 1;
idea_id idea created_by_name
--  ---  ---
1   free coffee  jim
2   long breaks  jim

SELECT i.idea_id, i.idea, u.username AS created_by_name, rating FROM
ideas i JOIN users u ON i.created_by = u.user_id JOIN users_ideas ui
ON i.created_by = ui.user_id WHERE u.user_id = 1;
idea_id idea created_by_name  rating
--  ---  ---  --
1   free coffee  jim  100
1   free coffee  jim  100
1   free coffee  jim  50
2   long breaks  jim  100
2   long breaks  jim  100
2   long breaks  jim  50

SELECT i.idea_id, i.idea, u.username AS created_by_name, rating FROM
ideas i JOIN users u ON i.created_by = u.user_id LEFT JOIN users_ideas
ui ON i.created_by = ui.user_id WHERE u.user_id = 1;
idea_id idea created_by_name  rating
--  ---  ---  --
1   free coffee  jim  100
1   free coffee  jim  100
1   free coffee  jim  50
2   long breaks  jim  100
2   long breaks  jim  100
2   long breaks  jim  50


Given a specific user_id (say, user_id = 1) how on earth do I get the following?
idea_id idea created_by_name  rating
--  ---  ---  --
1   free coffee  jim  100
2   long breaks  jim  100
3   quit early   joe  NULL



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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 4:37pm, Ron Arts wrote:

> I want to skip the SQL processing entirely
> and write a C function that reaches directly into the
> internal memory structures to gets my record from there.

I assume that you've already tested the fastest way of doing this that  
the standard library allows: prebinding using _prepare, etc..

You could download the source code for SQLite, take a look at how the  
SELECT command is implemented, and write your own customised code  
which knows exactly which fields and index you want and how your table  
is structured.  I have no idea what the results would look like or  
whether the developer forum would be a better place to discuss it.   
Start by using EXPLAIN to look at the code generated from your SELECT  
command.




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

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Very true Simon,

this has been the fastest way so far and I can do around
35 selects/second this way, using prepared statements
(on my machine at least), but I need more speed.

That's why I want to skip the SQL processing entirely
and write a C function that reaches directly into the
internal memory structures to gets my record from there.

thanks,
Ron

Simon Slavin schreef:
> On 18 Oct 2009, at 8:37am, Ron Arts wrote:
> 
>> Is there a way to bypass the virtual machine altogether and reach  
>> directly
>> into the btree and just retrieve one record by it's oid (primary  
>> integer key),
>> and return it in a form that would allow taking out the column  
>> values by name?
> 
> The primary integer key column can always be referred to as the  
> special name ROWID, even if you have assigned it a column name of your  
> own.  So you can do
> 
> SELECT ROWID,myCol1,myCol2 FROM myTable
> 
> as long as you don't explicitly declare a primary integer key column  
> and then change the values in it.
> 
> 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] sqlite in-memory database far too slow in my use case

[Simon Slavin]

On 18 Oct 2009, at 8:37am, Ron Arts wrote:

> Is there a way to bypass the virtual machine altogether and reach  
> directly
> into the btree and just retrieve one record by it's oid (primary  
> integer key),
> and return it in a form that would allow taking out the column  
> values by name?

The primary integer key column can always be referred to as the  
special name ROWID, even if you have assigned it a column name of your  
own.  So you can do

SELECT ROWID,myCol1,myCol2 FROM myTable

as long as you don't explicitly declare a primary integer key column  
and then change the values in it.

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

Re: [sqlite] Why FTS3 has the limitations it does

[Wanadoo Hartwig]

Slightly different question but related to FTS3. Does anybody know why  
this fails using FTS3?

CREATE TABLE Simple (ID integer primary key, Name text);
CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN  
DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN  
INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
INSERT INTO Simple (Name) VALUES('one');
INSERT INTO Simple (Name) VALUES('two');
DELETE FROM Simple WHERE (ID = 1);
INSERT INTO Simple (Name) VALUES('three');
SELECT * FROM Simple;
SELECT last_insert_rowid() FROM Simple;

The output is:

2|two
3|three
4  <-- BUG?!
4
4

Hartwig

PS: This fails only with FTS3. If you use any other (virtual) table it  
works.

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

[sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

Thanks Duncan, for your feedback.

 

This is indeed a bug (currently running SQLite 3.6.18). I guess this is where 
you report bugs?

 

1) Run the C++ program below to generate the necessary SQL data.

2) Then run sqlite3.exe and read it in! (.read test.sql)

3) Execute the following queries:

 

Blistering fast:

SELECT COUNT(*) FROM Item;

Result: 1

 

Slow! WRONG result:

SELECT COUNT(*) FROM Item NATURAL JOIN Item;

Result: 1

 

Clearly, SQLite executes a cartesian product!

 

Oddly, this works as intented:

SELECT COUNT(*) FROM (Item) NATURAL JOIN (Item);

Result: 1

 

 

Unfortunately, this is absolutely crucial for my application. I hope this can 
be fixed very soon!

 

//

#include 

int main()
{
 std::ofstream file ("test.sql");
 
 file << "CREATE TABLE IF NOT EXISTS Item (ItemID INTEGER PRIMARY KEY, A 
INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER NOT NULL, D INTEGER NOT NULL, E 
INTEGER NOT NULL, UNIQUE (A, B));\r\n";
 file << "BEGIN TRANSACTION;\r\n";
 for (int i = 0; i < 1; ++i)
 {
  file << "INSERT INTO Item (A, B, C, D, E) VALUES ("
<< i << ", " << i + 1 << ", " << i + 2 << ", "
<< i + 3 << ", " << i + 4 << ");\r\n";
 }
 file << "COMMIT TRANSACTION;\r\n";
 
 file.close();

 return 0;
}

//

 

 

 

 

 

 

 

 


 
> Date: Sat, 17 Oct 2009 23:02:10 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign keys + NATURAL JOIN
> 
> Kristoffer Danielsson wrote:
> > Thanks.
> > 
> > This leads me to the next question.
> > 
> > Why does the statement below yield a cartesian product?
> > 
> > SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw!
> 
> It doesn't. In fact "t1 NATURAL JOIN t1" would do the exact opposite, because 
> *all* of the columns have the same names, and moreover because both rowsets 
> being joined are the same rowset, the result should be identical to if you 
> said 
> "t1 INTERSECT t1", which is the same as if you simply said "t1" without a 
> join 
> at all. Natural joining something with itself results in itself, and is 
> analogous to "1 * 1 = 1" in math.
> 
> > Why does the statement below NOT yield a cartesian product?
> > 
> > SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster 
> > than the query above!
> 
> This statement should have an identical result to the first one. Having 
> parenthesis around each t1 should make no difference.
> 
> > Sure, the query is brain-damaged, but this could happen "by accident" in my 
> > software.
> > 
> > I'd expect SQLite to optimize this to simply "t1"!
> 
> If SQLite is treating the above 2 queries differently, I would think that an 
> error. Are you sure that's what's happening?
> 
> If you are natural joining a table to itself, or intersecting a table with 
> itself, or unioning a table with itself, then hopefully the optimizer is 
> smart 
> enough to replace that operation with simply the table itself.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på 
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite in-memory database far too slow in my use case

[Ron Arts]

Pavel Ivanov schreef:
>> I use the following queries:
>>
>>   CREATE TABLE company(id INTEGER PRIMARY KEY ASC, name)
> 
> I'm not sure how SQLite treats this table definition but probably
> because of your ASC it could decide that id shouldn't be a synonym for
> rowid which will make at least inserts slower.
> 
>> But I'm still looking to speed up selects.
> 
> Other conditions that can speed up selects:
> - Depending on relation between size of your database and size of
> database cache selects could work faster if all database is cached. So
> for example in just started application first set of selects will work
> slower than second. But if full database cannot fit into cache then
> different sets of selects will have the same performance on average.
> - If you don't care about changing your database concurrently from
> other processes you can place all your inserts in one transaction or
> in case of your real application just start transaction at the
> beginning and commit/rollback it at the end.
> 
> Tell us if it still doesn't satisfy you.
> 
> 

Pavel,

I formulated a followup question in new thread, and that was silly, so I'll
repeat it here.

Is there a way to bypass the virtual machine altogether and reach directly
into the btree and just retrieve one record by it's oid (primary integer key),
and return it in a form that would allow taking out the column values by name?

I don't care if it's a bit dirty, but I really need to speed up record retrieval
and I know beforehand that 99% of my queries involves just retrieving one 
record by
primary integer key. I also know that the entire database will fit in memory.

I'll tell you the general flow of processing in my program: on startup, it 
copies
a postgresql database into a sqlite memory database so I need to do a lot of 
inserts,
but nod very fast, as postgresql cannot supply the records very fast.
Then my program opens a socket, and starts accepting connections, those 
connections
are long lasting, and send messages that need a fast reply. Many of the 
messages result
in messages being send to all other clients. The messages require on average 10 
lookups
in the memory db, each by oid. Very occasionally I get a message that need more,
and for that I need to use an SQL statement internally. I also receive SQL 
queries
from clients occasionally.

My application needs to scale beyond tens of thousand of clients, and should 
also
communicatie with similar apps running on other machines for high availability
and geographical separation.

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

Re: [sqlite] Foreign keys + NATURAL JOIN

[Darren Duncan]

Kristoffer Danielsson wrote:
> Thanks.
> 
> This leads me to the next question.
> 
> Why does the statement below yield a cartesian product?
> 
> SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw!

It doesn't.  In fact "t1 NATURAL JOIN t1" would do the exact opposite, because 
*all* of the columns have the same names, and moreover because both rowsets 
being joined are the same rowset, the result should be identical to if you said 
"t1 INTERSECT t1", which is the same as if you simply said "t1" without a join 
at all.  Natural joining something with itself results in itself, and is 
analogous to "1 * 1 = 1" in math.

> Why does the statement below NOT yield a cartesian product?
> 
> SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster 
> than the query above!

This statement should have an identical result to the first one.  Having 
parenthesis around each t1 should make no difference.

> Sure, the query is brain-damaged, but this could happen "by accident" in my 
> software.
> 
> I'd expect SQLite to optimize this to simply "t1"!

If SQLite is treating the above 2 queries differently, I would think that an 
error.  Are you sure that's what's happening?

If you are natural joining a table to itself, or intersecting a table with 
itself, or unioning a table with itself, then hopefully the optimizer is smart 
enough to replace that operation with simply the table itself.

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