What type of SQL code to add here. Are only the heatmap functions planned for now?
All the FUNCTIONS/TRIGGER etc. that are not tightly connected to the tables/data.
So it is for code, data stays in the migrations.
The SQL files are saved in backend/src/sql_files and should be named import_*.sql.
When it is being run?
First suggestion:
- creation of database
- running migrations
- the SQL imports we discuss here
- importing test data+maps
- run e2e tests
These deps should be tested and then documented and being implemented in Makefiles and CI (if it does not simply call make) the same way.
In what format should it be? Do we also need DROP statements? Otherwise just removing a function/file leaves the functions/types in the database.
It should be idempotent, so reimporting the SQL files many times should be no problem (no errors etc.).
So we need something like DROP FUNCTION IF EXISTS.
Migrations not needed for changes to SQL files
The SQL files exist specifically to avoid creating database migrations for the standalone SQL logic in these files.
Inheritance and ORDER BY caveats
The SQL imports may also contain ordering-sensitive logic. For example, when the plant attribute of "hydrology" were made inheritable in the scraper, the deepest_hydrology DESC ordering in import_heatmap.sql prevented inheritability from working correctly and had to be removed. However, similar ordering for deepest_soil_texture DESC did not cause the same issue, even after making soil texture inheritable as well.
When adding inheritable attributes or modifying aggregation/order logic, verification should be done that any ORDER BY clauses in the import SQL do not unintentionally override or block inheritance behavior.