[sqlite] backup memory database not working
On 2016/03/10 10:41 PM, asdf asdf wrote: > Hello, > what do you mean, please ? What code is not shown:my own code (and what > could be the cause then) using the example or what i posted in > stackoverflow. > > > I would be happy to solve it. Any information appreciated- He means that there is something else that is wrong in your code that you've used to test this Backup feature. We cannot see your full code, so we do not know what is wrong with it, but we know something is wrong because the backup from memory works when we do it, and works when done like the example. You can easily use the command line SQLite3.exe tool to test it. So, if it works correctly for us, meaning it is not an SQLite bug, but there might be something in your code that is missing or weird and we would like to help you find it, however, only if you show us the exact code you've used to test with. Also the DB schema - some SQL perhaps to populate the in-memory database. Perhaps something about the schema is strange and causes the problem, then it might even be a bug - but we can't know that until we have exactly what you have. Thanks, Ryan
[sqlite] "Circular" order by
On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: > > I do this kind of thing so often when filling a selection box for instance: > SELECT 'None' > UNION ALL > SELECT City FROM Countrylist WHERE Country = :1 > UNION ALL > SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City > > Which, as you can deduce, adds a 'None' to the option list, then the > selected country's capital city, then the other cities in alphabetical > order. I now think I need a more sophisticated method to ensure that output > doesn't get mangled. If SQLite ever changes this behaviour, lots of things > will break for me, but, that's life, I will start fixing them all. > > Heh, assumptions... that'll teach me! :) > Ryan > Not that I want to hijack the thread, but with the country list I got from here: https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql I came up with this simple modification to your query: SELECT 'None',0 as OrderNum UNION ALL SELECT Value,1 FROM List WHERE Value = :1 UNION ALL SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value
[sqlite] backup memory database not working
Hello, what do you mean, please ? What code is not shown:my own code (and what could be the cause then) using the example or what i posted in stackoverflow. I would be happy to solve it. Any information appreciated- Am 10.03.2016 um 20:39 schrieb Clemens Ladisch: > asdf asdf wrote: >> Backing up a file database works well;not so memory. >> >> I described the issue here: >> http://stackoverflow.com/questions/35834529/sqlite-backup-memory-database-c > There is an error in your code. Which you have not shown. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Circular" order by
On 03/10/16 20:16, R Smith wrote: >> Hmm, does this work any better? >> >> SELECT id FROM t >> ORDER BY id < 'pen' desc, id; > > It works, but not better. Indeed. Any operation in ORDER BY (even things like "ORDER BY id||''") result in scan + temp b-tree. Thanks again. -- Alberto
[sqlite] "Circular" order by
On 2016/03/10 8:37 PM, James K. Lowden wrote: > On Thu, 10 Mar 2016 10:17:57 +0100 > Alberto Wu wrote: > >> On 03/09/16 23:30, James K. Lowden wrote: SELECT P.id FROM ( SELECT 0 AS sect, id FROM t WHERE id >= 'pen' UNION ALL SELECT 1, id FROM t WHERE id < 'pen' ) AS P ORDER BY P.sect, P.id ; >>> This is the correct answer. >> Hi, >> >> unfortunately the correct answer comes with an extra scan and a temp >> b-tree so I'd rather keep the two queries split and handle the case >> programmatically. > Hmm, does this work any better? > > SELECT id FROM t > ORDER BY id < 'pen' desc, id; It works, but not better. I think it was Igor who proposed similar (if not, apologies) which of course produces the correct result, but cannot take advantage of the index on id so it becomes a result-set walk causing longer ordering of values - exactly what the OP tried to avoid. I myself is sad to find that the SELECTs in between UNION ALL statements qualify as sub-selects and do not implicitly honor the order in which they are UINIONed - I had this wrong. I do this kind of thing so often when filling a selection box for instance: SELECT 'None' UNION ALL SELECT City FROM Countrylist WHERE Country = :1 UNION ALL SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City Which, as you can deduce, adds a 'None' to the option list, then the selected country's capital city, then the other cities in alphabetical order. I now think I need a more sophisticated method to ensure that output doesn't get mangled. If SQLite ever changes this behaviour, lots of things will break for me, but, that's life, I will start fixing them all. Heh, assumptions... that'll teach me! :) Ryan
[sqlite] backup memory database not working
asdf asdf wrote: > Backing up a file database works well;not so memory. > > I described the issue here: > http://stackoverflow.com/questions/35834529/sqlite-backup-memory-database-c There is an error in your code. Which you have not shown. Regards, Clemens
[sqlite] Why are there no Authenticode signatures on prebuilt DLLs or tools?
Is there a way to request that the build pipeline for prebuilt DLLs and tools for Windows be modified to include Authenticode signatures? There is no means provided to verify the integrity of these executables, which means that anyone can download from the official URLs but have their connections intercepted to provide virus-laden or otherwise adulterated versions. (And, let me tell you, manually verifying GPG signatures gets old after the first seven or more times one must do so.) Serving via https would eliminate the MITM-interception attack, but unsigned binaries still provide a vector for local persistence of infection because they prevent the implementation of Windows Software Restriction Policies that prevent all unsigned code from running. Thanks for your time! -Kyle H
[sqlite] backup memory database not working
Hello, the examples for the back API as exposed here: https://www.sqlite.org/backup.html work not for memory database. Tested with latest amalgamation VC++ VS 2013. Backing up a file database works well;not so memory. I described the issue here: http://stackoverflow.com/questions/35834529/sqlite-backup-memory-database-c Thank you for help
[sqlite] Changes to VFS starting 3.8.3
Thanks, I suspect there's indeed some special behavior not obvious at the moment. I'll try to gather some additional information if it's possible or detect this specific behavior On Thu, Mar 10, 2016 at 5:26 PM, Richard Hipp wrote: > On 3/10/16, Max Vlasov wrote: > > I have a compatibility problem with my vfs implementation of memory > > databases. > > > > As I see from the version history page that no specific vfs-related > changes > > for 3.8.3 were reported, only a major change that can affect structure > and > > vfs is initial common table expressions implementation. > > > > What are the changes that might trigger the change? > > Dunno. The changes to the unix VFS were minimal > ( > https://www.sqlite.org/src/fdiff?v1=f076587029285554=f3ed0e406cbf9c82=1 > ) > and likewise the windows VFS > ( > https://www.sqlite.org/src/fdiff?v1=4323dd0bac4f7a70=1b21af72c5fa6f9e=1 > ). > Perhaps your in-memory VFS was relying on some unspecified behavior > that changed? Without access to your source code, it is difficult to > say. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] "Circular" order by
On Thu, 10 Mar 2016 21:16:28 +0200 R Smith wrote: > > Hmm, does this work any better? > > > > SELECT id FROM t > > ORDER BY id < 'pen' desc, id; > > It works, but not better. I think it was Igor who proposed similar > (if not, apologies) which of course produces the correct result, but > cannot take advantage of the index on id so it becomes a result-set > walk causing longer ordering of values - exactly what the OP tried to > avoid. Hmm, I don't know about "cannot", but I'm not surprised by "does not", because it's a tough inference. If you stand back a minute, you can see that id < 'pen' is a monotonic function of "id" if "id" is sorted. The query processor *could* include that logic, and could choose to process the rows, in index order, starting with the first row where id >= 'pen' , to the end, and wrapping back to the beginning. The big boys do that kind of thing. In general "order by f(x)" will use an index on x if f(x) has the same order. For hard problems, they support computed columns -- a little like a view attached to a table -- and indexes on them. In SQLite, I guess the OP's only solution is to make an index of the kind he needs. create table idx as select id < 'pen' as 'LT_pen', id from t; select id from idx order by LT_pen; Of course, that presupposes 'pen' is a constant. Whether or not that's true wasn't mentioned in the original post. --jkl
[sqlite] jude a record is exist or not
sql one : select * from table_name where id = **; sql two : select count() from table_name where id = **; I test the two sql statement. It almost the same speed. At 2016-03-10 16:29:16, "Hick Gunter" wrote: >Assuming the "id" is the primary key of your table "table_name", your >statement will list the id of all the records present (full scan, reading >every entry of the implicit primary key index). > >To find out if a specific record exists, use "select count() from table_name >where id = " to return a single row with the number of records >found. This may be faster only if id has a primary key or unique constraint or >is the first field of a declared index. > >-Urspr?ngliche Nachricht- >Von: sqlite-users-bounces at mailinglists.sqlite.org >[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Jim >Wang >Gesendet: Donnerstag, 10. M?rz 2016 09:01 >An: sqlite-users at mailinglists.sqlite.org >Betreff: [sqlite] jude a record is exist or not > >hi,all >As usual, we judge a record is exist or not in a data base is :select id > from table_name. >Except the above method, Is there any method which could judge a record is > exist or not much faster than the above method. > >I am looking forward to hearing from you. >Jim Wang. >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >___ > Gunter Hick >Software Engineer >Scientific Games International GmbH >FN 157284 a, HG Wien >Klitschgasse 2-4, A-1130 Vienna, Austria >Tel: +43 1 80100 0 >E-Mail: hick at scigames.at > >This communication (including any attachments) is intended for the use of the >intended recipient(s) only and may contain information that is confidential, >privileged or legally protected. Any unauthorized use or dissemination of this >communication is strictly prohibited. If you have received this communication >in error, please immediately notify the sender by return e-mail message and >delete all copies of the original communication. Thank you for your >cooperation. > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] jude a record is exist or not
Adding : the "id" is the primary key. At 2016-03-10 16:29:1 6, "Hick Gunter" wrote: >Assuming the "id" is the primary key of your table "table_name", your >statement will list the id of all the records present (full scan, reading >every entry of the implicit primary key index). > >To find out if a specific record exists, use "select count() from table_name >where id = " to return a single row with the number of records >found. This may be faster only if id has a primary key or unique constraint or >is the first field of a declared index. > >-Urspr?ngliche Nachricht- >Von: sqlite-users-bounces at mailinglists.sqlite.org >[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Jim >Wang >Gesendet: Donnerstag, 10. M?rz 2016 09:01 >An: sqlite-users at mailinglists.sqlite.org >Betreff: [sqlite] jude a record is exist or not > >hi,all >As usual, we judge a record is exist or not in a data base is :select id > from table_name. >Except the above method, Is there any method which could judge a record is > exist or not much faster than the above method. > >I am looking forward to hearing from you. >Jim Wang. >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >___ > Gunter Hick >Software Engineer >Scientific Games International GmbH >FN 157284 a, HG Wien >Klitschgasse 2-4, A-1130 Vienna, Austria >Tel: +43 1 80100 0 >E-Mail: hick at scigames.at > >This communication (including any attachments) is intended for the use of the >intended recipient(s) only and may contain information that is confidential, >privileged or legally protected. Any unauthorized use or dissemination of this >communication is strictly prohibited. If you have received this communication >in error, please immediately notify the sender by return e-mail message and >delete all copies of the original communication. Thank you for your >cooperation. > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changes to VFS starting 3.8.3
Hi, I have a compatibility problem with my vfs implementation of memory databases. I once implemented it successfully probably with a version 3.6.something. Today I tried to create a new database using the same code with the latest (3.11.1) version (the procedure is when no prior db data exists, i.e. absolutely new file is being worked with) and it failed on a first write-related query, something like "create table if not exists" . After that I decided to try different binary versions of sqlite and narrowed it down to the difference between 3.8.2 and 3.8.3. So, with 3.8.2 everything is ok, the db created has correct structure (checked on the memory block saved as file) and no error produced during the creation. With 3.8.3 the first attempt to perform the same query on a new file produces "Sql logic error or missing database". As I see from the version history page that no specific vfs-related changes for 3.8.3 were reported, only a major change that can affect structure and vfs is initial common table expressions implementation. What are the changes that might trigger the change? Thanks, Max
[sqlite] jude a record is exist or not
hi,all As usual, we judge a record is exist or not in a data base is :select id from table_name. Except the above method, Is there any method which could judge a record is exist or not much faster than the above method. I am looking forward to hearing from you. Jim Wang.
[sqlite] Possible bug
On 10 Mar 2016, at 4:43am, Marv Anderson wrote: > SQL Logic error or missing database near "SELECT": syntax error > >SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] This involves two things which are not built into SQLite: CONVERT() SCOPE_IDENTITY() Assuming that they're provided by your development environment that's fine. Unfortunately, the SQLite interface you're using ignored the detailed error message produced by SQLite, which would tell you where the syntax error was, and instead is just giving you a generic error message. I suspect you should talk to people who know about the SQLite Linq package to get a better understanding of the fault. Simon.
[sqlite] "Circular" order by
On Thu, 10 Mar 2016 10:17:57 +0100 Alberto Wu wrote: > On 03/09/16 23:30, James K. Lowden wrote: > >> SELECT P.id FROM ( > >> SELECT 0 AS sect, id FROM t WHERE id >= 'pen' > >> UNION ALL > >> SELECT 1, id FROM t WHERE id < 'pen' > >> ) AS P > >> ORDER BY P.sect, P.id > >> ; > > > > This is the correct answer. > > Hi, > > unfortunately the correct answer comes with an extra scan and a temp > b-tree so I'd rather keep the two queries split and handle the case > programmatically. Hmm, does this work any better? SELECT id FROM t ORDER BY id < 'pen' desc, id; --jkl
[sqlite] jude a record is exist or not
On 10 Mar 2016, at 9:54am, Jim Wang <2004wqg2008 at 163.com> wrote: > sql one : select * from table_name where id = **; > sql two : select count() from table_name where id = **; > > > I test the two sql statement. It almost the same speed. Another possibility to test for speed is select 1 from table_name where id = **; For a single command I would expect all these to take the same time. For a look of ten thousand you might expect to see a difference. However, unless your application is taking so long to get the result I support your idea that it doesn't matter which you use. Simon.
[sqlite] ICU and FTS5
On 03/10/2016 02:56 AM, Tim Uy wrote: > Will the ICU tokenizer work with FTS5, or does some work need to be done to > port it over (slight pun intended). It doesn't work with FTS5. Both FTS3/4 and FTS5 allow for user-defined tokenizers, but the interface is not the same. So the ICU tokenizer would need to be adapted or rewritten to work with FTS5. Dan.
[sqlite] regression: --disable-static-shell no longer works
Hi, Cross-compiling SQLite from source on Linux using sqlite-autoconf-3110100, the --disable-static-shell option no longer works, a static-linked-sqlite3 binary is created. The regression appears to have occurred here: http://www.sqlite.org/src/fdiff?v1=1c16576507759608=29e2a6e8d0c5e327=0 This patch makes --disable-static-shell work again. --- sqlite-3110100/Makefile.am.orig 2016-03-10 10:12:23.0 -0600 +++ sqlite-3110100/Makefile.am 2016-03-10 10:14:14.0 -0600 @@ -6,8 +6,8 @@ libsqlite3_la_LDFLAGS = -no-undefined -version-info 8:6:8 bin_PROGRAMS = sqlite3 -sqlite3_SOURCES = shell.c sqlite3.c sqlite3.h -sqlite3_LDADD = @READLINE_LIBS@ +sqlite3_SOURCES = shell.c sqlite3.h +sqlite3_LDADD = @EXTRA_SHELL_OBJ@ @READLINE_LIBS@ sqlite3_DEPENDENCIES = @EXTRA_SHELL_OBJ@ sqlite3_CFLAGS = $(AM_CFLAGS) -DSQLITE_ENABLE_EXPLAIN_COMMENTS Hmmm, removing "sqlite3.c" from sqlite3_SOURCES works even without --disable-static-shell specified and a static sqlite3 binary is created as expected. The above patch fixes the regression for me, though it may not work with all possible permutations of builds. Possibly the line: -- EXTRA_sqlite3_SOURCES = sqlite3.c -- still needs to be added and referenced appropriately, currently EXTRA_sqlite3_SOURCES is no longer referenced. Lonnie
[sqlite] "Circular" order by
On 03/09/16 23:30, James K. Lowden wrote: >> SELECT P.id FROM ( >> SELECT 0 AS sect, id FROM t WHERE id >= 'pen' >> UNION ALL >> SELECT 1, id FROM t WHERE id < 'pen' >> ) AS P >> ORDER BY P.sect, P.id >> ; > > This is the correct answer. Hi, unfortunately the correct answer comes with an extra scan and a temp b-tree so I'd rather keep the two queries split and handle the case programmatically. Thanks a lot for your help. -- Alberto
[sqlite] Changes to VFS starting 3.8.3
On 3/10/16, Max Vlasov wrote: > I have a compatibility problem with my vfs implementation of memory > databases. > > As I see from the version history page that no specific vfs-related changes > for 3.8.3 were reported, only a major change that can affect structure and > vfs is initial common table expressions implementation. > > What are the changes that might trigger the change? Dunno. The changes to the unix VFS were minimal (https://www.sqlite.org/src/fdiff?v1=f076587029285554=f3ed0e406cbf9c82=1) and likewise the windows VFS (https://www.sqlite.org/src/fdiff?v1=4323dd0bac4f7a70=1b21af72c5fa6f9e=1). Perhaps your in-memory VFS was relying on some unspecified behavior that changed? Without access to your source code, it is difficult to say. -- D. Richard Hipp drh at sqlite.org
[sqlite] jude a record is exist or not
Assuming the "id" is the primary key of your table "table_name", your statement will list the id of all the records present (full scan, reading every entry of the implicit primary key index). To find out if a specific record exists, use "select count() from table_name where id = " to return a single row with the number of records found. This may be faster only if id has a primary key or unique constraint or is the first field of a declared index. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Jim Wang Gesendet: Donnerstag, 10. M?rz 2016 09:01 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] jude a record is exist or not hi,all As usual, we judge a record is exist or not in a data base is :select id from table_name. Except the above method, Is there any method which could judge a record is exist or not much faster than the above method. I am looking forward to hearing from you. Jim Wang. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] sqlite3.exe as 64bit stand-alone pre-compiled binary version for Windows available?
When I go to page http://www.sqlite.org/download.html then there are a 32bit and a 64bit DLL library version of SQLite. Unfortunately there is only the 32bit version of the stand-alone engine. Ok, I know, I can run the 32bit version as well under 64bit Windows but I prefer a 64bit version since it is generally faster on 64bit system. Is there really no 64bit satnd-alone version? Why? Ben
[sqlite] sqlite3.exe as 64bit stand-alone pre-compiled binary version for Windows available?
On 3/10/16, Ben Stover wrote: > When I go to page > > http://www.sqlite.org/download.html > > then there are a 32bit and a 64bit DLL library version of SQLite. > > Unfortunately there is only the 32bit version of the stand-alone engine. > > Ok, I know, I can run the 32bit version as well under 64bit Windows but I > prefer a 64bit version since it > is generally faster on 64bit system. > > Is there really no 64bit satnd-alone version? > > Why? > You've already answered your own question: The 32-bit version runs fine on 64-bit windows systems. We also supply easy-to-compile source code so that you can rebuild for whatever platform and architecture you like. -- D. Richard Hipp drh at sqlite.org
[sqlite] Multithreaded SQLite
On Wed, 9 Mar 2016 17:18:15 -0500, Philippe Riand wrote: > Yes, I?m using prepare(), step and finalize(). The 2 threads should > actually have no interaction between them, but isolated. > I mean the 2 threads should be able to do very different > things (read data, write data?), within separated transactions. You'd have to give every thread its own connection to make them independent with respect to transaction context. -- Regards, Kees Nuyt
[sqlite] Multiple connections to in-memory db via .net
How can I create multiple connections to a shared SQLite in-memory database via .net? Thanks in advance.
[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction
On 03/09/2016 11:35 PM, Tomash Brechko wrote:
> Hello,
>
> With 3.11.0 if you run the following SQL you will get no result (which is
> wrong):
>
> -- cut --
> BEGIN;
> CREATE VIRTUAL TABLE fts USING fts4 (t);
> INSERT INTO fts (rowid, t) VALUES (1, 'test');
> INSERT INTO fts (fts) VALUES ('optimize');
> COMMIT;
> SELECT rowid FROM fts WHERE fts MATCH 'test';
> -- cut --
>
>
Thanks for reporting this. Now fixed here:
http://sqlite.org/src/info/79338b991bf01e81
Dan.
[sqlite] compiling 3.11.1
found it - old copy of cerod slipped into my sqlite3.c! On Wed, Mar 9, 2016 at 10:44 PM, Tim Uy wrote: > yup must be just me, it works fine on a clean fossil clone > > On Wed, Mar 9, 2016 at 10:15 PM, Tim Uy wrote: > >> It is probably just something I mangled - but oddly my tsrc/fts5.c has no >> trace of sqlite3PagerGet and no more than 2 or so lines. >> >> On Wed, Mar 9, 2016 at 10:07 PM, Tim Uy wrote: >> >>> I'm getting >>> >>> tsrc/fts5.c(23549): error C2198: 'sqlite3PagerGet': too few arguments >>> for call >>> tsrc/fts5.c(23568): error C2198: 'sqlite3PagerGet': too few arguments >>> for call >>> >>> What am I missing? :P >>> >> >> >
