SQL Table Reference November 1, 2010

Introduction

This document contains information on the fields inside the Nucleus database tables.

The structure as presented here is for Nucleus v3.6.

Color codes in this document
Blogs
Plugins
Skins and templates
Administration
Extras
- nucleus_karma
- nucleus_ban

Color codes

This document uses some visual styles to indicate the type of columns. An overview is listed below:

primary	Primary keys
foreign	Foreign keys (click to go to the references value in other columns). Please note that MySQL does not enforce foreign key restrictions.
toremove	Tables/columns that will probably be removed in upcoming Nucleus version, in favor of plugins that provide the same functionality.
fulltext	There is a fulltext index on the column (used for searches).

Table nucleus_blog

Column Name	Type	Default	Description
bnumber	int(11)		Blog ID
bname	varchar(60)	''	Blog Name
bshortname	varchar(15)	''	Short Blog Name (as used in skinvars `<%blog%>` etc.)
bdesc	varchar(200)	NULL	Blog Description
bcomments	tinyint(2)	'1'	Enable comments (1=true, 0=false)
bmaxcomments	int(11)	'0'	Maximum amount of comments to show on `index` skintypes (inline comments). The default value (0) means that there is no limit. If you don't use inline comments, you shouldn't edit this.
btimeoffset	decimal(3,1)	'0.0'	Time offset to use. The items will be stored in the database using the correct time (server time+offset).
bnotify	varchar(128)	NULL	Notify e-mail address. On certain events (see `bnotifytype` for the exact definition of these events), a notification e-mail is sent out to this e-mail address.
burl	varchar(100)	NULL	Blog URL
bupdate	varchar(60)	NULL	Update file that needs to be altered each time a new item is posted to the weblog. Its an absolute path of a file on the server.
bdefskin	int(11)	'1'	Default skin to use when displaying this weblog
bpublic	tinyint(2)	'1'	Allow comments by non-registered members? (1=true/0=false)
bconvertbreaks	tinyint(2)	'1'	Convert line breaks to `<br />`? (1=true/0=false)
bdefcat	int(11)	NULL	Default category. This category will be selected by default when no other category is selected.
bnotifytype	int(11)	'15'	Which events to send notification e-mails on (see `bnotify` for the specification of the e-mail address). It's a combination of three values (multiply the values to get the notify type): 3: New comment 5: New karma vote 7: New item Default = New comments & New karma votes (3*5=15)
ballowpast	tinyint(2)	'0'	Allow backdating of items and editing the timestamp of an item (1=true/0=false)
bincludesearch	tinyint(2)	'0'	Always include in search queries, even if the query is on another blog (1=true/0=false)
breqemail	tinyint(2)	'0'	Require non-members to supply an email address when adding comments (1=true/0=false)
bfuturepost	tinyint(2)	'0'	Whether blog has future posts awaiting publishing (1=true/0=false)

Table nucleus_category

Column Name	Type	Default	Description
catid	int(11)		Category ID
cblog	int(11)	'0'	Blog to which the category belongs
cname	varchar(40)	NULL	Category Name
cdesc	varchar(200)	NULL	Category Description

Table nucleus_item

Column Name	Type	Default	Description
inumber	int(11)		Item ID
ititle	varchar(160)	NULL	Title
ibody	text		Body text
imore	text		Extended text
iblog	int(11)	'0'	Blog to which the item belongs to
iauthor	int(11)	'0'	Member that is the author of the item
itime	datetime	'0000-00-00 00:00:00'	Item time (this is the corrected time, with offset already applies)
iclosed	tinyint(2)	'0'	Is item closed? (1=true/0=false). When an item is closed, it's no longer possible to add new comments or cast 'karma votes'
idraft	tinyint(2)	'0'	Is the item a draft version? Draft versions only show up in the admin area.
ikarmapos	int(11)	'0'	Total amount of positive karma votes casted
ikarmaneg	int(11)	'0'	Total amount of negative karma votes casted
icat	int(11)	NULL	Category to which the item belongs
iposted	tinyint(2)	'1'	Is the item posted? 0 means it is awaiting publication on a future date.

Table nucleus_comment

Column Name	Type	Default	Description
cnumber	int(11)		Comment ID
cbody	text		Comment text
cuser	varchar(40)	NULL	Anonymous user name (only used when comment was placed by a non-member. For members, see `cmember`)
cmail	varchar(100)	NULL	URL or, pre-3.3, E-mail address (only used when comment was placed by a non-member. For members, see `cmember`)
cemail	varchar(100)	NULL	E-mail address (only used when comment was placed by a non-member. For members, see `cmember`)
cmember	int(11)	NULL	ID of site member that placed the commend (0 for comments by non-members)
citem	int(11)	'0'	Item ID to which the comment is attached
ctime	datetime	'0000-00-00 00:00:00'	Time of comment
chost	varchar(60)	NULL	Hostname from where the comment was placed
cip	varchar(15)	''	IP address from where the comment was placed
cblog	int(11)	'0'	Blog to which the comment belongs (this is redundant information)

Table nucleus_member

Column Name	Type	Default	Description
mnumber	int(11)		Member ID
mname	varchar(32)	''	Display name (the one used to login)
mrealname	varchar(60)	NULL	Full name
mpassword	varchar(40)	''	password (md5 hash)
memail	varchar(60)	NULL	E-mail address. This should always be a valid address.
murl	varchar(100)	NULL	URL of members site
mnotes	varchar(100)	NULL	Extra notes (members can fill these out themselves)
madmin	tinyint(2)	'0'	Is super-admin? (1=true/0=false; super-admins have all rights; there must be at least one super-admin in the system)
mcanlogin	tinyint(2)	'1'	Can logon to admin area? (1=true/0=false)
mcookiekey	varchar(40)	NULL	A copy of the key that is stored in the users cookie. This key is used to log on. When a member logs on, a random cookiekey is generated. One copy goes into the database, another one goes into a cookie on the users computer. (together with the username).
deflang	varchar(20)	''	Language file to use for this member. When empty, uses the default site language.

Table nucleus_team

For each team member of a blog, there is a row in this table.

Column Name	Type	Default	Description
tmember	int(11)	'0'	Member ID
tblog	int(11)	'0'	Blog of which member is on team
tadmin	tinyint(2)	'0'	Is blog admin? (0=false/1=true; each blog must have at least one admin)

Table nucleus_plugin

Column Name	Type	Default	Description
pid	int(11)		Plugin ID
pfile	varchar(40)	''	Filename (e.g. `NP_CommentControl`) of plugin. This must be a file in the plugins directory (.php extension).
porder	int(11)	'0'	Order in which the plugins are called, and in which they are displayed on the plugins page. A lower order number places the plugin earlier in the list.

Table nucleus_plugin_event

A cache that remembers which plugins are registered to which events. Thsi information is stored to avoid having to load all plugins on each requests just to find out which events they want to have. More info on plugins events

Column Name	Type	Default	Description
pid	int(11)	'0'	Plugin ID
event	varchar(40)	NULL	Name of event

Table nucleus_plugin_option

Values for the plugin options. See the nucleus_plugin_option_desc table for the definition of the options itself.

Column Name	Type	Default	Description
oid	int(11)		Identification of the option (See the nucleus_plugin_option_desc table)
ovalue	TEXT	''	Value of the option
ocontextid	int(11)	'0'	Semantics depend on the option context type, global context: not used blog context: blog id category context: category id member context: member id

Table nucleus_plugin_option_desc

For each option created by a plugin, a row is present in this table.

Column Name	Type	Default	Description
oid	int(11)		Option ID. Used from nucleus_plugin_option
opid	int(11)	'0'	Plugin to which the option belongs
oname	varchar(20)	''	Name of the option
ocontext	varchar(20)	''	Context of the option (`global`, `blog`, `category`, `member`)
odesc	varchar(255)	NULL	Option description
otype	varchar(20)	NULL	option type. See plugin documentation.
odef	text		Default value for options
oextra	text		Extra data needed for some option types (e.g. `select` option type)

Table nucleus_skin

Column Name	Type	Default	Description
sdesc	int(11)	'0'	Reference to the skin description
stype	varchar(20)	''	Skinpart type `index` `item` `archive` `archivelist` `search` `error` `member` `imagepopup`
scontent	text		Contents of the skinpart

Table nucleus_skin_desc

Column Name	Type	Default	Description
sdnumber	int(11)		Skin ID
sdname	varchar(20)	''	Name of skin
sddesc	varchar(200)	NULL	Skin description
sdtype	varchar(40)	'text/html'	mimetype of skin
sdincmode	varchar(10)	'normal'	Include mode `normal` `skindir`
sdincpref	varchar(50)	''	Prefix to use when including files

Table nucleus_template

Column Name	Type	Default	Description
tdesc	int(11)	'0'	Reference to template description info
tpartname	varchar(64)	''	Name of template part ARCHIVELIST_FOOTER ARCHIVELIST_HEADER ARCHIVELIST_LISTITEM BLOGLIST_FOOTER BLOGLIST_HEADER BLOGLIST_LISTITEM CATLIST_FOOTER CATLIST_HEADER CATLIST_LISTITEM COMMENTS_AUTH COMMENTS_BODY COMMENTS_CONTINUED COMMENTS_FOOTER COMMENTS_HEADER COMMENTS_MANY COMMENTS_NONE COMMENTS_ONE COMMENTS_TOOMUCH DATE_FOOTER DATE_HEADER EDITLINK FORMAT_DATE FORMAT_TIME IMAGE_CODE ITEM_FOOTER ITEM_HEADER ITEM LOCALE MEDIA_CODE MORELINK NEW POPUP_CODE SEARCH_HIGHLIGHT SEARCH_NOTHINGFOUND other template fields as added by plugins using TemplateExtraFields event
tcontent	text		Contents of templatepart

Table nucleus_template_desc

Column Name	Type	Default	Description
tdnumber	int(11)		Template ID
tdname	varchar(64)	''	Name of template
tddesc	varchar(200)	NULL	Template description

Table nucleus_actionlog

[[general purpose description]]

Column Name	Type		Description
timestamp	datetime	'0000-00-00 00:00:00'	Time of action
message	varchar(255)	''	Action message

Table nucleus_config

Nucleus stores some global options in the nucleus_config table. They can be accessed at any time using $CONF['OptionName'] (the values are read on each request and stored in a global array named $CONF)

Column Name	Type	Default	Description
name	varchar(20)	''	Option name
value	varchar(128)	NULL	Option value

Available Options

An overview of available options is given below:

Options for yes/no options are represented using 1/0 (1=yes/true, 0=no/false)

Name	Default Value	Description
DefaultBlog	1	Default weblog. This is the blog that will be used when no other blog has been specified in the request.
AdminEmail		E-mail address of site administrator
IndexURL		URL of website. Should end with a slash.
Language	english	Language file to use by default. A file languagename.php must exist in the languages directory.
SessionCookie	0	Use session cookie instead of cookies with a lifetime of one month?
AllowMemberCreate	0	Allow visitors to create their own account?
AllowMemberMail	1	Allow members to send messages to each other through the member mail forms (e-mail addresses remain hidden)
SiteName		Name of the website
AdminURL		URL of admin area. Should end with a slash.
NewMemberCanLogon	1	Can newly registered members login right away? If not, the administrator will have to change their 'can login' option first
DisableSite	0	Is the website disabled? If so, only the administrator can access it. All other visitors are redirected to `DisableSiteURL`.
DisableSiteURL		An URL to redirect to when the site is disabled.
LastVisit	0	Save 'Last Visit' cookies
MediaURL		URL of media folder. Should end with a slash.
AllowedTypes	jpg, jpeg, gif, mpg, mpeg, avi, mov, mp3, swf, png	Filetypes that can be uploaded
AllowLoginEdit	0	Allow members to edit their login name and password?
AllowUpload	1	Allow file uploads?
DisableJsTools	2	Style of the javascript toolbar: 0: full featured (IE) 1: toolbar disabled 2: simpler (Gecko)
CookiePath	/	Path to set cookie on
CookiePrefix		String to prefix cookie names with. This is useful when multiple Nucleus installs are on the same domain, as it prevents login sessions to interfere with each other.
CookieDomain		Domain to set cookie on
CookieSecure	0	Secure cookie (https)
MediaPrefix	1	If true, the uploaded files get the current date in their filename.
MaxUploadSize	1048576	Max. size of uploaded files (in bytes)
NonmemberMail	0	Allow non-members to send e-mail messages to site members?
PluginURL		URL of plugin folder. Should end with a slash.
ProtectMemNames	1	When this option is enabled, non-logged in members cannot add comments using the same name as registered members. The reason to do this would be to avoid guest impersonating members.
BaseSkin	1	The option tells Nucleus which skin to fall back to when no such decision can be automatically made. This happens when skin parts are empty, when no blog or skin is implicitly/explicitly selected.
SkinsURL		URL of skins folder. Should end with a slash.
ActionURL		URL of `action.php` script.
URLMode	normal	either `normal` or `pathinfo`
DatabaseVersion	250	Last Nucleus version for which the database structure has been updated (introduced in Nucleus v2.5)
DebugVars	0	Whether unresolved variables should be displayed in skins (introduced in Nucleus v3.4)
DefaultListSize	10	Set the size of lists in the admin area (introduced in Nucleus v3.4)

Table nucleus_tickets

Nucleus uses the nucleus_tickets to prevent against certain security issues. In particular: each action on the admin area that affects the settings or database contents, requires a ticket. These tickets are generated when requesting an admin area page and passed along with the form. Tickets are destroyed one hour after their creation.

Column Name	Type	Description
ticket	varchar(40)	Unique ticket, valid for one particular member. A typical ticket looks like this: `65303a785423b4d53c7b3e6579766f26`
member	int(11)	Member for which this ticket is valid.
ctime	datetime	Time of ticket creation. A ticket is valid no longer than one hour.

Table nucleus_activation

When a new member registers, Nucleus doesn't allow that member to log in before his account is activated. This activation is done by sending out an activation link to the members email address. The nucleus_activation table keeps track of the activations that are in progress.

Column Name	Type	Description
vkey	varchar(40)	Activation key. This key needs to be passed to the member activation code. A typical key looks like this: `41cf637d4fbeeff954b4ca70b8bde9dd`
vmember	int(11)	Member which needs to be activated.
vtime	datetime	Time at which activation key was generated. Each activation key is valid no longer than 2 days.
vtype	varchar(15)	Type of activation. `forgot`: member forgot his password. `register`: new member registration. `addresschange`: member changed his e-mail address.
vextra	varchar(128)	Extra information. For an `addresschange` type of activation, this contains `oldemailaddress/x` with x either 0 or 1 and refering to the previous value of the `mcanlogin` field in the member table. (untill fully re-activated, a user cannot login)

Table nucleus_karma

This table will most likely be removed in future Nucleus version, in favor of plugins with the same functionality.

This table keeps track of IP addresses that have already voted for an item. This way, each IP address can cast only one vote.

Column Name	Type	Default	Description
itemid	int(11)	'0'	Item ID
ip	char(15)	''	IP address of voter

Table nucleus_ban