From 3ff241d34a1c98e10fa8ed527e736f5c4940a2b8 Mon Sep 17 00:00:00 2001 From: "Ole B. Rosentreter" Date: Fri, 11 Jul 2025 12:44:11 +0200 Subject: [PATCH] small enhancements --- src/sql/airline.sql | 16 +++++-- src/sql/schema.sql | 99 +++++++++++++++++++++++++++++++++++++++++ src/webui/flughafen.php | 4 +- 3 files changed, 114 insertions(+), 5 deletions(-) create mode 100644 src/sql/schema.sql diff --git a/src/sql/airline.sql b/src/sql/airline.sql index 9e14361..26daa20 100644 --- a/src/sql/airline.sql +++ b/src/sql/airline.sql @@ -2,9 +2,12 @@ insert into airline.land values (default, 'Belarus'); insert into airline.flughafen values ('SJU', 'San Juan', false, 10, (select id from airline.land where land_name = 'Puerto Rico')); -insert into airline.strecke values ('SIN', 'SJU', 17651, 1820, 317, 103, 102, true, false, false); -insert into airline.strecke values ('FRA', 'SJU', 7513, 2298, 406, 136, 105, true, false, false); -insert into airline.strecke values ('GIG', 'SJU', 5350, 2351, 255, 70, 84, true, false, false); +insert into airline.strecke values ('DEN', 'DFW', 1032, 1718, 902, 407, 423, true, false, false); +insert into airline.strecke values ('DEN', 'SFO', 1553, 1721, 903, 408, 413, true, false, false); +insert into airline.strecke values ('DEN', 'LAX', 1385, 1764, 926, 418, 423, true, false, false); +insert into airline.strecke values ('DEN', 'LAS', 1009, 1679, 881, 398, 414, true, false, false); +insert into airline.strecke values ('DEN', 'DTW', 1802, 1669, 876, 395, 400, true, false, false); +insert into airline.strecke values ('DEN', 'ORD', 1426, 1764, 926, 418, 423, true, false, false); @@ -18,7 +21,14 @@ select * from airline.strecke s where s.komplett = false and s.aktiv = true orde select count(*) as anz, ziel_iata from airline.strecke s group by ziel_iata order by anz desc, ziel_iata asc; select count(*) as anz, ziel_iata from airline.strecke s where s.aktiv=true group by ziel_iata order by anz desc, ziel_iata asc; select count(*) as anz, ziel_iata from airline.strecke s where s.aktiv=true and s.komplett=true group by ziel_iata order by anz desc, ziel_iata asc; + select count(*) as anz, land_name from airline.flughafen_land fl group by land_name order by anz desc, land_name asc; +select land_name, iata, flughafen_name from airline.flughafen_land fl group by land_name, iata, flughafen_name order by land_name asc; + +select count(*) as anz, start_iata from airline.strecke s group by start_iata order by anz desc, start_iata asc; +select count(*) as anz, start_iata from airline.strecke s where s.aktiv=true group by start_iata order by anz desc, start_iata asc; +select count(*) as anz, start_iata from airline.strecke s where s.aktiv=true and s.komplett=true group by start_iata order by anz desc, start_iata asc; + select ziel_iata from airline.strecke s where start_iata != 'DXB' group by ziel_iata; diff --git a/src/sql/schema.sql b/src/sql/schema.sql new file mode 100644 index 0000000..98e72c1 --- /dev/null +++ b/src/sql/schema.sql @@ -0,0 +1,99 @@ +-- airline.config definition + +-- Drop table + +-- DROP TABLE airline.config; + +CREATE TABLE airline.config ( + config_key varchar NOT NULL, + value_bool numeric NOT NULL, + CONSTRAINT config_unique UNIQUE (config_key, value_bool) +); + +-- airline.land definition + +-- Drop table + +-- DROP TABLE airline.land; + +CREATE TABLE airline.land ( + id serial4 NOT NULL, + land_name varchar NOT NULL, + CONSTRAINT land_pk PRIMARY KEY (id), + CONSTRAINT land_unique UNIQUE (land_name) +); + +-- airline.flughafen definition + +-- Drop table + +-- DROP TABLE airline.flughafen; + +CREATE TABLE airline.flughafen ( + iata bpchar(3) NOT NULL, + flughafen_name varchar NOT NULL, + is_hub bool NOT NULL, + kategorie int4 NOT NULL, + land int4 NULL, + CONSTRAINT flughafen_pk PRIMARY KEY (iata), + CONSTRAINT flughafen_land_fk FOREIGN KEY (land) REFERENCES airline.land(id) ON DELETE CASCADE ON UPDATE CASCADE +); + +-- airline.hersteller definition + +-- Drop table + +-- DROP TABLE airline.hersteller; + +CREATE TABLE airline.hersteller ( + id serial4 NOT NULL, + hersteller_name varchar NOT NULL, + CONSTRAINT hersteller_pk PRIMARY KEY (id), + CONSTRAINT hersteller_unique UNIQUE (hersteller_name) +); + +-- airline.modell definition + +-- Drop table + +-- DROP TABLE airline.modell; + +CREATE TABLE airline.modell ( + modell_name varchar NOT NULL, + hersteller int4 NOT NULL, + classic bool DEFAULT false NOT NULL, + reichweite int4 NOT NULL, + preis int4 NOT NULL, + pax int4 NOT NULL, + speed int4 NOT NULL, + kategorie int4 DEFAULT 0 NOT NULL, + verbrauch float4 DEFAULT 0 NOT NULL, + abnutzung float4 DEFAULT 0 NOT NULL, + id varchar NULL, + CONSTRAINT modell_name_hersteller_unique UNIQUE (modell_name, hersteller), + CONSTRAINT modell_pk PRIMARY KEY (modell_name), + CONSTRAINT modell_hersteller_fk FOREIGN KEY (hersteller) REFERENCES airline.hersteller(id) ON DELETE CASCADE ON UPDATE CASCADE +); + +-- airline.strecke definition + +-- Drop table + +-- DROP TABLE airline.strecke; + +CREATE TABLE airline.strecke ( + start_iata bpchar(3) NOT NULL, + ziel_iata bpchar(3) NOT NULL, + strecke int4 NOT NULL, + pax_economy int4 NOT NULL, + pax_business int4 NOT NULL, + pax_first int4 NOT NULL, + fracht int4 DEFAULT 0 NULL, + aktiv bool DEFAULT false NOT NULL, + in_arbeit bool DEFAULT false NOT NULL, + komplett bool DEFAULT false NOT NULL, + gebuehr int4 DEFAULT 0 NOT NULL, + CONSTRAINT strecke_pk PRIMARY KEY (ziel_iata, start_iata), + CONSTRAINT strecke_flughafen_start_fk FOREIGN KEY (start_iata) REFERENCES airline.flughafen(iata) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT strecke_flughafen_ziel_fk FOREIGN KEY (ziel_iata) REFERENCES airline.flughafen(iata) ON DELETE CASCADE ON UPDATE CASCADE +); \ No newline at end of file diff --git a/src/webui/flughafen.php b/src/webui/flughafen.php index f13dc81..5935ccd 100755 --- a/src/webui/flughafen.php +++ b/src/webui/flughafen.php @@ -96,7 +96,7 @@ getTableHead("flughafen_name", "Stadt", $page); ?>