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.
