CollectionSpace SQL

A place to record tips for working with CollectionSpace data directly in the PostgreSql database

Table of Contents

• Conventions
• Contents
• Other resources

Conventions

Client-identifying info has been removed from SQL examples and replaced with the following patterns:

{INSTANCEDOMAIN} replaces values like core.collectionspace.org or collectionspace.clientdomain.org in refname values or elsewhere

CAST(E'urn:cspace:{INSTANCEDOMAIN}:vocabularies:name(publishto):item:name(all)\'All\'' AS text) AS item

Contents

• Getting into the databases

• Common patterns/snippets - excluding deleted records, how to find data for a given record from the application UI, etc

• Functions - reusable bits

• Data projects - queries to support actual work

Other resources

• Nuxeo database documentation: Internal VCS Model —  VCS tables

• Some example JasperReports have embedded PGSQL queries to crib from:

  • standard reports

  • mcgill

  • ohc