autodoc/etc/schema.sql

ALTER SCHEMA `autodoc`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_general_ci ;

DROP TABLE IF EXISTS `documents_tags`;
DROP TABLE IF EXISTS `documents_pages`;
DROP TABLE IF EXISTS `pages_words`;
DROP TABLE IF EXISTS `words`;

DROP TABLE IF EXISTS `dict`;
DROP TABLE IF EXISTS `languages`;
DROP TABLE IF EXISTS `pages`;
DROP TABLE IF EXISTS `tags`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `documents`;

DROP PROCEDURE IF EXISTS `create_page_word`;
DROP PROCEDURE IF EXISTS `create_tag`;
DROP PROCEDURE IF EXISTS `create_document`;
DROP PROCEDURE IF EXISTS `create_page`;
DROP PROCEDURE IF EXISTS `delete_tag`;
DROP PROCEDURE IF EXISTS `get_document_filter`;
DROP PROCEDURE IF EXISTS `get_primary_page`;
DROP PROCEDURE IF EXISTS `set_primary_page`;
DROP PROCEDURE IF EXISTS `set_page_status`;

DROP FUNCTION IF EXISTS `SPLIT_STR`;

CREATE TABLE `dict` (
  `word` varchar(255) NOT NULL,
  `lang` char(2) NOT NULL,
  PRIMARY KEY (`word`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `username` varchar(255) NOT NULL,
  `passwd` varchar(255) DEFAULT NULL,
  `groups` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `documents` (
  `id` binary(16) NOT NULL,
  `owner` varchar(45) DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('nodata','inprogress','deleted','ok') NOT NULL DEFAULT 'nodata',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `languages` (
  `id` binary(16) NOT NULL,
  `short` char(2) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `short_UNIQUE` (`short`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pages` (
  `id` binary(16) NOT NULL,
  `owner` varchar(45) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('nodata','inprogress','deleted','ok') DEFAULT 'nodata',
  `isprimary` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tags` (
  `id` binary(16) NOT NULL,
  `tag` varchar(45) DEFAULT NULL,
  `color` enum('primary','secondary','success','danger','warning','info','light','dark') DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_UNIQUE` (`tag`,`color`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `words` (
  `id` binary(16) NOT NULL,
  `word` varchar(255) DEFAULT NULL,
  `languageId` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `word_UNIQUE` (`word`,`languageId`),
  KEY `fk_words_langid_idx` (`languageId`),
  CONSTRAINT `fk_words_languageid` FOREIGN KEY (`languageId`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `documents_tags` (
  `documentId` binary(16) NOT NULL,
  `tagId` binary(16) NOT NULL,
  PRIMARY KEY (`documentId`,`tagId`),
  KEY `fk_tags_id_idx` (`tagId`),
  CONSTRAINT `fk_doctags_docid` FOREIGN KEY (`documentId`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_doctags_tagid` FOREIGN KEY (`tagId`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pages_words` (
  `pageId` binary(16) NOT NULL,
  `wordId` binary(16) NOT NULL,
  `count` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`pageId`,`wordId`),
  KEY `fk_pagword_wordid_idx` (`wordId`),
  CONSTRAINT `fk_pagword_pageid` FOREIGN KEY (`pageId`) REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_pagword_wordid` FOREIGN KEY (`wordId`) REFERENCES `words` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `documents_pages` (
  `documentId` binary(16) NOT NULL,
  `pageId` binary(16) NOT NULL,
  PRIMARY KEY (`documentId`,`pageId`),
  KEY `fk_docpage_pageid_idx` (`pageId`),
  CONSTRAINT `fk_docpage_docid` FOREIGN KEY (`documentId`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_docpage_pageid` FOREIGN KEY (`pageId`) REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER ;;
CREATE FUNCTION `SPLIT_STR`(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
) RETURNS varchar(255) CHARSET utf8mb4
RETURN
	CASE WHEN CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) = CHAR_LENGTH(x)
	THEN x
	ELSE (REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''))
END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `create_page_word`(
	IN IN_pageid CHAR(36),
	IN IN_word VARCHAR(255),
	IN IN_langid CHAR(36))
BEGIN

DECLARE LOC_wordid BINARY(16) DEFAULT NULL;

INSERT IGNORE INTO words SET
	id     = UUID_TO_BIN(UUID()),
	word   = IN_word,
	langId = UUID_TO_BIN(IN_langid);

SELECT BIN_TO_UUID(id) INTO LOC_wordid FROM words WHERE word = IN_word AND langId = UUID_TO_BIN(IN_langid);

INSERT INTO pages_words SET
	pageId = UUID_TO_BIN(IN_pageid),
	wordId = UUID_TO_BIN(LOC_wordid),
	count = 1
	ON DUPLICATE KEY
	UPDATE count = count + 1;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `create_tag`(
	IN IN_documentid BINARY(16),
	IN IN_tag VARCHAR(45))
BEGIN

DECLARE LOC_tagid BINARY(16) DEFAULT UUID();

INSERT IGNORE INTO tags SET
	id = LOC_tagid,
	tag = IN_tag;

SELECT id INTO LOC_tagid FROM tags WHERE tag = IN_tag;

INSERT IGNORE INTO documents_tags SET
	documentId = IN_documentid,
	tagId = LOC_tagid;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `create_document`(
	IN IN_owner VARCHAR(45))
BEGIN

DECLARE LOC_documentid BINARY(16) DEFAULT UUID();

INSERT INTO documents SET
	id = LOC_documentid,
	owner = IN_owner,
	status = 'nodata';

SELECT LOC_documentid AS documentId;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `create_page`(
	IN IN_documentId BINARY(16))
BEGIN

DECLARE LOC_pageid BINARY(16) DEFAULT UUID();

INSERT INTO pages SET
	id = LOC_pageid,
	status = 'nodata';

INSERT INTO documents_pages SET
	documentId = UUID_TO_BIN(IN_documentid),
	pageId = LOC_pageid;

SELECT BIN_TO_UUID(LOC_pageid) AS pageId;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `delete_tag`(
	IN IN_documentid BINARY(16),
	IN IN_tag VARCHAR(45))
BEGIN

DECLARE LOC_tagid BINARY(16) DEFAULT UUID();

SELECT id INTO LOC_tagid FROM tags WHERE tag = IN_tag;

DELETE FROM documents_tags
WHERE documentId = IN_documentid
AND tagId = LOC_tagid;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `get_document_filter`(
	IN IN_words VARCHAR(255),
	IN IN_tags VARCHAR(255),
	IN IN_lim1 INT,
	IN IN_lim2 INT)
BEGIN

DECLARE L_query TEXT default "";
DECLARE L_pos INT UNSIGNED DEFAULT 0;

SELECT BIN_TO_UUID(dp.documentId) AS id, SUM(pw.count) AS wordcount
FROM documents_pages dp
JOIN pages_words pw ON dp.pageId = pw.pageId
JOIN words w ON pw.wordId = w.id
WHERE w.word REGEXP CONCAT("(", IN_words, ")")
GROUP BY dp.documentId
ORDER BY wordcount DESC
LIMIT IN_lim1, IN_lim2;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `get_primary_page`(
	IN IN_documentid BINARY(16))
BEGIN
DECLARE LOC_pageid BINARY(16) DEFAULT NULL;

SELECT dp.pageId INTO LOC_pageid FROM
	documents_pages dp
	JOIN pages p ON dp.pageId = p.id
	WHERE dp.documentId = IN_documentid
	AND p.isprimary = 1;

IF LOC_pageid IS NULL
THEN
	SELECT dp.pageId INTO LOC_pageid FROM
	documents_pages dp
	JOIN pages p ON dp.pageId = p.id
	WHERE dp.documentId = IN_documentid
	ORDER BY RAND() LIMIT 1;
END IF;

SELECT BIN_TO_UUID(LOC_pageid) AS pageId;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `set_primary_page`(
	IN IN_pageid BINARY(16))
BEGIN
DECLARE LOC_documentid BINARY(16) DEFAULT NULL;

SELECT documentId INTO LOC_documentid FROM documents_pages WHERE pageId = IN_pageid;

UPDATE
	documents_pages dp
	JOIN pages p ON dp.pageId = p.id
	SET p.isprimary = 0
	WHERE dp.documentId = LOC_documentid;

UPDATE
	pages
	SET	isprimary = 1
	WHERE id = IN_pageid;

END ;;
DELIMITER ;

DELIMITER ;;
CREATE PROCEDURE `set_page_status`(
	IN IN_pageid BINARY(16),
	IN IN_status ENUM('nodata', 'inprogress', 'deleted', 'ok')
)
BEGIN

UPDATE pages SET status = IN_status WHERE id = UUID_TO_BIN(IN_pageid);

END ;;
DELIMITER ;