SQLite Forum

Timeline
Login

50 most recent events by user larrybr occurring on or after 2021-11-06 23:10:06.

More ↑
2021-12-08
20:13 Reply: State of backup destination database if backup is abandoned artifact: 24725c61c0 user: larrybr

One reason I ask is that in some RDBMS (such as MySQL and Oracle), schema DDL statements cannot be a part of an open transaction. Do you see documentation about this for SQLite?

In SQLite, (just as in PostgreSQL), DDL is done within transactions which are either implicit (for each statement not in a transaction) or explicitly begun and completed. DML and queries may accompany the DDL within a transaction.

I cannot cite where this is said in so many words, but such dramatic behavior as Oracle has (where DDL closes open transactions and begins another to be closed when the DDL statement finishes) would be carefully documented, and there is no such language anywhere in the SQLite docs. (Further, the Oracle behavior seems like something that would necessitate very klutzy work-arounds, something to be assiduously avoided when the PG model is so clearly superior.)

The docs on transactions, because they mention no qualification or limitation related to DDL, can be taken as a guarantee that DDL is fine in (SQLite) transactions.

19:45 Reply: State of backup destination database if backup is abandoned artifact: 7458009778 user: larrybr

What if both source and destination databases have tables named "items" with different contents?
What if both source and destination databases have tables named "items" with different schema?
What if the source database contains a table the destination database does not or vice-versa?

When the backup API completes normally (and not as in your "abandoned" scenario), the destination DB is left in the same logical state as the source DB at the time a read transaction is begun on the source (during the first sqlite3_backeup_step() call.) This means identical schemas and table contents.

This can be inferred from three facts:
(1) The doc says, "The backup API copies the content of one database into another. It is useful either for creating backups of databases or for copying in-memory databases to or from persistent files.";
(2) It is called "the backup API", not "the try to blend databases API";
(3) There are words in the backup API docs about a resumable page copying process, which would make no sense if portions of pages representing row content were being blended into an inconsistent schema.

I'm torn between thinking the doc should be clearer on this and thinking the API name and "copies" claim say it all. I'm open to others' thoughts on this.

18:34 Edit reply: State of backup destination database if backup is abandoned artifact: 588a176c43 user: larrybr

(Edited to note response is partial.)

On this backup API page, can be found an assertion: "SQLite holds a write transaction open on the destination database file for the duration of the backup operation." Later, on sqlite_3_backup_finish(): "If sqlite3_backup_step() has not yet returned SQLITE_DONE, then any active write-transaction on the destination database is rolled back."

Together, these facts mean that the destination will be logically unaffected given the 1st usage pattern you posit. And because this conclusion rests upon documented API behavior (to which guarantees attach), it too is guaranteed.

18:28 Reply: State of backup destination database if backup is abandoned artifact: f38ab06c5d user: larrybr

On this backup API page, can be found an assertion: "SQLite holds a write transaction open on the destination database file for the duration of the backup operation." Later, on sqlite_3_backup_finish(): "If sqlite3_backup_step() has not yet returned SQLITE_DONE, then any active write-transaction on the destination database is rolled back."

Together, these facts mean that the destination will be logically unaffected given the usage pattern you posit. And because this conclusion rests upon documented API behavior (to which guarantees attach), it too is guaranteed.

12:43 Reply: SQLite Release 3.38.0 On 2022-03-39 (??) artifact: c88745fc7c user: larrybr

Draft docs are preliminary, particularly those speaking of future release features and dates. You can think of it as a placeholder. Translate that date as "date yet to be determined".

05:13 Reply: how to do "Explain Query Plan" using code (c++ or rust)? artifact: 1423dcc4a7 user: larrybr

...
I have no way to debug it, because "Explain query plan" seems to only work with the command line interface.
...
With the command line interface, I don't know how to provide my custom function.

Have you studied the effect of .autoExplain in the ShellState struct within shell.c?

Have you looked at the explain_data_prepare() function in shell.c? Is that particulary difficult to Rustify?

2021-12-07
20:16 Reply: How to skip intermediate data of DB in file? artifact: bdded2160e user: larrybr

