-- phpMyAdmin SQL Dump -- version 4.5.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Gegenereerd op: 09 nov 2016 om 22:03 -- Serverversie: 10.1.16-MariaDB -- PHP-versie: 5.6.24 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 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: `card_traders` -- -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `address` -- CREATE TABLE `address` ( `ADDRESS_ID` int(11) NOT NULL, `ADDRESS_LINE1` varchar(50) NOT NULL, `ADDRESS_LINE2` varchar(50) DEFAULT NULL, `STREET_NUMBER` int(11) NOT NULL, `ADDITION` varchar(6) DEFAULT NULL, `ZIPCODE` varchar(6) NOT NULL, `CITY` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Gegevens worden geëxporteerd voor tabel `address` -- INSERT INTO `address` (`ADDRESS_ID`, `ADDRESS_LINE1`, `ADDRESS_LINE2`, `STREET_NUMBER`, `ADDITION`, `ZIPCODE`, `CITY`) VALUES (1, 'Eeldersingel', NULL, 2, 'D3', '9726AR', 'Groningen'), (17, 'Usersstreet', NULL, 1, '', '1234AB', 'UserCity'), (18, 'Zuilen', NULL, 43, '', '9716KG', 'Groningen'), (19, '9', NULL, 10, '11', '12', '13'), (20, '1', NULL, 1, '1', '1', '1'); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `cards` -- CREATE TABLE `cards` ( `CARD_ID` int(11) NOT NULL, `CARD_NAME` varchar(50) NOT NULL, `ENERGY_TYPE` varchar(50) NOT NULL, `RARITY` varchar(50) NOT NULL, `EXPANSION` varchar(50) DEFAULT NULL, `HP` int(3) DEFAULT NULL, `WEAKNESS` varchar(50) DEFAULT NULL, `RESISTANCE` varchar(50) DEFAULT NULL, `CARD_TYPE` varchar(50) NOT NULL, `DESCRIPTION` varchar(500) NOT NULL, `IMG_LINK` varchar(500) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Gegevens worden geëxporteerd voor tabel `cards` -- INSERT INTO `cards` (`CARD_ID`, `CARD_NAME`, `ENERGY_TYPE`, `RARITY`, `EXPANSION`, `HP`, `WEAKNESS`, `RESISTANCE`, `CARD_TYPE`, `DESCRIPTION`, `IMG_LINK`) VALUES (1, 'Reshiram', 'Fire', 'Rare Holo', 'Black & White', 130, 'Water', 'None', 'Pokemon', '-', 'http://assets.pokemon.com/assets/cms2/img/cards/web/BW1/BW1_EN_26.png'), (3, 'Alakazam', 'Psychic', 'Rare Holo', 'Diamond & Pearl - Mysterious Treasures', 100, 'Psychic +30', 'None', 'Pokemon', 'Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi temp', 'http://assets.pokemon.com/assets/cms2/img/cards/web/DP2/DP2_EN_2.png'), (4, 'Shining Tyranitar', 'Darkness', 'Shining Holo', 'Neo Destiny', 80, 'None', 'Psychic -30', 'Pokemon', 'It is so powerful, it can knock down a mountain with just one arm', 'http://cdn.bulbagarden.net/upload/a/a5/ShiningTyranitarNeoDestiny113.jpg'), (5, 'Great Ball', 'None', 'Uncommon', ' EX FireRed & LeafGreen', 0, '-', '-', 'Trainer', 'Look at the top 7 cards of your deck, you may reveal a Pokemon you find there and put it in your hand. Shuffle the other cards back into your deck', 'http://cdn.bulbagarden.net/upload/d/d6/GreatBallEmergingPowers93.jpg'), (6, 'Fire Energy', 'Fire', '-', 'Evolutions', 0, '-', '-', 'Energy', '-', 'http://cdn.bulbagarden.net/upload/3/37/FireEnergyEvolutions92.jpg'); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `card_trades` -- CREATE TABLE `card_trades` ( `TRADE_ID` int(11) NOT NULL, `Initiation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `CARD_ID_ONE` int(11) NOT NULL, `CARD_ID_TWO` int(11) NOT NULL, `USER_ID_ONE` int(11) NOT NULL, `USER_ID_TWO` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `contactmsg` -- CREATE TABLE `contactmsg` ( `MSG_ID` int(11) NOT NULL, `FIRST_NAME` varchar(25) NOT NULL, `LAST_NAME` varchar(25) NOT NULL, `EMAIL` varchar(50) NOT NULL, `CATEGORY` varchar(25) NOT NULL, `SUBJECT` varchar(100) NOT NULL, `MESSAGE` varchar(1000) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Gegevens worden geëxporteerd voor tabel `contactmsg` -- INSERT INTO `contactmsg` (`MSG_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `CATEGORY`, `SUBJECT`, `MESSAGE`) VALUES (2, 'User', 'User', 'User@user.com', 'request', 'Charizard request', 'I''d like charizard in the database please add it.'); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `conversations` -- CREATE TABLE `conversations` ( `Conversation_id` int(11) NOT NULL, `User_one` int(11) NOT NULL, `User_two` int(11) NOT NULL, `OFFER_ID` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `favorites` -- CREATE TABLE `favorites` ( `FAVORITES_ID` int(11) NOT NULL, `CREATION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `USERS_USER_ID` int(11) NOT NULL, `CARD_ID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `messages` -- CREATE TABLE `messages` ( `MESSAGE_ID` int(11) NOT NULL, `CONVERSATION_ID` int(11) NOT NULL, `TEXT` varchar(500) NOT NULL, `MESSAGE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `notifications` -- CREATE TABLE `notifications` ( `ACTIVE` tinyint(1) NOT NULL DEFAULT '1', `CREATION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `MAX_PRICE` decimal(6,2) DEFAULT NULL, `CARD_ID` int(11) NOT NULL, `USER_ID` int(11) NOT NULL, `NOTIFICATIONS_ID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `offered` -- CREATE TABLE `offered` ( `OFFER_ID` int(11) NOT NULL, `DATE_PLACED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ACTIVE` tinyint(1) NOT NULL DEFAULT '1', `ASKING_PRICE` decimal(6,2) NOT NULL, `SELLER_ID` int(11) NOT NULL, `CARD_ID` int(11) NOT NULL, `CARD_CONDITION` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Gegevens worden geëxporteerd voor tabel `offered` -- INSERT INTO `offered` (`OFFER_ID`, `DATE_PLACED`, `ACTIVE`, `ASKING_PRICE`, `SELLER_ID`, `CARD_ID`, `CARD_CONDITION`) VALUES (1, '2016-11-08 15:16:03', 1, '21.00', 10, 4, 'Mint (M)'), (2, '2016-11-08 15:21:43', 1, '15.00', 10, 3, 'Excellent (EX)'), (3, '2016-11-08 15:54:24', 1, '23.00', 10, 5, 'Gem Mint (GEM)'), (4, '2016-11-08 16:37:40', 1, '200.00', 11, 3, 'Mint (M)'), (5, '2016-11-08 18:25:15', 1, '20.00', 1, 3, 'Near Mint Plus (+) (NM+)'), (6, '2016-11-09 13:10:28', 1, '50.00', 12, 5, 'Played (PL)'); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `orders` -- CREATE TABLE `orders` ( `ORDER_ID` int(11) NOT NULL, `ORDER_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ACCEPT_DATE` datetime DEFAULT NULL, `AGREED_PRICE` decimal(6,2) NOT NULL, `PURCHASER_ID` int(11) NOT NULL, `PAYMENT_METHOD` varchar(50) NOT NULL, `OFFER_ID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `tickets` -- CREATE TABLE `tickets` ( `TICKET_ID` int(11) NOT NULL, `USER_ID` int(11) NOT NULL, `CATEGORY` varchar(50) NOT NULL, `MESSAGE` varchar(500) NOT NULL, `TICKET_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `LAST_EDIT` datetime NOT NULL, `ORDER_ID` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `users` -- CREATE TABLE `users` ( `USER_ID` int(11) NOT NULL, `FIRST_NAME` varchar(25) NOT NULL, `LAST_NAME` varchar(50) NOT NULL, `USERNAME` varchar(50) NOT NULL, `PHONE` int(11) DEFAULT NULL, `IBAN` varchar(34) DEFAULT NULL, `EMAIL` varchar(50) NOT NULL, `REGISTRATION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ENABLED` tinyint(1) NOT NULL DEFAULT '1', `REVIEW_ENABLED` tinyint(1) NOT NULL DEFAULT '1', `PASSWORD` varchar(12) NOT NULL, `ADDRESS_ID` int(11) NOT NULL, `USER_TYPE` varchar(10) NOT NULL DEFAULT 'USER' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Gegevens worden geëxporteerd voor tabel `users` -- INSERT INTO `users` (`USER_ID`, `FIRST_NAME`, `LAST_NAME`, `USERNAME`, `PHONE`, `IBAN`, `EMAIL`, `REGISTRATION_DATE`, `ENABLED`, `REVIEW_ENABLED`, `PASSWORD`, `ADDRESS_ID`, `USER_TYPE`) VALUES (1, 'Dave', 'Thijs', 'drownzz', 624890495, '0', 'davethijs@hotmail.com', '2016-11-07 08:36:10', 1, 1, 'w8woord', 1, 'ADMIN'), (10, 'User', 'User', 'user', 123456789, '0', 'user@user.com', '2016-11-08 11:51:38', 1, 1, 'user', 17, 'USER'), (11, 'Raymond', 'Bal', 'Rbal', 0, '0', 'raymondbal@live.nl', '2016-11-08 16:33:13', 1, 1, 'Wachtwoord', 18, 'ADMIN'), (12, '5', '6', '1', 7, '8', 'a@b.nl', '2016-11-09 13:08:28', 1, 1, '3', 19, 'USER'), (13, 'N', 'N', 'Maester', 1, '1', 'naahmu@gamil.com', '2016-11-09 16:20:22', 1, 1, '123456', 20, 'ADMIN'); -- -- Indexen voor geëxporteerde tabellen -- -- -- Indexen voor tabel `address` -- ALTER TABLE `address` ADD PRIMARY KEY (`ADDRESS_ID`); -- -- Indexen voor tabel `cards` -- ALTER TABLE `cards` ADD PRIMARY KEY (`CARD_ID`); -- -- Indexen voor tabel `card_trades` -- ALTER TABLE `card_trades` ADD PRIMARY KEY (`TRADE_ID`), ADD KEY `fk_Trades_CARDS1_idx` (`CARD_ID_ONE`), ADD KEY `fk_Trades_CARDS2_idx` (`CARD_ID_TWO`), ADD KEY `fk_Trades_USERS1_idx` (`USER_ID_ONE`), ADD KEY `fk_Trades_USERS2_idx` (`USER_ID_TWO`); -- -- Indexen voor tabel `contactmsg` -- ALTER TABLE `contactmsg` ADD PRIMARY KEY (`MSG_ID`); -- -- Indexen voor tabel `conversations` -- ALTER TABLE `conversations` ADD PRIMARY KEY (`Conversation_id`), ADD KEY `MSGUSR_FK` (`User_one`), ADD KEY `MSGURSS_FK` (`User_two`), ADD KEY `MSGFFR_FK` (`OFFER_ID`); -- -- Indexen voor tabel `favorites` -- ALTER TABLE `favorites` ADD PRIMARY KEY (`FAVORITES_ID`), ADD KEY `FAVORITES_USERS_FK` (`USERS_USER_ID`), ADD KEY `FAVORITES_CARDS_FK` (`CARD_ID`); -- -- Indexen voor tabel `messages` -- ALTER TABLE `messages` ADD PRIMARY KEY (`MESSAGE_ID`), ADD KEY `MSG_CNV_FK` (`CONVERSATION_ID`); -- -- Indexen voor tabel `notifications` -- ALTER TABLE `notifications` ADD PRIMARY KEY (`NOTIFICATIONS_ID`), ADD KEY `NOTIFICATIONS_USERS_FK` (`USER_ID`), ADD KEY `NTFCARDS_FK` (`CARD_ID`); -- -- Indexen voor tabel `offered` -- ALTER TABLE `offered` ADD PRIMARY KEY (`OFFER_ID`), ADD KEY `OFFERED_USERS_FK` (`SELLER_ID`), ADD KEY `OFFERED_CARDS_FK` (`CARD_ID`); -- -- Indexen voor tabel `orders` -- ALTER TABLE `orders` ADD PRIMARY KEY (`ORDER_ID`), ADD KEY `ORDERS_OFFERED_FK` (`OFFER_ID`), ADD KEY `ORDERS_USERS_FK` (`PURCHASER_ID`); -- -- Indexen voor tabel `tickets` -- ALTER TABLE `tickets` ADD PRIMARY KEY (`TICKET_ID`), ADD KEY `TICKETS_USERS_FK` (`USER_ID`), ADD KEY `TICKET_USERS_FK` (`ORDER_ID`); -- -- Indexen voor tabel `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`USER_ID`), ADD UNIQUE KEY `EMAIL` (`EMAIL`), ADD KEY `USERS_ADDRESS_FK` (`ADDRESS_ID`); -- -- AUTO_INCREMENT voor geëxporteerde tabellen -- -- -- AUTO_INCREMENT voor een tabel `address` -- ALTER TABLE `address` MODIFY `ADDRESS_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21; -- -- AUTO_INCREMENT voor een tabel `cards` -- ALTER TABLE `cards` MODIFY `CARD_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT voor een tabel `card_trades` -- ALTER TABLE `card_trades` MODIFY `TRADE_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `contactmsg` -- ALTER TABLE `contactmsg` MODIFY `MSG_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT voor een tabel `conversations` -- ALTER TABLE `conversations` MODIFY `Conversation_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `favorites` -- ALTER TABLE `favorites` MODIFY `FAVORITES_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `messages` -- ALTER TABLE `messages` MODIFY `MESSAGE_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `notifications` -- ALTER TABLE `notifications` MODIFY `NOTIFICATIONS_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `offered` -- ALTER TABLE `offered` MODIFY `OFFER_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT voor een tabel `orders` -- ALTER TABLE `orders` MODIFY `ORDER_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `tickets` -- ALTER TABLE `tickets` MODIFY `TICKET_ID` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT voor een tabel `users` -- ALTER TABLE `users` MODIFY `USER_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14; -- -- Beperkingen voor geëxporteerde tabellen -- -- -- Beperkingen voor tabel `card_trades` -- ALTER TABLE `card_trades` ADD CONSTRAINT `fk_TRADES_USERS1` FOREIGN KEY (`USER_ID_ONE`) REFERENCES `users` (`USER_ID`), ADD CONSTRAINT `fk_TRADES_USERS2` FOREIGN KEY (`USER_ID_TWO`) REFERENCES `users` (`USER_ID`), ADD CONSTRAINT `fk_TRAFES_CARDS2` FOREIGN KEY (`CARD_ID_TWO`) REFERENCES `cards` (`CARD_ID`), ADD CONSTRAINT `fk_Trades_CARDS1` FOREIGN KEY (`CARD_ID_ONE`) REFERENCES `cards` (`CARD_ID`); -- -- Beperkingen voor tabel `conversations` -- ALTER TABLE `conversations` ADD CONSTRAINT `MSGFFR_FK` FOREIGN KEY (`OFFER_ID`) REFERENCES `offered` (`OFFER_ID`), ADD CONSTRAINT `MSGURSS_FK` FOREIGN KEY (`User_two`) REFERENCES `users` (`USER_ID`), ADD CONSTRAINT `MSGUSR_FK` FOREIGN KEY (`User_one`) REFERENCES `users` (`USER_ID`); -- -- Beperkingen voor tabel `favorites` -- ALTER TABLE `favorites` ADD CONSTRAINT `FAVORITES_CARDS_FK` FOREIGN KEY (`CARD_ID`) REFERENCES `cards` (`CARD_ID`), ADD CONSTRAINT `FAVORITES_USERS_FK` FOREIGN KEY (`USERS_USER_ID`) REFERENCES `users` (`USER_ID`); -- -- Beperkingen voor tabel `messages` -- ALTER TABLE `messages` ADD CONSTRAINT `MSG_CNV_FK` FOREIGN KEY (`CONVERSATION_ID`) REFERENCES `conversations` (`Conversation_id`); -- -- Beperkingen voor tabel `notifications` -- ALTER TABLE `notifications` ADD CONSTRAINT `NOTIFICATIONS_USERS_FK` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), ADD CONSTRAINT `NTFCARDS_FK` FOREIGN KEY (`CARD_ID`) REFERENCES `cards` (`CARD_ID`); -- -- Beperkingen voor tabel `offered` -- ALTER TABLE `offered` ADD CONSTRAINT `OFFERED_CARDS_FK` FOREIGN KEY (`CARD_ID`) REFERENCES `cards` (`CARD_ID`), ADD CONSTRAINT `OFFERED_USERS_FK` FOREIGN KEY (`SELLER_ID`) REFERENCES `users` (`USER_ID`); -- -- Beperkingen voor tabel `orders` -- ALTER TABLE `orders` ADD CONSTRAINT `ORDERS_OFFERED_FK` FOREIGN KEY (`OFFER_ID`) REFERENCES `offered` (`OFFER_ID`), ADD CONSTRAINT `ORDERS_USERS_FK` FOREIGN KEY (`PURCHASER_ID`) REFERENCES `users` (`USER_ID`); -- -- Beperkingen voor tabel `tickets` -- ALTER TABLE `tickets` ADD CONSTRAINT `TICKETS_USERS_FK` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`), ADD CONSTRAINT `TICKET_USERS_FK` FOREIGN KEY (`ORDER_ID`) REFERENCES `orders` (`ORDER_ID`); -- -- Beperkingen voor tabel `users` -- ALTER TABLE `users` ADD CONSTRAINT `USERS_ADDRESS_FK` FOREIGN KEY (`ADDRESS_ID`) REFERENCES `address` (`ADDRESS_ID`); /*!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 */;