[sqlite] Possible bug

Hello, I am a member of this list, but I am not sure which email address you have. I have tried the ones that I usually use, but my messages are getting held due to not recognizing my address. I am having a problem doing a Linq Insert using SQLite in Visual Studio 2015 (Community version) in

[sqlite] compiling 3.11.1

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

[sqlite] CTE for a noob

On 2016/03/09 10:01 PM, David Raymond wrote: > For my own clarification, the statements quoted way down below aren't exactly > equivalent, correct? > > "For each pair of columns identified by a USING clause, the column from the > right-hand dataset is omitted from the joined dataset. This is

[sqlite] sqlite3_update_hook() clarification

Thank you very much!! It helped a lot. On Wed, Mar 9, 2016 at 9:26 PM, Clemens Ladisch wrote: > Sairam Gaddam wrote: > > The documentation says that the function sqlite3_update_hook() is called > > whenever a row is updated, deleted or inserted > > No. It says that this function is called to

[sqlite] Multithreaded SQLite

On 9 Mar 2016, at 10:15pm, Philippe Riand wrote: > I tries busy_timeout but that was unsuccessful. What did you set the time to ? Try 5 minutes. You should definitely remove all the unusual PRAGMAs apart from that one and see if that makes the problem go away. Simon.

[sqlite] compiling 3.11.1

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 >

[sqlite] compiling 3.11.1

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

[sqlite] sqlite3_update_hook() clarification

http://www.sqlite.org/c3ref/update_hook.html The documentation says that the function sqlite3_update_hook() is called whenever a row is updated, deleted or inserted for a rowid table. But I don't find this function to be invoked in my program. When will this function be invoked?? And I am

[sqlite] "Circular" order by

On 2016/03/09 8:08 PM, Alberto Wu wrote: > On 03/09/16 17:26, R Smith wrote: >> Firstly, this is the best method - one I would use. UNION ALL is quite >> efficient. >> Secondly, the order by will be honoured - you can refer to the SQL >> standard for that even, it's an axiom of the output and

[sqlite] CTE for a noob

For my own clarification, the statements quoted way down below aren't exactly equivalent, correct? "For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset. This is the only difference between a USING clause and its

