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
• comp_runs: @sanderegg --> ⚠ run_id needs to run Increment INT index
• comp_tasks: @sanderegg --> ⚠ used by legacy service careful with changes/deletions, task_id needs to run Increment INT index
• 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): needs to run Increment INT index
• 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, PrimaryGID needs to be synced with s4l.io prior to migration, run needs to run Increment INT index on UserID and GID
• 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');

Tables for increment

table	increment column	serial sequence name
api_keys	id	api_keys_id_seq
comp_runs	run_id	comp_runs_run_id_seq
comp_tasks	task_id	comp_tasks_task_id_seq
folders_v2	folder_id	folders_v2_folder_id_seq
group_classifiers	id	group_classifiers_id_seq
groups	gid	groups_gid_seq
payments_autorecharge	id	payments_autorecharge_id_seq
projects_nodes	project_node_id	projects_nodes_project_node_id_seq
tags	id	tags_id_seq
tokens	token_id	tokens_token_id_seq
user_to_projects	id	user_to_projects_id_seq
users	id	users_id_seq
wallets	wallet_id	wallets_wallet_id_seq
workspaces	workspace_id	workspaces_workspace_id_seq

NOTE! resource_tracker_service_runs user ID needs to be manually incremented (foreign key can not be introduced, because all anonymous users are always deleted)