You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
196 lines
4.5 KiB
196 lines
4.5 KiB
-- phpMyAdmin SQL Dump |
|
-- version 5.0.4 |
|
-- https://www.phpmyadmin.net/ |
|
-- |
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; |
|
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: `shoppingList` |
|
-- |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `listEntries` |
|
-- |
|
|
|
CREATE TABLE `listEntries` ( |
|
`listId` int(11) NOT NULL, |
|
`productId` int(11) NOT NULL, |
|
`amount` float NOT NULL DEFAULT 1, |
|
`unit` int(11) NOT NULL |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `lists` |
|
-- |
|
|
|
CREATE TABLE `lists` ( |
|
`id` int(11) NOT NULL, |
|
`creationTime` bigint(20) NOT NULL, |
|
`comment` varchar(200) DEFAULT NULL |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `products` |
|
-- |
|
|
|
CREATE TABLE `products` ( |
|
`id` int(11) NOT NULL, |
|
`name` varchar(100) NOT NULL, |
|
`synonyms` varchar(500) DEFAULT NULL COMMENT 'Separate entries with semicolons.', |
|
`storeTypeId` int(11) NOT NULL |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `settings` |
|
-- |
|
|
|
CREATE TABLE `settings` ( |
|
`settingName` varchar(100) NOT NULL, |
|
`settingValue` varchar(100) NOT NULL |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- |
|
-- Dumping data for table `settings` |
|
-- |
|
|
|
INSERT INTO `settings` (`settingName`, `settingValue`) VALUES |
|
('databaseVersion', '3'); |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `storeTypes` |
|
-- |
|
|
|
CREATE TABLE `storeTypes` ( |
|
`id` int(11) NOT NULL, |
|
`name` varchar(200) NOT NULL |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- -------------------------------------------------------- |
|
|
|
-- |
|
-- Table structure for table `units` |
|
-- |
|
|
|
CREATE TABLE `units` ( |
|
`id` int(11) NOT NULL, |
|
`name` varchar(100) NOT NULL, |
|
`abbreviation` varchar(10) NOT NULL, |
|
`isDefault` tinyint(2) NOT NULL DEFAULT 0, |
|
`isDummy` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'This unit will not actually be displayed. Instead an item will just be shown without amount or unit.', |
|
`isPiece` tinyint(2) NOT NULL DEFAULT 0 |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
|
|
|
-- |
|
-- Indexes for dumped tables |
|
-- |
|
|
|
-- |
|
-- Indexes for table `listEntries` |
|
-- |
|
ALTER TABLE `listEntries` |
|
ADD PRIMARY KEY (`listId`,`productId`,`unit`), |
|
ADD KEY `listEntries_ibfk_3` (`unit`), |
|
ADD KEY `listEntries_ibfk_2` (`productId`); |
|
|
|
-- |
|
-- Indexes for table `lists` |
|
-- |
|
ALTER TABLE `lists` |
|
ADD PRIMARY KEY (`id`); |
|
|
|
-- |
|
-- Indexes for table `products` |
|
-- |
|
ALTER TABLE `products` |
|
ADD PRIMARY KEY (`id`), |
|
ADD UNIQUE KEY `name` (`name`), |
|
ADD KEY `storeTypeId` (`storeTypeId`); |
|
|
|
-- |
|
-- Indexes for table `settings` |
|
-- |
|
ALTER TABLE `settings` |
|
ADD PRIMARY KEY (`settingName`); |
|
|
|
-- |
|
-- Indexes for table `storeTypes` |
|
-- |
|
ALTER TABLE `storeTypes` |
|
ADD PRIMARY KEY (`id`), |
|
ADD UNIQUE KEY `name` (`name`); |
|
|
|
-- |
|
-- Indexes for table `units` |
|
-- |
|
ALTER TABLE `units` |
|
ADD PRIMARY KEY (`id`); |
|
|
|
-- |
|
-- AUTO_INCREMENT for dumped tables |
|
-- |
|
|
|
-- |
|
-- AUTO_INCREMENT for table `lists` |
|
-- |
|
ALTER TABLE `lists` |
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
|
|
|
-- |
|
-- AUTO_INCREMENT for table `products` |
|
-- |
|
ALTER TABLE `products` |
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
|
|
|
-- |
|
-- AUTO_INCREMENT for table `storeTypes` |
|
-- |
|
ALTER TABLE `storeTypes` |
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
|
|
|
-- |
|
-- AUTO_INCREMENT for table `units` |
|
-- |
|
ALTER TABLE `units` |
|
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
|
|
|
-- |
|
-- Constraints for dumped tables |
|
-- |
|
|
|
-- |
|
-- Constraints for table `listEntries` |
|
-- |
|
ALTER TABLE `listEntries` |
|
ADD CONSTRAINT `listEntries_ibfk_1` FOREIGN KEY (`listId`) REFERENCES `lists` (`id`), |
|
ADD CONSTRAINT `listEntries_ibfk_2` FOREIGN KEY (`productId`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
ADD CONSTRAINT `listEntries_ibfk_3` FOREIGN KEY (`unit`) REFERENCES `units` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; |
|
|
|
-- |
|
-- Constraints for table `products` |
|
-- |
|
ALTER TABLE `products` |
|
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`storeTypeId`) REFERENCES `storeTypes` (`id`); |
|
COMMIT; |
|
|
|
/*!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 */;
|
|
|