You are here

Home »

Drupal Database Dictionary

This is a work (very much) in progress. The following is the MySQL DB layout for the 4.7 HEAD..

If you notice any errors, please post a message to let me know. Here goes..

access

Holds a list of access rules added via admin/access/rules

Field Type Null Default Comment
aid tinyint(10) Unique sequence ID for each access rule.
mask varchar(255) A string to match the rule against.
type varchar(255) Denotes what field to match the rule against. Example values include user/email/host which match against the user name/email address/host address respectively.
status tinyint(2) 0 Indicates whether this is an "allow"[1] or "deny"[0] rule.

accesslog

Holds a list of page accesses and associated user details.

Field Type Null Default Comment
aid int(10) Unique sequence ID for each access log.
sid varchar(32) Session ID of the user.
title varchar(255) YES Title of the page accessed.
path varchar(255) YES Internal path of the page being accessed.
url varchar(255) YES The URL of the referrer.
hostname varchar(128) YES IP address of the user.
uid int(10) unsigned YES 0 User ID of the user who accessed the page.
timer int(10) unsigned 0 The time taken to generate the page.
timestamp int(11) unsigned 0 UNIX timestamp indicating the date and time of the page access.

aggregator_category

Holds the aggregator category list.

Field Type Null Default Comment
cid int(10) Unique sequence ID for each aggregator category.
title varchar(255) Title of the category.
description longtext Description of the category.
block tinyint(2) 0 Indicates the number of news items to display in an aggregator category block.

aggregator_category_feed

Relates aggregator feeds with aggregator categories.

Field Type Null Default Comment
fid int(10) 0 Feed ID of the aggregator feed.
cid int(10) 0 Category ID of the aggregator category belonging to the associated feed.

aggregator_category_item

Asociates aggregator categories with feed items.

Field Type Null Default Comment
iid int(10) 0 Item ID of the feed item.
cid int(10) 0 Category ID of the aggregator category that an item belongs to.

aggregator_feed

Holds a list of feeds created.

Field Type Null Default Comment
fid int(10) Unique sequence ID for each feed.
title varchar(255) Title of the feed.
url varchar(255) Fully qualified URL of the feed.
refresh int(10) 0 Indicates the frequency of feed updates (via cron) in seconds.
checked int(10) 0 Timestamp indicating time of last update.
link varchar(255) Channel link as reported by the feed.
description longtext Description as reported by the feed.
image longtext Image associated with feed.
etag varchar(255) A token used to check if a feed has changed since the last update.
modified int(10) 0 Timestamp indicates the time of last modification as reported by the feed.
block tinyint(2) 0 Indicates the number of news items to display in a feed block.

aggregator_item

Holds the items of a feed.

Field Type Null Default Comment
iid int(10) Unique sequence ID for each item.
fid int(10) 0 Feed ID of the aggregator feed.
title varchar(255) Title of the item.
link varchar(255) Link to the item as reported by the feed.
author varchar(255) Author of the item.
description longtext Content of the item.
timestamp int(11) YES Timestamp associated with an item, usually denoting time of creation.

authmap

Field Type Null Default Comment
aid int(10) unsigned
uid int(10) 0
authname varchar(128)
module varchar(128)

blocks

Holds block configuration information.

Field Type Null Default Comment
module varchar(64) Name of the module that owns the associated block.
delta varchar(32) 0 Used to identify different blocks associated with the same module.
theme varchar(255) Theme associated with a block - allows for theme specific customization.
status tinyint(2) 0 Indicates if the block is enabled.
weight tinyint(1) 0 Controls display order of a block.
region varchar(64) left Indicates the position of a block in a page.
custom tinyint(2) 0 Specifies if the block visibility can be controlled by the user.
throttle tinyint(1) 0 Indicates if a block should be throttled when under heavy traffic.
visibility tinyint(1) 0 Indicates page specific visibility settings.
pages text Depending on the visibility field, holds a list of eligible or ineligible pages or PHP code in PHP mode.

book

Holds page relationships for book nodes.

Field Type Null Default Comment
vid int(10) unsigned 0 Version ID of the book node.
nid int(10) unsigned 0 Node ID of the book node.
parent int(10) 0 ID of the parent book page. 0 indicates a top-level node.
weight tinyint(3) 0 Controls display order of a book node.

boxes

Field Type Null Default Comment
bid tinyint(4)
title varchar(64)
body longtext YES
info varchar(128)
format int(4) 0

cache

The table resposible for Drupal's caching mechanism.

Field Type Null Default Comment
cid varchar(255) Unique cache ID generated based on the type of data that is being cached. For node caches, this is usually a combination of filter:filter format:md5(data).
data longblob YES The cached data. This can be a simple serialized string or a BLOB.
expire int(11) 0 Timestamp that indicates when the cache expires.
created int(11) 0 Timestamp that indicates when the cache was created.
headers text YES N/A

client

Field Type Null Default Comment
cid int(10) unsigned
link varchar(255)
name varchar(128)
mail varchar(128)
slogan longtext
mission longtext
users int(10) 0
nodes int(10) 0
version varchar(35)
created int(11) 0
changed int(11) 0

client_system

Field Type Null Default Comment
cid int(10) 0
name varchar(255)
type varchar(255)

comments

Holds all comments.

Field Type Null Default Comment
cid int(10) Unique sequence ID for each comment.
pid int(10) 0 ID of the parent comment. 0 indicates a direct reply to the node.
nid int(10) 0 Node ID of the parent node the comment belongs to.
uid int(10) 0 User ID of the author of the comment.
subject varchar(64) Subject field of the comment.
comment longtext The actual text of the comment.
hostname varchar(128) The IP address of the author of the comment.
timestamp int(11) 0 Timestamp that indicates when the comment was created.
score mediumint(9) 0 N/A
status tinyint(3) unsigned 0 N/A
format int(4) 0 Input format of the comment.
thread varchar(255) Dotted representation of the comment's position in a threaded view. For e.g. 2.3.1 (reading backwards) indicates that the comment is the 1st reply of the 3rd reply to the second comment for the node in question.
users longtext YES N/A
name varchar(60) YES Comment author's name.
mail varchar(64) YES Comment author's email address
homepage varchar(255) YES Comment author's home page.

contact

The table resposible for Drupal's site-wide contact page.

Field Type Null Default Comment
cid int(10) unsigned Unique sequence ID for each contact category.
category varchar(255) The category name of the contact category.
recipients longtext A delimited string of recipient email addresses.
reply longtext The text for the auto-reply message.
weight tinyint(3) 0 Controls display order of contact categories.
selected tinyint(1) 0 Indicates if the category is the default selected category on the site-wide contact page.

file_revisions

Field Type Null Default Comment
fid int(10) unsigned 0
vid int(10) unsigned 0
description varchar(255)
list tinyint(1) unsigned 0

files

Field Type Null Default Comment
fid int(10) unsigned 0
nid int(10) unsigned 0
filename varchar(255)
filepath varchar(255)
filemime varchar(255)
filesize int(10) unsigned 0

filter_formats

Holds the various input formats.

Field Type Null Default Comment
format int(4) Unique sequence ID for each input format.
name varchar(255) Name of input format.
roles varchar(255) The roles associated with the input format.
cache tinyint(2) 0 Indicates whether the nodes that have been filtered through the input format in question can be cached.

filters

Holds the various input formats.

Field Type Null Default Comment
format int(4) 0 Format ID of the input format.
module varchar(64) N/A
delta tinyint(2) 0 N/A
weight tinyint(2) 0 Controls the order in which filters are applied.

flood

Tracks events for flood control. For e.g. the contact module uses this table to prevent abuse of the contact form.

Field Type Null Default Comment
event varchar(64) Type of event.
hostname varchar(128) Hostname (IP address) of the user.
timestamp int(11) 0 Indicates the time of occurence.

forum

Similar to the term_node table. Associates (fora) category terms with (forum) nodes.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the node.
vid int(10) unsigned 0 Version ID of the forum node.
tid int(10) unsigned 0 Term ID of the term.

history

Keeps track of the last viewed timestamp for each node for every user i.e. handles the 'new' functionality.

Field Type Null Default Comment
uid int(10) 0 User ID of the user being tracked.
nid int(10) 0 Node ID of the node.
timestamp int(11) 0 UNIX timestamp indicating time that the associated user last viewed the associated node.

locales_meta

Holds a list of installed locales.

