New (old) PHP forum SQL tables

While waiting to finish the rest of the code on my forum script, I decided to do some updates to the SQL tables. I’ve split up the scripts into four files; clean.sql, setup.sql, forums.sql, users.sql.

The separate files are to make the setup phase more flexible. An admin can choose to overwrite a previous installation by running clean.sql, or simply run setup.sql to create the tables if only the database exists. The remaining files, forums.sql and users.sql, are to create the default forums, users and groups. The default forums will contain one demo topic and reply. The default user will be the admin account.

The group privilege system is setup as a series of flags where 1 = has privilege and 0 = doesn’t have it. These flags are arranged in the following order:

  • Can create replies
  • Can create topics
  • Can use PM
  • Can reply in read-only forums
  • Can create topics in read-only forums
  • Can edit own posts
  • Can delete own posts
  • Can edit other users’ posts
  • Can delete other users’ posts
  • Can move topics
  • Can lock topics
  • Can create forums
  • Can edit forums
  • Can delete forums
  • Can create users
  • Can ban users
  • Can edit users
  • Can delete users
  • Can create groups
  • Can edit groups
  • Can delete groups

A user in the “Administrators” group will have : ‘1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1’. As in all the privileges.

A user in the “Moderators” group will have : ‘1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,1,0,0,0,0,0’. As in every privilege from replying to posts to locking topics. But canot affect forums, groups or users (other than banning them).

A user in the ordinary “Members” group will have : ‘1,1,1,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0’. As in the ability to create topics, replies and delete their own posts or replies, but nothing else.

I felt this is a better system than giving admins everthing, mods something and members nothing as it allows greater control. Maybe a forum owner would want different types of admins or introduce a ladder system via a plugin.

And speaking of plugins, this turned out to be the hardest part so far. It seems introducing some degree of user override without allowing a plugin to destroy the base system is a bit of a noodle scratcher for me. I thought of a simple hooks system, but this seemed is too limited. A strictly API based function override would be too rigid and cumbersome. So the only solution seems to be a hybrid of sorts. I’m still looking into this and I want to be careful where I get examples.

This is taking longer than expected because I’m trying to incorporate examples that are either in the public domain or under a very permissive license since I plan to release this with the ISC license. I would prefer to release it under only my usual disclaimer (pretty much in the Public Domain), but I don’t want to violate any other licenses in the process.

The ceanup sql script (clean.sql):

-- Existing table cleanup (This method destroys existing data)

DROP TABLE IF EXISTS `messages`;
DROP TABLE IF EXISTS `forums`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `groups`;
DROP TABLE IF EXISTS `pm`;
DROP TABLE IF EXISTS `polls`;
DROP TABLE IF EXISTS `pollvotes`;
DROP TABLE IF EXISTS `banned`;
DROP TABLE IF EXISTS `pollvotes`;
DROP TABLE IF EXISTS `confirmations`;

Note: The original six tables have already balooned into ten. This was inevitable as too much simplicity in the database will result in increased complexity code-side. In this case, when it came down to complexity, I had to pick the lesser of two evils.

The setup script (setup.sql):

-- Create all tables

