[sqlite] How to use multiple connections with locking_mode=exclusive

Richard implied in this bug that you can use locking_mode=exclusive when you have a single process using that database, but that process has multiple threads & connections: https://bugzilla.mozilla.org/show_bug.cgi?id=993556 However, I've tried this and can't get it to work - the second open

[sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

I'm trying to follow Richard's advise to work around this issue, which is: "Is that database ever used by more than a single process. (Use by multiple threads using separate connections does not count - I mean really used by multiple processes with their own address space.) If not (and I think

[sqlite] Index on computed value?

Is it possible to have an index on a computer value? E.g. I have a 40 byte value in one of my columns. I only want an index over the first 4 bytes of it. However, I don't really want to repeat those 4 bytes inside another column on the main table. Is there any way to accomplish that? -

[sqlite] Forcing a manual fsync in WAL/NORMAL mode

In the majority of my application, I'm fine running in WAL/NORMAL and lose some committed transactions if the application crashes. (Which actually just happened to me on iOS - I thought that can only happen on a full O/S crash - not just app. But oh well). However, every now and again I need to

Re: [sqlite] Forcing a manual fsync in WAL/NORMAL mode

Simon Slavin-3 wrote > When you lose data are you ever losing part of a transaction ? SQLite is > allowed to lose entire transactions, back to some END/COMMIT statement. > It should not be losing parts of transactions. Or, at least, when you > reopen the database using the SQLite library it

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't usable in any way shape or form. It will inevitably lead to catalog corruption if you hard-reboot OSX, even without the database or application open. We've tried FULLSYNC and F_FULLFSYNC, but it makes no difference.

Re: [sqlite] Thread safety of serialized mode

If you look at the original underlying NT I/O architecture that Cutler implemented - it is a thing of beauty that's based in async patterns, and not threads. It was the Win32 wrappers over the NT subsystem that tried to make things "easier" for developers to deal with, which forced synchronous

Re: [sqlite] Thread safety of serialized mode

fety of serialized mode On Fri, 17 Feb 2017 04:10:09 +0000 Deon Brewis <de...@outlook.com> wrote: > If you look at the original underlying NT I/O architecture that Cutler > implemented - it is a thing of beauty that's based in async patterns, > and not threads. ... > If instead NT initial

Re: [sqlite] Non-unique columns in unique index

; address for a fully functional demo licence > > On 1 March 2017 at 21:54, Simon Slavin <slav...@bigfraud.org> wrote: > >> >> On 1 Mar 2017, at 9:41pm, Deon Brewis <de...@outlook.com> wrote: >> >> > Yeah ok, but that is paltry compared with the g

Re: [sqlite] Non-unique columns in unique index

nal. If space is a problem, use only the single Unique index. You cannot optimize for everything, pick your favourite thing and optimize for that. Cheers, Ryan On 2017/03/01 7:00 PM, Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

Hipp Sent: Wednesday, August 31, 2016 7:34 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Expression Indexes - can I project the expression value from the index? On 8/31/16, Deon Brewis <de...@outlook.com> wrote: > > CREATE INDEX FOOIND

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

dex? On 8/31/16, Deon Brewis <de...@outlook.com> wrote: > Thanks! I'll be willing to test this beta once it's available. > > One more thing - I think there is a bug here in the query planner I'm pedantic about this because it is important: What you describe is not a bug. A bug

[sqlite] Expression Indexes - can I project the expression value from the index?

Let's say I have an expression index: CREATE INDEX FOOINDEX on FOO(func(col)) And then I use that index: SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42; I can see that it will use the stored value, since if I put a breakpoint on func, it won't hit. Good and well. HOWEVER, if I

Re: [sqlite] Complicated join

OVER PARTITION BY ... One can dream... - Deon -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Thursday, September 15, 2016 1:47 PM To: SQLite mailing list Subject: Re:

Re: [sqlite] Memoization in sqlite json1 functions

It has more to do with how you parse JSON - if you want to build it into an object tree, sure, then you're obviously dead in the water with any kind of json. If you however can use a forward-only push or pull parser like a SAX or StAX parse, it's a different story. I'm using a StAX-like pull

Re: [sqlite] Memoization in sqlite json1 functions

That would be nice. I've resorted to a few horrible hacks like this: SELECT parse(data), extract("name"), extract("address"), release(data) FROM some_table; It works, but it relies on LTR parsing of arguments (which it does now, but I seriously doubt is a guarantee), as well as

Re: [sqlite] Memoization in sqlite json1 functions

ite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Thursday, March 23, 2017 6:09 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Memoization in sqlite json1 functions > On Mar 23, 2017, at 3:17 PM, Deon Brewis <de...@outl

Re: [sqlite] Memoization in sqlite json1 functions

@mailinglists.sqlite.org> Subject: Re: [sqlite] Memoization in sqlite json1 functions > On Mar 24, 2017, at 4:48 PM, Deon Brewis <de...@outlook.com> wrote: > > Yeah ok, if you take I/O hits then things like memory pre-fetching makes zero > difference. We're more in the bu

Re: [sqlite] SQLite does not support ARM platform?

l, be successfully compiled and then run in the machine? Why cannot SQLite source code? I only need to generate the LIB file to add it to my project and the DLL file to copy to the machine. What command line do you use for your target machine? Regards Jaime -----Original Message- From: sqlit

Re: [sqlite] SQLite does not support ARM platform?

"That suggests me that the code is prepared for MSVC also, but it is not actually." The SQLITE code works just fine on an MSVC build. Have been using it for many years. What platform are you targeting? The MSVC ARM compiler is only meant for Windows RT devices - is that really what you're

[sqlite] Search semantics with a virtual table?

How do I convince SQLITE to give me SEARCH semantics over a virtual table instead of SCAN semantics? e.g. I have: explain query plan SELECT * FROM vtable WHERE value = 12345 0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1: And I'm returning in xBestIndex: pIdxInfo->idxNum = 1;

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

-Original Message- > On 8/31/16, Deon Brewis <de...@outlook.com> wrote: > > Let's say I have an expression index: > CREATE INDEX FOOINDEX on FOO(func(col)) > > And then I use that index: > SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42; > > I ca

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

sqlite] Expression Indexes - can I project the expression value from the index? On 4/5/17, Deon Brewis <de...@outlook.com> wrote: > Hi, > > Just following up - is this tracked somewhere in a feature/bug database? > We are aware of the feature request and discussed it internally jus

