SQLite Forum

Timeline
Login

50 most recent forum posts by user wyoung

2021-09-24
12:35 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 8d1eef2ecf user: wyoung)

You can ask wsl --status which version is the default on your system.

WSL1 is still in use on many systems from legacy installations, but also because it works inside VM systems that don't support double-virtualization, as with ARM Windows on Apple M1.

One of many differences between the two is that WSL1 uses a POSIX gloss on NTFS as the filesystem, whereas WSL2 uses an actual Linux kernel with regular Linux filesystems. This naturally has a whole laundry list of implications for SQLite's locking and file I/O semantics.

03:05 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 23b691d53d user: wyoung)

WSL2 works hugely differently from WSL1, correcting a large number of inherent flaws in WSL1. WSL2 still isn’t perfect, but being a lightweight Linux VM rather than an NT “personality,” I would class WSL2 as useful for some production tasks.

Can we please be clear about which version we’re talking about?

2021-09-20
01:42 Reply: Compiling FILEIO.C (artifact: 6a3c334684 user: wyoung)

That file is in src/, the same place you got the previous two files from.

Alternately, from an unpacked copy of the SQLite source tree, this might work:

 C:\PATH\TO\SQLITE\SOURCE> cl ext/misc/fileio.c -Isrc -link -dll -out:fileio.dll
2021-09-19
16:14 Reply: Database anomaly (artifact: acb1529ac8 user: wyoung)

I’ve seen this when mixing UTF-8 and UTF-16 improperly. Cygwin binaries with cmd.exe or native binaries with MinTTY, etc.

2021-09-17
06:01 Reply: Javascript enforcement (artifact: 9e9e41dddb user: wyoung)

These oft-repeated objections are answered in the javascript.md doc up-thread.

03:12 Edit reply: Javascript enforcement (artifact: 17083ba9ab user: wyoung)

useless Javascript.

Other posts in this very thread explain why this particular bit of JavaScript is useful. The document Stephan linked you to explains why all of all the other bits of JavaScript in Fossil — the DVCS backing SQLite and this very forum — are useful, too. Moreover, it catalogs the pains we've taken to reduce the use of it and to provide sensible fallbacks where practical.

You're welcome to disagree with individual elements of this on a technical basis, but to dismiss an entire technology the way you've done here is, frankly, unhinged from reality.

use plain links

Are you paying the bandwidth bill for robots to repeatedly download multimegabyte blobs as fast as possible?

The /src links on the page one click away via the links at the bottom of that page are similarly protected since they can cost the public SQLite servers arbitrary CPU time, not just bandwidth. If you let robots traverse the /timeline and /info trees on a Fossil repository without restriction, they'll repeatedly download the entire history of the project, with each version downloaded requiring an expensive tar+gz or zip operation.

Fossil has a cache to cope with this to some extent, but with so many versions in these projects' histories now, any reasonably-sized cache would be busted by allowing robots to run wild through the hyperlink tree. The cache would churn without end.

nuke that sucker from orbit.

Easier said than done, particularly when you're not even on the list of people potentially tasked with doing the doing.

Javascript is evil

Evil is in actions, not in things. Nouns cannot be evil; only particular uses of those nouns can be evil.

Javascript is the root of all evil.

All evil began in 1995?

Badly written javascript is responsible for 99.999% of all safety and security issues

That's not what the data show. The language topping that list is the one SQLite and Fossil are written in, and it's implicated in about four times the number of recorded incidents as JavaScript.

EDIT: Should you not therefore place about four times as much trust in the safety of JavaScript as in C?

And even C isn't responsible for more than half. It can only claim a plurality among the many other inherently-dangerous programming languages, since several of which are quite popular, preventing any language from taking a majority share of the blame.

there has never existed goodly-written-javascript in the entire history of the universe

You've got at least three of the authors of Fossil's JavaScript here in the thread. Your claim is that none of us have written any good JavaScript, either?

it was barfed-up by a moron.

I'm going to be charitable and assume you're using that term in the obsolete technical sense. You are objectively wrong on this point as well.

If you're allowing your technical definitions — and who better than one so pedantic as yourself to insist on precise use of technical words? — to expand to the point that one so objectively successful as Brendan Eich qualifies as a moron from an evaluative psychology standpoint, virtually everyone on the planet is also a moron. To take a position disregarding the value of most of the planet's population is to disconnect from society.

And by the tone and content of this post, you've also disconnected from polite society even among those you consider non-morons.

