====== 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 [[https://github.com/vufind-org/vufind/tree/dev/module/VuFind/sql|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 [[indexing:tracking_record_changes|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 [[configuration:shibboleth|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 [[configuration:authentication#persistent_login|persistent logins]]. ==== oai_resumption ==== This table supports VuFind's [[indexing:tracking_record_changes#oai-pmh_server_functionality|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. ==== 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. ==== shortlinks ==== When optional database-driven link shortening is turned on (see the url_shortener setting in [[configuration:files:config.ini|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 [[configuration:files: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 [[configuration:files: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 [[installation:migration_notes|migration notes]]). If you use PostgreSQL, you will currently have to apply migrations using scripts provided in the [[https://github.com/vufind-org/vufind/tree/dev/module/VuFind/sql/migrations/pgsql|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 [[configuration: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 ==== * 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 [[https://vufind.org/jira/browse/VUFIND-1139|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.