28 lines
1.2 KiB
SQL
28 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS `consumable` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(255) DEFAULT NULL,
|
|
`unit` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `name` (`name`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
|
|
|
|
INSERT INTO `consumable`(`id`, `name`, `unit`) VALUES (1, 'Super E5', 'l');
|
|
|
|
CREATE TABLE IF NOT EXISTS `vehicles_consumables` (
|
|
`vehicle_id` int(11) DEFAULT NULL,
|
|
`consumable_id` int(11) DEFAULT NULL,
|
|
KEY `vehicle_id` (`vehicle_id`),
|
|
KEY `consumable_id` (`consumable_id`),
|
|
CONSTRAINT `vehicles_consumables_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicle` (`id`),
|
|
CONSTRAINT `vehicles_consumables_ibfk_2` FOREIGN KEY (`consumable_id`) REFERENCES `consumable` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
ALTER TABLE `pitstop` ADD COLUMN `costs` decimal(5,2) DEFAULT NULL;
|
|
|
|
ALTER TABLE `pitstop` CHANGE `litres` `amount` decimal(5,2) DEFAULT NULL;
|
|
|
|
ALTER TABLE `pitstop` ADD COLUMN `consumable_id` int(11);
|
|
ALTER TABLE `pitstop` ADD FOREIGN KEY (`consumable_id`) REFERENCES `consumable` (`id`);
|
|
UPDATE `pitstop` set `consumable_id`=1;
|
|
|
|
INSERT INTO `vehicles_consumables`(`vehicle_id`, `consumable_id`) SELECT `id`, 1 FROM `vehicle`; |