SQLite Forum

Timeline
Login

50 most recent forum posts by user stephan

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

2021-05-12
12:37 Reply: SQLite 3.7.5 and above versions available in Redhat 6 (artifact: 8566f03e79 user: stephan)

Is SQLite 3.7.5v and above versions available in Redhat 6?

That's a question for your distribution's maintainers. The people participating in this project have no influence over which sqlite versions are used or available on any given platform.

2021-05-11
13:33 Reply: Fast way to insert rows in SQLite (artifact: 90be6b6e2f user: stephan)

I tried increasing it to 8KB, I didn't see much difference. Let me try again with 8KB and 16KB and report here.

Writing it out to an in-memory db, provided you have enough RAM, will tell you your theoretical maximum speed - writing it to persistent storage faster than that won't be possible. You could use such a test to estimate the approximate performance cost/overhead of the storage layer.

2021-05-06
06:29 Edit reply: select * from fcc where unique_system_identifier='4350131'; returns 1000+ records (artifact: ab1acc54da user: stephan)

Any program (that I know of) just doesn't cut it with importing something like that

Such files are trivial to slice up from the CLI. e.g. to split them into 100k-line chunks using the bash shell and sed:

$ x=1; y=1; chunk=100000; while sed -n $x,$((x + $chunk))p EN.dat > EN_$y.dat; do test -s EN_$y.dat || break; x=$((x + $chunk + 1)); y=$((y + 1)); done

$ wc -l EN.dat 
1505803 EN.dat
$ wc -l EN_*.dat 
   100001 EN_10.dat
   100001 EN_11.dat
   100001 EN_12.dat
   100001 EN_13.dat
   100001 EN_14.dat
   100001 EN_15.dat
     5788 EN_16.dat
        0 EN_17.dat
   100001 EN_1.dat
   100001 EN_2.dat
   100001 EN_3.dat
   100001 EN_4.dat
   100001 EN_5.dat
   100001 EN_6.dat
   100001 EN_7.dat
   100001 EN_8.dat
   100001 EN_9.dat
  1505803 total

That 0-sized file at the end will always exist using this simple automation - simply remove it before importing the chunks.

EDIT: fixed off-by-one error which caused each successive group to start with the last line of the previous group. Now the line counts match up.

06:29 Edit reply: select * from fcc where unique_system_identifier='4350131'; returns 1000+ records (artifact: 20a789e7ad user: stephan)

Any program (that I know of) just doesn't cut it with importing something like that

Such files are trivial to slice up from the CLI. e.g. to split them into 100k-line chunks using the bash shell and sed:

$ x=1; y=1; chunk=100000; while sed -n $x,$((x + $chunk + 1))p EN.dat > EN_$y.dat; do test -s EN_$y.dat || break; x=$((x + $chunk)); y=$((y + 1)); done

$ wc -l EN.dat 
1505803 EN.dat
$ wc -l EN_*.dat 
   100001 EN_10.dat
   100001 EN_11.dat
   100001 EN_12.dat
   100001 EN_13.dat
   100001 EN_14.dat
   100001 EN_15.dat
     5788 EN_16.dat
        0 EN_17.dat
   100001 EN_1.dat
   100001 EN_2.dat
   100001 EN_3.dat
   100001 EN_4.dat
   100001 EN_5.dat
   100001 EN_6.dat
   100001 EN_7.dat
   100001 EN_8.dat
   100001 EN_9.dat
  1505803 total

That 0-sized file at the end will always exist using this simple automation - simply remove it before importing the chunks.

EDIT: fixed off-by-one error which caused each successive group to start with the last line of the previous group. Now the line counts match up.

06:21 Reply: select * from fcc where unique_system_identifier='4350131'; returns 1000+ records (artifact: 1fa942f21e user: stephan)

Any program (that I know of) just doesn't cut it with importing something like that

Such files are trivial to slice up from the CLI. e.g. to split them into 100k-line chunks using the bash shell and sed:

$ x=1; y=1; chunk=100000; while sed -n $x,$((x + $chunk))p EN.dat > EN_$y.dat; do test -s EN_$y.dat || break; x=$((x + $chunk)); y=$((y + 1)); done

