63 lines
1.5 KiB
SQL
63 lines
1.5 KiB
SQL
CREATE TABLE `meta` (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`value` TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE `client` (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`connect_type` INT NOT NULL,
|
|
`addr` TEXT NOT NULL,
|
|
`reso` INT NULL,
|
|
`name` TEXT NOT NULL,
|
|
`options` TEXT NULL,
|
|
`port` INT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE `led` (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NULL,
|
|
`options` TEXT NULL,
|
|
`local_id` INT NULL,
|
|
`client_id` INT NOT NULL,
|
|
CONSTRAINT `fk_led_client`
|
|
FOREIGN KEY (`client_id`)
|
|
REFERENCES `client` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION
|
|
);
|
|
|
|
CREATE INDEX `fk_led_client_idx`
|
|
ON `led` (`client_id` ASC);
|
|
|
|
CREATE TABLE IF NOT EXISTS `group` (
|
|
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
`name` TEXT NOT NULL,
|
|
`type` INT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS `led_has_group` (
|
|
`led_id` INT NOT NULL,
|
|
`group_id` INT NOT NULL,
|
|
PRIMARY KEY (`led_id`, `group_id`),
|
|
CONSTRAINT `fk_led_has_group_led1`
|
|
FOREIGN KEY (`led_id`)
|
|
REFERENCES `led` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk_led_has_group_group1`
|
|
FOREIGN KEY (`group_id`)
|
|
REFERENCES `group` (`id`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION
|
|
);
|
|
|
|
CREATE INDEX `fk_led_has_group_group1_idx`
|
|
ON `led_has_group` (`group_id` ASC);
|
|
|
|
CREATE INDEX `fk_led_has_group_led1_idx`
|
|
ON `led_has_group` (`led_id` ASC);
|
|
|
|
|
|
INSERT INTO meta ("name", "value") VALUES ("version", "1");
|