[sqlite] Troubleshooting corrupt indexes

We've found a few database instances with index corruption, and I'm unsure how to troubleshoot it. The symptom is SELECT queries failing with SQLITE_CORRUPT. I've looked at the database files, and `pragma integrity_check` spits out five "row missing from index" errors on one index, and "wrong

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

> On Mar 15, 2020, at 4:54 AM, no...@null.net wrote: > > By chance or by design most emails to this list, like yours above, came > wrapped at a length suitable for text-based readers. Yet every message > I've received from the forum is a shocking cut-words-in-half affair in > my terminal.

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

> On Mar 12, 2020, at 1:17 PM, Richard Hipp wrote: > > I have set up an on-line forum as a replacement for this mailing list: Oh crap. > The Forum is powered by Fossil. I appreciate that you like to 'eat your own dog food'. However, I strongly disagree with your using a homemade forum

Re: [sqlite] Intersecting multiple queries

> On Feb 28, 2020, at 11:49 PM, Hamish Allan wrote: > > Again, I may be making incorrect assumptions. Remember the old Knuth quote about the danger of premature optimization. What’s the size of your data set? Have you tried making a dummy database of the same size and experimenting with

Re: [sqlite] more efficient JSON encoding: idle musing

> On Feb 25, 2020, at 6:12 AM, J Decker wrote: > > other than that; if space is really a concern, maybe a zip layer? In my experience, the concern is more about speed than size. Given the raw string/blob data from a SQLite column, and a specific property name/path, how fast can you find its

[sqlite] Setting auto_vacuum=2 doesn't work after setting journal_mode=WAL

I just discovered that none of our databases are auto-vacuuming, even though we set auto_vacuum to 'incremental' when a database is created, and periodically call 'pragma incremental_vacuum'. If I go into the CLI, open a database and run "pragma auto_vacuum", it returns 0. After some

Re: [sqlite] more efficient JSON encoding: idle musing

