Skip to content

Latest commit

 

History

History
205 lines (144 loc) · 9.78 KB

README.md

File metadata and controls

205 lines (144 loc) · 9.78 KB
Raw

aspace_preservica_db

A reporting database for collating and doing stuff with ArchivesSpace and Preservica metadata

Tables

The tables in the database and how they are generated

archival_object

This table is populated by running the archival_object_table.sql query against the ArchivesSpace database.

Column Description Type
id The archival object id int
repo_id The repository id int
root_record_id The parent resource id int
parent_id The parent archival object int
ref_id The ref id varchar(255)
component_id The component unique id varchar(255)
title The archival object title varchar(8704)
publish 1 published, 0 unpublished int
level Hierarchical level (i.e. file) varchar(255)
preservica_collection_id Found in the note field varchar(255)
extent Concatenated extent value varchar(255)
physical_containers Concatenated container data mediumtext
create_time ArchivesSpace creation time timestamp
m_time ArchivesSpace last modified timestamp

digital_object

This table is populated by running the digital_object_table.sql query against the ArchivesSpace database.

Column Description Type
id The digital object id int
digital_object_id The Preservica DelUnit id varchar(255)
archival_object_id The linked record id int
title The digital object title varchar(255)
publish 1 published, 0 unpublished int
has_content_link 1 has link. 0 no link int
create_time ArchivesSpace creation time timestamp
m_time ArchivesSpace creation time timestamp

digital_object_component

This table is populated by running the digital_object_component_table.sql query against the ArchivesSpacee database.

Column Description Type
id The digital object component id int
repo_id The repository id int
root_record_id The root digital object id int
parent_id The parent digital object id int
component_id The component unique id varchar(255)
title The component title varchar(8704)
publish 1 published, 0 unpublished int
create_time ArchivesSpace creation time timestamp
m_time ArchivesSpace creation time timestamp

restriction

This table is populated by running the restrictions_table.sql query against the ArchivesSpace database. This query can only be run on a local/test version of YUL ArchivesSpace, as it requires SQL common table expressions (CTEs) that only exist in MySQL 8+. The production version of YUL ArchivesSpace should be upgraded to MySQL 8 in early 2021.

Column Description Type
id The archival object id int
resource_note_text The resource-level note longtext
lvl The note level int
path The restriction text hierarchy longtext
type_path The restriction type hierarchy longtext
end_path The restriction date hierarchy longtext

hierarchy

This table is populated by running the hierarchies.sql query against the ArchivesSpace database. The hierarchies table is a view that only exists in a local/test version of YUL ArchivesSpace, as it requires SQL common table expressions (CTEs) that only exist in MySQL 8+. The production version of YUL ArchivesSpace should be upgraded to MySQL 8 in early 2021.

Column Description Type
id The archival object id int
root_record_id The resource id int
repo_id The repository id int
full_path The full hierarchy longtext
lvl The hierarchical level int

collection

This table is populated by extracting data from Preservica collection XML files. The collection IDs are stored in notes within ArchivesSpace archival object records, and are extracted by running the get_collection_ids.sql query against the ArchivesSpace database.

Column Description Type
id Preservica collection id varchar(255)
parent_collection_id Preservica parent collection varchar(255)
collection_code Preservica collection code varchar(255)
security_tag Preservica security tag varchar(255)
title Preservica collection title varchar(8704)
create_time Local database create time timestamp
m_time Local database last modified timestamp

deliverable_unit

This table is populated by extracting data from Preservica deliverable unit XML files. The deliverable unit IDs are stored in digital object records in ArchivesSpace, and are extracted by running the get_deliverable_unit_ids.sql query against the ArchivesSpace database.

Column Description Type
id The digital object id int
collection_id The Preservica collection id varchar(255)
parent_deliverable_unit The parent deliverable unit id varchar(255)
root_parent_del_unit The root deliverable unit id varchar(255)
deliverable_unit_level The deliverable unit level int
digital_surrogate yes or no varchar(255)
coverage_from Start date timestamp
coverage_to End date timestamp
security_tag Security tag varchar(255)
create_time Local database create time timestamp
m_time Local database last modified timestamp

manifestation

This table is populated by extracting data from Preservica deliverable unit XML files.

Column Description Type
id Preservica manifestation id varchar(255)
deliverable_unit_id Deliverable unit id varchar(255)
typeref The manifestation type varchar(255)
summary Preservica summary varchar(8704)
create_time Local database create time timestamp
m_time Local database last modified timestamp

digital_file

This table is populated by extracting data from Preservica digital file XML files. The list of digital files is derived from Preservica deliverable unit XML files.

Column Description Type
id The digital file id int
manifestation_id The manifestation id int
file_set_id The file set id int
filesize The file size in bytes int
filemoddate The file modification date timestamp
filename The name of the file varchar(255)
working_path The relative path to the file varchar(255)
format_puid The format unique id varchar(255)
format_name The format name mediumtext
mimetype The format mimetype varchar(255)
create_time Local database creation time timestamp
m_time Local database last modified timestamp

Extracting Data from Source Databases

How to extract data from ArchivesSpace and Preservica

ArchivesSpace

Data is extracted from ArchivesSpace via several SQL queries

Preservica

Data is extracted from Preservica by calling the Preservica API, as access to the Preservica database is restricted.

Initializing the aspace_preservica_db Database

How to create and populate the database using extracted data from ArchivesSpace and Preservica

Run process_xml_for_db.py

The first step in initializing the aspace_preservica_db database is running the process_xml_for_db.py script on extracted Preservica XML files.

Run db_manager.py

Once the CSV spreadsheets are prepared for ingest, it is time to run the db_manager.py script to create and populate the database.

Run SQL updates to fix field formatting

After the data is ingested, a little bit of clean-up is necessary.

Derive parent deliverable unit data

Updating the aspace_preservica_db Database

Checking for new data

Adding new data to the database

Design Notes and To-Dos

Reconciling missing data

Some data was missed during the initial load:

  • Collection data
  • Child deliverable unit data
  • Digital object component data
  • Restriction data

Consider re-designing manifestation table, since there are duplicate manifestation IDs; or at least sort out those issues.