Re: [sqlite] Massively multithreaded SQLite queries

Can you give an example of an Intel Processor SKU with half-assed cores? There's HyperThreading of course, but I don't think anybody has ever considered HyperThreading to be separate cores. - Deon -Original Message- From: sqlite-users

Re: [sqlite] Non-unique columns in unique index

-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to use the same index to enforce unique constraints, as well as > giving a covered result for other queries

Re: [sqlite] Non-unique columns in unique index

"But look how much space and processing time it would take up" Can you clarify what you mean by "space" ? The processing time argument I understand. I think this is one of those things that if the database engine doesn't internally support it, it can't really be emulated. Sure would be

Re: [sqlite] Non-unique columns in unique index

:38 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Non-unique columns in unique index On 1 Mar 2017, at 9:11pm, Deon Brewis <de...@outlook.com> wrote: > "But look how much space and processing time it would take up" > >

Re: [sqlite] Non-unique columns in unique index

I live my life one "indexed by" at a time. - Deon -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 2, 2017 7:10 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Are you by change using memory mapped IO (MMAP_SIZE something other than 0)? This does not work on OSX. Not even remotely. I tracked an issue down in November 2015, and was able to trivially corrupt a database 100% of the time using the repro steps below. This happens long after our app gets

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

org> Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS On 18 Apr 2017, at 9:50am, Deon Brewis <de...@outlook.com> wrote: > “From the OSX documentation: > > Note that while fsync() will flush all data from the host to the drive (i.e. > the "perm

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

ct: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS > On Apr 18, 2017, at 1:50 AM, Deon Brewis <de...@outlook.com> wrote: > > Are you by change using memory mapped IO (MMAP_SIZE something other than 0)? > > This does not work on OSX. Not even remotely. I track

Re: [sqlite] Many ML emails going to GMail's SPAM

I would LOVE for this to be a Forum. I'd be willing to be a paid member/sponsor to help pay for running the forum software. I prefer XenForo for Forum software personally - it supports clipboard image copy/paste into the forum, which vBulletin doesn't. - Deon -Original Message-

[sqlite] Understanding query optimizer choices

I have a query that joins with a virtual table where the optimizer has a choice of picking between 2 indexes for something, and it picks by far the wrong one. I'm trying to understand how it all works. I understand the output of both 'explain' and 'explain query plan' but I can't see from

[sqlite] Is there ever a perf advantage to LIMIT outside of a subquery?

e.g. If you do: SELECT c1 from t1 ORDER BY c2 LIMIT 5; vs. just running the query without the "LIMIT" clause and taking the top 5 rows programmatically? Obviously in the case of a subquery this makes a difference: select count(*) from (select * from t1 limit 5); But would it ever in a

[sqlite] Row count of a partial index ?

Is there a direct way to query how many rows are in a partial index? e.g. select count(*) from table indexed by partial_index (this does not work - no query solution). I know I can repeat the WHERE clause expression in the query, but this is for a dynamically constructed index and knowing

[sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

Looks like a missed optimization opportunity here. Well, more than that - there doesn't appear to be a way to get SQLITE to automatically use a partial index if a similar non-partial index exists. E.g. create table Foo(a,b,c,d,e); create index Foo_inx on Foo(a,b,c); create index Foo_partial_inx

Re: [sqlite] database locked on select

By one connection doing SELECT and UPDATE, do you mean multi-threaded mode and using the connection from 2 threads? - Deon -Original Message- From: sqlite-users On Behalf Of Simon Slavin Sent: Sunday, May 27, 2018 3:39 PM To: SQLite

Re: [sqlite] Can I create a stealth index?

mailing list <sqlite-users@mailinglists.sqlite.org> Cc: de...@outlook.com Subject: Re: [sqlite] Can I create a stealth index? On 1/16/18, Deon Brewis <de...@outlook.com> wrote: > > I have seen a few cases where a newly added index would start showing > up uninvited in old, prev

Re: [sqlite] WAL and pragma uncommitted

What is the level of consistency (or rather inconsistency) for read_uncommitted? If you start with: INSERT INTO Woz(Foo, Bar) Values(1,1) And a (normal) writer thread updates the 2 columns: UPDATE Woz SET Foo=2, Bar=2 Can a read_uncommitted thread read the value from the row as: Foo=1, Bar=2 ?

[sqlite] Can I create a stealth index?

Can I create an index in SQLITE that is only ever used in an 'indexed by' clause and not automatically picked up the query optimizer? I have seen a few cases where a newly added index would start showing up uninvited in old, previously tested queries and bring performance down by an order of

[sqlite] Convincing SQLITE to use alternate index for count(*)

I have a table with 2 indexes: CREATE TABLE Foo ( Id Integer, GuidId blob PRIMARY KEY ) WITHOUT ROWID; CREATE UNIQUE INDEX FooId ON Foo(Id); CREATE INDEX FooBar ON Resource(Bar(GuidId)); When I do: SELECT COUNT(*) FROM Foo; The query plan always uses the FooBar

Re: [sqlite] Convincing SQLITE to use alternate index for count(*)

...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, January 26, 2018 1:23 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Convincing SQLITE to use alternate index for count(*) On 1/26/18, Deon Brewis <d...@mylio.com> wrote: > I have

[sqlite] Header corruption

I’m trying to track down SQLITE corruptions that seems to corrupt our databases in a consistent way. (Running on 3.20.1). This isn’t related to a crash or hardware failure. The app is running and reading and writing to the database fine, and then suddenly we start getting a SQLITE_NOTADB

Re: [sqlite] Header corruption

On 02/06/2018 11:57 PM, Deon Brewis wrote: > I’m trying to track down SQLITE corruptions that seems to corrupt our > databases in a consistent way. (Running on 3.20.1). > > This isn’t related to a crash or hardware failure. The app is running and > reading and writing to th

Re: [sqlite] Header corruption

To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Header corruption On 7 Feb 2018, at 3:16pm, Deon Brewis <de...@outlook.com> wrote: > So this looks more like something is overwriting the memory of Page1 before > SQLITE writes it back to disk. Tha

[sqlite] Recommended tool to read SQLITE btree?

Anybody have a recommendation for a tool that can read/show/interpret a SQLITE file at the BTREE level? Want to be able to decode the links between pages, figure out what all the data mean etc. And should be able to work on a corrupted file. - Deon

[sqlite] iOS Watchdog and database corruption

Interesting case of database corruption on iOS here. Our main thread was waiting for a worker thread to exit. The worker thread was doing a sqlite3Close, which in turn did a checkpoint. The application got watchdog terminated by iOS because the main thread was taking too long (waiting for the

Re: [sqlite] iOS Watchdog and database corruption

lists.sqlite.org> Subject: Re: [sqlite] iOS Watchdog and database corruption On 21 Feb 2018, at 2:35pm, Deon Brewis <de...@outlook.com> wrote: > The application got watchdog terminated by iOS because the main thread was > taking too long (waiting for the sqlite3close on the worker t

Re: [sqlite] iOS Watchdog and database corruption

I do. I'll have to request permission from the customer though to share it - who will potentially be looking at the file? (Just so I can share names and background with the customer to put him at ease). - Deon -Original Message- From: sqlite-users

Re: [sqlite] C++ compiler

Ditto. Large C++ project - no problem using SQLite. Are you trying to compile sqlite.c as a C++ file? That won't work obviously, you have to compile as C and link it in. The sqlite3.h header however can be pulled into any C++ file. - Deon -Original Message- From: sqlite-users

[sqlite] SQLITE touches unchanged expression indexes on update

It seems like there is an opportunity for improvement on updates if an index contains expressions. In the following example: CREATE TABLE Foo(x, y, z); CREATE INDEX FooX on Foo(x); CREATE INDEX FooZ on Foo(z); CREATE INDEX FooLenZ on Foo(length(z)); explain UPDATE foo SET x=1 WHERE rowid=1; I

[sqlite] What does this assert mean? (sqlite3_mutex_held)

assert( sqlite3_mutex_held(pPage->pBt->mutex) ); Inside this stack: #4 0x000103e89384 in releasePageNotNull at sqlite3.c:62589 #5 0x000103e87a94 in btreeReleaseAllCursorPages at sqlite3.c:61069 #6 0x000103e8ee00 in sqlite3BtreeCloseCursor at sqlite3.c:64809

Re: [sqlite] Header corruption

: 1503010020 15030100 352518400 (decimal version) OpenSSL - Deon -Original Message- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Deon Brewis Sent: Tuesday, February 6, 2018 8:57 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Header corruption

ter Da Silva Sent: Tuesday, April 17, 2018 1:13 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Header corruption On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of de...@

Re: [sqlite] Header corruption

2018 1:54 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Header corruption On Apr 17, 2018, at 2:07 PM, Deon Brewis <de...@outlook.com> wrote: > > One just has to be in the mindset that on unix based platforms, a socket is a > fi

Re: [sqlite] Header corruption

ite] Header corruption > Le 17 avr. 2018 à 22:07, Deon Brewis <de...@outlook.com> a écrit : > > closesocket(_socket); // posix socket > SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket > above) These two statements are inherently wrong, in this order. Firs

[sqlite] SQLITE_CONFIG_MULTITHREAD needed for connection pool?

The model we use for SQLITE is to use a connection pool (connections opened via sqlite3_open_v2). We have many threads, but only one thread can use a connection at a time - a thread takes it out of the pool, and when done, returns it to the pool. The only exception to this is calls to:

[sqlite] Programmatically corrupting a database

I am trying to write unit tests for some error detection & recovery scenarios and would like to simulate the following type of corruptions: 1) Header corruption (easy) 2) Corrupting a specific SQL Table/Index so that it can be read, but inserts/updates will fail 3) Corrupting a specific SQL

