About Features Downloads Getting Started Documentation Events Support GitHub

Love VuFind®? Consider becoming a financial supporter. Your support helps build a better VuFind®!

Site Tools


Warning: This page has not been updated in over over a year and may be outdated or deprecated.
development:architecture:database_schema

Database Schema

VuFind uses a relational database for a few functions, primarily to track user accounts and user-generated data.

Tables

The latest VuFind database definitions can be found here. This page provides some documentation and clarification on the various tables used by the system.

auth_hash

This is used to store authentication hashes used by the email authenticator introduced in VuFind 6.1.

change_tracker

See Tracking Record Changes for details on the purpose of this table, which helps VuFind keep track of the last time each record was indexed or deleted.

comments

This table stores user comments on records.

external_session

This table associates external session IDs with internal ones; used for Shibboleth single log-out.

feedback

This table can be optionally used to collect user feedback form responses.

login_token

This table is used to manage persistent logins.

oai_resumption

This table supports VuFind's OAI-PMH Server functionality.

ratings

This table stores user ratings of records when the “rating” setting is turned on in the [Social] section of config.ini (introduced in VuFind 9.0).

record

This table is only used when the optional record cache (introduced in VuFind 3.0) is turned on. In this situation, it stores copies of records to accelerate performance and/or provide a fallback when third-party data sources are offline.

resource

This table contains information about records that have been associated with user-generated data (comments, favorites, etc.)

resource_tags

This is a linking table between resource and tags.

This table is used both for storing temporary (associated with PHP session IDs) search history and long-term saved searches (associated with user accounts).

session

This table stores session data when VuFind is configured for database session storage. It is unused when the session is stored on disk, in memcache, etc.

When optional database-driven link shortening is turned on (see the url_shortener setting in config.ini), this table is used to store URLs.

tags

This table stores user-generated tags.

user

This table stores user information (unless “privacy mode” is enabled in config.ini – available in VuFind 3.0 and newer).

Storage of Credentials

One of the most important functions of the user table is the storage of user credentials.

VuFind is designed to work with two sets of credentials: the “primary” credentials, which allow the user to access user-specific functions of VuFind itself, and the “catalog” credentials, which allow VuFind to connect to a third-party ILS system to retrieve additional information and perform user-specific operations.

Depending on VuFind's configuration, different columns of the user table may be used for storing these credentials. The hashed/encrypted fields were not added until VuFind 2, with the “unsafe” fields retained for backward compatibility.

:!: It is strongly encouraged to only use the hashed/encrypted versions of fields whenever possible. If you run VuFind's standard web-based installer and “fix” the security issue, your configuration will be automatically adjusted to use the secure fields. The web-based upgrade script will also help adjust legacy insecure data.

Here are detailed descriptions of the relevant fields:

  • username - The “primary” username for accessing VuFind. This is used as a unique identifier for the user's account and will be filled in with some sort of unique value regardless of authentication configuration.
  • password - The “primary” password, unencrypted. This will only be populated when VuFind is configured for low security and is using its own database for authentication (as opposed to a third-party system). :!: low security field
  • pass_hash - The “primary” password, one-way hashed. This allows users to log in, but does not store their actual password in a way that can be easily retrieved. This will only be populated when VuFind is configured for high security and is using its own database for authentication (as opposed to a third-party system).
  • cat_username - The “catalog” username for identifying the user to a third-party ILS (sometimes a barcode number, but varies from ILS to ILS).
  • cat_password - The “catalog” password for authenticating the user to a third-party ILS, stored in plain text. :!: low security field
  • cat_pass_enc - The “catalog” password for authenticating the user to a third-party ILS, stored in an encrypted format that VuFind can reverse using a key found in config.ini (see “ils_encryption_key” in the “Authentication” section).

Note that, in security mode, VuFind is able to hash its own “primary” password because it only needs a hash to validate incoming credentials and does not need to retrieve the original value. However, it is necessary to store the “catalog” password in a reversible encrypted format because it needs to send that value to the ILS every time the user performs a transaction that communicates with the third-party system.

user_card

This table is only used when VuFind's “library card” functionality (introduced in release 2.5) is enabled. This allows storage of multiple ILS account credentials so a single user can access multiple ILS instances.

user_list

This table stores information on user-generated favorite lists (the lists themselves, not their content).

user_resource

This table associates the user, user_list and resource tables in order to make favorite lists function.

user_stats / user_stats_fields

These tables are only used when VuFind's statistics collection functionality is enabled and configured to use the database for storage.

Changelog

VuFind's database schema is sometimes adjusted between releases. If you use a MySQL database backend, these changes are automatically applied when you run VuFind's web-based upgrade tool (see migration notes). If you use PostgreSQL, you will currently have to apply migrations using scripts provided in the migrations directory of the code.

Note that this changelog only goes back as far as release 2.5. Not every release includes database changes.

Release 10.0

  • Added login_token table.

Release 9.0

  • Added feedback table.
  • Added ratings table.
  • Added an additional index to the search table to improve expiration performance.
  • Changed the saved column of the search table to use a more appropriate type for Boolean values.
  • Changed the public column of the user_list table to use a more appropriate type for Boolean values.
  • Removed the unused folder_id column from the search table.

Release 7.1

  • Changed constraint/default of resource_id column in resource_tags table to support user list tagging.

Release 7.0

  • Added hash column to shortlinks table.

Release 6.1

  • Added new auth_hash table.
  • The pending_email and user_provided_email fields were added to the user table to support email change functionality.
  • The last_language field was added to the user table to track user language preferences.
  • The notification_frequency, last_notification_sent and notification_base_url fields were added to the search table to support Email Alerts.
  • The id fields of search, session and external_session were switched to the bigint type to allow more growth.

Release 6.0

  • The shortlinks table was added (used for the optional URL shortening mechanism).
  • Added column email_verified to user table.

Release 5.1

  • The resource table now includes an extra_metadata field that can be populated with a JSON document containing extra record details (currently used for storing Summon bookmarks, to deal with changing IDs).
  • The field sizes for storing catalog passwords have been increased to accommodate longer strings.

Release 5.0

  • The comments table now allows anonymous comments; user_id may now be null, and foreign key behavior has changed.
  • The last_login and auth_method columns were added to the user table (to track last login date and method).
  • The data column of the session table has been changed to type mediumtext to allow room for larger session data objects.

Release 4.0

  • Added external_session table.
  • Added cat_id field to user table.

Release 3.1

Release 3.0

  • Increased size of cat_pass_enc/cat_password fields in user table.
  • Increased size of record_id, title and author columns in resource table.
  • Changed default source value in resource table from 'VuFind' to 'Solr' (see VUFIND-1139).
  • Introduced record table.
  • Changed type of created field in search table from date to datetime.
  • Added checksum field to search table (to aid in faster lookups).

Release 2.5

  • Introduced user_card table.
  • Increased size of username and email columns in user table.
development/architecture/database_schema.txt · Last modified: 2024/01/19 14:21 by demiankatz