$ ls -la EN_*.dat
-rw-rw-r-- 1 stephan stephan 13360335 May  6 08:18 EN_10.dat
-rw-rw-r-- 1 stephan stephan 13340967 May  6 08:18 EN_11.dat
-rw-rw-r-- 1 stephan stephan 13286610 May  6 08:18 EN_12.dat
-rw-rw-r-- 1 stephan stephan 13306218 May  6 08:18 EN_13.dat
-rw-rw-r-- 1 stephan stephan 13322897 May  6 08:18 EN_14.dat
-rw-rw-r-- 1 stephan stephan 13336756 May  6 08:18 EN_15.dat
-rw-rw-r-- 1 stephan stephan   772532 May  6 08:18 EN_16.dat
-rw-rw-r-- 1 stephan stephan        0 May  6 08:18 EN_17.dat
-rw-rw-r-- 1 stephan stephan 12538362 May  6 08:18 EN_1.dat
-rw-rw-r-- 1 stephan stephan 12339289 May  6 08:18 EN_2.dat
-rw-rw-r-- 1 stephan stephan 12429059 May  6 08:18 EN_3.dat
-rw-rw-r-- 1 stephan stephan 12564839 May  6 08:18 EN_4.dat
-rw-rw-r-- 1 stephan stephan 12607103 May  6 08:18 EN_5.dat
-rw-rw-r-- 1 stephan stephan 12533003 May  6 08:18 EN_6.dat
-rw-rw-r-- 1 stephan stephan 12651300 May  6 08:18 EN_7.dat
-rw-rw-r-- 1 stephan stephan 11757352 May  6 08:18 EN_8.dat
-rw-rw-r-- 1 stephan stephan 13328327 May  6 08:18 EN_9.dat

That 0-sized file at the end will always exist using this simple automation - simply remove it before importing the chunks.

06:13 Reply: select * from fcc where unique_system_identifier='4350131'; returns 1000+ records (artifact: 2d1eacf841 user: stephan)

I think you would need to find some way to make these global changes to the file before you proceed.

Any decent stream editor (sed or perl) can do that. Here's a line of sed which fully quotes all of the records, double-quoting any existing quote characters:

$ sed -e 's/\r//g' -e 's/"/""/g' -e 's/|/","/g' -e 's/^/"/g' -e 's/$/"/'  < EN.dat > _en.dat
$ grep 'SCHURMAN SR' _en.dat 
"EN","261932","","","K7SAS","L","L00171357","SCHURMAN SR, ROBERT W","ROBERT","W","SCHURMAN","SR","","","","1175 1/2 ""D"" ST","BAKER CITY","OR","97814"," ","","000","0002101574","I","","","","","",""
...

The initial \r removal is needed with GNU sed, else it apparently doesn't recognize \r\n as the end-of-line for purposes of the $ character.

(Ach - it looks like Warren has responded while i was writing this, but i'll go ahead and post it for completeness's sake.)

2021-04-20
13:51 Reply: Why no release announcement in this Forum anymore? (artifact: 317359956d user: stephan)

Indeed, I wasn't aware at all about those Email Alerts. How's one supposed to know about them?

When you're logged in as a non-anonymous user, clicking on your name at the top of the site leads you to a page with a link to configure the various types of notifications. One of the many benefits we offer non-anonymous posters ;).

2021-04-19
13:38 Reply: How to Record all Transactions (artifact: e21eefdc60 user: stephan)

How can I record all of these statements?

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

2021-04-14
10:50 Reply: object name reserved for internal use: sqlite_parameters (artifact: 436fc3996d user: stephan)

Is there a way to initialize temp.sqlite_parameters using an SQL statement

i'm unable to find a citation but recall reading somewhere that the prefix sqlite_ is reserved for tables created by sqlite.

2021-04-05
05:00 Reply: COUNT statement returns inconsistent values when used with UNION ALL (artifact: 17c50976bf user: stephan)

This problem seems to be introduced by de9ed6293de53e89b7c37e7de9a8697d86d7f619

That's not a hash from the sqlite project:

[stephan@nuc:~/fossil/sqlite3]$ fossil time -n 1
=== 2021-04-04 ===
23:56:15 [ad718388a1] Fix an assert() in sqlite3BtreeLast() that needs an "|| CORRUPT_DB" term. Dbsqlfuzz case b92b72e4de80b5140c30ab71372ca719b8feb618. (user: drh tags:
         trunk)
--- entry limit (1) reached ---

...