Re: [sqlite] Programmatically corrupting a database

rammatically corrupting a database On 3/16/18, Deon Brewis <de...@outlook.com> wrote: > I am trying to write unit tests for some error detection & recovery > scenarios and would like to simulate the following type of corruptions: > > 1) Header corruption (easy) > 2) Cor

[sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

How do you actually get a SQLITE_CANTOPEN_ISDIR error? In order to get an extended result code, we need to pass a sqlite3* connection, but you don't have that if the file can't be opened in the first place. Like... if it was a directory. I see how this is implemented internally - it generally

[sqlite] Index on expression goes corrupt from valid SQL

I was just reading through this issue: https://www.sqlite.org/src/info/343634942dd54ab Does this bug have any other symptoms other than as specified in the report above? Reason I'm asking is that we are facing quite a bit of database corruption right now. We use a lot of expression indexes

Re: [sqlite] Index on expression goes corrupt from valid SQL

sqlite.org> Subject: Re: [sqlite] Index on expression goes corrupt from valid SQL On 23 Mar 2018, at 12:54am, Deon Brewis <de...@outlook.com> wrote: > However, what we see doesn't generally exhibit like the bug describes. The > bug as reported gives errors like this: > "ro

[sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

Is there anything that SQLITE_DETERMINISTIC would ever be used for in an aggregate? (Function with xStep/xFinal as opposed to just xFunc). I assume it's ignored, but just checking. - Deon ___ sqlite-users mailing list

[sqlite] 64-column covering index limit clarification

Hi, I seem to have run into a limit where SQLITE doesn't use an index correctly if an indexed column is over the 64th column in the table. It's a partial index like: CREATE INDEX idx ON table(A, B DESC, C, D) WHERE A > 0 Where A and B are columns 70 and 72 on 'table'. I know about the

Re: [sqlite] 64-column covering index limit clarification

Yes a non partial index beyond column 64 works as I would expect. - Deon > On Oct 18, 2018, at 12:34 PM, Shawn Wagner wrote: > > Does a normal non-partial index make a difference in the query plan? > >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis wrote: >> >>

Re: [sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

] Is SQLITE_DETERMINISTIC ignored on an aggregate? On 9/27/18, Deon Brewis wrote: > Is there anything that SQLITE_DETERMINISTIC would ever be used for in > an aggregate? (Function with xStep/xFinal as opposed to just xFunc). > > I assume it's ignored, but just checking. I believe you are corre

[sqlite] Calling sqlite3_create_module from a DLL

I have a DLL that makes a series of sqlite3_create_function_v2 calls. It all works fine and the DLL is usable. I've tried adding a sqlite3_create_module into the same DLL, but I get an assert in: sqlite3_mutex_try over here: assert( sqlite3GlobalConfig.mutex.xMutexTry ); xMutexTry (really

Re: [sqlite] Another Partial Index optimization opportunity (INSERT)

s.sqlite.org] On Behalf Of Deon Brewis Sent: Thursday, September 20, 2018 11:30 AM To: SQLite mailing list Subject: [sqlite] Another Partial Index optimization opportunity (INSERT) Hi, I previously reported that an UPDATE of a table containing an Expression and/or Partial Index will unneces

Re: [sqlite] integrity_check failure on 3.26 with expression indexes (wrong # of entries)

Sent: Tuesday, September 25, 2018 11:54 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] integrity_check failure on 3.26 with expression indexes (wrong # of entries) On 09/26/2018 01:14 PM, Deon Brewis wrote: > I am receiving an integrity check failure on 3.26 on the 9/19/2

[sqlite] integrity_check failure on 3.26 with expression indexes (wrong # of entries)

I am receiving an integrity check failure on 3.26 on the 9/19/2018 drop. It's not everywhere and it may be coincidental, but it may also be related to the new change where expression indexes aren't updated if their columns aren't modified. I'm getting these integrity check failures: wrong #

[sqlite] Another Partial Index optimization opportunity (INSERT)

Hi, I previously reported that an UPDATE of a table containing an Expression and/or Partial Index will unnecessarily touch the expression and/or partial index. I see both are now fixed in the 3.26.0 2018-09-19 codebase. Thank you so much - it works great! However, while testing it, I also

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

Right-click on the sqlite3.c file in your Visual Studio project, click on properties, then under C/C++ go to Precompiled Headers, and change the setting for "Precompiled Header" to "Not using precompiled header". Make sure to do this for "All Configurations" and "All Platforms" (at the top of

[sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

The 'NOT IN' query at the bottom should not return any results. It returns '2'. Repro: = create table foo(y, z); insert into foo values(1,1); insert into foo values(2,2); CREATE INDEX fooZ on Foo(y) where z=1; create table bar(x); insert into bar values(1); insert into bar values(2); select

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

: drhsql...@gmail.com On Behalf Of Richard Hipp Sent: Saturday, December 8, 2018 5:11 AM To: SQLite mailing list Cc: Deon Brewis ; o...@integral.be Subject: Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists Thanks for the bug report and for the repro script

Re: [sqlite] SQLITE touches unchanged expression indexes on update

;key=r[14..15] >>26Column 0 0 14 00 >r[14]=Foo.x >>27IdxDelete 3 14200 >>key=r[14..15] >>28IdxInsert 1 1 2 2 00 key=r[1] >>29IdxInsert

Re: [sqlite] SQLITE touches unchanged expression indexes on update

inal Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Wednesday, September 12, 2018 7:05 AM To: SQLite mailing list Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update On 9/11/18, Deon Brewis wrote: > It seems like there is an opportunity for improve

Re: [sqlite] SQLITE touches unchanged expression indexes on update

om: drhsql...@gmail.com On Behalf Of Richard Hipp Sent: Saturday, September 15, 2018 2:46 PM To: SQLite mailing list Cc: de...@outlook.com Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update On 9/11/18, Deon Brewis wrote: > It seems like there is an opportunity fo

[sqlite] Does SQLITE ever optimize index creation based on another index?

Given the SQL below, FooX is a covered index for x on Foo. I want to create FooXB as a second index on x in Foo. Since 'x' is covered on FooX it should be cheaper to build FooXB from index FooX, than from table Foo. However, as far as I can tell from the from the opcodes of the index creation

[sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

sqlite3_interrupt is documented as: “It is safe to call this routine from a thread different from the thread that is currently running the database operation” SQLITE_CONFIG_SINGLETHREAD is documented as: “puts SQLite into a mode where it can only be used by a single thread” Which one wins ?

Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

sday, August 14, 2019 6:19 AM To: SQLite mailing list Subject: Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD On 8/14/19, Deon Brewis wrote: > sqlite3_interrupt is documented as: > “It is safe to call this routine from a thread different from the > thread that is curr

[sqlite] Is rootPage a reasonable estimate for index age?

If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one of them is older? This is for a dynamic index sweep to decide which one to drop. It doesn't matter if it's sometimes wrong, if the index is needed again it will be re-created. I just don't want to get into a situation

Re: [sqlite] Is rootPage a reasonable estimate for index age?

for index age? On 8/7/19, Deon Brewis wrote: > If I have 2 indexes 'A' and 'B', can I use rootPage to determine which > one of them is older? > > This seems to work for me, or is it just because my database has been > vacuumed recently and if a database has a lot of empty pages it

Re: [sqlite] New word to replace "serverless"

In C/C++ the closest concept is a Header Only Library. Except that SQLITE is not only C+++, and it's not header only... Library Only Implementation? In-Proc / In-Thread Library? Self Contained Library? Looks like I'm on a generally "Library" theme here... - Deon -Original Message-

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

WHERE x IN (?1,?2,?3,?4...,?1000 ) And the IN clause is filled by a list or array that's held inside the calling application memory rather than in SQLITE. The alternate to this is to create a virtual table wrapper over the internal datasets of the app. Which is of course better, but harder.

Re: [sqlite] Row length in SQLITE

On Behalf Of Simon Slavin Sent: Wednesday, January 22, 2020 5:24 PM To: SQLite mailing list Subject: Re: [sqlite] Row length in SQLITE On 22 Jan 2020, at 11:44pm, Deon Brewis wrote: > Is there any way to get the length of rows in a table / index in sqlite? Do you mean the count of r

Re: [sqlite] New word to replace "serverless"

BYOT -> Bring Your Own Thread Put another way: SQLITE is a BYOT Library. - Deon -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Monday, January 27, 2020 2:19 PM To: General Discussion of SQLite Database Subject: [sqlite] New word to replace "serverless" For

Re: [sqlite] New word to replace "serverless"

This is a very important design distinction, not just implementation detail, If you know and internalize up front that SQLITE will run only on the thread you give it, you can architect your application better from the beginning and not e.g. go down one path initially and wonder how the get

Re: [sqlite] New word to replace "serverless"

> "What is the word for that programming methodology that existed since the > beginning when there were no threads and everything was single-task?" Real Mode. - Deon > On Jan 28, 2020, at 12:37 PM, Roman Fleysher > wrote: > > What is the word for that programming methodology that existed

[sqlite] Record serialization infrastructure of SQLITE

Inspired by the json encoding thread. We have a need for multi-dimensional data (like json), and currently use a record format for that that is derived from the SQLITE format with Serial Type/Size stored in varint Huffman encoding of twos-complement. It is a great fast, compact storage format.

[sqlite] Row length in SQLITE

Is there any way to get the length of rows in a table / index in sqlite? DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but it's an aggregate sum & max per page - I need the data per row (cell). - Deon ___ sqlite-users