48 lines
2.0 KiB
SQL
48 lines
2.0 KiB
SQL
CREATE TABLE `battery_groups` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`brand_id` integer NOT NULL,
|
|
`type_id` integer NOT NULL,
|
|
`available_count` integer DEFAULT 0 NOT NULL,
|
|
`charging_count` integer DEFAULT 0 NOT NULL,
|
|
`notes` text,
|
|
`created_at` text DEFAULT (datetime('now')),
|
|
`updated_at` text DEFAULT (datetime('now')),
|
|
FOREIGN KEY (`brand_id`) REFERENCES `brands`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`type_id`) REFERENCES `battery_types`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `brand_type_idx` ON `battery_groups` (`brand_id`,`type_id`);--> statement-breakpoint
|
|
CREATE TABLE `battery_types` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`is_custom` integer DEFAULT false,
|
|
`created_at` text DEFAULT (datetime('now'))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `battery_types_name_unique` ON `battery_types` (`name`);--> statement-breakpoint
|
|
CREATE TABLE `brands` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`created_at` text DEFAULT (datetime('now'))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `brands_name_unique` ON `brands` (`name`);--> statement-breakpoint
|
|
CREATE TABLE `device_batteries` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`device_id` integer NOT NULL,
|
|
`battery_group_id` integer NOT NULL,
|
|
`quantity` integer DEFAULT 1 NOT NULL,
|
|
`assigned_at` text DEFAULT (datetime('now')),
|
|
FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON UPDATE no action ON DELETE cascade,
|
|
FOREIGN KEY (`battery_group_id`) REFERENCES `battery_groups`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `device_battery_idx` ON `device_batteries` (`device_id`,`battery_group_id`);--> statement-breakpoint
|
|
CREATE TABLE `devices` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`description` text,
|
|
`created_at` text DEFAULT (datetime('now')),
|
|
`updated_at` text DEFAULT (datetime('now'))
|
|
);
|