USE BASE;

DELIMITER $$

DROP PROCEDURE IF EXISTS getDocument$$

CREATE PROCEDURE getDocument(IN setName CHAR( 32 ))
SQL SECURITY INVOKER
BEGIN
	SELECT
		Page.Value AS Page,
		Descriptions.Value AS Description,
		Keywords.Value AS Keyword,
		Titles.Value AS Title,
		Bodys.Value AS Body,
		Created.Value AS Created,
		Changed.Value AS Changed,
		Name.Value AS Name,
		Surname.Value AS Surname,
		Nickname.Value AS Nickname
	FROM
		BASE.Documents
		LEFT OUTER JOIN BASE.Names Page
			ON Documents.ID_Name = Page.ID
		LEFT OUTER JOIN BASE.Descriptions
			ON Documents.ID_Description = Descriptions.ID
		LEFT OUTER JOIN BASE.Keywords
			ON Documents.ID_Keyword = Keywords.ID
		LEFT OUTER JOIN BASE.Titles
			ON Documents.ID_Title = Titles.ID
		LEFT OUTER JOIN BASE.Bodys
			ON Documents.ID_Body = Bodys.ID
		LEFT OUTER JOIN BASE.Attributes
			ON Documents.ID_Attribute = Attributes.ID
		LEFT OUTER JOIN BASE.Dates Created
			ON Documents.ID_Created = Created.ID
		LEFT OUTER JOIN BASE.Dates Changed
			ON Documents.ID_Changed = Changed.ID
		LEFT OUTER JOIN BASE.Authors Author
			ON Documents.ID_Author = Author.ID
		LEFT OUTER JOIN BASE.Names Name
			ON Author.ID_Name = Name.ID
		LEFT OUTER JOIN BASE.Names Surname
			ON Author.ID_Surname = Surname.ID
		LEFT OUTER JOIN BASE.Names Nickname
			ON Author.ID_Nickname = Nickname.ID
	WHERE
		Page.Value = setName AND
		Attributes.Value = 'visible';
END
$$

DROP PROCEDURE IF EXISTS setDocument$$

CREATE PROCEDURE setDocument(IN Name VARCHAR( 32 ), IN Parent VARCHAR( 32 ),
	IN Description VARCHAR( 128 ), IN Keyword VARCHAR( 128 ), IN Title VARCHAR( 64 ),
	IN Body MEDIUMTEXT, IN Author VARCHAR( 32 ))
SQL SECURITY INVOKER
BEGIN
	IF NOT (SELECT Value FROM Names WHERE Value = Name) <=> Name
	AND (SELECT Value FROM Names WHERE Value = Parent) = Parent 
	AND (SELECT Value FROM Names WHERE Value = Author) = Author THEN

		INSERT INTO Names (ID, Value) VALUES (NULL, Name);

		IF NOT (SELECT Value FROM Descriptions WHERE Value = Description) <=> Description THEN
			INSERT INTO Descriptions (ID, Value) VALUES (NULL, Description);
		END IF;

		IF NOT (SELECT Value FROM Keywords WHERE Value = Keyword) <=> Keyword THEN
			INSERT INTO Keywords (ID, Value) VALUES (NULL, Keyword);
		END IF;

		IF NOT (SELECT Value FROM Titles WHERE Value = Title) <=> Title THEN
			INSERT INTO Titles (ID, Value) VALUES (NULL, Title);
		END IF;

		IF NOT (SELECT Value FROM Bodys WHERE Value = Body) <=> Body THEN
			INSERT INTO Bodys (ID, Value) VALUES (NULL, Body);
		END IF;

		IF NOT (SELECT Value FROM BASE.Dates WHERE Value = CURDATE()) <=> CURDATE() THEN
			INSERT INTO Dates (ID, Value) VALUES (NULL, CURDATE());
		END IF;

		SET @ID_Parent = (SELECT ID FROM Names WHERE Value = Parent);
		SET @ID_Name = (SELECT ID FROM Names WHERE Value = Name);
		SET @ID_Description = (SELECT ID FROM Descriptions WHERE Value = Description);
		SET @ID_Keyword = (SELECT ID FROM Keywords WHERE Value = Keyword);
		SET @ID_Title = (SELECT ID FROM Titles WHERE Value = Title);
		SET @ID_Body = (SELECT ID FROM Bodys WHERE Value = Body);
		SET @ID_Date = (SELECT ID FROM Dates WHERE Value = CURDATE());
		SET @ID_Author = (SELECT Authors.ID FROM BASE.Names LEFT OUTER JOIN
		BASE.Authors ON Authors.ID_Nickname = Names.ID WHERE Names.Value = Author);
		
		INSERT INTO Documents (ID, PID, ID_Name, ID_Description, ID_Keyword,
		ID_Title, ID_Body, ID_Attribute, ID_Created, ID_Changed, ID_Author)
		VALUES (NULL, @ID_Parent, @ID_Name, @ID_Description, @ID_Keyword,
		@ID_Title, @ID_Body, '1', @ID_Date, '2', @ID_Author);
		
	END IF;
END
$$

DELIMITER ;

CALL setDocument(
	'Test',	 -- Name
	'help',	 -- Parent
	'Test',	 -- Description
	'Test',	 -- Keyword
	'Test',	 -- Title
	'Test',	 -- Body
	'user1'); -- Author

CALL BASE.getDocument('Test');

     - поиск по заголовку
     - поиск в теле
     - поиск по дате