Database structure

The following information is outdated. It is currently based on the situation as of ModernBB 3.4.

The following is a complete list of all Luna database tables and their structures. Primary key fields are underlined, foreign keys are in italics.

Type conventions

Artificial primary keys are of type int(10).
Boolean values are of type tinyint(1), with the value 1 representing true and 0 representing false.

List of tables

bans
categories
censoring
config
forums
forum_perms
groups
online
posts
ranks
reports
search_cache
search_matches
search_words
topic_subscriptions
forum_subscriptions
topics
users

Please note: The below descriptions are based off the database structure when using MySQL(i). In other supported RDBMS the field type and default values may vary slightly.

bans

The bans table is used to hold details of all current bans. It is important to note that bans work on usernames, IP addresses or email addresses – not specific user accounts.

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`username`	`varchar(200)`	`NULL`	The username this ban applies to, or `NULL` for none.
`ip`	`varchar(255)`	`NULL`	The IP address(es) this ban applies to, or `NULL` for none.
`email`	`varchar(80)`	`NULL`	The email address this ban applies to, or `NULL` for none.
`message`	`varchar(255)`	`NULL`	A message to be displayed to the banned user.
`expire`	`int(10)`	`NULL`	A UNIX timestamp representing the time the ban should expire.
`ban_creator`	`int(10)`	`0`	The ID of the user who created the ban.

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`cat_name`	`varchar(80)`	`“New Category”`	The name of the category.
`disp_position`	`int(10)`	`0`	The position of this category in relation to the others.

censoring

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`search_for`	`varchar(60)`	`””`	The term to search for.
`replace_with`	`varchar(60)`	`””`	The term to replace with.

config

The config table holds key, value pairs for all the main configuration options. For performance reasons Luna caches these values and will only refresh the cache when they are updated via the admin panel.

For more information about the actual contents of the config table, see the $luna_config global variable.

Field	Type	Default	Description
`conf_name`	`varchar(255)`	`””`	The name of the configuration variable. General configuration options start with the prefix o_ and general permission options start with the prefix p_.
`conf_value`	`text`	`NULL`	The value of the configuration variable.

forums

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`forum_name`	`varchar(80)`	`“New forum”`	The name of the forum.
`forum_desc`	`text`	`NULL`	A description of the forum (may contain HTML).
`redirect_url`	`varchar(100)`	`NULL`	The URL to redirect users to upon clicking the forum link, or `NULL` for a normal forum.
`moderators`	`text`	`NULL`	A serialized associative PHP array with moderator names ⇒ user IDs.
`num_topics`	`mediumint(8)`	`0`	The number of topics the forum contains.
`num_posts`	`mediumint(8)`	`0`	The number of posts the forum contains.
`last_post`	`int(10)`	`NULL`	A UNIX timestamp representing the time the last post was made in the forum.
`last_post_id`	`int(10)`	`NULL`	The ID of the last post that was made in the forum.
`last_poster`	`varchar(200)`	`NULL`	The username (or guest name) of the user that made the last post in the forum.
`sort_by`	`tinyint(1)`	`0`	How the posts in the forum should be sorted. 0 = By last post time, 1 = By topic start time.
`disp_position`	`int(10)`	`0`	The position of this forum in relation to the others.
`cat_id`	`int(10)`	`0`	The ID of the category in which this forum resides.

forum_perms

Field	Type	Default	Description
`group_id`	`int(10)`	`0`	The ID of the group this permission set applies to.
`forum_id`	`int(10)`	`0`	The ID of the forum this permission set applies to.
`read_forum`	`tinyint(1)`	`1`	Allow members of the group to view this forum?
`post_replies`	`tinyint(1)`	`1`	Allow members of the group to post replies in this forum?
`post_topics`	`tinyint(1)`	`1`	Allow members of the group to start new topics in this forum?

groups

All fields in the groups table are prefixed with g_. This is to allow them to be easily combined with all the fields from the users table without any conflicting field names.

Field	Type	Default	Description
`g_id`	`int(10)`		The auto-incrementing primary key for this table.
`g_title`	`varchar(50)`	`””`	The name of this group.
`g_user_title`	`varchar(50)`	`NULL`	The user title to be used for members of this group.
`g_moderator`	`tinyint(1)`	`0`	Does this group have moderator privileges?
`g_mod_edit_users`	`tinyint(1)`	`0`	If g_moderator, can members of this group edit users profiles?
`g_mod_rename_users`	`tinyint(1)`	`0`	If g_moderator, can members of this group rename users?
`g_mod_change_passwords`	`tinyint(1)`	`0`	If g_moderator, can members of this group change users passwords?
`g_mod_ban_users`	`tinyint(1)`	`0`	If g_moderator, can members of this group ban users?
`g_read_board`	`tinyint(1)`	`1`	Can members of this group view boards? If this is 0 the group basically has no access to the forums.
`g_view_users`	`tinyint(1)`	`1`	Can members of this group view the user list?
`g_post_replies`	`tinyint(1)`	`1`	Can members of this group post replies?
`g_post_topics`	`tinyint(1)`	`1`	Can members of this group start new topics?
`g_edit_posts`	`tinyint(1)`	`1`	Can members of this group edit their own posts?
`g_delete_posts`	`tinyint(1)`	`1`	Can members of this group delete their own posts?
`g_delete_topics`	`tinyint(1)`	`1`	Can members of this group delete their own topics (including all replies)?
`g_set_title`	`tinyint(1)`	`1`	Can members of this group set their own user title?
`g_search`	`tinyint(1)`	`1`	Can members of this group use the search features?
`g_search_users`	`tinyint(1)`	`1`	Can members of this group search the user list?
`g_send_email`	`tinyint(1)`	`1`	Can members of this group send emails to users?
`g_post_flood`	`smallint(6)`	`30`	How many seconds members of this group must wait between making posts.
`g_search_flood`	`smallint(6)`	`30`	How many seconds members of this group must wait between making search requests.
`g_email_flood`	`smallint(6)`	`60`	How many seconds members of this group must wait between sending emails.

online

Field	Type	Default	Description
`user_id`	`int(10)`	`1`	The ID of the user (or 1 if the user is a guest).
`ident`	`varchar(200)`	`””`	Identification string for the user (Username for logged in users, IP address for guests).
`logged`	`int(10)`	`0`	A UNIX timestamp representing the time of the users last activity.
`idle`	`tinyint(1)`	`0`	If the user is idle or not (i.e. their last visit was more than o_timeout_online seconds ago, but less than o_timeout_visit seconds ago – see the $luna_config global variable).
`last_post`	`int(10)`	`NULL`	A UNIX timestamp representing the time the user last made a post.
`last_search`	`int(10)`	`NULL`	A UNIX timestamp representing the time the user last performed a search.

posts

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`poster`	`varchar(200)`	`””`	The username of the user who created this post.
`poster_id`	`int(10)`	`1`	The ID of the user who created this post.
`poster_ip`	`varchar(39)`	`NULL`	The IP address of the user who created this post.
`poster_email`	`varchar(80)`	`NULL`	If the post was created by a guest, their email address. If it was created by a logged in user, then `NULL`.
`message`	`mediumtext`	`NULL`	The contents of the post.
`hide_smilies`	`tinyint(1)`	`0`	Should smilies be hidden in this post?
`posted`	`int(10)`	`0`	A UNIX timestamp representing the time this post was created.
`edited`	`int(10)`	`NULL`	A UNIX timestamp representing the time this post was last edited, or `NULL` if it hasn’t been edited.
`edited_by`	`varchar(200)`	`NULL`	The username of the user who last edited this post, or `NULL` if it hasn’t been edited.
`topic_id`	`int(10)`	`0`	The ID of the parent topic for this post.

ranks

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`rank`	`varchar(50)`	`””`	The rank title.
`min_posts`	`mediumint(8)`	`0`	The number of posts a user must attain in order to reach the rank.

reports

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`post_id`	`int(10)`	`0`	The ID of the reported post.
`topic_id`	`int(10)`	`0`	The ID of topic in which the reported post is contained.
`forum_id`	`int(10)`	`0`	The ID of the forum in which the reported post is contained.
`reported_by`	`int(10)`	`0`	The ID of the user who created the report.
`created`	`int(10)`	`0`	A UNIX timestamp representing the time this report was created.
`message`	`text`	`NULL`	The report message entered by the user.
`zapped`	`int(10)`	`NULL`	A UNIX timestamp representing the time this report was zapped (marked as read).
`zapped_by`	`int(10)`	`NULL`	The ID of the user who zapped (marked as read) this report.

search_cache

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`ident`	`varchar(200)`	`””`	An identifier for the user who initiated the search. For a guest their IP address is used, for a logged in user, their username.
`search_data`	`mediumtext`	`NULL`	A serialized array containing search results.

search_matches

Field	Type	Default	Description
`post_id`	`int(10)`	`0`	The ID of the post which this word can be found.
`word_id`	`int(10)`	`0`	The ID of the word which can be found there.
`subject_match`	`tinyint(1)`	`0`	0 = The word is in the post body, 1 = the word is in a topic subject.

search_words

Field	Type	Default	Description
`id`	`int(10)`	`0`	The auto-incrementing primary key for this table.
`word`	`varchar(20)`	`””`	The word to be indexed.

topic_subscriptions

Field	Type	Default	Description
`user_id`	`int(10)`	`0`	The ID of the user which this subscription belongs to.
`topic_id`	`int(10)`	`0`	The ID of the topic which this subscription belongs to.

forum_subscriptions

Field	Type	Default	Description
`user_id`	`int(10)`	`0`	The ID of the user which this subscription belongs to.
`forum_id`	`int(10)`	`0`	The ID of the forum which this subscription belongs to.

