CREATE TABLE `intakes` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`dataset_id` bigint(20) unsigned NOT NULL,
|
`day_id` bigint(20) unsigned DEFAULT NULL,
|
`meal_id` bigint(20) unsigned DEFAULT NULL,
|
`group_id` bigint(20) unsigned DEFAULT NULL,
|
`food_id` bigint(20) unsigned DEFAULT NULL,
|
`code` bigint(20) unsigned NOT NULL,
|
`line` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`quantity` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
`portions` tinyint(3) unsigned DEFAULT NULL,
|
`hash` char(40) GENERATED ALWAYS AS (case when `line` is not null then sha(lcase(trim(`line`))) else NULL end) STORED,
|
`open_ended` tinyint(1) GENERATED ALWAYS AS (case when `food_id` is null then 1 else 0 end) STORED,
|
`attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attributes`)),
|
`created_date` date GENERATED ALWAYS AS (cast(`created_at` as date)) STORED,
|
`created_at` timestamp NULL DEFAULT NULL,
|
`updated_at` timestamp NULL DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `intakes_dataset_id_foreign` (`dataset_id`),
|
KEY `intakes_day_id_foreign` (`day_id`),
|
KEY `intakes_meal_id_foreign` (`meal_id`),
|
KEY `intakes_food_id_foreign` (`food_id`),
|
KEY `intakes_code_index` (`code`),
|
KEY `intakes_created_date_index` (`created_date`),
|
KEY `intakes_open_ended_index` (`open_ended`),
|
KEY `intakes_dataset_id_open_ended_index` (`dataset_id`,`open_ended`),
|
KEY `intakes_dataset_id_open_ended_created_date_index` (`dataset_id`,`open_ended`,`created_date`),
|
KEY `intakes_hash_index` (`hash`),
|
KEY `intakes_group_id_foreign` (`group_id`),
|
FULLTEXT KEY `intakes_line_fulltext` (`line`),
|
CONSTRAINT `intakes_dataset_id_foreign` FOREIGN KEY (`dataset_id`) REFERENCES `datasets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `intakes_day_id_foreign` FOREIGN KEY (`day_id`) REFERENCES `days` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
|
CONSTRAINT `intakes_food_id_foreign` FOREIGN KEY (`food_id`) REFERENCES `foods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `intakes_group_id_foreign` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
CONSTRAINT `intakes_meal_id_foreign` FOREIGN KEY (`meal_id`) REFERENCES `meals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=438584 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
Bug
Major