[stephan@nuc:~/fossil/sqlite3]$ fossil whatis de9ed6293de53e89b7c37e7de9a8697d86d7f619
unknown:    de9ed6293de53e89b7c37e7de9a8697d86d7f619
2021-03-31
06:42 Reply: Automatic change True value to 1 (artifact: 1b5ec2d4d2 user: stephan)

I was 100% sure the "boolean" function isn't avaible in SQLITE3.

True and false are keywords in sqlite:

$ sqlite3
... 
sqlite> select true;
1
sqlite> select false;
0
2021-03-24
15:12 Reply: Maybe fossil problem ? (artifact: 0d704e0f82 user: stephan)

And maybe would be nice to have the end-of-line spaces trimmed by default.

Fossil necessarily saves exactly what it is given, with the unfortunate obligatory exception that it can optionally replace the newlines to a consistent style.

Stripping trailing spaces would break certain files, possibly in subtle ways. e.g. at least one flavor of markdown uses backslash-blank-blank to force a line break within a paragraph.

2021-03-23
15:12 Reply: Sum data query request (artifact: 32155b0287 user: stephan)

I want to have A, B and C from col1 summed with their corresponding values in col2. For exaple:

Assuming the input you pasted in above is in the file foo.csv:

$ sqlite3
SQLite version 3.35.3 2021-03-20 01:00:26
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a,b);
sqlite> .import --skip 1 --csv 'foo.csv' t
sqlite> select a, sum(b) from t group by a;
AAA|344000
BBB|101972640
CCC|42268000
2021-03-22
19:29 Reply: Looking for the documentation drawing (artifact: e061bbee67 user: stephan)

The majority of the drawings are, since recently, created with pikchr:

https://pikchr.org/

There is no template. It's a text-based language for describing the image you want and pikchr translates that to SVG.

See:

https://www.sqlite.org/docsrc/dir?ci=tip&name=art/syntax

for the pikchr source code of many of the diagrams. Click on a file to see the diagram (rendered in real time when you click on it) and then click on the "Text" link along the top of that page to see the source code for that image. For example:

https://www.sqlite.org/docsrc/file?name=art/syntax/join-clause.pikchr&ci=tip&txt=1

00:31 Reply: php error : SQLSTATEHY000: General error: 6 database table is locked (artifact: df9fa762a3 user: stephan)

Whereas it works if I manually execute the query... What's the problem ?

Unless PDO has changed significantly in the 8+ years since i last used it, exec() supports only a single statement, whereas you have given it several statements. How to use the various PHP sqlite APIs is far out of scope here, though - this forum is for the C library and its close relatives.

2021-03-20
21:10 Reply: "Scope" of SQLITE_DETERMINISTIC? (artifact: eec2bbd78d user: stephan)

The current trunk version will only execute such functions once. Notice the ONCE block around lines 11-13:

Great, thank you :). Poking around in sqlite3.c:

**   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
**     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
**     and functions like sqlite_version() that can change, but not during
**     a single query. ...

That exactly describes the effect i'm looking for.

It seems that SLOCHNG is only the private/internal API, though. Oh, well. No biggie.

20:08 Reply: "Scope" of SQLITE_DETERMINISTIC? (artifact: 42a9be955f user: stephan)

I am not absolutely certain whether SLOCHNG implies step stability or the greater requirement of statement stability, but its general effect is to prevent the use of SLOCHNG functions in INDEX expressions but allow them to be used elsewhere as if they were DETERMINISTIC.

Thank you for the detailed responses, Keith!

SLOCHNG sounds like it's close enough to what i'm looking for, but it's not in the current docs and even google only finds 3 references to it from mailing archives.

This function would never be used in an index or some such, i'd just like to prevent that it gets called 293 times for:

select my_app_dir() || pathname from vfile;

for any given step loop. A subsequent step loop, however, should be free to call it again (once).

In any case, this isn't a "requirement" or "must-have" - just trying to eliminate some technically superfluous allocations.

18:43 Post: "Scope" of SQLITE_DETERMINISTIC? (artifact: d4e848e31a user: stephan)

https://sqlite.org/c3ref/c_deterministic.html:

The SQLITE_DETERMINISTIC flag means that the new function always gives the same output when the input parameters are the same.

Does "always" mean...

  1. For the duration of the connection into which the UDF is installed or...

  2. For the duration of a given statement's evaluation (a single sqlite3_step()) or...

  3. For the duration of a given statement handle's lifetime or...

  4. For the duration of all currently-being-evaluated statements?

  5. Something different?