I want example in c, c++.

Are you suggesting that somebody else either post some code that behaves this way or go find some for you to study? If that is your hope, you should prepare yourself for disappointment. Otherwise, you need to clarify.

FYI, Gunter has stated something that is true for practically all programs that must allocate memory to operate and are written by careful programmers. It should not be difficult to find some. The SQLite shell (with its incorporated SQLite library) would be a good example. See the Downloads page.

04:06 Reply: Windows Build default compile options? artifact: d56b7f2186 user: larrybr

From a portion of the loader's complaint, "SQLite.SQLiteConnection..ctor", I gather that you are trying to use your sqlite3.dll with (I'm guessing) the System.Data.SQLite library. That library expects to link against something else to get to the SQLite APIs, namely something named SQLite.Interop.dll .

To build SQLite.Interop.dll, you will want its source, into which you might be able to incorporate the sqlite3.c you have for the one it is distributed with.

I doubt your problem is due solely to name stripping, exporting or decoration.

2021-12-06
17:17 Reply: SQLite on Windows for ARM artifact: 139eb0d813 user: larrybr

beginning to wonder if it's a platform limitation due to the architecture

The project Downloads page has an Android binary, and the Fossil project (which uses SQLite) publishes a binary for the Raspberry Pi. Since those are both ARM platforms, I would say the evidence goes against the "platform limitation" idea.

01:53 Reply: confusing text in vfs.html artifact: 329c59ab1f user: larrybr

Thanks for the tip. This is fixed in the doc repo, to be pushed to the site in due course.

2021-12-04
00:26 Reply: How to use SQLITE_CUSTOM_INCLUDE in configure artifact: c68a8bb935 user: larrybr

That preprocessor symbol and its treatment is not intended to be used as a build option by itself.

Its purpose is to permit a file of the builder's choice to be #include'd by defining a preprocessor symbol value at compile or make time. And the way you do that is to add, to the make invocation, something resembling:

  "OPTS=-DSQLITE_CUSTOM_INCLUDE=my_custom_defines.h"

Persusing the Makefile, you can see that this is a supported build alteration:

  # Add in any optional parameters specified on the make commane line
  # ie.  make "OPTS=-DSQLITE_ENABLE_FOO=1 -DSQLITE_OMIT_FOO=1".
  TCC += $(OPTS)

If you like to invoke the compiler yourself, leave off the "OPTS=" part and put the remainder along with other compiler options in the invocation tail.

If you really like just typing (something as simple as) "make", you could write an alias to add the "OPTS=..." part.

2021-12-03
22:16 Reply: AMALGAMATION_LINE_MACROS in configure.ac artifact: 3de38a4542 user: larrybr

Jan,

Do you believe that patch accomplishes something more than this checkin?

If so, I would like to understand what and gain that additional functionality.

BTW, thanks for the prompt and fix on this.

19:29 Reply: sqlite3_compileoption_used() now meaningless for some macros artifact: 80cddb7155 user: larrybr

I will look into this. It goes against intention for anything in the sqlite3_compileoption_used() output to carry no real information.

2021-12-02
17:56 Reply: Can I assume that the TRIGGER runs from within my transaction? artifact: 2a650a70a6 user: larrybr

Yes and Yes.

16:18 Edit reply: Linking tables artifact: 7c4fe04f25 user: larrybr

(I was hoping Ryan would post a kindly stated and pertinent response.)

It appears to me that, having specified the foreign and primary keys and relationships, you expect some of such data (primary keys) to spring into existence when you insert other such data (foreign keys).

