New version of sqlfluff unhappy with existing migrating scripts
I created a new branch locally (feature/add_geometry_data_units
), did nothing in it but pushed it upstream so as to have it in Git. The pipeline failed with the following messages:
$ sqlfluff lint --dialect postgres
== [/builds/dynamicexposure/globaldynamicexposure/database-gdetiles/migrations/00003_create-table-data-unit-tiles.sql] FAIL
L: 7 | P: 20 | L010 | Keywords must be consistently upper case.
== [/builds/dynamicexposure/globaldynamicexposure/database-gdetiles/migrations/00004_create-table-data-units.sql] FAIL
L: 4 | P: 20 | L010 | Keywords must be consistently upper case.
== [/builds/dynamicexposure/globaldynamicexposure/database-gdetiles/migrations/00005_create-table-exposure-entities-costs-assumptions.sql] FAIL
L: 5 | P: 20 | L010 | Keywords must be consistently upper case.
== [/builds/dynamicexposure/globaldynamicexposure/database-gdetiles/migrations/00006_create-table-exposure-entities-population-time-distribution.sql] FAIL
L: 5 | P: 20 | L010 | Keywords must be consistently upper case.
== [/builds/dynamicexposure/globaldynamicexposure/database-gdetiles/migrations/00007_create-table-data-units-buildings.sql] FAIL
L: 6 | P: 21 | L010 | Keywords must be consistently upper case.
L: 10 | P: 20 | L010 | Keywords must be consistently upper case.
I ran sqlfluff
locally and got no warnings. I checked my local version, it was 0.7.1. I updated it to 0.9.4, and got the same messages locally. I ran:
(venv) ......./database-gdetiles/migrations feature/add_geometry_data_units sqlfluff fix --dialect postgres 00003_create-table-data-unit-tiles.sql
==== finding fixable violations ====
== [00003_create-table-data-unit-tiles.sql] FAIL
L: 7 | P: 20 | L010 | Keywords must be consistently upper case.
==== fixing violations ====
1 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [00003_create-table-data-unit-tiles.sql] PASS
Done. Please check your files to confirm.
All Finished 📜 🎉!
The diff with the previous version is:
diff --git a/migrations/00003_create-table-data-unit-tiles.sql b/migrations/00003_create-table-data-unit-tiles.sql
index 59f3784..98dbc40 100644
--- a/migrations/00003_create-table-data-unit-tiles.sql
+++ b/migrations/00003_create-table-data-unit-tiles.sql
@@ -4,7 +4,7 @@ CREATE TABLE public.data_unit_tiles
(
quadkey CHAR(18),
aggregated_source_id SMALLINT,
- occupancy_case occupancycase,
+ occupancy_case OCCUPANCYCASE,
exposure_entity CHAR(3),
data_unit_id VARCHAR,
size_data_unit_tile_area FLOAT,
So... I understand that the new version of sqlfluff
does not like the fact that I named the enums with small letters instead of capitals (occupancycase
is an enum defined in this same script as CREATE TYPE occupancycase AS ENUM ('residential', 'commercial', 'industrial');
). At the same time, it only changes it when defining the data type of the column occupancy_case
but not in the first line, where CREATE TYPE occupancycase AS ENUM ('residential', 'commercial', 'industrial');
is defined... which is a bit odd...?
I understand one is not meant to change "past" migration scripts. A new script can erase and replace the enum class, but this would not stop the sqlfluff
issue from being raised and, moreover, if this database were already fully running, I don't think it would allow us to just "replace" the enum (from what I've seen so far).
From a quick search it seems that it is possible to tell sqlfluff
to ignore certain errors... Would this be the way to go? Alternatively, given that the database is not fully running yet (i.e. we can erase what is in it and re-fill it), we can erase the database, modify everything sqlfluff
wants to modify, and create it again from new. I am not sure of the best way to go.
Can you please give me a hand with this, @fd?