SQLite Forum

Timeline
Login

50 most recent forum posts by user stephan

2021-09-16
15:13 Reply: Javascript enforcement (artifact: 3ff7c0ff2f user: stephan)

... gives a pretty bad smell on the site like doing malicious stuff or spying out the visitors...

It's an anti-bot measure attempting to stop bots from sucking up the site's bandwidth and CPU. (Nowadays most crawlers can run JS, so it's less effective than it used to be.)

Please see this document about the topic in sqlite's sister project, fossil:

https://fossil-scm.org/home/doc/trunk/www/javascript.md

2021-09-09
06:38 Reply: How to insert duplicate rows? (artifact: e245ee0aaf user: stephan)

Why all the double quotes? They seem to be in very strange places.

Formatting quirk. Tap the "source" link on the top post to see the original formatting.

2021-08-27
03:31 Reply: Amalgamation file for specific extension (artifact: ca01212759 user: stephan)

I suspect the question you meant to ask is:

FWIW, i understood the OP as meaning: is there a set of -Dxxxx options they can provide which will filter the core sqlite lib out of the build, leaving only the desired extension(s) in the output object file.

That does sound like an interesting way to distribute extensions, with the caveat that those extensions would, de facto, be assumed to be compatible with the version of sqlite they're bundled with and may well not work with the hypothetical external sqlite copy the OP apparently already has.

2021-08-23
21:50 Reply: Explanation about C code style (artifact: ba114d586c user: stephan)

Is the sqlite3_release_memory() function the public API interface?

If you can find documentation for it in the public interface then yes, else no.

What about sqlite3PcacheReleaseMemory()

The functions and data types with sqlite3CamelCaseNames are all internal, not for client-side use. They may be modified or removed by the sqlite3 developers at any time with no concerns for backwards compatibility (in strong contrast to the documented public APIs, which have strong backwards compatibility guarantees).

2021-08-13
16:40 Reply: pragma integrity_check; Error: disk I/O error (artifact: a6800d75ce user: stephan)

So whether the culprit was some system bug, AV bug or just memory crap piled up is not clear, but the AV interference sound a sensible option. Wonder if the lib/tool can be made more robust on that.

AV interference is not an uncommon problem on Windows. Libraries like sqlite have zero insight into what such a tool is doing and how it may be interfering. The "real" (if snarky) answer is to make the OS more robust so that it doesn't need AV running 24/7 ;).

2021-08-12
22:21 Reply: pragma integrity_check; Error: disk I/O error (artifact: 03711ff569 user: stephan)

Funny, quitted then reloaded the DB checked as per original post: last integrity_check got I/O error, now instead (started with -stats):

This sounds like a side effect of a virus scanner.

17:16 Reply: LEFT AND RIGHT OUTER JOINS problem (artifact: 4e0bcf17bb user: stephan)

kod do diagramu

All questions about why a given query does or does not work "really should" include sample data. A diagram of the schema is not nearly as helpful as the data. Posting a question about a query without sample data makes it far more difficult for this group's volunteers to help you, which greatly reduces the chances of getting a useful answer.

16:57 Reply: LEFT AND RIGHT OUTER JOINS problem (artifact: 20f02095cf user: stephan)

Can You help me with what I am doing wrong

Without the data to accompany your query, all anyone can reasonably do is speculate.

15:03 Reply: pragma integrity_check; Error: disk I/O error (artifact: f411dde9d6 user: stephan)

that's on Windows 10 though [sqlite3 dll+tools SQLite 3.36.0 2021-06-18], whereas on (Debian, SQLite 3.16.2 2017-01-06) Linux the check yields just 'ok'.

Are you perchance accessing that db via a virtual filesystem mounted in the other OS? If so, i recommend eliminating that middleman and trying again. It seems highly unlikely that, on a local/native/non-virtual filesystem, you could get different results for bitwise-identical copies of a database file. It is never recommended to use sqlite db's across remote mounts, e.g. SMB or NFS, or virtualization layers which abstract a host OS's storage to a guest OS. That Way Lies Madness.

13:59 Reply: SQLite irrespective query (artifact: d28ee10891 user: stephan)

Sorry, You just gave me the answer.

Note that the formulations from David and Donald are simpler and should be preferred.

13:37 Reply: SQLite irrespective query (artifact: 17ba85c53d user: stephan)

WHERE (gender = 'F') OR (gender = 'M' AND date_joined >= '2020-01-01')

Since the db schema allows for NULL genders, this slight tweak seems appropriate:

WHERE (gender = 'F') OR (gender IS NOT 'F' AND date_joined >= '2020-01-01')

(Noting that IS NOT is required instead of <>.)

2021-08-10
04:18 Edit reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed (artifact: bd8bfe928c user: stephan)

I am not asking how the code works.

That's precisely what you asked for before: "plain language" explanation of the code.

I am asking why it makes sense when length(sqlar.blob)==sqlar.sz, there is no compression, when length(sqlar.blob)<sqlar.sz there is compression?

My previous response answers that. sqlar will never produce compressed data which is the same size or larger than the original. If compression (including the header) would be the same size or larger than the original, the compressed results are discarded and the original data is used in its place.

04:17 Reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed (artifact: 8c9bdcbc11 user: stephan)

I am not asking how the code works.

That's precisely what you asked for before: "plain language" explanation of the code.

I am asking why it makes sense when length(sqlar.blob)==sqlar.sz, there is no compression, when length(sqlar.blob)<sqlar.sz there is compression?

My previous response answers that. sqlar will never produce compressed data which is the same size or larger than the original. If compression would be the same size or larger than the original, the compressed results are discarded and the original data is used in its place.

2021-08-09
11:38 Reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed (artifact: c303dcd528 user: stephan)

I don't follow the code. Could you explain it in plain language how it determines whether it is compressed or not in the case that I mentioned?

In the very last block it checks whether the compressed data is smaller than the original. If so, it keeps the compressed data, else the compression is discarded and the original is retained.

2021-08-04
18:13 Reply: C api sqlite3_db_readonly returns -1 (artifact: d72290462d user: stephan)

so if anyone can give me some clues about the causes of that returning value -1

Your array indexes for values[...] are wrong, so the final one is passing invalid memory to bind. Array index start at 0, not 1. Bind indexes, on the other hand, start at 1. Whether that is the problem, i don't know, but it's certainly a problem. (Apologies for the brevity, but am working from a tablet.)

16:30 Reply: C api sqlite3_db_readonly returns -1 (artifact: 7ec8091f32 user: stephan)

I need to know which are the causes of the returnining value -1.

For anyone here to be able to speculate on that, you'll need to demonstrate the exact code which is failing for you, not a summary of it.

2021-08-02
02:27 Reply: Which version of SQLite supports VxWorks DKM mode ? (artifact: 90d4346581 user: stephan)

pls close this topic.

This software doesn't have a way of closing topics. The closest we can do is stop responding to it (starting right after this response), noting that someone else may come along in a year, find it relevant to them, and "reopen" it.

2021-08-01
10:46 Reply: (Deleted) (artifact: e257826a25 user: stephan)

Wouldn't it be nice and simple to have a '.attach' feature in the CLI ?

We do, it's just missing the dot prefix:

sqlite> attach database 'foo.db' as 'blah';

More succinctly:

sqlite> attach 'foo.db' as 'blah';

That's only 1 letter longer than your proposal: it removes the "." and adds an "as".

2021-07-23
21:29 Edit reply: sqlite3 LIKE clause with parameters for c api (artifact: 9cc83fb7a8 user: stephan)

"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"

In SQL the string concatenation operator is ||, so:

SELECT ... FROM WHERE col LIKE '%' || ?1 || '%'

Whether or not the % signs need to be doubled depends on the exact API you're passing the string to, but sqlite3_prepare_v2() does not expand/process percent signs so do not double them.

20:59 Reply: sqlite3 LIKE clause with parameters for c api (artifact: 9a7993e907 user: stephan)

"SELECT ... FROM ... WHERE col LIKE '%%'+?1+'%%';"

In SQL the string concatenation operator is ||, so:

SELECT ... FROM WHERE col LIKE '%' || ?1 || '%'

Whether or not the % signs need to be doubled depends on the exact API you're passing the string to, but sqlite3_prepare_v2() does expand/process percent signs so do not double them.

12:36 Reply: Parse failure doing UPDATE FROM (artifact: 7e1f3d21cf user: stephan)

SQLite 3.23.1, built from the amalgamation.

According to https://sqlite.org/news.html update from was added in 3.33.

2021-07-14
15:47 Reply: Need help with read only access to database (artifact: 8f64ee4f78 user: stephan)

Which version of sqlite properly allows for read only access as i've described?

If you're going to upgrade, upgrade to the latest. Calls for support of old versions, even "just" a year old, are likely to fall on deaf ears.

2021-07-09
08:52 Reply: Deleted rows not in FreeList (artifact: 46d4661aa9 user: stephan)

So my goal is to recover deleted rows from SQLite DB's that a particular application generates.

How sqlite deletes rows is an internal implementation detail which applications cannot sensibly rely upon. If you don't want it deleted, don't delete it. If your application didn't delete it, you've no business trying to recover it, nor any guaranty (from sqlite) that you can do so. A row deleted by sqlite must be treated like a block of memory which has been passed to free(): any use of it after that point has undefined behaviour.

03:23 Reply: Gimme my options! a feature to be (artifact: 2d75465d9e user: stephan)

That is the exact consideration which led to a stringification in the implementation.

Excellent :). From the phone at the bus stop in the rain i didn't bother checking out the implementation.