02:54 Edit reply: Javascript enforcement (artifact: 2a0b7ba20c user: wyoung)

useless Javascript.

Other posts in this very thread explain why this particular bit of JavaScript is useful. The document Stephan linked you to explains why all of all the other bits of JavaScript in Fossil — the DVCS backing SQLite and this very forum — are useful, too. Moreover, it catalogs the pains we've taken to reduce the use of it and to provide sensible fallbacks where practical.

You're welcome to disagree with individual elements of this on a technical basis, but to dismiss an entire technology the way you've done here is, frankly, unhinged from reality.

use plain links

Are you paying the bandwidth bill for robots to repeatedly download multimegabyte blobs as fast as possible?

The /src links on the page one click away via the links at the bottom of that page are similarly protected since they can cost the public SQLite servers arbitrary CPU time, not just bandwidth. If you let robots traverse the /timeline and /info trees on a Fossil repository without restriction, they'll repeatedly download the entire history of the project, with each version downloaded requiring an expensive tar+gz or zip operation.

Fossil has a cache to cope with this to some extent, but with so many versions in these projects' histories now, any reasonably-sized cache would be busted by allowing robots to run wild through the hyperlink tree. The cache would churn without end.

nuke that sucker from orbit.

Easier said than done, particularly when you're not even on the list of people potentially tasked with doing the doing.

Javascript is evil

Evil is in actions, not in things. Nouns cannot be evil; only particular uses of those nouns can be evil.

Javascript is the root of all evil.

All evil began in 1995?

Badly written javascript is responsible for 99.999% of all safety and security issues

That's not what the data show. The language topping that list is the one SQLite and Fossil are written in, by about four times the number of recorded incidents.

And even C isn't responsible for more than half. It holds a plurality only because there are many inherently-dangerous programming languages, several of which are quite popular.

there has never existed goodly-written-javascript in the entire history of the universe

You've got at least three of the authors of Fossil's JavaScript here in the thread. Your claim is that none of us have written any good JavaScript, either?

it was barfed-up by a moron.

I'm going to be charitable and assume you're using that term in the obsolete technical sense. You are objectively wrong on this point as well.

If you're allowing your technical definitions — and who better than one so pedantic as yourself to insist on precise use of technical words? — to expand to the point that one so objectively successful as Brendan Eich qualifies as a moron from an evaluative psychology standpoint, virtually everyone on the planet is also a moron. To take a position disregarding the value of most of the planet's population is to disconnect from society.

And by the tone and content of this post, you've also disconnected from polite society even among those you consider non-morons.

00:01 Reply: Javascript enforcement (artifact: d7684d5077 user: wyoung)

useless Javascript.

Other posts in this very thread explain why this particular bit of JavaScript is useful. The document Stephan linked you to explains why all of all the other bits of JavaScript in Fossil — the DVCS backing SQLite and this very forum — are useful, too. Moreover, it catalogs the pains we've taken to reduce the use of it and to provide sensible fallbacks where practical.

You're welcome to disagree with individual elements of this on a technical basis, but to dismiss an entire technology the way you've done here is, frankly, unhinged from reality.

use plain links

Are you paying the bandwidth bill for robots to repeatedly download multimegabyte blobs as fast as possible?

The /src links on the page one click away via the links at the bottom of that page are similarly protected since they can cost the public SQLite servers arbitrary CPU time, not just bandwidth. If you let robots traverse the /timeline and /info trees on a Fossil repository without restriction, they'll repeatedly download the entire history of the project, with each version downloaded requiring an expensive tar or zip operation. Fossil has a cache to cope with this to some extent, but with so many versions in these projects history now, I can't imagine any reasonably-sized cache wouldn't be busted by allowing robots to run wild through the hyperlink tree. The cache would churn without end.

nuke that sucker from orbit.

Easier said than done, particularly when you're not even on the list of people potentially tasked with doing the doing.

Javascript is evil

Evil is in actions, not in things. Nouns cannot be evil; only particular uses of those nouns can be evil.

Javascript is the root of all evil.

All evil began in 1995?

Badly written javascript is responsible for 99.999% of all safety and security issues

That's not what the data show. The language topping that list is the one SQLite and Fossil are written in, by about four times the number of recorded incidents.

And even C isn't responsible for more than half. It holds a plurality only because there are many inherently-dangerous programming languages, several of which are quite popular.

there has never existed goodly-written-javascript in the entire history of the universe

You've got at least three of the authors of Fossil's JavaScript here in the thread. Your claim is that none of us have written any good JavaScript, either?

