4rMAHC8C

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	`active_flag` boolean NOT NULL DEFAULT TRUE
32) ENGINE=InnoDB DEFAULT CHARSET=latin1;
33
34DROP TABLE IF EXISTS `field_detail`;
35CREATE TABLE `field_detail`(
36	`field_id` int(32) PRIMARY KEY AUTO_INCREMENT,
37	`field_name` varchar(36) UNIQUE NOT NULL,
38	`field_group_id`  int(32) NOT NULL,
39	field_input_type_id int(32) NOT NULL
40	FOREIGN KEY (`field_group_id`) REFERENCES field_detail(`field_group_id`)
41  				on delete no action
42                on update no action,
43	FOREIGN KEY (`field_input_type_id`) REFERENCES field_input_type(`field_input_type_id`)
44  				on delete no action
45                on update no action
46) ENGINE=InnoDB DEFAULT CHARSET=latin1;
47
48
49DROP TABLE IF EXISTS `device_field_detail`;
50CREATE TABLE `device_field_detail`(
51	`id_device` int(32) NOT NULL,
52	`field_id` int(32) NOT NULL,
53	`option_id` int(32) NOT NULL,
54	FOREIGN KEY (`id_device`) REFERENCES device(`id_device`)
55  				on delete no action
56                on update no action,
57	FOREIGN KEY (`field_id`) REFERENCES field_detail(`field_id`)
58  				on delete no action
59                on update no action
60	FOREIGN KEY (`option_id`) REFERENCES field_option(`option_id`)
61  				on delete no action
62                on update no action
63	
64) ENGINE=InnoDB DEFAULT CHARSET=latin1;
65
66DROP TABLE IF EXISTS `field_input_type`;
67CREATE TABLE `field_input_type`(
68	`field_input_type_id` int(32) PRIMARY KEY AUTO_INCREMENT,
69	`input_type`  varchar(36) NOT NULL,
70	active_flag boolean NOT NULL DEFAULT TRUE
71) ENGINE=InnoDB DEFAULT CHARSET=latin1;
72
73DROP TABLE IF EXISTS `field_option`;
74CREATE TABLE `field_option`(
75	`option_id` int(32) PRIMARY KEY AUTO_INCREMENT,
76	`field_id`  varchar(36) NOT NULL,
77	`label` varchar(36)  NOT NULL,
78	`value_option` int(32) NOT NULL
79	FOREIGN KEY (`field_id`) REFERENCES field_detail(`field_id`)
80  				on delete no action
81                on update no action
82) ENGINE=InnoDB DEFAULT CHARSET=latin1;