[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction

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'; --

[sqlite] Multiple connections to in-memory db via .net

Jarred Ford wrote: > > How can I create multiple connections to a shared SQLite in-memory > database via .net? Thanks in advance. > SQLiteConnection connection = new SQLiteConnection( "FullUri=file::memory:?cache=shared;"); -- Joe Mistachkin

[sqlite] "Circular" order by

On 03/09/16 17:26, R Smith wrote: > Firstly, this is the best method - one I would use. UNION ALL is quite > efficient. > Secondly, the order by will be honoured - you can refer to the SQL > standard for that even, it's an axiom of the output and probably not > even considered "needed" to

[sqlite] How to export all entries from a sqlite database into a textfile?

As you may know Firefox browser uses a places.sqlite to store all its bookmarks. Assume the used sqlite database is D:\firefox\myprofile\places.sqlite How can I use sqlite3.exe (under Windows 7) to extract/export all entries from this database into a text file

[sqlite] "Circular" order by

On 2016/03/09 6:07 PM, Alberto Wu wrote: > Hi all, > > I'm looking for suggestions... > What I want to achieve is to "roll" the result set of a query around by > a certain amount (as in offset + wrap around). > > For example, given that: > CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY); > INSERT

[sqlite] <> in Makefile.msc

There are marks in the MSVC makefile to permit tooling to remove and/or replace the marked sections, see "tools/mkmsvcmin.tcl". Sent from my iPhone > On Mar 9, 2016, at 5:26 PM, Tim Uy wrote: > > What does <> <> mean in Makefile.msc? Just noticed it. >

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 2016/03/09 5:39 PM, Jean-Christophe Deschamps wrote: > > Sorry guys, I don't know why I wrote that. In fact I know: I shouldn't > be talking over the phone while reading the list. > > Of course I use correlated subqueries a lot, but never had to > re-select a column from the enclosing

[sqlite] How to export all entries from a sqlite database into a textfile?

On 9 Mar 2016, at 5:47pm, Ben Stover wrote: > How can I use sqlite3.exe (under Windows 7) to extract/export all entries > from this database into a text file As an experiment, try using the command file to open the database and show the schema: sqlite3 D:\firefox\myprofile\places.sqlite

[sqlite] "Circular" order by

On Wed, 9 Mar 2016 20:43:14 +0200 R Smith 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. I'm not sure what you meant by "axiom" in your

[sqlite] <> in Makefile.msc

What does <> <> mean in Makefile.msc? Just noticed it.

[sqlite] Insert or Replace Trouble

Apologies, it seems you got lots of replies explaining why, but I failed to mention how it would work. Try this in your code: CREATE TABLE messages (id numeric PRIMARY KEY); insert or replace into messages values (1); insert or replace into messages values (1); insert or replace into messages

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > which outputs one result (2), although the expected result would be > > empty. Sorry for my "what bug?" post. I forgot that the output was wrong! --jkl

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 09 Mar 2016 15:32:01 +0100 Jean-Christophe Deschamps wrote: > > select id from a where id not in (select a.id from b); > As I understand it, there is no more an a.id column in table b. It > looks like SQLite is trying to get clever ignoring the "a." qualifier. It's not ignoring the

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > select id from a where id not in (select a.id from b); > > > > which outputs one result (2), although the expected result would be > > empty. > > > > Thanks for the bug report. What bug? The query is valid SQL, and produces the

[sqlite] Multithreaded SQLite

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.

[sqlite] Multithreaded SQLite

On the OS standpoint, we are using multiple ones: Android, iOS and desktop (Windows, OSX and Linux). So we need to figure a solution that works with all these OSes. when I used the WAL mode, I got a schema locked, while in DELETE mode i got database locked. >>>If WAL is supported, then you can

[sqlite] Insert or Replace Trouble

On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote: > All, > > I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry > about duplicate entries without creating a trigger. Using the schema: > CREATE TABLE messages (id numeric); > > and using the SQL: > insert or replace into

[sqlite] "Circular" order by

Hi all, I'm looking for suggestions... What I want to achieve is to "roll" the result set of a query around by a certain amount (as in offset + wrap around). For example, given that: CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY); INSERT INTO t VALUES ('pen'), ('tree'), ('desk'), ('car'); I

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 2016/03/09 4:35 PM, Igor Tandetnik wrote: > On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote: >> At 15:16 09/03/2016, you wrote: >>> select id from a where id not in (select a.id from b); >> >> Shouldn't the engine bark on this, like it does on the modified version: >> >> select id from

[sqlite] sqlite3_update_hook() clarification

Sairam Gaddam wrote: > The documentation says that the function sqlite3_update_hook() is called > whenever a row is updated, deleted or inserted No. It says that this function is called to register a callback function that is called for these updates. > And I don't find any definition for this

[sqlite] Correlated subquery refers to wrong rowid - bug?

At 16:14 09/03/2016, you wrote: >On 3/9/2016 9:58 AM, R Smith wrote: >>On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >>>Yes, but why is that a problem? It is perfectly legal, and often >>>useful, for a subquery to refer to columns from enclosing query. >>>That's what makes it a *correlated*

[sqlite] Insert or Replace Trouble

Tilsley, Jerry M. wrote: > CREATE TABLE messages (id numeric); > > insert or replace into messages values (1); > > Why does this result in multiple rows of the same value? INSERT OR REPLACE deletes any old row that would cause a UNIQUE constraint violation. Without a UNIQUE (or PRIMARY KEY)

[sqlite] Correlated subquery refers to wrong rowid - bug?

At 15:16 09/03/2016, you wrote: > select id from a where id not in (select a.id from b); Shouldn't the engine bark on this, like it does on the modified version: select id from a where id not in (select zzz.id from b); "no such column zzz.id" As I understand it, there is no more an a.id

[sqlite] Correlated subquery refers to wrong rowid - bug?

In the question David Paton asks about a query like this: create table a(id integer primary key); create table b(id integer primary key); insert into a values (1), (2); insert into b values (1); select

[sqlite] Insert or Replace Trouble

There is no unique constraint (express or implied) in your schema, therefore no conflict occurs and it is possible to insert as many identical rows as your diskspace will hold. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces

[sqlite] Insert or Replace Trouble

All, I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry about duplicate entries without creating a trigger. Using the schema: CREATE TABLE messages (id numeric); and using the SQL: insert or replace into messages values (1); Why does this result in multiple rows of

[sqlite] compile switches: SQLITE_OMIT_ATTACH & SQLITE_OMIT_VIRTUALTABLE

On Tue, Mar 8, 2016 at 2:47 PM, Gert Corthout wrote: > hello, > when I compile the amalgation with these compile > switches:SQLITE_OMIT_VIRTUALTABLEorSQLITE_OMIT_VIRTUALTABLE > https://www.sqlite.org/compile.html If any of these options are defined, then the same set of SQLITE_OMIT_* options

[sqlite] ICU and FTS5

Will the ICU tokenizer work with FTS5, or does some work need to be done to port it over (slight pun intended).

[sqlite] Multithreaded SQLite

On 9 Mar 2016, at 5:00am, Philippe Riand wrote: > 1- Is it safe to share a single connection between all these threads > (assuming serialized mode)? In addition to Keith's excellent reply ... Are you using _prepare(), _step(), _finalize() ? If so then make sure you understand the

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 10:39 AM, Jean-Christophe Deschamps wrote: > Of course I use correlated subqueries a lot, but never had to re-select > a column from the enclosing query. Indeed, I'd say that most of the time > one uses columns from enclosing query as expressions, e.g. in comparison > operators or

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/16, Richard Hipp wrote: > > The fix is here: https://www.sqlite.org/src/info/1ed6b06ea3c432f9 > The "Pre-release Snapshot" at https://www.sqlite.org/download.html contains the fix. See https://www.sqlite.org/draft/releaselog/3_12_0.html for a summary of other changes in the Pre-release

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 9:58 AM, R Smith wrote: > On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >> Yes, but why is that a problem? It is perfectly legal, and often >> useful, for a subquery to refer to columns from enclosing query. >> That's what makes it a *correlated* subquery. > > True, but the OP's result

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/16, Clemens Ladisch wrote: > In the question > > David Paton asks about a query like this: > > create table a(id integer primary key); > create table b(id integer primary key); > insert into a values

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote: > At 15:16 09/03/2016, you wrote: >> select id from a where id not in (select a.id from b); > > Shouldn't the engine bark on this, like it does on the modified version: > > select id from a where id not in (select zzz.id from b); > > "no such

[sqlite] sqlite3 3.11 and 3.11.1 fail to build with ...

On 3/8/16, Michele Dionisio wrote: > Hi, > > downloading this sqlite-autoconf-3110100.tar.gz and compiling with > > ./configure CFLAGS="-DUSE_PREAD -DSQLITE_OMIT_DEPRECATED > -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_TRACE -DSQLITE_OMIT_EXPLAIN"

[sqlite] Creating & Dropping memory databases

On 03/09/2016 12:48 AM, Simon Slavin wrote: > On 8 Mar 2016, at 4:35pm, Dan Kennedy wrote: > >> I don't think it does that. sqlite3_shutdown() is for embedded platforms to >> shutdown sub-systems initialized by sqlite3_initialize(). Calling it with >> open connections will usually either leak

[sqlite] Multithreaded SQLite

I?m a bit lost with the multi-threaded concurrent access errors I?m getting, and looking for an advise on the best solution. Basically, I have a desktop/mobile app (single user) that embeds a tiny local http server. The UI is done through an embedded browser, calling the server for pages and