Created
June 30, 2016 01:40
-
-
Save faogustavo/3f60ce4082b0d7f559cb1e566aeda546 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- phpMyAdmin SQL Dump | |
| -- version 4.5.1 | |
| -- http://www.phpmyadmin.net | |
| -- | |
| -- Host: 127.0.0.1 | |
| -- Generation Time: 30-Jun-2016 às 03:39 | |
| -- Versão do servidor: 10.1.8-MariaDB | |
| -- PHP Version: 5.6.14 | |
| SET FOREIGN_KEY_CHECKS=0; | |
| SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; | |
| SET AUTOCOMMIT = 0; | |
| START TRANSACTION; | |
| SET time_zone = "+00:00"; | |
| /*!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: `trab_banco_2` | |
| -- | |
| CREATE DATABASE IF NOT EXISTS `trab_banco_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; | |
| USE `trab_banco_2`; | |
| DELIMITER $$ | |
| -- | |
| -- Procedures | |
| -- | |
| DROP PROCEDURE IF EXISTS `registrar_endereco`$$ | |
| CREATE DEFINER=`root`@`localhost` PROCEDURE `registrar_endereco` (IN `stateName` VARCHAR(64), IN `stateInitials` CHAR(2), IN `countryName` VARCHAR(64), IN `cityName` VARCHAR(64), IN `districtName` VARCHAR(64), IN `addressCEP` VARCHAR(9), IN `addressNumber` VARCHAR(24), IN `addressStreet` VARCHAR(64), IN `addressComplement` VARCHAR(24), OUT `addressId` INT(11)) BEGIN | |
| DECLARE stateId INT; | |
| DECLARE cityId INT; | |
| DECLARE districtId INT; | |
| IF ( | |
| SELECT EXISTS ( | |
| SELECT 1 FROM state as stt | |
| WHERE stt.name = stateName AND stt.initials = stateInitials AND stt.country = countryName | |
| ) | |
| ) THEN | |
| SELECT stt.id INTO @stateId FROM state as stt | |
| WHERE stt.name = stateName AND stt.initials = stateInitials AND stt.country = countryName; | |
| ELSE | |
| INSERT INTO `state` (`name`, `initials`, `country`, `created_at`, `updated_at`, `state`) | |
| VALUES (stateName, stateInitials, countryName, NOW(), NOW(), 'A'); | |
| SET stateId = last_insert_id(); | |
| END IF; | |
| IF ( | |
| SELECT EXISTS( | |
| SELECT 1 FROM city as c | |
| WHERE c.name = cityName AND c.state_id = @stateId | |
| ) | |
| ) THEN | |
| SELECT c.id INTO @cityId from city as c | |
| WHERE c.name = cityName AND c.state_id = @stateId; | |
| ELSE | |
| INSERT INTO `city` (`name`, `created_at`, `updated_at`, `state`, `state_id`) | |
| VALUES (cityName, NOW(), NOW(), 'A', @stateId); | |
| SET cityId = last_insert_id(); | |
| END IF; | |
| IF ( | |
| SELECT EXISTS( | |
| SELECT 1 FROM district d | |
| WHERE d.name = districtName AND d.city_id = @cityId | |
| ) | |
| ) THEN | |
| SELECT d.id INTO @districtId from district as d | |
| WHERE d.name = districtName AND d.city_id = @cityId; | |
| ELSE | |
| INSERT INTO `district` (`name`, `created_at`, `updated_at`, `state`, `city_id`) | |
| VALUES (districtName, NOW(), NOW(), 'A', @cityId); | |
| END IF; | |
| IF ( | |
| SELECT EXISTS( | |
| SELECT 1 FROM address as a | |
| WHERE a.CEP = addressCEP AND a.number = addressNumber | |
| AND a.street = addressStreet AND a.complement = addressComplement | |
| ) | |
| ) THEN | |
| SELECT a.id INTO addressId FROM address a | |
| WHERE a.CEP = addressCEP AND a.number = addressNumber | |
| AND a.street = addressStreet AND a.complement = addressComplement; | |
| ELSE | |
| INSERT INTO `address` (`CEP`, `number`, `street`, `complement`, `created_at`, `updated_at`, `state`, `district_id`) | |
| VALUES (addressCEP, addressNumber, addressStreet, addressComplement, NOW(), NOW(), 'A', @districtId); | |
| SET addressId = last_insert_id(); | |
| END IF; | |
| END$$ | |
| DROP PROCEDURE IF EXISTS `registrar_revenda`$$ | |
| CREATE DEFINER=`root`@`localhost` PROCEDURE `registrar_revenda` (IN `stateName` VARCHAR(64), IN `stateInitials` CHAR(2), IN `countryName` VARCHAR(64), IN `cityName` VARCHAR(64), IN `districtName` VARCHAR(64), IN `addressCEP` VARCHAR(9), IN `addressNumber` VARCHAR(24), IN `addressStreet` VARCHAR(64), IN `addressComplement` VARCHAR(24), IN `resaleName` VARCHAR(64)) BEGIN | |
| DECLARE addressId INT; | |
| CALL registrar_endereco(stateName, stateInitials, countryName, cityName, | |
| districtName, addressCEP, addressNumber, addressStreet, addressComplement, @addressId); | |
| INSERT INTO `resale` (`name`, `created_at`, `updated_at`, `state`, `address_id`) | |
| VALUES (resaleName, NOW(), NOW(), 'A', @addressId); | |
| END$$ | |
| DROP PROCEDURE IF EXISTS `registrar_usuario`$$ | |
| CREATE DEFINER=`root`@`localhost` PROCEDURE `registrar_usuario` (IN `stateName` VARCHAR(64), IN `stateInitials` CHAR(2), IN `countryName` VARCHAR(64), IN `cityName` VARCHAR(64), IN `districtName` VARCHAR(64), IN `addressCEP` VARCHAR(9), IN `addressNumber` VARCHAR(24), IN `addressStreet` VARCHAR(64), IN `addressComplement` VARCHAR(24), IN `userName` VARCHAR(64), IN `userCPF` VARCHAR(14), IN `userRG` VARCHAR(10), IN `userDataNascimento` DATE, IN `userUsername` VARCHAR(64), IN `userPassword` VARCHAR(255)) BEGIN | |
| DECLARE addressId INT; | |
| CALL registrar_endereco(stateName, stateInitials, countryName, cityName, | |
| districtName, addressCEP, addressNumber, addressStreet, addressComplement, @addressId); | |
| INSERT INTO `user` (`username`, `password`, `name`, `cpf`, `rg`, `birth_date`, `admission_date`, `resignation_date`, `created_at`, `updated_at`, `state`, `address_id`) | |
| VALUES (userUsername, userPassword, userName, userCPF, userRG, userDataNascimento, NOW(), NULL, NOW(), NOW(), 'A', @addressId); | |
| END$$ | |
| DELIMITER ; | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `acess` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `acess`; | |
| CREATE TABLE IF NOT EXISTS `acess` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `acess` | |
| -- | |
| TRUNCATE TABLE `acess`; | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `acess_has_user` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `acess_has_user`; | |
| CREATE TABLE IF NOT EXISTS `acess_has_user` ( | |
| `acess_id` int(11) NOT NULL, | |
| `user_id` int(11) NOT NULL, | |
| `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`acess_id`,`user_id`), | |
| KEY `fk_acess_has_user_user1_idx` (`user_id`), | |
| KEY `fk_acess_has_user_acess_idx` (`acess_id`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `acess_has_user` | |
| -- | |
| TRUNCATE TABLE `acess_has_user`; | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `address` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `address`; | |
| CREATE TABLE IF NOT EXISTS `address` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `CEP` varchar(9) NOT NULL, | |
| `number` varchar(24) NOT NULL, | |
| `street` varchar(64) NOT NULL, | |
| `complement` varchar(24) DEFAULT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| `district_id` int(11) NOT NULL, | |
| PRIMARY KEY (`id`,`district_id`), | |
| KEY `fk_address_district1_idx` (`district_id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `address` | |
| -- | |
| TRUNCATE TABLE `address`; | |
| -- | |
| -- Extraindo dados da tabela `address` | |
| -- | |
| INSERT INTO `address` (`id`, `CEP`, `number`, `street`, `complement`, `created_at`, `updated_at`, `state`, `district_id`) VALUES | |
| (1, '00000-000', '00', 'AdminStreet', 'Admin Ap', '2016-06-09 21:02:18', '2016-06-09 21:02:18', 'A', 1), | |
| (2, '97010-310', '891', 'Rua Marechal Floriano Peixoto', 'apto 202', '2016-06-17 00:19:52', '2016-06-17 00:19:52', 'A', 3), | |
| (3, '97010-310', '891', 'Rua Marechal Floriano Peixoto', 'apto 201', '2016-06-17 00:36:14', '2016-06-17 00:36:14', 'A', 3), | |
| (4, '97010-310', '891', 'Rua Marechal Floriano Peixoto', 'sala 01', '2016-06-22 20:10:07', '2016-06-22 20:10:07', 'A', 3), | |
| (5, '97010-310', '48489', 'Rua Marechal Floriano Peixoto', '98489', '2016-06-24 14:37:49', '2016-06-24 14:37:49', 'A', 3), | |
| (6, '97010-310', '89d4894sa89d4', 'Rua Marechal Floriano Peixoto', 'a8sd94d89as4d', '2016-06-24 16:15:30', '2016-06-24 16:15:30', 'A', 3), | |
| (8, '97650-000', '1391', 'Rodrigues Lima', '', '2016-06-29 22:08:43', '2016-06-29 22:08:43', 'A', 6); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `city` | |
| -- | |
| -- Criação: 17-Jun-2016 às 02:21 | |
| -- | |
| DROP TABLE IF EXISTS `city`; | |
| CREATE TABLE IF NOT EXISTS `city` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| `state_id` int(11) NOT NULL, | |
| PRIMARY KEY (`id`) USING BTREE, | |
| KEY `fk_city_state1_idx` (`state_id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `city` | |
| -- | |
| TRUNCATE TABLE `city`; | |
| -- | |
| -- Extraindo dados da tabela `city` | |
| -- | |
| INSERT INTO `city` (`id`, `name`, `created_at`, `updated_at`, `state`, `state_id`) VALUES | |
| (1, 'AdminCity', '2016-06-09 21:01:11', '2016-06-09 21:01:11', 'A', 1), | |
| (3, 'Santa Maria', '2016-06-17 00:07:21', '2016-06-17 00:07:21', 'A', 8), | |
| (5, 'Itaqui', '2016-06-29 22:07:22', '2016-06-29 22:07:22', 'A', 8), | |
| (6, 'Itaqui', '2016-06-29 22:08:43', '2016-06-29 22:08:43', 'A', 11), | |
| (7, 'Itaqui', '2016-06-29 22:11:36', '2016-06-29 22:11:36', 'A', 14), | |
| (8, 'Itaquis', '2016-06-29 22:21:30', '2016-06-29 22:21:30', 'A', 15); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `departament` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `departament`; | |
| CREATE TABLE IF NOT EXISTS `departament` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `Name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `departament` | |
| -- | |
| TRUNCATE TABLE `departament`; | |
| -- | |
| -- Extraindo dados da tabela `departament` | |
| -- | |
| INSERT INTO `departament` (`id`, `Name`, `created_at`, `updated_at`, `state`) VALUES | |
| (14, 'teste', '2016-06-24 16:15:13', '2016-06-24 16:15:38', 'A'), | |
| (15, 'teste 2', '2016-06-24 16:25:57', '2016-06-24 16:25:57', 'A'), | |
| (16, 'teste 3', '2016-06-24 16:26:51', '2016-06-24 16:26:51', 'A'), | |
| (17, 'teste 4', '2016-06-24 16:27:49', '2016-06-24 16:27:49', 'A'), | |
| (18, 'test 5', '2016-06-24 16:31:44', '2016-06-24 16:31:44', 'A'), | |
| (19, 'teste 6', '2016-06-24 16:32:09', '2016-06-24 16:32:09', 'A'), | |
| (20, 'teste 123', '2016-06-24 16:32:36', '2016-06-24 16:32:36', 'A'), | |
| (21, 'outro teste', '2016-06-24 16:33:44', '2016-06-24 16:33:44', 'A'), | |
| (22, 'mais um teste', '2016-06-24 16:34:09', '2016-06-24 16:34:09', 'A'); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `departament_has_resale` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `departament_has_resale`; | |
| CREATE TABLE IF NOT EXISTS `departament_has_resale` ( | |
| `departament_id` int(11) NOT NULL, | |
| `resale_id` int(11) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`departament_id`,`resale_id`), | |
| KEY `fk_departament_has_resale_resale1_idx` (`resale_id`), | |
| KEY `fk_departament_has_resale_departament1_idx` (`departament_id`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `departament_has_resale` | |
| -- | |
| TRUNCATE TABLE `departament_has_resale`; | |
| -- | |
| -- Extraindo dados da tabela `departament_has_resale` | |
| -- | |
| INSERT INTO `departament_has_resale` (`departament_id`, `resale_id`, `created_at`, `updated_at`, `state`) VALUES | |
| (14, 3, '2016-06-24 16:15:38', '2016-06-24 16:15:38', 'A'), | |
| (15, 3, '2016-06-24 16:25:57', '2016-06-24 16:25:57', 'A'), | |
| (16, 3, '2016-06-24 16:26:51', '2016-06-24 16:26:51', 'A'), | |
| (17, 3, '2016-06-24 16:27:49', '2016-06-24 16:27:49', 'A'), | |
| (18, 3, '2016-06-24 16:31:44', '2016-06-24 16:31:44', 'A'), | |
| (19, 3, '2016-06-24 16:32:09', '2016-06-24 16:32:09', 'A'), | |
| (20, 3, '2016-06-24 16:32:36', '2016-06-24 16:32:36', 'A'), | |
| (21, 3, '2016-06-24 16:33:44', '2016-06-24 16:33:44', 'A'), | |
| (22, 3, '2016-06-24 16:34:09', '2016-06-24 16:34:09', 'A'); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `district` | |
| -- | |
| -- Criação: 17-Jun-2016 às 03:10 | |
| -- | |
| DROP TABLE IF EXISTS `district`; | |
| CREATE TABLE IF NOT EXISTS `district` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| `city_id` int(11) NOT NULL, | |
| PRIMARY KEY (`id`) USING BTREE, | |
| KEY `fk_district_city1_idx` (`city_id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `district` | |
| -- | |
| TRUNCATE TABLE `district`; | |
| -- | |
| -- Extraindo dados da tabela `district` | |
| -- | |
| INSERT INTO `district` (`id`, `name`, `created_at`, `updated_at`, `state`, `city_id`) VALUES | |
| (1, 'AdminDistrict', '2016-06-09 21:01:28', '2016-06-09 21:01:28', 'A', 1), | |
| (3, 'Centro', '2016-06-17 00:14:49', '2016-06-17 00:14:49', 'A', 3), | |
| (6, 'Enio Sayago', '2016-06-29 22:07:39', '2016-06-29 22:07:39', 'A', 5), | |
| (7, 'Enio Sayago', '2016-06-29 22:09:00', '2016-06-29 22:09:00', 'A', 6), | |
| (8, 'Enio Sayago', '2016-06-29 22:12:50', '2016-06-29 22:12:50', 'A', 7); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `equipament` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `equipament`; | |
| CREATE TABLE IF NOT EXISTS `equipament` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `equipament` | |
| -- | |
| TRUNCATE TABLE `equipament`; | |
| -- | |
| -- Extraindo dados da tabela `equipament` | |
| -- | |
| INSERT INTO `equipament` (`id`, `name`, `created_at`, `updated_at`, `state`) VALUES | |
| (1, 'Carro', '2016-06-24 16:48:56', '2016-06-24 16:50:06', 'I'); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `equipament_has_user` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `equipament_has_user`; | |
| CREATE TABLE IF NOT EXISTS `equipament_has_user` ( | |
| `equipament_id` int(11) NOT NULL, | |
| `user_id` int(11) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`equipament_id`,`user_id`), | |
| KEY `fk_equipament_has_user_user1_idx` (`user_id`), | |
| KEY `fk_equipament_has_user_equipament1_idx` (`equipament_id`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `equipament_has_user` | |
| -- | |
| TRUNCATE TABLE `equipament_has_user`; | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `resale` | |
| -- | |
| -- Criação: 22-Jun-2016 às 22:57 | |
| -- | |
| DROP TABLE IF EXISTS `resale`; | |
| CREATE TABLE IF NOT EXISTS `resale` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| `address_id` int(11) NOT NULL, | |
| PRIMARY KEY (`id`) USING BTREE, | |
| KEY `fk_resale_address1_idx` (`address_id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `resale` | |
| -- | |
| TRUNCATE TABLE `resale`; | |
| -- | |
| -- Extraindo dados da tabela `resale` | |
| -- | |
| INSERT INTO `resale` (`id`, `name`, `created_at`, `updated_at`, `state`, `address_id`) VALUES | |
| (2, 'Novo teste', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'I', 5), | |
| (3, 'teste1234', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'A', 6), | |
| (5, 'Revenda N', '2016-06-29 22:21:30', '2016-06-29 22:21:30', 'A', 8); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `state` | |
| -- | |
| -- Criação: 17-Jun-2016 às 02:03 | |
| -- | |
| DROP TABLE IF EXISTS `state`; | |
| CREATE TABLE IF NOT EXISTS `state` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `name` varchar(64) NOT NULL, | |
| `initials` char(2) NOT NULL, | |
| `country` varchar(64) NOT NULL DEFAULT 'Brasil', | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `state` | |
| -- | |
| TRUNCATE TABLE `state`; | |
| -- | |
| -- Extraindo dados da tabela `state` | |
| -- | |
| INSERT INTO `state` (`id`, `name`, `initials`, `country`, `created_at`, `updated_at`, `state`) VALUES | |
| (1, 'AdminState', 'AD', 'Admin', '2016-06-09 21:00:38', '2016-06-09 21:00:38', 'A'), | |
| (8, 'Rio Grande do Sul', 'RS', 'Brasil', '2016-06-16 23:46:44', '2016-06-16 23:46:44', 'A'), | |
| (11, 'Rio Grande do Sul', 'RS', 'Brazil', '2016-06-29 22:07:39', '2016-06-29 22:07:39', 'A'), | |
| (14, 'Rio Grande do Sul', 'RS', 'Brasilis', '2016-06-29 22:10:45', '2016-06-29 22:10:45', 'A'), | |
| (15, 'Rio Grande do Sulis', 'RS', 'Brasilis', '2016-06-29 22:13:16', '2016-06-29 22:13:16', 'A'); | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `user` | |
| -- | |
| -- Criação: 30-Jun-2016 às 00:02 | |
| -- | |
| DROP TABLE IF EXISTS `user`; | |
| CREATE TABLE IF NOT EXISTS `user` ( | |
| `id` int(11) NOT NULL AUTO_INCREMENT, | |
| `username` varchar(64) NOT NULL, | |
| `password` varchar(255) NOT NULL, | |
| `name` varchar(64) NOT NULL, | |
| `cpf` varchar(14) NOT NULL, | |
| `rg` varchar(10) NOT NULL, | |
| `birth_date` date NOT NULL, | |
| `admission_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `resignation_date` datetime DEFAULT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| `address_id` int(11) NOT NULL, | |
| PRIMARY KEY (`id`) USING BTREE, | |
| KEY `fk_user_address1_idx` (`address_id`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `user` | |
| -- | |
| TRUNCATE TABLE `user`; | |
| -- | |
| -- Extraindo dados da tabela `user` | |
| -- | |
| INSERT INTO `user` (`id`, `username`, `password`, `name`, `cpf`, `rg`, `birth_date`, `admission_date`, `resignation_date`, `created_at`, `updated_at`, `state`, `address_id`) VALUES | |
| (1, 'admin', 'd033e22ae348aeb5660fc2140aec35850c4da997', 'Admin', '000.000.000-00', '0000000000', '2016-06-09', '2016-06-09 00:00:00', NULL, '2016-06-09 21:02:59', '2016-06-09 21:02:59', 'A', 1), | |
| (4, 'faogustavo', 'd033e22ae348aeb5660fc2140aec35850c4da997', 'Gustavo', '123.456.789-98', '2088525445', '1995-08-07', '2016-06-17 00:35:35', NULL, '2016-06-17 00:35:35', '2016-06-17 00:35:35', 'A', 2), | |
| (5, 'faogustavo2', 'd033e22ae348aeb5660fc2140aec35850c4da997', 'gustavo2', '033.750.670-11', '2088525445', '1995-01-15', '2016-06-17 00:36:14', NULL, '2016-06-17 00:36:14', '2016-06-17 00:36:41', 'A', 3), | |
| (8, 'faogustavo', '1234', 'Gustavo Fão Valvassori', '033.750.670-11', '2088525445', '2016-06-29', '2016-06-29 21:37:47', NULL, '2016-06-29 21:37:47', '2016-06-29 21:37:47', 'A', 1), | |
| (10, 'faogustavo', '1234', 'Gustavo Fão Valvassori', '033.750.670-11', '2088525445', '2016-06-29', '2016-06-29 22:08:43', NULL, '2016-06-29 22:08:43', '2016-06-29 22:08:43', 'A', 8), | |
| (11, 'faogustavo', '1234', 'Gustavo Fão Valvassori', '033.750.670-11', '2088525445', '2016-06-29', '2016-06-29 22:12:50', NULL, '2016-06-29 22:12:50', '2016-06-29 22:12:50', 'A', 8), | |
| (12, 'faogustavo', '1234', 'Gustavo Fão Valvassori', '033.750.670-11', '2088525445', '2016-06-29', '2016-06-29 22:13:16', NULL, '2016-06-29 22:13:16', '2016-06-29 22:13:16', 'A', 8); | |
| -- | |
| -- Acionadores `user` | |
| -- | |
| DROP TRIGGER IF EXISTS `t_demissao`; | |
| DELIMITER $$ | |
| CREATE TRIGGER `t_demissao` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN | |
| IF NEW.state = 'I' THEN | |
| SET NEW.resignation_date = NOW(); | |
| UPDATE equipament_has_user ehu | |
| SET ehu.state = 'I' WHERE ehu.user_id = NEW.id; | |
| END IF; | |
| END | |
| $$ | |
| DELIMITER ; | |
| -- -------------------------------------------------------- | |
| -- | |
| -- Estrutura da tabela `user_has_resale` | |
| -- | |
| -- Criação: 06-Jun-2016 às 16:36 | |
| -- | |
| DROP TABLE IF EXISTS `user_has_resale`; | |
| CREATE TABLE IF NOT EXISTS `user_has_resale` ( | |
| `user_id` int(11) NOT NULL, | |
| `resale_id` int(11) NOT NULL, | |
| `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| `state` char(1) NOT NULL DEFAULT 'A', | |
| PRIMARY KEY (`user_id`,`resale_id`), | |
| KEY `fk_user_has_Resale_Resale1_idx` (`resale_id`), | |
| KEY `fk_user_has_Resale_user1_idx` (`user_id`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; | |
| -- | |
| -- Truncate table before insert `user_has_resale` | |
| -- | |
| TRUNCATE TABLE `user_has_resale`; | |
| -- | |
| -- Constraints for dumped tables | |
| -- | |
| -- | |
| -- Limitadores para a tabela `acess_has_user` | |
| -- | |
| ALTER TABLE `acess_has_user` | |
| ADD CONSTRAINT `fk_acess_has_user_acess` FOREIGN KEY (`acess_id`) REFERENCES `acess` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | |
| ADD CONSTRAINT `fk_acess_has_user_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `address` | |
| -- | |
| ALTER TABLE `address` | |
| ADD CONSTRAINT `fk_address_district1` FOREIGN KEY (`district_id`) REFERENCES `district` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `city` | |
| -- | |
| ALTER TABLE `city` | |
| ADD CONSTRAINT `fk_city_state1` FOREIGN KEY (`state_id`) REFERENCES `state` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `departament_has_resale` | |
| -- | |
| ALTER TABLE `departament_has_resale` | |
| ADD CONSTRAINT `fk_departament_has_resale_departament1` FOREIGN KEY (`departament_id`) REFERENCES `departament` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | |
| ADD CONSTRAINT `fk_departament_has_resale_resale1` FOREIGN KEY (`resale_id`) REFERENCES `resale` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `district` | |
| -- | |
| ALTER TABLE `district` | |
| ADD CONSTRAINT `fk_district_city1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `equipament_has_user` | |
| -- | |
| ALTER TABLE `equipament_has_user` | |
| ADD CONSTRAINT `fk_equipament_has_user_equipament1` FOREIGN KEY (`equipament_id`) REFERENCES `equipament` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | |
| ADD CONSTRAINT `fk_equipament_has_user_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `resale` | |
| -- | |
| ALTER TABLE `resale` | |
| ADD CONSTRAINT `fk_resale_address1` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `user` | |
| -- | |
| ALTER TABLE `user` | |
| ADD CONSTRAINT `fk_user_address1` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
| -- | |
| -- Limitadores para a tabela `user_has_resale` | |
| -- | |
| ALTER TABLE `user_has_resale` | |
| ADD CONSTRAINT `fk_user_has_Resale_Resale1` FOREIGN KEY (`resale_id`) REFERENCES `resale` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | |
| ADD CONSTRAINT `fk_user_has_Resale_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Nada tão elaborado... mas é algo...hehehe
create view consulta_cad as select * from user;
create view consulta_address as select * from address;
create view consulta_resale as select r.name, r.state
from resale r
inner join address a on a.id = r.id
order by r.name;
select * from consulta_resale;
select * from consulta_address;
select * from consulta_cad;
create view user_full as select u.name, u.admission_date, u.username, ad.CEP, ad.street, ad.number, ad.complement
from user u
inner join address ad on u.address_id = ad.id
inner join district dis on ad.district_id = dis.id
group by u.admission_date;
select * from user_full;