What would be the impact if SQLite running on Windows began requiring APIs that are only available in Windows Vista and later?

I'm very curious to find out! I know that's not the question ;-)

I am interested to hear why someone might be interested in updates for those platforms too.

Dump them all I say!

Do you have specifics as to the APIs and functionality affected?

Looking at the code it seems like SQLite also tries to support ANSI (aka 8bit non-Unicode) APIs.

There is also support for Windows CE whose final release was 12 years ago, extended support ended 3 years ago, but license sales continue for another 2 years.

Given the vendor of those operating systems has long since had them out of any kind of support, it is not unreasonable for you to do the same. I'd advise supporting them be a separate paid service because that will reflect both the costs you incur providing the support, and someone needing the support will be able to get it focused on exactly what matters to them. Paid extended / long term support is a very reasonable service to offer.

FWIW Firefox is dropping support for Windows 7, 8, and 8.1 in a few months. That Firefox version was released in 2023.

I'd honestly be surprised if any of the pre-XP operating systems listed could use 3.53 in the first place, especially the pre-95 ones.

I use Fossil on a Windows XP x64 system, and even that has to be limited to version 2.26 (I think I could hypothetically build newer versions to run on it, but that requires the whole Visual Studio compiler to be set up and installed, I am not bothering); Fossil's good backwards/forwards compatibility makes it not very critical to upgrade even in that case.

Overall, I would think if the newer APIs provide good utility for SQLite, go for it and drop the older OSes. Windows Vista is ~19.5 years old, and hasn't been supported for about 12 years now. If that's the baseline level, it should be more than adequate for just about everyone.

There's two options for anyone that really needs the legacy compatibility:

• Keep using SQLite 3.53. It's already very featureful and unlikely to be a limiting factor for a good long time.
• Backport newer releases by removing or altering features. It's the harder route, but if you develop on legacy OSes, I'm sure you already expect some difficulty.

I think these changes will also apparently break WindowsCE, for which Microsoft ended extended support on 2023-10-09.

The same argument applies though: Users of WindowsCE can still use SQLite 3.53 or earlier. They just won't be able to upgrade to SQLite 3.54 or later. There is no new on-going development for WindowsCE. If the software running on WindowsCE works now, it should continue to work. Security patches should not be a factor, since you ought not be using WindowsCE device that isn't air-gapped. Am I wrong?

I'm all for dropping older OS'es, in general, WinXP and lower included.

But in the case of SQLite, the OS is circumscribed to the VFS.
Which is auto-selected at runtime and can be forced manually anyways.

So why not just fork the Windows VFS into older and newer versions,
with only the latter getting ongoing support?

That way, even WinXP can benefit from newer SQL,
since the VFS is much more stable in general.

Perhaps you mean to stop testing on those platforms too?
There's precedent of SQLite having code for non-tested platforms,
like EBCDIC I think, so keeping older and now untested code for a
WinXP VFS would be similar. Just thinking aloud. My $0.02.

Perhaps you mean to stop testing on those platforms too?

We don't have the ability to test on anything other than Win10 and Win11. If recent versions of SQLite work on any other versions of Windows, that is by good luck.

If the developer of the OS no longer supports that version of the OS, you're arguably fine in no longer supporting it yourself. After all, your user can no longer say they updated their computer and your product no longer works. However, from experience with other software on other platforms …

You may have a problem with vulnerability updates. If the latest version of your software no longer supports a version of the OS, you can't tell your users to fix a security problem by updating to your latest version. You may want, or need, to retro-fit any security update to an old version of your software. Or decide not to. So version 3.53.0 may become very important. Perhaps pay special attention to it. Perhaps rename the next version 3.60.0 to mark the major change.

I'm not arguing for any specific course of action. I'm now retired and no longer use SQLite for anything critical.

The platforms that are being deprecated have unpatched vulnerabilities by themselves, independent of SQLite. That's why I said that if you are running WindowsXP or similar, you really ought to air-gap it from the internet, so that vulnerabilities don't matter.

And now I'm tempted to write a VFS module for classic MacOS, just for the absurdity.

I have a circa-2005 iBook with a PPC processor that I use for CPU-diversity testing of SQLite, because PPC is big-endian. The latest trunk SQLite code still compiles and runs on that old PPC.

The Fossil version control system that we use to manage SQLite development also compiles and runs on the old iBook, as long as I omit HTTPS support (which requires OpenSSH which does not compile any more). This is so that I can easily load and test the SQLite code on that platform.

I have trouble getting newer machines to talk to the old iBook over SSH because newer machines refuse to talk to the older (and less secure) encryption algorithms available on the iBook, and I haven't been able to get a newer version of SSH to compile. But it turns out that you can give options to the newer and more secure versions of SSH in order to get them to converse with older less-secure protocols, so I can still use that as a work-around.

Have the post Windows 7 C libraries become more compatible with Unix versions so dropping support for the prior versions lets you dispense with a lot of OS-specific code?

Please don't.

My game still supports Windows XP. I don't know that I have a lot of users with it, but why break it? What do you gain from this? I already can't upgrade libcurl now for this reason, but, SQLite is very straightforward and portable.

If you start with this mindset, eventually your project will only work on Windows 10+. Many projects go this route. They unthinkingly serve the Machine. And who can oppose it?

For my day job, I also support software back to XP SP3. That's a .NET project. It takes very little effort.

I'm heartened to know SQLite does run on Windows 95 and 98.

God bless.

James

What do you gain from this?

• Improved performance on newer Windows platforms.
• Reduced maintenance burden, allowing the devs to spend more time focusing on improvements to newer platforms.

Version 3.53.X will continue to be available to you for free. If you need 3.54.0+ for WinXP and that is valuable to you, we can provide that for you under contract, or you can maintain a WinXP branch yourself since everything is open-source. The SQLite devs do not have infinite bandwidth. Choices need to be made. Dropping support for WinXP is an easy thing to do in order to free up resources to work on other things that are important to more people.

There's always some fraction of people that seem to believe supporting legacy hardware and/or software is free without drawbacks. Whether it's SQLite supporting Windows XP, or Linux running on a 486. In both cases, showing up with money for continued development and support is an option, but it'll never be taken. :-)

Even in the hypothetical scenario that Windows 10+ are the only supported versions, what's the downside? Pretty much 99%+ of Windows machines are already on those versions¹, and even Windows 10 is (mostly) out of support these days. If it benefits SQLite to depend on APIs only available in Windows 10 and forward, go for it. (That's not even being talked about: the trunk and future 3.54 release are now geared towards requiring Windows Vista at a minimum, a version that's almost 20 years old already.)

It's cute, and even some retro-focused circles appreciate continued development on ancient versions of Windows, but it's unreasonable to expect projects like this to tailor themselves for it. If you're using SQLite 3.53 or older today, and it works, it'll continue to work as it always has.

---

1. ^{^} Seriously, every version before 10 is a rounding error.

what's the downside?

The big problem is the deployed base of Windows systems hooked up to fancy scientific instruments and other types of deployed-in-the-field equipment. That they might have a pre-10 version of Windows on is quite believable.

But they really can normally just not update in the first place. They already have to have rules like that for so many other components (such as the OS). Or they can pay for a support contract, as that's a lot less than the cost of updating the instrument itself, often by many orders of magnitude. (Yes, that they'd budgeted for never spending that in the first place isn't my problem.)

Legacy science instruments can continue using SQLite 3.53.x or earlier - whatever it is they are using now. The instruments clearly do not have a need to stay up-to-date with the latest changes, or else they wouldn't still be running WinXP. SQLite 3.53.x isn't suddenly going to vanish. They can keep using it. It is just that they can't upgrade to 3.54.0 or later without also upgrading the underlying OS.

Richard, what kind of work is required to maintain this compatibility?

I am not a rich man, I cannot afford a support contract, but I am a stubborn man.

I would be willing to sign a contributor agreement if that is how your project operates. What would I need to do?

I keep XP support on my projects because I liked XP, and more than that, I suspect I would like anyone who is still using it. In my code it isn't a huge amount of effort, since it's now a stable target.

I also have some machines that run Vista, and I suspect that'd be next on the chopping block if you let this spirit take hold. From what I've seen it doesn't let go.

Thank you.

God bless.

James

I'm not Richard, but you basically need to maintain os_win.c, that's it.

The OS-specific code is less than 5% of SQLite library code, thanks to the clean VFS abtraction.

From experience, I'd be surprised DRH took you up on your offer, but who knows.

You can also maintain that XP VFS somewhere else. And explicitly use it, for example via:

sqlite3_vfs_register(&myXpVfs, 1);

Where that 1 makes it the default VFS.

So as Richard wrote, either you're stuck on 3.53 for XP; or you explicitly use your own VFS,
as a copy of os_win.c from the 3.53 era, to keep using the latest and greatest SQLite.
Might require a custom build of SQLite too, if the newer os_win.c won't build for you anymore.

My $0.02.

When you say 5% of the library code, are you treating the library and the shell program as distinct entities? Shell.c has dozens of windows-specific code blocks and isn't as clean in isolating things.

