date:20131021

Re: [sqlite] sqlite-users Digest, Vol 70, Issue 21

2013-10-21 Thread Joe Mistachkin

Paul Bainter wrote:
>
> Not sure the first two questions really matter.  What matters is that the
> application will not access the SQLite database before installing
> System.Data.SQLite and will work after the installation.  
> 

The system might not have the required Visual C++ Runtime Libraries
installed.
However, this can be done manually via one of the downloads at the following
link:

https://support.microsoft.com/kb/2019667

In your case, I believe you will want the Visual Studio 2012 (VC++ 11.0)
runtime libraries for x86?

Please note that the setup bundle does install the required Visual C++
runtime
libraries automatically.  That would explain why things worked correctly
after
you ran the setup bundle on the target machine.

--
Joe Mistachkin

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

[sqlite] When does SQLite DB file get locked?

2013-10-21 Thread romtek

Hi,

I started writing this message and then read
http://www.sqlite.org/lang_transaction.html, and I think I've gotten my
answer, but I will ask the question to be absolutely certain, particularly
when using SQLite with PHP (using PDO).

Probably based on flawed understanding of how SQLite worked, I've spent
(wasted, I see now) my time to create a library for working with my DBs
with an intention to minimize the time a script has an open connection to a
DB file. And I've tried to make sure that I close connections as soon as
possible. The document linked to above says that having a connection or
executing a regular BEGIN statement doesn't create a lock. So, if my new
understanding is correct, I should only be concerned with shortening
execution of code within transaction blocks and not at all with how long a
connection is held by a script. Is this correct?

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

Re: [sqlite] sqlite-users Digest, Vol 70, Issue 21

2013-10-21 Thread Paul Bainter

Thank you for looking at my mystery question.

Not sure the first two questions really matter.  What matters is that the
application will not access the SQLite database before installing
System.Data.SQLite and will work after the installation.  I have always had
in my app.config the lines exactly as you mention below, but that didn't
help the program access the database correctly before installing
System.Data.SQLite.  It is also named appropriately "appname.exe.config".
This is built by Visual Studio and I've checked it many times. That is why
this is such a mystery.  This is also not a unique occurance as I have done
this now on other machines that did not before have SQLite on them.  The
best test, however I believe, was to create a new virtual machine as clean
physical machines may be difficult to find.

Best Regards,
Paul Bainter

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
sqlite-users-requ...@sqlite.org
Sent: Monday, October 21, 2013 10:00 AM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 70, Issue 21

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@sqlite.org

When replying, please edit your Subject line so it is more specific than
"Re: Contents of sqlite-users digest..."

Today's Topics:

   1. Re: ambiguous temporary trigger metadata (Bogdan Ureche)
   2. Re: ambiguous temporary trigger metadata (Simon Slavin)
   3. Re: System.Data.SQLite Deployment Mystery (Joe Mistachkin)
   4. SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called
  for all flag pragmas (Ralf Junker)
   5. ORDER BY DESC after GROUP BY not using INDEX -intentionally?
  (Fabian B?ttner)
   6. Re: ORDER BY DESC after GROUP BY not using INDEX -
  intentionally? (Richard Hipp)
   7. Re: ORDER BY DESC after GROUP BY not using INDEX -
  intentionally? (Yuriy Kaminskiy)
   8. Re: ORDER BY DESC after GROUP BY not using INDEX -
  intentionally? (Fabian B?ttner)

--

Message: 1
Date: Sun, 20 Oct 2013 12:11:53 -0500
From: Bogdan Ureche 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] ambiguous temporary trigger metadata
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

Sorry, I misread your reply. You are correct. In your scenario, after step
3 the trigger references a table that no longer exists. A similar issue
exists with foreign keys.

Bogdan

On Sun, Oct 20, 2013 at 10:11 AM, Simon Slavin  wrote:

