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 <bogdan...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] ambiguous temporary trigger metadata Message-ID: <caold1sajyaq2h2mcz38cf7bh9+kjcz4byidesnicet3banc...@mail.gmail.com> 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 <slav...@bigfraud.org> wrote: > > On 20 Oct 2013, at 4:09pm, Bogdan Ureche <bogdan...@gmail.com> 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 <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 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 <bogdan...@gmail.com> 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" <sql...@mistachkin.com> To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Subject: Re: [sqlite] System.Data.SQLite Deployment Mystery Message-ID: <C96BA8F89D4F47188BBDEC673A8B7472@LACHRYMOSE> Content-Type: text/plain; charset="us-ascii" Paul Bainter wrote: > > When deploying my application to a clean Windows 7 x64 virtual machine > (VMWare Workstation 10), I got the message "Failed to find or load the > registered .NET Framework Data Provider" and of course with no > database the app would crash. > When does this error appear? What application is raising it? The .NET Framework Data Provider configuration is normally found within the "app.config" file (i.e. "App locally") or the machine-wide configuration file. The "app.config" file needs to be present in the directory containing the primary executable managed file for the application and must be named "${exeFileName}.config", where "${exeFileName}" is the name of the executable. Example: test.exe -- Executable file name. test.exe.config -- The "app.config" file name. > > I then installed the file: > "sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe" (retrieved from > your web > site) to this clean machine. I did NOT install it to the GAC which of course > also eliminated the Visual Studio Design stuff. At that point, I ran > my app > again and it worked as expected. > The setup bundle modifies the machine-wide configuration file to add the SQLite ADO.NET provider. However, when deploying to any machine that does not require the Visual Studio design-time functionality, add the necessary section to the "app.config" file is recommended instead. The file should look something like this: <configuration> <system.data> <DbProviderFactories> <remove invariant="System.Data.SQLite" /> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" /> </DbProviderFactories> </system.data> </configuration> -- Joe Mistachkin ------------------------------ Message: 4 Date: Mon, 21 Oct 2013 09:12:11 +0200 From: Ralf Junker <ralfjun...@gmx.de> To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> Subject: [sqlite] SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called for all flag pragmas Message-ID: <5264d3cb.7080...@gmx.de> Content-Type: text/plain; charset=ISO-8859-1 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/249742bd762770e5e6e5b67cfcb2fa33339b1049? ln=1960 Ralf ------------------------------ Message: 5 Date: Mon, 21 Oct 2013 11:27:15 +0200 From: Fabian B?ttner <fabian.buett...@gmx.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally? Message-ID: <5264f373.5050...@gmx.org> Content-Type: text/plain; charset=ISO-8859-15; format=flowed Hi, I have been thinking about a question on stackoverflow (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-gro up-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); explain query plan select x sqlite> from test1 group by x order by x; selectid order from detail ---------- ---------- ---------- ----------------------------------------------- 0 0 0 SCAN TABLE test1 USING COVERING INDEX test1_idx create table test2 (x INTEGER); sqlite> create index test2_idx on test2(x); explain query plan select x sqlite> from test2 group by x order by x desc; selectid order from detail ---------- ---------- ---------- ----------------------------------------------- 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); explain query plan sqlite> select x from test3 group by x order by x desc; selectid order from detail ---------- ---------- ---------- ----------------------------------------------- 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; selectid order from detail ---------- ---------- ---------- ----------------------------------------------- 0 0 0 SCAN TABLE test3 USING COVERING INDEX test3_idx Regards Fabian ------------------------------ Message: 6 Date: Mon, 21 Oct 2013 06:44:00 -0400 From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally? Message-ID: <CALwJ=MzrMfRUM7heCBwd8Gy4zSrAO4_tYg7GyX54tket=a+...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 On Mon, Oct 21, 2013 at 5:27 AM, Fabian B?ttner <fabian.buett...@gmx.org>wrote: > Hi, > > I have been thinking about a question on stackoverflow ( > http://stackoverflow.com/**questions/19236363/select-** > distinct-faster-than-group-by<http://stackoverflow.com/questions/19236363/se lect-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; > selectid order from detail > ---------- ---------- ---------- ------------------------------** > ----------------- > 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; > selectid order from detail > ---------- ---------- ---------- ------------------------------** > ----------------- > 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; > selectid order from detail > ---------- ---------- ---------- ------------------------------** > ----------------- > 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; > selectid order from detail > ---------- ---------- ---------- ------------------------------** > ----------------- > 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<http://sqli te.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org ------------------------------ Message: 7 Date: Mon, 21 Oct 2013 14:48:43 +0400 From: Yuriy Kaminskiy <yum...@gmail.com> To: sqlite-users@sqlite.org Subject: Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally? Message-ID: <l430q2$ls4$1...@ger.gmane.org> Content-Type: text/plain; charset=UTF-8 Fabian B?ttner wrote: > Hi, > > I have been thinking about a question on stackoverflow > (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-gro up-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; > selectid order from detail > ---------- ---------- ---------- > ----------------------------------------------- > 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; > selectid order from detail > ---------- ---------- ---------- > ----------------------------------------------- > 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; > selectid order from detail > ---------- ---------- ---------- > ----------------------------------------------- > 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; > selectid order from detail > ---------- ---------- ---------- > ----------------------------------------------- > 0 0 0 SCAN TABLE test3 USING COVERING > INDEX test3_idx > > > Regards > Fabian ------------------------------ Message: 8 Date: Mon, 21 Oct 2013 14:16:58 +0200 From: Fabian B?ttner <fabian.buett...@gmx.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally? Message-ID: <52651b3a.7030...@gmx.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed 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 End of sqlite-users Digest, Vol 70, Issue 21 ******************************************** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