-- Topics and replies are stored here
CREATE TABLE `messages` (
	`id` bigint(20) NOT NULL auto_increment,
	`parent_id` bigint(20) default 0,
	`forum_id` int(25) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`user_id` bigint(20) default 0,
	`author` varchar(50) default '',
	`author_email` varchar(100) default '',
	`author_web` varchar(255) default '',
	`author_ip` varchar(100) default '',
	`reply_count` int(25) default 0,
	`view_count` int(25) default 0,
	`locked` tinyint(4) default 0,
	`sticky` tinyint(4) default 0,
	`global_sticky` tinyint(4) default 0,
	`last_id` bigint(20) NOT NULL default 0,
	`last_author` varchar(50) NOT NULL default '',
	`last_date` datetime NOT NULL default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- All forum data
CREATE TABLE `forums` (
	`id` int(25) NOT NULL auto_increment,
	`parent_id` int(25) default 0,
	`display_order` int(16) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`content_plain` text default '',
	`topic_count` int(25) default 0,
	`reply_count` int(25) default 0,
	`last_id` bigint(20) default '0',
	`last_author` varchar(50) default '',
	`last_date` datetime default '0000-00-00 00:00:00',
	`read_only` tinyint(4) default 0,
	`private` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Membership info
CREATE TABLE `users` (
	`id` bigint(20) NOT NULL auto_increment,
	`username` varchar(50) NOT NULL default '',
	`password` text NOT NULL default '',
	`password_salt` text NOT NULL default '',
	`display_name` varchar(100) default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`enabled` tinyint(4) default 1,
	`sig` text default '',
	`avatar` text default '',
	`email` varchar(100) default '',
	`web` varchar(255) default '',
	`bio` text default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Sorted groups
CREATE TABLE `groups` (
	`id` int(25) NOT NULL auto_increment,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`signup` tinyint(4) default 0,
	`privileges` text default '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- User / Group relationships
DROP TABLE IF EXISTS `usersgroups`;

CREATE TABLE `usersgroups` (
	`user_id` bigint(20) NOT NULL,
	`group_id` int(25) NOT NULL
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Private Messaging
CREATE TABLE `pm` (
	`id` bigint(20) NOT NULL auto_increment,
	`user_id` bigint(20) default 0,
	`sender_id` bigint(20) default 0,
	`title` varchar(255) NOT NULL default '',
	`content` text default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`opened` tinyint(4) default 0,
	`hide_sender` tinyint(4) default 0,
	`hide_reader` tinyint(4) default 0,
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Topic polls
CREATE TABLE `polls` (
	`id` bigint(20) NOT NULL auto_increment,
	`message_id` bigint(20) default 0,
	`question` text NOT NULL default '',
	`multiple` tinyint(4) default 0,
	`option1` varchar(255) default '',
	`option2` varchar(255) default '',
	`option3` varchar(255) default '',
	`option4` varchar(255) default '',
	`option5` varchar(255) default '',
	`option6` varchar(255) default '',
	`option7` varchar(255) default '',
	`option8` varchar(255) default '',
	`option9` varchar(255) default '',
	`option10` varchar(255) default '',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Poll votes
CREATE TABLE `pollvotes` (
	`id` bigint(20) NOT NULL auto_increment,
	`poll_id` bigint(20) NOT NULL,
	`user_id` bigint(20) NOT NULL,
	`options` varchar(25) default '0,0,0,0,0,0,0,0,0,0',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Banned emails, IPs or usernames
CREATE TABLE `banned` (
	`id` bigint(20) NOT NULL auto_increment,
	`ip` varchar(100) default '',
	`username` varchar(50) default '',
	`email` varchar(100) default '',
	`reason` varchar(255) default '',
	`ban_date` datetime NOT NULL default '0000-00-00 00:00:00',
	`expires` datetime default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;


-- Registration or email change confirmations
CREATE TABLE `confirmations` (
	`id` bigint(20) NOT NULL auto_increment,
	`user_id` bigint(20) NOT NULL,
	`title` varchar(255) default '',
	`type` varchar(100) NOT NULL default '',
	`created` datetime default '0000-00-00 00:00:00',
	`expires` datetime default '0000-00-00 00:00:00',
		PRIMARY KEY (`id`)
) TYPE = MyISAM DEFAULT CHARSET = utf8;

Note: The “groups” table has a “signup” flag. This is where all new registered users will be sorted.

The default user and group setup script (users.sql):

-- Default user (admin)

INSERT INTO users(`id`, `username`, `password`, `password_salt`, `created`, `enabled`, `email`) 
		VALUES('1', 'USERNAME', 'PASSWORD', 'PASSWORDSALT', NOW(), '1', 'EMAIL');


-- Default groups (new members will be sorted into the Members group)

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('1', 'Administrators', 'Will have global administration privileges', '0', '1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1');

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('2', 'Moderators', 'Will moderation privileges', '0', '1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,1,0,0,0,0,0');

INSERT INTO groups(`id`, `title`, `content`, `signup`, `privileges`) 
		VALUES('2', 'Members', 'Will be the default registration group', '1', '1,1,1,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0');


-- Setup the default user in the Administrators group

INSERT INTO usersgroups(`user_id`, `group_id`) 
		VALUES('1', '1');

Note: USERNAME, PASSWORD, PASSWORDSALT and EMAIL markers in the first INSERT command will be replaced with the appropriate data by the installation script. These will be grabbed by the submitted form data.

And lastly, the demo forum, topic and reply data (forums.sql)

-- Default entries

INSERT INTO forums (`id`, `parent_id`, `display_order`, `title`, `content`, `content_plain`, `topic_count`, `reply_count`, `last_id`, `last_author`, `last_date`, `read_only`, `private`) 
		VALUES ('1', '0', '0', 'General', '<p>This is where the chit chat happens</p>', 'This is where the chit chat happens', '1', '1', '2', 'Anon Author', NOW(), '0', '0');

INSERT INTO messages (`id`, `parent_id`, `forum_id`, `title`, `content`, `content_plain`, `created`, `author`, `reply_count`, `view_count`, `locked`, `last_id`, `last_author`, `last_date`, `sticky`, `global_sticky`) 
		VALUES ('1', '0', '1', 'Demo Topic', '<p>This is an example of a forum topic</p>', 'This is an example of a forum topic', NOW(), 'Anon Author', '1', '0', '0', '0', 'Anon Author', NOW(), '0', '0');

INSERT INTO messages (`id`, `parent_id`, `forum_id`, `title`, `content`, `content_plain`, `created`, `author`, `reply_count`, `view_count`, `locked`, `last_id`, `last_author`, `last_date`, `sticky`, `global_sticky`) 
		VALUES ('2', '1', '1', 'Demo Reply', '<p>This is an example of a topic reply</p>', 'This is an example of a topic reply', NOW(), 'Anon Author', '0', '0', '0', '0', 'Anon Author', NOW(), '0', '0');

If you spot any errors in the SQL or if you have any suggestions on improving the overall design, please comment.

5 thoughts on “New (old) PHP forum SQL tables

  1. Um, uh, umm, uh, well, uhhh… Um, let’s see… Do I see any errors in the SQL…? Well, no, but then again, I have no idea what SQL is…

    Squirrels Quilt Languidly?
    Super Queen Latifah?
    Some Questions Loom?
    Simple Quip Leroy?
    Sunshine Quells Lunchtime?

    …Beautiful day out there. Isn’t it? Hope you’re doing well, eksith. (And once again ends any intelligent thought or insight Alan might have into these types of subjects.)

    • Hehe! “Structured Query Language” ;)

      Basically the gobbledygook we use to talk to databases and wrangle much needed information from them. Think of it as the computer equivalent of “Good Cop / Bad Cop”.

      Sadly, it’s not terribly sophisticated when compared to plain ol’ English, but just good enough when we need to get data in a timely manner. Much better alternative IMO than sifting through it ourselves until the dawn of the apocalypse.

      • Yeah, until Billy reads from the file Sandy is trying to write to. :P

        Incidentally, you may be interested in Fetch. It’s a text based db engine designed to function close to MySQL et al. Really handy piece of code.

        I worked with it a while back when I wrote a port of the Kure blog engine which also uses text files for storage (this is when I first seriously played with PHP).

        The software is very minimal, which is part of the reason I really liked it. Haven’t done anything lately with Fetch or Kure, but both are very interesting projects to watch out for. You can probably find some of my old posts on the Kure forums.

  2. Pingback: Forum Core.class.php progress update « This page intentionally left ugly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s