TMS view tables

If you need to construct view table queries, you can use the ones on this page as a template and you can also view existing view tables on databases with \d+ view_table_name

tms-booking

In this SQL b067f771 means customer ORG UUID first section

These six rows have been deleted from this query, as Hannu deemed them too time-consuming to implement for now:

CREATE OR REPLACE VIEW public.customer_session_b067f771 AS SELECT * FROM public.customer_session WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.customer_auth_b067f771 AS SELECT * FROM public.customer_auth WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.customer_auth_action_b067f771 AS SELECT * FROM public.customer_auth_action WHERE org_index='Customer_ORG_INDEX';

GRANT SELECT ON TABLE public.customer_session_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.customer_auth_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.customer_auth_action_b067f771 TO airbyte_b067f771;

Note: These queries use org_index instead of org_uuid

Create view tables to tms-booking DB:

CREATE OR REPLACE VIEW public.cost_center_b067f771 AS SELECT * FROM public.cost_center WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.booking_rating_b067f771 AS SELECT * FROM public.booking_rating WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.address_b067f771 AS SELECT * FROM public.address WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.customer_data_change_request_b067f771 AS SELECT * FROM public.customer_data_change_request WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.recurring_booking_b067f771 AS SELECT * FROM public.recurring_booking WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.customer_transport_account_b067f771 AS SELECT public.customer_transport_account.index, public.customer_transport_account.uuid, public.customer_transport_account.user_index, public.customer_transport_account.account_type, public.customer_transport_account.name, public.customer_transport_account.amount, public.customer_transport_account.amount_type, public.customer_transport_account.validity_type, public.customer_transport_account.valid_from, public.customer_transport_account.valid_until, public.customer_transport_account.exempt_from_copayment, public.customer_transport_account.created, public.customer_transport_account.updated, public.customer_transport_account.deleted_state, public.customer_transport_account.cost_center_index, public.customer.org_index FROM public.customer_transport_account INNER JOIN public.customer ON public.customer_transport_account.user_index = public.customer.index WHERE customer.org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.booking_event_b067f771 AS SELECT public.booking_event.index, public.booking_event.uuid, public.booking_event.booking_index, public.booking_event.type, public.booking_event.details, public.booking_event.created, public.booking_event.updated, public.booking.org_index FROM public.booking_event INNER JOIN public.booking ON public.booking_event.booking_index = public.booking.index WHERE public.booking.org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.customer_b067f771 AS SELECT * FROM public.customer WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.booking_b067f771 AS SELECT * FROM public.booking WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.copayment_b067f771 AS SELECT * FROM public.copayment WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.service_area_b067f771 AS SELECT * FROM public.service_area WHERE org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.booking_customer_b067f771 AS SELECT public.booking_customer.index, public.booking_customer.uuid, public.booking_customer.booking_index, public.booking_customer.user_index, public.booking_customer.user_transport_account_index, public.booking_customer.user_properties, public.booking_customer.booking_properties, public.booking_customer.created, public.booking_customer.updated FROM public.booking_customer INNER JOIN public.booking ON public.booking_customer.booking_index = public.booking.index WHERE public.booking.org_index='Customer_ORG_INDEX';
CREATE OR REPLACE VIEW public.booking_conversation_b067f771 AS SELECT public.booking_conversation.index, public.booking_conversation.uuid, public.booking_conversation.booking_uuid, public.booking_conversation.message, public.booking_conversation.created, public.booking_conversation.updated, public.booking_conversation.creator FROM public.booking_conversation INNER JOIN public.booking ON public.booking_conversation.booking_uuid = public.booking.uuid WHERE public.booking.org_index='Customer_ORG_INDEX';

GRANT SELECT ON TABLE public.cost_center_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.booking_rating_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.address_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.customer_data_change_request_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.recurring_booking_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.customer_transport_account_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.booking_event_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.customer_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.booking_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.copayment_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.service_area_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.booking_customer_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.booking_conversation_b067f771 TO airbyte_b067f771;

tms-drivers

tms-fleet

In this SQL b067f771 means customer ORG UUID first section

Create view tables to tms-fleet DB:

CREATE OR REPLACE VIEW public.agreement_b067f771 AS SELECT * FROM public.agreement WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.announcement_b067f771 AS SELECT * FROM public.announcement WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.announcement_answer_b067f771 AS SELECT * FROM public.announcement_answer WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.bill_correction_b067f771 AS SELECT * FROM public.bill_correction WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.bill_correction_company_b067f771 AS SELECT * FROM public.bill_correction_company WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.bill_export_b067f771 AS SELECT * FROM public.bill_export WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.billing_settings_b067f771 AS SELECT * FROM public.billing_settings WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.company_b067f771 AS SELECT * FROM public.company WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.product_code_b067f771 AS SELECT * FROM public.product_code WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.shift_agreement_b067f771 AS SELECT * FROM public.shift_agreement WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_bill_b067f771 AS SELECT * FROM public.trip_bill WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.vehicle_b067f771 AS SELECT * FROM public.vehicle WHERE vehicle->>'org_uuid'='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.vehicle_note_b067f771 AS SELECT * FROM public.vehicle_note WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.vehicle_pole_b067f771 AS SELECT * FROM public.vehicle_pole WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.vehicle_shift_b067f771 AS SELECT * FROM public.vehicle_shift WHERE org_uuid='Customer_ORG_UUID';

GRANT SELECT ON TABLE public.agreement_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.announcement_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.announcement_answer_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.bill_correction_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.bill_correction_company_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.bill_export_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.billing_settings_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.company_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.product_code_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.shift_agreement_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.trip_bill_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.vehicle_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.vehicle_note_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.vehicle_pole_b067f771 TO airbyte_b067f771;
GRANT SELECT ON TABLE public.vehicle_shift_b067f771 TO airbyte_b067f771;

tms-planner

CREATE OR REPLACE VIEW public.route_stop_abc12345 AS 
  SELECT route_stop.index,
    route_stop.uuid,
    route_stop.state,
    route_stop.route_index,
    route_stop.trip_index,
    route_stop.purpose,
    route_stop.booking_uuid,
    route_stop.started,
    route_stop.arrived,
    route_stop.completed,
    route_stop.created,
    route_stop.updated,
    route_stop.deleted_state,
    route_stop.waypoint_index,
    route_stop.transition_duration_seconds
   FROM route_stop
     JOIN trip ON route_stop.trip_index = trip.index
  WHERE trip.org_uuid = 'abc12345-abc1-2345-def1-abcd12345678'::uuid;
GRANT SELECT ON TABLE public.route_stop_abc12345 TO airbyte_abc12345;

CREATE OR REPLACE VIEW public.trip_abc12345 AS 
  SELECT trip.index,
    trip.uuid,
    trip.org_uuid,
    trip.state,
    trip.accepted_offer_index,
    trip.vehicle,
    trip.driver,
    trip.started,
    trip.completed,
    trip.created,
    trip.updated,
    trip.deleted_state,
    trip.estimates,
    trip.bonuses,
    trip.trip_metadata,
    trip.first_pickup,
    trip.last_dropoff
   FROM trip
  WHERE trip.org_uuid = 'abc12345-abc1-2345-def1-abcd12345678'::uuid;
GRANT SELECT ON TABLE public.trip_abc12345 TO airbyte_abc12345;

tms-reports

In this SQL 234cdsDF means customer ORG UUID first section

Create view tables to tms-reports DB:

CREATE OR REPLACE VIEW public.bill_correction_company_data_234cdsDF AS SELECT * FROM public.bill_correction_company_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.bill_correction_data_234cdsDF AS SELECT * FROM public.bill_correction_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.booking_report_data_234cdsDF AS SELECT * FROM public.booking_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.claims_report_data_234cdsDF AS SELECT * FROM public.claims_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.customer_transport_account_234cdsDF AS SELECT * FROM public.customer_transport_account WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.operator_claim_data_234cdsDF AS SELECT * FROM public.operator_claim_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.partner_claim_data_234cdsDF AS SELECT * FROM public.partner_claim_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.service_feedback_data_234cdsDF AS SELECT * FROM public.service_feedback_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.ticket_report_data_234cdsDF AS SELECT * FROM public.ticket_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_bill_accounting_report_data_234cdsDF AS SELECT * FROM public.trip_bill_accounting_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_offers_data_234cdsDF AS SELECT * FROM public.trip_offers_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_only_report_data_234cdsDF AS SELECT * FROM public.trip_only_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_report_data_234cdsDF AS SELECT * FROM public.trip_report_data WHERE org_uuid='Customer_ORG_UUID';
CREATE OR REPLACE VIEW public.trip_report_data_special_customer_234cdsDF AS SELECT * FROM public.trip_report_data_special_customer WHERE org_uuid='Customer_ORG_UUID';

GRANT SELECT ON TABLE public.bill_correction_company_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.bill_correction_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.booking_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.claims_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.customer_transport_account_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.operator_claim_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.partner_claim_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.service_feedback_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.ticket_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.trip_bill_accounting_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.trip_offers_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.trip_only_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.trip_report_data_234cdsDF TO airbyte_234cdsDF;
GRANT SELECT ON TABLE public.trip_report_data_special_customer_234cdsDF TO airbyte_234cdsDF;