>
> On 20 Oct 2013, at 4:09pm, Bogdan Ureche  wrote:
>
> > I tried without success to reproduce this scenario.
> >
> > create table t1(c);
> > create temporary trigger tr1 after insert on t1 begin select 
> > raise(abort, 'error'); end; insert into t1(c) values(1); -- error is 
> > raised here drop table t1; create table t1(c); insert into t1(c) 
> > values(1); -- no error here
> >
> > It seems that, when the table dies, it takes the associated trigger 
> > down with it.
>
> Not that table.  The other table.  Suppose you had a trigger on table 
> t1 which inserted a row into t2.  Then you can DROP and reCREATE t2 
> while the trigger exists.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

--

Message: 2
Date: Sun, 20 Oct 2013 19:18:05 +0100
From: Simon Slavin 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] ambiguous temporary trigger metadata
Message-ID: <709eed67-d99f-45a2-9178-ffd29bf23...@bigfraud.org>
Content-Type: text/plain; charset=us-ascii

On 20 Oct 2013, at 6:11pm, Bogdan Ureche  wrote:

> Sorry, I misread your reply

In your defence, my writing was ambiguous.

> You are correct. In your scenario, after step
> 3 the trigger references a table that no longer exists.

As an alternative one could ALTER TABLE RENAME the table to something else,
then create a new table with the old name.  Now, should the trigger refer to
the old table or the new table ?

> A similar issue
> exists with foreign keys.

Right.  SQLite is literal: it stores the commands as text, and executes what
the text says.  Some other SQL engines are semantic: they interpret the text
when it is entered, and do what it meant when the interpretation was done.
I don't think SQL92 specifies one or the other.  But it does mean that
anyone trying to write a GUI Manager for SQLite has to understand what it
does and why.

Simon.

--

Message: 3
Date: Sun, 20 Oct 2013 20:54:46 -0700
From: "Joe Mistachkin" 
To: "'General Di

Re: [sqlite] SELECT and UPDATE?

2013-10-21 Thread Normand Mongeau

Thanks (to other repliers too),

That put me on the right track.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf
Sent: October-18-13 7:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT and UPDATE?


For multiple consumers you might do something like:

BEGIN;
UPDATE processingqueue SET status = 'failed' WHERE status = 'processing' AND
processor = :processor; UPDATE processingqueue SET status = 'processing',
processor = :processor, started = strftime('%s') WHERE queueid = (SELECT
queueid from processingqueue where status = 'ready' AND processor IS NULL
ORDER BY queueid LIMIT 1); SELECT * FROM processingqueue WHERE status =
'processing' AND processor = :processor; UPDATE processingqueue SET status =
'ready', processor = NULL WHERE status = 'failed' AND processor =
:processor; COMMIT;

and run it as a single statement providing the processor name binding to the
named variable :processor and you will get back one row to processes on
:processor, assuming that there is work to do.  Otherwise you will not get
back a row.

If the same :processor asks for more work to do and it is already
processing, then the currently dispatched task failed and you should get a
new one.  The failed job will then be returned to ready and can be
dispatched to any worker in need of work.

schema would look like:

create table processingqueue
(
   queueid integer primary key,
   status text collate nocase,
   started integer,
   ... other data you need ...
  unique (processor, status, queueid)
);



On Fri, 18 Oct 2013 19:04:54 -0400
 "James K. Lowden"  wrote:
>On Fri, 18 Oct 2013 13:57:18 -0400
>"Normand Mongeau"  wrote:
>
>> Also, the consuming should be a 2-step process because the
>processing
>> is involved and may fail for reasons too long to explain here. So in 
>> essence, select a record, modify it to indicate it's being
>processed,
>> and once the processing is done delete the record. Is there a way to 
>> do the initial selection in one swoop (select and update) or is it 
>> two SQL statements? I have to avoid two different processes
>selecting
>> and modifying the same record to minimize rollbacks/retries.
>
>Read after write, not write after read.  
>
>Counterintuitive, perhaps, but if the reading process begins by
>*updating* the record it's about to process, you have idempotent 
>processing without the need for a user-defined transaction.
>
>writer: 
>   insert ... (status, key, data) values ('queued', 1, 'foo');
>
>reader:
>   update ... set status = 'processing'
>   where key = (select min(key) ... where status <> 'done');
>   select ... where status = 'processing';
>   /* work work work */
>   update ... set status = 'done' where key = @key;
>
>SQL-92 IIRC defines an OUTPUT clause for UPDATE, which would do what 
>you want (select+output in one statement).  But that's not a SQLite 
>feature.
>
>--jkl
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


#include 
___
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] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

2013-10-21 Thread Fabian Büttner

Thanks. I think using GROUP BY without aggregates is a strange way to 
remove duplicates, anyway.

Not intentional.  SQLite simply fails to recognize that by using the GROUP
BY in descending order it could avoid the ORDER BY clause.  This is an
optimization that we have never considered because it has never come up
before.



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

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

2013-10-21 Thread Yuriy Kaminskiy

Fabian Büttner wrote:
> Hi,
> 
> I have been thinking about a question on stackoverflow
> (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by),
> where some SQL framework removes duplicates from results using GROUP BY
> instead of DISTINCT.
> I don't want to discuss that this might not be a good idea. However, the
> core of that problem is the creation of temp b-trees when using ORDER BY
> ... DESC after GROUP BY.
> I wondered if the construction of a temp b-tree in the third query is
> intentional / by design?

I'd guess just "missing optimization opportunity". I think this fragment of code
is responsible for that optimization: src/select.c, sqlite3Select():

=== cut ===
  /* If there is both a GROUP BY and an ORDER BY clause and they are
  ** identical, then disable the ORDER BY clause since the GROUP BY
  ** will cause elements to come out in the correct order.  This is
  ** an optimization - the correct answer should result regardless.
  ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  ** to disable this optimization for testing purposes.
  */
  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
pOrderBy = 0;
  }
=== cut ===

Adding DESC to pOrderBy "breaks" sqlite3ExprListCompare(,) (note: just changing
sqlite3ExprListCompare to ignore it would be insufficient and will likely result
in *breakage*).

> I am using sqlite 3.8.1.
> 
> sqlite> PRAGMA legacy_file_format=OFF;
> 
> sqlite> create table test1 (x INTEGER);
> sqlite> create index test1_idx on test1(x);
> sqlite> explain query plan select x from test1 group by x order by x;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test1 USING COVERING
> INDEX test1_idx
> 
> create table test2 (x INTEGER);
> sqlite> create index test2_idx on test2(x);
> sqlite> explain query plan select x from test2 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test2 USING COVERING
> INDEX test2_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> create table test3 (x INTEGER);
> sqlite> create index test3_idx on test3(x desc);
> sqlite> explain query plan select x from test3 group by x order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> To double check:
> 
> sqlite> explain query plan select x from test3 order by x desc;
> selectidorder   fromdetail
> --  --  --
> ---
> 0   0   0   SCAN TABLE test3 USING COVERING
> INDEX test3_idx
> 
> 
> Regards
> Fabian

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

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

2013-10-21 Thread Richard Hipp

On Mon, Oct 21, 2013 at 5:27 AM, Fabian Büttner wrote:

> Hi,
>
> I have been thinking about a question on stackoverflow (
> http://stackoverflow.com/**questions/19236363/select-**
> distinct-faster-than-group-by
> )**, where some SQL framework removes duplicates from results using GROUP
> BY instead of DISTINCT.
> I don't want to discuss that this might not be a good idea. However, the
> core of that problem is the creation of temp b-trees when using ORDER BY
> ... DESC after GROUP BY.
> I wondered if the construction of a temp b-tree in the third query is
> intentional / by design?
>