Right. Good point. The shell got a major redesign, unlikely to ever port to XP, so you're better off using the old shell circa 3.53 (or before), and load a newer SQLite library DLL, which might require compiling it to use a shared-lib in the first place, as it builds statically with it by default (just compiles its code with the amalgamation, close enough). You'd get newer SQL and features from the shared-lib, and with SQLite's good backward compatibility, the old shell will mostly be OK, I suspect. Remains to be verified of course.

So as Richard wrote, either you're stuck on 3.53 for XP; or you explicitly use your own VFS

I thought about whether it would be difficult to abstract away anything system-related from SQLite, like having a conditional that strips bindings to the system APIs but mandates that the developer should provide everything needed. I initially thought that it is mostly the VFS, but after the comment from drh about Slim Reader/Writer locks, I found that the new bindings are part of the sqlite3_mutex_methods structure that, in theory, might be configured with SQLITE_CONFIG_MUTEX. But these are already two instances of abstraction that need to be fulfilled/maintained, already a difficult task.

An interesting observation: sometime in the past, CEF (Chromium Embedded Framework) and probably Chrome itself stopped supporting XP. For a project, I at some point decided to test run it in XP to see what the system will complain about. The first symbol not found in the system DLLs was AcquireSRWLockExclusive. So the Chromium team made the switch much sooner (using the Slim locks), but this fact does tell something about SQLite, which tries to support older systems much longer. It is really a good thing. I imagined my own scenario where XP support might be useful, not very likely, but nevertheless. Sometimes I have to use older peripherals that are no longer supported by Windows 10 64-bit, and a Windows XP 32-bit VM works OK in this case. Because my tools often have SQLite linked, and with the newest version possible, I can imagine that trying to run them in such VMs might fail now.

Version 3.54.0 switched to using Slim Reader/Writer Locks on Windows, because they are faster. But they are not available on WinXP.

I have reached out via private channels to Mr. Bellinger, but he hasn't gotton back to me yet...

FWIW, it's not so hard to create a shim package that you can put alongside the amalgam to get up and running in such cases. MS is open sourcing such a thing for Win7 as it happens. I think it would be very doable.

Can a SQLITE_ZERO_VFS switch be added, that removes all OS dependencies and allows switching to an application-provided VFS implementation?

1. Eliminate default baked-in VFS implementations (and their dependencies) for common operating systems/platforms
2. Applications must explicitly initialize SQLite by providing the entire VFS implementation

Existing VFS implementations can be converted to an external VFS module implementing step 2.

SQLite internally already abstracts the OS behaviour through the VFS mechanism. Most of the code is a pure C library without any OS dependencies. Take the VFS idea to its logical conclusion: separate the core SQLite, pure C, library entirely from the common OS specific VFS implementations.

The issue with legacy OS compatibility is the baked-in VFS implementations add strong dependencies on OS functions, which prevent the process from loading on OSes that lack those functions.

For example, SQLite 3.54, compiled for Windows, will have a new dependency on the AcquireSRWLockExclusive@kernel32.dll Windows API. This function is provided on Vista (and up). Attempting load a process using that function on Windows XP will fail as kernel32.dll lacks that function (the OS will refuse to load the EXE/DLL).

Applications that require legacy OS support can:

• Enable SQLITE_ZERO_VFS mode
• Switch to an external VFS module
• Maintain the legacy VFS module going forward.

The key point is legacy VFS maintenance becomes the Application's responsibility. SQLite's can provide updated optimized/new VFS implementations, as needed, that will be, by default baked-in.

To clarify, I have no issue with SQLite dropping Windows XP support.

I was simply outlining a change that could easy backwards compatibility issues.

Can a SQLITE_ZERO_VFS switch be added, that removes all OS dependencies and allows switching to an application-provided VFS implementation?

From os_setup.h:

/*
** Figure out if we are dealing with Unix, Windows, or some other operating
** system.
**
** After the following block of preprocess macros, all of 
**
**    SQLITE_OS_KV
**    SQLITE_OS_OTHER
**    SQLITE_OS_UNIX
**    SQLITE_OS_WIN
**
** will defined to either 1 or 0. One of them will be 1. The others will be 0.
** If none of the macros are initially defined, then select either
** SQLITE_OS_UNIX or SQLITE_OS_WIN depending on the target platform.
**
** If SQLITE_OS_OTHER=1 is specified at compile-time, then the application
** must provide its own VFS implementation together with sqlite3_os_init()
** and sqlite3_os_end() routines.
*/

Thanks. Sorry, I must have missed that particular switch.

Great! So the functionality already exists to replacing the VFS. So you can already implement a Windows XP compatible VFS implementation, at the application level.