it was barfed-up by a moron.

I'm going to be charitable and assume you're using that term in the obsolete technical sense. If so, you are objectively wrong on this point as well.

If you're allowing your technical definitions — and who better than one so pedantic as yourself to insist on precise use of technical words? — to expand to the point that one so objectively successful as Brendan Eich qualifies as a moron from an evaluative psychology standpoint, virtually everyone on the planet is also a moron. To take a position disregarding the value of most of the planet's population, you've disconnected from society.

And by the tone and content of this post, you've also disconnected from polite society even among those you consider non-morons.

2021-09-14
17:33 Edit reply: segmentation fault when closing a database from within a transaction (artifact: 525878980e user: wyoung)

I'd expect transactions left open on DB conn close to be rolled back, having not received the "COMMIT" call which can now never come by the very fact of the DB conn being closed now.

If closing the DB conn auto-commits all still-open transactions under the Tcl SQLite binding, I'd call that a bug, not a feature. The semantic meaning of transactions is that those not explicitly committed get rolled back, but once again, that can't happen with the conn closed.

16:59 Reply: segmentation fault when closing a database from within a transaction (artifact: 7b66e32abb user: wyoung)

I'd expect transactions left open on close to be rolled back, having not received the "COMMIT" call. If that doesn't happen in this Tcl case, I'd call that a bug, most likely in the Tcl language binding.

14:15 Reply: segmentation fault when closing a database from within a transaction (artifact: e672c5746c user: wyoung)

Okay, yes, good, fix the crash.

...but what did you expect it to do? This code says "close the database and then finalize the transaction." You can't finalize something via a closed conn.

2021-09-12
21:29 Reply: CLI - Output to Clipboard (artifact: f7ce1a96b2 user: wyoung)
2021-09-09
15:17 Reply: Write-ahead logging on SAN (artifact: 8eb42e5374 user: wyoung)

You might indeed get away with it then. I’d run the public test suite on your setup before committing any precious data to the DB, though.

15:00 Reply: Write-ahead logging on SAN (artifact: ac005547d0 user: wyoung)

WAL requires shared memory between any processes that use the DB to coordinate access to the log. So, the answer is “yes” only if all processes using the DB are on the same host.

14:56 Reply: Automatic indexing (idle question) (artifact: 81aad9c4f8 user: wyoung)
2021-09-07
10:05 Reply: Lua db:close deletes WAL file (artifact: cfa89bc7be user: wyoung)

I'll examine [the docs] more thoughtfully.

The relevant section is this one. It directly addresses your wish for the problem to be documented. It doesn’t speak of Lua specifically, but it also doesn’t speak of the other infinity of combinations the same problem can occur under.

2021-09-04
23:05 Edit reply: The characters to be escaped in Sqlite3 to prevent Sql Injection (artifact: affee348de user: wyoung)

…and if your attacker inserts double-quotes in his injected string, some escaped, some not?

No: use prepared statements, period, end of sentence. Do not try to play games, attempting to outthink your attacker. The many attackers collectively have all the time in the world, they need to succeed only once, and they are better-motivated than you are. You are alone and busy, you must succeed in every encounter with the attackers, and you have better things to do with your time. Use the simple solution that always works, so you can move on and do something productive with your day.

23:05 Reply: The characters to be escaped in Sqlite3 to prevent Sql Injection (artifact: 53ec3a55cb user: wyoung)

…and if your attacker inserts double-quotes in his injected string, some escaped, some not?

No: use prepared statements, period, end of sentence. Do not try to play games, attempting to outthink your attacker. The many attackers collectively have all the time in the world, needs to succeed only once, and is better-motivated than you are. You are alone and busy, you must succeed in every encounter with the attackers, and you have better things to do with your time. Use the simple solution that always works, so you can move on and do something productive with your day.

2021-08-31
14:06 Reply: several potential bugs of null pointer dereference (artifact: da7d25397a user: wyoung)

Please don't edit an original posting to change its meaning when it has replies referring to that original meaning. Reply to the reply if you wish to back off on some position you previously took.

12:43 Reply: several potential bugs of null pointer dereference (artifact: 891d1e41b4 user: wyoung)

It's a big step from "Static analyzers are usually wrong" to "They are not bugs." This particular static analyzer may be right in one or more of these cases, but proof requires more than some bold red text on a PNG.

