search

4Science / DSpace

This repository contains the 4Science optimized DSpace & DSpace-CRIS distribution.
https://wiki.lyrasis.org/display/DSPACECRIS/
BSD 3-Clause "New" or "Revised" License
42 stars 61 forks source link

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

Open saschaszott opened 1 year ago

saschaszott commented 1 year ago

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: