-- version 4.7.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: 03-Jul-2019 às 18:37
-- 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: `oficina`
--
DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_passwords` (IN `senha` VARCHAR(100)) NO SQL
BEGIN
INSERT INTO passwords (pass) VALUES (senha);
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_users` (IN `usern` VARCHAR(100)) NO SQL
BEGIN
INSERT INTO users (username) VALUES (usern);
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_users_passwords` (IN `usern` VARCHAR(100), IN `senha` VARCHAR(100)) NO SQL
BEGIN
INSERT INTO users (username) VALUES (usern);
SET @idUser=LAST_INSERT_ID();
INSERT INTO passwords (pass) VALUES (senha);
SET @idPassword=LAST_INSERT_ID();
INSERT INTO users_passwords (idUser, idPassword)
VALUES (@idUser, @idPassword);
rollback;
END$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Estrutura da tabela `funcoes`
--
CREATE TABLE `funcoes` (
`id` tinyint(4) NOT NULL,
`funcao` varchar(100) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `funcoes`
--
INSERT INTO `funcoes` (`id`, `funcao`, `estado`) VALUES
(1, 'Orçamentista', 1),
(2, 'Recepcionista', 0),
(3, 'Hardware', 1),
(4, 'Software', 1),
(5, 'Gerente', 0);
-- --------------------------------------------------------
--
-- Estrutura da tabela `passwords`
--
CREATE TABLE `passwords` (
`id` tinyint(4) NOT NULL,
`pass` varchar(100) NOT NULL,
`data_atual` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `passwords`
--
INSERT INTO `passwords` (`id`, `pass`, `data_atual`) VALUES
(1, 'mloureiro pass', '2019-06-19 18:22:46'),
(2, 'pass da liliana', '2019-06-19 18:35:23'),
(3, 'xpto', '2019-06-26 18:34:49'),
(4, 'senha', '2019-06-26 18:59:09'),
(5, 'senha', '2019-06-26 19:00:21'),
(6, '987', '2019-06-26 19:01:41'),
(7, '123', '2019-06-26 19:23:48'),
(8, '777', '2019-06-26 19:25:33'),
(9, '111', '2019-06-26 19:30:23'),
(10, 'qqq', '2019-06-26 19:31:57'),
(11, 'zxc', '2019-06-26 19:35:56'),
(12, '123', '2019-06-26 21:32:54'),
(13, '123', '2019-06-26 21:33:26'),
(14, '123', '2019-06-26 21:35:11'),
(15, '123', '2019-06-26 21:35:12'),
(16, '123', '2019-06-26 21:35:12'),
(17, '123', '2019-06-26 21:35:12'),
(18, '123', '2019-06-26 21:35:12'),
(19, '123', '2019-06-26 21:35:12'),
(20, '123', '2019-06-26 21:35:12'),
(21, '123', '2019-06-26 21:35:12'),
(22, '123', '2019-06-26 21:35:12'),
(23, '123', '2019-06-26 21:35:12'),
(24, '123', '2019-06-26 21:35:12');
-- --------------------------------------------------------
--
-- Estrutura da tabela `pessoas`
--
CREATE TABLE `pessoas` (
`id` tinyint(4) NOT NULL,
`nome` varchar(100) NOT NULL,
`morada` varchar(100) NOT NULL,
`telefone` varchar(100) NOT NULL,
`nif` varchar(100) NOT NULL,
`data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`estado` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='associar à tabela users_funcoes_pessoas';
--
-- Extraindo dados da tabela `pessoas`
--
INSERT INTO `pessoas` (`id`, `nome`, `morada`, `telefone`, `nif`, `data`, `estado`) VALUES
(1, 'Sérgio Orçamentista', 'Morada do Sérgio', 'Telefone do Sérgio', 'NIF do Sérgio', '2019-06-26 21:17:57', 1),
(2, 'Rui Recepcionista', 'Morada Rui', 'Telefone Rui', 'NIF Rui', '2019-06-26 21:23:21', 1),
(3, 'Fábio Hardware', 'Morada Fabio', 'Telefone Fabio', 'NIF Fabio', '2019-06-26 21:30:08', 1),
(4, 'Bruno SOftware', 'Morada Bruno', 'Telefone Bruno', 'NIF Bruno', '2019-06-26 21:30:08', 1),
(5, 'Liliana Gerente', 'Morada Liliana', 'Telefone Liliana', 'NIF Liliana', '2019-06-26 21:30:08', 1),
(6, 'António Gerente', 'Morada António', 'Telefone António', 'NIF António', '2019-06-26 21:30:08', 1),
(7, 'Ricardo Software', 'Morada Ricardo', 'Telefone Ricardo', 'NIF Ricardo', '2019-06-26 21:30:08', 1),
(8, 'Joao SOftware', 'Morada Joao', 'Telefone Joao', 'NIF Joao', '2019-06-26 21:30:08', 1),
(9, 'Jorge Gerente', 'Morada Jorge', 'Telefone Jorge', 'NIF Jorge', '2019-06-26 21:30:09', 1),
(10, 'Pedro Hardware', 'Morada Pedro', 'Telefone Pedro', 'NIF Pedro', '2019-06-26 21:30:09', 1),
(11, 'BrunoC Orcamentista', 'Morada BrunoC', 'Telefone BrunoC', 'NIF BrunoC', '2019-06-26 21:30:09', 1),
(12, 'Leonel Recpcionista', 'Morada Leonel', 'Telefone Leonel', 'NIF Leonel', '2019-06-26 21:30:09', 1);
-- --------------------------------------------------------
--
-- Estrutura da tabela `users`
--
CREATE TABLE `users` (
`id` tinyint(4) NOT NULL,
`username` varchar(50) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '0',
`data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='estado=0 para confirmar a autenticidade do username';
--
-- Extraindo dados da tabela `users`
--
INSERT INTO `users` (`id`, `username`, `estado`, `data`) VALUES
(18, 'Sergio', 0, '2019-06-26 21:32:54'),
(19, 'Rui', 0, '2019-06-26 21:33:26'),
(20, 'Fabio', 0, '2019-06-26 21:35:11'),
(21, 'BrunoM', 0, '2019-06-26 21:35:12'),
(22, 'Liliana', 0, '2019-06-26 21:35:12'),
(23, 'Joaquim', 0, '2019-06-26 21:35:12'),
(24, 'Antonio', 0, '2019-06-26 21:35:12'),
(25, 'Ricardo', 0, '2019-06-26 21:35:12'),
(26, 'Joao', 0, '2019-06-26 21:35:12'),
(27, 'Jorge', 0, '2019-06-26 21:35:12'),
(28, 'Pedro', 0, '2019-06-26 21:35:12'),
(29, 'BrunoC', 0, '2019-06-26 21:35:12'),
(30, 'Leonel', 0, '2019-06-26 21:35:12');
-- --------------------------------------------------------
--
-- Estrutura da tabela `users_funcoes`
--
CREATE TABLE `users_funcoes` (
`id` tinyint(4) NOT NULL,
`idUser` tinyint(4) NOT NULL,
`idFuncao` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `users_funcoes`
--
INSERT INTO `users_funcoes` (`id`, `idUser`, `idFuncao`) VALUES
(1, 24, 3),
(2, 29, 1),
(3, 21, 4),
(4, 20, 3),
(5, 26, 4),
(6, 23, 1);
-- --------------------------------------------------------
--
-- Estrutura da tabela `users_funcoes_pessoas`
--
CREATE TABLE `users_funcoes_pessoas` (
`id` tinyint(4) NOT NULL,
`idUserFuncao` tinyint(4) NOT NULL,
`idPessoa` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Estrutura da tabela `users_passwords`
--
CREATE TABLE `users_passwords` (
`id` tinyint(4) NOT NULL,
`idUser` tinyint(4) NOT NULL,
`idPassword` tinyint(4) NOT NULL,
`estado` tinyint(1) NOT NULL DEFAULT '1',
`data_atual` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela `users_passwords`
--
INSERT INTO `users_passwords` (`id`, `idUser`, `idPassword`, `estado`, `data_atual`) VALUES
(9, 18, 12, 1, '2019-06-26 21:32:54'),
(10, 19, 13, 1, '2019-06-26 21:33:26'),
(11, 20, 14, 1, '2019-06-26 21:35:11'),
(12, 21, 15, 1, '2019-06-26 21:35:12'),
(13, 22, 16, 1, '2019-06-26 21:35:12'),
(14, 23, 17, 1, '2019-06-26 21:35:12'),
(15, 24, 18, 1, '2019-06-26 21:35:12'),
(16, 25, 19, 1, '2019-06-26 21:35:12'),
(17, 26, 20, 1, '2019-06-26 21:35:12'),
(18, 27, 21, 1, '2019-06-26 21:35:12'),
(19, 28, 22, 1, '2019-06-26 21:35:12'),
(20, 29, 23, 1, '2019-06-26 21:35:12'),
(21, 30, 24, 1, '2019-06-26 21:35:12');
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_funcoes_ativas`
-- (See below for the actual view)
--
CREATE TABLE `v1_funcoes_ativas` (
`idFuncaoAtiva` tinyint(4)
,`funcao` varchar(100)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_funcoes_inativas`
-- (See below for the actual view)
--
CREATE TABLE `v1_funcoes_inativas` (
`idFuncao` tinyint(4)
,`funcao` varchar(100)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v1_login`
-- (See below for the actual view)
--
CREATE TABLE `v1_login` (
`idUser` tinyint(4)
,`username` varchar(50)
,`pass` varchar(100)
);
-- --------------------------------------------------------
--
-- Structure for view `v1_funcoes_ativas`
--
DROP TABLE IF EXISTS `v1_funcoes_ativas`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_funcoes_ativas` AS select `funcoes`.`id` AS `idFuncaoAtiva`,`funcoes`.`funcao` AS `funcao` from `funcoes` where (`funcoes`.`estado` = 1) ;
-- --------------------------------------------------------
--
-- Structure for view `v1_funcoes_inativas`
--
DROP TABLE IF EXISTS `v1_funcoes_inativas`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_funcoes_inativas` AS select `funcoes`.`id` AS `idFuncao`,`funcoes`.`funcao` AS `funcao` from `funcoes` where (`funcoes`.`estado` = 0) ;
-- --------------------------------------------------------
--
-- Structure for view `v1_login`
--
DROP TABLE IF EXISTS `v1_login`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_login` AS select `users_passwords`.`idUser` AS `idUser`,`users`.`username` AS `username`,`passwords`.`pass` AS `pass` from ((`users` join `passwords`) join `users_passwords`) where ((`users`.`id` = `users_passwords`.`idUser`) and (`passwords`.`id` = `users_passwords`.`idPassword`) and (`users_passwords`.`estado` = 1) and (`users`.`estado` = 1)) ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `funcoes`
--
ALTER TABLE `funcoes`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `passwords`
--
ALTER TABLE `passwords`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `pessoas`
--
ALTER TABLE `pessoas`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `users_funcoes`
--
ALTER TABLE `users_funcoes`
ADD PRIMARY KEY (`id`),
ADD KEY `idUser` (`idUser`),
ADD KEY `idFuncao` (`idFuncao`);
--
-- Indexes for table `users_funcoes_pessoas`
--
ALTER TABLE `users_funcoes_pessoas`
ADD PRIMARY KEY (`id`),
ADD KEY `idUserFuncao` (`idUserFuncao`),
ADD KEY `idPessoa` (`idPessoa`);
--
-- Indexes for table `users_passwords`
--
ALTER TABLE `users_passwords`
ADD PRIMARY KEY (`id`),
ADD KEY `idUser` (`idUser`),
ADD KEY `idPassword` (`idPassword`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `funcoes`
--
ALTER TABLE `funcoes`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `passwords`
--
ALTER TABLE `passwords`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
--
-- AUTO_INCREMENT for table `pessoas`
--
ALTER TABLE `pessoas`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;
--
-- AUTO_INCREMENT for table `users_funcoes`
--
ALTER TABLE `users_funcoes`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `users_funcoes_pessoas`
--
ALTER TABLE `users_funcoes_pessoas`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `users_passwords`
--
ALTER TABLE `users_passwords`
MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
--
-- Constraints for dumped tables
--
--
-- Limitadores para a tabela `users_funcoes`
--
ALTER TABLE `users_funcoes`
ADD CONSTRAINT `users_funcoes_ibfk_1` FOREIGN KEY (`idFuncao`) REFERENCES `funcoes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `users_funcoes_ibfk_2` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Limitadores para a tabela `users_funcoes_pessoas`
--
ALTER TABLE `users_funcoes_pessoas`
ADD CONSTRAINT `users_funcoes_pessoas_ibfk_1` FOREIGN KEY (`idPessoa`) REFERENCES `pessoas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `users_funcoes_pessoas_ibfk_2` FOREIGN KEY (`idUserFuncao`) REFERENCES `users_funcoes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Limitadores para a tabela `users_passwords`
--
ALTER TABLE `users_passwords`
ADD CONSTRAINT `users_passwords_ibfk_1` FOREIGN KEY (`idPassword`) REFERENCES `passwords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `users_passwords_ibfk_2` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
Sem comentários:
Enviar um comentário