D7 SQLite testing - FieldSqlStorageTestCase

This one could be tricky.

https://git.drupalcode.org/project/drupal/-/blob/7.73/modules/field/modu...

    // Attempt to update the field in a way that would break the storage.
    $prior_field = $field;
    $field['settings']['max_length'] = -1;
    try {
      field_update_field($field);
      $this->fail(t('Update succeeded.'));
    }
    catch (Exception $e) {
      $this->pass(t('Update properly failed.'));
    }

This relies on an exception being thrown when field_sql_storage_field_storage_update_field() calls db_create_table():

https://git.drupalcode.org/project/drupal/-/blob/7.73/modules/field/modu...

The query is this:

CREATE TABLE {field_data_test_text} (
`entity_type` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to', 
`bundle` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance', 
`deleted` TINYINT NOT NULL DEFAULT 0 COMMENT 'A boolean indicating whether this data item has been deleted', 
`entity_id` INT unsigned NOT NULL COMMENT 'The entity id this data is attached to', 
`revision_id` INT unsigned NULL DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned', 
`language` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.', 
`delta` INT unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields', 
`test_text_value` VARCHAR(-1) NULL DEFAULT NULL, 
`test_text_format` VARCHAR(255) NULL DEFAULT NULL, 
PRIMARY KEY (`entity_type`, `entity_id`, `deleted`, `delta`, `language`), 
INDEX `entity_type` (`entity_type`), 
INDEX `bundle` (`bundle`), 
INDEX `deleted` (`deleted`), 
INDEX `entity_id` (`entity_id`), 
INDEX `revision_id` (`revision_id`), 
INDEX `language` (`language`), 
INDEX `test_text_format` (`test_text_format`)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COMMENT 'Data storage for field 6 (test_text)'

...and MySQL will throw the following exception through PDO:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) NULL DEFAULT NULL, 
`test_text_format` VARCHAR(255) NULL DEFAULT NULL, 
PRIM' at line 9

Whereas it looks like SQLite will happily create a table with that sort of broken schema:

$ drush sqlc
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> CREATE TABLE foo (bar VARCHAR(-1) NULL DEFAULT NULL);

sqlite> .schema foo
CREATE TABLE foo (bar VARCHAR(-1) NULL DEFAULT NULL);

sqlite> INSERT INTO foo VALUES ('baz');

sqlite> SELECT * FROM foo;
baz

So no exception is thrown, and test fails because the update succeeds.

I'll check if there's anything obvious we can do to make SQLite more strict about the schema, but perhaps this is one we'll have to skip for SQLite?

In D8/9 \Drupal\KernelTests\Core\Entity\FieldSqlStorageTest::testFieldUpdateFailure does this:

    // Attempt to update the field in a way that would break the storage. The
    // parenthesis suffix is needed because SQLite has *very* relaxed rules for
    // data types, so we actually need to provide an invalid SQL syntax in order
    // to break it.
    // @see https://www.sqlite.org/datatype3.html
    $prior_field_storage = $field_storage;
    $field_storage->setSetting('max_length', '-1)');
    try {
      $field_storage->save();
      $this->fail('Update succeeded.');
    }
    catch (\Exception $e) {
      // Expected exception; just continue testing.
    }

https://git.drupalcode.org/project/drupal/-/blob/9.0.x/core/tests/Drupal...

So a straight backport here should get that test passing in SQLite. Again, no point in having the bot test that just yet, but I've verified it locally.

FWIW with the bracket, SQLite throws this exception:

SQLSTATE[HY000]: General error: 1 near "NULL": syntax error

Added credit from, and linked to original issue #2463103: SQLite: Fix system\Tests\Entity\FieldSqlStorageTest

Thanks!