DROP TABLE IF EXISTS BASE.Documents;
DROP TABLE IF EXISTS BASE.Authors;
DROP TABLE IF EXISTS BASE.Descriptions;
DROP TABLE IF EXISTS BASE.Keywords;
DROP TABLE IF EXISTS BASE.Titles;
DROP TABLE IF EXISTS BASE.Bodys;
DROP TABLE IF EXISTS BASE.Status;
DROP TABLE IF EXISTS BASE.Groups;
DROP TABLE IF EXISTS BASE.Dates;
DROP TABLE IF EXISTS BASE.Attributes;

CREATE TABLE IF NOT EXISTS BASE.Attributes(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value ENUM('visible', 'hidden') NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Attributes (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Dates(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (ID),
	INDEX ID_Dates (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Groups(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value ENUM('guest', 'manager', 'user') NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Groups (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Status(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value ENUM('on', 'off') NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Status (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Bodys(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value MEDIUMTEXT NOT NULL,
	PRIMARY KEY (ID)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Titles(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value VARCHAR( 64 ) NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Titles (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Keywords(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value VARCHAR( 128 ) NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Keywords (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Descriptions(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Value VARCHAR( 128 ) NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Descriptions (Value)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Authors(
	ID INT( 16 ) NOT NULL AUTO_INCREMENT,
	Name VARCHAR( 64 ) NOT NULL,
	Password VARCHAR( 64 ) NOT NULL,
	ID_Authorized INT( 16 ) NOT NULL,
	ID_Blocked INT( 16 ) NOT NULL,
	ID_Groups INT( 16 ) NOT NULL,
	ID_Status INT( 16 ) NOT NULL,
	PRIMARY KEY (ID),
	INDEX ID_Authors (Name)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS BASE.Documents(
	ID INT( 16 ) UNSIGNED NOT NULL AUTO_INCREMENT,
	PID INT( 16 ) NOT NULL,
	Name VARCHAR( 32 ) NOT NULL,
	ID_Author INT( 16 ) NOT NULL,
	ID_Description INT( 16 ) NOT NULL,
	ID_Keyword INT( 16 ) NOT NULL,
	ID_Title INT( 16 ) NOT NULL,
	ID_Body INT( 16 ) NOT NULL,
	ID_Created INT( 16 ) NOT NULL,
	ID_Changed INT( 16 ) NOT NULL,
	ID_Attribute INT( 16 ) NOT NULL,
	PRIMARY KEY (ID),

	INDEX ID_Authors (ID_Author),
	INDEX ID_Descriptions (ID_Description),
	INDEX ID_Keywords (ID_Keyword),
	INDEX ID_Titles (ID_Title),
	INDEX ID_Bodys (ID_Body),
	INDEX ID_Createds (ID_Created),
	INDEX ID_Changeds (ID_Changed),
	INDEX ID_Attributes (ID_Attribute),

	CONSTRAINT ID_Author FOREIGN KEY (ID_Author)
	REFERENCES BASE.authors (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Descriptions FOREIGN KEY (ID_Description)
	REFERENCES BASE.descriptions (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Keywords FOREIGN KEY (ID_Keyword)
	REFERENCES BASE.keywords (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Titles FOREIGN KEY (ID_Title)
	REFERENCES BASE.titles (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Bodys FOREIGN KEY (ID_Body)
	REFERENCES BASE.bodys (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Createds FOREIGN KEY (ID_Created)
	REFERENCES BASE.Dates (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Changeds FOREIGN KEY (ID_Changed)
	REFERENCES BASE.Dates (ID) ON UPDATE CASCADE,

	CONSTRAINT ID_Attributes FOREIGN KEY (ID_Attribute)
	REFERENCES BASE.Attributes (ID) ON UPDATE CASCADE

) ENGINE = INNODB;

INSERT INTO BASE.Attributes (id, value) VALUES
( 1 , 'visible'), ( 2 , 'hidden');

INSERT INTO BASE.Dates (id, value) VALUES
( 1 , '15.07.2010'), ( 2 , '16.07.2010');

INSERT INTO BASE.Groups (id, value) VALUES
( 1 , 'guest'), ( 2 , 'manager'), ( 3 , 'user');

INSERT INTO BASE.Status (id, value) VALUES
( 1 , 'on'), ( 2 , 'off');

INSERT INTO BASE.Bodys (id, value) VALUES
( 1 , 'about Body'), ( 2 , 'help Body'), ( 3 , 'faq Body');

INSERT INTO BASE.Titles (id, value) VALUES
( 1 , 'about Title'), ( 2 , 'help Title'), ( 3 , 'faq Title');

INSERT INTO BASE.Keywords (id, value) VALUES
( 1 , 'about Keyword'), ( 2 , 'help Keyword'), ( 3 , 'faq Keyword');

INSERT INTO BASE.Descriptions (id, value) VALUES
( 1 , 'about Description'), ( 2 , 'help Description'), ( 3 , 'faq Description');

INSERT INTO BASE.Authors (id, Name, Password, ID_Authorized, ID_Blocked,
ID_Groups, ID_Status) VALUES
( 1 , 'user 1', SHA('pass 1'), '1', '', '1', '1'),
( 2 , 'user 2', SHA('pass 2'), '2', '', '2', '1');

INSERT INTO BASE.Documents (id, PID, Name, ID_Author, ID_Description,
ID_Keyword, ID_Title, ID_Body, ID_Created, ID_Changed, ID_Attribute) VALUES
( 1 ,  1 , 'about',  1 ,  1 ,  1 ,  1 ,  1 ,  1 ,  2 ,  1 ),
( 2 ,  1 , 'help',  2 ,  2 ,  2 ,  2 ,  2 ,  1 ,  2 ,  1 ),
( 3 ,  1 , 'faq',  1 ,  3 ,  3 ,  3 ,  3 ,  1 ,  2 ,  1 );

SELECT
	Authors.Name AS Author,
	Descriptions.Value AS Description,
	Keywords.Value AS Keyword,
	Titles.Value AS Title,
	Bodys.Value AS Body,
	Dates.Value AS Created,
	Dates.Value AS Changed
FROM
	BASE.Documents
	LEFT OUTER JOIN BASE.Authors
		ON Authors.ID = Documents.ID_Author
	LEFT OUTER JOIN BASE.Descriptions
		ON Descriptions.ID = Documents.ID_Description
	LEFT OUTER JOIN BASE.Keywords
		ON Keywords.ID = Documents.ID_Keyword
	LEFT OUTER JOIN BASE.Titles
		ON Titles.ID = Documents.ID_Title
	LEFT OUTER JOIN BASE.Bodys
		ON Bodys.ID = Documents.ID_Body
	LEFT OUTER JOIN BASE.Attributes
		ON Attributes.ID = Documents.ID_Attribute
	LEFT OUTER JOIN BASE.Dates
		ON Dates.ID = Documents.ID_Created
		AND Dates.ID = Documents.ID_Changed
WHERE
	Documents.Name = 'help' AND
	Attributes.Value = 'visible'