phantomknight287/aero

public

Implement OpenAPI documentation and enhance authentication featurescc012f32 years ago155 lines · 4.6 KB

1	-- CreateEnum
2	CREATE TYPE "AirportType" AS ENUM ('
3
4	-- CreateEnum
5	CREATE TYPE "FlightType" AS ENUM (
6
7	-- CreateEnum
8	CREATE TYPE "FlightStatus" AS ENUM (
9
10	-- CreateTable
11	CREATE TABLE "airports" (
12	"id" TEXT NOT NULL
13	"ident" TEXT NOT NULL
14	"type" "AirportType" NOT NULL
15	"elevation" DECIMAL(65
16	"continent" TEXT NOT NULL
17	"iso_country" TEXT NOT NULL
18	"iso_region" TEXT NOT NULL
19	"municipality" TEXT,
20	"gps_code" TEXT,
21	"iata_code" TEXT,
22	"name" TEXT NOT NULL
23	"lat" DECIMAL(65
24	"long" DECIMAL(65
25	"created_at" TIMESTAMP(3
26	"updated_at" TIMESTAMP(3
27
28	CONSTRAINT "airports_pkey" PRIMARY KEY
29	);
30
31	-- CreateTable
32	CREATE TABLE "airlines" (
33	"id" TEXT NOT NULL
34	"name" TEXT NOT NULL
35	"icao" TEXT NOT NULL
36	"iata" TEXT NOT NULL
37	"image" TEXT NOT NULL
38	"created_at" TIMESTAMP(3
39	"updated_at" TIMESTAMP(3
40
41	CONSTRAINT "airlines_pkey" PRIMARY KEY
42	);
43
44	-- CreateTable
45	CREATE TABLE "aircrafts" (
46	"id" TEXT NOT NULL
47	"aircraft_id" TEXT NOT NULL
48	"reg" TEXT NOT NULL
49	"hex_icao" TEXT,
50	"model_code" TEXT,
51	"age" DECIMAL(65
52	"first_flight_date" TIMESTAMP(3
53	"delivery_date" TIMESTAMP(3
54	"type_name" TEXT,
55	"is_freighter" BOOLEAN NOT NULL
56	"image" TEXT,
57	"attribution" TEXT,
58	"created_at" TIMESTAMP(3
59	"updated_at" TIMESTAMP(3
60
61	CONSTRAINT "aircrafts_pkey" PRIMARY KEY
62	);
63
64	-- CreateTable
65	CREATE TABLE "flights" (
66	"id" TEXT NOT NULL
67	"airline" JSONB NOT NULL,
68	"arrival" JSONB NOT NULL,
69	"codeshared" JSONB,
70	"departure" JSONB NOT NULL,
71	"flight" JSONB NOT NULL,
72	"type" "FlightType" NOT NULL
73	"status" "FlightStatus",
74	"created_at" TIMESTAMP(3
75	"updated_at" TIMESTAMP(3
76
77	CONSTRAINT "flights_pkey" PRIMARY KEY
78	);
79
80	-- CreateTable
81	CREATE TABLE "great_circle_distance" (
82	"id" TEXT NOT NULL
83	"meter" DECIMAL(65
84	"km" DECIMAL(65
85	"mile" DECIMAL(65
86	"nm" DECIMAL(65
87	"feet" DECIMAL(65
88	"flight_id" TEXT NOT NULL
89	"created_at" TIMESTAMP(3
90	"updated_at" TIMESTAMP(3
91
92	CONSTRAINT "great_circle_distance_pkey" PRIMARY KEY
93	);
94
95	-- CreateTable
96	CREATE TABLE "flight" (
97	"id" TEXT NOT NULL
98	"flight_no" TEXT NOT NULL
99	"aircraft" JSONB NOT NULL,
100	"airline" JSONB NOT NULL,
101	"arrival" JSONB NOT NULL,
102	"departure" JSONB NOT NULL,
103	"cargo" BOOLEAN NOT NULL
104	"date" TIMESTAMP(3
105	"call_sign" TEXT NOT NULL
106	"created_at" TIMESTAMP(3
107	"updated_at" TIMESTAMP(3
108
109	CONSTRAINT "flight_pkey" PRIMARY KEY
110	);
111
112	-- CreateTable
113	CREATE TABLE "aircraft_registrations" (
114	"id" TEXT NOT NULL
115	"reg" TEXT NOT NULL
116	"active" BOOLEAN NOT NULL
117	"hex_icao" TEXT,
118	"airline_name" TEXT,
119	"registration_date" TIMESTAMP(3
120	"created_at" TIMESTAMP(3
121	"updated_at" TIMESTAMP(3
122	"aircraft_id" TEXT,
123
124	CONSTRAINT "aircraft_registrations_pkey" PRIMARY KEY
125	);
126
127	-- CreateIndex
128	CREATE UNIQUE INDEX "airports_id_key" ON
129
130	-- CreateIndex
131	CREATE UNIQUE INDEX "airlines_id_key" ON
132
133	-- CreateIndex
134	CREATE UNIQUE INDEX "aircrafts_id_key" ON
135
136	-- CreateIndex
137	CREATE UNIQUE INDEX "flights_id_key" ON
138
139	-- CreateIndex
140	CREATE UNIQUE INDEX "great_circle_distance_id_key" ON
141
142	-- CreateIndex
143	CREATE UNIQUE INDEX "great_circle_distance_flight_id_key" ON
144
145	-- CreateIndex
146	CREATE UNIQUE INDEX "flight_id_key" ON
147
148	-- CreateIndex
149	CREATE UNIQUE INDEX "aircraft_registrations_id_key" ON
150
151	-- AddForeignKey
152	ALTER TABLE "great_circle_distance" ADD
153
154	-- AddForeignKey
155	ALTER TABLE "aircraft_registrations" ADD