Update documentation and clean repository of deprecated files

[Piiit]

From our email:

Hi all,
I am happy to announce that the new version of the VKG is now online. It uses your triggers and logical replication.
Great job, thank you all!

In addition we have added schema versioning for your database assets via flyway (see sql/ folder).

Feedback about https://github.com/noi-techpark/odh-vkg

We copied some files from the data/replication/slave folder to sql/ and did a version tagging, such that it gets executed step by step. Each schema change and further triggers should now be added as consecutive update SQL scripts. See https://flywaydb.org for further details.

I also updated a part of the README.md and suggest that you could have a look at all READMEs and check if they are still up-to-date with the new logical replication approach and flyway (and sql/ folder). Eventually remove deprecated documentation or update it. Maybe we could combine all those READMEs into a single one inside the root folder. What do you think about that? Maybe just keeping single README to describe why a folder exists?

In addition, I had some difficulties to understand which files are still relevant, and which could be removed from the repository. It would be nice, if you could delete those files that are no longer needed.

Could you please also add “What do we need to do on schema changes?” and have a look at your scripts to check what is still relevant for the logical replication approach, and finally check the completeness of the current flyway migrations . Do I have inserted all triggers, or do I need to run the src/create_views_and_triggers_from_db.py script to generate another script.

Please note, that on the master side we have a publication for all tables, therefore we also need those on the slave, or would it be better to hardcode a list of relevant tables on both sides?

[Piiit]

@bcogrel FYI

[bcogrel]

Hi, we experienced that the triggers as versioned by Flyway (file V3_triggers.sql) work fine with Postgres 12.1 but not 12.4.

For 12.4 (and perhaps 12.2 and 12.3, not tested though) the public schema in the trigger definitions when performing the INSERT:

For instance,

INSERT INTO v_accommodationsopen

needs to be replaced by

INSERT INTO public.v_accommodationsopen

However, modifying the SQL file goes against the policy enforced by Flyway. @Piiit , shall we discuss this issue after your return?