event_reminder_schema.sql

event_reminder_schema.sql

CREATE DATABASE IF NOT EXISTS `calendar` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `calendar`;
-- MySQL dump 10.13  Distrib 8.0.14, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: calendar
-- ------------------------------------------------------
-- Server version	8.0.14

/*!40101 SET @OLD_CHARACTER_SET_CLIENT = @@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS = @@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION = @@COLLATION_CONNECTION */;
SET NAMES utf8;
/*!40103 SET @OLD_TIME_ZONE = @@TIME_ZONE */;
/*!40103 SET TIME_ZONE = '+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;
/*!40101 SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES = @@SQL_NOTES, SQL_NOTES = 0 */;

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `event`
(
    `id`               bigint(20)                               NOT NULL AUTO_INCREMENT,
    `userId`           bigint(20)                               NOT NULL,
    `sourceId`         bigint(20)                                        DEFAULT NULL,
    `sourceType`       varchar(50) COLLATE utf8mb4_unicode_ci            DEFAULT NULL,
    `title`            varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
    `descritpion`      varchar(2048) COLLATE utf8mb4_unicode_ci          DEFAULT NULL,
    `type`             smallint(6)                              NOT NULL DEFAULT '0',
    `url`              varchar(1024) COLLATE utf8mb4_unicode_ci          DEFAULT NULL,
    `active`           tinyint(1)                               NOT NULL DEFAULT '0',
    `system`           tinyint(1)                               NOT NULL DEFAULT '0',
    `reminderCount`    smallint(6)                              NOT NULL DEFAULT '0',
    `reminderInterval` smallint(6)                              NOT NULL DEFAULT '0',
    `reminderUnit`     smallint(6)                              NOT NULL DEFAULT '0',
    `createdAt`        datetime                                 NOT NULL,
    `updatedAt`        datetime                                          DEFAULT NULL,
    `scheduledAt`      datetime                                          DEFAULT NULL,
    `triggeredAt`      datetime                                          DEFAULT NULL,
    `content`          text COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (`id`),
    KEY `idx_event_user` (`userId`),
    CONSTRAINT `fk_event_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event`
--

LOCK TABLES `event` WRITE;
/*!40000 ALTER TABLE `event`
    DISABLE KEYS */;
/*!40000 ALTER TABLE `event`
    ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `event_template`
--

DROP TABLE IF EXISTS `event_template`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `event_template`
(
    `id`          bigint(20)                              NOT NULL AUTO_INCREMENT,
    `title`       varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `description` varchar(2048) COLLATE utf8mb4_unicode_ci         DEFAULT NULL,
    `type`        smallint(6)                             NOT NULL DEFAULT '0',
    `sourceType`  varchar(50) COLLATE utf8mb4_unicode_ci           DEFAULT NULL,
    `createdAt`   datetime                                NOT NULL,
    `updatedAt`   datetime                                         DEFAULT NULL,
    `content`     text COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `event_template`
--

LOCK TABLES `event_template` WRITE;
/*!40000 ALTER TABLE `event_template`
    DISABLE KEYS */;
/*!40000 ALTER TABLE `event_template`
    ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `reminder`
--

DROP TABLE IF EXISTS `reminder`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `reminder`
(
    `id`        bigint(20) NOT NULL AUTO_INCREMENT,
    `eventId`   bigint(20) NOT NULL,
    `userId`    bigint(20) NOT NULL,
    `read`      tinyint(1) NOT NULL DEFAULT '1',
    `trash`     tinyint(1) NOT NULL DEFAULT '1',
    `createdAt` datetime   NOT NULL,
    `updatedAt` datetime            DEFAULT NULL,
    `content`   text COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (`id`),
    KEY `idx_reminder_event` (`eventId`),
    KEY `idx_reminder_user` (`userId`),
    CONSTRAINT `fk_reminder_event` FOREIGN KEY (`eventId`) REFERENCES `event` (`id`),
    CONSTRAINT `fk_reminder_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `reminder`
--

LOCK TABLES `reminder` WRITE;
/*!40000 ALTER TABLE `reminder`
    DISABLE KEYS */;
/*!40000 ALTER TABLE `reminder`
    ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `user`
(
    `id`           bigint(20)                             NOT NULL AUTO_INCREMENT,
    `firstName`    varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `middleName`   varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `lastName`     varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `username`     varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `mobile`       varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `email`        varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `passwordHash` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    `registeredAt` datetime                               NOT NULL,
    `lastLogin`    datetime                               DEFAULT NULL,
    `intro`        tinytext COLLATE utf8mb4_unicode_ci,
    `profile`      text COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_username` (`username`),
    UNIQUE KEY `uq_mobile` (`mobile`),
    UNIQUE KEY `uq_email` (`email`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user`
    DISABLE KEYS */;
/*!40000 ALTER TABLE `user`
    ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE = @OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE = @OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT = @OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS = @OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION = @OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES = @OLD_SQL_NOTES */;

-- Dump completed on 2020-07-16 21:57:20