-- http://www.phpmyadmin.net
--
-- Generato il: Mar 23, 2014 alle 11:13
-- Versione del server: 5.5.36-MariaDB
-- Versione PHP: 5.5.10




--
-- Database: `simplecrud_db`
--
CREATE DATABASE `simplecrud_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `simplecrud_db`;

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

--
-- Struttura della tabella `address`
--

CREATE TABLE IF NOT EXISTS `address` (
  `ID` text NOT NULL,
  `CITY` text NOT NULL,
  `COUNTRY` text NOT NULL,
  `STREET` text NOT NULL,
  `SUBURB` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `address`
--

INSERT INTO `address` (`ID`, `CITY`, `COUNTRY`, `STREET`, `SUBURB`) VALUES
('1', 'city', 'country', 'street', 'suburb'),
('2', 'city', 'country', 'street', 'suburb'),
('3', 'city', 'country', 'street', 'suburb'),
('4', 'city', 'country', 'street', 'suburb'),
('5', 'city', 'country', 'street', 'suburb'),
('6', 'city', 'country', 'street', 'suburb'),
('7', 'city', 'country', 'street', 'suburb'),
('8', 'city', 'country', 'street', 'suburb'),
('9', 'city', 'country', 'street', 'suburb'),
('10', 'city', 'country', 'street', 'suburb'),
('11', 'city', 'country', 'street', 'suburb'),
('12', 'city', 'country', 'street', 'suburb'),
('13', 'city', 'country', 'street', 'suburb');

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

--
-- Struttura della tabella `role`
--

CREATE TABLE IF NOT EXISTS `role` (
  `ID` text NOT NULL,
  `ROLEDESC` text NOT NULL,
  `ROLENAME` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `role`
--

INSERT INTO `role` (`ID`, `ROLEDESC`, `ROLENAME`) VALUES
('1', 'Administrator', 'Administrators'),
('2', 'Manager', 'Managers'),
('3', 'User', 'Users');

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

--
-- Struttura della tabella `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `ID` text NOT NULL,
  `EMAIL` text NOT NULL,
  `FIRSTNAME` text NOT NULL,
  `LASTNAME` text NOT NULL,
  `PASSWORD` text NOT NULL,
  `USERNAME` text NOT NULL,
  `ADDRESS_ID` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user`
--

INSERT INTO `user` (`ID`, `EMAIL`, `FIRSTNAME`, `LASTNAME`, `PASSWORD`, `USERNAME`, `ADDRESS_ID`) VALUES
('1', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'admin', '1'),
('2', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user1', '2'),
('3', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user2', '3'),
('4', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user3', '4'),
('5', 'e-mail', 'name', 'surnamer', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user4', '5'),
('6', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user5', '6'),
('7', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user6', '7'),
('8', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user7', '8'),
('9', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user8', '9'),
('10', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user9', '10'),
('11', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user10', '11'),
('12', 'e-mail', 'name', 'surname', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'user11', '12'),
('13', 'e-mail', 'name', 'surname', '8cc7a4de25920211fb328c8b3e3f7ccadeff4a2073ba8a093ebf3a06ab828774', 'user12', '13');

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

--
-- Struttura stand-in per le viste `user_role_view`
--
CREATE TABLE IF NOT EXISTS `user_role_view` (
`username` text
,`password` text
,`rolename` text
);
-- --------------------------------------------------------

--
-- Struttura della tabella `user_roles`
--

CREATE TABLE IF NOT EXISTS `user_roles` (
  `Role_roleid` text NOT NULL,
  `User_userid` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `user_roles`
--

INSERT INTO `user_roles` (`Role_roleid`, `User_userid`) VALUES
('1', '1'),
('3', '2'),
('3', '3'),
('3', '4'),
('3', '5'),
('3', '6'),
('3', '7'),
('3', '8'),
('3', '9'),
('3', '10'),
('3', '11'),
('3', '12'),
('3', '13');

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

--
-- Struttura per la vista `user_role_view`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_role_view` AS select `user`.`USERNAME` AS `username`,`user`.`PASSWORD` AS `password`,`role`.`ROLENAME` AS `rolename` from ((`user_roles` join `user` on((`user_roles`.`User_userid` = `user`.`ID`))) join `role` on((`user_roles`.`Role_roleid` = `role`.`ID`)));