Field Type Null Default Comment
locale varchar(12) Locale code : Commonly this is an ISO 639 language code with an optional country code for regional variants. Examples include 'en', 'en-US' and 'zh-cn'.
name varchar(64) Name of the locale/language.
enabled int(2) 0 Indicates if the locale is currently enabled.
isdefault int(2) 0 Indicates if the locale in question is the default locale for the site.
plurals int(1) 0 Indicates if (word and numeric) plurals are handled in a special manner for the associated locale.
formula varchar(128) Formula to obtain the correct plural form.

locales_source

Holds a list of installed locales.

Field Type Null Default Comment
lid int(11) Sequence ID that uniquely identifies a localization string.
location varchar(255) Location of instances of the string in the code.
source blob Source string (usually in English).

locales_target

Holds a list of installed locales.

Field Type Null Default Comment
lid int(11) 0 Localization string ID of the string being translated.
translation blob Translated string.
locale varchar(12) Locale Code of the translated string.
plid int(11) 0 N/A
plural int(1) 0 N/A

menu

Holds all the menu items for a site, including standard menus displayed in blocks to menu tabs displayed in pages.

Field Type Null Default Comment
mid int(10) unsigned 0 Sequence ID that uniquely identifies a menu.
pid int(10) unsigned 0 mid of the parent menu.
path varchar(255) The path that the menu points to.
title varchar(255) The title of the menu.
description varchar(255) The text displayed when hovering over a menu item.
weight tinyint(4) 0 Controls the display order of a menu.
type int(2) unsigned 0 Bit-mask indicating the type of menu - a menu tab, standard menu item etc.

node

A node is the basic Drupal type. Blogs, Stories, Pages, Forum posts etc. are all types of nodes.

Field Type Null Default Comment
nid int(10) unsigned Sequence ID that uniquely identifies a node.
vid int(10) unsigned 0 Node Version ID.
type varchar(32) Type of node - blog/story etc.
title varchar(128) Title of the node.
uid int(10) 0 User ID of the author of this node.
status int(4) 1 Indicates whether the node has been published or not.
created int(11) 0 Node creation timestamp.
changed int(11) 0 Node modification timestamp.
comment int(2) 0 Denotes if comments are permitted or not.
promote int(2) 0 Indicates if the node is promoted to the front page.
moderate int(2) 0 Indicates if the node is in the moderation queue.
sticky int(2) 0 Indicates if the node is to be a sticky at the top of the page.

node_access

Dictates access control for nodes.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the node.
gid int(10) unsigned 0 Group ID e.g. a role or taxonomy ID.
realm varchar(255) Indicates the domain in which the access control is applicable e.g. taxonomy.
grant_view tinyint(1) unsigned 0 Indicates if the node can be viewed.
grant_update tinyint(1) unsigned 0 Indicates if the node can be updated.
grant_delete tinyint(1) unsigned 0 Indicates if the node can be deleted.

node_comment_statistics

Holds comment statistics for nodes to facilitate easy retrieval.

Field Type Null Default Comment
nid int(10) unsigned Node ID of the node.
last_comment_timestamp int(11) 0 Time stamp indicating the time and date of the last comment.
last_comment_name varchar(60) YES Name of the user who made the last comment. Only populated if anonymous comments are permitted.
last_comment_uid int(10) 0 User ID of the last user to make a comment in a node.
comment_count int(10) unsigned 0 Indicates the total number of comments in a node.

node_counter

Holds statistics related to page accesses. Used to populate the "popular content" block.

Field Type Null Default Comment
nid int(11) 0 Node ID of the node.
totalcount bigint(20) unsigned 0 Total number of times accessed.
daycount mediumint(8) unsigned 0 Total number of times accessed today.
timestamp int(11) unsigned 0 Timestamp indicating last time of access.

node_revisions

Holds different revisions of nodes.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the node.
vid int(10) unsigned 0 Node Version ID.
uid int(10) 0 User ID of the author of this node.
title varchar(128) Title of the node.
body longtext Body of the node.
teaser longtext Node teaser/preview.
log longtext Stores the optional log message for each revision.
timestamp int(11) 0 Node modification timestamp.
format int(4) 0 Input format associated with the node.

permission

Associates roles with permissions.

