SQLite driver should use COLLATE NOCASE instead of PDO custom collation (COLLATE NOCASE_UTF8)

I agree this is an issue, the db files are getting quite huge.

Apparently NOCASE_UTF8 is custom collation for PDO driver, but it is invalid in sqlite3.

Adding a custom collation is a feature of SQLite3 (https://www.sqlite.org/c3ref/create_collation.html) so it's perfectly valid to add our user-space NOCASE_UTF8 collation.

Since the SQLite database is generated and maintained by Drupal, you need to run the VACUUM command using Drupal's database layer in order for it to work properly.

I suspect the VACCUM command will fail. Any chance you could provide a drush command to accomplish this?
Please don't close the issue until its confirmed that VACUUM command will work when called via Drupal.

@amateescu the VACUUM command fails when called via drush, please see below

% cat command.txt 
VACUUM;

% `drush sql-connect` < command.txt 
Error: near line 1: no such collation sequence: NOCASE_UTF8

This still seems to be an issue as all of the drush sql:* commands simply execute shell commands using the 'driver' defined in the database config. They do not ever run the `Drupal\Core\Database\Driver\sqlite\Connection::open` command that is bootstrapping sqlite with all of the custom functions and the collation in question here?

While it seems the need to support custom functions and collations is very real, what is the best way to bring the bootstrapping of the driver out to the common management tools used maintain the state of our databases?

Is this a problem with the fundamental way the sql:* drush command work? If so, what can be done to bring continuity between core functionality of all the `Drupal\Core\Database\Driver` implementations through to the common management tools?

Obviously we can make some custom utils to get around this by using the native Connection:open, but that seems to miss the larger problem of a fundamental disconnect between the ubiquitous management tools, such as Drush, and Core.

Got this error today trying to manually edit a single field in phpstorm's database view, and trying to update the field in the query console:

[255] [UNKNOWN_ERROR] unknown error (no such collation sequence: NOCASE_UTF8)

So this problem really slows down a development process.

What are the solutions? How did you guys changed that NOCASE_UTF8 on an existing database?

Ok, I see that the core adds the collation for purpose:

    // Create a user-space case-insensitive collation with UTF-8 support.
    $pdo->sqliteCreateCollation('NOCASE_UTF8', ['Drupal\Component\Utility\Unicode', 'strcasecmp']);

So how to workaround this in other tools now?

Ok, spending a few hours I can't find any workaround to use phpstorm's DB tools with SQLite (except for viewing data in tables as is).

Have to use drush ev command like this: drush ev "\Drupal::database()->query('...')"

This way drush boots Drupal and executes a query in its context instead of bringing me directly to a DB console.

While this is not a general solution, i ported SQLite Vacuum to D8.
Help appreciated with #3283573: Add a drush sqlite:vacuum command.

Thanks @geek-merlin

```sh
drush php:eval "\Drupal\Core\Database\Database::getConnection('default', NULL)->query('vacuum;');"
```
https://git.drupalcode.org/project/sqlite_vacuum/-/blob/1.0.x/src/Comman...

Not being able to execute `vacuum` really creates a lot of problems.

At the same time the implementation fits in 1 line. Maybe this line should be added somewhere at the sqlite driver level to add such functionality?

Simply documenting #20 seems sufficient? 😊

(Many thanks, @apolitsin! 🙏)