fAbJDYYe

1
2-- device
3CREATE TABLE  `device` (
4  `id_device` int(32) PRIMARY KEY AUTO_INCREMENT,
5  `id_tipo_device` int(32) NOT NULL,
6  `id_azienda` int(32) NOT NULL,
7  `uuid` varchar(36) NOT NULL,
8  `flag_attivo` boolean NOT NULL DEFAULT TRUE,
9  FOREIGN KEY (`id_tipo_device`) REFERENCES device_type(`id_tipo_device`)
10  				on delete no action
11                on update no action,
12  FOREIGN KEY (`id_azienda`) REFERENCES aziende (`id_azienda`)
13    			on delete no action
14    			on update no action,						
15  UNIQUE KEY `id_tipo_device.uuid` (`id_tipo_device`, `uuid`)
16) ENGINE=InnoDB DEFAULT CHARSET=latin1;
17
18-- tipo_device 
19DROP TABLE IF EXISTS `tipo_device`;
20CREATE TABLE  `tipo_device` (
21  `id_tipo_device` int(32) PRIMARY KEY AUTO_INCREMENT,
22  `descr` varchar(512) UNIQUE NOT NULL,
23  `flag_attivo` boolean NOT NULL DEFAULT TRUE
24) ENGINE=InnoDB DEFAULT CHARSET=latin1;
25
26
27DROP TABLE IF EXISTS `field_group_type`;
28CREATE TABLE `field_group_type`(
29	`field_group_id` int(32) PRIMARY KEY AUTO_INCREMENT,
30	`field_group_desc` varchar(36) UNIQUE NOT NULL,
31	`configuration_flag` boolean NOT NULL DEFAULT FALSE
32	`notification_flag` boolean NOT NULL DEFAULT FALSE
33	`active_flag` boolean NOT NULL DEFAULT TRUE
34) ENGINE=InnoDB DEFAULT CHARSET=latin1;
35
36DROP TABLE IF EXISTS `field_detail`;
37CREATE TABLE `field_detail`(
38	`field_id` int(32) PRIMARY KEY AUTO_INCREMENT,
39	`field_name` varchar(36) UNIQUE NOT NULL,
40	`field_group_id`  int(32) NOT NULL,
41	`field_input_type_id` int(32) NOT NULL
42	FOREIGN KEY (`field_group_id`) REFERENCES field_group_type(`field_group_id`)
43  				on delete no action
44                on update no action,
45	FOREIGN KEY (`field_input_type_id`) REFERENCES field_input_type(`field_input_type_id`)
46  				on delete no action
47                on update no action
48) ENGINE=InnoDB DEFAULT CHARSET=latin1;
49
50
51DROP TABLE IF EXISTS `device_field_detail`;
52CREATE TABLE `device_field_detail`(
53	`id_device` int(32) NOT NULL,
54	`field_id` int(32) NOT NULL,
55	`field_value` int(32) NOT NULL,
56	FOREIGN KEY (`id_device`) REFERENCES device(`id_device`)
57  				on delete no action
58                on update no action,
59	FOREIGN KEY (`field_id`) REFERENCES field_detail(`field_id`)
60  				on delete no action
61                on update no action
62) ENGINE=InnoDB DEFAULT CHARSET=latin1;
63
64DROP TABLE IF EXISTS `field_input_type`;
65CREATE TABLE `field_input_type`(
66	`field_input_type_id` int(32) PRIMARY KEY AUTO_INCREMENT,
67	`input_type`  varchar(36) NOT NULL,
68	`option_flag` boolean NOT NULL DEFAULT TRUE,
69	`active_flag` boolean NOT NULL DEFAULT TRUE
70) ENGINE=InnoDB DEFAULT CHARSET=latin1;
71
72DROP TABLE IF EXISTS `field_option`;
73CREATE TABLE `field_option`(
74	`option_id` int(32) PRIMARY KEY AUTO_INCREMENT,
75	`field_id`  varchar(36) NOT NULL,
76	`label` varchar(36)  NOT NULL,
77	`value_option` int(32) NOT NULL
78	FOREIGN KEY (`field_id`) REFERENCES field_detail(`field_id`)
79  				on delete no action
80                on update no action
81) ENGINE=InnoDB DEFAULT CHARSET=latin1;