01:27 Reply: Gimme my options! a feature to be (artifact: 0f17abe79c user: stephan)

Examples:

(Writing from a bus stop, do can't test this..)

Don't those defines require quotes around the filenames? That bit of awkwardness (which requires wrapping up the whole -D with other quotes) is an argument for a fixed filename:

... '-DSQLITE_CUSTOM_INCLUDE="foo.h"'

The outer quotes being needed to keep bash from (IIRC) swallowing the inner ones. That seems like it's just asking for build pains.

(Again, though, i'm at a bus stop and can't currently test that.)

2021-07-08
19:22 Reply: edit() function usage (artifact: 7e221c46be user: stephan)

Is there something I'm missing?

My vague suspicion, based on a similar case once reported in the fossil project, is that your editor is forking and immediately returning to the calling process rather than waiting until the user explicitly exits it.

15:16 Reply: Gimme my options! a feature to be (artifact: 26ec623eda user: stephan)

Comments, for or against, or improvement suggestions, are welcome.

FWIW: definitely. i don't know for sure that it benefits from being configurable, though. Perhaps a slight simplification would suffice:

<compile> -DSQLITE_CUSTOM_H


... // in the amalgamation...

#if defined(SQLITE_CUSTOM_H)
#  include "sqlite3_custom.h"
#endif

One advantage to that approach, compared to a customizable name, is that people hopping from project to project would know the file's name without having to look it up in the build scripts. Since multiple such includes can't really be a thing, it "stands to reason" that a single, well-defined name "should" suffice.

0.02€ and all that, of course.

09:31 Reply: Segmentation fault in idxGetTableInfo (artifact: 14e7b68947 user: stephan)

git-master (commit 9d41caf361ea37e7bb91c3e0635bd9dca9f06040)

For future reference: the git commit numbers mean nothing in this project. The git export is a read-only, one-way export of the main source tree:

https://sqlite.org/src/timeline

You're more likely to get a useful response when referring to the "native" commit hashes instead of the git hashes.

2021-07-07
03:07 Reply: Query Detail Table (artifact: e5bef8312c user: stephan)

Below, I have included some SQL, contributed or linked by Keith Medcalf in this forum some time ago. It implements something like the catalog mentioned in Codd's 4th rule and available in heavy-weight DBMS's.

Sidebar for those attempting to use this schema via arbitrary applications: they might impose restrictions which break these queries. e.g. fossil disallows pragma_index_list():

$ fossil sql -R ../libfossil.fossil 
...
sqlite> select * from SysIndexColumns limit 10;
Error: unsafe use of virtual table "pragma_index_list"

$ sqlite3 ../libfossil.fossil 
...
sqlite> select * from SysIndexColumns limit 10;
table|blob|sqlite_autoindex_blob_1|0|3|uuid|0|BINARY|1
table|blob|sqlite_autoindex_blob_1|1|-1||0|BINARY|0
table|delta|delta_i1|0|1|srcid|0|BINARY|1
table|delta|delta_i1|1|-1||0|BINARY|0
...

Not a bug, just something to be aware of.

2021-07-03
15:13 Reply: The Untold Story of SQLite (artifact: bbb5685048 user: stephan)

The Untold Story of SQLite

i had to laugh a little at the interview's title, thinking "he gives at least 2-3 interviews about it a year, so how much 'untold' can there still be?" but after reading it realized that every such interview has indeed revealed new details. The bit about Knuth books' part in sqlite's development was new to me (and has got me eyeballing my 3-book set), as was the nature of the "broken pipe problem."

2021-07-02
05:45 Reply: Incorrect Result From SQLite Query (artifact: 1cb2b8e469 user: stephan)

SQLite Studio 3.2.1

SQLite Studio is a 3rd-party project neither maintained nor supported by this project. If you can reproduce your query result with the sqlite3 CLI application (as Larry attempts in his response), the libsqlite3 C API, or the System.Data dot-net bindings for that API, this is the correct forum. If you can't reproduce it in one those then the SQLite Studio team is your best bet.

2021-06-28
16:42 Reply: DOS? (artifact: a6c8e2b2a1 user: stephan)

I wonder if it is possible to get it compiled for use in a DOS application.

Here's a much older version:

https://www.ibiblio.org/pub/micro/pc-stuff/freedos/files/distributions/1.2/repos/pkg-html/sqlite.html

Good luck getting a halfway recent one to build.

2021-06-26
21:24 Reply: Better download file naming (artifact: f9cd0fd338 user: stephan)

Can you encode more than one version using that format and get the same filename result? I do not see how to do that.

The OP is mistaken about that but not:

The usage of the current year in the URL does not help either.

Having to know the year of a release is irritating every January.

10:31 Reply: Will SQLite support GQL or SQL/PGQ in the future? (artifact: 15dba1bee6 user: stephan)

Any opinions?

This topic and its very close cousin, stored procedures, have been beaten to death multiple times in the past.

Will SQLite support GQL or SQL/PGQ in the future?

Management summary: no, for diverse reasons.

See [78a60bdeec7c1ee9] for a recent thread on the topic.

2021-06-17
16:38 Reply: Unhandled exception on server application (artifact: 46aca22249 user: stephan)

gcc 6.3 manual says -fexceptions allows "C code to interoperate with exception handlers written in C++".

That's a compiler-specific extension. Portable code never relies on such things.

12:35 Reply: Unhandled exception on server application (artifact: df23a426a4 user: stephan)

How do I correct use sqlite3_exec in my C++ program?

If your callback is guaranteed to never throw an exception then it's fine as-is. i suspect, however, that:

gaSQLResult.Add( argv[i] ? argv[i] : "NULL" );

can potentially throw an exception, presumably a std::bad_alloc.

C libraries have no way of properly handling C++ exceptions which pass through them, so it's critical (for their proper behavior) that no C++ exceptions ever do so. If your callback has to throw an exception, catch it in that callback and return the relevant C-level result code. In the case of sqlite3_exec(), returning any non-0 value will cause sqlite3_exec() to fail properly:

https://www.sqlite.org/c3ref/exec.html

If an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns SQLITE_ABORT without invoking the callback again and without running any subsequent SQL statements.

To protect against a leaking exception, wrap up the body of your function in a try/catch. If no exception is thrown, return 0, else return any non-0 value.

10:40 Reply: Unhandled exception on server application (artifact: a63f57e424 user: stephan)

SQLite is pure C. It couldn’t throw a C++ exception if it wanted to.

Related: nor should a C++ application which implements callbacks for a pure C library ever allow a C++ exception to propagate from such a callback. That's a recipe for memory leaks and corruption in mixed C/C++ code.

Thus if SQLCallBack is capable of throwing:

try { bResult = sqlite3_exec( gpSQL, sQuery, SQLCallBack, 0, &zErrMsg ) == 0; } catch (std::exception& ex) { wxMessageBox( ex.what()); }

then its use here is fundamentally broken.

2021-06-09
17:59 Reply: Apps development using sqlite (artifact: 5d3a342fcc user: stephan)

... i want to known with language of programmation will be good for sqlite.?

Nearly every single programming language out there has at least one sqlite binding, so the question largely boils down to how suitable any given language is for whatever other features you want. No matter what language you choose, you'll be able to use sqlite with it. (There are very, very few exceptions to that, and no mainstream-language exceptions which i'm aware of.)