topics

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`poster`	`varchar(200)`	`””`	The username of the user who posted this topic.
`subject`	`varchar(255)`	`””`	The subject of the topic.
`posted`	`int(10)`	`0`	A UNIX timestamp representing the time this topic was posted.
`first_post_id`	`int(10)`	`0`	The ID of the first post in this topic.
`last_post`	`int(10)`	`0`	A UNIX timestamp representing the time the last post was made to this topic.
`last_post_id`	`int(10)`	`0`	The ID of the last post in this topic.
`last_poster`	`varchar(200)`	`NULL`	The username of the user who posted the last reply to this topic.
`num_views`	`mediumint(8)`	`0`	The number of times this topic has been viewed.
`num_replies`	`mediumint(8)`	`0`	The number of replies this topic has.
`closed`	`tinyint(1)`	`0`	Is this topic closed?
`sticky`	`tinyint(1)`	`0`	Is this topic a sticky?
`moved_to`	`int(10)`	`NULL`	If the topic has been moved, the ID of the new topic (this one now solely acts as a redirect).
`forum_id`	`int(10)`	`0`	The ID of the forum this topic is within.

users

Field	Type	Default	Description
`id`	`int(10)`		The auto-incrementing primary key for this table.
`group_id`	`int(10)`	`3`	The ID of the group to which this user belongs. The default is FORUM_MemBER.
`username`	`varchar(200)`	`””`	The users username.
`password`	`varchar(40)`	`””`	The users password hash.
`email`	`varchar(80)`	`””`	The users email address.
`title`	`varchar(50)`	`NULL`	The user title. If this field is empty, the title from the user’s usetgroup will be used.
`realname`	`varchar(40)`	`NULL`	The real name of the user.
`url`	`varchar(100)`	`NULL`	The website of the user.
`jabber`	`varchar(80)`	`NULL`	The Jabber address of the user.
`icq`	`varchar(12)`	`NULL`	The Icq address of the user.
`msn`	`varchar(80)`	`NULL`	The MSN address of the user.
`aim`	`varchar(30)`	`NULL`	The Aim address of the user.
`yahoo`	`varchar(30)`	`NULL`	The Yahoo! address of the user.
`location`	`varchar(30)`	`NULL`	The location of the user. This can be a country, city or something else.
`signature`	`text`	`NULL`	The contents of the users signature.
`disp_topics`	`tinyint(3)`	`NULL`	The number of topics to display per page, or the forum default o_disp_topics_default (see $luna_config) if `NULL`.
`disp_posts`	`tinyint(3)`	`NULL`	The number of posts to display per page, or the forum default o_disp_posts_default (see $luna_config>$luna_config) if `NULL`.
`email_setting`	`tinyint(1)`	`1`	The level of privacy for the users email address. 0 = Show email address to other users, 1 = Hide email address but allow others users to send emails via the forums, 2 = Hide email address and don’t allow other users to send emails.
`notify_with_post`	`tinyint(1)`	`0`	Should a plain-text version of the post be included in subscription emails to the user?
`auto_notify`	`tinyint(1)`	`0`	Should the user automatically be subscribed to their own posts?
`show_smilies`	`tinyint(1)`	`1`	Should smilies in posts be shown to the user?
`show_img`	`tinyint(1)`	`1`	Should images in posts be shown to the user?
`show_img_sig`	`tinyint(1)`	`1`	Should images in signatures be shown to the user?
`show_avatars`	`tinyint(1)`	`1`	Should avatars be shown to the user?
`show_sig`	`tinyint(1)`	`1`	Should signatures to shown to the user?
`timezone`	`float`	`0`	The users timezone.
`dst`	`tinyint(1)`	`0`	Is the user currently observing daylight saving time?
`time_format`	`tinyint(1)`	`0`	The time format that the user uses.
`date_format`	`tinyint(1)`	`0`	The date format that the user uses.
`language`	`varchar(25)`	`“English”`	The language that should be used for this user.
`style`	`varchar(25)`	`“Air”`	The name of the style that should be used for this user.
`num_posts`	`int(10)`	`0`	The number of posts the user has made. Note: This is the number made, not the number that currently exist (i.e. when a post is deleted this isn’t decremented).
`last_post`	`int(10)`	`NULL`	A UNIX timestamp representing the time the user last made a post.
`last_search`	`int(10)`	`NULL`	A UNIX timestamp representing the time the user last performed a search.
`last_email_sent`	`int(10)`	`NULL`	A UNIX timestamp representing the time the user last sent an email via the forums.
`registered`	`int(10)`	`0`	A UNIX timestamp representing the time the user registered.
`registration_ip`	`varchar(39)`	`“0.0.0.0”`	The IP address used by the user when registering.
`last_visit`	`int(10)`	`0`	A UNIX timestamp representing the time of the users last visit.
`admin_note`	`varchar(30)`	`NULL`	A note that the administrator has entered.
`activate_string`	`varchar(80)`	`NULL`	A temporary storage string for new passwords and new e-mail addresses.
`activate_key`	`varchar(8)`	`NULL`	A temporary storage string for new password and new e-mail address activation keys.