Differences in database schemas: schema of "fresh installation" vs. schema obtained after migration (based on DS CRIS 5)

[saschaszott]

The database schema (in Postgres) you'll get with a "fresh" DS CRIS 7 installation is not the same as the database schema you’ll get if you apply the DS CRIS 7 Flyway migration scripts on an existing DS CRIS 5.x installation.

The differences are summarized in the table below:

name of database object	type of database object	fresh DS CRIS 7 install	migration to DS CRIS 7
cris_unpaywall	table	table not present	CREATE TABLE …
cris_unpaywall_seq	sequence	sequence not present	CREATE SEQUENCE …
doi2item_seq	sequence	sequence not present	CREATE SEQUENCE …
eperson.netid	table column	netid character varying(64)	netid character varying(256)
subscription.community_id	table column	column not present	community_id integer
webapp.url	table column	url character varying	url character varying(1000)
cwf_claimtask_unique	UNIQUE constraint	missing	CONSTRAINT cwf_claimtask_unique UNIQUE (step_id, workflowitem_id, workflow_id, owner_id, action_id)
cwf_collectionrole_unique	UNIQUE constraint	missing	CONSTRAINT cwf_collectionrole_unique UNIQUE (role_id, collection_id, group_id)
cwf_in_progress_user_unique	UNIQUE constraint	missing	CONSTRAINT cwf_in_progress_user_unique UNIQUE (workflowitem_id, user_id)
cwf_workflowitem_item_id_key	UNIQUE constraint	missing	CONSTRAINT cwf_workflowitem_item_id_key UNIQUE (item_id)
cwf_workflowitemrole_unique	UNIQUE constraint	missing	CONSTRAINT cwf_workflowitemrole_unique UNIQUE (role_id, workflowitem_id, eperson_id)
cwf_claimtask_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_claimtask_eperson_fk_idx ON public.cwf_claimtask USING btree (owner_id)
cwf_claimtask_workflow_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_claimtask_workflow_eperson_fk_idx ON public.cwf_claimtask USING btree (workflowitem_id, owner_id)
cwf_claimtask_workflow_step_action_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_claimtask_workflow_step_action_eperson_fk_idx ON public.cwf_claimtask USING btree (workflowitem_id, step_id, action_id, owner_id)
cwf_collectionrole_coll_fk_idx	INDEX	missing	CREATE INDEX cwf_collectionrole_coll_fk_idx ON public.cwf_collectionrole USING btree (collection_id)
cwf_collectionrole_coll_role_fk_idx	INDEX	missing	CREATE INDEX cwf_collectionrole_coll_role_fk_idx ON public.cwf_collectionrole USING btree (collection_id, role_id)
cwf_in_progress_user_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_in_progress_user_eperson_fk_idx ON public.cwf_in_progress_user USING btree (user_id)
cwf_in_progress_user_workflow_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_in_progress_user_workflow_eperson_fk_idx ON public.cwf_in_progress_user USING btree (workflowitem_id, user_id)
cwf_pooltask_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_pooltask_eperson_fk_idx ON public.cwf_pooltask USING btree (eperson_id)
cwf_pooltask_workflow_eperson_fk_idx	INDEX	missing	CREATE INDEX cwf_pooltask_workflow_eperson_fk_idx ON public.cwf_pooltask USING btree (eperson_id, workflowitem_id)
cwf_workflowitem_coll_fk_idx	INDEX	missing	CREATE INDEX cwf_workflowitem_coll_fk_idx ON public.cwf_workflowitem USING btree (collection_id)
cwf_workflowitem_item_fk_idx	INDEX	missing	CREATE INDEX cwf_workflowitem_item_fk_idx ON public.cwf_workflowitem USING btree (item_id)

Please find attached the export of the database schema obtained by using pg_dump -s:

• database schema obtained after a clean DS CRIS 7 (2023.01) installation: schema-after-clean-install.txt
• database schema obtained after a successful migration from DS CRIS 5.11 to DS CRIS 7 (2023.01): schema-after-migration.txt