2021-05-31
08:04 Reply: Question on internals: Sync between WAL and B Tree (artifact: d3003af495 user: stephan)

Then you would end up making the changes again? If I am incrementing a column, wouldn't that get incremented twice?

When the WAL is applied to the same db N times, it overwrites/replaces the same records each time. The end effect is as if it had been applied one time, even if it's physically applied two or more times.

07:44 Reply: Question on internals: Sync between WAL and B Tree (artifact: 6fdbf7129c user: stephan)

Then on system restart, we would end up make changes to B Tree again? How does SQLite prevents this

Why would sqlite need or want to prevent that? Step (2) is a logical part of step (3). We cannot persistently know that step (2) succeeded unless recording that (in step (3)) succeeds. Thus if step (3) does not complete then step (2) is not complete. Ergo, it will replay the log the next time it can. That's a feature, not a bug.

2021-05-24
18:23 Edit reply: Suggestion on how to read the forums for latest items (artifact: 929d932d8c user: stephan)

Is there a query that will just show topics (one line per topic) that have had postings in the past 24 hours?

The days=N parameter is what you're looking for, but there's not a way to reduce the output to just the topic strings.

Example: /timeline?days=1&y=f

(Note that "N days" really means "N times 24 hours," so days=1 may span 2 days.)

Edit: adding &ss=c to the URL flags will reduce the output further but won't eliminate the timestamps.

