This is an old revision of the document!
Table of Contents
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.
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.
oai_resumption
This table supports VuFind's OAI-PMH Server functionality.
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.
search
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.
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 4.0
- Added external_session table.
- Made session_id field of session table NOT NULL in PostgreSQL definition for consistency with MySQL.
Release 3.1
- Changed the collation of the tags table to utf8_bin to fix strange case sensitivity behavior (only affects MySQL; see https://vufind.org/jira/browse/VUFIND-1187 for details).
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.