e.g. if a statement is prepared and cached for the duration of the connection, as opposed to being used as a "one-shot," are SQLITE_DETERMINISTIC UDFs used by that statement treated as always returning the same result for the same input, regardless of how long that statement handle lives?

Use case: a UDF which does something like:

select my_app_dir() || 'sub/path/within/app/dir';

where the directory name returned by my_app_dir() may change during the life of the application but will never change during the evaluation (as opposed to sqlite3_stmt handle lifetime) of any given statement or series of statements processed concurrently during recursion.

FWIW, my goal here is to attempt to micro-optimize away a handful of allocations if the scope of the deterministic flag is suitable for that purpose. (That's what i do for fun on weekends. Nevermind for a moment that creating, sending, and reading this post is already allocating more memory than this micro-optimization would likely ever save. ;))

:-?

2021-03-13
16:51 Reply: json_extract behavior with escaped-double-quote keys (artifact: eff9033339 user: stephan)

select json_extract('{ "a": 1, ""b"": 2 }', '$.""b""');

This variation works for m:

SQLite version 3.35.0 2021-03-09 21:20:12
Enter ".help" for usage hints.
sqlite> select json_extract('{ "a": 1, "\u0022b\u0022": 2 }', '$."\u0022b\u0022"');
2

Where \u0022 ==> ".

It ain't pretty, but neither is doubling up on double quotes ;).

2021-03-09
11:44 Reply: Update Timeout (artifact: 7d41cf53aa user: stephan)

Hallo ich bin Karl und programmiere vb.net mit Sqlite.

Hi Karl. Hier wird vorzugsweise Englisch gesprochen, so hier eine Uebersetzung fuer alle anderen:

(Abbreviated English translation of Karl's post:)

Using vb.net I have a simple table with an ID and one column. When I update the column once, it works, but when I repeat the update a second time, the Execute command times out. Here's the code:

@Karl i suspect that the problem is that you are disconnecting your db connection near the end of the function (SQLconnect.Close()), but i'm not familiar with vb.net so might be misunderstanding that API.

2021-03-07
15:16 Reply: Calling sqlite3_sleep() from CLI (artifact: 2d35d2e1cb user: stephan)

I only looked at The keyword index ..

It's not built in to the library - it's one of the functions the CLI app adds. It's presumably not available on platforms without usleep(), but i didn't look at the surrounding #if blocks to see when/whether it's enabled.

14:57 Reply: Calling sqlite3_sleep() from CLI (artifact: f3847e02fe user: stephan)

without customizing the CLI?

$ sqlite3
SQLite version 3.35.0 2021-02-06 14:56:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select usleep(1000000);
1000000
2021-03-06
16:44 Reply: Why don't temporary on-disk databases support the WAL mode? (artifact: 43025ae411 user: stephan)

Thanks for any hint!

i'm going to take a guess at this, without being 100% certain:

On non-Windows platforms temporary files are normally deleted as soon as they're opened. The file handle to them is still valid but the file is not visible in the filesystem and the file's resources will be reclaimed by the OS as soon as the opened file handle is closed (either via fclose() or exiting the app).

Because the file is deleted, it has no name in the filesystem, and thus cannot sensibly have a corresponding -wal file. (That begs they question how they handle journaling at all - presumably in memory, since it would be impossible to recover an on-disk journal for a transient/temporary file name.)

Again, just a guess. Someone here will eventually correct me if that's off base.

2021-03-04
15:58 Reply: Shell function to return screen width (artifact: 49e2cd0aeb user: stephan)

... it'd be nice to have a way of knowing what the terminal width is so that the query/view output is adjusted to not wrap in multiple lines.

For anyone interested in working on such a thing, Fossil has an implementation:

https://fossil-scm.org/home/finfo?name=src/terminal.c

15:52 Reply: CTRL-C behavior in shell (artifact: b1dba539cd user: stephan)

The problem is sometimes CTRL-C is pressed right at the moment the query ends naturally. At that point CTRL-C is interpreted as exit from SQLite3 shell.

On my box (Linux on a Raspberry Pi 4b) it only does so if ctrl-c is pressed 3 times in a row with no other intervening keystrokes:

$ sqlite3
SQLite version 3.34.0 2020-11-24 13:14:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ^C
sqlite> ^C
sqlite> ^C
sqlite> ^C^C
sqlite> ^C^C^C

Which implies that either you're tapping it too often or this is a platform-/build-option-specific problem (and you didn't mention your platform).

2021-03-03
04:30 Post: Clarification of transactions and attached dbs (artifact: 887fa59f08 user: stephan)

The ATTACH docs state:

Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. ...

Should that first sentence possibly say:

... main database is not ":memory:" or "" ...

:-?

(The db name "" works like ":memory:" but uses a temp file. Or at least that's my understanding.)

Hypothetically, since there's no(?) way to re-open the same "" a second time, crash recovery could not take place with such a db?

2021-02-25
11:40 Reply: Vallgrind reports memory leak in libsqlite3.so.0.8.6 (artifact: 2c7cea1ae2 user: stephan)

sqlite3_free(&ErrMessage);

Remove the ampersand. (Pardon brevity - on a train.)

05:48 Reply: Vallgrind reports memory leak in libsqlite3.so.0.8.6 (artifact: 632e6703f4 user: stephan)

The error message is used.

Then that looks like the source of your leak. sqlite3_load_extension()'s docs explain that the error message is allocated dynamically and must be freed by the caller.

sqlite3 is literally one of the best-tested pieces of software in the world, and claims that it is leaking memory require proof that it's doing so. Your valgrind trace shows a place where it's clearly possible (even likely) that the leak is caused by ignoring the instructions of the sqlite3_load_extension() docs.

Here's a way to quickly test that hypothesis: pass NULL as the final argument, instead of &ErrMessage, and run it through valgrind again. My bet is that the leak goes away.

05:29 Reply: Vallgrind reports memory leak in libsqlite3.so.0.8.6 (artifact: b46deb3d9f user: stephan)

rc = sqlite3_load_extension(DB, "/usr/lib/sqlite3/pcre0", 0, &ErrMessage);

It looks suspiciously like you are failing to free the error message (assuming you are getting one). If you intend to ignore any error message, pass NULL as the final argument.

https://sqlite.org/c3ref/load_extension.html

PS: there is no benefit to adding slash-zero to the end of string literals. They already have an implicit NUL terminator byte.

2021-02-24
11:34 Post: Tiny doc bug: sqlite_source_id() has outdated SHA1 reference (artifact: 3078d9ea50 user: stephan)

https://sqlite.org/lang_corefunc.html#sqlite_source_id

seems to be slightly out of date:

The string returned by sqlite_source_id() is the date and time that the source code was checked in followed by the SHA1 hash for that check-in.

(Emphasis added.)

It's currently an SHA3-256 hash:

SQLite version 3.35.0 2021-02-22 16:42:09
Enter ".help" for usage hints.
sqlite> select sqlite_source_id();
'2021-02-22 16:42:09 b5a0778cc5a98a864bea72670f83262da940aceb91fa4cdf46ec097337a3alt1'
05:07 Reply: Out of Memory Errors - Possible Enhancement (artifact: d939fcf1af user: stephan)

I am sorry, for some reason, I don't get the ability to edit my previous post.

A post which is pending moderation cannot be edited, to avoid a race condition between the moderation and the edit. Once your post has passed moderation (or your account toggled to not need moderation) you should be able to edit it.

2021-02-23
23:21 Reply: Out of Memory Errors - Possible Enhancement (artifact: 8db31ec64e user: stephan)

On such a computer, you will find that you can go to the table "Useful Queries" and run the two queries you find there one after the other

sqlite> select * from "Useful Queries";
Error: no such table: Useful Queries

The actual name is apparently UsefulQueries (no space).

19:32 Reply: Cannot bind text variable to insert query (artifact: 50b4624799 user: stephan)

sqlite3_bind_text(res, 1, alarmtimex); // alarmtimex is a global string variable

That cannot possibly have compiled, as that function requires more arguments:

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

2021-02-20
22:13 Reply: Retrieve query schema without executing query (artifact: 2c482b568a user: stephan)

I'm wondering if there's a way to retrieve the schema (columns + types) of a SQLite query without actually executing the query.

For tables you can use the table_info pragma:

https://www.sqlite.org/pragma.html#pragma_table_info

For arbitrary queries there cannot really be such a construct which returns the data type because any given column can hold any given data type in any given row. The data type declarations in a table are hints, not directly enforced by the db (but can be enforced with manually-installed constraints).

More ↓