18:22 Edit reply: Suggestion on how to read the forums for latest items (artifact: 929604a3f1 user: stephan)

Is there a query that will just show topics (one line per topic) that have had postings in the past 24 hours?

The days=N parameter is what you're looking for, but there's not a way to reduce the output to just the topic strings.

Example: /timeline?days=1&y=f

(Note that "N days" really means "N times 24 hours," so may span 2 days.)

Edit: adding &ss=c to the URL flags will reduce the output further but won't eliminate the timestamps.

17:31 Edit reply: Suggestion on how to read the forums for latest items (artifact: b5d289abe2 user: stephan)

Is there a query that will just show topics (one line per topic) that have had postings in the past 24 hours?

The days=N parameter is what you're looking for, but there's not a way to reduce the output to just the topic strings.

Example: /timeline?days=N&y=f

(Note that "N days" really means "N times 24 hours," so may span 2 days.)

Edit: adding &ss=c to the URL flags will reduce the output further but won't eliminate the timestamps.

17:29 Reply: Suggestion on how to read the forums for latest items (artifact: fa75402d9f user: stephan)

Is there a query that will just show topics (one line per topic) that have had postings in the past 24 hours?

The days=N parameter is what you're looking for, but there's not a way to reduce the output to just the topic strings.

Example: /timeline?days=N&y=f

