CMS
eharetea

An example of a content management system in MySQL.

Example MySQL CMS.

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 10:59 AM



Projects have a CHAR(1) approved row for determining the visibility of the project.
Permissions allow the user to specify whether other people can edit the section or modify files

Files table mainly keeps track of the user that uploaded new files, and revisions for updated files.

Categories, files, and projects have comments.
The type row in the comments table is used so link_id knows whether the comment is referencing a project_id or a file_id.

Categories can be nested, hence the parent_id.

The pages table will be used to keep track of custom php pages
The pages table is used so there can be settings for the projects content to be displayed using a certain page, or custom viewing styles(scripts, demos, videos, display icons, descriptions, expressions etc...).
The pages table allows projects to have a display setting referencing a page to use for that project.
Pages can be added into the pages table, and accessed using the page_id row in the projects table.

The associations table allows linkage of a type ( project, file ) to have multiple categores...good for searching(categories could be actual operator names as well)
The type row in the associations table is used so the link_id refers to project_id or file_id, so files and projects can be in the categories table.
The associations table allows a way for projects to grow, so that files within a project may not be just one file modified...
but a project of multiple files modified, and can live in multiple categories.


CREATE TABLE users(
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NOT NULL,
password CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL);

CREATE TABLE projects(
proj_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
page_id INT UNSIGNED NOT NULL,
parent_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
title VARCHAR(40) NOT NULL,
date_created DATETIME NOT NULL,
approved CHAR(1) NOT NULL,
permissions CHAR(3) NOT NULL);

CREATE TABLE files(
file_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
proj_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
title VARCHAR(40) NOT NULL,
upload_date DATETIME NOT NULL,
revision_num SMALLINT NOT NULL);

CREATE TABLE comments(
comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
link_id INT UNSIGNED NOT NULL,
type VARCHAR(15) NOT NULL,
comment TEXT NOT NULL,
FULLTEXT (comment) );

CREATE TABLE categories(
category_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
parent_id INT UNSIGNED NOT NULL,
title VARCHAR(40) NOT NULL);

CREATE TABLE pages(
page_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(20) NOT NULL,
description TEXT NOT NULL);

CREATE TABLE associations(
assoc_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
link_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
type VARCHAR(15) NOT NULL);

INSERT INTO categories (parent_id, title) VALUES (0,'Home'), (0,'Learn'), (0,'Content'), (0,'Training'), (0,'Log In');

INSERT INTO categories (parent_id, title) VALUES (2,'Animation'), (2,'Character'), (2,'Dyanmics'), (2,'Modeling'), (2,'Shading'), (2,'Expressions'), (2,'Hbin'), (2,'Hconfig'), (2,'Hscript'), (2,'Hython'), (2,'Matrix'), (2,'VEX'), (2,'Pipeline'), (2,'Rendering'), (2,'Tutorials');

INSERT INTO users VALUES(null,'Dan', 'Lynch', 'dan@mail.com', SHA('password'), NOW());

INSERT INTO projects VALUES(null,0,15, 1, 'opparm', NOW(), 1, 777);
INSERT INTO projects VALUES(null,0,15, 1, 'opset', NOW(), 1, 777);


INSERT INTO comments VALUES(null,1,'project','This describes the opset hscript command');

INSERT INTO comments VALUES(null,2,'project','This describes the opparm hscript command');

INSERT INTO comments VALUES(null,2,'category','This contains Learning information on Houdini');


INSERT INTO comments VALUES(null,20,'category','This category contains information on Rendering with Houdini');

INSERT INTO comments VALUES(null,1,'file','This shows denting with dops');

INSERT INTO comments VALUES(null,2,'file','This revision fixed dent problem');



SELECT * FROM comments WHERE MATCH(comment) AGAINST('dent');


INSERT INTO associations VALUES(null, 1,12,'project');
INSERT INTO associations VALUES(null, 1,17,'project');
INSERT INTO associations VALUES(null, 1,20,'project');

$query = 'select a.link_id, f.title, comment.short_desc from comment, folders AS f, associations AS a, categories AS c where (comment.link_id = f.folder_id && f.folder_id = a.link_id && a.category_id = c.category_id && '.$id.' = a.category_id)';