common_patterns.adoc

Common patterns/snippets

Table of Contents

• All records of a type
  • Person authorities
  • Objects
• Report number of each record type
• Remove deleted records from query
  • Active (non-deleted) person authorities
  • Active (non-deleted) objects
• Relationships
  • Refname of terms in a specific authority vocabulary that have child terms
• Data on media handling images
• Data for a known record from application UI
  • Via hierarchy table
  • Using id_from_csid function
  • Without the function
• Basic identifying info for objects

All records of a type

Person authorities

SELECT COUNT(persons.id)
FROM persons_common persons

Objects

SELECT COUNT(objs.id)
FROM collectionobjects_common objs

Report number of each record type

SELECT COUNT(*) AS REC_COUNT, REGEXP_REPLACE(TRIM(LEADING '/' FROM CC.URI), '\/.*', '') AS REC_TYPE
FROM PUBLIC.COLLECTIONSPACE_CORE CC
INNER JOIN MISC ON CC.ID = MISC.ID
AND MISC.LIFECYCLESTATE != 'deleted'
WHERE CC.URI not like '/contacts%'
	AND CC.URI not like '/relations%'
	AND CC.URI not like '/blobs%'
	AND CC.URI not like '/reports%'
	AND CC.URI not like '/batch%'
GROUP BY REC_TYPE
ORDER BY REC_COUNT DESC

Remove deleted records from query

Deleted records persist in the database with a lifecyclestate set to deleted in the informatively named misc table.

Active (non-deleted) person authorities

SELECT COUNT(persons.id)
FROM persons_common persons
INNER JOIN misc ON misc.id = persons.id AND misc.lifecyclestate != 'deleted'

Active (non-deleted) objects

SELECT COUNT(objs.id)
FROM collectionobjects_common objs
INNER JOIN misc ON misc.id = objs.id AND misc.lifecyclestate != 'deleted'

Relationships

Refname of terms in a specific authority vocabulary that have child terms

The example uses concept/associated.

select distinct objectrefname
from relations_common
where relationshiptype = 'hasBroader'
and subjectdocumenttype = 'conceptitem'
and subjectrefname like '%:name(concept):%'

See reports/objects_in_location_and_its_children for an example of how to pull a term and all its descendants.

Data on media handling images

In addition to the normal media and blob (subrecord) tables, the content table contains data on the original and derivative files.

The content.data column can be used to match a given file to an S3 object.

Data for a known record from application UI

The data for a given record is usually scattered across multiple tables in the database.

Also it’s a bit unclear at first how you can find a record in the database using the CSID from the end of a record’s URI in the application.

There are at least two methods. Entering via hierarchy table is probably the most practical, because you will have to use that table to combine record field data from different tables.

Via hierarchy table

Assuming URI for the record in the application UI is:

https://core.collectionspace.org/cspace/core/record/collectionobject/6aaf2595-2257-40f6-83b2

Using id_from_csid function

You will need to run the id_from_csid function creation query before using the following.

WITH dbid AS (
	select id_from_csid('6aaf2595-2257-40f6-83b2') as id
), thisrec AS (
	select * from hierarchy h
	inner join dbid d on h.id = d.id
), children AS (
	select * from hierarchy h
	inner join dbid d on h.parentid = d.id
), allrows AS (
	(select * from thisrec)
     union all
    (select * from children)
)

select * from allrows

Without the function

Essentially you just rewrite the function in the WITH dbid AS portion.

WITH dbid AS (
       select hierarchy.id
       from hierarchy
       where hierarchy.name = '6aaf2595-2257-40f6-83b2'
), thisrec AS (
	select * from hierarchy h
	inner join dbid d on h.id = d.id
), children AS (
	select * from hierarchy h
	inner join dbid d on h.parentid = d.id
), allrows AS (
	(select * from thisrec)
     union all
    (select * from children)
)

select * from allrows

Basic identifying info for objects

The following is a query fragment to append basic object id fields to collectionobjects_common rows identified by some logic. It assumes you have a CTE named objs which has (at least) columns:

• id (The database id of the collectionobjects_common row for each object)

• objectnumber

title as (
select
obj.id,
tg.title
from objs obj
left outer join hierarchy h on h.parentid = obj.id and h.primarytype = 'titleGroup' and h.pos = 0
left outer join titlegroup tg on h.id = tg.id
where tg.title is not null
),
objname as (
select
obj.id,
concat_ws(' - ', ong.objectnametype, deurn(ong.objectnamecontrolled), ong.objectname) as objectname
from objs obj
left outer join hierarchy h on h.parentid = obj.id and h.primarytype = 'objectNameGroup' and h.pos = 0
left outer join objectnamegroup ong on h.id = ong.id
),
prod as (
select
obj.id,
sd.datedisplaydate as productiondate,
deurn(opp.objectproductionperson) as productionperson
from objs obj
left outer join hierarchy hd on hd.parentid = obj.id and hd.pos = 0
	and hd.name = 'collectionobjects_common:objectProductionDateGroupList'
left outer join structureddategroup sd on sd.id = hd.id
left outer join hierarchy hp on hp.parentid = obj.id and hp.pos = 0
	and hp.primarytype = 'objectProductionPersonGroup'
left outer join objectproductionpersongroup opp on hp.id = opp.id
),
objdata as (
select
obj.objectnumber,
name.objectname,
ti.title,
prod.productiondate,
prod.productionperson,
obj.computedcurrentlocation
from objs obj
left outer join title ti on obj.id = ti.id
left outer join objname name on obj.id = name.id
left outer join prod on obj.id = prod.id
)
select * from objdata;