PDO SQLite versions support

[imfx77]

I've been developing a new and quite DB-utilizing plugin for a while now. I have some custom schema routines that are invoked for specific actions like compacting and reindexing the tables on demand. Thus, correctly handling schema versions for MySQL/PostgreSQL/SQLite is a must. And I made the effort to automate unit tests for that purpose.

I use KB v1.2.35 docker image for local development, and it comes with PHP 8.3.2, respectively the versions of all PDO extensions are v.8.3.2. It works great for all MySQL/PostgreSQL/SQLite with minor differences in the schema SQL statements.

However, the unit testing action on GitHub, would deploy and run with PHP 7.4.3, as a minimal requirement in KB's composer file. And I just realized that older PDO extension versions for SQLite in specific are lacking support for basic things like:

• UPDATE FROM from SQLite v.3.33
• ALTER TABLE DROP COLUMN from SQLite v.3.35
• RIGHT and FULL OUTER JOIN from SQLite v.3.39
• etc

I am further confused, as I cannot even find any information on which PDO version covers which SQLite version 🙄

Trying to achieve a backward compatibility in the dark is kinda tough call. I am really willing to meet the minimum KB requirement- as PHP 7.4 still has a big user base and SQLite is the default internal DB implementation of KB. BUT apart from driving big differences to the SQLite schema that are necessary to achieve that, those are also ugly and hacky, and introduce performance issues. For example, emulating a `DROP COLUMN' means copying a whole table!

I am really puzzled what should I do ...
Unfortunately, not using things like drop column, update from or joins will confront me with an entirely different bunch of problems, and an enormous rework if possible at all.
And even if I consider ignoring the backward compatibility and sticking to the latest supported PDO SQLite features, I will still get all my unit tests failing as those refer the configuration from the official KB repo and PHP 7.4.3.

So, anyone, if you have some insights on the issue, please comment and advise.
Thanks in advance 👍

[fguillot]

Kanboard hasn't deprecated yet the support of PHP 7.4. The unit tests on GitHub Actions runs with both PHP 7.4 and PHP 8 (only for Sqlite at the moment).

PHP can be compiled and linked with any version of Sqlite (libsqlite >= 3.5.0 according to the docs). Each Linux distribution will ship with slightly different versions of Sqlite.

The unit tests for PHP 7.4 uses this Dockerfile. It has whatever version of Sqlite that comes with Ubuntu 20.04 LTS.

[imfx77]

First of all, thank you for the answer 👍
It at least confirms that I need to have a backward support to PHP 7.4.

And apparently libsqlite >= 3.5.0 in PHP 7.4 is waaay older than the feature versions I need i.e. 3.33, 3.35, 3.39 🙄

The unit tests for PHP 7.4 uses this Dockerfile. It has whatever version of Sqlite that comes with Ubuntu 20.04 LTS.

Indeed, my unit test configuration is set to use ubuntu-latest which appears to be Ubuntu 20.04.4 LTS. This is confirmed by the unit test logs. And I also logged the PHP extension versions - can be seen in the Unit tests with Sqlite section - all PDOs are versions 7.4.3.

Am I correctly understanding, that it is the Ubuntu distribution which is responsible for those PDOs and not the PHP installment itself? And that I can run unit test with PHP 7.4 yet with newer PDO versions by referring to some other Dockerfile? How would that match the libsqlite version of PHP itself?

The unit tests on GitHub Actions runs with both PHP 7.4 and PHP 8 (only for Sqlite at the moment).

How can I control this? Where is that configured on my side?
It seems pretty automated to me, and for me it only runs with PHP 7.4.
Should I mangle the KB composer file in a cloned KB repo, and refer the fork in my unit test config?
Or is there some more clever and convenient way?

[fguillot]

Indeed, my unit test configuration is set to use ubuntu-latest which appears to be Ubuntu 20.04.4 LTS.

ubuntu-latest is currently the version 22.04 according to the official GitHub documentation and it has Sqlite 3.37.2 as documented here: https://github.com/actions/runner-images/blob/main/images/ubuntu/Ubuntu2204-Readme.md#installed-apt-packages

all PDOs are versions 7.4.3.

The PDO version doesn't matter that much. This is the version of Sqlite used during the PHP compilation process that matters. Which is often the same version as the one installed by default. You can use the phpinfo() command to find out:

php -i | grep "SQLite Library"
SQLite Library => 3.40.1

• Ubuntu 20.04 has Sqlite 3.31.1
• Ubuntu 22.04 has Sqlite 3.37.2
• Ubuntu 24.04 has Sqlite 3.45.1

How can I control this? Where is that configured on my side?

Everything is in the GitHub Actions workflow:

kanboard/.github/workflows/unit_tests.yml

Lines 11 to 36 in 1702721

	Sqlite:
	runs-on: ubuntu-latest
	container:
	image: ghcr.io/kanboard/tests:latest
	credentials:
	username: ${{ github.repository_owner }}
	password: ${{ secrets.GITHUB_TOKEN }}
	steps:
	- uses: actions/checkout@v4
	- name: Validate composer.json and composer.lock
	run: composer validate
	- name: Install dependencies
	run: composer install --prefer-dist --no-progress --no-suggest
	- name: Unit tests with Sqlite
	run: ./vendor/bin/phpunit -c tests/units.sqlite.xml
	Sqlite-PHP8:
	runs-on: ubuntu-22.04
	steps:
	- uses: actions/checkout@v4
	- name: Validate composer.json and composer.lock
	run: composer validate
	- name: Install dependencies
	run: composer install --prefer-dist --no-progress --no-suggest
	- name: Unit tests with Sqlite
	run: ./vendor/bin/phpunit -c tests/units.sqlite.xml

[imfx77]

Oh, I should have guessed to look there 🙄
I looked at KB docs where the actual unit test config isn't mentioned and explained. And since I am still a newbie at GitHub incl at Actions I went looking for examples in some other repos of KB plugins, but just not at the KB repo itself 😆
Thank you for pointing me to the right place!
I will start from there 👍