I believe this report wouldn't have been immediately disregarded and so would have received a less dismissive reply if the many problems in the original report were fixed:

  • We knew this "hyx" user by reputation, from prior posting.
  • He made the reports against the trunk version.
  • The line numbers were against the actual src/*.c files, not against the amalgamation.
  • The reports were in plain text — possibly with Markup formatting — rather than bitmapped-images-of-text.
  • Each flagged case was accompanied by separate prose descriptions of the reporter's hypothesis justifying the report. (Or, better, included proof-of-concept code showing how to exploit the claimed flaws.)

I'm with drh on this one: this "hyx" user has shown little regard for what the developers need and care about when going after static analyzer reports.

One more thing: knowing which tool gave this output might be helpful, too, particularly if it's available to drh, so he could just run it himself. If it's somehow proprietary, then we don't know what the tool is doing, so we can't evaluate how useful it is. Just because some bit of dumb anonymous software gave a report about some other bit of software doesn't justify work chasing the resulting output.

11:48 Reply: several potential bugs of null pointer dereference (artifact: 823f1899f7 user: wyoung)

It would be better to do it against the trunk version. Not only does that eliminate the gap between the last release and the current development version, it gives you the pre-amalgamation source files to work on, which will make your tool’s line references more useful.

2021-08-23
00:08 Reply: How to select columns that have name beginning with same prefix? (artifact: 65cd8799fb user: wyoung)

Even with this naming issue aside, "50 or 100 columns" is a schema design smell. Such a table probably holds more than it should, so it would benefit from a pass or three of normalization.

2021-08-16
21:10 Reply: Comment about application file format (artifact: 2138fc3612 user: wyoung)

Are you saying that CFBF isn't a post-OOXML format, it's what preceded the ZIP-based OOXML formats?

19:58 Reply: Comment about application file format (artifact: 28ed4551e2 user: wyoung)

Microsoft Office format does use a wrapped pile of files

Perhaps drh was thinking of the pre-OOXML formats. It does say "DOC" rather than "DOCX" and so forth. That interpretation is somewhat untenable, though, given that the referenced document only goes back to 2014.

it isn't ZIP

OOXML most definitely is ZIP-based.

I just created a PPTX file in PowerPoint 2019, and "unzip -t" reads the resulting document successfully. From my reading about this CFBF format, a file in that format shouldn't pass this test. CFBF is reportedly the basis of the MSI format, for example, and the unzip test doesn't pass on an MSI file I created recently.

How do I get Office to produce a CFBF-based file, one that fails this unzip test?

The relevance, of course, is toward the referenced SQLite doc: until we know what it takes to produce a CFBF based doc, it doesn't seem useful to be talking about it in that doc, except perhaps in reference to MSI.

I am not sure how to propose adding new things to the list of application ID numbers into the magic.txt

It depends on which implementation of file(1) that comes from, but statistically, it's likely to be this one. You may find the core of this project referenced as "libmagic", since it has wrappers other than file(1).

Using separate journal files, etc doesn't seem the best way to me

Those are only created when the DB is in WAL mode. Solution: don't use WAL mode.

a few people prefer TRON

I hadn't even heard of TRON encoding before today.

Who are these "few people" that they have any hope of countering the three decades of inertia behind Unicode?

At minimum, I think it'd take a supremely popular new OS that used TRON by default to wag this dog, this late in the game.

PostScript…doesn't have good FFI…not really a problem with SQLite itself

Yes, so why is it any objection here?

Never mind the specifics: you seem to be using these extremely niche cases to argue against SQLite as an application format. So fine, it doesn't work in those niches. How does that argue the broader point?

I once participated in an Internet flame thread where someone tried to argue that the adage "there's always someone worse off than you" was wrong by pointing out that you could be the worst-off person in the world. And yes, for one in 7.5 billion people, that's true, but to take that position, you're arguing a one-in-7.5-billion-against position.

Is the point here to be "technically right", even if that means being wrong in almost every practical case?

2021-08-15
05:49 Reply: Numeric vs integer (artifact: 3837a9a0a4 user: wyoung)

Q1 is answered in section 2 here.

As for Q2, the difference will depend on the CPU, the data, the compiler, the C standard library… it’s a matter for benchmarking, not for vague speculation on Internet discussion fora.

2021-08-12
11:51 Reply: help! decimal field multiplication error? (artifact: 0344686ce1 user: wyoung)

Technically true, but see section 2.2 of the doc I linked.

09:12 Reply: help! decimal field multiplication error? (artifact: 9706e3b8f9 user: wyoung)
2021-08-10
18:41 Reply: SQLite Binary Log support! (artifact: f036b0f271 user: wyoung)

You mention dqlite, but have you tried and rejected the other major distributed SQLite variants, BedrockDB and rqlite? If so, what makes them unsuitable?

18:40 Delete reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed (artifact: b57497e98a user: wyoung)
Deleted
17:32 Reply: sqlar: is it reliable to use length(sqlar.blob)==sqlar.sz determine whether the data is compressed (artifact: c04de3151d user: wyoung)

Have you tried and rejected the other major distributed SQLite variants, BedrockDB and rqlite? If so, what makes them unsuitable?

2021-08-08
08:09 Reply: large blank areas on config files. (artifact: 5f36b95cdc user: wyoung)

These are generated by GNU Autoconf, so their format is not under direct control of the SQLite developers. See the Autoconf docs for details.

If you’re worried about space efficiency, Fossil compresses the content of the files it manages, so the extra space is already “deflated” out of those files on disk and when transferred over the network. The same is true of the distribution Zip files and tarballs.

2021-07-31
06:22 Reply: Odd CL behaviour (artifact: 692b6786fe user: wyoung)

The current Mac binaries aren't dynamically linked to either libedit or to libreadline:

$ otool -L ~/Downloads/sqlite-tools-osx-x86-3360000/sqlite3 
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1252.250.1)
	/usr/lib/libz.1.dylib (compatibility version 1.0.0, current version 1.2.11)

Since macOS ships libedit in its stock config, the pre-built binaries should link to that rather than to Readline.

Trudge, another option is to use the Homebrew build of SQLite, which is always up-to-date and brings in Readline as a dependency. The main problem with this is that you then have two sqlite3 binaries on your system, both of which use different history file syntaxes, so bouncing between them can cause you to lose command history as each overwrites the other's output.

2021-07-30
21:02 Reply: Mention if printf() should have been called sprintf() (artifact: cafc54c318 user: wyoung)

printf() should have been called sprintf()

What end would this public self-flagellation serve?

You’re welcome to your opinion. I just don’t see what benefit the SQLite project gets from publishing it as part of the official docs.

If SQLite were the sort of project where APIs could be renamed, we’d have a point of discussion, but it isn’t, so we don’t.

bad grammar

Do you have a suggested replacement? Perhaps “…the reason is…that…”?

Found a typo, by the way: “student a Duke”

2021-07-28
23:26 Reply: Calculating Trip Duration (artifact: 9a21870d2a user: wyoung)

Why not just write abs(datediff(...))? See the docs.

…and then ask why you've got your start and end times swapped.

2021-07-26
03:45 Reply: sqlite3_carray_bind and constness (artifact: 4e357e3f76 user: wyoung)

Much of this excellent presentation is concerned with const-ness.

The bit about GCC “pure” functions is a promise of no side effects, approximately equivalent to SQLite’s “deterministic” UDF qualifier. The optimization depends on the compiler being able to prove that passed values will remain const.

The article also explains why SQLite’s amalgamation build is an effective optimization technique in absence of LTO in the compiler.

C++ takes it even further now with constexpr, but until someone forks a C++ only variant of SQLite permitting these optimizations, we’re getting off topic…

2021-07-23
22:03 Reply: Can SQLite be used with AWS Elastic File System (EFS)? (artifact: d2591c0c6e user: wyoung)

That's nice, but even within the same AWS zone the round-trip time to do the lock will be orders of magnitude more expensive than purely on-host locking. Then if you go and use the cloud the way it was meant to be used, distributing your operations across data centers, expect to add another zero or two to the ping time.

2021-07-21
07:59 Reply: Wrong version for download (artifact: 30ebe31c13 user: wyoung)

You didn’t unpack it in your PATH. Either:

  • do so; or
  • cd into the directory where it’s unpacked and run it as ./sqlite3; or
  • drag sqlite3 from the extracted folder into Terminal so you get an explicit path
2021-07-20
22:08 Edit reply: Calculating duration in ISO8601 timestamp (artifact: 1973b22f43 user: wyoung)

If you use a colon instead of a dot to separate the whole seconds from the fractional seconds, it isn't ISO 8601, so it isn't legal in SQLite. This includes strftime().

Rather than continue to arm-twist bad data formats in place, I'd reimport those columns. While you're at it, add a "T" between the date and time parts and put a "Z" on the end to make it absolutely clear that you're giving the time in UTC.

And if you aren't using UTC, convert the time from whatever local time zone you mean to UTC so the data doesn't break again when you encounter two or more time zones in a single calculation.

22:07 Reply: Calculating duration in ISO8601 timestamp (artifact: 29a04ec237 user: wyoung)

If you use a colon instead of a dot to separate the whole seconds from the fractional seconds, it isn't ISO 8601, so it isn't legal in SQLite. This includes strftime().

Rather than continue to arm-twist bad data formats in place, I'd reimport those columns. While you're at it, add a "T" between the date and time parts and put a "Z" on the end to make it absolutely clear that you're giving the time in UTC.

And if you aren't using UTC, convert the time from whatever local time zone you mean to UTC so the data doesn't break again when you encounter more than two time zones in a single calculation.

21:00 Reply: Calculating duration in ISO8601 timestamp (artifact: 77fd036ef3 user: wyoung)

julianday('datetime')

This is the right track, but lose the quotes. You've passed the literal string "datetime" to the function, not the name of a column.

If you had to quote the column name, you'd use double-quotes or backquotes. See the quoting rules for more details.

2021-07-14
16:37 Edit reply: Need help with read only access to database (artifact: 0fb2aceb84 user: wyoung)

3.7 is the version our sysadmins installed

Why are you limited to only what your sysadmins installed? SQLite is easy to compile and install. You don't need complex packaging systems with carefully-crafted permission sets to make it work properly. It's a single executable. On some systems, you can even link it statically so it doesn't depend on platform libraries, so you may even be able to build on a different Linux system type and copy the executable over.

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

Search for "read" in the SQLite change log, then ask yourself if you're seriously wanting someone to bisect 8 years of history with numerous changes to the required functionality for you, without even having a reproducible test case to do the bisecting with.

"linux OS" covers hundreds of distributions, each of which supports many filesystems, atop which you have SQLite's own multifarious build details, creating a combinatorial explosion of likely sources for your problem. Would you like us to simply guess which one covers your particular case?

What I can tell you without knowing your platform details is that the --readonly flag went into the sqlite3 shell in February of 2018, subsequently released in 3.23.0. That feature alone might make the difference between success and failure.

(The commit comment only speaks of the shell's .open command, but the flag is available to the shell itself, so you can add --readonly to your calls to sqlite3 without using .open directly.)

Another platform-independent guess is that you're using WAL mode, so the read-only WAL feature that went into 3.22.0 is what you need, possibly coupled with the --readonly flag.

If these informed guesses are wrong, then we're down to filesystem issues, kernel details, build details...all things that are currently hidden from us by your vague "linux OS" specifier.

I would not use the versions above to tell your sysadmins to install 3.22.0 or 3.23.0, though. I repeat my initial request: install the latest release, then let us know if that fails.

16:24 Reply: Need help with read only access to database (artifact: b4c2f182db user: wyoung)

3.7 is the version our sysadmins installed

Why are you limited to only what your sysadmins installed? SQLite is easy to compile and install. You don't need complex packaging systems with carefully-crafted permission sets to make it work properly. It's a single executable. On some systems, you can even link it statically so it doesn't depend on platform libraries, so you may even be able to build on one system time and copy the executable over.

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

Search for "read" in the SQLite change log, then ask yourself if you're seriously wanting someone to bisect 8 years of history with numerous changes to the required functionality for you, without even having a reproducible test case to do the bisecting with.

"linux OS" covers hundreds of distributions, each of which supports many filesystems, atop which you have SQLite's own multifarious build details, creating a combinatorial explosion of likely sources for your problem. Would you like us to simply guess which one covers your particular case?

What I can tell you without knowing your platform details is that the --readonly flag went into the sqlite3 shell in February of 2018, subsequently released in 3.23.0. That feature alone might make the difference between success and failure.

(The commit comment only speaks of the shell's .open command, but the flag is available to the shell itself, so you can add --readonly to your calls to sqlite3 without using .open directly.)

Another platform-independent guess is that you're using WAL mode, so the read-only WAL feature that went into 3.22.0 is what you need, possibly coupled with the --readonly flag.

If these informed guesses are wrong, then we're down to filesystem issues, kernel details, build details...all things that are currently hidden from us by your vague "linux OS" specifier.

I would not use the versions above to tell your sysadmins to install 3.22.0 or 3.23.0, though. I repeat my initial request: install the latest release, then let us know if that fails.

14:57 Reply: Need help with read only access to database (artifact: 4977b5b804 user: wyoung)

SQLite version 3.7.17 2013-05-20 00:56:22

Read-only behavior has improved in the last 8 years.

Upgrade, then we can discuss any remaining issues you have.

2021-07-12
13:24 Edit reply: How to define search query for special characters e.g(ā č ō) (artifact: 382c824c6d user: wyoung)

I'm very new to Sqlite and programing in general

Everyone started there, but this is not the place to learn how to program. We will expect a certain basic level of competence with your tools.

forgive me in advance if my question is not smart

How To Ask Questions The Smart Way

Pahlavi alphabet e.g (ā, č, ē)

That looks like a transliteration into a Western alphabet, not Pahlavi script. Your problems may multiply once you start adding right-to-left and ligatures and all the other stuff you get in non-Western scripts.

What should I change in raw query

First, stop using raw queries. It's unsafe to concatenate user-provided text with static SQL text — as with your "+text+" code — and then execute it.

Instead, you should prepare your SQL in advance, which the Android SDK calls "compiling" for some reason. (By using standard SQLite terminology, you can also get value out of SQLite's C API documentation.) Once you have a prepared statement object, you can then bind values to the indexed parameter spots in the prepared query. This prevents SQL injection attacks, among other benefits.

search for non English characters

SQLite isn't built with ICU by default, but since I'm not an Android developer, I cannot tell you whether Google builds their SQLite library with ICU enabled. If I may guess from the existence of projects like this one, they do not. Without that extension or something like it, LIKE is documented to do surprising things with non-ASCII Unicode characters. The ICU build option not only allows Unicode-aware case folding, it also allows for collation to work properly and more.

While I was researching this answer, I found — right up at the top of the documentation for Android's SQLiteDatabase class — a note about Unicode collation, which you may find valuable.

13:21 Reply: How to define search query for special characters e.g(ā č ō) (artifact: c1dd92cf55 user: wyoung)

I'm very new to Sqlite and programing in general

Everyone started there, but this is not the place to learn how to program. We will expect a certain basic level of competence with your tools.

forgive me in advance if my question is not smart

How To Ask Questions The Smart Way

Pahlavi alphabet e.g (ā, č, ē)

That looks like a transliteration into a Western alphabet, not Pahlavi script. Your problems may multiply once you start adding right-to-left and ligatures and all the other stuff you get in non-Western scripts.

What should I change in raw query

First, stop using raw queries. It's unsafe to concatenate user-provided text with static SQL text — as with your "+text+" code — and then execute it.

Instead, you should prepare your SQL in advance, which the Android SDK calls "compiling" for some reason. (By using standard SQLite terminology, you can also get value out of SQLite's C API documentation.) Once you have a prepared statement object, you can then bind values to the indexed parameter spots in the prepared query. This prevents SQL injection attacks, among other benefits.

search for non English characters

SQLite isn't built with ICU by default, but since I'm not an Android developer, I cannot tell you whether Google builds their SQLite library with ICU enabled. If I may guess from the existence of projects like this one, they do not. Without that extension or something like it, LIKE is documented to not match on non-ASCII Unicode characters. The ICU build option also allows for collation to work properly, and so on.

While I was researching this answer, I found — right up at the top of the documentation for Android's SQLiteDatabase class — a note about Unicode collation, which you may find valuable.

2021-07-03
00:16 Reply: SQLite3.exe Command shell: Iterate over table of csv files? (artifact: da9fa2765a user: wyoung)

If programmability is not available in the command shell...

...then you're using a terribly weak excuse for a command shell. :)

Dr. Bourne taught the world that programmable command shells was a good idea in 1977. His shell for UNIX V7 had logical expressions and loops, which didn't arrive in cmd.exe until decades later. If you take the original Unix shell spirit, we also had the ability to do arithmetic on variables, allowing us to clean up all that manual "([Gt 24 to 25 Weeks SUM 1]*25)+" stuff in your Lua code via expr and bc.

I believe you can do that today via your original cmd.exe method by using its FOR feature, but I wouldn't bother. Instead, I recommend that you continue down your new path: use a better-designed programming language from the start rather than try to arm-twist cmd.exe to suit.

This Lua script generates a .sqlite file

Why ever for? Call SQLite directly from Lua instead. It'll not only save you the temporary output SQL file, it'll run faster.

If you don't like that wrapper, I count seven others. (Do note that lsqlite3 is far and away the most popular among them, though.)

And again, you really should generate that "Gt...Weeks SUM" stuff rather than write it manually.

2021-07-02
23:53 Edit reply: Integrated user management for a group of repositories (artifact: e3f0b3f77f user: wyoung)

How would you set up the dependency graph? In the SQLite spirit, let's first try the simplest thing that could possibly work:

Fossil forum Fossil SCM SQLite forum SQLite proper
box "Fossil forum" fit
arrow 50%
box "Fossil SCM" fit
arrow 50%
box "SQLite forum" fit
arrow 50%
box "SQLite proper" fit

That means sanctified entities like drh can log in anywhere with all capabilities because he has the all-powerful Setup capability on the sqlite.org/src repo. It would also mean that lesser beings like you and I can log into the Fossil forum because we have a login here on this forum.

But hark! Now we have new problems:

  1. Just because we have a login on the SQLite forum, we have rights on the Fossil SCM code repo? Really??

  2. I have more rights on the Fossil SCM repo than here on this forum, and a different set of extended rights on the Fossil forum. I believe login groups allows user shadowing, so I could have independent logins with different capabilities while you get a single login everywhere, but it does at least go to show that we're not going to end up with "one login everywhere" regardless.

We have to fix problem #1, though. That one's fatal. Let's try again:

Fossil forum SQLite forum SQLite proper Fossil SCM
    box "Fossil forum" fit
    arrow 50%
    box "SQLite forum" fit
    arrow 50%
SP: box "SQLite proper" fit
FS: box "Fossil SCM" fit at .5 above SP
    arrow from FS.s to SP.n

Now we're saying that the likes of drh get a login on the Fossil SCM repo and all the forums, but we've fixed problem #1 by requiring that those who have a login on the Fossil SCM repo but not on SQLite's own source repo (like me) must sign up separately there.

But hark! There's at least one new problem: why don't people like me get a login on the Fossil forum delegated from the Fossil SCM repo? It appears that the feature only allows you to set up a single delegation per repository; you can't make the Fossil forum repo delegate to both the SQLite forum and Fossil SCM source repos, so that members of either get a login on the Fossil forum repo as well.

…And I haven't even tried to figure out where the combined Pikchr source/forum repo fits into any of this. Which single delegation should it declare, and why?

23:52 Reply: Integrated user management for a group of repositories (artifact: 896fce6288 user: wyoung)

How would you set up the dependency graph?

Fossil forum Fossil SCM SQLite forum SQLite proper
box "Fossil forum" fit
arrow 50%
box "Fossil SCM" fit
arrow 50%
box "SQLite forum" fit
arrow 50%
box "SQLite proper" fit

That means santified entities like drh can log in anywhere with all capabilities because he has the all-powerful Setup capability on the sqlite.org/src repo. It would also mean that lesser beings like you and I can log into the Fossil forum because we have a login here on this forum.

But hark! Now we have new problems:

  1. Just because we have a login on the SQLite forum, we have rights on the Fossil SCM code repo? Really??

  2. I have more rights on the Fossil SCM repo than here on this forum, and a different set of extended rights on the Fossil forum. I believe login groups allows user shadowing, so I could have independent logins with different capabilities while you get a single login everywhere, but it does at least go to show that we're not going to end up with "one login everywhere" regardless.

We have to fix problem #1, though. That one's fatal. Let's try again:

Fossil forum SQLite forum SQLite proper Fossil SCM
    box "Fossil forum" fit
    arrow 50%
    box "SQLite forum" fit
    arrow 50%
SP: box "SQLite proper" fit
FS: box "Fossil SCM" fit at .5 above SP
    arrow from FS.s to SP.n

Now we're saying that the likes of drh get a login on the Fossil SCM repo and all the forums, but we've fixed problem #1 by requiring that those who have a login on the Fossil SCM repo but not on SQLite's own source repo (like me) must sign up separately there.

But hark! There's at least one new problem: why don't people like me get a login on the Fossil forum delegated from the Fossil SCM repo? It appears that the feature only allows you to set up a single delegation per repository; you can't make the Fossil forum repo delegate to both the SQLite forum and Fossil SCM source repos, so that members of either get a login on the Fossil forum repo as well.

…And I haven't even tried to figure out where the combined Pikchr source/forum repo fits into any of this. Which single delegation should it declare, and why?

2021-06-30
12:42 Reply: Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8 (artifact: 9d62d2740b user: wyoung)

Based on the commit comment, that should do nicely. Thank you!

11:31 Reply: Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8 (artifact: 0b81c3e32f user: wyoung)

Indeed: there could be a whole table of values, listing what you break with all values from 1..9 or whatever the max recommended upper default limit ends up being.

More ↓