-- ================================================================
-- airport poc — duality views (graphql syntax) — final
-- oracle 23ai / oracle ai database
--
-- rules applied (hard-won from debugging):
--
-- 1. no @unnest — all nested tables are standard objects.
-- @unnest flattens columns into the parent namespace; if a
-- column name already exists in the parent (e.g. airline_id
-- in both flight and airline), oracle's internal type parser
-- collapses with ORA-22857.
--
-- 2. @link is explicit on every relationship.
-- FK in OUTER table → @link(from: ["COL"])
-- FK in INNER table → @link(to: ["COL"])
-- No direct FK → @link(from: ["COL"], to: ["COL"])
--
-- 3. Do NOT project the PK when it is also the FK joining to
-- the parent (flight_status.flight_id = PK + FK → omit it).
-- For all other nested tables, include their PK.
--
-- 4. No scalar nested inside another scalar (SQL rule, also
-- safer in GraphQL). passenger_notif_dv joins flightStatus
-- directly to the booking root via shared flight_id.
-- ================================================================
-- ────────────────────────────────────────────────────────────────
-- 1. departures_board_dv
-- ────────────────────────────────────────────────────────────────
CREATE JSON RELATIONAL DUALITY VIEW departures_board_dv AS
flight @noinsert @noupdate @nodelete {
_id : flight_id,
flightNumber : flight_number,
schedDep : sched_dep,
airline : airline @link(from: ["AIRLINE_ID"])
@noinsert @noupdate @nodelete {
airlineId : airline_id,
airlineName : name @nocheck
},
destination : airport_ref @link(from: ["DEST_ID"])
@noinsert @noupdate @nodelete {
destId : airport_id,
destCity : city @nocheck,
destIata : iata_code @nocheck
},
flightStatus : flight_status @link(to: ["FLIGHT_ID"])
@noinsert @update @nodelete {
statusKey : flight_id @noupdate,
code : status_code @nocheck,
delayMins : delay_minutes @nocheck,
actualDep : actual_dep @nocheck,
boardingOpens: boarding_opens @nocheck,
terminal : gate_terminal @nocheck,
gateNo : gate_number @nocheck
}
};
-- ────────────────────────────────────────────────────────────────
-- 2. arrivals_board_dv
-- ────────────────────────────────────────────────────────────────
CREATE JSON RELATIONAL DUALITY VIEW arrivals_board_dv AS
flight @noinsert @noupdate @nodelete {
_id : flight_id,
flightNumber : flight_number,
schedArr : sched_arr,
airline : airline @link(from: ["AIRLINE_ID"])
@noinsert @noupdate @nodelete {
airlineId : airline_id,
airlineName : name @nocheck
},
origin : airport_ref @link(from: ["ORIGIN_ID"])
@noinsert @noupdate @nodelete {
originId : airport_id,
originCity : city @nocheck,
originIata : iata_code @nocheck
},
flightStatus : flight_status @link(to: ["FLIGHT_ID"])
@noinsert @update @nodelete {
statusKey : flight_id @noupdate,
code : status_code @nocheck,
delayMins : delay_minutes @nocheck,
actualArr : actual_arr @nocheck,
baggageBelt : baggage_belt @nocheck,
terminal : gate_terminal @nocheck,
gateNo : gate_number @nocheck
}
};
-- ────────────────────────────────────────────────────────────────
-- 3. flight_ops_dv
-- ────────────────────────────────────────────────────────────────
CREATE JSON RELATIONAL DUALITY VIEW flight_ops_dv AS
flight @insert @update @delete {
_id : flight_id,
flightNumber : flight_number,
flightDate : flight_date,
schedDep : sched_dep,
schedArr : sched_arr,
airline : airline @link(from: ["AIRLINE_ID"])
@noinsert @noupdate @nodelete {
airlineId : airline_id,
airlineName : name @nocheck
},
origin : airport_ref @link(from: ["ORIGIN_ID"])
@noinsert @noupdate @nodelete {
originId : airport_id,
originCity : city @nocheck,
originIata : iata_code @nocheck
},
dest : airport_ref @link(from: ["DEST_ID"])
@noinsert @noupdate @nodelete {
destId : airport_id,
destCity : city @nocheck,
destIata : iata_code @nocheck
},
aircraft : aircraft_type @link(from: ["TYPE_ID"])
@noinsert @noupdate @nodelete {
typeId : type_id,
icaoType : icao_type @nocheck,
seatCapacity : seat_capacity @nocheck
},
flightStatus : flight_status @link(to: ["FLIGHT_ID"])
@noinsert @update @nodelete {
statusKey : flight_id @noupdate,
gateId : gate_id @nocheck,
code : status_code @nocheck,
delayMins : delay_minutes @nocheck,
actualDep : actual_dep @nocheck,
actualArr : actual_arr @nocheck,
boardingOpens: boarding_opens @nocheck,
checkinCloses: checkin_closes @nocheck,
baggageBelt : baggage_belt @nocheck,
terminal : gate_terminal @nocheck,
gateNo : gate_number @nocheck,
updatedAt : updated_at @nocheck
},
checkinDesks : flight_checkin @link(to: ["FLIGHT_ID"])
@insert @update @delete [
{
flightCheckinId : flight_checkin_id,
openTime : open_time @update,
closeTime : close_time @update,
desk : checkin_desk @link(from: ["DESK_ID"])
@noinsert @noupdate @nodelete {
deskId : desk_id,
terminal : terminal @nocheck,
deskNumber : desk_number @nocheck,
deskLabel : desk_label @nocheck
}
}
]
};
-- ────────────────────────────────────────────────────────────────
-- 4. gate_boarding_dv
-- ────────────────────────────────────────────────────────────────
CREATE JSON RELATIONAL DUALITY VIEW gate_boarding_dv AS
SELECT JSON {
'_id' : f.flight_id,
'flightnumber' : f.flight_number,
'scheddep' : f.sched_dep,
'flightstatus' : (SELECT JSON {
'statuskey' : fs.flight_id WITH NOUPDATE,
'code' : fs.status_code WITH NOCHECK,
'terminal' : fs.gate_terminal WITH NOCHECK,
'gateno' : fs.gate_number WITH NOCHECK
}
FROM flight_status fs WITH NOINSERT NOUPDATE NODELETE
WHERE fs.flight_id = f.flight_id),
'passengers' : [ SELECT JSON {
'bookingid' : b.booking_id,
'bookingref' : b.booking_ref,
'seatnumber' : b.seat_number,
'cabinclass' : b.cabin_class,
UNNEST (SELECT JSON {
'passengerid' : p.passenger_id,
'firstname' : p.first_name WITH NOCHECK,
'lastname' : p.last_name WITH NOCHECK
}
FROM passenger p WITH NOINSERT NOUPDATE NODELETE
WHERE p.passenger_id = b.passenger_id),
'boarding' : (SELECT JSON {
'boardingid' : br.boarding_id,
'boardingstatus' : br.boarding_status WITH UPDATE,
'checkintime' : br.checkin_time WITH NOCHECK,
'boardingtime' : br.boarding_time WITH NOCHECK,
'notifsentat' : br.notif_sent_at WITH NOCHECK
}
FROM boarding br WITH NOINSERT UPDATE NODELETE
WHERE br.booking_id = b.booking_id)
}
FROM booking b WITH NOINSERT NOUPDATE NODELETE
WHERE b.flight_id = f.flight_id ]
}
FROM flight f WITH NOINSERT NOUPDATE NODELETE;
-- ────────────────────────────────────────────────────────────────
-- 5. passenger_notif_dv
-- flightStatus joins directly to booking root via shared
-- flight_id column — avoids scalar-inside-scalar nesting.
-- ────────────────────────────────────────────────────────────────
CREATE JSON RELATIONAL DUALITY VIEW passenger_notif_dv AS
SELECT JSON {
'_id' : b.booking_id,
'bookingref' : b.booking_ref,
'seatnumber' : b.seat_number,
UNNEST (SELECT JSON {
'passengerid' : p.passenger_id,
'firstname' : p.first_name WITH NOUPDATE NOCHECK,
'lastname' : p.last_name WITH NOUPDATE NOCHECK,
'contactemail' : p.contact_email WITH NOUPDATE NOCHECK,
'contactphone' : p.contact_phone WITH NOUPDATE NOCHECK
}
FROM passenger p WITH NOINSERT NOUPDATE NODELETE
WHERE p.passenger_id = b.passenger_id),
UNNEST (SELECT JSON {
'flightid' : f.flight_id WITH NOUPDATE,
'flightnumber' : f.flight_number WITH NOCHECK,
'scheddep' : f.sched_dep WITH NOCHECK
}
FROM flight f WITH NOINSERT NOUPDATE NODELETE
WHERE f.flight_id = b.flight_id),
'flightstatus' : (SELECT JSON {
'statuskey' : fs.flight_id WITH NOUPDATE,
'code' : fs.status_code WITH NOCHECK,
'delaymins' : fs.delay_minutes WITH NOCHECK,
'terminal' : fs.gate_terminal WITH NOCHECK,
'gateno' : fs.gate_number WITH NOCHECK
}
FROM flight_status fs WITH NOINSERT NOUPDATE NODELETE
WHERE fs.flight_id = b.flight_id),
'boarding' : (SELECT JSON {
'boardingid' : br.boarding_id,
'boardingstatus' : br.boarding_status WITH NOUPDATE NOCHECK,
'checkintime' : br.checkin_time WITH NOUPDATE NOCHECK,
'notifsentat' : br.notif_sent_at WITH UPDATE NOCHECK
}
FROM boarding br WITH NOINSERT UPDATE NODELETE
WHERE br.booking_id = b.booking_id)
}
FROM booking b WITH NOINSERT NOUPDATE NODELETE;