Bk2psPSA

1CREATE TABLE flightplan (
2  id                    UUID             NOT NULL DEFAULT uuid_generate_v4()PRIMARY KEY,
3  callsign              UUID             NOT NULL,
4  registration          UUID             NOT NULL,
5  uassp_id              UUID             NOT NULL,
6  uaso_id               UUID             NOT NULL,
7  pilot_id              UUID,
8  typeof_flight         VARCHAR,
9  public_id             VARCHAR                   DEFAULT '00',
10  departure_latitude    DOUBLE PRECISION NOT NULL,
11  departure_longitude   DOUBLE PRECISION NOT NULL,
12  departure_altitude    DOUBLE PRECISION NOT NULL,
13  departure_time        TIMESTAMPTZ      NOT NULL,
14  destination_latitude  DOUBLE PRECISION NOT NULL,
15  destination_longitude DOUBLE PRECISION NOT NULL,
16  destination_altitude  DOUBLE PRECISION NOT NULL,
17  destination_time      TIMESTAMPTZ      NOT NULL,
18  ground_speed          DOUBLE PRECISION,
19  altitude              DOUBLE PRECISION NOT NULL,
20  apply_status          VARCHAR                   DEFAULT '00',
21  adjust_result         VARCHAR                   DEFAULT '00',
22  flight_status         VARCHAR                   DEFAULT '00',
23  com_equipment         VARCHAR,
24  nav_equipment         VARCHAR(256),
25  route_type            VARCHAR          NOT NULL,
26  route_radius          DOUBLE PRECISION,
27  route_space_err       INTEGER,
28  route_time_eerr       INTEGER,
29  registration_info     VARCHAR(256),
30  flight_name           VARCHAR(256),
31  free_text             VARCHAR(1000)
32);
33
34
35CREATE TABLE flightplan_coordinate (
36  id            VARCHAR          NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
37  flightplan_id UUID             NOT NULL REFERENCES flightplan (id),
38  longitude     DOUBLE PRECISION NOT NULL,
39  latitude      DOUBLE PRECISION NOT NULL,
40  altitude      DOUBLE PRECISION,
41  velocity      DOUBLE PRECISION,
42  time          TIMESTAMPTZ
43);
44
45--============================================================
46
47CREATE TABLE flightplan_application_result (
48  id            UUID    NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
49  flightplan_id UUID    NOT NULL REFERENCES flightplan (id),
50  callsign      UUID    NOT NULL,
51  registration  UUID    NOT NULL,
52  uassp_id      UUID    NOT NULL,
53  uaso_id       UUID,
54  pilot_id      UUID,
55  apply_status  VARCHAR NOT NULL,
56  result_id     VARCHAR
57);
58
59
60CREATE TABLE incomplete_info (
61  id                               VARCHAR NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
62  flightplan_application_result_id UUID    NOT NULL REFERENCES flightplan_application_result (id),
63  content                          VARCHAR(256),
64  item                             VARCHAR(256)
65);
66
67
68CREATE TABLE conflict_info (
69  id                               UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
70  flightplan_application_result_id UUID NOT NULL REFERENCES flightplan_application_result (id),
71  emergency_id                     VARCHAR,
72  no_fly_area_id                   VARCHAR,
73  no_fly_area_name                 VARCHAR,
74  no_fly_area_type                 VARCHAR,
75  ground_obstacle_id               VARCHAR,
76  ground_obstacle_name             VARCHAR(256),
77  weather_info                     VARCHAR(256)
78);
79
80CREATE TABLE conflict_coordinate (
81  id                    UUID             NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
82  conflict_info_id      UUID             NOT NULL REFERENCES conflict_info (id),
83  start_point_longitude DOUBLE PRECISION NOT NULL,
84  start_point_latitude  DOUBLE PRECISION NOT NULL,
85  start_point_altitude  DOUBLE PRECISION NOT NULL,
86  start_time            TIMESTAMPTZ      NOT NULL,
87  end_point_longitude   DOUBLE PRECISION NOT NULL,
88  end_point_latitude    DOUBLE PRECISION NOT NULL,
89  end_point_altitude    DOUBLE PRECISION NOT NULL,
90  end_time              TIMESTAMPTZ      NOT NULL
91);
92
93--=================================================================================================
94
95CREATE TABLE telemetry (
96  id            UUID             NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
97  flightplan_id UUID             NOT NULL REFERENCES flightplan (id),
98  longitude     DOUBLE PRECISION NOT NULL,
99  latitude      DOUBLE PRECISION NOT NULL,
100  altitude_gl   DOUBLE PRECISION NOT NULL,
101  gpc_recv_time TIMESTAMPTZ      NOT NULL,
102  speed_x       DOUBLE PRECISION NOT NULL,
103  speed_y       DOUBLE PRECISION NOT NULL,
104  speed_z       DOUBLE PRECISION NOT NULL,
105  squence_no    INTEGER          NOT NULL,
106  yaw           DOUBLE PRECISION,
107  pitch         DOUBLE PRECISION,
108  roll          DOUBLE PRECISION
109);
110
111--===================================================================================================
112
113CREATE TABLE message (
114  id            UUID        NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
115  flightplan_id UUID        NOT NULL REFERENCES flightplan (id),
116  origin        VARCHAR     NOT NULL,
117  origin_id     UUID        NOT NULL,
118  time_sent     TIMESTAMPTZ NOT NULL,
119  severity      VARCHAR     NOT NULL,
120  message_type  VARCHAR     NOT NULL,
121  free_text     VARCHAR(1000),
122  callbacl      VARCHAR(256)
123);
124
125-- DROP SCRIPTS
126
127
128DROP TABLE IF EXISTS flightplan_coordinate;
129
130DROP TABLE IF EXISTS flightplan;
131
132--=========================================
133
134DROP TABLE IF EXISTS incomplete_info;
135
136DROP TABLE IF EXISTS conflict_coordinate;
137
138DROP TABLE IF EXISTS conflict_info;
139
140DROP TABLE IF EXISTS flightplan_application_result;
141
142--=================================================
143
144DROP TABLE IF EXISTS telemetry;
145
146--=================================================
147
148DROP TABLE IF EXISTS message;