Problem with automated tests, SQLite and Drupal 8

[finnlewis]

Hi there!

We're working on a Drupal distribution (LocalGov Drupal) and have had some conversations on https://www.drupal.org/project/dbal/issues/3186378

We're still having problems with our automated tests, which is being discussed on this pull request:
localgovdrupal/localgov_subsites#8

I'm just cross posting here in case you have any suggestions about how best to resolve this or if it is even possible.

This comment is probably the numb of the issue: localgovdrupal/localgov_subsites#8 (comment)

Any ideas?

Many thanks,

Finn

[hkirsman]

One of the issues we have is that PHPUnit tests yell

 Drupal\Core\Entity\EntityStorageException: Table name must match the regex /^[a-zA-Z]\w{1,64}$/
   │ 
   │ /app/web/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php:846
   │ /app/web/core/lib/Drupal/Core/Entity/EntityBase.php:395

Interestingly it only fails on sqlite and not with mysql. When checking further then it seems for some reason Drupal tests create tables with dot in sqlite and in mysql without it.

Here's the code that adds dots: https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Driver%21sqlite%21Connection.php/8.0.x

// Add a ., so queries become prefix.table, which is proper syntax for
        // querying an attached database.
        $prefix .= '.';

So in sqlite the table name is:
test67168191.nested_set_field_toc_parent_node

and in mysql it is:
test67168191nested_set_field_toc_parent_node

The obvious reason why our tests fail is this https://github.com/previousnext/nested-set/blob/master/src/Storage/BaseDbalStorage.php#L15

const VALID_TABLE_REGEX = '/^[a-zA-Z]\w{1,64}$/';

But loosening the regex does not help because it can't find the table:

const VALID_TABLE_REGEX = '/^[a-zA-Z][a-zA-Z0-9_.]{1,64}$/';

I understand it's also a requirement of DBAL to not allow dots in table names.

As initial solution for this would be to just change the separator in to something else eg empty string
https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Driver%21sqlite%21Connection.php/8.0.x

$prefix .= '.';
-->
$prefix .= '';

For me it fixes the tests. I'm not using sqlite anywhere else plus it's 1-liner fix I can live with.