(Note that "N days" really means "N times 24 hours," so may span 2 days.)

2021-05-23
14:05 Reply: sql parser error (artifact: 0d729d540b user: stephan)

I test the sql with mysql ,and I received the right result! Why the sql can't run on sqlite,could some one help me?

The syntax of passing a parenthesized multi-column expression to WHERE is apparently a MySQL-ism, not supported by sqlite (at least in this context). Not all SQL runs on all SQL engines.

2021-05-18
02:13 Reply: memory vs mmap (artifact: 5af6fe28dc user: stephan)

So I guess what I am requesting is a pragma or whatever to force pages to be contiguous or whatever is required so that the efficient :memory: logic can be used.

Generically speaking, that would likely be impossible to do: code which needs to perform inserts would need to continually reallocate that block to add space for new records, invalidating any pointers held to it by higher-level code. Any and all pointers which referenced anything in that block would need to be accounted for and adjusted on every such realloc. You're only interested in the read-only case, but the VFS itself doesn't know that and has to have code paths for write mode as well. An mmap allocation is effectively one contiguous block, but any malloc-based non-trivial read/write data structure (a btree variant, in this case) cannot possibly be sensibly managed that way except, perhaps, in very specialized use cases which are far more limited in scope than sqlite's internals.

2021-05-17
15:18 Reply: memory vs mmap (artifact: 049a54e17e user: stephan)