Field Type Null Default Comment
rid int(10) unsigned 0 Role ID of a role.
perm longtext YES Permissions to associate with a role.
tid int(10) unsigned 0 N/A - possibly associates a role with a taxonomy term.

poll

Holds poll configuration data.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the poll node.
runtime int(10) 0 Running time of the poll in seconds.
active int(2) unsigned 0 Indicates if the poll is still active.

poll_choices

Holds the choice data for each poll.

Field Type Null Default Comment
chid int(10) unsigned Unique sequence ID for each choice.
nid int(10) unsigned 0 Node ID of the poll node.
chtext varchar(128) Choice text.
chvotes int(6) 0 Total number of votes attributed to a choice.
chorder int(2) 0 Display order of a choice.

poll_votes

Holds data on who have voted on a poll.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the poll node.
uid int(10) unsigned 0 User ID of the user who placed the vote.
hostname varchar(128) Hostname (IP) of the user who placed the vote. This field is only used for anonymous voters.

profile_fields

Holds custom form fields for the profile page.

Field Type Null Default Comment
fid int(10) Unique sequence ID for each profile field.
title varchar(255) YES Field title.
name varchar(128) YES A unique name identifying the field in the profile form.
explanation text YES An explanation for the profile field.
category varchar(255) YES Associates fields with sections on the profile page.
page varchar(255) YES Page title for fields configured to be shown on member listings.
type varchar(128) YES Type of form element.
weight tinyint(1) 0 Controls the display order of a field.
required tinyint(1) 0 Indicates if a field is required to be filled in.
register tinyint(1) 0 Indicates if the field should be added to the user registration form.
visibility tinyint(1) 0 Indicates the field's visibility settings. Fields can be set to be visible only to authenticated users etc.
options text YES Choice options for fields of type SELECT.

profile_values

Associates users and field values with corresponding profile fields.

Field Type Null Default Comment
fid int(10) unsigned YES 0 Profile field ID of the profile field.
uid int(10) unsigned YES 0 User ID of the user.
value text YES Value of the field.

role

Holds the different user roles.

Field Type Null Default Comment
rid int(10) unsigned Unique sequence ID for each role.
name varchar(32) Name of the role.

search_dataset

Field Type Null Default Comment
sid int(10) unsigned 0
type varchar(16) YES
data longtext

search_index

Field Type Null Default Comment
word varchar(50)
sid int(10) unsigned 0
type varchar(16) YES
fromsid int(10) unsigned 0
fromtype varchar(16) YES
score float YES

search_total

Field Type Null Default Comment
word varchar(50)
count float YES

sequences

Holds the sequence IDs for index fields in a number of tables.

Field Type Null Default Comment
name varchar(255) Holds the field name as {table}_{field}.
id int(10) unsigned 0 Current sequence ID of the field.

sessions

Takes care of user session management.

Field Type Null Default Comment
uid int(10) unsigned 0 User ID of the user identified by the session.
sid varchar(32) Session ID identifying the user's session.
hostname varchar(128) IP address of the user.
timestamp int(11) 0 Timestamp indicating last known activity of the user.
cache int(11) 0 Timestamp indicating time of last cache refresh. This is used to implement the "Minimum cache lifetime" feature.
session longtext YES Serialized session variables stored in the database.

system

Holds module/engine settings.

Field Type Null Default Comment
filename varchar(255) Filename of the module relative to the modules directory.
name varchar(255) Name of the module.
type varchar(255) Indicates whether the file is a module or theme engine.
description varchar(255) Holds information about the module.
status int(2) 0 Indicates if the module is enabled or disabled.
throttle tinyint(1) 0 Indicates if throttling is enabled or disabled for the module.
bootstrap int(2) 0 Indicates if a module is loaded during the Drupal bootstrap process.
schema_version smallint(3) -1 Indicates the update version of the module. This is used during the update process.
weight int(2) 0 Indicates order of precedence during function calls.

term_data

Holds all the taxonomy/category terms.

Field Type Null Default Comment
tid int(10) unsigned Unique sequence ID for each term.
vid int(10) unsigned 0 Vocabulary ID associating each term to its parent vocabulary.
name varchar(255) Term name.
description longtext YES Term description.
weight tinyint(4) 0 Indicates display order. Lighter terms have precedence.

term_hierarchy

Holds the hierarchical struture of category/taxonomy terms.

Field Type Null Default Comment
tid int(10) unsigned 0 Term ID of the term.
parent int(10) unsigned 0 Term ID of the term's parent.

term_node

Associates category terms with nodes. Not all node types reside in this table. Some types like the forum node are stored in their own tables.

Field Type Null Default Comment
nid int(10) unsigned 0 Node ID of the node.
tid int(10) unsigned 0 Term ID of the term.

term_relation

Holds terms that are related to other terms.

Field Type Null Default Comment
tid1 int(10) unsigned 0 Term ID of the primary term.
tid2 int(10) unsigned 0 Term ID of the related term.

term_synonym

Holds synonymous names for terms.

Field Type Null Default Comment
tid int(10) unsigned 0 Term ID of the term.
name varchar(255) Holds a synonym for the term.

url_alias

Associates user defined URL aliases with internal paths.

Field Type Null Default Comment
pid int(10) unsigned Unique sequence ID that identifies the alias.
src varchar(128) Internal Path.
dst varchar(128) URL Alias.

users

Holds user details.

Field Type Null Default Comment
uid int(10) unsigned 0 Unique sequence ID for each user.
name varchar(60) Username.
pass varchar(32) Password.
mail varchar(64) YES Email Address.
mode tinyint(1) 0 User's preferred comment mode. Default - Expanded thread view.
sort tinyint(1) YES 0 User's preferred sort order. Default - Newest first.
threshold tinyint(1) YES 0 N/A
theme varchar(255) User's selected theme.
signature varchar(255) User's signature
created int(11) 0 Indicates the time of user registration.
access int(11) 0 Indicates the time of last activity.
login int(11) 0 Indicates the time of a user's most recent login.
status tinyint(4) 0 Indicates if the account is enabled or disabled.
timezone varchar(8) YES User's timezone setting - an offset in seconds relative to GMT.
language varchar(12) User's language of choice.
picture varchar(255) User's uploaded picture/avatar.
init varchar(64) YES Initial values. Currently stores the email address that the user signed up with.
data longtext YES Serialized array of user specific variables.

users_roles

Associates users with user roles.

Field Type Null Default Comment
uid int(10) unsigned 0 User ID of the user.
rid int(10) unsigned 0 Role ID of the role to associate with the user.

variable

Holds variables and their values.

Field Type Null Default Comment
name varchar(48) Variable name.
value longtext Value of variable (serialized).

vocabulary

Holds vocabularies for the taxonomy/category system.

Field Type Null Default Comment
vid int(10) unsigned Unique sequence ID for each vocabulary.
name varchar(255) Name of vocabulary.
description longtext YES Description of vocabulary.
help varchar(255) Help text for vocabulary.
relations tinyint(3) unsigned 0 Indicates if related terms are permitted for terms within a vocabulary.
hierarchy tinyint(3) unsigned 0 Indicates if hierarchical term structures are permitted within a vocabulary.
multiple tinyint(3) unsigned 0 Indicates if a node can be associated with multiple terms in a vocabulary.
required tinyint(3) unsigned 0 Indicates if each node associated with a vocabulary should be associated with atleast one vocabulary term.
tags tinyint(3) unsigned 0 Indicates if free tagging is allowed for a vocabulary.
module varchar(255) Indicates which module this vocabulary belongs to.
weight tinyint(4) 0 Indicates display order. Lighter terms have precedence.

vocabulary_node_types

Associates vocabularies with nodes.

Field Type Null Default Comment
vid int(10) unsigned 0 Vocabulary ID of the vocabulary.
type varchar(32) Type of node.

watchdog

Holds system messages.

Field Type Null Default Comment
wid int(5) Unique sequence ID for each watchdog entry.
uid int(10) 0 User ID of the user generating the message.
type varchar(16) Type of message - content/php etc.
message longtext Message text.
severity tinyint(3) unsigned 0 Severity of the message - warnings/errors etc.
link varchar(255) Link visible in the operations column on log pages.
location varchar(128) Address of the page generating the message.
referer varchar(128) Referring URL.
hostname varchar(128) IP address of user.
timestamp int(11) 0 UNIX timestamp denoting date and time of message.

Tags: