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.

Yale-DMAC/aspace_preservica_db

aspace_preservica_db

Tables

archival_object

digital_object

digital_object_component

restriction

hierarchy

collection

deliverable_unit

manifestation

digital_file