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.

categories

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.