-- version 4.7.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: 06-Jan-2020 às 22:15
-- Versão do servidor: 5.6.34
-- PHP Version: 7.1.11
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Database: `espacocidadao`
--
-- --------------------------------------------------------
--
-- Estrutura da tabela `entidades`
--
CREATE TABLE `entidades` (
`id` int(11) NOT NULL,
`entidade` varchar(100) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '1',
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `entidades`
--
INSERT INTO `entidades` (`id`, `entidade`, `estado`, `dataReg`) VALUES
(1, 'NOS', 1, '2020-01-06 20:18:25'),
(2, 'VODAFONE', 1, '2020-01-06 20:18:25'),
(3, 'GALP', 1, '2020-01-06 20:18:38'),
(4, 'WORTEN', 0, '2020-01-06 20:18:38');
-- --------------------------------------------------------
--
-- Estrutura da tabela `entidades_servicos`
--
CREATE TABLE `entidades_servicos` (
`id` int(11) NOT NULL,
`idEntidade` int(11) NOT NULL,
`idServico` int(11) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '1',
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `entidades_servicos`
--
INSERT INTO `entidades_servicos` (`id`, `idEntidade`, `idServico`, `estado`, `dataReg`) VALUES
(1, 1, 1, 1, '2020-01-06 21:09:51'),
(2, 1, 2, 1, '2020-01-06 21:09:51'),
(3, 1, 3, 1, '2020-01-06 21:42:16'),
(4, 2, 1, 1, '2020-01-06 21:43:32'),
(5, 2, 2, 1, '2020-01-06 21:43:32'),
(6, 3, 1, 1, '2020-01-06 21:43:55'),
(7, 3, 2, 1, '2020-01-06 21:43:55'),
(8, 4, 4, 0, '2020-01-06 21:44:03');
-- --------------------------------------------------------
--
-- Estrutura da tabela `estados_servicos_users`
--
CREATE TABLE `estados_servicos_users` (
`id` int(11) NOT NULL,
`idEntidadeServico` int(11) NOT NULL,
`idUser` int(11) NOT NULL,
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`estado` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `estados_servicos_users`
--
INSERT INTO `estados_servicos_users` (`id`, `idEntidadeServico`, `idUser`, `dataReg`, `estado`) VALUES
(1, 1, 5, '2020-01-06 21:53:57', 1),
(2, 2, 5, '2020-01-06 21:53:57', 1);
-- --------------------------------------------------------
--
-- Estrutura da tabela `passwords`
--
CREATE TABLE `passwords` (
`id` int(11) NOT NULL,
`psw` varchar(100) NOT NULL,
`idUser` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `passwords`
--
INSERT INTO `passwords` (`id`, `psw`, `idUser`) VALUES
(1, '123', 1),
(2, '456', 1),
(3, '789', 2),
(4, '987', 3),
(5, '654', 3),
(6, '321', 4),
(7, '555', 2),
(8, '789', 9),
(9, '934', 10),
(10, '123', 5);
-- --------------------------------------------------------
--
-- Estrutura da tabela `privilegios`
--
CREATE TABLE `privilegios` (
`id` int(11) NOT NULL,
`privilegio` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `privilegios`
--
INSERT INTO `privilegios` (`id`, `privilegio`) VALUES
(1, 'admin'),
(2, 'programador'),
(3, 'user'),
(4, 'cliente');
-- --------------------------------------------------------
--
-- Estrutura da tabela `servicos`
--
CREATE TABLE `servicos` (
`id` int(11) NOT NULL,
`servico` varchar(100) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '1',
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `servicos`
--
INSERT INTO `servicos` (`id`, `servico`, `estado`, `dataReg`) VALUES
(1, 'Informações', 1, '2020-01-06 20:23:17'),
(2, 'Pagamentos', 1, '2020-01-06 20:23:17'),
(3, 'Contratos', 1, '2020-01-06 20:23:33'),
(4, 'Entregas', 0, '2020-01-06 20:23:33');
-- --------------------------------------------------------
--
-- Estrutura da tabela `sistema`
--
CREATE TABLE `sistema` (
`id` int(11) NOT NULL,
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`estado` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `sistema`
--
INSERT INTO `sistema` (`id`, `dataReg`, `estado`) VALUES
(1, '2019-12-02 21:09:14', 0),
(2, '2019-12-02 21:09:22', 1),
(3, '2019-12-02 21:46:20', 0),
(4, '2019-12-02 22:21:24', 1),
(5, '2019-12-02 22:39:33', 0),
(6, '2019-12-02 22:39:35', 1),
(7, '2019-12-02 22:39:36', 0),
(8, '2019-12-02 22:39:37', 1),
(9, '2019-12-02 22:39:38', 0),
(10, '2019-12-02 22:39:38', 1),
(11, '2019-12-02 22:39:38', 0),
(12, '2019-12-02 22:39:38', 1),
(13, '2019-12-02 22:39:39', 0),
(14, '2019-12-02 22:39:39', 0),
(15, '2019-12-02 22:39:39', 1),
(16, '2019-12-02 22:39:39', 0),
(17, '2019-12-02 22:39:40', 1),
(18, '2019-12-02 22:39:40', 0),
(19, '2019-12-02 22:39:40', 1),
(20, '2019-12-02 22:39:41', 0),
(21, '2019-12-06 21:32:37', 1),
(22, '2019-12-06 21:32:37', 0),
(23, '2019-12-06 21:32:38', 1),
(24, '2019-12-06 21:32:38', 0),
(25, '2019-12-06 21:32:38', 1);
-- --------------------------------------------------------
--
-- Estrutura da tabela `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(100) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '0',
`dataReg` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `users`
--
INSERT INTO `users` (`id`, `username`, `estado`, `dataReg`) VALUES
(1, 'marco@mail.pt', 0, '2019-11-19 21:52:03'),
(2, 'marcelo@mail.pt', 1, '2019-11-19 21:52:03'),
(3, 'ze@mail.pt', 1, '2019-11-19 21:52:24'),
(4, 'telma@mail.pt', 0, '2019-11-19 21:52:37'),
(5, 'adalberto', 0, '2019-12-06 22:24:41'),
(6, 'pateta', 0, '2019-12-06 22:27:48'),
(7, 'mickey', 0, '2019-12-06 22:30:46'),
(8, 'pluto', 0, '2019-12-06 22:32:00'),
(9, 'margarida', 0, '2019-12-06 22:38:03'),
(10, 'elsa', 0, '2019-12-06 22:41:06');
-- --------------------------------------------------------
--
-- Estrutura da tabela `users_privilegios`
--
CREATE TABLE `users_privilegios` (
`id` int(11) NOT NULL,
`idUser` int(11) NOT NULL,
`idPrivilegio` int(11) NOT NULL,
`dataReg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`estado` tinyint(1) NOT NULL DEFAULT '1',
`idOperador` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `users_privilegios`
--
INSERT INTO `users_privilegios` (`id`, `idUser`, `idPrivilegio`, `dataReg`, `estado`, `idOperador`) VALUES
(1, 2, 3, '2019-11-25 20:14:36', 1, 3),
(2, 1, 3, '2019-11-25 20:14:36', 1, 3),
(3, 1, 2, '2019-11-25 20:19:43', 1, 3),
(4, 4, 1, '2019-11-25 20:19:43', 1, 3),
(5, 3, 1, '2019-11-25 20:20:08', 1, 3),
(7, 10, 4, '2019-12-06 22:41:07', 1, 3),
(8, 5, 4, '2020-01-06 21:59:21', 1, 1);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_entidades_servicos`
-- (See below for the actual view)
--
CREATE TABLE `v1_entidades_servicos` (
`idEntidadeServico` int(11)
,`idEntidade` int(11)
,`entidade` varchar(100)
,`estadoE` tinyint(1)
,`idServico` int(11)
,`servico` varchar(100)
,`estadoS` tinyint(1)
,`estadoES` tinyint(1)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_passwords`
-- (See below for the actual view)
--
CREATE TABLE `v1_passwords` (
`id` int(11)
,`psw` varchar(100)
,`idUser` int(11)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_sistema`
-- (See below for the actual view)
--
CREATE TABLE `v1_sistema` (
`estado` tinyint(1)
,`id` int(11)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_total_users_inativos`
-- (See below for the actual view)
--
CREATE TABLE `v1_total_users_inativos` (
`total` bigint(21)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v2_users_passwords`
-- (See below for the actual view)
--
CREATE TABLE `v2_users_passwords` (
`idUser` int(11)
,`username` varchar(100)
,`psw` varchar(100)
,`usersEstado` tinyint(1)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v2_users_passwords_privilegios`
-- (See below for the actual view)
--
CREATE TABLE `v2_users_passwords_privilegios` (
`idUser` int(11)
,`username` varchar(100)
,`privilegio` varchar(100)
,`idPrivilegio` int(11)
,`psw` varchar(100)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v3_login`
-- (See below for the actual view)
--
CREATE TABLE `v3_login` (
`idUser` int(11)
,`username` varchar(100)
,`psw` varchar(100)
,`usersEstado` tinyint(1)
);
-- --------------------------------------------------------
--
-- Structure for view `v1_entidades_servicos`
--
DROP TABLE IF EXISTS `v1_entidades_servicos`;
CREATE ALGORITHM=UNDEFINED DEFINER=`manel`@`localhost` SQL SECURITY DEFINER VIEW `v1_entidades_servicos` AS select `entidades_servicos`.`id` AS `idEntidadeServico`,`entidades_servicos`.`idEntidade` AS `idEntidade`,`entidades`.`entidade` AS `entidade`,`entidades`.`estado` AS `estadoE`,`entidades_servicos`.`idServico` AS `idServico`,`servicos`.`servico` AS `servico`,`servicos`.`estado` AS `estadoS`,`entidades_servicos`.`estado` AS `estadoES` from ((`entidades` join `entidades_servicos`) join `servicos`) where ((`entidades`.`id` = `entidades_servicos`.`idEntidade`) and (`servicos`.`id` = `entidades_servicos`.`idServico`)) ;
-- --------------------------------------------------------
--
-- Structure for view `v1_passwords`
--
DROP TABLE IF EXISTS `v1_passwords`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_passwords` AS select `passwords`.`id` AS `id`,`passwords`.`psw` AS `psw`,`passwords`.`idUser` AS `idUser` from `passwords` where `passwords`.`id` in (select max(`passwords`.`id`) from `passwords` group by `passwords`.`idUser`) ;
-- --------------------------------------------------------
--
-- Structure for view `v1_sistema`
--
DROP TABLE IF EXISTS `v1_sistema`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_sistema` AS select `sistema`.`estado` AS `estado`,`sistema`.`id` AS `id` from `sistema` where `sistema`.`id` in (select max(`sistema`.`id`) from `sistema`) ;
-- --------------------------------------------------------
--
-- Structure for view `v1_total_users_inativos`
--
DROP TABLE IF EXISTS `v1_total_users_inativos`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_total_users_inativos` AS select count(0) AS `total` from `users` where (`users`.`estado` = 0) ;
-- --------------------------------------------------------
--
-- Structure for view `v2_users_passwords`
--
DROP TABLE IF EXISTS `v2_users_passwords`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2_users_passwords` AS select `users`.`id` AS `idUser`,`users`.`username` AS `username`,`v1_passwords`.`psw` AS `psw`,`users`.`estado` AS `usersEstado` from (`users` join `v1_passwords`) where (`users`.`id` = `v1_passwords`.`idUser`) ;
-- --------------------------------------------------------
--
-- Structure for view `v2_users_passwords_privilegios`
--
DROP TABLE IF EXISTS `v2_users_passwords_privilegios`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2_users_passwords_privilegios` AS select `users`.`id` AS `idUser`,`users`.`username` AS `username`,`privilegios`.`privilegio` AS `privilegio`,`users_privilegios`.`idPrivilegio` AS `idPrivilegio`,`v1_passwords`.`psw` AS `psw` from (((`users` join `privilegios`) join `users_privilegios`) join `v1_passwords`) where ((`users`.`id` = `users_privilegios`.`idUser`) and (`users`.`id` = `v1_passwords`.`idUser`) and (`privilegios`.`id` = `users_privilegios`.`idPrivilegio`)) ;
-- --------------------------------------------------------
--
-- Structure for view `v3_login`
--
DROP TABLE IF EXISTS `v3_login`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3_login` AS select `v2_users_passwords`.`idUser` AS `idUser`,`v2_users_passwords`.`username` AS `username`,`v2_users_passwords`.`psw` AS `psw`,`v2_users_passwords`.`usersEstado` AS `usersEstado` from `v2_users_passwords` where (`v2_users_passwords`.`usersEstado` = 1) ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `entidades`
--
ALTER TABLE `entidades`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `entidades_servicos`
--
ALTER TABLE `entidades_servicos`
ADD PRIMARY KEY (`id`),
ADD KEY `idEntidade` (`idEntidade`),
ADD KEY `idServico` (`idServico`);
--
-- Indexes for table `estados_servicos_users`
--
ALTER TABLE `estados_servicos_users`
ADD PRIMARY KEY (`id`),
ADD KEY `idEntidadeServico` (`idEntidadeServico`),
ADD KEY `idUser` (`idUser`);
--
-- Indexes for table `passwords`
--
ALTER TABLE `passwords`
ADD PRIMARY KEY (`id`),
ADD KEY `idUser` (`idUser`);
--
-- Indexes for table `privilegios`
--
ALTER TABLE `privilegios`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `servicos`
--
ALTER TABLE `servicos`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `sistema`
--
ALTER TABLE `sistema`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `users_privilegios`
--
ALTER TABLE `users_privilegios`
ADD PRIMARY KEY (`id`),
ADD KEY `idUser` (`idUser`),
ADD KEY `idPrivilegio` (`idPrivilegio`),
ADD KEY `idOperador` (`idOperador`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `entidades`
--
ALTER TABLE `entidades`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `entidades_servicos`
--
ALTER TABLE `entidades_servicos`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `estados_servicos_users`
--
ALTER TABLE `estados_servicos_users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `passwords`
--
ALTER TABLE `passwords`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `privilegios`
--
ALTER TABLE `privilegios`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `servicos`
--
ALTER TABLE `servicos`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `sistema`
--
ALTER TABLE `sistema`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `users_privilegios`
--
ALTER TABLE `users_privilegios`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- Constraints for dumped tables
--
--
-- Limitadores para a tabela `entidades_servicos`
--
ALTER TABLE `entidades_servicos`
ADD CONSTRAINT `entidades_servicos_ibfk_1` FOREIGN KEY (`idEntidade`) REFERENCES `entidades` (`id`),
ADD CONSTRAINT `entidades_servicos_ibfk_2` FOREIGN KEY (`idServico`) REFERENCES `servicos` (`id`);
--
-- Limitadores para a tabela `estados_servicos_users`
--
ALTER TABLE `estados_servicos_users`
ADD CONSTRAINT `estados_servicos_users_ibfk_1` FOREIGN KEY (`idEntidadeServico`) REFERENCES `entidades_servicos` (`id`),
ADD CONSTRAINT `estados_servicos_users_ibfk_2` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`);
--
-- Limitadores para a tabela `passwords`
--
ALTER TABLE `passwords`
ADD CONSTRAINT `passwords_ibfk_1` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Limitadores para a tabela `users_privilegios`
--
ALTER TABLE `users_privilegios`
ADD CONSTRAINT `users_privilegios_ibfk_1` FOREIGN KEY (`idOperador`) REFERENCES `users` (`id`),
ADD CONSTRAINT `users_privilegios_ibfk_2` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`),
ADD CONSTRAINT `users_privilegios_ibfk_3` FOREIGN KEY (`idPrivilegio`) REFERENCES `privilegios` (`id`);
COMMIT;
Sem comentários:
Enviar um comentário