spade-fork/db/spade-views.sql @ 20f290be

-- phpMyAdmin SQL Dump
-- version 5.0.4deb2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: May 10, 2022 at 05:24 PM
-- Server version: 10.5.15-MariaDB-0+deb11u1
-- PHP Version: 7.4.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;


/*!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 */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `spade`
--
USE `spade`;

-- --------------------------------------------------------

--
-- Stand-in structure for view `artifactView`
-- (See below for the actual view)
--
CREATE TABLE `artifactView` (
`name` varchar(255)
,`description` longtext
,`created` datetime
,`url` varchar(255)
,`id` bigint(20)
,`artifactClass` varchar(255)
,`mimeType` varchar(255)
,`size` bigint(20)
,`authorId` bigint(20)
,`authorName` varchar(255)
,`projectId` bigint(20)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `commitedConfigView`
-- (See below for the actual view)
--
CREATE TABLE `commitedConfigView` (
`id` bigint(20)
,`type` varchar(31)
,`name` varchar(255)
,`description` longtext
,`created` datetime
,`authorId` bigint(20)
,`authorName` varchar(255)
,`relationName` varchar(255)
,`relatedId` bigint(20)
,`relatedName` varchar(255)
,`projectId` bigint(20)
,`committed` datetime
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `commitView`
-- (See below for the actual view)
--
CREATE TABLE `commitView` (
`id` bigint(20)
,`type` varchar(31)
,`name` varchar(255)
,`description` longtext
,`created` datetime
,`authorId` bigint(20)
,`authorName` varchar(255)
,`relationName` varchar(255)
,`relatedId` bigint(20)
,`relatedName` varchar(255)
,`projectId` bigint(20)
,`committed` datetime
,`isRelease` bit(1)
,`tag` varchar(255)
,`branch` varchar(255)
,`main` bit(1)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `configurationView`
-- (See below for the actual view)
--
CREATE TABLE `configurationView` (
`id` bigint(20)
,`type` varchar(31)
,`name` varchar(255)
,`description` longtext
,`created` datetime
,`authorId` bigint(20)
,`authorName` varchar(255)
,`relationName` varchar(255)
,`relatedId` bigint(20)
,`relatedName` varchar(255)
,`projectId` bigint(20)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `fieldChangeView`
-- (See below for the actual view)
--
CREATE TABLE `fieldChangeView` (
`id` bigint(20)
,`type` varchar(31)
,`name` varchar(255)
,`description` longtext
,`created` datetime
,`authorId` bigint(20)
,`authorName` varchar(255)
,`relationName` varchar(255)
,`relatedId` bigint(20)
,`relatedName` varchar(255)
,`projectId` bigint(20)
,`changeName` varchar(255)
,`changeDesc` longtext
,`itemId` bigint(20)
,`itemType` varchar(31)
,`itemName` varchar(255)
,`itemDesc` longtext
,`itemCreated` datetime
,`field` varchar(255)
,`newValue` longtext
,`oldValue` longtext
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `personView`
-- (See below for the actual view)
--
CREATE TABLE `personView` (
`id` bigint(20)
,`name` varchar(255)
,`projectId` bigint(20)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `personWithRolesView`
-- (See below for the actual view)
--
CREATE TABLE `personWithRolesView` (
`id` bigint(20)
,`name` varchar(255)
,`role` varchar(255)
,`roleClass` varchar(255)
,`roleSuperClass` varchar(255)
,`projectId` bigint(20)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `workUnitView`
-- (See below for the actual view)
--
CREATE TABLE `workUnitView` (
`id` bigint(20)
,`name` varchar(255)
,`description` longtext
,`created` datetime
,`dueDate` date
,`estimatedTime` double
,`progress` int(11)
,`spentTime` double
,`startDate` date
,`projectId` bigint(20)
,`authorId` bigint(20)
,`authorName` varchar(255)
,`assigneeId` bigint(20)
,`assigneeName` varchar(255)
,`activityName` varchar(255)
,`activityDesc` longtext
,`activityEndDate` date
,`activityStartDate` date
,`iterationName` varchar(255)
,`iterationDesc` longtext
,`iterationStartDate` date
,`iterationEndDate` date
,`iterationCreated` datetime
,`phaseName` varchar(255)
,`phaseDesc` longtext
,`phaseStartDate` date
,`phaseEndDate` date
,`phaseCreated` datetime
,`priorityName` varchar(255)
,`priorityDesc` longtext
,`prioClass` varchar(255)
,`prioSuperClass` varchar(255)
,`severityName` varchar(255)
,`severityDesc` longtext
,`severityClass` varchar(255)
,`severitySuperClass` varchar(255)
,`resolutionName` varchar(255)
,`resolutionDescription` longtext
,`resolutionClass` varchar(255)
,`resolutionSuperClass` varchar(255)
,`statusName` varchar(255)
,`statusDescription` longtext
,`statusClass` varchar(255)
,`statusSuperClass` varchar(255)
,`wuTypeName` varchar(255)
,`wuTypeDescription` longtext
,`wuTypeClass` varchar(255)
,`categoryName` varchar(255)
,`categoryDesc` longtext
);

-- --------------------------------------------------------

--
-- Structure for view `artifactView`
--
DROP TABLE IF EXISTS `artifactView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `artifactView`  AS SELECT `wi`.`name` AS `name`, `wi`.`description` AS `description`, `wi`.`created` AS `created`, `wi`.`url` AS `url`, `a`.`id` AS `id`, `a`.`artifactClass` AS `artifactClass`, `a`.`mimeType` AS `mimeType`, `a`.`size` AS `size`, `p`.`id` AS `authorId`, `p`.`name` AS `authorName`, `p`.`projectId` AS `projectId` FROM ((`work_item` `wi` join `artifact` `a` on(`a`.`id` = `wi`.`id`)) join `personView` `p` on(`p`.`id` = `wi`.`authorId`)) ;

-- --------------------------------------------------------

--
-- Structure for view `commitedConfigView`
--
DROP TABLE IF EXISTS `commitedConfigView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `commitedConfigView`  AS SELECT `c`.`id` AS `id`, `c`.`type` AS `type`, `c`.`name` AS `name`, `c`.`description` AS `description`, `c`.`created` AS `created`, `c`.`authorId` AS `authorId`, `c`.`authorName` AS `authorName`, `c`.`relationName` AS `relationName`, `c`.`relatedId` AS `relatedId`, `c`.`relatedName` AS `relatedName`, `c`.`projectId` AS `projectId`, `cc`.`committed` AS `committed` FROM (`configurationView` `c` join `committed_configuration` `cc` on(`c`.`id` = `cc`.`id`)) ;

-- --------------------------------------------------------

--
-- Structure for view `commitView`
--
DROP TABLE IF EXISTS `commitView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `commitView`  AS SELECT `cc`.`id` AS `id`, `cc`.`type` AS `type`, `cc`.`name` AS `name`, `cc`.`description` AS `description`, `cc`.`created` AS `created`, `cc`.`authorId` AS `authorId`, `cc`.`authorName` AS `authorName`, `cc`.`relationName` AS `relationName`, `cc`.`relatedId` AS `relatedId`, `cc`.`relatedName` AS `relatedName`, `cc`.`projectId` AS `projectId`, `cc`.`committed` AS `committed`, `cm`.`isRelease` AS `isRelease`, `tg`.`name` AS `tag`, `br`.`name` AS `branch`, `br`.`isMain` AS `main` FROM ((((`commitedConfigView` `cc` join `commit` `cm` on(`cc`.`id` = `cm`.`id`)) left join `tag` `tg` on(`cm`.`id` = `tg`.`configurationId`)) join `configuration_branch` `cfbr` on(`cfbr`.`configurationId` = `cm`.`id`)) join `branch` `br` on(`br`.`id` = `cfbr`.`branchId`)) ;

-- --------------------------------------------------------

--
-- Structure for view `configurationView`
--
DROP TABLE IF EXISTS `configurationView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `configurationView`  AS SELECT `wi`.`id` AS `id`, `wi`.`workItemType` AS `type`, `wi`.`name` AS `name`, `wi`.`description` AS `description`, `wi`.`created` AS `created`, `author`.`id` AS `authorId`, `author`.`name` AS `authorName`, `cpr`.`name` AS `relationName`, `related`.`id` AS `relatedId`, `related`.`name` AS `relatedName`, `c`.`projectId` AS `projectId` FROM ((((`work_item` `wi` join `configuration` `c` on(`c`.`id` = `wi`.`id`)) join `personView` `author` on(`author`.`id` = `wi`.`authorId`)) left join `configuration_person_relation` `cpr` on(`cpr`.`configurationId` = `c`.`id`)) left join `personView` `related` on(`cpr`.`personId` = `related`.`id`)) ;

-- --------------------------------------------------------

--
-- Structure for view `fieldChangeView`
--
DROP TABLE IF EXISTS `fieldChangeView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `fieldChangeView`  AS SELECT `conf`.`id` AS `id`, `conf`.`type` AS `type`, `conf`.`name` AS `name`, `conf`.`description` AS `description`, `conf`.`created` AS `created`, `conf`.`authorId` AS `authorId`, `conf`.`authorName` AS `authorName`, `conf`.`relationName` AS `relationName`, `conf`.`relatedId` AS `relatedId`, `conf`.`relatedName` AS `relatedName`, `conf`.`projectId` AS `projectId`, `wchange`.`name` AS `changeName`, `wchange`.`description` AS `changeDesc`, `item`.`id` AS `itemId`, `item`.`workItemType` AS `itemType`, `item`.`name` AS `itemName`, `item`.`description` AS `itemDesc`, `item`.`created` AS `itemCreated`, `fc`.`name` AS `field`, `fc`.`newValue` AS `newValue`, `fc`.`oldValue` AS `oldValue` FROM ((((`configurationView` `conf` left join `configuration_change` `cfc` on(`cfc`.`configurationId` = `conf`.`id`)) left join `work_item_change` `wchange` on(`wchange`.`id` = `cfc`.`changeId`)) left join `field_change` `fc` on(`fc`.`workItemChangeId` = `wchange`.`id`)) join `work_item` `item` on(`wchange`.`workItemId` = `item`.`id`)) ;

-- --------------------------------------------------------

--
-- Structure for view `personView`
--
DROP TABLE IF EXISTS `personView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `personView`  AS SELECT `p`.`id` AS `id`, `p`.`name` AS `name`, `p`.`projectId` AS `projectId` FROM `person` AS `p` ;

-- --------------------------------------------------------

--
-- Structure for view `personWithRolesView`
--
DROP TABLE IF EXISTS `personWithRolesView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `personWithRolesView`  AS SELECT `p`.`id` AS `id`, `p`.`name` AS `name`, `r`.`name` AS `role`, `rc`.`class` AS `roleClass`, `rc`.`superClass` AS `roleSuperClass`, `p`.`projectId` AS `projectId` FROM (((`person` `p` join `person_role` `pr` on(`pr`.`personId` = `p`.`id`)) join `role` `r` on(`r`.`id` = `pr`.`roleId`)) join `role_classification` `rc` on(`rc`.`id` = `r`.`classId`)) ;

-- --------------------------------------------------------

--
-- Structure for view `workUnitView`
--
DROP TABLE IF EXISTS `workUnitView`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `workUnitView`  AS SELECT `wi`.`id` AS `id`, `wi`.`name` AS `name`, `wi`.`description` AS `description`, `wi`.`created` AS `created`, `wu`.`dueDate` AS `dueDate`, `wu`.`estimatedTime` AS `estimatedTime`, `wu`.`progress` AS `progress`, `wu`.`spentTime` AS `spentTime`, `wu`.`startDate` AS `startDate`, `wu`.`projectId` AS `projectId`, `author`.`id` AS `authorId`, `author`.`name` AS `authorName`, `assignee`.`id` AS `assigneeId`, `assignee`.`name` AS `assigneeName`, `ac`.`name` AS `activityName`, `ac`.`description` AS `activityDesc`, `ac`.`endDate` AS `activityEndDate`, `ac`.`startDate` AS `activityStartDate`, `it`.`name` AS `iterationName`, `it`.`description` AS `iterationDesc`, `it`.`startDate` AS `iterationStartDate`, `it`.`endDate` AS `iterationEndDate`, `it`.`created` AS `iterationCreated`, `ph`.`name` AS `phaseName`, `ph`.`description` AS `phaseDesc`, `ph`.`startDate` AS `phaseStartDate`, `ph`.`endDate` AS `phaseEndDate`, `ph`.`created` AS `phaseCreated`, `pr`.`name` AS `priorityName`, `pr`.`description` AS `priorityDesc`, `prcl`.`class` AS `prioClass`, `prcl`.`superClass` AS `prioSuperClass`, `ser`.`name` AS `severityName`, `ser`.`description` AS `severityDesc`, `sercl`.`class` AS `severityClass`, `sercl`.`superClass` AS `severitySuperClass`, `res`.`name` AS `resolutionName`, `res`.`description` AS `resolutionDescription`, `rescl`.`class` AS `resolutionClass`, `rescl`.`superClass` AS `resolutionSuperClass`, `st`.`name` AS `statusName`, `st`.`description` AS `statusDescription`, `stcl`.`class` AS `statusClass`, `stcl`.`superClass` AS `statusSuperClass`, `wt`.`name` AS `wuTypeName`, `wt`.`description` AS `wuTypeDescription`, `wtcl`.`class` AS `wuTypeClass`, `ct`.`name` AS `categoryName`, `ct`.`description` AS `categoryDesc` FROM ((((((((((((((((((`work_item` `wi` join `work_unit` `wu` on(`wu`.`id` = `wi`.`id`)) join `personView` `author` on(`wi`.`authorId` = `author`.`id`)) join `personView` `assignee` on(`assignee`.`id` = `wu`.`assigneeId`)) left join `activity` `ac` on(`wu`.`activityId` = `ac`.`id`)) left join `iteration` `it` on(`wu`.`iterationId` = `it`.`id`)) left join `phase` `ph` on(`wu`.`phaseId` = `ph`.`id`)) join `priority` `pr` on(`pr`.`id` = `wu`.`priorityId`)) join `priority_classification` `prcl` on(`prcl`.`id` = `pr`.`classId`)) join `severity` `ser` on(`ser`.`id` = `wu`.`severityId`)) join `severity_classification` `sercl` on(`sercl`.`id` = `ser`.`classId`)) left join `resolution` `res` on(`res`.`id` = `wu`.`resolutionId`)) left join `resolution_classification` `rescl` on(`rescl`.`id` = `res`.`classId`)) join `status` `st` on(`st`.`id` = `wu`.`statusId`)) join `status_classification` `stcl` on(`stcl`.`id` = `st`.`classId`)) join `wu_type` `wt` on(`wt`.`id` = `wu`.`wuTypeId`)) join `wu_type_classification` `wtcl` on(`wtcl`.`id` = `wt`.`classId`)) left join `work_unit_category` `wuc` on(`wuc`.`workUnitId` = `wu`.`id`)) left join `category` `ct` on(`ct`.`id` = `wuc`.`categoryId`)) ;
COMMIT;

/*!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 */;