move of osparc.io: make db tables ready for merging

[mrnicegyu11]

The plan is to insert / upsert the DB of osparc into the DB of sim4life. To do this, all osparc postgres tables will be audited and made ready for this procedure. Some tables might be empty (by design), some tables might not need to be moved and can be dropped, and some tables might need adjustments.

In order to avoid collisions in the DB, such as with tables that have monotonically increasing primary keys starting from 1, we have identified two ways to make the DB-tables of osparc.io "ready":

1. Add a large integer (needs to be determined individually) to all primary keys of osparc.io OR sim4life.io, to avoid collisions. [Note: This doesnt always work, e.g. in the users DB mail adresses are presumably expected to be unique even though they are not enforced to be unique in postgres?
2. Add the product as a second primary key and thereby make combinations of user-id & product etc. unique.

Likely, both ways would go via a PR with an alembic migration

• alembic_version: @mrnicegyu11
• api_keys: @giancarloromeo
• cluster_to_groups: ♻️DB maintenance: drop clusters and cluster_to_groups db tables (🗃️) osparc-simcore#7373
• clusters: ♻️DB maintenance: drop clusters and cluster_to_groups db tables (🗃️) osparc-simcore#7373
• comp_pipeline: @sanderegg --> ⚠ used by legacy service careful with changes/deletions, needs foreign key relation to project UUID
• comp_runs: @sanderegg --> run_id needs to be changed from integer to other or removed
• comp_tasks: @sanderegg --> ⚠ used by legacy service careful with changes/deletions, needs foreign keys to project/node UUID
  • MD NOTE: all these comp_* tables can be emptied before migration?
• confirmations: @pcrespov
• file_meta_data: @sanderegg --> ok. project/node ids are not foreign keys
• folders_v2: @matusdrobuliak66 (INT ID will be incremented)
• group_classifiers: @pcrespov
• groups: @pcrespov /@sanderegg /@matusdrobuliak66 --> ⚠ GID is integer and is in the interface (INT ID will be incremented)
• groups_extra_properties: @pcrespov /@sanderegg /@matusdrobuliak66 --> ok. foreign key to gid
• jinja2_templates: @pcrespov
• licensed_item_to_resource: @matusdrobuliak66 - empty
• licensed_items: @matusdrobuliak66 - empty
• licensed_resources: @matusdrobuliak66 - empty
• payments_autorecharge: @matusdrobuliak66 - empty
• payments_methods: @matusdrobuliak66 - empty
• payments_transactions: @matusdrobuliak66 - empty
• products: @pcrespov
• products_prices: @matusdrobuliak66 - currently empty
• products_to_templates: @pcrespov - currently empty
• project_to_groups: @pcrespov /@sanderegg /@matusdrobuliak66
• projects: @pcrespov
• projects_comments: @pcrespov
• projects_metadata: @pcrespov
• projects_networks: @pcrespov
• projects_node_to_pricing_unit: @matusdrobuliak66
• projects_nodes: @pcrespov
• projects_tags: @pcrespov
• projects_to_folders: @pcrespov
• projects_to_products: @pcrespov
• projects_to_wallet: @matusdrobuliak66
• resource_tracker_credit_transactions: @matusdrobuliak66 - empty
• resource_tracker_licensed_items_checkouts: @matusdrobuliak66 - empty
• resource_tracker_licensed_items_purchases: @matusdrobuliak66 - empty
• resource_tracker_pricing_plan_to_service: @matusdrobuliak66 - empty
• resource_tracker_pricing_plans: @matusdrobuliak66 - empty
• resource_tracker_pricing_unit_costs: @matusdrobuliak66 - empty
• resource_tracker_pricing_units: @matusdrobuliak66 - empty
• resource_tracker_service_runs: @matusdrobuliak66 - (? probably we can delete)
• scicrunch_resources: @pcrespov
• services_access_rights: @sanderegg --> use foreign key to gid
• services_compatibility: @pcrespov
• services_consume_filetypes: @pcrespov
• services_meta_data: @sanderegg --> use foreign key to gid
• services_specifications: @sanderegg --> use foreign key to gid
• services_tags: @pcrespov
• services_vendor_secrets: @pcrespov
• tags: @pcrespov
• tags_access_rights: @pcrespov
• tokens: @sanderegg --> ⚠ token_id is an int --> UUID, uses foreign key to user_id
• user_preferences_frontend: no auto increment primary key (multiple field one), no changes required
• user_preferences_user_service: no auto increment primary key (multiple field one), no changes required
• user_to_groups: @pcrespov @sanderegg @matusdrobuliak66 --> foreign key to uid/gid
• user_to_projects: @pcrespov @sanderegg - Can likely be discarded. DELETED
• users: @pcrespov @sanderegg @matusdrobuliak66 ⚠ USERID, foreign key to gid --> this will need something
• users_pre_registration_details: @pcrespov
• wallet_to_groups: @matusdrobuliak66
• wallets: @matusdrobuliak66 (INT ID will be incremented)
• workspaces: @matusdrobuliak66 (INT ID will be incremented)
• workspaces_access_rights: @matusdrobuliak66

[sanderegg]

• wallet_id is an INTeger and can likely not be changed

[matusdrobuliak66]

🗃️ Increment INT index

UPDATE folders_v2 SET folder_id = folder_id + 2000000, parent_folder_id = parent_folder_id + 2000000;
select pg_get_serial_sequence('folders_v2', 'folder_id');
SELECT setval('folders_v2_folder_id_seq', (SELECT MAX(folder_id) FROM folders_v2));

To test the value generation after it was moved:

select nextval('folders_v2_folder_id_seq');

[matusdrobuliak66]

Additional Simcore Tasks Before Migration

• Support users being part of multiple products
  • Fix logout behavior: when a user logs out from one product, it should not log them out from all products
• Add foreign keys to some tables (e.g., comp*)
• Remove group 1 (EVERYONE) used for sharing
  • This might cause issues with anonymous users. Should we bind them to a specific product?
  • We want to avoid the "everyone" group so that users in Sim4Life don’t see templates from oSPARC.