Would you expect a file in ramdisk, perhaps just after a copy to /dev/null, WHEN OPENED WITH PRAGMA MMAP, to be near-identical speed to the malloc() (ie :memory: followed by "restore from")

No...

because it goes through identical Sqlite code and accesses identical in-physical-RAM memory?

Because that's not my expectation of the API. However...

Note - this is a question about Sqlite - :memory: vs mmap pointer to file ON RAMDISK. I think it is on-topic, as it is about Sqlite internals. I want to confirm the identical codepath. ie identical xFetch or whatever calls.

We're going to have to wait on someone who's familiar with what sqlite actually does when the mmap pragma is enabled to enlighten us, but my outsider's guess is that the code paths do not become identical once mmap is enabled.

Even if the paths do become identical: as Keith pointed out, mmap does not necessarily eliminate I/O. OS-level file content caching is a "black box" feature applications cannot rely on. It might work as you intend on any given day on any given machine and it might not. That is two or three layers removed from where sqlite has any influence.

12:52 Edit reply: memory vs mmap (artifact: 83b293b96f user: stephan)

In that case, would you expect a file in ramdisk, perhaps just after a copy to /dev/null, to be near-identical speed to the malloc() because it goes through identical Sqlite code and accesses identical in-physical-RAM memory?

Not in the slightest: a db on a RAM disk is, for sqlite3's purposes, on external storage (and sqlite has no way of knowing otherwise), so it's going through the filesystem-based API. The in-memory VFS is the fastest you're going to get, hands down, no comparison.

12:51 Reply: memory vs mmap (artifact: a2e3f27ce1 user: stephan)

In that case, would you expect a file in ramdisk, perhaps just after a copy to /dev/null, to be near-identical speed to the malloc() because it goes through identical Sqlite code and accesses identical in-physical-RAM memory?

Not in the slightest: a db on a RAM disk is, for sqlite3's purposes, on external storage (and sqlite has no way of knowing otherwise). The in-memory VFS is the fastest you're going to get, hands down, no comparison.

11:26 Reply: FYI HN: Geocode-sqlite: Geocode rows in an SQLite database table (artifact: 33bbb772e5 user: stephan)

https://news.ycombinator.com/item?id=27179909

Just FYI: you don't need to duplicate the URL in that type of markup. Leaving an empty pair of square braces uses the URL itself as the link text.

https://news.ycombinator.com/item?id=27179909

More ↓