Not intentional.  SQLite simply fails to recognize that by using the GROUP
BY in descending order it could avoid the ORDER BY clause.  This is an
optimization that we have never considered because it has never come up
before.



>
> I am using sqlite 3.8.1.
>
> sqlite> PRAGMA legacy_file_format=OFF;
>
> sqlite> create table test1 (x INTEGER);
> sqlite> create index test1_idx on test1(x);
> sqlite> explain query plan select x from test1 group by x order by x;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test1 USING COVERING INDEX
> test1_idx
>
> create table test2 (x INTEGER);
> sqlite> create index test2_idx on test2(x);
> sqlite> explain query plan select x from test2 group by x order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test2 USING COVERING INDEX
> test2_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
>
> create table test3 (x INTEGER);
> sqlite> create index test3_idx on test3(x desc);
> sqlite> explain query plan select x from test3 group by x order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test3 USING COVERING INDEX
> test3_idx
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
>
> To double check:
>
> sqlite> explain query plan select x from test3 order by x desc;
> selectidorder   fromdetail
> --  --  -- --**
> -
> 0   0   0   SCAN TABLE test3 USING COVERING INDEX
> test3_idx
>
>
> Regards
> Fabian
> __**_
> 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

[sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

2013-10-21 Thread Fabian Büttner


Hi,

I have been thinking about a question on stackoverflow 
(http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by), 
where some SQL framework removes duplicates from results using GROUP BY 
instead of DISTINCT.
I don't want to discuss that this might not be a good idea. However, the 
core of that problem is the creation of temp b-trees when using ORDER BY 
... DESC after GROUP BY.
I wondered if the construction of a temp b-tree in the third query is 
intentional / by design?


I am using sqlite 3.8.1.

sqlite> PRAGMA legacy_file_format=OFF;

sqlite> create table test1 (x INTEGER);
sqlite> create index test1_idx on test1(x);
sqlite> explain query plan select x from test1 group by x order by x;
selectidorder   fromdetail
--  --  -- 
---
0   0   0   SCAN TABLE test1 USING COVERING 
INDEX test1_idx


create table test2 (x INTEGER);
sqlite> create index test2_idx on test2(x);
sqlite> explain query plan select x from test2 group by x order by x desc;
selectidorder   fromdetail
--  --  -- 
---
0   0   0   SCAN TABLE test2 USING COVERING 
INDEX test2_idx

0   0   0   USE TEMP B-TREE FOR ORDER BY

create table test3 (x INTEGER);
sqlite> create index test3_idx on test3(x desc);
sqlite> explain query plan select x from test3 group by x order by x desc;
selectidorder   fromdetail
--  --  -- 
---
0   0   0   SCAN TABLE test3 USING COVERING 
INDEX test3_idx

0   0   0   USE TEMP B-TREE FOR ORDER BY

To double check:

sqlite> explain query plan select x from test3 order by x desc;
selectidorder   fromdetail
--  --  -- 
---
0   0   0   SCAN TABLE test3 USING COVERING 
INDEX test3_idx



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

[sqlite] SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called for all flag pragmas

2013-10-21 Thread Ralf Junker

SQLite 3.8.1 compiled with SQLITE_OMIT_FLAG_PRAGMAS #defined calls 
sqlite3_busy_timeout() for all flag pragmas.

Example:

  PRAGMA legacy_file_format=100;

should do nothing but sets

  sqlite3_busy_timeout(db, 100);

The change was introduced here:

  
http://www.sqlite.org/src/artifact/249742bd762770e5e6e5b67cfcb2fa9b1049?ln=1960

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

Re: [sqlite] sqlite-users Digest, Vol 70, Issue 21

[sqlite] When does SQLite DB file get locked?

Re: [sqlite] sqlite-users Digest, Vol 70, Issue 21

Re: [sqlite] SELECT and UPDATE?

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

[sqlite] SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called for all flag pragmas

9 matches

Site Navigation

Mail list logo

Footer information