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

3 months ago
  1. -- phpMyAdmin SQL Dump
  2. -- version 5.0.4
  3. -- https://www.phpmyadmin.net/
  4. --
  5. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  6. START TRANSACTION;
  7. SET time_zone = "+00:00";
  8. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  9. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11. /*!40101 SET NAMES utf8mb4 */;
  12. --
  13. -- Database: `shoppingList`
  14. --
  15. -- --------------------------------------------------------
  16. --
  17. -- Table structure for table `listEntries`
  18. --
  19. CREATE TABLE `listEntries` (
  20. `listId` int(11) NOT NULL,
  21. `productId` int(11) NOT NULL,
  22. `amount` float NOT NULL DEFAULT 1,
  23. `unit` int(11) NOT NULL
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  25. -- --------------------------------------------------------
  26. --
  27. -- Table structure for table `lists`
  28. --
  29. CREATE TABLE `lists` (
  30. `id` int(11) NOT NULL,
  31. `creationTime` bigint(20) NOT NULL,
  32. `comment` varchar(200) DEFAULT NULL
  33. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  34. -- --------------------------------------------------------
  35. --
  36. -- Table structure for table `products`
  37. --
  38. CREATE TABLE `products` (
  39. `id` int(11) NOT NULL,
  40. `name` varchar(100) NOT NULL,
  41. `synonyms` varchar(500) DEFAULT NULL COMMENT 'Separate entries with semicolons.',
  42. `storeTypeId` int(11) NOT NULL
  43. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  44. -- --------------------------------------------------------
  45. --
  46. -- Table structure for table `settings`
  47. --
  48. CREATE TABLE `settings` (
  49. `settingName` varchar(100) NOT NULL,
  50. `settingValue` varchar(100) NOT NULL
  51. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  52. --
  53. -- Dumping data for table `settings`
  54. --
  55. INSERT INTO `settings` (`settingName`, `settingValue`) VALUES
  56. ('databaseVersion', '3');
  57. -- --------------------------------------------------------
  58. --
  59. -- Table structure for table `storeTypes`
  60. --
  61. CREATE TABLE `storeTypes` (
  62. `id` int(11) NOT NULL,
  63. `name` varchar(200) NOT NULL
  64. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  65. -- --------------------------------------------------------
  66. --
  67. -- Table structure for table `units`
  68. --
  69. CREATE TABLE `units` (
  70. `id` int(11) NOT NULL,
  71. `name` varchar(100) NOT NULL,
  72. `abbreviation` varchar(10) NOT NULL,
  73. `isDefault` tinyint(2) NOT NULL DEFAULT 0,
  74. `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.',
  75. `isPiece` tinyint(2) NOT NULL DEFAULT 0
  76. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  77. --
  78. -- Indexes for dumped tables
  79. --
  80. --
  81. -- Indexes for table `listEntries`
  82. --
  83. ALTER TABLE `listEntries`
  84. ADD PRIMARY KEY (`listId`,`productId`,`unit`),
  85. ADD KEY `listEntries_ibfk_3` (`unit`),
  86. ADD KEY `listEntries_ibfk_2` (`productId`);
  87. --
  88. -- Indexes for table `lists`
  89. --
  90. ALTER TABLE `lists`
  91. ADD PRIMARY KEY (`id`);
  92. --
  93. -- Indexes for table `products`
  94. --
  95. ALTER TABLE `products`
  96. ADD PRIMARY KEY (`id`),
  97. ADD UNIQUE KEY `name` (`name`),
  98. ADD KEY `storeTypeId` (`storeTypeId`);
  99. --
  100. -- Indexes for table `settings`
  101. --
  102. ALTER TABLE `settings`
  103. ADD PRIMARY KEY (`settingName`);
  104. --
  105. -- Indexes for table `storeTypes`
  106. --
  107. ALTER TABLE `storeTypes`
  108. ADD PRIMARY KEY (`id`),
  109. ADD UNIQUE KEY `name` (`name`);
  110. --
  111. -- Indexes for table `units`
  112. --
  113. ALTER TABLE `units`
  114. ADD PRIMARY KEY (`id`);
  115. --
  116. -- AUTO_INCREMENT for dumped tables
  117. --
  118. --
  119. -- AUTO_INCREMENT for table `lists`
  120. --
  121. ALTER TABLE `lists`
  122. MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  123. --
  124. -- AUTO_INCREMENT for table `products`
  125. --
  126. ALTER TABLE `products`
  127. MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  128. --
  129. -- AUTO_INCREMENT for table `storeTypes`
  130. --
  131. ALTER TABLE `storeTypes`
  132. MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  133. --
  134. -- AUTO_INCREMENT for table `units`
  135. --
  136. ALTER TABLE `units`
  137. MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  138. --
  139. -- Constraints for dumped tables
  140. --
  141. --
  142. -- Constraints for table `listEntries`
  143. --
  144. ALTER TABLE `listEntries`
  145. ADD CONSTRAINT `listEntries_ibfk_1` FOREIGN KEY (`listId`) REFERENCES `lists` (`id`),
  146. ADD CONSTRAINT `listEntries_ibfk_2` FOREIGN KEY (`productId`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  147. ADD CONSTRAINT `listEntries_ibfk_3` FOREIGN KEY (`unit`) REFERENCES `units` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  148. --
  149. -- Constraints for table `products`
  150. --
  151. ALTER TABLE `products`
  152. ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`storeTypeId`) REFERENCES `storeTypes` (`id`);
  153. COMMIT;
  154. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  155. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  156. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;