· 8 years ago · Dec 07, 2017, 03:06 AM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema recordr
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema recordr
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `recordr` DEFAULT CHARACTER SET utf8 ;
18USE `recordr` ;
19USE `recordr` ;
20
21-- -----------------------------------------------------
22-- Placeholder table for view `recordr`.`ClassificationView`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `recordr`.`ClassificationView` (`RecordId` INT, `ClassificationPath` INT);
25
26-- -----------------------------------------------------
27-- Placeholder table for view `recordr`.`FullTextView`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `recordr`.`FullTextView` (`rid` INT, `rconsign` INT, `rCreatedAt` INT, `rClosedAt` INT, `rNumber` INT, `rTitle` INT, `rUpdatedAt` INT, `bid` INT, `bcosign` INT, `bTitle` INT, `bNumber` INT, `nid` INT, `nTexts` INT, `lid` INT, `lName` INT, `rtid` INT, `rtName` INT, `classList` INT, `rsid` INT, `rsName` INT, `rscid` INT, `rscName` INT);
30
31-- -----------------------------------------------------
32-- View `recordr`.`ClassificationView`
33-- -----------------------------------------------------
34DROP TABLE IF EXISTS `recordr`.`ClassificationView`;
35USE `recordr`;
36CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`avengers`@`%` SQL SECURITY DEFINER VIEW `recordr`.`ClassificationView` AS select `recordr`.`recordclassifications`.`RecordId` AS `RecordId`,group_concat(`recordr`.`classifications`.`Name` separator ' - ') AS `ClassificationPath` from (`recordr`.`classifications` join `recordr`.`recordclassifications` on((`recordr`.`classifications`.`Id` = `recordr`.`recordclassifications`.`ClassId`))) group by `recordr`.`recordclassifications`.`RecordId`;
37
38-- -----------------------------------------------------
39-- View `recordr`.`FullTextView`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `recordr`.`FullTextView`;
42USE `recordr`;
43CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`avengers`@`%` SQL SECURITY DEFINER VIEW `recordr`.`FullTextView` AS select `r`.`Id` AS `rid`,`r`.`ConsignmentCode` AS `rconsign`,`r`.`CreatedAt` AS `rCreatedAt`,`r`.`ClosedAt` AS `rClosedAt`,`r`.`Number` AS `rNumber`,`r`.`Title` AS `rTitle`,`r`.`UpdatedAt` AS `rUpdatedAt`,`b`.`Id` AS `bid`,`b`.`ConsignmentCode` AS `bcosign`,`b`.`Title` AS `bTitle`,`b`.`Number` AS `bNumber`,`n`.`RowId` AS `nid`,`n`.`noteList` AS `nTexts`,`l`.`Id` AS `lid`,`l`.`Name` AS `lName`,`rt`.`Id` AS `rtid`,`rt`.`Name` AS `rtName`,`c`.`classList` AS `classList`,`rs`.`Id` AS `rsid`,`rs`.`Name` AS `rsName`,`rsc`.`Id` AS `rscid`,`rsc`.`Name` AS `rscName` from (((((((`recordr`.`records` `r` left join `recordr`.`containers` `b` on((`r`.`ContainerId` = `b`.`Id`))) left join (select `no`.`RowId` AS `RowId`,group_concat(`no`.`Text` separator ';;--;;') AS `noteList` from `recordr`.`notes` `no` group by `no`.`RowId`) `n` on((`n`.`RowId` = `r`.`Id`))) left join `recordr`.`recordtypes` `rt` on((`rt`.`Id` = `r`.`TypeId`))) left join `recordr`.`locations` `l` on((`r`.`LocationId` = `l`.`Id`))) left join (select `rc`.`RecordId` AS `RecordId`,group_concat(`c`.`Name` separator ';;--;;') AS `classList` from (`recordr`.`recordclassifications` `rc` left join `recordr`.`classifications` `c` on((`rc`.`ClassId` = `c`.`Id`))) group by `rc`.`RecordId`) `c` on((`r`.`Id` = `c`.`RecordId`))) left join `recordr`.`recordstates` `rs` on((`rs`.`Id` = `r`.`StateId`))) left join `recordr`.`retentionschedules` `rsc` on((`rsc`.`Id` = `r`.`ScheduleId`)));
44
45SET SQL_MODE=@OLD_SQL_MODE;
46SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
47SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;