SQLite Forum

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.

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?

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

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

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

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.

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.

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.

Pipe it.

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.

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.

We already have that.

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.

…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.

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.

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.

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.

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.

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

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?

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.

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

https://www.sqlite.org/floatingpoint.html

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

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.

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.

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”

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

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

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…

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.

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

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.

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.

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.

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.

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.

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.

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

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:

    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?

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

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.