You DML ("data modification language" SQL) doing INSERT and/or UPDATE needs to leave values in the various table rows which satisfy the primary/foreign key relationships you set out (or imagined) in your schema. There is no auto-magical "springing into place" for rows needed to satisfy them. (There can be auto-magical vanishing of rows whose foreign key(s) reference a primary key that was made to vanish. But that is beyond today's problem.)

As Ryan says, further details would probably be needed or useful as part of describing your problem. This is part of making clear what you expected to happen in contrast to what did or did not happen.

16:16 Reply: Linking tables artifact: 6f838fddc7 user: larrybr

(I was hoping Ryan would post a kindly stated and pertinent response.)

It appears to me that, having specified the foreign and primary keys and relationships, you expect some of such data (primary keys) to spring into existence when you insert other such data (foreign keys).

You DML ("data modification language" SQL) doing INSERT and/or UPDATE needs to leave values in the various table rows which satisfy the primary/foreign key relationships you set out (or imagined) in your schema. There is no auto-magical "springing into place" for rows needed to satisfy them. (There can be auto-magical vanishing or rows whose foreign key(s) reference a primary key that was made to vanish. But that is beyond today's problem.)

As Ryan says, further details would probably be needed or useful as part of describing your problem. This is part of making clear what you expected to happen in contrast to what did or did not happen.

2021-11-30
17:57 Reply: Compile for Apple Silicon artifact: d82fb0dde7 user: larrybr

On useless extern "C" blocks:

If the code was written to use a C++ compiler as "a better C compiler"a, then those modifying wrappers would be useful (without the #if 0, of course.) But there is nothing but confusion (and forum posts) to be gained by allowing sqlite3.c to be passed thru a C++ compiler.


a. This has been claimed, and I largely agree. But the code has been written for older C compilers and contains numerous violations of the C-like subset of C++, so it is futile at present to enable those extern "C" wrappers.

17:44 Reply: Compile for Apple Silicon artifact: d80b5f56db user: larrybr

Why not use clang instead of clang++?

12:32 Reply: Why does SQLite violate foreign key policy when data is populated through python program? artifact: 300ab3986a user: larrybr

You could issue the pragma I mentioned prior to doing anything for which you want referential integrity enforced.

12:10 Reply: Error while executing query, no column with such name exists artifact: f70323e9ac user: larrybr

Your decision to post that latter question separately is a good choice. We like to see threads with titles reflecting their content. That's hard to achieve when the only relation between some parts of the thread is their authorship.

12:07 Reply: Why does SQLite violate foreign key policy when data is populated through python program? artifact: ad0c313255 user: larrybr

See pragma foreign_keys and note carefully the default state affected by that pragma.

Short answer to "why ...": Because your Python program uses a SQLite library built in a defaulted way and does not enable foreign key enforcement.

2021-11-29
15:00 Reply: (Deleted) artifact: 805dcf9db5 user: larrybr

Yes, we can obtain a product with '*', but we generally do not do homework for lazy students.

If you have specific questions about SQLite or its usages, and can formulate them in a brief post, they are likely to be answered. However, it is not reasonable to expect people to go elsewhere to download something, then study it, in order to understand or answer your question.

2021-11-28
23:04 Reply: AMALGAMATION_LINE_MACROS in configure.ac artifact: d2c6ec53d7 user: larrybr

It's not in configure's --help output, so it can be tricky to trace back how to get it. We thought it would be more useful than turned out to be the case, so it is not a documented feature. (That could change if it does prove truly useful.)

To get #line preprocessor directives into the generated amalgamation, run:

    env amalgamation_line_macros=yes ./configure --enable-all
or whatever configure options you like. Or just preset the same environment variable the same way. Setting it to "no" or not setting it will suppress #line directive generation.

22:43 Reply: Suggestion of safe mode of command shell artifact: 813a098212 user: larrybr

I second this question, and suggest that usage scenarios be at least briefly described where the additional feature(s) would be useful and the objective not easily attained by other means.

2021-11-26
21:12 Reply: Существует ли справочник синтетических ошибок?Где искать исправления error near? artifact: 1a15ca14d4 user: larrybr

If you want help with that error, you will need to provide the sqlite3 shell input which induced the error.

And please edit the thread title so that it might prove useful someday to a larger set of readers.

2021-11-24
17:45 Reply: Feature Request: ATTACH DB readonly? artifact: 1c87db1032 user: larrybr

See URI query parameters, then use the URI file specification syntax for your ATTACH. (A compile-time option to enable URIs may be necessary, depending on where you get SQLite bits.)

17:18 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: f9d9efd9c4 user: larrybr

Just I want to know why heap size increase on query execution? If any body technically explain it will be better. Is there method to free it?

See Ryan's post (#11) and read the doc he linked. Your questions are answered there, so there is no reason to write the same out here.

2021-11-23
21:08 Reply: Possible bug: Unable to find an entry point artifact: 34307dc089 user: larrybr

... the following error is encountered on start: Unable to find an entry point named 'SIa069da76968b7553' in shared library 'SQLite.Interop.dll'

I am highly skeptical that a name like that is supposed to be exported by that DLL. I've seen many name decoration schemes, but never one that appears to encode a 64-bit number with a prefix. And all the names exported by SQLite.Interop.dll for x86 and amd64 platforms are more ordinary, semi-meaningful-to-humans identifiers.

Do you have any reason, beyond this error message, to believe that the System.Data.SQLite.Core library is, in fact, linked (albeit dynamically) to such an entry point?

19:43 Reply: Sir Richard artifact: fba533b3c7 user: larrybr

Please try to use thread titles which reflect the topic they introduce.

Both the expression syntax diagram and the operator table show IS in the role of a binary operator. Hence, taking those data as enabling, <expr> IS NULL clearly is permitted because NULL itself is an expression.

18:11 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: a637fae858 user: larrybr

How you can tell this is memory leak?

In just the code you posted, I see one likely memory leak. If errMsg is set by that sqlite3_exec() call, it is never freed by anything you show.

I am simply run sqlite query using sqlite3_exec() in c.

I hope you get understand the example std::stringstream sql;

I have written enough C++ to know that there is no need to extract a std::string from a std::stringstream when its only use is to get its content via c_str(). This cavalier approach to allocation makes me suspect those parts of your code that are not shown.

[big glop of code ...]

I'm not tempted to try to run that code fragment because I would have to create a lot of scaffolding around it; it's duplicative; it relies on a schema I am too lazy to infer; and I do not yet see any evidence or substantiated claim that an actual memory leak is occurring. What do sqlite3_memory_used() returns versus number of queries run look like? Does that function resemble a line with non-zero slope as the program keeps running?

16:46 Reply: How to free max heap size on execution of sqlite query using sqlite3_exec? artifact: ab3919ccc7 user: larrybr

To Gunter's question, "Have you checked to see if each execution of the query adds about the same amount to heap memory?", answer was:

Depending on number of records it increases

In other words, "No, allocated memory does not appear to grow approximately linearly as a query is repeated."

You have not described a memory "leak". Nor have you shown in what manner memory allocation increases over time. Hence, nobody can distinguish between something abnormal or objectionable and expectable, ordinary allocation growth toward a semi-stable state.

To Gunter's question, "Can you provide a minimal example (schema and queries in plain SQL) that illustrates the problem?", answer was:

I must have to check this thing in my running application on same device

Until you provide enough information for somebody else to observe your (not quite) reported problem, the assumption by others will be that what you see is a feature of your application rather than a deficiency of the SQLite library.

You may find this doc on SQLite memory allocation useful, particularly the configuration and debug tips.

2021-11-21
02:33 Reply: IS NULL optimization on NOT NULL constraints breaks on corrupted databases artifact: b90de8168b user: larrybr

You make a good point regarding the spirit versus ostensible thrust of the OP's original post. I have to admit being unsure what its point really wasa, and latching onto its most obvious aspect.

However, taking "revised schema only" as a stand-in for "data altered to no longer be consistent with schema", my point about the discrepancy remains: A corrupt DB, whether made corrupt by simple, repeatable or mysterious means, is not going to have defined behavior or even behavior that should be expected to remain the same across SQLite releases.

There have been many recent changes to how the library detects and responds to DB corruption. They help avoid (further) data loss and ease the task of avoiding/reducing undefined behavior in the C/C++ UB sense. This kind of evolution simply cannot be confined to perpetuate the behavior (or misbehavior) of past releases under such conditions.

Your point about sanity checks versus performance is well taken.


a. During my perplexity on the point, I've been tempted to parody it, along lines of" "I crossed out 'Apples' on this bag of fruit and wrote 'Oranges', then pulled an apple out." But it was too crude and not quite on point. And likely to seem unkind.

2021-11-20
17:00 Reply: IS NULL optimization on NOT NULL constraints breaks on corrupted databases artifact: 9f5da01ff4 user: larrybr

[Regarding effects of changing sqlite_schema table via UPDATE] ...
Which isn't obvious, and a change of behavior with respect to older SQLite versions

The effect of making your own changes to the schema table is undocumented and undefined, and always has been. Changes in undocumented behavior are sometimes intentionally avoided as the project evolves, but not always and not in any way that users should rely upon.

Your effort to inform about this particular change of behavior is appreciated. You may consider it a warning about reliance upon undocumented behavior.

2021-11-19
16:57 Reply: Hot backup database in WAL mode by coping artifact: 3a151087f5 user: larrybr

... needing twice the disk space of the SQLite database.

I do not see why anybody should believe that. Your "hot backup database" is going to consume at least the same disk space as a VACUUM INTO target database, and probably more. So where do you get 2 x? I get "1 x or less".

16:33 Edit reply: Possible data race in os.unix.c:5805:unixTempFileDir artifact: c660666202 user: larrybr

(Edited to correct hazard preconditions and reflect fix status.)

This is, technically, a race as claimed. The only potential harm would be when:
Multiple threads initially request the temp directory name at (nearly) the same time in the same process;
For different connections;
On a build with 64-bit pointers;
Running on a 32-bit machine.

This is extremely unlikely to ever happen, of course. Nevertheless, a "cure" has been checked into the source (thanks to Dan.)

14:03 Reply: Possible data race in os.unix.c:5805:unixTempFileDir artifact: fe55aff7f9 user: larrybr

This is, technically, a race as claimed. The only potential harm would be when:
Multiple threads request the temp directory name at (nearly) the same time;
On a build with 64-bit pointers;
Running on a 32-bit machine.

This is extremely unlikely to ever happen, of course. Nevertheless, a "cure" will soon appear in the source.

2021-11-18
21:46 Reply: PRINTF - newlines, tabs etc? artifact: 0f2e119817 user: larrybr

The printf() function does not interpret backslash-escape sequences in either C or SQLite. That is something that happens during translation, by a C or C++ compiler, from string literals in the source text to char sequences in the compiled output. And since SQLite does not implement the non-SQL convention of backslash escaping in its string literals, your examples would not work even if quoted correctly.

In SQL, the doublequote is used to delimit identifiers, not string literals. In SQL, a string literal is delimited by singlequote characters, except where they are paired within the quoted text.

15:40 Reply: sqlite3.dll is not returning control to my program artifact: 786206c9a7 user: larrybr

I have this in ~/.bash_aliases:

alias mdquote="perl -p -e 's/([\\[\\]\\\\\\<\\>*#])/\\\\\$1/g;'"

What you see posted is a misbegotten mishmash of that filter's output and some incremental back-edits.

15:40 Delete reply: sqlite3.dll is not returning control to my program artifact: 46c3d39789 user: larrybr
Deleted
15:39 Reply: sqlite3.dll is not returning control to my program artifact: 3669b7a22c user: larrybr

I have this in ~/.bash_aliases:

alias mdquote="perl -p -e 's/([\\[\\]\\\\\\<\\>*#])/\\\\\$1/g;'"

What you see posted is a misbegotten mishmash of that filter's output and some incremental back-edits.

2021-11-17
23:55 Reply: sqlite3.dll is not returning control to my program artifact: 65a819ce49 user: larrybr

I do not see anything definitely wrong with what you have shown. This makes me think something you're doing not yet shown is wrong. It would be a tedious guessing game to venture all the ways you might be erring.

Instead, I created this silly little C++ish program: #include <iostream> #include <string> #include <assert.h> #include "sqlite3.h" // using std::cout; using std::cerr; using std::endl; using std::string; // int execCallback(void*,int nc,char** pzColValues, char** pzColNames){ cout << "Result row:" << endl; for( int ic = 0; ic < nc; ++ic ){ char * zValue = (pzColValues[ic])? pzColValues[ic] : "NULL"; cout << " " << pzColNames[ic] << ": " << zValue << endl; } return 0; } // int main(int an, char *ap[]){ if( an < 2 ){ cerr << "Usage: sdb_futz <dbName> [<query1> ...]" << endl; return 1; } string dbName(ap[1]); sqlite3 * db = 0; int rc = sqlite3_open( dbName.c_str(), &db ); if( rc != SQLITE_OK ){ cerr << "Cannot open " << dbName << " as DB." << endl; return 2; } int nFlubs = 0; for( int na = 2; na < an; ++na ){ char * zErr = 0; string query(ap[na]); cout << "Running query: " << query << endl; rc = sqlite3_exec(db, query.c_str(), execCallback, 0, & zErr); if( rc != SQLITE_OK ){ cerr << "Exec failed with error " << rc << " (" << (zErr? zErr : "?") << ")" << endl; ++nFlubs; sqlite3_free(zErr); } } rc = sqlite3_close(db); return ( rc == SQLITE_OK && nFlubs == 0 )? 0 : 2+nFlubs; } // . This is what I did to build it: REM Prior to this, get 64-bit sqlite3.dll, sqlite3.def and REM sqlite3.h downloaded and extracted to this directory. REM Then, in a shell setup for MSVC 64-bit build tools, run: lib /def:sqlite3.def /out:sqlite3.lib /machine:amd64 REM Next, create something like above source: emacs_edit sdb_futz.cpp REM Then build and run it: cl -DDEBUG -EHsc sdb_futz.cpp /link sqlite3.lib sdb_futz furd.sdb "create table Silly(why text)" sdb_futz furd.sdb "insert into Silly values ('No reason'),('Why am I silly?')" sdb_futz furd.sdb "select * from Silly" "select count(*) from Silly" .

This ran as I would expect, so I'll not bore anybody with output.

I propose that you figure out how your code and build differ from the above. You can replicate my build, then use DUMPBIN to examine our objects and image to perhaps discover if linkage or calling convention has gone awry. (Naming conventions should prevent such problems, but they may have been circumvented.)

Your observation, "problem temporarily goes away if I restart VS2019", is not much to go on. But it does suggest you are causing the dreaded C/C++ undefined behavior to occur, just as your other symptoms suggest. (Failure of return from a SQLite API means something fundamental is wrong.)

You may notice that my build does virtually nothing to establish DLL linkage or set calling conventions. You may want to try that among your other explorations.

If you are serious about getting help with your problem here, you will need to simplify your code and build to something others here can reasonably hope to replicate. The Visual Studio IDE is nice, but it hides a tremendous amount of detail in its project files.

Obviously, you are building something which plays poorly with the sqlite3.dll published by the SQLite project. Yet, as you can see, I can build something that plays well with that DLL with little trouble.

01:15 Reply: Hot backup database in WAL mode by coping artifact: 095fb5be24 user: larrybr

Those 2 statements are executed one after the other by the CLI shell.

2021-11-11
01:54 Reply: Usage of application_id and magic.txt artifact: 660f667ab9 user: larrybr

[On magic numbers listed in <SQLite-source-root>/src/magic.txt ...]

This is still recommended path forward?

It is recommended if you want "utilities such as file(1)" to report something other than "SQLite3 Database" when given your particular DB files. It is up to you to determine whether to go to this trouble. To assess that, you need to consider how the "magic" text will be made to appear in the /usr/share/misc/magic configuration file used by the "file" utility on systems where your particular DB files are likely to appear and have provenance unknown to those who actually need to discover it. Given those predicates, an unqualified "recommended" seems a bit much in most cases.

If we're intending to do things the "Right Way" (TM) ...

(Chuckle) In my opinion, you or others in your organization are far better poised to decide what "the Right Way" is.

2021-11-10
19:21 Reply: Minor typo in copyright statement artifact: 5febca3ca1 user: larrybr

Fixed (but not yet published.) Thanks.

2021-11-08
16:59 Reply: CLI's .dump and ASCII vs UTF8 artifact: ac7cda0711 user: larrybr

This has been fixed

I don't see it on the live site, it's probably on Draft only. I didn't check the timeline.

It's in the docs repository, to be published in due course. (Minor changes are not normally published immediately.)

I'm not sure how much should be said about this

Well, given the above, the encoding of the .dump output in not even UTF, it is whatever the text values contain...
Because again, UTF-ness is not enforced in SQLite. So it's even more ambiguous than ASCII or UTF-8.

The .dump output data are returns from sqlite3_column_text(...), which are nominally UTF-8 for differing overall DB text encodings. Here, "nominally" means when text inputs have consisted of valid code point sequences. For DB encodings other than SQLITE_UTF8, those returns will be translated from the raw stored content.

I don't think it should be .dump's job (or option) to somehow filter invalid UTF-8 text that has been stored by users. It's job is simple, restricted to producing a text representation of the existing DB.

Also there won't be a BOM if uses non-ASCII multi-byte UTF-8 codepoint.

I suppose a --bom option might make sense for the .dump command. However, given that its output will normally be either subject to much further processing or fed directly to sqlite3, I do not yet see real value or necessity for that.

[general point that invalid code point treatment should be documented]

The fact that SQLite stores text as given to it, with possible translation between encoding schemes but no rejection of technically invalid code points, deserves mention somewhere. My hesitation is against cluttering the docs with this level of detail wherever the terms "UTF-8" or "UTF-16*" are used. I will find a place to mention this behavior, and maybe link to it where use of those terms might lead to false expectations.

... there should be a pragma to check basic UTF-8 (and UTF-16) of text values, which ...
I'm merely thinking aloud, and I'm used to being ignored on this forum by now.

This deserves a separate thread, and attention for a post-3.37 release.

14:07 Reply: CLI's .dump and ASCII vs UTF8 artifact: 040cfadf44 user: larrybr

[The .dump doc] says ASCII, but .dump seems to produce UTF instead.

This has been fixed. Thanks.

[I]t might be nice to have a basic QC check pragma for UTF*-ness* of all text-typed values in a DB, BTW.

Yes, it might. This is among the arguments to be made for shell extensibility, IMO.

More details on how .dump handles international characters might be nice to have in the doc. My $0.02 :)

For most platforms, .dump output is fprintf() output. I think little needs to be said about that since it is either uninteresting or strictly a platform quirk.

For Windows, .dump output is little more than fprintf() unless output is going to the console. In that case, (as a special dispensation to that quirky but common destination), output is translated as needed to MBCS because that console handles UTF-8 poorly. I'm not sure how much should be said about this, if anything, because it will rarely apply to output used programmatically.

12:48 Edit reply: CLI's .dump and ASCII vs UTF8 artifact: a7bd2d67a2 user: larrybr

You have made a convincing case that said doc should say "UTF-8" rather than "ASCII". Please clarify if there is some additional problem brought to light by your post. (I gather 1 error and 1 feature suggestion from it.)

BTW, ASCII is strictly a 7-bit coding scheme, so "°" cannot be encoded in it with any small number of bytes.

12:45 Reply: CLI's .dump and ASCII vs UTF8 artifact: cda38384b1 user: larrybr

You have made a convincing case that said doc should say "UTF-8" rather than "ASCII". Please clarify if there is some additional problem brought to light by your post.

BTW, ASCII is strictly a 7-bit coding scheme, so "°" cannot be encoded in it with any small number of bytes.

2021-11-07
18:58 Reply: Delete duplicate rows with an extra character artifact: 4f5c909ff8 user: larrybr

Here is how to get the rowid for delete candidates: SELECT x.rowid FROM players x, players y WHERE substr(x.player_tag,2)=y.player_tag AND substr(x.player_tag,1,1) IN ('#'); . Calling that query "Extras", the delete is: DELETE FROM players WHERE rowid IN (Extras); . It helps here that each row has a unique rowid.

2021-11-06
23:10 Reply: Best way check if a file is a SQLite DB? artifact: 142f659d06 user: larrybr

The SQLite 3 database header is stable; it will not change for any major version 3 of the library.

More ↓