Created
December 4, 2024 08:47
-
-
Save zzpzaf/3616416afd1f9fbeb60f1ba800c8aa27 to your computer and use it in GitHub Desktop.
ang18-SSR-SEO-SupportBlog2-testblog2-db-schema1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Blog backend Test schema | |
-------------------------- | |
schema.sql | |
---------------------------------------------------------------------------- | |
MariaDB/MySQL Tables and Data Objectsc for Jdbc default | |
Requires MariaDB version 10.2.1 or MySQL version 8.0.13 and aftrward. | |
(C)opyright Panos Zafeiropoulos - 2024 | |
---------------------------------------------------------------------------- | |
Last update: 241123 - PZ | |
----------------------------------------------------------------------------- | |
*/ | |
USE `testblog2`; | |
@ | |
--DELIMITER @ | |
-- ------------------------------------ | |
-- Table structure for testcategories | |
-- ------------------------------------ | |
BEGIN; | |
@ | |
DROP TABLE IF EXISTS `categories`; | |
@ | |
CREATE TABLE `testcategories` ( | |
`categoryId` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`categoryTitle` varchar(100) NOT NULL, | |
`categoryStatusId` int(3) unsigned NOT NULL DEFAULT 1, | |
`categoryUUID` varchar(36) NOT NULL DEFAULT (UUID()), | |
PRIMARY KEY (`categoryId`) | |
); | |
@ | |
COMMIT; | |
@ | |
-- ---------------------------------- | |
-- Table structure for testarticles | |
-- ---------------------------------- | |
BEGIN; | |
@ | |
DROP TABLE IF EXISTS `articles`; | |
@ | |
CREATE TABLE `articles` ( | |
`articleId` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`categoryId` int(10) unsigned NOT NULL, | |
`userId` int(10) unsigned NOT NULL, | |
`articleTitle` varchar(100) NOT NULL, | |
`articleSubTitle` varchar(200) NOT NULL, | |
`articleSlug` varchar(300) UNIQUE, | |
`articleDescription` varchar(250), | |
`articleContent` MEDIUMTEXT, | |
`articleStatusId` int(3) unsigned NOT NULL DEFAULT 1, | |
`articleUUID` varchar(36) NOT NULL DEFAULT (UUID()), | |
`articleCreationTimestamp` TIMESTAMP(6) DEFAULT (CURRENT_TIMESTAMP(6)), | |
`articleLastUpdTimestamp` TIMESTAMP(6) DEFAULT (CURRENT_TIMESTAMP(6)), | |
PRIMARY KEY (`articleId`) | |
); | |
@ | |
COMMIT; | |
@ | |
-- ---------------------------- | |
-- Table structure for Userss | |
-- ---------------------------- | |
BEGIN; | |
DROP TABLE IF EXISTS `users`; | |
--@ | |
CREATE TABLE `users` ( | |
`userId` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`userSlugName` varchar(15) NOT NULL UNIQUE, | |
`userName` varchar(40) NOT NULL, | |
`userSurname` varchar(120), | |
`userPassword` varchar(80) NOT NULL, | |
`userEmail` varchar(40) NOT NULL UNIQUE, | |
`userIsAuthor` tinyint(1) DEFAULT 1, | |
`userIsEnabled` tinyint(1) DEFAULT 1, | |
`userStatusId` int(3) unsigned NOT NULL DEFAULT 1, | |
`userUUID` varchar(36) NOT NULL DEFAULT (UUID()), | |
`userCreationTimestamp` TIMESTAMP(6) DEFAULT (CURRENT_TIMESTAMP(6)), | |
`userClientUUID` varchar(40), | |
PRIMARY KEY (`userId`) | |
); | |
COMMIT; | |
@ | |
-- ---------------------------------------- | |
-- Trigger for auto-encryption of password | |
-- ---------------------------------------- | |
DROP TRIGGER IF EXISTS `INS_USER_PASSW`; | |
@ | |
CREATE TRIGGER `INS_USER_PASSW` | |
BEFORE INSERT ON `users` | |
FOR EACH ROW | |
BEGIN | |
DECLARE salt VARCHAR(16); | |
-- Check if the password is 80 characters and hexadecimal | |
IF CHAR_LENGTH(NEW.`userPassword`) = 80 AND | |
NEW.`userPassword` REGEXP '^[0-9a-fA-F]+$' THEN | |
-- Leave the password unchanged | |
SET NEW.`userPassword` = NEW.`userPassword`; | |
ELSE | |
-- Generate salt and hash the password | |
SET salt = SUBSTRING(SHA2(RAND(), 256), 1, 16); | |
SET NEW.`userPassword` = CONCAT(salt, SHA2(CONCAT(salt, NEW.`userPassword`), 256)); | |
END IF; | |
END | |
; | |
@ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment