Creating a custom controlled vocabulary via a database

Overview

When the custom controlled vocabulary is larger or may change often, the terms can be defined in the database.

Prerequisites

NOTE: This section describes setting up the database manually. You may want to explore work done by Georgia State University Library that imports a controlled vocabulary into QA tables using an import script they wrote. https://github.com/gsu-library/controlled_vocabulary_importer

Create the local authority tables in the database

ONE TIME ONLY: If you are creating multiple database backed custom controlled vocabularies, perform this step only once.

The qa:local:tables generator will create two tables and associated ActiveRecord models:

• Qa::LocalAuthority - holds the names of the database backed vocabularies, and
• Qa::LocalAuthorityEntry - holds the terms for the database backed vocabularies.

To create these tables, run the generator...

rails generate qa:local:tables
rake db:migrate

Note: If you are using MYSQL as your database use the MSQL database generator instead

rails generate qa:local:tables:mysql
rake db:migrate

Create the controlled vocabulary

FOR EACH VOCABULARY: This step is performed for each database backed vocubulary you want to create. The commands are run once to create the vocabulary and add terms in the database. You can run it from a script or through rails console.

This section shows an example that creates a controlled vocabulary named languages and adds some terms.

Create the vocabulary

Run the following once per vocabulary to create the new vocabulary...

languages_auth = Qa::LocalAuthority.find_or_create_by(name: 'languages')

NOTES:

• Substitute in place of 'languages' the name of your vocabulary. This will be used as the subauthority name for the local authority in the QA URL when requesting terms from this vocabulary.
• You will use the returned instance of Qa::LocalAuthority when adding new terms.
• If you execute this command twice, it will return the existing vocabulary which is useful for making changes to the set of terms at a later date.

Add terms to the vocabulary

Qa::LocalAuthorityEntry is an ActiveRecord model, so you have access to all the usual ActiveRecord methods for adding, updating, and deleting entries. The following is used to add new terms...

Qa::LocalAuthorityEntry.create(local_authority: languages_auth,
                               label: 'German',
                               uri: 'http://id.loc.gov/vocabulary/languages/ger')
Qa::LocalAuthorityEntry.create(local_authority: languages_auth,
                               label: 'German, Middle High (ca. 1050-1500)',
                               uri: 'http://id.loc.gov/vocabulary/languages/gmh')
Qa::LocalAuthorityEntry.create(local_authority: languages_auth,
                               label: 'German, Old High (ca. 750-1050)',
                               uri: 'http://id.loc.gov/vocabulary/languages/goh')
Qa::LocalAuthorityEntry.create(local_authority: languages_auth,
                               label: 'French',
                               uri: 'http://id.loc.gov/vocabulary/languages/fre')
Qa::LocalAuthorityEntry.create(local_authority: languages_auth,
                               label: 'Uighur',
                               uri: 'http://id.loc.gov/vocabulary/languages/uig')

NOTES:

• Substitute in place of languages_auth the instance variable you created using Qa::LocaslAuthority.find_or_create_by.
• label and uri are the data from the new term entry

Adding an index

Unfortunately, Rails doesn't have a mechanism for adding functional indexes to tables, so if you have a lot of rows, you'll want to add an index:

CREATE INDEX "index_qa_local_authority_entries_on_lower_label" ON
  "qa_local_authority_entries" (local_authority_id, lower(label))

Note: If you are using MYSQL as your database and used the MYSQL database generator we tried to execute the correct SQL to create the virtual fields and indexes for you

Register your authority in an initializer

Registration of database backed controlled vocabularies needs to happen everytime the server restarts. The following registers the languages authority. You can put the statement in any initializer. One option is to put it in config/initializers/qa.rb.

Qa::Authorities::Local.register_subauthority('languages', 'Qa::Authorities::Local::TableBasedAuthority')

NOTES:

• Substitute in place of 'languages' the name you used when creating Qa::LocalAuthority. This will be the namespace used to identify this vocabulary in the QA request URLs.

Note: If you are using MYSQL as your database and used the MYSQL database generator, register the MysqlTableBasedAuthority instead of the TableBasedAuthority

Accessing via QA

Authority:	local

Subauthorities:

• namespace_used_when_registering_the_local_subauthority (e.g. languages)

---

Example search queries for custom vocabulary languages:

/qa/search/local/languages?q=Ger

Result:

[
  {"id":"http://id.loc.gov/vocabulary/languages/ger","label":"German"},
  {"id":"http://id.loc.gov/vocabulary/languages/gmh","label":"German, Middle High (ca. 1050-1500)"},
  {"id":"http://id.loc.gov/vocabulary/languages/goh","label":"German, Old High (ca. 750-1050)"}
]

NOTES:

• Search searches terms, but not IDs/URIs. If you search for q=gmh, the result set will be empty.
• Search is NOT case-sensitive. The result set is the same when searching for q=ger.

---

Example term fetch request for custom vocabulary languages:

/qa/show/local/languages/http%3A%2F%2Fid%2Eloc%2Egov%2Fvocabulary%2Flanguages%2Ffre

Result:

{"id":"http://id.loc.gov/vocabulary/languages/fre","label":"French"}

NOTE:

• URL encoding is required since the ID is a URI; otherwise, it is treated as part of the path and doesn't match a route.

---

Example list all terms for custom vocabulary languages

/qa/terms/local/languages

Result:

[
  {"id":"http://id.loc.gov/vocabulary/languages/fre","label":"French"},
  {"id":"http://id.loc.gov/vocabulary/languages/uig","label":"Uighur"},
  {"id":"http://id.loc.gov/vocabulary/languages/ger","label":"German"},
  {"id":"http://id.loc.gov/vocabulary/languages/gmh","label":"German, Middle High (ca. 1050-1500)"},
  {"id":"http://id.loc.gov/vocabulary/languages/goh","label":"German, Old High (ca. 750-1050)"}
]

NOTES:

• Up to the first 1000 terms will be listed.
• Terms are not sorted, so they will appear in the order they were stored in the database.

---

Documentation

This is a locally defined controlled vocabulary and does not have any associated documentation.