mirror of
https://github.com/linuxserver/Clarkson.git
synced 2026-01-09 07:01:12 +08:00
117 lines
5.1 KiB
SQL
117 lines
5.1 KiB
SQL
CREATE TABLE `FuelUnits` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`unit` VARCHAR(20) NOT NULL,
|
|
`unitName` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC)
|
|
);
|
|
|
|
CREATE TABLE `FuelConsumptionUnits` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`unit` VARCHAR(20) NOT NULL,
|
|
`unitName` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC)
|
|
);
|
|
|
|
CREATE TABLE `DistanceUnits` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`unit` VARCHAR(20) NOT NULL,
|
|
`unitName` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC)
|
|
);
|
|
|
|
CREATE TABLE `CurrencyUnits` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`unit` VARCHAR(20) NOT NULL,
|
|
`unitName` VARCHAR(50) NOT NULL,
|
|
`symbol` VARCHAR(10) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC)
|
|
);
|
|
|
|
CREATE TABLE `Users` (
|
|
`id` VARCHAR(36) NOT NULL,
|
|
`email` VARCHAR(100) NOT NULL,
|
|
`username` VARCHAR(100) NOT NULL,
|
|
`password` VARCHAR(200) NOT NULL,
|
|
`admin` TINYINT(1) NULL DEFAULT 0,
|
|
`fuelUnit` INT(11) NOT NULL,
|
|
`distanceUnit` INT(11) NOT NULL,
|
|
`fuelConsumptionUnit` INT(11) NOT NULL,
|
|
`currencyUnit` INT(11) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC),
|
|
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
|
|
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
|
|
INDEX `FK_user_fuelUnit_idx` (`fuelUnit`),
|
|
INDEX `FK_user_distanceUnit_idx` (`distanceUnit`),
|
|
INDEX `FK_user_fuelConsumptionUnit_idx` (`fuelConsumptionUnit`),
|
|
INDEX `FK_user_currencyUnit_idx` (`currencyUnit`),
|
|
CONSTRAINT `FK_user_fuelUnit` FOREIGN KEY (`fuelUnit`) REFERENCES `FuelUnits` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `FK_user_distanceUnit` FOREIGN KEY (`distanceUnit`) REFERENCES `DistanceUnits` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `FK_user_fuelConsumptionUnit` FOREIGN KEY (`fuelConsumptionUnit`) REFERENCES `FuelConsumptionUnits` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `FK_user_currencyUnit` FOREIGN KEY (`currencyUnit`) REFERENCES `CurrencyUnits` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE `FuelTypes` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`type` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC)
|
|
);
|
|
|
|
CREATE TABLE `Vehicles` (
|
|
`id` VARCHAR(36) NOT NULL,
|
|
`user` VARCHAR(36) NOT NULL,
|
|
`name` VARCHAR(100) NOT NULL,
|
|
`registration` VARCHAR(20) DEFAULT NULL,
|
|
`make` VARCHAR(100) DEFAULT NULL,
|
|
`model` VARCHAR(100) DEFAULT NULL,
|
|
`picture` VARCHAR(100) DEFAULT NULL,
|
|
`yearOfManufacture` INT(11) NULL,
|
|
`vin` VARCHAR(100) NULL,
|
|
`engineSizeCC` INT(11) NULL,
|
|
`fuelType` INT(11) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `id_UNIQUE` (`id` ASC),
|
|
INDEX `FK_vehicle_user_idx` (`user`),
|
|
INDEX `FK_vehicle_fuelType_idx` (`fuelType`),
|
|
CONSTRAINT `FK_vehicle_fuelType` FOREIGN KEY (`fuelType`) REFERENCES `FuelTypes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `FK_vehicle_user` FOREIGN KEY (`user`) REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE `Fuel` (
|
|
`id` VARCHAR(36) NOT NULL,
|
|
`vehicle` VARCHAR(36) NOT NULL,
|
|
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`fuelAmount` DECIMAL(5,2) NOT NULL,
|
|
`totalCost` DECIMAL(5,2) NULL,
|
|
`fuelUnitCost` DECIMAL(5,3) NULL,
|
|
`locationLatitude` DECIMAL(10,8) NULL,
|
|
`locationLongitude` DECIMAL(11,8) NULL,
|
|
`odometerReading` INT(11) NULL,
|
|
`notes` TEXT NULL,
|
|
`fullTank` TINYINT(1) NULL DEFAULT 1,
|
|
`missedFillUp` TINYINT(1) NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
|
|
INDEX `FK_fuel_vehicleId_idx` (`vehicle` ASC),
|
|
CONSTRAINT `FK_fuel_vehicleId` FOREIGN KEY (`vehicle`) REFERENCES `Vehicles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE `Costs` (
|
|
`id` VARCHAR(36) NOT NULL,
|
|
`vehicle` VARCHAR(36) NOT NULL,
|
|
`date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`costType` VARCHAR(20) NOT NULL,
|
|
`totalCost` DECIMAL(6,2) NOT NULL,
|
|
`invoice` VARCHAR(100) NULL,
|
|
`notes` TEXT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
|
|
INDEX `FK_cost_vehicleId_idx` (`vehicle` ASC),
|
|
CONSTRAINT `FK_cost_vehicleId` FOREIGN KEY (`vehicle`) REFERENCES `Vehicles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
);
|