Review main Abandoned

85103[WIP][TASK] Introduce linter for SQL

Ayke Halder CR 0 V 0 Hard +412 −279 updated 07/04/24, 15:55

Commit message

This introduces a SQL linter:
`Build/Scripts/runTests.sh -s lintSql`

SQL files from Tests are excluded from linting.

To make inline comments in SQL code possible, the TYPO3 SqlReader is
extended to also remove SQL comments starting with ` -- ` from the end
of a line.

As SQL linter this uses the official Docker image of "sqlfluff".
The first sqlfluff config set for TYPO3 tries to resemble the current
state of the already implemented SQL code. This results in only small
changes to the SQL code to become compatible with the config set.

To deactivate sqlfluff rules on a line-by-line basis a sqlfluff
special inline comment can be added to the end of the line:
` -- noqa: RULE_TO_DEACTIVATE`

Especially rule "RF04 - Keywords should not be used as identifiers." is
deactivated for some occurences:

- The widely used identifier `uid` is ignored via sqlfluff config:
  `references.keywords` `ignore_words`
- All other identifiers are ignored via a case-by-case inline-comment to
  prevent any further use of the rejected identifier in new SQL code:
  ` -- noqa: RF04`

The rule "CP02 - Unquoted identifiers must be lower case." is resolved
by quoting identifiers that use upper case characters.

Review

  • Code review CR 0
  • Verified V 0

Files 25

Review log 89

  1. Ayke Halder uploaded patch set 1 07/04/24, 10:05

  2. core-ci voted on patch set 1 V +1 07/04/24, 10:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/60958

  3. Christian Kuhn commented on patch set 1 CR -1 07/04/24, 10:20

    hey. thanks for your efforts. i think there is little point in doing this: ext_tables.sql is a custom dialect, handled by our internal lexer, and it is not even bound to a specific dbms. quoting for instance happens on a different layer. these files are not supposed to be executed onto some dmbs directly.
  4. Ayke Halder commented on patch set 1 07/04/24, 10:28

    Reply

    This still enhances the code by checking some code-style rules, that in the end have an impact on the DB. E.g. rules like "do not use upper-case characters for column identifiers". (The quoting is not for quoting, but to mark identifiers breaking rules.)
  5. Christian Kuhn commented on patch set 1 07/04/24, 12:13

    Reply

    no. and we will also most likely not change such "offending" field names in the foreseeable future since that would create heavily breaking changes without serious benefit. there is no point in marking them as 'breaking rules', and we are NOT breaking rules since all DBMS support this, and ext_tables.sql is a custom dialect. what we will do: we will postulate clearly that only [A-Z,a-z,_] (need to re-check) are allowed for field and table names, since that will allow us to use latin1 for fields that contain these names, but that's a different todo for a different time when the parser is able to handle this.
  6. Ayke Halder commented on patch set 1 07/04/24, 12:56

    Reply

    For sure these already "offending" field names can not be changed because of the serious breaking changes this would introduce. Most of the field and table names only use underscore and lower-case `[_a-z]` right now, why should we allow/introduce upper-case `[A-Z]` in the future? This is more about freezing the status quo for already introduced field names. For field names newly introduced in the future we might set strict rules. ---- About the 'custom SQL dialect': As of now this is really close to mysql/mariadb SQL dialect - actually I'd say we are just using a subset of mysql/mariadb SQL dialect. (On postgres comparison: a special point where we are not compatible with postgres is comments starting with a hash `#` - does not matter as TYPO3s `SqlReader` is removing comments and is preparing the code db-engine-wise anyway.) ---- Just to have an example: We are using code-style rules for PHP so that the look of the code does not vary: ```php // as-is: if ($isDevelopment) { $foo = 'bar'; } // also working: if ( $isDevelopment ) { $foo = 'bar'; } if($isDevelopment){ $foo='bar'; } if ($isDevelopment) { $foo = 'bar'; } if ($isDevelopment) $foo = 'bar'; ``` Why should we not introduce code-style rules for TYPO3s 'custom SQL dialect'? As we are using a subset of mysql/mariadb SQL dialect, it looks do-able to introduce code-style rules here. ```sql /* E.g. how TYPO3s 'custom SQL dialect' should look (TBD) */ CREATE TABLE be_dashboards ( cruser_id int(11) unsigned DEFAULT 0 NOT NULL ); /* How it should not look */ CREATE TABLE be_dashboards ( cruser_id INT( 11 ) UNSIGNED DEFAULT 0 NOT NULL ) ; create table be_dashboards ( cruser_id int(11) unsigned default 0 not null ); Create Table be_dashboards ( cruser_id Int(11) Unsigned Default 0 Not NULL ); ```
  7. Christian Kuhn commented on patch set 1 07/04/24, 13:56

    Reply

    Ayke. Please stop this. We will not add a linter for these files for the mentioned reasons, or pollute the files with some "-- noqa: RF04" magic strings, a linter wants to have now for a format that we do not bow to. We'll also not create infrastructure for this, or run it in CI. ext_tables.sql are became significantly smaller, even in core extensions, and most ext devs will not bother much with them in the future anymore. There is no point in adding something from a third party that checks something that does not match. Please abandon.
  8. Ayke Halder abandoned the change 07/04/24, 15:55

  9. Ayke Halder commented on patch set 1 07/04/24, 15:55

    Reply

    Acknowledged