We would like to get the best from cuba’s automatic table update (and create) scripts with using sql views.
Whenever the create view statement references table columns, tables, … that don’t exist any more, we would like to produce the following behaviour by placing create and drop view statements accordingly.
Behaviour:
The create view statement is part of the automatically executed create and update scripts
The create view statement is executed last, after table create / update scripts
The create view statement (and with it the whole create / update sequence) fails, if the view references no more existing tables / views.
Questions:
Where do we need to place the create view statement?
Do we need to place a drop view statement, and where?
Cheers
Oliver
Background:
We have a requirement to display summary information from “all over” the data model (deep relations). As our dev’s are good with native sql, not so good with jpql, and db native functions are involved, we lean towards using native sql.
Our data model is bound to change and evolve quite frequently, we therefore care a lot about automatic table update scripts from production db model v1 (with data) to production db model v2 (with migrated data).
Placing the native sql in a db view will get us immediate feedback if the native query is no longer compatible with the underlying table structure (Which is good, of course).
Hi,
The CUBA way of evolving and migrating the database schema is the following:
When you create the view for the first time, you should place its creation script to one of the init scripts and to the separate update script:
1.1) For init script - use 30.create-db.sql
1.2) Create separate update script: Main Data Store -> context menu -> New -> Database update script. Modify script name. Put view definition to this script. The date in the beginning of the script name defines update script execution order.
Note that when the new database is created (e.g. in the local development environment, or in the continious integration pipeline) - then only init scripts are executed. All update scripts existing at this moment are marked as executed, because it is assumed that init scripts represent the way to create the database conforming to the data model.
When existing database (staging, production) is updated to the next release - only new update scripts are executed (new means not yet registered in the SYS_DB_CHANGELOG table).
When you need to update the view definition, do the following:
2.1) change view creation script in the 30.create-db.sql
2.2) create new update script with the SQL script that re-creates the view definition (“create or replace view”, or “drop view; create view”).
If the updated view definition depends on other db schema updates, then the number (date in the file name) of the view-update script should be larger than other related update scripts, to ensure correct execution order.
When you need to drop the no longer used view definition:
3.1) delete view creation script from the 30.create-db.sql
3.2) create new update script with the “drop view” statement.
To ensure that the view definition is correct and works as expected together with other DB tables, I would suggest to create a middleware integration test. Such test should insert the data to the database by using other ordinary entities and DataManager, then read the data back by using the view, and finally check that fetched data is correct.
Such test needs to be run in the Continious Integration tool (e.g. Jenkins) automatically on a daily basis.
The test scenario in the continious integration pipeline is the following:
assemble the project
create fresh copy of the database “gradlew createDb”
run tests: “gradlew test”
As a side note, Studio 14 has improvements regarding displaying and creating the middleware integration tests in the CUBA project tree.
Thank you Alex for your replies especially for your input about middleware integration test.
This actually was my first thought before I thought about using db views and got “lazy”
db view pros:
I can use persistent entities in cuba - very similar to working with “normal” entities based on tables
The “create view” statement will fail if the structure of the underlying tables changed in the meantime - so I get 80% of the integration test “for free”
db view cons:
I think the update skripts will fail whenever they try to change a table included in the view due to postgresql dependency tracking. What I would like to do is:
Before running all update skripts: Drop the view
Run the update skripts
After all update skripts: Try to create the view on the modified tables.
Can this be “integrated” into the cuba upgrade procedure to make it work all the time? Can I for example just write gradle tasks for step’s 1 and 3, with incorporating the existing task 2?
I am sure they will not.
I have worked with project: with PostgreSQL and with views that reference other tables. And I have not met any update script fails.
The link you’ve presented - only covers DROP table:
PostgreSQL makes sure that you cannot drop objects that other objects still depend on
Altering the table won’t fail because a view references a table.