> On Feb 21, 2020, at 4:20 AM, Wout Mertens wrote: > > I was wondering if the JSON extension could not do the same thing: for each > table, keep a hidden stash of object layouts, and store the values as > sqlite primitives. (you'd be able to disable this, in case the layouts > rarely repeat)

Re: [sqlite] more efficient JSON encoding: idle musing

> On Feb 21, 2020, at 4:20 AM, Wout Mertens wrote: > > In JavaScript, objects are key-value collections with unique keys, where the > order of the keys is important. JSON is not JavaScript. The order of keys is NOT significant in JSON, and many, many JSON implementations parse JSON objects

Re: [sqlite] Performance Issue on Large Table

> On Feb 20, 2020, at 5:24 PM, Chip Beaulieu wrote: > > CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`) > CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`) > CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses` > (`BibleID`,`Book`,`Chapter`,`VerseNumber`) I believe

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

> On Feb 20, 2020, at 10:48 AM, Richard Hipp wrote: > > That assumption is not correct for SQLite, which does you a > cryptographically strong PRNG. And the SQLite PRNG is seeded from > /dev/random on unix. Not quite; I'm looking at the function unixRandomness() in SQLite 3.28. It's seeded

Re: [sqlite] Virtual table function calls

> On Feb 13, 2020, at 12:52 PM, David Jones wrote: > > sqlite> select F,G,H,attr(H,3) from summary; # show formula used > to calculate column H. Maybe pass the column name as a string, i.e. `attr('H',3)`? It sounds like your `attr` function needs to know the _identity_ of the

Re: [sqlite] Compiling SQLite Encryption Extension for ARM processor

> On Feb 13, 2020, at 10:51 AM, Subodh Pathak wrote: > > I am looking for help to configure machine to compile SEE for ARM. I am > using Android mobile Samsung G7. You have to use a cross-compiler, a version of GCC that runs on your platform but generates ARM-Linux code. Specifically, to

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

> On Feb 10, 2020, at 10:27 PM, Хусаинов Динар > wrote: > > Problem: the query takes 3000 ms (3 seconds) on my machine to complete. If I > create a real table with the SAME structure, insert the SAME data into it, > and run the SAME query, get the SAME result back, it takes 10-15 ms

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

> On Feb 12, 2020, at 5:30 AM, Hick Gunter wrote: > > This is documented here https://sqlite.org/partialindex.html > and here > https://sqlite.org/queryplanner.html > > Specifically, SQLIte does not prove theorems

[sqlite] Optimizer limitation with partial indexes

I'm running into a problem with partial indexes; apparently the query optimizer isn't smart enough. I currently have indexes of the form CREATE INDEX Index1 ON Table (expr1) CREATE INDEX Index2 ON Table (expr2) where expr1 and expr2 are expressions involving table columns. The

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

> On Feb 11, 2020, at 2:10 AM, Digital Dog wrote: > > Thanks for enlighening again. It was just a thought. It seems it would be a > lot of design and code to maintain the performance while preventing the > original problem from happening. Not worth the trouble. But maybe > increasing the

Re: [sqlite] Make sqlite3 database searchable on Mac OS X

> On Feb 10, 2020, at 8:10 PM, Peng Yu wrote: > > It seems that sqlite3 databases are not searchable by Spotlight on Mac > OS X. Is there a way to make them searchable? Thanks. How would Spotlight know what tables or columns to index? It doesn't understand what database schema mean, and it

Re: [sqlite] loading extension csv.c

> On Feb 7, 2020, at 9:11 AM, chiahui chen wrote: > > /usr/include/sqlite3ext.h:437:53: note: expanded from macro > 'sqlite3_vsnprintf' > > #define sqlite3_vsnprintf sqlite3_api->vsnprintf > > ~~~ ^ > >

Re: [sqlite] notify all processes of database modification

> On Feb 7, 2020, at 6:23 AM, Kees Nuyt wrote: > > Anyway, SQLite doesn't have such a mechanism by itself. > Maybe inotify is useful to you : > > https://en.wikipedia.org/wiki/Inotify > http://man7.org/linux/man-pages/man7/inotify.7.html >

Re: [sqlite] VTable Column Affinity Question and Change Request

> On Feb 5, 2020, at 6:56 PM, Keith Medcalf wrote: > >> It probably doesn’t parse that declaration or >> figure out from it what the declared types of the columns are. > > Actually it does, and this is documented for the sqlite3_declare_vtab > function -- and in fact the column names and

Re: [sqlite] VTable Column Affinity Question and Change Request

> On Feb 5, 2020, at 9:58 AM, Keith Medcalf wrote: > > It seems that "column affinities" are not respected in Virtual Table > implementations -- that is the value that is returned is the datatype > provided by the the vtab_cursor sqlite3_result_* function and the "column > affinity" from the

Re: [sqlite] Compile error with SQLITE_OMIT_CTE (3.28)

> On Jan 31, 2020, at 9:55 AM, Jens Alfke wrote: > > If I define SQLITE_OMIT_CTE and compile the amalgamation (3.28.0), the build > fails due to two remaining calls to > sqlite3WithAdd() in the parser. This function is neither declared nor > implemented when SQLITE_O

[sqlite] Compile error with SQLITE_OMIT_CTE (3.28)

If I define SQLITE_OMIT_CTE and compile the amalgamation (3.28.0), the build fails due to two remaining calls to sqlite3WithAdd() in the parser. This function is neither declared nor implemented when SQLITE_OMIT_CTE is defined. case 286: /* wqlist ::= nm eidlist_opt AS LP select RP */ {

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

> On Jan 28, 2020, at 3:18 AM, Richard Hipp wrote: > > Things like MySQL-embedded and H2 run a "server" as a thread instead > of as a separate process. ... So this is really the > same thing as a server using IPC except that the server runs in the > same address space as the client. I see

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

> On Jan 27, 2020, at 2:18 PM, Richard Hipp wrote: > > But more recently, "serverless" has become a popular buzz-word that > means "managed by my hosting provider rather than by me." I hate this buzzword. It's especially confusing because peer-to-peer architectures are also validly

Re: [sqlite] BUG(?) in FTS5

> On Jan 23, 2020, at 6:47 AM, mailing lists wrote: > > The following SELECT statement fails with the error "unable to use function > MATCH in the requested context": This is an annoying but documented limitation of FTS, not a bug. The MATCH operator can’t be used inside an OR expression. It

[sqlite] Request: Allow virtual tables to make use of partial indexes

I believe I've found another limitation for efficient querying of virtual tables. The xBestIndex call communicates column constraints, but it doesn't specify whether a constraint's value is known at compile time, nor pass such a compile-time value to xBestIndex. This means that the

Re: [sqlite] Best way to store key,value pairs

> On Jan 13, 2020, at 1:45 PM, James K. Lowden wrote: > > So, basically, a nomalized design requires too much use of INSERT? > You're making an efficiency argument here, or maybe > ease-of-implementation assertion. For me, inserting one header row or > 20 is the same coding effort (still

Re: [sqlite] Best way to store key,value pairs

> On Jan 12, 2020, at 4:12 PM, James K. Lowden wrote: > > What is the motivation behind this advice? It's completely unnecessary. Thanks for your opinion, James! I disagree. RFC822 headers are schemaless, and in a Usenet or email database they have rather high volume (probably 20+ per

Re: [sqlite] Test failures on GPFS

> On Jan 11, 2020, at 2:58 PM, T J wrote: > > I was interested in using sqlite over GPFS. The standard advice on using SQLite over a network file system is “don’t do it.” Even if you find the rare file system that handles locks properly, you’ll likely have performance issues. A

Re: [sqlite] Best way to store key,value pairs

> On Jan 8, 2020, at 3:13 AM, Simon Slavin wrote: > > I believe Jens' point is valid, as long as you don't have to search/scan > headers. You can even do that — to search for a specific header’s value, just create an index on json_extract(headers, ‘$Header-Name’), then in a query use that

Re: [sqlite] Best way to store key,value pairs

Consider encoding the headers as JSON and storing them in a single column. SQLite has a JSON extension that makes it easy to access values from JSON data in a query. You can even index them. Simon’s suggestion (a row per header) is correct in theory, but the large numbers of headers you’ll be

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

> On Jan 2, 2020, at 11:54 AM, Doug wrote: > > I know there has been a lot of talk about what can and cannot be done with > the C calling interface because of compatibility issues and the myriad set of > wrappers on various forms. I’m having a hard time letting go of a possible > 25%

Re: [sqlite] New SQL functions available for testing only.

> On Jan 1, 2020, at 8:03 AM, Richard Hipp wrote: > > expr_implies_expr(A,B) > > Return non-zero if expression A implies expression B. See > https://www.sqlite.org/src/artifact/971e442fd5?ln=5277-5299 for > details. This routine is used to determine if a partial index is > usable. Thanks!

Re: [sqlite] Causal profiling

> On Dec 31, 2019, at 7:03 AM, Richard Damon wrote: > > 1) The C API between separate compilations is very well established, and > fully documented in most environments. Agreed. APIs between library boundaries generally need to be C. This is pretty easy to do though (I've done it multiple

Re: [sqlite] Causal profiling

> On Dec 30, 2019, at 6:23 PM, Alexander Vega wrote: > > -fdevirtualize > -fdevirtualize-speculatively I believe those are C++ optimizations, specific to C++ virtual methods. What SQLite is doing is similar to a class with virtual methods, but done “by hand” in plain C, so the optimizer

Re: [sqlite] Causal profiling

> On Dec 30, 2019, at 7:19 AM, Doug wrote: > > I am suggesting that if the we added the global calls to the underlying > functions to the API - that is, the functions that are called by the function > table indirection - then one could code the application to call the > underlying

Re: [sqlite] Causal profiling

> On Dec 26, 2019, at 3:45 PM, Doug wrote: > > Can you explain what the API is that you use to "set concurrency levels per > connection", please? Is it a parameter on open() or its own function call? > And how would the API break, exactly? sqlite3_config(), specifically the

Re: [sqlite] Causal profiling

> On Dec 25, 2019, at 2:53 PM, Doug wrote: > > I wrote an application in Qt which uses SQLite. Therefore, I invoke SQLite > functions with some wrapper. For a 9% performance improvement in SQLite using > the direct call versus indirect call (as discussed in the talk), cannot the > wrapper

[sqlite] Compiling SQLite without the database storage?

This may sound crazy, but is there a way to compile SQLite without its B-tree and table code? This would be for a use case with _only_ virtual tables — i.e. SQLite being used as a SQL query engine on top of a different data store*. It would be nice not to drag in too much unused code. —Jens *

Re: [sqlite] Multi layer JSON query

> On Dec 19, 2019, at 7:45 PM, No.1 Perfect <757171...@qq.com> wrote: > > How can I count the amountand numof goods when the customer or > goodsname is different. Querying the contents of arrays is kind of complicated. SQL doesn't understand arrays, so the query has to use a "table-valued

Re: [sqlite] mysql -> sqlite problem

> On Dec 19, 2019, at 4:49 PM, Mike Bentley wrote: > > Is it true that "Each index name must be unique in the database"? Yes. You're right that the docs don't seem to spell this out, but since the syntax to delete an index is "DROP INDEX ", you couldn't have multiple indexes with the same

Re: [sqlite] Slightly offtopic: indexes vs. hash tables

Thank you, Simon. That was an interesting article, and even better, it has a link to a book-length PDF of “Modern B-Tree Techniques” that is *solid gold*. I’ve been wanting to learn more about b-trees, indexing, query planning, etc. and this book goes way beyond anything I’ve found previously.

Re: [sqlite] Coding standard

—Jens > On Dec 12, 2019, at 11:23 AM, Warren Young wrote: > > I wouldn’t dismiss this warning I wouldn’t dismiss a warning about the full scenario. (In fact I wasn’t aware that assignment to a field might overwrite pad bytes; that’s good to know.) But warning about every call to memset is

Re: [sqlite] Coding standard

> On Dec 12, 2019, at 10:36 AM, Simon Slavin wrote: > > For instance CodeSonar reports every use of memset() because you /can/ leak > uninitialised bits of memory using memset() (CERT C Section 3.6 DCL39-C). > But it has no way to check whether what you're doing with memset() does >

Re: [sqlite] sqlite sync over network

> On Dec 9, 2019, at 7:43 AM, George wrote: > > (litesync - SQLite Replication and Synchronization) > litesync seems to be a version of sqlite made by Ramos Bernardo from Brazil. Interesting, but I don't see how they can make those kinds of claims without running into conflicts on primary

Re: [sqlite] sqlite sync over network

> On Dec 5, 2019, at 8:05 AM, George wrote: > > Changes -> bidirectional. All terminals can save to db. Amount of collected > data it's not big and frequency between readings will be minimum 2sec. When > we push more readings. Data itself it's very small. Like reading temperature > from a

[sqlite] virtual tables vs. expression-based indexes

I'm considering using a virtual table to query an external-to-SQLite data store. However, I've carefully read about the xBestIndex method, and it appears that virtual tables cannot have indexes on expressions; or rather that the SQLite query engine can't make use of such indexes, only indexes

Re: [sqlite] Concurrency Question

> On Nov 23, 2019, at 7:17 AM, Dan Kennedy wrote: > > This should only happen if you are using shared-cache mode. Don't use > shared-cache mode. Shared-cache mode also breaks Isolation between connections — during a transaction, other connections will see the writer’s intermediate state.

[sqlite] Request: Combining skip-scan with 'max' optimization

I'm following up on my "Optimizing `SELECT a, max(b) GROUP BY a`" thread from a few weeks ago, rephrasing it as a clearer enhancement request. ACTUAL BEHAVIOR: A query of the form `SELECT a, max(b) GROUP BY a` runs slowly (O(n) with the number of table rows), even if there is an index on (a, b

Re: [sqlite] What is the C language standard to which sqlite conforms ?

> On Nov 21, 2019, at 7:01 AM, Richard Hipp wrote: > > The memset() just forces the bug to the surface in builds where the > ckmalloc()/ckfree() routines of TCL are using caching that prevents > valgrind/ASAN from seeing the use-after-free. The memset() is not > part of the bug fixx itself,

Re: [sqlite] What is the C language standard to which sqlite conforms ?

> On Nov 19, 2019, at 5:29 AM, Dennis Clarke wrote: > > Yes I have tried gcc 9.2.0 and the whole process fails in the tests > and no it will not compile as C90 code. Have you tried just not forcing strict compliance? Which is the way people normally build SQLite? —Jens

Re: [sqlite] What is the C language standard to which sqlite conforms ?

> On Nov 18, 2019, at 2:44 PM, Dennis Clarke wrote: > > On some machines with different compilers I get good > results using C99 strict compliance. On other machines, such as those > running Red Hat Enterprise Linux, I get terrible results. Why does it matter to you? I usually worry about

Re: [sqlite] Things you shouldn't assume when you store names

> On Nov 11, 2019, at 11:09 AM, Jose Isaias Cabrera wrote: > > Compared to me, you are a genius in everything, but you just lack a little > bit of understanding about other languages and their localization behavior. > As a Technical Project Manager for 12 years on my last job, all of these

Re: [sqlite] Things you shouldn't assume when you store names

> On Nov 11, 2019, at 9:39 AM, Jose Isaias Cabrera wrote: > > However, space is cheap now It isn't. A sizable fraction of all software development is done for devices with under a megabyte of RAM. (IoT and embedded are huge markets.) And remember, we're talking on the email forum for a

Re: [sqlite] Things you shouldn't assume when you store names

> On Nov 11, 2019, at 9:30 AM, Jose Isaias Cabrera wrote: > > Say that I am writing a report and I only want to print the first 20 > characters of a string, that would be something like, Hang on — why exactly 20 characters? Of text in an arbitrary language, which is to be displayed in an

Re: [sqlite] Things you shouldn't assume when you store names

> On Nov 11, 2019, at 7:49 AM, Jose Isaias Cabrera wrote: > > if you want to count characters in languages such as Arabic, Hebrew, Chinese, > Japanese, etc., the easiest way is to convert that string to UTF32, and do a > string count of that UTF32 variable. No, the easiest way is to ask your

Re: [sqlite] Things you shouldn't assume when you store names

> On Nov 10, 2019, at 4:03 AM, Richard Damon wrote: > > Actually, 'The Artist whose name formerly was Prince' (which wasn't his > name, his legal name was an unpronounceable pictograph), breaks every > computer system I know. Unicode Character PRINCE (U+1F934)

Re: [sqlite] Things you shouldn't assume when you store names

On Nov 9, 2019, at 1:09 PM, sky5w...@gmail.com wrote: > > In this case, data modelers hoping to save a column. arrggg. > It flies in the face of data normalization and pushes the problem down the > line. But you _cannot_ normalize people’s names; that’s the exact point of that article. Anything

Re: [sqlite] Reading a SharePoint file

> On Nov 8, 2019, at 12:21 PM, Jose Isaias Cabrera wrote: > > Yeah, that is what I am doing now. I was trying to save time to just be able > to read a few tables and see if I needed to update it, so then, download it > and upload it. But now, I have to download it, and read it, and delete

Re: [sqlite] Reading a SharePoint file

> On Nov 8, 2019, at 11:57 AM, Jose Isaias Cabrera wrote: > > Is there any way that SQLite can read a file on a Sharepoint site? ie >

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

> On Nov 8, 2019, at 11:08 AM, Mario M. Westphal wrote: > > The EXPENSIVE_FUNCTION function is referred multiple times in the update > statement. But it always returns the same result (for any given row). There was a similar thread (that I started, I think) from two years ago with subject

Re: [sqlite] SQLite with branching

> On Nov 7, 2019, at 9:02 AM, Bernardo Ramos wrote: > > If you are interested in just the performance without the branching feature, > there are at least 3 options: > > 1. SQLigthning: I was thinking in updating it to the last version of SQLite That would be awesome! I have looked at it a

Re: [sqlite] Limit on number of columns in SQLite table

> On Nov 6, 2019, at 9:25 AM, Digital Dog wrote: > > If there are millions or billions of rows > in the data set I consider it big data and the only reasonable format for > storing it is a column store format. There are many types of stores for "big data". My employer, Couchbase, has

Re: [sqlite] SQLite with branching

> On Nov 5, 2019, at 1:27 AM, Dominique Devienne wrote: > > AFAIK, that was one of the goals of SQLite4 [1], to change the backend to LSM. LMDB (LiteTree's back-end) doesn't use LSM; it's a B-tree manager. The speedup appears to come from a combination of techniques like eliminating caching

Re: [sqlite] SQLite with branching

> On Nov 4, 2019, at 4:57 AM, Simon Slavin wrote: > > That's one of the reasons that the source code for SQLite is public: so that > people can add the features they want. Totally agree. However, when you go off the mainline of SQLite you lose some things, like easy updating to new SQLite

Re: [sqlite] Roadmap?

> On Oct 27, 2019, at 1:50 PM, Thomas Kurz wrote: > > But not compatible to standards from Open Geospatial Consortium, as far as I > know. That's the nice thing about standards: there are so many of them. > Which requires additional conversions, and considering that geodata usually > have

Re: [sqlite] Roadmap?

> On Oct 26, 2019, at 5:12 PM, Thomas Kurz wrote: > > Geospatial support would be one of the features I would *LOVE* to see in > SQLite :-) SQLite has had geospatial support for years via the r-tree extension, and more recently GeoJSON. As for time stamps ... I’ve been using SQLite since

[sqlite] Optimizing `SELECT a, max(b) GROUP BY a`

I'm looking for an optimal way to query the maximum value of one (integer) column as grouped by another (string) column. For example, say I've got a table of timestamped data points from many named sources, and I want to find each source's latest timestamp. I'm seeing decent performance if the

Re: [sqlite] Roadmap?

 > On Oct 20, 2019, at 12:53 AM, Thomas Kurz wrote: > > many "playground" gadgets keep being implemented (like virtual columns, > virtual tables, FTS3/4/5, ...), I suspect you are used to database servers, and haven’t used SQLite as an embedded library inside an app (its primary use case.)

Re: [sqlite] Limit on number of columns in SQLite table

—Jens > On Oct 17, 2019, at 1:56 AM, Mitar wrote: > > So why not increase the limit to 2 billion Why should SQLite make changes, which would introduce performance problems if used, just to save your particular application the trouble of concatenating some vectors into single columns, when

Re: [sqlite] Network file system that support sqlite3 well

—Jens > On Oct 16, 2019, at 3:37 AM, Peng Yu wrote: > > I will need to use the actual files to test for dependency (just as > the dependency that can be used by GNU make) I don’t understand what that means. You want to use a makefile that checks the mod date of the database? —Jens

Re: [sqlite] Limit on number of columns in SQLite table

> On Oct 16, 2019, at 6:08 AM, Mitar wrote: > > Quite > some of datasets we are dealing with have 100k or so columns. There was a thread about this a few months ago. You Should not store every number of a huge vector in a separate column. You don’t need to individually query on every

Re: [sqlite] Network file system that support sqlite3 well

> On Oct 15, 2019, at 3:47 PM, Peng Yu wrote: > > I'd like to use sqlite3 db files on many compute nodes. But they > should access the same storage device for the sqlite3 db files. Why not use an actual client-server database system like MySQL? It's optimized for this use case, so it incurs

Re: [sqlite] Table was deleted on macOS

Was the database in a folder managed by Dropbox or Google Drive or iCloud Drive, i.e. where some background agent could replace it with a 'newer' copy from another computer? —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Last record

> On Oct 15, 2019, at 9:44 AM, Simon Slavin wrote: > > You can then remember the value(s) of some column(s) of the row returned, and > watch for the same one(s) when you do your desired SELECT. As long as all the result rows are unique… All of these workaround seem more expensive/complex

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

> On Oct 11, 2019, at 9:19 AM, Mike Bayer wrote: > > GNU Mailman is still very widely used and IMO does the job very well Its web interface is like something from 1997. In particular, it makes reading archives very painful, clicking through to one message at a time. I’d recommend groups.io —

Re: [sqlite] insertion of a single record taking more than 200 msec some times

> On Oct 11, 2019, at 7:43 AM, GopiKrishna Parisa > wrote: > > One weird thing is for some insertions, it's taking more than 200 msec for > single record where as others takes around 20 to 40 (avag 27 msec). Those sound like numbers I’d expect for committing a transaction. (The longer time

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

> On Oct 9, 2019, at 10:02 AM, Keith Medcalf wrote: > > SUBSTR(name, 0, ?) is an expression, so unless you have an index on that > expression, then an index cannot be used to SEARCH for the rows. That's accurate in general. However, there _is_ a very similar special-case optimization for

Re: [sqlite] Date time input

> On Oct 8, 2019, at 5:34 AM, Jose Isaias Cabrera wrote: > > No, that is not what I was trying to say or ask. Not even close. What I was > trying to say, and most of you missed it was, that if I give date a date > format, and I also provide the format of how that date is to be understood, >

Re: [sqlite] Date time input

> On Oct 7, 2019, at 6:17 AM, Jose Isaias Cabrera wrote: > > I have to ask this question: Why is it that the date function does not take > a '4/5/2019' and returns '2019-04-05'? Because that's locale-dependent. Some countries, like the US, use month/day/year; most other countries use

Re: [sqlite] Calling a java function from a trigger

> On Sep 29, 2019, at 5:05 PM, Faria wrote: > > Is it possible to call a java function from a trigger in SQLite? Yes, but you'd have to implement a C function that calls the Java function via JNI, then register the C function with SQLite, then call that function in your trigger. —Jens

[sqlite] LIKE optimization when the LHS is an indexed expression?

I've been reading about the LIKE optimization[1]. One of the constraints on its use is: > if case_sensitive_like mode is enabled then the column must indexed using > BINARY collating sequence, or if case_sensitive_like mode is disabled then > the column must indexed using built-in NOCASE

Re: [sqlite] Safe to use SQLite over a sketchy network?

> On Sep 24, 2019, at 3:48 PM, Keith Medcalf wrote: > > There are not, to my knowledge, any client/server database systems that will > work properly if the database resides on a network filesystem (meaning remote > multi-access). The "client" is remote from the "server" because the "client"

Re: [sqlite] Safe to use SQLite over a sketchy network?

> On Sep 24, 2019, at 3:22 PM, Jose Isaias Cabrera wrote: > > Even on a great network, you can have problems, so when you say "sketchy", > then definitely there will be problems. And even with a perfect network and perfect networked filesystem, it's still possible to get denial-of-service

Re: [sqlite] Safe to use SQLite over a sketchy network?

> On Sep 24, 2019, at 2:14 PM, Randall Smith wrote: > > My question is: Do the commit-or-rollback semantics of SQLite transactions > work if the connection to the file system is weird? What filesystem is it — SMB, NFS, AFP, …? I'm not an expert, but in general I think the database file

Re: [sqlite] SQLite - macOS

> On Sep 23, 2019, at 9:53 AM, Pierre Clouthier > wrote: > > sqlite3_exec("PRAGMA encoding = \"UTF-8\";") That isn't necessary. SQLite defaults to UTF-8. In most cases SQLite doesn't interpret the byte sequences in a string. It just knows it's using an 8-bit character set and leaves it

Re: [sqlite] How to install REGEXP support?

On Sep 19, 2019, at 7:21 PM, Peng Yu wrote: > > My question is `But it is not clear how to install it for sqlite3 installed by > homebrew.` That sounds more like a question to ask the Homebrew community; it isn’t really related to SQLite directly. SQLite is just making a system call to load a

Re: [sqlite] How to install REGEXP support?

> On Sep 17, 2019, at 8:13 AM, Peng Yu wrote: > > I can compile it. Where do I put the .dylib file? According to the documentation Warren linked to, you use a SQL statement or shell dot-command to load the extension, giving the path to the library. —Jens

Re: [sqlite] How to install REGEXP support?

> On Sep 16, 2019, at 1:44 PM, Peng Yu wrote: > > But it is not clear how to install it for sqlite3 installed by > homebrew. Does anybody how to install it? Thanks. SQLite doesn't come with a regexp implementation; it has to be added by an extension. I searched the website; there's a simple

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

> On Sep 13, 2019, at 10:57 AM, Hick Gunter wrote: > > This is faster if the number of keys in the list is small relative to the > number of records in the table. > If the number of keys is similar to the number of records in the table, then > a simple full table scan may be faster.

Re: [sqlite] Fastest way to SELECT on a set of keys?

> On Sep 13, 2019, at 1:30 PM, Keith Medcalf wrote: > > The only thing that is clear is that where the overhead of executing each > select is significant it is clearly better to execute fewer of them. Thanks for the research, Keith! In my case the per-query overhead is lower since I'm

[sqlite] Documentation error in comment in carray.c

There's a mistake in the documentation block-comment at the start of carray.c: **sqlite3_bind_value(pStmt, i, aX, "carray", 0); The function should be sqlite3_bind_pointer. —Jens ___ sqlite-users mailing list

[sqlite] Fastest way to SELECT on a set of keys?

If I have a set of primary keys (let's say a few hundred) and need to fetch data from the table rows with those keys, what's the fastest way to do so? The options seem to be: (a) Execute "SELECT … FROM table WHERE key=?", once for each key. (b) Execute "SELECT key, … FROM table WHERE key IN

Re: [sqlite] How to increase performance when inserting a lot of small data into table using indices

> On Sep 12, 2019, at 12:33 PM, mailing lists wrote: > > the application also runs on mobile devices and the insertion (excluding > indexing) also takes a couple of minutes. This means that the user might > switch to another app during insertion and - in the worst case - the system > is

Re: [sqlite] SQLITE and the memory

> On Sep 8, 2019, at 2:12 PM, Philippe RIO <51...@protonmail.ch> wrote: > > I use the windows task manager to see how the memory is used I don't use Windows, but I know that in any modern OS, memory usage is a very vague thing and is tricky to measure. There are quite a few numbers that mean

Re: [sqlite] Why is a multiple MATCH clause not allowed in an FTS query?

> On Aug 30, 2019, at 12:43 AM, mailing lists wrote: > > SELECT * FROM myData WHERE (content MATCH 'one') AND (body MATCH 'two'); > > What is the reason that the above query is not allowed I'm curious too. This limitation is documented somewhere, but not the reason why. For programs that

Re: [sqlite] sqlite: see: encryption

> On Sep 2, 2019, at 11:41 PM, Vadiraj Villivalam > wrote: > > With the open os like android providing keystore and key generation > mechanism, we want to switch to this secure key generation mechanism and > avoid generating key ourselves. As the key store does not allow the key > itself to

Re: [sqlite] database like file archive

> On Aug 27, 2019, at 12:21 PM, Keith Medcalf wrote: > > Everything that has been touched by a third-party is inherently > untrustworthy. Thus it is and thus it has always been. Yes. I have a lot of experience with network coding and security, so I'm aware of this, thanks. My question

Re: [sqlite] database like file archive

> On Aug 27, 2019, at 7:06 AM, Philip Bennefall wrote: > > There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of the > Sqlite3 repository. "An SQLite Archive is an ordinary SQLite database file that

  1   2   3   4   5   6   >