0WYuHHBH

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